private void buttonSave_Click(object sender, EventArgs e)
{
if (isAdding)
{
SqlParams paramDict = new SqlParams();
paramDict.Add("Content", richTextBoxContent.Text);
paramDict.Add("Status", 0);
decimal id = CommonData.AccessHelper.InsertAndReturnNewIdentity("Reminder", paramDict);
isAdding = false;
originalContent = richTextBoxContent.Text;
int index = reminderDict.Count;
Reminder reminder = new Reminder()
{
ID = id, Content = richTextBoxContent.Text, Status = 0
};
reminderDict.Add(index, reminder);
richTextBoxContent.Tag = reminder;
currentIndex = index;
}
else
{
if (richTextBoxContent.Text == originalContent)
{
return;
}
if (richTextBoxContent.Tag == null || !(richTextBoxContent.Tag is Reminder reminder) || reminder == null || reminder.ID <= 0)
{
MessageBox.Show("数据错误。", "提示");
return;
}
//decimal id;
//if (!decimal.TryParse(richTextBoxContent.Tag.ToString(), out id))
//{
// MessageBox.Show("数据错误，ID错误。", "提示");
// return;
//}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("Content", richTextBoxContent.Text);
SqlParams whereParamDict = new SqlParams();
//whereParamDict.Add("ID", id);
whereParamDict.Add("ID", reminder.ID);
CommonData.AccessHelper.Update("Reminder", setParamDict, whereParamDict);
originalContent = richTextBoxContent.Text;
//KeyValuePair<int, Reminder> pair = reminderDict.FirstOrDefault(r => r.Value.ID == id);
KeyValuePair <int, Reminder> pair = reminderDict.FirstOrDefault(r => r.Value.ID == reminder.ID);
if (pair.Value != null)
{
pair.Value.Content = richTextBoxContent.Text;
}
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (!CheckDataType())
{
return;
}
if (string.IsNullOrWhiteSpace(textBoxItemName.Text))
{
MessageBox.Show("名称不能为空", "提示");
return;
}
string table = string.Empty;
SqlParams paramDict = new SqlParams();
if (currentDataType == CurrentDataType.User)
{
table = "[User]";
paramDict.Add("Name", textBoxItemName.Text.Trim());
}
else if (currentDataType == CurrentDataType.Project)
{
table = "Project";
paramDict.Add("Name", textBoxItemName.Text.Trim());
}
else if (currentDataType == CurrentDataType.Branch)
{
int projectID;
if (comboBoxProject.SelectedItem is Project project && project.ID != CommonData.ItemAllValue)
{
projectID = project.ID;
}
else
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (BranchExists(projectID, textBoxItemName.Text.Trim()))
{
MessageBox.Show("项目中已存在同名分支", "提示");
return;
}
table = "Branch";
paramDict.Add("Name", textBoxItemName.Text.Trim());
paramDict.Add("[Memo]", textBoxMemo.Text.Trim());
paramDict.Add("ProjectID", projectID);
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (!(comboBoxOperateProject.SelectedItem is Project project) || project.ID <= 0)
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (string.IsNullOrWhiteSpace(richTextBoxOperateContent.Text))
{
MessageBox.Show("内容不能为空", "提示");
return;
}
SqlParams paramDict = new SqlParams();
paramDict.Add("UserID", CommonData.CurrentUser.ID);
paramDict.Add("ProjectID", project.ID);
paramDict.Add("Content", richTextBoxOperateContent.Text.Trim());
paramDict.Add("Source", (int)EnumReportSource.Manual);
if (dateTimePickerOperateFinishTime.Checked && dateTimePickerOperateFinishTime.Value > DateTime.MinValue)
{
paramDict.Add("FinishTime", dateTimePickerOperateFinishTime.Value);
}
if (comboBoxOperateBranch.SelectedItem is Branch branch && branch.ID != CommonData.ItemAllValue)
{
paramDict.Add("BranchID", comboBoxOperateBranch.SelectedValue);
}
if (!string.IsNullOrWhiteSpace(textBoxOperateRelatedID.Text))
{
paramDict.Add("RelatedID", textBoxOperateRelatedID.Text.Trim());
}
CommonData.AccessHelper.Insert("Report", paramDict);
richTextBoxOperateContent.Text = string.Empty;
ShowMessageAskRefresh();
}
/// <summary>
/// 获取Sql参数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static SqlParameter[] GetSqlParams(CModel model)
{
foreach (var field in model.Fields)
{
SqlParams.Add(new SqlParameter($"@{field}", model[field]));
}
return(SqlParams.ToArray());
}
private bool BranchExists(int projectID, string branchName)
{
string sql = "select count(*) from Branch where ProjectID = @ProjectID and Name = @Name";
SqlParams paramDict = new SqlParams();
paramDict.Add("ProjectID", projectID);
paramDict.Add("Name", branchName);
int i = -1;
if (int.TryParse(CommonData.AccessHelper.ExecuteScalar(sql, paramDict).ToString(), out i) && i > 0)
{
return(true);
}
else
{
return(false);
}
}
public async Task <PagedResult <BookDto> > GetAsync(int page, int size, string keyword = null, long?tagId = null)
{
var param = new SqlParams();
var sql_ = Db.NewSql().From("Book").Where();
if (!string.IsNullOrEmpty(keyword))
{
sql_.Line("AND (Title LIKE @pattern OR Subtitle LIKE @pattern OR Author LIKE @pattern)");
param.Add("pattern", $"%{keyword}%");
}
if (tagId.HasValue)
{
sql_.Line("AND EXISTS (SELECT 1 FROM BookTag WHERE BookId=Book.Id AND TagId=@tagId)");
param.Add("tagId", tagId.Value);
}
var result = new PagedResult <BookDto>(page, size)
{
Total = await Db.SingleAsync <int>(sql_.Select("COUNT(1)"), param),
Items = await Db.QueryAsync <BookDto>(
sql_.Select("*").Order("Id DESC").Page(page, size), param),
};
sql_.Clear()
.Line("SELECT * FROM BookTag BT")
.Line("LEFT JOIN Tag T ON T.Id=BT.TagId")
.Line("WHERE BT.BookId IN @bookIds");
var bookTags = await Db.QueryAsync <BookTag, Tag, BookTag>(sql_,
(bookTag, tag) =>
{
bookTag.Tag = tag;
return(bookTag);
},
new { bookIds = result.Items.Select(x => x.Id) });
foreach (var book in result.Items)
{
book.Tags = bookTags.Where(x => x.BookId == book.Id).Select(x => x.Tag).ToList();
}
return(result);
}
private void buttonDelete_Click(object sender, EventArgs e)
{
if (isAdding)
{
BindReminder(-1);
}
else
{
if (richTextBoxContent.Tag == null || !(richTextBoxContent.Tag is Reminder reminder) || reminder == null || reminder.ID <= 0)
{
MessageBox.Show("数据错误。", "提示");
return;
}
//decimal id;
//if (!decimal.TryParse(richTextBoxContent.Tag.ToString(), out id))
//{
// MessageBox.Show("数据错误，ID错误。", "提示");
// return;
//}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("Status", 1);
SqlParams whereParamDict = new SqlParams();
//whereParamDict.Add("ID", id);
whereParamDict.Add("ID", reminder.ID);
CommonData.AccessHelper.Update("Reminder", setParamDict, whereParamDict);
reminderDict.Remove(currentIndex);
Dictionary <int, Reminder> newDict = new Dictionary <int, Reminder>();
foreach (KeyValuePair <int, Reminder> pair in reminderDict)
{
if (pair.Key >= currentIndex)
{
newDict.Add(pair.Key - 1, pair.Value);
}
else
{
newDict.Add(pair.Key, pair.Value);
}
}
reminderDict.Clear();
reminderDict = newDict;
if (currentIndex == 0)
{
currentIndex = reminderDict.Count - 1;
}
else
{
currentIndex--;
}
BindReminder(currentIndex);
if (reminder.ToDo != null && reminder.ToDo.ID > 0)
{
MessageBox.Show("如有需要，请手动在待办事项程序中修改状态。", "提示");
}
}
}
public List <StatusSummary> Execute(int companyId)
{
this.SqlParams.Clear();
SqlParams.Add("@intCid", companyId);
DataTable dt = base.Execute();
IEnumerable <StatusSummary> summary = base.MapData(dt, typeof(StatusSummary)).Cast <StatusSummary>();
List <StatusSummary> summaryList = summary.ToList();
return(summaryList);
}
public override Params ExecuteStoredProcedure(string strProcedureName, params object[] cmdParams)
{
Params params2;
Params @params = new SqlParams();
SqlConnection objConnection = (SqlConnection)base.objConnection;
try
{
SqlCommand command = new SqlCommand(strProcedureName, objConnection)
{
CommandType = CommandType.StoredProcedure
};
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
SqlCommandBuilder.DeriveParameters(command);
for (int i = 1; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Direction == ParameterDirection.Input)
{
if (cmdParams.Length <= (i - 1))
{
throw new Exception("Parameter Count does not match.");
}
command.Parameters[i].Value = cmdParams[i - 1];
}
else
{
command.Parameters[i].Value = DBNull.Value;
}
}
command.ExecuteNonQuery();
foreach (SqlParameter parameter in command.Parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Output))
{
@params.Add(parameter);
}
}
params2 = @params;
}
catch (Exception exception)
{
throw exception;
}
finally
{
this.CloseConnection();
}
return(params2);
}
private void buttonEdit_Click(object sender, EventArgs e)
{
if (dataGridViewShow.SelectedRows.Count != 1)
{
MessageBox.Show("选中一行后再修改", "提示");
return;
}
if (!(dataGridViewShow.SelectedRows[0].Tag is Report report) || report.ID <= 0)
{
MessageBox.Show("报告数据错误", "提示");
return;
}
if (!(comboBoxOperateProject.SelectedItem is Project project) || project.ID <= 0)
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (string.IsNullOrWhiteSpace(richTextBoxOperateContent.Text))
{
MessageBox.Show("内容不能为空", "提示");
return;
}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("ProjectID", project.ID);
setParamDict.Add("RelatedID", string.IsNullOrWhiteSpace(textBoxOperateRelatedID.Text) ? null : textBoxOperateRelatedID.Text.Trim());
setParamDict.Add("Content", richTextBoxOperateContent.Text.Trim());
SqlParams whereParamDict = new SqlParams();
if (dateTimePickerOperateFinishTime.Checked && dateTimePickerOperateFinishTime.Value > DateTime.MinValue)
{
setParamDict.Add("FinishTime", dateTimePickerOperateFinishTime.Value);
}
if (comboBoxOperateBranch.SelectedItem is Branch branch)
{
if (branch.ID == CommonData.ItemAllValue)
{
setParamDict.Add("BranchID", null);
}
else
{
setParamDict.Add("BranchID", comboBoxOperateBranch.SelectedValue);
}
}
whereParamDict.Add("ID", report.ID);
CommonData.AccessHelper.Update("Report", setParamDict, whereParamDict);
//MessageBox.Show("修改完成", "提示");
ShowMessageAskRefresh();
}
/// <summary>
/// 返回删除语句
/// </summary>
/// <param name="model">实体</param>
/// <returns></returns>
public static string GetDelete(CModel model)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append($" DELETE FROM {model.TableName} ");
sql.Append($" WHERE {model.KeyName}=@{model.KeyName}");
SqlParams.Add(new SqlParameter($"@{model.KeyName}", model.KeyValue));
InitSql = sql.ToString();
return(sql.ToString());
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (!(comboBoxProject.SelectedItem is Project project) || project.ID <= 0)
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (string.IsNullOrWhiteSpace(richTextBoxContent.Text))
{
MessageBox.Show("内容不能为空", "提示");
return;
}
SqlParams paramDict = new SqlParams();
paramDict.Add("UserID", CommonData.CurrentUser.ID);
paramDict.Add("ProjectID", project.ID);
paramDict.Add("Content", richTextBoxContent.Text.Trim());
paramDict.Add("Source", (int)EnumReportSource.Todo);
paramDict.Add("ToDoID", toDo.ID);
if (dateTimePickerFinishTime.Checked && dateTimePickerFinishTime.Value > DateTime.MinValue)
{
paramDict.Add("FinishTime", DataConvert.ToDateTimeValue(dateTimePickerFinishTime));
}
if (comboBoxBranch.SelectedItem is Branch branch && branch.ID != CommonData.ItemAllValue)
{
paramDict.Add("BranchID", branch.ID);
}
if (textBoxRelatedID.Text.NotNullOrWhiteSpace())
{
paramDict.Add("RelatedID", textBoxRelatedID.Text.ToString());
}
int i = CommonData.AccessHelper.Insert("Report", paramDict);
//避免新增失败后用户不知道，此处进行提醒
if (i > 0)
{
this.Close();
}
else
{
MessageBox.Show("新增失败", "提示");
}
}
public async Task <PagedResult <UserDto> > GetAsync(int page, int size, string keyword = null)
{
var param = new SqlParams();
var sql = Db.NewSql().From("AnetUser").Where();
if (!string.IsNullOrEmpty(keyword))
{
sql.Line("AND Name=@Name");
param.Add("Name", $"'%{keyword}%'");
}
var result = new PagedResult <UserDto>(page, size)
{
Total = await Db.SingleAsync <int>(sql.Select("COUNT(1)"), param),
Items = await Db.QueryAsync <UserDto>(sql.Select("*").Line("ORDER BY Id").Page(page, size), param),
};
return(result);
}
public async Task <IEnumerable <TagDto> > GetAsync(string keyword = null)
{
var param = new SqlParams();
var sql = Db.NewSql()
.Line("SELECT T.*, COUNT(BT.BookId) BookCount")
.Line("FROM Tag T")
.Line("LEFT JOIN BookTag BT ON T.Id=BT.TagId")
.Line("WHERE 1=1");
if (!string.IsNullOrEmpty(keyword))
{
sql.LineTab("AND (Name LIKE @pattern) ");
param.Add("pattern", $"%{keyword}%");
}
sql.Line("GROUP BY T.Id");
var list = await Db.QueryAsync <TagDto>(sql, param);
return(list);
}
/// <summary>
/// 返回插入语句
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static string GetInsert(CModel model)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {model.TableName} ");
int i = 0;
StringBuilder str_params = new StringBuilder();
StringBuilder param_values = new StringBuilder();
foreach (var field in model.Fields)
{
if (field == model.KeyName) //不更新主键
{
i++;
continue;
}
if (i++ > 1)
{
str_params.Append(",");
param_values.Append(",");
}
str_params.Append($"{field}");
param_values.Append($"@{field}");
SqlParams.Add(new SqlParameter($"@{field}", model[field]));
}
sql.Append($"({str_params.ToString()}) VALUES ({param_values.ToString()})");
InitSql = sql.ToString();
return(sql.ToString());
}
/// <summary>
/// 返回查询语句
/// </summary>
/// <param name="type"></param>
/// <param name="key"></param>
/// <returns></returns>
public static string GetSelect(Type type, object key)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
int i = 0;
PropertyInfo[] fields = type.GetProperties();
foreach (var field in fields)
{
sql.Append(field.Name);
if (i++ < fields.Length - 1)
{
sql.Append(",");
}
}
sql.Append(" FROM ");
sql.Append(type.Name);
string keyName = new CModel().GetKeyName(fields);
if (!string.IsNullOrWhiteSpace(keyName))
{
sql.Append($" WHERE {keyName}=@{keyName}");
SqlParams.Add(new SqlParameter($"@{keyName}", key));
}
return(sql.ToString());
}
/// <summary>
/// 返回更新语句
/// </summary>
/// <returns></returns>
public static string GetUpdate(CModel model)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append($" UPDATE {model.TableName} SET ");
int i = 0;
foreach (var field in model.Fields)
{
if (field == model.KeyName) //不更新主键
{
i++;
SqlParams.Add(new SqlParameter($"@{field}", model[field]));
continue;
}
if (i++ > 1)
{
sql.Append(",");
}
sql.Append($"{field}=@{field}");
SqlParams.Add(new SqlParameter($"@{field}", model[field]));
}
sql.Append($" WHERE {model.KeyName}=@{model.KeyName}");
InitSql = sql.ToString();
return(sql.ToString());
}
private void buttonRelateToToDo_Click(object sender, EventArgs e)
{
if (richTextBoxOperateContent.Tag == null || !(richTextBoxOperateContent.Tag is Report report) || report == null || report.ID == CommonData.ItemNullValue || report.ID == CommonData.ItemAllValue)
{
MessageBox.Show("没有选择报告或报告数据错误", "提示");
return;
}
int projectID = CommonData.ItemAllValue, branchID = CommonData.ItemAllValue;
if (report.ToDoID.HasValue && report.ToDoID.Value != CommonData.ItemNullValue && report.ToDoID.Value != CommonData.ItemAllValue)
{
string sql = "select Title from ToDo where ID = @ID";
SqlParams paramDict = new SqlParams();
paramDict.Add("ID", report.ToDoID.Value);
object result = CommonData.AccessHelper.ExecuteScalar(sql, paramDict);
if (result != null)
{
if (DialogResult.No == MessageBox.Show("已关联【" + result.ToString() + "】，是否重新关联？", "提示", MessageBoxButtons.YesNo))
{
return;
}
}
}
if (comboBoxOperateProject.SelectedValue != null)
{
int.TryParse(comboBoxOperateProject.SelectedValue.ToString(), out projectID);
}
if (comboBoxOperateBranch.SelectedValue != null)
{
int.TryParse(comboBoxOperateBranch.SelectedValue.ToString(), out branchID);
}
RelateToToDo relate = new RelateToToDo(projectID, branchID, report);
relate.ShowDialog();
}
/// <summary>
/// 返回查询语句
/// </summary>
/// <param name="model"></param>
/// <param name="key"></param>
/// <returns></returns>
public static string GetSelect(CModel model, object key)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
int i = 0;
foreach (var field in model.Fields)
{
sql.Append(field);
if (i++ < model.Fields.Count - 1)
{
sql.Append(",");
}
}
sql.Append(" FROM ");
sql.Append(model.TableName);
string keyName = model.KeyName;
if (!string.IsNullOrWhiteSpace(keyName))
{
sql.Append($" WHERE {keyName}=@{keyName}");
SqlParams.Add(new SqlParameter($"@{keyName}", key));
}
return(sql.ToString());
}
