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(); }