private void BtnFailed_Click(object sender, EventArgs e)
{
cost_main Main = new cost_main
{
id = costId,
status = 3
};
List <cost_approval> ListApproval = new List <cost_approval>();
cost_approval approval = new cost_approval
{
cost_id = costId,
approval_id = UserInfoBLL.UserId,
result = false,
time = DateTime.Now,
opinion = TexOpinion.Text
};
ListApproval.Add(approval);
Result res = new CostApprovalBLL().Update(new cost
{
Main = Main,
ApprovalList = ListApproval
});
MessageBox.Show(res.Message, "操作结果提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
/// <summary>
/// 更新费用单信息
/// </summary>
/// <param name="cost">费用单主表对象 Main：id、apply_money、remark
/// 费用单审批详情列表 ApprovalList:
/// 费用详情列表 DetailList：cost_type、money</param>
/// <returns>数据表受影响的行数</returns>
public int Update(cost cost)
{
cost_main Main = cost.Main;
List <cost_detail> DetailList = cost.DetailList;
//构造sql语句数组
string[] sqlArray = new string[2 + DetailList.Count];
//先更新cost_main表
//更新费用金额信息和状态
sqlArray[0] = "update cost_main set " +
nameof(cost_main.apply_money) + "=" + Main.apply_money + "," +
nameof(cost_main.remark) + "='" + Main.remark +
"' where id='" + Main.id + "'";
//再删除cost_detail表数据
sqlArray[1] = "delete from cost_detail where " + nameof(cost_detail.cost_id) + "='" + Main.id + "'";
//再插入cost_detail表数据
int count = 1;
foreach (cost_detail detail in DetailList)
{
detail.cost_id = Main.id;
sqlArray[count + 1] = ConditionsToSql <cost_detail> .InsertSql(detail);
count++;
}
//调用方法以事务方式执行sql数组里的语句
return(sqlArrayToTran.doTran(sqlArray));
}
/// <summary>
/// 添加费用单
/// </summary>
/// <param name="cost">费用单主表对象 Main：apply_id、apply_money、status、apply_time、remark
/// 费用单审批详情列表 ApprovalList:approval_id
/// 费用详情列表 DetailList：cost_type、money</param>
/// <returns>数据表受影响的行数</returns>
public int Add(cost cost)
{
cost_main Main = cost.Main;
List <cost_detail> DetailList = cost.DetailList;
//先构造所有的sql语句
string[] sqlArray = new string[2 + DetailList.Count];
//构造插入cost_main、cost_approval表的语句
int timeStamp = TimeTools.Timestamp();
Main.id = timeStamp;//主键(费用单id)是时间戳
sqlArray[0] = ConditionsToSql <cost_main> .InsertSql(Main);
//插入cost_approval表时，鉴于审核逻辑的关系，最多一次插入一个
cost_approval Approval = cost.ApprovalList.First();
Approval.cost_id = timeStamp;
sqlArray[1] = ConditionsToSql <cost_approval> .InsertSql(Approval);
//构造插入cost_detail表语句
int count = 1;
foreach (cost_detail detail in DetailList)
{
detail.cost_id = timeStamp;
sqlArray[count + 1] = ConditionsToSql <cost_detail> .InsertSql(detail);
count++;
}
//调用方法以事务方式执行sql数组里的语句
return(sqlArrayToTran.doTran(sqlArray));
}
public void DBTestMethod()
{
int costId = TimeTools.Timestamp();
List <cost_detail> details = new List <cost_detail>
{
new cost_detail {
cost_id = costId, cost_type = "出差", money = 1213
},
new cost_detail {
cost_id = costId, cost_type = "住宿", money = 2324
}
};
cost_main costMain = new cost_main
{
applicant = "test",
apply_money = 1213.12M,
apply_time = DateTime.Now,
approver = "test",
id = costId,
cost_detail = details,
status = 1,
approval_time = DateTime.Now,
approval_money = 131
};
CostApply costApply = new CostApply();
int a = costApply.InsertBill(costMain);
Console.WriteLine(a);
}
private void BtnCostApply_Click(object sender, EventArgs e)
{
if (CmbApprover.SelectedItem == null)
{
MessageBox.Show("请选择一个审批人负责您的费用申请", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
CostApplyBLL costApplyBLL = new CostApplyBLL();
List <cost_detail> listDetail = new List <cost_detail>();
decimal applyMoney = 0;
foreach (DataGridViewRow row in this.DgvCostDetail.Rows)
{
if (row.Cells[0].Value == null)
{
continue;
}
int type = int.Parse((((string)row.Cells[0].Value).Split('.')[0]));
decimal money = decimal.Parse((string)row.Cells[1].Value);
listDetail.Add(new cost_detail
{
cost_type_id = type,
money = money
});
applyMoney += money;
}
cost_main main = new cost_main
{
apply_money = applyMoney,
apply_time = DateTime.Now,
apply_id = UserInfoBLL.UserId,
status = 0,
remark = TexRemark.Text
};
List <cost_approval> ListApproval = new List <cost_approval>
{
new cost_approval
{
approval_id = int.Parse(CmbApprover.SelectedItem.ToString().Split('.')[0])
}
};
cost cost = new cost
{
Main = main,
DetailList = listDetail,
ApprovalList = ListApproval
};
Result res = costApplyBLL.Add(cost);
DialogResult dialogResult = MessageBox.Show(res.Message, "添加费用申请单状态提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (dialogResult == DialogResult.OK)
{
if (res.Code == RES.OK)
{
this.DgvCostDetail.Rows.Clear();
this.Close();
}
}
}
private void BtnPass_Click(object sender, EventArgs e)
{
if (CmbNextApprover.Visible && CmbNextApprover.SelectedItem == null)
{
MessageBox.Show("请选择一个上级审批人！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
cost_main Main = new cost_main
{
id = costId,
status = (byte)(CmbNextApprover.Visible ? 1 : 2)
};
List <cost_approval> ListApproval = new List <cost_approval>();
cost_approval approval = new cost_approval
{
cost_id = costId,
approval_id = UserInfoBLL.UserId,
result = true,
time = DateTime.Now,
opinion = TexOpinion.Text
};
ListApproval.Add(approval);
if (CmbNextApprover.Visible)
{
ListApproval.Add(new cost_approval
{
cost_id = costId,
approval_id = int.Parse(CmbNextApprover.SelectedItem.ToString().Split('.')[0])
});
}
Result res = new CostApprovalBLL().Update(new cost
{
Main = Main,
ApprovalList = ListApproval
});
MessageBox.Show(res.Message, "操作结果提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
private void CostApprovalForm_Load(object sender, EventArgs e)
{
cost cost = new CostApplyBLL().Query(new Dictionary <string, object>
{
{ "id", costId }
}).First();
cost_main main = cost.Main;
view_sys_u2g applicant = new SysUserDAL().SelectById(main.apply_id).First();
TexApplicant.Text = applicant.name;
foreach (cost_detail detail in cost.DetailList)
{
int index = this.DgvCostDetail.Rows.Add();
this.DgvCostDetail.Rows[index].SetValues(new CostApplyDAL().GetCostTypeById(detail.cost_type_id), detail.money);
}
foreach (cost_approval approval in cost.ApprovalList)
{
int index = this.DgvApproval.Rows.Add();
string approver = approval.approval_id + "." + new SysUserDAL().SelectById(approval.approval_id).First().name;
this.DgvApproval.Rows[index].SetValues(approver, approval.result, approval.time, approval.opinion);
}
int userId = UserInfoBLL.UserId;
List <string> approverList = new CostApplyDAL().GetApprovalInfo(userId);
if (approverList.Count == 0)
{
LblNextApprover.Visible = false;
CmbNextApprover.Visible = false;
return;
}
else
{
LblNextApprover.Visible = true;
CmbNextApprover.Visible = true;
}
CmbNextApprover.Items.Clear();
CmbNextApprover.Items.AddRange(approverList.ToArray());
}
/// <summary>
/// 更新费用单信息
/// </summary>
/// <param name="cost">费用单主表对象 Main：id、apply_money、remark
/// 费用单审批详情列表 ApprovalList:
/// 费用详情列表 DetailList：cost_type、cost_type_name、money</param>
/// <returns>更新是否成功</returns>
public Result Update(cost cost)
{
cost_main main = cost.Main;
List <cost_detail> listDeatil = cost.DetailList;
Result res = new Result()
{
Code = RES.ERROR,
Message = "更新失败！"
};
if (main == null || listDeatil == null || listDeatil.Count == 0)
{
return(res);
}
CostApplyDAL apply = new CostApplyDAL();
//获取该费用单的审批状态
byte status = apply.QueryMain(new Dictionary <string, object>
{
{ "id", main.id }
}).First().status;
//如果费用单不是未审批状态，则更新信息失败
if (status != 0)
{
return(res);
}
//先获取未更新时费用详情记录数
int originDetailCount = apply.QueryDetail(main.id).Count;
//再更新费用单
int rows = apply.Update(cost);
if (rows == 1 + originDetailCount + listDeatil.Count)
{
res.Code = RES.OK;
res.Message = "更新成功！";
}
return(res);
}
/// <summary>
/// 添加费用单
/// </summary>
/// <param name="cost">费用单主表对象 Main：apply_id、apply_money、status、apply_time、remark
/// 费用单审批详情列表 ApprovalList:approval_id
/// 费用详情列表 DetailList：cost_type、money</param>
/// <returns>添加是否成功</returns>
public Result Add(cost cost)
{
cost_main Main = cost.Main;
List <cost_detail> DetailList = cost.DetailList;
Result res = new Result()
{
Code = RES.ERROR,
Message = "添加失败！"
};
if (Main == null || DetailList == null || DetailList.Count == 0)
{
return(res);
}
int rows = new CostApplyDAL().Add(cost);
if (rows == 2 + DetailList.Count)
{
res.Code = RES.OK;
res.Message = "添加成功！";
}
return(res);
}
private void CostApplyDetailForm_Load(object sender, EventArgs e)
{
cost cost = new CostApplyBLL().Query(new Dictionary <string, object>
{
{ "id", costId }
}).First();
cost_main main = cost.Main;
LblCostId.Text = main.id.ToString();
view_sys_u2g applicant = new SysUserDAL().SelectById(main.apply_id).First();
LblApplicant.Text = applicant.name;
LblApplicantOrg.Text = applicant.org_name;
LblApplyTime.Text = main.apply_time.ToString("yyyy-MM-dd HH:mm:ss");
LblApplyMoney.Text = main.apply_money.ToString();
foreach (cost_detail detail in cost.DetailList)
{
int index = this.DgvCostDetail.Rows.Add();
this.DgvCostDetail.Rows[index].SetValues(new CostApplyDAL().GetCostTypeById(detail.cost_type_id), detail.money);
}
switch (main.status)
{
case 0: LblStatus.Text = "未审核"; break;
case 1: LblStatus.Text = "正在审核"; break;
case 2: LblStatus.Text = "审核通过"; break;
case 3: LblStatus.Text = "审核驳回"; break;
}
foreach (cost_approval approval in cost.ApprovalList)
{
int index = this.DgvApproval.Rows.Add();
string approver = approval.approval_id + "." + new SysUserDAL().SelectById(approval.approval_id).First().name;
this.DgvApproval.Rows[index].SetValues(approver, approval.result, approval.time, approval.opinion);
}
}
/// <summary>
/// 根据组合条件查询费用单（可分页）
/// </summary>
/// <param name="conditions">条件键值对key: "id", "apply_id", "status", "start_time", "end_time","page","limit"</param>
/// <returns>费用单列表</returns>
public List <cost_main> QueryMain(Dictionary <string, object> conditions)
{
//对组合条件参数进行合法性检验，获取合法的查询参数列表
string[] keys = new string[] { "id", "apply_id", "status", "start_time", "end_time" };
List <string> keyList = new List <string>();
foreach (string key in conditions.Keys)
{
if (keys.Contains(key))
{
keyList.Add(key);
}
}
//根据参数列表，拼接组合条件sql语句
string sql = "select ROW_NUMBER () OVER ( ORDER BY id ) AS rowNumber, * from cost_main ";
foreach (string key in keyList)
{
//对第一个key特殊处理
if (key.Equals(keyList.First()))
{
sql += " where ";
}
else
{
sql += " and ";
}
//对比较特别的关键字做不同的处理方式
if (key.Equals("start_time"))
{
DateTime start_time = (DateTime)conditions["start_time"];
sql += " apply_time>='" + new DateTime(start_time.Year, start_time.Month, start_time.Day, 0, 0, 0) + "'";
}
else if (key.Equals("end_time"))
{
DateTime end_time = (DateTime)conditions["end_time"];
sql += " apply_time<='" + new DateTime(end_time.Year, end_time.Month, end_time.Day, 23, 59, 59) + "'";
}
else
{ //增加对中文的支持
sql += " " + key + " like N'%" + conditions[key] + "%'";
}
}
//分页基础参数
int page = 1, limit = 10;//默认查询第一页，一页十条数据
if (conditions.Keys.Contains("page"))
{
page = (int)conditions["page"];
}
if (conditions.Keys.Contains("limit"))
{
limit = (int)conditions["limit"];
}
//拼接分页sql语句
sql = "select top " + limit + " * from ( " + sql + " ) as t where rowNumber > " + (limit * (page - 1));
//执行查询获取数据并封装返回
List <cost_main> listMain = new List <cost_main>();
DataTable dataTable = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql).Tables[0];
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
cost_main main = new cost_main
{
id = (int)row["id"],
apply_id = (int)row["apply_id"],
apply_money = (decimal)row["apply_money"],
status = (byte)row["status"],
apply_time = (DateTime)row["apply_time"],
remark = (string)row["remark"]
};
listMain.Add(main);
}
return(listMain);
}