增加App报工、工序检验功能

yl

2023-05-30 925394b1b7f61a2c2a15fdaa0ecd0763214ccda1

 VueWebApi/DLL/DAL/QualityManagementDAL.cs

@@ -1,4 +1,5 @@
using Dapper;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
@@ -652,7 +653,7 @@
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.partcode like '%'+@stanedname+'%' ";
                    search += "and A.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
@@ -685,21 +686,16 @@
                    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.wo_code,A.partcode,B.partname,B.partspec,A.step_code,S.stepname,A.checkstaned_code,T.name as checkstaned_name,A.check_user,
                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.lm_user=U.usercode 
                            where " + search;
                            left join TUser U on A.check_user=U.usercode 
                            where A.check_type in('FirstCheck','PatroCheck','EndCheck') " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -724,16 +720,24 @@
            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;
                //存储过程名
                sql = @"h_p_IFCLD_WuLiuCheckSubSelect";
                dynamicParams.Add("@checkid", id);
                DataTable dt = DapperHelper.selectProcedure(sql, dynamicParams);
                if (dt.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "查询成功!";
                    mes.data = dt;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "无检验明细数据!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
@@ -793,6 +797,115 @@
                    search += "and A.check_result=@checkresult ";
                    dynamicParams.Add("@checkresult", checkresult);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.wo_code
                            ELSE ''END AS '工单号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partcode
                            ELSE ''END AS '产品编码',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partname
                            ELSE ''END AS '产品名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partspec
                            ELSE ''END AS '规格型号',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN S.stepname
                            ELSE ''END AS '工序名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.checkstaned_code
                            ELSE ''END AS '标准编码',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN T.name
                            ELSE ''END AS '标准名称',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN U.username
                            ELSE ''END AS '检验人员',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_typename
                            ELSE ''END AS '检验类型',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_result
                            ELSE ''END AS '检验结果',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_descr
                            ELSE ''END AS '检验描述',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_qty
                            END AS '样本数量',
                            CASE WHEN
                            ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.lm_date
                            END AS '检验时间',
                            B.checknum as 检验件次,B.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果',
                            C.standvalue as '标准值',C.uppervalue as '上限值',C.lowervalue as '下限值',B.check_value as '实测值'
                            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 TStepCheckStandardSub C on A.checkstaned_code=C.stepstaned_code and B.checkitem_code=C.stepcheckitem_code
                            left join TUser U on A.check_user=U.usercode  
                            where A.check_type in('FirstCheck','PatroCheck','EndCheck') " + 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 ";
@@ -800,61 +913,273 @@
                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  
                var sql = @"select *  from(
                            select A.code,A.name,A.status,A.checktype,A.sampmethod,A.sampscare,A.suitobject,A.stepcode,S.stepname,A.descr,A.suitpart,A.lm_date,U.username,
                            case when A.suitobject='P' then
                            STUFF(( SELECT ';' + partname
                            FROM TMateriel_Info
                            where   PATINDEX('%;' + RTRIM(TMateriel_Info.partcode) + ';%',';' + A.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')  
                            when A.suitobject='PY' 
                            then 
                            STUFF(( SELECT ';' + name
                            FROM T_Dict
                            where   PATINDEX('%;' + RTRIM(T_Dict.code) + ';%',';' + A.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')
                            end as suitpartname
                            from TStepCheckStandard A
                            left join TUser U on  A.lm_user=U.usercode
                            left join TStep S on A.stepcode=S.stepcode
                            ) as AA 
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName ="Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
                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.decimalnum,A.standvalue,A.uppervalue,A.lowervalue,A.stepcheckitem_desc  
                        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

        #region[质检方案新增/编辑提交]
        public static ToMessage QualityInspectionAddEditSave(string qualityinsptcode, string qualityinsptname, string status, string checktype, string sampmethod, string sampscare, string suitobject, string suitpart, string descr, string stepcode, string checkitem, string type, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                string date = DateTime.Now.ToString(); //获取系统时间
                //获取质检列表检验项目数据
                JArray arra = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject(checkitem);
                list.Clear();
                switch (type)
                {
                    case "Add": //新增
                        //写入质检方案主表
                        sql = @"insert into  TStepCheckStandard(code,name,status,checktype,sampmethod,sampscare,suitobject,suitpart,stepcode,descr,lm_user,lm_date) 
                                values(@code,@name,@status,@checktype,@sampmethod,@sampscare,@suitobject,@suitpart,@stepcode,@descr,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = qualityinsptcode,
                                name = qualityinsptname,
                                status = status,
                                checktype = checktype,
                                sampmethod = sampmethod,
                                sampscare = sampscare,
                                suitobject = suitobject,
                                suitpart = suitpart,
                                stepcode = stepcode,
                                descr = descr,
                                lm_user = username,
                                lm_date = date
                            }
                        });
                        //写入质检方案子表
                        for (int i = 0; i < arra.Count; i++)
                        {
                            sql = @"insert into  TStepCheckStandardSub(stepstaned_code,stepcheckitem_seq,stepcheckitem_code,required,numberjudge,unit,decimalnum,standvalue,uppervalue,lowervalue,stepcheckitem_desc,lm_user,lm_date) 
                                values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@required,@numberjudge,@unit,@decimalnum,@standvalue,@uppervalue,@lowervalue,@stepcheckitem_desc,@lm_user,@lm_date)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    stepstaned_code = qualityinsptcode,
                                    stepcheckitem_seq = Convert.ToInt32(arra[i]["stepcheckitem_seq"].ToString()),
                                    stepcheckitem_code = arra[i]["stepcheckitem_code"].ToString(),
                                    required = arra[i]["required"].ToString(),
                                    numberjudge = arra[i]["numberjudge"].ToString(),
                                    unit = arra[i]["unit"].ToString(),
                                    decimalnum = arra[i]["decimalnum"].ToString(),
                                    standvalue = arra[i]["standvalue"].ToString(),
                                    uppervalue = arra[i]["uppervalue"].ToString(),
                                    lowervalue = arra[i]["lowervalue"].ToString(),
                                    stepcheckitem_desc = arra[i]["stepcheckitem_desc"].ToString(),
                                    lm_user = username,
                                    lm_date = date
                                }
                            });
                        }
                        break;
                    case "Update"://修改
                        //判断是否生成检验记录(入厂检验(InCheck)  出厂检验(OutCheck)  FirstCheck（首检） PatroCheck(巡检)  EndCheck(完工检))
                        sql = @"select *  from TStepCheckRecord where checkstaned_code=@qualityinsptcode and check_type=@checktype";
                        dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
                        dynamicParams.Add("@checktype", checktype);
                        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 from TStepCheckStandardSub where stepstaned_code=@code";
                        list.Add(new { str = sql, parm = new { code = qualityinsptcode } });
                        //修改质检方案主表
                        sql = @"update TStepCheckStandard set name=@name,status=@status,checktype=@checktype,sampmethod=@sampmethod,sampscare=@sampscare,
                                suitobject=@suitobject,suitpart=@suitpart,stepcode=@stepcode,descr=@descr
                                where code=@code";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = qualityinsptcode,
                                name = qualityinsptname,
                                status = status,
                                checktype = checktype,
                                sampmethod = sampmethod,
                                sampscare = sampscare,
                                suitobject = suitobject,
                                suitpart = suitpart,
                                stepcode = stepcode,
                                descr = descr,
                            }
                        });
                        //新增质检方案子表
                        for (int i = 0; i < arra.Count; i++)
                        {
                            sql = @"insert into  TStepCheckStandardSub(stepstaned_code,stepcheckitem_seq,stepcheckitem_code,required,numberjudge,unit,decimalnum,standvalue,uppervalue,lowervalue,stepcheckitem_desc,lm_user,lm_date) 
                                values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@required,@numberjudge,@unit,@decimalnum,@standvalue,@uppervalue,@lowervalue,@stepcheckitem_desc,@lm_user,@lm_date)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    stepstaned_code = qualityinsptcode,
                                    stepcheckitem_seq = Convert.ToInt32(arra[i]["stepcheckitem_seq"].ToString()),
                                    stepcheckitem_code = arra[i]["stepcheckitem_code"].ToString(),
                                    required = arra[i]["required"].ToString(),
                                    numberjudge = arra[i]["numberjudge"].ToString(),
                                    unit = arra[i]["unit"].ToString(),
                                    decimalnum = arra[i]["decimalnum"].ToString(),
                                    standvalue = arra[i]["standvalue"].ToString(),
                                    uppervalue = arra[i]["uppervalue"].ToString(),
                                    lowervalue = arra[i]["lowervalue"].ToString(),
                                    stepcheckitem_desc = arra[i]["stepcheckitem_desc"].ToString(),
                                    lm_user = username,
                                    lm_date = date
                                }
                            });
                        }
                        break;
                    default:
                        break;
                }
                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 QualityInspectionDelete(string qualityinsptcode, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断是否生成检验记录(入厂检验(InCheck)  出厂检验(OutCheck)  FirstCheck（首检） PatroCheck(巡检)  EndCheck(完工检))
                sql = @"select *  from TStepCheckRecord where checkstaned_code=@qualityinsptcode";
                dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
                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=@code";
                list.Add(new { str = sql, parm = new { code = qualityinsptcode } });
                //删除质检方案子表
                sql = @"delete TStepCheckStandardSub  where stepstaned_code=@code";
                list.Add(new { str = sql, parm = new { code = qualityinsptcode } });

                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)
            {