Forums

Bob - I have a report that I use in cfv4, and it works great there. I can't seem to get the report to work in cfv5.

Form Input - works fine...although the calendars are black background which I'm not a fan of...but it works
[attachment=1]cfv5_paypal_forminput.png[/attachment]

Result - Data not showing
[attachment=0]cfv5_paypal_report.png[/attachment]

Custom code on submit

?php
	  $startdate=$form->data['startdate'];
	  $enddate=$form->data['enddate'];
	  echo "Paypal Report: $startdate thru $enddate";
	  $db = JFactory::getDBO();
	  $query = $db->getQuery(true);   
	       $query = "SELECT 
	
	(select sum(p.order_product_quantity * p.order_product_price) FROM ...hikashop_order_product as p WHERE p.order_id=o.order_id 
			AND p.order_product_name not like '%Membership%'
                        AND p.order_product_name not like '%Campaign%'
                        AND p.order_product_name not like '%SAT / ACT%'
			AND p.order_product_name not like '%Donation%'
		) as spiritwear,
	
	(select sum(p.order_product_quantity * p.order_product_price) FROM ..._hikashop_order_product as p WHERE p.order_id=o.order_id AND p.order_product_name like '%Membership%') as membership, 
		
	(select sum(p.order_product_quantity * p.order_product_price) FROM ...hikashop_order_product as p WHERE p.order_id=o.order_id AND p.order_product_name like '%Donation%') as donation,

       (select sum(p.order_product_quantity * p.order_product_price) FROM ...hikashop_order_product as p WHERE p.order_id=o.order_id AND p.order_product_name like '%Campaign%') as campaign,

      (select sum(p.order_product_quantity * p.order_product_price) FROM ...hikashop_order_product as p WHERE p.order_id=o.order_id AND p.order_product_name like '%SAT / ACT%') as satact,
order_discount_price as discount,
order_full_price as total

	
	 FROM ...hikashop_order as o
	
	 WHERE o.order_status IN ('confirmed','shipped') AND o.order_payment_method='paypal' AND from_unixtime(o.order_created)>='$startdate 00:00:00' AND from_unixtime(o.order_created)<='$enddate 23:59:59'
AND exists (SELECT 1 FROM ...hikashop_history where history_reason='automatic payment notification received' and history_order_id=order_id)
	
	";  
	   $db->setQuery($query);
	   $data = $db->loadAssocList ();
	   ?>
	
	<table class="admin list" style="border:1px solid #dddddd;padding:10px;">
	<tbody>
	       <tr style='border:1px solid #cccccc;'>
	    <th style='padding:10px;'>Spirit Wear</th>
	    <th style='padding:10px;'>Membership</th>
	    <th style='padding:10px;'>Donation</th>
	    <th style='padding:10px;'>Sun Shade</th>
	    <th style='padding:10px;'>SAT/ACT</th>
	    <th style='padding:10px;'>Discount</th>    
	    <th style='padding:10px;'>Total</th>    
	   </tr>
	<?php
	
	
	function showdata($data,$isdollar) {
	   if (!$isdollar) {
		echo "<td style='padding:5px;'>".$data."</td>";
	   }
	   else {
		if(!($data+0)) {
		  $amount="";
		}
	        else {
		  $amount=sprintf("%.2f",$data);
	        }
	        echo "<td style='text-align:center;padding:5px;'>".$amount."</td>";
	
	   }
	}
	
	foreach($data as $detail) {
	   echo "<tr style='border:1px solid #cccccc;'>";
	   $fields=array('spiritwear','membership','donation','campaign','satact','discount','total');
	   foreach ($fields as $f) {
		showdata($detail[$f],1);
		$subtotal[$f]+=$detail[$f];
	   }
	
//	showdata($detail['spiritwear'],1);
//	showdata($detail['membership'],1);
//	showdata($detail['donation'],1);
//	showdata($detail['campaign'],1);
//	showdata($detail['satact'],1);
//	showdata($detail['discount'],1);
//	showdata($detail['total'],1);

	//$grandtotal=$detail['spiritwear']+$detail['membership']+$detail['donation']+$detail['campaign']+$detail['satact'];
	//showdata($grandtotal,1);
	
	echo "</tr>";
	}
	// total row
	echo "<tr style='border:2px solid #000000;'>";
	foreach ($fields as $f) {
		showdata($subtotal[$f],1);
	}
	echo "</tr>";
	?>
	</tbody>
	   </table>

Would you be able to take a look at it? Would love to buy you a beer / coffee for any assist!!! 😀

Hi mduda,

I don't' see any obvious reason why the code would not work in CFv5.

By all means email or PM me the site URL, the form name, and a SuperAdmin login and I'll take a look.

Bob

Hi mduda,

I found an extra space in this line before the (

 $data = $db->loadAssocList ();

With that removed (and the code re-formatted a bit) it appears to run without an error - though I don't actually see any data in the listing.

Bob

Thank Bob. The form ran before without errors, I just can't seem to get any data...hmmm.

Do you think it's possible that the date field on the form is not setup correctly?