/// <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); } } }
internal static IEnumerable<long> GetAllPaygroups(OleDbConnection con) { var grpsdt = con.GetDataTable("paygroups", "select recnum from paygrp"); return from r in grpsdt.Rows.ToIEnumerable() select Convert.ToInt64(r["recnum"]); }