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