/// <summary>
/// 根据合并批次主键获取可被合并的批次数据。
/// </summary>
/// <param name="strParentLotKey">合并批次主键。</param>
/// <returns>包含可被合并的批次数据的数据集对象。</returns>
public DataSet GetLotsForMerge(string strParentLotKey)
{
DataSet dsReturn = new DataSet();
DataSet ds = new DataSet();
string strParentLot = strParentLotKey;
string strWorkOrderKey = "", strStepKey = "", strLineKey = "";
string strStateFlag = "", strReworkFlag = "";
try
{
ds = LotManagement.GetLotBasicInfo(db, strParentLot);
if (ds.Tables[0].Rows.Count > 0)
{
strWorkOrderKey = ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_WORK_ORDER_KEY].ToString();
strStepKey = ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_STEP_VER_KEY].ToString();
strLineKey = ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_CUR_PRODUCTION_LINE_KEY].ToString();
strStateFlag = ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_STATE_FLAG].ToString();
strReworkFlag = ds.Tables[0].Rows[0][POR_LOT_FIELDS.FIELD_IS_REWORKED].ToString();
}
string sql = string.Format(@"SELECT '' AS CHECK_BOX,A.LOT_KEY,A.LOT_NUMBER,A.LINE_NAME,A.WORK_ORDER_SEQ,
A.QUANTITY,A.HOLD_FLAG,A.STATUS,A.STATE_FLAG,
A.CUR_PRODUCTION_LINE_KEY,A.CUR_ROUTE_VER_KEY,
A.CUR_STEP_VER_KEY,A.WORK_ORDER_KEY,
A.ROUTE_ENTERPRISE_VER_KEY,C.ORDER_NUMBER,C.PART_NUMBER,
D.ROUTE_NAME,B.ROUTE_OPERATION_VER_KEY,
B.SCRAP_REASON_CODE_CATEGORY_KEY,B.ROUTE_STEP_NAME,A.EDIT_TIME
FROM POR_WORK_ORDER C,POR_ROUTE_ROUTE_VER D,POR_ROUTE_STEP B,POR_LOT A
WHERE C.WORK_ORDER_KEY=A.WORK_ORDER_KEY
AND ( A.CUR_STEP_VER_KEY = B.ROUTE_STEP_KEY AND A.CUR_ROUTE_VER_KEY = D.ROUTE_ROUTE_VER_KEY)
AND A.STATUS = 1
AND A.QUANTITY != 0
AND A.DELETED_TERM_FLAG = 0
AND A.HOLD_FLAG=0
AND A.LOT_KEY !='{0}'
AND A.WORK_ORDER_KEY = '{1}'
AND A.CUR_STEP_VER_KEY = '{2}'
AND A.STATE_FLAG = {3}",
strParentLotKey.PreventSQLInjection(),
strWorkOrderKey.PreventSQLInjection(),
strStepKey.PreventSQLInjection(),
strStateFlag.PreventSQLInjection());
if (strReworkFlag == "0")
{
sql = sql + string.Format(" AND A.REWORK_FLAG={0}", strReworkFlag.PreventSQLInjection());
}
else
{
sql = sql + " AND A.REWORK_FLAG>0";
}
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetLotsForMerge Error: " + ex.Message);
}
return(dsReturn);
}
/// <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>
/// 执行拆分批次。
/// </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);
}