/// <summary>
/// 添加费用申请单
/// </summary>
/// <param name="expApplyModel"></param>
/// <param name="expDetailModelList"></param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static bool SaveExpensesApply(ExpensesApplyModel expApplyModel, List<ExpDetailsModel> expDetailModelList,out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(expApplyModel.ExpCode,expApplyModel.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int expID;
expID = InsertExpensesApply(expApplyModel, tran);
InsertExpensesDetails(expDetailModelList, expID,tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 插入费用申请主表信息
/// </summary>
/// <param name="expApplyModel"></param>
/// <param name="tran"></param>
public static int InsertExpensesApply(ExpensesApplyModel expApplyModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.FeeApply (");
strSql.Append(" CompanyCD,ExpCode,Title,Applyor,AriseDate,NeedDate,TotalAmount,PayType,CanViewUser,");
strSql.Append(" Reason,DeptID,TransactorID,Status,Creator,CreateDate,ExpType,CustID,SellChanceNo,IsReimburse,");
strSql.Append(" ModifiedUserID,ModifiedDate,Confirmor,ConfirmDate,ProjectID,Attachment,EndReimbTime)");
strSql.Append(" values (@CompanyCD,@ExpCode,@Title,@Applyor,@AriseDate,@NeedDate,@TotalAmount,@PayType,@CanViewUser,");
strSql.Append(" @Reason,@DeptID,@TransactorID,@Status,@Creator,@CreateDate,");
strSql.Append(" @ExpType,@CustID,@SellChanceNo,@IsReimburse,@ModifiedUserID,@ModifiedDate,@Confirmor,@ConfirmDate,");
strSql.Append(" @ProjectID,@Attachment,@EndReimbTime); set @Id=@@IDENTITY");
#region 参数
SqlParameter[] param={
new SqlParameter ("@CompanyCD",expApplyModel.CompanyCD ),
new SqlParameter ("@ExpCode",expApplyModel.ExpCode ),
new SqlParameter("@Title",expApplyModel.Title ),
new SqlParameter("@Applyor",expApplyModel.Applyor ),
new SqlParameter("@AriseDate",expApplyModel.AriseDate ),
new SqlParameter("@NeedDate",expApplyModel.NeedDate ),
new SqlParameter("@TotalAmount",expApplyModel.TotalAmount ),
new SqlParameter("@PayType",expApplyModel.PayType ),
new SqlParameter("@Reason",expApplyModel.Reason ),
new SqlParameter("@DeptID",expApplyModel.DeptID ),
new SqlParameter("@TransactorID",expApplyModel.TransactorID ),
new SqlParameter("@Status",expApplyModel.Status ),
new SqlParameter("@Creator",expApplyModel.Creator ),
new SqlParameter("@CreateDate",expApplyModel.CreateDate ),
new SqlParameter("@ExpType",expApplyModel.ExpType ),
new SqlParameter("@CustID",expApplyModel.CustID ),
new SqlParameter("@SellChanceNo",expApplyModel.SellChanceNo ),
new SqlParameter("@IsReimburse",expApplyModel.IsReimburse ),
new SqlParameter("@ModifiedUserID",expApplyModel.ModifiedUserID ),
new SqlParameter("@ModifiedDate",expApplyModel.ModifiedDate ),
new SqlParameter("@Confirmor",DBNull.Value ),
new SqlParameter("@ConfirmDate",DBNull.Value),
new SqlParameter("@Id",SqlDbType.Int,6),
new SqlParameter("@CanViewUser",expApplyModel.CanViewUser),
new SqlParameter("@ProjectID",expApplyModel.ProjectID),
new SqlParameter("@Attachment",expApplyModel.Attachment),
new SqlParameter("@EndReimbTime",expApplyModel.EndReimbTime)
};
param[22].Direction=ParameterDirection.Output;
foreach (SqlParameter para in param)
{
//if (para.Value == null)
if (para.Value == null || para.Value.ToString() == "-1")
{
para.Value = DBNull.Value;
}
}
//SqlHelper.ExecuteSql(strSql.ToString(), param);
SqlHelper.ExecuteTransSql(strSql.ToString(), param);
int Id =Convert.ToInt32(param[22].Value);
return Id;
#endregion
}
/// <summary>
/// 修改费用申请单
/// </summary>
/// <param name="expApplyModel">费用主表Model</param>
/// <param name="expDetailModelList">费用明细表Model</param>
/// <param name="strMsg">消息字符串</param>
/// <returns></returns>
public static bool UpdateExpensesApply(ExpensesApplyModel expApplyModel, List<ExpDetailsModel> expDetailModelList, out string strMsg)
{
return ExpensesDBHelper.UpdateExpensesApply(expApplyModel, expDetailModelList, out strMsg);
}
/// 根据查询条件获取已审批的费用申请单列表
public static DataTable GetExpensesApplyAuditList(ExpensesApplyModel expApplyModel,int empid, DateTime? AriseDate1, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return ExpensesDBHelper.GetExpensesApplyAuditList(expApplyModel,empid, AriseDate1, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 根据查询条件，获取所以符合条件记录的金额合计
/// </summary>
/// <param name="expApplyMOdel">ExpensesApplyModel实体</param>
/// <param name="empid"></param>
/// <param name="AriseDate1"></param>
/// <param name="FlowStatus"></param>
/// <returns></returns>
public static DataTable GetExpSumTotal(ExpensesApplyModel expApplyMOdel, int empid, DateTime? AriseDate1, string FlowStatus)
{
return ExpensesDBHelper.GetExpSumTotal(expApplyMOdel, empid, AriseDate1, FlowStatus);
}
/// <summary>
/// 根据查询条件获取历史费用申请单列表
/// </summary>
/// <param name="expApplyModel">ExpensesApplyModel 实体</param>
/// <param name="AriseDate1">申请日期</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord">排序</param>
/// <param name="TotalCount">总记录数</param>
/// <returns></returns>
public static DataTable GetExpensesApplyHistList(ExpensesApplyModel expApplyModel,int empid, DateTime? AriseDate1, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select exp.ID,exp.ExpCode,exp.Title,exp.Applyor,e1.EmployeeName as ApplyorName,convert(varchar(100), exp.AriseDate, 23) as AriseDate,");
strSql.Append("convert(varchar(100),exp.NeedDate,23) as NeedDate,exp.TotalAmount,exp.DeptID,isnull(d.DeptName,'') as DeptName, ");
strSql.Append("exp.TransactorID,e2.EmployeeName as TransactorName,exp.Status ");//,exp.CurrencyType,isnull(c.CurrencyName,'') as CurrencyName,exp.CurrencyRate ");
strSql.Append(" from officedba.FeeApply exp ");
strSql.Append(" left join officedba.EmployeeInfo e1 on exp.Applyor=e1.ID ");
strSql.Append(" left join officedba.EmployeeInfo e2 on exp.TransactorID=e2.ID ");
strSql.Append(" left join officedba.DeptInfo d on exp.DeptID=d.ID ");
//strSql.Append(" left join officedba.CurrencyTypeSetting c on exp.CurrencyType=c.ID ");
strSql.Append(" where exp.CompanyCD=@CompanyCD ");
strSql.Append(" and ( charindex('," + empid + ",' , ','+exp.CanViewUser+',')>0 or exp.Creator=" + empid + " OR exp.CanViewUser='' OR exp.CanViewUser is null) ");
if (expApplyModel.ExpCode != null)
{
strSql.Append(" and exp.ExpCode like '%" + expApplyModel.ExpCode + "%' ");
}
if (expApplyModel.Title != null)
{
strSql.Append(" and exp.Title like '%" + expApplyModel.Title + "%' ");
}
if (expApplyModel.TransactorID != -1)
{
strSql.Append(" and exp.TransactorID="+expApplyModel.TransactorID );
}
if (expApplyModel.Applyor != -1)
{
strSql.Append(" and exp.Applyor="+expApplyModel.Applyor );
}
if (expApplyModel.AriseDate != null && AriseDate1 != null)
{
strSql.Append(" and (exp.AriseDate >= '"+expApplyModel.AriseDate.ToString().Trim() +"' and exp.AriseDate <= '"+AriseDate1.ToString().Trim() +"' )");
}
if (expApplyModel.Status != null)
{
strSql.Append(" and exp.Status ="+expApplyModel.Status );
}
SqlParameter[] param ={
new SqlParameter("@CompanyCD",expApplyModel.CompanyCD)
};
return SqlHelper.CreateSqlByPageExcuteSql(strSql.ToString(), pageIndex, pageCount, ord, param, ref TotalCount);
}
/// <summary>
/// 根据查询条件，获取所以符合条件记录的金额合计
/// </summary>
/// <param name="expApplyMOdel">ExpensesApplyModel实体</param>
/// <param name="empid"></param>
/// <param name="AriseDate1"></param>
/// <param name="FlowStatus"></param>
/// <returns></returns>
public static DataTable GetExpSumTotal(ExpensesApplyModel expApplyModel, int empid, DateTime? AriseDate1, string FlowStatus)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select sum(exp.TotalAmount) as AllTotalAmount ");
strSql.Append(" from officedba.FeeApply exp ");
strSql.Append(" left join officedba.FlowInstance f on exp.CompanyCD=f.CompanyCD and f.BillTypeFlag=1 ");
strSql.Append(" and f.BillTypeCode=4 and f.BillNo=exp.ExpCode ");
strSql.Append(" and f.ID=(select max(ID) from officedba.FlowInstance as f2 where exp.ID=f2.BillID and f2.BillTypeFlag=1 and f2.BillTypeCode=4 )");
strSql.Append(" where exp.CompanyCD=@CompanyCD ");
strSql.Append(" and ( charindex('," + empid + ",' , ','+exp.CanViewUser+',')>0 or exp.Creator=" + empid + " OR exp.CanViewUser='' OR exp.CanViewUser is null) ");
if (expApplyModel.ExpCode != null)
{
strSql.Append(" and exp.ExpCode like '%" + expApplyModel.ExpCode + "%' ");
}
if (expApplyModel.Title != null)
{
strSql.Append(" and exp.Title like '%" + expApplyModel.Title + "%' ");
}
if (expApplyModel.TransactorID != -1)
{
strSql.Append(" and exp.TransactorID=" + expApplyModel.TransactorID);
}
if (expApplyModel.Applyor != -1)
{
strSql.Append(" and exp.Applyor=" + expApplyModel.Applyor);
}
if (expApplyModel.AriseDate != null && AriseDate1 != null)
{
strSql.Append(" and (exp.AriseDate >= '" + expApplyModel.AriseDate.ToString().Trim() + "' and exp.AriseDate <= '" + AriseDate1.ToString().Trim() + "' )");
}
if (expApplyModel.Status != null)
{
strSql.Append(" and exp.Status =" + expApplyModel.Status);
}
if (expApplyModel.DeptID != -1)
{
strSql.Append(" and exp.DeptID=" + expApplyModel.DeptID);
}
if (expApplyModel.ProjectID != null)
{
strSql.Append(" and exp.ProjectID=" + expApplyModel.ProjectID);
}
if (FlowStatus != null)
{
if (FlowStatus == "0")
{
strSql.Append(" and f.FlowStatus is null ");
}
else
{
strSql.Append(" and f.FlowStatus='" + FlowStatus + "'");
}
}
SqlParameter[] param ={
new SqlParameter("@CompanyCD",expApplyModel.CompanyCD)
};
return SqlHelper.ExecuteSql(strSql.ToString(), param);
}
/// <summary>
/// 根据查询条件获取费用申请单列表
/// 导出列表页调用此方法
/// </summary>
/// <param name="expApplyModel">ExpensesApplyModel 实体</param>
/// <param name="AriseDate1">申请日期</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord">排序</param>
/// <param name="TotalCount">总记录数</param>
/// <returns></returns>
public static DataTable GetExpensesApplyList(ExpensesApplyModel expApplyModel,int empid, DateTime? AriseDate1,string FlowStatus, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select exp.ID,exp.ExpCode,exp.Title,exp.Applyor,e1.EmployeeName as ApplyorName,");
strSql.Append(" convert(varchar(100), exp.AriseDate, 23) as AriseDate,convert(varchar(100),exp.NeedDate,23) as NeedDate, ");
strSql.Append(" exp.TotalAmount,exp.DeptID,isnull(d.DeptName,'') as DeptName,exp.TransactorID,e2.EmployeeName as TransactorName,exp.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(" ,exp.Confirmor,e3.EmployeeName as ConfirmorName,convert(varchar(100),exp.ConfirmDate,23) as ConfirmDate ");
//strSql.Append(" ,exp.ProjectID,p,ProjectName ");
strSql.Append(" ,exp.IsReimburse,case exp.IsReimburse when 1 then '是' when 0 then '否' end as IsReimburseText ");
strSql.Append(" from officedba.FeeApply exp ");
strSql.Append(" left join officedba.EmployeeInfo e1 on exp.Applyor=e1.ID ");
strSql.Append(" left join officedba.EmployeeInfo e2 on exp.TransactorID=e2.ID ");
strSql.Append(" left join officedba.EmployeeInfo e3 on exp.Confirmor=e3.ID ");
strSql.Append(" left join officedba.DeptInfo d on exp.DeptID=d.ID ");
//strSql.Append(" left join officedba.ProjectInfo p on p.ID=exp.ProjectID ");
strSql.Append(" left join officedba.FlowInstance f on exp.CompanyCD=f.CompanyCD and f.BillTypeFlag=1 ");
strSql.Append(" and f.BillTypeCode=4 and f.BillNo=exp.ExpCode ");
strSql.Append(" and f.ID=(select max(ID) from officedba.FlowInstance as f2 where exp.ID=f2.BillID and f2.BillTypeFlag=1 and f2.BillTypeCode=4 )");
strSql.Append(" where exp.CompanyCD=@CompanyCD ");
strSql.Append(" and ( charindex('," + empid + ",' , ','+exp.CanViewUser+',')>0 or exp.Creator=" + empid + " OR exp.CanViewUser='' OR exp.CanViewUser is null) ");
if (expApplyModel.ExpCode != null)
{
strSql.Append(" and exp.ExpCode like '%" + expApplyModel.ExpCode + "%' ");
}
if (expApplyModel.Title != null)
{
strSql.Append(" and exp.Title like '%" + expApplyModel.Title + "%' ");
}
if (expApplyModel.TransactorID != -1)
{
strSql.Append(" and exp.TransactorID="+expApplyModel.TransactorID );
}
if (expApplyModel.Applyor != -1)
{
strSql.Append(" and exp.Applyor="+expApplyModel.Applyor );
}
if (expApplyModel.AriseDate != null && AriseDate1 != null)
{
strSql.Append(" and (exp.AriseDate >= '"+expApplyModel.AriseDate.ToString().Trim() +"' and exp.AriseDate <= '"+AriseDate1.ToString().Trim() +"' )");
}
if (expApplyModel.Status != null)
{
strSql.Append(" and exp.Status ="+expApplyModel.Status );
}
if (expApplyModel.DeptID != -1)
{
strSql.Append(" and exp.DeptID="+expApplyModel.DeptID );
}
if (expApplyModel.ProjectID != null)
{
strSql.Append(" and exp.ProjectID=" + expApplyModel.ProjectID);
}
if (expApplyModel.IsReimburse != null)
{
strSql.Append(" and exp.IsReimburse='"+expApplyModel.IsReimburse+"'");
}
if (FlowStatus != null)
{
if (FlowStatus == "0")
{
strSql.Append(" and f.FlowStatus is null ");
}
else
{
strSql.Append(" and f.FlowStatus='" + FlowStatus + "'");
}
}
SqlParameter[] param ={
new SqlParameter("@CompanyCD",expApplyModel.CompanyCD)
};
return SqlHelper.CreateSqlByPageExcuteSql(strSql.ToString(), pageIndex, pageCount, ord, param, ref TotalCount);
}
/// <summary>
/// 修改费用申请单主表信息
/// </summary>
/// <param name="expApplyModel">费用主表Model</param>
/// <param name="tran"></param>
public static void UpdateExpApply(ExpensesApplyModel expApplyModel, TransactionManager tran)
{
StringBuilder strSql=new StringBuilder ();
strSql.Append("update officedba.FeeApply set ");
strSql.Append(" Title=@Title,Applyor=@Applyor,AriseDate=@AriseDate,NeedDate=@NeedDate,TotalAmount=@TotalAmount,");
strSql.Append(" PayType=@PayType,Reason=@Reason,CanViewUser=@CanViewUser,");
strSql.Append(" DeptID=@DeptID,TransactorID=@TransactorID,ExpType=@ExpType,CustID=@CustID,");
strSql.Append(" SellChanceNo=@SellChanceNo,ModifiedUserID=@ModifiedUserID,ModifiedDate=@ModifiedDate,");
strSql.Append(" ProjectID=@ProjectID,Attachment=@Attachment,EndReimbTime=@EndReimbTime ");
strSql.Append(" where ID=@ID ");
SqlParameter [] param={
new SqlParameter("@ID",expApplyModel.ID ),
new SqlParameter("@Title",expApplyModel.Title ),
new SqlParameter("@Applyor",expApplyModel.Applyor),
new SqlParameter("@AriseDate",expApplyModel.AriseDate),
new SqlParameter("@NeedDate",expApplyModel.NeedDate),
new SqlParameter("@TotalAmount",expApplyModel.TotalAmount),
new SqlParameter("@PayType",expApplyModel.PayType),
//new SqlParameter("@CurrencyType",expApplyModel.CurrencyType),
//new SqlParameter("@CurrencyRate",expApplyModel.CurrencyRate),
new SqlParameter("@Reason",expApplyModel.Reason),
new SqlParameter("@DeptID",expApplyModel.DeptID),
new SqlParameter("@TransactorID",expApplyModel.TransactorID),
new SqlParameter("@ExpType",expApplyModel.ExpType),
new SqlParameter("@CustID",expApplyModel.CustID),
new SqlParameter("@SellChanceNo",expApplyModel.SellChanceNo),
new SqlParameter("@ModifiedUserID",expApplyModel.ModifiedUserID),
new SqlParameter("@ModifiedDate",expApplyModel.ModifiedDate),
new SqlParameter("@CanViewUser",expApplyModel.CanViewUser),
new SqlParameter("@ProjectID",expApplyModel.ProjectID),
new SqlParameter("@Attachment",expApplyModel.Attachment),
new SqlParameter("@EndReimbTime",expApplyModel.EndReimbTime)
};
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="expApplyModel">费用主表Model</param>
/// <param name="expDetailModelList">费用明细表Model</param>
/// <param name="strMsg">消息字符串</param>
/// <returns></returns>
public static bool UpdateExpensesApply(ExpensesApplyModel expApplyModel, List<ExpDetailsModel> expDetailModelList, out string strMsg)
{
bool isSucc = false;//是否修改成功
strMsg = "";
if (IsUpdate(expApplyModel.ExpCode , expApplyModel.CompanyCD))
{
string strSql = "delete from officedba.FeeApplyDetail where ExpID=@ExpID ";
SqlParameter[] paras = { new SqlParameter("@ExpID", expApplyModel.ID)};
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateExpApply(expApplyModel, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
InsertExpensesDetails(expDetailModelList,expApplyModel.ID, tran);
tran.Commit();
isSucc = true;
strMsg = "修改成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "修改失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "非制单状态的单据不可修改！";
}
return isSucc;
}