using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using DapperExtensions.SqlServerExt;
using HH.AMS.Ex.Interface;
using HH.AMS.Ex.Factroy;
using Hanhe.iWCS.Common;
using HH.AMS.Common;

namespace HH.AMS.Ex.MSSQLServices
{
    public class AutoBomServices : IAutoBomInterface
    {
        /// <summary>
        /// 获取货位信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetLocationList()
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = @"select * from tn_ab_stock_location_00";
                    dt = dapper.GetDataTable(sql);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                CMMLog.Error("GetLocationList ---errMsg=" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 获取货位与AGV站点关系信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetLocationAgvCodeList()
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = @"select * from tn_ab_b_location_agvcode";
                    dt = dapper.GetDataTable(sql);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                CMMLog.Error("GetLocationAgvCodeList ---errMsg=" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 获取仓库
        /// </summary>
        /// <returns></returns>
        public DataTable GetStock()
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = @"select * from tn_ab_stock";
                    dt = dapper.GetDataTable(sql);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                CMMLog.Error("GetStock ---errMsg=" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 获取数据字典
        /// </summary>
        /// <returns></returns>
        public DataTable GetDictList()
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = @"select * from dbo.tn_ab_b_dict";
                    dt = dapper.GetDataTable(sql);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                CMMLog.Error("GetDictList ---errMsg=" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 根据仓库获取排
        /// </summary>
        /// <param name="stockName"></param>
        /// <returns></returns>
        public DataTable GetAllRow(string stockName)
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = "SELECT CN_S_ROW FROM TN_AB_B_STOCK_STRUCTURE where  CN_S_STOCK_CODE='" + stockName + "' group by CN_S_ROW";
                    dt = dapper.GetDataTable(sql);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                CMMLog.Error("GetAllRow ---errMsg=" + ex.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 获取货位的分页数据
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="strWhere"></param>
        /// <param name="orderBy"></param>
        /// <param name="total"></param>
        /// <param name="totalPage"></param>
        /// <returns></returns>
        public DataTable GetLocationList(int pageIndex, int pageSize, string strWhere, string orderBy, out int total, out int totalPage)
        {
            DataTable dt = new DataTable();
            try
            {
                using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString))
                {
                    string sql = @"(select c.CN_S_STOCK_NAME,a.CN_S_STOCK_CODE,
                          a.CN_S_LOCATION_CODE,
                          a.CN_N_MAX_STORE_NUM,
                          a.CN_C_IS_DO_PARTITION,
                          a.CN_S_NOTE,
                          a.CN_C_IS_ENABLE,
                          a.CN_S_ROADWAY,
                          a.CN_S_ROW,
                          a.CN_S_COL,
                          a.CN_S_FLOOR,
                          a.CN_S_TYPE,
                          a.CN_N_AGV_LOCATION,
                          a.CN_S_CREATOR,
                          a.CN_S_CREATOR_BY,
                          a.CN_T_CREATE,
                          a.CN_S_MODIFY,
                          a.CN_S_MODIFY_BY,
                          a.CN_T_MODIFY,
                          a.CN_S_POSITION,
                          a.CN_GUID,
                          a.CN_S_STATUS,
                          a.CN_S_LOCATION_NAME,
                          a.CN_S_AREA_CODE,b.CN_S_USE_STATE,b.CN_S_LOCATION_STATE from tn_ab_stock_location a 
                    left join tn_ab_b_location_ext b on a.CN_S_STOCK_CODE=b.CN_S_STOCK_CODE and a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
                    join tn_ab_stock c on a.CN_S_STOCK_CODE=c.CN_S_STOCK_CODE) t ";
                    var param = new DynamicParameters();
                    param.Add("TableName", sql);
                    param.Add("PageSize", pageSize);
                    param.Add("PageIndex", pageIndex);
                    param.Add("WhereStr", strWhere);
                    param.Add("OrderByStr", orderBy);
                    param.Add("TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                    param.Add("TotalRecord", 0, DbType.Int32, ParameterDirection.Output);
                    IDataReader dr = dapper.ExecuteReader("prc_query", param, null, null, CommandType.StoredProcedure);
                    dt = DataConvert.DataTableToIDataReader(dr);
                    total = param.Get<int>("TotalRecord");
                    totalPage = param.Get<int>("TotalPage");
                    return dt;
                }
            }
            catch (Exception ex)
            {
                total = 0;
                totalPage = 0;
                CMMLog.Error("GetLocationList ---errMsg=" + ex.ToString());
                return dt;
            }
        }


		public DataTable GetDataTable(string sql) {
			DataTable dt = new DataTable();
			try {
				using (var dapper = Connections.Instance.GetConnection(DBConnetctionString.GetDBConnetctionString)) {
					dt = dapper.GetDataTable(sql);
					return dt;
				}
			}
			catch (Exception ex) {
				CMMLog.Error("GetDataTable ---errMsg=" + ex.ToString());
			}
			return dt;
		}
	}
}