|
|
|
|
|
|
| Trade Gain / Loss Statement: |
Date |
|
ID |
|
Debit |
|
Credit |
|
Balance |
|
");
//$res4 = mysql_query("ALTER TABLE commissions ORDER BY entry_day")
// or die("Invalid query: set1" . mysql_error());
$res5 = mysql_query("SET @balance2 = 0.00")
or die("Invalid query: set2" . mysql_error());
/*
$res2 = mysql_query("SELECT * FROM (SELECT p.plan_id, h.hit_date,
SUM(p.commis_exit) AS com_total, p.commis_exit AS com,
@balance1:= FORMAT(@balance1 - p.commis_exit, 2) AS balance1
FROM (plan p, portfolio po)
LEFT JOIN hit h ON (h.plan_id = p.plan_id AND h.hit_type != 3 AND h.hit_type != 4)
WHERE p.portfolio_id = '$portfolio_id' AND p.portfolio_id = po.portfolio_id AND (p.status_id = 4 OR p.status_id = 6)
GROUP BY plan_id WITH ROLLUP) t
ORDER BY hit_date DESC, com_total DESC, plan_id DESC")
or die("Invalid query: 2" . mysql_error());
*/
if(empty($sort_by)){
$sort_by = "plan_id";
}
if(empty($order_by)){
$order_by = "";
}else{
$order_by = "";
}
$shares = "SUM(f.share) / if(COUNT(pd2.day) > 0, COUNT(pd2.day), 1)";
$exit_price = "ROUND(AVG(f1.price), 2)";
$res2 = mysql_query("SELECT p.plan_id, p.term_id, DATE_FORMAT(p.entry_time, '%Y, %b. %e') AS entry_date, pr.symbol,
@balance2:= @balance2 + (p.term_id * (ROUND(AVG(f.price), 2) - $exit_price) * $shares) AS balance1,
FORMAT(@balance2 + (p.term_id * (ROUND(AVG(f.price), 2) - $exit_price) * $shares), 2) AS balance,
p.term_id * (ROUND(AVG(f.price), 2) - $exit_price) * $shares AS gain_loss2,
FORMAT(p.term_id * (ROUND(AVG(f.price), 2) - $exit_price) * $shares, 2) AS gain_loss
FROM (plan p, product pr)
LEFT JOIN hit th ON (th.plan_id = p.plan_id AND th.hit_type = 1)
LEFT JOIN hit lh ON (lh.plan_id = p.plan_id AND lh.hit_type = 2)
LEFT JOIN hit h ON (h.plan_id = p.plan_id AND h.hit_type < 3)
LEFT JOIN fill f ON (p.plan_id = f.plan_id AND f.share > 0)
LEFT JOIN fill f1 ON (p.plan_id = f1.plan_id AND f1.share < 0)
LEFT JOIN product_data pd1 ON (pd1.product_id = p.symbol_id AND pd1.day =
(SELECT day FROM product_data pd2, plan p WHERE pd2.product_id = p.symbol_id ORDER BY day DESC LIMIT 1))
LEFT JOIN product_data pd2 ON (pd2.product_id = p.symbol_id AND pd2.day <=
(SELECT h1.hit_date FROM hit h1 WHERE p.plan_id = h1.plan_id AND (h1.hit_type < 3 OR h1.hit_type = 5) ORDER BY h1.hit_date LIMIT 1)
AND pd2.day >= (SELECT f.trade_date FROM fill f WHERE p.plan_id = f.plan_id AND f.share > 0
ORDER BY f1.trade_date DESC LIMIT 1))
LEFT JOIN product_data pd3 ON (pd3.product_id = p.symbol_id AND pd3.day =
(SELECT h.hit_date FROM hit h WHERE h.plan_id = p.plan_id LIMIT 1))
WHERE p.symbol_id = pr.product_id
AND p.portfolio_id = '$portfolio_id' AND (p.status_id = 4 OR p.status_id = 6)
GROUP BY plan_id ORDER BY $sort_by $order_by LIMIT 100")
or die("Invalid query: 1" . mysql_error());
$n = 0;
while($row2 = mysql_fetch_array($res2, MYSQL_ASSOC)){
$balance1 += $row2['gain_loss2'];
$balance = number_format($balance1, 2);
$n++;
if(is_float($n/2)){
echo("");
}else{
echo(" ");
}
echo("| $row2[entry_date]");
echo(" |
|
");
echo("$row2[plan_id]");
echo(" |
|
");
if($row2['gain_loss2'] < 0){
echo("$row2[gain_loss]");
}
echo(" |
|
");
if($row2['gain_loss2'] > 0){
echo("$row2[gain_loss]");
}
echo(" |
| ");
if($balance1 > 0){
echo("$balance | ");
}else{
echo("$balance | ");
}
echo("
");
}
?>
|
|
|
|
|
|
|
|
|
| |
