Add new field to customers table

[tamarillojohn]

I need to add a new field to the database, in the customers table. It will not be a required field. At present Prestashop creates a customer_id which increases by +1 each time a new customer is added. I need to identify each customer by an existing acount number eg "C0832" . Since the customer_id field is integer only I cant change that, so I thought of adding an extra field that could be inputed manually after the customer had registered. Any thoughts I know I will find valuable.

[phrasespot]

If you modify a native table, you will make updates more involved. Customer id field is unique so use that as a key in a new table you will create. Something like

 

| my_new_table		   |
-----------------------------
| id_customer | identifier |
-----------------------------
| 412		| C0832	  |
-----------------------------
...

[tamarillojohn]

Thanks for that - Im just struggling on constructing the query i would run in phpmyadmin to create the new table. Eventually orders will be exported out of prestashop and be identified by the (C0832) number, so SAP (there companys accounting, sales and stock keeping system)can recognise it.i have already re-written the export module to do this. If i can get this solved then not only I, bu many others can integrate Prestashop with existing accounting software for many companys.

[phrasespot]

Creating the table is only the third of the solution. You will also need a way to populate it and a mechanism to keep it up-to-date as new accounts are created. Below is an example how you could create it (won't work as written, replace example names). How you populate it will depend on where the unique identifiers you use (e.g. C0832) are coming from. To keep it up-to-date, you will need additional code to hook customer account create/modify/delete.

 

CREATE  TABLE IF NOT EXISTS `mydb`.`my_new_table` (
 `id_customer` INT(10) UNSIGNED NOT NULL ,
 `my_identifier` VARCHAR(45) NOT NULL ,
 INDEX `fk_my_new_table_customer` (`id_customer` ASC) ,
 UNIQUE INDEX `my_identifier_UNIQUE` (`my_identifier` ASC) ,
 CONSTRAINT `fk_my_new_table_customer`
FOREIGN KEY (`id_customer` )
REFERENCES `mydb`.`PREFIX_customer` (`id_customer` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB

 

If i can get this solved then not only I, bu many others can integrate Prestashop with existing accounting software for many companys.

There are already modules available for various systems. Did you check the add-ons store?

[tamarillojohn]

Thank you for that. Before I run this, I should add im running 1.3.1.1. - all the queries I have run so far have the Engine = MyISAM DEFAULT CHARSET=utf8; and also a refernece to PREFIX for each table.

 

PS: This extra field does not need to be maintined, it is just another field that will be exported with each customer order, in fact we dont want customers to be able to edit it, only admin.

 

This is what I have so far, with the database name being "tamarillojohn_trade"

 

CREATE  TABLE IF NOT EXISTS `tamarillojohn_trade`.`sap_account` (
 `id_customer` INT(10) UNSIGNED NOT NULL ,
 `sap_customer` VARCHAR(45) NOT NULL ,
 INDEX `fk_sap_account_customer` (`id_customer` ASC) ,
 UNIQUE INDEX `sap_customer_UNIQUE` (`sap_customer` ASC) ,
 CONSTRAINT `fk_sap_account_customer`
    FOREIGN KEY (`id_customer` )
    REFERENCES `tamarillojohn_trade`.`PREFIX_customer`
(`id_customer` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = MyISAM DEFAULT CHARSET=utf8;

[tamarillojohn]

Ok I have run this query and all is good. Im assuming now I have to populate the sap_customer field via some sort of admin function, as this wont be a fileld customers can edit, just one they can see. I would like the sap_customer number to appear in the identity (personnal info) page (not editable just displayed) from the My Account block, so i guess identity.php and identity.tpl (theme file) will have to be modified too. Then ofcourse I need it to appear in order-detail.php. Starting to wish that I'd never bloody asked now lol.

[phrasespot]

What you are trying to do is too involved to talk you thru in a forum post but here are few pointers. Don't modify any native file. Make a module that will

- hook createAccount and override Customer class update method to keep the new table up-to-date. how you do that depends on where is this sap code coming from

- hook customerAccount (not myAccountBlock), query new table and display the relevant code

- hook orderDetailDisplayed, query new table and display the relevant code