public ActionResult ShowItemType(string ItemTypeDescription)
{
//get default view
if (ItemTypeDescription == null)
{
ItemTypeDescription = "Caribbean Line";
}
DataSet db = new ReportsBLL().GetProductByProductCategory_BLL(ItemTypeDescription);
return(PartialView(db));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
Cache["database"] = new ReportsBLL().GetProduct_BLL(null);
productsgrid.DataSource = Cache["database"];
productsgrid.DataBind();
categoryRpt.DataSource = new ReportsBLL().GetProductCats_BLL();
categoryRpt.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
Cache["dateNum"] = 0;
Cache["custID"] = -1;
Cache["custName"] = "";
Cache["manName"] = "";
String[] temp = new ReportsBLL().getCustNames("").Tables[0].AsEnumerable().Select(x => x.Field <string>("CustName")).ToArray();
foreach (string item in temp)
{
manager.Items.Add(new ListItem(item, item));
}
updateGrid();
}
}
private void QueryBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
this.Invoke(new Action(() =>
{
buttonBuscar.Enabled = false;
loaderControl1.title = "Por favor espere";
loaderControl1.description = "Obteniendo información";
loaderControl1.Visible = true;
}
));
//System.Threading.Thread.Sleep(3000);
ReportsBLL ReportsBLL = new ReportsBLL();
this.Invoke(new Action(() => dataGridViewReporteGeneral.DataSource = ReportsBLL.ReportHoursJob(dateTimeFechaInicio.Value, dateTimeFechaFin.Value, Convert.ToInt32(comboBoxTurno.SelectedValue.ToString()), Convert.ToInt32(comboBoxDepartamento.SelectedValue.ToString()), Convert.ToInt32(comboBoxEmpleado.SelectedValue.ToString()))));
}
public ActionResult SubmitLogin(UserAccount obj)
{
if (ModelState.IsValid)
{
ReportsBLL Bll = new ReportsBLL();
var isValid = Bll.IsValidUser(obj.UserID, obj.Password);
if (isValid)
{
Session["UserId"] = obj.UserID;
FormsAuthentication.SetAuthCookie(obj.UserID, obj.RememberMe);
//use RememberMe checkbox to decide if saving UserID or not
if (obj.RememberMe)
{
HttpCookie cookie = new HttpCookie("Login");
cookie.Values.Add("UserID", obj.UserID);
cookie.Expires = DateTime.Now.AddDays(15);
Response.Cookies.Add(cookie);
}
else
{
HttpCookie cookie = new HttpCookie("Login");
cookie.Expires = DateTime.Now.AddDays(-1);
Response.Cookies.Add(cookie);
}
return(RedirectToAction("Product", "Product"));
}
ViewData["ErrorMessage"] = "UserID or Password is incorrect";
return(View("Login"));
}
else
{
return(View("Login"));
}
}
public byte[] ExportEmployeePostReportInfos(List<string> companyids, string sort, string filterString, IList<object> paras, string userID, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeePostReportInfos(companyids, sort, filterString, paras, userID, DtStart, DtEnd);
}
}
public List<V_EmployeeChangeInfos> EmployeePostReportInfos(List<string> companyids, string sort, string filterString, IList<object> paras, string userID, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
IQueryable<V_EmployeeChangeInfos> q = bll.EmployeePostReportInfos(companyids, sort, filterString, paras, userID, DtStart, DtEnd);
return q != null ? q.ToList() : null;
}
}
public byte[] ExportEmployeeBasicInfosNoGetReports(List<V_EmployeeBasicInfo> ListInfos, DateTime Dt, List<string> companyids)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportGetEmployeeBasicInfosByCompany(ListInfos, Dt, companyids);
}
}
public byte[] ExportEmployeeBasicInfosByCompanyReports(List<string> companyids, string sort, string filterString, object[] paras, string userID, string IsType, DateTime start, DateTime end)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportGetEmployeeBasicInfosByCompany(companyids, sort, filterString, paras, userID, IsType, start, end);
}
}
public byte[] ExportEmployeeCollectsReports(string sort, string filterString, object[] paras, string userID, string CompanyID, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeesCollectReports(sort, filterString, paras, userID, CompanyID, DtStart, DtEnd);
}
}
public byte[] ExportEmployeeLeftOfficeConfirmReports(List<string> companyids, string sort, string filterString, IList<object> paras, string userID, string IsType, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeeLeftOfficeConfirmReports(companyids, sort, filterString, paras, userID, IsType, DtStart, DtEnd);
}
}
public void LoadDataGridView()
{
ReportsBLL ReportsBLL = new ReportsBLL();
dataGridViewReporteGeneral.DataSource = ReportsBLL.ReportAbsenteeism(dateTimeFechaInicio.Value, dateTimeFechaFin.Value, Convert.ToInt32(comboBoxDepartamento.SelectedValue.ToString()), Convert.ToInt32(comboBoxEmpleado.SelectedValue.ToString()));
}
public ActionResult LarcaNewsProcess(Larca2.Views.ViewModels.LAScopeViewModel model, string command, string txtType)
{
var smo = model.smo;
var rbu = model.rbu;
var type = txtType;
var masterRows = new List <ReportRow>();
switch (command)
{
case "Filter by BU":
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
break;
case "Filter by SMO":
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
}
break;
}
if (command.Equals("Export Excel"))
{
if (rbu != "0")
{
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
}
else
{
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
}
}
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
// Download
DownloadFile(file);
}
else
{
if (command.Equals("Send Email"))
{
string datafin = "";
if (rbu != "0")
{
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
datafin = rbu;
}
else
{
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
datafin = smo;
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
datafin = smo;
}
}
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, datafin);
ViewBag.Result = "File Exported and sent!.";
}
else
{
if (command.Equals("Send All Reports"))
{
foreach (var item in model.SMOList)
{
if (item.Text == "LA")
{
item.Value = "1";
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, item.Value);
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, item.Text);
}
else
{
masterRows = new ReportsBLL().LARCANews(item.Value, string.Empty, string.Empty);
var file2 = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file2, item.Text);
}
}
foreach (var item in model.BUList)
{
masterRows = new ReportsBLL().LARCANews(string.Empty, item.Value, string.Empty);
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, item.Text);
}
ViewBag.Result = "Multiple Files Exported and sent!.";
}
}
}
ViewData["MasterRows"] = masterRows;
return(View("Index", model));
}
private int writeToExcel(string type, int startRow, ExcelWorksheet ws, string salesId)
{
DataTable dt = new DataTable();
int result = startRow;
List <string> titles = new List <string>();
ws.Cells.Style.Font.Size = 11; // Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; // Default Font name for whole sheet
var reportYear = Convert.ToInt16(lbReportYear.Content);
var reportMonth = Convert.ToInt16(lbReportMonth.Content);
var startYearMonth1 = (reportYear - 1).ToString() + "/07";
var currentYearMonth1 = reportMonth >= 7 ? (reportYear - 1).ToString() + "/" + reportMonth.ToString() : (reportYear).ToString() + "/" + reportMonth.ToString();
var startYearMonth2 = (reportYear - 2).ToString() + "/07";
var currentYearMonth2 = reportMonth >= 7 ? (reportYear - 2).ToString() + "/" + reportMonth.ToString() : (reportYear - 1).ToString() + "/" + reportMonth.ToString();
var startYearMonth3 = (reportYear - 3).ToString() + "/07";
var currentYearMonth3 = reportMonth >= 7 ? (reportYear - 3).ToString() + "/" + reportMonth.ToString() : (reportYear - 2).ToString() + "/" + reportMonth.ToString();
// Set Title for diffrent table
switch (type)
{
case "s1t1":
case "s1t3":
ws.Name = "年度比較"; // Setting Sheet's name
titles.Add("業務員");
titles.Add(reportYear.ToString() + "年度" + "(" + startYearMonth1 + "~" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "年度" + "(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度" + "(" + startYearMonth2 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "成長率");
titles.Add((reportYear - 2).ToString() + "v.s" + reportYear.ToString() + "成長率");
if (type == "s1t1")
{
ws.Cells[startRow, 1].Value = "同期間比較(已銷+未出)";
dt = ReportsBLL.getSalesReportS1T1();
}
else
{
ws.Cells[startRow, 1].Value = "同期間比較(已銷不含未出)";
dt = ReportsBLL.getSalesReportS1T3();
}
break;
case "s1t2":
titles.Add("業務員");
titles.Add(reportYear.ToString() + "年度已銷+未出(累計至" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "銷貨金額");
titles.Add((reportYear - 2).ToString() + "銷貨金額");
titles.Add(reportYear.ToString() + "達成率");
titles.Add(reportYear.ToString() + "預估");
titles.Add("預估與實際達成率");
ws.Cells[startRow, 1].Value = "累計 vs 銷售";
dt = ReportsBLL.getSalesReportS1T2();
break;
case "s2":
ws.Name = "當月、年度比較"; // Setting Sheet's name
titles.Add("業務員");
titles.Add("客戶編號");
titles.Add("客戶名稱");
titles.Add(reportYear.ToString() + "年度已銷(" + startYearMonth1 + "~" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "年度已銷(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度已銷(" + startYearMonth3 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "同期成長率((D/E)-1)");
titles.Add(reportYear.ToString() + "年度已接未出貨(" + reportYear.ToString() + "/06/30)");
titles.Add(reportYear.ToString() + "年度已銷+未出(" + startYearMonth1 + "~" + currentYearMonth1 + ")(D+H)");
titles.Add((reportYear - 1).ToString() + "年度已銷+未出(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度已銷+未出(" + startYearMonth3 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "成長率((I/J)-1)");
titles.Add((reportYear - 2).ToString() + "v.s" + reportYear.ToString() + "成長率((I/K)-1)");
titles.Add((reportYear - 1).ToString() + "總銷貨額");
titles.Add((reportYear - 2).ToString() + "總銷貨額");
titles.Add(reportYear.ToString() + "年度達成率(I/N)");
titles.Add(reportYear.ToString() + "預估");
titles.Add("預估與實際達成率(I/Q)");
titles.Add(reportYear.ToString() + "/" + reportMonth.ToString() + "下單金額");
if (salesId != "")
{
dt = ReportsBLL.getSalesPersonalReport(salesId);
}
else
{
dt = ReportsBLL.getSalesPersonalReportTotal();
}
break;
case "s3":
ws.Name = "每月預估出貨金額"; // Setting Sheet's name
titles.Add("業務員");
titles.Add("客戶編號");
titles.Add("客戶名稱");
titles.Add((reportYear - 1).ToString() + ".07");
titles.Add((reportYear - 1).ToString() + ".08");
titles.Add((reportYear - 1).ToString() + ".09");
titles.Add((reportYear - 1).ToString() + ".10");
titles.Add((reportYear - 1).ToString() + ".11");
titles.Add((reportYear - 1).ToString() + ".12");
titles.Add((reportYear).ToString() + ".01");
titles.Add((reportYear).ToString() + ".02");
titles.Add((reportYear).ToString() + ".03");
titles.Add((reportYear).ToString() + ".04");
titles.Add((reportYear).ToString() + ".05");
titles.Add((reportYear).ToString() + ".06");
titles.Add("Total");
if (salesId != "")
{
dt = ReportsBLL.getSalesPersonalForecast(salesId);
}
else
{
dt = ReportsBLL.getSalesPersonalForecastTotal();
}
break;
}
if (dt.Rows.Count > 1)
{
if (type != "s2" && type != "s3")
{
// Merging cells and create a center heading for out table
ws.Cells[startRow, 1, startRow, dt.Columns.Count - 1].Merge = true;
ws.Cells[startRow, 1, startRow, dt.Columns.Count - 1].Style.Font.Bold = true;
//ws.Cells[startRow, 1, startRow, dt.Columns.Count-1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(1).Width = 18;
ws.Column(2).Width = 18;
ws.Column(3).Width = 18;
ws.Column(4).Width = 18;
ws.Column(5).Width = 12;
ws.Column(6).Width = 18;
ws.Column(7).Width = 12;
}
else if (type == "s2")
{
ws.Column(1).Width = 12;
ws.Column(2).Width = 12;
ws.Column(3).Width = 12;
ws.Column(4).Width = 18;
ws.Column(5).Width = 18;
ws.Column(6).Width = 18;
ws.Column(7).Width = 12;
ws.Column(8).Width = 18;
ws.Column(9).Width = 18;
ws.Column(10).Width = 18;
ws.Column(11).Width = 18;
ws.Column(12).Width = 12;
ws.Column(13).Width = 12;
ws.Column(14).Width = 18;
ws.Column(15).Width = 18;
ws.Column(16).Width = 12;
ws.Column(17).Width = 18;
ws.Column(18).Width = 12;
ws.Column(19).Width = 18;
}
else
{
ws.Column(1).Width = 12;
ws.Column(2).Width = 12;
ws.Column(3).Width = 12;
ws.Column(4).Width = 14;
ws.Column(5).Width = 14;
ws.Column(6).Width = 14;
ws.Column(7).Width = 14;
ws.Column(8).Width = 14;
ws.Column(9).Width = 14;
ws.Column(10).Width = 14;
ws.Column(11).Width = 14;
ws.Column(12).Width = 14;
ws.Column(13).Width = 14;
ws.Column(14).Width = 14;
ws.Column(15).Width = 14;
ws.Column(16).Width = 14;
ws.Column(17).Width = 14;
}
int colIndex = 1;
int rowIndex = startRow + 1;
// Create table header
if (type == "s2" && startRow != 1 || type == "s3" && startRow != 1)
{
}
else
{
foreach (var title in titles)
{
var cell = ws.Cells[rowIndex, colIndex];
cell.Style.WrapText = true;
// Setting Top/left,right/bottom borders.
var border = cell.Style.Border;
border.Bottom.Style =
border.Top.Style =
border.Left.Style =
border.Right.Style = ExcelBorderStyle.Thin;
// Setting Value in cell
cell.Value = title;
colIndex++;
}
}
// Insert table content
//foreach (DataRow dr in dt.Rows) // Adding Data into rows
for (int i = 0; i < dt.Rows.Count; i++)
{
rowIndex++;
for (int j = 1; j < dt.Columns.Count; j++)
{
var cell = ws.Cells[rowIndex, j];
switch (type)
{
case "s1t1":
case "s1t3":
cell.Value = dt.Rows[i][j];
if (j >= 2 && j <= 4)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j >= 5 && j <= 6)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s1t2":
cell.Value = dt.Rows[i][j];
if (j >= 2 && j <= 4 || j == 6)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j == 5 || j == 7)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s2":
if (i != 0 && j == 1)
{
}
else
{
cell.Value = dt.Rows[i][j];
}
if (j >= 4 && j <= 6 || j >= 8 && j <= 11 || j >= 14 && j <= 15 || j == 17 || j == 19)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j == 7 || j >= 12 && j <= 13 || j == 16 || j == 18)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s3":
if (i != 0 && j == 1)
{
}
else
{
cell.Value = dt.Rows[i][j];
}
if (j >= 4)
{
cell.Style.Numberformat.Format = "$#,##0";
}
break;
}
// Setting borders of cell
var border = cell.Style.Border;
border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
colIndex++;
}
}
if (type != "s2" && type != "s3")
{
result += dt.Rows.Count + 5;
}
else
{
result += dt.Rows.Count;
}
}
dt.Clear();
return(result);
}
public byte[] ExportEmployeePostChangeNoqueryReport(List<string> companyids, List<V_EmployeeChangeInfos> ListInfos, DateTime Dt)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeePostChangeNoQueryReport(companyids, ListInfos, Dt);
}
}
public List<V_EmployeeLeftOfficeInfos> GetEmployeeLeftOfficeConfirmReports(List<string> companyids, string sort, string filterString, IList<object> paras, string userID, string IsType, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
IQueryable<V_EmployeeLeftOfficeInfos> q = bll.GetEmployeeLeftOfficeConfirmReports(companyids, sort, filterString, paras, userID, IsType, DtStart, DtEnd);
if (q != null)
{
return q.Count() > 0 ? q.ToList() : null;
}
else
{
return null;
}
}
// return q.Count() > 0 ? q.ToList() : null;
}
static void Main(string[] args)
{
// Hold the command line argument for RptFreq
List <string> sArgs = new List <string>();
string rptFreq = string.Empty;
// Hold messages for the log file
List <string> logMsgs = new List <string>();
// Get command line arguments
// Should be one for RptFreq = D,M,Q,Y
try
{
if (args.Length > 0)
{
rptFreq = 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;
}
/*
* Get all Employees Reports
*/
ReportsBLL obj = new ReportsBLL();
List <EmployeesReportsModel> eList = new List <EmployeesReportsModel>();
try
{
eList = obj.Get_Employees_Reports();
}
catch (Exception ex)
{
logMsgs.Add("Get_Employees_Reports Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
/*
* Get all active reports.
*/
List <ReportsModel> rList = new List <ReportsModel>();
try
{
rList = obj.Get_Reports();
}
catch (Exception ex)
{
logMsgs.Add("Get_Reports Exception:");
logMsgs.Add(ex.Message.ToString());
Logger.Log(logMsgs);
return;
}
// Find Reports where RptFreq = args[0]
IEnumerable <ReportsModel> rptResults =
from rpt in rList
where rpt.freq == rptFreq
select rpt;
// Loop through reports
foreach (var r in rptResults)
{
logMsgs.Clear();
logMsgs.Add("Emailing:");
logMsgs.Add(r.name + " - " + r.filename + " - " + r.freq);
logMsgs.Add("Tos:");
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 = "Report - " + r.name;
mail.Body = "Report attached";
// Get only employee reports for r.rptID
IEnumerable <EmployeesReportsModel> eResults =
from e in eList
where e.rptID == r.rptID
select e;
//Build To: line from IEnumerable<EmployeesReportsModel>
foreach (var e in eResults)
{
logMsgs.Add(e.email);
mail.To.Add(e.email);
}
// Add attachment
Attachment attach;
attach = new Attachment(r.fullpath);
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);
}
} //foreach
// testing only to stop application so I can read the console
//Console.WriteLine("Press key to exit");
//Console.ReadKey();
}
public byte[] ExportEmployeeLeftOfficeConfirmNoQueryReports(List<string> companyids, List<V_EmployeeLeftOfficeInfos> ListInfos, DateTime DtStart)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeeLeftOfficeConfirmReports(companyids, ListInfos, DtStart);
}
}
public List<V_EmployeeBasicInfo> GetEmployeeBasicInfosByCompany(List<string> CompanyIDs, string sort, string filterString, object[] paras, string userID, string IsType, DateTime Start, DateTime End)
{
using (ReportsBLL bll = new ReportsBLL())
{
IQueryable<V_EmployeeBasicInfo> q = bll.GetEmployeeBasicInfosByCompany(CompanyIDs, sort, filterString, paras, userID, IsType, Start, End);
return q != null ? q.ToList() : null;
}
}
public byte[] ExportEmployeeTructReports(List<string> companyids, string sort, string filterString, object[] paras, string userID, string CompanyID, DateTime DtStart, DateTime DtEnd)
{
using (ReportsBLL bll = new ReportsBLL())
{
return bll.ExportEmployeesTructReports(companyids, sort, filterString, paras, userID, CompanyID, DtStart, DtEnd);
}
}
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();
}
