function GetBestSellerList($by = "AMOUNT", $arFilter = array(), $arOrderFilter = array(), $limit = 0)
{
global $DB;
$byQuantity = false;
if ($by == "QUANTITY") {
$byQuantity = true;
}
$arJoin = array();
$arWhere = array();
$orderFilter = "";
$i = 1;
if (is_array($arFilter) && count($arFilter) > 0) {
foreach ($arFilter as $key => $value) {
$arJoin[] = "LEFT JOIN b_sale_basket_props p" . $i . " ON (b.ID = p" . $i . ".BASKET_ID)";
$arFilter = CSaleProduct::GetFilterOperation($key, $value);
$arWhere[] = " AND p" . $i . ".CODE = '" . $arFilter["field"] . "' AND p" . $i . ".VALUE " . $arFilter["operation"] . " " . $arFilter["value"];
$i++;
}
}
$arFields = array("ID" => array("FIELD_NAME" => "O.ID", "FIELD_TYPE" => "int"), "LID" => array("FIELD_NAME" => "O.LID", "FIELD_TYPE" => "string"), "PERSON_TYPE_ID" => array("FIELD_NAME" => "O.PERSON_TYPE_ID", "FIELD_TYPE" => "int"), "PAYED" => array("FIELD_NAME" => "O.PAYED", "FIELD_TYPE" => "string"), "DATE_PAYED" => array("FIELD_NAME" => "O.DATE_PAYED", "FIELD_TYPE" => "datetime"), "EMP_PAYED_ID" => array("FIELD_NAME" => "O.EMP_PAYED_ID", "FIELD_TYPE" => "int"), "CANCELED" => array("FIELD_NAME" => "O.CANCELED", "FIELD_TYPE" => "string"), "DATE_CANCELED" => array("FIELD_NAME" => "O.DATE_CANCELED", "FIELD_TYPE" => "datetime"), "EMP_CANCELED_ID" => array("FIELD_NAME" => "O.EMP_CANCELED_ID", "FIELD_TYPE" => "int"), "REASON_CANCELED" => array("FIELD_NAME" => "O.REASON_CANCELED", "FIELD_TYPE" => "string"), "STATUS_ID" => array("FIELD_NAME" => "O.STATUS_ID", "FIELD_TYPE" => "string"), "DATE_STATUS" => array("FIELD_NAME" => "O.DATE_STATUS", "FIELD_TYPE" => "datetime"), "PAY_VOUCHER_NUM" => array("FIELD_NAME" => "O.PAY_VOUCHER_NUM", "FIELD_TYPE" => "string"), "PAY_VOUCHER_DATE" => array("FIELD_NAME" => "O.PAY_VOUCHER_DATE", "FIELD_TYPE" => "date"), "EMP_STATUS_ID" => array("FIELD_NAME" => "O.EMP_STATUS_ID", "FIELD_TYPE" => "int"), "PRICE_DELIVERY" => array("FIELD_NAME" => "O.PRICE_DELIVERY", "FIELD_TYPE" => "double"), "ALLOW_DELIVERY" => array("FIELD_NAME" => "O.ALLOW_DELIVERY", "FIELD_TYPE" => "string"), "DATE_ALLOW_DELIVERY" => array("FIELD_NAME" => "O.DATE_ALLOW_DELIVERY", "FIELD_TYPE" => "datetime"), "EMP_ALLOW_DELIVERY_ID" => array("FIELD_NAME" => "O.EMP_ALLOW_DELIVERY_ID", "FIELD_TYPE" => "int"), "PRICE" => array("FIELD_NAME" => "O.PRICE", "FIELD_TYPE" => "double"), "CURRENCY" => array("FIELD_NAME" => "O.CURRENCY", "FIELD_TYPE" => "string"), "DISCOUNT_VALUE" => array("FIELD_NAME" => "O.DISCOUNT_VALUE", "FIELD_TYPE" => "double"), "SUM_PAID" => array("FIELD_NAME" => "O.SUM_PAID", "FIELD_TYPE" => "double"), "USER_ID" => array("FIELD_NAME" => "O.USER_ID", "FIELD_TYPE" => "int"), "PAY_SYSTEM_ID" => array("FIELD_NAME" => "O.PAY_SYSTEM_ID", "FIELD_TYPE" => "int"), "DELIVERY_ID" => array("FIELD_NAME" => "O.DELIVERY_ID", "FIELD_TYPE" => "string"), "DATE_INSERT" => array("FIELD_NAME" => "O.DATE_INSERT", "FIELD_TYPE" => "datetime"), "DATE_INSERT_FORMAT" => array("FIELD_NAME" => "O.DATE_INSERT", "FIELD_TYPE" => "datetime"), "DATE_UPDATE" => array("FIELD_NAME" => "O.DATE_UPDATE", "FIELD_TYPE" => "datetime"), "USER_DESCRIPTION" => array("FIELD_NAME" => "O.USER_DESCRIPTION", "FIELD_TYPE" => "string"), "ADDITIONAL_INFO" => array("FIELD_NAME" => "O.ADDITIONAL_INFO", "FIELD_TYPE" => "string"), "PS_STATUS" => array("FIELD_NAME" => "O.PS_STATUS", "FIELD_TYPE" => "string"), "PS_STATUS_CODE" => array("FIELD_NAME" => "O.PS_STATUS_CODE", "FIELD_TYPE" => "string"), "PS_STATUS_DESCRIPTION" => array("FIELD_NAME" => "O.PS_STATUS_DESCRIPTION", "FIELD_TYPE" => "string"), "PS_STATUS_MESSAGE" => array("FIELD_NAME" => "O.PS_STATUS_MESSAGE", "FIELD_TYPE" => "string"), "PS_SUM" => array("FIELD_NAME" => "O.PS_SUM", "FIELD_TYPE" => "double"), "PS_CURRENCY" => array("FIELD_NAME" => "O.PS_CURRENCY", "FIELD_TYPE" => "string"), "PS_RESPONSE_DATE" => array("FIELD_NAME" => "O.PS_RESPONSE_DATE", "FIELD_TYPE" => "datetime"), "COMMENTS" => array("FIELD_NAME" => "O.COMMENTS", "FIELD_TYPE" => "string"), "TAX_VALUE" => array("FIELD_NAME" => "O.TAX_VALUE", "FIELD_TYPE" => "double"), "STAT_GID" => array("FIELD_NAME" => "O.STAT_GID", "FIELD_TYPE" => "string"), "RECURRING_ID" => array("FIELD_NAME" => "O.RECURRING_ID", "FIELD_TYPE" => "int"), "RECOUNT_FLAG" => array("FIELD_NAME" => "O.RECOUNT_FLAG", "FIELD_TYPE" => "string"), "AFFILIATE_ID" => array("FIELD_NAME" => "O.AFFILIATE_ID", "FIELD_TYPE" => "int"), "DELIVERY_DOC_NUM" => array("FIELD_NAME" => "O.DELIVERY_DOC_NUM", "FIELD_TYPE" => "string"), "DELIVERY_DOC_DATE" => array("FIELD_NAME" => "O.DELIVERY_DOC_DATE", "FIELD_TYPE" => "date"));
if (is_array($arOrderFilter) && count($arOrderFilter) > 0) {
$sqlWhere = new CSQLWhere();
$sqlWhere->SetFields($arFields, $arJ);
$orderFilter = $sqlWhere->GetQueryEx($arOrderFilter, $arJ);
}
//if($byQuantity)
// $strSql = "SELECT b.PRODUCT_ID, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, SUM(b.QUANTITY) as QUANTITY \n";
//else
$strSql = "SELECT b.PRODUCT_ID, b.NAME, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, SUM(b.PRICE*b.QUANTITY) as PRICE, AVG(b.PRICE) as AVG_PRICE, SUM(b.QUANTITY) as QUANTITY, b.CURRENCY \n";
$strSql .= "FROM b_sale_basket b \n";
foreach ($arJoin as $v) {
$strSql .= $v . "\n";
}
if (strlen($orderFilter) > 0) {
$strSql .= "INNER JOIN b_sale_order O ON (b.ORDER_ID = O.ID) \n";
}
$strSql .= "WHERE \n" . " b.ORDER_ID is not null \n";
foreach ($arWhere as $v) {
$strSql .= $v . "\n";
}
if (strlen($orderFilter) > 0) {
$strSql .= " AND " . $orderFilter . "\n";
}
$strSql .= " GROUP BY b.PRODUCT_ID, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, b.CURRENCY \n";
if ($byQuantity) {
$strSql .= " ORDER BY QUANTITY DESC\n";
} else {
$strSql .= " ORDER BY PRICE DESC\n";
}
if (IntVal($limit) > 0) {
$strSql .= "LIMIT " . IntVal($limit);
}
// echo htmlspecialcharsbx($strSql);
$dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
return $dbRes;
}
