コード例 #1
0
    public List <StaffDataList> getNoMembershipStaffDataList()
    {
        List <StaffDataList> returnValue = new List <StaffDataList>();
        DataBase             Base        = new DataBase();

        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string        sql = "SELECT * FROM StaffDatabase a WHERE NOT EXISTS (select UserName FROM aspnet_Users b WHERE b.UserName=a.StaffID) AND isDeleted=0";
                SqlCommand    cmd = new SqlCommand(sql, Sqlconn);
                SqlDataReader dr  = cmd.ExecuteReader();
                while (dr.Read())
                {
                    StaffDataList addValue = new StaffDataList();
                    addValue.sID    = dr["StaffID"].ToString();
                    addValue.sName  = dr["StaffName"].ToString();
                    addValue.sEmail = dr["Email"].ToString();
                    returnValue.Add(addValue);
                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                StaffDataList addValue = new StaffDataList();
                addValue.sID   = "-1";
                addValue.sName = e.Message;
                returnValue.Add(addValue);
            }
        }
        return(returnValue);
    }
コード例 #2
0
    public string[] getMembershipStaffRoles(string StaffID)
    {
        string[] returnValue = new string[6];
        returnValue[0] = "0";
        returnValue[1] = "0";
        returnValue[2] = "0";
        returnValue[3] = "0";
        returnValue[4] = "0";
        returnValue[5] = "0";
        DataBase Base = new DataBase();

        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string     sql = "SELECT * FROM Staff_Competence_Roles WHERE StaffID=@StaffID";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffID);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    returnValue[0] = dr["ID"].ToString();
                    returnValue[1] = dr["Roles1"].ToString();
                    returnValue[2] = dr["Roles2"].ToString();
                    returnValue[3] = dr["Roles3"].ToString();
                    returnValue[4] = dr["Roles4"].ToString();
                    returnValue[5] = dr["Roles5"].ToString();
                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                StaffDataList addValue = new StaffDataList();
                addValue.sID   = "-1";
                addValue.sName = e.Message;
                //returnValue.Add(addValue);
            }
        }
        return(returnValue);
    }
コード例 #3
0
    public List<StaffDataList> getNoMembershipStaffDataList()
    {
        List<StaffDataList> returnValue = new List<StaffDataList>();
        DataBase Base = new DataBase();
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string sql = "SELECT * FROM StaffDatabase a WHERE NOT EXISTS (select UserName FROM aspnet_Users b WHERE b.UserName=a.StaffID) AND isDeleted=0";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    StaffDataList addValue = new StaffDataList();
                    addValue.sID = dr["StaffID"].ToString();
                    addValue.sName = dr["StaffName"].ToString();
                    addValue.sEmail = dr["Email"].ToString();
                    returnValue.Add(addValue);

                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                StaffDataList addValue = new StaffDataList();
                addValue.sID = "-1";
                addValue.sName = e.Message;
                returnValue.Add(addValue);
            }

        }
        return returnValue;
    }
コード例 #4
0
    public string[] getMembershipStaffRoles(string StaffID)
    {
        string[] returnValue = new string[6];
        returnValue[0] = "0";
        returnValue[1] = "0";
        returnValue[2] = "0";
        returnValue[3] = "0";
        returnValue[4] = "0";
        returnValue[5] = "0";
        DataBase Base = new DataBase();
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string sql = "SELECT * FROM Staff_Competence_Roles WHERE StaffID=@StaffID";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffID);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    returnValue[0] = dr["ID"].ToString();
                    returnValue[1] = dr["Roles1"].ToString();
                    returnValue[2] = dr["Roles2"].ToString();
                    returnValue[3] = dr["Roles3"].ToString();
                    returnValue[4] = dr["Roles4"].ToString();
                    returnValue[5] = dr["Roles5"].ToString();

                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                StaffDataList addValue = new StaffDataList();
                addValue.sID = "-1";
                addValue.sName = e.Message;
                //returnValue.Add(addValue);
            }
        }
        return returnValue;
    }
コード例 #5
0
    public List<StaffDataList> SearchStaffSalaryData(int indexpage, SearchStaff SearchStaffConditionData)
    {
        List<StaffDataList> returnValue = new List<StaffDataList>();
        DataBase Base = new DataBase();
        string SearchStaffCondition = this.SearchStaffSalaryConditionReturn(SearchStaffConditionData);
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY StaffSalary.WriteDate DESC) " +
                             "AS RowNum, StaffSalary.*,StaffDatabase.StaffName, StaffDatabase.sex  " +
                             "FROM StaffSalary " +
                            "INNER JOIN StaffDatabase ON StaffSalary.StaffID=StaffDatabase.StaffID WHERE StaffSalary.isDeleted=0 " + SearchStaffCondition + " ) " +
                             "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("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffID);
                cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffConditionData.txtstaffName) + "%";
                cmd.Parameters.Add("@sex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffSex);
                cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffConditionData.txtstaffBirthdayStart);
                cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffConditionData.txtstaffBirthdayEnd);
                cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffUnit);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    StaffDataList addValue = new StaffDataList();
                    addValue.ID = dr["ID"].ToString();
                    addValue.sID = dr["StaffID"].ToString();
                    addValue.sName = dr["StaffName"].ToString();
                    addValue.sSex = dr["sex"].ToString();
                    addValue.sUnit = dr["Unit"].ToString();
                    addValue.FileDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
                    returnValue.Add(addValue);
                }
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                StaffDataList addValue = new StaffDataList();
                addValue.checkNo = "-1";
                addValue.errorMsg = e.Message;
                returnValue.Add(addValue);
            }

        }
        return returnValue;
    }
コード例 #6
0
    public List<StaffDataList> getAllStaffDataListRemindlist(List<int> WorkItem)
    {
        List<StaffDataList> returnValue = new List<StaffDataList>();
        DataBase Base = new DataBase();
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string itemStr = "";
                if (WorkItem.Count > 0)
                {
                    foreach (int item in WorkItem)
                    {
                        if (item != 0)
                        {
                            if (itemStr.Length > 0)
                            {
                                itemStr += " OR ";
                            }
                            itemStr += " WorkItem= " + item;
                        }
                    }
                    if (itemStr.Length > 0)
                    {
                        itemStr = " AND ( " + itemStr + " ) ";
                    }
                }
                //StaffDataBase sDB = new StaffDataBase();
                //sDB.personnelFunction();
                //List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
                //if (int.Parse(sDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
                //{
                //    itemStr += " AND Unit =" + UserFile[2] + " ";
                //}
                //List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
                string sql = "SELECT * FROM StaffDatabase WHERE isDeleted=0  and ResignationDate = '1900-01-01' " + itemStr + "order by StaffID ASC";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
               // cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[2].ToString());
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    StaffDataList addValue = new StaffDataList();
                    addValue.sID = dr["StaffID"].ToString();
                    addValue.sName = dr["StaffName"].ToString();
                    addValue.sEmail = dr["Email"].ToString();
                    addValue.sUnit = dr["Unit"].ToString();
                    returnValue.Add(addValue);

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

        }
        return returnValue;
    }
コード例 #7
0
 private List<StaffDataList> getCreditParticipants(string cID)
 {
     List<StaffDataList> returnValue = new List<StaffDataList>();
     DataBase Base = new DataBase();
     using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
     {
         try
         {
             Sqlconn.Open();
             string sql = "SELECT StaffUpgradeCreditStaff.*,StaffDatabase.StaffName FROM StaffUpgradeCreditStaff "+
                 "INNER JOIN StaffDatabase ON StaffUpgradeCreditStaff.StaffID=StaffDatabase.StaffID "+
                 "WHERE CreditID=@CreditID AND StaffUpgradeCreditStaff.isDeleted=0 ";
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@CreditID", SqlDbType.BigInt).Value = int.Parse(cID);
             SqlDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 StaffDataList addValue = new StaffDataList();
                 addValue.ID = dr["ID"].ToString();
                 addValue.sName = dr["StaffName"].ToString();
                 addValue.sID = dr["StaffID"].ToString();
                 returnValue.Add(addValue);
             }
             dr.Close();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             StaffDataList addValue = new StaffDataList();
             addValue.ID = "-1";
             addValue.sName = e.Message.ToString(); ;
             returnValue.Add(addValue);
         }
     }
     return returnValue;
 }