1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
|
<?php
require_once "inc/configure.php";
// Retrieve total invoice amount.
$total_ivc_price = $_GET["invoice_ttlprice"];
$remainingBalance = $_GET["remain_balance"];
// Retrieve values from query string (URL)
$ivc_nbr = $_GET["ivc_nb"];
$date = $_GET["date"];
$type = $_GET["type"];
if ($type === 'Deposit'){
$reference = $_GET["reference_deposit"];
}
if ($type === 'Payment'){
$reference = $_GET["reference_payment"];
}
if ($type === 'CreditNote'){
$reference = $_GET["reference_creditNote"];
}
$amount = $_GET["amount"];
$datetime = date("Y-m-d", strtotime(str_replace("/", "-", $date)));
$refidPass = $_GET["refidPass"];
if (in_array($_SESSION['user'], array('jmcenza', 'Linda', 'Patrick', 'ITDept'))) {
// sql query to retrieve remaining_balance from table ivc_cus
$sql_get_remaining_balance = "SELECT remaining_balance FROM ivc_cus WHERE ivc_nbr = :ivc_nbr";
$sth_get_remaining_balance = $dbh->prepare($sql_get_remaining_balance);
$sth_get_remaining_balance->bindParam(':ivc_nbr', $ivc_nbr, PDO::PARAM_STR);
$sth_get_remaining_balance->execute();
$remainingBalance = $sth_get_remaining_balance->fetchColumn();
if ($amount == 0 || $amount < 0) {
echo '<script>window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '&amount_null=true";</script>';
} else if (($remainingBalance !== null && $amount > $remainingBalance) || ($remainingBalance === null & $amount > $total_ivc_price)) {
echo '<script>window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '&amount_toolarge=true";</script>';
} else {
try {
if ($remainingBalance === null) {
$remainingAmount = $total_ivc_price;
} else {
$remainingAmount = $remainingBalance;
}
$remainingAmount -= $amount;
// If the type is "Deposit
if ($type === 'Deposit') {
// sql query to retrieve remaining_balance from table cus_deposit
$sql_get_deposit_remaining_balance = "SELECT deposit_remaining_amount FROM cus_deposit WHERE deposit_reference = :reference";
$sth_get_deposit_remaining_balance = $dbh->prepare($sql_get_deposit_remaining_balance);
$sth_get_deposit_remaining_balance->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_deposit_remaining_balance->execute();
$depositRemainingBalance = $sth_get_deposit_remaining_balance->fetchColumn();
if ($depositRemainingBalance === null || $amount > $depositRemainingBalance) {
echo '<script>window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '&amount_toolarge=true";</script>';
exit; // stops script execution if the deposit amount is insufficient
}
// Update the remaining deposit amount
$depositRemainingBalance -= $amount;
// Update the remaining deposit amount in the database
$sql_update_deposit_restant = "UPDATE cus_deposit SET deposit_remaining_amount = :deposit_remaining_amount WHERE deposit_reference = :reference";
$sth_update_deposit_restant = $dbh->prepare($sql_update_deposit_restant);
$sth_update_deposit_restant->bindParam(':deposit_remaining_amount', $depositRemainingBalance, PDO::PARAM_STR);
$sth_update_deposit_restant->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_deposit_restant->execute()) {
echo "Error updating the remaining amount of the deposit.";
exit;
}
// If the remaining deposit balance is 0, update the deposit status to "PAID".
if ($depositRemainingBalance == 0) {
$sql_update_deposit_status = "UPDATE cus_deposit SET deposit_status = 'USED' WHERE deposit_reference = :reference";
$sth_update_deposit_status = $dbh->prepare($sql_update_deposit_status);
$sth_update_deposit_status->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_deposit_status->execute()) {
echo "Error updating the deposit status to USED.";
exit;
}
}
}
// If the type is "Payment
if ($type === 'Payment') {
// sql query to retrieve remaining_balance from table cus_payments
$sql_get_fullPayment_remaining_balance = "SELECT payments_remaining_amount FROM cus_payments WHERE payments_reference = :reference";
$sth_get_fullPayment_remaining_balance = $dbh->prepare($sql_get_fullPayment_remaining_balance);
$sth_get_fullPayment_remaining_balance->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_fullPayment_remaining_balance->execute();
$fullPaymentRemainingBalance = $sth_get_fullPayment_remaining_balance->fetchColumn();
if ($fullPaymentRemainingBalance === null || $amount > $fullPaymentRemainingBalance) {
echo '<script>window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '&amount_toolarge=true";</script>';
exit; // stops script execution if the payment amount is insufficient
}
// Update the remaining payments amount
$fullPaymentRemainingBalance -= $amount;
// Update the remaining payments amount in the database
$sql_update_fullPayment_restant = "UPDATE cus_payments SET payments_remaining_amount = :fullPayment_remaining_amount WHERE payments_reference = :reference";
$sth_update_fullPayment_restant = $dbh->prepare($sql_update_fullPayment_restant);
$sth_update_fullPayment_restant->bindParam(':fullPayment_remaining_amount', $fullPaymentRemainingBalance, PDO::PARAM_STR);
$sth_update_fullPayment_restant->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_fullPayment_restant->execute()) {
echo "Error updating the remaining amount of the payment.";
exit;
}
// If the remaining deposit balance is 0, update the deposit status to "PAID".
if ($fullPaymentRemainingBalance == 0) {
$sql_update_fullPayment_status = "UPDATE cus_payments SET payments_status = 'USED' WHERE payments_reference = :reference";
$sth_update_fullPayment_status = $dbh->prepare($sql_update_fullPayment_status);
$sth_update_fullPayment_status->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_fullPayment_status->execute()) {
echo "Error updating the Payment status to USED.";
exit;
}
}
}
// If the type is "Payment
if ($type === 'CreditNote') {
// sql query to retrieve remaining_balance and ttl_amt from table cn_cus_main
$sql_get_CreditNote_remaining_balance = "SELECT remaining_amount, ttl_amt FROM cn_cus_main WHERE cn_nbr = :reference";
$sth_get_CreditNote_remaining_balance = $dbh->prepare($sql_get_CreditNote_remaining_balance);
$sth_get_CreditNote_remaining_balance->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_CreditNote_remaining_balance->execute();
$CreditNoteSqlResult = $sth_get_CreditNote_remaining_balance->fetch(PDO::FETCH_ASSOC);
$CreditNoteRemainingBalance = $CreditNoteSqlResult['remaining_amount'];
if ($CreditNoteRemainingBalance === null){
$CreditNoteRemainingBalance = $CreditNoteSqlResult['ttl_amt'];
}
if ($amount > $CreditNoteRemainingBalance) {
echo '<script>window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '&amount_toolarge=true";</script>';
exit; // stops script execution if the payment amount is insufficient
}
// Update the remaining payments amount
$CreditNoteRemainingBalance -= $amount;
// Update the remaining payments amount in the database
$sql_update_CreditNote_restant = "UPDATE cn_cus_main SET remaining_amount = :remaining_amount WHERE cn_nbr = :reference";
$sth_update_CreditNote_restant = $dbh->prepare($sql_update_CreditNote_restant);
$sth_update_CreditNote_restant->bindParam(':remaining_amount', $CreditNoteRemainingBalance, PDO::PARAM_STR);
$sth_update_CreditNote_restant->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_CreditNote_restant->execute()) {
echo "Error updating the remaining amount of the credit note.";
exit;
}
// If the remaining deposit balance is 0, update the credit note status to "PAID".
if ($CreditNoteRemainingBalance == 0) {
$sql_update_CreditNote_status = "UPDATE cn_cus_main SET status = 'USED' WHERE cn_nbr = :reference";
$sth_update_CreditNote_status = $dbh->prepare($sql_update_CreditNote_status);
$sth_update_CreditNote_status->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_CreditNote_status->execute()) {
echo "Error updating the credit note status to USED.";
exit;
}
}
}
// Insert the payment into the database.
$sql_addpayment = "INSERT INTO ivc_cus_payment (ivc_nbr, payment_date, payment_type, reference, payment_amount) VALUES (:ivc_nbr, :payment_date, :payment_type, :reference, :payment_amount)";
$sth_addpayment = $dbh->prepare($sql_addpayment);
$sth_addpayment->bindParam(':ivc_nbr', $ivc_nbr, PDO::PARAM_STR);
$sth_addpayment->bindParam(':payment_date', $datetime, PDO::PARAM_STR);
$sth_addpayment->bindParam(':payment_type', $type, PDO::PARAM_STR);
$sth_addpayment->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_addpayment->bindParam(':payment_amount', $amount, PDO::PARAM_STR);
if ($sth_addpayment->execute()) {
// Update the outstanding amount in the database.
$sql_update_restant = "UPDATE ivc_cus SET remaining_balance = :remaining_amount WHERE ivc_nbr = :ivc_nbr";
$sth_update_restant = $dbh->prepare($sql_update_restant);
$sth_update_restant->bindParam(':remaining_amount', $remainingAmount, PDO::PARAM_STR);
$sth_update_restant->bindParam(':ivc_nbr', $ivc_nbr, PDO::PARAM_STR);
if ($sth_update_restant->execute()) {
echo "The payment was added successfully.";
// Check if the remaining balance is now 0 and update the invoice status to "PAID".
if ($remainingAmount == 0) {
$sql_update_invoice_status = "UPDATE ivc_cus SET status = 'PAID' WHERE ivc_nbr = :ivc_nbr";
$sth_update_invoice_status = $dbh->prepare($sql_update_invoice_status);
$sth_update_invoice_status->bindParam(':ivc_nbr', $ivc_nbr, PDO::PARAM_STR);
if ($sth_update_invoice_status->execute()) {
echo "The invoice status was updated to PAID.";
} else {
echo "Error updating the invoice status to PAID.";
}
}
echo '<script>setTimeout(function(){ window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '"; });</script>';
} else {
echo "Error updating the outstanding amount.";
}
} else {
$errorInfo = $sth_addpayment->errorInfo();
echo "Error adding payment. Error code: " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
}
} catch (PDOException $e) {
echo "Error : " . $e->getMessage();
}
}
}else{
echo '<script>setTimeout(function(){ window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '"; });</script>';
}
?>
|