it shouldnt be hard to modify the script to have consistence in invoicing; the problem remains (i probably speak for italy only) if the merchant sells both online and offline and already have an invoicing system;
lets see some workflows
if he keeps its own invoicing system, its just a matter of defining the opencart invoice as simpy orders (and it doesnt need any modification, besides removing the lable "invoice" and changing it to "order"); once the order is received, its printed and sent (or mailed) to order preparation departement, once is ready is sent back (and status updated), sent to the invoicing department that will make an invoice based on the order, sent back and status updated, then is shipped...
this is useful for company well estabilished with a lot of customers and a legacy invoicing system
if the merchant want to use opencart as main invoicing system, we must change the invoice numbering according to the laws, and every offline order must be inserted in opencart by an operator and precessed there;
this is very useful for a new company, or maybe for company that sell most online and wish to process offline orders with opencart (and this is very useful for statistics)
there are of course hybrid solutions such us connecting to legacy invoice system (most of them offers api webservices to do that), but this could be little more complicated
You speak for the whole UE, Italy (and maybe other countries) has the additional requirement of "restart the numbers every year".lenna wrote:(i probably speak for italy only)
However what you say about online/offline sync is true, it could be another issue for a UE company.
Which most (if not all) of us are NOT.lenna wrote: sent to the invoicing department that will make an invoice based on the order, sent back and status updated, then is shipped...
this is useful for company well estabilished with a lot of customers and a legacy invoicing system
I have a small business and If a need to buy an extra application to enter the orders manualy, then create the invoice and send them to the customer seperate I will use a cart that does offer a legal invoice system.
It is not that I cannot effort the extra cost of an invoice program + additional mail cost but I do not want to use 3 programs if it can be handled by one.
No, if the invoice-system in OC is build according to the specs givin in this topic the issue is solved and OC can be used by small business and large business, the small ones can use the OC invoicing and larger ones can use an additional program if needed.if the merchant want to use opencart as main invoicing system, we must change the invoice numbering according to the laws, and every offline order must be inserted in opencart by an operator and precessed there;
this is very useful for a new company, or maybe for company that sell most online and wish to process offline orders with opencart (and this is very useful for statistics)
there are of course hybrid solutions such us connecting to legacy invoice system (most of them offers api webservices to do that), but this could be little more complicated
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
its quiete easy to adapt the invoicing system to the law;No, if the invoice-system in OC is build according to the specs givin in this topic the issue is solved and OC can be used by small business and large business, the small ones can use the OC invoicing and larger ones can use an additional program if needed.
anway if i have already an invoicing system that for example has already printed invoice 0001-2009 0002-2009
the next invoice ill make with OC should be 0003-2009 (so OC MUST read from the legacy, that is not impossibile... i already did that for a customer, both syncing with invoicing and stock appilcation) and next made by legacy should be 0004-2009
of course if you have your small business and you use only OC it really useful to treat offline orders as if they come from internet (when you receive an order by fax, or by voice, you collect user data, insert into OC and make an order with this new user!)
just to speak, i dont have a personal online shop, i usually install, customize, consult for company that already have an offline business, so my expreience is limited to this
as i said before, using a correct invoicing its not hard; its just a matter of defyining a rule, probably add a field in the order table, probably a new (small) table to handle the invoice number, and then modify the order.php page to set an invoice id to the order once the order reach a certain status (complete? shipped?)
Daniel is looking in to that.lenna wrote:its quiete easy to adapt the invoicing system to the law;
Daniel is also looking at the Manual Order Entry/Order Edit (Here).of course if you have your small business and you use only OC it really useful to treat offline orders as if they come from internet (when you receive an order by fax, or by voice, you collect user data, insert into OC and make an order with this new user!)
The invoice should only be allowed to be created/printed when the order is set to: complete. Then you can print the invoice and ship it with the order (I use the invoice as shipping-label).just to speak, i dont have a personal online shop, i usually install, customize, consult for company that already have an offline business, so my expreience is limited to this
as i said before, using a correct invoicing its not hard; its just a matter of defyining a rule, probably add a field in the order table, probably a new (small) table to handle the invoice number, and then modify the order.php page to set an invoice id to the order once the order reach a certain status (complete? shipped?)
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
this is quiete easy, you simply invoke a function called (for example) generateInvoce($order_id) insideThe invoice should only be allowed to be created/printed when the order is set to: complete. Then you can print the invoice and ship it with the order (I use the invoice as shipping-label).
public function editOrder($order_id, $data) {
---
}
when (int)$data['order_status_id'] is equal to a status of your choice
generateInvoice would simply create a valid invoice number based on year, previous invoice number (for example you can store everyting in a table called invoices (with cols as year|invoicenumber|issuedon)
the algorithm sould be self explanatory if year is already present in the table you choos the highest invoicenumber, add one, and insert as a new row, if year is not present then invoicenumber is assumed to 0 and you add 1 the same (in both case you store the invoice date).
once generated the invoice number you can update on table order adding invoicenumber and issuedon for entry $order_id.
at this poin the BO interface would have a button to show/print/pdf an invoice if the order got am invoicenumber, otherwise it will have only button to show/print/pdf the order.
you can even add this feature to the user interface, allowing him to view his invoice in digital.
we can even add a "reset" button to use once everything is tested, and a completedtest status to not create invoice for testing orders.
i can do that in a couple of days... im quiete busy at the moment...
[/quote]lenna wrote: generate Invoice would simply create a valid invoice number based on year, previous invoice number (for example you can store everyting in a table called invoices (with cols as year|invoicenumber|issuedon)
the algorithm sould be self explanatory if year is already present in the table you choos the highest invoicenumber, add one, and insert as a new row, if year is not present then invoicenumber is assumed to 0 and you add 1 the same (in both case you store the invoice date).
once generated the invoice number you can update on table order adding invoicenumber and issuedon for entry $order_id.
at this poin the BO interface would have a button to show/print/pdf an invoice if the order got am invoicenumber, otherwise it will have only button to show/print/pdf the order.
you can even add this feature to the user interface, allowing him to view his invoice in digital.
The option to have a customer vieuw/print their own invoice(s) is ideal but for the moment optional I'll gues.
Lets focus on getting the invoice-number issue solved
I rather would have a query to reset the invoice-table instead of a reset-buttonwe can even add a "reset" button to use once everything is tested, and a completedtest status to not create invoice for testing orders.
If you could give it a try but do communicate with Daniel as he said he would also look into the matter.i can do that in a couple of days... im quiete busy at the moment...
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
ok,
here's my solution for a (probably) correct invoicing system, try this on a test ambient, dont take it as bullet proof, dont try it on a production environment, please backup DB and files before startin
foreword: maybe someone could find my approach a little baroque i made a new table where it seems i keep duplicates of orders, when it would be possible to add an auto-increment column on the order table; but i usually works on big system so i prefere to keep things as small as possible, anyway, you can change as you like.
The generated invoice is a six digit zerofill number, the date of the invoice is generated at the momento of invoicing and cant be changed (unless you play with your db)
first of all
DB enhance
add a new table to keep track of invoices, add two columns in order table to transforma an "order" into a pay "document"; this assume DB_PREFIX is 'sh_', change to suit your preferences
next, edit admin/model/customer/order.php at the beginning of the function editOrder (just before the two UPDATE INSERT query in the function), change the status to the status you want the invoice be generated
to see and print the invoice modify the file admin/controller/customer/order.php in the function invoice(), just after where it sets $this->data['date_added'] = date($this->language->get('date_format_short'), strtotime($order_info['date_added']));
then you need to modify the language/english/customer/order.php (englishe and translations of course) file to modify and add the following keys:
this will print out the order (in backoffice) as an order if it has not completed, and as an invoice (that you can print) if the order has become an invoice
hope this could help... and please be careful with your code!
here's my solution for a (probably) correct invoicing system, try this on a test ambient, dont take it as bullet proof, dont try it on a production environment, please backup DB and files before startin
foreword: maybe someone could find my approach a little baroque i made a new table where it seems i keep duplicates of orders, when it would be possible to add an auto-increment column on the order table; but i usually works on big system so i prefere to keep things as small as possible, anyway, you can change as you like.
The generated invoice is a six digit zerofill number, the date of the invoice is generated at the momento of invoicing and cant be changed (unless you play with your db)
first of all
DB enhance
add a new table to keep track of invoices, add two columns in order table to transforma an "order" into a pay "document"; this assume DB_PREFIX is 'sh_', change to suit your preferences
Code: Select all
CREATE TABLE IF NOT EXISTS `sh_invoice` (
`year` year(4) NOT NULL,
`invoice` int(6) unsigned zerofill NOT NULL,
`issued` date NOT NULL,
KEY `invoice` (`invoice`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `sh_invoice`
--
INSERT INTO `sh_invoice` (`year`, `invoice`, `issued`) VALUES (2009, 000001, '2009-11-17');
alter table `sh_order` add column `invoice_id` int(6) after `order_id`;
alter table `sh_order` add column `invoice_date` DATE after `order_id`;
Code: Select all
if ((int)$data['order_status_id']==3) { // THIS IS THE STATUS THAT WILL GENERATE THE INVOICE, IN THIS CASE IS SHIPPED (YOU CAN CHANGE TO THE STATUS YOU PREFERE, USUALLY COMPLETED OR SHIPPED)
$year=date("Y");
$last_inv=$this->db->query("SELECT invoice from " . DB_PREFIX . "invoice where year='" . $year ."' order by invoice DESC");
if (count($last_inv->row)>0) {
$next_inv=$last_inv->row['invoice']+1;
} else {
$next_inv=1;
}
$this->db->query("INSERT INTO " . DB_PREFIX . "invoice (year,invoice,issued) VALUES (".$year.",".$next_inv.", NOW())");
$this->db->query("UPDATE " . DB_PREFIX . "order set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");
}
Code: Select all
if ($order_info['invoice_id']>0) {
$this->data['order_id']=$order_info['invoice_id'];
$this->data['date_added']=date($this->language->get('date_format_short'), strtotime($order_info['invoice_date']));
$this->data['text_invoice'] = $this->language->get('text_invoice_real');
$this->data['text_invoice_date'] = $this->language->get('text_invoice_date_real');
$this->data['text_invoice_no'] = $this->language->get('text_invoice_no_real');
}
Code: Select all
$_['text_invoice'] = 'Order';
$_['text_invoice_no'] = 'Order No.:';
$_['text_invoice_date'] = 'Order Date:';
$_['text_invoice_real'] = 'Invoice';
$_['text_invoice_no_real'] = 'Invoice No.:';
$_['text_invoice_date_real'] = 'Invoice Date:';
hope this could help... and please be careful with your code!
Thanks for the code, could you be more specific to where this code must be added.
As I'm no coder the words "the beginning of the function editOrder (just before the two UPDATE INSERT query in the function)," sound abracadabra to me
So if you could explain exectly where to add this code (the db change I can handle).
As for an example:
In admin/model/customer/order.php find:
just after $this->db->query add:
As I'm no coder the words "the beginning of the function editOrder (just before the two UPDATE INSERT query in the function)," sound abracadabra to me
So if you could explain exectly where to add this code (the db change I can handle).
As for an example:
In admin/model/customer/order.php find:
Code: Select all
$this->db->query("UPDATE `" . DB_PREFIX . "order` SET order_status_id = '" . (int)$data['order_status_id'] . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");
Code: Select all
if ((int)$data['order_status_id']==3) { // THIS IS THE STATUS THAT WILL GENERATE THE INVOICE, IN THIS CASE IS SHIPPED (YOU CAN CHANGE TO THE STATUS YOU PREFERE, USUALLY COMPLETED OR SHIPPED)
$year=date("Y");
$last_inv=$this->db->query("SELECT invoice from " . DB_PREFIX . "invoice where year='" . $year ."' order by invoice DESC");
if (count($last_inv->row)>0) {
$next_inv=$last_inv->row['invoice']+1;
} else {
$next_inv=1;
}
$this->db->query("INSERT INTO " . DB_PREFIX . "invoice (year,invoice,issued) VALUES (".$year.",".$next_inv.", NOW())");
$this->db->query("UPDATE " . DB_PREFIX . "order set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");
}
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
Anyway, I managed to get the code "in" my files but when i changed the orderstatus to Shipped I get:
Code: Select all
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order set invoice_id=1, invoice_date=NOW() where order_id = '3'' at line 1
Error No: 1064
UPDATE order set invoice_id=1, invoice_date=NOW() where order_id = '3'Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
Great news!Daniel wrote:invopice will be in the next version.
Yep, that's what Open Source is forthank you to the person for heling me by posting how to fix this issue.
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
sorry i was here latei2Paq wrote:Anyway, I managed to get the code "in" my files but when i changed the orderstatus to Shipped I get:Code: Select all
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order set invoice_id=1, invoice_date=NOW() where order_id = '3'' at line 1 Error No: 1064 UPDATE order set invoice_id=1, invoice_date=NOW() where order_id = '3'
did you update your order table?
anyway you proabbly need to enclcose the name of the table order in ampersand like changing
Code: Select all
$this->db->query("UPDATE " . DB_PREFIX . "order set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");Code: Select all
$this->db->query("UPDATE `" . DB_PREFIX . "order` set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");before UPDATE and after order;
since order is a reserved mysql word you have to "escape" it to use as a name of a table; or use a DB_PREFIX to avoid this error
try this solution and let me know
the code reset invoice number to 000001 when a new year startsDoes the code also reset the progression on every new year?
Yeah I've seen the tricklenna wrote:the code reset invoice number to 000001 when a new year startsDoes the code also reset the progression on every new year?
Code: Select all
if (count($last_inv->row)>0) {
...
} else {
$next_inv=1;
}
Yes I did, I needed to remove the prefix of the tablelenna wrote: sorry i was here late
did you update your order table?
It does not show an error anymore but I can print invoice with or without the order set to shipped.anyway you proabbly need to enclcose the name of the table order in ampersand like changingtoCode: Select all
$this->db->query("UPDATE " . DB_PREFIX . "order set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");notice this symbol `Code: Select all
$this->db->query("UPDATE `" . DB_PREFIX . "order` set invoice_id=" .$next_inv. ", invoice_date=NOW() where order_id = '" . (int)$order_id . "'");
before UPDATE and after order;
since order is a reserved mysql word you have to "escape" it to use as a name of a table; or use a DB_PREFIX to avoid this error
try this solution and let me know
Plus it says invoice 3.
I think it will be better to add the year to the invoice-number as needed in Italy: 0001-2009the code reset invoice number to 000001 when a new year startsDoes the code also reset the progression on every new year?
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
If the invoice does include the date there's no problem, however it could be a good addition just to make things clearer.i2Paq wrote: I think it will be better to add the year to the invoice-number as needed in Italy: 0001-2009
So, anyone been testing lenna's solution yet? Would like to hear some opinions on it
Request Reviews v1.0 released.
I have but could not get it to work.dbstr wrote:So, anyone been testing lenna's solution yet? Would like to hear some opinions on it
As Daniel is working on the same thing and implementing it in the next release of OC (I think) I'll just wait.
Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________
Our FREE search: Find your answer FAST!.
[How to] BTW + Verzend + betaal setup.
Who is online
Users browsing this forum: No registered users and 19 guests
