Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Location
    Canada
    Posts
    58

  2. #2
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    4,366
    The existing reports with WHMCS are all "open" for you to see how they work - to do your reports is just a couple of queries and putt the values into an array
    Rob Golding Astutium Ltd - UK based ICANN Accredited Domain Registrar - proud to accept BitCoins
    Buying Web Hosts and Domain Registrars Today @ hostacquisitions.co.uk
    UK Web Hosting | UK VPS | UK Dedicated Servers | ADSL/FTTC | Backup/DR | Cloud
    UK Colocation | Reseller Accounts | IPv6 Transit | Secondary MX | DNS | WHMCS Modules

  3. #3
    Join Date
    Apr 2009
    Location
    Canada
    Posts
    58
    I have the report created now
    I just need help getting the required info into it

    <?php

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

    $reportdata["title"] = "Staff Reports";
    $reportdata["description"] = "A summary staff sales";

    $reportdata["tableheadings"][] = "Period";

    foreach ($currencies AS $currencyid=>$currencyname) {
    $reportdata["tableheadings"][] = "$currencyname Amount";
    }

    $totals = array();

    for ( $day = 0; $day < 120; $day += 30) {
    $startdate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-$day,date("Y")));
    $enddate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-($day+30),date("Y")));
    $rowdata = array();
    $rowdata[] = "$day - ".($day+30);
    $currencytotals = array();
    $query = "SELECT tblcustomfields,SUM(tblinvoices.total),(SELECT SUM(amountin-amountout) FROM tblaccounts INNER JOIN tblinvoices ON tblinvoices.id=tblaccounts.invoiceid INNER JOIN tblclients t2 ON t2.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.duedate>='$enddate' AND tblinvoices.status='Unpaid' AND t2.currency=tblclients.currency) FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.duedate>='$enddate' AND tblinvoices.status='Unpaid' GROUP BY tblclients.currency";
    $result = mysql_query($query);
    while ($data = mysql_fetch_array($result)) {
    $currencytotals[$data[0]] = $data[1]-$data[2];
    }
    foreach ($currencies AS $currencyid=>$currencyname) {
    $currencyamount = $currencytotals[$currencyid];
    if (!$currencyamount) $currencyamount=0;
    $totals[$currencyid] += $currencyamount;
    $currency = getCurrency('',$currencyid);
    $rowdata[] = formatCurrency($currencyamount);
    }
    $reportdata["tablevalues"][] = $rowdata;
    }

    $startdate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-120,date("Y")));
    $rowdata = array();
    $rowdata[] = "120 +";
    $currencytotals = array();
    $query = "SELECT tblcustomfields,SUM(tblinvoices.total) FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.status='Unpaid' GROUP BY tblclients.currency";
    $result = mysql_query($query);
    while ($data = mysql_fetch_array($result)) {
    $currencytotals[$data[0]] = $data[1];
    }
    foreach ($currencies AS $currencyid=>$currencyname) {
    $currencyamount = $currencytotals[$currencyid];
    if (!$currencyamount) $currencyamount=0;
    $totals[$currencyid] += $currencyamount;
    $currency = getCurrency('',$currencyid);
    $rowdata[] = formatCurrency($currencyamount);
    }
    $reportdata["tablevalues"][] = $rowdata;

    $rowdata = array();
    $rowdata[] = "<b>Total</b>";
    foreach ($currencies AS $currencyid=>$currencyname) {
    $currencytotal = $totals[$currencyid];
    if (!$currencytotal) $currencytotal=0;
    $currency = getCurrency('',$currencyid);
    $rowdata[] = "<b>".formatCurrency($currencytotal)."</b>";
    }

    $reportdata["tablevalues"][] = $rowdata;

    ?>

Similar Threads

  1. WHMCS Reports Needed
    By VN-Ken in forum Software & Scripts Requests
    Replies: 2
    Last Post: 05-23-2011, 10:45 AM
  2. Replies: 0
    Last Post: 01-19-2010, 11:28 AM
  3. Replies: 13
    Last Post: 12-15-2009, 05:55 PM
  4. WHMCS Audit Reports
    By Nick H in forum Software & Scripts Offers
    Replies: 1
    Last Post: 08-02-2009, 01:37 PM
  5. Need Plesk custom customer reports
    By albatroz in forum Web Hosting
    Replies: 1
    Last Post: 10-12-2007, 08:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •