<?php

class Companyuseraccess extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "COMPANY USER ACCESS API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
	
	
	function lookupuser(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
				$this->sys_error("Invalid Token");
				exit;
            }
            $prm = $this->sys_input;
			$id = $prm['id'];
            $sql = "select M_UserID as id,m_user.*,M_CompanyName
                    
                    from m_user 
                    LEFT JOIN m_company ON M_UserM_CompanyID = M_CompanyID
                    where 
                    M_UserM_CompanyID = {$id} AND M_UserIsActive = 'Y'";
				//	echo $sql;
                $query = $this->db_onedev->query($sql, $sql_param);
                $rows = $query->result_array();
            if($rows){
            }

            $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 = $prm['search'];
		$all = $prm['all'];
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
		}
		$sql = "select COUNT(*) as total
                from m_company 
                where 
                M_CompanyIsActive = 'Y'";
        $sql_param = array($search);
        $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
		
		
        $sql = "select M_CompanyID as id, M_CompanyName as name, M_CompanyAddress as address, M_CompanyPhone as phone, M_CompanyEmail as email,M_CompanyFax as fax, M_CompanyPIC as pic,
        CONCAT(M_CompanyName,' ' ,'[ ',IFNULL(Nat_HierarchyName,''),' ]') as description , M_CompanyTypeID, M_CompanyM_CompanyTypeID as companytypeid, M_CompanyTypeName as companytypename,
        M_CompanyIsLabFrom as islabfrom, M_CompanyIsLabTo as islabto, M_CompanyIsDefault as isdefault,'xxx' as companymou,
        M_CompanyBusinessID,M_CompanyBusinessName,Nat_CompanyLevelID,Nat_CompanyLevelName,CONCAT(Nat_CompanyLevelName,' [',M_CompanyName,']') as Nat_CompanyLevelNames ,M_ProvinceID,M_ProvinceName,M_StaffID,M_StaffName,M_CompanyNumber,M_CompanyIDParent,
        Nat_HierarchyID,Nat_HierarchyName,M_CompanyFax,
        M_CityID, M_CityName, M_DistrictID, M_DistrictName, M_KelurahanID, M_KelurahanName, M_DoctorID, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorNames
                from m_company 
                LEFT JOIN m_companytype ON M_CompanyM_CompanyTypeID = M_CompanyTypeID
                LEFT JOIN m_kelurahan ON M_CompanyM_KelurahanID = M_KelurahanID
                LEFT JOIN m_district ON M_CompanyM_DistrictID = M_DistrictID
                LEFT JOIN m_city ON M_CompanyM_CityID = M_CityID
                LEFT JOIN m_province ON M_CompanyM_ProvinceID = M_ProvinceID
                LEFT JOIN m_companybusiness ON M_CompanyM_CompanyBusinessID = M_CompanyBusinessID
                LEFT JOIN m_staff ON M_CompanyM_StaffID = M_StaffID
                LEFT JOIN nat_companylevel ON M_CompanyNat_CompanyLevelID = Nat_CompanyLevelID
                LEFT JOIN nat_hierarchy ON M_CompanyNat_HierarchyID = Nat_HierarchyID
                LEFT JOIN m_doctor ON M_CompanyM_DoctorID = M_DoctorID
                where 
                ( 	M_CompanyName LIKE CONCAT('%','{$search}','%') 
				)AND
                M_CompanyIsActive = 'Y' $limit";
        $sql_param = array($search);
        $query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("m_company 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);
    }
   }
   
   
   public function addnewcompany()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $name = $prm['name'];
        $idparent = $prm['idparent'];
        $companytype = $prm['companytype'];
        $companybusiness = $prm['companybusiness'];
        $hierarkiid = $prm['hierarkiid'];
        $companylevelid = $prm['companylevelid'];
        $staffid = $prm['staffid'];
        $province = $prm['province'];
        $city = $prm['city'];
        $district = $prm['district'];
        $kelurahan = $prm['kelurahan'];
        $address = $prm['address'];
        $email = $prm['email'];
        $phone = $prm['phone'];
        $fax = $prm['fax'];
        $isdefault = $prm['isdefault'];

            $sql = "insert into m_company(
                M_CompanyName,
                M_CompanyIDParent,	
                M_CompanyM_CompanyTypeID,
                M_CompanyM_CompanyBusinessID,
                M_CompanyNat_HierarchyID,
                M_CompanyNat_CompanyLevelID,
                M_CompanyM_StaffID,	
                M_CompanyM_ProvinceID,
                M_CompanyM_CityID,
                M_CompanyM_DistrictID,	
                M_CompanyM_KelurahanID,
                M_CompanyAddress,	
                M_CompanyEmail,	
                M_CompanyPhone,	
                M_CompanyFax,	            	              
                M_CompanyIsDefault,
                M_CompanyCreated,
                M_CompanyLastUpdated
				) 
				values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,now(), now())";
			$query = $this->db_onedev->query($sql,
				array(
                    $name,
                    $idparent,
                    $companytype,
                    $companybusiness,
                    $hierarkiid,
                    $companylevelid,
                    $staffid,
                    $province,
                    $city,
                    $district,
                    $kelurahan,
                    $address,
                    $email,
                    $phone,
                    $fax,
                    $isdefault
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("m_company insert");
				exit;
			} 
			
			$result = array ("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
            $last_id = $this->db_onedev->insert_id();
            if($isdefault == 'Y'){
                $querydefault ="UPDATE m_company SET      
                M_CompanyIsDefault = 'N' WHERE M_CompanyIsDefault = 'Y'
                AND M_CompanyID <> '{$last_id}' AND M_CompanyIsActive = 'Y'
            ";
            $rows = $this->db_onedev->query($querydefault);
            }
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function editcompany()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $id_company = $prm['id'];
		$name = $prm['name'];
        $idparent = $prm['idparent'];
        $companytype = $prm['companytype'];
        $companybusiness = $prm['companybusiness'];
        $hierarkiid = $prm['hierarkiid'];
        $companylevelid = $prm['companylevelid'];
        $staffid = $prm['staffid'];
        $province = $prm['province'];
        $city = $prm['city'];
        $district = $prm['district'];
        $kelurahan = $prm['kelurahan'];
        $address = $prm['address'];
        $email = $prm['email'];
        $phone = $prm['phone'];
        $fax = $prm['fax'];
        $isdefault = $prm['isdefault'];

          $sqlcompany = "update m_company SET 
          M_CompanyName = ?,
          M_CompanyIDParent = ?,	
          M_CompanyM_CompanyTypeID = ?,
          M_CompanyM_CompanyBusinessID = ?,
          M_CompanyNat_HierarchyID = ?,
          M_CompanyNat_CompanyLevelID = ?,
          M_CompanyM_StaffID = ?,
          M_CompanyM_ProvinceID = ?,
          M_CompanyM_CityID = ?,
          M_CompanyM_DistrictID = ?,	
          M_CompanyM_KelurahanID = ?,
          M_CompanyAddress = ?,
          M_CompanyEmail = ?,	
          M_CompanyPhone = ?,	
          M_CompanyFax = ?,            	              
          M_CompanyIsDefault = ?,
          M_CompanyLastUpdated = now()
              where
              M_CompanyID = ?
              ";
          $querycompany = $this->db_onedev->query($sqlcompany,
              array(
                $name,
                $idparent,
                $companytype,
                $companybusiness,
                $hierarkiid,
                $companylevelid,
                $staffid,
                $province,
                $city,
                $district,
                $kelurahan,
                $address,
                $email,
                $phone,
                $fax,
                $isdefault,
                 $id_company
              )
          );
        //  echo $query;
          if (!$querycompany) {
              $this->sys_error_db("m_company update");
              exit;
          } 
          $result = array ("total" => 1, "records" => array("xid" => $id_company));
          $this->sys_ok($result);
          if($isdefault == 'Y'){
              $querydefault ="UPDATE m_company SET      
              M_CompanyIsDefault = 'N' WHERE M_CompanyIsDefault = 'Y' AND M_CompanyIsActive = 'Y'
              AND M_CompanyID <> '{$id_company}'
          ";
          $rows = $this->db_onedev->query($querydefault);
          }
         
			
		

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function addnewcompanytype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $name_companytype = $prm['name'];
        $userid = $this->sys_user["M_UserID"];
            $sql = "insert into m_companytype(
                M_CompanyTypeName,
                M_CompanyTypeUserID,
                M_CompanyTypeCreated,
                M_CompanyTypeLastUpdated
				) 
				values(?,?,now(),now())";
			$query = $this->db_onedev->query($sql,
				array(
                    $name_companytype,
                    $userid
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("m_companytype insert");
				exit;
			} 
			$rows = [];
            $query ="	SELECT *, COUNT(M_CompanyID) as used
FROM (SELECT m_companytype.*,M_CompanyID
FROM
m_companytype
LEFT JOIN m_company ON M_CompanyTypeID = M_CompanyM_CompanyTypeID AND M_CompanyIsActive = 'Y'
WHERE M_CompanyTypeIsActive = 'Y') a
GROUP BY M_CompanyTypeID
                    ";
                    //echo $query;
            $rows['companytypes'] = $this->db_onedev->query($query)->result_array();
			$result = array ("total" => 1, "records" => $rows);
            $this->sys_ok($result);
            $last_id = $this->db_onedev->insert_id();
        
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function editcompanytype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        //# ambil parameter input
        $prm = $this->sys_input;
		$id_company = $prm['id'];
        $name_company = $prm['name'];
        $userid = $this->sys_user["M_UserID"];
          $sqlcompany = "update m_companytype SET 
          M_CompanyTypeName = ?,
          M_CompanyTypeUserID = ?,
          M_CompanyTypeLastUpdated = now()
              where
              M_CompanyTypeID = ?
              ";
          $querycompany = $this->db_onedev->query($sqlcompany,
              array(
                  $name_company,
                  $userid,
                  $id_company
              )
          );
        //  echo $query;
          if (!$querycompany) {
              $this->sys_error_db("m_companytype update");
              exit;
          } 
          $rows = [];
          $query ="	SELECT *, COUNT(M_CompanyID) as used
FROM (SELECT m_companytype.*,M_CompanyID
FROM
m_companytype
LEFT JOIN m_company ON M_CompanyTypeID = M_CompanyM_CompanyTypeID AND M_CompanyIsActive = 'Y'
WHERE M_CompanyTypeIsActive = 'Y') a
GROUP BY M_CompanyTypeID";
                  //echo $query;
          $rows['companytypes'] = $this->db_onedev->query($query)->result_array();
          $result = array ("total" => 1, "records" => $rows);
          $this->sys_ok($result);    
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function deletecompanytype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        //# ambil parameter input
        $prm = $this->sys_input;
		$id_company = $prm['id'];
        $userid = $this->sys_user["M_UserID"];
          $sqlcompany = "update m_companytype SET 
          M_CompanyTypeIsActive = 'N',
          M_CompanyTypeUserID = ?,
          M_CompanyTypeLastUpdated = now()
              where
              M_CompanyTypeID = ?
              ";
          $querycompany = $this->db_onedev->query($sqlcompany,
              array(
                  $userid,
                  $id_company
              )
          );
        //  echo $query;
          if (!$querycompany) {
              $this->sys_error_db("m_companytype update");
              exit;
          } 
          $rows = [];
          $query ="	SELECT * 
                      FROM m_companytype 
                      WHERE	
                          M_CompanyTypeIsActive = 'Y'
                  ";
                  //echo $query;
          $rows['companytypes'] = $this->db_onedev->query($query)->result_array();
          $result = array ("total" => 1, "records" => $rows);
          $this->sys_ok($result);    
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function addnewcompanybusiness()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $name_companytype = $prm['name'];
        $userid = $this->sys_user["M_UserID"];
            $sql = "insert into m_companybusiness(
                M_CompanyBusinessName,
                M_CompanyBusinessUserID,
                M_CompanyBusinessCreated,
                M_CompanyBusinessLastUpdated
				) 
				values(?,?,now(),now())";
			$query = $this->db_onedev->query($sql,
				array(
                    $name_companytype,
                    $userid
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("m_companybusiness insert");
				exit;
			} 
			$rows = [];
            $query ="	SELECT *, COUNT(M_CompanyID) as used
FROM (SELECT m_companybusiness.*,M_CompanyID
FROM
m_companybusiness
LEFT JOIN m_company ON M_CompanyBusinessID = M_CompanyM_CompanyBusinessID AND M_CompanyIsActive = 'Y'
WHERE M_CompanyBusinessIsActive = 'Y') a
GROUP BY M_CompanyBusinessID
                    ";
                    //echo $query;
            $rows['companybusinesss'] = $this->db_onedev->query($query)->result_array();
			$result = array ("total" => 1, "records" => $rows);
            $this->sys_ok($result);
            $last_id = $this->db_onedev->insert_id();
        
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function editcompanybusiness()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        //# ambil parameter input
        $prm = $this->sys_input;
		$id_company = $prm['id'];
        $name_company = $prm['name'];
        $userid = $this->sys_user["M_UserID"];
          $sqlcompany = "update m_companybusiness SET 
          M_CompanyBusinessName = ?,
          M_CompanyBusinessUserID = ?,
          M_CompanyBusinessLastUpdated = now()
              where
              M_CompanyBusinessID = ?
              ";
          $querycompany = $this->db_onedev->query($sqlcompany,
              array(
                  $name_company,
                  $userid,
                  $id_company
              )
          );
        //  echo $query;
          if (!$querycompany) {
              $this->sys_error_db("m_companybusiness update");
              exit;
          } 
          $rows = [];
          $query ="	SELECT *, COUNT(M_CompanyID) as used
FROM (SELECT m_companybusiness.*,M_CompanyID
FROM
m_companybusiness
LEFT JOIN m_company ON M_CompanyBusinessID = M_CompanyM_CompanyBusinessID AND M_CompanyIsActive = 'Y'
WHERE M_CompanyBusinessIsActive = 'Y') a
GROUP BY M_CompanyBusinessID";
                  //echo $query;
          $rows['companybusinesss'] = $this->db_onedev->query($query)->result_array();
          $result = array ("total" => 1, "records" => $rows);
          $this->sys_ok($result);    
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function deletecompanybusiness()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        //# ambil parameter input
        $prm = $this->sys_input;
		$id_company = $prm['id'];
        $userid = $this->sys_user["M_UserID"];
          $sqlcompany = "update m_companybusiness SET 
          M_CompanyBusinessIsActive = 'N',
          M_CompanyBusinessUserID = ?,
          M_CompanyBusinessLastUpdated = now()
              where
              M_CompanyBusinessID = ?
              ";
          $querycompany = $this->db_onedev->query($sqlcompany,
              array(
                  $userid,
                  $id_company
              )
          );
        //  echo $query;
          if (!$querycompany) {
              $this->sys_error_db("m_companybusiness update");
              exit;
          } 
          $rows = [];
          $query ="	SELECT * 
                      FROM m_companybusiness 
                      WHERE	
                          M_CompanyBusinessIsActive = 'Y'
                  ";
                  //echo $query;
          $rows['companybusinesss'] = $this->db_onedev->query($query)->result_array();
          $result = array ("total" => 1, "records" => $rows);
          $this->sys_ok($result);    
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function addnewuser()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $prm = $this->sys_input;
         $companyid = $prm['companyid'];
         $username = $prm['username'];
         $userfullname = $prm['userfullname'];
         $usergrup = $prm['usergrup'];
         $password = $prm['password'];
         $md5_password = md5($this->one_salt . $prm["password"] . $this->one_salt);


         if($prm['xid'] == 0){
            $query = "SELECT COUNT(*) as exist FROM m_user WHERE M_UserIsActive = 'Y' AND M_UserUsername = '{$username}'";
            $exist_username =  $this->db_onedev->query($query)->row()->exist;

            if($exist_username == 0){
               $sql = "insert into m_user(
                M_UserM_UserGroupID,
                M_UserUsername,
                M_UserPassword,
                M_UserM_CompanyID,
                M_UserFullName,
                M_UserCreated,
                M_UserLastUpdated
             )
             values( ?,?,?,?,?,now(),now())";
$query = $this->db_onedev->query($sql,
array(
    $usergrup,
    $username,
    $md5_password,
    $companyid,
    $userfullname
)
);
if (!$query) {
$this->sys_error_db("m_user insert",$this->db_onedev);
exit;
}
$result = array ("total" => 1, "records" => array("xid" => 0));
$this->sys_ok($result);
            }else{
               $errors = array();
               if($exist_username != 0){
                  array_push($errors,array('field'=>'username','msg'=>'Nama User sudah ada yang pakai dong'));
               }


               $result = array ("total" => -1,"errors" => $errors, "records" => 0);
               $this->sys_ok($result);
            }
         }else{
            $query = "SELECT COUNT(*) as exist FROM m_user WHERE M_UserIsActive = 'Y' AND M_UserUsername = '{$username}' AND M_UserID <> {$prm['xid']}";
            $exist_username =  $this->db_onedev->query($query)->row()->exist;
            //echo $query;

            //echo $query;
            if($exist_username == 0 ){
                $sql = "UPDATE m_user SET M_UserUsername = '{$username}', M_UserFullName = '{$userfullname}'  WHERE M_UserID = '{$prm['xid']}'";
				//echo $sql;
               $query = $this->db_onedev->query($sql);
               $result = array ("total" => 1, "records" => array("xid" => 0));
               $this->sys_ok($result);
            }else{
               $errors = array();
               if($exist_username != 0){
                  array_push($errors,array('field'=>'username','msg'=>'Username sudah ada yang pakai dong'));
               }

               $result = array ("total" => -1,"errors" => $errors, "records" => 0);
               $this->sys_ok($result);
            }
         }



      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   
   public function deleteuser()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $prm = $this->sys_input;

         $sql = "update m_user SET
            M_UserIsActive = 'N',
            M_UserLastUpdated = now()
            WHERE
            M_UserID = ?

";

$query = $this->db_onedev->query($sql,
array(
   $prm['id']
)
);
// echo $query;
if (!$query) {
$this->sys_error_db("m_user delete");
exit;
}
$result = array ("total" => 1, "records" => array("xid" => 0));
$this->sys_ok($result);

         } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
         }
      }
   public function confirmmou()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
       
        $sql = "update m_mou SET 
        M_MouIsApproved = 'Y',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            } 
        
		
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function verifymou()
   {
    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"];
        $id = $prm['id'];
        $sql = "update m_mou SET 
        M_MouIsVerified = 'Y',
        M_MouVerifyDate = now(),
        M_MouStatus = 'V',
        M_MouVerifyUserID = '{$userid}',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            } 
        
            $querystatus ="INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'V',
            '{$userid}',
            now(),
            now())
        ";
        $rows = $this->db_onedev->query($querystatus);
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function unverifymou()
   {
    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"];
        $id = $prm['id'];
        $sql = "update m_mou SET 
        M_MouIsVerified = 'N',
        M_MouStatus = 'UV',
        M_MouVerifyDate = now(),
        M_MouVerifyUserID = '{$userid}',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            } 
            $querystatus ="INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'UV',
            '{$userid}',
            now(),
            now())
        ";
        $rows = $this->db_onedev->query($querystatus);
		
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function releasemou()
   {
    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"];
        $id = $prm['id'];
        $sql = "update m_mou SET 
        M_MouIsReleased = 'Y',
        M_MouStatus = 'R',
        M_MouReleaseDate = now(),
        M_MouReleaseUserID = '{$userid}',
        M_MouIsApproved = 'Y',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            } 
            $querystatus ="INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'R',
            '{$userid}',
            now(),
            now())
        ";
        $rows = $this->db_onedev->query($querystatus);
		
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function unreleasemou()
   {
    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"];
        $id = $prm['id'];
        $sql = "update m_mou SET 
        M_MouIsReleased = 'N',
        M_MouStatus = 'UR',
        M_MouReleaseDate = now(),
        M_MouReleaseUserID = '{$userid}',
        M_MouIsApproved = 'N',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            } 
            $querystatus ="INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'UR',
            '{$userid}',
            now(),
            now())
        ";
        $rows = $this->db_onedev->query($querystatus);
		
		
		
		
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function deletecompany()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
       
        $sql = "update m_company SET 
        M_CompanyIsActive = 'N',
        M_CompanyLastUpdated = now()
				WHERE
				M_CompanyID = ?
            
				";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("m_company delete");
                exit;
            } 

		$sql = "UPDATE m_mou SET 
				M_MouIsActive = 'N',
				M_MouLastUpdated = now()
				WHERE
				M_MouM_CompanyID = ?
			";
    
		$query = $this->db_onedev->query($sql,
			array(
				$prm['id']
			)
		);
	   // echo $query;
		if (!$query) {
			$this->sys_error_db("m_mou 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 selectcompanytype(){

    try {
        //# cek token valid
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $rows = [];
        $query ="	SELECT *, COUNT(M_CompanyID) as used
FROM (SELECT m_companytype.*,M_CompanyID
FROM
m_companytype
LEFT JOIN m_company ON M_CompanyTypeID = M_CompanyM_CompanyTypeID AND M_CompanyIsActive = 'Y'
WHERE M_CompanyTypeIsActive = 'Y') a
GROUP BY M_CompanyTypeID
                ";
                //echo $query;
        $rows['companytypes'] = $this->db_onedev->query($query)->result_array();
        
    
        $result = array(
            "total" => count($rows) , 
            "records" => $rows, 
        ); 
        $this->sys_ok($result);


    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
	
}
function selectcompanybusiness(){
    
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query ="	SELECT *, COUNT(M_CompanyID) as used
    FROM (SELECT m_companybusiness.*,M_CompanyID
    FROM
    m_companybusiness
    LEFT JOIN m_company ON M_CompanyBusinessID = M_CompanyM_CompanyBusinessID AND M_CompanyIsActive = 'Y'
    WHERE M_CompanyBusinessIsActive = 'Y') a
    GROUP BY M_CompanyBusinessID
                    ";
                    //echo $query;
            $rows['companybusinesss'] = $this->db_onedev->query($query)->result_array();
            
        
            $result = array(
                "total" => count($rows) , 
                "records" => $rows, 
            ); 
            $this->sys_ok($result);
    
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
        
    }
    function selectcompanylevel(){
        
            try {
                //# cek token valid
                if (! $this->isLogin) {
                    $this->sys_error("Invalid Token");
                    exit;
                }
                $prm = $this->sys_input;
                $id = $prm['id'];
                $rows = [];              
                $query ="	SELECT *,CONCAT(Nat_CompanyLevelName,' [',M_CompanyName,']') as Nat_CompanyLevelName, COUNT(M_CompanyID) as used
        FROM (SELECT nat_companylevel.*,M_CompanyID,M_CompanyName
        FROM
        nat_companylevel
        LEFT JOIN m_company ON Nat_CompanyLevelNat_CompanyID = M_CompanyID AND M_CompanyIsActive = 'Y'
        WHERE Nat_CompanyLevelIsActive = 'Y') a
        GROUP BY Nat_CompanyLevelID
                        ";
                    //echo $query;
                $rows['companylevels'] = $this->db_onedev->query($query)->result_array();
                
            
                $result = array(
                    "total" => count($rows) , 
                    "records" => $rows, 
                ); 
                $this->sys_ok($result);
        
        
            } catch(Exception $exc) {
                $message = $exc->getMessage();
                $this->sys_error($message);
            }
            
        }
        function selecthierarchy(){
            
                try {
                    //# cek token valid
                    if (! $this->isLogin) {
                        $this->sys_error("Invalid Token");
                        exit;
                    }
                    $rows = [];
                    $query ="	SELECT *, COUNT(M_CompanyID) as used
            FROM (SELECT nat_hierarchy.*,M_CompanyID
            FROM
            nat_hierarchy
            LEFT JOIN m_company ON Nat_HierarchyID = M_CompanyNat_HierarchyID AND M_CompanyIsActive = 'Y'
            WHERE Nat_HierarchyIsActive = 'Y') a
            GROUP BY Nat_HierarchyID
                            ";
                            //echo $query;
                    $rows['hierarchys'] = $this->db_onedev->query($query)->result_array();
                    
                
                    $result = array(
                        "total" => count($rows) , 
                        "records" => $rows, 
                    ); 
                    $this->sys_ok($result);
            
            
                } catch(Exception $exc) {
                    $message = $exc->getMessage();
                    $this->sys_error($message);
                }
                
            }
function selectdoctor(){
    
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query ="	SELECT * 
                        FROM m_doctor 
                        WHERE	
                            M_DoctorIsActive = 'Y'
                    ";
                    //echo $query;
            $rows['doctors'] = $this->db_onedev->query($query)->result_array();
            
        
            $result = array(
                "total" => count($rows) , 
                "records" => $rows, 
            ); 
            $this->sys_ok($result);
    
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
        
    }
    function searchdoctor(){
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
    
        $max_rst = 12;
        $tot_count =0;
    
        $q = [
            'search'     => '%'
        ];
    
        if ($prm['search'] != '')
        {
            $q['search'] = "%{$prm['search']}%";  
        }
    
          // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM m_doctor
                WHERE 
                M_DoctorName like ?
                AND M_DoctorIsActive = 'Y'";
        $query = $this->db_onedev->query($sql,$q['search']);
        //echo $query;
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("m_doctor count",$this->db_onedev);
            exit;
        }
    
        $sql = "
        SELECT * FROM(SELECT *, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorNames
        FROM m_doctor
        WHERE M_DoctorIsActive = 'Y') a
            WHERE 
            M_DoctorNames like ?
            AND M_DoctorIsActive = 'Y'
            ORDER BY M_DoctorName DESC
          ";
          $query = $this->db_onedev->query($sql, array($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("m_doctor rows",$this->db_onedev);
             exit;
          }
    }
    function searchcompanylevel(){
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
    
        $max_rst = 12;
        $tot_count =0;
    
        
        $name = $prm['name'];
        $hirarkiid = intval($prm['id']) - 1;
    
          // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM nat_companylevel
                WHERE 
                Nat_CompanyLevelName like '%{$name}%'
                AND
                Nat_CompanyLevelNat_HierarchyID = '{$hirarkiid}'
                AND Nat_CompanyLevelIsActive = 'Y'";
        $query = $this->db_onedev->query($sql);
        //echo $query;
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("nat_companylevel count",$this->db_onedev);
            exit;
        }
    
        $sql = "
        SELECT * FROM(SELECT *, CONCAT(Nat_CompanyLevelName, ' [',M_CompanyName,']') as Nat_CompanyLevelNames
        FROM nat_companylevel
        LEFT JOIN m_company ON Nat_CompanyLevelNat_CompanyID = M_CompanyID
        WHERE Nat_CompanyLevelIsActive = 'Y') a
            WHERE 
            Nat_CompanyLevelName like '%{$name}%'
            AND
            Nat_CompanyLevelNat_HierarchyID = '{$hirarkiid}'
            AND Nat_CompanyLevelIsActive = 'Y'
            ORDER BY Nat_CompanyLevelName DESC
          ";
          $query = $this->db_onedev->query($sql);
            
          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("nat_companylevel rows",$this->db_onedev);
             exit;
          }
    }
function searchcity(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_city
            WHERE 
            M_CityName like ?
            AND M_CityIsActive = 'Y'";
    $query = $this->db_onedev->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("m_city count",$this->db_onedev);
        exit;
    }

    $sql = "
        SELECT *
        FROM m_city
        WHERE 
        M_CityName like ?
        AND M_CityIsActive = 'Y'
        ORDER BY M_CityName DESC
      ";
      $query = $this->db_onedev->query($sql, array($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("m_city rows",$this->db_onedev);
         exit;
      }
}
function getstaff(){
    $prm = $this->sys_input;
    $query ="	SELECT * 
                FROM m_staff
                WHERE	
                    M_StaffIsActive = 'Y' AND M_StaffM_PositionID = 2";
            //echo $query;
    $rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
    
    $result = array(
        "total" => count($rows) , 
        "records" => $rows, 
    ); 
    $this->sys_ok($result);
    exit;
}
function getprovince(){
    $prm = $this->sys_input;
    $query ="	SELECT * 
                FROM m_province
                WHERE	
                    M_ProvinceIsActive = 'Y'";
            //echo $query;
    $rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
    
    $result = array(
        "total" => count($rows) , 
        "records" => $rows, 
    ); 
    $this->sys_ok($result);
    exit;
}
function getcity(){
    $prm = $this->sys_input;
    $query ="	SELECT * 
                FROM m_city
                WHERE	
                    M_CityIsActive = 'Y' AND M_CityM_ProvinceID = ?
            ";
            //echo $query;
    $rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
    
    $result = array(
        "total" => count($rows) , 
        "records" => $rows, 
    ); 
    $this->sys_ok($result);
    exit;
}
function getdistrict(){
    $prm = $this->sys_input;
    $query ="	SELECT * 
                FROM m_district 
                WHERE	
                    M_DistrictIsActive = 'Y' AND M_DistrictM_CityID = ?
            ";
            //echo $query;
    $rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
    
    $result = array(
        "total" => count($rows) , 
        "records" => $rows, 
    ); 
    $this->sys_ok($result);
    exit;
}

function getkelurahan(){
    $prm = $this->sys_input;
    $query ="	SELECT * 
                FROM m_kelurahan 
                WHERE	
                    M_KelurahanIsActive = 'Y' AND M_KelurahanM_DistrictID = ?
            ";
            //echo $query;
    $rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
    
    $result = array(
        "total" => count($rows) , 
        "records" => $rows, 
    ); 
    $this->sys_ok($result);
    exit;
}
function selectbase(){
    
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query ="	SELECT 'SPK' as baseid, 'SPK' as basename
            UNION SELECT 'MOU' as baseid, 'MOU' as basename
                    ";
                    //echo $query;
            $rows['bases'] = $this->db_onedev->query($query)->result_array();
            
        
            $result = array(
                "total" => count($rows) , 
                "records" => $rows, 
            ); 
            $this->sys_ok($result);
    
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
        
    }
    function selectomzettype(){
        
            try {
                //# cek token valid
                if (! $this->isLogin) {
                    $this->sys_error("Invalid Token");
                    exit;
                }
                $rows = [];
                $query ="	SELECT * 
                            FROM m_omzettype 
                            WHERE	
                            M_OmzetTypeIsActive = 'Y'
                        ";
                        //echo $query;
                $rows['omzettypes'] = $this->db_onedev->query($query)->result_array();
                
            
                $result = array(
                    "total" => count($rows) , 
                    "records" => $rows, 
                ); 
                $this->sys_ok($result);
        
        
            } catch(Exception $exc) {
                $message = $exc->getMessage();
                $this->sys_error($message);
            }
            
        }
        function selectmoutype(){
            
                try {
                    //# cek token valid
                    if (! $this->isLogin) {
                        $this->sys_error("Invalid Token");
                        exit;
                    }
                    $rows = [];
                    $query ="	SELECT * 
                                FROM m_moutype 
                                WHERE	
                                M_MouTypeIsActive = 'Y'
                            ";
                            //echo $query;
                    $rows['moutypes'] = $this->db_onedev->query($query)->result_array();
                    
                
                    $result = array(
                        "total" => count($rows) , 
                        "records" => $rows, 
                    ); 
                    $this->sys_ok($result);
            
            
                } catch(Exception $exc) {
                    $message = $exc->getMessage();
                    $this->sys_error($message);
                }
                
            }
            function selectagingtype(){
                
                    try {
                        //# cek token valid
                        if (! $this->isLogin) {
                            $this->sys_error("Invalid Token");
                            exit;
                        }
                        $rows = [];
                        $query ="	SELECT * 
                                    FROM m_agingtype 
                                    WHERE	
                                    M_AgingIsActive = 'Y'
                                ";
                                //echo $query;
                        $rows['agingtypes'] = $this->db_onedev->query($query)->result_array();
                        
                    
                        $result = array(
                            "total" => count($rows) , 
                            "records" => $rows, 
                        ); 
                        $this->sys_ok($result);
                
                
                    } catch(Exception $exc) {
                        $message = $exc->getMessage();
                        $this->sys_error($message);
                    }
                    
                }
   
}