/// <summary>
/// 列表
/// </summary>
/// yangj 16.07.01
/// <param name="dictionaryTypeId">字典类型ID</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, bdt.BDT_ID,bdt.Name, temp.Name AS ParentName
FROM BANK_DictionaryType AS bdt
RIGHT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY BDT_ID DESC) AS rownum,BDT_ID FROM BANK_DictionaryType
)AS tmp ON bdt.BDT_ID = tmp.BDT_ID
LEFT JOIN BANK_DictionaryType
AS temp ON bdt.ParentType = temp.BDT_ID
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(
@"SELECT COUNT(*) FROM BANK_DictionaryType
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 产品列表查询方法
/// </summary>
/// cais 16.03.28
/// <param name="pagination">分页</param>
/// <param name="filter">参数</param>
/// <returns></returns>
public DataTable Find(Models.Pagination pagination, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT
Produce.ProduceId,Code,Name,(InterestRate * 12) as InterestRate,(Rate * 12) as Rate,MinFinancingRatio,MaxFinancingRatio,FinalRatio,FinancingPeriods,RepaymentInterval,CustomerBailRatio,CustomerPoundage,AddDate,Remarks,AdditionalGPSCost,AdditionalOtherCost, dbo.Dic(3,RepaymentMethod) AS paymothed FROM PROD_ProduceInfo
AS Produce LEFT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY ProduceId DESC) AS rownum,ProduceId FROM PROD_ProduceInfo
WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')) AS TMP
ON Produce.ProduceId=TMP.ProduceId WHERE TMP.rownum>@Begin
");
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, pagination.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, pagination.End);
DHelper.AddParameter(comm, "@Code_Name", SqlDbType.VarChar, filter["ProductCodeOrName"]);
SqlCommand commPage = DHelper.GetSqlCommand(@"SELECT Count(*) FROM PROD_ProduceInfo WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')");
DHelper.AddParameter(commPage, "@Code_Name", SqlDbType.NVarChar, filter["ProductCodeOrName"]);
pagination.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 列表
/// </summary>
/// yangj 16.07.05
/// <param name="page"></param>
/// <param name="filter"></param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, bm.MetaCode, bm.Name, bm.DataType, bm.DatasLength,CASE bm.Type WHEN 1 THEN '企业' WHEN 2 THEN '个人' END AS TypeName
FROM BANK_Meta AS bm
RIGHT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY MetaCode DESC) AS rownum,MetaCode,Type FROM BANK_Meta
)AS tmp ON bm.MetaCode = tmp.MetaCode AND tmp.Type = bm.Type
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(
@"SELECT COUNT(*) FROM BANK_Meta
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
public static PaginationContent ObtainPaginationContent(Models.Pagination Model, string url)
{
//
PaginationContent paginationContent = new PaginationContent();
int index = 1;
//build base url for navigation links
paginationContent.baseUri = new Uri(url);
// this gets all the query string key value pairs as a collection
paginationContent.queryString = HttpUtility.ParseQueryString(paginationContent.baseUri.Query);
// this removes the key if exists
paginationContent.queryString.Remove("pageNo");
paginationContent.queryString.Add("pageNo", "");
// this gets the page path from root without QueryString
paginationContent.baseUrl = paginationContent.baseUri.GetLeftPart(UriPartial.Path);
paginationContent.baseUrl = paginationContent.queryString.Count > 0 ? String.Format("{0}?{1}", paginationContent.baseUrl, paginationContent.queryString) : paginationContent.baseUrl;
//Set values for page indexes
paginationContent.previous = Model.pageNo - 1;
paginationContent.next = Model.pageNo + 1;
//Determine if prev/next values are correct.
if (paginationContent.previous < 1)
{
paginationContent.previous = 1;
}
if (paginationContent.next > Model.totalPages)
{
paginationContent.next = Model.totalPages;
}
return(paginationContent);
}
/// <summary>
/// 查找指定用户所有的通知
/// </summary>
/// zouql 16.08.29
/// <param name="userId">用户标识</param>
/// <param name="pagination">分页</param>
/// <returns>通知</returns>
public DataTable GetAll(int userId, Models.Pagination pagination = null)
{
return(NoticeMapper.FindAll(userId, pagination));
}
/// <summary>
/// 查找指定用户（已读/未读）的通知
/// </summary>
/// zouql 16.08.29
/// <param name="userId">用户标识</param>
/// <param name="isRead">是否已读</param>
/// <param name="pagination">分页</param>
/// <returns>通知集合</returns>
public DataTable Get(int userId, bool isRead = false, Models.Pagination pagination = null)
{
return(NoticeMapper.Find(userId, isRead, pagination));
}
/// <summary>
/// 产品列表查询方法
/// </summary>
/// cais 16.03.28
/// <param name="pagination">分页</param>
/// <param name="filter">参数</param>
/// <returns>dt</returns>
public DataTable List(Models.Pagination pagination, NameValueCollection filter)
{
return(produceMapper.Find(pagination, filter));
}
/// <summary>
/// 查找字典类型列表
/// </summary>
/// yangj 16.07.01
/// <param name="dictionaryTypeId">字典类型ID</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
return(dictionaryTypeMapper.List(page, filter));
}