搜索到
105
篇与
Ezra
的结果
-
-
nginx下解决跨域问题 server { listen 8064; server_name zhtyphp.test; root "D:/lice/zhty_php/public"; #允许跨域请求的域，*代表所有 add_header 'Access-Control-Allow-Origin' *; #允许带上cookie请求 add_header 'Access-Control-Allow-Credentials' 'true'; #允许请求的方法，比如 GET/POST/PUT/DELETE add_header 'Access-Control-Allow-Methods' *; #允许请求的header add_header 'Access-Control-Allow-Headers' *; location / { index index.php index.html error/index.html; if (!-e $request_filename) { rewrite ^/(.*)$ /index.php?s=$1; } error_page 400 /error/400.html; error_page 403 /error/403.html; error_page 404 /error/404.html; error_page 500 /error/500.html; error_page 501 /error/501.html; error_page 502 /error/502.html; error_page 503 /error/503.html; error_page 504 /error/504.html; error_page 505 /error/505.html; error_page 506 /error/506.html; error_page 507 /error/507.html; error_page 509 /error/509.html; error_page 510 /error/510.html; include D:/lice/zhty_php/public/nginx.htaccess; autoindex off; } location ~ \.php(.*)$ { fastcgi_pass 127.0.0.1:9001; fastcgi_index index.php; fastcgi_split_path_info ^((?U).+\.php)(/?.+)$; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; fastcgi_param PATH_INFO $fastcgi_path_info; fastcgi_param PATH_TRANSLATED $document_root$fastcgi_path_info; include fastcgi_params; } }
-
记录一次PHP写多sheet的Excel public function dcsbExport(){ $param = Request::instance()->param(); $begin = strtotime($param['day1']); $end = strtotime($param['day2']); $stime = date('Y-m-d 00:00:01', $begin); $etime = date('Y-m-d H:00:00', $end + 86410); // halt($etime); //引入PHPExcel导出类 vendor("PHPExcel.PHPExcel"); $sheetIndex=0; $objPHPExcel = new \PHPExcel(); $sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++); $sheet->setTitle('同比水量日明细'); $objPHPExcel->getProperties()->setCreator("ctos") ->setLastModifiedBy("ctos") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 水平居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// 垂直居中 $sheet->getDefaultColumnDimension()->setWidth(15);//设置默认列宽度 $sheet->getColumnDimension('A')->setWidth(30);//设置某列宽度 // AutoSize $objPHPExcel->getActiveSheet()->SetCellValue('A1', '电磁水表在装表统计'); $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');//合并单元格 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);//设置默认行高度 $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);//设置某一行高度 $objPHPExcel->getActiveSheet()->SetCellValue('A2', '单位'); $objPHPExcel->getActiveSheet()->mergeCells('A2:A3');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('B2', '电磁水表在装'.substr($stime,0,7).'前'); $objPHPExcel->getActiveSheet()->mergeCells('B2:D2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('B3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('C3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('D3', '小计'); $objPHPExcel->getActiveSheet()->SetCellValue('E2', '电磁水表新装'.substr($stime,0,7).'-'.substr($etime,0,7)); $objPHPExcel->getActiveSheet()->mergeCells('E2:G2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('E3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('F3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('G3', '小计'); $objPHPExcel->getActiveSheet()->SetCellValue('H2', '电磁水表在装新装总计'); $objPHPExcel->getActiveSheet()->mergeCells('H2:J2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('H3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('I3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('J3', '小计'); //查找在选择时间之前的表 //查出组织机构 $zzjg=db('management')->whereIn('id',[7,10,11,12,13,14,15])->select(); // $title_array = ['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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH']; $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']; $line=4;//从第四行开始写数据 $zong=[]; foreach ($zzjg as $key => $value) { $lineData=[]; $lineData['A']=$value['names']; $lineData['B']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特')->where('a.AddTime','<',$stime)->count(); $lineData['C']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信')->where('a.AddTime','<',$stime)->count(); $lineData['D']=$lineData['B']+$lineData['C']; $lineData['E']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特') ->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count(); $lineData['F']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信') ->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count(); $lineData['G']=$lineData['E']+$lineData['G']; $lineData['H']=$lineData['B']+$lineData['E']; $lineData['I']=$lineData['C']+$lineData['F']; $lineData['J']=$lineData['D']+$lineData['G']; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $lineData[$zimu]);//计算值所在的位置 if($zimu!='A'){ $zong[$zimu]=$zong[$zimu]+$lineData[$zimu];//计算总计 } } $line++; } $zong['A']='总计'; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $zong[$zimu]);//计算值所在的位置 } //===================================================================================================================================================== foreach($zzjg as $zzjgInfo){ //创建新sheet表 $objPHPExcel->createSheet($sheetIndex); $sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++); $sheet->setTitle($zzjgInfo['names']);//单位名称 $sheet->getDefaultColumnDimension()->setWidth(20);//设置默认列宽度 $sheet->getColumnDimension('F')->setWidth(40);//设置默认列宽度 $objPHPExcel->getActiveSheet()->SetCellValue('A1', '序号'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', '仪表管理号'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', '点位名称'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', '仪表口径'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', '计量性质'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', '安装地址'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', '安装时间'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', '仪表分类'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', '管理单位'); $objPHPExcel->getActiveSheet()->SetCellValue('J1', '是否在用'); $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']; $meters=db('meter_equip2') ->alias('a') ->join('meter_info m','a.id = m.id') ->where('m.manageunit',$zzjgInfo['id']) // ->where('m.manageunit',11) ->whereIn('brand',['肯特','拓安信']) ->field('m.*,a.staname,a.AddTime') ->select(); $meterData=[]; $line=2;//从第二行开始写 foreach($meters as $meter){ $meterData['A']=$meter['ROW_NUMBER']; $meterData['B']=$meter['id']; $meterData['C']=$meter['staname']; $meterData['D']=$meter['caliber']; $meterData['E']=$meter['measurecon']; $meterData['F']=$meter['installpl']; $meterData['G']=$meter['AddTime']; $meterData['H']=$meter['brand']; $meterData['I']=$zzjgInfo['names']; $dic_status=[""=>"使用","0"=>"使用","1"=>"停用","2"=>"报废"]; $meterData['J']=$dic_status[$meter['ybstatus']]; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $meterData[$zimu]);//计算值所在的位置 } $line++; } } //====================================================================================================================================================== $objPHPExcel->setActiveSheetIndex(0); //设置sheet起始位置 ob_clean(); header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;'); header('Content-Disposition:attachment;filename=电磁水表在装表统计'. date('Y-m-d', $begin) . '-' . date('Y-m-d', $end) .'.xlsx'); $save_object = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $save_object->save('php://output'); }
-
thinkphp分组查询,并查出每组最新数据 $subsql = Db::table('value_meter_yj_message')->where(['read'=>0])->field('equip_id,max(time) as time1,type')->group('equip_id,type') ->buildSql(); $list=Db::table('value_meter_yj_message') ->alias('a') ->join([$subsql=> 'w'], 'a.equip_id = w.equip_id and a.time=w.time1') ->field('a.*') ->select();
-
sqlserver对字段相同的表进行合并查询 如果有一个项目每个月分一次表,字段是相同的,想进行正常查询用以下方法:/** * 分表后根据合并分表查询, * tp的union方法好像对sqlserver不好使 */ public function get_union_index($stime,$etime,$eqid,$field='*'){ //首先先查出日期所对应的月份,确认要查的表 $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->select(); return $sql; } /** * 分页版 */ public function get_union_page($stime,$etime,$eqid,$field='*'){ $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->paginate(); return $sql; } /** * 第一条或最后一条 */ public function get_union_first($stime,$etime,$eqid,$field='*',$order='desc'){ $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime',$order)->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->find(); return $sql; } public function join_instant_table($begin, $end, $field = '*') { $tables = self::get_instant_tables($begin, $end); $sqls = []; foreach ($tables as $table) { //判断表是否存在 try { db::name($table)->find(); $sql = sprintf("select {$field} from value_%s", $table); $sqls[] = $sql; } catch (\Throwable $e) { continue; } } if ($sqls) { return '(' . implode(' union all ', $sqls) . ') i'; } return ''; } /** * @param begin string 开始时间 * @param end string 结束时间 * @return: */ public function get_instant_tables($begin,$end) { $table_basis = 'meter_instant'; $initial = strtotime('2022-12-01'); $begin = !empty($begin) ? strtotime($begin) : $initial; $end = !empty($end) ? strtotime($end) : time(); if ($end > time()) $end = time(); // if ($end < $initial) return [$table_basis.'_202212']; $end3 = strtotime(date('Y-m-01'));//2 // if ($begin >= $end3) return [$table_basis]; $tables = []; while ($begin <= $end) { if ($begin < $initial) { $tables[] = $table_basis.'_202212'; } else if ($begin >= $end3) { $tables[] = $table_basis; } else { $tables[] = $table_basis.'_'.date('Y',$begin).date('m',$begin); } $begin = strtotime('+1 month '.date('Y-m-01',$begin)); } return array_values(array_unique($tables)); }
