Thiết kế và triển khai kho dữ liệu khách hàng sử dụng dịch vụ viễn thông của Tổng công ty Bưu chinh Viễn thông Việt Nam (VNPT)

um(tientru) tientru FROM wh_ccbs.ct_cuoc_kh a group by a.thang, a.gio_bd, a.donvi_ql; Thống kê cuộc gọi theo dịch vụ Bảng tổng hợp này được sử dụng để phân tích cuộc gọi theo dịch vụ. Bảng thống kê cuộc gọi theo dịch vụ. Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_thongke_cg_kieu_dv AS SELECT a.thang, a.kieu_dv, a.donvi_ql, COUNT(1) socuoc, SUM(a.tg_goi) tg_goi, SUM(a.tien) tien, SUM(a.vat) vat, SUM(tientru) tientru FROM wh_ccbs.ct_cuoc_kh a GROUP BY a.thang, a.kieu_dv, a.donvi_ql; Thống kê cuộc gọi theo mã vùng Bảng tổng hợp này được sử dụng để phân tích cuộc gọi theo mã vùng Bảng thống kê cuộc gọi theo mã vùng. Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_thongke_cg_mavung AS SELECT a.thang, a.ma_vung, a.donvi_ql, count(1) socuoc, sum(a.tg_goi) tg_goi, sum(a.tien) tien, sum(a.vat) vat, sum(tientru) tientru FROM wh_ccbs.ct_cuoc_kh a group by a.thang, a.ma_vung, a.donvi_ql; Thống kê doanh thu Bảng tổng hợp này được sử dụng để phân tích doanh thu theo dịch vụ, đối tượng, đơn vị quản lý, tháng. Bảng thống kê doanh thu Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_thongke_doanhthu AS SELECT thang, dichvu_vt, donvi_ql, a.doituong, sum(a.socuoc) socuoc, sum(a.sophut) sophut, sum(a.sogiay) sogiay, sum(a.tien) tien, sum(a.vat) vat, sum(tien+vat) tong FROM wh_ccbs.th_cuoc_kh a group by thang, dichvu_vt, donvi_ql, a.doituong; Thống kê tiền nợ cước Bảng tổng hợp này được sử dụng để phân tích tình hình nợ cước theo khoản mục, đơn vị quản lý, tháng. Bảng thống kê tiền nợ cước Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_thongke_tien_no AS Select thang, khoanmuc_cuoc, donvi_ql, sum(tien) tien, sum(thue) vat, sum(hoahong) hoahong, sum(tien+thue-hoahong) tong FROM wh_ccbs.ct_no_kh a Group by thang, khoanmuc_cuoc, donvi_ql; Thống kê tiền thanh toán Bảng tổng hợp này được sử dụng để phân tích tiền thanh toán nợ cước theo khoản mục, đơn vị quản lý, tháng, hình thức thanh toán. Bảng thống kê tiền thanh toán Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_thongke_tien_tt AS Select thang, khoanmuc_cuoc, hinhthuc_tt, donvi_ql, sum(tien) tien, sum(vat) vat, sum(hoahong) hoahong, sum(tien+vat-hoahong) tong FROM wh_ccbs.ct_ttno_kh a Group by thang, khoanmuc_cuoc, donvi_ql, hinhthuc_tt; Tổng hợp cước sử dụng khách hàng Bảng tổng hợp này được sử dụng để phân tích cước sử dụng của khách hàng. Bảng tổng hợp cước sử dụng khách hàng Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_tonghop_cuoc_sd_kh AS SELECT thang, khachhang, sum(a.socuoc) socuoc, sum(a.sophut) sophut, sum(a.sogiay) sogiay, sum(a.tien) tien, sum(a.vat) vat, sum(tien+vat) tong FROM wh_ccbs.th_cuoc_kh a group by thang, khachhang; Tổng hợp tiền nợ khách hàng Bảng tổng hợp này được sử dụng để phân tích tiền nợ của khách hàng. Bảng tổng hợp tiền nợ khách hàng Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_tonghop_tien_no_kh AS Select thang, khachhang, sum(tien) tien, sum(thue) vat, sum(hoahong) hoahong, sum(tien+thue-hoahong) tong FROM wh_ccbs.ct_no_kh a Group by thang, khachhang; Tổng hợp tiền thanh toán khách hàng Bảng tổng hợp này được sử dụng để phân tích tiền thanh toán của khách hàng. Bảng tổng hợp tiền thanh toán khách hàng Lệnh tạo bảng tổng hợp CREATE MATERIALIZED VIEW mv_tonghop_tien_tt_kh AS Select thang, khachhang, sum(tien) tien, sum(vat) vat, sum(hoahong) hoahong, sum(tien+vat-hoahong) tong FROM wh_ccbs.ct_ttno_kh a Group by thang, khachhang; 3.2.3.5. Phân vùng Các partiton đóng vai trò quan trọng trong các bảng khai thác với số liệu vô cùng lớn. Nó cho phép phân chia dữ liệu theo một số tiêu thức nào đó. Trong kho dữ liệu viễn thông, chúng ta thiết kế các partition dựa trên các trường về thời gian. Dữ liệu chi tiết cuộc gọi, cước sử dụng, tiền thanh toán,…của khách hàng là rất lớn và thường được xử lý trong từng tháng, nên chúng ta sẽ phân vùng dữ liệu các dữ liệu này theo tháng. Danh sách các bảng được phân vùng dữ liệu Tên bảng Mô tả Trường phân vùng CT_CUOC_KH Bảng sự kiện chi tiết cước khách hàng Tháng CT_NO_KH Bảng sự kiện chi tiết nợ khách hàng Tháng CT_TTNO_KH Bảng sự kiện chi tiết thanh toán nợ KH Tháng NHACNO Bảng sự kiện nhắc nợ Tháng TH_CUOC_KH Bảng sự kiện tổng hợp cước KH Tháng MV_TONGHOP_CUOC_SD_KH Bảng tổng hợp cước sử dụng khách hàng Tháng MV_TONGHOP_TIEN_NO_KH Bảng tổng hợp tiền nợ khách hàng Tháng MV_TONGHOP_TIEN_TT_KH Bảng tổng hợp tiền thanh toán khách hàng Tháng 3.2.3.6. Chỉ mục Thiết kế các chỉ mục (Index) làm tăng hiệu suất truy vấn dữ liệu. Chỉ nên đánh chỉ mục đối với các trường có sự khác nhau về dữ liệu nhiều. Sau đây liệt kê các bảng có thiết kế index. Tên bảng Mô tả Trường index KHACHHANG Khóa chính khachhang_id THANHTOAN Khóa chính thanhtoan_id THUEBAO Khóa chính thuebao_id CT_CUOC_KH Chiều khách hàng khachhang CT_CUOC_KH Chiều thanh toán thanhtoan CT_CUOC_KH Chiều thuê bao thuebao CT_NO_KH Chiều khách hàng khachhang CT_NO_KH Chiều thanh toán thanhtoan CT_TTNO_KH Chiều khách hàng khachhang CT_TTNO_KH Chiều thanh toán thanhtoan NHACNO Chiều khách hàng khachhang NHACNO Chiều thuê bao thuebao TH_CUOC_KH Chiều khách hàng khachhang TH_CUOC_KH Chiều thanh toán thanhtoan MV_TONGHOP_CUOC_SD_KH Chiều khách hàng khachhang MV_TONGHOP_TIEN_NO_KH Chiều khách hàng khachhang MV_TONGHOP_TIEN_TT_KH Chiều khách hàng khachhang 3.2.4. Thiết kế trích xuất chuyển đổi nạp dữ liệu 3.2.4.1. Mô tả chung Hệ quản trị cơ sở dữ liệu hệ thống tính cước và chăm sóc khác hàng là oracle, gồm nhiều tài khoản dữ liệu như: Dữ liệu phát triển thuê bao bao gồm 2 user là admim, css; Dữ liệu cước khách hàng bao gồm 2 user tinhcuoc, tc_data; Dữ liệu quản lý thu nợ qln_common, qln_data; Dữ liệu giải quyết khiếu nại là user data gqkn; Dữ liệu báo hỏng 119 là user bh119,…Trong đề tài này sẽ sử dụng công cụ warehouser builder của oracle để thiết kế các ánh xạ dữ liệu từ hệ thống nguồn vào kho dữ liệu. Các bước thực hiện: Tạo các kết nối vào cở sở dữ liệu nguồn, xây dựng các mapping dữ liệu, triển khai mapping, chạy mapping. Một số ánh xạ dữ liệu được chạy theo lịch định sẵn. 3.2.4.2. Ánh xạ cho các chiều Chiều sản phẩm (dịch vụ viễn thông) Chiều sản phẩm được lấy từ hai bảng DICHVU_VT và LOAIHINH_TB của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ DICHVU_VT_MAP cho chiều sản phẩm như sau: Sơ đồ ánh xạ DICHVU_VT_MAP Lệnh ánh xạ dữ liệu chiều sản phẩm: INSERT INTO DICHVU_VT ( DICHVUVT_ID, TEN_DVVT, MA_DVVT, MA_LHTB, LOAIHINH_TB, LOAITB_ID ) SELECT DICHVU_VT.DICHVUVT_ID DICHVUVT_ID, DICHVU_VT.TEN_DVVT TEN_DVVT, DICHVU_VT.MA_DVVT MA_DVVT, LOAIHINH_TB.MA_LHTB MA_LHTB, LOAIHINH_TB.LOAIHINH_TB LOAIHINH_TB, LOAIHINH_TB.LOAITB_ID LOAITB_ID FROM CSS.DICHVU_VT DICHVU_VT, CSS.LOAIHINH_TB LOAIHINH_TB WHERE (LOAIHINH_TB.DICHVUVT_ID = DICHVU_VT.DICHVUVT_ID); Chiều địa chỉ Chiều địa chỉ được lấy từ các bảng TINH, QUAN, PHUONG của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ DIACHI_MAP cho chiều địa chỉ như sau: Sơ đồ ánh xạ DIACHI_MAP Lệnh ánh xạ dữ liệu chiều địa chỉ: INSERT INTO DIACHI( MA_TINH, TINH_ID, TEN_TINH, TEN_HUYEN, QUAN_ID, MA_QUAN, TEN_PHUONG, PHUONG_ID,MA_PHUONG ) SELECT TINH.MA_TINH MA_TINH, TINH.TINH_ID TINH_ID, TINH.TEN_TINH TEN_TINH, QUAN.TEN_QUAN TEN_QUAN, QUAN.QUAN_ID QUAN_ID, QUAN.MA_QUAN MA_QUAN, PHUONG.TEN_PHUONG TEN_PHUONG, PHUONG.PHUONG_ID PHUONG_ID, PHUONG.MA_PHUONG MA_PHUONG FROM CSS.TINH TINH, CSS.QUAN QUAN, CSS.PHUONG PHUONG WHERE (TINH.TINH_ID = QUAN.TINH_ID ) AND (QUAN.QUAN_ID = PHUONG.QUAN_ID); Chiều kiểu yêu cầu Chiều kiểu yêu cầu được lấy từ bảng LOAI_HD, KIEU_LD của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ KIEU_YC_MAP cho chiều kiểu yêu cầu như sau: Sơ đồ ánh xạ KIEU_YC_MAP Chiều khách hàng Chiều khách hàng được lấy từ bảng DB_KHACHHANG của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ KHACHHANG_MAP cho chiều khách hàng như sau: Sơ đồ ánh xạ KHACHHANG_MAP Chiều thanh toán Chiều thanh toán được lấy từ bảng DB_THANHTOAN của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ THANHTOAN_MAP cho chiều thanh toán như sau: Sơ đồ ánh xạ THANHTOAN_MAP Chiều thuê bao Chiều thuê bao được lấy từ bảng DB_THUEBAO của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ THUEBAO_MAP cho chiều thuê bao như sau: Sơ đồ ánh xạ THUEBAO_MAP 3.2.4.3. Ánh xạ cho các bảng sự kiện Bảng sự kiện bán hàng (Đăng ký dịch vụ viễn thông) Bảng sự kiện bán hàng được lấy từ các bảng HD_KHACHHANG, HD_THUEBAO, DIACHI_HDTB, DIACHI, CT_TIENHD của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ DANGKY_DVVT_MAP cho bảng sự kiện bán hàng như sau: Sơ đồ ánh xạ DANGKY_DVVT_MAP Bảng sự kiện phát triển thuê bao Bảng sự kiện phát triển thuê bao được lấy từ các bảng HD_KHACHHANG, HD_THUEBAO, DIACHI_HDTB, DIACHI của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ PHATTRIEN_TB_MAP cho bảng sự kiện phát triển thuê bao như sau: Sơ đồ ánh xạ PHATTRIEN_TB_MAP Bảng sự kiện điều hành thi công Bảng sự kiện điều hành thi công được lấy từ các bảng GIAOPHIEU, HD_THUEBAO của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ DIEUHANH_TC_MAP cho bảng sự kiện điều hành thi công như sau: Sơ đồ ánh xạ DIEUHANH_TC_MAP Bảng sự kiện khiếu nại Bảng sự kiện khiếu nại lấy từ các bảng KHIEUNAI, CT_KHIEUNAI của dữ liệu khiếu nại (user qgkn), DB_THUEBAO (user css). Sơ đồ ánh xạ KHIEUNAI_MAP cho bảng sự kiện khiếu nại như sau: Sơ đồ ánh xạ KHIEUNAI_MAP Bảng sự kiện xử lý khiếu nại Bảng sự kiện xử lý khiếu nại lấy từ các bảng KHIEUNAI, CT_KHIEUNAI, của dữ liệu xử lý khiếu nại (user qgkn), DB_THUEBAO( user css). Sơ đồ ánh xạ XL_KHIEUNAI_MAP cho bảng sự kiện xử lý khiếu nại như sau: Sơ đồ ánh xạ XL_KHIEUNAI_MAP Bảng sự kiện báo hỏng Bảng sự kiện báo hỏng lấy từ các bảng BAOHONG của dữ liệu báo hỏng (user bh119), DB_THUEBAO (user css). Sơ đồ ánh xạ BAOHONG_MAP cho bảng sự kiện báo hỏng như sau: Sơ đồ ánh xạ BAOHONG_MAP Bảng sự kiện xử lý báo hỏng Bảng sự kiện xử lý báo hỏng lấy từ các bảng BAOHONG của dữ liệu xử lý báo hỏng (user bh119), DB_THUEBAO (user css). Sơ đồ ánh xạ XL_BAOHONG_MAP cho bảng sự kiện xử lý báo hỏng như sau: Sơ đồ ánh xạ XL_BAOHONG_MAP Bảng sự kiện chi tiết cước khách hàng Bảng sự kiện chi tiết cước khách hàng lấy từ các bảng MV_CHITIET_CUOC của dữ liệu tính cước (user tinhcuoc). Sơ đồ ánh xạ CT_CUOC_KH_MAP cho bảng sự kiện chi tiết cước khách hàng như sau: Sơ đồ ánh xạ CT_CUOC_KH_MAP Bảng sự kiện tổng hợp cước khách hàng Bảng sự kiện tổng hợp cước khách hàng lấy từ các bảng MV_TONGHOP_CUOC của dữ liệu tính cước (user tinhcuoc). Sơ đồ ánh xạ TH_CUOC_KH_MAP cho bảng sự kiện tổng hợp cước khách hàng như sau: Sơ đồ ánh xạ TH_CUOC_KH_MAP Bảng sự kiện chi tiết tiền nợ khách hàng Bảng sự kiện chi tiết tiền nợ khách hàng lấy từ các bảng MV_NODAUKY của dữ liệu nợ đầu kỳ (user qln_data). Sơ đồ ánh xạ CT_NO_KH_MAP cho bảng sự kiện chi tiết tiền nợ khách hàng như sau: Sơ đồ ánh xạ CT_NO_KH_MAP Bảng sự kiện chi tiết tiền thanh toán khách hàng Bảng sự kiện chi tiết tiền thanh toán của khách hàng lấy từ các bảng MV_CHITIET_TT_KH của dữ liệu thanh toán (user qln_data). Sơ đồ ánh xạ CT_TTNO_KH_MAP cho bảng sự kiện chi tiết tiền thanh toán của khách hàng như sau: Sơ đồ ánh xạ CT_TTNO_KH_MAP Bảng sự kiện sử dụng dịch vụ viễn thông Bảng sự kiện sử dụng dịch vụ viễn thông được lấy từ các bảng DB_THUEBAO, DIACHI_TB, DIACHI của dữ liệu phát triển thuê bao (user css). Sơ đồ ánh xạ SUDUNG_DVVT_MAP cho bảng sự kiện sử dụng dịch vụ viễn thông như sau: Sơ đồ ánh xạ SUDUNG_DVVT_MAP Các câu lệnh script của các ánh xạ trên được mô tả chi tiết trong phụ lục 2 (Lệnh script của các ánh xạ trích xuất chuyển đổi và nạp dữ liệu sản sinh từ OWB) 3.2.5. Thiết kế công cụ báo cáo, tra cứu động Báo cáo, tra cứu là phần không thể thiếu trong các hệ thống kho dữ liệu. Việc xây dựng công cụ tạo báo cáo, tra cứu động và hệ thống báo cáo từ xa được xem xét để phát triển hệ thống báo cáo, tra cứu. Công cụ cho phép quản lý báo cáo, tra cứu đầu ra và cho phép người dùng bổ xung báo cáo, tra cứu vào hệ thống, mở rộng khả năng khai thác của người dùng và đáp ứng nhu cầu sử dụng lâu dài. 3.2.5.1. Mô hình báo cáo từ xa a. Mô hình truyền thống Mô hình truyền thống của hệ thống báo cáo được trình bày trên hình dưới. Trong mô hình này, một báo cáo được tạo ra từ thông tin định dạng báo cáo (chứa trong file định dạng) kết hợp với dữ liệu (từ cơ sở dữ liệu). Sự kết hợp và tạo ra báo cáo này được thực hiện bởi engine báo cáo. Đây là mô hình hoạt động phổ biến nhất của các giải pháp báo cáo hiện nay. Máy trạm CSDL Engine báo cáo Cấu trúc báo cáo Báo cáo Giao diện xem báo cáo Mô hình báo cáo truyền thống Đặc điểm của mô hình này là engine báo cáo, nơi phụ trách việc lấy dữ liệu, trình bày dữ liệu và xuất ra báo cáo được triển khai ở máy trạm. Đây có thể coi là mô hình 2 lớp của báo cáo. Điều này dẫn tới một số nhược điểm tương tự với các nhược điểm của mô hình 2 lớp: Đòi hỏi cấu hình máy trạm cao. Phức tạp khi triển khai, do phải triển khai engine báo cáo tới từng máy trạm. Khả năng mở rộng hệ thống thấp. Engine báo cáo nằm ở máy trạm, cũng là điểm xem báo cáo của người dùng cuối. Đòi hỏi kết nối trực tiếp tới cơ sở dữ liệu khiến báo cáo thông thường không thể được mở rộng ra bên ngoài môi trường mạng LAN. b. Mô hình báo cáo từ xa sử dụng Webservice Mô hình báo cáo từ xa sử dụng Webservice được trình bày trên hình dưới. Trong mô hình này, engine báo cáo được chuyển lên máy chủ, và được máy chủ cung cấp giao diện ra bên ngoài thông qua Webservice. Hệ thốngkhác Máy chủ báo cáo Engine báo cáo Máy trạm Engine báo cáo Engine báo cáo Web service Máy chủ web Browser Window Form Web viewer Mô hình báo cáo từ xa sử dụng Webservice Việc chuyển engine báo cáo lên máy chủ sẽ giải quyết các nhược điểm của mô hình truyền thống. Trong khi đó, việc sử dụng Webservice mở ra các khả năng: Sử dụng thống nhất báo cáo cho ứng dụng web và ứng dụng Smart Client. Ứng dụng Smart Client, cũng như máy chủ web có thể trực tiếp kết nối đến Webservice báo cáo để hiển thị báo cáo trên các form của ứng dụng Smart Client hoặc browser của ứng dụng web. Tích hợp dễ dàng hệ thống báo cáo tới các hệ thống khác thông qua môi trường Internet hoặc mạng LAN. 3.2.5.2. Kiến trúc và công nghệ Với mô hình báo cáo từ xa lựa chọn ở trên kiến trúc của hệ thống báo cáo, tra cứu được xây dựng như sau. Kiến trúc và công nghệ hệ thống báo cáo, tra cứu Máy chủ báo cáo được sử dụng ở đây là Crystal Report Server, truy suất vào kho dữ liệu Oralce Database qua giao thức Oracle Provider OLE DB. Server Proccess, Web Server có chứa các ứng dụng viết bằng ngôn ngữ C#, ASP.Net kết nối với cở sở dữ liệu Oracle qua giao thức Oracle Provider Net. Các bước xử lý khi người dùng thực hiện một báo cáo, tra cứu: Khi có người dùng muốn xem báo cáo từ ứng dụng Smart Client: Ứng dụng truyền yêu cầu cho Server Proccess, Server Proccess sẽ kết nối với cở sở dữ liệu lấy các tham số báo cáo, tham số báo cáo từ Server Proccess truyền cho Smart Client. Giao tiếp giữa Smart Client và Server Proccess qua giao thức Net Remoting. Sau khi người dùng chọn các điều kiện báo cáo và chấp nhận xem báo cáo, yêu cầu báo cáo từ Smart Client đến Crystal Report Server qua giao thức Web Service. Sau khi Crystal Report Server kết nối cơ sở dữ liệu lấy dữ liệu và xử lý dữ liệu, kết quả báo cáo trả về cho Smart Client. Khi có người dùng muốn xem báo cáo từ Web: Yêu cầu truyền cho Web Server, Web Server sẽ kết nối với cở sở dữ liệu lấy các tham số báo cáo, tham số báo cáo từ Web Server truyền cho Web Browser. Sau khi người dùng chọn các điều kiện báo và chấp nhận xem báo cáo, yêu cầu báo cáo từ Web Server đến Crystal Report Server qua giao thức Web Service. Sau khi Crystal Report Server kết nối cơ sở dữ liệu lấy dữ liệu và xử lý dữ liệu, kết quả báo cáo trả về cho Web Server và được hiển thị trên Web Browser. Khi có người dùng muốn tra cứu từ ứng dụng Smart Client: Ứng dụng truyền yêu cầu cho Server Proccess, Server Proccess sẽ kết nối với cở sở dữ liệu lấy các tham số tra cứu, tham số tra cứu từ Server Proccess truyền cho Smart Client. Giao tiếp giữa Smart Client và Server Proccess qua giao thức Net Remoting. Sau khi người dùng chọn các điều kiện tra cứu và chấp nhận xem tra cứu, yêu cầu tra cứu từ Smart Client đến Server Proccess. Sau khi Server Proccess kết nối cơ sở dữ liệu lấy dữ liệu và xử lý dữ liệu, kết quả tra cứu trả về cho Smart Client. Khi có người dùng muốn xem tra cứu từ Web: Yêu cầu truyền cho Web Server, Web Server sẽ kết nối với cở sở dữ liệu lấy các tham số tra cứu, tham số tra cứu từ Web Server truyền cho Web Browser. Sau khi người dùng chọn các điều kiện tra cứu và chấp nhận xem tra cứu, Web Server kết nối cơ sở dữ liệu lấy dữ liệu và xử lý dữ liệu, kết quả tra cứu trả về cho Web Browser. 3.2.5.3. Mô hình cơ sở dữ liệu của hệ thống báo cáo Mô hình cở sở dữ liệu của hệ thống báo cá, tra cứu Trong sơ đồ quan hệ thì một nhóm báo cáo có nhiều báo cáo; một báo cáo có nhiều tham số báo cáo và báo cáo con. Mỗi tham số báo cáo thuộc 1 loại điều khiển nào đó. Một báo cáo được gán cho 1 hay nhiều quyền. 3.2.5.4. Các chức năng cho hệ thống báo cáo Có 2 nhóm chức năng để có thể tạo và xem báo cáo: nhóm chức năng định nghĩa báo cáo, nhóm chức năng xem báo cáo. Nhóm chức năng định nghĩa báo cáo Chức năng định nghĩa báo cáo sẽ cho phép tạo ra báo cáo mới, phân quyền xem báo cáo mới cho người dùng hoặc nhóm người dùng. Các tham số, điều kiện của báo cáo này sẽ được lưu vào CSDL. Các bước và quy tắc định nghĩa một báo cáo mới: Định nghĩa một danh mục báo cáo mới: Nếu báo cáo cần hiển thị dưới dạng crystal report người dùng sẽ sử dụng Crystal Report Design để thiết kết mẫu báo và copy mẫu báo cáo lên Crystal Report Server. Mỗi báo cáo, tra cứu có 1 câu lệnh để lấy dữ liệu. Trong câu lệnh đó có thể có các tham số truyền vào, cú pháp của tham số truyền vào là {?ten_tham_so}. Ví dụ báo cáo thống kê doanh thu, câu lệnh lấy dữ liệu có 5 tham số: vttinh_id, nam, quy, thang, DK_DONVI_QL. SELECT ROW_NUMBER() OVER (ORDER BY a.donvi_ql desc) stt, c.ten_dv, sum(a.socuoc) cuoc, sum(a.tien) tien, sum(a.vat) vat, sum(a.tong) tong FROM wh_ccbs.mv_thongke_doanhthu a, wh_ccbs.thang b, wh_ccbs.donvi_ql c where a.thang = b.dimension_key and a.donvi_ql = c.donvi_id and a.donvi_ql in ({?DK_DONVI_QL}) and c.vttinh_id = {?vttinh_id} and b.nam = {?nam} and decode({?quy},0,0,b.quy) = {?quy} and decode({?thang},0,0,b.thang) = {?thang} group by a.donvi_ql, c.ten_dv Trong chức năng định nghĩa báo cáo, tra cứu mới: Mỗi báo cáo có 1 câu lệnh SQL, tên file cystal report (Nếu muốn hiển thị kết quả báo cáo theo thiết kế của file crystal report), trạng thái của báo cáo, nhóm báo cáo,… Định nghĩa tham số cho báo cáo: Mỗi báo cáo, tra cứu có thể có nhiều tham số. Có hai loại tham số: tham số chọn trên chức năng xem báo cáo, tra cứu và tham số phân quyền cho người dùng. Cú pháp của tham số phân quyền cho người sử dụng {?DK_TENBANG}, chương trình sẽ thay thế tham số đó bằng danh sách giá trị của bảng (chiều) TENBANG mà người dùng có quyền thao tác, loại tham số này không cần định nghĩa. Trong ví dụ trên tham số DK_DONVI_QL sẽ được chương trình thay thế bằng danh sách các đơn vị quản lý mà người dùng có quyền thao tác. Các tham số còn lại vttinh_id, nam, quy, thang là tham số được chọn trên chức năng xem báo cáo các tham số này phải được định nghía. Trong chức năng định nghĩa tham số báo cáo mỗi tham số có các thuộc như hình vẽ: Tên tham số: Đặt tên giống với tên trong câu lệnh lấy dữ liệu báo cáo. Ví dụ: vttinh_id. Tên hiển thị: Tên hiển thị trên chức năng xem báo cáo, tra cứu. Loại điều khiển: Có các loại điều khiển như Combobox, Textbox, Listbox, Checkbox, Ngay gio, Ngay, Thang. Định dạng: Đối với loại điều khiển Ngay gio, Ngay, Thang phải có định dạng của dữ liệu, ví dụ tham số thang có định dạng yyyyMM. Lệnh: Đối với loại điều khiển Combobox, Listbox phải có kèm theo câu lệnh SQL lấy dữ liệu cho điều khiển đó, ví dụ tham số vttinh_id có câu lệnh lấy dữ liệu: Cột thứ nhất là id, cột thứ hai hiển thị trên giao diện. select distinct a.vttinh_id, a.vt_tinh from wh_ccbs.donvi_ql a Các điều khiển Combobox có thể có quan hệ với nhau, Combobox trên giới hạn dữ liệu cho Combobox dưới. Ví dụ khi chọn tham số quy, để tham số thang chỉ hiển thị các tháng thuộc quý đó thì câu lệnh lấy dữ liệu cho tháng phải có cấu trúc: SELECT distinct a.thang, substr(a.thang,5,2) aa FROM wh_ccbs.thang a where a.quy = :quy order by thang Kiểu dữ liệu, có 2 loại: varchar và number. Vị trí: Thứ tự hiển thị điều khiển trên chức năng xem báo cáo, tra cứu. Giá trị text: Đối với điều khiển dạng Combobox, nếu có giá trị này thì tham số báo cáo được thay thế bằng giá trị text của Combobox (cột thứ 2 của câu lệnh lấy dữ liệu) ngược lại lấy theo id (cột thứ 1 của lệnh lấy dữ liệu). Hiển thị nhãn: Nếu có giá trị thì, sẽ có 1 tham số có tên lblTenThamSo tương ứng với tham số TenThamSo, giá trị của nó là giá trị text của điều khiển. Sử dụng tham số trong lệnh lấy dữ liệu hoặc hiển thị trên form kết quả. Định dạng nhãn: Định dạng cho tham số nhãn. Định nghĩa tham số cho báo cáo: Một báo cáo có thể nhiều báo cáo con. Mỗi báo cáo con có câu lệnh tương ứng. Gán quyền xem báo cáo cho người dùng, nhóm người dùng: Sau khi định nghĩa xong báo cáo có thể gán báo cáo đó cho 1 quyền nào đó, quyền đó được gán cho người dùng hoặc nhóm người dùng. Nhóm chức năng xem báo cáo, tra cứu Hiển thị các báo cáo, tra cứu mà người dùng có quyền xem. Đối với từng báo cáo, tra cứu hiển thị các điều kiện để người dùng lựa chọn. Đối với báo cáo truyền yêu cầu báo cáo cho Crystal Report Server xử lý và hiển thị kết quả báo cáo cho người sử dụng. Đối với tra cứu truyền yêu cầu báo cáo cho Server Proccess xử lý và hiển thị kết quả tra cứu cho người sử dụng. Đối với từng báo cáo, chương trình sẽ lấy danh sách các tham số trong CSDL. Đối với từng tham số, dựa vào loại điều khiện và thông số cho điều khiển đó, chương trình tự động thêm control vào vùng điều kiện trên chức năng hiển thị báo cáo, tra cứu. Sau khi người dùng chọn điều kiện xong và chấp nhận xem báo cáo tra cứu, chương trình lần lượt đọc các điều khiển, lấy giá trị người dùng đã chọn thay thế các tham số trong câu lệnh SQL lấy dữ liệu. Với câu lệnh SQL đã được xử lý, Crystal Report Server hoặc Server Proccess sẽ trả kết quả cho người sử dụng. Trong ví dụ trên, báo cáo ‘Thống kê doanh thu’ có 4 tham số được hiển thị trên form để người dùng chọn điều kiện: Viễn thông tỉnh, năm, quý, tháng. 3.2.6. Thiết kế công cụ quản trị hệ thống Chức năng quản trị hệ thống cho phép người quản trị hệ thống định nghĩa các quyền và phân quyền cho người dùng. 3.2.6.1. Các chức năng công cụ quản trị hệ thống Cập nhật nhóm người dùng. Cập nhật nhân viên. Cập nhật người dùng. Định nghĩa form nhập dữ liệu từ điển: Người dùng có thể tự định nghĩa form nhập cho một bảng dữ liệu nào đó (bảng chiều hoặc bảng sự kiện). Form chung nhập dữ liệu từ điển: Từ định nghĩa form nhập dữ liệu cho 1 bảng nào đó, chương trình tự động xây dựng form nhập dữ liệu cho bảng đó. Cập nhật menu: Định nghĩa các hệ thống menu khác nhau dựa trên các chức năng đã được định nghĩa. Cập nhật báo cáo: Mô tả phần báo cáo ở trên. Cập nhật hàm. Định nghĩa các bảng giới hạn thao tác. Quản lý quyền, mỗi quyền bao gồm 4 thành phần: menu, báo cáo, hàm, giá trị bảng được phép thao tác. Gán quyền cho người dùng: Có thể gán 1 hoặc nhiều role cho một nhóm người dùng. Một người dùng có thể có các quyền ứng với 1 role nào đó, ngoài ra còn có thêm các quyền riêng lẻ. Gán quyền cho nhóm người dùng. 3.2.6.2. Mô hình cơ sở dữ liệu của công cụ quản trị hệ thống Mô hình cở sở dữ liệu của công cụ quản trị hệ thống Trong mô hình CSDL này có các mối quan hệ sau: Mỗi đơn vị có nhiều nhân viên. Mỗi nhân viên có thể có nhiều người dùng. Mỗi nhóm người dùng có thể có nhiều người dùng. Mỗi quyền có thể có nhiều menu, hàm, báo cáo, bảng. Mỗi quyền có thể gán cho nhiều người dùng, nhóm người dùng. 3.3. Cài đặt, triển khai 3.3.1. Cài đặt hệ thống Theo hình 53, hệ thống kho dữ liệu thử nghiệm sẽ bao gồm các thành phần sau: Máy chủ lưu kho dữ liệu, máy chủ ETL, máy chủ báo cáo+ webserver, máy trạm. Máy chủ lưu kho dữ liệu: Có bộ nhớ tối thiểu 2GB, dung lượng ổ cứng tối thiểu là 80GB (Có thể lưu trữ được dữ liệu của vài tháng). Được cài đặt Oracle 11G. Trên máy chủ này có 3 user chính: OWBSYS – Lưu trữ cấu hình, quản trị, thiết kế kho dữ liệu; ADMIN_WH – Lưu trữ cở sở dữ liệu hệ thống báo cáo động và quản trị hệ thống; Lưu trữ WH_CCBS – Lưu trữ dữ liệu chính kho dữ liệu. Máy chủ ETL: Có cài đặt Warehouse Builder 11G. Máy chủ báo cáo + WebServer + Proccess Server: Cài đặt dotnetfx 2.0, Crystal report server 9, IIS. Có chứa các ứng dụng: Địa chỉ web báo cáo tra cứu, Proccess Server, Địa chỉ cài đặt cho máy client (Bộ cài dùng chung cho tất cả các máy trạm: bộ cài dotnetfx 2.0, crystalreports for net 2.0 và ứng dụng). Máy trạm: Máy trạm có thể chạy các ứng dụng báo cáo và tra cứu bằng web hoặc winform. Để chạy được ứng dụng bằng winfom phải cài đặt dotnetfx 2.0, crystalreports for net 2.0. Người dùng cài đặt qua địa chỉ web, khi có phiên bản mới tự động tải về. Khi cài đặt chúng ta có thể cài đặt tất cả cảc máy chủ kho dữ liệu, ứng dụng và ETL trên cùng 1 máy. 3.3.2. Triển khai Sau khi đã cài đặt hệ thống và có bản thiết kế DWH ở trên, việc triển khai DWH qua các bước sau: Tạo cấu trúc cở sở dữ liệu cho các chiều: Sử dụng OWB để delploy các chiều đã được thiết kế ở trên. Tạo cấu trúc cở sở dữ liệu cho các bảng sự kiện: Sử dụng OWB để delploy các bảng sự kiện đã được thiết kế ở trên. Tạo cấu trúc cở sở dữ liệu cho các bảng tổng hợp, view, materialized view: Sử dụng OWB để delploy các materialized view đã được thiết kế ở trên. Lúc này cơ sở hạ tầng của DWH đã được hoàn chỉnh. Tạo các hàm, thủ tục, package cho các mapping thu thập số liệu: Sử dụng OWB để delploy các mapping thu thập số liệu đã được thiết kế ở trên. Chạy các mapping thu thập số liệu cho các chiều: Sử dụng OWB để start các mapping thu thập số liệu các chiều. Chạy các mapping thu thập số liệu cho các bảng sự kiện: Sử dụng OWB để start các mapping thu thập số liệu các bảng sự kiện. Tạo người dùng, phân quyền cho người dùng xem báo cáo, tra cứu và cập nhật số liệu. Tạo báo cáo, tra cứu,…phân quyền cho người dùng báo cáo, tra cứu đó. Chương 4: KẾT QUẢ VÀ HƯỚNG PHÁT TRIỂN Tóm lược nội dung: Nội dung chính của chương này là trình bày các kết quả của luận văn; một số giao diện của công cụ báo cáo, tra cứu, quản trị hệ thống; một số báo cáo phân tích;hướng phát triển của luận văn. 4.1. Kết quả Sau một thời gian nghiên cứu và thực hiện các nội dung của luận văn, luận văn đạt được các kết quả sau: Về mặt lý thuyết: Tìm hiểu về DWH, các kiến trúc công nghệ để xây dựng DWH. Tìm hiểu hệ thống nguồn CCBS, các yêu cầu của một hệ thống DWH viễn thông cần có. Có bản thiết kế chi tiết mô hình lôgíc, mô hình vật lý, thu thập số liệu cho DWH. Thu thập và chuyển đổi số liệu từ hệ thống CCBS đến hệ thống DWH. Xây dựng được công cụ báo cáo, tra cứu động, quản trị hệ thống cho DWH đã xây dựng. Xây dựng được một số báo cáo phân tích. Phần tiếp theo luận văn sẽ giới thiệu một số giao diện chính, báo cáo phân tích. 4.2. Giao diện chương trình Giao diện chính hệ thống báo cáo, tra cứu, quản trị hệ thống. Giao diện chính Giao diện chức năng định nghĩa báo cáo và tra cứu động. Giao diện định nghĩa báo cáo và tra cứu Giao diện chung hiển thị báo cáo. Giao diện chung hiển thị báo cáo Giao diện tra cứu động Giao diện tra cứu động Giao diện cấu hình form nhập dữ liệu từ điển Giao diện cấu hình form nhập dữ liệu từ điển Giao diện chung nhập dữ liệu từ điển. Giao diện chung nhập dữ liệu từ điển Giao diện định nghĩa quyền. Giao diện định nghĩa quyền Giao diện phân quyền cho nhóm người dùng. Giao diện phân quyền cho nhóm người dùng 4.3. Báo cáo phân tích Hiện tại luận văn đã thiết kế một số báo cáo, tra cứu phân tích cơ bản, việc thiết kế các báo cáo, tra cứu mới người dùng có thể thực hiện dễ dàng bằng công cụ tạo báo cáo và tra cứu động mà không phải dịch lại code (Người dùng ở đây là người có quyền tạo báo cáo, tra cứu và nắm được CSDL của DWH). Các câu lệnh SQL lấy dữ liệu của các báo cáo phân tích được mô tả chi tiết trong phần phụ lục 3. Báo cáo phân tích bán hàng: Thống kê phát triển thuê bao theo Viễn thông tỉnh, dịch vụ viễn thông, thời gian. Báo cáo thuê bao phát triển trong chương trình khuyến mãi: Thống kê phát triển thuê bao trong chương trình khuyến mãi theo Viễn thông tỉnh, dịch vụ viễn thông. Báo cáo tổng hợp doanh thu theo thời gian: Thống kê doanh thu theo Viễn thông tỉnh, thời gian (Năm, quý, tháng). Báo cáo tổng hợp thu tiền theo thời gian: Thống kê thu tiền theo Viễn thông tỉnh, thời gian (Năm, quý, tháng). Báo cáo tổng hợp tiền nợ theo thời gian: Thống kê tiền nợ theo Viễn thông tỉnh, thời gian (Năm, quý, tháng). Thống kê danh sách khách hàng có doanh thu cao nhất: Danh sách 10 khách hàng có doanh thu cao nhất theo tháng. Thống kê danh sách khách hàng có doanh cao: Danh sách các khách hàng có doanh thu lớn hơn 20.000.000 theo tháng. Báo cáo phân tích khiếu nại khách hàng: Thông kê khiếu nại của khách hàng theo tháng, dịch vụ viễn thông. Thống kê báo hỏng: Thông kê báo hỏng của khách hàng theo tháng, dịch vụ viễn thông. Thống kê sử dụng dịch vụ: Thông kê sử dụng dịch vụ của khách hàng theo Viễn thông tỉnh, dịch vụ viễn thông, tháng. Thống kê cuộc gọi theo giờ gọi: Thống kê cuộc gọi theo viễn thông tỉnh, thời gian (Năm, quý, tháng). Thống kê cuộc gọi theo mã vùng: Thống kê cuộc gọi theo viễn thông tỉnh, thời gian (Năm, quý, tháng). Thống kê cuộc gọi theo dịch vụ: Thống kê cuộc gọi theo viễn thông tỉnh, thời gian (Năm, quý, tháng). 4.4. Hướng phát triển của đề tài Hiện tại kho dữ liệu đã thiết kế hoàn chỉnh cho doanh nghiệp viễn thông nhưng dữ liệu nguồn chủ yếu lấy từ hệ thống tính cước chăm sóc khách hàng của một Viễn thông tỉnh thành phố. Dữ liệu khách hàng sử dụng dịch vụ viễn thông của VNPT đang chủ yếu tập trung ở các tỉnh, tập đoàn Bưu chính Viễn thông đang có kế hoặch quản dữ liệu tập trung cho tất cả 64 tỉnh thành. Phần khai thác kho dữ liệu chủ yếu mới là các báo cáo và tra cứu. Từ nhu cầu thực tế của các viễn thông tỉnh và tập đoàn cũng như hoàn thiệt thiết kế kho dữ liệu, một số hướng tiếp tục của đề tài sẽ là: Tiếp tục xác định thêm các yêu cầu tại các Viễn thông tỉnh và trên tập đoàn để thiết kế kho dữ liệu hoàn chỉnh hơn. Xây dựng công cụ chuyển đổi từ nhiều nguồn khác nhau. Bổ sung dữ liệu quản lý mạng như trạng thái mạng, lưu lượng mạng,… Thiết kế thu thập số liệu khi có dự án quản lý khách hàng tập trung của VNPT. Sử dụng công cụ khai thác phân tích dữ liệu trực tuyến OLAP hỗ trợ quá trình ra quyết định. Xây dựng các ứng dụng khai phá dữ liệu chẳng hạn như phát hiện gian lận, quản lý chăm sóc khách hàng, phát hiện và cô lập lỗi trên hệ thống mạng viễn thông. Xây dụng một số phần dưới dạng kho dữ liệu thời gian thực. KẾT LUẬN VÀ KIẾN NGHỊ Sau một thời gian nghiên cứu và thực hiện các nội dung của luận văn, tác giả đã xây dựng được một DWH ban đầu tương đối hoàn chỉnh cho Viễn thông tỉnh lấy từ hệ thống nguồn CCBS. Hiện tại hệ thống thử nghiệm đã chạy thử nghiệm tại Viễn Long An và trong thời gian tới tiếp tục thử nghiệm tại một số Viễn thông tỉnh khác. Hệ thống tiếp tục được hoàn thiện, mở rộng để có thể áp dụng chính thức cho các Viễn thông tỉnh cũng như VNPT. Hệ thống DWH được xây dựng chung cho lĩnh vực viễn thông nên không chỉ áp dụng VNPT mà còn có thể áp dụng cho các doanh nghiệp viễn thông khác. Trong quá trình nghiên cứu và hoàn thành luận văn này, tác giả đã tiếp thu những kiến thức mới và tham khảo một số tài liệu của các nhà khoa học, các thầy cô giáo và các bạn bè đồng nghiệp ở Đại học Công nghệ, Viện Công nghệ Thông tin, Công ty Cổ phần Tin học Ứng dụng và Viễn thông. Tác giả xin trân trọng cảm ơn các thầy cô giáo và các đồng nghiệp, đặc biệt là Thầy giáo, PGS.TS Đặng Hữu Đạo - Viện Công nghệ Thông tin đã tận tình hướng dẫn, tạo điều kiện để tác giả hoàn thành luận văn này. Tác giả xin được gửi lời cảm ơn tới Viễn thông Long An đã tạo điều kiện và hỗ trợ trong quá trình khảo sát yêu cầu, thử nghiệm hệ thống. Do điều kiện và thời gian nghiên cứu còn nhiều hạn chế nên luận văn chắc chắn không tránh khỏi những thiếu sót. Tác giả rất mong nhận được sự đóng góp ý kiến của các thầy cô giáo, bạn bè đồng nghiệp và những người quan tâm. TÀI LIỆU THAM KHẢO Tài liệu tiếng Việt Đoàn Văn Ban (1997), Phương pháp thiết kế và khai thác kho dữ liệu, đề tài nghiên cứu cấp Trung tâm KHTN & CNQG Hà Thái Bảo, Trung tâm Công nghệ Thông tin, Học viện Bưu chính Viễn thông (2002), Áp dụng các công nghệ mới để xây dựng hệ thống tính cước và chăm sóc khách hàng theo mô hình nhiều lớp (Multi-layer), Hà Nội. Nguyễn Thanh Bình, Đại học Huế, Kho dữ liệu và Hệ hỗ trợ quyết định, Huế. Công ty Cổ phần Tin học Ứng dụng và Viễn thông (2008), Tài liệu thiết kế hệ thống tính cước và chăm sóc khách hàng, Hà Nội. Huỳnh Đức Nghĩa, Trung tâm Công nghệ Thông tin, Học viện Bưu chính Viễn thông (2004), Nghiên cứu công nghệ phân tán trên nền .NET Framework áp dụng cho việc phát triển và tích hợp các hệ thống phần mềm, Hà Nội. Tập đoàn Bưu chính Viễn thông Việt Nam VNPT (2006), Tài liệu quy trình nghiệp vụ, Hà Nội. Tài liệu tiếng Anh. Artech House, Boston London (1997), Data Warehousing and Data Mining for Telecommunications, London. IBM (2003), Telecommunications Data Warehouse, USA.. J.D. Meier, Alex Mackman, Michael Dunner, and Srinath Vasireddy, Microsoft Corporation (2002), .NET Remoting Security. Oracle (2007), Data Warehousing Guide 11g Release 1, USA. VINCENT RAINARDI (2008), Building a Data Warehouse With Examples in SQL Server, USA. Ralph Kimball Margy Ross (2002), The Data WarehouseToolkit Second Edition The Complete Guide to Dimensional Modeling, New York. Stephen Toub (2002), “Secure Your .NET Remoting Traffic by Writing an Asymmetric Encryption Channel Sink”, MSDN Magazine. Oracle (2007), Warehou Builder Guide 11g Release 1, USA. WWW.ORACLE.COM Phụ lục 1: SƠ ĐỒ CSDL BẢNG SỰ KIỆN CỦA DWH Phát triển thuê bao Điều hành thi công Chi tiết cước khách hàng Tổng hợp cước khách hàng Chi tiết nợ khách hàng Chi tiết tiền thanh toán Khiếu nại khách hàng Nhắc nợ khách hàng Khóa mở máy Xử lý nợ Báo hỏng Sử dụng dịch vụ Phụ lục 2: LỆNH SCRIPT CỦA CÁC ÁNH XẠ ETL SẢN SINH TỪ OWB Ánh xạ chuyển đổi số liệu bán hàng (Đăng ký dịch vụ viễn thông) FUNCTION "DANGKY_DVVT_Bat" RETURN BOOLEAN IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; BEGIN INSERT INTO "DANGKY_DVVT" ("TIEN_DK","VAT_DK","TIEN_KM","VAT_KM", "TIEN_TT","VAT_TT","NGAY_TT","NGAY_YC","MA_TB", "CHUONGTRINH_KM","DIACHI","DICHVU_VT","DIEM_GD", "KENH_GT","KHACHHANG","KIEU_YC","THOIGIAN") (SELECT "AGGREGATOR"."TIEN_DK$1" "TIEN_DK", "AGGREGATOR"."VAT_DK$1" "VAT_DK", "AGGREGATOR"."TIEN_KM$1" "TIEN_KM", "AGGREGATOR"."VAT_KM$1" "VAT_KM", "AGGREGATOR"."TIEN_DK$1" - "AGGREGATOR"."TIEN_KM$1"/* TIEN_THANHTOAN.OUTGRP1.TIEN_TT */ "TIEN_TT", "AGGREGATOR"."VAT_DK$1" - "AGGREGATOR"."VAT_KM$1"/* TIEN_THANHTOAN.OUTGRP1.VAT_TT */ "VAT_TT", "AGGREGATOR"."NGAY_TT$1" "NGAY_TT", "AGGREGATOR"."NGAY_YC$1" "NGAY_YC", "AGGREGATOR"."MA_TB$1" "MA_TB", "AGGREGATOR"."CHUONGTRINH_KM$1" "CHUONGTRINH_KM", "AGGREGATOR"."DIACHI$1" "DIACHI", "AGGREGATOR"."DICHVU_VT$1" "DICHVU_VT", "AGGREGATOR"."DIEM_GD$1" "DIEM_GD", "AGGREGATOR"."KENH_GT$1" "KENH_GT", "AGGREGATOR"."KHACHHANG$1" "KHACHHANG", "AGGREGATOR"."KIEU_YC$1" "KIEU_YC", "AGGREGATOR"."THOIGIAN$1" "THOIGIAN" FROM (SELECT "AGG_INPUT"."CTKM_ID"/* AGGREGATOR.OUTGRP1.CHUONGTRINH_KM */ "CHUONGTRINH_KM$1", "AGG_INPUT"."PHUONG_ID"/* AGGREGATOR.OUTGRP1.DIACHI */ "DIACHI$1", "AGG_INPUT"."LOAITB_ID"/* AGGREGATOR.OUTGRP1.DICHVU_VT */ "DICHVU_VT$1", "AGG_INPUT"."DONVI_ID"/* AGGREGATOR.OUTGRP1.DIEM_GD */ "DIEM_GD$1", 1/* AGGREGATOR.OUTGRP1.KENH_GT */ "KENH_GT$1", "AGG_INPUT"."KHACHHANG_ID"/* AGGREGATOR.OUTGRP1.KHACHHANG */ "KHACHHANG$1", "AGG_INPUT"."KIEULD_ID"/* AGGREGATOR.OUTGRP1.KIEU_YC */ "KIEU_YC$1", "AGG_INPUT"."NGAY"/* AGGREGATOR.OUTGRP1.THOIGIAN */ "THOIGIAN$1", "AGG_INPUT"."NGAY_TT$2"/* AGGREGATOR.OUTGRP1.NGAY_TT */ "NGAY_TT$1", "AGG_INPUT"."NGAYLAP_HD"/* AGGREGATOR.OUTGRP1.NGAY_YC */ "NGAY_YC$1", SUM(NVL("AGG_INPUT"."TIEN",0))/* AGGREGATOR.OUTGRP1.TIEN_DK */ "TIEN_DK$1", SUM(NVL("AGG_INPUT"."VAT",0))/* AGGREGATOR.OUTGRP1.VAT_DK */ "VAT_DK$1", SUM(NVL("AGG_INPUT"."TIEN_KM$2",0))/* AGGREGATOR.OUTGRP1.TIEN_KM */ "TIEN_KM$1", SUM(NVL("AGG_INPUT"."VAT_KM$2",0))/* AGGREGATOR.OUTGRP1.VAT_KM */ "VAT_KM$1", "AGG_INPUT"."MA_TB$2"/* AGGREGATOR.OUTGRP1.MA_TB */ "MA_TB$1" FROM (SELECT "HD_KHACHHANG"."NGAYLAP_HD" "NGAYLAP_HD", "HD_KHACHHANG"."DONVI_ID" "DONVI_ID", "HD_THUEBAO"."HDTB_ID" "HDTB_ID", "HD_THUEBAO"."MA_TB" "MA_TB$2", "HD_THUEBAO"."KIEULD_ID" "KIEULD_ID", "HD_THUEBAO"."LOAITB_ID" "LOAITB_ID", "HD_THUEBAO"."NGAY_TT" "NGAY_TT$2", "DIACHI"."PHUONG_ID" "PHUONG_ID", "CT_TIENHD"."TIEN" "TIEN", "CT_TIENHD"."VAT" "VAT", "CT_TIENHD"."TIEN_TT" "TIEN_TT$1", "CT_TIENHD"."VAT_TT" "VAT_TT$1", "CT_TIENHD"."TIEN_KM" "TIEN_KM$2", "CT_TIENHD"."VAT_KM" "VAT_KM$2", CSS_LAN.LAY_CTKM_HDTB("HD_THUEBAO"."HDTB_ID")/* LAY_CTKM_HDTB.OUTGRP1.CTKM_ID */ "CTKM_ID", to_number(to_char( "HD_KHACHHANG"."NGAYLAP_HD", 'yyyyMMdd'))/* LAY_CHIEU_NGAY_LAPHD.OUTGRP1.NGAY */ "NGAY", "HD_KHACHHANG"."KHACHHANG_ID" "KHACHHANG_ID" FROM "CSS_LAN"."HD_KHACHHANG" "HD_KHACHHANG" JOIN "KHACHHANG" "KHACHHANG" ON ( ( "HD_KHACHHANG"."KHACHHANG_ID" = "KHACHHANG"."KHACHHANG_ID" ) ) JOIN "CSS_LAN"."HD_THUEBAO" "HD_THUEBAO" ON ( ( "HD_THUEBAO"."HDKH_ID" = "HD_KHACHHANG"."HDKH_ID" ) ) JOIN "CSS_LAN"."DIACHI_HDTB" "DIACHI_HDTB" ON ( ( "HD_THUEBAO"."HDTB_ID" = "DIACHI_HDTB"."HDTB_ID" ) ) JOIN "CSS_LAN"."DIACHI" "DIACHI" ON ( ( "DIACHI_HDTB"."DIACHI_ID" = "DIACHI"."DIACHI_ID" ) ) LEFT OUTER JOIN ( SELECT "CT_TIENHD"."CT_TIENHD_ID" "CT_TIENHD_ID", "CT_TIENHD"."TIEN" "TIEN", "CT_TIENHD"."VAT" "VAT", "CT_TIENHD"."TIEN_TT" "TIEN_TT", "CT_TIENHD"."VAT_TT" "VAT_TT", "CT_TIENHD"."TIEN_KM" "TIEN_KM", "CT_TIENHD"."VAT_KM" "VAT_KM", "CT_TIENHD"."HDTB_ID" "HDTB_ID" FROM "CSS_LAN"."CT_TIENHD" "CT_TIENHD" ) "CT_TIENHD" ON ( ( "HD_THUEBAO"."HDTB_ID" = "CT_TIENHD"."HDTB_ID" ) ) WHERE ("HD_THUEBAO"."TTHD_ID" not in (1,7)/* JOINER */)) "AGG_INPUT" GROUP BY "AGG_INPUT"."CTKM_ID","AGG_INPUT"."PHUONG_ID","AGG_INPUT"."LOAITB_ID","AGG_INPUT"."DONVI_ID","AGG_INPUT"."KHACHHANG_ID","AGG_INPUT"."KIEULD_ID","AGG_INPUT"."NGAY","AGG_INPUT"."NGAY_TT$2","AGG_INPUT"."MA_TB$2","AGG_INPUT"."NGAYLAP_HD",1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1/* AGGREGATOR */) "AGGREGATOR" ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; RETURN FALSE; END; COMMIT; RETURN TRUE; END "DANGKY_DVVT_Bat"; Ánh xạ chuyển đổi số liệu điều hành thi công FUNCTION "DIEUHANH_TC_Bat" RETURN BOOLEAN IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; BEGIN INSERT INTO "DIEUHANH_TC"("NGAY_GIAO","NGAY_TH","SONGAY","DICHVU_VT", "DONVI_TC","HUONGGIAO","THOIGIAN","THUEBAO","KIEU_YC") (SELECT "GIAOPHIEU"."NGAYGIAO" "NGAYGIAO", "GIAOPHIEU"."NGAY_TH" "NGAY_TH", trunc( "GIAOPHIEU"."NGAY_TH" ) - trunc ( "GIAOPHIEU"."NGAYGIAO" )/* LAY_SONGAY_TH.OUTGRP1.SONGAY */ "SONGAY", "HD_THUEBAO"."LOAITB_ID" "LOAITB_ID", "GIAOPHIEU"."DONVI_NHAN_ID" "DONVI_NHAN_ID", "GIAOPHIEU"."HUONGGIAO_ID" "HUONGGIAO_ID", to_number(to_char( "GIAOPHIEU"."NGAY_TH" ,'yyyyMMdd'))/* LAY_CHIEU_THOIGIAN.OUTGRP1.NGAY */ "NGAY", "HD_THUEBAO"."THUEBAO_ID" "THUEBAO_ID", "HD_THUEBAO"."KIEULD_ID" "KIEULD_ID" FROM "CSS_LAN"."HD_THUEBAO" "HD_THUEBAO", "CSS_LAN"."GIAOPHIEU" "GIAOPHIEU", "THUEBAO" "THUEBAO" WHERE ( "HD_THUEBAO"."HDTB_ID" = "GIAOPHIEU"."HDTB_ID" ) AND ( "HD_THUEBAO"."THUEBAO_ID" = "THUEBAO"."THUEBAO_ID" ) AND ( "HD_THUEBAO"."TTHD_ID" = 6 ) AND ( "GIAOPHIEU"."NGAY_TH" is not null ) ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; RETURN FALSE; END; COMMIT; RETURN TRUE; END "DIEUHANH_TC_Bat"; Ánh xạ chuyển đổi số liệu chi tiết cước FUNCTION "CT_CUOC_KH_Bat" RETURN BOOLEAN IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; BEGIN INSERT INTO "CT_CUOC_KH" ("CHUGOI","BIGOI","TG_BD","GIO_BD","TG_GOI","TIEN", "VAT","TIENTRU","BYTE_IN","BYTE_OUT","KHACHHANG", "KIEU_CG","KIEU_DV","THOIGIAN","THUEBAO", "MA_VUNG","DICHVU_VT","DONVI_QL","DOITUONG_KH","THANG") (SELECT "MV_CHITIET_CUOC"."CHUGOI" "CHUGOI", "MV_CHITIET_CUOC"."BIGOI" "BIGOI", "MV_CHITIET_CUOC"."GIO_BD" "GIO_BD", to_number(to_char( "MV_CHITIET_CUOC"."GIO_BD" ,'HH24'))/* LAY_GIO_GOI.OUTGRP1.GIO_BD */ "GIO_BD$1", "MV_CHITIET_CUOC"."TG_GOI" "TG_GOI", "MV_CHITIET_CUOC"."TIEN" "TIEN", "MV_CHITIET_CUOC"."VAT" "VAT", "MV_CHITIET_CUOC"."TIENTRU" "TIENTRU", "MV_CHITIET_CUOC"."BYTE_IN" "BYTE_IN", "MV_CHITIET_CUOC"."BYTE_OUT" "BYTE_OUT", "THUEBAO"."KHACHHANG_ID" "KHACHHANG_ID", "MV_CHITIET_CUOC"."KIEUCG_ID" "KIEUCG_ID", "MV_CHITIET_CUOC"."HUONGDT_ID" "HUONGDT_ID", to_number(to_char( "MV_CHITIET_CUOC"."GIO_BD" ,'yyyyMMdd'))/* LAY_NGAY_GOI.OUTGRP1.NGAY */ "NGAY", "THUEBAO"."THUEBAO_ID" "THUEBAO_ID", "MA_VUNG"."MAVUNG_ID" "MAVUNG_ID", "THUEBAO"."LOAITB_ID" "LOAITB_ID", "THUEBAO"."DONVI_ID" "DONVI_ID", "THUEBAO"."DOITUONG_ID" "DOITUONG_ID", "MV_CHITIET_CUOC"."THANG" "THANG" FROM "TINHCUOC"."MV_CHITIET_CUOC" "MV_CHITIET_CUOC", "THUEBAO" "THUEBAO", "MA_VUNG" "MA_VUNG" WHERE ( "MV_CHITIET_CUOC"."CHUGOI" = "THUEBAO"."MA_TB" ) AND ( "MV_CHITIET_CUOC"."MAVUNG" = "MA_VUNG"."MA_VUNG" ) ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; RETURN FALSE; END; COMMIT; RETURN TRUE; END "CT_CUOC_KH_Bat"; Ánh xạ chuyển đổi số liệu xử lý khiếu nại FUNCTION "XL_KHIEUNAI_Bat" RETURN BOOLEAN IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; BEGIN INSERT INTO "XL_KHIEUNAI"("NGAY_KN","NGAY_XL","NOIDUNG_XL","DICHVU_VT", "KETQUA_KN","KHACHHANG","THOIGIAN","THUEBAO","NHOM_KN", "DONVI_XLKN") (SELECT "KHIEUNAI"."NGAY_KN" "NGAY_KN", "KHIEUNAI"."NGAY_TL" "NGAY_TL", "KHIEUNAI"."GIAIPHAP_KP" "GIAIPHAP_KP", "DB_THUEBAO"."LOAITB_ID" "LOAITB_ID", "KHIEUNAI"."KETQUAKN_ID" "KETQUAKN_ID", "DB_THUEBAO"."KHACHHANG_ID" "KHACHHANG_ID", to_number(to_char( "KHIEUNAI"."NGAY_TL" ,'yyyyMMdd'))/* LAY_CHIEU_THOIGIAN.OUTGRP1.THOIGIAN */ "THOIGIAN", "DB_THUEBAO"."THUEBAO_ID" "THUEBAO_ID", "CHITIET_KN"."LOAIKN_ID" "LOAIKN_ID", "KHIEUNAI"."DONVI_GQ_ID" "DONVI_GQ_ID" FROM "GQKN"."KHIEUNAI" "KHIEUNAI", "GQKN"."CHITIET_KN" "CHITIET_KN", "CSS_LAN"."DB_THUEBAO" "DB_THUEBAO" WHERE ( "KHIEUNAI"."KHIEUNAI_ID" = "CHITIET_KN"."KHIEUNAI_ID" ) AND ( "KHIEUNAI"."THUEBAO_ID" = "DB_THUEBAO"."THUEBAO_ID" ) AND ( "KHIEUNAI"."TRANGTHAIKN_ID" = 2 ) AND ( "KHIEUNAI"."NGAY_TL" is not null ) ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; RETURN FALSE; END; COMMIT; RETURN TRUE; END "XL_KHIEUNAI_Bat"; Ánh xạ chuyển đổi số liệu báo hỏng FUNCTION "FT_BAOHONG_Bat" RETURN BOOLEAN IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; BEGIN INSERT INTO "BAOHONG" ("NGAY_BH", "GHICHU_HONG", "KHACHHANG", "THOIGIAN", "THUEBAO", "TINHTRANG", "DONVI_QL", "DICHVU_VT") (SELECT "BAOHONG"."NGAY_BH" "NGAY_BH", "BAOHONG"."GHICHU_HONG" "GHICHU_HONG", "DB_THUEBAO"."KHACHHANG_ID" "KHACHHANG_ID", to_number(to_char( "BAOHONG"."NGAY_BH" ,'yyyyMMdd'))/* LAY_CHIEU_NGAY.OUTGRP1.NGAY */ "NGAY", "BAOHONG"."THUEBAO_ID" "THUEBAO_ID", "BAOHONG"."TINHTRANG_ID" "TINHTRANG_ID", "DB_THUEBAO"."DONVITC_ID" "DONVI_ID", "DB_THUEBAO"."LOAITB_ID" "LOAITB_ID" FROM "BH119"."BAOHONG" "BAOHONG", "CSS_LAN"."DB_THUEBAO" "DB_THUEBAO" WHERE ( "BAOHONG"."THUEBAO_ID" = "DB_THUEBAO"."THUEBAO_ID" ) ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; COMMIT; RETURN FALSE; END; COMMIT; RETURN TRUE; END "FT_BAOHONG_Bat"; Phụ lục 3: CÂU LỆNH SQL LẤY DỮ LIỆU CỦA CÁC BÁO CÁO PHÂN TÍCH Thống kê phát triển thuê bao theo thời gian select ROW_NUMBER() OVER (ORDER BY donvi_ql desc) stt, ten_dv, pt PhatTrien,tl ThanhLy, pt-tl PhatSinh from ( SELECT a.donvi_ql, b.ten_dv, sum(decode(c.loaihd_id,1,1,0)) pt, sum(decode(c.loaihd_id,4,1,0)) tl FROM wh_ccbs.phattrien_tb a, wh_ccbs.donvi_ql b, wh_ccbs.kieu_yc c, wh_ccbs.thoigian d, wh_ccbs.dichvu_vt e where a.donvi_ql = b.donvi_id and a.kieu_yc = c.kieuyc_id and a.thoigian = d.dimension_key and a.dichvu_vt = e.loaitb_id and d.day_day_code>={?ngay_bd} and d.day_day_code<={?ngay_kt} and b.vttinh_id = {?vttinh_id} and e.dichvuvt_id = {?dichvuvt_id} group by a.donvi_ql, b.ten_dv ) Thống kê PTTB trong chương trình khuyến mãi select ROW_NUMBER() OVER (ORDER BY donvi_ql desc) stt, ten_dv, sum(pt) PhatTrien,sum(tl) ThanhLy, sum(pt)-sum(tl) PhatSinh from ( SELECT a.donvi_ql, b.ten_dv, count(1) pt, 0 tl FROM wh_ccbs.phattrien_tb a, wh_ccbs.donvi_ql b, wh_ccbs.kieu_yc c, wh_ccbs.thoigian d, wh_ccbs.dichvu_vt e where a.donvi_ql = b.donvi_id and a.kieu_yc = c.kieuyc_id and a.thoigian = d.dimension_key and a.dichvu_vt = e.loaitb_id and a.chuongtrinh_km = {?ctkm_id} and b.vttinh_id = {?vttinh_id} and e.dichvuvt_id = {?dichvuvt_id} and c.loaihd_id = 1 group by a.donvi_ql, b.ten_dv union all SELECT a.donvi_ql, b.ten_dv, 0 pt, count(1) tl FROM wh_ccbs.phattrien_tb a, wh_ccbs.donvi_ql b, wh_ccbs.kieu_yc c, wh_ccbs.thoigian d, wh_ccbs.dichvu_vt e where a.donvi_ql = b.donvi_id and a.kieu_yc = c.kieuyc_id and a.thoigian = d.dimension_key and a.dichvu_vt = e.loaitb_id and d.day_day_code>=(select to_char(max(ngay_bd),'yyyyMMdd') from wh_ccbs.chuongtrinh_km where ctkm_id = {?ctkm_id}) and d.day_day_code<=(select to_char(max(ngay_kt),'yyyyMMdd') from wh_ccbs.chuongtrinh_km where ctkm_id = {?ctkm_id}) and b.vttinh_id = {?vttinh_id} and e.dichvuvt_id = {?dichvuvt_id} and c.loaihd_id = 4 group by a.donvi_ql, b.ten_dv ) a group by a.donvi_ql, a.ten_dv Thống kê doanh thu SELECT ROW_NUMBER() OVER (ORDER BY a.donvi_ql) stt, c.ten_dv, sum(a.socuoc) cuoc, sum(a.tien) tien, sum(a.vat) vat, sum(a.tong) tong FROM wh_ccbs.mv_thongke_doanhthu a, wh_ccbs.thang b, wh_ccbs.donvi_ql c where a.thang = b.dimension_key and a.donvi_ql = c.donvi_id and c.vttinh_id = {?vttinh_id} and b.calendar_year_cal_year_code = {?nam} and decode({?quy},0,0,b.calendar_quart_cal_quarter_co) = {?quy} and decode({?thang},0,0,b.calendar_month_cal_month_code) = {?thang} group by a.donvi_ql, c.ten_dv Thống kê doanh thu sản phẩm SELECT ROW_NUMBER() OVER (ORDER BY c.dichvuvt_id) stt, c.dichvuvt_id, c.ten_dvvt, sum(a.socuoc) cuoc, sum(a.tien) tien, sum(a.vat) vat, sum(a.tong) tong FROM wh_ccbs.mv_thongke_doanhthu a, wh_ccbs.thang b, wh_ccbs.dichvu_vt c, wh_ccbs.donvi_ql d where a.thang = b.dimension_key and a.dichvu_vt = c.loaitb_id and a.donvi_ql = d.donvi_id and d.vttinh_id = {?vttinh_id} and b.calendar_year_cal_year_code = {?nam} and decode({?quy},0,0,b.calendar_quart_cal_quarter_co) = {?quy} and decode({?thang},0,0,b.calendar_month_cal_month_code) = {?thang} group by c.dichvuvt_id, c.ten_dvvt Danh sách khách hàng có doanh thu cao nhất select * from ( SELECT ROW_NUMBER() OVER (ORDER BY a.tong desc) stt, b.ma_kh, b.ten_kh, b.diachi_kh, a.tien, a.vat, a.tong FROM wh_ccbs.mv_tonghop_cuoc_sd_kh PARTITION(P{?thang}) a, wh_ccbs.khachhang b where a.khachhang = b.khachhang_id order by a.tong desc ) where stt<={?soluong} Thống kê thanh toán SELECT ROW_NUMBER() OVER (ORDER BY a.donvi_ql desc) stt, c.ten_dv, sum(a.hoahong) cuoc, sum(a.tien) tien, sum(a.vat) vat, sum(a.tong) tong FROM wh_ccbs.mv_thongke_tien_tt a, wh_ccbs.thang b, wh_ccbs.donvi_ql c where a.thang = b.dimension_key and a.donvi_ql = c.donvi_id and c.vttinh_id = {?vttinh_id} and b.calendar_year_cal_year_code = {?nam} and decode({?quy},0,0,b.calendar_quart_cal_quarter_co) = {?quy} and decode({?thang},0,0,b.calendar_month_cal_month_code) = {?thang} group by a.donvi_ql, c.ten_dv Thống kê nợ SELECT ROW_NUMBER() OVER (ORDER BY a.donvi_ql desc) stt, c.ten_dv, sum(a.hoahong) cuoc, sum(a.tien) tien, sum(a.vat) vat, sum(a.tong) tong FROM wh_ccbs.mv_thongke_tien_no a, wh_ccbs.thang b, wh_ccbs.donvi_ql c where a.thang = b.dimension_key and a.donvi_ql = c.donvi_id and c.vttinh_id = {?vttinh_id} and b.calendar_year_cal_year_code = {?nam} and decode({?quy},0,0,b.calendar_quart_cal_quarter_co) = {?quy} and decode({?thang},0,0,b.calendar_month_cal_month_code) = {?thang} group by a.donvi_ql, c.ten_dv Thống kê khiếu nại khách hàng SELECT ROW_NUMBER() OVER (ORDER BY c.nhomkn_id desc) stt, c.nhom_kn, count(1) sl FROM wh_ccbs.khieunai a, wh_ccbs.thoigian b, wh_ccbs.nhom_kn c, wh_ccbs.dichvu_vt d where a.thoigian = b.dimension_key and a.nhom_kn = c.loaikn_id and b.calendar_month_cal_month_code = {?thang} and a.dichvu_vt = d.loaitb_id and d.dichvuvt_id = {?dichvuvt_id} group by c.nhomkn_id, c.nhom_kn Thống kê sử dụng dịch vụ theo địa chỉ SELECT ROW_NUMBER() OVER (ORDER BY c.ten_huyen,c.ten_phuong desc) stt, c.ten_phuong, c.ten_huyen, count(1) sl FROM wh_ccbs.sudung_dvvt a, wh_ccbs.donvi_ql b, wh_ccbs.diachi c, wh_ccbs.dichvu_vt d where a.diachi = c.phuong_id and a.dichvu_vt = d.loaitb_id and a.donvi_ql = b.donvi_id and b.vttinh_id = {?vttinh_id} and d.dichvuvt_id = {?dichvuvt_id} and decode({?quan_id},0,0,c.quan_id) = {?quan_id} group by c.ten_phuong, c.ten_huyen Thống kê báo hỏng SELECT ROW_NUMBER() OVER (ORDER BY c.tinhtrang_id desc) stt, c.tinhtrang, count(1) sl FROM wh_ccbs.baohong a, wh_ccbs.thoigian b, wh_ccbs.tinhtrang c, wh_ccbs.dichvu_vt d where a.thoigian = b.dimension_key and a.tinhtrang = c.tinhtrang_id and b.calendar_month_cal_month_code = {?thang} and a.dichvu_vt = d.loaitb_id and d.dichvuvt_id = {?dichvuvt_id} group by c.tinhtrang_id, c.tinhtrang,b.calendar_month_cal_month_code