public string[] createLendingData(createAidsManageLoan StructData)
{
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 HearingAidsManageLoanRecord (ManageID, OutDate, OutPeople, ScheduledReturnDate, ReturnDate, Remark, CreateFileBy, UpFileBy, UpFileDate) " +
"VALUES(@ManageID, @OutDate, @OutPeople, @ScheduledReturnDate, @ReturnDate, @Remark,@CreateFileBy, @UpFileBy, (getDate()))";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ManageID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StructData.mID);
cmd.Parameters.Add("@OutDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StructData.lendDate);
cmd.Parameters.Add("@OutPeople", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StructData.lendPeopleID);
cmd.Parameters.Add("@ScheduledReturnDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StructData.returnDate);
cmd.Parameters.Add("@ReturnDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StructData.returnDate2);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StructData.remark);
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 createAidsManage getAidsManageData(string sID)
{
createAidsManage returnValue = new createAidsManage();
returnValue.LoanList = new List<createAidsManageLoan>();
returnValue.ServiceList = new List<createAidsManageService>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "SELECT * FROM HearingAidsManage WHERE ID=@ID AND isDeleted=0";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.Unit = dr["Unit"].ToString();
returnValue.aStatu = dr["AidsStatu"].ToString();
returnValue.aID = dr["HearingAidsID"].ToString();
returnValue.assistmanage = dr["AidsType"].ToString();
returnValue.brand = dr["AidsBrand"].ToString();
returnValue.model = dr["AidsModel"].ToString();
returnValue.aNo = dr["HearingAidsNo"].ToString();
returnValue.aSource = dr["AidsSource"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.remark = dr["Remark"].ToString();
}
dr.Close();
sql = "SELECT HearingAidsManageLoanRecord.*,StudentDatabase.StudentName FROM HearingAidsManageLoanRecord " +
"LEFT JOIN StudentDatabase ON HearingAidsManageLoanRecord.OutPeople=StudentDatabase.StudentID "+
"WHERE ManageID=@mID AND HearingAidsManageLoanRecord.isDeleted=0 ORDER BY OutDate DESC";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@mID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnValue.ID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
createAidsManageLoan addValue = new createAidsManageLoan();
addValue.ID= dr["ID"].ToString();
addValue.mID = dr["ManageID"].ToString();
addValue.lendDate = DateTime.Parse(dr["OutDate"].ToString()).ToString("yyyy-MM-dd");
addValue.lendPeople = dr["StudentName"].ToString();
addValue.lendPeopleID = dr["OutPeople"].ToString();
addValue.returnDate = DateTime.Parse(dr["ScheduledReturnDate"].ToString()).ToString("yyyy-MM-dd");
addValue.returnDate2 = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd");
addValue.remark = dr["Remark"].ToString();
returnValue.LoanList.Add(addValue);
}
dr.Close();
sql = "SELECT * FROM HearingAidsManageMaintenanceRecord WHERE ManageID=@mID AND isDeleted=0 ORDER BY MaintenanceDate DESC";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@mID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnValue.ID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
createAidsManageService addValue2 = new createAidsManageService();
addValue2.ID = dr["ID"].ToString();
addValue2.mID = dr["ManageID"].ToString();
addValue2.serviceDate = DateTime.Parse(dr["MaintenanceDate"].ToString()).ToString("yyyy-MM-dd");
addValue2.serviceItem = dr["Reason"].ToString();
addValue2.serviceFirm = dr["MaintenanceVendor"].ToString();
addValue2.serviceFirmDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd");
addValue2.sRemark = dr["Remark"].ToString();
returnValue.ServiceList.Add(addValue2);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message.ToString();
}
}
return returnValue;
}
public string[] setLendingData(createAidsManageLoan StructData)
{
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 HearingAidsManageLoanRecord SET ReturnDate=@ReturnDate, Remark=@Remark, UpFileBy=@UpFileBy, UpFileDate=getDate() " +
"WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StructData.ID);
cmd.Parameters.Add("@ReturnDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StructData.returnDate2);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StructData.remark);
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[] setLendingDataBase(createAidsManageLoan StructData)
{
Audiometry aDB = new Audiometry();
if (int.Parse(aDB._StaffhaveRoles[1]) == 1)
{
return aDB.setLendingData(StructData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}