private string SearchSingleTeachCondition(SearchCaseISPRecord SearchStructure, int type)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentName like (@StudentName) ";
}
if (SearchStructure.txtteacherName != null)
{
ConditionReturn += " AND TeacherName like (@TeacherName) ";
}
if (SearchStructure.txtConventionDatestart != null && SearchStructure.txtConventionDateend != null && SearchStructure.txtConventionDatestart != DateBase && SearchStructure.txtConventionDateend != DateBase)
{
ConditionReturn += " AND ( PlanDateStart BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) or PlanDateEnd BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) ) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
caseBTFunction();
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND b.Unit =" + UserFile[2] + " ";
}
if (type == 0)
{
ConditionReturn += " AND b.CaseStatu2 =" + type + " ";
}
return ConditionReturn;
}
public List<SingleClassShortTerm> SearchSingleTeach(int indexpage, SearchCaseISPRecord SearchStructure, int type)
{
List<SingleClassShortTerm> returnValue = new List<SingleClassShortTerm>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchCaseISPRecordCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (SELECT ROW_NUMBER() OVER (ORDER BY isnull( a.id,'') DESC) as RowNum,a.ID , PlanDateStart ,PlanDateEnd , b.studentName , c.TeacherName ";
sql += " FROM SingleClassShortTerm a ";
sql += " left join studentDatabase b on a.studentid = b.studentid ";
sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid";
sql += " WHERE isnull(a.isDeleted,0) = 0 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%";
cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart);
cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SingleClassShortTerm addValue = new SingleClassShortTerm();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = dr["ID"].ToString();
addValue.studentName = dr["studentName"].ToString();
addValue.teacherName = dr["teacherName"].ToString();
addValue.PlanDateStart = DateTime.Parse(dr["PlanDateStart"].ToString()).ToString("yyyy-MM-dd");
addValue.PlanDateEnd = DateTime.Parse(dr["PlanDateEnd"].ToString()).ToString("yyyy-MM-dd");
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
//ShowCaseISPRecord addValue = new ShowCaseISPRecord();
//addValue.checkNo = "-1";
//addValue.errorMsg = e.Message.ToString();
//returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchSingleTeachCount(SearchCaseISPRecord SearchStructure, int type)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchSingleTeachCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM SingleClassShortTerm a left join studentDatabase b on a.studentid = b.id left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid WHERE isnull(a.isDeleted,0) = 0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%";
cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart);
cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<ShowCaseISPRecord> SearchCaseISPRecord(int indexpage, SearchCaseISPRecord SearchStructure, int type)
{
List<ShowCaseISPRecord> returnValue = new List<ShowCaseISPRecord>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchCaseISPRecordCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.ConventionDate,'') DESC) as RowNum , a.ID ,a.ConventionName, convert(varchar, isnull(convert(date,a.ConventionDate,1),'1912'), 111) as ConventionDate, b.StudentName ";
sql += " , TeacherName, ParticipantTeacheName,ParticipantSocialWorkerName,ParticipantAudiologistName,ParticipantHeadName ";
sql += " , a.ParticipantParent, a.ParticipantProfessionals ";
sql += " FROM CaseISPRecord a left join studentDatabase b on a.studentid = b.id ";
sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid ";
sql += " left join (select staffid as did , StaffName as ParticipantTeacheName from staffDatabase) d on a.ParticipantTeache = d.did ";
sql += " left join (select staffid as eid , StaffName as ParticipantSocialWorkerName from staffDatabase) e on a.ParticipantSocialWorker = e.eid ";
sql += " left join (select staffid as fid , StaffName as ParticipantAudiologistName from staffDatabase) f on a.ParticipantAudiologist = f.fid ";
sql += " left join (select staffid as gid , StaffName as ParticipantHeadName from staffDatabase) g on a. ParticipantHead = g.gid ";
sql += " WHERE isnull(a.isDeleted,0) = 0 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%";
cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart);
cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ShowCaseISPRecord addValue = new ShowCaseISPRecord();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = dr["ID"].ToString();
addValue.ConventionName = dr["ConventionName"].ToString();
addValue.ConventionDate = Convert.ToDateTime(dr["ConventionDate"].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1);
addValue.StudentName = dr["StudentName"].ToString();
addValue.TeacherName = dr["TeacherName"].ToString();
addValue.ParticipantTeacheName = dr["ParticipantTeacheName"].ToString();
addValue.ParticipantSocialWorkerName = dr["ParticipantSocialWorkerName"].ToString();
addValue.ParticipantAudiologistName = dr["ParticipantAudiologistName"].ToString();
addValue.ParticipantHeadName = dr["ParticipantHeadName"].ToString();
addValue.ParticipantParent = dr["ParticipantParent"].ToString();
addValue.ParticipantProfessionals = dr["ParticipantProfessionals"].ToString();
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
ShowCaseISPRecord addValue = new ShowCaseISPRecord();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchSingleTeachCount(SearchCaseISPRecord SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchSingleTeachCount(SearchStructure, 1);
}
public List<SingleClassShortTerm> SearchSingleTeach(int index, SearchCaseISPRecord SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchSingleTeach(index, SearchStructure, 0);
}
public string[] SearchCaseISPRecordCount(SearchCaseISPRecord SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
//aDB.caseBTFunction();
//if (int.Parse(aDB._StaffhaveRoles[2]) == 1)
//{
return aDB.SearchCaseISPRecordCount(SearchStructure, 1);
//}
//else
//{
// return new string[2] { _noRole, _errorMsg };
//}
}
public List<ShowCaseISPRecord> SearchCaseISPRecord(int index, SearchCaseISPRecord SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchCaseISPRecord(index, SearchStructure, 0);
}