/// <summary> /// 保存费用报销单 /// </summary> /// <param name="reimbModel"></param> /// <param name="reimbDetailList"></param> /// <param name="strMsg"></param> public static bool SaveReimbursement(ReimbursementModel reimbModel, List<ReimbDetailsModel> reimbDetailList, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; //判断单据编号是否存在 if (NoIsExist(reimbModel.ReimbNo, reimbModel.CompanyCD)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { int reimbID; reimbID = InsertReimbursement(reimbModel, tran); InsertReimbDetails(reimbDetailList, reimbID, tran); tran.Commit(); isSucc = true; strMsg = "保存成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "该编号已被使用,请输入未使用的编号!"; } return isSucc; }
/// <summary> /// 费用报销单主表数据插入 /// </summary> /// <param name="reimbModel">费用报销单主表实体</param> /// <param name="tran">事务</param> /// <returns>主表插入后返回的ID</returns> public static int InsertReimbursement(ReimbursementModel reimbModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.FeeReturn ("); strSql.Append(" CompanyCD,ReimbNo,Title,FromType,Applyor,ReimbDate,ReimbDeptID,UserReimbID,CanViewUser,"); strSql.Append(" ExpAllAmount,ReimbAllAmount,RestoreAllAmount,Status,Creator,CreateDate,Remark,"); strSql.Append(" ModifiedUserID,ModifiedDate,Confirmor,ConfirmDate,ProjectID,SubjectsNo,CustID,ContactsUnitID,FromTBName,ContactsUnitName,Attachment)"); strSql.Append(" values (@CompanyCD,@ReimbNo,@Title,@FromType,@Applyor,@ReimbDate,@ReimbDeptID,@UserReimbID,@CanViewUser,"); strSql.Append(" @ExpAllAmount,@ReimbAllAmount,@RestoreAllAmount,@Status,@Creator,@CreateDate,"); strSql.Append(" @Remark,@ModifiedUserID,@ModifiedDate,@Confirmor,@ConfirmDate,@ProjectID,@SubjectsNo,@CustID,"); strSql.Append(" @ContactsUnitID,@FromTBName,@ContactsUnitName,@Attachment); set @Id=@@IDENTITY"); SqlParameter[] param ={ new SqlParameter("@CompanyCD",reimbModel.CompanyCD ), new SqlParameter("@ReimbNo",reimbModel.ReimbNo ), new SqlParameter("@Title",reimbModel.Title ), new SqlParameter("@FromType",reimbModel.FromType), new SqlParameter("@Applyor",reimbModel.Applyor ), new SqlParameter("@ReimbDate",reimbModel.ReimbDate ), new SqlParameter("@ReimbDeptID",reimbModel.ReimbDeptID ), new SqlParameter("@UserReimbID",reimbModel.UserReimbID ), new SqlParameter("@ExpAllAmount",reimbModel.ExpAllAmount ), new SqlParameter("@ReimbAllAmount",reimbModel.ReimbAllAmount ), new SqlParameter("@RestoreAllAmount",reimbModel.RestoreAllAmount ), new SqlParameter("@Status",reimbModel.Status ), new SqlParameter("@Creator",reimbModel.Creator ), new SqlParameter("@CreateDate",reimbModel.CreateDate ), new SqlParameter("@Remark",reimbModel.Remark ), new SqlParameter("@ModifiedUserID",reimbModel.ModifiedUserID ), new SqlParameter("@ModifiedDate",reimbModel.ModifiedDate ), new SqlParameter("@Confirmor",DBNull.Value ), new SqlParameter("@ConfirmDate",DBNull.Value), new SqlParameter("@Id",SqlDbType.Int,6), new SqlParameter("@CanViewUser",reimbModel.CanViewUser), new SqlParameter("@ProjectID",reimbModel.ProjectID), new SqlParameter("@SubjectsNo",reimbModel.SubjectsNo), new SqlParameter("@CustID",reimbModel.CustID), new SqlParameter("@ContactsUnitID",reimbModel.ContactsUnitID), new SqlParameter("@FromTBName",reimbModel.FromTBName), new SqlParameter("@ContactsUnitName",reimbModel.ContactsUnitName), new SqlParameter("@Attachment",reimbModel.Attachment) }; param[19].Direction = ParameterDirection.Output; foreach (SqlParameter para in param) { if (para.Value == null || para.Value.ToString() == "-1") { para.Value = DBNull.Value; } } SqlHelper.ExecuteTransSql(strSql.ToString(), param); int Id = Convert.ToInt32(param[19].Value); return Id; }
/// <summary> /// 确认报销单 /// </summary> /// <param name="reimbNo">报销单ID</param> /// <param name="strCompanyCD">公司编码</param> /// <param name="EmployeeName">当前登录人名称</param> /// <param name="EmployeeID">当前登录人ID</param> /// <param name="UserID">当前登录人用户ID</param> /// <param name="strMsg">返回信息</param> /// <returns>返回是否成功标志</returns> public static bool ConfirmReimbursement(ReimbursementModel reimbModel,List<ReimbDetailsModel> reimbDetailModelList,string strExpID, string strCompanyCD, string EmployeeName, int EmployeeID, string UserID, out string strMsg) { strMsg = ""; bool isSuc = false; StringBuilder strSql = new StringBuilder(); if (isHandle(reimbModel.ReimbNo, "1", strCompanyCD)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { //判断是否启用自动生成凭证 //((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsApply;//自动审核登帐 //((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsVoucher;//自动生成凭证 if (((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsVoucher) { if (isExistSubjectNos(reimbModel.SubjectsNo, reimbDetailModelList)) { #region 自动生成凭证并确认 XBase.Model.Office.FinanceManager.AttestBillModel Model = new XBase.Model.Office.FinanceManager.AttestBillModel();//凭证主表实例 ArrayList DetailList = new ArrayList();//凭证明细数组 Model.CompanyCD = strCompanyCD; Model.FromName = ""; Model.Attachment = 1;//附件数 Model.AttestName = "记账凭证";//凭证名称 Model.VoucherDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));//凭证日期 Model.AttestNo = "记-" + XBase.Data.Office.FinanceManager.VoucherDBHelper.GetMaxAttestNo(strCompanyCD, DateTime.Now.ToString("yyyy-MM-dd"));//凭证号 Model.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;//制单人 Model.CreateDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));//制单日期 Model.FromTbale = "officedba.FeeReturn";//来源表名 Model.FromValue = reimbModel.ID.ToString();//来源表主键 XBase.Model.Office.FinanceManager.AttestBillDetailsModel DetailModel = new XBase.Model.Office.FinanceManager.AttestBillDetailsModel();//凭证明细表实例 DetailModel.Abstract = reimbModel.Remark == null ? "" : reimbModel.Remark;// dt.Rows[0]["Abstract"].ToString();//摘要 DetailModel.CurrencyTypeID = Convert.ToInt32(XBase.Data.Office.FinanceManager.CurrTypeSettingDBHelper.GetMasterCurrency(strCompanyCD).Rows[0]["ID"].ToString()); //币种 DetailModel.ExchangeRate = 1;//汇率 DetailModel.SubjectsCD = reimbModel.SubjectsNo == null ? "" : reimbModel.SubjectsNo;//科目编号 DetailModel.OriginalAmount = (decimal)reimbModel.ReimbAllAmount;//原币金额 DetailModel.DebitAmount = 0;//借方金额 DetailModel.CreditAmount = (decimal)reimbModel.ReimbAllAmount;//贷方金额 DetailModel.SubjectsDetails = ""; DetailModel.FormTBName = ""; DetailModel.FileName = ""; string Auciliary = XBase.Data.Office.FinanceManager.VoucherDBHelper.GetSubjectsAuciliaryCD(reimbModel.SubjectsNo, strCompanyCD); if (Auciliary == "供应商" || Auciliary == "客户" || Auciliary == "职员") { DetailModel.SubjectsDetails = reimbModel.UserReimbID.ToString();//辅助核算 if (Auciliary == "供应商") { DetailModel.FormTBName = "officedba.ProviderInfo"; DetailModel.FileName = "CustName"; } else if (Auciliary == "客户") { DetailModel.FormTBName = "officedba.CustInfo"; DetailModel.FileName = "CustName"; } else { DetailModel.FormTBName = "officedba.EmployeeInfo"; DetailModel.FileName = "EmployeeName"; } } DetailList.Add(DetailModel); //根据凭证模板构建凭证明细数组 foreach (ReimbDetailsModel RDetailM in reimbDetailModelList) { XBase.Model.Office.FinanceManager.AttestBillDetailsModel DetailM = new XBase.Model.Office.FinanceManager.AttestBillDetailsModel();//凭证明细表实例 DetailM.Abstract = RDetailM.Notes;//摘要 DetailM.CurrencyTypeID = Convert.ToInt32(XBase.Data.Office.FinanceManager.CurrTypeSettingDBHelper.GetMasterCurrency(strCompanyCD).Rows[0]["ID"].ToString());//币种 DetailM.ExchangeRate = 1;//汇率 DetailM.SubjectsCD = RDetailM.SubjectsNo;//科目编号 DetailM.OriginalAmount = RDetailM.ReimbAmount;//原币金额 DetailM.DebitAmount = RDetailM.ReimbAmount;//借方金额 DetailM.CreditAmount = 0;//贷方金额 DetailM.SubjectsDetails = ""; DetailM.FormTBName = ""; DetailM.FileName = ""; DetailList.Add(DetailM); } //自动生成凭证并根据IsApply判断是否登帐 --生成成功 string isApply = "0";//自动审核登帐 if (((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsApply) { isApply = "1"; } int BillID = 0; if (XBase.Data.Office.FinanceManager.VoucherDBHelper.InsertIntoAttestBill(Model, DetailList, out BillID, isApply)) { strSql.Append(" update officedba.FeeReturn set "); strSql.Append(" Confirmor=@Confirmor ,Status=@Status,ConfirmDate=getdate()"); strSql.Append(" ,ModifiedUserID=@ModifiedUserID,ModifiedDate=getdate() "); strSql.Append(" ,IsAccount='1',Accountor=@Confirmor,AccountDate=getdate(),AttestBillID=@BillID "); strSql.Append(" where CompanyCD= @CompanyCD and ReimbNo=@ReimbNo "); SqlParameter[] param ={ new SqlParameter("@Confirmor",EmployeeID), new SqlParameter("@Status","2"), new SqlParameter("@CompanyCD",strCompanyCD), new SqlParameter("@ReimbNo",reimbModel.ReimbNo), new SqlParameter("@ModifiedUserID",UserID), new SqlParameter("@BillID",BillID) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); string[] expID = strExpID.Split(','); for (int j = 0; j < expID.Length; j++) { if (expID[j] != "") { SqlParameter[] param2 = { new SqlParameter("@ExpID",int.Parse(expID[j])) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "update officedba.FeeApply set IsReimburse='1' where ID=@ExpID", param2); } } tran.Commit(); isSuc = true; strMsg = "确认并生产凭证成功!" + "@" + EmployeeName + "@" + System.DateTime.Now + "@" + BillID; } #endregion } else { strSql.Append(" update officedba.FeeReturn set "); strSql.Append(" Confirmor=@Confirmor ,Status=@Status,ConfirmDate=getdate()"); strSql.Append(" ,ModifiedUserID=@ModifiedUserID,ModifiedDate=getdate() "); strSql.Append(" where CompanyCD= @CompanyCD and ReimbNo=@ReimbNo "); SqlParameter[] param ={ new SqlParameter("@Confirmor",EmployeeID), new SqlParameter("@Status","2"), new SqlParameter("@CompanyCD",strCompanyCD), new SqlParameter("@ReimbNo",reimbModel.ReimbNo), new SqlParameter("@ModifiedUserID",UserID) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); string[] expID = strExpID.Split(','); for (int j = 0; j < expID.Length; j++) { if (expID[j] != "") { SqlParameter[] param2 = { new SqlParameter("@ExpID",int.Parse(expID[j])) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "update officedba.FeeApply set IsReimburse='1' where ID=@ExpID", param2); } } tran.Commit(); isSuc = true; strMsg = "警告!确认成功!生成凭证失败:结算科目或者费用科目为空!" + "@" + EmployeeName + "@" + System.DateTime.Now+"@"+0; } } else { strSql.Append(" update officedba.FeeReturn set "); strSql.Append(" Confirmor=@Confirmor ,Status=@Status,ConfirmDate=getdate()"); strSql.Append(" ,ModifiedUserID=@ModifiedUserID,ModifiedDate=getdate() "); strSql.Append(" where CompanyCD= @CompanyCD and ReimbNo=@ReimbNo "); SqlParameter[] param ={ new SqlParameter("@Confirmor",EmployeeID), new SqlParameter("@Status","2"), new SqlParameter("@CompanyCD",strCompanyCD), new SqlParameter("@ReimbNo",reimbModel.ReimbNo), new SqlParameter("@ModifiedUserID",UserID) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); string[] expID = strExpID.Split(','); for (int j = 0; j < expID.Length; j++) { if (expID[j] != "") { SqlParameter[] param2 = { new SqlParameter("@ExpID",int.Parse(expID[j])) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "update officedba.FeeApply set IsReimburse='1' where ID=@ExpID", param2); } } tran.Commit(); isSuc = true; strMsg = "确认成功!" + "@" + EmployeeName + "@" + System.DateTime.Now+"@"+0; } } catch (Exception ex) { tran.Rollback(); strMsg = "确认失败!请联系管理员"; isSuc = false; throw ex; } } else {//已经被其他人确认 strMsg = "已经确认的单据不可再次确认!"; isSuc = false; } return isSuc; }
/// <summary> /// 修改费用报销单 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="reimbDetailModelList">费用报销明细表实体列表</param> /// <param name="strMsg"></param> /// <returns></returns> public static bool UpdateReimbursement(ReimbursementModel reimbModel, List<ReimbDetailsModel> reimbDetailModelList, out string strMsg) { bool isSucc = false;//是否修改成功 strMsg = ""; if (IsUpdate(reimbModel.ReimbNo, reimbModel.CompanyCD)) { string strSql = "delete from officedba.FeeReturnDetail where ReimbID=@ReimbID "; SqlParameter[] paras = { new SqlParameter("@ReimbID", reimbModel.ID) }; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateReimbursement(reimbModel, tran); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras); InsertReimbDetails(reimbDetailModelList, reimbModel.ID, tran); tran.Commit(); isSucc = true; strMsg = "修改成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "修改失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "非制单状态的单据不可修改!"; } return isSucc; }
/// <summary> /// 修改费用报销主表信息 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="tran">事务</param> public static void UpdateReimbursement(ReimbursementModel reimbModel,TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.FeeReturn set "); strSql.Append(" Title=@Title,Applyor=@Applyor,ReimbDate=@ReimbDate,ReimbDeptID=@ReimbDeptID,UserReimbID=@UserReimbID,"); strSql.Append(" FromType=@FromType,CanViewUser=@CanViewUser,"); strSql.Append(" ExpAllAmount=@ExpAllAmount,ReimbAllAmount=@ReimbAllAmount,RestoreAllAmount=@RestoreAllAmount,"); strSql.Append(" Remark=@Remark,ModifiedUserID=@ModifiedUserID,ModifiedDate=@ModifiedDate,ProjectID=@ProjectID,"); strSql.Append(" SubjectsNo=@SubjectsNo,CustID=@CustID,ContactsUnitID=@ContactsUnitID,FromTBName=@FromTBName,ContactsUnitName=@ContactsUnitName,Attachment=@Attachment "); strSql.Append(" where ID=@ID "); SqlParameter[] param ={ new SqlParameter("@ID",reimbModel.ID ), new SqlParameter("@Title",reimbModel.Title ), new SqlParameter("@FromType",reimbModel.FromType), new SqlParameter("@Applyor",reimbModel.Applyor), new SqlParameter("@ReimbDate",reimbModel.ReimbDate), new SqlParameter("@ReimbDeptID",reimbModel.ReimbDeptID), new SqlParameter("@UserReimbID",reimbModel.UserReimbID), new SqlParameter("@ExpAllAmount",reimbModel.ExpAllAmount), new SqlParameter("@ReimbAllAmount",reimbModel.ReimbAllAmount), new SqlParameter("@RestoreAllAmount",reimbModel.RestoreAllAmount), new SqlParameter("@Remark",reimbModel.Remark), new SqlParameter("@ModifiedUserID",reimbModel.ModifiedUserID), new SqlParameter("@ModifiedDate",reimbModel.ModifiedDate), new SqlParameter("@CanViewUser",reimbModel.CanViewUser), new SqlParameter("@ProjectID",reimbModel.ProjectID), new SqlParameter("@SubjectsNo",reimbModel.SubjectsNo), new SqlParameter("@CustID",reimbModel.CustID), new SqlParameter("@ContactsUnitID",reimbModel.ContactsUnitID), new SqlParameter("@FromTBName",reimbModel.FromTBName), new SqlParameter("@ContactsUnitName",reimbModel.ContactsUnitName), new SqlParameter("@Attachment",reimbModel.Attachment) }; foreach (SqlParameter para in param) { if (para.Value == null || para.Value.ToString() == "-1") { para.Value = DBNull.Value; } } SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); }
/// <summary> /// 根据检索条件获取报销单据金额合计 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="empid"></param> /// <param name="ReimbDate1"></param> /// <param name="FlowStatus"></param> /// <param name="ReimbAmount1"></param> /// <returns></returns> public static DataTable GetReimbSumTotal(ReimbursementModel reimbModel, int empid, DateTime? ReimbDate1, string FlowStatus, decimal? ReimbAmount1) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select sum(reimb.ExpAllAmount) as ExpAllAmountTotal,sum(reimb.ReimbAllAmount) as ReimbAllAmountTotal,sum(reimb.RestoreAllAmount) as RestoreAllAmountTotal ");//reimb.Status "); strSql.Append(" from officedba.FeeReturn reimb "); strSql.Append(" left join officedba.FlowInstance f on reimb.CompanyCD=f.CompanyCD and f.BillTypeFlag=1 "); strSql.Append(" and f.BillTypeCode=5 and f.BillNo=reimb.ReimbNo "); strSql.Append(" and f.ID=(select max(ID) from officedba.FlowInstance as f2 where reimb.ID=f2.BillID and f2.BillTypeFlag=1 and f2.BillTypeCode=5 )"); strSql.Append(" where reimb.CompanyCD=@CompanyCD "); strSql.Append(" and ( charindex('," + empid + ",' , ','+reimb.CanViewUser+',')>0 or reimb.Creator=" + empid + " OR reimb.CanViewUser='' OR reimb.CanViewUser is null) "); if (reimbModel.ReimbNo != null) { strSql.Append(" and reimb.ReimbNo like '%" + reimbModel.ReimbNo + "%' "); } if (reimbModel.Title != null) { strSql.Append(" and reimb.Title like '%" + reimbModel.Title + "%' "); } if (reimbModel.Applyor != -1) { strSql.Append(" and reimb.Applyor=" + reimbModel.Applyor); } if (reimbModel.ReimbDate != null && ReimbDate1 != null) { strSql.Append(" and (reimb.ReimbDate >= '" + reimbModel.ReimbDate.ToString().Trim() + "' and reimb.ReimbDate <= '" + ReimbDate1.ToString().Trim() + "' )"); } if (reimbModel.Status != null) { strSql.Append(" and reimb.Status =" + reimbModel.Status); } if (FlowStatus != null) { if (FlowStatus == "0") { strSql.Append(" and f.FlowStatus is null "); } else { strSql.Append(" and f.FlowStatus='" + FlowStatus + "'"); } } if (reimbModel.UserReimbID != -1) { strSql.Append(" and reimb.UserReimbID=" + reimbModel.UserReimbID); } if (reimbModel.ReimbAllAmount != null) { strSql.Append(" and reimb.ReimbAllAmount>=" + reimbModel.ReimbAllAmount); } if (ReimbAmount1 != null) { strSql.Append(" and reimb.ReimbAllAmount<=" + ReimbAmount1); } if (reimbModel.ProjectID != null) { strSql.Append(" and reimb.ProjectID=" + reimbModel.ProjectID); } if (reimbModel.CustID != null) { strSql.Append(" and reimb.CustID='" + reimbModel.CustID + "'"); } if (reimbModel.ContactsUnitID != null) { strSql.Append(" and reimb.ContactsUnitID='" + reimbModel.ContactsUnitID + "'"); } SqlParameter[] param ={ new SqlParameter("@CompanyCD",reimbModel.CompanyCD) }; return SqlHelper.ExecuteSql(strSql.ToString(), param); }
/// <summary> /// 根据条件获取历史报销单列表 /// </summary> /// <param name="reimbModel">报销单主表实体</param> /// <param name="ReimbDate1">报销日期</param> /// <param name="pageIndex">当前页</param> /// <param name="pageCount">每页显示记录数</param> /// <param name="ord">排序</param> /// <param name="TotalCount">总记录数</param> /// <returns>返回历史报销单列表datatable</returns> public static DataTable GetHisReimbursementList(ReimbursementModel reimbModel,int empid, DateTime? ReimbDate1, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder strSql = new StringBuilder(); strSql.Append("select reimb.ID,reimb.ReimbNo,reimb.Title,reimb.Applyor,e1.EmployeeName as ApplyorName,"); strSql.Append(" convert(varchar(100), reimb.ReimbDate, 23) as ReimbDate, "); strSql.Append(" reimb.ExpAllAmount,reimb.ReimbAllAmount,reimb.RestoreAllAmount,reimb.Status "); strSql.Append(" ,reimb.Confirmor,e3.EmployeeName as ConfirmorName,convert(varchar(100),reimb.ConfirmDate,23) as ConfirmDate "); strSql.Append(" from officedba.FeeReturn reimb "); strSql.Append(" left join officedba.EmployeeInfo e1 on reimb.Applyor=e1.ID "); strSql.Append(" left join officedba.EmployeeInfo e3 on reimb.Confirmor=e3.ID "); strSql.Append(" where reimb.CompanyCD=@CompanyCD "); strSql.Append(" and ( charindex('," + empid + ",' , ','+reimb.CanViewUser+',')>0 or reimb.Creator=" + empid + " OR reimb.CanViewUser='' OR reimb.CanViewUser is null) "); if (reimbModel.ReimbNo != null) { strSql.Append(" and reimb.ReimbNo like '%" + reimbModel.ReimbNo + "%' "); } if (reimbModel.Title != null) { strSql.Append(" and reimb.Title like '%" + reimbModel.Title + "%' "); } if (reimbModel.Applyor != -1) { strSql.Append(" and reimb.Applyor=" + reimbModel.Applyor); } if (reimbModel.ReimbDate != null && ReimbDate1 != null) { strSql.Append(" and (reimb.ReimbDate >= '" + reimbModel.ReimbDate.ToString().Trim() + "' and reimb.ReimbDate <= '" + ReimbDate1.ToString().Trim() + "' )"); } if (reimbModel.Status != null) { strSql.Append(" and reimb.Status =" + reimbModel.Status); } SqlParameter[] param ={ new SqlParameter("@CompanyCD",reimbModel.CompanyCD) }; return SqlHelper.CreateSqlByPageExcuteSql(strSql.ToString(), pageIndex, pageCount, ord, param, ref TotalCount); }
/// <summary> /// 根据查询条件获取费用报销单列表 /// 导出列表页调用此方法 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="ReimbDate1">报销日期</param> /// <param name="FlowStatus">审批状态</param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable GetReimbursementList(ReimbursementModel reimbModel, int empid,DateTime? ReimbDate1, string FlowStatus,decimal? ReimbAmount1, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder strSql = new StringBuilder(); strSql.Append("select reimb.ID,reimb.ReimbNo,reimb.Title,reimb.Applyor,e1.EmployeeName as ApplyorName,"); strSql.Append(" reimb.UserReimbID,e2.EmployeeName as UserReimbName,"); strSql.Append(" convert(varchar(100), reimb.ReimbDate, 23) as ReimbDate, "); strSql.Append(" reimb.ExpAllAmount,reimb.ReimbAllAmount,reimb.RestoreAllAmount,reimb.Status "); strSql.Append(" ,isnull( case f.FlowStatus when '1' then '待审批' when '2' then '审批中'"); strSql.Append(" when '3' then '审批通过' when '4' then '审批不通过' when '5' then '撤消审批' end,'') as FlowStatus "); strSql.Append(" ,reimb.Confirmor,e3.EmployeeName as ConfirmorName,convert(varchar(100),reimb.ConfirmDate,23) as ConfirmDate "); strSql.Append(" ,reimb.ProjectID,p.ProjectName,reimb.CustID,reimb.ContactsUnitID, "); strSql.Append(" isnull(c.CustName,'') as CustName,isnull(reimb.ContactsUnitName,'') as ContactsUnitName "); strSql.Append(" from officedba.FeeReturn reimb "); strSql.Append(" left join officedba.EmployeeInfo e1 on reimb.Applyor=e1.ID "); strSql.Append(" left join officedba.EmployeeInfo e2 on reimb.UserReimbID=e2.ID "); strSql.Append(" left join officedba.EmployeeInfo e3 on reimb.Confirmor=e3.ID "); strSql.Append(" left join officedba.ProjectInfo p on p.ID=reimb.ProjectID "); strSql.Append(" left join officedba.CustInfo c on c.ID=reimb.CustID "); strSql.Append(" left join officedba.FlowInstance f on reimb.CompanyCD=f.CompanyCD and f.BillTypeFlag=1 "); strSql.Append(" and f.BillTypeCode=5 and f.BillNo=reimb.ReimbNo "); strSql.Append(" and f.ID=(select max(ID) from officedba.FlowInstance as f2 where reimb.ID=f2.BillID and f2.BillTypeFlag=1 and f2.BillTypeCode=5 )"); strSql.Append(" where reimb.CompanyCD=@CompanyCD "); strSql.Append(" and ( charindex('," + empid + ",' , ','+reimb.CanViewUser+',')>0 or reimb.Creator=" + empid + " OR reimb.CanViewUser='' OR reimb.CanViewUser is null) "); if (reimbModel.ReimbNo != null) { strSql.Append(" and reimb.ReimbNo like '%" + reimbModel.ReimbNo + "%' "); } if (reimbModel.Title != null) { strSql.Append(" and reimb.Title like '%" + reimbModel.Title + "%' "); } if (reimbModel.Applyor != -1) { strSql.Append(" and reimb.Applyor=" + reimbModel.Applyor); } if (reimbModel.ReimbDate != null && ReimbDate1 != null) { strSql.Append(" and (reimb.ReimbDate >= '" + reimbModel.ReimbDate.ToString().Trim() + "' and reimb.ReimbDate <= '" + ReimbDate1.ToString().Trim() + "' )"); } if (reimbModel.Status != null) { strSql.Append(" and reimb.Status =" + reimbModel.Status); } if (FlowStatus != null) { if (FlowStatus == "0") { strSql.Append(" and f.FlowStatus is null "); } else { strSql.Append(" and f.FlowStatus='" + FlowStatus + "'"); } } if (reimbModel.UserReimbID != -1) { strSql.Append(" and reimb.UserReimbID="+reimbModel.UserReimbID ); } if (reimbModel.ReimbAllAmount != null) { strSql.Append(" and reimb.ReimbAllAmount>="+reimbModel.ReimbAllAmount); } if (ReimbAmount1 != null) { strSql.Append(" and reimb.ReimbAllAmount<="+ReimbAmount1); } if (reimbModel.ProjectID != null) { strSql.Append(" and reimb.ProjectID=" + reimbModel.ProjectID); } if (reimbModel.CustID != null) { strSql.Append(" and reimb.CustID='" + reimbModel.CustID + "'"); } if (reimbModel.ContactsUnitID != null) { strSql.Append(" and reimb.ContactsUnitID='" + reimbModel.ContactsUnitID + "'"); } SqlParameter[] param ={ new SqlParameter("@CompanyCD",reimbModel.CompanyCD) }; return SqlHelper.CreateSqlByPageExcuteSql(strSql.ToString(), pageIndex, pageCount, ord, param, ref TotalCount); }
/// <summary> /// 确认报销单 /// </summary> /// <param name="reimbNo">报销单ID</param> /// <param name="strCompanyCD">公司编码</param> /// <param name="EmployeeName">当前登录人名称</param> /// <param name="EmployeeID">当前登录人ID</param> /// <param name="UserID">当前登录人用户ID</param> /// <param name="strMsg">返回信息</param> /// <returns>返回是否成功标志</returns> public static bool ConfirmReimbursement(ReimbursementModel reimbModel,List<ReimbDetailsModel> reimbDetailModelList, string strExpID ,string strCompanyCD, string EmployeeName, int EmployeeID, string UserID, out string strMsg) { return ReimbursementDBHelper.ConfirmReimbursement(reimbModel, reimbDetailModelList, strExpID, strCompanyCD, EmployeeName, EmployeeID, UserID, out strMsg); }
/// <summary> /// 修改费用报销单 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="reimbDetailModelList">费用报销明细表实体列表</param> /// <param name="strMsg"></param> /// <returns></returns> public static bool UpdateReimbursement(ReimbursementModel reimbModel, List<ReimbDetailsModel> reimbDetailModelList, out string strMsg) { return ReimbursementDBHelper.UpdateReimbursement(reimbModel, reimbDetailModelList, out strMsg); }
/// <summary> /// 根据条件获取历史报销单列表 /// </summary> /// <param name="reimbModel">报销单主表实体</param> /// <param name="ReimbDate1">报销日期</param> /// <param name="pageIndex">当前页</param> /// <param name="pageCount">每页显示记录数</param> /// <param name="ord">排序</param> /// <param name="TotalCount">总记录数</param> /// <returns>返回历史报销单列表datatable</returns> public static DataTable GetHisReimbursementList(ReimbursementModel reimbModel,int empid, DateTime? ReimbDate1, int pageIndex, int pageCount, string ord, ref int TotalCount) { return ReimbursementDBHelper.GetHisReimbursementList(reimbModel,empid, ReimbDate1, pageIndex, pageCount, ord, ref TotalCount); }
/// <summary> /// 根据检索条件获取报销单据金额合计 /// </summary> /// <param name="reimbModel">费用报销主表实体</param> /// <param name="empid"></param> /// <param name="ReimbDate1"></param> /// <param name="FlowStatus"></param> /// <param name="ReimbAmount1"></param> /// <returns></returns> public static DataTable GetReimbSumTotal(ReimbursementModel reimbModel, int empid, DateTime? ReimbDate1, string FlowStatus, decimal? ReimbAmount1) { return ReimbursementDBHelper.GetReimbSumTotal(reimbModel, empid, ReimbDate1, FlowStatus, ReimbAmount1); }