function GenerateLabels(&$pdf, $mode, $bOnlyComplete = false) { if ($mode == "indiv") { $sSQL = "SELECT * FROM person_per LEFT JOIN family_fam ON person_per.per_fam_ID = family_fam.fam_ID WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ") ORDER BY per_LastName"; } else { $sSQL = "(SELECT *, 0 AS memberCount FROM person_per LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID = 0 AND per_ID in ( " . ConvertCartToString($_SESSION['aPeopleCart']) . " ))\n\t\tUNION (SELECT *, COUNT(*) AS memberCount FROM person_per LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID > 0 AND per_ID in ( " . ConvertCartToString($_SESSION['aPeopleCart']) . " ) GROUP BY per_fam_ID HAVING memberCount = 1)\n\t\tUNION (SELECT *, COUNT(*) AS memberCount FROM person_per LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID > 0 AND per_ID in ( " . ConvertCartToString($_SESSION['aPeopleCart']) . " ) GROUP BY per_fam_ID HAVING memberCount > 1)"; } $rsCartItems = RunQuery($sSQL); while ($aRow = mysql_fetch_array($rsCartItems)) { $sRowClass = AlternateRowStyle($sRowClass); if ($aRow['memberCount'] > 1 && $mode == "fam") { $sName = $aRow['fam_Name'] . " Family"; } else { $sName = FormatFullName($aRow['per_Title'], $aRow['per_FirstName'], "", $aRow['per_LastName'], $aRow['per_Suffix'], 1); } SelectWhichAddress($sAddress1, $sAddress2, $aRow['per_Address1'], $aRow['per_Address2'], $aRow['fam_Address1'], $aRow['fam_Address2'], false); $sCity = SelectWhichInfo($aRow['per_City'], $aRow['fam_City'], False); $sState = SelectWhichInfo($aRow['per_State'], $aRow['fam_State'], False); $sZip = SelectWhichInfo($aRow['per_Zip'], $aRow['fam_Zip'], False); $sAddress = $sAddress1; if ($sAddress2 != "") { $sAddress .= "\n" . $sAddress2; } if (!$bOnlyComplete || strlen($sAddress) && strlen($sCity) && strlen($sState) && strlen($sZip)) { $pdf->Add_PDF_Label(sprintf("%s\n%s\n%s, %s %s", $sName, $sAddress, $sCity, $sState, $sZip)); } } }
function ExportCartToCSV() { $sSQL = " DROP TEMPORARY TABLE IF EXISTS tmp_canvassers "; RunQuery($sSQL); // Make temporary copy of person_per table and call it tmp_canvassers $sSQL = " CREATE TEMPORARY TABLE tmp_canvassers " . " SELECT * FROM person_per "; RunQuery($sSQL); $sSQL = " SELECT lst_OptionName AS Classification, fam_Name AS Family, " . " person_per.per_LastName AS Last_Name, " . " person_per.per_FirstName AS First_Name, " . " fam_HomePhone, person_per.per_HomePhone AS per_HomePhone, " . " fam_Address1, fam_Address2, fam_City, " . " fam_State, fam_Zip, person_per.per_DateEntered AS DateEntered, " . " tmp_canvassers.per_LastName AS Cnvsr_Last_Name, " . " tmp_canvassers.per_FirstName AS Cnvsr_First_Name " . " FROM person_per " . " LEFT JOIN family_fam ON fam_ID = person_per.per_fam_ID " . " LEFT JOIN list_lst ON lst_OptionID = person_per.per_cls_ID " . " LEFT JOIN tmp_canvassers ON tmp_canvassers.per_ID = fam_Canvasser " . " WHERE person_per.per_ID " . " IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ") " . " AND lst_ID='1' " . " ORDER BY fam_Name, fam_ID, Last_Name, First_Name "; //Run the SQL $rsQueryResults = RunQuery($sSQL); $sCSVstring = ""; if (mysql_error() != "") { $sCSVstring = gettext("An error occured: ") . mysql_errno() . "--" . mysql_error(); } else { //Loop through the fields and write the header row for ($iCount = 0; $iCount < mysql_num_fields($rsQueryResults); $iCount++) { $sCSVstring .= mysql_field_name($rsQueryResults, $iCount) . ","; } $sCSVstring .= "\n"; //Loop through the recordsert while ($aRow = mysql_fetch_array($rsQueryResults)) { //Loop through the fields and write each one for ($iCount = 0; $iCount < mysql_num_fields($rsQueryResults); $iCount++) { $sCSVstring .= $aRow[$iCount] . ","; } $sCSVstring .= "\n"; } } header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=Cart-" . date("Ymd-Gis") . ".csv"); header("Content-Transfer-Encoding: binary"); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); echo $sCSVstring; exit; }
} } // See if this group has special properties. $sSQL = "SELECT * FROM groupprop_master WHERE grp_ID = " . $iGroupID . " ORDER BY prop_ID"; $rsProps = RunQuery($sSQL); $bHasProps = mysql_num_rows($rsProps) > 0; $sSQL = "SELECT * FROM person_per\n\t\t\tLEFT JOIN family_fam ON per_fam_ID = fam_ID "; if ($bHasProps) { $sSQL .= "LEFT JOIN groupprop_" . $iGroupID . " ON groupprop_" . $iGroupID . ".per_ID = person_per.per_ID "; } $sSQL .= "LEFT JOIN person2group2role_p2g2r ON p2g2r_per_ID = person_per.per_ID\n\t\t\tWHERE p2g2r_grp_ID = " . $iGroupID; if ($iRoleID > 0) { $sSQL .= " AND p2g2r_rle_ID = " . $iRoleID; } if ($bOnlyCartMembers && count($_SESSION['aPeopleCart']) > 0) { $sSQL .= " AND person_per.per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ")"; } $sSQL .= " ORDER BY per_LastName"; $rsRecords = RunQuery($sSQL); // This is used for the headings for the letter changes. // Start out with something that isn't a letter to force the first one to work // $sLastLetter = "0"; while ($aRow = mysql_fetch_array($rsRecords)) { $OutStr = ""; $pdf->sFamily = FormatFullName($aRow['per_Title'], $aRow['per_FirstName'], $aRow['per_MiddleName'], $aRow['per_LastName'], $aRow['per_Suffix'], 3); SelectWhichAddress($sAddress1, $sAddress2, $aRow['per_Address1'], $aRow['per_Address2'], $aRow['fam_Address1'], $aRow['fam_Address2'], false); $sCity = SelectWhichInfo($aRow['per_City'], $aRow['fam_City'], false); $sState = SelectWhichInfo($aRow['per_State'], $aRow['fam_State'], false); $sZip = SelectWhichInfo($aRow['per_Zip'], $aRow['fam_Zip'], false); $sHomePhone = SelectWhichInfo($aRow['per_HomePhone'], $aRow['fam_HomePhone'], false); $sWorkPhone = SelectWhichInfo($aRow['per_WorkPhone'], $aRow['fam_WorkPhone'], false);
if (strlen($sDirClassifications)) { $sClassQualifier = "AND per_cls_ID in (" . $sDirClassifications . ")"; } if (!empty($_POST["GroupID"])) { $sGroupTable = ", person2group2role_p2g2r"; $count = 0; foreach ($_POST["GroupID"] as $Grp) { $aGroups[$count++] = FilterInput($Grp, 'int'); } $sGroupsList = implode(",", $aGroups); $sWhereExt .= "AND per_ID = p2g2r_per_ID AND p2g2r_grp_ID in (" . $sGroupsList . ")"; // This is used by per-role queries to remove duplicate rows from people assigned multiple groups. $sGroupBy = " GROUP BY per_ID"; } if ($_POST['cartdir'] != null) { $sWhereExt .= "AND per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ")"; } $mysqlinfo = mysql_get_server_info(); $mysqltmp = explode(".", $mysqlinfo); $mysqlversion = $mysqltmp[0]; if (count($mysqltmp[1] > 1)) { $mysqlsubversion = $mysqltmp[1]; } else { $mysqlsubversion = 0; } if ($mysqlversion >= 4) { // This query is similar to that of the CSV export with family roll-up. // Here we want to gather all unique families, and those that are not attached to a family. $sSQL = "(SELECT *, 0 AS memberCount, per_LastName AS SortMe FROM (person_per {$sGroupTable}) LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID = 0 {$sWhereExt} {$sClassQualifier} )\n UNION (SELECT *, COUNT(*) AS memberCount, fam_Name AS SortMe FROM (person_per {$sGroupTable}) LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID > 0 {$sWhereExt} {$sClassQualifier} GROUP BY per_fam_ID HAVING memberCount = 1)\n UNION (SELECT *, COUNT(*) AS memberCount, fam_Name AS SortMe FROM (person_per {$sGroupTable}) LEFT JOIN family_fam ON per_fam_ID = fam_ID WHERE per_fam_ID > 0 {$sWhereExt} {$sClassQualifier} GROUP BY per_fam_ID HAVING memberCount > 1)\n ORDER BY SortMe"; } else { if ($mysqlversion == 3 && $mysqlsubversion >= 22) {
$appendToQuery = ""; if ($iGroupID > 0) { // If mapping only members of a group build a condition to add to the query used below //Get all the members of this group $sSQL = "SELECT per_fam_ID FROM person_per, person2group2role_p2g2r WHERE per_ID = p2g2r_per_ID AND p2g2r_grp_ID = " . $iGroupID; $rsGroupMembers = RunQuery($sSQL); $appendToQuery = " WHERE fam_ID IN ("; while ($aPerFam = mysql_fetch_array($rsGroupMembers)) { extract($aPerFam); $appendToQuery .= $per_fam_ID . ","; } $appendToQuery = substr($appendToQuery, 0, strlen($appendToQuery) - 1); $appendToQuery .= ")"; } elseif ($iGroupID > -1) { // group zero means map the cart $sSQL = "SELECT per_fam_ID FROM person_per WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ")"; $rsGroupMembers = RunQuery($sSQL); $appendToQuery = " WHERE fam_ID IN ("; while ($aPerFam = mysql_fetch_array($rsGroupMembers)) { extract($aPerFam); $appendToQuery .= $per_fam_ID . ","; } $appendToQuery = substr($appendToQuery, 0, strlen($appendToQuery) - 1); $appendToQuery .= ")"; } $sSQL = "SELECT fam_ID, per_cls_ID, fam_Name, fam_latitude, fam_longitude, fam_Address1, fam_City, fam_State, fam_Zip FROM family_fam LEFT JOIN person_per on family_fam.fam_ID = person_per.per_fam_ID AND per_fmr_ID IN ( {$sDirRoleHead} )"; $sSQL .= $appendToQuery; $rsFams = RunQuery($sSQL); $markerIcons = explode(",", $sGMapIcons); array_unshift($markerIcons, "red-pushpin"); while ($aFam = mysql_fetch_array($rsFams)) {
* * smjGPIB is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation. * ******************************************************************************/ // Include the function library require "Include/Config.php"; require "Include/Functions.php"; // Set the page title and include HTML header $sPageTitle = gettext("View Your Cart"); require "Include/Header.php"; if (count($_SESSION['aPeopleCart']) == 0) { echo "<p align=\"center\" class=\"LargeText\">" . gettext("You have no items in your cart.") . "</p>"; } else { $sSQL = "SELECT * FROM person_per LEFT JOIN family_fam ON person_per.per_fam_ID = family_fam.fam_ID WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ") ORDER BY per_LastName"; $rsCartItems = RunQuery($sSQL); echo "<p align=\"center\">" . gettext("There are") . " " . mysql_num_rows($rsCartItems) . " " . gettext("item(s) in your cart.") . "</p>"; echo "<table align=\"center\" width=\"50%\" cellpadding=\"4\" cellspacing=\"0\">\n"; echo "<tr class=\"TableHeader\">"; echo "<td><b>" . gettext("Name") . "</b></td>"; echo "<td align=\"center\"><b>" . gettext("Address?") . "</b></td>"; echo "<td align=\"center\"><b>" . gettext("Email?") . "</b></td>"; echo "<td><b>" . gettext("Remove") . "</b></td>"; $sEmailLink = "<a href=\"mailto:?to="; $iEmailNum = 0; while ($aRow = mysql_fetch_array($rsCartItems)) { $sRowClass = AlternateRowStyle($sRowClass); extract($aRow); $sEmail = SelectWhichInfo($per_Email, $fam_Email, False); if (strlen($sEmail)) {
</p> <?php if (count($_SESSION['aPeopleCart']) > 0) { // Get all the families $sSQL = "SELECT fam_Name, fam_ID FROM family_fam ORDER BY fam_Name"; $rsFamilies = RunQuery($sSQL); // Get the family roles $sSQL = "SELECT * FROM list_lst WHERE lst_ID = 2 ORDER BY lst_OptionSequence"; $rsFamilyRoles = RunQuery($sSQL); $sRoleOptionsHTML = ""; while ($aRow = mysql_fetch_array($rsFamilyRoles)) { extract($aRow); $sRoleOptionsHTML .= "<option value=\"" . $lst_OptionID . "\">" . $lst_OptionName . "</option>"; } $sSQL = "SELECT per_Title, per_FirstName, per_MiddleName, per_LastName, per_Suffix, per_fam_ID, per_ID\n\t\t\tFROM person_per WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ")\n\t\t\tORDER BY per_LastName"; $rsCartItems = RunQuery($sSQL); echo "<table align=\"center\" width=\"25%\" cellpadding=\"4\" cellspacing=\"0\">\n"; echo "<tr class=\"TableHeader\">"; echo "<td> </td>"; echo "<td><b>" . gettext("Name") . "</b></td>"; echo "<td align=\"center\"><b>" . gettext("Assign Role") . "</b></td>"; $count = 1; while ($aRow = mysql_fetch_array($rsCartItems)) { $sRowClass = AlternateRowStyle($sRowClass); extract($aRow); echo "<tr class=\"" . $sRowClass . "\">"; echo "<td align=\"center\">" . $count++ . "</td>"; echo "<td><a href=\"PersonView.php?PersonID=" . $per_ID . "\">" . FormatFullName($per_Title, $per_FirstName, $per_MiddleName, $per_LastName, $per_Suffix, 1) . "</a></td>"; echo "<td align=\"center\">"; if ($per_fam_ID == 0) {
function GenerateLabels(&$pdf, $mode, $iBulkMailPresort, $bToParents, $bOnlyComplete) { // $mode is "indiv" or "fam" unset($didFam); $sSQL = "SELECT cfg_name, IFNULL(cfg_value, cfg_default) AS value "; $sSQL .= "FROM config_cfg WHERE cfg_section='General'"; $rsConfig = RunQuery($sSQL); if ($rsConfig) { while (list($cfg_name, $cfg_value) = mysql_fetch_row($rsConfig)) { ${$cfg_name} = $cfg_value; } } $sAdultRole = $sDirRoleHead . "," . $sDirRoleSpouse; $sAdultRole = trim($sAdultRole, " ,\t\n\r\v"); $aAdultRole = explode(",", $sAdultRole); $aAdultRole = array_unique($aAdultRole); sort($aAdultRole); $sChildRole = trim($sDirRoleChild, " ,\t\n\r\v"); $aChildRole = explode(",", $sChildRole); $aChildRole = array_unique($aChildRole); sort($aChildRole); $sSQL = "SELECT * FROM person_per LEFT JOIN family_fam "; $sSQL .= "ON person_per.per_fam_ID = family_fam.fam_ID "; $sSQL .= "WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ") "; $sSQL .= "ORDER BY fam_Zip, per_LastName, per_FirstName"; $rsCartItems = RunQuery($sSQL); $sRowClass = "RowColorA"; $didFam = array(); while ($aRow = mysql_fetch_array($rsCartItems)) { // It's possible (but unlikely) that three labels can be generated for a // family even when they are grouped. // At most one label for all adults // At most one label for all children // At most one label for all others (for example, another church or a landscape // company) $sRowClass = AlternateRowStyle($sRowClass); if ($aRow['per_fam_ID'] == 0 && $mode == "fam") { // Skip people with no family ID continue; } // Skip if mode is fam and we have already printed labels if (array_key_exists($aRow['per_fam_ID'], $didFam) and $didFam[$aRow['per_fam_ID']] && $mode == "fam") { continue; } $didFam[$aRow['per_fam_ID']] = 1; unset($aName); if ($mode == "fam") { $aName = GroupBySalutation($aRow['per_fam_ID'], $aAdultRole, $aChildRole); } else { $sName = FormatFullName($aRow['per_Title'], $aRow['per_FirstName'], "", $aRow['per_LastName'], $aRow['per_Suffix'], 1); $bChild = FALSE; foreach ($aChildRole as $value) { if ($aRow['per_fmr_ID'] == $value) { $bChild = TRUE; } } if ($bChild) { $aName['child'] = substr($sName, 0, 33); } else { $aName['indiv'] = substr($sName, 0, 33); } } foreach ($aName as $key => $sName) { // Bail out if nothing to print if ($sName == "Nothing to return") { continue; } if ($bToParents && $key == "child") { $sName = "To the parents of:\n" . $sName; } SelectWhichAddress($sAddress1, $sAddress2, $aRow['per_Address1'], $aRow['per_Address2'], $aRow['fam_Address1'], $aRow['fam_Address2'], false); $sCity = SelectWhichInfo($aRow['per_City'], $aRow['fam_City'], False); $sState = SelectWhichInfo($aRow['per_State'], $aRow['fam_State'], False); $sZip = SelectWhichInfo($aRow['per_Zip'], $aRow['fam_Zip'], False); $sAddress = $sAddress1; if ($sAddress2 != "") { $sAddress .= "\n" . $sAddress2; } if (!$bOnlyComplete || strlen($sAddress) && strlen($sCity) && strlen($sState) && strlen($sZip)) { $sLabelList[] = array('Name' => $sName, 'Address' => $sAddress, 'City' => $sCity, 'State' => $sState, 'Zip' => $sZip); //,'fam_ID'=>$aRow['fam_ID']); } } // end of foreach loop } // end of while loop unset($zipLabels); if ($iBulkMailPresort) { // // now sort the label list by presort bundle definitions // $zipLabels = ZipBundleSort($sLabelList); if ($iBulkMailPresort == 2) { while (list($i, $sLT) = each($zipLabels)) { $pdf->Add_PDF_Label(sprintf("%s\n%s\n%s\n%s, %s %s", $sLT['Note'], $sLT['Name'], $sLT['Address'], $sLT['City'], $sLT['State'], $sLT['Zip'])); } // end while } else { while (list($i, $sLT) = each($zipLabels)) { $pdf->Add_PDF_Label(sprintf("%s\n%s\n%s, %s %s", $sLT['Name'], $sLT['Address'], $sLT['City'], $sLT['State'], $sLT['Zip'])); } // end while } // end of if ($BulkMailPresort == 2) } else { while (list($i, $sLT) = each($sLabelList)) { $pdf->Add_PDF_Label(sprintf("%s\n%s\n%s, %s %s", $sLT['Name'], $sLT['Address'], $sLT['City'], $sLT['State'], $sLT['Zip'])); } // end while } // end of if($iBulkMailPresort) if (isset($zipLabels)) { return serialize($zipLabels); } else { return serialize($sLabelList); } }
} } $sLabelFormat = FilterInput($_GET["labeltype"]); setcookie("labeltype", $sLabelFormat, time() + 60 * 60 * 24 * 90, "/"); $pdf = new PDF_NameTags($sLabelFormat); $sFontInfo = FontFromName($_GET["labelfont"]); setcookie("labelfont", $_GET["labelfont"], time() + 60 * 60 * 24 * 90, "/"); $sFontSize = $_GET["labelfontsize"]; setcookie("labelfontsize", $sFontSize, time() + 60 * 60 * 24 * 90, "/"); $pdf->SetFont($sFontInfo[0], $sFontInfo[1]); if ($sFontSize != "default") { $pdf->Set_Char_Size($sFontSize); } //if($sFontSize != "default") // $pdf->SetFontSize($sFontSize); $sSQL = "SELECT * FROM person_per WHERE per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ") ORDER BY per_LastName"; $rsPersons = RunQuery($sSQL); while ($aPer = mysql_fetch_array($rsPersons)) { extract($aPer); $PosX = $pdf->_Margin_Left + $pdf->_COUNTX * ($pdf->_Width + $pdf->_X_Space); $PosY = $pdf->_Margin_Top + $pdf->_COUNTY * ($pdf->_Height + $pdf->_Y_Space); $perimg = "../Images/Person/" . $per_ID . ".jpg"; if (file_exists($perimg)) { $s = getimagesize($perimg); $h = $pdf->_Width / $s[0] * $s[1]; if ($h > $pdf->_Height) { $useWidth = $pdf->_Width * $pdf->_Height / $h; } else { $useWidth = $pdf->_Width; } $pdf->Image($perimg, $PosX, $PosY, $useWidth);