<?php
define("STATE_ID","3");
define("DATA_CACHE_TIMEOUT","9999");
/**
* Class to import data from SECC into SRDB
*
*@author Rochak Chauhan
*/
class ImportController extends Controller {
public function actionIndex() {
$t1=date("r");
$ts1=microtime(true);
$tot=0;
echo "\r\n<p> ******** Started at $t1 ******** </p>\r\n";
$connection = Yii::app()->db;
$command = $connection->createCommand('SELECT * FROM seccsirsa LIMIT 0,10');
$rows = $command->queryAll(); //executes the SQL statement and returns the first row of the
//$res = array();
foreach ($rows as $row) {
extract($row);
echo "\r\n<p>Full Name: $Name_en ($Name_h) </p>\r\n";
$SRDB_District_ID=$this->getDistrictId($District);
}
$t2=date("r");
$ts2=microtime(true);
$diff=($ts2-$ts1);
echo "\r\n<p> ******** Stopped at $t2 ******** </p>\r\n";
echo "\r\n<p> ******** Total Execution Time: $diff microseconds ******** </p>\r\n";
echo "\r\n<p> ******** Total Records Imported: $tot ******** </p>\r\n";
}
/**
*Function to get District ID from District Name
*
*@param string $District
*@param int
*/
private function getDistrictId($District){
//Select if record exists
$District=trim(strtolower($District));
$connection = Yii::app()->db;
$connection->active=true;
$command = $connection->createCommand("SELECT LR_ID FROM tblLandRegion WHERE lower(LR_Name) = '$District' AND LR_Type='District' ");
$row = $command->queryRow();
//If no record found
if($row===false){
//Insert record in DB (Master Table)
$command = $connection->createCommand("INSERT INTO tblLandRegion (LR_Name,LR_Type) VALUES('$District', 'District') ");
$rowCount=$command->execute();
if($rowCount==0){
die("\r\n<hr />FATAL ERROR 0x01: Failed to insert $District in Master table :: ".__FILE__." at line nunber ".__LINE__);
}
else{
//Cache and Return the PK/ID of the new inserted record.
$row = Yii::app()->cache->get('cache_'.$District."_".STATE_ID);
if (($row === false) || row($res)) {
$District=trim(strtolower($District));
$connection = Yii::app()->db;
$command = $connection->createCommand("SELECT LR_ID FROM tblLandRegion WHERE lower(LR_Name) = '$District' AND LR_Type='District' ORDER BY LR_ID DESC");
$row = $command->queryRow();
Yii::app()->cache->set('cache_'.$District."_".STATE_ID, $row, DATA_CACHE_TIMEOUT);
}
$District_ID=$row['LR_ID'];
//Insert record in DB (Reference Table)
$command = $connection->createCommand("INSERT INTO tblDistrict (District_ID, State_ID) VALUES('$District_ID', '".STATE_ID."') ");
$rowCount=$command->execute();
if($rowCount==0){
die("\r\n<hr />FATAL ERROR 0x02: Failed to insert $District in Reference table :: ".__FILE__." at line nunber ".__LINE__);
}
return $District_ID;
}
}
else{
//Return the PK/ID of the record.
return $row['LR_ID'];
}
}
/**
*Function to get City ID from City Name
*
*@param string $City
*@param int
*/
private function getCityId($City){
//Select if record exists
$City=trim(strtolower($City));
$connection = Yii::app()->db;
$connection->active=true;
$command = $connection->createCommand("SELECT LR_ID FROM tblLandRegion WHERE lower(LR_Name) = '$City' AND LR_Type='City' ");
$row = $command->queryRow();
//If no record found
if($row===false){
//Insert record in DB (Master Table)
$command = $connection->createCommand("INSERT INTO tblLandRegion (LR_Name,LR_Type) VALUES('$City', 'City') ");
$rowCount=$command->execute();
if($rowCount==0){
die("\r\n<hr />FATAL ERROR 0x01: Failed to insert $City in Master table :: ".__FILE__." at line nunber ".__LINE__);
}
else{
//Cache and Return the PK/ID of the new inserted record.
$row = Yii::app()->cache->get('cache_'.$City."_".$District);
if (($row === false) || row($res)) {
$District=trim(strtolower($District));
$connection = Yii::app()->db;
$command = $connection->createCommand("SELECT LR_ID FROM tblLandRegion WHERE lower(LR_Name) = '$City' AND LR_Type='City' ORDER BY LR_ID DESC");
$row = $command->queryRow();
Yii::app()->cache->set('cache_'.$City."_".$District, $row, DATA_CACHE_TIMEOUT);
}
$City_ID=$row['LR_ID'];
//Insert record in DB (Reference Table)
$command = $connection->createCommand("INSERT INTO tblCity (District_ID, City_ID) VALUES('$District_ID', '$City_ID') ");
$rowCount=$command->execute();
if($rowCount==0){
die("\r\n<hr />FATAL ERROR 0x02: Failed to insert $District in Reference table :: ".__FILE__." at line nunber ".__LINE__);
}
return $City_ID;
}
}
else{
//Return the PK/ID of the record.
return $row['LR_ID'];
}
}
}
?>