Skip to content
Notes
SaaS
All assumptions should be made that the system can facilitate multiple organisations, who can configure their own parameters/ settings.
All transactions are logged.
No rows in a transactions are adjusted or deleted in the Database tables. E.g A refund would not delete or alter the sale value, a negative balance as a new line would be added against the sale.
All Transactions must be ACID compliant
A Transaction ( capital T) refers to a full set of transactions with a customer e.g Pledge, Sale , Buyback, Stock Transfer etc.
Either the full Transaction completes or the full Transaction rolls back and notifies the user of the error (and logged).
All Constants must be editable in a settings table(s) - but remain the same in the Transactions
VAT Rate per Category
VAT Rate per Transaction
Interest Rate per Transaction and Value band
Once a Transaction is completed the variables are added to the Transaction table and DO NOT reference the Settings table (as the values in the Settings table may change e.g. Interest Rate and not reflect the value at the time of the Transaction.class.system.php ( PHP script, ASCII text )
The HTML file type is not supported
<?php
class System {
function __construct() {
$this->db = new sweetzDB();
}
private $db;
/* SYSTEM USER FUNCTIONS */
public function getUsers($user_id=null) {
$sql = "SELECT * FROM users WHERE display = 1 ";
if(!empty($user_id)) {
$sql.= "AND user_id = $user_id ";
}
$sql.= "ORDER BY fullname";
$res = $this->db->queryArr($sql);
return $res;
}
public function getAllUsers() {
$sql = "SELECT s.store_id, s.store_name, u.user_id, u.fullname
FROM stores s
JOIN user_stores us ON (us.store_id = s.store_id)
JOIN users u ON (u.user_id = us.user_id)
WHERE u.status = ?
GROUP BY s.store_id, u.user_id
ORDER BY s.store_name, u.fullname";
$params = array("i",1);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$users = array();
foreach($res as $row) {
if(!isset($users[$row['user_id']])) {
$users[$row['user_id']] = array('user'=>$row['fullname'], 'stores'=>array());
}
$users[$row['user_id']]['stores'][$row['store_id']] = $row['store_name'];
}
return $users;
} else {
return false;
}
}
public function getUserStores($user_id) {
$sql = "SELECT s.*
FROM user_stores u
JOIN stores s ON (u.store_id = s.store_id)
WHERE u.user_id = ?";
$params = array("i",$user_id);
$res = $this->db->cleanQueryArr($sql,$params);
$stores = array();
if($this->db->getLastnumrows() > 0) {
foreach($res as $row) {
$stores[$row['store_id']] = $row;
}
}
return $stores;
}
public function getRoles() {
$sql = "SELECT * FROM user_control u";
$res = $this->db->queryArr($sql);
return $res;
}
public function getUserRoles($user_id) {
$sql = "SELECT control_id
FROM user_roles r
WHERE user_id = $user_id";
$res = $this->db->queryArr($sql);
$roles = array();
foreach($res as $row) { array_push($roles, $row['control_id']); }
return $roles;
}
public function getUserByName($username) {
$sql = "SELECT user_id FROM users WHERE username = ?";
$params = array("s",$username);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res[0]['user_id'];
} else {
return false;
}
}
public function addUser($fullname, $email, $address, $phone, $pLimit, $gem, $username, $password, $location_id, $status, $access) {
$sql = "INSERT INTO users (username, password, email, fullname, address, phone, purchase_limit, gemstone_access, location_group_id, date_created, last_login, status, user_access)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("ssssssdiissii",$username,md5($password),$email,$fullname,$address,$phone,$pLimit,$gem,$location_id,date('Y-m-d H:i:s'),date('Y-m-d H:i:s'),$status,$access);
$this->db->cleanQueryArr($sql,$params);
return $this->db->getLastinsertid();
}
public function updateUser($user_id, $fullname, $email, $address, $phone, $pLimit, $gem, $username, $status, $access, $location_id, $password=null) {
$sql = "UPDATE users
SET fullname = ?,
location_group_id = ?,
email = ?,
address = ?,
phone = ?,
purchase_limit = ?,
gemstone_access = ?,
username = ?,";
if(!empty($password)) { $sql.= "password = ?,"; }
$sql.= " status = ?,
user_access = ?
WHERE user_id = ?";
if(!empty($password)) {
$params = array("sisssdissiii",$fullname,$location_id,$email,$address,$phone,$pLimit,$gem,$username,md5($password),$status,$access,$user_id);
} else {
$params = array("sisssdisiii",$fullname,$location_id,$email,$address,$phone,$pLimit,$gem,$username,$status,$access,$user_id);
}
$this->db->cleanQueryArr($sql,$params);
}
public function deleteUser($user_id) {
$sql = "DELETE FROM users WHERE user_id = ?";
$params = array("i",$user_id);
$this->db->cleanQueryArr($sql,$params);
}
public function setUserStores($user_id, $stores) {
$sql = "DELETE FROM user_stores WHERE user_id = ?";
$params = array("i",$user_id);
$this->db->cleanQueryArr($sql,$params);
foreach($stores as $store) {
$sql = "INSERT INTO user_stores (user_id, store_id) VALUES (?, ?)";
$params = array("ii",$user_id,$store);
$this->db->cleanQueryArr($sql,$params);
}
}
public function setUserRole($user_id, $control_id, $action) {
$sql = "DELETE FROM user_roles WHERE user_id = ? AND control_id = ?";
$params = array("ii",$user_id,$control_id);
$this->db->cleanQueryArr($sql,$params);
logDeletion($sql, $params, "user_roles");
if($action == 'add') {
$sql = "INSERT INTO user_roles (user_id, control_id) VALUES (?, ?)";
$params = array("ii",$user_id,$control_id);
$this->db->cleanQueryArr($sql,$params);
}
}
public function getStoreUsers($exclude=0) {
$sql = "SELECT * FROM users WHERE status = ? AND user_id != ? ORDER BY fullname";
$params = array("ii",1,$exclude);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res;
} else {
return false;
}
}
// Cashflow Management //
public function saveCashflow($store_id, $user_id, $type, $ref, $transaction_id, $value, $is_safe = 0, $non_cash = 0.00) {
$session = (isset($_COOKIE['PHPSESSID']) ? $_COOKIE['PHPSESSID'] : 'unknown');
$sql = "INSERT INTO store_cashflow (store_id, user_id, safe_transaction, cashflow_type, cashflow_ref, transaction_ref, cashflow_value, noncash_value, date_created, ip_address, session_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("iiisisddsss",$store_id,$user_id,$is_safe,$type,$ref,$transaction_id,$value,$non_cash,date('Y-m-d H:i:s'),$_SERVER['REMOTE_ADDR'],$session);
$this->db->cleanQueryArr($sql,$params);
}
public function getCashflowRecord($transaction_id, $transaction_ref, $cashflow_type) {
$sql = "SELECT *
FROM store_cashflow
WHERE cashflow_type = ?
AND cashflow_ref = ?
AND transaction_ref = ?
AND safe_transaction = ?";
$params = array("sisi",$cashflow_type,$transaction_id,$transaction_ref,0);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res[0];
} else {
return false;
}
}
// Transfer Management //
public function saveTransfer($store_id, $user_id, $owner_id, $type, $value, $transaction_id, $reference, $recipient, $outbound, $is_safe) {
$sql = "INSERT INTO store_transfer (store_id, user_id, transferrer_id, safe_float, transfer_type, transfer_value, transaction_ref, reference, recipient_id, outbound_destination, transfer_date)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("iiiiidssiss",$store_id,$user_id,$owner_id,$is_safe,$type,$value,$transaction_id,$reference,$recipient,$outbound,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$transfer_id = $this->db->getLastinsertid();
return $transfer_id;
}
// Expense Management //
public function saveExpense($store_id, $user_id, $type, $value, $transaction_id, $reference, $vat, $notes) {
$this->db->debug(true);
$sql = "INSERT INTO store_expense (store_id, user_id, expense_type, expense_value, transaction_ref, expense_reference, vat_receipt, expense_notes, expense_date)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("iisdssiss",$store_id,$user_id,$type,$value,$transaction_id,$reference,$vat,$notes,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$expense_id = $this->db->getLastinsertid();
$this->db->printErrors();
return $expense_id;
}
public static function diamondCalc($size, $colour, $clarity, $count) {
global $db;
$pricing = array('poor'=>'price_1',
'good'=>'price_2',
'excellent'=>'price_3');
$sql = "SELECT *
FROM gold_diamond
WHERE size_min <= ?
AND size_max >= ?";
$params = array("dd",$size,$size);
$res = $db->cleanQueryArr($sql,$params);
if($db->getLastnumrows() > 0) {
$price = 0.00;
foreach($res as $row) {
if($row['price_type'] == 'colour') {
$price = $price + $row[$pricing[$colour]];
} elseif($row['price_type'] == 'clarity') {
$price = $price + $row[$pricing[$clarity]];
}
}
$price = number_format($price * $count, 2, '.', '');
return $price;
} else {
return 0.00;
}
}
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.