model = new \app\common\model\Daily();
}
public function tt(){
$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 static function ll($list,$list1,$arr=[]){
foreach ($list as $k=>$v){
foreach ($list1 as $k1=>$v1){
if($v['m1'] < $v1['m2']){
$list[$k]['m1'] = 0;
$list1[$k1]['m2'] = $v1['m2']-$v['m1'];
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v['m1'],
];
unset($list[$k]);
self::ll($list,$list1,$arr);
break ;
}else if ($v['m1'] > $v1['m2']){
$list[$k]['m1'] = $v['m1']-$v1['m2'];
$list1[$k1]['m2'] =0;
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v1['m2'],
];
unset($list1[$k1]);
self::ll($list,$list1,$arr);
break;
}else{
$list[$k]['m1'] = $v['m1']-$v1['m2'];
$list1[$k1]['m2'] =0;
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v1['m2'],
];
unset($list1[$k1]);
unset($list[$k]);
self::ll($list,$list1,$arr);
break ;
}
}
}
return $arr;
}
public static function ll1($list,$list1){
$arr = [];
while (!empty($list) && !empty($list1)){
foreach ($list as $k=>$v){
foreach ($list1 as $k1=>$v1){
if($v['m1'] < $v1['m2']){
$list[$k]['m1'] = 0;
$list1[$k1]['m2'] = $v1['m2']-$v['m1'];
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v['m1'],
];
unset($list[$k]);
}else if ($v['m1'] > $v1['m2']){
$list[$k]['m1'] = $v['m1']-$v1['m2'];
$list1[$k1]['m2'] =0;
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v1['m2'],
];
unset($list1[$k1]);
}else{
$list[$k]['m1'] = $v['m1']-$v1['m2'];
$list1[$k1]['m2'] =0;
$arr[] = [
'm1_id'=>$v['id'],
'm2_id'=>$v1['id'],
'm'=>$v1['m2'],
];
unset($list1[$k1]);
unset($list[$k]);
}
break;
}
break;
}
}
return $arr;
}
public function tx(){
$list = Db::name('user')
->where('m1','>',0)
->select();
$list1 = Db::name('user')
->where('m2','>',0)
->select();
$r = self::ll1($list,$list1);
echo "";
print_r(array_sum(array_column($r,'m')));
print_r($r);
die();
}
}