此篇随笔是2013年根据项目需求开发记录的,不一定符合大众口味,只需了解开发思路,毕竟解决方案多种多样。
下面简单说说需求点吧:
(1)通过下拉列表可以选择一个DataSet(数据集),一个DataSet存在可以互相关联的多个DataTable(数据表格),DataTable数据来源于数据库视图;SQL语句关联比较复杂
(2)一个DataTable(数据表格)存在多个可供选择查询显示的Column(列),支持动态组合
(3)Column(列)同时支持作为查询条件进行并运算
先看效果吧,免得待会看到太多代码失去看下去的兴趣了:
(1)数据库方面
1 USE [IMSDB] 2 GO 3 4 drop view View_CustomReport_ItemCategory 5 go 6 drop view View_CustomReport_ItemClassification 7 go 8 9 ---- the view total is 21 10 alter view View_CustomReport_BasicInfo 11 as 12 select iig.holding_id as 'Holding_Id', 13 row_number() over(order by iig.item_group_id) as 'Internal_ID', 14 od.dept_name as 'Holder_Department', 15 og.group_name as 'Holder_Group', 16 os.section_name as 'Holder_Section', 17 ou.unit_name as 'Holder_Unit', 18 iig.brand as 'Brand', 19 iig.model as 'Model', 20 iica.name_en as 'Category', 21 iicaSub.name_en as 'Sub____category', 22 iicl.name_en as 'Classification', 23 iiclSub.name_en as 'Sub____classification', 24 iig.item_desc as 'GF272_Description', 25 (case when iig.inventory_flg=1 then 'inventory' 26 when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 27 (select ivc.balance 28 from is_view_item_group ivig 29 inner join is_view_column ivc 30 on ivig.sheet_id = ivc.sheet_id 31 and ivig.line_id = ivc.line_no 32 and ivig.column_id = ivc.column_no 33 where ivig.item_group_id = iig.item_group_id) as 'Current_Qty_Balance', 34 iig.unit_of_qty as 'Unit_of_Qty' 35 from is_item_group iig 36 inner join is_inventory_holding_unit iihu 37 on iig.holding_id = iihu.holding_id 38 left join org_department od 39 on iihu.dept_id = od.dept_id 40 left join org_group og 41 on iihu.group_id = og.group_id 42 left join org_section os 43 on iihu.section_id = os.section_id 44 left join org_unit ou 45 on iihu.unit_id = ou.unit_id 46 left join is_item_category iica 47 on iig.item_cat_id = iica.item_cat_id 48 left join is_item_category iicaSub 49 on iig.item_sub_cat_id = iicaSub.item_cat_id 50 left join is_item_classification iicl 51 on iig.item_classic_id = iicl.item_classific_id 52 left join is_item_classification iiclSub 53 on iig.item_sub_classic_id = iiclSub.item_classific_id 54 go 55 56 alter view View_CustomReport_GF272SheetInfo 57 as 58 select iig.holding_id as 'Holding_Id', 59 ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 60 ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 61 ivc.line_no as 'Last_GF272_Valid_Line_No000', 62 ivc.column_no as 'Last_GF272_Valid_Column_No000', 63 ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 64 from is_item_group iig 65 inner join is_view_item_group ivig 66 on iig.item_group_id = ivig.item_group_id 67 inner join is_view_sheet ivs 68 on ivig.sheet_id = ivs.sheet_id 69 inner join is_view_column ivc 70 on ivs.sheet_id = ivc.sheet_id 71 inner join is_view_line ivl 72 on ivs.sheet_id = ivl.sheet_id 73 go 74 75 76 alter view View_CustomReport_LineItemInformation 77 as 78 select iig.holding_id as 'Holding_Id', 79 od.dept_name as 'Holder_Department', 80 og.group_name as 'Holder_Group', 81 os.section_name as 'Holder_Section', 82 ou.unit_name as 'Holder_Unit', 83 iig.brand as 'Brand', 84 iig.model as 'Model', 85 iica.name_en as 'Category', 86 iicaSub.name_en as 'Sub____category', 87 iicl.name_en as 'Classification', 88 iiclSub.name_en as 'Sub____classification', 89 iig.item_desc as 'GF272_Description', 90 (case when iig.inventory_flg=1 then 'inventory' 91 when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 92 ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 93 ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 94 ivc.line_no as 'Last_GF272_Valid_Line_No000', 95 ivc.column_no as 'Last_GF272_Valid_Column_No000', 96 ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 97 from is_item_group iig 98 inner join is_inventory_holding_unit iihu 99 on iig.holding_id = iihu.holding_id100 left join org_department od101 on iihu.dept_id = od.dept_id102 left join org_group og103 on iihu.group_id = og.group_id104 left join org_section os105 on iihu.section_id = os.section_id106 left join org_unit ou107 on iihu.unit_id = ou.unit_id108 left join is_item_category iica109 on iig.item_cat_id = iica.item_cat_id110 left join is_item_category iicaSub111 on iig.item_sub_cat_id = iicaSub.item_cat_id112 left join is_item_classification iicl113 on iig.item_classic_id = iicl.item_classific_id114 left join is_item_classification iiclSub115 on iig.item_sub_classic_id = iiclSub.item_classific_id116 inner join is_view_item_group ivig117 on iig.item_group_id = ivig.item_group_id118 inner join is_view_sheet ivs119 on ivig.sheet_id = ivs.sheet_id120 inner join is_view_column ivc121 on ivs.sheet_id = ivc.sheet_id122 inner join is_view_line ivl123 on ivs.sheet_id = ivl.sheet_id124 go125 126 alter view View_CustomReport_IndividualItemBasicInfo127 as128 select iig.holding_id as 'Holding_Id',129 ii.serial_no as 'Serial_No000',130 ii.barcode_no as 'IMS_Barcode_No000',131 ii.self_assign_id as 'Self_Assign_ID',132 ii.detailed_desc as 'Detailed_Description',133 (case when ii.item_status='RJ' then 'Reject'134 when ii.item_status='TP' then 'Temp'135 when ii.item_status='DR' then 'Draft'136 when ii.item_status='N' then 'Normal'137 when ii.item_status='UN' then 'Unserviceable'138 when ii.item_status='TI' then 'Transfer'139 when ii.item_status='TO' then 'TransferOutOfICAC'140 when ii.item_status='D' then 'Disposed'141 when ii.item_status='WO' then 'WriteOff'142 when ii.item_status='SI' then 'SurplusItem'143 when ii.item_status='R' then 'Repair'144 when ii.item_status='TL' then 'TransferLedger' end) as 'Item_Status',145 ii.remarks as 'Remarks',146 ii.remarks2 as 'Remarks2',147 ii.remarks3 as 'Remarks3',148 ii.remarks4 as 'Remarks4'149 from is_item ii150 inner join is_item_group iig151 on ii.item_group_id = iig.item_group_id152 go153 154 alter view View_CustomReport_Location155 as156 select iig.holding_id as 'Holding_Id',157 (case when ii.head_quarter_flg=1 then 'Yes'158 when ii.head_quarter_flg=0 then 'No' end) as 'Headquarter_Flag',159 il.loc_name as 'Location_District',160 il2.loc_name as 'Location_Building',161 il3.loc_name as 'Location_Floor',162 il4.loc_name as 'Location_Room',163 ii.loc_area as 'Location_Area'164 from is_item ii165 inner join is_item_group iig166 on ii.item_group_id = iig.item_group_id167 left join is_location il168 on ii.loc_district = il.loc_id169 left join is_location il2170 on ii.loc_building = il2.loc_id171 left join is_location il3172 on ii.loc_floor = il3.loc_id173 left join is_location il4174 on ii.loc_room = il4.loc_id175 go176 177 alter view View_CustomReport_PhysicalCheckInfo178 as179 select iic.holding_id as 'Holding_Id',180 icld.update_datetime as 'Last_Physical_Checked_Date',181 icld.check_by as 'Last_Physical_Checked_By',182 (case when iic.check_status='IP' then 'InProgress'183 when iic.check_status='CP' then 'Completed'184 when iic.check_status='SC' then 'SoConfirm' end) as 'Last_Check_Status'185 from is_check_list icl186 inner join is_check_list_detail icld187 on icl.list_id = icld.list_id and icld.is_manual_check=1188 inner join is_inventory_check iic189 on icl.check_id = iic.check_id190 go191 192 alter view View_CustomReport_Aggregate193 as194 select iig.holding_id as 'Holding_Id',195 ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'196 from is_item ii197 inner join is_item_group iig198 on ii.item_group_id = iig.item_group_id199 inner join is_item_aggregate_map iiam200 on ii.item_id = iiam.parent_item_id201 go202 203 alter view View_CustomReport_PurchaseInfo204 as205 select iig.holding_id as 'Holding_Id',206 ii.pr_ref_no as 'Purchase_PR_Ref_No000',207 ii.quot_ref_no as 'Purchase_ICAC_Quotation_Ref_No000',208 ii.purchase_mode as 'Purchase_Mode',209 ii.po_no as 'Purchase_PO_No000___Ref',210 ii.invoice_no as 'Purchase_Invoice_No000',211 ii.date_of_order as 'Purchase_Date_of_Order',212 ii.date_of_receipt as 'Purchase_Date_of_Receipt',213 ii.date_of_acceptance as 'Purchase_Date_of_Acceptance',214 icim.contract_month as 'Free_Warranty_Period',215 ii.holder_file_ref as 'Holder_File_Reference',216 ii.contact_person as 'Contact_Person',217 ii.contact_person_post as 'Contact_Person_Position',218 itdi.voucher_type as 'Purchase_Related_Voucher_Type',219 itdi.voucher_no as 'Purchase_Related_Voucher_No000'220 from is_item ii221 inner join is_item_group iig222 on ii.item_group_id = iig.item_group_id223 inner join(224 select item_id,225 datediff(month,contract_start_date,contract_end_date) as 'contract_month'226 from is_contract_item_map227 where contract_id in(228 select min(contract_id) from is_contract_item_map229 group by item_id230 )231 ) as icim232 on ii.item_id = icim.item_id233 inner join(234 select itdi.item_id,itr.voucher_type,itr.voucher_no235 from is_tx_record itr 236 inner join is_tx_detail_item itdi237 on itr.tx_id = itdi.tx_id238 where itdi.tx_id in(239 select min(tx_id) from is_tx_detail_item240 group by item_id241 )242 ) as itdi243 on ii.item_id = itdi.item_id244 go245 246 create function Func_StrArrayStrOfIndex 247 ( 248 @str varchar(1024), --要分割的字符串 249 @split varchar(10), --分隔符号 250 @index int --取第几个元素 251 ) 252 returns varchar(1024) 253 as 254 begin 255 declare @location int 256 declare @start int 257 declare @next int 258 declare @seed int 259 260 set @str=ltrim(rtrim(@str)) 261 set @start=1 262 set @next=1 263 set @seed=len(@split) 264 265 set @location=charindex(@split,@str) 266 while @location<>0 and @index>@next 267 begin 268 set @start=@location+@seed 269 set @location=charindex(@split,@str,@start) 270 set @next=@next+1 271 end 272 if @location =0 select @location =len(@str)+1273 274 return substring(@str,@start,@location-@start) 275 end 276 go277 alter view View_CustomReport_LoanInfo278 as279 select iig.holding_id as 'Holding_Id',280 (case when ii.loan_flg=1 then 'On Loan'281 when ii.loan_flg=0 then 'In Hand' end) as 'Current_Loan_Status',282 isc.english_name as 'Staff_Name_of_Borrower',283 dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',1) as 'Borrower111s_Department',284 dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',2) as 'Borrower111s_Group',285 dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',3) as 'Borrower111s_Section'286 from is_item ii287 inner join is_item_group iig288 on ii.item_group_id = iig.item_group_id289 left join is_loan_record ilr 290 on ii.loan_id = ilr.loan_id291 left join is_staff_card isc 292 on ilr.borrower_id = isc.staff_card_id293 go294 295 alter view View_CustomReport_IssueInfo296 as297 select iig.holding_id as 'Holding_Id',298 (case when ii.issue_flg=1 then 'Issued'299 when ii.issue_flg=0 then 'Not Issued' end) as 'Current_Issue_Status',300 od.dept_name as 'Issue_Dept',301 og.group_name as 'Issue_Group',302 os.section_name as 'Issue_Section',303 ou.unit_name as 'Issue_Unit'304 from is_item ii305 inner join is_item_group iig306 on ii.item_group_id = iig.item_group_id307 left join org_department od308 on ii.issued_dept_id = od.dept_id309 left join org_group og310 on ii.issued_group_id = og.group_id311 left join org_section os312 on ii.issued_section_id = os.section_id313 left join org_unit ou314 on ii.issued_unit_id = ou.unit_id315 go316 317 alter view View_CustomReport_EndUserInfo318 as319 select iig.holding_id as 'Holding_Id',320 ieur.end_user as 'Current_Item_End____user',321 convert(varchar(10),ieur.assign_date,105)+' to '+convert(varchar(10),ieur.return_date,105) as 'Usage_Date'322 from is_item ii323 inner join is_item_group iig324 on ii.item_group_id = iig.item_group_id325 inner join is_end_user_record ieur326 on ii.item_id = ieur.item_id327 go328 329 alter view View_CustomReport_SurplusInfo330 as331 select iig.holding_id as 'Holding_Id',332 (case when ii.item_status='SI' then 'Yes'333 else 'No' end) as 'Surplus_Status',334 isipo.[start_date] as 'Surplus_Post_Out_Date_From',335 isipo.end_date as 'Surplus_Post_Out_Date_To'336 from is_item ii337 inner join is_item_group iig338 on ii.item_group_id = iig.item_group_id339 inner join(340 select item_id,[start_date],end_date341 from is_surplus_item_post_out342 where request_id in(343 select max(request_id) from is_surplus_item_post_out344 group by item_id345 )346 ) as isipo347 on ii.item_id = isipo.item_id348 go349 350 alter view View_CustomReport_DisposalInfo351 as352 select iig.holding_id as 'Holding_Id',353 ii.anticipated_date_disposal as 'Anticipated_Disposal_Date',354 (case when idr.disposal_status='R' or idr.disposal_status='V' then 'Not Applicable'355 when idr.disposal_status='D' or idr.disposal_status='W' then 'Requesting'356 when idr.disposal_status='T' then 'Approved by SO'357 when idr.disposal_status='P' then 'Physically Disposed' end) as 'Disposal_Status',358 idr.job_no as 'Dispose_Job_No000',359 idr.dumping_date as 'Date_of_Disposal',360 idr.item_condition as 'Condition_of_Item_in_Disposal',361 idr.disposal_method as 'Disposal_Method',362 itr.voucher_no as 'Disposal_Related_Voucher_No000'363 from is_item ii364 inner join is_item_group iig365 on ii.item_group_id = iig.item_group_id366 inner join is_disposal_record idr367 on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id368 left join is_request ir369 on idr.request_id = ir.request_id370 left join is_tx_record itr371 on ir.out_tx_id = itr.tx_id372 go373 374 alter view View_CustomReport_RepairInfo375 as376 select iig.holding_id as 'Holding_Id',377 (case when ii.item_status='R' then 'Yes'378 else 'No' end) as 'On_Repair_Status'379 from is_item ii380 inner join is_item_group iig381 on ii.item_group_id = iig.item_group_id382 go383 384 alter view View_CustomReport_ReplaceInfo385 as386 select iig.holding_id as 'Holding_Id',387 (case when ii.replace_flg=1 then 'Yes'388 else 'No' end) as 'Replaced_Status',389 ii.barcode_no as 'IMS_Barcode_No_of_Replacing_Item',390 itr.voucher_no as 'Voucher_No_for_Replaced_Item'391 from is_item ii392 inner join is_item_group iig393 on ii.item_group_id = iig.item_group_id394 inner join(395 select itdi.item_id,itr.voucher_no396 from is_tx_record itr397 inner join is_tx_detail_item itdi398 on itr.tx_id = itdi.tx_id399 where itdi.tx_id in(400 select max(tx_id) from is_tx_detail_item401 group by item_id402 )403 ) as itr404 on ii.item_id = itr.item_id405 go406 407 alter view View_CustomReport_DelegateMaintUserInfo408 as409 select iig.holding_id as 'Holding_Id',410 (case when ii.maint_dept_id is null then 'No'411 when ii.maint_dept_id=0 then 'No'412 else 'Yes' end) as 'Delegated_to_others_for_Maintenance',413 od.dept_name as 'Maintenance_Dept',414 og.group_name as 'Maintenance_Group',415 os.section_name as 'Maintenance_Section',416 ou.unit_name as 'Maintenance_Unit'417 from is_item ii418 inner join is_item_group iig419 on ii.item_group_id = iig.item_group_id420 left join org_department od421 on ii.maint_dept_id = od.dept_id422 left join org_group og423 on ii.maint_group_id = og.group_id424 left join org_section os425 on ii.maint_section_id = os.section_id426 left join org_unit ou427 on ii.maint_unit_id = ou.unit_id428 go429 430 alter view View_CustomReport_MaintenanceBasic431 as432 select iig.holding_id as 'Holding_Id',433 ivm.vote_name as 'Maintenance_Vote',434 ii.remarks_maint as 'Remarks_for_Maintenance',435 img.grouping_name as 'Maint000_Grouping',436 iig.gld_contract_no as 'GLD_Contract_No000',437 (case when iig.further_maint_flag=1 then 'Yes'438 else 'No' end) as 'Needed_to_be_Maintenance_Afterwards'439 from is_item ii440 inner join is_item_group iig441 on ii.item_group_id = iig.item_group_id442 inner join is_maint_grouping img443 on iig.maint_grouping = img.[grouping_id]444 left join is_contract_item_map icim445 on ii.item_id = icim.item_id446 left join is_vote_maintenance ivm447 on icim.vote = ivm.vote_id448 go449 450 alter view View_CustomReport_CurrentMaintenanceInfo451 as452 select iig.holding_id as 'Holding_Id',453 imc.ref_contract_id as 'Current_Maintenance_Contract_User_Reference_No000',454 iv.vendor_name as 'Current_Maintenance_Vendor',455 ii.annual_maint_cost as 'Current_Maintenance_Unit_Cost',456 icim.contract_start_date as 'Current_Maintenance_Coverage_Start_Date',457 icim.contract_end_date as 'Current_Maintenance_Coverage_End_Date',458 iml.level_desc as 'Current_Maintenance_Level',459 imc.bu_date as 'Next_Maintenance_BU_Date'460 from is_item ii461 inner join is_item_group iig462 on ii.item_group_id = iig.item_group_id463 left join is_contract_item_map icim464 on ii.item_id = icim.item_id465 left join is_maintenance_contract imc466 on icim.contract_id = imc.contract_id467 left join is_vender iv468 on icim.vendor_id = iv.vendor_id469 left join is_maint_level iml470 on icim.level_of_maint = iml.level_code471 go472 473 474 alter view View_CustomReport_ContractHeader475 as476 select iig.holding_id as 'Holding_Id',477 imc.ref_contract_id as 'Contract_User_Reference_No000',478 imc.contract_name as 'Contract_Name',479 (case when imc.maint_pr_no='null' then ''480 else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',481 icim.contract_start_date as 'Contract_Start_Date',482 icim.contract_end_date as 'Contract_End_Date',483 imc.contact_person as 'Contact_Person',484 imc.contact_person_post as 'Contact_Post',485 imc.bu_date as 'BU_Date'486 from is_item ii487 inner join is_item_group iig488 on ii.item_group_id = iig.item_group_id489 left join is_contract_item_map icim490 on ii.item_id = icim.item_id491 left join is_maintenance_contract imc492 on icim.contract_id = imc.contract_id493 go494 495 alter view View_CustomReport_ItemsVendorInfo496 as497 select imc.holding_id as 'Holding_Id',498 iv.vendor_name as 'Maint_Vendor',499 icvm.contact_person as 'Maint_Vendor_Contact_Person',500 icvm.contact_person_post as 'Maint_Vendor_Contact_Person_Position',501 icvm.quotation_no as 'Maint_Vendor_Quotation_No000',502 icvp.maint_op_no as 'Maint_Po_No000__Ref',503 icvp.group_num as 'Payment_Schedule',504 icvp.actual_payment_date as 'Payment_Date',505 icvp.paid_amount_hkd as 'Paid_Amount',506 (case when icvp.payment_status='1' then 'Paid'507 when icvp.payment_status='0' then 'Not Paid' end) as 'Payment_Status',508 icvp.invoice_no as 'Invoice_No000',509 icvp.file_ref_no as 'File_Ref_No000'510 from is_vender iv511 inner join is_contract_vendor_map icvm512 on iv.vendor_id = icvm.vendor_id513 left join (514 select contract_id,515 vendor_id,516 maint_op_no,517 actual_payment_date,518 paid_amount_hkd,519 payment_status,520 invoice_no,521 file_ref_no,522 row_number() over(partition by contract_id,vendor_id 523 order by actual_payment_date) as 'group_num'524 from is_contract_vendor_payment525 ) as icvp526 on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id527 inner join is_maintenance_contract imc528 on icvm.contract_id = imc.contract_id529 go530 531 alter view View_CustomReport_ItemsMaintenanceBasicInfo532 as533 select iig.holding_id as 'Holding_Id',534 ii.serial_no as 'Item_Serial_No000',535 ii.barcode_no as 'IMS_Item_Barcode_No000',536 iig.maint_grouping as 'Maintenance_Grouping',537 ivm.vote_name as 'Maintenance_Vote',538 ii.remarks_maint as 'Remarks_for_Maintenance',539 img.grouping_name as 'Maint000_Grouping',540 iig.gld_contract_no as 'GLD_Contract_No000',541 (case when iig.further_maint_flag=1 then 'Yes'542 else 'No' end) as 'Needed_to_be_Maintenance_Afterwards',543 (case when imc.maint_pr_no='null' then ''544 else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',545 icim.cost_total_hkd as 'Maintenance_Unit_Cost',546 icim.paid_total_price_hkd as 'Maintenance_Unit_Actual_Cost',547 icim.contract_start_date as 'Maintenance_Coverage_Start_Date',548 icim.contract_end_date as 'Maintenance_Coverage_End_Date',549 iml.level_desc as 'Maintenance_Level',550 icim.remarks as 'Item_Remarks_in_this_Contract'551 from is_item ii552 inner join is_item_group iig553 on ii.item_group_id = iig.item_group_id554 inner join is_maint_grouping img555 on iig.maint_grouping = img.[grouping_id]556 left join is_contract_item_map icim557 on ii.item_id = icim.item_id558 left join is_vote_maintenance ivm559 on icim.vote = ivm.vote_id560 left join is_maintenance_contract imc561 on icim.contract_id = imc.contract_id562 left join is_maint_level iml563 on icim.level_of_maint = iml.level_code564 go
(2)页面处理方面
CustomReport.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Web; 6 7 using System.Data; 8 using DAL; 9 10 namespace Bll.Report 11 { 12 public enum ColumnType 13 { 14 Number = 0, 15 String = 1, 16 DateTime = 2 17 } 18 19 public class CustomReportColumn 20 { 21 #region Property 22 private string _PageColumnName; 23 public string PageColumnName 24 { 25 get { return _PageColumnName; } 26 set { _PageColumnName = value; } 27 } 28 29 private string _DataBaseColumnName; 30 public string DataBaseColumnName 31 { 32 get { return _DataBaseColumnName; } 33 set { _DataBaseColumnName = value; } 34 } 35 36 private ColumnType _ColumnType; 37 public ColumnType ColumnType 38 { 39 get { return _ColumnType; } 40 set { _ColumnType = value; } 41 } 42 #endregion 43 } 44 45 public class CustomReportTable 46 { 47 #region Property 48 private string _PageTableName; 49 public string PageTableName 50 { 51 get { return _PageTableName; } 52 set { _PageTableName = value; } 53 } 54 55 private string _DataBaseTableName; 56 public string DataBaseTableName 57 { 58 get { return _DataBaseTableName; } 59 set { _DataBaseTableName = value; } 60 } 61 62 private List<CustomReportColumn> _ListColumn; 63 public List<CustomReportColumn> ListColumn 64 { 65 get { return _ListColumn; } 66 set { _ListColumn = value; } 67 } 68 #endregion 69 } 70 71 public class CustomReport 72 { 73 public static string[] tableSetNameArr = new string[] { "Latest Information of Line Item", "Individual Item", "Maintenance Contract" }; 74 public static string tableHaveUserId = ""; 75 76 public static Dictionary<string, List<CustomReportTable>> TableSet(int cacheMinutes) 77 { 78 Dictionary<string, List<CustomReportTable>> tableSet = new Dictionary<string, List<CustomReportTable>>(); 79 for (int i = 0, len = tableSetNameArr.Length; i < len; i++) 80 tableSet.Add(tableSetNameArr[i], ListTable(i, 0)); 81 return tableSet; 82 } 83 84 public static List<CustomReportTable> ListTable(int index, int cacheMinutes) 85 { 86 string cacheKey = string.Format("CustomReport_ListTable_{0}", index); 87 List<CustomReportTable> listTable = HttpContext.Current.Cache[cacheKey] as List<CustomReportTable>; 88 if (listTable == null) 89 { 90 listTable = new List<CustomReportTable>(); 91 InitDataForTable(index, ref listTable); 92 93 if (listTable.Count > 0 && cacheMinutes > 0) 94 HttpContext.Current.Cache.Insert(cacheKey, 95 listTable, 96 null, 97 DateTime.Now.AddMinutes(cacheMinutes), 98 System.Web.Caching.Cache.NoSlidingExpiration); 99 }100 return listTable;101 }102 103 private static void InitDataForTable(int index, ref List<CustomReportTable> listTable)104 {105 List<CustomReportColumn> listColumn;106 switch (index)107 {108 case 0:109 #region Latest Information of Line Item110 #region Basic Info111 listColumn = new List<CustomReportColumn>();112 listColumn.Add(new CustomReportColumn() { PageColumnName = "Internal ID", DataBaseColumnName = "Internal_ID", ColumnType = ColumnType.Number });113 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });114 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });115 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });116 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });117 listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });118 listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });119 listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });120 listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });121 listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });122 listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });123 listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });124 listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });125 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Qty Balance", DataBaseColumnName = "Current_Qty_Balance", ColumnType = ColumnType.Number });126 listColumn.Add(new CustomReportColumn() { PageColumnName = "Unit of Qty", DataBaseColumnName = "Unit_of_Qty", ColumnType = ColumnType.Number });127 listTable.Add(new CustomReportTable()128 {129 PageTableName = "Basic Info",130 DataBaseTableName = "View_CustomReport_BasicInfo",131 ListColumn = listColumn132 });133 #endregion134 135 #region GF272 Sheet Info136 listColumn = new List<CustomReportColumn>();137 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });138 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });139 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });140 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });141 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });142 listTable.Add(new CustomReportTable()143 {144 PageTableName = "GF272 Sheet Info",145 DataBaseTableName = "View_CustomReport_GF272SheetInfo",146 ListColumn = listColumn147 });148 #endregion149 #endregion150 break;151 case 1:152 #region Individual Item153 #region Line Item Information154 listColumn = new List<CustomReportColumn>();155 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });156 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });157 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });158 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });159 listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });160 listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });161 listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });162 listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });163 listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });164 listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });165 listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });166 listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });167 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });168 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });169 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });170 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });171 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });172 listTable.Add(new CustomReportTable()173 {174 PageTableName = "Line Item Information",175 DataBaseTableName = "View_CustomReport_LineItemInformation",176 ListColumn = listColumn177 });178 #endregion179 180 #region Individual Item Basic Info181 listColumn = new List<CustomReportColumn>();182 listColumn.Add(new CustomReportColumn() { PageColumnName = "Serial No.", DataBaseColumnName = "Serial_No000", ColumnType = ColumnType.String });183 listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No.", DataBaseColumnName = "IMS_Barcode_No000", ColumnType = ColumnType.String });184 listColumn.Add(new CustomReportColumn() { PageColumnName = "Self Assign ID", DataBaseColumnName = "Self_Assign_ID", ColumnType = ColumnType.String });185 listColumn.Add(new CustomReportColumn() { PageColumnName = "Detailed Description", DataBaseColumnName = "Detailed_Description", ColumnType = ColumnType.String });186 listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Status", DataBaseColumnName = "Item_Status", ColumnType = ColumnType.String });187 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks", DataBaseColumnName = "Remarks", ColumnType = ColumnType.String });188 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks2", DataBaseColumnName = "Remarks2", ColumnType = ColumnType.String });189 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks3", DataBaseColumnName = "Remarks3", ColumnType = ColumnType.String });190 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks4", DataBaseColumnName = "Remarks4", ColumnType = ColumnType.String });191 listTable.Add(new CustomReportTable()192 {193 PageTableName = "Individual Item Basic Info",194 DataBaseTableName = "View_CustomReport_IndividualItemBasicInfo",195 ListColumn = listColumn196 });197 #endregion198 199 #region Location200 listColumn = new List<CustomReportColumn>();201 listColumn.Add(new CustomReportColumn() { PageColumnName = "Headquarter Flag", DataBaseColumnName = "Headquarter_Flag", ColumnType = ColumnType.String });202 listColumn.Add(new CustomReportColumn() { PageColumnName = "Location District", DataBaseColumnName = "Location_District", ColumnType = ColumnType.String });203 listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Building", DataBaseColumnName = "Location_Building", ColumnType = ColumnType.String });204 listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Floor", DataBaseColumnName = "Location_Floor", ColumnType = ColumnType.String });205 listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Room", DataBaseColumnName = "Location_Room", ColumnType = ColumnType.String });206 listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Area", DataBaseColumnName = "Location_Area", ColumnType = ColumnType.String });207 listTable.Add(new CustomReportTable()208 {209 PageTableName = "Location",210 DataBaseTableName = "View_CustomReport_Location",211 ListColumn = listColumn212 });213 #endregion214 215 #region Physical Check Info216 listColumn = new List<CustomReportColumn>();217 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked Date", DataBaseColumnName = "Last_Physical_Checked_Date", ColumnType = ColumnType.DateTime });218 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked By", DataBaseColumnName = "Last_Physical_Checked_By", ColumnType = ColumnType.String });219 listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Check Status", DataBaseColumnName = "Last_Check_Status", ColumnType = ColumnType.String });220 listTable.Add(new CustomReportTable()221 {222 PageTableName = "Physical Check Info",223 DataBaseTableName = "View_CustomReport_PhysicalCheckInfo",224 ListColumn = listColumn225 });226 #endregion227 228 #region Aggregate229 listColumn = new List<CustomReportColumn>();230 listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No. of Aggregate Parent", DataBaseColumnName = "IMS_Barcode_No000_of_Aggregate_Parent", ColumnType = ColumnType.String });231 listTable.Add(new CustomReportTable()232 {233 PageTableName = "Aggregate",234 DataBaseTableName = "View_CustomReport_Aggregate",235 ListColumn = listColumn236 });237 #endregion238 239 #region Purchase Info240 listColumn = new List<CustomReportColumn>();241 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PR Ref No.", DataBaseColumnName = "Purchase_PR_Ref_No000", ColumnType = ColumnType.String });242 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase ICAC Quotation Ref No.", DataBaseColumnName = "Purchase_ICAC_Quotation_Ref_No000", ColumnType = ColumnType.String });243 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Mode", DataBaseColumnName = "Purchase_Mode", ColumnType = ColumnType.String });244 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PO No./ Ref", DataBaseColumnName = "Purchase_PO_No000___Ref", ColumnType = ColumnType.String });245 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Invoice No.", DataBaseColumnName = "Purchase_Invoice_No000", ColumnType = ColumnType.String });246 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Order", DataBaseColumnName = "Purchase_Date_of_Order", ColumnType = ColumnType.DateTime });247 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Receipt", DataBaseColumnName = "Purchase_Date_of_Receipt", ColumnType = ColumnType.DateTime });248 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Acceptance", DataBaseColumnName = "Purchase_Date_of_Acceptance", ColumnType = ColumnType.DateTime });249 listColumn.Add(new CustomReportColumn() { PageColumnName = "Free Warranty Period", DataBaseColumnName = "Free_Warranty_Period", ColumnType = ColumnType.Number });250 listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder File Reference", DataBaseColumnName = "Holder_File_Reference", ColumnType = ColumnType.String });251 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });252 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person Position", DataBaseColumnName = "Contact_Person_Position", ColumnType = ColumnType.String });253 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher Type", DataBaseColumnName = "Purchase_Related_Voucher_Type", ColumnType = ColumnType.String });254 listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher No.", DataBaseColumnName = "Purchase_Related_Voucher_No000", ColumnType = ColumnType.String });255 listTable.Add(new CustomReportTable()256 {257 PageTableName = "Purchase Info",258 DataBaseTableName = "View_CustomReport_PurchaseInfo",259 ListColumn = listColumn260 });261 #endregion262 263 #region Loan Info264 listColumn = new List<CustomReportColumn>();265 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Loan Status", DataBaseColumnName = "Current_Loan_Status", ColumnType = ColumnType.String });266 listColumn.Add(new CustomReportColumn() { PageColumnName = "Staff Name of Borrower", DataBaseColumnName = "Staff_Name_of_Borrower", ColumnType = ColumnType.String });267 listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Department", DataBaseColumnName = "Borrower111s_Department", ColumnType = ColumnType.String });268 listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Group", DataBaseColumnName = "Borrower111s_Group", ColumnType = ColumnType.String });269 listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Section", DataBaseColumnName = "Borrower111s_Section", ColumnType = ColumnType.String });270 listTable.Add(new CustomReportTable()271 {272 PageTableName = "Loan Info",273 DataBaseTableName = "View_CustomReport_LoanInfo",274 ListColumn = listColumn275 });276 #endregion277 278 #region Issue Info279 listColumn = new List<CustomReportColumn>();280 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Issue Status", DataBaseColumnName = "Current_Issue_Status", ColumnType = ColumnType.String });281 listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Dept", DataBaseColumnName = "Issue_Dept", ColumnType = ColumnType.String });282 listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Group", DataBaseColumnName = "Issue_Group", ColumnType = ColumnType.String });283 listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Section", DataBaseColumnName = "Issue_Section", ColumnType = ColumnType.String });284 listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Unit", DataBaseColumnName = "Issue_Unit", ColumnType = ColumnType.String });285 listTable.Add(new CustomReportTable()286 {287 PageTableName = "Issue Info",288 DataBaseTableName = "View_CustomReport_IssueInfo",289 ListColumn = listColumn290 });291 #endregion292 293 #region End-user info294 listColumn = new List<CustomReportColumn>();295 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Item End-user", DataBaseColumnName = "Current_Item_End____user", ColumnType = ColumnType.String });296 listColumn.Add(new CustomReportColumn() { PageColumnName = "Usage Date", DataBaseColumnName = "Usage_Date", ColumnType = ColumnType.String });297 listTable.Add(new CustomReportTable()298 {299 PageTableName = "End-user info",300 DataBaseTableName = "View_CustomReport_EndUserInfo",301 ListColumn = listColumn302 });303 #endregion304 305 #region Surplus Info306 listColumn = new List<CustomReportColumn>();307 listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Status", DataBaseColumnName = "Surplus_Status", ColumnType = ColumnType.String });308 listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date From", DataBaseColumnName = "Surplus_Post_Out_Date_From", ColumnType = ColumnType.DateTime });309 listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date To", DataBaseColumnName = "Surplus_Post_Out_Date_To", ColumnType = ColumnType.DateTime });310 listTable.Add(new CustomReportTable()311 {312 PageTableName = "Surplus Info",313 DataBaseTableName = "View_CustomReport_SurplusInfo",314 ListColumn = listColumn315 });316 #endregion317 318 #region Disposal Info319 listColumn = new List<CustomReportColumn>();320 listColumn.Add(new CustomReportColumn() { PageColumnName = "Anticipated Disposal Date", DataBaseColumnName = "Anticipated_Disposal_Date", ColumnType = ColumnType.DateTime });321 listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Status", DataBaseColumnName = "Disposal_Status", ColumnType = ColumnType.String });322 listColumn.Add(new CustomReportColumn() { PageColumnName = "Dispose Job No.", DataBaseColumnName = "Dispose_Job_No000", ColumnType = ColumnType.String });323 listColumn.Add(new CustomReportColumn() { PageColumnName = "Date of Disposal", DataBaseColumnName = "Date_of_Disposal", ColumnType = ColumnType.DateTime });324 listColumn.Add(new CustomReportColumn() { PageColumnName = "Condition of Item in Disposal", DataBaseColumnName = "Condition_of_Item_in_Disposal", ColumnType = ColumnType.String });325 listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Method", DataBaseColumnName = "Disposal_Method", ColumnType = ColumnType.String });326 listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Related Voucher No.", DataBaseColumnName = "Disposal_Related_Voucher_No000", ColumnType = ColumnType.String });327 listTable.Add(new CustomReportTable()328 {329 PageTableName = "Disposal Info",330 DataBaseTableName = "View_CustomReport_DisposalInfo",331 ListColumn = listColumn332 });333 #endregion334 335 #region Repair Info336 listColumn = new List<CustomReportColumn>();337 listColumn.Add(new CustomReportColumn() { PageColumnName = "On Repair Status", DataBaseColumnName = "On_Repair_Status", ColumnType = ColumnType.String });338 listTable.Add(new CustomReportTable()339 {340 PageTableName = "Repair Info",341 DataBaseTableName = "View_CustomReport_RepairInfo",342 ListColumn = listColumn343 });344 #endregion345 346 #region Replace Info347 listColumn = new List<CustomReportColumn>();348 listColumn.Add(new CustomReportColumn() { PageColumnName = "Replaced Status", DataBaseColumnName = "Replaced_Status", ColumnType = ColumnType.String });349 listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No of Replacing Item", DataBaseColumnName = "IMS_Barcode_No_of_Replacing_Item", ColumnType = ColumnType.String });350 listColumn.Add(new CustomReportColumn() { PageColumnName = "Voucher No for Replaced Item", DataBaseColumnName = "Voucher_No_for_Replaced_Item", ColumnType = ColumnType.String });351 listTable.Add(new CustomReportTable()352 {353 PageTableName = "Replace Info",354 DataBaseTableName = "View_CustomReport_ReplaceInfo",355 ListColumn = listColumn356 });357 #endregion358 359 #region Delegate Maint User Info360 listColumn = new List<CustomReportColumn>();361 listColumn.Add(new CustomReportColumn() { PageColumnName = "Delegated to others for Maintenance", DataBaseColumnName = "Delegated_to_others_for_Maintenance", ColumnType = ColumnType.String });362 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Dept", DataBaseColumnName = "Maintenance_Dept", ColumnType = ColumnType.String });363 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Group", DataBaseColumnName = "Maintenance_Group", ColumnType = ColumnType.String });364 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Section", DataBaseColumnName = "Maintenance_Section", ColumnType = ColumnType.String });365 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit", DataBaseColumnName = "Maintenance_Unit", ColumnType = ColumnType.String });366 listTable.Add(new CustomReportTable()367 {368 PageTableName = "Delegate Maint User Info",369 DataBaseTableName = "View_CustomReport_DelegateMaintUserInfo",370 ListColumn = listColumn371 });372 #endregion373 374 #region Maintenance Basic375 listColumn = new List<CustomReportColumn>();376 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });377 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });378 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });379 listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });380 listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });381 listTable.Add(new CustomReportTable()382 {383 PageTableName = "Maintenance Basic",384 DataBaseTableName = "View_CustomReport_MaintenanceBasic",385 ListColumn = listColumn386 });387 #endregion388 389 #region Current Maintenance Info390 listColumn = new List<CustomReportColumn>();391 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Contract User Reference No.", DataBaseColumnName = "Current_Maintenance_Contract_User_Reference_No000", ColumnType = ColumnType.String });392 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Vendor", DataBaseColumnName = "Current_Maintenance_Vendor", ColumnType = ColumnType.String });393 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Unit Cost", DataBaseColumnName = "Current_Maintenance_Unit_Cost", ColumnType = ColumnType.Number });394 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage Start Date", DataBaseColumnName = "Current_Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });395 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage End Date", DataBaseColumnName = "Current_Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });396 listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Level", DataBaseColumnName = "Current_Maintenance_Level", ColumnType = ColumnType.String });397 listColumn.Add(new CustomReportColumn() { PageColumnName = "Next Maintenance BU Date", DataBaseColumnName = "Next_Maintenance_BU_Date", ColumnType = ColumnType.DateTime });398 listTable.Add(new CustomReportTable()399 {400 PageTableName = "Current Maintenance Info",401 DataBaseTableName = "View_CustomReport_CurrentMaintenanceInfo",402 ListColumn = listColumn403 });404 #endregion405 #endregion406 break;407 case 2:408 #region Maintenance Contract409 #region Contract Header410 listColumn = new List<CustomReportColumn>();411 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract User Reference No.", DataBaseColumnName = "Contract_User_Reference_No000", ColumnType = ColumnType.String });412 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Name", DataBaseColumnName = "Contract_Name", ColumnType = ColumnType.String });413 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });414 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Start Date", DataBaseColumnName = "Contract_Start_Date", ColumnType = ColumnType.DateTime });415 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract End Date", DataBaseColumnName = "Contract_End_Date", ColumnType = ColumnType.DateTime });416 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });417 listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Post", DataBaseColumnName = "Contact_Post", ColumnType = ColumnType.String });418 listColumn.Add(new CustomReportColumn() { PageColumnName = "BU Date", DataBaseColumnName = "BU_Date", ColumnType = ColumnType.DateTime });419 listTable.Add(new CustomReportTable()420 {421 PageTableName = "Contract Header",422 DataBaseTableName = "View_CustomReport_ContractHeader",423 ListColumn = listColumn424 });425 #endregion426 427 #region Item’s Vendor Info428 listColumn = new List<CustomReportColumn>();429 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor", DataBaseColumnName = "Maint_Vendor", ColumnType = ColumnType.String });430 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person", DataBaseColumnName = "Maint_Vendor_Contact_Person", ColumnType = ColumnType.String });431 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person Position", DataBaseColumnName = "Maint_Vendor_Contact_Person_Position", ColumnType = ColumnType.String });432 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Quotation No.", DataBaseColumnName = "Maint_Vendor_Quotation_No000", ColumnType = ColumnType.String });433 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Po No./Ref", DataBaseColumnName = "Maint_Po_No000__Ref", ColumnType = ColumnType.String });434 listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Schedule", DataBaseColumnName = "Payment_Schedule", ColumnType = ColumnType.Number });435 listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Date", DataBaseColumnName = "Payment_Date", ColumnType = ColumnType.DateTime });436 listColumn.Add(new CustomReportColumn() { PageColumnName = "Paid Amount", DataBaseColumnName = "Paid_Amount", ColumnType = ColumnType.Number });437 listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Status", DataBaseColumnName = "Payment_Status", ColumnType = ColumnType.String });438 listColumn.Add(new CustomReportColumn() { PageColumnName = "Invoice No.", DataBaseColumnName = "Invoice_No000", ColumnType = ColumnType.String });439 listColumn.Add(new CustomReportColumn() { PageColumnName = "File Ref No.", DataBaseColumnName = "File_Ref_No000", ColumnType = ColumnType.String });440 listTable.Add(new CustomReportTable()441 {442 PageTableName = "Item’s Vendor Info",443 DataBaseTableName = "View_CustomReport_ItemsVendorInfo",444 ListColumn = listColumn445 });446 #endregion447 448 #region Item’s Maintenance Basic Info449 listColumn = new List<CustomReportColumn>();450 listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Serial No.", DataBaseColumnName = "Item_Serial_No000", ColumnType = ColumnType.String });451 listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Item Barcode No.", DataBaseColumnName = "IMS_Item_Barcode_No000", ColumnType = ColumnType.String });452 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Grouping", DataBaseColumnName = "Maintenance_Grouping", ColumnType = ColumnType.String });453 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });454 listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });455 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });456 listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });457 listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });458 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });459 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Cost", DataBaseColumnName = "Maintenance_Unit_Cost", ColumnType = ColumnType.Number });460 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Actual Cost", DataBaseColumnName = "Maintenance_Unit_Actual_Cost", ColumnType = ColumnType.Number });461 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage Start Date", DataBaseColumnName = "Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });462 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage End Date", DataBaseColumnName = "Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });463 listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Level", DataBaseColumnName = "Maintenance_Level", ColumnType = ColumnType.String });464 listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Remarks in this Contract", DataBaseColumnName = "Item_Remarks_in_this_Contract", ColumnType = ColumnType.String });465 listTable.Add(new CustomReportTable()466 {467 PageTableName = "Item’s Maintenance Basic Info",468 DataBaseTableName = "View_CustomReport_ItemsMaintenanceBasicInfo",469 ListColumn = listColumn470 });471 #endregion472 #endregion473 break;474 }475 }476 477 public static DataTable GetCustomReport(string sql)478 {479 DataTable dt = SqlHelper.ExecuteDataTable(sql);480 if (dt != null)481 {482 for (int i = 0, len = dt.Columns.Count; i < len; i++)483 dt.Columns[i].ColumnName = ConvertColumnName(dt.Columns[i].ColumnName);484 }485 return dt;486 }487 488 public static string ConvertColumnName(string columnName)489 {490 string convertColumnName = "";491 if (!string.IsNullOrEmpty(columnName))492 convertColumnName = columnName.Replace("____", "-").Replace("000", ".").Replace("__", "/").Replace("_", " ").Replace("111", "'");493 return convertColumnName;494 }495 }496 }
SearchCustomReport.aspx
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SearchCustomReport.aspx.cs" 2 Inherits="IMSWeb.App.IS.Report.SearchCustomReport" %> 3 4 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 5 <html "http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7 <title></title> 8 </head> 9 <body> 10 <form id="form1" runat="server"> 11 <div> 12 <fieldset class="pageFieldset" style="width: 800px"> 13 <legend class="segmentTitle">Custom Report Search</legend> 14 <table class="tblDetail"> 15 <tr style="display:none"> 16 <td> 17 Issued Item 18 </td> 19 <td colspan="3"> 20 <asp:DropDownList ID="ddlIssuedItem" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlIssuedItem_SelectedIndexChanged" 21 Width="160px"> 22 <asp:ListItem Value="">--ALL--</asp:ListItem> 23 <asp:ListItem Value="1">--Show--</asp:ListItem> 24 <asp:ListItem Value="0">--Don't Show--</asp:ListItem> 25 </asp:DropDownList> 26 </td> 27 </tr> 28 <tr> 29 <td style=" white-space:200px;"> 30 Inventory Holder 31 </td> 32 <td colspan="3"> 33 <asp:DropDownList ID="ddlHolder" runat="server"> 34 </asp:DropDownList> 35 </td> 36 </tr> 37 <tr> 38 <td> 39 Table Set 40 </td> 41 <td> 42 <asp:DropDownList ID="ddlTableSet" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTableSet_SelectedIndexChanged" 43 Width="160px"> 44 </asp:DropDownList> 45 </td> 46 <td style="width: 80px;"> 47 Item Group 48 </td> 49 <td> 50 <asp:DropDownList ID="ddlTable" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTable_SelectedIndexChanged" 51 Width="160px"> 52 </asp:DropDownList> 53 </td> 54 </tr> 55 <tr> 56 <td> 57 Item Name 58 </td> 59 <td colspan="3"> 60 <table cellpadding="0" cellspacing="0" style="width: 100%;"> 61 <tr> 62 <td> 63 Brand 64 </td> 65 <td rowspan="2" class="style1"> 66 <asp:Button ID="btnSelectAll" runat="server" Text=">>" OnClick="btnSelectAll_Click" /> 67 <p /> 68 <asp:Button ID="btnSelect" runat="server" Text=">" OnClick="btnSelect_Click" /> 69 <p /> 70 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 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 109 <asp:TextBox ID="txtValueEnter" runat="server" Width="228px"></asp:TextBox> 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 116 </td>117 </tr>118 <tr>119 <td>120 Search Criteria121 </td>122 <td colspan="3">123 <asp:TextBox ID="txtSearchCriteria" runat="server" Width="469px" Height="100px" ReadOnly="true"124 TextMode="MultiLine"></asp:TextBox> 125 <asp:TextBox ID="txtSearchCriteriaVal" runat="server" ReadOnly="true" Style="display:none"></asp:TextBox>126 <asp:Button ID="btnResetSearchCriteria" runat="server" Text="Reset" OnClick="btnResetSearchCriteria_Click" />127 </td>128 </tr>129 </table>130 <div class="block">131 <asp:Button ID="btnPreview" runat="server" Text="Preview" ValidationGroup="lose"132 OnClick="btnPreview_Click" />133 </div>134 </fieldset>135 </div>136 </form>137 </body>138 </html>
SearchCustomReport.aspx.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 8 using Bll; 9 using System.Text; 10 using Bll.Report; 11 12 namespace IMSWeb.App.IS.Report 13 { 14 /// <summary> 15 /// add by Kenmu at 2013-04-28 16 /// </summary> 17 public partial class SearchCustomReport : BasePage 18 { 19 public const string joinStr = "\r\n"; 20 21 #region Property 22 23 #endregion 24 25 #region Event 26 protected void Page_Load(object sender, EventArgs e) 27 { 28 if (!IsPostBack) 29 InitData(); 30 } 31 32 protected void ddlIssuedItem_SelectedIndexChanged(object sender, EventArgs e) 33 { 34 } 35 36 protected void ddlTableSet_SelectedIndexChanged(object sender, EventArgs e) 37 { 38 InitTable(); 39 } 40 41 protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e) 42 { 43 InitColumn(); 44 } 45 46 protected void btnSelect_Click(object sender, EventArgs e) 47 { 48 SelectItem(); 49 ControlPreview(); 50 } 51 52 protected void btnUnSelect_Click(object sender, EventArgs e) 53 { 54 UnSelectItem(); 55 ControlPreview(); 56 } 57 58 protected void btnSelectAll_Click(object sender, EventArgs e) 59 { 60 SelectAllItem(); 61 ControlPreview(); 62 } 63 64 protected void btnUnSelectAll_Click(object sender, EventArgs e) 65 { 66 UnSelectAllItem(); 67 ControlPreview(); 68 } 69 70 protected void ddlAvailableFieldEnter_SelectedIndexChanged(object sender, EventArgs e) 71 { 72 ControlValueEnter(); 73 } 74 75 protected void btnAddCriteria_Click(object sender, EventArgs e) 76 { 77 AddCriteria(); 78 } 79 80 protected void btnResetSearchCriteria_Click(object sender, EventArgs e) 81 { 82 ResetSearchCriteria(); 83 } 84 85 protected void btnPreview_Click(object sender, EventArgs e) 86 { 87 string url = string.Format("CustomReport.aspx?Sql={0}&t=", Server.UrlEncode(FinallySql().Replace(joinStr, ddlJoin.SelectedValue)), DateTime.Now.ToString("yyyyMMddHHmmssms")); 88 this.RegisterJS(string.Format("window.open('{0}');", url)); 89 } 90 #endregion 91 92 #region Method 93 private void InitData() 94 { 95 bool isSo = App_Code.SessionUtil.IsSORole; 96 is_inventory_holding_unit bllHoldingUnit = new is_inventory_holding_unit(); 97 Bll.Common.Report.InitDropDownList(ddlHolder, true, null, bllHoldingUnit.GetAvailableISInventoryHolder(), "holding_name", "holding_id"); 98 if (App_Code.SessionUtil.IsHolding) 99 this.ddlHolder.SelectedValue = CurrentHoldingId.ToString();100 if (!isSo)101 {102 ddlHolder.Enabled = false;103 ddlHolder.SelectedValue = CurrentHoldingId.ToString();104 }105 106 InitTableSet(isSo);107 }108 109 private void InitTableSet(bool isSORole)110 {111 string[] tableSetNameArr = Bll.Report.CustomReport.tableSetNameArr;112 int lastIndex = tableSetNameArr.Length - 1;113 if (!isSORole)114 {115 ddlTableSet.Items.Add(new ListItem(tableSetNameArr[lastIndex], lastIndex.ToString()));116 }117 else118 {119 for (int i = 0; i <= lastIndex; i++)120 ddlTableSet.Items.Add(new ListItem(tableSetNameArr[i], i.ToString()));121 }122 ddlTableSet.SelectedIndex = 0;123 InitTable();124 }125 126 private void InitTable()127 {128 List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);129 Bll.Common.Report.InitDropDownList(ddlTable, false, null, listTable, "PageTableName", "DataBaseTableName");130 ddlTable.SelectedIndex = 0;131 InitColumn();132 }133 134 private CustomReportTable CurrentReportTable()135 {136 List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), 5);137 CustomReportTable entity = (from table in listTable138 where table.DataBaseTableName == ddlTable.SelectedValue139 select table).FirstOrDefault<CustomReportTable>();140 return entity;141 }142 143 private void InitColumn()144 {145 List<CustomReportColumn> listColumn = CurrentReportTable().ListColumn;146 Bll.Common.Report.InitListBox(lstAvailableField, false, null, listColumn, "PageColumnName", "DataBaseColumnName");147 Bll.Common.Report.InitDropDownList(ddlAvailableFieldEnter, false, null, listColumn, "PageColumnName", "DataBaseColumnName");148 lstAvailableField.SelectedIndex = 0;149 ddlAvailableFieldEnter.SelectedIndex = 0;150 lstSelectedField.Items.Clear();151 ResetSearchCriteria();152 ControlValueEnter();153 ControlPreview();154 }155 156 private void SelectItem()157 {158 if (lstAvailableField.SelectedIndex != -1)159 {160 int currentIndex = lstAvailableField.SelectedIndex;161 ListItem li = new ListItem(lstAvailableField.SelectedItem.Text, lstAvailableField.SelectedItem.Value);162 lstSelectedField.Items.Add(li);163 lstAvailableField.Items.Remove(li);164 165 int count = lstAvailableField.Items.Count;166 if (count > 0)167 lstAvailableField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;168 }169 }170 171 private void UnSelectItem()172 {173 if (lstSelectedField.SelectedIndex != -1)174 {175 int currentIndex = lstSelectedField.SelectedIndex;176 ListItem li = new ListItem(lstSelectedField.SelectedItem.Text, lstSelectedField.SelectedItem.Value);177 lstAvailableField.Items.Add(li);178 lstSelectedField.Items.Remove(li);179 180 int count = lstSelectedField.Items.Count;181 if (count > 0)182 lstSelectedField.SelectedIndex = count > currentIndex ? currentIndex : count - 1;183 }184 }185 186 private void SelectAllItem()187 {188 int count = lstAvailableField.Items.Count;189 if (count > 0)190 {191 foreach (ListItem li in lstAvailableField.Items)192 lstSelectedField.Items.Add(li);193 lstAvailableField.Items.Clear();194 }195 }196 197 private void UnSelectAllItem()198 {199 int count = lstSelectedField.Items.Count;200 if (count > 0)201 {202 foreach (ListItem li in lstSelectedField.Items)203 lstAvailableField.Items.Add(li);204 lstSelectedField.Items.Clear();205 }206 }207 208 private void AddCriteria()209 {210 bool isLike = ddlOperation.SelectedValue.IndexOf("like") != -1;211 bool isAddSingleQuotes = IsAddSingleQuotes();212 string val = txtValueEnter.Text;213 if (isLike)214 {215 val = string.Format("'{0}'", ddlOperation.SelectedValue.Replace("like", txtValueEnter.Text));216 }217 else if (isAddSingleQuotes)218 {219 val = string.Format("'{0}'", txtValueEnter.Text);220 }221 222 string criteriaVal = string.Format("{0}{1}{2}",223 ddlAvailableFieldEnter.SelectedItem.Value,224 isLike ? " like " : ddlOperation.SelectedValue,225 val);226 string criteriaText = string.Format("{0}{1}{2}",227 ddlAvailableFieldEnter.SelectedItem.Text,228 isLike ? " like " : ddlOperation.SelectedValue,229 val);230 231 if (txtSearchCriteriaVal.Text.IndexOf(criteriaVal) == -1)232 {233 if (isLike || isAddSingleQuotes || txtValueEnter.Text.Trim() != "")234 {235 criteriaVal = string.Format("{0} {1}", txtSearchCriteriaVal.Text != "" ? " " + joinStr : "", criteriaVal);236 txtSearchCriteriaVal.Text += criteriaVal;237 238 criteriaText = string.Format("{0} {1}", txtSearchCriteria.Text != "" ? " " + joinStr : "", criteriaText);239 txtSearchCriteria.Text += criteriaText;240 }241 }242 }243 244 private string FinallySql()245 {246 StringBuilder sb = new StringBuilder("select ");247 foreach (ListItem li in lstSelectedField.Items)248 sb.AppendFormat("{0},", li.Value);249 sb.Remove(sb.Length - 1, 1);250 string dataBaseTableName = ddlTable.SelectedValue;251 sb.AppendFormat(" from {0}", dataBaseTableName);252 string criteria = txtSearchCriteriaVal.Text;253 if (criteria != "")254 sb.AppendFormat(" where {0}", criteria);255 string holdingId = ddlHolder.SelectedValue;256 if (holdingId != "0")257 {258 sb.AppendFormat(" {0} Holding_Id={1}",259 sb.ToString().IndexOf("where") != -1 ? "and" : "where",260 holdingId);261 }262 if (Bll.Report.CustomReport.tableHaveUserId.IndexOf(dataBaseTableName) != -1)263 {264 sb.AppendFormat(" {0} User_Id={1}",265 sb.ToString().IndexOf("where") != -1 ? "and" : "where",266 CurrentUserId);267 }268 return sb.ToString();269 }270 271 private void ControlValueEnter()272 {273 txtValueEnter.Text = "";274 txtValueEnter.Attributes["onclick"] = "return true;";275 txtValueEnter.CssClass = "textBox";276 txtValueEnter.Attributes["onkeyup"] = "return true;";277 txtValueEnter.Attributes["onafterpaste"] = "return true;";278 279 ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;280 switch (ct)281 {282 case ColumnType.Number:283 string jsStr = "this.value=this.value.replace(/[^0-9|^\\-|^\\.]/g,\'\');";284 txtValueEnter.Attributes["onkeyup"] = jsStr;285 txtValueEnter.Attributes["onafterpaste"] = jsStr;286 break;287 case ColumnType.DateTime:288 txtValueEnter.Attributes["onclick"] = "WdatePicker({dateFmt:'MM-dd-yyyy'})";289 txtValueEnter.CssClass = "Wdate";290 break;291 default:292 break;293 }294 }295 296 private bool IsAddSingleQuotes()297 {298 ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;299 return ct != ColumnType.Number;300 }301 302 private void ResetSearchCriteria()303 {304 txtSearchCriteria.Text = "";305 txtSearchCriteriaVal.Text = "";306 }307 308 private void ControlPreview()309 {310 btnPreview.Enabled = lstSelectedField.Items.Count > 0;311 }312 #endregion313 }314 }
CustomReport.aspx
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomReport.aspx.cs" Inherits="IMSWeb.App.IS.Report.CustomReport" %> 2 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 <html "http://www.w3.org/1999/xhtml"> 5 <head runat="server"> 6 <title></title> 7 </head> 8 <body> 9 <form id="form1" runat="server">10 <div>11 <fieldset>12 <legend class="segmentTitle">Custom Report List</legend>13 <div style="margin-left: 10px; margin-bottom: 20px">14 <asp:GridView runat="server" ID="gvCustomReportList" AllowPaging="True" PageSize="20"15 OnPageIndexChanging="gvCustomReportList_PageIndexChanging">16 </asp:GridView>17 <p />18 <asp:Button runat="server" Width="100px" ID="btnExportCSV" Text="Export Report" OnClick="btnExportCSV_Click" /> 19 <asp:Button runat="server" Width="100px" ID="btnPrint" Text="Print" />20 </div>21 </fieldset>22 </div>23 </form>24 </body>25 </html>
CustomReport.aspx.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 8 using System.Data; 9 using System.Text; 10 11 namespace IMSWeb.App.IS.Report 12 { 13 /// <summary> 14 /// add by Kenmu at 2013-05-07 15 /// </summary> 16 public partial class CustomReport : BasePage 17 { 18 #region Event 19 protected void Page_Load(object sender, EventArgs e) 20 { 21 if (!IsPostBack) 22 InitData(); 23 } 24 25 protected void gvCustomReportList_PageIndexChanging(object sender, GridViewPageEventArgs e) 26 { 27 gvCustomReportList.PageIndex = e.NewPageIndex; 28 InitData(); 29 } 30 31 protected void btnExportCSV_Click(object sender, EventArgs e) 32 { 33 try 34 { 35 string sql = Server.UrlDecode(Request["Sql"]); 36 DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql); 37 StringBuilder sbHeader = new StringBuilder(); 38 StringBuilder sbContent = new StringBuilder(); 39 DateTime tempDateTime = DateTime.MinValue; 40 string tempVal = ""; 41 42 for (int i = 0, len = dt.Rows.Count; i < len; i++) 43 { 44 for (int j = 0, len2 = dt.Columns.Count; j < len2; j++) 45 { 46 if (i == 0) 47 { 48 sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName); 49 } 50 51 tempVal = dt.Rows[i][j].ToString(); 52 if(DateTime.TryParse(tempVal,out tempDateTime)) 53 tempVal = tempDateTime.ToString("dd-MM-yyyy"); 54 55 sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal)); 56 } 57 sbContent.Remove(sbContent.Length - 1, 1); 58 sbContent.AppendLine(); 59 } 60 sbHeader.Remove(sbHeader.Length - 1, 1); 61 sbHeader.AppendLine(); 62 63 IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response, 64 string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")), 65 sbHeader.ToString() + sbContent.ToString()); 66 } 67 catch (Exception ex) 68 { 69 IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId); 70 this.ShowErrorMsg(ex); 71 } 72 } 73 #endregion 74 75 #region Method 76 private void InitData() 77 { 78 string sql = Server.UrlDecode(Request["Sql"]); 79 DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql); 80 int count = dt.Rows.Count; 81 if (count == 0) 82 { 83 this.RegisterJS("alert('Without relevant data!');window.opener=null;window.open('','_self');window.close();"); 84 return; 85 } 86 gvCustomReportList.DataSource = dt; 87 DynamicAddColumn(dt); 88 gvCustomReportList.DataBind(); 89 } 90 91 private void DynamicAddColumn(DataTable dt) 92 { 93 gvCustomReportList.Columns.Clear(); 94 for (int i = 0, len = dt.Columns.Count; i < len; i++) 95 AddColumn(dt.Columns[i].ColumnName, dt.Columns[i].DataType.ToString()); 96 } 97 98 private void AddColumn(string columnName, string type) 99 {100 BoundField bf = new BoundField();101 bf.DataField = columnName;102 bf.HeaderText = columnName;103 switch (type)104 {105 case "System.DateTime"://日期类型106 bf.DataFormatString = "{0:dd-MM-yyyy}";107 break;108 //case "System.String"://字符串类型109 // break;110 //case "System.Boolean"://布尔型 111 // break;112 //case "System.Int16"://整型113 //case "System.Int32":114 //case "System.Int64":115 //case "System.Byte":116 // break;117 //case "System.Decimal"://浮点型118 //case "System.Double":119 // break;120 //case "System.DBNull"://Null值处理121 // break;122 default:123 break;124 }125 gvCustomReportList.Columns.Add(bf);126 }127 #endregion128 }129 }
原标题:GridView动态添加列并判断绑定数据DataTable的列类型控制展示内容
关键词:GridView