Skip to content
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
Name
Date Loan Started
Redeemed Date
Amount
Interest Rate
Notes
Item 1
4/1/2023
Open
Item 2
Open
Item 3
Open
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) {
$pledge = $this->getPledgeDetails($pledge_id);
if($pledge) {
$curStatus = $pledge['pledge_status'];
$sql = "SELECT * FROM gold_pledge_cancellations WHERE pledge_id = ?";
$params = array("i",$pledge_id);
$cancel = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$cancel = $cancel[0];
} else { $cancel = false; }
if(($curStatus == 1 || ($curStatus == 3 && !$cancel)) && $payment == $expected) {
$sql = "INSERT INTO gold_pledge_cancellations (pledge_id, store_id, user_id, notify_date, cancel_date, is_cancelled, cancellation_value)
VALUES (?, ?, ?, ?, ?, ?, ?)";
$params = array("iiissid",$pledge_id,$store_id,$user_id,date('Y-m-d H:i:s'),date('Y-m-d H:i:s'),1,$payment);
$this->db->cleanQueryArr($sql,$params);
$cancel_id = $this->db->getLastinsertid();
$pledge_status = 4;
} elseif($curStatus == 3 && $payment == $expected) {
$sql = "UPDATE gold_pledge_cancellations
SET cancel_date = ?,
is_cancelled = ?,
cancellation_value = ?
WHERE pledge_id = ?
AND cancellation_id = ?";
$params = array("sidii",date('Y-m-d H:i:s'),1,$payment,$pledge_id,$cancel['cancellation_id']);
$this->db->cleanQueryArr($sql,$params);
$cancel_id = $cancel['cancellation_id'];
$pledge_status = 4;
} elseif($curStatus == 1 && $payment == 0) {
$sql = "INSERT INTO gold_pledge_cancellations (pledge_id, store_id, user_id, notify_date)
VALUES (?, ?, ?, ?)";
$params = array("iiis",$pledge_id,$store_id,$user_id,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$cancel_id = $this->db->getLastinsertid();
$pledge_status = 3;
} else {
return false;
}
$sql = "UPDATE gold_pledge
SET pledge_status = ?,
last_updated = ?
WHERE pledge_id = ?";
$params = array("isi",$pledge_status,date('Y-m-d H:i:s'),$pledge_id);
$this->db->cleanQueryArr($sql,$params);
return $cancel_id;
} else {
return false;
}
}
public function checkPledgeClosed($pledge_id) {
$sql = "SELECT pledge_item_id
FROM gold_pledge_items
WHERE item_status IN (1,2,3,5) AND pledge_id = ?";
$params = array("i",$pledge_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() == 0) {
$sql = "UPDATE gold_pledge
SET pledge_status = ?,
last_updated = ?
WHERE pledge_id = ?";
$params = array("isi",9,date('Y-m-d H:i:s'),$pledge_id);
$this->db->cleanQueryArr($sql,$params);
}
}
public function getPledgeDetails($pledge_id) {
$sql = "SELECT p.*, ps.status_name, ps.status_description, m.*, a.*,
s.store_name, s.store_address, p.date_created
FROM gold_pledge p
JOIN gold_pledge_status ps ON (ps.status_id = p.pledge_status)
JOIN members m ON (m.member_id = p.member_id)
JOIN member_addresses a ON (a.member_id = m.member_id)
JOIN stores s ON (s.store_id = p.store_id)
WHERE pledge_id = ?";
$params = array("i",$pledge_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$pledge = $res[0];
$sql = "SELECT i.*, c.category_name as cat_name, c2.category_name as sub_cat_name, s.status_name, op.total_price - op.line_discount as sale_price, op.date_created as sale_date
FROM gold_pledge_items i
JOIN gold_item_status s ON (s.status_id = i.item_status)
JOIN categories c ON (c.category_id = i.category_id)
LEFT JOIN categories c2 ON (c2.category_id = i.sub_category_id)
LEFT JOIN order_products op ON (op.product_id = i.product_id)
WHERE i.pledge_id = ?";
$params = array("i",$pledge_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$pledge['items'] = $res;
}
$sql = "SELECT *
FROM gold_pledge_affidavit
WHERE pledge_id = ?
AND affidavit_status = ?
ORDER BY affidavit_issued DESC
LIMIT 1";
$params = array("ii",$pledge_id,1);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$pledge['affidavit'] = $res[0];
}
$pledge['payment'] = 0.00;
$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.pledge_id = ?
AND p.pledge_transaction_ref = ?
ORDER BY p.active_payment, p.payment_date DESC";
$params = array("is",$pledge_id,$pledge['transaction_ref']);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$pledge['payments'] = $res;
foreach($res as $row) {
if($row['active_payment']) {
$pledge['payment'] = $pledge['payment'] + $row['payment_value'];
}
}
$pledge['payment'] = number_format($pledge['payment'], 2, '.', '');
}
return $pledge;
} else {
return false;
}
}
public function getPledgeSpecific($pledge_id, $specify) {
if($specify == 'cancel') {
$sql = "SELECT * FROM gold_pledge_cancellations WHERE pledge_id = ?";
$params = array("i",$pledge_id);
} elseif($specify == 'redeem') {
$sql = "SELECT * FROM gold_pledge_redemtions WHERE pledge_id = ?";
$params = array("i",$pledge_id);
} elseif($specify == 'renew') {
$sql = "SELECT * FROM gold_pledge_renewals WHERE pledge_id = ?";
$params = array("i",$pledge_id);
}
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res[0];
} else {
return false;
}
}
public static function getCapitalLoan($pledge_id) {
global $db;
$sql = "SELECT *
FROM gold_pledge_capital
WHERE pledge_id = ?
ORDER BY capital_id ASC";
$params = array("i",$pledge_id);
$res = $db->cleanQueryArr($sql,$params);
if($db->getLastnumrows() > 0) {
return $res;
} else {
return false;
}
}
public static function calculateCapitalInterest($pledge_id, $loan, $interest, $loanDate, $end_date = false) {
global $db;
$months = array();
$date = date('Y-m-d', strtotime($loanDate));
if($end_date) {
$nowDate = date('Y-m-d', strtotime($end_date));
} else {
$nowDate = date('Y-m-d');
}
while($date <= $nowDate) {
$months[$date] = 0;
$date = date('Y-m-d', strtotime('+ 1 month', strtotime($date)));
}
$rate = ($interest / 100);
foreach($months as $key => $month) {
$months[$key] = number_format($rate * $loan, 2, '.', '');
}
$capital = self::getCapitalLoan($pledge_id);
if($capital) {
foreach($capital as $cap) {
foreach($months as $key => $month) {
$cap_date = date('Y-m-d', strtotime($cap['capital_date']));
if($key > $cap_date) {
$months[$key] = number_format($rate * $cap['pledge_capital'], 2, '.', '');
}
}
}
}
//echo '<h3>Interest</h3><pre>'; print_r($months); echo '</pre>';
$interest = 0.00;
foreach($months as $key => $month) { $interest = $interest + $month; }
$interest = number_format($interest, 2, '.', '');
return $interest;
}
public static function calculatePledgeInterest($loan, $interest, $loanDate, $end_date = false) {
$loanDate = strtotime($loanDate);
if($end_date) {
$nowDate = strtotime($end_date);
} else {
$nowDate = strtotime(date('Y-m-d H:i:s'));
}
$months = 1;
while (($loanDate = strtotime("+1 MONTH", $loanDate)) <= $nowDate) {
$months++;
}
$months = max(1, $months);
$rate = ($interest / 100);
$interest = number_format(($rate * $loan) * $months, 2, '.', '');
return $interest;
}
public static function calculatePledgeInterestDays($loan, $interest, $loan_date) {
$dateDiff = strtotime(date('Y-m-d H:i:s')) - strtotime($loan_date);
$days = max(1, ceil(($dateDiff/(60*60*24))));
$rate = ($interest / 100);
$calDays = 31;
$daily = number_format(($loan * $rate) / $calDays, 2, '.', '');
$interest = number_format($daily * $days, 2, '.', '');
return $interest;
}
public static function calculatePledgeAPR($pledge, $interest, $term, $full_term) {
$days = 365.25;
$sum1 = 1+($full_term-$pledge)/$pledge;
$sum2 = 12/$interest;
$sum3 = pow($sum1, $sum2);
$apr = number_format(100*($sum3-1), 2, '.', '');
return $apr;
}
public static function getPledgeBalance($pledge_id, $end_date = false) {
global $db;
$balance = array('renew'=>0, 'redeem'=>0, 'contract'=>0, 'other'=>0);
$sql = "SELECT * FROM gold_pledge WHERE pledge_id = ?";
$params = array("i",$pledge_id);
$res = $db->cleanQueryArr($sql,$params);
if($db->getLastnumrows() > 0) {
$pledge = $res[0];
$balance['redeem'] = $pledge['loan_value'];
$balance['contract'] = $pledge['loan_value'];
$interest = self::calculateCapitalInterest($pledge_id, $pledge['loan_value'], $pledge['interest_rate'], $pledge['date_created'], $end_date);
$balance['renew'] = $interest;
$balance['redeem'] = $balance['redeem'] + $interest;
$balance['contract'] = $balance['contract'] + $interest;
$sql = "SELECT SUM(charge_value) as charges
FROM gold_pledge_charges
WHERE pledge_id = ?";
$params = array("i",$pledge_id);
$res = $db->cleanQueryArr($sql,$params);
$balance['redeem'] = $balance['redeem'] + $res[0]['charges'];
$balance['other'] = $res[0]['charges'];
$sql = "SELECT SUM(payment_value) as payments
FROM gold_pledge_payments
WHERE pledge_id = ?";
$params = array("i",$pledge_id);
$res = $db->cleanQueryArr($sql,$params);
$balance['redeem'] = $balance['redeem'] - $res[0]['payments'];
$balance['renew'] = $balance['renew'] - $res[0]['payments'];
$balance['other'] = $balance['other'] - $res[0]['payments'];
$balance['redeem'] = number_format(max(0, $balance['redeem']), 2, '.', '');
$balance['renew'] = number_format(max(0, $balance['renew']), 2, '.', '');
$balance['contract'] = number_format(max(0, $balance['contract']), 2, '.', '');
$balance['other'] = number_format(max(0, $balance['other']), 2, '.', '');
}
return $balance;
}
public function newPledgeCharge($pledge_id, $store_id, $user_id, $charge, $reason) {
$sql = "INSERT INTO gold_pledge_charges (pledge_id, user_id, store_id, charge_value, charge_reason, charge_date)
VALUES (?, ?, ?, ?, ?, ?)";
$params = array("iiidss",$pledge_id,$user_id,$store_id,$charge,$reason,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$charge_id = $this->db->getLastinsertid();
return $charge_id;
}
public function newPledgeAffidavit($pledge_id, $store_id, $user_id, $charge) {
$sql = "INSERT INTO gold_pledge_affidavit (pledge_id, store_id, user_id, affidavit_charge, affidavit_issued)
VALUES (?, ?, ?, ?, ?)";
$params = array("iiids",$pledge_id,$store_id,$user_id,$charge,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$affidavit_id = $this->db->getLastinsertid();
return $affidavit_id;
}
public function getPledgeCharges($pledge_id) {
$sql = "SELECT c.*, s.store_name, u.fullname
FROM gold_pledge_charges c
JOIN stores s ON (s.store_id = c.store_id)
JOIN users u ON (u.user_id = c.user_id)
WHERE c.pledge_id = ?";
$params = array("i",$pledge_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
return $res;
} else {
return false;
}
}
public static function getPledgeForfeits($store_id) {
global $db;
$sql = "SELECT p.pledge_id, p.member_id, p.transaction_ref, p.loan_value, p.interest_rate, p.date_created, p.pledge_notes,
m.member_id, CONCAT(m.title, '. ', m.first_name, ' ', m.last_name) as member_name,
i.carat, i.description, i.weight, i.gemstone, i.item_value, i.loan_value,
p.forfeit_date, p.extended_forfeit_date,
IF(length(p.extended_forfeit_date) > 1, p.extended_forfeit_date, p.forfeit_date) as active_forfeit_date
FROM gold_pledge p
JOIN gold_pledge_items i ON (i.pledge_id = p.pledge_id)
JOIN members m ON (m.member_id = p.member_id)
WHERE p.store_id = ?
AND p.pledge_status = ?
AND p.pledge_void = ?
HAVING active_forfeit_date <= ?";
$params = array("iiis",$store_id,1,0,date('Y-m-d'));
$res = $db->cleanQueryArr($sql,$params);
if($db->getLastnumrows() > 0) {
$pledges = array();
foreach($res as $row) {
if(!isset($pledges[$row['pledge_id']])) {
$pledges[$row['pledge_id']] = $row;
$pledges[$row['pledge_id']]['items'] = array();
}
$pledges[$row['pledge_id']]['items'][] = array('carat'=>$row['carat'],
'description'=>$row['description'],
'weight'=>$row['weight'],
'gemstone'=>$row['gemstone'],
'value'=>$row['item_value'],
'loan'=>$row['loan_value']);
}
return $pledges;
} else {
return false;
}
}
public static function getFailedCancellations() {
global $db;
$cancel_date = date('Y-m-d 23:59:59', strtotime('- 30 days'));
$sql = "SELECT p.pledge_id, p.member_id, p.transaction_ref, p.loan_value, p.interest_rate, p.date_created, p.forfeit_date, p.pledge_notes,
m.member_id, CONCAT(m.title, '. ', m.first_name, ' ', m.last_name) as member_name,
i.carat, i.description, i.weight, i.gemstone, i.item_value, i.loan_value
FROM gold_pledge p
JOIN gold_pledge_cancellations c ON (c.pledge_id = p.pledge_id)
JOIN gold_pledge_items i ON (i.pledge_id = p.pledge_id)
JOIN members m ON (m.member_id = p.member_id)
WHERE p.pledge_status = ?
AND c.is_cancelled = ?
AND p.pledge_void = ?
AND c.notify_date <= ?";
$params = array("iiis",3,0,0,$cancel_date);
$res = $db->cleanQueryArr($sql,$params);
if($db->getLastnumrows() > 0) {
foreach($res as $row) {
$sql = "UPDATE gold_pledge
SET pledge_status = ?,
last_updated = ?
WHERE pledge_id = ?";
$params = array("isi",1,date('Y-m-d H:i:s'),$row['pledge_id']);
$db->cleanQueryArr($sql,$params);
}
}
}
public function newPledgeTransfer($store_id, $user_id) {
$sql = "INSERT INTO gold_pledge_transfers (store_id, user_id, transfer_created) VALUES (?, ?, ?)";
$params = array("iis",$store_id,$user_id,date('Y-m-d H:i:s'));
$this->db->cleanQueryArr($sql,$params);
$transfer_id = $this->db->getLastinsertid();
return $transfer_id;
}
public function addPledgeTransferPledge($transfer_id, $pledge_id) {
$sql = "INSERT INTO gold_pledge_transfer_pledges (pledge_transfer_id, pledge_id, pledge_transfer_status) VALUES (?, ?, ?)";
$params = array("iii",$transfer_id, $pledge_id, 1);
$this->db->cleanQueryArr($sql,$params);
$sql = "UPDATE gold_pledge SET pledge_status = ?, last_updated = ? WHERE pledge_id = ?";
$params = array("isi",5,date('Y-m-d H:i:s'),$pledge_id);
$this->db->cleanQueryArr($sql,$params);
$sql = "UPDATE gold_pledge_items SET item_status = ? WHERE pledge_id = ? AND item_status = ?";
$params = array("iii",2,$pledge_id,1);
$this->db->cleanQueryArr($sql,$params);
}
public function setPledgeItemStatus($item_id, $status) {
$sql = "UPDATE gold_pledge_items SET item_status = ? WHERE pledge_item_id = ?";
$params = array("ii",$status,$item_id);
$this->db->cleanQueryArr($sql,$params);
}
public function setPledgeTransferStatus($pledge_id, $status) {
$sql = "UPDATE gold_pledge_transfer_pledges SET pledge_transfer_status = ? WHERE pledge_id = ?";
$params = array("ii",$status,$pledge_id);
$this->db->cleanQueryArr($sql,$params);
}
public function recievePledgeTransfer($transfer_id) {
$sql = "UPDATE gold_pledge_transfers
SET transfer_received = ?,
transfer_status = ?
WHERE pledge_transfer_id = ?";
$params = array("sii",date('Y-m-d H:i:s'),2,$transfer_id);
$this->db->cleanQueryArr($sql,$params);
}
public function actionItem($type, $pledge_id, $item_id, $action, $result = 0.00) {
$item_status = $this->getItemStatusID($action);
if($item_status) {
if($type == 'pledge') {
if($action == 'retail' && $result > 0) {
$this->productFromPledgeItem($pledge_id, $item_id, $result);
}
if($action == 'scrap' || $action == 'auction' || $action='sold') {
$this->savePledgeActionResult($pledge_id, $item_id, $result);
}
$this->setPledgeItemStatus($item_id, $item_status, $result);
} else {
if($action == 'retail') {
$this->productFromPurchaseItem($pledge_id, $item_id);
}
$this->setPurchaseItemStatus($item_id, $item_status);
}
}
}
public function completePledgeRetail($product_id, $price) {
$sql = "SELECT * FROM gold_pledge_items WHERE product_id = ?";
$params = array("i",$product_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$this-> actionItem('pledge', $res[0]['pledge_id'], $res[0]['pledge_item_id'], 'sold', $price);
}
$this->checkPledgeClosed($res[0]['pledge_id']);
}
public function savePledgeActionResult($pledge_id, $item_id, $result) {
$sql = "UPDATE gold_pledge_items SET action_value = ?, action_date = ? WHERE pledge_item_id = ? AND pledge_id = ?";
$params = array("dsii",$result,date('Y-m-d H:i:s'),$item_id,$pledge_id);
$this->db->cleanQueryArr($sql,$params);
}
public function productFromPledgeItem($pledge_id, $item_id, $cost) {
$sql = "SELECT i.*, p.transaction_ref
FROM gold_pledge_items i
JOIN gold_pledge p ON (p.pledge_id = i.pledge_id)
WHERE i.pledge_id = ?
AND i.pledge_item_id = ?";
$params = array("ii",$pledge_id,$item_id);
$res = $this->db->cleanQueryArr($sql,$params);
if($this->db->getLastnumrows() > 0) {
$item = $res[0];
$objProduct = new Product();
$ctName = (is_numeric($item['carat']) ? $item['carat'].'ct' : ucwords($item['carat']));
$name = $item['description'].' '.$ctName;
$ref = str_replace('-', '', $item['transaction_ref']).$item['pledge_item_id'];
$url = $objProduct->getProductURL($name, 0);
$cats = array();
$cats[] = $item['category_id'];
$cats[] = $item['sub_category_id'];
if($item['sub_sub_category_id']) {
$cats[] = $item['sub_sub_category_id'];
}
$product_id = $objProduct->newProductStep1($name, $name, $name, '', $ref, '', '', $url, 0, $cats, 0);
$stock = new Stock($product_id);
$stock->bookInStock(1, 'head office', 1, 1, $cost, 2, 0, '');
$sql = "UPDATE gold_pledge_items SET product_id = ? WHERE pledge_id = ? AND pledge_item_id = ?";
$params = array("iii",$product_id,$pledge_id,$item_id);
$this->db->cleanQueryArr($sql,$params);
return true;
} else {
exit;
return false;
}
}
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.