public List <SubuserEntity> GetSubusers(DBSite site, int userId, string subuser_ids = "")
{
List <SubuserEntity> subuser_list = new List <SubuserEntity>();
SubuserEntity subuser = null;
string qry = "SELECT "
+ "SubuserId"
+ ", SubuserName"
+ ", SubuserCreationDate"
+ ", SubuserPassword"
+ ", EmailId"
+ ", Designation"
+ ", Address"
+ ", City"
+ ", Mobile"
+ ", Active"
+ ", UserId"
+ " FROM tblSubuser "
+ " WHERE userId=" + userId;
if (subuser_ids != "")
{
qry += " AND SubuserId IN ( " + subuser_ids + " )";
}
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
subuser = new SubuserEntity();
subuser.SubuserId = util.CheckNullInt(row["SubuserId"]);
subuser.SubuserName = util.CheckNull(row["SubuserName"]);
DateTime date = Convert.ToDateTime(row["SubuserCreationDate"]);
subuser.CreationDate = date.ToShortDateString();
subuser.Password = util.CheckNull(row["SubuserPassword"]);
subuser.EmailId = util.CheckNull(row["EmailId"]);
subuser.Designation = util.CheckNull(row["Designation"]);
subuser.Address = util.CheckNull(row["Address"]);
subuser.City = util.CheckNull(row["City"]);
subuser.Mobile = util.CheckNull(row["Mobile"]);
subuser.UserState = util.CheckNullInt(row["Active"]) == 1 ? "Yes" : "No";
subuser.Permissions = GetPermissions(site, subuser.SubuserId + "");
subuser.TeacherClasses = GetTeacherClasses(site, subuser.SubuserId + "");
subuser.TeacherSubjects = GetTeacherSubjects(site, subuser.SubuserId + "");
subuser_list.Add(subuser);
}
return(subuser_list);
}
public List <ClassMasterEntity> GetClassListBySubUser(DBSite site, int userId, string id, bool BySubUser)
{
List <ClassMasterEntity> classList = new List <ClassMasterEntity>();
string qry = "";
if (BySubUser)
{
qry = " SELECT ClassMasterID, ClassName, ClassOrder, cl.UserID, cl.FYear "
+ " FROM tblClassMaster cl "
+ " INNER JOIN tblTeacherClasses tc ON tc.classId = cl.classMasterID "
+ " AND tc.userID = cl.userID";
}
else
{
qry = " SELECT ClassMasterID, ClassName, ClassOrder, UserID, FYear "
+ " FROM tblClassMaster cl ";
}
qry += " WHERE cl.UserId = " + Util_BLL.User;
if (id != string.Empty)
{
qry += "AND ClassMasterID = " + id;
}
qry += " ORDER BY ClassOrder ";
DataTable dt = site.ExecuteSelect(qry);
ClassMasterEntity cls;
foreach (DataRow dr in dt.Rows)
{
cls = new ClassMasterEntity();
cls.ClassMasterId = util.CheckNullInt(dr["ClassMasterID"].ToString());
cls.ClassName = util.CheckNull(dr["ClassName"]);
cls.ClassOrder = util.CheckNullInt(dr["ClassOrder"]);
cls.UserID = util.CheckNullInt(dr["UserID"]);
cls.FYear = util.CheckNullInt(dr["FYear"]);
classList.Add(cls);
}
return(classList);
}
private void timer1_Tick(object sender, ElapsedEventArgs e)
{
// witer code to do some job as per ur need
// send sms for Abentee to be sent on 9:15 AM
Util_BLL util = new Util_BLL();
AttendanceBll aBLL = new AttendanceBll();
//aBLL.ProcessMachineAttendance(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), 0);
// Check if attendance table has students per day if not then add
aBLL.AddStudentDataInAttdencance(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), 0);
List <AttendanceBll.AttendanceEntity> absentees = new List <AttendanceBll.AttendanceEntity>();
absentees = aBLL.GetAttendance(site, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, -1, false);
foreach (AttendanceBll.AttendanceEntity abs in absentees)
{
string logdate = "";
// Update InTime
if (abs.Status == 0 && abs.IdcardNo.Length > 1)
{
// get logdate
logdate = aBLL.GetlogDate(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), abs.AttendanceId, abs.IdcardNo, "INTIME");
if (logdate.Length > 0)
{
aBLL.UpDateInTime_OutTime(site, logdate, abs.AttendanceId, 1, "INTIME");
//SendSMSToParents.WriteErrorLog("UPDATE INTIME " + abs.IdcardNo);
}
}
// OUTTIME
if (abs.Status >= 0 && abs.IdcardNo.Length > 1 && abs.IsSMSSent == 1)
{
logdate = aBLL.GetlogDate(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), abs.AttendanceId, abs.IdcardNo, "OUTTIME");
if (logdate.Length > 0)
{
aBLL.UpDateInTime_OutTime(site, logdate, abs.AttendanceId, 1, "OUTTIME");
//SendSMSToParents.WriteErrorLog("UPDATE OUTTIME " + abs.IdcardNo);
}
}
}
// get students again after updating
List <AttendanceBll.AttendanceEntity> absentees2 = new List <AttendanceBll.AttendanceEntity>();
absentees2 = aBLL.GetAttendance(site, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, -1, false);
string msg_str = "";
foreach (AttendanceBll.AttendanceEntity absente in absentees2)
{
if (absente.Status > 0)
{
if (absente.Status == 1 && absente.IsSMSSent == 0 && absente.InTime.Length > 0)
{
msg_str = "Dear Parents, Your child " + absente.StudentName + " has arrived in school on " + absente.InTime + ".";
util.SendSms(absente.MobileNo, msg_str, 1, "WHSLMC", false);
util.SaveSentSMSToDB(site, absente.MobileNo, msg_str, false, 36);
aBLL.PostSMS(site, absente.StudentId, 1);
SendSMSToParents.WriteErrorLog("set isSMSSent to 1 " + absente.StudentName);
}
// Exit
if (absente.Status == 1 && absente.IsSMSSent == 1 && absente.OutTime.Length > 0)
{
msg_str = "Dear Parents, Your child " + absente.StudentName + " has left the school on " + absente.OutTime + ".";
util.SendSms(absente.MobileNo, msg_str, 1, "WHSLMC", false);
util.SaveSentSMSToDB(site, absente.MobileNo, msg_str, false, 36);
aBLL.PostSMS(site, absente.StudentId, 2);
SendSMSToParents.WriteErrorLog("set isSMSSent to 2 " + absente.StudentName);
}
}
}
SendSMSToParents.WriteErrorLog("Timer ticked and some job has been done sucessesfully");
}
public void SaveUserInfo(DBSite site, User user)
{
string qry = " INSERT INTO tblUserInfo("
+ " UserName "
+ ", Email"
+ ", UserPassword"
+ ", CreationDate"
+ ", AmountPaid"
+ ", StartDate"
+ ", EndDate"
+ ", Address"
+ ", City"
+ ", Country"
+ ", Phone"
+ ", Mobile"
+ ", TinNumber"
+ ", SalesTaxNumber"
+ ", CSTNumber"
+ ", UserType"
+ ", NumberOfSubusers"
+ ", Remarks"
+ ", FYear"
+ " )";
qry += " VALUES( '" + user.UserName + "'"
+ ", '" + user.EmailId + "'"
+ ", '" + user.UserPassword + "'"
+ ", '" + user.CreationDate + "'"
+ ", " + user.AmountPaid
+ ", '" + user.StartDate + "'"
+ ", '" + user.EndDate + "'"
+ ", '" + user.Address + "'"
+ ", '" + user.City + "'"
+ ", '" + user.Country + "'"
+ ", '" + user.Phone + "'"
+ ", '" + user.Mobile + "'"
+ ", '" + user.TinNumber + "'"
+ ", '" + user.SalesTaxNumber + "'"
+ ", '" + user.CSTNumber + "'"
+ ", " + user.UserType
+ ", " + user.NumberOFSubusers
+ ", '" + user.Remarks + "'"
+ ", " + user.fYear
+ ")";
site.Execute(qry);
//------ Get User Id Just Inbserted ------------------------
qry = " SELECT UserInfoId FROM tblUserInfo WHERE Email='" + user.EmailId + "'";
user.UserId = util.CheckNullInt(site.ExecuteSelect(qry).Rows[0]["UserInfoId"]); //----- get user id query ----------
foreach (Permission permission in user.Permissions)
{
qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ user.UserId
+ ", "
+ permission.PermissionId
+ ")";
site.Execute(qry);
}
}
//public void SaveAttendance(DBSite site, List<AttendanceEntity> attdList)
//{
// string qry = "";
// foreach (AttendanceEntity atd in attdList)
// {
// qry = " INSERT INTO tblAttendance ("
// + " YearNo, MonthNo, DayNo, HolydayID, StudentMasterID, InTime, OutTime, Status, IsPosted, IsSMSSent, UserId, SubUserId, FYear )"
// + " VALUES ( "
// + atd.YearNo
// + ", " + atd.MonthNo
// + "," + atd.DayNo
// + "," + atd.StudentId
// + ", '" + atd.InTime + "'"
// + ", '" + atd.OutTime + "'"
// + ", " + atd.Status
// + ", " + atd.IsPosted
// + ", " + atd.IsSMSSent ;
// qry +=", "+ util.GetUserInsertQry(Util_BLL.User);
// qry += " ) ";
// site.Execute(qry);
// }
// }
//public List<AttendanceByMonthEntity> GetAttendanceByMonth(int yearNo, int monthNo, int SchoolClassId)
//{
// List<AttendanceByMonthEntity> attendance_list = new List<AttendanceByMonthEntity>();
// AttendanceByMonthEntity abm;
// string qry = "SELECT * FROM vwAttendanceByMonth "
// + Util_BLL.GetUserWhereCondition(Util_BLL.User)
// + " AND YearNo = " + yearNo
// + " AND MonthNo = " + monthNo;
// if (SchoolClassId != -1)
// qry += " AND ClassMasterId = " + SchoolClassId;
// DBSite site2 = new DBSite();
// DataTable dt = site2.ExecuteSelect(qry);
// foreach (DataRow dr in dt.Rows)
// {
// abm = new AttendanceByMonthEntity();
// abm.StudentId = util.CheckNullInt(dr["StudentMasterID"]);
// abm.StudentName = util.CheckNull(dr["StudentName"]);
// abm.ClassId = util.CheckNullInt(dr["ClassMasterId"]);
// abm.ClassName = util.CheckNull(dr["ClassName"]);
// abm.YearNo = util.CheckNullInt(dr["YearNo"]);
// abm.MonthNo = util.CheckNullInt(dr["MonthNo"]);
// abm.Day1 = util.CheckNull(dr["Day1"]);
// abm.Day2 = util.CheckNull(dr["Day2"]);
// abm.Day3 = util.CheckNull(dr["Day3"]);
// abm.Day4 = util.CheckNull(dr["Day4"]);
// abm.Day5 = util.CheckNull(dr["Day5"]);
// abm.Day6 = util.CheckNull(dr["Day6"]);
// abm.Day7 = util.CheckNull(dr["Day7"]);
// abm.Day8 = util.CheckNull(dr["Day8"]);
// abm.Day9 = util.CheckNull(dr["Day9"]);
// abm.Day10 = util.CheckNull(dr["Day10"]);
// abm.Day11 = util.CheckNull(dr["Day11"]);
// abm.Day12 = util.CheckNull(dr["Day12"]);
// abm.Day13 = util.CheckNull(dr["Day13"]);
// abm.Day14 = util.CheckNull(dr["Day14"]);
// abm.Day15 = util.CheckNull(dr["Day15"]);
// abm.Day16 = util.CheckNull(dr["Day16"]);
// abm.Day17 = util.CheckNull(dr["Day17"]);
// abm.Day18 = util.CheckNull(dr["Day18"]);
// abm.Day19 = util.CheckNull(dr["Day19"]);
// abm.Day20 = util.CheckNull(dr["Day20"]);
// abm.Day21 = util.CheckNull(dr["Day21"]);
// abm.Day22 = util.CheckNull(dr["Day22"]);
// abm.Day23 = util.CheckNull(dr["Day23"]);
// abm.Day2 = util.CheckNull(dr["Day24"]);
// abm.Day25 = util.CheckNull(dr["Day25"]);
// abm.Day26 = util.CheckNull(dr["Day26"]);
// abm.Day27 = util.CheckNull(dr["Day27"]);
// abm.Day28 = util.CheckNull(dr["Day28"]);
// abm.Day29 = util.CheckNull(dr["Day29"]);
// abm.Day30 = util.CheckNull(dr["Day30"]);
// abm.Day31 = util.CheckNull(dr["Day31"]);
// attendance_list.Add(abm);
// }
// return attendance_list;
//}
//public List<AttendanceEntity> GetAttendanceByDate(string fromDate, int SchoolClassId)
//{
// DateTime enteredDate = DateTime.Today;
// int year_no = 2014;
// int month_no = 5;
// int day_no = 5;
// if (fromDate != null)
// enteredDate = DateTime.Parse(fromDate);
// year_no = enteredDate.Year;
// month_no = enteredDate.Month;
// day_no = enteredDate.Day;
// DBSite site2 = new DBSite();
// return GetAttendance(site2, year_no, month_no, day_no, SchoolClassId, false);
//}
public List <AttendanceEntity> GetAttendance(DBSite site, int yearNo, int theMonth, int theDay, int clasId, bool onlyAbsentees)
{
List <AttendanceEntity> adList = new List <AttendanceEntity>();
AttendanceEntity da = null;
string qry = "";
qry = "SELECT AttendanceId, YearNo, MonthNo, DayNo, HolidayID "
+ ", a.StudentMasterID, StudentName, s.classID, MobileF , ClassName, s.SectionId, sc.SectionName, s.IdCardNo "
+ " , InTime "
+ " , OutTime, Status "
+ ", IsPosted "
+ " , IsSmsSent "
+ " FROM tblAttendance a "
+ " LEFT OUTER JOIN tblStudentMaster s ON s.StudentMasterID = a.StudentMasterID "
+ " LEFT OUTER JOIN tblClassMaster C ON c.classMasterID = s.classID "
+ " LEFT OUTER JOIN tblSectionMaster sc ON sc.SectionMasterID = s.SectionId "
+ Util_BLL.GetUserWhereCondition("a")
+ " AND YearNo = " + yearNo
+ " AND MonthNo = " + theMonth
+ " AND DayNo = " + theDay;
if (onlyAbsentees)
{
qry += " AND status = 0 ";
}
if (clasId != -1)
{
qry += " AND c.classMasterID = " + clasId;
}
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
da = new AttendanceEntity();
da.AttendanceId = util.CheckNullInt(dr["AttendanceId"]);
da.YearNo = util.CheckNullInt(dr["YearNo"]);
da.MonthNo = util.CheckNullInt(dr["MonthNo"]);
da.DayNo = util.CheckNullInt(dr["DayNo"]);
da.HolidayId = util.CheckNullInt(dr["HolidayID"]);
da.StudentId = util.CheckNullInt(dr["StudentMasterID"]);
da.StudentName = util.CheckNull(dr["StudentName"]);
da.MobileNo = util.CheckNull(dr["MobileF"]);
da.ClassId = util.CheckNullInt(dr["ClassID"]);
da.ClassName = util.CheckNull(dr["ClassName"]);
da.SectionId = util.CheckNullInt(dr["SectionID"]);
da.SectionName = util.CheckNull(dr["SectionName"]);
da.IdcardNo = util.CheckNull(dr["IdCardNo"]);
da.InTime = util.CheckNull(dr["InTime"]);
da.OutTime = util.CheckNull(dr["OutTime"]);
da.IsPosted = util.CheckNullInt(dr["IsPosted"]);
da.IsSMSSent = util.CheckNullInt(dr["IsSMSSent"]);
da.Status = util.CheckNullInt(dr["status"]);
//if (da.IsPosted==0)
// da.Status = 1;
adList.Add(da);
}
//SendSMSToParents.WriteErrorLog("GetAttendance : " + qry);
return(adList);
}