Beispiel #1
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);
        }
Beispiel #2
0
        private void timer1_Tick(object sender, ElapsedEventArgs e)
        {
            // witer code to do some job as per ur need

            // send sms for Abentee to be sent on 9:15 AM

            Util_BLL      util = new Util_BLL();
            AttendanceBll aBLL = new AttendanceBll();

            //aBLL.ProcessMachineAttendance(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), 0);

            // Check if attendance table has students per day if not then add
            aBLL.AddStudentDataInAttdencance(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), 0);

            List <AttendanceBll.AttendanceEntity> absentees = new List <AttendanceBll.AttendanceEntity>();

            absentees = aBLL.GetAttendance(site, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, -1, false);
            foreach (AttendanceBll.AttendanceEntity abs in absentees)
            {
                string logdate = "";
                // Update InTime
                if (abs.Status == 0 && abs.IdcardNo.Length > 1)
                {
                    // get logdate
                    logdate = aBLL.GetlogDate(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), abs.AttendanceId, abs.IdcardNo, "INTIME");

                    if (logdate.Length > 0)
                    {
                        aBLL.UpDateInTime_OutTime(site, logdate, abs.AttendanceId, 1, "INTIME");
                        //SendSMSToParents.WriteErrorLog("UPDATE INTIME  " + abs.IdcardNo);
                    }
                }
                // OUTTIME
                if (abs.Status >= 0 && abs.IdcardNo.Length > 1 && abs.IsSMSSent == 1)
                {
                    logdate = aBLL.GetlogDate(site, util.CheckNullInt(DateTime.Now.Year), util.CheckNullInt(DateTime.Now.Month), util.CheckNullInt(DateTime.Now.Day), abs.AttendanceId, abs.IdcardNo, "OUTTIME");

                    if (logdate.Length > 0)
                    {
                        aBLL.UpDateInTime_OutTime(site, logdate, abs.AttendanceId, 1, "OUTTIME");
                        //SendSMSToParents.WriteErrorLog("UPDATE OUTTIME " + abs.IdcardNo);
                    }
                }
            }


            // get students again after updating
            List <AttendanceBll.AttendanceEntity> absentees2 = new List <AttendanceBll.AttendanceEntity>();

            absentees2 = aBLL.GetAttendance(site, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, -1, false);
            string msg_str = "";

            foreach (AttendanceBll.AttendanceEntity absente in absentees2)
            {
                if (absente.Status > 0)
                {
                    if (absente.Status == 1 && absente.IsSMSSent == 0 && absente.InTime.Length > 0)
                    {
                        msg_str = "Dear Parents, Your child " + absente.StudentName + " has arrived in school on " + absente.InTime + ".";
                        util.SendSms(absente.MobileNo, msg_str, 1, "WHSLMC", false);
                        util.SaveSentSMSToDB(site, absente.MobileNo, msg_str, false, 36);
                        aBLL.PostSMS(site, absente.StudentId, 1);
                        SendSMSToParents.WriteErrorLog("set isSMSSent to 1 " + absente.StudentName);
                    }

                    // Exit

                    if (absente.Status == 1 && absente.IsSMSSent == 1 && absente.OutTime.Length > 0)
                    {
                        msg_str = "Dear Parents, Your child " + absente.StudentName + " has left the school on " + absente.OutTime + ".";
                        util.SendSms(absente.MobileNo, msg_str, 1, "WHSLMC", false);
                        util.SaveSentSMSToDB(site, absente.MobileNo, msg_str, false, 36);
                        aBLL.PostSMS(site, absente.StudentId, 2);
                        SendSMSToParents.WriteErrorLog("set isSMSSent to 2 " + absente.StudentName);
                    }
                }
            }

            SendSMSToParents.WriteErrorLog("Timer ticked and some job has been done sucessesfully");
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        //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);
        }