<?php

class  program_data {

	public function fetchdata($srv_mrkt,$srv_type,$chnl_lst,$pstDate,$pedDate,$StTime="0000",$EdTime="3000",$Myresp_SQL="",$TimeZone=8,$pLevel=1,$pBreak=0,$progname="") {
		
		$program_data =array();
		
		try {		
			$pg_sql = "select prog.*,mst_typo.descript from prog left join mst_typo on left(prog.typocode,2) = mst_typo.code
						where 1=1 ";
			
			if($pLevel!=0) $pg_sql .= ' and level=1 ';
			if($pBreak==0) $pg_sql .= ' and TypoAlp not in ("AA","AB") ';
			if(count($chnl_lst)>0) $pg_sql .= ' and chid in ('. implode(',',$chnl_lst) . ') ';
			if(strlen(trim($progname)) >0)  $pg_sql .= ' and progname like "'. $progname . '" ';
			
						$pg_sql .= ' and (gmt_sttime between "'. $StTime .'" and "' . $EdTime .'") ';
						$pg_sql .= ' and rtrim(ltrim(lower(progname))) != "system abort" ';
						$pg_sql .= ' and left(typocode,2) != "17" ';
						$pg_sql .= ' and left(typocode,4) != "0802" ';
						$pg_sql .= ' and (pdate between "' . $pstDate . '" and "' . $pedDate . '") ';
			$pg_sql .= ' order by chid,pdate,gmt_sttime ';
			
			$pg_rslt = mysql_query($pg_sql);
			
			$mslno = 1;
			$chid  = 0;
			while($rw_pg=mysql_fetch_assoc($pg_rslt))  {
				
				if($chid != $rw_pg['chid']) {
					$mslno = 1;
					$chid  = $rw_pg['chid'];
				}
				
				$rw_pg['slno']    = $mslno;
				$pg_date = new DateTime($rw_pg['pdate']);
				
				$rw_pg['pmonth']  = $pg_date->format("m");
				$rw_pg['pcmonth'] = $pg_date->format("F");
				
				$mStime = $this->gmttolocal($srv_mrkt,$rw_pg['gmt_sttime']);
				$rw_pg['StTime']  = substr($mStime,0,2) . ":" . substr($mStime,2,2);
				$mEtime = $this->gmttolocal($srv_mrkt,$rw_pg['gmt_edtime']);
				$rw_pg['EndTime'] = substr($mEtime,0,2) . ":" . substr($mEtime,2,2);
				
				$mTimeSegSlots = $this->get_time_slots($mStime,$mEtime);

				$rw_pg['Tseg1']	   = $mTimeSegSlots[0];
				$rw_pg['Tseg2']	   = $mTimeSegSlots[1];
				
				$rw_pg['mDur'] = $this->caluate_duration($mStime,$mEtime);

				// Calculate Ratings
				$ShowThisPrg = true;
				
				if($ShowThisPrg == true) {
					if(strlen($Myresp_SQL) <=0) {
						$Myresp_SQL = 'select respid from survey_resp 
										where srv_mrkt = ' . $srv_mrkt . ' 
											  and srv_type = ' . $srv_type . ' 
											  and srv_dt>= "' . $pstDate . '" and srv_dt <=  "' . $pedDate . '"';
					}
					
					$sql_1  = 'SELECT sum(weight) as weight FROM survey_resp WHERE respid in (' . $Myresp_SQL . ') AND srv_dt = "' . $rw_pg['pdate'] . '"';
					$rslt_1 = mysql_query($sql_1);
					$All_Viewers = 0;
					if(mysql_num_rows($rslt_1) >0) {
						$All_Viewers_Ary = mysql_fetch_assoc($rslt_1);
						$All_Viewers = $All_Viewers_Ary['weight'];
					}
					$All_Viewers_resp_sql = 'SELECT respid FROM survey_resp WHERE respid in (' . $Myresp_SQL . ') AND srv_dt = "' . $rw_pg['pdate'] . '"';
					mysql_free_result($rslt_1);
					
					if($All_Viewers <= 0) {  // As there is no Survey Consider data as a weekday
						$prog_date = new DateTime($rw_pg['pdate']);
						$wday = $prog_date->format("w");
						
						$sql_1  = 'SELECT sum(weight) as weight FROM survey_resp 
									WHERE respid in (' . $Myresp_SQL . ') AND DATE_FORMAT(srv_dt,"%w") = ' . $wday;
									
						$rslt_1 = mysql_query($sql_1);
						$All_Viewers = 0;
						if(mysql_num_rows($rslt_1) >0) {
							$All_Viewers_Ary = mysql_fetch_assoc($rslt_1);
							$All_Viewers = $All_Viewers_Ary['weight'];
						}
						$All_Viewers_resp_sql = 'SELECT respid FROM survey_resp WHERE respid in (' . $Myresp_SQL . ') AND DATE_FORMAT(srv_dt,"%w") = ' . $wday;
						mysql_free_result($rslt_1);					
					}
					
					
					if($All_Viewers > 0) { 
						/*
						//PeopleWatched
						$sql_2 = "select sum(survey_resp.weight) as weight
								from survey_resp,survey_timesegment
								where survey_resp.respid=survey_timesegment.respid
								      and survey_resp.respid in (" . $All_Viewers_resp_sql . ") ";
						$rslt_2 = mysql_query($sql_2);
						$PeopleWatched = 0;
						if(mysql_num_rows($rslt_2) >0) {
							$rw_pw = mysql_fetch_assoc($rslt_2);
							$PeopleWatched = $rw_pw['weight'];
						}
						mysql_free_result($rslt_2);
						// End PeopleWatched
						
						// PeopleWatchedChnl
						$sql_3 = "select sum(survey_resp.weight) as weight 
								  from survey_resp,survey_timesegment 
								  where survey_resp.respid=survey_timesegment.respid 
								  and survey_timesegment.chid = " . $rw_pg['chid'] . 
								" and survey_resp.respid in (" . $All_Viewers_resp_sql . ") ";
						$rslt_3 = mysql_query($sql_3);
						$PeopleWatchedChnl = 0;
						if(mysql_num_rows($rslt_3) >0) {
							$rw_pw = mysql_fetch_assoc($rslt_3);
							$PeopleWatchedChnl = $rw_pw['weight'];
						}
						mysql_free_result($rslt_3);						
						// End PeopleWatchedChnl
						
						// no_of_Viewers
						$sql_4 = "select sum(survey_resp.weight) as weight
								  from survey_resp,survey_timesegment
								  where survey_resp.respid=survey_timesegment.respid 
								  and survey_timesegment.chid = " . $rw_pg['chid'] .
								" and (survey_timesegment.tsid between " . $rw_pg['Tseg1'] . " and " . $rw_pg['Tseg2'] . ") 
								  and survey_resp.respid in (" . $All_Viewers_resp_sql . ")";  
						$rslt_4 = mysql_query($sql_4);
						$no_of_Viewers = 0;
						if(mysql_num_rows($rslt_4) >0) {
							$rw_pw = mysql_fetch_assoc($rslt_4);
							$no_of_Viewers = $rw_pw['weight'];
						}
						mysql_free_result($rslt_4);									
						// End of no_of_Viewers
						
						// no_of_Dist_Viewers
						$sql_5 = "select distinct survey_resp.respid,survey_resp.weight 
								  from survey_resp,survey_timesegment 
								  where survey_resp.respid=survey_timesegment.respid 
								  and survey_timesegment.chid = " . $rw_pg['chid'] .
								" and (survey_timesegment.tsid between " . $rw_pg['Tseg1'] . " and " . $rw_pg['Tseg2'] . ") 
								  and survey_resp.respid in (" . $All_Viewers_resp_sql . ")";  

						$rslt_5 = mysql_query($sql_5);
						$no_of_Dist_Viewers = 0;
						while($rw_pw=mysql_fetch_assoc($rslt_5)) {
							$no_of_Dist_Viewers = $no_of_Dist_Viewers + $rw_pw['weight'];
						}
						mysql_free_result($rslt_5);
						// End of no_of_Dist_Viewers
						
						
						// no_of_Dist_Viewers_totaltv
						$sql_6 = "select distinct survey_resp.respid,survey_resp.weight 
								  from survey_resp,survey_timesegment 
								  where survey_resp.respid=survey_timesegment.respid 
								  and (survey_timesegment.tsid between " . $rw_pg['Tseg1'] . " and " . $rw_pg['Tseg2'] . ") 
								  and survey_resp.respid in (" . $All_Viewers_resp_sql . ")";  
						$rslt_6 = mysql_query($sql_6);
						$no_of_Dist_Viewers_totaltv = 0;
						while($rw_pw=mysql_fetch_assoc($rslt_6)) {
							$no_of_Dist_Viewers_totaltv = $no_of_Dist_Viewers_totaltv + $rw_pw['weight'];
						}
						mysql_free_result($rslt_6);						
						// end of no_of_Dist_Viewers_totaltv
						
						
						$mreach = 0;
						$mTot_Tv= 0;
						$mpShare= 0;
						$mAts	= "00:00";
						$mats_perc = 0;
						
						$mmAvg_Rat = 0;					
						$mmreach   = 0;
						$mmpShare  = 0;
						$mxAvg_Rat = 0;					
						$mxreach   = 0;
						$mxpShare  = 0;					
						
						$mreach = round(($no_of_Dist_Viewers * 100 ) / $All_Viewers,2);
						$mTot_Tv= round(($no_of_Dist_Viewers_totaltv * 100 ) / $All_Viewers,2);
						if($no_of_Dist_Viewers_totaltv >0) {
							$mpShare= round(($no_of_Dist_Viewers * 100 ) / $no_of_Dist_Viewers_totaltv,2);
						}
						
						$rw_pg['reach'] = $mreach;
						$rw_pg['share'] = $mpShare;
						*/
						
						$sql_7 = "select tsid,sum(survey_resp.weight) as weight
								  from survey_resp,survey_timesegment
								  where survey_resp.respid=survey_timesegment.respid 
								  and survey_timesegment.chid = " . $rw_pg['chid'] .
								" and (survey_timesegment.tsid between " . $rw_pg['Tseg1'] . " and " . $rw_pg['Tseg2'] . ") 
								  and survey_resp.respid in (" . $All_Viewers_resp_sql . ")
								  group by tsid"; 
						$rslt_7 = mysql_query($sql_7);
						$nospots = mysql_num_rows($rslt_7);
						$Grp = 0;
						$TotSeen = 0;
						while($rw_pw=mysql_fetch_assoc($rslt_7)) {
								$mPerc = round(($rw_pw['weight'] * 100) / $All_Viewers,2);
								$Grp = $Grp + $mPerc;
								$TotSeen = $TotSeen + $rw_pw['weight'];
						}
						mysql_free_result($rslt_7);
						
						$rw_pg['Grp'] = $Grp;

						if($nospots >0) {
							$Avg_Rat  = round($Grp / $nospots,2);
						}
						else {
							$Avg_Rat  = 0;
						}

						$rw_pg['Avg_Rat'] = $Avg_Rat;
						
						/*
						$rw_pg['ats_perc'] = 0;
						$rw_pg['Ats'] = 0;
						
						if($no_of_Dist_Viewers >0) {
							$ats_perc = round(($TotSeen * 100) / ($nospots * $no_of_Dist_Viewers),2);
							$rw_pg['ats_perc'] = $ats_perc;
							$mDurMins   = round(intval(substr($rw_pg['mDur'],0,2)) * 60 + intval(substr($rw_pg['mDur'],3,2)),0);
							$mAts_mins  = round($mDurMins * ($ats_perc/100),0);
							$mAtsHrs    = intval(round($mAts_mins / 60,2));
							$mAtsMins   = $mAts_mins - ($mAtsHrs * 60);
							$Ats	    = str_pad($mAtsHrs,2,"0",STR_PAD_LEFT)+":"+str_pad($mAtsMins,2,"0",STR_PAD_LEFT);
							$rw_pg['Ats'] = $Ats;
						}
							
						if($PeopleWatchedChnl >0 && $All_Viewers >0) {
							$Share_Ch = round(($Grp * 100) /(($PeopleWatchedChnl * 100) / $All_Viewers),2);
						}
						else {
							$Share_Ch = 0;
						}
						$rw_pg['Share_Ch'] = $Share_Ch;
						
						if($PeopleWatched >0 && $All_Viewers >0) {
							$Share_T  = round(($Grp * 100) /(($PeopleWatched * 100) / $All_Viewers),2);
						}
						else {
							$Share_T  = 0;
						}
						$rw_pg['Share_T'] = $Share_T;
						*/
					}
					
					
				}
				// End Calculation





				
				$program_data[] = $rw_pg;
				
				$mslno++;
			}
		
			mysql_free_result($pg_rslt);
		} 
		catch (Exception $e) {

		}
		
		return $program_data;
	}
	
	
	public function gmttolocal($toMarket,$sTseg) {
		$localTime   = $sTseg;

		try {
			$mstmkt_sql  = "select mkt_code,mkt_name,mkt_gmt,mkt_cur from mst_market where mkt_code = " . $toMarket;
			$mstmkt_rslt = mysql_query($mstmkt_sql);
			if(mysql_num_rows($mstmkt_rslt) >0) {
				$rw_dt = mysql_fetch_assoc($mstmkt_rslt);
				$GmtDiff = $rw_dt['mkt_gmt'];
				$lnST = intval(substr($sTseg,0,2))*60 + intval(substr($sTseg,2,2));
	
				if($GmtDiff == 0) {
					$localTime = $sTseg;
				}
				else {
					$lnST = $lnST - $GmtDiff;
		
					if($lnST < (6 * 60)) {
						$lnST = $lnST + (24 * 60);
					}
					if($lnST >= (30 * 60)) {
						$lnST = $lnST - (24 * 60);
					}
					$localTime = str_pad(intval($lnST/60),2,'0',STR_PAD_LEFT) . str_pad(intval(($lnST % 60)),2,'0',STR_PAD_LEFT);
				}
				
			}
			mysql_free_result($mstmkt_rslt);
		}
		catch (Exception $e) {
		
		}			
		return $localTime;
	}
	
	
	public function get_time_slots($pTime1,$pTime2,$mBrk=15,$cDur=1) {
		
		$Rv = array();
		
		try {
		
			$TimeJumper  = ($mBrk * 60) - 1;
			
			// No of Slots
			$mDur  = $this->caluate_duration($pTime1,$pTime2);
			$mMins = round(intval(substr($mDur,0,2)) * 60 + intval(substr($mDur,2,2)),0);
			$Slot15= $mMins / $mBrk;
			
			if(($mMins % $mBrk) > 0) {
				$Slot15 = intval($Slot15) + 1;
			}
			//**
			$preal_time1 = $this->to_real_time($pTime1);
			$preal_time2 = $this->to_real_time($pTime2);
			
			
			if(intval(substr($pTime1,0,2)) >= 24) {
				$mDtime1 = new DateTime('2000-01-02 ' . $preal_time1 . ":00");
			}
			else {
				$mDtime1 = new DateTime('2000-01-01 ' . $preal_time1 . ":00");
			}
				
			if(intval(substr($pTime2,0,2)) >= 24) {
				$mDtime2 = new DateTime('2000-01-02 ' . $preal_time2 . ":00");
			}
			else {
				$mDtime2 = new DateTime('2000-01-01 ' . $preal_time2 . ":00");
			}
	
			if($mDtime1 > $mDtime2) {
				$mDtime2 = $mDtime2->add(new DateInterval('PT86400S')); // skip a day for program starting at 29:00 and ending at 6:30
			}		
			
			$xDate = new DateTime('2000-01-01 06:00:00');
			$yDate = new DateTime('2000-01-03 05:59:59');
			
			$xTesg1 = 0;
			while($xDate <= $yDate) {
				if($xDate >= $mDtime1) {
					break;
				}
				$xTesg1 = $xTesg1 + 1;
				$xDate  = $xDate->add(new DateInterval('PT'. $TimeJumper .'S'));
			}
			
			$xDate = new DateTime('2000-01-01 06:00:00');
			$yDate = new DateTime('2000-01-03 05:59:59');
			
			$xTesg2 = 0;
			while($xDate <= $yDate) {
				if($xDate >= $mDtime2) {
					break;
				}
				$xTesg2 = $xTesg2 + 1;
				$xDate  = $xDate->add(new DateInterval('PT'. $TimeJumper .'S'));
			}
			
			
			// Avoid time segment to correct the duration of program
			if($cDur == 1) {
				$No_of_tsegs = ($xTesg2 - $xTesg1)+1;
				
				$remainSlots = $No_of_tsegs - $Slot15;
			
				if($remainSlots != 0) {
					// The remaining slots must be avoided from starting or ending time
					// Time took from first slot
					$FirstSlot = new DateTime('2000-01-01 06:00:00');
						  $inc = (($xTesg1-1) * ($TimeJumper+1));
					$FirstSlot = $FirstSlot->add(new DateInterval('PT'. $inc .'S'));
					
					$tmp_First_Slot      = $FirstSlot;
								   $sinc = ($mBrk-1) * 60;
					$tmp_First_Slot      = $tmp_First_Slot->add(new DateInterval('PT'. $sinc .'S'));
					$interval_First_Slot = $tmp_First_Slot->diff($mDtime1);
					$Sec_From_First_Slot = ($interval_First_Slot->days * 24 * 60 * 60) + ($interval_First_Slot->h * 60 * 60) +  ($interval_First_Slot->i * 60) +  ($interval_First_Slot->s);				
					
					// Time took from Last slot
					$LastSlot  = new DateTime('2000-01-01 06:00:00');
						  $inc = (($xTesg2-1) * ($TimeJumper+1));
					$LastSlot  = $LastSlot->add(new DateInterval('PT'. $inc .'S'));	  
					
					$Dif_From_Last_Slot = $mDtime2->diff($LastSlot);
					$Sec_From_Last_Slot = ($Dif_From_Last_Slot->days * 24 * 60 * 60) + ($Dif_From_Last_Slot->h * 60 * 60) +  ($Dif_From_Last_Slot->i * 60) +  ($Dif_From_Last_Slot->s);	
						
					if($Sec_From_First_Slot >= $Sec_From_Last_Slot) {
						$xTesg2 = $xTesg2 - $remainSlots;
					}
					else {
						$xTesg1 = $xTesg1 + $remainSlots;
					}
			
				}
			}
			
			$Rv = array(str_pad($xTesg1,2,"0",STR_PAD_LEFT),str_pad($xTesg2,2,"0",STR_PAD_LEFT));
		}
		catch (Exception $e) {
		
		}
		return $Rv;
	}
	
	public function caluate_duration($pTime1,$pTime2) {
		$rv_Dur ="00:00";
		
		try {
			
			$preal_time1 = $this->to_real_time($pTime1);
			$preal_time2 = $this->to_real_time($pTime2);
			
			
			if(intval(substr($pTime1,0,2)) >= 24) {
				$mDtime1 = new DateTime('2000-01-02 ' . $preal_time1 . ":00");
			}
			else {
				$mDtime1 = new DateTime('2000-01-01 ' . $preal_time1 . ":00");
			}
				
			if(intval(substr($pTime2,0,2)) >= 24) {
				$mDtime2 = new DateTime('2000-01-02 ' . $preal_time2 . ":00");
			}
			else {
				$mDtime2 = new DateTime('2000-01-01 ' . $preal_time2 . ":00");
			}
	
			if($mDtime1 > $mDtime2) {
				$mDtime2 = $mDtime2->add(new DateInterval('PT86400S')); // skip a day for program starting at 29:00 and ending at 6:30
			}
		
			$interval = $mDtime2->diff($mDtime1);
			$DurSec = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) +  ($interval->i * 60) +  ($interval->s);
			$DurSec = $DurSec + 60;
			$hrs  = intval(round($DurSec / 3600,3));
			$mins = intval(round(($DurSec - ($hrs * 3600))/60,3));
			$Secs = $DurSec - (($hrs * 3600) + ($mins * 60));
			
			$rv_Dur =str_pad($hrs,2,"0",STR_PAD_LEFT) . ":" . str_pad($mins,2,"0",STR_PAD_LEFT);
		}
		catch (Exception $e) {
		
		}

		return $rv_Dur;
	}
	
	public function to_real_time($pTime) {
		$RealTime = "00:00";
		
		try {
			$fSlot = substr($pTime,0,2);
			if(intval($fSlot) >=24) {
				$fSlot = str_pad(round(intval($fSlot),0) - 24,2,"0",STR_PAD_LEFT);
			}
			$RealTime = $fSlot . ":" . substr($pTime,3,2);
		}
		catch (Exception $e) {
		
		}			
		return $RealTime;
	}
	
}

?>