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