public string[] SearchRemindCount(SearchRemindSystem RemindSystemData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn=this.SearchRemindSystemConditionReturn(RemindSystemData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT COUNT(*) FROM ( " +
"SELECT * FROM RemindList " +
"WHERE Executor=@UserName OR CreateFileBy=@UserName AND isDeleted=0 ) AS NewTable " +
"LEFT JOIN StaffDatabase AS s1 ON NewTable.CreateFileBy=s1.StaffID " +
"LEFT JOIN StaffDatabase AS s2 ON NewTable.Executor=s2.StaffID "+
"WHERE NewTable.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
//cmd.Parameters.Add("@ExecutorType", SqlDbType.TinyInt).Value = 0;//Chk.CheckStringtoIntFunction(RemindSystemData.rType);
cmd.Parameters.Add("@ExecutorName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(RemindSystemData.txtrecipient) + "%";
cmd.Parameters.Add("@designeName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(RemindSystemData.txtdesignee) + "%";
cmd.Parameters.Add("@fulfillmentDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtfulfillmentDatestart);
cmd.Parameters.Add("@fulfillmentDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtfulfillmentDateend);
cmd.Parameters.Add("@executionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtexecutionDatestart);
cmd.Parameters.Add("@executionDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtexecutionDateend);
cmd.Parameters.Add("@designeeDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtdesigneeDatestart);
cmd.Parameters.Add("@designeeDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtdesigneeDateend);
cmd.Parameters.Add("@UserName", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<SearchRemindSystemResult> SearchRemind(int indexpage, SearchRemindSystem RemindSystemData)
{
List<SearchRemindSystemResult> returnValue = new List<SearchRemindSystemResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchRemindSystemConditionReturn(RemindSystemData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY NewTable.rID DESC) " +
"AS RowNum, NewTable.*,s1.StaffName AS designee,s2.StaffName AS recipient FROM " +
"( SELECT * FROM RemindList " +
"WHERE Executor=@UserName OR CreateFileBy=@UserName AND isDeleted=0 ) AS NewTable "+
"LEFT JOIN StaffDatabase AS s1 ON NewTable.CreateFileBy=s1.StaffID " +
"LEFT JOIN StaffDatabase AS s2 ON NewTable.Executor=s2.StaffID " +//AND StaffDatabase.isDeleted=0
"WHERE NewTable.isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable2 " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
//cmd.Parameters.Add("@ExecutorType", SqlDbType.TinyInt).Value = 0;//Chk.CheckStringtoIntFunction(RemindSystemData.rType);
cmd.Parameters.Add("@ExecutorName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(RemindSystemData.txtrecipient) + "%";
cmd.Parameters.Add("@designeName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(RemindSystemData.txtdesignee) + "%";
cmd.Parameters.Add("@fulfillmentDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtfulfillmentDatestart);
cmd.Parameters.Add("@fulfillmentDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtfulfillmentDateend);
cmd.Parameters.Add("@executionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtexecutionDatestart);
cmd.Parameters.Add("@executionDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtexecutionDateend);
cmd.Parameters.Add("@designeeDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtdesigneeDatestart);
cmd.Parameters.Add("@designeeDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.txtdesigneeDateend);
cmd.Parameters.Add("@UserName", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchRemindSystemResult addValue = new SearchRemindSystemResult();
addValue.Number = dr["RowNum"].ToString();
addValue.rID = dr["rID"].ToString();
addValue.rType = dr["ExecutorType"].ToString();
addValue.recipient = dr["recipient"].ToString();
addValue.executionContent = dr["RemindContent"].ToString();
addValue.executionDate = DateTime.Parse(dr["RemindDate"].ToString()).ToString("yyyy-MM-dd");
addValue.fulfillmentDate = DateTime.Parse(dr["CompleteDate"].ToString()).ToString("yyyy-MM-dd");
addValue.designee = dr["designee"].ToString();
addValue.designeeDate = DateTime.Parse(dr["CreateFileDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
SearchRemindSystemResult addValue = new SearchRemindSystemResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchRemindSystemDataCount(SearchRemindSystem RemindSystemData)
{
OtherDataBase sDB = new OtherDataBase();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
return sDB.SearchRemindCount(RemindSystemData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
private string SearchRemindSystemConditionReturn(SearchRemindSystem RemindSystemData)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (RemindSystemData.txtrecipient != null) {
ConditionReturn += " AND s2.StaffName like @ExecutorName ";
}
if (RemindSystemData.txtdesignee != null)
{
ConditionReturn += " AND s1.StaffName like @designeName ";
}
if (RemindSystemData.txtfulfillmentDatestart != null && RemindSystemData.txtfulfillmentDateend != null && RemindSystemData.txtfulfillmentDatestart != DateBase && RemindSystemData.txtfulfillmentDateend != DateBase) {
ConditionReturn += " AND RemindList.CompleteDate BETWEEN @fulfillmentDatestart AND @fulfillmentDateend";
}
if (RemindSystemData.txtexecutionDatestart != null && RemindSystemData.txtexecutionDateend != null && RemindSystemData.txtexecutionDatestart != DateBase && RemindSystemData.txtexecutionDateend != DateBase)
{
ConditionReturn += " AND RemindList.RemindDate BETWEEN @executionDatestart AND @executionDateend";
}
if (RemindSystemData.txtdesigneeDatestart != null && RemindSystemData.txtdesigneeDateend != null && RemindSystemData.txtdesigneeDatestart != DateBase && RemindSystemData.txtdesigneeDateend != DateBase)
{
ConditionReturn += " AND (select CONVERT(varchar(12) ,RemindList.CreateFileDate,23)) BETWEEN @designeeDatestart AND @designeeDateend";
}
return ConditionReturn;
}
public List<SearchRemindSystemResult> SearchRemindSystemData(int index, SearchRemindSystem RemindSystemData)
{
OtherDataBase sDB = new OtherDataBase();
return sDB.SearchRemind(index, RemindSystemData);
}
