trappedatuf

I would be interested in being a beta tester, please send me info. A couple of questions: 1. What about when we create a back-dated invoice in WHMCS? Would it be imported and hence "mess-up" our final numbers for a previous month? That would be a concern, as we would need to monitor this. 2. We usually just add a line item called "merchant fees" to the invoice to subtract out the merchant fees we pay per transaction... this then lets us just record the deposit for the sales receipt and be done with it. I assume there would be no issue in going back into these invoices and adding that line item via QB, correct? Looking forward to this as we've been using WHMCS and QB for a long time and manual cut-and-paste entry becomes quite annoying!

Ok, so Matt was kind enough to let me know the following crucial info: The fields are stored in tblcustomfields and the values of those fields in tblcustomfieldvalues. Those then relate to the product in tblhosting based on relid=id. So the join would need to link to tblhosting, tblcustomfieldvalues and tblcustomfields and then the query select based on multiple field IDs matching the search crtieria. So then I did some testing to try and create a SQL query that provided me with the results I required. So I started with the most simplistic statement and built upon that. I'm going to outline the steps so you guys can follow how I came up with the final query and then comment on how/if you would have done it differently/more efficiently. 1. This statement identifies only the custom fields called 'Status': SELECT * FROM `tblcustomfields` WHERE `tblcustomfields`.`fieldname` LIKE 'Status' 2. From that I see what those field's ID numbers are and I can use that to relate it to the `tblcustomfieldsvalues` table. So I added a (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id) to that statement and get: SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) 3. That gives me all the 'Status' fields correlated with the `value` of that status field (value being Design Complete, In Design, etc -- which is what I am looking for). So now I must find out, out of that list, which one's have the value of 'In Design'. SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') 4. Now I must use the `tblcustomfieldsvalues`.`relid` field and relate that to `tblhosting`.`id` so I can see what hosting accounts those actually are. SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`) 5. And last but not least I want to ignore all but "Active' clients in my query so I put that in: SELECT * FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`) && (`tblhosting`.`domainstatus` = 'Active') 6. Now to only show the hosting account's domain name and not all fields so I am not given unneeded info: SELECT `tblhosting`.`domain` FROM `tblcustomfields`, `tblcustomfieldsvalues`, `tblhosting` WHERE (`tblcustomfields`.`fieldname` LIKE 'Status') && (`tblcustomfieldsvalues`.`fieldid` = `tblcustomfields`.`id`) && (`tblcustomfieldsvalues`.`value` LIKE '%In Design%') && (`tblhosting`.`id` = `tblcustomfieldsvalues`.`relid`) && (`tblhosting`.`domainstatus` = 'Active') Ok, so that final query gives me the output I want. Feel free to run these queries on any custom fields you may have that are the same through all your different hosting products.

Not really ... all I need is te simplistic view of what status all the hosting accounts have. The current search view that WHMCS has would work great because all I want to do is quickly sort on the Status field to see how many projects are "In Design" at the current moment and which one's those are. So really all I need is an SQL query that will do what is currently done when you search one "Hosting Plan - Status" with all the others so you can view everything at once instead of piecemeal. Matt... what is the current SQL query used? Maybe if I had that I can figure out the JOINS myself?

And now this is my main follow-up question... maybe someone here can help with the SQL query needed to find this info: Does this just pull data from the database via an SQL query? Why can't we edit the query to use a JOIN statement and have it join all the status fields for all the different type of products. I'm no expert on SQL but I think if I knew where to look in the database for this info I could come up with something. Can you let me know where this data is held and how that function gets it's data from the DB then I'm sure we could come up with a solution... this is really CRUCIAL for me to be able to easily see the status of all my accounts so I can see which ones are finished and more importantly which clients are still in the design phase. Thanks, Dave

Here's Matt's response: It's not possible to do that as each custom field is a seperate entity in the database Regards, Matt

I already posted this as a ticket to Matt, and I'm going to put his reply in... but I though to post it here in the forums so maybe someone can help with the SQL query needed. I created an user-defined field drop-down box on all my Hosting service accounts called "Status". This contains options like "In Design", "On Hold", "Complete"... etc. Now, I want to see all the accounts that are "In Design", but there is a problem! If I go to "List Hosting Accounts" and click on "Search/Filter" then I can sort on "Custom Field"... BUT... it segments the status by account type. For example I have a lot of different types of accounts such as "Website Express", "Website Pro", "Ecommerce", etc... so the sort is showing "Website Pro - Status", "Website Express - Status"... etc. Now this doesn't help me... how can I sort on just the "Status" field regardless of account type.

Right now, my current software takes orders on my website and creates an entry in our seperate database system. I can have it format a string that can be just entered into a browser window and it will then create the account in WHMCS... that is if WHMCS accepts GET requests. For example: http://www.mywhmcslink.com/admin/clientsadd.php?firstname=John&lastname=Doe&companyname=Test%20Company&email=johndoe@email.com (etc...) Then if I paste that link into a browser it will call WHMCS and create that account. Now the only thing is that it will have to be a public function I am sure, so if someone could give me some advice on how to do this it would be much appreciated. PS: I know I could have my other software just add it the client info to the WHMCS database directly by an internal function I write, but I would rather do it the link way because it would be much easier. Thanks for the help, Dave

Steve, It should work right off the bat. I coded this thing and it reads the balance from my Paypal Business account just fine.

Yeah, I thought this would be a little tough. If there was a way we could just mark a site as "hidden" then have an option if users can view "hidden" site details that would be a pretty simple work around, don't you think?

Matt is awesome and he has helped develop some custom things for me. His work is great, fast and economical! I would like this feature but I'm not sure if I need it bad enough to warrant an extra expense for it. Let me know guys if anyone else could benefit from something like this and would like to see this feature and then we can decide if it's something worth paying for to have developed

I thought that was going to be the unfortunate answer :-( How hard would this be to incorporate? Let me know guys, would anyone else like a feature like this?

I have a couple of hosting accounts that I don't want certain administrators to see. I want it to be effectively hidden like it wasn't there to some of my administrators. How would I do this?

No, it uses your PP API_USERNAME, API_PASSWORD and API_SIGNATURE... it is based on code Paypal provides as the proper way to access their API system to get things like your balance and transactions, etc.

No prob! I really like this software so I'm just trying to help out where I can

We use Paypal Website Payments Pro for our merchant processing. We thought it would be nice to have the Paypal balance displayed on the admin homepage, for easy reference! Just download the zip file, follow the instructions and you're all set paypalBalance.zip