protected void btnSave_Click(object sender, EventArgs e)
{
DatabaseInfo dbInfo = new DatabaseInfo();
dbInfo.Name = "RoadbookDB";
dbInfo.Ip = "127.0.0.1";
dbInfo.Port = 1433;
dbInfo.UserId = "sa";
dbInfo.UserPassword = "******";
MsSqlManager ms = new MsSqlManager();
ms.Open(dbInfo);
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" INSERT TB_CONTENTS ( TITLE, SUMMARY, CREATE_DT, CREATE_USER_NM, TAGS, CATEGORY_IDX ) ");
sbSQL.Append(
string.Format(" VALUES( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}' )",
txtTitle.Text,
txtSummary.Text,
DateTime.Now.ToString("yyyy-MM-dd"),
txtUserNm.Text,
txtTags.Text,
2
)
);
ms.Insert(sbSQL.ToString());
Response.Redirect("Default.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
DatabaseInfo dbInfo = new DatabaseInfo();
dbInfo.Name = "RoadbookDB";
dbInfo.Ip = "127.0.0.1";
dbInfo.Port = 1433;
dbInfo.UserId = "sa";
dbInfo.UserPassword = "******";
MsSqlManager ms = new MsSqlManager();
ms.Open(dbInfo);
DataTable dt = ms.Select("SELECT IDX, TITLE, SUMMARY, CREATE_DT, CREATE_USER_NM, TAGS, LIKE_CNT, CATEGORY_IDX FROM TB_CONTENTS");
GridView1.DataSource = dt;
GridView1.DataBind();
}
private static Boolean AutodiscoverUrl(ExchangeService service, Office365User user)
{
Boolean isSuccess = false;
try
{
LoggingManager.WriteToLog("Exchange Online", "Started", string.Empty);
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Exchange Online", "Started", "Exchange Online started"));
service.AutodiscoverUrl(user.Account, CallbackMethods.RedirectionUrlValidationCallback);
LoggingManager.WriteToLog("Exchange Online", "Connected", string.Empty);
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Exchange Online", "Connected", "Exchange Online connected"));
isSuccess = true;
}
catch (Exception e)
{
LoggingManager.WriteToLog("AutodiscoverUrl", "Exception", e.Message);
Console.WriteLine(e.Message);
}
return(isSuccess);
}
private static void ProcessEmailAsInvalid(EmailMessage email, string mailboxMonitorAddress, FolderId invalidMailFolderId)
{
//forward for debugging
LoggingManager.WriteToLog("Processing Mail", "Forwarding with body", "Invalid");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Invalid"));
email.Forward(new MessageBody(BodyType.HTML, "INVALID?"), new EmailAddress[1] {
mailboxMonitorAddress
});
LoggingManager.WriteToLog("Processing Mail", "Forwarded with body", "Invalid");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Invalid"));
email.IsRead = true;
email.Update(ConflictResolutionMode.AlwaysOverwrite);
LoggingManager.WriteToLog("Processing Mail", "Email marked as", "Unread");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Marked as", "Unread"));
//move mail to processed folder
email.Move(invalidMailFolderId);
LoggingManager.WriteToLog("Processing Mail", "Email moved to", "Invalid");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email moved to", "Invalid"));
}
private void SetMsSalManager()
{
_ms = new MsSqlManager();
}
public void Run()
{
DatabaseInfo dbInfo = new DatabaseInfo();
dbInfo.Name = "RoadbookDB";
dbInfo.Ip = "127.0.0.1";
dbInfo.Port = 1433;
dbInfo.UserId = "sa";
dbInfo.UserPassword = "******";
MsSqlManager ms = new MsSqlManager();
ms.Open(dbInfo);
StringBuilder sbMessage = new StringBuilder();
sbMessage.AppendLine("******************************");
sbMessage.AppendLine("1. SELECT");
sbMessage.AppendLine("2. INSERT");
sbMessage.AppendLine("3. UPDATE");
sbMessage.AppendLine("4. DELETE");
sbMessage.AppendLine("0. QUIT");
sbMessage.AppendLine("******************************");
while (true)
{
Console.WriteLine(sbMessage.ToString());
string input = Console.ReadLine();
if (input == "0")
{
ms.Close();
Console.WriteLine("BYE!!");
break;
}
else
{
string index = string.Empty;
string title = string.Empty;
string summary = string.Empty;
string createUserNm = string.Empty;
string tags = string.Empty;
string createDate = string.Empty;
StringBuilder sbSQL = new StringBuilder();
switch (input)
{
case "1": // SELECT
DataTable dt = ms.Select("SELECT IDX, TITLE, SUMMARY, CREATE_DT, CREATE_USER_NM, TAGS, LIKE_CNT, CATEGORY_IDX FROM TB_CONTENTS");
if (dt.Rows.Count > 0)
{
string[] columns = new string[dt.Columns.Count];
for (int idx = 0; idx < dt.Columns.Count; idx++)
{
columns[idx] = dt.Columns[idx].ToString();
Console.Write(dt.Columns[idx] + "\t");
}
Console.WriteLine();
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
for (int idx_j = 0; idx_j < dt.Columns.Count; idx_j++)
{
Console.Write(dt.Rows[idx][columns[idx_j]] + "\t");
}
Console.WriteLine();
}
}
else
{
Console.WriteLine("No Data!!");
}
break;
case "2": // INSERT
Console.Write("TITLE : ");
title = Console.ReadLine();
Console.Write("SUMMARY : ");
summary = Console.ReadLine();
Console.Write("CREATE_USER_NM : ");
createUserNm = Console.ReadLine();
Console.Write("TAGS : ");
tags = Console.ReadLine();
createDate = DateTime.Now.ToString("yyyy-MM-dd");
sbSQL.Append(" INSERT TB_CONTENTS ( TITLE, SUMMARY, CREATE_DT, CREATE_USER_NM, TAGS, CATEGORY_IDX ) ");
sbSQL.Append(
string.Format(" VALUES( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}' )",
title, summary, createDate, createUserNm, tags, 2
)
);
ms.Insert(sbSQL.ToString());
break;
case "3": // UPDATE
ms.Open(dbInfo);
Console.Write("Changed IDX : ");
index = Console.ReadLine();
Console.Write("TITLE : ");
title = Console.ReadLine();
Console.Write("SUMMARY : ");
summary = Console.ReadLine();
sbSQL.Append(" UPDATE TB_CONTENTS SET ");
sbSQL.Append(
string.Format(" TITLE = '{0}', SUMMARY = '{1}' ",
title, summary
)
);
sbSQL.Append(
string.Format(" WHERE IDX = {0}",
index
)
);
ms.Update(sbSQL.ToString());
break;
case "4": // DELETE
ms.Open(dbInfo);
Console.Write("DELETED IDX : ");
index = Console.ReadLine();
sbSQL.Append(" DELETE FROM TB_CONTENTS ");
sbSQL.Append(
string.Format(" WHERE IDX = {0}",
index
)
);
ms.Update(sbSQL.ToString());
break;
default:
Console.WriteLine("Invalid");
break;
}
}
}
}
internal static void Start(string[] args)
{
try
{
var applicationSettings = new Properties.Settings();
ServicePointManager.ServerCertificateValidationCallback = CallbackMethods.CertificateValidationCallBack;
ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2013_SP1);
// Get the information of the account.
var user = new Office365User();
service.Credentials = new WebCredentials(user.Account, user.Pwd);
// Set the url of server.
if (!AutodiscoverUrl(service, user))
{
return;
}
var mailboxMonitorAddress = applicationSettings.Mailbox_Monitor;
var moveMailFolderId = GetFolderId(service, "Processed");
var invalidMailFolderId = GetFolderId(service, "Invalid");
var folderView = new FolderView(2);
folderView.PropertySet = new PropertySet(BasePropertySet.IdOnly);
folderView.Traversal = FolderTraversal.Deep;
//var inboxFilter = new SearchFilter.IsEqualTo(FolderSchema.DisplayName, "Test");
//var inboxResults = service.FindFolders(WellKnownFolderName.Root, inboxFilter, folderView);
// var inboxResults = service.FindFolders(WellKnownFolderName.Inbox, folderView);
// if (inboxResults.TotalCount > 0)
// {
Folder inbox = Folder.Bind(service, WellKnownFolderName.Inbox);
// var inboxId = inboxResults.Folders[0].Id;
Folder inboxFolder = Folder.Bind(service, inbox.Id);
if (inboxFolder.UnreadCount > 0)
{
ItemView unreadView = new ItemView(inboxFolder.UnreadCount);
unreadView.PropertySet = PropertySet.IdOnly;
FindItemsResults <Item> results = service.FindItems(inbox.Id, unreadView);
foreach (Item item in results.Items)
{
EmailMessage email = EmailMessage.Bind(service, new ItemId(item.Id.UniqueId.ToString()));
if (!email.IsRead)
{
PropertySet ps = new PropertySet(ItemSchema.MimeContent, ItemSchema.Body, EmailMessageSchema.Sender, EmailMessageSchema.IsRead, ItemSchema.DateTimeReceived, ItemSchema.Attachments);
email.Load(ps);
var savedMySQLMessageId = MsSqlManager.SaveRAWMessage(email);
var processedPlainTextBody = ProcessEmailBodyText(email, mailboxMonitorAddress, moveMailFolderId, savedMySQLMessageId);
if (!processedPlainTextBody)
{
var processedAttachment = ProcessEmailAttachments(email, mailboxMonitorAddress, moveMailFolderId, savedMySQLMessageId);
if (!processedAttachment)
{
ProcessEmailAsInvalid(email, mailboxMonitorAddress, invalidMailFolderId);
}
}
}
}
}
// }
}
catch (Exception exc)
{
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString(), "Start", "Exception", exc.StackTrace));
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Exchange Online", "Exception", exc.StackTrace));
}
}
private static bool ProcessEmailAttachments(EmailMessage email, string mailboxMonitorAddress, FolderId moveMailFolderId, long savedMySQLMessageId)
{
if (email.Attachments.Count > 0)
{
foreach (var emailAttachment in email.Attachments.Where(a => a.Name.ToLower().EndsWith("lic")))
{
if (emailAttachment is FileAttachment)
{
var emailFileAttachment = emailAttachment as FileAttachment;
emailFileAttachment.Load();
var utfDecodedContent = Encoding.UTF8.GetString(emailFileAttachment.Content);
if (utfDecodedContent.Contains("---"))
{
Atum.DAL.Managers.LoggingManager.WriteToLog("Processing Mail", "From", email.Sender.Address);
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "From", email.Sender.Address));
Atum.DAL.Managers.LoggingManager.WriteToLog("Processing Mail", "Start of license block", "Found");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Start of license block", "Found"));
var activatedLicenseFile = Atum.DAL.Licenses.OnlineCatalogLicenses.FromLicenseStream(utfDecodedContent);
if (activatedLicenseFile.Count == 1)
{
LoggingManager.WriteToLog("Processing Mail", "License Object", "Processed");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "License Object", "Processed"));
//debug (use forwarding to monitor registration process)
email.Forward(new MessageBody(BodyType.HTML, "Done?"), new EmailAddress[1] {
mailboxMonitorAddress
});
LoggingManager.WriteToLog("Processing Mail", "Forwarding with body", "Done");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Done"));
//prepare
var emailReply = new StringBuilder();
if (email.Body.BodyType == BodyType.HTML)
{
emailReply.AppendLine("Dear customer, <br/><br/>Thank you for requesting an activation code.<br/><br/>");
}
else
{
emailReply.AppendLine("Dear customer,");
emailReply.AppendLine("Thank you for requesting an activation code.");
emailReply.AppendLine();
emailReply.AppendLine();
}
//activate license
activatedLicenseFile[0].Activated = true;
activatedLicenseFile[0].ExpirationDate = DateTime.Now.AddYears(1);
activatedLicenseFile[0].ActivationDate = DateTime.Now;
activatedLicenseFile[0].LicenseType = Atum.DAL.Licenses.AvailableLicense.TypeOfLicense.StudioStandard;
var emailFileAttachmentName = emailFileAttachment.Name;
var emailFileAttachmentNameActivated = emailFileAttachment.Name.Substring(0, emailFileAttachment.Name.LastIndexOf("."));
emailFileAttachmentNameActivated += "-activated.lic";
var replyMessage = email.CreateReply(true);
replyMessage.BodyPrefix = emailReply.ToString();
EmailMessage replyEmailMessage = replyMessage.Save();
replyEmailMessage.Attachments.AddFileAttachment(emailFileAttachmentNameActivated, Encoding.UTF8.GetBytes(activatedLicenseFile.ToLicenseRequest()));
replyEmailMessage.Update(ConflictResolutionMode.AutoResolve);
replyEmailMessage.Send();
LoggingManager.WriteToLog("Processing Mail", "Forwarded with body", "Done");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Done"));
//mark as read
email.IsRead = true;
email.Update(ConflictResolutionMode.AlwaysOverwrite);
LoggingManager.WriteToLog("Processing Mail", "Email marked as", "Unread");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email marked as", "Unread"));
//save to database
MsSqlManager.UpdateRAWMessageWithActivationCode(savedMySQLMessageId, activatedLicenseFile);
//move mail to processed folder
email.Move(moveMailFolderId);
LoggingManager.WriteToLog("Processing Mail", "Email moved to", "Processed");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email moved to", "Processed"));
//save to path
var atumLicenseFile = new AtumEmailLicenseRequest(email.Sender.Address, email.DateTimeReceived, activatedLicenseFile[0]);
atumLicenseFile.Save();
LoggingManager.WriteToLog("Processing Mail", "Email moved to", "Processed");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email moved to", "Processed"));
}
}
}
}
return(true);
}
return(false);
}
private static bool ProcessEmailBodyText(EmailMessage email, string mailboxMonitorAddress, FolderId moveMailFolderId, long savedMySQLMessageId)
{
if (email.Body.Text.Contains("---StartOfLicenseCode---"))
{
Atum.DAL.Managers.LoggingManager.WriteToLog("Processing Mail", "From", email.Sender.Address);
Atum.DAL.Managers.LoggingManager.WriteToLog("Processing Mail", "Start of license block", "Found");
var emailBodyAsHtml = email.Body.Text;
var emailBodyText = emailBodyAsHtml.Substring(emailBodyAsHtml.IndexOf("---StartOfLicenseCode---"));
var endLicenseTag = "---EndofLicenseCode---";
emailBodyText = emailBodyText.Substring(0, emailBodyText.IndexOf(endLicenseTag) + endLicenseTag.Length);
emailBodyText = Regex.Replace(emailBodyText, "<.*?>", string.Empty); //strip HTML tags
emailBodyText = WebUtility.HtmlDecode(emailBodyText); //convert html encoding to text encoding
var licenseFile = Atum.DAL.Licenses.OnlineCatalogLicenses.FromLicenseStream(emailBodyText);
if (licenseFile.Count == 1)
{
LoggingManager.WriteToLog("Processing Mail", "License Object", "Processed");
//debug (use forwarding to monitor registration process)
email.Forward(new MessageBody(BodyType.HTML, "Done?"), new EmailAddress[1] {
mailboxMonitorAddress
});
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Done"));
LoggingManager.WriteToLog("Processing Mail", "Forwarding with body", "Done");
//preprare
var emailReply = new StringBuilder();
if (email.Body.BodyType == BodyType.HTML)
{
emailReply.AppendLine("Dear customer, <br/><br/>Thank you for requesting an activation code.<br/><br/>");
}
else
{
emailReply.AppendLine("Dear customer,");
emailReply.AppendLine("Thank you for requesting an activation code.");
emailReply.AppendLine();
emailReply.AppendLine();
}
//activate license
licenseFile[0].Activated = true;
licenseFile[0].ExpirationDate = DateTime.Now.AddYears(1);
licenseFile[0].ActivationDate = DateTime.Now;
licenseFile[0].LicenseType = Atum.DAL.Licenses.AvailableLicense.TypeOfLicense.StudioStandard;
emailReply.AppendLine(licenseFile.ToLicenseRequest());
email.Reply(new MessageBody(email.Body.BodyType, emailReply.ToString()), true);
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Forwarding with body", "Done"));
LoggingManager.WriteToLog("Processing Mail", "Forwarded with body", "Done");
//mark as read
email.IsRead = true;
email.Update(ConflictResolutionMode.AlwaysOverwrite);
LoggingManager.WriteToLog("Processing Mail", "Email marked as", "Unread");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email marked as", "Unread"));
//save to database
MsSqlManager.UpdateRAWMessageWithActivationCode(savedMySQLMessageId, licenseFile);
//move mail to processed folder
email.Move(moveMailFolderId);
LoggingManager.WriteToLog("Processing Mail", "Email moved to", "Processed");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email moved to", "Processed"));
//save to path
var atumLicenseFile = new AtumEmailLicenseRequest(email.Sender.Address, email.DateTimeReceived, licenseFile[0]);
atumLicenseFile.Save();
LoggingManager.WriteToLog("Processing Mail", "Email moved to", "Processed");
MsSqlManager.SaveLogging(new DAL.Logging.LoggingInfo(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "Processing Mail", "Email moved to", "Processed"));
}
return(true);
}
return(false);
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateProductsFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var products = oracleContext.PRODUCTS
.Where(p => p.ISCOPIED == false && p.ISDELETED == false)
.Select(p => new
{
p.PRODUCTNAME,
p.PRICE,
p.VENDOR.VENDORNAME,
p.MEASURE.MEASURENAME,
p.PRODUCTSTYPE.TYPENAME
}).ToList();
if (products.Count > 0)
{
var addedProductsList = new List <string>();
foreach (var product in products)
{
var productName = product.PRODUCTNAME;
var price = product.PRICE;
var vendorId = MsSqlManager.GetVendorIdByName(product.VENDORNAME);
var measureId = MsSqlManager.GetMeasureIdByName(product.MEASURENAME);
var typeId = MsSqlManager.GetTypeIdByName(product.TYPENAME);
try
{
msSqLcontext.Products.AddOrUpdate(
p => p.ProductName,
new Product()
{
ProductName = productName,
VendorId = vendorId,
MeasureId = measureId,
ProductTypeId = typeId,
Price = (float)price
});
msSqLcontext.SaveChanges();
addedProductsList.Add(productName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var productsToChange =
oracleContext.PRODUCTS.Where(p => addedProductsList.Contains(p.PRODUCTNAME)).ToList();
productsToChange.ForEach(p => p.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Products from OracleBD into MS SQL Server:");
productsToChange.ForEach(p => Console.WriteLine("Added product name: {0}", p.PRODUCTNAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into PRODUCTS table!");
}
}
