User: Administrator
Date: 2016/1/15 0015
Time: 下午 4:29
function recordFail($xml)
{
$sql = "INSERT INTO tbl_rp_fail(return_code, return_msg, result_code,err_code,err_code_des,mch_billno,mch_id,wxappid,re_openid,total_amount)VALUES(\r\n'{$xml->return_code}','{$xml->return_msg}','{$xml->result_code}','{$xml->err_code}','{$xml->err_code_des}','{$xml->mch_billno}','{$xml->mch_id}','{$xml->wxappid}','{$xml->re_openid}',{$xml->total_amount}\r\n);";
$sh = new SqlHelper();
$sh->connDataBase(DB_WX_URL, DB_WX_USER, DB_WX_PWD, DB_WX_NAME);
return $sh->modify($sql);
}
function addMessage($sender,$getter,$con) {
//创建SQL语句
$sql="insert into messages (sender,getter,content,sendTime) values('$sender','$getter','$con',now())";
file_put_contents("/Library/WebServer/Documents/ajax/mylog.txt",$sql."\r", FILE_APPEND);
$sqlHelper=new SqlHelper();
return $sqlHelper->execute_dml($sql);
}
public function ChangePassword($username, $oldPassword, $newPassword)
{
$result = false;
$sql = "select password from t_admin where username='******'";
//创建一个SqlHelper对象
$sqlHelper = new SqlHelper();
$res = $sqlHelper->execute_dql($sql);
if ($row = mysql_fetch_assoc($res)) {
//比对密码
if (md5($oldPassword) == $row['password']) {
$sql = "update t_admin set password='******' where username='******'";
if ($sqlHelper->execute_dml($sql) != 0) {
$result = true;
} else {
$this->errMessage = "更新失败！";
}
} else {
$this->errMessage = "原始密码不正确";
}
}
//资源
mysql_free_result($res);
//关闭链接
$sqlHelper->close_connect();
return $result;
}
public function showMessageByPage($fenyePage, $loginname)
{
$sql = "select *from message where getter = '" . $loginname . "'" . "limit " . $fenyePage->pageSize * ($fenyePage->page - 1) . "," . $fenyePage->pageSize;
$sqlHelper = new SqlHelper();
$res = $sqlHelper->execute_dql2($sql);
return $res;
}
public function get_reply($bbs_id)
{
$sqlHelper = new SqlHelper();
$sql = "select * from reply where bbs_id={$bbs_id}";
$res = $sqlHelper->res_array_erwei($sql);
$sqlHelper->close();
return $res;
}
public function get_user_info($sno)
{
$sqlHelper = new SqlHelper();
$sql = "select sno,usernc,truename,email,tel,major,img from student where sno='{$sno}'";
$res = $sqlHelper->res_array($sql);
$sqlHelper->close();
return $res;
}
public function updateEmpUI($id, $name, $grade, $email)
{
$sql = "update emp set name='{$name}',grade={$grade},email='{$email}' where id={$id}";
$sqlHelper = new SqlHelper();
$res = $sqlHelper->execute_dml($sql);
$sqlHelper->close_connect();
return $res;
}
public function getStuListByPage($pageNow, $pageSize)
{
$sql = "select * from student limit " . ($pageNow - 1) * $pageSize . ",{$pageSize}";
$sqlHelper = new SqlHelper();
$res2 = $sqlHelper->execute_dql2($sql);
//free
return $res2;
}
public function get_question_gaicuo($paper_id)
{
$sqlHelper = new SqlHelper();
$sql = "select correct_name,correct_code,true_code from correct where paper_id='{$paper_id}'";
$arr = $sqlHelper->res_array_erwei($sql);
$sqlHelper->close();
return $arr;
}
private function update($gameId, $player_name)
{
$st = new SqlHelper();
$sql = "UPDATE tbl_player SET player_name='{$player_name}',last_login_utc=UNIX_TIMESTAMP(NOW()), last_login_date = NOW() WHERE game_id={$gameId}";
$st->conn();
$result = $st->modify($sql);
$st->close();
return $result;
}
public function insert_correct_question($correct_name, $correct_code, $true_code, $run_answer, $paper_id)
{
$sqlHelper = new SqlHelper();
$correct_code = str_replace("<", "\"", $correct_code);
$correct_code = str_replace(">", "\"", $correct_code);
$sql = "INSERT INTO `weike`.`correct` ( `correct_name`, `correct_code`, `true_code`, `run_answer`, `paper_id`) VALUES ( '{$correct_name}', '{$correct_code}', '{$true_code}', '{$run_answer}', '{$paper_id}')";
$res = $sqlHelper->dml($sql);
return $res;
}
private function record2DB($openid, $qList)
{
$sql = "INSERT INTO tbl_feedback VALUES('{$openid}',NOW(),'{$qList['0']}','{$qList['1']}','{$qList['2']}','{$qList['3']}','{$qList['4']}','{$qList['5']}','{$qList['6']}','{$qList['7']}');";
$st = new SqlHelper();
$st->conn();
$result = $st->modify($sql);
$st->close();
return $result;
}
public function getId($tel)
{
$sqlHelper = new SqlHelper();
$user = new User();
$sql = "select id from user where tel='{$tel}'";
$res = $sqlHelper->dql_arr($sql);
$user->setId($res[0]['id']);
return $user;
}
private function record2DB($openid, $key)
{
$sql = "UPDATE tbl_rbkey SET openid='{$openid}',used_date=NOW() WHERE rb_key ='{$key}' AND openid IS NULL;";
$st = new SqlHelper();
$st->connDataBase(DB_URL, DB_USER, DB_PWD, DB_RBKEY);
$result = $st->modify($sql);
$st->close();
return $result;
}
/**
* @param $fenyePage
*/
function getFenyePage($fenyePage)
{
$sqlhelper = new SqlHelper();
$sql1 = "select * from emp limit " . ($fenyePage->pageNow - 1) * $fenyePage->pageSize . "," . $fenyePage->pageSize;
echo "当前页:" . $fenyePage->pageNow . "<br/>";
echo $sql1;
$sql2 = "select count(id) from emp";
$sqlhelper->exectue_dql_fenye($sql1, $sql2, $fenyePage);
$sqlhelper->close_conn();
}
public function CheckAdmin($id, $password)
{
$sqlHelper = new SqlHelper();
$sql = "select password,name from admin where id={$id}";
$res = $sqlHelper->res_array($sql);
if (md5($password) == $res[0]) {
return $res[1];
exit;
}
$sqlHelper->close();
return "";
}
public function checkAdmin($id, $password)
{
$sql = "select password,name from admin where id={$id}";
$sqlhelper = new SqlHelper();
$res = $sqlhelper->execute_dql($sql);
if ($row = mysqli_fetch_assoc($res)) {
if (md5($password) == $row['password']) {
return true;
}
}
mysqli_free_result($res);
$sqlhelper->close_conn();
return false;
}
public function AdminLogin($username, $password)
{
$sqlHelper = new SqlHelper();
$admin = new Admin();
$sql = "select password,level from admin where username='******'";
$res = $sqlHelper->dql_arr($sql);
if ($res[0]['password'] == md5($password)) {
$admin->setUsername($username);
$admin->setLevel($res[0]['level']);
return $admin;
} else {
return null;
}
}
function getPageCount($pageSize)
{
//查询rowcout
$sql = "select count(id) from brand_wyeth_customer";
$sqlhelper = new SqlHelper();
$res = $sqlhelper->execute_dql($sql);
//计算pagecount
if ($row = mysql_fetch_row()) {
$pagecount = ceil($row[0] / $pageSize);
}
//释放资源关闭链接
mysql_free_result($res);
mysql_close();
return $pagecount;
}
/**
* 生成商户订单号
* @param $openid
*/
function getOrderId($mchBillno)
{
$temp = $mchBillno . date("Ymd");
$sql = "SELECT COUNT(*) AS count FROM tbl_rp_success";
$st = new SqlHelper();
$st->connDataBase(DB_WX_URL, DB_WX_USER, DB_WX_PWD, DB_WX_NAME);
$result = $st->query($sql);
$id = $result[0]['count'];
$count = strlen($id);
for ($i = $count; $i < 10; $i++) {
$temp .= '0';
}
$temp .= $id;
return $temp;
}
/**
* 转义数据
* @param mixed $data
* @param boolean $isQuote 字符串值是否用引号引起来。默认不括起来。
* @return mixed
*/
public static function escape($data, $isQuote = false)
{
if (is_int($data)) {
return strval($data);
} elseif (is_float($data)) {
return strval($data);
} elseif (is_string($data)) {
if ($isQuote) {
# 判断值是否需要 quote 起来
if (SqlHelper::isWrapperNoQuote($data)) {
return addslashes(SqlHelper::UnwrapperNoQuote($data));
} else {
return "'" . addslashes($data) . "'";
}
} else {
return addslashes($data);
}
} elseif (is_bool($data)) {
return strval(intval($data));
} elseif (empty($data)) {
if ($isQuote) {
return "''";
} else {
return '';
}
} elseif (is_array($data)) {
foreach ($data as &$val) {
$val = self::escape($val, $isQuote);
}
return $data;
} else {
return false;
}
}
public function actionSorting()
{
if (isset($_POST['tree'])) {
$model = new Category();
$this->performAjaxValidation($model);
//при сортировке дерева параметры корня измениться не могут,
//поэтоtму его вообще сохранять не будем
$data = json_decode($_POST['tree']);
array_shift($data);
//получаем большие case для update
$update = array();
$nestedSortableFields = array('depth' => Category::DEPTH, 'left' => Category::LFT, 'right' => Category::RGT);
foreach ($nestedSortableFields as $key => $field) {
$update_data = CHtml::listData($data, 'item_id', $key);
$update[] = "{$field} = " . SqlHelper::arrToCase('id', $update_data);
}
//обновляем всю таблицу, кроме рута
$condition = Category::DEPTH . " > 1";
$command = Yii::app()->db->commandBuilder->createSqlCommand("UPDATE `{$model->tableName()}` SET " . implode(', ', $update) . " WHERE {$condition}");
$command->execute();
echo CJSON::encode(array('status' => 'ok', 'redirect' => $this->createUrl('manage')));
Yii::app()->end();
}
$this->render('sorting');
}
public function run()
{
$model = $this->model;
$class = get_class($model);
if (isset($_POST['tree'])) {
$this->controller->performAjaxValidation($model);
//при сортировке дерева параметры корня измениться не могут,
//поэтоtму его вообще сохранять не будем
$data = json_decode($_POST['tree']);
array_shift($data);
//получаем большие case для update
$update = array();
$nestedSortableFields = array('depth' => $class::DEPTH, 'left' => $class::LFT, 'right' => $class::RGT);
foreach ($nestedSortableFields as $key => $field) {
$update_data = CHtml::listData($data, 'item_id', $key);
if ($key == $class::DEPTH) {
foreach ($update_data as $key => $val) {
$update_data[$key]++;
}
}
$update[] = "{$field} = " . SqlHelper::arrToCase('id', $update_data);
}
//обновляем всю таблицу, кроме рута
$condition = $class::DEPTH . " > 1";
$command = Yii::app()->db->createCommand("UPDATE `{$model->tableName()}` SET " . implode(', ', $update) . " WHERE {$condition}");
$command->execute();
$this->controller->forward($this->forwardRoute, true);
}
$this->controller->render('sort', array('model' => $model, 'class' => $class));
}
public function EditEntity($bo)
{
$db = new Database();
$newID = UUID::newID();
$ID = str_replace("sys", "", $bo["EntityName"]) . "id";
if ($bo[$ID] != null) {
$values = SqlHelper::GetUpdates(explode(",", $bo["EntityFields"]), $bo);
$sql = 'UPDATE ' . $bo["EntityName"] . ' SET ' . $values . ' WHERE ' . $ID . ' = "' . $bo[$ID] . '"';
} else {
//
$fields = str_replace($ID . ",", "", $bo["EntityFields"]);
$values = SqlHelper::GetValues(explode(",", $fields), $bo);
$sql = 'INSERT INTO ' . $bo["EntityName"] . '(' . $ID . ',' . $fields . ')' . ' VALUES("' . $newID . '",' . $values . ')';
//table
if ($bo["EntityName"] == 'sysentity') {
$table = ';CREATE TABLE IF NOT EXISTS ' . $bo["Name"] . '(' . $bo["Name"] . 'ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (' . $bo["Name"] . 'ID))ENGINE = InnoDB';
$sql .= $table;
}
if ($bo["EntityName"] == 'sysproperty') {
$result = $db->RunSQL('SELECT Name FROM SysEntity WHERE EntityID = ' . $bo["EntityID"]);
$table = ';ALTER TABLE ' . $result[0]['Name'] . ' ADD COLUMN ' . $bo["Name"] . ' VARCHAR(45) NULL ';
$sql .= $table;
}
}
$result = $db->ExecuteSQL($sql);
return $this->GetEntityView($bo["RefreshEntityViewID"], array("id" => $newID));
}
public function actionSorting($root_id, $menu_id)
{
if (isset($_POST['tree'])) {
$model = new MenuSection();
$this->performAjaxValidation($model);
//при сортировке дерева параметры корня измениться не могут,
//поэтоtму его вообще сохранять не будем
$data = json_decode($_POST['tree']);
array_shift($data);
//получаем большие case для update
$update = [];
$js_to_sql_mapping = ['depth' => 'level', 'left' => 'left', 'right' => 'right'];
foreach ($js_to_sql_mapping as $js_field => $field) {
$update_data = CHtml::listData($data, 'item_id', $js_field);
$update[] = "t.{$field} = " . SqlHelper::arrToCase('id', $update_data, 't');
}
$in = implode(', ', array_values(CHtml::listData($data, 'item_id', 'item_id')));
$condition = "t.level > 1";
$command = Yii::app()->db->commandBuilder->createSqlCommand("UPDATE `{$model->tableName()}` as t SET " . implode(', ', $update) . " WHERE {$condition} AND t.id IN ({$in})");
$command->execute();
echo CJSON::encode(['status' => 'ok', 'redirect' => $this->createUrl('manage', ['menu_id' => $menu_id])]);
Yii::app()->end();
}
$this->render('sorting', ['root_id' => $root_id, 'menu_id' => $menu_id]);
}
public function checkAdmin($id, $password)
{
$sql = "select password,name from userlogin where id={$id}";
$sqlHelper = new SqlHelper();
$res = $sqlHelper->execute_dql($sql);
if ($row = mysql_fetch_assoc($res)) {
if (md5($password) == $row['password']) {
return $row['name'];
}
}
//关闭资源
mysql_free_result($res);
//关闭连接
$sqlHelper->close_connect();
return "";
}
public function checkStudent($sno, $pwd)
{
$arr_sno = array();
$sqlHelper = new SqlHelper();
$sql = "select usernc,pwd,sno from student where sno='{$sno}'";
$arr = $sqlHelper->res_array($sql);
if ($arr[1] == md5($pwd)) {
$arr_sno[0] = $arr[0];
$arr_sno[1] = $arr[2];
return $arr_sno;
//返回用户名
exit;
}
$sqlHelper->close();
return "";
}
public function run()
{
$ids = array_reverse($_POST['File']);
$files = new MediaFile('sort');
$case = SqlHelper::arrToCase('id', array_flip($ids), 't');
$arr = implode(',', $ids);
Yii::app()->db->getCommandBuilder()->createSqlCommand("UPDATE {$files->tableName()} AS t SET t.order = {$case} WHERE t.id IN ({$arr})")->execute();
}
public function EditEntity($bo)
{
$db = new Database();
$newID = UUID::newID();
$propertyID = UUID::newID();
$ID = str_replace("sys", "", $bo["entityname"]) . "id";
$ID_Field = str_replace("sys", "", $bo["name"]) . "id";
//$sql = "START TRANSACTION;";
if ($bo[$ID] != null) {
$values = SqlHelper::GetUpdates(explode(",", $bo["EntityFields"]), $bo);
$sql .= 'UPDATE ' . $bo["entityname"] . ' SET ' . $values . ' WHERE ' . $ID . ' = "' . $bo[$ID] . '"';
//echo $sql;
} else {
//
$fields = str_replace("," . $ID . ",", ",", "," . $bo["EntityFields"] . ",");
$fields = substr($fields, 1, $fields . length - 1);
$values = SqlHelper::GetValues(explode(",", $fields), $bo);
$sql .= 'INSERT INTO ' . $bo["entityname"] . '(' . $ID . ',' . $fields . ') VALUES("' . $newID . '",' . $values . ')';
//echo $sql;
//table
if ($bo["entityname"] == 'sysentity') {
$sql .= ';INSERT INTO sysproperty(propertyid, entityid, name) VALUES("' . $propertyID . '","' . $newID . '", "' . $ID_Field . '")';
$table = ';CREATE TABLE IF NOT EXISTS ' . $bo["name"] . '(' . $ID_Field . ' VARCHAR(100) NOT NULL, ' . ' createddate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ' . 'PRIMARY KEY (' . $ID_Field . '))ENGINE = InnoDB';
$sql .= $table;
//echo $sql;
}
if ($bo["entityname"] == 'sysproperty' && !$bo["isonlyview"]) {
$columnType = ' VARCHAR(255) NULL';
if ($bo["xtype"] == "numberfield") {
$columnType = ' INT NULL';
} else {
if ($bo["xtype"] == "datefield") {
$columnType = ' DATETIME NULL';
} else {
if ($bo["xtype"] == "textareafield") {
$columnType = ' TEXT NULL';
} else {
if ($bo["xtype"] == "checkboxfield") {
$columnType = ' BOOL NULL';
} else {
if ($bo["xtype"] == "htmleditor") {
$columnType = ' TEXT NULL';
}
}
}
}
}
$result = $db->RunSQL('SELECT name FROM sysentity WHERE entityid = "' . $bo["entityid"] . '"');
$table = ';ALTER TABLE ' . $result[0]['name'] . ' ADD COLUMN ' . $bo["name"] . ' ' . $columnType;
$sql .= $table;
}
}
//$sql .= ';COMMIT;';
//echo $sql;
$entity = array("entityViewID" => $bo["RefreshEntityViewID"]);
$result = $db->ExecuteSQL($sql);
return $this->GetEntityView($entity);
}
function QueryAllNation()
{
$sql = "select nationId,nationName from t_Nation";
$sqlHelper = new SqlHelper();
$res = $sqlHelper->execute_dql2($sql);
// 关闭连接
$sqlHelper->close_connect();
return $res;
}