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