private void populateDeliveryBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT name FROM ecommerce.DELIVERY_COMPANY" +
" WHERE hasExpressDelivery = @hasExpressDelivery", con);
cm1.Parameters.Add("@hasExpressDelivery", SqlDbType.Bit).Value = hasExpressDelivery;
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
deliveryBox.Items.Add(rd1["name"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.PRODUCT_CATEGORY", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["Code"].ToString());
item.SubItems.Add(rd1["Name"].ToString());
item.SubItems.Add(rd1["VAT"].ToString());
Cat_ListView.Items.Add(item);
}
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private String getCategoryCode(String category_str)
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("SELECT Code FROM ecommerce.PRODUCT_CATEGORY " +
"WHERE Name = @Name", con);
cm1.Parameters.Add("@Name", SqlDbType.VarChar).Value = category_str;
SqlDataReader rd1 = cm1.ExecuteReader();
rd1.Read();
return(rd1["Code"].ToString());
}
FormValidation.showError("Category cannot be empty.");
return("");
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
return("");
}
finally
{
con.Close();
}
}
private void generateTotalBids()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT COUNT(*) FROM ecommerce.BID WHERE auctionID = @auctionID", con);
cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID;
int count_bids = (int)cm1.ExecuteScalar();
totalbids_label.Text = count_bids.ToString();
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateBuyerBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT userName FROM ecommerce.REGULAR_USER", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
buyerBox.Items.Add(rd1["userName"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateDeliveryBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT DISTINCT Delivery_Company_Name FROM ecommerce.VIEW_ONGOING_SHIPPING", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
deliveryBox.Items.Add(rd1["Delivery_Company_Name"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateFormDetails()
{
labelPurchase.Text = purchaseID.ToString();
labelAmount.Text = amount.ToString("F");
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM " +
"ecommerce.VIEW_PENDING_PAYMENTS WHERE purchaseID = @purchaseID", con);
cm1.Parameters.Add("@purchaseID", SqlDbType.Int).Value = purchaseID;
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
labelUsername.Text = rd1["Buyer_username"].ToString();
name_lbl.Text = rd1["Buyer_Name"].ToString();
billingBox.Text = rd1["Possible_Billing_Address"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private static void closeAuctions()
{
while (true)
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("ecommerce.sp_Close_All_Expired_Auctions", con);
cm1.CommandType = CommandType.StoredProcedure;
cm1.ExecuteNonQuery();
//Console.WriteLine("Executing ecommerce.sp_Close_All_Expired_Auctions");
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
Thread.Sleep(AUCTION_EXPIRE_DELAY);
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.INTERNAL_OPERATION", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["operationID"].ToString());
item.SubItems.Add(rd1["commission"].ToString());
item.SubItems.Add(rd1["collectedVAT"].ToString());
item.SubItems.Add(rd1["paymentCode"].ToString());
item.SubItems.Add(rd1["date"].ToString());
InternalOperationLV.Items.Add(item);
}
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populatePurchaseBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT purchaseID FROM ecommerce.VIEW_PENDING_PAYMENTS", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
purchaseBox.Items.Add(rd1["purchaseID"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
String usernameSeller = sellerBox.Text;
String usernameBuyer = buyerBox.Text;
String auctionID_str = auctionBox.Text;
if (usernameSeller == "")
{
usernameSeller = "%";
}
if (usernameBuyer == "")
{
usernameBuyer = "%";
}
if (auctionID_str == "")
{
auctionID_str = "%";
}
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.VIEW_COMPLETED_PURCHASES " +
"WHERE Seller_Username LIKE @Seller " +
"AND Buyer_Username LIKE @Buyer " +
"AND auctionID LIKE @auctionID", con);
cm1.Parameters.AddWithValue("@Seller", usernameSeller);
cm1.Parameters.AddWithValue("@Buyer", usernameBuyer);
cm1.Parameters.AddWithValue("@auctionID", auctionID_str);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["purchaseID"].ToString());
item.SubItems.Add(rd1["auctionID"].ToString());
item.SubItems.Add(rd1["Buyer_Username"].ToString());
item.SubItems.Add(rd1["Seller_Username"].ToString());
item.SubItems.Add(rd1["Purchase_Final_Price"].ToString());
purchCompLV.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
public decimal getMinValidBid(int auctionID)
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
String query = "SELECT Min_Valid_Bid " +
"FROM ecommerce.VIEW_MIN_VALID_BID " +
"WHERE auctionID = @auctionID";
SqlCommand cm1 = new SqlCommand(query, con);
cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID;
SqlDataReader rd1 = cm1.ExecuteReader();
rd1.Read();
return(Convert.ToDecimal(rd1["Min_Valid_Bid"]));
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
return(-1);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.BID WHERE auctionID = @auctionID", con);
cm1.Parameters.Add("@auctionID", SqlDbType.Int).Value = auctionID;
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["bidID"].ToString());
item.SubItems.Add(rd1["Amount"].ToString());
item.SubItems.Add(rd1["userName_Buyer"].ToString());
item.SubItems.Add(rd1["Date"].ToString());
BidLV.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private String getSeller(int code)
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT userName_Seller FROM " +
"ecommerce.PRODUCT WHERE Code = @Code", con);
cm1.Parameters.Add("@Code", SqlDbType.Int).Value = code;
SqlDataReader rd1 = cm1.ExecuteReader();
rd1.Read();
return(rd1["userName_Seller"].ToString());
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
return("");
}
finally
{
con.Close();
}
}
private void generateTotalCredits()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("SELECT SUM(amount) FROM ecommerce.CREDIT WHERE accountID = @accountID", con);
cm1.Parameters.Add("@accountID", SqlDbType.Int).Value = Convert.ToInt32(accID_label.Text);
decimal sum_credits = (decimal)cm1.ExecuteScalar();
totalcred.Text = sum_credits.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.CREDIT WHERE accountID = @accountID", con);
cm1.Parameters.Add("@accountID", SqlDbType.Int).Value = Convert.ToInt32(accID_label.Text);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["creditID"].ToString());
item.SubItems.Add(rd1["amount"].ToString());
item.SubItems.Add(rd1["paymentCode"].ToString());
item.SubItems.Add(rd1["date"].ToString());
CreditlistView.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateComboBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT DISTINCT username_Seller FROM ecommerce.VIEW_PRODUCT_DETAILS", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
sellerBox.Items.Add(rd1["username_Seller"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateCategoryBox()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm2 = new SqlCommand("SELECT Name FROM ecommerce.PRODUCT_CATEGORY", con);
SqlDataReader rd2 = cm2.ExecuteReader();
while (rd2.Read())
{
categoryBox.Items.Add(rd2["Name"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void company_bttn_Click(object sender, EventArgs e)
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT name FROM " +
"ecommerce.DELIVERY_COMPANY JOIN ecommerce.SHIPPING " +
"ON ecommerce.DELIVERY_COMPANY.name = ecommerce.SHIPPING.deliveryCompany " +
"WHERE ecommerce.SHIPPING.Code = @Shipping_Code", con);
cm1.Parameters.Add("@Shipping_Code", SqlDbType.Int).Value = shippingCode;
SqlDataReader rd1 = cm1.ExecuteReader();
rd1.Read();
String delivery_company_name = rd1["name"].ToString();
Delivery_Comp f = new Delivery_Comp(delivery_company_name);
f.Show();
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.[USER] AS us JOIN ecommerce.REGULAR_USER AS ru ON us.userName = ru.userName ORDER BY us.userName", con);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["userName"].ToString());
item.SubItems.Add(rd1["Name"].ToString());
item.SubItems.Add(rd1["Email"].ToString());
item.SubItems.Add(rd1["TIN"].ToString());
item.SubItems.Add(rd1["Address"].ToString());
listViewRU.Items.Add(item);
}
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
String usernameBuyer = buyerBox.Text;
String purchaseID_str = purchaseBox.Text;
String paymentCode_str = payBox.Text;
if (usernameBuyer == "")
{
usernameBuyer = "%";
}
if (purchaseID_str == "")
{
purchaseID_str = "%";
}
if (paymentCode_str == "")
{
paymentCode_str = "%";
}
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM ecommerce.VIEW_COMPLETED_PAYMENTS " +
"WHERE Buyer_Username LIKE @Buyer " +
"AND payment_code LIKE @payment_code " +
"AND purchaseID LIKE @purchaseID", con);
cm1.Parameters.AddWithValue("@Buyer", usernameBuyer);
cm1.Parameters.AddWithValue("@purchaseID", purchaseID_str);
cm1.Parameters.AddWithValue("@payment_code", paymentCode_str);
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["payment_code"].ToString());
item.SubItems.Add(rd1["purchaseID"].ToString());
item.SubItems.Add(rd1["Buyer_Username"].ToString());
item.SubItems.Add(rd1["Amount"].ToString());
item.SubItems.Add(rd1["Payment_Date"].ToString());
payCompLV.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void submitButton_Click(object sender, EventArgs e)
{
String delivery_company = deliveryBox.Text;
String seller_address = sellerAdBox.Text;
String buyer_address = buyerAdBox.Text;
DateTime dispatchDate;
DateTime estimatedArrivalDate;
try
{
dispatchDate = Convert.ToDateTime(dispatch_date_dtp.Text);
estimatedArrivalDate = get_Estimated_Arrival_Date(dispatchDate, hasExpressDelivery);
}
catch (Exception ex)
{
FormValidation.showError("The dispatch date has formatting issues.");
return;
}
if (!FormValidation.validateShipping(purchaseID, delivery_company, seller_address, buyer_address,
dispatchDate, estimatedArrivalDate))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand("ecommerce.sp_Create_Shipping", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@deliveryCompany", delivery_company);
cmd.Parameters.AddWithValue("@dispatchDate", dispatchDate);
cmd.Parameters.AddWithValue("@estimatedArrivalDate", estimatedArrivalDate);
cmd.Parameters.AddWithValue("@purchaseID", purchaseID);
cmd.Parameters.AddWithValue("@dispatch_address", seller_address);
cmd.Parameters.AddWithValue("@delivery_address", buyer_address);
cmd.ExecuteNonQuery();
MessageBox.Show("You have ordered a new shipping!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
clear_text();
this.Close();
}
private void submitButton_Click(object sender, EventArgs e)
{
String name = nameLabel.Text;
String phone = PhoneBox.Text;
String address = AddressBox.Text;
Boolean hasExpressDelivery;
if (YesButton.Checked)
{
hasExpressDelivery = true;
}
else if (NoButton.Checked)
{
hasExpressDelivery = false;
}
else
{
FormValidation.showError("You have to check if the company provides express delivery.");
return;
}
if (!FormValidation.validateDeliveryCompany(name, phone, address, hasExpressDelivery))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Update_DeliveryCompany", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@name", name);
cmd1.Parameters.AddWithValue("@contactNumber", phone);
cmd1.Parameters.AddWithValue("@address", address);
cmd1.Parameters.AddWithValue("@hasExpressDelivery", hasExpressDelivery);
cmd1.ExecuteNonQuery();
MessageBox.Show("You have updated a delivery company!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
clear_text();
this.Close();
}
private void submitButton_Click(object sender, EventArgs e)
{
String cc_no = noBox.Text;
String cc_cvc = cvcBox.Text;
String billing_address = billingBox.Text;
DateTime expiry_date;
try
{
expiry_date = Convert.ToDateTime(expiry_date_dtp.Text);
}
catch (Exception ex)
{
FormValidation.showError("The expiry date has formatting issues.");
return;
}
if (!FormValidation.validatePayment(purchaseID, amount, billing_address,
cc_no, cc_cvc, expiry_date))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cmd = new SqlCommand("ecommerce.sp_Create_Payment", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@billingAddress", billing_address);
cmd.Parameters.AddWithValue("@creditCardCVC", cc_cvc);
cmd.Parameters.AddWithValue("@amount", amount);
cmd.Parameters.AddWithValue("@creditCardNo", cc_no);
cmd.Parameters.AddWithValue("@creditCardExpiryDate", expiry_date);
cmd.Parameters.AddWithValue("@purchaseID", purchaseID);
cmd.ExecuteNonQuery();
MessageBox.Show("You have made a new payment!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
clear_text();
this.Close();
}
private void submitButton_Click(object sender, EventArgs e)
{
decimal debitAmount;
try
{
debitAmount = Convert.ToDecimal(debitBox.Text);
}
catch (Exception ex)
{
FormValidation.showError("The debit amount must be a number.");
return;
}
String iban = ibanBox.Text;
if (!FormValidation.validateDebit(debitAmount, iban))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
}
catch (SqlException ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
return;
}
try
{
SqlCommand cmd3 = new SqlCommand("ecommerce.sp_Create_Debit", con);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.AddWithValue("@debitAmount", debitAmount);
cmd3.Parameters.AddWithValue("@IBAN", iban);
cmd3.Parameters.AddWithValue("@accountID", accountID);
cmd3.ExecuteNonQuery();
MessageBox.Show("You have performed a new debit!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show("Failed to make a new debit:\r\n" + ex.Message, "Failed Operation", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
clear_text();
this.Close();
}
private void populateListView()
{
SqlConnection con = DbConnectionFactory.newConnection();
String order = getDisplayOrder();
String pcode = "%";
if (product_code > 0)
{
pcode = product_code.ToString();
}
try
{
con.Open();
String query = "SELECT mvb.auctionID, Product_Code, Product_Name, " +
"userName_Winner, Min_Valid_Bid, FinishDate, Seller " +
"FROM ecommerce.VIEW_MIN_VALID_BID AS mvb " +
"JOIN ecommerce.VIEW_AUCTION_DETAILS AS ad " +
"ON mvb.auctionID = ad.auctionID " +
"LEFT OUTER JOIN ecommerce.VIEW_WINNING_BID AS wb " +
"ON ad.auctionID = wb.auction_ID " +
"WHERE ad.Status = 1 AND Product_Code LIKE @Product_Code " +
"AND BeginningDate < GETDATE() " + order;
SqlCommand cm1 = new SqlCommand(query, con);
cm1.Parameters.Add("@Product_Code", SqlDbType.VarChar).Value = pcode;
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
ListViewItem item = new ListViewItem(rd1["auctionID"].ToString());
item.SubItems.Add(rd1["Product_Code"].ToString());
item.SubItems.Add(rd1["Product_Name"].ToString());
item.SubItems.Add(rd1["userName_Winner"].ToString());
item.SubItems.Add(rd1["Min_Valid_Bid"].ToString());
item.SubItems.Add(rd1["FinishDate"].ToString());
item.SubItems.Add(rd1["Seller"].ToString());
OpenAuctionLV.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void submitButton_Click(object sender, EventArgs e)
{
decimal bidAmount;
try
{
bidAmount = Convert.ToDecimal(bidBox.Text);
}
catch (Exception ex)
{
FormValidation.showError("The bid amount must be a number.");
return;
}
if (!FormValidation.validateBid(bidAmount))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
// Check if bid is greater or equal to minimum valid bid
if (bidAmount < getMinValidBid(auctionID))
{
FormValidation.showError("The amount must equal or greater than the minimum valid bid.");
return;
}
SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Create_Bid", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@auctionID", auctionID);
cmd1.Parameters.AddWithValue("@Amount", bidAmount);
cmd1.Parameters.AddWithValue("@userName_Buyer", username_Buyer);
cmd1.ExecuteNonQuery();
MessageBox.Show("You have placed a new bid!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
bidBox.Text = "";
this.Close();
}
private void confirmButton_Click(object sender, EventArgs e)
{
Buyer_Name_Record = buyerNameTxtBox.Text;
Seller_Name_Record = sellerNameTxtBox.Text;
Buyer_TIN_Record = buyerTINTxtBox.Text;
Seller_TIN_Record = sellerTINTxtBox.Text;
hasExpressDelivery = expressBttn.Checked ? true : false;
finalPrice = getFinalPrice(auctionID, hasExpressDelivery);
if (!FormValidation.validatePurchase(finalPrice, VAT_Record, hasExpressDelivery,
auctionID, Buyer_Name_Record, Buyer_TIN_Record, Seller_Name_Record, Seller_TIN_Record))
{
return;
}
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cmd1 = new SqlCommand("ecommerce.sp_Create_Purchase", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@amount", finalPrice);
cmd1.Parameters.AddWithValue("@VAT_Record", VAT_Record);
cmd1.Parameters.AddWithValue("@hasExpressDelivery", hasExpressDelivery);
cmd1.Parameters.AddWithValue("@auctionID", auctionID);
cmd1.Parameters.AddWithValue("@Buyer_Name_Record", Buyer_Name_Record);
cmd1.Parameters.AddWithValue("@Buyer_TIN_Record", Buyer_TIN_Record);
cmd1.Parameters.AddWithValue("@Seller_Name_Record", Seller_Name_Record);
cmd1.Parameters.AddWithValue("@Seller_TIN_Record", Seller_TIN_Record);
cmd1.ExecuteNonQuery();
MessageBox.Show("You have made a new purchase!", "Successful Operation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
clearText();
this.Close();
}
private void executeCountQueries()
{
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
// Average No Sells Per User
SqlCommand cm1 = new SqlCommand("select count(*) / cast((select count(*) from ecommerce.regular_user) as decimal)" +
" as 'avg sells per user' " +
"from ecommerce.product as pr " +
"where pr.[status] = 'Sold'", con);
decimal avg_sells = (decimal)cm1.ExecuteScalar();
avgSells.Text = avg_sells.ToString("F");
// Average No Bids Per User
SqlCommand cm2 = new SqlCommand("select count(*) / cast((select count(*) from ecommerce.regular_user) as decimal) " +
" as 'avg bids per user' " +
"from ecommerce.bid", con);
decimal avg_bids = (decimal)cm2.ExecuteScalar();
avgBids.Text = avg_bids.ToString("F");
// Average Seller Rating
SqlCommand cm3 = new SqlCommand("select avg(cast(avgRating as decimal)) " +
"from ecommerce.VIEW_SELLER_RATING", con);
decimal avg_rating = (decimal)cm3.ExecuteScalar();
avgRating.Text = avg_rating.ToString("F");
// % of users that made a bid
SqlCommand cm4 = new SqlCommand("select count(distinct bid.userName_Buyer) / cast((select count(*) from ecommerce.regular_user) as decimal) " +
"as '% of users that made a bid' from ecommerce.bid as bid", con);
decimal per_bids = (decimal)cm4.ExecuteScalar();
per_bids_lbl.Text = per_bids.ToString("F");
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
private void Purchase_Details_Load(object sender, EventArgs e)
{
String delivery = "";
SqlConnection con = DbConnectionFactory.newConnection();
try
{
con.Open();
SqlCommand cm1 = new SqlCommand("SELECT * FROM " +
"ecommerce.VIEW_COMPLETED_PURCHASES WHERE purchaseID = @purchaseID", con);
cm1.Parameters.Add("@purchaseID", SqlDbType.Int).Value = purchaseID;
SqlDataReader rd1 = cm1.ExecuteReader();
while (rd1.Read())
{
price_lbl.Text = rd1["Purchase_Final_Price"].ToString();
date_lbl.Text = rd1["Purchase_Date"].ToString();
name_lbl.Text = rd1["Product_Name"].ToString();
code_lbl.Text = rd1["Product_Code"].ToString();
vat_lbl.Text = rd1["VAT_Record"].ToString();
bid_lbl.Text = rd1["winningAmount"].ToString();
auction_lbl.Text = rd1["auctionID"].ToString();
buyer_user_lbl.Text = rd1["Buyer_Username"].ToString();
buyer_name_lbl.Text = rd1["Buyer_Name_Record"].ToString();
buyer_tin_lbl.Text = rd1["Buyer_TIN_Record"].ToString();
seller_user_lbl.Text = rd1["Seller_Username"].ToString();
seller_name_lbl.Text = rd1["Seller_Name_Record"].ToString();
seller_tin_lbl.Text = rd1["Seller_TIN_Record"].ToString();
bool hasExpressDelivery = (bool)rd1["expressDelivery"];
delivery = hasExpressDelivery ? "Yes - 15€" : "No";
delivery_lbl.Text = delivery;
purchID_lbl.Text = purchaseID.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR \r\n" + ex.Message, "Connection Test", MessageBoxButtons.OK);
}
finally
{
con.Close();
}
}
