<?php
//retrieving stock data and storing them in the database
$row = 1;
if (($handle = fopen("http://download.finance.yahoo.com/d/quotes.csv?s=AAPL+ADBE+AMZN+APOL+CSCO+CTSH+DELL+ERIC+GOOG+INFY+INTC+MSFT+ORCL+PROV+QCOM+SNDK+SWIR+VRTU+YHOO+ACCL+ACIW+BBSI+BITS+BLKB+BCSI+CVLT+CNQR+CCUR+PROJ+ELRC+ERII+FFDF+FNGN+GSBC+GRNB+GCBC+HALL+HBNK+HBHC+IBOC+JXSB+JFBC+KINS
+LKFN+SIRI+RAX+VVUS+DNDN+EBAY+RMBS&f=nsabp5c1v", "r")) !== FALSE)
{
	$con = mysql_connect("localhost:3306","","");
	if (!$con)
	{
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("test", $con);
	mysql_query("delete from stockdata");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
	{
		$num = count($data);
		$row++;
		for ($c=0; $c < $num; $c++) 
		{
		   $var[$c]= $data[$c] ;
		}
		//inserting fields into stockdata database
		mysql_query("INSERT INTO stockdata (company,symbol,ask,bid,price,stockchange,stockvolume)
		VALUES ('$var[0]','$var[1]','$var[2]','$var[3]','$var[4]','$var[5]','$var[6]')");
	}
	mysql_close($con);
    fclose($handle);
}
?>


<?php 

$con = mysql_connect("localhost:3306","","");
	if (!$con)
	{
		die('Could not connect: ' . mysql_error());
	}
mysql_select_db("test", $con);


//checking for expired transactions
$current = mktime(0,0,0,date("m"),date("d"),date("Y"));
$pendingres=mysql_query("select orderid,UNIX_TIMESTAMP(transaction_date) as unixtime from orders where status='pending'");
$pendingnum=mysql_num_rows($pendingres);
$v=0;
while($row = mysql_fetch_array($pendingres))
{
	$pendingid[$v]=$row['orderid'];
	$pending_date[$v]=$row['unixtime'];
	$v++;
}
for($j=0;$j<$pendingnum;$j++)
{
	$expiry = mktime(0,0,0,date("m",$pending_date[$j]),date("d",$pending_date[$j])+90,date("Y",$pending_date[$j]));
	if($current==$expiry)
	{
		mysql_query("delete from orders where orderid=$pendingid[$j]");
	}
}



//processing buy limit orders
$limit_bresult=mysql_query("select * from orders where status='pending' and action='buy' and order_type='limit';");
$buycount = mysql_num_rows($limit_bresult);
$i=0;
while($row = mysql_fetch_array($limit_bresult))
		  {
			$orderid[$i]=$row['orderid'];
			$tickersymbol[$i]=$row['tickersymbol'];
			$company[$i]=$row['company'];
			$lprice[$i]=$row['threshold1'];
			$action[$i]=$row['action'];
			$order_type[$i]=$row['order_type'];
			$quantity[$i]=$row['quantity'];
			$transaction_date[$i]=$row['transaction_date'];
			$userid[$i]=$row['userid'];
			$i++;
		  }

//extracting the bid prices
for($j=0;$j<$buycount;$j++)
{
	$bid_result=mysql_query("select bid from stockdata where symbol='$tickersymbol[$j]'");
	while($row = mysql_fetch_array($bid_result))
	{
		$bid[$j]=$row['bid'];
	}
	$email_result=mysql_query("select Email from userdata where userid=$userid[$j]");
	while($row = mysql_fetch_array($email_result))
	{
		$email[$j]=$row['Email'];
	}
}
$k=0;
for($k=0;$k<$buycount;$k++)
{
	if($lprice[$k]>$bid[$k])
	{
		mysql_query("SET autocommit=0;");
		$portfolio_result=mysql_query("select cashbalance from portfolio where userid=$userid[$k];");
		while($row = mysql_fetch_array($portfolio_result))
		{
			$cash_val = $row['cashbalance'];
		}
		$stockprice=$quantity[$k]*$bid[$k];
		$commission=$stockprice/50;
		$d=$stockprice+$commission;
		if($cash_val<$d)
		{
			
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($email[$k]);
			$objEmail->subject('INSUFFICIENT FUNDS FOR THE FOLLOWING TRANSACTION');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbol[$k]").nl2br("\nBidPrice:$bid[$k]$").nl2br("\nQuantity:$quantity[$k]").nl2br("\nAction:$action[$k]").nl2br("\nOrder-type:$order_type[$k]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
			mysql_query("delete from orders where orderid=$orderid[$k];");
			mysql_query("commit");		
			
		}
		else
		{
			$cash_val=$cash_val-$d;
			mysql_query("update portfolio set cashbalance=$cash_val,datemodified=SYSDATE() where userid=$userid[$k];");
			mysql_query("update orders set price=$bid[$k],threshold1=null,transaction_date=SYSDATE(),status='completed',commission=$commission where orderid=$orderid[$k];"); 
			mysql_query("commit");
			//email-notification
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($email[$k]);
			$objEmail->subject('Transaction Summary');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbol[$k]").nl2br("\nBidPrice:$bid[$k]$").nl2br("\nQuantity:$quantity[$k]").nl2br("\nAction:$action[$k]").nl2br("\nOrder-type:$order_type[$k]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
		}
	}
}



//processing limit sell orders
$limit_sresult=mysql_query("select * from orders where status='pending' and action='sell' and order_type='limit'");
$sellcount = mysql_num_rows($limit_sresult);
$l=0;
while($row = mysql_fetch_array($limit_sresult))
		  {
			 $orderids[$l]=$row['orderid'];
			$tickersymbols[$l]=$row['tickersymbol'];
			$companys[$l]=$row['company'];
			$lprices[$l]=$row['threshold1'];
			$actions[$l]=$row['action'];
			$order_types[$l]=$row['order_type'];
			$quantitys[$l]=$row['quantity'];
			$transaction_dates[$l]=$row['transaction_date'];
			$userids[$l]=$row['userid'];
			$l++;
		  }
//extracting the ask prices
for($m=0;$m<$sellcount;$m++)
{
	$ask_result=mysql_query("select ask from stockdata where symbol='$tickersymbols[$m]'");
	while($row = mysql_fetch_array($ask_result))
	{
		$ask[$m]=$row['ask'];
	}
	$email_sresult=mysql_query("select Email from userdata where userid=$userids[$m]");
	while($row = mysql_fetch_array($email_sresult))
	{
		$emails[$m]=$row['Email'];
	}
}
$n=0;
for($n=0;$n<$sellcount;$n++)
{
	if($lprices[$n]<$ask[$n])
	{
		mysql_query("SET autocommit=0;");
		$portfolio_result=mysql_query("select cashbalance from portfolio where userid=$userids[$n];");
		while($row = mysql_fetch_array($portfolio_result))
		{
			$cash_val = $row['cashbalance'];
		}
		$stockprice=$quantitys[$n]*$ask[$n];
		$commission=$stockprice/50;
		$d=$stockprice-$commission;
		$ticker_result1=mysql_query("select sum(quantity) as count from orders
		where tickersymbol='$tickersymbols[$n]' and userid=$userids[$n] and status='completed' and action='buy'");
		while($row = mysql_fetch_array($ticker_result1))
		{
			$stock_bought = $row['count'];
		}
		$ticker_result2=mysql_query("select sum(quantity) as count from orders
		where tickersymbol='$tickersymbols[$n]' and userid=$userids[$n] and status='completed' and action='sell'");
		while($row = mysql_fetch_array($ticker_result2))
		{
			$stock_sold = $row['count'];
		}
		$stock_num=$stock_bought-$stock_sold;
		if($stock_num<$quantitys[$n])
		{
		
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emails[$n]);
			$objEmail->subject('INSUFFICIENT SHARES FOR THE FOLLOWING TRANSACTION');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbols[$n]").nl2br("\nAskPrice:$ask[$k]$").nl2br("\nQuantity:$quantitys[$n]").nl2br("\nAction:$actions[$n]").nl2br("\nOrder-type:$order_types[$n]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
			mysql_query("delete from orders where orderid=$orderids[$n];");
			mysql_query("commit");
			
		}
		else
		{
			$cash_val=$cash_val+$d;
			mysql_query("update portfolio set cashbalance=$cash_val,datemodified=SYSDATE() where userid=$userids[$n];");
			mysql_query("update orders set price=$ask[$n],threshold1=null,transaction_date=SYSDATE(),status='completed',commission=$commission where orderid=$orderids[$n];"); 
			mysql_query("commit");
			//email-notification
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emails[$n]);
			$objEmail->subject('Transaction Summary');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbols[$n]").nl2br("\nAskPrice:$ask[$k]$").nl2br("\nQuantity:$quantitys[$n]").nl2br("\nAction:$actions[$n]").nl2br("\nOrder-type:$order_types[$n]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
		}
	}
}



//processing buy stop orders
$stop_bresult=mysql_query("select * from orders where status='pending' and action='buy' and order_type='stop';");
$stopbuycount = mysql_num_rows($stop_bresult);
$o=0;
while($row = mysql_fetch_array($stop_bresult))
		  {
			$orderidst[$o]=$row['orderid'];
			$tickersymbolst[$o]=$row['tickersymbol'];
			$companyst[$o]=$row['company'];
			$sprice[$o]=$row['threshold2'];
			$actionst[$o]=$row['action'];
			$order_typest[$o]=$row['order_type'];
			$quantityst[$o]=$row['quantity'];
			$transaction_datest[$o]=$row['transaction_date'];
			$useridst[$o]=$row['userid'];
			$i++;
		  }
//extracting the bid prices
for($j=0;$j<$stopbuycount;$j++)
{
	$bid_result=mysql_query("select bid from stockdata where symbol='$tickersymbolst[$j]'");
	while($row = mysql_fetch_array($bid_result))
	{
		$stopbid[$j]=$row['bid'];
	}
	$email_result=mysql_query("select Email from userdata where userid=$useridst[$j]");
	while($row = mysql_fetch_array($email_result))
	{
		$emailst[$j]=$row['Email'];
	}
}
for($k=0;$k<$stopbuycount;$k++)
{
	if($sprice[$k]<$stopbid[$k])
	{
		mysql_query("SET autocommit=0;");
		$portfolio_result=mysql_query("select cashbalance from portfolio where userid=$useridst[$k];");
		while($row = mysql_fetch_array($portfolio_result))
		{
			$cash_val = $row['cashbalance'];
		}
		$stockprice=$quantityst[$k]*$stopbid[$k];
		$commission=$stockprice/50;
		$d=$stockprice+$commission;
		echo $cash_val."</br>";
		echo $d;
		echo $emailst[$k];
		if($cash_val<$d)
		{
			
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emailst[$k]);
			$objEmail->subject('INSUFFICIENT FUNDS FOR THE FOLLOWING TRANSACTION');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbolst[$k]").nl2br("\nBidPrice:$stopbid[$k]$").nl2br("\nQuantity:$quantityst[$k]").nl2br("\nAction:$actionst[$k]").nl2br("\nOrder-type:$order_typest[$k]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
			mysql_query("delete from orders where orderid=$orderidst[$k];");
			mysql_query("commit");		
			
		}
		else
		{
			$cash_val=$cash_val-$d;
			mysql_query("update portfolio set cashbalance=$cash_val,datemodified=SYSDATE() where userid=$useridst[$k];");
			mysql_query("update orders set price=$stopbid[$k],threshold2=null,transaction_date=SYSDATE(),status='completed',commission=$commission where orderid=$orderidst[$k];"); 
			mysql_query("commit");
			//email-notification
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emailst[$k]);
			$objEmail->subject('Transaction Summary');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbolst[$k]").nl2br("\nBidPrice:$stopbid[$k]$").nl2br("\nQuantity:$quantityst[$k]").nl2br("\nAction:$actionst[$k]").nl2br("\nOrder-type:$order_typest[$k]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
		}
	}
}




//processing stop sell orders
$stop_sresult=mysql_query("select * from orders where status='pending' and action='sell' and order_type='stop'");
$stopsellcount = mysql_num_rows($stop_sresult);
$p=0;
while($row = mysql_fetch_array($stop_sresult))
		  {
			 $orderidsts[$p]=$row['orderid'];
			$tickersymbolsts[$p]=$row['tickersymbol'];
			$companysts[$p]=$row['company'];
			$sprices[$p]=$row['threshold2'];
			$actionsts[$p]=$row['action'];
			$order_typests[$p]=$row['order_type'];
			$quantitysts[$p]=$row['quantity'];
			$transaction_datests[$p]=$row['transaction_date'];
			$useridsts[$p]=$row['userid'];
			$p++;
		  }
//extracting the ask prices
for($m=0;$m<$stopsellcount;$m++)
{
	$ask_result=mysql_query("select ask from stockdata where symbol='$tickersymbolsts[$m]'");
	while($row = mysql_fetch_array($ask_result))
	{
		$stopask[$m]=$row['ask'];
	}
	$email_sresult=mysql_query("select Email from userdata where userid=$useridsts[$m]");
	while($row = mysql_fetch_array($email_sresult))
	{
		$emailsts[$m]=$row['Email'];
	}
}
for($n=0;$n<$stopsellcount;$n++)
{
	if($sprices[$n]>$stopask[$n])
	{
		mysql_query("SET autocommit=0;");
		$portfolio_result=mysql_query("select cashbalance from portfolio where userid=$useridsts[$n];");
		while($row = mysql_fetch_array($portfolio_result))
		{
			$cash_val = $row['cashbalance'];
		}
		$stockprice=$quantitysts[$n]*$stopask[$n];
		$commission=$stockprice/50;
		$d=$stockprice-$commission;
		$ticker_result1=mysql_query("select sum(quantity) as count from orders
		where tickersymbol='$tickersymbolsts[$n]' and userid=$useridsts[$n] and status='completed' and action='buy'");
		while($row = mysql_fetch_array($ticker_result1))
		{
			$stock_bought = $row['count'];
		}
		$ticker_result2=mysql_query("select sum(quantity) as count from orders
		where tickersymbol='$tickersymbolsts[$n]' and userid=$useridsts[$n] and status='completed' and action='sell'");
		while($row = mysql_fetch_array($ticker_result2))
		{
			$stock_sold = $row['count'];
		}
		$stock_num=$stock_bought-$stock_sold;
		if($stock_num<$quantitysts[$n])
		{
		
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emailsts[$n]);
			$objEmail->subject('INSUFFICIENT SHARES FOR THE FOLLOWING TRANSACTION');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbolsts[$n]").nl2br("\nAskPrice:$stopask[$k]$").nl2br("\nQuantity:$quantitysts[$n]").nl2br("\nAction:$actionsts[$n]").nl2br("\nOrder-type:$order_typests[$n]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
			mysql_query("delete from orders where orderid=$orderidsts[$n];");
			mysql_query("commit");
			
		}
		else
		{
			$cash_val=$cash_val+$d;
			mysql_query("update portfolio set cashbalance=$cash_val,datemodified=SYSDATE() where userid=$useridsts[$n];");
			mysql_query("update orders set price=$stopask[$n],threshold2=null,transaction_date=SYSDATE(),status='completed',commission=$commission where orderid=$orderidsts[$n];"); 
			mysql_query("commit");
			//email-notification
			require_once("email.php");
			$objEmail	=	new CI_Email();
			$objEmail->from('tradefun2011@gmail.com', 'TradeFun');
			$objEmail->to($emailsts[$n]);
			$objEmail->subject('Transaction Summary');
			$objEmail->message('Transaction Summary:'.nl2br("\nTicker-symbol:$tickersymbolsts[$n]").nl2br("\nAskPrice:$stopask[$k]$").nl2br("\nQuantity:$quantitysts[$n]").nl2br("\nAction:$actionsts[$n]").nl2br("\nOrder-type:$order_typests[$n]").nl2br("\nCommission fee:$commission$"));
			$objEmail->send();
		}
	}
}


?>
 

<script>
	setTimeout("window.location.reload()",900000);
 </script>