ExpenseManager/expense_submit.php at master · devoshm/ExpenseManager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
<?php

if (isset ( $_GET ['action'] )) {
	require_once ('dbconnect.php');

	$action = $_GET ['action'];

	if ($action == "createtxn") {
		$date = $_GET ['date'];
		$paid = $_GET ['paid'];
		$item = $_GET ['item'];
		$share = $_GET ['share'];
		$amount = $_GET ['amount'];

		$date = DateTime::createFromFormat('d/m/Y', $date);
		$date = $date->format('Y-m-d');
		$noshares = sizeof($share);
		$share = implode($share, ",");

		$sql = "INSERT INTO item_purchase (ItemId, BoughtBy, BoughtDate, SharedBy, Cost) VALUES (:ItemId, :BoughtBy, :BoughtDate, :SharedBy, :Cost)";
 		$q = $db->prepare($sql);
 		$q->execute(array(':ItemId'=>$item, ':BoughtBy'=>$paid, ':BoughtDate'=>$date, ':SharedBy'=>$share, ':Cost'=>$amount));
 		$affected_rows = $q->rowCount();

 		echo $affected_rows;

 		if($affected_rows == 1) {
 			$txnid = $db->lastInsertId('TxnId');
 			$eachshare = $amount/$noshares;

 			$sql = "INSERT INTO balancesheet (TxnId, UserId, TxnDate, Share, Expense, Balance) VALUES (:TxnId, :UserId, :TxnDate, :Share, :Expense, :Balance)";
 			$q = $db->prepare($sql);
 			$q->execute(array(':TxnId'=>$txnid, ':UserId'=>$paid, ':TxnDate'=>$date, ':Share'=>0.00, ':Expense'=>$amount, ':Balance'=>-$amount));

 			$users = explode(",", $share);

 			foreach ($users as $user){
 				foreach ($db->query("select * from user_master where name='$user'") as $row)
 					$userid = $row['UserId'];

 				$sql = "INSERT INTO balancesheet (TxnId, UserId, TxnDate, Share, Expense, Balance) VALUES ('$txnid', '$userid', '$date', '$eachshare', '0.00', '$eachshare') on duplicate key update Share = '$eachshare', Balance = $eachshare-$amount";
 				$db->exec($sql);
 			}
 		}
 	}

 	if ($action == "edittxn") {
 		$txnid = $_GET ['txnid'];
 		$date = $_GET ['date'];
 		$paid = $_GET ['paid'];
 		$item = $_GET ['item'];
 		$share = $_GET ['share'];
 		$amount = $_GET ['amount'];

 		$date = DateTime::createFromFormat('d/m/Y', $date);
 		$date = $date->format('Y-m-d');
 		$noshares = sizeof($share);
 		$share = implode($share, ",");

 		$sql = "update item_purchase set ItemId = :ItemId, BoughtBy = :BoughtBy, BoughtDate = :BoughtDate, SharedBy = :SharedBy, Cost = :Cost where TxnId = :Txnid";
 		$q = $db->prepare($sql);
 		$q->execute(array(':ItemId'=>$item, ':BoughtBy'=>$paid, ':BoughtDate'=>$date, ':SharedBy'=>$share, ':Cost'=>$amount, ':Txnid'=>$txnid));
 		$affected_rows = $q->rowCount();

 		if($affected_rows == 1) {
 			$sql = "delete from balancesheet where TxnId = '$txnid'";
 			$db->exec($sql);

 			$eachshare = $amount/$noshares;

 			$sql = "INSERT INTO balancesheet (TxnId, UserId, TxnDate, Share, Expense, Balance) VALUES ('$txnid', '$paid', '$date', '0.00', '$amount', '-$amount')";
 			$db->exec($sql);

 			$users = explode(",", $share);

 			foreach ($users as $user){
 				foreach ($db->query("select * from user_master where name='$user'") as $row)
 					$userid = $row['UserId'];

 				$sql = "INSERT INTO balancesheet (TxnId, UserId, TxnDate, Share, Expense, Balance) VALUES ('$txnid', '$userid', '$date', '$eachshare', '0.00', '$eachshare') on duplicate key update Share = '$eachshare', Balance = $eachshare-$amount";
 				$affected_rows = $db->exec($sql);
 			}
 			echo $affected_rows;
 		}
 	}

 	if ($action == "deletetxn") {
 		$txnid = $_GET ['txnid'];

 		$sql = "delete item_purchase, balancesheet from item_purchase, balancesheet where item_purchase.TxnId=balancesheet.TxnId and item_purchase.TxnId = '$txnid'";
 		$affected_rows = $db->exec($sql);

 		echo $affected_rows;
 	}
}