model = new \app\common\model\Daily();
}
public function device(){
set_time_limit(0);
ini_set("memory_limit","512M");
exit();
$file = "./1.xls";
$orgId = 100;
$cate1 = 22; //灭火器
$cate2 = 23; // 消火栓
$form1 = "20,21,22,23";
$form2 = "24,25,26,27";
$userId = 3139;
$data = ExcelUtil::read($file,['title','sn','area','cate_id','content']);
$i = 0;
foreach ($data as $k=>$v){
if($i >= 906){
$data[$k]['org_id'] = $orgId;
$data[$k]['enable'] = 1;
if($v['cate_id'] == "灭火器"){
$data[$k]['cate_id'] = $cate1;
$data[$k]['device_form'] = $form1;
}else{
$data[$k]['cate_id'] = $cate2;
$data[$k]['device_form'] = $form2;
}
$deivceId = Db::name('device')->insertGetId($data[$k]);
if($deivceId){
Db::name('device_user')->insert([
"device_id" => $deivceId,
"user_id" => $userId,
]);
}
}
$i++;
}
halt($data);
}
public function device2(){
set_time_limit(0);
ini_set("memory_limit","2048M");
$file = "./2.xls";
$orgId = 100;
$cate1 = 24; //月度
$cate2 = 25; //季度
$cate3 = 26; //年度
$cate4 = 27; //半年度
$form1 = "20,21,22,23";
$form2 = "24,25,26,27";
$userId = 3139;
$data = ExcelUtil::read($file,['num','title','sn','spec','content','area','forms','cate']);
// halt($data);
$forms = [];
$i = 0;
foreach ($data as $k=>$v){
$forms = array_merge($forms,explode("；",$v['forms']));
if($i >= 228){
$device = [
'org_id' => $orgId,
'title' => $v['title'],
'cate_id' => 0,
'enable' => 1,
'del' => 0,
'create_time' => getTime(),
'sn' => $v['sn'],
'spec' => $v['spec'],
'area' => $v['area'],
'content' => $v['content']
];
$cateId = 0;
if($v['cate'] == "1年/次"){
$cateId = $cate3;
//$data[$k]['device_form'] = $form1;
}if($v['cate'] == "1季度/次"){
$cateId = $cate2;
//$data[$k]['device_form'] = $form1;
}if($v['cate'] == "1月/次"){
$cateId = $cate1;
//$data[$k]['device_form'] = $form1;
}else{
$cateId = $cate4;
// $data[$k]['device_form'] = $form2;
}
$device['cate_id'] = $cateId;
$farr = $this->formatForms(explode("；",$v['forms']));
$formIds = [];
foreach ($farr as $kk=>$vv){
// 检查是否已存在
$forminfo = Db::name("device_form")->where('org_id',$orgId)->where('cate_id',$cateId)->where('title',$vv)->find();
if($forminfo){
$formIds[] = $forminfo['id'];
}else{
$fid = Db::name("device_form")->insertGetId([
'org_id' => $orgId,
'cate_id' => $cateId,
'title' => $vv,
'sort' => 50,
'del' => 0,
'enable' => 1,
'create_time' => getTime()
]);
$formIds[] = $fid;
}
}
$device['device_form'] = implode(",",$formIds);
$deivceId = Db::name('device')->insertGetId($device);
if($deivceId){
Db::name('device_user')->insert([
"device_id" => $deivceId,
"user_id" => $userId,
]);
}
}
$i++;
}
// $forms = array_unique($forms);
// $nforms = [];
// foreach ($forms as $k=>$v){
// if(!empty(trim($v))){
// $nforms[] = $this->format($v);
// }
// }
//
// $farr = [];
// foreach ($nforms as $k=>$v){
// $farr[] = [
// "org_id" => $orgId,
// "title" => $v,
// "sort" => 50,
// "del" => 0,
// "enable" => 1,
// "create_time" => getTime(),
// ];
// }
//
// $ids = [20,21,22,23,24,25,26,27];
// dump($nforms);
halt($data);
}
public function formatForms($lists){
$forms = array_unique($lists);
$nforms = [];
foreach ($forms as $k=>$v){
if(!empty(trim($v))){
$nforms[] = $this->format($v);
}
}
return $nforms;
}
public function format($val){
$ff = [
'1.','2.','3.','4.','5.','6.','7.','8.','9.',
'5、','6、','7，'
];
foreach ($ff as $k=>$v){
$val = str_ireplace($v,"",$val);
}
return trim($val);
}
public function tt(){
$orgId = 28;
$mode = 3;
// $res = send_jpush(['8'],AppMsg::PUSH_WORKER_ORDER_SEND,'',['id'=>1]);
// echo "";
// print_r($res);
// die;
}
public function index(){
halt(get_unique_sn());
$cc = config('database.database');
halt($cc);
// $json = [
// 'users' => [9],
// 'msg' => 'ssssssssssssssss',
// 'extra' => [],
// 'type' => AppMsg::PUSH_WORKER_ORDER_SEND
// ];
// queue_push(json_encode($json),0);
$client = new \GuzzleHttp\Client();
$url = "https://www.baidu.com";
$response = $client->request('GET', $url);
$result = $response->getBody();
halt($result);
}
public function setSn(){
$orders = Db::name('orders')->where('sn','')->limit(300)->field('id,create_time')->select();
$orders = $orders?$orders:[];
$nums = 100001;
foreach ($orders as $k=>$v){
Db::name('orders')->where('id',$v['id'])->update(['sn' => date('YmdHis').$nums]);
$nums++;
}
dump(count($orders));
}
public function setSn2(){
$orders = Db::name('todo')->where('sn','')->limit(300)->field('id,create_time')->select();
$orders = $orders?$orders:[];
$nums = 100001;
foreach ($orders as $k=>$v){
Db::name('todo')->where('id',$v['id'])->update(['sn' => date('YmdHis').$nums]);
$nums++;
}
dump(count($orders));
}
public function sqldic(){
$db = config('database.database');
$sql = <<$v){
if(!in_array($v['name'],$tables)){
$mdata = Db::query($sql2."'{$v['name']}'");
if(!$mdata){
halt('查询表错误');
}
$table = $mdata[0];
if($table['type'] != 'VIEW'){
$tables[] = $v['name'];
$cols = [];
foreach ($lists as $kk=>$vv){
if($vv['name'] == $v['name']){
$cols[] = [
'name' => $vv['col'],
'type' => $vv['type'],
'comment' => $vv['comment']
];
}
}
$dic[] = [
'name' => $v['name'],
'engine' => $table['engine'],
'type' => $table['type'],
'collation' => $table['collation'],
'comment' => $table['comment'],
'cols' => $cols
];
}
}
}
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle('数据库字典');
$titles = ['表名','引擎','排序规则','注释'];
foreach ($titles as $key => $value) {
$worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}
$arr = array('A','B','C','D');
$styleArray = [
'font' => [
'bold' => true
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$styleArray2 = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
foreach($arr as $k=>$v){
$worksheet->getStyle($v.'1')->applyFromArray($styleArray)->getFont()->setSize(12);
$worksheet->getStyle($v)->applyFromArray($styleArray2)->getAlignment()->setWrapText(true);//换行
$worksheet->getColumnDimension($v)->setWidth(25);
}
$lists = $dic;
foreach ($lists as $k=>$v){
$worksheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
$worksheet->setCellValueByColumnAndRow(2, $k + 2, $v['engine']);
$worksheet->setCellValueByColumnAndRow(3, $k + 2, $v['collation']);
$worksheet->setCellValueByColumnAndRow(4, $k + 2, $v['comment']);
$cols = $v['cols'];
$sheet = $spreadsheet->createSheet($k+10)->setTitle($v['name']);
$worksheet->getCellByColumnAndRow(1, $k + 2)->getHyperlink()->setUrl("sheet://{$v['name']}!A1");
foreach ($cols as $kk=>$vv){
$this->writeSheet($sheet,$cols,$k + 2);
}
}
$worksheet->setCellValueByColumnAndRow(1, $k + 3, '共'.count($lists).'张表');
$fileName = '数据库字典.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');
}
private function writeSheet($sheet,$data,$row){
$arr = array('A','B','C','D');
$styleArray = [
'font' => [
'bold' => true
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$styleArray2 = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
foreach($arr as $k=>$v){
$sheet->getStyle($v.'2')->applyFromArray($styleArray)->getFont()->setSize(12);
$sheet->getStyle($v)->applyFromArray($styleArray2)->getAlignment()->setWrapText(true);//换行
$sheet->getColumnDimension($v)->setWidth(20);
}
$sheet->setCellValueByColumnAndRow(1, 1, '返回目录页');
$sheet->getCellByColumnAndRow(1, 1)->getHyperlink()->setUrl("sheet://数据库字典!A".$row);
$titles = ['字段名','类型','注释'];
foreach ($titles as $key => $value) {
$sheet->setCellValueByColumnAndRow($key+1, 2, $value);
}
foreach ($data as $k=>$v){
$sheet->setCellValueByColumnAndRow(1, $k + 3, $v['name']);
$sheet->setCellValueByColumnAndRow(2, $k + 3, $v['type']);
$sheet->setCellValueByColumnAndRow(3, $k + 3, $v['comment']);
}
}
public function host(){
exit();
$host1 = "http://lf.jya-tech.com";
$host2 = "http://wy.gzbjwy.net";
$host = "http://logistics.hetaigl.com";
// $lists = Db::name('screen_module')->select();
// foreach ($lists as $k=>$v){
// if($v['img']){
// $img = str_ireplace($host1,$host,$v['img']);
// $img = str_ireplace($host2,$host,$img);
// Db::name('screen_module')->where('id',$v['id'])->setField('img',$img);
// }
// }
$lists = Db::name('screen')->select();
foreach ($lists as $k=>$v){
if($v['img']){
$img = str_ireplace($host1,$host,$v['img']);
$img = str_ireplace($host2,$host,$img);
Db::name('screen')->where('id',$v['id'])->setField('img',$img);
}
}
$lists = Db::name('screen_template')->select();
foreach ($lists as $k=>$v){
if($v['img']){
$img = str_ireplace($host1,$host,$v['img']);
$img = str_ireplace($host2,$host,$img);
Db::name('screen_template')->where('id',$v['id'])->setField('img',$img);
}
}
// $lists = Db::name('app_icon')->select();
// foreach ($lists as $k=>$v){
// if($v['path']){
// $img = str_ireplace($host1,$host,$v['path']);
// $img = str_ireplace($host2,$host,$img);
// Db::name('app_icon')->where('id',$v['id'])->setField('path',$img);
// }
// if($v['url']){
// $img = str_ireplace($host1,$host,$v['url']);
// $img = str_ireplace($host2,$host,$img);
// Db::name('app_icon')->where('id',$v['id'])->setField('url',$img);
// }
// }
}
private function getSize($text,$size,$weight,$height,$i=1,$angle=0){
//获取文字信息
$font = env('root_path').'public/static/hylht.ttf';
// $size = 100;
$info = imagettfbbox($size, $angle, $font, $text);
$minx = min($info[0], $info[2], $info[4], $info[6]);
$maxx = max($info[0], $info[2], $info[4], $info[6]);
$miny = min($info[1], $info[3], $info[5], $info[7]);
$maxy = max($info[1], $info[3], $info[5], $info[7]);
/* 计算文字初始坐标和尺寸 */
$x = $minx;
$y = abs($miny);
$w = $maxx - $minx;
$h = $maxy - $miny;
$xx = ($weight - $w) / 2;
$yy = $height - ($h+10)*$i;
return [$xx,$yy];
}
public function excel2(){
set_time_limit(0);
ini_set("memory_limit","2048M");
exit();
$page = 9;
$size = 10000;
$lists = Db::name('oa_lis')->page($page,$size)->order('start asc')->select();
if(!$lists){
halt('无数据');
}
foreach ($lists as $k=>$v){
$btime = date('Y-m-d',strtotime($v['start'])).' 03:00:00';
if($btime > $v['start']){ // 算昨天的
$v['day'] = date('Ymd',strtotime($btime) - 24*60*60);
}
$ret = Db::name('oa')->where('name',$v['name'])->where('day',$v['day'])->find();
if($ret){
if(!$ret['end'] || $ret['end'] < $v['start']){
Db::name('oa')->where('id',$ret['id'])->update([
'end' => $v['start']
]);
}
}else{
if($btime <= $v['start']){
Db::name('oa')->insert([
'name' => $v['name'],
'start' => $v['start'],
'day' => $v['day'],
]);
}
}
}
halt('结束2');
}
public function excel3(){
$names = [
'冯主任','张棉旺','吴晓刚','王先生','肖先生','王君','杨林源','王杰','董春艳','吴新刚','胡添滢','荣海燕',
'丁文佳','王国玲','杨和融','赵雷','杨劲','石少亮','许倩鸿','杨璐','王玉玲','秦健','汪涵','郑琳','蔡涛',
'贾永利','陈婧','周钊伟','雷春光','孙晨','谢正辉','吕雯','张宁','杨耀强','李本新','李月辉','陈晨','林进军',
'寇建娣','王晓博','齐学东','隗永芝','牛全起','李鑫','闫勇哲','郭风军','张光利','文辉','王晨','王敏','范泉',
'刘学政','王昱','马跃征','董杰','刘卉丛','梁艳','董军红','刘晓伟','赵雪','赵南','伍力','王宇','张京环',
'李永尖','陈鹏','贾自强','杨云','黄志军','张天舒','张云','王新先','沈涛','吴德安','王帅','龙朝辉','王瑞',
'刘爱枝','耿波','薛晓辉','徐新建','魏京菊','周云隆','孙立民','刁文矛','王鹏飞','冯峰-华馨泰','刘建宁-华馨泰'
];
// $days = [1,2,3,4,5,6,7,10,16,17,23,24,30,31];
// $mdays = 31;
// $m = '10';
// $month = '202110';
$days = [6,7,13,14,20,21,27,28];
$mdays = 30;
$m = '11';
$month = '202111';
// $days = [4,5,11,12,18,19,25,26];
// $mdays = 31;
// $m = '12';
// $month = '202112';
$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 = [];
for( $i=0;$i<$mdays*3 + 4;$i++ ){
$r1 = $i%26;
$r2 = floor($i/26);
if($r2 == 0){
$lins[] = $char[$r1];
}else if($r2 == 1){
$lins[] = 'A'.$char[$r1];
}else if($r2 == 2){
$lins[] = 'B'.$char[$r1];
}else if($r2 == 3){
$lins[] = 'C'.$char[$r1];
}
}
$lists = [];
foreach ($names as $k=>$v){
$d = [
'name' => $v,
'tday' => $mdays - count($days),
];
$thour = 0;
for( $i=1;$i<=$mdays;$i++ ){
if($i < 10){
$day = $month.'0'.$i;
}else{
$day = $month.$i;
}
$d['s'.$i] = '';
$d['e'.$i] = '';
$d['h'.$i] = 0;
$info = Db::name('oa')->where('name',$v)->where('day',$day)->find();
if($info){
$d['s'.$i] = date('H:i:s',strtotime($info['start']));
$d['e'.$i] = $info['end']?date('H:i:s',strtotime($info['end'])):'';
$hour = 0;
if($info['end']){
$hour = round((strtotime($info['end']) - strtotime($info['start']))/3600,2);
}
$d['h'.$i] = $hour;
$thour = $thour + $hour;
}
}
$d['thour'] = $thour;
$d['avghour'] = round($thour/$d['tday'],2);
$lists[] = $d;
}
// halt($lists);
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('考勤'.$m.'月');
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, '姓名');
$spreadsheet->getActiveSheet()->mergeCells('A1:A2');
$j = 1;
for( $i=1;$i<=$mdays*3;$i=$i+3 ){
$spreadsheet->getActiveSheet()->mergeCells($lins[$i].'1:'.$lins[$i+2].'1');
$worksheet->setCellValueByColumnAndRow($i+1, 1, $j.'日');
$worksheet->setCellValueByColumnAndRow($i+2, 1, '');
$worksheet->setCellValueByColumnAndRow($i+3, 1, '');
$j++;
}
$worksheet->setCellValueByColumnAndRow($mdays*3+2, 1, '应出勤天数');
$spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+1].'1:'.$lins[$mdays*3+1].'2');
$worksheet->setCellValueByColumnAndRow($mdays*3+3, 1, '总时长(h)');
$spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+2].'1:'.$lins[$mdays*3+2].'2');
$worksheet->setCellValueByColumnAndRow($mdays*3+4, 1, '平均时长(h)');
$spreadsheet->getActiveSheet()->mergeCells($lins[$mdays*3+3].'1:'.$lins[$mdays*3+3].'2');
$worksheet->setCellValueByColumnAndRow(1, 2, '');
for( $i=1;$i<=$mdays*3;$i=$i+3 ){
$worksheet->setCellValueByColumnAndRow($i+1, 2, '上班');
$worksheet->setCellValueByColumnAndRow($i+2, 2, '下班');
$worksheet->setCellValueByColumnAndRow($i+3, 2, '时长(h)');
}
foreach ($lists as $k=>$v){
$n = $v['name'];
if($n == '冯主任'){
$n = '冯磊';
}else if($n == '王先生'){
$n = '王汝江';
}else if($n == '肖先生'){
$n = '肖洪钦';
}else if($n == '冯峰-华馨泰'){
$n = '冯峰';
}else if($n == '刘建宁-华馨泰'){
$n = '刘建宁';
}
$worksheet->setCellValueByColumnAndRow(1, $k+3, $n);
$j = 1;
for( $i=1;$i<=$mdays*3;$i=$i+3 ){
$worksheet->setCellValueByColumnAndRow($i+1, $k+3, $v['s'.$j]);
$worksheet->setCellValueByColumnAndRow($i+2, $k+3, $v['e'.$j]);
$worksheet->setCellValueByColumnAndRow($i+3, $k+3, $v['h'.$j]);
$j++;
}
$worksheet->setCellValueByColumnAndRow($mdays*3+2, $k+3, $v['tday']);
$worksheet->setCellValueByColumnAndRow($mdays*3+3, $k+3, $v['thour']);
$worksheet->setCellValueByColumnAndRow($mdays*3+4, $k+3, $v['avghour']);
}
$filename = '考勤'.$m.'月.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 ll(){
$list = Db::name('mate_goods')
->alias('a')
->join('mate_cate b','a.cate_id=b.id')
->where('a.org_id',27)
->field('a.*,b.title as cateName')
->select();
$succ = 0;
foreach ($list as $k=>$v){
if($v['cateName']=='电工类'){
$a = 9;
}elseif ($v['cateName']=='木工类'){
$a = 93;
}elseif ($v['cateName']=='水工类'){
$a = 128;
}
$r = Db::name('mate_goods')
->where('id',$v['id'])
->update([
'update_time'=>getTime(),
'cate_id'=>$a
]);
if($r){
$succ++;
}
}
echo $succ;
}
}