/methods.php

<?php

require_once('lib/phpass/PasswordHash.php');
require_once('./smarty/smarty_main.inc');

session_start();

$db = null;
$SUPERSECRETSALT = 'tariflovesthecolorpink';

function getDB() {
    $link = mysql_connect('127.0.0.1:3306', 'wowuser', 'opensesame!');
    if (!$link) {
            die('Could not connect: ' . mysql_error());
            }
    //echo 'Connected successfully<br />';
    mysql_select_db('wowdb', $link);
    return $link;
}

function closeDB($link) {
    mysql_close($link);
}

function sql2json($query) {
    $data_sql = mysql_query($query) or die("'';//" . mysql_error());// If an error has occurred, 
            //    make the error a js comment so that a javascript error will NOT be invoked
    $json_str = ""; //Init the JSON string.

    if($total = mysql_num_rows($data_sql)) { //See if there is anything in the query
        $json_str .= "[\n";

        $row_count = 0;    
        while($data = mysql_fetch_assoc($data_sql)) {
            if(count($data) > 1) $json_str .= "{\n";

            $count = 0;
            foreach($data as $key => $value) {
                //If it is an associative array we want it in the format of "key":"value"
                if(count($data) > 1) $json_str .= "\"$key\":\"$value\"";
                else $json_str .= "\"$value\"";

                //Make sure that the last item don't have a ',' (comma)
                $count++;
                if($count < count($data)) $json_str .= ",\n";
            }
            $row_count++;
            if(count($data) > 1) $json_str .= "}\n";

            //Make sure that the last item don't have a ',' (comma)
            if($row_count < $total) $json_str .= ",\n";
        }

        $json_str .= "]\n";
    }

    //Replace the '\n's - make it faster - but at the price of bad redability.
    $json_str = str_replace("\n","",$json_str); //Comment this out when you are debugging the script

    //Finally, output the data
    return $json_str;
}

function sql2array($query) {
    $result = mysql_query($query);
    $rows = array();
    while($r = mysql_fetch_assoc($result)) {
        $rows['result'][] = $r;
    }
    return $rows;
}

function login($username, $password) {
    $pwdHasher = new PasswordHash(8, FALSE);

    $result = null;

    $hashedPassword = $pwdHasher->HashPassword($password);

    $query = sprintf("SELECT UserID, Username, Password, isTechnician, LastLatitude, LastLongitude FROM users WHERE Username='%s'", $username, $hashedPassword);

    $result = sql2array($query);
    if (!isset($result['result'][0])) {
        return false;
    }
    $result = $result['result'][0];

    $checked = $pwdHasher->CheckPassword($password, $result['Password']);

    if ($checked === true) {
        $_SESSION['UserID'] = $result['UserID'];
        $_SESSION['isTechnician'] = $result['isTechnician'];
        $_SESSION['Latitude'] = $result['LastLatitude'];
        $_SESSION['Longitude'] = $result['LastLongitude'];
        return $result;
    } else {
        return false;
    }
}

function register($username, $password, $isTechnician) {
    $pwdHasher = new PasswordHash(8, FALSE);

    $result = null;

    $hashedPassword = $pwdHasher->HashPassword($password);

    $query = sprintf("INSERT INTO users (Username, Password, IsTechnician) VALUES('%s', '%s', '%d')", $username, $hashedPassword, $isTechnician);

    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't Register Account"));
    } else {
        $userID = mysql_insert_id();
        $_SESSION['UserID'] = $userID;
        $_SESSION['isTechnician'] = $isTechnician;
        return array('result' => array('success' => true));
    }

    $checked = $pwdHasher->CheckPassword($password, $result['Password']);

    /*if ($checked === true) {
        $_SESSION['UserID'] = $result['UserID'];
        print_r($_SESSION);
        return $result;
    } else {
        return false;
    }*/
}

function setLocation($latitude, $longitude) {
    if (isset($_SESSION['UserID'])) {
        $userID = $_SESSION['UserID'];
        $result = null;
        $query = sprintf("UPDATE users SET LastLatitude='%s', LastLongitude='%s' WHERE UserID=%d", $latitude, $longitude, $userID);
        print_r($query);

        mysql_query($query);

        $_SESSION['Latitude'] = $latitude;
        $_SESSION['Longitude'] = $longitude;

        return array('result' => array('success' => true));
    } else {
        return array('result' => array('error' => "Must Be Logged In"));
    }
    

    return getItemByID($itemID);
    //$itemID = mysql_insert_id();
    //return getItemByID($itemID);
}

function addItem($name, $description, $vendorname, $scenario, $class, $cost) {
    $result = null;
    $query = sprintf("INSERT INTO items (Name, Description, VendorName, Scenario, Class, Cost) VALUES('%s', '%s', '%s', '%s', '%s', %d)", $name, $description, $vendorname, $scenario, $class, (int) $cost);
    
    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't add Item"));
    } else {
        return array('result' => array('success' => $true));
    }
}

function editItem($itemID, $name, $description, $vendorname, $scenario, $class, $cost) {
    $result = null;
    $query = sprintf("UPDATE items SET Name='%s', Description='%s', VendorName='%s', Scenario='%s', Class='%s', Cost=%d WHERE ItemID=%d", $name, $description, $vendorname, $scenario, $class, $cost, $itemID);

    mysql_query($query);

    return getItemByID($itemID);
    //$itemID = mysql_insert_id();
    //return getItemByID($itemID);
}

function addItemImage($itemID, $itemImage, $fromTechnician) {
    $itemID = (int) $itemID;

    $query = sprintf("INSERT INTO itemImages (ItemID, ImageURL, fromTechnician) VALUES('%d', '%s', '%d')", $itemID, $itemImage, $fromTechnician);

    $result = mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't add itemImage"));
    } else {
        return array('result' => array('success' => true));
    }
}

function deleteItem($itemID) {
    $result = null;
    $query = sprintf("DELETE FROM items WHERE ItemID=%d", $itemID);

    $result = mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "No affected item"));
    } else {
        return array('result' => array('success' => $result));
    }
}

function getAllItems() {
    $result = array('result' => null);
    $query = sprintf("SELECT * FROM items");
    $result = sql2array($query);
    return $result;
}

function getAttributesByItemID($itemID) {
    $result = array('result' => null);
    $itemID = (int) $itemID;
    $query = sprintf("SELECT * FROM itemAttributes WHERE ItemID='%d' ORDER BY Score DESC", $itemID);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No itemAttributes for that ID');
    }
    return $result;
}

function getItemByID($itemID) {
    $result = array('result' => null);
    $itemID = (int) $itemID;
    //$query = sprintf("SELECT * FROM items WHERE ItemID=%d", $itemID);
    $query = sprintf("SELECT *, items.VendorName FROM items LEFT JOIN vendors ON vendors.VendorName=items.VendorName WHERE ItemID=%d", $itemID);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No item for that ID');
    }
    return $result;
}

function getImagesByItemID($itemID) {
    $result = array('result' => null);
    $itemID = (int) $itemID;
    $query = sprintf("SELECT * FROM itemImages WHERE ItemID=%d ORDER BY fromTechnician DESC", $itemID);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No images for that ID');
    }
    return $result;
}

function getItemsByVendorName($vendorName) {
    $result = array('result' => null);
    $query = sprintf("SELECT * FROM items WHERE VendorName='%s'", $vendorName);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No items for vendor');
    }
    return $result;
}

function searchItemsByName($name) {
    $result = array('result' => null);
    $name = mysql_escape_string($name);
    $query = sprintf("SELECT * FROM items WHERE Name LIKE '%%%s%%' OR Description LIKE '%%%s%%' OR VendorName LIKE '%%%s%%' OR Scenario LIKE '%%%s%%' OR Class LIKE '%%%s%%'", $name, $name, $name, $name, $name);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No Results');
    }
    return $result;
}

function searchItemsByPrice($minVal, $maxVal) {
    $result = array('result' => null);
    $name = mysql_escape_string($name);
    $query = sprintf("SELECT * FROM items WHERE Cost>='%d' AND Cost<='%d'", $minVal, $maxVal);
    $result = sql2array($query);
    if (count($result) <= 0) {
        $result = array('error' => 'No Results');
    }
    return $result;
}

function getAllVendors() {
    $result = array('result' => null);
    $query = sprintf("SELECT * FROM vendors");
    $result = sql2array($query);
    return $result;
}

function getVendorByID($vendorID) {
    $result = array('result' => null);
    $query = sprintf("SELECT * FROM vendors WHERE VendorID=%d", (int)$vendorID);
    $result = sql2array($query);
    return $result;
}

function addAttribute($itemID, $attributeName) {
    $result = null;
    $itemID = (int) $itemID;
    $query = sprintf("INSERT INTO itemAttributes (ItemID, AttributeName) VALUES('%d', '%s')", $itemID, $attributeName);
    
    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't add Attribute"));
    } else {
        return array('result' => array('success' => true));
    }
}

function upVoteAttribute($attributeID) {
    $result = null;
    $attributeID = (int) $attributeID;

    $query = sprintf("UPDATE itemAttributes SET Score=Score + 1 WHERE AttributeID=%s", $attributeID);

    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't upVote Attribute"));
    } else {
        return array('result' => array('success' => true));
    }
}

function downVoteAttribute($attributeID) {
    $result = null;
    $attributeID = (int) $attributeID;

    $query = sprintf("UPDATE itemAttributes SET Score=Score - 1 WHERE AttributeID=%s", $attributeID);

    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't downVote Attribute"));
    } else {
        return array('result' => array('success' => true));
    }
}

function deleteAttribute($attributeID) {
    $result = null;
    $attributeID = (int) $attributeID;

    $query = sprintf("DELETE FROM itemAttributes WHERE AttributeID=%s AND Score<=0", $attributeID);

    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't delete Attribute"));
    } else {
        return array('result' => array('success' => true));
    }
}

function addVendor($vendorName, $latitude, $longitude, $address, $phone, $email) {
    $result = null;
    $latitude = (float) $latitude;
    $longitude = (float) $longitude;
    $query = sprintf("INSERT INTO vendors (VendorName, Latitude, Longitude, Address, Phone, EMail) VALUES('%s', '%s', '%s', '%s', '%d', '%s')", $vendorName, $latitude, $longitude, $address, $phone, $email);
    
    mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Couldn't add Vendor"));
    } else {
        return array('result' => array('success' => true));
    }
}

function editVendor($vendorID, $vendorName, $latitude, $longitude, $address, $phone, $email) {
    $result = null;
    $vendorID = (int) $vendorID;
    $latitude = (float) $latitude;
    $longitude = (float) $longitude;
    $phone = (int) $phone;

    $query = sprintf("UPDATE vendors SET VendorName='%s', Latitude='%s', Longitude='%s', Address='%s', Phone='%d', EMail='%s' WHERE VendorID=%s", $vendorName, $latitude, $longitude, $address, $phone, $email, $vendorID);

    mysql_query($query);

    return getVendorByID($vendorID);
    //$itemID = mysql_insert_id();
    //return getItemByID($itemID);
}

function deleteVendor($vendorID) {
    $result = null;
    $vendorID = (int) $vendorID;
    $query = sprintf("DELETE FROM vendors WHERE VendorID=%d", $vendorID);

    $result = mysql_query($query);

    if (mysql_affected_rows() <= 0) {
        return array('result' => array('error' => "Unable to delete vendor"));
    } else {
        return array('result' => array('success' => $result));
    }
}

function claimVendor($vendorID) {
    $result = null;
    $vendorID = (int) $vendorID;

    if (isset($_SESSION['UserID']) && isset($_SESSION['isTechnician'])) {
        $query = sprintf("UPDATE vendors SET Owner='%d' WHERE VendorID=%s", $_SESSION['UserID'], $vendorID);
        mysql_query($query);
        if (mysql_affected_rows() <= 0) {
            return array('result' => array('error' => "Couldn't claim vendor"));
        } else {
            $email  = "richard@localhost"; 
            $title   = "User-> " . $_SESSION['UserID'] . "| Claiming Vendor-> " . $vendorID; 
            $message = "Successful Claim"; 
            mail($email, $title, $message);
            return array('result' => array('success' => true));
        }
    } else {
        return array('result' => array('error' => "Access Control: Privileged login required"));
    }
}

function disownVendor($vendorID) {
    $result = null;
    $vendorID = (int) $vendorID;

    if (isset($_SESSION['UserID']) && isset($_SESSION['isTechnician'])) {
        $query = sprintf("UPDATE vendors SET Owner='' WHERE VendorID=%s", $vendorID);
        mysql_query($query);
        if (mysql_affected_rows() <= 0) {
            return array('result' => array('error' => "Couldn't disown vendor"));
        } else {
            return array('result' => array('success' => true));
        }
    } else {
        return array('result' => array('error' => "Access Control: Privileged login required"));
    }
}

?>