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 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);
}
//----- 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 bool IsDuplicate(DBSite site, string value)
{
string qry = " SELECT UserInfoId FROM tblUserInfo"
+ " WHERE UserName='******'";
return(site.ExecuteSelect(qry).Rows.Count > 0);
}
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);
}
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 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);
}
//---------- 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 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 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);
}
}
//-------------- 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 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 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);
}
