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 System.Data;
using System.Text;
using static Estsh.Core.Model.EnumUtil.WmsEnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 零星出库
    /// </summary>
    public class ScatteredOutStockRepository : BaseRepository<BaseEntity>, IScatteredOutStockRepository
    {
        public ScatteredOutStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取零星出库单据信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsOutstock> GetScatteredOutStockOrderList(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 in ( '" + (int)WmsEnumUtil.OutStockType.EXCEPTION_OUT_STOCK +"','"+ (int)WmsEnumUtil.OutStockType.RESERVE_OUT + "' ) and a.enabled = 'Y' ");
                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<WmsOutstock> GetScatteredOutStockOrderListByOrderNo(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.out_qty) out_qty ,a.vendor_code ,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,p.part_spec2,a.vendor_code 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 in ('" + (int)WmsEnumUtil.OutStockType.EXCEPTION_OUT_STOCK +"','"+ (int)WmsEnumUtil.OutStockType.RESERVE_OUT + "') and a.enabled='Y' ");
                List<WmsOutstock> wmsMoves = dbConn.Query<WmsOutstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;
            }
        }
        //根据条码号获取单据信息
        public SysStock GetStockByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.vendor_id, a.vendor_code, a.carton_no, a.locate_id, a.locate_name,a.qty, a.status, a.enabled,b.part_id,b.part_no,b.part_spec, b.part_spec, a.warehouse_id,a.warehouse_name,a.zone_id,a.zone_name,c.enum_desc AS stock_status " +
                                          ",a.unit,a.factory_id,a.factory_code,a.erp_warehouse FROM sys_stock a (nolock) LEFT JOIN dbo.sys_part b (nolock) ON a.part_no = b.part_no LEFT JOIN dbo.sys_enum c (nolock) ON a.status=c.enum_value AND enum_type ='sys_stock_status' WHERE a.carton_no='" + cartonNo + "' ");
                SysStock sysStocks = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());
                return sysStocks;
            }
        }

        public SysStock getProdcuteCode(string prodcuteCode, string orderNo, string loginId, string rackCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("   select b.* from wms_rack_package as a left join sys_stock as b on a.carton_no = b.carton_no where a.rack_no='" + rackCode + "' and b.part_no=(  select part_no from sys_stock where carton_no = '" + prodcuteCode + "') ");
                SysStock sysStocks = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());
                return sysStocks;
            }
        }

        public SysStock getoutStock(string orderNo, string cartonNo, string codeNum, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from sys_stock where  carton_no='" + cartonNo + "'  and enabled='Y' ");
                SysStock sysStocks = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());
                return sysStocks;
            }
        }
        public List<SysStock> CheckMaterialSplit(List<SysStock> oldStock, int splitNum, string loginId, int splitCount, string orderNo, List<WmsOutstockDetail> WmsOutstockDetails)
        {
            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;

                #region 老条码改数量

                //更新条码表，数量
                StringBuilder updateString = new StringBuilder(1024);
                updateString.Append("update sys_stock set qty=@qty, update_userid=@update_userid,update_time=@update_time 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)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

                #endregion

                #region 旧条码 新增条码

                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].VendorCode);
                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", (int)WmsEnumUtil.StockStatus.OUTSTOCKED);
                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", 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);
                #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)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", (int)WmsEnumUtil.StockStatus.OUTSTOCKED);
                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

                #endregion

                #region 更改零星出库明细

                StringBuilder updateString6 = new StringBuilder(1024);
                DynamicParameters updateStringparameters6 = new DynamicParameters();
                updateString6.Append("UPDATE wms_outstock_detail SET out_qty += @out_qty,item_status = case when qty=out_qty + @out_qty then @item_status else item_status  end  ,update_userid=@update_userid,update_time=CONVERT(VARCHAR(20),GETDATE(),21) WHERE order_no=@order_no and  part_no=@part_no ");

                updateStringparameters6.Add("@out_qty", oldStock[0].Qty);
                updateStringparameters6.Add("@item_status", (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED);
                updateStringparameters6.Add("@update_userid", loginId);
                updateStringparameters6.Add("@order_no", orderNo);
                updateStringparameters6.Add("@part_no", oldStock[0].PartNo);

                sqlStrings.Add(updateString6.ToString());
                parameterList.Add(updateStringparameters6);


                #endregion

                #region 新增SN 条码表
                StringBuilder updateString7 = new StringBuilder(1024);
                updateString7.Append("INSERT INTO dbo.wms_outstock_sn ");
                updateString7.Append("( ");
                updateString7.Append("    order_no, ");
                updateString7.Append("    item_no, ");
                updateString7.Append("    part_id, ");
                updateString7.Append("    part_no, ");
                updateString7.Append("    part_spec, ");
                updateString7.Append("    carton_no, ");
                updateString7.Append("    qty, ");
                updateString7.Append("    unit, ");
                updateString7.Append("    status, ");
                updateString7.Append("    factory_id, ");
                updateString7.Append("    factory_code, ");
                updateString7.Append("    enabled, ");
                updateString7.Append("    create_userid, ");
                updateString7.Append("    create_time, ");
                updateString7.Append("    guid ");
                updateString7.Append(") ");
                updateString7.Append("VALUES ");
                updateString7.Append("(   N'" + orderNo + "', ");
                updateString7.Append("    '" + WmsOutstockDetails[0].ItemNo + "',");
                updateString7.Append("    " + oldStock[0].PartId + ", ");
                updateString7.Append("    N'" + oldStock[0].PartNo + "',");
                updateString7.Append("    N'" + oldStock[0].PartSpec + "',");
                updateString7.Append("    '" + oldStock[0].CartonNo + "',");
                updateString7.Append("    " + oldStock[0].Qty + ", ");
                updateString7.Append("    '" + oldStock[0].Unit + "',");
                updateString7.Append("    " + (int)WmsEnumUtil.MoveOrderSnStatus.PROCESSED + ",");//已创建
                updateString7.Append("    " + oldStock[0].FactoryId + ", ");
                updateString7.Append("    N'" + oldStock[0].FactoryCode + "', ");
                updateString7.Append("    'Y', ");
                updateString7.Append("   " + loginId + " ,  ");
                updateString7.Append("    CONVERT(VARCHAR(20),GETDATE(),21), ");
                updateString7.Append("    newid()");
                updateString7.Append("    );");
                sqlStrings.Add(updateString7.ToString());
                DynamicParameters updateStringparameters7 = new DynamicParameters();
                parameterList.Add(updateStringparameters7);
                #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 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.Query<SysStock>(stringBuilder.ToString(), parameters).ToList();
            }
        }

        public List<SysStock> GetRKStockByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select b.* from [wms_rack_package] as a left join sys_stock as b on a.carton_no = b.carton_no  where a.rack_no = '" + cartonNo + "' and a.enabled = 'Y' and b.enabled = 'Y' ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }
        public SysStock GetStockByCartonNoIf(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock a (nolock)   WHERE carton_no='" + cartonNo + "' ");
                SysStock sysStocks = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());
                return sysStocks;
            }
        }
        //根据单据号获取零星出库单信息
        public List<WmsOutstock> GetOutStockByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT * FROM dbo.wms_outstock (nolock) WHERE order_no = '" + orderNo + "' AND order_status in ('10','30') AND order_type in ('" + (int)WmsEnumUtil.OutStockType.EXCEPTION_OUT_STOCK + "','" + (int)WmsEnumUtil.OutStockType.RESERVE_OUT + "') and enabled='Y'");
                List<WmsOutstock> WmsOutstocks = dbConn.Query<WmsOutstock>(stringBuilder.ToString()).ToList();
                return WmsOutstocks;
            }
        }

        /// <summary>
        /// 根据单据号、erp库存地验证是否在单据内
        /// </summary>
        /// <param name="locateName"></param>
        /// <returns></returns>
        public List<WmsOutstockDetail> GetOutstockByOrderNoSrcErp(string orderNo, string srcErpWare)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_outstock_detail (nolock) WHERE order_no='" + orderNo + "' and src_erp_warehouse='" + srcErpWare + "' and enabled='Y'");
                List<WmsOutstockDetail> instockDetailsErp = dbConn.Query<WmsOutstockDetail>(stringBuilder.ToString()).ToList();
                return instockDetailsErp;
            }
        }
        //根据单据号、零件号验证
        public List<WmsOutstockDetail> GetOutstockByOrderNoPartNo(string orderNo, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_outstock_detail (nolock) WHERE order_no='" + orderNo + "' and part_no='" + partNo + "' and enabled='Y'");
                List<WmsOutstockDetail> WmsOutstockDetails = dbConn.Query<WmsOutstockDetail>(stringBuilder.ToString()).ToList();
                return WmsOutstockDetails;
            }
        }


        public List<WmsOutstock> GetOutstockByOrderNo(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,CONVERT(INT, c.qty) qty,CONVERT(INT, c.out_qty) out_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 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_status in ('10','30') AND order_type in ( '" + (int)WmsEnumUtil.OutStockType.EXCEPTION_OUT_STOCK + "','" + (int)WmsEnumUtil.OutStockType.RESERVE_OUT + "' ) and a.enabled='Y'");
                List<WmsOutstock> wmsMoves = dbConn.Query<WmsOutstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;
            }
        }
        public List<WmsOutstockDetail> GetOutstockDetailByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.out_qty)) out_qty,c.part_no,c.part_spec,b.part_spec2,a.vendor_code  FROM dbo.wms_outstock a (nolock) ");
                SqlStringBuilder.Append(" LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append("   left join sys_part as b on c.part_id=b.part_id ");
                SqlStringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status in ('10','30') AND order_type in ( '" + (int)WmsEnumUtil.OutStockType.EXCEPTION_OUT_STOCK + "','" + (int)WmsEnumUtil.OutStockType.RESERVE_OUT + "' ) and a.enabled='Y' group by c.part_no,c.part_spec,b.part_spec2,a.vendor_code ");

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

        //获取完成信息提交数据
        public List<WmsOutstockDetail> GetFinishOutStockDetailByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.Append("SELECT SUM(CONVERT(INT, c.out_qty)) out_qty,c.part_no,src_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no FROM dbo.wms_outstock a (nolock) ");
                //SqlStringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                //SqlStringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status <> '40' and a.enabled='Y' group by c.part_no,src_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no");
                SqlStringBuilder.Append(" SELECT isnull(SUM(CONVERT(decimal, b.out_qty)),0) out_qty,c.part_no,c.src_erp_warehouse,a.factory_code,a.order_no,a.order_type,c.item_no FROM dbo.wms_outstock a (nolock) ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append(" left join (select part_no,sum(qty) out_qty from wms_outstock_sn (nolock) where order_no=@order_no and status=@status and enabled='Y' group by part_no) as b  on c.part_no=b.part_no ");
                SqlStringBuilder.Append("   WHERE a.order_no = @order_no AND order_status <> @order_status and a.enabled='Y'  ");
                SqlStringBuilder.Append("  group by c.part_no,src_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no,a.order_type,c.item_no ");

                DynamicParameters updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@order_no", orderNo);
                updateStringparameters.Add("@status", (int)MoveOrderSnStatus.PROCESSED);
                updateStringparameters.Add("@order_status", (int)MoveOrderDetailStatus.COMPLETED);

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

        public List<WmsOutstockSn> GetFinishOutStockSnByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("  select * from wms_outstock_sn (nolock) where order_no=@order_no and status=@status and enabled='Y' ");

                DynamicParameters updateStringparameters = new DynamicParameters();
                updateStringparameters.Add("@order_no", orderNo);
                updateStringparameters.Add("@status", (int)MoveOrderSnStatus.GENERATED);

                List<WmsOutstockSn> outstockSn = dbConn.Query<WmsOutstockSn>(SqlStringBuilder.ToString(), updateStringparameters).ToList();
                return outstockSn;
            }
        }

        //获取零件号信息
        public SysPart GetPartNOInfoByPartNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT * from sys_part (nolock) where part_no='" + partNo + "' ");
                SysPart sysParts = dbConn.QueryFirstOrDefault<SysPart>(SqlStringBuilder.ToString());
                return sysParts;
            }
        }

        //事务批量执行添加、修改
        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;
                    }
                }
            }
        }
    }
}