/// <summary>
/// 查询代理商
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currPage"></param>
/// <param name="where"></param>
/// <returns></returns>
public IDictionary <String, Object> QueryAgent(Int32 pageSize, Int32 currPage, IDictionary <String, String> where)
{
DataTable dtPage = null;
Int32 totalRow = 0;
Int32 totalPage = 0;
string sqlCount = @"select count(1) totalrow ";
string sql = @"SELECT [AGENT_ID],[USER_ID],[AGENT_NO],[AGENT_NAME],[AGENT_CARD_NUMBER],[AGENT_AREA],[AGENT_CONTACTS],[AGENT_TEL],[AGENT_BUSINESS_LICENSE],[AGENT_ID_CARD]
,[AGENT_COMMITMENT_LETTER],[STATUS],[CREATED_TIME]";
string othersql = " FROM [dbo].[TB_DATA_AGENT] WHERE STATUS>-1";
if (where.ContainsKey("AGENT_NAME"))
{
othersql = othersql + String.Format(" AND AGENT_NAME like @AGENT_NAME");
}
sqlCount = sqlCount + othersql;
sql = sql + othersql;
//设定排序条件
sql = sql + String.Format(" ORDER BY {0} {1} ", "CREATED_TIME", "DESC");
CustomSqlSection customCount = Gateway.Default.FromCustomSql(sqlCount);
CustomSqlSection custom = Gateway.Default.FromCustomSql(sql);
if (where.ContainsKey("AGENT_NAME"))
{
customCount.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["AGENT_NAME"] + "%");
custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["AGENT_NAME"] + "%");
}
try
{
totalRow = Convert.ToInt32(customCount.ToDataSet().Tables[0].Rows[0]["totalrow"]);
totalPage = Convert.ToInt32(Math.Ceiling(totalRow * 1.0 / pageSize));
totalPage = totalPage == 0 ? 1 : totalPage;
if (totalPage < currPage)
{
currPage = totalPage;
}
dtPage = custom.ToDataTable(pageSize, currPage);
}
catch (Exception ex)
{
Log.WriteLog(ex.Message);
return(null);
}
Dictionary <String, Object> dic = new Dictionary <String, Object>();
dic.Add("table", dtPage);
dic.Add("totalRow", totalRow);
dic.Add("totalPage", totalPage);
return(dic);
}
/// <summary>
/// 查询卡信息
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currPage"></param>
/// <param name="where"></param>
/// <returns></returns>
public IDictionary <String, Object> QueryCard(Int32 pageSize, Int32 currPage, IDictionary <String, String> where)
{
DataTable dtPage = null;
Int32 totalRow = 0;
Int32 totalPage = 0;
string sqlCount = @"select count(1) totalrow ";
string sql = @"SELECT [CARD_ID],a.[AGENT_ID],b.AGENT_NAME,a.[END_CUSTOMER_ID],c.END_CUSTOMER_NAME,a.[BRAND_ID],d.BRAND_NAME,
[CARD_ICCID],[CARD_NO],[SERVICE_PROVIDER],a.[STATUS],[IS_COMPLAINT],[COMPLAINT_DATE],[COMPLAINT_URL],
[REAL_NAME_PERSON],[REAL_NAME_ID_CARD],[DISTRIBUTION_DATE],a.[LAST_UPDATED_TIME]";
string othersql = @" FROM [dbo].[TB_DATA_CARD] a
left join TB_DATA_AGENT b on a.AGENT_ID = b.AGENT_ID
left join TB_DATA_END_CUSTOMER c on a.[END_CUSTOMER_ID]=c.[END_CUSTOMER_ID]
left join TB_DATA_BRAND d on a.[BRAND_ID]=d.[BRAND_ID]
where 1=1";
if (where.ContainsKey("CARD_NO"))
{
othersql = othersql + String.Format(" AND CARD_NO like @CARD_NO");
}
if (where.ContainsKey("BEGIN_NUM"))
{
othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))>=@BEGIN_NUM");
}
if (where.ContainsKey("END_NUM"))
{
othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))<=@END_NUM");
}
if (where.ContainsKey("AGENT_ID"))
{
othersql = othersql + String.Format(" AND a.AGENT_ID=@AGENT_ID");
}
if (where.ContainsKey("END_CUSTOMER_ID"))
{
othersql = othersql + String.Format(" AND a.END_CUSTOMER_ID]=@END_CUSTOMER_ID");
}
if (where.ContainsKey("BRAND_ID"))
{
othersql = othersql + String.Format(" AND a.BRAND_ID=@BRAND_ID");
}
if (where.ContainsKey("COMPLAINT_BEGIN_DATE"))
{
othersql = othersql + String.Format(" AND a.COMPLAINT_DATE>=@COMPLAINT_BEGIN_DATE");
}
if (where.ContainsKey("COMPLAINT_END_DATE"))
{
othersql = othersql + String.Format(" AND a.COMPLAINT_DATE<=@COMPLAINT_END_DATE");
}
if (where.ContainsKey("UPDATED_BEGIN_DATE"))
{
othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME>=@UPDATED_BEGIN_DATE");
}
if (where.ContainsKey("UPDATED_END_DATE"))
{
othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME<=@UPDATED_END_DATE");
}
if (where.ContainsKey("STATUS"))
{
othersql = othersql + String.Format(" AND a.STATUS=@STATUS");
}
sqlCount = sqlCount + othersql;
sql = sql + othersql;
//设定排序条件
sql = sql + String.Format(" ORDER BY {0} {1} ", "a.CREATED_TIME", "DESC");
CustomSqlSection customCount = Gateway.Default.FromCustomSql(sqlCount);
CustomSqlSection custom = Gateway.Default.FromCustomSql(sql);
if (where.ContainsKey("CARD_NO"))
{
customCount.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["CARD_NO"] + "%");
custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["CARD_NO"] + "%");
}
if (where.ContainsKey("BEGIN_NUM"))
{
customCount.AddInputParameter("BEGIN_NUM", DbType.Int32, where["BEGIN_NUM"]);
custom.AddInputParameter("BEGIN_NUM", DbType.Int32, where["BEGIN_NUM"]);
}
if (where.ContainsKey("END_NUM"))
{
customCount.AddInputParameter("END_NUM", DbType.Int32, where["END_NUM"]);
custom.AddInputParameter("END_NUM", DbType.Int32, where["END_NUM"]);
}
if (where.ContainsKey("AGENT_ID"))
{
customCount.AddInputParameter("AGENT_ID", DbType.String, where["AGENT_ID"]);
custom.AddInputParameter("AGENT_ID", DbType.String, where["AGENT_ID"]);
}
if (where.ContainsKey("END_CUSTOMER_ID"))
{
customCount.AddInputParameter("END_CUSTOMER_ID", DbType.String, where["END_CUSTOMER_ID"]);
custom.AddInputParameter("END_CUSTOMER_ID", DbType.String, where["END_CUSTOMER_ID"]);
}
if (where.ContainsKey("BRAND_ID"))
{
customCount.AddInputParameter("BRAND_ID", DbType.String, where["BRAND_ID"]);
custom.AddInputParameter("BRAND_ID", DbType.String, where["BRAND_ID"]);
}
if (where.ContainsKey("COMPLAINT_BEGIN_DATE"))
{
customCount.AddInputParameter("COMPLAINT_BEGIN_DATE", DbType.String, where["COMPLAINT_BEGIN_DATE"]);
custom.AddInputParameter("COMPLAINT_BEGIN_DATE", DbType.String, where["COMPLAINT_BEGIN_DATE"]);
}
if (where.ContainsKey("COMPLAINT_END_DATE"))
{
customCount.AddInputParameter("COMPLAINT_END_DATE", DbType.String, where["COMPLAINT_END_DATE"]);
custom.AddInputParameter("COMPLAINT_END_DATE", DbType.String, where["COMPLAINT_END_DATE"]);
}
if (where.ContainsKey("UPDATED_BEGIN_DATE"))
{
customCount.AddInputParameter("UPDATED_BEGIN_DATE", DbType.String, where["UPDATED_BEGIN_DATE"]);
custom.AddInputParameter("UPDATED_BEGIN_DATE", DbType.String, where["UPDATED_BEGIN_DATE"]);
}
if (where.ContainsKey("UPDATED_END_DATE"))
{
customCount.AddInputParameter("UPDATED_END_DATE", DbType.String, where["UPDATED_END_DATE"]);
custom.AddInputParameter("UPDATED_END_DATE", DbType.String, where["UPDATED_END_DATE"]);
}
if (where.ContainsKey("STATUS"))
{
customCount.AddInputParameter("STATUS", DbType.String, where["STATUS"]);
custom.AddInputParameter("STATUS", DbType.String, where["STATUS"]);
}
try
{
totalRow = Convert.ToInt32(customCount.ToDataSet().Tables[0].Rows[0]["totalrow"]);
totalPage = Convert.ToInt32(Math.Ceiling(totalRow * 1.0 / pageSize));
totalPage = totalPage == 0 ? 1 : totalPage;
if (totalPage < currPage)
{
currPage = totalPage;
}
dtPage = custom.ToDataTable(pageSize, currPage);
}
catch (Exception ex)
{
Log.WriteLog(ex.Message);
return(null);
}
Dictionary <String, Object> dic = new Dictionary <String, Object>();
dic.Add("table", dtPage);
dic.Add("totalRow", totalRow);
dic.Add("totalPage", totalPage);
return(dic);
}