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