// /// <summary>
// /// 根据父批号获取拆分后批号。
// /// </summary>
// /// <param name="db">数据库对象。</param>
// /// <param name="strParentLotNumber">父批号。</param>
// /// <param name="index"></param>
// /// <returns>拆分后的子批号。</returns>
// private static string GetChildLotNumber(Database db, DbTransaction dbTrans, string strParentLotNumber, int index)
// {
// string strChildLotNumber = string.Empty;
// string strMaxNum = string.Empty;
// string sql = string.Empty;
// string strPrepositive = strParentLotNumber;
// sql = string.Format(@"SELECT ISNULL(MAX(SUBSTRING(A.LOT_NUMBER, LEN('{0}')+2,2)),0) AS MAX_NUM
// FROM POR_LOT A
// WHERE A.LOT_NUMBER LIKE '{0}%'",
// strPrepositive.PreventSQLInjection());
// object objMaxNum = db.ExecuteScalar(dbTrans, CommandType.Text, sql);
// int maxNum = 0;
// if (objMaxNum == null || objMaxNum == DBNull.Value)
// {
// maxNum = Convert.ToInt32(maxNum);
// }
// maxNum = maxNum + 1;
// strMaxNum = maxNum.ToString().PadLeft(2, '0');
// strChildLotNumber = strPrepositive + "-" + strMaxNum;
// return strChildLotNumber;
// }
/// <summary>
/// 记录批次自动进站或自动出站错误消息。
/// </summary>
/// <param name="title">消息标题。</param>
/// <param name="message">消息内容。</param>
/// <param name="toUser">消息到达用户。</param>
/// <param name="group">消息到达角色。</param>
/// <param name="user">发送消息用户。</param>
/// <param name="timeZone">时区。</param>
/// <param name="objectKey">消息类型的对象主键。</param>
/// <param name="objectType">消息类型</param>
internal static void RecordErrorMessage(Database db,
string title, string message, string toUser, string group,
string user, string timeZone, string objectKey, string objectType)
{
WIP_MESSAGE_FIELDS wef = new WIP_MESSAGE_FIELDS();
string rowKey = string.Empty;
string sql = string.Empty;
Hashtable hsTable = new Hashtable();
rowKey = UtilHelper.GenerateNewKey(0);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_ROW_KEY, rowKey);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_TITLE, title);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_CONTEXT, message);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_CRITICAL_LEVEL, "0");
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_PUBLIC_LEVEL, "0");
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_TO_USER, toUser);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_TO_GROUP, group);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_STATUS, "0");
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_CREATOR, user);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_CREATE_TIME, null);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_CREATE_TIMEZONE, timeZone);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_OBJECTKEY, objectKey);
hsTable.Add(WIP_MESSAGE_FIELDS.FIELD_OBJECTTYPE, objectType);
sql = DatabaseTable.BuildInsertSqlStatement(wef, hsTable, null);
db.ExecuteNonQuery(CommandType.Text, sql);
}
/// <summary>
/// 新增生产批次预设暂停的记录。
/// </summary>
/// <param name="db">数据库操作对象。</param>
/// <param name="dbtran">数据库操作事务对象。</param>
/// <param name="lotKey">生产批次主键。</param>
/// <param name="operationName">暂停批次的工序。</param>
private static void InsertFutureHold(Database db, DbTransaction dbtran, string lotKey, string operationName)
{
//根据批次主键和工序名称获取暂停的工艺流程组主键、工艺流程主键、工步主键以及批次信息。
string sql = string.Format(@"SELECT a.ROUTE_ENTERPRISE_VER_KEY,a.ROUTE_ROUTE_VER_KEY,a.ROUTE_STEP_KEY,
b.LOT_NUMBER,b.CUR_ROUTE_VER_KEY,b.CUR_STEP_VER_KEY,b.WORK_ORDER_NO
FROM V_PROCESS_PLAN a
INNER JOIN POR_LOT b ON a.ROUTE_ENTERPRISE_VER_KEY=b.ROUTE_ENTERPRISE_VER_KEY
WHERE a.ROUTE_STEP_NAME='{0}'
AND b.LOT_KEY='{1}'",
operationName.PreventSQLInjection(),
lotKey.PreventSQLInjection());
DataSet dsInfo = db.ExecuteDataSet(dbtran, CommandType.Text, sql);
if (null != dsInfo &&
dsInfo.Tables.Count > 0 &&
dsInfo.Tables[0].Rows.Count > 0)
{
DataRow drInfo = dsInfo.Tables[0].Rows[0];
//检查生产批次对应的工步是否设置了预设暂停批次的自定义属性。
sql = string.Format(@"SELECT A.ATTRIBUTE_VALUE
FROM POR_ROUTE_STEP_ATTR A,POR_LOT B
WHERE A.ROUTE_STEP_KEY =B.CUR_STEP_VER_KEY
AND B.LOT_KEY='{0}' AND A.ATTRIBUTE_NAME='{1}'",
lotKey.PreventSQLInjection(),
ROUTE_STEP_ATTRIBUTE.HoldPassword);
string holdPassword = Convert.ToString(db.ExecuteScalar(dbtran, CommandType.Text, sql));
Hashtable htFuturHold = new Hashtable();
WIP_FUTUREHOLD_FIELDS futureHoldField = new WIP_FUTUREHOLD_FIELDS();
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_ACTION_NAME, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TRACKIN);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_CREATE_TIME, null);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_CREATOR, "system");
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_DELETE_FLAG, 0);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_EDIT_TIME, null);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_EDITOR, "system");
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_ENTERPRISE_KEY, drInfo[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_HOLD_LEVEL, 1);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_HOLD_PASSWORD, holdPassword);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_LOT_KEY, lotKey);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_LOT_NUMBER, drInfo[POR_LOT_FIELDS.FIELD_LOT_NUMBER]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_OPERATION_NAME, operationName);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE, string.Empty);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE_KEY, string.Empty);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE_CATEGORY_KEY, string.Empty);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE_CATEGORY_NAME, string.Empty);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_REMARK, "超出控制规格线。");
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_ROUTE_KEY, drInfo[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_ROW_KEY, UtilHelper.GenerateNewKey(0));
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_SET_ENTERPRISE_KEY, drInfo[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_SET_ROUTE_KEY, drInfo[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_ROUTE_VER_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_SET_STEP_KEY, drInfo[POR_ROUTE_STEP_FIELDS.FIELD_ROUTE_STEP_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_STATUS, 0);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_STEP_KEY, drInfo[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
htFuturHold.Add(WIP_FUTUREHOLD_FIELDS.FIELDS_WORKORDER_NUMBER, drInfo[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO]);
sql = DatabaseTable.BuildInsertSqlStatement(futureHoldField, htFuturHold, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
}
}
/// <summary>
/// 检查是否要HOLD批次。如果需要HOLD批次，则HOLD批次。
/// </summary>
/// <param name="dbtran">数据库事务对象。。</param>
/// <param name="lotKey">批次主键。</param>
/// <param name="stepKey">工步主键。</param>
/// <param name="shiftName">班别名称。</param>
/// <param name="shiftKey">班别主键。</param>
/// <param name="editor">编辑人。</param>
/// <param name="oprComputer">操作计算机名称。</param>
/// <param name="editTimeZone">编辑时间时区。</param>
/// <param name="action">动作名称。<see cref="COMMON_FIELDS.FIELD_ACTIVITY_TRACKIN"/>或者<see cref="COMMON_FIELDS.FIELD_ACTIVITY_TRACKOUT"/></param>
/// <returns>触发预设暂停的个数。0：没有批次被HOLD。>0：该批次被HOLD。</returns>
private int CheckAndUpdateFutureHold(DbTransaction dbtran, string lotKey, string stepKey,
string shiftName, string shiftKey,
string editor, string oprComputer, string editTimeZone, string action)
{
string sql = @"SELECT * FROM WIP_FUTUREHOLD
WHERE LOT_KEY=@lotKey AND STEP_KEY=@stepKey AND STATUS=1 AND ACTION_NAME=@action
ORDER BY EDIT_TIME DESC";
DbCommand dbCmd = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCmd, "lotKey", DbType.String, lotKey);
db.AddInParameter(dbCmd, "stepKey", DbType.String, stepKey);
db.AddInParameter(dbCmd, "action", DbType.String, action);
DataSet dsFutureHold = db.ExecuteDataSet(dbCmd, dbtran);
int nRet = 0;
if (dsFutureHold != null && dsFutureHold.Tables.Count > 0 && dsFutureHold.Tables[0].Rows.Count > 0)
{
DataTable dtFutureHold = dsFutureHold.Tables[0];
DataRow drFutureHold = dtFutureHold.Rows[0];
string transactionKey = UtilHelper.GenerateNewKey(0);
string rcCodeKey = Convert.ToString(drFutureHold[WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE_KEY]);
string rcCodeName = Convert.ToString(drFutureHold[WIP_FUTUREHOLD_FIELDS.FIELDS_REASON_CODE]);
string comment = Convert.ToString(drFutureHold[WIP_FUTUREHOLD_FIELDS.FIELDS_REMARK]);
string opUser = Convert.ToString(drFutureHold[WIP_FUTUREHOLD_FIELDS.FIELDS_EDITOR]);
Hashtable htParams = new Hashtable();
htParams[POR_LOT_FIELDS.FIELD_LOT_KEY] = lotKey;
htParams[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
htParams[WIP_HOLD_RELEASE_FIELDS.FIELD_REASON_CODE_KEY] = rcCodeKey;
htParams[WIP_HOLD_RELEASE_FIELDS.FIELD_REASON_CODE_NAME] = rcCodeName;
htParams[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT] = comment;
htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME] = shiftName;
htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY] = shiftKey;
htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER] = oprComputer;
htParams[WIP_TRANSACTION_FIELDS.FIELD_OPERATOR] = opUser;
htParams[WIP_TRANSACTION_FIELDS.FIELD_EDITOR] = opUser;
htParams[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY] = editTimeZone;
DataTable dtParams = SolarViewer.Hemera.Share.Common.CommonUtils.ParseToDataTable(htParams);
dtParams.TableName = TRANS_TABLES.TABLE_MAIN_DATA;
DataSet dsParams = new DataSet();
dsParams.Tables.Add(dtParams);
WipManagement.HoldLot(db, dbtran, dsParams);
sql = @"UPDATE WIP_FUTUREHOLD
SET STATUS=0,EDITOR=@editor,EDIT_TIME= GETDATE()
WHERE LOT_KEY=@lotKey AND STEP_KEY=@stepKey AND STATUS=1 AND ACTION_NAME=@action";
dbCmd = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCmd, "lotKey", DbType.String, lotKey);
db.AddInParameter(dbCmd, "stepKey", DbType.String, stepKey);
db.AddInParameter(dbCmd, "action", DbType.String, action);
db.AddInParameter(dbCmd, "editor", DbType.String, editor);
nRet = db.ExecuteNonQuery(dbCmd, dbtran);
}
return(nRet);
}
/// <summary>
/// 批次暂停操作。
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_HOLD"/>。
/// </remarks>
/// <param name="dsParams">包含批次暂停信息的数据集对象。</param>
/// <param name="dbTran">数据库操作事务对象。</param>
public void LotHold(DbTransaction dbTran, DataSet dsParams)
{
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME) ||
!dsParams.Tables.Contains(WIP_HOLD_RELEASE_FIELDS.DATABASE_TABLE_NAME) ||
dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME].Rows.Count == 0 || //暂停批次操作记录不能为空记录。
dsParams.Tables[WIP_HOLD_RELEASE_FIELDS.DATABASE_TABLE_NAME].Rows.Count != 1 //暂停原因只能有一条记录
)
{
throw new Exception("传入参数不正确，请检查。");
}
IDbConnection dbConn = dbTran.Connection;
IDbCommand dbCmd = dbConn.CreateCommand();
dbCmd.Transaction = dbTran;
dbCmd.CommandType = CommandType.Text;
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
DataTable dtHold = dsParams.Tables[WIP_HOLD_RELEASE_FIELDS.DATABASE_TABLE_NAME]; //存放批次暂停原因明细数据
//插入操作记录
foreach (DataRow drTransaction in dtTransaction.Rows)
{
string lotKey = Convert.ToString(drTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]); //批次主键
string activity = Convert.ToString(drTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
string editor = Convert.ToString(drTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]); //编辑人
string timeZone = Convert.ToString(drTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]); //编辑时区
//操作动作必须是 HOLD
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_HOLD)
{
throw new Exception("传入参数的操作动作不正确，请检查。");
}
string transKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transKey, lotKey);
//向WIP_TRANSACTION表插入批次调整的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
drTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transKey;
drTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME] = DBNull.Value;
string sql = DatabaseTable.BuildInsertSqlStatement(wipFields, drTransaction, null);
dbCmd.CommandText = sql;
dbCmd.ExecuteNonQuery();
//向WIP_HOLD_RELEASE表插入批次暂停的明细记录。
WIP_HOLD_RELEASE_FIELDS holdFields = new WIP_HOLD_RELEASE_FIELDS();
dtHold.Rows[0][WIP_HOLD_RELEASE_FIELDS.FIELD_TRANSACTION_KEY] = transKey;
sql = DatabaseTable.BuildInsertSqlStatement(holdFields, dtHold.Rows[0], null);
dbCmd.CommandText = sql;
dbCmd.ExecuteNonQuery();
//更新批次数据
StringBuilder sbUpdateSql = new StringBuilder();
sbUpdateSql.AppendFormat(@"UPDATE POR_LOT
SET HOLD_FLAG=1,EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}'
WHERE LOT_KEY='{0}'",
lotKey.PreventSQLInjection(),
editor.PreventSQLInjection(),
timeZone.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sbUpdateSql.ToString());
}
}
/// <summary>
///（电池片/组件）不良操作
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CELLDEFECT"/>和<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_DEFECT"/>。
/// </remarks>
/// <param name="dsParams">包含不良信息的数据集对象。</param>
/// <param name="dbTran">数据库操作事务对象。</param>
private void LotDefect(DataSet dsParams, DbTransaction dbTran)
{
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME) ||
!dsParams.Tables.Contains(WIP_DEFECT_FIELDS.DATABASE_TABLE_NAME) ||
dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME].Rows.Count == 0 //批次操作记录不能为空记录。
)
{
throw new Exception("传入参数不正确，请检查。");
}
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = CommonUtils.ConvertToHashtable(dtTransaction);
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]); //批次主键
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
//操作动作必须是 DEFECT 或 CELLDEFECT
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CELLDEFECT &&
activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_DEFECT)
{
throw new Exception("传入参数的不良操作动作不正确，请检查。");
}
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
//向WIP_TRANSACTION表插入批次不良的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
string sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//如果数据集中包含名称WIP_DEFECT_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dsParams.Tables.Contains(WIP_DEFECT_FIELDS.DATABASE_TABLE_NAME))
{
WIP_DEFECT_FIELDS defectFields = new WIP_DEFECT_FIELDS();
DataTable dtDefect = dsParams.Tables[WIP_DEFECT_FIELDS.DATABASE_TABLE_NAME]; //存放不良明细数据
//遍历批次的不良数据。
for (int i = 0; i < dtDefect.Rows.Count; i++)
{
DataRow drDefect = dtDefect.Rows[i];
Hashtable htDefect = CommonUtils.ConvertRowToHashtable(drDefect);
if (!htDefect.ContainsKey(WIP_DEFECT_FIELDS.FIELD_TRANSACTION_KEY))
{
htDefect.Add(WIP_DEFECT_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htDefect[WIP_DEFECT_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//插入一笔批次不良数据。
sql = DatabaseTable.BuildInsertSqlStatement(defectFields, htDefect, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
}
}
/// <summary>
/// 批次终止操作
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TERMINALLOT"/>。
/// </remarks>
/// <param name="dsParams">包含批次终止信息的数据集对象。</param>
/// <param name="dbTran">数据库操作事务对象。</param>
private void LotTerminal(DataSet dsParams, DbTransaction dbTran)
{
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME) ||
dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME].Rows.Count == 0 //批次操作记录不能为空记录。
)
{
throw new Exception("传入参数不正确，请检查。");
}
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = CommonUtils.ConvertToHashtable(dtTransaction);
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
string stepKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY]);
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]);
string editTimeZone = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY]);
string editor = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]);
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
//操作动作必须是 TERMINALLOT
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TERMINALLOT)
{
throw new Exception("传入参数的不良操作动作不正确，请检查。");
}
//向WIP_TRANSACTION表插入批次不良的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
string sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//更新批次终止状态。
sql = string.Format(@"UPDATE POR_LOT
SET DELETED_TERM_FLAG=1,EDITOR='{0}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{1}'
WHERE LOT_KEY='{0}'",
lotKey.PreventSQLInjection(),
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//更新批次设备信息表
sql = string.Format(@"UPDATE EMS_LOT_EQUIPMENT
SET END_TIMESTAMP = GETDATE(),USER_KEY='{2}'
WHERE LOT_KEY = '{0}' AND STEP_KEY = '{1}' AND END_TIMESTAMP IS NULL",
lotKey.PreventSQLInjection(),
stepKey.PreventSQLInjection(),
editor.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
public DataSet MaterialDateInsert(DataSet dataSet)
{
DataSet retDS = new DataSet();
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
string sql = string.Empty;
try
{
string key = UtilHelper.GenerateNewKey(0);
if (null != dataSet)
{
string sql01 = string.Format("SELECT MAX(MATERIAL_VERSION) FROM POR_MATERIAL WHERE MATERIAL_CODE = '{0}'",
dataSet.Tables[0].Rows[0]["MATERIAL_CODE"].ToString());
DataSet ds01 = db.ExecuteDataSet(CommandType.Text, sql01);
int materialVersion = 1;
if (ds01.Tables[0].Rows.Count > 0)
{
materialVersion = Convert.ToInt32(ds01.Tables[0].Rows[0][0].ToString()) + 1;
}
sql = @" INSERT INTO POR_MATERIAL
(MATERIAL_KEY,MATERIAL_NAME ,MATERIAL_CODE ,
MATERIAL_SPEC, BARCODE,CREATOR,CREATE_TIME,CREATE_TIMEZONE,MATERIAL_VERSION)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}',GETDATE(),'CN-ZH','{6}')";
string sql2 = string.Format(sql,
key,
dataSet.Tables[0].Rows[0]["MATERIAL_NAME"].ToString(),
dataSet.Tables[0].Rows[0]["MATERIAL_CODE"].ToString(),
dataSet.Tables[0].Rows[0]["MATERIAL_SPEC"].ToString(),
dataSet.Tables[0].Rows[0]["BARCODE"].ToString(),
dataSet.Tables[0].Rows[0]["CREATOR"].ToString(),
materialVersion);
db.ExecuteNonQuery(CommandType.Text, sql2);
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, "${res:Global.ServerMessage.M0004}");
}
}
catch (Exception ex)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, ex.Message);
LogService.LogError("MaterialDateInsert Error: " + ex.Message);
}
return(retDS);
}
public DataSet InsertIntoGWJ(DataTable dtData, string lotNumber)
{
DataSet retDS = new DataSet();
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
try
{
///sql定义start--------------------------------------------------------------------------------------------------------------
string sqlInsert = @" INSERT INTO POR_GWJ_BULIANG(GWJ_DETAIL_KEY,LOT_NUMBER,GWJ_POSITION,GWJ_DETAIL,GWJ_VERSION,EDITOR,CREATOR,GWJSTATUS)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',1) ";
string sqlUpdateStatus = @"UPDATE dbo.POR_GWJ_BULIANG SET GWJSTATUS= 0 WHERE LOT_NUMBER = '{0}'";
///sql执行start-------------------------------------------------------------------------------------------------------------------
string strUpdate = string.Format(sqlUpdateStatus, lotNumber);
db.ExecuteNonQuery(dbTrans, CommandType.Text, strUpdate);
foreach (DataRow dr in dtData.Rows)
{
string keyPo = UtilHelper.GenerateNewKey(0);
string lotnumber = dr["LOT_NUMBER"].ToString().Trim().PreventSQLInjection();
string position = dr["GWJ_POSITION"].ToString().Trim().PreventSQLInjection();
string detail = dr["GWJ_DETAIL"].ToString().Trim().PreventSQLInjection();
string editor = dr["EDITOR"].ToString().Trim().PreventSQLInjection();
string creater = dr["CREATOR"].ToString().Trim().PreventSQLInjection();
int version = int.Parse(dr["GWJ_VERSION"].ToString().Trim().PreventSQLInjection());
string sqlDetail = string.Format(sqlInsert,
keyPo, //主键
lotnumber,
position,
detail,
version,
editor,
creater
);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlDetail);
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
dbTrans.Commit();
}
catch (Exception ex)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, ex.Message);
LogService.LogError("InsertIntoGWJ Error: " + ex.Message);
}
return(retDS);
}
/// <summary>
/// 保存采集到的明细数据。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库事务对象。</param>
/// <param name="dataset">包含到采集到的明细数据的数据集。</param>
internal static void SaveEDCCollectionData(Database db, DbTransaction dbtran, DataSet dsParams)
{
string editor = Convert.ToString(dsParams.ExtendedProperties[EDC_MAIN_INS_FIELDS.FIELD_EDITOR]);
EDC_COLLECTION_DATA_FIELDS edcCollectionData = new EDC_COLLECTION_DATA_FIELDS();
string sql = "";
if (dsParams.Tables.Contains(EDC_COLLECTION_DATA_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dtParams = dsParams.Tables[EDC_COLLECTION_DATA_FIELDS.DATABASE_TABLE_NAME];
for (int i = 0; i < dtParams.Rows.Count; i++)
{
OperationAction action = (OperationAction)Convert.ToInt32(dtParams.Rows[i][COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION]);
switch (action)
{
case OperationAction.New:
//生成INSERT SQL
string colkey = UtilHelper.GenerateNewKey(0);
dtParams.Rows[i][EDC_COLLECTION_DATA_FIELDS.FIELD_COL_KEY] = colkey;
sql = DatabaseTable.BuildInsertSqlStatement(edcCollectionData, dtParams, i,
new Dictionary <string, string>()
{
{ COMMON_FIELDS.FIELD_COMMON_EDITOR, editor },
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIME, string.Empty },
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIMEZONE, "CN-ZH" },
},
new List <string>()
{
COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION
});
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
break;
case OperationAction.Update:
//生成UPDATE SQL
string paramValue = Convert.ToString(dtParams.Rows[i][EDC_COLLECTION_DATA_FIELDS.FIELD_PARAM_VALUE]);
string colKey = Convert.ToString(dtParams.Rows[i][EDC_COLLECTION_DATA_FIELDS.FIELD_COL_KEY]);
sql = string.Format(@"UPDATE EDC_COLLECTION_DATA
SET PARAM_VALUE='{1}',EDIT_TIME= GETDATE(),EDITOR='{2}'
WHERE COL_KEY='{0}'",
colKey.PreventSQLInjection(),
paramValue.PreventSQLInjection(),
editor.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
break;
default:
break;
}
}
}
}
public DataSet InsertNewInf(string parameter, string useqty, string useunit, string conrtastQty, string conrtastUnt, string name)
{
IList <string> sqlCommandList = new List <string>();
DataSet retDS = new DataSet();
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
try
{
///sql定义start--------------------------------------------------------------------------------------------------------------
string sqlKo = @" INSERT INTO dbo.WST_STORE_MATERIAL_BUCKLE_CONTROL
(PARAMETER_KEY,PARAMETER,USE_QTY,USE_UNIT,
USE_CONRTAST_QTY,USE_CONRTAST_UNIT,CREATOR,
CREATER_TIME,STATUS)
VALUES
('{0}','{1}',{2},'{3}',{4},'{5}','{6}',GETDATE(),1) ";
string check = string.Format(@"SELECT COUNT(*) AS COUNT FROM WST_STORE_MATERIAL_BUCKLE_CONTROL
WHERE PARAMETER = '{0}' AND STATUS = 1", parameter);
if (Convert.ToInt32(db.ExecuteDataSet(CommandType.Text, check).Tables[0].Rows[0]["COUNT"].ToString()) > 0)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, "已经存在该参数的配置信息！请确认");
}
else
{
///sql执行start-------------------------------------------------------------------------------------------------------------------
//插入抬头表信息
string strInsert = string.Format(sqlKo,
UtilHelper.GenerateNewKey(0), //主键
parameter,
useqty,
useunit,
conrtastQty,
conrtastUnt,
name
);
db.ExecuteNonQuery(dbTrans, CommandType.Text, strInsert);
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
}
catch (Exception ex)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, ex.Message);
LogService.LogError("InsertNewInf Error: " + ex.Message);
}
return(retDS);
}
public DataSet ProIsert(DataSet dsSetIn)
{
DataSet retDS = new DataSet();
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
string sql = string.Empty;
try
{
if (null != dsSetIn)
{
sql = @" INSERT INTO AWMS_PP_ZMMDBYP
(BYP_KEY,MATNR_M,MATNR_B2 ,MATNR_B3 ,
PTYP3, CDATE,CUSER,MDATE,MUSER)
VALUES('{0}','{1}','{2}','{3}','{4}',GETDATE(),'{5}',GETDATE(),'{6}')";
string key = UtilHelper.GenerateNewKey(0);
DataTable dtHash = dsSetIn.Tables["HASH"];
Hashtable hsTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtHash);
string sql2 = string.Format(sql,
key,
dsSetIn.Tables["PP_ZMMDBYP"].Rows[0]["MATNR_M"].ToString(),
dsSetIn.Tables["PP_ZMMDBYP"].Rows[0]["MATNR_B2"].ToString(),
dsSetIn.Tables["PP_ZMMDBYP"].Rows[0]["MATNR_B3"].ToString(),
dsSetIn.Tables["PP_ZMMDBYP"].Rows[0]["PTYP3"].ToString(),
hsTable["CREATOR"].ToString(),
hsTable["EDITOR"].ToString());
db2.ExecuteNonQuery(CommandType.Text, sql2);
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, "${res:Global.ServerMessage.M0004}");
}
}
catch (Exception ex)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, ex.Message);
LogService.LogError("ProIsert Error: " + ex.Message);
}
return(retDS);
}
/// <summary>
/// 批次进站时更新设备信息。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbTrans">数据库操作事务对象。</param>
/// <param name="lotKey">批次主键。</param>
/// <param name="lotCurrentQuantity">批次当前数量。</param>
/// <param name="operationKey">工序主键。</param>
/// <param name="stepKey">工步主键。</param>
/// <param name="equipmentKey">设备主键。</param>
/// <param name="userName">用户。</param>
/// <returns>true：更新设备信息成功。false：更新设备信息失败。</returns>
internal static bool TrackInForEquipment(Database db, DbTransaction dbTrans,
string lotKey,
double lotCurrentQuantity,
string operationKey,
string stepKey,
string equipmentKey,
string userName)
{
const string RUN_STATE_NAME = "RUN";
string lostStateKey = string.Empty;
string runStateKey = string.Empty;
string lostToRunChangeStateKey = string.Empty;
int n = 0;
if (db != null && dbTrans != null)
{
string sqlString = string.Empty;
//获取设备当前状态的主键。
sqlString = string.Format("SELECT EQUIPMENT_STATE_KEY FROM EMS_EQUIPMENTS WHERE EQUIPMENT_KEY='{0}'", equipmentKey.PreventSQLInjection());
lostStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
//获取设备RUN的主键。
sqlString = string.Format("SELECT EQUIPMENT_STATE_KEY FROM EMS_EQUIPMENT_STATES WHERE EQUIPMENT_STATE_NAME='{0}'", RUN_STATE_NAME);
runStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
//获取设备当前状态->RUN的事件更改主键。
sqlString = string.Format(@"SELECT EQUIPMENT_CHANGE_STATE_KEY FROM EMS_EQUIPMENT_CHANGE_STATES
WHERE EQUIPMENT_FROM_STATE_KEY='{0}'
AND EQUIPMENT_TO_STATE_KEY='{1}'",
lostStateKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection());
lostToRunChangeStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
#region 新增批次设备数据
sqlString = string.Format("INSERT INTO EMS_LOT_EQUIPMENT(LOT_EQUIPMENT_KEY, LOT_KEY, OPERATION_KEY, EQUIPMENT_KEY, START_TIMESTAMP, USER_KEY, QUANTITY,STEP_KEY) " +
"VALUES('{0}','{1}','{2}','{3}', GETDATE(), '{4}', {5},'{6}')",
UtilHelper.GenerateNewKey(0),
lotKey.PreventSQLInjection(),
operationKey.PreventSQLInjection(),
equipmentKey.PreventSQLInjection(),
userName.PreventSQLInjection(),
lotCurrentQuantity,
stepKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//if (count <= 0)
//{
// throw new Exception("${res:FanHai.Hemera.Module.Common.M0003}");
//}
#endregion
//如果设备状态转变记录不为空。
if (!string.IsNullOrEmpty(lostToRunChangeStateKey))
{
#region 多腔体设备，需要更新父设备的状态和状态切换日志。
sqlString = string.Format("SELECT PARENT_EQUIPMENT_KEY FROM EMS_EQUIPMENTS WHERE EQUIPMENT_KEY = '{0}' AND ISCHAMBER = 1", equipmentKey.PreventSQLInjection());
object scalar = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
//多腔体设备。
if (scalar != null && scalar != DBNull.Value) //Multi Chamber Equipment
{
string parentEquipmentKey = scalar.ToString();
#region 更新父设备状态。
sqlString = string.Format(@"UPDATE EMS_EQUIPMENTS
SET EQUIPMENT_STATE_KEY = CASE EQUIPMENT_STATE_KEY WHEN '{3}' THEN '{1}' ELSE EQUIPMENT_STATE_KEY END,
EQUIPMENT_CHANGE_STATE_KEY = '{2}'
WHERE EQUIPMENT_KEY = '{0}' AND EQUIPMENT_STATE_KEY = '{3}'",
parentEquipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
lostToRunChangeStateKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection());
n = db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
if (n > 0)
{
#region 更新和插入父设备日志
//获取状态切换事件日志的最大值。
sqlString = string.Format(@"SELECT MAX(ISCURRENT)
FROM EMS_STATE_EVENT
WHERE EQUIPMENT_KEY='{0}'",
parentEquipmentKey.PreventSQLInjection());
object objCount = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
int count = 1;
if (objCount != null && objCount != DBNull.Value)
{
count = Convert.ToInt16(objCount) + 1;
}
//更新状态切换事件日志
sqlString = string.Format(@"UPDATE EMS_STATE_EVENT
SET EQUIPMENT_TO_STATE_KEY='{0}',EDIT_TIME=GETDATE(),EDITOR='{1}'
WHERE EQUIPMENT_KEY='{2}'
AND EQUIPMENT_FROM_STATE_KEY='{3}'
AND ISCURRENT={4}",
runStateKey.PreventSQLInjection(),
userName.PreventSQLInjection(),
parentEquipmentKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
count - 1);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//新增一笔状态切换事件日志
sqlString = string.Format(@"INSERT INTO EMS_STATE_EVENT
(EVENT_KEY,EQUIPMENT_KEY,EQUIPMENT_CHANGE_STATE_KEY,EQUIPMENT_FROM_STATE_KEY,ISCURRENT,CREATE_TIME,CREATOR)
VALUES('{0}', '{1}', '{2}', '{3}', '{4}', GETDATE(),'{5}')",
UtilHelper.GenerateNewKey(0),
parentEquipmentKey.PreventSQLInjection(),
lostToRunChangeStateKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
count,
userName.PreventSQLInjection());
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//if (command.ExecuteNonQuery() <= 0)
//{
// throw new Exception("${res:FanHai.Hemera.Module.Common.M0004}");
//}
#endregion
}
#endregion
}
#endregion
#region 更新设备状态
sqlString = string.Format(@"UPDATE EMS_EQUIPMENTS
SET EQUIPMENT_STATE_KEY = CASE EQUIPMENT_STATE_KEY WHEN '{3}' THEN '{1}' ELSE EQUIPMENT_STATE_KEY END,
EQUIPMENT_CHANGE_STATE_KEY = '{2}'
WHERE EQUIPMENT_KEY = '{0}' AND EQUIPMENT_STATE_KEY = '{3}'",
equipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
lostToRunChangeStateKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection());
n = db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
if (n > 0)
{
#region 更新设备日志状态，插入设备日志。
//获取状态切换事件日志的最大值。
sqlString = string.Format(@"SELECT MAX(ISCURRENT)
FROM EMS_STATE_EVENT
WHERE EQUIPMENT_KEY='{0}'",
equipmentKey.PreventSQLInjection());
object objCount = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
int count = 1;
if (objCount != null && objCount != DBNull.Value)
{
count = Convert.ToInt32(objCount) + 1;
}
//更新状态切换事件日志
sqlString = string.Format(@"UPDATE EMS_STATE_EVENT
SET EQUIPMENT_TO_STATE_KEY='{0}',EDIT_TIME=GETDATE(),EDITOR='{1}'
WHERE EQUIPMENT_KEY='{2}'
AND EQUIPMENT_FROM_STATE_KEY='{3}'
AND ISCURRENT ={4}",
runStateKey.PreventSQLInjection(),
userName.PreventSQLInjection(),
equipmentKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
count - 1);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//新增一笔状态切换事件日志
sqlString = string.Format(@"INSERT INTO EMS_STATE_EVENT
(EVENT_KEY,EQUIPMENT_KEY,EQUIPMENT_CHANGE_STATE_KEY,EQUIPMENT_FROM_STATE_KEY,ISCURRENT,CREATE_TIME,CREATOR)
VALUES('{0}', '{1}', '{2}', '{3}', '{4}', GETDATE(),'{5}')",
UtilHelper.GenerateNewKey(0),
equipmentKey.PreventSQLInjection(),
lostToRunChangeStateKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
count,
userName.PreventSQLInjection());
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//if (command.ExecuteNonQuery() <= 0)
//{
// throw new Exception("${res:FanHai.Hemera.Module.Common.M0004}");
//}
#endregion
}
#endregion
}
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 新增物料耗用。
/// </summary>
/// <param name="dsParams">包含物料耗用记录的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。</returns>
public DataSet InsertMaterial(DataSet dsParams)
{
const string CONST_GET_CURRENT_QTY_SQL = @" SELECT ISNULL(CURRENT_QTY,0) AS CURRENT_QTY
FROM WST_STORE_MATERIAL_DETAIL
WHERE STORE_MATERIAL_DETAIL_KEY='{0}'";
const string CONST_INSERT_1 = @" INSERT INTO WST_MATERIAL_USED_DETAIL
(MATERIAL_USED_DETAIL_KEY, MATERIAL_USED_KEY, STORE_MATERIAL_DETAIL_KEY,
MATERIAL_LOT, STATUS, USED_QTY, STIR_TIME,PRINT_QTY)
VALUES('{0}','{1}','{2}','{3}',1,{4},'{5}',{6})";
const string CONST_INSERT_2 = @" INSERT INTO WST_MATERIAL_USED
(MATERIAL_USED_KEY,OPERATION_KEY,EQUIPMENT_KEY,SHIFT_NAME,
OPERATOR,USED_TIME,DESCRIPTION,CREATE_TIME,CREATOR,CREATE_TIMEZONE,
EDITOR,EDIT_TIME,EDIT_TIMEZONE)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',GETDATE(),'{7}','{8}','{9}',GETDATE(),'{10}')";
string sqlInsert = string.Empty;
DataSet dsReturn = new DataSet();
IList <string> sqlCommandList = new List <string>();
DataTable dtDetail = dsParams.Tables["MATERIAL_USED_DATAIL"];
DataTable dtUsed = dsParams.Tables["MATERIAL_USED"];
DataTable dtHash = dsParams.Tables["HASH"];
Hashtable hsTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtHash);
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
try
{
//根据工序名称获取工序主键。
string operationName = Convert.ToString(dtUsed.Rows[0][0]);
string sql = string.Format(@"SELECT ROUTE_OPERATION_VER_KEY
FROM POR_ROUTE_OPERATION_VER WHERE ROUTE_OPERATION_NAME = '{0}'",
operationName.PreventSQLInjection());
string strOperationKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sql));
//生成插入耗用记录的SQL
string key = UtilHelper.GenerateNewKey(0);
string strInsertInToUsed = string.Format(CONST_INSERT_2,
key.PreventSQLInjection(), //主键
strOperationKey.PreventSQLInjection(), //工序主键
dtUsed.Rows[0][1].ToString().PreventSQLInjection(), //设备主键（EMS_EQUIPMENTS)
dtUsed.Rows[0][2].ToString().PreventSQLInjection(), //班次值
dtUsed.Rows[0][4].ToString().PreventSQLInjection(), //操作人
dtUsed.Rows[0][3].ToString().PreventSQLInjection(), //耗用时间
dtUsed.Rows[0][5].ToString().PreventSQLInjection(), //描述
hsTable["CREATOR"].ToString().PreventSQLInjection(), //创建人
hsTable["CREATE_TIMEZONE"].ToString().PreventSQLInjection(), //创建时区
hsTable["EDITOR"].ToString().PreventSQLInjection(), //编辑人
hsTable["EDIT_TIMEZONE"].ToString().PreventSQLInjection() //编辑时区
);
sqlCommandList.Add(strInsertInToUsed);
//生成插入耗用明细记录的SQL
foreach (DataRow dr in dtDetail.Rows)
{
string strStoreMaterialKey = dr["STORE_MATERIAL_DETAIL_KEY"].ToString(); //线上仓物料主键
string strStoreMaterialLot = dr["MATERIAL_LOT"].ToString(); //线上仓物料批号
double dUsedQty = Convert.ToDouble(dr["CURRENT_QTY"]); //耗用数量
string dtStirTime = dr["STIR_TIME"].ToString();
string strPrintQty = dr["PRINT_QTY"].ToString();
double dPrintQty = 0;
if (!double.TryParse(strPrintQty, out dPrintQty))
{
dPrintQty = 0;
}
//根据线上仓物料主键获取物料批对应的当前数量
string strGetCurrentQtySql = string.Format(CONST_GET_CURRENT_QTY_SQL, strStoreMaterialKey.PreventSQLInjection());
object objCurrentQty = db.ExecuteScalar(CommandType.Text, strGetCurrentQtySql);
double dCurrentQty = 0;
if (objCurrentQty != null)
{
dCurrentQty = Convert.ToDouble(objCurrentQty);
}
if (dCurrentQty < dUsedQty)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "物料:" + strStoreMaterialLot + "的当前数量小于耗用数量，不能保存。");
return(dsReturn);
}
//生成向物料耗用明细表插入记录的SQL语句
string strMaterialUsedDetailKey = System.Guid.NewGuid().ToString();
string sqlDetail = string.Format(CONST_INSERT_1,
strMaterialUsedDetailKey, //主键
key,
strStoreMaterialKey,
strStoreMaterialLot,
dUsedQty,
dtStirTime,
dPrintQty
);
sqlCommandList.Add(sqlDetail);
//modi by chao.pang 来料接收程序修改引发的只记录材料批次号，不扣减数量
//更新线上仓物料数量
//string strUpdateStoreSql = " UPDATE WST_STORE_MATERIAL_DETAIL " +
// " SET CURRENT_QTY= " + Convert.ToString(dCurrentQty - dUsedQty) + "" +
// " WHERE STORE_MATERIAL_DETAIL_KEY ='" + strStoreMaterialKey.PreventSQLInjection() + "'";
//sqlCommandList.Add(strUpdateStoreSql);
}
foreach (string sql1 in sqlCommandList)
{
db.ExecuteNonQuery(dbTrans, CommandType.Text, sql1);
}
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
dbTrans.Rollback();
LogService.LogError("InsertMaterial Error: " + ex.Message);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
}
finally
{
dbTrans = null;
dbCon.Close();
dbCon = null;
}
return(dsReturn);
}
/// <summary>
/// 电池片回收操作，用于撤销电池片报废和电池片补片。
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_RECOVERED"/>。
/// </remarks>
/// <param name="dsParams">包含回收信息的数据集对象。</param>
/// <returns>包含结果数据的数据集对象。</returns>
public DataSet LotRecovered(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
DbConnection dbConn = null;
DbTransaction dbTran = null;
try
{
dbConn = db.CreateConnection();
dbConn.Open();
dbTran = dbConn.BeginTransaction();
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(TRANS_TABLES.TABLE_PARAM) || //存放附加参数数据
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME)) //存放操作数据
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数不正确，请检查。");
return(dsReturn);
}
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_PARAM]; //存放附加参数数据
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtTransaction);
Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
string opEditTime = Convert.ToString(htParams[COMMON_FIELDS.FIELD_COMMON_EDIT_TIME]); //操作时编辑时间
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]);
string editTimeZone = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY]);
string editor = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]);
double leftQty = Convert.ToDouble(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT]);
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
//操作动作必须是 RECOVERED(电池片回收)
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_RECOVERED)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数的电池片回收操作动作不正确，请检查。");
return(dsReturn);
}
//检查记录是否过期。防止重复修改。
KeyValuePair <string, string> kvp = new KeyValuePair <string, string>(POR_LOT_FIELDS.FIELD_LOT_KEY, lotKey);
List <KeyValuePair <string, string> > listCondition = new List <KeyValuePair <string, string> >();
listCondition.Add(kvp);
//如果记录过期，当前编辑时间<数据库中的记录编辑时间。结束方法执行。
if (UtilHelper.CheckRecordExpired(db, POR_LOT_FIELDS.DATABASE_TABLE_NAME, listCondition, opEditTime))
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "信息已过期，请关闭该界面后重试。");
return(dsReturn);
}
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
//更新批次数量。
string sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}'
WHERE LOT_KEY='{3}'",
leftQty,
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection(),
lotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//向WIP_TRANSACTION表插入批次回收的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//如果数据集中包含名称WIP_RECOVERED_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dsParams.Tables.Contains(WIP_RECOVERED_FIELDS.DATABASE_TABLE_NAME))
{
WIP_RECOVERED_FIELDS recoveredFields = new WIP_RECOVERED_FIELDS();
DataTable dtRecovered = dsParams.Tables[WIP_RECOVERED_FIELDS.DATABASE_TABLE_NAME]; //存放回收明细数据
//获取不重复的被回收批次及其对应的编辑时间，只有在电池片回收是撤销补片操作时才发生。
var distinctRecoveredLot = from item in dtRecovered.AsEnumerable()
where Convert.ToString(item[WIP_RECOVERED_FIELDS.FIELD_RECOVERED_LOT_KEY]) != lotKey //排除回收批次
group item by new
{
LotKey = Convert.ToString(item[WIP_RECOVERED_FIELDS.FIELD_RECOVERED_LOT_KEY]),
EditTime = Convert.ToString(item[WIP_RECOVERED_FIELDS.FIELD_EDIT_TIME])
} into g
select new { LotKey = g.Key.LotKey, EditTime = g.Key.EditTime, Count = g.Count() };
foreach (var item in distinctRecoveredLot)
{
//检查记录是否过期。防止重复修改。
kvp = new KeyValuePair <string, string>(POR_LOT_FIELDS.FIELD_LOT_KEY, item.LotKey);
listCondition = new List <KeyValuePair <string, string> >();
listCondition.Add(kvp);
//如果记录过期，编辑时间<数据库中的记录编辑时间。结束方法执行。
if (UtilHelper.CheckRecordExpired(db, POR_LOT_FIELDS.DATABASE_TABLE_NAME, listCondition, item.EditTime))
{
string msg = string.Format("信息已过期，请关闭该界面后重试。");
ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
return(dsReturn);
}
}
//遍历批次的回收数据。
for (int i = 0; i < dtRecovered.Rows.Count; i++)
{
DataRow drRecovered = dtRecovered.Rows[i];
Hashtable htRecovered = CommonUtils.ConvertRowToHashtable(drRecovered);
string beRecoveredTransactionKey = string.Empty;
string recoveredType = Convert.ToString(htRecovered[WIP_RECOVERED_FIELDS.FIELD_RECOVERED_TYPE]);
//回收类型为撤销电池片补片（PATCH）操作,需要新增被回收批次的操作记录。
if (recoveredType == ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_PATCH)
{
string beRecoveredLotKey = Convert.ToString(htRecovered[WIP_RECOVERED_FIELDS.FIELD_BERECOVERED_LOT_KEY]);
double recoveredQuantity = Convert.ToDouble(htRecovered[WIP_RECOVERED_FIELDS.FIELD_RECOVERED_QUANTITY]);
//获取被回收批次的信息
DataSet dsPatchLotInfo = LotManagement.GetLotBasicInfo(db, dbTran, beRecoveredLotKey);
DataRow drPatchLotInfo = dsPatchLotInfo.Tables[0].Rows[0];
double quantityIn = Convert.ToDouble(drPatchLotInfo[POR_LOT_FIELDS.FIELD_QUANTITY]);
//double quantityOut = quantityIn - recoveredQuantity; //电池片补片时不再回加被补片批次的数量，所以在不需要减去回收数量
double quantityOut = quantityIn;
string enterpriseKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
string enterpriseName = Convert.ToString(drPatchLotInfo[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME]);
string routeKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
string routeName = Convert.ToString(drPatchLotInfo[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME]);
string stepKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
string stepName = Convert.ToString(drPatchLotInfo[POR_ROUTE_STEP_FIELDS.FIELD_ROUTE_STEP_NAME]);
string workOrderKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
string stateFlag = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
string reworkFlag = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_IS_REWORKED]);
string lineKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY]);
string lineName = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_OPR_LINE]);
string edcInsKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_EDC_INS_KEY]);
string equipmentKey = Convert.ToString(drPatchLotInfo[EMS_EQUIPMENTS_FIELDS.FIELD_EQUIPMENT_KEY]);
beRecoveredTransactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, beRecoveredTransactionKey, beRecoveredLotKey);
//更新被回收批次的数量
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}'
WHERE LOT_KEY='{3}'",
quantityOut,
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection(),
beRecoveredLotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//插入被回收批次的操作记录
Hashtable htBeRecoveredTransaction = new Hashtable(htTransaction);
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = beRecoveredTransactionKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY] = beRecoveredLotKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY] = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_BE_RECOVERED;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN] = quantityIn;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT] = quantityOut;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY] = enterpriseKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME] = enterpriseName;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY] = routeKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME] = routeName;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY] = stepKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME] = stepName;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY] = workOrderKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG] = stateFlag;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG] = reworkFlag;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY] = lineKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE] = lineName;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE_PRE] = lineName;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDC_INS_KEY] = edcInsKey;
htBeRecoveredTransaction[WIP_TRANSACTION_FIELDS.FIELD_EQUIPMENT_KEY] = equipmentKey;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htBeRecoveredTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
//插入一笔批次回收明细数据
//回收操作记录主键。
if (!htRecovered.ContainsKey(WIP_RECOVERED_FIELDS.FIELD_TRANSACTION_KEY))
{
htRecovered.Add(WIP_RECOVERED_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htRecovered[WIP_RECOVERED_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//被回收批次操作记录主键，只是回收操作是撤销电池片补片时才有值，否则为空白值。
if (!htRecovered.ContainsKey(WIP_RECOVERED_FIELDS.FIELD_BERECOVERED_TRANSACTION_KEY))
{
htRecovered.Add(WIP_RECOVERED_FIELDS.FIELD_BERECOVERED_TRANSACTION_KEY, beRecoveredTransactionKey);
}
htRecovered[WIP_RECOVERED_FIELDS.FIELD_BERECOVERED_TRANSACTION_KEY] = beRecoveredTransactionKey;
//重置回收明细的编辑时间为当前时间。
htRecovered[WIP_RECOVERED_FIELDS.FIELD_EDIT_TIME] = null;
sql = DatabaseTable.BuildInsertSqlStatement(recoveredFields, htRecovered, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
}
dbTran.Commit();
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("LotRecovered Error: " + ex.Message);
dbTran.Rollback();
}
finally
{
dbConn.Close();
}
return(dsReturn);
}
/// <summary>
/// 将线边仓中的批次进行合并。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库操作事务对象。</param>
/// <param name="dsParams">
/// 包含合批数据的数据集对象.
/// (1)数据集对象中包含一个名称为<see cref="BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME"/>的数据表对象。
/// 数据表中存放待合并的批次返工或退库数据。
/// (2) 数据集对象中包含一个名称为<see cref="TRANS_TABLES.TABLE_MAIN_DATA"/>的数据表对象。
/// 数据表中必须包含两个列"name"和"value"。列name存放哈希表的键名，列value存放哈希表键对应的键值。
/// 键名：
/// 合并到批次的返工或退库记录的主键<see cref="WST_STORE_MAT_FIELDS.FIELD_ROW_KEY"/>,
/// 合并到批次的批次号<see cref="POR_LOT_FIELDS.FIELD_LOT_NUMBER"/>,
/// 合并后批次的数量<see cref="POR_LOT_FIELDS.FIELD_QUANTITY"/>,
/// 编辑人<see cref="WIP_TRANSACTION_FIELDS.FIELD_EDITOR"/>,
/// 编辑时区<see cref="WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY"/>,
/// 编辑时间<see cref="WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME"/>,
/// 批次主键<see cref="POR_LOT_FIELDS.FIELD_LOT_KEY"/>。
/// </param>
private void MergeLotInStore(Database db, DbTransaction dbtran, DataSet dsParams)
{
String sql = string.Empty;
DataSet dsReturn = new DataSet();
DataSet ds;
string strParentRowKey = "";
string strParentLotNumber = "";
string strParentTransKey = "";
string strParentStepKey = "";
string strParentRouteKey = "";
string strParentEnterpriseKey = "";
string strParentQTY = "";
string strTotalQTY = "";
string strSumQTY = "";
string strComment = "";
string strEditor = "", strEditTimeZone = "", strEditTime = "";
string strLineKey = "", strShiftName = "", strWorkOrderKey = "", strStateFlag = "";
if (dsParams.Tables.Count > 0)//数据集中包含数据表。
{
DataTable dataTable = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable);
strParentRowKey = hashData[WST_STORE_MAT_FIELDS.FIELD_ROW_KEY].ToString();
strParentLotNumber = hashData["LOT_NUMBER"].ToString();
strSumQTY = hashData[POR_LOT_FIELDS.FIELD_QUANTITY].ToString();
//strComment = hashData[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT].ToString();
strEditor = hashData[WIP_TRANSACTION_FIELDS.FIELD_EDITOR].ToString();
strEditTimeZone = hashData[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY].ToString();
strEditTime = UtilHelper.GetSysdate(db).ToString("yyyy-MM-dd HH:mm:ss");
// Get Lot Details
ds = StoreEngine.GetStoreLotDetailsInfor(db, strParentRowKey);
strParentStepKey = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][WST_STORE_MAT_FIELDS.FIELD_STEP_KEY].ToString();
strParentRouteKey = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][WST_STORE_MAT_FIELDS.FIELD_ROUTE_KEY].ToString();
strParentEnterpriseKey = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][WST_STORE_MAT_FIELDS.FIELD_ENTERPRISE_KEY].ToString();
strParentQTY = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][WST_STORE_MAT_FIELDS.FIELD_ITEM_QTY].ToString();
strLineKey = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][WST_STORE_MAT_FIELDS.FIELD_LINE_KEY].ToString();
strShiftName = UtilHelper.GetShiftKey(db, strEditTime);
strWorkOrderKey = ds.Tables[WST_STORE_MAT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
//更新WST_STORE_MAT记录
sql = string.Format(@"UPDATE WST_STORE_MAT
SET ITEM_QTY= '{0}', EDIT_TIME=GETDATE()
WHERE ROW_KEY='{1}'",
strSumQTY.PreventSQLInjection(),
strParentRowKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
strTotalQTY = (Convert.ToInt32(strParentQTY) + Convert.ToInt32(strSumQTY)).ToString();
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
#region 合并批次操作记录
Hashtable parenttransactionTable = new Hashtable();
DataTable parenttransaction = new DataTable();
strParentTransKey = UtilHelper.GenerateNewKey(0);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, hashData[POR_LOT_FIELDS.FIELD_LOT_KEY].ToString());
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_CHILD_LOT_KEY, strParentRowKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strParentQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, strTotalQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_STOREMERGE);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, strComment);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, strEditTime);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strStateFlag);
//向WIP_TRANSACTION插入合并到批次的操作记录
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, parenttransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 生成子批次
string strChildRowKey = "";
string strChildLotNumber = "";
string strChildQty = "";
if (dsParams.Tables.Contains(BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME))
{
DataTable parameterdatatable = dsParams.Tables[BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME];
for (int i = 0; i < parameterdatatable.Rows.Count; i++)
{
//更新WST_STORE_MAT数据
strChildRowKey = parameterdatatable.Rows[i][WST_STORE_MAT_FIELDS.FIELD_ROW_KEY].ToString();
strChildLotNumber = parameterdatatable.Rows[i]["LOT_NUMBER"].ToString();
strChildQty = parameterdatatable.Rows[i][WST_STORE_MAT_FIELDS.FIELD_ITEM_QTY].ToString();
sql = @"UPDATE WST_STORE_MAT
SET ITEM_QTY='0',OBJECT_STATUS='3', EDIT_TIME=GETDATE()
WHERE ROW_KEY='" + strChildRowKey.PreventSQLInjection() + "'";
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//向WIP_TRANSACTION插入被合并批次的操作记录
Hashtable childtransactionTable = new Hashtable();
DataTable childtransaction = new DataTable();
//sql = "";
string strChildTransKey = UtilHelper.GenerateNewKey(0);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, parameterdatatable.Rows[i][POR_LOT_FIELDS.FIELD_LOT_KEY].ToString());
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_CHILD_LOT_KEY, strChildRowKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strChildQty);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_STORE_MERGED);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, strEditTime);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strStateFlag);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, childtransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//向WIP_MERGE插入合并批次记录
Hashtable MergeHashTable = new Hashtable();
WIP_MERGE_FIELDS wipMerge = new WIP_MERGE_FIELDS();
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_LOT_KEY, strChildRowKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MAIN_LOT_KEY, strParentRowKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MERGE_QUANTITY, strChildQty);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_STEP_KEY, strParentStepKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDITOR, strEditor);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIME, strEditTime);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIMEZONE, strEditTimeZone);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_TRANSACTION_KEY, strChildTransKey);
sql = DatabaseTable.BuildInsertSqlStatement(wipMerge, MergeHashTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
}
}
#endregion
}
}
/// <summary>
/// 执行合并批次的操作。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库操作事务。</param>
/// <param name="dsParams">包含合并数据的数据集。</param>
private void MergeLot(Database db, DbTransaction dbtran, DataSet dsParams)
{
string sql = string.Empty;
DataSet dsReturn = new DataSet();
DataSet ds;
string strParentLotKey = "";
string strParentLotNumber = "";
string strParentTransKey = "";
string strParentStepKey = "";
string strParentRouteKey = "";
string strParentEnterpriseKey = "";
string strParentQTY = "";
string strTotalQTY = "";
string strComment = "";
string strEditor = "", strEditTimeZone = "", strEditTime = "";
string strLineKey = "", strShiftName = "", strWorkOrderKey = "", strStateFlag = "";
string oprLine = "";
if (dsParams.Tables.Count > 0)
{
//获取输入参数。
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
strParentLotKey = htParams[POR_LOT_FIELDS.FIELD_LOT_KEY].ToString();
strParentLotNumber = htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER].ToString();
strTotalQTY = htParams[POR_LOT_FIELDS.FIELD_QUANTITY].ToString();
strComment = htParams[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT].ToString();
strEditor = htParams[WIP_TRANSACTION_FIELDS.FIELD_EDITOR].ToString();
strEditTimeZone = htParams[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY].ToString();
strEditTime = UtilHelper.GetSysdate(db).ToString("yyyy-MM-dd HH:mm:ss");
string shiftKey = Convert.ToString(htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY]);
string operComputerName = Convert.ToString(htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER]);
if (htParams.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME))
{
strShiftName = htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME].ToString();
}
else
{
strShiftName = UtilHelper.GetShiftValue(db, strEditTime);
}
if (string.IsNullOrEmpty(shiftKey))
{
shiftKey = UtilHelper.GetShiftKey(db, strEditTime);
}
//获取合并到批次的详细信息。
ds = LotManagement.GetLotDetailsEx(db, dbtran, strParentLotKey);
strParentStepKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString();
strParentRouteKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY].ToString();
strParentEnterpriseKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString();
strParentQTY = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString();
strLineKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY].ToString();
oprLine = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_OPR_LINE].ToString();
strWorkOrderKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
strStateFlag = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_STATE_FLAG].ToString();
string reworkFlag = Convert.ToString(ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_IS_REWORKED]);
//更新合并到批次的数据
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}'
WHERE LOT_KEY='{3}'",
strTotalQTY.PreventSQLInjection(),
strEditor.PreventSQLInjection(),
strEditTimeZone.PreventSQLInjection(),
strParentLotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//记录合批操作。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable parenttransactionTable = new Hashtable();
DataTable parenttransaction = new DataTable();
strParentTransKey = UtilHelper.GenerateNewKey(0);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, strParentLotKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strParentQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, strTotalQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_MERGE);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, strComment);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, strEditTime);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strStateFlag);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, reworkFlag);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, strEditor);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, shiftKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, operComputerName);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, parenttransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
string strChildLotKey = "";
string strChildLotNumber = "";
string strChildQty = "";
if (dsParams.Tables.Contains(BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dtBaseParameters = dsParams.Tables[BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME];
//循环被合并的批次。
for (int i = 0; i < dtBaseParameters.Rows.Count; i++)
{
strChildLotKey = dtBaseParameters.Rows[i]["LOT_KEY"].ToString();
strChildLotNumber = dtBaseParameters.Rows[i]["LOT_NUMBER"].ToString();
strChildQty = dtBaseParameters.Rows[i]["QUANTITY"].ToString();
//更新被合并批次的数据
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY='0',DELETED_TERM_FLAG='1',EDITOR='{0}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{1}'
WHERE LOT_KEY='{2}'",
strEditor.PreventSQLInjection(),
strEditTimeZone.PreventSQLInjection(),
strChildLotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//插入被合并批次的交易记录
Hashtable childtransactionTable = new Hashtable();
DataTable childtransaction = new DataTable();
string strChildTransKey = UtilHelper.GenerateNewKey(0);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, strChildLotKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strChildQty);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_MERGED);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, strEditTime);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strStateFlag);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, reworkFlag);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, strEditor);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, shiftKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, operComputerName);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, childtransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//插入WIP_MERGE表
Hashtable MergeHashTable = new Hashtable();
WIP_MERGE_FIELDS wipMerge = new WIP_MERGE_FIELDS();
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_LOT_KEY, strChildLotKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MAIN_LOT_KEY, strParentLotKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MERGE_QUANTITY, strChildQty);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_STEP_KEY, strParentStepKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDITOR, strEditor);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIME, strEditTime);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIMEZONE, strEditTimeZone);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_TRANSACTION_KEY, strChildTransKey);
sql = DatabaseTable.BuildInsertSqlStatement(wipMerge, MergeHashTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
}
}
}
}
/// <summary>
/// 退料操作，根据操作名称(<see cref="WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY"/>)区分。
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_RETURN_MATERIAL"/>。
/// </remarks>
/// <param name="dsParams">包含退料信息的数据集对象。</param>
/// <returns>包含结果数据的数据集对象。</returns>
public DataSet LotReturnMaterial(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
DbConnection dbConn = null;
DbTransaction dbTran = null;
try
{
dbConn = db.CreateConnection();
dbConn.Open();
dbTran = dbConn.BeginTransaction();
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(TRANS_TABLES.TABLE_PARAM) || //存放附加参数数据
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME)) //存放操作数据
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数不正确，请检查。");
return(dsReturn);
}
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_PARAM]; //存放附加参数数据
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtTransaction);
Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
string opEditTime = Convert.ToString(htParams[COMMON_FIELDS.FIELD_COMMON_EDIT_TIME]); //操作时编辑时间
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]);
string editTimeZone = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY]);
string editor = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]);
double qty = Convert.ToDouble(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN]);
double leftQty = Convert.ToDouble(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT]);
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
//操作动作必须是 RETURN_MATERIAL
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_RETURN_MATERIAL)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数的退料操作动作不正确，请检查。");
return(dsReturn);
}
//检查记录是否过期。防止重复修改。
KeyValuePair <string, string> kvp = new KeyValuePair <string, string>(POR_LOT_FIELDS.FIELD_LOT_KEY, lotKey);
List <KeyValuePair <string, string> > listCondition = new List <KeyValuePair <string, string> >();
listCondition.Add(kvp);
//如果记录过期，当前编辑时间<数据库中的记录编辑时间。结束方法执行。
if (UtilHelper.CheckRecordExpired(db, POR_LOT_FIELDS.DATABASE_TABLE_NAME, listCondition, opEditTime))
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, COMMON_FIELDS.FIELD_COMMON_EDITTIME_EXP);
return(dsReturn);
}
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
//更新批次数量。
string sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}',DELETED_TERM_FLAG={3}
WHERE LOT_KEY='{4}'",
leftQty,
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection(),
leftQty > 0 ? 0 : 1,
lotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//更新线上仓物料
//查询工单主键，批次数量，物料批号，车间主键,工序名称。
string sqlCommand = string.Format(@"SELECT a.MATERIAL_CODE,a.MATERIAL_LOT,a.FACTORYROOM_KEY,a.CREATE_OPERTION_NAME
FROM POR_LOT a
WHERE a.LOT_KEY='{0}'",
lotKey.PreventSQLInjection());
DataSet ds = db.ExecuteDataSet(dbTran, CommandType.Text, sqlCommand);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
string materialLot = Convert.ToString(ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_MATERIAL_LOT]);
string materialCode = Convert.ToString(ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_MATERIAL_CODE]);
string factoryRoomKey = Convert.ToString(ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY]);
string createOperationName = Convert.ToString(ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CREATE_OPERTION_NAME]);
//根据车间主键+工序+仓库类型获取线上仓主键。
sqlCommand = string.Format(@"SELECT STORE_KEY,STORE_NAME
FROM WST_STORE
WHERE STORE_TYPE='9' AND LOCATION_KEY='{0}' AND OPERATION_NAME='{1}'",
factoryRoomKey.PreventSQLInjection(),
createOperationName.PreventSQLInjection());
ds = db.ExecuteDataSet(dbTran, CommandType.Text, sqlCommand);
string storeKey = string.Empty;
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
storeKey = Convert.ToString(ds.Tables[0].Rows[0]["STORE_KEY"]);
}
string storeMaterialDetailKey = string.Empty;
//根据线上仓主键 + 物料批号键获取线上仓物料明细主键。
sqlCommand = string.Format(@"SELECT b.STORE_MATERIAL_DETAIL_KEY
FROM WST_STORE_MATERIAL a
LEFT JOIN POR_MATERIAL c ON a.MATERIAL_KEY=c.MATERIAL_KEY
LEFT JOIN WST_STORE_MATERIAL_DETAIL b ON a.STORE_MATERIAL_KEY=b.STORE_MATERIAL_KEY
WHERE a.STORE_KEY='{0}' AND b.MATERIAL_LOT='{1}' AND c.MATERIAL_CODE='{2}'",
storeKey.PreventSQLInjection(),
materialLot.PreventSQLInjection(),
materialCode.PreventSQLInjection());
ds = db.ExecuteDataSet(dbTran, CommandType.Text, sqlCommand);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
storeMaterialDetailKey = Convert.ToString(ds.Tables[0].Rows[0]["STORE_MATERIAL_DETAIL_KEY"]);
}
//更新线上仓+物料批次数量。
sqlCommand = string.Format(@"UPDATE WST_STORE_MATERIAL_DETAIL
SET CURRENT_QTY=CURRENT_QTY+{0}
WHERE STORE_MATERIAL_DETAIL_KEY='{1}'",
qty - leftQty,
storeMaterialDetailKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand);
}
//向WIP_TRANSACTION表插入批次退料的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//如果数据集中包含名称WIP_RETURN_MAT_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dsParams.Tables.Contains(WIP_RETURN_MAT_FIELDS.DATABASE_TABLE_NAME))
{
WIP_RETURN_MAT_FIELDS returnFields = new WIP_RETURN_MAT_FIELDS();
DataTable dtReturn = dsParams.Tables[WIP_RETURN_MAT_FIELDS.DATABASE_TABLE_NAME]; //存放退料明细数据
//遍历批次的退料数据。
for (int i = 0; i < dtReturn.Rows.Count; i++)
{
DataRow drReturn = dtReturn.Rows[i];
Hashtable htReturn = CommonUtils.ConvertRowToHashtable(drReturn);
if (!htReturn.ContainsKey(WIP_RETURN_MAT_FIELDS.FIELD_TRANSACTION_KEY))
{
htReturn.Add(WIP_RETURN_MAT_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htReturn[WIP_RETURN_MAT_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//插入一笔批次退料数据。
sql = DatabaseTable.BuildInsertSqlStatement(returnFields, htReturn, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
}
dbTran.Commit();
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("LotReturnMaterial Error: " + ex.Message);
dbTran.Rollback();
}
finally
{
dbConn.Close();
}
return(dsReturn);
}
/// <summary>
///（电池片/组件）报废操作
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CELLSCRAP"/>和<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_SCRAP"/>。
/// </remarks>
/// <param name="dsParams">包含报废信息的数据集对象。</param>
/// <param name="dbTran">数据库操作事务对象。</param>
private void LotScrap(DataSet dsParams, DbTransaction dbTran)
{
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME) ||
!dsParams.Tables.Contains(WIP_SCRAP_FIELDS.DATABASE_TABLE_NAME) ||
dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME].Rows.Count == 0 //批次操作记录不能为空记录。
)
{
throw new Exception("传入参数不正确，请检查。");
}
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = CommonUtils.ConvertToHashtable(dtTransaction);
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]); //批次主键
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
//操作动作必须是 SCRAP 或 CELLSCRAP
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_SCRAP &&
activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CELLSCRAP)
{
throw new Exception("传入参数的报废操作动作不正确，请检查。");
}
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
string sql = string.Empty;
//是否是组件报废操作？
if (activity == ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_SCRAP)
{
//更新批次状态。
sql = string.Format(@"UPDATE POR_LOT
SET DELETED_TERM_FLAG=1
WHERE LOT_KEY='{0}'",
lotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
//向WIP_TRANSACTION表插入批次报废的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//如果数据集中包含名称WIP_SCRAP_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dsParams.Tables.Contains(WIP_SCRAP_FIELDS.DATABASE_TABLE_NAME))
{
WIP_SCRAP_FIELDS scrapFields = new WIP_SCRAP_FIELDS();
DataTable dtScrap = dsParams.Tables[WIP_SCRAP_FIELDS.DATABASE_TABLE_NAME]; //存放报废明细数据
//遍历批次的报废数据。
for (int i = 0; i < dtScrap.Rows.Count; i++)
{
DataRow drScrap = dtScrap.Rows[i];
Hashtable htScrap = CommonUtils.ConvertRowToHashtable(drScrap);
if (!htScrap.ContainsKey(WIP_SCRAP_FIELDS.FIELD_TRANSACTION_KEY))
{
htScrap.Add(WIP_SCRAP_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htScrap[WIP_SCRAP_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//插入一笔批次报废数据。
sql = DatabaseTable.BuildInsertSqlStatement(scrapFields, htScrap, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
}
}
/// <summary>
/// 批次出站时更新设备信息。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbTrans">数据库操作事务对象。</param>
/// <param name="lotKey">批次主键。</param>
/// <param name="stepKey">工步主键。</param>
/// <param name="equipmentKey">设备主键。</param>
/// <param name="userName">用户。</param>
/// <returns>true：更新设备信息成功。false：更新设备信息失败。</returns>
internal static bool TrackOutForEquipment(Database db, DbTransaction dbTrans,
string lotKey,
string stepKey,
string equipmentKey,
string userName)
{
const string LOST_STATE_NAME = "LOST";
//const string RUN_STATE_NAME = "RUN";
string lostStateKey = string.Empty;
string runStateKey = string.Empty;
string runToLostChangeStateKey = string.Empty;
int n = 0;
if (db != null && dbTrans != null)
{
string sqlString = string.Empty;
//获取设备LOST的主键。
sqlString = string.Format("SELECT EQUIPMENT_STATE_KEY FROM EMS_EQUIPMENT_STATES WHERE EQUIPMENT_STATE_NAME='{0}'", LOST_STATE_NAME);
lostStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
//获取设备当前状态的主键。
sqlString = string.Format("SELECT EQUIPMENT_STATE_KEY FROM EMS_EQUIPMENTS WHERE EQUIPMENT_KEY='{0}'", equipmentKey.PreventSQLInjection());
runStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
//获取设备当前状态->LOST的事件更改主键。
sqlString = string.Format(@"SELECT EQUIPMENT_CHANGE_STATE_KEY FROM EMS_EQUIPMENT_CHANGE_STATES
WHERE EQUIPMENT_FROM_STATE_KEY='{0}'
AND EQUIPMENT_TO_STATE_KEY='{1}'",
runStateKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection());
runToLostChangeStateKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
#region 更新批次设备信息表
sqlString = string.Format(@"UPDATE EMS_LOT_EQUIPMENT
SET END_TIMESTAMP = GETDATE(),USER_KEY='{3}'
WHERE LOT_KEY = '{0}' AND STEP_KEY = '{1}' AND EQUIPMENT_KEY = '{2}' AND END_TIMESTAMP IS NULL",
lotKey.PreventSQLInjection(),
stepKey.PreventSQLInjection(),
equipmentKey.PreventSQLInjection(),
userName.PreventSQLInjection());
n = db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
if (n <= 0)
{
LogService.LogError(sqlString);
//throw new Exception("更新批次设备信息数据失败。");
}
#endregion
#region 检查设备当前是否有处理的批次。
//检查设备当前是否有处理的批次。
sqlString = string.Format(@"SELECT LOT_EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE EQUIPMENT_KEY = '{0}' AND END_TIMESTAMP IS NULL",
equipmentKey.PreventSQLInjection());
string lotEquipmentKey = Convert.ToString(db.ExecuteScalar(dbTrans, CommandType.Text, sqlString));
if (!string.IsNullOrEmpty(runToLostChangeStateKey) && //可以转变为LOST
string.IsNullOrEmpty(lotEquipmentKey)) //Equipment Currently Without Processing Lot
{
#region 多腔体设备，需要更新父设备的状态和状态切换日志。
sqlString = string.Format("SELECT PARENT_EQUIPMENT_KEY FROM EMS_EQUIPMENTS WHERE EQUIPMENT_KEY = '{0}' AND ISCHAMBER = 1",
equipmentKey.PreventSQLInjection());
object scalar = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
if (scalar != null && scalar != DBNull.Value) //多腔体设备。
{
string parentEquipmentKey = scalar.ToString();
//检查父设备所有的子设备是否有正在处理的批次。
sqlString = string.Format(@"SELECT a.LOT_EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT a
LEFT JOIN EMS_EQUIPMENTS b ON a.EQUIPMENT_KEY=b.EQUIPMENT_KEY
WHERE a.EQUIPMENT_KEY = '{0}'
AND b.PARENT_EQUIPMENT_KEY='{1}'
AND a.END_TIMESTAMP IS NULL",
equipmentKey.PreventSQLInjection(),
parentEquipmentKey.PreventSQLInjection());
scalar = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
if (scalar != null && scalar != DBNull.Value)
{
#region 更新父设备状态。
sqlString = string.Format(@"UPDATE EMS_EQUIPMENTS
SET EQUIPMENT_STATE_KEY = CASE EQUIPMENT_STATE_KEY WHEN '{3}' THEN '{1}' ELSE EQUIPMENT_STATE_KEY END,
EQUIPMENT_CHANGE_STATE_KEY = '{2}'
WHERE EQUIPMENT_KEY = '{0}' AND EQUIPMENT_STATE_KEY = '{3}'",
parentEquipmentKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
runToLostChangeStateKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection());
n = db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
if (n > 0)
{
#region 更新和插入父设备日志
//获取状态切换事件日志的最大值。
sqlString = string.Format(@"SELECT MAX(ISCURRENT)
FROM EMS_STATE_EVENT
WHERE EQUIPMENT_KEY='{0}'",
parentEquipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection());
object objCount = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
int count = 1;
if (objCount != null && objCount != DBNull.Value)
{
count = Convert.ToInt32(objCount) + 1;
}
//更新状态切换事件日志
sqlString = string.Format(@"UPDATE EMS_STATE_EVENT
SET EQUIPMENT_TO_STATE_KEY='{0}',EDIT_TIME=GETDATE(),EDITOR='{1}'
WHERE EQUIPMENT_KEY='{2}'
AND EQUIPMENT_FROM_STATE_KEY='{3}'
AND ISCURRENT={4}",
lostStateKey.PreventSQLInjection(),
userName.PreventSQLInjection(),
parentEquipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
count - 1);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//新增一笔状态切换事件日志
sqlString = string.Format(@"INSERT INTO EMS_STATE_EVENT
(EVENT_KEY,EQUIPMENT_KEY,EQUIPMENT_CHANGE_STATE_KEY,EQUIPMENT_FROM_STATE_KEY,ISCURRENT,CREATE_TIME,CREATOR)
VALUES('{0}', '{1}', '{2}', '{3}', '{4}', GETDATE(),'{5}')",
UtilHelper.GenerateNewKey(0),
parentEquipmentKey.PreventSQLInjection(),
runToLostChangeStateKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
count,
userName.PreventSQLInjection());
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//if (command.ExecuteNonQuery() <= 0)
//{
// throw new Exception("${res:FanHai.Hemera.Module.Common.M0005}");
//}
#endregion
}
#endregion
}
}
#endregion
#region 更新设备状态
sqlString = string.Format(@"UPDATE EMS_EQUIPMENTS
SET EQUIPMENT_STATE_KEY = CASE EQUIPMENT_STATE_KEY WHEN '{3}' THEN '{1}' ELSE EQUIPMENT_STATE_KEY END,
EQUIPMENT_CHANGE_STATE_KEY = '{2}'
WHERE EQUIPMENT_KEY = '{0}' AND EQUIPMENT_STATE_KEY = '{3}'",
equipmentKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
runToLostChangeStateKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection());
n = db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
if (n > 0)
{
#region 更新设备日志状态，插入设备日志。
//获取状态切换事件日志的最大值。
sqlString = string.Format(@"SELECT MAX(ISCURRENT)
FROM EMS_STATE_EVENT
WHERE EQUIPMENT_KEY='{0}'",
equipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection());
object objCount = db.ExecuteScalar(dbTrans, CommandType.Text, sqlString);
double count = 1;
if (objCount != null && objCount != DBNull.Value)
{
count = Convert.ToDouble(objCount) + 1;
}
//更新状态切换事件日志
sqlString = string.Format(@"UPDATE EMS_STATE_EVENT
SET EQUIPMENT_TO_STATE_KEY='{0}',EDIT_TIME=GETDATE(),EDITOR='{1}'
WHERE EQUIPMENT_KEY='{2}'
AND EQUIPMENT_FROM_STATE_KEY='{3}'
AND ISCURRENT={4}",
lostStateKey.PreventSQLInjection(),
userName.PreventSQLInjection(),
equipmentKey.PreventSQLInjection(),
runStateKey.PreventSQLInjection(),
count - 1);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//新增一笔状态切换事件日志
sqlString = string.Format(@"INSERT INTO EMS_STATE_EVENT
(EVENT_KEY,EQUIPMENT_KEY,EQUIPMENT_CHANGE_STATE_KEY,EQUIPMENT_FROM_STATE_KEY,ISCURRENT,CREATE_TIME,CREATOR)
VALUES('{0}', '{1}', '{2}', '{3}', '{4}', GETDATE(),'{5}')",
UtilHelper.GenerateNewKey(0),
equipmentKey.PreventSQLInjection(),
runToLostChangeStateKey.PreventSQLInjection(),
lostStateKey.PreventSQLInjection(),
count,
userName.PreventSQLInjection());
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString);
//if (command.ExecuteNonQuery() <= 0)
//{
// throw new Exception("${res:FanHai.Hemera.Module.Common.M0004}");
//}
#endregion
}
#endregion
}
#endregion
return(true);
}
else
{
return(false);
}
}
//ds为柜明细表中取值的托信息的数据集，dt为前台gridview中的数据
public DataSet UpdateArkInf(DataSet ds, DataTable dt, int flag)
{
DataSet retDS = new DataSet();
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
string sql = string.Empty;
try
{
#region
//0.判定柜是是新组柜还是已经存在的柜
// 无托信息柜组柜
//1.新增信息到柜抬头表
//2.新增组柜信息到明细表
//3.修改包装表中托状态为已组柜1
if (flag == 1)
{
string key = UtilHelper.GenerateNewKey(0);
DataTable dtHash = ds.Tables["HASH"];
Hashtable hsTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtHash);
string sql01 = string.Format(@"INSERT INTO AWMS_CONTAINER(CONTAINER_KEY,CONTAINER_CODE,STATUS) VALUES('{0}','{1}','0')",
key,
hsTable["CONTAINER_CODE"].ToString()
);
db2.ExecuteNonQuery(CommandType.Text, sql01);
if (dt != null)
{
string sql02 = string.Format(@"SELECT CONTAINER_KEY,CONTAINER_CODE FROM AWMS_CONTAINER WHERE STATUS = '0' AND CONTAINER_CODE ='{0}'", hsTable["CONTAINER_CODE"].ToString());
DataSet ds02 = db2.ExecuteDataSet(CommandType.Text, sql02);
for (int i = 0; i < dt.Rows.Count; i++)
{
string sql03 = string.Format(@"INSERT INTO AWMS_CONTAINER_DETAIL(CONTAINER_KEY,CONTAINER_CODE,PALLET_NO,CREATOR,CDATE,STATUS)
VALUES('{0}','{1}','{2}','{3}',GETDATE(),'1')",
ds02.Tables[0].Rows[0]["CONTAINER_KEY"].ToString(),
ds02.Tables[0].Rows[0]["CONTAINER_CODE"].ToString(),
dt.Rows[i]["PALLET_NO"],
hsTable["CREATOR"].ToString()
);
db2.ExecuteNonQuery(CommandType.Text, sql03);
}
DataTable dt04 = dt;
string strPlus = string.Empty;
for (int i = 0; i < dt04.Rows.Count; i++)
{
strPlus += "'" + dt04.Rows[i]["PALLET_NO"].ToString().Trim() + "',";
}
if (string.IsNullOrEmpty(strPlus))
{
strPlus = "'',";
}
string str = strPlus.Substring(0, strPlus.Length - 1);
string sql04 = string.Format(@"UPDATE WIP_CONSIGNMENT SET ARK_FLAG = '1' WHERE PALLET_NO IN({0})",
str
);
db.ExecuteNonQuery(CommandType.Text, sql04);
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
}
#endregion
// 有柜组柜
// 明细表中没有托信息则不执行1 2两步
//1.删除明细表中该柜主键的所有托信息
//2.修改包装表中对应的托状态为未组柜0
//3.新增组柜托信息到明细表
//4.修改包装表中对应托状态为已组柜1
else if (flag == 0)
{
string key = UtilHelper.GenerateNewKey(0);
DataTable dtHash = ds.Tables["HASH"];
Hashtable hsTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtHash);
#region
if (dt != null)
{
string sql01 = string.Format(@"SELECT CONTAINER_KEY,CONTAINER_CODE FROM AWMS_CONTAINER WHERE STATUS = '0' AND CONTAINER_CODE ='{0}'"
, hsTable["CONTAINER_CODE"].ToString());
DataSet ds01 = db2.ExecuteDataSet(CommandType.Text, sql01);
if (ds01 != null)
{
if (ds01.Tables[0].Rows.Count == 1)
{
string sql02 = string.Format(@"SELECT PALLET_NO FROM AWMS_CONTAINER_DETAIL WHERE CONTAINER_KEY = '{0}'"
, ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString());
DataSet ds02 = db2.ExecuteDataSet(CommandType.Text, sql02);
string sql03 = string.Format(@"DELETE FROM AWMS_CONTAINER_DETAIL WHERE CONTAINER_KEY = '{0}'"
, ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString());
db2.ExecuteNonQuery(CommandType.Text, sql03);
string strPlus04 = string.Empty;
DataTable dt04 = ds02.Tables[0];
for (int i = 0; i < dt04.Rows.Count; i++)
{
strPlus04 += "'" + dt04.Rows[i]["PALLET_NO"].ToString().Trim() + "',";
}
if (string.IsNullOrEmpty(strPlus04))
{
strPlus04 = "'',";
}
string str04 = strPlus04.Substring(0, strPlus04.Length - 1);
string sql04 = string.Format(@"UPDATE WIP_CONSIGNMENT SET ARK_FLAG = '0' WHERE PALLET_NO IN({0})"
, str04);
db.ExecuteNonQuery(CommandType.Text, sql04);
for (int i = 0; i < dt.Rows.Count; i++)
{
string sql05 = string.Format(@"INSERT INTO AWMS_CONTAINER_DETAIL(CONTAINER_KEY,CONTAINER_CODE,PALLET_NO,CREATOR,CDATE,STATUS)
VALUES('{0}','{1}','{2}','{3}',GETDATE(),'1')",
ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString(),
ds01.Tables[0].Rows[0]["CONTAINER_CODE"].ToString(),
dt.Rows[i]["PALLET_NO"],
hsTable["CREATOR"].ToString()
);
db2.ExecuteNonQuery(CommandType.Text, sql05);
}
DataTable dt06 = dt;
string strPlus06 = string.Empty;
for (int i = 0; i < dt06.Rows.Count; i++)
{
strPlus06 += "'" + dt06.Rows[i]["PALLET_NO"].ToString().Trim() + "',";
}
if (string.IsNullOrEmpty(strPlus06))
{
strPlus06 = "'',";
}
string str06 = strPlus06.Substring(0, strPlus06.Length - 1);
string sql06 = string.Format(@"UPDATE WIP_CONSIGNMENT SET ARK_FLAG = '1' WHERE PALLET_NO IN({0})",
str06
);
db.ExecuteNonQuery(CommandType.Text, sql06);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string sql05 = string.Format(@"INSERT INTO AWMS_CONTAINER_DETAIL(CONTAINER_KEY,CONTAINER_CODE,PALLET_NO,CREATOR,CDATE,STATUS)
VALUES('{0}','{1}','{2}','{3}',GETDATE(),'1')",
ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString(),
ds01.Tables[0].Rows[0]["CONTAINER_CODE"].ToString(),
dt.Rows[i]["PALLET_NO"],
hsTable["CREATOR"].ToString()
);
db2.ExecuteNonQuery(CommandType.Text, sql05);
}
DataTable dt06 = dt;
string strPlus06 = string.Empty;
for (int i = 0; i < dt06.Rows.Count; i++)
{
strPlus06 += "'" + dt06.Rows[i]["PALLET_NO"].ToString().Trim() + "',";
}
if (string.IsNullOrEmpty(strPlus06))
{
strPlus06 = "'',";
}
string str06 = strPlus06.Substring(0, strPlus06.Length - 1);
string sql06 = string.Format(@"UPDATE WIP_CONSIGNMENT SET ARK_FLAG = '1' WHERE PALLET_NO IN({0})",
str06
);
db.ExecuteNonQuery(CommandType.Text, sql06);
}
}
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
#endregion
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (dt == null)
{
string sql01 = string.Format(@"SELECT CONTAINER_KEY,CONTAINER_CODE FROM AWMS_CONTAINER WHERE STATUS = '0' AND CONTAINER_CODE ='{0}'"
, hsTable["CONTAINER_CODE"].ToString());
DataSet ds01 = db2.ExecuteDataSet(CommandType.Text, sql01);
if (ds01 != null)
{
if (ds01.Tables[0].Rows.Count == 1)
{
string sql02 = string.Format(@"SELECT PALLET_NO FROM AWMS_CONTAINER_DETAIL WHERE CONTAINER_KEY = '{0}'"
, ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString());
DataSet ds02 = db2.ExecuteDataSet(CommandType.Text, sql02);
string sql03 = string.Format(@"DELETE FROM AWMS_CONTAINER_DETAIL WHERE CONTAINER_KEY = '{0}'"
, ds01.Tables[0].Rows[0]["CONTAINER_KEY"].ToString());
db2.ExecuteNonQuery(CommandType.Text, sql03);
string strPlus04 = string.Empty;
DataTable dt04 = ds02.Tables[0];
for (int i = 0; i < dt04.Rows.Count; i++)
{
strPlus04 += "'" + dt04.Rows[i]["PALLET_NO"].ToString().Trim() + "',";
}
if (string.IsNullOrEmpty(strPlus04))
{
strPlus04 = "'',";
}
string str04 = strPlus04.Substring(0, strPlus04.Length - 1);
string sql04 = string.Format(@"UPDATE WIP_CONSIGNMENT SET ARK_FLAG = '0' WHERE PALLET_NO IN({0})"
, str04);
db.ExecuteNonQuery(CommandType.Text, sql04);
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, string.Empty);
}
}
}
}
}
catch (Exception ex)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(retDS, ex.Message);
LogService.LogError("UpdateArkInf Error: " + ex.Message);
}
return(retDS);
}
/// <summary>
/// 保存数据采集实例数据。
/// </summary>
/// <remarks>
/// 新增数据采集实例数据并根据生产批次状态以完成生产批次的数据采集。
/// </remarks>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库事务对象。</param>
/// <param name="dataset">包含数据采集实例数据的数据集。</param>
internal static void SaveEDCMainIn(Database db, DbTransaction dbtran, DataSet dsParams)
{
string sql = "";
string activityType = string.Empty;
string editor = "", lineKey = "", workOrderKey = "", quantity = "", stepKey = "";
//新增数据采集实例数据
EDC_MAIN_INS_FIELDS edcMainIns = new EDC_MAIN_INS_FIELDS();
DataTable dtParams = dsParams.Tables[EDC_MAIN_INS_FIELDS.DATABASE_TABLE_NAME];
Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
string lotKey = Convert.ToString(htParams[EDC_MAIN_INS_FIELDS.FIELD_LOT_KEY]);
string edcInsKey = Convert.ToString(htParams[EDC_MAIN_INS_FIELDS.FIELD_EDC_INS_KEY]);
string transactionState = string.Empty;
editor = Convert.ToString(htParams[COMMON_FIELDS.FIELD_COMMON_EDITOR]);
htParams.Remove(COMMON_FIELDS.FIELD_COMMON_EDITOR);
htParams.Add(EDC_MAIN_INS_FIELDS.FIELD_COL_START_TIME, null);
htParams.Add(EDC_MAIN_INS_FIELDS.FIELD_COL_END_TIME, null);
htParams.Add(EDC_MAIN_INS_FIELDS.FIELD_CREATOR, editor);
htParams.Add(EDC_MAIN_INS_FIELDS.FIELD_EDITOR, editor);
sql = DatabaseTable.BuildInsertSqlStatement(edcMainIns, htParams, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//获取生产批次状态。
int state = -1;
sql = @"SELECT STATE_FLAG FROM POR_LOT WHERE LOT_KEY='" + lotKey + "'";
state = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sql));
//更新生产批次状态。
if (state == 0)
{
transactionState = "1";
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_INEDC;
sql = @"UPDATE POR_LOT SET STATE_FLAG=1, EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE()," +
"EDC_INS_KEY='" + edcInsKey.PreventSQLInjection() + "' " +
"WHERE LOT_KEY='" + lotKey.PreventSQLInjection() + "'";
}
else if (state == 4)
{
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_OUTEDC;
transactionState = "5";
sql = @"UPDATE POR_LOT SET STATE_FLAG=5, EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE()," +
"EDC_INS_KEY='" + edcInsKey.PreventSQLInjection() + "' " +
"WHERE LOT_KEY='" + lotKey.PreventSQLInjection() + "'";
}
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//获取批次信息。
DataSet dsLot = GetLotsInfo(db, dbtran, lotKey);
if (null != dsLot && dsLot.Tables.Count > 0 && dsLot.Tables[0].Rows.Count > 0)
{
lineKey = Convert.ToString(dsLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY]);
quantity = Convert.ToString(dsLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY]);
workOrderKey = Convert.ToString(dsLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
stepKey = Convert.ToString(dsLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
#region 新增交易记录
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable hashTable = new Hashtable();
string strTransactionKey = UtilHelper.GenerateNewKey(0);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strTransactionKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, lotKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, activityType);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, quantity);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, quantity);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "EDC");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, editor);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, "CN-ZH");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, null);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, transactionState);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, lineKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, stepKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, workOrderKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDC_INS_KEY, edcInsKey);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, hashTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
}
else
{
throw new Exception("获取批次信息失败。");
}
}
/// <summary>
/// 执行批次进站作业。
/// </summary>
/// <param name="dsParams">
/// 数据集对象。包含名称为<see cref="TRANS_TABLES.TABLE_PARAM"/>的数据表。
/// 数据表中必须包含两个列"name"和"value"。列name存放哈希表的键名，列value存放哈希表键对应的键值。</param>
/// <returns>
/// 包含执行结果的数据集。
/// 0：成功。-1或1：失败。 2：需要进行数据采集。
/// </returns>
public DataSet TrackInLot(DataSet dsParams)
{
DateTime startTime = DateTime.Now;
DataSet dsReturn = new DataSet();
string sql = "";
DbConnection dbconn = null;
DbTransaction dbtran = null;
string lotKey = "",
lineKey = "",
workOrderKey = "",
stepKey = "",
quantityIn = "",
user = "",
editTime = "",
lotNumber = "",
strEquKey = "",
strEquStateKey = "",
strOperationKey = "",
strIsBatch = "",
maxQuantity = "",
inProductQuantity = "",
isAutoTrackOut = string.Empty,
oprLine = string.Empty,
shiftName = string.Empty,
lineName = string.Empty,
editTimeZone = string.Empty,
shiftKey = string.Empty,
operateCompName = string.Empty;
try
{
dbconn = db.CreateConnection();
dbconn.Open();
//Create Transaction
dbtran = dbconn.BeginTransaction();
//如果数据集中包含TRANS_TABLES.TABLE_PARAM的数据表
if (dsParams.Tables.Contains(TRANS_TABLES.TABLE_PARAM))
{
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_PARAM];
Hashtable htParams = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
lotKey = htParams[POR_LOT_FIELDS.FIELD_LOT_KEY].ToString();
lotNumber = htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER].ToString();
lineKey = htParams[WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY].ToString();
workOrderKey = htParams[WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
stepKey = htParams[WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY].ToString();
quantityIn = htParams[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN].ToString();
user = htParams[WIP_TRANSACTION_FIELDS.FIELD_EDITOR].ToString();
editTime = htParams[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME].ToString();
editTimeZone = htParams[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY].ToString();
oprLine = htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE].ToString();
shiftName = htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME].ToString();
lineName = htParams[POR_LOT_FIELDS.FIELD_LINE_NAME].ToString();
shiftKey = Convert.ToString(htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY]);
operateCompName = Convert.ToString(htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER]);
}
#region 检查记录是否过期。
KeyValuePair <string, string> kvp = new KeyValuePair <string, string>(POR_LOT_FIELDS.FIELD_LOT_KEY, lotKey);
List <KeyValuePair <string, string> > listCondition = new List <KeyValuePair <string, string> >();
listCondition.Add(kvp);
//判断数据是否过期，如果过期，则返回执行结果为“数据已过期。”，结束方法执行。
if (UtilHelper.CheckRecordExpired(db, POR_LOT_FIELDS.DATABASE_TABLE_NAME, listCondition, editTime))
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, 1, "数据已过期");
return(dsReturn);
}
#endregion
DateTime dtCurrent = UtilHelper.GetSysdate(db);
#region 检查设备
//如果数据集合中包含EMS_EQUIPMENTS_FIELDS.DATABASE_TABLE_NAME数据表对象。
if (dsParams.Tables.Contains(EMS_EQUIPMENTS_FIELDS.DATABASE_TABLE_NAME))
{
DataTable equDataTable = dsParams.Tables[EMS_EQUIPMENTS_FIELDS.DATABASE_TABLE_NAME];
Hashtable equHashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(equDataTable);
strEquKey = equHashData[EMS_EQUIPMENTS_FIELDS.FIELD_EQUIPMENT_KEY].ToString();
strOperationKey = equHashData[POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY].ToString();
//如果设备主键有值。
if (strEquKey != "" && strEquKey.Length > 0)
{
sql = @"SELECT a.ISBATCH,a.EQUIPMENT_STATE_KEY,a.MAXQUANTITY ,b.EQUIPMENT_STATE_TYPE,b.EQUIPMENT_STATE_NAME
FROM EMS_EQUIPMENTS a
LEFT JOIN EMS_EQUIPMENT_STATES b ON a.EQUIPMENT_STATE_KEY=b.EQUIPMENT_STATE_KEY
WHERE EQUIPMENT_KEY='" + strEquKey.PreventSQLInjection() + "'";
DataSet dsEqu = db.ExecuteDataSet(CommandType.Text, sql);//查询设备
//如果有数据。
if (dsEqu.Tables[0].Rows.Count > 0)
{
strIsBatch = Convert.ToString(dsEqu.Tables[0].Rows[0][EMS_EQUIPMENTS_FIELDS.FIELD_ISBATCH]); //设备是否是批处理设备。
strEquStateKey = Convert.ToString(dsEqu.Tables[0].Rows[0][EMS_EQUIPMENTS_FIELDS.FIELD_EQUIPMENT_STATE_KEY]); //设备状态。
maxQuantity = Convert.ToString(dsEqu.Tables[0].Rows[0][EMS_EQUIPMENTS_FIELDS.FIELD_MAXQUANTITY]); //最大数量。
string strEquStateName = Convert.ToString(dsEqu.Tables[0].Rows[0][EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_NAME]); //设备状态名称。
string strEquStateType = Convert.ToString(dsEqu.Tables[0].Rows[0][EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_TYPE]); //设备状态类型。
#region 检查设备状态
//如果设备状态不是“待产”
if (strEquStateType != "LOST")
{
//如果设备状态是“在产”
if (strEquStateType == "RUN")
{
//如果是批处理设备。
if (strIsBatch == "1")
{
//获取设备在产数量。
sql = @"SELECT ISNULL(SUM(T.QUANTITY),0) AS INQUANTITY
FROM EMS_LOT_EQUIPMENT T
WHERE T.EQUIPMENT_KEY = '" + strEquKey + "' AND T.END_TIMESTAMP IS NULL";
DataSet dsLotEqu = db.ExecuteDataSet(CommandType.Text, sql);
if (dsLotEqu.Tables[0].Rows.Count > 0 && !string.IsNullOrEmpty(maxQuantity))//获取设备在产数量成功
{
inProductQuantity = dsLotEqu.Tables[0].Rows[0]["inQuantity"].ToString();
//在产数量>设备的最大数量，结束方法执行。
if (Convert.ToInt32(inProductQuantity) + Convert.ToInt32(quantityIn) > Convert.ToInt32(maxQuantity))
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "${res:SolarViewer.Hemera.Modules.Wip.WipEngine.Msg.BigThenMaxQuantity}");
return(dsReturn);
}
}
}
else
{
//如果不是批处理设备，结束方法执行。
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "${res:SolarViewer.Hemera.Modules.Wip.WipEngine.Msg.BatchIsFalse}");
return(dsReturn);
}
}
else
{
//如果设备状态不是在产，结束方法执行。
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "${res:SolarViewer.Hemera.Modules.Wip.WipEngine.Msg.EquipmentCanNotUser}");
return(dsReturn);
}
}
#endregion
}
else//获取设备数据失败。
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "${res:SolarViewer.Hemera.Modules.Wip.WipEngine.Msg.GetEquipmentError}");
return(dsReturn);
}
}
}
#endregion
//批次进站，更新批次数据，插入批次进站操作记录。
WipManagement.TrackInLot(db, dbtran, dsParams);
//设备主键不为空。
if (strEquKey != "")
{
//批次和设备进行关联。
WipManagement.TrackInForEquipment(lotKey, Convert.ToInt32(quantityIn), strOperationKey, stepKey, strEquKey, user, dbtran);
}
#region 检查自动合批
//工步主键不为空。
if (stepKey != "")
{
string isLastStep = "";
string isAutoMerge = "";
int maxBoxQuantity = -1;
DataSet dsStepUda = GetStepUda(stepKey);
//如果获取到工步自定义属性数据。
if (dsStepUda.Tables[0].Rows.Count > 0)
{
//遍历工序的自定义属性。
for (int i = 0; i < dsStepUda.Tables[0].Rows.Count; i++)
{
string szAttriName = dsStepUda.Tables[0].Rows[i][POR_ROUTE_STEP_ATTR_FIELDS.FIELD_ATTRIBUTE_NAME].ToString();
string szAttriValue = dsStepUda.Tables[0].Rows[i][POR_ROUTE_STEP_ATTR_FIELDS.FIELD_ATTRIBUTE_VALUE].ToString();
//是否是最后一个工步。
if (szAttriName == "IsLastStep")
{
isLastStep = szAttriValue;
}
//是否自动合批。
if (szAttriName == "AutoMerge")
{
isAutoMerge = szAttriValue;
}
//箱子最大数量。
if (szAttriName == "MaxBoxQuantity" && !int.TryParse(szAttriValue, out maxBoxQuantity))
{
maxBoxQuantity = -1;
}
//是否自动出站。
if (szAttriName == "AutoTrackOut")
{
isAutoTrackOut = szAttriValue;
}
}
if (isAutoMerge.ToLower() == "true")//进行自动合批
{
AutoMerge(db, dbtran, lotKey, workOrderKey, stepKey, lineKey,
quantityIn, maxBoxQuantity, 4, user, false, oprLine, shiftName);
}
}
}
#endregion
#region 检查自动出站
//自动出站
if (isAutoTrackOut.ToLower() == "true")
{
DataSet dsInfo = GetLotsInfo(dbtran, lotKey);
//获取批次信息成功。
if (dsInfo.Tables.Count > 0 && dsInfo.Tables[0].Rows.Count > 0)
{
string nextRunTime = string.Empty;
string sDuration = Convert.ToString(dsInfo.Tables[0].Rows[0][POR_ROUTE_STEP_FIELDS.FIELD_DURATION]);
double duration = 0;
if (!double.TryParse(sDuration, out duration))
{
duration = 0;
}
nextRunTime = dtCurrent.AddMinutes(duration).ToString("yyyy-MM-dd HH:mm:ss");
Hashtable hashWipJob = new Hashtable();
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_EDIT_TIMEZONE, editTimeZone);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_EDITOR, user);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_EDIT_TIME, dtCurrent);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_ENTERPRISE_KEY, dsInfo.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString());
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_CREATETIME, dtCurrent);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_EQUIPMENT_KEY, strEquKey);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_CLOSETYPE, string.Empty);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_NEXTRUNTIME, nextRunTime);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_RUNACCOUNT, "0");
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_STATUS, "0");
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_JOB_TYPE, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TRACKIN);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_LINE_NAME, lineName);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_LOT_KEY, lotKey);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_LOT_NUMBER, lotNumber);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_NOTIFY_USER, string.Empty);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_ROUTE_KEY, dsInfo.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_ROW_KEY, UtilHelper.GenerateNewKey(0));
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_STEP_KEY, dsInfo.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
hashWipJob.Add(WIP_JOB_FIELDS.FIELDS_WORKORDER_NUMBER, dsInfo.Tables[0].Rows[0][POR_WORK_ORDER_FIELDS.FIELD_ORDER_NUMBER]);
//插入一笔自动过站任务。
WipJobAutoTrack.InsertWipJob(db, dbtran, hashWipJob);
}
}
#endregion
//检查是否需要进行数据采集。
string edcPointKey = string.Empty;
if (CheckNeedEdc(dbtran, lotKey, strEquKey, out edcPointKey))
{
IEDCEngine edcEngine = RemotingServer.ServerObjFactory.Get <IEDCEngine>();
edcEngine.SaveEdcMainInfo(dbtran, lotKey, edcPointKey, user, strEquKey, oprLine, shiftKey);
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, 2, "批次" + lotNumber + "需要抽检");
}
else
{
//更新批次状态为9（WaitingForTrackout）
UpdateLotState(dbtran, lotKey, 9);
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, 0, string.Empty);
}
//检查是否需要进行锁定。
CheckAndUpdateFutureHold(dbtran, lotKey, stepKey, shiftName, shiftKey, user,
operateCompName, editTimeZone, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TRACKIN);
dbtran.Commit();
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, 1, "批次进站出错" + ex.Message);
LogService.LogError("TrackInLot Error: " + ex.Message);
dbtran.Rollback();
}
finally
{
dbconn.Close();
}
System.DateTime endTime = System.DateTime.Now;
LogService.LogInfo("TrackIn Lot Time: " + (endTime - startTime).TotalMilliseconds.ToString());
return(dsReturn);
}
/// <summary>
/// 新增数据采集实例数据。
/// </summary>
/// <remarks>
/// 新增数据采集实例数据并根据生产批次状态以完成生产批次的数据采集。
/// </remarks>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库事务对象。</param>
/// <param name="dataset">包含数据采集实例数据的数据集。</param>
internal static void InsertEdcMainInsData(Database db, DbTransaction dbtran, DataSet dsParams)
{
int lotState = -1;
string transactionState = string.Empty;
string activityType = string.Empty;
string sqlCommand = string.Empty;
List <string> sqlCommandList = new List <string>();
DataTable dtParams = dsParams.Tables[EDC_MAIN_INS_FIELDS.DATABASE_TABLE_NAME];
//生成新增数据采集实例数据的SQL
DatabaseTable.BuildInsertSqlStatements(ref sqlCommandList,
new EDC_MAIN_INS_FIELDS(),
dtParams,
new Dictionary <string, string>()
{
{ EDC_MAIN_INS_FIELDS.FIELD_COL_START_TIME, null },
{ EDC_MAIN_INS_FIELDS.FIELD_COL_END_TIME, null }
},
new List <string>()
{
POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY,
POR_LOT_FIELDS.FIELD_QUANTITY,
COMMON_FIELDS.FIELD_COMMON_EDITOR
});
//获取批次状态。
string lotKey = Convert.ToString(dtParams.Rows[0][POR_LOT_FIELDS.FIELD_LOT_KEY]);
sqlCommand = string.Format(@"SELECT STATE_FLAG FROM POR_LOT WHERE LOT_KEY='{0}'", lotKey.PreventSQLInjection());
lotState = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sqlCommand));
//更新批次状态。
sqlCommand = @"UPDATE POR_LOT
SET STATE_FLAG = {0},
EDITOR = '{1}',
EDIT_TIME =GETDATE(),
EDC_INS_KEY = '{2}'
WHERE LOT_KEY = '{3}'";
if (lotState == 0)
{
transactionState = "1";
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_INEDC;
sqlCommand = string.Format(sqlCommand, 1,
dtParams.Rows[0][COMMON_FIELDS.FIELD_COMMON_EDITOR],
dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_EDC_INS_KEY],
dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_LOT_KEY]);
}
else if (lotState == 4)
{
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_OUTEDC;
transactionState = "5";
sqlCommand = string.Format(sqlCommand, 5,
dtParams.Rows[0][COMMON_FIELDS.FIELD_COMMON_EDITOR],
dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_EDC_INS_KEY],
dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_LOT_KEY]);
}
sqlCommandList.Add(sqlCommand);
//生产新增操作记录的SQL
DatabaseTable.BuildInsertSqlStatements(ref sqlCommandList,
new WIP_TRANSACTION_FIELDS(),
new DataTable(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME),
new Dictionary <string, string>()
{
{ EDC_MAIN_INS_FIELDS.FIELD_COL_START_TIME, null },
{ EDC_MAIN_INS_FIELDS.FIELD_COL_END_TIME, null },
{ WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, UtilHelper.GenerateNewKey(0) },
{ WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0" },
{ WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_LOT_KEY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, activityType },
{ WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, dtParams.Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, dtParams.Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "EDC" },
{ WIP_TRANSACTION_FIELDS.FIELD_EDITOR, dtParams.Rows[0][COMMON_FIELDS.FIELD_COMMON_EDITOR].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
{ WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, "CN-ZH" },
{ WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, transactionState },
{ WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, dtParams.Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_STEP_KEY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, dtParams.Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString() },
{ WIP_TRANSACTION_FIELDS.FIELD_EDC_INS_KEY, dtParams.Rows[0][EDC_MAIN_INS_FIELDS.FIELD_EDC_INS_KEY].ToString() }
},
new List <string>());
//执行数据新增。
if (sqlCommandList.Count > 0)
{
DbConnection dbConn = db.CreateConnection();
dbConn.Open();
DbTransaction dbTrans = dbConn.BeginTransaction();
try
{
foreach (string sql in sqlCommandList)
{
db.ExecuteNonQuery(dbTrans, CommandType.Text, sql);
}
dbTrans.Commit();
}
catch (Exception ex)
{
dbTrans.Rollback();
throw ex;
}
finally
{
dbTrans = null;
dbConn.Close();
dbConn = null;
}
}
}
/// <summary>
/// 自动合批操作。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据操作的事务对象。</param>
/// <param name="lotKeyFrom">需要合批的批次主键。</param>
/// <param name="workOrderKeyFrom">工单主键。</param>
/// <param name="stepKeyFrom">工步主键</param>
/// <param name="lineKeyFrom">线别主键</param>
/// <param name="quantityFrom">数量</param>
/// <param name="maxBoxQuantity">最大箱数量</param>
/// <param name="stateFlag">批次状态标记</param>
/// <param name="editor">编辑人</param>
/// <param name="isRework">是否重工</param>
/// <param name="oprLine">操作线别</param>
/// <param name="shiftName">操作班别</param>
public static void AutoMerge(Database db,
DbTransaction dbtran,
string lotKeyFrom,
string workOrderKeyFrom,
string stepKeyFrom,
string lineKeyFrom,
string quantityFrom,
int maxBoxQuantity,
int stateFlag,
string editor,
bool isRework,
string oprLine,
string shiftName)
{
string lotKey = "", quantity = "", enterpriseKey = "", routeKey = "", stepKey = "";
int totalQuantity = 0;
int mergedQuantity = 0;
int leftQuantity = 0;
string sql = "", strParentTransKey = "";
DataSet dsMergeLot = new DataSet();
//获取批次主键不等于指定批次，但工单为指定工单，工步为指定工步，线别为指定线别，状态为指定状态的批次信息。
sql = string.Format(@"SELECT A.LOT_KEY,A.QUANTITY,A.ROUTE_ENTERPRISE_VER_KEY,A.CUR_ROUTE_VER_KEY,A.CUR_STEP_VER_KEY
FROM POR_LOT A
LEFT JOIN FMM_PRODUCTION_LINE B ON A.LINE_NAME=B.LINE_NAME
WHERE A.LOT_KEY !='{0}'
AND A.WORK_ORDER_KEY='{1}'
AND A.CUR_STEP_VER_KEY='{2}'
AND B.PRODUCTION_LINE_KEY='{3}'
AND A.STATUS = 1 AND A.QUANTITY != 0
AND A.DELETED_TERM_FLAG = 0
AND A.HOLD_FLAG=0
AND A.STATE_FLAG={4}",
lotKeyFrom.PreventSQLInjection(),
workOrderKeyFrom.PreventSQLInjection(),
stepKeyFrom.PreventSQLInjection(),
lineKeyFrom.PreventSQLInjection(), stateFlag);
//如果指定了最大箱数量，则查询条件增加数量小于最大箱数量。
if (maxBoxQuantity != -1)
{
sql = sql + " AND A.QUANTITY<" + maxBoxQuantity + "";
}
//如果是重工，则查询条件增加重工>0
if (isRework)
{
sql = sql + " AND A.REWORK_FLAG > 0";
}
dsMergeLot = db.ExecuteDataSet(CommandType.Text, sql);
//查询得到的数据集中的数据表个数>0。
if (dsMergeLot.Tables.Count > 0)
{
//可以合并的批次的记录大于0。
if (dsMergeLot.Tables[0].Rows.Count > 0)
{
lotKey = dsMergeLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_LOT_KEY].ToString(); //批次主键
quantity = dsMergeLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString(); //数量
enterpriseKey = dsMergeLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString(); //流程组主键
routeKey = dsMergeLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY].ToString(); //流程主键
stepKey = dsMergeLot.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString(); //工步主键
string time = UtilHelper.GetSysdate(db).ToString("yyyy-MM-dd HH:mm:ss"); //当前时间
//合并的批次数量>0 并且 被合并的批次数量>0
if (quantity.Length > 0 && quantityFrom.Length > 0)
{
totalQuantity = Convert.ToInt32(quantity) + Convert.ToInt32(quantityFrom);
#region 更新合批批次的数量
//如果最大箱数量不等于 -1 并且总数量>最大箱数量。
if (maxBoxQuantity != -1 & totalQuantity > maxBoxQuantity)
{
#region 更新合并到的批次数量为最大箱数量。
sql = @"UPDATE POR_LOT SET QUANTITY=" + maxBoxQuantity + "," +
"EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE() " +
"WHERE LOT_KEY='" + lotKey.PreventSQLInjection() + "'";
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 更新被合并的批次数量为剩余数量。
leftQuantity = totalQuantity - maxBoxQuantity;
mergedQuantity = Convert.ToInt32(quantityFrom) - leftQuantity;
sql = @"UPDATE POR_LOT SET QUANTITY=" + leftQuantity + "," +
"EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE() " +
"WHERE LOT_KEY='" + lotKeyFrom.PreventSQLInjection() + "'";
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
}
else
{
#region 更新合并到的批次数量为总数量。
sql = @"UPDATE POR_LOT SET QUANTITY=" + totalQuantity + "," +
"EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE()" +
"WHERE LOT_KEY='" + lotKey.PreventSQLInjection() + "'";
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 更新被合并的批次数量为0。删除标记为1。
mergedQuantity = Convert.ToInt32(quantityFrom);
sql = @"UPDATE POR_LOT SET QUANTITY='0',DELETED_TERM_FLAG='1',
EDITOR='" + editor.PreventSQLInjection() + "'," +
"EDIT_TIME=GETDATE() " +
"WHERE LOT_KEY='" + lotKeyFrom.PreventSQLInjection() + "'";
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
}
#endregion
#region 插入一笔【合并到批次】的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable parenttransactionTable = new Hashtable();
DataTable parenttransaction = new DataTable();
strParentTransKey = UtilHelper.GenerateNewKey(0);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, lotKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, quantity);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, totalQuantity);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_MERGE);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, "");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, "CN-ZH");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, time);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, lineKeyFrom);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, workOrderKeyFrom);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, stateFlag.ToString());
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, shiftName);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, parenttransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 插入一笔【被合并批次】的操作记录。
Hashtable childtransactionTable = new Hashtable();
DataTable childtransaction = new DataTable();
string strChildTransKey = UtilHelper.GenerateNewKey(0);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, lotKeyFrom);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, mergedQuantity.ToString());
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, leftQuantity.ToString());
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_MERGED);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, "");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, "CN-ZH");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, time);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, lineKeyFrom);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, workOrderKeyFrom);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, stateFlag.ToString());
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, shiftName);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, childtransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 插入一笔批次合批的操作记录。
Hashtable MergeHashTable = new Hashtable();
WIP_MERGE_FIELDS wipMerge = new WIP_MERGE_FIELDS();
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_LOT_KEY, lotKeyFrom);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MAIN_LOT_KEY, lotKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_MERGE_QUANTITY, mergedQuantity.ToString());
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_STEP_KEY, stepKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ROUTE_KEY, routeKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_ENTERPRISE_KEY, enterpriseKey);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDITOR, "");
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIME, time);
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_EDIT_TIMEZONE, "CN-ZH");
MergeHashTable.Add(WIP_MERGE_FIELDS.FIELD_CHILD_TRANSACTION_KEY, strChildTransKey);
sql = DatabaseTable.BuildInsertSqlStatement(wipMerge, MergeHashTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
}
}
}
}
/// <summary>
/// 批次入库。
/// </summary>
/// <param name="dbTran">数据库事务对象。</param>
/// <param name="drLotInfo">批次信息。</param>
/// <param name="dtOperationParam">工序入库参数数据。</param>
/// <param name="htParams">附近参数数据。</param>
private void LotToWarehouse(DbTransaction dbTran, DataRow drLotInfo, DataTable dtOperationParam, Hashtable htParams)
{
string lotKey = Convert.ToString(drLotInfo[POR_LOT_FIELDS.FIELD_LOT_KEY]);
Hashtable htTransaction = new Hashtable();
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_TO_WAREHOUSE);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, null);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, htParams[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, htParams[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME, drLotInfo[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EQUIPMENT_KEY, htParams[WIP_TRANSACTION_FIELDS.FIELD_EQUIPMENT_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, htParams[WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, htParams[WIP_TRANSACTION_FIELDS.FIELD_OPERATOR]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, htParams[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE_PRE, drLotInfo[POR_LOT_FIELDS.FIELD_LINE_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_LOT_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, 0);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, drLotInfo[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, drLotInfo[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, drLotInfo[POR_LOT_FIELDS.FIELD_IS_REWORKED]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME, drLotInfo[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, htParams[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, 9);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME, drLotInfo[POR_ROUTE_STEP_FIELDS.FIELD_ROUTE_STEP_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TO_STEP_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TIME_STAMP, null);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, drLotInfo[POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
string transactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, transactionKey, lotKey);
//向WIP_TRANSACTION表插入批次入库的操作记录。
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
string sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//工步采集参数数据。
//如果数据集中包含名称WIP_PARAM_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dtOperationParam != null)
{
WIP_PARAM_FIELDS wipParamFields = new WIP_PARAM_FIELDS();
//遍历批次的工步采集参数数据。
for (int i = 0; i < dtOperationParam.Rows.Count; i++)
{
DataRow drWIPParam = dtOperationParam.Rows[i];
Hashtable htWIPParam = CommonUtils.ConvertRowToHashtable(drWIPParam);
if (!htWIPParam.ContainsKey(WIP_PARAM_FIELDS.FIELD_TRANSACTION_KEY))
{
htWIPParam.Add(WIP_PARAM_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htWIPParam[WIP_PARAM_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//插入一笔工序参数数据。
sql = DatabaseTable.BuildInsertSqlStatement(wipParamFields, htWIPParam, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
}
//更新批次状态,11-已入库
sql = string.Format(@"UPDATE POR_LOT SET STATE_FLAG=11,EDIT_TIME=GETDATE()
WHERE LOT_KEY='{0}'",
lotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
/// <summary>
/// 执行拆分批次。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbtran">数据库事务操作对象。</param>
/// <param name="dataset">包含拆分批次数据的数据集。</param>
/// <param name="dsReturn">包含执行结果的数据集。</param>
/// <returns></returns>
internal static int SplitLotTransact(Database db, DbTransaction dbtran, DataSet dsParams, ref DataSet dsReturn)
{
String sql = string.Empty;
DataSet ds;
string strParentLotKey = "";
string strParentLotNumber = "";
string strParentSEQ = "";
string strParentTransKey = "";
string strParentStepKey = "";
string strParentRouteKey = "";
string strParentEnterpriseKey = "";
string strParentQTY = "";
string strRemnantQTY = "";
string strComment = "";
string strEditor = "", strEditTimeZone = "", strEditTime = "";
string strLineKey = "", strShiftName = "", strWorkOrderKey = "", strParentStateFlag = "";
string oprLine = string.Empty;
string reworkFlag = string.Empty;
int splitQuantitySum = 0;
int intSEQ = 0;
int workOrderStep = 0;
if (dsParams.Tables.Count > 0)
{
//获取参数输入。
strEditTime = UtilHelper.GetSysdate(db).ToString("yyyy-MM-dd HH:mm:ss");
DataTable dataTable = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable);
strParentLotKey = hashData[POR_LOT_FIELDS.FIELD_LOT_KEY].ToString();
strRemnantQTY = hashData[POR_LOT_FIELDS.FIELD_QUANTITY].ToString();
strComment = hashData[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT].ToString();
strEditor = hashData[WIP_TRANSACTION_FIELDS.FIELD_EDITOR].ToString();
strEditTimeZone = hashData[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY].ToString();
strParentQTY = hashData[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN].ToString();
string shiftKey = Convert.ToString(hashData[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY]);
string operComputerName = Convert.ToString(hashData[WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER]);
if (hashData.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME))
{
strShiftName = hashData[WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME].ToString();
}
else
{
strShiftName = UtilHelper.GetShiftValue(db, strEditTime);
}
if (string.IsNullOrEmpty(shiftKey))
{
shiftKey = UtilHelper.GetShiftKey(db, strEditTime);
}
if (hashData.Contains(POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY))
{
strParentStepKey = hashData[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString();
}
if (hashData.Contains(POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY))
{
strParentRouteKey = hashData[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY].ToString();
}
if (hashData.Contains(POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY))
{
strParentEnterpriseKey = hashData[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString();
}
if (hashData.Contains(POR_LOT_FIELDS.FIELD_STATE_FLAG))
{
strParentStateFlag = hashData[POR_LOT_FIELDS.FIELD_STATE_FLAG].ToString();
}
if (hashData.ContainsKey(COMMON_FIELDS.FIELD_WORK_ORDER_STEP))
{
workOrderStep = Convert.ToInt32(hashData[COMMON_FIELDS.FIELD_WORK_ORDER_STEP]);
}
//获取被拆分批次信息。
ds = LotManagement.GetLotDetailsEx(db, dbtran, strParentLotKey);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
strParentSEQ = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_SEQ].ToString();
strParentLotNumber = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_LOT_NUMBER].ToString();
strWorkOrderKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
strLineKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY].ToString();
oprLine = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_OPR_LINE].ToString();
reworkFlag = Convert.ToString(ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_IS_REWORKED]);
if (strParentStepKey == "" || strParentRouteKey == "" || strParentEnterpriseKey == "" || strParentStateFlag == "")
{
strParentStepKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString();
strParentRouteKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY].ToString();
strParentEnterpriseKey = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString();
strParentStateFlag = ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_STATE_FLAG].ToString();
}
}
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
//更新批次信息
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY='{0}',EDITOR='{1}',EDIT_TIME= GETDATE(),EDIT_TIMEZONE='{2}' ",
strRemnantQTY.PreventSQLInjection(),
strEditor.PreventSQLInjection(),
strEditTimeZone.PreventSQLInjection());
//如果数量为0终止批次。
if (strRemnantQTY == "0")
{
sql += ",DELETED_TERM_FLAG=1 ";
}
sql += string.Format("WHERE LOT_KEY='{0}'", strParentLotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#region 记录批次拆分操作数据
Hashtable parenttransactionTable = new Hashtable();
DataTable parenttransaction = new DataTable();
strParentTransKey = UtilHelper.GenerateNewKey(0);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, strParentLotKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strParentQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, strRemnantQTY);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_SPLIT);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, strComment);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, null);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strParentStateFlag);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, reworkFlag);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, strEditor);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, shiftKey);
parenttransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, operComputerName);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, parenttransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
#region 创建子批次。
string strChildLotKey = "";
string strChildLotNumber = "";
string strChildQty = "";
string strChildLotSEQ = "";
//int intSEQ = 0;
int intCount = 0;
if (dsParams.Tables.Contains(BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dtReturn = new DataTable();
dtReturn.Columns.Add("ITEM_SEQ", Type.GetType("System.String"));
dtReturn.Columns.Add("LOT_NUMBER", Type.GetType("System.String"));
dtReturn.Columns.Add("QUANTITY", Type.GetType("System.String"));
dtReturn.Columns.Add("LOT_KEY", Type.GetType("System.String"));
intCount = strParentSEQ.Length;
//get exist max seq
sql = string.Format(@"SELECT MAX(A.WORK_ORDER_SEQ) AS WORK_ORDER_SEQ,
SUBSTRING(MAX(A.WORK_ORDER_SEQ),{0},2) AS WORK_ORDER_STEP,
LEN(MAX(A.WORK_ORDER_SEQ)),A.WORK_ORDER_KEY
FROM POR_LOT A
WHERE A.WORK_ORDER_KEY = '{1}'
AND A.WORK_ORDER_SEQ LIKE '{2}.%'
GROUP BY A.WORK_ORDER_KEY",
(intCount + 2),
strWorkOrderKey.PreventSQLInjection(),
strParentSEQ.PreventSQLInjection());
using (IDataReader readerSEQ = db.ExecuteReader(dbtran, CommandType.Text, sql))
{
if (readerSEQ.Read())
{
if (readerSEQ["WORK_ORDER_STEP"].ToString() != "")
{
intSEQ = Int32.Parse(readerSEQ["WORK_ORDER_STEP"].ToString());
}
}
readerSEQ.Close();
}
if (workOrderStep > intSEQ)
{
intSEQ = workOrderStep;
}
DataTable parameterdatatable = dsParams.Tables[BASE_PARAMETER_FIELDS.DATABASE_TABLE_NAME];
for (int i = 0; i < parameterdatatable.Rows.Count; i++)
{
//get form collect data
strChildLotKey = UtilHelper.GenerateNewKey(0);
strChildLotNumber = GetChildLotNumber(db, dbtran, strParentLotNumber, i + 1);
strChildQty = parameterdatatable.Rows[i]["QUANTITY"].ToString();
strChildLotSEQ = parameterdatatable.Rows[i]["SPLIT_SEQ"].ToString();
dtReturn.Rows.Add(strChildLotSEQ, strChildLotNumber, strChildQty, strChildLotKey);
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_LOT_KEY] = strChildLotKey;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_LOT_NUMBER] = strChildLotNumber;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY] = strChildQty;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_EDIT_TIME] = strEditTime;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CREATE_TIME] = strEditTime;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_START_WAIT_TIME] = strEditTime;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_STATE_FLAG] = strParentStateFlag;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY] = strParentEnterpriseKey;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY] = strParentRouteKey;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY] = strParentStepKey;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_IS_MAIN_LOT] = 0;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_OPR_COMPUTER] = operComputerName;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CREATOR] = strEditor;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_EDITOR] = strEditor;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_EDIT_TIMEZONE] = strEditTimeZone;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_CREATE_TIMEZONE_KEY] = strEditTimeZone;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_IS_PRINT] = "0";
if (intSEQ == 0)
{
//new SEQ
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_SEQ] = strParentSEQ + "." + strChildLotSEQ;
}
else
{
//add SEQ
intSEQ = intSEQ + 1;
ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_SEQ] = strParentSEQ + "." + intSEQ.ToString("00");
}
//Create chilid Lot
if (ds.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
ds.Tables.Remove(TRANS_TABLES.TABLE_MAIN_DATA);
}
#region Create Lot & Create Udas for the lot
Hashtable hashDataOfLot = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertRowToHashtable(ds.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME].Rows[0]);
//initialize tablefields
POR_LOT_FIELDS porLotFields = new POR_LOT_FIELDS();
POR_LOT_ATTR_FIELDS lotUda = new POR_LOT_ATTR_FIELDS();
//get sql
sql = DatabaseTable.BuildInsertSqlStatement(porLotFields, hashDataOfLot, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//create udas for the child lot
if (ds.Tables.Contains(POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME))
{
List <string> sqlCommandList = new List <string>();
foreach (DataRow row in ds.Tables[POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME].Rows)
{
row[POR_LOT_ATTR_FIELDS.FIELD_LOT_KEY] = strChildLotKey;
}
DatabaseTable.BuildInsertSqlStatements(ref sqlCommandList,
new POR_LOT_ATTR_FIELDS(),
ds.Tables[POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME],
new Dictionary <string, string>()
{
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIME, null },
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIMEZONE, "CN-ZH" }
},
new List <string>()
{
BASE_ATTRIBUTE_FIELDS.FIELDS_DATA_TYPE
});
foreach (string sqlU in sqlCommandList)
{
db.ExecuteNonQuery(dbtran, CommandType.Text, sqlU);
}
}
#endregion
#region Insert WipTransaction
//insert Wip transaction
Hashtable childtransactionTable = new Hashtable();
DataTable childtransaction = new DataTable();
string strChildTransKey = UtilHelper.GenerateNewKey(0);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, strChildLotKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, strChildQty);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, strChildQty);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CREATELOT);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "");
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, strEditor);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, null);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, strEditTimeZone);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, strLineKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, strWorkOrderKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, strParentStepKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, strParentStateFlag);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, reworkFlag);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, strEditor);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, strShiftName);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, shiftKey);
childtransactionTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, operComputerName);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, childtransactionTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
//insert wip split
Hashtable splitHashTable = new Hashtable();
DataTable splitTable = new DataTable();
WIP_SPLIT_FIELDS wipSplit = new WIP_SPLIT_FIELDS();
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_TRANSACTION_KEY, strParentTransKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_CHILD_LOT_KEY, strChildLotKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_CHILD_LOT_TRANSACTION_KEY, strChildTransKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_SPLIT_QUANTITY, strChildQty);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_STEP_KEY, strParentStepKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_ROUTE_KEY, strParentRouteKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_ENTERPRISE_KEY, strParentEnterpriseKey);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_EDITOR, strEditor);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_EDIT_TIME, null);
splitHashTable.Add(WIP_SPLIT_FIELDS.FIELD_EDIT_TIMEZONE, strEditTimeZone);
sql = DatabaseTable.BuildInsertSqlStatement(wipSplit, splitHashTable, null);
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
#endregion
}
dtReturn.TableName = "CHILDLOT_DATA";
dsReturn.Merge(dtReturn, false, MissingSchemaAction.Add);
}
#endregion
#region 获取批次当前加工的设备数据
sql = string.Format(@"SELECT EQUIPMENT_KEY,LOT_EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE LOT_KEY='{0}' AND STEP_KEY='{1}' AND END_TIMESTAMP IS NULL",
strParentLotKey.PreventSQLInjection(),
strParentStepKey.PreventSQLInjection());
DataSet dsLotEquipment = db.ExecuteDataSet(dbtran, CommandType.Text, sql);
if (dsLotEquipment.Tables[0].Rows.Count > 0)
{
string equipmentKey = dsLotEquipment.Tables[0].Rows[0]["EQUIPMENT_KEY"].ToString();
string lotEquipmentKey = dsLotEquipment.Tables[0].Rows[0]["LOT_EQUIPMENT_KEY"].ToString();
if (dsReturn.Tables.Contains("CHILDLOT_DATA"))
{
for (int i = 0; i < dsReturn.Tables["CHILDLOT_DATA"].Rows.Count; i++)
{
string splitLotKey = dsReturn.Tables["CHILDLOT_DATA"].Rows[i]["LOT_KEY"].ToString();
int splitQuantity = Convert.ToInt32(dsReturn.Tables["CHILDLOT_DATA"].Rows[i]["QUANTITY"]);
splitQuantitySum += splitQuantity;
sql = string.Format(@"INSERT INTO EMS_LOT_EQUIPMENT
(LOT_EQUIPMENT_KEY, LOT_KEY,EQUIPMENT_KEY, START_TIMESTAMP, USER_KEY, QUANTITY,STEP_KEY)
VALUES('{0}','{1}','{2}', GETDATE(), '{3}', {4},'{5}')",
UtilHelper.GenerateNewKey(0),
splitLotKey.PreventSQLInjection(),
equipmentKey.PreventSQLInjection(),
strEditor.PreventSQLInjection(),
splitQuantity,
strParentStepKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
}
}
sql = string.Format(@"UPDATE EMS_LOT_EQUIPMENT
SET QUANTITY=QUANTITY-{0}
WHERE LOT_EQUIPMENT_KEY='{1}'",
splitQuantitySum,
lotEquipmentKey.PreventSQLInjection());
db.ExecuteNonQuery(dbtran, CommandType.Text, sql);
}
#endregion
}
return(intSEQ);
}
/// <summary>
/// 创建批次。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dbTran">数据库事务操作对象。</param>
/// <param name="dsParams">包含批次创建信息的数据集对象。</param>
public static void CreateLot(Database db, DbTransaction dbTran, DataSet dsParams)
{
DataSet dsReturn = new DataSet();
List <string> sqlCommandList = new List <string>();
DateTime curTime = UtilHelper.GetSysdate(db);
string lotEditTime = curTime.ToString("yyyy-MM-dd HH:mm:ss");
if (dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtMainData = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
DataTable dtAddtionData = dsParams.Tables[1]; //存放附加数据
Hashtable htMainData = CommonUtils.ConvertToHashtable(dtMainData);
Hashtable htAddtionData = CommonUtils.ConvertToHashtable(dtAddtionData);
string enterpriseName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME]);
string routeName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME]);
string stepName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME]);
string editor = Convert.ToString(htMainData[POR_LOT_FIELDS.FIELD_CREATOR]);
string editTimezone = string.Empty;
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_EDIT_TIMEZONE))
{
editTimezone = Convert.ToString(htMainData[POR_LOT_FIELDS.FIELD_EDIT_TIMEZONE]);
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_EDITOR) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_EDITOR, editor);
}
htMainData[POR_LOT_FIELDS.FIELD_LOT_KEY] = UtilHelper.GenerateNewKey(0);
htMainData[POR_LOT_FIELDS.FIELD_EDIT_TIME] = null;
htMainData[POR_LOT_FIELDS.FIELD_CREATE_TIME] = null;
htMainData[POR_LOT_FIELDS.FIELD_START_WAIT_TIME] = null;
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO, htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO]);
}
else
{
htMainData[POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO] = htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO];
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_STATE_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_STATE_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_REWORKED) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_REWORKED, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_MAIN_LOT) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_MAIN_LOT, "1");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_HOLD_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_HOLD_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_SPLITED) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_SPLITED, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG, "0");
}
//创建批次。
POR_LOT_FIELDS porLotFields = new POR_LOT_FIELDS();
string sql = DatabaseTable.BuildInsertSqlStatement(porLotFields, htMainData, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
#region 插入操作记录
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable htTransaction = new Hashtable();
DataTable dtTransaction = new DataTable();
string strChildTransKey = UtilHelper.GenerateNewKey(0);
string strShiftKey = UtilHelper.GetShiftKey(db, curTime.ToString("yyyy-MM-dd HH:mm:ss"));
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, htMainData[POR_LOT_FIELDS.FIELD_LOT_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, htMainData[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, htMainData[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CREATELOT);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, htMainData[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, string.Empty);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, htMainData[POR_LOT_FIELDS.FIELD_OPR_COMPUTER]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, strShiftKey);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, htMainData[POR_LOT_FIELDS.FIELD_SHIFT_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, htMainData[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, htMainData[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, htMainData[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME, stepName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME, routeName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME, enterpriseName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, curTime);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, editTimezone);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, editor);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, editor);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, htMainData[POR_LOT_FIELDS.FIELD_IS_REWORKED]);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
#endregion
if (dsParams.Tables.Contains(POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME))
{
DatabaseTable.BuildInsertSqlStatements(ref sqlCommandList,
new POR_LOT_ATTR_FIELDS(),
dsParams.Tables[POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME],
new Dictionary <string, string>()
{
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIME, null },
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIMEZONE, "CN-ZH" }
},
new List <string>()
{
COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION, BASE_ATTRIBUTE_FIELDS.FIELDS_DATA_TYPE
});
}
foreach (string s in sqlCommandList)
{
db.ExecuteNonQuery(dbTran, CommandType.Text, s);
}
}
}
/// <summary>
/// 保存数据采集实例数据。
/// </summary>
/// <param name="dbTrans">事务操作对象。</param>
/// <param name="lotKey">批次主键。</param>
/// <param name="edcPointKey">抽检点设置主键。</param>
/// <param name="editor">编辑人。</param>
/// <param name="equipmentKey">设备主键。</param>
/// <param name="oprLine">操作线别名称。</param>
/// <param name="shiftKey">班别主键。</param>
internal static void SaveEdcMainInfo(Database db, DbTransaction dbTrans, string lotKey, string edcPointKey,
string editor, string equipmentKey, string oprLine, string shiftKey)
{
string activityType = string.Empty;
string transactionState = string.Empty;
string sql = string.Empty;
List <string> sqlCommandList = new List <string>();
DataTable lotTable = GetLotsInfo(db, dbTrans, lotKey).Tables[0];
string lotNumber = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_LOT_NUMBER].ToString();
string enterpriseKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY].ToString();
string routeKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY].ToString();
string stepKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString();
string workOrderKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
string partKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_PART_VER_KEY].ToString();
string stateFlag = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_STATE_FLAG].ToString();
string edcName = lotTable.Rows[0][EDC_MAIN_FIELDS.FIELD_EDC_NAME].ToString();
string stepName = lotTable.Rows[0][POR_ROUTE_STEP_FIELDS.FIELD_ROUTE_STEP_NAME].ToString();
string partNo = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_PART_NUMBER].ToString();
string locationKey = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY].ToString();
string matrialLot = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_MATERIAL_LOT].ToString();
string supplier = Convert.ToString(lotTable.Rows[0][POR_LOT_FIELDS.FIELD_SUPPLIER_NAME]);
//string partType = lotTable.Rows[0][POR_LOT_FIELDS.FIELD_TYPE].ToString();
string shiftName = Convert.ToString(lotTable.Rows[0][POR_LOT_FIELDS.FIELD_SHIFT_NAME]);
string reworkFlag = Convert.ToString(lotTable.Rows[0][POR_LOT_FIELDS.FIELD_IS_REWORKED]);
string userName = Convert.ToString(lotTable.Rows[0][POR_LOT_FIELDS.FIELD_OPERATOR]);
string computeName = Convert.ToString(lotTable.Rows[0][POR_LOT_FIELDS.FIELD_OPR_COMPUTER]);
//插入数据采集记录
#region 创建生成EDC_MAIN_INS记录的INSERT SQL
sql = @"SELECT EDC_KEY,SP_KEY FROM EDC_POINT WHERE ROW_KEY='" + edcPointKey.PreventSQLInjection() + "'";
DataTable pointTable = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
string edcInsKey = UtilHelper.GenerateNewKey(0);
Hashtable mainTable = new Hashtable();
EDC_MAIN_INS_FIELDS edcMainField = new EDC_MAIN_INS_FIELDS();
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDC_INS_KEY, edcInsKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDC_KEY, pointTable.Rows[0][EDC_POINT_FIELDS.FIELD_EDC_KEY].ToString());
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDC_SP_KEY, pointTable.Rows[0][EDC_POINT_FIELDS.FIELD_SP_KEY].ToString());
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_ENTERPRISE_KEY, enterpriseKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_ROUTE_KEY, routeKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_STEP_KEY, stepKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_WORK_ORDER_KEY, workOrderKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_LOT_KEY, lotKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_LOT_NUMBER, lotNumber);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_PART_KEY, partKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDC_POINT_KEY, edcPointKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EQUIPMENT_KEY, equipmentKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_COL_START_TIME, null);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_SUPPLIER, supplier);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_STEP_NAME, stepName);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_PART_NO, partNo);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_MATERIAL_LOT, matrialLot);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_LOCATION_KEY, locationKey);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDC_NAME, edcName);
//mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_PART_TYPE, partType);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDITOR, editor);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_CREATOR, editor);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_CREATE_TIME, null);
mainTable.Add(EDC_MAIN_INS_FIELDS.FIELD_EDIT_TIME, null);
sql = DatabaseTable.BuildInsertSqlStatement(edcMainField, mainTable, null);
sqlCommandList.Add(sql);
#endregion
if (sqlCommandList.Count > 0)
{
foreach (string sqlCommand in sqlCommandList)
{
db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlCommand);
}
}
//更新批次状态。
if (Convert.ToInt32(stateFlag) == 0)//等待进站数据采集。
{
transactionState = "1";
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_INEDC;
//更新为进站数据采集。
sql = string.Format(@"UPDATE POR_LOT
SET STATE_FLAG=1,EDITOR='{0}',EDIT_TIME=GETDATE(),EDC_INS_KEY='{1}'
WHERE LOT_KEY='{2}'",
editor.PreventSQLInjection(),
edcInsKey.PreventSQLInjection(),
lotKey.PreventSQLInjection());
}
else if (Convert.ToInt32(stateFlag) == 4)//等待出站数据收集
{
activityType = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_OUTEDC;
transactionState = "5";
//更新为数据采集。
sql = string.Format(@"UPDATE POR_LOT
SET STATE_FLAG=5,EDITOR='{0}',EDIT_TIME=GETDATE(),EDC_INS_KEY='{1}'
WHERE LOT_KEY='{2}'",
editor.PreventSQLInjection(),
edcInsKey.PreventSQLInjection(),
lotKey.PreventSQLInjection());
}
db.ExecuteNonQuery(dbTrans, CommandType.Text, sql);
#region 插入操作历史记录
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable hashTable = new Hashtable();
string strTransactionKey = UtilHelper.GenerateNewKey(0);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strTransactionKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, lotKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, activityType);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, lotTable.Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString());
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, lotTable.Rows[0][POR_LOT_FIELDS.FIELD_QUANTITY].ToString());
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, "EDC");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE, oprLine);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, editor);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, "CN-ZH");
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, null);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, transactionState);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY, lotTable.Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY].ToString());
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, stepKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, routeKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, enterpriseKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, workOrderKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EDC_INS_KEY, edcInsKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_EQUIPMENT_KEY, equipmentKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, shiftName);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, shiftKey);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, reworkFlag);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, userName);
hashTable.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, computeName);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, hashTable, null);
db.ExecuteNonQuery(dbTrans, CommandType.Text, sql);
#endregion
}
/// <summary>
/// 电池片补片操作。
/// </summary>
/// <remarks>
/// 操作名称：<see cref="ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_PATCH"/>。
/// </remarks>
/// <param name="dsParams">包含补片信息的数据集对象。</param>
/// <returns>包含结果数据的数据集对象。</returns>
public DataSet LotPatch(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
DbConnection dbConn = null;
DbTransaction dbTran = null;
try
{
dbConn = db.CreateConnection();
dbConn.Open();
dbTran = dbConn.BeginTransaction();
//参数数据。
if (dsParams == null ||
!dsParams.Tables.Contains(TRANS_TABLES.TABLE_PARAM) || //存放附加参数数据
!dsParams.Tables.Contains(WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME)) //存放操作数据
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数不正确，请检查。");
return(dsReturn);
}
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_PARAM]; //存放附加参数数据
DataTable dtTransaction = dsParams.Tables[WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME]; //存放操作数据
Hashtable htTransaction = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtTransaction);
Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
string opEditTime = Convert.ToString(htParams[COMMON_FIELDS.FIELD_COMMON_EDIT_TIME]); //操作时编辑时间
string lotKey = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY]);
string editTimeZone = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY]);
string editor = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDITOR]);
double leftQty = Convert.ToDouble(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT]);
string activity = Convert.ToString(htTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY]);
//操作动作必须是 PATCH
if (activity != ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_PATCH)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, "传入参数的补片操作动作不正确，请检查。");
return(dsReturn);
}
//如果数据集中包含名称WIP_PATCH_FIELDS.DATABASE_TABLE_NAME的数据表对象。
if (dsParams.Tables.Contains(WIP_PATCH_FIELDS.DATABASE_TABLE_NAME))
{
string transactionKey = UtilHelper.GenerateNewKey(0);
string sql = string.Empty;
double sumPatchedQty = 0;
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
WIP_PATCH_FIELDS patchFields = new WIP_PATCH_FIELDS();
DataTable dtPatch = dsParams.Tables[WIP_PATCH_FIELDS.DATABASE_TABLE_NAME]; //存放补片明细数据
//遍历批次的报废数据。
for (int i = 0; i < dtPatch.Rows.Count; i++)
{
DataRow drPatch = dtPatch.Rows[i];
Hashtable htPatch = CommonUtils.ConvertRowToHashtable(drPatch);
string patchedLotKey = Convert.ToString(htPatch[WIP_PATCH_FIELDS.FIELD_PATCHED_LOT_KEY]);
double patchedQuantity = Convert.ToDouble(htPatch[WIP_PATCH_FIELDS.FIELD_PATCH_QUANTITY]);
sumPatchedQty += patchedQuantity; //总的补片数量。
int isOnlyPatch = Convert.ToInt32(htPatch[WIP_PATCH_FIELDS.FIELD_IS_ONLY_PATCHED]);
//获取被补片批次的信息
DataSet dsPatchLotInfo = LotManagement.GetLotBasicInfo(db, dbTran, patchedLotKey);
DataRow drPatchLotInfo = dsPatchLotInfo.Tables[0].Rows[0];
double quantityIn = Convert.ToDouble(drPatchLotInfo[POR_LOT_FIELDS.FIELD_QUANTITY]);
//isOnlyPatch=0 先报废后补片，所以数量不需要改变。isOnlyPatch=1 仅做补片。
double quantityOut = isOnlyPatch == 0? quantityIn: quantityIn + patchedQuantity;
string enterpriseKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
string enterpriseName = Convert.ToString(drPatchLotInfo[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME]);
string routeKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
string routeName = Convert.ToString(drPatchLotInfo[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME]);
string stepKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
string stepName = Convert.ToString(drPatchLotInfo[POR_ROUTE_STEP_FIELDS.FIELD_ROUTE_STEP_NAME]);
string workOrderKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
string stateFlag = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
string reworkFlag = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_IS_REWORKED]);
string lineKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY]);
string lineName = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_OPR_LINE]);
string edcInsKey = Convert.ToString(drPatchLotInfo[POR_LOT_FIELDS.FIELD_EDC_INS_KEY]);
string equipmentKey = Convert.ToString(drPatchLotInfo[EMS_EQUIPMENTS_FIELDS.FIELD_EQUIPMENT_KEY]);
string patchedTransactionKey = UtilHelper.GenerateNewKey(0);
AddWIPLot(dbTran, patchedTransactionKey, patchedLotKey);
//更新被补片批次的数量
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}'
WHERE LOT_KEY='{3}'",
quantityOut,
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection(),
patchedLotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
Hashtable htPatchedTransaction = new Hashtable(htTransaction);
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY] = patchedLotKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY] = enterpriseKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME] = enterpriseName;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY] = routeKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME] = routeName;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY] = stepKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME] = stepName;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY] = workOrderKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG] = stateFlag;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG] = reworkFlag;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_LINE_KEY] = lineKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE] = lineName;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_OPR_LINE_PRE] = lineName;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_EDC_INS_KEY] = edcInsKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_EQUIPMENT_KEY] = equipmentKey;
//插入报废记录。
//isOnlyPatch=0 先报废后补片。isOnlyPatch=1 仅做补片。
if (isOnlyPatch == 0)
{
string scrapTransaction = UtilHelper.GenerateNewKey(0);
DataSet dsScrapParams = new DataSet();
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN] = quantityIn;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT] = quantityIn - patchedQuantity;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = scrapTransaction;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY] = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CELLSCRAP;
DataTable dtScrapTransaction = CommonUtils.ParseToDataTable(htPatchedTransaction);
dtScrapTransaction.TableName = WIP_TRANSACTION_FIELDS.DATABASE_TABLE_NAME;
dsScrapParams.Tables.Add(dtScrapTransaction);
DataTable dtScrap = CommonUtils.CreateDataTable(new WIP_SCRAP_FIELDS());
DataRow drScrap = dtScrap.NewRow();
drScrap[WIP_SCRAP_FIELDS.FIELD_DESCRIPTION] = drPatch[WIP_PATCH_FIELDS.FIELD_DESCRIPTION];
drScrap[WIP_SCRAP_FIELDS.FIELD_EDIT_TIME] = drPatch[WIP_PATCH_FIELDS.FIELD_EDIT_TIME];
drScrap[WIP_SCRAP_FIELDS.FIELD_EDIT_TIMEZONE_KEY] = drPatch[WIP_PATCH_FIELDS.FIELD_EDIT_TIMEZONE_KEY];
drScrap[WIP_SCRAP_FIELDS.FIELD_EDITOR] = drPatch[WIP_PATCH_FIELDS.FIELD_EDITOR];
drScrap[WIP_SCRAP_FIELDS.FIELD_ENTERPRISE_KEY] = drPatch[WIP_PATCH_FIELDS.FIELD_ENTERPRISE_KEY];
drScrap[WIP_SCRAP_FIELDS.FIELD_ENTERPRISE_NAME] = drPatch[WIP_PATCH_FIELDS.FIELD_ENTERPRISE_NAME];
drScrap[WIP_SCRAP_FIELDS.FIELD_REASON_CODE_CLASS] = drPatch[WIP_PATCH_FIELDS.FIELD_REASON_CODE_CLASS];
drScrap[WIP_SCRAP_FIELDS.FIELD_REASON_CODE_KEY] = drPatch[WIP_PATCH_FIELDS.FIELD_REASON_CODE_KEY];
drScrap[WIP_SCRAP_FIELDS.FIELD_REASON_CODE_NAME] = drPatch[WIP_PATCH_FIELDS.FIELD_REASON_CODE_NAME];
drScrap[WIP_SCRAP_FIELDS.FIELD_RESPONSIBLE_PERSON] = drPatch[WIP_PATCH_FIELDS.FIELD_RESPONSIBLE_PERSON];
drScrap[WIP_SCRAP_FIELDS.FIELD_ROUTE_KEY] = drPatch[WIP_PATCH_FIELDS.FIELD_ROUTE_KEY];
drScrap[WIP_SCRAP_FIELDS.FIELD_ROUTE_NAME] = drPatch[WIP_PATCH_FIELDS.FIELD_ROUTE_NAME];
drScrap[WIP_SCRAP_FIELDS.FIELD_SCRAP_QUANTITY] = drPatch[WIP_PATCH_FIELDS.FIELD_PATCH_QUANTITY];
drScrap[WIP_SCRAP_FIELDS.FIELD_STEP_KEY] = drPatch[WIP_PATCH_FIELDS.FIELD_STEP_KEY];
drScrap[WIP_SCRAP_FIELDS.FIELD_STEP_NAME] = drPatch[WIP_PATCH_FIELDS.FIELD_STEP_NAME];
drScrap[WIP_SCRAP_FIELDS.FIELD_TRANSACTION_KEY] = scrapTransaction;
dtScrap.Rows.Add(drScrap);
dsScrapParams.Tables.Add(dtScrap);
LotScrap(dsScrapParams, dbTran);
}
//插入被补片批次的操作记录
//isOnlyPatch=0 先报废后补片。isOnlyPatch=1 仅做补片。
if (isOnlyPatch == 0)
{
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN] = quantityIn - patchedQuantity;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT] = quantityIn;
}
else
{
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN] = quantityIn;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT] = quantityIn + patchedQuantity;
}
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = patchedTransactionKey;
htPatchedTransaction[WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY] = ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_PATCHED;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htPatchedTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//插入一笔批次补片明细数据
//补片操作记录主键。
if (!htPatch.ContainsKey(WIP_PATCH_FIELDS.FIELD_TRANSACTION_KEY))
{
htPatch.Add(WIP_PATCH_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htPatch[WIP_PATCH_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
//被补片批次操作记录主键
if (!htPatch.ContainsKey(WIP_PATCH_FIELDS.FIELD_PATCHED_TRANSACTION_KEY))
{
htPatch.Add(WIP_PATCH_FIELDS.FIELD_PATCHED_TRANSACTION_KEY, patchedTransactionKey);
}
htPatch[WIP_PATCH_FIELDS.FIELD_PATCHED_TRANSACTION_KEY] = patchedTransactionKey;
//重置补片明细的编辑时间为当前时间。
htPatch[WIP_PATCH_FIELDS.FIELD_EDIT_TIME] = null;
sql = DatabaseTable.BuildInsertSqlStatement(patchFields, htPatch, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
AddWIPLot(dbTran, transactionKey, lotKey);
//获取组件补片批次当前数量
sql = string.Format("SELECT QUANTITY FROM POR_LOT WHERE LOT_KEY='{0}'", lotKey.PreventSQLInjection());
double quantity = Convert.ToDouble(db.ExecuteScalar(CommandType.Text, sql));
if (quantity < sumPatchedQty)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Format("组件补片批次当前实际数量[{0}]小于补片数量[{1}]，请确认。", quantity, sumPatchedQty));
dbTran.Rollback();
return(dsReturn);
}
//更新批次数量。
sql = string.Format(@"UPDATE POR_LOT
SET QUANTITY={0},EDITOR='{1}',EDIT_TIME=GETDATE(),EDIT_TIMEZONE='{2}',DELETED_TERM_FLAG={3}
WHERE LOT_KEY='{4}'",
quantity - sumPatchedQty,
editor.PreventSQLInjection(),
editTimeZone.PreventSQLInjection(),
(quantity - sumPatchedQty) > 0 ? 0 : 1,
lotKey.PreventSQLInjection());
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
//向WIP_TRANSACTION表插入批次补片的操作记录。
if (!htTransaction.ContainsKey(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY))
{
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, transactionKey);
}
htTransaction[WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY] = transactionKey;
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(dbTran, CommandType.Text, sql);
}
dbTran.Commit();
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("LotPatch Error: " + ex.Message);
dbTran.Rollback();
}
finally
{
dbConn.Close();
}
return(dsReturn);
}
/// <summary>
/// 创建批次。
/// </summary>
/// <param name="db">数据库对象。</param>
/// <param name="dsParams">包含批次创建信息的数据集对象。</param>
/// <param name="drLotCell">创批组件对应的。</param>
public static void CreateNewLot(Database db, DataSet dsParams, DataRow drCellData)
{
DataSet dsReturn = new DataSet();
List <string> sqlCommandList = new List <string>();
DateTime curTime = UtilHelper.GetSysdate(db);
string lotEditTime = curTime.ToString("yyyy-MM-dd HH:mm:ss");
if (dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtMainData = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
DataTable dtAddtionData = dsParams.Tables[1]; //存放附加数据
Hashtable htMainData = CommonUtils.ConvertToHashtable(dtMainData);
Hashtable htAddtionData = CommonUtils.ConvertToHashtable(dtAddtionData);
string enterpriseName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME]);
string routeName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME]);
string stepName = Convert.ToString(htAddtionData[WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME]);
string editor = Convert.ToString(htMainData[POR_LOT_FIELDS.FIELD_CREATOR]);
string editTimezone = string.Empty;
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_EDIT_TIMEZONE))
{
editTimezone = Convert.ToString(htMainData[POR_LOT_FIELDS.FIELD_EDIT_TIMEZONE]);
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_EDITOR) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_EDITOR, editor);
}
string lotKey = UtilHelper.GenerateNewKey(0);
htMainData[POR_LOT_FIELDS.FIELD_LOT_KEY] = lotKey;
htMainData[POR_LOT_FIELDS.FIELD_EDIT_TIME] = null;
htMainData[POR_LOT_FIELDS.FIELD_CREATE_TIME] = null;
htMainData[POR_LOT_FIELDS.FIELD_START_WAIT_TIME] = null;
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO, htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO]);
}
else
{
htMainData[POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO] = htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO];
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_STATE_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_STATE_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_REWORKED) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_REWORKED, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_MAIN_LOT) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_MAIN_LOT, "1");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_HOLD_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_HOLD_FLAG, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_IS_SPLITED) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_IS_SPLITED, "0");
}
if (htMainData.ContainsKey(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG) == false)
{
htMainData.Add(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG, "0");
}
//创建批次。
POR_LOT_FIELDS porLotFields = new POR_LOT_FIELDS();
string sql = DatabaseTable.BuildInsertSqlStatement(porLotFields, htMainData, null);
db.ExecuteNonQuery(CommandType.Text, sql);
#region 插入组件片对应电池片信息
string sqlCommond = string.Empty;
sqlCommond = string.Format(@"SELECT LOT_KEY FROM POR_LOT_CELL_PARAM
WHERE LOT_NUMBER = '{0}'
AND IS_USED = 'Y'", drCellData["LOT_NUMBER"].ToString());
DataTable dtCellReturn = db.ExecuteDataSet(CommandType.Text, sqlCommond).Tables[0];
if (dtCellReturn.Rows.Count > 0)
{
sqlCommond = string.Format(@"UPDATE POR_LOT_CELL_PARAM
SET IS_USED = 'N'
WHERE LOT_KEY = '{0}'", dtCellReturn.Rows[0]["LOT_KEY"].ToString());
db.ExecuteNonQuery(CommandType.Text, sqlCommond);
}
sqlCommond = string.Format(@"INSERT INTO POR_LOT_CELL_PARAM ( [LOT_KEY]
,[LOT_NUMBER]
,[CELLLOT1]
,[CELLPN1]
,[CELLLOT2]
,[CELLPN2]
,[PACKAGEQTY]
,[CELLSUPPLIER]
,[CELLFACTORY]
,[CELLLINE]
,[CELLCOLOR]
,[CELLEFFICIENCY]
,[CELLPOWER]
,[SMALL_PACK_NUMBER]
,[IS_USED]
,[CREATOR]
,[CREATE_TIME]
,[CREATE_TIMEZONE]
,[EDITOR]
,[EDIT_TIME]
,[EDIT_TIMEZONE]
,[ETL_FLAG])
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',
'{12}','{13}','Y','{14}',SYSDATETIME(),'{15}','{16}',SYSDATETIME(),'{17}','Y')",
lotKey,
drCellData["LOT_NUMBER"].ToString(),
drCellData["CELLLOT1"].ToString(),
drCellData["CELLPN1"].ToString(),
drCellData["CELLLOT2"].ToString(),
drCellData["CELLPN2"].ToString(),
drCellData["PACKAGEQTY"].ToString(),
drCellData["CELLSUPPLIER"].ToString(),
drCellData["CELLFACTORY"].ToString(),
drCellData["CELLLINE"].ToString(),
drCellData["CELLCOLOR"].ToString(),
drCellData["CELLEFFICIENCY"].ToString(),
drCellData["CELLPOWER"].ToString(),
drCellData["SMALL_PACK_NUMBER"].ToString(),
editor,
editTimezone,
editor,
editTimezone
);
db.ExecuteNonQuery(CommandType.Text, sqlCommond);
#endregion
#region 插入操作记录
WIP_TRANSACTION_FIELDS wipFields = new WIP_TRANSACTION_FIELDS();
Hashtable htTransaction = new Hashtable();
DataTable dtTransaction = new DataTable();
string strChildTransKey = UtilHelper.GenerateNewKey(0);
string strShiftKey = UtilHelper.GetShiftKey(db, curTime.ToString("yyyy-MM-dd HH:mm:ss"));
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_TRANSACTION_KEY, strChildTransKey);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_TYPE, "0");
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_PIECE_KEY, htMainData[POR_LOT_FIELDS.FIELD_LOT_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_IN, htMainData[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_QUANTITY_OUT, htMainData[POR_LOT_FIELDS.FIELD_QUANTITY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY, ACTIVITY_FIELD_VALUES.FIELD_ACTIVITY_CREATELOT);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STATE_FLAG, htMainData[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ACTIVITY_COMMENT, string.Empty);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPR_COMPUTER, htMainData[POR_LOT_FIELDS.FIELD_OPR_COMPUTER]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_KEY, strShiftKey);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_SHIFT_NAME, htMainData[POR_LOT_FIELDS.FIELD_SHIFT_NAME]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_WORK_ORDER_KEY, htMainData[POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_KEY, htMainData[POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_KEY, htMainData[POR_LOT_FIELDS.FIELD_CUR_ROUTE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_KEY, htMainData[POR_LOT_FIELDS.FIELD_ROUTE_ENTERPRISE_VER_KEY]);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_STEP_NAME, stepName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ROUTE_NAME, routeName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_ENTERPRISE_NAME, enterpriseName);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIME, curTime);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDIT_TIMEZONE_KEY, editTimezone);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_EDITOR, editor);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_OPERATOR, editor);
htTransaction.Add(WIP_TRANSACTION_FIELDS.FIELD_REWORK_FLAG, htMainData[POR_LOT_FIELDS.FIELD_IS_REWORKED]);
sql = DatabaseTable.BuildInsertSqlStatement(wipFields, htTransaction, null);
db.ExecuteNonQuery(CommandType.Text, sql);
#endregion
if (dsParams.Tables.Contains(POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME))
{
DatabaseTable.BuildInsertSqlStatements(ref sqlCommandList,
new POR_LOT_ATTR_FIELDS(),
dsParams.Tables[POR_LOT_ATTR_FIELDS.DATABASE_TABLE_NAME],
new Dictionary <string, string>()
{
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIME, null },
{ COMMON_FIELDS.FIELD_COMMON_EDIT_TIMEZONE, "CN-ZH" }
},
new List <string>()
{
COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION, BASE_ATTRIBUTE_FIELDS.FIELDS_DATA_TYPE
});
}
foreach (string s in sqlCommandList)
{
db.ExecuteNonQuery(CommandType.Text, s);
}
}
}
