public bool IsDuplicate(DBSite site, string value)
{
string qry = " SELECT UserInfoId FROM tblUserInfo"
+ " WHERE UserName='******'";
return(site.ExecuteSelect(qry).Rows.Count > 0);
}
//----- get permissions ----------------------------------------------
public List <Permission> GetPermissions(DBSite site, string subuser_id)
{
List <Permission> permission_list = new List <Permission>();
Permission permission = null;
string qry = " SELECT "
+ " p.PermissionId"
+ ", p.Module"
+ ", p.ASPXPageName"
+ " FROM tblUserPermissions as up"
+ " LEFT OUTER JOIN tblPermission as p"
+ " ON up.PermissionId = p.PermissionId"
+ " WHERE up.UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
DataTable permission_table = site.ExecuteSelect(qry);
foreach (DataRow row in permission_table.Rows)
{
permission = new Permission();
permission.PermissionId = util.CheckNullInt(row["PermissionId"]);
permission.ModuleName = util.CheckNull(row["Module"]);
permission.ASPXPageName = util.CheckNull(row["ASPXPageName"]);
permission_list.Add(permission);
}
return(permission_list);
}
public string GetMobileNos(DBSite site, List <AttendanceEntity> attdList)
{
string mobileNos = "";
string attdIds = "";
foreach (AttendanceEntity atd in attdList)
{
attdIds += atd.AttendanceId + ", ";
}
attdIds += "-1111";
string qry = "SELECT MobileF "
+ " from tblAttendance a "
+ " INNER JOIN tblStudentMaster s ON s.StudentMasterID = a.StudentMasterID "
+ " WHERE attendanceID IN (" + attdIds + ") ";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
mobileNos += util.CheckNull(dr["MobileF"]) + ", ";
}
return(mobileNos);
}
public List <ClassMasterBLL.ClassMasterEntity> GetTeacherClasses(DBSite site, string subuser_id)
{
List <ClassMasterBLL.ClassMasterEntity> class_list = new List <ClassMasterBLL.ClassMasterEntity>();
ClassMasterBLL.ClassMasterEntity clas = null;
string qry = " SELECT "
+ " tc.classId"
+ " FROM tblTeacherClasses tc "
+ " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterID = tc.ClassID "
+ " WHERE tc.UserId=" + Util_BLL.User.UserId
+ " AND tc.SubuserId=" + subuser_id;
DataTable class_table = site.ExecuteSelect(qry);
foreach (DataRow row in class_table.Rows)
{
clas = new ClassMasterBLL.ClassMasterEntity();
clas.ClassMasterId = util.CheckNullInt(row["ClassID"]);
class_list.Add(clas);
}
return(class_list);
}
public List <SubjectMasterBLL.SubjectMasterEntity> GetTeacherSubjects(DBSite site, string subuser_id)
{
List <SubjectMasterBLL.SubjectMasterEntity> sub_list = new List <SubjectMasterBLL.SubjectMasterEntity>();
SubjectMasterBLL.SubjectMasterEntity sub = null;
string qry = " SELECT "
+ " ts.SubjectID"
+ " FROM tblTeacherSubjects ts "
+ " LEFT OUTER JOIN tblSubjectMaster s ON s.SubjectMasterID = ts.SubjectID "
+ " WHERE ts.UserId=" + Util_BLL.User.UserId
+ " AND ts.SubuserId=" + subuser_id;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
sub = new SubjectMasterBLL.SubjectMasterEntity();
sub.SubjectMasterId = util.CheckNullInt(row["subjectId"]);
sub_list.Add(sub);
}
return(sub_list);
}
public int GetPunchCount(DBSite site, int IDCard, int day, int mnth, int yr, bool isExit)
{
int punchCount = 0;
string tbl = "etimetracklite1.dbo.DeviceLogs_" + mnth + "_" + yr;
string whereStr = " WHERE USerID=" + IDCard
+ " AND DAY(logDate) = " + day
+ " AND MONTH(logDate) = " + mnth
+ " AND Year(logDate) = " + yr;
string qry = " SELECT COUNT(*) theCount FROM " + tbl + whereStr;
// When exit : at least 1 hr gap between first punch and the second punch
if (isExit)
{
qry = " SELECT count(logdate) theCount FROM " + tbl + whereStr
+ " AND LogDate > ( SELECT dateadd(MINUTE, 60, MIN(logdate)) "
+ " FROM " + tbl + whereStr + ")";
}
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
punchCount = util.CheckNullInt(dr["theCount"]);
}
return(punchCount);
}
public List <AttendanceEntity> GetNonPostedAttendance(DBSite site)
{
List <AttendanceEntity> atdList = new List <AttendanceEntity>();
return(atdList);
}
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 int SaveSentSMSToDB(DBSite site, string mobileNos, string msg, bool isUnicode, int UserId)
{
/// 777
string mobileNoStr = "0";
string qry = "";
int count = 0;
string[] arr = mobileNos.Split(',');
int msg_count = 0;
for (int i = 0; i < arr.Length; i++)
{
mobileNoStr = arr[i].Trim();
if (mobileNoStr.Length > 9)
{
/// break sms message in into tokens of 157 words
List <string> strparts = GetMsgInParts(msg, isUnicode);
msg_count = strparts.Count;
qry = "INSERT INTO tblSMSSent(SMSText, MsgCount, mobileNo, UserID)"
+ " VALUES ("
+ "N'" + msg + "'"
+ ", " + msg_count + ""
+ ", '" + mobileNoStr + "'"
+ ", " + UserId
+ ")";
site.Execute(qry);
count += 1;
// if message is long then send in parts
//foreach (string msg_str in strparts)
//{
// qry = "INSERT INTO tblSMSSent(SMSText, mobileNo, UserID)"
// + " VALUES ("
// + "N'" + msg_str + "'"
// + ", '" + mobileNoStr + "'"
// + ", " + usr.UserId
// + ")";
// site.Execute(qry);
// count += 1;
//}
}
}
return(count);
}
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 UpDateInTime_OutTime(DBSite site, string logdate, int attendanceId, int status, string InTime_OutTime)
{
string qry = "";
qry = " UPDATE tblAttendance "
+ " SET " + InTime_OutTime + " = '" + logdate + "'"
+ ", status = " + status
+ " WHERE AttendanceID = " + attendanceId;
//SendSMSToParents.WriteErrorLog("UpDateInTime_OutTime " + qry);
site.Execute(qry);
}
public void PostSMS(DBSite site, int atId, int smsStatus)
{
// update tblAttendance
string qry = "";
qry = " UPDATE tblAttendance SET "
+ " IsSMSSent =" + smsStatus
+ " WHERE studentMasterID = " + atId;
site.Execute(qry);
}
public int GetSMSBalance2(DBSite site, int userId)
{
// 7777
int smsBalance = 0;
string qry = "";
qry = "SELECT SMSBalance From tblUserInfo "
+ " WHERE USerInfoID = " + userId;
DataTable dt = site.ExecuteSelect(qry);
int smsUserBalance = 0;
foreach (DataRow row in dt.Rows)
{
smsUserBalance = CheckNullInt(row["SMSBalance"]);
}
if (smsUserBalance == 0)
{
smsBalance = -99;
return(smsBalance);
}
qry = "SELECT t1.userID, purchased, IsNull(smsSent, 0) SMSSent "
+ " , T1.Purchased - IsNull(smsSent, 0) as BalanceOnHand "
+ " FROM "
+ "( "
+ " select userID, SUM(SmsPurchasedCount) Purchased "
+ " FROM tblSMSPurchased "
+ " GROUP BY UserID "
+ " ) T1 "
+ " LEFT OUTER JOIN "
+ " ( "
+ " select userID, Count(*)SMSSent "
+ " FROM tblSMSSent "
+ " GROUP BY UserID "
+ " ) T2 ON t2.userId = t1.UserID "
+ " WHERE t1.USerID = " + userId;
dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
smsBalance = CheckNullInt(row["BalanceOnHand"]);
}
return(smsBalance);
}
public void PostSMS(DBSite site, List <AttendanceEntity> attdList)
{
// update tblAttendance
string qry = "";
foreach (AttendanceEntity atd in attdList)
{
qry = " UPDATE tblAttendance SET "
+ " IsSMSSent = 1 "
+ " WHERE AttendanceId = " + atd.AttendanceId;
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 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);
}
//------------ Edit User Info ----------------------------------------
public void EditUserInfo(DBSite site, User user, string user_id)
{
string qry = " UPDATE tblUserInfo SET "
+ " UserName ='******'"
+ ", Email ='" + user.EmailId + "'"
+ ", UserPassword ='******'"
+ ", CreationDate='" + user.CreationDate + "'"
+ ", AmountPaid = " + user.AmountPaid
+ ", StartDate ='" + user.StartDate + "'"
+ ", EndDate ='" + user.EndDate + "'"
+ ", Address ='" + user.Address + "'"
+ ", City ='" + user.City + "'"
+ ", Country ='" + user.Country + "'"
+ ", Phone ='" + user.Phone + "'"
+ ", Mobile ='" + user.Mobile + "'"
+ ", TinNumber ='" + user.TinNumber + "'"
+ ", SalesTaxNumber ='" + user.SalesTaxNumber + "'"
+ ", CSTNumber ='" + user.CSTNumber + "'"
+ ", UserType = 1"
+ ", Remarks ='" + user.Remarks + "'"
+ ", NumberOfSubusers=" + user.NumberOFSubusers
+ " WHERE UserInfoId=" + user_id;
site.Execute(qry); //---------- update user information -----------
qry = " DELETE FROM tblUserPermissions"
+ " WHERE UserId=" + user_id;
site.Execute(qry); //----- delete all permissions with current user -----
//---------- then insert all the permissions now granted---------
foreach (Permission permission in user.Permissions)
{
qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ user_id
+ ", "
+ permission.PermissionId
+ ")";
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 PostAttendance(DBSite site, List <AttendanceEntity> attdList)
{
// update tblAttendance
string qry = "";
foreach (AttendanceEntity atd in attdList)
{
qry = " UPDATE tblAttendance SET "
+ " Status = " + atd.Status
+ ", IsPosted = 1 "
+ " WHERE AttendanceId = " + atd.AttendanceId;
site.Execute(qry);
}
}
public bool IsSMSSent(DBSite site, int attdId)
{
bool isSMSAlreadySent = false;
string qry = "select IsSMSSent from tblAttendance where attendanceID =" + attdId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
isSMSAlreadySent = util.CheckNullInt(dr["IsSMSSent"]) == 1;
}
return(isSMSAlreadySent);
}
public void DeleteUser(DBSite site, string user_id)
{
string qry = " DELETE FROM tblUserInfo"
+ " WHERE UserInfoId IN (" + user_id + ")";
site.Execute(qry); //---- delete all user's from user information table ---------
qry = " DELETE FROM tblSubuser WHERE UserId IN (" + user_id + ")";
site.Execute(qry); //-------- delete all subusers of all selected users ------------------
qry = " DELETE FROM tblUserPermissions"
+ " WHERE UserId IN (" + user_id + ")";
site.Execute(qry); //----------- delete all permissions from User Permission table for all selected user's and their corressponding subusers--------
}
public string GetHomeSetting(DBSite site, string colName)
{
string colValue = "";
string checkQry = " SELECT top 1 ColValue FROM tblHome ";
checkQry += " WHERE ColName = '" + colName + "'";
DataTable dt = site.ExecuteSelect(checkQry);
foreach (DataRow dr in dt.Rows)
{
colValue = CheckNull(dr["colValue"]);
}
return(colValue);
}
public string GetClassName(DBSite site, int attdId)
{
string clasName = "";
string qry = " SELECT className "
+ " from tblClassMaster c "
+ " LEFT OUTER JOIN tblStudentMaster s ON s.classID = c.ClassMasterID "
+ " LEFT OUTER JOIN tblAttendance a ON a.StudentMasterID = s.StudentMasterID "
+ " WHERE a.attendanceID =" + attdId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
clasName = util.CheckNull(dr["className"]);
}
return(clasName);
}
public bool IsSubUserHasPermissionForThePage(DBSite site, int subuser_id, string aspxPage)
{
bool hasPermission = false;
List <Permission> persList = this.GetPermissions(site, subuser_id.ToString());
foreach (Permission perm in persList)
{
if (perm.ASPXPageName == aspxPage)
{
hasPermission = true;
break;
}
}
return(hasPermission);
}
//---------- Get subusers of user -------------------
public List <SubuserBLL.SubuserEntity> GetSubusers(DBSite site, string user_id)
{
List <SubuserBLL.SubuserEntity> subuser_list = new List <SubuserBLL.SubuserEntity>();
SubuserBLL.SubuserEntity subuser = null;
string qry = " SELECT "
+ "SubuserId"
+ ", SubuserName"
+ ", SubuserCreationDate"
+ ", SubuserPassword"
+ ", EmailId"
+ ", Active"
+ ", Designation"
+ ", Address"
+ ", City"
+ ", Mobile"
+ " FROM tblSubuser"
+ " WHERE UserId=" + user_id;
DataTable subuser_table = site.ExecuteSelect(qry);
foreach (DataRow row in subuser_table.Rows)
{
subuser = new SubuserBLL.SubuserEntity();
subuser.SubuserId = util.CheckNullInt(row["SubuserId"]);
subuser.SubuserName = util.CheckNull(row["SubuserName"]);
subuser.CreationDate = Convert.ToDateTime(row["SubuserCreationDate"]).ToShortDateString();
subuser.Password = util.CheckNull(row["SubuserPassword"]);
subuser.EmailId = util.CheckNull(row["EmailId"]);
subuser.UserState = util.CheckNull(row["Active"]);
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_list.Add(subuser);
}
return(subuser_list);
}
//public List<SchoolClass> GetClasses()
//{
// List<SchoolClass> class_list = new List<SchoolClass>();
// DBSite site = new DBSite();
// string qry = " SELECT "
// + " ClassMasterID"
// + ", ClassName"
// + " FROM tblClassMaster "
// + " WHERE UserId=" + Util_BLL.User.UserId
// + " ORDER By ClassOrder ";
// DataTable dt = site.ExecuteSelect(qry);
// SchoolClass the_class;
// foreach (DataRow row in dt.Rows)
// {
// the_class = new SchoolClass();
// the_class.SchoolClassId = util.CheckNullInt(row["ClassMasterID"]);
// the_class.SchoolClassName = util.CheckNull (row["ClassName"]);
// class_list.Add(the_class);
// }
// return class_list;
//}
public string GetMobileNo(DBSite site, int attdId)
{
string mobileNos = "";
string qry = "SELECT MobileF "
+ " from tblAttendance a "
+ " INNER JOIN tblStudentMaster s ON s.StudentMasterID = a.StudentMasterID "
+ " WHERE attendanceID = " + attdId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
mobileNos += util.CheckNull(dr["MobileF"]) + ", ";
}
return(mobileNos);
}
public List <int> GetMobileListForDuplicateSMS(DBSite site, string mobileNos, string msg, int UserId)
{
// if msg is same in last 8 hours then stop the sms of same mobileNos
List <int> duplicate_sms_mob_list = new List <int>();
List <int> mob_List = new List <int>();
mob_List = mobileNos.Split(',').Select(int.Parse).ToList();
string qry = "SELECT MobileNo, SMSText FROM tblSMSSent "
+ " WHERE userId=" + UserId
+ " AND CreatedAT > dateadd(hh,-8,getdate()) ";
DataTable dt = site.ExecuteSelect(qry);
DataRow row = null;
foreach (int mob in mob_List)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
row = dt.Rows[j];
if (mob == CheckNullInt(row["MobileNo"]))
{
// sms is same
if (msg == CheckNull(row["SMSText"]))
{
duplicate_sms_mob_list.Add(mob);
}
}
}
}
//9999
return(duplicate_sms_mob_list);
}
public string GetlogDate(DBSite site, int yr, int mnth, int dey, int attendanceId, string IdCardNo, string InTime_OutTime)
{
string log_date = "";
string subQry = "";
string esslTbl = "etimetracklite1.dbo.DeviceLogs_" + mnth + "_" + yr;
string whereQry = " WHERE day(logdate)=" + dey
+ " AND MONTH(logdate)=" + mnth
+ " AND YEAR(LogDate) = " + yr
+ " AND UserId = " + IdCardNo;
if (InTime_OutTime == "INTIME")
{
subQry = " SELECT MIN(logdate) punch "
+ " FROM " + esslTbl
+ whereQry;
}
else if (InTime_OutTime == "OUTTIME")
{
subQry = " SELECT MIN(logdate) punch "
+ " FROM " + esslTbl
+ whereQry
+ " AND LogDate > "
+ " (SELECT dateadd(MINUTE, 60, MIN(logdate)) "
+ " FROM " + esslTbl
+ whereQry + ")";
}
DataTable dt = site.ExecuteSelect(subQry);
foreach (DataRow dr in dt.Rows)
{
log_date = util.CheckNull(dr["punch"]);
}
//SendSMSToParents.WriteErrorLog("logdate qry " + subQry);
return(log_date);
}
public void DeleteSubuserInfo(DBSite site, string subuser_ids)
{
string delete_subuser_qry = " DELETE FROM tblSubuser "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND SubuserId IN (" + subuser_ids + " )";
site.Execute(delete_subuser_qry); //------------- Delete Subuser Information ---------------
string[] subuserIds = subuser_ids.Split(','); //--------- Get Array Of SubuserId's -----------
string delete_associated_permission_qry;
//------------- Delete Associated Permissions Of Subuser -----------------------------------
foreach (string subuser_id in subuserIds)
{
delete_associated_permission_qry = " DELETE FROM tblUserPermissions "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
site.Execute(delete_associated_permission_qry);
}
foreach (string subuser_id in subuserIds)
{
delete_associated_permission_qry = " DELETE FROM tblTeacherClasses "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
site.Execute(delete_associated_permission_qry);
}
foreach (string subuser_id in subuserIds)
{
delete_associated_permission_qry = " DELETE FROM tblTeacherSubjects "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
site.Execute(delete_associated_permission_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);
}