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);
}