private void createAlertRunsEntry(int alertID, DateTime timestamp, int?MSID, string AlertRunsObjectID)
{
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.StoredProcedure, "sp_truckschedapp_LogAlertRuns", new SqlParameter("@pALERTID", alertID),
new SqlParameter("@pTIME", timestamp),
new SqlParameter("@pMSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@pOBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing createAlertRunsEntry(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing createAlertRunsEntry(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
}
private void updateAlertRunsEntry(int alertID, DateTime timestamp, int newRunNum, int?MSID, string AlertRunsObjectID)
{
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.AlertRuns SET TimeStampAlertLastRan = @TIME, TimesRan = @RUN WHERE AlertID = @ALERTID";
if (MSID == null && string.IsNullOrEmpty(AlertRunsObjectID))
{
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID));
}
else if (MSID != null && string.IsNullOrEmpty(AlertRunsObjectID))
{
sqlCmdText = sqlCmdText + " AND MSID = @MSID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@ALERTID", alertID));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID == null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID != null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID AND MSID = @MSID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing updateAlertRunsEntry(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing updateAlertRunsEntry(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
}
protected int getTimesAlertRan(int alertID, int?MSID, string AlertRunsObjectID)
{
int runNum = 0;
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT TimesRan FROM dbo.AlertRuns WHERE AlertID = @ALERTID";
if (MSID == null && string.IsNullOrEmpty(AlertRunsObjectID))
{
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID)));
}
else if (MSID != null && string.IsNullOrEmpty(AlertRunsObjectID))
{
sqlCmdText = sqlCmdText + " AND MSID = @MSID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@ALERTID", alertID)));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID == null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID))));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID != null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID AND MSID = @MSID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID))));
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing getTimesAlertRan(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing getTimesAlertRan(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
return(runNum);
}
public void CreateChangeLogEntryIfChanged(SqlConnection _sqlconn, bool writeLog, bool setbyUndo)
{
bool rethrow = false;
Exception ExToRethrow = new Exception();
SqlCommand sqlCmd = new SqlCommand();
SqlConnection sqlConn = _sqlconn;
try
{
using (var scope = new TransactionScope())
{
this._writeLogEvenIfNotNewValue = writeLog;
this._setByUndo = setbyUndo;
//string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
sqlCmd.Connection = sqlConn;
SqlParameter paramChangeType = new SqlParameter("@CHANGETYPE", SqlDbType.NVarChar);
SqlParameter paramTableName = new SqlParameter("@TABLE", SqlDbType.NVarChar);
SqlParameter paramColumnName = new SqlParameter("@COLNAME", SqlDbType.NVarChar);
SqlParameter paramTimestamp = new SqlParameter("@TIME", SqlDbType.DateTime);
SqlParameter paramChangeBy = new SqlParameter("@CHANGEBY", SqlDbType.Int);
SqlParameter paramDataType = new SqlParameter("@DATA", SqlDbType.NVarChar);
SqlParameter paramValueOld = new SqlParameter("@OLDVAL", SqlDbType.NVarChar);
SqlParameter paramValueNew = new SqlParameter("@NEWVAL", SqlDbType.NVarChar);
SqlParameter paramEventID = new SqlParameter("@EID", SqlDbType.Int);
SqlParameter paramPK1ColName = new SqlParameter("@PK1COL", SqlDbType.NVarChar);
SqlParameter paramPK1Value = new SqlParameter("@PK1VAL", SqlDbType.NVarChar);
SqlParameter paramPK2ColName = new SqlParameter("@PK2COL", SqlDbType.NVarChar);
SqlParameter paramPK2Value = new SqlParameter("@PK2VAL", SqlDbType.NVarChar);
SqlParameter paramPK3ColName = new SqlParameter("@PK3COL", SqlDbType.NVarChar);
SqlParameter paramPK3Value = new SqlParameter("@PK3VAL", SqlDbType.NVarChar);
SqlParameter paramSetBYUndo = new SqlParameter("@UNDO", SqlDbType.Bit);
paramChangeType.Value = this._ChangeType;
paramTableName.Value = this._TableName;
paramColumnName.Value = this._ColumnName;
paramTimestamp.Value = this._Timestamp;
paramChangeBy.Value = this._ChangedBy;
paramDataType.Value = this._DataType;
paramValueNew.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._Value_New);
paramEventID.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._EventID);
paramPK1ColName.Value = this._PK1_ColumnName;
paramPK1Value.Value = this._PK1_Value;
paramPK2ColName.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_ColumnName);
paramPK2Value.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_Value);
paramPK3ColName.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK3_ColumnName);
paramPK3Value.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK3_Value);
paramSetBYUndo.Value = this._setByUndo;
sqlCmd.Parameters.Add(paramChangeType);
sqlCmd.Parameters.Add(paramTableName);
sqlCmd.Parameters.Add(paramColumnName);
sqlCmd.Parameters.Add(paramTimestamp);
sqlCmd.Parameters.Add(paramChangeBy);
sqlCmd.Parameters.Add(paramDataType);
sqlCmd.Parameters.Add(paramValueNew);
sqlCmd.Parameters.Add(paramEventID);
sqlCmd.Parameters.Add(paramPK1ColName);
sqlCmd.Parameters.Add(paramPK1Value);
sqlCmd.Parameters.Add(paramPK2ColName);
sqlCmd.Parameters.Add(paramPK2Value);
sqlCmd.Parameters.Add(paramPK3ColName);
sqlCmd.Parameters.Add(paramPK3Value);
sqlCmd.Parameters.Add(paramSetBYUndo);
// !!!!!!!!!Update version of change log. Will be for 2.0!!!!!!!!!!
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//object oldVal = new object();
//bool hasOldValUpdated = false;
//if (this._ChangeType == ChangeLog.ChangeLogChangeType.UPDATE)
//{
// sqlCmdText = "SELECT " + this._ColumnName + " FROM " + this._TableName + " WHERE " + this._PK1_ColumnName + "= @PK1VAL";
// if (!string.IsNullOrEmpty(this._PK2_ColumnName))
// {
// sqlCmdText = sqlCmdText + " AND " + this._PK2_ColumnName + "= @PK2VAL";
// }
// if (!string.IsNullOrEmpty(this._PK3_ColumnName))
// {
// sqlCmdText = sqlCmdText + " AND " + this._PK3_ColumnName + " = @PK3VAL";
// }
// if (string.IsNullOrEmpty(this._PK2_ColumnName) && string.IsNullOrEmpty(this._PK3_ColumnName))
// {
// oldVal = SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PK1VAL", this._PK1_Value));
// }
// else if (!string.IsNullOrEmpty(this._PK2_ColumnName) && string.IsNullOrEmpty(this._PK3_ColumnName))
// {
// oldVal = SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PK1VAL", this._PK1_Value)
// , new SqlParameter("@PK2VAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_Value)));
// }
// else if (!string.IsNullOrEmpty(this._PK2_ColumnName) && !string.IsNullOrEmpty(this._PK3_ColumnName))
// {
// oldVal = SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PK1VAL", this._PK1_Value)
// , new SqlParameter("@PK2VAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_Value))
// , new SqlParameter("@PK3VAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK3_Value)));
// }
//}
//var needsNewLog = false;
//sqlCmdText = "";
//if (this._ChangeType == ChangeLogChangeType.UPDATE)
//{
// //Check if values are different. If different, create log
// //if date type, parse before compare
// if (this._DataType == ChangeLog.ChangeLogDataType.DATE || this._DataType == ChangeLog.ChangeLogDataType.DATETIME)
// {
// if (DBNull.Value.Equals(oldVal))
// {
// needsNewLog = true;
// }
// else
// {
// DateTime oldDate = DateTime.Parse(oldVal.ToString());
// DateTime newDate = DateTime.Parse(this._Value_New);
// if (oldDate.CompareTo(newDate) != 0)
// {
// needsNewLog = true;
// }
// }
// }
// else if (!string.Equals(oldVal.ToString(), this._Value_New)) //all other non date/datetime type
// {
// needsNewLog = true;
// }
//}
//else
//{
// needsNewLog = true;
//}
//if (needsNewLog || this._writeLogEvenIfNotNewValue)
//{
// sqlCmdText = "INSERT INTO dbo.ChangeLog VALUES (@CHANGETYPE, @TABLE, @COLNAME, @TIME, @CHANGEBY, @DATA, @OLDVAL, @NEWVAL, @EID, @PK1COL, @PK1VAL, @PK2COL, @PK2VAL, @PK3COL, @PK3VAL, @UNDO)";
// SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@CHANGETYPE", this._ChangeType),
// new SqlParameter("@TABLE", this._TableName),
// new SqlParameter("@COLNAME", this._ColumnName),
// new SqlParameter("@TIME", this._Timestamp),
// new SqlParameter("@CHANGEBY", this._ChangedBy),
// new SqlParameter("@DATA", this._DataType),
// new SqlParameter("@OLDVAL", TransportHelperFunctions.convertStringEmptyToDBNULL(oldVal)),
// new SqlParameter("@NEWVAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._Value_New)),
// new SqlParameter("@EID", TransportHelperFunctions.convertStringEmptyToDBNULL(this._EventID)),
// new SqlParameter("@PK1COL", this._PK1_ColumnName),
// new SqlParameter("@PK1VAL", this._PK1_Value),
// new SqlParameter("@PK2COL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_ColumnName)),
// new SqlParameter("@PK2VAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK2_Value)),
// new SqlParameter("@PK3COL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK3_ColumnName)),
// new SqlParameter("@PK3VAL", TransportHelperFunctions.convertStringEmptyToDBNULL(this._PK3_Value)),
// new SqlParameter("@UNDO", this._setByUndo));
//}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//get old value by querying
//create query using inputted tablename, columnname and primary cols and pkeys
if (this._ChangeType == ChangeLog.ChangeLogChangeType.UPDATE)
{
sqlCmd.CommandText = "SELECT " + this._ColumnName + " FROM " + this._TableName + " WHERE " + this._PK1_ColumnName + "= @PK1VAL";
if (!string.IsNullOrEmpty(this._PK2_ColumnName))
{
sqlCmd.CommandText = sqlCmd.CommandText + " AND " + this._PK2_ColumnName + "= @PK2VAL";
}
if (!string.IsNullOrEmpty(this._PK3_ColumnName))
{
sqlCmd.CommandText = sqlCmd.CommandText + " AND " + this._PK3_ColumnName + " = @PK3VAL";
}
object oldVal = sqlCmd.ExecuteScalar();
paramValueOld.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(oldVal);
sqlCmd.Parameters.Add(paramValueOld);
}
else
{
paramValueOld.Value = TransportHelperFunctions.convertStringEmptyToDBNULL(string.Empty);;
sqlCmd.Parameters.Add(paramValueOld);
}
var needsNewLog = false;
//check if new log needed
if (this._ChangeType == ChangeLogChangeType.UPDATE)
{
//Check if values are different. If different, create log
//if date type, parse before compare
if (this._DataType == ChangeLog.ChangeLogDataType.DATE || this._DataType == ChangeLog.ChangeLogDataType.DATETIME)
{
if (DBNull.Value.Equals(paramValueOld.Value))
{
needsNewLog = true;
}
else
{
DateTime oldDate = DateTime.Parse(paramValueOld.Value.ToString());
DateTime newDate = DateTime.Parse(this._Value_New);
if (oldDate.CompareTo(newDate) != 0)
{
needsNewLog = true;
}
}
}
else if (!string.Equals(paramValueOld.Value.ToString(), this._Value_New)) //all other non date/datetime type
{
needsNewLog = true;
}
}
else
{
needsNewLog = true;
}
if (needsNewLog || this._writeLogEvenIfNotNewValue)
{
sqlCmd.CommandText = "INSERT INTO dbo.ChangeLog VALUES (@CHANGETYPE, @TABLE, @COLNAME, @TIME, @CHANGEBY, @DATA, @OLDVAL, @NEWVAL, @EID, @PK1COL, @PK1VAL, @PK2COL, @PK2VAL, @PK3COL, @PK3VAL, @UNDO)";
sqlCmd.ExecuteNonQuery();
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in ChangeLog CreateChangeLogEntry(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
rethrow = true;
ExToRethrow = excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in ChangeLog CreateChangeLogEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
rethrow = true;
ExToRethrow = ex;
}
finally
{
if (rethrow)
{
throw ExToRethrow;
}
if (this._closeConnectionAfter && sqlConn != null && sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
sqlConn.Dispose();
}
}
}
public static void SetRejectionComment(int MSID, string COMMENT)
{
DateTime now = DateTime.Now;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "RejectionComment", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, TransportHelperFunctions.convertStringEmptyToDBNULL(COMMENT).ToString(), null, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.MainSchedule SET RejectionComment = @COMMENT " +
"WHERE (MSID = @MSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@COMMENT", TransportHelperFunctions.convertStringEmptyToDBNULL(COMMENT)),
new SqlParameter("@MSID", MSID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck SetRejectionComment(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
}
public int createNewEventLog(MainScheduleEvent MSEvent)
{
int nEventID = 0;
try
{
using (var scope = new TransactionScope())
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
nEventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.StoredProcedure, "sp_truckschedapp_LogMainScheduleEvent", new SqlParameter("@pMSID", MSEvent.MSID),
new SqlParameter("@pEventTypeID", MSEvent.EventTypeID),
new SqlParameter("@pEventSubTypeID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSEvent.EventSubtypeID)),
new SqlParameter("@pTimestamp", MSEvent.TimeStamp),
new SqlParameter("@pUserID", MSEvent.UserID),
new SqlParameter("@pIsHidden", MSEvent.isHidden)));
if (nEventID == 0)
{
throw new Exception("CreateNewEventLog Failed");
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in MainScheduleEventLogger createNewEventLog(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in MainScheduleEventLogger createNewEventLog(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
return(nEventID);
}