year; $lastYear = $currentYear - 1; $currentMonth = Carbon::now()->month; $totalValueAllTime = SaleOrdersProduct::where('del_time',0)->sum('finished_num'); $totalValueLastYear = SaleOrdersProduct::where('del_time',0)->whereRaw("YEAR(FROM_UNIXTIME(crt_time)) = $lastYear")->sum('finished_num'); $totalValueCurrentYearMonth = SaleOrdersProduct::where('del_time',0)->whereRaw("YEAR(FROM_UNIXTIME(crt_time)) = $currentYear AND MONTH(FROM_UNIXTIME(crt_time)) = $currentMonth")->sum('finished_num'); return $this->json_return(200,'',['total_time'=>$totalValueAllTime, 'total_last_year'=>$totalValueLastYear, 'total_current_month'=>$totalValueCurrentYearMonth]); } /** * 项目进度 * @param Request $request * @return array */ public function order_process1(Request $request) { $result = SaleOrdersProduct::where('del_time',0) ->select(DB::raw('sum(order_quantity) as total'), DB::raw('sum(finished_num) as finished_num'),'out_order_no as Code','customer_name as CustomerName') ->groupBy('out_order_no') ->orderBy('id', 'desc') // 添加这一行以按创建时间降序排序 ->take(200) // 添加这一行以限制结果集大小为100 ->get()->toArray(); if(! empty($result)){ foreach ($result as $key => $value){ $result[$key]['rate'] = number_format($value['finished_num'] / $value['total'] * 100,2) * 1; unset($result[$key]['total']); unset($result[$key]['finished_num']); } $rate = array_column($result, 'rate'); // 提取列作为排序依据 array_multisort($rate, SORT_DESC,$result); } return $this->json_return(200,'',$result); } /** * 历史项目 在手项目 * @param Request $request * @return array */ public function project_region(Request $request){ $all = SaleOrdersProduct::where('del_time',0) ->whereColumn('order_quantity','=','finished_num') ->select('out_order_no') ->groupBy('out_order_no') ->get() ->toArray(); $all = array_column($all,'out_order_no'); $not_all = SaleOrdersProduct::where('del_time',0) ->whereColumn('order_quantity','<>','finished_num') ->select('out_order_no') ->groupBy('out_order_no') ->get() ->toArray(); $not_all = array_column($not_all,'out_order_no'); $all = array_diff($all, $not_all); $arr = [ "all_num" => count($all) + 8000, "num" => count($not_all), ]; return $this->json_return(200,'',$arr); } //假数据 public function output_value_month1(Request $request){ $date = date('Ymd',time()); $startDate = strtotime(date('Y-m-01 00:00:00', time())) * 1000; $endDate = strtotime(date('Y-m-t 23:59:59', time())) * 1000; $return = $this->getDayInMonth(); //获取数据 $data = SystemL::where('time','>=',$startDate) ->where('time','<',$endDate) ->where('data_point_name',SystemL::stop) ->where('value',0) ->select('time','value') ->get()->toArray(); if(! empty($data)){ foreach ($data as $value){ $crt_time = date('Y-m-d',$value['time'] / 1000); if(isset($return[$crt_time])){ $return[$crt_time]['num'] += 1; } } ksort($return); } $return = array_values($return); return $this->json_return(200,'',$return); } function getDayInMonth(){ $startDate = strtotime(date('Y-m-01 00:00:00', time())); // 获取当前日期 $currentDate = time(); $dates = array(); while ($currentDate >= $startDate) { $t = date('Y-m-d', $currentDate); $dates[$t] = [ 'value' => $t, 'num' => 0 ]; $currentDate = strtotime('-1 day', $currentDate); } return $dates; } /** * 本月质量 * @param Request $request * @return array */ public function output_value_month11(Request $request){ $date = date('Ymd',time()); $startDate = strtotime(date('Y-m-01 00:00:00', time())); $endDate = strtotime(date('Y-m-t 23:59:59', time())); //工序----------------------------- $model = new OrdersProductProcess(['channel' => $date]);//当前季度的数据 $data = $model->where('del_time',0) ->where('status',4) ->select('crt_time') ->where('crt_time','>=',$startDate) ->where('crt_time','<=',$endDate) ->get()->toArray(); $return = []; if(! empty($data)){ foreach ($data as $value){ $crt_time = date('Y-m-d',$value['crt_time']); if(isset($return[$crt_time])){ $return[$crt_time]['num'] += 1; }else{ $return[$crt_time] = [ 'num' => 1, 'value' => $crt_time ]; } } } $return = array_values($return); return $this->json_return(200,'',$return); } /** * 产量趋势图 * @param Request $request * @return array */ public function output_value_efficiency1(Request $request){ // 获取当前时间戳 $currentTimestamp = time(); // 输出过去两周的起止时间(包括当前日期) $timestamp = strtotime("-13 days", $currentTimestamp); $startOfDay = strtotime(date('Y-m-d 00:00:00', $timestamp)); $endOfDay = strtotime(date('Y-m-d 23:59:59', $currentTimestamp)); $box = Box::where('del_time',0) ->where('crt_time','>=',$startOfDay) ->where('crt_time','<=',$endOfDay) ->select('crt_time','top_order_no','order_no') ->orderBy('crt_time','desc') ->get()->toArray(); $result = []; if(! empty($box)){ foreach ($box as $value){ $model = new BoxDetail(['channel' => $value['top_order_no']]); $map = $model->where('del_time',0) ->where('order_no',$value['order_no']) ->select('order_no',DB::raw('sum(num) as num')) ->groupBy('order_no') ->pluck('num','order_no') ->toArray(); $output = $map[$value['order_no']] ?? 0; $times = date('Y-m-d',$value['crt_time']); if(isset($result[$times])){ $result[$times]['output'] += $output; }else{ $result[$times] = [ 'time' => $times, 'output' => $output ]; } } } $result = array_values($result); return $this->json_return(200,'',$result); } //产量趋势图(完工) public function output_value_efficiency(Request $request){ $list = DispatchSub::where('del_time',0) ->where('finished_num','>=',0) ->select('crt_time','finished_num') ->orderBy('id','desc') ->limit(20) ->get()->toArray(); $return = []; foreach ($list as $value){ $date = date("Y-m-d",$value['crt_time']); if(isset($return[$date])){ $num = bcadd($value['finished_num'], $return[$date]['output'],3); $return[$date]['output'] = $num; }else{ $return[$date] = [ 'output' => $value['finished_num'], 'time' => $date, ]; } } ksort($return); $result = array_values($return); return $this->json_return(200,'',$result); } function getDay(){ // 获取当前季度的开始日期 $month = date('m'); if ($month >= 1 && $month <= 3) { $quarter = 1; } elseif ($month >= 4 && $month <= 6) { $quarter = 2; } elseif ($month >= 7 && $month <= 9) { $quarter = 3; } else { $quarter = 4; } $year = date('Y'); // 获取当前年份 if ($quarter == 1) { $startDate = strtotime("$year-01-01"); // 第一季度的开始日期 } elseif ($quarter == 2) { $startDate = strtotime("$year-04-01"); // 第二季度的开始日期 } elseif ($quarter == 3) { $startDate = strtotime("$year-07-01"); // 第三季度的开始日期 } else { $startDate = strtotime("$year-10-01"); // 第四季度的开始日期 } // 获取当前日期 $currentDate = time(); // 生成当前季度到今天为止的所有日期 $dates = array(); while ($currentDate >= $startDate) { $t = date('Y-m-d', $currentDate); $dates[$t] = [ 'time' => $t, 'output' => 0 ]; $currentDate = strtotime('-1 day', $currentDate); } return $dates; } /** * 工序负荷全览 * @param Request $request * @return array */ public function capacity(Request $request){ $process = Process::where('del_time',0)->get()->toArray(); $list = DispatchSub::where('del_time',0) ->where('dispatch_quantity','>=',0) ->select('crt_time','dispatch_quantity') ->orderBy('id','desc') ->limit(20) ->get()->toArray(); $return = []; foreach ($list as $value){ $date = date("Ymd",$value['crt_time']); if(isset($return[$date])){ $num = bcadd($value['dispatch_quantity'], $return[$date],3); $return[$date] = $num; }else{ $return[$date] = $value['dispatch_quantity']; } } $maxValue = empty($return) ? 0 : max($return); $today = $return[date("Ymd")] ?? 0; $rate = $maxValue ? intval($today/$maxValue * 100) : 0; $array = []; foreach ($process as $value){ $array[] = [ [ 'title' => $value['title'], 'rate' => $rate ] ]; } //工序----------------------------- return $this->json_return(200,'',['data' => $array]); } /** * 设备信息 * @param Request $request * @return array */ public function product_num(Request $request){ //数据模型 $models = []; foreach (SystemL::$device as $k => $v){ $models[$k] = [ "machine_day_num"=> 0, "machine_month_num"=> 0, "machine_week_num"=> 0, "break_day_num"=> 0, "break_month_num"=> 0, "break_week_num"=> 0, "start_time"=> '', "day_num"=> 0, "week_num"=> 0, "month_num"=> 0, "rate"=> 0 ]; } //当天的开始与结束时间戳 $timestamp_today_start = strtotime(date("Y-m-d 00:00:00",time())); $timestamp_today_end = strtotime(date("Y-m-d 23:59:59",time())); //查询当日 $today = SystemL::where('time','>=',$timestamp_today_start * 1000) ->where('time','<=',$timestamp_today_end * 1000) ->select('device_name','data_point_name','time') ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy]) ->get()->toArray(); //组织当日数据 $this->fillData($today,1,$models); //上周时间 $previousWeekStartDate = Carbon::now()->subWeek()->startOfWeek(); $previousWeekEndDate = Carbon::now()->subWeek()->endOfWeek(); //上周开始结束日期 $start_week = $previousWeekStartDate->toDateString(); $end_week = $previousWeekEndDate->toDateString(); $last_week_key = $start_week . $end_week; list($status, $return_last_week) = $this->getRedisData($last_week_key); if(! $status){ $previousWeekStartTimeStamp = $previousWeekStartDate->timestamp * 1000; $previousWeekEndTimeStamp = $previousWeekEndDate->timestamp * 1000; //获取上周数据 $list_week = SystemL::where('time','>=',$previousWeekStartTimeStamp) ->where('time','<=',$previousWeekEndTimeStamp) ->select('device_name','data_point_name') ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy]) ->get()->toArray(); //组织上周数据 $this->fillData($list_week,2,$models); //缓存 // Cache::put($last_week_key,json_encode($list_week),86400); }else{ //有缓存 填充数据 组织上周数据 $this->fillData($return_last_week,2,$models); } //上月时间 $previousMonthStartDate = Carbon::now()->subMonth()->startOfMonth(); $previousMonthEndDate = Carbon::now()->subMonth()->endOfMonth(); //上月开始结束日期 $start_month = $previousMonthStartDate->toDateString(); $end_month = $previousMonthEndDate->toDateString(); $last_month_key = $start_month . $end_month; list($status, $return_last_month) = $this->getRedisData($last_month_key); if(! $status){ $previousMonthStartTimeStamp = $previousMonthStartDate->timestamp * 1000; $previousMonthEndTimeStamp = $previousMonthEndDate->timestamp * 1000; //获取上月数据 $list_month = SystemL::where('time','>=',$previousMonthStartTimeStamp) ->where('time','<=',$previousMonthEndTimeStamp) ->select('device_name','data_point_name') ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy]) ->get()->toArray(); //组织上月数据 $this->fillData($list_month,3,$models); //缓存 // Cache::put($last_month_key,json_encode($list_month),86400); }else{ //有缓存 填充数据 组织上周数据 $this->fillData($return_last_month,3,$models); } $return = []; foreach ($models as $key => $value){ $value['device_name'] = $key; $return[] = $value; } return $this->json_return(200,'',$return); } //在制工单 public function work_order(Request $request){ // 获取当前时间戳 $currentTimestamp = time(); $timestamp = strtotime("-3 months", $currentTimestamp); $startOfDay = strtotime(date('Y-m-d 00:00:00', $timestamp)); $endOfDay = strtotime(date('Y-m-d 23:59:59', $currentTimestamp)); $result = DispatchSub::where('del_time',0) ->where('crt_time',">=", $startOfDay) ->where('crt_time',"<=", $endOfDay) ->whereColumn('dispatch_quantity','>','finished_num') ->select('dispatch_no as order_no','process_id','product_title','technology_name','dispatch_quantity as product_num','finished_num as finish_num') ->get()->toArray(); if(! empty($result)){ $process_id = array_unique(array_column($result,'process_id')); $processMap = Process::whereIn('id',$process_id) ->pluck('title','id') ->toArray(); foreach ($result as $key => $value){ $result[$key]['procedure'] = $processMap[$value['process_id']] ?? ''; $result[$key]['product_title'] = $value['product_title'] . "(". $value['technology_name'] .")"; } } return $this->json_return(200,'',$result); } /** * 待加工 * @param Request $request * @return array */ public function nu_work_order(Request $request){ $startOfDay = strtotime(date('Y-m-d 00:00:00', strtotime("-20days"))); $result = DispatchSub::where('del_time',0) // ->where('dispatch_time_start',">=", $startOfDay) ->where('finished_num',0) ->select('dispatch_no as order_no','product_title','technology_name','dispatch_quantity as product_num','process_id') ->orderBy('id','desc') ->limit(20) ->get()->toArray(); if(! empty($result)){ $process_id = array_unique(array_column($result,'process_id')); $processMap = Process::whereIn('id',$process_id) ->pluck('title','id') ->toArray(); foreach ($result as $key => $value){ $result[$key]['procedure'] = $processMap[$value['process_id']] ?? ''; $result[$key]['product_title'] = $value['product_title'] . "(". $value['technology_name'] .")"; } } return $this->json_return(200,'',$result); } //获取缓存 public function getRedisData($cacheKey){ if(Cache::has($cacheKey)){ return [true,json_decode(Cache::get($cacheKey),true)]; } return [false, []]; } /** * 数据填充 * @param $list * @param $type * @param $models */ public function fillData($list,$type,&$models){ if(empty($list)) return; $run_time = $process_time = $fault = $start_time = []; foreach ($list as $value){ if($type == 1 && ! isset($start_time[$value['device_name']])){ $start_time_tmp = date("Y-m-d H:i:s", $value['time'] / 1000); $start_time[$value['device_name']] = $start_time_tmp; } if($value['data_point_name'] == SystemL::run || $value['data_point_name'] == SystemL::standBy){ //运行次数 if(isset($run_time[$value['device_name']])){ $run_time[$value['device_name']] += 1; }else{ $run_time[$value['device_name']] = 1; } } if($value['data_point_name'] == SystemL::standBy){ //工作次数 if(isset($process_time[$value['device_name']])){ $process_time[$value['device_name']] += 1; }else{ $process_time[$value['device_name']] = 1; } } if($value['data_point_name'] == SystemL::stop){ //故障次数 if(isset($fault[$value['device_name']])){ $fault[$value['device_name']] += 1; }else{ $fault[$value['device_name']] = 1; } } } foreach (SystemL::$device as $key => $value){ //运行次数 $run_num = $run_time[$key] ?? 0; //工作次数 $process_num = $process_time[$key] ?? 0; //故障次数 $fault_tmp = $fault[$key] ?? 0; //运行时间 $run_time_tmp = (new ReportFormsService())->calTimeReturnMin($run_num); //工作时间 $process_time_tmp = (new ReportFormsService())->calTimeReturnMin($process_num); //故障时间 $fault_time_tmp = (new ReportFormsService())->calTimeReturnMin($fault_tmp); //计划运行时间 工作时间 - 计划停机 (没有计划停机) //实际运行时间 计划运行时间 -故障停机 - 设备调整(没有设备调整 $true_process_time = $process_time_tmp - $fault_time_tmp; //有效率 实际/计划运行 时间 $efficient = $process_time_tmp > 0 ? number_format($true_process_time / $process_time_tmp,2) : 0; //表现性 加工数量/实际运行时间 $expressive = $true_process_time > 0 ? number_format($process_num / $true_process_time,2) : 0; //质量指数 加工数量- 废品数量 / 加工数量 $quality_index = $process_num > 0 ? number_format(($process_num - $fault_tmp) / $process_num,2) : 0; //OEE $oee = number_format($efficient * $expressive * $quality_index,2); if($type == 1){ $models[$key]['machine_day_num'] = $run_num; $models[$key]['day_num'] = $process_num; $models[$key]['break_day_num'] = $fault_tmp; $models[$key]['rate'] = $oee; $models[$key]['start_time'] = $start_time[$key] ?? '暂未开机'; }elseif($type == 2){ $models[$key]['machine_week_num'] = $run_num; $models[$key]['week_num'] = $process_num; $models[$key]['break_week_num'] = $fault_tmp; }elseif($type == 3){ $models[$key]['machine_month_num'] = $run_num; $models[$key]['month_num'] = $process_num; $models[$key]['break_month_num'] = $fault_tmp; } } } }