// --------------------------------------------------------
// ---------------- Ship Email ---------------
// --------------------------------------------------------
public List <ShipmentInfoVO> GetShipmentInfoData()
{
SqlDataReader rdr = null;
var list = new List <ShipmentInfoVO>();
try {
//SqlParameter[] param = { new SqlParameter("@DeliveryNumber", deliveryNumber)};
string sqlQuery = @"SELECT DISTINCT
R1.DocEntry AS DocEntry
, OC.CardCode AS CardCode
, OH.DocNum AS OrderNumber
, OH.CardName AS ShipToName
, OC.E_Mail ShipToEmail
, OH.U_ShippingMethod AS ShippingMethod
, R12.StreetS AS ShipToAddress1
, R12.BlockS AS ShipToAddress2
, R12.CityS AS ShipToCity
, R12.StateS AS ShipToState
, R12.ZipCodeS AS ShipToZip
, R1.U_TrackingNumber AS TrackingNumber
, R1.U_DeliveryNumber AS DeliveryNumber
, CASE WHEN OL.ShortName = 'CO' THEN 'ES' ELSE OL.ShortName END AS Language
FROM ORDR OH
INNER JOIN RDR1 R1
ON OH.DocEntry = R1.DocEntry
INNER JOIN RDR12 R12
ON OH.DocEntry = R12.DocEntry
INNER JOIN OCRD OC
ON OH.CardCode = OC.CardCode
INNER JOIN OLNG OL
ON OC.LangCode = OL.Code
WHERE CANCELED = 'N'
AND ISNULL(R1.U_DeliveryNumber,'') <> ''
AND ISNULL(R1.U_TrackingNumber,'') <> ''
AND ISNULL(R1.U_IsShipped,'') = ''
AND OH.DocDate >= '2020-05-01'
AND OC.U_OptOut = 0
";
rdr = getRecordSet(sqlQuery);
if (rdr.HasRows)
{
while (rdr.Read())
{
var vo = new ShipmentInfoVO();
vo.DocEntry = rdr["DocEntry"].ToString();
vo.DeliveryNumber = rdr["DeliveryNumber"].ToString();
vo.OrderNumber = rdr["OrderNumber"].ToString();
vo.CardCode = rdr["CardCode"].ToString();
vo.Language = rdr["Language"].ToString();
vo.ShipToEmail = rdr["ShipToEmail"].ToString();
vo.ShipToName = rdr["ShipToName"].ToString();
vo.ShippingMethod = rdr["ShippingMethod"].ToString();
vo.ShipToAddress1 = rdr["ShipToAddress1"].ToString();
vo.ShipToAddress2 = rdr["ShipToAddress2"].ToString();
vo.ShipToCity = rdr["ShipToCity"].ToString();
vo.ShipToState = rdr["ShipToState"].ToString();
vo.ShipToZip = rdr["ShipToZip"].ToString();
vo.TrackingNumber = rdr["TrackingNumber"].ToString();
list.Add(vo);
}
}
if (rdr != null && rdr.IsClosed == false)
{
rdr.Close();
}
} catch (SqlException ex) {
log.Error("SQL Exception", ex);
MailHelper.AddSystemIssueString(GetType().Name, ex.ToString());
throw ex;
} catch (Exception e) {
log.Error("Exception", e);
MailHelper.AddSystemIssueString(GetType().Name, e.ToString());
throw e;
} finally {
if (rdr != null && rdr.IsClosed == false)
{
rdr.Close();
}
}
return(list);
}
/* *********************************************
* s : Custom mail function for sepific Class
* *********************************************/
public static void SendShipEmailToCustomer(ShipmentInfoVO shipmentInfoVo)
{
var customerSenderName = System.Configuration.ConfigurationManager.AppSettings["CustomerSenderName"];
var customerSenderEmail = System.Configuration.ConfigurationManager.AppSettings["CustomerSenderEmail"];
var customerRecipientsName = System.Configuration.ConfigurationManager.AppSettings["CustomerRecipientsName"];
var customerRecipientsEmail = System.Configuration.ConfigurationManager.AppSettings["CustomerRecipientsEmail"];
var customerCcEmail = System.Configuration.ConfigurationManager.AppSettings["CustomerCcEmail"];
var customerBccEmail = System.Configuration.ConfigurationManager.AppSettings["CustomerBccEmail"];
var EMAIL_CONTENT_SERVER_PROTOCOL = System.Configuration.ConfigurationManager.AppSettings["EmailConetentServerWithProtocol"];
var EMAIL_CONTENT_SERVER = System.Configuration.ConfigurationManager.AppSettings["EmailConetentServer"];
var EMAIL_SHIPMENT_CONTENT = System.Configuration.ConfigurationManager.AppSettings["EmailShipmentPath"];
var EMAIL_WILLCALL_URL = System.Configuration.ConfigurationManager.AppSettings["EmailWillCallPath"];
var SUBJECT_ENGLISH = System.Configuration.ConfigurationManager.AppSettings["SubjectEnglish"];
var SUBJECT_SPANISH = System.Configuration.ConfigurationManager.AppSettings["SubjectFrench"];
var SUBJECT_FRENCH = System.Configuration.ConfigurationManager.AppSettings["SubjectSpanish"];
var ORDER_CONTENT_PATH_ENGLISH = System.Configuration.ConfigurationManager.AppSettings["OrderPathEnglish"];
var ORDER_CONTENT_PATH_SPANISH = System.Configuration.ConfigurationManager.AppSettings["OrderPathSpanish"];
var ORDER_CONTENT_PATH_FRENCH = System.Configuration.ConfigurationManager.AppSettings["OrderPathFrench"];
string CUSTOMER_CARE_PHONE = System.Configuration.ConfigurationManager.AppSettings["OrderPathFrench"];
var subject = SUBJECT_ENGLISH;
var contentUrl = EMAIL_SHIPMENT_CONTENT;
var orderContentPath = ORDER_CONTENT_PATH_ENGLISH;
var langCode = shipmentInfoVo.Language.ToLower() + "-us";
if (shipmentInfoVo.Language == "FR")
{
langCode = shipmentInfoVo.Language.ToLower() + "-ca";
}
if (shipmentInfoVo.Language == "ES")
{
subject = SUBJECT_SPANISH;
orderContentPath = ORDER_CONTENT_PATH_SPANISH;
}
else if (shipmentInfoVo.Language == "ES")
{
subject = SUBJECT_FRENCH;
orderContentPath = ORDER_CONTENT_PATH_FRENCH;
}
var message = string.Empty;
if (shipmentInfoVo.ShippingMethod == "CA_VAN_WILLCALL")
{
subject = "Your Univera Order is Ready to Pick Up.";
contentUrl = EMAIL_WILLCALL_URL;
}
var fullURL = EMAIL_CONTENT_SERVER_PROTOCOL + "/" + langCode + contentUrl;
var orderLink = EMAIL_CONTENT_SERVER + "/" + langCode + orderContentPath;
var trackingURL = GetCarrierURL(shipmentInfoVo.ShippingMethod, shipmentInfoVo.TrackingNumber, langCode);
var firstName = string.Empty;
try {
firstName = shipmentInfoVo.ShipToName.Contains(',') ? shipmentInfoVo.ShipToName.Split(' ').Last() : shipmentInfoVo.ShipToName.Split(' ').First();
} catch (Exception e) {
firstName = shipmentInfoVo.ShipToName;
}
var emailRecipient = shipmentInfoVo.ShipToEmail;
var content = new WebCallHelper().GetRequestString(fullURL);
message = content.Replace("{{FIRSTNAME}}", shipmentInfoVo.ShipToName)
.Replace("{{ORDER_LINK}}", orderLink)
.Replace("{{ORDERNUMBER}}", shipmentInfoVo.OrderNumber)
.Replace("{{FULLNAME}}", shipmentInfoVo.ShipToName)
.Replace("{{ADDRESS1}}", shipmentInfoVo.ShipToAddress1)
.Replace("{{ADDRESS2}}", shipmentInfoVo.ShipToAddress2)
.Replace("{{CITY}}", shipmentInfoVo.ShipToCity)
.Replace("{{STATE}}", shipmentInfoVo.ShipToState)
.Replace("{{POSTALCODE}}", shipmentInfoVo.ShipToZip + "\r\n")
.Replace("{{TRACKINGNUMBER}}", shipmentInfoVo.TrackingNumber)
.Replace("{{TRACKING_LINK}}", trackingURL + "\r\n")
.Replace("{{COUNTRYPHONE}}", CUSTOMER_CARE_PHONE);
if (emailRecipient != null && emailRecipient != "" && shipmentInfoVo.ShipToEmail.Equals("*****@*****.**") == false)
{
SendMailsOutside(subject, message, customerSenderName, customerSenderEmail, null, shipmentInfoVo.ShipToEmail, null, customerBccEmail, null);
}
}
