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;
}
