public List <Volunteer_Model> SelectVolunteer_byName(string name) { VolunteerEntities dbContext = new VolunteerEntities(); var q = from n1 in dbContext.Volunteer join n2 in dbContext.Identity_type on n1.Identity_type equals n2.Identity_type1 where n1.Chinese_name == name select new { Volunteer_no = n1.Volunteer_no, Chinese_name = n1.Chinese_name, Identity_type_name = n2.Identity_type_name, Photo = n1.Photo, Phone_no = n1.Phone_no, Mobile_no = n1.Mobile_no }; List <Volunteer_Model> Volunteer_Models = new List <Volunteer_Model>(); foreach (var row in q) { Volunteer_Model volunteer_Model = new Volunteer_Model(); volunteer_Model.Volunteer_no = row.Volunteer_no.ToString(); volunteer_Model.Chinese_name = row.Chinese_name; volunteer_Model.Identity_type = row.Identity_type_name; volunteer_Model._Photo = row.Photo; volunteer_Model.Phone_no = row.Phone_no.ToString(); volunteer_Model.Mobile_no = row.Mobile_no.ToString(); Volunteer_Models.Add(volunteer_Model); } return(Volunteer_Models); }
public List <Volunteer_Model> SelectVolunteer_byName(string name, int application_unit_no, int service_period_no) { VolunteerEntities dbContext = new VolunteerEntities(); var q = from n1 in dbContext.Volunteer join n2 in dbContext.Identity_type on n1.Identity_type equals n2.Identity_type1 where n1.Chinese_name == name select new { Volunteer_no = n1.Volunteer_no, Chinese_name = n1.Chinese_name, Identity_type_name = n2.Identity_type_name, Photo = n1.Photo, }; string period_name = dbContext.Service_period1.Where(p => p.Service_period_no == service_period_no).Select(p => p.Service_period).First(); List <Volunteer_Model> Volunteer_Models = new List <Volunteer_Model>(); foreach (var row in q) { Volunteer_Model volunteer_Model = new Volunteer_Model(); volunteer_Model.Volunteer_no = row.Volunteer_no.ToString(); volunteer_Model.Chinese_name = row.Chinese_name; volunteer_Model.Identity_type = row.Identity_type_name; volunteer_Model._Photo = row.Photo; var q2 = from n1 in dbContext.Service_period2 join n2 in dbContext.Stages on n1.Stage equals n2.Stage_ID join n3 in dbContext.Service_period1 on n1.Service_period_no equals n3.Service_period_no where n2.Stage_type == "排班意願" && n2.Stage1 == "未排班" && //n1.Application_unit == application_unit_no && n3.Service_period == period_name && n1.Volunteer_no == row.Volunteer_no select n1; foreach (var row1 in q2) { if (row1.Wish_order > 0) { volunteer_Model.Wish_order = (int)row1.Wish_order; } else { volunteer_Model.Wish_order = 0; } } Volunteer_Models.Add(volunteer_Model); } return(Volunteer_Models); }
public List <Volunteer_Model> Search_Volunteer(string Name, string Group, string Expertise, List <int> years) { VolunteerEntities dbcontext = new VolunteerEntities(); var q = from p1 in dbcontext.Volunteer join p2 in dbcontext.Expertise2 on p1.Volunteer_no equals p2.Volunteer_no join p2_5 in dbcontext.Expertise1 on p2.Expertise_no equals p2_5.Expertise_no join p3 in dbcontext.Service_Group1 on p1.Volunteer_no equals p3.Volunteer_no join p3_5 in dbcontext.Service_group on p3.Group_no equals p3_5.Group_no join p4 in dbcontext.Identity_type on p1.Identity_type equals p4.Identity_type1 where (!(Expertise == "(無)") ? p2.Expertise1.Expertise == Expertise : true) && (!(Group == "(無)") ? p3_5.Group_name == Group : true) && (!(Name == "") ? p1.Chinese_name.Contains(Name) : true) && (!(years.Count == 0)?years.Contains(p1.Seniority):true) select new { Volunteer_no = p1.Volunteer_no.ToString(), Chinese_name = p1.Chinese_name.ToString(), English_name = p1.English_name.ToString(), Sex = p1.sex.ToString(), Birthday = p1.birthday.ToString(), IDcrad_no = p1.IDcrad_no.ToString(), Medical_record_no = p1.Medical_record_no.ToString(), Identity_type = p4.Identity_type_name.ToString(), Seniority = p1.Seniority.ToString(), Join_date = p1.Join_date.ToString(), Leave_date = p1.Leave_date.ToString(), Leave_reason = p1.Leave_reason.ToString(), Phone_no = p1.Phone_no.ToString(), Mobile_no = p1.Mobile_no.ToString(), Vest_no = p1.Vest_no.ToString(), Postal_code = p1.Postal_code.ToString(), Address = p1.Address.ToString(), Education = p1.Education.ToString(), Lssuing_unit_no = p1.Lssuing_unit_no.ToString(), Service_manual_no = p1.Service_manual_no.ToString(), Personality_scale = p1.Personality_scale.ToString(), //合併項 Expertise = p2_5.Expertise.ToString(), Group = p3_5.Group_name.ToString(), //Photo = p1.Photo.ToString() }; List <Volunteer_Model> volunteer_Models = new List <Volunteer_Model>(); List <Volunteer_Model> volunteer_Models2 = new List <Volunteer_Model>(); foreach (var row in q) { Volunteer_Model model = new Volunteer_Model(); model.Volunteer_no = row.Volunteer_no; model.Chinese_name = row.Chinese_name; model.English_name = row.English_name; model.Sex = row.Sex; model.Birthday = row.Birthday; model.IDcrad_no = row.IDcrad_no; model.Medical_record_no = row.Medical_record_no; model.Identity_type = row.Identity_type; model.Seniority = row.Seniority; model.Join_date = row.Join_date; model.Leave_date = row.Leave_date; model.Leave_reason = row.Leave_reason; model.Phone_no = row.Phone_no; model.Mobile_no = row.Mobile_no; model.Vest_no = row.Vest_no; model.Postal_code = row.Postal_code; model.Address = row.Address; model.Education = row.Education; model.Lssuing_unit_no = row.Lssuing_unit_no; model.Service_manual_no = row.Service_manual_no; model.Personality_scale = row.Personality_scale; model.Group = row.Group; model.Experise = row.Expertise; model.VLT_group = new List <string>(); model.VLT_experise = new List <string>(); var q2 = from p2 in q where p2.Volunteer_no == row.Volunteer_no select p2; foreach (var group in q2) { //將各種組別、專長合併成一個欄位 if (!model.VLT_group.Contains(group.Group)) { model.VLT_group.Add(group.Group); } if (!model.VLT_experise.Contains(group.Expertise)) { model.VLT_experise.Add(group.Expertise); } } volunteer_Models.Add(model); } List <string> 存在的volunteer_no = new List <string>(); foreach (Volunteer_Model q3 in volunteer_Models) { if (!存在的volunteer_no.Contains(q3.Volunteer_no)) { 存在的volunteer_no.Add(q3.Volunteer_no); volunteer_Models2.Add(q3); } } return(volunteer_Models2); }