public static bool checkIfEnabled()
{
bool isEnabled = false;
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT isEnabled FROM dbo.ManualWeights";
isEnabled = Convert.ToBoolean(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_Weights checkIfEnabled(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
return(isEnabled);
}
public static void updateManualInputValue(bool isEnabled)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
if (isEnabled == true)
{
sqlCmdText = "UPDATE dbo.ManualWeights SET isEnabled = 'true'";
}
else
{
sqlCmdText = "UPDATE dbo.ManualWeights SET isEnabled = 'false'";
}
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText);
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_Weights updateManualInputValue(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public bool createNewAuditLogEntry(AuditLog aLog)
{
bool didSucceed = false;
try
{
using (var scope = new TransactionScope())
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.StoredProcedure, "sp_truckschedapp_createNewAuditLogEntry", new SqlParameter("@pIPAddress", aLog.IPAddress),
new SqlParameter("@pUserID", aLog.UserID),
new SqlParameter("@pTimeStamp", aLog.TimeStamp),
new SqlParameter("@pPageAddress", aLog.PageAddress),
new SqlParameter("@pUserAgentStringID", aLog.UserAgentStringID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in AuditLog createNewAuditLogEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
}
finally
{
}
return(didSucceed);
}
public static void setCOFAComment(int SAMPLEID, 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, "Samples", "COFAComment", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, COMMENT, null, "SampleID", SAMPLEID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Samples SET COFAComment = @COFAComment " +
"WHERE (SampleID = @SampleID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@COFAComment", COMMENT),
new SqlParameter("@SampleID", SAMPLEID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload setCOFAComment(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public static Object GetLogList()
{
List <object[]> data = new List <object[]>();
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
TruckLogHelperFunctions.logListConnection(sql_connStr, data);
}
catch (SqlException excep)
{
string strErr = " SQLException Error in COFAUpload GetLogList(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload GetLogList(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
finally
{
}
return(data);
}
public int createNewEventLogAndTriggerExistingAlerts(string customAlertMsg)
{
int newEventID = 0;
try
{
newEventID = createNewEventLog(this.MSEvent);
if (newEventID > 0)
{
this.MSEvent.setEventID(newEventID);
EventAlertsHelper eaHelper = new EventAlertsHelper();
eaHelper.sendAlertsForEvent(newEventID, customAlertMsg);
}
else
{
throw new Exception("Error: Create New Log and Send Alert failed.)");
}
}
catch (Exception ex)
{
string strErr = " Exception Error in MainScheduleEventLogger LogEventAndTriggerExistingAlerts(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw ex;
}
return(newEventID);
}
private void setODBCConnectionFromConfig()
{
String as400_connStr;
try
{
as400_connStr = ConfigurationManager.ConnectionStrings["AS400ConnectionString"].ConnectionString;
if (as400_connStr == String.Empty)
{
throw new Exception("Missing ODBCConnectionString in web.config");
}
else
{
ODBC_Conn = new OdbcConnection();
ODBC_Conn = new OdbcConnection(as400_connStr);
if (ODBC_Conn.State != ConnectionState.Open)
{
ODBC_Conn.Open();
}
}
}
catch (Exception ex)
{
string strErr = " Exception Error in ConfigurationKeysHelper setODBCConnectionFromConfig(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
ErrorLogging.sendtoErrorPage(1);
throw;
}
}
public static void DeleteFileDBEntry(int SampleID, int MSID)
{
DateTime now = DateTime.Now;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID, TimeStamp, UserID, isHidden) VALUES (@MSID, 4098, @TSTAMP, @UserID, 'false'); " +
"SELECT SCOPE_IDENTITY()";
int eventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID),
new SqlParameter("@TSTAMP", now),
new SqlParameter("@UserID", zxpUD._uid)));
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "Samples", "FileID_COFA", now, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, null, eventID, "SampleID", SampleID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Samples SET FileID_COFA = NULL, COFAEventID = NULL WHERE SampleID = @SampleID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@SampleID", SampleID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload DeleteFileDBEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
private void getDeploymentData()
{
string sqlGetDeploymentInfo = "SELECT TOP 1 DI.DeploymentVersion, DI.DeploymentDate " +
"FROM dbo.DeploymentInfo AS DI " +
"ORDER BY DI.DeploymentVersion DESC";
DataSet dsDeploymentInfo;
try
{
dsDeploymentInfo = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlGetDeploymentInfo);
System.Data.DataRow row = dsDeploymentInfo.Tables[0].Rows[0];
string depVer = row["DeploymentVersion"].ToString();
DateTime depDate = Convert.ToDateTime(row["DeploymentDate"]);
string stringBuilder = "Version: " + depVer + " Date: " + depDate.ToShortDateString().ToString();
this.deploymentInfo.Text = stringBuilder;
}
catch (SqlException excep)
{
string strErr = " SQLException Error in Site.Master getDeploymentData(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in Site.Master getDeploymentData(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
}
public static ZXPUserData GetZXPUserDataFromCookie()
{
ZXPUserData zxpUD = new ZXPUserData();
try
{
HttpCookie cookie = HttpContext.Current.Request.Cookies[System.Web.Security.FormsAuthentication.FormsCookieName];
if (null != cookie)
{
if (!string.IsNullOrEmpty(cookie.Value))
{
System.Web.Security.FormsAuthenticationTicket ticket = System.Web.Security.FormsAuthentication.Decrypt(cookie.Value);
zxpUD = ZXPUserData.DeserializeZXPUserData(ticket.UserData);
}
}
}
catch (Exception ex)
{
string strErr = " Exception Error in ZXPUserData GetZXPUserDataFromCookie(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
throw;
}
return(zxpUD);
}
public static string[] ProcessFileAndData(string filename, string strUploadType)
//public static string[] ProcessFileAndData(int MSID, string filename, string strUploadType)
{
ErrorLogging.WriteEvent("Starting fileupload", EventLogEntryType.Information);
uploadType upT = (uploadType)Enum.Parse(typeof(uploadType), strUploadType);
try
{
string[] newFileAndPath = renameAndMoveFile(filename, upT);
if (2 == newFileAndPath.Length && !string.IsNullOrWhiteSpace(newFileAndPath[0].ToString()))
{
//TODO: ADD CODE FOR PROCESSING
return(newFileAndPath);
}
else
{
throw new Exception("renameAndMoveFile returned null or empty string");
}
}
catch (Exception ex)
{
string strErr = " Exception Error in TransportHelperFunctions ProcessFileAndData(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
HttpCookie cookie = Request.Cookies[System.Web.Security.FormsAuthentication.FormsCookieName];
if (null != cookie && !string.IsNullOrEmpty(cookie.Value))
{
System.Web.Security.FormsAuthenticationTicket ticket = System.Web.Security.FormsAuthentication.Decrypt(cookie.Value);
zxpUD = ZXPUserData.DeserializeZXPUserData(ticket.UserData);
}
else
{
Response.BufferOutput = true;
// Response.Redirect("/Account/Login.aspx?ReturnURL=/Default.aspx", false); mi4 url
Response.Redirect("Account/Login.aspx?ReturnURL=~/Default.aspx", false);//zxp live url
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Default Page_Load(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
}
public static void disableInspectionList(int InspectionListID)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.InspectionLists " +
"SET isHidden = 'true' WHERE InspectionListID = @InspectionListID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@InspectionListID", InspectionListID));
sqlCmdText = "UPDATE dbo.InspectionListsProducts " +
"SET isDisabled = 'true' WHERE InspectionListID = @InspectionListID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@InspectionListID", InspectionListID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_InspectionLists disableInspectionList(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public int createNewUserAgentStringEntry()
{
int newUserAgentStringID;
SQLDataConnectionHelper sqlConnHelper = new SQLDataConnectionHelper();
try
{
using (var scope = new TransactionScope())
{
sqlConnHelper.setSqlConnectionStringUsingConfiguration();
sqlConnHelper.openConnection();
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
newUserAgentStringID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.StoredProcedure, "sp_truckscheduleapp_insertNewUserAgentString", new SqlParameter("@pUserAgentString", this.userAgentString)));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in UserAgentString getUserAgentStringID(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw ex;
}
finally
{
if (sqlConnHelper.doesConnectionExist())
{
sqlConnHelper.closeConnection();
}
}
return(newUserAgentStringID);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
//HttpCookie cookie = Request.Cookies[System.Web.Security.FormsAuthentication.FormsCookieName];
//if (null != cookie && !string.IsNullOrEmpty(cookie.Value))
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
if (zxpUD._uid != new ZXPUserData()._uid)
{
if (!zxpUD._isAdmin) //make sure this matches whats in Site.Master and Default
{
Response.BufferOutput = true;
Response.Redirect("/ErrorPage.aspx?ErrorCode=5", false);
}
}
else
{
Response.BufferOutput = true;
//Response.Redirect("/Account/Login.aspx?ReturnURL=/AdminMainPage.aspx", false); mi4 url
Response.Redirect("Account/Login.aspx?ReturnURL=~/AdminMainPage.aspx", false);//zxp live url
}
}
catch (Exception ex)
{
string strErr = " Exception Error in AdminMainPage Page_Load(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
}
public static Object GetGridData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
sqlCmdText = string.Concat("SELECT MSID, ETA, Comments, PONumber, isDropTrailer ",
",TruckType, isRejected ",
", StatusText, isOpenInCMS, ProdCount, topProdID ",
", ProductName_CMS, PONumber_ZXPOutbound, LocationLong ",
"FROM dbo.vw_TrailerGridData ",
"ORDER BY ETA, PoNumber");
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in AdminCleanUp GetTrailerGridData(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
return(data);
}
public static Object getLoaderPO()
{
try
{
List <object[]> data = new List <object[]>();
data.Add(new object[] { "112233", "Inbound" });
data.Add(new object[] { "445566", "Outbound" });
data.Add(new object[] { "778899", "Inbound" });
data.Add(new object[] { "998877", "Outbound" });
data.Add(new object[] { "665544", "Inbound" });
data.Add(new object[] { "332211", "Outbound" });
return(data);
}
catch (SqlException excep)
{
string strErr = " SQLException Error in loaderInstructions getLoaderPO(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderInstructions getLoaderPO(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
if (sql_connStr == String.Empty)
{
throw new Exception("Missing SQLConnectionString in web.config");
}
Response.AddHeader("pragma", "no-cache");
Response.AddHeader("cache-control", "private, no-cache, no-store, must-revalidate");
Response.AddHeader("cache-control", "post-check=0, pre-check=0");
Response.AddHeader("Expires", DateTime.Now.AddDays(-1).ToString());
Response.AddHeader("Last-Modified", DateTime.Now.ToString());
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Cache.SetExpires(DateTime.Now.AddDays(-1));
Response.Cache.SetNoStore();
createMenuItems();
getDeploymentData();
}
catch (SqlException excep)
{
string strErr = " SQLException Error in Master Page_Load(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
}
catch (Exception ex)
{
string strErr = " Exception Error in Master Page_Load(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
}
public static void updateTankCurrentVolume(int tankID, double newVolume)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.Tanks SET CurrentTankVolume = @VOL WHERE TankID = @TANKID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@TANKID", tankID), new SqlParameter("@VOL", newVolume));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup updateTankCurrentVolume(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in dataProcessingAndCleanup updateTankCurrentVolume(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
}
public static Object getInspectionTypes()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT InspectionTypeID, InspectionType FROM dbo.InspectionTypes";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_InspectionLists getInspectionTypes(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
return(data);
}
public static void setNewSortOrder(int INSPECTIONLISTID, int INSPECTIONHEADERID, int SORTORDER, int InspectionListDetailsID)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.InspectionListsDetails SET SortOrder = @SortOrder WHERE (InspectionListDetailsID = @InspectionListDetailsID AND InspectionListID = @InspectionListID AND isHidden = 'false')";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@SortOrder", SORTORDER),
new SqlParameter("@InspectionListDetailsID", InspectionListDetailsID),
new SqlParameter("@InspectionListID", INSPECTIONLISTID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_InspectionLists setNewSortOrder(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public static bool setNewInspectionToListAndAssociate(int INSPECTIONLISTID, int INSPECTIONHEADERID, int SORTORDER)
{
bool needsVerificationTest = false;
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
//sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "Select ISNULL(needsVerificationTest, 0) FROM dbo.InspectionHeader WHERE InspectionHeaderID = @InspectionHeaderID";
needsVerificationTest = Convert.ToBoolean(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@InspectionHeaderID", INSPECTIONHEADERID)));
if (needsVerificationTest == true)
{
for (var i = 0; i < 2; i++)
{
if (i == 0)
{
sqlCmdText = "INSERT INTO dbo.InspectionListsDetails (InspectionListID, InspectionHeaderID, SortOrder, isHidden) " +
"VALUES (@InspectionListID, @InspectionHeaderID, @SortOrder, 'false'); " +
"SELECT CAST(scope_identity() AS int)";
}
else
{
SORTORDER = SORTORDER + 1;
sqlCmdText = "INSERT INTO dbo.InspectionListsDetails (InspectionListID, InspectionHeaderID, SortOrder, isHidden) " +
"VALUES (@InspectionListID, @InspectionHeaderID, @SortOrder, 'false'); " +
"SELECT CAST(scope_identity() AS int)";
}
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@InspectionListID", INSPECTIONLISTID),
new SqlParameter("@InspectionHeaderID", INSPECTIONHEADERID),
new SqlParameter("@SortOrder", SORTORDER));
}
}
else
{
sqlCmdText = "INSERT INTO dbo.InspectionListsDetails (InspectionListID, InspectionHeaderID, SortOrder, isHidden) " +
"VALUES (@InspectionListID, @InspectionHeaderID, @SortOrder, 'false'); " +
"SELECT CAST(scope_identity() AS int)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@InspectionListID", INSPECTIONLISTID),
new SqlParameter("@InspectionHeaderID", INSPECTIONHEADERID),
new SqlParameter("@SortOrder", SORTORDER));
}
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in Admin_InspectionLists setNewInspectionToListAndAssociate(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
return(needsVerificationTest);
}
public static bool isPONumOpenInCMSDB(int POnum)
{
TruckScheduleConfigurationKeysHelper_ODBC odbc_helper = new TruckScheduleConfigurationKeysHelper_ODBC();
try
{
//get current status from cms
odbc_helper.ODBC_Cmd.CommandText = "SELECT KAOSTS AS openStatus " +
"FROM CMSDAT.POH AS A WHERE KAPO# = ? " +
"UNION " +
"SELECT DCSTAT AS openStatus " +
"FROM CMSDAT.OCRH AS B WHERE DCORD# = ? ";
odbc_helper.ODBC_Cmd.Parameters.Add("POnum", OdbcType.Numeric).Value = POnum;
odbc_helper.ODBC_Cmd.Parameters.Add("POnum2", OdbcType.Numeric).Value = POnum;
odbc_helper.ODBC_Cmd.CommandType = System.Data.CommandType.Text;
object result = odbc_helper.ODBC_Cmd.ExecuteScalar();
if (result != null)
{
string POStatus = result.ToString().Trim().ToUpper();
if (POStatus == "C")
{ //C status is closed/complete; all else keep open
return(false);
}
}
else
{
//at the moment, keep open; need to confirm with zxp what to do when PO cannot be found
}
}
catch (OdbcException excep)
{
string strErr = " ODBCException Error in dataProcessingAndCleanup isPONumOpenInCMSDB(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 3;
ErrorLogging.sendtoErrorPage(3);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in dataProcessingAndCleanup isPONumOpenInCMSDB(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
if (odbc_helper.ODBC_Conn != null && odbc_helper.ODBC_Conn.State != ConnectionState.Closed)
{
odbc_helper.ODBC_Conn.Close();
odbc_helper.ODBC_Conn.Dispose();
}
}
return(true);
}
public static List <object[]> getProductsAndVolumeDataFromCMS()
{
TruckScheduleConfigurationKeysHelper_ODBC odbc_helper = new TruckScheduleConfigurationKeysHelper_ODBC();
List <object[]> productVolumeData = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
//Get product qty on hand details from CMS Server
odbc_helper.ODBC_Cmd.CommandText = "SELECT YCPART, SUM(IFNULL(YCQTYH,0)) AS SumQtyOnHand, IFNULL(YCMINQ, 0) AS MinQty, IFNULL(YCMAXQ, 0) AS MaxQty, YCUNIT AS Unit FROM CMSDAT.MRPIX1 " +
"GROUP BY YCMINQ, YCMAXQ, YCUNIT, YCPART";
OdbcDataAdapter dsAdapter = new OdbcDataAdapter(odbc_helper.ODBC_Cmd);
dsAdapter.Fill(dataSet);
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
productVolumeData.Add(row.ItemArray);
}
}
catch (OdbcException excep)
{
string strErr = "ODBCException Error in dataProcessingAndCleanup getProductVolumesFromCMS(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 3;
ErrorLogging.sendtoErrorPage(3);
throw excep;
}
catch (SqlException excep)
{
string strErr = "SQLException Error in dataProcessingAndCleanup getProductVolumesFromCMS(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in dataProcessingAndCleanup getProductVolumesFromCMS(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
if (odbc_helper.ODBC_Conn != null && odbc_helper.ODBC_Conn.State != ConnectionState.Closed)
{
odbc_helper.ODBC_Conn.Close();
odbc_helper.ODBC_Conn.Dispose();
}
}
return(productVolumeData);
}
public void setSqlConnectionString(string newConnectionString)
{
try
{
sql_connStr = newConnectionString;
}
catch (Exception ex)
{
string strErr = " Exception Error in SQLDataConnectionHelper setSqlConnectionString(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
}
}
public static List <object> GetTankDataByProduct(string CmsProduct)
{
List <object> tankData = new List <object>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT ISNULL(SUM(TankCapacity),0) AS ttlTankCapacity " +
",ISNULL(SUM(CurrentTankVolume),0) AS ttlCurrentTankVolume " +
",COUNT(T.TankID) AS numberOfTanksWithProducts " +
",ISNULL(MAX(TankProdData.ProdCount),0) AS maxProdCount " +
"FROM dbo.Tanks T " +
"INNER JOIN dbo.TankProducts TP ON TP.TankID = T.TankID " +
"INNER JOIN (SELECT SUB1_T.TankID, COUNT(SUB1_TP.ProductID_CMS) AS ProdCount " +
"FROM dbo.Tanks SUB1_T " +
"INNER JOIN dbo.TankProducts SUB1_TP ON SUB1_T.TankID = SUB1_TP.TankID " +
"WHERE SUB1_T.isDisabled = 0 AND SUB1_TP.isDisabled = 0 " +
"GROUP BY SUB1_T.TankID " +
") AS TankProdData ON TankProdData.TankID = TP.TankID " +
"WHERE TP.ProductID_CMS = @CMSPROD AND T.isDisabled = 0 AND TP.isDisabled = 0";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@CMSPROD", CmsProduct));
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
tankData.AddRange(row.ItemArray);
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup GetTankDataByProduct(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in dataProcessingAndCleanup GetTankDataByProduct(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
return(tankData);
}
public void TriggerExistingAlertForEvent(int EventID, string customAlertMsg)
{
try
{
EventAlertsHelper eaHelper = new EventAlertsHelper();
eaHelper.sendAlertsForEvent(EventID, customAlertMsg);
}
catch (Exception ex)
{
string strErr = " Exception Error in MainScheduleEventLogger TriggerExistingAlertForEvent(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw ex;
}
}
public void setSqlConnectionStringUsingConfiguration()
{
TruckScheduleConfigurationKeysHelper TSCKHelper = new TruckScheduleConfigurationKeysHelper();
try
{
sql_connStr = TSCKHelper.sql_connStr;
}
catch (Exception ex)
{
string strErr = " Exception Error in SQLDataConnectionHelper getSqlConnectionStringFromConfiguration(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
}
}
public static string[] ProcessFileAndData(string filename, string strUploadType)
{
try
{
string[] newFileAndPath = TransportHelperFunctions.ProcessFileAndData(filename, strUploadType);
return(newFileAndPath);
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload ProcessFileAndData(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
return(null);
}
public string checkDatatypeOfColumn(string tableName, string columnName, SqlConnection _sqlconn)
{
string sDatatype;
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
SqlParameter pTable = new SqlParameter("@TABLE", SqlDbType.NVarChar);
SqlParameter pColumn = new SqlParameter("@COLUMN", SqlDbType.NVarChar);
pTable.Value = tableName;
pColumn.Value = columnName;
sqlCmdText = "SELECT TOP 1 DATA_TYPE " +
"FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_NAME = @TABLE AND COLUMN_NAME = @COLUMN";
sDatatype = Convert.ToString(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@TABLE", tableName),
new SqlParameter("@COLUMN", columnName)));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in ChangeLog checkDatatypeOfColumn(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in ChangeLog checkDatatypeOfColumn(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
return(sDatatype);
}