/// <summary>
/// 根据过滤条件，返回检索件数的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetCountSql(BaseFilter baseFilter)
{
CancelRefundFilter filter = baseFilter as CancelRefundFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(@" SELECT COUNT(1)
FROM finance.CancelRefund c
INNER JOIN dbo.Business b
ON c.BusinessID = b.BusinessID
INNER JOIN customer.CustomerInfo r
ON b.CustomerID = r.CustId
INNER JOIN customer.Person pe
ON r.PersonId = pe.PersonId");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
return(sb.ToString());
}
/// <summary>
/// 服务执行
/// </summary>
/// <param name="requestEntity">请求数据</param>
/// <param name="responseEntity">返回数据</param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
IDictionary <string, string> paraDict = requestEntity.Parameters;
CancelRefundFilter filter = ServiceUtility.ConvertToFilterFromDict <CancelRefundFilter>(paraDict);
// 更新数据
Singleton <BaseUpdateBLL <CancelRefundUpdateDal> > .Instance.UpdateData(filter, responseEntity);
}
/// <summary>
/// 服务执行
/// </summary>
/// <param name="requestEntity">请求数据</param>
/// <param name="responseEntity">返回数据</param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
IDictionary <string, string> paraDict = requestEntity.Parameters;
CancelRefundFilter filter = ServiceUtility.ConvertToFilterFromDict <CancelRefundFilter>(paraDict);
List <CancelRefundViewData> cancelRefundList = Singleton <CancelRefundDal> .Instance.GetCancelRefund(filter);
ResponseListResult <CancelRefundViewData> result = new ResponseListResult <CancelRefundViewData>();
result.TotalCount = filter.RecordCount;
result.LstResult = cancelRefundList;
responseEntity.ResponseStatus = (int)EnumResponseState.Success;
responseEntity.Results = result;
}
/// <summary>
/// 根据过滤条件，返回检索数据的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetSearchSql(BaseFilter baseFilter)
{
CancelRefundFilter filter = baseFilter as CancelRefundFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(@" SELECT * FROM
(SELECT b.BusinessID ,
b.ContractNo ,
pe.PersonName AS CustomerName ,
b.LoanCapital - ISNULL(b.ProceduresAmout, 0) AS RealLoanCapital ,
ISNULL(c.RefundAmt, b.LoanCapital - ISNULL(b.ProceduresAmout, 0)) AS RefundAmt ,
c.PayDate ,
c.ReceivedDate ,
c.PayType ,
c.CancelTime ,
ROW_NUMBER() OVER ( ORDER BY c.CancelTime ) AS num
FROM finance.CancelRefund c
INNER JOIN dbo.Business b ON c.BusinessID = b.BusinessID
INNER JOIN customer.CustomerInfo r ON b.CustomerID = r.CustId
INNER JOIN customer.Person pe ON r.PersonId = pe.PersonId ");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
sb.Append(" ) t");
sb.AppendFormat(" WHERE t.num > {0} AND t.num <= {1}", filter.FromIndex, filter.ToIndex);
sb.Append(" ORDER BY t.num");
return(sb.ToString());
}
/// <summary>
/// Author:shwang
/// Date:20140617
/// Desc:得到解约退款分页数据
/// </summary>
/// <param name="filter">过滤条件</param>
/// <param name="pageOption">分页信息</param>
/// <returns>解约退款数据</returns>
public List <CancelRefundViewData> GetCancelRefund(CancelRefundFilter filter)
{
string payDate = string.Empty;
if (filter.HasPayDate)
{
payDate = " c.PayDate IS NOT NULL ";
}
else
{
payDate = " c.PayDate IS NULL ";
}
StringBuilder conditionSbl = new StringBuilder();
if (!filter.LendingSideKey.IsNullString())
{
conditionSbl.AppendFormat(" AND b.LendingSideKey ='{0}'", filter.LendingSideKey);
}
if (filter.BusinessID.HasValue)
{
conditionSbl.AppendFormat(" AND c.BusinessID ={0}", filter.BusinessID.Value);
}
if (!string.IsNullOrEmpty(filter.ContractNo))
{
conditionSbl.AppendFormat(" AND b.ContractNo ='{0}'", filter.ContractNo);
}
if (!string.IsNullOrEmpty(filter.CustomerName))
{
conditionSbl.AppendFormat(" AND pe.PersonName ='{0}'", filter.CustomerName);
}
if (filter.CancelBeginTime.HasValue)
{
conditionSbl.AppendFormat(" AND c.CancelTime >='{0}'", filter.CancelBeginTime.Value.ToDateTimeString());
}
if (filter.CancelEndTime.HasValue)
{
conditionSbl.AppendFormat(" AND c.CancelTime <'{0}'", filter.CancelEndTime.Value.ToDateTimeString());
}
string regionJoin = string.Empty;
if (!string.IsNullOrEmpty(filter.Region))
{
regionJoin = @"LEFT JOIN dbo.ConstSysEnum s ON s.super=2619 AND
((LEN(b.ContractNo)=7 AND s.VALUE='02') OR
(LEN(b.ContractNo)=15 AND SUBSTRING(b.ContractNo,3,2)=s.VALUE) OR
(LEN(b.ContractNo)=18 AND SUBSTRING(b.ContractNo,6,2)=s.VALUE)
)";
conditionSbl.AppendFormat(" AND s.fullkey in({0})", filter.Region);
}
if (filter.PayBeginTime.HasValue)
{
conditionSbl.AppendFormat(" AND c.PayDate >='{0}'", filter.PayBeginTime.Value.ToDateTimeString());
}
if (filter.PayEndTime.HasValue)
{
conditionSbl.AppendFormat(" AND c.PayDate <'{0}'", filter.PayEndTime.Value.ToDateTimeString());
}
string pageStr = string.Empty;
if (filter.PageNo > 0)
{
int startNo = (filter.PageNo - 1) * filter.PageSize + 1;
int endNo = startNo + filter.PageSize - 1;
pageStr = string.Format("WHERE t.num BETWEEN {0} AND {1} ", startNo, endNo);
}
string sqlStr = "SQL\\DwjmPayConfirm\\Select_CancelRefund.sql".ToFileContent(false, payDate, conditionSbl.ToString(), pageStr, regionJoin);
return(DataAccessUtility.GetSearchDataByPageNo <CancelRefundViewData>(filter, sqlStr));
}
/// <summary>
/// 生成检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string CombineCondition(CancelRefundFilter filter)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(filter.Region))
{
sb.AppendFormat(@" LEFT JOIN dbo.ConstSysEnum s ON s.super=2619 AND
((LEN(b.ContractNo)=7 AND s.VALUE='02') OR
(LEN(b.ContractNo)=15 AND SUBSTRING(b.ContractNo,3,2)=s.VALUE) OR
(LEN(b.ContractNo)=18 AND SUBSTRING(b.ContractNo,6,2)=s.VALUE)
)");
}
if (filter.HasPayDate)
{
sb.AppendFormat(" WHERE c.PayDate IS NOT NULL ");
}
else
{
sb.AppendFormat(" WHERE c.PayDate IS NULL ");
}
if (!filter.LendingSideKey.IsNullString())
{
sb.AppendFormat(" AND b.LendingSideKey ='{0}'", filter.LendingSideKey);
}
if (filter.BusinessID.HasValue)
{
sb.AppendFormat(" AND c.BusinessID ={0}", filter.BusinessID.Value);
}
if (!string.IsNullOrEmpty(filter.ContractNo))
{
sb.AppendFormat(" AND b.ContractNo ='{0}'", filter.ContractNo);
}
if (!string.IsNullOrEmpty(filter.CustomerName))
{
sb.AppendFormat(" AND pe.PersonName ='{0}'", filter.CustomerName);
}
if (filter.CancelBeginTime.HasValue)
{
sb.AppendFormat(" AND c.CancelTime >='{0}'", filter.CancelBeginTime.Value.ToDateTimeString());
}
if (!string.IsNullOrEmpty(filter.Region))
{
sb.AppendFormat(" AND s.fullkey in('{0}')", filter.Region);
}
if (filter.CancelEndTime.HasValue)
{
sb.AppendFormat(" AND c.CancelTime <'{0}'", filter.CancelEndTime.Value.ToDateTimeString());
}
if (filter.PayBeginTime.HasValue)
{
sb.AppendFormat(" AND c.PayDate >='{0}'", filter.PayBeginTime.Value.ToDateTimeString());
}
if (filter.PayEndTime.HasValue)
{
sb.AppendFormat(" AND c.PayDate <'{0}'", filter.PayEndTime.Value.ToDateTimeString());
}
return(sb.ToString());
}
