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