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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：库存调整模块DAL层
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class StockAdjustDefineRepository : BaseRepository<SysStock>, IStockAdjustDefineRepository
    {
        public StockAdjustDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        public List<SysStock> GetAllData(string _where)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.ruid,c.part_no, ");
                SqlStringBuilder.Append("       b.locate_name,a.carton_no,a.qty,a.lot_no ");
                SqlStringBuilder.Append("FROM   dbo.sys_stock a ");
                SqlStringBuilder.Append("       LEFT JOIN dbo.sys_locate b ON a.locate_id = b.locate_id ");
                SqlStringBuilder.Append("       LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("WHERE  a.status in ('E') and a.enabled='Y' ");

                if (!string.IsNullOrEmpty(_where))
                {
                    SqlStringBuilder.Append(_where);
                }
                List<SysStock> result = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 将记录插入到历史库存表
        /// </summary>
        /// <param name="LotNo">编号</param>
        /// <returns></returns>
        public bool MoveStock(string LotNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " insert into sys_ht_stock select * from sys_stock where lot_no = @lotNo";

                DynamicParameters values = new DynamicParameters();
                values.Add("@lotNo", LotNo);
                int result = dbConn.Execute(SqlString, values);
                return result>0;
            }
        }

        /// <summary>
        /// 判断箱号是否存在
        /// </summary>
        /// <param name="carton_no">箱号</param>
        /// <returns>ture 不存在 false 存在</returns>
        public bool IsExist(string carton_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " select top 1 1 from sys_stock where carton_no = @carton_no ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@carton_no", carton_no);
                object obj = dbConn.ExecuteScalar(SqlString,values);
                return obj == null;
            }
        }

        /// <summary>
        /// 判断库存表中该库位信息是否存在
        /// </summary>
        /// <param name="locate_id">库位ID</param>
        /// <returns>ture 不存在 false 存在</returns>
        public bool Exist(string locateID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " select top 1 1 from sys_stock where locate_id = @locateID ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@locateID", locateID);
                object obj = dbConn.ExecuteScalar(SqlString, values);
                return obj == null;
            }
        }

        /// <summary>
        /// 更新库位是否为空的状态
        /// </summary>
        /// <param name="locateID">库位编号</param>
        public void UpdateLocateEmpty(string locateID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " update sys_locate set locate_empty='Y' where locate_id = @locateId ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@locateId", locateID);
                object obj = dbConn.Execute(SqlString, values);
            }
        }

        /// <summary>
        /// 批量调整库存
        /// </summary>
        /// <param name="dt">需要调整的数据集</param>
        /// <returns></returns>
        public string AdjustStock(List<StockAdjustDefine> dt,int userID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string Message = string.Empty;

                foreach (StockAdjustDefine dr in dt)
                {
                    Hashtable ht = new Hashtable();
                    double qty = 0;
                    if (string.IsNullOrEmpty(dr.Qty.ToString()))
                    {
                        qty = 0;
                    }
                    else
                    {
                        qty = Convert.ToDouble(dr.Qty.ToString());
                    }

                    double stockQty = Convert.ToDouble(dr.stockQty.ToString());
                    DynamicParameters Parameters = new DynamicParameters();
                    Parameters.Add("@orderNo", "");
                    Parameters.Add("@cartonNo", dr.CartonNo.ToString());
                    Parameters.Add("@qty", qty);
                    Parameters.Add("@locateName", dr.LocateName.ToString());
                    Parameters.Add("@partNo", dr.PartNo.ToString());
                    Parameters.Add("@userID", userID);
                    Parameters.Add("@stockQty", stockQty);
                    Parameters.Add("@locate", dr.SysLocateName.ToString());
                    Parameters.Add( "@message",null, DbType.String, ParameterDirection.Output ,255);

                    List<dynamic> depts = dbConn.Query<dynamic>("AdjustStock", Parameters, commandType: CommandType.StoredProcedure).ToList();
                    string mess = Parameters.Get<string>("@message");
                    if (string.IsNullOrEmpty(mess))
                        return string.Empty;

                    Message = mess;
                }
                return Message;
            }
        }

        /// <summary>
        /// 变更事务状态
        /// </summary>
        /// <param name="lotNo"></param>
        /// <returns></returns>
        public bool TransType(string lotNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " update sys_stock set trans_type = 'Delete' where lot_no=@lot_no ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@lot_no", lotNo);
                int result = dbConn.Execute(SqlString, values);
                return result>0;
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="qty">数量</param>
        /// <param name="lot_no">箱号</param>
        /// <returns></returns>
        public bool update(string qty,string lot_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "UPDATE sys_stock SET qty=@qty WHERE ruid=@lot_no";
                DynamicParameters values = new DynamicParameters();
                values.Add("@qty", qty);
                values.Add("@lot_no", lot_no);
                int num = dbConn.Execute(sql, values);
                if (num > 0)
                    return true;
                else
                    return false;
            }
        } 
    }
}