function receiptContents($guid, $connection2, $gibbonFinanceInvoiceID, $gibbonSchoolYearID, $currency = "", $email = FALSE, $receiptNumber)
{
$return = "";
//Get currency
$currency = getSettingByScope($connection2, "System", "currency");
try {
$data = array("gibbonSchoolYearID" => $gibbonSchoolYearID, "gibbonSchoolYearID2" => $gibbonSchoolYearID, "gibbonFinanceInvoiceID" => $gibbonFinanceInvoiceID);
$sql = "SELECT gibbonPerson.gibbonPersonID, studentID, surname, preferredName, gibbonFinanceInvoice.*, companyContact, companyName, companyAddress, gibbonRollGroup.name AS rollgroup FROM gibbonFinanceInvoice JOIN gibbonFinanceInvoicee ON (gibbonFinanceInvoice.gibbonFinanceInvoiceeID=gibbonFinanceInvoicee.gibbonFinanceInvoiceeID) JOIN gibbonPerson ON (gibbonFinanceInvoicee.gibbonPersonID=gibbonPerson.gibbonPersonID) JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID) JOIN gibbonRollGroup ON (gibbonStudentEnrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID) WHERE gibbonStudentEnrolment.gibbonSchoolYearID=:gibbonSchoolYearID2 AND gibbonFinanceInvoice.gibbonSchoolYearID=:gibbonSchoolYearID AND gibbonFinanceInvoiceID=:gibbonFinanceInvoiceID";
$result = $connection2->prepare($sql);
$result->execute($data);
} catch (PDOException $e) {
$return = FALSE;
}
if ($result->rowCount() == 1) {
//Let's go!
$row = $result->fetch();
if ($email == TRUE) {
$return .= "<div style='width: 100%; text-align: right'>";
$return .= "<a target='_blank' href='" . $_SESSION[$guid]["absoluteURL"] . "'><img height='100px' width='400px' class='School Logo' alt='Logo' src='" . $_SESSION[$guid]["absoluteURL"] . "/" . $_SESSION[$guid]["organisationLogo"] . "'/></a>";
$return .= "</div>";
}
//Receipt Text
$receiptText = getSettingByScope($connection2, "Finance", "receiptText");
if ($receiptText != "") {
$return .= "<p>";
$return .= $receiptText;
$return .= "</p>";
}
$style = "";
$style2 = "";
$style3 = "";
$style4 = "";
if ($email == TRUE) {
$style = "border-top: 1px solid #333; ";
$style2 = "border-bottom: 1px solid #333; ";
$style3 = "background-color: #f0f0f0; ";
$style4 = "background-color: #f6f6f6; ";
}
//Receipt Details
$return .= "<table cellspacing='0' style='width: 100%'>";
$return .= "<tr>";
$return .= "<td style='padding-top: 15px; padding-left: 10px; vertical-align: top; {$style} {$style3}' colspan=3>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Receipt To') . " (" . $row["invoiceTo"] . ")</span><br/>";
if ($row["invoiceTo"] == "Company") {
$invoiceTo = "";
if ($row["companyContact"] != "") {
$invoiceTo .= $row["companyContact"] . ", ";
}
if ($row["companyName"] != "") {
$invoiceTo .= $row["companyName"] . ", ";
}
if ($row["companyAddress"] != "") {
$invoiceTo .= $row["companyAddress"] . ", ";
}
$return .= substr($invoiceTo, 0, -2);
} else {
try {
$dataParents = array("gibbonFinanceInvoiceeID" => $row["gibbonFinanceInvoiceeID"]);
$sqlParents = "SELECT parent.title, parent.surname, parent.preferredName, parent.email, parent.address1, parent.address1District, parent.address1Country, homeAddress, homeAddressDistrict, homeAddressCountry FROM gibbonFinanceInvoicee JOIN gibbonPerson AS student ON (gibbonFinanceInvoicee.gibbonPersonID=student.gibbonPersonID) JOIN gibbonFamilyChild ON (gibbonFamilyChild.gibbonPersonID=student.gibbonPersonID) JOIN gibbonFamily ON (gibbonFamilyChild.gibbonFamilyID=gibbonFamily.gibbonFamilyID) JOIN gibbonFamilyAdult ON (gibbonFamily.gibbonFamilyID=gibbonFamilyAdult.gibbonFamilyID) JOIN gibbonPerson AS parent ON (gibbonFamilyAdult.gibbonPersonID=parent.gibbonPersonID) WHERE gibbonFinanceInvoiceeID=:gibbonFinanceInvoiceeID AND (contactPriority=1 OR (contactPriority=2 AND contactEmail='Y')) ORDER BY contactPriority, surname, preferredName";
$resultParents = $connection2->prepare($sqlParents);
$resultParents->execute($dataParents);
} catch (PDOException $e) {
$return .= "<div class='error'>" . $e->getMessage() . "</div>";
}
if ($resultParents->rowCount() < 1) {
$return .= "<div class='warning'>" . _('There are no family members available to send this receipt to.') . "</div>";
} else {
$return .= "<ul style='margin-top: 3px; margin-bottom: 3px'>";
while ($rowParents = $resultParents->fetch()) {
$return .= "<li>";
$invoiceTo = "";
$invoiceTo .= "<b>" . formatName(htmlPrep($rowParents["title"]), htmlPrep($rowParents["preferredName"]), htmlPrep($rowParents["surname"]), "Parent", false) . "</b>, ";
if ($rowParents["email"] != "") {
$invoiceTo .= $rowParents["email"] . ", ";
}
if ($rowParents["address1"] != "") {
$invoiceTo .= $rowParents["address1"] . ", ";
if ($rowParents["address1District"] != "") {
$invoiceTo .= $rowParents["address1District"] . ", ";
}
if ($rowParents["address1Country"] != "") {
$invoiceTo .= $rowParents["address1Country"] . ", ";
}
} else {
$invoiceTo .= $rowParents["homeAddress"] . ", ";
if ($rowParents["homeAddressDistrict"] != "") {
$invoiceTo .= $rowParents["homeAddressDistrict"] . ", ";
}
if ($rowParents["homeAddressCountry"] != "") {
$invoiceTo .= $rowParents["homeAddressCountry"] . ", ";
}
}
$return .= substr($invoiceTo, 0, -2);
$return .= "</li>";
}
$return .= "</ul>";
}
}
$return .= "</td>";
$return .= "</tr>";
$return .= "<tr>";
$return .= "<td style='width: 33%; padding-top: 15px; padding-left: 10px; vertical-align: top; {$style} {$style4}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Fees For') . "</span><br/>";
$return .= formatName("", htmlPrep($row["preferredName"]), htmlPrep($row["surname"]), "Student", true) . "<br/><span style='font-style: italic; font-size: 85%'>" . _('Roll Group') . " " . $row["rollgroup"] . "</span><br/>";
$return .= "</td>";
$return .= "<td style='width: 33%; padding-top: 15px; vertical-align: top; {$style} {$style4}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Status') . "</span><br/>";
if ($receiptNumber == NULL) {
//Old style receipt, before multiple payments
$return .= $row["status"];
} else {
$paymentFail = FALSE;
if (is_numeric($receiptNumber) == FALSE) {
$paymentFail = TRUE;
} else {
try {
$dataPayment = array("foreignTable" => "gibbonFinanceInvoice", "foreignTableID" => $gibbonFinanceInvoiceID);
$sqlPayment = "SELECT gibbonPayment.*, surname, preferredName FROM gibbonPayment JOIN gibbonPerson ON (gibbonPayment.gibbonPersonID=gibbonPerson.gibbonPersonID) WHERE foreignTable=:foreignTable AND foreignTableID=:foreignTableID LIMIT {$receiptNumber}, 1";
$resultPayment = $connection2->prepare($sqlPayment);
$resultPayment->execute($dataPayment);
} catch (PDOException $e) {
$paymentFail = TRUE;
$return .= "<div class='error'>" . $e->getMessage() . "</div>";
}
if ($resultPayment->rowCount() != 1) {
$paymentFail = TRUE;
} else {
$rowPayment = $resultPayment->fetch();
$return .= $row["status"];
if ($row["status"] == "Paid") {
$return .= " (" . $rowPayment["status"] . ")";
}
}
}
}
$return .= "</td>";
$return .= "<td style='width: 33%; padding-top: 15px; vertical-align: top; {$style} {$style4}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Schedule') . "</span><br/>";
if ($row["billingScheduleType"] == "Ad Hoc") {
$return .= _("Ad Hoc");
} else {
try {
$dataSched = array("gibbonFinanceBillingScheduleID" => $row["gibbonFinanceBillingScheduleID"]);
$sqlSched = "SELECT * FROM gibbonFinanceBillingSchedule WHERE gibbonFinanceBillingScheduleID=:gibbonFinanceBillingScheduleID";
$resultSched = $connection2->prepare($sqlSched);
$resultSched->execute($dataSched);
} catch (PDOException $e) {
$return .= "<div class='error'>" . $e->getMessage() . "</div>";
}
if ($resultSched->rowCount() == 1) {
$rowSched = $resultSched->fetch();
$return .= $rowSched["name"];
}
}
$return .= "</td>";
$return .= "</tr>";
$return .= "<tr>";
$return .= "<td style='width: 33%; padding-top: 15px; padding-left: 10px; vertical-align: top; {$style} {$style2} {$style3}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Due Date') . "</span><br/>";
$return .= dateConvertBack($guid, $row["invoiceDueDate"]);
$return .= "</td>";
$return .= "<td style='width: 33%; padding-top: 15px; vertical-align: top; {$style} {$style2} {$style3}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Date Paid') . "</span><br/>";
$return .= dateConvertBack($guid, $row["paidDate"]);
$return .= "</td>";
$return .= "<td style='width: 33%; padding-top: 15px; vertical-align: top; {$style} {$style2} {$style3}'>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Invoice Number') . "</span><br/>";
$invoiceNumber = getSettingByScope($connection2, "Finance", "invoiceNumber");
if ($invoiceNumber == "Person ID + Invoice ID") {
$return .= ltrim($row["gibbonPersonID"], "0") . "-" . ltrim($gibbonFinanceInvoiceID, "0");
} else {
if ($invoiceNumber == "Student ID + Invoice ID") {
$return .= ltrim($row["studentID"], "0") . "-" . ltrim($gibbonFinanceInvoiceID, "0");
} else {
$return .= ltrim($gibbonFinanceInvoiceID, "0");
}
}
if ($receiptNumber != NULL) {
$return .= "<br/>";
$return .= "<span style='font-size: 115%; font-weight: bold'>" . _('Receipt Number (on this invoice)') . "</span><br/>";
$return .= $receiptNumber + 1;
}
$return .= "</td>";
$return .= "</tr>";
$return .= "</table>";
//Check itemisation status
$hideItemisation = getSettingByScope($connection2, "Finance", "hideItemisation");
try {
$dataFees["gibbonFinanceInvoiceID"] = $row["gibbonFinanceInvoiceID"];
$sqlFees = "SELECT gibbonFinanceInvoiceFee.gibbonFinanceInvoiceFeeID, gibbonFinanceInvoiceFee.feeType, gibbonFinanceFeeCategory.name AS category, gibbonFinanceInvoiceFee.name AS name, gibbonFinanceInvoiceFee.fee, gibbonFinanceInvoiceFee.description AS description, NULL AS gibbonFinanceFeeID, gibbonFinanceInvoiceFee.gibbonFinanceFeeCategoryID AS gibbonFinanceFeeCategoryID, sequenceNumber FROM gibbonFinanceInvoiceFee JOIN gibbonFinanceFeeCategory ON (gibbonFinanceInvoiceFee.gibbonFinanceFeeCategoryID=gibbonFinanceFeeCategory.gibbonFinanceFeeCategoryID) WHERE gibbonFinanceInvoiceID=:gibbonFinanceInvoiceID ORDER BY sequenceNumber";
$resultFees = $connection2->prepare($sqlFees);
$resultFees->execute($dataFees);
} catch (PDOException $e) {
$return .= "<div class='error'>" . $e->getMessage() . "</div>";
}
if ($resultFees->rowCount() < 1) {
$return .= "<div class='error'>";
$return .= _("There are no records to display");
$return .= "</div>";
} else {
$feeTotal = 0;
if ($hideItemisation != "Y") {
//Do not hide itemisation
//Fee table
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Fee Table");
$return .= "</h3>";
$return .= "<table cellspacing='0' style='width: 100%; {$style4}'>";
$return .= "<tr class='head'>";
$return .= "<th style='text-align: left; padding-left: 10px'>";
$return .= _("Name");
$return .= "</th>";
$return .= "<th style='text-align: left'>";
$return .= _("Category");
$return .= "</th>";
$return .= "<th style='text-align: left'>";
$return .= _("Description");
$return .= "</th>";
$return .= "<th style='text-align: left; width: 150px'>";
$return .= _("Fee") . "<br/>";
if ($currency != "") {
$return .= "<span style='font-style: italic; font-size: 85%'>" . $currency . "</span>";
}
$return .= "</th>";
$return .= "</tr>";
$count = 0;
$rowNum = "odd";
while ($rowFees = $resultFees->fetch()) {
if ($count % 2 == 0) {
$rowNum = "even";
} else {
$rowNum = "odd";
}
$count++;
$return .= "<tr style='height: 25px' class={$rowNum}>";
$return .= "<td style='padding-left: 10px'>";
$return .= $rowFees["name"];
$return .= "</td>";
$return .= "<td>";
$return .= $rowFees["category"];
$return .= "</td>";
$return .= "<td>";
$return .= $rowFees["description"];
$return .= "</td>";
$return .= "<td>";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= number_format($rowFees["fee"], 2, ".", ",");
$feeTotal += $rowFees["fee"];
$return .= "</td>";
$return .= "</tr>";
}
$return .= "<tr style='height: 35px' class='current'>";
$return .= "<td colspan=3 style='text-align: right; {$style2}'>";
$return .= "<b>" . _("Invoice Total:") . "</b>";
$return .= "</td>";
$return .= "<td style='{$style2}'>";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($feeTotal, 2, ".", ",") . "</b>";
$return .= "</td>";
$return .= "</tr>";
$return .= "</table>";
} else {
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Amount Due");
$return .= "</h3>";
while ($rowFees = $resultFees->fetch()) {
$feeTotal += $rowFees["fee"];
}
$return .= "<p style='margin-top: 10px; text-align: right'>";
$return .= _('Invoice Total') . ": ";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($feeTotal, 2, ".", ",") . "</b>";
$return .= "</p>";
}
}
//Payment details
if ($receiptNumber == NULL) {
//Old style receipt, before multiple payments
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Payment Details");
$return .= "</h3>";
$return .= "<p style='margin-top: 10px; text-align: right'>";
$return .= _('Payment Total') . ": ";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($row["paidAmount"], 2, ".", ",") . "</b>";
$return .= "</p>";
} else {
//New style receipt, post multiple payments
if ($hideItemisation != "Y") {
//Do not hide itemisation
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Payment Details");
$return .= "</h3>";
if ($paymentFail) {
$return .= "<div class='error'>";
$return .= _("There are no records to display.");
$return .= "</div>";
} else {
$return .= getPaymentLog($connection2, $guid, "gibbonFinanceInvoice", $gibbonFinanceInvoiceID, $rowPayment["gibbonPaymentID"]);
}
} else {
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Amount Paid");
$return .= "</h3>";
if ($paymentFail) {
$return .= "<div class='error'>";
$return .= _("There are no records to display.");
$return .= "</div>";
} else {
$return .= "<p style='margin-top: 10px; text-align: right'>";
$return .= _('Payment Total') . ": ";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($rowPayment["amount"], 2, ".", ",") . "</b>";
$return .= "</p>";
}
}
}
//Display balance
if ($row["status"] == "Paid" or $row["status"] == "Paid - Partial") {
if ($rowPayment["status"] == "Partial") {
if ($receiptNumber != NULL) {
//New style receipt, with multiple payments
$balanceFail = FALSE;
$amountPaid = 0;
//Get amount paid until this point
try {
$dataPayment2 = array("foreignTable" => "gibbonFinanceInvoice", "foreignTableID" => $gibbonFinanceInvoiceID);
$sqlPayment2 = "SELECT gibbonPayment.*, surname, preferredName FROM gibbonPayment JOIN gibbonPerson ON (gibbonPayment.gibbonPersonID=gibbonPerson.gibbonPersonID) WHERE foreignTable=:foreignTable AND foreignTableID=:foreignTableID ORDER BY timestamp LIMIT 0, " . ($receiptNumber + 1);
$resultPayment2 = $connection2->prepare($sqlPayment2);
$resultPayment2->execute($dataPayment2);
} catch (PDOException $e) {
$balanceFail = TRUE;
}
if ($resultPayment2->rowCount() < 1) {
$paymentFail = TRUE;
} else {
while ($rowPayment2 = $resultPayment2->fetch()) {
$amountPaid += $rowPayment2["amount"];
}
}
if ($balanceFail == FALSE) {
$return .= "<h3 style='padding-top: 40px; padding-left: 10px; margin: 0px; {$style4}'>";
$return .= _("Outstanding Balance");
$return .= "</h3>";
if ($hideItemisation != "Y") {
//Do not hide itemisation
$return .= "<table cellspacing='0' style='width: 100%; {$style4}'>";
$return .= "<tr style='height: 35px' class='current'>";
$return .= "<td style='text-align: right; {$style2}'>";
$return .= "<b>" . _("Outstanding Balance:") . "</b>";
$return .= "</td>";
$return .= "<td style='width: 135px; {$style2}'>";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($feeTotal - $amountPaid, 2, ".", ",") . "</b>";
$return .= "</td>";
$return .= "</tr>";
$return .= "</table>";
} else {
//Hide itemisation
$return .= "<p style='margin-top: 10px; text-align: right'>";
$return .= _('Payment Total') . ": ";
if (substr($currency, 4) != "") {
$return .= substr($currency, 4) . " ";
}
$return .= "<b>" . number_format($feeTotal - $amountPaid, 2, ".", ",") . "</b>";
$return .= "</p>";
}
}
}
}
}
//Receipts Notes
$receiptNotes = getSettingByScope($connection2, "Finance", "receiptNotes");
if ($receiptNotes != "") {
$return .= "<h3 style='margin-top: 40px'>";
$return .= _("Notes");
$return .= "</h3>";
$return .= "<p>";
$return .= $receiptNotes;
$return .= "</p>";
}
return $return;
}
}
print "</td>";
print "</tr>";
print "</table>";
print "</td>";
print "</tr>";
}
}
//PUT PAYMENT LOG
print "<tr class='break'>";
print "<td colspan=2>";
print "<h3>" . _('Payment Log') . "</h3>";
print "</td>";
print "</tr>";
print "<tr>";
print "<td colspan=2>";
print getPaymentLog($connection2, $guid, "gibbonFinanceInvoice", $gibbonFinanceInvoiceID);
print "</td>";
print "</tr>";
//Receipt emailing
if ($row["status"] == "Issued" or $row["status"] == "Paid - Partial") {
?>
<script type="text/javascript">
$(document).ready(function(){
if ($('#status option:selected').val()!="Paid - Partial") {
$(".emailReceipt").css("display","none");
}
$("#status").change(function(){
if ($('#status option:selected').val()=="Paid" || $('#status option:selected').val()=="Paid - Partial" || $('#status option:selected').val()=="Paid - Complete") {
$(".emailReceipt").slideDown("fast", $(".emailReceipt").css("display","table-row"));
$("#emailReceipt").val('Y');
}
