HGSRM/WMS-BS/NFine.Application/DHAY/ICSWareHouseLotInfoLogApp.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 ICSWareHouseLotInfoLogApp : 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 MTIME >= '" + queryParam["StartDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    whereSql += " and MTIME < '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    whereSql2 += " and b.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    whereSql2 += " and b.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToString()))
                {
                    whereSql2 += " and b.InvStd like '%" + queryParam["InvStd"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
                {
                    whereSql2 += " and e.WarehouseCode like '%" + queryParam["WHCode"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                whereSql2 += " and b.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
          #region [SQL]
            string sql = $@"SELECT b.InvCode,b.InvName,b.InvStd,b.ClassCode,b.ClassName,b.InvUnit,b.EATTRIBUTE5 as OldInvCode,e.WarehouseCode,e.WarehouseName,ISnull(g.QCQuantity,0) QCQuantity,Isnull(d.SQuantity,0) SQuantity,Isnull(f.FQuantity,0) FQuantity,ISNULL(C.Quantity, 0) as Quantity  FROM 
    ICSInventory b 
  left JOIN (SELECT invCode,workPoint,WarehouseCode,SUM(Quantity) as Quantity FROM ICSWareHouseLotInfo GROUP BY invCode,workPoint,WarehouseCode) c ON c.InvCode=b.InvCode AND b.WorkPoint=c.WorkPoint 
	left JOIN ICSWarehouse e ON c.WarehouseCode=e.WarehouseCode AND c.WorkPoint=e.WorkPoint
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as SQuantity,TOWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='2' AND BusinessCode!='31' AND BusinessCode!='32' {whereSql}  GROUP BY invCode,workPoint,TOWarehouseCode) d ON d.InvCode=b.InvCode AND d.workPoint=b.WorkPoint AND c.WarehouseCode=d.WarehouseCode
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as FQuantity,FromWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='3' AND BusinessCode!='31' AND BusinessCode!='32' {whereSql}  GROUP BY invCode,workPoint,FromWarehouseCode) f ON f.InvCode=b.InvCode AND f.workPoint=b.WorkPoint AND c.WarehouseCode=f.WarehouseCode
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as QCQuantity,TOWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='1'  {whereSql}   GROUP BY invCode,workPoint,TOWarehouseCode) g ON g.InvCode=b.InvCode AND g.workPoint=b.WorkPoint AND c.WarehouseCode=g.WarehouseCode   where ( d.SQuantity<>0 or f.FQuantity<>0 )  {whereSql2}";

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


        public DataTable StatementExportAll(string StartDate, string EndDate, string InvCode, string WHCode, string InvName, string InvStd)
        {
            string TableCode = string.Empty;
            string whereSql = string.Empty;
            string whereSql2 = string.Empty;

            if (!string.IsNullOrWhiteSpace(StartDate))
            {
                whereSql += " and MTIME >= '" + StartDate + "' ";
            }
            if (!string.IsNullOrWhiteSpace(EndDate))
            {
                whereSql += " and MTIME < '" + EndDate + "' ";
            }
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                whereSql2 += " and b.InvCode like '%" + InvCode + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(InvName))
            {
                whereSql2 += " and b.InvName like '%" + InvName + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(InvStd))
            {
                whereSql2 += " and b.InvStd like '%" + InvStd + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(WHCode))
            {
                whereSql2 += " and e.WarehouseCode like '%" + WHCode + "%' ";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                whereSql2 += " and b.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }

            #region [SQL]
            string sql = $@"SELECT b.InvCode AS 物料编码,b.InvName AS 物料名称,b.InvStd as 物料规格 ,b.ClassCode AS 大类编码,b.ClassName AS 大类名称,e.WarehouseCode as 仓库编码,e.WarehouseName as 仓库名称,ISnull(g.QCQuantity, 0) 期初数量 ,Isnull(d.SQuantity, 0) 收料数量,Isnull(f.FQuantity, 0) 发料数量,ISNULL(C.Quantity, 0) as 库存数量  FROM
       ICSInventory b
     left JOIN (SELECT invCode,workPoint,WarehouseCode,SUM(Quantity) as Quantity FROM ICSWareHouseLotInfo GROUP BY invCode,workPoint,WarehouseCode) c ON c.InvCode=b.InvCode AND b.WorkPoint=c.WorkPoint 
	left JOIN ICSWarehouse e ON c.WarehouseCode=e.WarehouseCode AND c.WorkPoint=e.WorkPoint
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as SQuantity,TOWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='2' AND BusinessCode!='31' AND BusinessCode!='32' {whereSql}  GROUP BY invCode,workPoint,TOWarehouseCode) d ON d.InvCode=b.InvCode AND d.workPoint=b.WorkPoint AND c.WarehouseCode=d.WarehouseCode
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as FQuantity,FromWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='3' AND BusinessCode!='31' AND BusinessCode!='32' {whereSql}  GROUP BY invCode,workPoint,FromWarehouseCode) f ON f.InvCode=b.InvCode AND f.workPoint=b.WorkPoint AND c.WarehouseCode=f.WarehouseCode
 left JOIN (SELECT invCode,workPoint,SUM(Quantity) as QCQuantity,TOWarehouseCode AS WarehouseCode FROM ICSWareHouseLotInfoLog WHERE TransType='1'  {whereSql}   GROUP BY invCode,workPoint,TOWarehouseCode) g ON g.InvCode=b.InvCode AND g.workPoint=b.WorkPoint AND c.WarehouseCode=g.WarehouseCode   where ( d.SQuantity<>0 or f.FQuantity<>0 )  {whereSql2}";

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


    }
}