<?php

class R_mou extends MY_Controller
{
   function __construct() {
      parent::__construct();
      //$this->db = $this->load->database('regional', true);
      $this->db = $this->load->database('onedev', true);
   }
   function unrelease() {
      $md5 = $this->sys_input["md5"];
      $param = $this->sys_input["param"];
      $j_param = json_encode($param);
      $l_md5 = md5($j_param);
      if ($md5 != $l_md5 ) {
         echo json_encode(array("status" => "ERR", "message" => "MD5 $md5 <> $l_md5"));
         exit;
      }
      $userid = 3;
      $sql = "update m_mou SET 
         M_MouIsReleased = 'N',
         M_MouStatus = 'UR',
         M_MouReleaseDate = now(),
         M_MouReleaseUserID = '{$userid}',
         M_MouIsApproved = 'N',
         M_MouLastUpdated = now()
         WHERE
         M_MouID = ?";

      $query = $this->db_onedev->query($sql, array( $param['M_MouID']));
      // echo $query;
      if (!$query) {
         $this->sys_error_db("m_mou update : " . print_r($this->db_onedev->error(),true) );
         exit;
      } 
      $userid = 3; 
      $id = $param["M_MouID"];
      $sql_log ="INSERT g_moustatuslog       
         (G_MouStatusLogDate,	 
         G_MouStatusLogM_MouID, 
         G_MouStatusLogStatus, 
         G_MouStatusLogUserID, 
         G_MouStatusLogCreated, 
         G_MouStatusLogLastUpdated)
         VALUES(
            date(now()),
            '{$id}',
            'UR',
            '{$userid}',
            now(),
            now())
";
      $qry = $this->db_onedev->query($querystatus);
      if (!$query) {
         $this->sys_error_db("m_mou update : " . print_r($this->db_onedev->error(),true) );
         exit;
      } 
      $this->sys_ok("Unreleased OK");
   }
   function edit() {
      $md5 = $this->sys_input["md5"];
      $param = $this->sys_input["param"];
      $j_param = json_encode($param);
      $l_md5 = md5($j_param);
      if ($md5 != $l_md5 ) {
         echo json_encode(array("status" => "ERR", "message" => "MD5 $md5 <> $l_md5"));
         exit;
      }
      $t_price = $param["t_price"];
      foreach($t_price as $t) {
         unset($t["T_PriceID"]);
         $this->db->where("T_PriceM_MouID", $t["T_PriceM_MouID"]);
         $this->db->where("T_PriceT_TestID", $t["T_PriceT_TestID"]);
         $this->db->where("T_PriceIsCito", $t["T_PriceIsCito"]);
         $qry = $this->db->update("t_price",$t);
         if (! $qry) {
            echo json_encode(array("status" => "ERR", "message" => "update t_price ". print_r($this->db->error(),true) .
               $this->db->last_query() ));
            exit;
         } 
      }
      $price = $param["price"];
      $sql_d =  "delete from ss_price_mou where Ss_PriceMouM_MouID = ? and T_TestID = ? and T_PriceIsCito = ?";
      $sql_d_packet =  "delete from ss_price_mou where Ss_PriceMouM_MouID = ? and packet_id= ?";
      $sql_i_packet =  "insert into t_packetdetail(T_PacketDetailT_PacketID,T_PacketDetailT_TestID,T_PacketDetailPrice)
         values(?,?,?)";
      $sql_u = "update t_packetdetail set T_PacketDetailPrice = ? where 
         T_PacketDetailIsActive = 'Y' and T_PacketDetailT_TestID = ? and T_PacketDetailT_PacketID =?"; 
      $sql_packet = "select T_PacketDetailID from t_packetdetail where T_PacketDetailT_PacketID = ? and T_PacketDetailT_TestID=?
         and T_PacketDetailIsActive = 'Y'";
      foreach($price as $p) {
         $mouID = $p["T_PriceM_MouID"];
         $testID = $p["T_TestID"];
         $packetID = $p["packet_id"];
         $is_packet = $p["is_packet"];
         $cito = $p["T_PriceIsCito"];
         if ($is_packet == "Y") {
            //update packet price 
            $sql_where = "";
            foreach($child_test as $t) {
               $c_testID = $t["T_TestID"];
               $c_price = $t["T_PriceTotal"];
               $qry = $this->db->query($sql_packet, array($packetID, $c_testID));
               if (! $qry) {
                  echo json_encode(array("status" => "ERR", "message" => "sql packet ". print_r($this->db->error(),true) ));
                  exit;
               } 
               $rows = $qry->result_array();
               if (count($rows) > 0 ) {
                  $qry = $this->db->query($sql_u, array($c_price , $c_testID, $packetID));
               } else {
                  $qry = $this->db->query($sql_i_packet, array($packetID, $c_testID, $c_price));
               }
               if (! $qry) {
                  echo json_encode(array("status" => "ERR", "message" => "sql i/u packet ". print_r($this->db->error(),true) ));
                  exit;
               } 
               if ($sql_where != "" ) $sql_where .= " or ";
               $sql_where .= " ( T_PacketDetailT_PacketID = $packetID and T_PacketDetailT_TestID = $c_testID ) ";
            }
            $qry = $this->db->query($sql_d_packet,array($mouID, $packetID)); 
            if (! $qry) {
               echo json_encode(array("status" => "ERR", "message" => "sql d packet ". print_r($this->db->error(),true) ));
               exit;
            } 
            if ($sql_where != "" ) {
               $qry = $this->db->query("delete from t_packet where $sql_where ");
               if (! $qry) {
                  echo json_encode(array("status" => "ERR", "message" => "sql d packet detail ". print_r($this->db->error(),true) ));
                  exit;
               } 
            }
         } else {
            $qry = $this->db->query($sql_d,array($mouID, $testID, $cito)); 
            if (! $qry) {
               echo json_encode(array("status" => "ERR", "message" => "sql d test ". print_r($this->db->error(),true) ));
               exit;
            } 
         }
         unset($p["Ss_PriceMouID"]);
         $qry = $this->db->insert("ss_price_mou",$p);
         if (! $qry) {
            echo json_encode(array("status" => "ERR", "message" => "insert ss price mou". print_r($this->db->error(),true) ));
            exit;
         } 
      }
      echo json_encode(array("status" => "OK", "message" => "")); 
   }

   function is_exists($table, $field, $value) {
      $this->db->where($field,$value);
      $qry = $this->db->get($table);
      if (! $qry) {
         echo json_encode(array("status" => "ERR", "message" => "Cek exists $table $field $value". print_r($this->db->error(),true) ));
         exit;
      } 
      if (! empty($qry->result_array())) {
         return true;
      }
      return false;
   } 
   function verify() {
      $md5 = $this->sys_input["md5"];
      $param = $this->sys_input["param"];
      $j_param = json_encode($param);
      $l_md5 = md5($j_param);
      if ($md5 != $l_md5 ) {
         echo json_encode(array("status" => "ERR", "message" => "MD5 $md5 <> $l_md5"));
         exit;
      }
      $company = $param["company"];
      $mou = $param["mou"];
      $logs = $param["logs"];
      $price = $param["price_mou"];
      $t_price = $param["t_price"];
      $packet = $param["packet"];
      $packet_detail  = $param["packet_detail"];

      if ($this->is_exists("m_company","M_CompanyID", $company["M_CompanyID"])) {
         $this->db->where("M_CompanyID",$company["M_CompanyID"]);
         $qry = $this->db->update("m_company",$company);
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Update Company: " . print_r($this->db->error(),true) ));
            exit;
         }
      } else {
         $qry = $this->db->insert("m_company",$company);
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Insert Company: " . print_r($this->db->error(),true) ));
            exit;
         }
      }

      if ( $this->is_exists("m_mou","M_MouID", $mou["M_MouID"]) ) {
         $this->db->where("M_MouID", $mou["M_MouID"]);
         $qry = $this->db->update("m_mou",$mou);
      } else {
         $qry = $this->db->insert("m_mou",$mou);
      }
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Insert/Update MOU : " . print_r($this->db->error(),true) ));
         exit;
      }

      foreach($logs as $idx => $l) {
         unset($logs[$idx]["G_MouStatusLogID"]);
      }
      $this->db->where("G_MouStatusLogM_MouID", $mou["M_MouID"]);
      $qry = $this->db->delete("g_moustatuslog");
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Delete Logs : " . print_r($this->db->error(),true) ));
         exit;
      }

      $qry = $this->db->insert_batch("g_moustatuslog",$logs);       
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Insert Logs : " . print_r($this->db->error(),true) ));
         exit;
      }

      $this->db->where("Ss_PriceMouM_MouID", $mou["M_MouID"]);
      $qry = $this->db->delete("ss_price_mou");
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Delete Ss Price : " . print_r($this->db->error(),true) ));
         exit;
      }

      foreach($price as $idx => $l) {
         unset($price[$idx]["Ss_PriceMouID"]);
      }
      $qry = $this->db->insert_batch("ss_price_mou",$price);
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Insert Ss Price : " . print_r($this->db->error(),true) ));
         exit;
      }

      $this->db->where("T_PriceM_MouID", $mou["M_MouID"]);
      $qry = $this->db->delete("t_price");
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Delete T Price : " . print_r($this->db->error(),true) ));
         exit;
      }

      foreach($t_price as $idx => $l) {
         unset($t_price[$idx]["T_PriceID"]);
      }
      $qry = $this->db->insert_batch("t_price",$t_price);
      if (! $qry ) {
         echo json_encode(array("status" => "ERR", "message" => "Insert T Price : " . print_r($this->db->error(),true) ));
         exit;
      }


      if ( ! empty($packet) ) {
         $p_ids  =  array();
         $p_ids[] = 0;
         foreach($packet as $p) {
            $p_ids[] = $p["T_PacketID"];
         }
         $this->db->where_in("T_PacketID",$p_ids);
         $qry = $this->db->delete("t_packet");
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Delete Packet : " . print_r($this->db->error(),true) ));
            exit;
         }

         $qry = $this->db->insert_batch("t_packet",$packet );
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Insert Packet : " . print_r($this->db->error(),true) ));
            exit;
         }

         $this->db->where_in("T_PacketDetailT_PacketID",$p_ids);
         $qry = $this->db->delete("t_packetdetail");
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Delete Packet : " . print_r($this->db->error(),true) ));
            exit;
         }
         $qry = $this->db->insert_batch("t_packetdetail",$packet_detail );
         if (! $qry ) {
            echo json_encode(array("status" => "ERR", "message" => "Insert Packet Detail : " . print_r($this->db->error(),true) ));
            exit;
         }
      }

      echo json_encode( array("status" => "OK" , "message" => "" , "mouID" => $mou["M_MouID"]));
   }
}