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);
}