public static List <ContractorWiseReportModel> GetContractorWiseReport(ContractorModel obj) { using (var dbContext = new BSSDBEntities()) { List <ContractorWiseReportModel> ReportResult = new List <ContractorWiseReportModel>(); List <ContractorWiseReportModel> FinalResult = new List <ContractorWiseReportModel>(); var ReqData = dbContext.SP_ContractorWiseReport(obj.DealerID, obj.AccountID).ToList(); SessionDataBLL DataObj = new SessionDataBLL { DealerID = obj.DealerID, AccountId = obj.AccountID }; var StatusList = StatusMasterDAL.GetData(DataObj); foreach (var item in ReqData) { if (ReportResult.Where(x => x.Contractor_Id == item.Contractor_Id).FirstOrDefault() == null) { ReportResult.Add(new ContractorWiseReportModel { Contractor_Id = item.Contractor_Id, ContractorName = item.ContractorName, StatusList = ReqData.Where(x => x.Contractor_Id == item.Contractor_Id).Select(x => new StatusList { StatusCode = x.StatusCode, StatusID = x.StatusID, StatusName = x.StatusName, JobCards = x.JobCards }).ToList() }); } } foreach (var result in ReportResult) { int Total = 0; List <StatusList> SL = new List <StatusList>(); foreach (var pt in StatusList) { var Check = result.StatusList.Where(x => x.StatusID == pt.StatusID).FirstOrDefault(); int? JobCards = 0; string StatusName = pt.StatusName; string StatusCode = pt.StatusCode; long? StatusID = 0; if (Check != null) { SL.Add(new BODYSHPBLL.ImplBLL.StatusList { JobCards = Check.JobCards, StatusName = Check.StatusName, StatusCode = Check.StatusCode, StatusID = Check.StatusID }); } else { SL.Add(new BODYSHPBLL.ImplBLL.StatusList { JobCards = JobCards, StatusName = StatusName, StatusCode = StatusCode, StatusID = StatusID }); } } FinalResult.Add(new ContractorWiseReportModel { ContractorName = result.ContractorName, Contractor_Id = result.Contractor_Id, Total = Total, StatusList = SL }); } List <StatusList> VerticalCount = new List <StatusList>(); int RowTotal = 0; for (int i = 0; i < FinalResult[0].StatusList.Count; i++) { int ColumnTotal = 0; for (int j = 0; j < FinalResult.Count; j++) { ColumnTotal = ColumnTotal + FinalResult[j].StatusList[i].JobCards.Value; } VerticalCount.Add(new StatusList { JobCards = ColumnTotal }); } FinalResult.Add(new ContractorWiseReportModel { ContractorName = "Total", Contractor_Id = 0, Total = 0, StatusList = VerticalCount }); foreach (var result in FinalResult) { for (int i = 0; i < result.StatusList.Count; i++) { result.Total = result.Total + result.StatusList[i].JobCards; } } return(FinalResult); } }
public static string SendEmail(ReportInput Obj) { var root = System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ExcelPath"]); var StatusList = StatusMasterDAL.GetData(Obj.SessionData); Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(System.Reflection.Missing.Value); Microsoft.Office.Interop.Excel.Worksheet ws = wb.ActiveSheet; ws.Cells[1, 1] = "Group Name"; ws.Cells[1, 1].Font.Bold = true; for (int i = 0; i < StatusList.Count; i++) { ws.Cells[1, i + 2] = StatusList[i].StatusName; ws.Cells[1, i + 2].Font.Bold = true; //ws.Cells[1, i + 2].Text.Align= } ws.Cells[1, StatusList.Count + 2] = "Total"; ws.Cells[1, StatusList.Count + 2].Font.Bold = true; int cell = 2; foreach (var item in Obj.ContractorWiseReport) { int j = 1; ws.Cells[cell, j] = item.ContractorName; j++; foreach (var Status in item.StatusList) { ws.Cells[cell, j] = Status.JobCards; j++; } ws.Cells[cell, j] = item.Total; cell++; } string file = "GroupWiseReport_" + DateTime.Now.ToString("MM-dd-yyyy_hh_mm_ss") + ".xlsx"; string ExportPath = root + "" + file; wb.SaveAs(ExportPath); wb.Close(); Marshal.ReleaseComObject(wb); xla.Quit(); Marshal.FinalReleaseComObject(xla); // Function calling for Email MailAddressCollection toEmail = new MailAddressCollection(); MailAddressCollection ccEmail = new MailAddressCollection(); MailAddressCollection frmEmail = new MailAddressCollection(); using (var DbContext = new BSSDBEntities()) { var EmailList = DbContext.TblEmailMasters.Where(x => x.AccountId == Obj.SessionData.AccountId && x.DealerId == Obj.SessionData.DealerID).ToList(); foreach (var id in EmailList) { toEmail.Add(id.Email_ID); } } string MailBody = ReportsDAL.GenerateHtml(Obj.SessionData); //ccEmail.Add("*****@*****.**"); //toEmail.Add("*****@*****.**"); string MailStatus = Email.sendEmail("BodyShop_Report_" + DateTime.Now.ToString("MM-dd-yyyy"), "*****@*****.**", toEmail, ccEmail, ExportPath, MailBody); return(MailStatus); }