生产入库、采购入库接口提交

yl

2023-06-13 d6502baa8e5610c04127fbee92ff6111e40956f8

 VueWebApi/DLL/DAL/QualityManagementDAL.cs

@@ -1,4 +1,5 @@
using Dapper;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
@@ -63,7 +64,7 @@
            }
            return mes;
        }
      

        #endregion

        #region[质量管理，缺陷定义新增、编辑提交]
@@ -267,7 +268,7 @@
                    rout.name = data.Rows[0]["NAME"].ToString();
                    rout.description = data.Rows[0]["DESCR"].ToString();
                }
                else 
                else
                {
                    mes.code = "300";
                    mes.count = 0;
@@ -393,7 +394,7 @@
                        //新增工序检验标准子表
                        sql = @"insert TStepCheckStandardSub (stepstaned_code,stepcheckitem_seq,stepcheckitem_code,stepcheckitem_desc,lm_user,lm_date) 
                                values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@stepcheckitem_desc,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc= json.Data.Rows[i]["STEPCHECKITEM_DESC"].ToString(), lm_user = username, lm_date = DateTime.Now.ToString() } });
                        list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc = json.Data.Rows[i]["STEPCHECKITEM_DESC"].ToString(), lm_user = username, lm_date = DateTime.Now.ToString() } });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
@@ -411,14 +412,14 @@
                        mes.data = null;
                    }
                }
                if (opertype == "Update") 
                if (opertype == "Update")
                {
                    //删除工序检验标准子表
                    sql = @"delete from TStepCheckStandardSub where stepstaned_code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_code = json.code} });
                    list.Add(new { str = sql, parm = new { stepstaned_code = json.code } });
                    //修改工序检验标准主表
                    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 } });
                    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++)
                    {
@@ -685,13 +686,8 @@
                    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 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
@@ -699,7 +695,7 @@
                            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;
                            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,35 @@
            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);
                //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;
                //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,63 +808,60 @@
                    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
                            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.partcode ORDER BY B.checkitem_code) = 1 THEN M.partcode
                            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.partcode ORDER BY B.checkitem_code) = 1 THEN M.partname
                            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.partcode ORDER BY B.checkitem_code) = 1 THEN M.partspec
                            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.step_code ORDER BY B.checkitem_code) = 1 THEN S.stepname
                            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.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN A.checkstaned_code
                            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.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN T.name
                            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.check_user ORDER BY B.checkitem_code) = 1 THEN U.username
                            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.check_typename ORDER BY B.checkitem_code) = 1 THEN A.check_typename
                            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.check_result ORDER BY B.checkitem_code) = 1 THEN A.check_result
                            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.check_descr ORDER BY B.checkitem_code) = 1 THEN A.check_descr
                            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.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
                            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.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果'
                            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 " + search;
                            where A.check_type in('FirstCheck','PatroCheck','EndCheck') " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName ="Table"; //设置DataTable的名称
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -870,7 +882,7 @@


        #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)
        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 = "";
@@ -914,20 +926,22 @@
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select *  from(
                            select code,name,status,checktype,sampmethod,sampscare,suitobject,descr,suitpart, 
                            case when suitobject='P' then
                            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) + ';%',';' + TStepCheckStandard.suitpart + ';')>0    
                            where   PATINDEX('%;' + RTRIM(TMateriel_Info.partcode) + ';%',';' + A.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')  
                            when suitobject='PY' 
                            when A.suitobject='PY' 
                            then 
                            STUFF(( SELECT ';' + name
                            FROM T_Dict
                            where   PATINDEX('%;' + RTRIM(T_Dict.code) + ';%',';' + TStepCheckStandard.suitpart + ';')>0    
                            where   PATINDEX('%;' + RTRIM(T_Dict.code) + ';%',';' + A.suitpart + ';')>0    
                            FOR XML PATH('')), 1, 1,'')
                            end as suitpartname
                            from TStepCheckStandard
                            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;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
@@ -955,7 +969,7 @@
            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  
                        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";
@@ -976,5 +990,218 @@
            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)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}