Estsh-Yiri/Estsh.Core.Wms/Repositories/ScatteredInStockRepository.cs

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;
using static Estsh.Core.Model.EnumUtil.WmsEnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 零星入库
    /// </summary>
    public class ScatteredInStockRepository : BaseRepository<BaseEntity>, IScatteredInStockRepository
    {
        public ScatteredInStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取零星入库单据信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsInstock> GetScatteredInStockOrderList(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT   a.order_no,b.enum_desc AS order_type_desc,a.create_time,d.emp_no,a.factory_code,a.enabled,a.order_status FROM dbo.wms_instock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type = b.enum_value AND b.enum_type = 'wms_instock_order_type' ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid = d.emp_id ");
                SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' AND order_status in ('20','30') AND order_type in ( '" + (int)WmsEnumUtil.InStockType.EXCEPTION_IN_STOCK +"','"+ (int)WmsEnumUtil.InStockType.RESERVE_IN + "') and a.enabled = 'Y' ");
                SqlStringBuilder.Append("  order by  case when a.order_status='30' then 0 else 1 end, a.create_time  ");
                List<WmsInstock> wmsMoves = dbConn.Query<WmsInstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;
            }
        }


        /// <summary>
        /// 获取零星入库单据信息通过订单号
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsInstock> GetScatteredInStockOrderListByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.order_no,b.enum_desc AS order_type_desc,CONVERT(INT, c.qty) qty,CONVERT(INT, c.rec_qty) rec_qty,c.part_no ,a.create_userid,a.create_time,c.dest_zone_id,c.dest_zone_name,d.emp_no,a.factory_code,p.part_spec FROM dbo.wms_instock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type=b.enum_value AND	b.enum_type='wms_instock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.wms_instock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid=d.emp_id ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON c.part_no=p.part_no ");
                SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' AND order_type in ('" + (int)WmsEnumUtil.InStockType.EXCEPTION_IN_STOCK +"','"+ (int)WmsEnumUtil.InStockType.RESERVE_IN + "') and a.enabled='Y' ");

                List<WmsInstock> wmsMoves = dbConn.Query<WmsInstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;

            }
        }
        /// <summary>
        /// 库位信息
        /// </summary>
        /// <param name="locateName"></param>
        /// <returns></returns>
        public SysLocate GetLocateByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.*,b.erp_warehouse FROM dbo.sys_locate (nolock) as a left join sys_zone as b on a.zone_id= b.zone_id  WHERE locate_name = '" + locateName + "'");
                SysLocate sysLocates = dbConn.QueryFirstOrDefault<SysLocate>(stringBuilder.ToString());
                return sysLocates;
            }
        }

        //根据库位获取条码库存数量
        public SysStock GetStackQtySumByLocateName(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 ('90') and enabled='Y' ");
                SysStock sysStocksLocateCount = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());//库位在库数
                return sysStocksLocateCount;
            }
        }

        /// <summary>
        /// 判断库存地是否在单据内
        /// </summary>
        /// <param name="locateName"></param>
        /// <returns></returns>
        public List<WmsInstockDetail> GetInstockByOrderNoDestErp(string orderNo, string destErpWare)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_instock_detail (nolock) WHERE order_no='" + orderNo + "' and dest_erp_warehouse='" + destErpWare + "' and enabled='Y'");
                List<WmsInstockDetail> instockDetailsErp = dbConn.Query<WmsInstockDetail>(stringBuilder.ToString()).ToList();
                return instockDetailsErp;
            }
        }
        //根据条码号获取库存信息
        public SysStock GetStockByCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.vendor_id, a.vendor_code, a.carton_no, a.locate_id, a.locate_name,a.qty, a.status, a.enabled,b.part_id,b.part_no,b.part_spec, b.part_spec, a.warehouse_id,a.warehouse_name,a.zone_id,a.zone_name,c.enum_desc AS stock_status " +
                                      ",a.unit,a.factory_id,a.factory_code FROM sys_stock a (nolock) LEFT JOIN dbo.sys_part b (nolock) ON a.part_no = b.part_no LEFT JOIN dbo.sys_enum c (nolock) ON a.status=c.enum_value AND enum_type ='sys_stock_status' WHERE a.carton_no='" + cartonNo + "' ");
                SysStock sysStocks = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());
                return sysStocks;
            }
        }
        //根据库位获取库位信息
        public SysLocate GetLocateInfoByLocateName(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,a.zone_id,a.zone_name,a.warehouse_id,a.warehouse_name,c.part_no  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 "
                                             + "  LEFT JOIN dbo.sys_locate_part c (nolock) ON a.locate_id=c.locate_id  WHERE a.locate_name = '" + locateName + "'");
                SysLocate sysLocates = dbConn.QueryFirstOrDefault<SysLocate>(stringBuilder.ToString());
                return sysLocates;
            }
        }
        //根据库位获取在库信息
        public List<SysStock> GetInStockByLocateName(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> sysStocks3 = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks3;
            }
        }
        //根据单号查询处理中状态的零星入库单
        public List<WmsInstock> GetInStockByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT * FROM dbo.wms_instock (nolock) WHERE order_no = '" + orderNo + "' AND order_status in ('20','30') AND order_type in ('" + (int)WmsEnumUtil.InStockType.EXCEPTION_IN_STOCK +"','"+ (int)WmsEnumUtil.InStockType.RESERVE_IN + "') and enabled='Y'");
                List<WmsInstock> WmsInstocks = dbConn.Query<WmsInstock>(stringBuilder.ToString()).ToList();
                return WmsInstocks;
            }
        }
        //根据单号、零件号获取单据信息
        public List<WmsInstockDetail> GetInStockDetailByOrderNoPartNo(string orderNo, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_instock_detail (nolock) WHERE order_no='" + orderNo + "' and part_no='" + partNo + "' and enabled='Y'");
                List<WmsInstockDetail> WmsInstockDetails = dbConn.Query<WmsInstockDetail>(stringBuilder.ToString()).ToList();
                return WmsInstockDetails;
            }
        }
        public List<WmsInstockSn> GetInStockSnByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_instock_sn (nolock) WHERE order_no='" + orderNo + "' and status=" + (int)WmsEnumUtil.MoveOrderSnStatus.GENERATED);
                List<WmsInstockSn> moveSnCreates = dbConn.Query<WmsInstockSn>(stringBuilder.ToString()).ToList();
                return moveSnCreates;
            }
        }
        //根据单据、零件号获取单据
        public List<WmsInstockSn> GetInStockSnByOrderNoCartonNo(string orderNo, string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_instock_sn (nolock) WHERE order_no='" + orderNo + "' and status=" + (int)WmsEnumUtil.MoveOrderSnStatus.GENERATED + " and carton_no ='" + cartonNo + "'");
                List<WmsInstockSn> WmsInstockSns = dbConn.Query<WmsInstockSn>(stringBuilder.ToString()).ToList();
                return WmsInstockSns;
            }
        }
        public List<WmsInstock> GetInStockInfoByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.order_no,b.enum_desc AS order_type_desc,CONVERT(INT, c.qty) qty,CONVERT(INT, c.rec_qty) rec_qty,c.part_no ,a.create_userid,a.create_time,c.dest_zone_id,c.dest_zone_name,d.emp_no,a.factory_code,p.part_spec FROM dbo.wms_instock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type=b.enum_value AND	b.enum_type='wms_instock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.wms_instock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid=d.emp_id ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON c.part_no=p.part_no ");
                SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' AND order_status in ('20','30') AND order_type in ('" + (int)WmsEnumUtil.InStockType.EXCEPTION_IN_STOCK + "','" + (int)WmsEnumUtil.InStockType.RESERVE_IN + "') and a.enabled='Y'");

                List<WmsInstock> wmsMoves = dbConn.Query<WmsInstock>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves;
            }
        }

        public List<WmsInstockDetail> GetInStockDetailQtySumByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.rec_qty)) rec_qty,c.part_no,c.part_spec FROM dbo.wms_instock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.wms_instock_detail c (nolock) ON a.order_no=c.order_no ");
                SqlStringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status in ('20','30') AND order_type in ('" + (int)WmsEnumUtil.InStockType.EXCEPTION_IN_STOCK + "','" + (int)WmsEnumUtil.InStockType.RESERVE_IN + "') and a.enabled='Y' group by c.part_no,c.part_spec");
                List<WmsInstockDetail> wmsMoves1 = dbConn.Query<WmsInstockDetail>(SqlStringBuilder.ToString()).ToList();
                return wmsMoves1;
            }
        }

        //获取完成信息提交数据
        public List<WmsInstockDetail> GetFinishInStockDetailByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                //StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.Append("SELECT SUM(CONVERT(INT, c.rec_qty)) rec_qty,c.part_no,dest_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no FROM dbo.wms_instock a (nolock) ");
                //SqlStringBuilder.Append("LEFT JOIN dbo.wms_instock_detail c (nolock) ON a.order_no=c.order_no ");
                //SqlStringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status <> '40' and a.enabled='Y' group by c.part_no,dest_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no");

                StringBuilder query = new StringBuilder(1024);
                query.Append("SELECT ISNULL(SUM(CONVERT(decimal,b.rec_qty)),0) rec_qty,c.part_no,dest_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no,a.order_type,c.item_no  FROM dbo.wms_instock a (nolock)  ");
                query.Append("    LEFT JOIN dbo.wms_instock_detail c (nolock) ON a.order_no=c.order_no  ");
                query.Append("    LEFT JOIN (select part_no,sum(qty) rec_qty from wms_instock_sn (nolock) where order_no=@orderNo and status=@snStatus and enabled='Y' group by part_no) as b  on c.part_no=b.part_no ");
                query.Append("WHERE a.order_no = @orderNo AND order_status < @orderStatus  and a.enabled='Y'  ");
                query.Append("group by c.part_no,dest_erp_warehouse,plan_date,plan_time,a.factory_code,a.order_no,a.order_type,c.item_no  ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@orderNo", orderNo);
                parameters.Add("@snStatus", (int)MoveOrderSnStatus.PROCESSED);
                parameters.Add("@orderStatus", (int)MoveOrderDetailStatus.COMPLETED);

                List<WmsInstockDetail> wmsMoves = dbConn.Query<WmsInstockDetail>(query.ToString(), parameters).ToList();
                return wmsMoves;
            }
        }

        //获取零件号信息
        public SysPart GetPartNOInfoByPartNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT * from sys_part (nolock) where part_no='" + partNo + "' ");
                SysPart sysParts = dbConn.QueryFirstOrDefault<SysPart>(SqlStringBuilder.ToString());
                return sysParts;
            }
        }

        //事务批量执行添加、修改
        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;
                    }
                }
            }
        }
    }
}