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