HGSRM/WMS-BS/NFine.Application/DHAY/ICSOtherInOutApp.cs

using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NFine.Code;
using NFine.Repository;
using System.Data.Common;
using NFine.Domain._03_Entity.SRM;
using ICS.Application.Entity;
using Newtonsoft.Json;
using System.Configuration;
using System.Data.SqlClient;
using ICS.Data;
using Newtonsoft.Json.Linq;
using System.Web.UI.WebControls;
using static NFine.Code.Net;

namespace NFine.Application.DHAY
{
    public class ICSOtherInOutApp : RepositoryFactory<ICSVendor>
    {
        public static DataTable Invmes = new DataTable();

        public DataTable GetList(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();


            string whereSql = string.Empty;
            string whereSql2 = string.Empty;
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
                {
                    whereSql += " and a.MTIME >= '" + queryParam["StartDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    whereSql += " and a.MTIME < '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    whereSql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
                {
                    whereSql += " and e.WarehouseCode like '%" + queryParam["WHCode"].ToString() + "%' ";
                }

                if (!string.IsNullOrWhiteSpace(queryParam["LocationCode"].ToString()))
                {
                    whereSql += $" and (a.FromLocationCode like '%{queryParam["LocationCode"]}%' OR a.ToLocationCode like '%{queryParam["LocationCode"]}%') ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
                {
                    whereSql += " and c.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                whereSql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
          #region [SQL]
            string sql = $@"SELECT DISTINCT e.WarehouseCode
 AS WHCode,e.WarehouseName as WHName,a.InvCode,d.InvName,c.BatchCode ,d.ID as InvID,case when isnull(a.FromLocationCode,'')='' then a.ToLocationCode ELSE a.FromLocationCode END LocationCode
 from ICSWareHouseLotInfoLog a 
   INNER JOIN ICSInventoryLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
   INNER JOIN ICSExtension c ON b.ExtensionID=c.ID AND b.WorkPoint=c.WorkPoint
	 INNER JOIN ICSInventory d ON a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
	 INNER JOIN ICSWarehouse e ON e.WorkPoint=a.WorkPoint AND (e.WarehouseCode=a.FromWarehouseCode OR e.WarehouseCode=a.ToWarehouseCode)
 WHERE (a.BusinessCode='24' OR a.BusinessCode='25') {whereSql}";

            #endregion
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetDetails(string queryJson, ref Pagination jqgridparam,string ID)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();


            string whereSql = string.Empty;
            string whereSql2 = string.Empty;
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
                {
                    whereSql += " and a.MTIME >= '" + queryParam["StartDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    whereSql += " and a.MTIME < '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    whereSql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
                {
                    whereSql += " and f.BatchCode= '" + queryParam["BatchCode"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["LocationCode"].ToString()))
                {
                    whereSql += $" and (b.FromLocationCode like '%{queryParam["LocationCode"]}%' OR b.ToLocationCode like '%{queryParam["LocationCode"]}%') ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                whereSql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #region [SQL]
            string sql = $@"SELECT a.ID, a.InvCode,a.InvName,a.InvStd,b.TransCode ,b.MTIME ,b.Quantity,f.BatchCode,d.WarehouseCode AS WHCode,d.WarehouseName AS WHName,b.FromLocationCode LocationCode
  FROM ICSInventory a 
   INNER JOIN ICSWareHouseLotInfoLog b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint AND b.BusinessCode='24' AND b.FromWarehouseCode='{queryParam["WHCode"]}' --杂发
		INNER JOIN ICSWarehouse d ON a.WorkPoint=d.WorkPoint AND d.WarehouseCode=b.FromWarehouseCode
    INNER JOIN ICSInventoryLot e ON e.LotNo=b.LotNo  AND a.WorkPoint=e.WorkPoint
    INNER JOIN ICSExtension f ON e.ExtensionID=f.ID AND f.WorkPoint=a.WorkPoint
		WHERE a.ID='{ID}' {whereSql}
		
		UNION ALL
		
		SELECT a.ID, a.InvCode,a.InvName,a.InvStd,b.TransCode , b.MTIME ,b.Quantity,f.BatchCode,d.WarehouseCode AS WHCode,d.WarehouseName AS WHName,b.ToLocationCode LocationCode
  FROM ICSInventory a 
	  INNER JOIN ICSWareHouseLotInfoLog b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint AND b.BusinessCode='25' AND b.ToWarehouseCode='{queryParam["WHCode"]}' --杂收
		INNER JOIN ICSWarehouse d ON a.WorkPoint=d.WorkPoint AND d.WarehouseCode=b.ToWarehouseCode
    INNER JOIN ICSInventoryLot e ON e.LotNo=b.LotNo AND a.WorkPoint=e.WorkPoint
    INNER JOIN ICSExtension f ON e.ExtensionID=f.ID AND f.WorkPoint=a.WorkPoint
		WHERE a.ID='{ID}' {whereSql}";

            #endregion
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable StatementExportAll(string StartDate, string EndDate, string InvCode, string WHCode,string LocationCode, string BatchCode)
        {
            string TableCode = string.Empty;
            string whereSql = string.Empty;

            if (!string.IsNullOrWhiteSpace(StartDate))
            {
                whereSql += " and a.MTIME >= '" + StartDate + "' ";
            }
            if (!string.IsNullOrWhiteSpace(EndDate))
            {
                whereSql += " and a.MTIME < '" + EndDate + "' ";
            }
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                whereSql += " and b.InvCode like '%" + InvCode + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(WHCode))
            {
                whereSql += " and d.WarehouseCode like '%" + WHCode + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(LocationCode))
            {
                whereSql += $" and (b.FromLocationCode like '%{LocationCode}%' OR b.ToLocationCode like '%{LocationCode}%') ";
            }
            if (!string.IsNullOrWhiteSpace(BatchCode))
            {
                whereSql += " and f.BatchCode like '%" + BatchCode + "%' ";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                whereSql += " and b.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }

            #region [SQL]
            string sql = $@"SELECT a.InvCode 物料编码,a.InvName 物料名称,a.InvStd 规格型号,d.WarehouseCode 仓库编码,d.WarehouseName 仓库名称,b.FromLocationCode  库位代码,f.BatchCode 批次,b.TransCode '杂发/收单号',b.Quantity 数量 ,b.MTIME 时间
  FROM ICSInventory a 
   INNER JOIN ICSWareHouseLotInfoLog b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint AND b.BusinessCode='24' 
		INNER JOIN ICSWarehouse d ON a.WorkPoint=d.WorkPoint AND d.WarehouseCode=b.FromWarehouseCode
    INNER JOIN ICSInventoryLot e ON e.LotNo=b.LotNo  AND a.WorkPoint=e.WorkPoint
    INNER JOIN ICSExtension f ON e.ExtensionID=f.ID AND f.WorkPoint=a.WorkPoint
		WHERE 1=1 {whereSql}
		
		UNION ALL
		
		SELECT  a.InvCode 物料编码,a.InvName 物料名称,a.InvStd 规格型号,d.WarehouseCode 仓库代码,d.WarehouseName 仓库名称,b.ToLocationCode  库位代码,f.BatchCode 批次,b.TransCode '杂发/收单号',b.Quantity 数量, b.MTIME 时间 
  FROM ICSInventory a 
	  INNER JOIN ICSWareHouseLotInfoLog b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint AND b.BusinessCode='25' 
		INNER JOIN ICSWarehouse d ON a.WorkPoint=d.WorkPoint AND d.WarehouseCode=b.ToWarehouseCode
    INNER JOIN ICSInventoryLot e ON e.LotNo=b.LotNo AND a.WorkPoint=e.WorkPoint
    INNER JOIN ICSExtension f ON e.ExtensionID=f.ID AND f.WorkPoint=a.WorkPoint
		WHERE 1=1 {whereSql}";

            #endregion
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


    }
}