Ejemplo n.º 1
0
    public setTeachISP4 GetTeachISPPage4(Int64 StudentISP)
    {
        setTeachISP4 returnValue = new setTeachISP4();

        DataBase Base = new DataBase();
        using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
        {
            try
            {
                Sqlconn.Open();
                string sql = " select a.PlanWriter3,a.PlanWriteFrameDate3,a.PlanWriteExecutor3,a.PlanRevise3,a.PlanReviseDate3,a.PlanReviseExecutor3" +
                             " ,a.HearingAssessment,a.HearingAssessmentByT,a.HearingAssessmentDateT,a.HearingAssessmentTool" +
                             " ,a.VocabularyAssessment,a.VocabularyAssessmentBy,a.VocabularyAssessmentDate,a.VocabularyAssessmentTool" +
                             " ,a.LanguageAssessment,a.LanguageAssessmentBy,a.LanguageAssessmentDate,a.LanguageAssessmentTool" +
                             " ,a.intelligenceAssessment,a.intelligenceAssessmentBy,a.intelligenceAssessmentDate,a.intelligenceAssessmentTool" +
                             " ,a.OtherAssessment,a.OtherAssessmentBy,a.OtherAssessmentDate,a.OtherAssessmentTool" +
                             " ,a.Hearing,a.CognitiveAbility,a.ConnectAbility,a.ActAbility,a.Relationship,a.EmotionalManagement" +
                             " ,a.SensoryFunction,a.HealthState,a.DailyLiving,a.LearningAchievement,a.Advantage,a.WeakCapacity" +
                             " ,b.TeachOrder,b.MasterOrder,b.TargetLong " +
                             " ,c.DetailOrder,c.TargetShort,c.DateStart,c.DateEnd,c.EffectiveDate,c.EffectiveMode,c.EffectiveResult,c.Decide "+
                             " ,PlanRevise3Name,PlanWriter3Name ,HearingAssessmentByName,VocabularyAssessmentByName,LanguageAssessmentByName,intelligenceAssessmentByName,OtherAssessmentByName" +
                             "  from CaseISPstate a " +
                             " Left join TeachingPlan b on a.id = b.ISPID  " +
                             " Left join TeachingPlanDetail c on c.TPMID = b.id " +

                             " left join ( select staffid as did , StaffName as PlanRevise3Name from staffDatabase ) d on a.PlanRevise3 = d.did " +
                             " left join ( select staffid as eid , StaffName as PlanWriter3Name from staffDatabase ) e on a.PlanWriter3 = e.eid " +

                             " left join ( select staffid as did , StaffName as HearingAssessmentByName from staffDatabase ) f on a.HearingAssessmentByT = f.did " +
                             " left join ( select staffid as eid , StaffName as VocabularyAssessmentByName from staffDatabase ) g on a.VocabularyAssessmentBy = g.eid " +
                             " left join ( select staffid as did , StaffName as LanguageAssessmentByName from staffDatabase ) h on a.LanguageAssessmentBy = h.did " +
                             " left join ( select staffid as eid , StaffName as intelligenceAssessmentByName from staffDatabase ) i on a.intelligenceAssessmentBy = i.eid " +
                             " left join ( select staffid as did , StaffName as OtherAssessmentByName from staffDatabase ) j on a.OtherAssessmentBy = j.did " +

                             " where a.id =@ISPID ";

                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@ISPID", SqlDbType.BigInt).Value = StudentISP;
                SqlDataReader dr = cmd.ExecuteReader();
                string TO = ""; string MO = "";
                TeachingPlan addValue = new TeachingPlan();
                addValue.TeachingPlanDetail = new List<TeachingPlanDetail>();
                returnValue.TeachingPlan = new List<TeachingPlan>();
                while (dr.Read())
                {
                    if (returnValue.PlanWriter3 == "" || returnValue.PlanWriter3 == null)
                    {
                        returnValue.HearingAssessmentByName = dr["HearingAssessmentByName"].ToString();
                        returnValue.VocabularyAssessmentByName = dr["VocabularyAssessmentByName"].ToString();
                        returnValue.LanguageAssessmentByName = dr["LanguageAssessmentByName"].ToString();
                        returnValue.intelligenceAssessmentByName = dr["intelligenceAssessmentByName"].ToString();
                        returnValue.OtherAssessmentByName = dr["OtherAssessmentByName"].ToString();

                        returnValue.PlanWriter3 = dr["PlanWriter3"].ToString();
                        returnValue.PlanRevise3Name = dr["PlanRevise3Name"].ToString();
                        returnValue.PlanWriter3Name = dr["PlanWriter3Name"].ToString();
                        returnValue.PlanWriteFrameDate3 =  DateTime.Parse(dr["PlanWriteFrameDate3"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.PlanWriteExecutor3 = dr["PlanWriteExecutor3"].ToString();
                        returnValue.PlanRevise3 = dr["PlanRevise3"].ToString();
                        returnValue.PlanReviseDate3 = DateTime.Parse(dr["PlanReviseDate3"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.PlanReviseExecutor3 = dr["PlanReviseExecutor3"].ToString();
                        returnValue.HearingAssessment = dr["HearingAssessment"].ToString();
                        returnValue.HearingAssessmentBy = dr["HearingAssessmentByT"].ToString();
                        returnValue.HearingAssessmentDate = DateTime.Parse(dr["HearingAssessmentDateT"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.HearingAssessmentTool = dr["HearingAssessmentTool"].ToString();
                        returnValue.VocabularyAssessment = dr["VocabularyAssessment"].ToString();
                        returnValue.VocabularyAssessmentBy = dr["VocabularyAssessmentBy"].ToString();
                        returnValue.VocabularyAssessmentDate = DateTime.Parse(dr["VocabularyAssessmentDate"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.VocabularyAssessmentTool = dr["VocabularyAssessmentTool"].ToString();
                        returnValue.LanguageAssessment = dr["LanguageAssessment"].ToString();
                        returnValue.LanguageAssessmentBy = dr["LanguageAssessmentBy"].ToString();
                        returnValue.LanguageAssessmentDate = DateTime.Parse(dr["LanguageAssessmentDate"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.LanguageAssessmentTool = dr["LanguageAssessmentTool"].ToString();
                        returnValue.intelligenceAssessment = dr["intelligenceAssessment"].ToString();
                        returnValue.intelligenceAssessmentBy = dr["intelligenceAssessmentBy"].ToString();
                        returnValue.intelligenceAssessmentDate = DateTime.Parse(dr["intelligenceAssessmentDate"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.intelligenceAssessmentTool = dr["intelligenceAssessmentTool"].ToString();
                        returnValue.OtherAssessment = dr["OtherAssessment"].ToString();
                        returnValue.OtherAssessmentBy = dr["OtherAssessmentBy"].ToString();
                        returnValue.OtherAssessmentDate = DateTime.Parse(dr["OtherAssessmentDate"].ToString()).ToString("yyyy-MM-dd");
                        returnValue.OtherAssessmentTool = dr["OtherAssessmentTool"].ToString();
                        returnValue.Hearing = dr["Hearing"].ToString();
                        returnValue.CognitiveAbility = dr["CognitiveAbility"].ToString();
                        returnValue.ConnectAbility = dr["ConnectAbility"].ToString();
                        returnValue.ActAbility = dr["ActAbility"].ToString();
                        returnValue.Relationship = dr["Relationship"].ToString();
                        returnValue.EmotionalManagement = dr["EmotionalManagement"].ToString();
                        returnValue.SensoryFunction = dr["SensoryFunction"].ToString();
                        returnValue.HealthState = dr["HealthState"].ToString();
                        returnValue.DailyLiving = dr["DailyLiving"].ToString();
                        returnValue.LearningAchievement = dr["LearningAchievement"].ToString();
                        returnValue.Advantage = dr["Advantage"].ToString();
                        returnValue.WeakCapacity = dr["WeakCapacity"].ToString();
                    }
                    TeachingPlanDetail addDetailValue = new TeachingPlanDetail();
                    if ((TO != dr["TeachOrder"].ToString() || MO != dr["MasterOrder"].ToString()) && !String.IsNullOrEmpty(addValue.MasterOrder))
                    {

                        returnValue.TeachingPlan.Add(addValue);
                        addValue = new TeachingPlan();
                        addValue.TeachingPlanDetail = new List<TeachingPlanDetail>();
                        addDetailValue = new TeachingPlanDetail();
                        addValue.MasterOrder = dr["MasterOrder"].ToString();
                        addValue.TeachOrder = dr["TeachOrder"].ToString();
                        addValue.TargetLong = dr["TargetLong"].ToString();

                        //addDetailValue.DetailOrder = dr["DetailOrder"].ToString();

                        addDetailValue.DetailOrder = dr["DetailOrder"].ToString();
                        addDetailValue.TargetShort = dr["TargetShort"].ToString();
                        addDetailValue.DateStart = DateTime.Parse(dr["DateStart"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.DateEnd = DateTime.Parse(dr["DateEnd"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.EffectiveDate = DateTime.Parse(dr["EffectiveDate"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.EffectiveMode = dr["EffectiveMode"].ToString();
                        addDetailValue.EffectiveResult = dr["EffectiveResult"].ToString();
                        addDetailValue.Decide = dr["Decide"].ToString();

                        addValue.TeachingPlanDetail.Add(addDetailValue);

                        TO = dr["TeachOrder"].ToString();
                        MO = dr["MasterOrder"].ToString();
                    }
                    else
                    {
                        addDetailValue = new TeachingPlanDetail();
                        if (String.IsNullOrEmpty( addValue.MasterOrder))
                        {
                            addValue.MasterOrder = dr["MasterOrder"].ToString();
                            addValue.TeachOrder = dr["TeachOrder"].ToString();
                            addValue.TargetLong = dr["TargetLong"].ToString();
                        }
                        addDetailValue.DetailOrder = dr["DetailOrder"].ToString();
                        addDetailValue.TargetShort = dr["TargetShort"].ToString();
                        addDetailValue.DateStart = DateTime.Parse(dr["DateStart"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.DateEnd = DateTime.Parse(dr["DateEnd"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.EffectiveDate = DateTime.Parse(dr["EffectiveDate"].ToString()).ToString("yyyy-MM-dd");
                        addDetailValue.EffectiveMode = dr["EffectiveMode"].ToString();
                        addDetailValue.EffectiveResult = dr["EffectiveResult"].ToString();
                        addDetailValue.Decide = dr["Decide"].ToString();
                        addValue.TeachingPlanDetail.Add(addDetailValue);

                        TO = dr["TeachOrder"].ToString();
                        MO = dr["MasterOrder"].ToString();

                    }
                }
                returnValue.TeachingPlan.Add(addValue);
                dr.Close();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                string ex = e.Message.ToString();
                //returnValue.ISP1Data = new setTeachISP1();
                //returnValue.ISP1Data.studentID = "-1";
                //returnValue.ISP1Data.studentName = e.Message.ToString();
            }
        }

        return returnValue;
    }
Ejemplo n.º 2
0
    /*
    public int setTeachISPPage3(setTeachISP3 StudentISP)
    {
        int returnValue = 0;
        DataBase Base = new DataBase();
        SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
        using (Sqlconn)
        {
            try
            {
                Sqlconn.Open();
                string sql = "UPDATE CaseISPstate SET PlanWriter2=@PlanWriter2, PlanWriteFrameDate2=@PlanWriteFrameDate2, PlanWriteExecutor2=@PlanWriteExecutor2, PlanRevise2=@PlanRevise2, " +
                    "PlanReviseDate2=@PlanReviseDate2, PlanReviseExecutor2=@PlanReviseExecutor2, AudiometryRight=@AudiometryRight, AudiometryLeft=@AudiometryLeft, " +
                    "Naked250L=@Naked250L, Naked250R=@Naked250R, Naked250=@Naked250, Naked500L=@Naked500L, Naked500R=@Naked500R, Naked500=@Naked500, Naked1000L=@Naked1000L, " +
                    "Naked1000R=@Naked1000R, Naked1000=@Naked1000, Naked2000L=@Naked2000L, Naked2000R=@Naked2000R, Naked2000=@Naked2000, Naked4000L=@Naked4000L, " +
                    "Naked4000R=@Naked4000R, Naked4000=@Naked4000, Naked8000L=@Naked8000L, Naked8000R=@Naked8000R, Naked8000=@Naked8000, NakedAverageL=@NakedAverageL, " +
                    "NakedAverageR=@NakedAverageR, NakedAverage=@NakedAverage, After250L=@After250L, After250R=@After250R, After250=@After250, After500L=@After500L, " +
                    "After500R=@After500R, After500=@After500, After1000L=@After1000L, After1000R=@After1000R, After1000=@After1000, After2000L=@After2000L, " +
                    "After2000R=@After2000R, After2000=@After2000, After4000L=@After4000L, After4000R=@After4000R, After4000=@After4000, After8000L=@After8000L, " +
                    "After8000R=@After8000R, After8000=@After8000, AfterAverageL=@AfterAverageL, AfterAverageR=@AfterAverageR, AfterAverage=@AfterAverage, " +
                    "AudiometryOther=@AudiometryOther, AudiometryAssessmentBy=@AudiometryAssessmentBy, AudiometryAssessmentDate=@AudiometryAssessmentDate, " +
                    "AudiometryAssessmentScoring=@AudiometryAssessmentScoring, Hearing1State=@Hearing1State, Hearing1Explain=@Hearing1Explain, Hearing2State=@Hearing2State, " +
                    "Hearing2Explain=@Hearing2Explain, Hearing3State=@Hearing3State, Hearing3Explain=@Hearing3Explain, Hearing4State=@Hearing4State, " +
                    "Hearing4Explain=@Hearing4Explain, HearingAssessmentBy=@HearingAssessmentBy, HearingAssessmentDate=@HearingAssessmentDate, " +
                    "HearingAssessmentScoring1=@HearingAssessmentScoring1, HearingAssessmentScoring2=@HearingAssessmentScoring2, " +
                    "HearingAssessmentScoring3=@HearingAssessmentScoring3, HearingAssessmentScoring4=@HearingAssessmentScoring4, " +
                    "AidsState1=@AidsState1, AidsState2=@AidsState2, AidsState3=@AidsState3, AidsState4=@AidsState4, AidsState5=@AidsState5, " +
                    "AidsAssessmentBy=@AidsAssessmentBy, AidsAssessmentDate=@AidsAssessmentDate, AidsAssessmentScoring=@AidsAssessmentScoring, Summary=@Summary " +
                    "WHERE ID=@ID AND StudentID=@StudentID";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = StudentISP.Column;
                cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@PlanWriter2", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@PlanWriteFrameDate2", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@PlanWriteExecutor2", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@PlanRevise2", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@PlanReviseDate2", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@PlanReviseExecutor2", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@AudiometryRight", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AudiometryLeft", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked250L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked250R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked250", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked500L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked500R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked500", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked1000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked1000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked1000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked2000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked2000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked2000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked4000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked4000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked4000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked8000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked8000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Naked8000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@NakedAverageL", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@NakedAverageR", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@NakedAverage", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After250L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After250R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After250", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After500L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After500R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After500", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After1000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After1000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After1000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After2000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After2000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After2000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After4000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After4000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After4000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After8000L", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After8000R", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@After8000", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AfterAverageL", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AfterAverageR", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AfterAverage", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AudiometryOther", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@AudiometryAssessmentBy", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AudiometryAssessmentDate", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@AudiometryAssessmentScoring", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@Hearing1State", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Hearing1Explain", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@Hearing2State", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Hearing2Explain", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@Hearing3State", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Hearing3Explain", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@Hearing4State", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Hearing4Explain", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@HearingAssessmentBy", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@HearingAssessmentDate", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@HearingAssessmentScoring1", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@HearingAssessmentScoring2", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@HearingAssessmentScoring3", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@HearingAssessmentScoring4", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@AidsState1", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsState2", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsState3", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsState4", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsState5", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsAssessmentBy", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@AidsAssessmentDate", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@AidsAssessmentScoring", SqlDbType.NVarChar).Value = "";
                cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = "";
                returnValue = cmd.ExecuteNonQuery();
                int LCount=Chk.CheckIntFunction(StudentISP.HISP.Count);
                for (int i = 0; i < LCount; i++)
                {
                    Int64 LColumn = 0;
                    Int64 SColumn = 0;
                    //長目標有資料,Update
                    if (StudentISP.HISP[i].LongColumn != null && StudentISP.HISP[i].LongColumn != 0)
                    {
                        LColumn = StudentISP.HISP[i].LongColumn;
                        sql = "UPDATE HearingISPLong SET TargetContentLong=@TargetContentLong WHERE CaseISPID=@CaseISPID AND HISPLID=@HISPLID";
                        cmd = new SqlCommand(sql, Sqlconn);
                        cmd.Parameters.Add("@HISPLID", SqlDbType.BigInt).Value = LColumn;
                        cmd.Parameters.Add("@CaseISPID", SqlDbType.BigInt).Value = "";
                        cmd.Parameters.Add("@TargetContentLong", SqlDbType.NVarChar).Value = "";
                        returnValue = cmd.ExecuteNonQuery();

                        int SCount = Chk.CheckIntFunction(StudentISP.HISP[i].ShortTarget.Count);
                        for (int j = 0; j < SCount; j++)
                        {
                            //短目標有資料,Update
                            if (StudentISP.HISP[i].ShortTarget[j].ShortColumn != null && StudentISP.HISP[i].ShortTarget[j].ShortColumn != 0)
                            {
                                SColumn = StudentISP.HISP[i].ShortTarget[j].ShortColumn;
                                sql = "UPDATE HearingISPShort SET HISPLID=@HISPLID, TargetContentShort=@TargetContentShort, DateStart=@DateStart, DateEnd=@DateEnd, " +
                                    "EffectiveDate=@EffectiveDate, EffectiveMode=@EffectiveMode, EffectiveResult=@EffectiveResult, Decide=@Decide " +
                                    "WHERE HISPLID=@HISPLID AND HISPSID=@HISPSID ";
                                cmd = new SqlCommand(sql, Sqlconn);
                                cmd.Parameters.Add("@HISPLID", SqlDbType.BigInt).Value = LColumn;
                                cmd.Parameters.Add("@HISPSID", SqlDbType.BigInt).Value = SColumn;
                                cmd.Parameters.Add("@TargetContentShort", SqlDbType.Date).Value = "";
                                cmd.Parameters.Add("@DateStart", SqlDbType.Date).Value = "";
                                cmd.Parameters.Add("@DateEnd", SqlDbType.Date).Value = "";
                                cmd.Parameters.Add("@EffectiveDate", SqlDbType.Date).Value = "";
                                cmd.Parameters.Add("@EffectiveMode", SqlDbType.Int).Value = "";
                                cmd.Parameters.Add("@EffectiveResult", SqlDbType.Int).Value = "";
                                cmd.Parameters.Add("@Decide", SqlDbType.Int).Value = "";
                                returnValue = cmd.ExecuteNonQuery();
                            }
                            //短目標無資料,create
                            else {
                                returnValue = this.createHISPShort(LColumn);
                            }
                        }
                    }
                    else
                    {  //長目標無資料,create
                         LColumn =this.createHISPLong(StudentISP.Column);
                         returnValue = this.createHISPShort(LColumn);
                    }
                }
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                string error = e.Message;
                returnValue = -1;
            }
        }
        return returnValue;
    }
    private Int64 createHISPLong(Int64 CaseISPID)
    {
        Int64 returnValue = 0;
        DataBase Base = new DataBase();
        SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
        using (Sqlconn)
        {
            try
            {
                Sqlconn.Open();
                string sql = "INSERT INTO HearingISPLong(Domain, CaseISPID, TargetContentLong) VALUES (@Domain, @CaseISPID, @TargetContentLong)";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@Domain", SqlDbType.TinyInt).Value = "";
                cmd.Parameters.Add("@CaseISPID", SqlDbType.BigInt).Value = "";
                cmd.Parameters.Add("@TargetContentLong", SqlDbType.NVarChar).Value = "";
                returnValue = cmd.ExecuteNonQuery();

                if (returnValue != 0)
                {
                    sql = "select IDENT_CURRENT('HearingISPLong') AS ISPLong";
                    cmd = new SqlCommand(sql, Sqlconn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        returnValue = Int64.Parse(dr["ISPLong"].ToString());
                    }
                    dr.Close();
                }

                Sqlconn.Close();
            }
            catch (Exception e)
            {
                string error = e.Message;
                returnValue = -1;
            }
        }
        return returnValue;
    }

    private int createHISPShort(Int64 LID)
    {
        int returnValue = 0;
        DataBase Base = new DataBase();
        SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
        using (Sqlconn)
        {
            try
            {
                Sqlconn.Open();
                string sql = "INSERT INTO HearingISPShort(HISPLID, TargetContentShort, DateStart, DateEnd, EffectiveDate, EffectiveMode, EffectiveResult, Decide) " +
                    "VALUES (@HISPLID,@TargetContentShort,@DateStart,@DateEnd,@EffectiveDate,@EffectiveMode,@EffectiveResult,@Decide)";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@HISPLID", SqlDbType.BigInt).Value = LID;
                cmd.Parameters.Add("@TargetContentShort", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@DateStart", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@DateEnd", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@EffectiveDate", SqlDbType.Date).Value = "";
                cmd.Parameters.Add("@EffectiveMode", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@EffectiveResult", SqlDbType.Int).Value = "";
                cmd.Parameters.Add("@Decide", SqlDbType.Int).Value = "";
                returnValue = cmd.ExecuteNonQuery();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                string error = e.Message;
                returnValue = -1;
            }
        }
        return returnValue;
    }

    */
    public int setTeachISPPage4(setTeachISP4 StudentISP)
    {
        int returnValue = 0;

        DateTime now = DateTime.Now;
        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 = " delete TeachingPlanDetail where TPMID in ( select id from TeachingPlan where ISPID = @ISPID )   delete TeachingPlan where ISPID = @ISPID ";
                int i = 1;
                int j = 1;
                sql += " update CaseISPstate set "+
                " PlanWriter3=@PlanWriter3,PlanWriteFrameDate3=@PlanWriteFrameDate3,PlanWriteExecutor3=@PlanWriteExecutor3,PlanRevise3=@PlanRevise3,PlanReviseDate3=@PlanReviseDate3,PlanReviseExecutor3=@PlanReviseExecutor3"+
                " ,HearingAssessment=@HearingAssessment,HearingAssessmentByT=@HearingAssessmentByT,HearingAssessmentDateT=@HearingAssessmentDateT,HearingAssessmentTool=@HearingAssessmentTool"+
                " ,VocabularyAssessment=@VocabularyAssessment,VocabularyAssessmentBy=@VocabularyAssessmentBy,VocabularyAssessmentDate=@VocabularyAssessmentDate,VocabularyAssessmentTool=@VocabularyAssessmentTool"+
                " ,LanguageAssessment=@LanguageAssessment,LanguageAssessmentBy=@LanguageAssessmentBy,LanguageAssessmentDate=@LanguageAssessmentDate,LanguageAssessmentTool=@LanguageAssessmentTool"+
                " ,intelligenceAssessment=@intelligenceAssessment,intelligenceAssessmentBy=@intelligenceAssessmentBy,intelligenceAssessmentDate=@intelligenceAssessmentDate,intelligenceAssessmentTool=@intelligenceAssessmentTool " +
                " ,OtherAssessment=@OtherAssessment,OtherAssessmentBy=@OtherAssessmentBy,OtherAssessmentDate=@OtherAssessmentDate,OtherAssessmentTool=@OtherAssessmentTool"+
                " ,Hearing=@Hearing,CognitiveAbility=@CognitiveAbility,ConnectAbility=@ConnectAbility,ActAbility=@ActAbility,Relationship=@Relationship"+
                " ,EmotionalManagement=@EmotionalManagement,SensoryFunction=@SensoryFunction,HealthState=@HealthState,DailyLiving=@DailyLiving"+
                " ,LearningAchievement=@LearningAchievement,Advantage=@Advantage,WeakCapacity=@WeakCapacity "+
                " where ID = @ISPID ";

                //PlanWriter3,PlanWriteFrameDate3,PlanWriteExecutor3,PlanRevise3,PlanReviseDate3,PlanReviseExecutor3,HearingAssessment,HearingAssessmentByT,HearingAssessmentDateT,HearingAssessmentTool,VocabularyAssessment,VocabularyAssessmentBy,VocabularyAssessmentDate,VocabularyAssessmentTool,LanguageAssessment,LanguageAssessmentBy,LanguageAssessmentDate,LanguageAssessmentTool,intelligenceAssessment,intelligenceAssessmentBy,intelligenceAssessmentDate,intelligenceAssessmentTool,OtherAssessment,OtherAssessmentBy,OtherAssessmentDate,OtherAssessmentTool,Hearing,CognitiveAbility,ConnectAbility,ActAbility,Relationship,EmotionalManagement,SensoryFunction,HealthState,DailyLiving,LearningAchievement,Advantage,WeakCapacity
                //PlanWriter3=@PlanWriter3,PlanWriteFrameDate3=@PlanWriteFrameDate3,PlanWriteExecutor3=@PlanWriteExecutor3,PlanRevise3=@PlanRevise3,PlanReviseDate3=@PlanReviseDate3,PlanReviseExecutor3=@PlanReviseExecutor3,HearingAssessment=@HearingAssessment,HearingAssessmentByT=@HearingAssessmentByT,HearingAssessmentDateT=@HearingAssessmentDateT,HearingAssessmentTool=@HearingAssessmentTool,VocabularyAssessment=@VocabularyAssessment,VocabularyAssessmentBy=@VocabularyAssessmentBy,VocabularyAssessmentDate=@VocabularyAssessmentDate,VocabularyAssessmentTool=@VocabularyAssessmentTool,LanguageAssessment=@LanguageAssessment,LanguageAssessmentBy=@LanguageAssessmentBy,LanguageAssessmentDate=@LanguageAssessmentDate,LanguageAssessmentTool=@LanguageAssessmentTool,intelligenceAssessment=@intelligenceAssessment,intelligenceAssessmentBy=@intelligenceAssessmentBy,intelligenceAssessmentDate=@intelligenceAssessmentDate,intelligenceAssessmentTool=@intelligenceAssessmentTool,OtherAssessment=@OtherAssessment,OtherAssessmentBy=@OtherAssessmentBy,OtherAssessmentDate=@OtherAssessmentDate,OtherAssessmentTool=@OtherAssessmentTool,Hearing=@Hearing,CognitiveAbility=@CognitiveAbility,ConnectAbility=@ConnectAbility,ActAbility=@ActAbility,Relationship=@Relationship,EmotionalManagement=@EmotionalManagement,SensoryFunction=@SensoryFunction,HealthState=@HealthState,DailyLiving=@DailyLiving,LearningAchievement=@LearningAchievement,Advantage=@Advantage,WeakCapacity=@WeakCapacity,

                foreach (TeachingPlan TPM in StudentISP.TeachingPlan)
                {
                    sql += " DECLARE @MasterID"+ i +" int; ";
                    sql += " insert into TeachingPlan ( ISPID,TeachOrder,MasterOrder,TargetLong  ) values ( @ISPID  ,@TeachOrder" + i + ",@MasterOrder" + i + ",@TargetLong" + i + " ) ";
                    sql += " select @MasterID" + i + " = (select @@identity) ";
                    j = 1;
                    foreach (TeachingPlanDetail TPD in TPM.TeachingPlanDetail )
                    {
                        sql += " insert into TeachingPlanDetail ( TPMID,DetailOrder,TargetShort,DateStart,DateEnd,EffectiveDate,EffectiveMode,EffectiveResult,Decide ";
                        sql += " )values( ";
                        sql += " @MasterID" + i + ", @DetailOrder" + i + "_" + j + ",@TargetShort" + i + "_" + j + ",@DateStart" + i + "_" + j + ",@DateEnd" + i + "_" + j + ",@EffectiveDate" + i + "_" + j + ",@EffectiveMode" + i + "_" + j + ",@EffectiveResult" + i + "_" + j + ",@Decide" + i + "_" + j + ")";
                        j++;
                    }
                    i++;
                }
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@ISPID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentISP.ISPID);
                cmd.Parameters.Add("@PlanWriter3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.PlanWriter3);
                cmd.Parameters.Add("@PlanWriteFrameDate3", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.PlanWriteFrameDate3);

                cmd.Parameters.Add("@PlanWriteExecutor3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.PlanWriteExecutor3);
                cmd.Parameters.Add("@PlanRevise3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.PlanRevise3);
                cmd.Parameters.Add("@PlanReviseDate3", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.PlanReviseDate3);

                cmd.Parameters.Add("@PlanReviseExecutor3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.PlanReviseExecutor3);
                cmd.Parameters.Add("@HearingAssessment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.HearingAssessment);
                cmd.Parameters.Add("@HearingAssessmentByT", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.HearingAssessmentBy);
                cmd.Parameters.Add("@HearingAssessmentDateT", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.HearingAssessmentDate);
                cmd.Parameters.Add("@HearingAssessmentTool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.HearingAssessmentTool);
                cmd.Parameters.Add("@VocabularyAssessment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.VocabularyAssessment);
                cmd.Parameters.Add("@VocabularyAssessmentBy", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.VocabularyAssessmentBy);
                cmd.Parameters.Add("@VocabularyAssessmentDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.VocabularyAssessmentDate);
                cmd.Parameters.Add("@VocabularyAssessmentTool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.VocabularyAssessmentTool);
                cmd.Parameters.Add("@LanguageAssessment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.LanguageAssessment);
                cmd.Parameters.Add("@LanguageAssessmentBy", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.LanguageAssessmentBy);
                cmd.Parameters.Add("@LanguageAssessmentDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.LanguageAssessmentDate);
                cmd.Parameters.Add("@LanguageAssessmentTool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.LanguageAssessmentTool);
                cmd.Parameters.Add("@intelligenceAssessment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.intelligenceAssessment);
                cmd.Parameters.Add("@intelligenceAssessmentBy", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.intelligenceAssessmentBy);
                cmd.Parameters.Add("@intelligenceAssessmentDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.intelligenceAssessmentDate);
                cmd.Parameters.Add("@intelligenceAssessmentTool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.intelligenceAssessmentTool);
                cmd.Parameters.Add("@OtherAssessment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.OtherAssessment);
                cmd.Parameters.Add("@OtherAssessmentBy", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.OtherAssessmentBy);
                cmd.Parameters.Add("@OtherAssessmentDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentISP.OtherAssessmentDate);
                cmd.Parameters.Add("@OtherAssessmentTool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.OtherAssessmentTool);
                cmd.Parameters.Add("@Hearing", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.Hearing);
                cmd.Parameters.Add("@CognitiveAbility", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.CognitiveAbility);
                cmd.Parameters.Add("@ConnectAbility", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.ConnectAbility);
                cmd.Parameters.Add("@ActAbility", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.ActAbility);
                cmd.Parameters.Add("@Relationship", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.Relationship);
                cmd.Parameters.Add("@EmotionalManagement", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.EmotionalManagement);
                cmd.Parameters.Add("@SensoryFunction", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.SensoryFunction);
                cmd.Parameters.Add("@HealthState", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.HealthState);
                cmd.Parameters.Add("@DailyLiving", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.DailyLiving);
                cmd.Parameters.Add("@LearningAchievement", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.LearningAchievement);
                cmd.Parameters.Add("@Advantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.Advantage);
                cmd.Parameters.Add("@WeakCapacity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentISP.WeakCapacity);
                i = 1;
                foreach (TeachingPlan TPM in StudentISP.TeachingPlan)
                {
                    cmd.Parameters.Add("@TeachOrder" + i, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(TPM.TeachOrder);
                    cmd.Parameters.Add("@MasterOrder" + i, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(TPM.MasterOrder);
                    cmd.Parameters.Add("@TargetLong" + i, SqlDbType.NVarChar).Value = Chk.CheckStringFunction(TPM.TargetLong);
                    j = 1;
                    foreach (TeachingPlanDetail TPD in TPM.TeachingPlanDetail)
                    {
                        cmd.Parameters.Add("@DetailOrder" +i+"_" + j, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(TPD.DetailOrder);
                        cmd.Parameters.Add("@TargetShort" + i + "_" + j, SqlDbType.NVarChar).Value = Chk.CheckStringFunction(TPD.TargetShort);
                        cmd.Parameters.Add("@DateStart" + i + "_" + j, SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(TPD.DateStart);
                        cmd.Parameters.Add("@DateEnd" + i + "_" + j, SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(TPD.DateEnd);
                        cmd.Parameters.Add("@EffectiveDate" + i + "_" + j, SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(TPD.EffectiveDate);
                        cmd.Parameters.Add("@EffectiveMode" + i + "_" + j, SqlDbType.NVarChar).Value = Chk.CheckStringFunction(TPD.EffectiveMode);
                        cmd.Parameters.Add("@EffectiveResult" + i + "_" + j, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(TPD.EffectiveResult);
                        cmd.Parameters.Add("@Decide" + i + "_" + j, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(TPD.Decide);
                        j++;
                    }
                    i++;
                }

                returnValue = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                returnValue = -1;
                string ex = e.Message.ToString();
            }
        }

        return returnValue;
    }
Ejemplo n.º 3
0
 public int setTeachISPDate4(setTeachISP4 StudentISP)
 {
     TeachDataBase SDB = new TeachDataBase();
     return SDB.setTeachISPPage4(StudentISP);
 }