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;
    }
Esempio n. 5
0
 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 };
     }
 }
Esempio n. 6
0
 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;
 }
Esempio n. 7
0
 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;
 }