Profit Calculation

[just.irwan]

Hi... i think i need somebody to help me with profit calculation project.
here its looks like.

[urtrivedi]

what is table struture?
what is ur database? mysql, mssql or other?
do you have sale date/pur date?

[LastMitch]

@just.irwan

Is this on Excel? The image you provided looks like a spreadsheet.

[LastMitch]

@just.irwan

If it's in Excel, you can download this

http://phpexcel.codeplex.com/

I used this before it's very helpful

[just.irwan]

@urtrivedi : yeah im using mysql as my database and sure i got the sale and purchase date but to make it simple i just shorten it.

@LastMitch : No this is my PHP programme, just to make it more simple i create one with excel 1st.

[vibhaJ]

You need to work on mysql queries.
No one can help you without your database structure.

[just.irwan]

here is the structure

CREATE TABLE example (
id int(11) NOT NULL auto_increment,
type varchar(50) NOT NULL,
name varchar(50) NOT NULL,
quantity int(50) NOT NULL,
price int(50) NOT NULL,
total int(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO example (id, type, name, quantity, price, total) VALUES
(1, 'Buy', 'Midas', 50, 500, 25000),
(2, 'Buy', 'Midas', 25, 600, 15000),
(3, 'Buy', 'Midas', 100, 450, 45000),
(4, 'Sell', 'Midas', 10, 1200, 12000),
(5, 'Sell', 'Midas', 20, 1000, 20000),
(6, 'Sell', 'Midas', 15, 1100, 16500),
(7, 'Sell', 'Midas', 30, 1000, 30000),
(8, 'Sell', 'Midas', 25, 1000, 25000);

[just.irwan]

now what i want is the view like this :

For sale No.1

Item | Qty | Sell | Total Sell | Buy | Total Profit |
Midas | 10 | 1200 | 12000 | 10 x 500 = 5000 | 7000 |

For sale No.2

Item | Qty | Sell | Total Sell | Buy | Total Profit |
Midas | 20 | 1000 | 20000 | 20 x 500 = 10000| 10000 |

For sale No.3

Item | Qty | Sell | Total Sell | Buy | Total Profit |
Midas | 15 | 1100 | 16500 | 15 x 500 = 7500 | 9000 |

For sale No.4

Item | Qty | Sell | Total Sell | Buy | Total Profit |
Midas | 30 | 1000 | 30000 | 5 x 500 = 2500 | 12500 |
| 25 x 600 = 15000| |

For sale No.5

Item | Qty | Sell | Total Sell | Buy | Total Profit |
Midas | 25 | 1000 | 25000 | 25 x 450 = 11250| 13750 |

[urtrivedi]

where is the date?

and I think sql query only can not help, you need to do order by date in query and then you can see result and calculate profie for each row in php.

[Pip88]

what you just need to do is employ the use of a select statement, order by ****, then you display your output on an html form.

[just.irwan]

@urtrivediOk here come the database with date

CREATE TABLE example (
id int(11) NOT NULL auto_increment,
date date NOT NULL,
type varchar(50) NOT NULL,
name varchar(50) NOT NULL,
quantity int(50) NOT NULL,
price int(50) NOT NULL,
total int(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--

-- Dumping data for table example

INSERT INTO example (id, date, type, name, quantity, price, total) VALUES
(1, '2012-09-18', 'Buy', 'Midas', 50, 500, 25000),
(2, '2012-09-19', 'Buy', 'Midas', 25, 600, 15000),
(3, '2012-09-20', 'Buy', 'Midas', 100, 450, 45000),
(4, '2012-09-21', 'Sell', 'Midas', 10, 1200, 12000),
(5, '2012-09-21', 'Sell', 'Midas', 20, 1000, 20000),
(6, '2012-09-21', 'Sell', 'Midas', 15, 1100, 16500),
(7, '2012-09-22', 'Sell', 'Midas', 30, 1000, 30000),
(8, '2012-09-22', 'Sell', 'Midas', 25, 1000, 25000);

@Pip88 : can u help me with the viewing code?
@ALL : If there any thing that should be added in my database, please ure welcome to modify it.

[diafol]

DOn't know if total should be there at all - this IMO should be a calculated field - only 'physical' as a result of a query.

SELECT `id`, `date`, `type`, `name`, `quantity`, `price`, `quantity` * `price` AS `total` FROM `example`

The output will be just a while loop, surely?