Help with CSV Export

[Danse]

Hi there,

 

I'm new to WHMCS and i need a custom CSV export for my Billing / Booking System.

 

I have some Problems with exports.

 

1. The GOAL CSV would be this on: - Removed -

2. The Screenshots show actually development (low success)

 

My current attempt is as following:

 

<?php


# Report Table of Data Column Headings - should be an array of values
$reportdata["tableheadings"] = array("Satzart","Kundennummer","Firma Nr","Rechnungsdatum","Rechnungsnummer","Nettobetrag voller Umsatzsteuersatz","Steuer zum vollen Umsatzsteuersatz","Nettobetrag halber Umsatzsteuersatz","Steuer zum halben Umsatzsteuersatz","Umsätze innergemeinschaftliche Lieferung","Umsätze Export","Sonstige Umsätze: Konto Nr.","Sonstige Umsätze: Betrag","Währung (ISO-Codes)","Gegenkonto","Rechnungsart","Belegtext","Zahlungsbedingung","KontoVoll","KontoHalb","KontoEU","KontoExport","Storno","Schlussrechnung","Erl_sart","System","Verrechnen","Kostenstelle","Lastschrift-Ausführung am");

# Report Table Values - one of these lines for each row you want in the table
# should be an array of values to match the column headings
#$reportdata["tablevalues"][] = array("CMXUMS","User ID","1","date"=>"Creation Date","invoicenum"=>"Invoice Number","subtotal"=>"Subtotal","","","","","","","","","","","clientname"=>"Client Name","paymentmethod"=>"Payment Method","","","","","","","","","","","");


if (!defined("WHMCS"))
   die("This file cannot be accessed directly");

# PUNKT1 <----
$filterfields = array("CMXUMS","User ID","1","date"=>"Creation Date","invoicenum"=>"Invoice Number","subtotal"=>"Subtotal","","","","","","","","","","","clientname"=>"Client Name","paymentmethod"=>"Payment Method","","","","","","","","","","","");


#$reportdata["description"] = $reportdata["headertext"] = '';

$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
if (!is_array($incfields)) $incfields = array();
if (!is_array($filterfield)) $filterfield = array();
if (!is_array($filtertype)) $filtertype = array();
if (!is_array($filterq)) $filterq = array();

if (!$print) {

   $reportdata["description"] = "This report can be used to generate a custom export of invoices by applying up to 5 filters. CSV Export is available via the download link at the bottom of the page.";

   $reportdata["headertext"] = '<form method="post" action="reports.php?report='.$report.'">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
   $i=0;
   foreach ($filterfields AS $k=>$v) {
       $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="'.$k.'" id="fd'.$k.'"';
       if (in_array($k,$incfields)) $reportdata["headertext"] .= ' checked';
       $reportdata["headertext"] .= ' /> <label for="fd'.$k.'">'.$v.'</label></td>'; $i++;
       if (($i%5)==0) $reportdata["headertext"] .= '</tr><tr>';
   }
   $reportdata["headertext"] .= '</tr></table></td></tr>';

   for ( $i = 1; $i <= 5; $i ++ ) {
       $reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter '.$i.'</td><td class="fieldarea"><select name="filterfield['.$i.']"><option value="">None</option>';
       foreach ($filterfields AS $k=>$v) {
           $reportdata["headertext"] .= '<option value="'.$k.'"';
           if (isset($filterfield[$i]) && $filterfield[$i]==$k) $reportdata["headertext"] .= ' selected';
           $reportdata["headertext"] .= '>'.$v.'</option>';
       }
       $reportdata["headertext"] .= '</select> <select name="filtertype['.$i.']"><option>Exact Match</option><option value="like"';
       if (isset($filtertype[$i]) && $filtertype[$i]=="like") $reportdata["headertext"] .= ' selected';
       $reportdata["headertext"] .= '>Containing</option></select> <input type="text" name="filterq['.$i.']" size="30" value="'.(isset($filterq[$i])?$filterq[$i]:'').'" /></td></tr>';
   }
   $reportdata["headertext"] .= '</table>
<p align="center"><input type="submit" value="Filter" /></p>
</form>';

}

if (count($incfields)) {

   $filters = array();
   foreach ($filterfield as $i => $val) {
       if ($val && array_key_exists($val, $filterfields)) {
           if ($val == 'clientname') {
               $val = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblinvoices.userid)";
           }
           $filters[] = ($filtertype[$i]=="like")
               ? $val . " LIKE '%" . db_escape_string($filterq[$i]) . "%'"
               : $val . "='" . db_escape_string($filterq[$i]) . "'";
       }
   }

# HIER IST DAS PROBLEMKIND. Der haut mir für Tabellen die er nicht kennt (logisch die sind ja auch von Collmex die Überschriften) zahlen rein.
# Der soll aber das reinhauen was ich will. Das sind immer die gleichen Werte. 
# Wenn also OBEN bei "PUNKT1" das hier in der VAR drin ist "CMXUMS" dann soll er auch in der ersten Spalte fortlaufend für jede Rechnung CMXUMS eintragen. Aktuell wird überall 0 eingetragen


   $fieldlist = array();
   foreach ($incfields AS $fieldname) {
       if (array_key_exists($fieldname,$filterfields)) {

           if ($fieldname=="clientname") $fieldname = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblinvoices.userid)";
           $fieldlist[] = $fieldname;

       }


   }






   $result = select_query("tblinvoices", implode(',', $fieldlist), implode(' AND ', $filters));
   while ($data = mysql_fetch_assoc($result)) {
       if (isset($data['paymentmethod'])) $data['paymentmethod'] = $gateways->getDisplayName($data['paymentmethod']);
       $reportdata["tablevalues"][] = $data;
   }

}
?>
<script language="JavaScript">
function selectAll(source) {
	checkboxes = document.getElementsByName('incfields[]');
	for(var i in checkboxes)
		checkboxes[i].checked = source.checked;
}
</script>

<input type="checkbox" id="selectall" onClick="selectAll(this)" />

 

 

Any help i would appreciate.

Main Problem: - I cant get the most colums stay empty.

- The Date should be different.

- customers Numbers wont show up

- Javascript is also showing up in CSV

 

....

 

Thank you.