BE_IBL/application/controllers/v1/report-v6/Main.php

<?php

class Main extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Report Main API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_one= $this->load->database("onedev", true);
   }
   function detail(){
      try {
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }
         $prm = $this->sys_input;
         $id = $prm['id'];
         $sql = "select *
            from
            r_reportdetail
            join r_inputtype on R_ReportDetailR_InputTypeID = R_InputTypeID
            and R_ReportDetailIsActive = 'Y'
            where R_ReportDetailR_ReportID = ?
            order by R_ReportDetailID, R_ReportDetailPriority desc";

        // echo $sql;
         $qry = $this->db_one->query($sql, array($id) );
         $last_q = $this->db_one->last_query();
         if ($qry) {
            $rows = $qry->result_array();
            foreach($rows as $idx => $r) {
               $rows[$idx]["model"] = "";
               $rows[$idx]["items"] = "";
               $rows[$idx]["tmp_date"] = false;
            }
            $result = array ("total" => count($rows), "records" => $rows);
            $this->sys_ok($result);
         } else {
            $message = "Err Report Query";
            $this->sys_error($message,$this->db_one);
         }

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   function list(){
      try {
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }
         $userID = $this->sys_user["M_UserID"];
         $prm = $this->sys_input;
         $search = $prm['search'];
         $q_search = "";
         $q_param = array();
         $q_param[] = $userID;
         if ($search !== "") {
            $q_search = " where ( R_ReportCode like ? or R_ReportName like ? ) ";
            $q_param[] = "%$search%";
            $q_param[] = "%$search%";
         }
         $sql = "select
            R_ReportGroupID, R_ReportGroupName, R_ReportGroupColor,
            R_ReportID, R_ReportCode, R_ReportName, R_ReportUrl, R_ReportWithoutXls 
            from r_report
            join r_reportgroup on R_ReportGroupID = R_ReportR_ReportGroupID
            and R_ReportIsActive = 'Y'
            join m_user on M_UserID = ?
            join usergroup_report on R_ReportID = UserGroupReportR_ReportID
               and UserGroupReportM_UserGroupID = M_UserM_UserGroupID 
               and UserGroupReportIsActive = 'Y'
            $q_search ORDER BY R_ReportGroupName ASC, R_ReportCode ASC";
         //echo $sql;
         if ($q_search == "" ) {
            $qry = $this->db_one->query($sql,$userID);
         } else {
            $qry = $this->db_one->query($sql,$q_param);
         }
         $last_q = $this->db_one->last_query();
         if ($qry) {
            $rows = $qry->result_array();
            $reports = array();
            $prev_group = 0;
            $idx = -1;
            foreach($rows as $r) {
               if ($prev_group != $r["R_ReportGroupID"] ) {
                  $idx++;
                  $reports[] = array("id"=>$r["R_ReportGroupID"], "name" => $r["R_ReportGroupName"],
                        "color" => $r["R_ReportGroupColor"],
                        "reports" => array());
               }
               $prev_group = $r["R_ReportGroupID"];
               $reports[$idx]["reports"][] = $r;
            }
            $result = array ("total" => count($reports), "records" => $reports);
            $this->sys_ok($result);
         } else {
            $message = "Err Report Query";
            $this->sys_error_db($message,$this->db_one);
         }

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   public function lookupparam()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        $prm = $this->sys_input;
        $search = $prm['search'];
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
        }
        $number_limit = 10;
        $number_offset = ($prm['current_page'] - 1) * $number_limit ;
		$sql = "select COUNT(*) as total
	            from r_reportparam
                LEFT JOIN r_inputtype ON R_ReportParamR_InputTypeID = R_InputTypeID AND R_InputTypeIsActive = 'Y'
                where
                (R_ReportParamName LIKE CONCAT('%','{$search}','%') OR
                IFNULL(R_InputTypeName,'') LIKE CONCAT('%','{$search}','%') OR
                IFNULL(R_ReportParamLabel,'') LIKE CONCAT('%','{$search}','%')) AND
        R_ReportParamIsActive = 'Y'";
        $sql_param = array($search);
      //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
      $query = $this->db_onedev->query($sql);
      $tot_count = 0;
      $tot_page = 0;
      if ($query) {
         $tot_count = $query->result_array()[0]["total"];
         $tot_page = ceil($tot_count/$number_limit);
      } else {
         $this->sys_error_db("r_reportparam count", $this->db_onedev);
         exit;
      }
		
        $sql = "select R_ReportParamID as id,
        R_InputTypeName,
        r_reportparam.*
                from r_reportparam 
                LEFT JOIN r_inputtype ON R_ReportParamR_InputTypeID = R_InputTypeID AND R_InputTypeIsActive = 'Y'
                where 
                (R_ReportParamName LIKE CONCAT('%','{$search}','%') OR
                IFNULL(R_InputTypeName,'') LIKE CONCAT('%','{$search}','%') OR
                IFNULL(R_ReportParamLabel,'') LIKE CONCAT('%','{$search}','%')) AND
                R_ReportParamIsActive = 'Y'
                GROUP BY R_ReportParamID
                ORDER BY R_ReportParamName ASC
                limit $number_limit offset $number_offset";
        $sql_param = array($search);
        $query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("r_reportparam select");
          exit;
        }

        $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
}