// Pledge Management //
public function newPledge($store_id, $user_id, $manager_id, $member_id, $items, $loan, $interest, $notes) {
$trans_ref = Sweetz::newTransactionID($store_id, 'pledge');
$interest_value = self::calculatePledgeInterest($loan, $interest, date('Y-m-d H:i:s'));
$forfeit = date('Y-m-d', strtotime('+'.PLEDGE_MONTHS.' months', strtotime('- 1 day')));
$sql = "INSERT INTO gold_pledge (store_id, user_id, manager_id, member_id, transaction_ref, loan_value, interest_rate, interest_value,
date_created, loan_months, forfeit_date, last_updated, pledge_notes)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("iiiisdddsisss",$store_id,$user_id,$manager_id,$member_id,$trans_ref,$loan,$interest,$interest_value,date('Y-m-d H:i:s'),PLEDGE_MONTHS,$forfeit,date('Y-m-d H:i:s'),$notes);
$this->db->cleanQueryArr($sql,$params);
$pledge_id = $this->db->getLastinsertid();
foreach($items as $item) {
$sql = "INSERT INTO gold_pledge_items (pledge_id, category_id, sub_category_id, sub_sub_category_id, carat, description, weight,
gemstone, item_value, loan_value, date_created)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("iiiissdddds",$pledge_id,$item['cat'],$item['sub_cat'],$item['sub_sub_cat'],$item['metal'],$item['desc'],$item['weight'],$item['gem'],$item['value'],$item['loan'],date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
}
$change = - $loan;
$this->saveCashflow($store_id, $user_id, 'pledge', $pledge_id, $trans_ref, $change);
$this->updateUserFloat($user_id, $store_id, $change);
return $pledge_id;
}
public function newPledgePayment($pledge_id, $store_id, $user_id, $pay_for, $payment, $type, $reference) {
$pledge = $this->getPledgeDetails($pledge_id);
$trans_ref = Sweetz::newTransactionID($store_id, 'payment');
$sql = "INSERT INTO gold_pledge_payments (pledge_id, transaction_ref, pledge_transaction_ref, store_id, user_id, payment_for, payment_value, payment_type,
payment_reference, payment_date)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array("issiisdsss",$pledge_id,$trans_ref,$pledge['transaction_ref'],$store_id,$user_id,$pay_for,$payment,$type,$reference,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$payment_id = $this->db->getLastinsertid();
if($type == 'cash') {
$change = $payment;
$non_cash = 0.00;
} else {
$change = 0.00;
$non_cash = $payment;
}
$this->saveCashflow($store_id, $user_id, 'payment', $payment_id, $trans_ref, $change, 0, $non_cash);
$this->updateUserFloat($user_id, $store_id, $change);
return $payment_id;
}
public function saveCapitalPayment($pledge_id, $capital, $previous, $store_id, $user_id) {
$sql = "INSERT INTO gold_pledge_capital (pledge_id, pledge_capital, previous_capital, capital_date, store_id, user_id)
VALUES (?, ?, ?, ?, ?, ?)";
$params = array("iddsii",$pledge_id,$capital,$previous,date('Y-m-d H:i:s'),$store_id,$user_id);
$this->db->cleanQueryArr($sql,$params);
}
public function getPledgePayment($payment_id) {
$sql = "SELECT p.*, s.store_name, u.fullname
FROM gold_pledge_payments p
JOIN stores s ON (s.store_id = p.store_id)
JOIN users u ON (u.user_id = p.user_id)
WHERE p.payment_id = ?";
$params = array("i",$payment_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res[0];
} else {
return false;
}
}
public function alterPledgeDate($pledge_id, $pledge_date, $forfeit_date) {
$sql = "UPDATE gold_pledge
SET date_created = ?,
forfeit_date = ?
WHERE pledge_id = ?
LIMIT 1";
$params = array("ssi",$pledge_date,$forfeit_date,$pledge_id);
$this->db->cleanQueryArr($sql,$params);
}
public function extendForfeitDate($pledge_id, $extended_forfeit_date) {
$sql = "UPDATE gold_pledge
SET extended_forfeit_date = ?
WHERE pledge_id = ?
LIMIT 1";
$params = array("si",$extended_forfeit_date,$pledge_id);
$this->db->cleanQueryArr($sql,$params);
}
public function addPledgeNote($pledge_id, $store_id, $user_id, $label, $note) {
$sql = "INSERT INTO gold_pledge_notes (pledge_id, user_id, store_id, note_label, note, note_date)
VALUES (?, ?, ?, ?, ?, ?)";
$params = array("iiisss",$pledge_id,$user_id,$store_id,$label,$note,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
}
public function getPledgeNotes($pledge_id) {
$sql = "SELECT n.*, s.store_name, u.fullname
FROM gold_pledge_notes n
JOIN stores s ON (s.store_id = n.store_id)
JOIN users u ON (u.user_id = n.user_id)
WHERE n.pledge_id = ?
ORDER BY n.note_date DESC";
$params = array("i",$pledge_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res;
} else {
return false;
}
}
public function redeemPledge($pledge_id, $store_id, $user_id, $redemption) {
$pledge = $this->getPledgeDetails($pledge_id);
if($pledge) {
$sql = "INSERT INTO gold_pledge_redemptions (pledge_id, store_id, user_id, redemption_value, redemption_date)
VALUES (?, ?, ?, ?, ?)";
$params = array("iiids",$pledge_id,$store_id,$user_id,$redemption,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$redemption_id = $this->db->getLastinsertid();
$sql = "UPDATE gold_pledge
SET pledge_status = ?,
last_updated = ?
WHERE pledge_id = ?";
$params = array("isi",2,date('Y-m-d H:i:s'),$pledge_id);
$this->db->cleanQueryArr($sql,$params);
return $redemption_id;
} else {