<?php
class PktBruto extends CI_Controller
{
   function __construct() {
      parent::__construct();
      $this->db = $this->load->database("onedev", true);
   }
   function fix_tprice($mouID,$testID) {
      $sql = "select * from t_price where T_PriceM_MouID = ? and T_PriceT_TestID = ? and T_PriceIsActive = 'Y'";
      $qry = $this->db->query($sql, array($mouID, $testID) );
      if ( !$qry ) {
         return false;
      }
      $rows = $qry->result_array();
      $len = count($rows);
      if ($len > 0 ) {
         $amount = $rows[0]["T_PriceAmount"];
         $disc= $rows[0]["T_PriceDisc"];
         $discRp= $rows[0]["T_PriceDiscRp"];
         $subtotal = $rows[0]["T_PriceSubTotal"];
         $total = $rows[0]["T_PriceTotal"];
         return $rows[0];
      }
   }   
   function cekmou($mouID) {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketM_MouID = $mouID  and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      echo $this->db->last_query();
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      foreach($rows as $r) {
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);
         echo "\n ---------------\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "\n $paket_name : \n";
         foreach($ct as $idx => $t) {
            $test_name = $t["T_TestName"];
            $testid = $t["T_TestID"];
            $amount = $t["T_PriceAmount"];
            $disc = $t["T_PriceDisc"];
            $discRp = $t["T_PriceDiscRp"];
            $subtotal = $t["T_PriceSubTotal"];
            $total = $t["T_PriceTotal"];
            echo  "\t $test_name  : amount : $amount , disc : $disc , discRp : $discRp, subtotal : $subtotal => $total\n";
            $this->tprice($mouID,$testid);
         }
      }
   }
   function fixmou($mouID) {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketM_MouID= $mouID and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      $sql_u = "update ss_price_mou set child_test = ? where Ss_PriceMouID = ?";
      foreach($rows as $r) {
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);
         echo "\n ---------------\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "\n $paket_name : \n";
         foreach($ct as $idx => $t) {
            $test_name = $t["T_TestName"];
            $testid = $t["T_TestID"];
            $amount = $t["T_PriceAmount"];
            $disc = $t["T_PriceDisc"];
            $discRp = $t["T_PriceDiscRp"];
            $subtotal = $t["T_PriceSubTotal"];
            $total = $t["T_PriceTotal"];
            $tprice = $this->fix_tprice($mouID,$testid);
            $ct[$idx]["T_PriceAmount"] = $tprice["T_PriceAmount"];
            $ct[$idx]["T_PriceDisc"] = $tprice["T_PriceDisc"];
            $ct[$idx]["T_PriceDiscRp"] = $tprice["T_PriceDiscRp"];
            $ct[$idx]["T_PriceSubTotal"] = $tprice["T_PriceSubTotal"];
            $ct[$idx]["T_PriceTotal"] = $tprice["T_PriceTotal"];
         }
         $j_ct = json_encode($ct);
         $qry = $this->db->query($sql_u, array($j_ct, $ssPriceMouID));   
         if (! $qry ) {
            echo "update Error : " . print_r($this->db->error(),true) . "\n";
         } else {
            echo "update OK\n";
         }
      }
   }
   function fix() {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketOriginalBruto = 0 and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      $sql_u = "update ss_price_mou set child_test = ? where Ss_PriceMouID = ?";
      foreach($rows as $r) {
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);
         echo "\n ---------------\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "\n $paket_name : \n";
         foreach($ct as $idx => $t) {
            $test_name = $t["T_TestName"];
            $testid = $t["T_TestID"];
            $amount = $t["T_PriceAmount"];
            $disc = $t["T_PriceDisc"];
            $discRp = $t["T_PriceDiscRp"];
            $subtotal = $t["T_PriceSubTotal"];
            $total = $t["T_PriceTotal"];
            $tprice = $this->fix_tprice($mouID,$testid);
            $ct[$idx]["T_PriceAmount"] = $tprice["T_PriceAmount"];
            $ct[$idx]["T_PriceDisc"] = $tprice["T_PriceDisc"];
            $ct[$idx]["T_PriceDiscRp"] = $tprice["T_PriceDiscRp"];
            $ct[$idx]["T_PriceSubTotal"] = $tprice["T_PriceSubTotal"];
            $ct[$idx]["T_PriceTotal"] = $tprice["T_PriceTotal"];
         }
         $j_ct = json_encode($ct);
         $qry = $this->db->query($sql_u, array($j_ct, $ssPriceMouID));   
         if (! $qry ) {
            echo "update Error : " . print_r($this->db->error(),true) . "\n";
         } else {
            echo "update OK\n";
         }
      }
   }

   function cek() {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketOriginalBruto = 0 and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      foreach($rows as $r) {
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);
         echo "\n ---------------\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "\n $paket_name : \n";
         foreach($ct as $idx => $t) {
            $test_name = $t["T_TestName"];
            $testid = $t["T_TestID"];
            $amount = $t["T_PriceAmount"];
            $disc = $t["T_PriceDisc"];
            $discRp = $t["T_PriceDiscRp"];
            $subtotal = $t["T_PriceSubTotal"];
            $total = $t["T_PriceTotal"];
            echo  "\t $test_name  : amount : $amount , disc : $disc , discRp : $discRp, subtotal : $subtotal => $total\n";
            $this->tprice($mouID,$testid);
         }
      }
   }

   function cekpanelmou($mouID) {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y' and T_PacketType = 'PN'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketM_MouID = $mouID and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      echo $this->db->last_query();

      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join t_packet on packet_id = T_PacketID
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      $no = 0;
      foreach($rows as $r) {
         $no++;
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);

         $originalBruto = 0;
         $originalPrice = 0;
         foreach($ct as $idx => $t) {
            $amount = $t["T_PriceAmount"];
            $total = $t["T_PriceTotal"];
            $originalBruto += $amount;
            $originalPrice += $total;
         }
         $amount = $r["T_PriceAmount"];
         $subtotal = $r["T_PriceSubTotal"];
         $disc = $r["T_PriceDisc"];
         $total = $r["T_PriceTotal"];
         echo "\n ---------------\n";
         echo "NO : $no\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "$paket_name : \n";
         echo "\t Amount : $amount, disc : $disc ,subtotal : $subtotal => $total\n";
         echo "\t Fix \n ";
         $disc = $originalBruto - $originalPrice;
         echo "\t Amount : $originalBruto, disc : $disc ,subtotal : $originalPrice => $originalPrice\n";

      }
   }
   function cekpanel() {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y' and T_PacketType = 'PN'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketOriginalBruto = 0 and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join t_packet on packet_id = T_PacketID
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      $no = 0;
      foreach($rows as $r) {
         $no++;
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);

         $originalBruto = 0;
         $originalPrice = 0;
         foreach($ct as $idx => $t) {
            $amount = $t["T_PriceAmount"];
            $total = $t["T_PriceTotal"];
            $originalBruto += $amount;
            $originalPrice += $total;
         }
         $amount = $r["T_PriceAmount"];
         $subtotal = $r["T_PriceSubTotal"];
         $disc = $r["T_PriceDisc"];
         $total = $r["T_PriceTotal"];
         echo "\n ---------------\n";
         echo "NO : $no\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "$paket_name : \n";
         echo "\t Amount : $amount, disc : $disc ,subtotal : $subtotal => $total\n";
         echo "\t Fix \n ";
         $disc = $originalBruto - $originalPrice;
         echo "\t Amount : $originalBruto, disc : $disc ,subtotal : $originalPrice => $originalPrice\n";

      }
   }
   function fixpanel() {
      $sql = "create temporary table xtmp
         select distinct T_PacketID
         FROM `t_packet`
         JOIN m_mou ON T_PacketM_MouID = M_MouID AND M_MouIsActive = 'Y' and T_PacketType = 'PN'
         JOIN t_packetdetail ON T_PacketID = T_PacketDetailT_PacketID AND T_PacketDetailIsActive = 'Y' AND
         M_MouIsReleased = 'Y'
         join m_company on M_MouM_CompanyID = M_CompanyID
         WHERE
         T_PacketOriginalBruto = 0 and T_PacketIsActive = 'Y' ";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : " . print_r($this->db->error(), true);
         exit;
      }
      $sql = "select ss_price_mou.*, M_CompanyName, M_MouName
         from ss_price_mou 
         join t_packet on packet_id = T_PacketID
         join m_mou on Ss_PriceMouM_MouID = M_MouID 
         join m_company on M_MouM_CompanyID = M_CompanyID
         where is_packet = 'Y' and packet_id in ( select * from xtmp )";
      $qry = $this->db->query($sql);
      if ( !$qry ) {
         echo "ERR : select " . print_r($this->db->error(), true);
         exit;
      }
      $rows = $qry->result_array();
      $no = 0;
      $sql_u = "update ss_price_mou set T_PriceAmount = ?,
         T_PriceDiscRp = ?, T_PriceSubTotal = ? 
         where Ss_PriceMouID = ?";
      foreach($rows as $r) {
         $no++;
         $ssPriceMouID = $r["Ss_PriceMouID"];
         $paket_name = $r["T_TestName"];
         $company = $r["M_CompanyName"];
         $mou_name = $r["M_MouName"];
         $mouID = $r["Ss_PriceMouM_MouID"];
         $j_ct = $r["child_test"];
         $ct = json_decode($j_ct,true);

         $originalBruto = 0;
         $originalPrice = 0;
         foreach($ct as $idx => $t) {
            $amount = $t["T_PriceAmount"];
            $total = $t["T_PriceTotal"];
            $originalBruto += $amount;
            $originalPrice += $total;
         }
         $amount = $r["T_PriceAmount"];
         $subtotal = $r["T_PriceSubTotal"];
         $disc = $r["T_PriceDisc"];
         $total = $r["T_PriceTotal"];
         echo "\n ---------------\n";
         echo "NO : $no\n";
         echo "ID : $ssPriceMouID\n";
         echo "Company : $company\n";
         echo "Mou     : $mou_name\n";
         echo "$paket_name : \n";
         echo "\t Amount : $amount, disc : $disc ,subtotal : $subtotal => $total\n";
         echo "\t Fix \n ";
         $disc = $originalBruto - $originalPrice;
         echo "\t Amount : $originalBruto, disc : $disc ,subtotal : $originalPrice => $originalPrice\n";
         $qry = $this->db->query($sql_u, array($originalBruto, $disc, $originalPrice, $ssPriceMouID));
         if (! $qry ) {
            echo "update ERR : " . print_r($this->db->error(), true) . "\n";
         } else {
            echo "update OK\n";
         }
      }
   }
   function tprice($mouID,$testID) {
      $sql = "select * from t_price where T_PriceM_MouID = ? and T_PriceT_TestID = ? and T_PriceIsActive = 'Y'";
      $qry = $this->db->query($sql, array($mouID, $testID) );
      if ( !$qry ) {
         echo "\t Error get T_Price : " . print_r($this->db->error(),true);
         return;
      }
      $rows = $qry->result_array();
      $len = count($rows);
      echo "\t[ $len ] T_Price : ";
      if ($len > 0 ) {
         $amount = $rows[0]["T_PriceAmount"];
         $disc= $rows[0]["T_PriceDisc"];
         $discRp= $rows[0]["T_PriceDiscRp"];
         $subtotal = $rows[0]["T_PriceSubTotal"];
         $total = $rows[0]["T_PriceTotal"];
         echo  "\t  amount : $amount , disc : $disc , discRp : $discRp, subtotal : $subtotal => $total\n";
      }
   }
}