Beispiel #1
0
        public bool IsDuplicate(DBSite site, string value)
        {
            string qry = "  SELECT UserInfoId FROM tblUserInfo"
                         + " WHERE UserName='******'";

            return(site.ExecuteSelect(qry).Rows.Count > 0);
        }
Beispiel #2
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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }
Beispiel #7
0
        public List <AttendanceEntity> GetNonPostedAttendance(DBSite site)
        {
            List <AttendanceEntity> atdList = new List <AttendanceEntity>();


            return(atdList);
        }
Beispiel #8
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);
        }
Beispiel #9
0
        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);
        }
Beispiel #10
0
        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);
        }
Beispiel #11
0
        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);
        }
Beispiel #12
0
        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);
        }
Beispiel #13
0
        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);
        }
Beispiel #14
0
        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);
            }
        }
Beispiel #15
0
        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);
        }
Beispiel #16
0
        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);
        }
Beispiel #17
0
        //------------  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);
            }
        }
Beispiel #18
0
        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);
        }
Beispiel #19
0
        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);
            }
        }
Beispiel #20
0
        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);
        }
Beispiel #21
0
        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--------
        }
Beispiel #22
0
        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);
        }
Beispiel #23
0
        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);
        }
Beispiel #24
0
        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);
        }
Beispiel #25
0
        //----------  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);
        }
Beispiel #26
0
        //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);
        }
Beispiel #27
0
        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);
        }
Beispiel #28
0
        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);
        }
Beispiel #29
0
        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);
            }
        }
Beispiel #30
0
        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);
        }