using MESCommon;
using MesDataSource;
using MESDTO;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
namespace MESBusiness.InterfaceImpl
{
class ptPushWorkReportDataToCostSys : BusinessService
{
IPTDataSource source = PTDataSourceFactory.CreateDataSourceService();
private static object locker = new object();
DataTable appendDt = new DataTable();//工作中心为空的数据，后续需要补推
string msg = "";
string tomailAd = "yangling@silanic.cn;zhouxun@silanic.cn";//执行异常邮件通知人
SubmitResult returnValue = new SubmitResult()
{
ResultCode = "1",
ResultMsg = "",
Count = 0
};
protected override SubmitResult Execute(Dictionary<string, string> paras)
{
lock (locker)
{
//1.按时间查询时间段内尚未推送的数据，组装数据
string serviceID = this.GetType().FullName;
//DateTime date = source.GetDataLastDateByJobKey(serviceID);
//string startTime = date.ToString("yyyy-MM-dd HH:mm:ss");
//string toTime = date.AddHours(2).ToString("yyyy-MM-dd HH:mm:ss");
string startTime = "2025-09-30 00:00:00";
string toTime = "2025-10-01 00:00:00";
for (int i = 0; i <= 30; i++)
{
startTime = DateTime.ParseExact(startTime, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss");
toTime = DateTime.ParseExact(startTime, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss");
updateAndInsert(startTime, toTime, "dxdsConnectionStr2111", "2111", false);
updateAndInsert(startTime, toTime, "dxdsconnectionstr2041", "2041", false);
updateAndInsert(startTime, toTime, "dxdsConnectionStr2041", "2041", true);
}
if (appendDt.Rows.Count > 0)
{
var htmlBody = EmailHelper.ConvertDatatableToHtml(appendDt);
EmailHelper.SendEmail("172.17.1.40", "fatest@silanic.cn", "fatest@silanic.cn", "abc#123", tomailAd,
"", "补推推送报成本系统报工数据", $@"{startTime}-{toTime}时间段需要补推<br/>{htmlBody}", "", true, ref msg);
}
if (returnValue.ResultCode.Equals("1"))
{
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd HH:mm:ss";
//发送成功更新参数
//source.UpdateDataLastDateByJobKey(serviceID, Convert.ToDateTime(toTime, dtFormat));
}
return returnValue;
}
}
private void updateAndInsert(string startTime, string toTime, string conStr, string factroy, bool is_WY)
{
var batchSize = 1000;
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd HH:mm:ss";
DataTable dt = new DataTable();
DataTable patchTable = new DataTable();//工作中心为空，但能查出工作中心的数据
if (is_WY)
{
var wySql = $@"
select DISTINCT unid,post_date,post_time,procedure_code,plant_code,work_plant
,rTRIM(sosn) as sosn,rTRIM(prd_mat_code) as prd_mat_code,rTRIM(prodosn) as prodosn
,substring(prodosn,1,4) as prodo_type,rTRIM(lot_id) as lot_id,rTRIM(lot_type) as lot_type
,lot_qty,track_sn,track_sn_qty,track_sn_type,step_id,rTRIM(step_desc) as step_desc
,rTRIM(step_text) as step_text,process_flag,work_center,rTRIM(equipment_id) as equipment_id
,rTRIM(recipe_code) as recipe_code,act_reason1_qty,'MIN' as act_reason1_uom,act_reason2_qty
,'KWH' as act_reason2_uom,act_reason3_qty,'L' as act_reason3_uom,confirm_qty,scrap_qty
,rework_qty,scrap_die,rework_die,reason,operater,bas_unit_code,reserved1,reserved2
,reserved3
from WorkReportData
where
TXN_DATE >= convert(datetime,'{startTime}',120)
and TXN_DATE < convert(datetime,'{toTime}',120)
and prodosn is not null
and IS_PUSHED !='1'
and prd_mat_code is not null
and (work_center IS not NULL and work_center!='')";
dt = source.QueryFromWyMES(wySql);
}
else
{
var odsSql = $@"SELECT UNID,POST_DATE,POST_TIME,PROCEDURE_CODE,PLANT_CODE,WORK_PLANT,SOSN,PRD_MAT_CODE,PRODOSN
,substr(PRODOSN,0,4) as PRODO_TYPE,LOT_ID,LOT_TYPE,LOT_QTY,TRACK_SN,TRACK_SN_QTY,TRACK_SN_TYPE
,STEP_ID,STEP_DESC,STEP_TEXT,PROCESS_FLAG,WORK_CENTER,EQUIPMENT_ID,RECIPE_CODE,ACT_REASON1_QTY
,ACT_REASON1_UOM,ACT_REASON2_QTY,ACT_REASON2_UOM,ACT_REASON3_QTY,ACT_REASON3_UOM,CONFIRM_QTY
,SCRAP_QTY,REWORK_QTY,CONFIRM_DIE,SCRAP_DIE,REWORK_DIE,REASON,OPERATER,BAS_UNIT_CODE,WAFER_NUMBER
,RESERVED1,RESERVED2,RESERVED3,GENERAL_NOTES
FROM CAMSTARADMIN.PT_WORK_REPORT_DATA pwrd
where PRD_MAT_CODE is not null
and PRODOSN is not null
and nvl(IS_PUSHED,0)!=1
AND PLANT_CODE = '{factroy}'
and TXN_DATE >= to_date('{startTime}','yyyy-MM-dd hh24:mi:ss')
and TXN_DATE < to_date('{toTime}','yyyy-MM-dd hh24:mi:ss')
and WORK_CENTER is not null";
dt = source.QueryFromCamstar(odsSql);
var odsSqlpatch = $@"SELECT UNID,POST_DATE,POST_TIME,PROCEDURE_CODE,PLANT_CODE,WORK_PLANT,SOSN,PRD_MAT_CODE,PRODOSN
,substr(PRODOSN,0,4) as PRODO_TYPE,LOT_ID,LOT_TYPE,LOT_QTY,TRACK_SN,TRACK_SN_QTY,TRACK_SN_TYPE
,STEP_ID,STEP_DESC,STEP_TEXT,PROCESS_FLAG,WORK_CENTER,EQUIPMENT_ID,RECIPE_CODE,ACT_REASON1_QTY
,ACT_REASON1_UOM,ACT_REASON2_QTY,ACT_REASON2_UOM,ACT_REASON3_QTY,ACT_REASON3_UOM,CONFIRM_QTY
,SCRAP_QTY,REWORK_QTY,CONFIRM_DIE,SCRAP_DIE,REWORK_DIE,REASON,OPERATER,BAS_UNIT_CODE,WAFER_NUMBER
,RESERVED1,RESERVED2,RESERVED3,GENERAL_NOTES FROM CAMSTARADMIN.PT_WORK_REPORT_DATA pwrd
where PRD_MAT_CODE is not null
and PRODOSN is not null
and nvl(IS_PUSHED,0)!=1
AND PLANT_CODE = '{factroy}'
and TXN_DATE >= to_date('{startTime}','yyyy-MM-dd hh24:mi:ss')
and TXN_DATE < to_date('{toTime}','yyyy-MM-dd hh24:mi:ss')
and WORK_CENTER is null ";
patchTable = source.QueryFromCamstar(odsSqlpatch);
}
#region 处理工作中心为空的数据
if (patchTable != null && patchTable.Rows.Count > 0)
{
for (int i = patchTable.Rows.Count - 1; i >= 0; i--)
{
DataRow row = patchTable.Rows[i];
if (row.IsNull("WORK_CENTER"))
{
string workCenter = getWorkCenter(row);
if (string.IsNullOrEmpty(workCenter))
{
appendDt.ImportRow(row);
patchTable.Rows.Remove(row);
}
else
{ row["WORK_CENTER"] = workCenter; }
}
}
patchTable.AcceptChanges();
}
#endregion
if (patchTable != null && patchTable.Rows.Count > 0) dt.Merge(patchTable);
if (dt == null || dt.Rows.Count == 0) return;
#region 推送数据到成本系统,一次性全部插入。
using (var conn = new MySqlConnection(ConfigurationManager.AppSettings[conStr]))//2111
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
int totalRows = dt.Rows.Count;
int batchCount = (int)Math.Ceiling((double)totalRows / batchSize);
for (int batchIndex = 0; batchIndex < batchCount; batchIndex++)
{
StringBuilder sqlBuilder = new StringBuilder();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = transaction;
// 构建基础SQL
if (factroy == "2041")
{
if (is_WY)
{
sqlBuilder.AppendLine($@"INSERT INTO co_etl_work_report_2041
( unid, post_date, post_time, procedure_code, plant_code, work_plant, sosn, prd_mat_code, prodosn, prodo_type, lot_id, lot_type, lot_qty
, track_sn, track_sn_qty, track_sn_type, step_id, step_desc, step_text, process_flag, work_center, equipment_id, recipe_code, act_reason1_qty
, act_reason1_uom, act_reason2_qty, act_reason2_uom, act_reason3_qty, act_reason3_uom, confirm_qty, scrap_qty, rework_qty, scrap_die
, rework_die, reason, operater, bas_unit_code, reserved1, reserved2, reserved3)
VALUES ");
}
else
{
sqlBuilder.AppendLine($@"INSERT INTO co_etl_work_report_2041
( unid, post_date, post_time, procedure_code, plant_code, work_plant, sosn, prd_mat_code, prodosn, prodo_type, lot_id, lot_type, lot_qty
, track_sn, track_sn_qty, track_sn_type, step_id, step_desc, step_text, process_flag, work_center, equipment_id, recipe_code, act_reason1_qty
, act_reason1_uom, act_reason2_qty, act_reason2_uom, act_reason3_qty, act_reason3_uom, confirm_qty, scrap_qty, rework_qty, confirm_die, scrap_die
, rework_die, reason, operater, bas_unit_code, wafer_number, reserved1, reserved2, reserved3, general_notes)
VALUES ");
}
}
else
{
sqlBuilder.AppendLine(@"INSERT INTO co_etl_work_report_2111
( unid, post_date, post_time, procedure_code, plant_code, work_plant, sosn, prd_mat_code, prodosn, prodo_type, lot_id, lot_type, lot_qty
, track_sn, track_sn_qty, track_sn_type, step_id, step_desc, step_text, process_flag, work_center, equipment_id, recipe_code, act_reason1_qty
, act_reason1_uom, act_reason2_qty, act_reason2_uom, act_reason3_qty, act_reason3_uom, confirm_qty, scrap_qty, rework_qty, confirm_die, scrap_die
, rework_die, reason, operater, bas_unit_code, wafer_number, reserved1, reserved2, reserved3, general_notes)
VALUES ");
}
int startRow = batchIndex * batchSize;
int endRow = Math.Min(startRow + batchSize, totalRows);
// 动态生成参数化VALUES
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
DataRow row = dt.Rows[rowIndex];
sqlBuilder.Append("(");
// 为当前行所有列生成参数
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
string paramName = $"@p_{rowIndex}_{colIndex}";
sqlBuilder.Append(paramName);
if (colIndex < dt.Columns.Count - 1) sqlBuilder.Append(", ");
// 添加参数值
object value = row[colIndex];
cmd.Parameters.AddWithValue(
paramName,
value == DBNull.Value ? null : value
);
}
sqlBuilder.Append("),");
}
// 移除最后一个逗号
sqlBuilder.Length--;
cmd.CommandText = sqlBuilder.ToString();
// 执行批量插入
var affectedRows = cmd.ExecuteNonQuery();
//更新MES
int resultQty = 0;
var unidList = dt.AsEnumerable().Select(row => row["UNID"]).ToList();
for (int i = 0; i < unidList.Count; i += 1000)
{
var batch = unidList.Skip(i).Take(1000).ToList();
if (is_WY)
{
var updateSql = $@"update WorkReportData set IS_PUSHED = 1 where UNID IN('{string.Join("','", batch)}') and TXN_DATE >= convert(datetime,'{startTime}',120)
and TXN_DATE < convert(datetime,'{toTime}',120)";
resultQty = source.UpdateToWY(updateSql);//正式环境需要插入外延数据库
}
else
{
var updateSql = $@"update PT_WORK_REPORT_DATA set IS_PUSHED = 1 where UNID IN('{string.Join("','", batch)}') and TXN_DATE >= to_date('{startTime}','yyyy-MM-dd hh24:mi:ss')
and TXN_DATE < to_date('{toTime}','yyyy-MM-dd hh24:mi:ss')";
resultQty = source.UpdateToCamstar(updateSql);
}
if (resultQty < 0) break;
resultQty += resultQty;
}
if (resultQty > 0)
{
transaction.Commit();
returnValue.Count += dt.Rows.Count;
}
}
}
}
catch (Exception ex)
{
// 回滚事务
transaction.Rollback();
returnValue.ResultMsg = ex.Message;
returnValue.ResultCode = "0";
returnValue.DontSaveLog = false;
//发送失败推送邮件
string msg = "";
EmailHelper.SendEmail("172.17.1.40", "fatest@silanic.cn", "fatest@silanic.cn", "abc#123", tomailAd,
"", "推送报成本系统报工数据", $@"{startTime}-{toTime}时间段推送异常{ex.Message}", "", true, ref msg);
return;
}
}
conn.Close();
}
#endregion
}
private string getWorkCenter(DataRow row)
{
var workCenter = "";
var factoryname = row["PLANT_CODE"];
var resourcename = row["EQUIPMENT_ID"];
var spec = row["STEP_TEXT"];
if (DBNull.Value.Equals(factoryname) || factoryname.Equals("")) return workCenter;
//没有设备则取工序绑定的设备组中随机设备的工作中心
if (DBNull.Value.Equals(resourcename) || resourcename.Equals(""))
{
if (!DBNull.Value.Equals(spec) && !spec.Equals(""))
{
var sqlEqp = $@"select DISTINCT r.resourcename,w1.WORKCENTERNAME AS slworkcenter,w2.WORKCENTERNAME AS ptworkcenter
from a_equipmentmatrix ap
inner join spec sp on ap.specid = sp.specid
inner join specbase spb on sp.specbaseid = spb.specbaseid
inner join resourcegroup rp on ap.equipmentgroupid = rp.resourcegroupid
INNER JOIN ResourceGroupEntries RGE ON rp.ResourceGroupId = RGE.ResourceGroupId
INNER JOIN resourcedef r on r.ResourceId = rge.EntriesId
LEFT JOIN WORKCENTER w1 ON w1.WORKCENTERID = r.PTWORKCENTERID
LEFT JOIN WORKCENTER w2 ON W2.WORKCENTERID = r.PTWORKCENTER2ID
WHERE specname ='{spec}' AND w1.WORKCENTERNAME IS NOT NULL AND w2.WORKCENTERNAME IS NOT null ";
var dtEqp = source.QueryFromCamstar(sqlEqp);
if (dtEqp != null & dtEqp.Rows.Count > 0)
{
workCenter = factoryname == "2041" ? dtEqp.Rows[0]["SLWORKCENTER"]?.ToString() : dtEqp.Rows[0]["PTWORKCENTER"]?.ToString();
}
else
{
//测试中转库/模块测试中转库/成品包装/包装入库工序如果没绑定设备组，则设置默认工作中心
if (spec == "测试中转库")
{
workCenter = factoryname == "2041" ? "20411111" : "21110802";
}
if (spec == "模块测试中转库")
{
workCenter = factoryname == "2041" ? "20411111" : "";
}
if (spec == "成品包装")
{
workCenter = factoryname == "2041" ? "20411301" : "21111001";
}
if (spec == "包装入库")
{
workCenter = factoryname == "2041" ? "20411301" : "21111002";
}
}
}
}
//有设备号则获取设备的工作中心
else
{
var sql = $@"select CASE WHEN {factoryname} ='2041' THEN w1.WORKCENTERNAME ELSE W2.WORKCENTERNAME end AS workcenter
FROM camstaradmin.RESOURCEDEF r
LEFT JOIN camstaradmin.WORKCENTER w1 ON w1.WORKCENTERID = r.PTWORKCENTERID
LEFT JOIN camstaradmin.WORKCENTER w2 ON W2.WORKCENTERID = r.PTWORKCENTER2ID
WHERE r.RESOURCENAME='{resourcename}' ";
DataTable dt = source.QueryFromODS(sql);
if (dt != null && dt.Rows.Count > 0)
{
workCenter = dt.Rows[0]["workcenter"]?.ToString();
}
}
return workCenter;
}
}
}
这是我的代码 请分析错误原因
扫一扫
554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
