public function saveconfig()
{
$calc = new billingcalculator($this->params['id']);
$conf = serialize($calc->calculator->parseConfig($this->params));
$calc->update(array('config' => $conf));
expHistory::back();
}
function generateOrderReport()
{
global $db;
//eDebug($this->params);
$p = $this->params;
//eDebug();
//build
$start_sql = "SELECT DISTINCT(o.id), ";
$count_sql = "SELECT COUNT(DISTINCT(o.id)) as c, ";
$sql = "o.invoice_id, FROM_UNIXTIME(o.purchased,'%c/%e/%y %h:%i:%s %p') as purchased_date, b.firstname as bfirst, b.lastname as blast, o.grand_total, os.title as status_title, ot.title as order_type";
if (count($p['order_status_changed']) == 1 && $p['order_status_changed'][0] != -1 || count($p['order_status_changed']) > 1 || !empty($p['include_status_date']) && (!empty($p['date-sstartdate']) || !empty($p['date-senddate']))) {
$sql .= ", FROM_UNIXTIME(osc.created_at,'%c/%e/%y %h:%i:%s %p') as status_changed_date";
}
$sql .= " from " . $db->prefix . "orders as o ";
$sql .= "INNER JOIN " . $db->prefix . "orderitems as oi ON oi.orders_id = o.id ";
$sql .= "INNER JOIN " . $db->prefix . "order_type as ot ON ot.id = o.order_type_id ";
$sql .= "INNER JOIN " . $db->prefix . "product as p ON oi.product_id = p.id ";
//if ($p['order_type'][0] != -1) $sql .= "INNER JOIN " . $db->prefix . "order_type as ot ON o.order_type_id = ot.id ";
$sql .= "INNER JOIN " . $db->prefix . "order_status as os ON os.id = o.order_status_id ";
if (count($p['order_status_changed']) == 1 && $p['order_status_changed'][0] != -1 || count($p['order_status_changed']) > 1 || !empty($p['include_status_date']) && (!empty($p['date-sstartdate']) || !empty($p['date-senddate']))) {
$sql .= "INNER JOIN " . $db->prefix . "order_status_changes as osc ON osc.orders_id = o.id ";
}
$sql .= "INNER JOIN " . $db->prefix . "billingmethods as b ON b.orders_id = o.id ";
$sql .= "INNER JOIN " . $db->prefix . "shippingmethods as s ON s.id = oi.shippingmethods_id ";
$sql .= "LEFT JOIN " . $db->prefix . "geo_region as gr ON (gr.id = b.state OR gr.id = s.state) ";
if ($p['discounts'][0] != -1) {
$sql .= "LEFT JOIN " . $db->prefix . "order_discounts as od ON od.orders_id = o.id ";
}
$sqlwhere = "WHERE o.purchased != 0";
if (!empty($p['include_purchased_date']) && !empty($p['date-pstartdate'])) {
$sqlwhere .= " AND o.purchased >= " . strtotime($p['date-pstartdate'] . " " . $p['time-h-pstartdate'] . ":" . $p['time-m-pstartdate'] . " " . $p['ampm-pstartdate']);
}
/*if ($p->['time-h-startdate'] == )
if ($p->['time-m-startdate'] == )
if ($p->['ampm-startdate'] == )*/
if (!empty($p['include_purchased_date']) && !empty($p['date-penddate'])) {
$sqlwhere .= " AND o.purchased <= " . strtotime($p['date-penddate'] . " " . $p['time-h-penddate'] . ":" . $p['time-m-penddate'] . " " . $p['ampm-penddate']);
}
/*if ($p->['date-enddate'] == )
if ($p->['time-h-enddate'] == )
if ($p->['time-m-enddate'] == )
if ($p->['ampm-enddate'] == )*/
if (!empty($p['include_status_date']) && !empty($p['date-sstartdate'])) {
$sqlwhere .= " AND osc.created_at >= " . strtotime($p['date-sstartdate'] . " " . $p['time-h-sstartdate'] . ":" . $p['time-m-sstartdate'] . " " . $p['ampm-sstartdate']);
}
if (!empty($p['include_status_date']) && !empty($p['date-senddate'])) {
$sqlwhere .= " AND osc.created_at <= " . strtotime($p['date-senddate'] . " " . $p['time-h-senddate'] . ":" . $p['time-m-senddate'] . " " . $p['ampm-senddate']);
}
$inc = 0;
$sqltmp = '';
foreach ($p['order_status'] as $os) {
if ($os == -1) {
continue;
} else {
if ($inc == 0) {
$inc++;
$sqltmp .= " AND (o.order_status_id = " . $os;
} else {
$sqltmp .= " OR o.order_status_id = " . $os;
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
$inc = 0;
$sqltmp = '';
foreach ($p['order_status_changed'] as $osc) {
if ($osc == -1) {
continue;
} else {
if ($inc == 0) {
$inc++;
//$sqltmp .= " AND ((osc.to_status_id = " . $osc . " AND (osc.from_status_id != " . $osc . ")";
$sqltmp .= " AND (osc.to_status_id = " . $osc;
} else {
//$sqltmp .= " OR (osc.to_status_id = " . $osc . " AND (osc.from_status_id != " . $osc . ")";
$sqltmp .= " OR osc.to_status_id = " . $osc;
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
$inc = 0;
$sqltmp = '';
foreach ($p['order_type'] as $ot) {
if ($ot == -1) {
continue;
} else {
if ($inc == 0) {
$inc++;
$sqltmp .= " AND (o.order_type_id = " . $ot;
} else {
$sqltmp .= " OR o.order_type_id = " . $ot;
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
if (!empty($p['order-range-num'])) {
$operator = '';
switch ($p['order-range-op']) {
case 'g':
$operator = '>';
break;
case 'l':
$operator = '<';
break;
case 'e':
$operator = '=';
break;
}
$sqlwhere .= " AND o.invoice_id" . $operator . $p['order-range-num'];
}
if (!empty($p['order-price-num'])) {
$operator = '';
switch ($p['order-price-op']) {
case 'g':
$operator = '>';
break;
case 'l':
$operator = '<';
break;
case 'e':
$operator = '=';
break;
}
$sqlwhere .= " AND o.grand_total" . $operator . $p['order-price-num'];
}
if (!empty($p['pnam'])) {
$sqlwhere .= " AND p.title LIKE '%" . $p['pnam'] . "%'";
}
if (!empty($p['sku'])) {
$sqlwhere .= " AND p.model LIKE '%" . $p['sku'] . "%'";
}
$inc = 0;
$sqltmp = '';
if (isset($p['product_status'])) {
foreach ($p['product_status'] as $pstat) {
if ($pstat == -1) {
continue;
}
$product_status = new product_status($pstat);
if ($inc == 0) {
$inc++;
$sqltmp .= " AND (oi.products_status = '" . $product_status->title . "'";
} else {
$sqltmp .= " OR oi.products_status = '" . $product_status->title . "'";
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
if (!empty($p['uidata'])) {
$sqlwhere .= " AND oi.user_input_fields != '' AND oi.user_input_fields != 'a:0:{}'";
}
$inc = 0;
$sqltmp = '';
foreach ($p['discounts'] as $d) {
if ($d == -1) {
continue;
} else {
if ($inc == 0) {
$inc++;
$sqltmp .= " AND (od.discounts_id = " . $d;
} else {
$sqltmp .= " OR od.discounts_id = " . $d;
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
if (!empty($p['blshpname'])) {
$sqlwhere .= " AND (b.firstname LIKE '%" . $p['blshpname'] . "%'";
$sqlwhere .= " OR s.firstname LIKE '%" . $p['blshpname'] . "%'";
$sqlwhere .= " OR b.lastname LIKE '%" . $p['blshpname'] . "%'";
$sqlwhere .= " OR s.lastname LIKE '%" . $p['blshpname'] . "%')";
}
if (!empty($p['email'])) {
$sqlwhere .= " AND (b.email LIKE '%" . $p['email'] . "%'";
$sqlwhere .= " OR s.email LIKE '%" . $p['email'] . "%')";
}
if (!empty($p['zip'])) {
if ($p['bl-sp-zip'] == 'b') {
$sqlwhere .= " AND b.zip LIKE '%" . $p['zip'] . "%'";
} else {
if ($p['bl-sp-zip'] == 's') {
$sqlwhere .= " AND s.zip LIKE '%" . $p['zip'] . "%'";
}
}
}
if (isset($p['state'])) {
$inc = 0;
$sqltmp = '';
foreach ($p['state'] as $s) {
if ($s == -1) {
continue;
} else {
if ($inc == 0) {
$inc++;
if ($p['bl-sp-state'] == 'b') {
$sqltmp .= " AND (b.state = " . $s;
} else {
if ($p['bl-sp-state'] == 's') {
$sqltmp .= " AND (s.state = " . $s;
}
}
} else {
if ($p['bl-sp-state'] == 'b') {
$sqltmp .= " OR b.state = " . $s;
} else {
if ($p['bl-sp-state'] == 's') {
$sqltmp .= " OR s.state = " . $s;
}
}
}
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
}
if (isset($p['payment_method'])) {
$inc = 0;
$sqltmp = '';
//get each calculator's id
foreach ($p['payment_method'] as $s) {
if ($s == -1) {
continue;
}
if ($s == 'VisaCard' || $s == 'AmExCard' || $s == 'MasterCard' || $s == 'DiscoverCard') {
$paymentQuery = 'b.billing_options LIKE "%' . $s . '%"';
} else {
$bc = new billingcalculator();
$calc = $bc->findBy('calculator_name', $s);
$paymentQuery = 'billingcalculator_id = ' . $calc->id;
}
if ($inc == 0) {
$inc++;
$sqltmp .= " AND ( " . $paymentQuery;
} else {
$sqltmp .= " OR " . $paymentQuery;
}
}
if (!empty($sqltmp)) {
$sqlwhere .= $sqltmp .= ")";
}
}
//echo $sql . $sqlwhere . "<br>";
/*
Need: order, orderitems, order status, ordertype, billingmethods, geo region, shipping methods, products
[date-startdate] =>
[time-h-startdate] =>
[time-m-startdate] =>
[ampm-startdate] => am
[date-enddate] =>
[time-h-enddate] =>
[time-m-enddate] =>
[ampm-enddate] => am
[order_status] => Array
(
[0] => 0
[1] => 1
[2] => 2
)
[order_type] => Array
(
[0] => 0
[1] => 2
)
[order-range-op] => e
[order-range-num] =>
[order-price-op] => l
[order-price-num] =>
[pnam] =>
[sku] =>
[discounts] => Array
(
[0] => -1
)
[blshpname] =>
[email] =>
[bl-sp-zip] => s
[zip] =>
[bl-sp-state] => s
[state] => Array
(
[0] => -1
)
[status] => Array
(
[0] => -1
)
)
*/
//$sqlwhere .= " ORDER BY purchased_date DESC";
$count_sql .= $sql . $sqlwhere;
$sql = $start_sql . $sql;
expSession::set('order_print_query', $sql . $sqlwhere);
$reportRecords = $db->selectObjectsBySql($sql . $sqlwhere);
expSession::set('order_export_values', $reportRecords);
//eDebug(expSession::get('order_export_values'));
//$where = 1;//$this->aggregateWhereClause();
//$order = 'id';
$limit = empty($this->config['limit']) ? 350 : $this->config['limit'];
//$prod = new product();
// $order = new order();
//$items = $prod->find('all', 1, 'id DESC',25);
//$items = $order->find('all', 1, 'id DESC',25);
//$res = $mod->find('all',$sql,'id',25);
//eDebug($items);
//eDebug($sql . $sqlwhere);
$page = new expPaginator(array('count_sql' => $count_sql, 'sql' => $sql . $sqlwhere, 'limit' => $limit, 'order' => 'o.invoice_id', 'dir' => 'DESC', 'controller' => $this->baseclassname, 'action' => $this->params['action'], 'columns' => array('actupon' => true, 'Order #' => 'invoice_id|controller=order,action=show,showby=id', 'Purchased Date' => 'purchased_date', 'First' => 'bfirst', 'Last' => 'blast', 'Total' => 'grand_total', 'Status Changed Date' => 'status_changed_date', 'Order Type' => 'order_type', 'Status' => 'status_title')));
//strftime("%a %d-%m-%Y", get_first_day(3, 1, 2007)); Thursday, 1 April 2010
//$d_month_previous = date('n', mktime(0,0,0,(strftime("%m")-1),1,strftime("%Y")));
$action_items = array('print_orders' => 'Print', 'export_odbc' => 'Export ODBC File', 'export_status_report' => 'Export Status Report', 'export_inventory' => 'Export Inventory File', 'export_user_input_report' => 'Export User Input File', 'export_order_items' => 'Export Order Items File', 'show_payment_summary' => 'Show Payment & Tax Summary');
assign_to_template(array('page' => $page, 'action_items' => $action_items));
}
