public void PostBillingAdress(BillingInfoDTO info, string connectionString)
{
string sql = "INSERT INTO Billinginfo_Table"
+ "(fkOrder_ID, Email, Firstname, Lastname, Streetname, Zipcode, City, Country, Phone)"
+ " VALUES (@fkOrder_ID, @Email, @Firstname, @Lastname, @Streetname, @Zipcode, @City, @Country, @Phone)";
SqlConnection connection = new SqlConnection(connectionString);
using (SqlCommand insertCommand = new SqlCommand(sql, connection))
{
connection.Open();
insertCommand.Parameters.AddWithValue("@fkOrder_ID", info.fkOrder_ID);
insertCommand.Parameters.AddWithValue("@Email", info.Email);
insertCommand.Parameters.AddWithValue("@Firstname", info.Firstname);
insertCommand.Parameters.AddWithValue("@Lastname", info.Lastname);
insertCommand.Parameters.AddWithValue("@Streetname", info.Address);
insertCommand.Parameters.AddWithValue("@Zipcode", info.Zipcode);
insertCommand.Parameters.AddWithValue("@City", info.City);
insertCommand.Parameters.AddWithValue("@Country", info.Country);
insertCommand.Parameters.AddWithValue("@Phone", info.Phone);
insertCommand.ExecuteNonQuery();
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
public BillingInfoDTO GetBillingInfoForOrder(int order_id)
{
string sql = "SELECT Email, Firstname, Lastname, Streetname, Zipcode, City, Country, Phone FROM Billinginfo_Table where fkOrder_ID = @fkOrder_ID";
BillingInfoDTO result = new BillingInfoDTO();
using (SqlConnection databaseConnection = new SqlConnection(ConnectionHelper.GetEnglishConnectionString()))
{
databaseConnection.Open();
using (SqlCommand selectCommand = new SqlCommand(sql, databaseConnection))
{
selectCommand.Parameters.AddWithValue("@fkOrder_ID", order_id);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
string email = reader.GetString(0);
string firstname = reader.GetString(1);
string lastname = reader.GetString(2);
string address = reader.GetString(3);
int zip = reader.GetInt32(4);
string city = reader.GetString(5);
string country = reader.GetString(6);
string phone = reader.GetString(7);
result = new BillingInfoDTO(firstname, lastname, address, zip, city, country, phone, email);
}
}
}
}
databaseConnection.Close();
return(result);
}
}
public void SendOrderConfirmationMail(int orderID)
{
OrderDTO order = oc.GetSingleOrderFromID(orderID);
List <OrderlineDTO> orderLines = oc.GetListOfOrderLineDTOs(orderID);
BillingInfoDTO billingInfo = oc.GetBillingInfoForOrder(orderID);
MailAddress to = new MailAddress(billingInfo.Email, billingInfo.Firstname);
MailAddress from = new MailAddress("*****@*****.**", "SQMY");
MailMessage message = new MailMessage(from, to);
message.Subject = string.Format("Hygge Hejsa");
message.Body = CreateOrderEmailBody(order, orderLines, billingInfo);
message.IsBodyHtml = true;
using (SmtpClient client = new SmtpClient
{
Host = "smtp.gmail.com",
Port = 587,
UseDefaultCredentials = false,
EnableSsl = true
})
try
{
NetworkCredential nc = new NetworkCredential("*****@*****.**", "Pr4ktikH4ngFir3");
client.Credentials = CredentialCache.DefaultNetworkCredentials;
client.Credentials = nc;
client.Send(message);
}
catch (Exception)
{
}
}
private string CreateOrderEmailBody(OrderDTO order, List <OrderlineDTO> orderLines, BillingInfoDTO billingInfo)
{
string body = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath("~/Views/Templates/OrderConfirmationEmailTemplate.html"));
string prodIdListString = "";
string prodAmountListString = "";
string prodPricesListString = "";
string prodPricesTotalListString = "";
float orderTotalPrice = 0;
string prodNameListString = "";
Random rnd = new Random();
DateTime datetime = DateTime.Now;
var shippingDate = datetime.Date.AddDays(rnd.Next(1, 6));
foreach (var item in orderLines)
{
prodIdListString += string.Format("<p>{0}</p>", item.fkProduct_ID);
prodAmountListString += string.Format("<p>{0}</p>", item.Product_Amount);
prodPricesListString += string.Format("<p>{0}</p>", item.Product_Price);
prodPricesTotalListString += string.Format("<p>{0}</p>", (item.Product_Price * item.Product_Amount));
orderTotalPrice += (item.Product_Price * item.Product_Amount);
prodNameListString += string.Format("<p>{0}</p>", pc.GetProductNameByProductID(item.fkProduct_ID, ConnectionHelper.GetEnglishConnectionString()));
}
//Insert member infos
body = body.Replace("{member_name}", "NAME");
body = body.Replace("{member_address}", "Minvej 2");
body = body.Replace("{member_city}", "Minby 2");
body = body.Replace("{member_country}", "Denmark");
//Insert shipment infos
body = body.Replace("{shipping_name}", string.Format("{0} {1}", billingInfo.Firstname, billingInfo.Lastname));
body = body.Replace("{shipping_address}", string.Format("{0}", billingInfo.Address));
body = body.Replace("{shipping_city}", string.Format("{0} {1}", billingInfo.Zipcode, billingInfo.City));
body = body.Replace("{shipping_country}", string.Format("{0}", billingInfo.Country));
body = body.Replace("{shipping_email}", string.Format("{0}", billingInfo.Email));
body = body.Replace("{shipping_phone}", string.Format("{0}", billingInfo.Phone));
body = body.Replace("{arrival_date}", string.Format("{0}", shippingDate.ToString("dd-MM-yyyy")));
//OrderInfos
body = body.Replace("{order_id}", order.Order_ID.ToString());
body = body.Replace("{member_id}", order.Member_ID.ToString());
body = body.Replace("{product_names}", string.Format("{0}", prodNameListString));
body = body.Replace("{product_ids}", string.Format("{0}", prodIdListString));
body = body.Replace("{product_amounts}", prodAmountListString);
body = body.Replace("{product_prices}", prodPricesListString);
body = body.Replace("{product_prices_total}", prodPricesTotalListString);
body = body.Replace("{order_total}", orderTotalPrice.ToString());
body = body.Replace("{order_date}", order.CreatedOn.ToString());
return(body);
}
