Reports Challenge

[daveman]

Well this new reports module needs to be tested out and used more fully than it is, although the default three reports look pretty cool. I, personally, will be providing prizes in a community run contest to make a few report modules that will hopefully later be included in a release. My thinking right now is the prize will be a cd or dvd of your choosing. The first step is for the community to make a list of what reports they would like to see. Once a good list is made I will compile it and then set it forward as a challenge. Unless others wish to chip in, since every CE customer will directly benefit from this, I can provide three or four prizes for the top reports. Once report modules are made the community will vote in order to determine the winners.

So lets get a list of what we want togeather now!

List of upcoming transactions by payment processor
Signups by plan during the past X days
Income by plan in the past X days
Forcasted income over the next X days

[VertiHost]

sounds interesting. I post some more info on my "plugins of choice" later. :)

[dbutts]

Domain name list with expiration date and registrar allowing you to sort by expiration date, domain name and registrar. I don't know if the sorting would make it 3 plug-ins then?

[Targon]

Cost / Income per server but that needs the ability to input server cost

[philmcdonnell]

Domain name list with expiration date and registrar allowing you to sort by expiration date, domain name and registrar. I don't know if the sorting would make it 3 plug-ins then?

This is not possible right now since CE doesn't track domain name registrars or expiration dates etc. I guess you could add those fields in to CE as custom domain fields but it doesn't have the ability in the standard install...

Regards,
Phil

[dbutts]

This is not possible right now since CE doesn't track domain name registrars or expiration dates etc. I guess you could add those fields in to CE as custom domain fields but it doesn't have the ability in the standard install...

Regards,
Phil

doh! I have had those custom fields since the early days of CE and just forgot that they are not standard. I guess I need to learn some PHP and stuff to figure it out from my custom fields.

[daveman]

Upcoming expiration dates for customer's credit cards

[infinityws]

First we must establish what is and is not possible with these reports. And is their an SDK available?

[gummyAvenger]

List of upcoming transactions by payment processor
I went ahead and put this one together:
http://www.clientexec.com/forum/showthread.php?t=3551

-Daniel

[daveman]

I went ahead and put this one together:
http://www.clientexec.com/forum/showthread.php?t=3551

-Daniel
I also wrote that for myself quickly last night. ;)

[dacsoft]

I would like a report that shows the amount of space and bandwidth actually sold (or available ) on a server. I do this with Excel right now.

[gummyAvenger]

I also wrote that for myself quickly last night. ;)
Haha oh well :D
At least now I have a feel for how the reports system works. :)

-Daniel

P.S. -- Yours may be better than mine... I've got a feeling it could have been done with fewer calls to the database, but being self-taught I never really got into joins and some of the other more advanced SQL stuff... Been meaning to for a long while though :rolleyes:

That being said, if anyone thinks they can improve on it by using fewer database calls (or other optimizations), please do! I would be very interested to see how others would go about it.

[philmcdonnell]

I would like a report that shows the amount of space and bandwidth actually sold (or available ) on a server. I do this with Excel right now.

Can you give us a look or a copy of the excel spreadsheet? Then it can be kinda duplicated in a report plugin.

Regards,
Phil

[dbutts]

Can you give us a look or a copy of the excel spreadsheet? Then it can be kinda duplicated in a report plugin.

Regards,
Phil

Is anyone willing to help me write a report? I was able to get some help from a guy at work and have been trying to learn php, but just started.

I had mentioned before that I wanted a report to list

Domain Name | Expiration Date | Registrar

Of course two of these are custom domain fields, but I figured you could just exchange the custom domain field with other ones, or create the following and be able to track and use this plug in.

Here's as far as I got. I can make it work in MySQL, but don't know how to arrange the columns and make it print. I spent some time yesterday but just get a blank drop down menu item. I did once get "domain" as a menu item, but they all give me blank forms.

The guy helping me at work didn't quite understand how or why the other plug ins were doing things certain ways. I think he's a bit new too.

This is the query I want to use (please feel free to fix it too):


SELECT domains.DomainName, domain_customdomainfields.value
FROM domains, domain_customdomainfields
WHERE domains.id = domain_customdomainfields.userid AND ( domain_customdomainfields.customid = 3 OR domain_customdomainfields.customid = 4 )
ORDER BY domain_customdomainfields.userid, domain_customdomainfields.customid

Thanks to anyone that wants to help.

You can catch me online too at denny24hrs with AOL IM too.

[dacsoft]

Domain Name | Expiration Date | Registrar


SELECT domains.DomainName, domain_customdomainfields.value
FROM domains, domain_customdomainfields
WHERE domains.id = domain_customdomainfields.userid AND ( domain_customdomainfields.customid = 3 OR domain_customdomainfields.customid = 4 )
ORDER BY domain_customdomainfields.userid, domain_customdomainfields.customid

Thanks to anyone that wants to help..

I can't try this because I don't have your elements, but should be a good starting point:

<?php
// create a new report object
$report = new Report();

// set title of report
$report->SetDescription("Displays Domain Regustration");

// define your query
$reportSQL = "SELECT domains.DomainName, domain_customdomainfields.value
FROM domains, domain_customdomainfields
WHERE domains.id = domain_customdomainfields.userid
AND ( domain_customdomainfields.customid = 3 OR domain_customdomainfields.customid = 4 )
ORDER BY domain_customdomainfields.userid, domain_customdomainfields.customid";

// execute the query (returning an array in $result
$result = mysql_query($reportSQL, $connection) or die("ERROR REPORT: $reportSQL");

//initialize array and any variables
// list will move data elements from the $result to the variables listed.
while (list($tDomainName,$tDomainExpirationDate, $tRegistrar) = mysql_fetch_row($result))
{
// add to the aGroup[] array
// do any processing you need in here.
$aGroup[] = array($tDomainName, $tDomainExpirationDate, $tRegistrar);
}

// now add the group array to the report.
// format appears to be (array of data, "Title of Array", array of labels)
$report->Add($aGroup,"Domain Registration Information",array("Domain Name","Domain Expiration", "Registrat"));

// show the report
$report->Show();
?>

If you plan to provide this to others on the site, I suggest that instead of using
AND ( domain_customdomainfields.customid = 3 OR domain_customdomainfields.customid = 4 ) you consider using something like

AND (domain_customdomainfields.value like ('Registrar'). This isn't dependant on others having the same id for their custom fields.

[dacsoft]

I would like a report that shows the amount of space and bandwidth actually sold (or available ) on a server. I do this with Excel right now.

I went ahead and wrote this report myself. This report displays the name of each server and how much disk space and bandwidth have already been sold. An example is:

Allocated Resources
Server Name Disk Space (GB) Transfer Bandwidth (GB)
server1 10.75 40.50
server2 1.00 1.00
(Note: report is formatted correctly - don't know how to do it here)

The file is http://www.dacsofthosting.com/files/resource_allocation_report.zip

It requires 2 Domain Custom Fields called "Space" and "Transfer". It does not depend on the ID of the custom field.

I haven't done PHP in years, but it seems to work fine.

[dbutts]

I went ahead and wrote this report myself. This report displays the name of each server and how much disk space and bandwidth have already been sold. An example is:

Allocated Resources
Server Name Disk Space (GB) Transfer Bandwidth (GB)
server1 10.75 40.50
server2 1.00 1.00
(Note: report is formatted correctly - don't know how to do it here)

The file is http://www.dacsofthosting.com/files/resource_allocation_report.zip

It requires 2 Domain Custom Fields called "Space" and "Transfer". It does not depend on the ID of the custom field.

I haven't done PHP in years, but it seems to work fine.

Well, I am interested in your take on the Custom Fields solutions. I posted earlier about a Domain Name custom field report, and got some help, but it's not quite working yet. This is cool. Are you entering the data in these fields in Megabytes? or Gigabytes for it to do the math?

Sorry for the silly question.

[dacsoft]

This is cool. Are you entering the data in these fields in Megabytes? or Gigabytes for it to do the math?

Sorry for the silly question.
No problem. I actually enter the information into ClientExec as MB. But the report looks better in GB. While you often sell hosting in Mbs, you normally have GBs on your server (or reseller account). It would be very easy to change the function to whatever you wanted. In fact, I think that I left both variables in the file.

[MaximusGlorious]

anyone here can find a way

listing all clients and theri domains in a page with their due dates and billing cycles?

and can have edit buttons on them...

it may be seem like a phpmyadmin issue but i wish to have it?

maybe i try it myself

[dacsoft]

anyone here can find a way

listing all clients and theri domains in a page with their due dates and billing cycles?

and can have edit buttons on them...

it may be seem like a phpmyadmin issue but i wish to have it?

maybe i try it myself

What does the edit button do? Go to the client information, domain, or what?

[MaximusGlorious]

What does the edit button do? Go to the client information, domain, or what?

edit client info domain ? edit button for edit those values... or go to edit them...

it is become hard to see which domain is belong whom after a while...

[infinityws]

I went ahead and wrote this report myself. This report displays the name of each server and how much disk space and bandwidth have already been sold. An example is:

Allocated Resources
Server Name Disk Space (GB) Transfer Bandwidth (GB)
server1 10.75 40.50
server2 1.00 1.00
(Note: report is formatted correctly - don't know how to do it here)

The file is http://www.dacsofthosting.com/files/resource_allocation_report.zip

It requires 2 Domain Custom Fields called "Space" and "Transfer". It does not depend on the ID of the custom field.

I haven't done PHP in years, but it seems to work fine.
Wait a sec, I'm suppose to go through every client and enter their space and transfer? yah right :p

What about multiple domains?

Anyhow, nice report but it appears this is something more for your personal use. Maybe with some more hacking, it can query the WHM server and find out the bandwidth and space itself.



P.S. But even testing it with 2 custom fields did not work. What options did you choose for the fields.

[dbutts]

P.S. But even testing it with 2 custom fields did not work. What options did you choose for the fields.

I was able to create the 2 custome domain fields he mentioned and it worked if I filled in the information for those new fields.

I tried to contort this plugin with the data he supplied me to help me build my custom plugin, but can't get it off the ground. I guess I need to spend some serious reading up on PHP this holiday break.

I was sure that if I anyone created the custom domain fields, "Registrar" and "Expires on" they could have a nice plugin to add info in on.

Dacsoft, any ideas where I screwed up or didn't finish your help for my plugin?

[dacsoft]

I was able to create the 2 custome domain fields he mentioned and it worked if I filled in the information for those new fields.

Dacsoft, any ideas where I screwed up or didn't finish your help for my plugin?

Two issues here.
infinityws -- The report I wrote for showing disk and bandwidth usage were for my use and I know worked. I understand it might be a lot of work to enter the information - I already had the 2 fields with data entered. The report is only good for an example or if somebody wants to enter the information (manually or automatically). Please don't feel I am asking you to enter the information just to use my report. Multiple domains should work fine - it looks at each domain and adds the bandwidth/space allocated to the domain. It is not a client report, it was designed to show how much space/bandwidth I have sold on a server (I don't oversell).

dbutts -- The other script was just an example to answer somebody elses question. I couldn't test it because I don't have his custom fields. I am not even sure the sql query works. The sql query was provided and I copied it as-is, writing the report to use the query (assuming it works).

[dacsoft]

I was sure that if I anyone created the custom domain fields, "Registrar" and "Expires on" they could have a nice plugin to add info in on.

Dacsoft, any ideas where I screwed up or didn't finish your help for my plugin?

dbutts,

I just provided some more information. I would be glad to work it more. You just added the 2 fields identified in your email?

[dbutts]

dbutts,

I just provided some more information. I would be glad to work it more. You just added the 2 fields identified in your email?

Yep. That's it, I created those to Custom Domain Fields. If you think you can add those two fields yourself, as a test, it should work the same way your Bandwidth / disk space one worked, with no calculations needed. It's more of a list.

I really do appreciate the help. It sucks not being able to do what you are thinking, but I will keep reading up and learning.

I understand that you didn't create the SQL Query. It did work to an extent in MySQL, but like I said, I was getting help from someone who is not a pro at MySQL and PHP yet.

If you want to write it how you think it should be, by all means, please do so. It would be greatly appreciated.

It is not a client report, it was designed to show how much space/bandwidth I have sold on a server (I don't oversell).

I get it, that is a great use for it, I am excited to add that to my CE info.

[dacsoft]

I will work on it today. It looks like a good query to have.

[dacsoft]

I will work on it today. It looks like a good query to have.

dbutts,
http://www.dacsofthosting.com/files/domain_registration_report.zip is a draft copy. This version is not sorted at all and the dates are just text strings (as entered). Check it out and see if it does what you need.

Note: This report requires 2 custom fields that must be populated to be useful. The data is not automatically entered - you must do it yourself.

[Targon]

What are the chances of a custom field to put in server costs so a profit per server mod could be created ?

[dbutts]

dbutts,
http://www.dacsofthosting.com/files/domain_registration_report.zip is a draft copy. This version is not sorted at all and the dates are just text strings (as entered). Check it out and see if it does what you need.

Note: This report requires 2 custom fields that must be populated to be useful. The data is not automatically entered - you must do it yourself.

You are the BEST!!! That's it perfectly. All my data is there already, so, instant gratification.

Thank you so much for your help. I hope others enjoy!

How hard is it to have it sort by date? That's my big goal to see them in a date ordered list of what's due for renewal next.

[dacsoft]

You are the BEST!!! That's it perfectly. All my data is there already, so, instant gratification.

Thank you so much for your help. I hope others enjoy!

How hard is it to have it sort by date? That's my big goal to see them in a date ordered list of what's due for renewal next.

I can do that - just a little harder. I will work on it. (I am going to use it myself)

[dacsoft]

How hard is it to have it sort by date? That's my big goal to see them in a date ordered list of what's due for renewal next.

What is the format of date you put into the field? These are string fields so the data will need to be converted before they can be sorted.

[dbutts]

What is the format of date you put into the field? These are string fields so the data will need to be converted before they can be sorted.

I use MONTH / DATE / YEAR as an example 05/20/2003 for May 20th, 2003.

I am up for what ever works the easiest and willing to fix my dates to match the plugin.

[infinityws]

Two issues here.
infinityws -- The report I wrote for showing disk and bandwidth usage were for my use and I know worked. I understand it might be a lot of work to enter the information - I already had the 2 fields with data entered. The report is only good for an example or if somebody wants to enter the information (manually or automatically). Please don't feel I am asking you to enter the information just to use my report. Multiple domains should work fine - it looks at each domain and adds the bandwidth/space allocated to the domain. It is not a client report, it was designed to show how much space/bandwidth I have sold on a server (I don't oversell).

dbutts -- The other script was just an example to answer somebody elses question. I couldn't test it because I don't have his custom fields. I am not even sure the sql query works. The sql query was provided and I copied it as-is, writing the report to use the query (assuming it works).
I know I was just kidding around ;) But nonetheless, if it does work as people are saying it could come in handy. (I created 2 custom fields and entered data for 2 clients, but I could not get any data generated)

[dacsoft]

I know I was just kidding around ;) But nonetheless, if it does work as people are saying it could come in handy. (I created 2 custom fields and entered data for 2 clients, but I could not get any data generated)
infinityws,
Are you talking about the script to show disk space and bandwidth? If so, I just made 2 DOMAIN custom fields and called them 'Space' and 'Transfer' (THEY ARE CASE SENSITIVE). They must be Domain custom fields (from Client Info -> Viewing Domains screens). Is there a chance you made 2 PROFILE custom fields?

Once filled in with numbers (number is in MB's without the MB or GB) the report should work. What happens when you use it?

I am curious what the problem is. Could you please check the above items and let me know. If it still doesn't work, I can send you a couple sql queries to run for a test.

Sorry about the problem.

[dacsoft]

I use MONTH / DATE / YEAR as an example 05/20/2003 for May 20th, 2003.

I am up for what ever works the easiest and willing to fix my dates to match the plugin.

Download the file again from http://www.dacsofthosting.com/files/domain_registration_report.zip . This one should sort like you needed. It takes the date in the format of mm/dd/yyyy (12/01/2003).

[Targon]

Can I suggest this thread is split and one created for this mod and this thread returned to its' original topic

[dacsoft]

Can I suggest this thread is split and one created for this mod and this thread returned to its' original topic


Good idea. It is not at http://www.clientexec.com/forum/showthread.php?p=19009&goto=postid

[MaximusGlorious]

I can do that - just a little harder. I will work on it. (I am going to use it myself)
i have a suggestion... please archievie those add-ons in somewhere located in this forum or under umberalla of clientexec... you may use approve something for that... but i need this plugin cause i use it and now i lost the script code

[WelterServer]

The link are broken, anybody have the
resource_allocation_report.zip

to send me please

Thanks

Marcelo

[dacsoft]

The link are broken, anybody have the
resource_allocation_report.zip

to send me please

Thanks

Marcelo
Sorry, I never updated the link after changing to the 2.3 format. Here is the new link:

http://www.dacsofthosting.com/files/resource_allocation_report-account_&_packages.zip

[jschurawlow]

The link http://www.dacsofthosting.com/files/domain_registration_report.zip is also not working if you'd be able to update it with the one that sorts by date as above :)

EDIT: argh nevermind found it on the other thread :)

-Justin

[integrahost]

Income by plan in the past X days