<?php
function GetDataMsSQL2($aColumns = array(),$sIndexColumn = '',$sTable ='',$add_where = '')
    {
        //load database
        $united6 = $this->load->database('united6', true);

        if(empty($aColumns) || $sIndexColumn == '' || $sTable == '')
            return $this->return_empty();
        $sLimit = "";

        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
        {
            $start = $this->input->get('iDisplayStart');
            $end = $start + $this->input->get('iDisplayLength');
            $sLimit = " row_number > ".$start.' AND row_number <= '.$end;
        }

        /*
         * Ordering
         */
        $sOrder = '';
        if ( isset( $_GET['iSortCol_0'] ) )
        {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
            {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                {
                    $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".$united6->escape_str( $_GET['sSortDir_'.$i] ) .", ";
                }
            }

            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" )
            {
                $sOrder = "";
            }
        }

        $sWhere = "";
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
        {
            $sWhere = "WHERE (";
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                //check for date field start here
                $datefield = array('last_login','start_date','create_date');
                if(in_array($aColumns[$i],$datefield))
                    $sWhere .= "DATE_FORMAT(".$aColumns[$i].",'%d %b %Y') LIKE '%".$united6->escape_str( $_GET['sSearch'] )."%' OR ";
                else
                    $sWhere .= $aColumns[$i]." LIKE '%".$united6->escape_str( $_GET['sSearch'] )."%' OR ";
                //end here
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }

        /* Individual column filtering */
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && ($_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ))
            {
                if ( $sWhere == "" )
                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i]." LIKE '%".$united6->escape_str($_GET['sSearch_'.$i])."%' ";
            }
        }
        if($add_where != '')
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= " ".$add_where." ";
        }

        $sLimit = (($sLimit!="")? " WHERE ".$sLimit : "");

        /*
         * SQL queries
         * Get data to display
         */
        $sQuery = "
            SELECT * FROM (
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns)).", ROW_NUMBER() OVER (ORDER BY ".$sIndexColumn.") as row_number
            FROM   $sTable $sWhere) SRC
            $sLimit
            $sOrder
        ";
        //die($sQuery);
        $datares = $united6->query($sQuery);

        /* Data set length after filtering */
        $sQuery = "
                SELECT COUNT(".$sIndexColumn.") as count
                FROM   $sTable ".$sWhere;
        $datares2 = $united6->query($sQuery);

        $iFilteredTotal = $datares2->row()->count;

        /* Total data set length */

        if($add_where != '')
        {
            $sQuery = "
                SELECT COUNT(".$sIndexColumn.") as count
                FROM   $sTable WHERE ".$add_where;
        }
        else
        {
            $sQuery = "
                SELECT COUNT(".$sIndexColumn.") as count
                FROM   $sTable ";
        }

        $datacount = $united6->query($sQuery);
        $iTotal = $datacount->row()->count;


        /*
         * Output
         */
        $output = array(
            "sEcho" => isset($_GET['sEcho']) ? intval($_GET['sEcho']) : 0,
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => array()
        );

        return array('output'=>$output,'datares'=>$datares);
    }
?>