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;
    }
Esempio n. 4
0
 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;
     //}
 }