public string[] SearchStaffCreditDataBaseCount(SearchStaffCredit SearchStaffUpgradeData)
{
StaffDataBase sDB = new StaffDataBase();
sDB.personnelFunction();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
return sDB.SearchStaffCreditDataBaseCount(SearchStaffUpgradeData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<CreateStaffUpgrade> SearchStaffCreditDataBase(int index,SearchStaffCredit SearchStaffUpgradeData)
{
StaffDataBase sDB = new StaffDataBase();
return sDB.SearchStaffCreditDataBase(index, SearchStaffUpgradeData);
}
public string[] SearchStaffCreditDataBaseCount(SearchStaffCredit 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.SearchStaffCreditConditionReturn(SearchStaffUpgrade);
Sqlconn.Open();
string sql = "SELECT count(DISTINCT NewTable.CreditID) AS QCOUNT ,SUM(NewTable.CreditNumber) AS SumNumber FROM( "+
"SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b " +
"ON a.CreditID=b.CreditID AND b.isDeleted=0 " +
"WHERE a.isDeleted=0 " + ConditionReturn[0] + ConditionReturn[1] +
" ) AS NewTable";
/*"SELECT count(DISTINCT a.CreditID) FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b " +
"ON a.CreditID=b.CreditID AND b.isDeleted=0 " +
"WHERE a.isDeleted=0 " + ConditionReturn[0] + ConditionReturn[1];*/
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName) + "%";
cmd.Parameters.Add("@sCreditDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart);
cmd.Parameters.Add("@sCreditDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd);
cmd.Parameters.Add("@txtLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtLecturer) + "%";
cmd.Parameters.Add("@txtProve", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtProve);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue[0] = dr["QCOUNT"].ToString();
returnValue[1] = dr["SumNumber"].ToString();
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
private string[] SearchStaffCreditConditionReturn(SearchStaffCredit SearchStructure)
{
string[] ConditionReturnValue = new string[2];
string ConditionReturn1 = "";
string ConditionReturn2 = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstaffName != null)
{
ConditionReturn1 += " AND b.StaffID IN (SELECT c.StaffID FROM StaffDatabase c WHERE c.StaffID=b.StaffID AND c.StaffName like (@txtstaffName) )";
}
if (SearchStructure.txtLecturer != null)
{
ConditionReturn2 += " AND a.CreditTeacher like (@txtLecturer) ";
}
if (SearchStructure.txtDateStart != null && SearchStructure.txtDateEnd != null && SearchStructure.txtDateStart != DateBase && SearchStructure.txtDateEnd != DateBase)
{
ConditionReturn2 += " AND a.CreditDate BETWEEN (@sCreditDateStart) AND (@sCreditDateEnd) ";
}
if (SearchStructure.txtProve != null && SearchStructure.txtProve != "0")
{
ConditionReturn2 += " AND a.Prove =@txtProve ";
}
List<string> UserFile = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
this.personnelFunction();
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn2 += " AND a.Unit =" + UserFile[2] + " ";
}
ConditionReturnValue[0] = ConditionReturn1;
ConditionReturnValue[1] = ConditionReturn2;
return ConditionReturnValue;
}
public List<CreateStaffUpgrade> SearchStaffCreditDataBase(int indexpage, SearchStaffCredit SearchStaffUpgrade)
{
List<CreateStaffUpgrade> returnValue = new List<CreateStaffUpgrade>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
string[] ConditionReturn = this.SearchStaffCreditConditionReturn(SearchStaffUpgrade);
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC) " +
"AS RowNum,MuliTable.* FROM "+
"( SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b " +
"ON a.CreditID=b.CreditID AND b.isDeleted=0 " +
"WHERE a.isDeleted=0 " + ConditionReturn[0] + ConditionReturn[1] +" ) "+
"AS MuliTable )"+
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
/*SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC)
AS RowNum,MuliTable.* FROM (
SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b
ON a.CreditID=b.CreditID AND b.isDeleted=0
WHERE a.isDeleted=0 AND b.StaffID
IN (SELECT c.StaffID FROM StaffDatabase c WHERE c.StaffID=b.StaffID AND c.StaffName like '曾%'))
AS MuliTable
)
AS NewTable*/
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName) + "%";
cmd.Parameters.Add("@sCreditDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart);
cmd.Parameters.Add("@sCreditDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd);
cmd.Parameters.Add("@txtLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtLecturer) + "%";
cmd.Parameters.Add("@txtProve", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtProve);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateStaffUpgrade addValue = new CreateStaffUpgrade();
addValue.ID = dr["CreditID"].ToString();
addValue.courseDate = DateTime.Parse(dr["CreditDate"].ToString()).ToString("yyyy-MM-dd");
addValue.courseLecturer = dr["CreditTeacher"].ToString();
addValue.courseName = dr["Topics"].ToString();
addValue.courseTime = dr["Hours"].ToString();
addValue.courseProve = dr["Prove"].ToString();
addValue.courseCredit = dr["CreditNumber"].ToString();
addValue.otherExplanation = dr["Remark"].ToString();
addValue.Participants = new List<StaffDataList>();
addValue.Participants = this.getCreditParticipants(addValue.ID);
addValue.courseCreditTeacherType = dr["CreditTeacherType"].ToString();
addValue.courseInTeacher = dr["InTeacher"].ToString() == "False" ? "" : "Checked";
addValue.courseInAudiologist = dr["InAudiologist"].ToString() == "False" ? "" : "Checked";
addValue.courseInSocialWorkers = dr["InSocialWorkers"].ToString() == "False" ? "" : "Checked";
addValue.courseInAdministrative = dr["InAdministrative"].ToString() == "False" ? "" : "Checked";
addValue.courseIsSatisfaction = dr["IsSatisfaction"].ToString() == "False" ? "0" : "1";
addValue.courseDataQuestion = dr["DataQuestion"].ToString();
addValue.courseDataLecturer = dr["DataLecturer"].ToString();
addValue.courseDataLecture = dr["DataLecture"].ToString() == "False" ? "" : "Checked";
addValue.courseDataPhoto = dr["DataPhoto"].ToString() == "False" ? "" : "Checked";
addValue.courseDataTeaching = dr["DataTeaching"].ToString() == "False" ? "" : "Checked";
addValue.courseDataIsp = dr["DataIsp"].ToString() == "False" ? "" : "Checked";
addValue.courseDataOther = dr["DataOther"].ToString() == "False" ? "" : "Checked";
addValue.courseStartH = dr["StartH"].ToString();
addValue.courseEndH = dr["EndH"].ToString();
addValue.courseDataOtherText = dr["DataOtherText"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateStaffUpgrade addValue = new CreateStaffUpgrade();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
