/// <summary>
/// Update the job cost tax status based on the tax district of a job.
/// </summary>
/// <param name="jobCode"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
private void UpdateJobCostTaxStatus(long jobCode, DateTime startDate, DateTime endDate, OleDbConnection con)
{
//using (var con = SysconCommon.Common.Environment.Connections.GetOLEDBConnection())
{
// Check the tax status of all unbilled job cost records that are marked to be
// manually overridden for the billing total
//Get the appropriate tax district information
string sql = string.Format("SELECT taxdst.* FROM taxdst JOIN actrec ON taxdst.recnum = actrec.slstax "
+ "WHERE actrec.recnum = {0}", jobCode);
DataTable taxInfoDt = con.GetDataTable("TaxInfo", sql);
if (taxInfoDt.Rows.Count != 1)
{
//No matching tax district set up in job.
return;
}
int[] taxStatus = new int[9];
DataRow dr = taxInfoDt.Rows[0];
taxStatus[0] = (((string)dr["mattax"]) == "Y") ? 1 : 0;
taxStatus[1] = (((string)dr["labtax"]) == "Y") ? 1 : 0;
taxStatus[2] = (((string)dr["eqptax"]) == "Y") ? 1 : 0;
taxStatus[3] = (((string)dr["subtax"]) == "Y") ? 1 : 0;
taxStatus[4] = (((string)dr["othtax"]) == "Y") ? 1 : 0;
taxStatus[5] = (((string)dr["usrcs6"]) == "Y") ? 1 : 0;
taxStatus[6] = (((string)dr["usrcs7"]) == "Y") ? 1 : 0;
taxStatus[7] = (((string)dr["usrcs8"]) == "Y") ? 1 : 0;
taxStatus[8] = (((string)dr["usrcs9"]) == "Y") ? 1 : 0;
//Update the taxable status of unbilled job cost records for this particular job
string sql2 = string.Format("SELECT * from jobcst "
+ "WHERE jobcst.jobnum = {0} "
+ "AND jobcst.ovrrde = 1 "
+ "AND jobcst.bllsts = 1 "
+ "AND BETWEEN(jobcst.trndte, {1},{2})", jobCode,
startDate.ToFoxproDate(), endDate.ToFoxproDate());
DataTable dt = con.GetDataTable("Jobcst", sql2);
foreach (DataRow row in dt.Rows)
{
int cstTyp = (int)((decimal)row["csttyp"]);
decimal recNum = (decimal)row["recnum"];
string updateSql = string.Format("UPDATE jobcst SET taxabl = {0} "
+ "WHERE jobcst.recnum = {1} ", taxStatus[cstTyp - 1], recNum);
con.ExecuteNonQuery(updateSql);
}
}
}
/// <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");
}
}
/// <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.");
}
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;
}
}
}
}
}
