public List<CaseISPRecord> UpdateCaseISPRecord(CaseISPRecord sTemperatureData)
{
List<CaseISPRecord> returnValue = new List<CaseISPRecord>();
CaseISPRecord temp = new CaseISPRecord();
DataBase Base = new DataBase();
string strSql = "";
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "CreateFileBy":
case "CreateFileDate":
case "ID":
case "Unit":
// strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData);
break;
//case "CreateFileDate":
case "UpFileDate":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=getdate()";
// strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() ";
//cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Date).Value = DateTime.Now.ToLongDateString();
break;
default:
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=@" + fldInfo.Name;
//strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name;
break;
}
}
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 CaseISPRecord set " + strSql + " where id=@ID ";
//sql += " select @@identity as id ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
//case "ID":
case "CreateFileDate":
case "Unit":
// strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData);
break;
case "UpFileBy":
//cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData));
cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
break;
default:
if (fldInfo.GetValue(sTemperatureData) != null)
{
cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString());
}
else
{
cmd.Parameters.Add("@" + fldInfo.Name, "");
}
break;
}
}
cmd.ExecuteNonQuery();
temp.ID = sTemperatureData.ID;
}
catch (Exception e)
{
string a = e.Message.ToString();
}
Sqlconn.Close();
}
returnValue.Add(temp);
return returnValue;
}
public List<CaseISPRecord> CreatCaseISPRecord(CaseISPRecord sTemperatureData)
{
List<CaseISPRecord> returnValue = new List<CaseISPRecord>();
CaseISPRecord temp = new CaseISPRecord();
DataBase Base = new DataBase();
string strSql = "";
string strSqlPara = "";
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "isDeleted":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "0 ";
break;
case "CreateFileDate":
case "UpFileDate":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() ";
break;
default:
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name;
break;
}
}
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 = " insert into CaseISPRecord ( " + strSql + ")values(" + strSqlPara + ") ";
sql += " select @@identity as id ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
// strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData);
break;
case "CreateFileBy":
case "UpFileBy":
//cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData));
cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
break;
default:
if (fldInfo.GetValue(sTemperatureData) != null)
{
cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString());
}
else
{
cmd.Parameters.Add("@" + fldInfo.Name, "");
}
break;
}
}
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
temp.ID = dr["id"].ToString();
}
}
catch (Exception e)
{
string ex = e.Message.ToString();
}
Sqlconn.Close();
}
returnValue.Add(temp);
return returnValue;
}
public List<AchievementAssessmentLoad> ShowCaseIspRecord(int ID)
{
string strSql = "";
List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>();
AchievementAssessmentLoad temp = new AchievementAssessmentLoad();
CaseISPRecord sTemperatureData = new CaseISPRecord();
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "ConventionDate":
case "PlanExecutionFrameDate":
case "PlanExecutionOverDate":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name;
break;
default:
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name;
break;
}
}
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
//string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , " + strSql + " from CaseStudy a left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 ";
string sql = "select b.StudentName , c.TeacherName, ParticipantTeacheName,ParticipantSocialWorkerName,ParticipantAudiologistName,ParticipantHeadName , ";
sql += strSql;
sql += " FROM CaseISPRecord a left join (select id, studentName from 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 and a.ID = @ID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
temp.IDname = "studentName";
temp.ThisValue = dr["studentName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantTeacheName";
temp.ThisValue = dr["ParticipantTeacheName"].ToString();
returnvalue.Add(temp);
temp.IDname = "teacherName";
temp.ThisValue = dr["TeacherName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantSocialWorkerName";
temp.ThisValue = dr["ParticipantSocialWorkerName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantAudiologistName";
temp.ThisValue = dr["ParticipantAudiologistName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantHeadName";
temp.ThisValue = dr["ParticipantHeadName"].ToString();
returnvalue.Add(temp);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "ConventionDate":
case "PlanExecutionFrameDate":
case "PlanExecutionOverDate":
temp.IDname = fldInfo.Name;
temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1);
returnvalue.Add(temp);
break;
default:
string namee = fldInfo.Name;
string namee2 = dr[fldInfo.Name].ToString();
temp.IDname = fldInfo.Name;
temp.ThisValue = dr[fldInfo.Name].ToString();
returnvalue.Add(temp);
break;
}
}
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string error = e.Message;
}
}
return returnvalue;
}
public List<CaseISPRecord> CreatCaseISPRecord(CaseISPRecord sTemperatureData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
//aDB.caseBTFunction();
//if (int.Parse(aDB._StaffhaveRoles[2]) == 1)
//{
return aDB.CreatCaseISPRecord(sTemperatureData);
//}
//else
//{
// return null;
//}
}