<?php
/**
* fishow共通
* $Id: CompassHandler.inc,v 1.1 2015/10/08 13:34:45 g_wang Exp $
* @author $Author: g_wang $
* @access public
* @package jp.fishow.handler
*/
class CompassHandler{
/**
* API请求时发生错误
*/
public static function responseApiNg($error_code = 0, $error_message = null) {
$arr = ["status"=>"NG", "error_code"=>$error_code, "error_message"=>$error_message];
echo json_encode($arr);
exit;
}
/**
* API请求时返回正常结果
*/
public static function responseApiOk($result = []) {
$arr = ["status"=>"OK", "data"=>$result];
echo json_encode($arr);
exit;
}
/**
* 图片显示URL取得
* child_path(不包含"/")
*/
public static function getImageUrl($file_name, $type, $child_path) {
$file_path = IMAGES_PATH;
if ($type == "theme") {
$file_path = THEME_IMAGES_PATH;
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
} else if ($type == "article") {
$file_path = ARTICLE_IMAGES_PATH;
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
} else if ($type == "category") {
$file_path = ARTICLE_IMAGES_PATH;
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
} else if ($type == "course") {
$file_path = COURSE_IMAGES_PATH;
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
} else if ($type == "video") {
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
} else {
if(!empty($child_path)) {
$file_path .= "/" . $child_path;
}
$file_path .= "/" . $file_name;
}
$url = "display_image.php?type={type}&child_path={child_path}&file_name={file_name}";
$url = str_replace("{child_path}", $child_path, $url);
$url = str_replace("{file_name}", $file_name, $url);
$url = str_replace("{type}", $type, $url);
return $url;
}
/**
* 获取图集的目录下文件列表
*/
public static function getArticleImages($articleDat) {
$resultArray = array();
if(empty($articleDat) || empty($articleDat->image_path)) {
return $resultArray;
}
$image_full_path = IMAGES_PATH . "/" . $articleDat->image_path;
if(!is_dir($image_full_path)) {
return $resultArray;
}
//遍历读取列表
if($dh = @opendir($image_full_path)){
//读取
while(($file = readdir($dh)) !== false){
if($file != '.' && $file != '..'){
$resultArray[] = $file;
}
}
//关闭
closedir($dh);
}
return $resultArray;
}
/**
* 获取图集的目录下文件列表图片数量
*/
public static function getArticleImagesCount($articleDat) {
$resultCount = 0;
if(empty($articleDat) || empty($articleDat->image_path)) {
return $resultCount;
}
$image_full_path = IMAGES_PATH . "/" . $articleDat->image_path;
if(!is_dir($image_full_path)) {
return $resultCount;
}
//遍历读取列表
if($dh = @opendir($image_full_path)){
//读取
while(($file = readdir($dh)) !== false){
if($file != '.' && $file != '..'){
$resultCount++;
}
}
//关闭
closedir($dh);
}
return $resultCount;
}
/**
* 获取网络图片并保存
*/
public static function grabImage($url, $filename = "") {
//如果$url地址为空，直接退出
if(empty($url)){
return false;
}
//如果没有指定新的文件名
if(empty($filename)){
return false;
}
ob_start();//打开输出
//输出图片文件
if(!readfile($url)) {
return false;
}
$img = ob_get_contents();//得到浏览器输出
ob_end_clean();//清除输出并关闭
$size = strlen($img);//得到图片大小
$fp2 = @fopen($filename, "a");
fwrite($fp2, $img);//向当前目录写入图片文件，并重新命名
fclose($fp2);
return true;
}
/**
* 获取去重后的解梦关键词列表
*/
public static function getUniqueDreamListByCategoryId($categoty_id, $offset, $limit) {
$db = &CompassDBManager::getInstance();
$sql = "select distinct category_id, dream_id, title from dream_dat where delete_flg=false";
if(isset($categoty_id)) {
$sql .= " and category_id=".$categoty_id;
}
if(isset($limit)) {
$sql .= " limit ".$limit;
}
if(isset($offset)) {
$sql .= " offset ".$offset;
}
$result = $db->executeQuery($sql);
if(count($result) > 0){
return $result;
}
return array();
}
/**
* 获取单个广告合计后的去重点击数
*/
public static function getSumUniqueAdClickCount($adid, $object_date) {
$db = &CompassDBManager::getInstance();
$sql = "select sum(unique_click_count) as count from miniplay_ad_click_dat where delete_flg=false and channel_id<>'0' and adid='" . $adid . "' and object_date='" . $object_date . "'";
$result = $db->executeQuery($sql);
if(count($result) > 0){
return $result[0]["count"];
}
return 0;
}
/**
* 插入用户点数获得数据【openid】
*/
public static function insertPointLog($openid, $action_type, $point, $status) {
if(empty($openid) || $openid=="undefined") return;
$db = &CompassDBManager::getInstance();
$insert_sql = "insert into miniplay_user_point_log (openid, action_type, point, status) VALUES ('{$openid}', '{$action_type}','{$point}','{$status}')";
$db->executeQuery($insert_sql);
//更新该用户的累计点数
if($status=="SUCCESS") {
$point_val = (int)$point;
if($point_val > 0) {
$update_sql = "update user_mst set point=point+{$point_val}, sum_point=sum_point+{$point} where openid='{$openid}' and delete_flg=false";
} else {
$point_val2 = abs($point_val);
$update_sql = "update user_mst set point=point-{$point_val2} where openid='{$openid}' and delete_flg=false";
}
$db->executeQuery($update_sql);
}
return;
}
/**
* 插入用户点数获得数据【openid】
*/
public static function insertPointLogByUID($uid, $action_type, $point, $status) {
if(empty($uid) || $uid=="undefined") return;
$db = &CompassDBManager::getInstance();
$insert_sql = "insert into miniplay_user_point_log (openid, action_type, point, status) VALUES ('{$uid}', '{$action_type}','{$point}','{$status}')";
$db->executeQuery($insert_sql);
//更新该用户的累计点数
if($status=="SUCCESS") {
//判断记录是否存在
$param = array();
$param["union_id"] = $uid;
$param["delete_flg"] = false;
$tmp_list = UnionUserDat::getList($param, "id", "desc", 0 , 1);
if(empty($tmp_list)) {
$union_user_dat = new UnionUserDat();
$union_user_dat->union_id = $uid;
$union_user_dat->point = 0;
$union_user_dat->sum_point = 0;
} else {
$union_user_dat = $tmp_list[0];
}
$point_val = (int)$point;
if($point_val > 0) {
$union_user_dat->point += $point_val;
$union_user_dat->sum_point += $point_val;
} else {
$point_val2 = abs($point_val);
$union_user_dat->point -= $point_val;
}
$union_user_dat->save();
}
return;
}
/**
* 插入用户点数获得数据
*/
public static function insertOctopusPointLog($openid, $action_type, $group_id, $point) {
if(empty($openid) || $openid=="undefined") return;
$db = &CompassDBManager::getInstance();
$insert_sql = "insert into octopus_user_point_log (openid, action_type, group_id, point) VALUES ('{$openid}', '{$action_type}','{$group_id}','{$point}')";
$db->executeQuery($insert_sql);
//更新该用户的累计点数
$point_val = (int)$point;
if($point_val > 0) {
$update_sql = "update user_mst set point=point+{$point_val}, sum_point=sum_point+{$point} where openid='{$openid}' and delete_flg=false";
} else {
$point_val2 = abs($point_val);
$update_sql = "update user_mst set point=point-{$point_val2} where openid='{$openid}' and delete_flg=false";
}
$db->executeQuery($update_sql);
return;
}
/**
* 渠道广告点击记录
*/
public static function insertTrafficLog($channel_id, $openid, $adid) {
if(empty($openid) || $openid=="undefined") return;
$db = &CompassDBManager::getInstance();
$insert_sql = "insert into miniplay_traffic_click_log (channel_id, openid, adid) VALUES ('{$channel_id}', '{$openid}','{$adid}')";
$db->executeQuery($insert_sql);
return;
}
/**
* 下线广告处理
*/
public static function offlineAd($adDat) {
if(empty($adDat)) return;
//该广告对应的ad_plan下线处理
$param = array();
$param["delete_flg"] = false;
$param["adid"] = $adDat->id;
$param["is_finish"] = false;
$ad_plan_list = MiniplayAdPlan::getList($param);
if(!empty($ad_plan_list)) {
foreach($ad_plan_list as $ad_plan) {
$ad_plan->is_finish = true;
$ad_plan->save();
}
}
//渠道广告配置下线
$param = array();
$param["delete_flg"] = false;
$param["adid"] = $adDat->id;
$promotion_dat_list = MiniplayPromotionDat::getList($param);
if(!empty($promotion_dat_list)) {
foreach($promotion_dat_list as $promotion_dat) {
$promotion_dat->delete_flg = true;
$promotion_dat->save();
}
}
//广告下线
$adDat->is_top = false;
if($adDat->status == "OK") {
$adDat->status = "NG";
$adDat->save();
}
//渠道配置广告下线处理
return;
}
/**
* Cookies设定
* @param string $name
* @param string $value
* @param int $expire
* @return empty
*/
public static function setCookies($name, $value, $expire = 0) {
setcookie($name, $value, $expire, "/");
}
/**
* Cookies取得
* @param string $name
* @return string
*/
public static function getCookies($name) {
$cookies_value = null;
if (isset($_COOKIE[$name]) && $_COOKIE[$name] != null) {
$cookies_value = $_COOKIE[$name];
}
return $cookies_value;
}
/**
* Cookies清除
* @param string $name
* @return null
*/
public static function clearCookies($name) {
if (isset($_COOKIE[$name]) && $_COOKIE[$name] != null) {
setcookie($name, "", time() - 3600, "/");
}
return null;
}
/**
* CURLでＨｔｔｐ請求
* @param unknown $url
*/
public static function getCurl($url) {
$ch = curl_init();
$timeout = 5;
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
$file_contents = curl_exec($ch);
curl_close($ch);
return $file_contents;
}
/**
* 对象转数组
*/
public static function object2array($object) {
if (is_object($object)) {
foreach ($object as $key => $value) {
$array[$key] = $value;
}
}
else {
$array = $object;
}
return $array;
}
/**
* 排序专用callback
*/
public static function callbackCmpPriority($a, $b) {
return $a["idx"] < $b["idx"] ? true : false;
}
/**
* 新增account时使用的学校list
*/
public static function getSchoolListForAccount() {
$sql = "select id,title from school_mst where delete_flg = false order by id asc";
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($sql);
return $result;
}
/**
* 新增account时使用的领过机构list
*/
public static function getGovernmentlListForAccount() {
$sql = "select id,title from government_mst where delete_flg = false order by id asc";
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($sql);
return $result;
}
/**
* 根据条件查询account list
*/
public static function getAccountlList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from account_mst";
$sql_where =" where delete_flg=false";
if (isset($param["user_id"]) && !empty($param["user_id"])) {
$sql_where .= " and user_id = ".$param["user_id"];
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new AccountMst($tmp));
}
}
return $return;
}
/**
* 根据条件查询government list
*/
public static function getGovernmentlList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from government_mst";
$sql_where =" where delete_flg=false";
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new GovernmentMst($tmp));
}
}
return $return;
}
/**
* 根据条件查询school list count
*/
public static function getSchoollListCount($param) {
$from = "select count(*) count from school_mst";
$sql_where =" where delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and title like '%".$param["title"] . "%'";
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询school list
*/
public static function getSchoollList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from school_mst";
$sql_where =" where delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and title like '%".$param["title"] . "%'";
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new SchoolMst($tmp));
}
}
return $return;
}
/**
* 根据条件查询班级 list
*/
public static function getClassList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select cm.*,gm.title as grade_title from class_mst cm left join grade_mst gm on cm.grade_id=gm.id";
$sql_where =" where cm.delete_flg=false";
if (isset($param["school_id"]) && !empty($param["school_id"])) {
$sql_where .= " and cm.school_id=".$param["school_id"];
}
$sql_where.=" order by ".$order_key.' '.$sort;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 根据条件查询合集视频 list
*/
public static function getCourseListCount($param) {
$from = "select count(*) count from course_mst cm left join";
$from .= " (select course_id,max(registration_date) registration_date,sum(view_count) view_count from course_media_dat where delete_flg = false and course_id <> 0 group by course_id) cmd on cm.id=cmd.course_id";
$sql_where =" where cm.delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and cm.title like '%" . $param["title"] . "%'";
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询合集视频 list
*/
public static function getCourseList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select cm.*,cmd.registration_date as upload_date,cmd.view_count as view_count from course_mst cm left join ";
$from .= " (select course_id,max(registration_date) registration_date,sum(view_count) view_count from course_media_dat where delete_flg = false and course_id <> 0 group by course_id) cmd on cm.id=cmd.course_id";
$sql_where =" where cm.delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and cm.title like '%" . $param["title"] . "%'";
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 根据条件查询视频详细 count
*/
public static function getCourseMediaListCount($param) {
$from = "select count(*) count from course_media_dat";
$sql_where =" where delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and title like '%" . $param["title"] . "%'";
}
if (isset($param["course_id"]) && !empty($param["course_id"])) {
$sql_where .= " and course_id=" . $param["course_id"];
}
if (isset($param["media_type"])) {
if($param["media_type"] == "1"){
$sql_where .= " and course_id = 0";
}else if($param["media_type"] == "2"){
$sql_where .= " and course_id <> 0";
}
}
if (isset($param["media_kind"])) {
if($param["media_kind"] == "1"){
$sql_where .= " and is_free = true";
}else if($param["media_kind"] == "2"){
$sql_where .= " and is_free = false";
}
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询视频详细 list
*/
public static function getCourseMediaList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from course_media_dat";
$sql_where =" where delete_flg=false";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and title like '%" . $param["title"] . "%'";
}
if (isset($param["course_id"]) && !empty($param["course_id"])) {
$sql_where .= " and course_id=" . $param["course_id"];
}
if (isset($param["media_type"])) {
if($param["media_type"] == "1"){
$sql_where .= " and course_id = 0";
}else if($param["media_type"] == "2"){
$sql_where .= " and course_id <> 0";
}
}
if (isset($param["media_kind"])) {
if($param["media_kind"] == "1"){
$sql_where .= " and is_free = true";
}else if($param["media_kind"] == "2"){
$sql_where .= " and is_free = false";
}
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new CourseMediaDat($tmp));
}
}
return $return;
}
/**
* 根据条件查询certificate list
*/
public static function getCertificatelList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from certificate_mst";
$sql_where =" where delete_flg=false";
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new CertificateMst($tmp));
}
}
return $return;
}
/**
* 根据条件查询user_certificate list count
*/
public static function getUserCertificateListCount($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select ucm.* from user_certificate_dat ucm left join user_mst um on ucm.user_id=um.id left join certificate_mst cm on ucm.certificate_id=um.id";
$sql_where =" where ucm.delete_flg=false";
if (isset($param["user_name"]) && !empty($param["user_name"])) {
$sql_where .= " and um.name like '%" . $param["user_name"] . "%'";
}
if (isset($param["certificate_title"]) && !empty($param["certificate_title"])) {
$sql_where .= " and cm.title like '%" . $param["certificate_title"] . "%'";
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and ucm.name like '%" . $param["name"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and ucm.status='" . $param["status"] . "'";
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return count($result);
}
/**
* 根据条件查询user_certificate list
*/
public static function getUserCertificateList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select ucm.*,um.name user_name,cm.title certificate_title from user_certificate_dat ucm left join user_mst um on ucm.user_id=um.id left join certificate_mst cm on ucm.certificate_id=um.id";
$sql_where =" where ucm.delete_flg=false";
if (isset($param["user_name"]) && !empty($param["user_name"])) {
$sql_where .= " and um.name like '%" . $param["user_name"] . "%'";
}
if (isset($param["certificate_title"]) && !empty($param["certificate_title"])) {
$sql_where .= " and cm.title like '%" . $param["certificate_title"] . "%'";
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and ucm.name like '%" . $param["name"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and ucm.status='" . $param["status"] . "'";
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 根据id查询user_certificate_dat
*/
public static function getUserCertificateById($id) {
$from = "select ucm.*,um.name user_name,cm.title certificate_title from user_certificate_dat ucm left join user_mst um on ucm.user_id=um.id left join certificate_mst cm on ucm.certificate_id=um.id";
$sql_where =" where ucm.delete_flg=false and ucm.id=".$id;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0];
}
/**
* 获取收费视频收益
*/
public static function getProceedsMedia() {
$from = "select count(id) as total,sum(view_count) as view_total from course_media_dat ";
$sql_where =" where delete_flg=false and is_free=false";
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0];
}
/**
* 获取收费视频收益
*/
public static function getProceeds() {
$from = "select sum(money) as money_total from user_media_dat ";
$sql_where =" where delete_flg=false and is_free=false and status='SUCCESS'";
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0];
}
/**
* 获取收费视频一览
*/
public static function getProceedsMediaList($param = null, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select umd.id,umd.money,umd.registration_date,cmd.title,um.name from user_media_dat umd left join course_media_dat cmd on umd.media_id = cmd.id left join user_mst um on umd.user_id = um.id";
$sql_where =" where umd.delete_flg=false and umd.is_free=false and umd.status='SUCCESS'";
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 获取收费视频一览
*/
public static function getProceedsMediaListCount() {
$from = "select count(*) as count from user_media_dat umd left join course_media_dat cmd on umd.media_id = cmd.id left join user_mst um on umd.user_id = um.id";
$sql_where =" where umd.delete_flg=false and umd.is_free=false and umd.status='SUCCESS'";
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询机构认证 count
*/
public static function getOrgAccreditedListCount($param) {
$from = "select count(*) count from user_mst";
$sql_where =" where delete_flg=false and organization_no is not null";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and organization_title like '%" . $param["title"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and organization_status='" . $param["status"] . "'";
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询机构认证list
*/
public static function getOrgAccreditedList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select * from user_mst";
$sql_where =" where delete_flg=false and role=6";
if (isset($param["title"]) && !empty($param["title"])) {
$sql_where .= " and organization_title like '%" . $param["title"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and organization_status='" . $param["status"] . "'";
}
$sql_where.=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
$return = [];
if(count($result) > 0){
foreach ($result as $tmp){
array_push($return, new UserMst($tmp));
}
}
return $return;
}
/**
* 根据条件查询志愿者活动list
*/
public static function getEventList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select ved.*,vemd.involved_count,vemd.finish_count from volunteer_event_dat ved left join ";
$from .= "(select volunteer_event_id,count(volunteer_event_id) as involved_count,sum(CASE WHEN status='FINISH' THEN 1 else 0 END) as finish_count from volunteer_event_member_dat where delete_flg=false group by volunteer_event_id) vemd on ved.id=vemd.volunteer_event_id";
$sql_where =" where ved.delete_flg=false";
$sql_where .=" order by ".$order_key.' '.$sort.' limit '.$limit.' OFFSET '.$offset;
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 根据条件查询活动列表-数据详情list count
*/
public static function getEventMemberListCount($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select count(*) count from volunteer_event_member_dat vemd";
$from .= " join user_mst um on um.id=vemd.user_id and um.delete_flg=false";
$from .= " left join circle_dat cd on cd.id=vemd.circle_id and cd.delete_flg=false";
$sql_where =" where vemd.delete_flg=false";
if (isset($param["id"]) && !empty($param["id"])) {
$sql_where .= " and vemd.volunteer_event_id=" . $param["id"];
}
if (isset($param["mobile"]) && !empty($param["mobile"])) {
$sql_where .= " and vemd.mobile='" . $param["mobile"] . "'";
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and um.name like '%" . $param["name"] . "%'";
}
if (isset($param["is_circle"]) && !empty($param["is_circle"])) {
if($param["is_circle"] == 1){
$sql_where .= " and vemd.circle_id != 0";
}else if($param["is_circle"] == 2){
$sql_where .= " and vemd.circle_id = 0";
}
}
if (isset($param["is_finish"]) && !empty($param["is_finish"])) {
if($param["is_finish"] == 1){
$sql_where .= " and vemd.finish_time is not null";
}elseif($param["is_finish"] == 2){
$sql_where .= " and vemd.finish_time is null";
}
}
if (isset($param["circle_id"]) && !empty($param["circle_id"])) {
$sql_where .= " and vemd.circle_id=" . $param["circle_id"];
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询活动列表-数据详情list
*/
public static function getEventMemberList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select vemd.*,um.name as user_name,cd.title as circle_title from volunteer_event_member_dat vemd";
$from .= " join user_mst um on um.id=vemd.user_id and um.delete_flg=false";
$from .= " left join circle_dat cd on cd.id=vemd.circle_id and cd.delete_flg=false";
$sql_where =" where vemd.delete_flg=false";
if (isset($param["id"]) && !empty($param["id"])) {
$sql_where .= " and vemd.volunteer_event_id=" . $param["id"];
}
if (isset($param["member_id"]) && !empty($param["member_id"])) {
$sql_where .= " and vemd.id=" . $param["member_id"];
}
if (isset($param["mobile"]) && !empty($param["mobile"])) {
$sql_where .= " and vemd.mobile='" . $param["mobile"] . "'";
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and um.name like '%" . $param["name"] . "%'";
}
if (isset($param["is_circle"]) && !empty($param["is_circle"])) {
if($param["is_circle"] == 1){
$sql_where .= " and vemd.circle_id != 0";
}else if($param["is_circle"] == 2){
$sql_where .= " and vemd.circle_id = 0";
}
}
if (isset($param["is_finish"]) && !empty($param["is_finish"])) {
if($param["is_finish"] == 1){
$sql_where .= " and vemd.finish_time is not null";
}elseif($param["is_finish"] == 2){
$sql_where .= " and vemd.finish_time is null";
}
}
if (isset($param["circle_id"]) && !empty($param["circle_id"])) {
$sql_where .= " and vemd.circle_id=" . $param["circle_id"];
}
$sql_where.=" order by ".$order_key.' '.$sort;
if(isset($limit)){
$sql_where.= ' limit '.$limit;
}
if(isset($offset)){
$sql_where.= ' OFFSET '.$offset;
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
/**
* 根据条件查询募捐活动列表-数据详情list count
*/
public static function getUserDonationListCount($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select count(*) count from user_donation_dat udd";
$from .= " left join user_mst um on um.id=udd.user_id and um.delete_flg=false";
$sql_where =" where udd.delete_flg=false";
if (isset($param["id"]) && !empty($param["id"])) {
$sql_where .= " and udd.donation_id=" . $param["id"];
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and um.name like '%" . $param["name"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and udd.status in('" . implode("','", explode(",", $param["status"])). "')";
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result[0]["count"];
}
/**
* 根据条件查询活动列表-数据详情list
*/
public static function getUserDonationList($param, $order_key = "id", $sort = "DESC", $offset = null, $limit = null) {
$from = "select udd.*,um.name as user_name from user_donation_dat udd";
$from .= " left join user_mst um on um.id=udd.user_id and um.delete_flg=false";
$sql_where =" where udd.delete_flg=false";
if (isset($param["id"]) && !empty($param["id"])) {
$sql_where .= " and udd.donation_id=" . $param["id"];
}
if (isset($param["name"]) && !empty($param["name"])) {
$sql_where .= " and um.name like '%" . $param["name"] . "%'";
}
if (isset($param["status"]) && !empty($param["status"])) {
$sql_where .= " and udd.status in('" . implode("','", explode(",", $param["status"])). "')";
}
$sql_where.=" order by ".$order_key.' '.$sort;
if(isset($limit)){
$sql_where.= ' limit '.$limit;
}
if(isset($offset)){
$sql_where.= ' OFFSET '.$offset;
}
// 検索を行う
$db = &CompassDBManager::getInstance();
$result = $db->executeQuery($from.$sql_where);
return $result;
}
}
?>