public string[] createStaffCreditDataBase(CreateStaffUpgrade StaffUpgradeData) { StaffDataBase sDB = new StaffDataBase(); sDB.personnelFunction(); //return new string[2] { "", "" }; if (int.Parse(sDB._StaffhaveRoles[2]) == 1) { return sDB.createStaffCreditData(StaffUpgradeData); } else { return new string[2] { _noRole, _errorMsg }; } }
public string[] createStaffCreditData(CreateStaffUpgrade StaffUpgrade) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "INSERT INTO StaffUpgradeCredit (CreditDate, CreditTeacher, Topics, Prove, CreditNumber, Remark, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ,CreditTeacherType ,InTeacher ,InAudiologist ,InSocialWorkers ,InAdministrative ,IsSatisfaction ,DataQuestion ,DataLecturer ,DataLecture ,DataPhoto ,DataTeaching ,DataIsp ,DataOther,DataOtherText,StartH,EndH ) " + "VALUES (@CreditDate, @CreditTeacher, @Topics, @Prove, @CreditNumber, @Remark, @CreateFileBy, getDate(), @UpFileBy, getDate() ,@CreditTeacherType ,@InTeacher ,@InAudiologist ,@InSocialWorkers ,@InAdministrative ,@IsSatisfaction ,@DataQuestion ,@DataLecturer ,@DataLecture ,@DataPhoto ,@DataTeaching ,@DataIsp ,@DataOther,@DataOtherText,@StartH,@EndH)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@CreditDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StaffUpgrade.courseDate); cmd.Parameters.Add("@CreditTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseLecturer); cmd.Parameters.Add("@Topics", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseName); // cmd.Parameters.Add("@Hours", SqlDbType.Float).Value = Chk.CheckFloatFunction(StaffUpgrade.courseTime); cmd.Parameters.Add("@Prove", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseProve); cmd.Parameters.Add("@CreditNumber", SqlDbType.Float).Value = Chk.CheckStringFunction(StaffUpgrade.courseCredit); cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.otherExplanation); 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("@CreditTeacherType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseCreditTeacherType); cmd.Parameters.Add("@InTeacher", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInTeacher); cmd.Parameters.Add("@InAudiologist", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAudiologist); cmd.Parameters.Add("@InSocialWorkers", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInSocialWorkers); cmd.Parameters.Add("@InAdministrative", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAdministrative); cmd.Parameters.Add("@IsSatisfaction", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseIsSatisfaction); cmd.Parameters.Add("@DataQuestion", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataQuestion); cmd.Parameters.Add("@DataLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataLecturer); cmd.Parameters.Add("@DataLecture", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataLecture); cmd.Parameters.Add("@DataPhoto", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataPhoto); cmd.Parameters.Add("@DataTeaching", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataTeaching); cmd.Parameters.Add("@DataIsp", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataIsp); cmd.Parameters.Add("@DataOther", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataOther); cmd.Parameters.Add("@DataOtherText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataOtherText); cmd.Parameters.Add("@StartH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseStartH); cmd.Parameters.Add("@EndH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseEndH); returnValue[0] = cmd.ExecuteNonQuery().ToString(); if (returnValue[0] != "0") { Int64 Column = 0; sql = "select IDENT_CURRENT('StaffUpgradeCredit') AS cID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["cID"].ToString()); } dr.Close(); List<int> itemValue = new List<int>(); for (int i = 0; i < StaffUpgrade.Participants.Count; i++) { List<string> StaffData = this.getStaffDataName(StaffUpgrade.Participants[i].sID); if (StaffData[0] != "-1") { sql = "INSERT INTO StaffUpgradeCreditStaff (Unit, CreditID, StaffID, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " + "VALUES (@Unit, @CreditID, @StaffID, @CreateFileBy, getDate(), @UpFileBy, getDate())"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData[2]); cmd.Parameters.Add("@CreditID", SqlDbType.BigInt).Value = Column; cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffData[0]); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); itemValue.Add(cmd.ExecuteNonQuery()); } } returnValue[1] = Convert.ToInt32(!itemValue.Contains(0)).ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1]= e.Message.ToString(); } } return returnValue; }
public string[] setStaffCreditDataBase(CreateStaffUpgrade StaffUpgrade) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "UPDATE StaffUpgradeCredit SET CreditDate=@CreditDate, CreditTeacher=@CreditTeacher, Topics=@Topics, Hours=@Hours, Prove=@Prove, "+ "CreditNumber=@CreditNumber, Remark=@Remark, UpFileBy=@UpFileBy, UpFileDate=(getDate()),CreditTeacherType=@CreditTeacherType ,InTeacher=@InTeacher ,InAudiologist=@InAudiologist ,InSocialWorkers=@InSocialWorkers ,InAdministrative=@InAdministrative ,IsSatisfaction=@IsSatisfaction ,DataQuestion=@DataQuestion ,DataLecturer=@DataLecturer ,DataLecture=@DataLecture ,DataPhoto=@DataPhoto ,DataTeaching=@DataTeaching ,DataIsp=@DataIsp ,DataOther=@DataOther " + " ,DataOtherText=@DataOtherText,StartH=@StartH,EndH=@EndH " + "WHERE CreditID=@CreditID AND isDeleted=0 "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@CreditID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.ID); cmd.Parameters.Add("@CreditDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StaffUpgrade.courseDate); cmd.Parameters.Add("@CreditTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseLecturer); cmd.Parameters.Add("@Topics", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseName); cmd.Parameters.Add("@Hours", SqlDbType.Float).Value = Chk.CheckFloatFunction(StaffUpgrade.courseTime); cmd.Parameters.Add("@Prove", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseProve); cmd.Parameters.Add("@CreditNumber", SqlDbType.Float).Value = Chk.CheckStringFunction(StaffUpgrade.courseCredit); cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.otherExplanation); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@CreditTeacherType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseCreditTeacherType); cmd.Parameters.Add("@InTeacher", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInTeacher); cmd.Parameters.Add("@InAudiologist", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAudiologist); cmd.Parameters.Add("@InSocialWorkers", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInSocialWorkers); cmd.Parameters.Add("@InAdministrative", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAdministrative); cmd.Parameters.Add("@IsSatisfaction", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseIsSatisfaction); cmd.Parameters.Add("@DataQuestion", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataQuestion); cmd.Parameters.Add("@DataLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataLecturer); cmd.Parameters.Add("@DataLecture", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataLecture); cmd.Parameters.Add("@DataPhoto", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataPhoto); cmd.Parameters.Add("@DataTeaching", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataTeaching); cmd.Parameters.Add("@DataIsp", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataIsp); cmd.Parameters.Add("@DataOther", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataOther); cmd.Parameters.Add("@DataOtherText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataOtherText); cmd.Parameters.Add("@StartH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseStartH); cmd.Parameters.Add("@EndH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseEndH); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<CreateStaffUpgrade> SearchStaffCreditDataBase(int indexpage, SearchStaffCredit SearchStaffUpgrade) { List<CreateStaffUpgrade> returnValue = new List<CreateStaffUpgrade>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { string[] ConditionReturn = this.SearchStaffCreditConditionReturn(SearchStaffUpgrade); Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC) " + "AS RowNum,MuliTable.* FROM "+ "( SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b " + "ON a.CreditID=b.CreditID AND b.isDeleted=0 " + "WHERE a.isDeleted=0 " + ConditionReturn[0] + ConditionReturn[1] +" ) "+ "AS MuliTable )"+ "AS NewTable " + "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)"; /*SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC) AS RowNum,MuliTable.* FROM ( SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b ON a.CreditID=b.CreditID AND b.isDeleted=0 WHERE a.isDeleted=0 AND b.StaffID IN (SELECT c.StaffID FROM StaffDatabase c WHERE c.StaffID=b.StaffID AND c.StaffName like '曾%')) AS MuliTable ) AS NewTable*/ SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName) + "%"; cmd.Parameters.Add("@sCreditDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart); cmd.Parameters.Add("@sCreditDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd); cmd.Parameters.Add("@txtLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtLecturer) + "%"; cmd.Parameters.Add("@txtProve", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtProve); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { CreateStaffUpgrade addValue = new CreateStaffUpgrade(); addValue.ID = dr["CreditID"].ToString(); addValue.courseDate = DateTime.Parse(dr["CreditDate"].ToString()).ToString("yyyy-MM-dd"); addValue.courseLecturer = dr["CreditTeacher"].ToString(); addValue.courseName = dr["Topics"].ToString(); addValue.courseTime = dr["Hours"].ToString(); addValue.courseProve = dr["Prove"].ToString(); addValue.courseCredit = dr["CreditNumber"].ToString(); addValue.otherExplanation = dr["Remark"].ToString(); addValue.Participants = new List<StaffDataList>(); addValue.Participants = this.getCreditParticipants(addValue.ID); addValue.courseCreditTeacherType = dr["CreditTeacherType"].ToString(); addValue.courseInTeacher = dr["InTeacher"].ToString() == "False" ? "" : "Checked"; addValue.courseInAudiologist = dr["InAudiologist"].ToString() == "False" ? "" : "Checked"; addValue.courseInSocialWorkers = dr["InSocialWorkers"].ToString() == "False" ? "" : "Checked"; addValue.courseInAdministrative = dr["InAdministrative"].ToString() == "False" ? "" : "Checked"; addValue.courseIsSatisfaction = dr["IsSatisfaction"].ToString() == "False" ? "0" : "1"; addValue.courseDataQuestion = dr["DataQuestion"].ToString(); addValue.courseDataLecturer = dr["DataLecturer"].ToString(); addValue.courseDataLecture = dr["DataLecture"].ToString() == "False" ? "" : "Checked"; addValue.courseDataPhoto = dr["DataPhoto"].ToString() == "False" ? "" : "Checked"; addValue.courseDataTeaching = dr["DataTeaching"].ToString() == "False" ? "" : "Checked"; addValue.courseDataIsp = dr["DataIsp"].ToString() == "False" ? "" : "Checked"; addValue.courseDataOther = dr["DataOther"].ToString() == "False" ? "" : "Checked"; addValue.courseStartH = dr["StartH"].ToString(); addValue.courseEndH = dr["EndH"].ToString(); addValue.courseDataOtherText = dr["DataOtherText"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { CreateStaffUpgrade addValue = new CreateStaffUpgrade(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }