/// <summary>
/// 格式化成导出的格式
/// </summary>
/// <param name="batchViewList">原始数据</param>
/// <returns>格式化后结果</returns>
private DataTable FormatToExport(List <CancelRefundViewData> batchViewList, CancelRefundExportFilter filter)
{
DataTable dtb = new DataTable();
string[] columns = filter.Fields.Split(WebServiceConst.Separater_Comma.ToArray());
foreach (string col in columns)
{
dtb.Columns.Add(col);
}
foreach (var refund in batchViewList)
{
DataRow dtr = dtb.NewRow();
dtr["BusinessCode"] = "023";
dtr["DeductType"] = string.Format("0{0}", refund.PayType.ToString());
dtr["ContractID"] = refund.ContractNo;
dtr["CustomerName"] = refund.CustomerName;
dtr["ContractAmount"] = refund.RefundAmt;
dtr["DeductDate"] = refund.PayDate.ToDateString();
dtr["ThirdPartyCode"] = string.Empty;
dtr["RecieveDate"] = refund.ReceivedDate.ToDateTimeString();
//补解约数据
dtr["InterestAmount"] = 0;
dtr["deductcharge"] = 0;
dtr["PunitiveInterestAmount"] = 0;
dtr["RepayDuration"] = 0;
dtr["ContractTerminatedDate"] = refund.CancelTime.HasValue ? refund.CancelTime.ToDateTimeString() : refund.ReceivedDate.ToDateTimeString();
dtb.Rows.Add(dtr);
}
return(dtb);
}
/// <summary>
/// 根据过滤条件，返回检索数据的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetSearchSql(BaseFilter baseFilter)
{
CancelRefundExportFilter filter = baseFilter as CancelRefundExportFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(@" 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
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="baseFilter"></param>
/// <param name="responseEntity"></param>
public void ExportData(CancelRefundExportFilter baseFilter, ResponseEntity responseEntity)
{
if (baseFilter == null)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.RequestCommandError);
return;
}
var reportDetailList
= Singleton <CancelRefundExportDAL <CancelRefundViewData> > .Instance.SearchData(baseFilter);
if (reportDetailList == null || reportDetailList.Count == 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
m_Logger.Info("未查询到数据！。");
}
else
{
DataTable dt = FormatToExport(reportDetailList, baseFilter);
// 设置输出文件
SetExportFile(baseFilter, dt, responseEntity);
}
}
/// <summary>
/// 生成检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string CombineCondition(CancelRefundExportFilter 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());
}
