public bool _ExportExportCustom(TM.Connection.Oracle Oracle, TM.Connection.OleDBF FoxPro, Common.DefaultObj obj, Models.EXPORT_CUSTOM EXPORT_CUSTOM)
{
try
{
//Remove Old File
TM.IO.FileDirectory.Delete($"{obj.DataSource}{EXPORT_CUSTOM.TABLE_NAME}.dbf", false);
//Create New File
FoxPro.Connection.Query(EXPORT_CUSTOM.QUERY_CREATE);
var data = Oracle.Connection.Query(EXPORT_CUSTOM.QUERY_SELECT.Replace("$kyhoadon", obj.KYHD.ToString())).ToList();
var qry = $"SELECT * FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}' AND TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM}";
var EXPORT_TABLE = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
var ColumnExport = new Dictionary <string, string>();
FoxPro.Connection.InsertList3(EXPORT_CUSTOM.TABLE_NAME, EXPORT_TABLE, data);
//Execute Query
FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}");
_ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_END);
//FoxPro.Connection.Query($"USE {EXPORT_CUSTOM.TABLE_NAME}");
//if (EXPORT_CUSTOM.QUERY_END != null)
//{
// var QUERY_END = EXPORT_CUSTOM.QUERY_END.Trim(';').Split(';');
// foreach (var i in QUERY_END)
// FoxPro.Connection.Query(i.Trim().TrimStart('\n').Replace("$table", EXPORT_CUSTOM.TABLE_NAME));
//}
//Delete .BAK
FileManagerController.RemoveFileSource(obj.DataSource, false);
return(true);
}
catch (Exception) { throw; }
}
public JsonResult ExportToDBF(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
FileManagerController.InsertDirectory(obj.DataSource);
obj = getDefaultObj(obj);
FileManagerController.InsertDirectory(obj.DataSource, false);
//
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
try
{
var tmp_qry_fox = "";
var tmp_qry_sql = "";
var condition = "";
var qry = $"SELECT * FROM EXPORT_TABLE WHERE TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.DBF} AND TABLE_NAME='{obj.file}' AND FLAG=1 ORDER BY ORDERS";
var EXPORT_TABLE = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
//
if (EXPORT_TABLE.Count <= 0)
{
return(Json(new { warning = "Vui lòng cấu hình dữ liệu Export trước!" }, JsonRequestBehavior.AllowGet));
}
qry = CreateTable(obj.DataSource, obj.file, EXPORT_TABLE);
FoxPro.Connection.Query(qry);
//
var ColumnExport = new List <string>();
foreach (var i in EXPORT_TABLE)
{
ColumnExport.Add(i.COLUMN_NAME_EXPORT);
tmp_qry_fox += $"{i.COLUMN_NAME_EXPORT},";
tmp_qry_sql += $"{i.COLUMN_NAME},";
if (!string.IsNullOrEmpty(i.CONDITION))
{
condition += MappingCondition(i.COLUMN_TYPE, i.COLUMN_NAME, i.CONDITION) + " AND ";
}
}
//
qry = $"SELECT {tmp_qry_sql.Trim(',')} FROM {obj.file} {(!string.IsNullOrEmpty(condition) ? "WHERE " + condition.Substring(0, condition.Length - 5) : "")}";
var data = Oracle.Connection.Query <dynamic>(qry).ToList();
//FoxPro.Insert(data);
//
qry = $"INSERT INTO {obj.file}({tmp_qry_fox.Trim(',')}) VALUES(";
foreach (var i in data)
{
qry += "";
}
CRUDFoxPro.InsertList(FoxPro.Connection, obj.file, ColumnExport, data);
FileManagerController.InsertFile(obj.DataSource + obj.file + ".DBF", false);
return(Json(new { success = "Xử lý thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); FoxPro.Connection.Close(); }
}
public JsonResult GetReportDetailCustom(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
string msg = "Lấy báo cáo thành công!";
try
{
string qry = $"SELECT * FROM ITEMS WHERE GROUPID={obj.data_id} AND FLAG=1 ORDER BY ORDERS";
var item = SQLServer.Connection.Query <Models.ITEMS>(qry);
var data = new ReportDetailCustom();
foreach (var i in item)
{
if (i.APPKEY == "header")
{
data.hd = i.DESCRIPTION;
data.hc = i.CONTENTS;
}
else if (i.APPKEY == "footer")
{
data.fd = i.DESCRIPTION;
data.fc = i.CONTENTS;
}
else if (i.APPKEY == "content")
{
if (i.QUANTITY == 0)
{
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
data.cd = FoxPro.Connection.Query(i.DESCRIPTION);
data.cc = FixContent(i.CONTENTS);
FoxPro.Close();
}
else if (i.QUANTITY == 1)
{
data.cd = SQLServer.Connection.Query(i.DESCRIPTION);
data.cc = FixContent(i.CONTENTS);
}
}
}
return(Json(new { data = data, datetime = obj.datetime, success = msg }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public bool _ExportQueryExportCustom(TM.Connection.OleDBF FoxPro, string table_name, string query)
{
try
{
if (!string.IsNullOrEmpty(query))
{
var QUERY_END = query.Trim(';').Split(';');
foreach (var i in QUERY_END)
{
FoxPro.Connection.Query(i.Trim().TrimStart('\n').Replace("$table", table_name));
}
}
return(true);
}
catch (Exception) { throw; }
finally { FoxPro.Close(); }
}
public JsonResult XuLyHD(Common.DefaultObj obj, bool?ckhExcept)
{
var ok = new System.Text.StringBuilder();
var err = new System.Text.StringBuilder();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
var hddttime = obj.datetime.ToString("MMyyyy");
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
var indexError = "";
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
try
{
var hdall = "hdall" + obj.time;
var hdallhddt = "hdall_hddt" + obj.time;
TM.OleDBF.DataSource = TM.Common.Directories.HDData + obj.time + "\\";
//Khai báo biến
var listKey = new List <string>();
var NumberToLeter = new TM.Helper.NumberToLeter();
var hasError = false;
string fileName = "hoadon";
string fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf";
string fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml";
string fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip";
string fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml";
//Xóa file HDDT cũ
FileManagerController.DeleteDirFile(fileNameDBFHDDT);
FileManagerController.DeleteDirFile(fileNameZIPFull);
FileManagerController.DeleteDirFile(fileNameXMLError);
//
TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdall + ".dbf", fileNameDBFHDDT);
//Xử lý trùng mã thanh toán khác đơn vị
RemoveDuplicate(TM.OleDBF.DataSource, hdallhddt,
new string[] { "tong_cd", "tong_dd", "tong_net", "tong_tv", "vat", "tong", "kthue", "giam_tru", "tongcong" },
new string[] { "acc_net", "acc_tv", "so_dd", "so_cd", "diachi_tt", "ma_tuyen", "ms_thue", "ma_dt", "ma_cbt", "banknumber" },
"Remove Duplicate hoadon", null);
//Get data from HDDT
var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt);
//HDDT Hoa don
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine();
err.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine();
foreach (var i in data)
{
index++;
indexError = $"Index: {index}, APP_ID: {i.APP_ID}";
#region List Product
//<Product>
//<ProdName>Dịch vụ Internet</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>1</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ MyTv</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Cố định</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>20000</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Di động</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Khuyến mại, giảm trừ</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
#endregion
var check = true;
var MA_TT = i.MA_TT.Replace("-", "");
//Check EzPay
if (i.KIEU_TT == 1)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Check Error
if (string.IsNullOrEmpty(MA_TT))
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty</error></Inv>").AppendLine();
check = false;
hasError = true;
}
else
{
if (listKey.Contains(MA_TT))
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
if (ckhExcept.HasValue)
{
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
else
{
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
}
//Run
if (check)
{
var Products = $"{getProduct("Dịch vụ Internet", i.TONG_NET)}" +
$"{getProduct("Dịch vụ MyTv", i.TONG_TV)}" +
$"{getProduct("Dịch vụ Cố định", i.TONG_CD)}" +
$"{getProduct("Dịch vụ Di động", i.TONG_DD)}" +
$"{getProduct("Khuyến mại, giảm trừ", i.GIAM_TRU)}";
//var so_cd = string.IsNullOrEmpty(dt.Rows[i]["so_cd"].ToString().Trim());
//var so_dd = string.IsNullOrEmpty(dt.Rows[i]["so_dd"].ToString().Trim());
//var acc_net = string.IsNullOrEmpty(dt.Rows[i]["acc_net"].ToString().Trim());
//var acc_tv = string.IsNullOrEmpty(dt.Rows[i]["acc_tv"].ToString().Trim());
//var ma_in = dt.Rows[i]["ma_in"].ToString().Trim();
var Invoice = "<Invoice>" +
$"<MaThanhToan><![CDATA[{getMaThanhToanHD(hddttime, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" +
$"<CusCode><![CDATA[{MA_TT}]]></CusCode>" +
$"<CusName><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></CusName>" +
$"<CusAddress><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.DIACHI_TT)}]]></CusAddress>" +
$"<CusPhone><![CDATA[{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_CD, i.SO_DD)}]]></CusPhone>" +
$"<CusTaxCode>{i.MS_THUE}</CusTaxCode>" +
$"<PaymentMethod>{"TM/CK"}</PaymentMethod>" +
$"<KindOfService>Cước Tháng {obj.month_year_time}</KindOfService>" +
$"<Products>{Products}</Products>" +
$"<Total>{i.TONG}</Total>" +
$"<DiscountAmount>{i.GIAM_TRU}</DiscountAmount>" +
"<VATRate>10</VATRate>" +
$"<VATAmount>{i.VAT}</VATAmount>" +
$"<Amount>{i.TONGCONG}</Amount>" +
$"<AmountInWords><![CDATA[{NumberToLeter.DocTienBangChu(i.TONGCONG, "")}]]></AmountInWords>" +
$"<PaymentStatus>0</PaymentStatus>" +
$"<Extra>{i.MA_CBT};0;0</Extra>" +
$"<ResourceCode>{i.MA_CBT}</ResourceCode>" +
"</Invoice>";
Invoice = $"<Inv><key>{MA_TT}{hddttime}</key>{Invoice}</Inv>";
ok.Append(Invoice).AppendLine();
listKey.Add(MA_TT);
}
if (index % 100 == 0)
{
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
ok = new System.Text.StringBuilder();
}
}
ok.Append("</Invoices>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
if (hasError)
{
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
err.Append("</Invoices>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString()));
outfile.Close();
}
//this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file");
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
fileNameXMLFull
}, fileNameZIPFull, 9);
}
//Insert To File Manager
FileManagerController.DeleteDirFile(fileNameXMLFull);
FileManagerController.InsertFile(fileNameZIPFull);
FileManagerController.InsertFile(fileNameXMLError);
return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { FoxPro.Close(); }
}
public JsonResult XuLyKH(Common.DefaultObj obj)
{
var ok = new System.Text.StringBuilder();
var err = new System.Text.StringBuilder();
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
var hddttime = obj.datetime.ToString("MMyyyy");
// Loại bỏ
obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},";
try
{
var listKey = new List <string>();
var hasError = false;
var fileName = "cus";
var hdallhddt = "hdall_hddt" + obj.time;
var fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf";
var fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml";
var fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip";
var fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml";
//Xóa file HDDT cũ
FileManagerController.DeleteDirFile(fileNameZIPFull);
FileManagerController.DeleteDirFile(fileNameXMLError);
//Get data from HDDT
var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt);
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
ok.Append("<Customers>").AppendLine();
err.Append("<Customers>").AppendLine();
foreach (var i in data)
{
index++;
var MA_TT = i.MA_TT.Replace("-", "");
#region List Product
//<Product>
//<ProdName>Dịch vụ Internet</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>1</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ MyTv</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Cố định</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>20000</Amount>
//</Product>
//<Product>
//<ProdName>Dịch vụ Di động</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
//<Product>
//<ProdName>Khuyến mại, giảm trừ</ProdName>
//<ProdUnit></ProdUnit>
//<ProdQuantity></ProdQuantity>
//<ProdPrice></ProdPrice>
//<Amount>0</Amount>
//</Product>
#endregion
//Check EzPay
var check = true;
if (i.KIEU_TT == 1)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Check Error
if (string.IsNullOrEmpty(MA_TT))
{
err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty HDDT Khách hàng</error></Customer>").AppendLine();
check = false;
hasError = true;
}
else
{
if (listKey.Contains(MA_TT))
{
err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán Khách hàng</error></Customer>").AppendLine();
check = false;
hasError = true;
}
}
if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT}{hddttime},") < 0)
{
err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine();
check = false;
hasError = true;
}
//Run
if (check)
{
var customer = "<Customer>" +
$"<Name><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Name>" +
$"<Code>{MA_TT}</Code>" +
$"<TaxCode>{i.MS_THUE}</TaxCode>" +
$"<Address><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Address>" +
"<BankAccountName></BankAccountName>" +
"<BankName></BankName>" +
$"<BankNumber>{i.BANKNUMBER}</BankNumber>" +
"<Email></Email>" +
"<Fax></Fax>" +
$"<Phone>{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_DD, i.SO_CD)}</Phone>" +
"<ContactPerson></ContactPerson>" +
"<RepresentPerson></RepresentPerson>" +
"<CusType>0</CusType>" +
$"<MaThanhToan><![CDATA[{getMaThanhToanKH(obj.month_year_time, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" +
"</Customer>";
ok.Append(customer).AppendLine();
listKey.Add(MA_TT);
}
if (index % 100 == 0)
{
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
ok = new System.Text.StringBuilder();
}
}
ok.Append("</Customers>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString()));
//
outfile.Close();
}
if (hasError)
{
using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite))
{
err.Append("</Customers>");
outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString()));
outfile.Close();
}
//this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file");
}
//ZipFile
if (obj.ckhZipFile)
{
TM.IO.Zip.ZipFile(new List <string>()
{
fileNameXMLFull
}, fileNameZIPFull, 9);
}
//Insert To File Manager
FileManagerController.DeleteDirFile(fileNameXMLFull);
FileManagerController.InsertFile(fileNameZIPFull);
FileManagerController.InsertFile(fileNameXMLError);
//Insert To File Manager hdallhddt
FileManagerController.InsertFile(fileNameDBFHDDT);
//Delete .BAK
FileManagerController.RemoveFileSource(obj.DataSource, false);
return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); }
finally { FoxPro.Close(); }
}
public JsonResult ExportExportCustom(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
obj = getDefaultObj(obj);
FileManagerController.InsertDirectory(obj.DataSource, false);
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
try
{
var qry = $"SELECT * FROM EXPORT_CUSTOM WHERE ID='{obj.file}'";
var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry);
if (EXPORT_CUSTOM == null)
{
return(Json(new { success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
if (obj.data_value == Common.Objects.EXPORT_CUSTOM.DEFAULT.ToString())
{
var rs = _ExportExportCustom(Oracle, FoxPro, obj, EXPORT_CUSTOM);
if (!rs)
{
return(Json(new { danger = "Lỗi kết xuất, vui lòng thử lại" }, JsonRequestBehavior.AllowGet));
}
}
else if (obj.data_value == Common.Objects.EXPORT_CUSTOM.MODULES.ToString())
{
var tableList = EXPORT_CUSTOM.TABLE_LIST.Trim(',').Split(',');
if (tableList.Length < 1)
{
return(Json(new { warning = "Chưa có kết xuất!" }, JsonRequestBehavior.AllowGet));
}
foreach (var item in tableList)
{
if (string.IsNullOrEmpty(item))
{
continue;
}
qry = $"SELECT * FROM EXPORT_CUSTOM WHERE NAME='{item}' AND TYPE_NAME='{Common.Objects.EXPORT_CUSTOM.DEFAULT}'";
var _export = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry);
var rs = _ExportExportCustom(Oracle, FoxPro, obj, _export);
//
if (!string.IsNullOrEmpty(EXPORT_CUSTOM.QUERY_CREATE))
{
_ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_CREATE);
}
if (!string.IsNullOrEmpty(EXPORT_CUSTOM.QUERY_END))
{
_ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_END);
}
if (!rs)
{
return(Json(new { danger = "Lỗi kết suất, vui lòng thử lại" }, JsonRequestBehavior.AllowGet));
}
}
}
// FileManagerController.ReExtensionToLower($"{Common.Directories.HDData}{obj.time}");
var fileExport = $"{Common.Directories.HDData}{obj.time}\\{EXPORT_CUSTOM.TABLE_NAME}.dbf";
var fileName = $"{EXPORT_CUSTOM.TABLE_NAME}{obj.datetime.ToString("yyyyMM")}.dbf";
if (new System.IO.FileInfo(Server.MapPath($"~/{fileExport}")).Exists)
{
return(Json(new { success = "Kết xuất thành công!", url = UrlDownloadFiles(fileExport, fileName) }, JsonRequestBehavior.AllowGet));
}
return(Json(new { success = "Kết xuất thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); FoxPro.Connection.Close(); }
}
private string ImportData(Common.DefaultObj obj, Models.DICHVU_VT_BKN DVVT)
{
var SQLServer = new TM.Connection.SQLServer();
var FoxPro = new TM.Connection.OleDBF(obj.DataSource);
try
{
if (DVVT.TABLE_TARGET == "NET")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.NET>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
if (DVVT.TABLE_TARGET == "MEGA")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.MEGA>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
if (DVVT.TABLE_TARGET == "HD_NET")
{
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
//
var collection = FoxPro.Connection.Query <Models.HD_NET>(qry).ToList();;
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
var collectionInsert = new List <Models.HD_NET>();
//
var new_dbkh = FoxPro.Connection.Query <Models.DB_THANHTOAN_BKN>(qry).ToList();
UpdateProperties(new_dbkh, TABLE_FIELD_SET, DVVT, obj.datetime);
var old_dbkh = SQLServer.Connection.Query <Models.DB_THANHTOAN_BKN>($"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=1");
var DataInsert = new List <Models.DB_THANHTOAN_BKN>();
var DataUpdate = new List <Models.DB_THANHTOAN_BKN>();
foreach (var i in new_dbkh)
{
var _tmp = old_dbkh.FirstOrDefault(d => d.MA_TB == i.MA_TB);
var _tmp_collection = collection.FirstOrDefault(d => d.MA_TB == i.MA_TB);
if (_tmp_collection != null)
{
_tmp_collection.DBKH_ID = i.ID;
}
collectionInsert.Add(_tmp_collection);
if (_tmp != null)
{
if (!string.IsNullOrEmpty(i.MA_TB))
{
_tmp.MA_TB = i.MA_TB;
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_tmp.TEN_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIACHI_TT))
{
_tmp.DIACHI_TT = i.DIACHI_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIENTHOAI))
{
_tmp.DIENTHOAI = i.DIENTHOAI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_DVI))
{
_tmp.MA_DVI = i.MA_DVI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_CBT))
{
_tmp.MA_CBT = i.MA_CBT.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TUYEN))
{
_tmp.MA_TUYEN = i.MA_TUYEN.Trim();
}
if (!string.IsNullOrEmpty(i.MA_KH))
{
_tmp.MA_KH = i.MA_KH.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TT))
{
_tmp.MA_TT = i.MA_TT.Trim();
}
if (!string.IsNullOrEmpty(i.MS_THUE))
{
_tmp.MS_THUE = i.MS_THUE.Trim();
}
_tmp.MA_DT = i.MA_DT;
_tmp.KHLON_ID = i.KHLON_ID;
_tmp.LOAIKH_ID = i.LOAIKH_ID;
_tmp.TH_SD = 1;
_tmp.ISNULL = 0;
_tmp.ISNULLMT = 0;
_tmp.FIX = 1;
_tmp.FLAG = 1;
DataUpdate.Add(_tmp);
}
else
{
i.MA_DT = 1;
i.TH_SD = 1;
i.ISNULL = 1;
i.ISNULLMT = 1;
i.FIX = 1;
i.FLAG = 1;
DataInsert.Add(i);
}
}
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collectionInsert);
SQLServer.Connection.Insert(DataInsert);
SQLServer.Connection.Update(DataUpdate);
}
else if (DVVT.TABLE_TARGET == "MYTV")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.MYTV>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
else if (DVVT.TABLE_TARGET == "HD_MYTV")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.HD_MYTV>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
else if (DVVT.TABLE_TARGET == "DISCOUNT")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.DISCOUNT>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
else if (DVVT.TABLE_TARGET == "THANHTOANTRUOC")
{
var qry = $"SELECT a.*,a.MA_TB FROM {DVVT.MA_DVVT} a";
var collection = FoxPro.Connection.Query <Models.THANHTOANTRUOC>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
//Update NGAY_BD
qry = $"UPDATE THANHTOANTRUOC SET NGAY_BD=CAST(CAST(NAM AS varchar(4))+'-'+CAST(THANG AS varchar(2))+'-1' as datetime) WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}";
SQLServer.Connection.Query(qry);
//Update NGAY_KT
qry = $"UPDATE THANHTOANTRUOC SET NGAY_KT=DATEADD(MONTH,SOTHANG,CAST(CAST(NAM as varchar(4))+'-'+CAST(THANG as varchar(2))+'-01' as datetime)) WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}";
SQLServer.Connection.Query(qry);
}
else if (DVVT.TABLE_TARGET == "CD")
{
var qry = $"ALTER TABLE {DVVT.MA_DVVT} ALTER COLUMN dia_chi c(100)";
FoxPro.Connection.Query(qry);
qry = $"SELECT a.*,a.MA_KH1 AS MA_TT,a.TEN_CQ AS TEN_TT,a.DIA_CHI AS DIACHI_TT,a.MA_ST AS MS_THUE,a.dvql_id AS MA_DVI,a.TONG_CUOC AS TONGCONG FROM {DVVT.MA_DVVT} a";
var collection = FoxPro.Connection.Query <Models.CD>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
else if (DVVT.TABLE_TARGET == "DD")
{
var qry = $"SELECT a.*,a.cuoc_cthue AS TONG,a.thue AS VAT,a.ma_cq AS MA_KH,a.ma_cq AS MA_TT,a.taikhoan AS BANKNUMBER,GIAMTRU AS GIAM_TRU FROM {DVVT.MA_DVVT} a";
var collection = FoxPro.Connection.Query <Models.DD>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
else if (DVVT.TABLE_TARGET == "HD_TSL")
{
var qry = $"SELECT * FROM {DVVT.MA_DVVT}";
var collection = FoxPro.Connection.Query <Models.HD_TSL>(qry).ToList();
//
var TABLE_FIELD_SET = DVVT.TABLE_FIELD_SET.Trim(',').Split(',');
UpdateProperties(collection, TABLE_FIELD_SET, DVVT, obj.datetime);
//Delete old
SQLServer.Connection.Query($"DELETE {DVVT.TABLE_TARGET} WHERE TYPE_BILL={DVVT.DICHVUVT_ID} AND KYHOADON={obj.KYHD}");
//
SQLServer.Connection.Insert(collection);
}
}
catch (Exception) { throw; }
finally { SQLServer.Close(); FoxPro.Close(); }
return(DVVT.TEN_DVVT);
}
