<?php
namespace app\admin\controller;
use think\App;
use think\Db;
class WasteStatistics extends Auth {
public function work() {
$cur = date('Y-m-d');
$start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
$end = input('end', date('Y-m-d'));
$start1 = $start . ' 00:00:00';
$end1 = $end . ' 23:59:59';
$type = input('type');
$typeList = Db::name('waste_type')
->where('enable', 1)
->where('del', 0)
//->where('org_id', $this->orgId)
->select();
$list = $this->workData($start1, $end1, $type);
$this->assign('list', $list);
$this->assign('type', $type);
$this->assign('typeList', $typeList);
$this->assign('start', $start);
$this->assign('end', $end);
return $this->fetch();
}
public function workData($start1, $end1, $type) {
$map1[] = ['create_time', '>=', $start1];
$map1[] = ['create_time', '<=', $end1];
$map1[] = ['org_id', '=', $this->orgId];
if (!empty($type)) {
$map1[] = ['cateid', '=', $type];
}
$list = Db::name('waste_record')
->where($map1)
->where('del', 0)
->group('user_id')
->select();
foreach ($list as $k => $v) {
$list[$k]['title'] = Db::name('user')
->where('id', $v['user_id'])
->value('real_name');
$c = Db::name('waste_record')
->where($map1)
->where('del', 0)
->where('user_id', $v['user_id'])
->count();
$list[$k]['count'] = $c ? $c : 0;
$weight = Db::name('waste_record')
->where($map1)
->where('del', 0)
->where('user_id', $v['user_id'])
->sum('weight');
$list[$k]['weight'] = $weight ? round($weight / 1000, 2) : 0;
}
return $list;
}
public function workExport() {
$cur = date('Y-m-d');
$start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
$end = input('end', date('Y-m-d'));
$start1 = $start . ' 00:00:00';
$end1 = $end . ' 23:59:59';
$type = input('type');
$ret = $this->workData($start1, $end1, $type);
include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
//实例化PHPExcel类
$objPHPExcel = new \PHPExcel();
//激活当前的sheet表
$objPHPExcel->setActiveSheetIndex(0);
//设置表格头（即excel表格的第一行）
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '编号')
->setCellValue('B1', '姓名')
->setCellValue('C1', '单数')
->setCellValue('D1', '重量(kg)');
// 设置表格头水平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置列水平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置单元格宽度
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
//循环刚取出来的数组，将数据逐一添加到excel表格。
for ($i = 0; $i < count($ret); $i++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['user_id']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['title']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['count']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['weight']);
}
//设置保存的Excel表格名称
$filename = '医废工作量统计_' . date('YmdHis', time()) . '.xls';
//设置当前激活的sheet表格名称
$objPHPExcel->getActiveSheet()->setTitle('医废工作量统计');
//设置浏览器窗口下载表格
ob_end_clean();
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $filename);
//生成excel文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//下载文件在浏览器窗口
return $objWriter->save('php://output');
}
// 近两年医废统计
public function waste_year() {
$org_id = $this->orgId;
$year = date('Y');
$qyear = $year - 1;
$arr1 = [$year . '01', $year . '02', $year . '03', $year . '04', $year . '05', $year . '06', $year . '07', $year . '08', $year . '09', $year . '10', $year . '11', $year . '12',];
$arr2 = [$qyear . '01', $qyear . '02', $qyear . '03', $qyear . '04', $qyear . '05', $qyear . '06', $qyear . '07', $qyear . '08', $qyear . '09', $qyear . '10', $qyear . '11', $qyear . '12',];
$arrs = array_merge($arr2, $arr1);
$lists = [];
foreach ($arrs as $k => $v) {
$weight = Db::name('waste_record')
->where('org_id', $org_id)
->where('del', 0)
->where('create_yyyymm', $v)
->sum('weight');
$lists[] = [
'month' => $v,
'month_text' => intval(date('m', strtotime($v . '01'))) . '月',
'weight' => $weight && !empty($weight) ? round($weight / 1000, 2) : 0
];
}
$warr = $warr1 = $warr2 = [];
$tmonths = [];
$hb = [];
// 计算环比
foreach ($lists as $k => $v) {
if ($k == 0) {
$lists[$k]['hb'] = 0;
}
else {
if ($lists[$k - 1]['weight'] > 0) {
$lists[$k]['hb'] = round(($v['weight'] - $lists[$k - 1]['weight']) / $lists[$k - 1]['weight'], 3) * 100;
}
else {
$lists[$k]['hb'] = 0;
}
}
if ($k >= 12) {
$warr1[] = $v['weight'];
}
else {
$warr2[] = $v['weight'];
}
$warr[] = $v['weight'];
$tmonths[] = date('Y-m', strtotime($v['month'] . '01'));
$hb[] = $lists[$k]['hb'];
}
//计算同比
$tbarr = [];
foreach ($arr1 as $k => $v) {
$v1 = $lists[$k]['weight'];
$v2 = $lists[$k + 12]['weight'];
if ($v1 > 0) {
$tbarr[] = round(($v2 - $v1) / $v1, 3) * 100;
}
else {
$tbarr[] = 0;
}
}
$data = [
'lists' => $lists,
'tb' => $tbarr,
'months' => ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月'],
'year' => $year,
'qyear' => $qyear,
'warr' => $warr,
'warr1' => $warr1,
'warr2' => $warr2,
'tmonths' => $tmonths,
'hb' => $hb
];
$this->assign('data', $data);
return $this->fetch();
}
//医疗报表统计
public function view(){
$month = input('month',date('Y-m'));
$dep_id = input('dep_id','');
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$address =(new \app\common\model\Address())->getListByType(7,$this->orgId);
$dep = (new \app\common\model\Dep())->getList($this->orgId);
$typeList = Db::name('waste_type')
->where('enable', 1)
->where('del', 0)
// ->where('org_id', $this->orgId)
->select();
$return = $this->viewData($month,$dep_id,$addr_id,$type_id);
$this->assign('data',$return);
$this->assign('address',$address);
$this->assign('dep',$dep);
$this->assign('typeList',$typeList);
$this->assign('month',$month);
$this->assign('dep_id',$dep_id);
$this->assign('addr_id',$addr_id);
$this->assign('type_id',$type_id);
return $this->fetch();
}
public function viewData($month,$dep_id,$addr_id,$type_id){
$map[] = ['org_id','=',$this->orgId];
$typeMap = [];
if($type_id !=''){
$typeMap[] = ['id','=',$type_id];
}
$typeList = Db::name('waste_type')
->where('enable', 1)
->where('del', 0)
->where($typeMap)
// ->where('org_id', $this->orgId)
->select();
$recordMap = [];
$depWhere = [];
if($dep_id !=''){
$depWhere[] = ['id','=',$dep_id];
$map[] = ['del','=',0];
$map[] = ['enable','=',1];
$map[] = ['dep_id','=',$dep_id];
$map[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
$address = Db::name('address')
->where($map)
->order('id desc')
->field('id,title,remark,sn,x,y')
->select();
if($address){
$recordMap[] = ['waste_device_id','in',array_column($address,'id')];
}else{
$recordMap[] = ['waste_device_id','=',0];
}
}
if($addr_id!=''){
$recordMap[] = ['waste_device_id','=',$addr_id];
}
$name1 = [];
$name2 = [];
$name3 = [];
$data1 = [];
$data2 = [];
$data3 = [];
$data4 = [];
$day = getDay(strtotime($month));
foreach ($day as $k1=>$v){
$name2[] = date('d',strtotime($v));
}
$dep = Db::name('dep')
->where('del',0)
->where('org_id',$this->orgId)
->where('enable',1)
->where($depWhere)
->select();
$depList = [];
foreach ($dep as $k=>$v){
$dep_map[] = ['del','=',0];
$dep_map[] = ['enable','=',1];
$dep_map[] = ['dep_id','=',$v['id']];
$dep_map[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
$address = Db::name('address')
->where($dep_map)
->order('id desc')
->field('id,title,remark,sn,x,y')
->select();
if($address){
$depList[] = [
'title'=>$v['title'],
'ids'=>array_column($address,'id')
];
}
}
foreach ($depList as $k2=>$v2){
$map = [];
if($month){
$map[] =['create_yyyymm','=',date('Ym',strtotime($month))];
}
$nums1 = Db::name('waste_record')
->where('org_id',$this->orgId)
->where('del',0)
->where($recordMap)
->where($map)
->where('waste_device_id','in',$v2['ids'])
->sum('weight');
$depList[$k2]['total'] = $nums1?round($nums1/1000,2):0;
}
$depList = $depList?list_sort_by($depList,'total','desc'):[];
$name3[] = $depList?array_column($depList,'title'):[];
foreach ($typeList as $k=>$v){
$map = [];
if($month){
$map[] =['create_yyyymm','=',date('Ym',strtotime($month))];
}
$num = Db::name('waste_record')
->where('org_id',$this->orgId)
->where('del',0)
->where('cateid',$v['id'])
->where($recordMap)
->where($map)
->sum('weight');
$total = $num?round($num/1000,2):0;
$name1[] = $v['title'];
$data1[] = $total;
$data2[] =['value'=>$total,'name'=>$v['title']];
$numList = [];
$numList1 = [];
foreach ($day as $k1=>$v1){
$nums = Db::name('waste_record')
->where('org_id',$this->orgId)
->where('del',0)
->where('cateid',$v['id'])
->where($recordMap)
->where($map)
->where('create_yyyymmdd',$v1)
->sum('weight');
$totals = $nums?round($nums/1000,2):0;
$numList[] = $totals;
}
foreach ($depList as $k2=>$v2){
if($k2<=9){
$nums1 = Db::name('waste_record')
->where('org_id',$this->orgId)
->where('del',0)
->where('cateid',$v['id'])
->where($map)
->where('waste_device_id','in',$v2['ids'])
->sum('weight');
$totals1 = $nums1?round($nums1/1000,2):0;
$numList1[] = $totals1;
}
}
$data3[] = [
'name'=>$v['title'],
'type'=>'line',
'stack'=>'stack',
'data'=>$numList
];
$data4[] = [
'name'=>$v['title'],
'type'=>'line',
'stack'=>'stack',
'data'=>$numList1
];
}
$return = [
'name1'=>$name1,
'name2'=>$name2,
'name3'=>$name3,
'data1'=>$data1,
'data2'=>$data2,
'data3'=>$data3,
'data4'=>$data4,
];
return $return;
}
public function viewExportData($month,$dep_id,$addr_id,$type_id){
$tMap = [];
$tMap[] = ['enable','=',1];
$tMap[] = ['del','=',0];
//$tMap[] = ['org_id','=',$this->orgId];
if($type_id!=''){
$tMap[] = ['id','=',$type_id];
}
$lists = [];
$type = Db::name('waste_type')->where($tMap)->select();
$type = $type?$type:[];
$first = [
'name' => '类型',
];
$second = [
'name' => '重量',
];
$third = [
'name' => '比例',
];
$fourth = [
'name' => '',
];
$five = [
'name' => '按天统计',
];
$total = 0;
$count = 1;
foreach ($type as $k=>$v){
$count++;
$map = [];
$map[] = ['del','=',0];
$map[] = ['org_id','=',$this->orgId];
$map[] = ['status','in',[0,1,2]];
if($type_id){
$map[] = ['cateid','=',$type_id];
}
if($addr_id){
$map[] = ['waste_device_id','=',$addr_id];
}
if($dep_id){
$addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
if($addrs){
$map[] = ['waste_device_id','in',$addr_id];
}else{
$map[] = ['waste_device_id','=',-1];
}
}
if($month){
$map[] = ['create_yyyymm','=',str_ireplace('-','',$month)];
}
$weight = Db::name('waste_record')->where($map)->sum('weight');
$total += $weight;
$first['type'.$v['id']] = $v['title'];
$second['type'.$v['id']] = round($weight/1000,2);
$third['type'.$v['id']] = $weight;
$fourth['type'.$v['id']] = '';
$five['type'.$v['id']] = '';
}
foreach ($type as $k=>$v){
$third['type'.$v['id']] = $total>0?round($third['type'.$v['id']]/$total,2):0;
}
$lists[] = $first;
$lists[] = $second;
$lists[] = $third;
$lists[] = $fourth;
$lists[] = $five;
$days = getDay(strtotime($month.'-01'));
foreach ($days as $kk=>$vv){
$arr = [
'name' => date('Y-m-d',strtotime($vv))
];
foreach ($type as $k=>$v){
$map = [];
$map[] = ['del','=',0];
$map[] = ['org_id','=',$this->orgId];
$map[] = ['status','in',[0,1,2]];
if($type_id){
$map[] = ['cateid','=',$type_id];
}
if($addr_id){
$map[] = ['waste_device_id','=',$addr_id];
}
if($dep_id){
$addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
if($addrs){
$map[] = ['waste_device_id','in',$addr_id];
}else{
$map[] = ['waste_device_id','=',-1];
}
}
$map[] = ['create_yyyymmdd','=',$vv];
$weight = Db::name('waste_record')->where($map)->sum('weight');
$arr['type'.$v['id']] = round($weight/1000,2);
}
$lists[] = $arr;
}
$arr2 = [
'name' => '',
];
$arr3 = [
'name' => '按科室统计',
];
foreach ($type as $k=>$v){
$arr2['type'.$v['id']] = '';
$arr3['type'.$v['id']] = '';
}
$lists[] = $arr2;
$lists[] = $arr3;
$m[] = ['org_id','=',$this->orgId];
$m[] = ['enable','=',1];
$m[] = ['del','=',0];
if($dep_id > 0){
$m[] = ['id','=',$dep_id];
}
$deps = Db::name('dep')->where($m)->select();
$deps = $deps?$deps:[];
foreach ($deps as $kk=>$vv){
$arr = [
'name' => $vv['title'],
];
foreach ($type as $k=>$v){
$map = [];
$map[] = ['del','=',0];
$map[] = ['org_id','=',$this->orgId];
$map[] = ['status','in',[0,1,2]];
if($type_id){
$map[] = ['cateid','=',$type_id];
}
if($addr_id){
$map[] = ['waste_device_id','=',$addr_id];
}
if($vv['id']){
$addrs = Db::name('address')->where('dep_id',$dep_id)->where('enable',1)->where('del',0)->column('id');
if($addrs){
$map[] = ['waste_device_id','in',$addr_id];
}else{
$map[] = ['waste_device_id','=',-1];
}
}
$map[] = ['create_yyyymm','=',$month];
$weight = Db::name('waste_record')->where($map)->sum('weight');
$arr['type'.$v['id']] = round($weight/1000,2);
}
$lists[] = $arr;
}
$address = '地点：全部';
if($addr_id){
$address = '地点：'.Db::name('address')->where('id',$addr_id)->value('title');
}
$dep = '科室：全部';
if($dep_id){
$dep = '科室：'.Db::name('dep')->where('id',$dep_id)->value('title');
}
$typestr = '医废类型：全部';
if($type_id){
$typestr = '医废类型：'.Db::name('waste_type')->where('id',$type_id)->value('title');
}
$monthstr = "月份：".$month;
$data = [
'count' => $count,
'address' => $address,
'dep' => $dep,
'type' => $typestr,
'types' => $type,
'monthstr' => $monthstr,
'lists' => $lists
];
return $data;
}
public function viewExport(){
$month = input('month',date('Y-m'));
$dep_id = input('dep_id','');
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$data = $this->viewExportData($month,$dep_id,$addr_id,$type_id);
$lists = $data['lists'];
$types = $data['types'];
$char = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
$lins = [];
// 暂时支持2层
for( $i=0;$i<$data['count'];$i++ ){
$r1 = $i%26;
$r2 = floor($i/26);
if($r2 == 0){
$lins[] = $char[$r1];
}else if($r2 == 1){
$lins[] = $char[$r2-1].$char[$r1];
}else if($r2 == 2){
$lins[] = $char[$r2-1].$char[$r1];
}else if($r2 == 3){
$lins[] = $char[$r2-1].$char[$r1];
}
}
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('医疗废弃物报表');
foreach ($lins as $k=>$v){
$spreadsheet->setActiveSheetIndex(0)->getStyle($v)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$spreadsheet->setActiveSheetIndex(0)->getColumnDimension($v)->setAutoSize(true);
}
//表头
//设置单元格内容
$worksheet->setCellValueByColumnAndRow(1, 1, $data['address']);
$worksheet->setCellValueByColumnAndRow(2, 1, $data['dep']);
$worksheet->setCellValueByColumnAndRow(3, 1, $data['type']);
$worksheet->setCellValueByColumnAndRow(4, 1, $data['monthstr']);
foreach ($lists as $k=>$v){
$worksheet->setCellValueByColumnAndRow(1, $k+2, $v['name']);
foreach ($types as $kk=>$vv){
$worksheet->setCellValueByColumnAndRow($kk+2, $k+2, $v['type'.$vv['id']]);
}
}
$filename = '医疗废弃物报表'.$month.'.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}
// 医废日统计
public function wasteDay() {
$month = input('start',date('Y-m-d'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
//$tMap[] = ['org_id','=',$this->orgId];
$typeList1 = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteDataDay($month,$addr_id,$type_id);
$this->assign('data',$data);
$this->assign('typeList',$typeList);
$this->assign('typeList1',$typeList1);
$this->assign('addressList',$addressList);
$this->assign('start',$month);
$this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
$this->assign('type_id',$type_id?explode(',',$type_id):[]);
return $this->fetch();
}
public function wasteDataDay($month,$addr_id,$type_id){
$org_id = $this->orgId;
$aMap = [];
if($addr_id!=''){
$aMap[] = ['id','in',explode(',',$addr_id)];
}
$aMap[]=['','exp',Db::raw("FIND_IN_SET(7,types)")];
$dep = Db::name('address')
->where('org_id',$org_id)
->where('enable',1)
->where('del',0)
->where($aMap)
->select();
$tMap = [];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
}
$type = Db::name('waste_type')
->where('enable',1)
->where('del',0)
//->where('org_id',$this->orgId)
->where($tMap)
->select();
$date = date('Ymd',strtotime($month));
foreach ($dep as $k=>$v){
foreach ($type as $k1=>$v1){
$map = [];
$map[] = ['CREATE_YYYYMMDD','=',$date];
$row = Db::name('waste_record')
->where('waste_device_id',$v['id'])
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->count();
$dep[$k]['data'][] = $row;
$map1 = [];
if($addr_id!=''){
$map1[] = ['waste_device_id','in',explode(',',$addr_id)];
}
$dep[$k]['total'][] = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->count();
$kg = Db::name('waste_record')
->where('waste_device_id',$v['id'])
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$dep[$k]['data'][] = $we;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$dep[$k]['total'][] =$we;
}
}
return $dep;
}
//医废日统计导出
public function wasteDayExport()
{
set_time_limit(0);
ini_set("memory_limit", "1024M");
$month = input('start',date('Y-m-d'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$data = $this->wasteDataDay($month,$addr_id,$type_id);
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
//$tMap[] = ['org_id','=',$this->orgId];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$type = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$type = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$title = '医废日统计';
if(!empty($month)){
$title = $month.'医废日统计';
}
header("Content-type: application/vnd.ms-excel");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=".$title.".xls");
header('Expires:0');
header('Pragma:public');
$res = '';
$res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
$res.='<tr style="background: #ffffff;">
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">地点</th>';
foreach ($type as $k=>$v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';
}
$res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
$res.='<tr style="background: #ffffff;">';
foreach ($type as $v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
}
foreach ($data as $k=>$v){
$res.='<tr style="background: #ffffff;">';
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['title'].'</td>';
foreach ($v['data'] as $k1=>$v1){
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
$res.='</tr>';
}
$res.='<tr style="background: #ffffff;">
<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
if(!empty($data)){
foreach ($data[0]['total'] as $k1=>$v1){
$res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
}
$res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
</tr>';
echo $res;
}
// 医废月统计
public function wasteMonth() {
$month = input('start',date('Y-m'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
// $tMap[] = ['org_id','=',$this->orgId];
$typeList1 = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteMonthData($month,$addr_id,$type_id);
$this->assign('data',$data['res']);
$this->assign('totalData',$data['total']);
$this->assign('typeList',$typeList);
$this->assign('typeList1',$typeList1);
$this->assign('addressList',$addressList);
$this->assign('start',$month);
$this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
$this->assign('type_id',$type_id?explode(',',$type_id):[]);
return $this->fetch();
}
public function wasteMonthData($month,$addr_id,$type_id){
$org_id = $this->orgId;
$dep = getDay(strtotime($month));
$tMap = [];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
}
$type = Db::name('waste_type')
->where('enable',1)
->where('del',0)
// ->where('org_id',$this->orgId)
->where($tMap)
->select();
$res = [];
$total = [];
foreach ($dep as $k=>$v){
foreach ($type as $k1=>$v1){
$map = [];
$map[] = ['CREATE_YYYYMMDD','=',$v];
$map1 = [];
if($addr_id!=''){
$map1[] = ['waste_device_id','in',explode(',',$addr_id)];
}
$row = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->count();
$res[$k][] = $row;
$map2 = [];
$map2[] = ['CREATE_YYYYMM','=',date('Ym',strtotime($v))];
$row = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where($map2)
->count();
$total[$k][] = $row;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$res[$k][] = $we;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where($map2)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$total[$k][] =$we;
}
}
return ['res'=>$res,'total'=>$total];
}
public function wasteMonthExport(){
$month = input('start',date('Y-m'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
set_time_limit(0);
ini_set("memory_limit", "1024M");
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
// $tMap[] = ['org_id','=',$this->orgId];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteMonthData($month,$addr_id,$type_id);
$title = '医废月统计';
if(!empty($month)){
$title = $month.'医废月统计';
}
header("Content-type: application/vnd.ms-excel");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=".$title.".xls");
header('Expires:0');
header('Pragma:public');
$res = '';
$res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
$res.='<tr style="background: #ffffff;">
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">日期</th>';
foreach ($typeList as $k=>$v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';
}
$res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
$res.='<tr style="background: #ffffff;">';
foreach ($typeList as $v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
}
foreach ($data['res'] as $k=>$v){
$res.='<tr style="background: #ffffff;">';
$kk = $k+1;
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$kk.'</td>';
foreach ($v as $k1=>$v1){
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
$res.='</tr>';
}
$res.='<tr style="background: #ffffff;">
<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
if(!empty($data['total'])){
foreach ($data['total'][0] as $k1=>$v1){
$res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
}
$res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
</tr>';
echo $res;
}
// 医废年统计
public function wasteYears() {
$month = input('start',date('Y'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
//$tMap[] = ['org_id','=',$this->orgId];
$typeList1 = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteYearsData($month,$addr_id,$type_id);
$this->assign('data',$data);
$this->assign('typeList',$typeList);
$this->assign('typeList1',$typeList1);
$this->assign('addressList',$addressList);
$this->assign('start',$month);
$this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
$this->assign('type_id',$type_id?explode(',',$type_id):[]);
return $this->fetch();
}
public function wasteYearsData($month,$addr_id,$type_id){
$org_id = $this->orgId;
$dep = get_month($month);
$tMap = [];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
}
$type = Db::name('waste_type')
->where('enable',1)
->where('del',0)
//->where('org_id',$this->orgId)
->where($tMap)
->select();
foreach ($dep as $k=>$v){
foreach ($type as $k1=>$v1){
$map = [];
$map[] = ['create_yyyymm','=',$v['date']];
$map1 = [];
if($addr_id!=''){
$map1[] = ['waste_device_id','in',explode(',',$addr_id)];
}
$row = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->count();
$dep[$k]['list'][] = $row;
$map2 = [];
$map2[] = ['create_yyyy','=',$month];
$row = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where($map2)
->count();
$dep[$k]['total'][] = $row;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map)
->where($map1)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$dep[$k]['list'][] = $we;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where($map2)
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$dep[$k]['total'][] = $we;
}
}
return $dep;
}
public function wasteYearsExport() {
$month = input('start',date('Y'));
$addr_id = input('addr_id','');
$type_id = input('type_id','');
set_time_limit(0);
ini_set("memory_limit", "1024M");
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
//$tMap[] = ['org_id','=',$this->orgId];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteYearsData($month,$addr_id,$type_id);
$title = '医废年统计';
if(!empty($month)){
$title = $month.'医废年统计';
}
header("Content-type: application/vnd.ms-excel");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=".$title.".xls");
header('Expires:0');
header('Pragma:public');
$res = '';
$res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
$res.='<tr style="background: #ffffff;">
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">日期</th>';
foreach ($typeList as $k=>$v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';
}
$res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
$res.='</tr>';
$res.='<tr style="background: #ffffff;">';
foreach ($typeList as $v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
}
$res.='</tr>';
foreach ($data as $k=>$v){
$res.='<tr style="background: #ffffff;">';
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v['key'].'</td>';
foreach ($v['list'] as $k1=>$v1){
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
$res.='</tr>';
}
$res.='<tr style="background: #ffffff;">
<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">共计:</td>';
if(!empty($data)){
foreach ($data[1]['total'] as $k1=>$v1){
$res.=' <td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v1.'</td>';
}
}
$res.='<td style="border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>
</tr>';
echo $res;
}
// 医废暂存统计
public function wasteZc() {
$addr_id = input('addr_id','');
$type_id = input('type_id','');
$addressList = (new \app\common\model\Address())->getListByType(7,$this->orgId);
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
// $tMap[] = ['org_id','=',$this->orgId];
$typeList1 = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteZcData($addr_id,$type_id);
$this->assign('data',$data);
$this->assign('typeList',$typeList);
$this->assign('typeList1',$typeList1);
$this->assign('addressList',$addressList);
$this->assign('addr_id',$addr_id?explode(',',$addr_id):[]);
$this->assign('type_id',$type_id?explode(',',$type_id):[]);
return $this->fetch();
}
public function wasteZcData($addr_id,$type_id){
$org_id = $this->orgId;
$tMap = [];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
}
$type = Db::name('waste_type')
->where('enable',1)
->where('del',0)
//->where('org_id',$this->orgId)
->where($tMap)
->select();
$res = [];
foreach ($type as $k1=>$v1){
$map1 = [];
if($addr_id!=''){
$map1[] = ['waste_device_id','in',explode(',',$addr_id)];
}
$row = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where(function ($query)
{
$query->where('status', 1)->whereor('status', 2);
})
->count();
$res[]= $row;
$kg = Db::name('waste_record')
->where('cateid',$v1['id'])
->where('org_id',$org_id)
->where('del',0)
->where($map1)
->where(function ($query)
{
$query->where('status', 1)->whereor('status', 2);
})
->sum('weight');
$we = $kg?round($kg/1000,2):0;
$res[]= $we;
}
return $res;
}
// 医废暂存统计导出
public function wasteZcExport() {
$addr_id = input('addr_id','');
$type_id = input('type_id','');
set_time_limit(0);
ini_set("memory_limit", "1024M");
$tMap[] = ['del','=',0];
$tMap[] = ['enable','=',1];
//$tMap[] = ['org_id','=',$this->orgId];
if($type_id!=''){
$tMap[] = ['id','in',explode(',',$type_id)];
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}else{
$typeList = Db::name('waste_type')
->where($tMap)
->field('id,title')
->select();
}
$data = $this->wasteZcData($addr_id,$type_id);
$title = '医废暂存统计';
header("Content-type: application/vnd.ms-excel");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=".$title.".xls");
header('Expires:0');
header('Pragma:public');
$res = '';
$res.='<table style="border-top: 1px solid #ddd;border-left: 1px solid #ddd;border-spacing: 0;">';
$res.='<tr style="background: #ffffff;">';
foreach ($typeList as $k=>$v){
$res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" colspan="2">'.$v['title'].'</th>';
}
$res.='<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd" rowspan="2">备注</th>';
$res.='</tr>';
$res.='<tr style="background: #ffffff;">';
foreach ($typeList as $v){
$res.=' <th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">件数/包</th>
<th style="text-align: center;background: #ffffff;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">重量/kg</th>';
}
$res.='</tr>';
$res.='<tr style="background: #ffffff;">';
foreach ($data as $k=>$v){
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd">'.$v.'</td>';
}
$res.='<td style="text-align: center;border-bottom: 1px solid #ddd;border-right: 1px solid #ddd"></td>';
$res.='</tr>';
echo $res;
}
}