using System.Collections.Generic;
using System.Linq;
using System.Text;
using Edu.Common.Enum.Finance;
using Edu.Model.Entity.Finance;
using Edu.Model.ViewModel.Finance;
namespace Edu.Repository.Finance
{
/// <summary>
/// 财务单据仓储层
/// </summary>
public class RB_FinanceRepository : BaseRepository<RB_Finance>
{
/// <summary>
/// 表名称
/// </summary>
public string TableName { get { return nameof(RB_Finance); } }
/// <summary>
/// 记录表
/// </summary>
public string ReCordTableName { get { return nameof(Rb_Workflow_Auditrecord); } }
/// <summary>
/// 财务单据 主表查询
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_Finance_Extend> GetListSingle(RB_Finance_Extend model)
{
string where = "1=1 AND A.Is_Auto=0 ";
//单据ID
if (model.FrID > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " ={0}", model.FrID);
}
//集团
if (model.RB_Group_Id > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.RB_Group_Id) + " ={0}", model.RB_Group_Id);
}
if (model.RB_Branch_Id >= 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.RB_Branch_Id) + " ={0}", model.RB_Branch_Id);
}
if (model.TCID > 0)
{
where += string.Format(" AND A.FrID in (select b.FinanceId from rb_travel_finance_relation as b where b.FinanceId=FrID and b.TCID=" + model.TCID + ")");
}
if (!string.IsNullOrEmpty(model.TCIDStr))
{
where += string.Format(" AND A.FrID in (select b.FinanceId from rb_travel_finance_relation as b where b.FinanceId=FrID and b.TCID in(" + model.TCIDStr + "))");
}
if (model.BankList != null && model.BankList.Count > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in (SELECT b.FinanceId from rb_tradeway as b where b.FinanceId=A.FrID and Type=" + (int)model.BankList?.FirstOrDefault()?.Type + " and AccountId=" + model.BankList?.FirstOrDefault().AccountId + " Group By b.FinanceId) ");
}
//查询收款 并且账户类形一致的单据
if (!string.IsNullOrWhiteSpace(model.AccountIdStr))
{
where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in (SELECT b.FinanceId from rb_tradeway as b where b.FinanceId=A.FrID and Type=1 and AccountId in(" + model.AccountIdStr + ") Group By b.FinanceId) ");
}
if (model.ClientID > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.ClientID) + " ={0}", model.ClientID);
}
if (!string.IsNullOrEmpty(model.sTradeDate))
{
where += string.Format(" AND A." + nameof(RB_Finance_Extend.TradeDate) + ">='{0}'", model.sTradeDate);
}
if (!string.IsNullOrEmpty(model.eTradeDate))
{
where += string.Format(" AND A." + nameof(RB_Finance_Extend.TradeDate) + "<='{0} 23:59:59'", model.eTradeDate);
}
if (model.Is_Cashier >= 0)
{
where += string.Format(" AND A." + nameof(RB_Finance_Extend.Is_Cashier) + "={0}", model.Is_Cashier);
}
if (model.OrderID > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.OrderID) + " ={0}", model.OrderID);
}
if (!string.IsNullOrWhiteSpace(model.OrderIdStr))
{
where += string.Format(" AND A." + nameof(RB_Finance.OrderID) + " in({0})", model.OrderIdStr);
}
if (model.GuestId > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.GuestId) + " ={0}", model.GuestId);
}
if (!string.IsNullOrWhiteSpace(model.GuestIdStr))
{
where += string.Format(" AND A." + nameof(RB_Finance.GuestId) + " in({0})", model.GuestIdStr);
}
if (model.Type > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.Type) + " ={0}", (int)model.Type);
}
if (model.FinanceType.HasValue && model.FinanceType > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.FinanceType) + " ={0}", model.FinanceType);
}
if (model.OtherType > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.OtherType) + " ={0}", model.OtherType);
}
if (model.ReFinanceId > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.ReFinanceId) + " ={0}", model.ReFinanceId);
}
if (!string.IsNullOrEmpty(model.ReFinanceIds))
{
where += string.Format(" AND A." + nameof(RB_Finance.ReFinanceId) + " in({0})", model.ReFinanceIds);
}
if (model.Status > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.Status) + " ={0}", (int)model.Status);
}
if (model.OrderSource > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.OrderSource) + " ={0}", (int)model.OrderSource);
}
if (model.SourceID > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.SourceID) + " ={0}", model.SourceID);
}
if (!string.IsNullOrEmpty(model.AccountNumber))
{
where += string.Format(" AND A." + nameof(RB_Finance.AccountNumber) + " ='{0}'", model.AccountNumber);
}
if (!string.IsNullOrEmpty(model.FrIDs))
{
where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in ({0})", model.FrIDs);
}
if (!string.IsNullOrWhiteSpace(model.DijieOnlyCode))
{
where += string.Format(" AND A." + nameof(RB_Finance.DijieOnlyCode) + " ='{0}'", model.DijieOnlyCode);
}
if (model.IsSelectNormal == 1)
{
where += string.Format(" AND A." + nameof(RB_Finance.Status) + " in(1,2,5)");
}
string sql = string.Format(@"SELECT A.* FROM {0} AS A WHERE {2} {3}", TableName, ReCordTableName, where, " GROUP BY A.FrID DESC");
return Get<RB_Finance_Extend>(sql).ToList();
}
/// <summary>
/// 获取营收报表财务单据列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_Finance_Extend> GetDataStatisticsListThree(RB_Finance_Extend model)
{
string where = "1=1 and A.Is_Auto=0";
where += string.Format(" AND A." + nameof(RB_Finance.Status) + " in({0})", ((int)FinanceAuditStatus.CTemporary + "," + (int)FinanceAuditStatus.InReview + "," + (int)FinanceAuditStatus.Pass));
//营收报表
if (!string.IsNullOrEmpty(model.TCIDStr))
{
where += string.Format(" AND tfr.TCID in(" + model.TCIDStr + ")");
}
if (model.TCID.HasValue && model.TCID > 0)
{
where += string.Format(" AND tfr.TCID =" + model.TCID);
}
//简易报表
if (model.sAduitDate.HasValue)
{
where += string.Format(" AND A." + nameof(RB_Finance.TradeDate) + ">='{0}'", model.sAduitDate);
}
if (model.eAduitDate.HasValue)
{
where += string.Format(" AND A." + nameof(RB_Finance.TradeDate) + "<='{0} 23:59:59'", model.eAduitDate);
}
if (model.RB_Branch_Id >= 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.RB_Branch_Id) + "={0}", model.RB_Branch_Id);
}
if (model.RB_Group_Id > 0)
{
where += string.Format(" AND A." + nameof(RB_Finance.RB_Group_Id) + "={0}", model.RB_Group_Id);
}
if (model.OrderSource.HasValue)
{
where += string.Format(" AND A." + nameof(RB_Finance.OrderSource) + "={0}", (int)model.OrderSource);
}
string sql = string.Format(@"SELECT A.FrID,tfr.TCID,A.Status,A.TradeDate,IFNULL(A.Is_Cashier,0)Is_Cashier,A.OrderID,
A.OrderSource,A.SourceID,IFNULL(C.Money,0)as Money,IFNULL(A.Fee,0) as Fee,IFNULL(A.PayMoney,0)PayMoney,C.CostTypeId as CostTypeID,D.Name,A.Type
FROM rb_finance AS A
INNER JOIN rb_financedetail AS C ON A.FrID = C.FinanceId
INNER JOIN rb_costtype AS D ON D.ID=C.CostTypeId
left join rb_travel_finance_relation as tfr on tfr.FinanceId=A.FrID
WHERE {1} {2} ", TableName, where, " ORDER BY A.FrID DESC");
return Get<RB_Finance_Extend>(sql).ToList();
}
/// <summary>
/// 营收报表获取团号下所有的单据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_Finance_Extend> GetListForReport(RB_Finance_Extend model)
{
StringBuilder where = new StringBuilder();
where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 ");
StringBuilder builder = new StringBuilder();
//老徐喊修改的 2019-05-11 收款用交易日期 付款用制单日期
if (model.sAduitDate.HasValue && model.eAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b.CreateDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b.CreateDate<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b.TradeDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
}
else if (model.sAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' else b." + nameof(RB_Finance.TradeDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' end");
}
else if (model.eAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
}
if (model.RB_Branch_Id >= 0)
{
where.Append(" AND b." + nameof(RB_Finance.RB_Branch_Id) + "=" + model.RB_Branch_Id + "");
}
if (model.RB_Group_Id > 0)
{
where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
}
if (!string.IsNullOrEmpty(model.TCIDStr))
{
where.Append(" and E.TCID in(" + model.TCIDStr + ")");
}
if (model.TCID == -1)
{
where.Append(" and IFNULL(E.TCID,0)=0");
}
if (model.TCID == -2)
{
where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
where.Append(" and IFNULL(b.OrderID,0) =0");
}
if (model.IsSelectOrder == 1)
{
where.Append(" and IFNULL(b.OrderID,0) >0");//只查询订单相关单据
}
if (model.OrderSource.HasValue)
{
where.Append(" and IFNULL(b.OrderSource,0) =" + (int)model.OrderSource);
}
if (model.FinanceType.HasValue && model.FinanceType > 0)
{
where.Append(" AND b." + nameof(RB_Finance.FinanceType) + "=" + model.FinanceType + "");
}
builder.AppendFormat(@"select distinct b.FrID,IFNULL(b.MatchMoney,0) as MatchMoney,IFNULL(E.TCID,0)TCID,D.Name,b.Status,b.TradeDate,b.CreateBy,b.CreateDate,b.RB_Branch_Id,a.ID,a.CostTypeId,IFNULL(b.Is_Cashier,0)Is_Cashier,a.Money,IFNULL(b.OrderID,0) OrderID,b.OrderSource,b.SourceID,IFNULL(b.PayMoney,0)PayMoney,IFNULL(b.Fee,0)Fee,b.Type
from rb_financedetail a INNER JOIN rb_finance as b on a.FinanceId=b.FrID and a.`Status`=0
INNER JOIN rb_costtype AS D ON D.ID=a.CostTypeId
LEFT JOIN rb_tradeway as td ON b.FrID=td.FinanceId
LEFT JOIN rb_travel_finance_relation as E ON A.FinanceId=E.FinanceId where 1=1 " + where);
return Get<RB_Finance_Extend>(builder.ToString() + " ORDER BY b.FrID DESC ").ToList();
}
/// <summary>
/// 班级专用查询
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_Finance_Extend> GetListForReportClass(RB_Finance_Extend model, string datebaseStr)
{
StringBuilder where = new StringBuilder();
where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 ");
StringBuilder builder = new StringBuilder();
//老徐喊修改的 2019-05-11 收款用交易日期 付款用制单日期
if (model.sAduitDate.HasValue && model.eAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b.CreateDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b.CreateDate<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b.TradeDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
}
else if (model.sAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' else b." + nameof(RB_Finance.TradeDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' end");
}
else if (model.eAduitDate.HasValue)
{
where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
}
if (model.RB_Group_Id > 0)
{
where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
}
if (!string.IsNullOrEmpty(model.TCIDStr))
{
where.Append(" and E.TCID in(" + model.TCIDStr + ")");
}
if (model.TCID == -1)
{
where.Append(" and IFNULL(E.TCID,0)=0");
}
else if (model.TCID == -2)
{
where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
where.Append(" and IFNULL(b.OrderID,0) =0");
}
else if (model.TCID == -3)
{
where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
where.Append(" and (IFNULL(b.OrderID,0) =0 or (IFNULL(b.OrderID,0) >0 and b.OtherType =45))");
}
if (model.RB_Branch_Id >= 0)
{
where.Append(" AND c.School_Id =" + model.RB_Branch_Id);
}
if (model.OrderSource.HasValue)
{
where.Append(" and IFNULL(b.OrderSource,0) =" + (int)model.OrderSource);
}
if (model.FinanceType.HasValue && model.FinanceType > 0)
{
where.Append(" AND b." + nameof(RB_Finance.FinanceType) + "=" + model.FinanceType + "");
}
builder.AppendFormat($@"select distinct b.FrID,IFNULL(b.MatchMoney,0) as MatchMoney,IFNULL(E.TCID,0)TCID,D.Name,b.Status,b.TradeDate,b.CreateDate,b.RB_Branch_Id,a.ID,a.CostTypeId,IFNULL(b.Is_Cashier,0)Is_Cashier,a.Money,IFNULL(b.OrderID,0) OrderID,b.OrderSource,b.SourceID,IFNULL(b.PayMoney,0)PayMoney,IFNULL(b.Fee,0)Fee,b.Type
from rb_financedetail a INNER JOIN rb_finance as b on a.FinanceId=b.FrID and a.`Status`=0
INNER JOIN rb_costtype AS D ON D.ID=a.CostTypeId
LEFT JOIN rb_tradeway as td ON b.FrID=td.FinanceId
LEFT JOIN rb_travel_finance_relation as E ON A.FinanceId=E.FinanceId
left join {datebaseStr}.rb_class c on c.ClassId = E.TCID
where 1=1 " + where);
return Get<RB_Finance_Extend>(builder.ToString() + " ORDER BY b.FrID DESC ").ToList();
}
/// <summary>
/// 获取资金调拨 汇兑损益
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_TradeWay_Extend> GetTradeWayForCapitalAllocation(RB_Finance_Extend model)
{
StringBuilder where = new StringBuilder();
where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 and td.IsExChange=1 and td.Status=0 and b.Type=4 ");
StringBuilder builder = new StringBuilder();
if (model.sAduitDate.HasValue)
{
where.Append(" AND td." + nameof(RB_TradeWay_Extend.AllotDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "'");
}
if (model.eAduitDate.HasValue)
{
where.Append(" AND td." + nameof(RB_TradeWay_Extend.AllotDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' ");
}
if (model.RB_Branch_Id >= 0)
{
where.Append(" AND b." + nameof(RB_Finance.RB_Branch_Id) + "=" + model.RB_Branch_Id + "");
}
if (model.RB_Group_Id > 0)
{
where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
}
builder.AppendFormat(@"select td.*,b.RB_Branch_Id as BranchId from rb_tradeway td
left join rb_finance b on td.FinanceId=b.FrID where 1=1 " + where);
return Get<RB_TradeWay_Extend>(builder.ToString()).ToList();
}
}
}