//按下修改表單後, 表單從volunteer資料讀取資料
//修改要修改的東西後 按"確認"按鈕 修改資料
private void Button_Click(object sender, RoutedEventArgs e)
{
// WLC
DateTime myBirthDay = new DateTime();
DateTime myJoin_date = new DateTime();
DateTime myLeave_date = new DateTime();
if ((!string.IsNullOrEmpty(cbBirthDayYear.Text)) && (!string.IsNullOrEmpty(cbBirthDayMonth.Text)) && (!string.IsNullOrEmpty(cbBirthDayDay.Text)))
{
myBirthDay = DateTime.Parse(cbBirthDayYear.Text + "/" + cbBirthDayMonth.Text + "/" + cbBirthDayDay.Text);
}
if ((!string.IsNullOrEmpty(cbJoin_dateYear.Text)) && (!string.IsNullOrEmpty(cbJoin_dateMonth.Text)) && (!string.IsNullOrEmpty(cbJoin_dateDay.Text)))
{
myJoin_date = DateTime.Parse(cbJoin_dateYear.Text + "/" + cbJoin_dateMonth.Text + "/" + cbJoin_dateDay.Text);
}
if ((!string.IsNullOrEmpty(cbLeave_dateYear.Text)) && (!string.IsNullOrEmpty(cbLeave_dateMonth.Text)) && (!string.IsNullOrEmpty(cbLeave_dateDay.Text)))
{
myLeave_date = DateTime.Parse(cbLeave_dateYear.Text + "/" + cbLeave_dateMonth.Text + "/" + cbLeave_dateDay.Text);
}
VolunteerEntities dbContext = new VolunteerEntities();
//// System.Windows.Data.CollectionViewSource volunteerViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("volunteerViewSource")));
//dbContext.Volunteer.ToList();
//// volunteerViewSource.Source = dbContext.Volunteer.Local;
//dbContext.Volunteer.Local.Add(new Volunteer
var q = from a in dbContext.Volunteer
where a.Volunteer_no == Volunteer_no
select a;
foreach (var a in q)
{
a.Chinese_name = txtChineseName.Text;
a.English_name = txtEnglishName.Text;
a.sex = cbsex.Text;
a.birthday = myBirthDay;
a.IDcrad_no = txtIDcrad_no.Text;
a.Medical_record_no = txtMedical_record_no.Text;
// WLC
if (int.TryParse(cbIdentity_type.Text, out int Identity_type))
{
a.Identity_type = Identity_type;
}
if (int.TryParse(txtSeniority.Text, out int Seniority))
{
a.Seniority = Seniority;
}
a.Join_date = myJoin_date;
a.Leave_date = myLeave_date;
a.Leave_reason = cbLeaveReason.Text;
if (int.TryParse(txtPhone_no.Text, out int Phone_no))
{
a.Phone_no = Phone_no;
}
if (int.TryParse(txtMobile_no.Text, out int Mobile_no))
{
a.Mobile_no = Mobile_no;
}
a.Vest_no = txtVest_no.Text;
if (int.TryParse(txtPostal_code.Text, out int Postal_code))
{
a.Postal_code = Postal_code;
}
a.Address = txtAddress.Text;
a.Education = cbEducation.Text;
if (int.TryParse(txtLssuing_unit_no.Text, out int Lssuing_unit_no))
{
a.Lssuing_unit_no = Lssuing_unit_no;
}
a.Service_manual_no = txtService_manual_no.Text;
a.Personality_scale = txtPersonality_scale.Text;
a.Photo = imagebyte;
}
foreach (var n in AAA)
{
Expertise2 e1 = new Expertise2 {
Volunteer_no = Volunteer_no, Expertise_no = n
};
dbContext.Expertise2.Add(e1);
}
foreach (var n in AAA1)
{
var q1 = from de in dbContext.Expertise2
where de.Expertise_no == n && de.Volunteer_no == Volunteer_no
select new { pk = de.Primary_key };
foreach (var x in q1)
{
Expertise2 ex = dbContext.Expertise2.Find(x.pk);
dbContext.Expertise2.Remove(ex);
}
}
foreach (var n in BBB)
{
Service_Group1 e1 = new Service_Group1 {
Volunteer_no = Volunteer_no, Group_no = n
};
dbContext.Service_Group1.Add(e1);
}
foreach (var n in BBB1)
{
var q1 = from de in dbContext.Service_Group1
where de.Group_no == n && de.Volunteer_no == Volunteer_no
select new { pk = de.Primary_key };
foreach (var x in q1)
{
Service_Group1 ex = dbContext.Service_Group1.Find(x.pk);
dbContext.Service_Group1.Remove(ex);
}
}
try
{ dbContext.SaveChanges(); }
catch
{ throw; }
MessageBox.Show("修改成功");
}
public void Inertgroup_andexpertise(List <int> AD)
{
var Sign_up_list = (from n in Myentity.Sign_up
select n).ToList();
var Sign_up_expertise_list = (from n in Myentity.Sign_up_expertise
select n).ToList();
var Sign_up_Service_period_list = (from n in Myentity.Sign_up_Service_period select n).ToList();
var account_list = (from n in Myentity.accounts select n).ToList();
var stage_id = (from n in Myentity.Stages where n.Stage1 == "未排班" && n.Stage_type == "排班意願" select n.Stage_ID).First();
foreach (var row in AD)
{
Sign_up sign_Up = Sign_up_list.Where(p => p.Sign_up_no == row).First();
var q = (from n in Myentity.Volunteer
where n.Chinese_name == sign_Up.Chinese_name &&
n.Join_date == sign_Up.Sign_up_date
select n).First();
Service_Group1 service_Group1 = new Service_Group1();
service_Group1.Volunteer_no = q.Volunteer_no;
service_Group1.Group_no = 1;
Myentity.Service_Group1.Add(service_Group1);
var expertise_list = Sign_up_expertise_list.Where(p => p.Sign_up_no == row).ToList();
foreach (var expertise in expertise_list)
{
Expertise2 expertise2 = new Expertise2();
expertise2.Volunteer_no = q.Volunteer_no;
expertise2.Expertise_no = (int)expertise.Expertise;
Myentity.Expertise2.Add(expertise2);
}
var Service_period_list = Sign_up_Service_period_list.Where(p => p.Sign_up_no == row).ToList();
foreach (var _period in Service_period_list)
{
Service_period2 service_Period2 = new Service_period2();
service_Period2.Volunteer_no = q.Volunteer_no;
service_Period2.Service_period_no = _period.Service_period_no;
service_Period2.Stage = stage_id;
service_Period2.Wish_order = _period.Wish_order;
Myentity.Service_period2.Add(service_Period2);
}
var account = account_list.Select(p => p.Account_number).Max().Trim();
account _account = new account();
_account.Account_number = (int.Parse(account) + 1).ToString();
_account.Password = (int.Parse(account) + 1).ToString();
_account.User_ID = q.Volunteer_no;
_account.Permission = "Volunteer";
Myentity.accounts.Add(_account);
ThreadStart TS = new ThreadStart(
delegate()
{
sending_email(sign_Up.Email, sign_Up.Chinese_name + sign_Up.English_name, Issend_pass, _account.Account_number); //寄送郵件
System.Windows.Forms.Application.DoEvents();
});
send_mail = new Thread(TS);
send_mail.IsBackground = true;
send_mail.Start();
break;
}
this.Myentity.SaveChanges();
}
//亞衡
////加入一個靜態變數 用來存放當前加入的Volunteer
//public static int _Volunteer_no;
//從空白的表單 填完資料後
//最後按"確認"按鈕 新增資料
private void Button_Click1(object sender, RoutedEventArgs e)
{
// WLC
DateTime myBirthDay = new DateTime();
DateTime myJoin_date = new DateTime();
DateTime myLeave_date = new DateTime();
if ((!string.IsNullOrEmpty(cbBirthDayYear.Text)) && (!string.IsNullOrEmpty(cbBirthDayMonth.Text)) && (!string.IsNullOrEmpty(cbBirthDayDay.Text)))
{
myBirthDay = DateTime.Parse(cbBirthDayYear.Text + "/" + cbBirthDayMonth.Text + "/" + cbBirthDayDay.Text);
}
if ((!string.IsNullOrEmpty(cbJoin_dateYear.Text)) && (!string.IsNullOrEmpty(cbJoin_dateMonth.Text)) && (!string.IsNullOrEmpty(cbJoin_dateDay.Text)))
{
myJoin_date = DateTime.Parse(cbJoin_dateYear.Text + "/" + cbJoin_dateMonth.Text + "/" + cbJoin_dateDay.Text);
}
if ((!string.IsNullOrEmpty(cbLeave_dateYear.Text)) && (!string.IsNullOrEmpty(cbLeave_dateMonth.Text)) && (!string.IsNullOrEmpty(cbLeave_dateDay.Text)))
{
myLeave_date = DateTime.Parse(cbLeave_dateYear.Text + "/" + cbLeave_dateMonth.Text + "/" + cbLeave_dateDay.Text);
}
int intIdentity_type = 0;
int intPhone_no = 0;
int intMobile_no = 0;
int intPostal_code = 0;
int intLssuing_unit_no = 0;
int intSeniority = 0;
if (int.TryParse(cbIdentity_type.Text, out int Identity_type))
{
intIdentity_type = Identity_type;
}
if (int.TryParse(txtSeniority.Text, out int Seniority))
{
intSeniority = Seniority;
}
if (int.TryParse(txtPhone_no.Text, out int Phone_no))
{
intPhone_no = Phone_no;
}
if (int.TryParse(txtMobile_no.Text, out int Mobile_no))
{
intMobile_no = Mobile_no;
}
if (int.TryParse(txtPostal_code.Text, out int Postal_code))
{
intPostal_code = Postal_code;
}
if (int.TryParse(txtLssuing_unit_no.Text, out int Lssuing_unit_no))
{
intLssuing_unit_no = Lssuing_unit_no;
}
VolunteerEntities dbContext = new VolunteerEntities();
// System.Windows.Data.CollectionViewSource volunteerViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("volunteerViewSource")));
dbContext.Volunteer.ToList();
// volunteerViewSource.Source = dbContext.Volunteer.Local;
dbContext.Volunteer.Local.Add(new Volunteer
{
Chinese_name = txtChineseName.Text,
English_name = txtEnglishName.Text,
sex = cbsex.Text,
birthday = myBirthDay,
IDcrad_no = txtIDcrad_no.Text,
Medical_record_no = txtMedical_record_no.Text,
//WLC
Identity_type = intIdentity_type,
Seniority = intSeniority,
Join_date = myJoin_date,
Leave_date = myLeave_date,
Leave_reason = cbLeaveReason.Text,
Phone_no = intPhone_no,
Mobile_no = intMobile_no,
Vest_no = txtVest_no.Text,
Postal_code = intPostal_code,
Address = txtAddress.Text,
Education = cbEducation.Text,
Lssuing_unit_no = intLssuing_unit_no,
Service_manual_no = txtService_manual_no.Text,
Personality_scale = txtPersonality_scale.Text,
Photo = imagebyte
});
//dbContext.SaveChanges()後，會得到新的dbContext.Volunteer的Volunteer_no流水號
dbContext.SaveChanges();
//查詢 "新的dbContext.Volunteer的Volunteer_no流水號" 的那一筆 資料
var q = (from n in dbContext.Volunteer
select new { n.Volunteer_no }).ToList().LastOrDefault();
foreach (var n in AAA)
{
Expertise2 e1 = new Expertise2 {
Volunteer_no = q.Volunteer_no, Expertise_no = n
};
dbContext.Expertise2.Add(e1);
}
foreach (var n in BBB)
{
Service_Group1 sg1 = new Service_Group1 {
Volunteer_no = q.Volunteer_no, Group_no = n
};
dbContext.Service_Group1.Add(sg1);
}
dbContext.SaveChanges();
MessageBox.Show("志工資料新增成功");
// int numberb=0;
//foreach(var b in q )
//{
// numberb = b.Volunteer_no;
//}
//dbContext.Expertise2.ToList();
//dbContext.Expertise2.Local.Add(new Expertise2
//{
// Volunteer_no = numberb
//});
//亞衡 連線 作法 找出volunteer_no=========================================================================
//try
//{
// using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Volunteer;Integrated Security=True"))
// {
// using (SqlCommand cmd = new SqlCommand())
// {
// cmd.CommandText = "insert into [Volunteer].[Volunteer](Chinese_name,English_name,sex,birthday,IDcrad_no,Medical_record_no,Identity_type,Seniority,Join_date,Leave_date,Leave_reason,Phone_no,Mobile_no,Vest_no,Postal_code,Address,Education,Lssuing_unit_no,Service_manual_no,Personality_scale,Photo)" +
// "values(@Chinese_name,@English_name,@sex,@birthday,@IDcrad_no,@Medical_record_no,@Identity_type,@Seniority,@Join_date,@Leave_date,@Leave_reason,@Phone_no,@Mobile_no,@Vest_no,@Postal_code,@Address,@Education,@Lssuing_unit_no,@Service_manual_no,@Personality_scale,@Photo);" +
// "select Volunteer_no from [Volunteer].[Volunteer] where Volunteer_no=Scope_Identity()";
// cmd.Connection = conn;
// cmd.Parameters.Add("@Chinese_name", SqlDbType.NVarChar).Value = txtChineseName.Text;
// cmd.Parameters.Add("@English_name", SqlDbType.NVarChar).Value = txtEnglishName.Text;
// cmd.Parameters.Add("@sex", SqlDbType.NChar).Value = cbsex.Text;
// cmd.Parameters.Add("@birthday", SqlDbType.DateTime).Value = myBirthDay;
// cmd.Parameters.Add("@IDcrad_no", SqlDbType.NVarChar).Value = txtIDcrad_no.Text;
// cmd.Parameters.Add("@Medical_record_no", SqlDbType.NChar).Value = txtMedical_record_no.Text;
// cmd.Parameters.Add("@Identity_type", SqlDbType.Int).Value = intIdentity_type;
// cmd.Parameters.Add("@Seniority", SqlDbType.Int).Value = intSeniority;
// cmd.Parameters.Add("@Join_date", SqlDbType.Date).Value = myJoin_date;
// cmd.Parameters.Add("@Leave_date", SqlDbType.Date).Value = myLeave_date;
// cmd.Parameters.Add("@Leave_reason", SqlDbType.NChar).Value = cbLeaveReason.Text;
// cmd.Parameters.Add("@Phone_no", SqlDbType.Int).Value = intPhone_no;
// cmd.Parameters.Add("@Mobile_no", SqlDbType.Int).Value = intMobile_no;
// cmd.Parameters.Add("@Vest_no", SqlDbType.NChar).Value = txtVest_no.Text;
// cmd.Parameters.Add("@Postal_code", SqlDbType.Int).Value = intPostal_code;
// cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = txtAddress.Text;
// cmd.Parameters.Add("@Education", SqlDbType.NVarChar).Value = cbEducation.Text;
// cmd.Parameters.Add("@Lssuing_unit_no", SqlDbType.Int).Value = Lssuing_unit_no;
// cmd.Parameters.Add("@Service_manual_no", SqlDbType.NChar).Value = txtService_manual_no.Text;
// cmd.Parameters.Add("@Personality_scale", SqlDbType.NVarChar).Value = txtPersonality_scale.Text;
// cmd.Parameters.Add("@Photo", SqlDbType.VarBinary).Value = imagebyte;
// conn.Open();
// _Volunteer_no = (int)cmd.ExecuteScalar();
//=================================================================
}