public static List <ProductionJobInfo> GetProdTechRecords()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("Select ProdTech, ProdOrder, Job_No, Rel_to_pro, Rqstd_shpg, PROJ_SHPG, Est_Time_, ACT_TIME, PERCENT_CO, Mtl_Comple, DevRequired, CSIInstall from ");
sb.Append("JobsDB where ProdOrder<>'' AND ProdTech<>'' Order by ProdTech,ProdOrder");
//var connectionFromConfiguration = WebConfigurationManager.ConnectionStrings["DBConnection"];
List <ProductionJobInfo> ProductionJobInfoList = new List <ProductionJobInfo>();
using (SqlConnection dbConnection = new SqlConnection(CONNECTION_STRING))//connectionFromConfiguration.ConnectionString))
{
try
{
dbConnection.Open();
//
// Fill in the sites
//
SqlCommand sqlCommand = new SqlCommand(sb.ToString(), dbConnection);
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
ProductionJobInfo pInfo = new ProductionJobInfo();
pInfo.TechName = sqlReader["ProdTech"].ToString();
pInfo.Priority = Convert.ToInt32(sqlReader["ProdOrder"]);
pInfo.Job_Number = Convert.ToInt32(sqlReader["Job_No"]);
string strRFDateTime = sqlReader["Rel_to_pro"].ToString();
DateTime RFDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strRFDateTime))
{
if (DateTime.TryParse(strRFDateTime, out RFDateTime))
{
pInfo.Release_For_Production_Date = RFDateTime.ToString("MM/dd/yy");
}
else
{
pInfo.Release_For_Production_Date = string.Empty;
}
}
string strReqShipmentDateTime = sqlReader["Rqstd_shpg"].ToString();
DateTime ReqShipmentDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strReqShipmentDateTime))
{
if (DateTime.TryParse(strReqShipmentDateTime, out ReqShipmentDateTime))
{
pInfo.Requested_Shipment_Date = ReqShipmentDateTime.ToString("MM/dd/yy");
}
else
{
pInfo.Requested_Shipment_Date = string.Empty;
}
}
string strProjShipmentDateTime = sqlReader["PROJ_SHPG"].ToString();
DateTime ProjShipmentDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strProjShipmentDateTime))
{
if (DateTime.TryParse(strProjShipmentDateTime, out ProjShipmentDateTime))
{
pInfo.Projected_Shipment_Date = ProjShipmentDateTime.ToString("MM/dd/yy");
}
else
{
pInfo.Projected_Shipment_Date = string.Empty;
}
}
string EstimatedTime = sqlReader["Est_Time_"].ToString();
if (!string.IsNullOrEmpty(EstimatedTime))
{
string match = Regex.Match(EstimatedTime, @"\d+").Value;
pInfo.Hours_Estimated = int.Parse(match);
}
string HoursSpent = sqlReader["ACT_TIME"].ToString();
if (!string.IsNullOrEmpty(HoursSpent))
{
string match = Regex.Match(HoursSpent, @"\d+").Value;
pInfo.Hours_Spent = int.Parse(match);
}
pInfo.Percent_Complete = (pInfo.Hours_Spent * 100.0f) / (pInfo.Hours_Estimated * 1.0f);
int MaterialComplete = Convert.ToInt32(sqlReader["Mtl_Comple"]);
pInfo.Material_Complete = MaterialComplete == 1 ? "X" : String.Empty;
pInfo.Material_Location = sqlReader["Mtl_Location"].ToString();
int ScadaRequired = Convert.ToInt32(sqlReader["DevRequired"]);
pInfo.SCADA_Required = ScadaRequired == 1 ? "X" : String.Empty;
int InstallRequired = Convert.ToInt32(sqlReader["CSIInstall"]);
pInfo.SCADA_Required = InstallRequired == 1 ? "X" : String.Empty;
ProductionJobInfoList.Add(pInfo);
}
}
sqlReader.Close();
}
catch (SqlException ex)
{
}
finally
{
dbConnection.Close();
dbConnection.Dispose();
// SessionVars.ProductionJobInfoList = ProductionJobInfoList;
}
} // end using
return(ProductionJobInfoList);
} // end method
public static List <ProductionJobInfo> GetProductionInfoData()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("Select ProdTech, ProdOrder, Job_No, Rel_to_pro, Rqstd_shpg, PROJ_SHPG, Est_Time_, ACT_TIME, PERCENT_CO, Mtl_Comple, Mtl_loc, Prodnotes, DevRequired, CSIInstall from ");
sb.Append("JobsDB where ProdOrder<>'' AND ProdTech<>'' Order by ProdTech,ProdOrder");
//var connectionFromConfiguration = WebConfigurationManager.ConnectionStrings["DBConnection"];
List <ProductionJobInfo> ProductionJobInfoList = new List <ProductionJobInfo>();
using (SqlConnection dbConnection = new SqlConnection(CONNECTION_STRING))//connectionFromConfiguration.ConnectionString))
{
try
{
dbConnection.Open();
//
// Fill in the sites
//
SqlCommand sqlCommand = new SqlCommand(sb.ToString(), dbConnection);
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
ProductionJobInfo pInfo = new ProductionJobInfo();
pInfo.TechName = sqlReader["ProdTech"].ToString();
pInfo.Priority = Convert.ToInt32(sqlReader["ProdOrder"]);
pInfo.Job_Number = Convert.ToInt32(sqlReader["Job_No"]);
string strRFDateTime = sqlReader["Rel_to_pro"].ToString();
DateTime RFDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strRFDateTime))
{
if (DateTime.TryParse(strRFDateTime, out RFDateTime))
{
pInfo.Release_For_Production_Date = RFDateTime.ToString("MM/dd/yy");
}
else
{
pInfo.Release_For_Production_Date = string.Empty;
}
}
string EstimatedTime = sqlReader["Est_Time_"].ToString();
if (!string.IsNullOrEmpty(EstimatedTime))
{
string match = Regex.Match(EstimatedTime, @"\d+").Value;
pInfo.Hours_Estimated = int.Parse(match);
}
string HoursSpent = sqlReader["ACT_TIME"].ToString();
if (!string.IsNullOrEmpty(HoursSpent))
{
string match = Regex.Match(HoursSpent, @"\d+").Value;
pInfo.Hours_Spent = int.Parse(match);
}
pInfo.Percent_Complete = (pInfo.Hours_Spent * 100.0f) / (pInfo.Hours_Estimated * 1.0f);
int MaterialComplete = Convert.ToInt32(sqlReader["Mtl_Comple"]);
pInfo.Material_Complete = MaterialComplete == 1 ? "X" : String.Empty;
string material_loc = sqlReader["MTL_LOC"].ToString();
pInfo.Material_Location = (material_loc.Length > 15) ? material_loc.Substring(0, 12) : material_loc;
int ScadaRequired = Convert.ToInt32(sqlReader["DevRequired"]);
pInfo.SCADA_Required = ScadaRequired == 1 ? "X" : String.Empty;
int InstallRequired = Convert.ToInt32(sqlReader["CSIInstall"]);
pInfo.SCADA_Required = InstallRequired == 1 ? "X" : String.Empty;
// ==================================================================================== Update Status Field
double reqDaysToship = 0;
double prodDaysToship = 0;
pInfo.Requested_Shipment_Date = String.Empty;
pInfo.Projected_Shipment_Date = "";
pInfo.Status = "";
// Read Requested Ship Date and calculate Days to ship (based upon this date):
try
{
string strReqShipmentDateTime = sqlReader["Rqstd_shpg"].ToString();
DateTime ReqShipmentDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strReqShipmentDateTime))
{
if (DateTime.TryParse(strReqShipmentDateTime, out ReqShipmentDateTime))
{
reqDaysToship = ReqShipmentDateTime.Subtract(DateTime.Now).TotalDays;
pInfo.Requested_Shipment_Date = ReqShipmentDateTime.ToString("MM/dd/yy");
}
}
}
catch (Exception ex)
{
}
// Read Production Estimated Shipping Date and calculate days to ship:
try
{
string strProjShipmentDateTime = sqlReader["PROJ_SHPG"].ToString();
DateTime ProjShipmentDateTime = DateTime.Now;
if (!string.IsNullOrEmpty(strProjShipmentDateTime))
{
if (DateTime.TryParse(strProjShipmentDateTime, out ProjShipmentDateTime))
{
prodDaysToship = ProjShipmentDateTime.Subtract(DateTime.Now).TotalDays;
pInfo.Projected_Shipment_Date = ProjShipmentDateTime.ToString("MM/dd/yy");
}
}
}
catch (Exception ex)
{
}
// Evaluate which data to use for estimated ship date:
if (pInfo.Projected_Shipment_Date != String.Empty) // Production data takes priority
{
//// If reqDaysToship > 1, then display this value, else display "ON HOLD"
//if (prodDaysToship < 10.0) // 10 days to update
//{
// pInfo.Status = "ON HOLD"; // No status, assume "ON track and not late"
// pInfo.ProdDaysToShip = "";
//}
//else
//{
pInfo.ProdDaysToShip = prodDaysToship.ToString("0");
// }
} // Projected Shipment Date == NULL
else if (pInfo.Requested_Shipment_Date != String.Empty) // Use this date for estimate
{
if (reqDaysToship < -8.0) // 8 days to update
{
pInfo.Status = "ON HOLD"; // No status, assume "ON track and not late"
pInfo.ProdDaysToShip = "";
}
else
{
pInfo.ProdDaysToShip = reqDaysToship.ToString("0");
}
}
string prodnotes = sqlReader["ProdNotes"].ToString();
if (prodnotes.ToUpper().Contains("Status:[") == true)
{
pInfo.Status = ExtractPattern("STATUS:[", "]", prodnotes.ToUpper());
if (pInfo.Status.Length > 15)
{
pInfo.Status = pInfo.Status.Substring(0, 15);
}
}
ProductionJobInfoList.Add(pInfo);
}
}
sqlReader.Close();
}
catch (SqlException ex)
{
}
finally
{
dbConnection.Close();
dbConnection.Dispose();
// SessionVars.ProductionJobInfoList = ProductionJobInfoList;
}
} // end using
return(ProductionJobInfoList);
} // end method
