152 lines
6.8 KiB
PHP
152 lines
6.8 KiB
PHP
<?php
|
|
//2维历史数据分析
|
|
//可配置参数：zd1|bc|维度1,zd2|category|维度2
|
|
//2018-12-04 by hehe
|
|
//-----参数配置
|
|
//横向维度
|
|
$zd1=get_plugin_set("zd1","bc");
|
|
$zd2=get_plugin_set("zd2","category");
|
|
//
|
|
$use_layui_datatable="y";//使用标准layui datatable格式数据
|
|
$select_client_type="y";//dp:允许筛选店铺
|
|
//其他条件
|
|
//$con_other=" and brand='11'";
|
|
//
|
|
if(check_table_exist($table_pre."plugin_ls2")=="n"){
|
|
//
|
|
$code=0;
|
|
$error_msg="请先导入历史数据!";
|
|
}else{
|
|
//店铺查询条件
|
|
$con="";
|
|
if($msg_client["face"]=="4"){
|
|
$s_username=$msg_client["username"];
|
|
$con=" and uid='".$msg_client["id"]."'";
|
|
}else{
|
|
if($s_username){
|
|
$m2=get_db_msg("select id,face,path from ".$table_pre."client where username='".$s_username."'");
|
|
if($m2["face"]=="4"){
|
|
$con=" and uid='".$m2["id"]."'";
|
|
}else{
|
|
$con=" and (upath='".$m2["path"]."-".$m2["id"]."' or upath like '".$m2["path"]."-".$m2["id"]."-%')";
|
|
}
|
|
}else if($msg_client["face"]=="2"){
|
|
//区经角色所有客户
|
|
$con=get_qj_ucon_all($msg_client["charge_path_real"]);
|
|
}else{
|
|
//代理角色所有客户
|
|
$con=" and (upath='".$msg_client["path"]."-".$msg_client["id"]."' or upath like '".$msg_client["path"]."-".$msg_client["id"]."-%')";
|
|
}
|
|
}
|
|
//
|
|
$con_this=$con;
|
|
for($i=1;$i<3;$i++){
|
|
$v="zd".$i;
|
|
$v1="zd_title".$i;
|
|
$v2="arr_zd".$i;
|
|
//取得对应字段的名称
|
|
$$v1=get_cate_title_no("arr_select",$$v);
|
|
//取得字段的选项值
|
|
$q1="select no,title from ".$table_pre."keyword where cate='".$$v."' order by no";
|
|
$r1=$db->query($q1);
|
|
$arr_tmp=array();
|
|
while($m1=$db->fetch_array($r1)){
|
|
$arr_tmp[$m1["no"]]=$m1["title"];
|
|
}
|
|
$$v2=$arr_tmp;
|
|
}
|
|
//--------------------------------表头
|
|
//支持参数field,title,width,rowspan,colspan,style(样式),sort(true|false),fixed(left,right)
|
|
//第一行
|
|
$thead0="";
|
|
$thead0.="{\"field\":\"row11\",\"title\":\"".$zd_title1."\",\"rowspan\":2,\"fixed\":\"left\",\"width\":80},";
|
|
$thead0.="{\"field\":\"row12\",\"title\":\"".$zd_title2."\",\"rowspan\":2,\"width\":80},";
|
|
$thead0.="{\"field\":\"row13\",\"title\":\"往年销售\",\"colspan\":3},";
|
|
$thead0.="{\"field\":\"row14\",\"title\":\"实际订单\",\"colspan\":4},";
|
|
$thead0.="{\"field\":\"row15\",\"title\":\"差异\",\"colspan\":3}";
|
|
//第二行
|
|
$thead1="";
|
|
$thead1.="{\"field\":\"row201\",\"title\":\"销售款数\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row202\",\"title\":\"销售件数\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row203\",\"title\":\"销售金额\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row204\",\"title\":\"订单款数\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row205\",\"title\":\"订单件数\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row206\",\"title\":\"订单金额\",\"width\":100},";
|
|
$thead1.="{\"field\":\"row207\",\"title\":\"金额占%\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row208\",\"title\":\"款数差异\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row209\",\"title\":\"件数差异\",\"width\":80},";
|
|
$thead1.="{\"field\":\"row210\",\"title\":\"金额差异\",\"width\":100}";
|
|
//
|
|
$thead="\"thead\":[{\"index\":0,\"content\":[".$thead0."]},{\"index\":1,\"content\":[".$thead1."]}],";
|
|
$json_data.=$thead;
|
|
//-----------取得报表数据
|
|
$total_yd_je=$total_yd_js=$total_yd_ks=0;
|
|
//订单表数据
|
|
$q3="select ".$zd1.",".$zd2.",count(distinct iid) 'ks',sum(num) 'js',sum(jsje) 'je' from ".$table_pre."order where ka<>'y'".$con_other.$con_this." group by ".$zd1.",".$zd2;
|
|
$r3=$db->query($q3);
|
|
$arr_data_je=$arr_data_js=$arr_data_ks=array();
|
|
while($m3=$db->fetch_array($r3)){
|
|
$arr_data_je[$m3[$zd1]][$m3[$zd2]]=deel_num($m3["je"]);
|
|
$arr_data_js[$m3[$zd1]][$m3[$zd2]]=$m3["js"];
|
|
$arr_data_ks[$m3[$zd1]][$m3[$zd2]]=$m3["ks"];
|
|
$total_yd_je+=$m3["je"];
|
|
$total_yd_js+=$m3["js"];
|
|
$total_yd_ks+=$m3["ks"];
|
|
}
|
|
//sku
|
|
$q3="select ".$zd1.",".$zd2.",color,color_ka from ".$table_pre."kh where ka<>'y'";
|
|
$r3=$db->query($q3);
|
|
$arr_data_sku=array();
|
|
while($m3=$db->fetch_array($r3)){
|
|
if(trim($m3["color_ka"]))
|
|
$num1=count(explode(",",$m3["color_ka"]));
|
|
else
|
|
$num1=0;
|
|
$arr_data_sku[$m3[$zd1]][$m3[$zd2]]+=count(explode(",",$m3["color"]))-$num1;
|
|
}
|
|
//总历史数据
|
|
$tmp=get_db_msg("select sum(js) 'js',sum(je) 'je',sum(ks) 'ks' from ".$table_pre."plugin_ls2 where c1<>'' and c2<>''".$con_this);
|
|
$total_ls_je=$tmp["je"];
|
|
$total_ls_js=$tmp["js"];
|
|
$total_ls_ks=$tmp["ks"];
|
|
//合并单元格
|
|
$arr_colspan=array();
|
|
foreach($arr_zd1 as $key1=>$value1){
|
|
foreach($arr_zd2 as $key2=>$value2){
|
|
if($arr_data_sku[$key1][$key2]>0)
|
|
$arr_colspan[$key1]++;
|
|
}
|
|
}
|
|
//--------------------输出报表-----------------------------------------------
|
|
$tbody="";
|
|
foreach($arr_zd1 as $key1=>$value1){
|
|
$xj_1=$xj_2=$xj_3=$xj_4=$xj_5=0;
|
|
$count=0;
|
|
foreach($arr_zd2 as $key2=>$value2){
|
|
//取得指标数据
|
|
$m_ls=get_db_msg("select sum(js) 'js',sum(je) 'je',sum(ks) 'ks' from ".$table_pre."plugin_ls2 where c1='".$value1."' and c2='".$value2."'".$con_this);
|
|
if($arr_data_sku[$key1][$key2]>0){
|
|
$tbody.="{\"row201\":\"".$value1."\",\"row202\":\"".$value2."\",\"row203\":\"".deel_0($m_ls["ks"])."\",\"row204\":\"".deel_0($m_ls["js"])."\",\"row205\":\"".deel_0($m_ls["je"])."\",\"row206\":\"".deel_0($arr_data_ks[$key1][$key2])."\",\"row207\":\"".deel_0($arr_data_js[$key1][$key2])."\",\"row208\":\"".deel_0($arr_data_je[$key1][$key2])."\",\"row209\":\"".deel_bfb($arr_data_je[$key1][$key2],$total_yd_je)."\",\"row210\":\"".deel_0($m_ls["ks"]-$arr_data_ks[$key1][$key2])."\",\"row211\":\"".deel_0($m_ls["js"]-$arr_data_js[$key1][$key2])."\",\"row212\":\"".deel_0($m_ls["je"]-$arr_data_je[$key1][$key2])."\"},";
|
|
$xj_1+=$m_ls["ks"];
|
|
$xj_2+=$m_ls["js"];
|
|
$xj_3+=$m_ls["je"];
|
|
$xj_4+=$arr_data_ks[$key1][$key2];
|
|
$xj_5+=$arr_data_js[$key1][$key2];
|
|
$xj_6+=$arr_data_je[$key1][$key2];
|
|
$total_1+=$m_ls["ks"];
|
|
$total_2+=$m_ls["js"];
|
|
$total_3+=$m_ls["je"];
|
|
$total_4+=$arr_data_ks[$key1][$key2];
|
|
$total_5+=$arr_data_js[$key1][$key2];
|
|
$total_6+=$arr_data_je[$key1][$key2];
|
|
$count++;
|
|
}
|
|
}
|
|
//小计
|
|
$tbody.="{\"row201\":\"\",\"row202\":\"小计\",\"row203\":\"".deel_0($xj_1)."\",\"row204\":\"".deel_0($xj_2)."\",\"row205\":\"".deel_0($xj_3)."\",\"row206\":\"".deel_0($xj_4)."\",\"row207\":\"".deel_0($xj_5)."\",\"row208\":\"".deel_0($xj_6)."\",\"row209\":\"".deel_bfb($xj_6,$total_yd_je)."\",\"row210\":\"".deel_0($xj_1-$xj_4)."\",\"row211\":\"".deel_0($xj_2-$xj_5)."\",\"row212\":\"".deel_0($xj_3-$xj_6)."\"},";
|
|
}
|
|
//合计
|
|
$tbody.="{\"row201\":\"\",\"row202\":\"合计\",\"row203\":\"".deel_0($total_1)."\",\"row204\":\"".deel_0($total_2)."\",\"row205\":\"".deel_0($total_3)."\",\"row206\":\"".deel_0($total_4)."\",\"row207\":\"".deel_0($total_5)."\",\"row208\":\"".deel_0($total_6)."\",\"row209\":\"100%\",\"row210\":\"".deel_0($total_1-$total_4)."\",\"row211\":\"".deel_0($total_2-$total_5)."\",\"row212\":\"".deel_0($total_3-$total_6)."\"},";
|
|
}
|
|
$json_data.="\"tbody\":[".deel_end($tbody)."],";
|
|
?>
|