<?php

	class SAP_infosytem {

	    private $db = null;
	    private $_supportFormats = ['image/png','image/jpeg','image/jpg','image/gif'];

  //======================== login admin/staff, zone leader, members  ======================//

        public function Login_user($username, $password, $role) {
              global $db;
              session_start();

               $stmt1 = $db->prepare("SELECT * FROM tbl_admin WHERE username = :uname AND password= :upass  AND role = :stat");
               $stmt1->execute(array(':uname' => $username, ':upass' => $password, ':stat' => 'Administrator'));
               $row1=$stmt1->fetch(PDO::FETCH_ASSOC);

               $stmt2 = $db->prepare("SELECT * FROM tbl_zoneleader WHERE username = :uname AND password= :upass  AND role = :stat");
               $stmt2->execute(array(':uname' => $username, ':upass' => $password, ':stat' => 'Zone Leader'));
               $row2=$stmt2->fetch(PDO::FETCH_ASSOC);

               $stmt3 = $db->prepare("SELECT * FROM tbl_beneficiaries WHERE username = :uname AND password= :upass  AND role = :stat");
               $stmt3->execute(array(':uname' => $username, ':upass' => $password, ':stat' => 'Member'));
               $row3=$stmt3->fetch(PDO::FETCH_ASSOC);

                if ($stmt1->rowCount() > 0){
                 
                    $_SESSION['user_no']   = htmlentities($row1['username']);
                    $_SESSION['logged_in'] = true;

                    echo '1';
                  
                      exit();
                }elseif($stmt2->rowCount() > 0){
                 
                    $_SESSION['user_no']   = htmlentities($row2['username']);
                    $_SESSION['logged_in'] = true;

                    echo '2';
                  
                      exit();
                } elseif($stmt3->rowCount() > 0){
                 
                    $_SESSION['user_no']   = htmlentities($row3['beneficiaries_id']);
                    $_SESSION['logged_in'] = true;

                    echo '3';
                  
                      exit();
                }else{
                    //echo '0';
                    ?>
                        <span><i class="fa fa-user"></i> Login Failed. User not Found.</span>
                    <?php 
                }
            }

  //====================end login admin/staff, zone leader, members  ======================//


// ==============================Change  Password(Admin)========================================//

       public function fetch_changepassword($user_session) {
           global $db;

          $query = $db->prepare("SELECT * FROM tbl_admin WHERE username = ?");
          $query->execute(array($user_session));
          return $query->fetchAll();
      }

// ==============================end Change  Passwor(Admin)=====================================//


// ==============================Change  Password(Zone Leader)========================================//

       public function fetch_changepassword2($user_session) {
           global $db;

          $query = $db->prepare("SELECT * FROM tbl_zoneleader WHERE username = ?");
          $query->execute(array($user_session));
          return $query->fetchAll();
      }

// ==============================end Change  Password (ZOne Leaader)=====================================//


// ==============================Change  Password(Zone Leader)========================================//

       public function fetch_changepassword3($user_session) {
           global $db;

          $query = $db->prepare("SELECT * FROM tbl_beneficiaries WHERE beneficiaries_id = ?");
          $query->execute(array($user_session));
          return $query->fetchAll();
      }

// ==============================end Change  Password (ZOne Leaader)=====================================//


// ==============================count beneficiaries========================================//

        public function count_beneficiaries() {
                global $db;

                $query = $db->prepare("SELECT COUNT(beneficiaries_id) as beneficiaries_id FROM tbl_beneficiaries");
                $query->execute(array());
                return $query->fetchAll();
}

// ==============================end count beneficiaries=====================================//


// ==============================count age-senior beneficiaries==================================//

        public function count_beneficiariessesenior() {
                global $db;

                $query = $db->prepare("SELECT COUNT(age) as senior FROM tbl_beneficiaries WHERE age >=50");
                $query->execute(array());
                return $query->fetchAll();
}
// ==============================end count age-senior beneficiaries==============================//

// ==============================count female beneficiaries ==================================//

        public function count_beneficiariesfemale() {
                global $db;

                $query = $db->prepare("SELECT COUNT(gender) as fmale  FROM tbl_beneficiaries WHERE gender = 'Female'");
                $query->execute(array());
                return $query->fetchAll();
}
// ==============================end count female  beneficiaries==============================//


// ==============================count  male beneficiaries ==================================//

        public function count_beneficiariesmale() {
                global $db;

                $query = $db->prepare("SELECT COUNT(gender) as male  FROM tbl_beneficiaries WHERE gender = 'Male'");
                $query->execute(array());
                return $query->fetchAll();
}
// ==============================end count  male  beneficiaries==============================//


// ==============================count household============================================//

        public function count_household() {
                global $db;

                $query = $db->prepare("SELECT COUNT(household_id) as household_id FROM  tbl_household");
                $query->execute(array());
                return $query->fetchAll();
}
// ==============================end count household=====================================//



// ==============================count SAPForm============================================//

        public function count_sapform() {
                global $db;

                $query = $db->prepare("SELECT COUNT(sapform_id) as stat_approved FROM  tbl_sapform WHERE status = 'Approve'");
                $query->execute(array());
                return $query->fetchAll();
}
// ==============================end count SAPForm=====================================//



// ==============================fetch household========================================//

	    public function fetch_household() {
	        global $db;
	        $query = $db->prepare("SELECT *, count(purok_no) as purokno FROM tbl_household GROUP BY purok_no");
	        $query->execute(array());
	        return $query->fetchAll();
	    }
// ==============================end fetch household====================================//


// ==============================add  household=========================================//

	    public function add_household($household_no, $purok_no, $total_membersofthefamily, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_household(`household_no`, `purok_no`, `total_membersofthefamily`) VALUES(?,?,?)");
	        $true = $stmt->execute([$household_no, $purok_no, $total_membersofthefamily]);
	        if ($true == true) {
	            return true;
	        } else {
	            return false;
	        }
	    }
// ==============================end add  household======================================//

// ================================ edit  household======================================//

        public function edit_household($household_no, $purok_no, $total_membersofthefamily, $household_id, $user, $ip, $host,  $logdate, $action){
            global $db;


          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_household` SET  `household_no` = ?, `purok_no` = ?, `total_membersofthefamily` = ?  WHERE household_id=?";
            $update = $db->prepare($sql)->execute([$household_no, $purok_no, $total_membersofthefamily, $household_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit  household======================================//


// ==============================add  Beneficiaries=========================================//

	    public function add_beneficiaries($lastname, $firstname, $middlename, $age, $birthdate, $gender, $household_number, $birthplace, $differently_abledperson, $gcash_number, $blood_type, $purok_no, $civil_status, $total_householdmember, $religion, $occupation, $contact_number, $monthly_income,  $educational_attainment, $nationality, $philhealth_number, $skills, $water_usage, $land_ownershipstatus, $sektor, $benificiaries_of, $light_facilities, $remark, $former_address, $username, $password, $role, $images, $user, $ip, $host,  $logdate, $action){
           global $db;


           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_beneficiaries(`lastname`, `firstname`, `middlename`, `age`, `birthdate`, `gender`, `household_number`, `birthplace`, `differently_abledperson`, `gcash_number`, `blood_type`, `purok_no`, `civil_status`, `total_householdmember`, `religion`, `occupation`, `contact_number`, `monthly_income`,  `educational_attainment`, `nationality`, `philhealth_number`, `skills`, `water_usage`, `land_ownershipstatus`, `sektor`, `benificiaries_of`, `light_facilities`, `remark`, `former_address`, `username`, `password`, `role`, `image_profile`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
	        $true = $stmt->execute([$lastname, $firstname, $middlename, $age, $birthdate, $gender, $household_number, $birthplace, $differently_abledperson, $gcash_number, $blood_type, $purok_no, $civil_status, $total_householdmember, $religion, $occupation, $contact_number, $monthly_income,  $educational_attainment, $nationality, $philhealth_number, $skills, $water_usage, $land_ownershipstatus, $sektor, $benificiaries_of, $light_facilities, $remark, $former_address, $username, $password, $role, $images]);
	        if ($true == true) {
	            return true;
	        } else {
	            return false;
	        }
	    }
// ==============================end add  Beneficiaries======================================//


// ==============================   edit  Beneficiaries======================================//

	    public function edit_beneficiaries($lastname, $firstname, $middlename, $age, $birthdate, $gender, $household_number, $birthplace, $differently_abledperson, $gcash_number, $blood_type, $purok_no, $civil_status, $total_householdmember, $religion, $occupation, $contact_number, $monthly_income,  $educational_attainment, $nationality, $philhealth_number, $skills, $water_usage, $land_ownershipstatus, $sektor, $benificiaries_of, $light_facilities, $remark, $former_address, $username, $password, $beneficiaries_id, $user, $ip, $host,  $logdate, $action){
         
           	global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);


	        $sql = "UPDATE `tbl_beneficiaries` SET  `lastname` = ?, `firstname` = ?, `middlename` = ?, `age` = ?, `birthdate` = ?, `gender` = ?, `household_number` = ?, `birthplace` = ?, `differently_abledperson` = ?, `gcash_number` = ?, `blood_type` = ?, `purok_no` = ?, `civil_status` = ?, `total_householdmember` = ?, `religion` = ?, `occupation` = ?, `contact_number` = ?, `monthly_income` = ?,  `educational_attainment` = ?, `nationality` = ?, `philhealth_number` = ?, `skills` = ?, `water_usage` = ?, `land_ownershipstatus` = ?, `sektor` = ?, `benificiaries_of` = ?, `light_facilities` = ?, `remark` = ?, `former_address` = ?, `username` = ?, `password` = ?  WHERE beneficiaries_id = ?";
	        $update = $db->prepare($sql)->execute([$lastname, $firstname, $middlename, $age, $birthdate, $gender, $household_number, $birthplace, $differently_abledperson, $gcash_number, $blood_type, $purok_no, $civil_status, $total_householdmember, $religion, $occupation, $contact_number, $monthly_income,  $educational_attainment, $nationality, $philhealth_number, $skills, $water_usage, $land_ownershipstatus, $sektor, $benificiaries_of, $light_facilities, $remark, $former_address, $username, $password, $beneficiaries_id]);
	        //$updated_id = $db->lastInsertId();

	        if ($update == true) {
	            return true;
	        } else {
	            return false;
	       } 

        }


// ==============================end edit  Beneficiaries===================================//


// ==============================fetch Beneficiaries========================================//

	    public function fetch_beneficiaries() {
	        global $db;
	        $query = $db->prepare("SELECT *, CONCAT(lastname, ', ', firstname, ' ', middlename) as full_name FROM tbl_beneficiaries ORDER BY beneficiaries_id DESC");
	        $query->execute(array());
	        return $query->fetchAll();
	    }
// ==============================end fetch Beneficiaries====================================//


 // ================================ edit  Beneficiaries Image================================//

        public function edit_beneficiariesimage($images, $beneficiaries_id){
            global $db;

            $sql = "UPDATE `tbl_beneficiaries` SET  `image_profile` = ?  WHERE beneficiaries_id=?";
            $update = $db->prepare($sql)->execute([$images, $beneficiaries_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit  Beneficiaries Image===============================//

 // ==============================add  SAP Form==============================================//

	    public function add_sapform($sapform_no, $beneficiaries_id, $purpose, $financial_serviceprovider, $status, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_sapform(`sapform_no`, `beneficiaries_id`, `purpose`, `financial_serviceprovider`,`status`) VALUES(?,?,?,?,?)");
	        $true = $stmt->execute([$sapform_no, $beneficiaries_id, $purpose, $financial_serviceprovider, $status]);
	        if ($true == true) {
	            return true;
	        } else {
	            return false;
	        }
	    }
// ==============================end add  SAP Form======================================//


// ==============================fetch sapform(Approve) Zone leader============================================//

	    public function fetch_sapform() {
	        global $db;
	        $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id= b.beneficiaries_id WHERE a.status = 'Approve'");
	        $query->execute(array());
	        return $query->fetchAll();
	    }


// ==============================end fetch sapform(Approve) Zone leader====================================//

 // ==============================fetch sapform(Disapprove) Zone leader======================================//

      public function fetch_sapform1() {
          global $db;
          $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id= b.beneficiaries_id WHERE a.status = 'Disapprove'");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end fetch sapform(Disapprove) Zone leader====================================//

 // ==============================fetch sapform (Approve) ======================================//

      public function fetch_sapform2() {
          global $db;
          $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id= b.beneficiaries_id AND a.status = 'New'");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end fetch sapform (Approve) =============================//

    // ==============================fetch sapform (Request New) User======================================//

      public function fetch_sapform3($user_session) {
          global $db;
          $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id = b.beneficiaries_id AND a.status = 'New' WHERE b.beneficiaries_id = ?");
          $query->execute([$user_session]);
          return $query->fetchAll();
      }
// ==============================end fetch sapform (Request New) User====================================//

 // ==============================fetch sapform (Approve) User======================================//

      public function fetch_sapform4($user_session) {
          global $db;
          $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id = b.beneficiaries_id AND a.status = 'Approve' WHERE b.beneficiaries_id = ?");
          $query->execute([$user_session]);
          return $query->fetchAll();
      }
// ==============================end fetch sapform (Approve) User====================================//

 // ==============================fetch sapform (Dispprove) User======================================//

      public function fetch_sapform5($user_session) {
          global $db;
          $query = $db->prepare("SELECT *,CONCAT(b.lastname, ', ' ,b.firstname, ' ' ,b.middlename) as beneficiary_name FROM tbl_sapform a INNER JOIN tbl_beneficiaries b ON a.beneficiaries_id = b.beneficiaries_id AND a.status = 'Disapprove' WHERE b.beneficiaries_id = ?");
          $query->execute([$user_session]);
          return $query->fetchAll();
      }
// ==============================end fetch sapform (Dispprove) User====================================//


 // ================================ edit  sapform ====================================//

        public function edit_sapform($sapform_no, $beneficiaries_id, $purpose, $financial_serviceprovider, $sapform_id, $user, $ip, $host,  $logdate, $action){
            global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_sapform` SET  `sapform_no` = ?, `beneficiaries_id` = ?, `purpose` = ?, `financial_serviceprovider` = ?  WHERE beneficiaries_id=?";
            $update = $db->prepare($sql)->execute([$sapform_no, $beneficiaries_id, $purpose, $financial_serviceprovider, $sapform_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit  sapform======================================//


 // ============================== donut chart==========================================//

        public function fetch_donutchart1(){
            global $db;

             $q = $db->query("SELECT *,count(*) as UCT  from tbl_beneficiaries where benificiaries_of = 'UCT'")->fetchAll();
             $donut = '';
             foreach ($q as $row) {
              $donut = htmlentities($row['UCT']);
                 return $donut;
            }
       }

       public function fetch_donutchart2(){
            global $db;  

             $q = $db->query("SELECT *,count(*) as 4PS  from tbl_beneficiaries where benificiaries_of = '4PS'")->fetchAll();
             $donut = '';
             foreach ($q as $row) {
              $donut = htmlentities($row['4PS']);
                 return $donut;
            }
       }

    public function fetch_donutchart3(){
            global $db;  

             $q = $db->query("SELECT *,count(*) as UCT_4PS  from tbl_beneficiaries where benificiaries_of = 'UCT&4PS'")->fetchAll();
             $donut = '';
             foreach ($q as $row) {
              $donut = htmlentities($row['UCT_4PS']);
                 return $donut;
            }
       }

    public function fetch_donutchart4(){
            global $db;  

             $q = $db->query("SELECT *,count(*) as Katutubo  from tbl_beneficiaries where benificiaries_of = 'Katutubo'")->fetchAll();
             $donut = '';
             foreach ($q as $row) {
              $donut = htmlentities($row['Katutubo']);
                 return $donut;
            }
       }


    public function fetch_donutchart5(){
            global $db;   

             $q = $db->query("SELECT *,count(*) as Prefer_nottostay  from tbl_beneficiaries where benificiaries_of = 'Prefer not to stay'")->fetchAll();
             $donut = '';
             foreach ($q as $row) {
              $donut = htmlentities($row['Prefer_nottostay']);
                 return $donut;
            }
       }


  
// ==============================end donut chart========================================//


 // ==============================fetch Export word Beneficiaries========================================//

	         public function fetc_beneficiariesword() {
          global $db;
          $query = $db->prepare("SELECT *, CONCAT(lastname, ', ', firstname, ' ', middlename) as full_name FROM tbl_beneficiaries");
          $query->execute(array());
          return $query->fetchAll();
      }


      public function fetc_beneficiariesword1() {
          global $db;
          $query = $db->prepare("SELECT household_id,count(purok_no) as group_purok, purok_no FROM  tbl_household GROUP BY purok_no");
          $query->execute(array());
          return $query->fetchAll();
      }

      public function fetc_beneficiariesword2() {
          global $db;
          $query = $db->prepare("SELECT*, count(*) as cnt,round(monthly_income,-1) as income, CONCAT(lastname, ', ', firstname, ' ', middlename) as full_name FROM tbl_beneficiaries group by monthly_income ORDER BY beneficiaries_id ASC");
          $query->execute(array());
          return $query->fetchAll();
      }


     public function fetc_beneficiariesword3() {
          global $db;
          $query = $db->prepare("SELECT *, educational_attainment, CONCAT(lastname, ', ', firstname, ' ', middlename) as full_name FROM tbl_beneficiaries");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end  fetch Export word Beneficiaries================================//

// ==============================fetch contact============================================//

	    public function fetch_contact() {
	        global $db;
	        $query = $db->prepare("SELECT *,CONCAT(last_name, ', ' ,first_name) as full_name FROM tbl_contact");
	        $query->execute(array());
	        return $query->fetchAll();
	    }
// ==============================end fetch contact====================================//


// ==============================fetch Audittrail========================================//

        public function fetch_audittrail() {
                global $db;

                $query = $db->prepare("SELECT * FROM tbl_audittrail ORDER BY audittrail_id DESC");
                $query->execute(array());
                return $query->fetchAll();
}

// ==============================end fetch Audittrail   =====================================//


 // ================================ edit  Disapprove Sapform ====================================//

        public function disapprove_sapform($purpose,  $status, $sapform_id, $recordedBy, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$recordedBy, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_sapform` SET  `purpose` = ?, `status` = ?  WHERE sapform_id=?";
            $update = $db->prepare($sql)->execute([$purpose, $status, $sapform_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit  Disapprove Sapform======================================//

// ================================ edit  Approve Sapform ====================================//

        public function approve_sapform($sapform_no, $purpose, $financial_serviceprovider,  $status, $recordedBy, $sapform_id, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$recordedBy, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_sapform` SET  `sapform_no` = ?, `purpose` = ?, `financial_serviceprovider` = ?, `status` = ?, `recordedBy` = ?  WHERE sapform_id=?";
            $update = $db->prepare($sql)->execute([$sapform_no, $purpose, $financial_serviceprovider,  $status, $recordedBy, $sapform_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit  Approve Sapform ======================================//

// ==============================Change GET ID Members========================================//

       public function fetch_beneficiariesMember($user_session) {
           global $db;

          $query = $db->prepare("SELECT * FROM tbl_beneficiaries WHERE beneficiaries_id = ?");
          $query->execute(array($user_session));
          return $query->fetchAll();
      }

// ==============================End Change GET ID Members========================================//

// ==============================Request SAP Form (Benefiacries Member)=====================================//


      public function request_sapform($purpose, $beneficiaries_id, $status, $full_name, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$full_name, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO  tbl_sapform(`purpose`,`beneficiaries_id`,`status`) VALUES(?,?,?)");
          $true = $stmt->execute([$purpose, $beneficiaries_id, $status]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add Request SAP Form (Benefiacries Member)=====================================//

          //================= delete  SAP Form(Admin)=================//

        public function delete_sapform($sapform_id, $user, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "DELETE FROM `tbl_sapform` WHERE sapform_id = ?";
            $delete = $db->prepare($sql)->execute([$sapform_id]);
            if ($delete == true) {
                return true;
            } else {
                return false;
            }
        }

     //=================end delete SAP Form(Admin)=================/

       // ==============================add  contact user==============================================//

      public function add_contact2($first_name, $last_name, $email, $contact_number, $message, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_contact(`first_name`, `last_name`, `email`, `contact_number`, `message`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$first_name, $last_name, $email, $contact_number, $message]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add  contact use===================================================//

            // ==============================add  contact use(outside)==============================================//

      public function add_contact($first_name, $last_name, $email, $contact_number, $message){
           global $db;

           $stmt = $db->prepare("INSERT INTO tbl_contact(`first_name`, `last_name`, `email`, `contact_number`, `message`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$first_name, $last_name, $email, $contact_number, $message]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add  contact use(outside)===================================================//


   // ==============================fetch officials========================================//

      public function fetch_officials() {
          global $db;
          $query = $db->prepare("SELECT * FROM tbl_official ORDER BY official_id DESC");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end fetch officials======================================//

 // ==============================add  officials==============================================//

      public function add_official($full_name, $position, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_official(`full_name`, `position`) VALUES(?,?)");
          $true = $stmt->execute([$full_name, $position]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add  officials==========================================//



 // ================================ edit  officials ====================================//

        public function edit_official($full_name, $position, $official_id, $user, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_official` SET  `full_name` = ?, `position` = ?  WHERE official_id=?";
            $update = $db->prepare($sql)->execute([$full_name, $position, $official_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit officials======================================//



   // ==============================fetch Admin========================================//

      public function fetch_admin() {
          global $db;
          $query = $db->prepare("SELECT * FROM tbl_admin ORDER BY user_id DESC");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end fetch Admin======================================//


 // ==============================add  Admin==============================================//

      public function add_admin($username, $password, $role, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_admin(`username`, `password`, `role`) VALUES(?,?,?)");
          $true = $stmt->execute([$username, $password, $role]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add  Admin==========================================//



 // ================================ edit  Admin ====================================//

        public function edit_admin($username, $password, $user_id, $user, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_admin` SET  `username` = ?, `password` = ?  WHERE user_id=?";
            $update = $db->prepare($sql)->execute([$username, $password, $user_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit Admin======================================//

 // ==============================fetch Zone Leader========================================//

      public function fetch_zoneleader() {
          global $db;
          $query = $db->prepare("SELECT * FROM tbl_zoneleader ORDER BY user_id DESC");
          $query->execute(array());
          return $query->fetchAll();
      }
// ==============================end fetch Zone Leader======================================//

// ==============================add  Zone Leader==============================================//

      public function add_zoneleader($username, $password, $role, $user, $ip, $host,  $logdate, $action){
           global $db;

          $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
          $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

           $stmt = $db->prepare("INSERT INTO tbl_zoneleader(`username`, `password`, `role`) VALUES(?,?,?)");
          $true = $stmt->execute([$username, $password, $role]);
          if ($true == true) {
              return true;
          } else {
              return false;
          }
      }
// ==============================end add  Zone Leader==========================================//

// ================================ edit  Admin ====================================//

        public function edit_zoneleader($username, $password, $user_id, $role, $user, $ip, $host,  $logdate, $action){
            global $db;

           $stmt = $db->prepare("INSERT INTO  tbl_audittrail(`user_name`, `user_ip`, `user_host`, `logdate`, `action`) VALUES(?,?,?,?,?)");
           $true = $stmt->execute([$user, $ip, $host,  $logdate, $action]);

            $sql = "UPDATE `tbl_zoneleader` SET  `username` = ?, `password` = ?  WHERE user_id=?";
            $update = $db->prepare($sql)->execute([$username, $password, $user_id]);
            if ($update == true) {
                return true;
            } else {
                return false;
           }

       }
// ==============================end edit Admin======================================//

  }
?>
