Acorn 3

Transactions

Pledge

A loan against a personal item or items owned by a customer. A Pledge is redeemed or forfeited.

A Pledge is redeemed if the balance of the loan (loan amount plus accrued interest) is paid by the customer in full.*

KYC:

A customer record is required including proof of address, ID Ref (e.g. Driving license/ Passport) and a photo taken by the store. Ability to capture the image of the ID is useful but not essential (ie/ optional to attach scans).

Transaction Process

Calculations:

Interest

Interest

Name

Date Loan Started

Redeemed Date

Amount

Interest Rate

Notes

Item 1

4/1/2023

Open

Item 2

Open

Item 3

Open

There are no rows in this table

⁠

Exceptions: *

If the loan is cancelled i.e. within [x] days the money owed by the customer is the loan amount only without any interest.

The loan can also be RENEWED. In this case the interest is paid up to date and the pledge renewed and begins again for a further 7 month period. A new pledge number should be given during the renewal process and a link or reference to the old one for reporting purposes.

Code References

// 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 {

return false;

}

public function renewPledge($pledge_id, $store_id, $user_id) {

$pledge = $this->getPledgeDetails($pledge_id);

if($pledge) {

$interest_value = self::calculatePledgeInterest($pledge['loan_value'], $pledge['interest_rate'], date('Y-m-d H:i:s'));

$interest = $interest_value - $pledge['payment'];

$sql = "INSERT INTO gold_pledge_renewals (pledge_id, transaction_ref, store_id, user_id, renewal_date, original_loan_date, original_loan_value, interest_on_renewal)

VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

$params = array("isiissdd",$pledge_id,$pledge['transaction_ref'],$store_id,$user_id,date('Y-m-d H:i:s'),$pledge['date_created'],$pledge['loan_value'],$interest);

$this->db->cleanQueryArr($sql,$params);

$sql = "SELECT COUNT(renewal_id) as renewals FROM gold_pledge_renewals WHERE pledge_id = ?";

$params = array("i",$pledge_id);

$res = $this->db->cleanQueryArr($sql,$params);

$renCount = $res[0]['renewals'] + 1;

$transRef = explode('-', $pledge['transaction_ref']);

$newTransRef = $transRef[0].'-'.$transRef[1].'-'.$transRef[2].'-'.$renCount;

$sql = "UPDATE gold_pledge

SET transaction_ref = ?,

date_created = ?,

forfeit_date = ?,

last_updated = ?,

pledge_status = ?

WHERE pledge_id = ?";

$params = array("ssssii",$newTransRef,date('Y-m-d H:i:s'),date('Y-m-d', strtotime('+ '.PLEDGE_MONTHS.' months', strtotime('- 1 day'))),date('Y-m-d H:i:s'),1,$pledge_id);

$this->db->cleanQueryArr($sql,$params);

$sql = "UPDATE store_cashflow

SET transaction_ref = ?

WHERE cashflow_type = ?

AND cashflow_ref = ?";

$params = array("ssi",$newTransRef,'pledge',$pledge_id);

$this->db->cleanQueryArr($sql,$params);

$sql = "UPDATE gold_pledge_payments SET active_payment = ? WHERE pledge_id = ? AND active_payment = ?";

$params = array("iii",0,$pledge_id,1);

$this->db->cleanQueryArr($sql,$params);

return $pledge_id;

} else {

return false;

}

public function cancelPledge($pledge_id, $store_id, $user_id, $payment, $expected) {

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.