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