using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using Microsoft.Extensions.Primitives;
using System.Data;
using System.Text;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 快速入库
    /// </summary>
    public class QuicklyInStockRepository : BaseRepository<BaseEntity>, IQuicklyInStockRepository
    {
        public QuicklyInStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        //根据库位获取库位信息
        public List<SysLocate> GetLocateByName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.locate_id,a.locate_name,a.locate_type,a.locate_capacity,b.enum_desc as locate_typeDesc,a.enabled FROM dbo.sys_locate a (nolock) LEFT JOIN sys_enum b (nolock) ON b.enum_type='sys_locate_type' and a.locate_type=b.enum_value "
                                     + "WHERE a.locate_name = '" + locateName + "'");
                List<SysLocate> sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).ToList();
                return sysLocates;
            }
        }

        //根据库位获取条码信息
        public List<SysStock> GetStockQtyByLocate(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("  SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE locate_name = '" + locateName + "' AND status IN ('50','60','80') and enabled='Y' ");
                List<SysStock> sysStocksLocateCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();//库位在库数
                return sysStocksLocateCount;
            }
        }
        //根据分组号分组获取所有零件号
        public List<SysStock> GetStockPartByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.part_no FROM dbo.sys_stock a (nolock) WHERE group_no = '" + groupNo + "'  and status=40 and a.enabled='Y'  GROUP BY a.part_no ");
                List<SysStock> sysstockByGroupNoCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysstockByGroupNoCount;
            }
        }

        //根据条码号获取条码信息
        public List<SysStock> GetStockByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a (nolock) left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE carton_no='" + cartonNo + "'");
                List<SysStock> sysStocksLocateCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocksLocateCount;
            }
        }
        //根据库位名称获取库位信息
        public List<SysLocate> GetLocateAndPartByLocate(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();

                stringBuilder.Append("SELECT a.*,c.erp_warehouse  FROM dbo.sys_locate a (nolock)  ");
                stringBuilder.Append("LEFT JOIN sys_locate_part b (nolock) ON a.locate_id = b.locate_id   ");
                stringBuilder.Append("LEFT JOIN sys_zone c (nolock) on a.zone_id = c.zone_id ");
                stringBuilder.Append("WHERE a.locate_name = @locateName and a.enabled='Y' ");

                List<SysLocate> sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString(),new { locateName = locateName }).ToList();
                return sysLocates;
            }
        }

        //根据分组号获取条码数量和
        public List<SysStock> GetStockQtyByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "' and status in (40)  and enabled='Y' ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        //根据分组号获取条码信息
        public List<SysStock> GetStockByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "' and status in (40)  and enabled='Y' ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        public List<SysStock> GetStockInByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.part_no,a.part_spec,p.part_spec2,sum(qty) qty,locate_name,count(1) carton_qty FROM dbo.sys_stock a (nolock) left join sys_part p (nolock) on a.part_id=p.part_id WHERE carton_no = '" + cartonNo + "' and status in (50)  and a.enabled='Y' group by a.part_no,a.part_spec,p.part_spec2,locate_name ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        ////根据分组号获取入库条码信息
        //public List<SysStock> GetStockInByGroupNo(string groupNo)
        //{
        //    using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
        //    {
        //        dbConn.Open();
        //        StringBuilder stringBuilder = new StringBuilder();
        //        stringBuilder.Append("SELECT * FROM dbo.sys_stock WHERE group_no = '" + groupNo + "' and status in (50)  and enabled='Y' ");
        //        List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
        //        return sysStocks;
        //    }
        //}

        //根据分组号获取入库条码信息
        public List<SysStock> GetStockInByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.part_no,a.part_spec,p.part_spec2,sum(qty) qty,locate_name,count(1) carton_qty FROM dbo.sys_stock a (nolock) left join sys_part p (nolock) on a.part_id=p.part_id WHERE group_no='" + groupNo + "' and status in (50)  and a.enabled='Y' group by a.part_no,a.part_spec,p.part_spec2,locate_name");
                List <SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        //根据库位获取在库条码信息
        public List<SysStock> GetStockByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE locate_name='" + locateName + "' AND status in (50,60,80) AND enabled='Y'");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        //事务批量执行添加、修改
        public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
    }
}