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;
}
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");
}