You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1598 lines
76 KiB
1598 lines
76 KiB
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;
|
|
|
|
namespace NFine.Application.WMS
|
|
{
|
|
public class PickMaterialApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public static DataTable Invmes = new DataTable();
|
|
//工单备料(工单子件)
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
|
|
// sql = @"select a.ID,b.MOCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
//from ICSMOPick a
|
|
//left join ICSMO b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
|
|
//left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null";
|
|
|
|
sql = @"select
|
|
a.MOCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,case when max(d.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4 ,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
|
|
from ICSMO a
|
|
inner join ICSMOPick b on b.MODetailID=a.MODetailID and b.WorkPoint=a.WorkPoint
|
|
left join ICSMOPickMerge d on d.SourceID=b.ID and d.WorkPoint=b.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.MOCode, a.WorkPoint,a.CreateDateTime,a.createperson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and b.MOCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//工单备料(工单子件)子表查询
|
|
public DataTable GetSubGridJson1(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select a.ID,b.MOCode as Code,a.Sequence+'-'+b.Sequence AS Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
|
|
from ICSMOPick a
|
|
left join ICSMO b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
|
|
left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where b.MOCode='{0}' and a.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
//工单领料(发料申请)
|
|
public DataTable GetGridJson2(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSMOApply a
|
|
|
|
// left join ICSWarehouse b on a.WHCode = b.WarehouseCode and a.WorkPoint = b.WorkPoint
|
|
|
|
// left join ICSInventory d on a.InvCode = d.InvCode and a.WorkPoint = d.WorkPoint
|
|
|
|
// left join ICSMOPickMerge e on a.ID = e.SourceID and a.WorkPoint = e.WorkPoint
|
|
|
|
sql = @"
|
|
select a.ApplyCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4 ,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
|
|
from ICSMOApply a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.ApplyCode, a.WorkPoint,a.CreateDateTime,a.createperson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//工单领料(发料申请)子表查询
|
|
public DataTable GetSubGridJson2(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSMOApply a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.ApplyCode='{0}' and a.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
//工单材料出库(材料出库)
|
|
public DataTable GetGridJson3(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
// case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSMOIssue a
|
|
//left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint ";
|
|
// sql += " WHERE 1=1 and e.ID is null";
|
|
sql = @"select a.IssueCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSMOIssue a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.IssueCode, a.CreateDateTime,a.createperson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//工单材料出库(材料出库)子表查询
|
|
public DataTable GetSubGridJson3(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSMOIssue a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.IssueCode='{0}' and a.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
//委外备料(委外备料)
|
|
public DataTable GetGridJson4(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
sql = @"select b.OOCode as Code, b.CreateDateTime as MTIME,b.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOOPick a
|
|
inner join ICSOutsourcingOrder b on a.OODetailID=b.OODetailID and a.WorkPoint=b.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
WHERE b.WorkPoint='{0}'
|
|
group by b.OOCode,b.CreateDateTime,b.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//委外备料(委外备料)子表查询
|
|
public DataTable GetSubGridJson4(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,b.OOCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOOPick a
|
|
left join ICSOutsourcingOrder b on a.OODetailID=b.OODetailID and a.WorkPoint=b.WorkPoint
|
|
left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where b.OOCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
//委外领料(委外发料申请)
|
|
public DataTable GetGridJson5(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSOApply a
|
|
//left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null";
|
|
sql = @"select a.ApplyCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOApply a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.ApplyCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//委外领料(委外发料申请)子表查询
|
|
public DataTable GetSubGridJson5(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOApply a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.ApplyCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
//委外材料出库(委外发料申请)
|
|
public DataTable GetGridJson6(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseCode,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSOIssue a
|
|
//left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null";
|
|
sql = @"select a.IssueCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOIssue a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.IssueCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
//委外材料出库(委外发料申请)子表查询
|
|
public DataTable GetSubGridJson6(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseCode,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOIssue a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.IssueCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
//销售发货(销售发货)
|
|
public DataTable GetGridJson7(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"select a.ID,a.SDNCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSSDN a
|
|
//left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and a.Type='1' and e.ID is null";
|
|
sql = @"select a.SDNCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSSDN a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.SDNCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.SDNCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//销售发货(销售发货)子表查询
|
|
public DataTable GetSubGridJson7(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.SDNCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSSDN a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.SDNCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
//其它出库(其它出库)
|
|
public DataTable GetGridJson8(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
//from ICSOtherOut a
|
|
//left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null ";
|
|
sql = @"select a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOtherOut a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.OutCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.OutCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//其它出库(其它出库)子表查询
|
|
public DataTable GetSubGridJson8(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSOtherOut a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.OutCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
//借用(借用)
|
|
public DataTable GetGridJson9(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"
|
|
//select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
// case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSBrrow a
|
|
// left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
// left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
// left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null ";
|
|
sql = @"select a.BrrowCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSBrrow a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}'
|
|
group by a.BrrowCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//借用(借用)子表查询
|
|
public DataTable GetSubGridJson9(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSBrrow a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.OutCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
//物料调拨
|
|
public DataTable GetGridJson10(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"
|
|
//select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
// case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSBrrow a
|
|
// left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
// left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
// left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null ";
|
|
sql = @"select a.TransferNO as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSTransfer a
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}' and a.Status='1'
|
|
group by a.TransferNO,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
//物料调拨子表查询
|
|
public DataTable GetSubGridJson10(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.TransferNO as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.FromWarehouseCode as WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
from ICSTransfer a
|
|
left join ICSWarehouse b on a.FromWarehouseCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.TransferNO='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
//两步调拨
|
|
public DataTable GetGridJson11(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = "";
|
|
#region [SQL]
|
|
// sql = @"
|
|
//select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
// case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// from ICSBrrow a
|
|
// left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
// left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
// left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
|
|
// sql += " WHERE 1=1 and e.ID is null ";
|
|
sql = @"select a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,
|
|
case when max(e.SourceID) is null then '未合并' else '已合并' end as IsNew
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
FROM ICSOtherOut a
|
|
INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.WorkPoint='{0}' and a.Status='2'
|
|
group by a.OutCode,a.CreateDateTime,a.CreatePerson,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//两步调拨子表查询
|
|
public DataTable GetSubGridJson11(string Code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
FROM ICSOtherOut a
|
|
INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
where a.OutCode='{0}' and b.WorkPoint='{1}'";
|
|
sql = string.Format(sql, Code, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public DataTable GetICSMOPickMergeTemp(string ID,string Type)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
#region sql
|
|
string sql = @"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
|
|
c.Code,
|
|
c.InvCode,
|
|
c.InvName,
|
|
c.InvStd,
|
|
FLOOR(c.iQuantity) AS iQuantity,
|
|
d.WarehouseCode,
|
|
d.LocationCode,
|
|
d.LotNO,
|
|
FLOOR(ISNULL(d.Quantity, 0)) AS QTY,
|
|
CONVERT(varchar(100),d.MTIME, 23) MTIME,
|
|
FLOOR(f.QTYTotal) QTYTotal INTO #temp
|
|
FROM
|
|
({0}) c
|
|
LEFT JOIN ICSWareHouseLotInfo d ON c.InvCode=d.INVCode AND c.WhCode=d.WarehouseCode AND d.WorkPoint='{1}' AND d.Quantity>0
|
|
left join ICSInventoryLot m on d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint and c.ExtensionID=m.ExtensionID
|
|
LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
|
|
|
|
|
|
DECLARE @ItemCode VARCHAR(50),
|
|
@QTY DECIMAL(18,3),
|
|
@ItemCodeCurrent VARCHAR(50),
|
|
@QTYCurrent DECIMAL(18,3),
|
|
@iQuantityCurrent DECIMAL(18,3),
|
|
@CanDelete BIT,
|
|
@Row INT,
|
|
@rowCurrent INT
|
|
SET @Row = @@rowcount
|
|
SET @rowCurrent=1
|
|
SET @CanDelete=0
|
|
SET @ItemCode=''
|
|
|
|
WHILE @rowCurrent<=@Row
|
|
BEGIN
|
|
SELECT @ItemCodeCurrent=InvCode,@QTYCurrent=QTY,@iQuantityCurrent=iQuantity FROM #temp WHERE rowNo=@rowCurrent
|
|
|
|
PRINT(@rowCurrent)
|
|
IF @ItemCode<>@ItemCodeCurrent
|
|
BEGIN
|
|
SET @ItemCode=@ItemCodeCurrent
|
|
SET @QTY=0
|
|
SET @CanDelete=0
|
|
END
|
|
|
|
IF @CanDelete=1
|
|
BEGIN
|
|
DELETE FROM #temp WHERE rowNo=@rowCurrent
|
|
SET @rowCurrent += 1
|
|
CONTINUE
|
|
END
|
|
|
|
SET @QTY += @QTYCurrent
|
|
IF @QTY>=@iQuantityCurrent
|
|
BEGIN
|
|
SET @CanDelete=1
|
|
END
|
|
|
|
SET @rowCurrent += 1
|
|
END
|
|
SELECT * FROM #temp
|
|
DROP TABLE #temp
|
|
";
|
|
#endregion
|
|
string sqls = "";
|
|
if (Type=="1")
|
|
{
|
|
#region 工单备料sql
|
|
sqls = @" SELECT
|
|
c.MOCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOPick a
|
|
inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE c.MOCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "2")
|
|
{
|
|
#region 工单领料SQL
|
|
sqls = @" select
|
|
a.ApplyCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOApply a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="3")
|
|
{
|
|
#region 工单材料出库SQL
|
|
sqls = @" select
|
|
a.IssueCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOIssue a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="4")
|
|
{
|
|
#region 委外备料SQL
|
|
sqls = @" select
|
|
c.OOCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOOPick a
|
|
left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE c.OOCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,a.WhCode, a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="5")
|
|
{
|
|
#region 委外领料SQL
|
|
sqls = @" select
|
|
a.ApplyCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOApply a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="6")
|
|
{
|
|
#region 委外材料出库SQL
|
|
sqls = @" select
|
|
a.IssueCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOIssue a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="7")
|
|
{
|
|
#region 销售发货SQL
|
|
sqls = @" select
|
|
a.SDNCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSSDN a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.SDNCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type=="8")
|
|
{
|
|
#region 其它出库SQL
|
|
sqls = @" select
|
|
a.OutCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOtherOut a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "9")
|
|
{
|
|
#region 借用SQL
|
|
sqls = @"select
|
|
a.BrrowCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSBrrow a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.BrrowCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "10")
|
|
{
|
|
#region 物料调拨
|
|
sqls = @" select
|
|
a.TransferNO as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.FromWarehouseCode as WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSTransfer a
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.TransferNO in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,a.FromWarehouseCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "11")
|
|
{
|
|
#region 两步调出
|
|
sqls = @"select
|
|
a.OutCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WHCode ,
|
|
a.ExtensionID
|
|
from ICSOtherOut a
|
|
INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,a.WHCode,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
|
|
sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID)?"''":ID.TrimEnd(','),WorkPoint);
|
|
sql = string.Format(sql, sqls, WorkPoint);
|
|
DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
|
|
DataTable dtCloned = table.Clone();
|
|
foreach (DataColumn col in dtCloned.Columns)
|
|
{
|
|
col.DataType = typeof(string);
|
|
}
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
DataRow newrow = dtCloned.NewRow();
|
|
foreach (DataColumn column in dtCloned.Columns)
|
|
{
|
|
newrow[column.ColumnName] = row[column.ColumnName].ToString();
|
|
|
|
}
|
|
dtCloned.Rows.Add(newrow);
|
|
}
|
|
if (Invmes.Rows.Count > 0)
|
|
{
|
|
dtCloned.Merge(Invmes, false);
|
|
}
|
|
return dtCloned;
|
|
}
|
|
|
|
|
|
public DataTable GetICSMOPickMergeTemp2(string ID, string Type)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
#region sql
|
|
string sql = @"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
|
|
c.MergeID,
|
|
c.Code,
|
|
c.InvCode,
|
|
c.InvName,
|
|
c.InvStd,
|
|
FLOOR(c.iQuantity) AS iQuantity,
|
|
d.WarehouseCode,
|
|
d.LocationCode,
|
|
d.LotNO,
|
|
FLOOR(ISNULL(d.Quantity, 0)) AS QTY,
|
|
CONVERT(varchar(100),d.MTIME, 23) MTIME,
|
|
FLOOR(f.QTYTotal) QTYTotal INTO #temp
|
|
FROM
|
|
({0}) c
|
|
LEFT JOIN ICSWareHouseLotInfo d ON c.InvCode=d.INVCode AND c.WhCode=d.WarehouseCode AND d.WorkPoint='{1}' AND d.Quantity>0
|
|
left join ICSInventoryLot m on d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint and c.ExtensionID=m.ExtensionID
|
|
LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
|
|
|
|
DECLARE @ItemCode VARCHAR(50),
|
|
@QTY DECIMAL(18,3),
|
|
@ItemCodeCurrent VARCHAR(50),
|
|
@QTYCurrent DECIMAL(18,3),
|
|
@iQuantityCurrent DECIMAL(18,3),
|
|
@CanDelete BIT,
|
|
@Row INT,
|
|
@rowCurrent INT
|
|
SET @Row = @@rowcount
|
|
SET @rowCurrent=1
|
|
SET @CanDelete=0
|
|
SET @ItemCode=''
|
|
|
|
WHILE @rowCurrent<=@Row
|
|
BEGIN
|
|
SELECT @ItemCodeCurrent=InvCode,@QTYCurrent=QTY,@iQuantityCurrent=iQuantity FROM #temp WHERE rowNo=@rowCurrent
|
|
|
|
PRINT(@rowCurrent)
|
|
IF @ItemCode<>@ItemCodeCurrent
|
|
BEGIN
|
|
SET @ItemCode=@ItemCodeCurrent
|
|
SET @QTY=0
|
|
SET @CanDelete=0
|
|
END
|
|
|
|
IF @CanDelete=1
|
|
BEGIN
|
|
DELETE FROM #temp WHERE rowNo=@rowCurrent
|
|
SET @rowCurrent += 1
|
|
CONTINUE
|
|
END
|
|
|
|
SET @QTY += @QTYCurrent
|
|
IF @QTY>=@iQuantityCurrent
|
|
BEGIN
|
|
SET @CanDelete=1
|
|
END
|
|
|
|
SET @rowCurrent += 1
|
|
END
|
|
SELECT * FROM #temp
|
|
DROP TABLE #temp
|
|
";
|
|
#endregion
|
|
string sqls = "";
|
|
if (Type == "1")
|
|
{
|
|
#region 工单备料sql
|
|
sqls = @" select
|
|
x.MergeID,
|
|
c.MOCode as Code,
|
|
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOPick a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE c.MOCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,x.MergeID,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "2")
|
|
{
|
|
#region 工单领料SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.ApplyCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOApply a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE x.SourceID in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,x.MergeID,a.ExtensionID";
|
|
#endregion
|
|
}
|
|
if (Type == "3")
|
|
{
|
|
#region 工单材料出库SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.IssueCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSMOIssue a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "4")
|
|
{
|
|
#region 委外备料SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
c.OOCode as Code,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOOPick a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE c.OOCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID, x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "5")
|
|
{
|
|
#region 委外领料SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.ApplyCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOApply a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "6")
|
|
{
|
|
#region 委外材料出库SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.IssueCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOIssue a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "7")
|
|
{
|
|
#region 销售发货SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.SDNCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSSDN a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.SDNCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "8")
|
|
{
|
|
#region 其它出库SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.OutCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOtherOut a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "9")
|
|
{
|
|
#region 借用SQL
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.BrrowCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSBrrow a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.BrrowCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,a.WhCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "10")
|
|
{
|
|
#region 物料调拨
|
|
sqls = @" select
|
|
x.MergeID,
|
|
a.TransferNO as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.FromWarehouseCode as WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSTransfer a
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.TransferNO in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,a.FromWarehouseCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
if (Type == "10")
|
|
{
|
|
#region 两步调出
|
|
sqls = @"select
|
|
x.MergeID,
|
|
a.OutCode as Code,
|
|
a.InvCode,
|
|
|
|
b.InvName,
|
|
b.InvStd,
|
|
SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
a.WHCode as WhCode,
|
|
a.ExtensionID
|
|
FROM
|
|
ICSOtherOut a
|
|
INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
|
|
inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
|
|
GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,a.WHCode,a.ExtensionID,x.MergeID";
|
|
#endregion
|
|
}
|
|
|
|
|
|
sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(','), WorkPoint);
|
|
sql = string.Format(sql, sqls, WorkPoint);
|
|
DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
|
|
DataTable dtCloned = table.Clone();
|
|
foreach (DataColumn col in dtCloned.Columns)
|
|
{
|
|
col.DataType = typeof(string);
|
|
}
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
DataRow newrow = dtCloned.NewRow();
|
|
foreach (DataColumn column in dtCloned.Columns)
|
|
{
|
|
newrow[column.ColumnName] = row[column.ColumnName].ToString();
|
|
|
|
}
|
|
dtCloned.Rows.Add(newrow);
|
|
}
|
|
if (Invmes.Rows.Count > 0)
|
|
{
|
|
dtCloned.Merge(Invmes, false);
|
|
}
|
|
return dtCloned;
|
|
}
|
|
|
|
|
|
public string MergeMaterial(string keyValue,string Type)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
|
|
keyValue = keyValue.TrimEnd(',');
|
|
DateTime time = DateTime.Now;
|
|
string Tday = time.ToString("yyyyMMdd");
|
|
string sqls = string.Format(@"SELECT MAX(A.SourceID) AS SourceID FROM ICSMOPickMerge A WHERE A.SourceID LIKE '{0}%' AND LEN(a.SourceID) = 13", Tday);
|
|
DataSet ds = SqlHelper.GetDataSetBySql(sqls);
|
|
string MergeID = "";
|
|
Int64 Merge_ID = 0;
|
|
DataTable dts = ds.Tables[0];
|
|
|
|
if (dts != null && dts.Rows.Count > 0)
|
|
{
|
|
string lot = dts.Rows[0]["SourceID"].ToString();
|
|
if (!string.IsNullOrWhiteSpace(lot))
|
|
Merge_ID = Convert.ToInt64(lot.Substring(lot.Length - 5));
|
|
}
|
|
|
|
|
|
MergeID = Tday + (Merge_ID + 1).ToString().PadLeft(5, '0');
|
|
|
|
if (Type == "1")//工单备料
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',b.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSMO a inner join ICSMOPick b on b.MODetailID=a.MODetailID and b.WorkPoint=a.WorkPoint
|
|
where a.MOCode in ({5})
|
|
";
|
|
}
|
|
if (Type == "2")//工单发料
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSMOApply a
|
|
where a.ApplyCode in ({5})";
|
|
}
|
|
if (Type == "3")//材料出库
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSMOIssue a
|
|
where a.IssueCode in ({5}) ";
|
|
}
|
|
if (Type == "4")//委外备料
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',b.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOutsourcingOrder a inner join ICSOOPick b on a.OODetailID=b.OODetailID and b.WorkPoint=a.WorkPoint
|
|
where a.OOCode in ({5})";
|
|
}
|
|
if (Type == "5")//委外领料
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOApply a
|
|
where a.ApplyCode in ({5})";
|
|
}
|
|
if (Type == "6")//委外材料出库
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOIssue a
|
|
where a.IssueCode in ('{5}')";
|
|
}
|
|
if (Type == "7")//销售发货
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOIssue a
|
|
where a.IssueCode in ({5})";
|
|
}
|
|
if (Type == "8")//其它出库
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSSDN a
|
|
where a.SDNCode in ({5})";
|
|
}
|
|
if (Type == "9")//借用单
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOtherOut a
|
|
where a.OutCode in ({5})";
|
|
}
|
|
if (Type == "10")//物料调拨
|
|
{
|
|
sql += @"INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSTransfer a
|
|
where a.TransferNO in ({5})
|
|
";
|
|
}
|
|
if (Type == "11")//两步调出
|
|
{
|
|
sql += @" INSERT INTO dbo.ICSMOPickMerge
|
|
( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint)
|
|
select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
|
|
from ICSOtherOut a
|
|
INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
|
|
where a.OutCode in ({5})
|
|
";
|
|
}
|
|
sql = string.Format(sql, MergeID, Type, MUSER, MUSERNAME, WorkPoint, keyValue);
|
|
|
|
try
|
|
{
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
public string CheckQty(string objArr)
|
|
{
|
|
objArr= objArr.TrimEnd(',');
|
|
string sql = @"select * from ICSMOPickMerge a
|
|
left join ICSMOPick b on a.SourceID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSMO c on b.MODetailID=c.MODetailID and b.WorkPoint=c.WorkPoint
|
|
where MOCode in ({0})";
|
|
sql = string.Format(sql, objArr);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt != null&&dt.Rows.Count>0)
|
|
{
|
|
return "1";
|
|
}
|
|
|
|
|
|
return "0";
|
|
|
|
}
|
|
|
|
|
|
}
|
|
}
|