WebSVN – cheapmusic – Diff – /www/seopanel/controllers/report.ctrl.php



		}
		
		$fromTimeLabel = date('Y-m-d', $fromTime);
		$toTimeLabel = date('Y-m-d', $toTime);
		foreach($this->seLIst as $seInfo){
			$sql = "select min(`rank`) as `rank`,result_date from searchresults 
			where keyword_id=$keywordId and searchengine_id=".$seInfo['id']."
			and (result_date='$fromTimeLabel' or result_date='$toTimeLabel')
			group by result_date order by result_date DESC limit 0, 2";
			$reportList = $this->db->select($sql);
			$reportList = array_reverse($reportList);

		$subSql = "select [col] from keywords k,searchresults r, websites w 
		where k.id=r.keyword_id and k.website_id=w.id $conditions
		and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeTxt) . "'
		group by k.id";
 
		$unionOrderCol = ($orderCol == "keyword") ? "name" : "`rank`";
		$sql = "(". str_replace("[col]", "k.id,k.name,min(`rank`) `rank`,w.name website,w.url weburl", $subSql) .") 
		UNION 
		(select k.id,k.name,1000,w.name website,w.url weburl 
		from keywords k, websites w  
		where w.id=k.website_id $conditions and k.id not in
		(". str_replace("[col]", "distinct(k.id)", $subSql) ."))

				and searchengine_id={$matchInfo['se_id']} and result_date='$resultDate'";
				$this->db->query($sql);
			}
		}
		
		$sql = "insert into searchresults(keyword_id,searchengine_id,`rank`,`time`,result_date)
				values({$matchInfo['keyword_id']},{$matchInfo['se_id']},{$matchInfo['rank']},$time,'$resultDate')";
		$this->db->query($sql);
		
		$recordId = $this->db->getMaxId('searchresults');		
		$sql = "insert into searchresultdetails(searchresult_id,url,title,description)

		$this->render('report/showquickrankchecker');
	}
	
	#function to save keyword cron trcker for multiple execution of cron same day
	function saveCronTrackInfo($keywordId, $seId, $time) {
	    $sql = "Insert into keywordcrontracker(keyword_id,searchengine_id,`time`) values($keywordId,$seId,$time)";
	    $this->db->query($sql);
	}
	
	# function to check whether cron executed for a particular keyword and search engine
	function isCronExecuted($keywordId, $seId, $time) {
	    $sql = "select keyword_id from keywordcrontracker where keyword_id=$keywordId and searchengine_id=$seId and `time`=$time";
        $info = $this->db->select($sql, true);
	    return empty($info['keyword_id']) ? false : true;
	}
	
    # function to show system reports 

    		$subSql = "select [col] from keywords k,searchresults r, websites w
    		where k.id=r.keyword_id and k.website_id=w.id $conditions
    		and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeShort) . "'
    		group by k.id";
    		
    		$unionOrderCol = ($orderCol == "keyword") ? "name" : "`rank`";
    		$sql = "(". str_replace("[col]", "k.id,k.name,min(`rank`) `rank`,w.name website,w.url weburl", $subSql) .")
    		UNION
    		(select k.id,k.name,1000,w.name website,w.url weburl
    		from keywords k, websites w
    		where w.id=k.website_id $conditions and k.id not in
    		(". str_replace("[col]", "distinct(k.id)", $subSql) ."))

				# pagespeed reports
				$report = $pageSpeedCtrler->__getWebsitePageSpeedReport($listInfo['id'], $fromTime, $toTime);
				$report = $report[0];				
				$listInfo['desktop_speed_score'] = empty($report['desktop_speed_score']) ? "-" : $report['desktop_speed_score']." ".$report['rank_diff_desktop_speed_score'];
				$listInfo['mobile_speed_score'] = empty($report['mobile_speed_score']) ? "-" : $report['mobile_speed_score']." ".$report['rank_diff_mobile_speed_score'];
 
								
				$listInfo['dirsub']['total'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id']);
				$listInfo['dirsub']['active'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id'], true);
				$websiteRankList[] = $listInfo;
			}

					'Bing '.$spTextHome['Backlinks'],
					'Google '.$spTextHome['Indexed'],
					'Bing '.$spTextHome['Indexed'],
					$spTextPS['Desktop Speed'],
					$spTextPS['Mobile Speed'],
 
					$_SESSION['text']['common']['Total'].' Submission',
					$_SESSION['text']['common']['Active'].' Submission',
				);
				
				$exportContent .= createExportContent( $headList);

						strip_tags($websiteInfo['msn']['backlinks']),
						strip_tags($websiteInfo['google']['indexed']),					
						strip_tags($websiteInfo['msn']['indexed']),
						strip_tags($websiteInfo['desktop_speed_score']),
						strip_tags($websiteInfo['mobile_speed_score']),
 
						$websiteInfo['dirsub']['total'],					
						$websiteInfo['dirsub']['active'],
					);
					$exportContent .= createExportContent( $valueList);
				} 

Subversion Repositories cheapmusic

(root)/www/seopanel/controllers/report.ctrl.php – Rev 103 → 155

Rev 103		Rev 155
Line 36...		Line 36...
36	`}`	36	`}`
37		37
38	`$fromTimeLabel = date('Y-m-d', $fromTime);`	38	`$fromTimeLabel = date('Y-m-d', $fromTime);`
39	`$toTimeLabel = date('Y-m-d', $toTime);`	39	`$toTimeLabel = date('Y-m-d', $toTime);`
40	`foreach($this->seLIst as $seInfo){`	40	`foreach($this->seLIst as $seInfo){`
41	`$sql = "select min(rank) as rank,result_date from searchresults`	41	$sql = "select min(`rank`) as `rank`,result_date from searchresults
42	`where keyword_id=$keywordId and searchengine_id=".$seInfo['id']."`	42	`where keyword_id=$keywordId and searchengine_id=".$seInfo['id']."`
43	`and (result_date='$fromTimeLabel' or result_date='$toTimeLabel')`	43	`and (result_date='$fromTimeLabel' or result_date='$toTimeLabel')`
44	`group by result_date order by result_date DESC limit 0, 2";`	44	`group by result_date order by result_date DESC limit 0, 2";`
45	`$reportList = $this->db->select($sql);`	45	`$reportList = $this->db->select($sql);`
46	`$reportList = array_reverse($reportList);`	46	`$reportList = array_reverse($reportList);`
Line 151...		Line 151...
151	`$subSql = "select [col] from keywords k,searchresults r, websites w`	151	`$subSql = "select [col] from keywords k,searchresults r, websites w`
152	`where k.id=r.keyword_id and k.website_id=w.id $conditions`	152	`where k.id=r.keyword_id and k.website_id=w.id $conditions`
153	`and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeTxt) . "'`	153	`and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeTxt) . "'`
154	`group by k.id";`	154	`group by k.id";`
155		155
156	`$unionOrderCol = ($orderCol == "keyword") ? "name" : "rank";`	156	$unionOrderCol = ($orderCol == "keyword") ? "name" : "`rank`";
157	`$sql = "(". str_replace("[col]", "k.id,k.name,min(rank) rank,w.name website,w.url weburl", $subSql) .")`	157	$sql = "(". str_replace("[col]", "k.id,k.name,min(`rank`) `rank`,w.name website,w.url weburl", $subSql) .")
158	`UNION`	158	`UNION`
159	`(select k.id,k.name,1000,w.name website,w.url weburl`	159	`(select k.id,k.name,1000,w.name website,w.url weburl`
160	`from keywords k, websites w`	160	`from keywords k, websites w`
161	`where w.id=k.website_id $conditions and k.id not in`	161	`where w.id=k.website_id $conditions and k.id not in`
162	`(". str_replace("[col]", "distinct(k.id)", $subSql) ."))`	162	`(". str_replace("[col]", "distinct(k.id)", $subSql) ."))`
Line 839...		Line 839...
839	`and searchengine_id={$matchInfo['se_id']} and result_date='$resultDate'";`	839	`and searchengine_id={$matchInfo['se_id']} and result_date='$resultDate'";`
840	`$this->db->query($sql);`	840	`$this->db->query($sql);`
841	`}`	841	`}`
842	`}`	842	`}`
843		843
844	`$sql = "insert into searchresults(keyword_id,searchengine_id,rank,time,result_date)`	844	$sql = "insert into searchresults(keyword_id,searchengine_id,`rank`,`time`,result_date)
845	`values({$matchInfo['keyword_id']},{$matchInfo['se_id']},{$matchInfo['rank']},$time,'$resultDate')";`	845	`values({$matchInfo['keyword_id']},{$matchInfo['se_id']},{$matchInfo['rank']},$time,'$resultDate')";`
846	`$this->db->query($sql);`	846	`$this->db->query($sql);`
847		847
848	`$recordId = $this->db->getMaxId('searchresults');`	848	`$recordId = $this->db->getMaxId('searchresults');`
849	`$sql = "insert into searchresultdetails(searchresult_id,url,title,description)`	849	`$sql = "insert into searchresultdetails(searchresult_id,url,title,description)`
Line 894...		Line 894...
894	`$this->render('report/showquickrankchecker');`	894	`$this->render('report/showquickrankchecker');`
895	`}`	895	`}`
896		896
897	`#function to save keyword cron trcker for multiple execution of cron same day`	897	`#function to save keyword cron trcker for multiple execution of cron same day`
898	`function saveCronTrackInfo($keywordId, $seId, $time) {`	898	`function saveCronTrackInfo($keywordId, $seId, $time) {`
899	`$sql = "Insert into keywordcrontracker(keyword_id,searchengine_id,time) values($keywordId,$seId,$time)";`	899	$sql = "Insert into keywordcrontracker(keyword_id,searchengine_id,`time`) values($keywordId,$seId,$time)";
900	`$this->db->query($sql);`	900	`$this->db->query($sql);`
901	`}`	901	`}`
902		902
903	`# function to check whether cron executed for a particular keyword and search engine`	903	`# function to check whether cron executed for a particular keyword and search engine`
904	`function isCronExecuted($keywordId, $seId, $time) {`	904	`function isCronExecuted($keywordId, $seId, $time) {`
905	`$sql = "select keyword_id from keywordcrontracker where keyword_id=$keywordId and searchengine_id=$seId and time=$time";`	905	$sql = "select keyword_id from keywordcrontracker where keyword_id=$keywordId and searchengine_id=$seId and `time`=$time";
906	`$info = $this->db->select($sql, true);`	906	`$info = $this->db->select($sql, true);`
907	`return empty($info['keyword_id']) ? false : true;`	907	`return empty($info['keyword_id']) ? false : true;`
908	`}`	908	`}`
909		909
910	`# function to show system reports`	910	`# function to show system reports`
Line 1018...		Line 1018...
1018	`$subSql = "select [col] from keywords k,searchresults r, websites w`	1018	`$subSql = "select [col] from keywords k,searchresults r, websites w`
1019	`where k.id=r.keyword_id and k.website_id=w.id $conditions`	1019	`where k.id=r.keyword_id and k.website_id=w.id $conditions`
1020	`and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeShort) . "'`	1020	`and r.searchengine_id=".intval($orderCol)." and r.result_date='" . addslashes($toTimeShort) . "'`
1021	`group by k.id";`	1021	`group by k.id";`
1022		1022
1023	`$unionOrderCol = ($orderCol == "keyword") ? "name" : "rank";`	1023	$unionOrderCol = ($orderCol == "keyword") ? "name" : "`rank`";
1024	`$sql = "(". str_replace("[col]", "k.id,k.name,min(rank) rank,w.name website,w.url weburl", $subSql) .")`	1024	$sql = "(". str_replace("[col]", "k.id,k.name,min(`rank`) `rank`,w.name website,w.url weburl", $subSql) .")
1025	`UNION`	1025	`UNION`
1026	`(select k.id,k.name,1000,w.name website,w.url weburl`	1026	`(select k.id,k.name,1000,w.name website,w.url weburl`
1027	`from keywords k, websites w`	1027	`from keywords k, websites w`
1028	`where w.id=k.website_id $conditions and k.id not in`	1028	`where w.id=k.website_id $conditions and k.id not in`
1029	`(". str_replace("[col]", "distinct(k.id)", $subSql) ."))`	1029	`(". str_replace("[col]", "distinct(k.id)", $subSql) ."))`
Line 1191...		Line 1191...
1191	`# pagespeed reports`	1191	`# pagespeed reports`
1192	`$report = $pageSpeedCtrler->__getWebsitePageSpeedReport($listInfo['id'], $fromTime, $toTime);`	1192	`$report = $pageSpeedCtrler->__getWebsitePageSpeedReport($listInfo['id'], $fromTime, $toTime);`
1193	`$report = $report[0];`	1193	`$report = $report[0];`
1194	`$listInfo['desktop_speed_score'] = empty($report['desktop_speed_score']) ? "-" : $report['desktop_speed_score']." ".$report['rank_diff_desktop_speed_score'];`	1194	`$listInfo['desktop_speed_score'] = empty($report['desktop_speed_score']) ? "-" : $report['desktop_speed_score']." ".$report['rank_diff_desktop_speed_score'];`
1195	`$listInfo['mobile_speed_score'] = empty($report['mobile_speed_score']) ? "-" : $report['mobile_speed_score']." ".$report['rank_diff_mobile_speed_score'];`	1195	`$listInfo['mobile_speed_score'] = empty($report['mobile_speed_score']) ? "-" : $report['mobile_speed_score']." ".$report['rank_diff_mobile_speed_score'];`
1196	`$listInfo['mobile_usability_score'] = empty($report['mobile_usability_score']) ? "-" : $report['mobile_usability_score']." ".$report['rank_diff_mobile_usability_score'];`	-
1197		1196
1198	`$listInfo['dirsub']['total'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id']);`	1197	`$listInfo['dirsub']['total'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id']);`
1199	`$listInfo['dirsub']['active'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id'], true);`	1198	`$listInfo['dirsub']['active'] = $dirCtrler->__getTotalSubmitInfo($listInfo['id'], true);`
1200	`$websiteRankList[] = $listInfo;`	1199	`$websiteRankList[] = $listInfo;`
1201	`}`	1200	`}`
Line 1225...		Line 1224...
1225	`'Bing '.$spTextHome['Backlinks'],`	1224	`'Bing '.$spTextHome['Backlinks'],`
1226	`'Google '.$spTextHome['Indexed'],`	1225	`'Google '.$spTextHome['Indexed'],`
1227	`'Bing '.$spTextHome['Indexed'],`	1226	`'Bing '.$spTextHome['Indexed'],`
1228	`$spTextPS['Desktop Speed'],`	1227	`$spTextPS['Desktop Speed'],`
1229	`$spTextPS['Mobile Speed'],`	1228	`$spTextPS['Mobile Speed'],`
1230	`$spTextPS['Mobile Usability'],`	-
1231	`$_SESSION['text']['common']['Total'].' Submission',`	1229	`$_SESSION['text']['common']['Total'].' Submission',`
1232	`$_SESSION['text']['common']['Active'].' Submission',`	1230	`$_SESSION['text']['common']['Active'].' Submission',`
1233	`);`	1231	`);`
1234		1232
1235	`$exportContent .= createExportContent( $headList);`	1233	`$exportContent .= createExportContent( $headList);`
Line 1245...		Line 1243...
1245	`strip_tags($websiteInfo['msn']['backlinks']),`	1243	`strip_tags($websiteInfo['msn']['backlinks']),`
1246	`strip_tags($websiteInfo['google']['indexed']),`	1244	`strip_tags($websiteInfo['google']['indexed']),`
1247	`strip_tags($websiteInfo['msn']['indexed']),`	1245	`strip_tags($websiteInfo['msn']['indexed']),`
1248	`strip_tags($websiteInfo['desktop_speed_score']),`	1246	`strip_tags($websiteInfo['desktop_speed_score']),`
1249	`strip_tags($websiteInfo['mobile_speed_score']),`	1247	`strip_tags($websiteInfo['mobile_speed_score']),`
1250	`strip_tags($websiteInfo['mobile_usability_score']),`	-
1251	`$websiteInfo['dirsub']['total'],`	1248	`$websiteInfo['dirsub']['total'],`
1252	`$websiteInfo['dirsub']['active'],`	1249	`$websiteInfo['dirsub']['active'],`
1253	`);`	1250	`);`
1254	`$exportContent .= createExportContent( $valueList);`	1251	`$exportContent .= createExportContent( $valueList);`
1255	`}`	1252	`}`