//------------ 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 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 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 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 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 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 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); }
//---- Edit subuser information ----------------------------- public void EditSubuserInfo(DBSite site, SubuserEntity subuser, string subuser_id) { string qry = " UPDATE tblSubuser SET " + "SubuserName = '******'" + ", SubuserCreationDate = '" + subuser.CreationDate + "'" + ", SubuserPassword = '******'" + ", EmailId = '" + subuser.EmailId + "'" + ", Designation = '" + subuser.Designation + "'" + ", Address = '" + subuser.Address + "'" + ", City = '" + subuser.City + "'" + ", Mobile = '" + subuser.Mobile + "'" + ", Active = " + subuser.UserState; qry += " WHERE SubuserId=" + subuser_id; site.Execute(qry); // ---- update subuser information --------------- qry = " DELETE FROM tblUserPermissions" + " WHERE UserId=" + Util_BLL.User.UserId + " AND SubuserId=" + subuser_id; site.Execute(qry); //------ Delete all permissions to selected Subuser ------ //----- update permissions to Subuser -------------------- foreach (Permission permission in subuser.Permissions) { qry = " INSERT INTO tblUserPermissions" + "(" + "UserId" + ", SubuserId" + ", PermissionId" + ")" + " VALUES(" + Util_BLL.User.UserId //------ user id of Current User -------- + ", " + subuser_id + ", " + permission.PermissionId + ")"; site.Execute(qry); } qry = " DELETE FROM tblTeacherClasses" + " WHERE UserId=" + Util_BLL.User.UserId + " AND SubuserId=" + subuser_id; site.Execute(qry); foreach (ClassMasterBLL.ClassMasterEntity clas in subuser.TeacherClasses) { qry = " INSERT INTO tblTeacherClasses" + "(" + "UserId" + ", SubuserId" + ", ClassId" + ")" + " VALUES(" + Util_BLL.User.UserId //------ user id of Current User -------- + ", " + subuser_id + ", " + clas.ClassMasterId + ")"; site.Execute(qry); } qry = " DELETE FROM tblTeacherSubjects" + " WHERE UserId=" + Util_BLL.User.UserId + " AND SubuserId=" + subuser_id; site.Execute(qry); foreach (SubjectMasterBLL.SubjectMasterEntity sub in subuser.TeacherSubjects) { qry = " INSERT INTO tblTeacherSubjects" + "(" + "UserId" + ", SubuserId" + ", SubjectID" + ")" + " VALUES(" + Util_BLL.User.UserId //------ user id of Current User -------- + ", " + subuser_id + ", " + sub.SubjectMasterId + ")"; 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 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); } }