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.
1787 lines
80 KiB
1787 lines
80 KiB
using Newtonsoft.Json.Linq;
|
|
using Newtonsoft.Json;
|
|
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Repository;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Threading;
|
|
using System.Reflection.Emit;
|
|
using NFine.Application.WMS;
|
|
using System.Net;
|
|
using System.IO;
|
|
using System.Reflection;
|
|
using System.Data.SqlClient;
|
|
using NFine.Application.SystemManage;
|
|
using System.Collections;
|
|
using System.Web.Script.Serialization;
|
|
|
|
namespace NFine.Application.DHAY
|
|
{
|
|
public class ICSAccessoriesMOIApplyApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public static DataTable Invmes = new DataTable();
|
|
|
|
PickMaterialApp App = new PickMaterialApp();
|
|
DataActionApp actionapp = new DataActionApp();
|
|
|
|
|
|
#region 删除辅料材料出库单
|
|
public string DeleteICSMOApply(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"DELETE FROM dbo.ICSMOApply WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存发料信息
|
|
public string SaveICSMOApply(string ICSASN)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string msg = "";
|
|
List<JsonData> parsedData = JsonConvert.DeserializeObject<List<JsonData>>(ICSASN);
|
|
string sql = "";
|
|
string Colspan = "";
|
|
string str1 = "";
|
|
List<string> ExtensionIDList = new List<string>();
|
|
// 获取解析后的数据
|
|
JsonData data = parsedData[0];
|
|
foreach (var detail in data.Detail)
|
|
{
|
|
|
|
//获取物料的可用数量，判断当前创建单据数量
|
|
decimal invCount = GetInvCodeCount(detail.InvCode, "", "");
|
|
//获取创建单据的所有的物料信息
|
|
decimal allCount = data.Detail.Where(e => e.InvCode == detail.InvCode).Select(f => f.Quantity).Sum();
|
|
if (invCount < allCount)
|
|
{
|
|
throw new Exception($"物料{detail.InvCode}创建单据总数量：{allCount}不能大于库存可用量：{invCount}");
|
|
}
|
|
Colspan = "~" + detail.Batch + "~~~~~~~~~~~~";
|
|
string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
|
|
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
|
|
bool flag = true;
|
|
foreach (var item in ExtensionIDList)
|
|
{
|
|
if (item == Colspan + WorkPoint)
|
|
{
|
|
flag = false;
|
|
}
|
|
}
|
|
if (ExtensionID == null && flag == true)
|
|
{
|
|
str1 = Guid.NewGuid().ToString();
|
|
sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
|
|
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
|
|
str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
|
|
data.User, UserName, WorkPoint);
|
|
}
|
|
else if (ExtensionID != null)
|
|
{
|
|
str1 = ExtensionID.ToString();
|
|
}
|
|
ExtensionIDList.Add(Colspan + WorkPoint);
|
|
|
|
sql += "INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode,EATTRIBUTE11,EATTRIBUTE15,EATTRIBUTE16,EATTRIBUTE17,EATTRIBUTE18,EATTRIBUTE19,EATTRIBUTE20)" +
|
|
" select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{1}',newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','1','{23}','{24}',0,'{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}'";
|
|
sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, UserCode, data.MTIME, WorkPoint, '1', str1, UserCode, UserName, data.MTIME, data.Dept, data.CodeType, data.ApplyNegCode, data.Custmer, data.Remarks, detail.DetailReamrk, detail.SYproject, "杂发", detail.SourceCode, data.Remarks2, detail.DetailReamrk2, data.LYDept, data.Remarks3, data.DeptName, data.LYDeptName, data.CodeTypeName, data.ApplyNegName, data.CustmerName, detail.SYprojectName);
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 辅料材料出库单主表信息查询
|
|
public DataTable GetICSMOApply(ref Pagination jqgridparam, string MenuID, string queryJson)
|
|
{
|
|
string DataActionsql = actionapp.DataActionSqlGet(MenuID);
|
|
string ParentId = "";
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
var queryParam = queryJson.ToJObject();
|
|
string sql = @" SELECT DISTINCT max(a.ID) ID,
|
|
a.ApplyCode
|
|
,a.Status
|
|
,a.CreatePerson
|
|
,a.CreateDateTime
|
|
,a.MUSER
|
|
,u.F_RealName as CreateMUSERName
|
|
,a.MUSERName
|
|
,a.WHCode
|
|
--,a.SourceCode
|
|
,a.EATTRIBUTE
|
|
,LYdep.Name as LYDep
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE19
|
|
,a.EATTRIBUTE5
|
|
,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE11
|
|
,a.EATTRIBUTE10
|
|
,a.EATTRIBUTE13
|
|
,a.EATTRIBUTE14
|
|
,a.EATTRIBUTE17
|
|
,dep.DepName as SYDep
|
|
,case when c.Quantity=Isnull(c.IssueQuantity,0) then '1' else '2' END as FLStatus
|
|
from dbo.ICSMOApply a
|
|
INNER JOIN (SELECT Sum(a.Quantity) AS Quantity,
|
|
Sum(Isnull(a.IssueQuantity,0)) AS IssueQuantity,a.ApplyCode,a.WorkPoint FROM ICSMOApply a GROUP BY a.ApplyCode,a.WorkPoint ) c ON a.ApplyCode=c.ApplyCode AND a.WorkPoint=c.workpoint
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
left JOIN ICSDepartment dep ON dep.DepCode=a.EATTRIBUTE and a.WorkPoint=dep.WorkPoint
|
|
left JOIN (SELECT b.F_ItemName as Name,b.F_ItemCode as Code FROM [dbo].[Sys_SRM_Items] a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON b.F_ItemId = a.F_Id
|
|
WHERE F_EnCode = 'LYDep') LYdep ON Code=a.DepCode
|
|
LEFT JOIN Sys_SRM_User u ON u.F_Account=a.CreatePerson
|
|
where 1=1 ";
|
|
|
|
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 (!string.IsNullOrWhiteSpace(queryParam["Status"].ToString()))
|
|
{
|
|
if (queryParam["Status"].ToString() == "1")
|
|
{
|
|
sql += " and c.Quantity=Isnull(c.IssueQuantity,0) ";
|
|
}
|
|
else if (queryParam["Status"].ToString() == "2")
|
|
{
|
|
sql += " and c.Quantity!=Isnull(c.IssueQuantity,0) and Status='1' ";
|
|
}
|
|
else if (queryParam["Status"].ToString() == "3")
|
|
{
|
|
sql += " and c.Quantity!=Isnull(c.IssueQuantity,0) and Status='2' ";
|
|
}
|
|
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
|
|
{
|
|
sql += " and a.WHCode like '%" + queryParam["WHCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["SYDep"].ToString()))
|
|
{
|
|
sql += " and a.EATTRIBUTE like '%" + queryParam["SYDep"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
|
|
{
|
|
sql += " and u.F_RealName like '%" + queryParam["CreatedBy"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["DocType"].ToString()))
|
|
{
|
|
sql += " and a.EATTRIBUTE2 like '%" + queryParam["DocType"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
sql = string.Format(sql);
|
|
sql += " " + DataActionsql;
|
|
ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string groupsql = @" GROUP BY a.ApplyCode,a.Status,a.CreatePerson,a.CreateDateTime,a.MUSER,u.F_RealName
|
|
, a.MUSERName ,a.WHCode ,a.EATTRIBUTE,LYdep.Name ,a.EATTRIBUTE2,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE19,a.EATTRIBUTE5,a.EATTRIBUTE7,a.EATTRIBUTE8,a.EATTRIBUTE11
|
|
,a.EATTRIBUTE10,a.EATTRIBUTE13,a.EATTRIBUTE14,a.EATTRIBUTE17
|
|
,dep.DepName ,c.Quantity,c.IssueQuantity ";
|
|
|
|
if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
|
|
{
|
|
return SqlHelper.FindTablePageBySql_OtherTempByRole(sql.ToString(), sql, ParentId, UserCode, parameter.ToArray(),groupsql, ref jqgridparam);
|
|
}
|
|
else
|
|
{
|
|
sql += groupsql;
|
|
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode!="admin")
|
|
//{
|
|
// sql += " and a.CreatePerson like '%" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "%' ";
|
|
//}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 辅料材料出库单主表信息查询
|
|
public DataTable GetICSMOApplyByApplyCode(string ApplyCode)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT DISTINCT
|
|
a.ApplyCode
|
|
,a.Status
|
|
,a.CreatePerson
|
|
,a.CreateDateTime
|
|
,a.MUSER
|
|
,a.MUSERName
|
|
,a.SourceCode
|
|
,a.EATTRIBUTE
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE4
|
|
,a.EATTRIBUTE5
|
|
,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE9
|
|
,a.EATTRIBUTE10
|
|
,a.EATTRIBUTE11
|
|
,a.EATTRIBUTE19
|
|
,a.DepCode
|
|
from dbo.ICSMOApply a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
WHERE a.ApplyCode='{0}' and a.WorkPoint='{1}' ";
|
|
|
|
sql = string.Format(sql, ApplyCode,WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
#endregion
|
|
|
|
#region 主页面子表信息查询
|
|
public DataTable GetICSMOApplyDetail(string ApplyCode, string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select a.ID
|
|
,a.ApplyCode
|
|
,a.Sequence
|
|
,a.InvCode
|
|
,b.InvName
|
|
,b.InvStd
|
|
,b.EATTRIBUTE5 as OldInvCode
|
|
,a.Quantity
|
|
,a.Amount
|
|
,a.ExtensionID
|
|
,a.MUSER
|
|
,a.MUSERName
|
|
,u.F_RealName as CreateMUSERName
|
|
,a.MTIME
|
|
,a.WHCode
|
|
,c.WarehouseName as WHName
|
|
,a.SourceCode
|
|
,IssueQuantity
|
|
,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
|
|
,a.EATTRIBUTE
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE4
|
|
,a.EATTRIBUTE5
|
|
,a.EATTRIBUTE6
|
|
,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE9
|
|
,a.EATTRIBUTE10
|
|
,a.EATTRIBUTE11
|
|
from ICSMOApply a
|
|
LEFT JOIN ICSInventory b ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
left join ICSWarehouse c On c.WarehouseCode= a.WHCode AND c.WorkPoint=a.WorkPoint
|
|
LEFT JOIN Sys_SRM_User u ON u.F_Account=a.CreatePerson
|
|
WHERE a.ApplyCode='" + ApplyCode + "' ";
|
|
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 (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
|
|
//{
|
|
// sql += " and a.CreatePerson like '%" + queryParam["CreatedBy"].ToString() + "%' ";
|
|
//}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
|
|
}
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
|
|
#region 删除单据
|
|
public string DeleteICSMOApplyByCode(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
|
|
BEGIN
|
|
RAISERROR('该单据已发料，不能删除！',16,1);
|
|
RETURN
|
|
END;
|
|
IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND Status='2')
|
|
BEGIN
|
|
RAISERROR('该单据已审核，不能删除！',16,1);
|
|
RETURN
|
|
END;
|
|
DELETE FROM dbo.ICSMOApply WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
#region 删除单据
|
|
public string DeleteICSMOApplyById(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
// IF EXISTS(select ID from ICSMOApply where ID IN ({ 0}) and WorkPoint = '{1}' AND Status = '2')
|
|
// BEGIN
|
|
// RAISERROR('该单据已审核，不能删除！', 16, 1);
|
|
// RETURN
|
|
//END;
|
|
|
|
|
|
|
|
sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where Id IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
|
|
BEGIN
|
|
RAISERROR('该单据已发料，不能删除！',16,1);
|
|
RETURN
|
|
END;
|
|
|
|
DELETE FROM dbo.ICSMOApply WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 审核单据
|
|
/// <summary>
|
|
/// 审核单据
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string ApproveICSMOApplyByCode(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND status!='1')
|
|
BEGIN
|
|
RAISERROR('该单据的状态不是开立状态，不能审核！',16,1);
|
|
RETURN
|
|
END
|
|
UPDATE dbo.ICSMOApply SET Status='2', EATTRIBUTE12='{2}',EATTRIBUTE13='{3}',EATTRIBUTE14='{4}' WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint, UserCode, UserName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 弃审单据
|
|
/// <summary>
|
|
/// 弃审单据
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string AbandonICSMOApplyByCode(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND status!='2')
|
|
BEGIN
|
|
RAISERROR('该单据的状态不是审核状态，不能弃审!',16,1);
|
|
RETURN
|
|
END
|
|
IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
|
|
BEGIN
|
|
RAISERROR('该单据已发料，不能删除！',16,1);
|
|
RETURN
|
|
END
|
|
UPDATE dbo.ICSMOApply SET Status='1' , EATTRIBUTE12='',EATTRIBUTE13='' ,EATTRIBUTE14='' WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
#endregion
|
|
|
|
//#region 发料
|
|
///// <summary>
|
|
///// 发料
|
|
///// </summary>
|
|
///// <param name="keyValue"></param>
|
|
///// <returns></returns>
|
|
//public string ICSMOApplyByCodeFL(string MOCodes, string Type, string Mechanism, string InvCode)
|
|
//{
|
|
// string msg = string.Empty;
|
|
// try
|
|
// {
|
|
// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
// string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
// string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
|
|
// MOCodes = MOCodes.TrimEnd(',');
|
|
|
|
// //获取单据信息
|
|
// string ZDsql = $@" SELECT distinct ApplyCode FROM ICSMOApply WHERE ApplyCode in({MOCodes}) and WorkPoint='{WorkPoint}' ";
|
|
// var dt = SqlHelper.CmdExecuteDataTable(ZDsql);
|
|
// for (int i = 0; i < dt.Rows.Count; i++)
|
|
// {
|
|
// var applyCode = $"'{dt.Rows[i]["ApplyCode"]}'";
|
|
// //单据先进行拣料
|
|
// DataTable dateTable = App.GetICSMOPickMergeTemp(applyCode, Type, Mechanism, InvCode);
|
|
// DataRow[] dateRows = dateTable?.Select($@"LotNO <> '' And LotNO is not null");
|
|
// DataRow[] CheckdateRows = dateTable?.Select($@"LotNO = '' or LotNO is null");
|
|
|
|
// if (CheckdateRows.Length > 0)
|
|
// {
|
|
// msg += $"{Environment.NewLine}单据号：{applyCode},库存不足，请先检查库存";
|
|
// }
|
|
// else
|
|
// {
|
|
// string docCode = string.Empty;
|
|
// string docCodeAfter = string.Empty;
|
|
// LOTStockModel lOTStockModel = null;
|
|
|
|
// // 使用 LINQ 根据 Code 字段对 DataRow[] 进行分组
|
|
// var groups = dateRows.Cast<DataRow>().GroupBy(row => new
|
|
// {
|
|
// Code = row.Field<string>("Code"),
|
|
// Sequence = row.Field<string>("Sequence"),
|
|
// iQuantity = row.Field<string>("iQuantity")
|
|
// });
|
|
|
|
// foreach (var group in groups)
|
|
// {
|
|
// List<LOTStockModel> lOTStockModels = new List<LOTStockModel>();
|
|
// var firstRow = group.First();
|
|
// var code = firstRow["Code"];
|
|
// decimal count = 0.0M;
|
|
// string GetIdsql = $@" SELECT ID FROM ICSMOApply WHERE ApplyCode = '{firstRow["Code"]}' and Sequence='{firstRow["Sequence"]}' and WorkPoint='{WorkPoint}' ";
|
|
// var IDDT = SqlHelper.CmdExecuteDataTable(GetIdsql);
|
|
// lOTStockModel = new LOTStockModel
|
|
// {
|
|
// TransID = IDDT.Rows[0]["ID"].ToString(),
|
|
// TransCode = firstRow["Code"].ToString(),
|
|
// TransSequence = firstRow["Sequence"].ToString(),
|
|
// Amount = "0",
|
|
// User = UserCode,
|
|
// MTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
|
|
// WorkPoint = WorkPoint,
|
|
// Detail = new List<LOTStockModelList>(),
|
|
// };
|
|
|
|
// foreach (var row in group)
|
|
// {
|
|
// count += row["QTY"].ToDecimal();
|
|
// LOTStockModelList lOTStockModelList = new LOTStockModelList
|
|
// {
|
|
|
|
// TransSequence = firstRow["Sequence"].ToString(),
|
|
// CurrentQuantity = row["QTY"].ToString(),
|
|
// LotNo = row["LotNo"].ToString(),
|
|
// CurrentAmount = "0",
|
|
// Sequence = row["Sequence"].ToString(),
|
|
// WarehouseCode = row["WarehouseCode"].ToString(),
|
|
// LocationCode = row["LocationCode"].ToString()
|
|
// };
|
|
// lOTStockModel.Detail.Add(lOTStockModelList);
|
|
// }
|
|
// lOTStockModel.Quantity = count.ToString();
|
|
// lOTStockModels.Add(lOTStockModel);
|
|
|
|
// //20240913 在循环中调用PDA接口（U9接口多个单据一次提交会报错）
|
|
// string Inputstr = JsonConvert.SerializeObject(lOTStockModels);
|
|
// string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "LOTStockDown/Create";
|
|
// string result = HttpPost(APIURL, Inputstr);
|
|
// JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
|
|
// string MessAge = Obj["Message"].ToString();
|
|
// string Success = Obj["Success"].ToString();
|
|
|
|
// if (Success.ToUpper() == "FALSE")
|
|
// {
|
|
// msg += $"{Environment.NewLine}单据号：{firstRow["Code"]}，行号：{firstRow["Sequence"]}" + MessAge;
|
|
// }
|
|
|
|
// }
|
|
|
|
// }
|
|
|
|
// }
|
|
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// msg = ex.Message;
|
|
|
|
// }
|
|
// return msg;
|
|
//}
|
|
//#endregion
|
|
|
|
|
|
#region 发料 (按照单据请修改这个)
|
|
/// <summary>
|
|
/// 发料
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string ICSMOApplyByCodeFL(string MOCodes, string Type, string Mechanism, string InvCode)
|
|
{
|
|
string msg = string.Empty;
|
|
try
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
|
|
MOCodes = MOCodes.TrimEnd(',');
|
|
|
|
//获取单据信息
|
|
string ZDsql = $@" SELECT distinct ApplyCode FROM ICSMOApply WHERE ApplyCode in({MOCodes}) and WorkPoint='{WorkPoint}' ";
|
|
var dt = SqlHelper.CmdExecuteDataTable(ZDsql);
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
var applyCode = $"'{dt.Rows[i]["ApplyCode"]}'";
|
|
//单据先进行拣料
|
|
DataTable dateTable = App.GetICSMOPickMergeTemp(applyCode, Type, Mechanism, InvCode);
|
|
DataRow[] dateRows = dateTable?.Select($@"LotNO <> '' And LotNO is not null");
|
|
DataRow[] CheckdateRows = dateTable?.Select($@"LotNO = '' or LotNO is null");
|
|
|
|
if (CheckdateRows.Length > 0)
|
|
{
|
|
msg += $"{Environment.NewLine}单据号：{applyCode},库存不足，请先检查库存";
|
|
}
|
|
else
|
|
{
|
|
string docCode = string.Empty;
|
|
string docCodeAfter = string.Empty;
|
|
|
|
// 使用 LINQ 根据 Code 字段对 DataRow[] 进行分组
|
|
var groups = dateRows.Cast<DataRow>().GroupBy(row =>row.Field<string>("Code"));
|
|
|
|
foreach (var group in groups)
|
|
{
|
|
List<LOTStockModel> lOTStockModels = new List<LOTStockModel>();
|
|
//var firstRow = group.First();
|
|
//根据行号分组
|
|
var rowsGroups = group.ToArray().GroupBy(e=>e.Field<string>("Sequence"));
|
|
string code = group.Key.ToString();
|
|
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
|
foreach (var rowsGroup in rowsGroups)
|
|
{
|
|
string GetIdsql = $@" SELECT ID FROM ICSMOApply WHERE ApplyCode = '{code}' and Sequence='{rowsGroup.Key}' and WorkPoint='{WorkPoint}' ";
|
|
var IDDT = SqlHelper.CmdExecuteDataTable(GetIdsql);
|
|
var lotGroup = rowsGroup.ToArray();
|
|
decimal count = 0.0M;
|
|
LOTStockModel lOTStockModel = new LOTStockModel
|
|
{
|
|
TransID = IDDT.Rows[0]["ID"].ToString(),
|
|
TransCode = code,
|
|
TransSequence = rowsGroup.Key.ToString(),
|
|
Amount = "0",
|
|
User = UserCode,
|
|
MTime = date,
|
|
WorkPoint = WorkPoint,
|
|
Detail = new List<LOTStockModelList>(),
|
|
};
|
|
|
|
|
|
for (int info=0;info< lotGroup.Length;info++)
|
|
{
|
|
count += lotGroup[info]["QTY"].ToDecimal();
|
|
LOTStockModelList lOTStockModelList = new LOTStockModelList
|
|
{
|
|
|
|
TransSequence = rowsGroup.Key.ToString(),
|
|
CurrentQuantity = lotGroup[info]["QTY"].ToString(),
|
|
LotNo = lotGroup[info]["LotNo"].ToString(),
|
|
CurrentAmount = "0",
|
|
Sequence = lotGroup[info]["Sequence"].ToString(),
|
|
WarehouseCode = lotGroup[info]["WarehouseCode"].ToString(),
|
|
LocationCode = lotGroup[info]["LocationCode"].ToString()
|
|
};
|
|
lOTStockModel.Detail.Add(lOTStockModelList);
|
|
}
|
|
lOTStockModel.Quantity = count.ToString();
|
|
lOTStockModels.Add(lOTStockModel);
|
|
|
|
}
|
|
|
|
//20240913 在循环中调用PDA接口（U9接口多个单据一次提交会报错）
|
|
string Inputstr = JsonConvert.SerializeObject(lOTStockModels);
|
|
string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "LOTStockDown/Create";
|
|
string result = HttpPost(APIURL, Inputstr);
|
|
JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
|
|
string MessAge = Obj["Message"].ToString();
|
|
string Success = Obj["Success"].ToString();
|
|
|
|
if (Success.ToUpper() == "FALSE")
|
|
{
|
|
msg += $"{Environment.NewLine}单据号：{code}" + MessAge;
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取物料信息
|
|
public DataTable GetItemList(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT distinct a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd,a.InvUnit
|
|
|
|
FROM ICSInventory a
|
|
LEFT JOIN
|
|
(SELECT Sum(ISnull(Quantity,0)) as Quantity,WorkPoint,INVCode FROM (SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOApply GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN where EATTRIBUTE10='销售出库' GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue WHERE Status<>'3'
|
|
GROUP BY WorkPoint,INVCode)d
|
|
GROUP BY d.INVCode,d.WorkPoint) b
|
|
ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left JOIN( SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM ICSWareHouseLotInfo where Freeze<>1 GROUP BY InvCode, WorkPoint ) c ON c.InvCode=a.INVCode AND c.WorkPoint=a.WorkPoint
|
|
where 1=1";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["INVCODE"].ToString()))
|
|
sql += " and a.INVCODE like '%" + queryParam["INVCODE"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["INVNAME"].ToString()))
|
|
sql += " and a.INVNAME like '%" + queryParam["INVNAME"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
sql += " and a.WorkPoint=('" + WorkPoint + "') ";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 获取物料信息
|
|
public DataTable GetItemListInv(string invCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = $@"SELECT distinct a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd,a.InvUnit
|
|
|
|
FROM ICSInventory a
|
|
LEFT JOIN
|
|
(SELECT Sum(ISnull(Quantity,0)) as Quantity,WorkPoint,INVCode FROM (SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOApply where Status<>'3' GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN where EATTRIBUTE10='销售出库' GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue WHERE Status<>'3'
|
|
GROUP BY WorkPoint,INVCode)d
|
|
GROUP BY d.INVCode,d.WorkPoint) b
|
|
ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left JOIN( SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM ICSWareHouseLotInfo where Freeze<>1 GROUP BY InvCode, WorkPoint ) c ON c.InvCode=a.INVCode AND c.WorkPoint=a.WorkPoint
|
|
where 1=1 and a.INVCODE ='{invCode}' and a.WorkPoint='{WorkPoint}' ";
|
|
|
|
|
|
|
|
return SqlHelper.CmdExecuteDataTable(sql); ;
|
|
}
|
|
#endregion
|
|
|
|
#region 东辉单据类型
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetDHDocType(string code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
string sql = $@" SELECT b.F_ItemName as Name,b.F_ItemCode as Code FROM [dbo].[Sys_SRM_Items] a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON b.F_ItemId = a.F_Id
|
|
WHERE F_EnCode = '{code}' ";
|
|
sql = string.Format(sql, DbHelper.GetErpIpU9(), DbHelper.GetErpNameU9());
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
#endregion
|
|
#region 东辉客户信息
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetCustomer(string code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
string sql = $@" select F_CusCode Code,F_CusName Name from Sys_SRM_Customer WHERE F_CusCode= '{code}'
|
|
AND F_WorkPoint='{WorkPoint}' ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
#endregion
|
|
|
|
#region 东辉单据类型
|
|
/// <summary>
|
|
/// 获取U9单据类型(角色与单据类型绑定,对应角色进入界面只能选择某些单据类型)
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string GetFLDocType(string code)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
List<DocTypeModel> values = new List<DocTypeModel>();
|
|
values.Add(
|
|
new DocTypeModel
|
|
{
|
|
Code = "",
|
|
Name=""
|
|
}
|
|
);
|
|
string sql = $@" SELECT b.F_Define1 as Code,b.F_Define2 as Name FROM [dbo].[Sys_SRM_Items] a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON b.F_ItemId = a.F_Id
|
|
WHERE F_EnCode = '{code}' and F_ItemCode='{role}' ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt.Rows.Count>0)
|
|
{
|
|
var names = dt.Rows[0]["Name"].ToString().Replace('，',',').Split(',').ToList();
|
|
var codes = dt.Rows[0]["Code"].ToString().Replace('，', ',').Split(',').ToList();
|
|
for (int i = 0; i < names.Count; i++) {
|
|
DocTypeModel docTypeModel = new DocTypeModel();
|
|
docTypeModel.Name = names[i].Trim();
|
|
docTypeModel.Code = codes[i].Trim();
|
|
values.Add(docTypeModel);
|
|
}
|
|
|
|
|
|
}
|
|
return values.ToJson();
|
|
}
|
|
#endregion
|
|
|
|
#region 获取是否东辉特有
|
|
public DataTable GetDHCode()
|
|
{
|
|
string sql = "SELECT * FROM Sys_SRM_Items WHERE F_EnCode='ERPENDDeptFilter' AND F_EnabledMark='1' ";
|
|
return SqlHelper.CmdExecuteDataTable(sql); ;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取物料信息
|
|
/// <summary>
|
|
/// 获取物料信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
|
|
public DataTable GetItemListCount(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"--SELECT a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) + ISnull(d.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd
|
|
SELECT a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd
|
|
FROM ICSInventory a
|
|
LEFT JOIN
|
|
(SELECT Sum(ISnull(c.Quantity,0)) as Quantity,c.WorkPoint,c.INVCode FROM (
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut Where Quantity!=OutQuantity
|
|
GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOApply WHERE Status!='3' and Quantity!=IssueQuantity
|
|
GROUP BY WorkPoint,INVCode
|
|
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN where Quantity!=SDNQuantity GROUP BY WorkPoint,INVCode
|
|
UNION ALL
|
|
|
|
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue where Quantity!=IssueQuantity AND Status<>'3'
|
|
GROUP BY WorkPoint,INVCode ) c GROUP BY c.WorkPoint,c.INVCode ) b
|
|
ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left JOIN( SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM ICSWareHouseLotInfo where Freeze<>1 GROUP BY InvCode, WorkPoint) c ON c.InvCode=a.INVCode AND c.WorkPoint=a.WorkPoint
|
|
---left join ICSMOIssue d ON d.InvCode=a.InvCode AND a.WorkPoint=d.WorkPoint
|
|
where 1=1";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["INVCODE"].ToString()))
|
|
sql += " and a.INVCODE like '%" + queryParam["INVCODE"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ApplyCode"].ToString()))
|
|
sql += " and d.ApplyCode like '%" + queryParam["ApplyCode"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
sql += " and a.WorkPoint=('" + WorkPoint + "') ";
|
|
|
|
return SqlHelper.CmdExecuteDataTable(sql);
|
|
}
|
|
#endregion
|
|
|
|
#region 获取物料数量
|
|
/// <summary>
|
|
/// 获取物料数量
|
|
/// </summary>
|
|
/// <param name="invCode">物料编码</param>
|
|
/// <param name="whCode">仓库编码</param>
|
|
/// <param name="batchCode">批次编码</param>
|
|
/// <returns></returns>
|
|
public decimal GetInvCodeCount(string invCode, string whCode, string batchCode)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = $@"EXEC Proc_GetLotStorageQty '{WorkPoint}','{invCode}','{whCode}','{batchCode}'";
|
|
var dt = SqlHelper.CmdExecuteDataTable(sql);
|
|
return dt.Rows[0]["Quantity"].ToDecimal();
|
|
}
|
|
#endregion
|
|
|
|
#region 获取领料部门
|
|
public DataTable GetLYDep()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"SELECT b.F_ItemName as Name,b.F_ItemCode as Code FROM [dbo].[Sys_SRM_Items] a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON b.F_ItemId = a.F_Id
|
|
WHERE F_EnCode = 'LYDep' AND B.F_EnabledMark='1'";
|
|
return SqlHelper.CmdExecuteDataTable(sql);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 获取单据类型
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string GetU9CodeType(string type)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserRole = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.TrimEnd(',');
|
|
string result = String.Empty;
|
|
string APIURL = String.Empty;
|
|
DataTable Resultdt = new DataTable();
|
|
try
|
|
{
|
|
|
|
TypeModel model = new TypeModel
|
|
{
|
|
TypeName = type,
|
|
OrgCode = WorkPoint
|
|
};
|
|
string Inputstr = JsonConvert.SerializeObject(model);
|
|
//验证是否东辉特有信息
|
|
string sql = "SELECT * FROM Sys_SRM_Items WHERE F_EnCode='ERPENDDeptFilter' AND F_EnabledMark='1' ";
|
|
var dt = SqlHelper.CmdExecuteDataTable(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "GetU9DocType ";
|
|
}
|
|
else
|
|
{
|
|
APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "GetU9Information ";
|
|
}
|
|
|
|
result = HttpPost(APIURL, Inputstr);
|
|
if (type == "部门")
|
|
{
|
|
Resultdt = Json2DataTable(result);
|
|
string deptsql = @"select F_Define2 from Sys_SRM_Items A
|
|
LEFT JOIN Sys_SRM_ItemsDetail B ON B.F_ItemId=A.F_Id
|
|
where F_EnCode='AccessoriesIssueRole2DeptConfig' AND B.F_Define1='{0}'";
|
|
deptsql = string.Format(deptsql, UserRole);
|
|
DataTable deptdt = SqlHelper.CmdExecuteDataTable(deptsql);
|
|
if (deptdt.Rows.Count > 0)
|
|
{
|
|
for (int i = Resultdt.Rows.Count - 1; i >= 0; i--)
|
|
{
|
|
bool rightDept = false;
|
|
foreach (DataRow deptdr in deptdt.Rows)
|
|
{
|
|
if (Resultdt.Rows[i]["Code"].ToString() == deptdr["F_Define2"].ToString())
|
|
{
|
|
rightDept = true;
|
|
}
|
|
}
|
|
if (rightDept == false)
|
|
{
|
|
Resultdt.Rows.Remove(Resultdt.Rows[i]);
|
|
}
|
|
}
|
|
result = DataTable2Json(Resultdt);
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = ex.Message;
|
|
|
|
}
|
|
return result.TrimStart(' ');
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// Json转DataTable
|
|
/// </summary>
|
|
/// <param name="json"></param>
|
|
/// <returns></returns>
|
|
private DataTable Json2DataTable(string json)
|
|
{
|
|
JavaScriptSerializer jss = new JavaScriptSerializer();
|
|
ArrayList dic = jss.Deserialize<ArrayList>(json);
|
|
DataTable dtb = new DataTable();
|
|
if (dic.Count > 0)
|
|
{
|
|
foreach (Dictionary<string, object> drow in dic)
|
|
{
|
|
if (dtb.Columns.Count == 0)
|
|
{
|
|
foreach (string key in drow.Keys)
|
|
{
|
|
dtb.Columns.Add(key, drow[key].GetType());
|
|
}
|
|
}
|
|
DataRow row = dtb.NewRow();
|
|
foreach (string key in drow.Keys)
|
|
{
|
|
|
|
row[key] = drow[key];
|
|
}
|
|
dtb.Rows.Add(row);
|
|
}
|
|
}
|
|
return dtb;
|
|
}
|
|
|
|
/// <summary>
|
|
/// DataTable转Json
|
|
/// </summary>
|
|
/// <param name="dtb"></param>
|
|
/// <returns></returns>
|
|
private string DataTable2Json(DataTable dtb)
|
|
{
|
|
JavaScriptSerializer jss = new JavaScriptSerializer();
|
|
ArrayList dic = new ArrayList();
|
|
foreach (DataRow row in dtb.Rows)
|
|
{
|
|
Dictionary<string, object> drow = new Dictionary<string, object>();
|
|
foreach (DataColumn col in dtb.Columns)
|
|
{
|
|
drow.Add(col.ColumnName, row[col.ColumnName]);
|
|
}
|
|
dic.Add(drow);
|
|
}
|
|
|
|
return jss.Serialize(dic);
|
|
}
|
|
|
|
#region 关闭单据
|
|
/// <summary>
|
|
/// 关闭单据
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string CloseICSMOApplyByCode(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@" UPDATE dbo.ICSMOApply SET Status='3' WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取单号
|
|
/// <summary>
|
|
/// 获取单号
|
|
/// </summary>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public string GetBidCode(string WorkPoint)
|
|
{
|
|
WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string BidCide = string.Empty;
|
|
if (!string.IsNullOrEmpty(WorkPoint))
|
|
{
|
|
string DateString = DateTime.Now.ToString("yyyyMMdd");
|
|
|
|
string Pre = "CLCK" + DateString;
|
|
BidCide = GetSerialCode(WorkPoint, "ICSMOApply", "ApplyCode", Pre, 4);
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(BidCide))
|
|
{
|
|
string sqlISHave = @"SELECT ApplyCode FROM ICSMOApply a
|
|
WHERE a.ApplyCode = '{0}'";
|
|
sqlISHave = string.Format(sqlISHave, BidCide);
|
|
DataTable dtIsHave = SqlHelper.GetDataTableBySql(sqlISHave);
|
|
if (dtIsHave.Rows.Count > 0)
|
|
{
|
|
throw new Exception("单号已存在！");
|
|
}
|
|
}
|
|
return BidCide;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 获取单据必填项
|
|
/// <summary>
|
|
/// 获取单据必填项
|
|
/// </summary>
|
|
/// <param name="dept">受益部门</param>
|
|
/// <param name="codeType">单据类型</param>
|
|
/// <returns></returns>
|
|
public DataTable GetCodeInformation(string dept, string codeType)
|
|
{
|
|
string sql = $@"SELECT F_Define2
|
|
FROM Sys_SRM_Items a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON a.F_Id=b.F_ItemId
|
|
WHERE F_EnCode='DOC001' ";
|
|
if (!string.IsNullOrEmpty(dept))
|
|
{
|
|
sql += $" and b.F_Define1='{dept}' ";
|
|
}
|
|
if (!string.IsNullOrEmpty(codeType))
|
|
{
|
|
sql += $" and b.F_ItemName='{codeType}' ";
|
|
}
|
|
return SqlHelper.CmdExecuteDataTable(sql);
|
|
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 获取单据必填项
|
|
/// <summary>
|
|
/// 获取单据必填项
|
|
/// </summary>
|
|
/// <param name="dept">受益部门</param>
|
|
/// <param name="codeType">单据类型</param>
|
|
/// <returns></returns>
|
|
public DataTable GetUserDept()
|
|
{
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string sql = $@"SELECT F_Define1
|
|
FROM Sys_SRM_Items a
|
|
INNER JOIN Sys_SRM_ItemsDetail b ON a.F_Id=b.F_ItemId
|
|
WHERE F_EnCode='UserDept' AND b.F_ItemCode='{UserCode}' ";
|
|
return SqlHelper.CmdExecuteDataTable(sql);
|
|
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 获取序列号
|
|
/// <summary>
|
|
/// 获取序列号
|
|
/// </summary>
|
|
/// <param name="workPointCode"></param>
|
|
/// <param name="tbName"></param>
|
|
/// <param name="colName"></param>
|
|
/// <param name="Pre"></param>
|
|
/// <param name="numLen"></param>
|
|
/// <returns></returns>
|
|
public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
|
|
{
|
|
string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
|
|
sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
|
|
return SqlHelper.ExecuteScalar(sql).ToString();
|
|
}
|
|
#endregion
|
|
|
|
#region 修改单据
|
|
/// <summary>
|
|
/// 修改单据
|
|
/// </summary>
|
|
/// <param name="ICSASN"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSMOApply(string ICSASN)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string msg = "";
|
|
try
|
|
{
|
|
|
|
// 获取解析后的数据
|
|
JsonData data = JsonConvert.DeserializeObject<JsonData>(ICSASN);
|
|
//查询单据已创建数量
|
|
var DocSql = $@" SELECT Quantity,Sequence,InvCode from ICSMOApply WHERE ApplyCode='{data.Code}' AND WorkPoint='{WorkPoint}' ";
|
|
var dt = SqlHelper.CmdExecuteDataTable(DocSql);
|
|
string sql = $@"IF EXISTS( select ID from ICSMOApply where ApplyCode = '{data.Code}' and WorkPoint ='{WorkPoint}' AND Status='2')
|
|
BEGIN
|
|
RAISERROR('该单据已审核，不能修改！',16,1);
|
|
RETURN
|
|
END;
|
|
DECLARE @CreatedCode VARCHAR(50);
|
|
DECLARE @CreatedName VARCHAR(50);
|
|
DECLARE @CreateDateTime VARCHAR(100);
|
|
SET @CreatedCode =(SELECT top 1 CreatePerson from ICSMOApply where ApplyCode = '{data.Code}' and WorkPoint ='{WorkPoint}');
|
|
SET @CreateDateTime =(SELECT top 1 CONVERT(varchar(100), CreateDateTime, 21) from ICSMOApply where ApplyCode = '{data.Code}' and WorkPoint ='{WorkPoint}');
|
|
delete from ICSMOApply where ApplyCode = '{data.Code}'; ";
|
|
string str1 = "";
|
|
List<string> ExtensionIDList = new List<string>();
|
|
|
|
foreach (var detail in data.Detail)
|
|
{
|
|
string a = $"InvCode = '{detail.InvCode}' ";
|
|
decimal beforCount = dt.Select(a).Sum(x => x.Field<decimal>("Quantity"));
|
|
///获取物料的可用数量，判断当前创建单据数量
|
|
decimal invCount = GetInvCodeCount(detail.InvCode, "", "");
|
|
//获取创建单据的所有的物料信息
|
|
decimal allCount = data.Detail.Where(e => e.InvCode == detail.InvCode).Select(f => f.Quantity).Sum();
|
|
if (invCount < (allCount - beforCount))
|
|
{
|
|
throw new Exception($"物料{detail.InvCode}创建单据总数量：{allCount}不能大于库存可用量：{invCount}");
|
|
}
|
|
string Colspan = "~" + detail.Batch + "~~~~~~~~~~~~";
|
|
string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
|
|
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
|
|
bool flag = true;
|
|
foreach (var item in ExtensionIDList)
|
|
{
|
|
if (item == Colspan + WorkPoint)
|
|
{
|
|
flag = false;
|
|
}
|
|
}
|
|
if (ExtensionID == null && flag == true)
|
|
{
|
|
str1 = Guid.NewGuid().ToString();
|
|
sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
|
|
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
|
|
str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
|
|
data.User, UserName, WorkPoint);
|
|
}
|
|
else if (ExtensionID != null)
|
|
{
|
|
str1 = ExtensionID.ToString();
|
|
}
|
|
ExtensionIDList.Add(Colspan + WorkPoint);
|
|
|
|
sql += $@"INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode,EATTRIBUTE11,EATTRIBUTE15,EATTRIBUTE16,EATTRIBUTE17,EATTRIBUTE18,EATTRIBUTE19,EATTRIBUTE20) select '{detail.ID}','{data.Code}','{detail.WHCode}','{detail.Sequence}','{detail.InvCode}','{detail.Quantity}',@CreatedCode,@CreateDateTime,'{WorkPoint}','1','{data.Code}',newID(),'{str1}','{UserCode}','{UserName}','{data.MTIME}','{data.Dept}','{data.CodeType}','{data.ApplyNegCode}','{data.Custmer}','{data.Remarks}','{detail.DetailReamrk}','{detail.SYproject}','杂发','{detail.SourceCode}','1','{data.Remarks2}','{detail.DetailReamrk2}',0,'{data.LYDept}','{data.Remarks3}','{data.DeptName}','{data.LYDeptName}','{data.CodeTypeName}','{data.ApplyNegName}','{data.CustmerName}','{detail.SYprojectName}'";
|
|
|
|
}
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取单条信息
|
|
public DataTable GetICSReturnTemporary(string rfqno)
|
|
{
|
|
string sql = @"SELECT
|
|
a.ID,
|
|
a.ApplyCode,
|
|
a.InvCode,
|
|
b.InvName as INVNAME,
|
|
b.InvStd,
|
|
b.InvUnit,
|
|
a.Quantity,
|
|
a.SourceCode,
|
|
a.WHCode,
|
|
a.WHCode as WHCodeHHH,
|
|
a.EATTRIBUTE6,
|
|
a.EATTRIBUTE9,
|
|
a.SourceCode,
|
|
a.Quantity
|
|
,f.BatchCode as Batch
|
|
,c.WarehouseName as WHCodeName
|
|
,a.EATTRIBUTE7 as SYproject,
|
|
a.EATTRIBUTE11 ,
|
|
a.DepCode
|
|
FROM ICSMOApply a
|
|
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode
|
|
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSWarehouse c ON a.WHCode=c.WarehouseCode AND a.WorkPoint=c.WorkPoint
|
|
where a.ApplyCode='" + rfqno + "'";
|
|
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;
|
|
}
|
|
#endregion
|
|
|
|
#region 接口api解析
|
|
/// <summary>
|
|
/// 接口api解析
|
|
/// </summary>
|
|
/// <param name="url"></param>
|
|
/// <param name="body"></param>
|
|
/// <returns></returns>
|
|
public static string HttpPost(string url, string body)
|
|
|
|
{
|
|
try
|
|
{
|
|
Encoding encoding = Encoding.UTF8;
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
request.Method = "POST";
|
|
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
|
|
request.ContentType = "application/json; charset=utf-8";
|
|
|
|
byte[] buffer = encoding.GetBytes(body);
|
|
request.ContentLength = buffer.Length;
|
|
request.GetRequestStream().Write(buffer, 0, buffer.Length);
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
|
|
{
|
|
return reader.ReadToEnd();
|
|
}
|
|
}
|
|
catch (WebException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 深拷贝
|
|
public static T DeepCopyByReflect<T>(T obj)
|
|
{
|
|
if (obj == null)
|
|
return obj;
|
|
|
|
var type = obj.GetType();
|
|
//如果是字符串或值类型则直接返回
|
|
if (obj is string || type.IsValueType) return obj;
|
|
if (type.IsArray)
|
|
{
|
|
var elementType = Type.GetType(type.FullName.Replace("[]", null));
|
|
var array = obj as Array;
|
|
var copied = Array.CreateInstance(elementType, array.Length);
|
|
for (int idx = 0; idx < copied.Length; idx++)
|
|
{
|
|
copied.SetValue(DeepCopyByReflect(array.GetValue(idx)), idx);
|
|
}
|
|
return (T)Convert.ChangeType(copied, type);
|
|
}
|
|
|
|
|
|
object retval = Activator.CreateInstance(obj.GetType());
|
|
FieldInfo[] fields = obj.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static);
|
|
foreach (FieldInfo field in fields)
|
|
{
|
|
try { field.SetValue(retval, DeepCopyByReflect(field.GetValue(obj))); }
|
|
catch { }
|
|
}
|
|
return (T)retval;
|
|
}
|
|
#endregion
|
|
|
|
#region 数据导入
|
|
|
|
/// <summary>
|
|
/// 数据导入
|
|
/// </summary>
|
|
/// <param name="savePath"></param>
|
|
/// <param name="Year"></param>
|
|
/// <returns></returns>
|
|
public string SetData_PR(String savePath, string Year)
|
|
{
|
|
//数据获取
|
|
try
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
|
|
int index = 1;
|
|
string Tday = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
|
string sql = string.Empty;
|
|
string[] nameString = { "单据类型名称", "受益部门名称", "料品编码", "数量" };
|
|
//获取单据类型
|
|
string jsonstring = GetU9CodeType("杂发");
|
|
//获取项目信息
|
|
string jsonProject = GetU9CodeType("项目");
|
|
//获取部门信息
|
|
string jsonDep = GetU9CodeType("部门");
|
|
var docType = JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonstring);
|
|
var projectType = JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonProject);
|
|
var depType = JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonDep);
|
|
var LYDepType = JsonConvert.DeserializeObject<List<DocTypeModel>>(GetLYDep().ToJson());
|
|
string applyCode = GetBidCode(WorkPoint);
|
|
Pagination pagination = new Pagination();
|
|
pagination.rows = 20;
|
|
pagination.sidx = "INVCODE";
|
|
pagination.sord = "desc";
|
|
pagination.page = 1;
|
|
if (data != null && data.Rows.Count > 0)
|
|
{
|
|
|
|
foreach (DataRow dr in data.Rows)
|
|
{
|
|
index++;
|
|
for (int i = 0; i < nameString.Length; i++)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(dr[nameString[i]].ToString()))
|
|
{
|
|
throw new Exception($"第{index}行，{nameString[i]}不能为空！");
|
|
}
|
|
}
|
|
string queryJson = "{\"INVCODE\":\"" + dr["料品编码"].ToString() + "\",\"ApplyCode\":\"\"}";
|
|
DataTable ListData = GetItemListCount(queryJson, ref pagination);
|
|
if (ListData == null || ListData.Rows.Count == 0)
|
|
throw new Exception($"物料编码[{dr["料品编码"]}]没有库存");
|
|
decimal count = ListData.Rows[0]["Quantity"].ToDecimal();
|
|
string DocTypeName = dr["单据类型名称"].ToString();
|
|
string SYDep = string.Empty;
|
|
string SYDepName = dr["受益部门名称"].ToString();
|
|
string InvCode = dr["料品编码"].ToString();
|
|
string Count = dr["数量"].ToString();
|
|
string ZJProject = dr["在建工程项目"].ToString();
|
|
string CusCode = dr["客户编码"].ToString();
|
|
string RDCode = dr["RD编码"].ToString();
|
|
string ProjectCode = dr["项目编码"].ToString();
|
|
string LYDep = string.Empty;
|
|
string LYDepName = dr["领用部门名称"].ToString();
|
|
string WHCode = dr["仓库编码"].ToString();
|
|
string BatchCode = dr["批号"].ToString();
|
|
string SYProject = string.Empty;
|
|
string SYProjectName = dr["受益项目名称"].ToString();
|
|
string MOCode = dr["工单"].ToString();
|
|
string ReturnCount = dr["回收数量"].ToString();
|
|
string Ramark = dr["备注"].ToString();
|
|
string str1 = string.Empty;
|
|
//单据类型
|
|
List<DocTypeModel> DocTypeModel = docType?.Where(e => e.Name == DocTypeName).ToList();
|
|
if (DocTypeModel.IsEmpty())
|
|
{
|
|
throw new Exception($"第{index}行，单据类型不存在！");
|
|
}
|
|
string DocType = DocTypeModel[0].Code;
|
|
//受益部门
|
|
if (!string.IsNullOrEmpty(SYDepName))
|
|
{
|
|
List<DocTypeModel> depTypeModel = depType?.Where(e => e.Name == SYDepName).ToList();
|
|
if (depTypeModel.IsEmpty())
|
|
{
|
|
throw new Exception($"第{index}行，受益部门不存在！");
|
|
}
|
|
SYDep = depTypeModel[0].Code;
|
|
}
|
|
//领用部门
|
|
if (!string.IsNullOrEmpty(LYDepName))
|
|
{
|
|
List<DocTypeModel> LYDepModel = LYDepType?.Where(e => e.Name == LYDepName).ToList();
|
|
if (LYDepModel.IsEmpty())
|
|
{
|
|
throw new Exception($"第{index}行，领用部门不存在！");
|
|
}
|
|
LYDep = LYDepModel[0].Code;
|
|
}
|
|
//受益项目
|
|
if (!string.IsNullOrEmpty(SYProjectName))
|
|
{
|
|
List<DocTypeModel> SYprojectModel = projectType.Where(e => e.Name == SYProjectName).ToList();
|
|
if (SYprojectModel.IsEmpty())
|
|
{
|
|
throw new Exception($"第{index}行，受益项目不存在！");
|
|
}
|
|
SYProject = SYprojectModel[0].Code;
|
|
}
|
|
|
|
if (count < Count.ToDecimal())
|
|
{
|
|
throw new Exception($"第{index}行，物料编码：{InvCode}，创建单据数量不能超过库存数量！");
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(dr["批号"].ToString()))
|
|
{
|
|
var Colspan = "~" + dr["批号"].ToString() + "~~~~~~~~~~~~";
|
|
string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
|
|
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
|
|
if (ExtensionID == null)
|
|
{
|
|
str1 = Guid.NewGuid().ToString();
|
|
sql += $@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, MTIME, MUSER, MUSERName, WorkPoint)
|
|
Values('{str1}','{Colspan}','','{dr["批号"]}','','',getdate(),'{MUSER}','{MUSERNAME}','{WorkPoint}')";
|
|
|
|
}
|
|
else
|
|
{
|
|
str1 = ExtensionID.ToString();
|
|
}
|
|
}
|
|
|
|
sql += $@"IF not EXISTS( SELECT ID FROM ICSInventory WHERE InvCode='{InvCode}' AND WorkPoint='{WorkPoint}')
|
|
BEGIN
|
|
RAISERROR('第{index}行物料不存在，导入失败！',16,1);
|
|
RETURN
|
|
END;
|
|
INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode) Values(newID(),'{applyCode}','{WHCode}','{index - 1}','{InvCode}','{Count}','{MUSER}','{Tday}','{WorkPoint}','1',newID(),newID(),'{str1}','{MUSER}','{MUSERNAME}','{Tday}','{SYDep}','{DocType}','{ZJProject}','{CusCode}','{RDCode}','{ReturnCount}','{SYProject}','杂发','{MOCode}','1','{ProjectCode}','{Ramark}',0,'{LYDep}');";
|
|
|
|
|
|
|
|
}
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) <= 0)
|
|
{
|
|
return "导入失败，新增数据错误！";
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
return "无有效的导入数据。";
|
|
|
|
}
|
|
return "true";
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region Model
|
|
|
|
// 定义一个类来表示 JSON 数据的结构
|
|
public class JsonData
|
|
{
|
|
public string Dept { get; set; }
|
|
public string DeptName { get; set; }
|
|
public string LYDept { get; set; }
|
|
public string LYDeptName { get; set; }
|
|
public string CodeType { get; set; }
|
|
public string CodeTypeName { get; set; }
|
|
public string ApplyNegCode { get; set; }
|
|
public string ApplyNegName { get; set; }
|
|
public string Custmer { get; set; }
|
|
public string CustmerName { get; set; }
|
|
public string Remarks { get; set; }
|
|
public string Remarks2 { get; set; }
|
|
public string Remarks3 { get; set; }
|
|
public string Code { get; set; }
|
|
public string MTIME { get; set; }
|
|
public string User { get; set; }
|
|
|
|
public List<JsonDetail> Detail { get; set; }
|
|
}
|
|
|
|
public class JsonDetail
|
|
{
|
|
public string ID;
|
|
public int Sequence { get; set; }
|
|
public string InvCode { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
public string SYproject { get; set; }
|
|
public string SYprojectName { get; set; }
|
|
public string WHCode { get; set; }
|
|
/// <summary>
|
|
/// 工单号
|
|
/// </summary>
|
|
public string SourceCode { get; set; }
|
|
public string DetailReamrk { get; set; }
|
|
public string DetailReamrk2 { get; set; }
|
|
public string Batch { get; set; }
|
|
}
|
|
|
|
/// <summary>
|
|
/// 材料出库
|
|
/// </summary>
|
|
public class LOTStockModel
|
|
{
|
|
/// <summary>
|
|
/// 主键
|
|
/// </summary>
|
|
public string TransID { get; set; }
|
|
/// <summary>
|
|
/// 源头单据号
|
|
/// </summary>
|
|
public string TransCode { get; set; }
|
|
|
|
/// <summary>
|
|
/// 源头单据行号
|
|
/// </summary>
|
|
public string TransSequence { get; set; }
|
|
/// <summary>
|
|
/// 数量
|
|
/// </summary>
|
|
public string Quantity { get; set; }
|
|
/// <summary>
|
|
/// 辅计量数量
|
|
/// </summary>
|
|
public string Amount { get; set; }
|
|
/// <summary>
|
|
/// 操作类型
|
|
/// </summary>
|
|
public string TransType { get; set; } = "生产发料-领料申请单";
|
|
/// <summary>
|
|
/// 操作人
|
|
/// </summary>
|
|
public string User { get; set; }
|
|
/// <summary>
|
|
/// 操作时间
|
|
/// </summary>
|
|
public string MTime { get; set; }
|
|
|
|
|
|
public string WorkPoint { get; set; }
|
|
public List<LOTStockModelList> Detail { get; set; }
|
|
|
|
}
|
|
public class LOTStockModelList
|
|
{
|
|
/// <summary>
|
|
/// 源头单据行号
|
|
/// </summary>
|
|
public string TransSequence { get; set; }
|
|
/// <summary>
|
|
/// 条码
|
|
/// </summary>
|
|
public string LotNo { get; set; }
|
|
/// <summary>
|
|
/// 数量
|
|
/// </summary>
|
|
public string CurrentQuantity { get; set; }
|
|
/// <summary>
|
|
/// 辅计量数量
|
|
/// </summary>
|
|
public string CurrentAmount { get; set; }
|
|
|
|
public string Sequence { get; set; }
|
|
|
|
/// <summary>
|
|
/// 仓库代码
|
|
/// </summary>
|
|
public string WarehouseCode { get; set; }
|
|
/// <summary>
|
|
/// 库位代码
|
|
/// </summary>
|
|
public string LocationCode { get; set; }
|
|
/// <summary>
|
|
/// LogID
|
|
/// </summary>
|
|
public string LogID { get; set; }
|
|
}
|
|
|
|
public class TypeModel
|
|
{
|
|
/// <summary>
|
|
/// 名称
|
|
/// </summary>
|
|
public string TypeName { get; set; }
|
|
/// <summary>
|
|
/// 组织
|
|
/// </summary>
|
|
public string OrgCode { get; set; }
|
|
|
|
}
|
|
|
|
public class DocTypeModel
|
|
{
|
|
/// <summary>
|
|
/// 名称
|
|
/// </summary>
|
|
public string Name { get; set; }
|
|
/// <summary>
|
|
/// 编码
|
|
/// </summary>
|
|
public string Code { get; set; }
|
|
|
|
}
|
|
#endregion
|
|
|
|
}
|
|
}
|