[report] Monthly Domain Sales

[nike.stars]

Hi there, this is my first post in whmcs and i would like to share one of my report module called "Monthly Domain Sales"

 

The reason i create this report is because every month i need to count my revenue each month only from my webhosting account. Since whmcs only provide standard report for total income/transactions every month so i create this report so i can use this report to subtract from total sales per month and then i can get nett income for webhosting

 

this report still has a bug in the month selector, i appreciate any help i can get.

 

also i create this report according to my need so it maybe inappropriate for some of you.

 

please check this line:

if ($amount != '0')
$balance += $amount;
elseif (substr($domain, -3, 3) == 'com' || 'net' || 'org' || 'info')
$balance += '90000';

 

i use that line because i have a free domain promotion for every big package order and because of that the invoice for the domain is 0 (zero). so i add the code above to manually add domain prices for this "free domain". you can also modify it according to your needs.

 

and if you don't need any of that, just replace the above code with this:

$balance += $amount;

 

hopefully you guys like it

monthly_domain_sales.zip

[pjs32]

Hi,

I only get a blank page when i try to view it :-(

[nike.stars]

@pjs

hi there, thanks for your report, do you have whmcs standard table prefix in your database?

[Giannis]

Hi

thnx for this

i get...

Parse error: syntax error, unexpected T_VARIABLE, expecting ')' in /home/xxxxx/public_html/whmcs/modules/reports/monthly_domain_sales.php on line 38

[JordanSS]

@Giannis

in line 38 there is a bug...

 

Replace that line with this one:

 

$reportdata["tablevalues"][] = array($date,"<a href='/admin/invoices.php?action=edit&id=".$invoiceid."'>".$invoiceid."</a>","<a href='/admin/invoices.php?action=edit&id=".$invoicenum."'>".$invoicenum."</a>",$amount,$type,$domain,"<a href='/admin/clientssummary.php?userid=".$userid."'>".$userid."</a>",$firstname, $lastname);

[JordanSS]

@ nike.stars

 

The problem with the month you are talking about in the first script can be resolved using this code

 

if ($month<"10") {

$month="0".$month;

 

}

 

The code has to be placed after your

 

if ($month=="") {

$month=date("m");

$year=date("Y");

 

 

}

 

code.

[sparky]

This should get it going for you

<?php

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

$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

if ($month=="") {
   $month=date("m");
   $year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);  

$reportdata["title"] = "Monthly Domain Sales Report for ".$months[$month-1]." ".$year;
$reportdata["description"] = "This report shows a transactions summary for a selected month.";

$reportdata["tableheadings"] = array("Date", "Invoice ID", "Invoice Number" ,"Amount","Type","Domain","User ID","First Name","Last Name");

$query = "SELECT i.date, i.id, i.invoicenum, t.amount, t.type, d.domain, t.userid, c.firstname, c.lastname FROM tblinvoices i, tblinvoiceitems t, tblclients c, tbldomains d WHERE i.userid = t.userid AND t.invoiceid = i.id AND c.id = t.userid AND d.userid = c.id AND d.id = t.relid AND t.type = 'Domain' AND i.status = 'Paid' AND i.date LIKE '$year-$month-%'";

   $result=mysql_query($query);
   while ($data = mysql_fetch_array($result) ) {
       $date = $data[0];
       $invoiceid = $data[1];
       $invoicenum = $data[2];
       $amount = $data[3];
       $type = $data[4];
       $domain = $data[5];
       $userid = $data[6];
       $firstname = $data[7];
       $lastname = $data[8];
       $balance += $amount;


       $reportdata["tablevalues"][] = array($date, "<a href='invoices.php?action=edit&id=".$invoiceid."'>".$invoiceid."</a>", "<a href='invoices.php?action=edit&id=".$invoicenum."'>".$invoicenum."</a>", $amount,$type,$domain,"<a href='clientssummary.php?userid=".$userid."'>".$userid."</a>",$firstname,$lastname);
   }

$balance = sprintf("%01.2f",$balance);

$data["footertext"]="<p align=right><b>Balance: ".$CONFIG["CurrencySymbol"].$balance."</b></p><table width=90% align=center><tr><td>";
if ($month=="1") {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";
} else {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";
}
$data["footertext"].="</td><td align=right>";
if ($month=="12") {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1)."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

[nike.stars]

strange enough, i've posted the new revision, where's my post? :-/

 

anyway, here's the new revision:

http://www.mediafire.com/?wnrw74mc01m

[bear]

where's my post?

 

Spam filters accidentally caught it.

An unfortunate side effect of those that would spam forums. Carry on.

[nike.stars]

@jordan

hi there, thanks for your help, i have to change 'if' to 'else if' to make it work by default. it was blank if i just click the link without the month

 

and here's the final revision and it works perfectly now, cheers

http://www.mediafire.com/file/cin2z5zum3t/monthly_domain_sales.zip

[pjs32]

Nice one nike.stars -all works fine now!

 

What about adding a total domain count at the bottom?

[nixell]

thanks nike.stars

[othellotech]

WHERE i.userid = t.userid AND t.invoiceid = i.id AND c.id = t.userid AND d.userid = c.id AND d.id = t.relid

 

I'd highly recommend you look at the mysql website and learn what a *JOIN* is

 

And your report will include them in the month of the invoice, not necessarily the month of the payment recieved - might want to switch it to checking datepaid instead

[cmo]

We are looking for something like this, more updated.

[buzby]

invalid or deleted file,

can you upload the latest version pls.