=",$starttime]; // } // if($endtime!=""){ // $condition[]=["Date1617672422547","<=",$endtime]; // } $db =Db::connect("mysql2"); // if($endtime==""&& $starttime==""){ $list = $db->name("stats_tj")->field("p,total,wtotal,dtotal")->select()->toArray(); // }else{ // $list = $db->name("il150_querendan")->field("ShortText1619541934512 as p,sum(`Number1617648665166` ) AS `total`,0 wtotal,0 dtotal")->where // ($condition)->group("p")->select() // ->toArray(); // } $tips = Db::name("depart_tips")->field("department,total_tips")->where('year=CONCAT(YEAR(NOW()),"年") and month=CONCAT(month(NOW()),"月")')->select(); $data=[]; $temp=["p"=>"总计","total"=>0,"wtotal"=>0,"dtotal"=>0,"tips"=>0,"rate"=>0]; foreach ($list as $key=>$value){ $value['tips']=0; foreach ($tips as $val){ if($value["p"]==$val['department']){ $value['tips']=$val['total_tips']; } } $temp['total']+=$value['total']; $temp['wtotal']+=$value['wtotal']; $temp['dtotal']+=$value['dtotal']; $temp['tips']+=$value['tips']; $data[]=$value; } if($temp['tips']==0){ $temp['rate']="-"; $temp['tips']="-"; }else{ $temp['rate'] =number_format( $temp['total']/$temp['tips']*100,2); $temp['tips'] =round( $temp['tips'],2); } $temp['total'] = sprintf( "%.2f",$temp['total']); $temp['wtotal'] = sprintf( "%.2f",$temp['wtotal']); $temp['dtotal'] = sprintf( "%.2f",$temp['dtotal']); array_walk($data,function (&$value){ if(is_null($value['tips']) || $value['tips']==0){ $value['tips']="-"; $value["rate"]="-"; }else{ $value["rate"]=number_format( $value['total']/$value['tips']*100,2); $value['tips'] = sprintf( "%.2f",$value['tips']); } $value['total'] = sprintf( "%.2f",$value['total']); $value['wtotal'] = sprintf( "%.2f",$value['wtotal']); $value['dtotal'] = sprintf( "%.2f",$value['dtotal']); }); $sort = array_column($data,"rate"); array_multisort($sort,SORT_ASC,$data); array_push($data,$temp); $data = array_reverse($data); return app_show(0,"获取成功",$data); } /** * 显示创建资源表单页. * * @return \think\Response */ public function create() { // $post=\think\facade\Request::instance(); // $endtime = isset($post['endtime']) ? $post['endtime'] : ""; // $starttime = isset($post['starttime']) ? $post['starttime'] : ""; // $condition=[]; // if($starttime!=""){ // $condition[]=["createdTime",">=",$starttime]; // } // if($endtime!=""){ // $condition[]=["createdTime","<=",$endtime]; // } $db =Db::connect("mysql2"); $list = $db->name("stats_th")->select()->toArray(); $temp=["p"=>"总计","thfee"=>0,"wthfee"=>0,"mthfee"=>0,"m"=>0]; foreach ($list as $key=>$value){ $temp['thfee'] += $value['thfee']; $temp['wthfee'] += $value['wthfee']; $temp['mthfee'] += $value['mthfee']; $temp['m'] = $value['m']; } $temp['thfee'] = sprintf( "%.2f",$temp['thfee']); $temp['wthfee'] = sprintf( "%.2f",$temp['wthfee']); $temp['mthfee'] = sprintf( "%.2f",$temp['mthfee']); array_unshift($list,$temp); return app_show(0,"获取成功",$list); } /** * 保存新建的资源 * * @param \think\Request $request * @return \think\Response */ public function save() { $db =Db::connect("mysql2"); // if($endtime==""&& $starttime==""){ $list = $db->name("stats_thj")->field("p,total,wtotal,dtotal,thfee,wthfee,mthfee")->select()->toArray(); // $list2 = $db->name("stats_th")->field("p,thfee,wthfee,mthfee")->select()->toArray(); // }else{ // $list = $db->name("il150_querendan")->field("ShortText1619541934512 as p,sum(`Number1617648665166` ) AS `total`,0 wtotal,0 dtotal")->where // ($condition)->group("p")->select() // ->toArray(); // } $tips = Db::name("depart_tips")->field("department,total_tips")->where('year=CONCAT(YEAR(NOW()),"年") and month=CONCAT(month(NOW()),"月")')->select(); $data=[]; $temp=["p"=>"总计","thtotal"=>0,"total"=>0,"mthfee"=>0,"wtotal"=>0,"wthfee"=>0,"dtotal"=>0,"thfee"=>0,"tips"=>0, "rate"=>0,"thrate"=>0]; foreach ($list as $key=>$value){ $value['tips']=0; foreach ($tips as $val){ if($value["p"]==$val['department']){ $value['tips']=$val['total_tips']; } } $value['thtotal'] = $value['total']-$value['mthfee']; $temp['total']+=$value['total']; $temp['mthfee']+=$value['mthfee']; $temp['wtotal']+=$value['wtotal']; $temp['wthfee']+=$value['wthfee']; $temp['thfee']+=$value['thfee']; $temp['dtotal']+=$value['dtotal']; $temp['tips']+=$value['tips']; $temp['thtotal']+=$value['thtotal']; $data[]=$value; } if($temp['tips']==0){ $temp['rate']="-"; $temp['thrate']="-"; $temp['tips']="-"; }else{ $temp['rate'] =number_format( $temp['total']/$temp['tips']*100,2); $temp['thrate']=number_format( ($temp['total']- $temp['mthfee'])/$temp['tips']*100,2);; $temp['tips'] =round( $temp['tips'],2); } $temp['total'] = sprintf( "%.2f",$temp['total']); $temp['mthfee'] = sprintf( "%.2f",$temp['mthfee']); $temp['wtotal'] = sprintf( "%.2f",$temp['wtotal']); $temp['wthfee'] = sprintf( "%.2f",$temp['wthfee']); $temp['dtotal'] = sprintf( "%.2f",$temp['dtotal']); $temp['thfee'] = sprintf( "%.2f",$temp['thfee']); $temp['thtotal'] = sprintf( "%.2f",$temp['thtotal']); array_walk($data,function (&$value){ if(is_null($value['tips']) || $value['tips']==0){ $value['tips']="-"; $value["rate"]="-"; $value["thrate"]="-"; }else{ $value["rate"]=number_format( $value['total']/$value['tips']*100,2); $value["thrate"]=number_format( ($value['total']- $value['mthfee'])/$value['tips']*100,2); $value['tips'] = sprintf( "%.2f",$value['tips']); } $value['total'] = sprintf( "%.2f",$value['total']); $value['mthfee'] = sprintf( "%.2f",$value['mthfee']); $value['wtotal'] = sprintf( "%.2f",$value['wtotal']); $value['wthfee'] = sprintf( "%.2f",$value['wthfee']); $value['dtotal'] = sprintf( "%.2f",$value['dtotal']); $value['thfee'] = sprintf( "%.2f",$value['thfee']); $value['thtotal'] = sprintf( "%.2f",$value['thtotal']); }); $sort = array_column($data,"thrate"); array_multisort($sort,SORT_ASC,$data); array_push($data,$temp); $data = array_reverse($data); return app_show(0,"获取成功",$data); } /** * 显示指定的资源 * * @param int $id * @return \think\Response */ public function read() { $post=request()->post(); $endtime = isset($post['date']) ? $post['date'] : date("Y-m-d"); $list = DB::query(" SELECT a.suppitem as p, m, a.sale_total AS total, `a`.`th_total` AS `mthfee`, IFNULL(w,WEEK('{$endtime}',1)) as w, IFNULL(b.sale_total,0) AS wtotal, ifnull( `b`.`th_total`, 0 ) AS `wthfee`, IFNULL(c.d,date_format('{$endtime}', '%Y-%m-%d' )) as d, IFNULL(c.sale_total,0) AS dtotal, ifnull( `c`.`th_total`, 0 ) AS `thfee` FROM ( SELECT MONTH ( ordertime ) AS m, sum( sale_total ) AS sale_total, suppitem, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l ON ( l.productNo = t.th_qrdcpNo AND t.th_qrdcpNo <> '' ) WHERE l.suppitem = k.suppitem AND MONTH ( t.createtime ) = MONTH ( '{$endtime}' ) ) AS th_total FROM source_all k WHERE MONTH ( ordertime ) = MONTH ( '{$endtime}') GROUP BY suppitem, m ) AS a LEFT JOIN ( SELECT WEEK ( ordertime, 1 ) AS w, sum( sale_total ) AS sale_total, suppitem, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l ON ( l.productNo = t.th_qrdcpNo AND t.th_qrdcpNo <> '' ) WHERE l.suppitem = k.suppitem AND week ( t.createtime,1 ) = WEEK ( '{$endtime}', 1 ) ) AS th_total FROM source_all k WHERE WEEK ( ordertime, 1 ) = WEEK ( '{$endtime}', 1 ) GROUP BY suppitem, w ) AS b ON a.suppitem = b.suppitem LEFT JOIN ( SELECT date_format( ordertime, '%Y-%m-%d' ) AS d, sum( sale_total ) AS sale_total, suppitem, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l ON ( l.productNo = t.th_qrdcpNo AND t.th_qrdcpNo <> '' ) WHERE l.suppitem = k.suppitem AND date_format ( t.createtime,'%Y-%m-%d') = date_format ( '{$endtime}', '%Y-%m-%d' ) ) AS th_total FROM source_all k WHERE date_format( ordertime, '%Y-%m-%d' ) = date_format( '{$endtime}', '%Y-%m-%d' ) GROUP BY suppitem, d ) AS c ON a.suppitem = c.suppitem "); $tips = Db::name("depart_tips")->field("department,total_tips")->where('year=CONCAT(YEAR(NOW()),"年") and month=CONCAT(month(NOW()),"月")')->select(); $data=[]; $temp=["p"=>"总计","thtotal"=>0,"total"=>0,"mthfee"=>0,"wtotal"=>0,"wthfee"=>0,"dtotal"=>0,"thfee"=>0,"tips"=>0, "rate"=>0,"thrate"=>0]; foreach ($list as $key=>$value){ $value['tips']=0; foreach ($tips as $val){ if($value["p"]==$val['department']){ $value['tips']=$val['total_tips']; } } $value['thtotal'] = $value['total']-$value['mthfee']; $temp['total']+=$value['total']; $temp['mthfee']+=$value['mthfee']; $temp['wtotal']+=$value['wtotal']; $temp['wthfee']+=$value['wthfee']; $temp['thfee']+=$value['thfee']; $temp['dtotal']+=$value['dtotal']; $temp['tips']+=$value['tips']; $temp['thtotal']+=$value['thtotal']; $data[]=$value; } if($temp['tips']==0){ $temp['rate']="-"; $temp['thrate']="-"; $temp['tips']="-"; }else{ $temp['rate'] =number_format( $temp['total']/$temp['tips']*100,2); $temp['thrate']=number_format( ($temp['total']- $temp['mthfee'])/$temp['tips']*100,2);; $temp['tips'] =round( $temp['tips'],2); } $temp['total'] = sprintf( "%.2f",$temp['total']); $temp['mthfee'] = sprintf( "%.2f",$temp['mthfee']); $temp['wtotal'] = sprintf( "%.2f",$temp['wtotal']); $temp['wthfee'] = sprintf( "%.2f",$temp['wthfee']); $temp['dtotal'] = sprintf( "%.2f",$temp['dtotal']); $temp['thfee'] = sprintf( "%.2f",$temp['thfee']); $temp['thtotal'] = sprintf( "%.2f",$temp['thtotal']); array_walk($data,function (&$value){ if(is_null($value['tips']) || $value['tips']==0){ $value['tips']="-"; $value["rate"]="-"; $value["thrate"]="-"; }else{ $value["rate"]=number_format( $value['total']/$value['tips']*100,2); $value["thrate"]=number_format( ($value['total']- $value['mthfee'])/$value['tips']*100,2); $value['tips'] = sprintf( "%.2f",$value['tips']); } $value['total'] = sprintf( "%.2f",$value['total']); $value['mthfee'] = sprintf( "%.2f",$value['mthfee']); $value['wtotal'] = sprintf( "%.2f",$value['wtotal']); $value['wthfee'] = sprintf( "%.2f",$value['wthfee']); $value['dtotal'] = sprintf( "%.2f",$value['dtotal']); $value['thfee'] = sprintf( "%.2f",$value['thfee']); $value['thtotal'] = sprintf( "%.2f",$value['thtotal']); }); $sort = array_column($data,"thrate"); array_multisort($sort,SORT_ASC,$data); array_push($data,$temp); $data = array_reverse($data); return app_show(0,"获取成功",$data); } /** * 显示编辑资源表单页. * * @param int $id * @return \think\Response */ public function list() { $post=request()->post(); $endtime = isset($post['date']) ? $post['date'] : date("Y-m-d"); $list = DB::query(" SELECT a.suppitem as p, a.depart, m, a.sale_total AS total, `a`.`th_total` AS `mthfee`, IFNULL(w,WEEK('{$endtime}',1)) as w, IFNULL(b.sale_total,0) AS wtotal, ifnull( `b`.`th_total`, 0 ) AS `wthfee`, IFNULL(c.d,date_format('{$endtime}', '%Y-%m-%d' )) as d, IFNULL(c.sale_total,0) AS dtotal, ifnull( `c`.`th_total`, 0 ) AS `thfee` FROM ( SELECT MONTH ( ordertime ) AS m, sum( sale_total ) AS sale_total, suppitem, depart, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l on l.productNo = t.th_qrdcpNo WHERE t.th_qrdcpNo <> '' and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3 AND DATE_FORMAT ( t.createtime,'%Y-%m' ) = DATE_FORMAT ( '{$endtime}' ,'%Y-%m') ) AS th_total FROM source_all k WHERE DATE_FORMAT(ordertime,'%Y-%m') = DATE_FORMAT('{$endtime}','%Y-%m') GROUP BY suppitem,depart, m ) AS a LEFT JOIN ( SELECT WEEK ( ordertime, 1 ) AS w, sum( sale_total ) AS sale_total, suppitem, depart, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l on l.productNo = t.th_qrdcpNo WHERE t.th_qrdcpNo <> '' and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3 AND week ( t.createtime,1 ) = WEEK ( '{$endtime}', 1 ) and DATE_FORMAT ( t.createtime,'%Y' ) = DATE_FORMAT ( '{$endtime}' ,'%Y') ) AS th_total FROM source_all k WHERE WEEK ( ordertime, 1 ) = WEEK ( '{$endtime}', 1 ) and DATE_FORMAT ( ordertime,'%Y' ) = DATE_FORMAT ( '{$endtime}' ,'%Y') GROUP BY suppitem,depart, w ) AS b ON a.suppitem = b.suppitem and a.depart=b.depart LEFT JOIN ( SELECT date_format( ordertime, '%Y-%m-%d' ) AS d, sum( sale_total ) AS sale_total, suppitem,depart, ( SELECT ifnull( sum( t.th_qrd_fee ), 0 ) FROM cfp_th_source t LEFT JOIN source_all l on l.productNo = t.th_qrdcpNo WHERE t.th_qrdcpNo <> '' and l.depart=k.depart and l.suppitem=k.suppitem and t.th_status<>3 AND date_format ( t.createtime,'%Y-%m-%d') = date_format ( '{$endtime}', '%Y-%m-%d' ) ) AS th_total FROM source_all k WHERE date_format( ordertime, '%Y-%m-%d' ) = date_format( '{$endtime}', '%Y-%m-%d' ) GROUP BY suppitem,depart, d ) AS c ON a.suppitem = c.suppitem and a.depart=c.depart "); $dtae = isset($endtime) &&$endtime!="" ? $endtime." 00:00:00" : date("Y-m-d H:i:s"); $tips = Db::name("depart_tips")->field("department,total_tips")->where("year=CONCAT(YEAR('{$dtae}'),'年') and month=CONCAT(month('{$dtae}'),'月')")->column("department,total_tips"); $data=[]; $temp=["p"=>"总计",'item'=>"-","thtotal"=>0,"total"=>0,"mthfee"=>0,"wtotal"=>0,"wthfee"=>0,"dtotal"=>0,"thfee"=>0, "tips"=>0,"rate"=>0,"thrate"=>0]; foreach ($list as $key=>$value){ $value['tips']=0; // foreach ($tips as $val){ // if($value["depart"]==$val['department']){ // $value['tips']=$val['total_tips']; // } // } $value['thtotal'] = $value['total']-$value['mthfee']; $data[]=$value; } $list = $this->check($data,$temp,$tips); array_walk($list,function (&$value){ if(is_null($value['tips']) || $value['tips']==0){ $value['tips']="-"; $value["rate"]="-"; $value["thrate"]="-"; }else{ $value["rate"]=number_format( $value['total']/$value['tips']*100,2); $value["thrate"]=number_format( ($value['total']- $value['mthfee'])/$value['tips']*100,2); $value['tips'] = sprintf( "%.2f",$value['tips']); } $value['total'] = sprintf( "%.2f",$value['total']); $value['mthfee'] = sprintf( "%.2f",$value['mthfee']); $value['wtotal'] = sprintf( "%.2f",$value['wtotal']); $value['wthfee'] = sprintf( "%.2f",$value['wthfee']); $value['dtotal'] = sprintf( "%.2f",$value['dtotal']); $value['thfee'] = sprintf( "%.2f",$value['thfee']); $value['thtotal'] = sprintf( "%.2f",$value['thtotal']); }); if($temp['tips']==0){ $temp['rate']="-"; $temp['thrate']="-"; $temp['tips']="-"; }else{ $temp['rate'] =number_format( $temp['total']/$temp['tips']*100,2); $temp['thrate']=number_format( ($temp['total']- $temp['mthfee'])/$temp['tips']*100,2);; $temp['tips'] =round( $temp['tips'],2); } $temp['total'] = sprintf( "%.2f",$temp['total']); $temp['mthfee'] = sprintf( "%.2f",$temp['mthfee']); $temp['wtotal'] = sprintf( "%.2f",$temp['wtotal']); $temp['wthfee'] = sprintf( "%.2f",$temp['wthfee']); $temp['dtotal'] = sprintf( "%.2f",$temp['dtotal']); $temp['thfee'] = sprintf( "%.2f",$temp['thfee']); $temp['thtotal'] = sprintf( "%.2f",$temp['thtotal']); $sort = array_column($list,"thrate"); array_multisort($sort,SORT_ASC,$list); array_push($list,$temp); $list = array_reverse($list); return app_show(0,"获取成功",$list); } public function check($data,&$temp,$tipsArr) { $list =[]; // $item = [['客服部','积分商城'],['项目部','印刷系统','泰康小卖部'],['网络部'],['特殊订单']]; $suppitem = [['客服部'],['项目部','印刷系统'],['网络部'],['特殊订单'],['积分商城','泰康小卖部',"优选商城"]]; $item = ['客服部','项目部','网络部','财务部',"平台部"]; $checkitem = ['客服部','积分商城','项目部','印刷系统','泰康小卖部','网络部','特殊订单',"优选商城"]; $tips=array_column($tipsArr,'total_tips'); $tips_depart=array_column($tipsArr,'department'); foreach ($data as $key=>$value){ if(!in_array($value['p'],$checkitem)){ continue; } if(in_array($value['depart'],$item)){ $t= array_search( $value['depart'],$tips_depart); $k= array_search( $value['depart'],$item); $tip = $t!==false?$tips[$t]:0; }else{ continue; } isset($list[$k])? "" : $list[$k]=['total'=>0,'mthfee'=>0,'tips'=>0,'wtotal'=>0,'wthfee'=>0,'dtotal'=>0, 'child'=>[],'depart'=>'','thfee'=>0,'thtotal'=>0]; $list[$k]['total'] += $value['total']; $list[$k]['mthfee'] += $value['mthfee']; $list[$k]['wtotal'] += $value['wtotal']; $list[$k]['wthfee'] += $value['wthfee']; $list[$k]['dtotal'] += $value['dtotal']; $list[$k]['thfee'] += $value['thfee']; $list[$k]['thtotal'] += $value['thtotal']; $list[$k]['tips'] = $tip; $temp['total']+=$value['total']; $temp['mthfee']+=$value['mthfee']; $temp['wtotal']+=$value['wtotal']; $temp['wthfee']+=$value['wthfee']; $temp['thfee']+=$value['thfee']; $temp['dtotal']+=$value['dtotal']; $temp['thtotal']+=$value['thtotal']; if (is_null($value['tips']) || $value['tips'] == 0) { $value['tips'] = "-"; } else { $value['tips'] = sprintf("%.2f", $value['tips']); } $value['total'] = sprintf("%.2f", $value['total']); $value['mthfee'] = sprintf("%.2f", $value['mthfee']); $value['wtotal'] = sprintf("%.2f", $value['wtotal']); $value['wthfee'] = sprintf("%.2f", $value['wthfee']); $value['dtotal'] = sprintf("%.2f", $value['dtotal']); $value['thfee'] = sprintf("%.2f", $value['thfee']); $value['thtotal'] = sprintf("%.2f", $value['thtotal']); $list[$k]['child'][] = $value; $list[$k]['depart'] = $value['depart']; } $temp['tips'] = array_sum(array_column($list,'tips')); return $list; } /** * 保存更新的资源 * * @param \think\Request $request * @param int $id * @return \think\Response */ public function update(Request $request, $id) { // } /** * 删除指定资源 * * @param int $id * @return \think\Response */ public function delete($id) { // } }