private static void RunQuaratineBatchesReport() { if (!IsDuringRestore(DateTime.Now)) { try { using (var reportDB = new report01thas01Entities()) { string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?"; Regex rgx = new Regex(regexPattern); reportDB.Database.CommandTimeout = 10000; Console.WriteLine("Begin Retreiving Quarantine Batches Dataset..."); var financeQuaratineDataset = reportDB.THAS_CONNECT_FinanceQuarantineBatches().ToList(); Console.WriteLine("Successfully Retreived Dataset"); Console.WriteLine("Awaiting Excel Generation..."); FileInfo fileInfo; string theDate = DateTime.Now.ToString("yyyyMMdd"); string theDateHours = DateTime.Now.ToString("yyyyMMdd HH.mm.ss"); if (CreateDirectoryStructure(out fileInfo, theDate, theDateHours, @"FinanceQuarantineBatches", "Finance Reports", true)) //Finance Reports { using (ExcelPackage excelPackage = new ExcelPackage(fileInfo)) { var workSheet = excelPackage.Workbook.Worksheets.Add("QuarantineBatches"); workSheet.Cells["A1"].LoadFromCollection(financeQuaratineDataset, true, OfficeOpenXml.Table.TableStyles.Medium2); int rowCount = workSheet.Dimension.Rows; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(); workSheet.View.ZoomScale = 75; excelPackage.Save(); Console.WriteLine("Successfully Generated Finance Quarantine Batches Costings Excel File"); SendMail("Quarantine Batches Report", "Run Successfully"); } } } } catch (Exception ex) { Console.WriteLine("Quaratine Finance Report Has Failed. Reason: " + ex.Message + ex.InnerException.Message + ex.InnerException); } } }
private static void RunStockValuationReport() { if (!IsDuringRestore(DateTime.Now)) { try { using (var reportDB = new report01thas01Entities()) { string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?"; Regex rgx = new Regex(regexPattern); reportDB.Database.CommandTimeout = 10000; Console.WriteLine("Begin Retreiving Stock Valuation Dataset..."); var stockValDataset = reportDB.THAS_CONNECT_StockValuationReport().ToList(); foreach (var stockVal in stockValDataset) { stockVal.Notes = stockVal.Notes != null?rgx.Replace(stockVal.Notes, "") : stockVal.Notes; if (stockVal.Exclude_From_Provision == "N") { if (stockVal.Provision____TAS_method_ == "100%") { stockVal.Provision_Cost__TAS_method_ = stockVal.Material_Cost__.HasValue ? stockVal.Material_Cost__.Value : 0.0m; } else { stockVal.Provision_Cost__TAS_method_ = 0.0m; } } stockVal.Adjust_Value__TAS_method_ = stockVal.Material_Cost__.HasValue ? stockVal.Material_Cost__.Value : 0.0m - stockVal.Provision_Cost__TAS_method_; if (stockVal.Method_Type.ToLower() == "purchased") { stockVal.Type_Of_Stock = "Raw Materials"; } else if (stockVal.Seat_ == "YES" || stockVal.Product_Group_Code.ToLower() == "frm/frf") { stockVal.Type_Of_Stock = "Finished Goods"; } else { stockVal.Type_Of_Stock = "Manufactured Parts"; } } Console.WriteLine("Successfully Retreived Dataset"); Console.WriteLine("Awaiting Excel Generation..."); FileInfo fileInfo; string theDate = DateTime.Now.ToString("yyyyMMdd"); string theDateHours = DateTime.Now.ToString("yyyyMMdd HH.mm.ss"); if (CreateDirectoryStructure(out fileInfo, theDate, theDateHours, @"StockValuation12AM", "Finance Reports", true)) //Finance Reports { using (ExcelPackage excelPackage = new ExcelPackage(fileInfo)) { var workSheet = excelPackage.Workbook.Worksheets.Add("StockValuation"); workSheet.Cells["A1"].LoadFromCollection(stockValDataset, true, OfficeOpenXml.Table.TableStyles.Medium2); int rowCount = workSheet.Dimension.Rows; workSheet.Cells["I2:I" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells["J2:J" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells["K2:K" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells["L2:L" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells["U2:U" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells["Z2:Z" + rowCount].Style.Numberformat.Format = "dd/MM/yyyy"; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(); workSheet.View.ZoomScale = 75; excelPackage.Save(); Console.WriteLine("Successfully Generated Stock Valuation Costings Excel File"); SendMail("Stock Valuation Report", "Run Successfully"); } } } } catch (Exception ex) { Console.WriteLine("Stock Valuation Costings Report Has Failed. Reason: " + ex.Message + ex.InnerException.Message + ex.InnerException); } } }