<?php


class Functions {

    private $conn;

        function __construct() {
        require_once 'Connect.php';
    
        $db = new Connect();
        $this->conn = $db->connect();
    }

    // destructor
    function __destruct() {

    }



    ###############################################
    ##          Sync Ticket Transactions         ##
    ###############################################
     public function insertTicketTransactions($tickets){
      $stmt = $this->conn->prepare("INSERT IGNORE INTO `ticket_sales_tb`(`companyfk`, `routefk`, `stationfk`, `cashierfk`, `posid`, `custname`, `custnumber`, `custnextofkin`, `seatno`, `barcode`, `luggagefair`,`discount`, `transactiondate`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt->bind_param("sssssssssssss", $companyfk, $routefk, $stationfk, $cashierfk, $posid, $custname, $custnumber, $custnextofkin,  $seatno, $barcode, $luggagefair,$discount, $transactiondate);
        
        $response["ticketsTransactions"] = array();
    
        foreach ($tickets as $ticket) {
          $companyfk = $ticket['companyfk'];
          $routefk = $ticket['routefk'];
          $stationfk = $ticket['stationfk'];
          $cashierfk = $ticket['cashierfk'];
          $posid = $ticket['posid'];
          $custname = $ticket['custname'];
          $custnumber = $ticket['custnumber'];
          $custnextofkin = $ticket['custnextofkin'];
          $seatno = $ticket['seatno'];
          $barcode = $ticket['barcode'];
          $transactiondate = $ticket['transactiondate'];
          $luggagefair = $ticket['luggagefair'];
          $discount = $ticket['discount'];
         if($stmt->execute()){

            $entry["erorr"] = FALSE;
            $entry["message"] = "Inserted";
            $entry["barcode"]= $barcode;
            $response["ticketsTransactions"][] =  $entry;
        }
      }
    
  
        $stmt->close();

        return $response;
  }




    ###############################################
    ##    Insert single Ticket Transactions      ##
    ###############################################
     public function insertSingleTicketTransaction($companyfk, $routefk, $stationfk, $cashierfk, $posid, $custname, $custnumber, $custnextofkin,  $seatno, $barcode, $luggagefair,$discount, $transactiondate){
      $stmt = $this->conn->prepare("INSERT IGNORE INTO `ticket_sales_tb`(`companyfk`, `routefk`, `stationfk`, `cashierfk`, `posid`, `custname`, `custnumber`, `custnextofkin`, `seatno`, `barcode`, `luggagefair`, `discount`,`transactiondate`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt->bind_param("sssssssssssss", $companyfk, $routefk, $stationfk, $cashierfk, $posid, $custname, $custnumber, $custnextofkin,  $seatno, $barcode, $luggagefair,$discount, $transactiondate);
        $response["ticketsTransactions"] = array();
        $stmt->execute();
        $stmt->close();
        return $response;
  }



    ##############################################
    ##    Insert single Parcel Transactions     ##
    ###############################################
     public function insertSingleParcelTransaction($companyfk, $routefk, $stationfk, $cashierfk, $posid, $sendername, $receivername, $sendernumber,  $receivernumber, $price, $discount,$barcode,$depaturedate, $transactiondate){
      $stmt = $this->conn->prepare("INSERT INTO `parcel_tb`(`companyfk`, `routefk`, `stationfk`, `cashierfk`, `posid`, `sendername`, `receivername`, `sendernumber`, `receivernumber`, `price`, `discount`, `barcode`,  `depaturedate`, `transactiondate`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt->bind_param("", $companyfk, $routefk, $stationfk, $cashierfk, $posid, $sendername, $receivername, $sendernumber,  $receivernumber, $price, $discount,$barcode,$depaturedate, $transactiondate);
        $response["parcelsTransactions"] = array();
        $stmt->execute();
        $stmt->close();
        return $response;
  }

  
  



    ###############################################
    ##          Sync Parcel Transactions         ##
    ###############################################
     public function insertParcelTransactions($tickets){
      $stmt = $this->conn->prepare("INSERT IGNORE INTO `parcel_tb`(`companyfk`, `routefk`, `stationfk`, `cashierfk`, `posid`, `sendername`, `receivername`, `sendernumber`, `receivernumber`, `price`, `barcode`, `discount`,`transactiondate`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt->bind_param("ssssssssssss", $companyfk, $routefk, $stationfk, $cashierfk, $posid,  $sendername, $receivername, $sendernumber, $receivernumber, $price, $barcode, $discount ,$transactiondate);
        
        $response["parcelTransactions"] = array();
    
        foreach ($tickets as $ticket) {
          $companyfk = $ticket['companyfk'];
          $routefk = $ticket['routefk'];
          $stationfk = $ticket['stationfk'];
          $cashierfk = $ticket['cashierfk'];
          $posid = $ticket['posId'];
          $sendername = $ticket['sendername'];
          $receivername = $ticket['receivername'];
          $sendernumber = $ticket['senderphone'];
          $receivernumber = $ticket['receiverphone'];
          $price = $ticket['fair'];
          $barcode = $ticket['barcode'];
          $discount = $ticket['discount'];
          $transactiondate = $ticket['transactiondate'];
         if($stmt->execute()){
            $entry["erorr"] = FALSE;
            $entry["message"] = "Inserted";
            $entry["barcode"]= $barcode;
            $response["parcelTransactions"][] =  $entry;
        }
      }
        $stmt->close();

        return $response;
  }


    ###############################################
    ##         Fetch routes by company id        ##
    ###############################################
    public function fetchAllRoutes(){
    $stmt =  $this->conn->prepare("SELECT `routeid`, `companyfk`,`stationfk`, `origin`, `destination`, `fair` FROM `route_tb`");
    $stmt->execute();
    $response["routes"] = array();
    $stmt->bind_result($routeid, $companyfk, $stationfk, $origin, $destination, $fair);
        while($stmt->fetch()) 
        {
            $route = array();
            $route["routeid"] = $routeid;
            $route["companyfk"] = $companyfk; 
            $route["stationfk"] = $stationfk; 
            $route["origin"] = $origin;  
            $route["destination"] = $destination; 
            $route["fair"] = $fair;  
            $response["routes"][] =  $route;
        }
    return $response;
    }


    ###############################################
    ##         Fetch routes by company id        ##
    ###############################################
    public function fetchAllRoutesByCompanyId($companyfk){
    $stmt =  $this->conn->prepare("SELECT `routeid`, CONCAT(`origin`,' to ',`destination`) jorney FROM `route_tb` WHERE `companyfk` = ?");
    $stmt->bind_param("s", $companyfk);
    $stmt->execute();
    $response["routes"] = array();
    $stmt->bind_result($routeid, $jorney);
        while($stmt->fetch()) 
        {
            $route = array();
            $route["routeid"] = $routeid;
            $route["jorney"] = $jorney; 
            $response["routes"][] =  $route;
        }
    return $response;
    }


    ###############################################
    ##               Fetch all users             ##
    ###############################################
    public function fetchAllUsers(){
    $stmt =  $this->conn->prepare("SELECT `userid`, `companyfk`, `username`, `fname`, `lname`, `password`, `userlevel`, `datecreated` FROM `user_tb`");
    $stmt->execute();
    $response["users"] = array();
    $stmt->bind_result($userid, $companyfk, $username, $fname, $lname, $password, $userlevel, $datecreated);
        while($stmt->fetch()) 
        {
            $user = array();
            $user["userid"] = $userid;
            $user["companyfk"] = $companyfk; 
            $user["username"] = $username;  
            $user["fname"] = $fname; 
            $user["lname"] = $lname;  
            $user["password"] = $password; 
            $user["userlevel"] = $userlevel;  
            $user["datecreated"] = $datecreated; 
            $response["users"][] =  $user;
        }
    return $response;
    }



    ###############################################
    ##       Fetch stations by company id        ##
    ###############################################
    public function fetchAllStations(){
    $stmt =  $this->conn->prepare("SELECT `stationid`, `companyfk`, `name`, `location` FROM `station_tb`");
    $stmt->execute();
    $response["stations"] = array();
    $stmt->bind_result($stationid, $companyfk, $name, $location);
        while($stmt->fetch()) 
        {
            $station = array();
            $station["stationid"] = $stationid;
            $station["companyfk"] = $companyfk; 
            $station["name"] = $name;  
            $station["location"] = $location;  
            $response["stations"][] =  $station;

        }
    return $response;
    }


        ###############################################
    ##       Fetch stations by company id        ##
    ###############################################
    public function fetchAllStationsByCompanyId($companyfk){
    $stmt =  $this->conn->prepare("SELECT `stationid`, `name` FROM `station_tb` WHERE `companyfk` = ?");
    $stmt->bind_param("s", $companyfk);
    $stmt->execute();
    $response["stations"] = array();
    $stmt->bind_result($stationid, $name);
        while($stmt->fetch()) 
        {
            $station = array();
            $station["stationid"] = $stationid;
            $station["name"] = $name; 
            $response["stations"][] =  $station;
        }
    return $response;
    }



    ###############################################
    ##          Fetch buses by company id        ##
    ###############################################
    public function fetchAllBuses($companyfk){
    $stmt =  $this->conn->prepare("SELECT `busid`, `companyfk`, `plateno`, `numberofseats`, `datecreated` FROM `bus_tb` WHERE `companyfk` = ?");
    $stmt->bind_param("s", $companyfk);
    $stmt->execute();
    $response["buses"] = array();
    $stmt->bind_result($busid, $companyfk, $plateno, $numberofseats, $datecreated);
        while($stmt->fetch()) 
        {
            $station = array();
            $station["stationid"] = $stationid;
            $station["companyfk"] = $companyfk; 
            $station["name"] = $name;  
            $station["location"] = $location;  
            $response["stations"][] =  $station;

        }
    return $response;
    }
    

   




    ############################################################
    ##                   Fetch Revenues                      ##
    ############################################################
    public function fetchTicketDashboardRevenues($companyfk){
    $result =  $this->conn->prepare("SELECT 
(SELECT IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk WHERE t.companyfk = ? AND t.reversed = 0) 
as lifetimeRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  where t.datecreated >= NOW() - INTERVAL 1 DAY AND t.companyfk = ? AND t.reversed = 0) 
as dayRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  where t.datecreated >= NOW() - INTERVAL 7 DAY AND t.companyfk = ? AND t.reversed = 0) 
as weekRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  WHERE MONTH(t.datecreated) = MONTH(CURRENT_DATE()) AND YEAR(t.datecreated) = YEAR(CURRENT_DATE()) AND t.companyfk = ? AND t.reversed = 0) 
as monthRevenue");
    $result->bind_param("ssss", $companyfk, $companyfk, $companyfk, $companyfk);
    $result->execute();
    $result = $result->get_result();
    $revenue = $result->fetch_assoc();
    return $revenue;
    }



    ############################################################
    ##              Fetch reversed Revenues                      ##
    ############################################################
    public function fetchTicketReversedDashboardRevenues($companyfk){
    $result =  $this->conn->prepare("SELECT 
(SELECT IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk WHERE t.companyfk = ? AND t.reversed = 1) 
as lifetimeRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  where t.datecreated >= NOW() - INTERVAL 1 DAY AND t.companyfk = ? AND t.reversed = 1) 
as dayRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  where t.datecreated >= NOW() - INTERVAL 7 DAY AND t.companyfk = ? AND t.reversed = 1) 
as weekRevenue,
(SELECT  IFNULL(SUM(r.fair), 0) FROM ticket_transactions_tb t JOIN route_tb r ON r.routeid = t.routefk  WHERE MONTH(t.datecreated) = MONTH(CURRENT_DATE()) AND YEAR(t.datecreated) = YEAR(CURRENT_DATE()) AND t.companyfk = ? AND t.reversed = 1) 
as monthRevenue");
    $result->bind_param("ssss", $companyfk, $companyfk, $companyfk, $companyfk);
    $result->execute();
    $result = $result->get_result();
    $revenue = $result->fetch_assoc();
    return $revenue;
    }
    
    
    

    ############################################################
    ##                   Fetch Revenues                      ##
    ############################################################
    public function fetchParcelDashboardRevenues($companyfk){
    $result =  $this->conn->prepare("SELECT 
(SELECT IFNULL(SUM(p.fair), 0) FROM parcel_tb p  WHERE p.companyfk = ? AND p.reversed = 0) 
as lifetimeRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p where p.transactiondate >= NOW() - INTERVAL 1 DAY AND p.companyfk = ? AND p.reversed = 0) 
as dayRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p where p.transactiondate >= NOW() - INTERVAL 7 DAY AND p.companyfk = ? AND p.reversed = 0) 
as weekRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p WHERE MONTH(p.transactiondate) = MONTH(CURRENT_DATE()) AND YEAR(p.transactiondate) = YEAR(CURRENT_DATE()) AND p.companyfk = ? AND p.reversed = 0) 
as monthRevenue");
    $result->bind_param("ssss", $companyfk, $companyfk, $companyfk, $companyfk);
    $result->execute();
    $result = $result->get_result();
    $revenue = $result->fetch_assoc();
    return $revenue;
    }


     ############################################################
    ##        Fetch All Reversed parcel Revenues               ##
    ############################################################
    public function fetchReversedParcelDashboardRevenues($companyfk){
    $result =  $this->conn->prepare("SELECT 
(SELECT IFNULL(SUM(p.fair), 0) FROM parcel_tb p  WHERE p.companyfk = ? AND p.reversed = 1) 
as lifetimeRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p where p.transactiondate >= NOW() - INTERVAL 1 DAY AND p.companyfk = ? AND p.reversed = 1) 
as dayRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p where p.transactiondate >= NOW() - INTERVAL 7 DAY AND p.companyfk = ? AND p.reversed = 1) 
as weekRevenue,
(SELECT  IFNULL(SUM(p.fair), 0) FROM parcel_tb p WHERE MONTH(p.transactiondate) = MONTH(CURRENT_DATE()) AND YEAR(p.transactiondate) = YEAR(CURRENT_DATE()) AND p.companyfk = ? AND p.reversed = 1) 
as monthRevenue");
    $result->bind_param("ssss", $companyfk, $companyfk, $companyfk, $companyfk);
    $result->execute();
    $result = $result->get_result();
    $revenue = $result->fetch_assoc();
    return $revenue;
    }
    




     ############################################################
     ##                   Fetch all Trip Revenues                      ##
     ############################################################
    public function fetchTripDashboardRevenues($companyfk){
        $result =  $this->conn->prepare("SELECT 
        (SELECT IFNULL(SUM(t.fair), 0) FROM journey_tb j
        JOIN ticket_transactions_tb t ON j.journeyid = t.journeyid AND j.`companyfk` = t.`companyfk`
        JOIN parcel_tb p ON j.`journeyid` = t.`journeyid` AND j.`companyfk` = p.`companyfk`
        WHERE j.companyfk = ?
         ) 
        AS lifetimeRevenue,
        (SELECT  IFNULL(SUM(t.fair), 0) FROM journey_tb j
         JOIN ticket_transactions_tb t ON j.journeyid = t.journeyid AND j.`companyfk` = t.`companyfk`
         JOIN parcel_tb p ON j.`journeyid` = t.`journeyid` AND j.`companyfk` = p.`companyfk`
         WHERE t.datecreated >= NOW() - INTERVAL 1 DAY AND t.companyfk = ?) 
        AS dayRevenue,
        (SELECT  IFNULL(SUM(t.fair), 0) FROM journey_tb j
         JOIN ticket_transactions_tb t ON j.journeyid = t.journeyid AND j.`companyfk` = t.`companyfk`
         JOIN parcel_tb p ON j.`journeyid` = t.`journeyid` AND j.`companyfk` = p.`companyfk`
         WHERE t.datecreated >= NOW() - INTERVAL 7 DAY AND t.companyfk = ?) 
        AS weekRevenue,
        (SELECT  IFNULL(SUM(t.fair), 0) FROM journey_tb j
         JOIN ticket_transactions_tb t ON j.journeyid = t.journeyid AND j.`companyfk` = t.`companyfk`
         JOIN parcel_tb p ON j.`journeyid` = t.`journeyid` AND j.`companyfk` = p.`companyfk`
         WHERE MONTH(t.datecreated) = MONTH(CURRENT_DATE()) AND YEAR(t.datecreated) = YEAR(CURRENT_DATE()) AND t.companyfk = ?) 
        AS monthRevenue");
        $result->bind_param("ssss", $companyfk, $companyfk, $companyfk, $companyfk);
        $result->execute();
        $result = $result->get_result();
        $revenue = $result->fetch_assoc();
        return $revenue;
        }
    


    ############################################################
    ##          Fetch ParcelTransaction by company id        ##
    ############################################################
    public function fetchAllTripTransaction($companyfk){
        $result =  $this->conn->prepare("SELECT j.journeyid,j.origin,j.destination,j.fair,j.journey_date,j.bus_number_plate,j.seat_number,j.datecreated, Count(t.ticketid) sold_tickets, COUNT(p.parcelid) sold_parcels
        FROM journey_tb j
        LEFT JOIN ticket_transactions_tb t ON j.journeyid = t.journeyid
        LEFT JOIN parcel_tb p ON j.journeyid = p.journeyid
        where  j.companyfk = ?
        group by j.journeyid");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["trips"] = array();
        $result->bind_result($journeyid,$origin, $destination,$fair,$journey_date,$bus_number_plate,$seat_number,$datecreated,$sold_tickets,$sold_parcel);
            while($row = $result->fetch()) 
            {
               $trip = array();
               $trip["journeyid"] = $journeyid;
               $trip["origin"] = $origin;
               $trip["destination"] = $destination;
               $trip["fair"] = $fair;
               $trip["journey_date"] = $journey_date;
               $trip["bus_number_plate"] = $bus_number_plate;  
               $trip["seat_number"] = $seat_number;  
               $trip["datecreated"] = $datecreated;  
               $trip["sold_tickets"] = $sold_tickets;  
               $trip["sold_parcel"] = $sold_parcel;  
               $response["trips"][] =   $trip;
            }
        return $response;
        }


     






    ############################################################
    ##          Fetch ParcelTransaction by company id        ##
    ############################################################
    public function fetchAllParcelTransaction($companyfk){
        $result =  $this->conn->prepare(" SELECT p.`parcelid`, CONCAT(r.`origin`,' ' ,r.`destination`) routeName,
        s.`name`,p.`sendernumber`,p.`receivernumber`, p.`cashiername`,p.`fair`, p.`transactiondate`
        FROM `parcel_tb` p 
        JOIN company_tb c ON p.companyfk = c.companyid
        JOIN route_tb r ON r.routeid = p.routefk 
        JOIN station_tb s ON s.stationid = r.stationid
        WHERE p.`companyfk`= ? AND p.`reversed` = 0 ORDER BY transactiondate DESC");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["parcel"] = array();
        $result->bind_result($parcelid,$routeName, $station,$sendernumber,$receivernumber,$cashiername,$fair,$transactiondate);
            while($row = $result->fetch()) 
            {
               $parcel = array();
               $parcel["parcelid"] = $parcelid;
               $parcel["routeName"] =  $routeName;
               $parcel["name"] = $station; 
               $parcel["sendernumber"] = $sendernumber;
               $parcel["receivernumber"] = $receivernumber;
               $parcel["cashiername"] = $cashiername;
               $parcel["fair"] = $fair;
               $parcel["transactiondate"] = $transactiondate;  
               $response["parcel"][] =   $parcel;
    
            }
        return $response;
        }
        
    

     ############################################################
    ##    Fetch Reversed ParcelTransaction by company id      ##
    ############################################################
    public function fetchAllReversedParcelTransaction($companyfk){
        $result =  $this->conn->prepare(" SELECT p.`parcelid`, CONCAT(r.`origin`,' ' ,r.`destination`) routeName,
        s.`name`,p.`sendernumber`,p.`receivernumber`, p.`cashiername`,p.`fair`, p.`transactiondate`
        FROM `parcel_tb` p JOIN station_tb s ON s.companyfk = p.companyfk
        JOIN route_tb r ON r.routeid = p.routefk WHERE p.`companyfk`= ? AND p.`reversed` = 1 ORDER by transactiondate DESC ");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["parcel"] = array();
        $result->bind_result($parcelid,$routeName, $station,$sendernumber,$receivernumber,$cashiername,$price,$transactiondate);
            while($row = $result->fetch()) 
            {
               $parcel = array();
               $parcel["parcelid"] = $parcelid;
               $parcel["routeName"] =  $routeName;
               $parcel["name"] = $station; 
               $parcel["sendernumber"] = $sendernumber;
               $parcel["receivernumber"] = $receivernumber;
               $parcel["cashiername"] = $cashiername;
               $parcel["fair"] = $price;
               $parcel["transactiondate"] = $transactiondate;  
               $response["parcel"][] =   $parcel;

        }
    return $response;
    }
    

    
    

    ###################################################################
    ##          Fetch TicketTransaction by company id                ##
    ###################################################################
    public function fetchTicketSalesStatistics($companyfk){
    $result =  $this->conn->prepare("SELECT COUNT(ticketid), CONCAT(MONTHNAME(datecreated),', ', YEAR(datecreated)) MONTH FROM ticket_transactions_tb WHERE companyfk = ? And reversed = 0 GROUP BY YEAR(datecreated), MONTH(datecreated) ORDER BY datecreated ASC LIMIT 12");
    $result->bind_param("s", $companyfk);
    $result->execute();
    $response["salesstatistic"] = array();
    $result->bind_result($totalCount, $month);
        while($row = $result->fetch()) 
        {
            $sale = array();
            $sale["month"] =  $month;  
            $sale["count"] = $totalCount;
            $response["salesstatistic"][] =  $sale;
        }
    return json_encode($response["salesstatistic"]);
    }



    //  ###################################################################
    // ##          Fetch TicketTransaction by company id                ##
    // ###################################################################
    // public function fetchTicketSalesStatistics($companyfk){
    //     $result =  $this->conn->prepare("SELECT COUNT(ticketid), CONCAT(MONTHNAME(datecreated),', ', YEAR(datecreated)) MONTH FROM ticket_transactions_tb WHERE companyfk = ? And reversed = 0 GROUP BY YEAR(datecreated), MONTH(datecreated) ORDER BY datecreated ASC LIMIT 12");
    //     $result->bind_param("s", $companyfk);
    //     $result->execute();
    //     $response["salesstatistic"] = array();
    //     $result->bind_result($totalCount, $month);
    //         while($row = $result->fetch()) 
    //         {
    //             $sale = array();
    //             $sale["month"] =  $month;  
    //             $sale["count"] = $totalCount;
    //             $response["salesstatistic"][] =  $sale;
    //         }
    //     return json_encode($response["salesstatistic"]);
    //     }




 ###################################################################
    ##          Fetch TicketTransaction by company id                ##
    ###################################################################
    public function fetchTripTicketSalesStatistics($companyfk){
        $result =  $this->conn->prepare("SELECT COUNT(ticketid), CONCAT(MONTHNAME(datecreated),', ', YEAR(datecreated)) MONTH FROM ticket_transactions_tb WHERE companyfk = ? And reversed = 0 GROUP BY YEAR(datecreated), MONTH(datecreated) ORDER BY datecreated ASC LIMIT 12");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["salesstatistic"] = array();
        $result->bind_result($totalCount, $month);
            while($row = $result->fetch()) 
            {
                $sale = array();
                $sale["month"] =  $month;  
                $sale["count"] = $totalCount;
                $response["salesstatistic"][] =  $sale;
            }
        return json_encode($response["salesstatistic"]);
        }

    


    ###################################################################
    ##     Fetch Reversed TicketTransaction by company id            ##
    ###################################################################
    public function fetchReversedTicketSalesStatistics($companyfk){
    $result =  $this->conn->prepare("SELECT COUNT(ticketid), CONCAT(MONTHNAME(datecreated),', ', YEAR(datecreated)) MONTH FROM ticket_transactions_tb WHERE companyfk = ? And reversed = 1 GROUP BY YEAR(datecreated), MONTH(datecreated) ORDER BY datecreated ASC LIMIT 12");
    $result->bind_param("s", $companyfk);
    $result->execute();
    $response["salesstatistic"] = array();
    $result->bind_result($totalCount, $month);
        while($row = $result->fetch()) 
        {
            $sale = array();
            $sale["month"] =  $month;  
            $sale["count"] = $totalCount;
            $response["salesstatistic"][] =  $sale;

        }
    return json_encode($response["salesstatistic"]);
    }




    ###################################################################
    ##          Fetch ParcelTransaction by company id                ##
    ###################################################################
    public function fetchParcelSalesStatistics(){
    $result =  $this->conn->prepare("SELECT COUNT(parcelid), CONCAT(MONTHNAME(transactiondate),', ', YEAR(transactiondate)) month FROM parcel_tb WHERE reversed = 0 GROUP BY YEAR(transactiondate), MONTH(transactiondate) ORDER BY transactiondate ASC LIMIT 12");
    $result->execute();
    $response["salesstatistic"] = array();
    $result->bind_result($totalCount, $month);
        while($row = $result->fetch()) 
        {
            $sale = array();
            $sale["month"] =  $month;  
            $sale["count"] = $totalCount;
            $response["salesstatistic"][] =  $sale;

        }
    return json_encode($response["salesstatistic"]);
    }



    ###################################################################
    ##    Fetch Reversed ParcelTransaction by company id             ##
    ###################################################################
    public function fetchReversedParcelSalesStatistics(){
    $result =  $this->conn->prepare("SELECT COUNT(parcelid), CONCAT(MONTHNAME(transactiondate),', ', YEAR(transactiondate)) month FROM parcel_tb WHERE reversed = 1  GROUP BY YEAR(transactiondate), MONTH(transactiondate) ORDER BY transactiondate ASC LIMIT 12");
    $result->execute();
    $response["salesstatistic"] = array();
    $result->bind_result($totalCount, $month);
        while($row = $result->fetch()) 
        {
            $sale = array();
            $sale["month"] =  $month;  
            $sale["count"] = $totalCount;
            $response["salesstatistic"][] =  $sale;

        }
    return json_encode($response["salesstatistic"]);
    }




    ###################################################################
    ##               Fetch All cashiers by company id             ##
    ###################################################################
    public function fetchAllCashiers($companyfk){
    $result =  $this->conn->prepare("SELECT `userid`, CONCAT(`fname`, ' ', `lname`) FROM `user_tb` WHERE `companyfk` = ? AND `userlevel` = 3");
    $result->bind_param("s", $companyfk);
    $result->execute();
    $response["cashiers"] = array();
    $result->bind_result($userid, $name);
        while($row = $result->fetch()) 
        {
            $cashier = array();
            $cashier["userid"] =  $userid;  
            $cashier["name"] = $name;
            $response["cashiers"][] =  $cashier;
            

        }
    return $response;
    }


    
    ###################################################################
    ##          Fetch TicketTransaction by company id                ##
    ###################################################################
    public function fetchAllTicketTransaction($companyfk){
        $result =  $this->conn->prepare(" SELECT DISTINCT t.`ticketid`, CONCAT(r.`origin`,' ' ,r.`destination`) routeName, s.name, t.`custnumber`, 
        t.`custnextofkin`,r.`fair`,t.`cashiername`,t.`luggagefair`, t.`datecreated` 
        FROM `ticket_transactions_tb` t 
        JOIN company_tb c ON t.`companyfk` = c.companyid 
        JOIN route_tb r ON r.`routeid` = t.`routefk` 
        JOIN station_tb s ON s.stationid = r.stationid WHERE t.`companyfk`= ? AND t.`reversed` = 0 ORDER BY datecreated DESC
");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["transactions"] = array();
        $result->bind_result($ticketid,$routeName,$station,$custnumber,$custnextofkin,$fair,$cashiername,$luggagefair,$transactiondate);
            while($row = $result->fetch()) 
            {
                $ticket = array();
                $ticket["ticketid"] = $ticketid;
                $ticket["routeName"] =  $routeName;  
                $ticket["name"] = $station;
                $ticket["custnumber"] = $custnumber;  
                $ticket["custnextofkin"] = $custnextofkin;
                $ticket["fair"] = $fair; 
                $ticket["cashiername"] = $cashiername;
                $ticket["luggagefair"] = $luggagefair;
                $ticket["datecreated"] = $transactiondate;  
                $response["transactions"][] =  $ticket;
            }
        return $response;
        }
    
    ###################################################################
    ##          Fetch ReversedTicketTransaction by company id        ##
    ###################################################################
    public function fetchAllReversedTicketTransaction($companyfk){
        $result =  $this->conn->prepare(" SELECT t.`ticketid`, CONCAT(r.`origin`,' ' ,r.`destination`) routeName,
        s.name, t.`custnumber`, t.`custnextofkin`,r.`fair`,t.`cashiername`,t.`luggagefair`, t.`datecreated`
        FROM `ticket_transactions_tb` t JOIN station_tb s ON s.`companyfk` = t.`companyfk`
        JOIN route_tb r ON r.`routeid` = t.`routefk` WHERE t.`companyfk`= ? AND t.`reversed` = 1 ORDER by datecreated DESC");
        $result->bind_param("s", $companyfk);
        $result->execute();
        $response["transactions"] = array();
        $result->bind_result($ticketid,$routeName,$station,$custnumber,$custnextofkin,$fair,$cashiername,$luggagefair,$transactiondate);
            while($row = $result->fetch()) 
            {
                $ticket = array();
                $ticket["ticketid"] = $ticketid;
                $ticket["routeName"] =  $routeName;  
                $ticket["name"] = $station;
                $ticket["custnumber"] = $custnumber;  
                $ticket["custnextofkin"] = $custnextofkin;
                $ticket["fair"] = $fair; 
                $ticket["cashiername"] = $cashiername;
                $ticket["luggagefair"] = $luggagefair;
                $ticket["datecreated"] = $transactiondate;  
                $response["transactions"][] =  $ticket;

        }
    return $response;
    }
    
    
    
  


    ###############################################
    ##    Add multiple luggage transactions      ##
    ###############################################

     public function addLuggageMultipleTransactions($luggageTransactions){
     $response["luggageTransactionUploadResponse"] = array();
     $stmt = $this->conn->prepare("INSERT INTO `luggage_tb`(`ticketfk`,`companyfk`, `routefk`, `stationfk`, `posid`, `custname`, `custnumber`,  `cashiername`, `barcode`, `discount`,`transactiondate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
     $stmt->bind_param("ssssssssss", $ticketfk, $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $cashiername, $barcode, $discount, $transactiondate);
        
        foreach ($luggageTransactions as $luggageTransaction) {
          $ticketfk = $luggageTransaction['ticketfk'];
          $companyfk = $luggageTransaction['companyfk'];
          $routefk = $luggageTransaction['routefk'];
          $stationfk = $luggageTransaction['stationfk'];
          $posid = $luggageTransaction['posid'];
          $custname = $luggageTransaction['custname'];
          $custnumber = $luggageTransaction['custnumber'];
          $cashiername = $luggageTransaction['cashiername'];
          $barcode = $luggageTransaction['barcode'];
          $discount = $luggageTransaction['discount'];
          $transactiondate = $luggageTransaction['transactiondate'];
          $datecreated = $luggageTransaction['datecreated'];
          $transaction = array();
          $transaction["barcode"] = $barcode;
          $stmt->execute();
        }
        $stmt->close();
        return $error;
  }



    ###############################################
    ##   Add multiple ticket transactions        ##
    ###############################################

     public function addTicketMultipleTransactions($ticketTransactions){
     $response["ticketTransactionUploadResponse"] = array();
     $stmt = $this->conn->prepare("INSERT INTO `ticket_sales_tb`(`companyfk`, `routefk`, `stationfk`, `posid`, `custname`, `custnumber`, `custnextofkin`, `cashiername`, `seatno`, `barcode`, `discount`,`transactiondate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
     $stmt->bind_param("sssssssssss", $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $custnextofkin, $cashiername, $seatno, $barcode, $discount,$transactiondate);
        
        foreach ($ticketTransactions as $ticketTransaction) {
          $companyfk = $ticketTransaction['companyfk'];
          $routefk = $ticketTransaction['routefk'];
          $stationfk = $ticketTransaction['stationfk'];
          $posid = $ticketTransaction['posid'];
          $custname = $ticketTransaction['custname'];
          $custnumber = $ticketTransaction['custnumber'];
          $custnextofkin = $ticketTransaction['custnextofkin'];
          $cashiername = $ticketTransaction['cashiername'];
          $seatno = $ticketTransaction['seatno'];
          $barcode = $ticketTransaction['barcode'];
          $discount = $luggageTransaction['discount'];
          $transactiondate = $ticketTransaction['transactiondate'];
          $datecreated = $ticketTransaction['datecreated'];
          $transaction = array();
          $transaction["barcode"] = $barcode;
          $stmt->execute();
        }
        $stmt->close();
        return $error;
  }
  
  
      ###############################################
      ##          UpdateTickets                ##
      ##############################################
  
    public function  UpdateTickets($companyfk, $ticketid)
    {
    $stmt = $this->conn->prepare("UPDATE ticket_transactions_tb SET reversed = 1 WHERE  companyfk =? and ticketid= ?");
    $stmt->bind_param('ss',$companyfk, $ticketid);
    if($stmt->execute())
    return $this->fetchAllTicketTransaction($companyfk);
    else
    return false;
    
    }
  
  
      ###############################################
      ##          Updateparcel              ##
      ##############################################
  
    public function Updateparcel($companyfk, $parcelid)
    {
    $stmt = $this->conn->prepare("UPDATE parcel_tb SET reversed = 1 WHERE  companyfk =? and parcelid = ?");
    $stmt->bind_param('ss',$companyfk, $parcelid);
    $stmt->execute();
    return $stmt->affected_rows;
    }
  

      ###############################################
      ##          Add Updatereversed              ##
      ##############################################


     public function Updateticketreversed($ticketfk, $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $cashiername, $barcode, $transactiondate){
        $stmt = $this->conn->prepare("INSERT INTO `luggage_tb`(`ticketfk`, `companyfk`, `routefk`, `stationfk`, `posid`, `custname`, `custnumber`, `cashiername`, `barcode`, `transactiondate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("ssssssssss", $ticketfk, $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $cashiername, $barcode, $transactiondate);
        $stmt->execute();
        $stmt->close();
        
     } 

  
    ###############################################
    ##                  Add Route              ##
    ###############################################
     public function addroute($companyfk,$stationid, $origin, $destination,$fair){
        $stmt = $this->conn->prepare("INSERT INTO `route_tb`(`companyfk`,`stationid`, `origin`, `destination`,`fair`) VALUES (?, ?, ?, ?, ?)");
        $stmt->bind_param("sssss",$companyfk,$stationid, $origin, $destination,$fair);
        $stmt->execute();
        $stmt->close();
     } 

    
            
    ###############################################
    ##                  Add Station              ##
    ###############################################


     public function addStation($companyfk, $name, $location){
        $stmt = $this->conn->prepare("INSERT INTO `station_tb`(`companyfk`, `name`, `location`) VALUES (?, ?, ?)");
        $stmt->bind_param("sss",$companyfk, $name, $location);
        $stmt->execute();
        $stmt->close();
     }   
            
            
            
            
            

    ###############################################
    ##          Add Single Luggage               ##
    ###############################################


     public function addTicketSingleLuggage($ticketfk, $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $cashiername, $barcode, $transactiondate){
        $stmt = $this->conn->prepare("INSERT INTO `luggage_tb`(`ticketfk`, `companyfk`, `routefk`, `stationfk`, `posid`, `custname`, `custnumber`, `cashiername`, `barcode`, `transactiondate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("ssssssssss", $ticketfk, $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $cashiername, $barcode, $transactiondate);
        $stmt->execute();
        $stmt->close();
        
     } 

    ###############################################
    ##                  Add bus                  ##
    ###############################################


     public function addBus($companyfk, $plateno, $numberofseats){
        $stmt = $this->conn->prepare("INSERT INTO `buses_tb`(`companyfk`, `number_plate`, `seats`) VALUES (?, ?, ?)");
        $stmt->bind_param("sss",$companyfk, $plateno, $numberofseats);
        $stmt->execute();
        $stmt->close();
     } 


    ###############################################
    ##        Single Ticket Transaction          ##
    ###############################################

     public function addTicketSingleTransaction($companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $custnextofkin, $cashiername, $seatno, $barcode, $transactiondate){
        $stmt = $this->conn->prepare("INSERT INTO `ticket_sales_tb`(`companyfk`, `routefk`, `stationfk`, `posid`, `custname`, `custnumber`, `custnextofkin`, `cashiername`, `seatno`, `barcode`, `transactiondate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param("sssssssssss", $companyfk, $routefk, $stationfk, $posid, $custname, $custnumber, $custnextofkin, $cashiername, $seatno, $barcode, $transactiondate);
        $stmt->execute();
        $stmt->close();
     } 



    ###############################################
    ##             Register company              ##
    ###############################################

    public function registerCompany($name, $phone, $email, $plotno, $street, $city){
    $response['error'] = false;
    $response['message'] = 'Company created successfully, login.';

    if(!$this->isCompanyExisted($name)){
     $stmt = $this->conn->prepare("INSERT INTO `company_tb`(`name`, `phone`, `email`, `plotno`, `street`, `city`) VALUES (?, ?, ?, ?, ?, ?)");
     $stmt->bind_param("ssssss", $name, $phone, $email, $plotno, $street, $city);

        if($stmt->execute()){
          $error = FALSE;
        }
        else{
          $response['error'] = true;
          $response['message'] = 'Error occured, try again'; 
        }
        
      }else{
       $response['error'] = true;
       $response['message'] = 'Company already exits.';
      }
      $stmt->close();
      return $response;
     }



    ###############################################
    ##           Register company user           ##
    ###############################################

    public function registerUser($companyfk, $username, $fname, $lname,$station,$password){
    $response['error'] = false;
    $response['message'] = 'User created successfully, login.';
    if(!$this->isUserExisted($username, $password)){
     $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
     $stmt = $this->conn->prepare("INSERT INTO `user_tb`(`companyfk`, `username`, `fname`, `lname`, `stationfk`, `password_hash`) VALUES (?, ?, ?, ?, ?, ?)");
     $stmt->bind_param("ssssss", $companyfk, $username, $fname, $lname, $station, $hashedPassword);
        if($stmt->execute()){
          $error = FALSE;
          $stmt->close();
        }
        else{
          $response['error'] = true;
          $response['message'] = 'Error occured, try again'; 
        }
      }else{
       $response['error'] = true;
       $response['message'] = 'Username already in use.';
      }
      return $response;
    }





    ###############################################
    ##           check if user exists            ##
    ###############################################

      public function isUserExisted($username) {
        $stmt = $this->conn->prepare("SELECT username from user_tb WHERE username = ?");
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            $stmt->close();
            return true;
        } else {
            $stmt->close();
            return false;
        }
    }





    ###############################################
    ##          Check if company exists          ##
    ###############################################

      public function isCompanyExisted($name) {
        $stmt = $this->conn->prepare("SELECT  `name` FROM `company_tb` WHERE `name` = ?");
        $stmt->bind_param("s", $name);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            $stmt->close();
            return true;
        } else {
            $stmt->close();
            return false;
        }
    }


     ###############################################
     ##              Login company user           ##
     ###############################################


  public function login($username, $password){
    $response['error'] = true;
    $response['message'] = 'Incorrect credentials';
    $user = array();
    $stmt = $this->conn->prepare("SELECT `userid`, `companyfk`,`stationfk`,`username`, `fname`, `lname`, `password_hash`, `datecreated` FROM `user_tb` WHERE `username` = ? and userlevel = 2 ");
    $stmt->bind_param("s", $username);
    $stmt->bind_result($userid, $companyfk,$stationfk, $username, $fname, $lname, $hashedPassword, $datecreated);
    if($stmt->execute())
    while($row = $stmt->fetch()) 
    {
         if (password_verify($password, $hashedPassword))
         {
                $user['userid'] = $userid;
                $user['companyfk'] = $companyfk;
                $user['username'] = $username;
                $user['fname'] = $fname;
                $user['lname'] = $lname;
                $user['stationfk'] = $stationfk;
                $user['password_hash'] = $password_hash;
                $user['datecreated'] = $datecreated;
                $response['user'] = $user;
                $response['error'] = false;
                $response['message'] = 'Welcome back.';
              }

    }
        
         

        return $response;
}




 

}

?>