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.
 
 
 
 
 

620 lines
26 KiB

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
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.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
namespace NFine.Application.KBSWMS
{
public class ICSUpdateMoPickApp : RepositoryFactory<ICSVendor>
{
public DataTable GetICSMOPickInfo(string queryJson, ref Pagination jqgridparam)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @" select b.ID,a.MOCode,b.Sequence,b.InvCode,c.InvName,b.Quantity,b.IssueQuantity from dbo.ICSMO a
left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
left join dbo.ICSInventory c on b.InvCode=c.InvCode and b.WorkPoint=c.WorkPoint
where a.MOCode='" + queryParam["Code"].ToString() + "'";
//if (!string.IsNullOrWhiteSpace(queryJson))
//{
// if (!string.IsNullOrWhiteSpace(queryParam["Code"].ToString()))
// {
// sql += " and a.ApplyNegCode like '%" + queryParam["Code"].ToString() + "%' ";
// }
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
public string UpdateMopickInfoByCode(string Code)
{
string msg = "";
try
{
//检验输入的工单号是否存在
string sqlSeach = @"select * from dbo.ICSMO where MOCode='" + Code + "'";
DataTable dtMoInfo = SqlHelper.GetDataTableBySql(sqlSeach);
if (dtMoInfo == null )
{
throw new Exception("所输入的工单号未查询到相关单据信息!");
}
//调用同步方法更新子件数据
msg= ExecuteMoPick(Code);
}
catch (Exception ex)
{
msg=ex.Message;
}
return msg;
}
public string ExecuteMoPick(string Code)
{
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.Empty;
string sqlSeachMopickInfo = @"select b.PickID from dbo.ICSMO a
left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
where a.MOCode='" + Code + "'";
DataTable dtMoPickInfo = SqlHelper.GetDataTableBySql(sqlSeachMopickInfo);
string ERPUrl = ConfigurationManager.ConnectionStrings["MopickERPMUrl"].ConnectionString;
string Namespace = this.GetType().Namespace;
string Class = this.GetType().Name + WorkPoint;
StringBuilder sql = new StringBuilder();
QueryModel input = new QueryModel();
input.time = "2000-01-01";
input.pageSize = 2000;
input.workorder = Code;
//获取token
string token = GetToken.GetTokenInfermation();
string Inputstr = JsonConvert.SerializeObject(input);
string resultStr = HTTPHelper.HttpPost("生产备料", ERPUrl + "KBS_erp_wms_sfq", Inputstr, token);
ResultModel result = new ResultModel();
result = JsonConvert.DeserializeObject<ResultModel>(resultStr);
if (result.success && result.count > 0)
{
try
{
string extensionID = string.Empty;
//获取ICSExtensionID
string IDString = $@"SELECT ID from ICSExtension WHERE WorkPoint='{WorkPoint}' AND Colspan='~~~~~~~~~~~~~'";
var IDdt = SqlHelper.ExecuteTable(IDString);
if (IDdt.Rows.Count > 0)
{
extensionID = IDdt.Rows[0]["ID"].ToString();
}
else
{
extensionID = SqlHelper.GetNewid();
Dictionary<string, string> extensionvalue = new Dictionary<string, string>
{
{ "ID", $"'{extensionID}'" },
{ "BatchCode", "''" },
{ "version", "''" },
{ "Brand", "''" },
{ "ProjectCode", "''" },
{ "cFree1", "''" },
{ "cFree2", "''" },
{ "cFree3", "''" },
{ "cFree4", "''" },
{ "cFree5", "''" },
{ "cFree6", "''" },
{ "cFree7", "''" },
{ "cFree8", "''" },
{ "cFree9", "''" },
{ "cFree10", "''" },
{ "Colspan", "'" + "~~~~~~~~~~~~~" + "'" },
{ "MTIME", "GETDATE()" },
{ "MUSER", "'" + MUSER + "'" },
{ "MUSERName", "'" + MUSERNAME + "'" },
{ "WorkPoint", "'" + WorkPoint + "'" }
};
sql.Append(SqlHelper.InsertSQLKingDeeEx("ICSExtension", extensionvalue, WorkPoint));
}
var res = (List<SFAModel>)JsonConvert.DeserializeObject(result.data.list.ToString(), typeof(List<SFAModel>));
var sw1 = new Stopwatch();
sw1.Start();
//获取数据库的委外备料子件数据
string moPickString = $@"SELECT max(Cast(Sequence as int)) as Sequence, MODetailID,WorkPoint from ICSMOPick
GROUP BY MODetailID,WorkPoint";
var moPickdt = SqlHelper.ExecuteTable( moPickString);
//自由项
Dictionary<string, string> value = new Dictionary<string, string>();
var groups = res.GroupBy(o => o.SFB01).ToList();
sw1.Stop();
// Debug.WriteLine("查询时间" + sw1.Elapsed.TotalSeconds);
var sw = new Stopwatch();
sw.Restart();
foreach (var item in groups)
{
var data = item.OrderBy(o => o.RNUM).ToList();
for (int idx = 0; idx < data.Count; idx++)
{
//处理查询到的工单子件信息
string currentPickID = $"{data[idx].SFB01}~1~{data[idx].SFA27}";
for (int i = dtMoPickInfo.Rows.Count - 1; i >= 0; i--)
{
if (dtMoPickInfo.Rows[i]["PickID"].ToString() == currentPickID)
{
dtMoPickInfo.Rows.RemoveAt(i);
break;
}
}
Dictionary<string, string> values = new Dictionary<string, string>();
//委外备料
var MODetailID = $"'{data[idx].SFB01}~1'";
//查询数据库中有没有备料信息,获取行号信息
var Seqence = 0;
if (moPickdt != null && moPickdt.Rows.Count > 0)
{
var tmp = moPickdt.Select($"MODetailID = {MODetailID} and WorkPoint = '{data[idx].LEGAL}'");
if (tmp.Length > 0)
{
Seqence = int.Parse(tmp[0]["Sequence"].ToString()) + idx + 1;
}
else
{
Seqence = idx + 1;
}
}
else
{
Seqence = idx + 1;
}
values.Add("Sequence", $"'{Seqence}'");
values.Add("MODetailID", MODetailID);
values.Add("PickID", $"'{data[idx].SFB01}~1~{data[idx].SFA27}'");
values.Add("InvCode", $"'{data[idx].SFA27}'");
values.Add("Quantity", $"(SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA05})");
values.Add("ParentQuantity", "'0'");
values.Add("ParentAmount", "'0'");
values.Add("Amount", $"'{data[idx].SFA05}'");
values.Add("WHCode", "''");
values.Add("SupplyType", "'3'");
values.Add("ExtensionID", $"'{extensionID}'");
values.Add("MTIME", $"'{data[idx].SFBDATE}'");
values.Add("WorkPoint", $"'{data[idx].LEGAL}'");
values.Add("ID", "NEWID()");
//values.Add("IssueQuantity", $" CASE WHEN IssueQuantity>0 THEN IssueQuantity ELSE (SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA06}) END");
values.Add("IssueQuantity", $" (SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA06})");
values.Add("MUSER", $"'{data[idx].SFBMODU}'");
values.Add("MUSERName", $"'{data[idx].GEN02}'");
values.Add("EATTRIBUTE1", $"'0'");
sql.Append(SqlHelper.InsertSQLKingDee("ICSMOPick", values, $"MODetailID = {MODetailID} and InvCode= '{data[idx].SFA27}' and WorkPoint='{data[idx].LEGAL}' "));
}
}
// 构建删除 SQL 语句
if (dtMoPickInfo.Rows.Count > 0)
{
StringBuilder deleteSql = new StringBuilder();
deleteSql.Append("DELETE FROM ICSMOPick WHERE PickID IN (");
for (int i = 0; i < dtMoPickInfo.Rows.Count; i++)
{
if (i > 0)
{
deleteSql.Append(",");
}
deleteSql.Append($"'{dtMoPickInfo.Rows[i]["PickID"]}'");
}
deleteSql.Append(")");
SqlHelper.ExecuteDate(deleteSql.ToString());
}
SqlHelper.ExecuteDate( $"{sql}");
sql = new StringBuilder();
sw.Stop();
}
catch (Exception ex)
{
msg = ex.Message;
}
}
return msg;
}
public string UpdateMoInfoByCode(string Code)
{
string msg = "";
try
{
//检验输入的工单号是否存在
string sqlSeach = @"select * from dbo.ICSMO where MOCode='" + Code + "'";
DataTable dtMoInfo = SqlHelper.GetDataTableBySql(sqlSeach);
if (dtMoInfo == null)
{
throw new Exception("所输入的工单号未查询到相关单据信息!");
}
//调用同步方法更新工单数据
msg = ExecuteMo(Code);
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
public string ExecuteMo(string Code)
{
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 ERPUrl = ConfigurationManager.ConnectionStrings["MopickERPMUrl"].ConnectionString;
string msg = string.Empty;
string Namespace = this.GetType().Namespace;
string Class = this.GetType().Name + WorkPoint;
StringBuilder sql = new StringBuilder();
QueryModel input = new QueryModel();
input.time = "2000-01-01";
input.workorder = Code;
//获取token
string token = GetToken.GetTokenInfermation();
string Inputstr = JsonConvert.SerializeObject(input);
string resultStr = HTTPHelper.HttpPost("生产订单", ERPUrl + "KBS_erp_wms_sfb", Inputstr, token);
ResultModel result = new ResultModel();
result = JsonConvert.DeserializeObject<ResultModel>(resultStr);
if (result.success && result.count > 0)
{
try
{
JArray res = (JArray)JsonConvert.DeserializeObject(result.data.list.ToString());
var sw = new Stopwatch();
sw.Start();
//自由项
Dictionary<string, string> value = new Dictionary<string, string>();
value.Add("ID", "newid()");
value.Add("BatchCode", "''");
value.Add("version", "''");
value.Add("Brand", "''");
value.Add("ProjectCode", "''");
value.Add("cFree1", "''");
value.Add("cFree2", "''");
value.Add("cFree3", "''");
value.Add("cFree4", "''");
value.Add("cFree5", "''");
value.Add("cFree6", "''");
value.Add("cFree7", "''");
value.Add("cFree8", "''");
value.Add("cFree9", "''");
value.Add("cFree10", "''");
value.Add("Colspan", "'" + "~~~~~~~~~~~~~" + "'");
value.Add("MTIME", "GETDATE()");
value.Add("MUSER", "'" + MUSER + "'");
value.Add("MUSERName", "'" + MUSERNAME + "'");
value.Add("WorkPoint", "'" + WorkPoint + "'");
sql.Append(SqlHelper.InsertSQLKingDeeEx("ICSExtension", value,WorkPoint));
int i = 1;
foreach (var item in res)
{
JObject det = (JObject)item;
if (i % 1000 == 0)
{
SqlHelper.ExecuteDate( $"{sql}");
sql = new StringBuilder();
}
//采购订单
Dictionary<string, string> values = new Dictionary<string, string>();
values.Add("MOCODE", "'" + det["SFB01"].ToString().Trim() + "'");//工单号
values.Add("Sequence", "'1'");//行号(鼎捷不返回)
values.Add("InvCode", "'" + det["SFB05"].ToString().Trim() + "'");//物料代码
values.Add("Quantity", "'" + det["SFB08"].ToString().Trim() + "'");//数量
values.Add("Amount", "'0'");//辅计量
values.Add("DepCode", "'" + det["SFB82"].ToString().Trim() + "'");//部门代码
values.Add("WHCode", "'" + det["SFB30"].ToString().Trim() + "'");//仓库代码
values.Add("StartDate", "'" + det["SFB25"].ToString().Trim() + "'");//开工日期
values.Add("DueDate", "'" + det["SFB18"].ToString().Trim() + "'");//完工日期
values.Add("MOSTATUS", "'1'");//工单状态
values.Add("MOMemo", "''");//备注
values.Add("CreatePerson", "'" + det["SFBORIU"].ToString() + "'");//创建人
values.Add("CreateDateTime", "'" + det["SFB81"].ToString().Trim() + "'");//创建时间
values.Add("ERPStatus", "'2'");//生产订单状态(???怎么判断的)
values.Add("MOID", $"'{det["SFB01"]}'");
values.Add("MODetailID", $"'{det["SFB01"]}~1'");
values.Add("ExtensionID", $"(select id from ICSExtension where Colspan = '~~~~~~~~~~~~~' and WorkPoint = '{det["LEGAL"]}') ");//自由项需要最后update
values.Add("ID", "newid()");
values.Add("MUSER", "'" + det["SFBMODU"].ToString().Trim() + "'");
values.Add("MUSERName", "'" + det["GEN02"].ToString().Trim() + "'");
values.Add("WorkPoint", "'" + det["LEGAL"].ToString().Trim() + "'");
values.Add("MTIME", "'" + det["SFBDATE"].ToString().Trim() + "'");
sql.Append(SqlHelper.InsertSQLKingDee("ICSMO", values, "MOCODE ='" + det["SFB01"].ToString().Trim() + $"' and MODetailID='{det["SFB01"]}~{1}' and WorkPoint='{det["LEGAL"]}'"));
i++;
}
SqlHelper.ExecuteDate($"{sql}");
sw.Stop();
}
catch (Exception ex)
{
msg = ex.Message;
}
}
return msg;
}
/// <summary>
/// 查询接口Model
/// </summary>
public class QueryModel
{
/// <summary>
/// 时间
/// </summary>
public string time { get; set; }
/// <summary>
/// 页码
/// </summary>
public int pageIndex { get; set; } = 1;
/// <summary>
/// 页数
/// </summary>
public int pageSize { get; set; } = int.MaxValue;
public string workorder { get; set; }
}
public class TokenModel
{
public string token { get; set; }
}
public class ResultModel
{
public bool success { get; set; }
public string message { get; set; }
public int code { get; set; }
public int count { get; set; }
public DateTime? maxTime { get; set; }
public DataList data { get; set; }
}
public class DataList
{
public object count { get; set; }
public object list { get; set; }
}
public class SFAModel
{
/// <summary>
///
/// </summary>
public string SFB01 { get; set; }
/// <summary>
///
/// </summary>
public string SFA27 { get; set; }
/// <summary>
///
/// </summary>
public decimal SFA05 { get; set; }
/// <summary>
///
/// </summary>
public decimal SFA06 { get; set; }
/// <summary>
///
/// </summary>
public string SFBMODU { get; set; }
/// <summary>
/// 李晓粉
/// </summary>
public string GEN02 { get; set; }
/// <summary>
///
/// </summary>
public DateTime SFBDATE { get; set; }
/// <summary>
///
/// </summary>
public string LEGAL { get; set; }
/// <summary>
///
/// </summary>
public int RNUM { get; set; }
}
public class GetToken
{
// private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
public static string GetTokenInfermation()
{
try
{
string resultStr = HTTPHelper.QueryPostparamsService("获取token", $@"https://weapp.lexy.cn/lexyapi/getToken?ent_id=qy202307311690791200&ent_secret=66cc4211eaa37f79f3608353e4e28c36");
var tokenString = JsonConvert.DeserializeObject<TokenModel>(resultStr);
if (string.IsNullOrEmpty(tokenString.token))
{
throw new Exception("获取token失败");
}
return tokenString.token;
}
catch (System.Net.WebException ex)
{
throw new Exception(ex.Message);
}
}
}
public class HTTPHelper
{
// private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
public static string HttpPost(string apiName, string url, string body)
{
try
{
//log.Debug(url + Environment.NewLine + body);
Encoding encoding = Encoding.UTF8;
System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
request.Method = "POST";
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
request.ContentType = "application/json; charset=utf-8";
// request.ContentType = "text/html, application/xhtml+xml";
byte[] buffer = encoding.GetBytes(body);
request.ContentLength = buffer.Length;
request.GetRequestStream().Write(buffer, 0, buffer.Length);
System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
using (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
{
return reader.ReadToEnd();
}
}
catch (System.Net.WebException ex)
{
// log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine + body);
throw new Exception(apiName + "调用失败," + ex.Message);
}
}
public static string HttpPost(string apiName, string url, string body, string headers = "")
{
try
{
//log.Debug(url + Environment.NewLine + body);
Encoding encoding = Encoding.UTF8;
System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
request.Method = "POST";
request.Headers.Add("token", headers);
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
request.ContentType = "application/json; charset=utf-8";
// request.ContentType = "text/html, application/xhtml+xml";
byte[] buffer = encoding.GetBytes(body);
request.ContentLength = buffer.Length;
request.GetRequestStream().Write(buffer, 0, buffer.Length);
System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
using (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
{
return reader.ReadToEnd();
}
}
catch (System.Net.WebException ex)
{
// log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine + body);
throw new Exception(apiName + "调用失败," + ex.Message);
}
}
public static string QueryPostparamsService(string apiName, string url)
{
string result = "";
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
req.Method = "Get";
//req.Headers.Add()
try
{
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
Stream stream = resp.GetResponseStream();
//获取内容
using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
{
result = reader.ReadToEnd();
}
}
catch (Exception ex)
{
// log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine);
throw new Exception(apiName + "调用失败," + ex.Message);
}
return result;
}
}
}
}