invoice.php
1    <?php 
2    echo "<h3>Source tables</h3>"; 
3     
4    echo "<b>invoiceheader</b><br> 
5    <pre> 
6    invoicenum | invoicedate | invoiceamount 
7    -----------+-------------+-------------- 
8    00551198   | 1/1/2014    | $150.5 
9    00551199   | 1/2/2014    | $10 
10   </pre>"; 
11    
12   echo "<b>invoicedetail</b><br> 
13   <pre> 
14   invoicenum | trackingno         | detailamount 
15   -----------+--------------------+------------- 
16   00551198   | 1Z2F12346861507266 | $50 
17   00551198   | 1Z2F12346861507267 | $80 
18   00551198   | 1Z2F12346861507268 | $20.5 
19   00551199   | 1Z2F12346861503423 | $10.5 
20   </pre>"; 
21   echo "<b>invoicecharges</b><br> 
22   <pre> 
23   invoicenum | trackingno         | chargetype | chargeamount 
24   -----------+--------------------+------------+------------- 
25   00551198   | 1Z2F12346861507266 | FRT        | $40 
26   00551198   | 1Z2F12346861507266 | FUE        | $11 
27   00551198   | 1Z2F12346861507267 | FRT        | $65 
28   00551198   | 1Z2F12346861507267 | FUE        | $17 
29   00551198   | 1Z2F12346861507268 | FRT        | $10 
30   00551198   | 1Z2F12346861507268 | FUE        | $5 
31   00551198   | 1Z2F12346861507268 | HAZ        | $5.5 
32   00551199   | 1Z2F12346861503423 | FRT        | $8 
33   00551199   | 1Z2F12346861503423 | FUE        | $2.5 
34   </pre>"; 
35    
36   error_reporting(E_ALL); 
37   ini_set('display_errors', 1); 
38   $status = array('code'=>0, 'message'=>''); 
39    
40   try { 
41       $db = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
42       $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
43       $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
44   } 
45   catch(PDOException $e) 
46   { 
47       $status['code'] = $e->getCode(); 
48       $status['message'] = $e->getMessage(); 
49       print "Error code:".$status['code']." (".$status['message'].")"; 
50       exit(); 
51   } 
52    
53   // Report 1 
54    
55   $start_date = '2014-1-1'; 
56   $end_date = '2014-1-2'; 
57   try { 
58       $sql = "SELECT * FROM invoiceheader where invoicedate between ? and ?"; 
59       $stmt = $db->prepare($sql); 
60       $stmt->execute(array($start_date, $end_date)); 
61       $result = $stmt->fetchAll(PDO::FETCH_ASSOC); 
62   } 
63   catch(PDOException $e) 
64   { 
65       $status['code'] = $e->getCode(); 
66       $status['message'] = $e->getMessage(); 
67       print "Error code:".$status['code']." (".$status['message'].")"; 
68       exit(); 
69   } 
70   $count = count($result); 
71   $sum = 0; 
72   echo "<h3>Report 1</h3>"; 
73   echo " 
74   <p> 
75   For specified time period print all invoices<br> 
76   for each show invoicenum, invoice date and invoice amount<br> 
77   Print a total line: # of invoices and total amount for the period 
78   </p> 
79   "; 
80   echo "<table border = '1' style='border-collapse:collapse;'>"; 
81   echo "<tr><th>invoice #</th><th>invoice date</th><th>invoice amount</th></tr>"; 
82   foreach ($result as $row){ 
83       echo "<tr><td>".$row["invoicenum"]."</td><td>".$row["invoicedate"]."</td><td>".$row["invoiceamount"]."</td></tr>"; 
84       $sum += $row["invoiceamount"]; 
85   } 
86   echo "</table><br>"; 
87   echo "Total # of invoices: $count, total amount: $sum"; 
88    
89   try { 
90       $sql = "SELECT i.*, SUM(d.detailamount) as detailamount, (i.invoiceamount-SUM(d.detailamount)) as discrepancy ". 
91           "FROM invoiceheader i,invoicedetail d where (invoicedate between ? and ?) and i.invoicenum=d.invoicenum group by d.invoicenum"; 
92       $stmt = $db->prepare($sql); 
93       $stmt->execute(array($start_date, $end_date)); 
94       $result = $stmt->fetchAll(PDO::FETCH_ASSOC); 
95   } 
96   catch(PDOException $e) 
97   { 
98       $status['code'] = $e->getCode(); 
99       $status['message'] = $e->getMessage(); 
100      print "Error code:".$status['code']." (".$status['message'].")"; 
101      exit(); 
102  } 
103  echo "<h3>Report 2</h3>"; 
104  echo " 
105  <p> 
106  For specified time period print all invoices where the invoice amount<br>  
107  does not match the sum of all detailamount column values for this invoice<br> 
108  for each show invoicenum, invoice date and invoice amount,<br> 
109  detailamount total and the discrepancy amount 
110  </p> 
111  "; 
112  echo "<table border = '1' style='border-collapse:collapse;'>"; 
113  echo "<tr><th>invoice #</th><th>invoice date</th><th>invoice amount</th><th>detail amount total</th><th>discrepancy</th></tr>"; 
114  foreach ($result as $row){ 
115      echo "<tr><td>".$row["invoicenum"]."</td><td>".$row["invoicedate"]."</td><td>".$row["invoiceamount"]."</td><td>".$row["detailamount"]."</td><td>".$row["discrepancy"]."</td></tr>"; 
116      $sum += $row["invoiceamount"]; 
117  } 
118  echo "</table>"; 
119   
120  $sql = " 
121  SELECT i.invoicenum, i.invoicedate, t.trackingno, t.sum1 as detailamount, t.sum2 as chargeamount,(t.sum1-t.sum2) as discrepancy 
122  FROM invoiceheader i, 
123  (SELECT d.invoicenum, d.trackingno, sum( d.detailamount ) AS sum1, c.sum2 
124  FROM invoicedetail d, 
125  (select invoicenum, trackingno,sum( chargeamount ) as sum2 from invoicecharges GROUP BY invoicenum, trackingno) c 
126  WHERE d.invoicenum = c.invoicenum 
127  AND d.trackingno = c.trackingno 
128  GROUP BY d.invoicenum, d.trackingno) t 
129  where invoicedate between ? and ? and i.invoicenum=t.invoicenum and (t.sum1-t.sum2) != 0 
130  "; 
131  try { 
132      $stmt = $db->prepare($sql); 
133      $stmt->execute(array($start_date, $end_date)); 
134      $result = $stmt->fetchAll(PDO::FETCH_ASSOC); 
135  } 
136  catch(PDOException $e) 
137  { 
138      $status['code'] = $e->getCode(); 
139      $status['message'] = $e->getMessage(); 
140      print "Error code:".$status['code']." (".$status['message'].")"; 
141      exit(); 
142  } 
143   
144  echo "<h3>Report 3</h3>"; 
145  echo " 
146  <p> 
147  For specified time period print all tracking numbers  where the detailamount does not match <br> 
148  the sum of all chargemount column values for this invoice and trackingno<br> 
149  for each show invoicenum, invoice date, trackingno, detailamount, chargeamount<br>  
150  total and the discrepancy amount 
151  </p> 
152  "; 
153  echo "<table border = '1' style='border-collapse:collapse;'>"; 
154  echo "<tr><th>invoice #</th><th>invoice date</th><th>tracking #</th><th>detail amount total</th><th>charges amount total</th><th>discrepancy</th></tr>"; 
155  foreach ($result as $row){ 
156      echo "<tr><td>".$row["invoicenum"]."</td><td>".$row["invoicedate"]."</td><td>".$row["trackingno"]."</td><td>".$row["detailamount"]."</td><td>".$row["chargeamount"]."</td><td>".$row["discrepancy"]."</td></tr>"; 
157  } 
158  echo "</table><br>";