<?php
/**
* 对Yii2数据封装
* @author nike@youfumama.com
* @date 2017-03-08
* 抽象类不能被实例化
*
eg1: select
use common\models as b_model;
$users_model = b_model\FactoryModel::users();
$users_model->setPk(1);
$users_model->setFields('id,name,age,sex');
$result = $users_model->getInfoByPk();
=================================================
eg2: update|insert
use common\models as b_model;
use common\models\entity as b_entity;
$users_entity = new b_entity\Users();
$users_entity->name = '尼克';
$users_entity->age = 25;
$users_model = b_model\FactoryModel::users();
$users_model->setPk(1);
$result = $users_model->updateByPk($users_entity);
*/
namespace common\models;
use yii;
use yii\db\ActiveRecord;
/**
* Base model
* @author nike@youfumama.com
* @date 2017-03-08
*/
abstract class BaseModel extends ActiveRecord
{
protected static $_db = null;
private $sql;
private $PrimaryKeyField; //主键字段名
private $pk; //主键
private $field_values;//存放数据库字段数组
protected $table;
protected $field = '*';
protected $where;
protected $orderby;
protected $limit;
protected $groupby;
/**
* set db
* @param type $db
*/
protected function _setDb($db = null)
{
if(null == $db)
$db = Yii::$app->db;
self::$_db = $db;
}
/**
* get db
* @return type
*/
protected function _getDb()
{
if(empty(self::$_db))
$this->_setDb ();
return self::$_db;
}
/**
* 设置SQL语句
*/
private function setSQL($sql)
{
$this->sql = $sql;
}
/**
* 获取SQL语句
*/
function getSQL()
{
return $this->sql;
}
/**
* 设置field_values
*/
private function setFieldValues($field_values)
{
if(!is_object($field_values))
throw new Exception ('entity must be Object', 500);
$this->field_values = $field_values;
}
/**
* 获取field_values
*/
private function getFieldValues()
{
return $this->cleanArray((array)$this->field_values);
}
/**
* 清除values of SQL injections
* @param type $array
* @return array
*/
private function cleanArray($array)
{
$array = array_map('trim', $array);
$array = array_map('stripslashes', $array);
$array = array_map('mysql_real_escape_string', $array);
return $array;
}
/**
* 设置主键字段名
*/
protected function setPrimaryKeyField($PrimaryKeyField)
{
$this->PrimaryKeyField = $PrimaryKeyField;
}
/**
* 获取主键字段名
*/
protected function getPrimaryKeyField()
{
return $this->PrimaryKeyField;
}
/**
* 设置主键值
* @param int
*/
public function setPk($pk)
{
$this->pk = $pk;
}
/**
* 获取主键值
* @return int
*/
public function getPk()
{
return $this->pk;
}
/**
* 设置表名
*/
protected function setTable($table)
{
$this->table = $table;
}
/**
* 获取表名
*/
protected function getTable()
{
return $this->table;
}
/**
* 设置Fields
*/
function setFields($fields)
{
$this->field = $fields;
}
/**
* 获取Fields
*/
function getFields()
{
return $this->field;
}
/**
* 设置where条件
*/
function setWhere($where)
{
$this->where = $where;
}
/**
* 获取where条件
*/
function getWhere()
{
return $this->where;
}
/**
* 设置Group
*/
function setGroupBy($groupby)
{
$this->groupby = $groupby;
}
/**
* 获取Group
*/
function getGroupBy()
{
return $this->groupby;
}
/**
* 设置Order
*/
function setOrderBy($orderby)
{
$this->orderby = $orderby;
}
/**
* 设置Order
*/
function getOrderBy()
{
return $this->orderby;
}
/**
* 设置条数
*/
function setLimit( $limit )
{
$this->limit = $limit;
}
/**
* 获取条数
*/
function getLimit()
{
return $this->limit;
}
/**
* 根据主键获取
*/
function getInfoByPk()
{
$sql = "select {$this->getFields()} "
."from {$this->getTable()} "
."where {$this->getPrimaryKeyField()}={$this->getPk()}";
return $this->query_one($sql);
}
/**
* 根据where条件获取一条信息
*/
function getOneByWhere()
{
$sql = "SELECT {$this->getFields()} "
. "FROM {$this->getTable()} "
. "WHERE {$this->getWhere()}";
return $this->query_one($sql);
}
/**
* 根据where条件获取数组列表
*/
function getListByWhere()
{
$sql = "SELECT ";
$sql .= "{$this->getFields()} "
. "FROM {$this->getTable()} ";
if ( $this->getWhere() != null ) {
$sql .= "WHERE {$this->getWhere()} ";
}
if ( $this->getGroupby() != null ) {
$sql .= "GROUP BY {$this->getGroupby()} ";
}
if ( $this->getOrderby() != null ) {
$sql .= "ORDER BY {$this->getOrderby()} ";
}
if ( $this->getLimit() != null ) {
$sql .= "LIMIT {$this->getLimit()}";
}
return $this->query_all($sql);
}
/**
* 根据where获取count
*/
function getCountByWhere()
{
$sql_count = "SELECT COUNT(*) AS total FROM {$this->getTable()} ";
if ( $this->getWhere() != null ) {
$sql_count .= "WHERE " . $this->getWhere();
}
return $this->query_scalar($sql_count);
}
/**
* 根据主键更新
*/
function updateByPk($entity)
{
$this->setFieldValues($entity);
$sql = "UPDATE {$this->getTable()} SET ";
foreach ($this->getFieldValues() as $key => $one){
if ($one != NULL){
$sql .= "$key='$one',";
}
}
$sql = rtrim($sql, ',');
$sql .= " WHERE {$this->getPrimaryKeyField()}='{$this->getPk()}'";
return $this->execute($sql);
}
/**
* 根据WHERE更新
*/
function updateByWhere($entity)
{
$this->setFieldValues($entity);
$sql = "UPDATE {$this->getTable()} SET ";
foreach ($this->getFieldValues() as $key => $one){
if ($one != NULL){
$sql .= "$key='$one',";
}
}
$sql = rtrim($sql, ',');
$sql .= " {$this->getWhere()}";
return $this->execute($sql);
}
/**
* 根据WHERE更新
*/
function insert_table($entity)
{
$this->setFieldValues($entity);
$sql_values = '';
$sql = "INSERT INTO {$this->getTable()} (";
foreach ($this->getFieldValues() as $key => $one){
if ($one != NULL){
$sql .= "$key,";
$sql_values .= "'$one',";
}
}
$sql = rtrim($sql, ',').") VALUES (".rtrim($sql_values, ',').")";
return $this->execute($sql);
}
//-------------------------Yii2 base----------------------
//-------------------------Yii2 base----------------------
//-------------------------Yii2 base----------------------
//* @author nike@youfumama.com
//* @date 2017-03-08
//* 可扩展
/**
* 只获取一行，如果该查询没有结果则返回 false
* @param type $sql
* @return mix
*/
private function query_one($sql)
{
return $this->_getDb()->createCommand($sql)->queryOne();
}
/**
* 返回所有数组，如果该查询没有结果则返回空数组
* @param type $sql
* @return type
*/
private function query_all($sql)
{
return $this->_getDb()->createCommand($sql)->queryAll();
}
/**
* 返回一个标量值，如果该查询没有结果则返回 false
* @param type $sql
* @return type
*/
private function query_scalar($sql)
{
return $this->_getDb()->createCommand($sql)->queryScalar();
}
/**
* execute操作
* @param type $sql
* @return type
*/
private function execute($sql)
{
return $this->_getDb()->createCommand($sql)->execute();
}
/**
* sql过滤，后期安全扩展
* @param type $str
* @return type
*/
private function cleanString($value = '')
{
if (is_string($value)) {
$value = trim($value);
$value = stripslashes($value);
$value = mysqli_real_escape_string($value);
}
return $value;
}
//-------------------------Yii2 END----------------------
//-------------------------Yii2 END----------------------
//-------------------------Yii2 END----------------------
}
