public static void Prc_delete()
{
CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ);
string _query = @"DELETE FROM tb_temp_dchieu
WHERE mau = 'VAT-APP'";
_ora.TransStart();
try
{
_ora.TransExecute(_query);
_ora.TransCommit();
}
catch (Exception e) {
throw new Exception("Có lỗi khi xóa dữ liệu: " + e.Message.ToString());
_ora.TransRollBack();
}
finally {
_ora.close();
}
}
public static int Fnc_copy_file_dtnt_ftp(string p_forder,
DirectoryInfo p_dir_destination,
string p_short_name,
Forms.Frm_QLCD p_frm_qlcd,
string p_server,
string p_username,
string p_password,
string p_filename)
{
using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ))
{
// Biến lưu trữ trên hàm hoặc thủ tục
string v_pck = "FNC_COPY_FILE_DTNT";
// Biến xác định ghi logs khi thực hiện thành công
string logs = "";
// Biến lưu câu lệnh sql
string _query = "";
// Biến lưu trữ số file đã copy về máy
int _so_file = 1;
_ora.TransStart();
try
{
#region Copy files
//Create a WebClient.
WebClient request = new WebClient();
//Declare Ftp parameters
/* string FtpServer = "ftp://10.15.117.98/aiun/BPH_DBVAT_707/DTNT/";
string FtpUserName = "******";
string FtpPassword = "******";*/
//Setup our credentials
request.Credentials = new NetworkCredential(p_username, p_password);
try
{
//Download the data into a Byte array
byte[] fileData =
request.DownloadData(p_server + p_forder + "/" + p_filename);
//Create a FileStream that we'll write the
// byte array to.
FileStream file =
File.Create(p_dir_destination + "\\" +
p_filename);
//Write the full byte array to the file.
file.Write(fileData, 0, fileData.Length);
file.Close();
// _so_file++;
/* }
catch (FormatException e)
{
MessageBox.Show(e.Message);
}
}*/
}
catch (FormatException e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + ": " + e.Message);
logs = "E";
return -1;
}
// MessageBox.Show("Download complete");
#endregion
}
catch (Exception e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + ": " + e.Message);
// Ghi log
_query = null;
_query +=
"call PCK_TRACE_LOG.prc_ins_log_vs('" + p_short_name + "', '"
+ v_pck + "', 'N', '";
_query += e.Message.ToString().Replace("'", "\"") + "')";
_ora.TransExecute(_query);
_ora.TransRollBack();
return -1;
}
if (logs != "E")
{
// Ghi log
_query = null;
_query = "call PCK_TRACE_LOG.prc_ins_log_vs('" + p_short_name + "', '"
+ v_pck + "', 'Y', null)";
_ora.TransExecute(_query);
_ora.TransCommit();
}
return _so_file;
}
}
public static void Prc_import_psqt(DirectoryInfo p_dir_source, string p_path)
{
CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ);
string _query = "";
string _search_pattern = "PSQT.DBF";
_ora.TransStart();
try
{
ArrayList _listFile = new ArrayList();
_listFile.AddRange(p_dir_source.GetFiles(_search_pattern));
foreach (FileInfo _file in _listFile)
{
try
{
_query = @"SELECT short_name, matkhai, tien from {0}";
_query = _query.Replace("{0}", _file.Name.ToString());
CLS_DBASE.FOX _connFoxPro = new CLS_DBASE.FOX(p_path);
DataTable _dt = _connFoxPro.exeQuery(_query);
foreach (DataRow _dr in _dt.Rows)
{
_query = @"INSERT INTO tb_temp_dchieu ( short_name,
mau,
v_char1,
v_char2,
v_char3,
loai)
VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}')";
_query = _query.Replace("{1}", _dr["short_name"].ToString());
_query = _query.Replace("{2}", "VAT-APP");
_query = _query.Replace("{3}", _dr["matkhai"].ToString());
_query = _query.Replace("{4}", String.Format("{0:#,0}", Double.Parse(_dr["tien"].ToString())));
_query = _query.Replace("{5}", "");
_query = _query.Replace("{6}", "PS");
_ora.TransExecute(_query);
}
_dt.Clear();
_dt = null;
_connFoxPro.close();
}
catch (FormatException e)
{
_ora.TransRollBack();
throw new FormatException("Có lỗi khi import file psqt.dbf: " + e.Message.ToString());
}
catch (Exception e)
{
_ora.TransRollBack();
throw new Exception("Có lỗi khi import file psqt.dbf: " + e.Message.ToString());
}
}
_ora.TransCommit();
_listFile.Clear();
_listFile = null;
}
catch (Exception e)
{
_ora.TransRollBack();
throw new Exception("Có lỗi khi import file psqt.dbf: " + e.Message.ToString());
}
finally
{
_ora.close();
}
}
// Hàm đọc dữ liệu con khau tru 01/GTGT
public static int Fnc_doc_file_ckt_01_old(string p_short_name,
string p_tax_name,
string p_tax_code,
DateTime p_ky_chot,
string p_path,
DirectoryInfo p_dir_source,
Forms.Frm_QLCD p_frm_qlcd
)
{
string flages = "YES";
//using (CLS_DBASE.ORA _connOra_no = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ))
using (CLS_DBASE.ORA _connOra_no = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQVATW))
{
string _query = "";
// Biến lưu trữ tên của hàm hoặc thủ tục
string v_pck = "FNC_DOC_FILE_CKT_01";
// Biến lưu số bản ghi đã được bổ sung vào bảng TB_NO
int _rowsnum = 0;
string _File_Nghi = "Nghi" + p_ky_chot.ToString("yyyy") + ".DBF";
// Biến lưu mô tả lỗi, ngoại lệ trong quá trình đọc file dữ liệu
string _error_message = "";
#region Docfile ckt_01/GTGT
// File
string _search_pattern = "ST" + p_ky_chot.ToString("MMyyyy") + ".DBF";
// Đối tượng lưu trữ các file dữ liệu
ArrayList _listFile = new ArrayList();
// Lấy danh sách các file dữ liệu
_listFile.AddRange(p_dir_source.GetFiles(_search_pattern));
foreach (FileInfo _file in _listFile)
{
if (_file.Name.Length != 12)
continue;
_query = @"SELECT a.madtnt as tin,
max(c.machuong) as machuong,
max(c.makhoan) as makhoan,
max(a.KyKKhai) as KyKKhai,
max(a.HanNop) as HanNop,
a.MaTM as MaTMuc,
sum(a.KTTHTRUOC + a.sodcqt)*(-1) as SoTien
FROM {0} a
INNER JOIN
DTNT2.DBF as c
ON a.madtnt = c.madtnt
WHERE (KTTHTRUOC + sodcqt) < 0
and Allt(a.madtnt) not in (select Allt(MaDTNT) from {3} where empty(denngay))
GROUP BY a.madtnt, a.MaTM
";
_query = _query.Replace("{0}", _file.Name);
_query = _query.Replace("{3}", _File_Nghi);
CLS_DBASE.FOX _connFoxPro = new CLS_DBASE.FOX(p_path);
// Chứa dữ liệu
DataTable _dt = _connFoxPro.exeQuery(_query);
// int countf = 0;
foreach (DataRow _dr in _dt.Rows)
{
#region Xác định kỳ phát sinh của đối tượng nộp thuế
// Biến lưu trữ kỳ kê khai lấy từ file dữ liệu
string _ky_kkhai = _dr["KyKKhai"].ToString().Replace("/", "").Trim();
if (_ky_kkhai.Length < 6)
_ky_kkhai = p_ky_chot.ToString("MMyyyy");
//Nếu kỳ kê khai trước tháng 1/2005 thì chuyển thành 1/2005
try
{
if (Int32.Parse(_ky_kkhai.Substring(2, 4)) < 2005)
_ky_kkhai = "012005";
}
catch (FormatException e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + "/ " + v_pck + ": " + e.Message);
_error_message += e.Message + "(" + _file.Name + ");";
continue;
}
catch (Exception e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + "/ " + v_pck + ": " + e.Message);
_error_message += e.Message + "(" + _file.Name + ");";
continue;
}
// Ngày bắt đầu kỳ phát sinh
DateTime _kykk_tu_ngay;
// Ngày kết thúc kỳ phát sinh
DateTime _kykk_den_ngay;
try
{
_kykk_tu_ngay = new DateTime(Int32.Parse(_ky_kkhai.Substring(2, 4)), Int32.Parse(_ky_kkhai.Substring(0, 2)), 1);
_kykk_den_ngay =
new DateTime(Int32.Parse(_ky_kkhai.Substring(2, 4)), Int32.Parse(_ky_kkhai.Substring(0, 2)), 1);
_kykk_den_ngay = _kykk_den_ngay.AddMonths(1).AddDays(-1);
}
catch (FormatException e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + "/ " + v_pck + ": " + e.Message);
_error_message += e.Message + "(" + _file.Name + ");";
continue;
}
catch (Exception e)
{
p_frm_qlcd.AddToListView(0, " + " + p_short_name + "/ " + v_pck + ": " + e.Message);
_error_message += e.Message + "(" + _file.Name + ");";
continue;
}
#endregion
string matin = _dr["tin"].ToString().Trim();
if (matin.Length > 10)
{
matin = matin.Insert(10, "-");
}
_query = @"INSERT INTO tb_con_kt
(STT,
TIN,
tax_model,
ma_cqt,
ma_tkhai,
ma_tkhai_tms,
short_name,
ma_chuong,
ma_khoan,
ma_tmuc,
KYKK_TU_NGAY,
KYKK_DEN_NGAY,
han_nop,
ngay_htoan,
so_tien,
tkhoan
)
VALUES ({0}, '{1}', '{2}', '{3}', '{4}','{5}','{6}','{7}',
'{8}','{9}','{10}','{11}','{12}','{13}',{14},'{15}')";
_query = _query.Replace("{0}", _rowsnum.ToString());
_query = _query.Replace("{1}", matin);
_query = _query.Replace("{2}", "VAT-APP");
_query = _query.Replace("{3}", p_tax_code);
_query = _query.Replace("{4}", "01/GTGT");
_query = _query.Replace("{5}", "0026");
_query = _query.Replace("{6}", p_short_name);
_query = _query.Replace("{7}", _dr["machuong"].ToString().Trim());
_query = _query.Replace("{8}", _dr["makhoan"].ToString().Trim());
_query = _query.Replace("{9}", _dr["MaTMuc"].ToString().Trim());
_query = _query.Replace("{10}", _kykk_tu_ngay.ToString("dd/MM/yyyy"));
_query = _query.Replace("{11}", _kykk_den_ngay.ToString("dd/MM/yyyy"));
_query = _query.Replace("{12}", ((DateTime)_dr["HanNop"]).ToString("dd/MM/yyyy").Trim());
_query = _query.Replace("{13}", p_ky_chot.ToString("dd/MM/yyyy"));
_query = _query.Replace("{14}", _dr["SoTien"].ToString().Trim());
_query = _query.Replace("{15}", "TK_NGAN_SACH");
if (_connOra_no.exeUpdate(_query) != 0)
_rowsnum++;
}
_connFoxPro.close();
_dt.Clear();
_dt = null;
}
_listFile.Clear();
_listFile = null;
if (flages != "No")
{
// Ghi log
_connOra_no.TransStart();
_query = null;
// Modify by ManhTV3 on 30.05.2012
if (_error_message.Length == 0)
{
_query = "call PCK_TRACE_LOG.prc_ins_log_vs('" + p_short_name + "', '"
+ v_pck + "', 'Y', null)";
_connOra_no.TransExecute(_query);
_connOra_no.TransCommit();
}
else
{
_query = "call PCK_TRACE_LOG.prc_ins_log_vs('" + p_short_name + "', '"
+ v_pck + "', 'N', '" + _error_message + "')";
_connOra_no.TransExecute(_query);
_connOra_no.TransRollBack();
}
}
#endregion
return _rowsnum;
}
}