using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Model;
|
|
namespace CY.SQLDAL
|
{
|
public class Pay_IncExpRecordDAL : IPay_IncExpRecordDAL
|
{
|
|
private Database _dataBase = null;
|
|
public Pay_IncExpRecordDAL()
|
{
|
_dataBase = new Database();
|
}
|
public Pay_IncExpRecordDAL(Database database)
|
{
|
_dataBase = database;
|
}
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@PayId",trueModel.PayId),
|
new SqlParameter("@TradingName",trueModel.TradingName),
|
new SqlParameter("@TradingType",trueModel.TradingType),
|
new SqlParameter("@TradingState",trueModel.TradingState),
|
new SqlParameter("@TradingMoney",trueModel.TradingMoney),
|
new SqlParameter("@ResidualMoney",trueModel.ResidualMoney),
|
new SqlParameter("@PayerName",trueModel.PayerName),
|
new SqlParameter("@PayeeId",trueModel.PayeeId),
|
new SqlParameter("@PayeeName",trueModel.PayeeName),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Remark",trueModel.Remark),
|
new SqlParameter("@BalanceAccount",trueModel.BalanceAccount)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_IncExpRecord_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{ new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@PayId",trueModel.PayId),
|
new SqlParameter("@TradingName",trueModel.TradingName),
|
new SqlParameter("@TradingType",trueModel.TradingType),
|
new SqlParameter("@TradingState",trueModel.TradingState),
|
new SqlParameter("@TradingMoney",trueModel.TradingMoney),
|
new SqlParameter("@ResidualMoney",trueModel.ResidualMoney),
|
new SqlParameter("@PayerName",trueModel.PayerName),
|
new SqlParameter("@PayeeId",trueModel.PayeeId),
|
new SqlParameter("@PayeeName",trueModel.PayeeName),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_IncExpRecord_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.Pay_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_Pay_IncExpRecord_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 根据账户编号分页查询(type:支出-1收入1)
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="PayId"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectModelPage(Infrastructure.Query.Pagination pagination, int? PayId,int? Type)
|
{
|
if (PayId == null || PayId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string condtion = "";
|
switch (Type)
|
{
|
case -1:
|
condtion = string.Format(" PayId = '{0}' and TradingType={1}", PayId, Type);
|
break;
|
case 1:
|
condtion = string.Format(" PayeeId ='{0}' and TradingType={1}", PayId, Type);
|
break;
|
default:
|
break;
|
}
|
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, "*", "Pay_IncExpRecord", "CreateTime DESC", "CreateTime DESC", condtion) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 分页返回全部流水账
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="payId"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectAllModel(Infrastructure.Query.Pagination pagination, int? PayId)
|
{
|
if (PayId == null || PayId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string condtion = "";
|
condtion = string.Format(" (TradingType=-1 AND PayId={0}) OR (TradingType=1 AND PayeeId={0}) ", PayId);
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, "*", "Pay_IncExpRecord", "CreateTime DESC", "CreateTime DESC,TradingType desc", condtion) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
|
}
|
|
/// <summary>
|
/// 根据条件分页返回全部流水账
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="payId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="tradingType"></param>
|
/// <param name="balanceAccount"></param>
|
/// <param name="memberType"></param>
|
/// <param name="memberName"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectAllModelByPaging(Infrastructure.Query.Pagination pagination, int? payId, string startDate, string endDate, int tradingType, int balanceAccount, string memberType, string memberName, string province, string city, string county)
|
{
|
if (payId == null || payId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string selectTarget = "t.*";
|
string fromSouce = @"(
|
SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
|
FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.keyid INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
|
and d.DicType='收支科目'
|
WHERE TradingType=1
|
UNION
|
SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
|
FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayeeId=b.keyid INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
|
and d.DicType='收支科目'
|
WHERE TradingType=-1
|
) t ";
|
fromSouce += string.Format(" where ((t.TradingType=-1 AND t.PayId={0}) OR (t.TradingType=1 AND t.PayeeId={0}) or (t.TradingType=-1 AND t.PayeeId={0})) ", payId);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
fromSouce += " and t.CreateTime>='" + startDate + "' ";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
fromSouce += " and t.CreateTime<'" + endDate + "' ";
|
}
|
if (tradingType!=0)
|
{
|
fromSouce += " and t.TradingType='" + tradingType + "' ";
|
}
|
if (balanceAccount != -1)
|
{
|
fromSouce += " and t.balanceAccount='" + balanceAccount + "' ";
|
}
|
if (!string.IsNullOrEmpty(memberType))
|
{
|
fromSouce += " and t.MemberTypeName='" + memberType + "' ";
|
}
|
if (!string.IsNullOrEmpty(memberName))
|
{
|
fromSouce += " and t.MemberName like '%" + memberName + "%'";
|
}
|
if (!string.IsNullOrEmpty(province))
|
{
|
fromSouce += " and t.Province='"+province+"'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
fromSouce += " and t.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
fromSouce += " and t.County='" + county + "'";
|
}
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.CreateTime DESC", "CreateTime DESC,Keyid desc", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 获取已收佣金明细
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="payId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="province"></param>
|
/// <param name="city"></param>
|
/// <param name="county"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectAllModelByPaging(Infrastructure.Query.Pagination pagination, int? payId, string startDate, string endDate, string province, string city, string county)
|
{
|
if (payId == null || payId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string selectTarget = "t.*";
|
string fromSouce = @"(
|
SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
|
FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayId=b.keyid INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
|
and d.DicType='收支科目'
|
WHERE TradingType=1
|
UNION
|
SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
|
FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.PayeeId=b.keyid INNER JOIN dbo.EC_MemberBasic c
|
ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
|
and d.DicType='收支科目'
|
WHERE TradingType=-1
|
) t ";
|
fromSouce += string.Format(" where ((t.TradingType=-1 AND t.PayId={0}) OR (t.TradingType=1 AND t.PayeeId={0}) or (t.TradingType=-1 AND t.PayeeId={0})) ", payId);
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
fromSouce += " and t.CreateTime>='" + startDate + "' ";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
fromSouce += " and t.CreateTime<'" + endDate + "' ";
|
}
|
|
fromSouce += " and t.TradingType=1 ";
|
|
fromSouce += " and (t.balanceAccount=3 or t.balanceAccount=10) ";
|
|
|
if (!string.IsNullOrEmpty(province))
|
{
|
fromSouce += " and t.Province='" + province + "'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
fromSouce += " and t.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
fromSouce += " and t.County='" + county + "'";
|
}
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.CreateTime DESC", "CreateTime DESC,Keyid desc", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 查询欠款明细
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="province"></param>
|
/// <param name="city"></param>
|
/// <param name="county"></param>
|
/// <returns></returns>
|
public IEnumerable<Pay_OwedMoney> selectAllOwedMoneyList(Infrastructure.Query.Pagination pagination,string startDate, string endDate, string province, string city, string county)
|
{
|
string selectTarget = " c.Name as MemberName,a.OwedAmount,a.Subject,a.CreateTime ";
|
|
string fromSouce = @" dbo.Pay_OwedMoney a INNER JOIN dbo.Pay_PaymentAccount b
|
ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
|
WHERE a.IsEffective=1";
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
fromSouce += " and a.CreateTime>='" + startDate + "'";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
fromSouce += " and a.CreateTime<'" + endDate + "'";
|
}
|
if (!string.IsNullOrEmpty(province))
|
{
|
fromSouce += " and c.Province='" + province + "'";
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
fromSouce += " and c.City='" + city + "'";
|
}
|
if (!string.IsNullOrEmpty(county))
|
{
|
fromSouce += " and c.County='" + county + "'";
|
}
|
|
var result = _dataBase.SelectModelPage<Model.Pay_OwedMoney>(pagination, selectTarget, fromSouce, " a.CreateTime DESC ", " CreateTime DESC ", string.Empty);//执行查询
|
return null == result ? null : result;//返回结果
|
}
|
|
|
/// <summary>
|
/// 查询时间段内会员授信情况
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectAllCreditByPaging(Infrastructure.Query.Pagination pagination, int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
if (currentPayId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string WhereString = " 1=1 ";
|
string WhereString1 = "";
|
string WhereString2 = "";
|
string selectTarget = "t.*";
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
WhereString += " and a.Province='" + selectProvince + "' ";
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
WhereString += " and a.City='" + selectCity + "' ";
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
WhereString += " and a.County='" + selectCounty + "' ";
|
}
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
WhereString2 += " and d.CreateTime >= '" + startDate + "' ";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
WhereString1 += " and c.CreateTime< '" + endDate + "' ";
|
WhereString2 += " and d.CreateTime< '" + endDate + "' ";
|
}
|
|
string fromSouce = @" ( select * from ( SELECT a.Name as TradingName,SUM(ISNULL(c.TradingMoney,0)) AS TradingMoney,SUM(ISNULL(d.TradingMoney,0)) AS ResidualMoney FROM dbo.EC_MemberBasic a INNER JOIN dbo.Pay_PaymentAccount b ON a.MemberId = b.MemberId LEFT JOIN dbo.Pay_CreditRecard c ON b.Keyid = c.PayId AND c.PayId <> 23 " + WhereString1 + " LEFT JOIN dbo.Pay_IncExpRecord d ON b.Keyid = d.PayId AND d.TradingType=-1 AND d.PayId=23 AND d.BalanceAccount=9 " + WhereString2 + " where " + WhereString + " GROUP BY a.Name ) as m where m.TradingMoney > 0 or ( m.TradingMoney = 0 and m.ResidualMoney <> 0) ) t ";
|
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.TradingName DESC", "TradingName DESC", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 查询时间段内会员授信使用
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="currentPayId"></param>
|
/// <param name="startDate"></param>
|
/// <param name="endDate"></param>
|
/// <param name="selectProvince"></param>
|
/// <param name="selectCity"></param>
|
/// <param name="selectCounty"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.Pay_IncExpRecord> SelectUseCreditByPaging(Infrastructure.Query.Pagination pagination, int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
|
{
|
if (currentPayId <= 0)
|
return null;//错误数据返会空
|
try
|
{
|
string WhereString = "";
|
string selectTarget = "t.*";
|
if (!string.IsNullOrEmpty(selectProvince))
|
{
|
WhereString += " and a.Province='" + selectProvince + "' ";
|
}
|
|
if (!string.IsNullOrEmpty(selectCity))
|
{
|
WhereString += " and a.City='" + selectCity + "' ";
|
}
|
|
if (!string.IsNullOrEmpty(selectCounty))
|
{
|
WhereString += " and a.County='" + selectCounty + "' ";
|
}
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
WhereString += " and d.CreateTime >= '" + startDate + "' ";
|
}
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
WhereString += " and d.CreateTime< '" + endDate + "' ";
|
}
|
|
string fromSouce = @"( SELECT d.* FROM dbo.Pay_IncExpRecord d LEFT JOIN dbo.EC_MemberBasic a INNER JOIN dbo.Pay_PaymentAccount b ON a.MemberId = b.MemberId ON b.Keyid = d.PayId WHERE d.TradingType=-1 AND d.PayId=23 AND d.BalanceAccount=9 " + WhereString + " ) t ";
|
|
IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.TradingName DESC", "TradingName DESC", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
|
return null == result ? null : result;//返回结果
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
}
|
}
|
