<?php

namespace model;

use \model;

class projectRecommend extends model
{
    public $table = '';
    public $porscheId = '';
    private $userNum = 0;
    private $joinWhere = [];

    public function __construct()
    {
        parent::__construct();
        // 获取保时捷品牌的id
        $this->porscheId = $this->db->select('tag_brand', [], 'tb_id', ['tb_en_name' => 'porsche']);
        // 获取项目成员总人数
        $res= $this->db->select('user', [], ['userNum' => raw("COUNT(u_id)")],['RAW #find_in_set' => raw("FIND_IN_SET('1', u_project_ids)")]);
        $this->userNum = $res[0]['userNum'];
    }

    /**
     * @param  array $params
     * @param  array  $rules
     * @method 项目推荐单列表获取
     * @author hongzhang
     * @copyright 2025-08-04
     */
    public function getList($params, $rules = [])
    {
        $filters = $params['filters'];
        $page = $params['page'];
        $pagesize = $params['rows'];
        $sidx = empty($params['sidx']) ? 'oe_id' : $params['sidx'];
        $sord = empty($params['sidx']) ? 'desc' : $params['sord'];
        $orderby = $params['orderby'];
        $this->joinWhere = [];
        $refiled = [
            'cat_name' => 'cat.' . cateName(),
            'pcat_name' => 'pcat.' . cateName(),
            'oe_oe' => 'oe_oe_format',
        ];
        $this->set('refiled', $refiled);
        $sidx =  strtr($sidx, $refiled);

        $table = 'oe_item(oi)';
        $join = [
            "[><]pro_factory(pf)" => ["oi.oe_pf_id" => "pf_id"],
            "[><]pro_cate(cat)" => ["oi.oe_cate_id" => "id"],
            "[><]pro_cate(pcat)" => ["cat.parent_id" => "id"],
            "[><]oe_numidx(oen)" => ["oi.oe_id" => "on_oe_id"],
            // 只筛选有保时捷参考号的零件
            "[><]oe_numidx_tag_brand(ontb)" => ["oi.oe_id" => "ontb_oe_id", "ontb.ontb_tb_id" => "@" . "ontb.ontb_tb_id = " . $this->porscheId[0]],
            // 项目推荐单关联
            "[>]project_recommend(prr)" => ["oi.oe_id" => "prr_oe_id"],
            // 操作人信息
            "[>]adm_user(au)" => ["prr.prr_opuser" => "au_id"],
            // 保时捷参考号显示
            "[>]oe_numidx(on2)" => ["ontb.ontb_on_id" => "on_id"],
        ];

        $fields = ['prr.prr_status', 'pcat.' . cateName() . '(pcat_name)', 'cat.' . cateName() . '(cat_name)', 'pf.pf_name', 'oi.oe_oe(oe_oe)', 'oi.oe_id(oe_id)', 'oi.oe_' . cateName() . '(oe_name)','au_name','prr_create_time','prr_update_time','prr_id'];

        $where = $this->filter2medoo($filters, function (&$field, &$op, &$data) {
            $field = strtr($field, $this->get('refiled'));
            if ($field == 'prr_status') {
                $op = 'RAW_' . $field;
                if ($data == 1) {
                    $data = raw("prr_status = 1");
                } else if ($data == -1) {
                    $data = raw("prr_status = -1");
                }
            } else if ($field == 'oe_oe_format') {
                $data = formatNum($data);
            } else if ($field == 'chk_porscheRefs') {
                // 需要给查找参数精确命名
                $field = 'on2.on_number';
                $data = formatNum($data);
            } else if ($field == 'chk_pcs') {
                $op = 'RAW_' . $field;
                if ($data == '-1') {
                    $data = raw("NOT EXISTS (
                        SELECT 1 FROM oe_numidx on1
                        JOIN pro_numidx pn ON on1.on_pf_id=pn.pn_pf_id AND on1.on_formart=pn.pn_formart
                        JOIN pro p ON pn.pn_p_id=p.p_id
                        WHERE on1.on_oe_id = oe_id AND oe_cate_id = p.p_cate AND p.p_pb_id = 1
                    )");
                } else {
                    $data = raw("EXISTS (
                        SELECT 1 FROM oe_numidx on1
                        JOIN pro_numidx pn ON on1.on_pf_id=pn.pn_pf_id AND on1.on_formart=pn.pn_formart
                        JOIN pro p ON pn.pn_p_id=p.p_id
                        WHERE on1.on_oe_id = oe_id AND oe_cate_id = p.p_cate AND p.p_pb_id = 1
                    )");
                }
            } else if ($field == 'chk_sups') {
                $op = 'RAW_' . $field;
                if ($data == '-1') {
                    $data = raw(" NOT EXISTS (
                        SELECT 1 FROM oe_numidx
                        JOIN supplier_pro ON on_pf_id=spr_pf_id AND on_formart=spr_format 
                        WHERE on_oe_id = oe_id AND oe_cate_id= spr_cat_id
					)");
                } else {
                    $data = raw(" EXISTS (
                        SELECT 1 FROM oe_numidx
                        JOIN supplier_pro ON on_pf_id=spr_pf_id AND on_formart=spr_format 
                        WHERE on_oe_id = oe_id AND oe_cate_id= spr_cat_id
					)");
                }
            } else if ($field == 'pic') {
                $op = 'RAW_' . $field;
                if ($data == 1) {
                    $data = raw(" EXISTS (SELECT 1 FROM oe_pic JOIN files ON pic_fileid=fileid WHERE pic_oe_id = oe_id AND pic_type='img')");
                } else {
                    $data = raw(" NOT EXISTS (SELECT 1 FROM oe_pic JOIN files ON pic_fileid=fileid WHERE pic_oe_id = oe_id AND pic_type='img')");
                }
            } else if ($field == 'oe_name'){
               $field = 'oi.oe_' . cateName();
            }
        });

        foreach ($rules as $filed => $val) {
            if (empty($val)) continue;
            switch ($filed) {
                case 'p_cate':
                    $where['oe_cate_id'] = explode(',', $rules['p_cate']);
                    break;
                case 'numbers':
                    $tnums = [];
                    foreach ($val as $tk => $tv) {
                        $tnums = array_merge($tnums, reSearchNum($tv));
                    }
                    $tnums = array_unique($tnums);
                    $where['oen.on_formart' . ($rules['numlike'] ? '[~]' : '')] = $tnums;
                    $fields[] = 'oen.on_number';
                    $fields[] = 'fac.pf_name(on_pf_name)';
                    $join['[><]pro_factory(fac)'] = ['oen.on_pf_id' => 'pf_id'];
                    break;
            }
        }
        if (!empty($this->joinWhere)) {
            $where = array_merge($where, $this->joinWhere);
        }

        $this->db->Begin();
        //筛选不在项目明细中的,且更新推荐列表的prr_pi_id
        $piOEIds = $this->db->select('project_item', 'pi_oe_id', ["GROUP" => "pi_oe_id"]);
        if (!empty($piOEIds)) {
            $where['oe_id[!]'] = $piOEIds;
            // 删除project_recommend表中prr_oe_id在piOEIds中的项,保持数据一致
            $this->db->delete('project_recommend', ['prr_oe_id' => $piOEIds]);
        }
        $where['GROUP'] = 'oe_id';

        $where = $this->listWhere($where, $page, $pagesize, $sidx, $sord, $orderby);
        if ($page > 0 && $pagesize > 0) {
            $pages = $this->paging($table, $join, '*', $where, $page, $pagesize);
            $where["LIMIT"] = [$pages['limit'], $pagesize];
        }
        $arr = $this->db->select($table, $join, $fields, $where);
        $this->db->End();

        if (!empty($arr)) {
            $oeIds = array_column($arr, 'oe_id');

            //存在的供应商
            $sups = [];
            $sql = sprintf("SELECT oe_id,sup_id, sup_name FROM oe_numidx
				JOIN oe_item ON oe_id = on_oe_id
				JOIN supplier_pro ON on_pf_id=spr_pf_id AND on_formart=spr_format
				JOIN supplier ON spr_sup_id=sup_id
				LEFT JOIN object ON sup_level=o_id WHERE oe_cate_id = spr_cat_id and oe_id IN (%s) group by oe_id, sup_id ORDER BY o_sort,sup_name", implode(',', $oeIds));
            
            $rows = $this->db->query($sql)->fetchAll();
            if (!empty($rows)) {
                foreach ($rows as $rv) {
                    $sups[$rv['oe_id']][] = $rv;
                }
            }

            //存在商品库
            $pcs = [];
            $sql = sprintf("SELECT oe_id, p_id, p_innum,pb_" . cateName() . " AS pb_name FROM oe_numidx
				JOIN oe_item ON oe_id = on_oe_id
				JOIN pro_numidx ON on_pf_id=pn_pf_id AND on_formart=pn_formart 
				JOIN pro ON p_id=pn_p_id
                JOIN pro_brand on p_pb_id=pb_id
				WHERE oe_cate_id = p_cate AND oe_id IN (%s) group by oe_id, p_id order by p_pb_id", implode(',', $oeIds));
            $rows = $this->db->query($sql)->fetchAll();
            if (!empty($rows)) {
                foreach ($rows as $rv) {
                    $pcs[$rv['oe_id']][] = $rv;
                }
            }

            // 查找所有保时捷参考号(方式2，1在上面连表)
            $porscheRefs = [];
            $sql = sprintf("SELECT oe_id, on_number FROM oe_numidx
                JOIN oe_item ON oe_id = on_oe_id
				JOIN oe_numidx_tag_brand ON on_id = ontb_on_id
				WHERE ontb_tb_id = %d AND oe_id IN (%s) group by oe_id, on_number", $this->porscheId[0], implode(',', $oeIds));
            $rows = $this->db->query($sql)->fetchAll();
            if (!empty($rows)) {
                foreach ($rows as $rv) {
                    $porscheRefs[$rv['oe_id']][] = $rv['on_number'];
                }
            }

            // 统计确认成员
            $chkNum = [];
            $sql = sprintf("SELECT COUNT(pru_u_id) AS chkNum,prr_oe_id  FROM project_recommend_userchk
                JOIN project_recommend ON prr_id = pru_prr_id
				JOIN oe_item ON oe_id = prr_oe_id
				WHERE oe_id IN (%s) group by pru_prr_id",implode(',', $oeIds));
            $rows = $this->db->query($sql)->fetchAll();
            if (!empty($rows)) {
                foreach ($rows as $rv) {
                    $chkNum[$rv['prr_oe_id']] = $rv['chkNum'];
                }
            }

            //主图
            $picRes = $this->db->select('oe_pic', ['[><]files' => ['pic_fileid' => 'fileid']], '*', ['pic_oe_id' => $oeIds, 'pic_type' => 'img', 'pic_ismain' => 1]);
            $picArr = [];
            foreach ($picRes as $pic) {
                if ($pic['pic_type'] == 'img' && $pic['filetype'] == 'image') {
                    $picArr[$pic['pic_oe_id']]['bigImg'] = $pic['filepath'] . '/' . $pic['filebasename'] . 'big.' . $pic['fileextname'];
                    $picArr[$pic['pic_oe_id']]['smallImg'] = $pic['filepath'] . '/' . $pic['filebasename'] . 'small.' . $pic['fileextname'];
                    if ($params['isExport']) {
                        $picArr[$pic['pic_oe_id']]['type'] = 'image';
                        $picArr[$pic['pic_oe_id']]['filename'] = \mvc::$cfg['dir']['files'] . '/' . $picArr[$pic['pic_oe_id']]['smallImg'];

                        list($width, $height, $type, $attr) = getimagesize($picArr[$pic['pic_oe_id']]['filename']);
                        $picArr[$pic['pic_oe_id']]['widthScale'] = 1;
                        $picArr[$pic['pic_oe_id']]['heightScale'] = 1;
                        $picArr[$pic['pic_oe_id']]['rowHeight'] = $height;
                    }
                }
            }

            foreach ($arr as &$v) {
                $v['oe_amount'] = $v['oe_amount'] ?: '';
                $v['sups'] = $sups[$v['oe_id']] ? array_values($sups[$v['oe_id']]) : [];
                $v['pcs'] = $pcs[$v['oe_id']] ? array_values($pcs[$v['oe_id']]) : [];
                $v['porscheRefs'] = $porscheRefs[$v['oe_id']] ? array_values($porscheRefs[$v['oe_id']]) : [];
                $v['chkNum'] = $chkNum[$v['oe_id']] ? $chkNum[$v['oe_id']].'/'.$this->userNum : '';
                $v['pic'] = $picArr[$v['oe_id']] ?? [];

                // 检测供应商
                if (!empty($v['sups'])) {
                    $v['chk_sups'] .= '<ul class="sup-list">';
                    foreach ($v['sups'] as $tpk => $tpv) {
                        $v['chk_sups'] .= '<li> ' . $tpv['sup_name'] . ($tpk === 0 ? ' <a href="javascript:openOeSupList(' . $v['oe_id'] . ',\'' . $v['oe_oe'] . '\');"><i class="fa fa-list-alt"></i></a>' : '') . '</li>';
                    }
                    $v['chk_sups'] .= '</ul>';
                }

                //检测产品库
                $v['chk_pcs'] = '';
                if (!empty($v['pcs'])) {
                    $v['chk_pcs'] .= '<ul class="pro-list-self">';
                    foreach ($v['pcs'] as $tpk => $tpv) {
                        $tpv['pro_url'] = \mvc::$cfg['sitePath'] . '/pro/detail?tab=base&p_id=' . $tpv['p_id'];
                        $v['chk_pcs'] .= '<li><a href="' . $tpv['pro_url'] . '" tab>' . $tpv['pb_name'] . ':' . $tpv['p_innum'] . '</a></li>';
                    }
                    $v['chk_pcs'] .= '</ul>';
                }

                // 检测保时捷参考号
                $v['chk_porscheRefs'] = '';
                if (!empty($v['porscheRefs'])) {
                    $v['chk_porscheRefs'] .= '<ul class="pro-list-self">';
                    foreach ($v['porscheRefs'] as $tpk => $tpv) {
                        $v['chk_porscheRefs'] .= '<li>' . $tpv . '</li>';
                    }
                    $v['chk_porscheRefs'] .= '</ul>';
                }

                $v['captions'] = [
                    'oe_pic' => $v['filepath'] ? \mvc::$cfg['urls']['outfiles'] . '/' . $v['filepath'] . '/' . $v['filebasename'] . 'small.' . $v['fileextname'] : '',
                    'oe_oe' => $v['pf_name'] . ': ' . $v['oe_oe'],
                ];
            }
        }
        $data = [
            'page' => $page,
            'total' => $pages['total'],
            'records' => $pages['records'],
            'rows' => $arr
        ];
        return $data;
    }

    /**
     * @param  array $params
     * @param  boolean $isRecommend 是否为推荐（默认推荐）
     * @method 推荐(不推荐)选中项目
     * @author hongzhang
     * @copyright 2025-08-05
     */
    public function setRecommend($params, $isRecommend = true)
    {
        $oeIds = explode(',', $params['ids']);

        $this->db->Begin();
        $existIds = $this->db->select('project_recommend', [], 'prr_oe_id', []);

        //需要新增的项
        $newIds = array_diff($oeIds, $existIds);
        if (!empty($newIds)) {
            $addTemp = [];
            foreach ($newIds as $k => $v) {
                $addTemp[] = [
                    'prr_pi_id' => 0,
                    'prr_oe_id' => $v,
                    'prr_status' => $isRecommend ? 1 : -1,
                    'prr_opuser' => getOpUser(),
                    'prr_create_time' => date('Y-m-d H:i:s'),
                    'prr_update_time' => date('Y-m-d H:i:s'),
                ];
            }
            $this->db->insert('project_recommend', $addTemp);
        }

        //需要更新的项
        $updateIds = array_intersect($oeIds, $existIds);
        if (!empty($updateIds)) {
            $this->db->update('project_recommend', ['prr_status' => $isRecommend ? 1 : -1, 'prr_update_time' => date('Y-m-d H:i:s')], ['prr_oe_id' => $updateIds]);
        }
        $this->db->End();
    }


    /**
     * @param  int $prr_id
     * @method 确认成员详情
     * @author hongzhang
     * @copyright 2025-08-21
     */
    public function chkMemberDetail($params)
    {
        $prr_id = $params['prr_id'];
        $table = 'project_recommend_userchk';

        $filters = $params['filters'];
        $page = $params['page'];
        $pagesize = $params['rows'];
        $sidx = empty($params['sidx']) ? 'pru_u_id' : $params['sidx'];
        $sord = empty($params['sidx']) ? 'desc' : $params['sord'];
        $orderby = $params['orderby'];
        $join = [
            "[>]user"=>["pru_u_id"=>"u_id"]
        ];
        $fields = ['u_id','u_mb','u_name','u_company'];
        $where = $this->filter2medoo($filters, function () {});
        $where['pru_prr_id'] = $prr_id;
        $where = $this->listWhere($where, $page, $pagesize, $sidx, $sord, $orderby);
        if ($page > 0 && $pagesize > 0) {
            $pages = $this->paging($table, $join, '*', $where, $page, $pagesize);
            $where["LIMIT"] = [$pages['limit'], $pagesize];
        }
        $data = $this->db->select($table, $join, $fields, $where);
        return $data;
    }

    /**
     * @param  array $params
     * @method 选中项转项目清单
     * @author hongzhang
     * @copyright 2025-08-25
     */
    public function toProjectItems($params)
    {
        $idArr = explode(',', $params['ids']);
        $this->db->Begin();
        $insertOEArr = $this->db->select('oe_item',[],['oe_oe_format'],['oe_id'=>$idArr]);
        $insertOE = array_column($insertOEArr, 'oe_oe_format');
        if(!empty($insertOE)){
            $insertTempArr = [];
            $insertTempArr[] = ['号码','名称','备注','小类'];
            foreach($insertOE as $v){
                $insertTempArr[] = [$v, '', '', ''];
            }
            $import = makeOne('\model\import\\'.$params['impcode']);
            $params['data'] = $insertTempArr;
            unset($params['ids']);
            $import->params = $params;
            unset($import->params['data']);
            $data = $import->import($params['data']);
            // 添加成功的同时同步删除项目推荐表
            $this->db->delete('project_recommend', ['prr_oe_id'=>$idArr]);
            (new \model\user)->toLog('导入','导入'.$import->title.' '.(is_string($data) ? $data : count($data).'条,第一条内容:'.implode(',',$data[0])));
        }
        $this->db->End();
        return $data;
    }
}