Magento database: Invoice items database table?

Does anyone know where the Invoice data is stored in Magento database?

For example, I've found that the order data is stored in sales_order, sales_flat_order, sales_flat_order_item.

I've also found out that the main invoice data i开发者_开发知识库s stored in sales_order_entity, sales_order_entity_decimal and sales_order_entity_int. Through that I can change the subtotal and totals of the invoice in the system.

But! I don't know where to find the items data? For orders, that data is in sales_flat_order_item, but my sales_flat_invoice_item table is empty?!

http://img809.imageshack.us/img809/1921/invoicey.jpg

---

I will tell you what I know for 1.4.0.1 which is the version i currently develop for, it may or may not be the same for whatever version you are using.

Also, why are you in the database anyways? Magento has made models for you to use so that you don't have to work in the database. Regardless I will describe how I find whatever attribute I'm looking for ...

For starters I'm assuming that your already logged into the database via a mysql client, run

SELECT `entity_type_id`,`entity_type_code`,`entity_table` FROM `eav_entity_type`

which will get you something like ...

+----------------+----------------------+----------------------------------+
| entity_type_id | entity_type_code     | entity_table                     |
+----------------+----------------------+----------------------------------+
|              1 | customer             | customer/entity                  |
|              2 | customer_address     | customer/address_entity          |
|              3 | catalog_category     | catalog/category                 |
|              4 | catalog_product      | catalog/product                  |
|              5 | quote                | sales/quote                      |
|              6 | quote_item           | sales/quote_item                 |
|              7 | quote_address        | sales/quote_address              |
|              8 | quote_address_item   | sales/quote_entity               |
|              9 | quote_address_rate   | sales/quote_entity               |
|             10 | quote_payment        | sales/quote_entity               |
|             11 | order                | sales/order                      |
|             12 | order_address        | sales/order_entity               |
|             13 | order_item           | sales/order_entity               |
|             14 | order_payment        | sales/order_entity               |
|             15 | order_status_history | sales/order_entity               |
|             16 | invoice              | sales/order_entity               |
|             17 | invoice_item         | sales/order_entity               |
|             18 | invoice_comment      | sales/order_entity               |
|             19 | shipment             | sales/order_entity               |
|             20 | shipment_item        | sales/order_entity               |
|             21 | shipment_comment     | sales/order_entity               |
|             22 | shipment_track       | sales/order_entity               |
|             23 | creditmemo           | sales/order_entity               |
|             24 | creditmemo_item      | sales/order_entity               |
|             25 | creditmemo_comment   | sales/order_entity               |
+----------------+----------------------+----------------------------------+

We want to know more about the "invoice_item" entity so lets see what attributes it has ... run

SELECT `attribute_id`,`entity_type_id`,`attribute_code`,`backend_type` FROM `eav_attribute` WHERE `entity_type_id`=17;

and you'll get something like ...

+--------------+----------------+----------------------------------+--------------+
| attribute_id | entity_type_id | attribute_code                   | backend_type |
+--------------+----------------+----------------------------------+--------------+
|          349 |             17 | additional_data                  | text         |
|          340 |             17 | base_cost                        | decimal      |
|          346 |             17 | base_discount_amount             | decimal      |
|          345 |             17 | base_price                       | decimal      |
|          679 |             17 | base_price_incl_tax              | decimal      |
|          348 |             17 | base_row_total                   | decimal      |
|          681 |             17 | base_row_total_incl_tax          | decimal      |
|          347 |             17 | base_tax_amount                  | decimal      |
|          567 |             17 | base_weee_tax_applied_amount     | decimal      |
|          568 |             17 | base_weee_tax_applied_row_amount | decimal      |
|          579 |             17 | base_weee_tax_disposition        | decimal      |
|          580 |             17 | base_weee_tax_row_disposition    | decimal      |
|          337 |             17 | description                      | text         |
|          342 |             17 | discount_amount                  | decimal      |
|          336 |             17 | name                             | varchar      |
|          334 |             17 | order_item_id                    | int          |
|          333 |             17 | parent_id                        | static       |
|          341 |             17 | price                            | decimal      |
|          678 |             17 | price_incl_tax                   | decimal      |
|          335 |             17 | product_id                       | int          |
|          339 |             17 | qty                              | decimal      |
|          344 |             17 | row_total                        | decimal      |
|          680 |             17 | row_total_incl_tax               | decimal      |
|          338 |             17 | sku                              | varchar      |
|          343 |             17 | tax_amount                       | decimal      |
|          571 |             17 | weee_tax_applied                 | text         |
|          569 |             17 | weee_tax_applied_amount          | decimal      |
|          570 |             17 | weee_tax_applied_row_amount      | decimal      |
|          577 |             17 | weee_tax_disposition             | decimal      |
|          578 |             17 | weee_tax_row_disposition         | decimal      |
+--------------+----------------+----------------------------------+--------------+

the last column (backend_type) combined with the table for the entity (entity_table) is where the attribute for that entity will be so attribute "additional_data" should be in sales_order_entity_text with an attribute_id of 349.

Armed with this information now we just need to find an invoice, I'll use an example from a test install of mine. Lets look for the "base_price" of an invoice item.

First lets find all the items that are associated to the invoice (in my case invoice entity_id of 1303954)

SELECT * FROM `sales_order_entity` WHERE `entity_type_id`=17 AND `parent_id`=1303954;

which gives 2 items

+-----------+----------------+------------------+--------------+-----------+----------+---------------------+---------------------+-----------+
| entity_id | entity_type_id | attribute_set_id | increment_id | parent_id | store_id | created_at          | updated_at          | is_active |
+-----------+----------------+------------------+--------------+-----------+----------+---------------------+---------------------+-----------+
|   1303955 |             17 |                0 |              |   1303954 |     NULL | 2011-06-01 14:10:48 | 2011-06-01 14:10:48 |         1 |
|   1303956 |             17 |                0 |              |   1303954 |     NULL | 2011-06-01 14:10:48 | 2011-06-01 14:10:48 |         1 |
+-----------+----------------+------------------+--------------+-----------+----------+---------------------+---------------------+-----------+

Lets choose the first one and find the 'base_price'

SELECT * FROM `sales_order_entity_decimal` WHERE `attribute_id`=345 AND `entity_id`=1303955;

Which gives us ....

+----------+----------------+--------------+-----------+---------+
| value_id | entity_type_id | attribute_id | entity_id | value   |
+----------+----------------+--------------+-----------+---------+
|  7361390 |             17 |          345 |   1303955 | 31.2500 |
+----------+----------------+--------------+-----------+---------+

Which of course its just a simple update to change it.

Again if you can do it via a Magento model I would highly suggest you do it that way, but if manual is the only way to go then well I hope this helped :)