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;
}
