744 lines
33 KiB
PHP
744 lines
33 KiB
PHP
<?php
|
|
//字段一二维度报表
|
|
//可配置参数：zd1|category|字段1,zd2|brand|字段2,icon|menu_icon_bb_xw_ms|菜单图标
|
|
//2020-07-10 by hzz
|
|
//-----参数配置start
|
|
//菜单图标
|
|
$icon=get_plugin_set("icon","menu_icon_bb_xw_ms");//支持以下目录的图标ipad/images/menu_icon_*.png
|
|
//字段
|
|
$zd1=get_plugin_set("zd1","category");
|
|
$zd2=get_plugin_set("zd2","brand");
|
|
//
|
|
$select_client_type="y";//dp:允许筛选店铺
|
|
$use_layui_datatable="n";//使用标准layui datatable格式数据
|
|
//end
|
|
$s_key=$s_key?$s_key:$_key;
|
|
$s_ka=$s_ka?$s_ka:"n";
|
|
//-------------------------条件
|
|
//-------con为web_order
|
|
//-------con1为web_kh
|
|
|
|
//店铺查询条件
|
|
$con=" and ka<>'y'";
|
|
$con1=" and ka<>'y'";
|
|
|
|
if($s_key){
|
|
if($s_key=="all"){
|
|
$con.="";
|
|
}else{
|
|
$con.=" and _key='".$s_key."'";
|
|
}
|
|
}
|
|
|
|
|
|
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 if($msg_client["face"]=="1"){
|
|
//区经角色所有客户
|
|
}else{
|
|
//代理角色所有客户
|
|
$con.=" and (upath='".$msg_client["path"]."-".$msg_client["id"]."' or upath like '".$msg_client["path"]."-".$msg_client["id"]."-%')";
|
|
}
|
|
}
|
|
if($zd1!="" && $zd2!=""){
|
|
//--------------------------------------二维报表
|
|
$json_data.="\"sys_bb_type\":\"bball_2w\",";
|
|
//
|
|
$arr_group_by_title=array();
|
|
$r1=$db->query("select no,title from ".$table_pre."keyword where cate='arr_select'");
|
|
while($m1=$db->fetch_array($r1)){
|
|
$arr_group_by_title[$m1["no"]]=$m1["title"];
|
|
}
|
|
$arr_group_by_title["size"]="尺码";
|
|
$arr_group_by_title["color"]="颜色";
|
|
$arr_group_by_title["ks_size"]="款色深度";
|
|
//
|
|
$s_group_by1=$zd1;
|
|
$s_group_by2=$zd2;
|
|
|
|
if($s_group_by2=="size"){
|
|
//取得已订总件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0".$con);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//--------------------------------表头start
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td>".get_main_value($arr_group_by_title,$s_group_by1)."</td><td>尺码</td><td>已订件数</td><td>件数占比</td><td>吊牌金额</td><td>金额占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//--------------------------------表头end
|
|
|
|
|
|
$q1="select * from ".$table_pre."keyword where cate='".$s_group_by1."' order by ord";
|
|
$r1=$db->query($q1);
|
|
$count=$total_num=0;
|
|
$l=0;
|
|
while($m1=$db->fetch_array($r1)){
|
|
$tmp=get_db_msg("select size_group from ".$table_pre."kh where ".$s_group_by1."='".$m1["no"]."' limit 0,1");
|
|
$q="select * from ".$table_pre."size where iid='".$tmp["size_group"]."' order by iid,no";
|
|
$r=$db->query($q);
|
|
//
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and ".$s_group_by1."='".$m1["no"]."'".$con);
|
|
$xj_yd_js=$tmp["num"];
|
|
$xj_yd_je=$tmp["je"];
|
|
while($m=$db->fetch_array($r)){
|
|
$arr1=get_num_je_size_category($m["no"],$s_group_by1,$m1["no"]);
|
|
$total_num+=$arr1[0];
|
|
$total_money+=$arr1[1];
|
|
if($arr1[0]>0){
|
|
$content_data.="<tr align=center><td>".$m1["title"]."</td><td>".$m["title"]."</td><td>".$arr1[0]."</td><td>".deel_bfb($arr1[0],$xj_yd_js)."</td><td>".$arr1[1]."</td><td>".deel_bfb($arr1[1],$xj_yd_je)."</td></tr>";
|
|
$count++;
|
|
}
|
|
}
|
|
$l++;
|
|
}
|
|
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center><td colspan=2>合计</td><td>".$total_num."</td><td></td><td>".$total_money."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}else if($s_group_by2=="ks_size"){
|
|
//取得已订总件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0".$con);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//--------------------------------表头start
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td>".get_main_value($arr_group_by_title,$s_group_by1)."</td><td>件数</td><td>款色数</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//--------------------------------表头end
|
|
$q1="select * from ".$table_pre."keyword where cate='".$s_group_by1."' order by ord";
|
|
$r1=$db->query($q1);
|
|
$count=$total_js=$total_kss=0;
|
|
$l=0;
|
|
while($m1=$db->fetch_array($r1)){
|
|
$q="select sum(num)/count(distinct iid,color) 'num',count(distinct iid,color) 'kss' from ".$table_pre."order where ".$s_group_by1."='".$m1["no"]."'".$con." group by num order by num";
|
|
$r=$db->query($q);
|
|
//取得数据
|
|
$xj_js=$xj_kss=0;
|
|
$arr_data=array();
|
|
$k=0;
|
|
while($m=$db->fetch_array($r)){
|
|
$arr_data[deel_num($m["num"])]=$m["kss"];
|
|
$xj_js+=$m["num"]*$m["kss"];
|
|
$xj_kss+=$m["kss"];
|
|
$total_kss+=$m["kss"];
|
|
$total_js+=$m["num"]*$m["kss"];
|
|
$k++;
|
|
}
|
|
//
|
|
foreach($arr_data as $num1 => $kss1){
|
|
$content_data.="<tr align=center><td>".$m1["title"]."</td><td>".$num1."</td><td>".$kss1."</td><td>".deel_bfb($kss1,$xj_kss)."</td></tr>";
|
|
$count++;
|
|
}
|
|
//小计
|
|
if($k>0){
|
|
$content_data.="<tr align=center><td>".$m1["title"]."小计</td><td>".$xj_js."</td><td>".$xj_kss."</td><td>100%</td></tr>";
|
|
$count++;
|
|
}
|
|
}
|
|
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center><td>合计</td><td>".$total_js."</td><td>".$total_kss."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}else{
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td rowspan=2>".get_main_value($arr_group_by_title,$s_group_by1)."</td><td rowspan=2>".get_main_value($arr_group_by_title,$s_group_by2)."</td>";
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
$content_data.="<td rowspan=2>均价</td><td colspan=2>设计款数</td><td colspan=2>已订款数</td>";
|
|
}
|
|
$content_data.="<td colspan=2>设计款色</td><td colspan=2>已订款色</td><td colspan=2>已订件数</td><td colspan=2>吊牌金额</td></tr>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center>";
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
$content_data.="<td>款数</td><td>占比</td><td>款数</td><td>占比</td>";
|
|
}
|
|
$content_data.="<td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>件数</td><td>占比</td><td>金额</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
$con2="";
|
|
if($s_group_by1!="sx")
|
|
$con2.=" and ".$s_group_by1."<>''";
|
|
if($s_group_by2!="sx")
|
|
$con2.=" and ".$s_group_by2."<>''";
|
|
|
|
//取得总款数
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where no<>'' ".$con2.$con1);
|
|
$total_ks=$tmp["num"];
|
|
//取得总款色数
|
|
$total_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where no<>'' ".$con2.$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$arr1=explode(",",$m2["color"]);
|
|
$arr2=explode(",",$m2["color_ka"]);
|
|
for($i1=0;$i1<count($arr1);$i1++){
|
|
if(!in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}
|
|
}
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
//取得总均价
|
|
$tmp=get_db_msg("select sum(price)/count(*) 'jj' from ".$table_pre."kh where no<>'' ".$con2.$con1);
|
|
$price_jj=keep_float($tmp["jj"],1);
|
|
}
|
|
|
|
//取得已订总件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where ".$s_group_by2."<>'' and ".$s_group_by1."<>'' ".$con);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//取得已订总款数
|
|
$tmp=get_db_msg("select count(distinct no) 'num' from ".$table_pre."order where ".$s_group_by2."<>'' and ".$s_group_by1."<>'' ".$con);
|
|
$total_yd_ks=$tmp["num"];
|
|
//取得已订总款色数
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by2."<>'' and ".$s_group_by1."<>'' ".$con);
|
|
$total_yd_sku=$tmp["num"];
|
|
//
|
|
$q4="select * from ".$table_pre."keyword where cate='".$s_group_by1."' order by no";
|
|
$r4=$db->query($q4);
|
|
$count=$l=$total_num=$total_money=0;
|
|
while($m4=$db->fetch_array($r4)){
|
|
$con11=($s_group_by2=="sx" || $s_group_by2=="color")?"":" and ".$s_group_by2."<>''";
|
|
if($s_group_by1=="color"){
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where concat(',',color,',') like '%,".$m4["no"].",%'".$con11.$con1);
|
|
$xj_z_sku=$tmp["num"];
|
|
}else if($s_group_by1=="sx"){
|
|
//取得设计款色
|
|
$tmp=get_db_msg("select * from ".$table_pre."sx where no='".$m4["no"]."'");
|
|
$arr_color=explode(",",$tmp["_v"]);
|
|
$n1=0;
|
|
for($i1=0;$i1<count($arr_color);$i1++){
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where concat(',',color,',') like '%,".$arr_color[$i1].",%'".$con11.$con1);
|
|
$n1+=$tmp["num"];
|
|
}
|
|
$xj_z_sku=$n1;
|
|
}else{
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."'".$con11.$con1);
|
|
$xj_z_ks=$tmp["num"];
|
|
//取得小计设计款色数
|
|
$xj_z_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."'".$con11.$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$arr1=explode(",",$m2["color"]);
|
|
$arr2=explode(",",$m2["color_ka"]);
|
|
for($i1=0;$i1<count($arr1);$i1++){
|
|
if(!in_array($arr1[$i1],$arr2))
|
|
$xj_z_sku++;
|
|
}
|
|
}
|
|
}
|
|
|
|
//
|
|
$q="select * from ".$table_pre."keyword where cate='".$s_group_by2."' order by no";
|
|
//echo $q."<br>";
|
|
$r=$db->query($q);
|
|
$l=0;
|
|
//取得已订小计款数
|
|
$tmp=get_db_msg("select count(distinct no) 'num' from ".$table_pre."order where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."<>'' and ".$s_group_by1."<>'' ".$con);
|
|
$xj_yd_ks=$tmp["num"];
|
|
//取得已订小计款色数
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by1."<>'' and ".$s_group_by2."<>'' ".$con);
|
|
$xj_yd_sku=$tmp["num"];
|
|
//取得已订小计件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by1."<>'' and ".$s_group_by2."<>'' ".$con);
|
|
$xj_yd_js=$tmp["num"];
|
|
$xj_yd_je=$tmp["je"];
|
|
//
|
|
while($m=$db->fetch_array($r)){
|
|
$list=$count+1;
|
|
$gtitle=get_cate_title_no($s_group_by,$m[$s_group_by]);
|
|
//
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
//取得款数
|
|
$tmp=get_db_msg("select count(distinct no) 'num' from ".$table_pre."order where id>0 and ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."'".$con);
|
|
$tmp_yd_ks=$tmp["num"];
|
|
if($s_group_by1=="color")
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where concat(',',color,',') like '%,".$m4["no"].",%' and ".$s_group_by2."='".$m["no"]."'".$con1);
|
|
else if($s_group_by2=="color")
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."' and concat(',',color,',') like '%,".$m["no"].",%'".$con1);
|
|
else
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."'".$con1);
|
|
$tmp_z_ks=$tmp["num"];
|
|
}
|
|
|
|
//取得款色
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where id>0 and ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."'".$con);
|
|
$tmp_yd_sku=$tmp["num"];
|
|
if($s_group_by1=="sx" || $s_group_by2=="sx"){
|
|
if($s_group_by1=="sx"){
|
|
$con11=" no='".$m4["no"]."'";
|
|
$con12=$s_group_by2."='".$m["no"]."'";
|
|
}else{
|
|
$con11=" no='".$m["no"]."'";
|
|
$con12=$s_group_by1."='".$m4["no"]."'";
|
|
}
|
|
//取得设计款色
|
|
$tmp=get_db_msg("select * from ".$table_pre."sx where ".$con11);
|
|
$arr_color=explode(",",$tmp["_v"]);
|
|
$n1=0;
|
|
for($i1=0;$i1<count($arr_color);$i1++){
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ka<>'y' and concat(',',color,',') like '%,".$arr_color[$i1].",%' and ".$con12);
|
|
$n1+=$tmp["num"];
|
|
}
|
|
$tmp_z_sku=$n1;
|
|
}else if($s_group_by1=="color" || $s_group_by2=="color"){
|
|
if($s_group_by1=="color"){
|
|
$con11=$m4["no"];
|
|
$con12=$s_group_by2."='".$m["no"]."'";
|
|
}else{
|
|
$con11=$m["no"];
|
|
$con12=$s_group_by1."='".$m4["no"]."'";
|
|
}
|
|
//取得设计款色
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$con12." and concat(',',color,',') like '%,".$con11.",%'".$con1);
|
|
$tmp_z_sku=$tmp["num"];
|
|
}else{
|
|
$tmp_z_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."' ".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$n1=count(explode(",",$m2["color"]));
|
|
$n2=trim($m2["color_ka"])?count(explode(",",$m2["color_ka"])):0;
|
|
$tmp_z_sku+=$n1-$n2;
|
|
}
|
|
}
|
|
|
|
//取得数量
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."'".$con);
|
|
$tmp_yd_js=$tmp["num"];
|
|
$tmp_yd_je=$tmp["je"];
|
|
//
|
|
if($tmp_z_sku>0){
|
|
$content_data.="<tr align=center>";
|
|
$content_data.="<td>".$m4["title"]."</td>";
|
|
$content_data.="<td>".$m["title"]."</td>";
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
//取得均价
|
|
$tmp=get_db_msg("select sum(price)/count(*) 'jj' from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."='".$m["no"]."'".$con1);
|
|
$content_data.="<td>".keep_float($tmp["jj"],1)."</td>";
|
|
$content_data.="<td>".$tmp_z_ks."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_z_ks,$xj_z_ks)."</td>";
|
|
$content_data.="<td>".$tmp_yd_ks."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_yd_ks,$xj_yd_ks)."</td>";
|
|
}
|
|
$content_data.="<td>".$tmp_z_sku."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_z_sku,$xj_z_sku)."</td>";
|
|
$content_data.="<td>".$tmp_yd_sku."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_yd_sku,$xj_yd_sku)."</td>";
|
|
$content_data.="<td>".deel_01($tmp_yd_js)."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_yd_js,$xj_yd_js)."</td>";
|
|
$content_data.="<td>".deel_01($tmp_yd_je)."</td>";
|
|
$content_data.="<td>".deel_bfb($tmp_yd_je,$xj_yd_je)."</td>";
|
|
$content_data.="</tr>";
|
|
$count++;
|
|
}
|
|
}
|
|
if($xj_z_sku>0){
|
|
$content_data.="<tr align=center>";
|
|
$content_data.="<td colspan=2>小计</td>";
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
//取得均价
|
|
$tmp=get_db_msg("select sum(price)/count(*) 'jj' from ".$table_pre."kh where ".$s_group_by1."='".$m4["no"]."' and ".$s_group_by2."<>''".$con1);
|
|
$content_data.="<td>".keep_float($tmp["jj"],1)."</td>";
|
|
$content_data.="<td>".$xj_z_ks."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_z_ks,$total_ks)."</td>";
|
|
$content_data.="<td>".$xj_yd_ks."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_yd_ks,$total_yd_ks)."</td>";
|
|
}
|
|
$content_data.="<td>".$xj_z_sku."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_z_sku,$total_sku)."</td>";
|
|
$content_data.="<td>".$xj_yd_sku."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_yd_sku,$total_yd_sku)."</td>";
|
|
$content_data.="<td>".deel_num($xj_yd_js)."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_yd_js,$total_yd_js)."</td>";
|
|
$content_data.="<td>".deel_num($xj_yd_je)."</td>";
|
|
$content_data.="<td>".deel_bfb($xj_yd_je,$total_yd_je)."</td>";
|
|
$content_data.="</tr>";
|
|
$count++;
|
|
}
|
|
}
|
|
$content_data.="</tbody>";
|
|
//
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center>";
|
|
$content_data.="<td colspan=2>合计</td>";
|
|
|
|
if($s_group_by1!="sx" && $s_group_by2!="sx" && $s_group_by1!="color" && $s_group_by2!="color"){
|
|
$content_data.="<td>".$price_jj."</td>";
|
|
$content_data.="<td>".$total_ks."</td>";
|
|
$content_data.="<td></td>";
|
|
$content_data.="<td>".deel_num($total_yd_ks)."</td>";
|
|
$content_data.="<td></td>";
|
|
}
|
|
$content_data.="<td>".deel_num($total_sku)."</td>";
|
|
$content_data.="<td></td>";
|
|
$content_data.="<td>".deel_num($total_yd_sku)."</td>";
|
|
$content_data.="<td></td>";
|
|
$content_data.="<td>".deel_num($total_yd_js)."</td>";
|
|
$content_data.="<td></td>";
|
|
$content_data.="<td>".deel_num($total_yd_je)."</td>";
|
|
$content_data.="<td></td>";
|
|
$content_data.="</tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}
|
|
}else if($zd1=="" && $zd2==""){
|
|
//请求数据有误
|
|
$content_data="<div align='center'>请先配置参数!</div>";
|
|
$json_data.="\"sys_bb_type\":\"bball_null\",";
|
|
}else{
|
|
//---------------------------------------一维报表
|
|
$json_data.="\"sys_bb_type\":\"bball_1w\",";
|
|
if($zd1!=""){
|
|
$s_group_by=$zd1;
|
|
}else{
|
|
$s_group_by=$zd2;
|
|
}
|
|
|
|
if($s_group_by=="ks_size"){
|
|
//取得已订款色数
|
|
$tmp=get_db_msg("select count(distinct iid,color) 'num' from ".$table_pre."order where id>0 ".$con);
|
|
$total_kss=$tmp["num"];
|
|
//
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td>件数</td><td>款色数</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//
|
|
$tb_data_x="";
|
|
$tb_data_y=",";
|
|
$q="select sum(num)/count(distinct iid,color) 'num',count(distinct iid,color) 'kss' from (select sum(num) 'num',iid,color from ".$table_pre."order where id>0".$con." group by iid,color) as t1 group by num order by num";
|
|
$r=$db->query($q);
|
|
$count=0;
|
|
$l=0;
|
|
while($m=$db->fetch_array($r)){
|
|
//
|
|
$tb_data_x.=deel_num($m["num"])."件,";
|
|
$tb_data_y.=str_replace("%","",deel_bfb($m["kss"],$total_kss)).",";
|
|
//
|
|
$content_data.="<tr align=center><td>".deel_num($m["num"])."</td><td>".$m["kss"]."</td><td>".deel_bfb($m["kss"],$total_kss)."</td></tr>";
|
|
$count++;
|
|
$l+=$m["num"]*$m["kss"];
|
|
}
|
|
|
|
//
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr align=center style='background-color:#f2f2f2'><td>合计(".$l.")</td><td>".$total_kss."</td><td>100%</td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}else if($s_group_by=="size"){
|
|
//取得已订总件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0".$con);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td>尺码组</td><td>尺码</td><td>已订件数</td><td>件数占比</td><td>吊牌金额</td><td>金额占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//
|
|
$tb_data_x="";
|
|
$tb_data_y=",";
|
|
|
|
//
|
|
$tbody_str="";
|
|
$q1="select * from ".$table_pre."keyword where cate='size' order by ord";
|
|
$r1=$db->query($q1);
|
|
$count=$total_num=0;
|
|
$l=0;
|
|
while($m1=$db->fetch_array($r1)){
|
|
$q="select * from ".$table_pre."size where iid='".$m1["id"]."' order by iid,no";
|
|
$r=$db->query($q);
|
|
//
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and size_group_id='".$m1["id"]."'".$con);
|
|
$xj_yd_js=$tmp["num"];
|
|
$xj_yd_je=$tmp["je"];
|
|
while($m=$db->fetch_array($r)){
|
|
$arr1=get_num_je_size($m["no"],$m["iid"]);
|
|
$total_num+=$arr1[0];
|
|
$total_money+=$arr1[1];
|
|
$content_data.="<tr align=center><td>".$m1["title"]."</td><td>".$m["title"]."</td><td>".$arr1[0]."</td><td>".deel_bfb($arr1[0],$total_yd_js)."</td><td>".$arr1[1]."</td><td>".deel_bfb($arr1[1],$total_yd_je)."</td></tr>";
|
|
$count++;
|
|
//
|
|
$tb_data_x.=$m["title"].",";
|
|
$tb_data_y.=str_replace("%","",deel_bfb($arr1[0],$total_yd_js)).",";
|
|
}
|
|
$l++;
|
|
}
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr align=center style='background-color:#f2f2f2'><td></td><td></td><td>".$total_num."</td><td></td><td>".$total_money."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}else if($s_group_by=="color"){
|
|
//取得总款数
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where no<>''".$con1);
|
|
$total_ks=$tmp["num"];
|
|
//取得总款色数
|
|
$total_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where no<>''".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$arr1=explode(",",$m2["color"]);
|
|
$arr2=explode(",",$m2["color_ka"]);
|
|
for($i1=0;$i1<count($arr1);$i1++){
|
|
if($s_ka=="y"){
|
|
if(in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}else{
|
|
if(!in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}
|
|
}
|
|
}
|
|
//取得已订总件数和金额
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0".$con);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//取得已订总款色数
|
|
$tmp1=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."<>'' ".$con);
|
|
$total_yd_sku=$tmp1["num"];
|
|
//
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td rowspan=2>颜色</td><td colspan=2>设计款色</td><td colspan=2>已订款色</td><td colspan=2>已订件数</td><td colspan=2>吊牌金额</td></tr>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center><td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>件数</td><td>占比</td><td>金额</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//
|
|
$tb_data_x="";
|
|
$tb_data_y=",";
|
|
//
|
|
$q="select * from ".$table_pre."keyword where cate='".$s_group_by."' order by no";
|
|
$r=$db->query($q);
|
|
$count=$total_num=$total_money=0;
|
|
while($m=$db->fetch_array($r)){
|
|
$list=$count+1;
|
|
//取得数量
|
|
$m1=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and ".$s_group_by."='".$m["no"]."'".$con);
|
|
$total_num+=$m1["num"];
|
|
$total_money+=$m1["je"];
|
|
//取得款色
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_sku=$tmp["num"];
|
|
$tmp_z_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where no<>''".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$tmp1=explode(",",$m2["color"]);
|
|
$tmp2=explode(",",$m2["color_ka"]);
|
|
if(in_array($m["no"],$tmp1) && !in_array($m["no"],$tmp2)){
|
|
$tmp_z_sku++;
|
|
}
|
|
}
|
|
//
|
|
$content_data.="<tr align=center><td>".$m["title"]."</td><td>".$tmp_z_sku."</td><td>".deel_bfb($tmp_z_sku,$total_sku)."</td><td>".$tmp_yd_sku."</td><td>".deel_bfb($tmp_yd_sku,$total_yd_sku)."</td><td>".deel_01($m1["num"])."</td><td>".deel_bfb($m1["num"],$total_yd_js)."</td><td>".deel_01($m1["je"])."</td><td>".deel_bfb($m1["je"],$total_yd_je)."</td></tr>";
|
|
//
|
|
$tb_data_x.=$m["title"].",";
|
|
$tb_data_y.=str_replace("%","",deel_bfb($m1["num"],$total_yd_js)).",";
|
|
}
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr align=center style='background-color:#f2f2f2'><td>合计</td><td>".$total_sku."</td><td></td><td>".$total_yd_sku."</td><td></td><td>".$total_num."</td><td></td><td>".$total_money."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
|
|
}else if($s_group_by=="sx"){
|
|
//取得总款色数
|
|
$total_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where no<>''".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$arr1=explode(",",$m2["color"]);
|
|
$arr2=explode(",",$m2["color_ka"]);
|
|
for($i1=0;$i1<count($arr1);$i1++){
|
|
if($s_ka=="y"){
|
|
if(in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}else{
|
|
if(!in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}
|
|
}
|
|
}
|
|
//取得已订总件数和金额
|
|
$q="select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where ".$s_group_by."<>'' ".$con;
|
|
$tmp=get_db_msg($q);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//取得已订总款色数
|
|
$tmp1=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."<>'' ".$con);
|
|
$total_yd_sku=$tmp1["num"];
|
|
//
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td rowspan=2>色系</td><td colspan=2>设计款色</td><td colspan=2>已订款色</td><td colspan=2>已订件数</td><td colspan=2>吊牌金额</td></tr>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center><td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>件数</td><td>占比</td><td>金额</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//
|
|
$tb_data_x="";
|
|
$tb_data_y=",";
|
|
//
|
|
$tbody_str="";
|
|
$q="select * from ".$table_pre."keyword where cate='".$s_group_by."' order by no";
|
|
$r=$db->query($q);
|
|
$count=$total_num=$total_money=0;
|
|
while($m=$db->fetch_array($r)){
|
|
$list=$count+1;
|
|
$gtitle=get_cate_title_no($s_group_by,$m[$s_group_by]);
|
|
|
|
//取得设计款色
|
|
$tmp=get_db_msg("select * from ".$table_pre."sx where no='".$m["no"]."'");
|
|
$arr_color=explode(",",$tmp["_v"]);
|
|
$n1=0;
|
|
for($i1=0;$i1<count($arr_color);$i1++){
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where concat(',',color,',') like '%,".$arr_color[$i1].",%'".$con1);
|
|
$n1+=$tmp["num"];
|
|
}
|
|
$tmp_sku=$n1;
|
|
//取得款色
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_sku=$tmp["num"];
|
|
//取得数量
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_js=$tmp["num"];
|
|
$tmp_yd_je=$tmp["je"];
|
|
//
|
|
$content_data.="<tr align=center><td>".$m["title"]."</td><td>".deel_01($tmp_sku)."</td><td>".deel_bfb($tmp_sku,$total_sku)."</td><td>".deel_01($tmp_yd_sku)."</td><td>".deel_bfb($tmp_yd_sku,$total_yd_sku)."</td><td>".deel_01($tmp_yd_js)."</td><td>".deel_bfb($tmp_yd_js,$total_yd_js)."</td><td>".deel_01($tmp_yd_je)."</td><td>".deel_bfb($tmp_yd_je,$total_yd_je)."</td></tr>";
|
|
|
|
$count++;
|
|
//
|
|
$tb_data_x.=$m["title"].",";
|
|
$tb_data_y.=str_replace("%","",deel_bfb($tmp_yd_js,$total_yd_js)).",";
|
|
}
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr align=center style='background-color:#f2f2f2'><td>合计</td><td>".$total_sku."</td><td></td><td>".$total_yd_sku."</td><td></td><td>".$total_yd_js."</td><td></td><td>".$total_yd_je."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}else{
|
|
//取得总款数
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$s_group_by."<>''".$con1);
|
|
$total_ks=$tmp["num"];
|
|
//取得总款色数
|
|
$total_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where ".$s_group_by."<>''".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$arr1=explode(",",$m2["color"]);
|
|
$arr2=explode(",",$m2["color_ka"]);
|
|
for($i1=0;$i1<count($arr1);$i1++){
|
|
if(!in_array($arr1[$i1],$arr2))
|
|
$total_sku++;
|
|
}
|
|
}
|
|
//取得总均价
|
|
$tmp=get_db_msg("select sum(price)/count(*) 'jj' from ".$table_pre."kh where ".$s_group_by."<>''".$con1);
|
|
$price_jj=keep_float($tmp["jj"],1);
|
|
//取得已订总件数和金额
|
|
$q="select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where ".$s_group_by."<>'' ".$con;
|
|
$tmp=get_db_msg($q);
|
|
$total_yd_js=$tmp["num"];
|
|
$total_yd_je=$tmp["je"];
|
|
//取得已订总款数
|
|
$tmp1=get_db_msg("select count(distinct no) 'num' from ".$table_pre."order where ".$s_group_by."<>'' ".$con);
|
|
$total_yd_ks=$tmp1["num"];
|
|
//取得已订总款色数
|
|
$tmp1=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."<>'' ".$con);
|
|
$total_yd_sku=$tmp1["num"];
|
|
|
|
//
|
|
$content_data="";
|
|
$content_data.="<table id='tabledetail' class='cell-border nowrap' width='100%' lay-filter='layui_table' cellspacing='0' cellpadding='0' border='0' class='layui-table'>";
|
|
$content_data.="<thead><tr style='background-color:#f2f2f2' align=center><td rowspan=2>".get_cate_title_no("arr_select",$s_group_by)."</td><td rowspan=2>均价</td><td colspan=2>设计款数</td><td colspan=2>已订款数</td><td colspan=2>设计款色</td><td colspan=2>已订款色</td><td colspan=2>已订件数</td><td colspan=2>吊牌金额</td></tr>";
|
|
$content_data.="<tr style='background-color:#f2f2f2' align=center><td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>款数</td><td>占比</td><td>件数</td><td>占比</td><td>金额</td><td>占比</td></tr>";
|
|
$content_data.="</thead>";
|
|
$content_data.="<tbody>";
|
|
//
|
|
$tb_data_x="";
|
|
$tb_data_y=",";
|
|
//
|
|
$tbody_str="";
|
|
$q="select * from ".$table_pre."keyword where cate='".$s_group_by."' order by no";
|
|
$r=$db->query($q);
|
|
$count=$total_num=$total_money=0;
|
|
while($m=$db->fetch_array($r)){
|
|
$list=$count+1;
|
|
if($s_group_by=="color"){
|
|
$gtitle=get_cate_title_no("color",$m["color"]);
|
|
}else{
|
|
$gtitle=get_cate_title_no($s_group_by,$m[$s_group_by]);
|
|
}
|
|
//------取得款数
|
|
$tmp=get_db_msg("select count(distinct no) 'num' from ".$table_pre."order where ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_ks=$tmp["num"];
|
|
$tmp=get_db_msg("select count(*) 'num' from ".$table_pre."kh where ".$s_group_by."='".$m["no"]."'".$con1);
|
|
$tmp_z_ks=$tmp["num"];
|
|
//取得款色
|
|
$tmp=get_db_msg("select count(distinct no,color) 'num' from ".$table_pre."order where ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_sku=$tmp["num"];
|
|
$tmp_z_sku=0;
|
|
$q2="select no,color,color_ka from ".$table_pre."kh where ".$s_group_by."='".$m["no"]."'".$con1;
|
|
$r2=$db->query($q2);
|
|
while($m2=$db->fetch_array($r2)){
|
|
$n1=count(explode(",",$m2["color"]));
|
|
$n2=trim($m2["color_ka"])?count(explode(",",$m2["color_ka"])):0;
|
|
$tmp_z_sku+=$n1-$n2;
|
|
}
|
|
//取得数量
|
|
$tmp=get_db_msg("select sum(num) 'num',sum(je) 'je' from ".$table_pre."order where id>0 and ".$s_group_by."='".$m["no"]."'".$con);
|
|
$tmp_yd_js=$tmp["num"];
|
|
$tmp_yd_je=$tmp["je"];
|
|
if($tmp_z_ks>0){
|
|
//取得均价
|
|
$tmp=get_db_msg("select sum(price)/count(*) 'jj' from ".$table_pre."kh where ".$s_group_by."='".$m["no"]."'".$con1);
|
|
//
|
|
$tb_data_x.=$m["title"].",";
|
|
$tb_data_y.=str_replace("%","",deel_bfb($tmp_yd_js,$total_yd_js)).",";
|
|
$content_data.="<tr align=center><td>".$m["title"]."</td><td>".keep_float($tmp["jj"],1)."</td><td>".$tmp_z_ks."</td><td>".deel_bfb($tmp_z_ks,$total_ks)."</td><td>".$tmp_yd_ks."</td><td>".deel_bfb($tmp_yd_ks,$total_yd_ks)."</td><td>".$tmp_z_sku."</td><td>".deel_bfb($tmp_z_sku,$total_sku)."</td><td>".$tmp_yd_sku."</td><td>".deel_bfb($tmp_yd_sku,$total_yd_sku)."</td><td>".deel_01($tmp_yd_js)."</td><td>".deel_bfb($tmp_yd_js,$total_yd_js)."</td><td>".deel_01($tmp_yd_je)."</td><td>".deel_bfb($tmp_yd_je,$total_yd_je)."</td></tr>";
|
|
$count++;
|
|
}
|
|
}
|
|
|
|
$content_data.="</tbody>";
|
|
$content_data.="<tfoot>";
|
|
$content_data.="<tr align=center style='background-color:#f2f2f2'><td>合计</td><td>".$price_jj."</td><td>".$total_ks."</td><td></td><td>".$total_yd_ks."</td><td></td><td>".$total_sku."</td><td></td><td>".$total_yd_sku."</td><td></td><td>".$total_yd_js."</td><td></td><td>".$total_yd_je."</td><td></td></tr>";
|
|
$content_data.="</tfoot>";
|
|
$content_data.="</table>";
|
|
}
|
|
|
|
//图
|
|
$tb_data_y=deel_end($tb_data_y);
|
|
$tb_data_x=deel_end($tb_data_x);
|
|
$json_data.="\"chart_title\":\"\",";
|
|
$json_data.="\"chart_type\":\"pie\",";
|
|
$json_data.="\"chart_data_x\":\"".$tb_data_x."\",";
|
|
$json_data.="\"chart_data_y\":\"".$tb_data_y."\",";
|
|
|
|
}
|
|
$json_data.="\"table_html\":\"".$content_data."\",";
|
|
?>
|