public bool AddItem(itemWCF item)
{
itemWCF i = item;
int affected = 0;
//Stream s = new FileStream("", FileMode.Open);
//i.picture = ReadFully(s);
using (SqlConnection connection = new SqlConnection(Constants.SQLConnectionString))
using (SqlCommand cmd = new SqlCommand("INSERT INTO dbo.items(owner_id,chapter_id,name,description,picture,price,quality,sold,date_added) VALUES(@owner_id,@chapter_id,@name,@description,@picture,@price,@quality,@sold,SYSDATETIME())", connection))
{
cmd.Parameters.AddWithValue("@owner_id", i.owner_id);
cmd.Parameters.AddWithValue("@chapter_id", i.chapter_id);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@description", i.description);
cmd.Parameters.Add(new SqlParameter("@picture", SqlDbType.VarBinary, i.picture.Length)
{
Value = (SqlBinary)i.picture
});
cmd.Parameters.AddWithValue("@price", i.price);
cmd.Parameters.AddWithValue("@quality", i.quality);
cmd.Parameters.AddWithValue("@sold", i.sold);
connection.Open();
affected = cmd.ExecuteNonQuery();
}
return(affected > 0 ? true : false);
}
public List <itemWCF> GetItemsAssociatedWithUser(string user_id)
{
List <itemWCF> items = new List <itemWCF>();
using (SqlConnection connection = new SqlConnection(Constants.SQLConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT TOP (@limit) * FROM dbo.items WHERE owner_id = @owner_id", connection))
{
cmd.Parameters.AddWithValue("@limit", 25);
cmd.Parameters.AddWithValue("@owner_id", user_id);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
//TODO: chekc fvalues for null
while (reader.Read())
{
itemWCF i = new itemWCF(
reader.GetInt32(reader.GetOrdinal("id")),
reader.GetString(reader.GetOrdinal("owner_id")),
reader.GetInt32(reader.GetOrdinal("chapter_id")),
reader.GetString(reader.GetOrdinal("name")),
reader.GetString(reader.GetOrdinal("description")),
(byte[])reader.GetSqlBinary(reader.GetOrdinal("picture")) ?? new byte[0],
(float)reader.GetDouble(reader.GetOrdinal("price")),
(float)reader.GetDouble(reader.GetOrdinal("quality")),
reader.GetInt32(reader.GetOrdinal("sold")),
reader.GetDateTime(reader.GetOrdinal("date_added"))
);
items.Add(i);
}
}
}
}
}
return(items);
}
public bool processbuyRequest(string user_id, int item_id)
{
itemWCF item = DBACC.GetItem(item_id);
userWCF owner = GetUser(item.owner_id);
userWCF client = GetUser(user_id); //the user buying the item
fblaChapterWCF fbla = GetFBLAChapter(item.chapter_id);
string clientMailingMessage = string.Format("You are recieving this message because you showed an intrest in buying an item on The FBLA Fundraising app.\nItem Name: {0} \nItem Description: {1} \nItem Price: ${2} \nItem Quality: {3} \n\nIn Order to proceed with your order, a payment of ${2} is required to be sent to the following Paypal address: \n\nPaypal Address: {4} \nContact Address: {5} \n\nAny furthur questions should be sent to the contact email listed above, as any emails sent to this addres will not be replied to.", item.name, item.description, item.price, item.quality, fbla.payment_email, fbla.contact_email);
string ownerMailingMessage = string.Format("You are recieving this message because someone purchased an item that you listed on The FBLA Fundraising App \nItem Name: {0} \nItem Description: {1} \nItem Price: ${2} \nItem Quality: {3} \n If your FBLA Chapter advisor is in posession of this item, you can ignore this message. Otherwise, look for an email from The FBLA Chapter of which you have donated this item for a shipping address, and be prepared to ship this item. ", item.name, item.description, item.price, item.quality);
string adviserMailingMessage = string.Format("You are recieving this message because someone purchased an item from your FBLA Chapter on The FBLA Fundraising App \nItem Name: {0} \nItem Description: {1} \nItem Price: ${2} \nItem Quality: {3} \n\nAs the chapter adviser of {4} FBLA, you are responsible for getting the item to the person who bought it. You should check the paypal account at <b>{5}</b> to confirm that adequate payment has been made before shipping the items to the adress listed in the paypal reciept message. In the case that you are not in posession of the item, you should sent the shipping address to the item owner at {6} to allow them to ship the item. After someone has bought an item, you should delete the item from the app, by going to the items page and clicking the \"X\" in the top corrner", item.name, item.description, item.price, item.quality, fbla.school, fbla.payment_email, owner.email);
string apptitle = "FBLA NLC Fundraising";
//send email to client
mailing.sendMail(client.email, "Item on " + apptitle, clientMailingMessage);
//sent email to item owner
mailing.sendMail(owner.email, "Items on " + apptitle + " bougnt.", ownerMailingMessage);
//send email to chapter adviser
//mailing.sendMail(fbla.contact_email, "Items on " + apptitle + " bougnt.", adviserMailingMessage);
DBACC.setItemSellStatus(item.id, 1);
return(true);
}
public itemWCF GetItem(int item_id)
{
using (SqlConnection connection = new SqlConnection(Constants.SQLConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.items where id = @item_id", connection))
{
cmd.Parameters.AddWithValue("@item_id", item_id);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
while (reader.Read())
{
itemWCF i = new itemWCF(
reader.GetInt32(reader.GetOrdinal("id")),
reader.GetString(reader.GetOrdinal("owner_id")),
reader.GetInt32(reader.GetOrdinal("chapter_id")),
reader.GetString(reader.GetOrdinal("name")),
reader.GetString(reader.GetOrdinal("description")),
(byte[])reader.GetSqlBinary(reader.GetOrdinal("picture")) ?? new byte[0],
(float)reader.GetDouble(reader.GetOrdinal("price")),
(float)reader.GetDouble(reader.GetOrdinal("quality")),
reader.GetInt32(reader.GetOrdinal("sold")),
reader.GetDateTime(reader.GetOrdinal("date_added"))
);
return(i);
}
}
}
}
}
return(null);
}
public List <itemWCF> GetAllItems()
{
List <itemWCF> items = new List <itemWCF>();
using (SqlConnection connection = new SqlConnection(Constants.SQLConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT TOP 25 * from dbo.items ORDER BY date_added DESC,name", connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
while (reader.Read())
{
itemWCF i = new itemWCF(
reader.GetInt32(reader.GetOrdinal("id")),
reader.GetString(reader.GetOrdinal("owner_id")),
reader.GetInt32(reader.GetOrdinal("chapter_id")),
reader.GetString(reader.GetOrdinal("name")),
reader.GetString(reader.GetOrdinal("description")),
(byte[])reader.GetSqlBinary(reader.GetOrdinal("picture")) ?? new byte[0],
(float)reader.GetDouble(reader.GetOrdinal("price")),
(float)reader.GetDouble(reader.GetOrdinal("quality")),
reader.GetInt32(reader.GetOrdinal("sold")),
reader.GetDateTime(reader.GetOrdinal("date_added"))
);
items.Add(i);
}
}
}
}
}
return(items);
}
public bool AddItem(itemWCF item)
{
return(DBACC.AddItem(item));
}
public List <itemWCF> GetFBLAChapterItems(int chapter_id)
{
List <itemWCF> items = new List <itemWCF>();
using (SqlConnection connection = new SqlConnection(Constants.SQLConnectionString))
{
#region getting items based on users id (obfuscated)
//using (SqlCommand cmd = new SqlCommand("select * from dbo.items where owner_id = (select user_id from member_status where chapter_id = @chapter_id)", connection))
//{
// cmd.Parameters.AddWithValue("@chapter_id", chapter_id);
// connection.Open();
// using (SqlDataReader reader = cmd.ExecuteReader())
// {
// // Check is the reader has any rows at all before starting to read.
// if (reader.HasRows)
// {
// // Read advances to the next row.
// //TODO: chekc fvalues for null
// while (reader.Read())
// {
// itemWCF i = new itemWCF(
// reader.GetInt32(reader.GetOrdinal("id")),
// reader.GetString(reader.GetOrdinal("owner_id")),
// reader.GetInt32(reader.GetOrdinal("chapter_id")),
// reader.GetString(reader.GetOrdinal("name")),
// reader.GetString(reader.GetOrdinal("description")),
// (byte[])reader.GetSqlBinary(reader.GetOrdinal("picture")) ?? new byte[0],
// (float)reader.GetDouble(reader.GetOrdinal("price")),
// (float)reader.GetDouble(reader.GetOrdinal("quality")),
// reader.GetBoolean(reader.GetOrdinal("sold")),
// reader.GetDateTime(reader.GetOrdinal("date_added"))
// );
// items.Add(i);
// }
// }
// }
//}
#endregion
#region get items directly associated with chapter
using (SqlCommand cmd = new SqlCommand("select * from dbo.items where chapter_id = @chapter_id", connection))
{
cmd.Parameters.AddWithValue("@chapter_id", chapter_id);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
//TODO: chekc fvalues for null
while (reader.Read())
{
itemWCF i = new itemWCF(
reader.GetInt32(reader.GetOrdinal("id")),
reader.GetString(reader.GetOrdinal("owner_id")),
reader.GetInt32(reader.GetOrdinal("chapter_id")),
reader.GetString(reader.GetOrdinal("name")),
reader.GetString(reader.GetOrdinal("description")),
(byte[])reader.GetSqlBinary(reader.GetOrdinal("picture")) ?? new byte[0],
(float)reader.GetDouble(reader.GetOrdinal("price")),
(float)reader.GetDouble(reader.GetOrdinal("quality")),
reader.GetInt32(reader.GetOrdinal("sold")),
reader.GetDateTime(reader.GetOrdinal("date_added"))
);
items.Add(i);
}
}
}
}
#endregion
}
return(items);
}
