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
* 描述：处理菜单模块的业务数据
* 修改时间：2022.06.22
* 修改日志：系统迭代升级
*
**************************************************************************************************/
namespace Estsh.Core.Repositories
{

    public class CartonTrackRepository : BaseRepository<SysHtStock>, ICartonTrackRepository
    {
        public CartonTrackRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                if (PageSize == 0)
                    return result;
                DynamicParameters parameters = new DynamicParameters();

                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", "  sys_stock a (nolock)  left join sys_enum    g   ON a.status=g.enum_value and g.enum_type = 'sys_stock_status'  " +
                                            " left join sys_vendor b ON a.vendor_id = b.vendor_id" +
                                            " left join sys_part c ON a.part_id = c.part_id" +
                                            " left join sys_locate d ON d.locate_id = a.locate_id" +
                                            " left join sys_enum h ON d.locate_type = h.enum_value and h.enum_type = 'sys_locate_type'" +
                                            " left join sys_emp ct ON ct.emp_id = a.create_userid" +
                                            " left join sys_emp f ON f.emp_id = a.update_userid    ");
                parameters.Add("@Column", " a.carton_no,a.vendor_id,b.vendor_code,b.vendor_name,a.part_id,c.part_no,c.part_spec,c.part_spec2,c.unit," +
                                           "a.qty, a.snp_qty, a.lot_no, a.status, g.enum_name stock_status," +
                                          "a.locate_id, a.locate_name, h.enum_name locate_type_desc, a.zone_id, a.zone_name, a.warehouse_id, a.warehouse_name," +
                                           "a.factory_id, a.factory_code, ct.emp_no create_user, a.create_time, f.emp_no update_user, a.update_time, a.enabled");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

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

        public List<CartonTrack> getExportList(string strWhere, string orderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" select  a.carton_no,a.vendor_id,b.vendor_code,b.vendor_name,a.part_id,c.part_no,c.part_spec,c.unit, ");
                SqlStringBuilder.Append("           CAST(a.qty as int) qty, CAST(a.snp_qty as int) snp_qty, a.lot_no, a.status, g.enum_name stock_status, ");
                SqlStringBuilder.Append("          a.locate_id, a.locate_name, h.enum_name locate_type, a.zone_id, a.zone_name, a.warehouse_id, a.warehouse_name, ");
                SqlStringBuilder.Append("           a.factory_id, a.factory_code, ct.emp_no create_user, a.create_time, f.emp_no update_user, a.update_time, a.enabled ");
                SqlStringBuilder.Append("		  from sys_stock a (nolock) left join sys_enum    g   ON a.status=g.enum_value and g.enum_type = 'sys_stock_status'   ");
                SqlStringBuilder.Append("             left join sys_vendor b ON a.vendor_id = b.vendor_id ");
                SqlStringBuilder.Append("             left join sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("             left join sys_locate d ON d.locate_id = a.locate_id ");
                SqlStringBuilder.Append("             left join sys_enum h ON d.locate_type = h.enum_value and h.enum_type = 'sys_locate_type' ");
                SqlStringBuilder.Append("             left join sys_emp ct ON ct.emp_id = a.create_userid ");
                SqlStringBuilder.Append("             left join sys_emp f ON f.emp_id = a.update_userid where " + strWhere + orderBy);

                List<CartonTrack> result = dbConn.Query<CartonTrack>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectCartonTrack()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select part_id as [value] ,part_no as [key] from sys_part where  part_type = '1' and enabled='Y'  ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetLocateType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select enum_value [value],enum_desc as [key]  from sys_enum  where enum_type = 'sys_locate_type' and enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        #endregion  成员方法
    }
}