/// <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);
}
