Exemplo n.º 1
0
            /// <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);
                }
            }
Exemplo n.º 2
0
            /// <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);
                }
            }
Exemplo n.º 3
0
        /// <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);
            }
        }