你的位置:首页 > ASP.net教程

[ASP.net教程]GridView动态添加列并判断绑定数据DataTable的列类型控制展示内容


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

下面简单说说需求点吧:

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

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

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

 

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

 

(1)数据库方面

 1 USE [IMSDB] 2 GO 3  4 drop view View_CustomReport_ItemCategory 5 go 6 drop view View_CustomReport_ItemClassification 7 go 8  9 ---- the view total is 21 10 alter view View_CustomReport_BasicInfo 11 as 12   select iig.holding_id as 'Holding_Id', 13      row_number() over(order by iig.item_group_id) as 'Internal_ID', 14      od.dept_name as 'Holder_Department', 15      og.group_name as 'Holder_Group', 16      os.section_name as 'Holder_Section', 17      ou.unit_name as 'Holder_Unit', 18      iig.brand as 'Brand', 19      iig.model as 'Model', 20      iica.name_en as 'Category', 21      iicaSub.name_en as 'Sub____category', 22      iicl.name_en as 'Classification', 23      iiclSub.name_en as 'Sub____classification', 24      iig.item_desc as 'GF272_Description', 25      (case when iig.inventory_flg=1 then 'inventory'  26         when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 27      (select ivc.balance  28       from is_view_item_group ivig 29       inner join is_view_column ivc 30         on ivig.sheet_id = ivc.sheet_id  31           and ivig.line_id = ivc.line_no  32           and ivig.column_id = ivc.column_no 33       where ivig.item_group_id = iig.item_group_id) as 'Current_Qty_Balance', 34      iig.unit_of_qty as 'Unit_of_Qty' 35   from is_item_group iig 36   inner join is_inventory_holding_unit iihu 37     on iig.holding_id = iihu.holding_id 38   left join org_department od 39     on iihu.dept_id = od.dept_id 40   left join org_group og 41     on iihu.group_id = og.group_id 42   left join org_section os 43     on iihu.section_id = os.section_id 44   left join org_unit ou 45     on iihu.unit_id = ou.unit_id 46   left join is_item_category iica 47     on iig.item_cat_id = iica.item_cat_id 48   left join is_item_category iicaSub 49     on iig.item_sub_cat_id = iicaSub.item_cat_id 50   left join is_item_classification iicl 51     on iig.item_classic_id = iicl.item_classific_id 52   left join is_item_classification iiclSub 53     on iig.item_sub_classic_id = iiclSub.item_classific_id 54 go 55  56 alter view View_CustomReport_GF272SheetInfo 57 as 58   select iig.holding_id as 'Holding_Id', 59      ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 60      ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 61      ivc.line_no as 'Last_GF272_Valid_Line_No000', 62      ivc.column_no as 'Last_GF272_Valid_Column_No000', 63      ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 64   from is_item_group iig 65   inner join is_view_item_group ivig 66     on iig.item_group_id = ivig.item_group_id 67   inner join is_view_sheet ivs 68     on ivig.sheet_id = ivs.sheet_id 69   inner join is_view_column ivc 70     on ivs.sheet_id = ivc.sheet_id 71   inner join is_view_line ivl 72     on ivs.sheet_id = ivl.sheet_id 73 go 74  75  76 alter view View_CustomReport_LineItemInformation 77 as 78   select iig.holding_id as 'Holding_Id', 79      od.dept_name as 'Holder_Department', 80      og.group_name as 'Holder_Group', 81      os.section_name as 'Holder_Section', 82      ou.unit_name as 'Holder_Unit', 83      iig.brand as 'Brand', 84      iig.model as 'Model', 85      iica.name_en as 'Category', 86      iicaSub.name_en as 'Sub____category', 87      iicl.name_en as 'Classification', 88      iiclSub.name_en as 'Sub____classification', 89      iig.item_desc as 'GF272_Description', 90      (case when iig.inventory_flg=1 then 'inventory'  91         when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 92      ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 93      ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 94      ivc.line_no as 'Last_GF272_Valid_Line_No000', 95      ivc.column_no as 'Last_GF272_Valid_Column_No000', 96      ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 97   from is_item_group iig 98   inner join is_inventory_holding_unit iihu 99     on iig.holding_id = iihu.holding_id100   left join org_department od101     on iihu.dept_id = od.dept_id102   left join org_group og103     on iihu.group_id = og.group_id104   left join org_section os105     on iihu.section_id = os.section_id106   left join org_unit ou107     on iihu.unit_id = ou.unit_id108   left join is_item_category iica109     on iig.item_cat_id = iica.item_cat_id110   left join is_item_category iicaSub111     on iig.item_sub_cat_id = iicaSub.item_cat_id112   left join is_item_classification iicl113     on iig.item_classic_id = iicl.item_classific_id114   left join is_item_classification iiclSub115     on iig.item_sub_classic_id = iiclSub.item_classific_id116   inner join is_view_item_group ivig117     on iig.item_group_id = ivig.item_group_id118   inner join is_view_sheet ivs119     on ivig.sheet_id = ivs.sheet_id120   inner join is_view_column ivc121     on ivs.sheet_id = ivc.sheet_id122   inner join is_view_line ivl123     on ivs.sheet_id = ivl.sheet_id124 go125 126 alter view View_CustomReport_IndividualItemBasicInfo127 as128   select iig.holding_id as 'Holding_Id',129      ii.serial_no as 'Serial_No000',130      ii.barcode_no as 'IMS_Barcode_No000',131      ii.self_assign_id as 'Self_Assign_ID',132      ii.detailed_desc as 'Detailed_Description',133      (case when ii.item_status='RJ' then 'Reject'134         when ii.item_status='TP' then 'Temp'135         when ii.item_status='DR' then 'Draft'136         when ii.item_status='N' then 'Normal'137         when ii.item_status='UN' then 'Unserviceable'138         when ii.item_status='TI' then 'Transfer'139         when ii.item_status='TO' then 'TransferOutOfICAC'140         when ii.item_status='D' then 'Disposed'141         when ii.item_status='WO' then 'WriteOff'142         when ii.item_status='SI' then 'SurplusItem'143         when ii.item_status='R' then 'Repair'144         when ii.item_status='TL' then 'TransferLedger' end) as 'Item_Status',145      ii.remarks as 'Remarks',146      ii.remarks2 as 'Remarks2',147      ii.remarks3 as 'Remarks3',148      ii.remarks4 as 'Remarks4'149   from is_item ii150   inner join is_item_group iig151     on ii.item_group_id = iig.item_group_id152 go153 154 alter view View_CustomReport_Location155 as156   select iig.holding_id as 'Holding_Id',157      (case when ii.head_quarter_flg=1 then 'Yes'158         when ii.head_quarter_flg=0 then 'No' end) as 'Headquarter_Flag',159      il.loc_name as 'Location_District',160      il2.loc_name as 'Location_Building',161      il3.loc_name as 'Location_Floor',162      il4.loc_name as 'Location_Room',163      ii.loc_area as 'Location_Area'164   from is_item ii165   inner join is_item_group iig166     on ii.item_group_id = iig.item_group_id167   left join is_location il168     on ii.loc_district = il.loc_id169   left join is_location il2170     on ii.loc_building = il2.loc_id171   left join is_location il3172     on ii.loc_floor = il3.loc_id173   left join is_location il4174     on ii.loc_room = il4.loc_id175 go176 177 alter view View_CustomReport_PhysicalCheckInfo178 as179   select iic.holding_id as 'Holding_Id',180      icld.update_datetime as 'Last_Physical_Checked_Date',181      icld.check_by as 'Last_Physical_Checked_By',182      (case when iic.check_status='IP' then 'InProgress'183         when iic.check_status='CP' then 'Completed'184         when iic.check_status='SC' then 'SoConfirm' end) as 'Last_Check_Status'185   from is_check_list icl186   inner join is_check_list_detail icld187     on icl.list_id = icld.list_id and icld.is_manual_check=1188   inner join is_inventory_check iic189     on icl.check_id = iic.check_id190 go191 192 alter view View_CustomReport_Aggregate193 as194   select iig.holding_id as 'Holding_Id',195      ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'196   from is_item ii197   inner join is_item_group iig198     on ii.item_group_id = iig.item_group_id199   inner join is_item_aggregate_map iiam200     on ii.item_id = iiam.parent_item_id201 go202 203 alter view View_CustomReport_PurchaseInfo204 as205   select iig.holding_id as 'Holding_Id',206      ii.pr_ref_no as 'Purchase_PR_Ref_No000',207      ii.quot_ref_no as 'Purchase_ICAC_Quotation_Ref_No000',208      ii.purchase_mode as 'Purchase_Mode',209      ii.po_no as 'Purchase_PO_No000___Ref',210      ii.invoice_no as 'Purchase_Invoice_No000',211      ii.date_of_order as 'Purchase_Date_of_Order',212      ii.date_of_receipt as 'Purchase_Date_of_Receipt',213      ii.date_of_acceptance as 'Purchase_Date_of_Acceptance',214      icim.contract_month as 'Free_Warranty_Period',215      ii.holder_file_ref as 'Holder_File_Reference',216      ii.contact_person as 'Contact_Person',217      ii.contact_person_post as 'Contact_Person_Position',218      itdi.voucher_type as 'Purchase_Related_Voucher_Type',219      itdi.voucher_no as 'Purchase_Related_Voucher_No000'220   from is_item ii221   inner join is_item_group iig222     on ii.item_group_id = iig.item_group_id223   inner join(224     select item_id,225        datediff(month,contract_start_date,contract_end_date) as 'contract_month'226     from is_contract_item_map227     where contract_id in(228       select min(contract_id) from is_contract_item_map229       group by item_id230     )231   ) as icim232     on ii.item_id = icim.item_id233   inner join(234     select itdi.item_id,itr.voucher_type,itr.voucher_no235     from is_tx_record itr 236     inner join is_tx_detail_item itdi237       on itr.tx_id = itdi.tx_id238     where itdi.tx_id in(239       select min(tx_id) from is_tx_detail_item240       group by item_id241     )242   ) as itdi243     on ii.item_id = itdi.item_id244 go245 246 create function Func_StrArrayStrOfIndex 247 ( 248  @str varchar(1024), --要分割的字符串 249  @split varchar(10), --分隔符号 250  @index int --取第几个元素 251 ) 252 returns varchar(1024) 253 as 254 begin 255  declare @location int 256  declare @start int 257  declare @next int 258  declare @seed int 259  260  set @str=ltrim(rtrim(@str)) 261  set @start=1 262  set @next=1 263  set @seed=len(@split) 264   265  set @location=charindex(@split,@str) 266  while @location<>0 and @index>@next 267  begin 268   set @start=@location+@seed 269   set @location=charindex(@split,@str,@start) 270   set @next=@next+1 271  end 272  if @location =0 select @location =len(@str)+1273   274  return substring(@str,@start,@location-@start) 275 end 276 go277 alter view View_CustomReport_LoanInfo278 as279   select iig.holding_id as 'Holding_Id',280      (case when ii.loan_flg=1 then 'On Loan'281         when ii.loan_flg=0 then 'In Hand' end) as 'Current_Loan_Status',282      isc.english_name as 'Staff_Name_of_Borrower',283      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',1) as 'Borrower111s_Department',284      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',2) as 'Borrower111s_Group',285      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',3) as 'Borrower111s_Section'286   from is_item ii287   inner join is_item_group iig288     on ii.item_group_id = iig.item_group_id289   left join is_loan_record ilr 290     on ii.loan_id = ilr.loan_id291   left join is_staff_card isc 292     on ilr.borrower_id = isc.staff_card_id293 go294 295 alter view View_CustomReport_IssueInfo296 as297   select iig.holding_id as 'Holding_Id',298      (case when ii.issue_flg=1 then 'Issued'299         when ii.issue_flg=0 then 'Not Issued' end) as 'Current_Issue_Status',300      od.dept_name as 'Issue_Dept',301      og.group_name as 'Issue_Group',302      os.section_name as 'Issue_Section',303      ou.unit_name as 'Issue_Unit'304   from is_item ii305   inner join is_item_group iig306     on ii.item_group_id = iig.item_group_id307   left join org_department od308     on ii.issued_dept_id = od.dept_id309   left join org_group og310     on ii.issued_group_id = og.group_id311   left join org_section os312     on ii.issued_section_id = os.section_id313   left join org_unit ou314     on ii.issued_unit_id = ou.unit_id315 go316 317 alter view View_CustomReport_EndUserInfo318 as319   select iig.holding_id as 'Holding_Id',320      ieur.end_user as 'Current_Item_End____user',321      convert(varchar(10),ieur.assign_date,105)+' to '+convert(varchar(10),ieur.return_date,105) as 'Usage_Date'322   from is_item ii323   inner join is_item_group iig324     on ii.item_group_id = iig.item_group_id325   inner join is_end_user_record ieur326     on ii.item_id = ieur.item_id327 go328 329 alter view View_CustomReport_SurplusInfo330 as331   select iig.holding_id as 'Holding_Id',332      (case when ii.item_status='SI' then 'Yes'333                      else 'No' end) as 'Surplus_Status',334      isipo.[start_date] as 'Surplus_Post_Out_Date_From',335      isipo.end_date as 'Surplus_Post_Out_Date_To'336   from is_item ii337   inner join is_item_group iig338     on ii.item_group_id = iig.item_group_id339   inner join(340     select item_id,[start_date],end_date341     from is_surplus_item_post_out342     where request_id in(343       select max(request_id) from is_surplus_item_post_out344       group by item_id345     )346   ) as isipo347     on ii.item_id = isipo.item_id348 go349 350 alter view View_CustomReport_DisposalInfo351 as352   select iig.holding_id as 'Holding_Id',353      ii.anticipated_date_disposal as 'Anticipated_Disposal_Date',354      (case when idr.disposal_status='R' or idr.disposal_status='V' then 'Not Applicable'355         when idr.disposal_status='D' or idr.disposal_status='W' then 'Requesting'356         when idr.disposal_status='T' then 'Approved by SO'357         when idr.disposal_status='P' then 'Physically Disposed' end) as 'Disposal_Status',358      idr.job_no as 'Dispose_Job_No000',359      idr.dumping_date as 'Date_of_Disposal',360      idr.item_condition as 'Condition_of_Item_in_Disposal',361      idr.disposal_method as 'Disposal_Method',362      itr.voucher_no as 'Disposal_Related_Voucher_No000'363   from is_item ii364   inner join is_item_group iig365     on ii.item_group_id = iig.item_group_id366   inner join is_disposal_record idr367     on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id368   left join is_request ir369     on idr.request_id = ir.request_id370   left join is_tx_record itr371     on ir.out_tx_id = itr.tx_id372 go373 374 alter view View_CustomReport_RepairInfo375 as376   select iig.holding_id as 'Holding_Id',377      (case when ii.item_status='R' then 'Yes'378                     else 'No' end) as 'On_Repair_Status'379   from is_item ii380   inner join is_item_group iig381     on ii.item_group_id = iig.item_group_id382 go383 384 alter view View_CustomReport_ReplaceInfo385 as386   select iig.holding_id as 'Holding_Id',387      (case when ii.replace_flg=1 then 'Yes'388                     else 'No' end) as 'Replaced_Status',389      ii.barcode_no as 'IMS_Barcode_No_of_Replacing_Item',390      itr.voucher_no as 'Voucher_No_for_Replaced_Item'391   from is_item ii392   inner join is_item_group iig393     on ii.item_group_id = iig.item_group_id394   inner join(395     select itdi.item_id,itr.voucher_no396     from is_tx_record itr397     inner join is_tx_detail_item itdi398     on itr.tx_id = itdi.tx_id399     where itdi.tx_id in(400       select max(tx_id) from is_tx_detail_item401       group by item_id402     )403   ) as itr404     on ii.item_id = itr.item_id405 go406 407 alter view View_CustomReport_DelegateMaintUserInfo408 as409   select iig.holding_id as 'Holding_Id',410      (case when ii.maint_dept_id is null then 'No'411         when ii.maint_dept_id=0 then 'No'412                     else 'Yes' end) as 'Delegated_to_others_for_Maintenance',413      od.dept_name as 'Maintenance_Dept',414      og.group_name as 'Maintenance_Group',415      os.section_name as 'Maintenance_Section',416      ou.unit_name as 'Maintenance_Unit'417   from is_item ii418   inner join is_item_group iig419     on ii.item_group_id = iig.item_group_id420   left join org_department od421     on ii.maint_dept_id = od.dept_id422   left join org_group og423     on ii.maint_group_id = og.group_id424   left join org_section os425     on ii.maint_section_id = os.section_id426   left join org_unit ou427     on ii.maint_unit_id = ou.unit_id428 go429 430 alter view View_CustomReport_MaintenanceBasic431 as432   select iig.holding_id as 'Holding_Id',433      ivm.vote_name as 'Maintenance_Vote',434      ii.remarks_maint as 'Remarks_for_Maintenance',435      img.grouping_name as 'Maint000_Grouping',436      iig.gld_contract_no as 'GLD_Contract_No000',437      (case when iig.further_maint_flag=1 then 'Yes'438                        else 'No' end) as 'Needed_to_be_Maintenance_Afterwards'439   from is_item ii440   inner join is_item_group iig441     on ii.item_group_id = iig.item_group_id442   inner join is_maint_grouping img443     on iig.maint_grouping = img.[grouping_id]444   left join is_contract_item_map icim445     on ii.item_id = icim.item_id446   left join is_vote_maintenance ivm447     on icim.vote = ivm.vote_id448 go449 450 alter view View_CustomReport_CurrentMaintenanceInfo451 as452   select iig.holding_id as 'Holding_Id',453      imc.ref_contract_id as 'Current_Maintenance_Contract_User_Reference_No000',454      iv.vendor_name as 'Current_Maintenance_Vendor',455      ii.annual_maint_cost as 'Current_Maintenance_Unit_Cost',456      icim.contract_start_date as 'Current_Maintenance_Coverage_Start_Date',457      icim.contract_end_date as 'Current_Maintenance_Coverage_End_Date',458      iml.level_desc as 'Current_Maintenance_Level',459      imc.bu_date as 'Next_Maintenance_BU_Date'460   from is_item ii461   inner join is_item_group iig462     on ii.item_group_id = iig.item_group_id463   left join is_contract_item_map icim464     on ii.item_id = icim.item_id465   left join is_maintenance_contract imc466     on icim.contract_id = imc.contract_id467   left join is_vender iv468     on icim.vendor_id = iv.vendor_id469   left join is_maint_level iml470     on icim.level_of_maint = iml.level_code471 go472 473 474 alter view View_CustomReport_ContractHeader475 as476   select iig.holding_id as 'Holding_Id',477      imc.ref_contract_id as 'Contract_User_Reference_No000',478      imc.contract_name as 'Contract_Name',479      (case when imc.maint_pr_no='null' then ''480                       else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',481      icim.contract_start_date as 'Contract_Start_Date',482      icim.contract_end_date as 'Contract_End_Date',483      imc.contact_person as 'Contact_Person',484      imc.contact_person_post as 'Contact_Post',485      imc.bu_date as 'BU_Date'486   from is_item ii487   inner join is_item_group iig488     on ii.item_group_id = iig.item_group_id489   left join is_contract_item_map icim490     on ii.item_id = icim.item_id491   left join is_maintenance_contract imc492     on icim.contract_id = imc.contract_id493 go494 495 alter view View_CustomReport_ItemsVendorInfo496 as497   select imc.holding_id as 'Holding_Id',498      iv.vendor_name as 'Maint_Vendor',499      icvm.contact_person as 'Maint_Vendor_Contact_Person',500      icvm.contact_person_post as 'Maint_Vendor_Contact_Person_Position',501      icvm.quotation_no as 'Maint_Vendor_Quotation_No000',502      icvp.maint_op_no as 'Maint_Po_No000__Ref',503      icvp.group_num as 'Payment_Schedule',504      icvp.actual_payment_date as 'Payment_Date',505      icvp.paid_amount_hkd as 'Paid_Amount',506      (case when icvp.payment_status='1' then 'Paid'507         when icvp.payment_status='0' then 'Not Paid' end) as 'Payment_Status',508      icvp.invoice_no as 'Invoice_No000',509      icvp.file_ref_no as 'File_Ref_No000'510   from is_vender iv511   inner join is_contract_vendor_map icvm512     on iv.vendor_id = icvm.vendor_id513   left join (514     select contract_id,515         vendor_id,516         maint_op_no,517         actual_payment_date,518         paid_amount_hkd,519         payment_status,520         invoice_no,521         file_ref_no,522        row_number() over(partition by contract_id,vendor_id 523                 order by actual_payment_date) as 'group_num'524     from is_contract_vendor_payment525   ) as icvp526     on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id527   inner join is_maintenance_contract imc528     on icvm.contract_id = imc.contract_id529 go530 531 alter view View_CustomReport_ItemsMaintenanceBasicInfo532 as533   select iig.holding_id as 'Holding_Id',534      ii.serial_no as 'Item_Serial_No000',535      ii.barcode_no as 'IMS_Item_Barcode_No000',536      iig.maint_grouping as 'Maintenance_Grouping',537      ivm.vote_name as 'Maintenance_Vote',538      ii.remarks_maint as 'Remarks_for_Maintenance',539      img.grouping_name as 'Maint000_Grouping',540      iig.gld_contract_no as 'GLD_Contract_No000',541      (case when iig.further_maint_flag=1 then 'Yes'542                        else 'No' end) as 'Needed_to_be_Maintenance_Afterwards',543      (case when imc.maint_pr_no='null' then ''544                       else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',545      icim.cost_total_hkd as 'Maintenance_Unit_Cost',546      icim.paid_total_price_hkd as 'Maintenance_Unit_Actual_Cost',547      icim.contract_start_date as 'Maintenance_Coverage_Start_Date',548      icim.contract_end_date as 'Maintenance_Coverage_End_Date',549      iml.level_desc as 'Maintenance_Level',550      icim.remarks as 'Item_Remarks_in_this_Contract'551   from is_item ii552   inner join is_item_group iig553     on ii.item_group_id = iig.item_group_id554   inner join is_maint_grouping img555     on iig.maint_grouping = img.[grouping_id]556   left join is_contract_item_map icim557     on ii.item_id = icim.item_id558   left join is_vote_maintenance ivm559     on icim.vote = ivm.vote_id560   left join is_maintenance_contract imc561     on icim.contract_id = imc.contract_id562   left join is_maint_level iml563     on icim.level_of_maint = iml.level_code564 go

 

(2)页面处理方面

CustomReport.cs

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Web; 6  7 using System.Data; 8 using DAL; 9  10 namespace Bll.Report 11 { 12   public enum ColumnType 13   { 14     Number = 0, 15     String = 1, 16     DateTime = 2 17   } 18  19   public class CustomReportColumn 20   { 21     #region Property 22     private string _PageColumnName; 23     public string PageColumnName 24     { 25       get { return _PageColumnName; } 26       set { _PageColumnName = value; } 27     } 28  29     private string _DataBaseColumnName; 30     public string DataBaseColumnName 31     { 32       get { return _DataBaseColumnName; } 33       set { _DataBaseColumnName = value; } 34     } 35  36     private ColumnType _ColumnType; 37     public ColumnType ColumnType 38     { 39       get { return _ColumnType; } 40       set { _ColumnType = value; } 41     } 42     #endregion 43   } 44  45   public class CustomReportTable 46   { 47     #region Property 48     private string _PageTableName; 49     public string PageTableName 50     { 51       get { return _PageTableName; } 52       set { _PageTableName = value; } 53     } 54  55     private string _DataBaseTableName; 56     public string DataBaseTableName 57     { 58       get { return _DataBaseTableName; } 59       set { _DataBaseTableName = value; } 60     } 61  62     private List<CustomReportColumn> _ListColumn; 63     public List<CustomReportColumn> ListColumn 64     { 65       get { return _ListColumn; } 66       set { _ListColumn = value; } 67     } 68     #endregion 69   } 70  71   public class CustomReport 72   { 73     public static string[] tableSetNameArr = new string[] { "Latest Information of Line Item", "Individual Item", "Maintenance Contract" }; 74     public static string tableHaveUserId = ""; 75  76     public static Dictionary<string, List<CustomReportTable>> TableSet(int cacheMinutes) 77     { 78       Dictionary<string, List<CustomReportTable>> tableSet = new Dictionary<string, List<CustomReportTable>>(); 79       for (int i = 0, len = tableSetNameArr.Length; i < len; i++) 80         tableSet.Add(tableSetNameArr[i], ListTable(i, 0)); 81       return tableSet; 82     } 83  84     public static List<CustomReportTable> ListTable(int index, int cacheMinutes) 85     { 86       string cacheKey = string.Format("CustomReport_ListTable_{0}", index); 87       List<CustomReportTable> listTable = HttpContext.Current.Cache[cacheKey] as List<CustomReportTable>; 88       if (listTable == null) 89       { 90         listTable = new List<CustomReportTable>(); 91         InitDataForTable(index, ref listTable); 92  93         if (listTable.Count > 0 && cacheMinutes > 0) 94           HttpContext.Current.Cache.Insert(cacheKey, 95                  listTable, 96                 null, 97                  DateTime.Now.AddMinutes(cacheMinutes), 98                  System.Web.Caching.Cache.NoSlidingExpiration); 99       }100       return listTable;101     }102 103     private static void InitDataForTable(int index, ref List<CustomReportTable> listTable)104     {105       List<CustomReportColumn> listColumn;106       switch (index)107       {108         case 0:109           #region Latest Information of Line Item110           #region Basic Info111           listColumn = new List<CustomReportColumn>();112           listColumn.Add(new CustomReportColumn() { PageColumnName = "Internal ID", DataBaseColumnName = "Internal_ID", ColumnType = ColumnType.Number });113           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });114           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });115           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });116           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });117           listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });118           listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });119           listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });120           listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });121           listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });122           listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });123           listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });124           listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });125           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Qty Balance", DataBaseColumnName = "Current_Qty_Balance", ColumnType = ColumnType.Number });126           listColumn.Add(new CustomReportColumn() { PageColumnName = "Unit of Qty", DataBaseColumnName = "Unit_of_Qty", ColumnType = ColumnType.Number });127           listTable.Add(new CustomReportTable()128           {129             PageTableName = "Basic Info",130             DataBaseTableName = "View_CustomReport_BasicInfo",131             ListColumn = listColumn132           });133           #endregion134 135           #region GF272 Sheet Info136           listColumn = new List<CustomReportColumn>();137           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });138           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });139           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });140           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });141           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });142           listTable.Add(new CustomReportTable()143           {144             PageTableName = "GF272 Sheet Info",145             DataBaseTableName = "View_CustomReport_GF272SheetInfo",146             ListColumn = listColumn147           });148           #endregion149           #endregion150           break;151         case 1:152           #region Individual Item153           #region Line Item Information154           listColumn = new List<CustomReportColumn>();155           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });156           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });157           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });158           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });159           listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });160           listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });161           listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });162           listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });163           listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });164           listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });165           listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });166           listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });167           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });168           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });169           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });170           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });171           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });172           listTable.Add(new CustomReportTable()173           {174             PageTableName = "Line Item Information",175             DataBaseTableName = "View_CustomReport_LineItemInformation",176             ListColumn = listColumn177           });178           #endregion179 180           #region Individual Item Basic Info181           listColumn = new List<CustomReportColumn>();182           listColumn.Add(new CustomReportColumn() { PageColumnName = "Serial No.", DataBaseColumnName = "Serial_No000", ColumnType = ColumnType.String });183           listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No.", DataBaseColumnName = "IMS_Barcode_No000", ColumnType = ColumnType.String });184           listColumn.Add(new CustomReportColumn() { PageColumnName = "Self Assign ID", DataBaseColumnName = "Self_Assign_ID", ColumnType = ColumnType.String });185           listColumn.Add(new CustomReportColumn() { PageColumnName = "Detailed Description", DataBaseColumnName = "Detailed_Description", ColumnType = ColumnType.String });186           listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Status", DataBaseColumnName = "Item_Status", ColumnType = ColumnType.String });187           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks", DataBaseColumnName = "Remarks", ColumnType = ColumnType.String });188           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks2", DataBaseColumnName = "Remarks2", ColumnType = ColumnType.String });189           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks3", DataBaseColumnName = "Remarks3", ColumnType = ColumnType.String });190           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks4", DataBaseColumnName = "Remarks4", ColumnType = ColumnType.String });191           listTable.Add(new CustomReportTable()192           {193             PageTableName = "Individual Item Basic Info",194             DataBaseTableName = "View_CustomReport_IndividualItemBasicInfo",195             ListColumn = listColumn196           });197           #endregion198 199           #region Location200           listColumn = new List<CustomReportColumn>();201           listColumn.Add(new CustomReportColumn() { PageColumnName = "Headquarter Flag", DataBaseColumnName = "Headquarter_Flag", ColumnType = ColumnType.String });202           listColumn.Add(new CustomReportColumn() { PageColumnName = "Location District", DataBaseColumnName = "Location_District", ColumnType = ColumnType.String });203           listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Building", DataBaseColumnName = "Location_Building", ColumnType = ColumnType.String });204           listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Floor", DataBaseColumnName = "Location_Floor", ColumnType = ColumnType.String });205           listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Room", DataBaseColumnName = "Location_Room", ColumnType = ColumnType.String });206           listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Area", DataBaseColumnName = "Location_Area", ColumnType = ColumnType.String });207           listTable.Add(new CustomReportTable()208           {209             PageTableName = "Location",210             DataBaseTableName = "View_CustomReport_Location",211             ListColumn = listColumn212           });213           #endregion214 215           #region Physical Check Info216           listColumn = new List<CustomReportColumn>();217           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked Date", DataBaseColumnName = "Last_Physical_Checked_Date", ColumnType = ColumnType.DateTime });218           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked By", DataBaseColumnName = "Last_Physical_Checked_By", ColumnType = ColumnType.String });219           listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Check Status", DataBaseColumnName = "Last_Check_Status", ColumnType = ColumnType.String });220           listTable.Add(new CustomReportTable()221           {222             PageTableName = "Physical Check Info",223             DataBaseTableName = "View_CustomReport_PhysicalCheckInfo",224             ListColumn = listColumn225           });226           #endregion227 228           #region Aggregate229           listColumn = new List<CustomReportColumn>();230           listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No. of Aggregate Parent", DataBaseColumnName = "IMS_Barcode_No000_of_Aggregate_Parent", ColumnType = ColumnType.String });231           listTable.Add(new CustomReportTable()232           {233             PageTableName = "Aggregate",234             DataBaseTableName = "View_CustomReport_Aggregate",235             ListColumn = listColumn236           });237           #endregion238 239           #region Purchase Info240           listColumn = new List<CustomReportColumn>();241           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PR Ref No.", DataBaseColumnName = "Purchase_PR_Ref_No000", ColumnType = ColumnType.String });242           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase ICAC Quotation Ref No.", DataBaseColumnName = "Purchase_ICAC_Quotation_Ref_No000", ColumnType = ColumnType.String });243           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Mode", DataBaseColumnName = "Purchase_Mode", ColumnType = ColumnType.String });244           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PO No./ Ref", DataBaseColumnName = "Purchase_PO_No000___Ref", ColumnType = ColumnType.String });245           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Invoice No.", DataBaseColumnName = "Purchase_Invoice_No000", ColumnType = ColumnType.String });246           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Order", DataBaseColumnName = "Purchase_Date_of_Order", ColumnType = ColumnType.DateTime });247           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Receipt", DataBaseColumnName = "Purchase_Date_of_Receipt", ColumnType = ColumnType.DateTime });248           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Acceptance", DataBaseColumnName = "Purchase_Date_of_Acceptance", ColumnType = ColumnType.DateTime });249           listColumn.Add(new CustomReportColumn() { PageColumnName = "Free Warranty Period", DataBaseColumnName = "Free_Warranty_Period", ColumnType = ColumnType.Number });250           listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder File Reference", DataBaseColumnName = "Holder_File_Reference", ColumnType = ColumnType.String });251           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });252           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person Position", DataBaseColumnName = "Contact_Person_Position", ColumnType = ColumnType.String });253           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher Type", DataBaseColumnName = "Purchase_Related_Voucher_Type", ColumnType = ColumnType.String });254           listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher No.", DataBaseColumnName = "Purchase_Related_Voucher_No000", ColumnType = ColumnType.String });255           listTable.Add(new CustomReportTable()256           {257             PageTableName = "Purchase Info",258             DataBaseTableName = "View_CustomReport_PurchaseInfo",259             ListColumn = listColumn260           });261           #endregion262 263           #region Loan Info264           listColumn = new List<CustomReportColumn>();265           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Loan Status", DataBaseColumnName = "Current_Loan_Status", ColumnType = ColumnType.String });266           listColumn.Add(new CustomReportColumn() { PageColumnName = "Staff Name of Borrower", DataBaseColumnName = "Staff_Name_of_Borrower", ColumnType = ColumnType.String });267           listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Department", DataBaseColumnName = "Borrower111s_Department", ColumnType = ColumnType.String });268           listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Group", DataBaseColumnName = "Borrower111s_Group", ColumnType = ColumnType.String });269           listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Section", DataBaseColumnName = "Borrower111s_Section", ColumnType = ColumnType.String });270           listTable.Add(new CustomReportTable()271           {272             PageTableName = "Loan Info",273             DataBaseTableName = "View_CustomReport_LoanInfo",274             ListColumn = listColumn275           });276           #endregion277 278           #region Issue Info279           listColumn = new List<CustomReportColumn>();280           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Issue Status", DataBaseColumnName = "Current_Issue_Status", ColumnType = ColumnType.String });281           listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Dept", DataBaseColumnName = "Issue_Dept", ColumnType = ColumnType.String });282           listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Group", DataBaseColumnName = "Issue_Group", ColumnType = ColumnType.String });283           listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Section", DataBaseColumnName = "Issue_Section", ColumnType = ColumnType.String });284           listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Unit", DataBaseColumnName = "Issue_Unit", ColumnType = ColumnType.String });285           listTable.Add(new CustomReportTable()286           {287             PageTableName = "Issue Info",288             DataBaseTableName = "View_CustomReport_IssueInfo",289             ListColumn = listColumn290           });291           #endregion292 293           #region End-user info294           listColumn = new List<CustomReportColumn>();295           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Item End-user", DataBaseColumnName = "Current_Item_End____user", ColumnType = ColumnType.String });296           listColumn.Add(new CustomReportColumn() { PageColumnName = "Usage Date", DataBaseColumnName = "Usage_Date", ColumnType = ColumnType.String });297           listTable.Add(new CustomReportTable()298           {299             PageTableName = "End-user info",300             DataBaseTableName = "View_CustomReport_EndUserInfo",301             ListColumn = listColumn302           });303           #endregion304 305           #region Surplus Info306           listColumn = new List<CustomReportColumn>();307           listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Status", DataBaseColumnName = "Surplus_Status", ColumnType = ColumnType.String });308           listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date From", DataBaseColumnName = "Surplus_Post_Out_Date_From", ColumnType = ColumnType.DateTime });309           listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date To", DataBaseColumnName = "Surplus_Post_Out_Date_To", ColumnType = ColumnType.DateTime });310           listTable.Add(new CustomReportTable()311           {312             PageTableName = "Surplus Info",313             DataBaseTableName = "View_CustomReport_SurplusInfo",314             ListColumn = listColumn315           });316           #endregion317 318           #region Disposal Info319           listColumn = new List<CustomReportColumn>();320           listColumn.Add(new CustomReportColumn() { PageColumnName = "Anticipated Disposal Date", DataBaseColumnName = "Anticipated_Disposal_Date", ColumnType = ColumnType.DateTime });321           listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Status", DataBaseColumnName = "Disposal_Status", ColumnType = ColumnType.String });322           listColumn.Add(new CustomReportColumn() { PageColumnName = "Dispose Job No.", DataBaseColumnName = "Dispose_Job_No000", ColumnType = ColumnType.String });323           listColumn.Add(new CustomReportColumn() { PageColumnName = "Date of Disposal", DataBaseColumnName = "Date_of_Disposal", ColumnType = ColumnType.DateTime });324           listColumn.Add(new CustomReportColumn() { PageColumnName = "Condition of Item in Disposal", DataBaseColumnName = "Condition_of_Item_in_Disposal", ColumnType = ColumnType.String });325           listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Method", DataBaseColumnName = "Disposal_Method", ColumnType = ColumnType.String });326           listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Related Voucher No.", DataBaseColumnName = "Disposal_Related_Voucher_No000", ColumnType = ColumnType.String });327           listTable.Add(new CustomReportTable()328           {329             PageTableName = "Disposal Info",330             DataBaseTableName = "View_CustomReport_DisposalInfo",331             ListColumn = listColumn332           });333           #endregion334 335           #region Repair Info336           listColumn = new List<CustomReportColumn>();337           listColumn.Add(new CustomReportColumn() { PageColumnName = "On Repair Status", DataBaseColumnName = "On_Repair_Status", ColumnType = ColumnType.String });338           listTable.Add(new CustomReportTable()339           {340             PageTableName = "Repair Info",341             DataBaseTableName = "View_CustomReport_RepairInfo",342             ListColumn = listColumn343           });344           #endregion345 346           #region Replace Info347           listColumn = new List<CustomReportColumn>();348           listColumn.Add(new CustomReportColumn() { PageColumnName = "Replaced Status", DataBaseColumnName = "Replaced_Status", ColumnType = ColumnType.String });349           listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No of Replacing Item", DataBaseColumnName = "IMS_Barcode_No_of_Replacing_Item", ColumnType = ColumnType.String });350           listColumn.Add(new CustomReportColumn() { PageColumnName = "Voucher No for Replaced Item", DataBaseColumnName = "Voucher_No_for_Replaced_Item", ColumnType = ColumnType.String });351           listTable.Add(new CustomReportTable()352           {353             PageTableName = "Replace Info",354             DataBaseTableName = "View_CustomReport_ReplaceInfo",355             ListColumn = listColumn356           });357           #endregion358 359           #region Delegate Maint User Info360           listColumn = new List<CustomReportColumn>();361           listColumn.Add(new CustomReportColumn() { PageColumnName = "Delegated to others for Maintenance", DataBaseColumnName = "Delegated_to_others_for_Maintenance", ColumnType = ColumnType.String });362           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Dept", DataBaseColumnName = "Maintenance_Dept", ColumnType = ColumnType.String });363           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Group", DataBaseColumnName = "Maintenance_Group", ColumnType = ColumnType.String });364           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Section", DataBaseColumnName = "Maintenance_Section", ColumnType = ColumnType.String });365           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit", DataBaseColumnName = "Maintenance_Unit", ColumnType = ColumnType.String });366           listTable.Add(new CustomReportTable()367           {368             PageTableName = "Delegate Maint User Info",369             DataBaseTableName = "View_CustomReport_DelegateMaintUserInfo",370             ListColumn = listColumn371           });372           #endregion373 374           #region Maintenance Basic375           listColumn = new List<CustomReportColumn>();376           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });377           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });378           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });379           listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });380           listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });381           listTable.Add(new CustomReportTable()382           {383             PageTableName = "Maintenance Basic",384             DataBaseTableName = "View_CustomReport_MaintenanceBasic",385             ListColumn = listColumn386           });387           #endregion388 389           #region Current Maintenance Info390           listColumn = new List<CustomReportColumn>();391           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Contract User Reference No.", DataBaseColumnName = "Current_Maintenance_Contract_User_Reference_No000", ColumnType = ColumnType.String });392           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Vendor", DataBaseColumnName = "Current_Maintenance_Vendor", ColumnType = ColumnType.String });393           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Unit Cost", DataBaseColumnName = "Current_Maintenance_Unit_Cost", ColumnType = ColumnType.Number });394           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage Start Date", DataBaseColumnName = "Current_Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });395           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage End Date", DataBaseColumnName = "Current_Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });396           listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Level", DataBaseColumnName = "Current_Maintenance_Level", ColumnType = ColumnType.String });397           listColumn.Add(new CustomReportColumn() { PageColumnName = "Next Maintenance BU Date", DataBaseColumnName = "Next_Maintenance_BU_Date", ColumnType = ColumnType.DateTime });398           listTable.Add(new CustomReportTable()399           {400             PageTableName = "Current Maintenance Info",401             DataBaseTableName = "View_CustomReport_CurrentMaintenanceInfo",402             ListColumn = listColumn403           });404           #endregion405           #endregion406           break;407         case 2:408           #region Maintenance Contract409           #region Contract Header410           listColumn = new List<CustomReportColumn>();411           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract User Reference No.", DataBaseColumnName = "Contract_User_Reference_No000", ColumnType = ColumnType.String });412           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Name", DataBaseColumnName = "Contract_Name", ColumnType = ColumnType.String });413           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });414           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Start Date", DataBaseColumnName = "Contract_Start_Date", ColumnType = ColumnType.DateTime });415           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract End Date", DataBaseColumnName = "Contract_End_Date", ColumnType = ColumnType.DateTime });416           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });417           listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Post", DataBaseColumnName = "Contact_Post", ColumnType = ColumnType.String });418           listColumn.Add(new CustomReportColumn() { PageColumnName = "BU Date", DataBaseColumnName = "BU_Date", ColumnType = ColumnType.DateTime });419           listTable.Add(new CustomReportTable()420           {421             PageTableName = "Contract Header",422             DataBaseTableName = "View_CustomReport_ContractHeader",423             ListColumn = listColumn424           });425           #endregion426 427           #region Item’s Vendor Info428           listColumn = new List<CustomReportColumn>();429           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor", DataBaseColumnName = "Maint_Vendor", ColumnType = ColumnType.String });430           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person", DataBaseColumnName = "Maint_Vendor_Contact_Person", ColumnType = ColumnType.String });431           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person Position", DataBaseColumnName = "Maint_Vendor_Contact_Person_Position", ColumnType = ColumnType.String });432           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Quotation No.", DataBaseColumnName = "Maint_Vendor_Quotation_No000", ColumnType = ColumnType.String });433           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Po No./Ref", DataBaseColumnName = "Maint_Po_No000__Ref", ColumnType = ColumnType.String });434           listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Schedule", DataBaseColumnName = "Payment_Schedule", ColumnType = ColumnType.Number });435           listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Date", DataBaseColumnName = "Payment_Date", ColumnType = ColumnType.DateTime });436           listColumn.Add(new CustomReportColumn() { PageColumnName = "Paid Amount", DataBaseColumnName = "Paid_Amount", ColumnType = ColumnType.Number });437           listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Status", DataBaseColumnName = "Payment_Status", ColumnType = ColumnType.String });438           listColumn.Add(new CustomReportColumn() { PageColumnName = "Invoice No.", DataBaseColumnName = "Invoice_No000", ColumnType = ColumnType.String });439           listColumn.Add(new CustomReportColumn() { PageColumnName = "File Ref No.", DataBaseColumnName = "File_Ref_No000", ColumnType = ColumnType.String });440           listTable.Add(new CustomReportTable()441           {442             PageTableName = "Item’s Vendor Info",443             DataBaseTableName = "View_CustomReport_ItemsVendorInfo",444             ListColumn = listColumn445           });446           #endregion447 448           #region Item’s Maintenance Basic Info449           listColumn = new List<CustomReportColumn>();450           listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Serial No.", DataBaseColumnName = "Item_Serial_No000", ColumnType = ColumnType.String });451           listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Item Barcode No.", DataBaseColumnName = "IMS_Item_Barcode_No000", ColumnType = ColumnType.String });452           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Grouping", DataBaseColumnName = "Maintenance_Grouping", ColumnType = ColumnType.String });453           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });454           listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });455           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });456           listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });457           listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });458           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });459           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Cost", DataBaseColumnName = "Maintenance_Unit_Cost", ColumnType = ColumnType.Number });460           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Actual Cost", DataBaseColumnName = "Maintenance_Unit_Actual_Cost", ColumnType = ColumnType.Number });461           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage Start Date", DataBaseColumnName = "Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });462           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage End Date", DataBaseColumnName = "Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });463           listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Level", DataBaseColumnName = "Maintenance_Level", ColumnType = ColumnType.String });464           listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Remarks in this Contract", DataBaseColumnName = "Item_Remarks_in_this_Contract", ColumnType = ColumnType.String });465           listTable.Add(new CustomReportTable()466           {467             PageTableName = "Item’s Maintenance Basic Info",468             DataBaseTableName = "View_CustomReport_ItemsMaintenanceBasicInfo",469             ListColumn = listColumn470           });471           #endregion472           #endregion473           break;474       }475     }476 477     public static DataTable GetCustomReport(string sql)478     {479       DataTable dt = SqlHelper.ExecuteDataTable(sql);480       if (dt != null)481       {482         for (int i = 0, len = dt.Columns.Count; i < len; i++)483           dt.Columns[i].ColumnName = ConvertColumnName(dt.Columns[i].ColumnName);484       }485       return dt;486     }487 488     public static string ConvertColumnName(string columnName)489     {490       string convertColumnName = "";491       if (!string.IsNullOrEmpty(columnName))492         convertColumnName = columnName.Replace("____", "-").Replace("000", ".").Replace("__", "/").Replace("_", " ").Replace("111", "'");493       return convertColumnName;494     }495   }496 }

 

SearchCustomReport.aspx

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SearchCustomReport.aspx.cs" 2   Inherits="IMSWeb.App.IS.Report.SearchCustomReport" %> 3  4 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 5 <html "http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7   <title></title> 8 </head> 9 <body> 10   <form id="form1" runat="server"> 11   <div> 12     <fieldset class="pageFieldset" style="width: 800px"> 13       <legend class="segmentTitle">Custom Report Search</legend> 14       <table class="tblDetail"> 15         <tr style="display:none"> 16           <td> 17             Issued Item 18           </td> 19           <td colspan="3"> 20             <asp:DropDownList ID="ddlIssuedItem" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlIssuedItem_SelectedIndexChanged" 21               Width="160px"> 22               <asp:ListItem Value="">--ALL--</asp:ListItem> 23               <asp:ListItem Value="1">--Show--</asp:ListItem> 24               <asp:ListItem Value="0">--Don't Show--</asp:ListItem> 25             </asp:DropDownList> 26           </td> 27         </tr> 28         <tr> 29           <td style=" white-space:200px;"> 30             Inventory Holder 31           </td> 32           <td colspan="3"> 33             <asp:DropDownList ID="ddlHolder" runat="server"> 34             </asp:DropDownList> 35           </td> 36         </tr> 37         <tr> 38           <td> 39             Table Set 40           </td> 41           <td> 42             <asp:DropDownList ID="ddlTableSet" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTableSet_SelectedIndexChanged" 43               Width="160px"> 44             </asp:DropDownList> 45           </td> 46           <td style="width: 80px;"> 47             Item Group 48           </td> 49           <td> 50             <asp:DropDownList ID="ddlTable" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTable_SelectedIndexChanged" 51               Width="160px"> 52             </asp:DropDownList> 53           </td> 54         </tr> 55         <tr> 56           <td> 57             Item Name 58           </td> 59           <td colspan="3"> 60             <table cellpadding="0" cellspacing="0" style="width: 100%;"> 61               <tr> 62                 <td> 63                   Brand 64                 </td> 65                 <td rowspan="2" class="style1"> 66                   <asp:Button ID="btnSelectAll" runat="server" Text=">>" OnClick="btnSelectAll_Click" /> 67                   <p /> 68                   <asp:Button ID="btnSelect" runat="server" Text=">" OnClick="btnSelect_Click" /> 69                   <p /> 70                   &nbsp; 71                   <p /> 72                   <asp:Button ID="btnUnSelect" runat="server" Text="<" OnClick="btnUnSelect_Click" /> 73                   <p /> 74                   <asp:Button ID="btnUnSelectAll" runat="server" Text="<<" OnClick="btnUnSelectAll_Click" /> 75                 </td> 76                 <td> 77                   Model 78                 </td> 79               </tr> 80               <tr> 81                 <td style="padding-left: 0; width: 244px;"> 82                   <asp:ListBox ID="lstAvailableField" runat="server" Width="160px" Rows="15"></asp:ListBox> 83                 </td> 84                 <td style="padding-left: 0"> 85                   <asp:ListBox ID="lstSelectedField" runat="server" Width="160px" Rows="15"></asp:ListBox> 86                 </td> 87               </tr> 88             </table> 89           </td> 90         </tr> 91         <tr> 92           <td> 93             Enter Criteria 94           </td> 95           <td colspan="3"> 96             <asp:DropDownList ID="ddlAvailableFieldEnter" runat="server" Width="160px" AutoPostBack="true" 97               OnSelectedIndexChanged="ddlAvailableFieldEnter_SelectedIndexChanged"> 98             </asp:DropDownList> 99             &nbsp;&nbsp;100             <asp:DropDownList ID="ddlOperation" runat="server" Width="50px">101               <asp:ListItem Value="="></asp:ListItem>102               <asp:ListItem Value=">"></asp:ListItem>103               <asp:ListItem Value="<"></asp:ListItem>104               <%--<asp:ListItem Value="%like%"></asp:ListItem>105               <asp:ListItem Value="%like"></asp:ListItem>106               <asp:ListItem Value="like%"></asp:ListItem>--%>107             </asp:DropDownList>108             &nbsp;&nbsp;109             <asp:TextBox ID="txtValueEnter" runat="server" Width="228px"></asp:TextBox>&nbsp;&nbsp;110             <asp:Button ID="btnAddCriteria" runat="server" Text="Add" OnClick="btnAddCriteria_Click" />111             <asp:DropDownList ID="ddlJoin" runat="server" Width="50px" Visible="false">112               <asp:ListItem Value="And"></asp:ListItem>113               <asp:ListItem Value="Or"></asp:ListItem>114             </asp:DropDownList>115             &nbsp;&nbsp; &nbsp;&nbsp;116           </td>117         </tr>118         <tr>119           <td>120             Search Criteria121           </td>122           <td colspan="3">123             <asp:TextBox ID="txtSearchCriteria" runat="server" Width="469px" Height="100px" ReadOnly="true"124               TextMode="MultiLine"></asp:TextBox>&nbsp;&nbsp;125             <asp:TextBox ID="txtSearchCriteriaVal" runat="server" ReadOnly="true" Style="display:none"></asp:TextBox>126             <asp:Button ID="btnResetSearchCriteria" runat="server" Text="Reset" OnClick="btnResetSearchCriteria_Click" />127           </td>128         </tr>129       </table>130       <div class="block">131         <asp:Button ID="btnPreview" runat="server" Text="Preview" ValidationGroup="lose"132           OnClick="btnPreview_Click" />133       </div>134     </fieldset>135   </div>136   </form>137 </body>138 </html>

 

SearchCustomReport.aspx.cs

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7  8 using Bll; 9 using System.Text; 10 using Bll.Report; 11  12 namespace IMSWeb.App.IS.Report 13 { 14   /// <summary> 15   /// add by Kenmu at 2013-04-28 16   /// </summary> 17   public partial class SearchCustomReport : BasePage 18   { 19     public const string joinStr = "\r\n"; 20  21     #region Property 22  23     #endregion 24  25     #region Event 26     protected void Page_Load(object sender, EventArgs e) 27     { 28       if (!IsPostBack) 29         InitData(); 30     } 31  32     protected void ddlIssuedItem_SelectedIndexChanged(object sender, EventArgs e) 33     { 34     } 35  36     protected void ddlTableSet_SelectedIndexChanged(object sender, EventArgs e) 37     { 38       InitTable(); 39     } 40  41     protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e) 42     { 43       InitColumn(); 44     } 45  46     protected void btnSelect_Click(object sender, EventArgs e) 47     { 48       SelectItem(); 49       ControlPreview(); 50     } 51  52     protected void btnUnSelect_Click(object sender, EventArgs e) 53     { 54       UnSelectItem(); 55       ControlPreview(); 56     } 57  58     protected void btnSelectAll_Click(object sender, EventArgs e) 59     { 60       SelectAllItem(); 61       ControlPreview(); 62     } 63  64     protected void btnUnSelectAll_Click(object sender, EventArgs e) 65     { 66       UnSelectAllItem(); 67       ControlPreview(); 68     } 69  70     protected void ddlAvailableFieldEnter_SelectedIndexChanged(object sender, EventArgs e) 71     { 72       ControlValueEnter(); 73     } 74  75     protected void btnAddCriteria_Click(object sender, EventArgs e) 76     { 77       AddCriteria(); 78     } 79  80     protected void btnResetSearchCriteria_Click(object sender, EventArgs e) 81     { 82       ResetSearchCriteria(); 83     } 84  85     protected void btnPreview_Click(object sender, EventArgs e) 86     { 87       string url = string.Format("CustomReport.aspx?Sql={0}&t=", Server.UrlEncode(FinallySql().Replace(joinStr, ddlJoin.SelectedValue)), DateTime.Now.ToString("yyyyMMddHHmmssms")); 88       this.RegisterJS(string.Format("window.open('{0}');", url)); 89     } 90     #endregion 91  92     #region Method 93     private void InitData() 94     { 95       bool isSo = App_Code.SessionUtil.IsSORole; 96       is_inventory_holding_unit bllHoldingUnit = new is_inventory_holding_unit(); 97       Bll.Common.Report.InitDropDownList(ddlHolder, true, null, bllHoldingUnit.GetAvailableISInventoryHolder(), "holding_name", "holding_id"); 98       if (App_Code.SessionUtil.IsHolding) 99         this.ddlHolder.SelectedValue = CurrentHoldingId.ToString();100       if (!isSo)101       {102         ddlHolder.Enabled = false;103         ddlHolder.SelectedValue = CurrentHoldingId.ToString();104       }105 106       InitTableSet(isSo);107     }108 109     private void InitTableSet(bool isSORole)110     {111       string[] tableSetNameArr = Bll.Report.CustomReport.tableSetNameArr;112       int lastIndex = tableSetNameArr.Length - 1;113       if (!isSORole)114       {115         ddlTableSet.Items.Add(new ListItem(tableSetNameArr[lastIndex], lastIndex.ToString()));116       }117       else118       {119         for (int i = 0; i <= lastIndex; i++)120           ddlTableSet.Items.Add(new ListItem(tableSetNameArr[i], i.ToString()));121       }122       ddlTableSet.SelectedIndex = 0;123       InitTable();124     }125 126     private void InitTable()127     {128       List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);129       Bll.Common.Report.InitDropDownList(ddlTable, false, null, listTable, "PageTableName", "DataBaseTableName");130       ddlTable.SelectedIndex = 0;131       InitColumn();132     }133 134     private CustomReportTable CurrentReportTable()135     {136       List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);137       CustomReportTable entity = (from table in listTable138                     where table.DataBaseTableName == ddlTable.SelectedValue139                     select table).FirstOrDefault<CustomReportTable>();140       return entity;141     }142 143     private void InitColumn()144     {145       List<CustomReportColumn> listColumn = CurrentReportTable().ListColumn;146       Bll.Common.Report.InitListBox(lstAvailableField, false, null, listColumn, "PageColumnName", "DataBaseColumnName");147       Bll.Common.Report.InitDropDownList(ddlAvailableFieldEnter, false, null, listColumn, "PageColumnName", "DataBaseColumnName");148       lstAvailableField.SelectedIndex = 0;149       ddlAvailableFieldEnter.SelectedIndex = 0;150       lstSelectedField.Items.Clear();151       ResetSearchCriteria();152       ControlValueEnter();153       ControlPreview();154     }155 156     private void SelectItem()157     {158       if (lstAvailableField.SelectedIndex != -1)159       {160         int currentIndex = lstAvailableField.SelectedIndex;161         ListItem li = new ListItem(lstAvailableField.SelectedItem.Text, lstAvailableField.SelectedItem.Value);162         lstSelectedField.Items.Add(li);163         lstAvailableField.Items.Remove(li);164 165         int count = lstAvailableField.Items.Count;166         if (count > 0)167           lstAvailableField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;168       }169     }170 171     private void UnSelectItem()172     {173       if (lstSelectedField.SelectedIndex != -1)174       {175         int currentIndex = lstSelectedField.SelectedIndex;176         ListItem li = new ListItem(lstSelectedField.SelectedItem.Text, lstSelectedField.SelectedItem.Value);177         lstAvailableField.Items.Add(li);178         lstSelectedField.Items.Remove(li);179 180         int count = lstSelectedField.Items.Count;181         if (count > 0)182           lstSelectedField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;183       }184     }185 186     private void SelectAllItem()187     {188       int count = lstAvailableField.Items.Count;189       if (count > 0)190       {191         foreach (ListItem li in lstAvailableField.Items)192           lstSelectedField.Items.Add(li);193         lstAvailableField.Items.Clear();194       }195     }196 197     private void UnSelectAllItem()198     {199       int count = lstSelectedField.Items.Count;200       if (count > 0)201       {202         foreach (ListItem li in lstSelectedField.Items)203           lstAvailableField.Items.Add(li);204         lstSelectedField.Items.Clear();205       }206     }207 208     private void AddCriteria()209     {210       bool isLike = ddlOperation.SelectedValue.IndexOf("like") != -1;211       bool isAddSingleQuotes = IsAddSingleQuotes();212       string val = txtValueEnter.Text;213       if (isLike)214       {215         val = string.Format("'{0}'", ddlOperation.SelectedValue.Replace("like", txtValueEnter.Text));216       }217       else if (isAddSingleQuotes)218       {219         val = string.Format("'{0}'", txtValueEnter.Text);220       }221 222       string criteriaVal = string.Format("{0}{1}{2}",223                       ddlAvailableFieldEnter.SelectedItem.Value,224                       isLike ? " like " : ddlOperation.SelectedValue,225                       val);226       string criteriaText = string.Format("{0}{1}{2}",227                       ddlAvailableFieldEnter.SelectedItem.Text,228                       isLike ? " like " : ddlOperation.SelectedValue,229                       val);230 231       if (txtSearchCriteriaVal.Text.IndexOf(criteriaVal) == -1)232       {233         if (isLike || isAddSingleQuotes || txtValueEnter.Text.Trim() != "")234         {235           criteriaVal = string.Format("{0} {1}", txtSearchCriteriaVal.Text != "" ? " " + joinStr : "", criteriaVal);236           txtSearchCriteriaVal.Text += criteriaVal;237 238           criteriaText = string.Format("{0} {1}", txtSearchCriteria.Text != "" ? " " + joinStr : "", criteriaText);239           txtSearchCriteria.Text += criteriaText;240         }241       }242     }243 244     private string FinallySql()245     {246       StringBuilder sb = new StringBuilder("select ");247       foreach (ListItem li in lstSelectedField.Items)248         sb.AppendFormat("{0},", li.Value);249       sb.Remove(sb.Length - 1, 1);250       string dataBaseTableName = ddlTable.SelectedValue;251       sb.AppendFormat(" from {0}", dataBaseTableName);252       string criteria = txtSearchCriteriaVal.Text;253       if (criteria != "")254         sb.AppendFormat(" where {0}", criteria);255       string holdingId = ddlHolder.SelectedValue;256       if (holdingId != "0")257       {258         sb.AppendFormat(" {0} Holding_Id={1}",259                 sb.ToString().IndexOf("where") != -1 ? "and" : "where",260                 holdingId);261       }262       if (Bll.Report.CustomReport.tableHaveUserId.IndexOf(dataBaseTableName) != -1)263       {264         sb.AppendFormat(" {0} User_Id={1}",265                 sb.ToString().IndexOf("where") != -1 ? "and" : "where",266                 CurrentUserId);267       }268       return sb.ToString();269     }270 271     private void ControlValueEnter()272     {273       txtValueEnter.Text = "";274       txtValueEnter.Attributes["onclick"] = "return true;";275       txtValueEnter.CssClass = "textBox";276       txtValueEnter.Attributes["onkeyup"] = "return true;";277       txtValueEnter.Attributes["onafterpaste"] = "return true;";278 279       ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;280       switch (ct)281       {282         case ColumnType.Number:283           string jsStr = "this.value=this.value.replace(/[^0-9|^\\-|^\\.]/g,\'\');";284           txtValueEnter.Attributes["onkeyup"] = jsStr;285           txtValueEnter.Attributes["onafterpaste"] = jsStr;286           break;287         case ColumnType.DateTime:288           txtValueEnter.Attributes["onclick"] = "WdatePicker({dateFmt:'MM-dd-yyyy'})";289           txtValueEnter.CssClass = "Wdate";290           break;291         default:292           break;293       }294     }295 296     private bool IsAddSingleQuotes()297     {298       ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;299       return ct != ColumnType.Number;300     }301 302     private void ResetSearchCriteria()303     {304       txtSearchCriteria.Text = "";305       txtSearchCriteriaVal.Text = "";306     }307 308     private void ControlPreview()309     {310       btnPreview.Enabled = lstSelectedField.Items.Count > 0;311     }312     #endregion313   }314 }

 

CustomReport.aspx

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomReport.aspx.cs" Inherits="IMSWeb.App.IS.Report.CustomReport" %> 2  3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 <html "http://www.w3.org/1999/xhtml"> 5 <head runat="server"> 6   <title></title> 7 </head> 8 <body> 9   <form id="form1" runat="server">10   <div>11     <fieldset>12       <legend class="segmentTitle">Custom Report List</legend>13       <div style="margin-left: 10px; margin-bottom: 20px">14         <asp:GridView runat="server" ID="gvCustomReportList" AllowPaging="True" PageSize="20"15           OnPageIndexChanging="gvCustomReportList_PageIndexChanging">16         </asp:GridView>17         <p />18         <asp:Button runat="server" Width="100px" ID="btnExportCSV" Text="Export Report" OnClick="btnExportCSV_Click" />&nbsp;&nbsp;19         <asp:Button runat="server" Width="100px" ID="btnPrint" Text="Print" />20       </div>21     </fieldset>22   </div>23   </form>24 </body>25 </html>

 

CustomReport.aspx.cs

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7  8 using System.Data; 9 using System.Text; 10  11 namespace IMSWeb.App.IS.Report 12 { 13   /// <summary> 14   /// add by Kenmu at 2013-05-07 15   /// </summary> 16   public partial class CustomReport : BasePage 17   { 18     #region Event 19     protected void Page_Load(object sender, EventArgs e) 20     { 21       if (!IsPostBack) 22         InitData(); 23     } 24  25     protected void gvCustomReportList_PageIndexChanging(object sender, GridViewPageEventArgs e) 26     { 27       gvCustomReportList.PageIndex = e.NewPageIndex; 28       InitData(); 29     } 30      31     protected void btnExportCSV_Click(object sender, EventArgs e) 32     { 33       try 34       { 35         string sql = Server.UrlDecode(Request["Sql"]); 36         DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql); 37         StringBuilder sbHeader = new StringBuilder(); 38         StringBuilder sbContent = new StringBuilder(); 39         DateTime tempDateTime = DateTime.MinValue; 40         string tempVal = ""; 41  42         for (int i = 0, len = dt.Rows.Count; i < len; i++) 43         { 44           for (int j = 0, len2 = dt.Columns.Count; j < len2; j++) 45           { 46             if (i == 0) 47             { 48               sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName); 49             } 50  51             tempVal = dt.Rows[i][j].ToString(); 52             if(DateTime.TryParse(tempVal,out tempDateTime)) 53               tempVal = tempDateTime.ToString("dd-MM-yyyy"); 54  55             sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal)); 56           } 57           sbContent.Remove(sbContent.Length - 1, 1); 58           sbContent.AppendLine(); 59         } 60         sbHeader.Remove(sbHeader.Length - 1, 1); 61         sbHeader.AppendLine(); 62  63         IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response, 64                              string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")), 65                              sbHeader.ToString() + sbContent.ToString()); 66       } 67       catch (Exception ex) 68       { 69         IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId); 70         this.ShowErrorMsg(ex); 71       } 72     } 73     #endregion 74  75     #region Method 76     private void InitData() 77     { 78       string sql = Server.UrlDecode(Request["Sql"]); 79       DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql); 80       int count = dt.Rows.Count; 81       if (count == 0) 82       { 83         this.RegisterJS("alert('Without relevant data!');window.opener=null;window.open('','_self');window.close();"); 84         return; 85       } 86       gvCustomReportList.DataSource = dt; 87       DynamicAddColumn(dt); 88       gvCustomReportList.DataBind(); 89     } 90  91     private void DynamicAddColumn(DataTable dt) 92     { 93       gvCustomReportList.Columns.Clear(); 94       for (int i = 0, len = dt.Columns.Count; i < len; i++) 95         AddColumn(dt.Columns[i].ColumnName, dt.Columns[i].DataType.ToString()); 96     } 97  98     private void AddColumn(string columnName, string type) 99     {100       BoundField bf = new BoundField();101       bf.DataField = columnName;102       bf.HeaderText = columnName;103       switch (type)104       {105         case "System.DateTime"://日期类型106           bf.DataFormatString = "{0:dd-MM-yyyy}";107           break;108         //case "System.String"://字符串类型109         //  break;110         //case "System.Boolean"://布尔型 111         //  break;112         //case "System.Int16"://整型113         //case "System.Int32":114         //case "System.Int64":115         //case "System.Byte":116         //  break;117         //case "System.Decimal"://浮点型118         //case "System.Double":119         //  break;120         //case "System.DBNull"://Null值处理121         //  break;122         default:123           break;124       }125       gvCustomReportList.Columns.Add(bf);126     }127     #endregion128   }129 }