<?php

class survey_Model {
	
	public function new_survey($Srv_Obj) {
		$sql_survey = 'insert into survey (srv_desc,srv_type,srv_mrkt,srv_stdt,srv_eddt,user_id) values (
						"' . $Srv_Obj->get_srv_desc() . '"
					   ,"' . $Srv_Obj->get_srv_type() . '"
					   ,"' . $Srv_Obj->get_srv_mrkt() . '"
					   ,"' . $Srv_Obj->get_srv_stdt() . '"
					   ,"' . $Srv_Obj->get_srv_eddt() . '"
					   ,"' . $Srv_Obj->get_user_id()  . '")';

		mysql_query($sql_survey);
		
		if(mysql_insert_id() >0) {
			return true;
		}
		else {
			return false;	
		}
		
	}
	
	public function update_survey($Srv_Obj) {
		$sql_survey = 'update survey 
						set srv_desc  = "' . $Srv_Obj->get_srv_desc() . '"
							,srv_type = "' . $Srv_Obj->get_srv_type() . '"
							,srv_mrkt = "' . $Srv_Obj->get_srv_mrkt() . '"
							,srv_stdt = "' . $Srv_Obj->get_srv_stdt() . '"
							,srv_eddt = "' . $Srv_Obj->get_srv_eddt() . '"
						where srv_id = ' . $Srv_Obj->get_srv_id();


		mysql_query($sql_survey);
		
		if(strlen(mysql_error()) <=0) {
			return true;
		}
		else {
			return false;	
		}
		
	}	
	
	public function get_survey_details($survey_id) {
		
		$survey_details = array();
		
		$survey_details['id']=0;
		$survey_details['name']="";
		$survey_details['market']="";
		$survey_details['srv_mrkt']=0;
		$survey_details['type']=0;
		$survey_details['type_desc'] = "";
		$survey_details['population']=0;
		$survey_details['sample']=0;
		$survey_details['starting_date']="";
		$survey_details['ending_date']="";
		$survey_details['rw_srv_stdt']="";
		$survey_details['rw_srv_eddt']="";
		$survey_details['srvdays'] =0;
		$survey_details['uid'] =0;
		
		
		try {
			$sql_survey = "select srv_id,srv_desc,survey.srv_mrkt,mst_market.mkt_name,srv_type,mst_srv_type.type_desc,
							DATE_FORMAT(srv_stdt,'%d-%m-%Y') as srv_stdt,
							DATE_FORMAT(srv_eddt,'%d-%m-%Y') as srv_eddt,
							DATEDIFF(srv_eddt,srv_stdt) as srvdays,
							srv_stdt as rw_srv_stdt,srv_eddt as rw_srv_eddt, survey.user_id
							from survey left join mst_market on survey.srv_mrkt = mst_market.mkt_code
							left join mst_srv_type on survey.srv_type = mst_srv_type.type_code
							where srv_id = " . $survey_id;
							
			$rslt_survey = mysql_query($sql_survey);
			
			if(mysql_num_rows($rslt_survey) >0) {
				$survey_rw = mysql_fetch_assoc($rslt_survey);
				
				$survey_details['id']	 =	$survey_rw['srv_id'];
				$survey_details['name']	 =	$survey_rw['srv_desc'];
				$survey_details['market']=	$survey_rw['mkt_name'];
				$survey_details['srv_mrkt']=$survey_rw['srv_mrkt'];
				$survey_details['type']  = 	$survey_rw['srv_type'];
				$survey_details['type_desc']  = 	$survey_rw['type_desc'];
				$survey_details['starting_date']=	$survey_rw['srv_stdt'];
				$survey_details['ending_date']  =	$survey_rw['srv_eddt'];
				$survey_details['srvdays'] = $survey_rw['srvdays'];
				$survey_details['rw_srv_stdt']= $survey_rw['rw_srv_stdt'];
				$survey_details['rw_srv_eddt']= $survey_rw['rw_srv_eddt'];
				$survey_details['uid'] = $survey_rw['user_id'];
				
				mysql_free_result($rslt_survey);
				
				
				// Fetch population
				$pop_sql = "select population from mst_population where mkt_code = " . $survey_details['srv_mrkt'] . " order by entry_dt desc limit 1";
				$rslt_pop= mysql_query($pop_sql);
				
				if(mysql_num_rows($rslt_pop) >0) {
					$pop_rw = mysql_fetch_assoc($rslt_pop);
					$survey_details['population'] = $pop_rw['population'] * 1000;
				}
				
				mysql_free_result($rslt_pop);
				
				// Fetch Total Samples
				$sam_sql = "select sum(weight) as tot_sam from survey_resp 
									where srv_mrkt = " . $survey_details['srv_mrkt'] . " 
									  and srv_type = ". $survey_details['type'] . "
									  and srv_dt >='" . $survey_details['rw_srv_stdt'] . "' 
									  and srv_dt <='" . $survey_details['rw_srv_eddt'] . "'";
									  
				$rslt_sam = mysql_query($sam_sql);
				
				if(mysql_num_rows($rslt_sam) >0) {
					$sam_rw = mysql_fetch_assoc($rslt_sam);
					$survey_details['sample'] = round($sam_rw['tot_sam'],0);
				}
			}
		} 
		catch (Exception $e) {

		}
		
		return $survey_details;
	}
	
	public function list_survey($user_id) {
		$survey_lsit = array();
		
		$sql_survey = "select srv_id,srv_desc,srv_type,srv_mrkt,srv_stdt,srv_eddt from survey 
						where user_id = " . $user_id . " order by srv_mrkt,srv_eddt desc";
		$rslt_survey= mysql_query($sql_survey);
		while($srv_rw = mysql_fetch_assoc($rslt_survey)) {
			$survey_lsit[] = $srv_rw;
		}
		mysql_free_result($rslt_survey);
		
		return $survey_lsit;
	}
	
	public function data_statistics($survey_type=0)  {
		
		$data_statistics = array();
		$data_statistics['total'] = 0;
		$data_statistics['male']   = 0;
		$data_statistics['female'] = 0;
				
		$sql_mrkt  = "select mkt_code from mst_market order by 1";
		$rslt_mrkt = mysql_query($sql_mrkt);
		
		while($mrkt_rw=mysql_fetch_row($rslt_mrkt)) {
			$sql_resp = 'select survey_resp.respid,survey_resp.weight,
							survey_respsocio.factorid,survey_respsocio.opt_id,
							mst_factor.factordes,
							mst_socio.opt_desc
						from survey_resp
							left join survey_respsocio 
								on survey_resp.srv_mrkt = survey_respsocio.srv_mrkt and survey_resp.respid = survey_respsocio.respid
							left join mst_factor 
								on survey_respsocio.srv_mrkt = mst_factor.srv_mrkt and survey_respsocio.factorid = mst_factor.factorid
							left join mst_socio 
								on survey_respsocio.srv_mrkt = mst_socio.srv_mrkt and survey_respsocio.factorid = mst_socio.factorid 
								   and survey_respsocio.opt_id = mst_socio.opt_id
						where (survey_resp.srv_mrkt = '. $mrkt_rw[0];
						
						if($survey_type >0) {
							$sql_resp .= " and survey_resp.srv_type = " . $survey_type;
						}
						$sql_resp .= ") ";
						
						$sql_resp .= ' and (mst_factor.factordes like  "%Gender%")' ;
						 
			$sql_rslt = mysql_query($sql_resp);
			
			while($resp_rw = mysql_fetch_assoc($sql_rslt)) {
				$data_statistics['total'] = $data_statistics['total'] + $resp_rw['weight'];
				if(strtoupper(trim($resp_rw['opt_desc'])) == "MALE") {
					$data_statistics['male'] = $data_statistics['male'] + $resp_rw['weight'];
				}
				if(strtoupper(trim($resp_rw['opt_desc'])) == "FEMALE") {
					$data_statistics['female'] = $data_statistics['female'] + $resp_rw['weight'];
				}
			}
			mysql_free_result($sql_rslt);
			
		}
		mysql_free_result($rslt_mrkt);
	
		return $data_statistics;
	}
	
	public function del_survey($survey_id) {
		$sql_del = "delete from survey where srv_id = " . $survey_id;
		
		$rslt_del = mysql_query($sql_del);
		
		if(strlen(mysql_error()) <=0) {
			return true;
		}
		else {
			return false;
		}
	}
	
	public function list_market() {
		$sql_market = "select * from mst_market order by mkt_name";
		$rslt_market = mysql_query($sql_market);
		
		$market_rw = array();
		if(mysql_num_rows($rslt_market) >0) {
			while($mkt_rw = mysql_fetch_assoc($rslt_market)) {
				$market_rw[] = $mkt_rw;
			}
			mysql_free_result($rslt_market);
		}
		
		return $market_rw;
	}
	
	public function list_stype() {
		$sql_type = "select * from mst_srv_type order by type_desc";
		$rslt_type = mysql_query($sql_type);
		
		$type_rw = array();
		if(mysql_num_rows($rslt_type) >0) {
			while($ty_rw = mysql_fetch_assoc($rslt_type)) {
				$type_rw[] = $ty_rw;
			}
			mysql_free_result($rslt_type);
		}
		
		return $type_rw;
	}	
	
	
	public function list_time_segment($tmode) {
		
		$tseg_array = array();
		
		if($tmode==1) { // 30 mins
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data['tsname'],7,2) .":" . substr($rw_data['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data['tsid'],$tsname);
			}
		}
		elseif($tmode==2) { // Day part
			$msql = "select tseg1,tseg2,tseg_desc from mst_dp where dp_id=1 order by tseg_ordr";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				$tseg_array[] = array($rw_data['tseg1'],$rw_data['tseg2'],$rw_data['tseg_desc']);
			}
		}
		
		return $tseg_array;
	}


	public function list_time_segment2($tmode) {
		
		$tseg_array = array();
		
		if($tmode==1) { // 24 Hours
			$tsname = "Interval: " . "00:00". " - " . "24:00";
			$tseg_array[] = array(1,96,$tsname);
		}
		elseif($tmode==2) { // 8 Hours
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=31;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==3) { // 4 Hours
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=15;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==4) { // 3 Hours
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=11;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==5) { // 2 Hours
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=7;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==6) { // 1 Hour
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=3;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==7) { // 30 Mins
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				for($xr=1;$xr<=1;$xr++) {
					$rw_data2 = mysql_fetch_assoc($rslt);
				}
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data2['tsname'],7,2) .":" . substr($rw_data2['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data2['tsid'],$tsname);
			}
		}
		elseif($tmode==8) { // 15 Mins
			$msql = "select tsid,tsname from mst_timesegment order by tsid";
			$rslt = mysql_query($msql);
			while($rw_data = mysql_fetch_assoc($rslt)) {
				$tsname = "Interval: " . substr($rw_data['tsname'],0,2) .":" . substr($rw_data['tsname'],2,2) . " - " . substr($rw_data['tsname'],7,2) .":" . substr($rw_data['tsname'],9,2);
				$tseg_array[] = array($rw_data['tsid'],$rw_data['tsid'],$tsname);
			}
		}
		
		return $tseg_array;
	}

	
}

?>