/// <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>
/// 服务执行
/// </summary>
/// <param name="requestEntity">请求数据</param>
/// <param name="responseEntity">返回数据</param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
IDictionary <string, string> paraDict = requestEntity.Parameters;
GuaranteeBatchFilter filter = ServiceUtility.ConvertToFilterFromDict <GuaranteeBatchFilter>(paraDict);
List <GuaranteeObligaBatchViewData> guaranteeObligaBatchList = Singleton <GuaranteeObligaBatchDal> .Instance.GetGuaranteeObligationBatch(filter);
ResponseListResult <GuaranteeObligaBatchViewData> result = new ResponseListResult <GuaranteeObligaBatchViewData>();
result.TotalCount = (int)filter.RecordCount;
result.LstResult = guaranteeObligaBatchList;
responseEntity.ResponseStatus = (int)EnumResponseState.Success;
responseEntity.Results = result;
}
/// <summary>
/// 根据过滤条件，返回检索数据的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetSearchSql(BaseFilter baseFilter)
{
GuaranteeBatchFilter filter = baseFilter as GuaranteeBatchFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(@" SELECT * FROM
(SELECT g.GuaranteeNum AS GuaranteeNum ,
c.NAME AS Region ,
m.name AS ChildCompany ,
g.CreateDate ,
SUM(b.ToGuaranteeAmt) AS Amount ,
g.GuaranteeIndex ,
CAST(g.GuaranteeMonth AS VARCHAR) AS GuaranteeMonth ,
ROW_NUMBER() OVER ( ORDER BY c.fullkey, m.name, g.GuaranteeIndex ) AS num
FROM dun.GuaranteeBatchPay g WITH ( NOLOCK )
INNER JOIN dbo.Business b WITH ( NOLOCK )
ON b.GuaranteeNum = g.GuaranteeNum
LEFT JOIN common.MappingConfig s WITH ( NOLOCK )
ON b.ServiceSideKey = s.MappingKey
AND MappingType = 'SUBCOMPANY'
LEFT JOIN dbo.ConstSysEnum m WITH ( NOLOCK ) ON s.MappingValue = m.fullkey
LEFT JOIN dbo.ConstSysEnum c WITH ( NOLOCK ) ON g.Region = c.fullkey ");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
sb.Append(" GROUP BY g.GuaranteeNum,c.NAME,m.name,g.CreateDate,g.GuaranteeIndex,g.GuaranteeMonth,c.fullkey");
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:wangzhangming
/// Date:20140814
/// Desc:得到外贸担保批次信息
/// </summary>
/// <param name="filter">过滤条件</param>
/// <returns>外贸担保批次信息</returns>
public List <GuaranteeObligaBatchViewData> GetGuaranteeObligationBatch(GuaranteeBatchFilter filter)
{
StringBuilder sbl = new StringBuilder();
// 与需求文档创建者确认过，系统自动生成的外贸担保批次号应从201406月开始算起，
// 201405月份及更早的担保批次号不认为是系统自动生成的
if (!string.IsNullOrEmpty(filter.GuaranteeMonth))
{
sbl.AppendFormat(" AND g.GuaranteeMonth={0} and g.GuaranteeMonth > 201405 ", filter.GuaranteeMonth.ConvertToInt());
}
if (!string.IsNullOrEmpty(filter.Region))
{
sbl.AppendFormat(" AND g.Region IN({0})", filter.Region);
}
string sqlStr = "SQL\\DwjmPayConfirm\\Select_GuaranteeObligationBatch.sql".ToFileContent(false, sbl.ToString());
return(DataAccessUtility.GetSearchData <GuaranteeObligaBatchViewData>(filter, sqlStr));
}
/// <summary>
/// 生成检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string CombineCondition(GuaranteeBatchFilter filter)
{
StringBuilder sb = new StringBuilder();
if (filter.HasPayDate)
{
sb.AppendFormat(" WHERE g.PayDate IS NOT NULL ");
}
else
{
sb.AppendFormat(" WHERE g.PayDate IS NULL ");
}
if (!string.IsNullOrEmpty(filter.GuaranteeNo))
{
sb.AppendFormat(" AND g.GuaranteeNum='{0}'", filter.GuaranteeNo);
}
if (!string.IsNullOrEmpty(filter.Region))
{
sb.AppendFormat(" AND c.fullKey IN({0})", filter.Region);
}
if (!string.IsNullOrEmpty(filter.GuaranteeMonth))
{
sb.AppendFormat(" AND g.GuaranteeMonth={0}", filter.GuaranteeMonth.ToInt());
}
if (!string.IsNullOrEmpty(filter.ChildCompany))
{
sb.AppendFormat(" AND s.MappingValue='{0}'", filter.ChildCompany);
}
if (filter.GuaranteeIndex > 0)
{
sb.AppendFormat(" AND g.GuaranteeIndex={0}", filter.GuaranteeIndex);
}
return(sb.ToString());
}
/// <summary>
/// 根据过滤条件，返回检索件数的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetCountSql(BaseFilter baseFilter)
{
GuaranteeBatchFilter filter = baseFilter as GuaranteeBatchFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(@" SELECT COUNT(1) FROM
(SELECT DISTINCT
g.GuaranteeNum ,
c.NAME ,
s.MappingValue ,
g.CreateDate ,
g.GuaranteeIndex
FROM dun.GuaranteeBatchPay g WITH ( NOLOCK )
INNER JOIN dbo.Business b WITH ( NOLOCK )
ON b.GuaranteeNum = g.GuaranteeNum
LEFT JOIN common.MappingConfig s WITH ( NOLOCK )
ON b.ServiceSideKey = s.MappingKey
AND MappingType = 'SUBCOMPANY'
LEFT JOIN dbo.ConstSysEnum c WITH ( NOLOCK )
ON g.Region = c.fullkey ");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
sb.Append(" ) t");
return(sb.ToString());
}
