public SearchRemindSystemResult SearchRemindOne(string rID)
{
SearchRemindSystemResult returnValue = new SearchRemindSystemResult();
DataBase Base = new DataBase();
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 RemindList WHERE rID=@rID AND isDeleted=0 ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@rID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(rID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.rID = dr["rID"].ToString();
returnValue.rType = dr["ExecutorType"].ToString();
returnValue.recipient = dr["Executor"].ToString();
returnValue.executionContent = dr["RemindContent"].ToString();
returnValue.executionDate = DateTime.Parse(dr["RemindDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.fulfillmentDate = DateTime.Parse(dr["CompleteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.designee = dr["CreateFileBy"].ToString();
returnValue.designeeDate = DateTime.Parse(dr["CreateFileDate"].ToString()).ToString("yyyy-MM-dd");
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = 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;
}
/**main*/
public List<SearchRemindSystemResult> getMyselfRemindSystemData(string UserStaffName)
{
List<SearchRemindSystemResult> returnValue = new List<SearchRemindSystemResult>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY RemindList.rID DESC) " +
"AS RowNum,RemindList.*,s1.StaffName AS designee,s2.StaffName AS recipient FROM RemindList " +
"RIGHT JOIN StaffDatabase AS s1 ON RemindList.CreateFileBy=s1.StaffID " +
"RIGHT JOIN StaffDatabase AS s2 ON RemindList.Executor=s2.StaffID AND s2.StaffID=@StaffID " +//AND StaffDatabase.isDeleted=0
"WHERE RemindList.isDeleted=0 AND CompleteDate = @CompleteDate and RemindDate <= getdate() )" +
"AS NewTable ";
sql += " order by RemindDate desc ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(UserStaffName);
cmd.Parameters.Add("@CompleteDate", SqlDbType.Date).Value = new DateTime(1900,01,01);
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.recipient = "-1";
addValue.designee = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] setRemindSystemData2(SearchRemindSystemResult RemindSystemData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
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 = "UPDATE RemindList SET CompleteDate=@fulfillmentDate, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
"WHERE rID=@rID AND isDeleted=0 AND Executor=@recipientID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@rID", SqlDbType.BigInt).Value = RemindSystemData.rID;
cmd.Parameters.Add("@recipientID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@fulfillmentDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.fulfillmentDate);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] setRemindSystemData2(SearchRemindSystemResult RemindSystemData)
{
OtherDataBase sDB = new OtherDataBase();
return sDB.setRemindSystemData2(RemindSystemData);
}
public string[] setRemindSystemData1(SearchRemindSystemResult RemindSystemData,string vIndex)
{
OtherDataBase sDB = new OtherDataBase();
SearchRemindSystemResult oldData=sDB.SearchRemindOne(RemindSystemData.rID);
if (int.Parse(sDB._StaffhaveRoles[1]) == 1 && oldData.designee == HttpContext.Current.User.Identity.Name && oldData.checkNo ==null)
{
return sDB.setRemindSystemData1(RemindSystemData);
}
else
{
return new string[4] { _getcheckNo, _errorMsg, RemindSystemData.rID, vIndex };
}
}
