static void Main(string[] args)
{
// Hold the command line argument for RptFreq
List <string> sArgs = new List <string>();
string brh = string.Empty;
// Hold messages for the log file
List <string> logMsgs = new List <string>();
// Get command line arguments
try
{
if (args.Length > 0)
{
brh = args[0].ToString();
}
else
{
throw new Exception("No args in command line");
}
}
catch (Exception ex)
{
logMsgs.Add("Arg Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Inserting values into Excel will just append to the file.
* Need to delete the email version of Daily.xls, then
* replace with a template copy before inserting values
*/
string fileName = "Daily.xls";
string templatePath = @"c:\FilesToEmail\Templates";
string destPath = @"c:\FilesToEmail\Daily";
//File.Copy true will overwrite existing file at desination
File.Copy(Path.Combine(templatePath, fileName), Path.Combine(destPath, fileName), true);
/*
* Get Emails for a Brh
*/
ReportsBLL objEmails = new ReportsBLL();
List <string> eList = new List <string>();
try
{
eList = objEmails.Get_Emails_ByBrh(brh);
}
catch (Exception ex)
{
logMsgs.Add("Get_Emails_ByBrh Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Getting MTY Bookings
*/
ReportsBLL objBookings = new ReportsBLL();
List <BookingsModel> bookList = new List <BookingsModel>();
try
{
bookList = objBookings.Get_Bookings_MTY();
}
catch (Exception ex)
{
logMsgs.Add("Get_Employees_Reports Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Get PWC by Brh
*/
ReportsBLL objPWC = new ReportsBLL();
List <string> pwcList = new List <string>();
try
{
pwcList = objPWC.Get_PWC_ByBrh("ST");
}
catch (Exception ex)
{
logMsgs.Add("Get_PWC_ByBrh Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Get production for each PWC and the SLT combined PWC
* NOT DOING SLT RIGHT NOW
*/
ReportsBLL objProd = new ReportsBLL();
List <ProdModel> pList60 = new List <ProdModel>();
try
{
pList60 = objProd.Get_Prod_ByPWC("60S");
}
catch (Exception ex)
{
logMsgs.Add("Get_Prod_ByPWC(60S) Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
List <ProdModel> pList72 = new List <ProdModel>();
try
{
pList72 = objProd.Get_Prod_ByPWC("72S");
}
catch (Exception ex)
{
logMsgs.Add("Get_Prod_ByPWC(72S) Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
List <ProdModel> pListCTL = new List <ProdModel>();
try
{
pListCTL = objProd.Get_Prod_ByPWC("CTL");
}
catch (Exception ex)
{
logMsgs.Add("Get_Prod_ByPWC(CTL) Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
List <ProdModel> pListMSB = new List <ProdModel>();
try
{
pListMSB = objProd.Get_Prod_ByPWC("MSB");
}
catch (Exception ex)
{
logMsgs.Add("Get_Prod_ByPWC(MSB) Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Insert data into Daily.xls
*/
// initialize text used in OleDbCommand
string cmdText = "";
string excelConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(destPath, fileName) + @";Extended Properties=""Excel 8.0;HDR=YES;""";
using (OleDbConnection eConn = new OleDbConnection(excelConnString))
{
try
{
eConn.Open();
OleDbCommand eCmd = new OleDbCommand();
eCmd.Connection = eConn;
/*
* Tried to build the whole string outside the eConn using block, but could not get past a ; missing error
* when inserting more than one set of values in the SQL statement.
*
* Interate the list of Booking value and insert each set one-at-a-time
*/
// Insert Bookings into Excel
foreach (BookingsModel b in bookList)
{
cmdText = "Insert into [Book$] (WORK_DY,BOOK_DT,SW_DLY,MS_DLY,SW_AVE,MS_AVE) Values(" + b.workDy.ToString() + "," + b.prodDt.ToString() + "," + b.swDly.ToString() + "," + b.msDly.ToString() + "," + b.swAve.ToString() + "," + b.msAve.ToString() + ");";
eCmd.CommandText = cmdText;
Console.WriteLine(cmdText);
eCmd.ExecuteNonQuery();
}
/*
* Instead of hard coding each PWC, try:
* 1. Loop through PWC
* 2. cmdText tab is current PWC
* 3. Reuse <List> instead of individial for each PWC
* 4. Use parameters for the OleDb Cmd
*/
/*
* Insert production for each PWC in a specific tab
*/
foreach (ProdModel p in pList60)
{
// PWC is a string so it needs double single quotes.
// Do this by adding a "'" on both sides of the property
cmdText = @"Insert into [60S$] (WORK_DY,PROD_DT,PWC,JOBS,LBS,BRKS,SETUPS) Values(" + p.workDy.ToString() + "," + p.prodDt.ToString() + "," + "'" + p.pwc + "'" + "," + p.jobs.ToString() + "," + p.lbs.ToString() + "," + p.brks.ToString() + "," + p.setUps.ToString() + ");";
eCmd.CommandText = cmdText;
Console.WriteLine(cmdText);
eCmd.ExecuteNonQuery();
}
foreach (ProdModel p in pList72)
{
cmdText = @"Insert into [72S$] (WORK_DY,PROD_DT,PWC,JOBS,LBS,BRKS,SETUPS) Values(" + p.workDy.ToString() + "," + p.prodDt.ToString() + "," + "'" + p.pwc + "'" + "," + p.jobs.ToString() + "," + p.lbs.ToString() + "," + p.brks.ToString() + "," + p.setUps.ToString() + ");";
eCmd.CommandText = cmdText;
Console.WriteLine(cmdText);
eCmd.ExecuteNonQuery();
}
foreach (ProdModel p in pListCTL)
{
cmdText = @"Insert into [CTL$] (WORK_DY,PROD_DT,PWC,JOBS,LBS,BRKS,SETUPS) Values(" + p.workDy.ToString() + "," + p.prodDt.ToString() + "," + "'" + p.pwc + "'" + "," + p.jobs.ToString() + "," + p.lbs.ToString() + "," + p.brks.ToString() + "," + p.setUps.ToString() + ");";
eCmd.CommandText = cmdText;
Console.WriteLine(cmdText);
eCmd.ExecuteNonQuery();
}
foreach (ProdModel p in pListMSB)
{
cmdText = @"Insert into [MSB$] (WORK_DY,PROD_DT,PWC,JOBS,LBS,BRKS,SETUPS) Values(" + p.workDy.ToString() + "," + p.prodDt.ToString() + "," + "'" + p.pwc + "'" + "," + p.jobs.ToString() + "," + p.lbs.ToString() + "," + p.brks.ToString() + "," + p.setUps.ToString() + ");";
eCmd.CommandText = cmdText;
Console.WriteLine(cmdText);
eCmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
logMsgs.Add("OleDb Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
Console.WriteLine(ex.Message.ToString());
}
}
//Email Daily.xls
try
{
MailMessage mail = new MailMessage();
//SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
SmtpClient SmtpServer = new SmtpClient("smtp.office365.com");
//mail.From = new MailAddress("*****@*****.**");
mail.From = new MailAddress("*****@*****.**");
mail.Subject = "Daily Reports";
mail.Body = "Report attached";
//Build To: line from List of emails
foreach (string e in eList)
{
logMsgs.Add(e);
mail.To.Add(e);
}
// Add attachment
Attachment attach;
//attach = new Attachment("c:\\FilesToEmail\\Daily.xls");
attach = new Attachment(Path.Combine(destPath, fileName));
mail.Attachments.Add(attach);
SmtpServer.Port = 587;
//SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "A8dg2h8q");
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "Smet@524");
SmtpServer.EnableSsl = true;
//SmtpServer.Send(mail);
Logger.Log(logMsgs);
}
catch (Exception ex)
{
logMsgs.Add("Mail Exception:");
logMsgs.Add(ex.ToString());
Logger.Log(logMsgs);
}
// testing only to stop application so I can read the console
Console.WriteLine("Press key to exit");
Console.ReadKey();
}