Esempio n. 1
0
 private string SearchFmAssessmentConditionReturn(SearchFMAidsAssess SearchStructure)
 {
     string ConditionReturn = "";
     string DateBase = "1900-01-01";
     if (SearchStructure.txtstudentName != null)
     {
         ConditionReturn += " AND StudentName like (@StudentName) ";
     }
     if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0")
     {
         ConditionReturn += " AND StudentSex=(@StudentSex) ";
     }
     if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase)
     {
         ConditionReturn += " AND StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
     }
     if (SearchStructure.txtAssessDatestart != null && SearchStructure.txtAssessDateend != null && SearchStructure.txtAssessDatestart != DateBase && SearchStructure.txtAssessDateend != DateBase)
     {
         ConditionReturn += " AND AssessDate BETWEEN (@sAssessDateStart) AND (@sAssessDateEnd) ";
     }
     StaffDataBase sDB = new StaffDataBase();
     List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
     if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
     {
         ConditionReturn += " AND FMAidsAssess.Unit =" + UserFile[2] + " ";
     }
     return ConditionReturn;
 }
Esempio n. 2
0
 public string[] SearchFmAssessmentDataCount(SearchFMAidsAssess SearchStructure)
 {
     string[] returnValue = new string[2];
     returnValue[0] = "0";
     returnValue[1] = "0";
     DataBase Base = new DataBase();
     string ConditionReturn = this.SearchFmAssessmentConditionReturn(SearchStructure);
     using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
     {
         try
         {
             Sqlconn.Open();
             string sql = "SELECT COUNT(*) AS QCOUNT FROM FMAidsAssess " +
                         "INNER JOIN StudentDatabase ON FMAidsAssess.StudentID=StudentDatabase.StudentID " +
                         "WHERE FMAidsAssess.isDeleted=0 " + ConditionReturn;
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + SearchStructure.txtstudentName + "%";
             cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
             cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
             cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
             cmd.Parameters.Add("@sAssessDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAssessDatestart);
             cmd.Parameters.Add("@sAssessDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAssessDateend);
             returnValue[0] = cmd.ExecuteScalar().ToString();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             returnValue[0] = "-1";
             returnValue[1] = e.Message.ToString();
         }
     }
     return returnValue;
 }
Esempio n. 3
0
 public string[] SearchFmAssessmentDataBaseCount(SearchFMAidsAssess StructData)
 {
     Audiometry aDB = new Audiometry();
     if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
     {
         return aDB.SearchFmAssessmentDataCount(StructData);
     }
     else
     {
         return new string[2] { _noRole, _errorMsg };
     }
 }
Esempio n. 4
0
 public List<SearchFMAidsAssessResult> SearchFmAssessmentData(int indexpage, SearchFMAidsAssess SearchStructure)
 {
     List<SearchFMAidsAssessResult> returnValue = new List<SearchFMAidsAssessResult>();
     DataBase Base = new DataBase();
     StaffDataBase sDB = new StaffDataBase();
     string ConditionReturn = this.SearchFmAssessmentConditionReturn(SearchStructure);
     using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
     {
         try
         {
             Sqlconn.Open();
             string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY StudentDatabase.ID DESC) " +
                         "AS RowNum,  FMAidsAssess.*,StudentDatabase.StudentName,StudentDatabase.StudentBirthday FROM FMAidsAssess " +
                         "INNER JOIN StudentDatabase ON FMAidsAssess.StudentID=StudentDatabase.StudentID " +
                         "WHERE FMAidsAssess.isDeleted=0 " + ConditionReturn + " ) " +
                      "AS NewTable " +
                      "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
             cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + SearchStructure.txtstudentName + "%";
             cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
             cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
             cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
             cmd.Parameters.Add("@sAssessDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAssessDatestart);
             cmd.Parameters.Add("@sAssessDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAssessDateend);
             SqlDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 SearchFMAidsAssessResult addValue = new SearchFMAidsAssessResult();
                 addValue.ID = dr["ID"].ToString();
                 addValue.txtstudentName = dr["StudentName"].ToString();
                 addValue.txtAssessDate = DateTime.Parse(dr["AssessDate"].ToString()).ToString("yyyy-MM-dd");
                 addValue.txtbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()).ToString("yyyy-MM-dd");
                 string Audiologist = dr["Audiologist"].ToString();
                 List<string> CreateFileName = sDB.getStaffDataName(Audiologist);
                 addValue.txtaudiologist = CreateFileName[1];
                 returnValue.Add(addValue);
             }
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             SearchFMAidsAssessResult addValue = new SearchFMAidsAssessResult();
             addValue.checkNo = "-1";
             addValue.errorMsg = e.Message.ToString();
             returnValue.Add(addValue);
         }
     }
     return returnValue;
 }
Esempio n. 5
0
 public List<SearchFMAidsAssessResult> SearchFmAssessmentDataBase(int index,SearchFMAidsAssess StructData)
 {
     Audiometry aDB = new Audiometry();
     return aDB.SearchFmAssessmentData(index, StructData);
 }