using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using Microsoft.Extensions.Primitives;
using System.Data;
using System.Text;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 委外配料
    /// </summary>
    public class OutSourceBatchRepository : BaseRepository<BaseEntity>, IOutSourceBatchRepository
    {
        public OutSourceBatchRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        public List<WmsOutstockDetail> CheckMoveDetail(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder getqcSQL = new StringBuilder(1024);
                getqcSQL.Append(@"select *  from wms_outstock_detail as a (nolock) where order_no=@order_no and enabled='Y'");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_no", orderNo);
                List<WmsOutstockDetail> WmsOutstockDetail = dbConn.Query<WmsOutstockDetail>(getqcSQL.ToString(), parameters).ToList();

                return WmsOutstockDetail;
            }
        }

        public List<SysStock> GetCartonInfoByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE group_no=@group_no");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@group_no", groupNo);

                return dbConn.Query<SysStock>(stringBuilder.ToString(), parameters).ToList();
            }
        }

        public SysStock GetCartonInfo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a (nolock) left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE carton_no=@carton_no");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

                return dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString(), parameters);
            }
        }

        public List<SysStock> GetStockByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from sys_stock (nolock) where carton_no=@carton_no ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

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

        public List<SysStock> GetPartByFIFO(string part_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@"select * from sys_stock (nolock) where part_id=@part_id and status=@status  and enabled='Y' 
                            and create_time=(select min(create_time) from sys_stock (nolock) where part_id=@part_id and status=@status and enabled='Y' )");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@part_id", part_id);
                parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                //List<SysStock> dt = dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }

        public SetObjectDetail UpdteStatus(string orderNo, List<WmsOutstockDetail> detail, SysStock Stock, string groupNo, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();//返回消息
                List<string> sqlStrings = new List<string>();
                List<DynamicParameters> parameterList = new List<DynamicParameters>();

                //修改移库主表  30 处理中
                StringBuilder updateString = new StringBuilder(1024);
                updateString.Append(" update wms_outstock set order_status=@order_status,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ");
                DynamicParameters updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
                updateStringparameters.Add("@order_no", detail[0].OrderNo);
                updateStringparameters.Add("@update_userid", loginId);
                updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                //修改移库明细表  30 处理中
                StringBuilder updateString2 = new StringBuilder(1024);
                updateString2.Append(@"update wms_outstock_detail set pick_qty = pick_qty + @pick_qty, item_status=@item_status,update_userid=@update_userid,update_time=@update_time where ruid=@ruid ");
                DynamicParameters updateStringparameters2 = new DynamicParameters();
                updateStringparameters2.Add("@item_status", (int)WmsEnumUtil.MoveOrderDetailStatus.BATCHING);
                updateStringparameters2.Add("@ruid", detail[0].Ruid);
                updateStringparameters2.Add("@pick_qty", Stock.Qty);
                updateStringparameters2.Add("@update_userid", loginId);
                updateStringparameters2.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString2.ToString());
                parameterList.Add(updateStringparameters2);

                //修改条码表  60 配料中
                StringBuilder updateString5 = new StringBuilder(1024);
                updateString5.Append("update sys_stock set status=@status,group_no='" + groupNo + "',update_userid=@update_userid,update_time=@update_time  where carton_no= @carton_no ");
                DynamicParameters updateStringparameters5 = new DynamicParameters();
                updateStringparameters5.Add("@carton_no", Stock.CartonNo);
                updateStringparameters5.Add("@status", (int)WmsEnumUtil.StockStatus.BATCHED);
                updateStringparameters5.Add("@update_userid", loginId);
                updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString5.ToString());
                parameterList.Add(updateStringparameters5);


                //插入 wms_outstock_sn 条码表
                StringBuilder updateString3 = new StringBuilder(1024);
                updateString3.Append(@"INSERT INTO [dbo].[wms_outstock_sn]
                                        ([order_no]
                                        ,[item_no]
                                        ,[part_id]
                                        ,[part_no]
                                        ,[part_spec]
                                        ,[carton_no]
                                        ,[qty]
                                        ,[unit]
                                        ,[status]
                                        ,[factory_id]
                                        ,[factory_code]
                                        ,[enabled]
                                        ,[create_userid]
                                        ,[create_time]
                                        ,[update_userid]
                                        ,[update_time]
                                        ,[guid])
                                         VALUES (@order_no,@item_no,@part_id,@part_no,@part_spec,@carton_no,@qty,@unit,@status,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid)");
                DynamicParameters updateStringparameters3 = new DynamicParameters();
                updateStringparameters3.Add("@order_no", detail[0].OrderNo);
                updateStringparameters3.Add("@item_no", detail[0].ItemNo);
                updateStringparameters3.Add("@part_id", detail[0].PartId);
                updateStringparameters3.Add("@part_no", detail[0].PartNo);
                updateStringparameters3.Add("@part_spec", detail[0].PartSpec);
                updateStringparameters3.Add("@carton_no", Stock.CartonNo);
                updateStringparameters3.Add("@qty", Stock.Qty);
                updateStringparameters3.Add("@unit", Stock.Unit);
                updateStringparameters3.Add("@status", (int)WmsEnumUtil.MoveOrderSnStatus.BATCHED);
                updateStringparameters3.Add("@factory_id", detail[0].FactoryId);
                updateStringparameters3.Add("@factory_code", detail[0].FactoryCode);
                updateStringparameters3.Add("@enabled", "Y");
                updateStringparameters3.Add("@update_userid", loginId);
                updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters3.Add("@create_userid", loginId);
                updateStringparameters3.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters3.Add("@guid", Guid.NewGuid());

                sqlStrings.Add(updateString3.ToString());
                parameterList.Add(updateStringparameters3);


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

                DynamicParameters updateStringparameters4 = new DynamicParameters();
                updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.OUTWARD_PICK);
                updateStringparameters4.Add("@carton_no", Stock.CartonNo);
                updateStringparameters4.Add("@part_id", Stock.PartId);
                updateStringparameters4.Add("@part_no", Stock.PartNo);
                updateStringparameters4.Add("@part_spec", Stock.PartSpec);
                updateStringparameters4.Add("@src_locate_id", Stock.LocateId);
                updateStringparameters4.Add("@src_locate_name", Stock.LocateName);
                updateStringparameters4.Add("@dest_locate_id", Stock.LocateId);
                updateStringparameters4.Add("@dest_locate_name", Stock.LocateName);
                updateStringparameters4.Add("@old_qty", Stock.Qty);
                updateStringparameters4.Add("@new_qty", Stock.Qty);
                updateStringparameters4.Add("@trans_qty", Stock.Qty);
                updateStringparameters4.Add("@old_status", Stock.Status);
                updateStringparameters4.Add("@new_status", (int)WmsEnumUtil.StockStatus.BATCHED);
                updateStringparameters4.Add("@old_qms_status", Stock.QmsStatus);
                updateStringparameters4.Add("@new_qms_status", Stock.QmsStatus);
                updateStringparameters4.Add("@unit", Stock.Unit);
                updateStringparameters4.Add("@factory_id", Stock.FactoryId);
                updateStringparameters4.Add("@factory_code", Stock.FactoryCode);
                updateStringparameters4.Add("@src_erp_warehouse", Stock.ErpWarehouse);
                updateStringparameters4.Add("@dest_erp_warehouse", Stock.ErpWarehouse);
                updateStringparameters4.Add("@src_warehouse_id", Stock.WarehouseId);
                updateStringparameters4.Add("@src_warehouse_name", Stock.WarehouseName);
                updateStringparameters4.Add("@dest_warehouse_id", Stock.WarehouseId);
                updateStringparameters4.Add("@dest_warehouse_name", Stock.WarehouseName);
                updateStringparameters4.Add("@src_zone_id", Stock.ZoneId);
                updateStringparameters4.Add("@src_zone_name", Stock.ZoneName);
                updateStringparameters4.Add("@dest_zone_id", Stock.ZoneId);
                updateStringparameters4.Add("@dest_zone_name", Stock.ZoneName);
                updateStringparameters4.Add("@enabled", Stock.Enabled);
                updateStringparameters4.Add("@update_userid", loginId);
                updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@create_userid", loginId);
                updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@guid", Guid.NewGuid());
                updateStringparameters4.Add("@ref_order_no", orderNo);

                sqlStrings.Add(updateString4.ToString());
                parameterList.Add(updateStringparameters4);

                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();

                bool successCount = true;
                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
                    {
                        successCount = false;
                    }
                }
                if (successCount)
                {
                    transaction.Commit();

                    StringBuilder stringBuilder = new StringBuilder(1024);
                    stringBuilder.Append("SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.pick_qty)) pick_qty,c.part_no,c.part_spec,(select top 1 locate_name from sys_stock (nolock) where part_id=c.part_id and status=" + (int)WmsEnumUtil.StockStatus.INSTOCKED + " order by create_time) as recommend_locate  FROM dbo.wms_outstock a (nolock) ");
                    stringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                    stringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status in ('30') AND order_type=" + (int)WmsEnumUtil.OutStockType.OUTSOURCE_ACTION + " and a.enabled='Y' group by c.part_id,c.part_no,c.part_spec");

                    List<WmsOutstockDetail> wmsMoves1 = dbConn.Query<WmsOutstockDetail>(stringBuilder.ToString()).ToList();
                    for (int i = 0; i < wmsMoves1.Count; i++)
                    {
                        if (wmsMoves1[i].Qty != wmsMoves1[i].PickQty)
                        {
                            rfInfo.type = "PASS";
                            rfInfo.message = "操作成功!请继续扫描箱条码";
                            rfInfo.wmsObjectDetail = wmsMoves1.ConvertAll(s => (object)s);
                            return rfInfo;
                        }
                    }
                    rfInfo.type = "PASS";
                    rfInfo.message = "订单操作完成!请切换订单或移至委外发运";
                    rfInfo.wmsObjectDetail = wmsMoves1.ConvertAll(s => (object)s);
                    return rfInfo;

                }
                else
                {
                    transaction.Rollback();
                    rfInfo.type = "FAIL";
                    rfInfo.message = "未查询到数据，请检查！";
                    return rfInfo;
                }
            }
        }

        /// <summary>
        /// 按照组托批量处理配料交易
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="groupNo"></param>
        /// <param name="loginId"></param>
        /// <returns></returns>
        public bool UpdteStatusGroup(string orderNo, string groupNo, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();//返回消息
                List<string> sqlStrings = new List<string>();
                List<DynamicParameters> parameterList = new List<DynamicParameters>();

                //修改移库主表  30 处理中
                StringBuilder updateString = new StringBuilder(1024);
                updateString.Append(" update wms_outstock set order_status=@order_status,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ");
                DynamicParameters updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
                updateStringparameters.Add("@order_no", orderNo);
                updateStringparameters.Add("@update_userid", loginId);
                updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                //修改移库明细表  30 处理中

                updateString.Clear();
                updateString.Append("UPDATE a SET a.pick_qty = a.pick_qty + b.qty,item_status=@itemStatus,update_userid=@updateUserid,update_time=@updateTime ");
                updateString.Append("FROM dbo.wms_outstock_detail a ");
                updateString.Append("JOIN (SELECT  part_id,part_no,SUM(qty) AS qty FROM dbo.sys_stock  ");
                updateString.Append("      WHERE group_no = @groupNo GROUP BY part_id,part_no) b ON a.part_id = b.part_id ");
                updateString.Append("WHERE a.order_no = @orderNo ");

                updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@itemStatus", (int)WmsEnumUtil.MoveOrderDetailStatus.BATCHING);
                updateStringparameters.Add("@updateUserid", loginId);
                updateStringparameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters.Add("@orderNo", orderNo);
                updateStringparameters.Add("@groupNo", groupNo);

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                //插入 wms_outstock_sn 条码表
                updateString.Clear();
                updateString.Append("INSERT INTO wms_outstock_sn  ");
                updateString.Append("  (order_no,item_no,part_id,part_no,part_spec,carton_no,qty ,unit,status, ");
                updateString.Append("  factory_id,factory_code,enabled,create_userid,create_time,guid) ");
                updateString.Append("select a.order_no,a.item_no,a.part_id ,a.part_no ,a.part_spec ,b.carton_no ,b.qty ,b.unit ,@snStatus, ");
                updateString.Append("  a.factory_id ,a.factory_code ,'Y',@createUserid,@createTime,@guid ");
                updateString.Append("from wms_outstock_detail a ");
                updateString.Append("join sys_stock b on a.part_id = b.part_id  ");
                updateString.Append("where a.order_no = @orderNo and  b.group_no = @groupNo ");

                updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@snStatus", (int)WmsEnumUtil.MoveOrderSnStatus.BATCHED);
                updateStringparameters.Add("@createUserid", loginId);
                updateStringparameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters.Add("@guid", Guid.NewGuid());
                updateStringparameters.Add("@orderNo", orderNo);
                updateStringparameters.Add("@groupNo", groupNo);

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                //新增 sys_stock_trans 事务表
                updateString.Clear();
                updateString.Append("INSERT INTO sys_stock_trans ");
                updateString.Append("    (trans_code,carton_no,part_id,part_no,part_spec,src_locate_id,src_locate_name, ");
                updateString.Append("    dest_locate_id,dest_locate_name,old_qty,new_qty,trans_qty,old_status,new_status, ");
                updateString.Append("    old_qms_status,new_qms_status,unit,factory_id,factory_code,src_erp_warehouse,dest_erp_warehouse, ");
                updateString.Append("    src_warehouse_id,src_warehouse_name,dest_warehouse_id,dest_warehouse_name,src_zone_id,src_zone_name, ");
                updateString.Append("   dest_zone_id,dest_zone_name,enabled,create_userid,create_time,guid,ref_order_no) ");
                updateString.Append("select @transCode,carton_no ,part_id ,part_no ,part_spec ,locate_id ,locate_name , ");
                updateString.Append("    locate_id ,locate_name ,qty ,qty ,qty ,status ,@newStatus, ");
                updateString.Append("    qms_status ,qms_status ,unit ,factory_id ,factory_code ,erp_warehouse , ");
                updateString.Append("    erp_warehouse ,warehouse_id ,warehouse_name ,warehouse_id ,warehouse_name ,zone_id ,zone_name , ");
                updateString.Append("    zone_id ,zone_name ,'Y',@createUserid,@createTime,@guid,@orderNo ");
                updateString.Append("FROM sys_stock where group_no= @groupNo and status = @status and enabled = 'Y' ");

                updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@transCode", (int)WmsEnumUtil.TransType.OUTWARD_PICK);
                updateStringparameters.Add("@newStatus", (int)WmsEnumUtil.StockStatus.BATCHED);
                updateStringparameters.Add("@createUserid", loginId);
                updateStringparameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters.Add("@guid", Guid.NewGuid());
                updateStringparameters.Add("@orderNo", orderNo);
                updateStringparameters.Add("@groupNo", groupNo);
                updateStringparameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                //修改条码表  60 配料中
                updateString.Clear();
                updateString.Append("update sys_stock set status=@status,update_userid=@update_userid,update_time=@update_time ");
                updateString.Append("where group_no= @groupNo and status = @whereStatus and enabled = 'Y' ");

                updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@groupNo", groupNo);
                updateStringparameters.Add("@whereStatus", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                updateStringparameters.Add("@status", (int)WmsEnumUtil.StockStatus.BATCHED);
                updateStringparameters.Add("@update_userid", loginId);
                updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);

                return this.ExecuteTransaction(sqlStrings,parameterList);
            }
        }

        public List<WmsOutstockDetail> getWmsOutstockDetailInfo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.pick_qty)) pick_qty,c.part_no,c.part_spec,(select top 1 locate_name from sys_stock (nolock) where part_id=c.part_id and status=" + (int)WmsEnumUtil.StockStatus.INSTOCKED + " order by create_time) as recommend_locate  FROM dbo.wms_outstock a (nolock) ");
                stringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                stringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status in ('30') AND order_type=" + (int)WmsEnumUtil.OutStockType.OUTSOURCE_ACTION + " and a.enabled='Y' group by c.part_id,c.part_no,c.part_spec");

                List<WmsOutstockDetail> wmsMoves1 = dbConn.Query<WmsOutstockDetail>(stringBuilder.ToString()).ToList();
                return wmsMoves1;
            }
        }


        public List<SysStock> CheckMaterialSplit(List<SysStock> oldStock, int splitNum, string loginId, int splitCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

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

                decimal oldQty = oldStock[0].Qty;
                decimal newQty = oldStock[0].Qty - splitNum;

                //更新条码表，数量
                StringBuilder updateString = new StringBuilder(1024);
                updateString.Append("update sys_stock set qty=@qty  where carton_no=@carton_no ");
                DynamicParameters updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@qty", newQty);
                updateStringparameters.Add("@carton_no", oldStock[0].CartonNo);
                updateStringparameters.Add("@update_userid", loginId);
                updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                sqlStrings.Add(updateString.ToString());
                parameterList.Add(updateStringparameters);


                //新增 sys_stock_trans 事务表
                #region sys_stock_trans 事务表

                StringBuilder updateString4 = new StringBuilder(1024);
                updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
                                                    ([trans_code]
                                                    ,[carton_no]
                                                    ,[part_id]
                                                    ,[part_no]
                                                    ,[part_spec]
                                                    ,[src_locate_id]
                                                    ,[src_locate_name]
                                                    ,[dest_locate_id]
                                                    ,[dest_locate_name]
                                                    ,[old_qty]
                                                    ,[new_qty]
                                                    ,[trans_qty]
                                                    ,[old_status]
                                                    ,[new_status]
                                                    ,[old_qms_status]
                                                    ,[new_qms_status]
                                                    ,[unit]
                                                    ,[factory_id]
                                                    ,[factory_code]
                                                    ,[src_erp_warehouse]
                                                    ,[dest_erp_warehouse]
                                                    ,[src_warehouse_id]
                                                    ,[src_warehouse_name]
                                                    ,[dest_warehouse_id]
                                                    ,[dest_warehouse_name]
                                                    ,[src_zone_id]
                                                    ,[src_zone_name]
                                                    ,[dest_zone_id]
                                                    ,[dest_zone_name]
                                                    ,[enabled]
                                                    ,[update_userid]
                                                    ,[update_time]
                                                    ,[create_userid]
                                                    ,[create_time]
                                                    ,[guid])
                                                  VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty 
                                        ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");

                DynamicParameters updateStringparameters4 = new DynamicParameters();
                updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT);
                updateStringparameters4.Add("@carton_no", oldStock[0].CartonNo);
                updateStringparameters4.Add("@part_id", oldStock[0].PartId);
                updateStringparameters4.Add("@part_no", oldStock[0].PartNo);
                updateStringparameters4.Add("@part_spec", oldStock[0].PartSpec);
                updateStringparameters4.Add("@src_locate_id", oldStock[0].LocateId);
                updateStringparameters4.Add("@src_locate_name", oldStock[0].LocateName);
                updateStringparameters4.Add("@dest_locate_id", oldStock[0].LocateId);
                updateStringparameters4.Add("@dest_locate_name", oldStock[0].LocateName);
                updateStringparameters4.Add("@old_qty", oldQty);
                updateStringparameters4.Add("@new_qty", newQty);
                updateStringparameters4.Add("@trans_qty", oldStock[0].Qty);
                updateStringparameters4.Add("@old_status", oldStock[0].Status);
                updateStringparameters4.Add("@new_status", oldStock[0].Status);
                updateStringparameters4.Add("@old_qms_status", oldStock[0].QmsStatus);
                updateStringparameters4.Add("@new_qms_status", oldStock[0].QmsStatus);
                updateStringparameters4.Add("@unit", oldStock[0].Unit);
                updateStringparameters4.Add("@factory_id", oldStock[0].FactoryId);
                updateStringparameters4.Add("@factory_code", oldStock[0].FactoryCode);
                updateStringparameters4.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters4.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters4.Add("@src_warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters4.Add("@src_warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters4.Add("@dest_warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters4.Add("@dest_warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters4.Add("@src_zone_id", oldStock[0].ZoneId);
                updateStringparameters4.Add("@src_zone_name", oldStock[0].ZoneName);
                updateStringparameters4.Add("@dest_zone_id", oldStock[0].ZoneId);
                updateStringparameters4.Add("@dest_zone_name", oldStock[0].ZoneName);
                updateStringparameters4.Add("@enabled", oldStock[0].Enabled);
                updateStringparameters4.Add("@update_userid", loginId);
                updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@create_userid", loginId);
                updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@guid", Guid.NewGuid());

                sqlStrings.Add(updateString4.ToString());
                parameterList.Add(updateStringparameters4);
                #endregion

                oldStock[0].CartonNo = checkCartonNo(oldStock[0].CartonNo, splitCount);

                oldStock[0].Qty = splitNum;
                //插入拆分出的箱条码
                #region 插入拆分出的箱条码

                StringBuilder updateString3 = new StringBuilder(1024);
                updateString3.Append(@" INSERT INTO [dbo].[sys_stock]
                                        ([vendor_id]
                                        ,[vendor_code]
                                        ,[carton_no]
                                        ,[part_id]
                                        ,[part_no]
                                        ,[part_spec]
                                        ,[lot_no]
                                        ,[fix_lot_no]
                                        ,[status]
                                        ,[qty]
                                        ,[snp_qty]
                                        ,[locate_id]
                                        ,[locate_name]
                                        ,[group_no]
                                        ,[erp_warehouse]
                                        ,[date_code]
                                        ,[qms_status]
                                        ,[ref_order_no]
                                        ,[unit]
                                        ,[dock]
                                        ,[warehouse_id]
                                        ,[warehouse_name]
                                        ,[zone_id]
                                        ,[zone_name]
                                        ,[printed]
                                        ,[print_time]
                                        ,[remark]
                                        ,[factory_id]
                                        ,[factory_code]
                                        ,[enabled]
                                        ,[create_userid]
                                        ,[create_time]
                                        ,[update_userid]
                                        ,[update_time]
                                        ,[guid])
                                        VALUES(@vendor_id,@vendor_code,@carton_no,@part_id,@part_no,@part_spec,@lot_no,@fix_lot_no,@status,@qty,@snp_qty
,@locate_id,@locate_name,@group_no,@erp_warehouse,@date_code,@qms_status,@ref_order_no,@unit,@dock,@warehouse_id,@warehouse_name,@zone_id,@zone_name
,@printed,@print_time,@remark,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid )");

                DynamicParameters updateStringparameters3 = new DynamicParameters();
                updateStringparameters3.Add("@vendor_id", oldStock[0].VendorId);
                updateStringparameters3.Add("@vendor_code", oldStock[0].VendorName);
                updateStringparameters3.Add("@carton_no", oldStock[0].CartonNo);
                updateStringparameters3.Add("@part_id", oldStock[0].PartId);
                updateStringparameters3.Add("@part_no", oldStock[0].PartNo);
                updateStringparameters3.Add("@part_spec", oldStock[0].PartSpec);
                updateStringparameters3.Add("@lot_no", oldStock[0].LotNo);
                updateStringparameters3.Add("@fix_lot_no", oldStock[0].FixLotNo);
                updateStringparameters3.Add("@status", oldStock[0].Status);
                updateStringparameters3.Add("@qty", splitNum);
                updateStringparameters3.Add("@snp_qty", oldStock[0].SnpQty);
                updateStringparameters3.Add("@locate_id", oldStock[0].LocateId);
                updateStringparameters3.Add("@locate_name", oldStock[0].LocateName);
                updateStringparameters3.Add("@group_no", oldStock[0].GroupNo);
                updateStringparameters3.Add("@erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters3.Add("@date_code", oldStock[0].DateCode);
                updateStringparameters3.Add("@qms_status", oldStock[0].QmsStatus);
                updateStringparameters3.Add("@ref_order_no", oldStock[0].RefOrderNo);
                updateStringparameters3.Add("@unit", oldStock[0].Unit);
                updateStringparameters3.Add("@dock", oldStock[0].Dock);
                updateStringparameters3.Add("@warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters3.Add("@warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters3.Add("@zone_id", oldStock[0].ZoneId);
                updateStringparameters3.Add("@zone_name", oldStock[0].ZoneName);
                updateStringparameters3.Add("@printed", oldStock[0].Printed);
                updateStringparameters3.Add("@print_time", oldStock[0].PrintTime);
                updateStringparameters3.Add("@remark", oldStock[0].Remark);
                updateStringparameters3.Add("@factory_id", oldStock[0].FactoryId);
                updateStringparameters3.Add("@factory_code", oldStock[0].FactoryCode);
                updateStringparameters3.Add("@enabled", oldStock[0].Enabled);
                updateStringparameters3.Add("@update_userid", loginId);
                updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters3.Add("@create_userid", oldStock[0].CreateUserId);
                updateStringparameters3.Add("@create_time", oldStock[0].CreateTime);
                updateStringparameters3.Add("@guid", Guid.NewGuid());

                sqlStrings.Add(updateString3.ToString());
                parameterList.Add(updateStringparameters3);
                #endregion

                //新增 sys_stock_trans 事务表
                #region sys_stock_trans 事务表

                StringBuilder updateString5 = new StringBuilder(1024);
                updateString5.Append(@" INSERT INTO[dbo].[sys_stock_trans]
                                                    ([trans_code]
                                                    ,[carton_no]
                                                    ,[part_id]
                                                    ,[part_no]
                                                    ,[part_spec]
                                                    ,[src_locate_id]
                                                    ,[src_locate_name]
                                                    ,[dest_locate_id]
                                                    ,[dest_locate_name]
                                                    ,[old_qty]
                                                    ,[new_qty]
                                                    ,[trans_qty]
                                                    ,[old_status]
                                                    ,[new_status]
                                                    ,[old_qms_status]
                                                    ,[new_qms_status]
                                                    ,[unit]
                                                    ,[factory_id]
                                                    ,[factory_code]
                                                    ,[src_erp_warehouse]
                                                    ,[dest_erp_warehouse]
                                                    ,[src_warehouse_id]
                                                    ,[src_warehouse_name]
                                                    ,[dest_warehouse_id]
                                                    ,[dest_warehouse_name]
                                                    ,[src_zone_id]
                                                    ,[src_zone_name]
                                                    ,[dest_zone_id]
                                                    ,[dest_zone_name]
                                                    ,[enabled]
                                                    ,[update_userid]
                                                    ,[update_time]
                                                    ,[create_userid]
                                                    ,[create_time]
                                                    ,[guid])
                                                  VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty 
                                        ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");

                DynamicParameters updateStringparameters5 = new DynamicParameters();
                updateStringparameters5.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT);
                updateStringparameters5.Add("@carton_no", oldStock[0].CartonNo);
                updateStringparameters5.Add("@part_id", oldStock[0].PartId);
                updateStringparameters5.Add("@part_no", oldStock[0].PartNo);
                updateStringparameters5.Add("@part_spec", oldStock[0].PartSpec);
                updateStringparameters5.Add("@src_locate_id", oldStock[0].LocateId);
                updateStringparameters5.Add("@src_locate_name", oldStock[0].LocateName);
                updateStringparameters5.Add("@dest_locate_id", oldStock[0].LocateId);
                updateStringparameters5.Add("@dest_locate_name", oldStock[0].LocateName);
                updateStringparameters5.Add("@old_qty", 0);
                updateStringparameters5.Add("@new_qty", splitNum);
                updateStringparameters5.Add("@trans_qty", oldStock[0].Qty);
                updateStringparameters5.Add("@old_status", oldStock[0].Status);
                updateStringparameters5.Add("@new_status", oldStock[0].Status);
                updateStringparameters5.Add("@old_qms_status", oldStock[0].QmsStatus);
                updateStringparameters5.Add("@new_qms_status", oldStock[0].QmsStatus);
                updateStringparameters5.Add("@unit", oldStock[0].Unit);
                updateStringparameters5.Add("@factory_id", oldStock[0].FactoryId);
                updateStringparameters5.Add("@factory_code", oldStock[0].FactoryCode);
                updateStringparameters5.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters5.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters5.Add("@src_warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters5.Add("@src_warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters5.Add("@dest_warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters5.Add("@dest_warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters5.Add("@src_zone_id", oldStock[0].ZoneId);
                updateStringparameters5.Add("@src_zone_name", oldStock[0].ZoneName);
                updateStringparameters5.Add("@dest_zone_id", oldStock[0].ZoneId);
                updateStringparameters5.Add("@dest_zone_name", oldStock[0].ZoneName);
                updateStringparameters5.Add("@enabled", oldStock[0].Enabled);
                updateStringparameters5.Add("@update_userid", loginId);
                updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters5.Add("@create_userid", loginId);
                updateStringparameters5.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters5.Add("@guid", Guid.NewGuid());

                sqlStrings.Add(updateString5.ToString());
                parameterList.Add(updateStringparameters5);
                #endregion


                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();

                bool successCount = true;
                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
                    {
                        successCount = false;
                    }
                }
                if (successCount)
                {
                    transaction.Commit();
                    return oldStock;
                }
                else
                {
                    transaction.Rollback();
                    return null;
                }
            }
        }

        public string checkCartonNo(string carton, int splitCount)
        {
            List<SysStock> MaterialInfo = GetMaterialInfo(carton + "-" + splitCount);
            if (MaterialInfo.Count > 0)
            {
                //有重复
                return checkCartonNo(carton, splitCount + 1);
            }
            else
            {
                return carton + "-" + splitCount;
            }
        }

        public List<SysStock> GetMaterialInfo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from sys_stock (nolock) where carton_no= @carton_no ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

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


        /// <summary>
        /// 获取委外配料单据信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsOutstock> GetOutSourceBatchOrderList(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.order_no,b.enum_desc AS order_type_desc,a.create_time,d.emp_no,a.factory_code,a.enabled,a.order_status  FROM dbo.wms_outstock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type = b.enum_value AND b.enum_type = 'wms_outstock_order_type' ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid = d.emp_id ");
                SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' AND order_status in ('10','30') AND order_type = " + (int)WmsEnumUtil.OutStockType.OUTSOURCE_ACTION + " and a.enabled = 'Y' ");
                SqlStringBuilder.Append("AND order_no IN(SELECT t.order_no FROM wms_outstock_detail t (nolock) WHERE a.order_no= t.order_no GROUP BY t.order_no HAVING SUM(t.qty)!=SUM(t.pick_qty)) ");
                SqlStringBuilder.Append("  order by  case when order_status='30' then 0 else 1 end, create_time   ");

                List<WmsOutstock> wmsMoves = dbConn.Query<WmsOutstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;

            }
        }

        /// <summary>
        /// 获取委外配料单据信息通过订单号
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsOutstockDetail> GetOutSourceBatchOrderListByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.order_no,b.enum_desc AS order_type_desc,CONVERT(INT, c.qty) qty,CONVERT(INT, c.pick_qty) pick_qty,c.part_no ,a.create_userid,a.create_time,c.src_zone_id,c.src_zone_name,d.emp_no,a.factory_code,p.part_spec,(select top 1 locate_name from sys_stock (nolock) where part_id=c.part_id and status=" + (int)WmsEnumUtil.StockStatus.INSTOCKED + " order by create_time) as recommend_locate FROM dbo.wms_outstock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type=b.enum_value AND	b.enum_type='wms_outstock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid=d.emp_id ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON c.part_no=p.part_no ");
                SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' AND order_type=" + (int)WmsEnumUtil.OutStockType.OUTSOURCE_ACTION + " and a.enabled='Y' ");

                List<WmsOutstockDetail> wmsMoves = dbConn.Query<WmsOutstockDetail>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;
            }
        }

        //事务批量执行添加、修改
        public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
    }
}