public static IEnumerable<TCLineAmount> FindAmount(IEnumerable<TCLineKey> lines, TCAmountType type, long clcnum = 0) { using (var con = Connections.GetOLEDBConnection()) { using (var analysis_tbl = FindAmountTable(lines, type, clcnum)) { return from row in con.GetDataTable("Analysis", "select * from {0}", analysis_tbl).Rows.ToIEnumerable() select new TCLineAmount( Convert.ToDecimal(row["amount"]), Convert.ToDecimal(row["recttl"]), new TCLineKey( Convert.ToInt64(row["recnum"]), Convert.ToInt64(row["linnum"])), type, clcnum); } } }
public static Env.TempDBFPointer FindAmountTable(IEnumerable<TCLineKey> lines, TCAmountType type, long clcnum = 0) { if (type == TCAmountType.Calculation && clcnum <= 0) throw new SysconException("Calculation # not specified"); var lines_array = lines.ToArray(); if (con == null) { con = Connections.GetOLEDBConnection(); } var analysis_tbl = con.GetTempDBF(); // so basically the solution comes to: // - calculate for all records that belong to the payrecords we care about, this is import because of how // we compare zeros // - select a multiplier, this is the rate*hours_mult // - multiply by some base (hours or gross) // - scale so that our zeros match those in tmcddd // - filter out the records we don't care about if (payrecs_tbl == null || _cached_lines != lines) { _cached_lines = lines; var payrecs = (from l in lines_array select l.payrec).Uniq(); payrecs_tbl = con.BuildRecnumTable(payrecs); } con.ExecuteNonQuery("select tmcdln.recnum, tmcdln.linnum, " + "00000000000000000000000000000000000.0000 as amount, 000000000000000.0000 as recttl, 000000000000000.00000000 as mult, nvl(loctax.inctax, payrec.taxste) as taxste" + " from tmcdln" + " join {0} mypayrecs on mypayrecs.recnum = tmcdln.recnum" + " left join loctax on loctax.recnum = tmcdln.loctax" + " join payrec on payrec.recnum = tmcdln.recnum" + " into table {1}" , payrecs_tbl, analysis_tbl); switch (type) { case TCAmountType.GrossPay: SetAmountToGrossPay(analysis_tbl); break; case TCAmountType.Calculation: // first filter out the lines that this calculation does not apply to con.ExecuteNonQuery("delete anal" + " from {0} anal" + " left join tmcddd on tmcddd.clcnum = {1} and tmcddd.recnum = anal.recnum" + " where isnull(tmcddd.amount) or tmcddd.amount = 0" , analysis_tbl, clcnum); // then run var cnt = con.GetScalar<long>("select count(*) from {0}", analysis_tbl); if (cnt > 0) { SetAmountToCalculation(analysis_tbl, clcnum); } break; default: throw new NotImplementedException(); } // filter out the items that we don't care about if (keep == null || _cached_lines != lines) { keep = con.GetTempDBF(); con.ExecuteNonQuery("create table {0} (recnum n(10,0) not null, linnum n(4,0) not null)", keep); foreach (var l in lines_array) { con.ExecuteNonQuery("insert into {0} (recnum, linnum) values ({1}, {2})" , keep, l.payrec, l.lineno); } } con.ExecuteNonQuery("delete anal from {0} anal" + " left join {1} keep on keep.recnum = anal.recnum and keep.linnum = anal.linnum" + " where isnull(keep.recnum)" , analysis_tbl, keep); // now add the linnum = 0 records to our analysis table, this should be fast foreach (var l in lines) { if (l.lineno == 0) { con.ExecuteNonQuery("insert into {0} (recnum, linnum, amount, recttl, mult, taxste) values ({1}, 0, 0, 0, 0, '')", analysis_tbl, l.payrec); } } switch (type) { case TCAmountType.GrossPay: con.ExecuteNonQuery("update anal set amount = payrec.grspay, recttl = payrec.grspay, mult = 1, taxste = payrec.taxste" + " from {0} anal" + " join payrec on payrec.recnum = anal.recnum" + " where anal.linnum = 0" , analysis_tbl); break; case TCAmountType.Calculation: con.ExecuteNonQuery("update anal set amount = tmcddd.amount, recttl = tmcddd.amount, mult = 1, taxste = payded.taxste" + " from {0} anal" + " join tmcddd on tmcddd.clcnum = {1} and tmcddd.recnum = anal.recnum" + " join payded on payded.recnum = tmcddd.clcnum" + " where anal.linnum = 0" , analysis_tbl, clcnum); break; default: throw new NotImplementedException(); } return analysis_tbl; }
public TCLineAmount(decimal _amount, decimal _payrec_ttl, TCLineKey _line, TCAmountType _type = TCAmountType.GrossPay, long _clcnum = 0) { if (_type != TCAmountType.Calculation) Validity.Assert(_clcnum == 0, "non payroll calculation analysis results cannot have a clcnum"); else Validity.Assert(_clcnum > 0, "payrol calculation result must have a clcnum"); clcnum = _clcnum; type = _type; line = _line; amount = _amount; payrec_ttl = _payrec_ttl; }