/// <summary>
/// Returns the 'Earned' (estimated) hours from [EstimateMain] for the provided phase code and system name.
/// </summary>
/// <remarks>
/// Connects to the "BudgetVSTO" database.
/// </remarks>
/// <param name="phaseCode"></param>
/// <returns></returns>
public double GetEarnedHours(string systemName, string phaseCode)
{
if (ValidateSystemName(systemName))
{
SQLControl sql = new SQLControl(estimateDatabaseString);
sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@systemName", systemName);
sql.AddParam("@phaseCode", phaseCode);
sql.ExecQuery("SELECT TOP(1) EarnedHours FROM EstimateMain WHERE SystemName = @systemName AND PhaseCode = @phaseCode AND JobNumber = @jobNumber");
//sql.ExecQuery("SELECT * FROM EstimateMain");
if (sql.HasException())
{
throw new Exception(sql.Exception.ToString());
}
else if (sql.DBDT.Rows.Count == 0)
{
//MessageBox.Show("There doesn't appear to be a record for the system: " + systemName + " in Job Number: " + _jobNumber + ". This could be: \n" +
// "1. Because of a mismatch in system names between the name contained in the estimate sheet and the name contained in the report. \n" +
// "2. Because a phase code was included on the CSV report that wasn't included in the original Estimate." );
return(0);
}
else
{
double earnedHours = Convert.ToDouble(sql.DBDT.Rows[0].ItemArray[0].ToString());
return(earnedHours);
}
}
else
{
return(0);
}
}
/// <summary>
/// Returns the 'Original_Est_Hours' from [JC_PHASE_MASTER_MC] for the provided Job Number and Phase Code, where Cost Type is always "L" for "Labor"
/// </summary>
/// <param name="phaseCode"></param>
/// <returns></returns>
//public double GetBudgetedHoursByPhaseCode(string phaseCode)
//{
// phaseCode = phaseCode.Remove(4, 1);
// SQLControl sql = new SQLControl(spectrumDatabaseString);
// //sql.AddParam("@jobNumber", _jobNumber);
// sql.AddParam("@jobNumber", " " + _jobNumber);
// sql.AddParam("@phaseCode", phaseCode);
// sql.AddParam("@costType", "L");
// //sql.AddParam("@costType", "L");
// //sql.ExecQuery("SELECT TOP(1) Original_Est_Hours FROM JC_PHASE_MASTER_MC WHERE Phase_Code = @phaseCode AND Job_Number = @jobNumber AND Cost_Type = @costType");
// sql.ExecQuery("SELECT TOP(1) Projected_Hours FROM JC_PROJ_COST_HISTORY_MC WHERE Phase = @phaseCode AND Job = @jobNumber AND Cost_Type = @costType");
// //sql.ExecQuery("SELECT * FROM EstimateMain");
// if (sql.HasException())
// {
// throw new Exception(sql.Exception.ToString());
// }
// else if (sql.DBDT.Rows.Count == 0)
// {
// //MessageBox.Show("There doesn't appear to be a record for the system: " + systemName + " in Job Number: " + _jobNumber + ". This could be: \n" +
// // "1. Because of a mismatch in system names between the name contained in the estimate sheet and the name contained in the report. \n" +
// // "2. Because a phase code was included on the CSV report that wasn't included in the original Estimate." );
// return 0;
// }
// else
// {
// //int budgetedHours = Convert.ToInt32(sql.DBDT.Rows[0].ItemArray[0].ToString());
// double budgetedHours = Convert.ToDouble(sql.DBDT.Rows[0].ItemArray[0].ToString());
// return budgetedHours;
// }
//}
/// <summary>
/// Returns the "Projected_Hours" field from the "JC_PROJ_COST_HISTORY_MC" table in SPECTRUM.
/// </summary>
/// <param name="phaseCode"></param>
/// <returns></returns>
public double GetProjectedHoursByPhaseCode(string phaseCode)
{
if (_dataProvider.UsingExternalCache == true)
{
return(externalDataCache.GetProjectedHoursByPhaseCode(phaseCode));
}
else
{
try
{
phaseCode = phaseCode.Remove(4, 1);
//SQLControl sql = new SQLControl(spectrumDatabaseString);
SQLControl sql = new SQLControl(spectrumDatabaseString);
//sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@jobNumber", " " + _jobNumber);
sql.AddParam("@phaseCode", phaseCode);
sql.AddParam("@costType", "L");
sql.ExecQuery("SELECT SUM(Projected_Hours) FROM JC_PROJ_COST_HISTORY_MC WHERE Phase = @phaseCode AND Job = @jobNumber AND Cost_Type = @costType");
//sql.ExecQuery("SELECT * FROM EstimateMain");
if (sql.HasException())
{
return(externalDataCache.GetProjectedHoursByPhaseCode(phaseCode));
}
else if (sql.DBDT.Rows.Count == 0)
{
//MessageBox.Show("There doesn't appear to be a record for the system: " + systemName + " in Job Number: " + _jobNumber + ". This could be: \n" +
// "1. Because of a mismatch in system names between the name contained in the estimate sheet and the name contained in the report. \n" +
// "2. Because a phase code was included on the CSV report that wasn't included in the original Estimate." );
return(0);
}
else
{
//int budgetedHours = Convert.ToInt32(sql.DBDT.Rows[0].ItemArray[0].ToString());
double projectedHours = Convert.ToDouble(sql.DBDT.Rows[0].ItemArray[0].ToString());
return(projectedHours);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
throw;
}
}
}
//TODO: Refence this function in the SPECTRUM-related procedures above.
private bool ValidateSPECTRUMPhaseCode(string phaseCode)
{
SQLControl sql = new SQLControl(estimateDatabaseString);
sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@phaseCode", phaseCode);
sql.ExecQuery("SELECT * FROM EstimateMain WHERE JobNumber = @jobNumber AND PhaseCode = @phaseCode");
if (sql.HasException())
{
throw new Exception(sql.Exception.ToString());
}
else if (sql.DBDT.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Validates the provided system name by matching it with a corresponding entry in the 'EstimateMain' table.
/// </summary>
/// <param name="systemName"></param>
/// <returns></returns>
public bool ValidateSystemName(string systemName)
{
//To avoid displaying multiple error messages for the same system, a list of systems for which errors have been displayed is kept. When the next error
//is triggered, the message will only display if the system is not found in this list.
List <string> uniqueSystemsList = new List <string>();
SQLControl sql = new SQLControl(estimateDatabaseString);
sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@systemName", systemName);
sql.ExecQuery("SELECT * FROM EstimateMain WHERE SystemName = @systemName AND JobNumber = @jobNumber");
if (sql.HasException())
{
throw new Exception(sql.Exception.ToString());
}
else if (sql.DBDT.Rows.Count == 0)
{
if (uniqueSystemsList.Contains(systemName))
{
//Do nothing.
}
else
{
MessageBox.Show("The system: " + systemName + " was not found in the Estimate record. This could be due to a mismatch between the case/spelling used in " +
"CSV report file. Check the Estimate sheet for the job and make sure the system names match exactly. If they do not, alter the CSV file entry and try again");
//Add the system name to the uniqueSystemsList so that no more error messages appear for this system.
uniqueSystemsList.Add(systemName);
}
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Returns the summed earnedhours from EstimateMain for provided jobnumber and phase code
/// </summary>
/// <param name="phaseCode"></param>
/// <returns></returns>
public double GetTeamBudgetHours(string phaseCode)
{
if (ValidateEstimatePhaseCode(phaseCode))
{
SQLControl sql = new SQLControl(estimateDatabaseString);
//sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@jobNumber", _jobNumber);
sql.AddParam("@phaseCode", phaseCode);
//sql.AddParam("@costType", "L");
sql.ExecQuery("SELECT SUM(EarnedHours) FROM EstimateMain WHERE JobNumber = @jobNumber AND PhaseCode = @phaseCode");
//sql.ExecQuery("SELECT * FROM EstimateMain");
if (sql.HasException())
{
throw new Exception(sql.Exception.ToString());
}
else if (sql.DBDT.Rows.Count == 0 || sql.DBDT == null)
{
//MessageBox.Show("There doesn't appear to be a record for the system: " + systemName + " in Job Number: " + _jobNumber + ". This could be: \n" +
// "1. Because of a mismatch in system names between the name contained in the estimate sheet and the name contained in the report. \n" +
// "2. Because a phase code was included on the CSV report that wasn't included in the original Estimate." );
return(0);
}
else
{
//int budgetedHours = Convert.ToInt32(sql.DBDT.Rows[0].ItemArray[0].ToString());
//int teamBudgetHours = Convert.ToInt32(sql.DBDT.Rows[0].ItemArray[0].ToString());
double teamBudgetHours = Convert.ToDouble(sql.DBDT.Rows[0].ItemArray[0].ToString());
return(teamBudgetHours);
}
}
else
{
return(0);
}
}
/// <summary>
/// Commits the SystemEstimate objects contained in the provided list to the database.
/// </summary>
/// <param name="systemEstimateList"></param>
public void Commit(List <SystemEstimate> systemEstimateList)
{
//Convert the systemEstimateLIst into EstimateTransaction form, ready for database commit.
List <EstimateTransaction> transactionData = ConvertToEstimateTransaction(systemEstimateList);
if (transactionData != null && transactionData.Count != 0)
{
try
{
//Insert a new header entry into the database.
SQLControl sql = new SQLControl(ConnectionStringService.GetConnectionString("Estimate"));
sql.AddParam("@jobNumber", _jobNumber);
sql.AddOutputParam("@estimateID", SqlDbType.Int, 4);
int nextEstimateID = (int)sql.GetReturnValueFromStoredProcedure("spInsertEstimateHeader", "@estimateID");
//If the new estimate header was successfully added, continue with the process.
if (nextEstimateID > 0)
{
//Assign the estimate ID to the transaction objects.
//int newEstimateID = 12;
foreach (EstimateTransaction t in transactionData)
{
t.EstimateID = nextEstimateID;
}
//Send the transaction list to be inserted in the database.
InsertMultipleRecords(transactionData);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
throw;
}
}
else
{
throw new Exception("Estimate commit aborted because transaction data list failed to populate correctly.");
}
}
/// <summary>
/// Returns true if the job number is found in the estimating database. Only one estimate record is allowed per job number.
/// </summary>
/// <param name="jobNumber"></param>
/// <returns></returns>
public bool IsDuplicateJobNumber(string jobNumber)
{
using (SQLControl sql = new SQLControl(ConnectionStringService.GetConnectionString("Estimate")))
{
sql.AddParam("@jobNumber", jobNumber);
sql.ExecQuery("SELECT * FROM EstimateHeader WHERE JobNumber = @jobNumber");
if (sql.HasException())
{
throw new Exception("Error in SQLControl class while attempting to detect duplicate job numbers.");
}
if (sql.DBDT.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
};
}
