/// <summary>
/// Read an order from the database
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public OrderInfo GetOrder(int orderId)
{
//Create a parameter
SqlParameter parm = new SqlParameter(PARM_ORDER_ID, SqlDbType.Int);
parm.Value = orderId;
//Execute a query to read the order
using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQL_SELECT_ORDER, parm)) {
if (rdr.Read()) {
//Generate an order header from the first row
CreditCardInfo creditCard = new CreditCardInfo(rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null);
AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null);
OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), creditCard, billingAddress, shippingAddress, rdr.GetDecimal(21));
ArrayList lineItems = new ArrayList();
LineItemInfo item = null;
//Create the lineitems from the first row and subsequent rows
do{
item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
lineItems.Add(item);
}while(rdr.Read());
order.LineItems = (LineItemInfo[])lineItems.ToArray(typeof(LineItemInfo));
return order;
}
}
return null;
}
/// <summary>
/// Read an order from the database
/// </summary>
/// <param name="orderId">Order Id</param>
/// <returns>Details of the Order</returns>
public OrderInfo GetOrder(int orderId)
{
//Create a parameter
OracleParameter parm = new OracleParameter(PARM_ORDER_ID, OracleType.Number);
parm.Value = orderId;
//Execute a query to read the order
using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) {
if (rdr.Read()) {
//Generate an order header from the first row
AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email");
AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email");
OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null);
IList<LineItemInfo> lineItems = new List<LineItemInfo>();
LineItemInfo item = null;
//Create the lineitems from the first row and subsequent rows
do {
item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
lineItems.Add(item);
} while (rdr.Read());
order.LineItems = new LineItemInfo[lineItems.Count];
lineItems.CopyTo(order.LineItems, 0);
return order;
}
}
return null;
}
/// <summary>
/// Constructor with specified initial values
/// </summary>
/// <param name="orderId">Unique identifier</param>
/// <param name="date">Order date</param>
/// <param name="userId">User placing order</param>
/// <param name="creditCard">Credit card used for order</param>
/// <param name="billing">Billing address for the order</param>
/// <param name="shipping">Shipping address for the order</param>
/// <param name="total">Order total value</param>
public OrderInfo(int orderId, DateTime date, string userId, CreditCardInfo creditCard, AddressInfo billing, AddressInfo shipping, decimal total)
{
this._orderId = orderId;
this._date = date;
this._userId = userId;
this._creditCard = creditCard;
this._billingAddress = billing;
this._shippingAddress = shipping;
this._orderTotal = total;
}
/// <summary>
/// Constructor with specified initial values
/// </summary>
/// <param name="userId">Unique identifier</param>
/// <param name="password">Password</param>
/// <param name="email">Email address</param>
/// <param name="address">The default address object</param>
/// <param name="language">Prefered language</param>
/// <param name="category">Favourite Category</param>
/// <param name="showFavorites">Show customized favourites based on prefered category</param>
/// <param name="showBanners">Show personalized banners</param>
public AccountInfo(string userId, string password, string email, AddressInfo address, string language, string category, bool showFavorites, bool showBanners)
{
this._userId = userId;
this._password = password;
this._email = email;
this._address = address;
this._language = language;
this._category = category;
this._showFavorites = showFavorites;
this._showBanners = showBanners;
}
/// <summary>
/// Constructor with specified initial values
/// </summary>
/// <param name="orderId">Unique identifier</param>
/// <param name="date">Order date</param>
/// <param name="userId">User placing order</param>
/// <param name="creditCard">Credit card used for order</param>
/// <param name="billing">Billing address for the order</param>
/// <param name="shipping">Shipping address for the order</param>
/// <param name="total">Order total value</param>
/// <param name="line">Ordered items</param>
/// <param name="authorization">Credit card authorization number</param>
public OrderInfo(int orderId, DateTime date, string userId, CreditCardInfo creditCard, AddressInfo billing, AddressInfo shipping, decimal total, LineItemInfo[] line, Nullable<int> authorization)
{
this.orderId = orderId;
this.date = date;
this.userId = userId;
this.creditCard = creditCard;
this.billingAddress = billing;
this.shippingAddress = shipping;
this.orderTotal = total;
this.lineItems = line;
this.authorizationNumber = authorization;
}
public void ShowAddress(AddressInfo address)
{
if (address!= null){
// update fields with info
lblFirstName.Text = address.FirstName;
lblLastName.Text = address.LastName;
lblAdr1.Text = address.Address1;
lblAdr2.Text = address.Address2;
lblCity.Text = address.City;
lblState.Text = address.State;
lblPostalCode.Text = address.Zip;
}
}
/// <summary>
/// Return the address information for a user
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public AddressInfo GetAddress(string userId)
{
AddressInfo address= null;
SqlParameter[] addressParms = GetAddressParameters();
addressParms[0].Value = userId;
using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQL_SELECT_ADDRESS, addressParms)) {
if (rdr.Read()) {
address = new AddressInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8));
}
}
return address;
}
/// <summary>
/// Update account for current user
/// </summary>
/// <param name="uniqueID">User id</param>
/// <param name="addressInfo">Account information for current user</param>
public void SetAccountInfo(int uniqueID, AddressInfo addressInfo)
{
string sqlDelete = "DELETE FROM Account WHERE UniqueID = :UniqueID";
OracleParameter param = new OracleParameter(":UniqueID", OracleType.Int32);
param.Value = uniqueID;
string sqlInsert = "INSERT INTO Account (UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone) VALUES (:UniqueID, :Email, :FirstName, :LastName, :Address1, :Address2, :City, :State, :Zip, :Country, :Phone)";
OracleParameter[] parms = {
new OracleParameter(":UniqueID", OracleType.Number, 10),
new OracleParameter(":Email", OracleType.VarChar, 80),
new OracleParameter(":FirstName", OracleType.VarChar, 80),
new OracleParameter(":LastName", OracleType.VarChar, 80),
new OracleParameter(":Address1", OracleType.VarChar, 80),
new OracleParameter(":Address2", OracleType.VarChar, 80),
new OracleParameter(":City", OracleType.VarChar, 80),
new OracleParameter(":State", OracleType.VarChar, 80),
new OracleParameter(":Zip", OracleType.VarChar, 80),
new OracleParameter(":Country", OracleType.VarChar, 80),
new OracleParameter(":Phone", OracleType.VarChar, 80)};
parms[0].Value = uniqueID;
parms[1].Value = addressInfo.Email;
parms[2].Value = addressInfo.FirstName;
parms[3].Value = addressInfo.LastName;
parms[4].Value = addressInfo.Address1;
parms[5].Value = addressInfo.Address2;
parms[6].Value = addressInfo.City;
parms[7].Value = addressInfo.State;
parms[8].Value = addressInfo.Zip;
parms[9].Value = addressInfo.Country;
parms[10].Value = addressInfo.Phone;
OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringProfile);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, param);
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
trans.Commit();
}
catch(Exception e) {
trans.Rollback();
throw new ApplicationException(e.Message);
}
finally {
conn.Close();
}
}
/// <summary>
/// Retrieve account information for current username and application.
/// </summary>
/// <param name="userName">User Name</param>
/// <param name="appName">Application Name</param>
/// <returns>Account information for current user</returns>
public AddressInfo GetAccountInfo(string userName, string appName)
{
string sqlSelect = "SELECT Account.Email, Account.FirstName, Account.LastName, Account.Address1, Account.Address2, Account.City, Account.State, Account.Zip, Account.Country, Account.Phone FROM Account, Profiles WHERE Account.UniqueID = Profiles.UniqueID AND Profiles.Username = :Username AND Profiles.ApplicationName = :ApplicationName";
OracleParameter[] parms = {
new OracleParameter(":Username", OracleType.VarChar, 256),
new OracleParameter(":ApplicationName", OracleType.VarChar, 256)};
parms[0].Value = userName;
parms[1].Value = appName;
AddressInfo addressInfo = null;
OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, sqlSelect, parms);
while (dr.Read()) {
string address2 = string.Empty;
if (!dr.IsDBNull(4))
address2 = dr.GetString(4);
addressInfo = new AddressInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), address2, dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(0));
}
dr.Close();
return addressInfo;
}
/// <summary>
/// Verify the users login credentials against the database
/// If the user is valid return all information for the user
/// </summary>
/// <param name="userId">Username</param>
/// <param name="password">password</param>
/// <returns></returns>
public AccountInfo SignIn(string userId, string password)
{
SqlParameter[] signOnParms = GetSignOnParameters();
signOnParms[0].Value = userId;
signOnParms[1].Value = password;
using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQL_SELECT_ACCOUNT, signOnParms)) {
if (rdr.Read()) {
AddressInfo myAddress = new AddressInfo(rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9));
return new AccountInfo(userId, password, rdr.GetString(0), myAddress, rdr.GetString(10), rdr.GetString(11), Convert.ToBoolean(rdr.GetInt32(12)), Convert.ToBoolean(rdr.GetInt32(13)));
}
return null;
}
}
/// <summary>
/// Update account for current user
/// </summary>
/// <param name="uniqueID">User id</param>
/// <param name="addressInfo">Account information for current user</param>
public void SetAccountInfo(int uniqueID, AddressInfo addressInfo)
{
string sqlDelete = "DELETE FROM Account WHERE UniqueID = @UniqueID;";
SqlParameter param = new SqlParameter("@UniqueID", SqlDbType.Int);
param.Value = uniqueID;
string sqlInsert = "INSERT INTO Account (UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone) VALUES (@UniqueID, @Email, @FirstName, @LastName, @Address1, @Address2, @City, @State, @Zip, @Country, @Phone);";
SqlParameter[] parms = {
new SqlParameter("@UniqueID", SqlDbType.Int),
new SqlParameter("@Email", SqlDbType.VarChar, 80),
new SqlParameter("@FirstName", SqlDbType.VarChar, 80),
new SqlParameter("@LastName", SqlDbType.VarChar, 80),
new SqlParameter("@Address1", SqlDbType.VarChar, 80),
new SqlParameter("@Address2", SqlDbType.VarChar, 80),
new SqlParameter("@City", SqlDbType.VarChar, 80),
new SqlParameter("@State", SqlDbType.VarChar, 80),
new SqlParameter("@Zip", SqlDbType.VarChar, 80),
new SqlParameter("@Country", SqlDbType.VarChar, 80),
new SqlParameter("@Phone", SqlDbType.VarChar, 80)};
parms[0].Value = uniqueID;
parms[1].Value = addressInfo.Email;
parms[2].Value = addressInfo.FirstName;
parms[3].Value = addressInfo.LastName;
parms[4].Value = addressInfo.Address1;
parms[5].Value = addressInfo.Address2;
parms[6].Value = addressInfo.City;
parms[7].Value = addressInfo.State;
parms[8].Value = addressInfo.Zip;
parms[9].Value = addressInfo.Country;
parms[10].Value = addressInfo.Phone;
SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringProfile);
conn.Open();
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, param);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
trans.Commit();
}
catch(Exception e) {
trans.Rollback();
throw new ApplicationException(e.Message);
}
finally {
conn.Close();
}
}
/// <summary>
/// A method to store the shipping address information
/// </summary>
public void StoreShippingAddress(AddressInfo shippingAddress)
{
HttpContext.Current.Session[SHIPPING_KEY] = shippingAddress;
}
/// <summary>
/// A method to store the billing address information
/// </summary>
public void StoreBillingAddress(AddressInfo billingAddress)
{
HttpContext.Current.Session[BILLING_KEY] = billingAddress;
}
// Profile setters
// Update account info
private static void SetAccountInfo(int uniqueID, AddressInfo addressInfo)
{
dal.SetAccountInfo(uniqueID, addressInfo);
}