public int InsertMain(Bill_Main main)
{
string sql = @"insert into bill_main( billCode, billName, flowID, stepID, billUser, billDate, billDept, billJe, loopTimes, billType, billName2,isGk,gkDept,dydj,note1,note2,note3,note4,note5) values
(@billCode, @billName, @flowID, @stepID, @billUser, @billDate, @billDept, @billJe, @loopTimes, @billType, @billName2,@isGk,@gkDept,@dydj,@note1,@note2,@note3,@note4,@note5)";
SqlParameter[] sps =
{
new SqlParameter("@billCode", SqlNull(main.BillCode)),
new SqlParameter("@billName", SqlNull(main.BillName)),
new SqlParameter("@flowID", SqlNull(main.FlowId)),
new SqlParameter("@stepID", SqlNull(main.StepId)),
new SqlParameter("@billUser", SqlNull(main.BillUser)),
new SqlParameter("@billDate", SqlNull(main.BillDate)),
new SqlParameter("@billDept", SqlNull(main.BillDept)),
new SqlParameter("@billJe", SqlNull(main.BillJe)),
new SqlParameter("@loopTimes", SqlNull(main.LoopTimes)),
new SqlParameter("@billType", SqlNull(main.BillType)),
new SqlParameter("@billName2", SqlNull(main.BillName2)),
new SqlParameter("@isGk", SqlNull(main.IsGk)),
new SqlParameter("@gkDept", SqlNull(main.GkDept)),
new SqlParameter("@dydj", SqlNull(main.Dydj)),
new SqlParameter("@note1", SqlNull(main.Note1)),
new SqlParameter("@note2", SqlNull(main.Note2)),
new SqlParameter("@note3", SqlNull(main.Note3)),
new SqlParameter("@note4", SqlNull(main.Note4)),
new SqlParameter("@note5", SqlNull(main.Note5))
};
return(DataHelper.ExcuteNonQuery(sql, sps, false));
}
public bool Edit(Bill_Main main, IList <T_BillingApplication> kpsqLists)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal maindal = new MainDal();
maindal.DeleteMain(main.BillCode, tran);
Delete(main.BillCode, tran);
maindal.InsertMain(main, tran);
foreach (var i in kpsqLists)
{
Add(i, tran);
}
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
public bool Ph_Add(Bill_Main main, Bill_Ybbxmxb ybbx, Bill_Ybbxmxb_Fykm fykm, IList <Bill_Ybbxmxb_Fykm_Dept> hsbmList, IList <Bill_Ybbxmxb_Hsxm> hsxmList)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
DeleteMain(main.BillCode, tran);
InsertMain(main, tran);
AddYbbxmx(ybbx, tran);
AddFykm(fykm, tran);
AddHsbm(hsbmList, tran);
AddHsXm(hsxmList, tran);
DeletePh_mainByBillCode(main.BillCode, tran);
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
public void InsertYbbx(Bill_Main main, IList <Bill_Ybbxmxb> mxList)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal mainDal = new MainDal();
mainDal.DeleteMain(main.BillCode, tran);
DeleteYbbx(main.BillCode, tran);
mainDal.InsertMain(main, tran);
foreach (Bill_Ybbxmxb ybbxmx in mxList)
{
InsertYbbxmxb(ybbxmx, tran);
if (ybbxmx.KmList != null)
{
foreach (Bill_Ybbxmxb_Fykm km in ybbxmx.KmList)
{
InsertYbbxKm(km, tran);
if (km.DeptList != null)
{
foreach (Bill_Ybbxmxb_Fykm_Dept dept in km.DeptList)
{
InsertYbbxDept(dept, tran);
}
}
if (km.XmList != null)
{
foreach (Bill_Ybbxmxb_Hsxm xm in km.XmList)
{
InsertYbbxXm(xm, tran);
}
}
}
}
if (ybbxmx.FysqList != null)
{
foreach (Bill_Ybbx_Fysq fysq in ybbxmx.FysqList)
{
InsertYbbxFysq(fysq, tran);
}
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
}
}
private void UpdateMain(Bill_Main main, SqlTransaction tran)
{
string sql = "update bill_main set billje=billje+@je where billCode=@billCode and flowId=@flowid ";
SqlParameter[] parms =
{
new SqlParameter("@je", main.BillJe),
new SqlParameter("@billCode", main.BillCode),
new SqlParameter("@flowid", main.FlowId)
};
DataHelper.ExcuteNonQuery(sql, tran, parms, false);
}
/// <summary>
/// 删除一条数据
/// </summary>
public int Delete(Bill_Main main, string MainCode)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal maindal = new MainDal();
maindal.DeleteMain(main.BillCode, tran);
int intRow = Delete(MainCode, tran);
tran.Commit();
return(intRow);
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
public bool AddModel(T_LoanList model, Bill_Main main)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
new MainDal().DeleteMain(main.BillCode, tran);
new MainDal().InsertMain(main, tran);
LoanDal.Delete(model.Listid, tran);
LoanDal.Add(model, tran);
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
public void InsertYsmx(IList <Bill_Ysmxb> list, Bill_Main main)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
DeleteYsmx(main.BillCode);
InsertYsmx(list, tran);
MainDal md = new MainDal();
md.DeleteMain(main.BillCode);
md.InsertMain(main, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 添加 添加本表 添加主表 将所选的入库的cdf13 赋值为本主表的主键
/// </summary>
/// <param name="model"></param>
public bool Add(bill_yksq model, Bill_Main main)
{
string[] arrCodes = model.rkCodes.Split(',');
string dbMsg = "";
if (arrCodes.Length > 0)
{
bool isSet;
dbMsg = GetDbName(out isSet);
if (!isSet) //如果没有配置直接返删除失败
{
return(false);
}
}
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
new MainDal().DeleteMain(main.BillCode, tran);
new MainDal().InsertMain(main, tran);
dal.Delete(model.billCode, tran);
dal.Add(model, tran);
for (int i = 0; i < arrCodes.Length; i++)
{
string temp = "update " + dbMsg + " set cDefine13='" + main.BillCode + "' where ID= " + arrCodes[i];
DataHelper.ExcuteNonQuery(temp, tran, null, false);
}
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="mainModel"></param>
/// <param name="model"></param>
/// <returns></returns>
public int Add(Bill_Main mainModel, dz_yksqd model)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
Delete(model.sqbh, tran);
MainDal maindal = new MainDal();
maindal.DeleteMain(mainModel.BillCode);
maindal.InsertMain(mainModel);
int iRel = Add(model, tran);
tran.Commit();
return(iRel);
}
catch
{
tran.Rollback();
throw;
}
}
}
public bool Edit(Bill_Main main, ZiChan_ChuZhiDan sqmodel)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal maindal = new MainDal();
maindal.DeleteMain(main.BillCode, tran);
Delete(main.BillCode, tran);
maindal.InsertMain(main, tran);
Add(sqmodel, tran);
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="billCode"></param>
/// <returns></returns>
public Bill_Main GetMainByCode(string billCode)
{
string sql = "select * from bill_main where billCode=@billCode";
SqlParameter[] sps = { new SqlParameter("@billCode", billCode) };
using (SqlDataReader dr = DataHelper.GetDataReader(sql, sps))
{
if (dr.Read())
{
Bill_Main main = new Bill_Main();
main.BillCode = Convert.ToString(dr["BillCode"]);
main.BillDate = Convert.ToDateTime(dr["BillDate"]);
main.BillDept = Convert.ToString(dr["BillDept"]);
main.BillJe = Convert.ToDecimal(dr["BillJe"]);
main.BillName = Convert.ToString(dr["BillName"]);
main.BillName2 = Convert.ToString(dr["BillName2"]);
main.BillType = Convert.ToString(dr["BillType"]);
main.BillUser = Convert.ToString(dr["BillUser"]);
main.FlowId = Convert.ToString(dr["FlowId"]);
main.LoopTimes = Convert.ToInt32(dr["LoopTimes"]);
main.StepId = Convert.ToString(dr["StepId"]);
main.IsGk = Convert.ToString(dr["IsGk"]);
main.GkDept = Convert.ToString(dr["GkDept"]);
main.Dydj = Convert.ToString(dr["Dydj"]);
main.Note1 = Convert.ToString(dr["Note1"]);
main.Note2 = Convert.ToString(dr["Note2"]);
main.Note3 = Convert.ToString(dr["Note3"]);
main.Note4 = Convert.ToString(dr["Note4"]);
main.Note5 = Convert.ToString(dr["Note5"]);
return(main);
}
else
{
return(null);
}
}
}
public bool Add(Bill_Main main, IList <ZiChan_ChuZhiDan> czLists)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal maindal = new MainDal();
maindal.InsertMain(main, tran);
foreach (var i in czLists)
{
Add(i, tran);
}
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
public void InsertXmzfdDal(Bill_xmzfd xmzfd, Bill_Main main)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal mainDal = new MainDal();
if (GetXmzfdByCode(xmzfd.Billcode) != null)
{
DeleteXmzfd(xmzfd.Billcode, tran);
}
mainDal.InsertMain(main, tran);
InsertXmzfdDal(xmzfd, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 通过报销单号 billname获取多个报销单（归口分解用到）
/// </summary>
/// <param name="billcode"></param>
/// <returns></returns>
public IList <Bill_Main> GetMainsByBillNameorCode(string billname)
{
string sql = "select * from bill_main where billname=@billname or billcode=@billname";
SqlParameter[] arrsp = new SqlParameter[] { new SqlParameter("@billname", billname) };
DataTable dtrel = DataHelper.GetDataTable(sql, arrsp, false);
IList <Bill_Main> lstmain = new List <Bill_Main>();
int idtrows = dtrel.Rows.Count;
for (int i = 0; i < idtrows; i++)
{
DataRow dr = dtrel.Rows[i];
Bill_Main main = new Bill_Main();
main.BillCode = Convert.ToString(dr["BillCode"]);
main.BillDate = Convert.ToDateTime(dr["BillDate"]);
main.BillDept = Convert.ToString(dr["BillDept"]);
main.BillJe = Convert.ToDecimal(dr["BillJe"]);
main.BillName = Convert.ToString(dr["BillName"]);
main.BillName2 = Convert.ToString(dr["BillName2"]);
main.BillType = Convert.ToString(dr["BillType"]);
main.BillUser = Convert.ToString(dr["BillUser"]);
main.FlowId = Convert.ToString(dr["FlowId"]);
main.LoopTimes = Convert.ToInt32(dr["LoopTimes"]);
main.StepId = Convert.ToString(dr["StepId"]);
main.IsGk = Convert.ToString(dr["IsGk"]);
main.GkDept = Convert.ToString(dr["GkDept"]);
main.Dydj = Convert.ToString(dr["Dydj"]);
main.Note1 = Convert.ToString(dr["Note1"]);
main.Note2 = Convert.ToString(dr["Note2"]);
main.Note3 = Convert.ToString(dr["Note3"]);
main.Note4 = Convert.ToString(dr["Note4"]);
main.Note5 = Convert.ToString(dr["Note5"]);
lstmain.Add(main);
}
return(lstmain);
}
/// <summary>
/// 插入预算明细
/// </summary>
/// <param name="list"></param>
/// <param name="main"></param>
public void InsertYsmx(IList <Bill_Ysmxb> list, Bill_Main main)
{
ysDal.InsertYsmx(list, main);
}
//修改记录
public bool Edit(Bill_Main main, IList <T_BillingApplication> kpsqLists)
{
return(dal.Edit(main, kpsqLists));
}
//增加记录
public bool Add(Bill_Main main, IList <ZiChan_CaiGouShenQing> kpsqLists)
{
return(dal.Add(main, kpsqLists));
}
//修改记录
public bool Edit(Bill_Main main, ZiChan_CaiGouShenQing sqmodel)
{
return(dal.Edit(main, sqmodel));
}
protected void btnSave_Click(object sender, EventArgs e)
{
bill_travelApplicationBLL bllTravelApplication = new bill_travelApplicationBLL();
string strCtrl = Request["type"];
string strBillCode = Request["billCode"];
string str_billuser = PubMethod.SubString(txt_billUser.Text);
string users = hfuser.Value;
if (string.IsNullOrEmpty(users))
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('出差人不能为空！');", true);
return;
}
string[] userArr = users.Split(',');
string travelPersons = "";
for (int i = 0; i < userArr.Length; i++)
{
if (travelPersons.IndexOf(userArr[i]) != -1)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('有重复的出差人！');", true);
return;
}
travelPersons += "|&|" + PubMethod.SubString(userArr[i]);
}
string str_billdate = txt_billDate.Text.Trim();
string str_billdept = server.GetCellValue("select userdept from bill_users where usercode='" + str_billuser + "'");
string bm = PubMethod.SubString(txt_userDept.Text);
//申请表
Bill_TravelApplication modelTravelApplication = new Bill_TravelApplication();
//主表
Bill_Main modelMainBill = new Bill_Main();
string strMsg = "";
try
{
//添加修改
if (strCtrl.Equals("edit") && strBillCode.Equals(""))
{
if (strBillCode.Equals(""))
{
throw new Exception("单号丢失！");
}
modelTravelApplication = bllTravelApplication.GetModel(strBillCode);
}
//添加
modelMainBill.BillName = "出差管理单";
modelMainBill.BillType = "";
modelMainBill.BillUser = str_billuser;
modelMainBill.FlowId = "ccsq";
modelMainBill.GkDept = "";
modelMainBill.IsGk = "";
modelMainBill.LoopTimes = 0;
modelMainBill.StepId = "-1";
//出差申请单表
modelTravelApplication.arrdess = this.txt_address.Text.Trim();
modelTravelApplication.maincode = txt_bh.Text.Trim();
modelTravelApplication.MoreThanStandard = Convert.ToInt32(ddlIsbz.SelectedValue);
//modelTravelApplication.needAmount = int.Parse(this.txtFeePlan.Text.Trim());
modelTravelApplication.reasion = this.txt_reasion.Text.Trim();
modelTravelApplication.Transport = this.txt_jtgj.Text.Trim();
modelTravelApplication.travelDate = this.txt_travelDate.Text.Trim();
modelTravelApplication.sendDept = PubMethod.SubString(txt_bm.Text);
string strAppPersion = this.txt_billUser.Text.Trim();
if (string.IsNullOrEmpty(strAppPersion))
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('申请人不能为空！');", true);
return;
}
modelTravelApplication.travelPersionCode = travelPersons;
modelTravelApplication.travelplan = this.txt_plan.Text.Trim();;
modelTravelApplication.jiaotongfei = ToIntNum(txt_jtf.Text);
modelTravelApplication.zhusufei = ToIntNum(txt_zsf.Text);
modelTravelApplication.yewuzhaodaifei = ToIntNum(txt_zdf.Text);
modelTravelApplication.huiyifei = ToIntNum(txt_hyf.Text);
modelTravelApplication.yinshuafei = ToIntNum(txt_ysf.Text);
modelTravelApplication.qitafei = ToIntNum(txt_qt.Text);
modelTravelApplication.needAmount = modelTravelApplication.jiaotongfei + modelTravelApplication.zhusufei + modelTravelApplication.yewuzhaodaifei + modelTravelApplication.huiyifei + modelTravelApplication.yinshuafei + modelTravelApplication.qitafei;
modelMainBill.BillJe = (decimal)modelTravelApplication.needAmount;
//if (this.ddlTravelType.SelectedValue == null)
//{
// throw new Exception("出差类型不能为空！");
//}
//modelTravelApplication.typecode = this.ddlTravelType.SelectedValue.Trim();
modelMainBill.BillCode = txt_bh.Text.Trim();
DateTime dtBillDate;
bool boBillDate = DateTime.TryParse(str_billdate, out dtBillDate);
if (boBillDate)
{
modelMainBill.BillDate = dtBillDate;
}
else
{
throw new Exception("日期格式输入不正确！");
}
UserMessage user = new UserMessage(modelMainBill.BillUser);
string strBillDept = user.GetRootDept().DeptCode;
if (string.IsNullOrEmpty(strBillDept))
{
throw new Exception("未发现人员所在单位！");
}
modelMainBill.BillDept = strBillDept;
//modelMainBill.BillJe = int.Parse(this.txtFeePlan.Text.Trim());//单据金额
int iRel = bllTravelApplication.AddNote(modelMainBill, modelTravelApplication, out strMsg);
if (iRel < 1)
{
throw new Exception(strMsg);
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('保存成功！');window.location.href='travelApplyList.aspx';", true);
}
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('保存失败,原因：" + ex.Message + "');", true);
}
}
// 删除记录
public int Delete(Bill_Main main, string bh)
{
return(dal.Delete(main, bh));
}
//增加记录
public bool Add(Bill_Main main, IList <ZiChan_ChuZhiDan> kpsqLists)
{
return(dal.Add(main, kpsqLists));
}
//修改记录
public bool Edit(Bill_Main main, ZiChan_ChuZhiDan sqmodel)
{
return(dal.Edit(main, sqmodel));
}
public void insertYbbxForGkfj(Bill_Main main, IList <Bill_Ybbxmxb> ybbxList)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
MainDal mainDal = new MainDal();
DeleteYbbxsByName(main.BillName, tran);
mainDal.DeleteMainByName(main.BillName, tran);
foreach (Bill_Ybbxmxb ybbxmx in ybbxList)
{
foreach (Bill_Ybbxmxb_Fykm km in ybbxmx.KmList)
{
//主表
main.GkDept = km.Bxbm;
main.BillCode = Guid.NewGuid().ToString();;
main.BillJe = km.Je;
mainDal.InsertMain(main, tran);
//明细表
ybbxmx.BillCode = main.BillCode;
InsertYbbxmxb(ybbxmx, tran);
//科目明细表
km.BillCode = main.BillCode;
InsertYbbxKm(km, tran);
if (km.DeptList != null)
{
foreach (Bill_Ybbxmxb_Fykm_Dept dept in km.DeptList)
{
InsertYbbxDept(dept, tran);
}
}
if (km.XmList != null)
{
foreach (Bill_Ybbxmxb_Hsxm xm in km.XmList)
{
InsertYbbxXm(xm, tran);
}
}
}
if (ybbxmx.FysqList != null)
{
foreach (Bill_Ybbx_Fysq fysq in ybbxmx.FysqList)
{
InsertYbbxFysq(fysq, tran);
}
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
}
}
/// <summary>
/// 归口费用报销单
/// </summary>
/// <param name="main"></param>
/// <param name="ybbxList"></param>
public void insertYbbxForGkfj(Bill_Main main, IList <Bill_Ybbxmxb> ybbxList)
{
ybbxDal.insertYbbxForGkfj(main, ybbxList);
}
public void InsertXmzfdDal(Bill_xmzfd xmzfd, Bill_Main main)
{
xmzfdal.InsertXmzfdDal(xmzfd, main);
}
public void InsertYbbx(Bill_Main main, IList <Bill_Ybbxmxb> mxList)
{
ybbxDal.InsertYbbx(main, mxList);
}