function getData($key, $get_user_count = false)
{
global $timeframe, $view_level, $vertical_id, $mem, $QUERY, $cache_expire, $checks, $sql, $user_checks, $year;
if (i($QUERY, 'no_cache')) {
$data = array();
} else {
return $mem->get("Infogen:index/data#{$timeframe},{$view_level},{$vertical_id},{$key}");
}
if ($key == 'vertical') {
$data = getFromBothTables("IQ.vid as id,IQ.vname as name, %amount%", "users\n\t\t\t\t\tINNER JOIN\n\t\t\t\t\t(SELECT U.id as uid,U.name,V.id as vid,V.name as vname FROM makeadiff_madapp.User U\n\t\t\t\t\tINNER JOIN makeadiff_madapp.UserGroup UG\n\t\t\t\t\tON UG.user_id = U.id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.`Group` G\n\t\t\t\t\tON G.id = UG.group_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Vertical V\n\t\t\t\t\tON V.id = G.vertical_id\n\t\t\t\t\tWHERE U.status = 1 AND U.user_type = 'volunteer' AND UG.year = {$year} AND (G.type = 'national' OR G.type = 'strat' OR G.type = 'fellow')\n\t\t\t\t\tGROUP BY U.id,V.id)IQ\n\t\t\t\t\tON IQ.uid = users.madapp_user_id\n\t\t\t\t\t%donation_table%", "IQ.vid");
} elseif ($key == 'nt') {
$data = getFromBothTables("users.id,CONCAT(users.first_name, ' ', users.last_name) AS name, %amount%", "\n\t\t\t\t\tusers\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.UserGroup UG ON UG.user_id = users.madapp_user_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Group G on G.id = UG.group_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Vertical V on V.id = G.vertical_id\n\t\t\t\t\t%donation_table%", "users.id", "AND (G.type ='national' OR G.type = 'strat') AND R.id=9 AND UG.year = {$year}");
} elseif ($key == 'fellow') {
$data = getFromBothTables("users.id,CONCAT(users.first_name, ' ', users.last_name) AS name, %amount%", "\n\t\t\t\t\tusers\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.UserGroup UG ON UG.user_id = users.madapp_user_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Group G on G.id = UG.group_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Vertical V on V.id = G.vertical_id\n\t\t\t\t\t%donation_table%", "users.id", "AND (G.type = 'fellow') AND R.id=9 AND UG.year = {$year}");
} elseif ($key == 'coach') {
$data = getFromBothTables("manager.id,CONCAT(manager.first_name, ' ', manager.last_name) AS name, %amount%", "\n\t\t\t\t\tusers\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.UserGroup UG ON UG.user_id = users.madapp_user_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Group G on G.id = UG.group_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Vertical V on V.id = G.vertical_id\n\t\t\t\t\t%donation_table%", "manager.id", "AND (G.type = 'fellow') AND R.id=9 AND UG.year = {$year}");
} elseif ($key == 'volunteer') {
$data = getFromBothTables("users.id,CONCAT(users.first_name, ' ', users.last_name) AS name, %amount%", "\n\t\t\t\t\tusers\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.UserGroup UG ON UG.user_id = users.madapp_user_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Group G on G.id = UG.group_id\n\t\t\t\t\tINNER JOIN makeadiff_madapp.Vertical V on V.id = G.vertical_id\n\t\t\t\t\t%donation_table%", "users.id", "AND (G.type = 'fellow') AND R.id=9 AND UG.year = {$year}");
}
$mem->set("Infogen:index/data#{$timeframe},{$view_level},{$vertical_id},{$key}", $data, $cache_expire);
return $data;
}
function getData($key, $get_user_count = false)
{
global $timeframe, $view_level, $state_id, $city_id, $group_id, $mem, $QUERY, $cache_expire, $checks, $sql, $user_checks;
if (i($QUERY, 'no_cache')) {
$data = array();
} else {
return $mem->get("Infogen:index/data#{$timeframe},{$view_level},{$state_id},{$city_id},{$group_id},{$key}");
}
if ($key == 'region') {
$data = getFromBothTables("S.id,S.name, %amount%", "states S\n\t\t\t\t\tINNER JOIN cities C ON C.state_id=S.id\n\t\t\t\t\tINNER JOIN users ON users.city_id=C.id\n\t\t\t\t\t%donation_table%", "S.id");
//To get the number of users who have donated above Rs. 12,000
$user_data = getFromBothTables("users.id,%amount%, S.id as state_id", "states S\n\t\t\t\t\tINNER JOIN cities C ON C.state_id=S.id\n\t\t\t\t\tINNER JOIN users ON users.city_id=C.id\n\t\t\t\t\t%donation_table%", "users.id", "", false);
foreach ($data as $key => $row) {
$data[$key]['user_count_12k'] = 0;
}
foreach ($user_data as $row) {
if ($row['amount'] >= 12000) {
$data[$row['state_id']]['user_count_12k']++;
}
}
$user_count_data = $sql->getById("SELECT S.id, COUNT(users.id) AS count\n\t\t\t\tFROM users\n\t\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\t\tINNER JOIN states S ON S.id=C.state_id\n\t\t\t\tWHERE " . implode(" AND ", $user_checks) . " GROUP BY S.id");
foreach ($data as $key => $row) {
$data[$key]['user_count'] = $user_count_data[$row['id']];
}
} elseif ($key == 'city') {
$data = getFromBothTables("C.id,C.name, %amount%", "cities C\n\t\t\t\t\tINNER JOIN users ON city_id=C.id\n\t\t\t\t\t%donation_table%", "C.id");
//To get the number of users who have donated above Rs. 12,000
$user_data = getFromBothTables("users.id,%amount%, C.id as city_id", "cities C\n\t\t\t\t\tINNER JOIN users ON users.city_id=C.id\n\t\t\t\t\t%donation_table%", "users.id", "", false);
foreach ($data as $key => $row) {
$data[$key]['user_count_12k'] = 0;
}
foreach ($user_data as $row) {
if ($row['amount'] >= 12000 && !empty($data[$row['city_id']])) {
$data[$row['city_id']]['user_count_12k']++;
}
}
$user_count_data = $sql->getById("SELECT C.id, COUNT(users.id) AS count\n\t\t\t\tFROM users\n\t\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\t\tWHERE " . implode(" AND ", $user_checks) . " GROUP BY C.id");
foreach ($data as $key => $row) {
$data[$key]['user_count'] = $user_count_data[$row['id']];
}
} elseif ($key == 'group') {
$data = getFromBothTables("G.id,G.name, %amount%", "users\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users manager ON manager.id=RT.manager_id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN groups G ON G.id=manager.group_id\n\t\t\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\t\t\t%donation_table%", "G.id", "AND R.id=9");
$user_data = getFromBothTables("users.id, %amount%, G.id as group_id", "users\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users manager ON manager.id=RT.manager_id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN groups G ON G.id=manager.group_id\n\t\t\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\t\t\t%donation_table%", "G.id", "AND R.id=9", false);
foreach ($data as $key => $row) {
$data[$key]['user_count_12k'] = 0;
}
foreach ($user_data as $row) {
if ($row['amount'] >= 12000 && !empty($data[$row['group_id']])) {
$data[$row['group_id']]['user_count_12k']++;
}
}
$user_count_data = $sql->getById("SELECT G.id, COUNT(users.id) AS count\n\t\t\tFROM users\n\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\tINNER JOIN users manager ON manager.id=RT.manager_id\n\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\tINNER JOIN groups G ON G.id=manager.group_id\n\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\tWHERE R.id=9 AND " . implode(" AND ", $user_checks) . " GROUP BY G.id");
foreach ($data as $key => $row) {
$data[$key]['user_count'] = $user_count_data[$row['id']];
}
} elseif ($key == 'coach') {
$data = getFromBothTables("manager.id,CONCAT(manager.first_name, ' ', manager.last_name) AS name, %amount%", "users\n\t\t\t\t\t%donation_table%\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id \n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN cities C ON C.id=users.city_id", "manager.id", "AND R.id=9");
$user_data = getFromBothTables("users.id, %amount%, manager.id as manager_id", "users\n\t\t\t\t\t%donation_table%\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\tINNER JOIN cities C ON C.id=users.city_id", "users.id", "AND R.id=9", false);
foreach ($data as $key => $row) {
$data[$key]['user_count_12k'] = 0;
}
foreach ($user_data as $row) {
if ($row['amount'] >= 12000 && !empty($data[$row['manager_id']])) {
$data[$row['manager_id']]['user_count_12k']++;
}
}
$user_count_data = $sql->getById("SELECT manager.id, COUNT(users.id) AS count\n\t\t\tFROM users\n\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id\n\t\t\tINNER JOIN users manager ON manager.id=RT.manager_id\n\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\tINNER JOIN cities C ON C.id=users.city_id\n\t\t\tWHERE R.id=9 AND " . implode(" AND ", $user_checks) . " GROUP BY manager.id");
foreach ($data as $key => $row) {
$data[$key]['user_count'] = $user_count_data[$row['id']];
}
} elseif ($key == 'user') {
$data = getFromBothTables("users.id,CONCAT(users.first_name, ' ', users.last_name) AS name, %amount%", "users \n\t\t\t\t\tINNER JOIN cities C ON users.city_id=C.id\n\t\t\t\t\tINNER JOIN reports_tos RT ON RT.user_id=users.id \n\t\t\t\t\tINNER JOIN users AS manager ON RT.manager_id=manager.id\n\t\t\t\t\tINNER JOIN user_role_maps RM ON RM.user_id=manager.id\n\t\t\t\t\tINNER JOIN roles R ON R.id=RM.role_id\n\t\t\t\t\t%donation_table%", "users.id", "AND R.id=9");
}
$mem->set("Infogen:index/data#{$timeframe},{$view_level},{$state_id},{$city_id},{$group_id},{$key}", $data, $cache_expire);
return $data;
}
