Beispiel #1
0
 function list_content()
 {
     global $FANNIE_OP_DB, $FANNIE_URL;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $supertype = FormLib::get_form_value('supertype', 'dept');
     $manufacturer = FormLib::get_form_value('manufacturer', '');
     $mtype = FormLib::get_form_value('mtype', 'prefix');
     $deptStart = FormLib::get_form_value('deptStart', 0);
     $deptEnd = FormLib::get_form_value('deptEnd', 0);
     $deptMulti = FormLib::get('departments', array());
     $subDepts = FormLib::get('subdepts', array());
     $super = FormLib::get_form_value('deptSub');
     $vendorID = FormLib::get('vendor');
     $upc_list = FormLib::get('u', array());
     $inUse = FormLib::get('inUse', 1);
     $store = FormLib::get('store', 0);
     $sort = FormLib::get_form_value('sort', 'Department');
     $order = 'dept_name';
     if ($sort === 'UPC') {
         $order = 'i.upc';
     } elseif ($sort === 'Description') {
         $order = 'i.description, i.upc';
     }
     $ret = 'Report sorted by ' . $sort . '<br />';
     if ($supertype == 'dept' && $super === '') {
         $ret .= 'Department ' . $deptStart . ' to ' . $deptEnd . '<br />';
     } else {
         if ($supertype == 'dept') {
             $ret .= 'Sub department ' . $super . '<br />';
         } else {
             if ($supertype == 'manu') {
                 $ret .= _('Brand') . ' ' . $manufacturer . '<br />';
             } else {
                 if ($supertype == 'vendor') {
                     $vendor = new VendorsModel($dbc);
                     $vendor->vendorID($vendorID);
                     $vendor->load();
                     $ret .= 'Vendor ' . $vendor->vendorName() . '<br />';
                 }
             }
         }
     }
     $ret .= date("F j, Y, g:i a") . '<br />';
     $page_url = sprintf('ProductListPage.php?supertype=%s&deptStart=%s&deptEnd=%s&deptSub=%s&manufacturer=%s&mtype=%s&vendor=%d', $supertype, $deptStart, $deptEnd, $super, $manufacturer, $mtype, $vendorID);
     if (!$this->excel) {
         $ret .= '<form action="' . filter_input(INPUT_SERVER, 'PHP_SELF') . '" method="post" id="excel-form">
             <input type="hidden" name="supertype" value="' . $supertype . '" />
             <input type="hidden" name="deptStart" value="' . $deptStart . '" />
             <input type="hidden" name="deptEnd" value="' . $deptEnd . '" />
             <input type="hidden" name="deptSub" value="' . $super . '" />
             <input type="hidden" name="manufacturer" value="' . $manufacturer . '" />
             <input type="hidden" name="mtype" value="' . $mtype . '" />
             <input type="hidden" name="vendor" value="' . $vendorID . '" />
             <input type="hidden" name="inUse" value="' . $inUse . '" />
             <input type="hidden" name="excel" value="yes" />';
         if (is_array($subDepts)) {
             foreach ($subDepts as $s) {
                 $ret .= '<input type="hidden" name="subdepts[]" value="' . $s . '" />';
             }
         }
         if (is_array($upc_list)) {
             foreach ($upc_list as $u) {
                 $ret .= '<input type="hidden" name="u[]" value="' . $u . '" />';
             }
         }
         $ret .= '</form>';
         $ret .= sprintf('<a href="" onclick="$(\'#excel-form\').submit();return false;">Save to Excel</a> 
             &nbsp; &nbsp; <a href="%s">Back</a><br />', basename(__FILE__));
     }
     /** base select clause and joins **/
     $query = "\n            SELECT i.upc,\n                i.description,\n                i.brand,\n                d.dept_name as department,\n                i.normal_price,\n                (CASE WHEN i.tax = 1 THEN 'X' WHEN i.tax=0 THEN '-' ELSE LEFT(t.description,1) END) as Tax,              \n                (CASE WHEN i.foodstamp = 1 THEN 'X' ELSE '-' END) as FS,\n                (CASE WHEN i.discount = 0 THEN '-' ELSE 'X'END) as DISC,\n                (CASE WHEN i.scale = 1 THEN 'X' ELSE '-' END) as WGHd,\n                (CASE WHEN i.local > 0 AND o.originID IS NULL THEN 'X' \n                  WHEN i.local > 0 AND o.originID IS NOT NULL THEN LEFT(o.shortName,1) ELSE '-' END) as local,\n                COALESCE(v.vendorName, x.distributor) AS distributor,\n                i.cost,\n                i.store_id,\n                l.description AS storeName\n            FROM products as i \n                LEFT JOIN departments as d ON i.department = d.dept_no\n                LEFT JOIN taxrates AS t ON t.id = i.tax\n                LEFT JOIN prodExtra as x on i.upc = x.upc\n                LEFT JOIN vendors AS v ON i.default_vendor_id=v.vendorID\n                LEFT JOIN Stores AS l ON i.store_id=l.storeID\n                LEFT JOIN origins AS o ON i.local=o.originID";
     /** add extra joins if this lookup requires them **/
     if ($supertype == 'dept' && $super !== '') {
         if ($super >= 0) {
             $query .= ' LEFT JOIN superdepts AS s ON i.department=s.dept_ID ';
         } elseif ($super == -2) {
             $query .= ' LEFT JOIN MasterSuperDepts AS s ON i.department=s.dept_ID ';
         }
     } elseif ($supertype == 'vendor') {
         $query .= ' LEFT JOIN vendors AS z ON z.vendorName=x.distributor ';
     }
     /** build where clause and parameters based on
         the lookup type **/
     $query .= ' WHERE 1=1 ';
     $args = array();
     if ($supertype == 'dept' && $super !== '') {
         if ($super >= 0) {
             $query .= ' AND s.superID=? ';
             $args = array($super);
         } elseif ($super == -2) {
             $query .= ' AND s.superID <> 0 ';
         }
         if ($deptStart != 0 && $deptEnd != 0) {
             $query .= ' AND i.department BETWEEN ? AND ? ';
             $args[] = $deptStart;
             $args[] = $deptEnd;
         } elseif (count($deptMulti) > 0) {
             $query .= ' AND i.department IN (';
             foreach ($deptMulti as $d) {
                 $query .= '?,';
                 $args[] = $d;
             }
             $query = substr($query, 0, strlen($query) - 1) . ')';
         }
         if (is_array($subDepts) && count($subDepts) > 0) {
             $query .= ' AND i.subdept IN (';
             foreach ($subDepts as $s) {
                 $query .= '?,';
                 $args[] = $s;
             }
             $query = substr($query, 0, strlen($query) - 1) . ')';
         }
     } elseif ($supertype == 'manu' && $mtype == 'prefix') {
         $query .= ' AND i.upc LIKE ? ';
         $args = array('%' . $manufacturer . '%');
     } elseif ($supertype == 'manu' && $mtype != 'prefix') {
         $query .= ' AND (i.brand LIKE ? OR x.manufacturer LIKE ?) ';
         $args = array('%' . $manufacturer . '%', '%' . $manufacturer . '%');
     } elseif ($supertype == 'vendor') {
         $query .= ' AND (i.default_vendor_id=? OR z.vendorID=?) ';
         $args = array($vendorID, $vendorID);
     } elseif ($supertype == 'upc') {
         $inp = '';
         foreach ($upc_list as $u) {
             $inp .= '?,';
             $args[] = $u;
         }
         $inp = substr($inp, 0, strlen($inp) - 1);
         $query .= ' AND i.upc IN (' . $inp . ') ';
     } else {
         $query .= ' AND i.department BETWEEN ? AND ? ';
         $args = array($deptStart, $deptEnd);
         if (is_array($subDepts) && count($subDepts) > 0) {
             $query .= ' AND i.subdept IN (';
             foreach ($subDepts as $s) {
                 $query .= '?,';
                 $args[] = $s;
             }
         }
     }
     if ($inUse == 1) {
         $query .= ' AND i.inUse=1 ';
     } else {
         $query .= ' AND i.inUse=0 ';
     }
     if ($store > 0) {
         $query .= ' AND i.store_id=? ';
         $args[] = $store;
     }
     /** finish building query w/ order clause **/
     $query .= 'ORDER BY ' . $order;
     if ($order != "i.upc") {
         $query .= ",i.upc";
     }
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($prep, $args);
     if ($result === false || $dbc->num_rows($result) == 0) {
         return 'No data found!';
     }
     $ret .= '<table class="table table-striped table-bordered tablesorter small">
         <thead>
         <tr>';
     $ret .= "<th>UPC</th><th>Brand</th><th>Description</th><th>Dept</th><th>" . _('Vendor') . "</th><th>Cost</th><th>Price</th>";
     $ret .= "<th>Tax</th><th>FS</th><th>Disc</th><th>Wg'd</th><th>Local</th>";
     if (!$this->excel && $this->canEditItems !== false) {
         $ret .= '<th>&nbsp;</th>';
     }
     $ret .= "</tr></thead><tbody>";
     $multi = $this->config->get('STORE_MODE') == 'HQ' ? true : false;
     while ($row = $dbc->fetch_row($result)) {
         $ret .= '<tr id="' . $row[0] . '">';
         $enc = base64_encode($row[1]);
         if (!$this->excel) {
             $ret .= "<td align=center class=\"td_upc\"><a href=ItemEditorPage.php?searchupc={$row['0']}>{$row['0']}</a>";
             if ($multi) {
                 $ret .= ' (' . substr($row['storeName'], 0, 1) . ')';
             }
             if ($this->canDeleteItems !== false) {
                 $ret .= " <a href=\"\" onclick=\"deleteCheck('{$row['0']}','{$enc}'); return false;\">";
                 $ret .= \COREPOS\Fannie\API\lib\FannieUI::deleteIcon() . '</a>';
             }
             $ret .= '</td>';
             $ret .= '<input type="hidden" class="hidden_upc" value="' . $row[0] . '" />';
             $ret .= '<input type="hidden" class="hidden_store_id" value="' . $row['store_id'] . '" />';
         } else {
             $ret .= "<td align=center>{$row['0']}</td>";
         }
         $ret .= "<td align=center class=\"td_brand clickable\">{$row['brand']}</td>";
         $ret .= "<td align=center class=\"td_desc clickable\">{$row['description']}</td>";
         $ret .= "<td align=center class=\"td_dept clickable\">{$row['department']}</td>";
         $ret .= "<td align=center class=\"td_supplier clickable\">{$row['distributor']}</td>";
         $ret .= "<td align=center class=\"td_cost clickable\">" . sprintf('%.2f', $row['cost']) . "</td>";
         $ret .= "<td align=center class=\"td_price clickable\">{$row['normal_price']}</td>";
         $ret .= "<td align=center class=td_tax>{$row['Tax']}</td>";
         $ret .= "<td align=center class=td_fs>{$row['FS']}</td>";
         $ret .= "<td align=center class=td_disc>{$row['DISC']}</td>";
         $ret .= "<td align=center class=td_wgt>{$row['WGHd']}</td>";
         $ret .= "<td align=center class=td_local>{$row['local']}</td>";
         if (!$this->excel && $this->canEditItems !== False) {
             $ret .= "<td align=center class=td_cmd><a href=\"\" \n                    class=\"edit-link\"\n                    onclick=\"edit(\$(this).closest('tr')); return false;\">" . \COREPOS\Fannie\API\lib\FannieUI::editIcon() . '</a>
                 <a href="" class="save-link collapse"
                 onclick="save($(this).closest(\'tr\')); return false;">' . \COREPOS\Fannie\API\lib\FannieUI::saveIcon() . '</a></td>';
         }
         $ret .= "</tr>\n";
     }
     $ret .= '</tbody></table>';
     if ($this->excel) {
         header('Content-Type: application/ms-excel');
         header('Content-Disposition: attachment; filename="itemList.csv"');
         $array = \COREPOS\Fannie\API\data\DataConvert::htmlToArray($ret);
         $ret = \COREPOS\Fannie\API\data\DataConvert::arrayToCsv($array);
     } else {
         $this->add_script('../src/javascript/tablesorter/jquery.tablesorter.min.js');
         $this->add_onload_command("\$('.tablesorter').tablesorter();\n");
     }
     return $ret;
 }
Beispiel #2
0
    }
    printf('<tr><th>Ttl</th><th>%s</th>
        <th>$%.2f</th><th>%.2f%%</th>
        <th>$%.2f</th><th>%.2f%%</th>
        <th>$%.2f</th></tr>', $sname, $slocal, 100 * ($slocal / $sttl), $sc, 100 * ($sc / $sttl), $sttl);
    printf('<tr><td colspan=7>&nbsp;</td></tr>
        <tr><th>Ttl</th><th>Store</th>
        <th>$%.2f</th><th>%.2f%%</th>
        <th>$%.2f</th><th>%.2f%%</th>
        <th>$%.2f</th></tr>', $master_totals[0], 100 * ($master_totals[0] / $master_totals[2]), $master_totals[1], 100 * ($master_totals[1] / $master_totals[2]), $master_totals[2]);
    echo '</table>';
    if (isset($_REQUEST['excel'])) {
        $output = ob_get_contents();
        ob_end_clean();
        $array = \COREPOS\Fannie\API\data\DataConvert::htmlToArray($output);
        $xls = \COREPOS\Fannie\API\data\DataConvert::arrayToExcel($array);
        echo $xls;
    }
} else {
    $page_title = "Fannie : Local Sales Report";
    $header = "Local Sales Report";
    include $FANNIE_ROOT . 'src/header.html';
    $lastMonday = "";
    $lastSunday = "";
    $ts = mktime(0, 0, 0, date("n"), date("j") - 1, date("Y"));
    while ($lastMonday == "" || $lastSunday == "") {
        if (date("w", $ts) == 1 && $lastSunday != "") {
            $lastMonday = date("Y-m-d", $ts);
        } elseif (date("w", $ts) == 0) {
            $lastSunday = date("Y-m-d", $ts);
        }
Beispiel #3
0
    return;
}
header("Content-Disposition: inline; filename=mailingList.csv");
header("Content-Description: PHP3 Generated Data");
header("Content-type: application/vnd.ms-excel; name='excel'");
include '../config.php';
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = $dbc->prepare_statement("SELECT CardNo, \n          LastName, \n          FirstName, \n          street,\n          city,\n          state,\n          zip,\n          phone,\n          memType,\n          end_date\n          FROM custdata AS c\n      LEFT JOIN meminfo AS m\n      ON c.CardNo=m.card_no\n      LEFT JOIN memDates AS d\n      ON c.CardNo=d.card_no\n          WHERE \n      c.Type='PC'\n          AND (end_date > " . $dbc->now() . " \n        or end_date = '' \n        or end_date is null\n        or end_date='1900-01-01 00:00:00'\n        or end_date='0000-00-00 00:00:00')\n          AND ads_OK = 1\n          AND personNum = 1\n      AND LastName <> 'NEW MEMBER'\n          order by m.card_no");
$result = $dbc->exec_statement($query);
$ret = array();
while ($row = $dbc->fetch_row($result)) {
    $new = array(11);
    $new[0] = $row[0];
    $new[1] = $row[1];
    $new[2] = $row[2];
    if (strstr($row[3], "\n") === False) {
        $new[3] = $row[3];
        $new[4] = "";
    } else {
        $pts = explode("\n", $row[3]);
        $new[3] = $pts[0];
        $new[4] = $pts[1];
    }
    for ($i = 5; $i <= 10; $i++) {
        $new[$i] = $row[$i - 1];
    }
    $ret[] = $new;
}
$xls = \COREPOS\Fannie\API\data\DataConvert::arrayToCsv($ret);
echo $xls;
Beispiel #4
0
 /**
   Helper: check default export args
 */
 protected function formatCheck()
 {
     if (FormLib::get('excel') === 'xls') {
         $this->report_format = 'xls';
         $this->window_dressing = false;
         /**
           Verify whether Excel support is available. If it is not,
           fall back to CSV output. Should probably
           generate some kind of log message or notification.
         */
         if (!\COREPOS\Fannie\API\data\DataConvert::excelSupport()) {
             $this->report_format = 'csv';
         }
     } elseif (FormLib::get('excel') === 'csv') {
         $this->report_format = 'csv';
         $this->window_dressing = false;
     }
 }