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 Estsh.Core.Util;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：库存明细查询
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{

    public class StockDetailQueryRepository : BaseRepository<SysStock>, IStockDetailQueryRepository
    {
        public StockDetailQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法
  
        public Hashtable getZoneList(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                Hashtable result = new Hashtable();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@GroupColumn", " warehouse_id, warehouse_name,zone_id,zone_name");
                Params.Add("@Table", " sys_stock (nolock)  ");
                Params.Add("@Column", " warehouse_id, warehouse_name,zone_id,zone_name,SUM(qty) qty ,COUNT(1) Printed ");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 1);

                List<SysStock> dataList = dbConn.Query<SysStock>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", Params.Get<int>("@TotalCount"));

                return result;
            }
        }

        public List<SysStock> getLocateListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = string.Format(@" SELECT warehouse_id, warehouse_name,zone_id,zone_name,locate_id,locate_name,SUM(qty) qty ,COUNT(1)   Printed FROM
                                               sys_stock (nolock) where " + strWhere
                                               + " GROUP BY warehouse_id, warehouse_name,zone_id,zone_name,locate_id,locate_name");
                List<SysStock> dataList = dbConn.Query<SysStock>(sql).ToList();
                return dataList;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public List<SysStock> getListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = string.Format(@" SELECT a.*, b.enum_name as stock_status,c.part_spec2 FROM
                                               sys_stock a (nolock) left join sys_enum b on a.status= b.enum_value  
                                                and b.enum_type = 'sys_stock_status' 
                                               left join sys_part c (nolock) on a.part_no=c.part_no where " + strWhere);
                List<SysStock> dataList = dbConn.Query<SysStock>(sql).ToList();
                return dataList;
            }
        }

        public List<KeyValueResult> GetStockType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select enum_value as [value] ,enum_name as [key] from sys_enum where enum_type = 'sys_stock_status' order by   convert(int, enum_value) ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        #endregion  成员方法

        /// <summary>
        /// 判断用户输入的零件号是否存在
        /// BY NOAH
        /// </summary>
        /// <param name="part_no"></param>
        /// <returns></returns>
        public String isExsitPart_no(String part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string part_id = null;
                string SqlString = "select top 1 part_id from sys_part (nolock) where enabled='Y' and part_no=@partNo";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@partNo", part_no);
                List<SysPart> dt = dbConn.Query<SysPart>(SqlString, Params).ToList();
                if (dt.Count < 1)
                {
                    return part_id;
                }
                else
                {
                    part_id = dt[0].PartId.ToString();
                    return part_id;
                }
            }
        }

        /// <summary>
        /// 获取零件号
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetPartNo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select part_no as [value],part_no as [key] from sys_part (nolock) where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取库位名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetLocateName()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select locate_name as [value],locate_name as [key] from sys_locate where enabled='Y'  ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取枚举值
        /// </summary>
        /// <param name="enumtype">枚举类型</param>
        /// <returns></returns>
        public List<KeyValueResult> GetSysEnum(string enumtype)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string Sqlstring = "select enum_desc as [key],enum_value as [value] from sys_enum where enum_type=@enum_type and enabled='Y' ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@enum_type", enumtype);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(Sqlstring, values).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetWareHouseName()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select warehouse_id as [value] , warehouse_name,warehouse_desc as [key] from sys_warehouse where enabled='Y' ";
                try
                {
                    List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                    return result;
                }
                catch
                {
                    return null;
                }
            }
        }

        public List<StockDetailQuery> getExportList(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = string.Format(@" SELECT a.ruid , a.vendor_id , a.vendor_code , a.carton_no , a.carton_type , a.part_id , a.part_no , a.part_spec , a.lot_no , a.fix_lot_no , a.status ,cast( a.qty as int ) qty , cast(a.snp_qty as int ) snp_qty , a.locate_id , a.locate_name , a.group_no , a.erp_warehouse , a.date_code , a.qms_status , a.ref_order_no , a.unit , a.dock , a.warehouse_id , a.warehouse_name , a.zone_id , a.zone_name , a.printed , a.print_time , a.remark , a.factory_id , a.factory_code , a.enabled , a.create_userid , a.create_time , a.update_userid , a.update_time , a.guid , a.timestamp , a.count_qty , a.reveice_time , a.qc_finish_time , a.tray_no , a.channel_number , a.mtoc , a.mtoc_desc , a.cartype_name , a.pick_up_tab , b.enum_name as stock_status,c.part_spec2  FROM
                                               sys_stock a (nolock) left join sys_enum b on a.status= b.enum_value  
                                                and b.enum_type = 'sys_stock_status' 
                                               left join sys_part c (nolock) on a.part_no=c.part_no where " + strWhere);
                List<StockDetailQuery> dataList = dbConn.Query<StockDetailQuery>(sql).ToList();
                return dataList;
            }
        }
        public int EnableData(string id, string empId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_stock set Enabled='Y',update_userid=" + empId + ", update_time = CONVERT(varchar(50), GETDATE(), 21)  WHERE ruid in (" + id + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }


    }
}