using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using AutoTurnOver.Models;
using AutoTurnOver.Models.Common;

namespace AutoTurnOver.DB
{
    public class common:connectionHelper
    {
        /// <summary>
        /// 获取仓库列表
        /// </summary>
        /// <param name="type">仓库类型</param>
        /// <param name="areaId">区域</param>
        /// <returns></returns>
        public static List<dc_base_warehouse> ListWareHouse(string type,int? areaId)
        {
            var sql = " select * from dc_base_warehouse where 1=1 ";

            if (areaId.HasValue)
            {
                sql += " and area_id=" + areaId.Value;
            }

            if (!string.IsNullOrWhiteSpace(type))
            {
                sql += " and hq_type=" + $"'{type}'";
            }


            return _connection.Query<Models.dc_base_warehouse>(sql).AsList();
        }

        public static dc_base_sku GetPrductBySku(string bailun_sku)
        {
            var sql = "select * from dc_base_sku where bailun_sku=@bailun_sku ";
            return _connection.QueryFirstOrDefault<dc_base_sku>(sql,new { bailun_sku });
        }

        public static dc_base_warehouse GetWareHouseByCode(string code)
        {
            var sql = "select * from dc_base_warehouse where 1=1 ";
            if (!string.IsNullOrWhiteSpace(code))
            {
                sql += (" and warehouse_code='" + code + "'");
            }
            return _connection.QueryFirstOrDefault<dc_base_warehouse>(sql);
        }

        /// <summary>
        /// 获取仓库类型列表
        /// </summary>
        /// <returns></returns>
        public static List<mwarehousetype> ListWareHouseType()
        {
            var sql = "select hq_type as name from dc_base_warehouse where hq_type!='' and hq_type is not null GROUP BY hq_type";
            return _connection.Query<mwarehousetype>(sql).AsList();
        }

        /// <summary>
        /// 获取仓库区域列表
        /// </summary>
        /// <returns></returns>
        public static List<mwarehouseArea> ListWareHouseArea()
        {
            var sql = "select area_id as id,area_name as name from dc_base_warehouse GROUP BY area_id";
            return _connection.Query<mwarehouseArea>(sql).AsList();
        }

        /// <summary>
        /// 查询供应商 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static List<supplier_dto> SearchSupplier(string name)
        {
            if (string.IsNullOrWhiteSpace(name))
            {
                return new List<supplier_dto>();
            }
            else
            {
                var sql = " select suppliers_name as 'name' from dc_base_sku where suppliers_name like @suppliers_name GROUP BY suppliers_name ";
                return _connection.Query<supplier_dto>(sql,new { suppliers_name = $"%{name}%" }).AsList();
            }
        }

        /// <summary>
        /// 查询采购员
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static List<purchase_user_dto> SearchPurchaseUser(string name)
        {
            if (string.IsNullOrWhiteSpace(name))
            {
                return new List<purchase_user_dto>();
            }
            else
            {
                var sql = " select buyer_name as 'name' from dc_base_sku where buyer_name like @buyer_name GROUP BY buyer_name ";
                return _connection.Query<purchase_user_dto>(sql, new { buyer_name = $"%{name}%" }).AsList();
            }
        }

    }
}
