File: /home/cafsindia/crm_cafsindia_com/application/models/Homemodel.php
<?php
class Homemodel extends CI_Model{
public function change_password($emp_id,$old_password,$new_password,$confirm_new_password){
$exist = $this->is_exist($emp_id,$old_password);
if($exist !== 0){
$this->db->where('id',$emp_id);
return $this->db->update('employees',array('password'=>$new_password,'upd_no'=>date('Y-m-d h:i:s')));
}else{
return false;
}
}
public function is_exist($emp_id,$old_password){
$this->db->from('employees');
$this->db->where('id',$emp_id);
$this->db->where('password',$old_password);
$this->db->where('deleted',0);
return $this->db->get()->num_rows();
}
//MIS SAT START 09aug2018
public function mis_life_chart($category,$type){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
$date = "";
if($type === "M"){
$date = "and login_date like '$month%'";
}else
if($type === "FY"){
$date = "and DATE_FORMAT(login_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($type === "LTD"){
$date = "";
}
$life_chart_info = $this->db->query("SELECT sum(overall_net_premium) as tot_count,product_category,vendorcompanyname FROM ospos_leads_product INNER join ospos_vendor on vendor_id = company where ospos_leads_product.status = '1' and product_category = '$category' $date GROUP by company ORDER BY `tot_count` DESC");
$life_chart_rslt = $life_chart_info->result_array();
return $life_chart_rslt;
}
public function mis_rm_chart($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rslt = array();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on (ospos_lead_type_info.created_by = ospos_employees.id or ospos_lead_type_info.rm_name = ospos_employees.id) left join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and role = '5' and deleted = '0' and ospos_leads_product.status = '1' GROUP by reporting,id ORDER BY `netamount` DESC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach($rm_rslt as $key => $value){
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$reporting' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manage = $rm_manager_rslt->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and ospos_lead_type_info.status = '1' and mf_investment != '2' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '2'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
$rows['data'][] = array($first_name, $netamount);
$rslt = array();
array_push($rslt,$rows);
}
}
return $rslt;
}
public function mis_health_chart($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rslt = array();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
$ht_tl_info = $this->db->query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and deleted = '0' and category = '3' ORDER BY ospos_employees.reporting ASC");
$ht_tl_rslt = $ht_tl_info->result();
$ht_tl_list = "";
foreach ($ht_tl_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$team_info = $this->db->query("SELECT GROUP_CONCAT(id) as team_ids FROM `ospos_employees` WHERE reporting = '$id'");
$team_rslt = $team_info->row();
$team_ids = $team_rslt->team_ids.",".$id;
$lead_info = $this->db->query("SELECT ospos_leads_product.company,vendorcompanyname,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id inner join ospos_happy_calling on ospos_happy_calling.call_product_id = ospos_leads_product.customer_product_id left join ospos_vendor on vendor_id = ospos_leads_product.company where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.company");
$lead_rslt = $lead_info->result();
$star_amt = 0;
$nj_amt = 0;
$other_amt = 0;
foreach ($lead_rslt as $key => $value){
$company = $value->vendorcompanyname;
$netamount = $value->netamount;
if($company === "Star Health"){
$star_amt += $netamount;
}else
if($company === "NJ"){
$nj_amt += $netamount;
}else{
$other_amt += $netamount;
}
}
$net = $star_amt + $nj_amt + $other_amt;
$rows['data'][] = array($first_name, $net);
$rslt = array();
array_push($rslt,$rows);
}
return $rslt;
}
public function issuance_chart($category,$date){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($date === "M"){
$date = "where ospos_happy_calling.login_date like '$month%'";
}else
if($date === "FY"){
$date = "where DATE_FORMAT(ospos_happy_calling.login_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($date === "LTD"){
$date = "";
}
$issuance_chart_info = $this->db->query("SELECT issuance_status,IFNULL(sum(overall_net_premium),0) as premium FROM `ospos_happy_calling` left join ospos_leads_product on call_product_id = customer_product_id $date and ospos_leads_product.status = '1' and product_category ='$category' GROUP by issuance_status");
$issuance_chart_rslt = $issuance_chart_info->result_array();
return $issuance_chart_rslt;
}
public function issuance_chart_drill($issuance_status,$category,$date){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($date === "M"){
$qry = "where ospos_happy_calling.login_date like '$month%'";
}else
if($date === "FY"){
$qry = "where DATE_FORMAT(ospos_happy_calling.login_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($date === "LTD"){
$qry = "";
}
$issuance_chart_info = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as doc_premium,document_issue_status FROM `ospos_happy_calling` left join ospos_leads_product on call_product_id = customer_product_id $qry and product_category = '$category' and issuance_status = '$issuance_status' GROUP by document_issue_status");
$issuance_chart_rslt = $issuance_chart_info->result_array();
return $issuance_chart_rslt;
}
public function renew_chart($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$qry = "";
if($category === "2"){
$qry = "and log_renew_status = 'Paid' and log_client_status = 'Closed'";
}else
if($category === "3"){
$qry = "and (log_renew_status = 'Renewed' or log_client_status = '')";
}
$paid_chart_info = $this->db->query("SELECT sum(log_overall_total_premium) as premium,log_renew_status FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and log_renewal_date between '$start_date' and '$end_date' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) $qry");
$paid_chart_rslt = $paid_chart_info->result_array();
$renew_chart_info = $this->db->query("SELECT sum(ren_overall_total_premium) as premium,renew_status as log_renew_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' and renew_status != 'Paid' GROUP BY renew_status");
$renew_chart_rslt = $renew_chart_info->result_array();
$result = array_merge($paid_chart_rslt , $renew_chart_rslt);
return $result;
}
public function renew_chart_drill($renew_status,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
if($renew_status === "Paid"){
$renew_chart_drill = $this->db->query("SELECT sum(log_overall_total_premium) as premium,log_client_status FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and log_renew_status = '$renew_status' and ospos_renewal.status = '1' and log_renewal_date between '$start_date' and '$end_date' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) GROUP BY log_client_status");
$drill_rslt = $renew_chart_drill->result_array();
}else{
$renew_chart_drill = $this->db->query("SELECT sum(ren_overall_total_premium) as premium,client_status as log_client_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' and renew_status != 'Paid' and renew_status = '$renew_status' GROUP BY client_status");
$drill_rslt = $renew_chart_drill->result_array();
}
return $drill_rslt;
}
public function renew_chart_drill2($client_status,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
//$renew_chart_drill2 = "";
if($client_status === "Intrested"){
$renew_chart_drill2 = $this->db->query("SELECT sum(ren_overall_total_premium) as premium,premium_status as log_premium_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' and renew_status != 'Paid' and client_status = '$client_status' GROUP BY premium_status");
$drill2_rslt = $renew_chart_drill2->result_array();
return $drill2_rslt;
}else
if($client_status === "NotIntrested"){
$renew_chart_drill2 = $this->db->query("SELECT sum(ren_overall_total_premium) as premium,renew_reason as log_premium_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' and renew_status != 'Paid' and client_status = '$client_status' GROUP BY renew_reason");
$drill2_rslt = $renew_chart_drill2->result_array();
return $drill2_rslt;
}else
if($client_status === "NotContactable"){
$renew_chart_drill2 = $this->db->query("SELECT sum(ren_overall_total_premium) as premium,not_contact_reason as log_premium_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' and renew_status != 'Paid' and client_status = '$client_status' GROUP BY not_contact_reason");
$drill2_rslt = $renew_chart_drill2->result_array();
return $drill2_rslt;
}
}
public function rmm_team_chart($date,$emp_id){
$cur_month = date("Y-m");
$rslt = array();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_leads_product on lead_type_id = lead_info_id where role = '5' and deleted = '0' and ospos_lead_type_info.lead_mode = '1' and ospos_leads_product.status = '1' and reporting in ($emp_id) GROUP by id ORDER BY `netamount` ASC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach($rm_rslt as $key => $value){
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$reporting' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manage = $rm_manager_rslt->first_name;
$lead_info = $this->db->query("SELECT ospos_lead_type_info.created_by, ospos_lead_type_info.rm_name,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where login_date like '$cur_month%' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and ospos_leads_product.product_category = '2'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value) {
$netamount = $value->netamount;
$rows['data'][] = array($first_name, $netamount);
$rslt = array();
array_push($rslt,$rows);
}
}
return $rslt;
}
//CRM Chart
public function crm_chart($emp_id,$emp_role,$emp_cat){
$emp_cat = $emp_cat;
$emp_id = $emp_id;
$role_id = $emp_role;
if($role_id === "6"){
$id = $emp_id;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where created_by in ($emp_id) and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.lead_status != '3' GROUP by lead_status");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function crm_chart_drill($lead_status,$emp_id,$emp_role,$emp_cat){
$emp_cat = $emp_cat;
$emp_id = $emp_id;
$role_id = $emp_role;
if($role_id === "6"){
$id = $emp_id;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where created_by in ($emp_id) and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.lead_status != '3' and lead_status = '$lead_status' GROUP by prospect_level");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function tl_crm_met_chart($emp_id_all,$role,$emp_cat){
$month = date('Y-m');
$emp_cat = $emp_cat;
$emp_id_all = $emp_id_all;
$role_id = $role;
if($role_id === "6"){
$id = $emp_id_all;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
$tl_crm_met_chart_info = $this->db->query("SELECT count(*) as lead_count,first_name as crm_name FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status inner join ospos_people on ospos_lead_type_info.created_by=ospos_people.person_id inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.category = '2' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_met_log.met_date like '%$month%' and first_met='1' and ospos_lead_type_info.lead_status = '9' GROUP by lead_status,ospos_lead_type_info.created_by");
$tl_crm_met_chart_rslt = $tl_crm_met_chart_info->result_array();
return $tl_crm_met_chart_rslt;
}
public function tl_rm_met_chart($emp_id_all,$role,$emp_cat){
$month = date('Y-m');
$emp_cat = $emp_cat;
$emp_id_all = $emp_id_all;
$role_id = $role;
if($role_id === "6"){
$id = $emp_id_all;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
$tl_rm_met_chart_info = $this->db->query("SELECT count(*) as lead_count,first_name as crm_name FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status inner join ospos_people on ospos_lead_type_info.rm_name=ospos_people.person_id inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.category = '2' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_met_log.met_date like '%$month%' and first_met='1' and ospos_lead_type_info.lead_status = '9' GROUP by lead_status,ospos_lead_type_info.rm_name");
$tl_rm_met_chart_rslt = $tl_rm_met_chart_info->result_array();
return $tl_rm_met_chart_rslt;
}
public function healthteamperformance($emp_id_all,$role,$emp_cat){
$month = date('Y-m');
$emp_cat = $emp_cat;
$emp_id_all = $emp_id_all;
$role_id = $role;
if($role_id === "6"){
$id = $emp_id_all;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers;
}else{
$map_crm_query=$this->db->query("select teammembers from ospos_team where FIND_IN_SET($emp_id_all ,teammembers)");
$map_crm_result = $map_crm_query->result_array();
$emp_id = $map_crm_result[0]['teammembers'];
}
$healthteamperformance_info = $this->db->query("SELECT first_name as crm_name, IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_info_id = lead_type_id inner join ospos_people on ospos_lead_type_info.created_by=ospos_people.person_id where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($emp_id) and ospos_leads_product.status = '1' and ospos_lead_type_info.prospect_level = '4' and ospos_lead_type_info.ncd like '$month%' and ospos_lead_type_info.category in ($emp_cat) group by ospos_lead_type_info.created_by");
$healthteamperformance_rslt = $healthteamperformance_info->result_array();
return $healthteamperformance_rslt;
}
//RM Chart
public function get_rm_val_chart($type){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($type === "M"){
$date = "and ospos_lead_type_info.created_date like '$month%'";
}else
if($type === "FY"){
$date = "and DATE_FORMAT(ospos_lead_type_info.created_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($type === "LTD"){
$date = "";
}
$emp_cat = $this->session->userdata('emp_category');
$emp_id = $this->session->userdata('emp_id');
$role_id = $this->session->userdata('emp_role');
$rm_chart_info = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where (created_by in ($emp_id) or rm_name in($emp_id)) and ospos_lead_type_info.category = '$emp_cat' $date and ospos_lead_type_info.lead_mode = '1' and ospos_lead_type_info.status = '1' and ospos_lead_type_info.lead_status != '3' GROUP by lead_status");
$rm_chart_rslt = $rm_chart_info->result_array();
return $rm_chart_rslt;
}
public function rm_chart_drill($type,$lead_status){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($type === "M"){
$date = "and ospos_lead_type_info.created_date like '$month%'";
}else
if($type === "FY"){
$date = "and DATE_FORMAT(ospos_lead_type_info.created_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($type === "LTD"){
$date = "";
}
$emp_cat = $this->session->userdata('emp_category');
$emp_id = $this->session->userdata('emp_id');
$role_id = $this->session->userdata('emp_role');
$rm_drill_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where (created_by in ($emp_id) or rm_name in($emp_id)) $date and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and ospos_lead_type_info.lead_mode = '1' and lead_status = '$lead_status' GROUP by prospect_level");
$rm_drill_rslt = $rm_drill_info->result_array();
return $rm_drill_rslt;
}
public function rm_ni_chart(){
$emp_id = $this->session->userdata('emp_id');
$rm_ni_chart_info = $this->db->query("SELECT ospos_renewal.renew_reason,IFNULL(sum(ospos_leads_product.net_premium),0) as notpaid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id where (ospos_lead_type_info.created_by in ($emp_id) or ospos_lead_type_info.created_by in ($emp_id)) and ospos_renewal.client_status = 'NotIntrested' GROUP BY ospos_renewal.renew_reason");
$rm_ni_chart_rslt = $rm_ni_chart_info->result_array();
return $rm_ni_chart_rslt;
}
public function rmm_ni_chart(){
$emp_id = $this->session->userdata('emp_id');
$role_id = $this->session->userdata('emp_role');
$rm_team = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where reporting = '$emp_id' and deleted = '0'");
$team_info = $rm_team->result_array();
$rm_team_ids = $team_info[0]['ids'];
$rm_team_count = $team_info[0]['count'];
$rmm_ni_chart_info = $this->db->query("SELECT ospos_renewal.renew_reason,IFNULL(sum(ospos_leads_product.net_premium),0) as notpaid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id where (ospos_lead_type_info.created_by in ($rm_team_ids) or ospos_lead_type_info.created_by in ($rm_team_ids)) and ospos_renewal.client_status = 'NotIntrested' GROUP BY ospos_renewal.renew_reason");
$rmm_ni_chart_rslt = $rmm_ni_chart_info->result_array();
return $rmm_ni_chart_rslt;
}
//RM Manager Chart
public function get_rm_chart($type,$emp_id,$emp_role,$emp_cat){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($type === "M"){
$date = "and ospos_lead_type_info.created_date like '$month%'";
}else
if($type === "FY"){
$date = "and DATE_FORMAT(ospos_lead_type_info.created_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($type === "LTD"){
$date = "";
}
$rm_chart_info = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where (created_by in ($emp_id) or rm_name in($emp_id)) and ospos_lead_type_info.category = '$emp_cat' $date and ospos_lead_type_info.lead_mode = '1' and ospos_lead_type_info.status = '1' and ospos_lead_type_info.lead_status != '3' GROUP by lead_status");
$rm_chart_rslt = $rm_chart_info->result_array();
return $rm_chart_rslt;
}
public function rmm_chart_drill($type,$lead_status,$emp_id,$emp_role,$emp_cat){
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_date = $year."-"."04"."-"."01";
$fin_end_date = $year2."-"."03"."-"."31";
}else{
$fin_start_date = $year1."-"."04"."-"."01";
$fin_end_date = $year."-"."03"."-"."31";
}
$month = date('Y-m');
if($type === "M"){
$date = "and ospos_lead_type_info.created_date like '$month%'";
}else
if($type === "FY"){
$date = "and DATE_FORMAT(ospos_lead_type_info.created_date, '%Y-%m-%d') between '$fin_start_date' and '$fin_end_date'";
}else
if($type === "LTD"){
$date = "";
}
$rm_drill_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where (created_by in ($emp_id) or rm_name in($emp_id)) $date and ospos_lead_type_info.lead_mode = '1' and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_status = '$lead_status' GROUP by prospect_level");
$rm_drill_rslt = $rm_drill_info->result_array();
return $rm_drill_rslt;
}
//Area Manager Chart
public function am_crm_chart($emp_id,$emp_cat,$emp_role){
if($emp_role === "6"){
$id = $emp_id;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
//echo "SAT $emp_id";
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where created_by in ($emp_id) and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.lead_status != '3' GROUP by lead_status");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function am_crm_chart_drill($lead_status,$emp_id,$emp_cat,$emp_role){
if($emp_role === "6"){
$id = $emp_id;
$team = $this->get_team_info($id);
$emp_id = $team->teammembers.",".$id;
}
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where created_by in ($emp_id) and ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and lead_status = '$lead_status' GROUP by prospect_level");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function get_team_info($id){
$this->db->select("GROUP_CONCAT(teammembers SEPARATOR ',') as teammembers");
$this->db->from('team');
$this->db->where("ospos_team.teamleader IN (".$id.")",NULL, false);
//$this->db->where_in('teamleader', $id);
$this->db->where('team.deleted',0);
return $this->db->get()->row();
//echo $this->db->last_query();
}
public function gm_lead_portfolio_chart($start_date,$end_date,$emp_cat){
$start_date=date("Y-m-d", strtotime($start_date));
$end_date=date("Y-m-d", strtotime($end_date));
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.lead_status != '3' and ospos_lead_type_info.created_date between '$start_date' and '$end_date' GROUP by lead_status");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function gm_lead_portfolio_chart_drill($lead_status,$start_date,$end_date,$emp_cat){
$start_date=date("Y-m-d", strtotime($start_date));
$end_date=date("Y-m-d", strtotime($end_date));
$emp_cat = $emp_cat;
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count FROM `ospos_lead_type_info` INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.lead_status != '3' and ospos_lead_type_info.created_date between '$start_date' and '$end_date' and lead_status = '$lead_status' GROUP by prospect_level");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function gm_lead_portfolio_rm_chart($start_date,$end_date,$emp_cat){
$start_date=date("Y-m-d", strtotime($start_date));
$end_date=date("Y-m-d", strtotime($end_date));
//$cur_month = date("Y-m");
$rslt = array();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_leads_product on lead_type_id = lead_info_id where role = '5' and deleted = '0' and ospos_lead_type_info.lead_mode = '1' and ospos_leads_product.status = '1' and ospos_employees.role='5' GROUP by id ORDER BY `netamount` ASC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach($rm_rslt as $key => $value){
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$lead_info = $this->db->query("SELECT ospos_lead_type_info.created_by, ospos_lead_type_info.rm_name,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where login_date between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and ospos_leads_product.product_category = '2'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value) {
$netamount = $value->netamount;
$rows['data'][] = array($first_name, $netamount);
$rslt = array();
array_push($rslt,$rows);
}
}
return $rslt;
}
public function gm_health_crm_chart($start_date,$end_date,$emp_cat){
$start_date=date("Y-m-d", strtotime($start_date));
$end_date=date("Y-m-d", strtotime($end_date));
$gm_health_crm_chart = $this->db->query("SELECT ospos_lead_type_info.lead_status,statusname,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status where ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and ospos_lead_type_info.created_date between '$start_date' and '$end_date' GROUP by lead_status");
$gm_health_crm_chart_rslt = $gm_health_crm_chart->result_array();
return $gm_health_crm_chart_rslt;
}
public function gm_health_crm_chart_drill($start_date,$end_date,$statusname,$emp_cat){
$emp_cat = $emp_cat;
$start_date=date("Y-m-d", strtotime($start_date));
$end_date=date("Y-m-d", strtotime($end_date));
$crm_chart_info = $this->db->query("SELECT ospos_lead_type_info.prospect_level,prospect_name,count(*) as lead_count,IFNULL(sum(expected_value),0) as crmvalue,IFNULL(sum(expected_login),0) as rmvalue FROM `ospos_lead_type_info` INNER JOIN ospos_lead_status on ospos_lead_status.status_id = ospos_lead_type_info.lead_status INNER JOIN ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level where ospos_lead_type_info.category = '$emp_cat' and ospos_lead_type_info.status = '1' and lead_mode = '1' and statusname = '$statusname' and ospos_lead_type_info.created_date between '$start_date' and '$end_date' GROUP by prospect_name");
$crm_chart_rslt = $crm_chart_info->result_array();
return $crm_chart_rslt;
}
public function gm_health_manager_bus($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rows = array();
$health_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and category = '$category' and deleted = '0' GROUP by id");
$health_rslt = $health_manager_info->result();
foreach($health_rslt as $key => $value){
$id = $value->id;
$health_tl_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id' ");
$health_manager_rslt = $health_tl_info->row();
$health_manager = $health_manager_rslt->first_name;
$crm_ids_info = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as crm_team_ids FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.reporting = '$id' ");
$crm_ids_rslt = $crm_ids_info->result();
foreach($crm_ids_rslt as $key => $value){
$ids = $value->crm_team_ids;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($ids) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '3'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
//$avg_net = $netamount / $month_count;
$rows['data'][] = array('id'=> $id,'health_manager' => $health_manager,'netamount' => $netamount);
}
}
}
return $rows;
}
public function gm_health_crm_bus($manager,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rows = array();
$crm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '3' and deleted = '0' and reporting in ($manager) GROUP by id");
$crm_rslt = $crm_info->result();
foreach($crm_rslt as $key => $value){
$id = $value->id;
$crm_name = $value->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($id) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '3'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
$rows['data'][] = array('crm_name' => $crm_name,'value' => $netamount);
}
}
return $rows;
}
public function gm_rm_manager_avg($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
//Get Month Count
$ts1 = strtotime($start_date);
$ts2 = strtotime($end_date);
$year1 = date('Y', $ts1);
$year2 = date('Y', $ts2);
$month1 = date('m', $ts1);
$month2 = date('m', $ts2);
$month_count = (($year2 - $year1) * 12) + ($month2 - $month1);
$rows = array();
$rm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '9' and deleted = '0' GROUP by id");
$rm_rslt = $rm_info->result();
foreach($rm_rslt as $key => $value){
$id = $value->id;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manager = $rm_manager_rslt->first_name;
$rm_ids_info = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as rm_team_ids FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.reporting = '$id' ");
$rm_ids_rslt = $rm_ids_info->result();
foreach($rm_ids_rslt as $key => $value){
$ids = $value->rm_team_ids;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by in ($ids) or ospos_lead_type_info.rm_name in ($ids)) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '2'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
//$avg_net = $netamount / $month_count;
$rows['data'][] = array('id'=> $id,'month_count' =>$month_count ,'rm_manager' => $rm_manager,'netamount' => $netamount);
}
}
}
return $rows;
}
public function gm_rm_avg($manager,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$ts1 = strtotime($start_date);
$ts2 = strtotime($end_date);
$year1 = date('Y', $ts1);
$year2 = date('Y', $ts2);
$month1 = date('m', $ts1);
$month2 = date('m', $ts2);
$month_count = (($year2 - $year1) * 12) + ($month2 - $month1);
$rows = array();
$rm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '5' and deleted = '0' and reporting in ($manager) GROUP by id");
$rm_rslt = $rm_info->result();
foreach($rm_rslt as $key => $value){
$id = $value->id;
$rm_name = $value->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by in ($id) or ospos_lead_type_info.rm_name in ($id)) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '2'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
$rows['data'][] = array('rm_name' => $rm_name,'month_count' =>$month_count,'value' => $netamount);
}
}
return $rows;
}
public function gm_month_wise_chart($start_date,$end_date,$category){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$qry = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as netamount,ospos_leads_product.login_date as login_date FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '$category' GROUP BY YEAR(ospos_leads_product.login_date),MONTH(ospos_leads_product.login_date) ORDER BY YEAR(ospos_leads_product.login_date),MONTH(ospos_leads_product.login_date) ASC");
$row = $qry->result_array();
$month['name'] = 'Name';
$series1['name'] = 'Value';
foreach ($row as $key => $value) {
$login_date = $value['login_date'];
$netamount = $value['netamount'];
$time = strtotime($login_date);
$month_name = date("F",$time);
$year=date("Y",$time);
$login = $month_name."-".$year;
$month['data'][] = $login;
$series1['data'][] = $netamount;
}
$result = array();
array_push($result,$month);
array_push($result,$series1);
return $result;
}
public function gm_health_manager_avg($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
//Get Month Count
$ts1 = strtotime($start_date);
$ts2 = strtotime($end_date);
$year1 = date('Y', $ts1);
$year2 = date('Y', $ts2);
$month1 = date('m', $ts1);
$month2 = date('m', $ts2);
$month_count = (($year2 - $year1) * 12) + ($month2 - $month1);
$rows = array();
$health_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and category = '$category' and deleted = '0' GROUP by id");
$health_rslt = $health_manager_info->result();
foreach($health_rslt as $key => $value){
$id = $value->id;
$health_tl_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id' ");
$health_manager_rslt = $health_tl_info->row();
$health_manager = $health_manager_rslt->first_name;
$crm_ids_info = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as crm_team_ids FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.reporting = '$id' ");
$crm_ids_rslt = $crm_ids_info->result();
foreach($crm_ids_rslt as $key => $value){
$ids = $value->crm_team_ids;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($ids) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '3'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
//$avg_net = $netamount / $month_count;
$rows['data'][] = array('id'=> $id,'month_count' =>$month_count ,'health_manager' => $health_manager,'netamount' => $netamount);
}
}
}
return $rows;
}
public function gm_health_crm_avg($manager,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$ts1 = strtotime($start_date);
$ts2 = strtotime($end_date);
$year1 = date('Y', $ts1);
$year2 = date('Y', $ts2);
$month1 = date('m', $ts1);
$month2 = date('m', $ts2);
$month_count = (($year2 - $year1) * 12) + ($month2 - $month1);
$rows = array();
$crm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '3' and deleted = '0' and reporting in ($manager) GROUP by id");
$crm_rslt = $crm_info->result();
foreach($crm_rslt as $key => $value){
$id = $value->id;
$crm_name = $value->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($id) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '3'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
$rows['data'][] = array('crm_name' => $crm_name,'month_count' =>$month_count,'value' => $netamount);
}
}
return $rows;
}
public function gm_health_month_wise_chart($start_date,$end_date,$category){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$qry = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as netamount,ospos_leads_product.login_date as login_date FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_leads_product.product_category = '$category' GROUP BY YEAR(ospos_leads_product.login_date),MONTH(ospos_leads_product.login_date) ORDER BY YEAR(ospos_leads_product.login_date),MONTH(ospos_leads_product.login_date) ASC");
$row = $qry->result_array();
$month['name'] = 'Name';
$series1['name'] = 'Value';
foreach ($row as $key => $value) {
$login_date = $value['login_date'];
$netamount = $value['netamount'];
$time = strtotime($login_date);
$month_name = date("F",$time);
$year=date("Y",$time);
$login = $month_name."-".$year;
$month['data'][] = $login;
$series1['data'][] = $netamount;
}
$result = array();
array_push($result,$month);
array_push($result,$series1);
return $result;
}
/*
public function gm_mf_chart($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rows = array();
$lead_info = $this->db->query("SELECT first_name,IFNULL(sum(amount),0) as netamount FROM ospos_mf_investment inner join ospos_employees on ospos_employees.id = ospos_mf_investment.rm_name INNER join ospos_people on ospos_people.person_id = ospos_employees.id where DATE_FORMAT(ospos_mf_investment.entry_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_mf_investment.investment_type = 'SIP' and ospos_mf_investment.status = '1' and ospos_employees.deleted = '0' GROUP BY first_name");
$lead_rslt = $lead_info->result_array();
return $lead_rslt;
} */
public function gm_mf_manager_avg($category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rows = array();
$rm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '9' and deleted = '0' GROUP by id");
$rm_rslt = $rm_info->result();
foreach($rm_rslt as $key => $value){
$id = $value->id;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manager = $rm_manager_rslt->first_name;
$rm_ids_info = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as rm_team_ids FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.reporting = '$id' ");
$rm_ids_rslt = $rm_ids_info->result();
foreach($rm_ids_rslt as $key => $value){
$ids = $value->rm_team_ids;
$lead_info = $this->db->query("SELECT IFNULL(sum(amount),0) as netamount FROM ospos_lead_type_info INNER join ospos_mf_investment on ospos_mf_investment.lead_id = ospos_lead_type_info.lead_id where DATE_FORMAT(ospos_mf_investment.entry_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_mf_investment.rm_name in ($ids) and ospos_lead_type_info.status = '1' and investment_type = 'SIP' and fund_class = 'Equity' and ospos_mf_investment.status = '1'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
//$avg_net = $netamount / $month_count;
$rows['data'][] = array('id'=> $id,'rm_manager' => $rm_manager,'netamount' => $netamount);
}
}
}
return $rows;
}
public function gm_mf_rm_avg($manager,$category,$start_date,$end_date){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$rows = array();
$rm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '5' and deleted = '0' and reporting in ($manager) GROUP by id");
$rm_rslt = $rm_info->result();
foreach($rm_rslt as $key => $value){
$id = $value->id;
$rm_name = $value->first_name;
$lead_info = $this->db->query("SELECT IFNULL(sum(amount),0) as netamount FROM ospos_lead_type_info INNER join ospos_mf_investment on ospos_mf_investment.lead_id = ospos_lead_type_info.lead_id where DATE_FORMAT(ospos_mf_investment.entry_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_mf_investment.rm_name in ($id) and investment_type = 'SIP' and fund_class = 'Equity' and ospos_lead_type_info.status = '1' and ospos_mf_investment.status = '1'");
$lead_rslt = $lead_info->result();
foreach($lead_rslt as $key => $value){
$netamount = $value->netamount;
$rows['data'][] = array('rm_name' => $rm_name,'value' => $netamount);
}
}
return $rows;
}
public function gm_mf_month_wise_chart($start_date,$end_date,$category){
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$qry = $this->db->query("SELECT IFNULL(sum(amount),0) as netamount,ospos_mf_investment.entry_date as entry_date FROM ospos_mf_investment inner join ospos_employees on ospos_employees.id = ospos_mf_investment.rm_name INNER join ospos_people on ospos_people.person_id = ospos_employees.id where DATE_FORMAT(ospos_mf_investment.entry_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_mf_investment.investment_type = 'SIP' and fund_class = 'Equity' and ospos_mf_investment.status = '1' and ospos_employees.deleted = '0' GROUP BY YEAR(ospos_mf_investment.entry_date),MONTH(ospos_mf_investment.entry_date) ORDER BY YEAR(ospos_mf_investment.entry_date),MONTH(ospos_mf_investment.entry_date) ASC");
$row = $qry->result_array();
$month['name'] = 'Name';
$series1['name'] = 'Value';
foreach ($row as $key => $value) {
$entry_date = $value['entry_date'];
$netamount = $value['netamount'];
$time = strtotime($entry_date);
$month_name = date("F",$time);
$year=date("Y",$time);
$login = $month_name."-".$year;
$month['data'][] = $login;
$series1['data'][] = $netamount;
}
$result = array();
array_push($result,$month);
array_push($result,$series1);
return $result;
}
//MF Start SAT 11 Aug 2018
public function get_client_sts(){
$client_sts_info = $this->db->query("SELECT count(*) as count,client_sts FROM ospos_mf_ewealth_log where ospos_mf_ewealth_log.status = '1' GROUP by client_sts");
$client_sts_rslt = $client_sts_info->result_array();
return $client_sts_rslt;
}
public function doc_chart_drill($client_sts){
$doc_sts_info = $this->db->query("SELECT count(*) as count,doc_sts FROM ospos_mf_ewealth_log where client_sts = '$client_sts' and ospos_mf_ewealth_log.status = '1' GROUP by doc_sts");
$doc_sts_rslt = $doc_sts_info->result_array();
return $doc_sts_rslt;
}
public function ewealth_sts_chart_drill2($doc_sts){
$ewealth_sts_info = $this->db->query("SELECT count(*) as count,ewealth_sts FROM ospos_mf_ewealth_log where doc_sts = '$doc_sts' and ospos_mf_ewealth_log.status = '1' GROUP by ewealth_sts");
$ewealth_sts_rslt = $ewealth_sts_info->result_array();
return $ewealth_sts_rslt;
}
public function ewealth_reason_drill3($ewealth_sts){
$rej_sts_info = $this->db->query("SELECT count(*) as count,rejected_reason FROM ospos_mf_ewealth_log where ewealth_sts = '$ewealth_sts' and ospos_mf_ewealth_log.status = '1' GROUP by rejected_reason");
$rej_sts_rslt = $rej_sts_info->result_array();
return $rej_sts_rslt;
}
public function invest_login_chart(){
$login_sts_info = $this->db->query("SELECT sum(amount) as amount,login_status FROM ospos_mf_investment where investment_type = 'SIP' and ospos_mf_investment.status = '1' GROUP by login_status");
$login_sts_rslt = $login_sts_info->result_array();
return $login_sts_rslt;
}
public function invest_chart_drill($login_status){
$sip_sts_info = $this->db->query("SELECT sum(amount) as amount,sip_status FROM ospos_mf_investment where investment_type = 'SIP' and login_status='$login_status' and ospos_mf_investment.status = '1' GROUP by sip_status");
$sip_sts_rslt = $sip_sts_info->result_array();
return $sip_sts_rslt;
}
public function investment_chart(){
$cur_month = date("Y-m");
$investment_info = $this->db->query("SELECT sum(amount) as amount,investment_type,login_status FROM ospos_mf_investment where ospos_mf_investment.status = '1' GROUP by investment_type");
$investment_rslt = $investment_info->result_array();
return $investment_rslt;
}
}
?>