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
|
<?php
// Make sure you have a PDO connection here.
require_once "inc/configure.php";
// Retrieve the paymentId from the POST request.
$ivc_nbr = $_GET["ivc_nb"];
$paymentId = $_GET["paymentId"];
$refidPass = $_GET["refidPass"];
if (in_array($_SESSION['user'], array('jmcenza', 'Linda', 'Patrick', 'ITDept'))) {
// SQL query to retrieve the value of the payment amount with the payment id
$sql_get_payment_data = "SELECT payment_amount, payment_type, reference FROM ivc_cus_payment WHERE refid = :paymentId";
$sth_get_payment_data = $dbh->prepare($sql_get_payment_data);
$sth_get_payment_data->bindParam(':paymentId', $paymentId, PDO::PARAM_INT);
$sth_get_payment_data->execute();
$paymentData = $sth_get_payment_data->fetch(PDO::FETCH_ASSOC);
if ($paymentData) {
$paymentAmount = $paymentData['payment_amount'];
$paymentType = $paymentData['payment_type'];
$reference = $paymentData['reference'];
// SQL query to retrieve payments_remaining_amount and deposit_status from the cus_deposit table
$sql_get_deposit_data = "SELECT deposit_remaining_amount, deposit_status FROM cus_deposit WHERE deposit_reference = :reference";
$sth_get_deposit_data = $dbh->prepare($sql_get_deposit_data);
$sth_get_deposit_data->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_deposit_data->execute();
$depositData = $sth_get_deposit_data->fetch(PDO::FETCH_ASSOC);
// SQL query to retrieve payments_remaining_amount and payments_status from the cus_payments table
$sql_get_fullPayment_data = "SELECT payments_remaining_amount, payments_status FROM cus_payments WHERE payments_reference = :reference";
$sth_get_fullPayment_data = $dbh->prepare($sql_get_fullPayment_data);
$sth_get_fullPayment_data->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_fullPayment_data->execute();
$fullPaymentData = $sth_get_fullPayment_data->fetch(PDO::FETCH_ASSOC);
// SQL query to retrieve payments_remaining_amount and payments_status from the cus_payments table
$sql_get_creditNote_data = "SELECT remaining_amount, status FROM cn_cus_main WHERE cn_nbr = :reference";
$sth_get_creditNote_data = $dbh->prepare($sql_get_creditNote_data);
$sth_get_creditNote_data->bindParam(':reference', $reference, PDO::PARAM_STR);
$sth_get_creditNote_data->execute();
$creditNoteData = $sth_get_creditNote_data->fetch(PDO::FETCH_ASSOC);
if ($depositData) {
$depositRemainingBalance = $depositData['deposit_remaining_amount'];
$depositStatus = $depositData['deposit_status'];
// Update the remaining deposit amount in the database
$depositRemainingBalance += $paymentAmount;
$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()) {
$errorInfo = $sth_update_deposit_restant->errorInfo();
echo "Error updating remaining deposit amount. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
// If the remaining deposit balance is no longer 0, reset the deposit status to "IN PROGRESS".
if ($depositRemainingBalance > 0 && $depositStatus === 'USED') {
$sql_update_deposit_status = "UPDATE cus_deposit SET deposit_status = 'IN PROGRESS' 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()) {
$errorInfo = $sth_update_deposit_status->errorInfo();
echo "Error updating deposit status to IN PROGRESS. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
}
}
if ($fullPaymentData) {
$fullPaymentRemainingBalance = $fullPaymentData['payments_remaining_amount'];
$fullPaymentStatus = $fullPaymentData['payments_status'];
// If the remaining deposit balance is no longer 0, reset the payment status to "IN PROGRESS".
$fullPaymentRemainingBalance += $paymentAmount;
$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()) {
$errorInfo = $sth_update_fullPayment_restant->errorInfo();
echo "Error updating remaining payment amount. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
// If the remaining deposit balance is no longer 0, reset the deposit status to "IN PROGRESS".
if ($fullPaymentRemainingBalance > 0 && $fullPaymentStatus === 'USED') {
$sql_update_fullPayment_status = "UPDATE cus_payments SET payments_status = 'IN PROGRESS' 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()) {
$errorInfo = $sth_update_fullPayment_status->errorInfo();
echo "Error updating payment status to IN PROGRESS. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
}
}
if ($creditNoteData) {
$creditNoteRemainingBalance = $creditNoteData['remaining_amount'];
$creditNoteStatus = $creditNoteData['status'];
// If the remaining deposit balance is no longer 0, reset the payment status to "IN PROGRESS".
$creditNoteRemainingBalance += $paymentAmount;
$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', $fullPaymentRemainingBalance, PDO::PARAM_STR);
$sth_update_creditNote_restant->bindParam(':reference', $reference, PDO::PARAM_STR);
if (!$sth_update_creditNote_restant->execute()) {
$errorInfo = $sth_update_creditNote_restant->errorInfo();
echo "Error updating remaining credit Note amount. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
// If the remaining deposit balance is no longer 0, reset the deposit status to "IN PROGRESS".
if ($creditNoteRemainingBalance > 0 && $creditNoteStatus === 'USED') {
$sql_update_creditNote_status = "UPDATE cn_cus_main SET status = 'NEW' 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()) {
$errorInfo = $sth_update_creditNote_status->errorInfo();
echo "Error updating credit Note status to IN PROGRESS. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
exit;
}
}
}
// Retrieve remaining invoice amount
$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();
$remainingBalance += $paymentAmount;
// Delete payment from database.
try {
$sql = "DELETE FROM ivc_cus_payment WHERE refid = :paymentId";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':paymentId', $paymentId, PDO::PARAM_INT);
if ($stmt->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', $remainingBalance, PDO::PARAM_STR);
$sth_update_restant->bindParam(':ivc_nbr', $ivc_nbr, PDO::PARAM_STR);
if ($sth_update_restant->execute()) {
echo "The payment was removed successfully.";
// Check if the remaining balance is now 0 and update the invoice status to "PAID".
if ($remainingBalance > 0) {
$sql_update_invoice_status = "UPDATE ivc_cus SET status = 'INPROGRESS' 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 INPROGRESS.";
} else {
echo "Error updating the invoice status to INPROGRESS.";
}
}
echo '<script>setTimeout(function(){ window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '"; });</script>';
} else {
$errorInfo = $sth_update_restant->errorInfo();
echo "Error updating outstanding amount. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
}
} else {
$errorInfo = $stmt->errorInfo();
echo "Error deleting payment. Error code : " . $errorInfo[1] . "<br>";
echo "Error message : " . $errorInfo[2];
}
} catch (PDOException $e) {
echo "Erreur : " . $e->getMessage();
}
} else {
echo "Payment not found with ID: $paymentId";
}
}else{
echo '<script>setTimeout(function(){ window.location.href = "finances_individual_cus1.php?refid=' . $refidPass . '"; });</script>';
}
?>
|