protected void Stype_SelectedIndexChanged(object sender, EventArgs e)
{
if (Stype.SelectedValue != "")
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//get user id and security data
string loginuser = Context.User.Identity.GetUserName();
DataTable idsearch = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'");
string loginuserid = "";
foreach (DataRow row in idsearch.Rows)
{
loginuserid = loginuserid + row["accountNumber"];
}
//get data to be input in dropdown list
Snamecode.Items.Clear();
Snamecode.Items.Add(new ListItem("Name (Code)", ""));
DataTable heldsecurity = myHKeInvestData.getData("SELECT code, name FROM SecurityHolding WHERE SecurityHolding.accountNumber = '" + loginuserid + "' AND SecurityHolding.type = '" + Stype.SelectedValue + "'");
foreach (DataRow row in heldsecurity.Rows)
{
//Snamecode.Items.Add(New ListItem(row["name"].ToString().Trim() + " (code: " + row["code"].ToString().Trim() + ")", row["code"].ToString().Trim()));
//Snamecode.Items.Add(row["name"].ToString().Trim()+" (code: "+row["code"].ToString().Trim()+")");
Snamecode.Items.Add(new ListItem(row["name"].ToString().Trim() + " (code: " + row["code"].ToString().Trim() + ")", row["code"].ToString().Trim()));
}
}
}
protected void Snamecode_SelectedIndexChanged(object sender, EventArgs e)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string loginuser = Context.User.Identity.GetUserName();
DataTable idsearch = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'");
string loginuserid = "";
foreach (DataRow row in idsearch.Rows)
{
loginuserid = loginuserid + row["accountNumber"];
}
string choosencode = Snamecode.SelectedValue.Trim();
string choosentype = Stype.SelectedValue.Trim();
string curhighv = "";
string curlowv = "";
DataTable curalert = myHKeInvestData.getData("SELECT * FROM Alert WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'");
if (curalert.Rows.Count == 0)
{
}
else
{
foreach (DataRow row in curalert.Rows)
{
curhighv = curhighv + row["highValue"];
curlowv = curlowv + row["lowValue"];
}
curhigh.Text = curhighv;
curlow.Text = curlowv;
}
}
private decimal Get_Assets(string accountNumber, decimal balance)
{
HKeInvestCode myHKeInvestCode = new HKeInvestCode();
HKeInvestData myHKeInvestData = new HKeInvestData();
ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable dtCurrency = myExternalFunctions.getCurrencyData();
DataTable dt = myHKeInvestData.getData("SELECT type, code, shares, base FROM [SecurityHolding] WHERE accountNumber='" + accountNumber + "'");
decimal ret = balance;
foreach (DataRow row in dt.Rows)
{
string securityCode = row["code"].ToString();
string securityType = row["type"].ToString();
string securityBase = row["base"].ToString();
decimal shares = Convert.ToDecimal(row["shares"]);
decimal price = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
decimal value = Math.Round(shares * price - (decimal).005, 2);
DataRow[] baseRateRow = dtCurrency.Select("currency = '" + securityBase + "'");
DataRow[] toRateRow = dtCurrency.Select("currency = 'HKD'");
if (baseRateRow.Length == 1 && toRateRow.Length == 1)
{
value = myHKeInvestCode.convertCurrency(securityBase, baseRateRow[0]["rate"].ToString(), "HKD", toRateRow[0]["rate"].ToString(), value);
}
ret += value;
}
return(ret);
}
private void UpdateAccountUserName(string accountNumber, string userName)
{
HKeInvestData myInvestData = new HKeInvestData();
string sql = "update [AccountTemp] set [userName]='" + userName + "' where [accountNumber]='" + accountNumber + "'";
SqlTransaction trans = myInvestData.beginTransaction();
myInvestData.setData(sql, trans);
myInvestData.commitTransaction(trans);
}
private void Update_OrderStatus(string referenceNumber, string status, decimal serviceFee)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string sql = string.Format("UPDATE [Order] SET orderStatus='{0}', serviceFee={1} WHERE orderReferenceNumber='{2}'", status, serviceFee, referenceNumber);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
private string Get_UpdateSql(DataTable dtOrderDetails, string type, string accountNumber, decimal totalShares, string securityBase, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string code = dtOrderDetails.Rows[0].Field <string>("securityCode");
// find if the security is in the account
string sql = string.Format("SELECT shares FROM [SecurityHolding] WHERE type='{0}' and code='{1}' and accountNumber='{2}'", type, code, accountNumber);
DataTable dt = myHKeInvestData.getData(sql);
if (dt.Rows.Count == 0)
{
if (buyOrSell == "sell")
{
return(null);
}
// new security, return insert statement
return(string.Format("INSERT INTO [SecurityHolding] VALUES ('{0}', '{1}', '{2}', '{3}', {4}, '{5}')",
accountNumber,
type,
code,
dtOrderDetails.Rows[0].Field <string>("securityName"),
totalShares,
securityBase));
}
else
{
decimal newShares;
// already hold this security generate update
if (buyOrSell == "buy")
{
newShares = totalShares + dt.Rows[0].Field <decimal>("shares");
return(string.Format("UPDATE [SecurityHolding] SET shares={0} WHERE accountNumber='{1}' and type='{2}' and code='{3}'", newShares, accountNumber, type, code));
}
else if (buyOrSell == "sell")
{
newShares = dt.Rows[0].Field <decimal>("shares") - totalShares;
if (newShares == 0)
{
// sold out delete the record;
return(string.Format("DELETE FROM [SecurityHolding] WHERE accountNumber='{0}' and type='{1}' and code='{2}'", accountNumber, type, code));
}
else if (newShares > 0)
{
return(string.Format("UPDATE [SecurityHolding] SET shares={0} WHERE accountNumber='{1}' and type='{2}' and code='{3}'", newShares, accountNumber, type, code));
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
private void AddUserName(string userName, string accountNumber)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
SqlTransaction trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("update[Account] set[userName] = '" +
userName + "' " + "where [accountNumber]= '" + accountNumber + "'", trans);
myHKeInvestData.commitTransaction(trans);
}
private DataTable getOrderDetails(string referenceNumber, out string type, out string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [UnitTrustOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "unit trust";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [UnitTrustOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "unit trust";
buyOrSell = "sell";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [StockOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "stock";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [StockOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "stock";
buyOrSell = "sell";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [BondOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "bond";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [BondOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "bond";
buyOrSell = "sell";
return(dt);
}
type = "";
buyOrSell = "";
return(null);
}
private void Update_SecurityHolding(DataTable dtOrderDetails, string type, string accountNumber, decimal totalShares, string securityBase, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string sql = Get_UpdateSql(dtOrderDetails, type, accountNumber, totalShares, securityBase, buyOrSell);
if (sql == null)
{
return;
}
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
protected void cvUserName_ServerValidate(object source, ServerValidateEventArgs args)
{
string username = UserName.Text.Trim();
string sql2 = "SELECT userName FROM Account WHERE userName = '******'";
HKeInvestData myHKeInvestData2 = new HKeInvestData();
DataTable dtUser = myHKeInvestData2.getData(sql2);
foreach (DataRow row in dtUser.Rows)
{
if (row != null)
{
args.IsValid = false;
cvUserName.ErrorMessage = "The user name has been used.";
}
}
}
private bool CheckClientRecord(string firstName, string lastName, string dateOfBirth, string email, string HKID, string accountNumber)
{
string isPrimary = "yes";
string sql = "select [firstName], [lastName], [dateOfBirth], [email], [HKIDPassportNumber] from [ClientTemp] where [accountNumber]='" + accountNumber + "' and [firstName]='" + firstName + "' and [lastName]='" + lastName + "' and [dateOfBirth]=CONVERT(date, '" + DateOfBirth.Text + "', 103) and [email]='" + email + "' and [HKIDPassportNumber]='" + HKID + "' and [isPrimary]='" + isPrimary + "'";
HKeInvestData myInvestData = new HKeInvestData();
DataTable dtClient = myInvestData.getData(sql);
if (dtClient == null || dtClient.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
private void Send_Notification(string accountNumber, string type, string code, string highOrLow, decimal currPrice)
{
// fetch the primary account holder email
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtClient = myHKeInvestData.getData(string.Format("SELECT lastName, email FROM [Client] WHERE accountNumber='{0}' AND isPrimary=(1)", accountNumber));
if (dtClient.Rows.Count != 1)
{
return;
}
string destination = dtClient.Rows[0].Field <string>("email");
string lastName = dtClient.Rows[0].Field <string>("lastName");
// fetch the name of the security
DataTable dtSecurity = myHKeInvestData.getData(string.Format("SELECT name FROM [SecurityHolding] WHERE accountNumber='{0}' AND type='{1}' AND code='{2}'", accountNumber, type, code));
if (dtSecurity.Rows.Count != 1)
{
return;
}
string securityName = dtSecurity.Rows[0].Field <string>("name");
#region construct notification message
string text = string.Format(@"Hi, {0}<br/>One of your alert(s) has been triggered:<br/>{1} {2} {3}, {4} price reached<br/>the price that triggered the alert: {5}<br/>",
lastName, type, code, securityName, highOrLow, currPrice);
string html = text;
#endregion
#region emailAccount
string username = "******";
#endregion
// Create an instance of MailMessage named mail.
MailMessage mail = new MailMessage();
// Set the sender (From), receiver (To), subject and message body fields of the mail message.
mail.From = new MailAddress(username, "Team104 Newbee");
mail.To.Add(destination);
mail.Subject = "[HKeInvest] Alert Notification";
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(text, null, MediaTypeNames.Text.Plain));
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(html, null, MediaTypeNames.Text.Html));
// Create an instance of SmtpClient named emailServer and set the mail server to use as "smtp.cse.ust.hk".
SmtpClient emailServer = new SmtpClient("smtp.cse.ust.hk");
// Send the message.
emailServer.Send(mail);
return;
}
protected void Register_Click(object sender, EventArgs e)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
SqlTransaction trans = myHKeInvestData.beginTransaction();
DateTime MyDateTime = new DateTime();
MyDateTime = DateTime.ParseExact(DateOfBirth.Text.Trim(), "MM/dd/yyyy", CultureInfo.InvariantCulture);
String DOB = MyDateTime.ToShortDateString();
myHKeInvestData.setData("insert into [Account] ([accountNumber], [accountType],[balance]) values ('" + AccountNumber.Text.Trim() + "', '" + ddlAccountType.SelectedValue.ToString().Trim() + "', '" + balance.Text.Trim() + "' )", trans);
myHKeInvestData.setData("insert into [Client] ([firstName], [lastName],[dateofBirth],[email],[HKIDPassportNumber],[accountNumber],[building],[street],[district],[homePhone],[homeFax],[businessPhone],[mobilePhone],[countryOfCitizenship],[countryOfLegalResidence],[passportCountryOfIssue],[employmentStatus],[occupation],[yearsWithEmployer],[employerName],[employerPhone],[natureOfBusiness],[employedByFinanceInst],[memberPublicTradedInst],[primarySourceOfFunds],[investmentObjective],[investmentKnowledge],[annualIncome],[approxLiquidNetWorth],[sweep]) values ('"
+ FirstName.Text.Trim() + "', '" + LastName.Text.Trim() + "', '" + DOB + "', '" + Email.Text.Trim() + "', '" + HKID.Text.Trim() + "', '" + AccountNumber.Text.Trim() + "', '" + Building.Text.Trim() + "', '" + Street.Text.Trim() + "', '" + District.Text.Trim() + "', '" + HomePhone.Text.Trim() + "', '" + HomeFax.Text.Trim() + "', '" + BusinessPhone.Text.Trim() + "', '" + MobilePhone.Text.Trim() + "', '" + CountryOfCitizenship.Text.Trim() + "', '" + CountryOfLegalResidence.Text.Trim() + "', '" + PassportCountryOfIssue.Text.Trim() + "', '" + ddlEmploymentStatus.SelectedValue.ToString().Trim() + "', '" + Occupation.Text.Trim() + "', '" + YearsWithEmployer.Text.Trim() + "', '" + EmployerName.Text.Trim() + "', '" + EmployerPhone.Text.Trim() + "', '" + NatureOfBusiness.Text.Trim() + "', '" + ddlEmployedByFinanceInst.SelectedValue.ToString().Trim() + "', '" + ddlMemberPublicTradedInst.SelectedValue.ToString().Trim() + "', '" + ddlPrimarySourceOfFunds.SelectedValue.ToString().Trim() + "', '" + ddlInvestmentObjective.SelectedValue.ToString().Trim() + "', '" + ddlInvestmentKnowledge.SelectedValue.ToString().Trim() + "', '" + ddlAnnualIncome.SelectedValue.ToString().Trim() + "', '" + ddlApproxLiquidNetWorth.SelectedValue.ToString().Trim() + "', '" + ddlSweep.SelectedValue.ToString().Trim() + "')", trans);
myHKeInvestData.commitTransaction(trans);
}
private void Check_AlertStatus()
{
HKeInvestData myHKeInvestData = new HKeInvestData();
ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable dtAlert = myHKeInvestData.getData("SELECT * FROM [Alert]");
foreach (DataRow alert in dtAlert.Rows)
{
string accountNumber = alert.Field <string>("accountNumber");
string code = alert.Field <string>("code");
string type = alert.Field <string>("type");
string highOrLow = alert.Field <string>("highOrLow");
decimal value = alert.Field <decimal>("value");
string isSameSide = alert.Field <string>("isSameSide");
decimal currPrice = myExternalFunctions.getSecuritiesPrice(type, code);
if (((highOrLow == "high" && currPrice >= value) || (highOrLow == "low" && currPrice <= value)) && isSameSide == "no")
{
// send notification to the client and cancel the alert.
string sql = string.Format("DELETE FROM [Alert] WHERE accountNumber='{0}' AND code='{1}' AND type='{2}' AND highOrLow='{3}'",
accountNumber,
code,
type,
highOrLow);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
Send_Notification(accountNumber, type, code, highOrLow, currPrice);
}
else if (isSameSide == "yes" && ((highOrLow == "high" && currPrice < value) || (highOrLow == "low" && currPrice > value)))
{
string sql = string.Format("UPDATE [Alert] SET isSameSide='no' WHERE accountNumber='{0}' AND code='{1}' AND type='{2}' AND highOrLow='{3}'",
accountNumber,
code,
type,
highOrLow);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
}
}
private decimal Update_AccountBalance(string accountNumber, decimal balance, decimal serviceFee, decimal totalPrice, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
decimal newBalance = balance - serviceFee;
if (buyOrSell == "buy")
{
newBalance = newBalance - totalPrice;
}
else if (buyOrSell == "sell")
{
newBalance = newBalance + totalPrice;
}
string sql = string.Format("UPDATE [Account] SET balance={0} WHERE accountNumber='{1}'", newBalance, accountNumber);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
return(newBalance);
}
protected void CreateAccount(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//AddAccountRecord
string generateAccNum = "";
//SELECT accountNumber FROM Account WHERE accountNumber LIKE 'AA%'
string accEng = "";
if (LastName.Text.Length == 1)
{
accEng = accEng + LastName.Text.ToUpper() + LastName.Text.ToUpper();
}
else if (LastName.Text.Length > 1)
{
accEng = accEng + LastName.Text.Substring(0, 2).ToUpper();
}
int acDigit = 1;
//string accDigit = "";
string precedingzeros = "";
int precedzeros = 8 - acDigit.ToString().Length;
DataTable samelastname = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE accountNumber LIKE '" + accEng + "%' ORDER BY accountNumber");
if (samelastname.Rows.Count != 0)
{
foreach (DataRow row in samelastname.Rows)
{
//for each accNum with same last name, compare the 8 digit and returns the one havn't used
int ifDigitEq = 0;
string accindatabase = "" + row["accountNumber"];
string compareAccDigit = accindatabase.Substring(2, 8);
string precedzero = "";
for (int i = 0; i < precedzeros; i++)
{
precedzero = precedzero + "0";
}
string comAccDigit = precedingzeros + acDigit.ToString();
ifDigitEq = compareAccDigit.CompareTo(comAccDigit);
Console.WriteLine(compareAccDigit);
if (ifDigitEq != 1 || ifDigitEq != -1)
{
acDigit = acDigit + 1;
}
}
}
for (int i = 0; i < precedzeros; i++)
{
precedingzeros = precedingzeros + "0";
}
generateAccNum = accEng + precedingzeros + acDigit.ToString();
//inserting data into table Account
SqlTransaction tranAcc = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Account (accountNumber, accountType, balance, sweepFreeCredit) VALUES ('" + generateAccNum + "', '" + ddlAccType.SelectedValue + "', " + deposit.Text + ", '" + ddlsweep.SelectedValue + "')", tranAcc);
myHKeInvestData.commitTransaction(tranAcc);
//inserting data into table Client
SqlTransaction tranCli = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Client (accountNumber, title, lastName, firstName, dateOfBirth, email, building, street, district, homePhone, homeFax, businessPhone, mobile, citizenship, legalResidence, HKIDPassportNumber, passportCountryOfIssue) VALUES ('" + generateAccNum + "', '" + ddlTitle.SelectedValue + "', '" + LastName.Text + "', '" + FirstName.Text + "', '" + DateOfBirth.Text + "', '" + Email.Text + "', '" + Building.Text + "', '" + Street.Text + "', '" + District.Text + "', " + HomePhone.Text + ", " + HomeFax.Text + ", " + BusinessPhone.Text + ", " + MobilePhone.Text + ", '" + Citizenship.Text + "', '" + Residence.Text + "', '" + HKID.Text + "', '" + PassportCountry.Text + "')", tranCli);
myHKeInvestData.commitTransaction(tranCli);
//inserting data into table Employment
if (ddlEmployed.SelectedValue != "employed")
{
SqlTransaction tranEmpl = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Employment (accountNumber, status) VALUES ('" + generateAccNum + "', '" + ddlEmployed.SelectedValue + "')", tranEmpl);
myHKeInvestData.commitTransaction(tranEmpl);
}
else
{
SqlTransaction tranEmpl = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Employment (accountNumber, status, specificOccupation, yearsWithEmployer, employerName, employerPhone, businessNature) VALUES ('" + generateAccNum + "', '" + ddlEmployed.SelectedValue + "', '" + specificOccupation.Text + "', " + yearEmploy.Text + ", '" + employerName.Text + "', " + employerPhone.Text + ", '" + busiNature.Text + "')", tranEmpl);
myHKeInvestData.commitTransaction(tranEmpl);
}
//inserting data into table Investment
SqlTransaction tranInv = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO RegulatoryDisclosures (accountNumber, employedByFinancialInstitution, publiclyTradedCompany, primarySourceOfFunds, otherSource) VALUES ('" + generateAccNum + "', '" + ddlemployedByFinancialInstitution.SelectedValue + "', '" + ddlDirector.SelectedValue + "', '" + ddlPrimarySource.SelectedValue + "', '" + otherPrimarySource.Text + "')", tranInv);
myHKeInvestData.commitTransaction(tranInv);
//inserting data into table Regulatory Disclosures
SqlTransaction tranReg = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Investment (accountNumber, objective, knowledge, experience, annualIncome, liquidNetWorth) VALUES ('" + generateAccNum + "', '" + ddlInvestmentObjective.SelectedValue + "', '" + ddlInvestmentKnowledge.SelectedValue + "', '" + ddlInvestmentExperience.SelectedValue + "', '" + ddlAnnualIncome.SelectedValue + "', '" + ddlNetWorth.SelectedValue + "')", tranReg);
myHKeInvestData.commitTransaction(tranReg);
//inserting data into table Security Holdings
/*SqlTransaction tranSec = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("", tranSec);
* myHKeInvestData.commitTransaction(tranSec);*/
//INSERT INTO Account (accountNumber, accountType, balance) VALUES ('HI00000001', 'individual', 1000)
//generate a new account number for new added client
/*string generateAccNum= "";
* SqlTransaction trans = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("update [Account] set [accountNumber]='" + generateAccNum + "' WHERE [HKIDPassportNumber] = '" + HKID.Text + "'", trans);
* myHKeInvestData.commitTransaction(trans);*/
//}
}
}
protected void cvAccountNumber_ServerValidate(object source, ServerValidateEventArgs args)
{
if (AccountNumber.Text != null && LastName.Text != null)
{
string accountnumber = AccountNumber.Text;
string lastname = LastName.Text.ToUpper();
Int32 test;
string sql = "SELECT userName FROM Account WHERE accountNumber = '" + accountnumber + "'";
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtClient = myHKeInvestData.getData(sql);
foreach (DataRow row in dtClient.Rows)
{
if (row != null)
{
args.IsValid = false;
cvAccountNumber.ErrorMessage = "This account number has already been used to create an account.";
}
}
if (accountnumber.Length == 10)
{
if ((!Int32.TryParse(accountnumber.Substring(2), out test)))
{
args.IsValid = false;
cvAccountNumber.ErrorMessage = "The format of account number is not correct.";
}
}
else
{
args.IsValid = false;
}
if (lastname.Length == 1)
{
if (accountnumber[0] != lastname[0])
{
args.IsValid = false;
}
if (accountnumber[1] != lastname[0])
{
args.IsValid = false;
}
if (args.IsValid == false)
{
cvAccountNumber.ErrorMessage = "The account number does not match the client's last name.";
}
}
if (lastname.Length > 1)
{
if (accountnumber[0] != lastname[0])
{
args.IsValid = false;
}
if (accountnumber[1] != lastname[1])
{
args.IsValid = false;
}
if (args.IsValid == false)
{
cvAccountNumber.ErrorMessage = "The account number does not match the client's last name.";
}
}
}
}
protected void setAlertValue(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//get user id
string loginuser = Context.User.Identity.GetUserName();
DataTable idsearch = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'");
string loginuserid = "";
foreach (DataRow row in idsearch.Rows)
{
loginuserid = loginuserid + row["accountNumber"];
}
//************Now loginuserid stores the id**************
string choosencode = Snamecode.SelectedValue.Trim();
string choosentype = Stype.SelectedValue.Trim();
string high = "NULL";
string low = "NULL";
string inputhigh = high = highValue.Text.Trim();
string inputlow = lowValue.Text.Trim();
if (highValue.Text.Trim() != "")
{
high = highValue.Text.Trim();
}
if (lowValue.Text.Trim() != "")
{
low = lowValue.Text.Trim();
}
//verify if alert had been set
DataTable checkalert = myHKeInvestData.getData("SELECT * FROM Alert WHERE accountNumber = '" + loginuserid + "' AND type = '" + choosentype + "' AND code = '" + choosencode + "'");
if (checkalert.Rows.Count == 0)
{
//add new alert data if doesnt exist
SqlTransaction addalertdata = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Alert (accountNumber, type, code, highValue, lowValue) VALUES ('" + loginuserid + "', '" + choosentype + "', '" + choosencode + "', " + high + ", " + low + ")", addalertdata);
myHKeInvestData.commitTransaction(addalertdata);
}
else
{
//update alert info (cover old value)
SqlTransaction modifyalertdata = myHKeInvestData.beginTransaction();
if (inputhigh != "" && inputlow != "")
{
myHKeInvestData.setData("UPDATE Alert SET highValue = '" + high + "', lowValue = '" + low + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
else if (inputhigh == "" && inputlow != "")
{
myHKeInvestData.setData("UPDATE Alert SET lowValue = '" + low + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
else if (inputhigh != "" && inputlow == "")
{
myHKeInvestData.setData("UPDATE Alert SET highValue = '" + high + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
Label1.Visible = true;
}
string curhighv = "";
string curlowv = "";
DataTable curalert = myHKeInvestData.getData("SELECT * FROM Alert WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'");
if (curalert.Rows.Count == 0)
{
}
else
{
foreach (DataRow row in curalert.Rows)
{
curhighv = curhighv + row["highValue"];
curlowv = curlowv + row["lowValue"];
}
curhigh.Text = curhighv;
curlow.Text = curlowv;
}
}
}
private void PeriodicTask()
{
HKeInvestData myHKeInvestData = new HKeInvestData();
ExternalFunctions myExternalFunctions = new ExternalFunctions();
do
{
DataTable dtOrders = myHKeInvestData.getData("SELECT * FROM [Order]");
foreach (DataRow order in dtOrders.Rows)
{
string code = order.Field <string>("securityCode");
string referenceNumber = order.Field <string>("orderReferenceNumber");
string oldStatus = order.Field <string>("orderStatus").Trim();
string status = myExternalFunctions.getOrderStatus(referenceNumber).Trim();
if (status == "partial" || oldStatus != status)
{
DataTable dtTransaction = myExternalFunctions.getOrderTransaction(referenceNumber);
// update the local transaction table, get the new transactions in dtChanges
DataTable dtChanges = Sync_TransactionTable(dtTransaction, referenceNumber);
if (dtChanges != null || oldStatus != status)
{
string type, buyOrSell;
DataTable dtOrderDetails = getOrderDetails(referenceNumber, out type, out buyOrSell);
if (dtOrderDetails == null)
{
// cannot find the order details, internal error
continue;
}
string accountNumber = order.Field <string>("accountNumber");
DataTable dtAccount = myHKeInvestData.getData("SELECT balance FROM [Account] WHERE accountNumber='" + accountNumber + "'");
if (dtAccount.Rows.Count != 1)
{
// cannot find the account balance, internal error
continue;
}
decimal balance = dtAccount.Rows[0].Field <decimal>("balance");
// calculate the total executed price for dtChanges not all transactions
decimal totalPrice, totalShares;
string securityBase;
if (dtChanges != null)
{
Calculate_totalPrice(dtChanges, type, code, out totalPrice, out totalShares, out securityBase);
// update account balance and security holdings
balance = Update_AccountBalance(accountNumber, balance, 0, totalPrice, buyOrSell);
Update_SecurityHolding(dtOrderDetails, type, accountNumber, totalShares, securityBase, buyOrSell);
}
if (oldStatus != status)
{
if (status == "completed" || status == "cancelled")
{
// order finished execution
decimal assets = Get_Assets(accountNumber, balance);
// calculate service fee
Calculate_totalPrice(dtTransaction, type, code, out totalPrice, out totalShares, out securityBase);
decimal serviceFee = Calculate_ServiceFee(totalPrice, assets, type, buyOrSell, dtOrderDetails);
// update order status
Update_OrderStatus(referenceNumber, status, serviceFee);
// update account balance and security holdings
Update_AccountBalance(accountNumber, balance, serviceFee, 0, buyOrSell);
// send invoice to client
Send_Invoice(referenceNumber, accountNumber, buyOrSell, code, type, dtOrderDetails, serviceFee, totalPrice, totalShares, dtTransaction);
}
else
{
Update_OrderStatus(referenceNumber, status, 0);
}
}
}
}
}
// check the status of alerts
Check_AlertStatus();
Thread.Sleep(20000);
} while (true);
throw new NotImplementedException();
}
private void Send_Invoice(string referenceNumber, string accountNumber, string buyOrSell, string code, string type,
DataTable dtOrderDetails, decimal serviceFee, decimal totalPrice, decimal totalShares, DataTable dtTransaction)
{
// fetch the primary account holder email
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtClient = myHKeInvestData.getData(string.Format("SELECT lastName, email FROM [Client] WHERE accountNumber='{0}' AND isPrimary=(1)", accountNumber));
if (dtClient.Rows.Count != 1)
{
return;
}
string destination = dtClient.Rows[0].Field <string>("email");
string lastName = dtClient.Rows[0].Field <string>("lastName");
#region construct the invoice
string text = string.Format("Hi, {11}<br/>One of your order has been processd<br/>order reference number: {0}<br/>" +
"account number: {1}<br/>" +
"buy or sell: {2}<br/>" +
"security code: {3}<br/>" +
"security name: {4}<br/>" +
"stock order type: {5}<br/>" +
"date of submission: {6}<br/>" +
"total number of shares {7}: {8}<br/>" +
"total executed HKD amount: {9}<br/>" +
"fee charged: {10}<br/>",
referenceNumber,
accountNumber,
buyOrSell,
code,
dtOrderDetails.Rows[0].Field <string>("securityName"),
type == "stock" ? dtOrderDetails.Rows[0].Field <string>("stockOrderType") : "N/A",
dtOrderDetails.Rows[0].Field <DateTime>("dateOfSubmission"),
buyOrSell == "buy" ? "bought" : "sold", totalShares,
totalPrice,
serviceFee,
lastName);
string transactionDetails = "transaction details:<br/>";
foreach (DataRow transaction in dtTransaction.Rows)
{
string transactionNumber = transaction.Field <int>("transactionNumber").ToString("00000000");
DateTime executeDate = transaction.Field <DateTime>("executeDate");
decimal executeShares = transaction.Field <decimal>("executeShares");
decimal executePrice = transaction.Field <decimal>("executePrice");
transactionDetails += string.Format("transaction number:{0} execute date:{1} execute shares:{2} execute price:{3}<br/>", transactionNumber, executeDate, executeShares, executePrice);
}
text += transactionDetails;
string html = text;
#endregion
#region emailAccount
string username = "******";
#endregion
// Create an instance of MailMessage named mail.
MailMessage mail = new MailMessage();
// Set the sender (From), receiver (To), subject and message body fields of the mail message.
mail.From = new MailAddress(username, "Team104 Newbee");
mail.To.Add(destination);
mail.Subject = "[HKeInvest] Security Invoice";
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(text, null, MediaTypeNames.Text.Plain));
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(html, null, MediaTypeNames.Text.Html));
// Create an instance of SmtpClient named emailServer and set the mail server to use as "smtp.cse.ust.hk".
SmtpClient emailServer = new SmtpClient("smtp.cse.ust.hk");
// Send the message.
emailServer.Send(mail);
return;
}
protected void CreateUser_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string idnum = HKID.Text.Trim();
string mail = Email.Text.Trim();
//check if HKIDPassportNumber is really stored in the database
DataTable curHKID = myHKeInvestData.getData("SELECT HKIDPassportNumber FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
if (curHKID.Rows.Count == 0)
{
ErrorMessage.Text = "The input data does not match the client data.";
return;
}
//check if input data matches the one in database
DataTable checkdata = myHKeInvestData.getData("SELECT email, accountNumber, lastName, firstName, dateOfBirth FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
DataTable checkdate = myHKeInvestData.getData("SELECT Convert(varchar(10),CONVERT(date,dateOfBirth,106),103) AS DOB FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
string checkemail = "";
string checkAccNum = "";
string checklastname = "";
string checkfirstname = "";
string checkDOB = "";
foreach (DataRow row in checkdata.Rows)
{
checkemail = checkemail + row["email"];
checkAccNum = checkAccNum + row["accountNumber"];
checklastname = checklastname + row["lastName"];
checkfirstname = checkfirstname + row["firstName"];
}
foreach (DataRow row in checkdate.Rows)
{
checkDOB = checkDOB + row["DOB"];
}
int emailcheck = checkemail.CompareTo(Email.Text.Trim());
Console.WriteLine(emailcheck);
int accNumCheck = checkAccNum.CompareTo(AccountNumber.Text.Trim());
Console.WriteLine(accNumCheck);
int lastNameCheck = checklastname.CompareTo(LastName.Text.Trim());
Console.WriteLine(lastNameCheck);
int firstNameCheck = checkfirstname.CompareTo(FirstName.Text.Trim());
Console.WriteLine(firstNameCheck);
int DOBcheck = checkDOB.CompareTo(DateOfBirth.Text.ToString());
Console.WriteLine(DOBcheck);
if (emailcheck == -1 || accNumCheck == -1 || lastNameCheck == -1 || firstNameCheck == -1 || DOBcheck == -1)
{
ErrorMessage.Text = "The input data does not match the client data.";
return;
}
var manager = Context.GetOwinContext().GetUserManager <ApplicationUserManager>();
var signInManager = Context.GetOwinContext().Get <ApplicationSignInManager>();
var user = new ApplicationUser()
{
UserName = UserName.Text, Email = Email.Text
};
IdentityResult result = manager.Create(user, Password.Text);
if (result.Succeeded)
{
//assign to role client
IdentityResult roleResult = manager.AddToRole(user.Id, "Client");
SqlTransaction trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("update [Account] set [userName]='" + UserName.Text + "' WHERE [accountNumber] = '" + AccountNumber.Text + "'", trans);
myHKeInvestData.commitTransaction(trans);
if (!roleResult.Succeeded)
{
ErrorMessage.Text = roleResult.Errors.FirstOrDefault();
}
// For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771
//string code = manager.GenerateEmailConfirmationToken(user.Id);
//string callbackUrl = IdentityHelper.GetUserConfirmationRedirectUrl(code, user.Id, Request);
//manager.SendEmail(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>.");
signInManager.SignIn(user, isPersistent: false, rememberBrowser: false);
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
else
{
ErrorMessage.Text = result.Errors.FirstOrDefault();
}
/*SqlTransaction trans = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("update [Account] set [userName]='" + UserName.Text + "' WHERE [accountNumber] = '" + AccountNumber.Text + "'", trans);
* myHKeInvestData.commitTransaction(trans);*/
}
}
private DataTable Sync_TransactionTable(DataTable dtTransaction, string orderReferenceNumber)
{
if (dtTransaction == null)
{
return(null);
}
// clone the table, and convert the column type
DataTable dtCloned = new DataTable();
var primaryKey = dtCloned.Columns.Add("transactionNumber", typeof(string));
dtCloned.Columns.Add("orderReferenceNumber", typeof(string));
dtCloned.Columns.Add("executeDate", typeof(DateTime));
dtCloned.Columns.Add("executeShares", typeof(decimal));
dtCloned.Columns.Add("executePrice", typeof(decimal));
dtCloned.PrimaryKey = new DataColumn[] { primaryKey };
foreach (DataRow transaction in dtTransaction.Rows)
{
DateTime executeDate = transaction.Field <DateTime>("executeDate");
string transactionNumber = transaction.Field <int>("transactionNumber").ToString("00000000");
string referenceNumber = transaction.Field <int>("referenceNumber").ToString("00000000");
decimal executeShares = transaction.Field <decimal>("executeShares");
decimal executePrice = transaction.Field <decimal>("executePrice");
DataRow newRow = dtCloned.NewRow();
newRow["transactionNumber"] = transactionNumber;
newRow["orderReferenceNumber"] = referenceNumber;
newRow["executeDate"] = executeDate;
newRow["executeShares"] = executeShares;
newRow["executePrice"] = executePrice;
dtCloned.Rows.Add(newRow);
}
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtLast = myHKeInvestData.getData("SELECT * FROM [Transaction] WHERE [orderReferenceNumber]='" + orderReferenceNumber.Trim() + "'");
dtLast.AcceptChanges();
dtLast.Merge(dtCloned);
DataTable dtChanges = dtLast.GetChanges(DataRowState.Added);
if (dtChanges == null)
{
return(null);
}
foreach (DataRow transaction in dtChanges.Rows)
{
DateTime executeDate = transaction.Field <DateTime>("executeDate");
string transactionNumber = transaction.Field <string>("transactionNumber");
string referenceNumber = transaction.Field <string>("orderReferenceNumber");
decimal executeShares = transaction.Field <decimal>("executeShares");
decimal executePrice = transaction.Field <decimal>("executePrice");
string date = executeDate.ToString("MM/dd/yyyy hh:mm:ss tt");
string sql = string.Format("INSERT INTO [Transaction] VALUES ('{0}', '{1}', '{2}', {3}, {4})",
transactionNumber,
referenceNumber,
date,
executeShares,
executePrice);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
return(dtChanges);
}
