public void UpdateClassFrm(DBSite site, ClassMasterEntity cls) { string qry = "UPDATE tblClassMaster SET "; qry += " ClassName ='" + cls.ClassName + "'"; qry += ", ClassOrder=" + cls.ClassOrder + ""; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); //------------- gwt user where condition -------------- qry += " AND ClassMasterID=" + cls.ClassMasterId; site.Execute(qry); }
public void ProcessMachineAttendance(DBSite site, int yr, int mnth, int dey, int isHoliday) { // Insert records in tblAttendance when page loads from attendance machine string esslTbl = "etimetracklite1.dbo.DeviceLogs_" + mnth + "_" + yr; string qry = " INSERT INTO tblAttendance(YearNo, MonthNo, DayNo, HolidayId " + ", status, InTime, OutTime, StudentMasterID, UserID, SubUserID, FYear) " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(logDate) is Null THEN 0 ELSE 1 END) status " + " , Min(logDate), MAX(logDate), s.StudentMasterID, " + util.GetUserInsertQry() + " FROM tblStudentMaster s " + " LEFT OUTER JOIN " + esslTbl + " e ON e.UserID=s.IDCardNO " + " AND Day(logDate) = " + dey + " and month(logDate) = " + mnth + Util_BLL.GetUserWhereCondition(Util_BLL.User, "2014", "s") + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outTime is Null AND YearNo = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID " + " UNION " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(logDate) is Null THEN 0 ELSE 1 END) status " + " , Min(logDate), MAX(logDate), s.StudentMasterID, " + util.GetUserInsertQry() + " FROM tblStudentMaster s " + " INNER JOIN " + esslTbl + " e ON e.UserID=s.IDCardNO" + " AND Day(logDate) = " + dey + " and month(logDate) = " + mnth + " AND year(logDate) = " + yr + Util_BLL.GetUserWhereCondition(Util_BLL.User, "2014", "s") + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outtime = logDate AND year(logDate) = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID "; //SendSMSToParents.WriteErrorLog("qry Process Machine " + qry); site.Execute(qry); }
public void DeleteClasses(DBSite site, string ids) { string qry = "DELETE FROM tblStudentMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND ClassID IN (" + ids + ")"; site.Execute(qry); qry = "DELETE FROM tblClassMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND ClassMasterID IN (" + ids + ")"; site.Execute(qry); }
public void AddStudentDataInAttdencance(DBSite site, int yr, int mnth, int dey, int isHoliday) { // Insert records in tblAttendance when page loads from attendance machine string qry = " INSERT INTO tblAttendance(YearNo, MonthNo, DayNo, HolidayId " + ", status, InTime, OutTime, StudentMasterID, UserID, SubUserID, FYear) " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", 0 " + " , null, null, s.StudentMasterID, " + util.GetUserInsertQry() + " FROM tblStudentMaster s " + " LEFT OUTER JOIN tblAttendance a ON a.StudentMasterID = s.StudentMasterID " + " AND DayNo = " + dey + " and monthNo = " + mnth + " and YearNo = " + yr + Util_BLL.GetUserWhereCondition(Util_BLL.User, "2014", "s") + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND YearNo = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) "; //SendSMSToParents.WriteErrorLog("AddStudentDataInAttdencance " + qry); site.Execute(qry); }
public List <User> GetMatchedUsers(DBSite site, string value_to_search) { List <User> users = new List <User>(); User user = null; string qry = " SELECT " + " UserInfoId" + ", UserName" + ", Email" + ", SenderID" + ", UserPassword" + ", CreationDate" + ", AmountPaid" + ", StartDate" + ", EndDate" + ", Address" + ", City" + ", Country" + ", Phone" + ", Mobile" + ", TinNumber" + ", SalesTaxNumber" + ", CSTNumber" + ", UserType" + ", Remarks"; qry += " FROM tblUserInfo "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // qry += " WHERE "; //qry += " WHERE UserID = " + User.userID + " AND FYear= " + User.fYear + " AND "; qry += "AND (( UserName LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Email LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Address LIKE '%" + value_to_search + "%' ) OR"; qry += " ( City LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Country LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Phone LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Mobile LIKE '%" + value_to_search + "%' ) OR"; qry += " ( TinNumber LIKE '%" + value_to_search + "%' ) OR"; qry += " ( SalesTaxNumber LIKE '%" + value_to_search + "%' ) OR"; if (util.IsDate(value_to_search)) { qry += " ( CreationDate = '" + value_to_search + "' ) OR"; qry += " ( EndDate = '" + value_to_search + "' ) OR"; qry += " ( StartDate = '" + value_to_search + "' ) OR"; } if (util.IsNumber(value_to_search)) { qry += " ( AmountPaid = '" + value_to_search + "' ) OR "; } qry += " ( CSTNumber LIKE '%" + value_to_search + "%' ) "; qry += ")"; DataTable dt = site.ExecuteSelect(qry); foreach (DataRow row in dt.Rows) { user = new User(); user.UserId = util.CheckNullInt(row["UserInfoId"]); user.UserName = util.CheckNull(row["UserName"]); user.EmailId = util.CheckNull(row["Email"]); user.SenderId = util.CheckNull(row["SenderID"]); user.UserPassword = util.CheckNull(row["UserPassword"]); user.AmountPaid = util.CheckNullDecimal(row["AmountPaid"]); user.CreationDate = ((DateTime)row["CreationDate"]).ToShortDateString(); user.StartDate = ((DateTime)row["StartDate"]).ToShortDateString(); user.EndDate = ((DateTime)row["EndDate"]).ToShortDateString(); user.Address = util.CheckNull(row["Address"]); user.City = util.CheckNull(row["City"]); user.Country = util.CheckNull(row["Country"]); user.Phone = util.CheckNull(row["Phone"]); user.Mobile = util.CheckNull(row["Mobile"]); user.TinNumber = util.CheckNull(row["PinNumber"]); user.SalesTaxNumber = util.CheckNull(row["SalesTaxNumber"]); user.CSTNumber = util.CheckNull(row["CSTNumber"]); user.UserType = util.CheckNullInt(row["UserType"]); user.Remarks = util.CheckNull(row["Remarks"]); users.Add(user); } return(users); }
public List <AttendanceEntity> GetAttendance2MAy19(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 " + " , (SELECT MIN(InTime) FROM tblAttendance " + Util_BLL.GetUserWhereCondition("a") + " AND YearNo = " + yearNo + " AND MonthNo = " + theMonth + " AND DayNo = " + theDay + " AND studentmasterid=s.studentMasterID " + " GROUP BY StudentMasterID, YearNo, monthno, dayno, userId, FYear )InTime " + " , OutTime, Status " + ", IsPosted " + " , (select MAX(IsSMSSent) FROM tblAttendance where dayNo=" + theDay + " AND STUDENTMASTERID = s.studentMasterID GROUP BY StudentMasterID) 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; } qry += " AND AttendanceID In (select MAx(AttendanceID) " + " FROM tblAttendance b" + Util_BLL.GetUserWhereCondition("b") + " GROUP BY StudentMasterID, YearNo, monthno, dayno, userId, FYear ) " + " ORDER BY ClassOrder,sectionName, StudentName "; SendSMSToParents.WriteErrorLog("GetAttendance qry " + qry); 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); } return(adList); }
//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); }