public function execute() { $tp = SJB_System::getTemplateProcessor(); $action = SJB_Request::getVar('action', 'search'); $template = SJB_Request::getVar('template', 'sales.tpl'); $errors = array(); $userGroups = SJB_UserGroupManager::getAllUserGroupsIDsAndCaptions(); foreach ($userGroups as $key => $userGroup) { unset($userGroups[$key]); $userGroups[$userGroup['id']] = $userGroup; } switch ($action) { case 'export': $period = SJB_Request::getVar('period', array()); $filter = SJB_Request::getVar('filter', false); $sorting_field = SJB_Request::getVar('sorting_field', 'total'); $sorting_order = SJB_Request::getVar('sorting_order', 'DESC'); $statistics = array(); if ($filter) { $statistics = SJB_Statistics::getSalesStatistics($period, $filter, $sorting_field, $sorting_order); if (!empty($statistics['errors'])) { $errors = $statistics['errors']; } } else { $errors[] = 'EMPTY_PARAMETER'; } $columnTitle = ''; if (strstr($filter, 'userGroup_')) { $userGroupSID = str_replace('userGroup_', '', $filter); if ($userGroups[$userGroupSID]['key'] == 'Employer') { $columnTitle = 'Company Name'; } else { $columnTitle = $userGroups[$userGroupSID]['caption'] . ' Name'; } $exportProperties['generalColumn'] = $columnTitle; } elseif ($filter == 'sid') { $columnTitle = 'Product Name'; $exportProperties['generalColumn'] = 'Product Name'; $exportProperties['product_type'] = 'Product Type'; $tp->assign('link', 'product'); } else { $fieldInfo = SJB_ListingFieldDBManager::getListingFieldInfoByID($filter); $exportProperties['generalColumn'] = $fieldInfo['caption']; } $exportProperties['units_sold'] = 'Units Sold'; $exportProperties['total'] = 'Income'; $exportProperties['percent'] = '%'; if (!$errors && $statistics) { $type = SJB_Request::getVar('type', 'csv'); SJB_StatisticsExportController::createExportDirectory(); switch ($type) { case 'csv': $exportData = SJB_StatisticsExportController::getSalesExportData($statistics, $exportProperties); $fileName = 'sales.csv'; SJB_StatisticsExportController::makeCSVExportFile($exportData, $fileName, 'App & Views'); SJB_StatisticsExportController::archiveAndSendExportFile('sales', 'csv'); break; case 'xls': $exportData = SJB_StatisticsExportController::getSalesExportData($statistics, $exportProperties); $fileName = 'sales.xls'; SJB_StatisticsExportController::makeXLSExportFile($exportData, $fileName, 'App & Views'); SJB_StatisticsExportController::archiveAndSendExportFile('sales', 'xls'); break; } break; } case 'search': $search = SJB_Request::getVar('search', false); $period = SJB_Request::getVar('period', array()); $filter = SJB_Request::getVar('filter', false); $sorting_field = SJB_Request::getVar('sorting_field', 'total'); $sorting_order = SJB_Request::getVar('sorting_order', 'DESC'); $statistics = array(); if ($search) { if (!empty($period['from']) && !empty($period['to']) && strtotime($period['from']) > strtotime($period['to'])) { $errors[] = 'SELECTED_PERIOD_IS_INCORRECT'; } else { if ($filter) { $statistics = SJB_Statistics::getSalesStatistics($period, $filter, $sorting_field, $sorting_order); if (!empty($statistics['errors'])) { $errors = $statistics['errors']; } } else { $errors[] = 'EMPTY_PARAMETER'; } } } $columnTitle = ''; if (strstr($filter, 'userGroup_')) { $userGroupSID = str_replace('userGroup_', '', $filter); if ($userGroups[$userGroupSID]['key'] == 'Employer') { $columnTitle = 'Company Name'; } else { $columnTitle = $userGroups[$userGroupSID]['caption'] . ' Name'; } $tp->assign('link', 'user'); } else { if ($filter == 'sid') { $columnTitle = 'Product Name'; $tp->assign('link', 'product'); } else { if (in_array($filter, array('Location_Country', 'Location_State', 'Location_City'))) { $fieldInfo = SJB_ListingFieldDBManager::getLocationFieldsInfoById($filter); } else { $fieldInfo = SJB_ListingFieldDBManager::getListingFieldInfoByID($filter); } $columnTitle = $fieldInfo['caption']; } } $i18n = SJB_I18N::getInstance(); $periodView = array(); foreach ($period as $key => $value) { $periodView[$key] = $i18n->getInput('date', $period[$key]); } $tp->assign('search', $search); $tp->assign('filter', $filter); $tp->assign('columnTitle', $columnTitle); $tp->assign('period', $period); $tp->assign('periodView', $periodView); $tp->assign('statistics', $statistics); $tp->assign('countResult', count($statistics)); $tp->assign('sorting_field', $sorting_field); $tp->assign('sorting_order', $sorting_order); break; } $tp->assign('userGroups', $userGroups); $tp->assign('errors', $errors); $tp->assign('action', $action); $tp->display($template); }
public function execute() { $tp = SJB_System::getTemplateProcessor(); $action = SJB_Request::getVar('action', 'search'); $template = SJB_Request::getVar('template', 'listings_statistics.tpl'); $errors = array(); $userGroups = SJB_UserGroupManager::getAllUserGroupsIDsAndCaptions(); foreach ($userGroups as $key => $userGroup) { unset($userGroups[$key]); $userGroups[$userGroup['id']] = $userGroup; } switch ($action) { case 'export': $period = SJB_Request::getVar('period', array()); $filter = SJB_Request::getVar('filter', false); $listingTypeSID = SJB_Request::getVar('listingTypeSID', false); $listingTypeID = SJB_ListingTypeManager::getListingTypeIDBySID($listingTypeSID); $sorting_field = SJB_Request::getVar('sorting_field', 'total'); $sorting_order = SJB_Request::getVar('sorting_order', 'DESC'); $statistics = array(); if ($filter) { $statistics = SJB_Statistics::getListingsStatistics($period, $listingTypeSID, $filter, $sorting_field, $sorting_order); if (!empty($statistics['errors'])) { $errors = $statistics['errors']; } } else { $errors[] = 'EMPTY_PARAMETER'; } $columnTitle = ''; if (strstr($filter, 'userGroup_')) { $userGroupSID = str_replace('userGroup_', '', $filter); if ($userGroups[$userGroupSID]['key'] == 'Employer') { $columnTitle = 'Company Name'; } else { $columnTitle = $userGroups[$userGroupSID]['caption'] . ' Name'; } } else { $fieldInfo = SJB_ListingFieldDBManager::getListingFieldInfoByID($filter); $columnTitle = $fieldInfo['caption']; } if (!$errors && $statistics) { $type = SJB_Request::getVar('type', 'csv'); $listingTypes = SJB_ListingTypeManager::getListingAllTypesForListType(); SJB_StatisticsExportController::createExportDirectory(); $exportProperties['title'] = $columnTitle; $exportProperties['regular'] = ''; if ($listingTypeID == 'Job') { $exportProperties['featured'] = ''; } $exportProperties['priority'] = ''; $exportProperties['total'] = 'Total'; $exportProperties['percent'] = '%'; foreach ($listingTypes as $listingType) { if ($listingType['id'] == $listingTypeSID) { switch ($listingType['key']) { case 'Job': $featuredTitle = "Number of Featured {$listingType['key']}s Posted"; $exportProperties['featured'] = $featuredTitle; case 'Resume': $regularTitle = "Number of Regular {$listingType['key']}s Posted"; $exportProperties['regular'] = $regularTitle; $priorityTitle = "Number of Priority {$listingType['key']}s Posted"; $exportProperties['priority'] = $priorityTitle; break; default: $regularTitle = 'Number of Regular "' . $listingType['caption'] . '" Listings Posted'; $exportProperties['regular'] = $regularTitle; $priorityTitle = 'Number of Priority "' . $listingType['caption'] . '" Listings Posted'; $exportProperties['priority'] = $priorityTitle; break; } } } switch ($type) { case 'csv': $exportData = SJB_StatisticsExportController::getListingExportData($statistics, $listingTypeID); $fileName = strtolower($listingTypeID) . '_statistics.csv'; SJB_StatisticsExportController::makeCSVExportFile($exportData, $fileName, "{$listingTypeID} Statistics"); SJB_StatisticsExportController::archiveAndSendExportFile(strtolower($listingTypeID) . '_statistics', 'csv'); break; case 'xls': $exportData = SJB_StatisticsExportController::getListingExportData($statistics, $listingTypeID); $fileName = strtolower($listingTypeID) . '_statistics.xls'; SJB_StatisticsExportController::makeXLSExportFile($exportData, $fileName, "{$listingTypeID} Statistics"); SJB_StatisticsExportController::archiveAndSendExportFile(strtolower($listingTypeID) . '_statistics', 'xls'); break; } break; } case 'search': $search = SJB_Request::getVar('search', false); $period = SJB_Request::getVar('period', array()); $filter = SJB_Request::getVar('filter', false); $listingTypeSID = SJB_Request::getVar('listingTypeSID', false); $sorting_field = SJB_Request::getVar('sorting_field', 'total'); $sorting_order = SJB_Request::getVar('sorting_order', 'DESC'); $statistics = array(); if ($search) { $i18n = SJB_I18N::getInstance(); $from = $i18n->getInput('date', $period['from']); $to = $i18n->getInput('date', $period['to']); if (!empty($period['from']) && !empty($period['to']) && strtotime($from) > strtotime($to)) { $errors[] = 'SELECTED_PERIOD_IS_INCORRECT'; } else { if ($filter) { $statistics = SJB_Statistics::getListingsStatistics($period, $listingTypeSID, $filter, $sorting_field, $sorting_order); if (!empty($statistics['errors'])) { $errors = $statistics['errors']; } } else { $errors[] = 'EMPTY_PARAMETER'; } } } $columnTitle = ''; if (strstr($filter, 'userGroup_')) { $userGroupSID = str_replace('userGroup_', '', $filter); if ($userGroups[$userGroupSID]['key'] == 'Employer') { $columnTitle = 'Company Name'; } else { $columnTitle = $userGroups[$userGroupSID]['caption'] . ' Name'; } $tp->assign('link', 'user'); } else { if (in_array($filter, array('Location_Country', 'Location_State', 'Location_City'))) { $fieldInfo = SJB_ListingFieldDBManager::getLocationFieldsInfoById($filter); } else { $fieldInfo = SJB_ListingFieldDBManager::getListingFieldInfoByID($filter); } $columnTitle = $fieldInfo['caption']; } $i18n = SJB_I18N::getInstance(); $periodView = array(); foreach ($period as $key => $value) { $periodView[$key] = $i18n->getInput('date', $period[$key]); } $tp->assign('filter', $filter); $tp->assign('search', $search); $tp->assign('columnTitle', $columnTitle); $tp->assign('listingTypeSID', $listingTypeSID); $tp->assign('period', $period); $tp->assign('periodView', $periodView); $tp->assign('statistics', $statistics); $tp->assign('countResult', count($statistics)); $tp->assign('sorting_field', $sorting_field); $tp->assign('sorting_order', $sorting_order); break; } $listingTypes = SJB_ListingTypeManager::getListingAllTypesForListType(); $products = SJB_ProductsManager::getAllProductsInfo(); $acl = SJB_Acl::getInstance(); foreach ($listingTypes as $key => $listingType) { $userGroup = array(); foreach ($products as $productInfo) { if ($acl->isAllowed('post_' . strtolower($listingType['key']), $productInfo['sid'], 'product') && !in_array($productInfo['user_group_sid'], $userGroup)) { $userGroup[] = $productInfo['user_group_sid']; } } $listingTypes[$listingType['id']] = $listingType; $listingTypes[$listingType['id']]['userGroups'] = $userGroup; unset($listingTypes[$key]); } $tp->assign('userGroups', $userGroups); $tp->assign('listingTypes', $listingTypes); $tp->assign('errors', $errors); $tp->assign('action', $action); $tp->display($template); }
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; }