/mls-project/php/Extraction/Common/ExtractionDB.php

<?php
/**
 * Factory model extraction
 * Class ExtractionDB
 * Script used for execute sql queries
 * @category MLSProject
 * @package  Common
 * @author   Naheeda <incubator189@hotmail.com>
 * @date     18-Jul-2015
 */

class ExtractionDB 
{
    /**
     * common database connection variable
     * @var object
     */
    public $dbConnection;
    
    /**
     * ExtractionDB construct
     * Constructor, Initilizes common Database
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    20-JULY-2016
     */
    public function __construct()
    {
        global $dbconnection;
        $dbconnection = $this->dbConnection = new Db();
    }
    
    /**
     * ExtractionDB selectQuery
     * select query in which returns only one result
     * @param   String  columnName, list of database column name
     * @param   String  tableName,   database table name
     * @param   String  where condition
     * @return  Array row, associative array 
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    20-JULY-2016
     */
    public function selectQuery($columnName = '*',$tableName,$where)
    {
        $sql = "SELECT $columnName FROM $tableName WHERE $where";
        $result = $this->dbConnection->executeQuery($sql);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        return $row;     
    }
    
    /**
     * ExtractionDB selectQueryExecute
     * select query in which returns loop of result should execute
     * @param   String  columnName, list of database column name
     * @param   String  tableName,   database table name
     * @param   String  where condition
     * @return  Array result
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    20-JULY-2016
     */
    public function selectQueryExecute($columnName = '*',$tableName,$where)
    {
        $sql = "SELECT $columnName FROM $tableName WHERE $where";
        $result = $this->dbConnection->executeQuery($sql);
        return $result;     
    }

    /**
     * ExtractionDB selectQueryWithInnerjoin
     * select query with inner join
     * @param   String  columnName, list of database column name
     * @param   String  tableName,   database table name
     * @param   String  innerJoinTable,   database inner join table name
     * @param   String  on,   on condition
     * @param   String  where condition
     * @return  Array results
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    26-JULY-2016
     */
    public function selectQueryWithInnerjoin($columnName = '*',$tableName,$innerJoinTable,$on,$where) 
    {
        $sql = "SELECT $columnName
                FROM $tableName
                INNER JOIN $innerJoinTable
                ON $on
                WHERE $where";
        $results = $this->dbConnection->executeQuery($sql);
        
        return $results;
    }

    public function selectQueryforMultipleValues($columnName = '*',$tableName,$where)
    {
        $sql = "SELECT $columnName FROM $tableName WHERE $where";
        $result = $this->dbConnection->executeQuery($sql);
        while ($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
         // print_r($row);
            $serverInfo[] = $row;
        }
        return $serverInfo;     
    }
    
    public function selectQueryWithInnerjoinScalar($columnName = '*',$tableName,$innerJoinTable,$on,$and) 
    {
        $sql = "SELECT $columnName
                FROM $tableName
                INNER JOIN $innerJoinTable
                ON $on
                AND $and LIMIT 1";
        $results = $this->dbConnection->executeScalar($sql);
        
        return $results;
    }
    
    /**
     * ExtractionDB insertQuery
     * insert query, to insert in database
     * @param   String  columnName, list of database column name
     * @param   String  tableName,   database table name
     * @param   String  values 
     * @return  Array results
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    26-JULY-2016
     */
    public function insertQuery($tableName,$columnName,$values) 
    {
        $sql = "INSERT INTO $tableName 
                ($columnName) VALUES
                ($values)";
        $result = $this->dbConnection->executeNonQuery($sql);
        return $result;
    }
    
    /**
     * ExtractionDB updateQuery
     * update query, to update in database
     * @param   String  columnName, list of database column name
     * @param   String  tableName,   database table name
     * @param   String  where, where condition
     * @return  Array results
     * @author  Naheeda <incubator189@hotmail.com>
     * @date    26-JULY-2016
     */
    public function updateQuery($tableName,$columnName,$where) 
    {
         $sql = "UPDATE $tableName 
                SET $columnName
                WHERE $where";
        $result = $this->dbConnection->executeNonQuery($sql);
        //echo "\nupdated $columnName\n";
    }

/**
     * ExtractionDB  InsertObjectType
     * Update the tbl_server with the image type and image url support
     * @param  serverID,img_type,img_url_support,img_unique_id etc
     * @return the query for updating the status once the csv writing has started
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   19-July-2016
     */
    
    public function InsertObjectType($serverID,$resource,$img_type,$img_url_support,$img_unique_id)
    {
        $this->_insertObjectType       = "UPDATE tbl_server SET image_resource='$resource',image_type='$img_type',image_url_support='$img_url_support',image_unique_id='$img_unique_id' WHERE id=$serverID";
        return $this->_insertObjectType;
        
    }

/**
     * ExtractionDB  getServerClasses
     * get the servername and the classes
     * @param  serverID etc
     * @return the query for getting the servername and classname
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   19-July-2016
     */

    public function getServerClasses($server_id)
    {
        $sqlNormalServerClasses = "select
                                    s.id AS server_id,
                                    s.server_name,
                                    b.server_timestamp_field,
                                    b.server_unique_field,
                                    b.class_name,
                                    b.mandatory_query,
                                    b.id AS class_id
                                    from 
                                    tbl_server s 
                                    join
                                    tbl_server_class b
                                    on 
                                    s.id=b.tbl_server_id AND s.id=$server_id";
        $sql = "($sqlNormalServerClasses)";

        return $this->dbConnection->executeQuery($sql);
    }
    
    /**
     * ExtractionDB updateOffset
     * returns server url of the current server
     * @return  String  server url rets server url of the connected server
     * @author  Dhanya S <incubator133@hotmail.com>
     * @date    20-JUL-2016
     */
    public function updateOffset($serverID,$offset) {
        $sql = "update `tbl_server` SET offset_support = '{$offset}' 
                WHERE id = $serverID LIMIT 1";
        $this->dbConnection->executeNonQuery($sql);  
       
    }
    
    /**
     * ExtractionDB  getClassesforRestart
     * get the servername and the classes
     * @param  serverID etc
     * @return the query for getting the servername and classname
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   19-July-2016
     */

    public function getClassesforRestart($classID)
    {
    
    
        $this->_selectServerStatus        = "Select e.id as sectionid,
                                                e.status,
                                                cs.id as clsid
                                                FROM tbl_server_class cs join tbl_section e on 
                                                cs.id=e.tbl_class_id
                                                join tbl_server_cron_status as ct on ct.tbl_server_class_id= cs.id
                                                AND ct.extraction_type =3
                                                AND e.tbl_class_id=$classID
                                                AND (e.status=2 or e.status=0)";
        $noOfRowsInserted=$this->dbConnection->executeQuery($this->_selectServerStatus);
        $this->_num_rows = mysqli_num_rows($noOfRowsInserted);
        return $this->_num_rows;
    }
    
    /**
     * ExtractionDB  getClassesforRestartSplit
     * get the servername and the classes
     * @param  serverID etc
     * @return the query for getting the servername and classname
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   2-Aug-2016
     */

    public function getClassesforRestartSplit($serverID)
    {
    
    
        $this->_selectServerStatus        = "Select cs.id as clsid
                                                FROM tbl_server_class cs join tbl_server as st on st.id= cs.tbl_server_id
                                                AND cs.class_status is null
                                                AND st.id=$serverID";
        $noOfRowsInserted=$this->dbConnection->executeQuery($this->_selectServerStatus);
        //$this->_num_rows = mysqli_num_rows($noOfRowsInserted);
         //$row = mysqli_fetch_array($noOfRowsInserted,MYSQLI_ASSOC);
        return $noOfRowsInserted; 
        //return $this->_num_rows;
    }
    
    
    
    /**
     * ExtractionDB  getQueryforSectionRestart
     * Update the tbl_server with the image type and image url support
     * @param  serverID,img_type,img_url_support,img_unique_id etc
     * @return the query for updating the status once the csv writing has started
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   19-July-2016
     */
    
    public function getQueryforSectionRestart($classID)
    {
        $this->_getQueryforSectionRestart       = "select 
                                                    a.id
                                                    from
                                                    tbl_section a
                                                    join
                                                    tbl_server_cron_status b
                                                    on 
                                                    a.tbl_cron_id=b.id
                                                    and
                                                    a.tbl_class_id=$classID
                                                    and 
                                                    b.extraction_type=3
                                                    where (a.status=0 or a.status=2)";
        return $this->dbConnection->executeQuery($this->_getQueryforSectionRestart);
        
    }
    
    
    /**
     * ExtractionDB  getclassdetails
     * Get the classdetails
     * @param  classname,serverid
     * @return className
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   22-July-2016
     */
   
    public function getclassdetails($classID)
    {
       
       $this->_getClassname        = "Select class_name,time_zone_offset,server_timestamp_field,server_unique_field,nongmt_query_flag FROM tbl_server_class WHERE id=$classID";
       $classresult=$this->dbConnection->executeQuery($this->_getClassname);
       $row = mysqli_fetch_array($classresult,MYSQLI_ASSOC);
       return $row; 
       
    }
    
    
    /**
     * ExtractionDB  getserverdetails
     * Get the serverdetails
     * @param  serverid
     * @return server details
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   22-July-2016
     */
   
    public function getserverdetails($serverID)
    {
       
       $this->_getServername        = "Select server_name,offset_support FROM tbl_server WHERE id=$serverID";
       $serverresult=$this->dbConnection->executeQuery($this->_getServername);
       $row = mysqli_fetch_array($serverresult,MYSQLI_ASSOC);
       return $row; 
       
    }
    
    /**
     * ExtractionDB  getsectiondetails
     * Get the sectiondetails
     * @param  sectionID
     * @return section_query
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   22-July-2016
     */
   
    public function getsectiondetails($sectionID)
    {
       
       $this->_getsection_query        = "Select section_query FROM tbl_section WHERE id=$sectionID";
       $sectionresult=$this->dbConnection->executeQuery($this->_getsection_query);
       $row = mysqli_fetch_array($sectionresult,MYSQLI_ASSOC);
       return $row['section_query']; 
       
    }
    
    
    /**
     * ExtractionDB  deleteQuery
     * Get the deleteQuery
     * @param  sectionID
     * @return section_query
     * @author Praveen EP <incubator329@hotmail.com>
     * @date   25-July-2016
     */
    
    public function deleteQuery($tableName,$condition) 
    {
        $sql = "DELETE
                FROM $tableName
                WHERE $condition";
        $results = $this->dbConnection->executeScalar($sql);
        return $results;
    }

    /**
     * ExtractionDB updateDB
     * returns server url of the current server
     * @return  String  server url rets server url of the connected server
     * @param  serverID ,fieldName,fieldvalue
     * @author  Dhanya S <incubator133@hotmail.com>
     * @date    20-JUL-2016
     */
    public function updateDB($serverID,$fieldvalue,$fieldName,$class) {
        echo "\nUpdate {$fieldName} in DB\n";
        $sql = "update `tbl_server_class` SET $fieldName = '{$fieldvalue}' 
                WHERE tbl_server_id = $serverID and class_name= '{$class}' LIMIT 1";
        $this->dbConnection->executeNonQuery($sql);  
    }
    
     public function selectFileName($classid)
     {

         $sql = "SELECT extracted_filename,file_path from tbl_server_extracted_files inner join tbl_section on tbl_server_extracted_files.tbl_section_id =tbl_section.id where tbl_section.status=1 and tbl_server_class_id=$classid ";
             
         echo "$sql\n\n";
        $results = $this->dbConnection->executeQuery($sql);
        
        return $results;
    }
   
}