<?php

class Schedule_cponev2 extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "SCHEDULENEW API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }

    function lookupday(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
            }
            $prm = $this->sys_input;
            $id = $prm['id'];
            $sql = "select *, IF(isavailable = 'Y',M_ScheduleDayID,9000+dayofweek) as id, '{$id}' as paramid FROM
                    (SELECT M_ScheduleDayID,M_DayOfWeekID as dayofweek, M_DayOfWeekName as dayofweekname, IF(IFNULL(M_ScheduleDayID,0) = 0 ,'N','Y') as isavailable
                    from m_dayofweek
                    LEFT JOIN m_schedule_day ON M_DayOfWeekID = M_ScheduleDayDayOfWeek AND M_ScheduleDayM_ScheduleGroupID = {$id} AND M_ScheduleDayIsActive = 'Y'
                    ORDER BY M_DayOfWeekID ASC) a ORDER BY dayofweek ASC";
            $sql_param = array($orderid);
            $query = $this->db_onedev->query($sql,$sql_param);
            if ($query) {
                $rows = $query->result_array();
                
            } else {
            $this->sys_error_db("m_schedule_day select by schedule");
            exit;
            }
            $result = array ("total" => count($rows), "records" => $rows);
            $this->sys_ok($result);


        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function lookup_branches(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $sql = "select M_BranchID as id, M_BranchName as name from m_branch where M_BranchIsActive = 'Y' ORDER BY M_BranchName ASC";
            $query = $this->db_onedev->query($sql);
            if ($query) {
                $rows = $query->result_array();
                
            } else {
            $this->sys_error_db("m_branch select");
            exit;
            }
            $result = array ("total" => count($rows), "records" => $rows);
            $this->sys_ok($result);


        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
	
	
   function lookuppromise(){
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
        $id = $prm['id'];
        $sql = "select M_ScheduleID as id,
        CONCAT(M_ScheduleStartHourMinute,' - ',M_ScheduleEndHourMinute) as name,
        M_ScheduleDayOfWeek,
        M_DayOfWeekName as dayofweekname,
        M_ScheduleStartHourMinute as start_hm,
        M_ScheduleEndHourMinute as end_hm,
        M_ScheduleMonth	as xmonth,	 
        M_ScheduleWeek as xweek,
        M_ScheduleDay as xday,
        M_ScheduleHour as xhour,
        M_ScheduleMinute as xmin,
        M_ScheduleAtTime as at_time,
        M_ScheduleFlagAtTime as flagattime,
        'xxx' as action
        from m_schedule
        LEFT JOIN m_dayofweek ON M_ScheduleDayOfWeek = M_DayOfWeekID
        where 
        M_ScheduleM_ScheduleGroupID = {$id} AND M_ScheduleIsActive = 'Y'
        ORDER BY M_ScheduleDayOfWeek ASC, M_ScheduleStartHourMinute ASC, M_ScheduleEndHourMinute ASC,M_ScheduleID ASC";
        //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_test() {
    try {
       //# cek token valid
       if (! $this->isLogin) {
          $this->sys_error("Invalid Token");
          exit;
       }
       $prm = $this->sys_input;
       $id = $prm['id'];
       $search = isset($prm['search']) ? $prm['search'] : '';
       $sql_param = array( $id);
       $sql = "select distinct 
          M_ScheduleGroupTestNat_TestID Nat_TestID, Nat_TestName
          from m_schedulegrouptest
          join nat_test on Nat_TestID  = M_ScheduleGroupTestNat_TestID
          where 
          M_ScheduleGroupTestM_ScheduleGroupID = ? AND Nat_TestName LIKE CONCAT('%','$search','%')
          and
          M_ScheduleGroupTestIsActive = 'Y'";
       $query = $this->db_onedev->query($sql,$sql_param);
       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_notest() {
    try {
       //# cek token valid
       if (! $this->isLogin) {
          $this->sys_error("Invalid Token");
          exit;
       }
       $prm = $this->sys_input;
       $id = $prm['id'];
       $search = $prm['search'];
       $branchid = $prm['branchid'];
       $sql_param = array( $id );
       $exist_test = "";

       $sql = "SELECT M_ScheduleGroupTestNat_TestID as nat_testid
            FROM m_schedulegrouptest
            JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND M_ScheduleGroupM_BranchID = {$branchid}
            WHERE
            M_ScheduleGroupTestIsActive = 'Y'";

        $query = $this->db_onedev->query($sql);
        if(!$query){
            $this->sys_error_db( $this->db_onedev->last_query() );
            exit;
        }
        $row_exist_test = $query->result_array();
        if(count($row_exist_test) > 0){
            foreach($row_exist_test as $k => $v){
                if($exist_test == ""){
                    $exist_test = $v['nat_testid'];
                }else{
                    $exist_test .= "," . $v['nat_testid'];
                }
            }
        }

        $filter = "";
        if($exist_test != ""){
            $filter = " AND Nat_TestID NOT IN({$exist_test})";
        }

       $sql = "SELECT Nat_TestID, Nat_TestName
       FROM nat_test
       WHERE  Nat_TestIsActive = 'Y' AND Nat_TestName LIKE CONCAT('%','$search','%')
       $filter
        " ;
       $query = $this->db_onedev->query($sql);
       $rows = $query->result_array();
       $result = array ("total" => count($rows), "records" => $rows);
       $this->sys_ok($result);

    } catch(Exception $exc) {
       $message = $exc->getMessage();
       $this->sys_error($message);
    }
 }
   public function lookup()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        $prm = $this->sys_input;
        $search = isset($prm['search']) ? $prm['search'] : '';
        $test = isset($prm['test']) ? $prm['test'] : '';
		$all = $prm['all'];
		$branch = isset($prm['branch']) ? $prm['branch'] : 0;
        if($branch == 0){
            $this->sys_error("Silahkan pilih cabang terlebih dahulu");
            exit;
        }

        $filter = "";
        if($search != ''){
            $filter = " AND M_ScheduleGroupName LIKE CONCAT('%','{$search}','%')";
        }

        if($test != ''){
            $filter = " AND Nat_TestCode = '{$test}'";
        }

		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
		}
		$sql = "SELECT COUNT(*) as total FROM
                (SELECT M_ScheduleGroupID from m_schedulegroup 
                LEFT JOIN m_schedulegrouptest ON M_ScheduleGroupTestM_ScheduleGroupID =  M_ScheduleGroupID AND M_ScheduleGroupTestIsActive = 'Y'
                LEFT JOIN nat_test ON Nat_TestID = M_ScheduleGroupTestNat_TestID
                WHERE 
                M_ScheduleGroupIsActive = 'Y' AND 
                M_ScheduleGroupM_BranchID = {$branch}
                $filter
                GROUP BY  M_ScheduleGroupID
                ) a";

        $total = $this->db_onedev->query($sql)->row()->total;
		//echo $this->db_onedev->last_query();
		
        $sql = "SELECT M_ScheduleGroupID as id, M_ScheduleGroupName as name,
        M_ScheduleGroupName as detail,
        'xxx' as scheduledays, 'xxx' as scheduletests, 'xxx' as schedulepromise,
        'xxx' as tests
                from m_schedulegroup
                LEFT JOIN m_schedulegrouptest ON M_ScheduleGroupTestM_ScheduleGroupID =  M_ScheduleGroupID AND 
                M_ScheduleGroupTestIsActive = 'Y'
                LEFT JOIN nat_test ON Nat_TestID = M_ScheduleGroupTestNat_TestID
                WHERE 
                M_ScheduleGroupIsActive = 'Y' 
                AND M_ScheduleGroupM_BranchID = {$branch}
                $filter
                GROUP BY  M_ScheduleGroupID
                $limit";
        $sql_param = array($search,$test);
        $query = $this->db_onedev->query($sql);
        //echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
            if($rows){  
                foreach($rows as $k => $v){
                    $rows[$k]['tests'] = $this->add_test($v['id']);
                }
            }
        } else {
          $this->sys_error_db("m_schedulegroup select");
          exit;
        }

        $result = array ("total" => $total, "total_filter"=>count($rows),"records" => $rows);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   function add_test($orderid){
        $query ="	SELECT m_schedulegrouptest.*, 'Y' as editable, M_ScheduleGroupTestNat_TestID as xid, Nat_TestID, Nat_TestName
                    
                    FROM m_schedulegrouptest
                    JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID
                    JOIN nat_test ON M_ScheduleGroupTestNat_TestID = Nat_TestID 
                    WHERE
                    M_ScheduleGroupTestM_ScheduleGroupID = {$orderid} AND M_ScheduleGroupTestIsActive = 'Y'
                GROUP BY M_ScheduleGroupTestID";
            // echo $query;
        $rows = $this->db_onedev->query($query)->result_array();
        if(!$rows)
            $rows = array();
        return $rows;
    }

   function addnewschedulegroup()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $name = $prm['name'];
        $branch = $prm['branch'];

        $sql = "SELECT COUNT(*) as total FROM m_schedulegroup WHERE M_ScheduleGroupName = '{$name}' AND 
        M_ScheduleGroupM_BranchID = {$branch['id']} AND M_ScheduleGroupIsActive = 'Y'";
        $query = $this->db_onedev->query($sql);
        $total = $query->row()->total;
        if($total > 0){
            $this->sys_error("Nama Schedule sudah ada");
            exit;
        }

        if($branch == 0){
            $this->sys_error("Silahkan pilih cabang");
            exit;
        }

        if(count($prm['ordertests']) == 0){
            $this->sys_error("Silahkan pilih pemeriksaan");
            exit;
        }
        
        $userid = $this->sys_user["M_UserID"];

        $this->db_onedev->trans_start();
        
		$sql = "INSERT INTO m_schedulegroup(
            M_ScheduleGroupName,	
            M_ScheduleGroupM_BranchID,
            M_ScheduleGroupCreatedUserID,
            M_ScheduleGroupCreated
				) 
				values( ?,?,?,now())";
		$query = $this->db_onedev->query($sql,
			array(
				$name,
                $branch['id'],
                $userid
			)
		);
        if(!$query){
            $this->sys_error_db("m_schedulegroup insert");
            exit;
        }
        //echo $query;
        $schedulegroup_id = $this->db_onedev->insert_id();
		foreach($prm['ordertests'] as $k=>$v){
            $nattestid =  $v['Nat_TestID'];
            $sql = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test
            WHERE Nat_TestID = '{$nattestid}'")->row();	
            $tipeid = $sql->tipeid;
            if($tipeid == 3 || $tipeid == 4){
                $sql = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test
                WHERE T_TestNat_TestID = '{$nattestid}'")->row();	
                $tsascode = $sql->tsascode; 

                $sql = $this->db_onedev->query("SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild FROM t_test
                WHERE T_TestSasCode like concat('{$tsascode}','%') AND T_TestIsActive = 'Y'
                and T_TestIsResult = 'Y'")->row();	
                $tchild = $sql->tchild;

                $existparent = "UPDATE m_schedulegrouptest 
                JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND 
                M_ScheduleGroupM_BranchID = {$branch['id']}
                SET 
                M_ScheduleGroupTestIsActive = 'N',
                M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                M_ScheduleGroupTestLastUpdated = now()
                WHERE
                M_ScheduleGroupTestNat_TestID = $nattestid";
                $delete_exist_tparent= $this->db_onedev->query($existparent);
                if(!$delete_exist_tparent){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test delete exist parent");
                    exit;
                }

                $insertparent = "INSERT INTO m_schedulegrouptest (
					M_ScheduleGroupTestM_ScheduleGroupID,
                    M_ScheduleGroupTestNat_TestID, 
                    M_ScheduleGroupTestCreatedUserID,	 
                    M_ScheduleGroupTestCreated
				)
				VALUES($schedulegroup_id,$nattestid,$userid,now())";
                $insert_tparent= $this->db_onedev->query($insertparent);
                if(!$insert_tparent){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test update parent");
                    exit;
                }



                $existchild = "UPDATE m_schedulegrouptest 
                JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND 
                M_ScheduleGroupM_BranchID = {$branch['id']}
                SET 
                M_ScheduleGroupTestIsActive = 'N',
                M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                M_ScheduleGroupTestLastUpdated = now()
                WHERE
                M_ScheduleGroupTestNat_TestID IN($tchild)";
                $delete_exist_tchild= $this->db_onedev->query($existchild);
                if(!$delete_exist_tchild){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test delete exist child");
                    exit;
                }
                $insertchild = "INSERT INTO m_schedulegrouptest (
					M_ScheduleGroupTestM_ScheduleGroupID,
                    M_ScheduleGroupTestNat_TestID, 
                    M_ScheduleGroupTestCreatedUserID,	 
                    M_ScheduleGroupTestCreated
				)
				SELECT $schedulegroup_id as M_ScheduleGroupTestM_ScheduleGroupID,
                Nat_TestID as M_ScheduleGroupTestNat_TestID,
                $userid as M_ScheduleGroupTestUserID,
                now() as M_ScheduleGroupTestCreated
                FROM nat_test
                WHERE Nat_TestID IN($tchild) AND Nat_TestIsActive = 'Y'";
                $insert_tchild= $this->db_onedev->query($insertchild);
                if(!$insert_tchild){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test insert child");
                    exit;
                }

            }else{
                $existparent = "UPDATE m_schedulegrouptest 
                JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND 
                M_ScheduleGroupM_BranchID = {$branch['id']}
                SET 
                M_ScheduleGroupTestIsActive = 'N',
                M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                M_ScheduleGroupTestLastUpdated = now()
                WHERE
                M_ScheduleGroupTestNat_TestID = $nattestid";
                $delete_exist_tparent= $this->db_onedev->query($existparent);
                if(!$delete_exist_tparent){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test delete exist parent");
                    exit;
                }
                
                $query = "INSERT INTO m_schedulegrouptest (
					M_ScheduleGroupTestM_ScheduleGroupID,
                    M_ScheduleGroupTestNat_TestID, 
                    M_ScheduleGroupTestCreatedUserID, 
                    M_ScheduleGroupTestCreated
				)
				VALUES(
					?,?,?,now()
				)";
				$insert_new_test = $this->db_onedev->query($query,array(
													$schedulegroup_id,
													$v['Nat_TestID'],
													$userid
												));
                if(!$insert_new_test){
                    
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegroup_test insert");
                    exit;
                }
		    }	

        
		
        }	

        

        $this->db_onedev->trans_commit();
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   function editschedulegroup()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $prm = $this->sys_input;
         $id_schedulegroup = $prm['id'];
         $name_schedulegroup = $prm['name'];
         $branch = $prm['branch'];
         $userid = $this->sys_user["M_UserID"];

         $this->db_onedev->trans_start();

         $query = "SELECT COUNT(*) as exist FROM m_schedulegroup WHERE M_ScheduleGroupIsActive = 'Y' AND M_ScheduleGroupName = '{$name_schedulegroup}' AND M_ScheduleGroupID <> {$id_schedulegroup}";
         $exist_name =  $this->db_onedev->query($query)->row()->exist;

         if($exist_name == 0){
            $sql = "UPDATE m_schedulegroup SET 
               M_ScheduleGroupName = ?,
               M_ScheduleGroupLastUpdatedUserID = ?,
               M_ScheduleGroupLastUpdated = NOW()
               WHERE
               M_ScheduleGroupID = ?";
            $query = $this->db_onedev->query($sql,
               array(
                  $name_schedulegroup,
                  $userid,
                  $id_schedulegroup
               )
            );
           // echo $query;
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegroup update");
                exit;
            }
        }
        
        $sql_param = $prm['id'];
        $sql = "SELECT * FROM m_schedulegroup WHERE M_ScheduleGroupID = ? AND M_ScheduleGroupIsActive = 'Y'";
        $query = $this->db_onedev->query($sql,$sql_param); 
    
        if ($query) {
            $rows = $query->result_array();
        } else {
            $this->db_onedev->trans_rollback();
            $this->sys_error_db("nat_test select by schedulegroup");
            exit;
        }

        if(count($rows) == 0){
            $this->db_onedev->trans_rollback();
            $this->sys_error("Schedule group not found");
            exit;
        }

        foreach($prm['ordertests'] as $k=>$v){
            if($v['xid'] == 0 || $v['xid'] == '0'){
                $nattestid =  $v['Nat_TestID'];
                $sql = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test
                WHERE Nat_TestID = '{$nattestid}'")->row();	
                $tipeid = $sql->tipeid;
                if($tipeid == 4){
                    $sql = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test
                    WHERE T_TestNat_TestID = '{$nattestid}'")->row();	
                    $tsascode = $sql->tsascode; 

                    $sql = $this->db_onedev->query("SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild FROM t_test
                    WHERE T_TestSasCode like concat('{$tsascode}','%') AND T_TestIsActive = 'Y'
                    and T_TestIsResult = 'Y'")->row();	
                    $tchild = $sql->tchild;

                    $existparent = "UPDATE m_schedulegrouptest SET 
                                    M_ScheduleGroupTestIsActive = 'N',
                                    M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                                    M_ScheduleGroupTestLastUpdated = NOW()
                                    WHERE
                                    M_ScheduleGroupTestNat_TestID = {$nattestid}";
                    $delete_exist_tparent = $this->db_onedev->query($existparent);
                    if(!$delete_exist_tparent){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist parent");
                        exit;
                    }

                    $insertparent = "INSERT INTO m_schedulegrouptest (
                        M_ScheduleGroupTestM_ScheduleGroupID,
                        M_ScheduleGroupTestNat_TestID, 
                        M_ScheduleGroupTestCreatedUserID,	 
                        M_ScheduleGroupTestCreated
                    )
                    VALUES({$id_schedulegroup},{$nattestid},{$userid},NOW())";
                    $insert_tparent= $this->db_onedev->query($insertparent);
                    if(!$insert_tparent){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest insert parent");
                        exit;
                    }

                    $existchild = "UPDATE m_schedulegrouptest SET 
                    M_ScheduleGroupTestIsActive = 'N',
                    M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                    M_ScheduleGroupTestLastUpdated = now()
                    WHERE
                    M_ScheduleGroupTestNat_TestID IN ($tchild)";
                    $delete_exist_tchild= $this->db_onedev->query($existchild);
                    if(!$delete_exist_tchild){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist child");
                        exit;
                    }

                    $insertchild = "INSERT INTO m_schedulegrouptest (
                        M_ScheduleGroupTestM_ScheduleGroupID,
                        M_ScheduleGroupTestNat_TestID, 
                        M_ScheduleGroupTestCreatedUserID,	 
                        M_ScheduleGroupTestCreated
                    )
                    SELECT $id_schedulegroup as M_ScheduleGroupTestM_ScheduleGroupID,
                    Nat_TestID as M_ScheduleGroupTestNat_TestID,
                    {$userid} as M_ScheduleGroupTestCreatedUserID,
                    NOW() as M_ScheduleGroupTestCreated
                    FROM nat_test
                    WHERE Nat_TestID IN({$tchild}) AND Nat_TestIsActive = 'Y'";
                    $insert_tchild= $this->db_onedev->query($insertchild);
                    if(!$insert_tchild){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest insert child");
                        exit;
                    }

                }else{
                    $existparent = "UPDATE m_schedulegrouptest SET 
                    M_ScheduleGroupTestIsActive = 'N',
                    M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                    M_ScheduleGroupTestLastUpdated = NOW()
                    WHERE
                    M_ScheduleGroupTestNat_TestID = {$nattestid}";
                    $delete_exist_tparent= $this->db_onedev->query($existparent);
                    if(!$delete_exist_tparent){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist parent");
                        exit;
                    }

                    $query = "INSERT INTO m_schedulegrouptest (
                        M_ScheduleGroupTestM_ScheduleGroupID,
                        M_ScheduleGroupTestNat_TestID, 
                        M_ScheduleGroupTestCreatedUserID,	 
                        M_ScheduleGroupTestCreated
                    )
                    VALUES(
                        ?,?,?,NOW()
                    )";
                    $insert_new_test = $this->db_onedev->query($query,array(
                                                        $id_schedulegroup,
                                                        $v['Nat_TestID'],
                                                        $userid
                                                    ));
                    if(!$insert_new_test){
                        $this->db_onedev->trans_rollback();
                       // echo $this->db_onedev->last_query();
                        $this->sys_error_db("m_schedulegrouptest insert");
                        exit;
                    }
                }
            }    
        }
        
        if(isset($prm['deleted_ordertests'])){
            foreach($prm['deleted_ordertests'] as $i=>$del){
                if($tipeid == 4){
                    $sql = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test
                    WHERE T_TestNat_TestID = '{$nattestid}'")->row();	
                    $tsascode = $sql->tsascode; 
         
                    $sql = $this->db_onedev->query("SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild 
                    FROM t_test
                    WHERE T_TestSasCode like concat('{$tsascode}','%') AND T_TestIsActive = 'Y'
                    and T_TestIsResult = 'Y'")->row();	
                    $tchild = $sql->tchild;
         
                    $sqlparent = "UPDATE m_schedulegrouptest SET
                                    M_ScheduleGroupTestIsActive = 'N', 
                                    M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                                    M_ScheduleGroupTestLastUpdated = NOW()
                                  WHERE 
                                    M_ScheduleGroupTestNat_TestID = {$del['M_ScheduleGroupTestNat_TestID']} AND
                                    M_ScheduleGroupTestM_ScheduleGroupID = {$del['M_ScheduleGroupTestM_ScheduleGroupID']}";
                    $queryparent = $this->db_onedev->query($sqlparent);
                    if(!$queryparent){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist parent");
                        exit;
                    }
         
                    $sqlchild = "UPDATE m_schedulegrouptest SET
                                    M_ScheduleGroupTestIsActive = 'N',
                                    M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                                    M_ScheduleGroupTestLastUpdated = NOW()
                                WHERE 
                                    M_ScheduleGroupTestNat_TestID IN ({$tchild}) AND 
                                    M_ScheduleGroupTestM_ScheduleGroupID = {$del['M_ScheduleGroupTestM_ScheduleGroupID']}";
         
                    $querychild = $this->db_onedev->query($sqlchild);
                    if (! $querychild) {
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist child");
                       exit;
                    }
                }else{
                    $sql = "UPDATE m_schedulegrouptest SET
                            M_ScheduleGroupTestIsActive = 'N',
                            M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                            M_ScheduleGroupTestLastUpdated = NOW()
                            WHERE 
                            M_ScheduleGroupTestNat_TestID = ? AND 
                            M_ScheduleGroupTestM_ScheduleGroupID = ?";
                    $query = $this->db_onedev->query($sql,array(
                        $del['M_ScheduleGroupTestNat_TestID'],
                        $del['M_ScheduleGroupTestM_ScheduleGroupID']
                    ));
                    if (! $query) {
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db("m_schedulegrouptest delete exist test");
                        exit;
                    }
                }
            }
        }

        $this->db_onedev->trans_commit();

        $result = array ("total" => 1, "records" => array("xid" => $id_schedulegroup));
        $this->sys_ok($result);


      } catch(Exception $exc) {
        $this->db_onedev->trans_rollback();
        $message = $exc->getMessage();
        $this->sys_error($message);
      }
    
    }

   function addnewscheduleday()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $errors = array(); 
        $scheduleid = $prm['scheduleid'];
        $dayid = $prm['dayid'];
        $userid = $this->sys_user["M_UserID"];
        
		$sql = "INSERT INTO m_schedule_day(
                    M_ScheduleDayM_ScheduleID,
                    M_ScheduleDayDayOfWeek,
                    M_ScheduleDayUserID,
					M_ScheduleDayCreated,
					M_ScheduleDayLastUpdate
				) 
				values( ?,?,?,now(),now())";
		$query = $this->db_onedev->query($sql,
			array(
				$scheduleid,
                $dayid,
                $userid
			)
		);
		//echo $query;
		if (!$query) {
			$this->sys_error_db("m_schedule_day insert");
			exit;
		} 
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   function delete_day() {
    try {
       //# cek token valid
       if (! $this->isLogin) {
          $this->sys_error("Invalid Token");
          exit;
       }
       $prm = $this->sys_input;
       $dayid = $prm['dayid'];
       $scheduleid = $prm['scheduleid'];
       $sql_param = array( $dayid ,$scheduleid);
       $sql = "update m_schedule_day
       set M_ScheduleDayIsActive = 'N'
       where M_ScheduleDayDayOfWeek= ?
       and M_ScheduleDayM_ScheduleID=?";
       $query = $this->db_onedev->query($sql,$sql_param);
       if (! $query) {
          $this->sys_error_db( $this->db_onedev->last_query() );
          exit;
       }
       $sql = "select * from m_schedule_day
          where M_ScheduleDayDayOfWeek= ?
          and M_ScheduleDayM_ScheduleID=?";
       $query = $this->db_onedev->query($sql,$sql_param); 
  
       if ($query) {
          $rows = $query->result_array();
       } else {
          $this->sys_error_db("nat_test select by requirement");
          exit;
       }
       $result = array ("status" => "OK"); 
       $this->sys_ok($result);
  
    } catch(Exception $exc) {
       $message = $exc->getMessage();
       $this->sys_error($message);
    }
  
  }
   
  function addnewschedulepromise()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $xmonth = $prm['xmonth'];
        $xweek = $prm['xweek'];
        $xday = $prm['xday'];
        $xhour = $prm['xhour'];
        $xminute = $prm['xminute'];

        $userid = $this->sys_user["M_UserID"];
        $this->db_onedev->trans_start();

        if($prm['flagattime'] == 'Y' && ($prm['attime'] !== 'Invalid date')){
           // echo "flagattime = Y and attime is not Invalid date";
        if($prm['xid'] == "0" || $prm['xid'] == 0){
            //echo "xid = 0 or xid = 0";
            $sql = "INSERT INTO m_schedule(
                M_ScheduleDayOfWeek,
                M_ScheduleM_ScheduleGroupID,
                M_ScheduleStartHourMinute,
                M_ScheduleEndHourMinute,
				M_ScheduleMonth,
				M_ScheduleWeek,
                M_ScheduleDay,
                M_ScheduleHour,
                M_ScheduleMinute,
                M_ScheduleAtTime,
                M_ScheduleFlagAtTime,
                M_ScheduleCreated,
                M_ScheduleCreatedUserID
            ) 
            VALUES(?,?,?,?,?,?,?,?,?,?,?,NOW(),?)";
            
            $query = $this->db_onedev->query($sql,
                array(
                    $prm['dayofweek'],
                    $prm['scheduleid'],
                    str_replace('.',':',$prm['starthm']),
                    str_replace('.',':',$prm['endhm']),
					$xmonth,
					$xweek,
                    $xday,
                    $xhour,
                    $xminute,
                    str_replace('.',':',$prm['attime']),
                    $prm['flagattime'],
                    $userid
                )
            );
           //echo $this->db_onedev->last_query();
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule insert");
                exit;
            } 
        }
        else{
            //echo "else : xid is not 0 or xid is not 0";
            $sql = "UPDATE m_schedule SET 
            M_ScheduleDayOfWeek = ?,
            M_ScheduleStartHourMinute = ?,
            M_ScheduleEndHourMinute = ?,
			M_ScheduleMonth = ?,
			M_ScheduleWeek = ?,
            M_ScheduleDay = ?,
            M_ScheduleHour = ?,
            M_ScheduleMinute = ?,
            M_ScheduleAtTime = ?,
            M_ScheduleFlagAtTime = ?,
            M_ScheduleLastUpdate = now(),
            M_ScheduleLastUpdateUserID = ?
            WHERE
            M_ScheduleID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['dayofweek'],
                    str_replace('.',':',$prm['starthm']),
                    str_replace('.',':',$prm['endhm']),
					$xmonth,
					$xweek,
                    $xday,
                    $xhour,
                    $xminute,
                    str_replace('.',':',$prm['attime']),
                    $prm['flagattime'],
                    $userid,
                    $prm['xid']
                )
            );
            //echo $this->db_onedev->last_query();
            //exit;
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule update");
                exit;
            } 
        }
    }
    elseif($prm['flagattime'] == 'N'){
        //echo "flagattime = N";
        if($prm['xid'] == "0" || $prm['xid'] == 0){
            //echo "xid = 0 or xid = 0";
            $sql = "INSERT INTO m_schedule(
                M_ScheduleDayOfWeek,
                M_ScheduleM_ScheduleGroupID,
                M_ScheduleStartHourMinute,
                M_ScheduleEndHourMinute,
				M_ScheduleMonth,
				M_ScheduleWeek,
                M_ScheduleDay,
                M_ScheduleHour,
                M_ScheduleMinute,
                M_ScheduleAtTime,
                M_ScheduleFlagAtTime,
                M_ScheduleCreated,
                M_ScheduleCreatedUserID
            ) 
            VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW(),?)";
            
            $query = $this->db_onedev->query($sql,
                array(
                    $prm['dayofweek'],
                    $prm['scheduleid'],
                    str_replace('.',':',$prm['starthm']),
                    str_replace('.',':',$prm['endhm']),
					$xmonth,
					$xweek,
                    $xday,
                    $xhour,
                    $xminute,
                    '',
                    $prm['flagattime'],
                    $userid
                )
            );
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule insert");
                exit;
            } 
        }
        else{
            //echo "else B : xid is not 0 or xid is not 0";
            $sql = "UPDATE m_schedule SET 
            M_ScheduleDayOfWeek = ?,
            M_ScheduleStartHourMinute = ?,
            M_ScheduleEndHourMinute = ?,
			M_ScheduleMonth = ?,
			M_ScheduleWeek = ?,
            M_ScheduleDay = ?,
            M_ScheduleHour = ?,
            M_ScheduleMinute = ?,
            M_ScheduleAtTime = ?,
            M_ScheduleFlagAtTime = ?,
            M_ScheduleLastUpdate = NOW(),
            M_ScheduleLastUpdateUserID = ?
            WHERE
            M_ScheduleID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['dayofweek'],
                    str_replace('.',':',$prm['starthm']),
                    str_replace('.',':',$prm['endhm']),
					$xmonth,
					$xweek,
                    $xday,
                    $xhour,
                    $xminute,
                    '',
                    $prm['flagattime'],
                    $userid,
                    $prm['xid']
                )
            );

            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule update");
                exit;
            } 
        }
		
        
		
		
		
       
    }else{
        //echo "else C : flagattime is not Y or N";
        
        array_push($errors,array('field'=>'attime','msg'=>'Pada jam (hh:mm) di isi dulu'));            
    
    }

    $this->db_onedev->trans_commit();

    $result = array ("total" => 1, "records" => array("xid" => 0), );
    if(count($errors) > 0){
        $result = array ("total" => -1,"errors" => $errors, "records" => 0);
    }
    $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   
   function deleteschedulepromise()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];

        $this->db_onedev->trans_start();

        $sql = "UPDATE m_schedule SET 
            M_ScheduleIsActive = 'N',
            M_ScheduleLastUpdate = NOW(),
            M_ScheduleLastUpdateUserID = ?
            WHERE
            M_ScheduleID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $userid,
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule delete");
                exit;
            } 
        
        $this->db_onedev->trans_commit();
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   public function addnewpreschedulepromise()
   {
    try {
        //# cek token valid
        if (! $this->isogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $dayofweek = $prm['dayofweek'];
        $query = "SELECT COUNT(*) as exist FROM m_preschedulepromise WHERE M_PreSchedulePromiseIsActive = 'Y' AND M_PreSchedulePromiseDayOfWeek = '{$dayofweek}'";
        $exist_name =  $this->db_onedev->query($query)->row()->exist;
        
        if($prm['xid'] == "0" || $prm['xid'] == 0){
            if($exist_name == 0){
            $sql = "INSERT INTO m_preschedulepromise(
                M_PreSchedulePromiseM_ScheduleID,
                M_PreSchedulePromiseDayOfWeek,
                M_PreSchedulePromiseHour,
                M_PreSchedulePromiseCreated,
                M_PreSchedulePromiseLastUpdated
            ) 
            values( ?,?,?,now(),now())";
            $query = $this->db_onedev->query($sql,
                array(
                    $prm['scheduleid'],
                    $prm['dayofweek'],
                    $prm['xhour']
                )
            );
            if (!$query) {
                $this->sys_error_db("m_preschedulepromise insert");
                exit;
            } 
            $result = array ("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } else {
            $result = array ("total" => -1, "records" => 0);
			$this->sys_ok($result);
        }
        }
        else{
            $idx = $prm['xid'];
            $dayofweek = $prm['dayofweek'];
            $query = "SELECT COUNT(*) as exist FROM m_preschedulepromise WHERE M_PreSchedulePromiseIsActive = 'Y' AND M_PreSchedulePromiseDayOfWeek = '{$dayofweek}' AND M_PreSchedulePromiseID <> {$idx}";
            $exist_name =  $this->db_onedev->query($query)->row()->exist;
            if($exist_name == 0){
            $sql = "update m_preschedulepromise SET 
            M_PreSchedulePromiseDayOfWeek = ?, 
            M_PreSchedulePromiseHour = ?,
            M_PreSchedulePromiseLastUpdated = now()
            WHERE
            M_PreSchedulePromiseID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['dayofweek'],
                    $prm['xhour'],
                    $prm['xid']
                )
            );

            if (!$query) {
                $this->sys_error_db("m_preschedulepromise update");
                exit;
            } 
            $result = array ("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        }else {
            $result = array ("total" => -1, "records" => 0);
			$this->sys_ok($result);
        }
        }
		
        
    
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   
   public function deletepreschedulepromise()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
       
        $sql = "update m_preschedulepromise SET 
            M_PreSchedulePromiseIsActive = 'N',
            M_PreSchedulePromiseLastUpdated = now()
            WHERE
            M_PreSchedulePromiseID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_preschedulepromise delete");
                exit;
            } 
        
		
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   function addnewschedulenewtest()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $schedulegroup_id = $prm['scheduleid'];   
        if($schedulegroup_id == 0){
            $this->sys_error("Silahkan pilih schedule group");
            exit;
        }
        $nattestid = $prm['testid'];
        $branchid = $prm['branchid'];
        $userid = $this->sys_user["M_UserID"];
        $sql = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test
        WHERE Nat_TestID = '{$nattestid}'")->row();	
        $tipeid = $sql->tipeid;

        $this->db_onedev->trans_start();

        if($tipeid == 4){
            $sql = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test
            WHERE T_TestNat_TestID = '{$nattestid}'")->row();	
            $tsascode = $sql->tsascode; 

            $sql = $this->db_onedev->query("SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild FROM t_test
            WHERE T_TestSasCode like concat('{$tsascode}','%') AND T_TestIsActive = 'Y'
            and T_TestIsResult = 'Y'")->row();	
            $tchild = $sql->tchild;
            //echo $tchild;
            $existparent = "UPDATE m_schedulegrouptest 
            JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND
            M_ScheduleGroupM_BranchID = {$branchid}
            SET 
            M_ScheduleGroupTestIsActive = 'N',
            M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
            M_ScheduleGroupTestLastUpdated = now()
            WHERE
            M_ScheduleGroupTestNat_TestID = $nattestid AND 
            M_ScheduleGroupTestIsActive = 'Y'";
            $delete_exist_tparent= $this->db_onedev->query($existparent);
            if(!$delete_exist_tparent){
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegrouptest update parent");
                exit;
            }

            $insertparent = "INSERT INTO m_schedulegrouptest (
                M_ScheduleGroupTestM_ScheduleGroupID,
                M_ScheduleGroupTestNat_TestID, 
                M_ScheduleGroupTestCreatedUserID,	 
                M_ScheduleGroupTestCreated
            )
            VALUES($schedulegroup_id,$nattestid,$userid,now())";
            $insert_tparent= $this->db_onedev->query($insertparent);
            if(!$insert_tparent){
                echo $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegrouptest insert parent");
                exit;
            }

            $existchild = "UPDATE m_schedulegrouptest 
            JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND
            M_ScheduleGroupM_BranchID = {$branchid}
            SET 
            M_ScheduleGroupTestIsActive = 'N',
            M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
            M_ScheduleGroupTestLastUpdated = now()
            WHERE
            M_ScheduleGroupTestNat_TestID IN($tchild) AND 
            M_ScheduleGroupTestIsActive = 'Y'";
            $delete_exist_tchild= $this->db_onedev->query($existchild);
            $insertchild = "INSERT INTO m_schedulegrouptest (
                M_ScheduleGroupTestM_ScheduleGroupID,
                M_ScheduleGroupTestNat_TestID, 
                M_ScheduleGroupTestCreatedUserID,	 
                M_ScheduleGroupTestCreated
            )
            SELECT $schedulegroup_id as M_ScheduleGroupTestM_ScheduleGroupID,
            Nat_TestID as M_ScheduleGroupTestNat_TestID,
            $userid as M_ScheduleGroupTestUserID,
            now() as M_ScheduleGroupTestCreated
            FROM nat_test
            WHERE Nat_TestID IN ($tchild) AND Nat_TestIsActive = 'Y'";
            $insert_tchild= $this->db_onedev->query($insertchild);
            if(!$insert_tchild){
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegrouptest insert child");
                exit;
            }

        }else{
            $existparent = "UPDATE m_schedulegrouptest
            JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND
            M_ScheduleGroupM_BranchID = {$branchid}
            SET 
            M_ScheduleGroupTestIsActive = 'N',
            M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
            M_ScheduleGroupTestLastUpdated = now()
            WHERE
            M_ScheduleGroupTestNat_TestID = $nattestid AND 
            M_ScheduleGroupTestIsActive = 'Y'";
            $delete_exist_tparent= $this->db_onedev->query($existparent);
            if(!$delete_exist_tparent){
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegrouptest update parent");
                exit;
            }
            
            $insertparent = "INSERT INTO m_schedulegrouptest (
                M_ScheduleGroupTestM_ScheduleGroupID,
                M_ScheduleGroupTestNat_TestID, 
                M_ScheduleGroupTestCreatedUserID,	 
                M_ScheduleGroupTestCreated
            )
            VALUES($schedulegroup_id,$nattestid,$userid,NOW())";
            $insert_tparent= $this->db_onedev->query($insertparent);
            if(!$insert_tparent){
                echo $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegrouptest insert parent");
                exit;
            }
        }	
	
        $this->db_onedev->trans_commit();
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   function delete_test() {
    try {
       //# cek token valid
       if (! $this->isLogin) {
          $this->sys_error("Invalid Token");
          exit;
       }
       $prm = $this->sys_input;
       $id = $prm['id'];
       $nattestid = $prm['id'];
       $scheduleid = $prm['scheduleid'];
       $sql_param = array( $id ,$scheduleid);
       $userid = $this->sys_user["M_UserID"];

       $sql = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test
       WHERE Nat_TestID = '{$nattestid}'")->row();	
       $tipeid = $sql->tipeid;
       if($tipeid == 4){
           $sql = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test
           WHERE T_TestNat_TestID = '{$nattestid}'")->row();	
           $tsascode = $sql->tsascode; 

           $sql = $this->db_onedev->query("SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild FROM t_test
           WHERE T_TestSasCode like concat('{$tsascode}','%') AND T_TestIsActive = 'Y'
           and T_TestIsResult = 'Y'")->row();	
           $tchild = $sql->tchild;


           $sqlparent = "UPDATE m_schedulegrouptest
           set M_ScheduleGroupTestIsActive = 'N',
           M_ScheduleGroupTestLastUpdated = NOW(),
           M_ScheduleGroupTestLastUpdatedUserID = {$userid}
           where M_ScheduleGroupTestNat_TestID = ?
           and M_ScheduleGroupTestM_ScheduleGroupID = ? AND
           M_ScheduleGroupTestIsActive = 'Y'";
           $queryparent = $this->db_onedev->query($sqlparent,$sql_param);

           $sqlchild = "UPDATE m_schedulegrouptest
           set M_ScheduleGroupTestIsActive = 'N',
           M_ScheduleGroupTestLastUpdated = NOW(),
           M_ScheduleGroupTestLastUpdatedUserID = {$userid}
           where M_ScheduleGroupTestNat_TestID IN($tchild)
           and M_ScheduleGroupTestM_ScheduleGroupID = $scheduleid AND
           M_ScheduleGroupTestIsActive = 'Y'";

           $querychild = $this->db_onedev->query($sqlchild);
           if (! $queryparent) {
              $this->sys_error_db( $this->db_onedev->last_query() );
              exit;
           }
       }else{
        $sql = "UPDATE m_schedulegrouptest
        set M_ScheduleGroupTestIsActive = 'N',
        M_ScheduleGroupTestLastUpdated = NOW(),
        M_ScheduleGroupTestLastUpdatedUserID = {$userid}
        where M_ScheduleGroupTestNat_TestID= ?
        and M_ScheduleGroupTestM_ScheduleGroupID=? AND
        M_ScheduleGroupTestIsActive = 'Y'";
        $query = $this->db_onedev->query($sql,$sql_param);
        if (! $query) {
           $this->sys_error_db( $this->db_onedev->last_query() );
           exit;
        }
       }
       
       $sql = "select * from m_schedulegrouptest
          where M_ScheduleGroupTestNat_TestID= ?
          and M_ScheduleGroupTestM_ScheduleGroupID=?";
       $query = $this->db_onedev->query($sql,$sql_param); 

       if ($query) {
          $rows = $query->result_array();
       } else {
          $this->sys_error_db("nat_test select by requirement");
          exit;
       }
       $result = array ("status" => "OK"); 
       $this->sys_ok($result);

    } catch(Exception $exc) {
       $message = $exc->getMessage();
       $this->sys_error($message);
    }

 }
   public function deletescheduletest()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];

        $this->db_onedev->trans_begin();
       
        $sql = "UPDATE  m_scheduletest SET 
        M_ScheduleTestIsActive = 'N',
        M_ScheduleTestLastUpdated = now(),
        M_ScheduleTestLastUpdatedUserID = {$userid}
            WHERE
            M_ScheduleTestID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                echo $this->db_onedev->last_query();
                $this->sys_error_db("m_scheduletest delete");
                exit;
            } 
        
		$this->db_onedev->trans_commit();
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   
   public function deleteschedule()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;

        $userid = $this->sys_user["M_UserID"];

        $this->db_onedev->trans_begin();

        $sql = "UPDATE m_schedulegroup SET 
        M_ScheduleGroupIsActive = 'N',
        M_ScheduleGroupLastUpdated = now(),
        M_ScheduleGroupLastUpdatedUserID = {$userid}
            WHERE
            M_ScheduleGroupID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedulegroup delete");
                exit;
            } 

            $sql = "UPDATE m_schedulegrouptest SET 
            M_ScheduleGroupTestIsActive = 'N',
            M_ScheduleGroupTestLastUpdated = NOW(),
            M_ScheduleGroupTestLastUpdatedUserID = {$userid}
                WHERE
                M_ScheduleGroupTestM_ScheduleGroupID = ?
                
                ";
    
                $query = $this->db_onedev->query($sql,
                    array(
                        $prm['id']
                    )
                );
               // echo $query;
                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegrouptest delete");
                    exit;
                } 
       
        $sql = "UPDATE m_schedule SET 
        M_ScheduleIsActive = 'N',
        M_ScheduleLastUpdate = NOW(),
        M_ScheduleLastUpdateUserID = {$userid}
            WHERE
            M_ScheduleM_ScheduleGroupID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->db_onedev->trans_rollback();
                $this->sys_error_db("m_schedule delete");
                exit;
            } 		
    
            

        $this->db_onedev->trans_commit();

        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }

   function searchtest(){
    if (! $this->isLogin) {
       $this->sys_error("Invalid Token");
       exit;
    }
    $prm = $this->sys_input;
    $search = $prm['search'];
    $schedulegroup_id = $prm['schedulegroup_id'];
    $branchid = isset($prm['branch']) ? $prm['branch'] : 0;
    if($branchid == 0){
        $branchid = isset($prm['branchid']) ? $prm['branchid'] : 0;
        if($branchid == 0){
            $this->sys_error("Silahkan pilih cabang");
            exit;
        }
        
    }


    $max_rst = 12;
    $tot_count =0;

    $sql = "SELECT DISTINCT M_ScheduleGroupTestNat_TestID as nat_testid
    FROM m_schedulegrouptest
    JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID AND M_ScheduleGroupM_BranchID = {$branchid}
    WHERE
    M_ScheduleGroupTestIsActive = 'Y'
    ";
    $query = $this->db_onedev->query($sql);
    if(!$query){
        $this->sys_error_db( $this->db_onedev->last_query() );
        exit;
    }

    $row_exist_test = $query->result_array();
    $exist_test = "";
    if(count($row_exist_test) > 0){
        foreach($row_exist_test as $k => $v){
            if($exist_test == ""){
                $exist_test = $v['nat_testid'];
            }else{
                $exist_test .= "," . $v['nat_testid'];
            }
        }
    }

    $filter = "";
    if($exist_test != ""){
        $filter = " AND Nat_TestID NOT IN({$exist_test})";
    }

    $sql = "SELECT Nat_TestID,Nat_TestCode,Nat_TestName, 0 as M_ScheduleGroupTestID, 0 as xid
    FROM nat_test
    WHERE Nat_TestName LIKE '%$search%' $filter
    AND Nat_TestIsActive = 'Y' 
    ORDER BY Nat_TestName ASC
    ";
    $query = $this->db_onedev->query($sql);

    if ($query) { 
       $rows = $query->result_array();
       //echo $this->db->last_query();
       $tot_count = count($rows);
       $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
       $this->sys_ok($result);
    } 
    else {
       $this->sys_error_db("nat_test rows",$this->db);
       exit;
    }
 }
   
   public function searchtestx()
   {
      $prm = $this->sys_input;
	  $scheduleid = $prm['id'];
      $max_rst = 12;
      $tot_count =0;

      $q = [
         'search'     => '%'
      ];

      if ($prm['search'] != '')
      {
         $q['search'] = "%{$prm['search']}%";  
      }

      // QUERY TOTAL
      $sql = "SELECT count(*) as total
		FROM t_test
		LEFT JOIN m_scheduletest ON M_ScheduleTestT_TestID = T_TestID AND M_ScheduleTestM_ScheduleID = ? AND M_ScheduleTestIsActive = 'Y'
		WHERE 
        ISNULL(M_ScheduleTestID) AND

		T_TestIsActive = 'Y'
		AND T_TestName like ?";
      $query = $this->db_onedev->query($sql,array($scheduleid,$q['search']));
      
      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("t_test count",$this->db_onedev);
         exit;
      }

      $sql = "
		SELECT T_TestID as id, 
		T_TestName as name
		FROM t_test
		LEFT JOIN m_scheduletest ON M_ScheduleTestT_TestID = T_TestID AND M_ScheduleTestM_ScheduleID = ? AND M_ScheduleTestIsActive = 'Y'
		WHERE 
        ISNULL(M_ScheduleTestID) AND
		T_TestIsActive = 'Y'
		AND T_TestName like ?
        ORDER BY T_TestCode ASC
		
	  ";
      $query = $this->db_onedev->query($sql, array($scheduleid,$q['search']));
		
      if ($query) { 
         $rows = $query->result_array();
        //echo $this->db_onedev->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("t_test rows",$this->db_onedev);
         exit;
      }
   }

   public function checkcloneconflicts()
   {
    try {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $source_id = isset($prm['id']) ? (int)$prm['id'] : 0;
        $target_branch = isset($prm['target_branch']) ? (int)$prm['target_branch'] : 0;

        if ($source_id === 0 || $target_branch === 0) {
            $this->sys_error("Parameter tidak lengkap");
            exit;
        }

        // ambil schedule sumber
        $sql = "SELECT * FROM m_schedulegroup WHERE M_ScheduleGroupID = ? AND M_ScheduleGroupIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, array($source_id));
        if (!$query) {
            $this->sys_error_db("m_schedulegroup select source");
            exit;
        }
        $rows = $query->result_array();
        if (count($rows) == 0) {
            $this->sys_error("Schedule group not found");
            exit;
        }

        $source = $rows[0];
        $source_branch = (int)$source['M_ScheduleGroupM_BranchID'];

        if ($source_branch === $target_branch) {
            $this->sys_error("Cabang tujuan tidak boleh sama dengan cabang asal");
            exit;
        }

        // ambil semua test aktif schedule sumber
        $tests = $this->add_test($source_id);
        $conflict_tests = array();

        if ($tests && count($tests) > 0) {
            foreach ($tests as $v) {
                $nattestid = isset($v['Nat_TestID']) ? $v['Nat_TestID'] : $v['M_ScheduleGroupTestNat_TestID'];
                $testname = isset($v['Nat_TestName']) ? $v['Nat_TestName'] : '';

                $rowtipe = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test WHERE Nat_TestID = '{$nattestid}'")->row();
                if (!$rowtipe) {
                    continue;
                }
                $tipeid = $rowtipe->tipeid;

                if ($tipeid == 4) {
                    // cek parent
                    $sql_check = "SELECT DISTINCT sgt.M_ScheduleGroupTestNat_TestID, nt.Nat_TestName, sg.M_ScheduleGroupName
                                  FROM m_schedulegrouptest sgt
                                  JOIN m_schedulegroup sg ON sgt.M_ScheduleGroupTestM_ScheduleGroupID = sg.M_ScheduleGroupID
                                  JOIN nat_test nt ON sgt.M_ScheduleGroupTestNat_TestID = nt.Nat_TestID
                                  WHERE sg.M_ScheduleGroupM_BranchID = {$target_branch}
                                    AND sgt.M_ScheduleGroupTestNat_TestID = {$nattestid}
                                    AND sgt.M_ScheduleGroupTestIsActive = 'Y'";
                    $check_result = $this->db_onedev->query($sql_check)->result_array();
                    
                    if ($check_result && count($check_result) > 0) {
                        foreach ($check_result as $conflict) {
                            $conflict_tests[] = array(
                                'Nat_TestID' => $conflict['M_ScheduleGroupTestNat_TestID'],
                                'Nat_TestName' => $conflict['Nat_TestName'],
                                'ScheduleName' => $conflict['M_ScheduleGroupName'],
                                'type' => 'parent'
                            );
                        }
                    }

                    // cek child
                    $rowcode = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test WHERE T_TestNat_TestID = '{$nattestid}'")->row();
                    if ($rowcode) {
                        $tsascode = $rowcode->tsascode;
                        $rowchild = $this->db_onedev->query("
                            SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild
                            FROM t_test
                            WHERE T_TestSasCode LIKE CONCAT('{$tsascode}','%')
                              AND T_TestIsActive = 'Y'
                              AND T_TestIsResult = 'Y'
                        ")->row();
                        $tchild = $rowchild ? $rowchild->tchild : null;

                        if ($tchild) {
                            $sql_check_child = "SELECT DISTINCT sgt.M_ScheduleGroupTestNat_TestID, nt.Nat_TestName, sg.M_ScheduleGroupName
                                                FROM m_schedulegrouptest sgt
                                                JOIN m_schedulegroup sg ON sgt.M_ScheduleGroupTestM_ScheduleGroupID = sg.M_ScheduleGroupID
                                                JOIN nat_test nt ON sgt.M_ScheduleGroupTestNat_TestID = nt.Nat_TestID
                                                WHERE sg.M_ScheduleGroupM_BranchID = {$target_branch}
                                                  AND sgt.M_ScheduleGroupTestNat_TestID IN ({$tchild})
                                                  AND sgt.M_ScheduleGroupTestIsActive = 'Y'";
                            $check_child_result = $this->db_onedev->query($sql_check_child)->result_array();
                            
                            if ($check_child_result && count($check_child_result) > 0) {
                                foreach ($check_child_result as $conflict) {
                                    $conflict_tests[] = array(
                                        'Nat_TestID' => $conflict['M_ScheduleGroupTestNat_TestID'],
                                        'Nat_TestName' => $conflict['Nat_TestName'],
                                        'ScheduleName' => $conflict['M_ScheduleGroupName'],
                                        'type' => 'child'
                                    );
                                }
                            }
                        }
                    }
                } else {
                    // tipe biasa
                    $sql_check = "SELECT DISTINCT sgt.M_ScheduleGroupTestNat_TestID, nt.Nat_TestName, sg.M_ScheduleGroupName
                                  FROM m_schedulegrouptest sgt
                                  JOIN m_schedulegroup sg ON sgt.M_ScheduleGroupTestM_ScheduleGroupID = sg.M_ScheduleGroupID
                                  JOIN nat_test nt ON sgt.M_ScheduleGroupTestNat_TestID = nt.Nat_TestID
                                  WHERE sg.M_ScheduleGroupM_BranchID = {$target_branch}
                                    AND sgt.M_ScheduleGroupTestNat_TestID = {$nattestid}
                                    AND sgt.M_ScheduleGroupTestIsActive = 'Y'";
                    $check_result = $this->db_onedev->query($sql_check)->result_array();
                    
                    if ($check_result && count($check_result) > 0) {
                        foreach ($check_result as $conflict) {
                            $conflict_tests[] = array(
                                'Nat_TestID' => $conflict['M_ScheduleGroupTestNat_TestID'],
                                'Nat_TestName' => $conflict['Nat_TestName'],
                                'ScheduleName' => $conflict['M_ScheduleGroupName'],
                                'type' => 'normal'
                            );
                        }
                    }
                }
            }
        }

        // remove duplicates berdasarkan Nat_TestID
        $unique_conflicts = array();
        $seen_ids = array();
        foreach ($conflict_tests as $conflict) {
            if (!in_array($conflict['Nat_TestID'], $seen_ids)) {
                $unique_conflicts[] = $conflict;
                $seen_ids[] = $conflict['Nat_TestID'];
            }
        }

        $result = array(
            "total" => 1,
            "records" => array("has_conflicts" => count($unique_conflicts) > 0),
            "conflict_tests" => $unique_conflicts,
            "conflict_count" => count($unique_conflicts)
        );
        $this->sys_ok($result);

    } catch (Exception $exc) {
        $this->sys_error($exc->getMessage());
    }
   }

   public function cloneschedulegroup()
   {
    try {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $source_id = isset($prm['id']) ? (int)$prm['id'] : 0;
        $target_branch = isset($prm['target_branch']) ? (int)$prm['target_branch'] : 0;
        $userid = $this->sys_user["M_UserID"];

        $all_conflict_ids = isset($prm['all_conflict_ids']) ? $prm['all_conflict_ids'] : array();
        $selected_conflict_ids = isset($prm['selected_conflict_ids']) ? $prm['selected_conflict_ids'] : array();

        if (!is_array($all_conflict_ids)) {
            $all_conflict_ids = array();
        }
        if (!is_array($selected_conflict_ids)) {
            $selected_conflict_ids = array();
        }

        // normalisasi ke integer
        $all_conflict_ids = array_map('intval', $all_conflict_ids);
        $selected_conflict_ids = array_map('intval', $selected_conflict_ids);

        $all_conflict_map = array_flip($all_conflict_ids);
        $selected_conflict_map = array_flip($selected_conflict_ids);

        if ($source_id === 0 || $target_branch === 0) {
            $this->sys_error("Parameter tidak lengkap");
            exit;
        }

        // ambil schedule sumber
        $sql = "SELECT * FROM m_schedulegroup WHERE M_ScheduleGroupID = ? AND M_ScheduleGroupIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, array($source_id));
        if (!$query) {
            $this->sys_error_db("m_schedulegroup select source");
            exit;
        }
        $rows = $query->result_array();
        if (count($rows) == 0) {
            $this->sys_error("Schedule group not found");
            exit;
        }

        $source = $rows[0];
        $name = $source['M_ScheduleGroupName'];
        $source_branch = (int)$source['M_ScheduleGroupM_BranchID'];

        if ($source_branch === $target_branch) {
            $this->sys_error("Cabang tujuan tidak boleh sama dengan cabang asal");
            exit;
        }

        // pastikan nama schedule belum ada di cabang tujuan
        $sql = "SELECT COUNT(*) as total FROM m_schedulegroup
                WHERE M_ScheduleGroupName = ? AND M_ScheduleGroupM_BranchID = ? AND M_ScheduleGroupIsActive = 'Y'";
        $query = $this->db_onedev->query($sql, array($name, $target_branch));
        if (!$query) {
            $this->sys_error_db("m_schedulegroup check name");
            exit;
        }
        if ((int)$query->row()->total > 0) {
            $this->sys_error("Nama Schedule sudah ada di cabang tujuan");
            exit;
        }

        // ambil semua test aktif schedule sumber
        $tests = $this->add_test($source_id);

        // Catatan: Jika user hilangkan centang (skip), test tersebut TIDAK akan di-clone dan TIDAK akan menonaktifkan yang lama.
        // Itu adalah pilihan yang valid - tidak perlu pre-check error. Kita cukup skip test tersebut saat clone.

        $this->db_onedev->trans_start();

        // insert group baru
        $sql = "INSERT INTO m_schedulegroup(
                    M_ScheduleGroupName,
                    M_ScheduleGroupM_BranchID,
                    M_ScheduleGroupCreatedUserID,
                    M_ScheduleGroupCreated
                ) VALUES (?,?,?,NOW())";
        $ok = $this->db_onedev->query($sql, array($name, $target_branch, $userid));
        if (!$ok) {
            $this->db_onedev->trans_rollback();
            $this->sys_error_db("m_schedulegroup insert clone");
            exit;
        }
        $new_id = $this->db_onedev->insert_id();

        // clone jadwal (time slot) di tabel m_schedule untuk schedule group ini
        // Semua jadwal aktif dari schedule group sumber akan di-copy ke schedule group baru
        $sql = "INSERT INTO m_schedule(
                    M_ScheduleDayOfWeek,
                    M_ScheduleM_ScheduleGroupID,
                    M_ScheduleStartHourMinute,
                    M_ScheduleEndHourMinute,
                    M_ScheduleMonth,
                    M_ScheduleWeek,
                    M_ScheduleDay,
                    M_ScheduleHour,
                    M_ScheduleMinute,
                    M_ScheduleFlagAtTime,
                    M_ScheduleAtTime,
                    M_ScheduleIsActive,
                    M_ScheduleCreatedUserID,
                    M_ScheduleCreated
                )
                SELECT
                    M_ScheduleDayOfWeek,
                    {$new_id} as M_ScheduleM_ScheduleGroupID,
                    M_ScheduleStartHourMinute,
                    M_ScheduleEndHourMinute,
                    M_ScheduleMonth,
                    M_ScheduleWeek,
                    M_ScheduleDay,
                    M_ScheduleHour,
                    M_ScheduleMinute,
                    M_ScheduleFlagAtTime,
                    M_ScheduleAtTime,
                    'Y' as M_ScheduleIsActive,
                    {$userid} as M_ScheduleCreatedUserID,
                    NOW() as M_ScheduleCreated
                FROM m_schedule
                WHERE M_ScheduleM_ScheduleGroupID = {$source_id}
                  AND M_ScheduleIsActive = 'Y'";
        if (!$this->db_onedev->query($sql)) {
            $this->db_onedev->trans_rollback();
            $this->sys_error_db("m_schedule insert clone");
            exit;
        }

        // clone test dengan rule "no duplicate Nat_TestID per branch"
        // STEP 1: Kumpulkan semua test yang akan di-clone (termasuk child untuk type 4)
        // Gunakan array dengan key untuk menghindari duplikat
        $tests_to_clone = array(); // array dengan key Nat_TestID untuk menyimpan test yang akan di-clone
        $all_test_ids_to_clone = array(); // array untuk menyimpan semua Nat_TestID yang akan di-clone (parent + child)
        $deactivated_tests = array(); // untuk tracking test yang di-deactivate
        
        if ($tests && count($tests) > 0) {
            foreach ($tests as $v) {
                $nattestid = isset($v['Nat_TestID']) ? (int)$v['Nat_TestID'] : (int)$v['M_ScheduleGroupTestNat_TestID'];
                $testname = isset($v['Nat_TestName']) ? $v['Nat_TestName'] : '';

                // Skip jika test ini sudah ada di array (menghindari duplikat)
                if (isset($tests_to_clone[$nattestid])) {
                    continue;
                }

                // jika test ini termasuk konflik dan TIDAK dipilih user, skip (tidak clone & tidak deactivate)
                $is_conflict = isset($all_conflict_map[$nattestid]);
                $is_selected = isset($selected_conflict_map[$nattestid]);
                if ($is_conflict && !$is_selected) {
                    continue;
                }

                $rowtipe = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test WHERE Nat_TestID = '{$nattestid}'")->row();
                if (!$rowtipe) {
                    continue; // skip jika test tidak ditemukan
                }
                $tipeid = $rowtipe->tipeid;

                if ($tipeid == 4) {
                    // dapatkan child test seperti di addnewschedulenewtest
                    $rowcode = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test WHERE T_TestNat_TestID = '{$nattestid}'")->row();
                    if (!$rowcode) {
                        continue;
                    }
                    $tsascode = $rowcode->tsascode;

                    $rowchild = $this->db_onedev->query("
                        SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild
                        FROM t_test
                        WHERE T_TestSasCode LIKE CONCAT('{$tsascode}','%')
                          AND T_TestIsActive = 'Y'
                          AND T_TestIsResult = 'Y'
                    ")->row();
                    $tchild = $rowchild ? $rowchild->tchild : null;

                    // simpan parent test dengan key Nat_TestID untuk menghindari duplikat
                    $tests_to_clone[$nattestid] = array(
                        'Nat_TestID' => $nattestid,
                        'Nat_TestName' => $testname,
                        'type' => 4,
                        'tchild' => $tchild
                    );
                    
                    // tambahkan parent ke list test yang akan di-clone (jika belum ada)
                    if (!in_array($nattestid, $all_test_ids_to_clone)) {
                        $all_test_ids_to_clone[] = $nattestid;
                    }
                    
                    // tambahkan child ke list test yang akan di-clone
                    if ($tchild) {
                        $child_ids = explode(',', $tchild);
                        foreach ($child_ids as $child_id) {
                            $child_id = (int)trim($child_id);
                            if ($child_id > 0 && !in_array($child_id, $all_test_ids_to_clone)) {
                                $all_test_ids_to_clone[] = $child_id;
                            }
                        }
                    }
                } else {
                    // simpan normal test dengan key Nat_TestID untuk menghindari duplikat
                    $tests_to_clone[$nattestid] = array(
                        'Nat_TestID' => $nattestid,
                        'Nat_TestName' => $testname,
                        'type' => $tipeid,
                        'tchild' => null
                    );
                    
                    // tambahkan ke list test yang akan di-clone (jika belum ada)
                    if (!in_array($nattestid, $all_test_ids_to_clone)) {
                        $all_test_ids_to_clone[] = $nattestid;
                    }
                }
            }
        }

        // STEP 2: UPDATE hanya untuk test yang dipilih user untuk di-nonaktifkan (sebelum insert)
        // Hanya update test yang ada di selected_conflict_ids (yang dipilih user untuk di-nonaktifkan)
        if (count($selected_conflict_ids) > 0) {
            // Untuk setiap test yang dipilih untuk di-nonaktifkan, perlu cek apakah type 4 (parent)
            // Jika type 4, perlu nonaktifkan parent + child-nya juga
            $test_ids_to_deactivate = array();
            
            foreach ($selected_conflict_ids as $conflict_test_id) {
                $test_ids_to_deactivate[] = $conflict_test_id;
                
                // Cek apakah test ini type 4 (parent)
                $rowtipe = $this->db_onedev->query("SELECT Nat_TestNat_TestTypeID as tipeid FROM nat_test WHERE Nat_TestID = '{$conflict_test_id}'")->row();
                if ($rowtipe && (int)$rowtipe->tipeid == 4) {
                    // Ambil child test
                    $rowcode = $this->db_onedev->query("SELECT T_TestSasCode as tsascode FROM t_test WHERE T_TestNat_TestID = '{$conflict_test_id}'")->row();
                    if ($rowcode && $rowcode->tsascode) {
                        $tsascode = $rowcode->tsascode;
                        $rowchild = $this->db_onedev->query("
                            SELECT GROUP_CONCAT(DISTINCT T_TestNat_TestID SEPARATOR ',') as tchild
                            FROM t_test
                            WHERE T_TestSasCode LIKE CONCAT('{$tsascode}','%')
                              AND T_TestIsActive = 'Y'
                              AND T_TestIsResult = 'Y'
                        ")->row();
                        if ($rowchild && $rowchild->tchild) {
                            $child_ids = explode(',', $rowchild->tchild);
                            foreach ($child_ids as $child_id) {
                                $child_id = (int)trim($child_id);
                                if ($child_id > 0) {
                                    $test_ids_to_deactivate[] = $child_id;
                                }
                            }
                        }
                    }
                }
            }
            
            // Hapus duplikat
            $test_ids_to_deactivate = array_unique($test_ids_to_deactivate);
            
            if (count($test_ids_to_deactivate) > 0) {
                $test_ids_str = implode(',', $test_ids_to_deactivate);
                
                // cek test yang akan di-deactivate (untuk tracking)
                $sql_check_deactivate = "SELECT DISTINCT sgt.M_ScheduleGroupTestNat_TestID, nt.Nat_TestName
                                         FROM m_schedulegrouptest sgt
                                         JOIN m_schedulegroup sg ON sgt.M_ScheduleGroupTestM_ScheduleGroupID = sg.M_ScheduleGroupID
                                         JOIN nat_test nt ON sgt.M_ScheduleGroupTestNat_TestID = nt.Nat_TestID
                                         WHERE sg.M_ScheduleGroupM_BranchID = {$target_branch}
                                           AND sgt.M_ScheduleGroupTestNat_TestID IN ({$test_ids_str})
                                           AND sgt.M_ScheduleGroupTestIsActive = 'Y'";
                $check_deactivate_result = $this->db_onedev->query($sql_check_deactivate)->result_array();
                
                // track test yang di-deactivate
                if ($check_deactivate_result && count($check_deactivate_result) > 0) {
                    foreach ($check_deactivate_result as $deact) {
                        $deactivated_tests[] = array(
                            'Nat_TestID' => $deact['M_ScheduleGroupTestNat_TestID'],
                            'Nat_TestName' => $deact['Nat_TestName'],
                            'type' => 'normal'
                        );
                    }
                }
                
                // UPDATE hanya test yang dipilih user untuk di-nonaktifkan (kecuali yang baru dibuat)
                $sql = "UPDATE m_schedulegrouptest
                        JOIN m_schedulegroup ON M_ScheduleGroupTestM_ScheduleGroupID = M_ScheduleGroupID
                         AND M_ScheduleGroupM_BranchID = {$target_branch}
                        SET M_ScheduleGroupTestIsActive = 'N',
                            M_ScheduleGroupTestLastUpdatedUserID = '{$userid}',
                            M_ScheduleGroupTestLastUpdated = NOW()
                        WHERE M_ScheduleGroupTestNat_TestID IN ({$test_ids_str})
                          AND M_ScheduleGroupTestIsActive = 'Y'
                          AND M_ScheduleGroupTestM_ScheduleGroupID <> {$new_id}";
                if (!$this->db_onedev->query($sql)) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("m_schedulegrouptest update before clone");
                    exit;
                }
            }
        }

        // STEP 3: INSERT semua test yang akan di-clone
        // Track test yang sudah di-insert untuk menghindari duplikat
        $inserted_test_ids = array();
        
        if (count($tests_to_clone) > 0) {
            foreach ($tests_to_clone as $test_data) {
                $nattestid = $test_data['Nat_TestID'];
                $tipeid = $test_data['type'];
                $tchild = $test_data['tchild'];

                if ($tipeid == 4) {
                    // insert parent (cek dulu apakah sudah di-insert)
                    if (!in_array($nattestid, $inserted_test_ids)) {
                        // Cek dulu apakah sudah ada di database (double check)
                        $sql_check = "SELECT COUNT(*) as cnt FROM m_schedulegrouptest
                                      WHERE M_ScheduleGroupTestM_ScheduleGroupID = {$new_id}
                                        AND M_ScheduleGroupTestNat_TestID = {$nattestid}";
                        $check_result = $this->db_onedev->query($sql_check)->row();
                        if ($check_result && (int)$check_result->cnt == 0) {
                            $sql = "INSERT INTO m_schedulegrouptest(
                                        M_ScheduleGroupTestM_ScheduleGroupID,
                                        M_ScheduleGroupTestNat_TestID,
                                        M_ScheduleGroupTestCreatedUserID,
                                        M_ScheduleGroupTestCreated
                                    ) VALUES ({$new_id},{$nattestid},{$userid},NOW())";
                            if (!$this->db_onedev->query($sql)) {
                                $this->db_onedev->trans_rollback();
                                $this->sys_error_db("m_schedulegrouptest insert parent (clone)");
                                exit;
                            }
                            $inserted_test_ids[] = $nattestid;
                        }
                    }

                    // insert child (hanya yang belum di-insert)
                    if ($tchild) {
                        $child_ids = explode(',', $tchild);
                        $child_ids_to_insert = array();
                        foreach ($child_ids as $child_id) {
                            $child_id = (int)trim($child_id);
                            if ($child_id > 0 && !in_array($child_id, $inserted_test_ids)) {
                                $child_ids_to_insert[] = $child_id;
                            }
                        }
                        
                        if (count($child_ids_to_insert) > 0) {
                            $child_ids_str = implode(',', $child_ids_to_insert);
                            // Insert child satu per satu untuk memastikan tidak ada duplikat dan tracking yang akurat
                            foreach ($child_ids_to_insert as $child_id) {
                                if (!in_array($child_id, $inserted_test_ids)) {
                                    $sql = "INSERT INTO m_schedulegrouptest(
                                                M_ScheduleGroupTestM_ScheduleGroupID,
                                                M_ScheduleGroupTestNat_TestID,
                                                M_ScheduleGroupTestCreatedUserID,
                                                M_ScheduleGroupTestCreated
                                            )
                                            SELECT {$new_id},
                                                   Nat_TestID,
                                                   {$userid},
                                                   NOW()
                                            FROM nat_test
                                            WHERE Nat_TestID = {$child_id}
                                              AND Nat_TestIsActive = 'Y'
                                              AND NOT EXISTS (
                                                  SELECT 1 FROM m_schedulegrouptest
                                                  WHERE M_ScheduleGroupTestM_ScheduleGroupID = {$new_id}
                                                    AND M_ScheduleGroupTestNat_TestID = {$child_id}
                                              )
                                            LIMIT 1";
                                    if (!$this->db_onedev->query($sql)) {
                                        $this->db_onedev->trans_rollback();
                                        $this->sys_error_db("m_schedulegrouptest insert child (clone)");
                                        exit;
                                    }
                                    // Track child yang sudah di-insert
                                    $inserted_test_ids[] = $child_id;
                                }
                            }
                        }
                    }
                } else {
                    // insert normal test (cek dulu apakah sudah di-insert)
                    if (!in_array($nattestid, $inserted_test_ids)) {
                        // Cek dulu apakah sudah ada di database (double check)
                        $sql_check = "SELECT COUNT(*) as cnt FROM m_schedulegrouptest
                                      WHERE M_ScheduleGroupTestM_ScheduleGroupID = {$new_id}
                                        AND M_ScheduleGroupTestNat_TestID = {$nattestid}";
                        $check_result = $this->db_onedev->query($sql_check)->row();
                        if ($check_result && (int)$check_result->cnt == 0) {
                            $sql = "INSERT INTO m_schedulegrouptest(
                                        M_ScheduleGroupTestM_ScheduleGroupID,
                                        M_ScheduleGroupTestNat_TestID,
                                        M_ScheduleGroupTestCreatedUserID,
                                        M_ScheduleGroupTestCreated
                                    ) VALUES ({$new_id},{$nattestid},{$userid},NOW())";
                            if (!$this->db_onedev->query($sql)) {
                                $this->db_onedev->trans_rollback();
                                $this->sys_error_db("m_schedulegrouptest insert normal (clone)");
                                exit;
                            }
                            $inserted_test_ids[] = $nattestid;
                        }
                    }
                }
            }
        }

        $this->db_onedev->trans_commit();
        $result = array(
            "total" => 1, 
            "records" => array("xid" => $new_id),
            "deactivated_tests" => $deactivated_tests,
            "deactivated_count" => count($deactivated_tests)
        );
        $this->sys_ok($result);

    } catch (Exception $exc) {
        $this->db_onedev->trans_rollback();
        $this->sys_error($exc->getMessage());
    }
   }
}