Level 18
Sounds like you need to get the SUM of each invoice, then deduct the deposits for each invoice.
May 4, 2022
3
Level 1
invoice with many items and deposit
I am building an invoice system in which the invoice has many items. my db structure as following
| invoices | | -------- | | id | | service_prices | | discount | | final_price | | deposit | | rest_price_to_pay | | total_paid | | date | | status (1= paid - 2= not-paid 3= deposit|
| invoice_items | | -------- | | invoice_id | | quantity | | price | | date |
it works fine but the problem is when I need to get a daily report of each item's income to know what income I get daily from each item, it does not work properly with deposits, because when I want to get the daily items income i foreach items from invoice_items table, so when the client does deposit for sure the income will not be the equal of each item price. so i don't know how to solve it in the database
Level 80
so i don't know how to solve it in the database
@shady hesham Well, you need to come up with the logic before you can solve it in the database.
Unfortunately, if customers are paying arbitrary deposit amounts then you’re not going to be able to get item income. How are you going to attribute a deposit payment to an item?
If you have an item that’s $30.00, and you have three of them in a single invoice (making the invoice total $90), and a customer pays a deposit of $50.00, then there’s no way to say what items that $50.00 deposit is covering.
Level 75
Normally income is a monthly report. I suggest not showing anything on these items until fully paid.
But you can also "split" the items.
50.00 deposit on 3 items, so each has been paid 50.00/3. Make sure you have good income and expense (transaction table) with an area to show partials, and a boolean (maybe checkbox) to indicate fully paid.
Please or to participate in this conversation.