|
public ExecuteQuery ( string sql, string parameters, string values ) : |
||
| sql | string | |
| parameters | string | |
| values | string | |
| return | ||
//table not ready yet
protected void btnShipStatus_Click(object sender, EventArgs e)
{
DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
//eventually going to need something like a 'where shipdate > datetime.now' for pending orders
string sql = "select * from orders";
DataSet ds = new DataSet();
string[] s = { };
ds = da.ExecuteQuery(sql, s, s);
gvShipStatus.DataSource = ds.Tables[0];
gvShipStatus.DataBind();
//code for tablesorter ready gridviews
if (this.gvShipStatus.Rows.Count > 0)
{
gvShipStatus.UseAccessibleHeader = true;
gvShipStatus.HeaderRow.TableSection = TableRowSection.TableHeader;
gvShipStatus.FooterRow.TableSection = TableRowSection.TableFooter;
}
//end
s = null;
sql = null;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
string sql = "select * from categories;";
DataSet ds = new DataSet();
string[] s = { };
ds = da.ExecuteQuery(sql, s, s);
repeater1.DataSource = ds.Tables[0];
repeater1.DataBind();
s = null;
sql = null;
}
}
protected void btnClickMe_Click(object sender, EventArgs e)
{
DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
string sql1 = "Update categories set CategoryName = @catname where categoryid = @catid";
//the parameters must be in the order they appear in the sql above!
string[] s1 = { "@catname", "@catid" };
string[] r1 = { txtsearch.Text, "1" };
da.ExecuteNonQuery(sql1, s1, r1);
//Rob wrote all of this code....
string sql = "select * from categories where categoryName = @categoryname";
DataSet ds = new DataSet();
string[] s = {"@categoryname"};
string[] r = {txtsearch.Text};
ds = da.ExecuteQuery(sql, s, r);
repeater1.DataSource = ds.Tables[0];
repeater1.DataBind();
s = null;
sql = null;
}
private void GetOrderInfo()
{
string Orders_OrderDate_Start = txtOrders_OrderDate_Start.Text;
string Orders_OrderDate_End = txtOrders_OrderDate_End.Text;
string Orders_NetTotal_Start = txtOrders_NetTotal_Start.Text;
string Orders_NetTotal_End = txtOrders_NetTotal_End.Text;
string Customer_CustomerID = txtCustomer_CustomerID.Text;
string Customer_FName = txtCustomer_FName.Text;
string Customer_LName = txtCustomer_LName.Text;
string Customer_UserName = txtCustomer_UserName.Text;
string Customer_City = txtCustomer_City.Text;
string Customer_State = txtCustomer_State.Text;
string Items_ProductName = txtItems_ProductName.Text;
DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
DataSet ds = new DataSet();
string s1;
string s2 = "";
int fields = CountUsedFields();
int p;
string[] p1 = new string[fields];
string[] v1 = new string[fields];
s1 = "SELECT o.* FROM Orders o " +
"RIGHT OUTER JOIN Customer c ON o.CustomerID = c.CustomerID ";
if (Items_ProductName != "")
{
s1 += "RIGHT OUTER JOIN OrderItems oi ON o.ORDERID = oi.OrderID " +
"INNER JOIN Items i ON oi.ItemID = i.ItemID AND oi.VendorID = i.VendorID ";
}
s1 += "WHERE ";
if (Orders_OrderDate_Start != "")
{
s2 += "AND o.OrderDate >= @OrderDate ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@OrderDate";
v1[p] = Orders_OrderDate_Start;
}
if (Orders_OrderDate_End != "")
{
s2 += "AND o.OrderDate <= @OrderDate ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@OrderDate";
v1[p] = Orders_OrderDate_End;
}
if (Orders_NetTotal_Start != "")
{
s2 += "AND o.NetTotal >= @NetTotal ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@NetTotal, ";
v1[p] = Orders_NetTotal_Start + ", ";
}
if (Orders_NetTotal_End != "")
{
s2 += "AND o.NetTotal <= @NetTotal ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@NetTotal, ";
v1[p] = Orders_NetTotal_End + ", ";
}
if (Customer_CustomerID != "")
{
s2 += "AND c.CustomerID = @CustomerID ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@CustomerID, ";
v1[p] = Customer_CustomerID + ", ";
}
if (Customer_FName != "")
{
s2 += "AND c.FName = @FName ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@FName, ";
v1[p] = Customer_FName + ", ";
}
if (Customer_LName != "")
{
s2 += "AND c.LName = @LName ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@LName, ";
v1[p] = Customer_LName + ", ";
}
if (Customer_UserName != "")
{
s2 += "AND c.UserName = @UserName ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@UserName, ";
v1[p] = Customer_UserName + ", ";
}
if (Customer_City != "")
{
s2 += "AND c.City = @City ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@City, ";
v1[p] = Customer_City + ", ";
}
if (Customer_State != "")
{
s2 += "AND c.State = @State ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@State, ";
v1[p] = Customer_State + ", ";
}
if (Items_ProductName != "")
{
s2 += "AND i.ProductName = @ProductName ";
p = 0;
while (p1[p] != "")
{
p += 1;
}
p1[p] = "@ProductName, ";
v1[p] = Items_ProductName + ", ";
}
s2 = s2.TrimStart('A', 'N', 'D', ' ');
s1 += s2;
ds = da.ExecuteQuery(s1, p1, v1);
gvOrders1.DataSource = ds.Tables[0];
gvOrders1.DataBind();
if (this.gvOrders1.Rows.Count > 0)
{
gvOrders1.UseAccessibleHeader = true;
gvOrders1.HeaderRow.TableSection = TableRowSection.TableHeader;
gvOrders1.FooterRow.TableSection = TableRowSection.TableFooter;
}
}
private string txn_id; //unique transaction id
#endregion Fields
#region Methods
protected void Page_Load(object sender, EventArgs e)
{
//Post back to either sandbox or live
string strSandbox = "https://www.sandbox.paypal.com/cgi-bin/webscr";
string strLive = "https://www.paypal.com/cgi-bin/webscr";
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strSandbox);
//Set values for the request back
req.Method = "POST";
req.ContentType = "application/x-www-form-urlencoded";
byte[] param = Request.BinaryRead(HttpContext.Current.Request.ContentLength);
string strRequest = Encoding.ASCII.GetString(param);
strRequest += "&cmd=_notify-validate";
req.ContentLength = strRequest.Length;
//for proxy
//WebProxy proxy = new WebProxy(new Uri("http://*****:*****@orderID";
DataSet ds6 = new DataSet();
// make arrays for paramaters and input
string[] s6 = { "@orderID" };
string[] v6 = { orderID };
ds6 = da6.ExecuteQuery(comm6, s6, v6);
// returns a 1 if the item exists if not the transaction is a dummy
grossTotal = decimal.Parse(ds6.Tables[0].Rows[0].ItemArray[0].ToString());
// subtract shipping to compare to gross total
decimal total = decimal.Parse(grossTotal.ToString("n2")) - decimal.Parse(mc_shipping);
//clear
s6 = null;
v6 = null;
// make sure customer paid the correct amount
// total < 0 for reversals
if (grossTotal.ToString("n2") == total.ToString("n2") || total < 0)
{
// check to see if email returned is ours
if (receiver_email == "*****@*****.**") // make sure the receiver email is ours
{
// count how many orderIDs that have not been verified exist in the orders table
DAL.DataAccess da5 =
new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
// make command statement
string comm5 = "SELECT COUNT(OrderID) FROM Orders WHERE TXNID = @txnID";
DataSet ds5 = new DataSet();
// make arrays for paramaters and input
string[] s5 = { "@txnID" };
string[] v5 = { txn_id };
ds5 = da5.ExecuteQuery(comm5, s5, v5);
// returns one item
txnID = ds5.Tables[0].Rows[0].ItemArray[0];
//clear
s5 = null;
v5 = null;
if (int.Parse(txnID.ToString()) == 0)
{
if (payment_status == "Completed")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus, Date = @date = @paymentStatus WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Completed", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is pending
if (payment_status == "Pending")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Pending", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Processed
if (payment_status == "Processed")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Processed", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Refunded
// parent_txn_id = old txn_id
if (payment_status == "Refunded")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Refunded", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Reversed
// parent_txn_id = old txn_id
if (payment_status == "Reversed")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Reversed", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Canceled_Reversal
// parent_txn_id = old txn_id
if (payment_status == "Canceled_Reversal")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Canceled Reversal", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Voided
if (payment_status == "Voided")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Voided", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
// if payment status is Denied
if (payment_status == "Denied")
{
// update total of orders table for the customer
DAL.DataAccess da2 =
new DAL.DataAccess(
ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
string comm2 =
"UPDATE Orders SET TXNID = @txnID, PaymentStatus = @paymentStatus, Date = @date WHERE OrderID = @orderID";
// empty array
string[] p2 = { "@txnID", "@paymentStatus", "@orderID", "@date" };
string[] v2 = { txn_id, "Denied", orderID, datetime.ToString() };
da2.ExecuteNonQuery(comm2, p2, v2);
// clear
p2 = null;
v2 = null;
}
}
//abandon session
Session.Abandon();
Session.Clear();
}
}
} // end of try
catch (SqlException)
{
// nothing
}
catch (Exception)
{
// nothing
}
// string paymentStatus = HttpUtility.UrlDecode(Request.Form["payment_status"].ToString());
}
else if (strResponse == "INVALID")
{
//log for manual investigation
}
}
// check to see if item is on sale
private bool isItemOnSale()
{
GetItems();
// get the customerID of the user who is logged on
DAL.DataAccess da4 = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
// make command statement
string comm4 = "SELECT DiscountedPrice FROM Items WHERE ItemID = @itemid";
//"SELECT Count(*) FROM Orders"; //WHERE CustomerID = @customerID AND TXNID = @txnID";
DataSet ds4 = new DataSet();
// make arrays for paramaters and input
string[] s4 = { "@itemID" };
string[] v4 = { itemID.Text };
ds4 = da4.ExecuteQuery(comm4, s4, v4);
// returns one item
object item = ds4.Tables[0].Rows[0].ItemArray[0];
//clear
s4 = null;
v4 = null;
// if the items discounted price
// is blank the item is not discounted
if (item.ToString() == "")
{
return false;
}
return true;
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string s1;
string[] p1 = { "@VendorID" };
string[] v1 = { txtVendorID.Text };
DAL.DataAccess da = new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString, "System.Data.SqlClient");
DataSet ds = new DataSet();
s1 = "SELECT VendorID,IsActive,VendorName,MainPhone,ContactName,ContactEmail, " +
"ContactPhone, Website, Address, Address2, City, State, Zip, Country " +
"FROM Vendor WHERE VendorID = @VendorID";
ds = da.ExecuteQuery(s1, p1, v1);
cboxIsActive.Checked = Convert.ToBoolean(ds.Tables[0].Rows[0]["IsActive"].ToString());
txtVendorName.Text = ds.Tables[0].Rows[0]["VendorName"].ToString();
txtMainPhone.Text = ds.Tables[0].Rows[0]["MainPhone"].ToString();
txtContactName.Text = ds.Tables[0].Rows[0]["ContactName"].ToString();
txtContactEmail.Text = ds.Tables[0].Rows[0]["ContactEmail"].ToString();
txtContactPhone.Text = ds.Tables[0].Rows[0]["ContactPhone"].ToString();
txtWebsite.Text = ds.Tables[0].Rows[0]["Website"].ToString();
txtAddress.Text = ds.Tables[0].Rows[0]["Address"].ToString();
txtAddress2.Text = ds.Tables[0].Rows[0]["Address2"].ToString();
txtCity.Text = ds.Tables[0].Rows[0]["City"].ToString();
txtState.Text = ds.Tables[0].Rows[0]["State"].ToString();
txtZip.Text = ds.Tables[0].Rows[0]["Zip"].ToString();
txtCountry.Text = ds.Tables[0].Rows[0]["Country"].ToString();
txtVendorID.Enabled = false;
cboxIsActive.Enabled = true;
txtVendorName.Enabled = true;
txtMainPhone.Enabled = true;
txtContactName.Enabled = true;
txtContactEmail.Enabled = true;
txtContactPhone.Enabled = true;
txtWebsite.Enabled = true;
txtAddress.Enabled = true;
txtAddress2.Enabled = true;
txtCity.Enabled = true;
txtState.Enabled = true;
txtZip.Enabled = true;
txtCountry.Enabled = true;
}
private void BindGridRepeater()
{
if (System.Web.HttpContext.Current.User.Identity.IsAuthenticated || Session["AnonymousUserName"] != null)
{
// fill up gridview
// instantiate class
DAL.DataAccess da =
new DAL.DataAccess(ConfigurationManager.ConnectionStrings["MyPetStoreDB"].ConnectionString,
"System.Data.SqlClient");
// sql command
string comm =
"SELECT Orders.OrderID, Orders.CustomerID, OrderItem.ItemID, OrderItem.Price, OrderItem.TotalPrice, OrderItem.Quantity, Items.ItemID, Items.ProductName, Items.Description, Items.PhotoLocation, Items.QuantityAvailable, Items.MinQuantity, Items.VendorID FROM Orders, OrderItem, Items WHERE Orders.OrderID = OrderItem.OrderID and OrderItem.ItemID = Items.ItemID and Orders.CustomerID = @customerID AND Orders.TXNID = @txnID";
// data set
DataSet ds = new DataSet();
// empty array
string[] p = { "@customerID", "@txnID" };
string[] v = { GetCustomerID(), "" };
ds = da.ExecuteQuery(comm, p, v);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
// clear
p = null;
v = null;
// fill up repeater
// instantiate class
Order order = new Order();
order.CustomerId = int.Parse(GetCustomerID());
order.TxnId = "";
OrderDA orderDA = new OrderDA();
Collection<Order> getOrder = orderDA.Get(order);
rptOne.DataSource = getOrder;
rptOne.DataBind();
// clear
p = null;
v = null;
}
else
{
items.InnerHtml = "<h1>" + "Your Shopping Cart is Empty." + "</h1>";
}
}
