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; }
/* 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; }
public int setTeachISPDate4(setTeachISP4 StudentISP) { TeachDataBase SDB = new TeachDataBase(); return SDB.setTeachISPPage4(StudentISP); }