public List<Program_tbl> getprogramslist()
 {
     db = new CollegeERPDBEntities();
     
         var programs = db.Program_tbl.ToList();
         return programs;
     
 }

 public void AddQuestionare(int UserID)
 {
     db = new CollegeERPDBEntities();
     
     AdminMails_tbl obj = new AdminMails_tbl {SenderID=UserID,Message="<a href=DownloadReport.aspx?senderid='"+UserID+"'>Download Questionair</a>",Subject="SuggestionRequest",Status=0,Date=DateTime.Now.Date};
     db.AdminMails_tbl.Add(obj);
     db.SaveChanges();
 }

 public List<FormSections_tbl> getformSectionlist()
 {
     using (db = new CollegeERPDBEntities())
     {
         var Sections = db.FormSections_tbl.ToList();
         return Sections;
     }
 }

 public Candidate_tbl LoginChek(string username, string password)
 {
    db = new CollegeERPDBEntities();
 
         var candidate = db.Candidate_tbl.Where(x => x.Username == username && x.Password == password).FirstOrDefault();
         
         return candidate;
  
 }

       public void AddRegistrationNotice(Mails_tbl t)
       {
           db = new CollegeERPDBEntities();

           db.Mails_tbl.Add(t);
           db.SaveChanges();


       }

 public int AddCandidate(Candidate_tbl candidate)
 {
     db = new CollegeERPDBEntities();
     
         db.Candidate_tbl.Add(candidate);
         db.SaveChanges();
         return candidate.ID;
     
 }

       public void addQuestion(string Question,string[] answers)
       {
           using (db = new CollegeERPDBEntities())
           {
               Questionaire_tbl qstn = new Questionaire_tbl { Question = Question };
               db.Questionaire_tbl.Add(qstn);
               db.SaveChanges();
               foreach (string ans in answers)
               {
                   Answers_tbl anss = new Answers_tbl { Q_ID = qstn.Q_ID, Answer = ans };
                   db.Answers_tbl.Add(anss);
                   db.SaveChanges();
               }
           }

       }

       public void addFormField(Forms_tbl form,string [] options)
       {
           using (db = new CollegeERPDBEntities())
           {
               db.Forms_tbl.Add(form);
               db.SaveChanges();

               if(form.FormControl!="Text")
               {
                   foreach (string option in options)
                       db.ControlOptions_tbl.Add(new ControlOptions_tbl { FieldID=form.ID,optionvalue=option});
               }
                   db.SaveChanges();
           }


       }

    public List<Results_tbl> getresult(int stdid,string term)
    {

        db = new CollegeERPDBEntities();
        var metricno=db.AddmissionList_tbl.Where(x=>x.UserID==stdid).FirstOrDefault().MetricNo;
        string year = DateTime.Now.Year.ToString();
        return db.Results_tbl.Where(x => x.MetricNo == metricno && x.Year==year && x.ExamType==term).ToList();
        
    }

 //txtHomeaddress.Text, int.Parse(dropdownSto.SelectedValue),int.Parse(dropdownLocalGovtarea.SelectedValue),Emailtxt.Text,dropdownDay.SelectedItem.Text + "-" + dropdownMonth.SelectedItem.Text + "-" + dropdownyears.SelectedItem.Text,Phonetxt.Text
 public void updatestudtent(int stdid,string Name,int prid,int semester,string feediscount,string address,int sto,int area,string email,string dob,string phone)
 {
      db = new CollegeERPDBEntities();
     var student= db.StudentInfo_tbl.Where(x=>x.UserId==stdid).FirstOrDefault();
     student.ProgramID = prid;
     student.Semester = semester;
     student.FeeDiscount = feediscount;
     student.Candidate_tbl.Name = Name;
     student.Candidate_tbl.ProgrammeID = prid;
     student.Candidate_tbl.HomeAdress = address;
     student.Candidate_tbl.LocalGovtArea = area;
     student.Candidate_tbl.Stateoforigin = sto;
     student.Candidate_tbl.Email = email;
     student.Candidate_tbl.Phone = phone;
     student.Candidate_tbl.DateofBirth = dob;
     db.SaveChanges();
 }

 public int addresults(Results_tbl result)
 {
     db = new CollegeERPDBEntities();
     db.Results_tbl.Add(result);
     db.SaveChanges();
     return result.ID;
 }

    public List<Book> getbooklist(int page, int pagesize)
    {
        db = new CollegeERPDBEntities();
        List<Book> temp = db.Books.OrderBy(x=>x.ID).Skip(page*pagesize).Take(pagesize).ToList();
        
        // var course = db.Courses_tbl.Where(x => x.ID>=pageStart && x.ID<=pageEnd).FirstOrDefault();

        return temp;
    }

    public Mails_tbl getusermail(int mid)
    {
        db = new CollegeERPDBEntities();

        var mail = db.Mails_tbl.Where(x => x.ID == mid).FirstOrDefault();
        mail.Status = 1;
        db.SaveChanges();
        return mail;
    }

 public List<Attendance_tbl> getattdance_course(int courseid)
 {
     db = new CollegeERPDBEntities();
     return db.Attendance_tbl.Where(x => x.CourseID == courseid).ToList();
 }

 public void placeOrder(IssueBook book)
 {
     using (db = new CollegeERPDBEntities())
     {
         db.IssueBooks.Add(book);
         db.SaveChanges();
         AdminMails_tbl amail = new AdminMails_tbl { SenderID = book.LibraryMember.UserID, Message = book.LibraryMember.Candidate_tbl.Name + " has Requested for " + book.Book.Title, Date = DateTime.Now.Date, Subject = "Book Request", Status = 0 };
         addadminmail(amail);        
     }
 }

    public bool checkattendance(int courseid)
    {
        db = new CollegeERPDBEntities();
        DateTime date=DateTime.Now.Date;
        var check = db.Attendance_tbl.Where(x=>x.CourseID==courseid && x.Date==date).FirstOrDefault();

        return (check == null);
            
    }

 public List<Enroll_Course> getenrolledstudents(int crsid)
 {
     db = new CollegeERPDBEntities();
    return db.Enroll_Course.Where(x=>x.CourseID==crsid&&x.Status==1).ToList();
 }

 public int getapplicant_count()
 {
     db = new CollegeERPDBEntities();
     var candidates = db.Candidate_tbl.ToList().Count;
     return candidates;
 }

 public List<Candidate_tbl> generatemeritlist2(int prgid)
 {
     db = new CollegeERPDBEntities();
     
     var candidates = db.Candidate_tbl.Where(x => x.ProgrammeID == prgid).OrderByDescending(x => x.CuttoffPoints).Take(100).ToList();
     return candidates;
 }

 public List<Candidate_tbl> getapplicantlist(int page,int pagesize)
 {
     db = new CollegeERPDBEntities();
     var candidates= db.Candidate_tbl.OrderByDescending(x=>x.CuttoffPoints).Skip(page*pagesize).Take(pagesize).ToList() ;
     return candidates;
 }