public List<CreateVolunteer> searchVolunteerData(int indexpage, SearchVolunteer SearchStructure)
{
List<CreateVolunteer> returnValue = new List<CreateVolunteer>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchVolunteerConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY VOLID DESC) " +
"AS RowNum, * FROM VolunteerData WHERE 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("@vID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvID);
cmd.Parameters.Add("@vName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtvName) + "%";
cmd.Parameters.Add("@vSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvSex);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateVolunteer addValue = new CreateVolunteer();
addValue.ID = dr["ID"].ToString();
addValue.volunteerId = dr["VOLID"].ToString();
addValue.volunteerName = dr["VOLName"].ToString();
addValue.volunteerPhone = dr["Phone"].ToString();
addValue.vEmail = dr["Email"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateVolunteer addValue = new CreateVolunteer();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] setVolunteerDataBase(CreateVolunteer SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
Sqlconn.Open();
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
string sql = "UPDATE VolunteerData SET VOLName=@VOLName, Sex=@Sex, Birthday=@Birthday, CurrentWorkOrSchoolName=@CurrentWorkOrSchoolName, "+
"Tel_Daytime=@Tel_Daytime, Tel_Night=@Tel_Night, Phone=@Phone, Fax=@Fax, AddressZip=@AddressZip, AddressCity=@AddressCity, AddressOther=@AddressOther, "+
"Email=@Email, PersonalExpertise=@PersonalExpertise, ServiceExperience=@ServiceExperience, VOLExpect=@VOLExpect, ServiceTime=@ServiceTime, "+
"ServiceItem=@ServiceItem, ServiceOther=@ServiceOther, IntroductionSource=@IntroductionSource " +
" WHERE ID = @ID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.ID);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.fillInDate);
cmd.Parameters.Add("@VOLID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.volunteerId);
cmd.Parameters.Add("@VOLName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerName);
cmd.Parameters.Add("@Sex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.vSex);
cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.vBirthday);
cmd.Parameters.Add("@CurrentWorkOrSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.nowJob);
cmd.Parameters.Add("@Tel_Daytime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.telDaytime);
cmd.Parameters.Add("@Tel_Night", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.telNight);
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerPhone);
cmd.Parameters.Add("@Fax", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerFax);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.address);
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.vEmail);
cmd.Parameters.Add("@PersonalExpertise", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.Expertise);
cmd.Parameters.Add("@ServiceExperience", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.Experience);
cmd.Parameters.Add("@VOLExpect", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.VOLExpect);
cmd.Parameters.Add("@ServiceTime", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.servicedate);
cmd.Parameters.Add("@ServiceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.servicecontent);
cmd.Parameters.Add("@ServiceOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.otherService);
cmd.Parameters.Add("@IntroductionSource", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.vSource);
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 CreateVolunteer getVolunteerData(string vID)
{
CreateVolunteer returnValue = new CreateVolunteer();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM VolunteerData WHERE isDeleted=0 AND ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(vID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.sUnit = dr["Unit"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.volunteerId =dr["VOLID"].ToString();
returnValue.volunteerName = dr["VOLName"].ToString();
returnValue.vEmail = dr["Email"].ToString();
returnValue.volunteerPhone = dr["Phone"].ToString();
returnValue.vSex = dr["sex"].ToString();
returnValue.servicecontent = dr["Servicetime"].ToString();
returnValue.nowJob = dr["CurrentWorkOrSchoolName"].ToString();
returnValue.telDaytime = dr["Tel_Daytime"].ToString();
returnValue.telNight = dr["Tel_Night"].ToString();
returnValue.volunteerFax = dr["Fax"].ToString();
returnValue.addressZip = dr["AddressZip"].ToString();
returnValue.addressCity = dr["AddressCity"].ToString();
returnValue.address = dr["AddressOther"].ToString();
returnValue.Expertise = dr["PersonalExpertise"].ToString();
returnValue.Experience = dr["ServiceExperience"].ToString();
returnValue.VOLExpect = dr["VOLExpect"].ToString();
returnValue.servicedate = dr["ServiceTime"].ToString();
returnValue.servicecontent = dr["ServiceItem"].ToString();
returnValue.otherService = dr["ServiceOther"].ToString();
returnValue.vSource = dr["IntroductionSource"].ToString();
returnValue.vBirthday = DateTime.Parse(dr["Birthday"].ToString()).ToString("yyyy-MM-dd");
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
public string[] creatVolunteerDataBase(CreateVolunteer SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
Sqlconn.Open();
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
string sql = "INSERT INTO VolunteerData(Unit, WriteDate, VOLID, VOLName, Sex, Birthday, CurrentWorkOrSchoolName, Tel_Daytime, Tel_Night, Phone, Fax, "+
"AddressZip, AddressCity, AddressOther, Email, PersonalExpertise, ServiceExperience, VOLExpect, ServiceTime, ServiceItem, ServiceOther, IntroductionSource, "+
"CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@Unit, @WriteDate, @VOLID, @VOLName, @Sex, @Birthday, @CurrentWorkOrSchoolName, @Tel_Daytime, @Tel_Night, @Phone, @Fax, @AddressZip, "+
"@AddressCity, @AddressOther, @Email, @PersonalExpertise, @ServiceExperience, @VOLExpect, @ServiceTime, @ServiceItem, @ServiceOther, @IntroductionSource, "+
"@CreateFileBy, @UpFileBy, (getDate()) )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.fillInDate);
cmd.Parameters.Add("@VOLID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.volunteerId);
cmd.Parameters.Add("@VOLName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerName);
cmd.Parameters.Add("@Sex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.vSex);
cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.vBirthday);
cmd.Parameters.Add("@CurrentWorkOrSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.nowJob);
cmd.Parameters.Add("@Tel_Daytime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.telDaytime);
cmd.Parameters.Add("@Tel_Night", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.telNight);
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerPhone);
cmd.Parameters.Add("@Fax", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.volunteerFax);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.address);
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.vEmail);
cmd.Parameters.Add("@PersonalExpertise", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.Expertise);
cmd.Parameters.Add("@ServiceExperience", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.Experience);
cmd.Parameters.Add("@VOLExpect", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.VOLExpect);
cmd.Parameters.Add("@ServiceTime", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.servicedate);
cmd.Parameters.Add("@ServiceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.servicecontent);
cmd.Parameters.Add("@ServiceOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.otherService);
cmd.Parameters.Add("@IntroductionSource", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.vSource);
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")
{
string FieldName = "Volunteer_" + CreateFileName[2];
Int64 Column = 0;
sql = "UPDATE AutomaticNumberTable SET " + FieldName + "=" + FieldName + "+1 WHERE ID=1 ";
cmd = new SqlCommand(sql, Sqlconn);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
sql = "select id from VolunteerData where VOLID = @VOLID ";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@VOLID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.volunteerId);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()){
returnValue[1] = dr["id"].ToString(); //Column.ToString();
}
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setVolunteerDataBase(CreateVolunteer VolunteerData)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setVolunteerDataBase(VolunteerData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public CreateVolunteer getVolunteerServiceDataBase(int index,string vID)
{
CaseDataBase SDB = new CaseDataBase();
CreateVolunteer returnValue = new CreateVolunteer();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
returnValue=SDB.getVolunteerServiceData(index, vID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
public CreateVolunteer getVolunteerDataBase(string vID)
{
CaseDataBase SDB = new CaseDataBase();
CreateVolunteer returnValue = new CreateVolunteer();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
returnValue=SDB.getVolunteerData(vID);
}
else
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.sUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}