从获取数据的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\t";
}
echo "\t\n"; //content
if ($type == 'web_site_time') {
foreach ($data_arr as $value) {
foreach ($value as $k => $v) {
echo "$v\t";
}
echo "\t\n";
}
}
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\t";
}
echo "\t\n";
}
}
if ($type == 'web_stock_time') {
foreach ($data_arr as $value) {
foreach ($value as $k => $v) {
echo "$v\t";
}
echo "\t\n";
}
}
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\t";
continue;
} $v = mb_convert_encoding($v, "gb2312", "utf-8");
echo "$v\t";
}
echo "\t\n";
}
} if ($type == 'wireless_site_time') {
foreach ($data_arr as $value) {
foreach ($value as $k => $v) {
echo "$v\t";
}
echo "\t\n";
}
}
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\t";
continue;
} $v = mb_convert_encoding($v, "gb2312", "utf-8");
echo "$v\t";
}
echo "\t\n";
}
}
if ($type == 'wireless_stock_time') {
foreach ($data_arr as $value) {
foreach ($value as $k => $v) {
echo "$v\t";
}
echo "\t\n";
}
}
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\t";
continue;
} $v = mb_convert_encoding($v, "gb2312", "utf-8");
echo "$v\t";
}
echo "\t\n";
}
}
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\t";
}
echo "\t\n";
}
}
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\t";
}
echo "\t\n";
}
}
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\t";
}
echo "\t\n";
}
}
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\t";
}
echo "\t\n";
}
} 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 \t";
}
echo "\t\n"; //content
foreach ($data_arr as $value) { foreach ($value as $k => $v) {
$v = mb_convert_encoding($v, "gb2312", "utf-8");
echo "$v \t";
}
echo "\t\n";
}
exit;
} function ajax_return($data)
{
if ($data != null) {
return array("errorno" => 0, "data" => $data);
} else {
return array("errorno" => 1, "data" => false);
}
}
OrderKeyData.php
<?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);
} }
OrderKeyData.class.php
从OrderKeyData.php里的getData2()到OrderKeyData.class.php的getSumByWirelessBySite(),可以看出此报表的结果表是：
[ETL0_Combine_IAS].[dbo].[t_aggregation_daily_area_wap_web]
位于：服务器:192.168.2.80 易迅IDC:192.168.2.224,53195 腾讯IDC:10.217.136.21 user/pass: app_public/app_public_2014
结论：无线结果表从TDW拉数据，服务器挂了！