304 lines
15 KiB
C#
304 lines
15 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.Globalization;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using System.Xml.Linq;
|
||
|
||
namespace SYNC_SAP_MES
|
||
{
|
||
public class Sql_Biz_SAP
|
||
{
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
public static void LoadConfig()
|
||
{
|
||
var sql =
|
||
$" SELECT TOP 1 [AID],[AppServerHost],[SystemNumber],[User],[Password],[Client]" +
|
||
$",[Language],[PoolSize],[IdleTimeout],[Name],[SystemType],[Enable]" +
|
||
$",[IsDemo],[ReachSyncCount],[LineCode]" +
|
||
$" FROM [大柴_接口_SAP登录信息]" +
|
||
$" WHERE [Enable] = 'True'";
|
||
DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
var row = dt.Rows[0];
|
||
PP.AppServerHost = row["AppServerHost"].ToString();
|
||
PP.SystemNumber = row["SystemNumber"].ToString();
|
||
PP.User = row["User"].ToString();
|
||
PP.Password = row["Password"].ToString();
|
||
PP.Client = row["Client"].ToString();
|
||
PP.Language = row["Language"].ToString();
|
||
PP.PoolSize = row["PoolSize"].ToString();
|
||
PP.IdleTimeout = row["IdleTimeout"].ToString();
|
||
PP.Name = row["Name"].ToString();
|
||
|
||
PP.IsDemo = row["IsDemo"].ToString();
|
||
PP.ReachSyncCount = Convert.ToInt32(row["ReachSyncCount"]);
|
||
PP.LineCode = row["LineCode"].ToString();
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="date"></param>
|
||
/// <param name="dateTime"></param>
|
||
/// <returns></returns>
|
||
public static bool Select_SyncParam_SAP(out string date, out string dateTime)
|
||
{
|
||
bool success = false;
|
||
date = "";
|
||
dateTime = "";
|
||
|
||
//SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]
|
||
|
||
var sql = "SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]";
|
||
success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
|
||
success = err.Length > 0 ? false : true;
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
date = DateTime.Parse(dt.Rows[0]["日期"].ToString()).ToString("yyyy-MM-dd");
|
||
dateTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
|
||
}
|
||
else
|
||
{
|
||
var sql_Insert =
|
||
$" INSERT INTO [大柴_接口_SAP生产计划_标记]([日期],[修改时间])" +
|
||
$" VALUES(getdate(),getdate())";
|
||
DataLinkMesWork.SQLCommon.ExecuteSql(sql_Insert, PP.ConnectionString, out string err_Index);
|
||
Select_SyncParam_SAP(out date, out dateTime);
|
||
}
|
||
|
||
return success;
|
||
}
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="date"></param>
|
||
/// <returns></returns>
|
||
public static bool Update_SyncParam_SAP(string date)
|
||
{
|
||
//SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]
|
||
var sql =
|
||
$" UPDATE [大柴_接口_SAP生产计划_标记]" +
|
||
$" SET [日期] = '{date}',[更新时间] = getdate()";
|
||
return DataLinkMesWork.SQLCommon.ExecuteSql(sql, PP.ConnectionString, out string err);
|
||
}
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="startFlowNo"></param>
|
||
/// <param name="flowNoTime"></param>
|
||
/// <returns></returns>
|
||
public static bool Select_SyncParam_FlowNo(out int startFlowNo,out string flowNoTime)
|
||
{
|
||
bool success = false;
|
||
startFlowNo = 0;
|
||
flowNoTime = "";
|
||
|
||
//SELECT [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号]
|
||
|
||
var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] ORDER BY 更新时间 DESC";
|
||
success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
|
||
success = err.Length > 0 ? false : true;
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
startFlowNo = Convert.ToInt32(dt.Rows[0]["流水号"]);
|
||
flowNoTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
|
||
}
|
||
|
||
return success;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据上线日期获取到 这天的流水号，若这天没有则用【历史最大】流水号
|
||
/// </summary>
|
||
/// <param name="上线日期"></param>
|
||
/// <param name="startFlowNo"></param>
|
||
/// <param name="flowNoTime"></param>
|
||
/// <returns></returns>
|
||
public static bool Select_SyncParam_FlowNo(string useDate,out int startFlowNo, out string flowNoTime)
|
||
{
|
||
bool success = false;
|
||
startFlowNo = 0;
|
||
flowNoTime = "";
|
||
|
||
//SELECT [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号]
|
||
|
||
var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] WHERE 使用日期 = '{useDate}'";
|
||
//var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] ORDER BY 使用时间 ASC";
|
||
success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
|
||
success = err.Length > 0 ? false : true;
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
startFlowNo = Convert.ToInt32(dt.Rows[0]["流水号"]);
|
||
flowNoTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
|
||
}
|
||
else
|
||
{
|
||
// 获取前一天的流水号 用这个流水号往下排
|
||
DateTime date = DateTime.ParseExact(useDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
|
||
DateTime previousDay = date.AddDays(-1); // 获取前一天的日期
|
||
string formattedDate = previousDay.ToString("yyyy-MM-dd"); // 格式化为字符串形式
|
||
var sql2 = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] WHERE 使用日期 <= '{formattedDate}' ORDER BY [流水号] DESC";
|
||
success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql2, PP.ConnectionString, out DataTable dt2, out string err2);
|
||
success = err2.Length > 0 ? false : true;
|
||
if (dt2.Rows.Count > 0)
|
||
{
|
||
startFlowNo = Convert.ToInt32(dt2.Rows[0]["流水号"]);
|
||
flowNoTime = DateTime.Parse(dt2.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
|
||
}
|
||
}
|
||
|
||
return success;
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="FlowNo"></param>
|
||
/// <returns></returns>
|
||
public static bool GetFlowNo_CurrMax(out string FlowNo)
|
||
{
|
||
var success = false;
|
||
FlowNo = "-1";
|
||
var sql =
|
||
$" SELECT MAX([发动机流水号]) FROM [大柴_接口_SAP生产计划_执行计划]";
|
||
success = SqlOperation.ExecuteSql(sql, out DataTable dt, out string error);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
FlowNo = dt.Rows[0][0].ToString();
|
||
}
|
||
return success;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取今天明天两天的订单信息
|
||
/// </summary>
|
||
/// <param name="dateDay"></param>
|
||
/// <param name="table_Plan"></param>
|
||
/// <returns></returns>
|
||
public static bool Get_MesPlan(DateTime dateDay,out DataTable table_Plan)
|
||
{
|
||
|
||
TimeSpan oneDay = new TimeSpan(1, 0, 0, 0); // 创建一个表示一天的 TimeSpan 对象
|
||
//var sql = $"SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间]" +
|
||
// $" FROM [大柴_接口_SAP生产计划]" +
|
||
// $" WHERE [上线日期] >= '{dateDay.ToString("yyyy-MM-dd")}'" + //OR [上线日期] = '{tomorrow.ToString("yyyy-MM-dd")}'
|
||
// $" ORDER BY [上线日期] ,[上线序号]";
|
||
var sql = $"SELECT a.[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间],isnull(b.Exist_InMes,0) as Exist_InMes" +
|
||
$" FROM [大柴_接口_SAP生产计划] a left join " +
|
||
$" (select 订单号,count(*) as Exist_InMes from 大柴_接口_SAP生产计划_执行计划 where 生产日期>='{dateDay.ToString("yyyy-MM-dd")}' group by 订单号) b on a.订单号=b.订单号 " +
|
||
$" WHERE [上线日期] >= '{dateDay.ToString("yyyy-MM-dd")}'" + //OR [上线日期] = '{tomorrow.ToString("yyyy-MM-dd")}'
|
||
$" ORDER BY [上线日期] ,[上线序号]";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
|
||
}
|
||
public static bool Get_Mes_ExcutePlan(DateTime dateDay,string key_OrderNum,out DataTable table_Plan)
|
||
{
|
||
var sql = $@"
|
||
SELECT [订单类型],[订单号],[机型号],[订单数量],[生产日期],[上线序号],[上线总排序],[发动机流水号],[起始流水号],[终止流水号],[执行状态]
|
||
FROM[大柴_接口_SAP生产计划_执行计划] WHERE 生产日期 = '{dateDay.ToString("yyyy-MM-dd")}' AND 订单号 LIKE '%{key_OrderNum}%'";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 只获取今天的订单信息
|
||
/// </summary>
|
||
/// <param name="dateDay"></param>
|
||
/// <param name="table_Plan"></param>
|
||
/// <returns></returns>
|
||
public static bool Get_MesTodayPlan(DateTime dateDay, out DataTable table_Plan)
|
||
{
|
||
//SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间] FROM [大柴_接口_SAP生产计划]
|
||
var sql = $"SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间],isnull([流水号拆分已上报],0) as 流水号拆分已上报" +
|
||
$" FROM [大柴_接口_SAP生产计划]" +
|
||
$" WHERE [上线日期] = '{dateDay.ToString("yyyy-MM-dd")}'" +
|
||
$" ORDER BY [上线序号]";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
|
||
}
|
||
/// <summary>
|
||
/// 更新订单的流水号拆分上报状态：流水号拆分已上报=0，未上报，流水号拆分已上报=1 已上报
|
||
/// </summary>
|
||
/// <param name="OrderNum"></param>
|
||
/// <returns></returns>
|
||
public static bool Update_FlowNoSplit_UpLoadState(string OrderNum)
|
||
{
|
||
//SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间] FROM [大柴_接口_SAP生产计划]
|
||
var sql = $"Update [大柴_接口_SAP生产计划] set 流水号拆分已上报=1 WHERE [订单号] = '{OrderNum}'";
|
||
return SqlOperation.ExecuteSql(sql, out string error);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取拆分后数据 展示到网页
|
||
/// </summary>
|
||
/// <param name="dateDay">数据日期</param>
|
||
/// <param name="table_Plan"></param>
|
||
/// <returns></returns>
|
||
public static bool Get_MesSpiltPlan(DateTime dateDay, out DataTable table_Plan)
|
||
{
|
||
var sql = $"SELECT [AID],[订单类型],[生产日期],[班次],[上线序号],[上线总排序],[发动机流水号],[机型号],[托盘号],[订单号],[订单序号],[订单数量],[起始流水号],[终止流水号],[创建时间],[执行状态],[状态变更时间],[操作标记]" +
|
||
$" FROM [大柴_接口_SAP生产计划_执行计划]" +
|
||
$" WHERE [生产日期] = '{dateDay.ToString("yyyy-MM-dd")}'" +
|
||
$" ORDER BY 起始流水号";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
|
||
}
|
||
|
||
public static bool Get_MesPlan_ExecPlan(string orderNo, out DataTable table_ExecPlan)
|
||
{
|
||
var sql = $"SELECT [订单号],[订单类型],[机型号],[订单数量],[发动机流水号],[上线序号],[上线总排序],[执行状态],[生产日期]" +
|
||
$" FROM [大柴_接口_SAP生产计划_执行计划]" +
|
||
$" WHERE [订单号] = '{orderNo}'" +
|
||
$" ORDER BY [上线总排序]";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_ExecPlan, out string err);
|
||
}
|
||
public static DateTime Get_BOMChanged_DateFlag(string LineCode)
|
||
{
|
||
var sql = $"SELECT [编辑时间标识] FROM [大柴_接口_SAP订单BOM_编辑时间标识] WHERE [LineCode]='{LineCode}'";
|
||
DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
|
||
if(dt.Rows.Count>0)
|
||
{
|
||
return DateTime.Parse(dt.Rows[0]["编辑时间标识"].ToString());
|
||
}
|
||
else
|
||
{
|
||
return DateTime.Now.AddDays(-10);
|
||
}
|
||
}
|
||
public static void Update_TimeFlag_BomChanged(DateTime NewTimeFlag,string LineCode)
|
||
{
|
||
var name = "Event_接口_SAP订单BOM_时间标识";
|
||
|
||
var sqlParameters = new SqlParameter[] {
|
||
new SqlParameter("@NewTimeFlag", NewTimeFlag),
|
||
new SqlParameter("@LineCode", LineCode)
|
||
};
|
||
SqlOperation.ExecuteStoredProcedure(name, sqlParameters, out DataSet ds, out string error);
|
||
|
||
}
|
||
|
||
public static bool Get_MesBOM_ByOrderNo(string orderNo, out DataTable table_BOM)
|
||
{
|
||
var sql = $"SELECT [订单号],[工序号],[物料号],[物料描述],[总数量],[指定厂家代码],[指定厂家简称],[指定厂家名称],[工艺合件]," +
|
||
$"[虚拟件],[试装标识],[单台数量],[创建时间] FROM [大柴_接口_SAP订单BOM] where 订单号 = {orderNo}";
|
||
return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_BOM, out string err);
|
||
}
|
||
|
||
}
|
||
}
|