/// <summary>
/// Get SalesOrder Via Key
/// </summary>
/// <param name="dataset">sale order key</param>
/// <returns>dataset</returns>
public DataSet GetSalesOrderByKey(string orderKey)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sqlCommand
String[] sqlCommand = new String[5];
try
{
if (orderKey != string.Empty)
{
sqlCommand[0] = @"SELECT * FROM POR_SALES_ORDER_ITEM_ATTR
WHERE SALES_ORDER_ITEM_KEY IN
(SELECT DISTINCT SALES_ORDER_ITEM_KEY
FROM POR_SALES_ORDER_ITEM
WHERE SALES_ORDER_KEY = '" + orderKey + "') ORDER BY 1";
DataTable dtTable1 = new DataTable();
Database db = DatabaseFactory.CreateDatabase();
dtTable1 = db.ExecuteDataSet(CommandType.Text, sqlCommand[0]).Tables[0];
dtTable1.TableName = POR_SALES_ORDER_ITEM_ATTR_FIELDS.DATABASE_TABLE_NAME;
dataDs.Merge(dtTable1, false, MissingSchemaAction.Add);
sqlCommand[1] = @"SELECT * FROM POR_SALES_ORDER_ITEM WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1";
DataTable dtTable2 = new DataTable();
dtTable2 = db.ExecuteDataSet(CommandType.Text, sqlCommand[1]).Tables[0];
dtTable2.TableName = POR_SALES_ORDER_ITEM_FIELDS.DATABASE_TABLE_NAME;
dataDs.Merge(dtTable2, false, MissingSchemaAction.Add);
sqlCommand[2] = "SELECT a.*, b." + BASE_ATTRIBUTE_FIELDS.FIELDS_DATA_TYPE +
" FROM " + POR_SALES_ORDER_ATTR_FIELDS.DATABASE_TABLE_NAME + " a," +
" " + BASE_ATTRIBUTE_FIELDS.DATABASE_TABLE_NAME + " b" +
" WHERE a." + POR_SALES_ORDER_ATTR_FIELDS.FIELDS_ATTRIBUTE_KEY + " = b." + BASE_ATTRIBUTE_FIELDS.FIELDS_ATTRIBUTE_KEY +
" AND a." + POR_SALES_ORDER_ATTR_FIELDS.FIELDS_SALES_ORDER_KEY + " = '" + orderKey + "'";
//sqlCommand[2] = @"SELECT * FROM POR_SALES_ORDER_ATTR WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1";
DataTable dtTable3 = new DataTable();
dtTable3 = db.ExecuteDataSet(CommandType.Text, sqlCommand[2]).Tables[0];
dtTable3.TableName = POR_SALES_ORDER_ATTR_FIELDS.DATABASE_TABLE_NAME;
dataDs.Merge(dtTable3, false, MissingSchemaAction.Add);
sqlCommand[3] = @"SELECT * FROM POR_SALES_ORDER WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1";
DataTable dtTable4 = new DataTable();
dtTable4 = db.ExecuteDataSet(CommandType.Text, sqlCommand[3]).Tables[0];
dtTable4.TableName = POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME;
dataDs.Merge(dtTable4, false, MissingSchemaAction.Add);
//add paramter table
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
}
catch (Exception ex)
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("GetSalesOrderByKey Error: " + ex.Message);
}
return(dataDs);
}
/// <summary>
/// 获取工单报工数据。
/// </summary>
/// <param name="dsParams">包含查询条件的数据集对象。</param>
/// <param name="pageNo">第几页。</param>
/// <param name="pageSize">每页行数。</param>
/// <param name="pages">总页数。</param>
/// <param name="records">总记录数。</param>
/// <returns>包含工单数据的数据集对象。</returns>
public DataSet GetWorkOrderWorkFor(DataSet dsParams,
int pageNo, int pageSize, out int pages, out int records)
{
DataSet dsReturn = new DataSet();
pages = 0;
records = 0;
try
{
string sqlString = @"SELECT (P.ACK_DATA + ' ' + P.ACK_TIME) AS ACKDATETIME,P.AUFNR,P.APLFL,P.GMNGA,P.XMNGA,
P.REPORTOR,P.SHIFT_NAME,P.CON_END,P.WERKS,L.LOCATION_NAME
FROM POR_WORK_ORDER_REPORT P,FMM_LOCATION L
WHERE L.LOCATION_KEY = P.ROOM_KEY";
if (pageNo > 0 && pageSize > 0)
{
//查询结果翻页数据
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, dsReturn, POR_WORK_ORDER_REPORT_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, dsReturn, new string[] { POR_WORK_ORDER_REPORT_FIELDS.DATABASE_TABLE_NAME });
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetWorkOrderWorkFor Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet DeleteMaterialCode(string _code)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sql
String[] sqlCommand = new String[5];
try
{
if (_code != string.Empty)
{
using (DbConnection dbConn = db.CreateConnection())
{
//Open Connection
dbConn.Open();
//Create Transaction
DbTransaction dbTran = dbConn.BeginTransaction();
try
{
string sql01 = string.Format("SELECT MAX(MATERIAL_VERSION) FROM POR_MATERIAL WHERE MATERIAL_CODE = '{0}'",
_code);
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());
}
sqlCommand[0] = "UPDATE POR_MATERIAL SET STATUS = 0 ,MATERIAL_VERSION = '" + materialVersion + "' WHERE MATERIAL_CODE = '" + _code + "' AND STATUS = 1";
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]);
//Commit Transaction
dbTran.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
//Rollback Transaction
dbTran.Rollback();
LogService.LogError("DeleteMaterialCode Error: " + ex.Message);
}
finally
{
//Close Connection
dbConn.Close();
}
}
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("DeleteMaterialCode Error: " + ex.Message);
}
return(dataDs);
}
/// <summary>
/// Search SalesOrders
/// </summary>
/// <param name="dataset">sale order info</param>
/// <returns>dataset</returns>
public DataSet SearchSalesOrders(DataSet dataset)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sqlCommand
String[] sqlCommand = new String[5];
try
{
if (dataset != null && dataset.Tables.Contains(POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dataTable = dataset.Tables[POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME];
Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable);
sqlCommand[0] = @"SELECT SALES_ORDER_KEY,
ORDER_NUMBER,
ORDER_STATE,
ORDER_PRIORITY,
ENTERED_TIME,
PROMISED_TIME,
FINISHED_TIME,
SHIPPED_TIME,
CLOSED_TIME,
DESCRIPTIONS
FROM POR_SALES_ORDER
WHERE ORDER_NUMBER LIKE '%" + hashData["ORDER_NUMBER"] + "%'";
if (hashData["ORDER_STATE"] != null)
{
sqlCommand[0] += "AND ORDER_STATE LIKE '%" + hashData["ORDER_STATE"] + "%'";
}
if (hashData["ORDER_PRIORITY"] != null)
{
sqlCommand[0] += "AND ORDER_PRIORITY LIKE '%" + hashData["ORDER_PRIORITY"] + "%'";
}
sqlCommand[0] += "ORDER BY 1";
}
else
{
sqlCommand[0] = "SELECT * FROM POR_SALES_ORDER ORDER BY 1";
}
Database database = DatabaseFactory.CreateDatabase();
DataTable dtTable = database.ExecuteDataSet(CommandType.Text, sqlCommand[0]).Tables[0];
dtTable.TableName = POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME;
dataDs.Merge(dtTable, false, MissingSchemaAction.Add);
//add paramter table
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
catch (Exception ex)
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("SearchSalesOrders Error: " + ex.Message);
}
return(dataDs);
}
/// <summary>
/// 保存退料单数据。
/// </summary>
/// <param name="strRetMatNo">退料单号。</param>
/// <param name="strRetMatDate">退料单日期。</param>
/// <param name="strShift">班别名称。</param>
/// <param name="strOperator">操作人。</param>
/// <param name="strRetMatReason">退料原因。</param>
/// <param name="dtMatLotList">包含退料物料数据的数据表对象。</param>
/// <returns>true:保存成功。false：保存失败。</returns>
public bool Save(string strRetMatNo, string strRetMatDate, string strShift, string strOperator,
string strRetMatReason, DataTable dtMatLotList)
{
bool bltmp = false;
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
try
{
//1.表示退料单的数据表 WST_TL_ZMMLKO
DbCommand dbCom = dbCon.CreateCommand();
dbCom.CommandText = Save1(strRetMatNo, strRetMatDate, strShift, strOperator, strRetMatReason, dtMatLotList);
dbCom.Transaction = dbTrans;
dbCom.ExecuteNonQuery();
//2.表示退料的详细信息，一批一批记录
for (int i = 0; i < dtMatLotList.Rows.Count; i++)
{
dbCom.CommandText = Save2(strRetMatNo, strRetMatDate, strShift, strOperator, strRetMatReason, dtMatLotList.Rows[i]);
dbCom.ExecuteNonQuery();
}
//3.更新数量
for (int i = 0; i < dtMatLotList.Rows.Count; i++)
{
dbCom.CommandText = Save3(strRetMatNo, strRetMatDate, strShift, strOperator, strRetMatReason, dtMatLotList.Rows[i]);
dbCom.ExecuteNonQuery();
}
//4.调用RFC
DataSet dsOut = new DataSet();
DataSet dsIn = Save4(strRetMatNo, strRetMatDate, strShift, strOperator, strRetMatReason, dtMatLotList);
AllCommonFunctions.SAPRemoteFunctionCall("Z_RFC_RET", dsIn, out dsOut);
if (int.Parse(dsOut.Tables[0].Rows[0]["NUMBER"].ToString()) == 0) //MESSAGE
{
dbTrans.Commit();
bltmp = true;
}
else
{
dbTrans.Rollback();
bltmp = false;
}
}
catch (Exception ex)
{
dbTrans.Rollback();
LogService.LogError("WOMaterialReturnEngine Save Error: " + ex.Message);
}
finally
{
dbCon.Close();
}
return(bltmp);
}
/// <summary>
/// Add Uda SalesOrderAttr
/// </summary>
/// <param name="dataset">dataset for add items cloumns and values</param>
/// <returns>dataset</returns>
public DataSet AddUdaSalesOrderAttr(DataSet dataset)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sql
string sql = "";
try
{
if (dataset.Tables.Count > 0)
{
if (dataset.Tables[0].Rows.Count > 0)
{
sql += "INSERT INTO POR_SALES_ORDER_ATTR(";
//repeated check
for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)
{
if (i == 0)
{
sql += dataset.Tables[0].Rows[i][0].ToString();
}
else
{
sql += ", " + dataset.Tables[0].Rows[i][0].ToString();
}
}
sql += ") VALUES (";
for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)
{
if (i == 0)
{
sql += dataset.Tables[0].Rows[i][1];
}
else
{
sql += ", " + dataset.Tables[0].Rows[i][1];
}
}
sql += " )";
//excute insert
dbEngine.ExecuteNonQuery(sql);
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
}
}
catch (Exception ex)
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("AddUdaSalesOrderAttr Error: " + ex.Message);
}
return(dataDs);
}
public DataSet DeleteMatSpecialInf(string _workOrderNum, string _mat, string _paramerTeam)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sql
String[] sqlCommand = new String[5];
try
{
if (_workOrderNum != string.Empty && _mat != string.Empty && _paramerTeam != string.Empty)
{
using (DbConnection dbConn = db.CreateConnection())
{
//Open Connection
dbConn.Open();
//Create Transaction
DbTransaction dbTran = dbConn.BeginTransaction();
try
{
sqlCommand[0] = string.Format(@"UPDATE POR_WORK_ORDER_BOM_EXTENSION SET STATUS = 0 WHERE STATUS = 1 AND ORDER_NUMBER = '{0}' AND MATERIAL_CODE = '{1}' AND MATKL = '{2}'",
_workOrderNum, _mat, _paramerTeam);
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]);
//Commit Transaction
dbTran.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
//Rollback Transaction
dbTran.Rollback();
LogService.LogError("DeleteMatSpecialInf Error: " + ex.Message);
}
finally
{
//Close Connection
dbConn.Close();
}
}
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("DeleteMatSpecialInf Error: " + ex.Message);
}
return(dataDs);
}
/// <summary>
/// 搜索工单信息。可以使用工单号（ORDER_NUMBER），工单状态（ORDER_STATE)和工单优先级(ORDER_PRIORITY)作为搜索条件。
/// </summary>
/// <param name="dataset">
/// 包含查询条件的数据集对象。若设置查询条件，数据集中必须包含数据表名为<see cref="POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME"/>的数据表对象，
/// 数据表对象包含两个列分别为NAME和VALUE。 列NAME存放查询条件名，列VALUE存放查询条件值。
/// 列NAME可以存放如下查询条件：工单号（ORDER_NUMBER）(可选，模糊查询)、
/// 工单状态（ORDER_STATE)(可选，模糊查询）、工单优先级(ORDER_PRIORITY)(可选，模糊查询）。
/// </param>
/// <returns>查询得到的包含工单信息的数据集对象。该数据集对象包含两个数据表对象。
/// 一个名称为<see cref="POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME"/>的数据表对象。
/// 一个名称为paraTable的数据表对象。
/// POR_WORK_ORDER数据表存放查询得到的工单信息。paraTable的数据表对象存放查询的执行结果，paraTable数据表对象中包含两列“ATTRIBUTE_KEY”和“ATTRIBUTE_VALUE”。最多包含两行：“ATTRIBUTE_KEY”列等于“CODE”的行和“ATTRIBUTE_KEY”列等于“MESSAGE”的行。
/// </returns>
public object SearchWorkOrder(DataSet dataSet)
{
DataSet returnDS = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
string sqlCommand = "";
try
{
//查询条件数据集不为null，且包含名称为POR_WORK_ORDER的数据表对象。
if (dataSet != null && dataSet.Tables.Contains(POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME))
{
DataTable dataTable = dataSet.Tables[POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME];
Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable);
sqlCommand = @"SELECT WORK_ORDER_KEY,
ORDER_NUMBER,
ORDER_STATE,
ORDER_PRIORITY,
ENTERED_TIME,
PROMISED_TIME
FROM POR_WORK_ORDER
WHERE ORDER_NUMBER LIKE '%" + hashData["ORDER_NUMBER"] + "%'";
//包含工单状态的查询条件
if (hashData["ORDER_STATE"] != null)
{
sqlCommand += "AND ORDER_STATE LIKE '%" + hashData["ORDER_STATE"] + "%'";
}
//包含工单优先级的查询条件
if (hashData["ORDER_PRIORITY"] != null)
{
sqlCommand += "AND ORDER_PRIORITY LIKE '%" + hashData["ORDER_PRIORITY"] + "%'";
}
}
else
{
sqlCommand = "SELECT * FROM POR_WORK_ORDER";
}
DataTable dtTable = new DataTable();
db = DatabaseFactory.CreateDatabase();
dtTable = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0];
dtTable.TableName = POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME;
//将查询得到的数据合并到数据集中
returnDS.Merge(dtTable, false, MissingSchemaAction.Add);
//add paramter table
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(returnDS, "");
}
catch (Exception ex)
{
SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(returnDS, ex.Message);
LogService.LogError("SearchWorkOrder Error: " + ex.Message);
}
//返回数据集
return(returnDS);
}
public DataSet DeleteSupplierCode(string supplierCode)
{
//get dynamic dataset constructor
DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet();
//define sql
String[] sqlCommand = new String[5];
try
{
if (supplierCode != string.Empty)
{
using (DbConnection dbConn = db.CreateConnection())
{
//Open Connection
dbConn.Open();
//Create Transaction
DbTransaction dbTran = dbConn.BeginTransaction();
try
{
sqlCommand[0] = "DELETE BASE_SUPPLIER WHERE CODE = '" + supplierCode + "'";
db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]);
//Commit Transaction
dbTran.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
//Rollback Transaction
dbTran.Rollback();
LogService.LogError("DeleteReasonCode Error: " + ex.Message);
}
finally
{
//Close Connection
dbConn.Close();
}
}
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message);
LogService.LogError("DeleteReasonCode Error: " + ex.Message);
}
return(dataDs);
}
public DataSet GetByFourParameters(string partId, string partType, string partModule, string partClass, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sqlCommon = new StringBuilder();
try
{
sqlCommon.Append(@"SELECT PART_ID,PART_DESC,PART_TYPE,PART_MODULE,PART_CLASS,
(SELECT COUNT(1)
FROM POR_PART_BYPRODUCT b
WHERE b.MAIN_PART_NUMBER=A.PART_ID
AND b.IS_USED='Y') AS TOTOL
FROM dbo.POR_PART A WHERE 1=1 AND A.PART_STATUS = 1");
if (!string.IsNullOrEmpty(partId))
{
sqlCommon.AppendFormat(" AND PART_ID LIKE '{0}%'", partId);
}
if (!string.IsNullOrEmpty(partType))
{
sqlCommon.AppendFormat(" AND PART_TYPE LIKE '{0}%'", partType);
}
if (!string.IsNullOrEmpty(partModule))
{
sqlCommon.AppendFormat(" AND PART_MODULE LIKE '{0}%'", partModule);
}
if (!string.IsNullOrEmpty(partClass))
{
sqlCommon.AppendFormat(" AND PART_CLASS LIKE '{0}%'", partClass);
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sqlCommon.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
"POR_PART",
"PART_ID ASC");
pconfig.Pages = pages;
pconfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetByFourParameters Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 执行SAP远程函数（RFC)。
/// </summary>
/// <param name="functionName">RFC函数名称。</param>
/// <param name="inputData">>RFC函数输入数据。</param>
/// <returns>RFC函数返回数据。</returns>
public DataSet ExecuteRFC(string functionName, DataSet inputData)
{
DateTime startTime = DateTime.Now;
DataSet outputData = null;
try
{
AllCommonFunctions.SAPRemoteFunctionCall(functionName, inputData, out outputData);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(outputData, ex.Message);
LogService.LogError("Execute RFC Error: " + ex.Message);
}
DateTime endTime = DateTime.Now;
LogService.LogInfo("Cal SAP Function: " + functionName + " Total Time:" + (endTime - startTime).TotalMilliseconds.ToString());
return(outputData);
}
//删除
/// <summary>
/// 获取SAP中的退料单是否删除。
/// </summary>
/// <param name="dsIMPORT">包含退料单信息的数据集对象。</param>
/// <returns>true:退料单在SAP中已删除。false:退料单未删除。</returns>
public bool DeleteMat(DataSet dsIMPORT)
{
bool bltmp = false;
DataSet dsOut = new DataSet();
try
{
//调用RFC
AllCommonFunctions.SAPRemoteFunctionCall("Z_RFC_RET", dsIMPORT, out dsOut);
}
catch (Exception ex)
{
LogService.LogError("WOMaterialReturnEngine Save Error: " + ex.Message);
}
finally
{
}
if (Convert.ToString(dsOut.Tables[0].Rows[0]["LVORM"]) == "D")//判断条件
{
bltmp = true;
}
return(bltmp);
}
/// <summary>
/// 入库单查询
/// </summary>
/// <param name="ZMBLNR"></param>
/// <returns></returns>
public DataSet QueryWarehouseWarrant(DataSet dsParams, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
sBuilder.Append(@"SELECT ZMBLNR, WERKS, STATUS, ZMMTYP, AUFNR, VBELN_OEM, DEPT,
CDATE, CREATOR, MBLNR, MBLNR1, CAST(ISSYN AS VARCHAR(10)) AS ISSYN, SYNMAN, SYNDATE
FROM AWMS_WH_ENTRY a
WHERE STATUS = '正常' ");
if (dsParams != null && dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = CommonUtils.ConvertToHashtable(dtParams);
if (htParams.ContainsKey("ZMBLNR"))
{
string ZMBLNR = Convert.ToString(htParams["ZMBLNR"]);
sBuilder.AppendFormat(" AND ZMBLNR LIKE '{0}%'", ZMBLNR.PreventSQLInjection());
}
if (htParams.ContainsKey("AUFNR"))
{
string AUFNR = Convert.ToString(htParams["AUFNR"]);
sBuilder.AppendFormat(" AND AUFNR LIKE '{0}%'", AUFNR.PreventSQLInjection());
}
if (htParams.ContainsKey("MATNR"))
{
string MATNR = Convert.ToString(htParams["MATNR"]);
sBuilder.AppendFormat(" AND EXISTS (SELECT ZMBLNR FROM AWMS_WH_ENTRY_DETAIL b WHERE a.ZMBLNR = b.ZMBLNR AND MATNR LIKE '{0}%')",
MATNR.PreventSQLInjection());
}
if (htParams.ContainsKey("CREATOR"))
{
string CREATOR = Convert.ToString(htParams["CREATOR"]);
sBuilder.AppendFormat(" AND CREATOR LIKE '{0}%'", CREATOR.PreventSQLInjection());
}
if (htParams.ContainsKey("BDATE"))
{
string BDATE = Convert.ToString(htParams["BDATE"]);
sBuilder.AppendFormat(" AND CDATE >= '{0}'", BDATE.PreventSQLInjection());
}
if (htParams.ContainsKey("EDATE"))
{
string EDATE = Convert.ToString(htParams["EDATE"]);
sBuilder.AppendFormat(" AND CDATE <= '{0}'", EDATE.PreventSQLInjection());
}
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"ZMBLNR ASC");
pconfig.Pages = pages;
pconfig.Records = records;
if (records == 0)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "订单信息不存在！");
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, String.Empty);
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("WarehouseWarrant Query Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 新增设备状态。
/// </summary>
/// <param name="dsParams">包含设备状态的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。</returns>
public DataSet InsertEquipmentStates(DataSet dsParams)
{
DataSet dsReturn = new DataSet();
try
{
if (dsParams != null &&
dsParams.Tables.Contains(EMS_EQUIPMENT_STATES_FIELDS.DATABASE_TABLE_NAME) &&
dsParams.Tables.Contains(PARAMETERS_INPUT.DATABASE_TABLE_NAME))
{//判定数据集是否存在表EMS_EQUIPMENT_STATES，INPUT_PARAM_TABLE
DataTable equipmentStateDataTable = dsParams.Tables[EMS_EQUIPMENT_STATES_FIELDS.DATABASE_TABLE_NAME];
DataTable inputParamDataTable = dsParams.Tables[PARAMETERS_INPUT.DATABASE_TABLE_NAME];
EMS_EQUIPMENT_STATES_FIELDS equipmentStatesFields = new EMS_EQUIPMENT_STATES_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildInsertSqlStatements(equipmentStatesFields, equipmentStateDataTable);
string equipmentStateKey = string.Empty;
string equipmentStateName = string.Empty;
string sqlString = string.Empty;
string createTime = string.Empty;
if (sqlStringList.Count > 0 && inputParamDataTable.Rows.Count > 0 && equipmentStateDataTable.Rows.Count > 0)
{
equipmentStateKey = Convert.ToString(inputParamDataTable.Rows[0][PARAMETERS_INPUT.FIELD_KEY]);
equipmentStateName = Convert.ToString(equipmentStateDataTable.Rows[0][EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_NAME]);
sqlString = sqlStringList[0];
}
using (DbConnection dbConn = db.CreateConnection())
{
dbConn.Open();
using (DbTransaction dbTrans = dbConn.BeginTransaction())
{
try
{
#region 检查设备状态名称。
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(equipmentStatesFields,
EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_NAME,
EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_NAME,
equipmentStateName,
dbTrans);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("${res:FanHai.Hemera.Modules.EMS.EquipmentStates.M0001}");
}
#endregion
if (db.ExecuteNonQuery(dbTrans, CommandType.Text, sqlString) > 0)
{
createTime = AllCommonFunctions.GetSpecifyTableColumnData(equipmentStatesFields,
EMS_EQUIPMENT_STATES_FIELDS.FIELD_CREATE_TIME,
EMS_EQUIPMENT_STATES_FIELDS.FIELD_EQUIPMENT_STATE_KEY,
equipmentStateKey,
dbTrans);
}
else
{
throw new Exception("${res:FanHai.Hemera.Module.Common.M0002}");
}
dbTrans.Commit();
}
catch (Exception ex)
{
LogService.LogError("InsertEquipmentStates Error: " + ex.Message);
dbTrans.Rollback();
throw;
}
finally
{
dbConn.Close();
}
}
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty, createTime);
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "${res:FanHai.Hemera.Module.Common.M0001}");
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("InsertEquipmentStates Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet GetSchedulePM(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string equipmentKey = reqDS.ExtendedProperties.ContainsKey(EMS_PM_SCHEDULE_FIELDS.FIELD_EQUIPMENT_KEY) ? reqDS.ExtendedProperties[EMS_PM_SCHEDULE_FIELDS.FIELD_EQUIPMENT_KEY].ToString() : string.Empty;
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
string sqlString = @"SELECT PS.*,
U.USERNAME AS NOTIFY_USER_NAME,
UC.USERNAME AS NOTIFY_CC_USER_NAME,
ECS.EQUIPMENT_CHANGE_STATE_NAME,
ECR.EQUIPMENT_CHANGE_REASON_NAME,
E.EQUIPMENT_NAME,
C.CHECKLIST_NAME
FROM EMS_PM_SCHEDULE PS,
EMS_CHECKLIST C,
EMS_EQUIPMENTS E,
RBAC_USER U,
RBAC_USER UC,
EMS_EQUIPMENT_CHANGE_REASONS ECR,
EMS_EQUIPMENT_CHANGE_STATES ECS
WHERE PS.CHECKLIST_KEY = C.CHECKLIST_KEY
AND PS.EQUIPMENT_KEY = E.EQUIPMENT_KEY
AND PS.NOTIFY_USER_KEY = U.USER_KEY
AND PS.NOTIFY_CC_USER_KEY = UC.USER_KEY(+)
AND PS.EQUIPMENT_CHANGE_REASON_KEY = ECR.EQUIPMENT_CHANGE_REASON_KEY(+)
AND PS.EQUIPMENT_CHANGE_STATE_KEY = ECS.EQUIPMENT_CHANGE_STATE_KEY(+)";
if (!string.IsNullOrEmpty(equipmentKey))
{
sqlString += string.Format(" AND E.EQUIPMENT_KEY = '{0}'", equipmentKey);
}
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetSchedulePM Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet UpdateSchedulePM(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.Tables.Contains(EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
DataTable schedulePMDataTable = reqDS.Tables[EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME];
string scheduleKey = reqDS.ExtendedProperties[PARAMETERS.INPUT_KEY].ToString();
string editor = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDITOR].ToString();
string editTime = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIME].ToString();
try
{
EMS_PM_SCHEDULE_FIELDS schedulePMFields = new EMS_PM_SCHEDULE_FIELDS();
#region Build Update SQL Conditions
Conditions conditions = new Conditions();
Condition condition;
if (string.IsNullOrEmpty(scheduleKey))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY,
GlobalEnums.DatabaseCompareOperator.Equal, scheduleKey);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editor))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Equal, editor);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editTime))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Equal, editTime);
}
conditions.Add(condition);
List <Conditions> conditionsList = new List <Conditions>()
{
conditions
};
#endregion
List <string> sqlStringList = DatabaseTable.BuildUpdateSqlStatements(schedulePMFields, schedulePMDataTable, conditionsList);
if (sqlStringList.Count > 0 && schedulePMDataTable.Rows.Count > 0)
{
string scheduleName = schedulePMDataTable.Rows[0][EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_NAME].ToString();
string frequence = schedulePMDataTable.Rows[0][EMS_PM_SCHEDULE_FIELDS.FIELD_FREQUENCE].ToString();
string frequenceUnit = schedulePMDataTable.Rows[0][EMS_PM_SCHEDULE_FIELDS.FIELD_FREQUENCE_UNIT].ToString();
string nextEventTime = string.Empty;
string sqlString = sqlStringList[0];
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Validate Schedule PM Name
if (!string.IsNullOrEmpty(scheduleName))
{
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_NAME, GlobalEnums.DatabaseCompareOperator.Equal, scheduleName);
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY, GlobalEnums.DatabaseCompareOperator.NotEqual, scheduleKey);
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(schedulePMFields, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_NAME, conditions, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("计划PM名称已存在!");
}
}
#endregion
#region Update Schedule PM Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
if (string.IsNullOrEmpty(frequence) && string.IsNullOrEmpty(frequenceUnit))
{
editTime = AllCommonFunctions.GetSpecifyTableColumnData(schedulePMFields, EMS_PM_SCHEDULE_FIELDS.FIELD_EDIT_TIME, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY, scheduleKey, transaction);
}
else
{
#region Calculate Schedule PM Next Event Time
sqlString = string.Format(@"UPDATE EMS_PM_SCHEDULE
SET NEXT_EVENT_TIME = CASE FREQUENCE_UNIT WHEN '小时' THEN SYSDATE + FREQUENCE * INTERVAL '1' HOUR WHEN '天' THEN SYSDATE + FREQUENCE * INTERVAL '1' DAY WHEN '周' THEN SYSDATE + FREQUENCE * INTERVAL '7' DAY WHEN '月' THEN SYSDATE + FREQUENCE * INTERVAL '1' MONTH WHEN '年' THEN SYSDATE + FREQUENCE * INTERVAL '1' YEAR ELSE SYSDATE + FREQUENCE * INTERVAL '1' HOUR END
WHERE SCHEDULE_KEY = '{0}'", scheduleKey);
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
#region Get Next Event Time And Edit Time
List <string> interestFields = new List <string>();
interestFields.Add(EMS_PM_SCHEDULE_FIELDS.FIELD_NEXT_EVENT_TIME);
interestFields.Add(EMS_PM_SCHEDULE_FIELDS.FIELD_EDIT_TIME);
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY, GlobalEnums.DatabaseCompareOperator.Equal, scheduleKey);
sqlString = DatabaseTable.BuildQuerySqlStatement(schedulePMFields, interestFields, conditions);
using (IDataReader dataReader = db.ExecuteReader(transaction, CommandType.Text, sqlString))
{
if (dataReader.Read())
{
nextEventTime = dataReader.GetDateTime(0).ToString();
editTime = dataReader.GetDateTime(1).ToString();
}
dataReader.Close();
}
#endregion
}
else
{
throw new Exception("计算下次PM时间出错!");
}
#endregion
}
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(EMS_PM_SCHEDULE_FIELDS.FIELD_NEXT_EVENT_TIME, nextEventTime);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, editTime);
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("UpdateSchedulePM Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
private DataSet Query(DataSet dsParams, ref PagingQueryConfig pConfig, bool ispaging)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
////出库校验
//if (status == "CKJY")
//{
sBuilder.Append(@"select OUTBANDNO,VBELN,POSNR,CPBH,XHGG,OBDQTY,QC_RESULT,BATCHNO,STATUS,QC_PERSON,
case PKG_MAT when 0 then '合格' when 1 then '不合格' else ''end as PKG_MAT,
case BILL_BRND when 0 then '合格' when 1 then '不合格'else '' end as BILL_BRND ,
case CANTR when 0 then '合格' when 1 then '不合格' else '' end as CANTR ,
case F_OUTB_CUSTM when 0 then '合格' when 1 then '不合格'else '' end as F_OUTB_CUSTM ,
case EL when 0 then '合格' when 1 then '不合格'else '' end as EL ,
case DATA_FORMT when 0 then '合格' when 1 then '不合格'else '' end as DATA_FORMT,
case LIST_ABSENCE when 0 then '合格' when 1 then '不合格'else '' end as LIST_ABSENCE ,
case LiST_ERR when 0 then '合格' when 1 then '不合格'else '' end as LiST_ERR ,
case CELL when 0 then '合格' when 1 then '不合格'else '' end as CELL ,
case MOD_ERR when 0 then '合格' when 1 then '不合格'else '' end as MOD_ERR ,
case QLVL_ERR when 0 then '合格' when 1 then '不合格'else '' end as QLVL_ERR,
case FRAME when 0 then '合格' when 1 then '不合格'else '' end as FRAME ,
case BRND_PARM_ERR when 0 then '合格' when 1 then '不合格'else '' end as BRND_PARM_ERR ,
case CONT_LOCK_BRK when 0 then '合格' when 1 then '不合格'else '' end as CONT_LOCK_BRK ,
case CUSTM_CK when 0 then '合格' when 1 then '不合格'else '' end as CUSTM_CK
from (select OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT,BILL_BRND ,CANTR ,F_OUTB_CUSTM ,EL ,DATA_FORMT, LIST_ABSENCE , LiST_ERR , CELL ,
MOD_ERR ,QLVL_ERR,FRAME ,BRND_PARM_ERR ,CONT_LOCK_BRK ,CUSTM_CK
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已确认') and b.STATUS is null
union select distinct OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT=2,BILL_BRND=2 ,CANTR=2 ,F_OUTB_CUSTM= 2,EL=2 ,DATA_FORMT=2,
LIST_ABSENCE=2 ,LiST_ERR=2 , CELL=2 ,MOD_ERR=2 , QLVL_ERR=2,FRAME=2 ,
BRND_PARM_ERR=2 ,CONT_LOCK_BRK=2 ,CUSTM_CK=2
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已确认') and b.STATUS ='删除'
union select OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT,BILL_BRND ,CANTR ,F_OUTB_CUSTM ,EL ,DATA_FORMT, LIST_ABSENCE , LiST_ERR , CELL ,
MOD_ERR ,QLVL_ERR,FRAME ,BRND_PARM_ERR ,CONT_LOCK_BRK ,CUSTM_CK
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已过账','已检验') and b.STATUS='正常') a where 1=1
");
//}
// //出库管理
// else if (status == "CKGL")
// {
// sBuilder.Append(@"select a.OUTBANDNO,a.VBELN,a.POSNR,a.status,CPBH,XHGG,QCQTY,OBDQTY,QC_RESULT,BATCHNO,
// c.PALLET_NO from AWMS_OUTB_ITEM a,AWMS_CONTAINER_DETAIL b ,[dbo].WIP_CONSIGNMENT c
// where a.Cabinet_NO=b.CONTAINER_CODE and a.Cabinet_KEY=b.CONTAINER_KEY and a.CPBH=c.SAP_NO
// and b.PALLET_NO=c.PALLET_NO and a.STATUS in('已确认','已过账','已检验')
// ");
// }
if (dsParams != null && dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParam = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable hsParam = CommonUtils.ConvertToHashtable(dtParam);
if (hsParam.ContainsKey(AWMS_OUTB_ITEM_FIELDS.Field_VBELN))
{
string vbelnNo = Convert.ToString(hsParam[AWMS_OUTB_ITEM_FIELDS.Field_VBELN]);
sBuilder.AppendFormat(" and a.VBELN like '{0}%'", vbelnNo.PreventSQLInjection());
}
if (hsParam.ContainsKey(AWMS_OUTB_ITEM_FIELDS.Field_OUTBANDNO))
{
string outboundNo = Convert.ToString(hsParam[AWMS_OUTB_ITEM_FIELDS.Field_OUTBANDNO]);
sBuilder.AppendFormat(" and a.OUTBANDNO like '{0}%'", outboundNo.PreventSQLInjection());
}
if (hsParam.ContainsKey(AWMS_CONTAINER_DETAIL_FIELDS.Field_PALLET_NO))
{
string outboundNo = Convert.ToString(hsParam[AWMS_CONTAINER_DETAIL_FIELDS.Field_PALLET_NO]);
sBuilder.AppendFormat(" and c.PALLET_NO like '{0}%'", outboundNo.PreventSQLInjection());
}
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pConfig.PageNo,
pConfig.PageSize,
out pages,
out records,
db,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"");
pConfig.Pages = pages;
pConfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("OutboundOperationEngine Query Error:" + ex.Message);
}
finally
{ }
return(dsReturn);
}
/// <summary>
/// 查询在线物料。
/// </summary>
/// <param name="model">查询条件。</param>
/// <param name="pconfig">分页对象。</param>
/// <returns>包含在线物料信息的数据集对象。</returns>
public DataSet Query(OnlineMaterialQueryModel model, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.Append(@"SELECT A.STORE_MATERIAL_KEY ,
SUM(A.CURRENT_QTY)AS QTY,
C.STORE_NAME AS STORE_NAME,
D.MATERIAL_CODE AS MATERIAL_CODE,
D.MATERIAL_NAME AS MATERIAL_NAME,
D.UNIT AS UNIT,
E.LOCATION_NAME AS ROOM_NAME,
C.OPERATION_NAME AS OPERATION_NAME
FROM WST_STORE_MATERIAL_DETAIL A
LEFT JOIN WST_STORE_MATERIAL B ON A.STORE_MATERIAL_KEY=B.STORE_MATERIAL_KEY
LEFT JOIN WST_STORE C ON B.STORE_KEY=C.STORE_KEY
LEFT JOIN POR_MATERIAL D ON B.MATERIAL_KEY=D.MATERIAL_KEY
LEFT JOIN FMM_LOCATION E ON C.LOCATION_KEY=E.LOCATION_KEY
WHERE CURRENT_QTY>0 ");
if (!string.IsNullOrEmpty(model.OperationName))
{
sbSql.AppendFormat(UtilHelper.BuilderWhereConditionString("C.OPERATION_NAME", model.OperationName.Split(',')));
}
if (!string.IsNullOrEmpty(model.StoreName))
{
sbSql.AppendFormat(UtilHelper.BuilderWhereConditionString("C.STORE_NAME", model.StoreName.Split(',')));
}
if (!string.IsNullOrEmpty(model.MaterialCode))
{
sbSql.AppendFormat(" AND D.MATERIAL_CODE LIKE '{0}%'", model.MaterialCode.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.MaterialLot))
{
sbSql.AppendFormat(" AND A.MATERIAL_LOT LIKE '{0}%'", model.MaterialLot.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.RoomName))
{
sbSql.AppendFormat(" AND E.LOCATION_NAME LIKE '{0}%'", model.RoomName.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.SupplierName))
{
sbSql.AppendFormat(" AND A.MATERIAL_SUPPLIER LIKE '{0}%'", model.SupplierName.PreventSQLInjection());
}
sbSql.Append(@" GROUP BY A.STORE_MATERIAL_KEY,C.STORE_NAME,D.MATERIAL_CODE,E.LOCATION_NAME,
D.MATERIAL_NAME,D.UNIT, E.LOCATION_NAME,C.OPERATION_NAME ");
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sbSql.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
"WST_STORE_MATERIAL",
"MATERIAL_CODE ASC");
pconfig.Pages = pages;
pconfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotList Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 工单报工。
/// 1.UPDATE WIP_TRANSACTION
/// 2.UPDATE POR_QC_DATA
/// 3.UPDATE WST_RK_ZXBPO
/// 4.获取工单的报废数量
/// 5.获取工单的合格品数量
/// 6.INSERT POR_WORK_ORDER_REPORT
/// 7.UPDATE WIP_TRANSACTION
/// 8.UPDATE POR_QC_DATA
/// 9.UPDATE WST_RK_ZXBPO
/// 10.调用SAP RFC
/// </summary>
/// <param name="tableParam">
/// 传入参数的值：AUFNR 工单号 ROOM_KEY 工厂主键 CREATOR 创建者 CREATE_TIMEZONE 创建时间 REPORTOR 报工人 SHIFT_NAME 班次
/// </param>
/// <returns>包含执行结果的数据集对象。</returns>
public DataSet GongDanBaoGong(DataTable tableParam)
{
const string CONST_UPDATE_WIP_TRANSACTION_1 = @"UPDATE t
SET t.REPORT_FLAG='R'
FROM WIP_TRANSACTION t
WHERE EXISTS(SELECT a.TRANSACTION_KEY
FROM WIP_TRANSACTION a
INNER JOIN POR_WORK_ORDER c ON a.WORK_ORDER_KEY=c.WORK_ORDER_KEY
WHERE a.ACTIVITY='SETLOSS'
AND a.UNDO_FLAG=0
AND a.REPORT_FLAG='N'
AND c.ORDER_NUMBER='{0}'
AND a.TRANSACTION_KEY=t.TRANSACTION_KEY)";
const string CONST_UPDATE_POR_QC_DATA_2 = @"UPDATE POR_QC_DATA
SET REPORT_FLAG='R'
WHERE STATUS=1 AND REPORT_FLAG='N'
AND ORDER_NUMBER='{0}'";
const string CONST_UPDATE_WST_RK_ZXBPO_3 = @"UPDATE t
SET t.REPORT_FLAG='R'
FROM WST_RK_ZXBPO t
WHERE t.AUFNR='{0}'
AND EXISTS (SELECT a.ZXBNO
FROM WST_RK_ZXBPO a
INNER JOIN WST_RK_ZXBKO b ON a.ZXBNO=b.ZXBNO
WHERE b.LVORM=0 AND b.STATE=2 AND a.REPORT_FLAG='N'
AND a.ZXBNO=t.ZXBNO AND a.BARCODE=t.BARCODE AND a.AUFNR='{0}')";
const string CONST_SELECT_1 = @"SELECT SUM(quantity_out) SCRAP_QTY,c.ORDER_NUMBER
FROM WIP_TRANSACTION a
INNER JOIN POR_LOT b ON a.PIECE_KEY=b.LOT_KEY AND a.PIECE_TYPE=0
INNER JOIN POR_WORK_ORDER c ON b.WORK_ORDER_KEY=c.WORK_ORDER_KEY
WHERE a.ACTIVITY='SETLOSS' AND a.UNDO_FLAG=0 AND a.REPORT_FLAG='R' AND c.ORDER_NUMBER='{0}'
GROUP BY c.ORDER_NUMBER
UNION
SELECT SUM(SCRAPPED_QTY),ORDER_NUMBER
FROM POR_QC_DATA
WHERE STATUS=1 AND REPORT_FLAG='R' AND ORDER_NUMBER='{0}'
GROUP BY ORDER_NUMBER";
const string CONST_SELECT_2 = @"SELECT SUM(MENGE) OK_QTY,a.AUFNR
FROM WST_RK_ZXBPO a
INNER JOIN WST_RK_ZXBKO b ON a.ZXBNO=b.ZXBNO
WHERE b.LVORM=0 AND b.STATE=2 AND a.REPORT_FLAG='R' AND b.ORDER_NUMBER='{0}'
GROUP BY a.AUFNR";
const string CONST_INSERT_POR_WORK_ORDER_REPORT_1 = @"INSERT INTO POR_WORK_ORDER_REPORT
(ROW_KEY,ACK_DATA,ACK_TIME,AUFNR,APLFL,GMNGA,GMEIN,XMNGA,RMZHL,TYPE,START_TIME_STAMP
,TIME_STAMP,ROOM_KEY,WERKS,CON_END,CREATE_TIME,CREATOR,CREATE_TIMEZONE,REPORTOR,SHIFT_NAME)
VALUES('{0}',CONVERT(varchar(10), GETDATE(),126),CONVERT(varchar(10), GETDATE(),108),'{1}','0060',
{2},'PC',{3},'0','0','',CAST('{4}' AS Datetime),'{5}','HZ01','N',GETDATE(),'{6}','{7}','{8}','{9}')";
const string CONST_UPDATE_POR_WORK_WIP_TRANSACTION_4 = @"UPDATE t
SET t.REPORT_FLAG='Y'
FROM WIP_TRANSACTION t
WHERE EXISTS(SELECT a.TRANSACTION_KEY
FROM WIP_TRANSACTION a
INNER JOIN POR_WORK_ORDER c ON a.WORK_ORDER_KEY=c.WORK_ORDER_KEY
WHERE a.ACTIVITY='SETLOSS'
AND a.UNDO_FLAG=0
AND a.REPORT_FLAG='R'
AND c.ORDER_NUMBER='{0}'
AND a.TRANSACTION_KEY=t.TRANSACTION_KEY)";
const string CONST_UPDATE_POR_QC_DATA_5 = @"UPDATE POR_QC_DATA
SET REPORT_FLAG='Y'
WHERE STATUS=1 AND REPORT_FLAG='R'
AND ORDER_NUMBER='{0}'";
const string CONST_UPDATE_WST_RK_ZXBPO_6 = @"UPDATE t
SET t.REPORT_FLAG='Y'
FROM WST_RK_ZXBPO t
WHERE t.AUFNR='{0}'
AND EXISTS (SELECT a.ZXBNO
FROM WST_RK_ZXBPO a
INNER JOIN WST_RK_ZXBKO b ON a.ZXBNO=b.ZXBNO
WHERE b.LVORM=0 AND b.STATE=2 AND a.REPORT_FLAG='R'
AND a.ZXBNO=t.ZXBNO AND a.BARCODE=t.BARCODE AND a.AUFNR='{0}')";
DataSet dsReturn = new DataSet();
DataTable dtHash = tableParam;
Hashtable hsTable = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtHash);
DbConnection dbCon = db.CreateConnection();
dbCon.Open();
DbTransaction dbTrans = dbCon.BeginTransaction();
try
{
string sqlUpdate1 = "";
string sqlUpdate2 = "";
string sqlUpdate3 = "";
string sqlSelect1 = "";
string sqlSelect2 = "";
string sqlInsert1 = "";
string sqlUpdate4 = "";
string sqlUpdate5 = "";
string sqlUpdate6 = "";
DbCommand dbCom = dbCon.CreateCommand();
dbCom.Transaction = dbTrans;
string orderNumber = Convert.ToString(hsTable["AUFNR"]).PreventSQLInjection();
sqlUpdate1 = string.Format(CONST_UPDATE_WIP_TRANSACTION_1, orderNumber);
dbCom.CommandText = sqlUpdate1;
int i = dbCom.ExecuteNonQuery();
sqlUpdate2 = string.Format(CONST_UPDATE_POR_QC_DATA_2, orderNumber);
dbCom.CommandText = sqlUpdate2;
int j = dbCom.ExecuteNonQuery();
sqlUpdate3 = string.Format(CONST_UPDATE_WST_RK_ZXBPO_3, orderNumber);
dbCom.CommandText = sqlUpdate3;
int k = dbCom.ExecuteNonQuery();
sqlSelect1 = string.Format(CONST_SELECT_1, orderNumber);
DataSet dsBaoFei = db.ExecuteDataSet(dbTrans, CommandType.Text, sqlSelect1);
sqlSelect2 = string.Format(CONST_SELECT_2, orderNumber);
DataSet dsHeGe = db.ExecuteDataSet(dbTrans, CommandType.Text, sqlSelect2);
if (dsHeGe.Tables[0].Rows.Count <= 0 && dsBaoFei.Tables[0].Rows.Count <= 0)
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "合格品数量=0 并且报废数量=0，无法报工。");
}
else
{
string scrapQty = "0";
if (dsBaoFei.Tables[0].Rows.Count > 0)
{
scrapQty = Convert.ToString(dsBaoFei.Tables[0].Rows[0][0]);
}
string okQty = "0";
if (dsHeGe.Tables[0].Rows.Count > 0)
{
okQty = Convert.ToString(dsHeGe.Tables[0].Rows[0][0]);
}
scrapQty = string.IsNullOrEmpty(scrapQty) ? "0" : scrapQty;
okQty = string.IsNullOrEmpty(okQty) ? "0" : okQty;
if (scrapQty == "0" && okQty == "0")
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "合格品数量=0 并且报废数量=0，无法报工。");
}
else
{
string sql = @"SELECT GETDATE()";
string time = Convert.ToString(db.ExecuteScalar(CommandType.Text, sql));
string strNewKey = System.Guid.NewGuid().ToString();
string roomKey = Convert.ToString(hsTable["ROOM_KEY"]);
string creator = Convert.ToString(hsTable["CREATOR"]);
string timezone = Convert.ToString(hsTable["CREATE_TIMEZONE"]);
string reportor = Convert.ToString(hsTable["REPORTOR"]);
string shiftName = Convert.ToString(hsTable["SHIFT_NAME"]);
sqlInsert1 = string.Format(CONST_INSERT_POR_WORK_ORDER_REPORT_1,
strNewKey, //主键
orderNumber, //工单号
okQty, //合格品数量
scrapQty, //报废数量
time,
roomKey, //工厂车间
creator, //创建人
timezone, //创建时区
reportor, //报工人
shiftName //班次
);
dbCom.CommandText = sqlInsert1;
int p = dbCom.ExecuteNonQuery();
sqlUpdate4 = string.Format(CONST_UPDATE_POR_WORK_WIP_TRANSACTION_4, orderNumber);
dbCom.CommandText = sqlUpdate4;
int q = dbCom.ExecuteNonQuery();
sqlUpdate5 = string.Format(CONST_UPDATE_POR_QC_DATA_5, orderNumber);
dbCom.CommandText = sqlUpdate5;
int w = dbCom.ExecuteNonQuery();
sqlUpdate6 = string.Format(CONST_UPDATE_WST_RK_ZXBPO_6, orderNumber);
dbCom.CommandText = sqlUpdate6;
int e = dbCom.ExecuteNonQuery();
///////////////////////////////////////////////////////////////////////////////////////////////////////
//调用RFC
DataSet dsOut = new DataSet();
DataSet dsIn = new DataSet();
DataTable dtWorkOrderWorkFor = new DataTable();
dtWorkOrderWorkFor.Columns.Add("ITMNO");
dtWorkOrderWorkFor.Columns.Add("AUFNR");
dtWorkOrderWorkFor.Columns.Add("WERKS");
dtWorkOrderWorkFor.Columns.Add("VORNR");
dtWorkOrderWorkFor.Columns.Add("LMNGA");
dtWorkOrderWorkFor.Columns.Add("MEINH");
dtWorkOrderWorkFor.Columns.Add("XMNGA");
dtWorkOrderWorkFor.Columns.Add("CON_END");
DataRow dr = dtWorkOrderWorkFor.NewRow();
dr["ITMNO"] = 1;
dr["AUFNR"] = hsTable["AUFNR"].ToString(); //工单号
dr["WERKS"] = "HZ01"; //杭州晶硅
dr["VORNR"] = "0060"; //晶硅电池
dr["LMNGA"] = okQty; //合格品数量
dr["MEINH"] = "PC"; //单位
dr["XMNGA"] = scrapQty; //报废数量
dr["CON_END"] = ""; //确认
dtWorkOrderWorkFor.Rows.Add(dr);
dtWorkOrderWorkFor.TableName = "ZPP_WORK";
dsIn.Merge(dtWorkOrderWorkFor);
//AllCommonFunctions.SAPRemoteFunctionCall("RFC FUNCTION NAME", dsIn, out dsOut);
AllCommonFunctions.SAPRemoteFunctionCall("Z_RFC_COF", dsIn, out dsOut);
bool bRollback = false;
if (dsOut == null ||
dsOut.Tables.Count == 0 ||
dsOut.Tables[0].Rows.Count == 0 ||
int.Parse(dsOut.Tables[0].Rows[0]["NUMBER"].ToString()) < 0)
{
bRollback = true;
}
if (!bRollback) //MESSAGE
{
dbTrans.Commit();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "工单报工成功！");
}
else
{
dbTrans.Rollback();
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, dsOut.Tables[0].Rows[0][1].ToString());
}
}
}
}
catch (Exception ex)
{
dbTrans.Rollback();
LogService.LogError("GongDanBaoGong Error: " + ex.Message);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
}
return(dsReturn);
}
public DataSet GetCheckListJobs(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string equipmentKey = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY].ToString() : string.Empty;
string checkListJobKey = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY].ToString() : string.Empty;
string checkListJobName = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME].ToString() : string.Empty;
string checkListJobState = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_STATE) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_STATE].ToString() : string.Empty;
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
string sqlString = @"SELECT CJ.*,
E.EQUIPMENT_NAME,
C.CHECKLIST_NAME,
CASE CJ.PM_TYPE
WHEN 'SCHEDULE' THEN
PS.SCHEDULE_NAME
WHEN 'CONDITION' THEN
PC.CONDITION_NAME
ELSE
''
END AS PM_NAME
FROM EMS_CHECKLIST_JOBS CJ,
EMS_CHECKLIST C,
EMS_PM_SCHEDULE PS,
EMS_PM_CONDITION PC,
EMS_EQUIPMENTS E
WHERE CJ.CHECKLIST_KEY = C.CHECKLIST_KEY
AND CJ.PM_KEY = PS.SCHEDULE_KEY(+)
AND CJ.PM_KEY = PC.CONDITION_KEY(+)
AND CJ.EQUIPMENT_KEY = E.EQUIPMENT_KEY";
if (!string.IsNullOrEmpty(checkListJobState))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_STATE = '{0}'", checkListJobState);
}
if (!string.IsNullOrEmpty(checkListJobName))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_NAME LIKE '%{0}%'", checkListJobName);
}
if (!string.IsNullOrEmpty(checkListJobKey))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_KEY = '{0}'", checkListJobKey);
}
if (!string.IsNullOrEmpty(equipmentKey))
{
sqlString += string.Format(" AND E.EQUIPMENT_KEY = '{0}'", equipmentKey);
}
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetCheckListJobs Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet InsertCheckList(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null && reqDS.Tables.Contains(EMS_CHECKLIST_FIELDS.DATABASE_TABLE_NAME))
{
DataTable checkListDataTable = reqDS.Tables[EMS_CHECKLIST_FIELDS.DATABASE_TABLE_NAME];
try
{
EMS_CHECKLIST_FIELDS checkListFields = new EMS_CHECKLIST_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildInsertSqlStatements(checkListFields, checkListDataTable);
string checkListKey = string.Empty;
string checkListName = string.Empty;
string createTime = string.Empty;
string sqlString = string.Empty;
if (sqlStringList.Count > 0 && checkListDataTable.Rows.Count > 0)
{
checkListKey = checkListDataTable.Rows[0][EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY].ToString();
checkListName = checkListDataTable.Rows[0][EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME].ToString();
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Validate Check List Name
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(checkListFields, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME, checkListName, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("检查表单名称已存在!");
}
#endregion
#region Insert Check List Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
createTime = AllCommonFunctions.GetSpecifyTableColumnData(checkListFields, EMS_CHECKLIST_FIELDS.FIELD_CREATE_TIME, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY, checkListKey, transaction);
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
#region Insert Check List Items Data
if (reqDS.Tables[EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME] != null && reqDS.Tables[EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME].Rows.Count > 0)
{
DbCommand insertCommand = connection.CreateCommand();
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = @"INSERT INTO EMS_CHECKLIST_ITEM
(CHECKLIST_KEY, CHECKITEM_KEY, SEQUENCE, STANDARD, OPTIONAL)
VALUES
(:P1, :P2, :P3, :P4, :P5)";
db.AddInParameter(insertCommand, "P1", DbType.String, checkListKey);
db.AddInParameter(insertCommand, "P2", DbType.String, EMS_CHECKLIST_ITEM_FIELDS.FIELD_CHECKITEM_KEY, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P3", DbType.Int32, EMS_CHECKLIST_ITEM_FIELDS.FIELD_SEQUENCE, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P4", DbType.String, EMS_CHECKLIST_ITEM_FIELDS.FIELD_STANDARD, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P5", DbType.Int32, EMS_CHECKLIST_ITEM_FIELDS.FIELD_OPTIONAL, DataRowVersion.Current);
if (db.UpdateDataSet(reqDS, EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME, insertCommand, null, null, transaction) <= 0)
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, createTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("InsertCheckList Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
/// <summary>
/// 新增设备组。
/// </summary>
/// <param name="dsParams">包含设备组数据的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。</returns>
public DataSet InsertEquipmentGroups(DataSet dsParams)
{
DataSet resDS = new DataSet();
try
{
if (dsParams != null && dsParams.Tables.Contains(EMS_EQUIPMENT_GROUPS_FIELDS.DATABASE_TABLE_NAME) && dsParams.Tables.Contains(PARAMETERS_INPUT.DATABASE_TABLE_NAME))
{
DataTable equipmentGroupsDataTable = dsParams.Tables[EMS_EQUIPMENT_GROUPS_FIELDS.DATABASE_TABLE_NAME];
DataTable inputParamDataTable = dsParams.Tables[PARAMETERS_INPUT.DATABASE_TABLE_NAME];
EMS_EQUIPMENT_GROUPS_FIELDS equipmentGroupsFields = new EMS_EQUIPMENT_GROUPS_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildInsertSqlStatements(equipmentGroupsFields, equipmentGroupsDataTable);
string equipmentGroupKey = string.Empty;
string equipmentGroupName = string.Empty;
string sqlString = string.Empty;
string createTime = string.Empty;
if (sqlStringList.Count > 0 && inputParamDataTable.Rows.Count > 0 && equipmentGroupsDataTable.Rows.Count > 0)
{
equipmentGroupKey = inputParamDataTable.Rows[0][PARAMETERS_INPUT.FIELD_KEY].ToString();
equipmentGroupName = equipmentGroupsDataTable.Rows[0][EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME].ToString();
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Check Equipment Group Name
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(equipmentGroupsFields, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME, equipmentGroupName, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("${res:FanHai.Hemera.Modules.EMS.EquipmentGroups.M0001}");
}
#endregion
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
createTime = AllCommonFunctions.GetSpecifyTableColumnData(equipmentGroupsFields, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_CREATE_TIME, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_KEY,
equipmentGroupKey, transaction);
}
else
{
throw new Exception("${res:FanHai.Hemera.Module.Common.M0002}");
}
transaction.Commit();
}
catch (Exception ex)
{
LogService.LogError("InsertEquipmentGroups Error: " + ex.Message);
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, string.Empty, createTime);
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, "${res:FanHai.Hemera.Module.Common.M0001}");
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, ex.Message);
LogService.LogError("InsertEquipmentGroups Error: " + ex.Message);
}
return(resDS);
}
/// <summary>
/// 查询包含批次信息的数据集。
/// </summary>
/// <param name="dsSearch">
/// 包含查询条件的数据集。
/// </param>
/// <param name="pconfig">
/// 分页查询的配置对象。
/// </param>
/// <param name="isPaging">
/// 是否分页查询。
/// </param>
/// <returns>包含批次信息的数据集。</returns>
private DataSet SearchLotList(DataSet dsSearch, ref PagingQueryConfig pconfig, bool isPaging)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
sBuilder.Append(@"SELECT A.[LOT_KEY],A.[LOT_NUMBER],A.[WORK_ORDER_KEY],A.[WORK_ORDER_NO],A.[WORK_ORDER_SEQ]
,A.[PART_VER_KEY],A.[PART_NUMBER],A.[PRO_ID],A.[PRO_LEVEL],A.[PRIORITY],A.[QUANTITY_INITIAL]
,A.[LOT_LINE_CODE],A.[LOT_LINE_KEY]
,A.[QUANTITY],A.[ROUTE_ENTERPRISE_VER_KEY],A.[CUR_ROUTE_VER_KEY],A.[CUR_STEP_VER_KEY]
,A.[CUR_PRODUCTION_LINE_KEY],A.[LINE_NAME],A.[START_WAIT_TIME],A.[START_PROCESS_TIME]
,A.[EDC_INS_KEY],A.[STATE_FLAG],A.[IS_MAIN_LOT],A.[SPLIT_FLAG],A.[LOT_SEQ],A.[REWORK_FLAG]
,A.[HOLD_FLAG],A.[SHIPPED_FLAG],A.[DELETED_TERM_FLAG],A.[IS_PRINT],A.[LOT_TYPE],A.[CREATE_TYPE]
,A.[COLOR],A.[PALLET_NO],A.[PALLET_TIME],A.[STATUS],A.[OPERATOR],A.[OPR_LINE],A.[OPR_COMPUTER]
,A.[OPR_LINE_PRE],A.[CHILD_LINE],A.[MATERIAL_CODE],A.[MATERIAL_LOT],A.[SUPPLIER_NAME],A.[SI_LOT]
,A.[EFFICIENCY],A.[FACTORYROOM_KEY],A.[FACTORYROOM_NAME],A.[CREATE_OPERTION_NAME],A.[CREATOR]
,A.[CREATE_TIME],A.[CREATE_TIMEZONE_KEY],A.[EDITOR],A.[EDIT_TIME],A.[EDIT_TIMEZONE],A.[SHIFT_NAME]
,A.[DESCRIPTIONS],A.[LOT_SIDECODE],A.[LOT_CUSTOMERCODE],A.[ORG_WORK_ORDER_NO],w.PART_NUMBER AS ORG_PART_NUMBER,
E.ENTERPRISE_NAME,
D.ROUTE_NAME,
B.ROUTE_STEP_NAME,
(SELECT TOP 1 EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE LOT_KEY=A.LOT_KEY
AND STEP_KEY=A.CUR_STEP_VER_KEY
AND END_TIMESTAMP IS NULL) AS EQUIPMENT_KEY,
(SELECT EQUIPMENT_NAME
FROM EMS_EQUIPMENTS
WHERE EQUIPMENT_KEY=(SELECT TOP 1 EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE LOT_KEY=A.LOT_KEY
AND STEP_KEY=A.CUR_STEP_VER_KEY
AND END_TIMESTAMP IS NULL)) AS EQUIPMENT_NAME
FROM POR_LOT A
LEFT JOIN POR_WORK_ORDER w ON w.ORDER_NUMBER=A.ORG_WORK_ORDER_NO
LEFT JOIN POR_ROUTE_STEP B ON A.CUR_STEP_VER_KEY= B.ROUTE_STEP_KEY
LEFT JOIN POR_ROUTE_ROUTE_VER D ON A.CUR_ROUTE_VER_KEY=D.ROUTE_ROUTE_VER_KEY
LEFT JOIN POR_ROUTE_ENTERPRISE_VER E ON A.ROUTE_ENTERPRISE_VER_KEY =E.ROUTE_ENTERPRISE_VER_KEY
WHERE A.STATUS < 2");
if (dsSearch != null && dsSearch.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParams = dsSearch.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = CommonUtils.ConvertToHashtable(dtParams);
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY))
{
string roomKey = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY]);
sBuilder.AppendFormat(" AND A.FACTORYROOM_KEY ='{0}'", roomKey.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER LIKE '%{0}%'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_START"))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_START"]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER >='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_END"))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_END"]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER <='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_HOLD_FLAG))
{
string holdFalg = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_HOLD_FLAG]);
sBuilder.AppendFormat(" AND A.HOLD_FLAG={0}", holdFalg.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_TYPE))
{
string lotType = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_TYPE]);
sBuilder.AppendFormat(" AND A.LOT_TYPE='{0}'", lotType.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_STATE_FLAG))
{
string stateFlag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
sBuilder.AppendFormat(" AND A.STATE_FLAG={0}", stateFlag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATOR))
{
string creator = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATOR]);
sBuilder.AppendFormat(" AND A.CREATOR='{0}'", creator.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG))
{
string deletedTermFlag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG]);
sBuilder.AppendFormat(" AND A.DELETED_TERM_FLAG={0}", deletedTermFlag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG))
{
string flag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_SHIPPED_FLAG]);
sBuilder.AppendFormat(" AND A.SHIPPED_FLAG={0}", flag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PRO_ID))
{
string proId = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PRO_ID]);
sBuilder.AppendFormat(" AND A.PRO_ID LIKE '%{0}%'", proId.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PART_NUMBER))
{
string partNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PART_NUMBER]);
sBuilder.AppendFormat(" AND A.PART_NUMBER LIKE '%{0}%'", partNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_WORK_ORDER_NO))
{
string orderNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO]);
sBuilder.AppendFormat(" AND A.WORK_ORDER_NO LIKE '%{0}%'", orderNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO))
{
string orderNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO]);
sBuilder.AppendFormat(" AND A.ORG_WORK_ORDER_NO LIKE '%{0}%'", orderNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PALLET_NO))
{
string palletNo = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PALLET_NO]);
sBuilder.AppendFormat(" AND A.PALLET_NO LIKE '%{0}%'", palletNo.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LINE_NAME))
{
string lineName = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LINE_NAME]);
sBuilder.AppendFormat(" AND A.LINE_NAME ='{0}'", lineName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_SHIFT_NAME))
{
string shiftName = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_SHIFT_NAME]);
sBuilder.AppendFormat(" AND A.SHIFT_NAME='{0}'", shiftName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME))
{
string enterpriseName = Convert.ToString(htParams[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME]);
sBuilder.AppendFormat(" AND E.ENTERPRISE_NAME='{0}'", enterpriseName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME))
{
string routeName = Convert.ToString(htParams[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME]);
sBuilder.AppendFormat(" AND D.ROUTE_NAME='{0}'", routeName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME))
{
string stepName = Convert.ToString(htParams[POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME]);
sBuilder.AppendFormat(" AND B.ROUTE_STEP_NAME='{0}'", stepName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATE_TIME + "_START"))
{
string createStartTime = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATE_TIME + "_START"]);
sBuilder.AppendFormat(" AND A.CREATE_TIME >='{0}'", createStartTime.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATE_TIME + "_END"))
{
string createEndTime = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATE_TIME + "_END"]);
sBuilder.AppendFormat(" AND A.CREATE_TIME<='{0}'", createEndTime.PreventSQLInjection());
}
}
if (!isPaging)
{
sBuilder.Append(" ORDER BY A.LOT_NUMBER ASC,A.CREATE_TIME DESC");
dsReturn = this._dbRead.ExecuteDataSet(CommandType.Text, sBuilder.ToString());
}
else
{
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
this._dbRead,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"ASC",
new string[] { "LOT_NUMBER", "CREATE_TIME" });
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_START_WAIT_TIME].DateTimeMode = DataSetDateTime.Unspecified;
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_START_PROCESS_TIME].DateTimeMode = DataSetDateTime.Unspecified;
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_EDIT_TIME].DateTimeMode = DataSetDateTime.Unspecified;
pconfig.Pages = pages;
pconfig.Records = records;
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotList Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet UpdateCheckList(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.Tables.Contains(EMS_CHECKLIST_FIELDS.DATABASE_TABLE_NAME) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
DataTable checkListDataTable = reqDS.Tables[EMS_CHECKLIST_FIELDS.DATABASE_TABLE_NAME];
string checkListKey = reqDS.ExtendedProperties[PARAMETERS.INPUT_KEY].ToString();
string editor = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDITOR].ToString();
string editTime = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIME].ToString();
try
{
EMS_CHECKLIST_FIELDS checkListFields = new EMS_CHECKLIST_FIELDS();
#region Build Update SQL Conditions
Conditions conditions = new Conditions();
Condition condition;
if (string.IsNullOrEmpty(checkListKey))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY,
GlobalEnums.DatabaseCompareOperator.Equal, checkListKey);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editor))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Equal, editor);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editTime))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Equal, editTime);
}
conditions.Add(condition);
List <Conditions> conditionsList = new List <Conditions>()
{
conditions
};
#endregion
List <string> sqlStringList = DatabaseTable.BuildUpdateSqlStatements(checkListFields, checkListDataTable, conditionsList);
string checkListName = string.Empty;
string sqlString = string.Empty;
if (sqlStringList.Count > 0 && checkListDataTable.Rows.Count > 0)
{
checkListName = checkListDataTable.Rows[0][EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME].ToString();
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Validate Check List Name
if (!string.IsNullOrEmpty(checkListName))
{
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME, GlobalEnums.DatabaseCompareOperator.Equal, checkListName);
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY, GlobalEnums.DatabaseCompareOperator.NotEqual, checkListKey);
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(checkListFields, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME, conditions, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("检查表单名称已存在!");
}
}
#endregion
#region Update Check List Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
editTime = AllCommonFunctions.GetSpecifyTableColumnData(checkListFields, EMS_CHECKLIST_FIELDS.FIELD_EDIT_TIME, EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_KEY,
checkListKey, transaction);
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
#region Insert Check List Items Data
if (reqDS.Tables[EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME] != null && reqDS.Tables[EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME].Rows.Count > 0)
{
#region Delete Check List Items Data
sqlString = string.Format("DELETE EMS_CHECKLIST_ITEM WHERE CHECKLIST_KEY = '{0}'", checkListKey);
db.ExecuteNonQuery(transaction, CommandType.Text, sqlString);
#endregion
DbCommand insertCommand = connection.CreateCommand();
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = @"INSERT INTO EMS_CHECKLIST_ITEM
(CHECKLIST_KEY, CHECKITEM_KEY, SEQUENCE, STANDARD, OPTIONAL)
VALUES
(:P1, :P2, :P3, :P4, :P5)";
db.AddInParameter(insertCommand, "P1", DbType.String, checkListKey);
db.AddInParameter(insertCommand, "P2", DbType.String, EMS_CHECKLIST_ITEM_FIELDS.FIELD_CHECKITEM_KEY, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P3", DbType.Int32, EMS_CHECKLIST_ITEM_FIELDS.FIELD_SEQUENCE, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P4", DbType.String, EMS_CHECKLIST_ITEM_FIELDS.FIELD_STANDARD, DataRowVersion.Current);
db.AddInParameter(insertCommand, "P5", DbType.Int32, EMS_CHECKLIST_ITEM_FIELDS.FIELD_OPTIONAL, DataRowVersion.Current);
if (db.UpdateDataSet(reqDS, EMS_CHECKLIST_ITEM_FIELDS.DATABASE_TABLE_NAME, insertCommand, null, null, transaction) <= 0)
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, editTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("UpdateCheckList Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet InsertPart(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null && reqDS.Tables.Contains(EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME))
{
DataTable partsDataTable = reqDS.Tables[EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME];
if (partsDataTable.Rows.Count > 0)
{
try
{
EMS_EQUIPMENT_PARTS_FIELDS partsFields = new EMS_EQUIPMENT_PARTS_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildInsertSqlStatements(partsFields, partsDataTable);
string partKey = partsDataTable.Rows[0][EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_KEY].ToString();
string partName = partsDataTable.Rows[0][EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME].ToString();
string createTime = string.Empty;
string sqlString = string.Empty;
if (sqlStringList.Count > 0)
{
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Validate Part Name
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(partsFields, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME, partName, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("备件名称已存在!");
}
#endregion
#region Insert Part Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
createTime = AllCommonFunctions.GetSpecifyTableColumnData(partsFields, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATE_TIME, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_KEY, partKey, transaction);
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, createTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("InsertPart Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet GetParts(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string partName = reqDS.ExtendedProperties[PARAMETERS.INPUT_KEY].ToString();
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
EMS_EQUIPMENT_PARTS_FIELDS partsFields = new EMS_EQUIPMENT_PARTS_FIELDS();
List <string> interestColumns = new List <string>();
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_DESCRIPTION);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_TYPE);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_MODE);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_UNIT);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATOR);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATE_TIMEZONE_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATE_TIME);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDITOR);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDIT_TIMEZONE_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDIT_TIME);
Conditions conditions = new Conditions();
if (!string.IsNullOrEmpty(partName))
{
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME, GlobalEnums.DatabaseCompareOperator.Like, string.Format("%{0}%", partName));
}
string sqlString = DatabaseTable.BuildQuerySqlStatement(partsFields, interestColumns, conditions);
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetParts Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet UpdateCheckListJob(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.Tables.Contains(EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
DataTable checkListJobsDataTable = reqDS.Tables[EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME];
string checkListJobKey = reqDS.ExtendedProperties[PARAMETERS.INPUT_KEY].ToString();
string editor = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDITOR].ToString();
string editTime = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIME].ToString();
try
{
EMS_CHECKLIST_JOBS_FIELDS checkListJobsFields = new EMS_CHECKLIST_JOBS_FIELDS();
#region Build Update SQL Conditions
Conditions conditions = new Conditions();
Condition condition;
if (string.IsNullOrEmpty(checkListJobKey))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY,
GlobalEnums.DatabaseCompareOperator.Equal, checkListJobKey);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editor))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDITOR,
GlobalEnums.DatabaseCompareOperator.Equal, editor);
}
conditions.Add(condition);
if (string.IsNullOrEmpty(editTime))
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Null, string.Empty);
}
else
{
condition = new Condition(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME,
GlobalEnums.DatabaseCompareOperator.Equal, editTime);
}
conditions.Add(condition);
List <Conditions> conditionsList = new List <Conditions>()
{
conditions
};
#endregion
List <string> sqlStringList = DatabaseTable.BuildUpdateSqlStatements(checkListJobsFields, checkListJobsDataTable, conditionsList);
string sqlString = string.Empty;
if (sqlStringList.Count > 0 && checkListJobsDataTable.Rows.Count > 0)
{
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Update Check List Job Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
editTime = AllCommonFunctions.GetSpecifyTableColumnData(checkListJobsFields, EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY,
checkListJobKey, transaction);
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, editTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("UpdateCheckListJob Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
/// <summary>
/// 查询工序数据。
/// </summary>
/// <param name="dsParams">包含查询条件的数据集。</param>
/// <param name="operationName">工序名称。</param>
/// <param name="pageNo">页号。</param>
/// <param name="pageSize">每页记录数。</param>
/// <param name="pages">总页数。</param>
/// <param name="records">总记录数。</param>
/// <returns>包含工序数据的数据集对象。</returns>
public DataSet GetOperations(DataSet dsParams, string operationName, int pageNo, int pageSize, out int pages, out int records)
{
DataSet dsReturn = new DataSet();
pages = 0;
records = 0;
try
{
POR_ROUTE_OPERATION_VER_FIELDS operationsFields = new POR_ROUTE_OPERATION_VER_FIELDS();
#region 返回的数据列
List <string> interestColumns = new List <string>();
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_DESCRIPTIONS);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_IMAGE_KEY);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_DURATION);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_VERSION);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_STATUS);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_IS_REWORKABLE);
#endregion
#region 组织查询条件
Conditions conditions = new Conditions();
if (dsParams != null && dsParams.Tables.Contains(PARAMETERS_INPUT.DATABASE_TABLE_NAME))
{
DataTable inputParamDataTable = dsParams.Tables[PARAMETERS_INPUT.DATABASE_TABLE_NAME];
if (inputParamDataTable != null && inputParamDataTable.Columns.Contains(PARAMETERS_INPUT.FIELD_KEY))
{
foreach (DataRow row in inputParamDataTable.Rows)
{
object key = row[PARAMETERS_INPUT.FIELD_KEY];
if (key == null || key == DBNull.Value)
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY, DatabaseCompareOperator.Null, string.Empty);
}
else
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY, DatabaseCompareOperator.Equal, key.ToString());
}
}
}
}
#endregion
//已激活的工序。
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_STATUS, DatabaseCompareOperator.Equal, "1");
//工序名称
if (!string.IsNullOrEmpty(operationName))
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME, DatabaseCompareOperator.Like, string.Format("%{0}%", operationName));
}
//创建查询SQL
string sqlString = DatabaseTable.BuildQuerySqlStatement(operationsFields, interestColumns, conditions);
if (pageNo > 0 && pageSize > 0)//分页查询
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, dsReturn, POR_ROUTE_OPERATION_VER_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, dsReturn, new string[] { POR_ROUTE_OPERATION_VER_FIELDS.DATABASE_TABLE_NAME });
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetOperations Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 更新设备组数据。
/// </summary>
/// <param name="dsParams">包含设备组数据的数据集对象。</param>
/// <returns>包含执行结果的数据集对象。</returns>
public DataSet UpdateEquipmentGroups(DataSet dsParams)
{
DataSet resDS = new DataSet();
try
{
if (dsParams != null && dsParams.Tables.Contains(EMS_EQUIPMENT_GROUPS_FIELDS.DATABASE_TABLE_NAME) && dsParams.Tables.Contains(PARAMETERS_INPUT.DATABASE_TABLE_NAME))
{
DataTable equipmentGroupsDataTable = dsParams.Tables[EMS_EQUIPMENT_GROUPS_FIELDS.DATABASE_TABLE_NAME];
DataTable inputParamDataTable = dsParams.Tables[PARAMETERS_INPUT.DATABASE_TABLE_NAME];
#region Build Conditions List
List <Conditions> conditionsList = new List <Conditions>();
foreach (DataRow row in inputParamDataTable.Rows)
{
Conditions conditions = new Conditions();
Condition keyCondition;
if (row[PARAMETERS_INPUT.FIELD_KEY] != null && row[PARAMETERS_INPUT.FIELD_KEY] != DBNull.Value)
{
keyCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_KEY,
DatabaseCompareOperator.Equal, row[PARAMETERS_INPUT.FIELD_KEY].ToString());
}
else
{
keyCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_KEY,
DatabaseCompareOperator.Null, string.Empty);
}
conditions.Add(keyCondition);
Condition editorCondition;
if (row[PARAMETERS_INPUT.FIELD_EDITOR] != null && row[PARAMETERS_INPUT.FIELD_EDITOR] != DBNull.Value)
{
editorCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EDITOR,
DatabaseCompareOperator.Equal, row[PARAMETERS_INPUT.FIELD_EDITOR].ToString());
}
else
{
editorCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EDITOR,
DatabaseCompareOperator.Null, string.Empty);
}
conditions.Add(editorCondition);
Condition editTimeCondition;
if (row[PARAMETERS_INPUT.FIELD_EDIT_TIME] != null && row[PARAMETERS_INPUT.FIELD_EDIT_TIME] != DBNull.Value)
{
editTimeCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EDIT_TIME,
DatabaseCompareOperator.Equal, row[PARAMETERS_INPUT.FIELD_EDIT_TIME].ToString());
}
else
{
editTimeCondition = new Condition(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EDIT_TIME,
DatabaseCompareOperator.Null, string.Empty);
}
conditions.Add(editTimeCondition);
conditionsList.Add(conditions);
}
#endregion
EMS_EQUIPMENT_GROUPS_FIELDS equipmentGroupsFields = new EMS_EQUIPMENT_GROUPS_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildUpdateSqlStatements(equipmentGroupsFields, equipmentGroupsDataTable, conditionsList);
string equipmentGroupKey = string.Empty;
string equipmentGroupName = string.Empty;
string sqlString = string.Empty;
string editTime = string.Empty;
if (sqlStringList.Count > 0 && inputParamDataTable.Rows.Count > 0 && equipmentGroupsDataTable.Rows.Count > 0)
{
equipmentGroupKey = inputParamDataTable.Rows[0][PARAMETERS_INPUT.FIELD_KEY].ToString();
equipmentGroupName = equipmentGroupsDataTable.Rows[0][EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME].ToString();
sqlString = sqlStringList[0];
}
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Check Equipment Group Name
if (!string.IsNullOrEmpty(equipmentGroupName))
{
Conditions conditions = new Conditions();
conditions.Add(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME, DatabaseCompareOperator.Equal, equipmentGroupName);
conditions.Add(DatabaseLogicOperator.And, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_KEY, DatabaseCompareOperator.NotEqual, equipmentGroupKey);
string returnData = AllCommonFunctions.GetSpecifyTableColumnData(equipmentGroupsFields, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_NAME, conditions, transaction);
if (!string.IsNullOrEmpty(returnData))
{
throw new Exception("${res:FanHai.Hemera.Modules.EMS.EquipmentGroups.M0001}");
}
}
#endregion
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
editTime = AllCommonFunctions.GetSpecifyTableColumnData(equipmentGroupsFields, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EDIT_TIME, EMS_EQUIPMENT_GROUPS_FIELDS.FIELD_EQUIPMENT_GROUP_KEY,
equipmentGroupKey, transaction);
}
else
{
throw new Exception("${res:FanHai.Hemera.Module.Common.M0002}");
}
transaction.Commit();
}
catch (Exception ex)
{
LogService.LogError("UpdateEquipmentGroups Error: " + ex.Message);
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, string.Empty, editTime);
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, "${res:FanHai.Hemera.Module.Common.M0001}");
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, ex.Message);
LogService.LogError("UpdateEquipmentGroups Error: " + ex.Message);
}
return(resDS);
}
/// <summary>
/// 获取物料领用记录。
/// </summary>
/// <param name="dtParams">
/// 包含查询条件的数据表对象。
/// --------------------------------------
/// {DO}{STORE_NAME}{OPERATION_NAME}{OPERATOR}{CHARG}{LLIEF}{MATNR}{MBLNR}{RECEIVE_TIME_START}{RECEIVE_TIME_END}
/// --------------------------------------
/// DO=Query
/// ---------------------------------------
/// DO=其他, 线上仓名称使用逗号分隔,store1,store1...
/// --------------------------------------
/// </param>
/// <param name="pconfig">
/// 分页查询的配置对象。
/// </param>
/// <returns>包含物料领用详细信息的数据集对象。</returns>
public DataSet GetReceiveMaterialHistory(DataTable dtParams, ref PagingQueryConfig pconfig)
{
string msg = string.Empty;
DataSet dsReturn = new DataSet();
try
{
string sql = @"SELECT W.SAP_ISSURE_KEY,
W.MBLNR ,
W.CHARG ,
W.AUFNR ,
W.PRO_ID,
W.GRADE,
W.EFFICIENCY,
W.LLIEF ,
W.SUPPLIER_CODE,
W.ERFMG ,
W.MATNR ,
W.MATXT ,
W.ERFME ,
B.OPERATION_NAME ,
B.STORE_NAME ,
F.LOCATION_NAME,
W.RECEIVE_TIME ,
W.SHIFT_NAME ,
W.OPERATOR,
W.MEMO
FROM WST_STORE_MATERIAL A
INNER JOIN WST_STORE B ON A.STORE_KEY = B.STORE_KEY
INNER JOIN POR_MATERIAL C ON A.MATERIAL_KEY = C.MATERIAL_KEY
INNER JOIN WST_STORE_MATERIAL_DETAIL D ON A.STORE_MATERIAL_KEY = D.STORE_MATERIAL_KEY
INNER JOIN WST_SAP_ISSURE W ON D.STORE_MATERIAL_DETAIL_KEY = W.STORE_MATERIAL_DETAIL_KEY
INNER JOIN FMM_LOCATION F ON B.LOCATION_KEY = F.LOCATION_KEY
WHERE B.OBJECT_STATUS=1 ";
//判断是查询还是LOAD
string doName = Convert.ToString(dtParams.Rows[0]["DO"]).Trim();
if (doName == "Query")
{
//领料项目号不为空
string materialLot = Convert.ToString(dtParams.Rows[0]["CHARG"]).Trim();
if (!string.IsNullOrEmpty(materialLot))
{
sql += string.Format(" AND W.CHARG LIKE '{0}%'", materialLot.PreventSQLInjection());
}
//工单号
string orderNo = Convert.ToString(dtParams.Rows[0]["AUFNR"]).Trim();
if (!string.IsNullOrEmpty(orderNo))
{
sql += string.Format(" AND W.AUFNR ='{0}'", orderNo.PreventSQLInjection());
}
//产品号
string proId = Convert.ToString(dtParams.Rows[0]["PRO_ID"]).Trim();
if (!string.IsNullOrEmpty(proId))
{
sql += string.Format(" AND W.PRO_ID ='{0}'", proId.PreventSQLInjection());
}
//转换效率
string efficiency = Convert.ToString(dtParams.Rows[0]["EFFICIENCY"]).Trim();
if (!string.IsNullOrEmpty(efficiency))
{
sql += string.Format(" AND W.EFFICIENCY ='{0}'", efficiency.PreventSQLInjection());
}
//等级
string grade = Convert.ToString(dtParams.Rows[0]["GRADE"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND W.GRADE ='{0}'", grade.PreventSQLInjection());
}
//供应商名称
string supplierName = Convert.ToString(dtParams.Rows[0]["LLIEF"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND W.LLIEF ='{0}'", supplierName.PreventSQLInjection());
}
//线上仓名称
string storeName = Convert.ToString(dtParams.Rows[0]["STORE_NAME"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND B.STORE_NAME ='{0}'", storeName.PreventSQLInjection());
}
//最早发料时间不为空
string receiveStartTime = Convert.ToString(dtParams.Rows[0]["RECEIVE_TIME_START"]).Trim();
if (!string.IsNullOrEmpty(receiveStartTime))
{
sql += string.Format(" AND W.RECEIVE_TIME >='{0}'", receiveStartTime.PreventSQLInjection());
}
//最晚发料时间不为空
string receiveEndTime = Convert.ToString(dtParams.Rows[0]["RECEIVE_TIME_END"]).Trim() + " 23:59:59";
if (!string.IsNullOrEmpty(receiveEndTime))
{
sql += string.Format(" AND W.RECEIVE_TIME <= '{0}'", receiveEndTime.PreventSQLInjection());
}
}
else
{
string storeName = Convert.ToString(dtParams.Rows[0]["STORE_NAME"]).Trim();
if (!string.IsNullOrEmpty(storeName))
{
string sStoreName = UtilHelper.BuilderWhereConditionString("B.STORE_NAME", storeName.Split(','));
sql += sStoreName;
}
}
if (pconfig == null)
{
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
}
else
{
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sql, pconfig.PageNo, pconfig.PageSize, out pages,
out records, db, dsReturn, "STORE_MATERIAL");
pconfig.Pages = pages;
pconfig.Records = records;
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
}
catch (Exception ex)
{
msg = ex.Message;
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
LogService.LogError(string.Format("GetReceiveMaterialHistory Error:{0}", ex.Message));
}
return(dsReturn);
}
