Jump to content

Chance

Level 2 Member
  • Content count

    40
  • Joined

  • Last visited

Community Reputation

10 Good

About Chance

  • Rank
    Level 2 Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Chance

    Double-Entry Accounting Module

    @hostingct - that is exactly why we set out to create this accounting add-on as the native methods WHMCS uses to track income/expenses/credits is not according to standard accounting practice. So as we began to develop a better solution for this, we realized it was very difficult to do without the double-entry system. I could go into much greater detail and outline all things we felt were lacking but our intent is not to bash something but to make it better. To properly record and track expenses/income/credits required common accounting building blocks, like accounts to record against, line item quantities, better sales tax rule/agency handling, ability to offer tangible item sales rather than just services, and tie it ALL into the native WHMCS system. At that time, having been forced to form the building blocks, we decided to go all out and create a full-fledged quickbooks-style accounting system. So yes, it does track expenses very well and if you were to use the add-on, you would have to spend some time tweaking your accounting settings to fit your business, and the activation process would require some database processing, but it would definitely be worth your time! :0) If you'd like, PM us and we can send you just a couple of screenshots of expense tracking interfaces and reports it can generate. @bendrop - thank you for the interest as well! We hope to build a large user-base with this add-on and are very excited to see some interest. So thank you!! You too, are invited to PM us as well!
  2. Chance

    Double-Entry Accounting Module

    Hi hostingct, Thanks again for your interest. Please see our response to your message yesterday regarding this as we would very much like to make it public.... https://whmcs.community/messenger/74115/?tab=comments#comment-80244
  3. Chance

    InvoiceCreated Hook for auto invoices

    We have confirmed that the hook point InvoiceCreated and InvoiceCreationPreEmail are NOT invoked from within the cron. We implemented our own logging function inside of our addon to monitor every call into WHMCS--whether from the web client area, web admin area, api, or cron. Each and every invocation of WHMCS is logged with our addon. Then throughout our addon code, we output log lines to track what our code is or is not doing. One of its very useful features is that it allows us to know for certain if WHMCS is notifying our code of events or not. We have hook points defined for all major invoice events: AddInvoiceLateFee, InvoiceCancelled, InvoiceCreated, InvoiceCreation, InvoiceCreationPreEmail, UpdateInvoiceTotal, etc. Inside each of these hook points the code starts as follows (we are not showing all hook points): add_hook("AddInvoiceLateFee", 1, $vars) { OURLOG::writeLine("hook_AddInvoiceLateFee()->vars[invoiceid]=>" . $vars['invoiceid']); ..code to handle event omitted.. }); add_hook("InvoiceCreated", 1, $vars) { OURLOG::writeLine("hook_InvoiceCreated()->vars[invoiceid]=>" . $vars['invoiceid']); ..code to handle event omitted.. }); add_hook("InvoiceCreation", 1, $vars) { OURLOG::writeLine("hook_InvoiceCreation()->vars[invoiceid]=>" . $vars['invoiceid']); ..code to handle event omitted.. }); add_hook("UpdateInvoiceTotal", 1, $vars) { OURLOG::writeLine("hook_UpdateInvoiceTotal()->vars[invoiceid]=>" . $vars['invoiceid']); ..code to handle event omitted.. }); Below is a sample output from our addon during a standard scheduled cron run. An invoice was created during this particular cron and notice the log does include the hook points for InvoiceCreation and UpdateInvoiceTotal, but NOT for InvoiceCreated. ------------------------------------------------------------------------------ DATE: 10/08/2017 16:00 BACKTRACE: /home/cpanel_account_removed/whmcs_secure_folder_removed/crons/cron.php /home/cpanel_account_removed/whmcs_secure_folder_removed/crons/bootstrap.php /home/cpanel_account_removed/whmcs_secure_folder_removed/init.php /home/cpanel_account_removed/whmcs_secure_folder_removed/functions.php /home/cpanel_account_removed/whmcs_secure_folder_removed/hookfunctions.php /home/cpanel_account_removed/public_html/modules/addons/addon_module_name_removed/hooks.php PARAMS: None LOG: hook_InvoiceCreation()->vars[invoiceid]=>24970 ..other log lines from our addon omittted.. hook_updateInvoiceTotal()->vars[invoiceid]=>24970 ..other log lines from our addon omittted.. ------------------------------------------------------------------------------ We have learned you can use these reliably and maybe find a way to accomplish your goals without relying on InvoiceCreated or InvoiceCreationPreEmail. Also, note our version of WHMCS is still 6.2 so we have not yet confirmed if this same scenario holds true for the latest version 7.3. We have clients that have the latest WHMCS so we will run this test on their install and update with our findings...
  4. Chance

    Double-Entry Accounting Module

    Hi Jason, Appreciate the input and questions. Due to the extensive functionality of the module, we felt it impractical to list all of its features in a single post, which is why we kept the original post very brief. We felt it better to post a brief inquiry as we did to see what type of response and interest it generated and then respond to the questions and input. This way we are able to focus on what is most important to those interested and not talk about features that others may not need or find that much interest in. In answer to your specific questions, yes expenses can be input. Expenses are recorded against standard cash accounts (banks, credit cards, credit lines, accounts payable, etc.) and the offsetting entries are recorded against expense accounts that are optionally tagged to clients. Those expenses are tracked against the cash account, each related expense account, as well as each client. And those expenses, if billable to a client, are still recorded as Billable Items within the core WHMCS system. In fact, the module adds a ton of functionality without compromising how WHMCS functions. The module offers very detailed reporting that allows one to see exactly where money is being expensed and what the return in sales on those expenses are. The module includes Profit & Loss reports, Sales by Vendor reports, Income by Client reports, Sales by Item reports, and a host of many others. [EDIT: For those reports that WHMCS offers a similar, the module greatly extends the data shown in the reports.] This is a full-fledged double-entry accounting system that offers many features that are sure to enhance WHMCS for a lot of companies. Thanks again for replying.
  5. Chance

    Double-Entry Accounting Module

    Hi Daniel, Thank you for your interest! While the accounting module is multi-language, the add-on is in USD currency only. This is because it has been used by us exclusively and our currency has always been USD. If the module generates enough potential sales and interest in adding multi-currency, we would certainly explore the option of adding it.
  6. Hello WHMCS Forum Members... We have seen this question asked before, and even discussed. However we have not yet seen a complete solution. Many have mentioned how cumbersome and tedious the task would be but we have actually taken on the challenge and succeeded. We have authored a full-fledged accounting WHMCS add-on module that our own company has used for several years now. It began as a tool that helped us better track income/expenses in WHMCS and over the past several years has metamorphosed into so much more. It is a very robust accounting system that is functionally comparable to other popular accounting software like QuickBooks and Peachtree. The primary difference being we no longer need QuickBooks because our Accounting module does all accounting for us and our CPA. We would like to know how many WHMCS users would be interested in us bringing this add-on the WHMCS marketplace? All input is welcome...
  7. Chance

    InvoiceCreated Hook for auto invoices

    Hi Brian, Thanks for the input. That description you quoted would pertain to us as we are actually still on v6.2.0. However, I am definitely seeing our custom module fail at being notified of invoice creation during the cron. I have inserted quite a bit of debug logging code lines into our own module code base to trap what was being called and when. All of the code inside the InvoiceCreated hook point is NOT being executed when the cron runs. All other code in our module and WHMCS hook points executes as expected. One would assume that there is a coding issue with our hook point, but if invoices are created via the API, or from within the web GUI, the InvoiceCreated hook point is called as documentation states and executes perfectly. Weird to say the least. I will keep researching this and continue to hope WHMCS chimes in also. Thank you again! :0)
  8. Chance

    InvoiceCreated Hook for auto invoices

    Yes, this affects a custom module we have created as well. Our module is never notified of invoices being created from the cron task because the InvoiceCreated hook is never notified from the cron. Whether this is by design, or an oversight, remains to be determined. Since WHMCS developers usually monitor the forums, perhaps they will enlighten us on this subject?
  9. Chance

    Profit and Loss Report

    JLHC: In the very first post of this thread I indicated the report highlighted here actually is an excerpt from our own Profit & Loss report that we use to track all income and expenses within our WHMCS install. Our P&L report does report transaction fees and the report lists that as an expense titled 'Merchant Account Fees' under 'Cost of Goods Sold'. Gross Profit is calculated based on all products'/services' income minus all expenses to get those products/services sold. pjs32: A P&L report typically does not show any tax-related data as that is not considered an expense. We, too, wanted to see how much tax we collected and paid to the appropriate agencies and so we wrote our own Sales Tax Liability report. That report lists each invoice's non-taxable items, taxable items, subtotal, credit, discount, total tax, and invoice total on a line-by-line basis for whatever calendar month/year desired. Then the footer of this report includes gross sales, taxable sales, exempt sales, tax collected, etc. for that period. Using this Sales Tax Liability report we can quickly determine how much tax we collected. We then add this Tax Collected amount from the Sales Tax Liability report to our Total Expenses amount from the Profit & Loss report, and subtract that total from our Gross Profit on the Profit & Loss report to determine our bottom line Net Income. Seems like a lot of steps but it is only two reports and basic math. AndyW: Converting to base currency rate is not as simple as it may sound. First, there is no exchange rate in the invoice table as you mentioned. Nor is there any reference in the invoice table to the currency used. An invoice record links to a client record and that client record dictates the default currency used by that client but not necessarily the currency used for each invoice. Now, there is an exchange rate in the transactions table that does link to each invoice and that could be used. However, we chose not to include currency conversion data in our reports because we do not have any clients that use anything other than USD. For those WHMCS members that do accept multiple currencies, this will definitely be a limitation. If you would like, you can PM me and we can discuss the cost for us to create a custom version for you.
  10. Chance

    Profit and Loss Report

    Hello, I would say to make sure that you have uploaded the reportfunctions_2.php file into your WHMCS includes directory, and make sure you upload the profit_and_loss_2.php and sales_by_product_2.php files into your WHMCS modules/reports directory. These two reports require no changes to any WHMCS settings or database. By default, they should pull and accurately report your sales data.
  11. Chance

    Profit and Loss Report

    Thanks to Snowman for pointing out that missing data. We have corrected the Sales By Product 2 report to include income from virtual and dedicated server accounts, as well as income from reseller accounts. The lines that have been changed begin at 198 and end at 223. We were not able to validate Snowman's mention of the P & L report missing that income though so we did not need to change that report at all to include that data. We tested these new mods by creating new orders for each type of product mentioned and then ran the reports. Both reports do include this data properly. Thanks to all who have taken the time to upload these reports and use them. It is nice to know they are useful to others. custom_reports.zip
  12. Chance

    Profit and Loss Report

    Hi ethix, Glad to see you here. Hope this information helps you....
  13. Chance

    Profit and Loss Report

    Hello, This post is in response to both the repliers of this post here and those that have PMed us regarding uploading our custom reports so that members could preview them. I have edited these reports extensively to remove all of our additional customizations that do not pertain to the original topic, which was custom reports on income from products. If you are interested, we will also share our customized Sales Tax Liability report, which includes much more detailed information for tax-reporting purposes in the US. There are three files for Product Sales: 1) reportfunctions_2.php, containing a mixture of functions and code used by all reports. 2) profit_and_loss_2.php, containing summary info of income from all products, domains, etc. 3) sales_by_product_2.php, containing the detail lines used to build the profit_and_loss report. I will not explain how the reports work as it should be pretty straightforward once you upload the scripts to your WHMCS install. Again, if you have trouble getting them to work, I would be willing to share a desktop session so that you can see our reports in live action. Anyway, thank you to every one that has shown interest and happy customizing!!! custom_reports.zip
  14. Hello all. WHMCS was definitely not designed with pre-built reports to report detailed income statistics. Having the need to view details about what income came in from what product, domain, or service was a must for us. So I wrote a custom report that does report ALL income in a very detailed layout, grouped by product type and/or service type, similar to how a true P&L report would do. Our report is titled Profit & Loss. We also track our expenses related to our business so our P&L actually contains both income and expense, with a complete summary for total income, total expenses, gross profit, net income, etc. And it is a report that is grouped by product type can be filtered by date range. Each income total in the P&L report is a link in the report that loads a Sales by Product report with line by line item detail for all the invoice items used in the P&L report for a specific product and/or service. For this post, I am only posting the queries needed to get the summary income from the WHMCS database tables. WHMCS does keep track of every product/service sold--it just does so in more than one database table. Not a great method but it does work. You just have to know which database table the different products, services, and domain information is stored in. This is easily accomplished when you know the product type. All products of type 'Other' are stored in tblinvoiceitems, and the relid field in tblinvoiceitems points directly to the product in tblproducts. All products of type 'Domain' are stored in tbldomains, and the relid field in tblinvoiceitems points directly to the domain in tbldomains. All products of type 'Hosting' are stored in tblhosting, and the relid field in tblinvoiceitems points directly to the product in tblhosting, and the packageid field in tblhosting points directly to the product in tblproducts. 1) Report Product Income Create a query to pull back all products from your WHMCS install, like so, for example: $query = "SELECT pg.id AS gid, pg.name AS groupname, p.id, p.name, p.type FROM tblproductgroups pg INNER join tblproducts p ON p.gid=pg.id ORDER BY pg.name, p.order"; $products = mysql_query($query); 2) Set your date range and running total $totalincome = 0; $startdate = '2011-06-01'; $enddate = '2011-06-30'; 3) Then, loop through each product, like so: while ( $product = mysql_fetch_array( $products ) ) { $pid = $product["id"]; # if product type = other get data from tblinvoiceitems if($product['type']=="other") { # get data from invoices $query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE relid=$pid AND invoiceid IN "; $query .="(SELECT id FROM tblinvoices WHERE (datepaid >= '$startdate' AND datepaid <= '$enddate')) "; $query .= "AND type='Other'"; $pincome = scalarQuery($query); $totalincome += $pincome; } # all other products go through tblhosting else { $query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE relid IN "; $query .= "(SELECT id FROM tblhosting WHERE packageid=$pid) AND invoiceid IN "; $query .="(SELECT id FROM tblinvoices WHERE (datepaid >= '$startdate' AND datepaid <= '$enddate')) "; $query .= "AND type!='Other' AND type !='Domain'"; $pincome = scalarQuery($query); $totalincome += $pincome; } 4) Report Domain Income Getting domain-related income is a three-step process because you need to know how much you earned in registrations, transfers, and renewals. 4a) For registrations, like so: $query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Registration%' "; $query .= "AND invoiceid IN "; $query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') "; $domainreg = scalarQuery($query); 4b) For transfers, like so: $query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Transfer%' "; $query .= "AND invoiceid IN "; $query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') "; $domaintfr = scalarQuery($query); 4c) For renewals, like so: $query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Renewal%' "; $query .= "AND invoiceid IN "; $query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') "; $domainrnw = scalarQuery($query); $totalincome += ($domainreg + $domaintfr + $domainrnw); 5) Late Fees is also income: $query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE invoiceid IN "; $query .="(SELECT id FROM tblinvoices WHERE id=ii.invoiceid "; $query .= "AND (datepaid >= '$startdate' AND datepaid <= '$enddate')) "; $query .= "AND type='LateFee'"; $latefees = scalarQuery($query); $totalincome += $latefees; Our custom P&L report, coupled with our own custom Sales by Product report, and a few helper scripts containing useful database parsing functions, report header date selectors, etc., works perfectly and we have been using them for almost two years now with 100% success. If you would like to see this in action, PM me.
  15. Chance

    Custom WHMCS Reports

    Hi, Since you posted a sample report that involves currencies, this implies you are looking for a report such as: Employee | USD | GBP | EUR ... etc. ------------------------------------------------------------------------------------ Tom | 4.00 USD | 6.50 GBP | 7.43 EUR ... etc. Dick | 7.25 USD | 8.50 GBP | 9.99 EUR ... etc. Harry | 3.00 USD | 5.70 GBP | 6.73 EUR ... etc. That is the most logical way of looking at what sales a specific employee brought in with a specific currency -- one column per currency with one row for each employee. *************************************************************************************************** But, take currency out of the equation, this implies you are looking for a report such as: Employee | Product #1 | Product #2 | Product #3 ... etc. -------------------------------------------------------------------------------------- Tom | 15 | 35 | 6 ... etc. Dick | 25 | 15 | 9 ... etc. Harry | 10 | 18 | 2 ... etc. That is the most logical way of looking at how many of each product a specific employee sold -- one column per product with one row for each employee. *************************************************************************************************** Which one are you looking for? Report on currency sales, or report on product sales count?
×

Important Information

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