1.生产入库功能接口 2.生产入库单报表接口

yl

2024-10-30 65a9633ebcf5cf4c0871d1c857a743ec7694c157

 VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs

@@ -5,6 +5,8 @@
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;
using VueWebCoreApi.Models.InventoryModel;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.DLL.DAL
@@ -20,7 +22,7 @@


        #region[生产进度报表]
        public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status,string socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
        public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -118,7 +120,7 @@
        #endregion

        #region[生产进度报表导出]
        public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status,string socode, string wocode,string partcode, string partname, string partspec, string opendate, string closedate)
        public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status, string socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -222,7 +224,7 @@


        #region[班组工资报表记录查询]
        public static ToMessage GroupSalaryReportSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
        public static ToMessage GroupSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -233,7 +235,7 @@
                    search += "and P.isend=@isend ";
                    dynamicParams.Add("@isend", "Y");
                }
                if (wkshopcode != "" && wkshopcode != null)  
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and K.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
@@ -353,7 +355,7 @@
        #endregion

        #region[班组工资报表记录导出]
        public static ToMessage GroupSalaryReportExcelSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
        public static ToMessage GroupSalaryReportExcelSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -455,7 +457,7 @@


        #region[人员工资明细报表]
        public static ToMessage PeopleSalaryReportSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec,string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
        public static ToMessage PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -578,7 +580,7 @@
        #endregion

        #region[人员工资明细报表导出]
        public static ToMessage PeopleSalaryReportSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
        public static ToMessage PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -1382,11 +1384,6 @@
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
@@ -1468,11 +1465,6 @@
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
@@ -1542,11 +1534,6 @@
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
@@ -1610,11 +1597,6 @@
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
@@ -1645,5 +1627,309 @@
            return mes;
        }
        #endregion


        #region[入库记录]
        public static ToMessage StorageRecordSearch(string hbillno, string sono, string mono, string wocode, string partcode, string partname, string stockcode, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (hbillno != "" && hbillno != null)
                {
                    search += "and AA.hbillno like '%'+@hbillno+'%' ";
                    dynamicParams.Add("@hbillno", hbillno);
                }
                if (sono != "" && sono != null)
                {
                    search += "and AA.salecode like '%'+@sono+'%' ";
                    dynamicParams.Add("@sono", sono);
                }
                if (mono != "" && mono != null)
                {
                    search += "and AA.mo_no like '%'+@mono+'%' ";
                    dynamicParams.Add("@mono", mono);
                }
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wocode like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode  like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname  like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (stockcode != "" && stockcode != null)
                {
                    search += "and AA.stockcode=@stockcode ";
                    dynamicParams.Add("@stockcode", stockcode);
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and AA.hbdate between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select *  from(
                           select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
                           B.qty,B.inbarcode,R.step_code,E.stepname,R.style,A.create_user,A.create_date  
                           from TK_WMS_Inwh_Main A
                           inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
                           inner join TK_Wrk_Record R on B.inbarcode=R.inbarcode
                           left join  TK_Wrk_Man M on B.wocode=M.wo_code
                           left join  TMateriel_Info P on B.partcode=P.partcode
                           left join  TSecStck S on B.stockcode=S.code
                           left join  TStep E on R.step_code=E.stepcode
                           left join  TOrganization T on M.wkshp_code=T.torg_code
                           union all
                           select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
                           B.qty,B.inbarcode,O.step_code,E.stepname,O.style,A.create_user,A.create_date  
                           from TK_WMS_Inwh_Main A
                           inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
                           inner join TK_Wrk_OutRecord O on B.inbarcode=O.inbarcode
                           left join  TK_Wrk_Man M on B.wocode=M.wo_code
                           left join  TMateriel_Info P on B.partcode=P.partcode
                           left join  TSecStck S on B.stockcode=S.code
                           left join  TStep E on O.step_code=E.stepcode
                           left join  TOrganization T on M.wkshp_code=T.torg_code
                           ) as AA where 1=1 " + 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 DeleteStorageRecord(List<StorageRecord> json, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                // 使用LINQ的lambda表达式根据hbillno分组并统计每个组的条数  
                var groupedRecords = json.GroupBy(r => r.hbillno).Select(g => new{hbillno = g.Key,count = g.Count()}).ToList();
                for (int i = 0; i < groupedRecords.Count; i++)
                {
                    //查询入库单子表的数据条数
                    sql = @"select *  from TK_WMS_Inwh_Sub where hbillno=@hbillno";
                    dynamicParams.Add("@hbillno", groupedRecords[i].hbillno);
                    var data = DapperHelper.selectdata(sql, dynamicParams);
                    //子表有对应入库单号数据且数据条数等于提交的入库条数
                    if (data.Rows.Count > 0 && data.Rows.Count == groupedRecords[i].count)
                    {
                        //删除入库单主表
                        sql = @"delete  TK_WMS_Inwh_Main  where hbillno=@hbillno";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                hbillno = groupedRecords[i].hbillno.ToString()
                            }
                        });
                    }
                }

                for (int i = 0; i < json.Count; i++)
                {
                    //删除入库单子表
                    sql = @"delete  TK_WMS_Inwh_Sub  where hbillno=@hbillno  and inbarcode=@inbarcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            hbillno = json[i].hbillno.ToString(),
                            inbarcode = json[i].inbarcode.ToString()
                        }
                    });
                    if (json[i].style == "B") 
                    {
                        //自制报工记录表入库数量
                        sql = @"update TK_Wrk_Record set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                qty = json[i].qty.ToString(),
                                inbarcode = json[i].inbarcode.ToString()
                            }
                        });
                    }
                    if (json[i].style == "S")
                    {
                        //外协收料记录表入库数量
                        sql = @"update TK_Wrk_OutRecord set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                qty = json[i].qty.ToString(),
                                inbarcode = json[i].inbarcode.ToString()
                            }
                        });
                    }
                    //清除工序任务表累计入库数量
                    sql = @"update TK_Wrk_Step set inhouseqty=inhouseqty-@qty where wo_code=@wocode and step_code=@stepcode and isend='Y'";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            qty = json[i].qty.ToString(),
                            wocode = json[i].wocode.ToString(),
                            stepcode = json[i].step_code.ToString()
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                LogHelper.WriteLogData(aa.ToString());
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了入库单:"+ string.Join(",", json.Select(r => $"{r.hbillno},{r.inbarcode}")), us.usertype);
                    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 StorageRecordExcelSearch(string hbillno, string sono, string mono, string wocode, string partcode, string partname, string stockcode, string respondopendate, string respondclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (hbillno != "" && hbillno != null)
                {
                    search += "and AA.hbillno like '%'+@hbillno+'%' ";
                    dynamicParams.Add("@hbillno", hbillno);
                }
                if (sono != "" && sono != null)
                {
                    search += "and AA.salecode like '%'+@sono+'%' ";
                    dynamicParams.Add("@sono", sono);
                }
                if (mono != "" && mono != null)
                {
                    search += "and AA.mo_no like '%'+@mono+'%' ";
                    dynamicParams.Add("@mono", mono);
                }
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wocode like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode  like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname  like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (stockcode != "" && stockcode != null)
                {
                    search += "and AA.stockcode=@stockcode ";
                    dynamicParams.Add("@stockcode", stockcode);
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and A.hbdate between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.hbillno as 入库单号,AA.hbdate as 单据日期,AA.salecode as 销售单号,AA.mo_no as 订单号,AA.wocode as 工单号,AA.partcode as 产品编码,AA.partname as 产品名称,
                            AA.partspec as 产品规格,AA.wkshp_code as 车间编码,AA.torg_name as 车间名称,AA.stockcode as 仓库编码,AA.stockname as 仓库名称, AA.qty as 入库数量,
                            AA.step_code as 工序编码,AA.stepname as 工序名称,AA.create_user as 创建人员,AA.create_date as 创建时间   
                           from(
                           select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name,B.stockcode,S.name as stockname,
                           B.qty,B.inbarcode,R.step_code,E.stepname,R.style,A.create_user,A.create_date  
                           from TK_WMS_Inwh_Main A
                           inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
                           inner join TK_Wrk_Record R on B.inbarcode=R.inbarcode
                           left join  TK_Wrk_Man M on B.wocode=M.wo_code
                           left join  TMateriel_Info P on B.partcode=P.partcode
                           left join  TSecStck S on B.stockcode=S.code
                           left join  TStep E on R.step_code=E.stepcode
                           left join  TOrganization T on M.wkshp_code=T.torg_code
                           union all
                           select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
                           B.qty,B.inbarcode,O.step_code,E.stepname,O.style,A.create_user,A.create_date  
                           from TK_WMS_Inwh_Main A
                           inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
                           inner join TK_Wrk_OutRecord O on B.inbarcode=O.inbarcode
                           left join  TK_Wrk_Man M on B.wocode=M.wo_code
                           left join  TMateriel_Info P on B.partcode=P.partcode
                           left join  TSecStck S on B.stockcode=S.code
                           left join  TStep E on O.step_code=E.stepcode
                           left join  TOrganization T on M.wkshp_code=T.torg_code
                           ) as AA where 1=1 " + 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
    }
}