public function ProductStatsByNumSoldGrid()
{
$GLOBALS['OrderGrid'] = "";
if (isset($_GET['From']) && isset($_GET['To'])) {
$from_stamp = (int) $_GET['From'];
$to_stamp = (int) $_GET['To'];
// How many records per page?
if (isset($_GET['Show'])) {
$per_page = (int) $_GET['Show'];
} else {
$per_page = 20;
}
$GLOBALS['ProductsPerPage'] = $per_page;
$GLOBALS["IsShowPerPage" . $per_page] = 'selected="selected"';
// Should we limit the records returned?
if (isset($_GET['Page'])) {
$page = (int) $_GET['Page'];
} else {
$page = 1;
}
$GLOBALS['ProductsByNumSoldCurrentPage'] = $page;
// Workout the start and end records
$start = $per_page * $page - $per_page;
$end = $start + ($per_page - 1);
// Only fetch products this user can actually see
$vendorRestriction = $this->GetVendorRestriction();
$vendorSql = '';
if ($vendorRestriction !== false) {
$vendorSql = " AND prodvendorid='" . $GLOBALS['ISC_CLASS_DB']->Quote($vendorRestriction) . "'";
}
// How many products are there in total?
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOUNT(*) AS num\n\t\t\t\tFROM\n\t\t\t\t\t[|PREFIX|]order_products\n\t\t\t\t\tINNER JOIN [|PREFIX|]orders ON orderorderid = orderid\n\t\t\t\t\tLEFT JOIN [|PREFIX|]products ON ordprodid = productid\n\t\t\t\tWHERE\n\t\t\t\t\tordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ")\n\t\t\t\t\tAND ordprodtype != 'giftcertificate'\n\t\t\t\t\tAND ordprodid != 0\n\t\t\t\t\tAND orddate >= '" . $from_stamp . "'\n\t\t\t\t\tAND orddate <= '" . $to_stamp . "'" . $vendorSql;
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$row = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
$total_products = $row['num'];
if ($total_products > 0) {
// Workout the paging
$num_pages = ceil($total_products / $per_page);
$paging = sprintf(GetLang('PageXOfX'), $page, $num_pages);
$paging .= " ";
// Is there more than one page? If so show the « to jump back to page 1
if ($num_pages > 1) {
$paging .= "<a href='javascript:void(0)' onclick='ChangeProductsByNumSoldPage(1)'>«</a> | ";
} else {
$paging .= "« | ";
}
// Are we on page 2 or above?
if ($page > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeProductsByNumSoldPage(%d)'>%s</a> | ", $page - 1, GetLang('Prev'));
} else {
$paging .= sprintf("%s | ", GetLang('Prev'));
}
for ($i = 1; $i <= $num_pages; $i++) {
// Only output paging -5 and +5 pages from the page we're on
if ($i >= $page - 6 && $i <= $page + 5) {
if ($page == $i) {
$paging .= sprintf("<strong>%d</strong> | ", $i);
} else {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeProductsByNumSoldPage(%d)'>%d</a> | ", $i, $i);
}
}
}
// Are we on page 2 or above?
if ($page < $num_pages) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeProductsByNumSoldPage(%d)'>%s</a> | ", $page + 1, GetLang('Next'));
} else {
$paging .= sprintf("%s | ", GetLang('Next'));
}
// Is there more than one page? If so show the » to go to the last page
if ($num_pages > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeProductsByNumSoldPage(%d)'>»</a> | ", $num_pages);
} else {
$paging .= "» | ";
}
$paging = rtrim($paging, ' |');
$GLOBALS['Paging'] = $paging;
// Should we set focus to the grid?
if (isset($_GET['FromLink']) && $_GET['FromLink'] == "true") {
$GLOBALS['JumpToOrdersByItemsSoldGrid'] = "<script type=\"text/javascript\">document.location.href='#ordersByItemsSoldAnchor';</script>";
}
if (isset($_GET['SortOrder']) && $_GET['SortOrder'] == "asc") {
$sortOrder = 'asc';
} else {
$sortOrder = 'desc';
}
$sortFields = array('ordprodid', 'ordprodsku', 'ordprodname', 'revenue', 'numitemssold', 'totalprofit');
if (isset($_GET['SortBy']) && in_array($_GET['SortBy'], $sortFields)) {
$sortField = $_GET['SortBy'];
SaveDefaultSortField("ProductStatsBySold", $_REQUEST['SortBy'], $sortOrder);
} else {
list($sortField, $sortOrder) = GetDefaultSortField("ProductStatsBySold", "numitemssold", $sortOrder);
}
$sortLinks = array("ProductId" => "ordprodid", "Code" => "ordprodsku", "Name" => "ordprodname", "UnitsSold" => "numitemssold", "Revenue" => "revenue", "Profit" => "totalprofit");
BuildAdminSortingLinks($sortLinks, "javascript:SortProductsByNumSold('%%SORTFIELD%%', '%%SORTORDER%%');", $sortField, $sortOrder);
// Fetch the orders for this page
$query = "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tordprodid,\n\t\t\t\t\t\tordprodsku,\n\t\t\t\t\t\tordprodname,\n\t\t\t\t\t\tSUM(ordprodcost * ordprodqty) AS revenue,\n\t\t\t\t\t\tSUM(ordprodqty) as numitemssold,\n\t\t\t\t\t\tIF(ordprodcostprice > '0', SUM((ordprodcost - ordprodcostprice) * ordprodqty), 0) AS totalprofit,\n\t\t\t\t\t\tproductid\n\t\t\t\t\tFROM\n\t\t\t\t\t\t[|PREFIX|]order_products op\n\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON op.orderorderid = o.orderid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]products p ON p.productid = op.ordprodid\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ")\n\t\t\t\t\t\tAND ordprodtype != 'giftcertificate'\n\t\t\t\t\t\tAND orddate >= '" . $from_stamp . "'\n\t\t\t\t\t\tAND orddate <= '" . $to_stamp . "'\n\t\t\t\t\t\tAND ordprodid != 0 " . $vendorSql . "\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tordprodid\n\t\t\t\t\tORDER BY " . $sortField . " " . $sortOrder;
// Add the Limit
$query .= $GLOBALS['ISC_CLASS_DB']->AddLimit($start, $per_page);
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
if ($GLOBALS['ISC_CLASS_DB']->CountResult($result) > 0) {
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
if ($row['totalprofit'] > 0) {
$total_profit = sprintf("%s", FormatPrice($row['totalprofit']));
} else {
$total_profit = GetLang('NA');
}
$sku = GetLang('NA');
if ($row['ordprodsku']) {
$sku = isc_html_escape($row['ordprodsku']);
}
$prodlink = $row['ordprodname'];
if (!is_null($row['productid'])) {
$prodlink = "<a href='" . ProdLink($row['ordprodname']) . "' target='_blank'>" . isc_html_escape($row['ordprodname']) . "</a>";
}
$GLOBALS['OrderGrid'] .= sprintf("\n\t\t\t\t\t\t\t<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">\n\t\t\t\t\t\t\t\t<td nowrap height=\"22\" class=\"" . $GLOBALS['SortedFieldProductIdClass'] . "\">\n\t\t\t\t\t\t\t\t\t%d\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldCodeClass'] . "\">\n\t\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldNameClass'] . "\">\n\t\t\t\t\t\t\t\t\t%s</a>\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldUnitsSoldClass'] . "\">\n\t\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldRevenueClass'] . "\">\n\t\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldProfitClass'] . "\">\n\t\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t", $row['ordprodid'], $sku, $prodlink, (int) $row['numitemssold'], FormatPrice($row['revenue']), $total_profit);
}
}
} else {
$GLOBALS['OrderGrid'] .= sprintf("\n\t\t\t\t\t<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">\n\t\t\t\t\t\t<td nowrap height=\"22\" colspan=\"7\">\n\t\t\t\t\t\t\t<em>%s</em>\n\t\t\t\t\t\t</td>\n\t\t\t\t\t</tr>\n\t\t\t\t", GetLang('StatsNoOrdersForDate'));
}
$GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate("stats.products.bynumsoldgrid");
$GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate();
}
}
public function CustomerStatsByRevenueGrid()
{
$GLOBALS['CustomerGrid'] = "";
if (isset($_GET['From']) && isset($_GET['To'])) {
$from_stamp = (int) $_GET['From'];
$to_stamp = (int) $_GET['To'];
// How many records per page?
if (isset($_GET['Show'])) {
$per_page = (int) $_GET['Show'];
} else {
$per_page = 20;
}
$GLOBALS['CustomersPerPage'] = $per_page;
$GLOBALS["IsShowPerPage" . $per_page] = 'selected="selected"';
// Should we limit the records returned?
if (isset($_GET['Page'])) {
$page = (int) $_GET['Page'];
} else {
$page = 1;
}
$GLOBALS['RevenueByCustomersCurrentPage'] = $page;
// Workout the start and end records
$start = $per_page * $page - $per_page;
$end = $start + ($per_page - 1);
// How many customers with orders between this period are there in total?
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOUNT(*) AS num\n\t\t\t\tFROM\n\t\t\t\t\t[|PREFIX|]orders\n\t\t\t\t\tLEFT JOIN [|PREFIX|]customers ON ordcustid = customerid\n\t\t\t\tWHERE\n\t\t\t\t\tordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND\n\t\t\t\t\torddate >= '" . $from_stamp . "' AND\n\t\t\t\t\torddate <= '" . $to_stamp . "'\n\t\t\t\tGROUP BY\n\t\t\t\t\tordcustid\n\t\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$row = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
$total_customers = $row['num'];
// Workout the paging
$num_pages = ceil($total_customers / $per_page);
$paging = sprintf(GetLang('PageXOfX'), $page, $num_pages);
$paging .= " ";
// Is there more than one page? If so show the « to jump back to page 1
if ($num_pages > 1) {
$paging .= "<a href='javascript:void(0)' onclick='ChangeRevenuePerCustomerPage(1)'>«</a> | ";
} else {
$paging .= "« | ";
}
// Are we on page 2 or above?
if ($page > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeRevenuePerCustomerPage(%d)'>%s</a> | ", $page - 1, GetLang('Prev'));
} else {
$paging .= sprintf("%s | ", GetLang('Prev'));
}
for ($i = 1; $i <= $num_pages; $i++) {
// Only output paging -5 and +5 pages from the page we're on
if ($i >= $page - 6 && $i <= $page + 5) {
if ($page == $i) {
$paging .= sprintf("<strong>%d</strong> | ", $i);
} else {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeRevenuePerCustomerPage(%d)'>%d</a> | ", $i, $i);
}
}
}
// Are we on page 2 or above?
if ($page < $num_pages) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeRevenuePerCustomerPage(%d)'>%s</a> | ", $page + 1, GetLang('Next'));
} else {
$paging .= sprintf("%s | ", GetLang('Next'));
}
// Is there more than one page? If so show the » to go to the last page
if ($num_pages > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeRevenuePerCustomerPage(%d)'>»</a> | ", $num_pages);
} else {
$paging .= "» | ";
}
$paging = rtrim($paging, ' |');
$GLOBALS['Paging'] = $paging;
// Should we set focus to the grid?
if (isset($_GET['FromLink']) && $_GET['FromLink'] == "true") {
$GLOBALS['JumpToOrdersByItemsSoldGrid'] = "<script type=\"text/javascript\">document.location.href='#revenuePerCustomerAnchor';</script>";
}
if (isset($_GET['SortOrder']) && $_GET['SortOrder'] == "asc") {
$sortOrder = 'asc';
} else {
$sortOrder = 'desc';
}
$sortFields = array('customerid', 'name', 'custconemail', 'custdatejoined', 'numorders', 'revenue');
if (isset($_GET['SortBy']) && in_array($_GET['SortBy'], $sortFields)) {
$sortField = $_GET['SortBy'];
SaveDefaultSortField("CustomerStatsByRevenue", $_REQUEST['SortBy'], $sortOrder);
} else {
list($sortField, $sortOrder) = GetDefaultSortField("CustomerStatsByRevenue", "revenue", $sortOrder);
}
$sortLinks = array("Cust" => "name", "Email" => "custconemail", "Date" => "custdatejoined", "NumOrders" => "numorders", "AmountSpent" => "revenue");
BuildAdminSortingLinks($sortLinks, "javascript:SortRevenuePerCustomer('%%SORTFIELD%%', '%%SORTORDER%%');", $sortField, $sortOrder);
// Fetch the actual results for this page
$query = sprintf("\n\t\t\t\tSELECT\n\t\t\t\t\tcustomerid,\n\t\t\t\t\tCONCAT(custconfirstname, ' ', custconlastname) AS name,\n\t\t\t\t\tcustconemail,\n\t\t\t\t\tCONCAT(ordbillfirstname, ' ', ordbilllastname) AS billname,\n\t\t\t\t\tordbillemail,\n\t\t\t\t\tcustdatejoined,\n\t\t\t\t\tCOUNT(orderid) AS numorders,\n\t\t\t\t\tSUM(ordtotalamount) AS revenue\n\t\t\t\tFROM\n\t\t\t\t\t[|PREFIX|]orders\n\t\t\t\t\tLEFT JOIN [|PREFIX|]customers ON ordcustid = customerid\n\t\t\t\tWHERE\n\t\t\t\t\tordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND\n\t\t\t\t\torddate >= '%d' AND\n\t\t\t\t\torddate <= '%d'\n\t\t\t\tGROUP BY\n\t\t\t\t\tordcustid\n\t\t\t\tORDER BY\n\t\t\t\t\t%s %s", $from_stamp, $to_stamp, $sortField, $sortOrder);
// Add the Limit
$query .= $GLOBALS['ISC_CLASS_DB']->AddLimit($start, $per_page);
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
if ($GLOBALS['ISC_CLASS_DB']->CountResult($result) > 0) {
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
if (!is_null($row['customerid'])) {
$customerLink = "<a href=\"index.php?ToDo=viewCustomers&searchQuery=" . (int) $row['customerid'] . "\">" . isc_html_escape($row['name']) . "</a>";
$email = $row['custconemail'];
} else {
$customerLink = isc_html_escape($row['billname']);
$email = $row['ordbillemail'];
}
$GLOBALS['CustomerGrid'] .= sprintf("\n\t\t\t\t\t\t<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">\n\t\t\t\t\t\t\t<td nowrap height=\"22\" class=\"" . $GLOBALS['SortedFieldCustClass'] . "\">\n\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldEmailClass'] . "\">\n\t\t\t\t\t\t\t\t<a href=\"mailto:%s\">%s</a>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td nowrap class=\"" . $GLOBALS['SortedFieldDateClass'] . "\">\n\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td nowrap align='right' class=\"" . $GLOBALS['SortedFieldNumOrdersClass'] . "\">\n\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td nowrap align='right' class=\"" . $GLOBALS['SortedFieldAmountSpentClass'] . "\">\n\t\t\t\t\t\t\t\t%s\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\n\t\t\t\t\t", $customerLink, isc_html_escape($email), isc_html_escape($email), isc_date(GetConfig('DisplayDateFormat'), $row['custdatejoined']), $row['numorders'], FormatPrice($row['revenue']));
}
} else {
$GLOBALS['HideStatsRows'] = "none";
$GLOBALS['CustomerGrid'] .= sprintf("\n\t\t\t\t\t\t<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">\n\t\t\t\t\t\t\t<td nowrap height=\"22\" colspan=\"6\">\n\t\t\t\t\t\t\t\t<em>%s</em>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t", GetLang('StatsNoCustomersForDate'));
}
$GLOBALS['ISC_CLASS_TEMPLATE']->SetTemplate("stats.customers.byrevenue");
$GLOBALS['ISC_CLASS_TEMPLATE']->ParseTemplate();
}
}
/**
* Generate the chart data for "Orders by Revenue"
*/
public function OrderStatsByRevenueData()
{
if (isset($_GET['from']) && is_numeric($_GET['from']) && isset($_GET['to']) && is_numeric($_GET['to'])) {
$from_stamp = (int) $_GET['from'];
$to_stamp = (int) $_GET['to'];
$xml = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
$xml .= "<pie>\n";
// Only fetch products this user can actually see
$vendorRestriction = $this->GetVendorRestriction();
$vendorSql = '';
if ($vendorRestriction !== false) {
$vendorSql = " AND ordvendorid='" . (int) $vendorRestriction . "'";
}
$query = "\n\t\t\t\tSELECT COUNT(orderid) AS num, MIN(ordtotalamount) AS mintotal, MAX(ordtotalamount) AS maxtotal\n\t\t\t\tFROM [|PREFIX|]orders\n\t\t\t\tWHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "'\n\t\t\t\t" . $vendorSql . "\n\t\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$row = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
$num_orders = $row['num'];
$order_list = array();
$mintotal = $row['mintotal'];
$maxtotal = $row['maxtotal'];
// If there aren't any orders then we'll show dummy data
if ($num_orders == 0) {
$mintotal = 1;
$maxtotal = 100;
}
// What's the difference between the min and max?
$diff = $maxtotal - $mintotal;
if ($diff <= 1000) {
$increments = 10;
} else {
if ($diff <= 10000) {
$increments = 100;
} else {
$increments = 1000;
}
}
for ($i = 0; $i < ceil($maxtotal); $i += $increments) {
$start = $i;
$end = $i + $increments - 1;
$order_list[sprintf("%s - %s", FormatPrice($start), FormatPrice($end))] = array("min" => $start, "max" => $end, "numorders" => 0);
}
// Now we'll get the total of all orders between the periods and save them into an array
$query = "\n\t\t\t\tSELECT ordtotalamount\n\t\t\t\tFROM [|PREFIX|]orders\n\t\t\t\tWHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "'\n\t\t\t\t" . $vendorSql . "\n\t\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
foreach ($order_list as $k => $v) {
if ($row['ordtotalamount'] >= $v['min'] && $row['ordtotalamount'] <= $v['max']) {
$order_list[$k]['numorders']++;
break;
}
}
}
foreach ($order_list as $k => $v) {
$xml .= sprintf("\t<slice title=\"%s\" pull_out=\"false\">%d</slice>\n", isc_html_escape($k), (int) $v['numorders']);
}
$xml .= "</pie>";
echo $xml;
}
}
/**
* Get the total revenue and number of completed orders (ordstatus=2 or 10) between two timestamps
*/
protected function _GetOrderValueForPeriod($FromStamp, $ToStamp)
{
$vendorRestriction = $this->GetVendorRestriction();
$vendorSql = '';
if ($vendorRestriction !== false) {
$vendorSql = " AND ordvendorid='" . (int) $vendorRestriction . "'";
}
$query = "\n\t\t\tSELECT count(orderid) AS num, SUM(ordtotalamount) AS total\n\t\t\tFROM [|PREFIX|]orders\n\t\t\tWHERE orddate >= '" . (int) $FromStamp . "' AND orddate < '" . (int) $ToStamp . "' AND ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") " . $vendorSql . "\n\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$row = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
$vals = array("total" => $row['total'], "count" => $row['num']);
//searching unique vistor based on the GMT time, because the unique visitors are saved in GMT time
$FromStamp = mktime(0, 0, 0, isc_date("m", $FromStamp), isc_date("d", $FromStamp), isc_date("y", $FromStamp));
$ToStamp = mktime(0, 0, 0, isc_date("m", $ToStamp), isc_date("d", $ToStamp), isc_date("y", $ToStamp));
// Workout the number of unique visitors for the period
$query = "\n\t\t\tSELECT SUM(numuniques) AS visitors\n\t\t\tFROM [|PREFIX|]unique_visitors\n\t\t\tWHERE datestamp >='" . (int) $FromStamp . "' AND datestamp <= '" . (int) $ToStamp . "'\n\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$row = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
$vals['uniques'] = $row['visitors'];
return $vals;
}
private function GetQueries(&$countQuery, &$mainQuery, $vendorSql, $sortField, $sortOrder, &$NameField)
{
/* To get the from and to date in timestamp -- Baskaran */
$from_stamp = '';
$to_stamp = '';
if (isset($_GET['From']) && isset($_GET['To'])) {
$from_stamp = (int) $_GET['From'];
$to_stamp = (int) $_GET['To'];
}
if ($vendorSql == "") {
$part_vendorSql = "";
$tmp_vendorSql = " where t4.itemid IS NULL";
} else {
$part_vendorSql = " AND " . $vendorSql . "";
$vendorSql = " WHERE " . $vendorSql . "";
$tmp_vendorSql = $vendorSql . " AND t4.itemid IS NULL";
}
/* Code Ends */
switch ($_REQUEST['showby']) {
case 'category':
if ($vendorSql == "") {
$WhereQuery = " WHERE rc.catparentid = 0 ";
$nonezero = "AND totalcount != '0' OR ordprodqty != '0'";
} else {
$WhereQuery = " AND rc.catparentid = 0 ";
$nonezero = "AND (totalcount != '0' OR ordprodqty != '0')";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT t1.itemid, t1.prodnumviews, IFNULL(t2.numsold,0) numsold, t1.avgrating, t1.commonnamefield, IF(t1.prodnumviews > 0, (t2.numsold / t1.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT pc.categoryid itemid, SUM(vc.totalcount) AS prodnumviews, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, pc.catname AS commonnamefield\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories pc ON pc.categoryid = c.catparentid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY pc.categoryid\n\t\t\t\t\t\t\t\t) t1 LEFT JOIN\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tSELECT pc.categoryid itemid, SUM(op.ordprodqty) numsold\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories pc ON pc.categoryid = c.catparentid\n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate'\n\t\t\t\t\t\t\t\t " . $part_vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY pc.categoryid\n\t\t\t\t\t\t\t\t) t2 ON t1.itemid = t2.itemid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tSELECT t3.itemid, t4.prodnumviews, IFNULL(t3.numsold,0) numsold, t3.avgrating, t3.commonnamefield, IF(t4.prodnumviews > 0, (t3.numsold / t4.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT pc.categoryid itemid, SUM(op.ordprodqty) numsold, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, pc.catname AS commonnamefield\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories pc ON pc.categoryid = c.catparentid\n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t" . $part_vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY pc.categoryid\n\t\t\t\t\t\t\t\t) t3 LEFT JOIN\n\t\t\t\t\t\t\t\t(SELECT pc.categoryid itemid, SUM(vc.totalcount) AS prodnumviews\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories pc ON pc.categoryid = c.catparentid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY pc.categoryid\n\t\t\t\t\t\t\t\t) t4\n\t\t\t\t\t\t\t\tON t3.itemid = t4.itemid\n\t\t\t\t\t\t\t\t" . $tmp_vendorSql . " \n ORDER BY\n " . $sortField . " " . $sortOrder;
//echo $mainQuery."<br>";
/* $countQuery = "SELECT COUNT(DISTINCT rc.categoryid) AS num FROM
[|PREFIX|]products p
LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid
INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )
LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid )
LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid)) AND rc.catparentid = 0
" . $vendorSql . "
" . $WhereQuery . " AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";*/
//$NameField = 'catname';
//LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid))
break;
case 'subcategory':
if ($vendorSql == "") {
$cnt = "WHERE pvc.countid != '0' ";
$nonezero = "WHERE totalcount != '0' OR ordprodqty != '0'";
} else {
$cnt = "AND pvc.countid != '0' ";
$nonezero = "AND (totalcount != '0' OR ordprodqty != '0')";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT t1.itemid, t1.prodnumviews, IFNULL(t2.numsold,0) numsold, t1.avgrating, t1.commonnamefield, IF(t1.prodnumviews > 0, (t2.numsold / t1.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT c.categoryid itemid, SUM(vc.totalcount) AS prodnumviews, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, c.catname AS commonnamefield\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY c.categoryid\n\t\t\t\t\t\t\t\t) t1 LEFT JOIN\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tSELECT c.categoryid itemid, SUM(op.ordprodqty) numsold\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t" . $part_vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY c.categoryid\n\t\t\t\t\t\t\t\t) t2 ON t1.itemid = t2.itemid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tSELECT t3.itemid, t4.prodnumviews, IFNULL(t3.numsold,0) numsold, t3.avgrating, t3.commonnamefield, IF(t4.prodnumviews > 0, (t3.numsold / t4.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\tSELECT c.categoryid itemid, SUM(op.ordprodqty) numsold, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, c.catname AS commonnamefield\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t" . $part_vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY c.categoryid\n\t\t\t\t\t\t\t\t) t3 LEFT JOIN\n\t\t\t\t\t\t\t\t(SELECT c.categoryid itemid, SUM(vc.totalcount) AS prodnumviews\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]categories c ON c.categoryid = ca.categoryid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tGROUP BY c.categoryid\n\t\t\t\t\t\t\t\t) t4\n\t\t\t\t\t\t\t\tON t3.itemid = t4.itemid\n\t\t\t\t\t\t\t\t" . $tmp_vendorSql . " \n ORDER BY\n " . $sortField . " " . $sortOrder;
//echo $mainQuery."<br />";
/* $countQuery = "
SELECT COUNT(DISTINCT c.categoryid) AS num
FROM [|PREFIX|]categories c
INNER JOIN [|PREFIX|]categoryassociations ca ON ( c.categoryid = ca.categoryid )
INNER JOIN [|PREFIX|]products p ON ( ca.productid = p.productid )
LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid
AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'
" . $vendorSql . " ".$cnt." "; */
// echo $mainQuery;
//$NameField = 'catname';
break;
case 'brand':
if ($vendorSql == "") {
$cnt = "WHERE pvc.countid != '0' ";
$nonezero = "WHERE totalcount != '0' OR ordprodqty != '0'";
} else {
$cnt = "AND pvc.countid != '0' ";
// $nonezero = "AND totalcount != '0' OR ordprodqty != '0'";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT t1.itemid, t1.prodnumviews, IFNULL(t2.numsold,0) numsold, t1.avgrating, t1.commonnamefield, IF(t1.prodnumviews > 0, (t2.numsold / t1.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\t\tSELECT b.brandid itemid, SUM(vc.totalcount) AS prodnumviews, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, b.brandname AS commonnamefield\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n\t\t\t\t\t\t\t\t\tGROUP BY b.brandid\n\t\t\t\t\t\t\t\t) t1 LEFT JOIN\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\tSELECT b.brandid itemid, SUM(op.ordprodqty) numsold\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n\t\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t\tGROUP BY b.brandid\n\t\t\t\t\t\t\t\t) t2 ON t1.itemid = t2.itemid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tSELECT t3.itemid, t4.prodnumviews, IFNULL(t3.numsold,0) numsold, t3.avgrating, t3.commonnamefield, IF(t4.prodnumviews > 0, (t3.numsold / t4.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\t\tSELECT b.brandid itemid, SUM(op.ordprodqty) numsold, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid, b.brandname AS commonnamefield\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n\t\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t\tGROUP BY b.brandid\n\t\t\t\t\t\t\t\t) t3 LEFT JOIN\n\t\t\t\t\t\t\t\t(SELECT b.brandid itemid, SUM(vc.totalcount) AS prodnumviews\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n\t\t\t\t\t\t\t\tGROUP BY b.brandid\n\t\t\t\t\t\t\t\t) t4\n\t\t\t\t\t\t\t\tON t3.itemid = t4.itemid\n\t\t\t\t\t\t\t\t" . $tmp_vendorSql . " \n ORDER BY\n " . $sortField . " " . $sortOrder;
//echo $mainQuery."<br />";
/* $countQuery = "SELECT COUNT(DISTINCT b.brandid) AS num
FROM [|PREFIX|]brands b
INNER JOIN [|PREFIX|]products p ON b.brandid = p.prodbrandid
LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid
AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'
" . $vendorSql . " ".$cnt." "; */
//$NameField = 'brandname';
break;
case 'series':
if ($vendorSql == "") {
$WhereQuery1 = " WHERE bs.seriesid != 0 AND (totalcount != '0' OR ordprodqty != '0')";
$WhereQuery2 = " WHERE p.brandseriesid = 0 AND (totalcount != '0' OR ordprodqty != '0')";
$nonezero = " WHERE totalcount != '0'";
$cnt = "WHERE pvc.countid != '0' ";
} else {
$WhereQuery1 = " AND bs.seriesid != 0 AND (totalcount != '0' OR ordprodqty != '0') ";
$WhereQuery2 = " AND p.brandseriesid = 0 AND totalcount != '0' ";
$nonezero = " AND totalcount != '0'";
$cnt = "AND pvc.countid != '0' ";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n \t\tSELECT p1.itemid, p1.prodnumviews, p1.numsold, p1.avgrating, CONCAT(b.brandname, ' - ', bs.seriesname) AS commonnamefield, p1.avgrating, IF(p1.prodnumviews > 0, (p1.numsold / p1.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\tFROM(\n\t\t\t\t\t\t\t\tSELECT t1.itemid, t1.prodnumviews, IFNULL(t2.numsold,0) numsold, t1.avgrating, IF(t1.prodnumviews > 0, (t2.numsold / t1.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\t\tSELECT p.brandseriesid itemid, SUM(vc.totalcount) AS prodnumviews, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\t\tWHERE p.brandseriesid>0\n\t\t\t\t\t\t\t\t\tGROUP BY p.brandseriesid\n\t\t\t\t\t\t\t\t) t1 LEFT JOIN\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\tSELECT p.brandseriesid itemid, SUM(op.ordprodqty) numsold\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t\tAND p.brandseriesid>0\n\t\t\t\t\t\t\t\t\tGROUP BY p.brandseriesid\n\t\t\t\t\t\t\t\t) t2 ON t1.itemid = t2.itemid\n\t\t\t\t\t\t\t\t" . $vendorSql . " \n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tSELECT t3.itemid, IFNULL(t4.prodnumviews,0) prodnumviews, t3.numsold, IFNULL(t3.avgrating,0) avgrating, IF(t4.prodnumviews > 0, (t3.numsold / t4.prodnumviews), 0) AS unitssoldpercent\n\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\t\tSELECT p.brandseriesid itemid, SUM(op.ordprodqty) numsold, IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating, p.prodvendorid\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op\n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]products p ON op.ordprodid = p.productid\n\t\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' \n\t\t\t\t\t\t\t\t\tAND p.brandseriesid>0\n\t\t\t\t\t\t\t\t\tGROUP BY p.brandseriesid\n\t\t\t\t\t\t\t\t) t3 LEFT JOIN\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\tSELECT p.brandseriesid itemid, SUM(vc.totalcount) AS prodnumviews\n\t\t\t\t\t\t\t\t\tFROM [|PREFIX|]products p INNER JOIN ( \n\t\t\t\t\t\t\t\t\t SELECT COUNT(countid) AS totalcount, productid \n\t\t\t\t\t\t\t\t\t FROM [|PREFIX|]product_view_count pvc \n\t\t\t\t\t\t\t\t\t WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' GROUP BY pvc.productid \n\t\t\t\t\t\t\t\t\t) AS vc on vc.productid = p.productid \n\t\t\t\t\t\t\t\t\tWHERE p.brandseriesid>0\n\t\t\t\t\t\t\t\t\tGROUP BY p.brandseriesid\n\t\t\t\t\t\t\t\t) t4 ON t3.itemid = t4.itemid\n\t\t\t\t\t\t\t\t" . $tmp_vendorSql . " \n\t\t\t\t\t\t\t) p1 INNER JOIN [|PREFIX|]brand_series bs ON p1.itemid = bs.seriesid\n\t\t\t\t\t\t\tINNER JOIN [|PREFIX|]brands b ON bs.brandid = b.brandid\n ORDER BY\n\t\t\t\t\t\t\t" . $sortField . " " . $sortOrder;
//echo $mainQuery."<br />";
/*$countQuery = "SELECT COUNT(DISTINCT bs.seriesid) AS num
FROM [|PREFIX|]brand_series bs
INNER JOIN isc_products p ON bs.seriesid = p.brandseriesid
LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid
AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'
" . $vendorSql . " ".$cnt." "; */
//$NameField = 'seriescomname';
/*
echo "<noscript>";
echo $mainQuery."<br />";
echo $countQuery."<br />";
echo "</noscript>";
*/
break;
default:
// Fetch the orders for this page
if ($vendorSql == "") {
$nonezero = " WHERE pvc.countid != '0' OR ordprodqty != '0' ";
} else {
$nonezero = " AND (pvc.countid != '0' OR ordprodqty != '0') ";
}
$AndQuery = " AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT \n\t\t\t\t\t\tp.productid itemid, p.prodcode, p.prodname commonnamefield, p.prodprice as price, b.brandname AS brand, bs.seriesname as series, po.totalcount AS prodnumviews, po.ordprodqty AS numsold,\n\t\t\t\t\t\tIF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating,\n\t\t\t\t\t\tIF(po.totalcount > 0, (po.ordprodqty / po.totalcount), 0) AS unitssoldpercent,\n\t\t\t\t\t\tIF(c1.catparentid > 0, c2.catname, c1.catname) AS category,\n\t\t\t\t\t\tIF(c1.catparentid > 0, c1.catname, '') AS subcategory\n\t\t\t\t\t\tFROM [|PREFIX|]products p \n\t\t\t\t\t\tINNER JOIN(\n\t\t\t\t\t\t\tSELECT pvc.productid, ifnull(pvc.totalcount, 0) totalcount, ifnull(o.ordprodqty, 0) ordprodqty\n\t\t\t\t\t\t\tFROM(\n\t\t\t\t\t\t\t\tSELECT productid, count(0) totalcount\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]product_view_count \n\t\t\t\t\t\t\t\tWHERE viewdate >= '" . $from_stamp . "' AND viewdate <= '" . $to_stamp . "'\n\t\t\t\t\t\t\t\tAND productid>0\n\t\t\t\t\t\t\t\tGROUP BY productid\n\t\t\t\t\t\t\t\tHAVING count(0) > 0\n\t\t\t\t\t\t\t) pvc\n\t\t\t\t\t\t\tLEFT JOIN(\n\t\t\t\t\t\t\t\tSELECT op.ordprodid as productid, SUM(ordprodqty) ordprodqty \n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op \n\t\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' and op.ordprodid>0\n\t\t\t\t\t\t\t\tGROUP BY op.ordprodid \n\t\t\t\t\t\t\t\tHAVING SUM(ordprodqty) > 0\n\t\t\t\t\t\t\t) o ON pvc.productid = o.productid\n\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\tSELECT o.productid, ifnull(pvc.totalcount, 0) totalcount, ifnull(o.ordprodqty, 0) ordprodqty\n\t\t\t\t\t\t\tFROM(\n\t\t\t\t\t\t\t\tSELECT productid, count(0) totalcount\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]product_view_count \n\t\t\t\t\t\t\t\tWHERE viewdate >= '" . $from_stamp . "' AND viewdate <= '" . $to_stamp . "'\n\t\t\t\t\t\t\t\tAND productid>0\n\t\t\t\t\t\t\t\tGROUP BY productid\n\t\t\t\t\t\t\t\tHAVING count(0) > 0\n\t\t\t\t\t\t\t) pvc\n\t\t\t\t\t\t\tRIGHT JOIN(\n\t\t\t\t\t\t\t\tSELECT op.ordprodid as productid, SUM(ordprodqty) ordprodqty \n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op \n\t\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' and op.ordprodid>0\n\t\t\t\t\t\t\t\tGROUP BY op.ordprodid \n\t\t\t\t\t\t\t\tHAVING SUM(ordprodqty) > 0\n\t\t\t\t\t\t\t) o ON pvc.productid = o.productid\n\t\t\t\t\t\t\tWHERE pvc.productid IS NULL\n\t\t\t\t\t\t) po ON p.productid = po.productid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]brand_series bs ON p.brandseriesid = bs.seriesid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categoryassociations ca ON p.productid = ca.productid\n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categories c1 ON ca.categoryid = c1.categoryid \n\t\t\t\t\t\tLEFT JOIN [|PREFIX|]categories c2 ON c1.catparentid = c2.categoryid \n " . $vendorSql . " \n ORDER BY\n " . $sortField . " " . $sortOrder;
//echo $mainQuery."<br>";
$countQuery = "\n SELECT 1 num\n\t\t\t\t\t\tFROM [|PREFIX|]products p \n\t\t\t\t\t\tINNER JOIN(\n\t\t\t\t\t\t\tSELECT pvc.productid, ifnull(pvc.totalcount, 0) totalcount, ifnull(o.ordprodqty, 0) ordprodqty\n\t\t\t\t\t\t\tFROM(\n\t\t\t\t\t\t\t\tSELECT productid, count(0) totalcount\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]product_view_count \n\t\t\t\t\t\t\t\tWHERE viewdate >= '" . $from_stamp . "' AND viewdate <= '" . $to_stamp . "'\n\t\t\t\t\t\t\t\tAND productid>0\n\t\t\t\t\t\t\t\tGROUP BY productid\n\t\t\t\t\t\t\t\tHAVING count(0) > 0\n\t\t\t\t\t\t\t) pvc\n\t\t\t\t\t\t\tLEFT JOIN(\n\t\t\t\t\t\t\t\tSELECT op.ordprodid as productid, SUM(ordprodqty) ordprodqty \n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op \n\t\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' and op.ordprodid>0\n\t\t\t\t\t\t\t\tGROUP BY op.ordprodid \n\t\t\t\t\t\t\t\tHAVING SUM(ordprodqty) > 0\n\t\t\t\t\t\t\t) o ON pvc.productid = o.productid\n\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\tSELECT o.productid, ifnull(pvc.totalcount, 0) totalcount, ifnull(o.ordprodqty, 0) ordprodqty\n\t\t\t\t\t\t\tFROM(\n\t\t\t\t\t\t\t\tSELECT productid, count(0) totalcount\n\t\t\t\t\t\t\t\tFROM [|PREFIX|]product_view_count \n\t\t\t\t\t\t\t\tWHERE viewdate >= '" . $from_stamp . "' AND viewdate <= '" . $to_stamp . "'\n\t\t\t\t\t\t\t\tAND productid>0\n\t\t\t\t\t\t\t\tGROUP BY productid\n\t\t\t\t\t\t\t\tHAVING count(0) > 0\n\t\t\t\t\t\t\t) pvc\n\t\t\t\t\t\t\tRIGHT JOIN(\n\t\t\t\t\t\t\t\tSELECT op.ordprodid as productid, SUM(ordprodqty) ordprodqty \n\t\t\t\t\t\t\t\tFROM [|PREFIX|]order_products op \n\t\t\t\t\t\t\t\tLEFT JOIN [|PREFIX|]orders o ON o.orderid = op.orderorderid \n\t\t\t\t\t\t\t\tWHERE o.ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND o.orddate >= '" . $from_stamp . "' AND o.orddate <= '" . $to_stamp . "' AND op.ordprodtype != 'giftcertificate' and op.ordprodid>0\n\t\t\t\t\t\t\t\tGROUP BY op.ordprodid \n\t\t\t\t\t\t\t\tHAVING SUM(ordprodqty) > 0\n\t\t\t\t\t\t\t) o ON pvc.productid = o.productid\n\t\t\t\t\t\t\tWHERE pvc.productid IS NULL\n\t\t\t\t\t\t) po ON p.productid = po.productid\n " . $vendorSql;
//echo $countQuery."<br>";
}
/*
//Fetch the orders for this page
$mainQuery = "
SELECT
p.productid,
p.prodcode,
p.prodname,
$newselect,
IF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating
FROM
[|PREFIX|]products p
" . $leftJoin . "
" . $vendorSql . "
" . $groupBy . "
ORDER BY
" . $sortField . " " . $sortOrder;
*/
}
/**
* Generate the code to display a Google map containing the location of the store's
* top 100 customers during the selected date range.
*/
public function OverviewOrderLocationChart()
{
if (isset($_GET['from']) && isset($_GET['to'])) {
$from = (int) $_GET['from'];
$to = (int) $_GET['to'];
// Workout the top 100 customers for the selected date period
$address_list = "";
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tcustconcompany,\n\t\t\t\t\tcustconfirstname,\n\t\t\t\t\tcustconlastname,\n\t\t\t\t\tcustconemail,\n\t\t\t\t\tcustconphone,\n\t\t\t\t\tordbillstreet1,\n\t\t\t\t\tordbillstreet2,\n\t\t\t\t\tordbillsuburb,\n\t\t\t\t\tordbillstate,\n\t\t\t\t\tordbillzip,\n\t\t\t\t\tordbillcountry,\n\t\t\t\t\tcustomerid,\n\t\t\t\t\tCONCAT(ordbillstreet1, ' ', ordbillstreet2, ' ', ordbillsuburb, ' ', ordbillstate, ' ', ordbillzip, ' ', ordbillcountry) AS custaddress\n\t\t\t\tFROM\n\t\t\t\t\t[|PREFIX|]orders\n\t\t\t\t\tINNER JOIN [|PREFIX|]customers ON ordcustid = customerid\n\t\t\t\tWHERE\n\t\t\t\t\tordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND\n\t\t\t\t\torddate >= '" . $from . "' AND\n\t\t\t\t\torddate <= '" . $to . "'\n\t\t\t\tGROUP BY\n\t\t\t\t\tcustaddress\n\t\t\t";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$num_orders = $GLOBALS['ISC_CLASS_DB']->CountResult($result);
if ($num_orders > 0) {
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
$address_list .= "var customerDetails = {\n\t\t\t\t\t\tname: '" . addslashes($row['custconfirstname']) . " " . addslashes($row['custconlastname']) . "',\n\t\t\t\t\t\tid: '" . (int) $row['customerid'] . "',\n\t\t\t\t\t\tphone: '" . addslashes($row['custconphone']) . "',\n\t\t\t\t\t\taddress1: '" . addslashes($row['ordbillstreet1']) . "',\n\t\t\t\t\t\taddress2: '" . addslashes($row['ordbillstreet2']) . "',\n\t\t\t\t\t\tsuburb: '" . addslashes($row['ordbillsuburb']) . "',\n\t\t\t\t\t\tstate: '" . addslashes($row['ordbillstate']) . "',\n\t\t\t\t\t\tcountry: '" . addslashes($row['ordbillcountry']) . "',\n\t\t\t\t\t\tzip: '" . addslashes($row['ordbillzip']) . "'\n\t\t\t\t\t};\r\n";
$address_list .= "showAddress(customerDetails);";
}
$mapScript = "http://maps.google.com/maps?file=api&v=2&key=" . GetConfig('GoogleMapsAPIKey');
?>
<html>
<head>
<style>
* { font-family:Arial; font-size:11px; }
body { margin:0px; }
</style>
<link rel="stylesheet" type="text/css" href="styles/thickbox.css" />
<script src="<?php
echo $mapScript;
?>
"></script>
<script src="../javascript/jquery.js"></script>
<script src="../javascript/thickbox.js"></script>
<script type="text/javascript">
var map = null;
var geocoder = null;
function gmap_initialize() {
if(GBrowserIsCompatible()) {
map = new GMap2(document.getElementById("map_canvas"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.setCenter(new GLatLng(37.4419, -122.1419), 2);
geocoder = new GClientGeocoder();
}
}
function showAddress(info) {
if(geocoder) {
// Build the address to show
var address = info.address1+" "+info.address2+" "+info.suburb+" "+info.state+" "+info.zip+" "+info.country;
address = address.replace(/N\/A/i, '');
address = address.replace(/C\/O/i, '');
geocoder.getLatLng(
address,
function(point) {
if(!point) {
// If the whole address was not found, strip out the street etc
var address = info.suburb+" "+info.state+" "+info.zip+" "+info.country;
address = address.replace('/N\/A/i', '');
address = address.replace('/C\/O/i', '');
geocoder.getLatLng(
address,
function(point) {
if(point) {
DrawOverlay(info, point);
}
}
);
//alert(address + " not found");
}
else {
DrawOverlay(info, point);
}
}
);
}
}
function DrawOverlay(info, point) {
var infoWindow = "<div style='font-weight: bold; font-size: 11px;'>"+info.name+" (<a target='_parent' href='index.php?ToDo=viewCustomers&searchQuery="+info.id+"' style='color:blue'><?php
echo GetLang('ViewOrderHistory');
?>
</a>)</div><br />"+info.suburb+", "+info.state+"<br />"+info.country+" "+info.zip;
var marker = new GMarker(point);
map.addOverlay(marker);
GEvent.addListener(marker, "click", function() {
marker.openInfoWindowHtml(infoWindow);
});
}
function gDo() {
window.setTimeout("gBuild();", 1000);
}
function gBuild() {
gmap_initialize();
<?php
echo $address_list;
?>
}
</script>
</head>
<body onload="gDo()">
<div id="map_canvas" style="width: 99%; height: 99%; border:solid 1px #CCC"></div>
</body>
</html>
<?php
}
}
}
private function GetQueries(&$countQuery, &$mainQuery, $vendorSql, $sortField, $sortOrder, &$NameField)
{
switch ($_REQUEST['showby']) {
case 'category':
if ($vendorSql == "") {
$WhereQuery = " WHERE rc.catparentid = 0 ";
} else {
$WhereQuery = " AND rc.catparentid = 0 ";
}
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n rc.categoryid itemid,\n rc.catname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(p.prodnumviews) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(p.prodnumviews) > 0, SUM(ordprodqty) / SUM(p.prodnumviews), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid)) AND rc.catparentid = 0 \n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . "\n " . $WhereQuery . "\n GROUP BY rc.categoryid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT rc.categoryid) AS num FROM\n [|PREFIX|]products p\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid)) AND rc.catparentid = 0\n " . $vendorSql . "\n " . $WhereQuery . " ";
//$NameField = 'catname';
//LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid))
break;
case 'subcategory':
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n c.categoryid itemid,\n c.catname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(p.prodnumviews) AS prodnumviews,\n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(p.prodnumviews) > 0, SUM(ordprodqty) / SUM(p.prodnumviews), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . "\n GROUP BY c.categoryid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "\n SELECT COUNT(DISTINCT c.categoryid) AS num\n FROM isc_categories c\n INNER JOIN isc_categoryassociations ca ON ( c.categoryid = ca.categoryid ) \n INNER JOIN isc_products p ON ( ca.productid = p.productid ) \n " . $vendorSql . "";
//$NameField = 'catname';
break;
case 'brand':
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n b.brandid itemid, \n b.brandname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(p.prodnumviews) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(p.prodnumviews) > 0, (SUM(ordprodqty) / SUM(p.prodnumviews)), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p \n LEFT JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid\n " . $vendorSql . "\n GROUP BY b.brandid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT b.brandid) AS num\n FROM [|PREFIX|]brands b\n INNER JOIN isc_products p ON b.brandid = p.prodbrandid \n " . $vendorSql . "";
//$NameField = 'brandname';
break;
case 'series':
if ($vendorSql == "") {
$WhereQuery1 = " WHERE bs.seriesid != 0 ";
$WhereQuery2 = " WHERE p.brandseriesid = 0 ";
} else {
$WhereQuery1 = " AND bs.seriesid != 0 ";
$WhereQuery2 = " AND p.brandseriesid = 0 ";
}
$mainQuery = "\n SELECT * FROM \n (\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n bs.seriesid itemid, \n CONCAT(b.brandname, ' - ', bs.seriesname) AS commonnamefield,\n SUM(ordprodqty) AS numsold,\n SUM(p.prodnumviews) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(p.prodnumviews) > 0, SUM(ordprodqty)/SUM(p.prodnumviews), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p \n LEFT JOIN [|PREFIX|]brand_series bs ON p.brandseriesid = bs.seriesid\n LEFT JOIN [|PREFIX|]brands b ON bs.brandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . "\n " . $WhereQuery1 . "\n GROUP BY bs.seriesid \n UNION\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n p.prodbrandid itemid, \n b.brandname commonnamefield,\n SUM(ordprodqty) AS numsold,\n SUM(p.prodnumviews) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(p.prodnumviews) > 0, (SUM(ordprodqty) / SUM(p.prodnumviews)), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p \n LEFT JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . " \n " . $WhereQuery2 . "\n GROUP BY b.brandid\n ) AS combinedtable\n ORDER BY\n combinedtable." . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT bs.seriesid) AS num\n FROM [|PREFIX|]brand_series bs\n INNER JOIN isc_products p ON bs.seriesid = p.brandseriesid \n " . $vendorSql . "";
//$NameField = 'seriescomname';
/*
echo "<noscript>";
echo $mainQuery."<br />";
echo $countQuery."<br />";
echo "</noscript>";
*/
break;
default:
// Fetch the orders for this page
$mainQuery = "\n SELECT\n p.productid itemid,\n p.prodcode,\n p.prodname commonnamefield,\n ordprodqty AS numsold,\n p.prodnumviews,\n IF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating,\n IF(p.prodnumviews > 0, (ordprodqty / p.prodnumviews), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . " \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "\n SELECT\n COUNT(*) AS num\n FROM\n [|PREFIX|]products\n " . $vendorSql;
//$NameField = 'prodname';
}
/*
//Fetch the orders for this page
$mainQuery = "
SELECT
p.productid,
p.prodcode,
p.prodname,
$newselect,
IF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating
FROM
[|PREFIX|]products p
" . $leftJoin . "
" . $vendorSql . "
" . $groupBy . "
ORDER BY
" . $sortField . " " . $sortOrder;
*/
}
private function GetQueries(&$countQuery, &$mainQuery, $vendorSql, $sortField, $sortOrder, &$NameField)
{
/* To get the from and to date in timestamp -- Baskaran */
$from_stamp = '';
$to_stamp = '';
if (isset($_GET['From']) && isset($_GET['To'])) {
$from_stamp = (int) $_GET['From'];
$to_stamp = (int) $_GET['To'];
}
/* Code Ends */
switch ($_REQUEST['showby']) {
case 'category':
if ($vendorSql == "") {
$WhereQuery = " WHERE rc.catparentid = 0 ";
$nonezero = "AND totalcount != '0'";
} else {
$WhereQuery = " AND rc.catparentid = 0 ";
$nonezero = "AND totalcount != '0'";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n rc.categoryid itemid,\n rc.catname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(totalcount) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(totalcount) > 0, SUM(ordprodqty) / SUM(totalcount), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN \n (\n SELECT COUNT(countid) AS totalcount, productid FROM [|PREFIX|]product_view_count pvc " . $AndQuery . "\n GROUP BY pvc.productid\n ) AS vc on vc.productid = p.productid\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid)) AND rc.catparentid = 0 \n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . "\n " . $WhereQuery . " " . $nonezero . "\n GROUP BY rc.categoryid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT rc.categoryid) AS num FROM\n [|PREFIX|]products p\n LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid)) AND rc.catparentid = 0\n " . $vendorSql . "\n " . $WhereQuery . " AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
//$NameField = 'catname';
//LEFT JOIN [|PREFIX|]categories rc ON ((c.catparentid = rc.categoryid) || (c.catparentid=0 AND c.categoryid=rc.categoryid))
break;
case 'subcategory':
if ($vendorSql == "") {
$cnt = "WHERE pvc.countid != '0' ";
$nonezero = "WHERE totalcount != '0' ";
} else {
$cnt = "AND pvc.countid != '0' ";
$nonezero = "AND totalcount != '0' ";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n c.categoryid itemid,\n c.catname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(totalcount) AS prodnumviews,\n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(totalcount) > 0, SUM(ordprodqty) / SUM(totalcount), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN \n (\n SELECT COUNT(countid) AS totalcount, productid FROM [|PREFIX|]product_view_count pvc " . $AndQuery . "\n GROUP BY pvc.productid\n ) AS vc on vc.productid = p.productid\n INNER JOIN [|PREFIX|]categoryassociations ca ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]categories c ON ( c.categoryid = ca.categoryid ) \n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . " " . $nonezero . "\n GROUP BY c.categoryid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "\n SELECT COUNT(DISTINCT c.categoryid) AS num\n FROM isc_categories c\n INNER JOIN isc_categoryassociations ca ON ( c.categoryid = ca.categoryid ) \n INNER JOIN isc_products p ON ( ca.productid = p.productid )\n LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid\n AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' \n " . $vendorSql . " " . $cnt . " ";
//$NameField = 'catname';
break;
case 'brand':
if ($vendorSql == "") {
$cnt = "WHERE pvc.countid != '0' ";
$nonezero = "WHERE totalcount != '0' ";
} else {
$cnt = "AND pvc.countid != '0' ";
$nonezero = "AND totalcount != '0' ";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n b.brandid itemid, \n b.brandname commonnamefield, \n SUM(ordprodqty) AS numsold,\n SUM(totalcount) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(totalcount) > 0, (SUM(ordprodqty) / SUM(totalcount)), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p \n LEFT JOIN \n (\n SELECT COUNT(countid) AS totalcount, productid FROM [|PREFIX|]product_view_count pvc " . $AndQuery . "\n GROUP BY pvc.productid\n ) AS vc on vc.productid = p.productid\n LEFT JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM [|PREFIX|]order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid\n " . $vendorSql . " " . $nonezero . "\n GROUP BY b.brandid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT b.brandid) AS num\n FROM [|PREFIX|]brands b\n INNER JOIN [|PREFIX|]products p ON b.brandid = p.prodbrandid\n LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid\n AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' \n " . $vendorSql . " " . $cnt . " ";
//$NameField = 'brandname';
break;
case 'series':
if ($vendorSql == "") {
$WhereQuery1 = " WHERE bs.seriesid != 0 AND totalcount != '0' ";
$WhereQuery2 = " WHERE p.brandseriesid = 0 AND totalcount != '0' ";
$nonezero = " WHERE totalcount != '0'";
$cnt = "WHERE pvc.countid != '0' ";
} else {
$WhereQuery1 = " AND bs.seriesid != 0 AND totalcount != '0' ";
$WhereQuery2 = " AND p.brandseriesid = 0 AND totalcount != '0' ";
$nonezero = " AND totalcount != '0'";
$cnt = "AND pvc.countid != '0' ";
}
$AndQuery = " WHERE pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT * FROM \n (\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n bs.seriesid itemid, \n CONCAT(b.brandname, ' - ', bs.seriesname) AS commonnamefield,\n SUM(ordprodqty) AS numsold,\n SUM(totalcount) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(totalcount) > 0, SUM(ordprodqty)/SUM(totalcount), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN \n (\n SELECT COUNT(countid) AS totalcount, productid FROM [|PREFIX|]product_view_count pvc " . $AndQuery . "\n GROUP BY pvc.productid\n ) AS vc on vc.productid = p.productid\n LEFT JOIN [|PREFIX|]brand_series bs ON p.brandseriesid = bs.seriesid\n LEFT JOIN [|PREFIX|]brands b ON bs.brandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM isc_order_products\n LEFT JOIN [|PREFIX|]orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . " \n " . $WhereQuery1 . "\n GROUP BY bs.seriesid \n UNION\n SELECT\n p.productid,\n p.prodcode,\n p.prodname,\n p.prodbrandid itemid, \n b.brandname commonnamefield,\n SUM(ordprodqty) AS numsold, \n SUM(totalcount) AS prodnumviews, \n IF(SUM(p.prodnumratings) > 0, (SUM(p.prodratingtotal) / SUM(p.prodnumratings)), 0) AS avgrating,\n IF(SUM(totalcount) > 0, (SUM(ordprodqty) / SUM(totalcount)), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN \n (\n SELECT count(countid) AS totalcount, productid FROM [|PREFIX|]product_view_count pvc " . $AndQuery . "\n GROUP BY pvc.productid\n ) AS vc on vc.productid = p.productid\n LEFT JOIN [|PREFIX|]brands b ON p.prodbrandid = b.brandid\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM [|PREFIX|]order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid \n " . $vendorSql . " \n " . $WhereQuery2 . "\n GROUP BY b.brandid\n ) AS combinedtable\n ORDER BY\n combinedtable." . $sortField . " " . $sortOrder;
$countQuery = "SELECT COUNT(DISTINCT bs.seriesid) AS num\n FROM [|PREFIX|]brand_series bs\n INNER JOIN isc_products p ON bs.seriesid = p.brandseriesid\n LEFT JOIN [|PREFIX|]product_view_count pvc ON p.productid = pvc.productid\n AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "' \n " . $vendorSql . " " . $cnt . " ";
//$NameField = 'seriescomname';
break;
default:
// Fetch the orders for this page
if ($vendorSql == "") {
$nonezero = " WHERE pvc.countid != '0'";
} else {
$nonezero = " AND pvc.countid != '0'";
}
$AndQuery = " AND pvc.viewdate >= '" . $from_stamp . "' AND pvc.viewdate <= '" . $to_stamp . "'";
$mainQuery = "\n SELECT\n p.productid itemid,\n p.prodcode,\n p.prodname commonnamefield,\n ordprodqty AS numsold,\n count(countid) AS prodnumviews,\n IF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating,\n IF(count(countid) > 0, (ordprodqty / count(countid)), 0) AS unitssoldpercent\n FROM\n [|PREFIX|]products p\n LEFT JOIN [|PREFIX|]product_view_count pvc on pvc.productid = p.productid " . $AndQuery . "\n LEFT JOIN \n (\n SELECT \n SUM(ordprodqty) ordprodqty, ordprodid FROM [|PREFIX|]order_products\n LEFT JOIN isc_orders ON orderid = orderorderid \n WHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $from_stamp . "' AND orddate <= '" . $to_stamp . "' \n AND ordprodtype != 'giftcertificate' \n GROUP BY ordprodid\n ) AS op ON op.ordprodid = p.productid\n " . $vendorSql . " " . $nonezero . "\n GROUP BY p.productid \n ORDER BY\n " . $sortField . " " . $sortOrder;
$countQuery = "\n SELECT\n COUNT(*) AS num\n FROM\n [|PREFIX|]product_view_count pvc LEFT JOIN [|PREFIX|]products p on p.productid = pvc.productid " . $AndQuery . " " . $vendorSql . " group by p.productid";
}
/*
//Fetch the orders for this page
$mainQuery = "
SELECT
p.productid,
p.prodcode,
p.prodname,
$newselect,
IF(p.prodnumratings > 0, p.prodratingtotal / p.prodnumratings, 0) AS avgrating
FROM
[|PREFIX|]products p
" . $leftJoin . "
" . $vendorSql . "
" . $groupBy . "
ORDER BY
" . $sortField . " " . $sortOrder;
*/
}
/**
* Calculate information about a specific vendor's outstanding balance & last payment.
*
* @param int The vendor ID to generate the information for.
* @return array Array containing the total amount of orders, forward balance, last payment date and the outstanding balance.
*/
public function CalculateOutstandingVendorBalance($vendorId)
{
// Grab the date of the last payment sent to the vendor and the balance owing at the time
$query = "\n\t\t\tSELECT paymentdate, paymentforwardbalance, vendorprofitmargin\n\t\t\tFROM [|PREFIX|]vendors\n\t\t\tLEFT JOIN [|PREFIX|]vendor_payments ON (paymentvendorid=vendorid AND paymentdeducted='1')\n\t\t\tWHERE vendorid='" . $vendorId . "'\n\t\t\tORDER BY paymentdate DESC\n\t\t";
$query .= $GLOBALS['ISC_CLASS_DB']->AddLimit(0, 1);
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$vendorPaymentDetails = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
if (!$vendorPaymentDetails['paymentdate']) {
$vendorPaymentDetails['paymentdate'] = 0;
$vendorPaymentDetails['paymentforwardbalance'] = 0;
// Try and grab the date of the first order for this vendor
$query = "\n\t\t\t\tSELECT orddate\n\t\t\t\tFROM [|PREFIX|]orders\n\t\t\t\tWHERE ordvendorid='" . (int) $vendorId . "'\n\t\t\t\tORDER BY orddate ASC\n\t\t\t\tLIMIT 1\n\t\t\t";
$vendorPaymentDetails['paymentdate'] = $GLOBALS['ISC_CLASS_DB']->FetchOne($query);
}
if (!$vendorPaymentDetails['paymentdate']) {
$vendorPaymentDetails['paymentdate'] = time();
}
// Grab the total amount of orders since the last payment
$query = "\n\t\t\tSELECT SUM(ordtotalamount)\n\t\t\tFROM [|PREFIX|]orders\n\t\t\tWHERE ordvendorid='" . (int) $vendorId . "' AND orddate >= '" . (int) $vendorPaymentDetails['paymentdate'] . "'\n\t\t\t\tAND ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ")\n\t\t";
$GLOBALS['ISC_CLASS_DB']->Query($query);
$totalOrders = $GLOBALS['ISC_CLASS_DB']->FetchOne($query);
$profitMargin = 0;
if ($vendorPaymentDetails['vendorprofitmargin'] > 0) {
$profitMargin = $totalOrders / 100 * $vendorPaymentDetails['vendorprofitmargin'];
}
$summary = array('totalOrders' => $totalOrders, 'balanceForward' => $vendorPaymentDetails['paymentforwardbalance'], 'lastPaymentDate' => $vendorPaymentDetails['paymentdate'], 'outstandingBalance' => $totalOrders - $profitMargin + $vendorPaymentDetails['paymentforwardbalance'], 'profitMargin' => $profitMargin, 'profitMarginPercentage' => number_format($vendorPaymentDetails['vendorprofitmargin'], GetConfig('DecimalPlaces'), GetConfig('DecimalToken'), ''));
return $summary;
}
public function TaxStatsByDateGrid()
{
$GLOBALS['TaxGrid'] = "";
if(!(isset($_GET['From']) && isset($_GET['To']))) {
return;
}
$from_stamp = (int)$_GET['From'];
$to_stamp = (int)$_GET['To'];
// How many records per page?
if(isset($_GET['Show'])) {
$per_page = (int)$_GET['Show'];
}
else {
$per_page = 20;
}
$GLOBALS['TaxPerPage'] = $per_page;
$GLOBALS["IsShowPerPage" . $per_page] = 'selected="selected"';
// Should we limit the records returned?
if(isset($_GET['Page'])) {
$page = (int)$_GET['Page'];
}
else {
$page = 1;
}
$GLOBALS['TaxByDateCurrentPage'] = $page;
// Workout the start and end records
$start = ($per_page * $page) - $per_page;
$end = $start + ($per_page - 1);
// Only fetch data this user can actually see
$vendorRestriction = $this->GetVendorRestriction();
$vendorSql = '';
if($vendorRestriction !== false) {
$vendorSql = " AND ordvendorid='".(int)$vendorRestriction."'";
}
// Calculate the number of seconds from GMT +0 that we are in. We'll be adjusting
// the orddate in the query below so that it becomes timezone specific (remember, MySQL thinks we're +0)
$timezoneAdjustment = GetConfig('StoreTimeZone');
if(GetConfig('StoreDSTCorrection')) {
++$timezoneAdjustment;
}
$timezoneAdjustment *= 3600;
if (empty($_GET['TaxListBy'])) {
$groupBy = 'Day';
}
else {
$groupBy = $_GET['TaxListBy'];
}
$fieldSQL = '';
$addDay = 0;
$addMonth = 0;
$addYear = 0;
$startStamp = $from_stamp;
$endStamp = mktime(23, 59, 59, date('m', $to_stamp), date('j', $to_stamp), date('Y', $to_stamp));
switch ($groupBy) {
case 'Day':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-%m-%d') AS formatteddate";
$addDay = 1;
$currentStamp = $startStamp;
$dateFormat = 'jS M Y';
break;
case 'Month':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-%m-1') AS formatteddate";
$addMonth = 1;
$currentStamp = mktime(0, 0, 0, date('m', $from_stamp) + $start, 1, date('Y', $from_stamp));
$dateFormat = 'F Y';
break;
case 'Year':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-1-1') AS formatteddate";
$addYear = 1;
$currentStamp = mktime(0, 0, 0, 1, 1, date('Y', $from_stamp));
$dateFormat = 'Y';
break;
}
$query = "
SELECT
t.name,
t.class,
t.rate,
SUM(t.line_amount) AS amount,
COUNT(DISTINCT t.order_id) AS numorders,
".$fieldSQL."
FROM [|PREFIX|]order_taxes t
JOIN [|PREFIX|]orders o ON (o.orderid=t.order_id)
WHERE
t.line_amount > 0 AND
o.ordstatus IN (".implode(',', GetPaidOrderStatusArray()).") AND
o.deleted = 0 AND
orddate >= '" . $startStamp . "' AND
orddate <= '" . $endStamp . "'
" . $vendorSql . "
GROUP BY
formatteddate,
t.name,
t.class,
t.rate
ORDER BY
formatteddate
";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$dataRows = array();
while($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
$date = strtotime($row['formatteddate']);
$dataRows[$date][] = $row;
}
$lastdate = '';
$outputRows = array();
while (true) {
if (!isset($dataRows[$currentStamp])) {
$outputRows[] = array(
'period' => $currentStamp,
'taxtype' => '',
'taxrate' => '',
'numorders' => 0,
'taxamount' => 0
);
}
else {
foreach ($dataRows[$currentStamp] as $row) {
$outputRows[] = array(
'period' => $currentStamp,
'taxtype' => $row['name'].' - '.$row['class'],
'taxrate' => $row['rate'],
'numorders' => $row['numorders'],
'taxamount' => $row['amount']
);
}
}
$currentStamp = mktime(0,0,0,date('m', $currentStamp) + $addMonth, date('d',$currentStamp) + $addDay, date('Y', $currentStamp) + $addYear);
if ($currentStamp > $endStamp) {
break;
}
}
// Workout the paging
$num_pages = ceil(count($outputRows) / $per_page);
$paging = sprintf(GetLang('PageXOfX'), $page, $num_pages);
$paging .= " ";
// Is there more than one page? If so show the « to jump back to page 1
if($num_pages > 1) {
$paging .= "<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(1)'>«</a> | ";
}
else {
$paging .= "« | ";
}
// Are we on page 2 or above?
if($page > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%s</a> | ", $page-1, GetLang('Prev'));
}
else {
$paging .= sprintf("%s | ", GetLang('Prev'));
}
for($i = 1; $i <= $num_pages; $i++) {
// Only output paging -5 and +5 pages from the page we're on
if($i >= $page-6 && $i <= $page+5) {
if($page == $i) {
$paging .= sprintf("<strong>%d</strong> | ", $i);
}
else {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%d</a> | ", $i, $i);
}
}
}
// Are we on page 2 or above?
if($page < $num_pages) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%s</a> | ", $page+1, GetLang('Next'));
}
else {
$paging .= sprintf("%s | ", GetLang('Next'));
}
// Is there more than one page? If so show the » to go to the last page
if($num_pages > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>»</a> | ", $num_pages);
}
else {
$paging .= "» | ";
}
$paging = rtrim($paging, ' |');
$GLOBALS['Paging'] = $paging;
if(isset($_GET['SortOrder']) && $_GET['SortOrder'] == "desc") {
$sortOrder = "desc";
}
else {
$sortOrder = "asc";
}
$sortFields = array('period','taxtype','taxrate','numorders','taxamount');
if(isset($_GET['SortBy']) && in_array($_GET['SortBy'], $sortFields)) {
$sortField = $_GET['SortBy'];
SaveDefaultSortField("TaxStats", $_REQUEST['SortBy'], $sortOrder);
}
else {
list($sortField, $sortOrder) = GetDefaultSortField("TaxStats", "period", $sortOrder);
}
$sortLinks = array(
"Period" => "period",
"TaxType" => "taxtype",
"TaxRate" => "taxrate",
"NumOrders" => "numorders",
"TaxAmount" => "taxamount"
);
BuildAdminSortingLinks($sortLinks, "javascript:SortTaxStats('%%SORTFIELD%%', '%%SORTORDER%%');", $sortField, $sortOrder);
if ($sortOrder == "asc") {
$arraySort = SORT_ASC;
}
else {
$arraySort = SORT_DESC;
}
// now lets sort the rows we've built
$outputRows = Interspire_Array::msort($outputRows, array($sortField => $arraySort));
$offset = ($page - 1) * $per_page;
// lets grab the subset of rows for the current page
$outputRows = array_slice($outputRows, $offset, $per_page);
$lastStamp = 0;
$output = '';
foreach ($outputRows as $row) {
$output .= "<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">";
if ($lastStamp != $row['period']) {
$output .= '<td class="'.$GLOBALS['SortedFieldPeriodClass'].'">' . date($dateFormat, $row['period']) . '</td>';
}
else {
$output .= '<td class="'.$GLOBALS['SortedFieldPeriodClass'].'"> </td>';
}
if ($row['taxamount'] == 0) {
$output .= '<td align="left" colspan="2" class="'.$GLOBALS['SortedFieldTaxTypeClass'].'"><em>' . GetLang('NoTaxCollected') . '</em></td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldNumOrdersClass'].'">0</td>';
$output .= '<td align="right" class="'.$GLOBALS['SortedFieldTaxAmountClass'].'">' . FormatPrice(0) . '</td>';
}
else {
$output .= '<td class="'.$GLOBALS['SortedFieldTaxTypeClass'].'">' . $row['taxtype'] . '</td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldTaxRateClass'].'">' . ($row['taxrate']/1) .'%</td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldNumOrdersClass'].'">' . number_format($row['numorders']) . '</td>';
$output .= '<td align="right" class="'.$GLOBALS['SortedFieldTaxAmountClass'].'">' . FormatPrice($row['taxamount']) . '</td>';
}
$output .= '</tr>';
$lastStamp = $row['period'];
}
$GLOBALS['TaxGrid'] = $output;
// Add the limit
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$this->template->display('stats.orders.salestax.tpl');
}
/**
* Generate the KPI table for orders, visitors, conversion rate etc.
* Will use the time period from the request if one exists (GET or COOKIE)
* or falls back to the last week.
*
* @return string The generated HTML for the performance indicators table.
*/
public function GeneratePerformanceIndicatorsTable()
{
if (!$this->auth->HasPermission(AUTH_Statistics_Overview)) {
return false;
}
// If we don't have a period coming in via the URL, use the default
if (!isset($_GET['period'])) {
// Is it set in a cookie?
if (isset($_COOKIE['DashboardPerformanceIndicatorsPeriod'])) {
$period = $_COOKIE['DashboardPerformanceIndicatorsPeriod'];
} else {
$period = 'week';
}
} else {
$period = $_GET['period'];
}
// Determine for which dates we need to fetch the statistics
switch ($period) {
case 'week':
$lastPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d') - 13, isc_date('y'));
$thisPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d') - 6, isc_date('y'));
break;
case 'month':
$lastPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m') - 2, isc_date('d'), isc_date('y'));
$thisPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m') - 1, isc_date('d'), isc_date('y'));
break;
case 'year':
$lastPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d'), isc_date('y') - 2);
$thisPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d'), isc_date('y') - 1);
break;
default:
$period = 'day';
$lastPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d') - 1, isc_date('y'));
$thisPeriodFrom = isc_gmmktime(0, 0, 0, isc_date('m'), isc_date('d'), isc_date('y'));
}
$this->template->Assign('LastPeriodHeader', GetLang('Last' . ucfirst($period)));
$this->template->Assign('ThisPeriodHeader', GetLang('This' . ucfirst($period)));
// Run up until 1 second before the current period. Subtracting 1 second allows us to generate displayable dates for the period.
$lastPeriodTo = $thisPeriodFrom - 1;
if ($period != 'day') {
$this->template->Assign('LastPeriodDateRange', CDate($lastPeriodFrom) . ' - ' . CDate($lastPeriodTo));
$this->template->Assign('ThisPeriodDateRange', CDate($thisPeriodFrom) . ' - ' . CDate(time()));
} else {
$this->template->Assign('LastPeriodDateRange', CDate($lastPeriodFrom));
$this->template->Assign('ThisPeriodDateRange', CDate($thisPeriodFrom));
}
// Calculate the number of orders and the total revenue
$vendorAdd = '';
if ($this->auth->GetVendorId()) {
$vendorAdd .= " AND ordvendorid='" . $this->auth->GetVendorId() . "'";
}
$query = "\n\t\t\tSELECT SUM(ordtotalamount) AS totalrevenue, COUNT(orderid) AS numorders\n\t\t\tFROM [|PREFIX|]orders\n\t\t\tWHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $lastPeriodFrom . "' AND orddate <= '" . $lastPeriodTo . "' " . $vendorAdd . "\n\t\t";
$result = $this->db->Query($query);
$lastPeriodOrderStats = $this->db->Fetch($result);
$query = "\n\t\t\tSELECT SUM(ordtotalamount) AS totalrevenue, COUNT(orderid) AS numorders\n\t\t\tFROM [|PREFIX|]orders\n\t\t\tWHERE ordstatus IN (" . implode(',', GetPaidOrderStatusArray()) . ") AND orddate >= '" . $thisPeriodFrom . "' " . $vendorAdd . "\n\t\t";
$result = $this->db->Query($query);
$thisPeriodOrderStats = $this->db->Fetch($result);
// Calculate the number of visitors
if (!$this->auth->GetVendorId()) {
$query = "\n\t\t\t\tSELECT SUM(numuniques)\n\t\t\t\tFROM [|PREFIX|]unique_visitors\n\t\t\t\tWHERE datestamp >= '" . $lastPeriodFrom . "' AND datestamp <= '" . $lastPeriodTo . "'\n\t\t\t";
$lastPeriodVisitorStats = $this->db->FetchOne($query);
$query = "\n\t\t\t\tSELECT SUM(numuniques)\n\t\t\t\tFROM [|PREFIX|]unique_visitors\n\t\t\t\tWHERE datestamp >= '" . $thisPeriodFrom . "'\n\t\t\t";
$thisPeriodVisitorStats = $this->db->FetchOne($query);
// Calculate the percentage change in visitors between the last period and the current period
$visitorChange = $thisPeriodVisitorStats - $lastPeriodVisitorStats;
$prefix = '';
if ($visitorChange == 0) {
$visitorChangePercent = 0;
} else {
if ($lastPeriodVisitorStats > 0) {
$visitorChangePercent = round($visitorChange / $lastPeriodVisitorStats * 100, 2);
} else {
$visitorChangePercent = 100;
}
}
if ($visitorChangePercent > 0) {
$prefix = '+';
$this->template->Assign('NumVisitorsChangeClass', 'Positive');
} else {
if ($visitorChangePercent < 0) {
$this->template->Assign('NumVisitorsChangeClass', 'Negative');
}
}
$visitorChangePercent = $prefix . number_format($visitorChangePercent, 2) . '%';
$this->template->Assign('LastPeriodNumVisitors', number_format($lastPeriodVisitorStats));
$this->template->Assign('ThisPeriodNumVisitors', number_format($thisPeriodVisitorStats));
$this->template->Assign('NumVisitorsChange', $visitorChangePercent);
$lastConversion = 0;
if ($lastPeriodVisitorStats > 0) {
$lastConversion = $lastPeriodOrderStats['numorders'] / $lastPeriodVisitorStats * 100;
}
$this->template->Assign('LastPeriodConversionRate', number_format(round($lastConversion, 2), 2));
$thisConversion = 0;
if ($thisPeriodVisitorStats > 0) {
$thisConversion = $thisPeriodOrderStats['numorders'] / $thisPeriodVisitorStats * 100;
}
$this->template->Assign('ThisPeriodConversionRate', number_format(round($thisConversion, 2), 2));
// Calculate the difference between the two conversion dates to get the change
$conversionChangePercent = $thisConversion - $lastConversion;
$prefix = '';
if ($conversionChangePercent > 0) {
$prefix = '+';
$this->template->Assign('ConversionChangeClass', 'Positive');
} else {
if ($conversionChangePercent < 0) {
$this->template->Assign('ConversionChangeClass', 'Negative');
}
}
$conversionChangePercent = $prefix . number_format($conversionChangePercent, 2) . '%';
$this->template->Assign('ConversionChange', $conversionChangePercent);
} else {
$this->template->Assign('HideConversionRate', 'display: none');
$this->template->Assign('HideVisitorStats', 'display: none');
}
// Calculate the percentage change in revenue between the last period and the current period
$revenueChange = $thisPeriodOrderStats['totalrevenue'] - $lastPeriodOrderStats['totalrevenue'];
$prefix = '';
if ($revenueChange == 0) {
$revenueChangePercent = 0;
} else {
if ($lastPeriodOrderStats['totalrevenue'] > 0) {
$revenueChangePercent = round($revenueChange / $lastPeriodOrderStats['totalrevenue'] * 100, 2);
} else {
$revenueChangePercent = 100;
}
}
if ($revenueChangePercent > 0) {
$prefix = '+';
$this->template->Assign('TotalRevenueChangeClass', 'Positive');
} else {
if ($revenueChangePercent < 0) {
$this->template->Assign('TotalRevenueChangeClass', 'Negative');
}
}
$revenueChangePercent = $prefix . number_format($revenueChangePercent, 2) . '%';
// Calculate the percentage change in the number of orders in the last period and the current period
$numOrdersChange = $thisPeriodOrderStats['numorders'] - $lastPeriodOrderStats['numorders'];
$prefix = '';
if ($numOrdersChange == 0) {
$numOrdersChangePercent = 0;
} else {
if ($lastPeriodOrderStats['numorders'] > 0) {
$numOrdersChangePercent = round($numOrdersChange / $lastPeriodOrderStats['numorders'] * 100, 2);
} else {
$numOrdersChangePercent = 100;
}
}
if ($numOrdersChangePercent > 0) {
$prefix = '+';
$this->template->Assign('NumOrdersChangeClass', 'Positive');
} else {
if ($numOrdersChangePercent < 0) {
$this->template->Assign('NumOrdersChangeClass', 'Negative');
}
}
$numOrdersChangePercent = $prefix . number_format($numOrdersChangePercent, 2) . '%';
$this->template->Assign('LastPeriodRevenue', FormatPrice($lastPeriodOrderStats['totalrevenue']));
$this->template->Assign('LastPeriodNumOrders', number_format($lastPeriodOrderStats['numorders']));
$this->template->Assign('ThisPeriodRevenue', FormatPrice($thisPeriodOrderStats['totalrevenue']));
$this->template->Assign('ThisPeriodNumOrders', number_format($thisPeriodOrderStats['numorders']));
$this->template->Assign('TotalRevenueChange', $revenueChangePercent);
$this->template->Assign('NumOrdersChange', $numOrdersChangePercent);
// If they've just changed periods, store it in a cookie
if (isset($_GET['period'])) {
isc_setcookie('DashboardPerformanceIndicatorsPeriod', $period);
}
return $this->template->GetSnippet('DashboardPerformanceIndicators');
}
public function GetListQuery($where, $having, $sortField, $sortOrder)
{
if ($where) {
$where .= " AND ";
}
$query = "
SELECT
o.orddate,
t.name,
t.class,
t.rate,
SUM(t.line_amount) AS amount,
COUNT(DISTINCT t.order_id) AS numorders,
" . $this->dateField . " AS formatteddate
FROM [|PREFIX|]order_taxes t
JOIN [|PREFIX|]orders o ON (o.orderid=t.order_id)
WHERE
" . $where . "
t.line_amount > 0 AND
o.ordstatus IN (".implode(',', GetPaidOrderStatusArray()).") AND
o.deleted = 0
GROUP BY
formatteddate,
t.name,
t.class,
t.rate
ORDER BY
formatteddate
";
return $query;
}
/**
* Calculate information about a specific vendor's outstanding balance & last payment.
*
* @param int The vendor ID to generate the information for.
* @return array Array containing the total amount of orders, forward balance, last payment date and the outstanding balance.
*/
public function CalculateOutstandingVendorBalance($vendorId)
{
// Grab the date of the last payment sent to the vendor and the balance owing at the time
$query = "
SELECT paymentdate, paymentforwardbalance, vendorprofitmargin
FROM [|PREFIX|]vendors
LEFT JOIN [|PREFIX|]vendor_payments ON (paymentvendorid=vendorid AND paymentdeducted='1')
WHERE vendorid='".($vendorId)."'
ORDER BY paymentdate DESC
";
$query .= $GLOBALS['ISC_CLASS_DB']->AddLimit(0, 1);
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$vendorPaymentDetails = $GLOBALS['ISC_CLASS_DB']->Fetch($result);
if(!$vendorPaymentDetails['paymentdate']) {
$vendorPaymentDetails['paymentdate'] = 0;
$vendorPaymentDetails['paymentforwardbalance'] = 0;
// Try and grab the date of the first order for this vendor
$query = "
SELECT orddate
FROM [|PREFIX|]orders
WHERE ordvendorid='".(int)$vendorId."'
ORDER BY orddate ASC
LIMIT 1
";
$vendorPaymentDetails['paymentdate'] = $GLOBALS['ISC_CLASS_DB']->FetchOne($query);
}
if(!$vendorPaymentDetails['paymentdate']) {
$vendorPaymentDetails['paymentdate'] = time();
}
// Grab the total amount of orders since the last payment
$query = "
SELECT SUM(total_inc_tax)
FROM [|PREFIX|]orders
WHERE ordvendorid='".(int)$vendorId."' AND orddate >= '".(int)$vendorPaymentDetails['paymentdate']."'
AND ordstatus IN (".implode(',', GetPaidOrderStatusArray()).")
";
$GLOBALS['ISC_CLASS_DB']->Query($query);
$totalOrders = $GLOBALS['ISC_CLASS_DB']->FetchOne($query);
$profitMargin = 0;
if($vendorPaymentDetails['vendorprofitmargin'] > 0) {
$profitMargin = ($totalOrders/100)*$vendorPaymentDetails['vendorprofitmargin'];
}
// check if the vendor has issued any store credit for a return, we need to deduct that from the total
$query = "
SELECT
cc.creditamount
FROM
[|PREFIX|]returns r
LEFT JOIN [|PREFIX|]customer_credits cc ON cc.creditrefid = r.returnid
WHERE
cc.credittype = 'return' AND
r.retreceivedcredit = 1 AND
r.retvendorid = '" . (int)$vendorId . "' AND
cc.creditdate >= '" . (int)$vendorPaymentDetails['paymentdate'] . "'
";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$issuedCredit = 0;
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
$issuedCredit += $row['creditamount'];
}
$summary = array(
'totalOrders' => $totalOrders,
'balanceForward' => $vendorPaymentDetails['paymentforwardbalance'],
'issuedCredit' => $issuedCredit,
'lastPaymentDate' => $vendorPaymentDetails['paymentdate'],
'outstandingBalance' => ($totalOrders-$profitMargin) + $vendorPaymentDetails['paymentforwardbalance'] - $issuedCredit,
'profitMargin' => $profitMargin,
'profitMarginPercentage' => number_format($vendorPaymentDetails['vendorprofitmargin'], GetConfig('DecimalPlaces'), GetConfig('DecimalToken'), '')
);
return $summary;
}