<?php

class survey_calc_Model {
	
	public $filter_resp_sql;
	public $target_total_weight;
	
	
	
	public function filter_resp($srv_object,$target_id=0,$factorid=0,$optionid=0) {
			
			$targ_sql = "";
			$target_ary = array();			
			
			if($target_id>0) {
				$sql_target  = "select factorid,opt_id from mst_targets where srv_mrkt=" . $srv_object['srv_mrkt'] . " and trg_id = " . $target_id;
				$rslt_target = mysql_query($sql_target);
				while($trw = mysql_fetch_assoc($rslt_target)) {
					$target_ary[$trw['factorid']][] = $trw['opt_id'];
				}
			}
			else {
				if($factorid>0) {
					$target_ary[$factorid][] = $optionid;
				}
			}
			
			if(count($target_ary) >0) {
				$resp_array = array();
				foreach($target_ary as $factorid=>$opt_ary) {
					if($factorid >0) {
						$psql = " select distinct respid from survey_respsocio where  srv_mrkt=" . $srv_object['srv_mrkt'] .
							    " and (factorid = ". $factorid. " and opt_id in (". implode(",",$opt_ary) . ")) ";
						if(count($resp_array) >0) {
							$psql .= " and (respid in (" . implode(",",$resp_array) . ")) ";
						}
						$prslt = mysql_query($psql);
						
						$resp_array = array();
						if(mysql_num_rows($prslt)>0) {
							while($prw = mysql_fetch_array($prslt)) {
								$resp_array[] = $prw[0];
							}
							mysql_free_result($prslt);
						}
					}
				}
				
				if(count($resp_array) >0) {
					$targ_sql = "select distinct respid from survey_respsocio where srv_mrkt=" . $srv_object['srv_mrkt'] . " and respid in (" . implode(",",$resp_array) . ") ";
				}
				else {
					$targ_sql = "select distinct respid from survey_respsocio where srv_mrkt=" . $srv_object['srv_mrkt'];
				}
				
			}
			
		
			// Filter Respondents
			$this->filter_resp_sql = "select respid from survey_resp 
								where	srv_mrkt = " . $srv_object['srv_mrkt']
										. " and srv_type = " . $srv_object['type']
										. " and (srv_dt >=  '" . $srv_object['rw_srv_stdt'] . "' and srv_dt <= '" . $srv_object['rw_srv_eddt'] . "') ";
			if(strlen($targ_sql) >0) {
				$this->filter_resp_sql .= " and (respid in (" .	$targ_sql . ")) ";
			}
			
			$target_resp_sql = "select sum(weight) as totweight from survey_resp 
								where	srv_mrkt = " . $srv_object['srv_mrkt']
										. " and srv_type = " . $srv_object['type']
										. " and (srv_dt >=  '" . $srv_object['rw_srv_stdt'] . "' and srv_dt <= '" . $srv_object['rw_srv_eddt'] . "') ";
			if(strlen($targ_sql) >0) {
				$target_resp_sql .= " and (respid in (" .	$targ_sql . ")) ";
			}

			$target_resp_rslt = mysql_query($target_resp_sql);
			$this->target_total_weight = 0;
			
			if(mysql_num_rows($target_resp_rslt) >0) {
				$tg_rw =mysql_fetch_assoc($target_resp_rslt);
				$this->target_total_weight = 	$tg_rw['totweight'];
			}		
		
	}


	public function extract_grp_reach($srv_object,$target_id=0,$analysis_by=1,$chnls_list=array(),$sort=1,$sortby='grp') {   // Analysis by: 1= Channel, 2= Typology, 3= Network
	
		$channel_list = array();
		
		try {

			$this->filter_resp($srv_object,$target_id);
			
			// Extract Channel Data
			if($srv_object['type'] == 1) {  // Applicable only for TV 
				if($analysis_by==1) { // By channel
					$list_chnls_sql = "select mst_channels.chid,mst_channels.chname,
											  mst_channels.grpid,mst_channels.typoid,
											  mst_networks.netdesc,mst_typologies.typo_desc 
											  from mst_channels 
												 left join mst_networks on mst_channels.grpid = mst_networks.id
												 left join mst_typologies on mst_channels.typoid = mst_typologies.id
											  where chnl_type = 'TV' ";
					if(count($chnls_list) >0) {
						$list_chnls_sql .= " and chid in (". implode(",",$chnls_list) . ") ";
					}
					$list_chnls_sql .= " order by chid";
				}
				elseif($analysis_by==2) { // By typology
					$list_chnls_sql = "select distinct typoid,typo_desc from mst_channels 
										left join mst_typologies on mst_channels.typoid = mst_typologies.id  
										where chnl_type = 'TV' and typoid >0 order by typoid";
				}
				elseif($analysis_by==3) { // By Network
					$list_chnls_sql = "select distinct grpid,netdesc from mst_channels 
										left join mst_networks on mst_channels.grpid = mst_networks.id  
										where chnl_type = 'TV' and grpid >0  order by grpid";
				}
				
			} elseif($srv_object['type'] == 2) {   // Applicable only for Radio
				if($analysis_by==1) { // By channel
					$list_chnls_sql = "select mst_channels.chid,mst_channels.chname,
											  mst_channels.grpid,mst_channels.typoid,
											  mst_networks.netdesc,mst_typologies.typo_desc 
											  from mst_channels 
												 left join mst_networks on mst_channels.grpid = mst_networks.id
												 left join mst_typologies on mst_channels.typoid = mst_typologies.id											  
											  where chnl_type = 'RD' ";
					if(count($chnls_list) >0) {
						$list_chnls_sql .= " and chid in (". implode(",",$chnls_list) . ") ";
					}
					$list_chnls_sql .= " order by chid";
				}
				elseif($analysis_by==2) { // By typology
					$list_chnls_sql = "select distinct typoid,typo_desc from mst_channels left join mst_typologies on mst_channels.typoid = mst_typologies.id  where chnl_type = 'RD' order by typoid";
				}
				elseif($analysis_by==3) { // By Network
					$list_chnls_sql = "select distinct grpid,netdesc from mst_channels left join mst_networks on mst_channels.grpid = mst_networks.id  where chnl_type = 'RD' order by grpid";
				}
				
			}
			
			if($analysis_by==1) {  // By channel
				$channel_list[0]['chid'] = 0;
				$channel_list[0]['chname'] = "Total";
				$channel_list[0]['network'] = "";
				$channel_list[0]['typology'] = "";
				$channel_list[0]['grp']    = $this->get_grp(0,1);
				$channel_list[0]['reach']  = $this->get_reach(0,1);
			}
			elseif($analysis_by==2) {  // By Typology
				$channel_list[0]['typoid'] = 0;
				$channel_list[0]['typo_desc'] = "Total";
				$channel_list[0]['grp']    = $this->get_grp(0,2);
				$channel_list[0]['reach']  = $this->get_reach(0,2);
			}
			elseif($analysis_by==3) {  // By Network
				$channel_list[0]['grpid'] = 0;
				$channel_list[0]['netdesc'] = "Total";
				$channel_list[0]['grp']    = $this->get_grp(0,3);
				$channel_list[0]['reach']  = $this->get_reach(0,3);
			}

			
			if($analysis_by==1) {  // By channel
				if($srv_object['type'] == 1 || $srv_object['type'] == 2) {
					$list_chnls_rslt = mysql_query($list_chnls_sql);
					while($chls_rw = mysql_fetch_assoc($list_chnls_rslt)) {
						$channel_list[$chls_rw['chid']]['chid'] = $chls_rw['chid'];
						$channel_list[$chls_rw['chid']]['chname'] = $chls_rw['chname'];
						$channel_list[$chls_rw['chid']]['network'] = $chls_rw['netdesc'];
						$channel_list[$chls_rw['chid']]['typology'] = $chls_rw['typo_desc'];
						$channel_list[$chls_rw['chid']]['grp']    = $this->get_grp($chls_rw['chid'],1);
						$channel_list[$chls_rw['chid']]['reach']  = $this->get_reach($chls_rw['chid'],1);
					}
				}
			}elseif($analysis_by==2) {  // By typology
				if($srv_object['type'] == 1 || $srv_object['type'] == 2) {
					$list_chnls_rslt = mysql_query($list_chnls_sql);
					while($chls_rw = mysql_fetch_assoc($list_chnls_rslt)) {
						$channel_list[$chls_rw['typoid']]['typoid'] = $chls_rw['typoid'];
						$channel_list[$chls_rw['typoid']]['typo_desc'] = $chls_rw['typo_desc'];
						$channel_list[$chls_rw['typoid']]['grp']    = $this->get_grp($chls_rw['typoid'],2);
						$channel_list[$chls_rw['typoid']]['reach']  = $this->get_reach($chls_rw['typoid'],2);
					}
				}
			}elseif($analysis_by==3) {  // By Network
				if($srv_object['type'] == 1 || $srv_object['type'] == 2) {
					$list_chnls_rslt = mysql_query($list_chnls_sql);
					while($chls_rw = mysql_fetch_assoc($list_chnls_rslt)) {
						$channel_list[$chls_rw['grpid']]['grpid'] = $chls_rw['grpid'];
						$channel_list[$chls_rw['grpid']]['netdesc'] = $chls_rw['netdesc'];
						$channel_list[$chls_rw['grpid']]['grp']    = $this->get_grp($chls_rw['grpid'],3);
						$channel_list[$chls_rw['grpid']]['reach']  = $this->get_reach($chls_rw['grpid'],3);
					}
				}
			}
				
			
			mysql_free_result($list_chnls_rslt);
		}
		catch (Exception $e) {
			return false;
		}
		
		$channel_grp = array();
		foreach ($channel_list as $channellst) {    
			$channel_grp[] = $channellst[$sortby];
		}

		if($sort==1) {
			if($sortby == "grp" || $sortby == "reach") {
				array_multisort($channel_grp, SORT_DESC, $channel_list);
			}
			else {
				array_multisort($channel_grp, SORT_ASC, $channel_list);	
			}
		}
	
		return $channel_list;
	}


	public function extract_ts_grp_reach($srv_object,$target_id=0,$chnls_list,$ts,$analysis_by=1) {
		
		$ts_chls_arry = array();
		
		try {
			
			$this->filter_resp($srv_object,$target_id);
			
			// Extract Day Parts
			foreach($chnls_list as $key=>$chnls) {
				
				if($analysis_by==1) { // By Channels
					$ky = $chnls['chid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['chid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['chname'];
				}
				elseif($analysis_by==2) { // By Typologies
					$ky = $chnls['typoid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['typoid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['typo_desc'];
				}
				elseif($analysis_by==3) { // By Network
					$ky = $chnls['grpid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['grpid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['netdesc'];
				}				
				
				foreach($ts as $rw) {
					$did = $rw['tseg1'] . "_" . $rw['tseg2'];
					$ts_chls_arry[$ky]['tseg'][$did]['tseg_desc'] = $rw['tseg_desc'];
					$ts_chls_arry[$ky]['tseg'][$did]['tseg1'] = $rw['tseg1'];
					$ts_chls_arry[$ky]['tseg'][$did]['tseg2'] = $rw['tseg2'];
					$ts_chls_arry[$ky]['tseg'][$did]['grp']   = $this->get_grp($ky,$analysis_by,$rw['tseg1'],$rw['tseg2']);
					$ts_chls_arry[$ky]['tseg'][$did]['reach'] = $this->get_reach($ky,$analysis_by,$rw['tseg1'],$rw['tseg2']);
				}
			}
			
		} catch (Exception $e) {
			print_r($e);
		}
		
		return $ts_chls_arry;
	}




	
	public function extract_dp_grp_reach($srv_object,$target_id=0,$chnls_list,$dpid) {
		
		$dp_chls_arry = array();
		
		try {
			
			$this->filter_resp($srv_object,$target_id);
			
			// Extract Day Parts
			$dp_sql = "select tseg1,tseg2,tseg_ordr,tseg_desc,dp_name,dp_id  from mst_dp where dp_id = " . $dpid ." order by tseg_ordr" ;
			$dp_rslt= mysql_query($dp_sql);
			
			foreach($chnls_list as $ky=>$chnl_name) {
				$dp_chls_arry[$ky]['chid'] = $ky;
				$dp_chls_arry[$ky]['chname'] = $chnl_name;
				mysql_data_seek($dp_rslt,0);
				while($rw=mysql_fetch_assoc($dp_rslt)) {
					$did = $rw['dp_id']."_".$rw['tseg_ordr'];
					$dp_chls_arry[$ky]['daypart'][$did]['dp_id'] = $did;
					$dp_chls_arry[$ky]['daypart'][$did]['dp_name'] = $rw['dp_name'];
					$dp_chls_arry[$ky]['daypart'][$did]['tseg_desc'] = $rw['tseg_desc'];
					$dp_chls_arry[$ky]['daypart'][$did]['tseg1'] = $rw['tseg1'];
					$dp_chls_arry[$ky]['daypart'][$did]['tseg2'] = $rw['tseg2'];
					$dp_chls_arry[$ky]['daypart'][$did]['tseg_ordr'] = $rw['tseg_ordr'];					
					$dp_chls_arry[$ky]['daypart'][$did]['grp']   = $this->get_grp($ky,1);
					$dp_chls_arry[$ky]['daypart'][$did]['reach'] = $this->get_reach($ky,1,$rw['tseg1'],$rw['tseg2']);
				}
			}
			
		} catch (Exception $e) {
			print_r($e);
		}
		
		return $dp_chls_arry;
	}


	public function extract_socio_grp_reach($srv_object,$factor,$option,$chnls_list,$ts,$analysis_by=1) {
		
		$ts_chls_arry = array();
		
		try {
			
			$this->filter_resp($srv_object,0,$factor,$option);
			
			// Extract Day Parts
			foreach($chnls_list as $key=>$chnls) {
				
				if($analysis_by==1) { // By Channels
					$ky = $chnls['chid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['chid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['chname'];
				}
				elseif($analysis_by==2) { // By Typologies
					$ky = $chnls['typoid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['typoid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['typo_desc'];
				}
				elseif($analysis_by==3) { // By Network
					$ky = $chnls['grpid'];
					$ts_chls_arry[$ky]['chid']   = $chnls['grpid'];
					$ts_chls_arry[$ky]['chname'] = $chnls['netdesc'];
				}				
				
				foreach($ts as $rw) {
					$did = $rw['tseg1'] . "_" . $rw['tseg2'];
					
					$ts_chls_arry[$ky]['tseg'][$did]['tseg_desc'] = $rw['tseg_desc'];
					$ts_chls_arry[$ky]['tseg'][$did]['tseg1'] = $rw['tseg1'];
					$ts_chls_arry[$ky]['tseg'][$did]['tseg2'] = $rw['tseg2'];
					$ts_chls_arry[$ky]['tseg'][$did]['grp']   = $this->get_grp($ky,$analysis_by,$rw['tseg1'],$rw['tseg2']);
					$ts_chls_arry[$ky]['tseg'][$did]['reach'] = $this->get_reach($ky,$analysis_by,$rw['tseg1'],$rw['tseg2']);
				}
			}
			
		} catch (Exception $e) {
			print_r($e);
		}
		
		return $ts_chls_arry;
	}


	
	public function get_grp($chid,$analysis_by,$tsg1=0,$tsg2=96) {
		$grp = 0;
		try {
			
			if($analysis_by==1) {  //By Channel
				$grp_sql = "select survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
									        and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";
				if($chid>0) $grp_sql.= " and chid = " . $chid;
			}
			elseif($analysis_by==2) {  //By Typology
				$grp_sql = "select survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
							left join mst_channels on survey_timesegment.chid  = mst_channels.chid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
									  		and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";
				if($chid>0) $grp_sql.= " and mst_channels.typoid = " . $chid;
			}
			elseif($analysis_by==3) {  //By Network
				$grp_sql = "select survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
							left join mst_channels on survey_timesegment.chid  = mst_channels.chid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
									  		and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";
				if($chid>0) $grp_sql.= " and mst_channels.grpid = " . $chid;
			}
			
			$grp_rslt = mysql_query($grp_sql);	
			$resp_tot = 0;
			while($grp_rw=mysql_fetch_assoc($grp_rslt)) {
				$resp_tot = $resp_tot + $grp_rw['weight'];
			}
			
			if($this->target_total_weight >0) {
				$grp = round(($resp_tot / $this->target_total_weight) * 100,2);
			}
			else {
				$grp =0;
			}
			
			mysql_free_result($grp_rslt);
		}
		catch (Exception $e) {
			return 0;		
		}
		return $grp;
	}
	
	public function get_reach($chid,$analysis_by,$tsg1=0,$tsg2=96) {
		$reach = 0;
		try {
			if($analysis_by==1) { // By Channel
				$reach_sql = "select distinct survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
							and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";
				if($chid>0) $reach_sql.= " and chid = " . $chid;
			}
			elseif($analysis_by==2) { // By Typology
				$reach_sql = "select distinct survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
							left join mst_channels on survey_timesegment.chid  = mst_channels.chid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
							and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";	
				if($chid>0) $reach_sql.=  " and mst_channels.typoid = " . $chid;
			}
			elseif($analysis_by==3) { // By Network
				$reach_sql = "select distinct survey_resp.respid,survey_resp.weight 
									  from survey_timesegment 
							left join survey_resp on survey_timesegment.respid = survey_resp.respid
							left join mst_channels on survey_timesegment.chid  = mst_channels.chid
									  where survey_resp.respid in (" . $this->filter_resp_sql . ") 
							and (tsid >= ". $tsg1 . " and tsid <= " . $tsg2 . ")";	
				if($chid>0) $reach_sql.=  " and mst_channels.grpid = " . $chid;
			}
			
			
			$reach_rslt = mysql_query($reach_sql);	
			$resp_tot = 0;
			while($reach_rw=mysql_fetch_assoc($reach_rslt)) {
				$resp_tot = $resp_tot + $reach_rw['weight'];
			}
			
			if($this->target_total_weight >0) {
				$reach = round(($resp_tot / $this->target_total_weight) * 100,2);
			}
			else {
				$reach = 0;
			}
			
			mysql_free_result($reach_rslt);
		}
		catch (Exception $e) {
			return 0;		
		}		
		return $reach;
	}
	
	
	
	
}

?>