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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：采购周计划条码导出数据访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
	/// <summary>
    /// 处理采购周计划条码导出模块的业务数据
	/// </summary>
	public class PurchaseExportRepository : BaseRepository<SysVendor>, IPurchaseExportRepository
    {
        public PurchaseExportRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取要货单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getOrderList(String create_time)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strSql = "SELECT DISTINCT iss_seq as [value],iss_seq as [key] FROM g_buy_weekly WHERE create_time=@create_time";
                DynamicParameters param = new DynamicParameters();
                param.Add("@create_time", create_time);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql, param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取要货单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> geVendorList(String iss_seq)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strSql = "SELECT DISTINCT vendor_code as [value],vendor_code as [key] FROM sys_vendor WHERE vendor_id IN (SELECT DISTINCT vendor_id FROM sys_stock WHERE iss_seq=@iss_seq) and enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@iss_seq", iss_seq);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql, param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得条码数据
        /// </summary>
        /// <param name="issSeq"></param>
        /// <param name="vendorCode"></param>
        /// <returns></returns>
        public List<SysStock> GetBarcodeData(string issSeq, string vendorCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sql = new StringBuilder(1024);
                sql.Remove(0, sql.Length);
                sql.Append("SELECT a.iss_seq,a.carton_no,a.qty,a.part_id,b.part_no,b.part_spec,a.lot_no,a.vendor_id,c.vendor_code ");
                sql.Append(" ,c.vendor_name,a.buy_no,a.enabled FROM sys_stock AS a LEFT JOIN sys_part AS b ON a.part_id=b.part_id ");
                sql.Append(" LEFT JOIN sys_vendor AS c ON a.vendor_id=c.vendor_id WHERE a.enabled='Y' AND a.iss_seq=@iss_seq ");
                if (vendorCode != "all")
                {
                    sql.Append(" AND a.vendor_id=(SELECT TOP 1 vendor_id FROM sys_vendor WHERE vendor_code=@vendor_code) ");
                }
                sql.Append(" ORDER BY b.part_no,a.lot_no,carton_no");
                DynamicParameters param = new DynamicParameters();
                param.Add("@iss_seq", issSeq);
                param.Add("@vendor_code", vendorCode);

                List<SysStock> result = dbConn.Query<SysStock>(sql.ToString(), param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得供应商密码
        /// </summary>
        /// <param name="vendorCode"></param>
        /// <returns></returns>
        public string QuerySupplierPwd(string vendorCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strSql = "SELECT TOP 1 vendor_pwd FROM sys_vendor WHERE vendor_code=@vendorCode AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@vendorCode", vendorCode);

                object obj = dbConn.ExecuteScalar(strSql, param);
                if (obj == null)
                {
                    return "";
                }
                return obj.ToString();
            }
        }

		#endregion  成员方法
	}
}