public void TestMSMQ(string body)
{
Warehouse.SendMessage(new BookOrder("tituloteste", 10, 1));
}
//returns the id of inserted order
//return -1 if the book title doesn't exist
//return -2 if other sql error
public int CreateOrder(string title, string client, string email, string address, int quantity)
{
int stock;
double unitPrice;
bool requestWarehouse = false;
using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["store_db"].ConnectionString))
{
try
{
c.Open();
string sql = "select stock, price from [book] where title = @title";
SqlCommand cmd = new SqlCommand(sql, c);
cmd.Parameters.Add("@title", SqlDbType.NVarChar, 50).Value = title;
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(-1);
}
else
{
reader.Read();
stock = reader.GetInt32(0);
unitPrice = reader.GetFloat(1);
}
}
catch (SqlException e)
{
Console.WriteLine(e);
return(-2);
}
finally
{
c.Close();
}
}
using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["store_db"].ConnectionString))
{
try
{
c.Open();
string sql =
"insert into [order](quantity, client_name, address, email, state, state_date, book, total_price) " +
"output inserted.id " +
"values(@quantity, @client_name, @address, @email, @state, @state_date, @book, @total_price)";
SqlCommand cmd = new SqlCommand(sql, c);
cmd.Parameters.Add("@quantity", SqlDbType.Int).Value = quantity;
cmd.Parameters.Add("@client_name", SqlDbType.NVarChar, 80).Value = client;
cmd.Parameters.Add("@address", SqlDbType.NVarChar, 80).Value = address;
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 80).Value = email;
if (stock >= quantity)
{
cmd.Parameters.Add("@state", SqlDbType.Char, 1).Value = 'D'; //dispatched in next day
DateTime nextDay = DateTime.Now.AddDays(1);
cmd.Parameters.Add("@state_date", SqlDbType.DateTime2).Value = nextDay.ToString("yyyy-MM-dd");
UpdateStock(title, 0 - quantity);
// TODO: send email to the client with the info
}
else
{
cmd.Parameters.Add("@state", SqlDbType.Char, 1).Value = 'W'; //waiting expedition
cmd.Parameters.Add("@state_date", SqlDbType.DateTime2).Value = DBNull.Value;
requestWarehouse = true;
}
cmd.Parameters.Add("@book", SqlDbType.NVarChar, 50).Value = title;
cmd.Parameters.Add("@total_price", SqlDbType.Real).Value = unitPrice * quantity;
Int32 inserted = (Int32)cmd.ExecuteScalar();
if (requestWarehouse)
{
Warehouse.SendMessage(new BookOrder(title, 10 * quantity, inserted));
}
else
{
string body = "Mr./Ms. " + client + ",<br>" +
"Your order was succesfully processed and will be dispatched tomorrow.<br><br><br>" +
"<u>Details</u><br>" +
"<b>Book Title:</b> " + title + "<br>" +
"<b>Quantity:</b> " + quantity + "<br>" +
"<b>Total Price:</b> " + Math.Round(unitPrice * quantity, 2) + "<br>" +
"<b>State:</b> Dispatched at " + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") +
"<br>" +
"<b>Address:</b> " + address +
"<br><br><br>" +
"Thanks for choosing our store!";
try
{
SendEmail(email, "[TDIN_bookstore] Order " + inserted + " dispatching", body);
}
catch (Exception)
{
}
}
return(inserted);
}
catch (SqlException)
{
return(-2);
}
finally
{
c.Close();
}
}
}
