private void GetReport()
{
if (reportAll != null && reportAll.Count > 0)
{
reportAll.Clear();
}
StringBuilder sql = new StringBuilder("select r.ID, r.UserID, u.Name as UserName, r.ProjectID, p.Name as ProjectName, r.BranchID, b.Name as BranchName, r.RelatedID, r.Content, r.FinishTime, r.Source, r.ToDoID from ((Report r left join [User] u on r.UserID = u.ID) left join Project p on r.ProjectID = p.ID) left join Branch b on r.BranchID = b.ID where 1 = 1");
SqlParams paramDict = new SqlParams();
if (dateTimePickerSearchFrom.Checked)
{
sql.Append(paramDict.AddToWhere("FinishTime", ">=", "FinishTimeStart", DataConvert.ToDateTimeValue(dateTimePickerSearchFrom, 0, 0, 0), "r"));
}
if (dateTimePickerSearchTo.Checked)
{
sql.Append(paramDict.AddToWhere("FinishTime", "<=", "FinishTimeEnd", DataConvert.ToDateTimeValue(dateTimePickerSearchTo, 23, 59, 59), "r"));
}
if (comboBoxSearchProject.SelectedItem is Project project && project.ID != CommonData.ItemAllValue)
{
sql.Append(paramDict.AddToWhere("ProjectID", comboBoxSearchProject.SelectedValue, "r"));
}
if (comboBoxSearchBranch.SelectedItem is Branch branch && branch.ID != CommonData.ItemAllValue)
{
sql.Append(paramDict.AddToWhere("BranchID", comboBoxSearchBranch.SelectedValue, "r"));
}
if (!string.IsNullOrWhiteSpace(textBoxSearchRelatedID.Text))
{
sql.Append(paramDict.AddToWhere("RelatedID", textBoxSearchRelatedID, "r"));
}
if (!string.IsNullOrWhiteSpace(textBoxKeyWord.Text))
{
sql.Append(paramDict.AddLikeToWhere("Content", textBoxKeyWord, "r"));
}
sql.Append(" order by r.FinishTime, r.ID");
if (reportAll == null)
{
reportAll = new List <Report>();
}
else if (reportAll.Count > 0)
{
reportAll.Clear();
}
reportAll.Clear();
DataTable dt = CommonData.AccessHelper.GetDataTable(sql.ToString(), paramDict);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
Report report = new Report
{
ID = DataConvert.ToDecimal(row["ID"]),
User = new User()
{
ID = DataConvert.ToInt(row["UserID"]),
Name = DataConvert.ToString(row["UserName"])
},
Project = new Project()
{
ID = DataConvert.ToInt(row["ProjectID"]),
Name = DataConvert.ToString(row["ProjectName"])
},
Branch = new Branch()
{
ID = DataConvert.ToInt(row["BranchID"]),
Name = DataConvert.ToString(row["BranchName"])
},
RelatedID = DataConvert.ToString(row["RelatedID"]),
Content = DataConvert.ToString(row["Content"]),
FinishTime = DataConvert.ToDateTime(row["FinishTime"]),
Source = (EnumReportSource?)DataConvert.ToEnum <EnumReportSource>(row["Source"]),
ToDoID = DataConvert.ToNullableDecimal(row["ToDoID"])
};
reportAll.Add(report);
}
}
BindReport();
}
private void GetToDoList()
{
if (toDoListAll != null && toDoListAll.Count > 0)
{
toDoListAll.Clear();
}
StringBuilder sql = new StringBuilder("select t.ID, t.ProjectID, p.Name as ProjectName, t.BranchID, b.Name as BranchName, t.RelatedID, t.Priority, t.Severity, t.Title, t.Content, t.[Memo], t.UserID, u.Name as UserName, t.PlannedStartTime, t.PlannedEndTime, t.PlannedHours, t.PlannedDays, t.Status, t.FinishTime, t.FinishUserID, uf.Name as FinishUserName from (((ToDo t left join Project p on t.ProjectID = p.ID) left join Branch b on t.BranchID = b.ID) left join [User] u on t.UserID = u.ID) left join [User] uf on t.FinishUserID = uf.ID where 1 = 1");
SqlParams paramDict = new SqlParams();
if (comboBoxSearchProject.SelectedItem is Project project && project.ID != CommonData.ItemAllValue)
{
sql.Append(paramDict.AddToWhere("ProjectID", project.ID, "t"));
}
if (comboBoxSearchBranch.SelectedItem is Branch branch && branch.ID != CommonData.ItemAllValue)
{
sql.Append(paramDict.AddToWhere("BranchID", branch.ID, "t"));
}
if (!string.IsNullOrWhiteSpace(textBoxSearchTitle.Text.Trim()))
{
sql.Append(paramDict.AddLikeToWhere("Title", textBoxSearchTitle, "t"));
}
if (!string.IsNullOrWhiteSpace(textBoxSearchContent.Text.Trim()))
{
sql.Append(paramDict.AddLikeToWhere("Content", textBoxSearchContent, "t"));
}
if (textBoxSearchRelatedID.Text.NotNullOrWhiteSpace())
{
sql.Append(paramDict.AddToWhere("RelatedID", textBoxSearchRelatedID, "t"));
}
if (comboBoxSearchStatus.SelectedItem is ToDoStatus status && status.ID != CommonData.ItemAllValue)
{
if (status.ID == CommonData.toDoStatusNotDoneID)
{
List <object> paramValueList = new List <object>()
{
(int)EnumToDoStatus.Planning, (int)EnumToDoStatus.Working
};
sql.Append(paramDict.AddToWhere("Status", paramValueList, "t"));
}
else
{
sql.Append(paramDict.AddToWhere("Status", (int)status.Status, "t"));
}
}
if (dateTimePickerSearchFisnishTimeFrom.Checked)
{
sql.Append(paramDict.AddToWhere("FinishTime", ">=", "FinishTimeStart", DataConvert.ToDateTimeValue(dateTimePickerSearchFisnishTimeFrom, 0, 0), "t"));
}
if (dateTimePickerSearchFinishTimeTo.Checked)
{
sql.Append(paramDict.AddToWhere("FinishTime", "<=", "FinishTimeEnd", DataConvert.ToDateTimeValue(dateTimePickerSearchFinishTimeTo, 59, 59), "t"));
}
if (comboBoxSearchFinishUser.SelectedItem is User user && user.ID != CommonData.ItemAllValue)
{
sql.Append(paramDict.AddToWhere("FinishUserID", user.ID, "t"));
}
sql.Append(" order by t.FinishTime desc");
if (toDoListAll == null)
{
toDoListAll = new List <ToDo>();
}
else if (toDoListAll.Count > 0)
{
toDoListAll.Clear();
}
toDoListAll.Clear();
DataTable dt = CommonData.AccessHelper.GetDataTable(sql.ToString(), paramDict);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
ToDo toDo = new ToDo
{
ID = DataConvert.ToDecimal(row["ID"]),
//Project = new Project()
//{
// ID = DataConvert.ToInt(row["ProjectID"]),
// Name = DataConvert.ToString(row["ProjectName"])
//},
RelatedID = DataConvert.ToString(row["RelatedID"]),
Priority = (EnumToDoPriority?)DataConvert.ToEnum <EnumToDoPriority>(row["Priority"]),
Severity = (EnumToDoSeverity?)DataConvert.ToEnum <EnumToDoSeverity>(row["Severity"]),
Title = DataConvert.ToString(row["Title"]),
Content = DataConvert.ToString(row["Content"]),
Memo = DataConvert.ToString(row["Memo"]),
//User = new User()
//{
// ID = DataConvert.ToInt(row["UserID"]),
// Name = DataConvert.ToString(row["UserName"])
//},
PlannedStartTime = DataConvert.ToNullableDateTime(row["PlannedStartTime"]),
PlannedEndTime = DataConvert.ToNullableDateTime(row["PlannedEndTime"]),
PlannedHours = DataConvert.ToNullableDecimal(row["PlannedHours"]),
PlannedDays = DataConvert.ToNullableDecimal(row["PlannedDays"]),
Status = (EnumToDoStatus?)DataConvert.ToEnum <EnumToDoStatus>(row["Status"]),
FinishTime = DataConvert.ToNullableDateTime(row["FinishTime"]),
//FinishUser = new User()
//{
// ID = DataConvert.ToInt(row["FinishUserID"]),
// Name = DataConvert.ToString(row["FinishUserName"])
//}
};
int projectID = DataConvert.ToInt(row["ProjectID"]);
string projectName = DataConvert.ToString(row["ProjectName"]);
if (projectID > 0 || !string.IsNullOrWhiteSpace(projectName))
{
toDo.Project = new Project()
{
ID = projectID, Name = projectName
}
}
;
int branchID = DataConvert.ToInt(row["BranchID"]);
string branchName = DataConvert.ToString(row["BranchName"]);
if (branchID > 0 || !string.IsNullOrWhiteSpace(branchName))
{
toDo.Branch = new Branch()
{
ID = branchID, Name = branchName
}
}
; //, Project = toDo.Project
int userID = DataConvert.ToInt(row["UserID"]);
string userName = DataConvert.ToString(row["UserName"]);
if (userID > 0 || !string.IsNullOrWhiteSpace(userName))
{
toDo.User = new User()
{
ID = userID, Name = userName
}
}
;
int finishUserID = DataConvert.ToInt(row["FinishUserID"]);
string finishUserName = DataConvert.ToString(row["FinishUserName"]);
if (finishUserID > 0 || !string.IsNullOrWhiteSpace(finishUserName))
{
toDo.FinishUser = new User()
{
ID = finishUserID, Name = finishUserName
}
}
;
toDoListAll.Add(toDo);
}
}
BindToDoList();
}
