/// <summary>
/// Runs the stored procedure Report in QB for the job. This needs ODBC conneciton, unfortunately.
/// </summary>
/// <param name="jobList"></param>
private static void threadStoredProcedure(SuperJob job)
{
Console.WriteLine("Stored Procedure Started");
ConsoleWriter.WriteLine("Stored Procedure(s) Started");
// Create SQL statement for grabbing table data
DataTable result_StoredProcedure = new DataTable();
try // Try to query
{
string customer = job.customerName;
OdbcDataAdapter dAdapter = new OdbcDataAdapter(
"sp_report JobProfitabilityDetail " +
"show RowData, AmountActualCost, AmountActualRevenue, AmountDifferenceActual " +
"parameters DateMacro = 'All', EntityFilterFullNames = '" + customer + "'",
con);
// Store query results into DataTable Object
dAdapter.Fill(result_StoredProcedure);
// Set Primary Key
// Replace Null Values in RowData Column
// Stored Procedure $ are of type Decimal
foreach (DataRow row in result_StoredProcedure.Rows)
{
if (row["RowData"] is System.DBNull)
{
row["RowData"] = "NO DATA" + result_StoredProcedure.Rows.IndexOf(row);
}
}
DataColumn[] key = new DataColumn[1];
key[0] = result_StoredProcedure.Columns["RowData"];
result_StoredProcedure.PrimaryKey = key;
// Map data to job objects
mapData(job, result_StoredProcedure);
}
catch (OdbcException objEx)
{
Console.WriteLine(objEx.Message);
}
}
/// <summary>
/// Maps data to job entries
/// </summary>
/// <param name="job"></param> Job analyzed
/// <param name="result_StoredProcedure"></param> Table that has all stored procedure data
private static void mapData(SuperJob job, DataTable result_StoredProcedure)
{
try // Try to map bad cost data
{
job.badCostData = (decimal)result_StoredProcedure.Rows.Find(job.partNumber)["AmountActualCost_1"];
}
catch (Exception e)
{
Console.Write(e.Message);
Console.WriteLine("No material data found for: " + job.customerName);
ConsoleWriter.WriteLine("No material data found for: " + job.customerName);
}
// Map total Costs
job.amountActualCost = (decimal)result_StoredProcedure.Rows[result_StoredProcedure.Rows.Count - 1]["AmountActualCost_1"];
// Map total Revenue
job.amountActualRevenue = (decimal)result_StoredProcedure.Rows[result_StoredProcedure.Rows.Count - 1]["AmountActualRevenue_1"];
try // Try to Map freight if found
{
job.freight = (decimal)result_StoredProcedure.Rows.Find("Freight")["AmountActualRevenue_1"];
}
catch (Exception e)
{
Console.Write(e.Message);
Console.WriteLine("No freight data found for: " + job.customerName);
ConsoleWriter.WriteLine("No freight data found for: " + job.customerName);
}
try // Try to map msc Tooling if found
{
job.miscToolingCost = (decimal)result_StoredProcedure.Rows.Find("MISC TOOLING")["AmountActualRevenue_1"];
}
catch (Exception e)
{
Console.Write(e.Message);
Console.WriteLine("No tooling data found for: " + job.customerName);
ConsoleWriter.WriteLine("No tooling data found for: " + job.customerName);
}
}
/// <summary>
/// Colors the cells based on job costing values
/// </summary>
/// <param name="job"></param> Job analyzed
/// <param name="range"></param> row that the job is in
/// <param name="mySheet"></param> current worksheet
private static void formatJobDoc(SuperJob job, Excel.Range range, Excel.Worksheet mySheet)
{
if (job.salesRep == "Not Fully Invoiced" || job.salesRep == "No Revenue for Job" || job.salesRep == "TimeClock Not Imported")
{
mySheet.Cells[range.Row, ExcelColumn.salesRep].Interior.Color = Color.FromArgb(96, 96, 96);
}
else
{
mySheet.Cells[range.Row, ExcelColumn.salesRep].Interior.ColorIndex = Excel.Constants.xlNone;
}
mySheet.Cells[range.Row, ExcelColumn.actualCost].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.actualRevenue].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.difference].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.freight].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.marlinFreight].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.miscTooling].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.costToCure].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.healingFactor].Interior.ColorIndex = Excel.Constants.xlNone;
if (job.salesRep == "Not Fully Invoiced" || job.salesRep == "No Revenue for Job" || job.salesRep == "TimeClock Not Imported")
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(96, 96, 96);
}
else if (job.grossMargin <= -.25)
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(255, 182, 193);
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Font.Color = Color.FromArgb(178, 34, 34);
}
else if (job.grossMargin <= 0)
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(255, 204, 229);
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Font.Color = Color.FromArgb(225, 0, 127);
}
else if (job.grossMargin < .25)
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(255, 128, 0);
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Font.Color = Color.FromArgb(0, 0, 0);
}
else if (job.grossMargin < .42)
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(255, 255, 0);
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Font.Color = Color.FromArgb(0, 0, 0);
}
else if (job.grossMargin >= .42)
{
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Interior.Color = Color.FromArgb(182, 255, 193);
mySheet.Cells[range.Row, ExcelColumn.grossMargin].Font.Color = Color.FromArgb(20, 100, 20);
}
if (job.salesRep == "Not Fully Invoiced" || job.salesRep == "No Revenue for Job" || job.salesRep == "TimeClock Not Imported")
{
mySheet.Cells[range.Row, ExcelColumn.unitHigh].Interior.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitMed].Interior.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitLow].Interior.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitFloor].Interior.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitHigh].Font.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitMed].Font.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitLow].Font.Color = Color.FromArgb(96, 96, 96);
mySheet.Cells[range.Row, ExcelColumn.unitFloor].Font.Color = Color.FromArgb(96, 96, 96);
}
else
{
mySheet.Cells[range.Row, ExcelColumn.unitHigh].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.unitMed].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.unitLow].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.unitFloor].Interior.ColorIndex = Excel.Constants.xlNone;
mySheet.Cells[range.Row, ExcelColumn.unitHigh].Font.Color = Color.FromArgb(0, 0, 0);
mySheet.Cells[range.Row, ExcelColumn.unitMed].Font.Color = Color.FromArgb(0, 0, 0);
mySheet.Cells[range.Row, ExcelColumn.unitLow].Font.Color = Color.FromArgb(0, 0, 0);
mySheet.Cells[range.Row, ExcelColumn.unitFloor].Font.Color = Color.FromArgb(0, 0, 0);
}
}
