public string[] createSalaryValue(string Value) { 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 PointValue (PointValue, CreateFileBy, CreateFileDate) " + "VALUES(@PointValue, @CreateFileBy, (getDate()))"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@PointValue", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(Value); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message; } } return returnValue; }
public string[] createAidsBrandList(CreateAidsBrand structValue) { 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 AidsBrandTable (Category, Brand, CreateFileBy, CreateFileDate, UpFileBy , UpFileDate) " + "VALUES(@Category, @Brand, @CreateFileBy, getDate(), @UpFileBy, getDate() )"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(structValue.brandType); cmd.Parameters.Add("@Brand", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structValue.brandName); 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; } } return(returnValue); }
public string[] delAidsBrandList(string sID) { 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 = "UPDATE AidsBrandTable SET isDeleted=1, UpFileBy=@UpFileBy, UpFileDate=getDate() " + "WHERE ID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sID); 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; } } return(returnValue); }
public string[] createSalaryValue(string Value) { 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 PointValue (PointValue, CreateFileBy, CreateFileDate) " + "VALUES(@PointValue, @CreateFileBy, (getDate()))"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@PointValue", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(Value); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message; } } return(returnValue); }
public string[] createAidsBrandList(CreateAidsBrand structValue) { 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 AidsBrandTable (Category, Brand, CreateFileBy, CreateFileDate, UpFileBy , UpFileDate) " + "VALUES(@Category, @Brand, @CreateFileBy, getDate(), @UpFileBy, getDate() )"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(structValue.brandType); cmd.Parameters.Add("@Brand", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structValue.brandName); 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; } } return returnValue; }
public void loginsuccess(List<string> result) { _Staff_ID = result[_Index_ID]; _Staff_Name = result[_Index_Name]; _Staff_UnitID = result[_Index_Unit]; StaffDataBase myDataBase = new StaffDataBase(); _Staff_UnitName = myDataBase.getUnitName(_Staff_UnitID); _Header_Info_innerString = getLoginInfoString(); }
public void loginsuccess(List <string> result) { _Staff_ID = result[_Index_ID]; _Staff_Name = result[_Index_Name]; _Staff_UnitID = result[_Index_Unit]; StaffDataBase myDataBase = new StaffDataBase(); _Staff_UnitName = myDataBase.getUnitName(_Staff_UnitID); _Header_Info_innerString = getLoginInfoString(); }
public string[] createExternalTeacherData(CreateExternal StaffData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; 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 ExternalTeacherDatabase (Unit, TeachName, TeachIdentity, AddressZip1, AddressCity1, AddressOther1, AddressZip2, AddressCity2, AddressOther2, Phone, Phone2, Email, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " + "VALUES (@Unit, @TeachName, @TeachIdentity, @AddressZip1, @AddressCity1, @AddressOther1, @AddressZip2, @AddressCity2, @AddressOther2, @Phone, @Phone2, @Email, @CreateFileBy, getDate(), @UpFileBy, getDate())"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@TeachName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffName); cmd.Parameters.Add("@TeachIdentity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffTWID); cmd.Parameters.Add("@AddressZip1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddressZip); cmd.Parameters.Add("@AddressCity1", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.censusCity); cmd.Parameters.Add("@AddressOther1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddress); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.address); cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone); cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone2); cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffemail); 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('ExternalTeacherDatabase') 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 void caseBTFunction() { StaffDataBase sDB = new StaffDataBase(); RolesStruct StaffAllRoles = sDB.getStaffRoles(HttpContext.Current.User.Identity.Name); //_StaffhaveRoles[0] = StaffAllRoles.caseStu[0];//權限 char[] haveRolesitem = new char[4] { '0', '0', '0', '0' }; haveRolesitem = StaffAllRoles.caseBT[0].ToCharArray(); _StaffhaveRoles[0] = haveRolesitem[0].ToString();//刪除 _StaffhaveRoles[1] = haveRolesitem[1].ToString();//更新 _StaffhaveRoles[2] = haveRolesitem[2].ToString();//新增 _StaffhaveRoles[3] = haveRolesitem[3].ToString();//查詢 _StaffhaveRoles[4] = StaffAllRoles.caseBT[1];//跨區與否 }
public string[] setStaffRolesData(List <string> RolesList, string sID) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = ""; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = ""; SqlCommand cmd; StaffDataBase sDB = new StaffDataBase(); List <string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (RolesList[0] == "0") { sql = "INSERT INTO Staff_Competence_Roles (StaffID, Roles1, Roles2, Roles3, Roles4, Roles5, CreateFileBy, UpFileBy, UpFileDate) " + "VALUES(@StaffID, @Roles1, @Roles2, @Roles3, @Roles4, @Roles5, @CreateFileBy, @UpFileBy, (getDate()))"; } else { sql = "UPDATE Staff_Competence_Roles SET Roles1=@Roles1, Roles2=@Roles2, Roles3=@Roles3, Roles4=@Roles4, " + "Roles5=@Roles5, UpFileBy=@UpFileBy, UpFileDate=(getDate()) WHERE StaffID=@StaffID AND ID=@ID"; } cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[0]); cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sID); cmd.Parameters.Add("@Roles1", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[1]); cmd.Parameters.Add("@Roles2", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[2]); cmd.Parameters.Add("@Roles3", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[3]); cmd.Parameters.Add("@Roles4", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[4]); cmd.Parameters.Add("@Roles5", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RolesList[5]); 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; } } return(returnValue); }
public Audiometry() { // // TODO: Add constructor logic here // StaffDataBase sDB = new StaffDataBase(); RolesStruct StaffAllRoles = sDB.getStaffRoles(HttpContext.Current.User.Identity.Name); //_StaffhaveRoles[0] = StaffAllRoles.caseStu[0];//權限 char[] haveRolesitem = new char[4] { '0', '0', '0', '0' }; haveRolesitem = StaffAllRoles.hearing[0].ToCharArray(); _StaffhaveRoles[0] = haveRolesitem[0].ToString();//刪除 _StaffhaveRoles[1] = haveRolesitem[1].ToString();//更新 _StaffhaveRoles[2] = haveRolesitem[2].ToString();//新增 _StaffhaveRoles[3] = haveRolesitem[3].ToString();//查詢 _StaffhaveRoles[4] = StaffAllRoles.hearing[1];//跨區與否 }
private string SearchSingleTeachCondition(SearchCaseISPRecord SearchStructure, int type) { string ConditionReturn = ""; string DateBase = "1900-01-01"; if (SearchStructure.txtstudentName != null) { ConditionReturn += " AND StudentName like (@StudentName) "; } if (SearchStructure.txtteacherName != null) { ConditionReturn += " AND TeacherName like (@TeacherName) "; } if (SearchStructure.txtConventionDatestart != null && SearchStructure.txtConventionDateend != null && SearchStructure.txtConventionDatestart != DateBase && SearchStructure.txtConventionDateend != DateBase) { ConditionReturn += " AND ( PlanDateStart BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) or PlanDateEnd BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) ) "; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); caseBTFunction(); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND b.Unit =" + UserFile[2] + " "; } if (type == 0) { ConditionReturn += " AND b.CaseStatu2 =" + type + " "; } return ConditionReturn; }
private string SearchClassNameCondition(SearchClassName SearchStructure) { string ConditionReturn = ""; if (SearchStructure.txtstaffID != null) { ConditionReturn += " AND TeacherID=(@txtstaffID) "; } if (SearchStructure.txtstaffName != null) { ConditionReturn += " AND TeacherName like (@txtstaffName) "; } if (SearchStructure.txtClassID != null) { ConditionReturn += " AND ClassID=(@txtClassID) "; } if (SearchStructure.txtClassName != null) { ConditionReturn += " AND ClassName like (@txtClassName) "; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); caseBTFunction(); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND a.Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public string[] setBookData1(SearchBookResult bookDataResult) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; 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 BookDatabase SET BookName=@executionTitle, Author=@executionAuthor, Press=@executionPress, PressDate=@executionPressDate, " + "Remark=@executionRemark, UpFileBy=@UpFileBy, UpFileDate=(getDate()),bookUseTo=@executionbookUseTo,bookComefrom=@executionbookComefrom,bookGeter=@executionbookGeter,bookScrapstatus=@executionbookScrapstatus WHERE BookID=@bID AND isDeleted=0"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@bID", SqlDbType.BigInt).Value = bookDataResult.bID; cmd.Parameters.Add("@executionTitle", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionTitle); cmd.Parameters.Add("@executionAuthor", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionAuthor); cmd.Parameters.Add("@executionPress", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionPress); cmd.Parameters.Add("@executionPressDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(bookDataResult.executionPressDate); cmd.Parameters.Add("@executionRemark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionRemark); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@executionbookUseTo", SqlDbType.Int).Value = Chk.CheckStringFunction(bookDataResult.executionbookUseTo); cmd.Parameters.Add("@executionbookComefrom", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionbookComefrom); cmd.Parameters.Add("@executionbookGeter", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookDataResult.executionbookGeter); cmd.Parameters.Add("@executionbookScrapstatus", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookDataResult.executionbookScrapstatus); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] delBookData(Int64 bID) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; 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 BookDatabase SET isDeleted=1, UpFileBy=@UpFileBy, UpFileDate=(getDate()) WHERE BookID=@bID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@bID", SqlDbType.BigInt).Value = bID; 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[] CreatTeachServiceSupervisor(TeachServiceSupervisor sTemperatureData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; string strSql = ""; string strSqlPara = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": break; case "isDeleted": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "0 "; break; case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } 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); string sql = " insert into TeachServiceSupervisor ( " + strSql + " )values( " + strSqlPara + ") select @@identity as ID "; Sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql, Sqlconn); //cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData.AcademicYear.ToString()); //cmd.Parameters.Add("@VDID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData.ID.ToString()); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": break; case "CreateFileBy": case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; case "ClassDate": if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, Convert.ToDateTime( fldInfo.GetValue(sTemperatureData).ToString()).AddYears(1911).ToShortDateString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; } } SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { returnValue[0] = dr["ID"].ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] createTeacherTemperatureData(CreateTeacherSystem temperatureDataSystem) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DateTime now = DateTime.Now; deleteStudentTemperatureData(temperatureDataSystem.txtpeopleID, now.Year.ToString(), now.Month.ToString(), now.Day.ToString()); 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 TeacherTemperature ([TeacherID],[TeacherTemperature],[CheckContent],[CreateFileBy],[CreateFileDate],[UpFileBy],[UpFileDate] " + " ) VALUES " + "(@TeacherID, @TeacherTemperature, @CheckContent, @CreateFileBy, @CreateFileDate , @UpFileBy, (getDate()))"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@TeacherID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.txtpeopleID); cmd.Parameters.Add("@TeacherTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.TeacherTemp); cmd.Parameters.Add("@CheckContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.CheckContent); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@CreateFileDate", SqlDbType.Date).Value = (string.IsNullOrEmpty(temperatureDataSystem.Year) ? Chk.CheckStringFunction(now.Year.ToString()) : Chk.CheckStringFunction(temperatureDataSystem.Year)) + "/" + (string.IsNullOrEmpty(temperatureDataSystem.Month) ? Chk.CheckStringFunction(now.Month.ToString()) : Chk.CheckStringFunction(temperatureDataSystem.Month)) + "/" + (string.IsNullOrEmpty(temperatureDataSystem.Day) ? Chk.CheckStringFunction(now.Day.ToString()) : Chk.CheckStringFunction(temperatureDataSystem.Day)); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] createHearLoss(UpdateHearLoss temperatureDataSystem, int SID) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "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 = " DECLARE @MasterID int " + " insert into Hearing_Loss_AnsMaster ( StudentID , LossDate , Tool,page) values ( @StudentID ,@LossDate, @Tool,@page) " + " select @MasterID = (select @@identity) "; for (int i = 0; i < Chk.CheckStringFunction(temperatureDataSystem.anser).Split('|').Length; i++) { sql += " insert into Hearing_Loss_AnsDetail (MasterID , Anser) values (@MasterID , @Anser" + i.ToString() + ") "; } SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID; string date = temperatureDataSystem.Date; cmd.Parameters.Add("@LossDate", SqlDbType.NVarChar).Value = date; cmd.Parameters.Add("@Tool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.tool); cmd.Parameters.Add("@page", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.page); for (int i = 0; i < Chk.CheckStringFunction(temperatureDataSystem.anser).Split('|').Length; i++) { cmd.Parameters.Add("@Anser" + i.ToString(), SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.anser.Split('|').GetValue(i).ToString()); } returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
//顯示學生姓名(有點多餘) public List<SearchStudentResult> ShowStudent(int SID) { List<SearchStudentResult> returnvalue = new List<SearchStudentResult>(); SearchStudentResult temp = new SearchStudentResult(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "select StudentName from StudentDatabase where ID=@StudentID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { //temp.txtstudentSex = int.Parse(dr["StudentSex"].ToString()); temp.txtstudentName = dr["StudentName"].ToString(); //temp.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); returnvalue.Add(temp); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { } } return returnvalue; }
public List<AchievementAssessmentLoad> ShowCaseStudy(int ID) { string strSql = ""; List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>(); AchievementAssessmentLoad temp = new AchievementAssessmentLoad(); CaseStudy sTemperatureData = new CaseStudy(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": break; case "WriteDate": case "RecordedDateTime": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , c.WriteNameName , d.WriteName1Name,e.RecordedByName , " + strSql + " from CaseStudy a "; sql += " left join ( select staffid as cid , StaffName as WriteNameName from staffDatabase ) c on a.WriteName = c.cid "; sql += " left join ( select staffid as did , StaffName as WriteName1Name from staffDatabase ) d on a.WriteName1 = d.did "; sql += " left join ( select staffid as eid , StaffName as RecordedByName from staffDatabase ) e on a.RecordedBy = e.eid "; sql += " left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { temp.IDname = "studentName"; temp.ThisValue = dr["studentName"].ToString(); returnvalue.Add(temp); temp.IDname = "WriteNameName"; temp.ThisValue = dr["WriteNameName"].ToString(); returnvalue.Add(temp); temp.IDname = "WriteName1Name"; temp.ThisValue = dr["WriteName1Name"].ToString(); returnvalue.Add(temp); temp.IDname = "RecordedByName"; temp.ThisValue = dr["RecordedByName"].ToString(); returnvalue.Add(temp); temp.IDname = "studentbirthday"; temp.ThisValue = dr["studentbirthday"].ToString(); returnvalue.Add(temp); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": break; case "WriteDate": case "RecordedDateTime": temp.IDname = fldInfo.Name; temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); returnvalue.Add(temp); break; default: string namee = fldInfo.Name; string namee2 = dr[fldInfo.Name].ToString(); temp.IDname = fldInfo.Name; temp.ThisValue = dr[fldInfo.Name].ToString(); returnvalue.Add(temp); break; } } } dr.Close(); Sqlconn.Close(); } catch (Exception e) { string error = e.Message; } } return returnvalue; }
public string[] createBookDataBase(CreateBook bookData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; 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 BookDatabase (Unit, BookCodeID, BookName, CategoryID, Author, Press, PressDate, Remark, ArchivingDate, Status, " + "CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted ,bookUseTo,bookComefrom ,bookGeter,bookScrapstatus) VALUES " + "(@Unit, @BookCodeID, @BookName, @CategoryID, @Author, @Press, @PressDate, @Remark, @ArchivingDate, 1, " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0 , @bookUseTo,@bookComefrom ,@bookGeter,@bookScrapstatus)"; // bookUseTo,bookComefrom ,bookGeter,bookScrapstatus SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@BookCodeID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookNumber); cmd.Parameters.Add("@BookName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookTitle); cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookData.bookClassification); cmd.Parameters.Add("@Author", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookAuthor); cmd.Parameters.Add("@Press", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookPress); cmd.Parameters.Add("@PressDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(bookData.bookPressDate); cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookRemark); cmd.Parameters.Add("@ArchivingDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(bookData.bookFilingDate); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@bookUseTo", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookData.bookUseTo); cmd.Parameters.Add("@bookComefrom", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookComefrom); cmd.Parameters.Add("@bookGeter", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.bookGeter); cmd.Parameters.Add("@bookScrapstatus", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookData.bookScrapstatus); returnValue[0] = cmd.ExecuteNonQuery().ToString(); if (returnValue[0] != "0") { Int64 Column = 0; sql = "select IDENT_CURRENT('BookDatabase') AS bID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["bID"].ToString()); } dr.Close(); if (Column != 0) { // sql = "SELECT Count(*) AS QCOUNT FROM BookDatabase WHERE CategoryID=(@CategoryID) and Unit = @Unit "; sql = "SELECT top 1 CAST (SUBSTRING(BookCodeID,6,4 ) as int) AS QCOUNT FROM BookDatabase WHERE CategoryID=(@CategoryID) and Unit = @Unit order by BookCodeID desc "; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@CategoryID", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(bookData.bookClassification); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); SqlDataReader dr2 = cmd.ExecuteReader(); string stuNumber = "1"; if (dr2.Read()) { stuNumber = (Chk.CheckStringtoIntFunction(dr2["QCOUNT"].ToString()) + 1).ToString();//cmd.ExecuteScalar().ToString(); } dr2.Close(); string stuIDName = CreateFileName[2] + Chk.CheckStringFunction(bookData.bookClassificationCode) + stuNumber.PadLeft(4, '0'); sql = "UPDATE BookDatabase SET BookCodeID=(@BookCodeID) WHERE BookID=(@BID) "; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BID", SqlDbType.BigInt).Value = Column; cmd.Parameters.Add("@BookCodeID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(stuIDName); returnValue[0] = cmd.ExecuteNonQuery().ToString(); } } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<AchievementAssessmentLoad> ShowCaseIspRecord(int ID) { string strSql = ""; List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>(); AchievementAssessmentLoad temp = new AchievementAssessmentLoad(); CaseISPRecord sTemperatureData = new CaseISPRecord(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": break; case "ConventionDate": case "PlanExecutionFrameDate": case "PlanExecutionOverDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); //string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , " + strSql + " from CaseStudy a left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 "; string sql = "select b.StudentName , c.TeacherName, ParticipantTeacheName,ParticipantSocialWorkerName,ParticipantAudiologistName,ParticipantHeadName , "; sql += strSql; sql += " FROM CaseISPRecord a left join (select id, studentName from studentDatabase) b on a.studentid = b.id "; sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid "; sql += " left join (select staffid as did , StaffName as ParticipantTeacheName from staffDatabase) d on a.ParticipantTeache = d.did "; sql += " left join (select staffid as eid , StaffName as ParticipantSocialWorkerName from staffDatabase) e on a.ParticipantSocialWorker = e.eid "; sql += " left join (select staffid as fid , StaffName as ParticipantAudiologistName from staffDatabase) f on a.ParticipantAudiologist = f.fid "; sql += " left join (select staffid as gid , StaffName as ParticipantHeadName from staffDatabase) g on a. ParticipantHead = g.gid "; sql += " WHERE isnull(a.isDeleted,0) = 0 and a.ID = @ID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { temp.IDname = "studentName"; temp.ThisValue = dr["studentName"].ToString(); returnvalue.Add(temp); temp.IDname = "ParticipantTeacheName"; temp.ThisValue = dr["ParticipantTeacheName"].ToString(); returnvalue.Add(temp); temp.IDname = "teacherName"; temp.ThisValue = dr["TeacherName"].ToString(); returnvalue.Add(temp); temp.IDname = "ParticipantSocialWorkerName"; temp.ThisValue = dr["ParticipantSocialWorkerName"].ToString(); returnvalue.Add(temp); temp.IDname = "ParticipantAudiologistName"; temp.ThisValue = dr["ParticipantAudiologistName"].ToString(); returnvalue.Add(temp); temp.IDname = "ParticipantHeadName"; temp.ThisValue = dr["ParticipantHeadName"].ToString(); returnvalue.Add(temp); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": break; case "ConventionDate": case "PlanExecutionFrameDate": case "PlanExecutionOverDate": temp.IDname = fldInfo.Name; temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); returnvalue.Add(temp); break; default: string namee = fldInfo.Name; string namee2 = dr[fldInfo.Name].ToString(); temp.IDname = fldInfo.Name; temp.ThisValue = dr[fldInfo.Name].ToString(); returnvalue.Add(temp); break; } } } dr.Close(); Sqlconn.Close(); } catch (Exception e) { string error = e.Message; } } return returnvalue; }
public List<AchievementAssessmentLoad> ShowAchievementAssessment(int ID) { string strSql = ""; List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>(); AchievementAssessmentLoad temp = new AchievementAssessmentLoad(); AchievementAssessment sTemperatureData = new AchievementAssessment(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": break; case "Intelligence_Date": case "AuditorySkills_Date": case "Vocabulary_Date": case "Vocabulary1_Date": case "Language1_Date": case "Language2_Date": case "Language3_Date": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "select b.studentname , " + strSql + " from AchievementAssessment a left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { temp.IDname = "studentName"; temp.ThisValue = dr["studentName"].ToString(); returnvalue.Add(temp); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": break; case "Intelligence_Date": case "AuditorySkills_Date": case "Vocabulary_Date": case "Vocabulary1_Date": case "Language1_Date": case "Language2_Date": case "Language3_Date": temp.IDname = fldInfo.Name; temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); returnvalue.Add(temp); break; default: string namee = fldInfo.Name; string namee2 = dr[fldInfo.Name].ToString(); temp.IDname = fldInfo.Name; temp.ThisValue = dr[fldInfo.Name].ToString(); returnvalue.Add(temp); break; } } } dr.Close(); Sqlconn.Close(); } catch (Exception e) { string error = e.Message; } } return returnvalue; }
public List<CreateBookSystem> setBookReturnDataBase(CreateBookSystem bookSystemData) { List<CreateBookSystem> returnValue = new List<CreateBookSystem>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { string[] checkBookData = getBookDataName(bookSystemData.bookReturnCode); if (int.Parse(checkBookData[0]) > 0 && int.Parse(checkBookData[1]) == 2) { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "SELECT ID,BorrowerIdentity FROM BookManage WHERE BookID=@bookID AND Status=1 AND isDeleted=0"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2; cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]); SqlDataReader dr = cmd.ExecuteReader(); Int64 Column = 0; string borrowerStatus = "0"; if (dr.Read()) { Column = Int64.Parse(dr["ID"].ToString()); borrowerStatus = dr["BorrowerIdentity"].ToString(); } dr.Close(); if (Column != 0) { sql = "UPDATE BookManage SET Status=@Status,ReturnDate=(getDate()) WHERE ID=@ID AND Status=1 AND isDeleted=0"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Column; cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2; string numberStr = cmd.ExecuteNonQuery().ToString(); if (numberStr != "0") { sql = "UPDATE BookDatabase SET Status=@Status WHERE BookID=@bookID AND isDeleted=0"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 1; cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]); string numberStr2 = cmd.ExecuteNonQuery().ToString(); if (numberStr2 != "0") { if (borrowerStatus == "1") { sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName FROM BookManage " + "RIGHT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " + "RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " + "WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID"; } else if (borrowerStatus == "2") { sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StudentDatabase.StudentName AS BorrowerName FROM BookManage " + "RIGHT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " + "RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " + "WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID"; } cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BID", SqlDbType.BigInt).Value = Column; dr = cmd.ExecuteReader(); while (dr.Read()) { CreateBookSystem addValue = new CreateBookSystem(); addValue.bID = Column.ToString(); addValue.borrowStatus = dr["Status"].ToString(); addValue.borrowerClassID = dr["ClassID"].ToString(); addValue.borrowerName = dr["BorrowerName"].ToString(); addValue.borrowerID = dr["BorrowerID"].ToString(); addValue.borrowerStatus = dr["BorrowerIdentity"].ToString(); addValue.bookCode = dr["BookCodeID"].ToString(); addValue.bookName = dr["BookName"].ToString(); addValue.borrowDate = DateTime.Parse(dr["BorrowDate"].ToString()).ToString("yyyy-MM-dd"); addValue.expireDate = DateTime.Parse(dr["MaturityDate"].ToString()).ToString("yyyy-MM-dd"); addValue.restoreDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd"); addValue.checkNo = "1"; returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } } } Sqlconn.Close(); } else if (int.Parse(checkBookData[1]) == 1) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = "0"; addValue.errorMsg = "此本圖書已歸還"; returnValue.Add(addValue); } else if (int.Parse(checkBookData[0]) == 0) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = checkBookData[0]; addValue.errorMsg = "查無此本圖書"; returnValue.Add(addValue); } else { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = checkBookData[0]; addValue.errorMsg = checkBookData[1]; returnValue.Add(addValue); } } catch (Exception e) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
private string SearchVoiceDistanceCondition(SearchStudent SearchStructure, int type) { string ConditionReturn = ""; string DateBase = "1900-01-01"; if (SearchStructure.txtstudentID != null) { ConditionReturn += " AND StudentID=(@StudentID) "; } if (SearchStructure.txtstudentName != null) { ConditionReturn += " AND StudentName like (@StudentName) "; } if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0") { ConditionReturn += " AND StudentSex=(@StudentSex) "; } if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase) { ConditionReturn += " AND StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) "; } if (SearchStructure.txtAcademicYearstart != null && SearchStructure.txtAcademicYearend != null && SearchStructure.txtAcademicYearstart != DateBase && SearchStructure.txtAcademicYearend != DateBase) { ConditionReturn += " AND AcademicYear BETWEEN (@AcademicYearstart) AND (@AcademicYearend) "; // 教學管理使用 不知是否會與其他衝突 } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); caseBTFunction(); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND b.Unit =" + UserFile[2] + " "; } if (type == 0) { ConditionReturn += " AND b.CaseStatu2 =" + type + " "; } return ConditionReturn; }
public List<AchievementAssessment> CreatAchievementAssessment(AchievementAssessment sTemperatureData) { List<AchievementAssessment> returnValue = new List<AchievementAssessment>(); AchievementAssessment temp = new AchievementAssessment(); DataBase Base = new DataBase(); string strSql = ""; string strSqlPara = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; //cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Date).Value = DateTime.Now.ToLongDateString(); break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = " insert into AchievementAssessment ( " + strSql + ")values(" + strSqlPara + ") "; sql += " select @@identity as id "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; case "CreateFileBy": case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, System.DBNull.Value); } break; } } SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { temp.ID = dr["id"].ToString(); } } catch (Exception e) { temp.ID = e.Message.ToString(); } Sqlconn.Close(); } //temp.ID = strSql; returnValue.Add(temp); return returnValue; }
public List<CaseStudy> UpdateCaseStudy(CaseStudy sTemperatureData) { List<CaseStudy> returnValue = new List<CaseStudy>(); CaseStudy temp = new CaseStudy(); DataBase Base = new DataBase(); string strSql = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "CreateFileBy": case "CreateFileDate": case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; //case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=getdate()"; // strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; //cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Date).Value = DateTime.Now.ToLongDateString(); break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=@" + fldInfo.Name; //strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = " update CaseStudy set " + strSql + " where id=@ID "; //sql += " select @@identity as id "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { //case "ID": case "CreateFileDate": case "Unit": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; } } cmd.ExecuteNonQuery(); temp.ID = sTemperatureData.ID; } catch (Exception e) { string a = e.Message.ToString(); } Sqlconn.Close(); } returnValue.Add(temp); return returnValue; }
public string[] createStudentTemperatureData(CreateTemperatureSystem temperatureDataSystem) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DateTime now = DateTime.Now; //deleteStudentTemperatureData(temperatureDataSystem.txtpeopleID, now.Year.ToString(), now.Month.ToString(), now.Day.ToString()); 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 StudentTemperature (StudentID, StudentTemperature, ParentTemperature, Year,Month,Day ,LeaveItem,LeaveState," + "CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " + "(@StudentID, @StudentTemperature, @ParentTemperature, @Year,@Month,@Day ,@LeaveItem,@LeaveState, " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.txtpeopleID); cmd.Parameters.Add("@StudentTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.peopleTemp); cmd.Parameters.Add("@ParentTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.parentsTemp); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@LeaveItem", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.leaveItem); cmd.Parameters.Add("@LeaveState", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.leaveStatus); if (string.IsNullOrEmpty(temperatureDataSystem.Year)) { cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Year.ToString()); } else { cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Year); } if (string.IsNullOrEmpty(temperatureDataSystem.Month)) { cmd.Parameters.Add("@Month", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Month.ToString()); } else { cmd.Parameters.Add("@Month", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Month); } if (string.IsNullOrEmpty(temperatureDataSystem.Day)) { cmd.Parameters.Add("@Day", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(now.Day.ToString()); } else { cmd.Parameters.Add("@Day", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.Day); } returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<CreateBookSystem> createBookSystemDataBase(CreateBookSystem bookSystemData) { List<CreateBookSystem> returnValue = new List<CreateBookSystem>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { string[] checkBookData = getBookDataName(bookSystemData.bookCode); if (int.Parse(checkBookData[0]) > 0 && int.Parse(checkBookData[1]) == 1) { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "INSERT INTO BookManage (Unit, Status, ClassID, BorrowerIdentity, BorrowerID, BookID, BorrowDate, MaturityDate, " + "CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " + "(@Unit, @Status, @ClassID, @BorrowerIdentity, @BorrowerID, @BookID, (getDate()), DATEADD(day,7,getDate()), " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 1; //cmd.Parameters.Add("@ClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerClassID); cmd.Parameters.Add("@ClassID", SqlDbType.BigInt).Value = 0; cmd.Parameters.Add("@BorrowerIdentity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerStatus); cmd.Parameters.Add("@BorrowerID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerID); cmd.Parameters.Add("@BookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); string numberStr = cmd.ExecuteNonQuery().ToString(); if (numberStr != "0") { Int64 Column = 0; sql = "select IDENT_CURRENT('BookManage') AS bID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["bID"].ToString()); } dr.Close(); sql = "UPDATE BookDatabase SET Status=@Status WHERE BookID=@bookID AND isDeleted=0"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2; cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]); string numberStr2 = cmd.ExecuteNonQuery().ToString(); if (Column != 0 && numberStr2 != "0") { if (bookSystemData.borrowerStatus == "1") { sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName FROM BookManage " + "RIGHT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " + "RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " + "WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID"; } else if (bookSystemData.borrowerStatus == "2") { sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StudentDatabase.StudentName AS BorrowerName FROM BookManage " + "RIGHT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " + "RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " + "WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID"; } cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BID", SqlDbType.BigInt).Value = Column; dr = cmd.ExecuteReader(); while (dr.Read()) { CreateBookSystem addValue = new CreateBookSystem(); addValue.bID = Column.ToString(); addValue.borrowStatus = dr["Status"].ToString(); addValue.borrowerClassID = dr["ClassID"].ToString(); addValue.borrowerName = dr["BorrowerName"].ToString(); addValue.borrowerID = dr["BorrowerID"].ToString(); addValue.borrowerStatus = dr["BorrowerIdentity"].ToString(); addValue.bookCode = dr["BookCodeID"].ToString(); addValue.bookName = dr["BookName"].ToString(); addValue.borrowDate = DateTime.Parse(dr["BorrowDate"].ToString()).ToString("yyyy-MM-dd"); addValue.expireDate = DateTime.Parse(dr["MaturityDate"].ToString()).ToString("yyyy-MM-dd"); addValue.checkNo = "1"; returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } } Sqlconn.Close(); } else if (int.Parse(checkBookData[1]) == 2) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = "0"; addValue.errorMsg = "此本圖書已借出"; returnValue.Add(addValue); } else if (int.Parse(checkBookData[0]) == 0) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = checkBookData[0]; addValue.errorMsg = "查無此本圖書"; returnValue.Add(addValue); } else { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = checkBookData[0]; addValue.errorMsg = checkBookData[1]; returnValue.Add(addValue); } } catch (Exception e) { CreateBookSystem addValue = new CreateBookSystem(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public string[] createTeacherTemperatureData(CreateTemperatureSystem temperatureDataSystem) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; 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 TeacherTemperature (TeacherID, TeacherTemperature, " + "CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " + "(@TeacherID, @TeacherTemperature, " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@TeacherID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.txtpeopleID); cmd.Parameters.Add("@TeacherTemperature", SqlDbType.Decimal).Value = Chk.CheckStringtoDecimalFunction(temperatureDataSystem.peopleTemp); 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[] UpdateVoiceDistance(List<VoiceDistance> sTemperatureData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); string sql = " Update VoiceDistance set AcademicYear = @AcademicYear ,AcademicTerm = @AcademicTerm where id = @VDID "; for (int i = 0; i < sTemperatureData.Count; i++) { sql += " Update VoiceDistanceMaster set date = @" + i.ToString() + "date , remark =@" + i.ToString() + "remark ,up1 = @" + i.ToString() + "up1,up2=@" + i.ToString() + "up2,up3=@" + i.ToString() + "up3 , up4 =@" + i.ToString() + "up4 , up5 =@" + i.ToString() + "up5 where VDid = @VDID and ListOrder =@" + i.ToString() + "ListOrder "; sql += " Update VoiceDistanceDetail set Question = @" + i.ToString() + "Question , A1 = @" + i.ToString() + "A1,A2=@" + i.ToString() + "A2,A3=@" + i.ToString() + "A3,A4=@" + i.ToString() + "A4,A5 = @" + i.ToString() + "A5 where ID = @" + i.ToString() + "HidID"; } Sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[0].AcademicYear.ToString()); cmd.Parameters.Add("@AcademicTerm", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[0].AcademicTerm.ToString()); cmd.Parameters.Add("@VDID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData[0].ID.ToString()); for (int i = 0; i < sTemperatureData.Count; i++) { cmd.Parameters.Add("@" + i.ToString() + "date", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(sTemperatureData[i].Date.AddYears(1911).AddDays(-1).ToShortDateString()); cmd.Parameters.Add("@" + i.ToString() + "remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[i].remark.ToString()); cmd.Parameters.Add("@" + i.ToString() + "ListOrder", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(sTemperatureData[i].ListOrder.ToString()); cmd.Parameters.Add("@" + i.ToString() + "Question", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[i].Question.ToString()); cmd.Parameters.Add("@" + i.ToString() + "HidID", SqlDbType.Int).Value = Chk.CheckStringtoInt64Function(sTemperatureData[i].HidID.ToString()); for (int up = 0; up < sTemperatureData[i].up.Split('|').Length; up++) { cmd.Parameters.Add("@" + i.ToString() + "up" + (up + 1).ToString(), SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[i].up.Split('|').GetValue(up).ToString()); } for (int j = 0; j < sTemperatureData[i].Anser.Split('|').Length; j++) { cmd.Parameters.Add("@" + i.ToString() + "A" + (j + 1).ToString(), SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(sTemperatureData[i].Anser.Split('|').GetValue(j).ToString()); } } int dr = cmd.ExecuteNonQuery(); if (dr > 0) { returnValue[0] = sTemperatureData[0].ID.ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] CreatVoiceDistanceDatabase(List<VoiceDistance> sTemperatureData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); string sql = " DECLARE @ID int; DECLARE @MasterID int; insert into VoiceDistance (studentID,AcademicYear,AcademicTerm,studentAge,studentMonth) values( @studentID , @AcademicYear,@AcademicTerm,@StudentAge,@StudentMonth) "; sql += " select @ID = (select @@identity) "; sql += " insert into VoiceDistanceMaster ( VDid,date,remark ,ListOrder,up1,up2 ,up3 ,up4,up5 ) values( @ID,@date,@remark ,@ListOrder,@up1,@up2 ,@up3 ,@up4,@up5) "; sql += " select @MasterID = (select @@identity) "; for (int i = 0; i < sTemperatureData.Count; i++) { sql += "insert into VoiceDistanceDetail (VDMid,Question,A1,A2,A3,A4,A5,Rows)values(@MasterID,@Question" + i.ToString() + ",@A1" + i.ToString() + ",@A2" + i.ToString() + ",@A3" + i.ToString() + ",@A4" + i.ToString() + ",@A5" + i.ToString() + ","+(i+1).ToString()+")"; } sql += " select @ID as ID "; Sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData[0].StudentID.ToString()); cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[0].AcademicYear.ToString()); cmd.Parameters.Add("@AcademicTerm", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[0].AcademicTerm.ToString()); cmd.Parameters.Add("@StudentAge", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData[0].StudentAge.ToString()); cmd.Parameters.Add("@StudentMonth", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData[0].StudentMonth.ToString()); cmd.Parameters.Add("@date", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction( sTemperatureData[0].Date.AddYears(1911).AddDays(-1).ToShortDateString()); cmd.Parameters.Add("@remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[0].remark.ToString()); cmd.Parameters.Add("@ListOrder", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(sTemperatureData[0].ListOrder.ToString()); for (int i = 0; i < sTemperatureData[0].up.Split('|').Length; i++) { cmd.Parameters.Add("@up" + (i + 1).ToString(), SqlDbType.NVarChar).Value = Chk.CheckStringFunction( sTemperatureData[0].up.Split('|').GetValue(i).ToString()); } for (int i = 0; i < sTemperatureData.Count; i++) { cmd.Parameters.Add("@Question" + i.ToString(), SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData[i].Question.ToString()); for (int j = 0; j < sTemperatureData[0].Anser.Split('|').Length; j++) { cmd.Parameters.Add("@A" + (j + 1).ToString() + i.ToString(), SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(sTemperatureData[i].Anser.Split('|').GetValue(j).ToString()); } } SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { returnValue[0] = dr["ID"].ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
private string SearchBookConditionReturn(SearchBook bookData, string bookDBName) { string ConditionReturn = ""; if (bookData.txtbookNumber != null) { ConditionReturn += " AND BookCodeID=@txtbookNumber "; } if (bookData.txtbookTitle != null) { ConditionReturn += " AND BookName like @txtbookTitle "; } if (bookData.txtbookClassification != null) { ConditionReturn += " AND CategoryID=@txtbookClassification "; } if (bookData.txtbookAuthor != null) { ConditionReturn += " AND Author like @txtbookAuthor "; } if (bookData.txtbookPress != null) { ConditionReturn += " AND Press like @txtbookPress "; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND " + bookDBName + ".Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public string[] deleteHearLoss(int SID) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DateTime now = DateTime.Now; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "delete Hearing_Loss_AnsDetail where MasterID in ( select ID from Hearing_Loss_AnsMaster where StudentID = @StudentID)"; sql += " delete Hearing_Loss_AnsMaster where StudentID = @StudentID ";//SQL重新整理 提高流暢度 **有題目刪減遇上額外問題(待處理)** BY AWho SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID; cmd.ExecuteNonQuery(); //SqlDataReader dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // using (SqlConnection Sqlconn2 = new SqlConnection(Base.GetConnString())) // { // Sqlconn2.Open(); // string sql2 = "DELETE FROM Hearing_Loss_AnsDetail where (MasterID=@MasterID)"; // SqlCommand cmd2 = new SqlCommand(sql2, Sqlconn2); // cmd2.Parameters.Add("@MasterID", SqlDbType.Int).Value = int.Parse(dr["ID"].ToString()); // cmd2.ExecuteNonQuery(); // } //} //dr.Close(); //string sql3 = "DELETE FROM Hearing_Loss_AnsMaster where (StudentID=@StudentID)"; //SqlCommand cmd3 = new SqlCommand(sql3, Sqlconn); //cmd3.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID; //cmd3.ExecuteNonQuery(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
private string SearchBookStatisticsConditionReturn(SearchBookStatistics BookStatisticsData) { string ConditionReturn = ""; if (BookStatisticsData.txtbookStartDay != null && BookStatisticsData.txtbookEndDay != null) { ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN CONVERT(varchar, GETDATE() - @txtbookEndDay, 23) AND CONVERT(varchar, GETDATE() - @txtbookStartDay, 23) "; } if (BookStatisticsData.txtbookDateStartDate != null && BookStatisticsData.txtbookDateEndDate != null) { ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtbookDateStartDate AND @txtbookDateEndDate "; } if (BookStatisticsData.txtrecordBookID != null) { ConditionReturn += " AND BookManage.BookID=@txtrecordBookID "; } if (BookStatisticsData.txtrecordBookStartDate != null && BookStatisticsData.txtrecordBookEndDate != null) { ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtrecordBookStartDate AND @txtrecordBookEndDate "; } if (BookStatisticsData.txtrecordBorrowerType != null) { ConditionReturn += " AND BorrowerIdentity=@txtrecordBorrowerType "; } if (BookStatisticsData.txtrecordBorrowerType == "1" && BookStatisticsData.txtrecordBorrowerName != null) { ConditionReturn += " AND StaffDatabase.StaffName like @txtrecordBorrowerName "; } else if (BookStatisticsData.txtrecordBorrowerType == "2" && BookStatisticsData.txtrecordBorrowerName != null) { ConditionReturn += " AND StudentDatabase.StudentName like @txtrecordBorrowerName "; } if (BookStatisticsData.txtrecordBorrowerStartDate != null && BookStatisticsData.txtrecordBorrowerEndDate != null) { ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtrecordBorrowerStartDate AND @txtrecordBorrowerEndDate "; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND BookManage.Unit =" + UserFile[2] + " "; } return ConditionReturn; }