此篇随笔是2013年根据项目需求开发记录的，不一定符合大众口味，只需了解开发思路，毕竟解决方案多种多样。
下面简单说说需求点吧：
（1）通过下拉列表可以选择一个DataSet（数据集），一个DataSet存在可以互相关联的多个DataTable（数据表格），DataTable数据来源于数据库视图；SQL语句关联比较复杂
（2）一个DataTable（数据表格）存在多个可供选择查询显示的Column（列），支持动态组合
（3）Column（列）同时支持作为查询条件进行并运算
先看效果吧，免得待会看到太多代码失去看下去的兴趣了：
（1）数据库方面
USE [IMSDB]
GO drop view View_CustomReport_ItemCategory
go
drop view View_CustomReport_ItemClassification
go ---- the view total is 21
alter view View_CustomReport_BasicInfo
as
select iig.holding_id as 'Holding_Id',
row_number() over(order by iig.item_group_id) as 'Internal_ID',
od.dept_name as 'Holder_Department',
og.group_name as 'Holder_Group',
os.section_name as 'Holder_Section',
ou.unit_name as 'Holder_Unit',
iig.brand as 'Brand',
iig.model as 'Model',
iica.name_en as 'Category',
iicaSub.name_en as 'Sub____category',
iicl.name_en as 'Classification',
iiclSub.name_en as 'Sub____classification',
iig.item_desc as 'GF272_Description',
(case when iig.inventory_flg=1 then 'inventory'
when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag',
(select ivc.balance
from is_view_item_group ivig
inner join is_view_column ivc
on ivig.sheet_id = ivc.sheet_id
and ivig.line_id = ivc.line_no
and ivig.column_id = ivc.column_no
where ivig.item_group_id = iig.item_group_id) as 'Current_Qty_Balance',
iig.unit_of_qty as 'Unit_of_Qty'
from is_item_group iig
inner join is_inventory_holding_unit iihu
on iig.holding_id = iihu.holding_id
left join org_department od
on iihu.dept_id = od.dept_id
left join org_group og
on iihu.group_id = og.group_id
left join org_section os
on iihu.section_id = os.section_id
left join org_unit ou
on iihu.unit_id = ou.unit_id
left join is_item_category iica
on iig.item_cat_id = iica.item_cat_id
left join is_item_category iicaSub
on iig.item_sub_cat_id = iicaSub.item_cat_id
left join is_item_classification iicl
on iig.item_classic_id = iicl.item_classific_id
left join is_item_classification iiclSub
on iig.item_sub_classic_id = iiclSub.item_classific_id
go alter view View_CustomReport_GF272SheetInfo
as
select iig.holding_id as 'Holding_Id',
ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000',
ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000',
ivc.line_no as 'Last_GF272_Valid_Line_No000',
ivc.column_no as 'Last_GF272_Valid_Column_No000',
ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000'
from is_item_group iig
inner join is_view_item_group ivig
on iig.item_group_id = ivig.item_group_id
inner join is_view_sheet ivs
on ivig.sheet_id = ivs.sheet_id
inner join is_view_column ivc
on ivs.sheet_id = ivc.sheet_id
inner join is_view_line ivl
on ivs.sheet_id = ivl.sheet_id
go alter view View_CustomReport_LineItemInformation
as
select iig.holding_id as 'Holding_Id',
od.dept_name as 'Holder_Department',
og.group_name as 'Holder_Group',
os.section_name as 'Holder_Section',
ou.unit_name as 'Holder_Unit',
iig.brand as 'Brand',
iig.model as 'Model',
iica.name_en as 'Category',
iicaSub.name_en as 'Sub____category',
iicl.name_en as 'Classification',
iiclSub.name_en as 'Sub____classification',
iig.item_desc as 'GF272_Description',
(case when iig.inventory_flg=1 then 'inventory'
when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag',
ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000',
ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000',
ivc.line_no as 'Last_GF272_Valid_Line_No000',
ivc.column_no as 'Last_GF272_Valid_Column_No000',
ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000'
from is_item_group iig
inner join is_inventory_holding_unit iihu
on iig.holding_id = iihu.holding_id
left join org_department od
on iihu.dept_id = od.dept_id
left join org_group og
on iihu.group_id = og.group_id
left join org_section os
on iihu.section_id = os.section_id
left join org_unit ou
on iihu.unit_id = ou.unit_id
left join is_item_category iica
on iig.item_cat_id = iica.item_cat_id
left join is_item_category iicaSub
on iig.item_sub_cat_id = iicaSub.item_cat_id
left join is_item_classification iicl
on iig.item_classic_id = iicl.item_classific_id
left join is_item_classification iiclSub
on iig.item_sub_classic_id = iiclSub.item_classific_id
inner join is_view_item_group ivig
on iig.item_group_id = ivig.item_group_id
inner join is_view_sheet ivs
on ivig.sheet_id = ivs.sheet_id
inner join is_view_column ivc
on ivs.sheet_id = ivc.sheet_id
inner join is_view_line ivl
on ivs.sheet_id = ivl.sheet_id
go alter view View_CustomReport_IndividualItemBasicInfo
as
select iig.holding_id as 'Holding_Id',
ii.serial_no as 'Serial_No000',
ii.barcode_no as 'IMS_Barcode_No000',
ii.self_assign_id as 'Self_Assign_ID',
ii.detailed_desc as 'Detailed_Description',
(case when ii.item_status='RJ' then 'Reject'
when ii.item_status='TP' then 'Temp'
when ii.item_status='DR' then 'Draft'
when ii.item_status='N' then 'Normal'
when ii.item_status='UN' then 'Unserviceable'
when ii.item_status='TI' then 'Transfer'
when ii.item_status='TO' then 'TransferOutOfICAC'
when ii.item_status='D' then 'Disposed'
when ii.item_status='WO' then 'WriteOff'
when ii.item_status='SI' then 'SurplusItem'
when ii.item_status='R' then 'Repair'
when ii.item_status='TL' then 'TransferLedger' end) as 'Item_Status',
ii.remarks as 'Remarks',
ii.remarks2 as 'Remarks2',
ii.remarks3 as 'Remarks3',
ii.remarks4 as 'Remarks4'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
go alter view View_CustomReport_Location
as
select iig.holding_id as 'Holding_Id',
(case when ii.head_quarter_flg=1 then 'Yes'
when ii.head_quarter_flg=0 then 'No' end) as 'Headquarter_Flag',
il.loc_name as 'Location_District',
il2.loc_name as 'Location_Building',
il3.loc_name as 'Location_Floor',
il4.loc_name as 'Location_Room',
ii.loc_area as 'Location_Area'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join is_location il
on ii.loc_district = il.loc_id
left join is_location il2
on ii.loc_building = il2.loc_id
left join is_location il3
on ii.loc_floor = il3.loc_id
left join is_location il4
on ii.loc_room = il4.loc_id
go alter view View_CustomReport_PhysicalCheckInfo
as
select iic.holding_id as 'Holding_Id',
icld.update_datetime as 'Last_Physical_Checked_Date',
icld.check_by as 'Last_Physical_Checked_By',
(case when iic.check_status='IP' then 'InProgress'
when iic.check_status='CP' then 'Completed'
when iic.check_status='SC' then 'SoConfirm' end) as 'Last_Check_Status'
from is_check_list icl
inner join is_check_list_detail icld
on icl.list_id = icld.list_id and icld.is_manual_check=1
inner join is_inventory_check iic
on icl.check_id = iic.check_id
go alter view View_CustomReport_Aggregate
as
select iig.holding_id as 'Holding_Id',
ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join is_item_aggregate_map iiam
on ii.item_id = iiam.parent_item_id
go alter view View_CustomReport_PurchaseInfo
as
select iig.holding_id as 'Holding_Id',
ii.pr_ref_no as 'Purchase_PR_Ref_No000',
ii.quot_ref_no as 'Purchase_ICAC_Quotation_Ref_No000',
ii.purchase_mode as 'Purchase_Mode',
ii.po_no as 'Purchase_PO_No000___Ref',
ii.invoice_no as 'Purchase_Invoice_No000',
ii.date_of_order as 'Purchase_Date_of_Order',
ii.date_of_receipt as 'Purchase_Date_of_Receipt',
ii.date_of_acceptance as 'Purchase_Date_of_Acceptance',
icim.contract_month as 'Free_Warranty_Period',
ii.holder_file_ref as 'Holder_File_Reference',
ii.contact_person as 'Contact_Person',
ii.contact_person_post as 'Contact_Person_Position',
itdi.voucher_type as 'Purchase_Related_Voucher_Type',
itdi.voucher_no as 'Purchase_Related_Voucher_No000'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join(
select item_id,
datediff(month,contract_start_date,contract_end_date) as 'contract_month'
from is_contract_item_map
where contract_id in(
select min(contract_id) from is_contract_item_map
group by item_id
)
) as icim
on ii.item_id = icim.item_id
inner join(
select itdi.item_id,itr.voucher_type,itr.voucher_no
from is_tx_record itr
inner join is_tx_detail_item itdi
on itr.tx_id = itdi.tx_id
where itdi.tx_id in(
select min(tx_id) from is_tx_detail_item
group by item_id
)
) as itdi
on ii.item_id = itdi.item_id
go create function Func_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split) set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1 return substring(@str,@start,@location-@start)
end
go
alter view View_CustomReport_LoanInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.loan_flg=1 then 'On Loan'
when ii.loan_flg=0 then 'In Hand' end) as 'Current_Loan_Status',
isc.english_name as 'Staff_Name_of_Borrower',
dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',1) as 'Borrower111s_Department',
dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',2) as 'Borrower111s_Group',
dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',3) as 'Borrower111s_Section'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join is_loan_record ilr
on ii.loan_id = ilr.loan_id
left join is_staff_card isc
on ilr.borrower_id = isc.staff_card_id
go alter view View_CustomReport_IssueInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.issue_flg=1 then 'Issued'
when ii.issue_flg=0 then 'Not Issued' end) as 'Current_Issue_Status',
od.dept_name as 'Issue_Dept',
og.group_name as 'Issue_Group',
os.section_name as 'Issue_Section',
ou.unit_name as 'Issue_Unit'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join org_department od
on ii.issued_dept_id = od.dept_id
left join org_group og
on ii.issued_group_id = og.group_id
left join org_section os
on ii.issued_section_id = os.section_id
left join org_unit ou
on ii.issued_unit_id = ou.unit_id
go alter view View_CustomReport_EndUserInfo
as
select iig.holding_id as 'Holding_Id',
ieur.end_user as 'Current_Item_End____user',
convert(varchar(10),ieur.assign_date,105)+' to '+convert(varchar(10),ieur.return_date,105) as 'Usage_Date'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join is_end_user_record ieur
on ii.item_id = ieur.item_id
go alter view View_CustomReport_SurplusInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.item_status='SI' then 'Yes'
else 'No' end) as 'Surplus_Status',
isipo.[start_date] as 'Surplus_Post_Out_Date_From',
isipo.end_date as 'Surplus_Post_Out_Date_To'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join(
select item_id,[start_date],end_date
from is_surplus_item_post_out
where request_id in(
select max(request_id) from is_surplus_item_post_out
group by item_id
)
) as isipo
on ii.item_id = isipo.item_id
go alter view View_CustomReport_DisposalInfo
as
select iig.holding_id as 'Holding_Id',
ii.anticipated_date_disposal as 'Anticipated_Disposal_Date',
(case when idr.disposal_status='R' or idr.disposal_status='V' then 'Not Applicable'
when idr.disposal_status='D' or idr.disposal_status='W' then 'Requesting'
when idr.disposal_status='T' then 'Approved by SO'
when idr.disposal_status='P' then 'Physically Disposed' end) as 'Disposal_Status',
idr.job_no as 'Dispose_Job_No000',
idr.dumping_date as 'Date_of_Disposal',
idr.item_condition as 'Condition_of_Item_in_Disposal',
idr.disposal_method as 'Disposal_Method',
itr.voucher_no as 'Disposal_Related_Voucher_No000'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join is_disposal_record idr
on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id
left join is_request ir
on idr.request_id = ir.request_id
left join is_tx_record itr
on ir.out_tx_id = itr.tx_id
go alter view View_CustomReport_RepairInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.item_status='R' then 'Yes'
else 'No' end) as 'On_Repair_Status'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
go alter view View_CustomReport_ReplaceInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.replace_flg=1 then 'Yes'
else 'No' end) as 'Replaced_Status',
ii.barcode_no as 'IMS_Barcode_No_of_Replacing_Item',
itr.voucher_no as 'Voucher_No_for_Replaced_Item'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join(
select itdi.item_id,itr.voucher_no
from is_tx_record itr
inner join is_tx_detail_item itdi
on itr.tx_id = itdi.tx_id
where itdi.tx_id in(
select max(tx_id) from is_tx_detail_item
group by item_id
)
) as itr
on ii.item_id = itr.item_id
go alter view View_CustomReport_DelegateMaintUserInfo
as
select iig.holding_id as 'Holding_Id',
(case when ii.maint_dept_id is null then 'No'
when ii.maint_dept_id=0 then 'No'
else 'Yes' end) as 'Delegated_to_others_for_Maintenance',
od.dept_name as 'Maintenance_Dept',
og.group_name as 'Maintenance_Group',
os.section_name as 'Maintenance_Section',
ou.unit_name as 'Maintenance_Unit'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join org_department od
on ii.maint_dept_id = od.dept_id
left join org_group og
on ii.maint_group_id = og.group_id
left join org_section os
on ii.maint_section_id = os.section_id
left join org_unit ou
on ii.maint_unit_id = ou.unit_id
go alter view View_CustomReport_MaintenanceBasic
as
select iig.holding_id as 'Holding_Id',
ivm.vote_name as 'Maintenance_Vote',
ii.remarks_maint as 'Remarks_for_Maintenance',
img.grouping_name as 'Maint000_Grouping',
iig.gld_contract_no as 'GLD_Contract_No000',
(case when iig.further_maint_flag=1 then 'Yes'
else 'No' end) as 'Needed_to_be_Maintenance_Afterwards'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join is_maint_grouping img
on iig.maint_grouping = img.[grouping_id]
left join is_contract_item_map icim
on ii.item_id = icim.item_id
left join is_vote_maintenance ivm
on icim.vote = ivm.vote_id
go alter view View_CustomReport_CurrentMaintenanceInfo
as
select iig.holding_id as 'Holding_Id',
imc.ref_contract_id as 'Current_Maintenance_Contract_User_Reference_No000',
iv.vendor_name as 'Current_Maintenance_Vendor',
ii.annual_maint_cost as 'Current_Maintenance_Unit_Cost',
icim.contract_start_date as 'Current_Maintenance_Coverage_Start_Date',
icim.contract_end_date as 'Current_Maintenance_Coverage_End_Date',
iml.level_desc as 'Current_Maintenance_Level',
imc.bu_date as 'Next_Maintenance_BU_Date'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join is_contract_item_map icim
on ii.item_id = icim.item_id
left join is_maintenance_contract imc
on icim.contract_id = imc.contract_id
left join is_vender iv
on icim.vendor_id = iv.vendor_id
left join is_maint_level iml
on icim.level_of_maint = iml.level_code
go alter view View_CustomReport_ContractHeader
as
select iig.holding_id as 'Holding_Id',
imc.ref_contract_id as 'Contract_User_Reference_No000',
imc.contract_name as 'Contract_Name',
(case when imc.maint_pr_no='null' then ''
else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',
icim.contract_start_date as 'Contract_Start_Date',
icim.contract_end_date as 'Contract_End_Date',
imc.contact_person as 'Contact_Person',
imc.contact_person_post as 'Contact_Post',
imc.bu_date as 'BU_Date'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
left join is_contract_item_map icim
on ii.item_id = icim.item_id
left join is_maintenance_contract imc
on icim.contract_id = imc.contract_id
go alter view View_CustomReport_ItemsVendorInfo
as
select imc.holding_id as 'Holding_Id',
iv.vendor_name as 'Maint_Vendor',
icvm.contact_person as 'Maint_Vendor_Contact_Person',
icvm.contact_person_post as 'Maint_Vendor_Contact_Person_Position',
icvm.quotation_no as 'Maint_Vendor_Quotation_No000',
icvp.maint_op_no as 'Maint_Po_No000__Ref',
icvp.group_num as 'Payment_Schedule',
icvp.actual_payment_date as 'Payment_Date',
icvp.paid_amount_hkd as 'Paid_Amount',
(case when icvp.payment_status='' then 'Paid'
when icvp.payment_status='' then 'Not Paid' end) as 'Payment_Status',
icvp.invoice_no as 'Invoice_No000',
icvp.file_ref_no as 'File_Ref_No000'
from is_vender iv
inner join is_contract_vendor_map icvm
on iv.vendor_id = icvm.vendor_id
left join (
select contract_id,
vendor_id,
maint_op_no,
actual_payment_date,
paid_amount_hkd,
payment_status,
invoice_no,
file_ref_no,
row_number() over(partition by contract_id,vendor_id
order by actual_payment_date) as 'group_num'
from is_contract_vendor_payment
) as icvp
on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id
inner join is_maintenance_contract imc
on icvm.contract_id = imc.contract_id
go alter view View_CustomReport_ItemsMaintenanceBasicInfo
as
select iig.holding_id as 'Holding_Id',
ii.serial_no as 'Item_Serial_No000',
ii.barcode_no as 'IMS_Item_Barcode_No000',
iig.maint_grouping as 'Maintenance_Grouping',
ivm.vote_name as 'Maintenance_Vote',
ii.remarks_maint as 'Remarks_for_Maintenance',
img.grouping_name as 'Maint000_Grouping',
iig.gld_contract_no as 'GLD_Contract_No000',
(case when iig.further_maint_flag=1 then 'Yes'
else 'No' end) as 'Needed_to_be_Maintenance_Afterwards',
(case when imc.maint_pr_no='null' then ''
else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',
icim.cost_total_hkd as 'Maintenance_Unit_Cost',
icim.paid_total_price_hkd as 'Maintenance_Unit_Actual_Cost',
icim.contract_start_date as 'Maintenance_Coverage_Start_Date',
icim.contract_end_date as 'Maintenance_Coverage_End_Date',
iml.level_desc as 'Maintenance_Level',
icim.remarks as 'Item_Remarks_in_this_Contract'
from is_item ii
inner join is_item_group iig
on ii.item_group_id = iig.item_group_id
inner join is_maint_grouping img
on iig.maint_grouping = img.[grouping_id]
left join is_contract_item_map icim
on ii.item_id = icim.item_id
left join is_vote_maintenance ivm
on icim.vote = ivm.vote_id
left join is_maintenance_contract imc
on icim.contract_id = imc.contract_id
left join is_maint_level iml
on icim.level_of_maint = iml.level_code
go
（2）页面处理方面
CustomReport.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web; using System.Data;
using DAL; namespace Bll.Report
{
public enum ColumnType
{
Number = ,
String = ,
DateTime =
} public class CustomReportColumn
{
#region Property
private string _PageColumnName;
public string PageColumnName
{
get { return _PageColumnName; }
set { _PageColumnName = value; }
} private string _DataBaseColumnName;
public string DataBaseColumnName
{
get { return _DataBaseColumnName; }
set { _DataBaseColumnName = value; }
} private ColumnType _ColumnType;
public ColumnType ColumnType
{
get { return _ColumnType; }
set { _ColumnType = value; }
}
#endregion
} public class CustomReportTable
{
#region Property
private string _PageTableName;
public string PageTableName
{
get { return _PageTableName; }
set { _PageTableName = value; }
} private string _DataBaseTableName;
public string DataBaseTableName
{
get { return _DataBaseTableName; }
set { _DataBaseTableName = value; }
} private List<CustomReportColumn> _ListColumn;
public List<CustomReportColumn> ListColumn
{
get { return _ListColumn; }
set { _ListColumn = value; }
}
#endregion
} public class CustomReport
{
public static string[] tableSetNameArr = new string[] { "Latest Information of Line Item", "Individual Item", "Maintenance Contract" };
public static string tableHaveUserId = ""; public static Dictionary<string, List<CustomReportTable>> TableSet(int cacheMinutes)
{
Dictionary<string, List<CustomReportTable>> tableSet = new Dictionary<string, List<CustomReportTable>>();
for (int i = , len = tableSetNameArr.Length; i < len; i++)
tableSet.Add(tableSetNameArr[i], ListTable(i, ));
return tableSet;
} public static List<CustomReportTable> ListTable(int index, int cacheMinutes)
{
string cacheKey = string.Format("CustomReport_ListTable_{0}", index);
List<CustomReportTable> listTable = HttpContext.Current.Cache[cacheKey] as List<CustomReportTable>;
if (listTable == null)
{
listTable = new List<CustomReportTable>();
InitDataForTable(index, ref listTable); if (listTable.Count > && cacheMinutes > )
HttpContext.Current.Cache.Insert(cacheKey,
listTable,
null,
DateTime.Now.AddMinutes(cacheMinutes),
System.Web.Caching.Cache.NoSlidingExpiration);
}
return listTable;
} private static void InitDataForTable(int index, ref List<CustomReportTable> listTable)
{
List<CustomReportColumn> listColumn;
switch (index)
{
case :
#region Latest Information of Line Item
#region Basic Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Internal ID", DataBaseColumnName = "Internal_ID", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Qty Balance", DataBaseColumnName = "Current_Qty_Balance", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Unit of Qty", DataBaseColumnName = "Unit_of_Qty", ColumnType = ColumnType.Number });
listTable.Add(new CustomReportTable()
{
PageTableName = "Basic Info",
DataBaseTableName = "View_CustomReport_BasicInfo",
ListColumn = listColumn
});
#endregion #region GF272 Sheet Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "GF272 Sheet Info",
DataBaseTableName = "View_CustomReport_GF272SheetInfo",
ListColumn = listColumn
});
#endregion
#endregion
break;
case :
#region Individual Item
#region Line Item Information
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Department", DataBaseColumnName = "Holder_Department", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Group", DataBaseColumnName = "Holder_Group", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Section", DataBaseColumnName = "Holder_Section", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder Unit", DataBaseColumnName = "Holder_Unit", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Brand", DataBaseColumnName = "Brand", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Model", DataBaseColumnName = "Model", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Category", DataBaseColumnName = "Category", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-category", DataBaseColumnName = "Sub____category", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Classification", DataBaseColumnName = "Classification", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Sub-classification", DataBaseColumnName = "Sub____classification", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "GF272 Description", DataBaseColumnName = "GF272_Description", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Inventory Item Flag", DataBaseColumnName = "Inventory_Item_Flag", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Unit Ref No.", DataBaseColumnName = "Last_GF272_Valid_Unit_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Sheet No.", DataBaseColumnName = "Last_GF272_Valid_Sheet_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Line No.", DataBaseColumnName = "Last_GF272_Valid_Line_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Column No.", DataBaseColumnName = "Last_GF272_Valid_Column_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last GF272 Valid Commodity Code/Reference No.", DataBaseColumnName = "Last_GF272_Valid_Commodity_Code__Reference_No000", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Line Item Information",
DataBaseTableName = "View_CustomReport_LineItemInformation",
ListColumn = listColumn
});
#endregion #region Individual Item Basic Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Serial No.", DataBaseColumnName = "Serial_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No.", DataBaseColumnName = "IMS_Barcode_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Self Assign ID", DataBaseColumnName = "Self_Assign_ID", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Detailed Description", DataBaseColumnName = "Detailed_Description", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Status", DataBaseColumnName = "Item_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks", DataBaseColumnName = "Remarks", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks2", DataBaseColumnName = "Remarks2", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks3", DataBaseColumnName = "Remarks3", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks4", DataBaseColumnName = "Remarks4", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Individual Item Basic Info",
DataBaseTableName = "View_CustomReport_IndividualItemBasicInfo",
ListColumn = listColumn
});
#endregion #region Location
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Headquarter Flag", DataBaseColumnName = "Headquarter_Flag", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Location District", DataBaseColumnName = "Location_District", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Building", DataBaseColumnName = "Location_Building", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Floor", DataBaseColumnName = "Location_Floor", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Room", DataBaseColumnName = "Location_Room", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Location Area", DataBaseColumnName = "Location_Area", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Location",
DataBaseTableName = "View_CustomReport_Location",
ListColumn = listColumn
});
#endregion #region Physical Check Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked Date", DataBaseColumnName = "Last_Physical_Checked_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Physical Checked By", DataBaseColumnName = "Last_Physical_Checked_By", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Last Check Status", DataBaseColumnName = "Last_Check_Status", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Physical Check Info",
DataBaseTableName = "View_CustomReport_PhysicalCheckInfo",
ListColumn = listColumn
});
#endregion #region Aggregate
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No. of Aggregate Parent", DataBaseColumnName = "IMS_Barcode_No000_of_Aggregate_Parent", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Aggregate",
DataBaseTableName = "View_CustomReport_Aggregate",
ListColumn = listColumn
});
#endregion #region Purchase Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PR Ref No.", DataBaseColumnName = "Purchase_PR_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase ICAC Quotation Ref No.", DataBaseColumnName = "Purchase_ICAC_Quotation_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Mode", DataBaseColumnName = "Purchase_Mode", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase PO No./ Ref", DataBaseColumnName = "Purchase_PO_No000___Ref", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Invoice No.", DataBaseColumnName = "Purchase_Invoice_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Order", DataBaseColumnName = "Purchase_Date_of_Order", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Receipt", DataBaseColumnName = "Purchase_Date_of_Receipt", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Date of Acceptance", DataBaseColumnName = "Purchase_Date_of_Acceptance", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Free Warranty Period", DataBaseColumnName = "Free_Warranty_Period", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Holder File Reference", DataBaseColumnName = "Holder_File_Reference", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person Position", DataBaseColumnName = "Contact_Person_Position", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher Type", DataBaseColumnName = "Purchase_Related_Voucher_Type", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Purchase Related Voucher No.", DataBaseColumnName = "Purchase_Related_Voucher_No000", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Purchase Info",
DataBaseTableName = "View_CustomReport_PurchaseInfo",
ListColumn = listColumn
});
#endregion #region Loan Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Loan Status", DataBaseColumnName = "Current_Loan_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Staff Name of Borrower", DataBaseColumnName = "Staff_Name_of_Borrower", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Department", DataBaseColumnName = "Borrower111s_Department", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Group", DataBaseColumnName = "Borrower111s_Group", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Borrower's Section", DataBaseColumnName = "Borrower111s_Section", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Loan Info",
DataBaseTableName = "View_CustomReport_LoanInfo",
ListColumn = listColumn
});
#endregion #region Issue Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Issue Status", DataBaseColumnName = "Current_Issue_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Dept", DataBaseColumnName = "Issue_Dept", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Group", DataBaseColumnName = "Issue_Group", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Section", DataBaseColumnName = "Issue_Section", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Issue Unit", DataBaseColumnName = "Issue_Unit", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Issue Info",
DataBaseTableName = "View_CustomReport_IssueInfo",
ListColumn = listColumn
});
#endregion #region End-user info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Item End-user", DataBaseColumnName = "Current_Item_End____user", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Usage Date", DataBaseColumnName = "Usage_Date", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "End-user info",
DataBaseTableName = "View_CustomReport_EndUserInfo",
ListColumn = listColumn
});
#endregion #region Surplus Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Status", DataBaseColumnName = "Surplus_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date From", DataBaseColumnName = "Surplus_Post_Out_Date_From", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Surplus Post Out Date To", DataBaseColumnName = "Surplus_Post_Out_Date_To", ColumnType = ColumnType.DateTime });
listTable.Add(new CustomReportTable()
{
PageTableName = "Surplus Info",
DataBaseTableName = "View_CustomReport_SurplusInfo",
ListColumn = listColumn
});
#endregion #region Disposal Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Anticipated Disposal Date", DataBaseColumnName = "Anticipated_Disposal_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Status", DataBaseColumnName = "Disposal_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Dispose Job No.", DataBaseColumnName = "Dispose_Job_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Date of Disposal", DataBaseColumnName = "Date_of_Disposal", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Condition of Item in Disposal", DataBaseColumnName = "Condition_of_Item_in_Disposal", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Method", DataBaseColumnName = "Disposal_Method", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Disposal Related Voucher No.", DataBaseColumnName = "Disposal_Related_Voucher_No000", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Disposal Info",
DataBaseTableName = "View_CustomReport_DisposalInfo",
ListColumn = listColumn
});
#endregion #region Repair Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "On Repair Status", DataBaseColumnName = "On_Repair_Status", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Repair Info",
DataBaseTableName = "View_CustomReport_RepairInfo",
ListColumn = listColumn
});
#endregion #region Replace Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Replaced Status", DataBaseColumnName = "Replaced_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Barcode No of Replacing Item", DataBaseColumnName = "IMS_Barcode_No_of_Replacing_Item", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Voucher No for Replaced Item", DataBaseColumnName = "Voucher_No_for_Replaced_Item", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Replace Info",
DataBaseTableName = "View_CustomReport_ReplaceInfo",
ListColumn = listColumn
});
#endregion #region Delegate Maint User Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Delegated to others for Maintenance", DataBaseColumnName = "Delegated_to_others_for_Maintenance", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Dept", DataBaseColumnName = "Maintenance_Dept", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Group", DataBaseColumnName = "Maintenance_Group", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Section", DataBaseColumnName = "Maintenance_Section", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit", DataBaseColumnName = "Maintenance_Unit", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Delegate Maint User Info",
DataBaseTableName = "View_CustomReport_DelegateMaintUserInfo",
ListColumn = listColumn
});
#endregion #region Maintenance Basic
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Maintenance Basic",
DataBaseTableName = "View_CustomReport_MaintenanceBasic",
ListColumn = listColumn
});
#endregion #region Current Maintenance Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Contract User Reference No.", DataBaseColumnName = "Current_Maintenance_Contract_User_Reference_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Vendor", DataBaseColumnName = "Current_Maintenance_Vendor", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Unit Cost", DataBaseColumnName = "Current_Maintenance_Unit_Cost", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage Start Date", DataBaseColumnName = "Current_Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Coverage End Date", DataBaseColumnName = "Current_Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Current Maintenance Level", DataBaseColumnName = "Current_Maintenance_Level", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Next Maintenance BU Date", DataBaseColumnName = "Next_Maintenance_BU_Date", ColumnType = ColumnType.DateTime });
listTable.Add(new CustomReportTable()
{
PageTableName = "Current Maintenance Info",
DataBaseTableName = "View_CustomReport_CurrentMaintenanceInfo",
ListColumn = listColumn
});
#endregion
#endregion
break;
case :
#region Maintenance Contract
#region Contract Header
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract User Reference No.", DataBaseColumnName = "Contract_User_Reference_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Name", DataBaseColumnName = "Contract_Name", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract Start Date", DataBaseColumnName = "Contract_Start_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contract End Date", DataBaseColumnName = "Contract_End_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Person", DataBaseColumnName = "Contact_Person", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Contact Post", DataBaseColumnName = "Contact_Post", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "BU Date", DataBaseColumnName = "BU_Date", ColumnType = ColumnType.DateTime });
listTable.Add(new CustomReportTable()
{
PageTableName = "Contract Header",
DataBaseTableName = "View_CustomReport_ContractHeader",
ListColumn = listColumn
});
#endregion #region Item’s Vendor Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor", DataBaseColumnName = "Maint_Vendor", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person", DataBaseColumnName = "Maint_Vendor_Contact_Person", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Contact Person Position", DataBaseColumnName = "Maint_Vendor_Contact_Person_Position", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Vendor Quotation No.", DataBaseColumnName = "Maint_Vendor_Quotation_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint Po No./Ref", DataBaseColumnName = "Maint_Po_No000__Ref", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Schedule", DataBaseColumnName = "Payment_Schedule", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Date", DataBaseColumnName = "Payment_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Paid Amount", DataBaseColumnName = "Paid_Amount", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Payment Status", DataBaseColumnName = "Payment_Status", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Invoice No.", DataBaseColumnName = "Invoice_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "File Ref No.", DataBaseColumnName = "File_Ref_No000", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Item’s Vendor Info",
DataBaseTableName = "View_CustomReport_ItemsVendorInfo",
ListColumn = listColumn
});
#endregion #region Item’s Maintenance Basic Info
listColumn = new List<CustomReportColumn>();
listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Serial No.", DataBaseColumnName = "Item_Serial_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "IMS Item Barcode No.", DataBaseColumnName = "IMS_Item_Barcode_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Grouping", DataBaseColumnName = "Maintenance_Grouping", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Vote", DataBaseColumnName = "Maintenance_Vote", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Remarks for Maintenance", DataBaseColumnName = "Remarks_for_Maintenance", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint. Grouping", DataBaseColumnName = "Maint000_Grouping", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "GLD Contract No.", DataBaseColumnName = "GLD_Contract_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Needed to be Maintenance Afterwards", DataBaseColumnName = "Needed_to_be_Maintenance_Afterwards", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maint PR Ref No.", DataBaseColumnName = "Maint_PR_Ref_No000", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Cost", DataBaseColumnName = "Maintenance_Unit_Cost", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Unit Actual Cost", DataBaseColumnName = "Maintenance_Unit_Actual_Cost", ColumnType = ColumnType.Number });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage Start Date", DataBaseColumnName = "Maintenance_Coverage_Start_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Coverage End Date", DataBaseColumnName = "Maintenance_Coverage_End_Date", ColumnType = ColumnType.DateTime });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Maintenance Level", DataBaseColumnName = "Maintenance_Level", ColumnType = ColumnType.String });
listColumn.Add(new CustomReportColumn() { PageColumnName = "Item Remarks in this Contract", DataBaseColumnName = "Item_Remarks_in_this_Contract", ColumnType = ColumnType.String });
listTable.Add(new CustomReportTable()
{
PageTableName = "Item’s Maintenance Basic Info",
DataBaseTableName = "View_CustomReport_ItemsMaintenanceBasicInfo",
ListColumn = listColumn
});
#endregion
#endregion
break;
}
} public static DataTable GetCustomReport(string sql)
{
DataTable dt = SqlHelper.ExecuteDataTable(sql);
if (dt != null)
{
for (int i = , len = dt.Columns.Count; i < len; i++)
dt.Columns[i].ColumnName = ConvertColumnName(dt.Columns[i].ColumnName);
}
return dt;
} public static string ConvertColumnName(string columnName)
{
string convertColumnName = "";
if (!string.IsNullOrEmpty(columnName))
convertColumnName = columnName.Replace("____", "-").Replace("", ".").Replace("__", "/").Replace("_", " ").Replace("", "'");
return convertColumnName;
}
}
}
SearchCustomReport.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SearchCustomReport.aspx.cs"
Inherits="IMSWeb.App.IS.Report.SearchCustomReport" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset class="pageFieldset" style="width: 800px">
<legend class="segmentTitle">Custom Report Search</legend>
<table class="tblDetail">
<tr style="display:none">
<td>
Issued Item
</td>
<td colspan="">
<asp:DropDownList ID="ddlIssuedItem" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlIssuedItem_SelectedIndexChanged"
Width="160px">
<asp:ListItem Value="">--ALL--</asp:ListItem>
<asp:ListItem Value="">--Show--</asp:ListItem>
<asp:ListItem Value="">--Don't Show--</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td style=" white-space:200px;">
Inventory Holder
</td>
<td colspan="">
<asp:DropDownList ID="ddlHolder" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Table Set
</td>
<td>
<asp:DropDownList ID="ddlTableSet" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTableSet_SelectedIndexChanged"
Width="160px">
</asp:DropDownList>
</td>
<td style="width: 80px;">
Item Group
</td>
<td>
<asp:DropDownList ID="ddlTable" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlTable_SelectedIndexChanged"
Width="160px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Item Name
</td>
<td colspan="">
<table cellpadding="" cellspacing="" style="width: 100%;">
<tr>
<td>
Brand
</td>
<td rowspan="" class="style1">
<asp:Button ID="btnSelectAll" runat="server" Text=">>" OnClick="btnSelectAll_Click" />
<p />
<asp:Button ID="btnSelect" runat="server" Text=">" OnClick="btnSelect_Click" />
<p />
<p />
<asp:Button ID="btnUnSelect" runat="server" Text="<" OnClick="btnUnSelect_Click" />
<p />
<asp:Button ID="btnUnSelectAll" runat="server" Text="<<" OnClick="btnUnSelectAll_Click" />
</td>
<td>
Model
</td>
</tr>
<tr>
<td style="padding-left: 0; width: 244px;">
<asp:ListBox ID="lstAvailableField" runat="server" Width="160px" Rows=""></asp:ListBox>
</td>
<td style="padding-left: 0">
<asp:ListBox ID="lstSelectedField" runat="server" Width="160px" Rows=""></asp:ListBox>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
Enter Criteria
</td>
<td colspan="">
<asp:DropDownList ID="ddlAvailableFieldEnter" runat="server" Width="160px" AutoPostBack="true"
OnSelectedIndexChanged="ddlAvailableFieldEnter_SelectedIndexChanged">
</asp:DropDownList>
<asp:DropDownList ID="ddlOperation" runat="server" Width="50px">
<asp:ListItem Value="="></asp:ListItem>
<asp:ListItem Value=">"></asp:ListItem>
<asp:ListItem Value="<"></asp:ListItem>
<%--<asp:ListItem Value="%like%"></asp:ListItem>
<asp:ListItem Value="%like"></asp:ListItem>
<asp:ListItem Value="like%"></asp:ListItem>--%>
</asp:DropDownList>
<asp:TextBox ID="txtValueEnter" runat="server" Width="228px"></asp:TextBox>
<asp:Button ID="btnAddCriteria" runat="server" Text="Add" OnClick="btnAddCriteria_Click" />
<asp:DropDownList ID="ddlJoin" runat="server" Width="50px" Visible="false">
<asp:ListItem Value="And"></asp:ListItem>
<asp:ListItem Value="Or"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Search Criteria
</td>
<td colspan="">
<asp:TextBox ID="txtSearchCriteria" runat="server" Width="469px" Height="100px" ReadOnly="true"
TextMode="MultiLine"></asp:TextBox>
<asp:TextBox ID="txtSearchCriteriaVal" runat="server" ReadOnly="true" Style="display:none"></asp:TextBox>
<asp:Button ID="btnResetSearchCriteria" runat="server" Text="Reset" OnClick="btnResetSearchCriteria_Click" />
</td>
</tr>
</table>
<div class="block">
<asp:Button ID="btnPreview" runat="server" Text="Preview" ValidationGroup="lose"
OnClick="btnPreview_Click" />
</div>
</fieldset>
</div>
</form>
</body>
</html>
SearchCustomReport.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls; using Bll;
using System.Text;
using Bll.Report; namespace IMSWeb.App.IS.Report
{
/// <summary>
/// add by Kenmu at 2013-04-28
/// </summary>
public partial class SearchCustomReport : BasePage
{
public const string joinStr = "\r\n"; #region Property #endregion #region Event
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
InitData();
} protected void ddlIssuedItem_SelectedIndexChanged(object sender, EventArgs e)
{
} protected void ddlTableSet_SelectedIndexChanged(object sender, EventArgs e)
{
InitTable();
} protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
{
InitColumn();
} protected void btnSelect_Click(object sender, EventArgs e)
{
SelectItem();
ControlPreview();
} protected void btnUnSelect_Click(object sender, EventArgs e)
{
UnSelectItem();
ControlPreview();
} protected void btnSelectAll_Click(object sender, EventArgs e)
{
SelectAllItem();
ControlPreview();
} protected void btnUnSelectAll_Click(object sender, EventArgs e)
{
UnSelectAllItem();
ControlPreview();
} protected void ddlAvailableFieldEnter_SelectedIndexChanged(object sender, EventArgs e)
{
ControlValueEnter();
} protected void btnAddCriteria_Click(object sender, EventArgs e)
{
AddCriteria();
} protected void btnResetSearchCriteria_Click(object sender, EventArgs e)
{
ResetSearchCriteria();
} protected void btnPreview_Click(object sender, EventArgs e)
{
string url = string.Format("CustomReport.aspx?Sql={0}&t=", Server.UrlEncode(FinallySql().Replace(joinStr, ddlJoin.SelectedValue)), DateTime.Now.ToString("yyyyMMddHHmmssms"));
this.RegisterJS(string.Format("window.open('{0}');", url));
}
#endregion #region Method
private void InitData()
{
bool isSo = App_Code.SessionUtil.IsSORole;
is_inventory_holding_unit bllHoldingUnit = new is_inventory_holding_unit();
Bll.Common.Report.InitDropDownList(ddlHolder, true, null, bllHoldingUnit.GetAvailableISInventoryHolder(), "holding_name", "holding_id");
if (App_Code.SessionUtil.IsHolding)
this.ddlHolder.SelectedValue = CurrentHoldingId.ToString();
if (!isSo)
{
ddlHolder.Enabled = false;
ddlHolder.SelectedValue = CurrentHoldingId.ToString();
} InitTableSet(isSo);
} private void InitTableSet(bool isSORole)
{
string[] tableSetNameArr = Bll.Report.CustomReport.tableSetNameArr;
int lastIndex = tableSetNameArr.Length - ;
if (!isSORole)
{
ddlTableSet.Items.Add(new ListItem(tableSetNameArr[lastIndex], lastIndex.ToString()));
}
else
{
for (int i = ; i <= lastIndex; i++)
ddlTableSet.Items.Add(new ListItem(tableSetNameArr[i], i.ToString()));
}
ddlTableSet.SelectedIndex = ;
InitTable();
} private void InitTable()
{
List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), );
Bll.Common.Report.InitDropDownList(ddlTable, false, null, listTable, "PageTableName", "DataBaseTableName");
ddlTable.SelectedIndex = ;
InitColumn();
} private CustomReportTable CurrentReportTable()
{
List<CustomReportTable> listTable = Bll.Report.CustomReport.ListTable(int.Parse(ddlTableSet.SelectedValue), );
CustomReportTable entity = (from table in listTable
where table.DataBaseTableName == ddlTable.SelectedValue
select table).FirstOrDefault<CustomReportTable>();
return entity;
} private void InitColumn()
{
List<CustomReportColumn> listColumn = CurrentReportTable().ListColumn;
Bll.Common.Report.InitListBox(lstAvailableField, false, null, listColumn, "PageColumnName", "DataBaseColumnName");
Bll.Common.Report.InitDropDownList(ddlAvailableFieldEnter, false, null, listColumn, "PageColumnName", "DataBaseColumnName");
lstAvailableField.SelectedIndex = ;
ddlAvailableFieldEnter.SelectedIndex = ;
lstSelectedField.Items.Clear();
ResetSearchCriteria();
ControlValueEnter();
ControlPreview();
} private void SelectItem()
{
if (lstAvailableField.SelectedIndex != -)
{
int currentIndex = lstAvailableField.SelectedIndex;
ListItem li = new ListItem(lstAvailableField.SelectedItem.Text, lstAvailableField.SelectedItem.Value);
lstSelectedField.Items.Add(li);
lstAvailableField.Items.Remove(li); int count = lstAvailableField.Items.Count;
if (count > )
lstAvailableField.SelectedIndex = count > currentIndex ? currentIndex : count - ;
}
} private void UnSelectItem()
{
if (lstSelectedField.SelectedIndex != -)
{
int currentIndex = lstSelectedField.SelectedIndex;
ListItem li = new ListItem(lstSelectedField.SelectedItem.Text, lstSelectedField.SelectedItem.Value);
lstAvailableField.Items.Add(li);
lstSelectedField.Items.Remove(li); int count = lstSelectedField.Items.Count;
if (count > )
lstSelectedField.SelectedIndex = count > currentIndex ? currentIndex : count - ;
}
} private void SelectAllItem()
{
int count = lstAvailableField.Items.Count;
if (count > )
{
foreach (ListItem li in lstAvailableField.Items)
lstSelectedField.Items.Add(li);
lstAvailableField.Items.Clear();
}
} private void UnSelectAllItem()
{
int count = lstSelectedField.Items.Count;
if (count > )
{
foreach (ListItem li in lstSelectedField.Items)
lstAvailableField.Items.Add(li);
lstSelectedField.Items.Clear();
}
} private void AddCriteria()
{
bool isLike = ddlOperation.SelectedValue.IndexOf("like") != -;
bool isAddSingleQuotes = IsAddSingleQuotes();
string val = txtValueEnter.Text;
if (isLike)
{
val = string.Format("'{0}'", ddlOperation.SelectedValue.Replace("like", txtValueEnter.Text));
}
else if (isAddSingleQuotes)
{
val = string.Format("'{0}'", txtValueEnter.Text);
} string criteriaVal = string.Format("{0}{1}{2}",
ddlAvailableFieldEnter.SelectedItem.Value,
isLike ? " like " : ddlOperation.SelectedValue,
val);
string criteriaText = string.Format("{0}{1}{2}",
ddlAvailableFieldEnter.SelectedItem.Text,
isLike ? " like " : ddlOperation.SelectedValue,
val); if (txtSearchCriteriaVal.Text.IndexOf(criteriaVal) == -)
{
if (isLike || isAddSingleQuotes || txtValueEnter.Text.Trim() != "")
{
criteriaVal = string.Format("{0} {1}", txtSearchCriteriaVal.Text != "" ? " " + joinStr : "", criteriaVal);
txtSearchCriteriaVal.Text += criteriaVal; criteriaText = string.Format("{0} {1}", txtSearchCriteria.Text != "" ? " " + joinStr : "", criteriaText);
txtSearchCriteria.Text += criteriaText;
}
}
} private string FinallySql()
{
StringBuilder sb = new StringBuilder("select ");
foreach (ListItem li in lstSelectedField.Items)
sb.AppendFormat("{0},", li.Value);
sb.Remove(sb.Length - , );
string dataBaseTableName = ddlTable.SelectedValue;
sb.AppendFormat(" from {0}", dataBaseTableName);
string criteria = txtSearchCriteriaVal.Text;
if (criteria != "")
sb.AppendFormat(" where {0}", criteria);
string holdingId = ddlHolder.SelectedValue;
if (holdingId != "")
{
sb.AppendFormat(" {0} Holding_Id={1}",
sb.ToString().IndexOf("where") != - ? "and" : "where",
holdingId);
}
if (Bll.Report.CustomReport.tableHaveUserId.IndexOf(dataBaseTableName) != -)
{
sb.AppendFormat(" {0} User_Id={1}",
sb.ToString().IndexOf("where") != - ? "and" : "where",
CurrentUserId);
}
return sb.ToString();
} private void ControlValueEnter()
{
txtValueEnter.Text = "";
txtValueEnter.Attributes["onclick"] = "return true;";
txtValueEnter.CssClass = "textBox";
txtValueEnter.Attributes["onkeyup"] = "return true;";
txtValueEnter.Attributes["onafterpaste"] = "return true;"; ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;
switch (ct)
{
case ColumnType.Number:
string jsStr = "this.value=this.value.replace(/[^0-9|^\\-|^\\.]/g,\'\');";
txtValueEnter.Attributes["onkeyup"] = jsStr;
txtValueEnter.Attributes["onafterpaste"] = jsStr;
break;
case ColumnType.DateTime:
txtValueEnter.Attributes["onclick"] = "WdatePicker({dateFmt:'MM-dd-yyyy'})";
txtValueEnter.CssClass = "Wdate";
break;
default:
break;
}
} private bool IsAddSingleQuotes()
{
ColumnType ct = CurrentReportTable().ListColumn[ddlAvailableFieldEnter.SelectedIndex].ColumnType;
return ct != ColumnType.Number;
} private void ResetSearchCriteria()
{
txtSearchCriteria.Text = "";
txtSearchCriteriaVal.Text = "";
} private void ControlPreview()
{
btnPreview.Enabled = lstSelectedField.Items.Count > ;
}
#endregion
}
}
CustomReport.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomReport.aspx.cs" Inherits="IMSWeb.App.IS.Report.CustomReport" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset>
<legend class="segmentTitle">Custom Report List</legend>
<div style="margin-left: 10px; margin-bottom: 20px">
<asp:GridView runat="server" ID="gvCustomReportList" AllowPaging="True" PageSize=""
OnPageIndexChanging="gvCustomReportList_PageIndexChanging">
</asp:GridView>
<p />
<asp:Button runat="server" Width="100px" ID="btnExportCSV" Text="Export Report" OnClick="btnExportCSV_Click" />
<asp:Button runat="server" Width="100px" ID="btnPrint" Text="Print" />
</div>
</fieldset>
</div>
</form>
</body>
</html>
CustomReport.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls; using System.Data;
using System.Text; namespace IMSWeb.App.IS.Report
{
/// <summary>
/// add by Kenmu at 2013-05-07
/// </summary>
public partial class CustomReport : BasePage
{
#region Event
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
InitData();
} protected void gvCustomReportList_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvCustomReportList.PageIndex = e.NewPageIndex;
InitData();
} protected void btnExportCSV_Click(object sender, EventArgs e)
{
try
{
string sql = Server.UrlDecode(Request["Sql"]);
DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql);
StringBuilder sbHeader = new StringBuilder();
StringBuilder sbContent = new StringBuilder();
DateTime tempDateTime = DateTime.MinValue;
string tempVal = ""; for (int i = , len = dt.Rows.Count; i < len; i++)
{
for (int j = , len2 = dt.Columns.Count; j < len2; j++)
{
if (i == )
{
sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName);
} tempVal = dt.Rows[i][j].ToString();
if(DateTime.TryParse(tempVal,out tempDateTime))
tempVal = tempDateTime.ToString("dd-MM-yyyy"); sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal));
}
sbContent.Remove(sbContent.Length - , );
sbContent.AppendLine();
}
sbHeader.Remove(sbHeader.Length - , );
sbHeader.AppendLine(); IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response,
string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")),
sbHeader.ToString() + sbContent.ToString());
}
catch (Exception ex)
{
IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId);
this.ShowErrorMsg(ex);
}
}
#endregion #region Method
private void InitData()
{
string sql = Server.UrlDecode(Request["Sql"]);
DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql);
int count = dt.Rows.Count;
if (count == )
{
this.RegisterJS("alert('Without relevant data!');window.opener=null;window.open('','_self');window.close();");
return;
}
gvCustomReportList.DataSource = dt;
DynamicAddColumn(dt);
gvCustomReportList.DataBind();
} private void DynamicAddColumn(DataTable dt)
{
gvCustomReportList.Columns.Clear();
for (int i = , len = dt.Columns.Count; i < len; i++)
AddColumn(dt.Columns[i].ColumnName, dt.Columns[i].DataType.ToString());
} private void AddColumn(string columnName, string type)
{
BoundField bf = new BoundField();
bf.DataField = columnName;
bf.HeaderText = columnName;
switch (type)
{
case "System.DateTime"://日期类型
bf.DataFormatString = "{0:dd-MM-yyyy}";
break;
//case "System.String"://字符串类型
// break;
//case "System.Boolean"://布尔型
// break;
//case "System.Int16"://整型
//case "System.Int32":
//case "System.Int64":
//case "System.Byte":
// break;
//case "System.Decimal"://浮点型
//case "System.Double":
// break;
//case "System.DBNull"://Null值处理
// break;
default:
break;
}
gvCustomReportList.Columns.Add(bf);
}
#endregion
}
}