C# StaffDataBase.getStaffDataName方法代码示例

    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[] 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[] 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 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";
//.........这里部分代码省略.........

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

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

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

 private string SearchBookConditionReturn(SearchBook bookData, string bookDBName)
 {
     string ConditionReturn = "";
     if (bookData.txtbookNumber != null)
     {
         ConditionReturn += " AND [email protected] ";
     }
     if (bookData.txtbookTitle != null)
     {
         ConditionReturn += " AND BookName like @txtbookTitle ";
     }
     if (bookData.txtbookClassification != null)
     {
         ConditionReturn += " AND [email protected] ";
     }
     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[] SearchBookRecordBorrowerCount(SearchBookStatistics BookStatisticsData)
 {
     string[] returnValue = new string[2];
     returnValue[0] = "0";
     returnValue[1] = "0";
     DataBase Base = new DataBase();
     string ConditionReturn = this.SearchBookStatisticsConditionReturn(BookStatisticsData);
     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 = "SELECT COUNT(*) FROM ( " +
                         "SELECT COUNT(BookManage.BorrowerID) AS QCOUNT,BorrowerID FROM BookManage " +
                         "LEFT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
                         "LEFT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
                         "WHERE BookManage.isDeleted=0 " + ConditionReturn +
                         "GROUP BY BorrowerID,BorrowerIdentity " +
                         "HAVING COUNT(BorrowerID)>=1 " +
                         ") AS NewTable";
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@txtrecordBorrowerType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerType);
             cmd.Parameters.Add("@txtrecordBorrowerStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerStartDate);
             cmd.Parameters.Add("@txtrecordBorrowerEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerEndDate);
             cmd.Parameters.Add("@txtrecordBorrowerClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerClassID);
             cmd.Parameters.Add("@txtrecordBorrowerName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(BookStatisticsData.txtrecordBorrowerName) + "%";
             returnValue[0] = cmd.ExecuteScalar().ToString();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             returnValue[0] = "-1";
             returnValue[1] = e.Message.ToString();
         }
     }
     return returnValue;
 }

 public List<CreateBookRecordBorrower> SearchBookRecordBorrower(int indexpage, SearchBookStatistics BookStatisticsData)
 {
     List<CreateBookRecordBorrower> returnValue = new List<CreateBookRecordBorrower>();
     DataBase Base = new DataBase();
     string ConditionReturn = this.SearchBookStatisticsConditionReturn(BookStatisticsData);
     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 = "";
             sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY BookManage.BorrowerID DESC) " +
                 "AS RowNum, COUNT(BookManage.BorrowerID) AS QCOUNT,BookManage.BorrowerID,BookManage.BorrowerIdentity," +
                 "StaffDatabase.StaffName AS BorrowerName1,StudentDatabase.StudentName AS BorrowerName2,BookManage.ClassID FROM BookManage " +
                 "LEFT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
                 "LEFT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
                 "WHERE BookManage.isDeleted=0 " + ConditionReturn +
                 "GROUP BY BookManage.BorrowerID,BookManage.BorrowerIdentity,StaffDatabase.StaffName,StudentDatabase.StudentName,BookManage.ClassID " +
                 "HAVING COUNT(BookManage.BorrowerID)>=1 ) " +
                 "AS NewTable " +
                 "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
             cmd.Parameters.Add("@txtrecordBorrowerType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerType);
             cmd.Parameters.Add("@txtrecordBorrowerStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerStartDate);
             cmd.Parameters.Add("@txtrecordBorrowerEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerEndDate);
             cmd.Parameters.Add("@txtrecordBorrowerClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerClassID);
             cmd.Parameters.Add("@txtrecordBorrowerName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(BookStatisticsData.txtrecordBorrowerName) + "%";
             SqlDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 CreateBookRecordBorrower addValue = new CreateBookRecordBorrower();
                 addValue.borrowerClassID = dr["ClassID"].ToString();
                 if (dr["BorrowerName1"].ToString().Length == 0)
                 {
                     addValue.borrowerName = dr["BorrowerName2"].ToString();
                 }
                 else
                 {
                     addValue.borrowerName = dr["BorrowerName1"].ToString();
                 }
                 addValue.borrowerID = dr["BorrowerID"].ToString();
                 addValue.borrowerStatus = dr["BorrowerIdentity"].ToString();
                 addValue.borrowQuantity = dr["QCOUNT"].ToString();
                 addValue.checkNo = "1";
                 returnValue.Add(addValue);
             }
             dr.Close();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             CreateBookRecordBorrower addValue = new CreateBookRecordBorrower();
             addValue.checkNo = "-1";
             addValue.errorMsg = e.Message.ToString();
             returnValue.Add(addValue);
         }
     }
     return returnValue;
 }

 public List<CreateBookSystem> SearchBookRecord(int indexpage, SearchBookStatistics BookStatisticsData)
 {
     List<CreateBookSystem> returnValue = new List<CreateBookSystem>();
     DataBase Base = new DataBase();
     string ConditionReturn = this.SearchBookStatisticsConditionReturn(BookStatisticsData);
     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 = "";
             sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY BookManage.BookID DESC) " +
                  "AS RowNum, BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName1, StudentDatabase.StudentName AS BorrowerName2 FROM BookManage " +
                  "LEFT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
                  "LEFT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
                  "RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID " +
                  "WHERE BookManage.isDeleted=0 " + ConditionReturn + " ) " +
                  "AS NewTable " +
                  "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
             SqlCommand cmd = new SqlCommand(sql, Sqlconn);
             cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
             cmd.Parameters.Add("@txtrecordBookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBookID);
             cmd.Parameters.Add("@txtrecordBookStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBookStartDate);
             cmd.Parameters.Add("@txtrecordBookEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBookEndDate);
             SqlDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 CreateBookSystem addValue = new CreateBookSystem();
                 addValue.borrowerClassID = dr["ClassID"].ToString();
                 if (dr["BorrowerName1"].ToString() == null || (dr["BorrowerName1"].ToString()).Length == 0)
                 {
                     addValue.borrowerName = dr["BorrowerName2"].ToString();
                 }
                 else
                 {
                     addValue.borrowerName = dr["BorrowerName1"].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.restoreDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd");
                 addValue.checkNo = "1";
                 returnValue.Add(addValue);
             }
             dr.Close();
             Sqlconn.Close();
         }
         catch (Exception e)
         {
             CreateBookSystem addValue = new CreateBookSystem();
             addValue.checkNo = "-1";
             addValue.errorMsg = e.Message.ToString();
             returnValue.Add(addValue);
         }
     }
     return returnValue;
 }

    public List<CreateBookBorrow> SearchBookDate(SearchBookStatistics BookStatisticsData)
    {
        List<CreateBookBorrow> returnValue = new List<CreateBookBorrow>();
        DataBase Base = new DataBase();
        string ConditionReturn = this.SearchBookStatisticsConditionReturn(BookStatisticsData);
        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 = "SELECT COUNT(*) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=1 " + ConditionReturn;
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
                cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
                CreateBookBorrow addValue = new CreateBookBorrow();
                addValue.staffBorrowBookSum = cmd.ExecuteScalar().ToString();

                sql = "SELECT COUNT(DISTINCT BorrowerID) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=1 " + ConditionReturn;
                cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
                cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
                addValue.staffBorrowerSum = cmd.ExecuteScalar().ToString();

                sql = "SELECT COUNT(*) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=2 " + ConditionReturn;
                cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
                cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
                addValue.studentBorrowBookSum = cmd.ExecuteScalar().ToString();

                sql = "SELECT COUNT(DISTINCT BorrowerID) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=2 " + ConditionReturn;
                cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
                cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
                addValue.studentBorrowerSum = cmd.ExecuteScalar().ToString();

                addValue.bookBorrowStartDate = DateTime.Parse(BookStatisticsData.txtbookDateStartDate).ToString("yyyy-MM-dd");
                addValue.bookBorrowEndDate = DateTime.Parse(BookStatisticsData.txtbookDateEndDate).ToString("yyyy-MM-dd");
                addValue.checkNo = "1";
                returnValue.Add(addValue);
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                CreateBookBorrow addValue = new CreateBookBorrow();
                addValue.checkNo = "-1";
                addValue.errorMsg = e.Message.ToString();
                returnValue.Add(addValue);
            }
        }
        return returnValue;
    }

    public string[] GetBookIDData(CreateBook bookData)
    {
        string[]  returnValue = new string[2];
          returnValue[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 = "";
                  //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 ";
                  SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                  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 dr = cmd.ExecuteReader();
                  string stuNumber = "1";
                  if (dr.Read())
                  {
                      stuNumber = (Chk.CheckStringtoIntFunction(dr["QCOUNT"].ToString()) + 1).ToString();
                  }
                  returnValue[0] = CreateFileName[2] + Chk.CheckStringFunction(bookData.bookClassificationCode) + stuNumber.PadLeft(4, '0');
                  //returnValue = stuIDName;
              }
              catch
              {

              }
              finally {
                  Sqlconn.Close();
              }
          }

          return returnValue;
    }

    public int delTeacherSchudule(Int32 StructData)
    {
        int returnValue = 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 = " delete  TeacherSchudule ";
                sql += " where ID = @TeacherScheduleID ";

                sql += " delete TeacherSchuduleStudent where TeacherScheduleID = @TeacherScheduleID ";
               // sql += " select @TeacherScheduleID as TeacherScheduleID ";
                SqlCommand cmd = new SqlCommand(sql, Sqlconn);
                cmd.Parameters.Add("@TeacherScheduleID", SqlDbType.Int).Value = StructData;
               // SqlDataReader dr = cmd.ExecuteReader();
                returnValue = cmd.ExecuteNonQuery();
                Sqlconn.Close();
            }
            catch (Exception e)
            {
                string item = e.Message.ToString();
                returnValue = -1;
            }
        }

        return returnValue;
    }