public IActionResult GetPeoplAttendanceSummary(BranchPeopleSummaryModel model) { var result = new RequestResult <List <BranchAttendanceSummary> >() { Data = _reportsManager.GetPeopleAttendanceSummary(model), Message = "Success", Success = true }; return(Ok(result)); }
internal static string ReportsBranchPeopleSummary(BranchPeopleSummaryModel branchPeopleAttendance) { string allActCode = string.Join("' , '", branchPeopleAttendance.ActivityCode); return($"SELECT code.Act_cd, code.Act_Name as Act_Name, mstBr.UID_No as UID_No, " + $"mstBr.Roll_NO as Roll_NO, mstBr.Name_Full as Name_Full, mstBr.Mobile, " + $"mstBr.INI_JIG_NON as INI_JIG_NON, attend.Act_Date as Act_Date, " + $"count(mstBr.Roll_NO) as AttendanceCount, mstBr.SANT_SU " + $"FROM BranchMaster mstBr, Act2018 attend, ActivityCode code " + $"WHERE mstBr.Roll_NO = attend.Roll_NO " + $"AND attend.Act_cd = code.Act_cd " + $"AND attend.Act_cd in ('{allActCode}') " + $"AND attend.Act_Date >= #{branchPeopleAttendance.StartDate}# " + $"AND attend.Act_Date <= #{branchPeopleAttendance.EndDate}# " + $"GROUP BY code.Act_Name, mstBr.UID_No, mstBr.Roll_NO, mstBr.Name_Full, " + $"mstBr.INI_JIG_NON, attend.Act_Date, code.Act_cd, mstBr.Mobile, mstBr.SANT_SU " + $"ORDER BY mstBr.Name_Full"); }
internal List <BranchAttendanceSummary> GetPeopleAttendanceSummary(BranchPeopleSummaryModel branchPeopleAttendance) { List <BranchPeopleAttendance> lst = new List <BranchPeopleAttendance>(); // Registered Users Attendance Result var branchAttendance = _dbConnection.GetModelDetails(RawSQL.ReportsBranchPeopleSummary(branchPeopleAttendance)); foreach (DataRow dataRow in branchAttendance.AsEnumerable()) { var record = new BranchPeopleAttendance() { BrTitle = dataRow.Field <string>("INI_JIG_NON") == "INI" ? "Initiated" : dataRow.Field <string>("INI_JIG_NON") == "CHL" && string.IsNullOrWhiteSpace(dataRow.Field <string>("SANT_SU")) ? "Children" : dataRow.Field <string>("INI_JIG_NON") == "CHL" && !string.IsNullOrWhiteSpace(dataRow.Field <string>("SANT_SU")) ? "SantSu" : dataRow.Field <string>("INI_JIG_NON") == "OTH" ? "Other" : dataRow.Field <string>("INI_JIG_NON") == "JIG" ? "Jigyasu" : "", ActivityName = dataRow.Field <string>("Act_Name"), AttendanceDate = dataRow.Field <DateTime?>("Act_Date") }; lst.Add(record); } // Visitors Attendance Result var visitorsAttendance = _dbConnection.GetModelDetails(RawSQL.ReportsBranchVisitorsPeopleSummary(branchPeopleAttendance.ActivityCode, branchPeopleAttendance.StartDate, branchPeopleAttendance.EndDate)); foreach (DataRow dataRow in visitorsAttendance.AsEnumerable()) { var record = new BranchPeopleAttendance() { BrTitle = dataRow.Field <string>("Initiated").ToLower() == "yes".ToLower() ? "Initiated" : "Other", ActivityName = dataRow.Field <string>("Act_Name"), AttendanceDate = dataRow.Field <DateTime?>("Act_Date"), IsVisitors = true }; lst.Add(record); } List <BranchAttendanceSummary> finalLst = new List <BranchAttendanceSummary>(); foreach (var calc in lst.OrderBy(s => s.AttendanceDate).GroupBy(s => new { s.ActivityName, s.AttendanceDate })) { var totalIni = calc.Where(s => s.IsVisitors != true).Count(s => s.BrTitle == "Initiated"); var totalJig = calc.Count(s => s.BrTitle == "Jigyasu"); var totalOther = calc.Where(s => s.IsVisitors != true).Count(s => s.BrTitle == "Other"); var totalChild = calc.Count(s => s.BrTitle == "Children"); var totalSantSu = calc.Count(s => s.BrTitle == "SantSu"); var totalVisitorIni = calc.Where(s => s.IsVisitors).Count(s => s.BrTitle == "Initiated"); var totalVisitorOthers = calc.Where(s => s.IsVisitors).Count(s => s.BrTitle == "Other"); var totalPeople = totalIni + totalJig + totalOther + totalChild + totalSantSu + totalVisitorIni + totalVisitorOthers; finalLst.Add(new BranchAttendanceSummary { ActivityName = calc.Key.ActivityName, AttendanceDate = calc.Key.AttendanceDate, TotalIni = totalIni, TotalJig = totalJig, TotalChil = totalChild + totalSantSu, TotalOther = totalOther, TotalVisitorIni = totalVisitorIni, TotalVisitorOther = totalVisitorOthers, TotalPeople = totalPeople }); } // Voided Attendance List <BranchPeopleAttendance> voidLst = new List <BranchPeopleAttendance>(); var branchVoidedRec = _dbConnection.GetModelDetails(RawSQL.ReportsBranchPeopleAttendanceVoided(branchPeopleAttendance.ActivityCode, branchPeopleAttendance.StartDate, branchPeopleAttendance.EndDate)); foreach (DataRow dataRow in branchVoidedRec.AsEnumerable()) { var record = new BranchPeopleAttendance() { BrTitle = "NA", ActivityName = dataRow.Field <string>("Act_Name"), AttendanceDate = dataRow.Field <DateTime?>("Act_Date") }; voidLst.Add(record); } foreach (var add in voidLst) { finalLst.Add(new BranchAttendanceSummary { ActivityName = add.ActivityName, AttendanceDate = add.AttendanceDate, TotalIni = -1, TotalJig = -1, TotalChil = -1, TotalOther = -1, TotalVisitorIni = -1, TotalVisitorOther = -1, TotalPeople = -1, }); } return(finalLst); }
public List <BranchAttendanceSummary> GetPeopleAttendanceSummary(BranchPeopleSummaryModel branchPeopleAttendance) => _reportsDataAccess.GetPeopleAttendanceSummary(branchPeopleAttendance);