<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Reports_model extends CI_Model {
	
	//the mysql server runs on -0700 hrs time zone. hence add 10(7+3) hrs to convert to Nairobi time
	//select time_format(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),'%H%i‌​');
	//SELECT TIMESTAMPDIFF(SECOND, NOW(), UTC_TIMESTAMP); to get the difference in seconds.
	private $time_diff = 36000;//10*60*60;

	function __construct()
    {
     parent::__construct();		
	
    }
	
  /*this function pulls each month and the total-currently for diversity but idealli 
  it will be dynamic and will be used for different corporations*/
   function fetch_corporation_finacial_report($corp_id)
   {
	   $time_diff = $this->time_diff;
	   $sqlQuery = "SELECT 
						SUM(a.share_capital) as share_capital,a.date_time,
						DATE_FORMAT(FROM_UNIXTIME(date_time+$time_diff), '%b %Y') as month_paid,
					b.corporation_id 
					FROM 
						saccoManager.payments a
					INNER JOIN saccoManager.users b on a.user_id = b.user_id 
					WHERE b.corporation_id = $corp_id GROUP BY month_paid ORDER BY a.date_time ASC";
	   $query = $this->db->query($sqlQuery);
	   
	   if($query->result_array())
	   {
		  return $query->result_array();
	   }
	   else
	   {
		   return NULL;
	   }
   }
   
   function get_corporation_members($corp_id)
   {
	  $sqlQuery = "SELECT user_id FROM users WHERE user_type = 4 AND corporation_id = $corp_id";
	  
	  $query = $this->db->query($sqlQuery);
	   
	   if($query->result_array())
	   {
		  return $query->result_array();
	   }
	   else
	   {
		   return NULL;
	   }
   }
   	
  /*this function pulls each month and the contributions by each member per mont*/
   function fetch_contributions_per_member($corp_id)
   {
	   $time_diff = $this->time_diff;
	   $sqlQuery = "SELECT 
						a.user_id,
						a.share_capital,
						a.date_time,
						DATE_FORMAT(FROM_UNIXTIME(date_time+$time_diff), '%b %Y') as month_paid,
					b.corporation_id
					FROM
						saccoManager.payments a
					INNER JOIN saccoManager.users b ON b.user_id=a.user_id
					WHERE b.corporation_id = $corp_id ORDER BY a.date_time ASC";
	   $query = $this->db->query($sqlQuery);
	   
	   $contributionMonthMember = array();
	   if($query->result_array())
	   {
		  foreach($query->result_array() as $row)
		  {
			  $contributionMonthMember[$row['month_paid']][$row['user_id']] =  $row['share_capital'];
		  }
		  return $contributionMonthMember;
	   }
	   else
	   {
		   return NULL;
	   }
   }
      	
  /*this function pulls the total contributions for each member*/
   function fetch_total_per_member($corp_id)
   {
	   $sqlQuery = "SELECT
						a.user_id,
						SUM(a.share_capital) as share_capital,
						b.corporation_id
					FROM
						saccoManager.payments a
							INNER JOIN
						saccoManager.users b ON a.user_id = b.user_id
					WHERE
						b.corporation_id = $corp_id
					GROUP BY a.user_id";
	   $query = $this->db->query($sqlQuery);
	   
	   $totalContributionMember = array();
	   if($query->result_array())
	   {
		  foreach($query->result_array() as $row)
		  {
			  $totalContributionMember[$row['user_id']] =  $row['share_capital'];
		  }
		  return $totalContributionMember;
	   }
	   else
	   {
		   return NULL;
	   }
   }
}
?>