Jump to content
Danse

Help with CSV Export

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated