mes-sdai-server-api/application/api/controller/OrderSuperLoss.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Cache;
use \think\Request;
use \think\Db;
/**
 * 工单超节损核算接口
 */
class OrderSuperLoss extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }
    /**
     * 获取左侧菜单栏
     * @ApiMethod GET
    */
    public function getTab()
    {
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $sql = "SELECT LEFT(a.jjcp_sj, 7) AS date,RTRIM(b.`编号`) AS 编号,RTRIM(b.`名称`) as 客户名称,COUNT(DISTINCT a.jjcp_gdbh) AS count
                    FROM `成品入仓` a LEFT JOIN `物料_存货结构` b ON LEFT(a.`成品编码`, 4) = RTRIM(b.`编号`)
                    WHERE LEFT(a.jjcp_sj, 7) >= LEFT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), 7) AND a.jjcp_smb = '末 板' AND a.`成品编码` != ''
                    GROUP BY LEFT(a.jjcp_sj, 7), RTRIM(b.`编号`) ORDER BY date DESC, 编号 ASC";
        $data = Db::query($sql);
        // 创建一个新数组来存储整理后的数据
        $organizedData = [];
        // 遍历原始数据
        foreach ($data as $entry) {
            $date = date('Ym',strtotime($entry['date']));

            // 检查日期是否在已整理数据中存在
            if (isset($organizedData[$date])) {
                // 如果日期存在，将数据追加到该日期下
                $organizedData[$date][] = [
                    '客户名称' => $entry['客户名称'],
                    '客户编号' => $entry['编号'],
                    'total' => $entry['count'],
                ];
                // 统计该日期下所有数据的总和
                $organizedData[$date]['total_count'] += $entry['count'];
            } else {
                // 如果日期不存在，创建新的日期项并添加数据
                $organizedData[$date] = [
                    [
                        '客户名称' => $entry['客户名称'],
                        '客户编号' => $entry['编号'],
                        'total' => $entry['count'],
                    ]
                ];
                // 初始化该日期的总和
                $organizedData[$date]['total_count'] = $entry['count'];
            }
        }

        // 将整理后的数据调整为以日期和相加的总和为索引的格式
        $finalData = [];
        foreach ($organizedData as $date => $entries) {
            $totalCount = $entries['total_count'];
            $index = $date . '-' . $totalCount;
            unset($entries['total_count']);
            $finalData[$index] = $entries;
        }
        $this->success('请求成功',$finalData);
    }
    /**
     * 获取超节损工单列表
     * @ApiMethod GET
     * @params string date
     * @params string code
     * 报废定额=（基础损耗+损耗率*计划产量）*损耗系数*计损色数（特定工序会有，如果无就不乘）
     * 允损比例=报废定额/计划产量
     * 目标合格率 = 100%-超节损工单中显示工序的允损比例之和
     *
    */
    public function getList(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->param();
        $where = [];
        if (!empty($params['code'])) {
            if (!empty($params['search'])) {
                $this->error('参数错误');
            }
//            $where['a.成品编码'] = $params['code'];
        }
        if (!empty($params['date'])) {
            if (!empty($params['search'])) {
                $this->error('参数错误');
            }
            $where['a.jjcp_sj' ] = array('like',$params['date'].'%');
        }
        if (!empty($params['search'])) {
            if (!empty($params['date']) || !empty($params['code'])) {
               $this->error('参数错误');
            }
            $where['a.成品名称'] = array('like','%'.$params['search'].'%');
        }
        $where['b.行号' ] = 1;
        $where['a.jjcp_smb' ] = '末 板';
        $limit = $params['limit'];
        if (empty($limit)){
            $limit = 15;
        }
        $pages = $params['page'];
        if (empty($pages)){
            $pages = 1;
        }
        $is_have_cache = false;
        if ($is_have_cache === false){
            if (isset($params['code'])){
                $data = db('成品入仓')->alias('a')
                    ->join('工单_基本资料 b', 'a.jjcp_gdbh = b.Gd_gdbh','left')
                    ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh','left')
                    ->where($where)
                    ->where('LEFT(a.成品编码, 4) = '.$params['code'])
                    ->field('DISTINCT(a.jjcp_gdbh) as Gd_gdbh,a.jjcp_yjno,rtrim(a.成品编码) as 成品编码,rtrim(a.成品名称) as 成品名称,a.jjcp_sj,a.jjcp_smb,b.计量单位,
                b.Gd_khdh, b.实际投料,c.yj_Yjno, c.yj_ls')
                    ->order('b.Gd_khdh,a.成品编码 asc,a.jjcp_sj desc')
                    ->page($pages)
                    ->limit($limit)
                    ->select();
                $total = db('成品入仓')->alias('a')
                    ->join('工单_基本资料 b', 'a.jjcp_gdbh = b.Gd_gdbh','left')
                    ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh','left')
                    ->where($where)
                    ->where('LEFT(a.成品编码,4) ='.$params['code'])
                    ->count();
            }else{
                $data = db('成品入仓')->alias('a')
                    ->join('工单_基本资料 b', 'a.jjcp_gdbh = b.Gd_gdbh','left')
                    ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh','left')
                    ->where($where)
                    ->field('DISTINCT(a.jjcp_gdbh) as Gd_gdbh,a.jjcp_yjno,rtrim(a.成品编码) as 成品编码,rtrim(a.成品名称) as 成品名称,a.jjcp_sj,a.jjcp_smb,b.计量单位,
                b.Gd_khdh, b.实际投料,c.yj_Yjno, c.yj_ls')
                    ->order('b.Gd_khdh,a.成品编码 asc,a.jjcp_sj desc')
                    ->page($pages)
                    ->limit($limit)
                    ->select();
                $total = db('成品入仓')->alias('a')
                    ->join('工单_基本资料 b', 'a.jjcp_gdbh = b.Gd_gdbh','left')
                    ->join('工单_印件资料 c', 'a.jjcp_gdbh = c.Yj_Gdbh','left')
                    ->where($where)
                    ->count();
            }
            foreach ($data as $key => $value){
                //查出成品数量及日期
                $cp_sql = "SELECT SUM(jjcp_sl) as cp_sl,MAX(jjcp_sj) as jjcp_sj FROM `成品入仓` WHERE jjcp_gdbh = '{$value['Gd_gdbh']}' GROUP BY jjcp_gdbh";
                $cp_data = Db::query($cp_sql);
                $data[$key]['warehousing_num'] = $cp_data[0]['cp_sl'];
                $data[$key]['warehousing_date'] = substr($cp_data[0]['jjcp_sj'],0,10);
                //查出进入超节损的工序，有上报产量的工序就进入超节损
                $gxh_sql = "SELECT sczl_gxh FROM 
                        (SELECT sczl_gxh FROM 设备_产量计酬 WHERE sczl_gdbh = '{$value['Gd_gdbh']}' 
                        UNION SELECT sczl_gxh FROM db_sczl WHERE sczl_gdbh = '{$value['Gd_gdbh']}') AS combined_result";
                $gxh_arr = Db::query($gxh_sql);
                $gxh_values = array_column($gxh_arr, 'sczl_gxh');
                $gy_data =db('工单_工艺资料')->where('Gy0_gdbh',$value['Gd_gdbh'])->where('Gy0_gxh','in',$gxh_values)->field('Gy0_计划接货数,Gy0_计划损耗')->select();
                $arr = [];
                $plan_loss = [];//工单计划损耗
                foreach ($gy_data as $k=>$v){
                    $rate =  round($v['Gy0_计划损耗'] / $v['Gy0_计划接货数'],5);
                    $arr[$k] = floor($rate * 10000) /10000;
                    $plan_loss[$k] = $v['Gy0_计划损耗'];
                }
                $target_rate =  (1-array_sum($arr))*100;
                $data[$key]['target_rate'] =$target_rate.'%'; //目标合格率
                $real_rate = $cp_data[0]['cp_sl'] / ($value['实际投料'] * 10000) *100;
                $data[$key]['real_rate'] = number_format($real_rate,2) . '%';//实际合格率
                //奖惩系数及金额
                $data[$key]['reward_rate'] = '1';
                $data[$key]['reward_money'] = '';
                //制程废品
                $zzfp_data =db('设备_产量计酬')->where('sczl_gdbh',$value['Gd_gdbh'])->field('SUM(sczl_zcfp) as sczl_zcfp')->select();
                $data[$key]['zcfp'] = $zzfp_data[0]['sczl_zcfp'] * $value['yj_ls'];//制程废品
                //废品合计
                $wasteTotal = db('db_qczl')->where('qczl_gdbh',$value['Gd_gdbh'])->sum('qczl_fp');
                if (empty($wasteTotal)){
                    $wasteTotal = 0;
                }
                $data[$key]['废品合计'] = $wasteTotal + $data[$key]['zcfp'];//废品合计
                $data[$key]['工单无形损'] = (int)($value['实际投料'] *10000) - $cp_data[0]['cp_sl'] - $data[$key]['废品合计'];//工单无形损
                $out_sql = "SELECT fp_sl1,fp_sl2,fp_sl3,fp_sl4,fp_sl5,fp_sl6,fp_sl7,fp_sl8,fp_sl9,fp_sl10,fp_sl11,fp_sl12,fp_sl13,
                        fp_lb1,fp_lb2,fp_lb3,fp_lb4,fp_lb5,fp_lb6,fp_lb7,fp_lb8,fp_lb9,fp_lb10,fp_lb11,fp_lb12,fp_lb13, 
                        fp_bh1,fp_bh2,fp_bh3,fp_bh4,fp_bh5,fp_bh6,fp_bh7,fp_bh8,fp_bh9,fp_bh10,fp_bh11,fp_bh12,fp_bh13,
                        fp_gxmc1,fp_gxmc2,fp_gxmc3,fp_gxmc4,fp_gxmc5,fp_gxmc6,fp_gxmc7,fp_gxmc8,fp_gxmc9,fp_gxmc10,fp_gxmc11,fp_gxmc12,fp_gxmc13 
                        FROM db_qczl WHERE qczl_gdbh = '{$value['Gd_gdbh']}'";
                $waste_out = Db::query($out_sql);
                $list = []; //外发废数组
                $quality = [];//质检废数组
                $material = [];//材料废数组
                $change = [];//零头处理数组
                $j = 0;
                $m = 0;
                $n = 0;
                $l = 0;
                foreach ($waste_out as $entry) {
                    for ($i = 1; $i <= 13; $i++) {
                        $labelKey = "fp_lb" . $i;
                        $bhKey = "fp_bh" . $i;
                        $gxmcKey = "fp_gxmc" . $i;
                        $slKey = "fp_sl" . $i;
                        if (!empty($entry[$labelKey])) {
                            if (substr($entry[$gxmcKey],0,2) == '99'){
                                $list[$j]= $entry[$slKey];
                                $j++;
                            }
                            if (substr($entry[$labelKey],0,1) == 'L' ){
                                $material[$n]= $entry[$slKey];
                                $n++;
                            }
                            if (substr($entry[$labelKey],0,3) == 'M04' ){
                                $change[$l]= $entry[$slKey];
                                $l++;
                            }
                        }
                        if (!empty($entry[$bhKey])) {
                            if ($entry[$bhKey] != '000000' && $entry[$slKey] > 0){
                                $quality[$m]= $entry[$slKey];
                                $m++;
                            }
                        }
                    }
                }
                $data[$key]['材料废'] = array_sum($material); //材料废
                $data[$key]['零头处理'] = array_sum($change); //零头处理
                $data[$key]['外发废'] = array_sum($list);//外发废
                $data[$key]['分摊废'] = '';//分摊废
                $data[$key]['工单计划损耗'] = array_sum($plan_loss);//工单计划损耗
                $data[$key]['工单质检废'] =  array_sum($quality);  //质检废
            }
            $res = [
                'data'=>$data,
                'total' => $total
            ];
//            Cache::set('OrderSuperLoss/getList'.$params['date'],$res,3600);
        }else{
//            $res = Cache::get('OrderSuperLoss/getList'.$params['date']);
        }

        $this->success('请求成功',$res);
    }
    /**
     * 获取工单超节损工艺
     * @ApiMethod GET
     * @params string order
    */
    public function getOrderSuperLossGy(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->param();
        if (!isset($params['order']) || empty($params['order'])) {
            $this->error('参数错误');
        }
        $sql = "SELECT a.Gy0_gdbh,a.Gy0_yjno,a.Gy0_gxh,rtrim(a.Gy0_gxmc) as Gy0_gxmc,rtrim(a.Add_gxmc) as Add_gxmc,a.Gy0_ls,a.Gy0_Rate0,a.Gy0_Rate1,a.损耗系数,a.Gy0_ms,
                a.Gy0_计划接货数,a.Gy0_计划损耗,sum(b.sczl_cl) as total_cl,sum(b.sczl_zcfp) as total_fp,sum(c.sczl_cl) as cl,SUM(c.sczl_fp) as fp FROM `工单_工艺资料` a
                LEFT JOIN `设备_产量计酬` b ON a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_gxh = b.sczl_gxh
                LEFT JOIN `db_sczl` c ON a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_gxh = c.sczl_gxh
                WHERE a.Gy0_gdbh = '{$params['order']}' GROUP BY a.Gy0_gxh";
        $data = Db::query($sql);
        foreach ($data as $key=>$value){
            if (!empty($value['total_cl'])){
                $data[$key]['total_cl'] = $value['total_cl'] *$value['Gy0_ls'];
            }else{
                $data[$key]['total_cl'] =$value['cl'] *$value['Gy0_ls'];
            }
            if (!empty($value['total_fp'])){
                $data[$key]['total_fp'] = $value['total_fp'] *$value['Gy0_ls'];
            }else{
                $data[$key]['total_fp'] = $value['fp'] *$value['Gy0_ls'];
            }
            unset($data[$key]['cl']);
            unset($data[$key]['fp']);
        }
        $this->success('请求成功',$data);
    }
    /**
     * 工单超节损统计
     * @ApiMethod GET
     * @params string order
    */
    public function getOrderSuperLossCount(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->param();
        if (!isset($params['order']) || empty($params['order'])) {
            $this->error('参数错误');
        }
        $order = $params['order'];
        $field = 'Gd_gdbh,rtrim(成品代号) as 成品代号,rtrim(成品名称) as 成品名称,rtrim(销售订单号) as 销售订单号,订单数量,实际投料';
        $data =db('工单_基本资料')->where('Gd_Gdbh',$order)->where('行号',1)->field($field)->find();
        //查出成品数量及日期
        $cp_sql = "SELECT SUM(jjcp_sl) as cp_sl,MAX(jjcp_sj) as jjcp_sj FROM `成品入仓` WHERE jjcp_gdbh = '{$order}' GROUP BY jjcp_gdbh";
        $cp_data = Db::query($cp_sql);
        $data['warehousing_num'] = $cp_data[0]['cp_sl'];
        $data['warehousing_date'] = substr($cp_data[0]['jjcp_sj'],0,10);
        //查出进入超节损的工序，有上报产量的工序就进入超节损
        $gxh_sql = "SELECT sczl_gxh FROM 
                        (SELECT sczl_gxh FROM 设备_产量计酬 WHERE sczl_gdbh = '{$order}' 
                        UNION SELECT sczl_gxh FROM db_sczl WHERE sczl_gdbh = '{$order}') AS combined_result";
        $gxh_arr = Db::query($gxh_sql);
        $gxh_values = array_column($gxh_arr, 'sczl_gxh');
        $gy_data =db('工单_工艺资料')
            ->alias('a')
            ->field([
                'a.Gy0_yjno', 'a.Gy0_gxh', 'RTRIM(a.Gy0_gxmc) as Gy0_gxmc','RTRIM(a.Add_gxmc) as Add_gxmc', 'a.Gy0_ls', 'a.Gy0_计划接货数',
                'a.Gy0_计划损耗', 'a.超节损承担比例','SUM(b.sczl_zcfp) as total_fp','SUM(b.sczl_cl) as total_cl','SUM(c.sczl_cl) as cl','SUM(c.sczl_fp) as fp',
            ])
            ->join('设备_产量计酬 b', 'a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_gxh = b.sczl_gxh','left')
            ->join('db_sczl c', 'a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_gxh = c.sczl_gxh','left')
            ->where([
                'a.Gy0_gdbh' => $order,
                'a.Gy0_gxh' => ['in', $gxh_values]
            ])
            ->group('a.Gy0_gxh')
            ->select();
        $arr = [];
        $plan_loss = [];//工单计划损耗
        foreach ($gy_data as $k=>$v){
            $rate =  round($v['Gy0_计划损耗'] / $v['Gy0_计划接货数'],5);
            $arr[$k] = floor($rate * 10000) /10000;
            $plan_loss[$k] = $v['Gy0_计划损耗'];
        }
        $target_rate =  (1-array_sum($arr))*100;
        $data['target_rate'] =$target_rate.'%'; //目标合格率
        $real_rate = $cp_data[0]['cp_sl'] / ($data['实际投料'] * 10000) *100;
        $data['real_rate'] = number_format($real_rate,2) . '%';//实际合格率
        //制程废品
        $zzfp_data =db('设备_产量计酬')->where('sczl_gdbh',$order)->field('SUM(sczl_zcfp) as sczl_zcfp')->select();
        //联数
        $ls =db('工单_印件资料')->where('Yj_Gdbh',$order)->value('yj_ls');
        //制程废品*ls
        $data['zcfp'] = $zzfp_data[0]['sczl_zcfp'] * $ls;
        //废品数量
        $wasteTotal = db('db_qczl')->where('qczl_gdbh',$order)->sum('qczl_fp');
        if (empty($wasteTotal)){
            $wasteTotal = 0;
        }
        //废品合计
        $waste_total = $data['zcfp'] + $wasteTotal;
        //工单无形损
        $data['intangible_loss'] = $data['实际投料'] *10000 - $cp_data[0]['cp_sl'] - $waste_total;
        $out_sql = "SELECT fp_sl1,fp_sl2,fp_sl3,fp_sl4,fp_sl5,fp_sl6,fp_sl7,fp_sl8,fp_sl9,fp_sl10,fp_sl11,fp_sl12,fp_sl13,
                        fp_lb1,fp_lb2,fp_lb3,fp_lb4,fp_lb5,fp_lb6,fp_lb7,fp_lb8,fp_lb9,fp_lb10,fp_lb11,fp_lb12,fp_lb13, 
                        fp_bh1,fp_bh2,fp_bh3,fp_bh4,fp_bh5,fp_bh6,fp_bh7,fp_bh8,fp_bh9,fp_bh10,fp_bh11,fp_bh12,fp_bh13,
                        fp_gxmc1,fp_gxmc2,fp_gxmc3,fp_gxmc4,fp_gxmc5,fp_gxmc6,fp_gxmc7,fp_gxmc8,fp_gxmc9,fp_gxmc10,fp_gxmc11,fp_gxmc12,fp_gxmc13
                        FROM db_qczl WHERE qczl_gdbh = '{$order}'";
        $waste_out = Db::query($out_sql);
        $list = []; //外发废数组
        $quality = [];//质检废数组
        $material = [];//材料废数组
        $change = [];//零头处理数组
        $j = 0;
        $m = 0;
        $n = 0;
        $l = 0;
        foreach ($waste_out as $entry) {
            for ($i = 1; $i <= 13; $i++) {
                $labelKey = "fp_lb" . $i;
                $bhKey = "fp_bh" . $i;
                $gxmcKey = "fp_gxmc" . $i;
                $slKey = "fp_sl" . $i;
                if (!empty($entry[$labelKey])) {
                    if (substr($entry[$gxmcKey],0,2) == '99'){
                        $list[$j]= $entry[$slKey];
                        $j++;
                    }
                    if (substr($entry[$labelKey],0,1) == 'L' ){
                        $material[$n]= $entry[$slKey];
                        $n++;
                    }
                    if (substr($entry[$labelKey],0,3) == 'M04' ){
                        $change[$l]= $entry[$slKey];
                        $l++;
                    }
                }
                if (!empty($entry[$bhKey])) {
                    if ($entry[$bhKey] != '000000' && $entry[$slKey] > 0){
                        $quality[$m]= $entry[$slKey];
                        $m++;
                    }
                }

            }
        }
        $data['材料废'] = array_sum($material); //材料废
        $data['零头处理'] = array_sum($change); //零头处理
        $data['外发废'] = array_sum($list);//外发废
        $data['工单质检废'] =  array_sum($quality);//质检废
//        $data['分摊废'] = '';//分摊废
        $plan_total =db('工单_工艺资料')->where(['Gy0_gdbh' => $order, 'Gy0_gxh' => ['in', $gxh_values]])->value('SUM(Gy0_计划损耗)');
        //单据列表最后统计
        $total = [];
        $total['plan_loss'] = 0;
        $total['total_fp'] = 0;
        $total['waste_quality'] = 0;
        $total['waste_intangible'] = 0;
        //按工序打印
        if ($params['type'] == 1){
            $total['total_waste'] = 0;
            $total['loss'] = 0;
            foreach ($gy_data as $k=>$item){
                $gy_data[$k]['waste_quality'] = 0;
                $gy_data[$k]['total_fp'] = $item['total_fp'] * $item['Gy0_ls'];
                $gy_data[$k]['total_cl'] = $item['total_cl'] * $item['Gy0_ls'];
                $gy_data[$k]['intangible_loss'] = round($item['Gy0_计划损耗'] / $plan_total * $data['intangible_loss']);
                foreach ($waste_out as $entry) {
                    for ($i = 1; $i <= 13; $i++) {
                        $gxmcKey = "fp_gxmc" . $i;
                        $slKey = "fp_sl" . $i;
                        if ((int)substr($entry[$gxmcKey],0,2) == $item['Gy0_gxh']){
                            $gy_data[$k]['waste_quality'] += $entry[$slKey];

                        }
                    }
                }
                $gy_data[$k]['total_waste'] = $gy_data[$k]['waste_quality'] + $gy_data[$k]['intangible_loss'] +  $gy_data[$k]['total_fp'];
                $gy_data[$k]['loss'] = $item['Gy0_计划损耗'] - $gy_data[$k]['total_waste'];
                $gy_data[$k]['loss_rate'] = number_format($gy_data[$k]['loss'] / $item['Gy0_计划接货数'],4) * 100 .'%';
                $gy_data[$k]['target_loss_rate'] = number_format($item['Gy0_计划损耗'] / $item['Gy0_计划接货数'],4) * 100 .'%';
                $gy_data[$k]['actual_loss_rate'] = number_format($gy_data[$k]['total_waste'] / $item['Gy0_计划接货数'],4) * 100 .'%';
                $gy_data[$k]['超节损承担比例'] = number_format($item['超节损承担比例'],4) * 100 .'%';

                $total['plan_loss'] += $gy_data[$k]['Gy0_计划损耗'];
                $total['total_fp'] += $gy_data[$k]['total_fp'];
                $total['waste_quality'] += $gy_data[$k]['waste_quality'];
                $total['waste_intangible'] += $gy_data[$k]['intangible_loss'];
                $total['total_waste'] += $gy_data[$k]['total_waste'];
                $total['loss'] += $gy_data[$k]['loss'];
            }
            $data['gy_data'] = $gy_data;
            $data['total'] = $total;
            $this->success('请求成功',$data);
        }else{ //按班组打印
            //总产量数据
            $total_cl_data = \db('工单_工艺资料')->alias('a')
                ->field(['a.Gy0_yjno', 'a.Gy0_gxh','a.Gy0_ls', 'SUM(b.sczl_cl) as total_cl', 'SUM(c.sczl_cl) as cl'])
                ->join('设备_产量计酬 b', 'a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_gxh = b.sczl_gxh','left')
                ->join('db_sczl c', 'a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_gxh = c.sczl_gxh','left')
                ->where([
                    'a.Gy0_gdbh' => $order,
                    'a.Gy0_gxh' => ['IN', $gxh_values]
                ])
                ->group('b.sczl_gxh')
                ->order('a.Gy0_gxh')
                ->select();
            //按班组分的产量数据
            $gy_data =db('工单_工艺资料')
                ->alias('a')
                ->field([
                    'a.Gy0_yjno', 'a.Gy0_gxh', 'a.Gy0_ls', 'a.Gy0_计划损耗', 'a.超节损承担比例', 'b.sczl_gxmc',
                    'SUM(b.sczl_zcfp) as sczl_zcfp', 'SUM(b.sczl_cl) as total_cl',
                    'b.sczl_bh1', 'b.sczl_bh2', 'b.sczl_bh3', 'b.sczl_bh4', 'b.sczl_bh5', 'b.sczl_bh6',
                    'b.sczl_bh7', 'b.sczl_bh8', 'b.sczl_bh9', 'b.sczl_bh10',
                    'rtrim(c.sczl_gxmc) as gxmc', 'SUM(c.sczl_cl) as cl', 'SUM(c.sczl_fp) as fp', 'c.sczl_bh1 as sczl_bh'
                ])
                ->join('设备_产量计酬 b', 'a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_gxh = b.sczl_gxh','left')
                ->join('db_sczl c', 'a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_gxh = c.sczl_gxh','left')
                ->where([
                    'a.Gy0_gdbh' => $order,
                    'a.Gy0_gxh' => ['in', $gxh_values]
                ])
                ->group('b.sczl_gxh, b.sczl_bh1, b.sczl_bh2, b.sczl_bh3, b.sczl_bh4, c.sczl_bh')
                ->order('a.Gy0_gxh, b.UniqId DESC, c.UniqId DESC')
                ->select();
            foreach ($gy_data as $key=>$value){
                $gy_data[$key]['Gy0_yjno'] = $value['Gy0_yjno'] > 10 ? $value['Gy0_yjno'] : '0'.$value['Gy0_yjno'];
                //每道工序的产量（按班组分）
                $gy_data[$key]['total_cl'] = $value['total_cl'] * $value['Gy0_ls'];
                if (empty($value['total_cl'])){
                    $gy_data[$key]['total_cl'] = $value['cl'] * $value['Gy0_ls'];
                }
                //计算每道工序的计划损耗
                foreach ($total_cl_data as $k=>$v){
                    //每道工序的总产量
                    $cl = $v['total_cl'] * $v['Gy0_ls'];
                    if (empty($cl)){
                        $cl = $v['cl'] * $v['Gy0_ls'];
                    }
                    if ($value['Gy0_yjno'] == $v['Gy0_yjno'] && $value['Gy0_gxh'] == $v['Gy0_gxh']){
                        $gy_data[$key]['计划损耗'] = round($gy_data[$key]['total_cl'] / $cl  * $value['Gy0_计划损耗']);
                    }
                }
                //每道工序的制程废品（按班组分）
                $gy_data[$key]['sczl_zcfp'] = $value['sczl_zcfp'] * $value['Gy0_ls'];
                if (empty($value['sczl_zcfp'])){
                    $gy_data[$key]['sczl_zcfp'] = $value['fp'] * $value['Gy0_ls'];
                }
                //计算每道工序的质检废(按班组)
                $gy_data[$key]['sczl_gxmc'] = $value['sczl_gxmc'];
                if (empty($value['sczl_gxmc'])){
                    $gy_data[$key]['sczl_gxmc'] = $value['gxmc'];
                }
                if (empty($value['sczl_bh1']) || is_null($value['sczl_bh1'])){
                    $value['sczl_bh1'] = $value['sczl_bh'];
                }
                $gy_data[$key]['sczl_bh1'] = $value['sczl_bh1'];
                $gy_data[$key]['waste_quality'] = 0;
                foreach ($waste_out as $entry) {
                    for ($i = 1; $i <= 13; $i++) {
                        $gxmcKey = "fp_gxmc" . $i;
                        $slKey = "fp_sl" . $i;
                        $bhKey = "fp_bh" . $i;
                        if ((int)substr($entry[$gxmcKey],0,2) == $value['Gy0_gxh'] && $entry[$bhKey] == $value['sczl_bh1']){
                            $gy_data[$key]['waste_quality'] += $entry[$slKey];

                        }
                    }
                }
                //无形损分摊
                $gy_data[$key]['intangible_loss'] = round($gy_data[$key]['计划损耗'] / $plan_total * $data['intangible_loss']);
                //节损数量
                $total_waste = $gy_data[$key]['waste_quality'] + $gy_data[$key]['intangible_loss'] +  $gy_data[$key]['sczl_zcfp'];
                $gy_data[$key]['loss'] = $gy_data[$key]['计划损耗'] - $total_waste;
                $gy_data[$key]['超节损承担比例'] = number_format($value['超节损承担比例'],4) * 100 .'%';
                //班组成员
                $nameArr = [];
                for ($j = 1; $j <= 10; $j++) {
                    $bhKey = "sczl_bh" . $j;
                    if (!empty($value[$bhKey])){
                        $name = \db('人事_基本资料')->where('员工编号',$value[$bhKey])->value('rtrim(员工姓名)');
                        array_push($nameArr,$name);
                    }
                }

                $gy_data[$key]['member'] = implode(' ',$nameArr);
                unset($gy_data[$key]['Gy0_计划损耗']);
                unset($gy_data[$key]['gxmc']);
                unset($gy_data[$key]['cl']);
                unset($gy_data[$key]['fp']);
                unset($gy_data[$key]['sczl_bh']);
                $total['plan_loss'] += $gy_data[$key]['计划损耗'];
                $total['total_fp'] += $gy_data[$key]['sczl_zcfp'];
                $total['waste_quality'] += $gy_data[$key]['waste_quality'];
                $total['waste_intangible'] += $gy_data[$key]['intangible_loss'];
            }
            $data['gy_data'] = $gy_data;
            $data['total'] = $total;
            $this->success('请求成功',$data);
        }
    }
    /**
     * 获取工单工艺
     * @ApiMethod GET
     * @params string order
    */
    public function getOrderGy(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->param();
        if (!isset($params['order']) || empty($params['order'])) {
            $this->error('参数错误');
        }
        $order = $params['order'];
        $field = 'Gy0_yjno,Gy0_gxh,rtrim(Gy0_gxmc) as Gy0_gxmc,rtrim(Add_gxmc) as Add_gxmc,Gy0_rate0,Gy0_rate1,损耗系数,无形损承担比例 as loss_one,超节损承担比例 as loss_two,超节损核算单价 as loss_thr,UniqId';
        $data =db('工单_工艺资料')->where('Gy0_gdbh',$order)->field($field)->select();
        $this->success('请求成功',$data);
    }
    /**
     * 更新工单工艺
     * @ApiMethod POST
     * @params array data
    */
    public function updateOrderGy(){
        if (Request::instance()->isPost() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->post();
        if (!isset($params) || !isset($params[0]['UniqId'])){
            $this->error('参数不能为空');
        }
        $i = 0;
        foreach ($params as $key=>$value){
            $data = [];
            $data['无形损承担比例'] = $value['loss_one'];
            $data['超节损承担比例'] = $value['loss_two'];
            $data['超节损核算单价'] = $value['loss_thr'];
            $sql =db('工单_工艺资料')->where('UniqId',$value['UniqId'])->fetchSql(true)->update($data);
            $res = Db::query($sql);
            if ($res !== false){
                $i++;
            }
        }
        if ($i !== 0){
            $this->success('更新成功');
        }else{
            $this->error('更新失败');
        }
    }
    /**
     * 获取工单印件考核资料
     * @ApiMethod GET
     * @params string order
    */
    public function getOrderYj(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->request();
        if (!isset($params['order']) || empty($params['order'])) {
            $this->error('参数错误');
        }
        $data =db('工单_印件资料')->where('Yj_gdbh',$params['order'])->field('Yj_gdbh,rtrim(yj_Yjdh) as yj_Yjdh,yj_Yjno,rtrim(yj_yjmc) as yj_yjmc,质量考核')->find();
        $this->success('请求成功',$data);
    }
    /**
     * 更新工单印件考核资料
     * @ApiMethod POST
     * @params array data
    */
    public function updateOrderYj(){
        if (Request::instance()->isPost() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->request();
        if (!isset($params['order'])){
            $this->error('参数不能为空');
        }
        if (!isset($params['yj_yjno'])){
            $this->error('参数不能为空');
        }
        if (!isset($params['examine'])){
            $this->error('参数不能为空');
        }
        $where['Yj_gdbh'] = $params['order'];
        $where['yj_Yjno'] = $params['yj_yjno'];
        $res =db('工单_印件资料')->where($where)->setField('质量考核',$params['examine']);
        if ($res !== false){
            $this->success('更新成功');
        }else{
            $this->error('更新失败');
        }
    }
    /**
     * 获取修正工单实际投料列表
     * @ApiMethod GET
     * @params string year
     * @params string month
    */
    public function getOrderFeedList(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->request();
        if (empty($params['year']) || empty($params['month'])) {
            $this->error('参数错误');
        }
        $search = $params['year'].'.'.$params['month'];
        $field = "a.Gd_gdbh,rtrim(c.yj_yjdh) as yj_yjdh,c.yj_Yjno, c.yj_ks,c.yj_ls,rtrim(c.yj_zzdh) as yj_zzdh,
                  rtrim(b.BOM_物料名称) as BOM_物料名称,rtrim(c.yj_tlgg) as yj_tlgg,rtrim(b.BOM_投料单位) as BOM_投料单位,a.订单数量,c.yj_平张投料, b.BOM_实际用量,a.实际投料,a.投料确认,a.UniqId";
        $data =db('工单_基本资料')->alias('a')
            ->join('工单_印件资料 c','a.Gd_gdbh = c.Yj_Gdbh','left')
            ->join('工单_bom资料 b','c.Yj_Gdbh = b.BOM_工单编号 AND c.yj_zzdh = b.BOM_物料编码','left')
            ->where('a.投料确认','like','%'.$search.'%')
            ->field($field)
            ->group('a.Gd_gdbh')
            ->order('b.BOM_投料单位,a.UniqId asc')
            ->select();
        if (empty($data)){
            $this->success('请求成功');
        }
        foreach ($data as $key=>$value){
            $len = stripos($value['yj_tlgg'],'/');
            if ($len){
                $name = substr($value['yj_tlgg'],0,$len);
                $len_two = stripos($value['yj_tlgg'],'（');
                if ($len_two){
                    $name = substr($name,0,$len_two);
                }
                $data[$key]['yj_tlgg'] = $name;
            }
            $data[$key]['rate'] = '';
        }
        $this->success('请求成功',$data);
    }
    /**
     * 更新工单实际投料
     * @ApiMethod POST
     * @params int UniqId
     * @params string number
    */
    public function updateOrderFeed(){
        if (Request::instance()->isPost() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->post();
        if (!isset($params[0]['UniqId']) || empty($params[0]['UniqId'])){
            $this->error('参数错误');
        }
        $i = 0;
        foreach ($params as $key=>$value){
            if (!empty($value['number'])){
                $info = db('工单_基本资料')->where('UniqId',$value['UniqId'])->value('Gd_gdbh');
                $res_sql = db('工单_基本资料')->where('UniqId',$value['UniqId'])->fetchSql(true)->setField('实际投料',$value['number']);
                $res = Db::query($res_sql);
                $data['yj_实际投料'] = $value['number'];
                $yj_sql =db('工单_印件资料')->where('Yj_Gdbh',$info)->fetchSql(true)->update($data);
                $yjRes = Db::query($yj_sql);
                if (!$res && !$yjRes){
                    $i++;
                }
            }
        }
        if ($i != 0){
            $this->success('更新成功');
        }else{
            $this->error('更新失败');
        }
    }
    /**
     * 工单工序产量统计
     * @ApiMethod GET
     * @params string order
    */
    public function getOrderProcessCount(){
        if (Request::instance()->isGet() == false) {
            $this->error('非法请求');
        }
        $params = Request::instance()->request();
        if (empty($params['order']) || empty($params['order'])) {
            $this->error('参数错误');
        }
        $order = $params['order'];
        $sql = "SELECT a.Gy0_yjno,a.Gy0_gxh,rtrim(a.Gy0_gxmc) as Gy0_gxmc,rtrim(a.Add_gxmc) as Add_gxmc,a.Gy0_计划接货数, a.Gy0_ls,a.Gy0_sbbh,a.PD_WG,SUM(b.sczl_cl) as sczl_cl,
                SUM(b.sczl_zcfp) as sczl_zcfp,SUM(b.sczl_来料少数) as 来料异常,COUNT(DISTINCT b.sczl_num) as process_num,b.sczl_Pgcl,SUM(c.sczl_cl) as cl,b.sczl_rq
                FROM `工单_工艺资料` a
                LEFT JOIN `设备_产量计酬` b ON a.Gy0_gdbh = b.sczl_gdbh AND a.Gy0_gxh = b.sczl_gxh
                LEFT JOIN db_sczl c ON a.Gy0_gdbh = c.sczl_gdbh AND a.Gy0_gxh = c.sczl_gxh
                WHERE a.Gy0_gdbh = '{$order}' GROUP BY a.Gy0_gxh";
        $data = Db::query($sql);
        //手检数据
        $handData =db('db_手工检验')->where('sczl_gdbh',$order)->field('sum(sczl_cl) as cl,rtrim(sczl_yjgx) as sczl_gxh')->select();
        //包装及成品防护数据
        $sql = "SELECT sczl_gdbh1,sczl_gdbh2,sczl_gdbh3,sczl_gdbh4,sczl_gdbh5,sczl_gdbh6,rtrim(sczl_gxmc1) as sczl_gxmc1,rtrim(sczl_gxmc2) as sczl_gxmc2,
                rtrim(sczl_gxmc3) as sczl_gxmc3,rtrim(sczl_gxmc4) as sczl_gxmc4,rtrim(sczl_gxmc5) as sczl_gxmc5,rtrim(sczl_gxmc6) as sczl_gxmc6,
                sczl_cl1,sczl_cl2,sczl_cl3,sczl_cl4,sczl_cl5,sczl_cl6,sczl_PgCl1,sczl_PgCl2,sczl_PgCl3,sczl_PgCl4,sczl_PgCl5,sczl_PgCl6
                FROM `mesdb`.`db_包装计件` WHERE `sczl_gdbh1` LIKE '%{$order}%' OR `sczl_gdbh2` LIKE '%{$order}%' 
                OR `sczl_gdbh3` LIKE '%{$order}%' OR `sczl_gdbh4` LIKE '%{$order}%' OR `sczl_gdbh5` LIKE '%{$order}%' OR `sczl_gdbh6` LIKE '%{$order}%'";
        $package = Db::query($sql);
        $list = [];
        $j = 0;
        foreach ($package as $value){
            for ($i = 1; $i <= 6; $i++) {
                $gdbhlKey = "sczl_gdbh" . $i;
                $gxmcKey = "sczl_gxmc" . $i;
                $clKey = "sczl_cl" . $i;
                $pgclKey = "sczl_PgCl" . $i;
                if ($value[$gdbhlKey] == '2311743' && $value[$clKey] > 0) {
                    $list[$j]['sczl_gdbh'] = $value[$gdbhlKey];
                    $list[$j]['sczl_gxmc'] =  $value[$gxmcKey];
                    $list[$j]['sczl_cl'] =  $value[$clKey];
                    $list[$j]['sczl_PgCl'] =  $value[$pgclKey];
                    $j++;
                }
            }
        }
        // 创建一个关联数组，用于存储相同 sczl_gxmc 的值和对应的乘积之和
        $sumArray = [];
        foreach ($list as $item) {
            $key = $item['sczl_gxmc'];
            $cl = floatval($item['sczl_cl']);
            $pgCl = intval($item['sczl_PgCl']);
            $product = $cl * $pgCl;
            if (!isset($sumArray[$key])) {
                $sumArray[$key] = 0;
            }
            $sumArray[$key] += $product;
        }
        $handGxh = substr($handData[0]['sczl_gxh'],-2);
        foreach ($data as $key=>$item){
            $data[$key]['折算车头产量'] = round($item['Gy0_计划接货数']/$item['Gy0_ls']);
            $data[$key]['制程废品率'] = '';
            if ($item['sczl_zcfp'] > 0){
                $data[$key]['制程废品率'] = number_format($item['sczl_zcfp']/$item['sczl_cl']*100,4).'%';
            }
            if ($item['cl'] > 0){
                $data[$key]['sczl_cl'] = $item['cl'];
            }
            if ($item['sczl_Pgcl'] > 0){
                $data[$key]['sczl_cl'] = $item['sczl_cl'] * $item['sczl_Pgcl'];
            }
            if ($item['Gy0_gxh'] == (int)$handGxh){
                $data[$key]['sczl_cl'] = $handData[0]['cl'];
            }
            foreach ($sumArray as $k=>$v){
                if ($item['Gy0_gxmc'] == $k){
                    $data[$key]['sczl_cl'] = $v;
                }
            }
            if (substr($item['PD_WG'],0,4) == '1900'){
                $data[$key]['PD_WG'] = '计划';
            }else{
                $data[$key]['PD_WG'] = '完工';
            }
        }
        $this->success('请求成功',$data);
    }
}