//this function sends the cover letter request to the doc group
protected void SendCvrLtrReqMail()
{
string toAddress = "*****@*****.**";
string fromAddress = email.Text.ToString();
string subjectLine = "Cover Letter Request";
string dateNeeded;
//check the date, if less than today, make it today
if (Calendar1.SelectedDate.Date < System.DateTime.Now.Date)
{
Calendar1.SelectedDate = System.DateTime.Now.Date;
}
//test for current date and apply formatting accordingly
if (Calendar1.SelectedDate == System.DateTime.Now.Date)
{
dateNeeded = "<p><font color=\"red\"><b>Date Needed By:</b> " + Calendar1.SelectedDate.ToShortDateString() + "</font></p>";
}
else
{
dateNeeded = "<p><b>Date Needed By:</b> " + Calendar1.SelectedDate.ToShortDateString() + "</p>";
}
//indicate which format to return
bool emailFormat = true;
string emailDetails = fieldNameConversion(emailFormat);
string textBody = "<html><body><font face=\"verdana\"><p><b>Cover Letter Title:</b> " + cvrLtrTitle.Text.ToString() + "</p><p><b>Project:</b> " + drpDwnProject.SelectedItem.ToString() + emailDetails + "</p><p><b>SW Download Instructions:</b> " + swDownloadInst.Text.ToString() + "</p><p><b>HSSub Documents:</b> " + hssubReadme.Text.ToString() + "</p><p><b>Reviewers:</b> " + reviewers.Text.ToString() + dateNeeded + "</p><p><b>Requestor's Email Address:</b> " + email.Text.ToString() + "</p><p>Don't forget to pick up your htm file located here: " + @"\\atddat\DocDept\coverLetters</p></body></html>";
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, fromAddress, subjectLine, textBody);
}
//this function sends an error message to the doc dept if the request is not processed
protected void SendErrorNotice()
{
string toAddress = "*****@*****.**";
string subjectLine = "Error Notice: Media Artwork Request";
string textBody = "There was an error processing the Media Artwork request. Please contact the user at this email address: " + toCC;
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, toCC, fromAddress, subjectLine, textBody);
}
protected void SendErrorNotice()
{
string toAddress = "*****@*****.**";
string toCC = email.Text.ToString();
string subjectLine = "Error Notice: Cover Letter Request";
string textBody = "There was an error processing the cover letter request." + toCC;
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, toCC, fromAddress, subjectLine, textBody);
}
//this funciton sends the artwork request to the doc dept. It gathers the text from the form fields and adds them to the email body.
protected void SendMediaReqMail()
{
string toAddress = "*****@*****.**";
string fromAddress = txtEmail.Text.ToString();
string subjectLine = "Media Artwork Request";
string otherInfo;
string reviewers;
string dateNeeded;
//check the date, if less than today (or not selected), make it today
if (Calendar1.SelectedDate.Date < System.DateTime.Now.Date)
{
Calendar1.SelectedDate = System.DateTime.Now.Date;
}
//test for empty fields in form; don't include in email if empty
if (txtOther.Text.ToString() == "")
{
otherInfo = "";
}
else
{
otherInfo = "<p><b>Other Info:</b> " + txtOther.Text.ToString() + "</p>";
}
if (txtReviewers.Text.ToString() == "")
{
reviewers = "";
}
else
{
reviewers = "<p><b>Reviewers:</b> " + txtReviewers.Text.ToString() + "</p>";
}
//test for current date and change formatting accordingly
if (Calendar1.SelectedDate == System.DateTime.Now.Date)
{
dateNeeded = "<p><font color=\"red\"><b>Date Needed By:</b> " + Calendar1.SelectedDate.ToShortDateString() + "</font></p>";
}
else
{
dateNeeded = "<p><b>Date Needed By:</b> " + Calendar1.SelectedDate.ToShortDateString() + "</p>";
}
string textBody = "<html><body><font face=\"verdana\"><p><b>Media Title:</b> " + txtTitle.Text.ToString() + "</p><p><b>ITAR?</b>: " + radITAR.SelectedValue.ToString() + "</p><p><b>Media Type:</b> " + radMedia.SelectedValue.ToString() + "</p><p><b>Part Number:</b> " + txtPartNum.Text.ToString() + "</p><p><b>Number of Discs:</b> " + dropDwnCDs.SelectedValue.ToString() + "</p><p><b>Project:</b> " + drpDwnProject.SelectedValue.ToString() + otherInfo + reviewers + "</p><p><b>Deliver Media To:</b> " + txtDeliver.Text.ToString() + dateNeeded + "</p><p><b>Requestor's Email Address:</b> " + txtEmail.Text.ToString() + "</p></body></html>";
//send the message
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, fromAddress, subjectLine, textBody);
}
public void UpdateDB(string emailAddr, DateTime dateNeed, int reqType, int day, int project, int discs)
{
string name, fName, lName;
MailAddress addr = new MailAddress(emailAddr);
name = addr.User;
if (name.Contains('.'))
{
//split the name
String[] parts = name.Split(new[] { '.' });
fName = parts[0];
//if there is an initial in the email address, only take last part
if (parts.Length > 2)
{
lName = parts[2];
}
else
{
lName = parts[1];
}
}
else
{
fName = name;
lName = "";
}
DateTime dateReq = System.DateTime.Now;
DateTime dateNeeded = dateNeed;
int requestType = reqType;//1 for cover letters, 0 for artwork
int days = day;
int projType = project;
int numDiscs = discs;
//create the sql query
string txtSqlQuery = @"INSERT INTO workRequest (fName, lName, dateReq, dateNeeded, requestType, daysAllowed, projectID, discsNeeded) VALUES(@fName,@lName,@dateReq,@dateNeeded,@requestType,@daysAllowed,@projectID,@discsNeeded)";
//path and name of sqlite database
const string filename = @"\\atddat\DocDept\SQLiteDB\workRequests.sqlite";
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3;", true))//true so can open over a network
{
SQLiteCommand insertSQL = new SQLiteCommand(txtSqlQuery, connection);
insertSQL.Parameters.AddWithValue("@fName", fName);
insertSQL.Parameters.AddWithValue("@lName", lName);
insertSQL.Parameters.AddWithValue("@dateReq", dateReq);
insertSQL.Parameters.AddWithValue("@dateNeeded", dateNeeded);
insertSQL.Parameters.AddWithValue("@requestType", requestType);
insertSQL.Parameters.AddWithValue("@daysAllowed", days);
insertSQL.Parameters.AddWithValue("@projectID", projType);
insertSQL.Parameters.AddWithValue("@discsNeeded", numDiscs);
try
{
//open the DB connection
connection.Open();
insertSQL.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
//if error notify DBA
string toAddress = "*****@*****.**";
string subjectLine = "Error Notice: Database";
string textBody = "There was an error opening the database.";
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, subjectLine, textBody);
}
}
}
public void UpdateDB(string emailAddr, DateTime dateNeed, int reqType, int day, int project)
{
string name, fName, lName;
//get the data to add to database
//stackoverflow.com/questions/4443225/parse-plain-email-address-into-2-parts#4443326
MailAddress addr = new MailAddress(emailAddr);
name = addr.User;
if (name.Contains('.'))
{
//split the name
String[] parts = name.Split(new[] { '.' });
fName = parts[0];
lName = parts[1];
}
else
{
fName = name;
lName = "";
}
DateTime dateReq = System.DateTime.Now;
DateTime dateNeeded = dateNeed;
int requestType = reqType;//1 for cover letters, 0 for artwork
int days = day;
int projType = project;
//create the sql query
string txtSqlQuery = @"INSERT INTO workRequest (fName, lName, dateReq, dateNeeded, requestType, daysAllowed, projectID) VALUES(@fName,@lName,@dateReq,@dateNeeded,@requestType,@daysAllowed,@projectID)";
//path and name of sqlite database
const string filename = @"\\atddat\DocDept\SQLiteDB\workRequests.sqlite";
//set the connection string
//stackoverflow.com/questions/10875612/sqlite-c-unable-to-open-the-database-file#22328198
//msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3;", true))
{
//stackoverflow.com/questions/19479166/sqlite-simple-insert-query#19489736
//stackoverflow.com/questions/15257369/cannot-implicitly-convert-type-string-to-system-data-commandtype
SQLiteCommand insertSQL = new SQLiteCommand(txtSqlQuery, connection);
insertSQL.Parameters.AddWithValue("@fName", fName);
insertSQL.Parameters.AddWithValue("@lName", lName);
insertSQL.Parameters.AddWithValue("@dateReq", dateReq);
insertSQL.Parameters.AddWithValue("@dateNeeded", dateNeeded);
insertSQL.Parameters.AddWithValue("@requestType", requestType);
insertSQL.Parameters.AddWithValue("@daysAllowed", days);
insertSQL.Parameters.AddWithValue("@projectID", projType);
try
{
//open the DB connection
connection.Open();
insertSQL.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
//if error notify DBA
string toAddress = "*****@*****.**";
string subjectLine = "Error Notice: Database";
string textBody = "There was an error opening the database.";
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, subjectLine, textBody);
}
}
}
public void UpdateFeedbackTable(string emailAddr, int feedbackType, string feedback)
{
string name, fName, lName;
MailAddress addr = new MailAddress(emailAddr);
name = addr.User;
if (name.Contains('.'))
{
//split the name
String[] parts = name.Split(new[] { '.' });
fName = parts[0];
//if there is an initial in the email address, only take last part
if (parts.Length > 2)
{
lName = parts[2];
}
else
{
lName = parts[1];
}
}
else
{
fName = name;
lName = "";
}
DateTime dateReq = System.DateTime.Now;
int type = feedbackType;
string content = feedback;
//create the sql query
string txtSqlQuery = @"INSERT INTO feedback (fName, lName, dateReq, feedbackType, feedbackText) VALUES(@fName,@lName,@dateReq,@feedbackType,@feedbackText)";
//path and name of sqlite database
const string filename = @"\\atddat\DocDept\SQLiteDB\workRequests.sqlite";
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3;", true))//true so can open over a network
{
SQLiteCommand insertSQL = new SQLiteCommand(txtSqlQuery, connection);
insertSQL.Parameters.AddWithValue("@fName", fName);
insertSQL.Parameters.AddWithValue("@lName", lName);
insertSQL.Parameters.AddWithValue("@dateReq", dateReq);
insertSQL.Parameters.AddWithValue("@feedbackType", type);
insertSQL.Parameters.AddWithValue("@feedbackText", content);
try
{
//open the DB connection
connection.Open();
insertSQL.ExecuteNonQuery();
connection.Close();
}
catch (Exception)
{
//if error notify DBA
string toAddress = "*****@*****.**";
string subjectLine = "Error Notice: Database";
string textBody = "There was an error opening the database.";
//send the email
ProcessMail myMail = new ProcessMail();
myMail.SendMail(toAddress, subjectLine, textBody);
}
}
}
