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);
}
//-------------- savew subuser information -----------------
public void SaveSubuserInfo(DBSite site, SubuserEntity subuser)
{
int user_state = subuser.UserState == "1" ? 1 : 0;
string subuser_info_qry = " INSERT INTO tblSubuser ("
+ "SubuserName"
+ ", SubuserCreationDate"
+ ", SubuserPassword"
+ ", EmailId"
+ ", Designation"
+ ", Address"
+ ", City"
+ ", Mobile"
+ ", Active"
+ ", UserId"
+ ")";
subuser_info_qry += " VALUES ("
+ "'" + subuser.SubuserName + "'"
+ ", '" + subuser.CreationDate + "'"
+ ", '" + subuser.Password + "'"
+ ", '" + subuser.EmailId + "'"
+ ", '" + subuser.Designation + "'"
+ ", '" + subuser.Address + "'"
+ ", '" + subuser.City + "'"
+ ", '" + subuser.Mobile + "'"
+ ", " + user_state
+ ", " + Util_BLL.User.UserId //---- User ID in Currently Logged In ---------------
+ " )";
site.Execute(subuser_info_qry); //------------ Enter subuser information ------------------
subuser_info_qry = " SELECT SubuserId FROM tblSubuser"
+ " WHERE EmailId='" + subuser.EmailId + "'"
+ " AND UserId=" + Util_BLL.User.UserId;
string subuser_id = util.CheckNull(site.ExecuteSelect(subuser_info_qry).Rows[0]["SubuserId"]); //-- subuserId query --------------
foreach (Permission permission in subuser.Permissions)
{
subuser_info_qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", SubuserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ Util_BLL.User.UserId //------ user id of Current User --------
+ ", " + subuser_id
+ ", " + permission.PermissionId
+ ")";
site.Execute(subuser_info_qry);
}
foreach (ClassMasterBLL.ClassMasterEntity clas in subuser.TeacherClasses)
{
subuser_info_qry = " INSERT INTO tblTeacherClasses"
+ "("
+ "UserId"
+ ", SubuserId"
+ ", ClassId"
+ ")"
+ " VALUES("
+ Util_BLL.User.UserId //------ user id of Current User --------
+ ", " + subuser_id
+ ", " + clas.ClassMasterId
+ ")";
site.Execute(subuser_info_qry);
}
foreach (SubjectMasterBLL.SubjectMasterEntity sub in subuser.TeacherSubjects)
{
subuser_info_qry = " INSERT INTO tblTeacherSubjects"
+ "("
+ "UserId"
+ ", SubuserId"
+ ", SubjectId"
+ ")"
+ " VALUES("
+ Util_BLL.User.UserId
+ ", " + subuser_id
+ ", " + sub.SubjectMasterId
+ ")";
site.Execute(subuser_info_qry);
}
}
public List <User> GetUsers(DBSite site, string user_id = "")
{
List <User> user_list = 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"
+ ", IsUnicode"
+ ", NumberOfSubusers"
+ ", Remarks";
qry += " FROM tblUserInfo";
if (user_id != "")
{
qry += " WHERE UserInfoId=" + user_id;
}
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["TinNumber"]);
user.SalesTaxNumber = util.CheckNull(row["SalesTaxNumber"]);
user.CSTNumber = util.CheckNull(row["CSTNumber"]);
user.UserType = util.CheckNullInt(row["UserType"]);
user.IsUnicode = util.CheckNullInt(row["IsUnicode"]) == 1 ? true : false;
user.Remarks = util.CheckNull(row["Remarks"]);
user.NumberOFSubusers = util.CheckNullInt(row["NumberOfSubusers"]);
user.fYear = util.CheckNullInt(util.GetHomeSetting(site, "CurrentFYear"));
user.Subusers = GetSubusers(site, user.UserId + ""); //------ get subuser list --------
user.Permissions = GetPermissions(site, user.UserId + ""); //----- get permission list --------
user_list.Add(user);
}
return(user_list);
}
//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);
}
