using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System.Collections;
using System.Data;
using System.Text;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// ERP接口
    /// </summary>
    public class ERPInterfaceRepository : BaseRepository<BaseEntity>, IERPInterfaceRepository
    {
        public ERPInterfaceRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 从SAP传递物料基本信息到WMS
        /// </summary>
        /// <param name="sapPt"></param>
        /// <returns></returns>
        public WmsInterfaceResult MaterialMasterMethod(string sid, List<SapPtInterface> sapPts)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapPts));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);


                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapPts.Count; i++)
                    {
                        SapPtInterface sapPt = sapPts[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_PT(MATNR,MAKTX,MTART,MTBEZ,MATKL,WGBEZ,MEINS ");
                        SqlStringBuilder.Append("           ,MAABC,BSTRF,WERKS,LVORM,SPART,ZZPPMATNR ");
                        SqlStringBuilder.Append("           ,SID,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@MATNR,@MAKTX,@MTART,@MTBEZ,@MATKL,@WGBEZ,@MEINS,@MAABC ");
                        SqlStringBuilder.Append("           ,@BSTRF,@WERKS,@LVORM,@SPART,@ZZPPMATNR,'" + sid + "' ");
                        SqlStringBuilder.Append("           ,'N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@MATNR", sapPt.Matnr);
                        dynamic.Add("@MAKTX", sapPt.Maktx);
                        dynamic.Add("@MTART", sapPt.Mtart);
                        dynamic.Add("@MTBEZ", sapPt.Mtbez);
                        dynamic.Add("@MATKL", sapPt.Matkl);
                        dynamic.Add("@WGBEZ", sapPt.Wgbez);
                        dynamic.Add("@MEINS", sapPt.Meins);
                        dynamic.Add("@MAABC", sapPt.Maabc);
                        dynamic.Add("@BSTRF", sapPt.Bstrf);
                        dynamic.Add("@WERKS", sapPt.Werks);
                        dynamic.Add("@LVORM", sapPt.Lvorm);
                        dynamic.Add("@SPART", sapPt.Spart);
                        dynamic.Add("@ZZPPMATNR", sapPt.Zzppmatnr);
                        parameters.Add(dynamic);
                    }
                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.Message.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// 从SAP传递供应商基本信息到WMS
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult SupplierMasterMethod(string sid, List<SapSuInterface> sapSus)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapSus));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapSus.Count; i++)
                    {
                        SapSuInterface sapSu = sapSus[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_SU(LIFNR,NAME1,STRAS,TELF1,PSTL2,NODEL ");
                        SqlStringBuilder.Append("           ,SYNFLG,ACTFLG,STAFLG,SID) ");
                        SqlStringBuilder.Append("     VALUES(@LIFNR,@NAME1,@STRAS,@TELF1,@PSTL2,@NODEL,'N' ");
                        SqlStringBuilder.Append("           ,'N','N','" + sid + "') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@LIFNR", sapSu.LIFNR);
                        dynamic.Add("@NAME1", sapSu.NAME1);
                        dynamic.Add("@STRAS", sapSu.STRAS);
                        dynamic.Add("@TELF1", sapSu.TELF1);
                        dynamic.Add("@PSTL2", sapSu.PSTL2);
                        dynamic.Add("@NODEL", sapSu.NODEL);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// 从SAP传递客户基本信息到WMS
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult CustomerMasterMethod(string sid, List<SapCuInterface> sapCus)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapCus));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapCus.Count; i++)
                    {
                        SapCuInterface sapCu = sapCus[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_CU(KUNNR,NAME1,STRAS,TELF1,PSTL2,NODEL,SID ");
                        SqlStringBuilder.Append("           ,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@KUNNR,@NAME1,@STRAS,@TELF1,@PSTL2,@NODEL,'" + sid + "' ");
                        SqlStringBuilder.Append("           ,'N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@KUNNR", sapCu.Kunnr);
                        dynamic.Add("@NAME1", sapCu.Name1);
                        dynamic.Add("@STRAS", sapCu.Stras);
                        dynamic.Add("@TELF1", sapCu.Telf1);
                        dynamic.Add("@PSTL2", sapCu.Pstl2);
                        dynamic.Add("@NODEL", sapCu.Nodel);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// 从SAP传递物料号与客户物料号对照信息到WMS
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult CustomerItemMasterMethod(string sid, List<SapPtcptInterface> sapPtcpts)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapPtcpts));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapPtcpts.Count; i++)
                    {
                        SapPtcptInterface sapPtcpt = sapPtcpts[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_PTCPT(KUNNR,MATNR,KDMAT,ZZ_TYPE,SID,SYNFLG ");
                        SqlStringBuilder.Append("           ,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@KUNNR,@MATNR,@KDMAT,@ZZTYPE,'" + sid + "','N' ");
                        SqlStringBuilder.Append("           ,'N','N') ");

                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@KUNNR", sapPtcpt.Kunnr);
                        dynamic.Add("@MATNR", sapPtcpt.Matnr);
                        dynamic.Add("@KDMAT", sapPtcpt.Kdmat);
                        dynamic.Add("@ZZTYPE", sapPtcpt.ZzType);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// MBOM同步到MES系统
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult BomMasterMethod(string sid, List<SapBomInterface> sapBoms)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapBoms));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapBoms.Count; i++)
                    {
                        SapBomInterface sapBom = sapBoms[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_BOM(WERKS,MATNR,BMENG,BMEIN,IDNRK,MENGE,MEINS,DATUV,DATUS ");
                        SqlStringBuilder.Append("           ,LKENZ,AENNR,SID,SYNFLG,ACTFLG,STAFLG,STLKZ) ");
                        SqlStringBuilder.Append("     VALUES(@WERKS,@MATNR,@BMENG,@BMEIN,@IDNRK,@MENGE,@MEINS,@DATUV,@DATUS,@LKENZ ");
                        SqlStringBuilder.Append("           ,@AENNR,'" + sid + "','N','N','N',@STLKZ) ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@WERKS", sapBom.Werks);
                        dynamic.Add("@MATNR", sapBom.Matnr);
                        dynamic.Add("@BMENG", sapBom.Bmeng);
                        dynamic.Add("@BMEIN", sapBom.Bmein);
                        dynamic.Add("@IDNRK", sapBom.Idnrk);
                        dynamic.Add("@MENGE", sapBom.Menge);
                        dynamic.Add("@MEINS", sapBom.Meins);
                        dynamic.Add("@DATUV", sapBom.Datuv);
                        dynamic.Add("@DATUS", sapBom.Datus);
                        dynamic.Add("@LKENZ", sapBom.Lkenz);
                        dynamic.Add("@AENNR", sapBom.Aennr);
                        dynamic.Add("@STLKZ", sapBom.Stlkz);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// SAP系统把ASN传递到WMS用于采购收货
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult ASNDataMethod(string sid, List<SapAsnInterface> sapAsns)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapAsns));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapAsns.Count; i++)
                    {
                        SapAsnInterface sapAsn = sapAsns[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_ASN(ZASNNO,LIFNR,ZRNUM,MATNR,ZCN_NUM,UNIT,PSTYP,ZDATE,ZCJSJ ");
                        SqlStringBuilder.Append("           ,WERKS,LGORT,ZSTAS,ZTXM,SID,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@ZASNNO,@LIFNR,@ZRNUM,@MATNR,@ZCNNUM,@UNIT,@PSTYP,@ZDATE,@ZCJSJ,@WERKS ");
                        SqlStringBuilder.Append("           ,@LGORT,@ZSTAS,@ZTXM,'" + sid + "','N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@ZASNNO", sapAsn.Zasnno);
                        dynamic.Add("@LIFNR", sapAsn.Lifnr);
                        dynamic.Add("@ZRNUM", sapAsn.Zrnum);
                        dynamic.Add("@MATNR", sapAsn.Matnr);
                        dynamic.Add("@ZCNNUM", sapAsn.ZCN_NUM);
                        dynamic.Add("@UNIT", sapAsn.Unit);
                        dynamic.Add("@PSTYP", sapAsn.Pstyp);
                        dynamic.Add("@ZDATE", sapAsn.Zdate);
                        dynamic.Add("@ZCJSJ", sapAsn.Zcjsj);
                        dynamic.Add("@WERKS", sapAsn.Werks);
                        dynamic.Add("@LGORT", sapAsn.Lgort);
                        dynamic.Add("@ZSTAS", sapAsn.Zstas);
                        dynamic.Add("@ZTXM", sapAsn.Ztxm);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// SAP系统把PO传递到WMS用于采购收货
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult PODataMethod(string sid, List<SapDspoInterface> sapDspos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapDspos));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapDspos.Count; i++)
                    {
                        SapDspoInterface sapDspo = sapDspos[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_DSPO(EBELN,EVRTP,LIFNR,MATNR,ZDEV_NUM,UNIT,PSTYP,ZDATE,ZCJSJ,WERKS ");
                        SqlStringBuilder.Append("           ,LGORT,ZSTAS,BSTYP,BSTRF,SID,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@EBELN,@EVRTP,@LIFNR,@MATNR,@ZDEVNUM,@UNIT,@PSTYP,@ZDATE,@ZCJSJ,@WERKS,@LGORT,@ZSTAS ");
                        SqlStringBuilder.Append("           ,@BSTYP,@BSTRF,'" + sid + "','N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@EBELN", sapDspo.Ebeln);
                        dynamic.Add("@EVRTP", sapDspo.Evrtp);
                        dynamic.Add("@LIFNR", sapDspo.Lifnr);
                        dynamic.Add("@MATNR", sapDspo.Matnr);
                        dynamic.Add("@ZDEVNUM", sapDspo.Zdev_Num);
                        dynamic.Add("@UNIT", sapDspo.Unit);
                        dynamic.Add("@PSTYP", sapDspo.Pstyp);
                        dynamic.Add("@ZDATE", sapDspo.Zdate);
                        dynamic.Add("@ZCJSJ", sapDspo.Zcjsj);
                        dynamic.Add("@WERKS", sapDspo.Werks);
                        dynamic.Add("@LGORT", sapDspo.Lgort);
                        dynamic.Add("@ZSTAS", sapDspo.Zstas);
                        dynamic.Add("@BSTYP", sapDspo.Bstyp);
                        dynamic.Add("@BSTRF", sapDspo.Bstrf);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// SAP系统将一定期间内待发运交货单信息发布到WMS用于后续捡配发货
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult SalesAndDeliveryPlanMethod(string sid, List<SapDnInterface> sapDns)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapDns));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapDns.Count; i++)
                    {
                        SapDnInterface sapDn = sapDns[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_DN(VBELN,POSNR,WERKS,LGORT,LFDAT,KUNNR,MATNR,KDMAT,LFIMG,MEINS ");
                        SqlStringBuilder.Append("           ,SID,SYNFLG,ACTFLG,STAFLG,ZVBELN) ");
                        SqlStringBuilder.Append("     VALUES(@VBELN,@POSNR,@WERKS,@LGORT,@LFDAT,@KUNNR,@MATNR,@KDMAT,@LFIMG ");
                        SqlStringBuilder.Append("           ,@MEINS,'" + sid + "','N','N','N',@ZVBELN) ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@VBELN", sapDn.Vbeln);
                        dynamic.Add("@POSNR", sapDn.Posnr);
                        dynamic.Add("@WERKS", sapDn.Werks);
                        dynamic.Add("@LGORT", sapDn.Lgort);
                        dynamic.Add("@LFDAT", sapDn.Lfdat);
                        dynamic.Add("@KUNNR", sapDn.Kunnr);
                        dynamic.Add("@MATNR", sapDn.Matnr);
                        dynamic.Add("@KDMAT", sapDn.Kdmat);
                        dynamic.Add("@LFIMG", sapDn.Lfimg);
                        dynamic.Add("@MEINS", sapDn.Meins);
                        dynamic.Add("@ZVBELN", sapDn.Zvbeln);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// SAP把物料的零星出(入）指令传到WMS
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult SporadicEntryAndExitMethod(string sid, List<SapMiscoInterface> sapMiscos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapMiscos));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapMiscos.Count; i++)
                    {
                        SapMiscoInterface sapMisco = sapMiscos[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_MISCO(ZINSTNO,ZEILE,WERKS,LGORT,MATNR,ERFMG,ERFME,SOBKZ ");
                        SqlStringBuilder.Append("           ,HTEXT,ZZLZT,ZACTION,SID,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@ZINSTNO,@ZEILE,@WERKS,@LGORT,@MATNR,@ERFMG,@ERFME,@SOBKZ,@HTEXT ");
                        SqlStringBuilder.Append("           ,@ZZLZT,@ZACTION,'" + sid + "','N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@ZINSTNO", sapMisco.Zinstno);
                        dynamic.Add("@ZEILE", sapMisco.Zeile);
                        dynamic.Add("@WERKS", sapMisco.Werks);
                        dynamic.Add("@LGORT", sapMisco.Lgort);
                        dynamic.Add("@MATNR", sapMisco.Matnr);
                        dynamic.Add("@ERFMG", sapMisco.Erfmg);
                        dynamic.Add("@ERFME", sapMisco.Erfme);
                        dynamic.Add("@SOBKZ", sapMisco.Sobkz);
                        dynamic.Add("@HTEXT", sapMisco.Htext);
                        dynamic.Add("@ZZLZT", sapMisco.Zzlzt);
                        dynamic.Add("@ZACTION", sapMisco.Zaction);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// 盘点开始时，SAP冻结库存，并把盘点表传到WMS
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult InventoryListMethod(string sid, List<SapCycoInterface> sapCycos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapCycos));
                    IQdynamic.Add("@ACTFLG", 'N');

                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapCycos.Count; i++)
                    {
                        SapCycoInterface sapCyco = sapCycos[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_CYCO(IBLNR,ZEILI,MATNR,MEINS,WERKS,LGORT,ZSTAT,SOBKZ ");
                        SqlStringBuilder.Append("           ,GJAHR,SID,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@IBLNR,@ZEILI,@MATNR,@MEINS,@WERKS,@LGORT,@ZSTAT,@SOBKZ,@GJAHR ");
                        SqlStringBuilder.Append("           ,'" + sid + "','N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@IBLNR", sapCyco.Iblnr);
                        dynamic.Add("@ZEILI", sapCyco.Zeili);
                        dynamic.Add("@MATNR", sapCyco.Matnr);
                        dynamic.Add("@MEINS", sapCyco.Meins);
                        dynamic.Add("@WERKS", sapCyco.Werks);
                        dynamic.Add("@LGORT", sapCyco.Lgort);
                        dynamic.Add("@ZSTAT", sapCyco.Zstat);
                        dynamic.Add("@SOBKZ", sapCyco.Sobkz);
                        dynamic.Add("@GJAHR", sapCyco.Gjahr);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

        /// <summary>
        /// SAP把盘点差异转移到差异库的结果传到WMS，使得WMS可以根据盘点结果调整库存
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public WmsInterfaceResult InventoryDifferenceAdjustmentMethod(string sid, List<SapDifoInterface> sapDifos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                WmsInterfaceResult interfaceResult = new WmsInterfaceResult();
                try
                {
                    StringBuilder IQSqlStringBuilder = new StringBuilder(1024);
                    IQSqlStringBuilder.Append("INSERT INTO dbo.IQ(SID,RECTIM,XMLCON ");
                    IQSqlStringBuilder.Append("           ,ACTFLG) ");
                    IQSqlStringBuilder.Append("     VALUES(@SID,@RECTIM,@XMLCON ");
                    IQSqlStringBuilder.Append("           ,@ACTFLG) ");

                    DynamicParameters IQdynamic = new DynamicParameters();
                    IQdynamic.Add("@SID", sid);
                    IQdynamic.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    IQdynamic.Add("@XMLCON", Newtonsoft.Json.JsonConvert.SerializeObject(sapDifos));
                    IQdynamic.Add("@ACTFLG", 'N');
                    dbConn.Execute(IQSqlStringBuilder.ToString(), IQdynamic);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    List<string> sqlLists = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    for (int i = 0; i < sapDifos.Count; i++)
                    {
                        SapDifoInterface sapDifo = sapDifos[i];
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.SAP_DIFO(WERKS,IBLNR,ZEILI,ZTZBS,GJAHR,SID ");
                        SqlStringBuilder.Append("           ,SYNFLG,ACTFLG,STAFLG) ");
                        SqlStringBuilder.Append("     VALUES(@WERKS,@IBLNR,@ZEILI,@ZTZBS,@GJAHR,'" + sid + "','N','N','N') ");
                        sqlLists.Add(SqlStringBuilder.ToString());

                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@WERKS", sapDifo.Werks);
                        dynamic.Add("@IBLNR", sapDifo.Iblnr);
                        dynamic.Add("@ZEILI", sapDifo.Zeili);
                        dynamic.Add("@ZTZBS", sapDifo.Ztzbs);
                        dynamic.Add("@GJAHR", sapDifo.Gjahr);
                        parameters.Add(dynamic);
                    }

                    bool result = this.ExecuteTransaction(sqlLists, parameters);
                    if (result == true)
                    {
                        interfaceResult.success = true;
                        interfaceResult.message = "成功";
                    }
                    else
                    {
                        interfaceResult.success = false;
                        interfaceResult.message = "数据添加失败，请检查";
                    }
                }
                catch (Exception ex)
                {
                    interfaceResult.success = false;
                    interfaceResult.message = ex.ToString();
                }
                return interfaceResult;
            }
        }

    }
}