public string[] SearchStaffBehaveDataBaseCount(SearchStaffBehave SearchStaffUpgrade2)
 {
     StaffDataBase sDB = new StaffDataBase();
     sDB.personnelFunction();
     if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
     {
         return sDB.SearchStaffBehaveDataBaseCount(SearchStaffUpgrade2);
     }
     else
     {
         return new string[2] { _noRole, _errorMsg };
     }
 }
 public List<CreateStaffUpgradeSeries> SearchStaffBehaveDataBase(int index, SearchStaffBehave SearchStaffUpgradeData)
 {
     StaffDataBase sDB = new StaffDataBase();
     return sDB.SearchStaffBehaveDataBase(index, SearchStaffUpgradeData);
 }
 public string[] SearchStaffBehaveDataBaseCount(SearchStaffBehave SearchStaffUpgrade)
 {
     string[] returnValue = new string[2];
     returnValue[0] = "0";
     returnValue[1] = "0";
     DataBase Base = new DataBase();
     using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
     {
         try
         {
             string[] ConditionReturn = this.SearchStaffBehaveConditionReturn(SearchStaffUpgrade);
             Sqlconn.Open();
             string sql = "SELECT count(BehaveID) FROM StaffUpgradeBehave INNER JOIN StaffDatabase ON StaffUpgradeBehave.StaffID=StaffDatabase.StaffID " +ConditionReturn[0] +
                         "WHERE StaffUpgradeBehave.isDeleted=0 " + ConditionReturn[1];
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName1) + "%";
             cmd.Parameters.Add("@sDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart1);
             cmd.Parameters.Add("@sDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd1);
             cmd.Parameters.Add("@txtType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtType);
             returnValue[0] = cmd.ExecuteScalar().ToString();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             returnValue[0] = "-1";
             returnValue[1] = e.Message.ToString();
         }
     }
     return returnValue;
 }
 //***/
 private string[] SearchStaffBehaveConditionReturn(SearchStaffBehave SearchStructure)
 {
     string[] ConditionReturnValue = new string[2];
     string ConditionReturn1 = "";
     string ConditionReturn2 = "";
     string DateBase = "1900-01-01";
     if (SearchStructure.txtstaffName1 != null)
     {
         ConditionReturn1 += " AND StaffDatabase.StaffName like (@txtstaffName) ";
     }
     if (SearchStructure.txtType != null)
     {
         ConditionReturn2 += " AND StaffUpgradeBehave.Category = @txtType ";
     }
     if (SearchStructure.txtDateStart1 != null && SearchStructure.txtDateEnd1 != null && SearchStructure.txtDateStart1 != DateBase && SearchStructure.txtDateEnd1 != DateBase)
     {
         ConditionReturn2 += " AND StaffUpgradeBehave.PublicationDate BETWEEN (@sDateStart) AND (@sDateEnd) ";
     }
     List<string> UserFile = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
     this.personnelFunction();
     if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
     {
         ConditionReturn2 += " AND StaffUpgradeBehave.Unit =" + UserFile[2] + " ";
     }
     ConditionReturnValue[0] = ConditionReturn1;
     ConditionReturnValue[1] = ConditionReturn2;
     return ConditionReturnValue;
 }
    public List<CreateStaffUpgradeSeries> SearchStaffBehaveDataBase(int indexpage, SearchStaffBehave SearchStaffUpgrade)
    {
        List<CreateStaffUpgradeSeries> returnValue = new List<CreateStaffUpgradeSeries>();
        DataBase Base = new DataBase();
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                string[] ConditionReturn = this.SearchStaffBehaveConditionReturn(SearchStaffUpgrade);
                Sqlconn.Open();
                string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY StaffUpgradeBehave.BehaveID DESC) " +
                             "AS RowNum,StaffUpgradeBehave.*,StaffDatabase.StaffName  FROM StaffUpgradeBehave INNER JOIN StaffDatabase ON StaffUpgradeBehave.StaffID=StaffDatabase.StaffID " + ConditionReturn[0] +
                             "WHERE StaffUpgradeBehave.isDeleted=0 " + ConditionReturn[1] + " ) " +
                             "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("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName1) + "%";
                cmd.Parameters.Add("@sDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart1);
                cmd.Parameters.Add("@sDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd1);
                cmd.Parameters.Add("@txtType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtType);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    CreateStaffUpgradeSeries addValue = new CreateStaffUpgradeSeries();
                    addValue.ID = dr["BehaveID"].ToString();
                    addValue.articleDate = DateTime.Parse(dr["PublicationDate"].ToString()).ToString("yyyy-MM-dd");
                    addValue.author = dr["StaffName"].ToString();
                    addValue.serialNumber = dr["Amount"].ToString();
                    addValue.seriesTitle = dr["PublicationName"].ToString();
                    addValue.volume = dr["Period"].ToString();
                    addValue.articleTitle = dr["ArticleTitle"].ToString();
                    addValue.articleType = dr["Category"].ToString();
                    returnValue.Add(addValue);

                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                CreateStaffUpgradeSeries addValue = new CreateStaffUpgradeSeries();
                addValue.checkNo = "-1";
                addValue.errorMsg = e.Message;
                returnValue.Add(addValue);
            }
        }
        return returnValue;
    }