【网学网提醒】：网学会员为广大网友收集整理了，数据库分页，希望对大家有所帮助！
publicpartialclassReport_UCard_SellerDrawApplyRpt
{
protectedvoidPage_Load(objectsender,EventArgse)
{
pcList.DataListControlID=gvList.UniqueID;
pcList.NeedData+=newNeedDataEventHandler(DoPageControlNeedData);
if(!this.IsPostBack)
{
//自己处理其他信息
}
}
#regionbtnSearch_Click(objectsender,ImageClickEventArgse)
protectedvoidbtnSearch_Click(objectsender,ImageClickEventArgse)
{
pcList.PageSize=10000;
pcList.OnNeedData(1,pcList.PageSize);
}
#endregion
#regionDoPageControlNeedData实现自定义分页接口
///
///实现自定义分页接口
/// protectedvoidDoPageControlNeedData(objectsrc,intcurrentPage,intpageSize,
outobjectobjData,outinttotalRecord)
{
objData=null;
totalRecord=0;
inttotalPage=0;
intagentID=StringUtils.ToInt(this.cboSubCompany.SelectedValue,-1);
intsellerID=StringUtils.ToInt(cboSellerAccount.SelectedValue,-1);
ReturnValueretValue=this.Controller.GetSellerDrawApplyRpt(bdtBillTime.SelectedDBKey,
chkShowCompanyDetail.Checked,chkShowSellerDetail.Checked,chkShowProductDetail.Checked,agentID,sellerID,
StringUtils.ToInt(cboDrawType.SelectedValue),StringUtils.ToInt(this.cboParJPoint.SelectedValue),StringUtils.ToInt(cboBillStatus.SelectedValue),
bdtBillTime.BeginDateTime,bdtBillTime.EndDateTime,
currentPage,pageSize,outtotalRecord,outtotalPage);
if(retValue.HasError)
{
lblMessage.Text=retValue.Message;
this.ShowClientAlert(retValue.Message);
return;
}
else
{
DataSetdsData=retValue.ReturnObjectasDataSet;
if(dsData!=null&;&;dsData.Tables.Count>0&;&;dsData.Tables[0].Rows.Count>=0)
{
objData=dsData.Tables[0].DefaultView;
}
}
}
#endregion
}
#regionGetSellerDrawApplyRpt一卡通返点统计
///
///一卡通返点统计
/// ///
dbKey
///显示分公司
///显示销售商
///显示面值
///分公司
///经销商
///领取类型|0=实卡,1=虚卡,2=虚币
///J点面值
///单据状态|-1=无效,0=未处理,1=处理中,2=成功
///开始时间
///结
束时间
///
///
///
///
///
publicReturnValueGetSellerDrawApplyRpt(stringdbKey,boolshowAgent,boolshowSeller,boolshowParJPoint,intagentID,intsellerID,intdrawType,intparJPoint,
intstatus,DateTimefromTime,DateTimetoTime,
intcurPage,intpageSize,outinttotalRecord,outinttotalPage)
{
ReturnValueretValue=newReturnValue();
totalRecord=0;
totalPage=0;
try
{
QueryDALqueryDAL=GetQueryDAL(dbKey);
IListparms=newList();
parms.Add(queryDAL.CreateSqlParameter("@Is_Show_Agent",showAgent,SqlDbType.Bit));//显示分公司
parms.Add(queryDAL.CreateSqlParameter("@Is_Show_Seller",showSeller,SqlDbType.Bit));//显示销售商
parms.Add(queryDAL.CreateSqlParameter("@Is_Show_Par_JPoint",showParJPoint,SqlDbType.Bit));//显示产品
parms.Add(queryDAL.CreateSqlParameter("@Agent_ID",agentID,SqlDbType.Int));//分公司
parms.Add(queryDAL.CreateSqlParameter("@Seller_ID",sellerID,SqlDbType.Int));//经销商
parms.Add(queryDAL.CreateSqlParameter("@Draw_Type",drawType,SqlDbType.Int));//领取类型|0=实卡,1=虚卡,2=虚币
parms.Add(queryDAL.CreateSqlParameter("@Par_JPoint",parJPoint,SqlDbType.Int));//J点面值
parms.Add(queryDAL.CreateSqlParameter("@Status",status,SqlDbType.Int));//单据状态|-1=无效,0=未处理,1=处理中,2=成功
parms.Add(queryDAL.CreateSqlParameter("@From_Time",fromTime,SqlDbType.DateTime));//开始时间
parms.Add(queryDAL.CreateSqlParameter("@To_Time",toTime,SqlDbType.DateTime));//结束时间
parms.Add(queryDAL.CreateSqlParameter("@Current_Page",curPage,SqlDbType.Int));//当前显示的页码
parms.Add(queryDAL.CreateSqlParameter("@Page_Size",pageSize,SqlDbType.Int));//每页条数
//总记录数
SqlParameterparamTotalRecord=newSqlParameter("@Total_Record",SqlDbType.Int);
paramTotalRecord.Direction=ParameterDirection.Output;
parms.Add(paramTotalRecord);
//总页数
SqlParameterparamTotalPage=newSqlParameter("@Total_Page",SqlDbType.Int);
paramTotalPage.Direction=ParameterDirection.Output;
parms.Add(paramTotalPage);
//返回值，成功或者失败
SqlParameterparamReturn=newSqlParameter("RETURN_VALUE",SqlDbType.Int);
paramReturn.Direction=ParameterDirection.ReturnValue;
pa
rms.Add(paramReturn);
if(log.IsDebugEnabled)
{
log.Debug(MessageUtils.FormatProcedureText("procSeller_Draw_Apply_Rpt",parms));
}
DataSetdsData=queryDAL.ExecuteQuery("procSeller_Draw_Apply_Rpt",parms);
if(dsData!=null)
{
retValue.HasError=false;
totalRecord=StringUtils.ToInt(paramTotalRecord.Value);
totalPage=StringUtils.ToInt(paramTotalPage.Value);
retValue.ReturnObject=dsData;
}
else
{
intvalue=Int16.Parse(paramReturn.Value.ToString());
retValue.HasError=true;
if(value==-1)
{
retValue.Message="传入无效的数据";
}
else
{
retValue.Message="统计数据出错，请稍候再统计";
}
}
}
catch(Exceptionex)
{
retValue.HasError=true;
retValue.Message="很抱歉，现在系统繁忙，请稍候再统计";
log.Warn("GetSellerDrawApplyRptdbKey="+dbKey+"showAgent="+showAgent.ToString()+"agentID="+agentID+"fromTime="+fromTime+"toTime="+toTime+"sellerID="+sellerID+"出错，原因："+ex.Message);
}
returnretValue;
}
#endregion
CREATEPROCEDUREprocSeller_Draw_Apply_Rpt
(
@Is_Show_Agent Bit, --显示分公司
@Is_Show_Seller Bit, --显示销售商
@Is_Show_Par_JPoint Bit, --显示面值
@Agent_ID int,--代理商ID
@Seller_ID int,--销售商ID|一般为零售商，报刊亭
@Draw_Type smallint, --领取类型|0=实卡,1=虚卡,2=虚币
@Par_JPoint int,--J点面值
@Statussmallint, --单据状态|-1=无效,0=未处理,1=处理中,2=成功
@From_Time datetime, --统计开始时间
@To_Time datetime, --统计结束时间
@Current_Pageint, --当前显示的页码
@Page_Size int, --每页条数
@Total_Record intoutput, --总记录数
@Total_Page intoutput --总页数
)
AS
BEGIN
DECLARE
@v_SQL varchar(MAX), --SQL
@v_SelectSQL varchar(4000), --sql
@v_WhereSQL varchar(1000), --where条件
@v_GroupSQL varchar(500) --GroupBy条件
IF@Draw_Type=2AND@Par_JPoint>0
BEGIN
return0;
END
SET@v_SelectSQL='';
SET@v_GroupSQL='';
SET@
v_WhereSQL='a.Deal_Time>=convert(datetime,'''+convert(varchar(23),@From_Time,21)+''',21)'
+'ANDa.Deal_Time<=convert(datetime,'''+convert(varchar(10),@To_Time,21)+'23:59:59.997'',21)';
IF@Agent_ID>0
SET@v_WhereSQL=@v_WhereSQL+'ANDa.Agent_ID='+convert(varchar,@Agent_ID);
IF@Seller_ID>0
SET@v_WhereSQL=@v_WhereSQL+'ANDa.Seller_ID='+convert(varchar,@Seller_ID);
IF@Draw_Type>-1
SET@v_WhereSQL=@v_WhereSQL+'ANDa.Draw_Type='+convert(varchar,@Draw_Type);
IF@Status>-2
SET@v_WhereSQL=@v_WhereSQL+'ANDa.Status='+convert(varchar,@Status);
IF@Par_JPoint>0
SET@v_WhereSQL=@v_WhereSQL+'ANDb.Par_JPoint='+convert(varchar,@Par_JPoint);
IF(@Is_Show_Agent=1)
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'Agent_ID,'
SELECT@v_GroupSQL=@v_GroupSQL+'Agent_ID,'
END
ELSE
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'-1ASAgent_ID,'
END
--显示销售商
IF(@Is_Show_Seller=1)
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'Seller_ID,Seller_Account,Seller_Company,'
SELECT@v_GroupSQL=@v_GroupSQL+'Seller_ID,Seller_Account,Seller_Company,'
END
ELSE
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'-1ASSeller_ID,''''asSeller_Account,''''asSeller_Company,'
END
--区分产品面值
IF(@Is_Show_Par_JPoint=1)
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'Par_JPoint,'
SELECT@v_GroupSQL=@v_GroupSQL+'Par_JPoint,'
END
ELSE
BEGIN
SELECT@v_SelectSQL=@v_SelectSQL+'-1ASPar_JPoint,'
END
SELECT@v_SelectSQL=@v_SelectSQL+'ISNULL(SUM(Card_Num),0)ASSum_Card_Num,'
SELECT@v_SelectSQL=@v_SelectSQL+'ISNULL(SUM(Total_JPoint),0)ASSum_Total_JPoint'
--去掉最后的','号
IF(LEN(@v_GroupSQL)>0)
BEGIN
SELECT@v_GroupSQL=LEFT(@v_GroupSQL,LEN(@v_GroupSQL)-1)
END
SELECT@v_SQL='SELECT'+@v_SelectSQL+'FROMSeller_Draw_ApplyaleftjoinSeller_Draw_Apply_Detailbona.Record_ID=b.Record_ID';
SELECT@v_SQL=@v_SQL+'WHERE'+@v_WhereSQL;
IF(LEN(@v_GroupSQL)>0)
BEGIN
SELECT@v_SQL=@v_SQL+'GROUPBY'+@v_GroupSQL
END
--开始Try
BEGINTRY
CREATETABLE#tmpResult_Table
(
Agent_ID intnotnull,--代理商ID
Agent_Account nvarchar(50)null, --销售商帐号
Agent_Company nvarchar(50)null,--销售商公司
Seller_ID intnotnull,--销售商ID|一般为零售商，报刊亭
Seller_Account nvarchar(50)null, --销售商帐号
Seller_Company nvarchar(50)null,--销售商公司
Par_JPoint intnotnull,--J点面值
Sum_Card_Num intnotnull,--申请数量
Sum_Total_JPoint int --总J点面值
);
print@v_GroupSQL;
print@v_SQL;
--获取记录
INSERT#
tmpResult_Table(Agent_ID,Seller_ID,Seller_Account,Seller_Company,Par_JPoint,Sum_Card_Num,Sum_Total_JPoint)
EXEC(@v_SQL);
EXECprocCom_Get_Pagination
@Field_SQL='*',
@From_SQL='#tmpResult_Table',
@Where_SQL='1=1',
@Order_SQL='Sum_Total_JPointDESC',
@Current_Page=@Current_Page,
@Page_Size=@Page_Size,
@Total_Record=@Total_RecordOUTPUT,
@Total_Page=@Total_PageOUTPUT
IFEXISTS(SELECT*FROMtempdb.dbo.sysobjectsWHEREid=OBJECT_ID(N'tempdb..#tmpResult_Table')ANDType='U')
BEGIN
DROPTABLE#tmpResult_Table
END
RETURN0;
ENDTRY
--结束Try
--开始对抛出的例外处理
BEGINCATCH
RETURNERROR_NUMBER();
ENDCATCH;
END
GO
