public int? GetStudentCountReport(DTOInstituteDetails toinst, string gender)
{
using (Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext())
{
return (dbcontext.GetStudentCount(toinst.InstituteID)).Where(t => t.Gender == gender).Select(x => x.TotalCount).FirstOrDefault();
}
}
public DataTable GetStudentDetails(DTOStudentRegistration tostu)
{
using (Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext())
{
return (dbcontext.GetStudentDetails(tostu.StudentId)).ToDataTable();
}
}
public DataTable GetInternalsChartDetails(DTOInternals toint)
{
using (Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext())
{
int _year = int.Parse(toint.Year);
return dbContext.GetBarChartInternalsDetails(toint.InstituteId, toint.Month, _year).ToDataTable();
}
}
public bool SaveUpdateAttendance(DTOAttendance toAtt)
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
try
{
if (dbContext.tblAttendanceDetails.Where(x => x.colStudentId == toAtt.StudentId && x.colMonth == toAtt.Month && x.colYear == toAtt.Year).ToList().Count > 0)
{
tblAttendanceDetail Attendance = dbContext.tblAttendanceDetails.Where(x => x.colStudentId == toAtt.StudentId && x.colMonth == toAtt.Month && x.colYear == toAtt.Year).FirstOrDefault();
Attendance.colBranchId = toAtt.BranchId;
Attendance.colSemesterId = toAtt.SemesterId;
Attendance.colMonth = toAtt.Month;
Attendance.colYear = toAtt.Year;
Attendance.colInstituteId = toAtt.InstituteId;
Attendance.colClassesAttendedMonth = toAtt.ClassesAttended;
Attendance.colClassesAttendedMonthPercent = toAtt.ClassesPercentage;
Attendance.colClassesHeldMonth = toAtt.ClassesHeld;
Attendance.colAccumulatedClassesAttended = toAtt.CumClassesAttended;
Attendance.colAccumulatedClassesHeld = toAtt.CumClassesHeld;
Attendance.colAccumulatedClassesAttendedPercent = toAtt.CumClassesPercentage;
Attendance.colStudentId = toAtt.StudentId;
Attendance.colSubjectId = toAtt.SubjectId;
Attendance.colRemarks = toAtt.Remarks;
Attendance.colDescription = toAtt.Description;
dbContext.SubmitChanges();
return true;
}
else
{
tblAttendanceDetail Attendance = new tblAttendanceDetail();
Attendance.colBranchId = toAtt.BranchId;
Attendance.colSemesterId = toAtt.SemesterId;
Attendance.colMonth = toAtt.Month;
Attendance.colYear = toAtt.Year;
Attendance.colInstituteId = toAtt.InstituteId;
Attendance.colClassesAttendedMonth = toAtt.ClassesAttended;
Attendance.colClassesAttendedMonthPercent = toAtt.ClassesPercentage;
Attendance.colClassesHeldMonth = toAtt.ClassesHeld;
Attendance.colAccumulatedClassesAttended = toAtt.CumClassesAttended;
Attendance.colAccumulatedClassesHeld = toAtt.CumClassesHeld;
Attendance.colAccumulatedClassesAttendedPercent = toAtt.CumClassesPercentage;
Attendance.colStudentId = toAtt.StudentId;
Attendance.colSubjectId = toAtt.SubjectId;
Attendance.colRemarks = toAtt.Remarks;
Attendance.colDescription = toAtt.Description;
dbContext.tblAttendanceDetails.InsertOnSubmit(Attendance);
dbContext.SubmitChanges();
return true;
}
}
catch
{
return false;
}
}
public DataTable GetSubjectsList(string _pInstituteId, int classId)
{
int InstituteId = int.Parse(_pInstituteId);
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
var res = (from s in dbContext.tblSubjectDetails
join c in dbContext.tblBranchDetails on s.colBranchId equals c.colBranchId
where s.colInstituteId == InstituteId && s.colBranchId == classId
select s);
return res.ToDataTable();
}
public List<DTOClasswiseCount> GetStudentAttendanceCountClasswiseReport(int? instituteid, string columnname, int branchid, string month, string year)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
return (from v in dbcontext.GetStudentsAttendanceDetailsMonthwise(instituteid, month, year)
where v.columnname == columnname && v.colBranchId == branchid
select new DTOClasswiseCount
{
Count = v.AttendanceCount,
ClassName = v.colBranchName
}).ToList();
}
public List<DTOClasswiseCount> GetStudentCountClasswiseReport(DTOInstituteDetails toinst, string gender, int branchid)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
return (from v in dbcontext.GetStudentCountClasswiseGender(toinst.InstituteID)
where v.Gender == gender && v.colBranchId == branchid
select new DTOClasswiseCount
{
Count = v.TotalCount,
ClassName = v.colBranchName
}).ToList();
}
public string GetBranchId(string BranchName)
{
try
{
Campus2CaretakerDataContext db = new Campus2CaretakerDataContext();
var BranchData = db.tblBranchDetails.Where(x => x.colBranchName == BranchName).First();
return BranchData.colBranchId.ToString();
}
catch (Exception ex)
{
return string.Empty;
}
}
public List<DTOClasswiseCount> GetStudentInternalsCountClasswiseReport(int? instituteid, string columnname, int branchid, string month, string year,int subjectId)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
return (from v in dbcontext.GetStudentsMarksDetailsMonthwise(instituteid, month, year)
where v.columnname == columnname && v.colBranchId == branchid && v.colSubjectId == subjectId
select new DTOClasswiseCount
{
Count = v.MarksScoredCount,
ClassName = v.colBranchName,
SubjectName = v.colSubjectName
}).ToList();
}
public bool SaveUpdateInternals(DTOInternals toInt)
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
try
{
if (dbContext.tblInternalMarks.Where(x => x.colStudentId == toInt.StudentId && x.colMonth == toInt.Month && x.colYear == toInt.Year && x.colSubjectId == toInt.SubjectId).ToList().Count > 0)
{
tblInternalMark InternalMarks = dbContext.tblInternalMarks.Where(x => x.colStudentId == toInt.StudentId && x.colMonth == toInt.Month && x.colYear == toInt.Year && x.colSubjectId == toInt.SubjectId).FirstOrDefault();
InternalMarks.colBranchId = toInt.BranchId;
InternalMarks.colSemesterId = toInt.SemesterId;
InternalMarks.colMonth = toInt.Month;
InternalMarks.colYear = toInt.Year;
InternalMarks.colInstituteId = toInt.InstituteId;
InternalMarks.colMarksScored = toInt.MarksScored;
InternalMarks.colMaxMarks = toInt.MaxMarks;
InternalMarks.colMinMarks = toInt.MinMarks;
InternalMarks.colStudentId = toInt.StudentId;
InternalMarks.colSubjectId = toInt.SubjectId;
InternalMarks.colRemarks = toInt.Remarks;
InternalMarks.colDescription = toInt.Description;
dbContext.SubmitChanges();
return true;
}
else
{
tblInternalMark InternalMarks = new tblInternalMark();
InternalMarks.colBranchId = toInt.BranchId;
InternalMarks.colSemesterId = toInt.SemesterId;
InternalMarks.colMonth = toInt.Month;
InternalMarks.colYear = toInt.Year;
InternalMarks.colInstituteId = toInt.InstituteId;
InternalMarks.colMarksScored = toInt.MarksScored;
InternalMarks.colMaxMarks = toInt.MaxMarks;
InternalMarks.colMinMarks = toInt.MinMarks;
InternalMarks.colStudentId = toInt.StudentId;
InternalMarks.colSubjectId = toInt.SubjectId;
InternalMarks.colRemarks = toInt.Remarks;
InternalMarks.colDescription = toInt.Description;
dbContext.tblInternalMarks.InsertOnSubmit(InternalMarks);
dbContext.SubmitChanges();
return true;
}
}
catch
{
return false;
}
}
public DataTable GetStudentsListEdit(DTOAttendance toAtt)
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
if (toAtt.SemesterId > 0)
{
string _year = toAtt.Year.ToString();
return dbContext.GetStudentsAttendanceDetailsEdit(toAtt.InstituteId,
toAtt.SubjectId,
toAtt.Month,
_year,
toAtt.SemesterId,
toAtt.BranchId).ToDataTable();
}
throw new NotImplementedException();
}
public DataTable GetStudentAttendanceDetails(int StudentID)
{
try
{
using (Campus2CaretakerDataContext ctx = new Campus2CaretakerDataContext())
{
var sdet = (from u in ctx.GetStudentAttendanceDetailsParentsLogin(StudentID) select u).ToDataTable();
return sdet;
}
}
catch
{
return null;
}
}
public bool DeleteStudentDetails(DTOStudentRegistration tostu)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
// Institute Details
tblStudentDetail StudDetail = dbContext.tblStudentDetails.Where(x => x.colStudentId == tostu.StudentId).FirstOrDefault();
dbContext.tblStudentDetails.DeleteOnSubmit(StudDetail);
dbContext.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public bool DeleteInstituteDetails(DTOInstituteDetails toins)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
// Institute Details
tblInstituteDetail InstDetail = dbContext.tblInstituteDetails.Where(x => x.colInstituteId == toins.InstituteID).FirstOrDefault();
dbContext.tblInstituteDetails.DeleteOnSubmit(InstDetail);
dbContext.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public string GetExistingotp(string mobno)
{
try
{
using (Campus2CaretakerDataContext ctx = new Campus2CaretakerDataContext())
{
var otpdet = (from u in ctx.tblParentsLoginOTPs where u.colMobileNumber == mobno && u.colisUsed == 0 select u).FirstOrDefault();
if (!string.IsNullOrEmpty(otpdet.colOTP))
{
return otpdet.colOTP;
}
return string.Empty;
}
}
catch
{
return string.Empty; ;
}
}
public string GetParentsEmailId(string mobilenumber)
{
try
{
using (Campus2CaretakerDataContext ctx = new Campus2CaretakerDataContext())
{
var pdet = (from u in ctx.tblStudentDetails where u.colParentsMobileNo == mobilenumber select u).FirstOrDefault();
if (!string.IsNullOrEmpty(pdet.colParentsEmail))
{
return pdet.colParentsEmail;
}
return string.Empty;
}
}
catch
{
return string.Empty; ;
}
}
public DataTable GetStudentsListEdit(DTOInternals toint)
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
if (toint.SemesterId > 0)
{
return dbContext.GetStudentsInternalsDetailsEdit(toint.InstituteId,
toint.SubjectId,
toint.Month,
toint.Year,
toint.SemesterId,
toint.BranchId).ToDataTable();
}
// create stored procedure for schools without semester
return dbContext.GetStudentsInternalsDetailsEdit(toint.InstituteId,
toint.SubjectId,
toint.Month,
toint.Year,
0,
toint.BranchId).ToDataTable();
}
public bool SaveClasses(DTOPersonalizeApplication DTOPApplication)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
foreach (string _classname in DTOPApplication.Classes)
{
tblBranchDetail brnchDetail = new tblBranchDetail();
brnchDetail.colBranchName = _classname;
brnchDetail.colInstituteId = DTOPApplication.InstituteId;
dbContext.tblBranchDetails.InsertOnSubmit(brnchDetail);
}
dbContext.SubmitChanges();
return true;
}
catch (Exception ex)
{
return false;
}
}
public List<DTOInstituteDetails> GetAutoCompleteStates(string reqString)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
if (!string.IsNullOrEmpty(reqString))
{
return (from v in dbcontext.tblInstituteDetails
where v.colState.Contains(reqString)
select new DTOInstituteDetails
{
State = v.colState
}).ToList();
}
else
{
return (from v in dbcontext.tblInstituteDetails
select new DTOInstituteDetails
{
State = v.colState
}).ToList();
}
}
public bool CheckParentsLoginUser(DTOParentsLoginDetails tologin)
{
try
{
using (Campus2CaretakerDataContext ctx = new Campus2CaretakerDataContext())
{
var usr = (from u in ctx.tblParentsLoginOTPs where u.colMobileNumber == tologin.Mobilenumber && u.colOTP == tologin.Otp select u);
if (usr.Count() > 0)
{
usr.FirstOrDefault().colisUsed = 1;
ctx.SubmitChanges();
return true;
}
return false;
}
}
catch
{
return false;
}
}
public List<DTOStudentRegistration> GetAutoCompleteStudentNames(string reqString)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
if (!string.IsNullOrEmpty(reqString))
{
return (from v in dbcontext.tblStudentDetails
where v.colStudentName.ToLower().Contains(reqString.ToLower())
select new DTOStudentRegistration
{
StudentName = v.colStudentName,
StudentId = v.colStudentId
}).ToList();
}
else
{
return (from v in dbcontext.tblStudentDetails
select new DTOStudentRegistration
{
StudentName = v.colStudentName,
StudentId = v.colStudentId
}).ToList();
}
}
public bool SaveSubjects(DTOPersonalizeApplication DTOPApplication)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
foreach (string _theorysubject in DTOPApplication.TheorySubjects)
{
tblSubjectDetail subjDetail = new tblSubjectDetail();
subjDetail.colBranchId = DTOPApplication.ClassId;
subjDetail.colInstituteId = DTOPApplication.InstituteId;
subjDetail.colIsTheory = "Y";
subjDetail.colSubjectName = _theorysubject;
subjDetail.colSemester = DTOPApplication.Semester;
dbContext.tblSubjectDetails.InsertOnSubmit(subjDetail);
}
foreach (string _labsubject in DTOPApplication.LabSubjects)
{
tblSubjectDetail subjDetail = new tblSubjectDetail();
subjDetail.colBranchId = DTOPApplication.ClassId;
subjDetail.colInstituteId = DTOPApplication.InstituteId;
subjDetail.colIsTheory = "N";
subjDetail.colSubjectName = _labsubject;
subjDetail.colSemester = DTOPApplication.Semester;
dbContext.tblSubjectDetails.InsertOnSubmit(subjDetail);
}
dbContext.SubmitChanges();
return true;
}
catch (Exception ex)
{
return false;
}
}
public DataTable GetFilteredInstitutes(string _PInstName, string _PDistrict, string _PState)
{
using (Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext())
{
_PInstName = String.Concat("%", _PInstName, "%");
_PDistrict = String.Concat("%", _PDistrict, "%");
_PState = String.Concat("%", _PState, "%");
var results = (from x in dbcontext.tblInstituteDetails
where SqlMethods.Like(x.colInstituteName, _PInstName)
|| SqlMethods.Like(x.colDistrict, _PDistrict)
|| SqlMethods.Like(x.colState, _PState)
select x).ToDataTable();
return results;
}
}
public DataTable GetInstitutes()
{
using (Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext())
{
return dbcontext.tblInstituteDetails.ToDataTable();
}
}
public DataTable GetClassesList(string _pInstituteId)
{
int InstituteId = int.Parse(_pInstituteId);
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
return dbContext.tblBranchDetails.Where(x => x.colInstituteId == InstituteId).ToDataTable();
}
public bool UpdateInstituteDetails(DTOInstituteDetails toins)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
// Institute Details
tblInstituteDetail InstDetail = dbContext.tblInstituteDetails.Where(x => x.colInstituteId == toins.InstituteID).FirstOrDefault();
InstDetail.colAddress = toins.InstituteAddress;
InstDetail.colInstituteName = toins.InstituteName;
InstDetail.colInstituteType = toins.InstituteType;
InstDetail.colLogoPath = toins.LogoPath;
InstDetail.colPhone = toins.InstitutePhoneNo;
InstDetail.colPrincipalContact = toins.PrincipalContact;
InstDetail.colPrincipalName = toins.PrincipalName;
InstDetail.colState = toins.State;
InstDetail.colDistrict = toins.District;
InstDetail.colMaxStudents = toins.MaxStudents;
dbContext.SubmitChanges();
return true;
}
catch { return false; }
}
public bool IsInstituteNameExists(DTOInstituteDetails toins)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
int inCount = dbContext.tblInstituteDetails.Where(x => x.colInstituteName == toins.InstituteName).Count();
if (inCount > 0)
return true;
else
return false;
}
catch
{
return false;
}
}
public DataTable GetInstituteDetails(DTOInstituteDetails toins)
{
using (Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext())
{
return (dbcontext.GetInstituteDetails(toins.InstituteID)).ToDataTable();
}
}
public bool InsertInstituteDetails(DTOInstituteDetails toins)
{
try
{
Campus2CaretakerDataContext dbContext = new Campus2CaretakerDataContext();
// Institute Details
tblInstituteDetail InstDetail = new tblInstituteDetail();
InstDetail.colAddress = toins.InstituteAddress;
InstDetail.colInstituteName = toins.InstituteName;
InstDetail.colInstituteType = toins.InstituteType;
InstDetail.colLogoPath = toins.LogoPath;
InstDetail.colPhone = toins.InstitutePhoneNo;
InstDetail.colPrincipalContact = toins.PrincipalContact;
InstDetail.colPrincipalName = toins.PrincipalName;
InstDetail.colState = toins.State;
InstDetail.colDistrict = toins.District;
InstDetail.colMaxStudents = toins.MaxStudents;
dbContext.tblInstituteDetails.InsertOnSubmit(InstDetail);
dbContext.SubmitChanges();
// Institute Login Details
tblInstituteLogin InstLogin = new tblInstituteLogin();
InstLogin.colInstituteId = dbContext.tblInstituteDetails.Select(x => x.colInstituteId).Max();
InstLogin.colPassword = toins.InstituteDefaultPwd;
InstLogin.colUsername = toins.InstituteEmail;
InstLogin.colUserType = toins.InstituteUserType;
dbContext.tblInstituteLogins.InsertOnSubmit(InstLogin);
dbContext.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public int GetInstituteId(string email)
{
Campus2CaretakerDataContext dbcontext = new Campus2CaretakerDataContext();
var res = from x in dbcontext.tblInstituteLogins
where x.colUsername == email
select x;
return res.First().colInstituteId;
}
