private void btnUpdateJobCost_Click(object sender, EventArgs e)
        {
            SysconCommon.Common.Environment.Connections.SetOLEDBFreeTableDirectory(txtDataDir.Text);

            var validjobtypes_delim = Env.GetConfigVar<string>("tmtypes", "", true);
            var validjobtypes_strs  = validjobtypes_delim.Split(',');
            var validjobtypes       = validjobtypes_delim.Trim() == "" ? new long[] { } : validjobtypes_strs.Select(i => Convert.ToInt64(i));

            using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
            {
                long[] jobs = null;

                if (this.radioShowTMJobs.Checked)
                {
                    using (var jobtyps = con.GetTempDBF())
                    {
                        con.ExecuteNonQuery("create table {0} (jobtyp n(3, 0))", jobtyps);
                        foreach (var jt in validjobtypes)
                        {
                            con.ExecuteNonQuery("insert into {0} (jobtyp) values ({1})", jobtyps, jt);
                        }

                        jobs = (from x in con.GetDataTable("Jobnums", "select actrec.recnum from actrec join {0} jobtypes on actrec.jobtyp = jobtypes.jobtyp", jobtyps).Rows
                                select Convert.ToInt64(x["recnum"])).ToArray();
                    }
                }

                var job_selector = new MultiJobSelector(jobs);

                if (!(job_selector.ShowDialog() == System.Windows.Forms.DialogResult.Cancel))
                {
                    var jobnums = job_selector.SelectedJobNumbers.ToArray();
                    JobCostDbHelper jobCostDB = new JobCostDbHelper();

                    if (jobnums.Length > 0)
                    {
                        long phaseNum = 0;
                        int taxPartClassId = 0;
                        int costCode = 0;

                        try
                        {
                            //Based on the other parameters selected, run the Option 1 or Option 2.

                            // OPTION 1
                            if (this.radScanJobForTax.Checked)
                            {
                                int acctPeriod = Convert.ToInt32(cboAcctPeriod.SelectedItem);
                                int costCodeTax = 0;
                                FillCostCodeForTaxInfo(out costCodeTax);
                                FillTaxPartInfo(out taxPartClassId);

                                using (var progress = new ProgressDialog((4 * jobnums.Length) + 2))
                                {
                                    progress.Tick();
                                    progress.Text = "Starting scanning job costs for tax liabilities";
                                    progress.Show();

                                    foreach (long jobNum in jobnums)
                                    {
                                        // This routine scans job costs for tax liabilities
                                        jobCostDB.ScanForTaxLiability(dteStartDate.Value, dteEndDate.Value, jobNum, phaseNum, taxPartClassId, acctPeriod, costCodeTax, progress);
                                    }

                                    progress.Tick();
                                    progress.Text = "Finished scanning job costs for tax liabilities";
                                }

                                //MessageBox.Show("Finished scanning jobs for tax liabilities");
                            }

                            // OPTION 2
                            if (this.radCombineForBilling.Checked)
                            {
                                FillCostCodeInfo(out costCode);

                                using (var progress = new ProgressDialog((10 * jobnums.Length) + 2))
                                {
                                    progress.Tick();
                                    progress.Text = "Starting job cost consolidation";
                                    progress.Show();

                                    foreach (long jobNum in jobnums)
                                    {
                                        // The next two procedures are run together to create billable cost records that
                                        // are combined from distinct job cost records by cost type.
                                        jobCostDB.ConsolidateJobCost(dteStartDate.Value, dteEndDate.Value, jobNum, phaseNum, costCode, progress);
                                        jobCostDB.UpdateTMTJobCost(dteStartDate.Value, dteEndDate.Value, jobNum, phaseNum, progress);
                                    }

                                    progress.Tick();
                                    progress.Text = "Finished job cost consolidation";
                                }
                                //MessageBox.Show("Finished consolidating job costs");
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        }
                    }
                    else
                    {
                        MessageBox.Show("No jobs selected.", "Error", MessageBoxButtons.OK);
                    }
                }
            }
        }
        /// <summary>
        /// This routine scans all job costs that have not been billed, and match the 
        /// job number and phase as passed by the user.  If there has been no tax liability
        /// accrued on the invoice from which this job cost originated, we create a job 
        /// cost record.
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="jobNumber"></param>
        /// <param name="jobPhase"></param>
        /// <param name="taxPartClassId"></param>
        /// <param name="acctPeriod">Accounting period.</param>
        public void ScanForTaxLiability(DateTime startDate, DateTime endDate, long jobNumber, 
            long jobPhase, int taxPartClassId, int acctPeriod, int costCode, ProgressDialog progress)
        {
            //This part classification indicates which parts are considered tax parts
            int taxPartClass = taxPartClassId;
            double[] taxRates = new double[9];

            Env.Log("Started processing for tax liability.");

            using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
            {
                using
                    (
                        Env.TempDBFPointer
                        ActiveTaxParts = con.GetTempDBF(),
                        ActiveJobCosts = con.GetTempDBF(),
                        ActiveJobCostsTmp = con.GetTempDBF(),
                        ActiveAPLines = con.GetTempDBF(),
                        TaxJobCosts = con.GetTempDBF(),
                        TaxTemp = con.GetTempDBF()
                    )
                {
                    //Setting to zero. not needed for now.
                    jobPhase = 0;

                    //Get the data set version
                    decimal dataSetVersion = SMBHelper.GetDataSetVersion();
                    int curFiscalYear = 0;

                    if(dataSetVersion == -1)
                    {
                        //WAIT 'Invalid data directory' window
                        return;
                    }

                    //Get the current fiscal year if required
                    if(dataSetVersion >= 19)
                    {
                        curFiscalYear = SMBHelper.GetDataSetGLInfo("CURRENTFISCALYEAR");
                    }

                    progress.Tick();
                    progress.Text = string.Format("Scanning job# {0}", jobNumber);

                    //Get the tax rate details
                    int taxCode = con.GetScalar<int>("SELECT slstax from actrec where recnum = {0}", jobNumber);
                    string taxDetail = con.GetScalar<string>("Select ntetxt from taxdst where recnum = {0}", taxCode);

                    FillTaxRates(ref taxRates, taxDetail);

                    //Get the list of active tax rate parts for reference
                    int fldCount = con.ExecuteNonQuery("SELECT	recnum, prtnme, prtunt, csttyp, prtcls, prtcst FROM tkfprt WHERE prtcls = {0} "
                                                        + "INTO Table {1} ORDER BY recnum", taxPartClass, ActiveTaxParts);

                    //Get list of active job cost records to be billed
                    //TODO -   if JobNumber = zero, scan all jobs and phases
                    //         if JobPhase = zero, scan all phases for the jobs
                    fldCount = con.ExecuteNonQuery("SELECT * FROM jobcst "
                                                    + "WHERE jobnum = {0} "
                                                    + "AND phsnum = {1} "
                                                    + "AND status = 1 AND bllsts = 1 AND jobcst.trndte >= {2} AND jobcst.trndte <= {3} "
                                                    + "INTO Table {4}", jobNumber, jobPhase, startDate.ToFoxproDate(), endDate.ToFoxproDate(), ActiveJobCosts);

                    //Identify for each active job cost record if a tax burden has been applied in the
                    //entry of the originating transactions.   For now, that is only AP entries
                    //Get the list of AP invoices associated with the job costs
                    //1.0.8 - TaxTrnNum should not be truncated at this point.
                    fldCount = con.ExecuteNonQuery("SELECT ajc.*, NVL(a.recnum, 00000000) as aprecnum, NVL(a.invnum, SPACE(15)) as apinvnum, 000 as taxprtcnt, "
                                                    + "trnnum as TaxTrnNum, 000 as taxAccCnt "
                                                    + "FROM {0} ajc LEFT JOIN acpinv a ON ajc.lgrrec = a.lgrrec WHERE a.status <> 2 "
                                                    + "INTO Table {1}", ActiveJobCosts, ActiveJobCostsTmp);

                    //Get the list of AP lines used to generate the job costs
                    //Include a marking if the part number is from the tax part classification
                    //this indicates that it is a taxing part
                    fldCount = con.ExecuteNonQuery("SELECT DISTINCT a.recnum, a.linnum, a.prtnum, NVL(t.prtcls, 0) as prtcls, a.linqty, a.linprc, a.extttl, "
                                                + "a.actnum, a.subact FROM apivln a "
                                                + "JOIN {0} ajc ON a.recnum = ajc.aprecnum "
                                                + "LEFT JOIN tkfprt t ON a.prtnum = t.recnum "
                                                + "WHERE ajc.srcnum = 11 INTO TABLE {1}", ActiveJobCostsTmp, ActiveAPLines);

                    progress.Tick();
                    progress.Text = "Checking the tax accrual made on job cost";

                    //Mark each active job cost record as to whether there was a tax accrual/payment made on that
                    //job cost record.  This is done by counting the tax parts that were used on the invoice
                    DataTable dtJc1 = con.GetDataTable("ActiveJobCosts1", "Select * from {0}", ActiveJobCostsTmp);
                    foreach (DataRow dr in dtJc1.Rows)
                    {
                        decimal aprecNum = (decimal)dr["aprecnum"];
                        int count = con.GetScalar<int>("Select COUNT(*) from {0} WHERE recnum = {1} AND prtcls = {2}", ActiveAPLines, aprecNum, taxPartClass);
                        //If count is 0 then there is no point in updating the value as it is already set to 0 by default.
                        if (count > 0)
                        {
                            fldCount = con.ExecuteNonQuery("UPDATE {0} SET taxprtcnt = {1} WHERE aprecnum = {2}", ActiveJobCostsTmp, count, aprecNum);
                        }
                    }

                    //TODO: This query is little too complicated. To make it simpler
                    //Update taxacccnt
                    DataTable dt1 = con.GetDataTable("Dt1", "SELECT * from {0} WHERE usrnme <> \"TaxAcc\"", ActiveJobCostsTmp);
                    foreach (DataRow dr in dt1.Rows)
                    {
                        string taxTrnNum = (string)dr["taxtrnnum"];

                        //Version 1.0.8 - Should be scanning all job costs, not job costs temp for this part of the test
                        int count = con.GetScalar<int>("SELECT COUNT(*) FROM {0} WHERE trnnum = \"{1}\" AND usrnme = \"TaxAcc\"", ActiveJobCosts, taxTrnNum);
                        if (count > 0)
                        {
                            fldCount = con.ExecuteNonQuery("UPDATE {0} SET taxacccnt = {1} WHERE trnnum = \"{2}\" ",
                                                            ActiveJobCostsTmp, count, taxTrnNum);
                        }
                    }

                    //Check to see if each job cost has already had taxes accrued
                    //Create list of job cost records that must be accrued with taxes
                    fldCount = con.ExecuteNonQuery("SELECT	recnum, jobnum, phsnum, trnnum, dscrpt, trndte, {0} as entdte, actprd, 31 as srcnum, 1 as status, 1 as bllsts, "
                                        + "cstcde, csttyp, cstamt as origcstamt, 00000000.00 as cstamt, 00000000.00 as blgamt, 0 as taxabl, 000 as ovrrde, \"TaxAcc\" as usrnme, "
                                        + "{1} as postyr, vndnum FROM {2} WHERE taxprtcnt = 0 AND taxacccnt = 0 AND INLIST(srcnum,11) INTO Table {3}",
                                        DateTime.Today.ToFoxproDate(), curFiscalYear, ActiveJobCostsTmp, TaxJobCosts);

                    progress.Tick();
                    progress.Text = "Identifying the tax accrual records";

                    //Update the basic information to identify these as tax accrual records
                    DataTable taxJobCostDt = con.GetDataTable("TaxJobCosts", "select * from {0}", TaxJobCosts);
                    foreach (DataRow dr in taxJobCostDt.Rows)
                    {
                        decimal recNum = (decimal)dr["recnum"];
                        decimal cstType = (decimal)dr["csttyp"];
                        decimal origcStament = (decimal)dr["origcstamt"];

                        //1.0.8 - Don't truncate the transaction number, it does not need to be modified as it was originally
                        fldCount = con.ExecuteNonQuery("UPDATE {0} SET  "
                                                            + "dscrpt = ALLTRIM(SUBSTR(dscrpt,1,LEN(dscrpt)-4)) + \" Tax\", "
                                                            + "cstamt = origcstamt * {1}, "
                                                            + "blgamt = origcstamt * {2} WHERE recnum = {3}",
                                                            TaxJobCosts, (decimal)taxRates[((int)cstType - 1)],
                                                            (decimal)taxRates[((int)cstType - 1)], recNum);
                    }

                    //Set this so that FoxPro doesn't try to insert null values in empty columns
                    SetNullOff(con);

                    progress.Tick();
                    progress.Text = "Inserting tax records";

                    //Add the records
                    //int taxJobCostsCount = con.GetScalar<int>("select count(*) from {0}", TaxJobCosts);
                    //1.0.9 - Match the vendor number from the original record in the new tax record
                    DataTable dtTaxJobCosts = con.GetDataTable("TaxJobCosts","select * from {0}", TaxJobCosts);
                    if (dtTaxJobCosts != null && dtTaxJobCosts.Rows.Count > 0)
                    {
                        fldCount = 0;
                        foreach (DataRow dr in dtTaxJobCosts.Rows)
                        {
                            int recNum = con.GetScalar<int>("SELECT MAX(recnum) from jobcst") + 1;
                            DateTime trnDate = (DateTime)dr["trndte"];
                            DateTime eDate = (DateTime)dr["entdte"];
                            decimal cost = (decimal)dr["cstamt"];

                            if (cost != 0)
                            {
                                if (dataSetVersion >= 19.2M)
                                {
                                    con.ExecuteNonQuery("INSERT INTO jobcst ( recnum, jobnum, phsnum, trnnum, dscrpt, trndte, entdte, actprd, "
                                                                           + "srcnum, status, bllsts, cstcde, csttyp, cstamt, blgamt, taxabl, ovrrde, postyr, usrnme, vndnum ) "
                                                                           + "VALUES ({0}, {1}, {2}, \"{3}\", \"{4}\", {5}, {6}, "
                                                                           + "{7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, \"{18}\",{19}) "
                                                                           , recNum, dr["jobnum"], dr["phsnum"], dr["trnnum"], dr["dscrpt"], trnDate.ToFoxproDate(),
                                                                           eDate.ToFoxproDate(), acctPeriod, dr["srcnum"], dr["status"], dr["bllsts"], costCode, dr["csttyp"],
                                                                           dr["cstamt"], dr["blgamt"], dr["taxabl"], dr["ovrrde"], curFiscalYear, dr["usrnme"], dr["vndnum"]);
                                }
                                else
                                {
                                    con.ExecuteNonQuery("INSERT INTO jobcst ( recnum, jobnum, phsnum, trnnum, dscrpt, trndte, entdte, actprd, "
                                                                           + "srcnum, status, bllsts, cstcde, csttyp, cstamt, blgamt, taxabl, ovrrde, usrnme, vndnum ) "
                                                                           + "VALUES ({0}, {1}, {2}, \"{3}\", \"{4}\", {5}, {6}, "
                                                                           + "{7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, \"{17}\", {18}) "
                                                                           , recNum, dr["jobnum"], dr["phsnum"], dr["trnnum"], dr["dscrpt"], trnDate.ToFoxproDate(),
                                                                           eDate.ToFoxproDate(), acctPeriod, dr["srcnum"], dr["status"], dr["bllsts"], costCode, dr["csttyp"],
                                                                           dr["cstamt"], dr["blgamt"], dr["taxabl"], dr["ovrrde"], dr["usrnme"], dr["vndnum"]);
                                }
                                fldCount++;
                            }
                        }
                        Env.Log("{0} fields inserted in table jobcst", fldCount);
                    }
                }
                //Set null on again
                SetNullOn(con);
            }
            Env.Log("Finished processing for tax liability.");
        }
        /// <summary>
        /// This method updates a field "pieces" in the job cost record so we can identify which
        /// records should be included in a supplemental detail report.
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="jobNumber"></param>
        /// <param name="jobPhase"></param>
        // 1.0.10 - Added logic to include all billed records, not just those that have had a combined
        // cost included in the invoice (law)
        public void UpdateTMTJobCost(DateTime startDate, DateTime endDate, long jobNumber, long jobPhase, ProgressDialog progress)
        {
            using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
            {
                using
                    (
                        Env.TempDBFPointer
                        CombinedCosts = con.GetTempDBF(),
                        AllCosts = con.GetTempDBF(),
                        RecordList = con.GetTempDBF(),
                        ACRInvList = con.GetTempDBF()
                    )
                {
                    progress.Tick();
                    progress.Text = string.Format("Start updating the TMT job costs");

                    //1.0.10 All job costs that have been billed during the selected time period by the user (law)
                    int modifiedFldCount = con.ExecuteNonQuery("SELECT * FROM jobcst WHERE status = 1 AND acrinv > 0 AND BETWEEN(trndte,{0},{1}) "
                                + "AND usrnme <> \"Combine\" into table {2}", startDate.ToFoxproDate(), endDate.ToFoxproDate(), AllCosts);

                    //Find all the consolidated job costs in the selected time period
                    //That have been invoiced through T&M
                    modifiedFldCount = con.ExecuteNonQuery("SELECT * FROM jobcst WHERE status = 1 AND acrinv > 0 AND BETWEEN(trndte,{0},{1}) "
                                                    + "AND usrnme == \"Combine\" into table {2}", startDate.ToFoxproDate(), endDate.ToFoxproDate(), CombinedCosts);

                    Env.Log("{0} T&M records found in jobcst table for updation.", modifiedFldCount);

                    //Build a list of the job cost records that must be updated with the AR T&M invoice number
                    con.ExecuteNonQuery("create table {0} (recnum		n(10,0), acrinv		n(10,0))", RecordList);

                    progress.Tick();
                    progress.Text = string.Format("Reading from the memo fields of Combined Costs");

                    //Read each record from the memo fields of the Combined Costs
                    DataTable _combinedCosts = con.GetDataTable("CombinedCosts", "SELECT * FROM {0}", CombinedCosts);
                    foreach (DataRow dr in _combinedCosts.Rows)
                    {
                        string nteTxt = (dr != null) ? (string)dr["ntetxt"] : string.Empty;
                        char[] delimiterChars = { '\n', '|' };
                        string[] nteTxts = nteTxt.Split(delimiterChars);

                        if (nteTxts.Length >= 2)
                        {
                            for (int i = 1; i < nteTxts.Length; i++)
                            {
                                double recNum = 0.0;
                                if (double.TryParse(nteTxts[i], out recNum))
                                {
                                    con.ExecuteNonQuery("INSERT INTO {0} VALUES ({1}, {2})", RecordList, recNum, dr["acrinv"]);
                                }
                            }
                        }
                    }

                    // modifiedFldCount = con.ExecuteNonQuery("SELECT distinct acrinv FROM {0} INTO table {1}", CombinedCosts, ACRInvList);
                    // 1.0.10 (law) the ACRInvList has to include all invoices - even if there were no combined records in those invoices
                    modifiedFldCount = con.ExecuteNonQuery("SELECT distinct acrinv FROM {0} INTO table {1}", AllCosts, ACRInvList);

                    progress.Tick();
                    progress.Text = string.Format("Updating TMT job cost records");

                    //Reset all the records to blank - this is specific to the list of invoices we are updating
                    modifiedFldCount = con.ExecuteNonQuery("UPDATE jobcst SET pieces = 0 from {0} _ACRInvList WHERE jobcst.acrinv = _ACRInvList.acrinv", ACRInvList);
                    //1.0.10 Reset all the combined records to blank - again, this must be specific to the list of invoices we are updating
                    modifiedFldCount = con.ExecuteNonQuery("UPDATE jobcst SET pieces = 0 from {0} _RecordList WHERE _RecordList.recnum = jobcst.recnum", RecordList);

                    //Now update the job cost records from the standardn, non-Combined records - excluding combined records
                    modifiedFldCount = con.ExecuteNonQuery("UPDATE jobcst SET pieces = _ACRInvList.acrinv from {0} _ACRInvList WHERE jobcst.acrinv = _ACRInvList.acrinv "
                                                    + "AND jobcst.usrnme <> \"Combine\"", ACRInvList);

                    //Update the combined records
                    modifiedFldCount = con.ExecuteNonQuery("UPDATE jobcst SET pieces = _RecordList.acrinv from {0} _RecordList WHERE _RecordList.recnum = jobcst.recnum", RecordList);

                    Env.Log("{0} T&M records updated in jobcst table.", modifiedFldCount);
                }

                progress.Tick();
                progress.Text = string.Format("Finished updating the TMT job costs");

                //Set default null setting to on again
                SetNullOn(con);
            }
        }
        /// <summary>
        /// Scans the jobcst data table and combines job costs into an additional record for billing purposes.
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="jobNumber"></param>
        /// <param name="jobPhase"></para
        /// <param name="costCode"></param>
        public void ConsolidateJobCost(DateTime startDate, DateTime endDate, long jobNumber, long jobPhase, int costCode, ProgressDialog progress)
        {
            using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
            {
                using
                    (
                        Env.TempDBFPointer
                        ActiveJobCosts = con.GetTempDBF(),
                        MatCosts = con.GetTempDBF(),
                        SubMatCosts = con.GetTempDBF(),
                        NewMatCosts = con.GetTempDBF(),
                        NewSubMatCosts = con.GetTempDBF()
                    )
                {
                    Env.Log("Job cost consolidation started for job number: {0}", jobNumber);

                    //Get the data set version
                    decimal dataSetVersion = SMBHelper.GetDataSetVersion();
                    int curFiscalYear = 0;

                    if (dataSetVersion == -1)
                    {
                        //WAIT 'Invalid data directory' window
                        return;
                    }

                    //Get the current fiscal year if required
                    if (dataSetVersion >= 19.0M)
                    {
                        curFiscalYear = SMBHelper.GetDataSetGLInfo("CURRENTFISCALYEAR");
                    }

                    progress.Tick();
                    progress.Text = string.Format("Job cost consolidation started for job# {0}", jobNumber);

                    jobPhase = 0;
                    int modifiedFldCount = 0;

                    //Get list of active job cost records to be billed
                    modifiedFldCount = con.ExecuteNonQuery("SELECT * FROM jobcst WHERE jobnum = {0} "
                                                            + "AND phsnum = {1} AND status = 1 AND bllsts = 1 "
                                                            + "AND jobcst.trndte >= {2} AND jobcst.trndte <= {3} AND usrnme <> \"Combine\" INTO TABLE {4}",
                                                             jobNumber, jobPhase, startDate.ToFoxproDate(), endDate.ToFoxproDate(), ActiveJobCosts);

                    progress.Tick();
                    progress.Text = string.Format("Getting list of contract and subcontract material records\n to be combined by cost code");

                    //Get the list of Material Records to be combined by cost type
                    modifiedFldCount = con.ExecuteNonQuery("SELECT * FROM {0} WHERE csttyp = 1 INTO TABLE {1}", ActiveJobCosts, MatCosts);

                    //Get the list of Subcontract Material Records to be combined by cost type
                    modifiedFldCount = con.ExecuteNonQuery("SELECT * FROM {0} WHERE csttyp = 7 INTO TABLE {1}", ActiveJobCosts, SubMatCosts);

                    progress.Tick();
                    progress.Text = string.Format("Consolidating into a single billing record");
                    //
                    DataTable _matCosts = con.GetDataTable("MatCosts", "Select * from {0}", MatCosts);
                    string MatCostDetail = "The following job cost records have been consolidated into a single billing record:" + "|";

                    foreach (DataRow dr in _matCosts.Rows)
                    {
                        MatCostDetail = MatCostDetail + Convert.ToString(dr["recnum"]).PadLeft(7) + "|";
                    }

                    Env.Log("Material cost detail memo: {0}", MatCostDetail);

                    DataTable _subMatCosts = con.GetDataTable("MatCosts", "Select * from {0}", SubMatCosts);
                    string SubMatDetail = "The following job cost records have been consolidated into a single billing record:" + "|";

                    foreach (DataRow dr in _subMatCosts.Rows)
                    {
                        SubMatDetail = SubMatDetail + Convert.ToString(dr["recnum"]).PadLeft(7) + "|";
                    }
                    Env.Log("Sub-material cost detail memo: {0}", SubMatDetail);

                    progress.Tick();
                    progress.Text = string.Format("Combining material costs into a single record");

                    // Combine the material costs into two records for appending to the actual job costs
                    //There is some special math here that will ensure that if the user recalculates the
                    //t&m records, the billing total will be correct.
                    //LOCAL ARRAY tvalue(1,1)

                    //Combine the material costs into a single record for appending to the actual job costs
                    int matCostCount = con.GetScalar<int>("select count(*) from {0}", MatCosts);
                    if (matCostCount > 0)
                    {
                        decimal sumBlgAmt = 0.0m;
                        decimal sumBlgTotal = 0.0m;
                        decimal recBillAmout = 0.0m;
                        sumBlgAmt = con.GetScalar<decimal>("SELECT SUM(blgamt) FROM {0}", MatCosts);
                        sumBlgTotal = con.GetScalar<decimal>("SELECT SUM(blgttl) FROM {0}", MatCosts);

                        if (sumBlgTotal != 0.0m)
                        {
                            recBillAmout = (sumBlgAmt * sumBlgAmt) / sumBlgTotal;
                            recBillAmout = Math.Round(recBillAmout, 2);
                        }

                        string formattedED = string.Format("'{0} {1}'", endDate.ToString("MM/dd/yy"), "Mat");

                        #region "Ver 1.0.2"
                        //modifiedFldCount = con.ExecuteNonQuery("SELECT {0} as jobnum, {1} as phsnum, {2} as trnnum, \"Materials\" as dscrpt, {3} as trndte,"
                        //                        + "{4} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts,"
                        //                        + "{5} as cstcde, 1 as csttyp, SUM(cstamt) as blgamt, SUM(blgttl) as blgttl, SUM(shwamt) as shwamt, SUM(ovhamt) as ovhamt, "
                        //                        + "SUM(pftamt) as pftamt, 1 as ovrrde, \"Combine\" as usrnme FROM {6} INTO TABLE {7}",
                        //                        jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(), costCode, MatCosts, NewMatCosts);
                        #endregion

                        #region "Ver 1.0.3 onwards"
                        //Create the first record which will be the non-taxable amount of the combined materials
                        modifiedFldCount = con.ExecuteNonQuery("SELECT {0} as jobnum, {1} as phsnum, {2} as trnnum, \"Materials\" as dscrpt, {3} as trndte,"
                                                + "{4} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts,"
                                                + "{5} as cstcde, 1 as csttyp, {6} as blgamt, {7} as blgttl, 0 as taxabl,"
                                                + " 1 as ovrrde, {8} as postyr, \"Combine\" as usrnme FROM {9} INTO TABLE {10}",
                                                jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(),
                                                costCode, recBillAmout, sumBlgAmt, curFiscalYear, MatCosts, NewMatCosts);

                        //Create the second record which will be the taxable amount of the combined materials.
                        //NOTE: THE COST TYPE IS GOING TO BE 5 FOR THIS RECORD, NOT 1 LIKE THE ABOVE RECORD.
                        recBillAmout = 0.0m;
                        formattedED = string.Format("'{0} {1}'", endDate.ToString("MM/dd/yy"), "MSrv");
                        decimal blgTotal = sumBlgTotal - sumBlgAmt;

                        if ((sumBlgTotal != 0.0m) && (sumBlgAmt != 0.0m))
                        {
                            recBillAmout = (sumBlgTotal - sumBlgAmt) / (sumBlgTotal / sumBlgAmt);
                            recBillAmout = Math.Round(recBillAmout, 2);
                        }

                        modifiedFldCount = con.ExecuteNonQuery("INSERT INTO {0} SELECT {1} as jobnum, {2} as phsnum, {3} as trnnum, \"Materials Service\" as dscrpt, {4} as trndte,"
                                                + "{5} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts,"
                                                + "{6} as cstcde, 5 as csttyp, {7} as blgamt, {8} as blgttl, 1 as taxabl, "
                                                + " 1 as ovrrde, {9} as postyr, \"Combine\" as usrnme FROM {10}",
                                                NewMatCosts, jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(),
                                                costCode, recBillAmout, blgTotal, curFiscalYear, MatCosts);
                        #endregion
                    }

                    int subMatCostCount = con.GetScalar<int>("select count(*) from {0}", SubMatCosts);
                    if (subMatCostCount > 0)
                    {
                        decimal sumBlgAmt = 0.0m;
                        decimal sumBlgTotal = 0.0m;
                        decimal recBillAmout = 0.0m;
                        sumBlgAmt = con.GetScalar<decimal>("SELECT SUM(blgamt) FROM {0}", SubMatCosts);
                        sumBlgTotal = con.GetScalar<decimal>("SELECT SUM(blgttl) FROM {0}", SubMatCosts);

                        if (sumBlgTotal != 0.0m)
                        {
                            recBillAmout = (sumBlgAmt * sumBlgAmt) / sumBlgTotal;
                            recBillAmout = Math.Round(recBillAmout, 2);
                        }

                        string formattedED = string.Format("'{0} {1}'", endDate.ToString("MM/dd/yy"), "SubMat");

                        #region "Ver 1.0.2"
                        //modifiedFldCount = con.ExecuteNonQuery("SELECT {0} as jobnum, {1} as phsnum, {2} as trnnum, \"Subcontract Materials\" as dscrpt, "
                        //                            + "{3} as trndte, {4} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts, "
                        //                            + "{5} as cstcde, 7 as csttyp, SUM(cstamt) as blgamt, SUM(blgttl) as blgttl, SUM(shwamt) as shwamt, SUM(ovhamt) as ovhamt, "
                        //                            + "SUM(pftamt) as pftamt, 1 as ovrrde, \"Combine\" as usrnme FROM {6} INTO TABLE {7}",
                        //                            jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(), costCode, SubMatCosts, NewSubMatCosts);
                        #endregion

                        #region "Ver 1.0.3 onwards"
                        //Create the first record which will be the non-taxable amount of the combined materials
                        modifiedFldCount = con.ExecuteNonQuery("SELECT {0} as jobnum, {1} as phsnum, {2} as trnnum, \"Subcontract Materials\" as dscrpt, {3} as trndte,"
                                                + "{4} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts, "
                                                + "{5} as cstcde, 7 as csttyp, {6} as blgamt, {7} as blgttl, 0 as taxabl, "
                                                + " 1 as ovrrde, {8} as postyr, \"Combine\" as usrnme FROM {9} INTO TABLE {10}",
                                                jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(),
                                                costCode, recBillAmout, sumBlgAmt, curFiscalYear, SubMatCosts, NewSubMatCosts);

                        //Create the second record which will be the taxable amount of the combined materials.
                        //NOTE: THE COST TYPE IS GOING TO BE 5 FOR THIS RECORD, NOT 1 LIKE THE ABOVE RECORD.
                        recBillAmout = 0.0m;
                        formattedED = string.Format("'{0} {1}'", endDate.ToString("MM/dd/yy"), "SubMSrv");
                        decimal blgTotal = sumBlgTotal - sumBlgAmt;

                        if ((sumBlgTotal != 0.0m) && (sumBlgAmt != 0.0m))
                        {
                            recBillAmout = (sumBlgTotal - sumBlgAmt) / (sumBlgTotal / sumBlgAmt);
                            recBillAmout = Math.Round(recBillAmout, 2);
                        }

                        // With version 1.0.5, this second created record, Subcontractor Materials Service, is going
                        // to be to cost type 8 to separate
                        // it from the Materials Service element of the project.   Prior to version 1.0.5, it was going
                        // going to cost type 5.
                        modifiedFldCount = con.ExecuteNonQuery("INSERT INTO {0} SELECT {1} as jobnum, {2} as phsnum,"
                                                + "{3} as trnnum, \"Subcontract Materials Service\" as dscrpt, {4} as trndte,"
                                                + "{5} as entdte, MAX(actprd) as actprd, 31 as srcnum, 1 as status, 1 as bllsts,"
                                                + "{6} as cstcde, 8 as csttyp, {7} as blgamt, {8} as blgttl, 1 as taxabl, "
                                                + " 1 as ovrrde, {9} as postyr, \"Combine\" as usrnme FROM {10}",
                                                NewSubMatCosts, jobNumber, jobPhase, formattedED, endDate.ToFoxproDate(), DateTime.Today.ToFoxproDate(),
                                                costCode, recBillAmout, blgTotal, curFiscalYear, SubMatCosts);
                        #endregion
                    }

                    SetNullOff(con);

                    progress.Tick();
                    progress.Text = string.Format("Inserting material cost records into jobcst table");

                    if (System.IO.File.Exists(NewMatCosts.filename))
                    {
                        DataTable matCostDetails = con.GetDataTable("TaxJobCosts", "select * from {0}", NewMatCosts);
                        if (matCostDetails != null && matCostDetails.Rows.Count > 0)
                        {
                            //Add the recnum and ntetxt column
                            matCostDetails.Columns.Add("recnum", typeof(decimal));
                            matCostDetails.Columns.Add("ntetxt");

                            if (dataSetVersion < 19.0M)
                            {
                                matCostDetails.Columns.Remove("postyr");
                            }

                            foreach (DataRow dr in matCostDetails.Rows)
                            {
                                int recNum = con.GetScalar<int>("SELECT MAX(recnum) from jobcst") + 1;
                                //DateTime trnDate = (DateTime)dr["trndte"];
                                //DateTime eDate = (DateTime)dr["entdte"];

                                ////Using standard way to insert data
                                //var jobcst_row = con.GetDataTable("Job Cost", "select * from {0} where recnum = {0}", NewMatCosts, dr["recnum"]).Rows[0];
                                //jobcst_row["recnum"] = recNum;
                                //jobcst_row["jobnum"] = dr["jobnum"];
                                //jobcst_row["phsnum"] = dr["phsnum"];
                                //jobcst_row["trnnum"] = dr["trnnum"];
                                //jobcst_row["dscrpt"] = dr["dscrpt"];
                                //jobcst_row["trndte"] = trnDate.ToFoxproDate();
                                //jobcst_row["entdte"] = eDate.ToFoxproDate();
                                //jobcst_row["actprd"] = dr["actprd"];
                                //jobcst_row["srcnum"] = dr["srcnum"];
                                //jobcst_row["status"] = dr["status"];
                                //jobcst_row["bllsts"] = dr["bllsts"];
                                //jobcst_row["cstcde"] = dr["cstcde"];
                                //jobcst_row["blgamt"] = dr["blgamt"];
                                //jobcst_row["ovrrde"] = dr["ovrrde"];
                                //jobcst_row["usrnme"] = dr["usrnme"];
                                //jobcst_row["ntetxt"] = MatCostDetail;

                                //// insert the record
                                //var sql = jobcst_row.FoxproInsertString("jobcst");
                                //con.ExecuteNonQuery(sql);

                                // insert the record
                                dr["recnum"] = recNum;
                                dr["ntetxt"] = MatCostDetail;

                                var sql = dr.FoxproInsertString("jobcst");
                                con.ExecuteNonQuery(sql);

                                modifiedFldCount++;
                            }
                        }
                    }

                    progress.Tick();
                    progress.Text = string.Format("Inserting sub material cost records into jobcst table");

                    if (System.IO.File.Exists(NewSubMatCosts.filename))
                    {
                        DataTable subMatCostDetails = con.GetDataTable("TaxJobCosts", "select * from {0}", NewSubMatCosts);
                        if (subMatCostDetails != null && subMatCostDetails.Rows.Count > 0)
                        {
                            //Add the recnum and ntetxt column
                            subMatCostDetails.Columns.Add("recnum", typeof(decimal));
                            subMatCostDetails.Columns.Add("ntetxt");
                            if (dataSetVersion < 19.0M)
                            {
                                subMatCostDetails.Columns.Remove("postyr");
                            }

                            foreach (DataRow dr in subMatCostDetails.Rows)
                            {
                                int recNum = con.GetScalar<int>("SELECT MAX(recnum) from jobcst") + 1;
                                //DateTime trnDate = (DateTime)dr["trndte"];
                                //DateTime eDate = (DateTime)dr["entdte"];

                                //Working Query
                                //modifiedFldCount = con.ExecuteNonQuery("INSERT INTO jobcst ( recnum, jobnum, phsnum, trnnum, dscrpt, trndte, entdte, actprd, srcnum, "
                                //                                        + "status, bllsts, cstcde, csttyp, blgamt, ovrrde, usrnme, ntetxt ) "
                                //                                        + "VALUES ({0}, {1}, {2}, \"{3}\", \"{4}\", {5}, {6}, {7}, {8}, "
                                //                                        + "{9}, {10}, {11}, {12}, {13}, {14}, \"{15}\", \"{16}\")",
                                //                                        recNum, dr["jobnum"], dr["phsnum"], dr["trnnum"], dr["dscrpt"], trnDate.ToFoxproDate(),
                                //                                        eDate.ToFoxproDate(), dr["actprd"], dr["srcnum"], dr["status"], dr["bllsts"], dr["cstcde"],
                                //                                        dr["csttyp"], dr["blgamt"], dr["ovrrde"], dr["usrnme"], SubMatDetail);

                                // insert the record
                                dr["recnum"] = recNum;
                                dr["ntetxt"] = SubMatDetail;
                                var sql = dr.FoxproInsertString("jobcst");
                                con.ExecuteNonQuery(sql);

                                modifiedFldCount++;
                            }
                        }
                    }

                    //Finally, set all the billing status to non-billable for materials and subcontract materiasl
                    modifiedFldCount = con.ExecuteNonQuery("UPDATE jobcst SET jobcst.bllsts = 2 from {0} _ActiveJobCosts WHERE jobcst.recnum = _ActiveJobCosts.recnum "
                                                            + "AND INLIST(_ActiveJobCosts.csttyp,1,7)", ActiveJobCosts);
                    Env.Log("Updated billing status of {0} records in jobcst table.", modifiedFldCount);
                }

                //update the job cost tax status
                UpdateJobCostTaxStatus(jobNumber, startDate, endDate, con);

                Env.Log("--------------------------------------------------------------------------------\n");
            }
        }
        private void PopulateTemplate(string template, long[] jobnums, int strprd, int endprd, DateTime trndate, decimal[] nonbillablecstcdes)
        {
            using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
            {
                using (Env.TempDBFPointer
                    _jobcst = con.GetTempDBF(),
                    _jobnums = con.GetTempDBF(),
                    _blgqty = con.GetTempDBF(),
                    _nobill = con.GetTempDBF(),
                    _sources = con.GetTempDBF(),
                    _csttyps = con.GetTempDBF(),
                    _phases = con.GetTempDBF())
                {
                    using (var progress = new ProgressDialog(9))
                    {
                        progress.Text = "Select Job Cost Records";
                        progress.Show();

                        // first create a table of job numbers to join against
                        con.ExecuteNonQuery("create table {0} (jobnum n(10,0) not null)", _jobnums);
                        foreach(var j in jobnums)
                        {
                            con.ExecuteNonQuery("insert into {0} (jobnum) values ({1})", _jobnums, j);
                        }

                        con.ExecuteNonQuery("select"
                                + " jobcst.recnum"
                                + ", actrec.estemp as estnum"
                                + ", jobcst.empnum"
                                + ", jobcst.vndnum"
                                + ", jobcst.eqpnum"
                                + ", 000000 as tmemln"
                                + ", 000000 as tmeqln"
                                + ", jobcst.jobnum"
                                + ", actrec.jobnme"
                                + ", alltrim(reccln.clnnme) as clnnme"
                                + ", jobcst.trnnum"
                                + ", jobcst.dscrpt"
                                + ", jobcst.trndte"
                                + ", jobcst.actprd"
                                + ", jobcst.srcnum"
                                + ", jobcst.status"
                                + ", jobcst.bllsts"
                                + ", jobcst.phsnum"
                                + ", jobphs.phsnme"
                                + ", jobcst.cstcde"
                                + ", cstcde.cdenme"
                                + ", jobcst.csttyp"
                                + ", jobcst.csthrs"
                                + ", IIF(jobcst.acrinv = 0 AND jobcst.bllsts = 1 AND jobcst.csttyp = 2"
                                    + ", jobcst.blgqty"
                                    + ", 0000000000) as blgpnd"
                                + ", jobcst.eqpqty"
                                + ", jobcst.blgqty"
                                + ", jobcst.paytyp"
                                + ", jobcst.cstamt"
                                + ", jobcst.blgttl"
                                + ", jobcst.acrinv"
                                + ", IIF("
                                    + "jobcst.csttyp = 2, 'Employee    ',"
                                    + "IIF(jobcst.csttyp = 3, 'Equipment    ',"
                                    + "IIF(jobcst.empnum <> 0, 'Employee    ',"
                                    + "IIF(jobcst.eqpnum <> 0, 'Equipment   ',"
                                    + "IIF(jobcst.vndnum <> 0, 'Vendor      ',"
                                    + "'None        '))))) as empeqpvnd"
                                + ", [                                                        ] as ename"
                                + ", 00000000.00000000 as rate01"
                                + ", 00000000.00000000 as rate02"
                                + ", 00000000.00000000 as rate03"
                                + ", 00000000.00000000 as minhrs"
                                + ", 00000001.00000000 as markup"
                                + ", 0000000000000.00000000 as estbll"
                                + ", jobcst.eqpnum"
                                + ", eqpmnt.eqpnme"
                                + ", jobcst.blgunt"
                                + ", jobcst.empnum"
                                + ", employ.fullst"
                                + ", jobcst.blgamt"
                                + ", jobcst.ntetxt"
                                + ", estmtr.fullst as estnme"
                                + ", nvl(sprvsr.fullst, [No Supervisor               ]) as sprvsr"
                            + " from jobcst"
                            + " join actrec on jobcst.jobnum = actrec.recnum"
                            + " join reccln on reccln.recnum = actrec.clnnum"
                            + " left outer join employ estmtr on actrec.estemp = estmtr.recnum"
                            + " left join employ sprvsr on actrec.sprvsr = sprvsr.recnum"
                            + " left join cstcde on cstcde.recnum = jobcst.cstcde"
                            + " join {0} _jobnums on _jobnums.jobnum = jobcst.jobnum"
                            + " left join eqpmnt on eqpmnt.recnum = jobcst.eqpnum"
                            + " left join employ on employ.recnum = jobcst.empnum"
                            + " left join jobphs on jobphs.recnum = jobcst.jobnum and jobphs.phsnum = jobcst.phsnum"
                            // + " and between(jobcst.actprd, {1}, {2})"
                            + " where jobcst.actprd >= {1}"
                            + (chkUnbilled.Checked ? " and jobcst.bllsts = 1" : "")
                            + " and jobcst.actprd <= {2}"
                            + " and jobcst.status <> 2"
                            + " and jobcst.trndte <= {3}"
                            + " order by jobcst.recnum"
                            + " into table {4}"
                            , _jobnums, strprd, endprd, trndate.ToFoxproDate(), _jobcst);

                        progress.Tick();
                        progress.Text = "Selecting Names";

                        con.ExecuteNonQuery("update _jobcst set ename = alltrim(str(empnum)) + [ - ] + alltrim(employ.fullst)"
                            + " from {0} _jobcst"
                            + " join employ on _jobcst.empnum = employ.recnum"
                            + " where empeqpvnd = 'Employee'"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set ename = alltrim(str(vndnum)) + [ - ] + alltrim(actpay.vndnme)"
                            + " from {0} _jobcst"
                            + " join actpay on _jobcst.vndnum = actpay.recnum"
                            + " where empeqpvnd = 'Vendor'"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set ename = alltrim(str(eqpnum)) + [ - ] + alltrim(eqpmnt.eqpnme)"
                            + " from {0} _jobcst"
                            + " join eqpmnt on _jobcst.eqpnum = eqpmnt.recnum"
                            + " where empeqpvnd = 'Equipment'"
                            , _jobcst);

                        progress.Tick();
                        progress.Text = "Locating T&M Line items";

                        con.ExecuteNonQuery("update _jobcst set _jobcst.tmemln = tmemln.linnum"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmemln on"
                                + " tmemln.recnum = timmat.emptbl"
                                + " and timmat.emptbl <> 0"
                                + " and tmemln.empnum = 0"
                                + " and tmemln.cstcde = _jobcst.cstcde"
                            + " where _jobcst.csttyp = 2"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.tmemln = tmemln.linnum"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmemln on"
                                + " tmemln.recnum = timmat.emptbl"
                                + " and timmat.emptbl <> 0"
                                + " and tmemln.empnum = _jobcst.empnum"
                                + " and tmemln.cstcde = _jobcst.cstcde"
                            + " where _jobcst.empnum <> 0 AND _jobcst.csttyp = 2"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.tmeqln = tmeqln.linnum"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmeqln on tmeqln.recnum = timmat.eqptbl"
                            , _jobcst);

                        progress.Tick();
                        progress.Text = "Loading T&M Rates";

                        con.ExecuteNonQuery("update _jobcst set"
                                + " _jobcst.rate01 = tmeqln.oprrte"
                                + ", _jobcst.rate02 = tmeqln.stdrte"
                                + ", _jobcst.rate03 = tmeqln.idlrte"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmeqln on tmeqln.recnum = timmat.eqptbl and tmeqln.linnum = _jobcst.tmeqln"
                            + " where _jobcst.tmeqln <> 0"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set"
                                + " _jobcst.rate01 = tmemln.rate01"
                                + ", _jobcst.rate02 = tmemln.rate02"
                                + ", _jobcst.rate03 = tmemln.rate03"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmemln on tmemln.recnum = timmat.emptbl and tmemln.linnum = _jobcst.tmemln"
                            + " where _jobcst.tmemln <> 0"
                            , _jobcst);

                        progress.Tick();
                        progress.Text = "Loading Minimum Hours";

                        con.ExecuteNonQuery("update _jobcst set _jobcst.minhrs = tmeqln.minhrs"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmeqln on tmeqln.recnum = timmat.eqptbl and tmeqln.linnum = _jobcst.tmeqln"
                            + " where _jobcst.tmeqln <> 0"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.minhrs = tmemln.minhrs"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " join tmemln on tmemln.recnum = timmat.emptbl and tmemln.linnum = _jobcst.tmemln"
                            + " where _jobcst.tmemln <> 0"
                            , _jobcst);

                        progress.Tick();
                        progress.Text = "Selecting Markup Values";

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.mtrhdn / 100.00)) *"
                                + " (1.00 + (timmat.mtrshw / 100.00)) *"
                                + " (1.00 + (timmat.mtrovh / 100.00)) *"
                                + " (1.00 + (timmat.mtrpft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 1"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.labhdn / 100.00)) *"
                                + " (1.00 + (timmat.labshw / 100.00)) *"
                                + " (1.00 + (timmat.labovh / 100.00)) *"
                                + " (1.00 + (timmat.labpft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 2"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.eqphdn / 100.00)) *"
                                + " (1.00 + (timmat.eqpshw / 100.00)) *"
                                + " (1.00 + (timmat.eqpovh / 100.00)) *"
                                + " (1.00 + (timmat.eqppft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 3"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.subhdn / 100.00)) *"
                                + " (1.00 + (timmat.subshw / 100.00)) *"
                                + " (1.00 + (timmat.subovh / 100.00)) *"
                                + " (1.00 + (timmat.subpft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 4"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.otrhdn / 100.00)) *"
                                + " (1.00 + (timmat.otrshw / 100.00)) *"
                                + " (1.00 + (timmat.otrovh / 100.00)) *"
                                + " (1.00 + (timmat.otrpft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 5"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.cs6hdn / 100.00)) *"
                                + " (1.00 + (timmat.cs6shw / 100.00)) *"
                                + " (1.00 + (timmat.cs6ovh / 100.00)) *"
                                + " (1.00 + (timmat.cs6pft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 6"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.cs7hdn / 100.00)) *"
                                + " (1.00 + (timmat.cs7shw / 100.00)) *"
                                + " (1.00 + (timmat.cs7ovh / 100.00)) *"
                                + " (1.00 + (timmat.cs7pft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 7"
                            , _jobcst);

                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.cs8hdn / 100.00)) *"
                                + " (1.00 + (timmat.cs8shw / 100.00)) *"
                                + " (1.00 + (timmat.cs8ovh / 100.00)) *"
                                + " (1.00 + (timmat.cs8pft / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 8"
                            , _jobcst);

                        // BUG in SMB database, cs9pft is type C!!!!
                        con.ExecuteNonQuery("update _jobcst set _jobcst.markup ="
                                + " (1.00 + (timmat.cs9hdn / 100.00)) *"
                                + " (1.00 + (timmat.cs9shw / 100.00)) *"
                                + " (1.00 + (timmat.cs9ovh / 100.00)) *"
                                + " (1.00 + (val(timmat.cs9pft) / 100.00))"
                            + " from {0} _jobcst"
                            + " join timmat on timmat.recnum = _jobcst.jobnum"
                            + " where _jobcst.csttyp = 9"
                            , _jobcst);

                        progress.Tick();
                        progress.Text = "Selecting Billing Quantities";

                        con.ExecuteNonQuery("select _jobcst.recnum, 00000000.00000000 as hrs, cast(null as n(3)) as typ from {0} _jobcst into table {1}", _jobcst, _blgqty);

                        con.ExecuteNonQuery("update blgqty set hrs = _jobcst.csthrs, typ = _jobcst.paytyp"
                            + " from {0} blgqty"
                            + " join {1} _jobcst on blgqty.recnum = _jobcst.recnum"
                            + " where _jobcst.csttyp = 2"
                            , _blgqty, _jobcst);

                        con.ExecuteNonQuery("update blgqty set hrs = _jobcst.blgqty, typ = _jobcst.paytyp"
                            + " from {0} blgqty"
                            + " join {1} _jobcst on blgqty.recnum = _jobcst.recnum"
                            + " where _jobcst.csttyp = 2 and blgqty.hrs = 0 and _jobcst.bllsts = 1"
                            , _blgqty, _jobcst);

                        con.ExecuteNonQuery("update blgqty set hrs = _jobcst.eqpqty, typ = eqpmnt.eqptyp"
                            + " from {0} blgqty"
                            + " join {1} _jobcst on blgqty.recnum = _jobcst.recnum"
                            + " join eqpmnt on _jobcst.eqpnum = eqpmnt.recnum"
                            + " where _jobcst.csttyp = 3 and _jobcst.eqpnum <> 0 and eqpmnt.eqptyp <> 0"
                            , _blgqty, _jobcst);

                        progress.Tick();
                        progress.Text = "Calculating Billing Amounts";

                        con.ExecuteNonQuery("update _jobcst set _jobcst.estbll = "
                                + "IIF(typ = 1 and rate01 <> 0, hrs*rate01,"
                                + "IIF(typ = 2 and rate02 <> 0, hrs*rate02,"
                                + "IIF(typ = 3 and rate03 <> 0, hrs*rate03,"
                                + "cstamt)))"
                            + " from {0} _jobcst"
                            + " join {1} blgqty on blgqty.recnum = _jobcst.recnum"
                            , _jobcst, _blgqty);

                        con.ExecuteNonQuery("update _jobcst set estbll = cstamt"
                            + " from {0} _jobcst"
                            + " join eqpmnt on eqpmnt.recnum = _jobcst.eqpnum"
                            + " where _jobcst.csttyp = 3 and (isnull(eqpmnt.eqptyp) or eqpmnt.eqptyp = 0)"
                            , _jobcst);

                        con.ExecuteNonQuery("update {0} set estbll = estbll * markup", _jobcst);

                        con.ExecuteNonQuery("update {0} set estbll = 0 where bllsts = 2", _jobcst);

                        con.ExecuteNonQuery("update {0} set estbll = blgttl where bllsts = 3", _jobcst);

                        con.ExecuteNonQuery("update {0} set estbll = blgamt where estbll = 0", _jobcst);

                        // build a list of nonbillable cost codes
                        con.ExecuteNonQuery("create table {0} (cstcde n(18,3) not null)", _nobill);

                        foreach (var cstcde in nonbillablecstcdes)
                        {
                            con.ExecuteNonQuery("insert into {0} (cstcde) values ({1})", _nobill, cstcde);
                        }

                        // make sure those cost codes are not billed
                        con.ExecuteNonQuery("update _jobcst set estbll = 0 from {0} _jobcst join {1} _nobill on _nobill.cstcde = _jobcst.cstcde"
                            + " where _jobcst.acrinv = 0"
                            , _jobcst, _nobill);
                        // set their bllsts to unbillable cost code
                        // v2.1.2 BUT only for items that have not already been invoiced
                        con.ExecuteNonQuery("update _jobcst set bllsts = 4 from {0} _jobcst join {1} _nobill on _nobill.cstcde = _jobcst.cstcde"
                            + " where _jobcst.acrinv = 0"
                            , _jobcst, _nobill);

                        progress.Tick();
                        progress.Text = "Selecting References";

                        con.ExecuteNonQuery("select distinct _jobcst.srcnum as recnum, source.srcnme, source.srcdsc"
                            + " from {0} _jobcst"
                            + " left join source on source.recnum = _jobcst.srcnum"
                            + " into table {1}"
                            , _jobcst, _sources);

                        con.ExecuteNonQuery("select distinct _jobcst.csttyp as recnum, csttyp.typnme"
                            + " from {0} _jobcst"
                            + " left join csttyp on csttyp.recnum = _jobcst.csttyp"
                            + " into table {1}"
                            , _jobcst, _csttyps);

                        con.ExecuteNonQuery("select distinct _jobcst.phsnum, jobphs.phsnme, alltrim(str(_jobcst.phsnum)) + [ - ] + alltrim(jobphs.phsnme) as phsdsc"
                            + " from {0} _jobcst"
                            + " join jobphs on jobphs.recnum = _jobcst.jobnum and jobphs.phsnum = _Jobcst.phsnum"
                            + " into table {1}"
                            , _jobcst, _phases);

                        con.ExecuteNonQuery("insert into {0} (phsnum, phsnme, phsdsc) values (0, [None], [0 - None])", _phases);

                        /* if we want a summary, do the totals now */
                        var is_summary = chkSumCustomer.Checked || chkSumJob.Checked || chkSumPeriod.Checked;
                        Env.TempDBFPointer _jobcstsum = null;

                        if (is_summary)
                        {
                            var groupCols = new List<string>();
                            if(chkSumCustomer.Checked) groupCols.Add("clnnme");
                            if (chkSumJob.Checked) { groupCols.Add("jobnum"); groupCols.Add("jobnme"); groupCols.Add("clnnme"); }
                            if(chkSumPeriod.Checked) groupCols.Add("actprd");

                            var sum_cols = new string[]
                            {
                                "cstamt", "blgttl", "estbll", "blgqty", "blgpnd", "blgamt"
                            };

                            _jobcstsum = con.Summarize(_jobcst, groupCols.Uniq().ToArray(), sum_columns: sum_cols);
                        }

                        progress.Tick();
                        progress.Text = "Loading Excel Sheet";

                        ExcelAddinUtil.UseNewApp();

                        try
                        {
                            var details_dt = con.GetDataTable("Details", "select * from {0}", _jobcstsum != null ? _jobcstsum : _jobcst);
                            details_dt.ConfigurableWriteToExcel(template, "ImportData", "DetailData");

                            var sources_dt = con.GetDataTable("Sources", "select * from {0}", _sources);
                            sources_dt.ConfigurableWriteToExcel(template, "References", "Sources");

                            var csttyps_dt = con.GetDataTable("Cost Types", "select * from {0}", _csttyps);
                            csttyps_dt.ConfigurableWriteToExcel(template, "References", "CostTypes");

                            var jobphs_dt = con.GetDataTable("Phases", "select * from {0}", _phases);
                            jobphs_dt.ConfigurableWriteToExcel(template, "References", "JobPhases");

                            ExcelAddinUtil.app.Visible = true;

                            // auto-fit row heights
                            ExcelAddinUtil.app.ActiveWorkbook.getWorksheet("Job Detail").Cells.Rows.AutoFit();

                            // if the template has the unbilled by job pivot table, go ahead and set it's settings
                            try
                            {
                                if (_jobcstsum == null)
                                {
                                    /* set the pivot table options and refresh data for the unbilled by job sheet */
                                    var unbilled_ptable = ExcelAddinUtil.app.ActiveWorkbook.getWorksheet("Unbilled By Job").PivotTables("ByJob");
                                    unbilled_ptable.PivotCache.Refresh();

                                    /* set the billing status filter to 'Unbilled' */
                                    unbilled_ptable.PivotFields("Billing Status").ClearAllFilters();
                                    unbilled_ptable.PivotFields("Billing Status").CurrentPage = "Unbilled";

                                    /* sort the pivot table by date */
                                    unbilled_ptable.PivotFields("Date").AutoSort(1, "Date");

                                    /* hide the "Job" that shows up for unpopulated rows in the source data */
                                    var missing = Missing.Value;
                                    unbilled_ptable.PivotFields("Job").PivotFilters.Add(16, missing, "0 - 0 - 0 - 0", missing, missing, missing, missing, missing);

                                    /* collapse to dates */
                                    unbilled_ptable.PivotFields("Date").ShowDetail = false;

                                    /* format numbers in the pivot table */
                                    Worksheet ws = ExcelAddinUtil.app.ActiveWorkbook.getWorksheet("Unbilled By Job");
                                    Range r = ws.get_Range("B:H");
                                    r.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";
                                    r = ws.get_Range("D:D,B:B");
                                    r.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)";
                                }
                            }
                            catch { }
                        }
                        finally
                        {
                            ExcelAddinUtil.app.Visible = true;
                        }
                    }
                }
            }
        }
        private void import_file(string filename)
        {
            using (var progress = new ProgressDialog(3))
            {
                progress.Show();
                progress.Text = "Copying Required Files";
                progress.Tick();

                // first, make sure the audit log exists
                if (!File.Exists(mbapi.smartGetSMBDir() + "/syscon_tm_log.DBF"))
                {
                    File.Copy(Env.GetEXEDirectory() + "/syscon_tm_log.DBF", mbapi.smartGetSMBDir() + "/syscon_tm_log.DBF");
                    File.Copy(Env.GetEXEDirectory() + "/syscon_tm_log.FPT", mbapi.smartGetSMBDir() + "/syscon_tm_log.FPT");
                }

                // make a copy of the import file into Datadir + /Syscon TM Audit/<date>.xlsm
                try
                {
                    if (!Directory.Exists(mbapi.smartGetSMBDir() + "/Syscon TM Audit"))
                        Directory.CreateDirectory(mbapi.smartGetSMBDir() + "/Syscon TM Audit");

                    var today = DateTime.Today;
                    var dtestr = string.Format("{0}-{1}-{2}", today.Year, today.Month, today.Day);

                    int index = 1;
                    var ext = Path.GetExtension(filename);
                    var new_filename = string.Format("{0}/Syscon TM Audit/{1}_{2}{3}", mbapi.smartGetSMBDir(), dtestr, index, ext);
                    while (File.Exists(new_filename))
                    {
                        new_filename = string.Format("{0}/Syscon TM Audit/{1}_{2}{3}", mbapi.smartGetSMBDir(), dtestr, ++index, ext);
                    }

                    File.Copy(filename, new_filename);
                    filename = new_filename;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not copy the file for auditing purposes, not updating SMB.\r\nPlease ensure the file is closed and try again.", "Error", MessageBoxButtons.OK);
                    Env.Log("{0}\r\n{1}", ex.Message, ex.StackTrace);
                    return;
                }

                var import_errors = false;

                using (var excel_con = SysconCommon.Common.Environment.Connections.GetInMemoryDB())
                {
                    using (var mb7_con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
                    {
                        progress.Text = "Loading Excel (May take a while)";
                        progress.Tick();

                        // load the excel_con with values
                        ExcelAddinUtil.UseNewApp(_readonly: true);
                        try
                        {
                            var tempdt = ExcelAddinUtil.GetNamedRangeData(filename, "Change Summary", "ChangeSummary", true);
                            excel_con.LoadDataTable(tempdt);
                        }
                        finally
                        {
                            ExcelAddinUtil.CloseApp(false);
                        }

                        progress.Text = "Loading data into SMB";
                        progress.Tick();
                        // wrap it all in a transaction, so that if one thing fails, the db is not left in a corrupted state
                        // ARGHHH!!! Stupid SMB uses free tables, (no .dbc) so the rollback() seems to succeed but doesn't actually
                        // do anything.... god i hate Sage sometimes
                        try
                        {
                            // make sure there are approved changes to update
                            var chngrows_count = excel_con.GetScalar<int>("select count(*) from ChangeSummary where invalid = 0 and apprvd = 'Yes'");
                            if (chngrows_count == 0)
                            {
                                MessageBox.Show("No approved changes in spreadsheet.", "Error", MessageBoxButtons.OK);
                                return;
                            }

                            Func<string, long, bool> auditlog = (string msg, long recnum) =>
                            {
                                try
                                {
                                    mb7_con.ExecuteNonQuery("insert into syscon_tm_log (recnum, usrnme, chgdsc, chgdte, chgpth) values ({0}, {1}, {2}, {3}, {4})"
                                        , recnum
                                        , mbapi.LoggedInUser.FoxproQuote()
                                        , msg.FoxproQuote()
                                        , DateTime.Today.ToFoxproDate()
                                        , filename.FoxproQuote());

                                    return true;
                                }
                                catch (Exception ex)
                                {
                                    Env.Log("{0}\r\n{1}", ex.Message, ex.StackTrace);
                                    import_errors = true;
                                    return false;
                                }
                            };

                            // update billing status
                            var data_dt = excel_con.GetDataTable("Billing Status", "select recnum, bllsts from ChangeSummary where mbllsts = 1 and invalid = 0 and apprvd = 'Yes'");
                            foreach (var row in data_dt.Rows.ToIEnumerable())
                            {

                                try
                                {
                                    var bllsts = 0;
                                    switch (row["bllsts"].ToString().Trim().ToUpper())
                                    {
                                        case "UNBILLED":
                                            bllsts = 1;
                                            break;
                                        case "NON-BILLABLE":
                                            bllsts = 2;
                                            break;
                                        case "BILLED":
                                            bllsts = 3;
                                            break;
                                        case "UNBILLABLE COST CODE":
                                            bllsts = 2;
                                            break;
                                        default:
                                            throw new SysconException("Invalid Billing Status ({0}) for jobcst record {1}", row["bllsts"], row["recnum"]);
                                    }

                                    mb7_con.ExecuteNonQuery("update jobcst set bllsts = {0} where recnum = {1}", bllsts, row["recnum"]);
                                    auditlog("Updating Billing Status", Convert.ToInt64(row["recnum"]));
                                }
                                catch (Exception ex)
                                {
                                    import_errors = true;
                                    Env.Log("Error updating Jobcost {2}: {0}\r\n{1}", ex.Message, ex.StackTrace, row["recnum"]);
                                }
                            }

                            // update billing overrides
                            data_dt = excel_con.GetDataTable("Billing Overrides", "select recnum, bllamt from ChangeSummary where mbllovrrde = 1 and invalid = 0 and apprvd = 'Yes'");
                            foreach (var row in data_dt.Rows.ToIEnumerable())
                            {
                                try
                                {
                                    mb7_con.ExecuteNonQuery("update jobcst set ovrrde = 1, blgamt = {0} where recnum = {1}", row["bllamt"], row["recnum"]);
                                    auditlog("updated billing amount", Convert.ToInt64(row["recnum"]));
                                }
                                catch (Exception ex)
                                {
                                    import_errors = true;
                                    Env.Log("Error updating Jobcst {2}: {0}\r\n{1}", ex.Message, ex.StackTrace, row["recnum"]);
                                }
                            }

                            // update cost codes
                            data_dt = excel_con.GetDataTable("Cost Codes", "select recnum, cstcde from ChangeSummary where mcstcde = 1 and invalid = 0 and apprvd = 'Yes'");
                            foreach (var row in data_dt.Rows.ToIEnumerable())
                            {
                                try
                                {
                                    mb7_con.ExecuteNonQuery("update jobcst set cstcde = {0} where recnum = {1}", row["cstcde"], row["recnum"]);
                                    auditlog("updated cost code", Convert.ToInt64(row["recnum"]));
                                }
                                catch (Exception ex)
                                {
                                    import_errors = true;
                                    Env.Log("Error updating Jobcst {0}: {1}\r\n{2}", row["recnum"], ex.Message, ex.StackTrace);
                                }
                            }

                            // update notes
                            data_dt = excel_con.GetDataTable("Notes", "select recnum, newntes from ChangeSummary where mnotes = 1 and invalid = 0 and apprvd = 'Yes'");
                            foreach (var row in data_dt.Rows.ToIEnumerable())
                            {
                                try
                                {
                                    var new_ntetxt = row["newntes"].ToString().Trim();
                                    var old_ntetxt = mb7_con.GetScalar<string>("select ntetxt from jobcst where recnum = {0}", row["recnum"]);

                                    new_ntetxt = new_ntetxt.Replace("\r\n", new string(new char[] { (char)124 }));
                                    new_ntetxt += new string(new char[] { (char)124 });
                                    var ntetxt = new_ntetxt + old_ntetxt;

                                    mb7_con.ExecuteNonQuery("update jobcst set ntetxt = {0} where recnum = {1}", ntetxt.FoxproQuote(), row["recnum"]);
                                    auditlog("Updated Notes", Convert.ToInt64(row["recnum"]));
                                }
                                catch (Exception ex)
                                {
                                    import_errors = true;
                                    Env.Log("Error updating Jobcst {2}: {0}\r\n{1}", ex.Message, ex.StackTrace, row["recnum"]);
                                }
                            }

                            // update partial billings - this must happen last so that any other changes are copied to the new record correctly
                            data_dt = excel_con.GetDataTable("Partial Billings", "select recnum, originalbllqty, bllqty from ChangeSummary where mbllhours = 1 and invalid = 0 and apprvd = 'Yes'");
                            foreach (var row in data_dt.Rows.ToIEnumerable())
                            {
                                try
                                {
                                    // as an extra-check so this doesn't happen twice because that would be detrimental, make sure
                                    // that the original billing quantity matches that that is in SMB currently
                                    var smb_bllqty = mb7_con.GetScalar<decimal>("select blgqty from jobcst where recnum = {0}", row["recnum"]);
                                    var orig_bllqty = Convert.ToDecimal(row["originalbllqty"]);

                                    if (smb_bllqty != orig_bllqty)
                                    {
                                        throw new SysconException("SMB billing quantity ({0}) does not match original billing quantity ({1})", smb_bllqty, orig_bllqty);
                                    }

                                    // create a new jobcst record with the difference
                                    var jobcst_row = mb7_con.GetDataTable("Job Cost", "select * from jobcst where recnum = {0}", row["recnum"]).Rows[0];
                                    jobcst_row["cstamt"] = 0.0m;
                                    jobcst_row["payrec"] = 0;
                                    jobcst_row["csthrs"] = 0;
                                    jobcst_row["paytyp"] = 0;
                                    jobcst_row["blgqty"] = orig_bllqty - Convert.ToDecimal(row["bllqty"]);
                                    jobcst_row["srcnum"] = Env.GetConfigVar<int>("NewJobcostSource", 31, true);
                                    jobcst_row["usrnme"] = jobcst_row["usrnme"].ToString().Trim() + "-Import";
                                    jobcst_row["ntetxt"] = string.Format("{0}: Split from original billing record #{1}", DateTime.Now, row["recnum"]);
                                    jobcst_row["recnum"] = mb7_con.GetScalar<long>("select max(recnum) + 1 from jobcst");
                                    // jobcst_row["ovrrde"] = 1;

                                    // insert the record
                                    var sql = jobcst_row.FoxproInsertString("jobcst");
                                    mb7_con.ExecuteNonQuery(sql);
                                    auditlog(string.Format("Split from {0}", row["recnum"]), Convert.ToInt64(jobcst_row["recnum"]));

                                    // update the billing quantity
                                    mb7_con.ExecuteNonQuery("update jobcst set blgqty = {0}, ntetxt = {2} + ntetxt where recnum = {1}", row["bllqty"], row["recnum"],
                                        string.Format("{0} - Partial Billing, new record (#{1}) added{2}", DateTime.Now, jobcst_row["recnum"], (char)124).FoxproQuote());
                                    auditlog("Set to partial billing", Convert.ToInt64(row["recnum"]));
                                }
                                catch (Exception ex)
                                {
                                    import_errors = true;
                                    Env.Log("Error updating Jobcst {2}: {0}\r\n{1}", ex.Message, ex.StackTrace, row["recnum"]);
                                }
                            }

                            if (import_errors)
                            {
                                throw new SysconException("Error Importing Data.");
                            }

                            MessageBox.Show("File imported successfully.", "Success", MessageBoxButtons.OK);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Error updating SMB data, see logfile for details");
                            Env.Log(string.Format("Error updating SMB data: {0}\r\n{1}", ex.Message, ex.StackTrace));
                        }
                    }
                }
            }
        }
        // Runtime Analysis:
        // A = COUNT(*) in actrec
        // C = COUNT(*) in clnnum
        // J = COUNT(*) in jobtyp
        // E = COUNT(*) in employ
        // N = count of records in initialJobList, or 1 if initialJobList is null
        private void MultiJobSelector_Load(object sender, EventArgs e)
        {
            try
            {
                using (var con = Connections.GetOLEDBConnection())
                {
                    using (var joblist = con.GetTempDBF())
                    {
                        if (initialJobList != null)
                        {
                            // Runtime: O(N*Log(N))
                            // specifying that recnum is unique means it is indexed, which gives us better join times
                            con.ExecuteNonQuery("create table {0} (recnum n(10,0) not null unique)", joblist);
                            foreach (var recnum in initialJobList)
                            {
                                con.ExecuteNonQuery("insert into {0} (recnum) values ({1})", joblist, recnum);
                            }
                        }

                        // Runtime: O(A+A*Log(R)+A*Log(J)+A*Log(E))
                        var sql = string.Format(
                            "select actrec.recnum as JobNumber, jobnme as JobName, jobtyp.typnme as JobType, actrec.status as JobStatus"
                            + ", clnnum as ClientNumber, reccln.shtnme as ClientName"
                            + ", alltrim(employ.fullst) as Supervisor, actrec.contct as SiteContact"
                            + " from actrec"
                            + " left join reccln on clnnum = reccln.recnum"
                            + " left join jobtyp on actrec.jobtyp = jobtyp.recnum"
                            + " left join employ on actrec.sprvsr = employ.recnum");

                        // Runtime: O(A*Log(N))
                        sql += initialJobList != null
                            ? string.Format(" join {0} ijoblst on actrec.recnum = ijoblst.recnum", joblist)
                            : "";

                        // Runtime: O(N)
                        datalines_dt = con.GetDataTable("Job List", sql);
                        datalines_dt = datalines_dt.ToList<DataLine>().ToDataTable("datalines");
                        current_dt = datalines_dt;

                        // Total Runtime: O(N*Log(N) + A + A*Log(R) + A*Log(J) + A*Log(E) + A*Log(N) + N)
                        // we know that A > N, so we can drop the N*Log(N) term
                        // O(MAX(A*Log(R)+A*Log(J)+A*Log(E)+A*Log(N))) - simplify to dominate term without constants
                        // O(A*MAX(Log(R),Log(J),Log(E),Log(N))) - simplify to dominate term
                        // O(A*Log(MAX(R,J,E,N))
                    }

                    // load the project supervisors
                    var supervisors_dt = con.GetDataTable("Supervisors", "select distinct employ.recnum, employ.fullst from actrec join employ on actrec.sprvsr = employ.recnum order by employ.fullst asc");
                    cmbSupervisors.DataSource = new string [] { "*Any*" }.Concat(
                                                    (from s in supervisors_dt.Rows.ToIEnumerable()
                                                     select s["fullst"].ToString().Trim())).ToArray();

                    cmbSupervisors.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                Env.Log("{0}\r\n{1}", ex.Message, ex.StackTrace);
                MessageBox.Show("Error Loading Job List Form", "Error", MessageBoxButtons.OK);
            }
            #if false
            // Runtime: O(A+A*Log(R)+A*Log(J)+A*Log(E))
            datalines_dt = Connections.Connection.GetDataTable("datalines"
                , "select actrec.recnum as JobNumber, jobnme as JobName, jobtyp.typnme as JobType, actrec.status as JobStatus"
                + ", clnnum as ClientNumber, reccln.shtnme as ClientName"
                + ", alltrim(employ.fstnme) + ' ' + alltrim(employ.lstnme) as Supervisor, actrec.contct as SiteContact"
                + " from actrec"
                + " left join reccln on clnnum = reccln.recnum"
                + " left join jobtyp on actrec.jobtyp = jobtyp.recnum"
                + " left join employ on actrec.sprvsr = employ.recnum");

            var progress = new ProgressDialog(datalines_dt.Rows.Count + 1);
            progress.Text = "Getting Job List";

            if (initial_job_filter != null)
            {
                progress.Show();
            }

            // Runtime: O(A)
            // get the correct types, and make sure all columns exist
            var datalines = datalines_dt.ToList<DataLine>();
            datalines_dt = datalines.ToDataTable("datalines");

            var jobs = (from j in smbtable.GetAll<actrec>()
                        where initial_job_filter(j)
                        select j.recnum).ToArray();

            progress.Tick();

            if (initial_job_filter != null)
            {
                progress.Text = "Filtering Job List";

                // Runtime: O(AN)
                datalines_dt = datalines_dt.FilterRows(row =>
                {
                    progress.Tick();
                    return jobs.Contains(Convert.ToInt64(row["JobNumber"]));
                    // return true;
                });
            }

            current_dt = datalines_dt;

            progress.Close();

            // Total Runtime: O(A + A*Log(R) + A*Log(J) + A*Log(E) + A + AN)
            // O(A*MAX(Log(R),Log(J),Log(E),N)) - Simplify to dominate term
            // O(AN)

            #endif

            this.chkStatus1.Checked = Env.GetConfigVar("mjselect_chkStatus1", true, true);
            this.chkStatus2.Checked = Env.GetConfigVar("mjselect_chkStatus2", false, true);
            this.chkStatus3.Checked = Env.GetConfigVar("mjselect_chkStatus3", true, true);
            this.chkStatus4.Checked = Env.GetConfigVar("mjselect_chkStatus4", true, true);
            this.chkStatus5.Checked = Env.GetConfigVar("mjselect_chkStatus5", true, true);
            this.chkStatus6.Checked = Env.GetConfigVar("mjselect_chkStatus6", true, true);

            DoFilter();

            this.txtFilter.KeyPress += new KeyPressEventHandler(txtFilter_KeyPress);
            this.chkStatus1.CheckedChanged += new EventHandler(chkStatus_CheckedChanged);
            this.chkStatus2.CheckedChanged += chkStatus_CheckedChanged;
            this.chkStatus3.CheckedChanged += chkStatus_CheckedChanged;
            this.chkStatus4.CheckedChanged += chkStatus_CheckedChanged;
            this.chkStatus5.CheckedChanged += chkStatus_CheckedChanged;
            this.chkStatus6.CheckedChanged += chkStatus_CheckedChanged;
        }
Example #8
0
        public static IEnumerable<IJob> GetJobs()
        {
            return Cache.CacheResult(() =>
            {
                var count = Connections.GetScalar<int>("select count(*) from actrec");
                var progress = new ProgressDialog(count, "Selecting Job List");
                progress.Show();
                var result = GetJobs(jobNumber => {
                    progress.Tick();
                    return true;
                });

                progress.Close();
                return result;
            }, "AllJobs");
        }