/// <summary>
/// Author:shwang
/// Date:20140614
/// Desc:得到担保批次信息
/// </summary>
/// <param name="filter">过滤条件</param>
/// <param name="pageOption">页面信息</param>
/// <returns>担保批次信息</returns>
public List <GuaranteeBatchViewData> GetGuaranteeBatch(GuaranteeBatchFilter filter)
{
string payDateCondition;
if (filter.HasPayDate)
{
payDateCondition = "g.PayDate IS NOT NULL";
}
else
{
payDateCondition = "g.PayDate IS NULL";
}
StringBuilder sbl = new StringBuilder();
if (!string.IsNullOrEmpty(filter.GuaranteeNo))
{
sbl.AppendFormat(" AND g.GuaranteeNum='{0}'", filter.GuaranteeNo);
}
if (!string.IsNullOrEmpty(filter.Region))
{
sbl.AppendFormat(" AND c.fullKey IN({0})", filter.Region);
}
if (!string.IsNullOrEmpty(filter.GuaranteeMonth))
{
sbl.AppendFormat(" AND g.GuaranteeMonth={0}", filter.GuaranteeMonth.ConvertToInt());
}
if (!string.IsNullOrEmpty(filter.ChildCompany))
{
sbl.AppendFormat(" AND s.MappingValue='{0}'", filter.ChildCompany);
}
if (filter.GuaranteeIndex > 0)
{
sbl.AppendFormat(" AND g.GuaranteeIndex={0}", filter.GuaranteeIndex);
}
string pageStr = string.Empty;
if (filter.PageSize > 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_GuaranteeBatch.sql".ToFileContent(false, payDateCondition, sbl.ToString(), pageStr);
return(DataAccessUtility.GetSearchDataByPageNo <GuaranteeBatchViewData>(filter, sqlStr));
}
/// <summary>
/// 得到business信息
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="filter">过滤条件，字段名需要和数据库列名一致</param>
/// <returns>订单信息包括 客户姓名、身份证号</returns>
public List <T> GetBusinessInfo <T>(BaseFilter filter) where T : new()
{
Type filterType = filter.GetType();
PropertyInfo[] propertyInfoArray = filterType.GetProperties();
StringBuilder conditionSbl = new StringBuilder();
foreach (PropertyInfo propertyInfo in propertyInfoArray)
{
object propertyInfoV = propertyInfo.GetValue(filter, null);
if (propertyInfoV.IsDefaultValue())
{
continue;
}
if (DataAccessConsts.BaseFilterProperty.Contains(propertyInfo.Name))
{
continue;
}
if (propertyInfoV is string)
{
conditionSbl.AppendFormat("AND {0}='{1}' ", propertyInfo.Name, propertyInfoV);
}
else if (propertyInfoV is DateTime)
{
conditionSbl.AppendFormat("AND {0}='{1}' ", propertyInfo.Name, Convert.ToDateTime(propertyInfoV).DateTimeToString2());
}
else
{
conditionSbl.AppendFormat("AND {0}={1} ", propertyInfo.Name, propertyInfoV);
}
}
string pageConditionStr = string.Empty;
if (filter.PageNo > 0)
{
int startNo = (filter.PageNo - 1) * filter.PageSize + 1;
int endNo = startNo + filter.PageSize - 1;
pageConditionStr = string.Format("WHERE t.num BETWEEN {0} AND {1}", startNo, endNo);
}
string sql = "SQL\\BusinessService\\BusinessSearch.sql".ToFileContent(false, conditionSbl.ToString(), pageConditionStr);
return(DataAccessUtility.GetSearchDataByPageNo <T>(filter, sql));
}
/// <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));
}