/**
* @param $employee Employee
* @param $dependent Dependent
*/
public static function addDependent($employee, $dependent)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "INSERT INTO Dependents (EID, CID, Fname, Lname, Relation, Birthday) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = Conn::get()->prepare($sql);
$stmt->execute(array($employee->getEID(), $employee->getCID(), $dependent->getFname(), $dependent->getLname(), $dependent->getRelation(), $dependent->Birthday));
}
/**
* @param $Account ClientAccount
*/
public static function updateAccount($Account)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "UPDATE ClientAccount SET Balance = ? WHERE AccountNum = ?";
$stmt = Conn::get()->prepare($sql);
$stmt->execute(array($Account->getBalance(), $Account->getAccountNum()));
}
public static function getReport()
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$conn = Conn::get();
$sql = "SELECT * FROM Exception ORDER BY TimeStamp DESC";
try {
return $conn->query($sql);
} catch (Exception $e) {
return null;
}
}
public static function getRoleList()
{
include_once "../../bossflex/DB/Conn.php";
$sql = "SELECT RoleID, Role\n FROM UserRole\n ORDER BY RoleID DESC \n LIMIT 0 , 30";
$stmt = Conn::get()->query($sql, PDO::FETCH_ASSOC);
$roleList = array();
while ($role = $stmt->fetchObject(__CLASS__)) {
$roleList[] = $role;
}
return $roleList;
}
public static function getCodeList()
{
include_once "../../bossflex/DB/Conn.php";
$sql = "SELECT Code, Reason\n FROM DisableCode";
$stmt = Conn::get()->query($sql, PDO::FETCH_ASSOC);
$codeList = array();
while ($code = $stmt->fetchObject(__CLASS__)) {
$codeList[] = $code;
}
return $codeList;
}
public static function depositAmt($AccountNum, $DepositedByUID, $Amount, $PayRollDate)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$db = Conn::get();
$sql = "INSERT INTO Deposit (AccountNum, DepositedByUID, Amount, PayRollDate) VALUES (?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute(array($AccountNum, $DepositedByUID, $Amount, $PayRollDate));
$depID = $db->lastInsertId();
$sql = "SELECT * FROM Deposit WHERE DepositID = :id";
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":id", $depID, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchObject(__CLASS__);
}
public function getNumUsers()
{
try {
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "SELECT count(UID) AS numUsers FROM User WHERE CID = :CID";
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":CID", $this->CID, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetch()["numUsers"];
} catch (Exception $e) {
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/Helpers/ErrorReport.php";
ErrorReport::send($_SERVER["SCRIPT_NAME"], $e->getMessage(), $_SERVER['REMOTE_ADDR'], true);
return -1;
}
}
/**
* @param $ticket Ticket
* @return Ledger
*/
public static function addTicket($ticket)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$db = Conn::get();
$sql = "INSERT INTO Ledger (AccountNum, TicketNum, Amount, Detail) VALUES (?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute(array($ticket->getAccountNum(), $ticket->getTicketNum(), $ticket->getAmount(), $ticket->getDescription()));
$depID = $db->lastInsertId();
$sql = "SELECT * FROM Ledger WHERE TransID = :id";
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":id", $depID, PDO::PARAM_INT);
$stmt->execute();
$trans = $stmt->fetchObject(__CLASS__);
Ledger::recordTransaction($trans);
return $trans;
}
/**
* @param $User User
* @param $Password
* @return bool
*/
public static function changePassword($User, $Password)
{
try {
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Models/User.php";
$sql = "UPDATE Auth SET Password = :Pass WHERE UID = :UID";
$cost = 10;
$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');
$salt = sprintf("\$2a\$%02d\$", $cost) . $salt;
$Password = crypt($Password, $salt);
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":UID", $User->getUID());
$stmt->bindParam(":Pass", $Password);
$stmt->execute();
return true;
} catch (Exception $e) {
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/Helpers/ErrorReport.php";
ErrorReport::send($_SERVER["SCRIPT_NAME"], $e->getMessage(), $_SERVER['REMOTE_ADDR'], true);
return false;
}
}
public static function unassignedTicketsOnDate($date)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "SELECT COUNT( * ) AS UnassignedTickets, DATE( CreateDate ) AS ForDate\nFROM Ticket\nWhere Status = 0\nAnd DATE( CreateDate ) = ?\nGROUP BY DATE( CreateDate ) \nORDER BY ForDate";
$stmt = Conn::get()->prepare($sql);
$stmt->execute(array($date));
if ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
return $result['UnassignedTickets'];
} else {
return 0;
}
}
public function regenVarString()
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Models/Auth.php";
$var = Auth::getRandomString(5);
$sql = "UPDATE User SET VarString = :VarString Where UID = :UID";
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":VarString", $var);
$stmt->bindParam(":UID", $this->UID, PDO::PARAM_INT);
$stmt->execute();
$this->VarString = $var;
}
/**
* @param $FirstName
* @param $LastName
* @return BossFlexEmployee
*/
public static function getEmployeeByName($FirstName, $LastName)
{
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "SELECT BFID, Fname, Lname, PhoneNum, LastModified\n FROM BossFlexEmployee\n WHERE Fname = :fname\n AND Lname = :lname";
$stmt = Conn::get()->prepare($sql);
$stmt->bindParam(":fname", $FirstName);
$stmt->bindParam(":lname", $LastName);
$stmt->execute();
return $stmt->fetchObject(__CLASS__);
}
public static function downloadClaimReportAsCSV($startDate, $endDate, $CID)
{
//Settings
$filename = "Claims " . str_replace("/", "-", $startDate) . " to " . str_replace("/", "-", $endDate) . ".csv";
$startDate = strtotime($startDate);
$endDate = strtotime($endDate);
$startDate = date("Y-m-d", $startDate);
$endDate = date("Y-m-d", $endDate);
$delimiter = ",";
//Query
include_once $_SERVER['DOCUMENT_ROOT'] . "/bossflex/DB/Conn.php";
$sql = "SELECT Employee.Fname, Employee.Lname, Employee.EID, Employee.AccountNum, Ledger.Amount, Ledger.Date \n FROM Employee, Ledger\n WHERE Employee.AccountNum = Ledger.AccountNum\n AND Ledger.TicketNum > 0\n AND Ledger.Date>= DATE(?)\n AND Ledger.Date<= DATE(?)\n AND Employee.CID = ?";
$stmt = Conn::get()->prepare($sql);
$stmt->execute(array($startDate, $endDate, $CID));
$array = $stmt->fetchAll(PDO::FETCH_ASSOC);
//Download
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
$f = fopen('php://output', 'w');
fputcsv($f, array("First Name", "Last Name", "EmployeeID", "Account Number", "Amount Claimed", "Date Claimed"), $delimiter);
foreach ($array as $line) {
fputcsv($f, $line, $delimiter);
}
}
