public string[] createStudentTrans(CreateStudentTrans StudentData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = ""; 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 = "INSERT INTO CaseTransitionService (Unit, StudentID, Age, AgeMonth, TurnYear, TurnStage, BeforeSchool, AfterSchool, PrimaryContact, "+ "PrimaryContactTel, Events, EventsNam1, EventsDate1, EventsContent1, EventsNam2, EventsDate2, EventsContent2, EventsNam3, EventsDate3, EventsContent3, " + "EventsNam4, EventsDate4, EventsContent4, ResettlementMessage, SendDocument, Report, TurnReport, FamilyReport, ParticipateMeeting, " + "ParticipateMeetingRecord, TrackCaseStatu, TrackCaseStatuRecord, SchoolVisit, CreateFileBy, UpFileBy, UpFileDate) " + "VALUES(@Unit, @StudentID, @Age, @AgeMonth, @TurnYear, @TurnStage, @BeforeSchool, @AfterSchool, @PrimaryContact, @PrimaryContactTel, @Events, @EventsNam1, " + "@EventsDate1, @EventsContent1, @EventsNam2, @EventsDate2, @EventsContent2, @EventsNam3, @EventsDate3, @EventsContent3, @EventsNam4, @EventsDate4, " + "@EventsContent4, @ResettlementMessage, @SendDocument, @Report, @TurnReport, @FamilyReport, @ParticipateMeeting, @ParticipateMeetingRecord, " + "@TrackCaseStatu, @TrackCaseStatuRecord, @SchoolVisit, @CreateFileBy, @UpFileBy, (getDate()))"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2].ToString()); cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentID); cmd.Parameters.Add("@Age", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.studentAge); cmd.Parameters.Add("@AgeMonth", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.studentMonth); cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transYear); cmd.Parameters.Add("@TurnStage", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transStage); cmd.Parameters.Add("@BeforeSchool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.bSchool); cmd.Parameters.Add("@AfterSchool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.aSchool); cmd.Parameters.Add("@PrimaryContact", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.contact); cmd.Parameters.Add("@PrimaryContactTel", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.contactTel); cmd.Parameters.Add("@Events", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transActivity); cmd.Parameters.Add("@EventsNam1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName1); cmd.Parameters.Add("@EventsDate1", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate1); cmd.Parameters.Add("@EventsContent1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent1); cmd.Parameters.Add("@EventsNam2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName2); cmd.Parameters.Add("@EventsDate2", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate2); cmd.Parameters.Add("@EventsContent2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent2); cmd.Parameters.Add("@EventsNam3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName3); cmd.Parameters.Add("@EventsDate3", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate3); cmd.Parameters.Add("@EventsContent3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent3); cmd.Parameters.Add("@EventsNam4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName4); cmd.Parameters.Add("@EventsDate4", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate4); cmd.Parameters.Add("@EventsContent4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent4); cmd.Parameters.Add("@ResettlementMessage", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.antonMessage); cmd.Parameters.Add("@SendDocument", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.sendDocument); cmd.Parameters.Add("@Report", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transReport); cmd.Parameters.Add("@TurnReport", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transReportFile); cmd.Parameters.Add("@FamilyReport", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transFamilyReportFile); cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.meeting); cmd.Parameters.Add("@ParticipateMeetingRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.meetingVisitReport); cmd.Parameters.Add("@TrackCaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.adaptation); cmd.Parameters.Add("@TrackCaseStatuRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.adaptationReport); cmd.Parameters.Add("@SchoolVisit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.schoolVisit); cmd.Parameters.Add("@SchoolVisitRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.schoolVisitRecord); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); if (returnValue[0] != "0") { sql = "select IDENT_CURRENT('CaseTransitionService') AS cID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue[1] = dr["cID"].ToString(); } dr.Close(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] setStudentTrans(CreateStudentTrans StudentData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = ""; string FileUpdatestring = ""; if (StudentData.transReportFile != null) { FileUpdatestring += " TurnReport=@TurnReport, "; } if (StudentData.transFamilyReportFile != null) { FileUpdatestring += " FamilyReport=@FamilyReport, "; } if (StudentData.schooladvocacyReport != null) { FileUpdatestring += " SchoolAdvocacyReport=@SchoolAdvocacyReport, "; } 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 = "UPDATE CaseTransitionService SET Age=@Age, AgeMonth=@AgeMonth, TurnYear=@TurnYear, TurnStage=@TurnStage, BeforeSchool=@BeforeSchool, " + "AfterSchool=@AfterSchool, PrimaryContact=@PrimaryContact, PrimaryContactTel=@PrimaryContactTel, Events=@Events, EventsNam1=@EventsNam1, " + "EventsDate1=@EventsDate1, EventsContent1=@EventsContent1, EventsNam2=@EventsNam2, EventsDate2=@EventsDate2, EventsContent2=@EventsContent2, " + "EventsNam3=@EventsNam3, EventsDate3=@EventsDate3, EventsContent3=@EventsContent3, EventsNam4=@EventsNam4, EventsDate4=@EventsDate4, " + "EventsContent4=@EventsContent4, ResettlementMessage=@ResettlementMessage, SendDocument=@SendDocument, Report=@Report, " + "ParticipateMeeting=@ParticipateMeeting, ParticipateMeetingRecord=@ParticipateMeetingRecord, TrackCaseStatu=@TrackCaseStatu, " + "TrackCaseStatuRecord=@TrackCaseStatuRecord, SchoolVisit=@SchoolVisit, SchoolVisitRecord=@SchoolVisitRecord, " + FileUpdatestring + "UpFileBy=@UpFileBy, UpFileDate=(getDate()) " + "WHERE ID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.ID); cmd.Parameters.Add("@Age", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.studentAge); cmd.Parameters.Add("@AgeMonth", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.studentMonth); cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transYear); cmd.Parameters.Add("@TurnStage", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transStage); cmd.Parameters.Add("@BeforeSchool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.bSchool); cmd.Parameters.Add("@AfterSchool", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.aSchool); cmd.Parameters.Add("@PrimaryContact", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.contact); cmd.Parameters.Add("@PrimaryContactTel", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.contactTel); cmd.Parameters.Add("@Events", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transActivity); cmd.Parameters.Add("@EventsNam1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName1); cmd.Parameters.Add("@EventsDate1", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate1); cmd.Parameters.Add("@EventsContent1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent1); cmd.Parameters.Add("@EventsNam2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName2); cmd.Parameters.Add("@EventsDate2", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate2); cmd.Parameters.Add("@EventsContent2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent2); cmd.Parameters.Add("@EventsNam3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName3); cmd.Parameters.Add("@EventsDate3", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate3); cmd.Parameters.Add("@EventsContent3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent3); cmd.Parameters.Add("@EventsNam4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityName4); cmd.Parameters.Add("@EventsDate4", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.transActivityDate4); cmd.Parameters.Add("@EventsContent4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transActivityContent4); cmd.Parameters.Add("@ResettlementMessage", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.antonMessage); cmd.Parameters.Add("@SendDocument", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.sendDocument); cmd.Parameters.Add("@Report", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.transReport); cmd.Parameters.Add("@TurnReport", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transReportFile); cmd.Parameters.Add("@FamilyReport", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.transFamilyReportFile); cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.meeting); cmd.Parameters.Add("@ParticipateMeetingRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.meetingVisitReport); cmd.Parameters.Add("@TrackCaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.adaptation); cmd.Parameters.Add("@TrackCaseStatuRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.adaptationReport); cmd.Parameters.Add("@SchoolVisit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.schoolVisit); cmd.Parameters.Add("@SchoolVisitRecord", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentData.schoolVisitRecord); cmd.Parameters.Add("@SchoolAdvocacyReport", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.schooladvocacyReport); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] setStudentTransData(CreateStudentTrans StudentData) { CaseDataBase SDB = new CaseDataBase(); if (int.Parse(SDB._StaffhaveRoles[1]) == 1) { return SDB.setStudentTrans(StudentData); } else { return new string[2] { _noRole, _errorMsg }; } }
public CreateStudentTrans getStudentTrans(string ID) { CreateStudentTrans returnValue = new CreateStudentTrans(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT StudentDatabase.StudentName, CaseTransitionService.* " + "FROM CaseTransitionService INNER JOIN StudentDatabase ON CaseTransitionService.StudentID=StudentDatabase.StudentID " + "WHERE CaseTransitionService.isDeleted=0 AND CaseTransitionService.ID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(ID); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue.ID = dr["ID"].ToString(); returnValue.caseUnit = dr["Unit"].ToString(); returnValue.studentID = dr["StudentID"].ToString(); returnValue.studentName = dr["StudentName"].ToString(); returnValue.studentAge = dr["Age"].ToString(); returnValue.studentMonth = dr["AgeMonth"].ToString(); returnValue.transYear = dr["TurnYear"].ToString(); returnValue.transStage = dr["TurnStage"].ToString(); returnValue.bSchool = dr["BeforeSchool"].ToString(); returnValue.aSchool = dr["AfterSchool"].ToString(); returnValue.contact = dr["PrimaryContact"].ToString(); returnValue.contactTel = dr["PrimaryContactTel"].ToString(); returnValue.transActivity = dr["Events"].ToString(); returnValue.transActivityName1 = dr["EventsNam1"].ToString(); returnValue.transActivityDate1 = DateTime.Parse(dr["EventsDate1"].ToString()).ToString("yyyy-MM-dd"); returnValue.transActivityContent1 = dr["EventsContent1"].ToString(); returnValue.transActivityName2 = dr["EventsNam2"].ToString(); returnValue.transActivityDate2 = DateTime.Parse(dr["EventsDate2"].ToString()).ToString("yyyy-MM-dd"); returnValue.transActivityContent2 = dr["EventsContent2"].ToString(); returnValue.transActivityName3 = dr["EventsNam3"].ToString(); returnValue.transActivityDate3 = DateTime.Parse(dr["EventsDate3"].ToString()).ToString("yyyy-MM-dd"); returnValue.transActivityContent3 = dr["EventsContent3"].ToString(); returnValue.transActivityName4 = dr["EventsNam4"].ToString(); returnValue.transActivityDate4 = DateTime.Parse(dr["EventsDate4"].ToString()).ToString("yyyy-MM-dd"); returnValue.transActivityContent4 = dr["EventsContent4"].ToString(); returnValue.antonMessage = dr["ResettlementMessage"].ToString(); returnValue.sendDocument = dr["SendDocument"].ToString(); returnValue.transReport = dr["Report"].ToString(); returnValue.transReportFile = dr["TurnReport"].ToString(); returnValue.transFamilyReportFile = dr["FamilyReport"].ToString(); returnValue.meeting = dr["ParticipateMeeting"].ToString(); returnValue.meetingVisitReport = dr["ParticipateMeetingRecord"].ToString(); returnValue.adaptation = dr["TrackCaseStatu"].ToString(); returnValue.adaptationReport = dr["TrackCaseStatuRecord"].ToString(); returnValue.schoolVisit = dr["SchoolVisit"].ToString(); returnValue.schooladvocacyReport = dr["SchoolAdvocacyReport"].ToString(); returnValue.schoolVisitRecord = dr["SchoolVisitRecord"].ToString(); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { returnValue.checkNo = "-1"; returnValue.errorMsg = e.Message.ToString(); } } return returnValue; }
public CreateStudentTrans getStudentTransDataBase(string ID) { CaseDataBase SDB = new CaseDataBase(); CreateStudentTrans returnValue = new CreateStudentTrans(); if (int.Parse(SDB._StaffhaveRoles[3]) == 1) { returnValue= SDB.getStudentTrans(ID); } else { returnValue.checkNo = _noRole; returnValue.errorMsg = _errorMsg; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (returnValue.caseUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { returnValue.checkNo = _getcheckNo; returnValue.errorMsg = _errorMsg; } return returnValue; }