private static SysProfile GetClient(string name, ref string error)
{
SysProfile p;
var wrapper = new EncryptSupport.Simple3Des(SecretWord);
// Built in profile called ssl
if (name == "ssl")
{
p = new SysProfile
{
BuilInName = name,
Client = new SmtpClient
{
UseDefaultCredentials = false,
Port = 587,
Host = "smtp.gmail.com",
EnableSsl = true,
Credentials = new NetworkCredential(wrapper.DecryptData("DE5ZET4hY95fZ7JadaxKqchFuvrR3p12vlY="),
wrapper.DecryptData("ovkrtZ/="))
}
};
// encrypted credential
}
// Built in profile called simple
else if (name == "simple")
{
p = new SysProfile
{
BuilInName = name,
Client = new SmtpClient
{
UseDefaultCredentials = true,
Port = 25,
Host = "mail.iskon.hr",
EnableSsl = false
}
};
// encrypted credential
// p.client.Credentials = new NetworkCredential(wrapper.DecryptData("El+=="), wrapper.DecryptData("=="));
}
else
{
// determine profile by quering the database
var listOfParams = new SqlParameter[1];
var pName = new SqlParameter("name", name)
{
Size = 20,
SqlDbType = System.Data.SqlDbType.Char
};
listOfParams[0] = pName;
p = DataAccess.GetProfile(listOfParams, wrapper, ref error);
}
return(p);
}
public int UpdateSystemProfile(SysProfile profile, DataActionMode mode)
{
try
{
switch (mode)
{
case DataActionMode.Add:
_context.SysProfiles.Add(profile);
break;
case DataActionMode.Edit:
var _profile = GetSystemProfile(profile.CompanyCode);
_profile.Address = profile.Address;
_profile.AllowNagativeInventory = profile.AllowNagativeInventory;
_profile.CompanyName = profile.CompanyName;
_profile.ContactName = profile.ContactName;
_profile.Country = profile.Country;
_profile.CreditDuration = profile.CreditDuration;
_profile.CreditLimit = profile.CreditLimit;
_profile.DatabaseName = profile.DatabaseName;
_profile.DBAdmin = profile.DBAdmin;
_profile.DBAdminPassword = profile.DBAdminPassword;
_profile.DefaultLangCode = profile.DefaultLangCode;
_profile.DefaultWeightUOM = profile.DefaultWeightUOM;
_profile.DefaultWH = profile.DefaultWH;
_profile.HomeCurrency = profile.HomeCurrency;
_profile.ImageLocation = profile.ImageLocation;
_profile.PaymentTerm = profile.PaymentTerm;
_profile.ProfitMarkupRate = profile.ProfitMarkupRate;
_profile.ServerName = profile.ServerName;
_profile.SourceCurrency = profile.SourceCurrency;
_profile.TaxID = profile.TaxID;
_profile.TelFax = profile.TelFax;
_profile.VATRate = profile.VATRate;
break;
}
return(_context.SaveChanges());
}
catch
{
return(0);
}
}
private static SysProfile GetClient(string name, ref string error)
{
SysProfile p = null;
EncryptSupport.Simple3Des wrapper = new EncryptSupport.Simple3Des(SECRET_WORD);
//Built in profile called ssl
if (name == "ssl")
{
p = new SysProfile();
p.builInName = name;
p.client = new SmtpClient();
p.client.UseDefaultCredentials = false;
p.client.Port = 587;
p.client.Host = "smtp.gmail.com";
p.client.EnableSsl = true;
// encrypted credential
p.client.Credentials = new NetworkCredential(wrapper.DecryptData("DE5ZET4hY95fZ7JadaxKqchFuvrR3p12vlY="), wrapper.DecryptData("ovkrtZ/="));
}
//Built in profile called simple
else if (name == "simple")
{
p = new SysProfile();
p.builInName = name;
p.client = new SmtpClient();
p.client.UseDefaultCredentials = false;
p.client.Port = 25;
p.client.Host = "mail.iskon.hr";
p.client.EnableSsl = false;
// encrypted credential
p.client.Credentials = new NetworkCredential(wrapper.DecryptData("El+=="), wrapper.DecryptData("=="));
}
else
{
//determine profile by quering the database
SqlParameter[] listOfParams = new SqlParameter[1];
SqlParameter pName = new SqlParameter("name", name);
pName.Size = 20;
pName.SqlDbType = System.Data.SqlDbType.Char;
listOfParams[0] = pName;
p = DataAccess.GetProfile(listOfParams, wrapper, ref error);
}
return(p);
}
private void GetProfile(string CompanyId)
{
switch (_mode)
{
case DataActionMode.Add:
_profile = new SysProfile();
_profile.Address = "";
_profile.AllowNagativeInventory = false;
_profile.CompanyCode = vars.COMPANY_CODE;
_profile.CompanyName = "";
_profile.ContactName = "";
_profile.Country = "";
_profile.CreditDuration = 0;
_profile.CreditLimit = 1000m;
_profile.DatabaseName = vars.DEFAULT_DB_NAME;
_profile.DBAdmin = vars.DBUSER;
_profile.DBAdminPassword = vars.DBPassword;
_profile.DefaultLangCode = "EN";
_profile.DefaultWeightUOM = "GRAM";
_profile.DefaultWH = "";
_profile.HomeCurrency = "USD";
_profile.ImageLocation = "";
_profile.PaymentTerm = "COD";
_profile.ProfitMarkupRate = 100.0m;
_profile.ServerName = vars.SERVER;
_profile.SourceCurrency = "USD";
_profile.TaxID = "";
_profile.TelFax = "";
_profile.VATRate = 7.0m;
break;
case DataActionMode.Edit:
_profile = new SystemDAL().GetSystemProfile(CompanyId);
break;
}
MapProfile();
}
// static bool mailSent = false;
// private static void SendCompletedCallback(object sender, AsyncCompletedEventArgs e)
// {
// // Get the unique identifier for this asynchronous operation.
// String token = (string)e.UserState;
// if (e.Cancelled)
// {
// }
// if (e.Error != null)
// {
// }
// else
// {
// }
// mailSent = true;
// }
private static MailAddress DetermineFromAddress(string fromAddress, string displayName, SysProfile sp)
{
MailAddress retValue;
if (fromAddress.Equals(string.Empty) == false)
{
retValue = displayName.Trim() != string.Empty
? new MailAddress(fromAddress, displayName)
: new MailAddress(fromAddress, sp.DefaultDisplayName ?? "Simple Talk's CLR solution");
}
else
{
retValue = displayName.Trim() != string.Empty
? new MailAddress(sp.DefaultFromAddress, displayName)
: new MailAddress(sp.DefaultFromAddress, sp.DefaultDisplayName ?? "Simple Talk's CLR solution");
}
return(retValue);
}
public static void CLRSendMail([SqlFacet(MaxSize = 20)] SqlString profileName,
SqlString mailTo,
[SqlFacet(IsNullable = true, MaxSize = 255)] SqlString mailSubject,
SqlString mailBody,
[SqlFacet(IsNullable = true, MaxSize = 500)] SqlString fromAddress,
[SqlFacet(IsNullable = true, MaxSize = 400)] SqlString displayName,
[SqlFacet(IsNullable = true, MaxSize = 4000)] SqlString mailCc,
[SqlFacet(IsNullable = true, MaxSize = 4000)] SqlString blindCopyRec,
[SqlFacet(IsNullable = true, MaxSize = 4000)] SqlString replyAddress,
[SqlFacet(IsNullable = true, MaxSize = 4000)] SqlString fileAttachments,
[SqlFacet(IsNullable = true)] SqlBoolean requestReadReceipt,
[SqlFacet(IsNullable = true)] SqlInt16 deliveryNotification,
[SqlFacet(IsNullable = true)] SqlInt16 sensitivity,
[SqlFacet(IsNullable = true)] SqlInt16 mailPriorty,
[SqlFacet(IsNullable = true)] SqlBoolean bodyHtml,
[SqlFacet(IsNullable = true, MaxSize = 20)] SqlString configName)
{
SysProfile MailCLRClient = null;
MailMessage eMail = null;
SysConfig sc = null;
string validAttachment = string.Empty;
try
{
//Check parameters
if (profileName.IsNull || profileName.Value.Trim() == string.Empty)
{
SqlContext.Pipe.Send("Parameter @profileName must be specified !");
return;
}
if (mailTo.IsNull || mailTo.Value.Trim() == string.Empty)
{
SqlContext.Pipe.Send("Parametar @mailTo must be specified !");
return;
}
if (mailBody.IsNull || mailBody.Value.Trim() == string.Empty)
{
SqlContext.Pipe.Send("Parametar @mailBody must be specified !");
return;
}
string error = "";
if (configName.IsNull || configName.Value.Trim() == string.Empty)
{
sc = new SysConfig();
}
else
{
sc = GetConfig(configName.Value, ref error);
if (sc == null)
{
LogEntry.LogItem("Configuration is not defined !", "Warning", "There is an error when trying to determine config : " + error, "Config name : " + configName.Value);
SqlContext.Pipe.Send("Configuration is not defined !" + error != "" ? "There is an error when trying to determine config : " + error : "");
return;
}
}
MailCLRClient = GetClient(profileName.Value, ref error);
if (MailCLRClient == null)
{
LogEntry.LogItem("Profile is not defined !", "Warning", "There is an error when trying to determine profile : " + error, "Profile name : " + profileName.Value);
SqlContext.Pipe.Send("Profile is not defined !" + error != "" ? "There is an error when trying to determine profile : " + error : "");
return;
}
eMail = ConstructEmailMessage(
sc,
mailTo.Value.ToString(),
mailSubject.IsNull ? "SQLCLR Server Message" : mailSubject.Value.ToString(),
mailBody.IsNull ? String.Empty : mailBody.Value.ToString(),
DetermineFromAddress(fromAddress.IsNull ? string.Empty : fromAddress.Value.ToString(), displayName.IsNull ? string.Empty : displayName.Value.ToString(), MailCLRClient),
mailCc.IsNull ? string.Empty : (string)mailCc.Value,
blindCopyRec.IsNull ? string.Empty : (string)blindCopyRec.Value,
replyAddress.IsNull ? string.Empty : (string)replyAddress.Value,
fileAttachments.IsNull ? string.Empty : (string)fileAttachments.Value,
ref validAttachment,
requestReadReceipt.IsNull ? false : (bool)requestReadReceipt.Value,
sensitivity.IsNull ? -1 : (int)sensitivity.Value,
deliveryNotification.IsNull ? DeliveryNotificationOptions.None : (DeliveryNotificationOptions)Enum.Parse(typeof(DeliveryNotificationOptions), deliveryNotification.Value.ToString()),
mailPriorty.IsNull ? MailPriority.Normal : (MailPriority)Enum.Parse(typeof(MailPriority), mailPriorty.Value.ToString()),
bodyHtml.IsNull ? false : (bool)bodyHtml.Value);
if (MailCLRClient.client.EnableSsl)
{
ServicePointManager.ServerCertificateValidationCallback = (object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) => true;
}
if (sc.sendAsync)
{
MailCLRClient.client.SendAsync(eMail, null);
}
else
{
MailCLRClient.client.Send(eMail);
}
if (sc.noPiping == false)
{
SqlContext.Pipe.Send("Sent successfully!");
}
if (sc.loggingLevel == eLoggingLevel.Maximum)
{
LogEntry.LogItem("SQLCLR sent e-mail", "Information", "E-mail successfully sent!", eMail.HeaderInformation());
}
if (sc.saveEmails)
{
EmailTracker.SaveEmail(eMail, MailCLRClient.name == null ? null : MailCLRClient.name, sc.name == null ? null : sc.name, validAttachment, sc.saveAttachments);
}
}
catch (Exception ex)
{
//if (sc.noPiping == false)
SqlContext.Pipe.Send("There is an error in sending e-mail : " + ex.Message + "\r\n" + ex.InnerException == null ? "" : ex.InnerException.Message);
LogEntry.LogItem("SQLCLR sent e-mail", "Error", "E-mail was not sent!", ex.Message + "\r\n" + ex.InnerException == null ? "" : ex.InnerException.Message);
}
finally
{
eMail = null;
sc = null;
MailCLRClient = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
private static MailAddress DetermineFromAddress(string fromAddress, string displayName, SysProfile sp)
{
MailAddress retValue = null;
if (fromAddress.Equals(string.Empty) == false)
{
if (displayName.Trim() != string.Empty)
{
retValue = new MailAddress(fromAddress, displayName);
}
else
{
retValue = new MailAddress(fromAddress, sp.defaultDisplayName == null ? "DEFAULT GROUP" : sp.defaultDisplayName);
}
}
else
{
if (displayName.Trim() != String.Empty)
{
retValue = new MailAddress(sp.defaultFromAddress, displayName);
}
else
{
retValue = new MailAddress(sp.defaultFromAddress, sp.defaultDisplayName == null ? "DEFAULT GROUP" : sp.defaultDisplayName);
}
}
return(retValue);
}
