private static void SetCalculationMults(Env.TempDBFPointer analysis_tbl, payded ded)
{
var con = analysis_tbl.connection;
// workmans comp and liability insurance both come from the wkrcmp table
if (ded.clcmth == 17 && ded.taxtyp == 11)
{
con.ExecuteNonQuery("update anal set anal.mult = (wkrcmp.pctrte / 100.00000) * (IIF(inlist(tmcdln.paytyp, 2, 3), tmcdln.cmpsub, tmcdln.payrte))"
+ " from {0} anal"
+ " join tmcdln on tmcdln.recnum = anal.recnum and tmcdln.linnum = anal.linnum"
+ " join wkrcmp on wkrcmp.recnum = tmcdln.cmpcde"
+ " where tmcdln.paytyp < 7"
, analysis_tbl);
goto cleanup; // we are done
}
if (ded.clcmth == 17 && ded.taxtyp == 12)
{
con.ExecuteNonQuery("update anal set anal.mult = (wkrcmp.libins / 100.00000) * (IIF(inlist(tmcdln.paytyp, 2, 3), tmcdln.cmpsub, tmcdln.payrte))"
+ " from {0} anal"
+ " join tmcdln on tmcdln.recnum = anal.recnum and tmcdln.linnum = anal.linnum"
+ " join wkrcmp on wkrcmp.recnum = tmcdln.cmpcde"
+ " where tmcdln.paytyp < 7"
, analysis_tbl);
goto cleanup; // we are done
}
if (ded.benovr == 0) // rates across timecard lines within a single pay record should not vary
{
// we use 1, we can use anything we want because the results will get scaled correctly to match
// tmcddd later on, but 1 leads to few rounding errors
con.ExecuteNonQuery("update {0} set mult = 1", analysis_tbl);
/// We shouldn't do this...
/*
if (ded.uninum > 0)
{
// if the benefit is a union benefit, set the mult to zero for non-union lines
con.ExecuteNonQuery("update anal set anal.mult = 0"
+ " from {0} anal"
+ " join tmcdln on tmcdln.linnum = anal.linnum and tmcdln.recnum = anal.recnum"
+ " join paygrp on paygrp.recnum = tmcdln.paygrp"
+ " where isnull(paygrp.uninum) or paygrp.uninum <> {1}"
, analysis_tbl, ded.uninum);
}
*/
}
else // we have to pull the rates from paygroups
{
con.ExecuteNonQuery("update anal set anal.mult = benfit.dedrte"
+ " from {0} anal"
+ " join tmcdln on tmcdln.linnum = anal.linnum and tmcdln.recnum = anal.recnum"
+ " join benfit on benfit.paygrp = tmcdln.paygrp and benfit.dednum = {1}"
, analysis_tbl, ded.recnum);
// sometimes the calculation was removed from benfits, so we have to make these work as well
using (var zero_payrecs = con.GetTempDBF())
{
con.ExecuteNonQuery("select recnum, sum(mult) as mult_sum from {0} into table {1} group by recnum", analysis_tbl, zero_payrecs);
con.ExecuteNonQuery("delete from {0} where mult_sum <> 0", zero_payrecs);
con.ExecuteNonQuery("delete zeros"
+ " from {0} zeros"
+ " left join tmcddd on tmcddd.recnum = zeros.recnum and tmcddd.clcnum = {1}"
+ " where isnull(tmcddd.amount)"
, zero_payrecs, ded.recnum);
if (ded.benovr == 0)
{
con.ExecuteNonQuery("update anal set mult = 1"
+ " from {0} anal"
+ " join {1} zeros on zeros.recnum = anal.recnum"
, analysis_tbl, zero_payrecs);
}
else
{
using (var valid_paygroups = con.GetTempDBF())
{
// this is really just a guess, but it should usually be a valid one
con.ExecuteNonQuery("select recnum as paygrp from paygrp where paygrp.uninum = {0} into table {1}", ded.uninum, valid_paygroups);
con.ExecuteNonQuery("update anal set mult = 1"
+ " from {0} anal"
+ " join tmcdln on tmcdln.linnum = anal.linnum and tmcdln.recnum = anal.recnum"
+ " join {1} zeros on zeros.recnum = anal.recnum"
+ " join {2} vpg on vpg.paygrp = tmcdln.paygrp"
, analysis_tbl, zero_payrecs, valid_paygroups);
}
}
}
}
// certain calculation methods multiply by different amounts based on the paytyp, instead of having special
// logic later, we just multiply the multipliers by them now
var special = new decimal[][] {
// clcmth, paytyp, mult
new decimal[] { 12, 1, 0 },
new decimal[] { 13, 2, 1.5m },
new decimal[] { 13, 3, 2 },
new decimal[] { 14, 2, 1.5m },
new decimal[] { 14, 3, 1.5m },
new decimal[] { 15, 2, 1.5m },
new decimal[] { 15, 3, 2.0m },
new decimal[] { 16, 2, 2 },
new decimal[] { 16, 3, 2 },
new decimal[] { 4, 2, 0 },
new decimal[] { 4, 3, 0 },
new decimal[] { 9, 2, 0 },
new decimal[] { 9, 3, 0 },
};
foreach (var modification in special)
{
if(ded.clcmth != modification[0])
continue;
con.ExecuteNonQuery("update anal set anal.mult = anal.mult * {1} where tmcdln.paytyp {2}"
+ " from {0} anal"
+ " join tmcdln on tmcdln.recnum = anal.recnum and tmcdln.linnum = anal.linnum"
, analysis_tbl
, modification[2]
, modification[1] == 1 ? "not in (2, 3)" : " = " + modification[1].ToString());
}
var remove_above_typ3 = new decimal[] { 4, 9, 12, 13, 14, 15, 16 };
if (remove_above_typ3.Contains(ded.clcmth))
{
con.ExecuteNonQuery("update anal set anal.mult = 0"
+ " from {0} anal"
+ " join tmcdln on tmcdln.linnum = anal.linnum and tmcdln.recnum = anal.recnum"
+ " where tmcdln.paytyp > 3"
, analysis_tbl);
}
if (ded.loctax > 0)
{
con.ExecuteNonQuery("update anal set anal.mult = 0"
+ " from {0} anal"
+ " join tmcdln on tmcdln.linnum = anal.linnum and tmcdln.recnum = anal.recnum"
+ " where tmcdln.loctax <> {1}"
, analysis_tbl, ded.loctax);
}
if (ded.taxste != null && ded.taxste.Trim() != "")
{
con.ExecuteNonQuery("update {0} set mult = 0 where taxste <> {1}", analysis_tbl, ded.taxste.FoxproQuote());
}
cleanup:
// remove line items with a multiplier of 0, but first we have to make sure that not all the multipliers are 0
using (var zeros = con.GetTempDBF())
{
con.ExecuteNonQuery("select recnum, sum(mult) as ttl from {0} group by recnum into table {1}", analysis_tbl, zeros);
con.ExecuteNonQuery("delete from {0} where ttl <> 0", zeros);
con.ExecuteNonQuery("delete anal"
+ " from {0} anal"
+ " left join {1} zeros on zeros.recnum = anal.recnum"
+ " where isnull(zeros.recnum) and anal.mult = 0"
, analysis_tbl, zeros);
}
}
private static void _do_weight(payded ded, Env.TempDBFPointer analysis_tbl)
{
// wtf, this isn't making any changes!
// sometimes anal.recttl is 0, if this is the case, set it to 1 because all our amounts should be 0 anyways and therefore
analysis_tbl.connection.ExecuteNonQuery(
"update anal set anal.amount = nvl(tmcddd.amount * (anal.amount / anal.recttl), 0), anal.recttl = nvl(tmcddd.amount, 0)"
+ " from {0} anal"
+ " left join tmcddd on tmcddd.recnum = anal.recnum and tmcddd.clcnum = {1}"
, analysis_tbl, ded.recnum);
}
private static BaseType SelectBaseType(payded ded)
{
if (ded.clcmth == 17)
{
if (ded.taxtyp == 11 || ded.taxtyp == 12)
return BaseType.Hours;
}
long[] hours_types = new long[] { 4, 5, 8, 9 };
return hours_types.Contains(ded.clcmth) ? BaseType.Hours : BaseType.Gross;
}