Estsh-Yiri/Estsh.Core.Wms/Repositories/JisShippingRepository.cs

using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;
using System.Data.SqlTypes;

namespace Estsh.Core.Wms.Repositories
{
    public class JisShippingRepository : BaseRepository<MesJisShipping>, IJisShippingRepository
    {
        public JisShippingRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 查询成品发运接口
        /// </summary>
        /// <returns></returns>
        public List<MesWmsJisShipping> GetFinishedShippedInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT top 3000 * FROM MES_WMS_JIS_SHIPPING (nolock) ");
                sql.Append("WHERE  syn_flg = 10 "); //未同步：10  已同步：20
                sql.Append("ORDER BY create_time  ");

                return dbConn.Query<MesWmsJisShipping>(sql.ToString()).ToList();
            }
        }

        /// <summary>
        /// 处理成品发运接口
        /// </summary>
        /// <param name="mesWmsJisShippings"></param>
        /// <returns></returns>
        public bool InsertFinishedShippedInfo(List<MesWmsJisShipping> mesWmsJisShippings)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT * from sys_part (nolock) ");
                List<SysPart> sysPartsAll = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT * from sys_pdline (nolock) ");
                List<SysPdline> sysPdlinesAll = dbConn.Query<SysPdline>(stringBuilder.ToString()).ToList();

                StringBuilder factoryStringBuilder = new StringBuilder(1024);
                factoryStringBuilder.Append("select * from sys_factory (nolock) ");//工厂代码获取工厂ID
                List<SysFactory> sysFactoryAll = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();

                MesWmsJisShipping mesWmsJis = new MesWmsJisShipping();
                for (int i = 0; i < mesWmsJisShippings.Count; i++)
                {
                    List<string> sqlList = new List<string>();
                    List<DynamicParameters> parametersList = new List<DynamicParameters>();
                    mesWmsJis = mesWmsJisShippings[i];

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    DynamicParameters parameters = new DynamicParameters();

                    List<SysPart> sysParts = sysPartsAll.Where(a => a.PartNo == mesWmsJis.PartNo).ToList();//零件号ID

                    List<SysPdline> sysPdlines = sysPdlinesAll.Where(a => a.PdlineCode == mesWmsJis.PdlineCode).ToList();//产线

                    List<SysFactory> sysFactory = sysFactoryAll.Where(a => a.FactoryCode == mesWmsJis.FactoryCode).ToList();//工厂

                    stringBuilder = new StringBuilder();
                    stringBuilder.Append(" SELECT * from mes_jis_shipping (nolock) WHERE pdline_code='" + mesWmsJis.PdlineCode + "' and part_no='" + mesWmsJis.PartNo + "' and serial_number='" + mesWmsJis.SerialNumber + "'");
                    List<MesJisShipping> mesJisShippings = dbConn.Query<MesJisShipping>(stringBuilder.ToString()).ToList();
                    if (mesJisShippings.Count > 0)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("UPDATE dbo.mes_jis_shipping ");
                        SqlStringBuilder.Append("   SET serial_number = @serialNumber ");
                        SqlStringBuilder.Append("      ,qty += @qty ,ship_status = @shipStatus ");
                        SqlStringBuilder.Append("      ,erp_warehouse = @erpWarehouse ,erp_status = @erpStatus ");
                        SqlStringBuilder.Append("      ,customer_id = @customerId ,customer_code = @customerCode ");
                        SqlStringBuilder.Append("      ,factory_id = @factoryId ,factory_code = @factoryCode ");
                        SqlStringBuilder.Append("      ,enabled = @enabled ,update_userid = @updateUserid ");
                        SqlStringBuilder.Append("      ,update_time = @updateTime,order_no=@order_no ");
                        SqlStringBuilder.Append(" WHERE guid = @guid ");

                        sqlList.Add(SqlStringBuilder.ToString());
                        parameters.Add("@serialNumber", mesWmsJis.SerialNumber);//产品条码
                        parameters.Add("@qty", mesWmsJis.Qty);//数量
                        parameters.Add("@order_no", mesWmsJis.OrderNo);//数量
                        parameters.Add("@erpWarehouse", "");//ERP库存地
                        parameters.Add("@shipStatus", "10");//
                        parameters.Add("@erpStatus", "10");//
                        parameters.Add("@errMsg", "");//

                        StringBuilder customerStringBuilder = new StringBuilder(1024);
                        customerStringBuilder.Append("select * from sys_customer (nolock) where customer_code='" + mesWmsJis.CustomerCode + "'");//客户信息
                        List<SysCustomer> sysCustomers = dbConn.Query<SysCustomer>(customerStringBuilder.ToString()).ToList();
                        if (sysCustomers.Count == 0)
                        {
                            parameters.Add("@customerId", 0);//客户ID
                        }
                        else
                        {
                            parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID
                        }
                        parameters.Add("@customerCode", mesWmsJis.CustomerCode);//客户代码

                        if (sysFactory.Count == 0)
                        {
                            parameters.Add("@factoryId", 0);//工厂ID
                        }
                        else
                        {
                            parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                        }
                        parameters.Add("@factoryCode", mesWmsJis.FactoryCode);//工厂代码
                        parameters.Add("@enabled", 'Y');//启用
                        parameters.Add("@updateUserid", 0);//修改人
                        parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//修改日期
                        parameters.Add("@GUID", mesJisShippings[0].Guid);//GUID

                        parametersList.Add(parameters);

                    }
                    else
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.mes_jis_shipping (pdline_id,pdline_code,part_id,part_no,part_spec ");
                        SqlStringBuilder.Append("           ,serial_number,qty,ship_status,erp_warehouse,erp_status,customer_id,customer_code ");
                        SqlStringBuilder.Append("           ,err_msg,factory_id,factory_code,enabled,create_userid,create_time,order_no) ");
                        SqlStringBuilder.Append("     VALUES(@pdlineId,@pdlineCode,@partId,@partNo,@partSpec,@serialNumber,@qty,@shipStatus ");
                        SqlStringBuilder.Append("           ,@erpWarehouse,@erpStatus,@customerId,@customerCode,@errMsg,@factoryId ");
                        SqlStringBuilder.Append("           ,@factoryCode,@enabled,@createUserid,@createTime,@order_no) ");

                        sqlList.Add(SqlStringBuilder.ToString());
                        if (sysPdlines.Count > 0)
                        {
                            parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id
                            parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码
                        }
                        else
                        {
                            parameters.Add("@pdlineId", 0);//生产线id
                            parameters.Add("@pdlineCode", mesWmsJis.PdlineCode);//生产线代码
                        }

                        if (sysParts.Count > 0)
                        {
                            parameters.Add("@partId", sysParts[0].PartId);//零件号ID
                            parameters.Add("@partNo", sysParts[0].PartNo);//零件号
                            parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
                        }
                        else
                        {
                            parameters.Add("@partId", 0);//零件号ID
                            parameters.Add("@partNo", mesWmsJis.PartNo);//零件号
                            parameters.Add("@partSpec", "");//简码
                        }

                        parameters.Add("@serialNumber", mesWmsJis.SerialNumber);//产品条码
                        parameters.Add("@qty", mesWmsJis.Qty);//数量
                        parameters.Add("@order_no", mesWmsJis.OrderNo);//发运单号
                        parameters.Add("@erpWarehouse", "");//ERP库存地
                        parameters.Add("@shipStatus", "10");//
                        parameters.Add("@erpStatus", "10");//
                        parameters.Add("@errMsg", "");//

                        StringBuilder customerStringBuilder = new StringBuilder(1024);
                        customerStringBuilder.Append("select * from sys_customer (nolock) where customer_code='" + mesWmsJis.CustomerCode + "'");//客户信息
                        List<SysCustomer> sysCustomers = dbConn.Query<SysCustomer>(customerStringBuilder.ToString()).ToList();
                        if (sysCustomers.Count == 0)
                        {
                            parameters.Add("@customerId", 0);//客户ID
                        }
                        else
                        {
                            parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID
                        }
                        parameters.Add("@customerCode", mesWmsJis.CustomerCode);//客户代码

                        if (sysFactory.Count == 0)
                        {
                            parameters.Add("@factoryId", 0);//工厂ID
                        }
                        else
                        {
                            parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                        }
                        parameters.Add("@factoryCode", mesWmsJis.FactoryCode);//工厂代码
                        parameters.Add("@enabled", 'Y');//启用
                        parameters.Add("@createUserid", 0);//创建人
                        parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期
                        parametersList.Add(parameters);
                    }

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("update MES_WMS_JIS_SHIPPING set syn_flg=@synFlg,syn_time=@synTime,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
                    sqlList.Add(SqlStringBuilder.ToString());

                    parameters = new DynamicParameters();
                    parameters.Add("@GUID", mesWmsJis.Guid);//ID
                    parameters.Add("@synFlg", "20");//已同步
                    parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                    parameters.Add("@updateUserid", 0);//更新人
                    parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间
                    parametersList.Add(parameters);

                    bool staResult = ExecuteTransaction(sqlList, parametersList);
                    if (staResult == false)
                    {
                        sqlList = new List<string>();
                        parametersList = new List<DynamicParameters>();
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("update MES_WMS_JIS_SHIPPING set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
                        sqlList.Add(SqlStringBuilder.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@GUID", mesWmsJis.Guid);//ID
                        parameters.Add("@synFlg", "30");//同步异常
                        parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                        parameters.Add("@errMsg", "同步错误，请检查接口数据！");//错误
                        parameters.Add("@updateUserid", 0);//更新人
                        parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间
                        parametersList.Add(parameters);
                        ExecuteTransaction(sqlList, parametersList);
                        return false;
                    }
                }
                return true;
            }
        }

        /// <summary>
        /// 获取待Jis发运的数据
        /// </summary>
        /// <returns></returns>
        public List<MesJisShipping> GetJisShippingInfo()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    if (dbConn.State == ConnectionState.Closed)
            //    {
            //        dbConn.Open();
            //    }

            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT top 1000 * FROM mes_jis_shipping (readpast) ");
            sql.Append("WHERE ship_status = @shipStatus ");
            sql.Append("      AND enabled = 'Y' ");
            sql.Append("ORDER BY create_time  ");


            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@shipStatus", (int)MesEnumUtil.JisShipStatus.INIT);

            return dbConn.Query<MesJisShipping>(sql.ToString(), parameters).ToList();
            //}
        }

        /// <summary>
        /// 更新待Jis发运的数据
        /// </summary>
        /// <param name="outPdline"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpdateJisShippingInfo(MesJisShipping jisShip,
            MesEnumUtil.JisShipStatus status, string msg = "")
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    if (dbConn.State == ConnectionState.Closed)
            //    {
            //        dbConn.Open();
            //    }

            StringBuilder sql = new StringBuilder(1024);
            sql.Append("UPDATE dbo.mes_jis_shipping ");
            sql.Append("SET ship_status = @shipStatus, ");
            sql.Append("    err_msg = @errMsg, ");
            sql.Append("    update_userid = @updateUserid, ");
            sql.Append("    update_time = @updateTime ");
            sql.Append("WHERE serial_number = @serialNumber ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@shipStatus", status);
            parameters.Add("@errMsg", msg);
            parameters.Add("@updateUserid", 0);
            parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            parameters.Add("@serialNumber", jisShip.SerialNumber);

            return dbConn.Execute(sql.ToString(), parameters);
            //}
        }

        /// <summary>
        /// 处理JIS发运
        /// </summary>
        /// <param name="outPdline"></param>
        /// <param name="bomDetails"></param>
        /// <param name="pdline"></param>
        /// <returns></returns>
        public bool DoJisShip(List<MesJisShipping> jisShips, List<SysStock> cartonInfos)
        {
            List<string> sqlList = new List<string>();
            List<DynamicParameters> parametersList = new List<DynamicParameters>();
            StringBuilder sql = new StringBuilder(1024);
            DynamicParameters parameters;

            foreach (var cartonInfo in cartonInfos)
            {
                //更改条码表状态
                sql.Remove(0, sql.Length);
                sql.Append("UPDATE sys_stock ");
                sql.Append("SET status = @status, ");
                sql.Append("    locate_id = @locateId, ");
                sql.Append("    locate_name = @locateName, ");
                sql.Append("    zone_id = @zoneId, ");
                sql.Append("    zone_name = @zoneName, ");
                sql.Append("    warehouse_id = @warehouseId, ");
                sql.Append("    warehouse_name = @warehouseName, ");
                sql.Append("    erp_warehouse = @erpWarehouse, ");
                sql.Append("    update_userid = @updateUserid, ");
                sql.Append("    update_time = @updateTime ");
                sql.Append("WHERE carton_no = @cartonNo ");

                parameters = new DynamicParameters();
                parameters.Add("@cartonNo", cartonInfo.CartonNo);
                parameters.Add("@status", WmsEnumUtil.StockStatus.SHIPPED);
                parameters.Add("@locateId", 0);
                parameters.Add("@locateName", string.Empty);
                parameters.Add("@zoneId", 0);
                parameters.Add("@zoneName", string.Empty);
                parameters.Add("@warehouseId", 0);
                parameters.Add("@warehouseName", string.Empty);
                parameters.Add("@erpWarehouse", string.Empty);
                parameters.Add("@updateUserid", 0);
                parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));

                sqlList.Add(sql.ToString());
                parametersList.Add(parameters);
                // 2、更新事务表

                //新增 sys_stock_trans 事务表
                sql.Remove(0, sql.Length);
                sql.Append("INSERT INTO [dbo].[sys_stock_trans] ");
                sql.Append("( ");
                sql.Append("    [trans_code],[carton_no],[part_id],[part_no],[part_spec],[src_locate_id],[src_locate_name],[dest_locate_id], ");
                sql.Append("    [dest_locate_name],[old_qty],[new_qty],[trans_qty],[old_status],[new_status],[unit], ");
                sql.Append("    [factory_id],[factory_code],[src_erp_warehouse],[dest_erp_warehouse],[src_warehouse_id],[src_warehouse_name], ");
                sql.Append("    [dest_warehouse_id],[dest_warehouse_name],[src_zone_id],[src_zone_name],[dest_zone_id],[dest_zone_name],[enabled], ");
                sql.Append("    [update_userid],[update_time], [create_userid],[create_time],[guid],ref_order_no ");
                sql.Append(") ");
                sql.Append("VALUES ");
                sql.Append("(@trans_code, @carton_no, @part_id, @part_no, @part_spec, @src_locate_id, @src_locate_name, @dest_locate_id, ");
                sql.Append(" @dest_locate_name, @old_qty, @new_qty, @trans_qty, @old_status, @new_status,  @unit, ");
                sql.Append(" @factory_id, @factory_code, @src_erp_warehouse, @dest_erp_warehouse, @src_warehouse_id, @src_warehouse_name, ");
                sql.Append(" @dest_warehouse_id, @dest_warehouse_name, @src_zone_id, @src_zone_name, @dest_zone_id, @dest_zone_name, @enabled, ");
                sql.Append(" @update_userid, @update_time, @create_userid, @create_time, @guid,@ref_order_no) ");


                parameters = new DynamicParameters();
                parameters.Add("@trans_code", (int)WmsEnumUtil.TransType.JIS_OUTSTOCK);
                parameters.Add("@carton_no", cartonInfo.CartonNo);
                parameters.Add("@part_id", cartonInfo.PartId);
                parameters.Add("@part_no", cartonInfo.PartNo);
                parameters.Add("@part_spec", cartonInfo.PartSpec);
                parameters.Add("@src_locate_id", cartonInfo.LocateId);
                parameters.Add("@src_locate_name", cartonInfo.LocateName);
                parameters.Add("@dest_locate_id", 0);
                parameters.Add("@dest_locate_name", string.Empty);
                parameters.Add("@old_qty", cartonInfo.Qty);
                parameters.Add("@new_qty", cartonInfo.Qty);
                parameters.Add("@trans_qty", cartonInfo.Qty);
                parameters.Add("@old_status", cartonInfo.Status);
                parameters.Add("@new_status", (int)WmsEnumUtil.StockStatus.SHIPPED);
                parameters.Add("@unit", cartonInfo.Unit);
                parameters.Add("@factory_id", cartonInfo.FactoryId);
                parameters.Add("@factory_code", cartonInfo.FactoryCode);
                parameters.Add("@src_erp_warehouse", cartonInfo.ErpWarehouse);
                parameters.Add("@dest_erp_warehouse", string.Empty);
                parameters.Add("@src_warehouse_id", 0);
                parameters.Add("@src_warehouse_name", string.Empty);
                parameters.Add("@dest_warehouse_id", 0);
                parameters.Add("@dest_warehouse_name", string.Empty);
                parameters.Add("@src_zone_id", cartonInfo.ZoneId);
                parameters.Add("@src_zone_name", cartonInfo.ZoneName);
                parameters.Add("@dest_zone_id", 0);
                parameters.Add("@dest_zone_name", string.Empty);
                parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString());
                parameters.Add("@update_userid", 0);
                parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                parameters.Add("@create_userid", 0);
                parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                parameters.Add("@guid", Guid.NewGuid());
                parameters.Add("@ref_order_no", jisShips[0].OrderNo);

                sqlList.Add(sql.ToString());
                parametersList.Add(parameters);
                // 3、更新待扣减表。
                sql.Remove(0, sql.Length);
                sql.Append("UPDATE dbo.mes_jis_shipping ");
                sql.Append("SET ship_status = @shipStatus, ");
                sql.Append("    err_msg = '', ");
                sql.Append("    erp_warehouse = @erpWarehouse, ");
                sql.Append("    update_userid = @updateUserid, ");
                sql.Append("    update_time = @updateTime ");
                sql.Append("WHERE serial_number = @serialNumber ");

                parameters = new DynamicParameters();
                parameters.Add("@shipStatus", (int)MesEnumUtil.JisShipStatus.SHIPPED);
                parameters.Add("@erpWarehouse", cartonInfo.ErpWarehouse);
                parameters.Add("@updateUserid", 0);
                parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                parameters.Add("@serialNumber", cartonInfo.CartonNo);// jisShip.SerialNumber);

                sqlList.Add(sql.ToString());
                parametersList.Add(parameters);

            }

            foreach (var jisShip in jisShips)
            {
                // 4、写入JIS发运接口表
                //WMS将每次发运的信息传递给SAP,SAP用于创建交货单。 JIS
                sql.Remove(0, sql.Length);
                sql.Append("INSERT INTO dbo.WMS_JIS(ZZ_WMSNUM,WERKS,LGORT,LFDAT,KUNNR,MATNR ");
                sql.Append("           ,LFIMG,MEINS,ZPOST,ZTIME,SID,RECTIM,SYNFLG,GUID) ");
                sql.Append("     VALUES(@ZZ_WMSNUM,@WERKS,@LGORT,@LFDAT,@KUNNR,@MATNR,@LFIMG ");
                sql.Append("           ,@MEINS,@ZPOST,@ZTIME,@SID,@RECTIM,@SYNFLG,@GUID) ");

                parameters = new DynamicParameters();
                parameters.Add("@ZZ_WMSNUM", jisShip.OrderNo);//WMS发运单据号
                parameters.Add("@WERKS", jisShip.FactoryCode);//发货工厂
                parameters.Add("@LGORT", jisShip.ErpWarehouse);//发货库存地
                parameters.Add("@LFDAT", DateTime.Now.ToString("yyyyMMdd"));//交货日期
                parameters.Add("@KUNNR", jisShip.CustomerCode);//客户编码（送达方）
                parameters.Add("@MATNR", jisShip.PartNo);//物料号
                parameters.Add("@LFIMG", jisShip.Qty);//交货数量
                parameters.Add("@MEINS", "件");//计量单位
                parameters.Add("@ZPOST", DateTime.Now.ToString("yyyyMMdd"));//交易日期
                parameters.Add("@ZTIME", DateTime.Now.ToString("HHmmss"));//交易时间
                parameters.Add("@SID", Guid.NewGuid().ToString().Replace("-", ""));
                parameters.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                parameters.Add("@SYNFLG", "N");
                parameters.Add("@GUID", Guid.NewGuid().ToString());

                sqlList.Add(sql.ToString());
                parametersList.Add(parameters); 
            }

            return this.ExecuteTransaction(sqlList, parametersList);
        }

    }
}