<?php

class Box extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "REF RECEIVE SAMPLE API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
      $this->load->library("Kapus");
   }

   function selected_box1()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }
         $prm = $this->sys_input;
         $id = $prm['id'];
         $barcode = $prm['barcode'];
         $sql = "SELECT 
         ROUND(sysFaktur08SumTargetPct,0) as kapus
            from sys_faktur_08_sum WHERE sysFaktur08SumID = $id ";
         // echo $sql;
         $query = $this->db_onedev->query($sql);
         if ($query) {
            $rows = $query->result_array();
         } else {
            $this->sys_error_db( $this->db_onedev->last_query() );
            exit;
         }
         $result = array ("total" => count($rows), "records" => $rows);
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   function selected_box2()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }
         $prm = $this->sys_input;
         $id = $prm['id'];
         $barcode = $prm['barcode'];
         $sql = "SELECT *,
         FORMAT(IFNULL(sysFaktur08SumAmount,0),0) as kapus,
            FORMAT(sysFKapusSumTotal,0) as kascabang,
            ROUND(sysFKapusSumTargetPct,0) as target,
            sysFKapusSumActualPct as actual,
            DATE_FORMAT(sysFKapusSumDate,'%d-%m-%Y') as tanggal,
            IFNULL(sysFaktur08SumIsPosted,'N') as sysFaktur08SumIsPosted
            from sys_f_kapus_sum
            LEFT JOIN sys_faktur_08_sum ON sysFaktur08SumDate = sysFKapusSumDate
            WHERE sysFKapusSumID = $id";
          //echo $sql;
         $query = $this->db_onedev->query($sql);
         if ($query) {
            $rows = $query->result_array();
         } else {
            $this->sys_error_db( $this->db_onedev->last_query() );
            exit;
         }
         $result = array ("total" => count($rows), "records" => $rows);
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   function updategenerate(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }
      $prm = $this->sys_input;
      $userid = $this->sys_user["M_UserID"];
      $id = $prm["id"]; 
      $tanggal = $prm["tanggal"]; 
      $sysKaPusPct = $prm["sysKaPusPct"]; 
      $sumid = 0;
      $sqlsumid = "SELECT sysFaktur08SumID as id FROM sys_faktur_08_sum WHERE sysFaktur08SumDate = '{$tanggal}'";
      $sumid = $this->db_onedev->query($sqlsumid)->row()->id;
      if($sumid == 0){
         $sql = "INSERT INTO sys_faktur_08_sum(
            sysFaktur08SumDate,		
            sysFaktur08SumUserID)
            VALUES(
            '{$tanggal}',
            $userid)"; 
         $qry = $this->db_onedev->query($sql);
         $last_id = $this->db_onedev->insert_id();
      }else{
         $last_id = $sumid;
      }
      $sql = "UPDATE sys_faktur_08 SET sysFaktur08IsActive = 'N' WHERE sysFaktur08sysFaktur08SumID = $last_id"; 
      $qry = $this->db_onedev->query($sql);  

      $rst = $this->add_efaktur($last_id,$tanggal,$sysKaPusPct);
      if ($rst["status"] != "OK" ) {
         $this->sys_error($rst["message"]);
         exit;
      }

      $this->clean_mysqli_connection($this->db_onedev->conn_id); 

      $sqlkapus = "SELECT
      sys_faktur_08_sum.*,
      sysFKapusSumIsPosted,
      sysFKapusSumDate,
      sysFKapusSumID as id,
      sysFKapusSumID,
      FORMAT(sysFaktur08SumAmountAfterRounding,0) as kapus,
      FORMAT(sysFaktur08SumTotalAfterRounding,0) as kascabang,
      FORMAT(sysFaktur08SumTotalAfterRounding - sysFaktur08SumAmountAfterRounding,0) as kascabang_only,
      ROUND(sysFaktur08SumTargetPct,0) as target,
      sysFaktur08SumActualPct as actual,
      DATE_FORMAT(sysFKapusSumDate,'%d-%m-%Y') as tanggal,
      IFNULL(sysFaktur08SumIsPosted,'N') as sysFaktur08SumIsPosted
      from sys_f_kapus_sum
      LEFT JOIN sys_faktur_08_sum ON sysFaktur08SumDate = sysFKapusSumDate
      where 
      sysFKapusSumID = $id";
        $querykapus = $this->db_onedev->query($sqlkapus); 
   $rows = $querykapus->result_array();
      $result = array ("total" => count($rows), "records" => $rows, "message" =>$rst["message"]);
      $this->sys_ok($result);
      exit;
   }
   function add_efaktur($hid,$date,$percent)
    {
        // get total query
        $userid = $this->sys_user["M_UserID"];
        $total_order = 0;
        $total_efaktur = 0; // $percent * $total faktur
        $sqltotal = "SELECT $percent/100*SUM(T_OrderHeaderTotal) as total
         FROM t_orderheader
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID AND M_PatientM_IdTypeID = 1 AND M_PatientIDNumber NOT LIKE '00%'
         WHERE T_OrderHeaderM_CompanyID IN(1222,1235) 
         AND date(T_OrderHeaderDate) = '{$date}' 
         AND T_OrderHeaderID NOT IN
         (
         SELECT sysFKapusT_OrderHeaderID
         FROM sys_f_kapus
         WHERE sysFKapusDate = '{$date}' 
         )";
         $total_efaktur = $this->db_onedev->query($sqltotal)->row()->total;

         $sqltotalorder = "SELECT SUM(T_OrderHeaderTotal) as total
         FROM t_orderheader
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID AND M_PatientM_IdTypeID = 1 AND M_PatientIDNumber NOT LIKE '00%'
         WHERE T_OrderHeaderM_CompanyID IN(1222,1235) 
         AND date(T_OrderHeaderDate) = '{$date}' 
         AND T_OrderHeaderID NOT IN
         (
         SELECT sysFKapusT_OrderHeaderID
         FROM sys_f_kapus
         WHERE sysFKapusDate = '{$date}' 
         )";
         $total_order = $this->db_onedev->query($sqltotalorder)->row()->total;
         //echo   $this->db_onedev->last_query();

        // get listing t_orderheaderid
        $sql = "SELECT T_OrderHeaderID,T_OrderHeaderTotal 
      FROM t_orderheader
      JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID AND M_PatientM_IdTypeID = 1 AND M_PatientIDNumber NOT LIKE '00%'
      WHERE T_OrderHeaderM_CompanyID IN(1222,1235) 
      AND date(T_OrderHeaderDate) ='{$date}' 
      AND T_OrderHeaderID NOT IN
      (
      SELECT sysFKapusT_OrderHeaderID
      FROM sys_f_kapus
      WHERE sysFKapusDate = '{$date}' 
      
      )
    ORDER BY T_OrderHeaderID ASC";

        $qry = $this->db_onedev->query($sql);
     //echo   $this->db_onedev->last_query();
        if (!$qry) {
            // ada error query
            return [false, $this->db_onedev->error()["message"]];
        }
        $rows = $qry->result_array();
        $running_total = 0;
        $daftar_order_id = [];
        foreach ($rows as $r) {
            $daftar_order_id[] = $r["T_OrderHeaderID"];
            $running_total += $r["T_OrderHeaderTotal"];
            if ($running_total >= $total_efaktur) {
                break;
            }
        }
        // insert kan ke efaktur dari $daftar_order_id
        if (count($daftar_order_id) > 0) {
            $s_ids = implode(",", $daftar_order_id);
            $sql = "INSERT INTO sys_faktur_08(sysFaktur08sysFaktur08SumID,
            sysFaktur08T_OrderHeaderID,	
            sysFaktur08Amount,
            sysFaktur08AmountAfterRounding,	
            sysFaktur08M_UserID,	
            sysFaktur08Date,	
            sysFaktur08Pct,		
            sysFaktur08Created) 
            SELECT $hid as sysFaktur08sysFaktur08SumID,
            T_OrderHeaderID as sysFaktur08T_OrderHeaderID,
            T_OrderHeaderTotal as sysFaktur08Amount,
            (T_OrderHeaderTotal - T_OrderHeaderTotal mod 500) as sysFaktur08AmountAfterRounding,
            $userid as sysFaktur08M_UserID,
            '{$date}' as sysFaktur08Date,
            $percent as sysFaktur08Pct,
            now() as sysFaktur08Created
            FROM t_orderheader 
            WHERE T_OrderHeaderID in ($s_ids)";
            $qry = $this->db_onedev->query($sql);
           // echo   $this->db_onedev->last_query();
        }
        if (! $qry ) {
         return array("status" => "ERR" , "message" => "Error INSERT  sys_faktur_08");
      }
      $sql = "UPDATE sys_faktur_08_sum SET sysFaktur08SumTotal = $total_order,	
      sysFaktur08SumTotalAfterRounding = $total_order - $total_order mod 500,
      sysFaktur08SumAmountAfterRounding = $running_total - $running_total mod 500,
      sysFaktur08SumAmount = $running_total,	
      sysFaktur08SumTargetPct = $percent,	
      sysFaktur08SumActualPct =  $running_total / $total_order * 100,
      sysFaktur08SumUserID = $userid,
      sysFaktur08SumLastUpdated = now()
      WHERE sysFaktur08SumID = $hid"; 
      $qry = $this->db_onedev->query($sql);
      return array("status" => "OK" , "message" => "");
    }
   function konfirmasi(){
      try {
            //# cek token valid
            if (! $this->isLogin) {
               $this->sys_error("Invalid Token");
               exit;
            }
            $prm = $this->sys_input;
            $id = $prm['id'];
            $year = $prm['year'];
            $month = $prm['month'];
            $userid = $this->sys_user["M_UserID"];
            $tanggal = $prm["tanggal"]; 

            $sqlfaktur = "select COUNT(sysFaktur08ID) as totalfaktur
            FROM sys_faktur_08
            WHERE sysFaktur08sysFaktur08SumID = $id AND sysFaktur08IsActive = 'Y'";
            $totalfaktur  = $this->db_onedev->query($sqlfaktur)->row()->totalfaktur;

            $sqlnomor = "select COUNT(sysFaktur08NomorID) as totalnomor
            FROM sys_faktur_08_nomor
            WHERE sysFaktur08NomorIsActive = 'Y' AND sysFaktur08NomorIsUsed = 'N' 
            AND sysFaktur08NomorDate >= '{$tanggal}'";
            $totalnomor  = $this->db_onedev->query($sqlnomor)->row()->totalnomor;

            if($totalnomor < $totalfaktur){
               $this->sys_error("Nomor E-Faktur Tidak Cukup");
            exit;
            }else{

            


            // get listing t_orderheaderid
        $sql = "SELECT sysFaktur08ID FROM sys_faktur_08 WHERE sysFaktur08sysFaktur08SumID = $id AND sysFaktur08IsActive = 'Y'";

        $qry = $this->db_onedev->query($sql);
        if (!$qry) {
            // ada error query
            return [false, $this->db_onedev->error()["message"]];
        }
        $rows = $qry->result_array();
        foreach ($rows as $r) {
		$hid = $r['sysFaktur08ID'];
		$sql = "UPDATE sys_faktur_08 SET
		sysFaktur08NoEfaktur = `fn_faktur_08_get_nomor`('{$tanggal}'),
		sysFaktur08LastUpdated = now()
      WHERE sysFaktur08ID = $hid"; 
      $qry = $this->db_onedev->query($sql);
            if (! $qry ) {
         return array("status" => "ERR" , "message" => "Error INSERT  sys_faktur_08");
      }
        }
            $sql = "update sys_faktur_08_sum set sysFaktur08SumIsPosted = 'Y',sysFaktur08SumUserID = '{$userid}' where sysFaktur08SumID = ?";
      $query = $this->db_onedev->query($sql, array($id));
            if ($query) {
                  $sqlkapus = "SELECT *,
                  sysFKapusSumID as id,
                  FORMAT(sysFKapusSumAmount,0) as kapus,
                     FORMAT(sysFKapusSumTotal,0) as kascabang,
                     ROUND(sysFKapusSumTargetPct,0) as target,
                     sysFKapusSumActualPct as actual,
                     DATE_FORMAT(sysFKapusSumDate,'%d-%m-%Y') as tanggal
                     from sys_f_kapus_sum
                     WHERE sysFKapusSumID = $id";
                    $querykapus = $this->db_onedev->query($sqlkapus); 
               $rows = $querykapus->result_array();
            } else {
               $this->sys_error_db( $this->db_onedev->last_query() );
               exit;
            }

            $result = array ("total" => count($rows), "records" => $rows);
            $this->sys_ok($result);
         }
         } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
         }
   }

}