GridView动态添加列并判断绑定数据DataTable的列类型控制展示内容

此篇随笔是2013年根据项目需求开发记录的，不一定符合大众口味，只需了解开发思路，毕竟解决方案多种多样。

下面简单说说需求点吧：

（1）通过下拉列表可以选择一个DataSet（数据集），一个DataSet存在可以互相关联的多个DataTable（数据表格），DataTable数据来源于数据库视图；SQL语句关联比较复杂

（2）一个DataTable（数据表格）存在多个可供选择查询显示的Column（列），支持动态组合

（3）Column（列）同时支持作为查询条件进行并运算

 

先看效果吧，免得待会看到太多代码失去看下去的兴趣了：

 

（1）数据库方面

USE [IMSDB]
GO

drop view View_CustomReport_ItemCategory
go
drop view View_CustomReport_ItemClassification
go

---- the view total is 21
alter view View_CustomReport_BasicInfo
as
    select iig.holding_id as 'Holding_Id',
           row_number() over(order by iig.item_group_id) as 'Internal_ID',
           od.dept_name as 'Holder_Department',
           og.group_name as 'Holder_Group',
           os.section_name as 'Holder_Section',
           ou.unit_name as 'Holder_Unit',
           iig.brand as 'Brand',
           iig.model as 'Model',
           iica.name_en as 'Category',
           iicaSub.name_en as 'Sub____category',
           iicl.name_en as 'Classification',
           iiclSub.name_en as 'Sub____classification',
           iig.item_desc as 'GF272_Description',
           (case when iig.inventory_flg=1 then 'inventory' 
                 when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag',
           (select ivc.balance 
            from is_view_item_group ivig
            inner join is_view_column ivc
                on ivig.sheet_id = ivc.sheet_id 
                    and ivig.line_id = ivc.line_no 
                    and ivig.column_id = ivc.column_no
            where ivig.item_group_id = iig.item_group_id) as 'Current_Qty_Balance',
           iig.unit_of_qty as 'Unit_of_Qty'
    from is_item_group iig
    inner join is_inventory_holding_unit iihu
        on iig.holding_id = iihu.holding_id
    left join org_department od
        on iihu.dept_id = od.dept_id
    left join org_group og
        on iihu.group_id = og.group_id
    left join org_section os
        on iihu.section_id = os.section_id
    left join org_unit ou
        on iihu.unit_id = ou.unit_id
    left join is_item_category iica
        on iig.item_cat_id = iica.item_cat_id
    left join is_item_category iicaSub
        on iig.item_sub_cat_id = iicaSub.item_cat_id
    left join is_item_classification iicl
        on iig.item_classic_id = iicl.item_classific_id
    left join is_item_classification iiclSub
        on iig.item_sub_classic_id = iiclSub.item_classific_id
go

alter view View_CustomReport_GF272SheetInfo
as
    select iig.holding_id as 'Holding_Id',
           ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000',
           ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000',
           ivc.line_no as 'Last_GF272_Valid_Line_No000',
           ivc.column_no as 'Last_GF272_Valid_Column_No000',
           ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000'
    from is_item_group iig
    inner join is_view_item_group ivig
        on iig.item_group_id = ivig.item_group_id
    inner join is_view_sheet ivs
        on ivig.sheet_id = ivs.sheet_id
    inner join is_view_column ivc
        on ivs.sheet_id = ivc.sheet_id
    inner join is_view_line ivl
        on ivs.sheet_id = ivl.sheet_id
go


alter view View_CustomReport_LineItemInformation
as
    select iig.holding_id as 'Holding_Id',
           od.dept_name as 'Holder_Department',
           og.group_name as 'Holder_Group',
           os.section_name as 'Holder_Section',
           ou.unit_name as 'Holder_Unit',
           iig.brand as 'Brand',
           iig.model as 'Model',
           iica.name_en as 'Category',
           iicaSub.name_en as 'Sub____category',
           iicl.name_en as 'Classification',
           iiclSub.name_en as 'Sub____classification',
           iig.item_desc as 'GF272_Description',
           (case when iig.inventory_flg=1 then 'inventory' 
                 when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag',
           ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000',
           ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000',
           ivc.line_no as 'Last_GF272_Valid_Line_No000',
           ivc.column_no as 'Last_GF272_Valid_Column_No000',
           ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000'
    from is_item_group iig
    inner join is_inventory_holding_unit iihu
        on iig.holding_id = iihu.holding_id
    left join org_department od
        on iihu.dept_id = od.dept_id
    left join org_group og
        on iihu.group_id = og.group_id
    left join org_section os
        on iihu.section_id = os.section_id
    left join org_unit ou
        on iihu.unit_id = ou.unit_id
    left join is_item_category iica
        on iig.item_cat_id = iica.item_cat_id
    left join is_item_category iicaSub
        on iig.item_sub_cat_id = iicaSub.item_cat_id
    left join is_item_classification iicl
        on iig.item_classic_id = iicl.item_classific_id
    left join is_item_classification iiclSub
        on iig.item_sub_classic_id = iiclSub.item_classific_id
    inner join is_view_item_group ivig
        on iig.item_group_id = ivig.item_group_id
    inner join is_view_sheet ivs
        on ivig.sheet_id = ivs.sheet_id
    inner join is_view_column ivc
        on ivs.sheet_id = ivc.sheet_id
    inner join is_view_line ivl
        on ivs.sheet_id = ivl.sheet_id
go

alter view View_CustomReport_IndividualItemBasicInfo
as
    select iig.holding_id as 'Holding_Id',
           ii.serial_no as 'Serial_No000',
           ii.barcode_no as 'IMS_Barcode_No000',
           ii.self_assign_id as 'Self_Assign_ID',
           ii.detailed_desc as 'Detailed_Description',
           (case when ii.item_status='RJ' then 'Reject'
                 when ii.item_status='TP' then 'Temp'
                 when ii.item_status='DR' then 'Draft'
                 when ii.item_status='N' then 'Normal'
                 when ii.item_status='UN' then 'Unserviceable'
                 when ii.item_status='TI' then 'Transfer'
                 when ii.item_status='TO' then 'TransferOutOfICAC'
                 when ii.item_status='D' then 'Disposed'
                 when ii.item_status='WO' then 'WriteOff'
                 when ii.item_status='SI' then 'SurplusItem'
                 when ii.item_status='R' then 'Repair'
                 when ii.item_status='TL' then 'TransferLedger' end) as 'Item_Status',
           ii.remarks as 'Remarks',
           ii.remarks2 as 'Remarks2',
           ii.remarks3 as 'Remarks3',
           ii.remarks4 as 'Remarks4'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
go

alter view View_CustomReport_Location
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.head_quarter_flg=1 then 'Yes'
                 when ii.head_quarter_flg=0 then 'No' end) as 'Headquarter_Flag',
           il.loc_name as 'Location_District',
           il2.loc_name as 'Location_Building',
           il3.loc_name as 'Location_Floor',
           il4.loc_name as 'Location_Room',
           ii.loc_area as 'Location_Area'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join is_location il
        on ii.loc_district = il.loc_id
    left join is_location il2
        on ii.loc_building = il2.loc_id
    left join is_location il3
        on ii.loc_floor = il3.loc_id
    left join is_location il4
        on ii.loc_room = il4.loc_id
go

alter view View_CustomReport_PhysicalCheckInfo
as
    select iic.holding_id as 'Holding_Id',
           icld.update_datetime as 'Last_Physical_Checked_Date',
           icld.check_by as 'Last_Physical_Checked_By',
           (case when iic.check_status='IP' then 'InProgress'
                 when iic.check_status='CP' then 'Completed'
                 when iic.check_status='SC' then 'SoConfirm' end) as 'Last_Check_Status'
    from is_check_list icl
    inner join is_check_list_detail icld
        on icl.list_id = icld.list_id and icld.is_manual_check=1
    inner join is_inventory_check iic
        on icl.check_id = iic.check_id
go

alter view View_CustomReport_Aggregate
as
    select iig.holding_id as 'Holding_Id',
           ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join is_item_aggregate_map iiam
        on ii.item_id = iiam.parent_item_id
go

alter view View_CustomReport_PurchaseInfo
as
    select iig.holding_id as 'Holding_Id',
           ii.pr_ref_no as 'Purchase_PR_Ref_No000',
           ii.quot_ref_no as 'Purchase_ICAC_Quotation_Ref_No000',
           ii.purchase_mode as 'Purchase_Mode',
           ii.po_no as 'Purchase_PO_No000___Ref',
           ii.invoice_no as 'Purchase_Invoice_No000',
           ii.date_of_order as 'Purchase_Date_of_Order',
           ii.date_of_receipt as 'Purchase_Date_of_Receipt',
           ii.date_of_acceptance as 'Purchase_Date_of_Acceptance',
           icim.contract_month as 'Free_Warranty_Period',
           ii.holder_file_ref as 'Holder_File_Reference',
           ii.contact_person as 'Contact_Person',
           ii.contact_person_post as 'Contact_Person_Position',
           itdi.voucher_type as 'Purchase_Related_Voucher_Type',
           itdi.voucher_no as 'Purchase_Related_Voucher_No000'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join(
        select item_id,
               datediff(month,contract_start_date,contract_end_date) as 'contract_month'
        from is_contract_item_map
        where contract_id in(
            select min(contract_id) from is_contract_item_map
            group by item_id
        )
    ) as icim
        on ii.item_id = icim.item_id
    inner join(
        select itdi.item_id,itr.voucher_type,itr.voucher_no
        from is_tx_record itr 
        inner join is_tx_detail_item itdi
            on itr.tx_id = itdi.tx_id
        where itdi.tx_id in(
            select min(tx_id) from is_tx_detail_item
            group by item_id
        )
    ) as itdi
        on ii.item_id = itdi.item_id
go

create function Func_StrArrayStrOfIndex  
(  
  @str varchar(1024),  --要分割的字符串 
  @split varchar(10),  --分隔符号 
  @index int --取第几个元素 
)  
returns varchar(1024)  
as  
begin  
  declare @location int  
  declare @start int  
  declare @next int  
  declare @seed int  
  
  set @str=ltrim(rtrim(@str))  
  set @start=1  
  set @next=1  
  set @seed=len(@split)  
    
  set @location=charindex(@split,@str)  
  while @location<>0 and @index>@next  
  begin  
    set @start=@location+@seed  
    set @location=charindex(@split,@str,@start)  
    set @next=@next+1  
  end  
  if @location =0 select @location =len(@str)+1
    
  return substring(@str,@start,@location-@start)  
end  
go
alter view View_CustomReport_LoanInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.loan_flg=1 then 'On Loan'
                 when ii.loan_flg=0 then 'In Hand' end) as 'Current_Loan_Status',
           isc.english_name as 'Staff_Name_of_Borrower',
           dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',1) as 'Borrower111s_Department',
           dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',2) as 'Borrower111s_Group',
           dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',3) as 'Borrower111s_Section'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join is_loan_record ilr 
        on ii.loan_id = ilr.loan_id
    left join is_staff_card isc 
        on ilr.borrower_id = isc.staff_card_id
go

alter view View_CustomReport_IssueInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.issue_flg=1 then 'Issued'
                 when ii.issue_flg=0 then 'Not Issued' end) as 'Current_Issue_Status',
           od.dept_name as 'Issue_Dept',
           og.group_name as 'Issue_Group',
           os.section_name as 'Issue_Section',
           ou.unit_name as 'Issue_Unit'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join org_department od
        on ii.issued_dept_id = od.dept_id
    left join org_group og
        on ii.issued_group_id = og.group_id
    left join org_section os
        on ii.issued_section_id = os.section_id
    left join org_unit ou
        on ii.issued_unit_id = ou.unit_id
go

alter view View_CustomReport_EndUserInfo
as
    select iig.holding_id as 'Holding_Id',
           ieur.end_user as 'Current_Item_End____user',
           convert(varchar(10),ieur.assign_date,105)+' to '+convert(varchar(10),ieur.return_date,105) as 'Usage_Date'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join is_end_user_record ieur
        on ii.item_id = ieur.item_id
go

alter view View_CustomReport_SurplusInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.item_status='SI' then 'Yes'
                                          else 'No' end) as 'Surplus_Status',
           isipo.[start_date] as 'Surplus_Post_Out_Date_From',
           isipo.end_date as 'Surplus_Post_Out_Date_To'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join(
        select item_id,[start_date],end_date
        from is_surplus_item_post_out
        where request_id in(
            select max(request_id) from is_surplus_item_post_out
            group by item_id
        )
    ) as isipo
        on ii.item_id = isipo.item_id
go

alter view View_CustomReport_DisposalInfo
as
    select iig.holding_id as 'Holding_Id',
           ii.anticipated_date_disposal as 'Anticipated_Disposal_Date',
           (case when idr.disposal_status='R' or idr.disposal_status='V' then 'Not Applicable'
                 when idr.disposal_status='D' or idr.disposal_status='W' then 'Requesting'
                 when idr.disposal_status='T' then 'Approved by SO'
                 when idr.disposal_status='P' then 'Physically Disposed' end) as 'Disposal_Status',
           idr.job_no as 'Dispose_Job_No000',
           idr.dumping_date as 'Date_of_Disposal',
           idr.item_condition as 'Condition_of_Item_in_Disposal',
           idr.disposal_method as 'Disposal_Method',
           itr.voucher_no as 'Disposal_Related_Voucher_No000'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join is_disposal_record idr
        on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id
    left join is_request ir
        on idr.request_id = ir.request_id
    left join is_tx_record itr
        on ir.out_tx_id = itr.tx_id
go

alter view View_CustomReport_RepairInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.item_status='R' then 'Yes'
                                         else 'No' end) as 'On_Repair_Status'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
go

alter view View_CustomReport_ReplaceInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.replace_flg=1 then 'Yes'
                                         else 'No' end) as 'Replaced_Status',
           ii.barcode_no as 'IMS_Barcode_No_of_Replacing_Item',
           itr.voucher_no as 'Voucher_No_for_Replaced_Item'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join(
        select itdi.item_id,itr.voucher_no
        from is_tx_record itr
        inner join is_tx_detail_item itdi
        on itr.tx_id = itdi.tx_id
        where itdi.tx_id in(
            select max(tx_id) from is_tx_detail_item
            group by item_id
        )
    ) as itr
        on ii.item_id = itr.item_id
go

alter view View_CustomReport_DelegateMaintUserInfo
as
    select iig.holding_id as 'Holding_Id',
           (case when ii.maint_dept_id is null then 'No'
                 when ii.maint_dept_id=0 then 'No'
                                         else 'Yes' end) as 'Delegated_to_others_for_Maintenance',
           od.dept_name as 'Maintenance_Dept',
           og.group_name as 'Maintenance_Group',
           os.section_name as 'Maintenance_Section',
           ou.unit_name as 'Maintenance_Unit'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join org_department od
        on ii.maint_dept_id = od.dept_id
    left join org_group og
        on ii.maint_group_id = og.group_id
    left join org_section os
        on ii.maint_section_id = os.section_id
    left join org_unit ou
        on ii.maint_unit_id = ou.unit_id
go

alter view View_CustomReport_MaintenanceBasic
as
    select iig.holding_id as 'Holding_Id',
           ivm.vote_name as 'Maintenance_Vote',
           ii.remarks_maint as 'Remarks_for_Maintenance',
           img.grouping_name as 'Maint000_Grouping',
           iig.gld_contract_no as 'GLD_Contract_No000',
           (case when iig.further_maint_flag=1 then 'Yes'
                                               else 'No' end) as 'Needed_to_be_Maintenance_Afterwards'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join is_maint_grouping img
        on iig.maint_grouping = img.[grouping_id]
    left join is_contract_item_map icim
        on ii.item_id = icim.item_id
    left join is_vote_maintenance ivm
        on icim.vote = ivm.vote_id
go

alter view View_CustomReport_CurrentMaintenanceInfo
as
    select iig.holding_id as 'Holding_Id',
           imc.ref_contract_id as 'Current_Maintenance_Contract_User_Reference_No000',
           iv.vendor_name as 'Current_Maintenance_Vendor',
           ii.annual_maint_cost as 'Current_Maintenance_Unit_Cost',
           icim.contract_start_date as 'Current_Maintenance_Coverage_Start_Date',
           icim.contract_end_date as 'Current_Maintenance_Coverage_End_Date',
           iml.level_desc as 'Current_Maintenance_Level',
           imc.bu_date as 'Next_Maintenance_BU_Date'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join is_contract_item_map icim
        on ii.item_id = icim.item_id
    left join is_maintenance_contract imc
        on icim.contract_id = imc.contract_id
    left join is_vender iv
        on icim.vendor_id = iv.vendor_id
    left join is_maint_level iml
        on icim.level_of_maint = iml.level_code
go


alter view View_CustomReport_ContractHeader
as
    select iig.holding_id as 'Holding_Id',
           imc.ref_contract_id as 'Contract_User_Reference_No000',
           imc.contract_name as 'Contract_Name',
           (case when imc.maint_pr_no='null' then ''
                                             else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',
           icim.contract_start_date as 'Contract_Start_Date',
           icim.contract_end_date as 'Contract_End_Date',
           imc.contact_person as 'Contact_Person',
           imc.contact_person_post as 'Contact_Post',
           imc.bu_date as 'BU_Date'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    left join is_contract_item_map icim
        on ii.item_id = icim.item_id
    left join is_maintenance_contract imc
        on icim.contract_id = imc.contract_id
go

alter view View_CustomReport_ItemsVendorInfo
as
    select imc.holding_id as 'Holding_Id',
           iv.vendor_name as 'Maint_Vendor',
           icvm.contact_person as 'Maint_Vendor_Contact_Person',
           icvm.contact_person_post as 'Maint_Vendor_Contact_Person_Position',
           icvm.quotation_no as 'Maint_Vendor_Quotation_No000',
           icvp.maint_op_no as 'Maint_Po_No000__Ref',
           icvp.group_num as 'Payment_Schedule',
           icvp.actual_payment_date as 'Payment_Date',
           icvp.paid_amount_hkd as 'Paid_Amount',
           (case when icvp.payment_status='1' then 'Paid'
                 when icvp.payment_status='0' then 'Not Paid' end) as 'Payment_Status',
           icvp.invoice_no as 'Invoice_No000',
           icvp.file_ref_no as 'File_Ref_No000'
    from is_vender iv
    inner join is_contract_vendor_map icvm
        on iv.vendor_id = icvm.vendor_id
    left join (
        select contract_id,
               vendor_id,
               maint_op_no,
               actual_payment_date,
               paid_amount_hkd,
               payment_status,
               invoice_no,
               file_ref_no,
               row_number() over(partition by contract_id,vendor_id 
                                 order by actual_payment_date) as 'group_num'
        from is_contract_vendor_payment
    ) as icvp
        on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id
    inner join is_maintenance_contract imc
        on icvm.contract_id = imc.contract_id
go

alter view View_CustomReport_ItemsMaintenanceBasicInfo
as
    select iig.holding_id as 'Holding_Id',
           ii.serial_no as 'Item_Serial_No000',
           ii.barcode_no as 'IMS_Item_Barcode_No000',
           iig.maint_grouping as 'Maintenance_Grouping',
           ivm.vote_name as 'Maintenance_Vote',
           ii.remarks_maint as 'Remarks_for_Maintenance',
           img.grouping_name as 'Maint000_Grouping',
           iig.gld_contract_no as 'GLD_Contract_No000',
           (case when iig.further_maint_flag=1 then 'Yes'
                                               else 'No' end) as 'Needed_to_be_Maintenance_Afterwards',
           (case when imc.maint_pr_no='null' then ''
                                             else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',
           icim.cost_total_hkd as 'Maintenance_Unit_Cost',
           icim.paid_total_price_hkd as 'Maintenance_Unit_Actual_Cost',
           icim.contract_start_date as 'Maintenance_Coverage_Start_Date',
           icim.contract_end_date as 'Maintenance_Coverage_End_Date',
           iml.level_desc as 'Maintenance_Level',
           icim.remarks as 'Item_Remarks_in_this_Contract'
    from is_item ii
    inner join is_item_group iig
        on ii.item_group_id = iig.item_group_id
    inner join is_maint_grouping img
        on iig.maint_grouping = img.[grouping_id]
    left join is_contract_item_map icim
        on ii.item_id = icim.item_id
    left join is_vote_maintenance ivm
        on icim.vote = ivm.vote_id
    left join is_maintenance_contract imc
        on icim.contract_id = imc.contract_id
    left join is_maint_level iml
        on icim.level_of_maint = iml.level_code
go

 

（2）页面处理方面

CustomReport.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;

using System.Data;
using DAL;

namespace Bll.Report
{
    public enum ColumnType
    {
        Number = 0,
        String = 1,
        DateTime = 2
    }

    public class CustomReportColumn
    {
        #region Property
        private string _PageColumnName;
        public string PageColumnName
        {
            get { return _PageColumnName; }
            set { _PageColumnName = value; }
        }

        private string _DataBaseColumnName;
        public string DataBaseColumnName
        {
            get { return _DataBaseColumnName; }
            set { _DataBaseColumnName = value; }
        }

        private ColumnType _ColumnType;
        public ColumnType ColumnType
        {
            get { return _ColumnType; }
            set { _ColumnType = value; }
        }
        #endregion
    }

    public class CustomReportTable
    {
        #region Property
        private string _PageTableName;
        public string PageTableName
        {
            get { return _PageTableName; }
            set { _PageTableName = value; }
        }

        private string _DataBaseTableName;
        public string DataBaseTableName
        {
            get { return _DataBaseTableName; }
            set { _DataBaseTableName = value; }
        }

        private List<CustomReportColumn> _ListColumn;
        public List<CustomReportColumn> ListColumn
        {
            get { return _ListColumn; }
            set { _ListColumn = value; }
        }
        #endregion
    }

    public class CustomReport
    {
        public static string[] tableSetNameArr = new string[] { "Latest Information of Line Item", "Individual Item", "Maintenance Contract" };
        public static string tableHaveUserId = "";

        public static Dictionary<string, List<CustomReportTable>> TableSet(int cacheMinutes)
        {
            Dictionary<string, List<CustomReportTable>> tableSet = new Dictionary<string, List<CustomReportTable>>();
            for (int i = 0, len = tableSetNameArr.Length; i < len; i++)
                tableSet.Add(tableSetNameArr[i], ListTable(i, 0));
            return tableSet;
        }

        public static List<CustomReportTable> ListTable(int index, int cacheMinutes)
        {
            string cacheKey = string.Format("CustomReport_ListTable_{0}", index);
            List<CustomReportTable> listTable = HttpContext.Current.Cache[cacheKey] as List<CustomReportTable>;
            if (listTable == null)
            {
                listTable = new List<CustomReportTable>();
                InitDataForTable(index, ref listTable);

                if (listTable.Count > 0 && cacheMinutes > 0)
                    HttpContext.Current.Cache.Insert(cacheKey,
                                 listTable,
                                 null,
                                 DateTime.Now.AddMinutes(cacheMinutes),
                                 System.Web.Caching.Cache.NoSlidingExpiration);
            }
            return listTable;
        }

        private static void InitDataForTable(int index, ref List<CustomReportTable> listTable)
        {
            List<CustomReportColumn> listColumn;
            switch (index)
            {
                case 0:
                    #region Latest Information of Line Item
                    #region Basic Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Internal ID", DataBaseColumnName = "Internal_ID", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Qty Balance", DataBaseColumnName = "Current_Qty_Balance", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Unit of Qty", DataBaseColumnName = "Unit_of_Qty", ColumnType = ColumnType.Number });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Basic Info",
                        DataBaseTableName = "View_CustomReport_BasicInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region GF272 Sheet Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "GF272 Sheet Info",
                        DataBaseTableName = "View_CustomReport_GF272SheetInfo",
                        ListColumn = listColumn
                    });
                    #endregion
                    #endregion
                    break;
                case 1:
                    #region Individual Item
                    #region Line Item Information
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Line Item Information",
                        DataBaseTableName = "View_CustomReport_LineItemInformation",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Individual Item Basic Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Serial No.", DataBaseColumnName = "Serial_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No.", DataBaseColumnName = "IMS_Barcode_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Self Assign ID", DataBaseColumnName = "Self_Assign_ID", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Detailed Description", DataBaseColumnName = "Detailed_Description", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Status", DataBaseColumnName = "Item_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks", DataBaseColumnName = "Remarks", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks2", DataBaseColumnName = "Remarks2", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks3", DataBaseColumnName = "Remarks3", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks4", DataBaseColumnName = "Remarks4", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Individual Item Basic Info",
                        DataBaseTableName = "View_CustomReport_IndividualItemBasicInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Location
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Headquarter Flag", DataBaseColumnName = "Headquarter_Flag", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Location District", DataBaseColumnName = "Location_District", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Building", DataBaseColumnName = "Location_Building", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Floor", DataBaseColumnName = "Location_Floor", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Room", DataBaseColumnName = "Location_Room", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Area", DataBaseColumnName = "Location_Area", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Location",
                        DataBaseTableName = "View_CustomReport_Location",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Physical Check Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked Date", DataBaseColumnName = "Last_Physical_Checked_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked By", DataBaseColumnName = "Last_Physical_Checked_By", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Check Status", DataBaseColumnName = "Last_Check_Status", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Physical Check Info",
                        DataBaseTableName = "View_CustomReport_PhysicalCheckInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Aggregate
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No. of Aggregate Parent", DataBaseColumnName = "IMS_Barcode_No000_of_Aggregate_Parent", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Aggregate",
                        DataBaseTableName = "View_CustomReport_Aggregate",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Purchase Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PR Ref No.", DataBaseColumnName = "Purchase_PR_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase ICAC Quotation Ref No.", DataBaseColumnName = "Purchase_ICAC_Quotation_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Mode", DataBaseColumnName = "Purchase_Mode", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PO No./ Ref", DataBaseColumnName = "Purchase_PO_No000___Ref", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Invoice No.", DataBaseColumnName = "Purchase_Invoice_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Order", DataBaseColumnName = "Purchase_Date_of_Order", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Receipt", DataBaseColumnName = "Purchase_Date_of_Receipt", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Acceptance", DataBaseColumnName = "Purchase_Date_of_Acceptance", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Free Warranty Period", DataBaseColumnName = "Free_Warranty_Period", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder File Reference", DataBaseColumnName = "Holder_File_Reference", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person Position", DataBaseColumnName = "Contact_Person_Position", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher Type", DataBaseColumnName = "Purchase_Related_Voucher_Type", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher No.", DataBaseColumnName = "Purchase_Related_Voucher_No000", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Purchase Info",
                        DataBaseTableName = "View_CustomReport_PurchaseInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Loan Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Loan Status", DataBaseColumnName = "Current_Loan_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Staff Name of Borrower", DataBaseColumnName = "Staff_Name_of_Borrower", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Department", DataBaseColumnName = "Borrower111s_Department", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Group", DataBaseColumnName = "Borrower111s_Group", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Section", DataBaseColumnName = "Borrower111s_Section", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Loan Info",
                        DataBaseTableName = "View_CustomReport_LoanInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Issue Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Issue Status", DataBaseColumnName = "Current_Issue_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Dept", DataBaseColumnName = "Issue_Dept", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Group", DataBaseColumnName = "Issue_Group", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Section", DataBaseColumnName = "Issue_Section", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Unit", DataBaseColumnName = "Issue_Unit", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Issue Info",
                        DataBaseTableName = "View_CustomReport_IssueInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region End-user info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Item End-user", DataBaseColumnName = "Current_Item_End____user", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Usage Date", DataBaseColumnName = "Usage_Date", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "End-user info",
                        DataBaseTableName = "View_CustomReport_EndUserInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Surplus Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Status", DataBaseColumnName = "Surplus_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date From", DataBaseColumnName = "Surplus_Post_Out_Date_From", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date To", DataBaseColumnName = "Surplus_Post_Out_Date_To", ColumnType = ColumnType.DateTime });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Surplus Info",
                        DataBaseTableName = "View_CustomReport_SurplusInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Disposal Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Anticipated Disposal Date", DataBaseColumnName = "Anticipated_Disposal_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Status", DataBaseColumnName = "Disposal_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Dispose Job No.", DataBaseColumnName = "Dispose_Job_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Date of Disposal", DataBaseColumnName = "Date_of_Disposal", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Condition of Item in Disposal", DataBaseColumnName = "Condition_of_Item_in_Disposal", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Method", DataBaseColumnName = "Disposal_Method", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Related Voucher No.", DataBaseColumnName = "Disposal_Related_Voucher_No000", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Disposal Info",
                        DataBaseTableName = "View_CustomReport_DisposalInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Repair Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "On Repair Status", DataBaseColumnName = "On_Repair_Status", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Repair Info",
                        DataBaseTableName = "View_CustomReport_RepairInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Replace Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Replaced Status", DataBaseColumnName = "Replaced_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No of Replacing Item", DataBaseColumnName = "IMS_Barcode_No_of_Replacing_Item", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Voucher No for Replaced Item", DataBaseColumnName = "Voucher_No_for_Replaced_Item", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Replace Info",
                        DataBaseTableName = "View_CustomReport_ReplaceInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Delegate Maint User Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Delegated to others for Maintenance", DataBaseColumnName = "Delegated_to_others_for_Maintenance", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Dept", DataBaseColumnName = "Maintenance_Dept", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Group", DataBaseColumnName = "Maintenance_Group", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Section", DataBaseColumnName = "Maintenance_Section", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit", DataBaseColumnName = "Maintenance_Unit", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Delegate Maint User Info",
                        DataBaseTableName = "View_CustomReport_DelegateMaintUserInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Maintenance Basic
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Maintenance Basic",
                        DataBaseTableName = "View_CustomReport_MaintenanceBasic",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Current Maintenance Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Contract User Reference No.", DataBaseColumnName = "Current_Maintenance_Contract_User_Reference_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Vendor", DataBaseColumnName = "Current_Maintenance_Vendor", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Unit Cost", DataBaseColumnName = "Current_Maintenance_Unit_Cost", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage Start Date", DataBaseColumnName = "Current_Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage End Date", DataBaseColumnName = "Current_Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Level", DataBaseColumnName = "Current_Maintenance_Level", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Next Maintenance BU Date", DataBaseColumnName = "Next_Maintenance_BU_Date", ColumnType = ColumnType.DateTime });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Current Maintenance Info",
                        DataBaseTableName = "View_CustomReport_CurrentMaintenanceInfo",
                        ListColumn = listColumn
                    });
                    #endregion
                    #endregion
                    break;
                case 2:
                    #region Maintenance Contract
                    #region Contract Header
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract User Reference No.", DataBaseColumnName = "Contract_User_Reference_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Name", DataBaseColumnName = "Contract_Name", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Start Date", DataBaseColumnName = "Contract_Start_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract End Date", DataBaseColumnName = "Contract_End_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Post", DataBaseColumnName = "Contact_Post", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "BU Date", DataBaseColumnName = "BU_Date", ColumnType = ColumnType.DateTime });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Contract Header",
                        DataBaseTableName = "View_CustomReport_ContractHeader",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Item’s Vendor Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor", DataBaseColumnName = "Maint_Vendor", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person", DataBaseColumnName = "Maint_Vendor_Contact_Person", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person Position", DataBaseColumnName = "Maint_Vendor_Contact_Person_Position", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Quotation No.", DataBaseColumnName = "Maint_Vendor_Quotation_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Po No./Ref", DataBaseColumnName = "Maint_Po_No000__Ref", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Schedule", DataBaseColumnName = "Payment_Schedule", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Date", DataBaseColumnName = "Payment_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Paid Amount", DataBaseColumnName = "Paid_Amount", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Status", DataBaseColumnName = "Payment_Status", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Invoice No.", DataBaseColumnName = "Invoice_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "File Ref No.", DataBaseColumnName = "File_Ref_No000", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Item’s Vendor Info",
                        DataBaseTableName = "View_CustomReport_ItemsVendorInfo",
                        ListColumn = listColumn
                    });
                    #endregion

                    #region Item’s Maintenance Basic Info
                    listColumn = new List<CustomReportColumn>();
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Serial No.", DataBaseColumnName = "Item_Serial_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Item Barcode No.", DataBaseColumnName = "IMS_Item_Barcode_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Grouping", DataBaseColumnName = "Maintenance_Grouping", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Cost", DataBaseColumnName = "Maintenance_Unit_Cost", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Actual Cost", DataBaseColumnName = "Maintenance_Unit_Actual_Cost", ColumnType = ColumnType.Number });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage Start Date", DataBaseColumnName = "Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage End Date", DataBaseColumnName = "Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Level", DataBaseColumnName = "Maintenance_Level", ColumnType = ColumnType.String });
                    listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Remarks in this Contract", DataBaseColumnName = "Item_Remarks_in_this_Contract", ColumnType = ColumnType.String });
                    listTable.Add(new CustomReportTable()
                    {
                        PageTableName = "Item’s Maintenance Basic Info",
                        DataBaseTableName = "View_CustomReport_ItemsMaintenanceBasicInfo",
                        ListColumn = listColumn
                    });
                    #endregion
                    #endregion
                    break;
            }
        }

        public static DataTable GetCustomReport(string sql)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt != null)
            {
                for (int i = 0, len = dt.Columns.Count; i < len; i++)
                    dt.Columns[i].ColumnName = ConvertColumnName(dt.Columns[i].ColumnName);
            }
            return dt;
        }

        public static string ConvertColumnName(string columnName)
        {
            string convertColumnName = "";
            if (!string.IsNullOrEmpty(columnName))
                convertColumnName = columnName.Replace("____", "-").Replace("000", ".").Replace("__", "/").Replace("_", " ").Replace("111", "'");
            return convertColumnName;
        }
    }
}

 

SearchCustomReport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SearchCustomReport.aspx.cs"
    Inherits="IMSWeb.App.IS.Report.SearchCustomReport" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset class="pageFieldset" style="width: 800px">
            <legend class="segmentTitle">Custom Report Search</legend>
            <table class="tblDetail">
                <tr style="display:none">
                    <td>
                        Issued Item
                    </td>
                    <td colspan="3">
                        <asp:DropDownList ID="ddlIssuedItem" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlIssuedItem_SelectedIndexChanged"
                            Width="160px">
                            <asp:ListItem Value="">--ALL--</asp:ListItem>
                            <asp:ListItem Value="1">--Show--</asp:ListItem>
                            <asp:ListItem Value="0">--Don't Show--</asp:ListItem>
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td style=" white-space:200px;">
                        Inventory Holder
                    </td>
                    <td colspan="3">
                        <asp:DropDownList ID="ddlHolder" runat="server">
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td>
                        Table Set
                    </td>
                    <td>
                        <asp:DropDownList ID="ddlTableSet" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTableSet_SelectedIndexChanged"
                            Width="160px">
                        </asp:DropDownList>
                    </td>
                    <td style="width: 80px;">
                        Item Group
                    </td>
                    <td>
                        <asp:DropDownList ID="ddlTable" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTable_SelectedIndexChanged"
                            Width="160px">
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td>
                        Item Name
                    </td>
                    <td colspan="3">
                        <table cellpadding="0" cellspacing="0" style="width: 100%;">
                            <tr>
                                <td>
                                    Brand
                                </td>
                                <td rowspan="2" class="style1">
                                    <asp:Button ID="btnSelectAll" runat="server" Text=">>" OnClick="btnSelectAll_Click" />
                                    <p />
                                    <asp:Button ID="btnSelect" runat="server" Text=">" OnClick="btnSelect_Click" />
                                    <p />
                                    &nbsp;
                                    <p />
                                    <asp:Button ID="btnUnSelect" runat="server" Text="<" OnClick="btnUnSelect_Click" />
                                    <p />
                                    <asp:Button ID="btnUnSelectAll" runat="server" Text="<<" OnClick="btnUnSelectAll_Click" />
                                </td>
                                <td>
                                    Model
                                </td>
                            </tr>
                            <tr>
                                <td style="padding-left: 0; width: 244px;">
                                    <asp:ListBox ID="lstAvailableField" runat="server" Width="160px" Rows="15"></asp:ListBox>
                                </td>
                                <td style="padding-left: 0">
                                    <asp:ListBox ID="lstSelectedField" runat="server" Width="160px" Rows="15"></asp:ListBox>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
                <tr>
                    <td>
                        Enter Criteria
                    </td>
                    <td colspan="3">
                        <asp:DropDownList ID="ddlAvailableFieldEnter" runat="server" Width="160px" AutoPostBack="true"
                            OnSelectedIndexChanged="ddlAvailableFieldEnter_SelectedIndexChanged">
                        </asp:DropDownList>
                        &nbsp;&nbsp;
                        <asp:DropDownList ID="ddlOperation" runat="server" Width="50px">
                            <asp:ListItem Value="="></asp:ListItem>
                            <asp:ListItem Value=">"></asp:ListItem>
                            <asp:ListItem Value="<"></asp:ListItem>
                            <%--<asp:ListItem Value="%like%"></asp:ListItem>
                            <asp:ListItem Value="%like"></asp:ListItem>
                            <asp:ListItem Value="like%"></asp:ListItem>--%>
                        </asp:DropDownList>
                        &nbsp;&nbsp;
                        <asp:TextBox ID="txtValueEnter" runat="server" Width="228px"></asp:TextBox>&nbsp;&nbsp;
                        <asp:Button ID="btnAddCriteria" runat="server" Text="Add" OnClick="btnAddCriteria_Click" />
                        <asp:DropDownList ID="ddlJoin" runat="server" Width="50px" Visible="false">
                            <asp:ListItem Value="And"></asp:ListItem>
                            <asp:ListItem Value="Or"></asp:ListItem>
                        </asp:DropDownList>
                        &nbsp;&nbsp; &nbsp;&nbsp;
                    </td>
                </tr>
                <tr>
                    <td>
                        Search Criteria
                    </td>
                    <td colspan="3">
                        <asp:TextBox ID="txtSearchCriteria" runat="server" Width="469px" Height="100px" ReadOnly="true"
                            TextMode="MultiLine"></asp:TextBox>&nbsp;&nbsp;
                        <asp:TextBox ID="txtSearchCriteriaVal" runat="server" ReadOnly="true" Style="display:none"></asp:TextBox>
                        <asp:Button ID="btnResetSearchCriteria" runat="server" Text="Reset" OnClick="btnResetSearchCriteria_Click" />
                    </td>
                </tr>
            </table>
            <div class="block">
                <asp:Button ID="btnPreview" runat="server" Text="Preview" ValidationGroup="lose"
                    OnClick="btnPreview_Click" />
            </div>
        </fieldset>
    </div>
    </form>
</body>
</html>

 

SearchCustomReport.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using Bll;
using System.Text;
using Bll.Report;

namespace IMSWeb.App.IS.Report
{
    /// <summary>
    /// add by Kenmu at 2013-04-28
    /// </summary>
    public partial class SearchCustomReport : BasePage
    {
        public const string joinStr = "\r\n";

        #region Property

        #endregion

        #region Event
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                InitData();
        }

        protected void ddlIssuedItem_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        protected void ddlTableSet_SelectedIndexChanged(object sender, EventArgs e)
        {
            InitTable();
        }

        protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            InitColumn();
        }

        protected void btnSelect_Click(object sender, EventArgs e)
        {
            SelectItem();
            ControlPreview();
        }

        protected void btnUnSelect_Click(object sender, EventArgs e)
        {
            UnSelectItem();
            ControlPreview();
        }

        protected void btnSelectAll_Click(object sender, EventArgs e)
        {
            SelectAllItem();
            ControlPreview();
        }

        protected void btnUnSelectAll_Click(object sender, EventArgs e)
        {
            UnSelectAllItem();
            ControlPreview();
        }

        protected void ddlAvailableFieldEnter_SelectedIndexChanged(object sender, EventArgs e)
        {
            ControlValueEnter();
        }

        protected void btnAddCriteria_Click(object sender, EventArgs e)
        {
            AddCriteria();
        }

        protected void btnResetSearchCriteria_Click(object sender, EventArgs e)
        {
            ResetSearchCriteria();
        }

        protected void btnPreview_Click(object sender, EventArgs e)
        {
            string url = string.Format("CustomReport.aspx?Sql={0}&t=", Server.UrlEncode(FinallySql().Replace(joinStr, ddlJoin.SelectedValue)), DateTime.Now.ToString("yyyyMMddHHmmssms"));
            this.RegisterJS(string.Format("window.open('{0}');", url));
        }
        #endregion

        #region Method
        private void InitData()
        {
            bool isSo = App_Code.SessionUtil.IsSORole;
            is_inventory_holding_unit bllHoldingUnit = new is_inventory_holding_unit();
            Bll.Common.Report.InitDropDownList(ddlHolder, true, null, bllHoldingUnit.GetAvailableISInventoryHolder(), "holding_name", "holding_id");
            if (App_Code.SessionUtil.IsHolding)
                this.ddlHolder.SelectedValue = CurrentHoldingId.ToString();
            if (!isSo)
            {
                ddlHolder.Enabled = false;
                ddlHolder.SelectedValue = CurrentHoldingId.ToString();
            }

            InitTableSet(isSo);
        }

        private void InitTableSet(bool isSORole)
        {
            string[] tableSetNameArr = Bll.Report.CustomReport.tableSetNameArr;
            int lastIndex = tableSetNameArr.Length - 1;
            if (!isSORole)
            {
                ddlTableSet.Items.Add(new ListItem(tableSetNameArr[lastIndex], lastIndex.ToString()));
            }
            else
            {
                for (int i = 0; i <= lastIndex; i++)
                    ddlTableSet.Items.Add(new ListItem(tableSetNameArr[i], i.ToString()));
            }
            ddlTableSet.SelectedIndex = 0;
            InitTable();
        }

        private void InitTable()
        {
            List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);
            Bll.Common.Report.InitDropDownList(ddlTable, false, null, listTable, "PageTableName", "DataBaseTableName");
            ddlTable.SelectedIndex = 0;
            InitColumn();
        }

        private CustomReportTable CurrentReportTable()
        {
            List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);
            CustomReportTable entity = (from table in listTable
                                        where table.DataBaseTableName == ddlTable.SelectedValue
                                        select table).FirstOrDefault<CustomReportTable>();
            return entity;
        }

        private void InitColumn()
        {
            List<CustomReportColumn> listColumn = CurrentReportTable().ListColumn;
            Bll.Common.Report.InitListBox(lstAvailableField, false, null, listColumn, "PageColumnName", "DataBaseColumnName");
            Bll.Common.Report.InitDropDownList(ddlAvailableFieldEnter, false, null, listColumn, "PageColumnName", "DataBaseColumnName");
            lstAvailableField.SelectedIndex = 0;
            ddlAvailableFieldEnter.SelectedIndex = 0;
            lstSelectedField.Items.Clear();
            ResetSearchCriteria();
            ControlValueEnter();
            ControlPreview();
        }

        private void SelectItem()
        {
            if (lstAvailableField.SelectedIndex != -1)
            {
                int currentIndex = lstAvailableField.SelectedIndex;
                ListItem li = new ListItem(lstAvailableField.SelectedItem.Text, lstAvailableField.SelectedItem.Value);
                lstSelectedField.Items.Add(li);
                lstAvailableField.Items.Remove(li);

                int count = lstAvailableField.Items.Count;
                if (count > 0)
                    lstAvailableField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;
            }
        }

        private void UnSelectItem()
        {
            if (lstSelectedField.SelectedIndex != -1)
            {
                int currentIndex = lstSelectedField.SelectedIndex;
                ListItem li = new ListItem(lstSelectedField.SelectedItem.Text, lstSelectedField.SelectedItem.Value);
                lstAvailableField.Items.Add(li);
                lstSelectedField.Items.Remove(li);

                int count = lstSelectedField.Items.Count;
                if (count > 0)
                    lstSelectedField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;
            }
        }

        private void SelectAllItem()
        {
            int count = lstAvailableField.Items.Count;
            if (count > 0)
            {
                foreach (ListItem li in lstAvailableField.Items)
                    lstSelectedField.Items.Add(li);
                lstAvailableField.Items.Clear();
            }
        }

        private void UnSelectAllItem()
        {
            int count = lstSelectedField.Items.Count;
            if (count > 0)
            {
                foreach (ListItem li in lstSelectedField.Items)
                    lstAvailableField.Items.Add(li);
                lstSelectedField.Items.Clear();
            }
        }

        private void AddCriteria()
        {
            bool isLike = ddlOperation.SelectedValue.IndexOf("like") != -1;
            bool isAddSingleQuotes = IsAddSingleQuotes();
            string val = txtValueEnter.Text;
            if (isLike)
            {
                val = string.Format("'{0}'", ddlOperation.SelectedValue.Replace("like", txtValueEnter.Text));
            }
            else if (isAddSingleQuotes)
            {
                val = string.Format("'{0}'", txtValueEnter.Text);
            }

            string criteriaVal = string.Format("{0}{1}{2}",
                                            ddlAvailableFieldEnter.SelectedItem.Value,
                                            isLike ? " like " : ddlOperation.SelectedValue,
                                            val);
            string criteriaText = string.Format("{0}{1}{2}",
                                            ddlAvailableFieldEnter.SelectedItem.Text,
                                            isLike ? " like " : ddlOperation.SelectedValue,
                                            val);

            if (txtSearchCriteriaVal.Text.IndexOf(criteriaVal) == -1)
            {
                if (isLike || isAddSingleQuotes || txtValueEnter.Text.Trim() != "")
                {
                    criteriaVal = string.Format("{0} {1}", txtSearchCriteriaVal.Text != "" ? " " + joinStr : "", criteriaVal);
                    txtSearchCriteriaVal.Text += criteriaVal;

                    criteriaText = string.Format("{0} {1}", txtSearchCriteria.Text != "" ? " " + joinStr : "", criteriaText);
                    txtSearchCriteria.Text += criteriaText;
                }
            }
        }

        private string FinallySql()
        {
            StringBuilder sb = new StringBuilder("select ");
            foreach (ListItem li in lstSelectedField.Items)
                sb.AppendFormat("{0},", li.Value);
            sb.Remove(sb.Length - 1, 1);
            string dataBaseTableName = ddlTable.SelectedValue;
            sb.AppendFormat(" from {0}", dataBaseTableName);
            string criteria = txtSearchCriteriaVal.Text;
            if (criteria != "")
                sb.AppendFormat(" where {0}", criteria);
            string holdingId = ddlHolder.SelectedValue;
            if (holdingId != "0")
            {
                sb.AppendFormat(" {0} Holding_Id={1}",
                                sb.ToString().IndexOf("where") != -1 ? "and" : "where",
                                holdingId);
            }
            if (Bll.Report.CustomReport.tableHaveUserId.IndexOf(dataBaseTableName) != -1)
            {
                sb.AppendFormat(" {0} User_Id={1}",
                                sb.ToString().IndexOf("where") != -1 ? "and" : "where",
                                CurrentUserId);
            }
            return sb.ToString();
        }

        private void ControlValueEnter()
        {
            txtValueEnter.Text = "";
            txtValueEnter.Attributes["onclick"] = "return true;";
            txtValueEnter.CssClass = "textBox";
            txtValueEnter.Attributes["onkeyup"] = "return true;";
            txtValueEnter.Attributes["onafterpaste"] = "return true;";

            ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;
            switch (ct)
            {
                case ColumnType.Number:
                    string jsStr = "this.value=this.value.replace(/[^0-9|^\\-|^\\.]/g,\'\');";
                    txtValueEnter.Attributes["onkeyup"] = jsStr;
                    txtValueEnter.Attributes["onafterpaste"] = jsStr;
                    break;
                case ColumnType.DateTime:
                    txtValueEnter.Attributes["onclick"] = "WdatePicker({dateFmt:'MM-dd-yyyy'})";
                    txtValueEnter.CssClass = "Wdate";
                    break;
                default:
                    break;
            }
        }

        private bool IsAddSingleQuotes()
        {
            ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;
            return ct != ColumnType.Number;
        }

        private void ResetSearchCriteria()
        {
            txtSearchCriteria.Text = "";
            txtSearchCriteriaVal.Text = "";
        }

        private void ControlPreview()
        {
            btnPreview.Enabled = lstSelectedField.Items.Count > 0;
        }
        #endregion
    }
}

 

CustomReport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomReport.aspx.cs" Inherits="IMSWeb.App.IS.Report.CustomReport" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset>
            <legend class="segmentTitle">Custom Report List</legend>
            <div style="margin-left: 10px; margin-bottom: 20px">
                <asp:GridView runat="server" ID="gvCustomReportList" AllowPaging="True" PageSize="20"
                    OnPageIndexChanging="gvCustomReportList_PageIndexChanging">
                </asp:GridView>
                <p />
                <asp:Button runat="server" Width="100px" ID="btnExportCSV" Text="Export Report" OnClick="btnExportCSV_Click" />&nbsp;&nbsp;
                <asp:Button runat="server" Width="100px" ID="btnPrint" Text="Print" />
            </div>
        </fieldset>
    </div>
    </form>
</body>
</html>

 

CustomReport.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Text;

namespace IMSWeb.App.IS.Report
{
    /// <summary>
    /// add by Kenmu at 2013-05-07
    /// </summary>
    public partial class CustomReport : BasePage
    {
        #region Event
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                InitData();
        }

        protected void gvCustomReportList_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvCustomReportList.PageIndex = e.NewPageIndex;
            InitData();
        }
         
        protected void btnExportCSV_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = Server.UrlDecode(Request["Sql"]);
                DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql);
                StringBuilder sbHeader = new StringBuilder();
                StringBuilder sbContent = new StringBuilder();
                DateTime tempDateTime = DateTime.MinValue;
                string tempVal = "";

                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    for (int j = 0, len2 = dt.Columns.Count; j < len2; j++)
                    {
                        if (i == 0)
                        {
                            sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName);
                        }

                        tempVal = dt.Rows[i][j].ToString();
                        if(DateTime.TryParse(tempVal,out tempDateTime))
                            tempVal = tempDateTime.ToString("dd-MM-yyyy");

                        sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal));
                    }
                    sbContent.Remove(sbContent.Length - 1, 1);
                    sbContent.AppendLine();
                }
                sbHeader.Remove(sbHeader.Length - 1, 1);
                sbHeader.AppendLine();

                IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response,
                                                          string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")),
                                                          sbHeader.ToString() + sbContent.ToString());
            }
            catch (Exception ex)
            {
                IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId);
                this.ShowErrorMsg(ex);
            }
        }
        #endregion

        #region Method
        private void InitData()
        {
            string sql = Server.UrlDecode(Request["Sql"]);
            DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql);
            int count = dt.Rows.Count;
            if (count == 0)
            {
                this.RegisterJS("alert('Without relevant data!');window.opener=null;window.open('','_self');window.close();");
                return;
            }
            gvCustomReportList.DataSource = dt;
            DynamicAddColumn(dt);
            gvCustomReportList.DataBind();
        }

        private void DynamicAddColumn(DataTable dt)
        {
            gvCustomReportList.Columns.Clear();
            for (int i = 0, len = dt.Columns.Count; i < len; i++)
                AddColumn(dt.Columns[i].ColumnName, dt.Columns[i].DataType.ToString());
        }

        private void AddColumn(string columnName, string type)
        {
            BoundField bf = new BoundField();
            bf.DataField = columnName;
            bf.HeaderText = columnName;
            switch (type)
            {
                case "System.DateTime"://日期类型
                    bf.DataFormatString = "{0:dd-MM-yyyy}";
                    break;
                //case "System.String"://字符串类型
                //    break;
                //case "System.Boolean"://布尔型 
                //    break;
                //case "System.Int16"://整型
                //case "System.Int32":
                //case "System.Int64":
                //case "System.Byte":
                //    break;
                //case "System.Decimal"://浮点型
                //case "System.Double":
                //    break;
                //case "System.DBNull"://Null值处理
                //    break;
                default:
                    break;
            }
            gvCustomReportList.Columns.Add(bf);
        }
        #endregion
    }
}