private void UserLogin() { #region 数 AccessMdb mdb = new AccessMdb(); DataTable userTable = new DataTable(); #endregion #region 主処理 try { lblErrorMsg.Visibility = Visibility.Collapsed; //入力チェック if (this.txtID.Text.NullToString().TrimEnd() == "" || this.txtPW.Password.NullToString().TrimEnd() == "") { lblErrorMsg.Visibility = Visibility.Visible; lblErrorMsg.Content = "ログインIDあるいはパスワードを入力してください。"; return; } mdb.Connect(); userTable = mdb.ExecuteSql("Select * From T_USER Where UserID='" + txtID.Text + "' And PassWord='******'"); mdb.Disconnect(); if (userTable.Rows.Count == 0) { lblErrorMsg.Visibility = Visibility.Visible; lblErrorMsg.Content = "ログインIDまたはパスワードが不正です。 "; } else { lblErrorMsg.Visibility = Visibility.Hidden; List lwindow = new List(userTable.Rows[0]["UserID"].NullToString()); lwindow.lb_UserID.Content = userTable.Rows[0]["UserName"].NullToString() + " さん"; lwindow.Show(); this.Close(); } } #endregion #region エラー catch (Exception ex) { MessageBox.Show(ex.Message); } #endregion #region 後処理 finally { mdb.Disconnect(); mdb = null; if (userTable != null) { userTable.Dispose(); userTable = null; } } #endregion }
public void Initialize(string userID) { _UserID.Text = userID; dg_List.ItemsSource = MessageList(""); tvItem_All.IsSelected = true; datagrid_UserList.ItemsSource = UserList(); if (_UserID.Text != string.Empty) { AccessMdb mdb = new AccessMdb(); mdb.Connect(); DataTable userTable = mdb.ExecuteSql("Select * From T_USER Where UserID='" + _UserID.Text + "'"); mdb.Disconnect(); if (userTable.Rows.Count != 0) { if (userTable.Rows[0]["AuthorityFlag"].ToString() == "0") { tvItem_user.IsEnabled = false; Color color = (Color)ColorConverter.ConvertFromString("Gray"); tvItem_user.Foreground = new SolidColorBrush(color); } else if (userTable.Rows[0]["AuthorityFlag"].ToString() == "9") { tvItem_user.IsEnabled = true; Color color = (Color)ColorConverter.ConvertFromString("#4db6e9"); tvItem_user.Foreground = new SolidColorBrush(color); } } else { MessageBox.Show("エラーが発生しました!もう一度ログインしてください!"); this.Close(); } } else { MessageBox.Show("エラーが発生しました!もう一度ログインしてください!"); this.Close(); } }
public List <ReceivePeople> ReceivePeopleList(Message message) { try { AccessMdb mdb = new AccessMdb(); mdb.Connect(); string sql = ""; sql += "Select"; sql += " s.OpenedDate"; sql += ",s.LastRefData"; sql += ",s.StatsFlag"; sql += ",s.CompletionDate"; sql += ",u.UserName"; sql += ",c.Cmt"; sql += ",c.CreatDateTime"; sql += " From"; sql += "("; sql += "T_SendDetails As s"; sql += " Left Join"; sql += " T_USER As u"; sql += " On s.UserID = u.UserID"; sql += ")"; sql += " Left Join"; sql += " T_MessageCmt As c"; sql += " On s.MessageID = c.MessageID"; sql += " And s.UserID = c.UserID"; sql += " Where s.MessageID='" + ViewData["MessageID"] + "'"; DataTable Datatb_ReceivePeople = mdb.ExecuteSql(sql); mdb.Disconnect(); if (Datatb_ReceivePeople.Rows.Count == 0) { dg_UserList.Visibility = Visibility.Hidden; tb_warnning.Visibility = Visibility.Visible; } receivePeopleList = new List <ReceivePeople>(); for (int i = 0; i < Datatb_ReceivePeople.Rows.Count; i++) { receivePeopleList.Add( new ReceivePeople() { UserName = Datatb_ReceivePeople.Rows[i]["UserName"].NullToString() , OpenedDateTime = Datatb_ReceivePeople.Rows[i]["OpenedDate"].NullToString() , LastReDateTime = Datatb_ReceivePeople.Rows[i]["LastRefData"].NullToString() , State = Datatb_ReceivePeople.Rows[i]["StatsFlag"].NullToString() , CompletionDate = Datatb_ReceivePeople.Rows[i]["CompletionDate"].NullToString() , CMTDateTime = Datatb_ReceivePeople.Rows[i]["CreatDateTime"].NullToString() }); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return(receivePeopleList); }
private void btn_Send_Click(object sender, RoutedEventArgs e) { bool canUpdate = false; string[] files = new string[] { tb_file1.Text, tb_file2.Text }; bool msgStatus = (bool)cb_CompletionStatus.IsChecked; string coment = txtArea_Conment.Text; List <string> msgBox = new List <string>(); for (int i = 0; i < files.Length; i++) { if (files[i] != string.Empty) { files[i] = ViewData["File" + (i + 1).ToString()].NullToString(); } } AccessMdb mdb = new AccessMdb(); mdb.Connect(); //更新要かどうかを判断する if (coment != string.Empty || files[0] != string.Empty || files[1] != string.Empty || msgStatus != cb_HiddenStatus.IsChecked) { canUpdate = true; if (msgStatus != cb_HiddenStatus.IsChecked) { msgBox.Add("CompletionStatus"); } } else { MessageBox.Show("送信できる内容を見つかれなかった!"); } //コメント、完了状況、添付ファイルを更新します if (canUpdate) { DataTable dtb_Cmt = mdb.ExecuteSql("Select MessageID From T_MessageCmt Where MessageID='" + ViewData["MessageID"] + "'" + " And UserID='" + ViewData["UserID"] + "'"); if (dtb_Cmt.Rows.Count == 0) { //完了状況を更新します int statsFlag; if (cb_CompletionStatus.IsChecked == true) { statsFlag = 1; } else { statsFlag = 0; } mdb.ExecuteSql("Update T_SendDetails Set StatsFlag=" + statsFlag + " Where MessageID='" + ViewData["MessageID"] + "'" + " And SeqNo=" + ViewData["MsgSeqNo"]); //コメントを追加します mdb.ExecuteSql("Insert Into T_MessageCmt (" + "[MessageID]" + ",[UserID]" + ",[Cmt]" + ",[AttachmentsFile1]" + ",[AttachmentsFile2]" + ",[CreatDateTime]" + ",[UpdateDateTime]" + ")" + " Values" + " (" + "'" + ViewData["MessageID"].NullToString() + "'" + ",'" + ViewData["UserID"].NullToString() + "'" + ",'" + coment + "'" + ",'" + files[0] + "'" + ",'" + files[1] + "'" + ",Now()" + ",Now()" + ")"); } else //コメントを更新します { string sql = ""; sql += "Update T_MessageCmt Set"; sql += " UpdateDateTime=Now()"; if (coment != string.Empty) { sql += " And Cmt='" + coment + "'"; msgBox.Add("Cmt"); } if (files[0] != string.Empty) { sql += " And AttachmentsFile1='" + files[0] + "'"; msgBox.Add("File1"); } if (files[1] != string.Empty) { sql += " And AttachmentsFile2='" + files[1] + "'"; msgBox.Add("File2"); } sql += " Where MessageID='" + ViewData["MessageID"] + "'"; sql += " And UserID='" + ViewData["UserID"] + "'"; string showMsg = ""; for (int i = 0; i < msgBox.Count; i++) { if (msgBox[i] == "CompletionStatus") { showMsg += "[完了状況]"; } else if (msgBox[i] == "Cmt") { showMsg += "[コメント]"; } else if (msgBox[i] == "File1") { showMsg += "[添付ファイル:1]"; } else if (msgBox[i] == "File2") { showMsg += "[添付ファイル:2]"; } } showMsg += " を更新しますか?"; if (MessageBoxResult.Yes == MessageBox.Show(showMsg, "", MessageBoxButton.YesNo, MessageBoxImage.Information)) { bool saveFlag; try { mdb.ExecuteSql(sql); saveFlag = true; } catch { MessageBox.Show("保存エラーが発生しました!"); saveFlag = false; } if (saveFlag) { MessageBox.Show("送信が完了しました"); } } else { mdb.Disconnect(); } } } mdb.Disconnect(); }
public List <Message> MessageList(string addsql) { float openRate; bool personalStats; AccessMdb mdb = new AccessMdb(); mdb.Connect(); string sql = ""; sql += " Select "; sql += " m.ID"; sql += " , m.SeqNo"; sql += " , m.ActiveFlag , m.MsgTypeCode"; sql += " , m.DisplayFlag"; sql += " , m.AttributeCode"; sql += " , m.SendUserID"; sql += " , m.Title"; sql += " , m.Context"; sql += " , m.LimitFlag"; sql += " , m.LimitData"; sql += " , m.AttachmentsFile1"; sql += " , m.AttachmentsFile2"; sql += " , m.AttachmentsFile3"; sql += " , m.AttachmentsFile4"; sql += " , m.AttachmentsFile5"; sql += " , m.PostStartDate"; sql += " , m.SendPeople"; sql += " , m.OpenedPeople"; sql += " , m.CreatDateTime"; sql += " , m.UpdateDateTime"; sql += " , m.DisplayFlag"; sql += " , u.UserName as SendUserName"; sql += " , s.UserID , s.OpenedDate , s.LastRefData , s.StatsFlag , s.CompletionDate "; sql += " FROM"; sql += " ( "; sql += " T_MessageList m LEFT JOIN T_USER u ON m.SendUserID = u.UserID "; sql += " ) "; sql += " Left Join T_SendDetails s ON m.ID = s.MessageID AND m.SeqNo = s.SeqNo "; sql += " WHERE m.ActiveFlag = 1 "; sql += " And s.UserID='" + _UserID.Text + "'"; //messagelistのテーブル DataTable msgTable = mdb.ExecuteSql(sql + addsql); //部署のテーブル DataTable msgType = mdb.ExecuteSql("Select * from M_MessageType Order By DisplayNo "); Dictionary <string, string> codeName = new Dictionary <string, string>() { }; for (int i = 0; i < msgType.Rows.Count; i++) { codeName.Add(msgType.Rows[i]["Code"].ToString(), msgType.Rows[i]["Name2"].ToString()); } mdb.Disconnect(); messageList = new List <Message>(); for (int i = 0; i < msgTable.Rows.Count; i++) { string typeCode = msgTable.Rows[i]["MsgTypeCode"].ToString(); Label lb1 = new Label(); lb1.Content = $"[{codeName[typeCode]}]"; lb1.HorizontalContentAlignment = HorizontalAlignment.Center; string attributeCode = msgTable.Rows[i]["AttributeCode"].NullToString(); List <Dictionary <string, string> > attrNames = new List <Dictionary <string, string> >(); if (typeCode != "" || attributeCode != "") { List <string> attributeCodes = attributeCode.Split(',').ToList(); if (msgType != null && msgType.Rows.Count > 0) { for (int j = 0; j < msgType.Rows.Count; j++) { foreach (string attr in attributeCodes) { if (attr == msgType.Rows[j]["Code"].NullToString()) { Dictionary <string, string> item = new Dictionary <string, string>(); item["Name"] = msgType.Rows[j]["Name2"].NullToString(); item["Color"] = msgType.Rows[j]["Color"].NullToString(); attrNames.Add(item); } } } } } List <Label> labels = new List <Label>(); labels.Add(lb1); foreach (Dictionary <string, string> attr in attrNames) { Label lb = new Label(); if (attr["Color"] != "") { lb.Foreground = new SolidColorBrush((Color)ColorConverter.ConvertFromString(attr["Color"])); } lb.Content = $"[{attr["Name"].NullToString()}]"; labels.Add(lb); } Label lb_limitDate = new Label(); if (msgTable.Rows[i]["LimitData"].NullToString() == string.Empty) { lb_limitDate.Content = "未設定"; } else { lb_limitDate.Content = msgTable.Rows[i]["LimitData"].NullToString(); } if (msgTable.Rows[i]["LimitData"].NullToString() != string.Empty && DateTime.Compare(DateTime.Now, DateTime.Parse(lb_limitDate.Content.NullToString())) > 0) { lb_limitDate.Foreground = new SolidColorBrush((Color)ColorConverter.ConvertFromString("#DC143C")); } Label lb_refDt = new Label(); List <Label> lbl_refDt = new List <Label>(); for (int j = 1; j <= 5; j++) { if (msgTable.Rows[i]["AttachmentsFile" + j].ToString() != string.Empty) { lb_refDt = new Label(); lb_refDt.Content = msgTable.Rows[i]["AttachmentsFile" + j].NullToString(); lbl_refDt.Add(lb_refDt); } } //開封率 if ((int)msgTable.Rows[i]["SendPeople"] > 0 && (int)msgTable.Rows[i]["OpenedPeople"] > 0) { float sendPeople_num = int.Parse(msgTable.Rows[i]["SendPeople"].ToString()); float openedPeople_num = int.Parse(msgTable.Rows[i]["OpenedPeople"].ToString()); openRate = openedPeople_num / sendPeople_num * 100; } else { openRate = 0; } if (msgTable.Rows[i]["StatsFlag"].ToString() == "1") { personalStats = true; } else { personalStats = false; } messageList.Add(new Message() { MessageID = msgTable.Rows[i]["ID"].NullToString() + "," + msgTable.Rows[i]["SeqNo"].NullToString() , Type = labels , State = msgTable.Rows[i]["OpenedPeople"].NullToString() + "/" + msgTable.Rows[i]["SendPeople"].NullToString() + "\n" + "(" + openRate + "%)" , SendUserName = msgTable.Rows[i]["SendUserName"].NullToString() , Title = msgTable.Rows[i]["Title"].NullToString() , CreatDateTime = msgTable.Rows[i]["CreatDateTime"].NullToString() , OpenedDateTime = msgTable.Rows[i]["OpenedDate"].NullToString() , Cmt = msgTable.Rows[i]["CreatDateTime"].NullToString() , LimitDateTime = new List <Label>() { lb_limitDate } , LastRefDateTime = msgTable.Rows[i]["LastRefData"].NullToString() , AttachmentsFiles = lbl_refDt , Context = msgTable.Rows[i]["Context"].NullToString() , PersonalStats = personalStats }); } if (messageList.Count == 0) { dg_List.Visibility = Visibility.Hidden; lb_warning.Visibility = Visibility.Visible; } else { dg_List.Visibility = Visibility.Visible; lb_warning.Visibility = Visibility.Hidden; } return(messageList); }
public List <User> UserList() { AccessMdb mdb = new AccessMdb(); mdb.Connect(); DataTable userTable = mdb.ExecuteSql("Select * From T_USER"); DataTable department = mdb.ExecuteSql("Select * From M_Department"); mdb.Disconnect(); Dictionary <string, string> departmentList = new Dictionary <string, string>(); for (int i = 0; i < department.Rows.Count; i++) { departmentList.Add(department.Rows[i]["DeptCode"].ToString(), department.Rows[i]["DeptName"].ToString()); } userList = new List <User>(); for (int i = 0; i < userTable.Rows.Count; i++) { List <Label> lb_DeptNameList = new List <Label>() { }; string[] splitStrDe = userTable.Rows[i]["DepartmentCode"].NullToString().Split(','); if (departmentList.Count != 0) { for (int j = 0; j < splitStrDe.Length; j++) { Label lb_departMentName = new Label(); lb_departMentName.HorizontalAlignment = HorizontalAlignment.Center; lb_departMentName.Content = departmentList[splitStrDe[j]]; lb_DeptNameList.Add(lb_departMentName); } } string authority = userTable.Rows[i]["AuthorityFlag"].ToString(); if (authority == "9") { authority = "管理者"; } else if (authority == "0") { authority = "_"; } userList.Add(new User() { UserID = userTable.Rows[i]["UserID"].NullToString() , UserName = userTable.Rows[i]["UserName"].NullToString() , DepartmentName = lb_DeptNameList , Authority = authority , CreatDateTime = userTable.Rows[i]["CreatDateTime"].NullToString() , UpdateDateTime = userTable.Rows[i]["UpdateDateTime"].NullToString() }); } return(userList); }