コード例 #1
0
 private string SearchStudentAidConditionReturn(SearchStudentAid SearchStructure)
 {
     string ConditionReturn = "";
     string DateBase = "1900-01-01";
     if (SearchStructure.txtstudentID != null)
     {
         ConditionReturn += " AND FinancialAidApplications.StudentID=(@StudentID) ";
     }
     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.txtsubsidyitem != null && SearchStructure.txtsubsidyitem != "0")
     {
         ConditionReturn += " AND AidCategory =(@AidCategory) ";
     }
     if (SearchStructure.txtfillInDatestart != null && SearchStructure.txtfillInDateend != null && SearchStructure.txtfillInDatestart != DateBase && SearchStructure.txtfillInDateend != DateBase)
     {
         ConditionReturn += " AND FinancialAidApplications.WriteDate BETWEEN (@sfillInDateStart) AND (@sfillInDateEnd) ";
     }
     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 FinancialAidApplications.Unit =" + UserFile[2] + " ";
     }
     return ConditionReturn;
 }
コード例 #2
0
    public List<SearchStudentAidResult> searcStudentAidt(int indexpage, SearchStudentAid SearchStructure)
    {
        List<SearchStudentAidResult> returnValue = new List<SearchStudentAidResult>();
        DataBase Base = new DataBase();
        string ConditionReturn = this.SearchStudentAidConditionReturn(SearchStructure);
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY FinancialAidApplications.ID DESC) " +
                             "AS RowNum, FinancialAidApplications.*,StudentDatabase.StudentName, StudentDatabase.StudentIdentity " +
                             "FROM FinancialAidApplications INNER JOIN StudentDatabase ON FinancialAidApplications.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
                             "WHERE FinancialAidApplications.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("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
                cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(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("@sfillInDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtfillInDatestart);
                cmd.Parameters.Add("@sfillIndDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtfillInDateend);
                cmd.Parameters.Add("@AidCategory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtsubsidyitem);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SearchStudentAidResult addValue = new SearchStudentAidResult();
                    addValue.ID = dr["ID"].ToString();
                    addValue.txtstudentID = dr["StudentID"].ToString();
                    addValue.txtstudentName = dr["StudentName"].ToString();
                    addValue.txtfillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
                    addValue.txtsubsidyitem = dr["AidCategory"].ToString();
                    addValue.txtsubsidymoney = dr["Grants"].ToString();
                    addValue.txtsubsidydate1 = DateTime.Parse(dr["GrantsTimeSince"].ToString()).ToString("yyyy-MM-dd");
                    addValue.txtsubsidydate2 = DateTime.Parse(dr["GrantsTimeUntil"].ToString()).ToString("yyyy-MM-dd");
                    addValue.txtstudentTWID = dr["StudentIdentity"].ToString();
                    returnValue.Add(addValue);

                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                SearchStudentAidResult addValue = new SearchStudentAidResult();
                addValue.checkNo = "-1";
                addValue.errorMsg = e.Message;
                returnValue.Add(addValue);
            }
        }
        return returnValue;
    }
コード例 #3
0
 public string[] searcStudentAidDataCount(SearchStudentAid SearchData)
 {
     CaseDataBase SDB = new CaseDataBase();
     if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
     {
         return SDB.searcStudentAidCount(SearchData);
     }
     else
     {
         return new string[2] { _noRole, _errorMsg };
     }
 }
コード例 #4
0
 public string[] searcStudentAidCount(SearchStudentAid SearchStructure)
 {
     string[] returnValue = new string[2];
     returnValue[0] = "0";
     returnValue[1] = "0";
     DataBase Base = new DataBase();
     string ConditionReturn = this.SearchStudentAidConditionReturn(SearchStructure);
     using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
     {
         try
         {
             Sqlconn.Open();
             string sql = "SELECT COUNT(*) AS QCOUNT FROM FinancialAidApplications INNER JOIN StudentDatabase ON FinancialAidApplications.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
                 "WHERE FinancialAidApplications.isDeleted=0 " + ConditionReturn;
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
             cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(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("@sfillInDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtfillInDatestart);
             cmd.Parameters.Add("@sfillIndDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtfillInDateend);
             cmd.Parameters.Add("@AidCategory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtsubsidyitem);
             returnValue[0] = cmd.ExecuteScalar().ToString();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             returnValue[0] = "-1";
             returnValue[1] = e.Message;
         }
     }
     return returnValue;
 }
コード例 #5
0
 public List<SearchStudentAidResult> searchStudentAidData(int index, SearchStudentAid SearchData)
 {
     CaseDataBase SDB = new CaseDataBase();
     return SDB.searcStudentAidt(index, SearchData);
 }