Function: deleteAttachment
Inputs:
Outputs:
*/
if ($_POST['action'] == 'deleteAttachment') {
$return = ['status' => 'fail'];
if (unlink('attachments/' . $_POST['subID'])) {
$sth = $dbh->prepare('SELECT type, id, name, extension FROM attachments
WHERE attachmentID = :attachmentID');
$sth->execute([':attachmentID' => $_POST['subID']]);
$row = $sth->fetch();
$sth = $dbh->prepare('DELETE FROM attachments
WHERE attachmentID = :attachmentID');
$sth->execute([':attachmentID' => $_POST['subID']]);
$return = ['status' => 'success'];
addChange($row['type'], $row['id'], $_SESSION['employeeID'], 'D', json_encode(['subType' => 'attachment', 'name' => $row['name'], 'extension' => $row['extension']]));
}
echo json_encode($return);
}
/*
Function: customAjax
Inputs:
Outputs:
*/
if ($_POST['action'] == 'customAjax') {
$data = $_POST;
unset($data['action']);
unset($data['type']);
unset($data['id']);
$factoryItem = Factory::createItem($_POST['type']);
$return = $factoryItem->customAjax($_POST['id'], $data);
public function customAjax($id, $data)
{
global $dbh;
global $SETTINGS;
global $TYPES;
$return = ['status' => 'success'];
if ($data['subAction'] == 'list') {
//list subAction
$return['options'] = [];
if ($data['subType'] == 'preDiscount') {
//list all products and services on the order, plus an order line
$sth = $dbh->prepare('SELECT CONCAT("P", orderProductID) AS id, name, recurringID, parentRecurringID, date
FROM products, orders_products
WHERE orderID = 1 AND products.productID = orders_products.productID
UNION
SELECT CONCAT("S", orderServiceID) AS id, name, recurringID, parentRecurringID, date
FROM services, orders_services
WHERE orderID = 1 AND services.serviceID = orders_services.serviceID');
$sth->execute([':orderID' => $id]);
$return['options'][] = ['value' => 'O', 'text' => 'Order'];
while ($row = $sth->fetch()) {
if ($row['recurringID'] !== null) {
$text = 'Recurring: ' . $row['name'];
} elseif ($row['parentRecurringID'] !== null) {
$text = formatDate($row['date']) . ': ' . $row['name'];
} else {
$text = $row['name'];
}
$return['options'][] = ['value' => $row['id'], 'text' => htmlspecialchars($text, ENT_QUOTES | ENT_HTML5, 'UTF-8')];
}
} else {
$discounts = [];
if ($data['subType'] == 'discount') {
//get the list of discounts applied to the item already so we don't apply the same discount twice to the same item
$temp = [substr($data['subID'], 0, 1), substr($data['subID'], 1)];
$sth = $dbh->prepare('SELECT discountID
FROM orders_discounts
WHERE orderID = :orderID AND appliesToType = :appliesToType AND appliesToID = :appliesToID');
$sth->execute([':orderID' => $id, ':appliesToType' => $temp[0], ':appliesToID' => $temp[1]]);
while ($row = $sth->fetch()) {
$discounts[] = $row['discountID'];
}
}
$sth = $dbh->prepare('SELECT ' . $TYPES[$data['subType']]['idName'] . ', name
FROM ' . $TYPES[$data['subType']]['pluralName'] . '
WHERE active = 1');
$sth->execute();
while ($row = $sth->fetch()) {
if ($data['subType'] != 'discount' || !in_array($row[0], $discounts)) {
$return['options'][] = ['value' => $row[0], 'text' => htmlspecialchars($row[1], ENT_QUOTES | ENT_HTML5, 'UTF-8')];
}
}
}
} elseif ($data['subAction'] == 'getDefaultPrice') {
//getDefaultPrice subAction
$sth = $dbh->prepare('SELECT defaultPrice
FROM ' . $TYPES[$data['subType']]['pluralName'] . '
WHERE ' . $TYPES[$data['subType']]['idName'] . ' = :subID');
$sth->execute([':subID' => $data['subID']]);
$row = $sth->fetch();
$return['defaultPrice'] = formatNumber($row['defaultPrice']);
} elseif ($data['subAction'] == 'add') {
//add subAction
$return['status'] = 'fail';
$subType = $data['subType'];
unset($data['subAction']);
unset($data['subType']);
if ($subType == 'discount') {
//for discounts, subID contains a one letter indication of the type of item (O = order, P = product, S = service), and the unique subID
$itemType = substr($data['subID'], 0, 1);
$uniqueID = substr($data['subID'], 1);
if ($itemType == 'O') {
$itemTypeFull = 'order';
$subType = 'discountOrder';
} elseif ($itemType == 'P') {
$itemTypeFull = 'product';
$subType = 'discountProduct';
} elseif ($itemType == 'S') {
$itemTypeFull = 'service';
$subType = 'discountService';
}
if ($itemType == 'O') {
$data['subID'] = 0;
} else {
$sth = $dbh->prepare('SELECT ' . $TYPES[$itemTypeFull]['idName'] . '
FROM orders_' . $TYPES[$itemTypeFull]['pluralName'] . '
WHERE order' . $TYPES[$itemTypeFull]['formalName'] . 'ID = :uniqueID');
$sth->execute([':uniqueID' => $uniqueID]);
$row = $sth->fetch();
$data['subID'] = $row[0];
}
}
$data = cleanData('order', $subType, $data);
$return = verifyData('order', $subType, $data);
if ($return['status'] != 'fail') {
if ($subType == 'payment') {
$dateTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['date'])->getTimestamp();
$sth = $dbh->prepare('INSERT INTO orderPayments (orderID, date, paymentType, paymentAmount)
VALUES(:orderID, :date, :paymentType, :paymentAmount)');
$sth->execute([':orderID' => $id, ':date' => $dateTS, ':paymentType' => $data['paymentType'], ':paymentAmount' => $data['paymentAmount']]);
$changeData = ['subType' => 'payment', 'date' => $dateTS, 'paymentType' => $data['paymentType'], 'paymentAmount' => $data['paymentAmount']];
} elseif ($subType == 'product' || $subType == 'service') {
$sth = $dbh->prepare('SELECT quantity
FROM orders_' . $TYPES[$subType]['pluralName'] . '
WHERE orderID = :orderID AND ' . $TYPES[$subType]['idName'] . ' = :subID AND unitPrice = :unitPrice');
$sth->execute([':orderID' => $id, ':subID' => $data['subID'], ':unitPrice' => $data['unitPrice']]);
$result = $sth->fetchAll();
if (count($result) == 1 && $data['recurring'] == 'no') {
//if the product or service is already present in the expense AND we aren't doing a recurring item, add the quantity to the existing row
$totalQuantity = $data['quantity'] + $result[0]['quantity'];
$sth = $dbh->prepare('UPDATE orders_' . $TYPES[$subType]['pluralName'] . '
SET quantity = :quantity
WHERE orderID = :orderID AND ' . $TYPES[$subType]['idName'] . ' = :subID AND unitPrice = :unitPrice');
$sth->execute([':quantity' => $totalQuantity, ':orderID' => $id, ':subID' => $data['subID'], ':unitPrice' => $data['unitPrice']]);
$changeAction = 'E';
//this is technically an edit, not an add
$changeData = ['subType' => $subType, 'subID' => $data['subID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $totalQuantity];
} else {
if ($data['recurring'] == 'yes') {
$startTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['startDate'])->getTimestamp();
$endTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['endDate'])->getTimestamp();
//add the recurring item
$sth = $dbh->prepare('SELECT MAX(recurringID) AS recurringID
FROM orders_' . $TYPES[$subType]['pluralName']);
$sth->execute();
$result = $sth->fetchAll();
$recurringID = $result[0]['recurringID'] + 1;
$sth = $dbh->prepare('INSERT INTO orders_' . $TYPES[$subType]['pluralName'] . ' (orderID, ' . $TYPES[$subType]['idName'] . ', unitPrice, quantity, recurringID, dayOfMonth, startDate, endDate)
VALUES(:orderID, :subID, :unitPrice, :quantity, :recurringID, :dayOfMonth, :startDate, :endDate)');
$sth->execute([':orderID' => $id, ':subID' => $data['subID'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':recurringID' => $recurringID, ':dayOfMonth' => $data['dayOfMonth'], ':startDate' => $startTS, ':endDate' => $endTS]);
//add occasions from start date to now
$temp = new DateTime();
$temp->setTimestamp($startTS);
$patternStart = new DateTime($data['dayOfMonth'] . '-' . $temp->format('M') . '-' . $temp->format('Y'));
$interval = new DateInterval('P1M');
$now = new DateTime();
$period = new DatePeriod($patternStart, $interval, $now);
foreach ($period as $date) {
$timestamp = $date->getTimestamp();
if ($timestamp >= $startTS && $timestamp <= $endTS) {
$sth = $dbh->prepare('INSERT INTO orders_' . $TYPES[$subType]['pluralName'] . ' (orderID, ' . $TYPES[$subType]['idName'] . ', date, unitPrice, quantity, parentRecurringID)
VALUES(:orderID, :subID, :date, :unitPrice, :quantity, :parentRecurringID)');
$sth->execute([':orderID' => $id, ':subID' => $data['subID'], ':date' => $timestamp, ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':parentRecurringID' => $recurringID]);
}
}
$changeData = ['subType' => $subType, 'subID' => $data['subID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity'], 'recurring' => $data['recurring'], 'interval' => $data['interval'], 'dayOfMonth' => $data['dayOfMonth'], 'startDate' => $startTS, 'endDate' => $endTS];
} else {
//get date of order
$sth = $dbh->prepare('SELECT date
FROM orders
WHERE orderID = :orderID');
$sth->execute([':orderID' => $id]);
$row = $sth->fetch();
$sth = $dbh->prepare('INSERT INTO orders_' . $TYPES[$subType]['pluralName'] . ' (orderID, ' . $TYPES[$subType]['idName'] . ', date, unitPrice, quantity)
VALUES(:orderID, :subID, :date, :unitPrice, :quantity)');
$sth->execute([':orderID' => $id, ':subID' => $data['subID'], ':date' => $row['date'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity']]);
$changeData = ['subType' => $subType, 'subID' => $data['subID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']];
}
}
} elseif ($subType == 'discountOrder' || $subType == 'discountProduct' || $subType == 'discountService') {
//get discountType and discountAmount
$sth = $dbh->prepare('SELECT discountType, discountAmount
FROM discounts
WHERE discountID = :discountID');
$sth->execute([':discountID' => $data['discountID']]);
$row = $sth->fetch();
$discountType = $row['discountType'];
$discountAmount = $row['discountAmount'];
$sth = $dbh->prepare('INSERT INTO orders_discounts (orderID, discountID, appliesToType, appliesToID, discountType, discountAmount)
VALUES(:orderID, :discountID, :appliesToType, :appliesToID, :discountType, :discountAmount)');
$sth->execute([':orderID' => $id, ':discountID' => $data['discountID'], ':appliesToType' => $itemType, ':appliesToID' => $uniqueID, ':discountType' => $discountType, ':discountAmount' => $discountAmount]);
$changeData = ['subType' => $subType, 'subID' => $data['subID'], 'discountID' => $data['discountID']];
//determine if the appliesTo item is a recurring item, and if so, add a discount to past recurrences if they don't already have this discount
//TODO: user could possibly make a decision to apply this to past and future recurrences, or just future
if ($itemType != 'O') {
$sth = $dbh->prepare('SELECT recurringID
FROM orders_' . $TYPES[$itemTypeFull]['pluralName'] . '
WHERE order' . $TYPES[$itemTypeFull]['formalName'] . 'ID = :uniqueID');
$sth->execute([':uniqueID' => $uniqueID]);
$row = $sth->fetch();
if ($row['recurringID'] != null) {
$recurringID = $row['recurringID'];
$sth = $dbh->prepare('SELECT order' . $TYPES[$itemTypeFull]['formalName'] . 'ID AS uniqueID
FROM orders_' . $TYPES[$itemTypeFull]['pluralName'] . '
WHERE parentRecurringID = :parentRecurringID AND order' . $TYPES[$itemTypeFull]['formalName'] . 'ID NOT IN(
SELECT appliesToID
FROM orders_discounts
WHERE discountID = :discountID AND appliesToType = :appliesToType
)');
$sth->execute([':parentRecurringID' => $recurringID, ':discountID' => $data['discountID'], 'appliesToType' => $itemType]);
while ($row = $sth->fetch()) {
$sth2 = $dbh->prepare('INSERT INTO orders_discounts (orderID, discountID, appliesToType, appliesToID, discountType, discountAmount)
VALUES(:orderID, :discountID, :appliesToType, :appliesToID, :discountType, :discountAmount)');
$sth2->execute([':orderID' => $id, ':discountID' => $data['discountID'], ':appliesToType' => $itemType, ':appliesToID' => $row['uniqueID'], ':discountType' => $discountType, ':discountAmount' => $discountAmount]);
}
}
}
}
self::updateAmountDue($id);
$temp = isset($changeAction) ? $changeAction : 'A';
addChange('order', $id, $_SESSION['employeeID'], $temp, json_encode($changeData));
}
} elseif ($data['subAction'] == 'edit') {
//edit subAction
$subType = $data['subType'];
unset($data['subAction']);
unset($data['subType']);
$sth = $dbh->prepare('SELECT ' . $TYPES[$subType]['idName'] . ' AS id
FROM orders_' . $TYPES[$subType]['pluralName'] . '
WHERE order' . $TYPES[$subType]['formalName'] . 'ID = :uniqueID');
$sth->execute([':uniqueID' => $data['subID']]);
$row = $sth->fetch();
$uniqueID = $data['subID'];
$data['subID'] = $row['id'];
$data = cleanData('order', $subType, $data);
$return = verifyData('order', $subType, $data);
if ($return['status'] != 'fail') {
$sth = $dbh->prepare('UPDATE orders_' . $TYPES[$subType]['pluralName'] . '
SET unitPrice = :unitPrice, quantity = :quantity
WHERE order' . $TYPES[$subType]['formalName'] . 'ID = :uniqueID');
$sth->execute([':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':uniqueID' => $uniqueID]);
self::updateAmountDue($id);
addChange('order', $id, $_SESSION['employeeID'], 'E', json_encode(['subType' => $subType, 'subID' => $data['subID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']]));
}
} elseif ($data['subAction'] == 'delete') {
//delete subAction
if ($data['subType'] == 'payment') {
$sth = $dbh->prepare('SELECT date, paymentAmount
FROM orderPayments
WHERE paymentID = :paymentID');
$sth->execute([':paymentID' => $data['subID']]);
$row = $sth->fetch();
$sth = $dbh->prepare('DELETE FROM orderPayments
WHERE paymentID = :paymentID');
$sth->execute([':paymentID' => $data['subID']]);
$changeData = ['subType' => 'payment', 'date' => $row['date'], 'paymentAmount' => $row['paymentAmount']];
} elseif ($data['subType'] == 'product' || $data['subType'] == 'service') {
$appliesToType = $data['subType'] == 'product' ? 'P' : 'S';
$sth = $dbh->prepare('SELECT ' . $TYPES[$data['subType']]['idName'] . ' AS id, recurringID, unitPrice, quantity
FROM orders_' . $TYPES[$data['subType']]['pluralName'] . '
WHERE order' . $TYPES[$data['subType']]['formalName'] . 'ID = :uniqueID');
$sth->execute([':uniqueID' => $data['subID']]);
$row = $sth->fetch();
$recurring = $row['recurringID'] === null ? 'no' : 'yes';
//delete item discounts and children's discounts (if any)
$sth = $dbh->prepare('DELETE FROM orders_discounts
WHERE appliesToType = :appliesToType AND (appliesToID IN (
SELECT order' . $TYPES[$data['subType']]['formalName'] . 'ID
FROM orders_' . $TYPES[$data['subType']]['pluralName'] . '
WHERE parentRecurringID = :recurringID
) OR appliesToID = :appliesToID)');
$sth->execute([':appliesToType' => $appliesToType, ':recurringID' => $row['recurringID'], ':appliesToID' => $data['subID']]);
//delete item and children (if any)
$sth = $dbh->prepare('DELETE FROM orders_' . $TYPES[$data['subType']]['pluralName'] . '
WHERE order' . $TYPES[$data['subType']]['formalName'] . 'ID = :uniqueID OR parentRecurringID = :recurringID');
$sth->execute([':uniqueID' => $data['subID'], ':recurringID' => $row['recurringID']]);
$changeData = ['subType' => $data['subType'], 'subID' => $row['id'], 'unitPrice' => $row['unitPrice'], 'quantity' => $row['quantity'], 'recurring' => $recurring];
} elseif ($data['subType'] == 'discount') {
$sth = $dbh->prepare('SELECT discountID, appliesToType, appliesToID
FROM orders_discounts
WHERE orderDiscountID = :orderDiscountID');
$sth->execute([':orderDiscountID' => $data['subID']]);
$row = $sth->fetch();
if ($row['appliesToType'] == 'O') {
$subType = 'discountOrder';
} elseif ($row['appliesToType'] == 'P') {
$subType = 'discountProduct';
} elseif ($row['appliesToType'] == 'S') {
$subType = 'discountService';
}
$sth = $dbh->prepare('DELETE FROM orders_discounts
WHERE orderDiscountID = :orderDiscountID');
$sth->execute([':orderDiscountID' => $data['subID']]);
$changeData = ['subType' => $subType, 'subID' => $row['appliesToID'], 'discountID' => $row['discountID']];
}
self::updateAmountDue($id);
addChange('order', $id, $_SESSION['employeeID'], 'D', json_encode($changeData));
}
return $return;
}
public function customAjax($id, $data)
{
global $dbh;
global $SETTINGS;
$return = ['status' => 'success'];
if ($data['subAction'] == 'list') {
//list subAction
$return['products'] = generateTypeOptions('product', true);
$return['locations'] = generateTypeOptions('location', true);
} elseif ($data['subAction'] == 'add') {
//add subAction
$return['status'] = 'fail';
$subType = $data['subType'];
unset($data['subAction']);
unset($data['subType']);
$data = cleanData('expense', $subType, $data);
$return = verifyData('expense', $subType, 'add', $data);
if ($return['status'] != 'fail') {
if ($subType == 'payment') {
$dateTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['date'])->getTimestamp();
$sth = $dbh->prepare('INSERT INTO expensePayments (expenseID, date, paymentType, paymentAmount)
VALUES(:expenseID, :date, :paymentType, :paymentAmount)');
$sth->execute([':expenseID' => $id, ':date' => $dateTS, ':paymentType' => $data['paymentType'], ':paymentAmount' => $data['paymentAmount']]);
$changeData = ['subType' => 'payment', 'date' => $dateTS, 'paymentType' => $data['paymentType'], 'paymentAmount' => $data['paymentAmount']];
} elseif ($subType == 'product') {
$sth = $dbh->prepare('SELECT quantity
FROM expenses_products
WHERE expenseID = :expenseID AND productID = :productID AND locationID = :locationID AND unitPrice = :unitPrice');
$sth->execute([':expenseID' => $id, ':productID' => $data['productID'], ':locationID' => $data['locationID'], ':unitPrice' => $data['unitPrice']]);
$result = $sth->fetchAll();
if (count($result) == 1 && $data['recurring'] == 'no') {
//if the product is already present in the expense AND we aren't doing a recurring item, add the quantity to the existing row
$totalQuantity = $data['quantity'] + $result[0]['quantity'];
$sth = $dbh->prepare('UPDATE expenses_products
SET quantity = :quantity
WHERE expenseID = :expenseID AND productID = :productID AND locationID = :locationID AND unitPrice = :unitPrice');
$sth->execute([':quantity' => $totalQuantity, ':expenseID' => $id, ':productID' => $data['productID'], ':locationID' => $data['locationID'], ':unitPrice' => $data['unitPrice']]);
$changeAction = 'E';
//this is technically an edit, not an add
$changeData = ['subType' => 'product', 'productID' => $data['productID'], 'locationID' => $data['locationID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $totalQuantity];
} else {
if ($data['recurring'] == 'yes') {
$startTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['startDate'])->getTimestamp();
$endTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['endDate'])->getTimestamp();
//add the recurring item
$sth = $dbh->prepare('SELECT MAX(recurringID) AS recurringID
FROM expenses_products');
$sth->execute();
$result = $sth->fetchAll();
$recurringID = $result[0]['recurringID'] + 1;
$sth = $dbh->prepare('INSERT INTO expenses_products (expenseID, productID, locationID, unitPrice, quantity, recurringID, dayOfMonth, startDate, endDate)
VALUES(:expenseID, :productID, :locationID, :unitPrice, :quantity, :recurringID, :dayOfMonth, :startDate, :endDate)');
$sth->execute([':expenseID' => $id, ':productID' => $data['productID'], ':locationID' => $data['locationID'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':recurringID' => $recurringID, ':dayOfMonth' => $data['dayOfMonth'], ':startDate' => $startTS, ':endDate' => $endTS]);
//add occasions from start date to now
$temp = new DateTime();
$temp->setTimestamp($startTS);
$patternStart = new DateTime($data['dayOfMonth'] . '-' . $temp->format('M') . '-' . $temp->format('Y'));
$interval = new DateInterval('P1M');
$now = new DateTime();
$period = new DatePeriod($patternStart, $interval, $now);
foreach ($period as $date) {
$timestamp = $date->getTimestamp();
if ($timestamp >= $startTS && $timestamp <= $endTS) {
$sth = $dbh->prepare('INSERT INTO expenses_products (expenseID, productID, locationID, date, unitPrice, quantity, parentRecurringID)
VALUES(:expenseID, :productID, :locationID, :date, :unitPrice, :quantity, :parentRecurringID)');
$sth->execute([':expenseID' => $id, ':productID' => $data['productID'], ':locationID' => $data['locationID'], ':date' => $timestamp, ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':parentRecurringID' => $recurringID]);
}
}
$changeData = ['subType' => 'product', 'productID' => $data['productID'], 'locationID' => $data['locationID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity'], 'recurring' => $data['recurring'], 'interval' => $data['interval'], 'dayOfMonth' => $data['dayOfMonth'], 'startDate' => $startTS, 'endDate' => $endTS];
} else {
//get date of expense
$sth = $dbh->prepare('SELECT date
FROM expenses
WHERE expenseID = :expenseID');
$sth->execute([':expenseID' => $id]);
$row = $sth->fetch();
$sth = $dbh->prepare('INSERT INTO expenses_products (expenseID, productID, locationID, date, unitPrice, quantity)
VALUES(:expenseID, :productID, :locationID, :date, :unitPrice, :quantity)');
$sth->execute([':expenseID' => $id, ':productID' => $data['productID'], ':locationID' => $data['locationID'], ':date' => $row['date'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity']]);
$changeData = ['subType' => 'product', 'productID' => $data['productID'], 'locationID' => $data['locationID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']];
}
}
} elseif ($subType == 'other') {
$sth = $dbh->prepare('SELECT quantity
FROM expenseOthers
WHERE expenseID = :expenseID AND name = :name AND unitPrice = :unitPrice');
$sth->execute([':expenseID' => $id, ':name' => $data['name'], ':unitPrice' => $data['unitPrice']]);
$result = $sth->fetchAll();
if (count($result) == 1 && $data['recurring'] == 'no') {
//if the item is already present in the expense AND we aren't doing a recurring item, add the quantity to the existing row
$totalQuantity = $data['quantity'] + $result[0]['quantity'];
$sth = $dbh->prepare('UPDATE expenseOthers
SET quantity = :quantity
WHERE expenseID = :expenseID AND name = :name AND unitPrice = :unitPrice');
$sth->execute([':quantity' => $totalQuantity, ':expenseID' => $id, ':name' => $data['name'], ':unitPrice' => $data['unitPrice']]);
$changeAction = 'E';
//this is technically an edit, not an add
$changeData = ['subType' => 'other', 'name' => $data['name'], 'unitPrice' => $data['unitPrice'], 'quantity' => $totalQuantity];
} else {
if ($data['recurring'] == 'yes') {
$startTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['startDate'])->getTimestamp();
$endTS = DateTime::createFromFormat($SETTINGS['dateFormat'] . '|', $data['endDate'])->getTimestamp();
//add the recurring item
$sth = $dbh->prepare('SELECT MAX(recurringID) AS recurringID
FROM expenseOthers');
$sth->execute();
$result = $sth->fetchAll();
$recurringID = $result[0]['recurringID'] + 1;
$sth = $dbh->prepare('INSERT INTO expenseOthers (expenseID, name, unitPrice, quantity, recurringID, dayOfMonth, startDate, endDate)
VALUES(:expenseID, :name, :unitPrice, :quantity, :recurringID, :dayOfMonth, :startDate, :endDate)');
$sth->execute([':expenseID' => $id, ':name' => $data['name'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':recurringID' => $recurringID, ':dayOfMonth' => $data['dayOfMonth'], ':startDate' => $startTS, ':endDate' => $endTS]);
//add occasions from start date to now
$temp = new DateTime();
$temp->setTimestamp($startTS);
$patternStart = new DateTime($data['dayOfMonth'] . '-' . $temp->format('M') . '-' . $temp->format('Y'));
$interval = new DateInterval('P1M');
$now = new DateTime();
$period = new DatePeriod($patternStart, $interval, $now);
foreach ($period as $date) {
$timestamp = $date->getTimestamp();
if ($timestamp >= $startTS && $timestamp <= $endTS) {
$sth = $dbh->prepare('INSERT INTO expenseOthers (expenseID, name, date, unitPrice, quantity, parentRecurringID)
VALUES(:expenseID, :name, :date, :unitPrice, :quantity, :parentRecurringID)');
$sth->execute([':expenseID' => $id, ':name' => $data['name'], ':date' => $timestamp, ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':parentRecurringID' => $recurringID]);
}
}
$changeData = ['subType' => 'other', 'name' => $data['name'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity'], 'recurring' => $data['recurring'], 'interval' => $data['interval'], 'dayOfMonth' => $data['dayOfMonth'], 'startDate' => $startTS, 'endDate' => $endTS];
} else {
//get date of expense
$sth = $dbh->prepare('SELECT date
FROM expenses
WHERE expenseID = :expenseID');
$sth->execute([':expenseID' => $id]);
$row = $sth->fetch();
$sth = $dbh->prepare('INSERT INTO expenseOthers (expenseID, name, date, unitPrice, quantity)
VALUES(:expenseID, :name, :date, :unitPrice, :quantity)');
$sth->execute([':expenseID' => $id, ':name' => $data['name'], ':date' => $row['date'], ':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity']]);
$changeData = ['subType' => 'other', 'name' => $data['name'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']];
}
}
}
self::updateAmountDue($id);
$temp = isset($changeAction) ? $changeAction : 'A';
addChange('expense', $id, $_SESSION['employeeID'], $temp, json_encode($changeData));
}
} elseif ($data['subAction'] == 'edit') {
//edit subAction
$subType = $data['subType'];
unset($data['subAction']);
unset($data['subType']);
$subID = $data['subID'];
unset($data['subID']);
$data = cleanData('expense', $subType, $data);
$return = verifyData('expense', $subType, 'edit', $data);
if ($return['status'] != 'fail') {
if ($subType == 'product') {
$sth = $dbh->prepare('UPDATE expenses_products
SET unitPrice = :unitPrice, quantity = :quantity
WHERE expenseProductID = :expenseProductID');
$sth->execute([':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':expenseProductID' => $subID]);
$changeData = ['subType' => 'product', 'productID' => $data['productID'], 'locationID' => $data['locationID'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']];
} elseif ($subType == 'other') {
$sth = $dbh->prepare('UPDATE expenseOthers
SET unitPrice = :unitPrice, quantity = :quantity
WHERE expenseOtherID = :expenseOtherID');
$sth->execute([':unitPrice' => $data['unitPrice'], ':quantity' => $data['quantity'], ':expenseOtherID' => $subID]);
$changeData = ['subType' => 'other', 'name' => $data['name'], 'unitPrice' => $data['unitPrice'], 'quantity' => $data['quantity']];
}
self::updateAmountDue($id);
addChange('expense', $id, $_SESSION['employeeID'], 'E', json_encode($changeData));
}
} elseif ($data['subAction'] == 'delete') {
//delete subAction
if ($data['subType'] == 'payment') {
$sth = $dbh->prepare('SELECT date, paymentAmount FROM expensePayments
WHERE paymentID = :paymentID');
$sth->execute([':paymentID' => $data['subID']]);
$row = $sth->fetch();
$sth = $dbh->prepare('DELETE FROM expensePayments
WHERE paymentID = :paymentID');
$sth->execute([':paymentID' => $data['subID']]);
$changeData = ['subType' => 'payment', 'date' => $row['date'], 'paymentAmount' => $row['paymentAmount']];
} elseif ($data['subType'] == 'product') {
$sth = $dbh->prepare('SELECT productID, locationID, unitPrice, quantity, recurringID
FROM expenses_products
WHERE expenseProductID = :expenseProductID');
$sth->execute([':expenseProductID' => $data['subID']]);
$row = $sth->fetch();
$recurring = $row['recurringID'] === null ? 'no' : 'yes';
//delete item and children (if any)
$sth = $dbh->prepare('DELETE FROM expenses_products
WHERE expenseProductID = :expenseProductID OR parentRecurringID = :recurringID');
$sth->execute([':expenseProductID' => $data['subID'], ':recurringID' => $row['recurringID']]);
$changeData = ['subType' => 'product', 'productID' => $row['productID'], 'locationID' => $row['locationID'], 'unitPrice' => $row['unitPrice'], 'quantity' => $row['quantity'], 'recurring' => $recurring];
} elseif ($data['subType'] == 'other') {
$sth = $dbh->prepare('SELECT name, unitPrice, quantity, recurringID
FROM expenseOthers
WHERE expenseOtherID = :expenseOtherID');
$sth->execute([':expenseOtherID' => $data['subID']]);
$row = $sth->fetch();
$recurring = $row['recurringID'] === null ? 'no' : 'yes';
//delete item and children (if any)
$sth = $dbh->prepare('DELETE FROM expenseOthers
WHERE expenseOtherID = :expenseOtherID OR parentRecurringID = :recurringID');
$sth->execute([':expenseOtherID' => $data['subID'], ':recurringID' => $row['recurringID']]);
$changeData = ['subType' => 'other', 'name' => $row['name'], 'unitPrice' => $row['unitPrice'], 'quantity' => $row['quantity'], 'recurring' => $recurring];
}
self::updateAmountDue($id);
addChange('expense', $id, $_SESSION['employeeID'], 'D', json_encode($changeData));
}
return $return;
}
