Translating invoice items to accounts form

See more:

C#

SQL

database

Hi all,

Today i have a little bit complicated issue .

Now for example i have this invoice :

Item Code  | Qty | Unit Price | Discount | Total
book-1     | 1   |     5      | 2        | 3
book-2     | 2   |     5      | 2        | 8
Accessories| 1   |     2      | 1        | 1

I have the following sql tables
* "items" which contains item_Code as PK.
item_code |
book-1
book-2
Accessories
* "accounts" which contains account_id as PK .
account_id |

books sales account
books discount account
accessories sales account
accessories discount account

* "item_account_type" which contains the following :
1- type_id As PK.
2- type_name.
this table is filled as follows :

type_id  |  type_name
1        |  Sales account
2        |  discount account

* "item_account" which contain the following :
1- type_id As FK
2- item_code As FK
3- account_id As FK
For examples , this is the item_account for the same above invoice .
type_id | item_code | acount_id

1        | book-1      | books sales account
2        | book-1      | books discount account
1        | book-2      | books sales account
2        | book-2      | books discount account
1        | Accessories | accessories sales account
2        | Accessories | accessories discount account

Now, you may noticed that book-1 and book-2 had the same accounts .

the above invoice should be translated to the following form by checking each item account and if it's sales or discount and calculating the sums where account_id and type is the same . this is what i want to know ( how to make it like this ) :

the final form should be like this :

Account_id                   | total 
books sales account          | 15 
books discount account       | 4
accessories sales account    | 2
accessories discount account | 1

My tables are built correctly , but how do i reach to this point and save it in sql tables called " Entry" ..

I hope that my question is clear and please let me know if you have any questions ..

Thanks for trying to help me :) :)