//按下修改表單後, 表單從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("修改成功"); }
//按下修改表單後, 表單從volunteer資料讀取資料 public Volunteer_data_View(int a) { InitializeComponent(); //按下"確定"按鈕 this.btn_confirm.Click += Button_Click; Volunteer_no = a; VolunteerEntities dbContext = new VolunteerEntities(); var q = from v in dbContext.Volunteer //join v2 in dbContext.Identity_type on v.Identity_type equals v2.Identity_type1 where v.Volunteer_no == Volunteer_no select v; foreach (var v in q) { txtChineseName.Text = v.Chinese_name; txtEnglishName.Text = v.English_name; //?? cbsex.SelectedValue = v.sex.Trim(); //將birthday 拆開year, month, day if (v.birthday != null) { string sDate = Convert.ToString(v.birthday); DateTime datevalue = (Convert.ToDateTime(sDate)); string yy = datevalue.Year.ToString(); string mn = datevalue.Month.ToString(); string dy = datevalue.Day.ToString(); //WLC //cbBirthDayYear.Text = yy; //cbBirthDayMonth.Text = mn; //cbBirthDayDay.Text = dy; //可以把ComboBox的資料顯示出來 cbBirthDayYear.SelectedValue = yy.Trim(); cbBirthDayMonth.SelectedValue = mn.Trim(); cbBirthDayDay.SelectedValue = dy.Trim(); } //身份證字號 txtIDcrad_no.Text = v.IDcrad_no; //病歷號 txtMedical_record_no.Text = v.Medical_record_no; //身份類別 cbIdentity_type.SelectedValue = (Convert.ToString(v.Identity_type)).Trim(); //cbIdentity_type.Text = v.Identity_type.ToString(); //服務年資 txtSeniority.Text = Convert.ToString(v.Seniority); //加入日期 if (v.Join_date != null) { string sJoin_date = Convert.ToString(v.Join_date); DateTime Join_dateValue = (Convert.ToDateTime(sJoin_date)); string yyJoin_date = Convert.ToString(Join_dateValue.Year); string mnJoin_date = Convert.ToString(Join_dateValue.Month); string dyJoin_date = Convert.ToString(Join_dateValue.Day); cbJoin_dateYear.SelectedValue = yyJoin_date.Trim(); cbJoin_dateMonth.SelectedValue = mnJoin_date.Trim(); cbJoin_dateDay.SelectedValue = dyJoin_date.Trim(); } //離開日期 if (v.Leave_date != null) { string sLeave_date = Convert.ToString(v.Leave_date); DateTime Leave_dateValue = (Convert.ToDateTime(sLeave_date)); string yyLeave_date = Convert.ToString(Leave_dateValue.Year); string mnLeave_date = Convert.ToString(Leave_dateValue.Month); string dyLeave_date = Convert.ToString(Leave_dateValue.Day); cbLeave_dateYear.SelectedValue = yyLeave_date.Trim(); cbLeave_dateMonth.SelectedValue = mnLeave_date.Trim(); cbLeave_dateDay.SelectedValue = dyLeave_date.Trim(); } //離開原因 cbLeaveReason.Text = (v.Leave_reason == "" ? null : v.Leave_reason.Trim()); //地址 txtAddress.Text = v.Address; //電話號碼 txtPhone_no.Text = Convert.ToString(v.Phone_no); //手機號碼 txtMobile_no.Text = Convert.ToString(v.Mobile_no); //志工背心號碼 txtVest_no.Text = v.Vest_no; //郵遞區號 txtPostal_code.Text = Convert.ToString(v.Postal_code); //學歷 cbEducation.Text = v.Education; //發證單位 txtLssuing_unit_no.Text = Convert.ToString(v.Lssuing_unit_no); //志工服務手冊編號 txtService_manual_no.Text = v.Service_manual_no; //人格量表結果 txtPersonality_scale.Text = v.Personality_scale; //照片 if (v.Photo != null) { byte[] vphoto = null; vphoto = v.Photo; myImage.Source = ConvertByteArrayToBitmapImage(vphoto); } } }
//亞衡 ////加入一個靜態變數 用來存放當前加入的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(); //================================================================= }
private void Window_Loaded_1(object sender, RoutedEventArgs e) { VolunteerEntities dbContext = new VolunteerEntities(); //System.Windows.Data.CollectionViewSource volunteerViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("volunteerViewSource"))); dbContext.Volunteer.ToList(); //volunteerViewSource.Source = dbContext.Volunteer.Local; // 透過設定 CollectionViewSource.Source 屬性載入資料: // volunteerViewSource.Source = [泛用資料來源] //cbsex.Text = "請選擇"; //cbLeaveReason.Text = "請選擇"; //cbEducation.Text = "請選擇"; // WLC if (string.IsNullOrEmpty(cbIdentity_type.Text)) { cbIdentity_type.Text = "請輸入數字1到5"; } if (string.IsNullOrEmpty(txtSeniority.Text)) { txtSeniority.Text = "請輸入數字"; } if (string.IsNullOrEmpty(txtPhone_no.Text)) { txtPhone_no.Text = "請輸入數字"; } if (string.IsNullOrEmpty(txtMobile_no.Text)) { txtMobile_no.Text = "請輸入數字"; } if (string.IsNullOrEmpty(txtLssuing_unit_no.Text)) { txtLssuing_unit_no.Text = "請輸入數字1到10"; } if (string.IsNullOrEmpty(txtPostal_code.Text)) { txtPostal_code.Text = "請輸入數字"; } //姓別combobox List <string> itemSex = GetDataSex(); cbsex.ItemsSource = itemSex; //身份類別combobox List <string> itemIdentity_type = GetDataIdentity_type(); cbIdentity_type.ItemsSource = itemIdentity_type; //birthday生日 年的combobox List <string> ItemYear = GetItemYear(); cbBirthDayYear.ItemsSource = ItemYear; //月的combobox List <string> ItemMonth = GetItemMonth(); cbBirthDayMonth.ItemsSource = ItemMonth; //日的comboBox List <string> ItemDay = GetItemDay(); cbBirthDayDay.ItemsSource = ItemDay; cbJoin_dateYear.ItemsSource = ItemYear; cbJoin_dateMonth.ItemsSource = ItemMonth; cbJoin_dateDay.ItemsSource = ItemDay; cbLeave_dateYear.ItemsSource = ItemYear; cbLeave_dateMonth.ItemsSource = ItemMonth; cbLeave_dateDay.ItemsSource = ItemDay; }
//這個方法用來回傳Dictionary key為 public static Dictionary <string, List <string> > GetPeriodNames(int application_unit_no) { int lastyear = DateTime.Now.AddYears(-1).Year; Dictionary <string, List <string> > QueryDict = new Dictionary <string, List <string> >(); List <string> QueryStr = new List <string>(); //存放班別(中文) //List<string> QueryNames = new List<string>(); //存放各班別的所有人名 VolunteerEntities db = new VolunteerEntities(); var result = from ss in db.Shift_schedule join sp1 in db.Service_period1 on ss.Service_period_no equals sp1.Service_period_no join v in db.Volunteers on ss.Volunteer_no equals v.Volunteer_no where ss.Application_unit_no == application_unit_no && ss.year == lastyear select new Unit_Schedule { Schedule = ss, volunteer = v, period1 = sp1 }; foreach (var item in result.ToList()) { string periodCategoryCn = item.period1.Service_period; QueryStr.Add(periodCategoryCn); }//加好資料庫的的紀錄 QueryStr = QueryStr.Distinct().ToList();//讓他不重複 foreach (var item in QueryStr) { var names = result.Where(s => s.period1.Service_period == item).Select(s => s.volunteer.Chinese_name).ToList(); QueryDict.Add(item, names);//資料庫裡面的個班別的各個名字 } //建立一個完整的Dionary用來存放周一~周日日間 周一~週日晚間 Dictionary <string, List <string> > completePeriod = new Dictionary <string, List <string> >(); List <string> strL = new List <string>(); strL.Add(""); completePeriod.Add("週一上午", strL); completePeriod.Add("週二上午", strL); completePeriod.Add("週三上午", strL); completePeriod.Add("週四上午", strL); completePeriod.Add("週五上午", strL); completePeriod.Add("週六上午", strL); completePeriod.Add("週日上午", strL); completePeriod.Add("週一下午", strL); completePeriod.Add("週二下午", strL); completePeriod.Add("週三下午", strL); completePeriod.Add("週四下午", strL); completePeriod.Add("週五下午", strL); completePeriod.Add("週六下午", strL); completePeriod.Add("週日下午", strL); completePeriod.Add("週一夜間", strL); completePeriod.Add("週二夜間", strL); completePeriod.Add("週三夜間", strL); completePeriod.Add("週四夜間", strL); completePeriod.Add("週五夜間", strL); completePeriod.Add("週六夜間", strL); completePeriod.Add("週日夜間", strL); completePeriod.Add("週一支援", strL); completePeriod.Add("週二支援", strL); completePeriod.Add("週三支援", strL); completePeriod.Add("週四支援", strL); completePeriod.Add("週五支援", strL); completePeriod.Add("週六支援", strL); completePeriod.Add("週日支援", strL);//字典到這邊為止建好了 List <string> temp = new List <string> { "週一上午", "週二上午", "週三上午", "週四上午", "週五上午", "週六上午", "週日上午", "週一下午", "週二下午", "週三下午", "週四下午", "週五下午", "週六下午", "週日下午", "週一夜間", "週二夜間", "週三夜間", "週四夜間", "週五夜間", "週六夜間", "週日夜間", "週一支援", "週二支援", "週三支援", "週四支援", "週五支援", "週六支援", "週日支援" }; for (int i = 0; i < temp.Count; i++)//長度根據temp { foreach (var item in QueryDict) { if (item.Key == temp[i]) { completePeriod[item.Key] = QueryDict[item.Key]; } } } return(completePeriod); }