Odyssey
domain_email_report.prg
1 <?php
2  $monLibrary= dirname(__FILE__) . "/../library";
3  $sharedLibrary= dirname(__FILE__) . "/../../shared/library";
4  require_once("$monLibrary/cu_top.i");
5  require_once("$monLibrary/ck_hticket.i");
6  require_once("$monLibrary/cu_pass.i");
7  require_once("$sharedLibrary/cu_flagconst.i");
8 
9  if (!CheckPerm($link, $Hu, basename($_SERVER['SCRIPT_NAME']), $_SERVER['REMOTE_ADDR'])) {
10  // ** Permissions failed
11  // ** redirect to new page
12  header("Location: /hcuadm/hcu_noperm.prg");
13  exit;
14  }
15 
16 $dms_ok=array('rv'=>'string', 'btnCont'=>'string', 'btnUpload'=>'string', 'selResults'=>'string', 'selCompare'=>'string',
17  'chkShowZero'=>'digits', 'uplMonth'=>'string', 'uplYear'=>'string', 'uplFileName'=>'string',
18  "rdate"=>"digits", "enddate" => "digits", "action"=>"string");
19 dms_import($dms_ok);
20 
21 $report_view = "";
22  $self = $_SERVER['PHP_SELF'];
23  if (!empty($_REQUEST['rv'])) {
24  $report_view = $rv;
25  }
26 
27  $results_dir = "/home/httpd/monitor/emailrpt/";
28  $xrc_prefix = "emailrpt";
29 
30  // see if user is improving the mapping of domains to CU names
31  if ( $action == "fix_billing_mapping" ) {
32  $dms_ok=array('domain'=>'string', 'selResults'=>'string', 'selCompare'=>'string', 'cu_name'=>'string');
33  dms_import_v2($HB_ENV, 'HCUPOST', $dms_ok);
34 
35  // get the list of credit unions
36  $cuList = GetCUCodeList( $link );
37 
38  // get name of current credit union, if there is one
39  $cuName = trim($HB_ENV["HCUPOST"]["cu_name"]);
40 
41  print <<< print_html
42  <form action='$self' method='POST'>
43  <table border='0' align='center' cellpadding=2 cellspacing=0>
44  <input type="hidden" name="action" value="map_cu_domain">
45  <input type="hidden" name="domain" value="{$HB_ENV["HCUPOST"]["domain"]}">
46  <input type="hidden" name="selResults" value="{$HB_ENV["HCUPOST"]["selResults"]}">
47  <input type="hidden" name="selCompare" value="{$HB_ENV["HCUPOST"]["selCompare"]}">
48  <tr class='barb'><td align='center'><font size=+1>Map Credit Union Domain</font></td></tr>
49  <tr><td>Domain Name</td><td>{$HB_ENV["HCUPOST"]["domain"]}</td></tr>
50  <tr><td>Credit Union</td>
51  <td><select name="cu_name">
52  <option value="">Select one...</option>
53 print_html;
54 
55  for ( $i = 0; $i < count( $cuList ); $i++ ) {
56  $selected = $cuName == $cuList[$i] ? "SELECTED" : "";
57  print "<option value='{$cuList[$i]}' $selected>{$cuList[$i]}</option>";
58  }
59 
60  print <<< print_html
61  </select></td></tr>
62  <tr><td style="text-align:center;"><button type="submit">Map it!</button></td></tr>
63  </table>
64  </form>
65  <form method='post' action='$self' name='returnForm'>
66  <input type="hidden" name="btnCont" value="View Report">
67  <input type="hidden" name="selResults" value="{$HB_ENV["HCUPOST"]["selResults"]}">
68  <input type="hidden" name="selCompare" value="{$HB_ENV["HCUPOST"]["selCompare"]}">
69  <a onClick='returnForm.submit();' style='text-decoration:underline; cursor:pointer;'>Return to results</a>
70  </form>
71  </body></html>
72 print_html;
73 
74  exit;
75  }
76 
77  // see if mapping a domain
78  if ( $action == "map_cu_domain" ) {
79  // validate inputs
80  $dms_ok=array('domain'=>'string', 'selResults'=>'string', 'selCompare'=>'string', 'cu_name'=>'string');
81  dms_import_v2($HB_ENV, 'HCUPOST', $dms_ok);
82 
83  $domain = htmlEntities(trim($HB_ENV["HCUPOST"]["domain"]), ENT_QUOTES);
84  $cu_name = htmlEntities(trim($HB_ENV["HCUPOST"]["cu_name"]), ENT_QUOTES);
85 
86  if ( strlen( $cu_name ) > 0 && strlen( $domain ) > 0 ) {
87  $sql = "SELECT cu_name FROM cubilldomainmap WHERE domain = '$domain'";
88 
89  $sth = db_query($sql, $link);
90 
91  // find any occurances
92  $mapRow = db_fetch_assoc($sth);
93 
94  if ( strlen( trim($mapRow["cu_name"]) ) > 0 ) {
95  $operation = "updated";
96  $sql = "UPDATE cubilldomainmap SET cu_name = '$cu_name' WHERE domain = '$domain'";
97  } else {
98  $operation = "added";
99  $sql = "INSERT INTO cubilldomainmap VALUES ( '$cu_name', '$domain' )";
100  }
101 
102  $sth = db_query($sql, $link);
103 
104  if ( !$sth ) {
105  $message = "Credit union $cu_name mapping to $domain failed!";
106  } else {
107  $message = "Credit union $cu_name mapping to $domain was $operation successfully.";
108  }
109  }
110 
111  // now go to the report
112  $_POST["btnCont"] = "View Report";
113 
114  // the other variables were read at the top of the script
115  }
116 
117  // ** Execute any UPLOAD PRIOR to showing the form, so the new file will show up in the list
118  if (isset($_POST['btnUpload'])) {
119  $retMsg = ProcessFile($results_dir, $xrc_prefix, $uplFileName, $uplMonth, $uplYear);
120  }
121 
122  if (isset($_POST['btnCont'])) {
123  // ** We want to show the report
124  // ** Possibly some error checking -- but for now skip that part
125  $report_view = "REPORT";
126  } else if ( $report_view != "RAW" ) {
127 
128  $month = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
129 
130  // ** this is the results of the file directory
131  $results_list = Array();
132 
133  // ** Get a collection of names from the directory
134  if (is_dir($results_dir)) {
135  if ($dir_hndl = opendir($results_dir)) {
136  while (($file = readdir($dir_hndl)) !== false) {
137  if (filetype($results_dir . $file) == "file" && (substr($file, 0, strlen($xrc_prefix)) == $xrc_prefix)) {
138  $results_list[] = substr($file, strlen($xrc_prefix));
139  }
140  }
141  closedir($dir_hndl);
142  }
143  // ** Order the array
144  rsort($results_list);
145  }
146 
147  $month_select[date('M', mktime(0, 0, 0, date('m'), date('d'), date('Y')))] = " SELECTED ";
148  $year_select[date('Y', mktime(0, 0, 0, date('m'), date('d'), date('Y')))] = " SELECTED ";
149  }
150  /* ******
151  *** HERE are the phases to the form ---
152  *** VIEW -- View the available status reports to choose from
153  *** REPORT -- Report the information in the reports...
154  ** List -- This will list the information from the results file
155  * Include Zeroes -- By default the report will NOT show zero accounts unless this option is selected
156 
157  ****** */
158  $lookupError = 0;
159  switch ($report_view) {
160  case "REPORT":
161  $file1_domain = Array();
162  $result_file1 = $xrc_prefix . $selResults;
163  $rptShowZero = false;
164  if (isset($chkShowZero)) {
165  $rptShowZero = ($chkShowZero == 1);
166  }
167 
168  $TTL_Clients = 0;
169  $TTL_Client_Info = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
170  if (file_exists($results_dir . $result_file1)) {
171  $file1_results = file($results_dir . $result_file1);
172  foreach($file1_results as $key => $value){
173  $file1_domain[] = explode("\t", $value);
174  }
175  }
176 
177  // see if we are comparing to an earlier month
178  $compareList = array();
179  if ( strlen( $selCompare ) ) {
180  $compareFile = $xrc_prefix . $selCompare;
181 
182  if (file_exists($results_dir . $compareFile)) {
183  $compareResults = file($results_dir . $compareFile);
184  foreach($compareResults as $key => $value){
185  $compareList[] = explode("\t", $value);
186  }
187  }
188  }
189  // ** MWS 11/12/2009 - A change was made to add additional columns
190  // ** If the column count is 11 then this is the OLD schema
191  // ** 3 new columns were added -- Ordinal postions {6,7,8}
192  if (count($file1_domain[0]) == 11) {
193  for ( $idxAdjustRow = 0; $idxAdjustRow < count($file1_domain); $idxAdjustRow++) {
194  // ** Now reposition the data in the columns
195  $file1_domain[$idxAdjustRow][12] = $file1_domain[$idxAdjustRow][9];
196  $file1_domain[$idxAdjustRow][11] = $file1_domain[$idxAdjustRow][8];
197  $file1_domain[$idxAdjustRow][9] = $file1_domain[$idxAdjustRow][6];
198  // ** ReSET COlumn values -- ALways be zero for past months
199  $file1_domain[$idxAdjustRow][6] = 0;
200  $file1_domain[$idxAdjustRow][7] = 0;
201  $file1_domain[$idxAdjustRow][8] = 0;
202  }
203  }
204 
205  // ** LOAD THE INFORMATION -- into arrays
206 
207  if ( strlen( $message ) ) {
208  $messageLine = "<tr><td style='text-align:center; background-color:yellow;'>$message</td></tr>";
209  } else {
210  $messageLine = "";
211  }
212 
213  // ** PRINT HEADER INFORMATION
214  cu_header("Email Domain Report");
215  print <<< print_html
216  <form action='$self' method='POST'>
217  <table width='700' border='0' align='center' cellpadding=3 cellspacing=0 class='dmsbg'><tr><td>
218  <table width='100%' border='0' align='center' bgcolor=white cellpadding2 cellspacing=0>
219  <tr class='barb'><td align='center'><font size=+1>Report Results</font></td></tr>
220  <tr><td><a href="$infourl/hcuadm/cuilist.prg" target="parent">Credit Union List</a></td></tr>
221  $messageLine
222  <tr><td>
223  <table border=0 width='100%' align='center' cellpadding=1 cellspacing=1>
224  <tr class='hdrc'>
225  <td>CU</td>
226  <td>Domain Name</td>
227  <td>TTL ACCT</td>
228  <td>REG</td>
229  <td>250M</td>
230  <td>1G</td>
231  <td>2G</td>
232  <td>100 3Yr</td>
233  <td>100 5Yr</td>
234  <td>100 7Yr</td>
235  <td>Cost</td>
236  <td>Prem</td>
237  <td>Total Bill</td>
238  </tr>
239 print_html;
240 
241  // if comparing two files need to break the data into groups. Otherwise, do it the old way.
242  if ( count( $compareList ) > 0 ) {
243  // First, compare the two arrays
244  $outputSameList = array();
245  $outputChangeList = array();
246  $outputAddList = array();
247  $outputDropList = array();
248  for ( $i = 0; $i < count( $file1_domain ); $i++ ) {
249  $domainName = $file1_domain[$i][0];
250  $foundCompare = false;
251  for ( $c = 0; $c < count( $compareList ); $c++ ) {
252  if ( $compareList[$c][0] == $domainName ) {
253  $foundCompare = true;
254  break;
255  }
256  }
257  if ( $foundCompare ) {
258  // see if same
259  $diffArray = array_diff( $file1_domain[$i], $compareList[$c] );
260  if ( count( $diffArray ) ) {
261  $outputChangeList[] = $file1_domain[$i];
262  } else {
263  $outputSameList[] = $file1_domain[$i];
264  }
265  } else {
266  // put on the Add list
267  $outputAddList[] = $file1_domain[$i];
268  }
269  }
270 
271  // look to see what was dropped from last month to now
272  for ( $c = 0; $c < count( $compareList ); $c++ ) {
273  $domainName = $compareList[$c][0];
274  $foundInCurrent = false;
275  for ( $i = 0; $i < count( $file1_domain ); $i++ ) {
276  if ( $file1_domain[$i][0] == $domainName ) {
277  $foundInCurrent = true;
278  break;
279  }
280  }
281  if ( !$foundInCurrent ) {
282  $outputDropList[] = $compareList[$c];
283  }
284  }
285 
286  // Show what we are doing
287  $resultStamp = mktime( 6, 0, 0, substr( $selResults, 4, 2 ) + 1, 0, substr( $selResults, 0, 4 ) );
288  $resultName = date( "F Y", $resultStamp );
289  $compareStamp = mktime( 6, 0, 0, substr( $selCompare, 4, 2 ) + 1, 0, substr( $selCompare, 0, 4 ) );
290  $CompareName = date( "F Y", $compareStamp );
291  print "
292  <tr class='hdr'>
293  <td colspan='13' style='font-size:larger; font-style:italic; font-weight:normal;'>Comparing $resultName against $CompareName</td>
294  <tr> ";
295  // Show domains that didn't change
296  print "
297  <tr class='hdr'>
298  <td colspan='13'>Domains That Didn't Change</td>
299  <tr> ";
300  if ( count( $outputSameList ) > 0 ) {
301  $tr_class = "";
302  foreach ($outputSameList as $key => $dom_value) {
303  $TTL_Clients++;
304  if (intval($dom_value[12]) > 0 || $rptShowZero) {
305  $TTL_Client_Info[0]++;
306  for ($idx=1; $idx <= 12; $idx++) {
307  $TTL_Client_Info[$idx] += intval($dom_value[$idx]);
308  }
309  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
310  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
311  if ( $needsLookup ) {
312  $lookupError++;
313  $classColor = "red";
314  } else {
315  $classColor = "";
316  }
317  $lookupString = "<a href=\"$self?action=fix_billing_mapping&domain={$dom_value[0]}&selResults={$selResults}&selCompare={$selCompare}&cu_name=$cuCode\" style=\"color:$classColor;\">$cuCode</a>";
318 
319  print <<< print_html
320  <tr class='$tr_class'>
321  <td nowrap>{$lookupString}</td>
322  <td>{$dom_value[0]}</td>
323  <td>{$dom_value[1]}</td>
324  <td>{$dom_value[2]}</td>
325  <td>{$dom_value[3]}</td>
326  <td>{$dom_value[4]}</td>
327  <td>{$dom_value[5]}</td>
328  <td>{$dom_value[6]}</td>
329  <td>{$dom_value[7]}</td>
330  <td>{$dom_value[8]}</td>
331  <td align='right'>\${$dom_value[9]}</td>
332  <td align='right'>\${$dom_value[11]}</td>
333  <td align='right'>\${$dom_value[12]}</td>
334  </tr>
335 print_html;
336  }
337  }
338  } else {
339  print "<tr colspan='13' class='$tr_class'><td>No Entries</td></tr>";
340  }
341 
342  // Show domains that changed from last month (but are in both months)
343  print "
344  <tr><td colspan='13'>&nbsp;</td></tr>
345  <tr class='hdr'>
346  <td colspan='13'>Changed Domains</td>
347  <tr> ";
348  if ( count( $outputChangeList ) > 0 ) {
349  $tr_class = "";
350  foreach ($outputChangeList as $key => $dom_value) {
351  $TTL_Clients++;
352  if (intval($dom_value[12]) > 0 || $rptShowZero) {
353  $TTL_Client_Info[0]++;
354  for ($idx=1; $idx <= 12; $idx++) {
355  $TTL_Client_Info[$idx] += intval($dom_value[$idx]);
356  }
357  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
358  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
359  if ( $needsLookup ) {
360  $lookupError++;
361  $classColor = "red";
362  } else {
363  $classColor = "";
364  }
365  $lookupString = "<a href=\"$self?action=fix_billing_mapping&domain={$dom_value[0]}&selResults={$selResults}&selCompare={$selCompare}&cu_name=$cuCode\" style=\"color:$classColor;\">$cuCode</a>";
366  print <<< print_html
367  <tr class='$tr_class'>
368  <td nowrap>{$lookupString}</td>
369  <td>{$dom_value[0]}</td>
370  <td>{$dom_value[1]}</td>
371  <td>{$dom_value[2]}</td>
372  <td>{$dom_value[3]}</td>
373  <td>{$dom_value[4]}</td>
374  <td>{$dom_value[5]}</td>
375  <td>{$dom_value[6]}</td>
376  <td>{$dom_value[7]}</td>
377  <td>{$dom_value[8]}</td>
378  <td align='right'>\${$dom_value[9]}</td>
379  <td align='right'>\${$dom_value[11]}</td>
380  <td align='right'>\${$dom_value[12]}</td>
381  </tr>
382 print_html;
383  }
384  }
385  } else {
386  print "<tr colspan='13' class='$tr_class'><td>No Entries</td></tr>";
387  }
388 
389  // Show domains that are new
390  print "
391  <tr><td colspan='13'>&nbsp;</td></tr>
392  <tr class='hdr'>
393  <td colspan='13'>Added Domains</td>
394  <tr> ";
395  if ( count( $outputAddList ) > 0 ) {
396  $tr_class = "";
397  foreach ($outputAddList as $key => $dom_value) {
398  $TTL_Clients++;
399  if (intval($dom_value[12]) > 0 || $rptShowZero) {
400  $TTL_Client_Info[0]++;
401  for ($idx=1; $idx <= 12; $idx++) {
402  $TTL_Client_Info[$idx] += intval($dom_value[$idx]);
403  }
404  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
405  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
406  if ( $needsLookup ) {
407  $lookupError++;
408  $classColor = "red";
409  } else {
410  $classColor = "";
411  }
412  $lookupString = "<a href=\"$self?action=fix_billing_mapping&domain={$dom_value[0]}&selResults={$selResults}&selCompare={$selCompare}&cu_name=$cuCode\" style=\"color:$classColor;\">$cuCode</a>";
413  print <<< print_html
414  <tr class='$tr_class'>
415  <td nowrap>{$lookupString}</td>
416  <td>{$dom_value[0]}</td>
417  <td>{$dom_value[1]}</td>
418  <td>{$dom_value[2]}</td>
419  <td>{$dom_value[3]}</td>
420  <td>{$dom_value[4]}</td>
421  <td>{$dom_value[5]}</td>
422  <td>{$dom_value[6]}</td>
423  <td>{$dom_value[7]}</td>
424  <td>{$dom_value[8]}</td>
425  <td align='right'>\${$dom_value[9]}</td>
426  <td align='right'>\${$dom_value[11]}</td>
427  <td align='right'>\${$dom_value[12]}</td>
428  </tr>
429 print_html;
430  }
431  }
432  } else {
433  print "<tr colspan='13' class='$tr_class'><td>No Entries</td></tr>";
434  }
435 
436  // Show domains that dropped off
437  //*** NOTE: dropped domains don't count in the total
438  print "
439  <tr><td colspan='13'>&nbsp;</td></tr>
440  <tr class='hdr'>
441  <td colspan='13'>Dropped Domains (not counted in totals)</td>
442  <tr> ";
443  if ( count( $outputDropList ) > 0 ) {
444  $tr_class = "";
445  foreach ($outputDropList as $key => $dom_value) {
446  if (intval($dom_value[12]) > 0 || $rptShowZero) {
447  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
448  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
449  if ( $needsLookup ) {
450  $lookupError++;
451  $classColor = "red";
452  } else {
453  $classColor = "";
454  }
455  $lookupString = "<a href=\"$self?action=fix_billing_mapping&domain={$dom_value[0]}&selResults={$selResults}&selCompare={$selCompare}&cu_name=$cuCode\" style=\"color:$classColor;\">$cuCode</a>";
456  print <<< print_html
457  <tr class='$tr_class'>
458  <td nowrap>{$lookupString}</td>
459  <td>{$dom_value[0]}</td>
460  <td>{$dom_value[1]}</td>
461  <td>{$dom_value[2]}</td>
462  <td>{$dom_value[3]}</td>
463  <td>{$dom_value[4]}</td>
464  <td>{$dom_value[5]}</td>
465  <td>{$dom_value[6]}</td>
466  <td>{$dom_value[7]}</td>
467  <td>{$dom_value[8]}</td>
468  <td align='right'>\${$dom_value[9]}</td>
469  <td align='right'>\${$dom_value[11]}</td>
470  <td align='right'>\${$dom_value[12]}</td>
471  </tr>
472 print_html;
473  }
474  }
475  } else {
476  print "<tr colspan='13' class='$tr_class'><td>No Entries</td></tr>";
477  }
478 
479  } else {
480  // ** PRINT ALL DOMAINS -- FOR THE MOST CURRENT MONTH/YEAR
481  foreach ($file1_domain as $key => $dom_value) {
482  $TTL_Clients++;
483  if (intval($dom_value[12]) > 0 || $rptShowZero) {
484  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
485  $TTL_Client_Info[0]++;
486  for ($idx=1; $idx <= 12; $idx++) {
487  $TTL_Client_Info[$idx] += intval($dom_value[$idx]);
488  }
489  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
490  if ( $needsLookup ) {
491  $lookupError++;
492  $classColor = "red";
493  } else {
494  $classColor = "";
495  }
496  $lookupString = "<a href=\"$self?action=fix_billing_mapping&domain={$dom_value[0]}&selResults={$selResults}&selCompare={$selCompare}&cu_name=$cuCode\" style=\"color:$classColor;\">$cuCode</a>";
497  print <<< print_html
498  <tr class='$tr_class'>
499  <td nowrap>{$lookupString}</td>
500  <td>{$dom_value[0]}</td>
501  <td>{$dom_value[1]}</td>
502  <td>{$dom_value[2]}</td>
503  <td>{$dom_value[3]}</td>
504  <td>{$dom_value[4]}</td>
505  <td>{$dom_value[5]}</td>
506  <td>{$dom_value[6]}</td>
507  <td>{$dom_value[7]}</td>
508  <td>{$dom_value[8]}</td>
509  <td align='right'>\${$dom_value[9]}</td>
510  <td align='right'>\${$dom_value[11]}</td>
511  <td align='right'>\${$dom_value[12]}</td>
512  </tr>
513 print_html;
514  }
515  }
516  }
517 
518  print <<< print_html
519  <tr class='hdr'><td colspan='13' align='center'>Total Client Information</td></tr>
520  <tr class='dtl'>
521  <td>&nbsp;</td>
522  <td>Total Clients: $TTL_Clients Total Clients Reported: {$TTL_Client_Info[0]}</td>
523  <td>{$TTL_Client_Info[1]}</td>
524  <td>{$TTL_Client_Info[2]}</td>
525  <td>{$TTL_Client_Info[3]}</td>
526  <td>{$TTL_Client_Info[4]}</td>
527  <td>{$TTL_Client_Info[5]}</td>
528  <td>{$TTL_Client_Info[6]}</td>
529  <td>{$TTL_Client_Info[7]}</td>
530  <td>{$TTL_Client_Info[8]}</td>
531  <td align='right'>\${$TTL_Client_Info[9]}</td>
532  <td align='right'>\${$TTL_Client_Info[11]}</td>
533  <td align='right'>\${$TTL_Client_Info[12]}</td>
534  </tr>
535  </table>
536  </td></tr></table>
537  </td></tr>
538 print_html;
539 
540  if ( $lookupError > 0 ) {
541  print "<tr><td style='text-align:center; color:red; font-size:larger; background-color:white;'>Domain lookup errors exist. Need to correct.</td></tr>";
542  }
543 
544  print "</table>";
545  print "</form>";
546  print "</body></html>";
547 
548  break;
549  case "RAW":
550  $outString = "";
551 
552  if (!isset($enddate) || trim($enddate) == "")
553  {
554  $reportMonth = substr( $rdate, 0, 6 );
555  $file1_domain= getDataFromFile($reportMonth);
556  printCSVFromData($file1_domain, $link, false);
557  }
558  else
559  {
560  $startDate= DateTime::createFromFormat("Ym", $rdate);
561  $endDate= intval(substr($enddate, 0, 6));
562  $realData= array();
563  while(true)
564  {
565  $month= intval($startDate->format("m"));
566  $year= intval($startDate->format("Y"));
567  $reportMonth= $month < 10 ? intval("{$year}0{$month}") : intval("{$year}{$month}");
568  if ($reportMonth > $endDate)
569  break;
570  $data= getDataFromFile($reportMonth, true);
571 
572  foreach($data as $cuKey => $record)
573  {
574  $thisKey= trim(strtolower($cuKey));
575  if (!isset($realData[$cuKey]))
576  {
577  $realData[$thisKey]= $record;
578  }
579  else
580  {
581  $colIndex= 0; // Skip over the first one which is the key.
582  foreach($record as $col)
583  {
584  if ($colIndex != 0)
585  $realData[$thisKey][$colIndex]+= $col;
586  $colIndex++;
587  }
588  }
589  }
590  $startDate->setDate($year, $month+1, 1);
591  }
592 
593  printCSVFromData(array_values($realData), $link, false);
594  }
595 
596  break;
597  default:
598  $report_value = "SELECT";
599  // ** This is the main page that will be used to select the report to view
600  cu_header("Select Email Domain Result Files");
601 
602  if ($retMsg != '') {
603  $print_msg = "<tr><td colspan='2'>$retMsg</td></tr>";
604  }
605  print <<< print_html
606  <a href="$infourl/hcuadm/cuilist.prg" target="parent">Credit Union List</a><br />
607  <form name='domain_report' action="$self" method="post" enctype="multipart/form-data">
608  <input type="hidden" name="rv" value="$report_view">
609  <table cellpadding="3" cellspacing="0" border="0" width="300" class='dmsbg'><tr><td>
610  <table cellpadding="1" cellspacing="0" border="0" width="100%" bgcolor="white">
611  <tr>
612  <td colspan="4" class="bar" align="center">
613  Email Domain Report
614  </td>
615  </tr>
616  <tr>
617  <td colspan="4" class="bar" align="center">
618  Upload Everyone.net Details File
619  </td>
620  </tr>
621  <tr>
622  <td nowrap align="right" class="hdr" valign='top'>
623  File to Upload:
624  </td>
625  <td colspan="3" nowrap class='dtl' valign='top'>
626  <input type='file' name='uplFileName' />
627  </td>
628  </tr>
629  <tr>
630  <td nowrap align="right" class="hdr" valign='top'>
631  Month
632  </td>
633  <td colspan="3" nowrap class='dtl' valign='top'>
634  <select name='uplMonth'>
635  <option value='01' {$month_select['Jan']}>Jan</option>
636  <option value='02' {$month_select['Feb']}>Feb</option>
637  <option value='03' {$month_select['Mar']}>Mar</option>
638  <option value='04' {$month_select['Apr']}>Apr</option>
639  <option value='05' {$month_select['May']}>May</option>
640  <option value='06' {$month_select['Jun']}>Jun</option>
641  <option value='07' {$month_select['Jul']}>Jul</option>
642  <option value='08' {$month_select['Aug']}>Aug</option>
643  <option value='09' {$month_select['Sep']}>Sep</option>
644  <option value='10' {$month_select['Oct']}>Oct</option>
645  <option value='11' {$month_select['Nov']}>Nov</option>
646  <option value='12' {$month_select['Dec']}>Dec</option>
647  </select>
648  <select name='uplYear'>
649  <option value='2014' {$year_select['2014']}>2014</option>
650  <option value='2015' {$year_select['2015']}>2015</option>
651  <option value='2016' {$year_select['2016']}>2016</option>
652  <option value='2017' {$year_select['2017']}>2017</option>
653  </select>
654  </td>
655  </tr>
656  <tr>
657  <td nowrap align="right" class="hdr" valign='top'>
658  &nbsp;
659  </td>
660  <td colspan="3" nowrap class='dtl' valign='top'>
661  <input type="submit" name="btnUpload" Value="Upload File">
662  </td>
663  </tr>
664  $print_msg
665  <tr>
666  <td colspan="4" class="bar" align="center">
667  View Report
668  </td>
669  </tr>
670  <tr>
671  <td nowrap align="right" class="hdr" valign='middle'>
672  Select Results File
673  </td>
674  <td nowrap class='dtl' valign='top'>
675  <select size=6 name='selResults'>
676 print_html;
677  $cnt = 0;
678  foreach ($results_list as $value) {
679  $cnt++;
680  $year = substr($value, 0, 4);
681  $mon = intval(substr($value, 4, 2));
682  $disp_value = $month[($mon - 1)] . " " . $year;
683  print "<option value='$value' " . ($cnt == 1 ? "SELECTED" : "") . ">$disp_value</option>\n";
684  }
685  print <<< print_html
686  </select>
687  </td>
688  <td nowrap align="right" class="hdr" valign='middle'>
689  Compare Changes With
690  </td>
691  <td nowrap class='dtl' valign='top'>
692  <select size=6 name='selCompare'>
693  <option value='' SELECTED>None</option>
694 print_html;
695  $cnt = 0;
696  foreach ($results_list as $value) {
697  $cnt++;
698  $year = substr($value, 0, 4);
699  $mon = intval(substr($value, 4, 2));
700  $disp_value = $month[($mon - 1)] . " " . $year;
701  print "<option value='$value'>$disp_value</option>\n";
702  }
703  print <<< print_html
704  </select>
705  </td>
706  </tr>
707  <tr>
708  <td class='dtl'>&nbsp;</td>
709  <td colspan="3" nowrap class='dtl'>
710  <input type="checkbox" name="chkShowZero" Value="1">Show Zero Values
711  </td>
712  </tr>
713  <tr>
714  <td class='dtl'>&nbsp;</td>
715  <td colspan="3" nowrap class='dtl'>
716  <input type="submit" name="btnCont" Value="View Report">
717  </td>
718  </tr>
719  </table>
720  </td></tr></table>
721  </form></body></html>
722 print_html;
723  break;
724  }
725 
726 function getDataFromFile($reportMonth, $asMap=false)
727 {
728  global $xrc_prefix, $results_dir;
729 
730  // for the csv file just show the CUs that had activity (i.e. no comparison, no zero activity)
731  $file1_domain = Array();
732  $result_file1 = $xrc_prefix . $reportMonth;
733  $rptShowZero = false;
734 
735  if (file_exists($results_dir . $result_file1))
736  {
737  $file1_results = file($results_dir . $result_file1);
738  foreach($file1_results as $key => $value)
739  {
740  $record= explode("\t", $value);
741 
742  if ($asMap)
743  $file1_domain[trim(strtolower($record[0]))]= $record;
744  else
745  $file1_domain[] = $record;
746  }
747  }
748 
749  if (count($file1_domain[0]) == 11)
750  {
751  for ( $idxAdjustRow = 0; $idxAdjustRow < count($file1_domain); $idxAdjustRow++)
752  {
753  // ** Now reposition the data in the columns
754  $file1_domain[$idxAdjustRow][12] = $file1_domain[$idxAdjustRow][9];
755  $file1_domain[$idxAdjustRow][11] = $file1_domain[$idxAdjustRow][8];
756  $file1_domain[$idxAdjustRow][9] = $file1_domain[$idxAdjustRow][6];
757  // ** ReSET COlumn values -- ALways be zero for past months
758  $file1_domain[$idxAdjustRow][6] = 0;
759  $file1_domain[$idxAdjustRow][7] = 0;
760  $file1_domain[$idxAdjustRow][8] = 0;
761  }
762  }
763 
764  return $file1_domain;
765 }
766 
767 function printCSVFromData($file1_domain, $link, $showErrors)
768 {
769  $TTL_Clients = 0;
770  $TTL_Client_Info = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
771 
772  // start a header
773  $outString .= "CU,Domain Name,TTL ACCT,REG,250M,1G,2G,100 3Yr,100 5Yr,100 7Yr,Cost,Prem,Total Bill\r\n";
774 
775  // ** PRINT ALL DOMAINS -- FOR THE MOST CURRENT MONTH/YEAR
776  foreach ($file1_domain as $key => $dom_value)
777  {
778  $TTL_Clients++;
779  if (intval($dom_value[12]) > 0)
780  {
781  $TTL_Client_Info[0]++;
782  for ($idx=1; $idx <= 12; $idx++)
783  {
784  $TTL_Client_Info[$idx] += intval($dom_value[$idx]);
785  }
786  $cuCode = LookupCUCode( $link, $dom_value[0], $needsLookup );
787 
788  if ($needsLookup && $showErrors)
789  {
790  $outString .= "E,Error: Need Lookup for {$dom_value[0]},,,,,,,,,,,,\r\n";
791  }
792 
793  $outString .= "$cuCode,{$dom_value[0]},{$dom_value[1]},{$dom_value[2]},{$dom_value[3]}," .
794  "{$dom_value[4]},{$dom_value[5]},{$dom_value[6]},{$dom_value[7]},{$dom_value[8]}," .
795  "{$dom_value[9]},{$dom_value[11]},{$dom_value[12]}\r\n";
796  }
797  }
798 
799  $outString .= "T,Total Clients: $TTL_Clients Total Clients Reported: {$TTL_Client_Info[0]}," .
800  "{$TTL_Client_Info[1]},{$TTL_Client_Info[2]},{$TTL_Client_Info[3]},{$TTL_Client_Info[4]}," .
801  "{$TTL_Client_Info[5]},{$TTL_Client_Info[6]},{$TTL_Client_Info[7]},{$TTL_Client_Info[8]}," .
802  "{$TTL_Client_Info[9]},{$TTL_Client_Info[11]},{$TTL_Client_Info[12]}\r\n";
803 
804  header( "Content-length: " . strlen($outString) );
805  header( "Content-type: application/octetstream" );
806  header( "Content-disposition: inline; filename=\"download.csv\"" );
807 
808  print $outString;
809  exit;
810 }
811 
812 function ProcessFile($p_results_dir, $p_xrc_prefix, $p_upl_filename, $p_upl_month, $p_upl_year) {
813 
814  // ** This function will process the file simi
815  $xrc_logfile = "";
816  $xrc_directory = $p_results_dir;
817  $xrc_prefix = $p_xrc_prefix;
818 
819  $xrc_filename = $xrc_prefix . $p_upl_year . $p_upl_month;
820 
821  if (is_dir($xrc_directory)) {
822  $rpt_fh = @fopen($xrc_directory . $xrc_filename, "w");
823  }
824 
825  if (!$rpt_fh) {
826  return "PROBLEM OPENING FILE FOR REPORT! STOPPING";
827  }
828 
829 // *** DELETE OLDER FILES
830  $RemoveLogDate = date("Ym", mktime(0, 0, 0, date("m") - 15, date("d"), date("Y")));
831  if (is_dir($xrc_directory)) {
832  if ($dir_hndl = opendir($xrc_directory)) {
833  while (($file = readdir($dir_hndl)) !== false) {
834  if (filetype($xrc_directory . $file) == "file" && (substr($file, 0, strlen($xrc_prefix)) == $xrc_prefix)) {
835  if (substr($file, strlen($xrc_prefix)) <= $RemoveLogDate) {
836  // ** Delete File
837  unlink($xrc_directory . $file);
838  }
839  }
840  }
841  closedir($dir_hndl);
842  }
843  }
844 
845 // ** BUILD DOMAIN TOTALS
846  $row = 1;
847  $current_domain = "";
848  $client_count = 0;
849  $client_users = Array();
850  $mailsize_100 = 0;
851  $mailsize_250 = 0;
852  $mailsize_1000 = 0;
853  $mailsize_2000 = 0;
854  $mailsize_100_3yrArchive = 0;
855  $mailsize_100_5yrArchive = 0;
856  $mailsize_100_7yrArchive = 0;
857 
858  if (($handle = fopen($_FILES['uplFileName']['tmp_name'], "r")) !== FALSE) {
859  /*
860  * Build domain/user list first - I found a problem with users that can span multiple types
861  * if they change offers in the month.
862  * So first I will build this Alternate Array - Which will consist of {Domain} and username=>offer as the value
863  * The costlier item will be counted toward the total
864 
865  *
866  */
867  $row = 1;
868  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
869 
870  if ($row > 1) {
871  $row_domain = trim($data[0]);
872  $row_user = trim($data[1]);
873  $row_offer = trim($data[3]);
874 
875  // ** ALIAS ACCOUNT NOT FOUND -- AND NOT DELETED
876  if (strpos($row_user, "ALIAS ACCOUNT") === false && trim($data[2]) == '') {
877  // ** NOT AN ALIAS -- NOT DELETED
878  // * Check if it already exists
879  if ($client_users[$row_domain][$row_user] != '') {
880  // ** ALREADY HERE
881  if (RetrieveOfferLevel($row_offer) > $client_users[$row_domain][$row_user]) {
882  // ** OVERWRITE with the MORE premium offer
883  $client_users[$row_domain][$row_user] = $row_offer;
884  }
885  } else {
886  // ** NEW TO LIST
887  $client_users[$row_domain][$row_user] = $row_offer;
888  }
889  }
890  }
891  $row++;
892  }
893  fclose($handle);
894 
895  // ** NOW LOOP THROUGH THE KEYS OF client_list and count the mailsize information
896  foreach ($client_users as $current_domain => $domain_users) {
897  $client_count = 0;
898  $mailsize_100 = 0;
899  $mailsize_250 = 0;
900  $mailsize_1000 = 0;
901  $mailsize_2000 = 0;
902  $mailsize_100_3yrArchive = 0;
903  $mailsize_100_5yrArchive = 0;
904  $mailsize_100_7yrArchive = 0;
905 
906 
907  // ** domain_users should NOW be an array
908  foreach ($domain_users as $user_name => $user_offer) {
909  $client_count++;
910 
911  switch (trim($user_offer)) {
912  // ** 100 MB
913  case "100MB Package (with AV+AS) + Stor 10MB + Arch 5d":
914  $mailsize_100++;
915  break;
916  // ** 250 MB
917  case "250MB Package (with AV+AS) + Stor 10MB + Arch 5d":
918  $mailsize_250++;
919  break;
920  // ** 1 G
921  case "1000MB email + 500 MB storage + Arch 90d":
922  case "1000MB Package (with AV+AS) + Stor 10MB + Arch 5d":
923  $mailsize_1000++;
924  break;
925  // ** 2 G
926  case "2000MB Package (with AV+AS) + Stor 10MB + Arch 5d":
927  case "2GB Email + Stor 500MB + Arch 90d":
928  $mailsize_2000++;
929  break;
930  // ** 100 MB 3 YR
931  case "100MB Email + 10MB Stor + 3yr Arch":
932  $mailsize_100_3yrArchive++;
933  break;
934  // ** 100 MB 5 YR
935  case "100MB Email + 10MB Stor + 5yr Arch":
936  $mailsize_100_5yrArchive++;
937  break;
938  // ** 100 MB 7 YR
939  case "100MB Email + 10MB Stor + 7yr Arch":
940  $mailsize_100_7yrArchive++;
941  break;
942  }
943  }
944  // ** WRITE DOMAIN RESULTS
945  $domain_results[] = array($current_domain, $client_count, $mailsize_100, $mailsize_250, $mailsize_1000, $mailsize_2000, $mailsize_100_3yrArchive, $mailsize_100_5yrArchive, $mailsize_100_7yrArchive);
946  }
947  } else {
948  return "Unable to READ the file";
949  }
950 
951 
952 // ** WRITE DOMAIN TOTALS TO FILE
953  if (is_array($domain_results)) {
954  foreach ($domain_results as $dom_key=>$dom_value) {
955  $dom_accts = 0;
956  $dom_accts_prem = 0;
957  $dom_credit = 0; // Changed to zero. Credit is removed.
958  $dom_total_bill = 0;
959  $tr_class = ($tr_class == "odd_small" ? "even_small" : "odd_small");
960  // ** Add up the items in each bucket
961  $dom_accts = intval($dom_value[2]) + intval($dom_value[3]) + intval($dom_value[4]) + intval($dom_value[5]) + intval($dom_value[6]) + intval($dom_value[7]) + intval($dom_value[8]);
962  // ** Add up the premium account types
963  $dom_accts_prem = (intval($dom_value[3]) * 1) + (intval($dom_value[4]) * 2) + (intval($dom_value[5]) * 3) + (intval($dom_value[6]) * 3) + (intval($dom_value[7]) * 4) + (intval($dom_value[8]) * 5);
964  // ** Premium is ALWAYS ADDED TO THE PRICE -- Credit does not affect the premium
965  $dom_total_bill = intval($dom_accts) + intval($dom_accts_prem);
966 
967  // ** WRITE LINE TO FILE
968  fwrite ($rpt_fh, "{$dom_value[0]}\t{$dom_value[1]}\t{$dom_value[2]}\t{$dom_value[3]}\t{$dom_value[4]}\t{$dom_value[5]}\t{$dom_value[6]}\t{$dom_value[7]}\t{$dom_value[8]}\t{$dom_accts}\t{$dom_credit}\t{$dom_accts_prem}\t{$dom_total_bill}\t\n");
969  }
970  }
971 // ** SHOULD BE DONE
972  fclose($rpt_fh);
973 
974  return "Report Uploaded for $p_upl_month $p_upl_year completed.";
975 }
976 
977 
978 function RetrieveOfferLevel($p_offer) {
979  // ** This function will simply return a numeric level.. I will hopefully use this to compare cost later
980 
981 
982  $retLevel = 0;
983 
984  switch ($row_offer) {
985  // ** 100 MB
986  case "100MB Package (with AV+AS) + Stor 10MB + Arch 5d":
987  $retLevel = 0;
988  break;
989  // ** 250 MB
990  case "250MB Package (with AV+AS) + Stor 10MB + Arch 5d":
991  $retLevel = 1;
992  // ** 1 G
993  case "1000MB email + 500 MB storage + Arch 90d":
994  $retLevel = 2;
995  break;
996  // ** 2 G
997  case "2000MB Package (with AV+AS) + Stor 10MB + Arch 5d":
998  $retLevel = 3;
999  break;
1000  // ** 100 MB 3 YR
1001  case "100MB Email + 10MB Stor + 3yr Arch":
1002  $retLevel = 4;
1003  break;
1004  // ** 100 MB 5 YR
1005  case "100MB Email + 10MB Stor + 5yr Arch":
1006  $retLevel = 5;
1007  break;
1008  // ** 100 MB 7 YR
1009  case "100MB Email + 10MB Stor + 7yr Arch":
1010  $retLevel = 6;
1011  break;
1012 
1013  }
1014  return $retLevel;
1015 }
1016 
1017 // Look in cuinfo to see if can determine the credit union code based on the domain name.
1018 function LookupCUCode( $dbh, $domain, &$errorFlag ) {
1019 
1020  // strip off the right side
1021  $parts = explode( ".", $domain );
1022  $searchDomain = $parts[0];
1023 
1024  $sql = "SELECT cu_name FROM cubilldomainmap WHERE domain = '$domain'";
1025 
1026  $sth = db_query($sql, $dbh);
1027 
1028  // find all occurances
1029  $iRow = 0;
1030  $userList = array();
1031  while ( $userRow = db_fetch_assoc($sth, $iRow++) ) {
1032  $userList[] = trim($userRow["cu_name"]);
1033  }
1034 
1035  if ( !count( $userList ) ) {
1036  // if don't find the code then look for it in the cuinfo table and if found add it to the lookup
1037  $sql = "SELECT user_name FROM cuinfo WHERE home_page_url like '%$searchDomain%'";
1038  $sth = db_query($sql, $dbh);
1039 
1040  // find all occurances
1041  $iRow = 0;
1042  $userList = array();
1043  while ( $userRow = db_fetch_assoc($sth, $iRow++) ) {
1044  $userList[] = trim($userRow["user_name"]);
1045  }
1046 
1047  if ( count( $userList ) == 1 ) {
1048  // just add it because we didn't find it on the first query
1049  $sql = "INSERT INTO cubilldomainmap VALUES ( '$userList[0]', '$domain' )";
1050  $sth = db_query($sql, $dbh);
1051  }
1052  }
1053 
1054  // use flag to signify if single CU was found or not
1055  $errorFlag = -1;
1056  if ( count( $userList ) > 1 ) {
1057  $retString = implode( ",", $userList );
1058  } else if ( count( $userList ) > 0 ) {
1059  $retString = $userList[0];
1060  $errorFlag = 0;
1061  } else {
1062  $retString = "Not found";
1063  }
1064 
1065  return $retString;
1066 } // end LookupCUCode
1067 
1068 // Get list of all credit unions
1069 function GetCUCodeList( $dbh ) {
1070  global $SYS_TYPE_CLOSED;
1071 
1072  $sql = "SELECT user_name, system_options FROM cuinfo ORDER BY user_name ";
1073 
1074  $sth = db_query($sql, $dbh);
1075 
1076  // find all occurances
1077  $iRow = 0;
1078  $cuList = array();
1079  while ( $cuRow = db_fetch_assoc($sth, $iRow++) ) {
1080  if ( ($cuRow["system_options"] & $SYS_TYPE_CLOSED) == 0 ) {
1081  $cuList[] = trim($cuRow["user_name"]);
1082  }
1083  }
1084 
1085  return $cuList;
1086 } // end GetCUCodeList
1087 
Generated by   doxygen 1.8.15