//按下修改表單後, 表單從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);
}
