/// <summary>
/// Get Empty Check List Job Data Table
/// </summary>
/// <returns></returns>
/// Owner:Andy Gao 2011-07-21 08:45:55
private DataTable GetEmptyCheckListJobDataTable()
{
DataTable dataTable = EMS_CHECKLIST_JOBS_FIELDS.CreateDataTable(true);
dataTable.Columns.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_PM_NAME);
dataTable.Columns.Add(EMS_EQUIPMENTS_FIELDS.FIELD_EQUIPMENT_NAME);
dataTable.Columns.Add(EMS_CHECKLIST_FIELDS.FIELD_CHECKLIST_NAME);
return(dataTable);
}
public override bool Insert()
{
DataSet reqDS = new DataSet();
DataTable checkListJobsDataTable = EMS_CHECKLIST_JOBS_FIELDS.CreateDataTable(false);
Dictionary <string, string> dataRow = new Dictionary <string, string>()
{
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY, checkListJobKey },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME, checkListJobName },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_DESCRIPTION, description },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_STATE, checkListJobState },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY, equipmentKey },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_KEY, checkListKey },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATOR, Creator },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIMEZONE_KEY, CreateTimeZone },
{ EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIME, string.Empty }
};
FanHai.Hemera.Utils.Common.Utils.AddKeyValuesToDataTable(ref checkListJobsDataTable, dataRow);
checkListJobsDataTable.AcceptChanges();
reqDS.Tables.Add(checkListJobsDataTable);
string msg;
DataSet resDS = FanHai.Hemera.Utils.Common.Utils.ExecuteEngineMethod("FanHai.Hemera.Modules.EMS.EquipmentCheckListJobsEngine, FanHai.Hemera.Modules.EMS", "InsertCheckListJob", reqDS, out msg);
if (string.IsNullOrEmpty(msg))
{
CheckListJobName = resDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME) ? resDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME].ToString() : string.Empty;
CreateTimeStamp = resDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIMESTAMP) ? resDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIMESTAMP].ToString() : string.Empty;
CreateTime = resDS.ExtendedProperties.ContainsKey(PARAMETERS.OUTPUT_EDIT_TIME) ? resDS.ExtendedProperties[PARAMETERS.OUTPUT_EDIT_TIME].ToString() : string.Empty;
return(true);
}
else
{
MessageService.ShowError(msg);
return(false);
}
}
public override bool Update()
{
DataSet reqDS = new DataSet();
reqDS.ExtendedProperties.Add(PARAMETERS.INPUT_KEY, checkListJobKey);
reqDS.ExtendedProperties.Add(PARAMETERS.INPUT_EDITOR, Editor);
reqDS.ExtendedProperties.Add(PARAMETERS.INPUT_EDIT_TIME, EditTime);
this.DirtyList.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME, new DirtyItem(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME, string.Empty, string.Empty));
Editor = PropertyService.Get(PROPERTY_FIELDS.USER_NAME);
EditTimeZone = PropertyService.Get(PROPERTY_FIELDS.TIMEZONE);
DataTable checkListJobsDataTable = EMS_CHECKLIST_JOBS_FIELDS.CreateDataTable(false);
FanHai.Hemera.Utils.Common.Utils.AddKeyValuesToDataTable(ref checkListJobsDataTable, this.DirtyList);
checkListJobsDataTable.AcceptChanges();
reqDS.Tables.Add(checkListJobsDataTable);
string msg;
DataSet resDS = FanHai.Hemera.Utils.Common.Utils.ExecuteEngineMethod("FanHai.Hemera.Modules.EMS.EquipmentCheckListJobsEngine, FanHai.Hemera.Modules.EMS", "UpdateCheckListJob", reqDS, out msg);
if (string.IsNullOrEmpty(msg))
{
EditTime = resDS.ExtendedProperties.ContainsKey(PARAMETERS.OUTPUT_EDIT_TIME) ? resDS.ExtendedProperties[PARAMETERS.OUTPUT_EDIT_TIME].ToString() : string.Empty;
return(true);
}
else
{
MessageService.ShowError(msg);
return(false);
}
}
public DataSet CompleteCheckListJob(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.ExtendedProperties.Contains(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIMEZONE) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
string checkListJobKey = reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY].ToString();
string completeTimeStamp = string.Empty;
string editor = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDITOR].ToString();
string editTimeZone = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIMEZONE].ToString();
string editTime = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIME].ToString();
string sqlString = string.Empty;
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Update Check List Job Data
sqlString = string.Format(@"UPDATE EMS_CHECKLIST_JOBS
SET CHECKLIST_JOB_STATE = 'COMPLETED',
COMPLETE_TIMESTAMP = SYSDATE,
EDITOR = '{0}',
EDIT_TIME = SYSDATE,
EDIT_TIMEZONE = '{1}'
WHERE", editor, editTimeZone);
if (string.IsNullOrEmpty(editTime))
{
sqlString += " EDIT_TIME IS NULL";
}
else
{
sqlString += string.Format(" EDIT_TIME = TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", editTime);
}
sqlString += string.Format(" AND CHECKLIST_JOB_KEY = '{0}'", checkListJobKey);
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
#region Get Complete TimeStamp And Edit Time
EMS_CHECKLIST_JOBS_FIELDS checkListJobsFields = new EMS_CHECKLIST_JOBS_FIELDS();
List <string> interestFields = new List <string>();
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_COMPLETE_TIMESTAMP);
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME);
Conditions conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListJobKey);
sqlString = DatabaseTable.BuildQuerySqlStatement(checkListJobsFields, interestFields, conditions);
using (IDataReader dataReader = db.ExecuteReader(transaction, CommandType.Text, sqlString))
{
if (dataReader.Read())
{
completeTimeStamp = dataReader.GetDateTime(0).ToString();
editTime = dataReader.GetDateTime(1).ToString();
}
dataReader.Close();
}
#endregion
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
#region Process Check List Job PM Data
#region Get Check List Job PM Data
string pmType = string.Empty;
string pmKey = string.Empty;
sqlString = string.Format(@"SELECT CLJ.PM_TYPE, CLJ.PM_KEY
FROM EMS_CHECKLIST_JOBS CLJ
WHERE CLJ.CHECKLIST_JOB_KEY = '{0}'", checkListJobKey);
using (IDataReader dataReader = db.ExecuteReader(transaction, CommandType.Text, sqlString))
{
if (dataReader.Read())
{
pmType = dataReader.GetValue(0).ToString();
pmKey = dataReader.GetValue(1).ToString();
}
dataReader.Close();
}
#endregion
#region Schedule PM
if (pmType == "SCHEDULE" && pmKey.Length > 0)
{
sqlString = string.Format(@"SELECT PMS.BASE_ACTUAL_FINISH_TIME FROM EMS_PM_SCHEDULE PMS WHERE PMS.SCHEDULE_KEY = '{0}'", pmKey);
object scalar = db.ExecuteScalar(transaction, CommandType.Text, sqlString);
if (scalar != null && scalar != DBNull.Value)
{
if (scalar.ToString() == "1")
{
#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}'", pmKey);
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) <= 0)
{
throw new Exception("计算计划PM下次触发时间出错!");
}
#endregion
}
}
}
#endregion
#region Condition PM
if (pmType == "CONDITION" && pmKey.Length > 0)
{
//TODO: Not Implement
}
#endregion
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_COMPLETE_TIMESTAMP, completeTimeStamp);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, editTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("CompleteCheckListJob Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet StartCheckListJob(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.ExtendedProperties.Contains(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY) &&
reqDS.ExtendedProperties.Contains(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIMEZONE) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
string checkListJobKey = reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY].ToString();
string checkListKey = reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_KEY].ToString();
string startTimeStamp = string.Empty;
string editor = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDITOR].ToString();
string editTimeZone = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIMEZONE].ToString();
string editTime = reqDS.ExtendedProperties[PARAMETERS.INPUT_EDIT_TIME].ToString();
string sqlString = string.Empty;
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Insert Check List Job Item Data
sqlString = string.Format(@"INSERT INTO EMS_CHECKLIST_JOB_DATA
(CHECKLIST_JOB_KEY, CHECKITEM_KEY)
SELECT '{0}' AS CHECKLIST_JOB_KEY, CI.CHECKITEM_KEY
FROM EMS_CHECKLIST_ITEM CI
WHERE CI.CHECKLIST_KEY = '{1}'
ORDER BY CI.SEQUENCE ASC", checkListJobKey, checkListKey);
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) <= 0)
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
#region Update Check List Job Data
sqlString = string.Format(@"UPDATE EMS_CHECKLIST_JOBS
SET CHECKLIST_JOB_STATE = 'STARTED',
START_TIMESTAMP = SYSDATE,
EDITOR = '{0}',
EDIT_TIME = SYSDATE,
EDIT_TIMEZONE = '{1}'
WHERE", editor, editTimeZone);
if (string.IsNullOrEmpty(editTime))
{
sqlString += " EDIT_TIME IS NULL";
}
else
{
sqlString += string.Format(" EDIT_TIME = TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", editTime);
}
sqlString += string.Format(" AND CHECKLIST_JOB_KEY = '{0}'", checkListJobKey);
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
#region Get Start TimeStamp And Edit Time
EMS_CHECKLIST_JOBS_FIELDS checkListJobsFields = new EMS_CHECKLIST_JOBS_FIELDS();
List <string> interestFields = new List <string>();
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_START_TIMESTAMP);
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EDIT_TIME);
Conditions conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListJobKey);
sqlString = DatabaseTable.BuildQuerySqlStatement(checkListJobsFields, interestFields, conditions);
using (IDataReader dataReader = db.ExecuteReader(transaction, CommandType.Text, sqlString))
{
if (dataReader.Read())
{
startTimeStamp = dataReader.GetDateTime(0).ToString();
editTime = dataReader.GetDateTime(1).ToString();
}
dataReader.Close();
}
#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_CHECKLIST_JOBS_FIELDS.FIELD_START_TIMESTAMP, startTimeStamp);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, editTime);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("StartCheckListJob Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet DeleteCheckListJob(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
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 Delete 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.BuildDeleteSqlStatements(checkListJobsFields, conditionsList);
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 Delete Check List Job Item Data
db.ExecuteNonQuery(transaction, CommandType.Text, string.Format("DELETE EMS_CHECKLIST_JOB_DATA WHERE CHECKLIST_JOB_KEY = '{0}'", checkListJobKey));
#endregion
#region Delete Check List Job Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) > 0)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
}
else
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("DeleteCheckListJob Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet InsertCheckListJob(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null && reqDS.Tables.Contains(EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME))
{
DataTable checkListJobsDataTable = reqDS.Tables[EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME];
try
{
if (checkListJobsDataTable.Rows.Count > 0)
{
string checkListJobKey = checkListJobsDataTable.Rows[0][EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY].ToString();
string equipmentKey = checkListJobsDataTable.Rows[0][EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY].ToString();
string checkListJobName = string.Empty;
string createTimeStamp = string.Empty;
string createTime = string.Empty;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
#region Generate Check List Job Name
string equipmentName = string.Empty;
object scalar = db.ExecuteScalar(transaction, CommandType.Text, string.Format("SELECT EQUIPMENT_NAME FROM EMS_EQUIPMENTS E WHERE E.EQUIPMENT_KEY = '{0}'", equipmentKey));
if (scalar != null && scalar != DBNull.Value)
{
equipmentName = scalar.ToString();
}
if (string.IsNullOrEmpty(equipmentName))
{
throw new Exception("所选设备的名称为空!");
}
string checkListJobNamePrefix = DateTime.Now.ToString(COMMON_FORMAT.FAST_DATE_FORMAT) + equipmentName;
scalar = db.ExecuteScalar(transaction, CommandType.Text, string.Format("SELECT MAX(CLJ.CHECKLIST_JOB_NAME) FROM EMS_CHECKLIST_JOBS CLJ WHERE CLJ.CHECKLIST_JOB_NAME LIKE '{0}%'", checkListJobNamePrefix));
uint serialNumber = 1;
if (scalar != null && scalar != DBNull.Value)
{
string maxCheckListJobName = scalar.ToString();
serialNumber = Convert.ToUInt32(maxCheckListJobName.Substring(maxCheckListJobName.Length - 3, 3)) + 1;
}
checkListJobName = checkListJobNamePrefix + serialNumber.ToString("000");
#endregion
#region Generate Check List Job Insert SQL Statement
checkListJobsDataTable.Rows[0][EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME] = checkListJobName;
EMS_CHECKLIST_JOBS_FIELDS checkListJobsFields = new EMS_CHECKLIST_JOBS_FIELDS();
List <string> sqlStringList = DatabaseTable.BuildInsertSqlStatements(checkListJobsFields, checkListJobsDataTable);
#endregion
#region Insert Check List Job Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlStringList[0]) > 0)
{
#region Get Create TimeStamp And Create Time
List <string> interestFields = new List <string>();
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIMESTAMP);
interestFields.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIME);
Conditions conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListJobKey);
string sqlString = DatabaseTable.BuildQuerySqlStatement(checkListJobsFields, interestFields, conditions);
using (IDataReader dataReader = db.ExecuteReader(transaction, CommandType.Text, sqlString))
{
if (dataReader.Read())
{
createTimeStamp = dataReader.GetDateTime(0).ToString();
createTime = dataReader.GetDateTime(1).ToString();
}
dataReader.Close();
}
#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_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME, checkListJobName);
resDS.ExtendedProperties.Add(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CREATE_TIMESTAMP, createTimeStamp);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_EDIT_TIME, createTime);
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("InsertCheckListJob Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet DeleteCheckList(DataSet reqDS)
{
DataSet resDS = new DataSet();
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDITOR) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_EDIT_TIME))
{
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 Delete 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.BuildDeleteSqlStatements(checkListFields, conditionsList);
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 Check List Jobs Reference
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListKey);
EMS_CHECKLIST_JOBS_FIELDS checkListJobsFields = new EMS_CHECKLIST_JOBS_FIELDS();
List <string> interestColumns = new List <string>()
{
EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_KEY
};
string checkSqlString = DatabaseTable.BuildQuerySqlStatement(checkListJobsFields, interestColumns, conditions);
object scalar = db.ExecuteScalar(transaction, CommandType.Text, checkSqlString);
if (scalar != null && scalar != DBNull.Value)
{
throw new Exception("检查表单已经关联检查表单任务,不能删除!");
}
#endregion
#region Validate PM Schedule Reference
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_SCHEDULE_FIELDS.FIELD_CHECKLIST_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListKey);
EMS_PM_SCHEDULE_FIELDS schedulePMFields = new EMS_PM_SCHEDULE_FIELDS();
interestColumns = new List <string>()
{
EMS_PM_SCHEDULE_FIELDS.FIELD_SCHEDULE_KEY
};
checkSqlString = DatabaseTable.BuildQuerySqlStatement(schedulePMFields, interestColumns, conditions);
scalar = db.ExecuteScalar(transaction, CommandType.Text, checkSqlString);
if (scalar != null && scalar != DBNull.Value)
{
throw new Exception("检查表单已经关联计划PM,不能删除!");
}
#endregion
#region Validate PM Condition Reference
conditions = new Conditions();
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_PM_CONDITION_FIELDS.FIELD_CHECKLIST_KEY, GlobalEnums.DatabaseCompareOperator.Equal, checkListKey);
EMS_PM_CONDITION_FIELDS conditionPMFields = new EMS_PM_CONDITION_FIELDS();
interestColumns = new List <string>()
{
EMS_PM_CONDITION_FIELDS.FIELD_CONDITION_KEY
};
checkSqlString = DatabaseTable.BuildQuerySqlStatement(conditionPMFields, interestColumns, conditions);
scalar = db.ExecuteScalar(transaction, CommandType.Text, checkSqlString);
if (scalar != null && scalar != DBNull.Value)
{
throw new Exception("检查表单已经关联条件PM,不能删除!");
}
#endregion
#region Delete Check List Items Data
db.ExecuteNonQuery(transaction, CommandType.Text, string.Format("DELETE EMS_CHECKLIST_ITEM WHERE CHECKLIST_KEY = '{0}'", checkListKey));
#endregion
#region Delete Check List Data
if (db.ExecuteNonQuery(transaction, CommandType.Text, sqlString) <= 0)
{
throw new Exception("数据处理失败,请重新刷新数据后再提交!");
}
#endregion
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("DeleteCheckList Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
