public function execute() { $tp = SJB_System::getTemplateProcessor(); $action = SJB_Request::getVar('action', false); $sid = SJB_Request::getVar('sid', 0); $errors = array(); switch ($action) { case 'activate': SJB_ProductsManager::activateProductBySID($sid); break; case 'deactivate': SJB_ProductsManager::deactivateProductBySID($sid); break; case 'delete': if (SJB_ContractManager::getContractQuantityByProductSID($sid) || SJB_InvoiceManager::getInvoiceQuantityByProductSID($sid)) { $errors['PRODUCT_IS_IN_USE'] = 1; } else { SJB_ProductsManager::deleteProductBySID($sid); } break; } $products = SJB_ProductsManager::getAllProductsInfo(); foreach ($products as $key => $productInfo) { $product = new SJB_Product($productInfo, $productInfo['product_type']); $product->setNumberOfListings(1); if ($productInfo['product_type'] != 'post_listings' && $productInfo['product_type'] != 'mixed_product') { $products[$key]['number_of_postings'] = '-'; } $products[$key]['price'] = $product->getPrice(); $products[$key]['user_group'] = SJB_UserGroupManager::getUserGroupInfoBySID($productInfo['user_group_sid']); $products[$key]['product_type'] = SJB_ProductsManager::getProductTypeByID($productInfo['product_type']); $products[$key]['subscribed_users'] = SJB_ContractManager::getContractQuantityByProductSID($productInfo['sid']); $products[$key]['invoices'] = SJB_InvoiceManager::getInvoiceQuantityByProductSID($productInfo['sid']); if (!empty($productInfo['availability_to']) && $productInfo['availability_to'] <= date('Y-m-d')) { $products[$key]['expired'] = 1; } } $tp->assign('errors', $errors); $tp->assign('products', $products); $tp->display('products.tpl'); }
public static function getSalesStatistics($period, $filter, $sorting_field, $sorting_order) { $where = ''; if (!empty($period['from'])) { $period['from'] = SJB_I18N::getInstance()->getInput('date', $period['from']); $time = "00:00:00"; $where .= " AND s.`date` >= '{$period['from']} {$time}' "; } if (!empty($period['to'])) { $period['to'] = SJB_I18N::getInstance()->getInput('date', $period['to']); $time = "23:59:59"; $where .= " AND s.`date` <= '{$period['to']} {$time}' "; } $join = ''; $groupBy = ''; $query = ''; if (in_array($filter, array('Location_Country', 'Location_State', 'Location_City'))) { $fieldInfo = SJB_ListingFieldDBManager::getLocationFieldsInfoById($filter); } else { $fieldInfo = SJB_ListingFieldDBManager::getListingFieldInfoByID($filter); } if (strstr($filter, 'userGroup_')) { $userGroupSID = str_replace('userGroup_', '', $filter); $userGroupID = SJB_UserGroupManager::getUserGroupIDBySID($userGroupSID); $join = " INNER JOIN `users` u ON s.`user_sid` = u.`sid` "; $where .= " AND u.`user_group_sid` = '{$userGroupSID}'"; $groupBy = " u.`sid`"; $query = ', u.* '; } elseif (!empty($fieldInfo['type']) && $fieldInfo['type'] == 'list' && empty($fieldInfo['parent_sid'])) { $join = " INNER JOIN `users` u ON s.`user_sid` = u.`sid` INNER JOIN `user_profile_field_list` ufl ON u.`{$filter}` = ufl.`sid` "; $groupBy = " `{$filter}` "; $query = ", ufl.`value` as {$filter} "; } elseif (!empty($fieldInfo['type']) && $fieldInfo['type'] == 'list' && !empty($fieldInfo['parent_sid'])) { if ($filter == 'Location_Country') { $join = " INNER JOIN `users` u ON s.`user_sid` = u.`sid` INNER JOIN `countries` c ON u.`{$filter}` = c.`sid` "; $query = ", c.`country_name` as {$filter} "; } else { $join = " INNER JOIN `users` u ON s.`user_sid` = u.`sid` INNER JOIN `states` st ON u.`{$filter}` = st.`sid` "; $query = ", st.`state_name` as {$filter} "; } $groupBy = " `{$filter}` "; } elseif ($filter == 'sid') { $join = " INNER JOIN `products` p ON s.`object_sid` = p.`sid` "; $where .= " AND s.type = 'product' "; $groupBy = " s.`object_sid`, s.`featured`, s.`priority`, s.`reactivate` "; $query = ', p.* '; } else { $join = " INNER JOIN `users` u ON s.`user_sid` = u.`sid` "; $where .= " AND u.`{$filter}` != '' AND u.`{$filter}` IS NOT NULL "; $groupBy = " u.`{$filter}` "; $query = ', u.* '; } $orderBy = ''; if ($sorting_field == 'username') { if (strstr($filter, 'userGroup_')) { if ($userGroupID == 'Employer') { $orderBy = "ORDER BY `CompanyName` {$sorting_order}"; } else { $orderBy = "ORDER BY `FirstName`, `LastName` {$sorting_order}"; } } } else { $orderBy = "ORDER BY {$sorting_field} {$sorting_order}"; } $statisticsInfo = array(); $total = $totalSum = SJB_DB::query("SELECT sum(s.`price`*s.`count`) as total, sum(s.`count`) as units_sold, 'Total' as totalSum, 0 as `sid` FROM `statistics` s {$join} WHERE s.`event` = 'payment' {$where}"); $total = $total ? array_pop($total) : array('total' => 0); $percent = $total['total'] != 0 ? 100 / $total['total'] : 0; if ($filter == 'sid') { $statisticsInfo = SJB_DB::query("SELECT s.*, sum(s.`price`*s.`count`) as total, sum(s.`price`*s.`count`)*{$percent} as percent, sum(s.`count`) as units_sold {$query} \n\t\t\t\t\t\t\t\t\t\t\t\t FROM `statistics` s {$join} \n\t\t\t\t\t\t\t\t\t\t\t\t WHERE s.`event` = 'payment' {$where} \n\t\t\t\t\t\t\t\t\t\t\t\t GROUP BY {$groupBy} {$orderBy}"); } else { $statisticsSIDs = SJB_DB::query("SELECT {$groupBy} as sid, sum(s.`price`*s.`count`) as total FROM `statistics` s {$join} WHERE s.`event` = 'payment' {$where} GROUP BY {$groupBy} ORDER BY total DESC LIMIT 10"); foreach ($statisticsSIDs as $info) { $SIDs[] = "'" . $info['sid'] . "'"; } if (isset($SIDs)) { $SIDs = implode(',', $SIDs); $statisticsInfo = SJB_DB::query("SELECT s.*, sum(s.`price`*s.`count`) as total, sum(s.`price`*s.`count`)*{$percent} as percent, sum(s.`count`) as units_sold {$query} \n\t\t\t\t\t\t\t\t\t\t\t\t FROM `statistics` s {$join} \n\t\t\t\t\t\t\t\t\t\t\t\t WHERE {$groupBy} in ({$SIDs}) AND s.`event` = 'payment' {$where} \n\t\t\t\t\t\t\t\t\t\t\t\t GROUP BY {$groupBy} {$orderBy}"); $ohter = SJB_DB::query("SELECT s.*, sum(s.`price`*s.`count`) as total, sum(s.`price`*s.`count`)*{$percent} as percent, sum(s.`count`) as units_sold, 'Other' as other {$query} FROM `statistics` s {$join} WHERE {$groupBy} not in ({$SIDs}) AND s.`event` = 'payment' {$where}"); if (!empty($ohter[0]['sid'])) { $statisticsInfo = array_merge($statisticsInfo, $ohter); } } } $statisticsInfo = array_merge($statisticsInfo, $totalSum); $statistics = array(); foreach ($statisticsInfo as $key => $statisticInfo) { if ($filter == 'sid') { $productInfo = SJB_ProductsManager::getProductInfoBySID($statisticInfo['sid']); $statisticInfo['product_type'] = SJB_ProductsManager::getProductTypeByID($productInfo['product_type']); } $statistics[$key] = $statisticInfo; if (isset($statisticInfo['other'])) { $statistics[$key]['generalColumn'] = 'Other'; } elseif (isset($statisticInfo['totalSum'])) { $statistics[$key]['generalColumn'] = 'Total'; $statistics[$key]['name'] = 'Total'; $statistics[$key]['percent'] = '100%'; } elseif (strstr($filter, 'userGroup_')) { if ($userGroupID == 'Employer') { $statistics[$key]['generalColumn'] = !empty($statisticInfo['CompanyName']) ? $statisticInfo['CompanyName'] : $statisticInfo['username']; } else { $statistics[$key]['generalColumn'] = !empty($statisticInfo['FirstName']) && !empty($statisticInfo['LastName']) ? $statisticInfo['FirstName'] . " " . $statisticInfo['LastName'] : $statisticInfo['username']; } } elseif ($filter == 'sid') { $statistics[$key]['generalColumn'] = $statisticInfo['name']; } else { $statistics[$key]['generalColumn'] = $statisticInfo[$filter]; } $statistics[$key]['percent'] = round($statistics[$key]['percent'], 2); if ($statistics[$key]['percent'] == 99.98999999999999) { $statistics[$key]['percent'] = 100; } } return $statistics; }
/** * prepare template variables for Product emails */ private function prepareTplVarsForProduct() { $productTypes = array('post_listings', 'access_listings', 'mixed_product', 'featured_user', 'banners', 'custom_product'); foreach ($productTypes as $key => &$productType) { $products = SJB_ProductsManager::getProductsByProductType($productType); if (!empty($products)) { $productInfo = array_pop($products); $productExtraInfo = SJB_ProductsManager::getProductExtraInfoBySID($productInfo['sid']); $productInfo = array_merge($productInfo, $productExtraInfo); $productType = array('id' => $productType, 'caption' => SJB_ProductsManager::getProductTypeByID($productType)); $fields = SJB_ProductsManager::createTemplateStructureForProductForEmailTpl($productInfo); $fields = array_merge($fields, $productExtraInfo); unset($fields['METADATA']); $productType['fields'] = $this->echoVars($fields); } else { unset($productTypes[$key]); } } $this->tp->assign('productTypes', $productTypes); }