public void InsertApply_result(int apply_ID, List <string> insert_Result)
{
List <int> Result_no = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in insert_Result)
{
var q = from n in dbContext.Human_assessment_result
where n.Assessment_result == row
select n;
foreach (var row2 in q)
{
Result_no.Add(row2.Assessment_result_ID);
}
}
dbContext.Apply_result.ToList();
foreach (var row in Result_no)
{
dbContext.Apply_result.Local.Add(new Apply_result
{
Apply_ID = apply_ID,
result_ID = row
});
}
dbContext.SaveChanges();
}
public void DeleteApply_result(int apply_ID, List <string> delete_Result)
{
List <int> Result_no = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in delete_Result)
{
var q = from n in dbContext.Human_assessment_result
where n.Assessment_result == row
select n;
foreach (var row2 in q)
{
Result_no.Add(row2.Assessment_result_ID);
}
}
foreach (var row in Result_no)
{
var results = (from n in dbContext.Apply_result
where n.Apply_ID == apply_ID &&
n.result_ID == row
select n).FirstOrDefault();
dbContext.Apply_result.Remove(results);
}
dbContext.SaveChanges();
}
public void UpdateApplication_unit(Application_unit_Model application_Unit_Model)
{
VolunteerEntities dbContext = new VolunteerEntities();
var q = from n in dbContext.Application_unit
where n.Application_unit_no == application_Unit_Model.Application_unit_no
select n;
foreach (var row in q)
{
var q2 = from n in dbContext.Service_group
where n.Group_name == application_Unit_Model.Group
select n;
foreach (var row2 in q2)
{
if (row.Group_no != row2.Group_no)
{
row.Group_no = row2.Group_no;
}
}
row.Application_unit1 = application_Unit_Model.Application_unit;
row.Application_phone_no = application_Unit_Model.Application_phone_no;
row.Principal = application_Unit_Model.Principal;
row.Principal_phone_no = application_Unit_Model.Principal_phone_no;
row.Application_address = application_Unit_Model.Application_address;
row.Work_content = application_Unit_Model.Work_content;
}
dbContext.SaveChanges();
}
public void DeleteUnit_Expertise(int unit_no, List <string> Delete_list)
{
List <int> Expertise_no = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in Delete_list)
{
var q = from n in dbContext.Expertise1
where n.Expertise == row
select n;
foreach (var row2 in q)
{
Expertise_no.Add(row2.Expertise_no);
}
}
foreach (var row in Expertise_no)
{
var Unit_expertise = (from n in dbContext.Expertises
where n.Expertise_no == row && n.Application_unit_no == unit_no
select n).FirstOrDefault();
dbContext.Expertises.Remove(Unit_expertise);
}
dbContext.SaveChanges();
}
public void UpdateManpower_apply_byreject(int apply_ID)
{
int rejectapply = 0;
VolunteerEntities dbContext = new VolunteerEntities();
var a = from n in dbContext.Stages
where n.Stage_type == "人力申請"
select new
{
Stage_ID = n.Stage_ID,
Stage = n.Stage1
};
foreach (var row in a)
{
switch (row.Stage.ToString())
{
case "申請駁回":
rejectapply = row.Stage_ID;
break;
}
}
var q = from n in dbContext.Manpower_apply
where n.Apply_ID == apply_ID
select n;
foreach (var row in q)
{
row.Apply_state = rejectapply;
}
dbContext.SaveChanges();
}
public void InsertUnit_Expertise(int unit_no, List <string> Insert_list)
{
List <int> Expertise_no = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in Insert_list)
{
var q = from n in dbContext.Expertise1
where n.Expertise == row
select n;
foreach (var row2 in q)
{
Expertise_no.Add(row2.Expertise_no);
}
}
dbContext.Expertises.ToList();
foreach (var row in Expertise_no)
{
dbContext.Expertises.Local.Add(new Expertise
{
Application_unit_no = unit_no,
Expertise_no = row
});
}
dbContext.SaveChanges();
}
public void DeleteApply_assessment(int apply_ID, List <string> delete_Assessment)
{
List <int> Assessment_no = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in delete_Assessment)
{
var q1 = from n in dbContext.Human_assessment
where n.Assessment_name == row
select n;
foreach (var row2 in q1)
{
Assessment_no.Add(row2.Assessment_ID);
}
}
foreach (var row in Assessment_no)
{
var Assessments = (from n in dbContext.Apply_Assessment
where n.Apply_ID == apply_ID &&
n.Assessment_ID == row
select n).FirstOrDefault();
dbContext.Apply_Assessment.Remove(Assessments);
}
dbContext.SaveChanges();
}
public void DeleteUnit_service_period(int unit_no, List <Unit_service_period_Model> unit_Service_Period_Models)
{
List <int> Service_Period_nos = new List <int>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in unit_Service_Period_Models)
{
var q = from n in dbContext.Service_period1
where n.Service_period == row.Service_period
select n;
foreach (var row1 in q)
{
Service_Period_nos.Add(row1.Service_period_no);
}
}
dbContext.Service_period.ToList();
foreach (var row in Service_Period_nos)
{
var Unit_Service_period = (from n in dbContext.Service_period
where n.Service_period_no == row && n.Application_unit_no == unit_no
select n).FirstOrDefault();
dbContext.Service_period.Remove(Unit_Service_period);
}
dbContext.SaveChanges();
}
public void UpdateShift_schedule(int unit_id, int service_period_no)
{
VolunteerEntities dbContext = new VolunteerEntities();
var q1 = (from n1 in dbContext.Service_period2
join n2 in dbContext.Stages
on n1.Stage equals n2.Stage_ID
where (n2.Stage1 == "續留" || n2.Stage1 == "新申請") &&
n1.Application_unit == unit_id &&
n1.Service_period_no == service_period_no
select n1).ToList();
var q2 = (from n in dbContext.Shift_schedule
where n.Application_unit_no == unit_id &&
n.Service_period_no == service_period_no
select n).ToList();
foreach (var row in q1)
{
if (q2.Where(p => p.Volunteer_no == row.Volunteer_no).Count() > 0)
{
continue;
}
else
{
Shift_schedule shift_Schedule = new Shift_schedule();
shift_Schedule.Volunteer_no = row.Volunteer_no;
shift_Schedule.Application_unit_no = (int)row.Application_unit;
shift_Schedule.Service_period_no = row.Service_period_no;
shift_Schedule.year = 2018;
shift_Schedule.Wish_order = 1;
dbContext.Shift_schedule.Add(shift_Schedule);
}
}
foreach (var row in q2)
{
if (q1.Where(p => p.Volunteer_no == row.Volunteer_no).Count() > 0)
{
continue;
}
else
{
var schedule = (from n in dbContext.Shift_schedule
where n.Application_unit_no == unit_id &&
n.Service_period_no == service_period_no &&
n.Volunteer_no == row.Volunteer_no
select n).FirstOrDefault();
dbContext.Shift_schedule.Remove(schedule);
}
}
dbContext.SaveChanges();
}
public void DeleteVoluteer_list(List <int> delete_list, int activity_no)
{
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in delete_list)
{
var q = (from n in dbContext.Activity1
where n.Activity_no == activity_no &&
n.Volunteer_no == row
select n).First();
q.Stage = "駁回";
}
dbContext.SaveChanges();
}
public void UpdateService_period_volunteer(List <Service_period_volunteer_Model> stay_Period_Volunteers, List <Service_period_volunteer_Model> new_Period_Volunteers, List <Service_period_volunteer_Model> leave_Period_Volunteers, int unit_id, int service_period_no)
{
VolunteerEntities dbContext = new VolunteerEntities();
var Stage_nos = (from n in dbContext.Stages
where n.Stage_type == "排班意願"
select n).ToList();
var q = (from n1 in dbContext.Service_period2
join n2 in dbContext.Stages
on n1.Stage equals n2.Stage_ID
where n1.Service_period_no == service_period_no &&
n1.Application_unit == unit_id
select new Service_period_volunteer
{
Volunteer_no = n1.Volunteer_no,
Stage_id = (int)n1.Stage,
Stage = n2.Stage1
}).ToList();
SwitchService_period_volunteer(stay_Period_Volunteers, q, Stage_nos);
SwitchService_period_volunteer(new_Period_Volunteers, q, Stage_nos);
SwitchService_period_volunteer(leave_Period_Volunteers, q, Stage_nos);
foreach (var row in update_list)
{
var q2 = (from n in dbContext.Service_period2
where n.Volunteer_no == row.Volunteer_no &&
n.Service_period_no == service_period_no &&
n.Application_unit == unit_id
select n).First();
q2.Stage = row.Stage_id;
}
foreach (var row in insert_list)
{
Service_period2 service_Period2 = new Service_period2();
service_Period2.Volunteer_no = row.Volunteer_no;
service_Period2.Service_period_no = service_period_no;
service_Period2.Application_unit = unit_id;
service_Period2.Stage = row.Stage_id;
dbContext.Service_period2.Add(service_Period2);
}
dbContext.SaveChanges();
}
public void InsertVoluteer_list(List <int> insert_list, int activity_no)
{
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in insert_list)
{
Activity1 activity1 = new Activity1();
activity1.Activity_no = activity_no;
activity1.Volunteer_no = row;
activity1.Registration_date = DateTime.Now;
activity1.Confirm_time = DateTime.Now;
activity1.Stage = "成功";
activity1.Vegetarian = false;
dbContext.Activity1.Add(activity1);
}
dbContext.SaveChanges();
}
public void UpdateSign_up_byStage(int Signup_no, string Stage)
{
VolunteerEntities dbContext = new VolunteerEntities();
var q = from n1 in dbContext.Sign_up
where n1.Sign_up_no == Signup_no
select n1;
var q2 = from n in dbContext.Stages
where n.Stage1.Contains(Stage)
select n;
foreach (var Signup_row in q)
{
foreach (var Stage_row in q2)
{
Signup_row.Stage = Stage_row.Stage_ID;
}
}
dbContext.SaveChanges();
}
public void InsertService_period(int apply_id)
{
VolunteerEntities dbContext = new VolunteerEntities();
var q = from n1 in dbContext.Apply_Service_period
join n2 in dbContext.Manpower_apply
on n1.Apply_ID equals n2.Apply_ID
where n1.Apply_ID == apply_id
select new
{
Service_period_no = n1.Service_period_no,
Volunteer_number = n1.Volunteer_number,
Application_unit_no = n2.Application_unit_no
};
foreach (var row in q)
{
var q2 = (from n in dbContext.Service_period
where n.Application_unit_no == row.Application_unit_no
select n).ToList();
var _period = q2.Where(p => p.Application_unit_no == row.Application_unit_no && p.Service_period_no == row.Service_period_no);
if (_period.Count() == 0)
{
Service_period service_Period = new Service_period();
service_Period.Application_unit_no = row.Application_unit_no;
service_Period.Service_period_no = row.Service_period_no;
service_Period.Volunteer_number = row.Volunteer_number;
dbContext.Service_period.Add(service_Period);
}
else
{
_period.First().Volunteer_number = row.Volunteer_number;
}
}
dbContext.SaveChanges();
}
public void InsertApplication_unit(Application_unit_Model application_Unit_Model)
{
int group_num = 0;
VolunteerEntities dbContext = new VolunteerEntities();
var q = from n in dbContext.Service_group
where n.Group_name == application_Unit_Model.Group
select n;
foreach (var row in q)
{
group_num = row.Group_no;
}
dbContext.Application_unit.ToList();
dbContext.Application_unit.Local.Add(new Application_unit
{
Application_unit1 = application_Unit_Model.Application_unit,
Group_no = group_num,
Application_phone_no = application_Unit_Model.Application_phone_no,
Principal = application_Unit_Model.Principal,
Principal_phone_no = application_Unit_Model.Principal_phone_no,
Application_address = application_Unit_Model.Application_address,
Work_content = application_Unit_Model.Work_content
});
dbContext.SaveChanges();
var q2 = from n in dbContext.Application_unit
where n.Application_unit1 == application_Unit_Model.Application_unit
select n;
foreach (var row in q2)
{
Application_unit_no = row.Application_unit_no;
}
}
public void InsertUnit_service_period(int unit_no, List <Unit_service_period_Model> unit_Service_Period_Models)
{
List <Unit_service_period_Model> Service_Period_nos = new List <Unit_service_period_Model>();
VolunteerEntities dbContext = new VolunteerEntities();
foreach (var row in unit_Service_Period_Models)
{
var q1 = from n in dbContext.Service_period1
where n.Service_period == row.Service_period
select n;
foreach (var row1 in q1)
{
Unit_service_period_Model unit_Service_Period_Model = new Unit_service_period_Model();
unit_Service_Period_Model.Volunteer_number = row.Volunteer_number;
unit_Service_Period_Model.Service_period = row1.Service_period_no.ToString();
Service_Period_nos.Add(unit_Service_Period_Model);
}
}
var q = from n in dbContext.Service_period
where n.Application_unit_no == unit_no
select n;
foreach (var row1 in q)
{
foreach (var row2 in Service_Period_nos)
{
if (row1.Service_period_no.ToString() == row2.Service_period)
{
row1.Volunteer_number = int.Parse(row2.Volunteer_number);
}
}
}
dbContext.SaveChanges();
}
public void UpdateManpower_apply(int apply_ID, int reply_number, string repply_description, int supervision_ID)
{
int processing = 0;
int end_processing = 0;
Apply_assessment_Model apply_Assessment_Model = new Apply_assessment_Model();
List <string> Assessments = apply_Assessment_Model.SelectApply_assessment_byApply_ID(apply_ID);
Apply_result_Model apply_Result_Model = new Apply_result_Model();
List <string> Assessment_results = apply_Result_Model.SelectApply_result_byApply_ID(apply_ID);
VolunteerEntities dbContext = new VolunteerEntities();
var a = from n in dbContext.Stages
where n.Stage_type == "人力申請"
select new
{
Stage_ID = n.Stage_ID,
Stage = n.Stage1
};
foreach (var row in a)
{
switch (row.Stage.ToString())
{
case "處理中":
processing = row.Stage_ID;
break;
case "申請完成":
end_processing = row.Stage_ID;
break;
}
}
var q = from n in dbContext.Manpower_apply
where n.Apply_ID == apply_ID
select n;
foreach (var row in q)
{
row.Reply_number = reply_number;
row.Repply_description = repply_description;
row.Supervision_ID = supervision_ID;
row.Supervision_heads = "熊主任";
row.Reply_date = DateTime.Now;
if (reply_number > 0 &&
!string.IsNullOrEmpty(repply_description) &&
Assessments.Count() > 0 &&
Assessment_results.Count() > 0)
{
row.Apply_state = end_processing;
}
else
{
row.Apply_state = processing;
}
}
dbContext.SaveChanges();
}