<?php
include ("sendSms.php");


//unasigened();
$GLOBALS ['errors'] = array();
function registercustomer(){
    $db = getConnection();
     //receive all input values from the form
     //$memberNo = 1;
     $ticketNo = generateNo();
     
     $_SESSION['ticketNo'] = $ticketNo;
     //$pin = mysqli_real_escape_string($db, $_POST['pin']);
     $cusname = mysqli_real_escape_string($db, $_POST['cusname']);;
     $pin = mysqli_real_escape_string($db, $_POST['pin']);
     $idNo = mysqli_real_escape_string($db, $_POST['idNo']);
     $phoneNumber = mysqli_real_escape_string($db, $_POST['phoneNumber']);
     $businessName = mysqli_real_escape_string($db, $_POST['businessName']);
     $businessAddress = mysqli_real_escape_string($db, $_POST['businessAddress']);
     $county = mysqli_real_escape_string($db, $_POST['county']);
     $email = mysqli_real_escape_string($db, $_POST['email']);
    
     
     //form validation: ensure that the form is correctly filled ...
     //by adding (array_push()) corresponding error unto $GLOBALS ['errors'] array
     if (empty($idNo)) { array_push($GLOBALS ['errors'], "ID  is required"); }
     if (empty($phoneNumber)) { array_push($GLOBALS ['errors'], "Mobile Phone Number  is required"); }
     if (empty($cusname)) { array_push($GLOBALS ['errors'], "Customer name is required"); }
     if (empty($pin)) { array_push($GLOBALS ['errors'], "KRA pin  is required"); }
     if (empty($businessAddress)) { array_push($GLOBALS ['errors'], "Mobile Phone Number  is required"); }
     if (empty($businessName)) { array_push($GLOBALS ['errors'], "Business name is required"); }
     if (empty($county)) { array_push($GLOBALS ['errors'], "County name is required"); }
     if (empty($email)) { array_push($GLOBALS ['errors'], "Email Address is required"); }
     
     
    //  first check the database to make sure 
    //  a user does not already exist with the same username and/or email
     $user_check_query = "SELECT * FROM customers WHERE idNumber='$idNo' or `pin` = '$pin' LIMIT 1";
     $result = mysqli_query($db, $user_check_query);
     $user = mysqli_fetch_assoc($result);
 
     if ($user) { // if user exists
         if ($user['idNumber'] === $idNo) {
             array_push($GLOBALS ['errors'], "Customer  already exists");
         }
 
         if ($user['pin'] === $pin) {
             array_push($GLOBALS ['errors'], "KRA Pin Number already exists");
         }
     }
 
     // Finally, register user if there are no errors in the form
     if (count($GLOBALS ['errors']) == 0) {
         $ip = getip();
         $username = $_SESSION['username'];
         $msg="Dear ".$cusname.",Thank you for believing in us. We appreciate you joining us. We aspire to serve you best. Thank you";
        $t = time();
        //echo "hey hey";
         $d = date("Y-m-d G:i:s",$t);
         $query = "INSERT INTO `customers`(`dateCreated`, `idNumber`, `pin`, `phoneNumber`, `cusName`, `businessName`, `businessAddress`, `county`, `email`, `createdBy`) 
         VALUES ('$d','$idNo','$pin','$phoneNumber','$cusname','$businessName','$businessAddress','$county','$email','$username ')";
         mysqli_query($db, $query);

          //send sms
          sendSMSnew($phoneNumber, $msg);
          //save sms
          $insertSms = "INSERT INTO `sms`( `customer`, `phoneNumber`, `message`, `type`) 
          VALUES ('$cusname','$phoneNumber','$msg','New_Customer')";
          mysqli_query($db, $insertSms);

         $_SESSION['addition'] = "Customer added Successful ";
         $username = $_SESSION['username'];
         //$username = "admin";

          //
                $audit = "INSERT INTO audit_trail (username, time_stamp, `action`, results, impact, ip_address)
                    VALUES('$username','$d', 'add_customer', 'success', '$pin', '$ip')";
                    mysqli_query($db, $audit);

                //
        // header('location: /crm/main/index.php');

        //  if(mysqli_error($db)){
        //     echo mysqli_error($db);
        //}
     }
 }
// capture ticket data
 function regcomplain(){
    $db = getConnection();
    $ticketNo = generateNo();
    
    $_SESSION['ticketNo'] = $ticketNo;
    $cusName = mysqli_real_escape_string($db, $_POST['cusName']);
    $mobileNumber = mysqli_real_escape_string($db, $_POST['mobileNumber']);
    $businessName = mysqli_real_escape_string($db, $_POST['businessName']);
    $serialNumber = mysqli_real_escape_string($db, $_POST['serialNumber']);
    $source = mysqli_real_escape_string($db, $_POST['source']);
    $dStatus = mysqli_real_escape_string($db, $_POST['dStatus']);
    $priority = mysqli_real_escape_string($db, $_POST['priority']);
    $complain = mysqli_real_escape_string($db, $_POST['complain']);
    $status="OPEN";
    $assignedto=mysqli_real_escape_string($db, $_POST['assigned']);
    $user = $_SESSION['username'];
    $msg="Dear ".$cusName.", a ticket with ticket No. ".$ticketNo." has been created. We will inform your on the progres of the Ticket. Thank you";


    $ip = getip();
        $t = time();
        //echo "hey hey";
         $d = date("Y-m-d G:i",$t);

    $query = "INSERT INTO `insidence`( `dateCreated`, `ticketNumber`,
                                        `cusName`, `mobileNumber`, `businessName`, 
                                        `serialNumber`, `source`, `dStatus`, 
                                        `priority`, `complain`, `createdBy`,`status`,`AssignedTo`) 
         VALUES (
                                        '$d','$ticketNo','$cusName',
                                        '$mobileNumber','$businessName','$serialNumber',
                                        '$source', '$dStatus', '$priority', 
                                        '$complain', '$user', '$status', '$assignedto')";
         mysqli_query($db, $query);
         //send sms
            sendSMSnew($mobileNumber, $msg);
            //save sms
            $insertSms = "INSERT INTO `sms`( `customer`, `phoneNumber`, `message`, `type`) 
            VALUES ('$cusName','$mobileNumber','$msg','Ticket')";
            mysqli_query($db, $insertSms);
        
         $_SESSION['iaddition'] = "Ticket ".$ticketNo." added Successful ";

         //
         $audit = "INSERT INTO audit_trail (username, time_stamp, `action`, results, impact, ip_address)
         VALUES('$user','$d', 'add_ticket', 'success', '$ticketNo', '$ip')";
         mysqli_query($db, $audit);

     //

 }
 function regjobcard(){
    $db = getConnection();
    $jobcardNo = generateJobCardNo();
    $_SESSION['jobcardNo'] = $jobcardNo;
    $cusName = mysqli_real_escape_string($db, $_POST['cusName']);
    $mobileNumber = mysqli_real_escape_string($db, $_POST['mobileNumber']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $equipment = mysqli_real_escape_string($db, $_POST['equipment']);
    $charger = mysqli_real_escape_string($db, $_POST['charger']);
    $qty = mysqli_real_escape_string($db, $_POST['qty']);
    $modelseq = mysqli_real_escape_string($db, $_POST['modelseq']);
    $serialNumber = mysqli_real_escape_string($db, $_POST['serialNumber']);
    $fault=mysqli_real_escape_string($db, $_POST['fault']);
    $workdone=mysqli_real_escape_string($db, $_POST['workdone']);
    $technician=mysqli_real_escape_string($db, $_POST['technician']);
    $user = $_SESSION['username'];
    $status=1;
    $msg="Dear ".$cusName.", a Job Card with number ".$jobcardNo." has been created. We will keep you updated on the progres of the job card. Thank you";
    $ip = getip();
    $d = date("Y-m-d G:i");
    $query = " INSERT INTO `jobcards`(`dateCreated`, `jbcrdNum`, `customer`, `phoneNumber`, `serialNumber`, `email`, `devicename`, `charger`, `qty`, `model`, `fault`, `work`, `techn`, `createdBy`, `status`)
    VALUES ('$d','$jobcardNo','$cusName','$mobileNumber','$serialNumber','$email','$equipment','$charger','$qty','$modelseq','$fault','$workdone','$technician','$user','$status')";
         if(mysqli_query($db, $query)){
         //send sms
            sendSMSnew($mobileNumber, $msg);
            //save sms
            $insertSms = "INSERT INTO `sms`( `customer`, `phoneNumber`, `message`, `type`) 
            VALUES ('$cusName','$mobileNumber','$msg','JobCard')";
            mysqli_query($db, $insertSms);
         $_SESSION['iaddition'] = "Job Card ".$jobcardNo." added Successful ";
         //
         $audit = "INSERT INTO audit_trail (username, time_stamp, `action`, results, impact, ip_address)
         VALUES('$user','$d', 'add_ticket', 'success', '$jobcardNo', '$ip')";
         mysqli_query($db, $audit);
        }

 }
 
// MEMBER NO GENERATION
function generateNo(){
    $db = getConnection();
    $query2 = "SELECT `ticketnumber` FROM `djv_nums`";
    $fetchNumber = mysqli_query($db, $query2);
    $keyAvailable = mysqli_fetch_assoc($fetchNumber);
    $no =$keyAvailable['ticketNumber'] ;
    $newNo =++$no;
    $newNumber=  "DJV".$newNo."T";

    $updatequery = "UPDATE `djv_nums` SET `ticketNumber`=' $newNo' WHERE id=1";
    if(mysqli_query($db, $updatequery)){
        return $newNumber;
    }

   
}
function generateJobCardNo(){
    $db = getConnection();
    $query2 = "SELECT `jobcardNumber` FROM `djv_nums`";
    $fetchNumber = mysqli_query($db, $query2);
    $keyAvailable = mysqli_fetch_assoc($fetchNumber);
    $no =$keyAvailable['jobcardNumber'] ;
    $newNo =++$no;
    $newNumber=  "DJV".$newNo."JB";

    $updatequery = "UPDATE `djv_nums` SET `jobcardNumber`=' $newNo' WHERE id=1";
    if(mysqli_query($db, $updatequery)){
        return $newNumber;
    }

   
}
// GET A agents TICKET
function getAgentTiket($agent, $resolved = false){
    $db = getConnection();
    $query = "SELECT * FROM `insidence` WHERE `createdBy`= '$agent'  ORDER BY id DESC LIMIT 10";
    $result = mysqli_query($db, $query);
    if(mysqli_error($db)){
        echo mysqli_error($db);
    }

    return $result;
}

//getjobcards
function getAgentjobcards($agent){
    $db = getConnection();
    $queryjobcards = "SELECT * FROM `jobcards` WHERE `createdBy`='$agent' ORDER BY id DESC LIMIT 10";
    $result = mysqli_query($db, $queryjobcards);
    if(mysqli_error($db)){
        echo mysqli_error($db);
    }

    return $result;
}
function getAgentTikets($agent, $resolved = false){
    $db = getConnection();
    $query = "SELECT * FROM insidence WHERE createdBy = '$agent'  AND `status`= 'OPEN' ORDER BY id DESC ";

    if($resolved){
        $query = "SELECT * FROM insidence WHERE createdBy = '$agent'  AND `status`= 'closed' ORDER BY id DESC ";
    }

    $result = mysqli_query($db, $query);
    // if(mysqli_error($db)){
    //     echo mysqli_error($db);
    // }

    return $result;
}


function getAgentTikets2($agent, $resolved = false){
    $db = getConnection();
    $query = "SELECT * FROM insidence WHERE createdBy = '$agent'  AND `status`= 'Active' ORDER BY id DESC ";

    if($resolved){
        $query = "SELECT * FROM insidence WHERE  `status`= 'closed' ORDER BY id DESC ";
    }

    $result = mysqli_query($db, $query);
    // if(mysqli_error($db)){
    //     echo mysqli_error($db);
    // }

    return $result;
}

// all customers
function allcustomers(){
    $db = getConnection();
    $query = "SELECT * FROM customers ";
    $result = mysqli_query($db, $query);
    return $result;
}

//  GET ALL tickets
function allTickets(){
    $db = getConnection();
    $query = "SELECT * FROM `insidence`";
    $result = mysqli_query($db, $query);
    return $result;
}
function allResolvedTickets(){
    $db = getConnection();
    $query = "SELECT * FROM `insidence` WHERE `resolvedAt` IS NOT NULL AND `resolvedby` IS NOT NULL";
    $result = mysqli_query($db, $query);
    return $result;
}
//get child ticket

function childTickets($ticketNo){
    $db = getConnection();
    $query = "SELECT * FROM childIncident WHERE  tiketNo = '$ticketNo'";
    $result = mysqli_query($db, $query);
    return $result;
}

// CHILDREN

function allchildren(){
    $db = getConnection();
    $query = "SELECT * FROM members WHERE `age` BETWEEN 0.1 AND 15";
    $result = mysqli_query($db, $query);
    return $result;
}

function unasigened(){
    $db = getConnection();
    $query = "SELECT memberNo from members where name='' AND contact =''";
    $result = mysqli_query($db, $query);
    if(mysqli_error($db)){
        echo mysqli_error($db);
    }
    //echo($result);
    //$result = mysqli_fetch_assoc($result);
    //echo($result);
    $no=71;
    $number2=1411;
    foreach ($result as $row){
        if(isset($row['memberNo'])){
            //echo($row['memberNo']);
            $number = $row['memberNo'];
            echo $number;

        //    $query2 = "SELECT count(*) as count from members2";
        //    $result2 = mysqli_query($db, $query2);
        //    $no=mysqli_fetch_assoc($result2)['count'];

        //    for ($x =1; $x<$no; $x++) { 
            
            $query1 = "UPDATE `members2` SET `memberNo`='$number2' WHERE `id` = $no";
           $r= mysqli_query($db, $query1);
            $no=$no + 1;
            $number2= $number2 + 1;
            echo($no);
            if(mysqli_error($db)){
                echo mysqli_error($db);
            }
        //}
       }
    }
}



?>