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);
}
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);
}
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;
}
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;
}
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;
}
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;
}
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;
}