/* * Modify by ManhTV3 on 27/04/2012 * Kết xuất file excel báo cáo tiến độ chuẩn hóa * */ public static void Prc_Bcao_Chuan_Hoa(string p_sourcePath, string p_destinPath) { Microsoft.Office.Interop.Excel.Application _excelApp; _excelApp = new Microsoft.Office.Interop.Excel.Application(); // Mở file mẫu excel Microsoft.Office.Interop.Excel.Workbook workBook = _excelApp.Workbooks.Open(p_sourcePath, //Filename Type.Missing, //UpdateLinks Type.Missing, //ReadOnly Type.Missing, //Format Type.Missing, //Password Type.Missing, //WriteResPassword Type.Missing, //IgnoreReadOnlyRecommended Type.Missing, //Origin Type.Missing, //Delimiter Type.Missing, //Editable Type.Missing, //Notify Type.Missing, //Converter Type.Missing, //AddToMru Type.Missing, //Local Type.Missing); //CorruptLoad // Lấy dữ liệu using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { try { _ora.TransStart(); // Kết xuất báo cáo tiến độ chuẩn hóa string _sql = "SELECT * FROM vw_bc_ch;"; DataTable _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "BaoCaoChuanHoa", _dt); _dt.Clear(); workBook.SaveAs(p_destinPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //_ora.TransCommit(); } finally { // Đóng file mẫu excel workBook.Close(false, p_sourcePath, null); _excelApp.Quit(); CLS_EXCEL.Prc_releaseObject(workBook); CLS_EXCEL.Prc_releaseObject(_excelApp); } } }
public static void Prc_ChiTiet(string p_sourcePath, string p_destinPath, string p_short_name) { Microsoft.Office.Interop.Excel.Application _excelApp; _excelApp = new Microsoft.Office.Interop.Excel.Application(); // Mở file mẫu excel Microsoft.Office.Interop.Excel.Workbook workBook = _excelApp.Workbooks.Open(p_sourcePath, //Filename Type.Missing, //UpdateLinks Type.Missing, //ReadOnly Type.Missing, //Format Type.Missing, //Password Type.Missing, //WriteResPassword Type.Missing, //IgnoreReadOnlyRecommended Type.Missing, //Origin Type.Missing, //Delimiter Type.Missing, //Editable Type.Missing, //Notify Type.Missing, //Converter Type.Missing, //AddToMru Type.Missing, //Local Type.Missing); //CorruptLoad // Lấy dữ liệu using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { System.Data.DataTable _dt = null; string _sql = null; try { _ora.TransStart(); _ora.TransExecute("call PCK_MOI_TRUONG.Prc_Set_glView('" + p_short_name + "')"); // Kết xuất phát sinh _sql = "SELECT * FROM vw_ct_ps"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_PS", _dt); _dt.Clear(); // Kết xuất số nợ _sql = "SELECT * FROM vw_ct_no"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_NO", _dt); _dt.Clear(); // Kết xuất số tính phạt nộp chậm _sql = "SELECT * FROM VW_CT_TPHAT"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TPHAT", _dt); _dt.Clear(); // Kết xuất chi tiết tờ khai khoán _sql = "SELECT * FROM VW_CT_TKH"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TKH", _dt); _dt.Clear(); // Kết xuất chi tiết tờ khai khoán _sql = "SELECT * FROM VW_CT_CKT"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_CKT", _dt); _dt.Clear(); // Kết xuất đkntk _sql = "SELECT * FROM VW_CT_DKNTK"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_DKNTK", _dt); _dt.Clear(); // Kết xuất chi tiết tkmb _sql = "SELECT * FROM VW_CT_TKMB"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TKMB", _dt); _dt.Clear(); // Kết xuất chi tiết tkmb _sql = "SELECT * FROM VW_CT_01PNN"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TK01PNN", _dt); _dt.Clear(); // Kết xuất chi tiết tkmb _sql = "SELECT * FROM VW_CT_02PNN"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TK02PNN", _dt); _dt.Clear(); workBook.SaveAs(p_destinPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); _ora.TransCommit(); } finally { // Đóng file mẫu excel workBook.Close(false, p_sourcePath, null); _excelApp.Quit(); CLS_EXCEL.Prc_releaseObject(workBook); CLS_EXCEL.Prc_releaseObject(_excelApp); } } }
// Gen biên bản đối chiếu dữ liệu lần 1 public static void Prc_BienBan(string _pathFOpen, string _pathFSave, string _short_name ) { using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { #region HEADER string _query_ps = "SELECT stt, tax_model, ten_tkhai, sotien FROM vw_bc_ps"; string _query_no = "SELECT stt, tax_model, tmt_ma_tmuc, so_no, so_pnop FROM vw_bc_no"; string _query_tcno = "SELECT STT, TAX_MODEL, TEN_TCNO, SO_NO FROM VW_BC_TCNO"; string _query_tphat = "SELECT STT, TAX_MODEL, SL, SOTIEN FROM VW_BC_TPHAT"; string _query_tphat_1 = "SELECT STT, TAX_MODEL FROM VW_BC_TPHAT"; string _query_tkh = "SELECT STT, TAX_MODEL, SL, DOANHTHU, GTTT, THUEPN FROM VW_BC_TKH"; string _query_tkh_1 = "SELECT STT, TAX_MODEL FROM VW_BC_TKH"; string _query_dkntk = "SELECT STT, TAX_MODEL, TEN_TKHAI, SL FROM VW_BC_DKNTK"; string _query_ckt = "SELECT STT, TAX_MODEL, TEN_TKHAI, SOTIEN FROM VW_BC_CKT"; string _query_tkmb = "SELECT STT, TAX_MODEL, ten_bac, SL, SOTIEN FROM VW_BC_TKMB"; string _query_tkmb_1 = "SELECT STT, TAX_MODEL, ten_bac FROM VW_BC_TKMB"; string _query_01pnn = "SELECT stt, TAX_MODEL, ma_tmuc, SL, SOTIEN FROM VW_BC_01PNN"; string _query_01pnn_1 = "SELECT stt, TAX_MODEL, ma_tmuc FROM VW_BC_01PNN"; string _query_02pnn = "SELECT stt, TAX_MODEL, ma_tmuc, SL, SOTIEN FROM VW_BC_02PNN"; string _query_02pnn_1 = "SELECT stt, TAX_MODEL, ma_tmuc FROM VW_BC_02PNN"; const int _row_start_1 = 4; Object _objNULL = System.Reflection.Missing.Value; Object _objTRUE = true; Object _objFALSE = false; String _query = null; DataTable _dt = null; string _txtField = null; // Đóng phiên làm việc Database _ora.TransStart(); // Mở kết nối đến CQT trong các câu lệnh query _query = "call PCK_MOI_TRUONG.prc_set_glview('" + _short_name + "')"; _ora.TransExecute(_query); // Tham số cho thứ tự của table trong WORD int _k; // Tham số cho thứ tự xóa table trong WORD int _z = 0; // Các index của table trong _Document cần xóa ArrayList _arr_delTable = new ArrayList(); // Mở một instance cho MS Word Microsoft.Office.Interop.Word.ApplicationClass _wordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); _wordApp.Visible = false; // Sao chép mẫu biên bản theo _pathFOpen Object _strFile = _pathFOpen; _Document _doc = _wordApp.Documents.Add(ref _strFile, //Template ref _objNULL, //NewTemplate ref _objNULL, //DocumentType ref _objFALSE //Visible ); #endregion #region IN DU LIEU #region In bảng phát sinh _k = 4; _dt = _ora.TransExecute_DataTable(_query_ps); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng nợ _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_no); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng tc nợ _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tcno); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng tính phạt _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tphat); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So CQT quản lý _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tphat_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So sai lệch _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tphat_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng tk khoán _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkh); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //Số CQT quản lý _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkh_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //Số sai lệch _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkh_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng dkntk _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_dkntk); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng còn khấu trừ _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_ckt); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng TKMB _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkmb); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So CQT quan ly _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkmb_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So sai lech _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_tkmb_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng 01 PNN _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_01pnn); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So CQT quan ly _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_01pnn_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So sai lech _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_01pnn_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion #region In bảng 02 pnn _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_02pnn); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So CQT quan ly _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_02pnn); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); //So sai lech _k = _k + 1; _dt = _ora.TransExecute_DataTable(_query_02pnn_1); Prc_fill_tbBienBan(_k, _doc, _dt, _row_start_1); // Add các index của table cần xóa if (_dt.Rows.Count == 0) _arr_delTable.Add(_k); _dt.Reset(); #endregion // Xóa table không cần thiết của _Document foreach (int i in _arr_delTable) { _doc.Tables[i - _z].Delete(); _z++; } #endregion #region END // Tính số trang của biên bản Microsoft.Office.Interop.Word.WdStatistic _stat = Microsoft.Office.Interop.Word.WdStatistic.wdStatisticPages; int _countPage = _doc.ComputeStatistics(_stat, ref _objNULL); // Điền các giá trị MERGE MAIL #region MERGE MAIL _query = @"SELECT * FROM vw_bban_01"; _dt = _ora.TransExecute_DataTable(_query); foreach (Field _docField in _doc.Fields) { Microsoft.Office.Interop.Word.Range _rngFieldCode = _docField.Code; String _fieldText = _rngFieldCode.Text; if (_fieldText.StartsWith(" MERGEFIELD")) { // Kết quả _rngFieldCode.Text có dạng // MERGEFIELD MyFieldName \\* MERGEFORMAT Int32 _endMerge = _fieldText.IndexOf("\\"); Int32 _fieldNameLength = _fieldText.Length - _endMerge; String _fieldName = _fieldText.Substring(11, _endMerge - 11); // thực hiện thay thế các Fields foreach (DataColumn _col in _dt.Columns) { if (_col.ColumnName.ToString().Trim().ToLower().Equals(_fieldName.Trim().ToLower())) { foreach (DataRow _row in _dt.Rows) { _docField.Select(); _txtField = _row[_col].ToString(); //_txtField = _row[_col].ToString() + (_fieldName.Trim() == "fld_CQT1" ? " giữ 01 bản, " : ""); if ((_fieldName.Trim().ToUpper() == "fld_tax_model".ToUpper()) & _row[_col].ToString().ToUpper().Equals("QCT")) _txtField = @"QLT\QCT"; if (_fieldName.Trim().ToUpper() == "fld_CountPage".ToUpper()) _txtField = _countPage.ToString(); _wordApp.Selection.TypeText(CLS_FONT.Fnc_TCVN3ToUNICODE(_txtField)); } } } } } _dt.Reset(); #endregion // Save tài liệu Object _pathSaveFile = _pathFSave + "\\" + _short_name + "_BienBanDoiChieu_01.doc"; _doc.SaveAs(ref _pathSaveFile, //FileName ref _objNULL, //FileFormat ref _objNULL, //LockComments ref _objNULL, //Password ref _objNULL, //AddToRecentFiles ref _objNULL, //WritePassword ref _objNULL, //ReadOnlyRecommended ref _objNULL, //EmbedTrueTypeFonts ref _objNULL, //SaveNativePictureFormat ref _objNULL, //SaveFormsData ref _objNULL, //SaveAsAOCELetter ref _objNULL, //Encoding ref _objNULL, //InsertLineBreaks ref _objNULL, //AllowSubstitutions ref _objNULL, //LineEnding ref _objNULL //AddBiDiMarks ); // Đóng tài liệu _doc.Close(ref _objFALSE, ref _objNULL, ref _objNULL); // Thoát WORD _wordApp.Quit(ref _objNULL, ref _objNULL, ref _objNULL); // Đóng phiên làm việc Database _ora.TransCommit(); } #endregion }
public static void Prc_Ktra_Kchot(string p_short_name) { string _query = null; DataTable _dt = null; string _tax_model = "QLT"; using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { _ora.TransStart(); _query = @"SELECT tax_model FROM tb_lst_taxo WHERE short_name='&1'"; _query = _query.Replace("&1", p_short_name); _dt = _ora.TransExecute_DataTable(_query); if (_dt.Rows.Count > 0) { _tax_model = _dt.Rows[0]["tax_model"].ToString(); } _dt.Reset(); _query = @"SELECT (SELECT NVL(TO_CHAR(KY_CHOT,'MM/RRRR'),'NULL') FROM tb_lst_taxo WHERE short_name='&1') KYCHOT_DC, (SELECT TO_CHAR(MAX(kylb_den_ngay),'MM/RRRR') FROM qlt_sothue_lock@qlt_&1 WHERE loai_so='ST1B') KYKHOASO_CQT, (SELECT TO_CHAR(MAX(kyno_tu_ngay),'MM/RRRR') FROM qlt_so_no@qlt_&1) KYNO_QLT, (SELECT TO_CHAR(MAX(kyno_tu_ngay),'MM/RRRR') FROM &2_so_no@qlt_&1) KYNO_QCT FROM dual"; _query = _query.Replace("&1", p_short_name); _query = _query.Replace("&2", _tax_model); _dt = _ora.TransExecute_DataTable(_query); if (_dt.Rows.Count > 0) { if ( _dt.Rows[0]["KYCHOT_DC"].ToString().Equals(_dt.Rows[0]["KYCHOT_DC"].ToString()) & _dt.Rows[0]["KYCHOT_DC"].ToString().Equals(_dt.Rows[0]["KYKHOASO_CQT"].ToString()) & _dt.Rows[0]["KYCHOT_DC"].ToString().Equals(_dt.Rows[0]["KYNO_QLT"].ToString()) & _dt.Rows[0]["KYCHOT_DC"].ToString().Equals(_dt.Rows[0]["KYNO_QCT"].ToString()) ) { _query = null; _query = "call PCK_TRACE_LOG.prc_ins_log_vs('&1', 'PRC_KTRA_KCHOT', 'Y', null)"; _query = _query.Replace("&1", p_short_name); _ora.TransExecute(_query); } else { _query = null; _query = "call PCK_TRACE_LOG.prc_ins_log_vs('&1', 'PRC_KTRA_KCHOT', 'N', '&2')"; _query = _query.Replace("&1", p_short_name); _query = _query.Replace("&2", "KYCHOT_DC: " + _dt.Rows[0]["KYCHOT_DC"].ToString() + " " + "KYKHOASO_CQT: " + _dt.Rows[0]["KYKHOASO_CQT"].ToString() + " " + "KYNO_QLT: " + _dt.Rows[0]["KYNO_QLT"].ToString() + " " + "KYNO_QCT(cho QCT): " + _dt.Rows[0]["KYNO_QCT"].ToString()); _ora.TransExecute(_query); } } _ora.TransCommit(); } }
/** * Thuc hien lay du lieu va ket xuat file sai lech * @author Administrator * @date September 09, 2013 * @param p_sourcePath * @param p_destinPath * @param p_short_name * */ public static void Prc_SaiLech(string p_sourcePath, string p_destinPath, string p_short_name) { Microsoft.Office.Interop.Excel.Application _excelApp; _excelApp = new Microsoft.Office.Interop.Excel.Application(); // Mở file mẫu excel Microsoft.Office.Interop.Excel.Workbook workBook = _excelApp.Workbooks.Open(p_sourcePath, //Filename Type.Missing, //UpdateLinks Type.Missing, //ReadOnly Type.Missing, //Format Type.Missing, //Password Type.Missing, //WriteResPassword Type.Missing, //IgnoreReadOnlyRecommended Type.Missing, //Origin Type.Missing, //Delimiter Type.Missing, //Editable Type.Missing, //Notify Type.Missing, //Converter Type.Missing, //AddToMru Type.Missing, //Local Type.Missing); //CorruptLoad // Lấy dữ liệu using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { System.Data.DataTable _dt = null; string _sql = null; try { _ora.TransStart(); _ora.TransExecute("call PCK_MOI_TRUONG.Prc_Set_glView('" + p_short_name + "')"); // Kết xuất phát sinh _sql = "SELECT * FROM vw_sl_ps"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_PS", _dt); _dt.Clear(); // Kết xuất số nợ _sql = "SELECT * FROM vw_sl_no"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_NO", _dt); _dt.Clear(); // Kết xuất đăng ký nộp tờ khai quyết toán _sql = "SELECT * FROM vw_sl_dkntk"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_DKNTK", _dt); _dt.Clear(); // Kết xuất tờ khai thuế môn bài _sql = "SELECT * FROM vw_sl_tkmb"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TKMB", _dt); _dt.Clear(); // Kết xuất dữ liệu còn khấu trừ _sql = "SELECT * FROM vw_sl_con_kt"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_CON_KT", _dt); _dt.Clear(); // Kết xuất dữ liệu 01_thkh _sql = "SELECT * FROM vw_sl_01_thkh"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_01_THKH", _dt); _dt.Clear(); // Kết xuất dữ liệu 01/TK-SDDPNN _sql = "SELECT * FROM vw_sl_TK_SDDPNN where rownum < 100"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TK_SDDPNN", _dt); _dt.Clear(); // Kết xuất dữ liệu tính phạt _sql = "SELECT * FROM vw_sl_tinh_phat"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "DuLieu_TINH_PHAT", _dt); _dt.Clear(); // Kết xuất chi tiết sai lệch sổ nợ và sổ thu nộp _sql = "SELECT * FROM vw_sl_tn"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "SaiLechNo", _dt); _dt.Clear(); /* // Kết xuất chi tiết sai lệch mã số thuế trạng thái lỗi hoặc chưa có ngày đăng ký nhưng có phát sinh thuế _sql = "SELECT * FROM vw_sl_mst"; _dt = _ora.TransExecute_DataTable(_sql); if (_dt.Rows.Count > 0) CLS_EXCEL.Prc_Add_Sheets(workBook, "SaiLechMST", _dt); _dt.Clear(); */ workBook.SaveAs(p_destinPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); _ora.TransCommit(); } finally { // Đóng file mẫu excel workBook.Close(false, p_sourcePath, null); _excelApp.Quit(); CLS_EXCEL.Prc_releaseObject(workBook); CLS_EXCEL.Prc_releaseObject(_excelApp); } } }
//Prc_ChuyenDoiLoi public static void Prc_ChuyenDoiLoi(string p_sourcePath, string p_destinPath, string p_short_name) { string _query_pluc01 = "SELECT a.tax_model, a.ma_tkhai, a.tin, a.KY_PSINH_TU, " + "a.KY_PSINH_DEN, a.so_tien, a.err_name FROM vw_cd_ps a"; string _query_pluc02 = "SELECT a.tax_model, a.tmt_ma_tmuc, a.tin, a.kykk_tu_ngay, " + "a.kykk_den_ngay, a.han_nop, a.so_duong, a.so_am, a.err_name FROM vw_cd_no a"; string _query_pluc03 = "SELECT a.tax_model, a.tmt_ma_tmuc, a.tin, a.kykk_tu_ngay, " + "a.kykk_den_ngay, a.han_nop, a.ten_tc, a.so_tien, a.err_name FROM vw_cd_tc_no a"; string _query_pluc04 = "SELECT a.tax_model, a.tin, a.han_nop, a.so_tien, a.err_name FROM vw_cd_tphat a"; string _query_pluc05 = "SELECT a.tax_model, a.tin, a.kytt_tu_ngay, " + "a.kytt_den_ngay, a.doanh_thu, a.gt_tinh_thue, a.thue_pn, a.err_name FROM vw_cd_cctt a"; string _query_pluc06 = "SELECT a.tax_model, a.tin, a.ma_tkhai, a.err_name FROM vw_cd_dkntk a"; string _query_pluc07 = "SELECT a.tax_model, a.tin, a.kykk_tu_ngay, " + "a.kykk_den_ngay, a.so_tien, a.err_name FROM vw_cd_ckt a"; string _query_pluc08 = "SELECT a.tax_model, bmb_nnt_tms, a.tin, a.kytt_tu_ngay, " + "a.kytt_den_ngay, a.han_nop, a.so_tien, a.err_name FROM vw_cd_tkmb a"; string _query_pluc09 = "SELECT a.tax_model, a.ma_tmuc, a.tin, a.kytt_tu_ngay, a.kytt_den_ngay," + "a.ma_tkhai, a.ngay_nop_tk, a.sthue_pnop, a.err_name FROM vw_cd_01pnn a"; string _query_pluc10 = "SELECT a.tax_model, a.ma_tmuc, a.tin, a.kytt_tu_ngay, a.kytt_den_ngay," + "a.ma_tkhai, a.ngay_nop_tk, a.sthue_pnop, a.err_name FROM vw_cd_02pnn a"; string _sheet_name_pl01 = "Phuluc01"; string _sheet_name_pl02 = "Phuluc02"; string _sheet_name_pl03 = "Phuluc03"; string _sheet_name_pl04 = "Phuluc04"; string _sheet_name_pl05 = "Phuluc05"; string _sheet_name_pl06 = "Phuluc06"; string _sheet_name_pl07 = "Phuluc07"; string _sheet_name_pl08 = "Phuluc08"; string _sheet_name_pl09 = "Phuluc09"; string _sheet_name_pl10 = "Phuluc10"; Microsoft.Office.Interop.Excel.Application _excelApp; _excelApp = new Microsoft.Office.Interop.Excel.Application(); // Mở file mẫu excel Microsoft.Office.Interop.Excel.Workbook workBook = _excelApp.Workbooks.Open(p_sourcePath, //Filename Type.Missing, //UpdateLinks Type.Missing, //ReadOnly Type.Missing, //Format Type.Missing, //Password Type.Missing, //WriteResPassword Type.Missing, //IgnoreReadOnlyRecommended Type.Missing, //Origin Type.Missing, //Delimiter Type.Missing, //Editable Type.Missing, //Notify Type.Missing, //Converter Type.Missing, //AddToMru Type.Missing, //Local Type.Missing); //CorruptLoad // Lấy dữ liệu using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ)) { System.Data.DataTable _dt = null; int _kt1 = 0; try { _ora.TransStart(); _ora.TransExecute("call PCK_MOI_TRUONG.Prc_Set_glView('" + p_short_name + "')"); // Kết xuất phụ lục _dt = _ora.TransExecute_DataTable(_query_pluc01); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl01, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc02); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl02, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc03); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl03, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc04); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl04, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc05); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl05, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc06); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl06, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc07); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl07, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc08); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl08, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc09); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl09, _dt); } _dt = _ora.TransExecute_DataTable(_query_pluc10); if (_dt.Rows.Count > 0) { _kt1 = 1; CLS_EXCEL.Prc_Add_Sheets(workBook, _sheet_name_pl10, _dt); } if (_kt1 == 1) { workBook.SaveAs(p_destinPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } _dt.Clear(); _ora.TransCommit(); } finally { // Đóng file mẫu excel workBook.Close(false, p_sourcePath, null); _excelApp.Quit(); CLS_EXCEL.Prc_releaseObject(workBook); CLS_EXCEL.Prc_releaseObject(_excelApp); } } }