CompassHandler.inc

<?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;
	}
}

?>