/amfphp/services/TicketsDAO.php

<?php
class TicketsDAO {
    private $link;
    function TicketsDAO() {
        $this->link = new Datasource();
    }

    function addProduct($p) {
        //check name
        $sql = "SELECT count(id) as _count FROM tickets_product WHERE tickets_id='".$p['tickets_id']."' AND seat_name='".$p['type']."' ";
        $result = $this->link->_execute($sql);
        $row = $this->link->_nextRow($result);
        if($row['_count']>0){
            return array('error'=>'Seat Type Unavailable');
        }else if($p['price']<5){
            return array('error'=>'Minimum Seat Price is $5');
        }
        else{
            $sql = "INSERT tickets_product SET tickets_id='".$p['tickets_id']."', seat_name='".$p['type']."',
                                            seat_qty='".$p['quantity']."', seat_price='".$p['price']."',color='".$p['color']."',
                                            enabled='".($p['enabled']=='true' ? 1: 0)."', handicap='".($p['handicap']=='true' ? 1: 0)."' ";
            $this->link->_execute($sql);
            $p['id'] = mysql_insert_id();

            //insert for ticket children
            $sql = "SELECT id FROM tickets WHERE parent_id='".$p['tickets_id']."'";
            $result = $this->link->_execute($sql);
            while($row = $this->link->_nextRow($result)){
                $sql = "INSERT tickets_product SET tickets_id='".$row['id']."', seat_name='".$p['type']."',
                                            seat_qty='".$p['quantity']."', seat_price='".$p['price']."',color='".$p['color']."',
                                            enabled='".($p['enabled']=='true' ? 1: 0)."', handicap='".($p['handicap']=='true' ? 1: 0)."' ";
                $this->link->_execute($sql);
            }
            return $p;
        }
    }

    function updateProduct($p) {
        //get seat_name old
        $sql = "SELECT * FROM tickets_product WHERE id='".$p['id']."'";
        $result = $this->link->_execute($sql);
        $row = $this->link->_nextRow($result);
        $seat_name_old = $row['seat_name'];
        $seat_price_old = $row['seat_price'];
        //check name
        $sql = "SELECT count(id) as _count FROM tickets_product WHERE tickets_id='".$p['tickets_id']."' AND seat_name='".$p['type']."' AND id<>'".$p['id']."' ";
        $result = $this->link->_execute($sql);
        $row = $this->link->_nextRow($result);
        if($row['_count']>0){
            $p['error'] = "Seat Type Unavailable";
            $p['type'] = $seat_name_old;            
        }else if($p['price']<5){
            $p['error'] = "Minimum Seat Price is $5";
            $p['price'] = $seat_price_old;
        }
        
        $listid = $this->getListTicketProductIdOfChildren($p['tickets_id'], $seat_name_old);
        $listid[] = $p['id'];
        
        $sql = "UPDATE tickets_product SET seat_name='".$p['type']."',
                                       seat_qty='".$p['quantity']."', seat_price='".$p['price']."',color='".$p['color']."',
                                        enabled='".($p['enabled']=='true' ? 1: 0)."', handicap='".($p['handicap']=='true' ? 1: 0)."'
                WHERE id IN (".join(',',$listid).")";
        $this->link->_execute($sql);        

        return $p;
    }

    function removeProduct($id) {
        $sql = "SELECT * FROM tickets_product WHERE id='".$id."'";
        $result = $this->link->_execute($sql);
        $row = $this->link->_nextRow($result);

        $listid = $this->getListTicketProductIdOfChildren($row['tickets_id'], $row['seat_name']);
        $listid[] = $id;
        $sql = "DELETE FROM tickets_product WHERE id IN (".join(',',$listid).")";
        return $this->link->_execute($sql);
    }

    function getEvent($id) {
        $sql = "SELECT * FROM tickets WHERE id='".$id."' LIMIT 1";
        $result = $this->link->_execute($sql);
        $row = $this->link->_nextRow($result);
        if($row !==FALSE) {
            $row['title'] = $row['ticevent_name'];
            $row['timeBegin '] = $row['ticevent_startdate'];
            $row['timeEnd '] = $row['ticevent_enddate'];
            $row['ticevent_desc'] = $row['detail'];
            $row['seatsxml'] = $row['seatsxml'];
            $row['logoUrl'] = $row['ticevent_logoimage'];
            //get products
            $row['listProducts'] = $this->getProducts($row['id']);
            if(count($row['listProducts'])==0){//set default
                $arrDefault = array();
                $arrDefault[]=array('tickets_id'=>$row['id'],'type'=>"Sample 1",'quantity'=>1,'price'=>20,'handicap'=>'false','color'=>0xED1C24,'enabled'=>'true');
		$arrDefault[]=array('tickets_id'=>$row['id'],'type'=>"Sample 2",'quantity'=>1,'price'=>10,'handicap'=>'false','color'=>0x00A651,'enabled'=>'true');
		$arrDefault[]=array('tickets_id'=>$row['id'],'type'=>"Handicap 1",'quantity'=>1,'price'=>5,'handicap'=>'true','color'=>0x0054A6,'enabled'=>'true');

                $seatsxml = "<seatsxml><products>";
                foreach($arrDefault as $p){
                    $p = $this->addProduct($p);
                    $seatsxml .= '<product id="'.$p['id'].'" tickets_id="'.$p['tickets_id'].'" type="'.$p['type'].'" quantity="'.$p['quantity'].'" price="'.$p['price'].'" handicap="'.$p['handicap'].'" color="'.$p['color'].'" enabled="'.$p['enabled'].'" />';
                }
                $seatsxml .= "</products></seatsxml>";
                $row['seatsxml'] = $seatsxml;
                $row['listProducts'] = $this->getProducts($row['id']);

            }
            //chartsxml
            $row['chartsxml'] = $this->getChart($row['chart_id']);

        }
        return $row;
    }

    function getProducts($tickets_id) {
        $query = "SELECT * FROM tickets_product WHERE tickets_id='".$tickets_id."' ";
        $result = $this->link->_execute($query);
        $returnArr = array();
        while($row = mysql_fetch_array($result)) {
            $row['type'] = $row['seat_name'];
            $row['price'] = $row['seat_price'];
            $row['quantity'] = $row['seat_qty'];
            $returnArr[] = $row;
        }

        return $returnArr;
    }

    function getChart($id){
        $query = "SELECT chartsxml FROM ticket_seatcharts WHERE id='".$id."' ";
        $result = $this->link->_execute($query);
        $row = $this->link->_nextRow($result);
        if($row) return $row['chartsxml'];
        return '';
    }

    function getProductsAvailableByTicketId($tickets_id) {
        $query = "SELECT * FROM tickets_product WHERE tickets_id='".$tickets_id."' AND enabled='1' ";
        $result = $this->link->_execute($query);
        $returnArr = array();
        while($row = mysql_fetch_array($result)) {
            $row['type'] = $row['seat_name'];
            $row['price'] = $row['seat_price'];
            $row['quantity'] = 0;
            $row['available'] = $this->remainingTicket($row['id'], $tickets_id);
            $returnArr[] = $row;
        }

        return $returnArr;
    }


    function saveEvent($e) {
        $query = "UPDATE tickets SET seatsxml='".$e['seatsxml']."', chart_id='".$e['chart_id']."', ticevent_seat_chart='".$e['bgImage']."' WHERE id='".$e['id']."' ";
        $this->link->_execute($query);
        //update again products
        if(isset($e['products']) && is_array($e['products'])){
            foreach($e['products'] as $p){
                $this->updateProduct($p);
            }
        }
        //update seatsxml for children
        $chartsxml = $e['seatsxml'];
        $sql = "SELECT id FROM tickets WHERE parent_id='".$e['id']."'";
        $result = $this->link->_execute($sql);
        $listid = array();
        while($row=$this->link->_nextRow($result)){
            $listid[] = $row['id'];
        }
        if(count($listid)>0){
            //get list product $chartsxml
            $xml = simplexml_load_string($chartsxml);
            $xmlarray = get_object_vars($xml);
            $listp = array();
            if(isset($xmlarray['products'])){
                foreach ($xmlarray['products'] as $p) {
                    $p =get_object_vars($p);
                    $p = $p['@attributes'];
                    $listp[]=$p;
                }
            }

            foreach($listid as $tickets_id){
                $chartsxml_new = $chartsxml;
                foreach($listp as $p){                    
                    //check p exists
                    $sql = "SELECT * FROM tickets_product WHERE tickets_id='".$tickets_id."' AND seat_name='".$p['type']."' ";
                    $result = $this->link->_execute($sql);
                    
                    if(!($row = $this->link->_nextRow($result))){//insert
                        $sql = "INSERT INTO tickets_product SET tickets_id='".$tickets_id."',seat_name='".$p['type']."',seat_qty='".$p['quantity']."',
                                    seat_price='".$p['price']."',enabled='".$p['enabled']."',handicap='".$p['handicap']."',color='".$p['color']."' ";
                        $this->link->_execute($sql);
                        $id_new = mysql_insert_id();
                    }else{
                        $id_new = $row['id'];
                        $sql = "UPDATE tickets_product SET seat_qty='".$p['quantity']."',
                                    seat_price='".$p['price']."',enabled='".$p['enabled']."',handicap='".$p['handicap']."',color='".$p['color']."'
                                WHERE id='".$id_new."'";
                        $this->link->_execute($sql);
                    }
                    
                    $id_old = $p['id'];
                    //update id for product
                    $old = '/id="'.$id_old.'"/';
                    $new = 'id="'.$id_new.'"';
                    $chartsxml_new = preg_replace($old,$new , $chartsxml_new);

                    //update group_id for seat, shape
                    $old = '/group_id="'.$id_old.'"/';
                    $new = 'group_id="'.$id_new.'"';
                    $chartsxml_new = preg_replace($old,$new , $chartsxml_new);                    
                }

                $query = "UPDATE tickets SET seatsxml='".$chartsxml_new."', chart_id='".$e['chart_id']."', ticevent_seat_chart='".$e['bgImage']."' WHERE id='".$tickets_id."' ";
                $this->link->_execute($query);
            }
        }
    }

    function saveImage($imgdata,$namefile) {
        $imgdata = base64_decode($imgdata);
        //save image
        $des = "../../app/webroot/ticket_images/charts/";
        if(!file_exists($des)) {
            mkdir($des,0777);
        }
        $fp = fopen($des.$namefile, "w");
        if(!fwrite($fp, $imgdata)) {
            fclose($fp);
            return false;
        }else {
            fclose($fp);
            return "ticket_images/charts/". $namefile;
        }
    }

    function getRemainingProducts($items) {
        for($i=0;$i<count($items);$i++) {
            $items[$i]['available'] = $this->remainingTicket($items[$i]['id'], $items[$i]['tickets_id']);
        }
        return $items;
    }

    function addToCart($cart) {
        //DELETING THE UNPROCESSED TRANSACRTION FROM THE CART TABLE.
        $sql="SELECT id,transaction_id FROM ticket_carts WHERE user_id='".$cart['user_id']."' AND pay_status='0'";
        $result = $this->link->_execute($sql);

        while($row = mysql_fetch_array($result)) {
            //delete cart
            $sql = "DELETE FROM ticket_carts WHERE id='".$row['id']."'";
            $this->link->_execute($sql);
            //delete cart detail
            $sql = "DELETE FROM ticket_cart_details WHERE cart_id='".$row['id']."'";
            $this->link->_execute($sql);
            //delete transaction
            $sql = "DELETE FROM transaction_ticket WHERE id='".$row['transaction_id']."'";
            $this->link->_execute($sql);
        }

        $sql = "INSERT INTO ticket_carts SET `user_id` = '".$cart['user_id']."', `tickets_id`='".$cart['tickets_id']."', `coupon`='0', `saletax`='0', `ship`='0',
                            `total_price`='".$cart['total']."', `pay_status`='0', `ssn_id`='".session_id()."'";
        $this->link->_execute($sql);

        $cart_id = mysql_insert_id();

        foreach($cart['items'] as $item) {
            if($item['quantity']>0) {
                $sql= "INSERT INTO `ticket_cart_details` SET `cart_id`='".$cart_id."', `ticketno`='', `group_id`='".$item['id']."', `group_name`='".$item['type']."',
                                   `price`='".$item['price']."', `qty`='".$item['quantity']."',subTotal='".$item['subTotal']."', `del_method`='print', `user_id`='".$cart['user_id']."'";
                $this->link->_execute($sql);
                $cart_detail_id = mysql_insert_id();
                //insert seat_booked
                foreach($item['seats'] as $seat){
                    $this->addSeatBooked($seat, $cart_id, $cart_detail_id,$cart['tickets_id']);
                }
                if($cart['noSeat']=='true'){
                    for($i=0;$i<$item['quantity'];$i++){
                        $seat = array('section'=>'','row'=>'','number'=>'');
                        $this->addSeatBooked($seat, $cart_id, $cart_detail_id,$cart['tickets_id']);
                    }
                }
            }
        }
        return $cart_id;
    }

    function remainingTicket($product_id,$tickets_id) {
        $sql = " SELECT seat_qty FROM tickets_product tic WHERE tickets_id ='".$tickets_id."' AND id='".$product_id."' ";

        $prod= $this->link->_nextRow($this->link->_execute($sql));
        $remain = $prod['seat_qty'];
        //get the total no of tickets already sold or of transaction is in progress within the 15 min difference
        $sql="SELECT SUM(item.qty) sold FROM ticket_cart_details as item
                  RIGHT JOIN ticket_carts cart ON (item.cart_id=cart.id)
                  WHERE cart.tickets_id='".$tickets_id."' AND item.group_id='".$product_id."' AND (cart.pay_status='1' OR ( ADDTIME( cart.tran_time, '0:30:0') >=  NOW()) )
                  GROUP BY item.group_id";

        $result=$this->link->_execute($sql);
        if( ($row=$this->link->_nextRow($result)) ) {
            $remain -= $row['sold'];
        }

        return $remain;
    }

    function addSeatBooked($seat,$cart_id,$cart_detail_id,$event_id) {
        $ssn = $seat['section'].';'.$seat['row'].';'.$seat['number'];
        $sql = "INSERT INTO `ticket_seat_booked` SET `cart_id`='".$cart_id."', `cart_detail_id`='".$cart_detail_id."',
                    `event_id`='".$event_id."', `seatnumber`='".$seat['section'].$seat['row'].'-'.$seat['number']."', ssn='".$ssn."' ";
        mysql_query($sql);
    }

    function getSeatsBookedByEventId($event_id) {
        $sql = "SELECT S.seatnumber,S.event_id FROM ticket_seat_booked as S
                RIGHT JOIN ticket_carts cart ON (cart.id = S.cart_id)
                WHERE S.event_id='".$event_id."' AND (cart.pay_status='1' OR (ADDTIME( cart.tran_time, '0:30:0') >=  NOW()) )";
        return $this->link->listResult($this->link->_execute($sql));
    }

    function getCharts($user_id){
        $sql = "SELECT * FROM ticket_seatcharts WHERE user_id='".$user_id."'";
        return $this->link->listResult($this->link->_execute($sql));
    }

    function saveChart($item){
        if($item['id']==0){
            $sql = "INSERT INTO ticket_seatcharts SET user_id='".$item['user_id']."', name='".$item['name']."', chartsxml='".$item['chartsxml']."' ";
            mysql_query($sql);
            $item['id'] = mysql_insert_id();
        }else{
            $sql = "UPDATE ticket_seatcharts SET user_id='".$item['user_id']."', name='".$item['name']."', chartsxml='".$item['chartsxml']."'
                    WHERE id='".$item['id']."' ";
            mysql_query($sql);
        }
        return $item;
    }

    function copyChart($event_id,$chart_id){
        //get chart
        $sql = "SELECT chartsxml,user_id FROM ticket_seatcharts WHERE id='".$chart_id."' LIMIT 1";
        $result = $this->link->_execute($sql);
        $chart = '';
        if(($row =$this->link->_nextRow($result)) ){
            $chartsxml = $row['chartsxml'];
            //get list product $chartsxml
            $xml = simplexml_load_string($chartsxml);
            $xmlarray = get_object_vars($xml);
            $listp = array();
            if(isset($xmlarray['products'])){
                foreach ($xmlarray['products'] as $p) {
                    $p =get_object_vars($p);
                    $p = $p['@attributes'];
                    $listp[] = $p;
                }
            }
            //get list event_id
            $sql = "SELECT id FROM tickets WHERE id='".$event_id."' OR parent_id='".$event_id."'";
            $result = $this->link->_execute($sql);
            $listid = array();
            while($row=$this->link->_nextRow($result)){
                $listid[] = $row['id'];
            }

            foreach($listid as $tickets_id){
                $chartsxml_new = $chartsxml;
                 //delete product old
                $sql = "DELETE FROM tickets_product WHERE tickets_id='".$tickets_id."' ";
                $this->link->_execute($sql);

                foreach($listp as $p){
                    $id_old = $p['id'];
                    $sql = "INSERT INTO tickets_product SET tickets_id='".$tickets_id."',seat_name='".$p['type']."',seat_qty='".$p['quantity']."',
                                    seat_price='".$p['price']."',enabled='".$p['enabled']."',handicap='".$p['handicap']."',color='".$p['color']."' ";
                    $this->link->_execute($sql);
                    $id_new = mysql_insert_id();
                    //update id for product
                    $old = '/id="'.$id_old.'"/';
                    $new = 'id="'.$id_new.'"';
                    $chartsxml_new = preg_replace($old,$new , $chartsxml_new);

                    //update group_id for seat, shape
                    $old = '/group_id="'.$id_old.'"/';
                    $new = 'group_id="'.$id_new.'"';
                    $chartsxml_new = preg_replace($old,$new , $chartsxml_new);
                }
                $sql = "UPDATE tickets SET seatsxml='".$chartsxml_new."'  WHERE id='".$tickets_id."' ";
                $this->link->_execute($sql);
                if($tickets_id==$event_id){
                    //update chart
                    $chart = $chartsxml_new;
                    $sql = "UPDATE ticket_seatcharts SET chartsxml='".$chart."' WHERE id='".$chart_id."' ";
                    $this->link->_execute($sql);
                }
            }                        
        }

        return array('chart_id'=>$chart_id,'chartsxml'=>$chart);
    }
    
    function createUser($user){
	$sql = "SELECT id  FROM users WHERE username='".$user['username']."' ";
	$result=$this->link->_execute($sql);
        if( ($row=$this->link->_nextRow($result)) ) {
            return -1;
        }else{
	    $sql ="INSERT INTO users SET username='".$user['username']."',password='".$user['password']."',user_group_master_id=1,is_validate='1',user_status='1'";
	    mysql_query($sql);
	    $user_id = mysql_insert_id();
	    
	    $fname = addslashes(strtolower($user['first_name']));
            $lname = addslashes(strtolower($user['last_name']));
            $email = $user['email'];
            //$dob = date('Y-m-d',strtotime($user['dob']));
	    $sql ="INSERT INTO user_details SET user_id='".$user_id."',first_name='".$fname."',last_name='".$lname."',email='".$email."', dob='".$dob."', gender='".$user['gender']."' ";
	    mysql_query($sql);
	    return $user_id;
	}
	
    }
    
    function getUserId($user){
	$sql = "SELECT id  FROM users WHERE username='".$user['username']."' AND password='".$user['password']."' AND user_status='1' ";
	$result=$this->link->_execute($sql);
        if( ($row=$this->link->_nextRow($result)) ) {
            return $row['id'];
        }
	return -1;
    }
    
    function getListTicketProductIdOfChildren($parent_id,$seat_name){//private 
        $sql = "SELECT P.id FROM tickets_product as P
                RIGHT JOIN tickets as T ON (T.id=P.tickets_id)
                WHERE T.parent_id='".$parent_id."' AND seat_name='".$seat_name."'";
        $result = $this->link->_execute($sql);
        $listid = array();
        while($row=mysql_fetch_array($result)){
            $listid[] = $row['id'];
        }

        return $listid;
    }
}
?>