/*
* 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);
}
}
}