public static function createMinServer($db, $name)
{
$errors = array();
$type = $db->dbms_name();
if (CoreLocal::get('laneno') == 0) {
$errors[] = array('struct' => 'No structures created for lane #0', 'query' => 'None', 'details' => 'Zero is reserved for server');
return $errors;
}
$models = array('\\COREPOS\\pos\\lib\\models\\trans\\DTransactionsModel', '\\COREPOS\\pos\\lib\\models\\trans\\SuspendedModel', '\\COREPOS\\pos\\lib\\models\\trans\\EfsnetRequestModel', '\\COREPOS\\pos\\lib\\models\\trans\\EfsnetRequestModModel', '\\COREPOS\\pos\\lib\\models\\trans\\EfsnetResponseModel', '\\COREPOS\\pos\\lib\\models\\trans\\EfsnetTokensModel', '\\COREPOS\\pos\\lib\\models\\trans\\PaycardTransactionsModel', '\\COREPOS\\pos\\lib\\models\\trans\\CapturedSignatureModel', '\\COREPOS\\pos\\lib\\models\\trans\\CcReceiptViewModel');
foreach ($models as $class) {
$obj = new $class($db);
$errors[] = $obj->createIfNeeded($name);
}
$dlogQ = "CREATE VIEW dlog AS\n SELECT datetime AS tdate,\n register_no,\n emp_no,\n trans_no,\n upc,\n CASE \n WHEN trans_subtype IN ('CP','IC') OR upc LIKE '%000000052' THEN 'T' \n WHEN upc = 'DISCOUNT' THEN 'S' \n ELSE trans_type \n END AS trans_type,\n CASE \n WHEN upc = 'MAD Coupon' THEN 'MA' \n WHEN upc LIKE '%00000000052' THEN 'RR' \n ELSE trans_subtype \n END AS trans_subtype,\n trans_status,\n department,\n quantity,\n unitPrice,\n total,\n tax,\n foodstamp,\n ItemQtty,\n memType,\n staff,\n numflag,\n charflag,\n card_no,\n trans_id, " . $db->concat($db->convert('emp_no', 'char'), "'-'", $db->convert('register_no', 'char'), "'-'", $db->convert('trans_no', 'char'), '') . " AS trans_num\n FROM dtransactions\n WHERE trans_status NOT IN ('D','X','Z')\n AND emp_no <> 9999 \n AND register_no <> 99";
if (!$db->table_exists("dlog", $name)) {
$errors = InstallUtilities::dbStructureModify($db, 'dlog', $dlogQ, $errors);
}
$ttG = "\n CREATE VIEW TenderTapeGeneric AS\n SELECT tdate, \n emp_no, \n register_no,\n trans_no,\n CASE \n WHEN trans_subtype = 'CP' AND upc LIKE '%MAD%' THEN ''\n WHEN trans_subtype IN ('EF','EC','TA') THEN 'EF'\n ELSE trans_subtype\n END AS trans_subtype,\n CASE \n WHEN trans_subtype = 'ca' THEN\n CASE WHEN total >= 0 THEN total ELSE 0 END\n ELSE -1 * total\n END AS tender\n FROM dlog\n WHERE tdate >= " . $db->curdate() . "\n AND trans_subtype NOT IN ('0','')";
if (!$db->table_exists("TenderTapeGeneric", $name)) {
InstallUtilities::dbStructureModify($db, 'TenderTapeGeneric', $ttG, $errors);
}
return $errors;
}
function buildLTTViewsMySQL($db, $errors = array())
{
//--------------------------------------------------------------
// CREATE lttSummary VIEW
//--------------------------------------------------------------
$createStr = "CREATE view lttsummary as\n select \n (case when min(datetime) is null then " . $db->now() . " else min(datetime) end) as tdate,\n max(card_no) as card_no, \n CAST(sum(total) AS decimal(10,2)) as runningTotal,\n CAST(sum(case when discounttype = 1 then discount else 0 end) AS decimal(10,2)) as discountTTL,\n CAST(sum(case when discountable <> 0 and tax <> 0 then total else 0 end) AS decimal(10,2)) as discTaxable,\n CAST(sum(case when discounttype in (2,3) then memDiscount else 0 end) AS decimal(10,2)) as memSpecial,\n CAST(sum(case when discounttype=4 THEN memDiscount ELSE 0 END) AS decimal(10,2)) as staffSpecial,\n CAST(sum(case when discountable = 0 then 0 else total end) AS decimal(10,2)) as discountableTTL,\n ";
$taxRatesQ = "select id,description from taxrates order by id";
$taxRatesR = $db->query($taxRatesQ);
while ($taxRatesW = $db->fetch_row($taxRatesR)) {
$createStr .= "CAST(sum(case when (trans_type = 'I' or trans_type = 'D') and tax = " . $taxRatesW[0] . " and discountable = 0 then total else 0 end) AS decimal(10,2)) as noDiscTaxable_" . $taxRatesW[1] . ",\n";
$createStr .= "CAST(sum(case when (trans_type = 'I' or trans_type = 'D') and tax = " . $taxRatesW[0] . " and discountable <> 0 then total else 0 end) AS decimal(10,2)) as discTaxable_" . $taxRatesW[1] . ",\n";
$createStr .= "CAST(sum(case when (trans_type = 'I' or trans_type = 'D') and tax = " . $taxRatesW[0] . " and discountable = 0 and foodstamp=1 then total else 0 end) AS decimal(10,2)) as fsTaxable_" . $taxRatesW[1] . ",\n";
$createStr .= "CAST(sum(case when (trans_type = 'I' or trans_type = 'D') and tax = " . $taxRatesW[0] . " and discountable <> 0 and foodstamp=1 then total else 0 end) AS decimal(10,2)) as fsDiscTaxable_" . $taxRatesW[1] . ",\n";
}
$createStr .= "\nCAST(sum(case when trans_subtype = 'MI' or trans_subtype = 'CX' then total else 0 end) AS decimal(10,2)) as chargeTotal,\nCAST(sum(case when department = 990 then total else 0 end) AS decimal(10,2)) as paymentTotal,\nCAST(sum(case when trans_type = 'T' and department = 0 then total else 0 end) AS decimal(10,2)) as tenderTotal,\n";
$createStr .= "CAST(sum(case when trans_subtype = 'FS' or trans_subtype = 'EF' then total else 0 end) AS decimal(10,2)) as fsTendered,\nCAST(sum(case when foodstamp = 1 and discountable = 0 then total else 0 end) AS decimal(10,2)) as fsNoDiscTTL,\nCAST(sum(case when foodstamp = 1 and discountable <> 0 then total else 0 end) AS decimal(10,2)) as fsDiscTTL,\n(case when (max(percentDiscount) is null or max(percentDiscount) < 0) then 0.00 else max(CAST(percentDiscount AS decimal)) end) as percentDiscount,\nCAST(sum(case when numflag=1 THEN total ELSE 0 END) AS decimal(10,2)) as localTotal,\nCAST(sum(case when trans_status='V' THEN -total ELSE 0 END) AS decimal(10,2)) as voidTotal,\nmax(trans_id) as LastID\nfrom localtemptrans WHERE trans_type <> 'L'\n";
$errors = InstallUtilities::dbStructureModify($db, 'lttsummary', 'DROP VIEW lttsummary', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'lttsummary', $createStr, $errors);
$rpQ = str_replace("select", "select emp_no,register_no,trans_no,", $createStr);
$rpQ = str_replace("localtemptrans", "localtranstoday", $rpQ);
$rpQ = str_replace("lttsummary", "rp_lttsummary", $rpQ);
$rpQ .= " AND datetime >= CURRENT_DATE GROUP BY emp_no,register_no,trans_no";
$errors = InstallUtilities::dbStructureModify($db, 'rp_lttsummary', 'DROP VIEW rp_lttsummary', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'rp_lttsummary', $rpQ, $errors);
//echo str_replace("\n","<br />",$createStr)."<br />";
//echo "<hr />";
//--------------------------------------------------------------
// CREATE lttSubTotals VIEW
//--------------------------------------------------------------
$createStr = "CREATE VIEW lttsubtotals AS\n select tdate,\n";
$ratesQ = "select description,rate from taxrates";
$ratesR = $db->query($ratesQ);
$desc = array();
$rates = array();
while ($ratesW = $db->fetch_row($ratesR)) {
array_push($desc, $ratesW[0]);
array_push($rates, $ratesW[1]);
}
if (count($rates) > 0) {
$createStr .= "CAST(";
for ($i = 0; $i < count($rates); $i++) {
$createStr .= "(noDiscTaxable_" . $desc[$i] . " * " . $rates[$i] . ") + ";
$createStr .= "(discTaxable_" . $desc[$i] . " * ((100-percentDiscount)/100) * " . $rates[$i] . ") + ";
}
$createStr = substr($createStr, 0, strlen($createStr) - 2);
$createStr .= " AS decimal(10,2)) as taxTotal,\n";
} else {
$createStr .= "0 as taxTotal,\n";
}
$createStr .= "fsTendered,\nCAST(fsTendered + fsNoDiscTTL + (fsDiscTTL * ((100-percentDiscount)/100)) AS DECIMAL(10,2)) AS fsEligible,\n";
if (count($rates) > 0) {
for ($i = 0; $i < count($rates); $i++) {
$createStr .= "CAST((fsDiscTaxable_" . $desc[$i] . "*((100-percentDiscount)/100)) + fsTaxable_" . $desc[$i] . " AS decimal(10,2)) as fsTaxable_" . $desc[$i] . ",";
$createStr .= "CAST((fsDiscTaxable_" . $desc[$i] . "*((100-percentDiscount)/100)*" . $rates[$i] . ")+(fsTaxable_" . $desc[$i] . "*" . $rates[$i] . ") AS decimal(10,2)) as fsTax_" . $desc[$i] . ",\n";
}
} else {
$createStr .= "0 as fsTax,\n";
}
$createStr .= "CAST(discountableTTL * percentDiscount / 100 AS decimal(10,2)) as transDiscount\n\nfrom lttsummary\n";
$errors = InstallUtilities::dbStructureModify($db, 'lttsubtotals', 'DROP VIEW lttsubtotals', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'lttsubtotals', $createStr, $errors);
$rpQ = str_replace("select", "select emp_no,register_no,trans_no,", $createStr);
$rpQ = str_replace("lttsummary", "rp_lttsummary", $rpQ);
$rpQ = str_replace("lttsubtotals", "rp_lttsubtotals", $rpQ);
$errors = InstallUtilities::dbStructureModify($db, 'rp_lttsubtotals', 'DROP VIEW rp_lttsubtotals', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'rp_lttsubtotals', $rpQ, $errors);
//echo str_replace("\n","<br />",$createStr)."<br />";
//echo "<hr />";
//--------------------------------------------------------------
// CREATE SubTotals VIEW
//--------------------------------------------------------------
$createStr = "CREATE view subtotals as\nselect\n(case when l.LastID is null then 0 else l.LastID end) as LastID,\nl.card_no as card_no,\nl.runningTotal as runningTotal,\nl.discountableTTL as discountableTotal,\nl.tenderTotal as tenderTotal,\nl.chargeTotal as chargeTotal,\nl.paymentTotal as paymentTotal,\nl.discountTTL as discountTTL,\nl.memSpecial as memSpecial,\nl.staffSpecial as staffSpecial,\ns.fsEligible as fsEligible,\n";
$ratesQ = "select description,rate from taxrates order by rate desc";
$ratesR = $db->query($ratesQ);
$desc = array();
$rates = array();
while ($ratesW = $db->fetch_row($ratesR)) {
array_push($desc, $ratesW[0]);
array_push($rates, $ratesW[1]);
}
$fsTaxStr = "CAST(CASE WHEN ";
for ($i = 0; $i < count($rates); $i++) {
$fsTaxStr .= "s.fsTaxable_" . $desc[$i] . "+";
}
$fsTaxStr = substr($fsTaxStr, 0, strlen($fsTaxStr) - 1);
$fsTaxStr .= " = 0 THEN 0 ELSE CASE WHEN l.fsTendered <> 0 AND -1 * l.fsTendered >= ";
for ($i = 0; $i < count($rates); $i++) {
$fsTaxStr .= "s.fsTaxable_" . $desc[$i] . "+";
}
$fsTaxStr = substr($fsTaxStr, 0, strlen($fsTaxStr) - 1);
$fsTaxStr .= " THEN -1 * (";
for ($i = 0; $i < count($rates); $i++) {
$fsTaxStr .= "s.fsTax_" . $desc[$i] . "+";
}
$fsTaxStr = substr($fsTaxStr, 0, strlen($fsTaxStr) - 1);
$fsTaxStr .= ") ELSE CASE ";
for ($i = 0; $i < count($rates); $i++) {
$fsTaxStr .= "WHEN -1*l.fsTendered ";
for ($j = $i - 1; $j >= 0; $j--) {
$fsTaxStr .= "-s.fsTaxable_" . $desc[$j];
}
$fsTaxStr .= "<= s.fsTaxable_" . $desc[$i];
$fsTaxStr .= " THEN -(";
for ($j = $i - 1; $j >= 0; $j--) {
$fsTaxStr .= "s.fsTax_" . $desc[$j] . "+";
}
$fsTaxStr .= "((-1*l.fsTendered ";
for ($j = $i - 1; $j >= 0; $j--) {
$fsTaxStr .= "-s.fsTaxable_" . $desc[$j];
}
$fsTaxStr .= ") * " . $rates[$i] . "))";
}
$fsTaxStr .= " ELSE 0 ";
$fsTaxStr .= " END END END AS decimal(10,2))\n";
if (count($rates) > 0) {
$createStr .= $fsTaxStr . " as fsTaxExempt,\n";
$createStr .= "CAST(s.taxTotal+" . $fsTaxStr . " AS decimal(10,2)) as taxTotal,\n";
} else {
$createStr .= "0 as fsTaxExempt,\n";
$createStr .= "0 as taxTotal,\n";
}
$createStr .= "\ns.transDiscount as transDiscount,\nl.percentDiscount as percentDiscount,\nl.localTotal as localTotal,\nl.voidTotal as voidTotal\nfrom lttsummary l, lttsubtotals s where l.tdate = s.tdate\n";
$errors = InstallUtilities::dbStructureModify($db, 'subtotals', 'DROP VIEW subtotals', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'subtotals', $createStr, $errors);
$rpQ = str_replace("select", "select l.emp_no,l.register_no,l.trans_no,", $createStr);
$rpQ = str_replace("lttsummary", "rp_lttsummary", $rpQ);
$rpQ = str_replace("lttsubtotals", "rp_lttsubtotals", $rpQ);
$rpQ = str_replace("view subtotals", "view rp_subtotals", $rpQ);
$rpQ .= " AND l.emp_no=s.emp_no AND \n l.register_no=s.register_no AND\n l.trans_no=s.trans_no";
$errors = InstallUtilities::dbStructureModify($db, 'rp_subtotals', 'DROP VIEW rp_subtotals', $errors);
$errors = InstallUtilities::dbStructureModify($db, 'rp_subtotals', $rpQ, $errors);
//echo str_replace("\n","<br />",$createStr)."<br />";
return $errors;
}
