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

namespace Estsh.Core.Wms.Repositories
{
    public class ReplaceSerialNumberRepository : BaseRepository<BaseEntity>, IReplaceSerialNumberRepository
    {
        public ReplaceSerialNumberRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        /// <summary>
        /// 获取更换条码的列表
        /// </summary>
        /// <returns></returns>
        public List<SysStock> GetReplaceSerialNumber()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.new_serial_number,a.old_serial_number,b.* from MES_WMS_REPLACE_SERIAL_NUMBER (nolock) as a left join sys_stock (nolock) as b on a.old_serial_number=b.carton_no ");
                SqlStringBuilder.Append("  where  a.syn_flg = '10'  ");
                return dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
            }
        }

        /// <summary>
        /// 获取订单编号
        /// </summary>
        /// <returns></returns>
        public string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

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

        /// <summary>
        /// 处理返工返修物料拉动的数据
        /// </summary>
        /// <param name="sapSu"></param>
        /// <returns></returns>
        public int ChangeSerialNumber(List<SysStock> mesSerialNumberList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                DynamicParameters parameters = new DynamicParameters();
                int count = 0;
                foreach (var item in mesSerialNumberList)
                {
                    if (item.CartonNo == null)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("update MES_WMS_REPLACE_SERIAL_NUMBER set syn_flg='30',err_msg=@err_msg,update_time=@update_time  where old_serial_number=@old_serial_number ");//更新同步状态
                        sqlList.Add(SqlStringBuilder.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@old_serial_number", item.OldSerialNumber);//老条码
                        parameters.Add("@err_msg", "处理失败，未找到此零件条码:" + item.OldSerialNumber);
                        parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                        parametersList.Add(parameters);
                        ExecuteTransaction(sqlList, parametersList);
                    }
                    SqlStringBuilder = new StringBuilder(1024);
                    parameters = new DynamicParameters();

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("update sys_stock set carton_no=@NewSerialNumber,update_userid=@updateUserid,update_time=@updateTime where carton_no=@OldSerialNumber ");
                    sqlList.Add(SqlStringBuilder.ToString());

                    parameters = new DynamicParameters();
                    parameters.Add("@NewSerialNumber", item.NewSerialNumber);
                    parameters.Add("@updateUserid", 0);//更新人
                    parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间
                    parameters.Add("@OldSerialNumber", item.OldSerialNumber);
                    parametersList.Add(parameters);

                    #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.MES_CARTON_CHANGE);
                    updateStringparameters4.Add("@carton_no", item.NewSerialNumber);
                    updateStringparameters4.Add("@part_id", item.PartId);
                    updateStringparameters4.Add("@part_no", item.PartNo);
                    updateStringparameters4.Add("@part_spec", item.PartSpec);
                    updateStringparameters4.Add("@src_locate_id", item.LocateId);
                    updateStringparameters4.Add("@src_locate_name", item.LocateName);
                    updateStringparameters4.Add("@dest_locate_id", item.LocateId);
                    updateStringparameters4.Add("@dest_locate_name", item.LocateName);
                    updateStringparameters4.Add("@old_qty", item.Qty);
                    updateStringparameters4.Add("@new_qty", item.Qty);
                    updateStringparameters4.Add("@trans_qty", item.Qty);
                    updateStringparameters4.Add("@old_status", item.Status);
                    updateStringparameters4.Add("@new_status", item.Status);
                    updateStringparameters4.Add("@old_qms_status", item.QmsStatus);
                    updateStringparameters4.Add("@new_qms_status", item.QmsStatus);
                    updateStringparameters4.Add("@unit", item.Unit);
                    updateStringparameters4.Add("@factory_id", item.FactoryId);
                    updateStringparameters4.Add("@factory_code", item.FactoryCode);
                    updateStringparameters4.Add("@src_erp_warehouse", item.ErpWarehouse);
                    updateStringparameters4.Add("@dest_erp_warehouse", item.ErpWarehouse);
                    updateStringparameters4.Add("@src_warehouse_id", item.WarehouseId);
                    updateStringparameters4.Add("@src_warehouse_name", item.WarehouseName);
                    updateStringparameters4.Add("@dest_warehouse_id", item.WarehouseId);
                    updateStringparameters4.Add("@dest_warehouse_name", item.WarehouseName);
                    updateStringparameters4.Add("@src_zone_id", item.ZoneId);
                    updateStringparameters4.Add("@src_zone_name", item.ZoneName);
                    updateStringparameters4.Add("@dest_zone_id", item.ZoneId);
                    updateStringparameters4.Add("@dest_zone_name", item.ZoneName);
                    updateStringparameters4.Add("@enabled", item.Enabled);
                    updateStringparameters4.Add("@update_userid", 0);
                    updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters4.Add("@create_userid", 0);
                    updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters4.Add("@guid", Guid.NewGuid());

                    sqlList.Add(updateString4.ToString());
                    parametersList.Add(updateStringparameters4);
                    #endregion
                    bool staResult = ExecuteTransaction(sqlList, parametersList);

                    sqlList = new List<string>();
                    parametersList = new List<DynamicParameters>();
                    if (staResult)
                    {
                        count += 1;

                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("update MES_WMS_REPLACE_SERIAL_NUMBER set syn_flg='20',err_msg=@err_msg,update_time=@update_time  where old_serial_number=@old_serial_number ");//更新同步状态
                        sqlList.Add(SqlStringBuilder.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@old_serial_number", item.OldSerialNumber);//老条码
                        parameters.Add("@err_msg", "同步成功！");
                        parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                        parametersList.Add(parameters);
                        ExecuteTransaction(sqlList, parametersList);
                    }
                    else
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("update MES_WMS_REPLACE_SERIAL_NUMBER set syn_flg='30',err_msg=@err_msg,update_time=@update_time  where old_serial_number=@old_serial_number ");//更新同步状态
                        sqlList.Add(SqlStringBuilder.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@old_serial_number", item.OldSerialNumber);//老条码
                        parameters.Add("@err_msg", "同步错误，请检查接口数据！");
                        parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                        parametersList.Add(parameters);
                        ExecuteTransaction(sqlList, parametersList);
                    }

                }
                return count;

            }
        }
    }
}