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[] 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[] 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[] 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[] 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 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[] 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;
}
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 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 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 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;
}
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;
}
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<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<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 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;
}
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;
}
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;
}
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;
}
public string[] SearchBookRecordCount(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 BookManage WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
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);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
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<CreateBookResult> searchBookResult(int indexpage, SearchBook bookData)
{
List<CreateBookResult> returnValue = new List<CreateBookResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchBookConditionReturn(bookData, "BookDatabase");
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 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY BookDatabase.BookID DESC) " +
"AS RowNum, BookDatabase.*, BookCategory.CategoryCode, BookCategory.CategoryName FROM BookDatabase " +
"RIGHT JOIN BookCategory ON BookDatabase.CategoryID=BookCategory.ID " +
"WHERE BookDatabase.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("@txtbookNumber", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.txtbookNumber);
cmd.Parameters.Add("@txtbookTitle", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookTitle) + "%";
cmd.Parameters.Add("@txtbookClassification", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookData.txtbookClassification);
cmd.Parameters.Add("@txtbookAuthor", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookAuthor) + "%";
cmd.Parameters.Add("@txtbookPress", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookPress) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateBookResult addValue = new CreateBookResult();
addValue.bID = dr["BookID"].ToString();
addValue.bookNumber = dr["BookCodeID"].ToString();
addValue.bookTitle = dr["BookName"].ToString();
addValue.bookClassificationCode = dr["CategoryCode"].ToString();
addValue.bookClassificationName = dr["CategoryName"].ToString();
addValue.bookAuthor = dr["Author"].ToString();
addValue.bookPress = dr["Press"].ToString();
addValue.checkNo = "1";
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateBookResult addValue = new CreateBookResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
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 string[] searchBookResultCount(SearchBook bookData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchBookConditionReturn(bookData, "BookDatabase");
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 BookDatabase WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtbookNumber", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(bookData.txtbookNumber);
cmd.Parameters.Add("@txtbookTitle", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookTitle) + "%";
cmd.Parameters.Add("@txtbookClassification", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookData.txtbookClassification);
cmd.Parameters.Add("@txtbookAuthor", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookAuthor) + "%";
cmd.Parameters.Add("@txtbookPress", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(bookData.txtbookPress) + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
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[] searchUserData(SearchUser userData)
{
string[] returnValue = new string[4];
returnValue[0] = "0";
returnValue[1] = "";
returnValue[2] = "0"; //
returnValue[3] = "";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
CaseDataBase cDB = new CaseDataBase();
List<string> CreateFileName = sDB.getStaffDataName(userData.txtpeopleID);
if (CreateFileName[1].Length == 0)
{
CreateFileName = cDB.getStudentDataName(userData.txtpeopleID);
if (CreateFileName[1].Length != 0)
{
CreateFileName[2] = "2";
}
else
{
CreateFileName[0] = "0";
}
}
else
{
CreateFileName[2] = "1";
}
returnValue[0] = CreateFileName[0];
returnValue[1] = CreateFileName[1];
returnValue[2] = CreateFileName[2];
returnValue[3] = CreateFileName[3];
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
returnValue[2] = "0";
}
}
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[] 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;
}