| [ PHPXref.com ] | [ Generated: Sun Jul 20 19:12:48 2008 ] | [ OSC 2.0.5 ] |
| [ Index ] [ Variables ] [ Functions ] [ Classes ] [ Constants ] [ Statistics ] | ||
[Summary view] [Print] [Text view]
1 <?php 2 /******************************************************************************* 3 * 4 * filename : CSVCreateFile.php 5 * last change : 2003-06-11 6 * 7 * http://osc.sourceforge.net 8 * 9 * This product is based upon work previously done by Infocentral (infocentral.org) 10 * on their PHP version Church Management Software that they discontinued 11 * and we have taken over. We continue to improve and build upon this product 12 * in the direction of excellence. 13 * 14 * OpenSourceChurch (OSC) is free software; you can redistribute it and/or modify 15 * it under the terms of the GNU General Public License as published by 16 * the Free Software Foundation; either version 2 of the License, or 17 * (at your option) any later version. 18 * 19 * Any changes to the software must be submitted back to the OpenSourceChurch project 20 * for review and possible inclusion. 21 * 22 * copyright : Copyright 2001-2003 Deane Barker, Chris Gebhardt 23 ******************************************************************************/ 24 25 // Include the function library 26 require "Include/Config.php"; 27 require "Include/Functions.php"; 28 29 // If CSVAdminOnly option is enabled and user is not admin, redirect to the menu. 30 if (!$_SESSION['bAdmin'] && $bCSVAdminOnly) { 31 Redirect("Menu.php"); 32 exit; 33 } 34 35 // Turn ON output buffering 36 ob_start(); 37 38 // Get Source and Format from the request object and assign them locally 39 $sSource = strtolower($_POST["Source"]); 40 $sFormat = strtolower($_POST["Format"]); 41 $bSkipIncompleteAddr = isset($_POST["SkipIncompleteAddr"]); 42 $bSkipNoEnvelope = isset($_POST["SkipNoEnvelope"]); 43 44 // Get the custom fields 45 if ($sFormat == "default") 46 { 47 $sSQL = "SELECT * FROM person_custom_master WHERE chu_Church_ID=" . $_SESSION['iChurchID'] . " ORDER BY custom_Order"; 48 $rsCustomFields = RunQuery($sSQL); 49 } 50 51 //Get family roles 52 $sSQL = "SELECT * FROM list_lst WHERE lst_ID = 2 AND chu_Church_ID=" . $_SESSION['iChurchID'] . " ORDER BY lst_OptionSequence"; 53 $rsFamilyRoles = RunQuery($sSQL); 54 while ($aRow =mysql_fetch_array($rsFamilyRoles)) 55 { 56 extract($aRow); 57 $familyRoles[$lst_OptionID] = $lst_OptionName; 58 $roleSequence[$lst_OptionSequence] = $lst_OptionID; 59 } 60 61 // 62 // Prepare the MySQL query 63 // 64 65 $sJoinFamTable = " LEFT JOIN family_fam ON per_fam_ID = fam_ID "; 66 67 // If our source is the cart contents, we don't need to build a WHERE filter string 68 if ($sSource == "cart") 69 $sWhereExt = "AND per_ID IN (" . ConvertCartToString($_SESSION['aPeopleCart']) . ")"; 70 71 else 72 { 73 // If we're filtering by groups, include the p2g2r table 74 if (!empty($_POST["GroupID"])) $sGroupTable = ", person2group2role_p2g2r"; 75 76 // Prepare any extentions to the WHERE clauses 77 $sWhereExt = ""; 78 if (!empty($_POST["Classification"])) 79 { 80 $count = 0; 81 foreach ($_POST["Classification"] as $Cls) 82 { 83 $Class[$count++] = FilterInput($Cls,'int'); 84 } 85 if ($count == 1) 86 { 87 if ($Class[0]) 88 $sWhereExt .= "AND per_cls_ID = " . $Class[0] . " "; 89 } 90 else 91 { 92 $sWhereExt .= "AND (per_cls_ID = " . $Class[0]; 93 for($i = 1; $i < $count; $i++) 94 $sWhereExt .= " OR per_cls_ID = " . $Class[$i]; 95 $sWhereExt .= ") "; 96 // this is silly: should be something like.. $sWhereExt .= "AND per_cls_ID IN 97 } 98 } 99 100 if (!empty($_POST["FamilyRole"])) 101 { 102 $count = 0; 103 foreach ($_POST["FamilyRole"] as $Fmr) 104 { 105 $Class[$count++] = FilterInput($Fmr,'int'); 106 } 107 if ($count == 1) 108 { 109 if ($Class[0]) 110 $sWhereExt .= "AND per_fmr_ID = " . $Class[0] . " "; 111 } 112 else 113 { 114 $sWhereExt .= "AND (per_fmr_ID = " . $Class[0]; 115 for($i = 1; $i < $count; $i++) 116 { 117 $sWhereExt .= " OR per_fmr_ID = " . $Class[$i]; 118 } 119 $sWhereExt .= ") "; 120 } 121 } 122 123 if (!empty($_POST["Gender"])) 124 $sWhereExt .= "AND per_Gender = " . FilterInput($_POST["Gender"],'int') . " "; 125 126 if (!empty($_POST["GroupID"])) 127 { 128 $count = 0; 129 foreach ($_POST["GroupID"] as $Grp) 130 { 131 $Class[$count++] = FilterInput($Grp,'int'); 132 } 133 if ($count == 1) 134 { 135 if ($Class[0]) 136 $sWhereExt .= "AND per_ID = p2g2r_per_ID AND p2g2r_grp_ID = " . $Class[0] . " "; 137 } 138 else 139 { 140 $sWhereExt .= "AND per_ID = p2g2r_per_ID AND (p2g2r_grp_ID = " . $Class[0]; 141 for($i = 1; $i < $count; $i++) 142 { 143 $sWhereExt .= " OR p2g2r_grp_ID = " . $Class[$i]; 144 } 145 $sWhereExt .= ") "; 146 } 147 148 // This is used for individual mode to remove duplicate rows from people assigned multiple groups. 149 $sGroupBy = " GROUP BY per_ID"; 150 } 151 152 if (!empty($_POST["MembershipDate1"])) 153 $sWhereExt .= "AND per_MembershipDate >= '" . FilterInput($_POST["MembershipDate1"],'char',10) . "' "; 154 if ($_POST["MembershipDate2"] != date("Y-m-d")) 155 $sWhereExt .= "AND per_MembershipDate <= '" . FilterInput($_POST["MembershipDate2"],'char',10) . "' "; 156 157 $refDate = getdate(time()); 158 159 if (!empty($_POST["BirthDate1"])) 160 { 161 $sWhereExt .= "AND DATE_FORMAT(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),'%Y-%m-%d') >= '" . FilterInput($_POST["BirthDate1"],'char',10) . "' "; 162 } 163 164 if ($_POST["BirthDate2"] != date("Y-m-d")) 165 { 166 $sWhereExt .= "AND DATE_FORMAT(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),'%Y-%m-%d') <= '" . FilterInput($_POST["BirthDate2"],'char',10) . "' "; 167 } 168 169 if (!empty($_POST["AnniversaryDate1"])) 170 { 171 $annivStart = getdate(strtotime(FilterInput($_POST["AnniversaryDate1"]))); 172 173 // Add year to query if not in future 174 if ($annivStart["year"] < date("Y") || ($annivStart["year"] == date("Y") && $annivStart["mon"] <= date("m") && $annivStart["mday"] <= date("d"))) 175 $sWhereExt .= "AND fam_WeddingDate >= '" . FilterInput($_POST["AnniversaryDate1"],'char',10) . "' "; 176 else 177 $sWhereExt .= "AND DAYOFYEAR(fam_WeddingDate) >= DAYOFYEAR('" . FilterInput($_POST["AnniversaryDate1"],'char',10) . "') "; 178 } 179 180 if ($_POST["AnniversaryDate2"] != date("Y-m-d")) 181 { 182 $annivEnd=getdate(strtotime(FilterInput($_POST["AnniversaryDate2"],'char',10))); 183 184 // Add year to query if not in future 185 if ($annivEnd["year"] < date("Y") || ($annivEnd["year"] == date("Y") && $annivEnd["mon"] <= date("m") && $annivEnd["mday"] <= date("d"))) 186 $sWhereExt .= "AND fam_WeddingDate <= '" . FilterInput($_POST["AnniversaryDate2"],'char',10) . "' "; 187 else 188 { 189 $refDate = getdate(strtotime($_POST["AnniversaryDate2"])); 190 $sWhereExt .= "AND DAYOFYEAR(fam_WeddingDate) <= DAYOFYEAR('" . FilterInput($_POST["AnniversaryDate2"],'char',10) . "') "; 191 } 192 } 193 194 if (!empty($_POST["EnterDate1"])) 195 $sWhereExt .= "AND per_DateEntered >= '" . FilterInput($_POST["EnterDate1"],'char',10) . "' "; 196 if ($_POST["EnterDate2"] != date("Y-m-d")) 197 $sWhereExt .= "AND per_DateEntered <= '" . FilterInput($_POST["EnterDate2"],'char',10) . "' "; 198 } 199 200 if ($sFormat == "addtocart") 201 { 202 // Get individual records to add to the cart 203 204 $sSQL = "SELECT per_ID FROM person_per $sGroupTable $sJoinFamTable WHERE 1 = 1 AND person_per.chu_Church_ID=" . $_SESSION['iChurchID'] . " $sWhereExt $sGroupBy"; 205 $sSQL .= " ORDER BY per_LastName"; 206 $rsLabelsToWrite = RunQuery($sSQL); 207 while($aRow = mysql_fetch_array($rsLabelsToWrite)) 208 { 209 extract($aRow); 210 AddToPeopleCart($per_ID); 211 } 212 Redirect("CartView.php"); 213 } 214 else 215 { 216 // Build the complete SQL statement 217 218 if ($sFormat == "rollup") 219 { 220 $sSQL = "(SELECT *, 0 AS memberCount, per_LastName AS SortMe FROM person_per $sGroupTable $sJoinFamTable WHERE per_fam_ID = 0 AND person_per.chu_Church_ID=" . $_SESSION['iChurchID']. " $sWhereExt) 221 UNION (SELECT *, COUNT(*) AS memberCount, fam_Name AS SortMe FROM person_per $sGroupTable $sJoinFamTable WHERE per_fam_ID > 0 $sWhereExt GROUP BY per_fam_ID HAVING memberCount = 1) 222 UNION (SELECT *, COUNT(*) AS memberCount, fam_Name AS SortMe FROM person_per $sGroupTable $sJoinFamTable WHERE per_fam_ID > 0 $sWhereExt GROUP BY per_fam_ID HAVING memberCount > 1) ORDER BY SortMe"; 223 } 224 else 225 { 226 $sSQL = "SELECT * FROM person_per $sGroupTable $sJoinFamTable WHERE 1 = 1 AND person_per.chu_Church_ID=" . $_SESSION['iChurchID'] . " $sWhereExt $sGroupBy ORDER BY per_LastName"; 227 } 228 229 //Execute whatever SQL was entered 230 $rsLabelsToWrite = RunQuery($sSQL); 231 232 //Produce Header Based on Selected Fields 233 if ($sFormat == "rollup") 234 { 235 $headerString = "\"Name\","; 236 } 237 else 238 { 239 $headerString = "\"LastName\","; 240 if (!empty($_POST["Title"])) $headerString .= "\"Title\","; 241 if (!empty($_POST["FirstName"])) $headerString .= "\"FirstName\","; 242 if (!empty($_POST["Suffix"])) $headerString .= "\"Suffix\","; 243 if (!empty($_POST["MiddleName"])) $headerString .= "\"MiddleName\","; 244 } 245 246 if (!empty($_POST["Address1"])) $headerString .= "\"Address1\","; 247 if (!empty($_POST["Address2"])) $headerString .= "\"Address2\","; 248 if (!empty($_POST["City"])) $headerString .= "\"City\","; 249 if (!empty($_POST["State"])) $headerString .= "\"State\","; 250 if (!empty($_POST["Zip"])) $headerString .= "\"Zip\","; 251 if (!empty($_POST["Country"])) $headerString .= "\"Country\","; 252 if (!empty($_POST["HomePhone"])) $headerString .= "\"HomePhone\","; 253 if (!empty($_POST["WorkPhone"])) $headerString .= "\"WorkPhone\","; 254 if (!empty($_POST["CellPhone"])) $headerString .= "\"CellPhone\","; 255 if (!empty($_POST["Email"])) $headerString .= "\"Email\","; 256 if (!empty($_POST["WorkEmail"])) $headerString .= "\"WorkEmail\","; 257 if (!empty($_POST["Envelope"])) $headerString .= "\"Envelope Number\","; 258 if (!empty($_POST["MembershipDate"])) $headerString .= "\"MembershipDate\","; 259 260 261 if ($sFormat == "default") 262 { 263 if (!empty($_POST["BirthdayDate"])) $headerString .= "\"BirthDate\","; 264 if (!empty($_POST["Age"])) $headerString .= "\"Age\","; 265 if (!empty($_POST["PrintFamilyRole"])) $headerString .= "\"FamilyRole\","; 266 } 267 else 268 { 269 if (!empty($_POST["BirthdayDate"])) $headerString .= "\"AnnivDate\","; 270 if (!empty($_POST["Age"])) $headerString .= "\"Anniv\","; 271 } 272 273 // Add any custom field names to the header, unless using family roll-up mode 274 $bUsedCustomFields = false; 275 if ($sFormat == "default") 276 { 277 while($aRow = mysql_fetch_array($rsCustomFields)) 278 { 279 extract($aRow); 280 if (isset($_POST["$custom_Field"])) 281 { 282 $bUsedCustomFields = true; 283 $headerString .= "\"$custom_Name\","; 284 } 285 } 286 } 287 288 $headerString = substr($headerString,0,-1); 289 $headerString .= "\n"; 290 291 header("Content-type: text/x-csv"); 292 header("Content-Disposition: attachment; filename=infocentral-export-" . date("Ymd-Gis") . ".csv"); 293 294 echo $headerString; 295 296 while($aRow = mysql_fetch_array($rsLabelsToWrite)) 297 { 298 $per_Title = ""; 299 $per_FirstName = ""; 300 $per_MiddleName = ""; 301 $per_LastName = ""; 302 $per_Suffix = ""; 303 $per_Address1 = ""; 304 $per_Address2 = ""; 305 $per_City = ""; 306 $per_State = ""; 307 $per_Zip = ""; 308 $per_Country = ""; 309 $per_HomePhone = ""; 310 $per_WorkPhone = ""; 311 $per_CellPhone = ""; 312 $per_Email = ""; 313 $per_WorkEmail = ""; 314 $per_Envelope = ""; 315 $per_MembershipDate = ""; 316 317 $per_BirthDay = ""; 318 $per_BirthMonth = ""; 319 $per_BirthYear = ""; 320 321 $fam_Address1 = ""; 322 $fam_Address2 = ""; 323 $fam_City = ""; 324 $fam_State = ""; 325 $fam_Zip = ""; 326 $fam_Country = ""; 327 $fam_HomePhone = ""; 328 $fam_WorkPhone = ""; 329 $fam_CellPhone = ""; 330 $fam_Email = ""; 331 $fam_WeddingDate = ""; 332 333 $sCountry = ""; 334 335 extract($aRow); 336 337 // If we are doing a family roll-up, we want to favor available family address / phone numbers over the individual data returned 338 if ($sFormat == "rollup") 339 { 340 $sPhoneCountry = SelectWhichInfo($fam_Country, $per_Country, False); 341 $sHomePhone = SelectWhichInfo(ExpandPhoneNumber($fam_HomePhone,$fam_Country,$dummy), ExpandPhoneNumber($per_HomePhone,$sPhoneCountry,$dummy), False); 342 $sWorkPhone = SelectWhichInfo(ExpandPhoneNumber($fam_WorkPhone,$fam_Country,$dummy), ExpandPhoneNumber($per_WorkPhone,$sPhoneCountry,$dummy), False); 343 $sCellPhone = SelectWhichInfo(ExpandPhoneNumber($fam_CellPhone,$fam_Country,$dummy), ExpandPhoneNumber($per_CellPhone,$sPhoneCountry,$dummy), False); 344 $sCountry = SelectWhichInfo($fam_Country,$per_Country,False); 345 SelectWhichAddress($sAddress1, $sAddress2, $fam_Address1, $fam_Address2, $per_Address1, $per_Address2, False); 346 $sCity = SelectWhichInfo($fam_City,$per_City,False); 347 $sState = SelectWhichInfo($fam_State,$per_State,False); 348 $sZip = SelectWhichInfo($fam_Zip,$per_Zip,False); 349 $sEmail = SelectWhichInfo($fam_Email,$per_Email,False); 350 } 351 // Otherwise, the individual data gets precedence over the family data 352 else 353 { 354 $sPhoneCountry = SelectWhichInfo($per_Country, $fam_Country, False); 355 $sHomePhone = SelectWhichInfo(ExpandPhoneNumber($per_HomePhone,$sPhoneCountry,$dummy), ExpandPhoneNumber($fam_HomePhone,$fam_Country,$dummy), False); 356 $sWorkPhone = SelectWhichInfo(ExpandPhoneNumber($per_WorkPhone,$sPhoneCountry,$dummy), ExpandPhoneNumber($fam_WorkPhone,$fam_Country,$dummy), False); 357 $sCellPhone = SelectWhichInfo(ExpandPhoneNumber($per_CellPhone,$sPhoneCountry,$dummy), ExpandPhoneNumber($fam_CellPhone,$fam_Country,$dummy), False); 358 $sCountry = SelectWhichInfo($per_Country,$fam_Country,False); 359 SelectWhichAddress($sAddress1, $sAddress2, $per_Address1, $per_Address2, $fam_Address1, $fam_Address2, False); 360 $sCity = SelectWhichInfo($per_City,$fam_City,False); 361 $sState = SelectWhichInfo($per_State,$fam_State,False); 362 $sZip = SelectWhichInfo($per_Zip,$fam_Zip,False); 363 $sEmail = SelectWhichInfo($per_Email,$fam_Email,False); 364 } 365 366 // Check if we're filtering out people with incomplete addresses 367 if (!($bSkipIncompleteAddr && (strlen($sCity) == 0 || strlen($sState) == 0 || strlen($sZip) == 0 || (strlen($sAddress1) == 0 && strlen($sAddress2) == 0)))) 368 { 369 // Check if we're filtering out people with no envelope number assigned 370 // ** should move this to the WHERE clause 371 if (!($bSkipNoEnvelope && (strlen($per_Envelope) == 0))) 372 { 373 // If we are doing family roll-up, we use a single, formatted name field 374 if ($sFormat == "default") 375 { 376 $sString = "\"" . $per_LastName; 377 if (isset($_POST["Title"])) $sString .= "\",\"" . $per_Title; 378 if (isset($_POST["FirstName"])) $sString .= "\",\"" . $per_FirstName; 379 if (isset($_POST["Suffix"])) $sString .= "\",\"" . $per_Suffix; 380 if (isset($_POST["MiddleName"])) $sString .= "\",\"" . $per_MiddleName; 381 } 382 else if ($sFormat == "rollup") 383 { 384 if ($memberCount > 1) 385 $sString = "\"" . $fam_Name . " Family"; 386 else 387 $sString = "\"" . $per_LastName . ", " . $per_FirstName; 388 } 389 390 if (isset($_POST["Address1"])) $sString .= "\",\"" . $sAddress1; 391 if (isset($_POST["Address2"])) $sString .= "\",\"" . $sAddress2; 392 if (isset($_POST["City"])) $sString .= "\",\"" . $sCity; 393 if (isset($_POST["State"])) $sString .= "\",\"" . $sState; 394 if (isset($_POST["Zip"])) $sString .= "\",\"" . $sZip; 395 if (isset($_POST["Country"])) $sString .= "\",\"" . $sCountry; 396 if (isset($_POST["HomePhone"])) $sString .= "\",\"" . $sHomePhone; 397 if (isset($_POST["WorkPhone"])) $sString .= "\",\"" . $sWorkPhone; 398 if (isset($_POST["CellPhone"])) $sString .= "\",\"" . $sCellPhone; 399 if (isset($_POST["Email"])) $sString .= "\",\"" . $sEmail; 400 if (isset($_POST["WorkEmail"])) $sString .= "\",\"" . $per_WorkEmail; 401 if (isset($_POST["Envelope"])) $sString .= "\",\"" . $per_Envelope; 402 if (isset($_POST["MembershipDate"])) $sString .= "\",\"" . $per_MembershipDate; 403 404 if ($sFormat == "default") 405 { 406 if (isset($_POST["BirthdayDate"])) 407 { 408 $sString .= "\",\""; 409 if ($per_BirthYear != '') 410 $sString .= $per_BirthYear . "-"; 411 else 412 $sString .= "0000-"; 413 $sString .= $per_BirthMonth . "-" . $per_BirthDay; 414 } 415 416 if (isset($_POST["Age"])) 417 { 418 if (isset($per_BirthYear)) 419 $age = $refDate["year"] - $per_BirthYear - ($per_BirthMonth > $refDate["mon"] || ($per_BirthMonth == $refDate["mon"] && $per_BirthDay > $refDate["mday"])); 420 else 421 $age = ""; 422 423 $sString .= "\",\"" . $age; 424 } 425 426 if (isset($_POST["PrintFamilyRole"])) 427 { 428 $sString .= "\",\"" . $familyRoles[$per_fmr_ID]; 429 } 430 } 431 else 432 { 433 if (isset($_POST["BirthdayDate"])) 434 { 435 $sString .= "\",\"" . $fam_WeddingDate; 436 } 437 438 if (isset(