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