三沣开发知识 购物 网址 游戏 小说 歌词 地图 快照 开发 股票 美女 新闻 笑话 | 汉字 软件 日历 阅读 下载 图书馆 编程 租车 短信 China
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
vbs/VBScript DOS/BAT hta htc python perl 游戏相关 VBA 远程脚本 ColdFusion ruby专题
autoit seraphzone PowerShell linux shell Lua Golang Erlang 其它教程 CSS/HTML/Xhtml
html5 CSS XML/XSLT Dreamweaver教程 经验交流 开发者乐园 Android开发资料
站长资讯 .NET新手 ASP.NET C# WinForm Silverlight WCF CLR WPF XNA VisualStudio ASP.NET-MVC .NET控件开发 EntityFramework WinRT-Metro Java C++ PHP Delphi Python Ruby C语言 Erlang Go Swift Scala R语言 Verilog 其它语言 架构设计 面向对象 设计模式 领域驱动 Html-Css JavaScript jQuery HTML5 SharePoint GIS技术 SAP OracleERP DynamicsCRM K2 BPM 信息安全 企业信息 Android开发 iOS开发 WindowsPhone WindowsMobile 其他手机 敏捷开发 项目管理 软件工程 SQLServer Oracle MySQL NoSQL 其它数据库 Windows7 WindowsServer Linux
  IT知识库 -> ASP.NET -> GridView动态添加列并判断绑定数据DataTable的列类型控制展示内容 -> 正文阅读
 

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

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_id
100     left join org_department od
101         on iihu.dept_id = od.dept_id
102     left join org_group og
103         on iihu.group_id = og.group_id
104     left join org_section os
105         on iihu.section_id = os.section_id
106     left join org_unit ou
107         on iihu.unit_id = ou.unit_id
108     left join is_item_category iica
109         on iig.item_cat_id = iica.item_cat_id
110     left join is_item_category iicaSub
111         on iig.item_sub_cat_id = iicaSub.item_cat_id
112     left join is_item_classification iicl
113         on iig.item_classic_id = iicl.item_classific_id
114     left join is_item_classification iiclSub
115         on iig.item_sub_classic_id = iiclSub.item_classific_id
116     inner join is_view_item_group ivig
117         on iig.item_group_id = ivig.item_group_id
118     inner join is_view_sheet ivs
119         on ivig.sheet_id = ivs.sheet_id
120     inner join is_view_column ivc
121         on ivs.sheet_id = ivc.sheet_id
122     inner join is_view_line ivl
123         on ivs.sheet_id = ivl.sheet_id
124 go
125 
126 alter view View_CustomReport_IndividualItemBasicInfo
127 as
128     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 ii
150     inner join is_item_group iig
151         on ii.item_group_id = iig.item_group_id
152 go
153 
154 alter view View_CustomReport_Location
155 as
156     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 ii
165     inner join is_item_group iig
166         on ii.item_group_id = iig.item_group_id
167     left join is_location il
168         on ii.loc_district = il.loc_id
169     left join is_location il2
170         on ii.loc_building = il2.loc_id
171     left join is_location il3
172         on ii.loc_floor = il3.loc_id
173     left join is_location il4
174         on ii.loc_room = il4.loc_id
175 go
176 
177 alter view View_CustomReport_PhysicalCheckInfo
178 as
179     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 icl
186     inner join is_check_list_detail icld
187         on icl.list_id = icld.list_id and icld.is_manual_check=1
188     inner join is_inventory_check iic
189         on icl.check_id = iic.check_id
190 go
191 
192 alter view View_CustomReport_Aggregate
193 as
194     select iig.holding_id as 'Holding_Id',
195            ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'
196     from is_item ii
197     inner join is_item_group iig
198         on ii.item_group_id = iig.item_group_id
199     inner join is_item_aggregate_map iiam
200         on ii.item_id = iiam.parent_item_id
201 go
202 
203 alter view View_CustomReport_PurchaseInfo
204 as
205     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 ii
221     inner join is_item_group iig
222         on ii.item_group_id = iig.item_group_id
223     inner join(
224         select item_id,
225                datediff(month,contract_start_date,contract_end_date) as 'contract_month'
226         from is_contract_item_map
227         where contract_id in(
228             select min(contract_id) from is_contract_item_map
229             group by item_id
230         )
231     ) as icim
232         on ii.item_id = icim.item_id
233     inner join(
234         select itdi.item_id,itr.voucher_type,itr.voucher_no
235         from is_tx_record itr 
236         inner join is_tx_detail_item itdi
237             on itr.tx_id = itdi.tx_id
238         where itdi.tx_id in(
239             select min(tx_id) from is_tx_detail_item
240             group by item_id
241         )
242     ) as itdi
243         on ii.item_id = itdi.item_id
244 go
245 
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)+1
273     
274   return substring(@str,@start,@location-@start)  
275 end  
276 go
277 alter view View_CustomReport_LoanInfo
278 as
279     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 ii
287     inner join is_item_group iig
288         on ii.item_group_id = iig.item_group_id
289     left join is_loan_record ilr 
290         on ii.loan_id = ilr.loan_id
291     left join is_staff_card isc 
292         on ilr.borrower_id = isc.staff_card_id
293 go
294 
295 alter view View_CustomReport_IssueInfo
296 as
297     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 ii
305     inner join is_item_group iig
306         on ii.item_group_id = iig.item_group_id
307     left join org_department od
308         on ii.issued_dept_id = od.dept_id
309     left join org_group og
310         on ii.issued_group_id = og.group_id
311     left join org_section os
312         on ii.issued_section_id = os.section_id
313     left join org_unit ou
314         on ii.issued_unit_id = ou.unit_id
315 go
316 
317 alter view View_CustomReport_EndUserInfo
318 as
319     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 ii
323     inner join is_item_group iig
324         on ii.item_group_id = iig.item_group_id
325     inner join is_end_user_record ieur
326         on ii.item_id = ieur.item_id
327 go
328 
329 alter view View_CustomReport_SurplusInfo
330 as
331     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 ii
337     inner join is_item_group iig
338         on ii.item_group_id = iig.item_group_id
339     inner join(
340         select item_id,[start_date],end_date
341         from is_surplus_item_post_out
342         where request_id in(
343             select max(request_id) from is_surplus_item_post_out
344             group by item_id
345         )
346     ) as isipo
347         on ii.item_id = isipo.item_id
348 go
349 
350 alter view View_CustomReport_DisposalInfo
351 as
352     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 ii
364     inner join is_item_group iig
365         on ii.item_group_id = iig.item_group_id
366     inner join is_disposal_record idr
367         on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id
368     left join is_request ir
369         on idr.request_id = ir.request_id
370     left join is_tx_record itr
371         on ir.out_tx_id = itr.tx_id
372 go
373 
374 alter view View_CustomReport_RepairInfo
375 as
376     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 ii
380     inner join is_item_group iig
381         on ii.item_group_id = iig.item_group_id
382 go
383 
384 alter view View_CustomReport_ReplaceInfo
385 as
386     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 ii
392     inner join is_item_group iig
393         on ii.item_group_id = iig.item_group_id
394     inner join(
395         select itdi.item_id,itr.voucher_no
396         from is_tx_record itr
397         inner join is_tx_detail_item itdi
398         on itr.tx_id = itdi.tx_id
399         where itdi.tx_id in(
400             select max(tx_id) from is_tx_detail_item
401             group by item_id
402         )
403     ) as itr
404         on ii.item_id = itr.item_id
405 go
406 
407 alter view View_CustomReport_DelegateMaintUserInfo
408 as
409     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 ii
418     inner join is_item_group iig
419         on ii.item_group_id = iig.item_group_id
420     left join org_department od
421         on ii.maint_dept_id = od.dept_id
422     left join org_group og
423         on ii.maint_group_id = og.group_id
424     left join org_section os
425         on ii.maint_section_id = os.section_id
426     left join org_unit ou
427         on ii.maint_unit_id = ou.unit_id
428 go
429 
430 alter view View_CustomReport_MaintenanceBasic
431 as
432     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 ii
440     inner join is_item_group iig
441         on ii.item_group_id = iig.item_group_id
442     inner join is_maint_grouping img
443         on iig.maint_grouping = img.[grouping_id]
444     left join is_contract_item_map icim
445         on ii.item_id = icim.item_id
446     left join is_vote_maintenance ivm
447         on icim.vote = ivm.vote_id
448 go
449 
450 alter view View_CustomReport_CurrentMaintenanceInfo
451 as
452     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 ii
461     inner join is_item_group iig
462         on ii.item_group_id = iig.item_group_id
463     left join is_contract_item_map icim
464         on ii.item_id = icim.item_id
465     left join is_maintenance_contract imc
466         on icim.contract_id = imc.contract_id
467     left join is_vender iv
468         on icim.vendor_id = iv.vendor_id
469     left join is_maint_level iml
470         on icim.level_of_maint = iml.level_code
471 go
472 
473 
474 alter view View_CustomReport_ContractHeader
475 as
476     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 ii
487     inner join is_item_group iig
488         on ii.item_group_id = iig.item_group_id
489     left join is_contract_item_map icim
490         on ii.item_id = icim.item_id
491     left join is_maintenance_contract imc
492         on icim.contract_id = imc.contract_id
493 go
494 
495 alter view View_CustomReport_ItemsVendorInfo
496 as
497     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 iv
511     inner join is_contract_vendor_map icvm
512         on iv.vendor_id = icvm.vendor_id
513     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_payment
525     ) as icvp
526         on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id
527     inner join is_maintenance_contract imc
528         on icvm.contract_id = imc.contract_id
529 go
530 
531 alter view View_CustomReport_ItemsMaintenanceBasicInfo
532 as
533     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 ii
552     inner join is_item_group iig
553         on ii.item_group_id = iig.item_group_id
554     inner join is_maint_grouping img
555         on iig.maint_grouping = img.[grouping_id]
556     left join is_contract_item_map icim
557         on ii.item_id = icim.item_id
558     left join is_vote_maintenance ivm
559         on icim.vote = ivm.vote_id
560     left join is_maintenance_contract imc
561         on icim.contract_id = imc.contract_id
562     left join is_maint_level iml
563         on icim.level_of_maint = iml.level_code
564 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 Item
110                     #region Basic Info
111                     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 = listColumn
132                     });
133                     #endregion
134 
135                     #region GF272 Sheet Info
136                     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 = listColumn
147                     });
148                     #endregion
149                     #endregion
150                     break;
151                 case 1:
152                     #region Individual Item
153                     #region Line Item Information
154                     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 = listColumn
177                     });
178                     #endregion
179 
180                     #region Individual Item Basic Info
181                     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 = listColumn
196                     });
197                     #endregion
198 
199                     #region Location
200                     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 = listColumn
212                     });
213                     #endregion
214 
215                     #region Physical Check Info
216                     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 = listColumn
225                     });
226                     #endregion
227 
228                     #region Aggregate
229                     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 = listColumn
236                     });
237                     #endregion
238 
239                     #region Purchase Info
240                     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 = listColumn
260                     });
261                     #endregion
262 
263                     #region Loan Info
264                     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 = listColumn
275                     });
276                     #endregion
277 
278                     #region Issue Info
279                     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 = listColumn
290                     });
291                     #endregion
292 
293                     #region End-user info
294                     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 = listColumn
302                     });
303                     #endregion
304 
305                     #region Surplus Info
306                     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 = listColumn
315                     });
316                     #endregion
317 
318                     #region Disposal Info
319                     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 = listColumn
332                     });
333                     #endregion
334 
335                     #region Repair Info
336                     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 = listColumn
343                     });
344                     #endregion
345 
346                     #region Replace Info
347                     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 = listColumn
356                     });
357                     #endregion
358 
359                     #region Delegate Maint User Info
360                     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 = listColumn
371                     });
372                     #endregion
373 
374                     #region Maintenance Basic
375                     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 = listColumn
386                     });
387                     #endregion
388 
389                     #region Current Maintenance Info
390                     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 = listColumn
403                     });
404                     #endregion
405                     #endregion
406                     break;
407                 case 2:
408                     #region Maintenance Contract
409                     #region Contract Header
410                     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 = listColumn
424                     });
425                     #endregion
426 
427                     #region Item’s Vendor Info
428                     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 = listColumn
445                     });
446                     #endregion
447 
448                     #region Item’s Maintenance Basic Info
449                     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 = listColumn
470                     });
471                     #endregion
472                     #endregion
473                     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 xmlns="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 Criteria
121                     </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             else
118             {
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 listTable
138                                         where table.DataBaseTableName == ddlTable.SelectedValue
139                                         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         #endregion
313     }
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 xmlns="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         #endregion
128     }
129 }

  ASP.NET 最新文章
IIS服务器的请求流程
通过Web Service实现IP地址查询功能
VS 远程调试阿里云上的web站点,Remote Deb
sqlserver的四种分页方式
WebService服务(转)
C#去除字符串中的反斜杠
读取Excel文件内容在Web上显示
POST 方式上传图片
在ASP.NET Core Web API中为RESTful服务增加
C#实现Web文件上传的两种方法
上一篇文章      下一篇文章      查看所有文章
加:2015-06-01 19:27:57  更:2017-05-14 07:27:40 
 
技术频道: 站长资讯 .NET新手区 ASP.NET C# WinForm Silverlight WCF CLR WPF XNA Visual Studio ASP.NET MVC .NET控件开发 Entity Framework WinRT/Metro Java C++ PHP Delphi Python Ruby C语言 Erlang Go Swift Scala R语言 Verilog 其它语言 架构设计 面向对象 设计模式 领域驱动设计 Html/Css JavaScript jQuery HTML5 SharePoint GIS技术 SAP Oracle ERP Dynamics CRM K2 BPM 信息安全 企业信息化其他 Android开发 iOS开发 Windows Phone Windows Mobile 其他手机开发 敏捷开发 项目与团队管理 软件工程其他 SQL Server Oracle MySQL NoSQL 其它数据库 Windows 7 Windows Server Linux
脚本语言: vbs/VBScript DOS/BAT hta htc python perl 游戏相关 VBA 远程脚本 ColdFusion ruby专题 autoit seraphzone PowerShell linux shell Lua Golang Erlang 其它教程
网站开发: CSS/HTML/Xhtml html5 CSS XML/XSLT Dreamweaver教程 经验交流 开发者乐园 Android开发资料
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture 股票 租车
生肖星座 三丰软件 视频 开发 短信 中国文化 网文精选 搜图网 美图 阅读网 多播 租车 短信 看图 日历 万年历 2018年2日历
2018-2-19 16:13:38
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT知识库