正清河 仓库看板、车间综合看板接口

yl

2023-09-13 d11b595b22ae25e49e24f298ba2ca58528bda532

 VueWebApi/DLL/DAL/ProductModelDAL.cs

@@ -27,7 +27,7 @@
            try
            {
                //获取设备类型数据
                sql = @"select code,name from T_Dict where dict_type='CHLX' ";
                sql = @"select code,name,iparent_id from T_Dict where dict_type='CHLX' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -421,12 +421,29 @@
        #endregion

        #region[存货档案查询]
        public static ToMessage InventoryFileSelect(string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
        public static ToMessage InventoryFileSelect(string stu_torgcode,string stu_torgtypecode,string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search0 = "";
            string search = "";
            string search1 = "";
            try
            {
                switch (stu_torgtypecode)
                {
                    case "":
                        break;
                    case "D":
                        search1 += "and L.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search1 += "and F.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
                        break;
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and A.partcode like '%'+@partcode+'%' ";
@@ -444,7 +461,8 @@
                }
                if (stocktypecode != "" && stocktypecode != null)
                {
                    search += "and D.code=@stocktypecode ";
                    search0+= " where code = @stocktypecode";
                    search += " and   CTE.iparent_id<>'' and CTE.dict_type='CHLX'";
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                }
                if (materialtypecode != "" && materialtypecode != null)
@@ -463,24 +481,80 @@
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
                            C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                               //var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
                               //            C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                               //            A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                               //            from TMateriel_Info A
                               //            left join TUom B on A.uom_code=B.code
                               //            left join TMateriel_Type C on A.materieltype_code=C.code
                               //            left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
                               //            left join T_Sec_Stck T on A.stck_code=T.code 
                               //            left join TUser U on A.lm_user=U.usercode
                               //            left join (
                               //               select distinct materiel_code from TMateriel_Route M
                               //               left join TOrganization F on M.torg_code=F.org_code 
                               //               left join TOrganization as L on F.parent_id=L.id 
                               //               where  1=1 "+search1+")  M on A.partcode=M.materiel_code where A.is_delete<>'1' " + search;
                var sql0 = @"WITH CTE AS (
                        SELECT  code,name,iparent_id,dict_type
                        FROM T_Dict " + search0 + " ";
                sql0 += @"UNION ALL
                        SELECT t.code,t.name,t.iparent_id,t.dict_type
                        FROM T_Dict t
                        INNER JOIN CTE c ON c.code = t.iparent_id)
                        SELECT COUNT(1) as total FROM (
                        SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
                            A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                            A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                            from TMateriel_Info A
                            left join TUom B on A.uom_code=B.code
                            left join TMateriel_Type C on A.materieltype_code=C.code
                            left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
                            left join T_Sec_Stck T on A.stck_code=T.code 
                            left join TUser U on A.lm_user=U.usercode
                            left join (
                              select distinct materiel_code from TMateriel_Route 
                            )  M on A.partcode=M.materiel_code
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                  FROM CTE
                  right join TMateriel_Info A on CTE.code=A.stocktype_code
                  left join TUom B on A.uom_code=B.code
                        left join T_Dict D on A.stocktype_code=D.code 
                        left join T_Sec_Stck T on A.stck_code=T.code 
                        left join TUser U on A.lm_user=U.usercode
                        left join (
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1 + ")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' " + search+") AS Result";
                var data0 = DapperHelper.selectdata(sql0, dynamicParams);
                if (data0.Rows.Count > 0)
                {
                    total = Convert.ToInt32(data0.Rows[0]["total"].ToString());
                }
                else 
                {
                    total = 0;
                }

                var sql = @"WITH CTE AS (
                        SELECT  code,name,iparent_id,dict_type
                        FROM T_Dict "+search0+" ";
                sql += @"UNION ALL
                        SELECT t.code,t.name,t.iparent_id,t.dict_type
                        FROM T_Dict t
                        INNER JOIN CTE c ON c.code = t.iparent_id)
                        SELECT * FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY lm_date desc) AS RowNum
                        FROM(
                        SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
                            A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
                            A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
                  FROM CTE
                  right join TMateriel_Info A on CTE.code=A.stocktype_code
                  left join TUom B on A.uom_code=B.code
                        left join T_Dict D on A.stocktype_code=D.code 
                        left join T_Sec_Stck T on A.stck_code=T.code 
                        left join TUser U on A.lm_user=U.usercode
                        left join (
                               select distinct materiel_code from TMateriel_Route M
                               left join TOrganization F on M.torg_code=F.org_code 
                               left join TOrganization as L on F.parent_id=L.id 
                               where 1=1 " + search1+")  M on A.partcode=M.materiel_code  where  A.is_delete<>'1' "+search+ " ) AS Temp) AS Result WHERE RowNum >= "+ startNum + " AND RowNum <="+ endNum + " ORDER BY "+ prop + " "+order+"";
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
                mes.data = data;
            }
            catch (Exception e)
            {
@@ -494,7 +568,7 @@
        #endregion

        #region[存货档案新增编辑]
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string materialtypecode, string minstockqty, string maxstockqty,string is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
        public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string minstockqty, string maxstockqty,string is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
@@ -520,7 +594,7 @@
                    dynamicParams.Add("@uomcode", uomcode);
                    dynamicParams.Add("@warehousecode", warehousecode);
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                    dynamicParams.Add("@materialtypecode", "");
                    dynamicParams.Add("@minstockqty", minstockqty);
                    dynamicParams.Add("@maxstockqty", maxstockqty);
                    dynamicParams.Add("@username", username);
@@ -556,7 +630,7 @@
                    dynamicParams.Add("@uomcode", uomcode);
                    dynamicParams.Add("@warehousecode", warehousecode);
                    dynamicParams.Add("@stocktypecode", stocktypecode);
                    dynamicParams.Add("@materialtypecode", materialtypecode);
                    dynamicParams.Add("@materialtypecode", "");
                    dynamicParams.Add("@minstockqty", minstockqty);
                    dynamicParams.Add("@maxstockqty", maxstockqty);
                    dynamicParams.Add("@username", username);
@@ -657,6 +731,18 @@
                }
                //当前物料是否生成出入库记录
                //有没有被工单引用
                sql = @"select *  from TK_Wrk_Man
                        where materiel_code=@materialcode";
                dynamicParams.Add("@materialcode", materialcode);
                var data3 = DapperHelper.selectdata(sql, dynamicParams);
                if (data2.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "物料已有关联的生产工单,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除物料
                sql = @"delete TMateriel_Info  where partcode=@materialcode";
@@ -760,63 +846,40 @@
        public static ToMessage SaveInventoryFile(string stu_torgcode,string stu_torgtypecode, string partcode, string defaultroute_code, string username, List<PartRout> json)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            string search = "";
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (json == null || json.Count <= 0)
                switch (stu_torgtypecode)
                {
                    switch (stu_torgtypecode)
                    {
                        case "":
                            //清除物料关联工艺路线表数据
                            sql = @"delete TMateriel_Route where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = partcode } });
                            break;
                        case "D":
                            //清除物料关联工艺路线表数据
                            sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                    case "":
                        //清除物料关联工艺路线表数据
                        sql = @"delete TMateriel_Route where materiel_code=@partcode";
                        list.Add(new { str = sql, parm = new { partcode = partcode } });
                        break;
                    case "D":
                        //清除物料关联工艺路线表数据
                        sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.materiel_code=@partcode and  L.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                            break;
                        case "W":
                            //清除物料关联工艺路线表数据
                            sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                        list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                        break;
                    case "W":
                        //清除物料关联工艺路线表数据
                        sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.materiel_code=@partcode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                            break;
                    }
                        list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                        break;
                }
                else
                if (json != null && json.Count >0)
                {
                    switch (stu_torgtypecode)
                    {
                        case "":
                            //清除物料关联工艺路线表数据
                            sql = @"delete TMateriel_Route where materiel_code=@partcode";
                            list.Add(new { str = sql, parm = new { partcode = partcode } });
                            break;
                        case "D":
                            //清除物料关联工艺路线表数据
                            sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.materiel_code=@partcode and  L.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                            break;
                        case "W":
                            //清除物料关联工艺路线表数据
                            sql = @"delete from TMateriel_Route where id in (select A.id   from TMateriel_Route A
                                    left join TOrganization T on A.torg_code=T.org_code 
                                    left join TOrganization as L on T.parent_id=L.id 
                                    where 1=1 and  A.materiel_code=@partcode and  T.org_code=@stu_torgcode)";
                            list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
                            break;
                    }
                    //物料+组织查询节拍工价表中对应的所有工艺路线,然后根据传过来的做对比，再去删除节拍工价表

                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
@@ -1905,12 +1968,19 @@
                    //标记工序表关联工作站标识
                    sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //根据工序清除节拍工价表
                    sql = @"delete TPrteEqp_Stad where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                }
                else
                {
                    //清除工序关联工作站表数据
                    sql = @"delete TFlw_Rteqp where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });
                    //根据工序清除节拍工价表
                    sql = @"delete TPrteEqp_Stad where step_code=@stepcode";
                    list.Add(new { str = sql, parm = new { stepcode = stepcode } });

                    //循环写入用户关联角色表
                    for (int i = 0; i < json.Count; i++)
                    {
@@ -2307,6 +2377,7 @@
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            string search1 = "";
            try
            {
                switch (stu_torgtypecode)
@@ -2314,11 +2385,11 @@
                    case "":
                        break;
                    case "D":
                        search += "and L.org_code=@stu_torgcode ";
                        search1 += "and L.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    case "W":
                        search += "and F.org_code=@stu_torgcode ";
                        search1 += "and F.org_code=@stu_torgcode ";
                        dynamicParams.Add("@stu_torgcode", stu_torgcode);
                        break;
                    default:
@@ -2341,7 +2412,7 @@
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode";
                    search += "and A.eqp_code=@eqpcode ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (search == "")
@@ -2358,9 +2429,22 @@
                            left join TFlw_Rout C on A.route_code=C.code
                            left join TStep D on A.step_code=D.stepcode
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TOrganization F on E.wksp_code=F.org_code
                            left join TOrganization F on C.torg_code=F.org_code
                            left join TOrganization L on  F.parent_id=L.id
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.is_delete<>'1' " + search;
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.is_delete<>'1' " + search1 + search;
                    sql += @"union all 
                            select A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
                            D.stepcode,D.stepname,A.eqp_code,E.name as eqp_name,''as wksp_code,'' as wksp_name,
                            A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
                            from TPrteEqp_Stad A
                            left join TMateriel_Info B on A.materiel_code=B.partcode
                            left join TFlw_Rout C on A.route_code=C.code
                            left join TStep D on A.step_code=D.stepcode
                            left join TCustomer E on A.eqp_code=E.code
                            left join TOrganization F on C.torg_code=F.org_code
                            left join TOrganization L on  F.parent_id=L.id
                            where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' " + search;     
                            
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";