核心运营报表无线端数据，pv，uv相关数据，从9月1号开始就没了，为什么？
从获取数据的api的地址可以看出: http://data.51buy.com/json.php?biz=statistic&mod=OrderKeyData&act=getData2
相关文件是：
<?php require_once(STATISTIC_ROOT . 'etc/inecfg.inc.php'); require_once(STATISTIC_ROOT . 'api/OrderKeyData.class.php'); function CheckPrivilege($funcName){ switch (strtolower($funcName)) { case 'orderkeydata_getwarehouses': return true; case 'orderkeydata_getcitys'; return true; default: return 'orderkeydata2'; } } function OrderKeyData_getWarehouses() { return OrderKeyData::getWarehouses(); } function OrderKeyData_getJDWarehouses() { return OrderKeyData::getJDWarehouses(); } function OrderKeyData_getCitys() { return OrderKeyData::getCitys(); } function OrderKeyData_getGraph() { $dimension = $_REQUEST['dimension']; $stock_dimension = $_REQUEST['stock_dimension']; $siteIds = $_REQUEST['site']; $stockIds = $_REQUEST['stocksId']; $userType = $_REQUEST['user_type']; $province = $_REQUEST['province']; $city = $_REQUEST['city']; $area = $_REQUEST['area']; $start = $_REQUEST['start']; $end = $_REQUEST['end']; $type = $_REQUEST['type']; //趋势图,前4名省份 $provinceIds = @$_REQUEST['provinceIds']; //站维度的趋势图 if ($type == "site_line") { $area = OrderKeyData::getAreaLinebySite($siteIds, $provinceIds, $start, $end, $userType); $other = OrderKeyData::getAreaLineOtherbySite($siteIds, $provinceIds, $start, $end, $userType); return array("area" => $area, "other" => $other, "type" => "site"); } if ($area != null && $area > -1) { $province_out_area = " and province_id_out={$province} and city_id_out={$city} and distrinct_id_out = ${area}"; } else if ($city != null && $city > -1) { $province_out_area = " and province_id_out={$province} and city_id_out={$city}"; } else if ($province != null & $province > -1) { $province_out_area = " and province_id_out={$province}"; } else { $province_out_area = ""; } //仓维度的趋势图 if ($type == "stock_line") { $data = OrderKeyData::getAreaLineByStock($stockIds, $provinceIds, $start, $end, $userType, $province_out_area); return array("data" => $data, "type" => "stock"); } //无线趋势图 仓 if ($type == "wireless_line") { $wap_type = @$_REQUEST['user_type']; //mark $data = OrderKeyData::getAreaLineByWireless($stockIds, $provinceIds, $start, $end, $userType, $province_out_area, $wap_type); return array("data" => $data, "type" => "wireless"); } } class Param { public static $value = array(); public static function get($k, $default = "") { return isset(self::$value[$k]) ? self::$value[$k] : $default; } public static function set($k, $v) { self::$value[$k] = $v; } public static function show() { print_r(self::$value); } } function getRequest() { foreach ($_REQUEST as $k => $v) { Param::set($k, $v); } // request 接口表 // dimension //统计维度 1:网站; 0:无线 // stock_dimension //仓站选择, 1:站维度; 2:仓位度 // site //分站IDs // stocksId //仓IDs // user_type //用户类型 // province //省id // city //市id // area //区域id // start //开始时间 // end //结束时间 // wap_type //无线类型 // type //请求的逻辑类型 } function OrderKeyData_getTrend() { getRequest(); $type = Param::get("type"); $wap_type = Param::get("wap_type"); $user_type = Param::get("user_type"); $start = Param::get("start"); $end = Param::get("end"); $field = Param::get("field"); $stock_id = Param::get("stocksId"); $site_id = Param::get("site"); if ($type == "web_site") { $province_NO4_ids = array(); $pieData = OrderKeyData::getPieDataByWebSite($site_id, $start, $end, $user_type, $field = "pv"); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendByWebSite($site_id, $start, $end, $user_type, $province_NO4_ids); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } if ($type == "web_stock") { $province_NO4_ids = array(); $pieData = OrderKeyData::getPieDataByWebStock($stock_id, $start, $end, $user_type); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id_site']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendByWebStock($stock_id, $start, $end, $user_type, $province_NO4_ids); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } if ($type == "wireless_site") { $province_NO4_ids = array(); $pieData = OrderKeyData::getPieDataByWirelessBySite($start, $end, $wap_type, $user_type, $field); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id_site']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendByWirelessBySite($start, $end, $wap_type, $user_type, $province_NO4_ids); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } if ($type == "wireless_stock") { $pieData = OrderKeyData::getPieDataByWirelessByStock($start, $end, $wap_type, $user_type, $field, $stock_id); $province_NO4_ids = array(); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id_site']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendByWirelessByStock($start, $end, $wap_type, $user_type, $province_NO4_ids, $stock_id); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } if ($type == "webwireless_site") { $pieData = OrderKeyData::getPieBywebwireless_site($start, $end, $user_type, $field); $province_NO4_ids = array(); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendBywebwireless_site($start,$end,$user_type,$province_NO4_ids); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } if ($type == "webwireless_stock") { $areaWhere = out_area_where(); $pieData = OrderKeyData::getPieBywebwireless_stock($start, $end, $user_type, $field,$areaWhere,$stock_id); $province_NO4_ids = array(); foreach ($pieData as $k => $v) { array_push($province_NO4_ids, $v['province_id_site']); } $province_NO4_ids = implode(",", $province_NO4_ids); $trendData = OrderKeyData::getTrendBywebwireless_stock($start,$end,$user_type,$province_NO4_ids,$areaWhere,$stock_id); return ajax_return(array("pieData" => $pieData, "trendData" => $trendData)); } } function out_area_where(){ $province = Param::get('province'); $city = Param::get('city'); $area = Param::get('area'); $areaWhere = ""; if ($area != null && $area > -1) { $areaWhere = " and province_id_out={$province} and city_id_out={$city} and distrinct_id_out = ${area}"; } else if ($city != null && $city > -1) { $areaWhere = " and province_id_out={$province} and city_id_out={$city}"; } else if ($province != null & $province > -1) { $areaWhere = " and province_id_out={$province}"; } return $areaWhere; } function OrderKeyData_getData2() { getRequest(); $dimension = Param::get('dimension'); $stock_dimension = Param::get('stock_dimension'); $start = Param::get('start'); $end = Param::get('end'); $wap_type = Param::get('wap_type'); $user_type = Param::get('user_type'); $stocksId = Param::get('stocksId'); $site_id = Param::get('site'); $province = Param::get('province'); $city = Param::get('city'); $area = Param::get('area'); $areaWhere = ""; if ($area != null && $area > -1) { $areaWhere = " and province_id_out={$province} and city_id_out={$city} and distrinct_id_out = ${area}"; } else if ($city != null && $city > -1) { $areaWhere = " and province_id_out={$province} and city_id_out={$city}"; } else if ($province != null & $province > -1) { $areaWhere = " and province_id_out={$province}"; } if ($dimension == 1 && $stock_dimension == 1) { // 网站统计 站 if ($user_type == "1,2") { $user_type = "0"; } echo '11'; $detail = OrderKeyData::getTimeBySite($start, $end, $user_type, $site_id); echo '22'; $sum = OrderKeyData::sumData($start, $end, $user_type, $site_id); $area = OrderKeyData::getAreaBySite($start, $end, $user_type, $site_id); return ajax_return(array("detail" => $detail, "sum" => $sum, "area" => $area)); } if ($dimension == "1" && $stock_dimension == "2") { // 网站统计 仓 $sum = OrderKeyData::sumData2($start, $end, $user_type, $stocksId, $areaWhere); $detail = OrderKeyData::getTimeByStock($start, $end, $user_type, $stocksId, $areaWhere); $area = OrderKeyData::getAreaByStock($start, $end, $user_type, $stocksId, $areaWhere); return ajax_return(array("detail" => $detail, "sum" => $sum, "area" => $area)); } if ($dimension == "0" && $stock_dimension == "2") { // 无线统计 仓维度 $detail = OrderKeyData::getTimeByWireless($areaWhere, $user_type, $start, $end, $stocksId, $wap_type); $sum = OrderKeyData::sumData3($areaWhere, $user_type, $start, $end, $stocksId, $wap_type); $area = OrderKeyData::getAreaByWireless($areaWhere, $user_type, $stocksId, $start, $end, $wap_type); return ajax_return(array("detail" => $detail, "sum" => $sum, "area" => $area)); } if ($dimension === "0" && $stock_dimension == "1") { // 无线统计 站维度 $ret = OrderKeyData::getTimeByWirelessBySite($start, $end, $wap_type, $user_type); $sum = OrderKeyData::getSumByWirelessBySite($start, $end, $wap_type, $user_type); $area = OrderKeyData::getAreaByWirelessBySite($start, $end, $wap_type, $user_type); return ajax_return(array("detail" => $ret, "sum" => $sum, "area" => $area)); } if ($dimension =="all" && $stock_dimension == "1") { // 全部 站维度 $time = OrderKeyData::getTimeBywebwireless_site($start,$end,$user_type); // var_dump($time);exit; $sum = array_shift($time); $area = OrderKeyData::getAreaBywebwireless_site($start,$end,$user_type); return ajax_return(array("detail" => $time, "sum" => $sum, "area" => $area)); } if ($dimension =="all" && $stock_dimension == "2") { // 全部 仓维度 $time = OrderKeyData::getTimeBywebwireless_stock($start,$end,$user_type,$stocksId,$areaWhere); $area = OrderKeyData::getAreaBywebwireless_stock($start,$end,$user_type,$stocksId,$areaWhere); $sum = OrderKeyData::getSumBywebwireless_stock($start,$end,$user_type,$stocksId,$areaWhere); return ajax_return(array("detail" => $time,"sum"=>$sum, "area" => $area)); } } function OrderKeyData_download() { getRequest(); $data = Param::get('data'); $fileName = Param::get('name'); $columns = Param::get('columns'); $type = Param::get('type'); $data = mb_convert_encoding($data, "utf-8", "gb2312"); $columns = mb_convert_encoding($columns, "utf-8", "gb2312"); $fileName = mb_convert_encoding($fileName, "utf-8", "gb2312"); $data_arr = json_decode($data, true); $columns = json_decode($columns, true); // php生成excel报表，是通过发送header()头信息完成的 header("Content-Type: application/vnd.ms-excel; charset=gbk"); // header("Content-Type: application/vnd.ms-excel; charset=utf-8"); //告知浏览器文件名称，并要求客户端下载 header("Content-Disposition:filename=$fileName.xls"); header("Pragma: no-cache"); header("Expires: 0"); //列头 foreach ($columns as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; //content if ($type == 'web_site_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { echo "$v "; } echo " "; } } if ($type == 'web_site_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'web_stock_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { echo "$v "; } echo " "; } } if ($type == 'web_stock_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { if ($k == 'id') { continue; } if ($k == 'fid') { if ($v == null) { $v = '是'; } else { $v = '否'; } echo "$v "; continue; } $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'wireless_site_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { echo "$v "; } echo " "; } } if ($type == 'wireless_site_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { if ($k == 'id') { continue; } if ($k == 'fid') { if ($v == null) { $v = '是'; } else { $v = '否'; } echo "$v "; continue; } $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'wireless_stock_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { echo "$v "; } echo " "; } } if ($type == 'wireless_stock_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { if ($k == 'id') { continue; } if ($k == 'fid') { if ($v == null) { $v = '是'; } else { $v = '否'; } echo "$v "; continue; } $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'webwireless_site_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'webwireless_site_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'webwireless_stock_time') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } if ($type == 'webwireless_stock_area') { foreach ($data_arr as $value) { foreach ($value as $k => $v) { if ($k == 'fid') { continue; } $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } } exit; } //日报 function OrderKeyData_getDaily() { getRequest(); $siteIds = $_REQUEST['site']; $stock_id = $_REQUEST['stocksId']; $user_type = $_REQUEST['user_type']; $province = $_REQUEST['province']; $city = $_REQUEST['city']; $area = $_REQUEST['area']; $start = $_REQUEST['start']; $end = $_REQUEST['end']; $type = $_REQUEST['type']; if ($type == 'web_site') { if ($user_type == "0,1,2") { $user_type = "0"; } $id = Param::get('id'); $fid = Param::get('fid'); if (empty($fid) || $fid == "null") { $province = substr($id, 1); $areaWhere = "AND province_id={$province} AND city_id=0"; } else { $province = substr($fid, 1); $city = substr($id, 1); $areaWhere = "AND province_id={$province} AND city_id = {$city}"; } $data = OrderKeyData::getTimeDailyBySite($start, $end, $user_type, $siteIds, $areaWhere); return array("data" => $data); } else if ($type == 'web_stock') { if ($area != null && $area > -1) { $areaId = 'distrinct_id_out'; $areaValue = $area; } else if ($city != null && $city > -1) { $areaId = 'city_id_out'; $areaValue = $city; } else if ($province != null & $province > -1) { $areaId = 'province_id_out'; $areaValue = $province; } else { $areaId = 1; $areaValue = 1; } $id = @$_REQUEST['id']; $fid = @$_REQUEST['fid']; // die("stock". $fid); if ($fid == null || $fid == "null") { //为省级 $province_id_site = substr($id, 1); $data = OrderKeyData::getTimeDailyByStockProvince($start, $end, $user_type, $stock_id, $areaId, $areaValue, $province_id_site); return array("data" => $data); } else { //为市级 $province_id_site = substr($fid, 1); $city_id_site = substr($id, 1); $data = OrderKeyData::getTimeDailyByStockCity($start, $end, $user_type, $stock_id, $areaId, $areaValue, $province_id_site, $city_id_site); return array("data" => $data); } } else if ($type == 'wireless_stock') { $wap_type = @$_REQUEST['wap_type']; //mark if ($area != null && $area > -1) { $areaId = 'distrinct_id_out'; $areaValue = $area; } else if ($city != null && $city > -1) { $areaId = 'city_id_out'; $areaValue = $city; } else if ($province != null & $province > -1) { $areaId = 'province_id_out'; $areaValue = $province; } else { $areaId = 1; $areaValue = 1; } $id = @$_REQUEST['id']; $fid = @$_REQUEST['fid']; if (empty($fid) || $fid == "null") { //为省级 $province_id_site = substr($id, 1); $areaWhere = "and province_id_site = {$province_id_site}"; } else { //为市级 $province_id_site = substr($fid, 1); $city_id_site = substr($id, 1); $areaWhere = "and province_id_site = {$province_id_site} and city_id_site = {$city_id_site}"; } $data = OrderKeyData::getTimeDailyByWireless($start, $end, $user_type, $stock_id, $wap_type, $areaWhere); return array("data" => $data); } else if ($type == 'wireless_site') { $wap_type = @$_REQUEST['wap_type']; if ($area != null && $area > -1) { $areaId = 'distrinct_id_out'; $areaValue = $area; } else if ($city != null && $city > -1) { $areaId = 'city_id_out'; $areaValue = $city; } else if ($province != null & $province > -1) { $areaId = 'province_id_out'; $areaValue = $province; } else { $areaId = 1; $areaValue = 1; } $id = @$_REQUEST['id']; $fid = @$_REQUEST['fid']; if (empty($fid) || $fid == "null") { //为省级 $province_id_site = substr($id, 1); $areaWhere = "and province_id_site = {$province_id_site}"; } else { //为市级 $province_id_site = substr($fid, 1); $city_id_site = substr($id, 1); $areaWhere = "and province_id_site = {$province_id_site} and city_id_site = {$city_id_site}"; } $data = OrderKeyData::getTimeDailyByWirelessBySite($start, $end, $user_type, $wap_type, $areaWhere); return array("data" => $data); } else if ($type == 'webwireless_site') { // $start, $end, $user_type, $areaWhere $id = @$_REQUEST['id']; $fid = @$_REQUEST['fid']; if($fid =="null"){ $id = substr($id,1); $areaWhere = "AND province_id = $id "; }else{ $id = substr($id,1); $fid = substr($fid,1); $areaWhere = "AND province_id = $fid AND city_id = $id "; } $data = OrderKeyData::getDailyBywebwireless_site($start,$end,$user_type,$areaWhere); return array("data" => $data); }else if ($type == 'webwireless_stock') { $id = @$_REQUEST['id']; $fid = @$_REQUEST['fid']; if($fid =="null"){ $id = substr($id,1); $areaWhere = "AND province_id_site = $id "; }else{ $id = substr($id,1); $fid = substr($fid,1); $areaWhere = "AND province_id_site = $fid AND city_id_site = $id "; } $out_area = out_area_where(); $areaWhere = $areaWhere . $out_area; $data = OrderKeyData::getDailyBywebwireless_stock($start, $end, $user_type, $areaWhere,$stock_id); return array("data" => $data); } } function OrderKeyData_getVitualOrder() { $start = $_REQUEST['start']; $end = $_REQUEST['end']; $ret = OrderKeyData::getVitualOrder($start, $end); return ajax_return($ret); } function OrderKeyData_virtualDownload() { $data = $_REQUEST['data']; $columns = $_REQUEST['columns']; $fileName = $_REQUEST['name']; //$data = mb_convert_encoding($data, "utf-8", "gb2312"); //$columns = mb_convert_encoding($columns, "utf-8", "gb2312"); //$fileName = mb_convert_encoding($fileName, "utf-8", "gb2312"); //echo $fileName; //exit; $data_arr = json_decode($data, true); $columns = json_decode($columns, true); // php生成excel报表，是通过发送header()头信息完成的 header("Content-Type: application/vnd.ms-execl"); header("Content-Type: application/vnd.ms-excel; charset=gbk"); //告知浏览器文件名称，并要求客户端下载 header("Content-Disposition:filename=$fileName.xls"); header("Pragma: no-cache"); header("Expires: 0"); //列头 foreach ($columns as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; //content foreach ($data_arr as $value) { foreach ($value as $k => $v) { $v = mb_convert_encoding($v, "gb2312", "utf-8"); echo "$v "; } echo " "; } exit; } function ajax_return($data) { if ($data != null) { return array("errorno" => 0, "data" => $data); } else { return array("errorno" => 1, "data" => false); } }
<?php require_once(STATISTIC_ROOT . 'etc/inecfg.inc.php'); function getDB($No) { switch ($No) { case 1: $st = "channel_statistic"; //192.168.2.74 'DB' => 'ICSON_STATISTICS_CLICKFLOW' 网站统计 break; case 2: $st = "warehouses"; //192.168.2.80 'DB' => 'ETL0_Combine_IAS' 无线 和 仓 break; case 3: $st = "channel_sh_product_sales"; //'192.168.2.76', 'DB' => 'SH_SYNC' break; case 4: $st = "virtualorder"; //'192.168.2.77', 'DB' => ICSON_STATISTICS_PURCHASE..t_aggregation_daily_category1_sale_for_web //虚拟订单 break; case 5: $st = "real_time_sale"; //'192.168.2.76', 'DB' => [ICSON_STATISTICS_PURCHASE_REAL].[dbo].[t_combine_area_delivery_stock_jd] //实时销售 break; } global $_ADMIN_MSDB_CFG, $_MSDB_CFG; $_MSDB_CFG[$st] = $_ADMIN_MSDB_CFG[$st]; return Config::getMSDB($st); } class OrderKeyData { public static $pie_top_count = 6; public static function getWarehouses() { $sql = <<<QUERY SELECT TOP 1000 [stocksysno] ,[stockname] ,[fdcstocksysno] ,[fdcstockname] ,[dmsstocksysno] ,[dmsstockname] ,[StockType] ,[AreaName] ,[sizetype] FROM [ETL0_Combine_IAS].[dbo].[View_StockTree] QUERY; $db = getDB(2); return $db->getRows($sql); } public static function getJDWarehouses() { $sql = <<<QUERY SELECT TOP 1000 [areaid] ,[areaname] ,[deliveryid] ,[deliveryname] ,[storeid] ,[storename] FROM [ICSON_STATISTICS_PURCHASE_REAL].[dbo].[t_combine_area_delivery_stock_jd] QUERY; $db = getDB(5); return $db->getRows($sql); } public static function getCitys() { $sql = <<<QUERY SELECT TOP 10000 [SysNo] ,[ProvinceSysNo] ,[CitySysNo] ,[ProvinceName] ,[CityName] ,[DistrictName] FROM [SH_SYNC].[dbo].[Area] QUERY; $db = getDB(3); return $db->getRows($sql); } public static function getVitualOrder($start, $end) { /* $sql = <<<QUERY SELECT CONVERT(VARCHAR(20),s_date,23) AS s_date, order_num , order_product_num , order_fee , order_user_num FROM ICSON_STATISTICS_PURCHASE..t_aggregation_daily_category1_sale_for_web WHERE category1_sysno = 1721 AND warehouse_id = -999999 AND s_date >= '{$start}' AND s_date <= '{$end}' AND isWireLess = 0 AND base_product_type = -999999 AND product_status = -999999 AND product_salemodel = -999999 ORDER BY s_date QUERY; */ $sql = <<<QUERY SELECT CONVERT(varchar(20),s_date,23) as s_date , SUM([order_product_num]) AS [order_product_num] , SUM([order_num]) AS [order_num] , SUM([order_fee]) AS [order_fee] , SUM([order_user_num]) AS [order_user_num] FROM [ICSON_STATISTICS_PURCHASE].[dbo].[t_aggregation_daily_product_sale_for_web_pl] WHERE category1_sysno = 1721 AND warehouse_id = 1 AND s_date >= '{$start}' AND s_date <= '{$end}' GROUP BY s_date QUERY; // echo $sql;exit; $db = getDB(3); return $db->getRows($sql); } //网站数据- 站 public static function getTimeBySite($start, $end, $userType, $siteId) { $sql = <<<QUERY SELECT TOP 1000 CONVERT(VARCHAR(20),s_date,23) AS s_date, pv , pv_u, item_pv , uv , valid_uv , item_uv , cart_uv , order_uv , order_num , order_user_num , order_fee , ( CAST(order_num AS DECIMAL(19, 6)) / uv ) AS order_convert , out_num , out_user_num , out_fee , ( CAST(out_num AS DECIMAL(19, 6)) / uv ) AS out_convert , CASE WHEN out_num = 0 THEN 0 ELSE ( CAST(out_fee AS DECIMAL(19, 6)) / out_num ) END AS price_per_cst , CASE WHEN order_user_num = 0 THEN 0 ELSE ( CAST(order_product_num AS DECIMAL(19, 6)) / order_user_num ) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '{$start}' AND s_date <= '{$end}' AND warehouse_id in ({$siteId}) AND user_type in ({$userType}) AND city_id = 0 AND province_id=0 ORDER BY s_date desc QUERY; echo $sql ;exit; $db = getDB(1); return $db->getRows($sql); } public static function getAreaBySite($start, $end, $userType, $siteId) { $sql = <<<QUERY SELECT TOP 1000 province_id, city_id, SUM(pv) AS pv , SUM(pv_u) AS pv_u , SUM(item_pv) AS item_pv , SUM(uv) AS uv , SUM(valid_uv) AS valid_uv , SUM(item_uv) AS item_uv , SUM(cart_uv) AS cart_uv , SUM(order_uv) AS order_uv , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , ( CAST(SUM(order_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS order_convert, SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , ( CAST(SUM(out_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS out_convert ,CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '{$start}' AND s_date <= '{$end}' AND warehouse_id in ({$siteId}) AND user_type in ({$userType}) GROUP BY province_id,city_id QUERY; // echo $sql;exit; $db = getDB(1); return $db->getRows($sql); } public static function sumData($start, $end, $userType, $siteId) { $sql = <<<QUERY SELECT TOP 1000 SUM(cast(pv as float)) AS pv , CASE WHEN SUM(uv)=0 THEN 0 ELSE ( CAST(SUM(cast(pv as float)) AS DECIMAL(19, 6)) / SUM(uv)) END AS pv_u, SUM(item_pv) AS item_pv , SUM(uv) AS uv , SUM(valid_uv) AS valid_uv , SUM(item_uv) AS item_uv , SUM(cart_uv) AS cart_uv , SUM(order_uv) AS order_uv , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , CASE WHEN SUM(uv)=0 THEN 0 ELSE ( CAST(SUM(order_num) AS DECIMAL(19, 6)) / SUM(uv)) END AS order_convert, SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(uv)=0 THEN 0 ELSE ( CAST(SUM(out_num) AS DECIMAL(19, 6)) / SUM(uv)) END AS out_convert, CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '{$start}' AND s_date <= '{$end}' AND warehouse_id in ({$siteId}) AND user_type in ({$userType}) AND city_id = 0 AND province_id = 0 QUERY; //exit($sql); $db = getDB(1); return $db->getRows($sql); } public static function getTimeDailyBySite($start, $end, $userType, $siteId, $areaWhere) { $sql = <<<QUERY SELECT TOP 1000 CONVERT(VARCHAR(20),s_date,23) AS s_date, province_id, city_id, warehouse_id, pv , pv_u , item_pv , uv , valid_uv , item_uv , cart_uv , order_uv , order_num , order_user_num , order_fee, ( CAST(order_num AS DECIMAL(19, 6)) / uv ) AS order_convert , out_num, out_user_num , out_fee , CASE WHEN uv =0 THEN 0 ELSE (CAST(out_num AS DECIMAL(19, 6)) / uv ) END AS out_convert , CASE WHEN out_num = 0 THEN 0 ELSE ( CAST(out_fee AS DECIMAL(19, 6)) / out_num ) END AS price_per_cst , CASE WHEN order_user_num = 0 THEN 0 ELSE ( CAST(order_product_num AS DECIMAL(19, 6)) / order_user_num ) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '{$start}' AND s_date <= '{$end}' AND warehouse_id in ({$siteId}) AND user_type in ({$userType}) {$areaWhere} ORDER BY s_date desc QUERY; // echo $sql;exit; $db = getDB(1); return $db->getRows($sql); } public static function getPieDataByWebSite($site_id, $start, $end, $user_type, $field = "pv") { $pie_count = self::$pie_top_count; $sql = <<<QUERY SELECT TOP $pie_count province_id , SUM(pv) AS pv , SUM(pv_u) AS pv_u , SUM(item_pv) AS item_pv , SUM(uv) AS uv , SUM(valid_uv) AS valid_uv , SUM(item_uv) AS item_uv , SUM(cart_uv) AS cart_uv , SUM(order_uv) AS order_uv , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , ( CAST(SUM(order_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS order_convert , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , ( CAST(SUM(out_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS out_convert , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '$start' AND s_date <= '$end' AND warehouse_id IN ( $site_id ) AND user_type IN ( $user_type ) AND city_id = 0 AND province_id <> 0 GROUP BY province_id ORDER BY $field DESC QUERY; // die($sql); echo $sql;exit; $db = getDB(1); return $db->getRows($sql); } public static function getTrendByWebSite($site_id, $start, $end, $user_type, $province_id) { $sql = <<<QUERY SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , province_id, SUM(pv) AS pv , SUM(pv_u) AS pv_u , SUM(item_pv) AS item_pv , SUM(uv) AS uv , SUM(valid_uv) AS valid_uv , SUM(item_uv) AS item_uv , SUM(cart_uv) AS cart_uv , SUM(order_uv) AS order_uv , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , ( CAST(SUM(order_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS order_convert , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , ( CAST(SUM(out_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS out_convert , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ICSON_STATISTICS_CLICKFLOW ].[dbo].[t_aggregation_daily_area] WHERE s_date >= '$start' AND s_date <= '$end' AND warehouse_id IN ( $site_id ) AND user_type IN ( $user_type ) AND city_id = 0 AND province_id IN ( $province_id ) GROUP BY s_date,province_id ORDER BY s_date QUERY; $db = getDB(1); return $db->getRows($sql); } //网站数据 - 仓 public static function getTimeByStock($start, $end, $userType, $stock, $areaWhere) { $sql = <<<QUERY SELECT TOP 1000 convert(varchar(12),[s_date],23) as s_date, SUM(order_num) AS order_num, SUM(order_user_num) AS order_user_num, SUM(order_fee) AS order_fee, SUM(out_num) AS out_num, SUM(out_user_num) AS out_user_num, SUM(out_fee) AS out_fee ,CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE user_type in ({$userType}) AND stocksysno IN ( {$stock} ) AND s_date >= '{$start}' AND s_date <= '{$end}' {$areaWhere} GROUP BY s_date ORDER BY s_date desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getTimeDailyByStockProvince($start, $end, $userType, $stock, $areaId, $areaValue, $province_id_site) { $sql = <<<QUERY SELECT TOP 1000 convert(varchar(12),[s_date],23) as s_date, SUM(order_num) AS order_num, SUM(order_user_num) AS order_user_num, SUM(order_fee) AS order_fee, SUM(out_num) AS out_num, SUM(out_user_num) AS out_user_num, SUM(out_fee) AS out_fee ,CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE province_id_site={$province_id_site} AND {$areaId} = {$areaValue} AND user_type in ({$userType}) AND stocksysno IN ( {$stock} ) AND s_date >= '{$start}' AND s_date <= '{$end}' GROUP BY s_date ORDER BY s_date desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getTimeDailyByStockCity($start, $end, $userType, $stock, $areaId, $areaValue, $province_id_site, $city_id_site) { $sql = <<<QUERY SELECT TOP 1000 convert(varchar(12),[s_date],23) as s_date, SUM(order_num) AS order_num, SUM(order_user_num) AS order_user_num, SUM(order_fee) AS order_fee, SUM(out_num) AS out_num, SUM(out_user_num) AS out_user_num, SUM(out_fee) AS out_fee ,CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE province_id_site={$province_id_site} AND city_id_site = {$city_id_site} AND {$areaId} = {$areaValue} AND user_type in ({$userType}) AND stocksysno IN ( {$stock} ) AND s_date >= '{$start}' AND s_date <= '{$end}' GROUP BY s_date ORDER BY s_date desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function sumData2($start, $end, $userType, $warehouseID, $areaWhere) { $sql = <<<QUERY SELECT TOP 1000 SUM(order_num) AS order_num, SUM(order_user_num) AS order_user_num, SUM(order_fee) AS order_fee, SUM(out_num) AS out_num, SUM(out_user_num) AS out_user_num, SUM(out_fee) AS out_fee ,CASE WHEN SUM(out_num)=0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num)) END AS price_per_cst ,CASE WHEN SUM(order_user_num)=0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num)) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE user_type in ({$userType}) AND stocksysno IN ( {$warehouseID} ) AND s_date >= '{$start}' AND s_date <= '{$end}' {$areaWhere} QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getAreaByStock($start, $end, $userType, $stock, $areaWhere) { $sql = <<<QUERY SELECT 'p999999'AS id , NULL AS fid, '全国' AS name , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE user_type in ({$userType}) AND stocksysno IN ( {$stock}) AND s_date >= '{$start}' AND s_date <= '{$end}' {$areaWhere} UNION SELECT 'p'+ CAST(province_id_site AS NVARCHAR(10)) AS id , NULL AS fid, province_name_site AS name , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE user_type in ({$userType}) AND stocksysno IN ( {$stock}) AND s_date >= '{$start}' AND s_date <= '{$end}' {$areaWhere} GROUP BY province_id_site , province_name_site UNION SELECT 'c'+CAST(city_id_site AS NVARCHAR(10)) AS id , 'p'+CAST (province_id_site AS NVARCHAR(10)) AS fid , city_name_site AS name , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE user_type in ({$userType}) AND stocksysno IN ( {$stock}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND city_id_site <> 666666 {$areaWhere} GROUP BY province_id_site , province_name_site, city_id_site, city_name_site ORDER BY id desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getPieDataByWebStock($stock_id, $start, $end, $user_type) { $pie_count = self::$pie_top_count; $sql = <<<QUERY SELECT TOP $pie_count province_id_site , province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id_site <> 0 AND stocksysno IN ( $stock_id ) GROUP BY province_id_site , province_name_site ORDER BY order_num desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getTrendByWebStock($stock_id, $start, $end, $user_type, $province_id) { $sql = <<<QUERY SELECT TOP 200 CONVERT(VARCHAR(12),s_date,23) AS s_date, province_id_site , province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE province_id_site IN ($province_id ) AND s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id_site <> 0 AND stocksysno IN ($stock_id) GROUP BY s_date,province_id_site , province_name_site ORDER BY s_date desc QUERY; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } //无线数据 - 仓 public static function getTimeByWireless($areaWhere, $user_type, $start, $end, $stocks, $wap_type) { $sql = <<<SQL SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ( $user_type ) AND stocksysno IN ( $stocks ) AND s_date >= '$start' AND s_date <= '$end' and wap_type in ({$wap_type}) {$areaWhere} GROUP BY s_date ORDER BY s_date desc SQL; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function sumData3($areaWhere, $user_type, $start, $end, $stocks, $wap_type) { { $sql = <<<SQL SELECT TOP 1000 SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ( $user_type ) AND stocksysno IN ( $stocks ) AND s_date >= '$start' AND s_date <= '$end' and wap_type in ({$wap_type}) {$areaWhere} SQL; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } } public static function getAreaByWireless($areaWhere, $user_type, $stockIds, $start, $end, $wap_type) { $sql = <<<SQL SELECT TOP 1000 'p999999' AS id , NULL AS fid , '全国' AS name, SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ({$user_type}) AND stocksysno IN ({$stockIds}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND wap_type IN ( {$wap_type} ) {$areaWhere} UNION SELECT TOP 1000 'p'+CAST (province_id_site AS NVARCHAR(100)) AS id , NULL AS fid , province_name_site AS name, SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ({$user_type}) AND stocksysno IN ({$stockIds}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND wap_type IN ( {$wap_type} ) {$areaWhere} GROUP BY province_id_site , province_name_site UNION SELECT TOP 1000 'c'+CAST (city_id_site AS NVARCHAR(100)) AS id , 'p'+CAST (province_id_site AS NVARCHAR(100)) AS fid , city_name_site AS name, SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ({$user_type}) AND stocksysno IN ({$stockIds}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND wap_type IN ( {$wap_type} ) AND city_id_site <> 666666 {$areaWhere} GROUP BY province_id_site , province_name_site, city_id_site, city_name_site ORDER BY id desc SQL; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getAreaLineByWireless($stockIds, $province_id_sites, $start, $end, $userType, $area_id_out_where, $wap_type) { $sql = <<<SQL SELECT TOP 1000 CONVERT(VARCHAR(100), s_date, 23) AS s_date , province_id_site , province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE province_id_site IN ( {$province_id_sites} ) AND s_date >= '{$start}' AND s_date <= '{$end}' AND user_type IN ( $userType) AND stocksysno IN ( $stockIds ) AND wap_type IN ($wap_type) {$area_id_out_where} GROUP BY province_id_site , province_name_site , s_date UNION SELECT TOP 1000 CONVERT(VARCHAR(100), s_date, 23) AS s_date , -999999 AS province_id_site , '其他' AS province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock] WHERE province_id_site NOT IN ( {$province_id_sites} ) AND s_date >= '{$start}' AND s_date <= '{$end}' AND user_type IN ( $userType) AND stocksysno IN ( $stockIds ) AND wap_type IN ($wap_type) {$area_id_out_where} GROUP BY s_date SQL; die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getTimeDailyByWireless($start, $end, $userType, $stock, $wap_type, $areaWhere) { $sql = <<<SQL SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ( $userType ) AND stocksysno IN ( $stock ) AND s_date >= '$start' AND s_date <= '$end' and wap_type in ({$wap_type}) {$areaWhere} GROUP BY s_date ORDER BY s_date desc SQL; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getPieDataByWirelessByStock($start, $end, $wap_type, $user_type, $field = "order_num", $stockids) { $pie_count = self::$pie_top_count; $sql = <<<EOH SELECT TOP $pie_count province_id_site , province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ($user_type ) AND stocksysno IN ($stockids ) AND s_date >= '$start' AND s_date <= '$end' AND wap_type IN ($wap_type) GROUP BY province_id_site , province_name_site ORDER BY {$field} desc EOH; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getTrendByWirelessByStock($start, $end, $wap_type, $user_type, $province_ids, $stockids) { $sql = <<<HD SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , province_id_site , province_name_site , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap] WHERE user_type IN ( $user_type ) AND stocksysno IN ( $stockids ) AND s_date >= '$start' AND s_date <= '$end' AND wap_type IN ( $wap_type ) AND province_id_site IN ( $province_ids ) GROUP BY s_date , province_id_site , province_name_site ORDER BY s_date , province_id_site , province_name_site HD; $db = getDB(2); return $db->getRows($sql); } //无线数据 - 站 public static $wireless_site_field = <<<F SUM(cast(pv as float)) AS pv , SUM([uv]) AS uv , SUM([detail_pv]) AS detail_pv , SUM([valid_uv]) AS valid_uv , SUM([detail_uv]) AS detail_uv , SUM([buy_uv]) AS buy_uv , SUM([orderconfirm_uv]) AS orderconfirm_uv , CASE WHEN SUM(uv) = 0 THEN 0 ELSE ( CAST(SUM(cast(pv as float)) AS DECIMAL(19, 6)) / SUM(uv) ) END AS pv_u , SUM(order_num) AS order_num , SUM(order_user_num) AS order_user_num , SUM(order_fee) AS order_fee , SUM(out_num) AS out_num , SUM(out_user_num) AS out_user_num , SUM(out_fee) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst, ( CAST(sum(order_num)AS DECIMAL(19, 6)) / sum(uv) ) AS order_convert , ( CAST(sum(out_num) AS DECIMAL(19, 6)) / sum(uv) ) AS out_convert F; public static function getTimeByWirelessBySite($start, $end, $wap_type, $user_type) { $c_field = self::$wireless_site_field; $sql = "SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ( {$user_type}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ({$wap_type}) GROUP BY s_date ORDER BY s_date DESC"; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getSumByWirelessBySite($start, $end, $wap_type, $user_type) { $c_field = self::$wireless_site_field; $sql = "SELECT TOP 1000 $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ( {$user_type}) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ({$wap_type})"; $db = getDB(2); return $db->getRows($sql); } public static function getAreaByWirelessBySite($start, $end, $wap_type, $user_type) {$c_field = self::$wireless_site_field; $sql = <<<EOT SELECT TOP 1000 'p999999' AS id , NULL AS fid , '全国' AS name, $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ($user_type) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ({$wap_type}) UNION SELECT TOP 1000 'p' + CAST (province_id_site AS NVARCHAR(100)) AS id , NULL AS fid , [province_name_site] AS name, $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ($user_type) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ({$wap_type}) GROUP BY [province_id_site] , [province_name_site] UNION SELECT TOP 1000 'c' + CAST (city_id_site AS NVARCHAR(100)) AS id , 'p' + CAST (province_id_site AS NVARCHAR(100)) AS fid , [city_name_site] , $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ($user_type) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ({$wap_type}) GROUP BY province_id_site , province_name_site , city_id_site , city_name_site ORDER BY id desc EOT; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getPieDataByWirelessBySite($start, $end, $wap_type, $user_type, $field = "pv") { $c_field = self::$wireless_site_field; $pie_count = self::$pie_top_count; $sql = <<<EOH SELECT TOP $pie_count [province_id_site], [province_name_site], $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ( $user_type ) AND s_date >= '{$start}' AND s_date <= '{$end}' AND [wap_type] IN ( $wap_type) AND province_id_site <> 101 GROUP BY [province_id_site], [province_name_site] ORDER BY {$field} desc EOH; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getTrendByWirelessBySite($start, $end, $wap_type, $user_type, $province_ids) {$c_field = self::$wireless_site_field; $sql = " SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , province_id_site , province_name_site , $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ( $user_type ) AND s_date >= '$start' AND s_date <= '$end' AND [wap_type] IN ( $wap_type) AND province_id_site IN ( $province_ids) GROUP BY s_date , province_id_site , province_name_site ORDER BY s_date , province_id_site "; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getTimeDailyByWirelessBySite($start, $end, $userType, $wap_type, $areaWhere) {$c_field = self::$wireless_site_field; $sql = <<<QUERY SELECT TOP 1000 CONVERT(VARCHAR(12), [s_date], 23) AS s_date , $c_field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web] WHERE user_type IN ( $userType ) AND s_date >= '$start' AND s_date <= '$end' and wap_type in ({$wap_type}) {$areaWhere} GROUP BY s_date ORDER BY s_date desc QUERY; // die($sql); $db = getDB(2); return $db->getRows($sql); } //网站+无线 - 站 public static $webwireless_site_field = <<<F SUM(cast(pv as float)) AS pv , SUM([uv]) AS uv , SUM([valid_uv]) AS valid_uv , CASE WHEN SUM(uv) = 0 THEN 0 ELSE ( CAST(SUM(cast(pv as float)) AS DECIMAL(19, 6)) / SUM(uv) ) END AS pv_u , SUM([item_pv]) AS item_pv , SUM([item_uv]) AS item_uv , SUM([cart_uv]) AS cart_uv , SUM([order_uv]) order_uv , SUM([order_num]) AS order_num , SUM([order_fee]) AS order_fee , SUM([order_user_num]) AS order_user_num , ( CAST(SUM(order_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS order_convert , SUM([out_num]) AS out_num , SUM([out_fee]) AS out_fee , SUM([out_user_num]) AS out_user_num , ( CAST(SUM(out_num) AS DECIMAL(19, 6)) / SUM(uv) ) AS out_convert , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst F; public static function getTimeBywebwireless_site($start, $end, $user_type) { $field = self::$webwireless_site_field; // user_type = 0 全部的 if($user_type =="1,2"){ $all_user_type = 0; } $sql = <<<SQL SELECT TOP 1000 '2100-1-1' AS s_date , $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND province_id = 0 AND city_id = 0 AND user_type IN ( $user_type ) UNION ALL SELECT TOP 1000 CONVERT(VARCHAR(20),s_date,23) AS s_date, $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND province_id = 0 AND city_id = 0 AND user_type IN ( $user_type ) GROUP BY s_date ORDER BY s_date desc SQL; // echo $sql;exit; $db = getDB(2); return $db->getRows($sql); } public static function getAreaBywebwireless_site($start, $end, $user_type) { $field = self::$webwireless_site_field; $sql = <<<SQL SELECT TOP 1000 [city_id] AS id , [province_id] AS fid , $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id <> 0 GROUP BY province_id , city_id UNION ALL SELECT TOP 1000 0 AS [province_id] , 0 AS [city_id] , $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id = 0 UNION ALL SELECT TOP 1000 [province_id] AS id , 0 AS fid , $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id <> 0 GROUP BY province_id ORDER BY fid SQL; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getPieBywebwireless_site($start, $end, $user_type, $c_field) {$field = self::$webwireless_site_field; $pie_count = self::$pie_top_count; $sql = <<<SQL SELECT TOP $pie_count [province_id] AS [province_id] , $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ($user_type) AND province_id <> 0 GROUP BY province_id ORDER BY $c_field desc SQL; // die($sql); $db = getDB(2); return $db->getRows($sql); } public static function getTrendBywebwireless_site($start, $end, $user_type,$province_id){ $field = self::$webwireless_site_field; $sql = <<<SQL SELECT TOP 1000 CONVERT(varchar(20),s_date,23) as s_date, province_id, $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND province_id IN ( $province_id ) GROUP BY s_date,province_id ORDER BY s_date SQL; $db = getDB(2); return $db->getRows($sql); } public static function getDailyBywebwireless_site($start, $end, $user_type, $areaWhere){ $field = self::$webwireless_site_field; $sql = <<<SQL SELECT TOP 1000 CONVERT(varchar(20),s_date,23) as s_date, $field FROM [ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_web_all] WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) $areaWhere GROUP BY s_date ORDER BY s_date desc SQL; $db = getDB(2); return $db->getRows($sql); } //网站+无线 - 仓 public static $webwireless_stock_table = "[ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_stock_all]"; public static $webwireless_stock_field = <<<F SUM([order_num]) AS order_num , SUM([order_user_num]) AS order_user_num , SUM([order_fee]) AS order_fee , SUM([out_num]) AS out_num , SUM([out_user_num]) AS out_user_num , SUM([out_fee]) AS out_fee , CASE WHEN SUM(out_num) = 0 THEN 0 ELSE ( CAST(SUM(out_fee) AS DECIMAL(19, 6)) / SUM(out_num) ) END AS price_per_cst , CASE WHEN SUM(order_user_num) = 0 THEN 0 ELSE ( CAST(SUM(order_product_num) AS DECIMAL(19, 6)) / SUM(order_user_num) ) END AS num_per_cst F; public static function getTimeBywebwireless_stock($start, $end, $user_type,$stock_id,$areaWhere) { $field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $sql = <<<SQL SELECT TOP 1000 CONVERT(varchar(20),s_date,23) as s_date, $field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN( $stock_id) AND user_type IN ( $user_type) $areaWhere GROUP BY s_date ORDER BY s_date desc SQL; $db = getDB(2); return $db->getRows($sql); } public static function getSumBywebwireless_stock($start, $end, $user_type,$stock_id,$areaWhere) { $field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $sql = <<<SQL SELECT TOP 1000 $field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN( $stock_id) AND user_type IN ( $user_type) $areaWhere SQL; $db = getDB(2); return $db->getRows($sql); } public static function getAreaBywebwireless_stock($start, $end, $user_type,$stock_id,$areaWhere) { $c_field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $sql = <<<SQL SELECT TOP 1000 province_id_site AS id , NULL AS fid, province_name_site AS name, $c_field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN( $stock_id) AND user_type IN ($user_type) $areaWhere GROUP BY province_id_site,province_name_site UNION ALL SELECT TOP 1000 city_id_site AS id, province_id_site AS fid , city_name_site AS NAME , $c_field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN($stock_id) AND user_type IN ($user_type) AND province_id_site <> 0 $areaWhere GROUP BY province_id_site,province_name_site,city_id_site,city_name_site UNION ALL SELECT TOP 1000 0 AS id , NULL AS fid , '全国' AS name , $c_field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN( $stock_id) AND user_type IN ($user_type) $areaWhere ORDER BY fid,id SQL; $db = getDB(2); return $db->getRows($sql); } public static function getPieBywebwireless_stock($start, $end, $user_type, $c_field,$areaWhere,$stock_id) { $field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $pie_count = self::$pie_top_count; $sql = <<<SQL SELECT TOP $pie_count [province_id_site], [province_name_site], $field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ($user_type) AND [province_id_site] <> 0 AND stocksysno IN( $stock_id) $areaWhere GROUP BY [province_id_site],[province_name_site] ORDER BY $c_field desc SQL; $db = getDB(2); return $db->getRows($sql); } public static function getTrendBywebwireless_stock($start, $end, $user_type,$province_id,$areaWhere,$stock_id){ $field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $sql = <<<SQL SELECT TOP 1000 CONVERT(varchar(20),s_date,23) as s_date, [province_id_site], [province_name_site], $field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND user_type IN ( $user_type ) AND [province_id_site] IN ( $province_id ) AND stocksysno IN( $stock_id) $areaWhere GROUP BY s_date,province_id_site,[province_name_site] ORDER BY s_date SQL; $db = getDB(2); return $db->getRows($sql); } public static function getDailyBywebwireless_stock($start, $end, $user_type, $areaWhere,$stock_id){ $field = self::$webwireless_stock_field; $table = self::$webwireless_stock_table; $sql = <<<SQL SELECT TOP 1000 CONVERT(varchar(20),s_date,23) as s_date, $field FROM $table WHERE s_date >= '$start' AND s_date <= '$end' AND stocksysno IN( $stock_id) AND user_type IN ( $user_type) $areaWhere GROUP BY s_date ORDER BY s_date desc SQL; $db = getDB(2); return $db->getRows($sql); } }