public Attendance_Report GetAttendanceCountReport(string SearchFor, string InputOne, string InputTwo, int locationId, bool status)
{
SearchFor = SearchFor.ToString();
InputOne = InputOne.ToString();
InputTwo = InputTwo.ToString();
Attendance_Report Model = new Attendance_Report();
Model.sP_GetAttendanceCountReports = new List <SP_GetAttendanceCountReport>();
Model.SP_GetMonthlyAttendanceData = new List <SP_GetMonthlyAttendanceData>();
Model.sP_GetAttendanceLeaveDatas = new List <SP_GetAttendanceLeaveData>();
var SP_locationId = new SqlParameter("@locationId", locationId);
var SP_SelectType = new SqlParameter("@SelectType", SearchFor);
var SP_InputOne = new SqlParameter("@InputOne", InputOne);
var SP_InputTwo = new SqlParameter("@InputTwo", InputTwo);
var SP_Status = new SqlParameter("@Status", status);
var SP_SrId = new SqlParameter("@SrId", 1);
if (SearchFor == "Month")
{
SP_InputOne = new SqlParameter("@InputOne", InputTwo);
SP_InputTwo = new SqlParameter("@InputTwo", InputOne);
}
string usp = "LMS.usp_GetAttendanceCountReport @locationId, @SelectType, @InputOne, @InputTwo,@Status";
Model.sP_GetAttendanceCountReports = _dbContext._sp_GetAttendanceCountReport.FromSql(usp, SP_locationId, SP_SelectType, SP_InputOne, SP_InputTwo, SP_Status).ToList();
string uspLeaveData = "LMS.usp_GetAttendanceReportLeaveData @SelectType ,@InputOne, @InputTwo";
Model.sP_GetAttendanceLeaveDatas = _dbContext._sp_GetAttendanceLeaveData.FromSql(uspLeaveData, SP_SelectType, SP_InputOne, SP_InputTwo).ToList();
usp = "LMS.usp_GetMonthlyAttendance @SrId, @SelectType, @InputOne, @InputTwo,@Status";
Model.SP_GetMonthlyAttendanceData = _dbContext._sp_GetMonthlyAttendanceData.FromSql(usp, SP_SrId, SP_SelectType, SP_InputOne, SP_InputTwo, SP_Status).ToList();
return(Model);
}
public IActionResult AllAttendance(string date, string type, int location, bool status)
{
string url = GetAllAttendanceData(date, type, location, status);
HttpResponseMessage response = _service.GetResponse(url);
string stringData = response.Content.ReadAsStringAsync().Result;
Attendance_Report attendanceData = JsonConvert.DeserializeObject <Attendance_Report>(stringData);
return(Json(attendanceData));
}
public void SendAttendanceReportToAdminHRManager()
{
var year = DateTime.Now.ToString("yyyy");
DateTime d = DateTime.Now;
d = d.AddMonths(-1);
int month = d.Month;
string monthName = new DateTime(2000, month, 1).ToString("MMM", CultureInfo.InvariantCulture);
var rootPath = @"C:\Temp\AttendanceReportToAdmin\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
if (!Directory.Exists(rootPath + year + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\");
}
if (!Directory.Exists(rootPath + year + "\\" + monthName + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\" + monthName + "\\");
}
int locationId = 0;
var SearchFor = "Month";
char c = '0';
var InputOne = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, c);
var InputTwo = DateTime.Now.Year.ToString();
bool status = true;
IList <AttendanceData> attendanceData = new List <AttendanceData>();
Attendance_Report Model = new Attendance_Report();
Model.sP_GetAttendanceCountReports = new List <SP_GetAttendanceCountReport>();
var SP_locationId = new SqlParameter("@locationId", locationId);
var SP_SelectType = new SqlParameter("@SelectType", SearchFor);
var SP_InputOne = new SqlParameter("@InputOne", InputOne);
var SP_InputTwo = new SqlParameter("@InputTwo", InputTwo);
var SP_Status = new SqlParameter("@Status", status);
string usp = "LMS.usp_GetAttendanceCountReport @locationId, @SelectType, @InputOne, @InputTwo,@Status";
Model.sP_GetAttendanceCountReports = _dbContext._sp_GetAttendanceCountReport.FromSql(usp, SP_locationId, SP_SelectType, SP_InputOne, SP_InputTwo, SP_Status).ToList();
string attendanceReportPath = @"C:\Temp\AttendanceReportToAdmin\" + year + "\\" + monthName + "\\" + "AttendanceReport.xlsx";
if (File.Exists(attendanceReportPath))
{
File.Delete(attendanceReportPath);
}
var memory = new MemoryStream();
using (var sw = new FileStream(attendanceReportPath, FileMode.Create, FileAccess.Write))
{
int i = 1;
IWorkbook workbook;
ICell cell;
workbook = new XSSFWorkbook();
ICellStyle headerStyle = workbook.CreateCellStyle();
ISheet sheet = workbook.CreateSheet("Attendance Report");
IRow row = sheet.CreateRow(0);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Aadyam Consultant llp.");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Employee Management System");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Attendance Report:-" + monthName + "/" + year);
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Code");
cell = row.CreateCell(1);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Name");
cell = row.CreateCell(2);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Working Days");
cell = row.CreateCell(3);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave Without Approval");
cell = row.CreateCell(4);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave With Approval");
cell = row.CreateCell(5);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Total Leaves");
cell = row.CreateCell(6);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.BorderRight = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Present Days");
byte[] rgb1 = new byte[3] {
242, 255, 204
};
byte[] rgb2 = new byte[3] {
230, 255, 204
};
byte[] rgb3 = new byte[3] {
230, 255, 255
};
byte[] rgb4 = new byte[3] {
255, 214, 204
};
byte[] rgb5 = new byte[3] {
214, 245, 214
};
byte[] rgb6 = new byte[3] {
255, 238, 204
};
byte[] rgb7 = new byte[3] {
217, 255, 179
};
XSSFCellStyle headerStyle1 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle1.BorderBottom = BorderStyle.Medium;
headerStyle1.BorderLeft = BorderStyle.Medium;
headerStyle1.SetFillForegroundColor(new XSSFColor(rgb1));
headerStyle1.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle2.BorderBottom = BorderStyle.Medium;
headerStyle2.BorderLeft = BorderStyle.Medium;
headerStyle2.SetFillForegroundColor(new XSSFColor(rgb2));
headerStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle3 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle3.BorderBottom = BorderStyle.Medium;
headerStyle3.BorderLeft = BorderStyle.Medium;
headerStyle3.SetVerticalAlignment(3);
headerStyle3.SetFillForegroundColor(new XSSFColor(rgb3));
headerStyle3.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle4 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle4.BorderBottom = BorderStyle.Medium;
headerStyle4.BorderLeft = BorderStyle.Medium;
headerStyle4.SetVerticalAlignment(3);
headerStyle4.SetFillForegroundColor(new XSSFColor(rgb4));
headerStyle4.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle5 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle5.BorderBottom = BorderStyle.Medium;
headerStyle5.BorderLeft = BorderStyle.Medium;
headerStyle5.SetFillForegroundColor(new XSSFColor(rgb5));
headerStyle5.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle6 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle6.BorderBottom = BorderStyle.Medium;
headerStyle6.BorderLeft = BorderStyle.Medium;
headerStyle6.SetFillForegroundColor(new XSSFColor(rgb6));
headerStyle6.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle7 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle7.BorderBottom = BorderStyle.Medium;
headerStyle7.BorderLeft = BorderStyle.Medium;
headerStyle7.BorderRight = BorderStyle.Medium;
headerStyle7.SetFillForegroundColor(new XSSFColor(rgb7));
headerStyle7.FillPattern = FillPattern.SolidForeground;
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 7000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 5600);
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3200);
foreach (var attendance in Model.sP_GetAttendanceCountReports)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = headerStyle1;
cell.SetCellValue(attendance.EmployeeCode);
cell = row.CreateCell(1);
cell.CellStyle = headerStyle2;
cell.SetCellValue(attendance.EmployeeName);
cell = row.CreateCell(2);
cell.CellStyle = headerStyle3;
cell.SetCellValue(Convert.ToInt32(attendance.WorkingDay));
cell = row.CreateCell(3);
cell.CellStyle = headerStyle4;
cell.SetCellValue(Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(4);
cell.CellStyle = headerStyle5;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave));
cell = row.CreateCell(5);
cell.CellStyle = headerStyle6;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave) + Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(6);
cell.CellStyle = headerStyle7;
cell.SetCellValue(Convert.ToInt32(attendance.PresentDays));
i++;
}
workbook.Write(sw);
workbook.Close();
}
var persons = _repository.Employee.getAdminHrManager()
.Select(p => new
{
EmailAddress = p.EmailAddress
}).ToList();
foreach (var p in persons)
{
string To = p.EmailAddress;
string subject = "EMS Monthly Attendance Report";
string body = "Dear Sir/Maa'm \n" +
"Kindly find monthly attendance report in attachment";
new EmailManager(_configuration, _repository).SendEmail(subject, body, To, attendanceReportPath);
}
}
public IActionResult GetAllAttendance([FromRoute] string SearchFor, [FromRoute] string InputOne, [FromRoute] string InputTwo, [FromRoute] int locationId, [FromRoute] bool status)
{
Attendance_Report attendanceData = _repository.Attendances.GetAttendanceCountReport(SearchFor, InputOne, InputTwo, locationId, status);
return(Ok(attendanceData));
}
