using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：采购周计划导入数据访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理采购周计划导入模块的业务数据
    /// </summary>
    public class PurchaseNoteRepository : BaseRepository<SysStock>, IPurchaseNoteRepository
    {
        public PurchaseNoteRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 返回服务日期及时间，返回时格式化
        /// </summary>
        /// <param name="flag">返回的类型</param>
        /// <returns></returns>
        public string GetServerDateTime(string flag)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                //default style
                string serverDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Replace("-", "/");

                switch (flag.ToUpper().Replace(" ", ""))
                {
                    case "YMD":
                        return serverDateTime.Substring(0, 10); //style:yyyy/MM/dd
                    case "HMS":
                        return serverDateTime.Substring(11);    //style:HH:mm:ss
                    case "YY":
                        return serverDateTime.Substring(2, 2);  //style:yy(年前两位)
                    case "YYYY":
                        return serverDateTime.Substring(0, 4);   //style:yyyy
                }
                return serverDateTime;
            }
        }

        #region Multilple sheet import DB operat method
        /// <summary>
        /// Query supplier base information
        /// </summary>
        /// <param name="supplierCode">supplier code</param>
        /// <returns></returns>
        public List<SysVendor> QuerySupplierInfo(string vendorCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sbSql = new StringBuilder(1024);
                sbSql.Remove(0, sbSql.Length);
                sbSql.Append("SELECT TOP 1 vendor_id,vendor_code,vendor_name,vendor_sale,vendor_tel");
                sbSql.Append(",vendor_fax,vendor_mtel,vendor_addr,vendor_mail,vendor_pwd,is_print,guid ");
                sbSql.Append(" FROM sys_vendor (NOLOCK) WHERE vendor_code=@vendorCode AND enabled='Y'");
                DynamicParameters param = new DynamicParameters();
                param.Add("@vendorCode", vendorCode);

                List<SysVendor> result = dbConn.Query<SysVendor>(sbSql.ToString(), param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 查询下单员信息
        /// </summary>
        /// <param name="orderName">上单员姓名</param>
        /// <returns></returns>
        public List<SysIssuser> QueryOrderInfo(string orderName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strSql = "SELECT TOP 1 iss_id,iss_addr,iss_name,iss_tel,iss_mtel,iss_fax,iss_mail,iss_no " +
                    " FROM sys_issuser (NOLOCK) WHERE iss_name=@issName AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@issName", orderName);

                List<SysIssuser> result = dbConn.Query<SysIssuser>(strSql, param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取服务器日期、时间
        /// </summary>
        /// <param name="flag">返回类型标志</param>
        /// <returns></returns>
        public string GetApDateTime(int flag)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS ap_datetime";
                if (flag > 0)
                {
                    sql = "SELECT CONVERT(VARCHAR(10),GETDATE(),108) AS ap_datetime";
                }
                List<dynamic> result = dbConn.Query<dynamic>(sql).ToList();
                if (result.Count < 1)
                {
                    return "";
                }

                return result[0].ap_datetime;
            }
        }

        /// <summary>
        /// 查询零件行ID
        /// </summary>
        /// <param name="partNo">零件号</param>
        /// <returns></returns>
        public string QueryPartId(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 part_id FROM sys_part (NOLOCK) WHERE part_no=@partNo AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@partNo", partNo);

                object obj = dbConn.ExecuteScalar(sql, param);

                if (obj == null)
                {
                    return string.Empty;
                }

                return obj.ToString();
            }
        }

        /// <summary>
        /// 查询零件标准包装
        /// </summary>
        /// <param name="partId">零件行ID</param>
        /// <returns></returns>
        public int QueryPartSnp(string partId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 default_box_qty FROM sys_part (NOLOCK) WHERE part_id=@partId AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@partId", partId);

                object obj = dbConn.ExecuteScalar(sql, param);

                if (obj == null)
                {
                    return -1;
                }

                int iReturn = 0;
                try
                {
                    iReturn = (int)Convert.ToDouble(obj.ToString());
                }
                catch
                {
                    return -1;
                }
                return iReturn;
            }
        }

        /// <summary>
        /// 查询项目ID
        /// </summary>
        /// <param name="itemNo">项目（车型)代码</param>
        /// <returns></returns>
        public string QueryItemId(string itemNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 type_id FROM sys_model_type (NOLOCK) WHERE REPLACE(type_name,' ','')=@item_no and enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@item_no", itemNo);

                object obj = dbConn.ExecuteScalar(sql, param);

                if (obj == null)
                {
                    return "";
                }
                return obj.ToString();
            }
        }

        /// <summary>
        /// 验证采购单是否匹配
        /// </summary>
        /// <param name="purNo">采购单号</param>
        /// <param name="partNo">零件号</param>
        /// <returns></returns>
        public bool ValidatePurNo(string purNo, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                //更新sys_base增加WMS接口程序是否开启状态（工厂如未开通，则不验证)
                string sql = "SELECT TOP 1 param_value FROM sys_base (NOLOCK) WHERE param_name='WMSInterfaceEnabled'";
                object obj = dbConn.ExecuteScalar(sql);
                if (obj != null && obj.ToString().ToUpper() == "Y")
                {
                    sql = "SELECT TOP 1 xw_pod_part FROM xw_pod_det(NOLOCK) WHERE xw_pod_nbr=@purNo AND xw_pod_part=@partNo";
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@purNo", purNo);
                    param.Add("@partNo", partNo);

                    obj = dbConn.ExecuteScalar(sql, param);
                    if (obj == null)
                    {
                        return false;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 查询工厂ID
        /// </summary>
        /// <returns></returns>
        public string GetFactoryId(int userID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 factory_id FROM sys_emp (NOLOCK) WHERE emp_id=@userId AND enabled='Y'";
                DynamicParameters param = new DynamicParameters(1);
                param.Add("@userId", userID);

                object obj = dbConn.ExecuteScalar(sql, param);
                if (obj == null)
                {
                    return "";
                }

                return obj.ToString();
            }
        }

        /// <summary>
        /// 获取单据码
        /// </summary>
        /// <param name="item">单据号类型</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public string GetOrderNo(string item, string prefix)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", item);
                list.Add("@order_prefix", prefix);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        /// <summary>
        /// 保存采购单数据并转结日程单条码
        /// </summary>
        /// <returns></returns>
        public string DataSaveTransact(DataTable dtWeekSchedule, DataTable dtDaySchedule,int userID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (dtWeekSchedule == null || dtWeekSchedule.Rows.Count < 1)
                {
                    return "周计划表头数据读取错误，请确认文件格式!";
                }

                if (dtDaySchedule == null || dtDaySchedule.Rows.Count < 1)
                {
                    return "周计划表身数据读取错误，请确认文件格式!";
                }
                IDbTransaction transaction = dbConn.BeginTransaction();

                StringBuilder sql = new StringBuilder(1024);
                try
                {
                    string saveDate = GetServerDateTime("YMD");
                    sql.Remove(0, sql.Length);
                    sql.Append("SELECT RIGHT(CAST(COUNT(DISTINCT iss_seq)+10001 AS VARCHAR(5)),3) FROM g_buy_weekly WHERE create_time=@create_time");
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@create_time", saveDate);
                    string strSeq = dbConn.ExecuteScalar(sql.ToString(), param, transaction).ToString();
                    if (string.IsNullOrEmpty(strSeq))
                    {
                        transaction.Rollback();
                        return "周计划单导入次序查询失败!";
                    }
                    transaction.Commit();

                    strSeq = saveDate.Replace("/", "") + "-" + strSeq;
                    foreach (DataRow drWeek in dtWeekSchedule.Rows)
                    {
                        string strSeNo = GetOrderNo("BuyWeekOrder", "WR");//weekly order no
                        if (string.IsNullOrEmpty(strSeNo))
                        {
                            transaction.Rollback();
                            return "周计划单据号获取错误!";
                        }

                        string factoryId = drWeek["wa_no"].ToString();
                        string strSuNo = drWeek["su_no"].ToString();
                        string strVendorId = drWeek["vendor_id"].ToString();
                        DataRow[] drs = dtDaySchedule.Select("vendor_id='" + strVendorId + "'");
                        if (drs.Length < 1)
                        {
                            continue;
                        }

                        List<string> sqlStrings = new List<string>();
                        List<DynamicParameters> parameters = new List<DynamicParameters>();

                        //添加采购订单周计划表头
                        sql.Remove(0, sql.Length);
                        sql.Append("INSERT INTO g_buy_weekly (order_no,weekly_no,buy_no,vendor_id,iss_id,iss_date,iss_seq,factory_id ");
                        sql.Append(",enabled,create_userid, create_time) VALUES (@order_no,@weekly_no,@buy_no,@vendor_id,@iss_id,@iss_date ");
                        sql.Append(",@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
                        param = new DynamicParameters();
                        param.Add("@order_no", strSeNo);
                        param.Add("@weekly_no", drWeek["se_week"].ToString());
                        param.Add("@buy_no", drWeek["ch_no"].ToString());
                        param.Add("@vendor_id", strVendorId);
                        param.Add("@iss_id", drWeek["iss_id"].ToString());
                        param.Add("@iss_date", drWeek["se_date"].ToString());
                        param.Add("@iss_seq", strSeq);
                        param.Add("@factory_id", drWeek["wa_no"].ToString());
                        param.Add("@enabled", "Y");
                        param.Add("@user_id", userID);

                        sqlStrings.Add(sql.ToString());
                        parameters.Add(param);
                        //_remotingClone.ExecuteNonQuery(sql.ToString(), param);

                        //按供应商添加每次提交的日程单数据
                        foreach (DataRow drDay in drs)
                        {
                            sql.Remove(0, sql.Length);
                            sql.Append("INSERT INTO g_ht_buy_day (order_no,type_id,buy_no,vendor_id,se_date,se_time,part_id,qty,snp_qty ");
                            sql.Append(",carton_qty,iss_seq,factory_id,enabled,create_userid, create_time) VALUES (@order_no ");
                            sql.Append(",@type_id,@buy_no,@vendor_id,@se_date,@se_time,@part_id,@qty,@snpQty ");
                            sql.Append(",@carton_qty,@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");

                            param = new DynamicParameters();
                            param.Add("@order_no", strSeNo);
                            param.Add("@type_id", drDay["type_id"].ToString());
                            param.Add("@buy_no", drDay["ch_no"].ToString());
                            param.Add("@vendor_id", strVendorId);
                            param.Add("@se_date", drDay["se_date"].ToString());
                            param.Add("@se_time", drDay["se_time"].ToString());
                            param.Add("@part_id", drDay["part_id"].ToString());
                            param.Add("@qty", drDay["qty"].ToString());
                            param.Add("@snpQty", drDay["snp_qty"].ToString());
                            param.Add("@carton_qty", drDay["carton_qty"].ToString());
                            param.Add("@iss_seq", strSeq);
                            param.Add("@factory_id", factoryId);
                            param.Add("@enabled", "Y");
                            param.Add("@user_id", userID);

                            sqlStrings.Add(sql.ToString());
                            parameters.Add(param);
                            //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                        }

                        //清除日程单导入汇总表中数据，只保留最后一次导入数据（按供应商及本次导入的所含的日期)
                        sql.Remove(0, sql.Length);
                        sql.Append("update g_buy_day set Enabled='N'  WHERE vendor_id=@vendor_id AND se_date IN (SELECT DISTINCT se_date FROM g_ht_buy_day ");
                        sql.Append(" WHERE vendor_id=@vendor_id AND order_no=@order_no AND iss_seq=@iss_seq) AND type_id IN (SELECT DISTINCT ");
                        sql.Append(" type_id FROM g_ht_buy_day WHERE vendor_id=@vendor_id AND order_no=@order_no AND iss_seq=@iss_seq)");
                        param = new DynamicParameters();
                        param.Add("@vendor_id", strVendorId);
                        param.Add("@order_no", strSeNo);
                        param.Add("@iss_seq", strSeq);

                        sqlStrings.Add(sql.ToString());
                        parameters.Add(param);
                        //_remotingClone.ExecuteNonQuery(sql.ToString(), param);

                        //将本次提交数据更新至采购日程数据表中
                        foreach (DataRow drDay in drs)
                        {
                            sql.Remove(0, sql.Length);
                            sql.Append("INSERT INTO g_buy_day (order_no,type_id,buy_no,vendor_id,se_date,se_time,part_id,qty,snp_qty ");
                            sql.Append(",carton_qty,iss_seq,factory_id,enabled,create_userid, create_time) VALUES (@order_no ");
                            sql.Append(",@type_id,@buy_no,@vendor_id,@se_date,@se_time,@part_id,@qty,@snpQty ");
                            sql.Append(",@carton_qty,@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");

                            param = new DynamicParameters();
                            param.Add("@order_no", strSeNo);
                            param.Add("@type_id", drDay["type_id"].ToString());
                            param.Add("@buy_no", drDay["ch_no"].ToString());
                            param.Add("@vendor_id", strVendorId);
                            param.Add("@se_date", drDay["se_date"].ToString());
                            param.Add("@se_time", drDay["se_time"].ToString());
                            param.Add("@part_id", drDay["part_id"].ToString());
                            param.Add("@qty", drDay["qty"].ToString());
                            param.Add("@snpQty", drDay["snp_qty"].ToString());
                            param.Add("@carton_qty", drDay["carton_qty"].ToString());
                            param.Add("@iss_seq", strSeq);
                            param.Add("@factory_id", factoryId);
                            param.Add("@enabled", "Y");
                            param.Add("@user_id", userID);

                            sqlStrings.Add(sql.ToString());
                            parameters.Add(param);
                            //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                        }

                        // Batch Execute

                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                            transaction.Commit();
                        }
                        sql.Remove(0, sql.Length);
                        sql.Append("SELECT type_id,se_date,part_id,RIGHT(REPLACE(se_date,'/',''),6) AS lot_no,SUM(qty) AS qty,buy_no ");
                        sql.Append(" ,(SELECT TOP 1 part_no FROM sys_part (NOLOCK) WHERE part_id=a.part_id) AS part_no ");
                        sql.Append(" ,(SELECT TOP 1 buy_no FROM g_buy_day (NOLOCK) WHERE type_id=a.type_id AND se_date=a.se_date AND part_id=a.part_id) AS buy_no ");
                        sql.Append(",snp_qty,SUM(carton_qty) AS carton_qty FROM g_buy_day AS a WHERE vendor_id=@vendor_id AND order_no=@order_no ");
                        sql.Append(" AND iss_seq=@iss_seq GROUP BY type_id,se_date,part_id,snp_qty,buy_no ORDER BY type_id,se_date,part_id");
                        param = new DynamicParameters();
                        param.Add("@vendor_id", strVendorId);
                        param.Add("@order_no", strSeNo);
                        param.Add("@iss_seq", strSeq);
                        List<GBuyDay> dtCalc = dbConn.Query<GBuyDay>(sql.ToString(), param).ToList();
                        if (dtCalc == null || dtCalc.Count < 1)
                        {
                            continue;
                        }

                        sqlStrings = new List<string>();
                        parameters = new List<DynamicParameters>();
                        string dayOrderNo = string.Empty;
                        string sSeDate = string.Empty;
                        foreach (GBuyDay drCalc in dtCalc)
                        {
                            int iSnpQty = drCalc.SnpQty;
                            int iBoxQty = 0;
                            int iRema = 0;
                            int iDiffQty = 0;

                            if (sSeDate != drCalc.SeDate)
                            {
                                //dayOrderNo = "DR" + drCalc["se_date"].ToString().Replace("/", "");
                                dayOrderNo = GetOrderNo("BuyDayOrder", "PR");

                                sSeDate = drCalc.SeDate;
                            }
                            sql.Remove(0, sql.Length);
                            sql.Append("SELECT TOP 1 qty,snp_qty,carton_qty,is_closed FROM g_buy_day_fact (NOLOCK) ");
                            sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id AND vendor_id=@vendor_id ");
                            param = new DynamicParameters();
                            param.Add("@type_id", drCalc.TypeId.ToString());
                            param.Add("@se_date", drCalc.SeDate.ToString());
                            param.Add("@part_id", drCalc.PartId.ToString());
                            param.Add("@vendor_id", strVendorId);
                            List<GBuyDayFact> dtDayFact = dbConn.Query<GBuyDayFact>(sql.ToString(), param).ToList();
                            if (dtDayFact == null || dtDayFact.Count < 1)
                            {
                                sql.Remove(0, sql.Length);
                                sql.Append("INSERT INTO g_buy_day_fact (order_no,weekly_order_no,type_id,buy_no,vendor_id,se_date,part_id,lot_no ");
                                sql.Append(" ,qty,snp_qty,carton_qty,rec_qty,rec_carton_qty,diff_qty,status,is_closed,type,factory_id,enabled,create_userid, create_time) ");
                                sql.Append(" VALUES (@order_no,@weekly_order_no,@type_id,@buy_no,@vendor_id,@se_date,@part_id,@lot_no ");
                                sql.Append(" ,@qty,@snpQty,@carton_qty,'0','0',@qty,@status,@is_closed,@type,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
                                param = new DynamicParameters();
                                param.Add("@order_no", dayOrderNo);
                                param.Add("@weekly_order_no", strSeNo);
                                param.Add("@type_id", drCalc.TypeId.ToString());
                                param.Add("@buy_no", drCalc.BuyNo.ToString());
                                param.Add("@vendor_id", strVendorId);
                                param.Add("@se_date", drCalc.SeDate.ToString());
                                param.Add("@part_id", drCalc.PartId.ToString());
                                param.Add("@lot_no", drCalc.SeDate.ToString().Replace("/", "").Substring(2));
                                param.Add("@qty", drCalc.Qty.ToString());
                                param.Add("@snpQty", drCalc.SnpQty.ToString());
                                param.Add("@carton_qty", drCalc.CartonQty.ToString());
                                param.Add("@status", "0");
                                param.Add("@is_closed", "N");
                                param.Add("@type", "0");
                                param.Add("@factory_id", factoryId);
                                param.Add("@enabled", "Y");
                                param.Add("@user_id", userID);

                                //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                                sqlStrings.Add(sql.ToString());
                                parameters.Add(param);

                                iDiffQty = Convert.ToInt32(drCalc.Qty.ToString());
                                iBoxQty = (int)Math.Ceiling(Convert.ToDouble(iDiffQty) / iSnpQty);
                                iRema = iDiffQty % iSnpQty;
                            }
                            else
                            {
                                int iCalcQty = Convert.ToInt32(drCalc.Qty.ToString());
                                int iFactQty = Convert.ToInt32(dtDayFact[0].Qty.ToString());
                                iDiffQty = iCalcQty - iFactQty;

                                if (iDiffQty > 0)
                                {
                                    sql.Remove(0, sql.Length);
                                    sql.Append("UPDATE g_buy_day_fact SET qty=@qty,carton_qty=@carton_qty,diff_qty=@diff_qty ");
                                    sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id ");
                                    param = new DynamicParameters();
                                    param.Add("@qty", drCalc.Qty.ToString());
                                    param.Add("@carton_qty", drCalc.CartonQty.ToString());
                                    param.Add("@diff_qty", iDiffQty.ToString());
                                    param.Add("@type_id", drCalc.TypeId.ToString());
                                    param.Add("@se_date", drCalc.SeDate.ToString());
                                    param.Add("@part_id", drCalc.PartId.ToString());

                                    //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                                    sqlStrings.Add(sql.ToString());
                                    parameters.Add(param);

                                    iBoxQty = (int)Math.Ceiling(Convert.ToDouble(iDiffQty) / iSnpQty);
                                    iRema = iDiffQty % iSnpQty;
                                }
                                else
                                {
                                    sql.Remove(0, sql.Length);
                                    sql.Append("UPDATE g_buy_day_fact SET diff_qty=@diff_qty ");
                                    sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id ");
                                    param = new DynamicParameters();
                                    param.Add("@diff_qty", iDiffQty.ToString());
                                    param.Add("@type_id", drCalc.TypeId.ToString());
                                    param.Add("@se_date", drCalc.SeDate.ToString());
                                    param.Add("@part_id", drCalc.PartId.ToString());

                                    //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                                    sqlStrings.Add(sql.ToString());
                                    parameters.Add(param);
                                }
                            }

                            if (iDiffQty > 0 && iBoxQty > 0)
                            {
                                sql.Remove(0, sql.Length);
                                sql.Append("SELECT TOP 1 RIGHT(carton_no,3) AS carton_no,lot_no FROM sys_stock WHERE 1=1 ");
                                sql.Append(" AND se_date=@se_date AND part_id=@part_id  ORDER BY carton_no DESC");
                                param = new DynamicParameters();
                                param.Add("@type_id", drCalc.TypeId.ToString());
                                param.Add("@se_date", drCalc.SeDate.ToString());
                                param.Add("@part_id", drCalc.PartId.ToString());

                                int iLastQty = 0;
                                string strLotNo = string.Empty;
                                List<SysStock> dtCarton = dbConn.Query<SysStock>(sql.ToString(), param).ToList();
                                if (dtCarton == null || dtCarton.Count < 1)
                                {
                                    strLotNo = drCalc.SeDate.ToString().Replace("/", "").Substring(2);
                                }
                                else
                                {
                                    iLastQty = Convert.ToInt32(dtCarton[0].CartonNo.ToString());
                                    strLotNo = dtCarton[0].LotNo.ToString();
                                }

                                for (int i = 1; i <= iBoxQty; i++)
                                {
                                    iLastQty++;
                                    string strCartonNo = GetCartonNo(drCalc.PartNo.ToString(), strLotNo, iLastQty);
                                    string strCartonQty = iSnpQty.ToString();
                                    if (i == iBoxQty && iRema > 0)
                                    {
                                        strCartonQty = iRema.ToString();
                                    }

                                    sql.Remove(0, sql.Length);
                                    sql.Append("INSERT INTO sys_stock (order_no,weekly_order_no,type_id,buy_no,vendor_id,se_date,part_id ");
                                    sql.Append(" ,carton_no,lot_no,qty,snp_qty,plan_qty,status,iss_seq,group_no,factory_id,enabled,create_userid, create_time) ");
                                    sql.Append(" VALUES (@order_no,@weekly_order_no,@type_id,@buy_no,@vendor_id,@se_date,@part_id ");
                                    sql.Append(" ,@carton_no,@lot_no,@qty,@snpQty,@qty,@status,@iss_seq,@groupNo,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
                                    param = new DynamicParameters();
                                    param.Add("@order_no", dayOrderNo);
                                    param.Add("@weekly_order_no", strSeNo);
                                    param.Add("@type_id", drCalc.TypeId.ToString());
                                    param.Add("@buy_no", drCalc.BuyNo.ToString());
                                    param.Add("@vendor_id", strVendorId);
                                    param.Add("@se_date", drCalc.SeDate.ToString());
                                    param.Add("@part_id", drCalc.PartId.ToString());
                                    param.Add("@carton_no", strCartonNo);
                                    param.Add("@lot_no", strLotNo);
                                    param.Add("@qty", strCartonQty);
                                    param.Add("@snpQty", iSnpQty.ToString());
                                    param.Add("@status", "A");
                                    param.Add("@iss_seq", strSeq);
                                    param.Add("@groupNo", "");
                                    param.Add("@factory_id", factoryId);
                                    param.Add("@enabled", "Y");
                                    param.Add("@user_id", userID);

                                    //_remotingClone.ExecuteNonQuery(sql.ToString(), param);
                                    sqlStrings.Add(sql.ToString());
                                    parameters.Add(param);
                                }
                            }
                        }
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "周计划订单数据保存失败!\r\n" + ex.Message;
                }
                return "";
            }
        }

        /// <summary>
        /// 箱条码生成(零件号(18)+批号(6)+流水号(3))
        /// </summary>
        /// <param name="partNo">零件号</param>
        /// <param name="lotNo">批号</param>
        /// <param name="seq">流水号</param>
        /// <returns></returns>
        public string GetCartonNo(string partNo, string lotNo, int seq)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strCartonNo = partNo + "000000000000000000";
                strCartonNo = strCartonNo.Substring(0, 18) + lotNo;
                string strSeq = "000" + seq.ToString();
                return strCartonNo + strSeq.Substring(strSeq.Length - 3);
            }
        }
        #endregion
        #endregion  成员方法
    }
}