1.工艺路线删除判断工艺路路线是否被存货绑定 2.新增质检方案列表查询、列表查看编辑带回接口

yl

2023-02-21 e3e68c2fc02f2ada02254f1167056278cd570f5c

 VueWebApi/DLL/DAL/QualityManagementDAL.cs

@@ -29,23 +29,25 @@
            {
                if (defectcode != "" && defectcode != null)
                {
                    search += "and code=@defectcode ";
                    search += "and A.code like '%'+@defectcode+'%' ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and name like '%'+@defectname+'%' ";
                    search += "and A.name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (defectdescr != "" && defectdescr != null)
                {
                    search += "and descr like '%'+@defectdescr+'%' ";
                    search += "and A.descr like '%'+@defectdescr+'%' ";
                    dynamicParams.Add("@defectdescr", defectdescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TDefect where is_delete<>'1' " + search;
                var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date  from TDefect A
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -61,6 +63,7 @@
            }
            return mes;
        }
      
        #endregion

        #region[质量管理，缺陷定义新增、编辑提交]
@@ -208,23 +211,25 @@
            {
                if (stanedcode != "" && stanedcode != null)
                {
                    search += "and code=@stanedcode ";
                    search += "and A.code like '%'+@stanedcode+'%' ";
                    dynamicParams.Add("@stanedcode", stanedcode);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and name like '%'+@stanedname+'%' ";
                    search += "and A.name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (staneddescr != "" && staneddescr != null)
                {
                    search += "and descr like '%'+@staneddescr+'%' ";
                    search += "and A.descr like '%'+@staneddescr+'%' ";
                    dynamicParams.Add("@staneddescr", staneddescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TStepCheckStandard where is_delete<>'1' " + search;
                var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date  from TStepCheckStandard A
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -255,12 +260,21 @@
                        where code=@defectcode and is_delete<>'1'";
                dynamicParams.Add("@defectcode", defectcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                RoutEdit rout = new RoutEdit();
                rout.code = data.Rows[0]["CODE"].ToString();
                rout.name = data.Rows[0]["NAME"].ToString();
                rout.description = data.Rows[0]["DESCR"].ToString();

                if (data.Rows.Count > 0)
                {
                    rout.code = data.Rows[0]["CODE"].ToString();
                    rout.name = data.Rows[0]["NAME"].ToString();
                    rout.description = data.Rows[0]["DESCR"].ToString();
                }
                else 
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "无工序检验标准信息!";
                    mes.data = null;
                    return mes;
                }
                //根据工序检验标准编码获取关联的工序检验项目信息
                sql = @"select B.code,B.name,A.stepcheckitem_desc  from TStepCheckStandardSub A
                        left join TStepCheckItem  B on A.stepcheckitem_code=B.code
@@ -308,6 +322,59 @@
        }
        #endregion

        #region[工序检验标准删除]
        public static ToMessage DeleteStepCheckStaned(string stanedcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TStepCheckRecord  where checkstaned_code=@stanedcode";
                dynamicParams.Add("@stanedcode", stanedcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前检验标准已生成检验记录,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //删除工序检验标准主表
                sql = @"delete TStepCheckStandard  where code=@stanedcode";
                list.Add(new { str = sql, parm = new { stanedcode = stanedcode } });
                //删除工序检验标准项目子表
                sql = @"delete TStepCheckStandardSub  where stepstaned_code=@stanedcode";
                list.Add(new { str = sql, parm = new { stanedcode = stanedcode } });

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "工序检验标准删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "工序检验标准删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序检验标准新增、编辑提交]
        public static ToMessage AddUpdateStepCheckStaned(string opertype, RoutEdit json, string username)
        {
@@ -333,25 +400,25 @@
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
                if (opertype == "Update") 
                {
                    //删除工序检验标准子表
                    sql = @"delete from TStepCheckStandard where stepstaned_code=@stepstaned_code";
                    sql = @"delete from TStepCheckStandardSub where stepstaned_code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_code = json.code} });
                    //修改工序检验标准主表
                    sql = @"update  TStepCheckStandard set name=@stepstaned_name,descr=@stepstaned_desc where stepstaned_code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_name = json.code, stepstaned_desc=json.description } });
                    sql = @"update  TStepCheckStandard set name=@stepstaned_name,descr=@stepstaned_desc where code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_code=json.code, stepstaned_name = json.name, stepstaned_desc=json.description } });
                    //新增工序检验标准子表
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
@@ -366,14 +433,14 @@
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
@@ -400,23 +467,25 @@
            {
                if (itemcode != "" && itemcode != null)
                {
                    search += "and code=@itemcode ";
                    search += "and A.code like '%'+@itemcode+'%' ";
                    dynamicParams.Add("@itemcode", itemcode);
                }
                if (itemname != "" && itemname != null)
                {
                    search += "and name like '%'+@itemname+'%' ";
                    search += "and A.name like '%'+@itemname+'%' ";
                    dynamicParams.Add("@itemname", itemname);
                }
                if (itemdescr != "" && itemdescr != null)
                {
                    search += "and descr like '%'+@itemdescr+'%' ";
                    search += "and A.descr like '%'+@itemdescr+'%' ";
                    dynamicParams.Add("@itemdescr", itemdescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TStepCheckItem where is_delete<>'1' " + search;
                var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date  from TStepCheckItem A
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -567,5 +636,345 @@
            return mes;
        }
        #endregion


        #region[工序检验记录列表查询]
        public static ToMessage StepCheckTableSearch(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and B.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partapec != "" && partapec != null)
                {
                    search += "and B.partspec like '%'+@partapec+'%' ";
                    dynamicParams.Add("@partapec", partapec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and S.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (standname != "" && standname != null)
                {
                    search += "and T.name like '%'+@standname+'%' ";
                    dynamicParams.Add("@standname", standname);
                }
                if (checktype != "" && checktype != null)
                {
                    search += "and A.check_type=@checktype ";
                    dynamicParams.Add("@checktype", checktype);
                }
                if (checkresult != "" && checkresult != null)
                {
                    search += "and A.check_result=@checkresult ";
                    dynamicParams.Add("@checkresult", checkresult);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.wo_code,A.partcode,B.partname,B.partspec,A.step_code,S.stepname,A.checkstaned_code,T.name as checkstaned_name,A.check_user,
                            A.check_type,A.check_result,A.check_descr,A.check_qty,U.username as lm_user,A.lm_date   
                            from TStepCheckRecord A
                            left join TMateriel_Info B on A.partcode=B.partcode
                            left join TStep S on A.step_code=S.stepcode
                            left join TStepCheckStandard T on A.checkstaned_code=T.code
                            left join TUser U on A.check_user=U.usercode 
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序检验记录列表明细查询]
        public static ToMessage StepCheckTableSubSearch(string id)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select A.checkitem_seq,A.checkitem_code,A.checkitem_name,A.checkitem_descr,A.check_result,U.username as lm_user,A.lm_date
                        from TStepCheckRecordSub A
                        left join TUser U on A.lm_user=U.usercode
                        where A.m_id=@id";
                dynamicParams.Add("@id", id);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序检验记录导出]
        public static ToMessage StepCheckTableOutExcel(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.partcode like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and B.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partapec != "" && partapec != null)
                {
                    search += "and B.partspec like '%'+@partapec+'%' ";
                    dynamicParams.Add("@partapec", partapec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and S.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (standname != "" && standname != null)
                {
                    search += "and T.name like '%'+@standname+'%' ";
                    dynamicParams.Add("@standname", standname);
                }
                if (checktype != "" && checktype != null)
                {
                    search += "and A.check_type=@checktype ";
                    dynamicParams.Add("@checktype", checktype);
                }
                if (checkresult != "" && checkresult != null)
                {
                    search += "and A.check_result=@checkresult ";
                    dynamicParams.Add("@checkresult", checkresult);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.wo_code ORDER BY B.checkitem_code) = 1 THEN A.wo_code
                            ELSE ''END AS '工单号',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partcode
                            ELSE ''END AS '产品编码',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partname
                            ELSE ''END AS '产品名称',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partspec
                            ELSE ''END AS '规格型号',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.step_code ORDER BY B.checkitem_code) = 1 THEN S.stepname
                            ELSE ''END AS '工序名称',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN A.checkstaned_code
                            ELSE ''END AS '标准编码',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN T.name
                            ELSE ''END AS '标准名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.check_user ORDER BY B.checkitem_code) = 1 THEN U.username
                            ELSE ''END AS '检验人员',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.check_typename ORDER BY B.checkitem_code) = 1 THEN A.check_typename
                            ELSE ''END AS '检验类型',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.check_result ORDER BY B.checkitem_code) = 1 THEN A.check_result
                            ELSE ''END AS '检验结果',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.check_descr ORDER BY B.checkitem_code) = 1 THEN A.check_descr
                            ELSE ''END AS '检验描述',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.check_qty ORDER BY B.checkitem_code) = 1 THEN A.check_qty
                            END AS '检验数量',
                           CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.lm_date ORDER BY B.checkitem_code) = 1 THEN A.lm_date
                            END AS '检验时间',
                            B.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果'
                            from TStepCheckRecord A
                            left join TStepCheckRecordSub B on A.id=B.m_id
                            left join TMateriel_Info M on A.partcode=M.partcode
                            left join TStep S on A.step_code=S.stepcode
                            left join TStepCheckStandard T on A.checkstaned_code=T.code
                            left join TUser U on A.check_user=U.usercode  
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName ="Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[质检方案列表查询]
        public static ToMessage QualityInspectionSearch(string qualityinsptcode, string qualityinsptname, string status, string checktype, string sampltype,string suitobject, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (qualityinsptcode != "" && qualityinsptcode != null)
                {
                    search += "and AA.code like '%'+@qualityinsptcode+'%' ";
                    dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
                }
                if (qualityinsptname != "" && qualityinsptname != null)
                {
                    search += "and AA.name like '%'+@qualityinsptname+'%' ";
                    dynamicParams.Add("@qualityinsptname", qualityinsptname);
                }
                if (status != "" && status != null)
                {
                    search += "and AA.status=@status ";
                    dynamicParams.Add("@status", status);
                }
                if (checktype != "" && checktype != null)
                {
                    search += "and AA.checktype=@checktype ";
                    dynamicParams.Add("@checktype", checktype);
                }
                if (sampltype != "" && sampltype != null)
                {
                    search += "and AA.sampmethod=@sampltype ";
                    dynamicParams.Add("@sampltype", sampltype);
                }
                if (suitobject != "" && suitobject != null)
                {
                    search += "and AA.suitobject=@suitobject ";
                    dynamicParams.Add("@suitobject", suitobject);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select *  from(
                            select code,name,status,checktype,sampmethod,sampscare,suitobject,descr,suitpart, 
                            case when suitobject='P' then
                            STUFF(( SELECT ';' + partname
                            FROM TMateriel_Info
                            where   PATINDEX('%;' + RTRIM(TMateriel_Info.partcode) + ';%',';' + TStepCheckStandard.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')  
                            when suitobject='PY' 
                            then 
                            STUFF(( SELECT ';' + name
                            FROM T_Dict
                            where   PATINDEX('%;' + RTRIM(T_Dict.code) + ';%',';' + TStepCheckStandard.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')
                            end as suitpartname
                            from TStepCheckStandard
                            ) as AA 
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[质检方案查看编辑]
        public static ToMessage QualityInspectionSeeEdit(string qualityinsptcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select A.stepcheckitem_seq,A.stepcheckitem_code,B.name as stepcheckitem_name,A.required,
                        A.numberjudge,A.unit,A.decimal,A.standvalue,A.uppervalue,A.lowervalue  
                        from TStepCheckStandardSub A
                        inner join TStepCheckItem B on A.stepcheckitem_code=B.code
                        where A.stepstaned_code=@qualityinsptcode";
                dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}