Buy course plugin : bill generation

NicoDucou commented 6 years ago

There should be a billing management added to the Buy course plugin. When a payment is confirmed we should generate a bill and send it to the client.

When generate billing

list of action that should generate billing :

On the Courses and sessions sales report page (plugin/buycourses/src/sales_report.php) on the list of courses and sessions when clicking on "Subscribe user" button which gets in plugin/buycourses/src/service_sales_report.php when the action is confirm then there is a call to : $plugin->completeSale($sale['id']);
On the Services sales report page (plugin/buycourses/src/service_sales_report.php) when opening the service's sale's information window and clicking on "Confirm order" button which gets in plugin/buycourses/src/buycourses.ajax.php and there is a call to : $response = $plugin->completeServiceSale($id);
On Paypal automatic validation it returns to plugin/buycourses/src/success.php which on success there is a call to : $saleIsCompleted = $plugin->completeSale($sale['id']);

So we could just call the bill generation function when entering :

completeSale() function in plugin/buycourses/src/buy_course_plugin.class.php
completeServiceSale() function in plugin/buycourses/src/buy_course_plugin.class.php

Bill generation

We have to be able to enable or disable billing. We will also need information about the structure that is selling. All this should be general information that can be added in a new bloc "Billing configuration" on the payment configuration page (plugin/buycourses/src/paymentsetup.php) :

Name of company
Head of office address of company
Detail of company (Numéro Siren, code NAF, Forme Juridique, montant du capital social...)
VAT number
Default VAT rate

For the customer/client we also need information to be able to generate the bill. If billing is enabled, those data must have been set in the user profile and indicated on the order confirmation page (plugin/buycourses/src/process.php) below Payment methods, adding a bloc for billing information that the user can modify if needed or if it was not already registered in the profile. If the information is not set and billing is enabled then the order can not be confirmed.

The name and address of customer
VAT number of customer

When the payment is done (as seen above), we generate a bill (pdf document with the logo of the organisation as defined in the plateform parameters) with those data :

The date of edition of the bill, which is today
The number of the bill. The number should be unique and always be the following of the last bill.
The date when the sale occurred or the date of when the service will be done (for exemple the date of the training)
Name and address of head of office of the company
Detail of company
The name and address of customer
VAT number of customer
The description of the product saled
The list of products and the quantity for each and the individual price.
the price without VAT of each product
VAT rate and the total amount of VAT for the bill
Total price without VAT
Total price with VAT
The date of payment, which is today in all the case because we generate the bill on payment confirmation (Maybe we should add a form when the order is confirmed to indicate the date of payment because for a bank transfer the payment might be on a day and only a few days later the administrator confirms the order).

If the bill needs to be without VAT because Saler and/or Customer/Client is able to do so, this depends on the country of both and the legislation in those country. It's to complicated to manage this for the moment so we consider that we will always sale with VAT for the moment.

We should store in a table the number of the bill, the url to access the pdf document, the date, sale_id, the total amount without VAT, the total amount with VAT.

When the bill is generated it should be sent by email to the client.

Billing report

On the sales report page there should be a new button to access to billing report that shows a list of all the bills with for each :

bill number, client name, product type, product name, amount without VAT, amount with VAT

The bill number should be a link to download the bill in pdf.

It should have pagination to show per page 20, 50, 100 or all bills. There should be a filter to search for a bill number, client name, product type or product name There should be 2 dates selectors to be able to show the bills that are in between those 2 dates. There should be a check box in front of each line to enable global action to download the pdf of all the bills that are selected.

ywarnier commented 5 years ago

Just a note: @nosolored has been working on this, and I believe it is working now (and in 1.11.x)

NicoDucou commented 5 years ago

There is still a problem for the update of the plugin, when executing plugin/buycourses/update.php I get this error :

NonUniqueFieldNameException in AbstractMySQLDriver.php line 65: An exception occurred while executing 'ALTER TABLE plugin_buycourses_global_config ADD (
sale_email varchar(255) NOT NULL,
global_tax_perc int unsigned NOT NULL,
tax_applies_to int unsigned NOT NULL,
tax_name varchar(255) NOT NULL,
seller_name varchar(255) NOT NULL,
seller_id varchar(255) NOT NULL,
seller_address varchar(255) NOT NULL,
seller_email varchar(255) NOT NULL,
next_number_invoice int unsigned NOT NULL,
invoice_series varchar(255) NOT NULL,
sale_email varchar(255) NOT NULL
)':

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sale_email'

    in AbstractMySQLDriver.php line 65
    at AbstractMySQLDriver->convertException('An exception occurred while executing 'ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )': SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sale_email'', object(PDOException)) in DBALException.php line 169
    at DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing 'ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )': SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sale_email'') in DBALException.php line 145
    at DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )', array()) in Connection.php line 911
    at Connection->executeQuery('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in database.lib.php line 391
    at Database::query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in buy_course_plugin.class.php line 194
    at BuyCoursesPlugin->update() in update.php line 14

2/3 PDOException in PDOConnection.php line 90: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sale_email'

    in PDOConnection.php line 90
    at PDOConnection->query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in Connection.php line 908
    at Connection->executeQuery('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in database.lib.php line 391
    at Database::query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in buy_course_plugin.class.php line 194
    at BuyCoursesPlugin->update() in update.php line 14

1/3 PDOException in PDOConnection.php line 88: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'sale_email'

    in PDOConnection.php line 88
    at PDO->query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in PDOConnection.php line 88
    at PDOConnection->query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in Connection.php line 908
    at Connection->executeQuery('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in database.lib.php line 391
    at Database::query('ALTER TABLE plugin_buycourses_global_config ADD ( sale_email varchar(255) NOT NULL, global_tax_perc int unsigned NOT NULL, tax_applies_to int unsigned NOT NULL, tax_name varchar(255) NOT NULL, seller_name varchar(255) NOT NULL, seller_id varchar(255) NOT NULL, seller_address varchar(255) NOT NULL, seller_email varchar(255) NOT NULL, next_number_invoice int unsigned NOT NULL, invoice_series varchar(255) NOT NULL, sale_email varchar(255) NOT NULL )') in buy_course_plugin.class.php line 194
    at BuyCoursesPlugin->update() in update.php line 14

NicoDucou commented 5 years ago

The translation is only in spanish, after validating I will add french and english translation

NicoDucou commented 5 years ago

When desinstalling the plugin, the table plugin_buycourses_invoices is not removed and causes an error when reinstalling

NicoDucou commented 5 years ago

If there is not tax defined for course it generates an error en el momento de comprar el curso en la pagina https://11.chamilo.org/plugin/buycourses/src/process.php?i=35&t=1

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 772

And then when validation the order it generates this error :

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 772

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_COURSE - assumed 'TAX_APPLIES_TO_ONLY_COURSE' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 980

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_SESSION - assumed 'TAX_APPLIES_TO_ONLY_SESSION' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 981

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 1006

The same error when buying a session even if in the sale configuration of the plugin we set : Impuestos aplicados a => solo sesiones

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_COURSE - assumed 'TAX_APPLIES_TO_ONLY_COURSE' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 980

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_SESSION - assumed 'TAX_APPLIES_TO_ONLY_SESSION' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 981

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 1006

I changed the configuration to "Todos" to keep on going with the rest of the testing.

NicoDucou commented 5 years ago

I completed the process of buying a course and a session but I did not get the bill.

I bought courses with user nicoldd on https://11.chamilo.org/plugin/buycourses/src/course_panel.php I got the email informing that I bought the course and it is pending payment by bank transfer.

As an admin I validated the sale on https://11.chamilo.org/plugin/buycourses/src/sales_report.php?filter_type=0&status=0&user=&submit=&_qf__search=

but I get this error :

Notice: Array to string conversion in /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 117

Warning: Cannot modify header information - headers already sent by (output started at /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/sales_report.php on line 62

I'm subscribed to the course correctly but I did not received the bill. Probably because of the error above and the fact that the page did not finish to open so the process did not go until the end.

There is also no way for the user to find its bills on the plateform if the mail was not received.

Finally there is nothing in the readme that indicates what is supposed to append when billing is activated.

NicoDucou commented 5 years ago

@nosolored could you have a look at all the remarks in the different comments above and indicate if there is a problem in my process or if there should be corrections.

nosolored commented 5 years ago

thanks for the report, I review it

nosolored commented 5 years ago

https://github.com/chamilo/chamilo-lms/pull/2858 I hope it works

NicoDucou commented 5 years ago

Hi @nosolored

Thank you for the correction. I did some new testing since your update and here are the results.

The update of the plugin works fine, no more error. :+1: The desactivation of the plugin deletes all the tables from the DB correctly so no more error when activating it again. :+1:

But I had this notice when buying a course when getting on the page https://11.chamilo.org/plugin/buycourses/src/process.php?i=3&t=1

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 772

And then when confirming the process of buying the course I still have this warnings about TAX_APPLIES_TO_ONLY_SESSION with the option "impuestos aplicados a => solo sesiones"

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 772

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_COURSE - assumed 'TAX_APPLIES_TO_ONLY_COURSE' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 980

Warning: Use of undefined constant TAX_APPLIES_TO_ONLY_SESSION - assumed 'TAX_APPLIES_TO_ONLY_SESSION' (this will throw an Error in a future version of PHP) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 981

Notice: Undefined variable: taxAmount in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/buy_course_plugin.class.php on line 1006

Finaly I can not receive any invoice because when validating the sale on https://11.chamilo.org/plugin/buycourses/src/sales_report.php and clicking on "Subscribe user" I get this error and the page does not load :

Notice: Array to string conversion in /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 117

Warning: Cannot modify header information - headers already sent by (output started at /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/sales_report.php on line 62

nosolored commented 5 years ago

Hi @NicoDucou It's works correctly. The only message that appears on the screen is:

Notice: Array to string conversion in /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 117

Warning: Cannot modify header information - headers already sent by (output started at /furanet/sites/11.chamilo.org/web/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117) in /furanet/sites/11.chamilo.org/web/htdocs/plugin/buycourses/src/sales_report.php on line 62

it's because the debug mode is active and this error is not caused by the Buy courses plugin

The other errors should be cached, those lines were fixed in the last commit

NicoDucou commented 5 years ago

Thank you @nosolored every thing seems fine now. I can get the invoice of the buyer on the sales report page looking at the completed sales.

I do not close this task though because we can not manage the VAT number of the customer and also the customer can not access directly its invoice on the control panel. I think the invoice should be available on the control panel of the user chen he sees its list of courses and sessions that he bought.

For exemple with user nicoldd on https://11.chamilo.org/plugin/buycourses/src/course_panel.php We can see the list of course that this user bought but he can not see its invoice.

rajeekum commented 4 years ago

Hi All,

I need one help here. I have created a duplicate of "buy courses" plugins made some changes, Installed it and deleted it. Again installed this plugin from original one.

I have observed following issues:

custom_tab_1 is not getting deleted from DB after un-installing plugin. So "Buy Courses" Plugins remain irrespective of Plugin installed or not.
When Enabled; click on "Buy Courses" Tab but it enters - whoisonline.php page.
How to fix "custom_tab_1" which has replaced all my "help-doc" under administration configuration.

Regards Rajeev

chamilo / chamilo-lms