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);
}