/**
Get connection to member database
@return [SQLManager object]
*/
public function db()
{
if (!class_exists('FannieDB')) {
include_once dirname(__FILE__) . '/../data/FannieDB.php';
}
return \FannieDB::get(\FannieConfig::factory()->get('OP_DB'));
}
public function testPages()
{
$pages = FannieAPI::listModules('FanniePage', true);
$config = FannieConfig::factory();
$logger = new FannieLogger();
$op_db = $config->get('OP_DB');
$dbc = FannieDB::get($op_db);
$dbc->throwOnFailure(true);
foreach ($pages as $page_class) {
$obj = new $page_class();
$obj->setConfig($config);
$obj->setLogger($logger);
$dbc->selectDB($op_db);
$obj->setConnection($dbc);
if ($page_class == 'WfcHtViewSalaryPage') {
continue;
}
// header/redirect problem
ob_start();
$pre = $obj->preprocess();
ob_end_clean();
$this->assertInternalType('boolean', $pre);
$help = $obj->helpContent();
$this->assertInternalType('string', $help);
$auth = $obj->checkAuth();
$this->assertInternalType('boolean', $pre);
$obj->unitTest($this);
}
}
protected static function getPluginList()
{
$plugin_list = \FannieConfig::factory()->get('PLUGIN_LIST');
if (is_array($plugin_list)) {
return $plugin_list;
} else {
return array();
}
}
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'type')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs type');
return $ret;
}
// validate additional arguments
switch (strtolower($args->type)) {
case 'vendor':
if (!property_exists($args, 'vendor_id')) {
// vendor ID required
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs vendor_id');
return $ret;
} elseif (!property_exists($args, 'sku') && !property_exists($args, 'upc')) {
// either sku or upc is required
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs sku or upc');
return $ret;
}
break;
default:
// unknown type argument
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// lookup results
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->type)) {
case 'vendor':
$vendor = new \VendorItemsModel($dbc);
$vendor->vendorID($args->vendor_id);
if (property_exists($args, 'sku')) {
$vendor->sku($args->sku);
} elseif (property_exists($args, 'upc')) {
$vendor->upc($args->upc);
}
foreach ($vendor->find() as $v) {
$ret['sku'] = $v->sku();
$ret['upc'] = $v->upc();
$ret['size'] = $v->size();
$ret['units'] = $v->units();
$ret['brand'] = $v->brand();
$ret['description'] = $v->description();
$ret['cost'] = $v->cost();
break;
}
return $ret;
}
}
public function testItemFlags()
{
$config = FannieConfig::factory();
$connection = FannieDB::get($config->OP_DB);
/**
Setup preconditions for the test
*/
$upc = BarcodeLib::padUPC('16');
$product = new ProductsModel($connection);
$product->upc($upc);
$product->store_id(0);
$product->load();
if ($product->numflag() != 0) {
$product->numflag(0);
}
$product->save();
$module = new ItemFlagsModule();
$module->setConnection($connection);
$module->setConfig($config);
$form = new \COREPOS\common\mvc\ValueContainer();
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Handled empty input');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(0, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/**
Simulate real form input
*/
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = array(1, 3);
// 0b101 == 5
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Saving item flags failed');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(5, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/* put record back to normal */
$product->numflag(0);
$product->save();
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = 'not_an_array';
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(false, $saved, 'Accepted invalid input');
}
public function export_order($id)
{
$config = FannieConfig::factory();
$dbc = FannieDB::get($config->get('OP_DB'));
$items = new PurchaseOrderItemsModel($dbc);
$items->orderID($id);
$NL = "\r\n";
echo 'productCode,quantity' . $NL;
foreach ($items->find() as $item) {
echo str_pad($item->sku(), 7, '0', STR_PAD_LEFT);
echo ',';
echo $item->quantity();
echo $NL;
}
}
public function testEquityHistory()
{
$config = FannieConfig::factory();
$config->set('FANNIE_EQUITY_DEPARTMENTS', '1 2');
$logger = new FannieLogger();
$trans_db = $config->get('TRANS_DB');
$dbc = FannieDB::get($trans_db);
// create two test rows in dlog_15
$today = date('Y-m-d');
$trans_num = '1-1-1';
$dlog = new Dlog15Model($dbc);
$dlog->tdate($today);
$dlog->trans_num($trans_num);
$dlog->department(1);
$dlog->total(10);
$dlog->card_no(1);
$dlog->trans_id(1);
$dlog->save();
$dlog->trans_id(2);
$dlog->save();
$task = new EquityHistoryTask();
$task->setConfig($config);
$task->setLogger($logger);
$task->run();
// verify test rows were logged
$dbc->selectDB($trans_db);
$query = 'SELECT SUM(stockPurchase), COUNT(*) FROM stockpurchases WHERE card_no=1';
$res = $dbc->query($query);
$row = $dbc->fetchRow($res);
$this->assertEquals(20, $row[0]);
$this->assertEquals(2, $row[1]);
// add a third test row
$dlog->department(2);
$dlog->trans_id(3);
$dlog->save();
$task->run();
// verify only the new row is logged
$dbc->selectDB($trans_db);
$query = 'SELECT SUM(stockPurchase), COUNT(*) FROM stockpurchases WHERE card_no=1';
$res = $dbc->query($query);
$row = $dbc->fetchRow($res);
$this->assertEquals(30, $row[0]);
$this->assertEquals(3, $row[1]);
}
/**
Build a standardized tooltip
@param $text the full help text
@param $doc_link URL into CORE documentation [optional]
@param $tag HTML tag type for text [default is span]
@return an HTML string
*/
public static function toolTip($text, $doc_link = False, $tag = 'span')
{
$id = '_fhtt' . rand(0, 999999);
$img = \FannieConfig::factory()->get('URL') . 'src/img/buttons/help16.png';
$text = preg_replace('/\\s\\s+/', ' ', $text);
$snippet = strlen($text) > 100 ? strip_tags(substr($text, 0, 100)) . ' ...' : False;
if ($snippet || $doc_link) {
$snippet .= ' (Click for more)';
}
if ($doc_link) {
if (!$snippet) {
$snippet = $text;
}
$text .= sprintf(' (<a href="%s">CORE Documentation</a>)', $doc_link);
}
if ($snippet || $doc_link) {
return sprintf('<a href=""
onclick="$(\'#%s\').toggle();return false;"><img src="%s" title="%s" /></a>
<%s id="%s" style="display:none;">%s</%s>', $id, $img, $snippet, $tag, $id, $text, $tag);
} else {
return sprintf('<a href="" onclick="return false;"><img src="%s" title="%s" /></a>', $img, $text);
}
}
/**
Provide lookups for the autocomplete service
@param $field [string] field name being autocompleted
@param $val [string] partial field
*/
public static function autoComplete($field, $val)
{
$config = \FannieConfig::factory();
$dbc = \FannieDB::get($config->get('OP_DB'));
if (strtolower($field) == 'mfirstname') {
list($query, $args) = self::autoCompleteFirstName($val);
} elseif (strtolower($field) == 'mlastname') {
list($query, $args) = self::autoCompleteLastName($val);
} elseif (strtolower($field) == 'maddress') {
list($query, $args) = self::autoCompleteAddress($val);
} elseif (strtolower($field) == 'mcity') {
list($query, $args) = self::autoCompleteCity($val);
} elseif (strtolower($field) == 'memail') {
list($query, $args) = self::autoCompleteEmail($val);
} else {
$query = $field;
$args = array();
}
$ret = array();
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row[0];
if (count($ret) > 50) {
break;
}
}
return $ret;
}
/**
Add an open ring record to dtransactions on the backend
@param $connection [SQLManager] database connection
@param $department [integer] department number
$param $amount [number] ring amount
@param $trans_no [integer] transaction number (dtransactions.trans_no)
@param $params [array] of column_name => value
If emp_no and register_no values are not specified, the defaults
are the configuration settings FANNIE_EMP_NO and FANNIE_REGISTER_NO.
The following columns are automatically calculated based
on department number and amount:
- upc
- description
- trans_type
- trans_status
- unitPrice
- total
- regPrice
- quantity
- ItemQtty
Negative amounts result in a refund trans_status
This method calls DTrans::addItem() so columns datetime and trans_id are
also automatically assigned.
*/
public static function addOpenRing(SQLManager $connection, $department, $amount, $trans_no, $params = array())
{
$config = FannieConfig::factory();
$model = new DepartmentsModel($connection);
$model->whichDB($config->get('OP_DB'));
$model->dept_no($department);
$model->load();
$params['trans_type'] = 'D';
$params['department'] = $department;
$params['unitPrice'] = $amount;
$params['total'] = $amount;
$params['regPrice'] = $amount;
$params['quantity'] = 1;
$params['ItemQtty'] = 1;
if ($amount < 0) {
$params['quantity'] = -1;
$params['trans_status'] = 'R';
}
$params['description'] = $model->dept_name();
$params['upc'] = abs($amount) . 'DP' . $department;
return self::addItem($connection, $trans_no, $params);
}
/**
Check for input and display the page
*/
function drawPage()
{
if (!$this->config instanceof FannieConfig) {
$this->config = FannieConfig::factory();
}
if (!$this->checkAuth() && $this->must_authenticate) {
$this->loginRedirect();
} elseif ($this->preprocess()) {
/** Use FanniePage::drawPage for the plain old html
version of the page
*/
if ($this->content_function == 'form_content') {
if (FormLib::get('json') !== '') {
$this->addOnloadCommand(FormLib::fieldJSONtoJavascript(base64_decode(FormLib::get('json'))));
}
return parent::drawPage();
}
/**
Global setting overrides default behavior
to force the menu to appear.
Unlike normal pages, the override is only applied
when the output format is HTML.
*/
if (($this->config->get('WINDOW_DRESSING') || $this->new_tablesorter) && $this->report_format == 'html') {
$this->window_dressing = true;
}
if ($this->window_dressing) {
echo $this->getHeader();
}
if ($this->readinessCheck() !== false) {
$func = $this->content_function;
echo $this->{$func}();
} else {
echo $this->errorContent();
}
if ($this->window_dressing) {
$footer = $this->getFooter();
$footer = str_ireplace('</html>', '', $footer);
$footer = str_ireplace('</body>', '', $footer);
echo $footer;
}
if ($this->report_format == 'html') {
foreach ($this->scripts as $s_url => $s_type) {
printf('<script type="%s" src="%s"></script>', $s_type, $s_url);
echo "\n";
}
$js_content = $this->javascriptContent();
if (!empty($js_content) || !empty($this->onload_commands)) {
echo '<script type="text/javascript">';
echo $js_content;
echo "\n\$(document).ready(function(){\n";
foreach ($this->onload_commands as $oc) {
if (strstr($oc, 'standardFieldMarkup()')) {
continue;
}
echo $oc . "\n";
}
echo "});\n";
echo '</script>';
}
$page_css = $this->cssContent();
if (!empty($page_css)) {
echo '<style type="text/css">';
echo $page_css;
echo '</style>';
}
echo array_reduce($this->css_files, function ($carry, $css_url) {
return $carry . sprintf('<link rel="stylesheet" type="text/css" href="%s">' . "\n", $css_url);
}, '');
}
if ($this->window_dressing || $this->report_format == 'html') {
echo '</body></html>';
}
}
// drawPage()
}
public function avgSales($upc)
{
$config = FannieConfig::factory();
$dbc = FannieDB::get($config->get('OP_DB'));
$prodP = $dbc->prepare('SELECT auto_par FROM products WHERE upc=?');
$avg = $dbc->getValue($prodP, array($upc));
if ($avg) {
return $avg;
}
$dbc = FannieDB::get($config->get('ARCHIVE_DB'));
$avg = 0.0;
if ($dbc->tableExists('productWeeklyLastQuarter')) {
$maxP = $dbc->prepare('SELECT MAX(weekLastQuarterID) FROM productWeeklyLastQuarter WHERE upc=?');
$maxR = $dbc->execute($maxP, $upc);
if ($maxR && $dbc->numRows($maxR)) {
$maxW = $dbc->fetchRow($maxR);
$avgP = $dbc->prepare('
SELECT SUM((?-weekLastQuarterID)*quantity) / SUM(weekLastQuarterID)
FROM productWeeklyLastQuarter
WHERE upc=?');
$avgR = $dbc->execute($avgP, array($maxW[0], $upc));
$avgW = $dbc->fetchRow($avgR);
$avg = $avgW[0] / 7.0;
}
} else {
$dbc = FannieDB::get($config->get('TRANS_DB'));
$avgP = $dbc->prepare('
SELECT MIN(tdate) AS min,
MAX(tdate) AS max,
' . DTrans::sumQuantity() . ' AS qty
FROM dlog_90_view
WHERE upc=?');
$avgR = $dbc->execute($avgP, array($upc));
if ($avgR && $dbc->numRows($avgR)) {
$avgW = $dbc->fetchRow($avgR);
$d1 = new DateTime($avgW['max']);
$d2 = new DateTime($avgW['min']);
$num_days = $d1->diff($d2)->format('%a') + 1;
$avg = $avgW['qty'] / $num_days;
}
}
// put the database back where we found it (probably)
$dbc = FannieDB::get($config->get('OP_DB'));
return $avg;
}
public function guessAccounts()
{
$dbc = $this->connection;
$detailP = $dbc->prepare('
SELECT o.sku,
o.internalUPC,
o.receivedTotalCost
FROM PurchaseOrderItems AS o
WHERE o.internalUPC NOT IN (
SELECT upc FROM products
)
AND o.orderID=?
AND o.receivedTotalCost <> 0');
$detailR = $dbc->execute($detailP, array($this->orderID()));
$config = FannieConfig::factory();
$soP1 = $dbc->prepare('
SELECT d.salesCode
FROM ' . $config->get('TRANS_DB') . $dbc->sep() . 'CompleteSpecialOrder AS o
INNER JOIN departments AS d ON o.department=d.dept_no
WHERE o.upc=?');
$soP2 = $dbc->prepare('
SELECT d.salesCode
FROM ' . $config->get('TRANS_DB') . $dbc->sep() . 'PendingSpecialOrder AS o
INNER JOIN departments AS d ON o.department=d.dept_no
WHERE o.upc=?');
$vdP = $dbc->prepare('
SELECT d.salesCode
FROM vendorItems AS v
INNER JOIN vendorDepartments AS p ON v.vendorDept = p.deptID
INNER JOIN departments AS d ON p.deptID=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$coding = array('n/a' => 0.0);
while ($w = $dbc->fetchRow($detailR)) {
$soR = $dbc->execute($soP1, array($w['internalUPC']));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$soR = $dbc->execute($soP2, array($w['internalUPC']));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$soR = $dbc->execute($vdP, array($w['sku'], $this->vendorID()));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$coding['n/a'] += $w['receivedTotalCost'];
}
return $coding;
}
/**
Transition mechanism. Auto-append store_id value
if only a UPC has been specified.
*/
public function load()
{
if (!isset($this->instance['store_id'])) {
$config = FannieConfig::factory();
$this->store_id($config->get('STORE_ID'));
}
return parent::load();
}
public static function pricePerUnit($price, $sizeStr)
{
$country = \FannieConfig::factory()->get('COUNTRY', 'US');
$num = "";
$unit = "";
$mult = 1;
$inNum = 1;
for ($i = 0; $i < strlen($sizeStr); $i++) {
if ($inNum == 1) {
if (is_numeric($sizeStr[$i]) or $sizeStr[$i] == ".") {
$num .= $sizeStr[$i];
} else {
if ($sizeStr[$i] == "/" or $sizeStr[$i] == "-") {
$mult = $num;
$num = "";
} else {
$inNum = 0;
$unit .= $sizeStr[$i];
}
}
} else {
$unit .= $sizeStr[$i];
}
}
$unit = ltrim($unit);
$unit = strtoupper($unit);
if (strpos($unit, "FL") !== False) {
$unit = "FLOZ";
}
if ($num == "") {
$num = 1;
}
$num = (double) $num;
$num = $num * $mult;
if ($num == 0) {
return '';
}
switch ($unit) {
case '#':
case 'LB':
case 'LBS':
if ($country == "US") {
return round($price / ($num * 16), 3) . "/OZ";
} else {
return round($price / ($num * 453.59), 3) . "/G";
}
case 'ML':
if ($country == "US") {
return round($price / ($num * 0.034), 3) . "/OZ";
} else {
return round($price / $num, 3) . "/ML";
}
case 'FLOZ':
if ($country == 'US') {
return round($price / $num, 3) . "/OZ";
} else {
return round($price / ($num * 29.5735), 3) . "/ML";
}
case 'OZ':
case 'Z':
if ($country == 'US') {
return round($price / $num, 3) . "/OZ";
} else {
return round($price / ($num * 28.35), 3) . "/G";
}
case 'PINT':
case 'PINTS':
if ($country == "US") {
return round($price / ($num * 16), 3) . "/OZ";
} else {
return round($price / ($num * 473.18), 3) . "/ML";
}
case 'GR':
case 'GRAM':
case 'GM':
case 'GRM':
case 'G':
if ($country == "US") {
return round($price / ($num * 0.035), 3) . "/OZ";
} else {
return round($price / $num, 3) . "/G";
}
case 'LTR':
case 'L':
if ($country == "US") {
return round($price / ($num * 33.814), 3) . "/OZ";
} else {
return round($price / 1000, 3) . "/ML";
}
case 'GAL':
if ($country == "US") {
return round($price / ($num * 128), 3) . "/OZ";
} else {
return round($price / ($num * 3785.41), 3) . "/ML";
}
default:
return round($price / $num, 3) . "/" . $unit;
}
return "";
}
return $parts[0];
}
private function getOptionValue($opt)
{
$parts = explode('=', $opt, 2);
return $parts[1];
}
}
if (php_sapi_name() === 'cli' && basename($_SERVER['PHP_SELF']) == basename(__FILE__)) {
if ($argc < 2) {
echo "Usage: php FannieTask.php <Task Class Name>\n";
return 1;
}
include dirname(__FILE__) . '/../config.php';
include dirname(__FILE__) . '/FannieAPI.php';
$config = FannieConfig::factory();
$logger = new FannieLogger();
FannieDispatch::setLogger($logger);
FannieDispatch::setErrorHandlers();
// prepopulate autoloader
$preload = FannieAPI::listModules('FannieTask');
$class = $argv[1];
if (!class_exists($class)) {
echo "Error: class '{$class}' does not exist\n";
return 1;
}
$obj = new $class();
if (!is_a($obj, 'FannieTask')) {
echo "Error: invalid class. Must be subclass of FannieTask\n";
return 1;
}
function ldap_login($name, $passwd)
{
$config = FannieConfig::factory();
if (!isAlphanumeric($name)) {
return false;
}
if ($passwd == "") {
return false;
}
$conn = ldap_connect($config->get('LDAP_SERVER'), $config->get('LDAP_PORT'));
if (!$conn) {
return false;
}
$search_result = ldap_search($conn, $config->get('LDAP_DN'), $config->get('LDAP_SEARCH_FIELD') . "=" . $name);
if (!$search_result) {
return false;
}
$ldap_info = ldap_get_entries($conn, $search_result);
if (!$ldap_info) {
return false;
} else {
if ($ldap_info['count'] == 0) {
return false;
}
}
$user_dn = $ldap_info[0]["dn"];
$uid = $ldap_info[0][$config->get('LDAP_UID_FIELD')][0];
$fullname = $ldap_info[0][$config->get('LDAP_RN_FIELD')][0];
if (ldap_bind($conn, $user_dn, $passwd)) {
syncUserLDAP($name, $uid, $fullname);
doLogin($name);
return true;
}
return false;
}
/**
Constructor
@param $con a SQLManager object
*/
public function __construct($con)
{
$this->connection = $con;
if (empty($this->unique)) {
foreach ($this->columns as $name => $definition) {
if (isset($definition['primary_key']) && $definition['primary_key']) {
$this->unique[] = $name;
}
}
}
// detect fully qualfied name
if (is_a($this->connection, 'SQLManager') && $this->connection->isConnected()) {
$db_name = $this->connection->defaultDatabase();
if ($this->connection->tableExists($db_name . $this->connection->sep() . $this->name)) {
$this->fq_name = $this->connection->identifier_escape($db_name) . $this->connection->sep() . $this->connection->identifier_escape($this->name);
} else {
$this->fq_name = $this->connection->identifier_escape($this->name);
}
} else {
$this->fq_name = $this->name;
}
// fq name not working right now...
$this->fq_name = $this->name;
$this->config = FannieConfig::factory();
}
public function refresh_data($trans_db, $month, $year, $day = False)
{
$config = FannieConfig::factory();
$settings = $config->get('PLUGIN_SETTINGS');
$dbc = FannieDB::get($settings['WarehouseDatabase']);
$today = time();
$lastmonth = mktime(0, 0, 0, date('n', $today) - 1, 1, date('Y', $today));
$spotlight_months = array();
for ($i = 0; $i < 2; $i++) {
$spotlight_months[] = mktime(0, 0, 0, date('n', $lastmonth) - $i, 1, date('Y', $lastmonth));
}
$lastyear = mktime(0, 0, 0, date('n', $lastmonth) - 11, 1, date('Y', $lastmonth));
$basicQ = '
SELECT card_no,
MIN(date_id) AS firstVisit,
MAX(date_id) AS lastVisit,
SUM(total) AS totalSpending,
AVG(total) AS averageSpending,
SUM(quantity) AS totalItems,
AVG(quantity) AS averageItems,
SUM(transCount) AS totalVisits
FROM sumMemSalesByDay
WHERE date_id BETWEEN ? AND ?
GROUP BY card_no';
$basicP = $dbc->prepare($basicQ);
$all_time_args = array(0, date('Ymt', $lastmonth));
$dbc->query('TRUNCATE TABLE MemberSummary');
$insQ = '
INSERT INTO MemberSummary
(card_no, firstVisit, lastVisit, totalSpending,
averageSpending, totalItems, averageItems,
totalVisits) ' . $basicQ;
$insP = $dbc->prepare($insQ);
$basicR = $dbc->execute($insP, $all_time_args);
$spotlight_args = array(date('Ym01', $spotlight_months[count($spotlight_months) - 1]), date('Ymt', $spotlight_months[0]));
$spotlight_start = date('Y-m-01', $spotlight_months[count($spotlight_months) - 1]);
$spotlight_end = date('Y-m-t', $spotlight_months[0]);
$basicR = $dbc->execute($basicP, $spotlight_args);
$upP = $dbc->prepare('
UPDATE MemberSummary
SET spotlightStart=?,
spotlightEnd=?,
spotlightTotalSpending=?,
spotlightAverageSpending=?,
spotlightTotalItems=?,
spotlightAverageItems=?,
spotlightTotalVisits=?
WHERE card_no=?');
while ($spotlight = $dbc->fetchRow($basicR)) {
$dbc->execute($upP, array($spotlight_start, $spotlight_end, $spotlight['totalSpending'], $spotlight['averageSpending'], $spotlight['totalItems'], $spotlight['averageItems'], $spotlight['totalVisits'], $spotlight['card_no']));
}
$year_args = array(date('Ym01', $lastyear), date('Ymt', $lastmonth));
$basicR = $dbc->execute($basicP, $year_args);
$year_start = date('Y-m-01', $lastyear);
$year_end = date('Y-m-t', $lastmonth);
$upP = $dbc->prepare('
UPDATE MemberSummary
SET yearStart=?,
yearEnd=?,
yearTotalSpending=?,
yearAverageSpending=?,
yearTotalItems=?,
yearAverageItems=?,
yearTotalVisits=?
WHERE card_no=?');
while ($year = $dbc->fetchRow($basicR)) {
$dbc->execute($upP, array($year_start, $year_end, $year['totalSpending'], $year['averageSpending'], $year['totalItems'], $year['averageItems'], $year['totalVisits'], $year['card_no']));
}
$oldlight = array(strtotime($spotlight_args[0]), strtotime($spotlight_args[1]));
$oldlight_args = array(date('Ym01', mktime(0, 0, 0, date('n', $oldlight[0]), 1, date('Y', $oldlight[0]) - 1)), date('Ymt', mktime(0, 0, 0, date('n', $oldlight[1]), 1, date('Y', $oldlight[1]) - 1)));
$upP = $dbc->prepare('
UPDATE MemberSummary
SET oldlightTotalSpending=?,
oldlightAverageSpending=?,
oldlightTotalItems=?,
oldlightAverageItems=?,
oldlightTotalVisits=?
WHERE card_no=?');
$basicR = $dbc->execute($basicP, $oldlight_args);
while ($old = $dbc->fetchRow($basicR)) {
$dbc->execute($upP, array($old['totalSpending'], $old['averageSpending'], $old['totalItems'], $old['averageItems'], $old['totalVisits'], $old['card_no']));
}
$longSQL = '';
$long_args = array($spotlight_args[0]);
foreach ($spotlight_months as $m) {
$longSQL .= '?,';
$long_args[] = date('m', $m);
}
$longSQL = substr($longSQL, 0, strlen($longSQL) - 1);
$basicQ = '
SELECT card_no,
MIN(date_id) AS firstVisit,
MAX(date_id) AS lastVisit,
SUM(total) AS totalSpending,
AVG(total) AS averageSpending,
SUM(quantity) AS totalItems,
AVG(quantity) AS averageItems,
SUM(transCount) AS totalVisits
FROM sumMemSalesByDay
WHERE date_id < ?
AND SUBSTRING(CONVERT(date_id,CHAR),5,2) IN (' . $longSQL . ')
GROUP BY card_no';
$basicP = $dbc->prepare($basicQ);
$upP = $dbc->prepare('
UPDATE MemberSummary
SET longlightTotalSpending=?,
longlightAverageSpending=?,
longlightTotalItems=?,
longlightAverageItems=?,
longlightTotalVisits=?
WHERE card_no=?');
$basicR = $dbc->execute($basicP, $long_args);
while ($long = $dbc->fetchRow($basicR)) {
$dbc->execute($upP, array($long['totalSpending'], $long['averageSpending'], $long['totalItems'], $long['averageItems'], $long['totalVisits'], $long['card_no']));
}
// do ranks
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY totalSpending DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET totalSpendingRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY averageSpending DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET averageSpendingRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY totalVisits DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET totalVisitsRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY yearTotalSpending DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET yearTotalSpendingRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY yearAverageSpending DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET yearAverageSpendingRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
$rank = 1;
$query = '
SELECT card_no
FROM MemberSummary
ORDER BY yearTotalVisits DESC, card_no';
$rankP = $dbc->prepare('
UPDATE MemberSummary
SET yearTotalVisitsRank=?
WHERE card_no=?');
$result = $dbc->query($query);
while ($row = $dbc->fetchRow($result)) {
$dbc->execute($rankP, array($rank, $row['card_no']));
$rank++;
}
}
private static function loadFromCsv($table, $file, $sql)
{
$fannie_host = \FannieConfig::factory()->get('SERVER');
$LOCAL = 'LOCAL';
if ($fannie_host == '127.0.0.1' || $fannie_host == 'localhost') {
$LOCAL = '';
}
$prep = $sql->prepare_statement("LOAD DATA {$LOCAL} INFILE\n '{$file}'\n INTO TABLE {$table}\n FIELDS TERMINATED BY ','\n ESCAPED BY '\\\\'\n OPTIONALLY ENCLOSED BY '\"'\n LINES TERMINATED BY '\\r\\n'");
$try = $sql->exec_statement($prep);
if ($try === false) {
$error = $sql->error();
echo "<br><span style='color:red;'>" . (strlen($error) ? $error : 'Unknown error') . " executing:<br><code>{$prep[0]}</code><br></span><br>\n";
return false;
} else {
return true;
}
}
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'type')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// validate additional arguments
switch (strtolower($args->type)) {
case 'settings':
if (!property_exists($args, 'dept_no')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
break;
case 'children':
if (!property_exists($args, 'superID') && !property_exists($args, 'dept_no')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
if (property_exists($args, 'superID') && is_array($args->superID) && count($args->superID) != 2) {
// range must specify exactly two superIDs
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
if (property_exists($args, 'dept_no') && is_array($args->dept_no) && count($args->dept_no) != 2) {
// range must specify exactly two dept_nos
$et['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
break;
default:
// unknown type argument
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// lookup results
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->type)) {
case 'settings':
$model = new DepartmentsModel($dbc);
$model->dept_no($args->dept_no);
$model->load();
$ret['tax'] = $model->dept_tax();
$ret['fs'] = $model->dept_fs();
$ret['discount'] = $model->dept_discount();
$ret['seeID'] = $model->dept_see_id();
$ret['margin'] = $model->margin();
return $ret;
case 'children':
$query = '';
$params = array();
if (property_exists($args, 'dept_no')) {
$query = '
SELECT s.subdept_no AS id,
s.subdept_name AS name
FROM departments AS d
INNER JOIN subdepts AS s ON d.dept_no=s.dept_ID ';
if (property_exists($args, 'superID') && is_numeric($args->superID)) {
$query .= ' INNER JOIN superdepts AS a ON d.dept_no=a.dept_ID ';
}
if (is_array($args->dept_no)) {
$query .= ' WHERE d.dept_no BETWEEN ? AND ? ';
$params[] = $args->dept_no[0];
$params[] = $args->dept_no[1];
} else {
$query .= ' WHERE d.dept_no = ? ';
$params[] = $args->dept_no;
}
if (property_exists($args, 'superID') && is_numeric($args->superID)) {
$query .= ' AND a.superID = ? ';
$params[] = $args->superID;
}
$query .= ' ORDER BY s.subdept_no';
} else {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM superdepts AS s
INNER JOIN departments AS d ON d.dept_no=s.dept_ID ';
if (is_array($args->superID)) {
$query .= ' WHERE s.superID BETWEEN ? AND ? ';
$params[] = $args->superID[0];
$params[] = $args->superID[1];
} else {
$query .= ' WHERE s.superID = ? ';
$params[] = $args->superID;
}
$query .= ' ORDER BY d.dept_no';
// support meta-options for all departments
if (!is_array($args->superID) && $args->superID < 0) {
if ($args->superID == -1) {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM departments AS d
ORDER BY d.dept_no';
$params = array();
} elseif ($args->superID == -2) {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM departments AS d
INNER JOIN MasterSuperDepts AS m ON d.dept_no=m.dept_ID
WHERE m.superID <> 0
ORDER BY d.dept_no';
$params = array();
}
}
}
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $params);
while ($w = $dbc->fetch_row($res)) {
$ret[] = array('id' => $w['id'], 'name' => $w['name']);
}
return $ret;
}
}
public function preFlight()
{
if (!$this->config instanceof FannieConfig) {
$this->config = FannieConfig::factory();
}
if (!$this->checkAuth() && $this->must_authenticate) {
$this->loginRedirect();
exit;
}
}
/**
Copy a table from the lanes to the server
@param $table string table name
@param $db string 'op' or 'trans'
(default is 'trans')
@param $truncate integer
(default is TRUNCATE_SOURCE)
@return array
- sending => boolean attempted to copy table
- messages => string result information
*/
public static function pullTable($table, $db = 'trans', $truncate = self::TRUNCATE_SOURCE)
{
$config = \FannieConfig::factory();
$op_db = $config->get('OP_DB');
$trans_db = $config->get('TRANS_DB');
$lanes = $config->get('LANES');
$ret = array('sending' => True, 'messages' => '');
$db = strtolower($db);
if ($db != 'op' && $db != 'trans') {
$ret['sending'] = False;
$ret['messages'] = 'Error: Invalid database: ' . $db;
return $ret;
} elseif (empty($table)) {
$ret['sending'] = False;
$ret['messages'] = 'Error: No table given';
return $ret;
} elseif (!preg_match('/^[A-Za-z0-9_]$/', $table)) {
$ret['sending'] = False;
$ret['messages'] = 'Error: Illegal table name: ' . $table;
return $ret;
}
// use the transfer option in SQLManager to copy
// records from each lane
$server_db = $db == 'op' ? $op_db : $trans_db;
$dbc = \FannieDB::get($server_db);
if ($truncate & self::TRUNCATE_DESTINATION) {
$dbc->query("TRUNCATE TABLE {$table}", $server_db);
}
$laneNumber = 1;
foreach ($lanes as $lane) {
$dbc->add_connection($lane['host'], $lane['type'], $lane[$db], $lane['user'], $lane['pw']);
if ($dbc->connections[$lane[$db]]) {
$success = $dbc->transfer($lane[$db], "SELECT * FROM {$table}", $server_db, "INSERT INTO {$table}");
if ($truncate & self::TRUNCATE_SOURCE) {
$dbc->query("TRUNCATE TABLE {$table}", $lane[$db]);
}
$dbc->close($lane[$db]);
if ($success) {
$ret['messages'] .= "Lane {$laneNumber} ({$lane['host']}) {$table} completed successfully";
} else {
$ret['messages'] .= "Error: Lane {$laneNumber} ({$lane['host']}) {$table} completed but with some errors";
}
} else {
$ret['messages'] .= "Error: Couldn't connect to lane {$laneNumber} ({$lane['host']})";
}
$laneNumber++;
}
return $ret;
}
/**
Get all email addresses associated with
the given department
@param $dept [int] department number
@return [string] email address(es) or [boolean] false
*/
public static function getAddresses($dept)
{
$conf = \FannieConfig::factory();
$dbc = \FannieDB::getReadOnly($conf->get('OP_DB'));
$query = 'SELECT superID from superdepts WHERE dept_ID=? GROUP BY superID';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($dept));
$emails = '';
while ($row = $dbc->fetch_row($res)) {
$model = new \SuperDeptEmailsModel($dbc);
$model->superID($row['superID']);
if (!$model->load()) {
continue;
}
$addr = $model->emailAddress();
if ($addr && !strstr($emails, $addr)) {
if ($emails !== '') {
$emails .= ', ';
}
$emails .= $addr;
}
}
return $emails === '' ? false : $emails;
}
public function listItems()
{
$url = \FannieConfig::factory()->get('URL');
$ret = '<table class="table tablesorter tablesorter-core">';
$ret .= '<thead>';
$ret .= '<tr>
<th>UPC</th><th>Brand</th><th>Description</th><th>Price</th><th>Origin</th>
<td><label>Exclude
<input type="checkbox" onchange="$(\'.exclude-checkbox\').prop(\'checked\', $(this).prop(\'checked\'));" />
</label>
</td>
</tr>';
$ret .= '</thead><tbody>';
$data = $this->loadItems();
foreach ($data as $item) {
$ret .= sprintf('<tr>
<td><a href="%sitem/ItemEditorPage.php?searchupc=%s" target="_edit%s">%s</a></td>
<input type="hidden" name="update_upc[]" value="%d" />
<td>
<span class="collapse">%s</span>
<input class="FannieSignageField form-control" type="text"
name="update_brand[]" value="%s" /></td>
<td>
<span class="collapse">%s</span>
<input class="FannieSignageField form-control" type="text"
name="update_desc[]" value="%s" /></td>
<td>%.2f</td>
<td><input class="FannieSignageField form-control" type="text"
name="update_origin[]" value="%s" /></td>
<td><input type="checkbox" name="exclude[]" class="exclude-checkbox" value="%s" /></td>
</tr>', $url, $item['upc'], $item['upc'], $item['upc'], $item['upc'], $item['brand'], $item['brand'], str_replace('"', '"', $item['description']), str_replace('"', '"', $item['description']), $item['normal_price'], $item['originName'], $item['upc']);
}
$ret .= '</tbody></table>';
return $ret;
}
/**
Render the current page if appropriate
The page is only shown if it's accessed
directly rather than through an include().
@param $custom_errors @deprecated
This behavior is controlled by config variable
FANNIE_CUSTOM_ERRORS. The optional parameter
remains for th sake of compatibility but does
not do anything. It will go away when all calls
to this method have been cleaned up.
*/
public static function conditionalExec($custom_errors = true)
{
$frames = debug_backtrace();
// conditionalExec() is the only function on the stack
if (count($frames) == 1) {
$config = FannieConfig::factory();
$logger = new FannieLogger();
if ($config->get('SYSLOG_SERVER')) {
$logger->setRemoteSyslog($config->get('SYSLOG_SERVER'), $config->get('SYSLOG_PORT'), $config->get('SYSLOG_PROTOCOL'));
}
$op_db = $config->get('OP_DB');
$dbc = FannieDB::get($op_db);
self::setLogger($logger);
// setup error logging
self::setErrorHandlers();
// initialize locale & gettext
self::i18n();
// draw current page
$page = basename(filter_input(INPUT_SERVER, 'PHP_SELF'));
$class = substr($page, 0, strlen($page) - 4);
if ($class != 'index' && class_exists($class)) {
$obj = new $class();
if ($dbc->isConnected($op_db)) {
// write URL log
self::logUsage($dbc, $op_db);
/*
$auth = self::authOverride($dbc, $op_db, $class);
if ($auth) {
$obj->setPermissions($auth);
}
*/
}
$obj->setConfig($config);
$obj->setLogger($logger);
if (is_a($obj, 'FannieReportPage')) {
$dbc = FannieDB::getReadOnly($op_db);
}
$obj->setConnection($dbc);
$obj->draw_page();
} else {
trigger_error('Missing class ' . $class, E_USER_NOTICE);
}
}
}
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'field') || !property_exists($args, 'search')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
} else {
if (strlen($args->search) < 1) {
// search term is too short
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
}
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->field)) {
case 'item':
$res = false;
if (!is_numeric($args->search)) {
$prep = $dbc->prepare('SELECT p.upc,
p.description
FROM products AS p
LEFT JOIN productUser AS u ON u.upc=p.upc
WHERE p.description LIKE ?
OR p.brand LIKE ?
OR u.description LIKE ?
OR u.brand LIKE ?
GROUP BY p.upc,
p.description
ORDER BY p.description');
$term = '%' . $args->search . '%';
$res = $dbc->execute($prep, array($term, $term, $term, $term));
} elseif (ltrim($args->search, '0') != '') {
$prep = $dbc->prepare('
SELECT p.upc,
p.upc AS description
FROM products AS p
WHERE p.upc LIKE ?
GROUP BY p.upc');
$res = $dbc->execute($prep, array('%' . $args->search . '%'));
}
while ($res && ($row = $dbc->fetch_row($res))) {
$ret[] = array('label' => $row['description'], 'value' => $row['upc']);
}
case 'brand':
$prep = $dbc->prepare('SELECT brand
FROM products
WHERE brand LIKE ?
GROUP BY brand
ORDER BY brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'long_brand':
$prep = $dbc->prepare('
SELECT u.brand
FROM productUser AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
WHERE u.brand LIKE ?
GROUP BY u.brand
ORDER BY u.brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'vendor':
$prep = $dbc->prepare('SELECT vendorID,
vendorName
FROM vendors
WHERE vendorName LIKE ?
ORDER BY vendorName');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['vendorName'];
}
if ($dbc->tableExists('prodExtra')) {
$prep = $dbc->prepare('SELECT distributor
FROM prodExtra
WHERE distributor LIKE ?
GROUP BY distributor
ORDER BY distributor');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
if (!in_array($row['distributor'], $ret)) {
$ret[] = $row['distributor'];
}
}
}
return $ret;
case 'mfirstname':
case 'mlastname':
case 'maddress':
case 'mcity':
case 'memail':
return \COREPOS\Fannie\API\member\MemberREST::autoComplete($args->field, $args->search);
case 'sku':
$query = 'SELECT sku
FROM vendorItems
WHERE sku LIKE ? ';
$param = array($args->search . '%');
if (property_exists($args, 'vendor_id')) {
$query .= ' AND vendorID=? ';
$param[] = $args->vendor_id;
}
$query .= 'GROUP BY sku
ORDER BY sku';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['sku'];
if (count($ret) > 50) {
break;
}
}
return $ret;
case 'unit':
$query = '
SELECT unitofmeasure
FROM products
WHERE unitofmeasure LIKE ?
GROUP BY unitofmeasure
ORDER BY unitofmeasure';
$param = array($args->search . '%');
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetchRow($res)) {
$ret[] = $row['unitofmeasure'];
if (count($ret) > 50) {
break;
}
}
return $ret;
default:
return $ret;
}
}
/**
Check if authentication is enabled in
Fannie's configuration
@return boolean
*/
private static function enabled()
{
$enabled = FannieConfig::factory()->get('AUTH_ENABLED', false);
return $enabled ? true : false;
}
public static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array())
{
$base_table = self::selectStruct($dlog, $start_date, $end_date);
$dt_col = $dlog ? 'tdate' : 'datetime';
$clone_table = $dlog ? 'dlog_15' : 'transarchive';
/**
Grouping is required
*/
if (!is_array($groupby) || count($groupby) == 0) {
return $base_table;
}
/**
Validate group by columns
*/
$model = new DTransactionsModel(null);
$columns = $model->getColumns();
$insert_cols = array();
$select_cols = array();
for ($i = 0; $i < count($groupby); $i++) {
$group = $groupby[$i];
if (isset($columns[$group])) {
$insert_cols[] = $group;
$select_cols[] = $group;
} elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) {
$col_definition = $matches[1];
$col_alias = $matches[2];
if (isset($columns[$col_alias])) {
$insert_cols[] = $col_alias;
$select_cols[] = $group;
$groupby[$i] = $col_definition;
} else {
return $base_table;
}
} else {
return $base_table;
}
}
/**
Always include a datetime column
*/
if (!in_array($dt_col, $insert_cols)) {
$insert_cols[] = $dt_col;
$select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col;
}
/**
Create randomly named temporary table based
on the structure of dlog_15 or transachive
*/
$config = FannieConfig::factory();
$sep = $connection->sep();
$random_name = uniqid('temp' . rand(1000, 9999));
$temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name;
$clone_table = $config->get('TRANS_DB') . $sep . $clone_table;
$temp_name = $connection->temporaryTable($temp_table, $clone_table);
if ($temp_name === false) {
return $base_table;
}
/**
Build a query to insert aggregated rows into
the temporary table
*/
$query = 'INSERT INTO ' . $temp_name . '(';
foreach ($insert_cols as $c) {
$query .= $c . ',';
}
$query .= 'total, quantity) ';
$query .= ' SELECT ';
foreach ($select_cols as $c) {
$query .= $c . ',';
}
/**
Always aggregate by total & quantity
*/
$query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity
FROM __TRANSACTION_TABLE__
WHERE ' . $dt_col . ' BETWEEN ? AND ? ';
$params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59');
/**
Add a where clause if one has been specified
*/
if (property_exists($where, 'sql') && is_array($where->sql)) {
foreach ($where->sql as $sql) {
$query .= ' AND ' . $sql;
}
}
if (property_exists($where, 'params') && is_array($where->params)) {
foreach ($where->params as $p) {
$params[] = $p;
}
}
/**
Add the group by clause
*/
$query .= ' GROUP BY ';
foreach ($groupby as $group) {
$query .= $group . ',';
}
$query = substr($query, 0, strlen($query) - 1);
/**
Split monthly archive union if needed
*/
$source_tables = array();
if (strstr($base_table, ' UNION ')) {
preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches);
foreach ($matches[1] as $m) {
$source_tables[] = $m;
}
} else {
$source_tables = array($base_table);
}
/**
Load data into temporary table from source table(s)
using built query
*/
foreach ($source_tables as $source_table) {
$insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query);
$prep = $connection->prepare($insertQ);
if (!$connection->execute($prep, $params)) {
return $base_table;
}
}
return $temp_name;
}
/**
Helper function to send messages
@param $templateID [ScheduledEmailTemplatesModel] template
@param $address [string] recipient email address
@param $data [keyed array] of placeholder values
@return [boolean] success
*/
public static function sendEmail($template, $address, $data = array())
{
$config = FannieConfig::factory();
$settings = $config->get('PLUGIN_SETTINGS');
$mail = new PHPMailer();
$mail->isSMTP();
$mail->Host = '127.0.0.1';
$mail->Port = 25;
$mail->SMTPAuth = false;
$mail->From = $settings['ScheduledEmailFrom'];
$mail->FromName = $settings['ScheduledEmailFromName'];
$mail->addReplyTo($settings['ScheduledEmailReplyTo']);
$mail->addAddress($address);
$mail->Subject = $template->subject();
if ($template->hasHTML()) {
$mail->isHTML(true);
$mail->Body = self::substitutePlaceholders($template->htmlCopy(), $data);
if ($template->hasText()) {
$mail->AltBody = self::substitutePlaceholders($template->textCopy(), $data);
}
return $mail->send();
} elseif ($template->hasText()) {
$mail->isHTML(false);
$mail->Body = self::substitutePlaceholders($template->textCopy(), $data);
return $mail->send();
} else {
return false;
}
}