protected void CreateAccount_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
string sql = "";
// 1. generate a unique account number
string lastName = LastNamePrimary.Text.Trim().ToUpper();
if (lastName.Length == 1)
{
lastName = lastName + lastName;
}
else
{
lastName = string.Concat(lastName[0], lastName[1]);
}
sql = string.Format("SELECT COUNT(*) FROM dbo.Account WHERE accountNumber like '{0}%'", lastName);
decimal newNumber = myHKeInvestData.getAggregateValue(sql) + 1;
string newAccountNumber = lastName + newNumber.ToString("00000000");
string accountType = AccountType.SelectedValue;
var myTrans = myHKeInvestData.beginTransaction();
sql = InsertAccount(newAccountNumber, accountType);
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
// 2. insert client information into client table
// 2.1 insert primary account holder's information
InsertPrimaryAccountHolder(newAccountNumber, true);
// 2.2 insert co-account holder's information (if any)
if (accountType != "individual")
{
InsertCoAccountHolder(newAccountNumber, false);
}
Response.Redirect("../Default.aspx");
}
private DataTable GetIndividualSecurity(string securityCode)
{
// CHANGE THIS TO THE DYNAMIC ACTUAL NUMBERRRRRR
string accountNumber = getAccountNumber(); // CHANGE THIS TO THE DYNAMIC ACTUAL NUMBERRRRRR
string secTypeSql = "select securityType from OrderHistory where status='completed' and securityCode=" + securityCode + " and accountNumber='" + accountNumber + "'";
DataTable dtSecType = myHKeInvestData.getData(secTypeSql);
string securityType = dtSecType.Rows[0]["securityType"].ToString();
string sqlBase = "from OrderHistory inner join Transactions on OrderHistory.referenceNumber = Transactions.referenceNumber where status='completed' and securityCode=" + securityCode + " and OrderHistory.accountNumber='" + accountNumber + "'";
string buy = " and buyOrSell='Buy'";
string sell = " and buyOrSell='Sell'";
string sql = "SELECT distinct securityType, name, securityCode, " +
"cast('0.00' AS numeric(12,2)) AS shares, " +
"cast('0.00' AS numeric(12,2)) AS amount, " +
"cast('0.00' AS numeric(12,2)) AS dollarBuying, " +
"cast('0.00' AS numeric(12,2)) AS dollarSelling, " +
"cast('0.00' AS numeric(12,2)) AS totalFees, " +
"cast('0.00' AS numeric(12,2)) AS profitLoss " +
"from OrderHistory where status='completed' and securityCode=" + securityCode + " and accountNumber='" + accountNumber + "'"; // Complete the SQL statement.
DataTable dtClient = myHKeInvestData.getData(sql);
if (dtClient == null)
{
return(null);
} // If the DataSet is null, a SQL error occurred.
//myHKeInvestData.getAggregateValue(“select count(*) from [Person]”);
// If no result is returned by the SQL statement, then display a message.
if (dtClient.Rows.Count == 0)
{
//lblResultMessage.Text = Context.User.Identity.GetUserName();
SingleSecurity.Visible = false;
InvalidCode.Text = "You don't have any transactions with this security";
return(null);
}
var sharesBuy = myHKeInvestData.getAggregateValue(generateSumSQL("executeShares", accountNumber, securityCode) + " and buyOrSell='Buy'");
var sharesSell = myHKeInvestData.getAggregateValue(generateSumSQL("executeShares", accountNumber, securityCode) + " and buyOrSell='Sell'");
var sharesRemaining = sharesBuy - sharesSell;
var dollarBuying = getDollar(accountNumber, sqlBase, buy);
var dollarSelling = getDollar(accountNumber, sqlBase, sell);
var totalFees = myHKeInvestData.getAggregateValue(generateSumSQL("feesPaid", accountNumber, securityCode));
var sharesCurrPrice = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
var sharesRemainingCurrWorth = sharesRemaining * sharesCurrPrice;
var profitLoss = dollarSelling + sharesRemainingCurrWorth - dollarBuying - totalFees;
if (ValueListType.SelectedValue == "Percent")
{
profitLoss = (profitLoss / dollarBuying) * 100;
}
dtClient.Rows[0]["shares"] = sharesRemaining;
dtClient.Rows[0]["dollarBuying"] = dollarBuying;
dtClient.Rows[0]["dollarSelling"] = dollarSelling;
dtClient.Rows[0]["totalFees"] = totalFees;
dtClient.Rows[0]["profitLoss"] = profitLoss;
return(dtClient);
}
//The proceed button
protected void totalcheck(object sender, EventArgs s)
{
if (Page.IsValid)
{
//GET BASIC DETAILS
accessDataBase myData = new accessDataBase();
//string username = Context.User.Identity.GetUserName();
string username = Context.User.Identity.GetUserName();
string actnum = myData.getOneData("accountNumber", "Account", username);
string email = myData.getOneData("email", "Client", actnum);
string balance = myData.getOneData("balance", "Account", username);
DateTime thisDay = DateTime.Today;
string date = thisDay.ToString("d");
//Buy order
if (string.Compare(opdd.SelectedValue, "buy", true) == 0)
{
//Buy Stock
if (string.Compare(Stype.SelectedValue, "stock", true) == 0)
{
//Get data from text boxes
//anInteger = Convert.ToInt32(textBox1.Text);
string stockcode = Scode.Text.Trim();
string numofshares = qofshares.Text.Trim();
int numshares = Convert.ToInt32(qofshares.Text.Trim());
decimal curprice = myExternalFunctions.getSecuritiesPrice("stock", stockcode);
string expday = expdate.SelectedValue;
string highp = highPrice.Text.Trim();
string stopp = stopPrice.Text.Trim();
string ordertype = stockorderdd.SelectedValue;
string allornone = allornonecheck.SelectedValue;
decimal cost = numshares * curprice;
string c = Convert.ToString(cost);
string p = Convert.ToString(curprice);
string sqll = "";
string sql2 = "";
if (cost > (myHKeInvestData.getAggregateValue("select balance FROM Account WHERE userName = '******'")))
{
error.Text = "Account balance smaller then total amount to buy. Not enough balance. '" + username + "'";
error.Visible = true;
return;
}
string result = myExternalFunctions.submitStockBuyOrder(stockcode, numofshares, ordertype, expday, allornone, highp, stopp);
//update balance, update transactionrecord
sqll = "update [Account] set [balance] = [balance] - '" + cost + "' WHERE [userName] = '" + username + "'";
sql2 = "update [TransactionRecord] set ";
//CHECK STATUS
string status = myExternalFunctions.getOrderStatus(result);
//if (String.Compare(status, "completed", true)==0)
//{
// //BUY STOCK && UPDATE TABLE
// SqlTransaction trans = myHKeInvestData.beginTransaction();
// myHKeInvestData.setData(sqll, trans);
// myHKeInvestData.setData(sql2, trans);
// myHKeInvestData.commitTransaction(trans);
//}
return;
}
//Buy bond
if (string.Compare(Stype.SelectedValue, "bond", true) == 0)
{
//Bond code and amount
decimal amt = Convert.ToDecimal(amtofbond.Text.Trim());
string code = Scode.Text.Trim();
string sname = "";
DataTable checkname = myExternalFunctions.getSecuritiesByCode("bond", code);
foreach (DataRow row in checkname.Rows)
{
sname = row["name"].ToString();
}
decimal curprice = myExternalFunctions.getSecuritiesPrice("bond", code);
decimal cost = amt * curprice;
string strcost = cost.ToString();
//string username = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
string user = Context.User.Identity.GetUserName();
string sqll;
string updatetranssql = "";
if (amt > (myHKeInvestData.getAggregateValue("select balance FROM Account WHERE userName = '******'")))
{
error.Text = "Account balance smaller then total amount to buy. Not enough balance.";
error.Visible = true;
return;
}
string result = myExternalFunctions.submitBondBuyOrder(Scode.Text.Trim(), amtofbond.Text.Trim());
//Check Record
string status = myExternalFunctions.getOrderStatus(result);
if (result != null)
{
//save record in transactionRecord
SqlTransaction saverecord = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO TransactionRecord (accountNumber, transactionNumber, referenceNumber, userName, emailsent, buyOrSell, securityType, securityCode, name, shares, amount, base, dateSubmitted) VALUES ('" + actnum + "',' " + result + "', '" + status + "', '" + username + "', 0 , 'buy', 'bond', '" + code + "', '" + sname + "', '" + amtofbond.Text.Trim() + "', '" + strcost + "', 'HKD', '" + date + "')'", saverecord);
myHKeInvestData.commitTransaction(saverecord);
error.Text = "Order successfully submitted. Order reference no:" + result + "Confirmation email will be sent to you when order is completed";
error.Visible = true;
return;
}
else
{
error.Text = "Errors occured. Order not submitted";
error.Visible = true;
return;
}
return;
}
////Check if order completed
//if(String.Compare(status, "completed", true) == 0)
//{
// //Get Order Transactions
// DataTable Transactions = myExternalFunctions.getOrderTransaction(result);
// //Save record
// SqlTransaction updatedate = myHKeInvestData.beginTransaction();
// myHKeInvestData.setData("UPDATE TransactionRecord SET dateSubmitted='" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE accountNumber='" + actnum + "' AND securityCode='" + code + "' AND securityType = '" + "bond" + "' AND buyOrSell = buy AND status = completed AND executeDate ='" + DateTime.Now.ToString("yyyy-MM-dd") + "' AND executeShares = '" + amt + "'AND executePrice ='", updatedate);
// myHKeInvestData.commitTransaction(updatedate);
// //Generate invoice
//}
//Save order in record
//if (result != null)
//{
// //Order
//}
//sqll = "update [Account] set [balance] = [balance] - '" + amt + "' WHERE [userName] = '" + user + "'";
//updatetranssql = "update [TransactionRecord] set ";
//if(result!= null)
//{
// //KEEP TRANS RECORD IN TABLE
// SqlTransaction trans = myHKeInvestData.beginTransaction();
// myHKeInvestData.setData(sqll, trans);
// myHKeInvestData.setData(updatetranssql, trans);
// myHKeInvestData.commitTransaction(trans);
//}
//return;
//Save in own record
//Buy unitTrust
if (string.Compare(Stype.SelectedValue, "unitTrust", true) == 0)
{
//unit trust's code and amount
decimal amt = Convert.ToDecimal(amtofut.Text.Trim());
string code = Scode.Text.Trim();
string sname = "";
DataTable checkname = myExternalFunctions.getSecuritiesByCode("unit", code);
foreach (DataRow row in checkname.Rows)
{
sname = row["name"].ToString();
}
decimal curprice = myExternalFunctions.getSecuritiesPrice("unit trust", code);
decimal cost = amt * curprice;
string strcost = cost.ToString();
//string username = Context.User.Identity.GetUserName();
decimal bal = myHKeInvestData.getAggregateValue("select [balance] FROM [Account] WHERE [userName] = '" + username + "'");
if (amt > (myHKeInvestData.getAggregateValue("select [balance] FROM [Account] WHERE [userName] = '" + username + "'")))
{
error.Text = username + bal + "Account balance smaller then total amount to buy. Not enough balance.";
error.Visible = true;
return;
}
else
{
error.Text = username + bal + "Proceed";
error.Visible = true;
}
string result = myExternalFunctions.submitUnitTrustBuyOrder(Scode.Text.Trim(), amtofut.Text.Trim());
string status = myExternalFunctions.getOrderStatus(result);
if (result != null)
{
//save record in transactionRecord
SqlTransaction saverecord = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO TransactionRecord (accountNumber, transactionNumber, referenceNumber, userName, emailsent, buyOrSell, securityType, securityCode, name, shares, amount, base, dateSubmitted) VALUES ('" + actnum + "',' " + result + "', '" + status + "', '" + username + "', 0 , 'buy', 'unit trust', '" + code + "', '" + sname + "', '" + amtofut.Text.Trim() + "', '" + strcost + "', 'HKD', '" + date + "')'", saverecord);
myHKeInvestData.commitTransaction(saverecord);
error.Text = "Order successfully submitted. Order reference no:" + result + "Confirmation email will be sent to you when order is completed";
error.Visible = true;
return;
}
else
{
error.Text = "Errors occured. Order not submitted";
error.Visible = true;
return;
}
return;
//ham chut lai
}
}
//Sell order
if (String.Compare(opdd.SelectedValue, "sell", true) == 0)
{
if (string.Compare(Stype.SelectedValue, "bond", true) == 0)
{
//Check security owns
string bondcode = Scode.Text.Trim();
string sname = "";
string sbase = "";
DataTable checkname = myExternalFunctions.getSecuritiesByCode("bond", bondcode);
foreach (DataRow row in checkname.Rows)
{
sname = row["name"].ToString();
sbase = row["base"].ToString();
}
string searchshares = "SELECT [shares] FROM [SecurityHolding] WHERE [accountNumber] = '" + actnum + "' AND [type] = '" + Stype.SelectedValue + "' AND [code] = '" + bondcode + "'";
decimal secamount = myHKeInvestData.getAggregateValue(searchshares);
string strsecamt = secamount.ToString();
//decimal secamount = Convert.ToDecimal(searchshares);
decimal sellamount = Convert.ToDecimal(numofshares.Text.Trim());
string amt = numofshares.Text.Trim();
//CHECK IF HAVE SECURITY && AMOUNT
if (secamount == 0)
{
error.Text = "Selected security not in account or amount equal to zero. Order will not be proceeded.";
error.Visible = true;
return;
}
//CHECK SECURITY AMONT
if (secamount < sellamount)
{
error.Text = username + "Securities in Account less than securities to sell. Order will not be proceeded.";
error.Visible = true;
return;
}
string result = myExternalFunctions.submitBondSellOrder(Scode.Text.Trim(), numofshares.Text.Trim());
string status = myExternalFunctions.getOrderStatus(result);
if (result != null)
{
//save record in transactionRecord
SqlTransaction saverecord = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO TransactionRecord (accountNumber, transactionNumber, referenceNumber, userName, emailsent, buyOrSell, securityType, securityCode, name, shares, amount, base, dateSubmitted) VALUES ('" + actnum + "',' " + result + "', '" + status + "', '" + username + "', 0 , 'sell', 'bond', '" + bondcode + "', '" + sname + "', '" + strsecamt + "', '" + amt + "', '" + sbase + "', '" + date + "')'", saverecord);
myHKeInvestData.commitTransaction(saverecord);
error.Text = "Order successfully submitted. Order reference no:" + result + "Confirmation email will be sent to you when order is completed";
error.Visible = true;
return;
}
else
{
error.Text = "Errors occured. Order not submitted";
error.Visible = true;
return;
}
}
if (string.Compare(Stype.SelectedValue, "stock", true) == 0)
{
//stock's code, shares, orderType, expiryday, allornone, lowprice, stopPrice
string code = Scode.Text.Trim();
string shares = numofsellshares.Text.Trim();
string orderType = stockorderdd.SelectedValue;
string expday = expdate.SelectedValue;
string allornone = sellallornonecheck.SelectedValue;
string lowprice = lowPrice.Text.Trim();
string stopPrice = sellstopPrice.Text.Trim();
string searchshares = "SELECT shares FROM SecurityHolding WHERE accountNumber = '" + actnum + "' AND type = '" + "stock" + "' AND code = '" + code + "'";
decimal secamount = myHKeInvestData.getAggregateValue(searchshares);
decimal sellamount = Convert.ToDecimal(sellstockamt.Text.Trim());
//CHECK IF SECURITY EXIST
if (secamount == 0)
{
error.Text = "Selected security not in account or amount equal to zero. Order will not be proceeded.";
error.Visible = true;
return;
}
//CHECK SECURITY AMONT
if (secamount < sellamount)
{
error.Text = "Securities in Account less than securities to sell. Order will not be proceeded.";
error.Visible = true;
return;
}
string result = myExternalFunctions.submitStockSellOrder(code, shares, orderType, expday, allornone, lowprice, stopPrice);
}
if (string.Compare(Stype.SelectedValue, "unitTrust", true) == 0)
{
//unit trust's code, shares
string utcode = Scode.Text.Trim();
string sname = "";
string sbase = "";
DataTable checkname = myExternalFunctions.getSecuritiesByCode("bond", utcode);
foreach (DataRow row in checkname.Rows)
{
sname = row["name"].ToString();
sbase = row["base"].ToString();
}
string searchshares = "SELECT shares FROM SecurityHolding WHERE accountNumber = '" + actnum + "' AND type = '" + "unit trust" + "' AND code = '" + utcode + "'";
decimal secamount = myHKeInvestData.getAggregateValue(searchshares);
string stramt = secamount.ToString();
decimal sellamount = Convert.ToDecimal(numofutshares.Text.Trim());
string numofshares = numofutshares.Text.Trim();
//CHECK IF SECURITY EXIST
if (secamount == 0)
{
error.Text = "Selected security not in account or amount equal to zero. Order will not be proceeded.";
error.Visible = true;
return;
}
//CHECK SECURITY AMONT
if (secamount < sellamount)
{
//gfhgfhgfhg
error.Text = "Securities in Account less than securities to sell. Order will not be proceeded.";
error.Visible = true;
return;
}
//sdfsdf
string result = myExternalFunctions.submitUnitTrustSellOrder(Scode.Text.Trim(), numofutshares.Text.Trim());
string status = myExternalFunctions.getOrderStatus(result);
if (result != null)
{
//save record in transactionRecord
SqlTransaction saverecord = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO TransactionRecord (accountNumber, transactionNumber, referenceNumber, userName, emailsent, buyOrSell, securityType, securityCode, name, shares, amount, base, dateSubmitted) VALUES ('" + actnum + "',' " + result + "', '" + status + "', '" + username + "', 0 , 'sell', 'unit trust', '" + utcode + "', '" + sname + "', '" + numofshares + "', '" + stramt + "', '" + sbase + "', '" + date + "')'", saverecord);
myHKeInvestData.commitTransaction(saverecord);
error.Text = "Order successfully submitted. Order reference no:" + result + "Confirmation email will be sent to you when order is completed";
error.Visible = true;
return;
}
else
{
error.Text = "Errors occured. Order not submitted";
error.Visible = true;
return;
}
}
}
}
}
private void PeriodicTask()
{
do
{
/*
*
* FOR BUY & SELL PERIODIC TASK
*
*/
string status = "";
string refnum = "";
decimal fee = 0;
decimal cost = fee;
//get datatable where email has not yet sent out //another approach: condition: where status != completed --> still needa checkout
DataTable statustable = myHKeInvestData.getData("SELECT referenceNumber FROM TransactionRecord WHERE emailsent = 0");
foreach (DataRow rows in statustable.Rows)
{
//get referencenumber
refnum = rows["referenceNumber"].ToString();
//for each emailsent=0;
status = myExternalFunctions.getOrderStatus(refnum);
//if status is completed
if (String.Compare(status, "completed", true) == 0)
{
decimal accountNumber = myHKeInvestData.getAggregateValue("SELECT accountNumber FROM TransactionRecord WHERE referenceNumber = refnum");
string actnum = accountNumber.ToString();
string buyorsell = myData.getOneDatabyNO("buyOrSell", "TransactionRecord", actnum);
string date = DateTime.Now.ToString("yyyy-MM-dd");
decimal shares = myHKeInvestData.getAggregateValue("SELECT shares FROM TransactionRecord WHERE referenceNumber = refnum");
string strshares = shares.ToString();
string code = myData.getOneDatabyNO("securityCode", "TransactionRecord", actnum);
string username = myData.getOneDatabyNO("userName", "TransactionRecord", actnum);
decimal pricepshare = myHKeInvestData.getAggregateValue("SELECT executePrice FROM TransactionRecord WHERE referenceNumber = refnum");
string type = myData.getOneDatabyNO("securityType", "TransactionRecord", actnum);
string sname = myData.getOneDatabyNO("name", "TransactionRecord", actnum);
string sbase = myData.getOneDatabyNO("base", "TransactionRecord", actnum);
cost = shares * pricepshare;
string strcost = cost.ToString();
//get order transaction
DataTable ordertrans = myExternalFunctions.getOrderTransaction(refnum);
string exedate = "";
string exeshares = "";
string exeprice = "";
string transnum = "";
//string executeDate = "";
//string executeShares = "";
// DataTable temp = myHKeInvestData.getData("SELECT executeDate FROM ordertrans WHERE transactionNumber = '" + refnum + "'");
foreach (DataRow row in ordertrans.Rows)
{
transnum = row["transactionNumber"].ToString();
exedate = row["executeDate"].ToString();
exeshares = row["executeShares"].ToString();
exeprice = row["executePrice"].ToString();
}
//calcaulta transaction fee
fee = 0;
//modify account balance
SqlTransaction trans = myHKeInvestData.beginTransaction();
//set acct balance
myHKeInvestData.setData("UPDATE Account SET balance = (balance - '" + cost + "'-'" + fee + "' WHERE accountNumber = '" + accountNumber + "'", trans);
//set email sent =1
myHKeInvestData.setData("UPDATE TransactionRecord SET emailsent = 1 WHERE accountNumber ='" + accountNumber + "'", trans);
//update TransactionRecord to match the order status the rest of records
myHKeInvestData.setData("UPDATE TransactionRecord SET status = completed", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET transactionNumber ='" + transnum + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executeDate ='" + exedate + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executePrice ='" + exeprice + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executeShares ='" + exeshares + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.commitTransaction(trans);
//update security holding
SqlTransaction addsecurity = myHKeInvestData.beginTransaction();
//check if same bond n code exist
DataTable check = myHKeInvestData.getData("SELECT accountNumber, type, code FROM SecurityHolding WHERE accountNumber = '" + actnum + "'");
foreach (DataRow row in check.Rows)
{
string checktype = row["type"].ToString();
if (String.Compare(checktype, type, true) == 0)
{
if (String.Compare(row["code"].ToString(), code, true) == 0)
{
if (String.Compare(buyorsell, "buy", true) == 0)
{
myHKeInvestData.setData("UPDATE SecurityHolding SET shares = shares + '" + shares + "' WHERE accountNumber = '" + actnum + "' AND type = '" + type + "' AND code = '" + code + "'", addsecurity);
}
else if (String.Compare(buyorsell, "sell", true) == 0)
{
myHKeInvestData.setData("UPDATE SecurityHolding SET shares = shares - '" + shares + "' WHERE accountNumber = '" + actnum + "' AND type = '" + type + "' AND code = '" + code + "'", addsecurity);
}
}
else
{
myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
}
}
else
{
myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
}
}
//myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
myHKeInvestData.commitTransaction(addsecurity);
//myHKeInvestData.setData("UPDATE Account SET balance = (balance - cost) + value +"' WHERE accountNumber = '" + AccountNumber + "'", trans);
//gen invoice
// protected string generateInvoiceMsg(string user, string actnum, string orderrefnum, string buyorsell, string code,
//string sname, string stocktype, string date, string amt, string cost,
//string transnum, string dateExe, string numexe, string price)
string msg = generateInvoiceMsg(username, actnum, refnum, buyorsell, code, sname, type, date, strshares, strcost, refnum, exedate, exeshares, exeprice);
//send email
sendemail(username, msg);
//Update email flag
SqlTransaction emailflag = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE TransactionRecord SET emailsent = 1 WHERE accountNumber ='" + accountNumber + "'", emailflag);
myHKeInvestData.commitTransaction(emailflag);
}
//check if email sent
}
/*
*
* END OF PERIODIC TASK OF BUY & SELL
*
*/
// Place the method call for the periodic task here.
//if price in external table reach the value set in alert table, send email
//add a attribute "lastsent" to indicate if today had sent
//alert high, low save in table
//foreach compare wilth external
//HKeInvestData myHKeInvestData = new HKeInvestData();
//ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable alerts = myHKeInvestData.getData("SELECT * FROM Alert");
foreach (DataRow row in alerts.Rows)
{
string id = "" + row["accountNumber"];
string type = "" + row["type"].ToString().Trim();
string code = "" + row["code"].ToString().Trim();
//string high = "" + row["high"];
decimal high = System.Convert.ToDecimal(row["highValue"]);
decimal low = System.Convert.ToDecimal(row["lowValue"]);
decimal current = myExternalFunctions.getSecuritiesPrice(type, code);
string date = "";
DataTable searchdate = myHKeInvestData.getData("SELECT lastsent FROM Alert WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'");
foreach (DataRow rows in searchdate.Rows)
{
date = date + rows["lastsent"];
}
if (date == DateTime.Now.ToString("yyyy-MM-dd"))
{
}
else
{
string email = "";
DataTable searchemail = myHKeInvestData.getData("SELECT email FROM Client WHERE accountNumber='" + id + "'");
foreach (DataRow rows in searchemail.Rows)
{
email = email + rows["email"];
}
string name = "";
DataTable security = myExternalFunctions.getSecuritiesByCode(type, code);
foreach (DataRow rows in security.Rows)
{
name = name + rows["name"];
}
if (high <= myExternalFunctions.getSecuritiesPrice(type, code))
{
SqlTransaction updatedate = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE alert SET lastsent='" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'", updatedate);
myHKeInvestData.commitTransaction(updatedate);
System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(email);
mail.From = new MailAddress("*****@*****.**", "HKeInvest", System.Text.Encoding.UTF8);
mail.Subject = "Alert Triggered!";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = "The high value alert for your " + type + " security, code: " + code + " name: " + name + " had been triggered. The current price of the security is " + current + ". The high alert value you set is " + high + ".";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true;
mail.Priority = MailPriority.High;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("comp3111_team120", "team120#");
client.Port = 587;
client.Host = "smtp.cse.ust.hk";
client.EnableSsl = true;
try
{
client.Send(mail);
//Page.RegisterStartupScript("UserMsg", "<script>alert('Successfully Send...');if(alert){ window.location='SendMail.aspx';}</script>");
}
catch (Exception ex)
{
Exception ex2 = ex;
string errorMessage = string.Empty;
while (ex2 != null)
{
errorMessage += ex2.ToString();
ex2 = ex2.InnerException;
}
//Page.RegisterStartupScript("UserMsg", "<script>alert('Sending Failed...');if(alert){ window.location='SendMail.aspx';}</script>");
}
}
else if (low >= myExternalFunctions.getSecuritiesPrice(type, code))
{
SqlTransaction updatedate = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE alert SET lastsent='" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'", updatedate);
myHKeInvestData.commitTransaction(updatedate);
System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(email);
mail.From = new MailAddress("*****@*****.**", "HKeInvest", System.Text.Encoding.UTF8);
mail.Subject = "Alert Triggered!";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = "The low value alert for your " + type + " security, code: " + code + " name: " + name + " had been triggered. The current price of the security is " + current + ". The low alert value you set is " + low + ".";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true;
mail.Priority = MailPriority.High;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("comp3111_team120", "team120#");
client.Port = 587;
client.Host = "smtp.cse.ust.hk";
client.EnableSsl = true;
try
{
client.Send(mail);
//Page.RegisterStartupScript("UserMsg", "<script>alert('Successfully Send...');if(alert){ window.location='SendMail.aspx';}</script>");
}
catch (Exception ex)
{
Exception ex2 = ex;
string errorMessage = string.Empty;
while (ex2 != null)
{
errorMessage += ex2.ToString();
ex2 = ex2.InnerException;
}
//Page.RegisterStartupScript("UserMsg", "<script>alert('Sending Failed...');if(alert){ window.location='SendMail.aspx';}</script>");
}
}
}
}
Thread.Sleep(10000);
} while (true);
}
