public JsonResult UpdateDBDT(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try
{
//Get Data HNI
var qry = $"SELECT * FROM DB_DUONGTHU_BKN";
var data_hni = Oracle.Connection.Query <Models.DB_DUONGTHU_BKN>(qry).ToList();
//Update Data
foreach (var i in data_hni)
{
i.ID = Guid.NewGuid();
i.FIX = 0;
i.FLAG = 1;
i.MA_DT = i.MA_DT.Trim().ToUpper();
}
//Delete Old
qry = "DELETE DB_DUONGTHU_BKN WHERE FIX=0";
SQLServer.Connection.Query(qry);
//Insert Data
SQLServer.Connection.Insert(data_hni);
return(Json(new { success = $"DB_DUONGTHU_BKN - Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
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 UpdateQuanHuyen(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
try
{
//Get Data HNI
var qry = $"SELECT * FROM QUAN_HUYEN_BKN";
var data_hni = Oracle.Connection.Query <Models.QUAN_HUYEN_BKN>(qry).ToList();
//Delete Old
qry = "DELETE QUAN_HUYEN_BKN";
SQLServer.Connection.Query(qry);
//Insert Data
SQLServer.Connection.Insert(data_hni);
return(Json(new { success = $"QUAN_HUYEN_BKN - Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public JsonResult CreateExport(Common.DefaultObj obj)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
obj.DataSource = Common.Directories.HDData;
//
try
{
var qry = $"SELECT a.*,a.data_type COLUMN_TYPE,data_length COLUMN_LENGTH,a.column_id ORDERS FROM all_tab_cols a WHERE a.owner='{OWNER}' order by a.table_name";
var data = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
foreach (var i in data)
{
var MapDBF = MappingOracleDBF(i.COLUMN_TYPE);
i.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM;
i.COLUMN_NAME_EXPORT = i.COLUMN_NAME.Length > 10 ? i.COLUMN_NAME.Substring(0, 10).Trim('-').Trim('_') : i.COLUMN_NAME;
i.COLUMN_TYPE_EXPORT = MapDBF[0];
i.COLUMN_LENGTH_EXPORT = MapDBF[1];
i.CONDITION = "";
i.FLAG = 1;
}
return(Json(new { data = data, 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(); }
}
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 ImportGDSExcel(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var HNIVNPTBACKAN2 = new TM.Connection.Oracle("HNIVNPTBACKAN2");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var UploadFiles = new List <string>();
string strUpload = "Nhập file GDS thành công!";
try
{
TM.IO.FileDirectory.CreateDirectory(obj.DataSource, false);
var uploadData = UploadBase(obj.DataSource, strUpload, UploadFiles, ".xls");
if (UploadFiles.Count < 1)
{
return(Json(new { danger = "Chưa tải được tệp, Vui lòng thử lại!" }, JsonRequestBehavior.AllowGet));
}
//
var xxxx = TM.OleExcel.ToDataSet(obj.DataSource + UploadFiles[0]);
var excel = xxxx.Tables[0];
var list = new List <Models.CUOC_FIBERVNN>();
foreach (System.Data.DataRow r in excel.Rows)
{
index++;
var tmp = new Models.CUOC_FIBERVNN();
tmp.MA_MEN = r["MA_MEN"].ToString();
tmp.CUOC = r["CUOC"] != null?decimal.Parse(r["CUOC"].ToString()) : 0;
tmp.THANGNAM = obj.month_year_time;
list.Add(tmp);
}
//list.Add(new Models.GDSIMPORT { MA_MEN = r["MA_MEN"].ToString(), CUOC = (int)r["CUOC"], THANGNAM = ((DateTime)r["THANGNAM"]).ToString("MM/yyyy") });
//HNIVNPTBACKAN2.Connection.Query("DELETE CUOC_FIBERVNN WHERE THANGNAM='11/2017'");
TM.Dapper.CRUDOracle.InsertList(HNIVNPTBACKAN2.Connection, list);
//
if (uploadData == (int)Common.Objects.ResultCode._extension)
{
return(Json(new { danger = "Tệp phải định dạng .dbf!" }, JsonRequestBehavior.AllowGet));
}
else if (uploadData == (int)Common.Objects.ResultCode._length)
{
return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet));
}
//else if (uploadData == (int)Common.Objects.ResultCode._success)
// return Json(new { success = strUpload }, JsonRequestBehavior.AllowGet);
else
{
return(Json(new { success = strUpload }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
}
public ActionResult Export()
{
try
{
var Oracle = new TM.Connection.Oracle("ORCHNIVNPTBACKAN1");
var rs = Oracle.Connection.Query<Portal.ModelsHNI.TB_FSECURE_BKN>("SELECT * FROM TB_FSECURE_BKN");
//Export to any
TM.Exports.ExportExcel(TM.Helper.Data.ToDataTable(rs.ToList()), "DanhSachFSecure");
}
catch (Exception ex) { this.danger(ex.Message); }
return RedirectToAction("Index");
}
public JsonResult GetExportCustom(int flag = 1)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
var qry = $"SELECT * FROM EXPORT_CUSTOM WHERE FLAG={flag} ORDER BY TYPE_NAME DESC,NAME";
var data = Oracle.Connection.Query <Models.EXPORT_CUSTOM>(qry);
return(Json(new { data = data, roles = Authentication.Auth.AuthUser.roles, success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult EditQueryExportCustom(string id, string ExportName, string ExportTableName, string ExportQueryCreate, string ExportQuerySelect, string Condition)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
var qry = $"UPDATE EXPORT_CUSTOM SET NAME='{ExportName}',TABLE_NAME='{ExportTableName}',QUERY_CREATE='{ExportQueryCreate}',QUERY_SELECT='{ExportQuerySelect}',CONDITION='{Condition}' WHERE ID='{id}'";
Oracle.Connection.Query(qry);
return(Json(new { success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult UpdateTableExport(long id, string col, string val)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
var qry = $"UPDATE EXPORT_TABLE SET {col}='{val}' WHERE ID={id}";
Oracle.Connection.Query(qry);
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(); }
}
public JsonResult getDataHD(string so_hd)
{
try
{
var Oracle = new TM.Connection.Oracle("ORCHNIVNPTBACKAN1");
if (!checkHD(so_hd))
{
return(Json(new { danger = "Hợp đồng đã tồn tại!" }, JsonRequestBehavior.AllowGet));
}
var data = Oracle.Connection.Query <Models.HD_THUEBAO_BKN>($"SELECT * FROM HD_THUEBAO_BKN WHERE so_hd LIKE '{so_hd}%' ORDER BY LOAIHINHTB_ID").ToList();
System.Web.HttpContext.Current.Session[_HNIHD] = data;
return(Json(new { data = data, success = TM.Common.Language.msgSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
}
public JsonResult GetTableList(string database)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
// var qry = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME";
var qry = $"SELECT DISTINCT OWNER,OBJECT_NAME TABLE_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='{OWNER}';";
var data = Oracle.Connection.Query <Models.TABLES>(qry).ToList();
return(Json(new { data = data, 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(); }
}
public JsonResult RemoveExportCustom(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);
try
{
var qry = $@"DELETE EXPORT_TABLE WHERE APP_KEY='{obj.file}';
DELETE EXPORT_CUSTOM WHERE ID='{obj.file}';";
Oracle.Connection.Query(qry);
return(Json(new { success = "Xóa 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(); }
}
public JsonResult GetDetailsTableExport(string tableName)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
// var qry = $"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=N'{tableName}';";
var qry = $"SELECT * FROM all_tab_cols WHERE table_name='{tableName}' AND owner='{OWNER}'";
var data = Oracle.Connection.Query <Models.COLUMNS>(qry).ToList();
//
qry = $"SELECT * FROM EXPORT_TABLE WHERE TABLE_TYPE={(int)Common.Objects.TABLE_TYPE.DBF} AND TABLE_NAME='{tableName}'";
var listETOld = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
//
var listET = new List <Models.EXPORT_TABLE>();
foreach (var i in data)
{
var check = listETOld.FirstOrDefault(m => m.COLUMN_NAME == i.COLUMN_NAME);
if (check == null)
{
var tmp = new Models.EXPORT_TABLE();
var MapDBF = MappingOracleDBF(i.DATA_TYPE);
tmp.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.DBF;
tmp.TABLE_NAME = i.TABLE_NAME;
tmp.COLUMN_NAME = i.COLUMN_NAME;
tmp.COLUMN_TYPE = i.DATA_TYPE;
tmp.COLUMN_LENGTH = i.DATA_LENGTH;
tmp.COLUMN_NAME_EXPORT = i.COLUMN_NAME.Length > 10 ? i.COLUMN_NAME.Substring(0, 10).Trim('-').Trim('_') : i.COLUMN_NAME;
tmp.COLUMN_TYPE_EXPORT = MapDBF[0];
tmp.COLUMN_LENGTH_EXPORT = MapDBF[1];
tmp.CONDITION = "";
tmp.ORDERS = i.ORDINAL_POSITION;
tmp.FLAG = 1;
listET.Add(tmp);
}
}
Oracle.Connection.Insert(listET);
listET = Oracle.Connection.Query <Models.EXPORT_TABLE>(qry).ToList();
return(Json(new { data = listET, 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(); }
}
public JsonResult DeleteExportCustom(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);
try
{
var data = Oracle.Connection.Get <Models.EXPORT_CUSTOM>(obj.file);
if (data != null)
{
var qry = $"UPDATE EXPORT_CUSTOM SET FLAG={(data.FLAG == 0 ? 1 : 0)} WHERE ID='{obj.file}'";
Oracle.Connection.Query(qry);
}
return(Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult UpdateTTHD(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
var TTHD_ID_HUY = "10,12,13,14,16,17";
try
{
//Cập nhật tổng
var qry = $"select a.*,b.TTHD_ID from DB_THUEBAO_BKN a,HD_THUEBAO_BKN b,LOAIHINH_TB_BKN lhtb where a.MA_TB=b.MA_TB and a.LOAIHINHTB_ID=lhtb.LOAIHINHTB_ID and lhtb.DICHVUVT_ID=4";
var dbpttb = Oracle.Connection.Query <Models.TTHD_TSL>(qry);
qry = $"SELECT * FROM HD_TSL WHERE TYPE_BILL={TYPE_BILL} AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
var data = SQLServer.Connection.Query <Models.HD_TSL>(qry);
foreach (var i in data)
{
var _tmp = dbpttb.FirstOrDefault(d => d.MA_TB == i.MA_TB);
if (_tmp != null)
{
i.TTHD_ID = _tmp.TTHD_ID;
}
}
SQLServer.Connection.Update(data);
//
qry = $"UPDATE HD_TSL SET TYPE_BILL=-1 WHERE TYPE_BILL={TYPE_BILL} AND (TTHD_ID IN({TTHD_ID_HUY}) OR TTHD_ID IS NULL) AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
return(Json(new { success = $"HD_TSL - Cập nhật trạng thái hợp đồng thành công!" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public JsonResult getFSecureInformation(string SearhString)
{
try
{
var Oracle = new TM.Connection.Oracle("ORCHNIVNPTBACKAN1");
//Remove 0 from SO_DIDONG AND +84
//var prefix = SearhString.Substring(0, 2);
//SearhString = prefix == "84" ? SearhString.Substring(2) : SearhString;
//SearhString = SearhString.Replace("+84", "0");
//SearhString = SearhString[0].ToString() == "0" ? SearhString.Substring(1) : SearhString;
var data = Oracle.Connection.Query<Portal.ModelsHNI.TB_FSECURE_BKN>(
$@"SELECT * FROM TB_FSECURE_BKN WHERE
SO_DIDONG='{SearhString}' OR
USERNAME='******' OR
MA_FSECURE='{SearhString}' OR
ACCOUNT='{SearhString}' OR
SO_MEN='{SearhString}'").FirstOrDefault();
return Json(new { SearhString = SearhString, data = data, success = TM.Common.Language.msgSucsess }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex) { return Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet); }
}
public JsonResult EditExportCustom(string id)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
var qry = $"SELECT * FROM EXPORT_CUSTOM WHERE ID='{id}'";
var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry);
if (EXPORT_CUSTOM == null)
{
return(Json(new { success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
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();
return(Json(new { EXPORT_CUSTOM = EXPORT_CUSTOM, EXPORT_TABLE = EXPORT_TABLE, success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.Close(); }
}
public JsonResult UpdateContactData(Common.DefaultObj obj)
{
var SQLServer = new TM.Connection.SQLServer();
var Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var index = 0;
obj.DataSource = Common.Directories.HDDataSource;
obj = getDefaultObj(obj);
var TYPE_BILL = "4";
try
{
//Get DB PTTB
var qry = $"SELECT kh.KHACHHANG_ID,kh.MA_KH,a.THANHTOAN_ID,tt.MA_TT AS MA_TT,tt.MAPHO_ID,b.DICHVUVT_ID,tt.TEN_TT,tt.DIACHI_TT,tt.DIENTHOAI_TT AS DIENTHOAI,tt.MST AS MS_THUE,tt.MA_TUYENTHU AS MA_TUYEN,tt.DONVIQL_ID,a.DOITUONG_ID AS MA_DT,tttb.TRANGTHAITB_ID AS TH_SD,a.MA_TB,a.MA_TB AS MA_TB,a.DOITUONG_ID,a.GHICHU,a.LOAIHINHTB_ID,a.TRANGTHAITB_ID,a.TBDAYCHUNG_ID,a.NGAY_TRANGTHAITB AS NGAY_TTTB,a.NGAY_SUDUNG AS NGAY_SD,a.NGAY_CN,a.NGAY_HT,a.NGAY_CAT,qh.MA_QUANHUYEN AS MA_DVI,b.MA_LHTB FROM DB_THUEBAO_BKN a,DB_THANHTOAN_BKN tt,DB_KHACHHANG_BKN kh,LOAIHINH_TB_BKN b,TRANGTHAI_TB_BKN tttb, MA_PHO_BKN mp,PHUONG_XA_BKN px,QUAN_HUYEN_BKN qh WHERE a.THANHTOAN_ID=tt.THANHTOAN_ID AND tt.KHACHHANG_ID=kh.KHACHHANG_ID AND a.LOAIHINHTB_ID=b.LOAIHINHTB_ID AND tt.MAPHO_ID=mp.MAPHO_ID AND mp.PHUONGXA_ID=px.PHUONGXA_ID AND px.QUANHUYEN_ID=qh.QUANHUYEN_ID AND b.DICHVUVT_ID={TYPE_BILL} AND a.TRANGTHAITB_ID=tttb.TRANGTHAITB_ID ORDER BY qh.MA_QUANHUYEN,a.NGAY_CN,a.MA_TB";
var dbpttb = Oracle.Connection.Query <Models.DANH_BA_TSL>(qry);
//Get data DB_DUONGTHU_BKN
qry = $"SELECT * FROM DB_DUONGTHU_BKN";
var dbdt = SQLServer.Connection.Query <Models.DB_DUONGTHU_BKN>(qry);
//Get data DB KH
qry = $"SELECT * FROM {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} WHERE FIX=0 AND FLAG=1";//AND TYPE_BILL IN({TYPE_BILL})
var dbkh = SQLServer.Connection.Query <Models.DB_THANHTOAN_BKN>(qry);
//Get data TSL Remove
qry = $"SELECT * FROM HD_TSL WHERE TYPE_BILL=9641 AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
var tslRemove = SQLServer.Connection.Query <Models.HD_TSL>(qry);
//
var DataInsert = new List <Models.DB_THANHTOAN_BKN>();
var DataUpdate = new List <Models.DB_THANHTOAN_BKN>();
var DataInsertHD = new List <Models.HD_TSL>();
var _dbkh_id = Guid.Empty;
//Xóa dữ liệu cũ
qry = $"DELETE HD_TSL WHERE TYPE_BILL IN({TYPE_BILL},-1) AND FORMAT(TIME_BILL,'MM/yyyy')='{obj.month_year_time}'";
SQLServer.Connection.Query(qry);
foreach (var i in dbpttb)
{
//check data TSL Remove
if (tslRemove.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
//Cập nhật hóa đơn TSL
var hdtsl = new Models.HD_TSL();
hdtsl.ID = Guid.NewGuid();
hdtsl.DBKH_ID = _dbkh_id;
hdtsl.TYPE_BILL = int.Parse(TYPE_BILL);
hdtsl.KYHOADON = obj.KYHD;
hdtsl.MAPHO_ID = i.MAPHO_ID;
hdtsl.THANHTOAN_ID = i.THANHTOAN_ID;
hdtsl.THUEBAO_ID = i.THUEBAO_ID;
hdtsl.MA_TB = i.MA_TB;
hdtsl.MA_TB = i.MA_TB;
hdtsl.DOITUONG_ID = i.DOITUONG_ID;
hdtsl.TOC_DO = "TSL";
hdtsl.GHICHU = i.GHICHU;
hdtsl.TT_THANG = 1;
hdtsl.NGAY_TB = obj.day_in_month;
hdtsl.LOAIHINHTB_ID = i.LOAIHINHTB_ID;
hdtsl.MA_LHTB = i.MA_LHTB;
hdtsl.TRANGTHAITB_ID = i.TRANGTHAITB_ID;
hdtsl.TBDAYCHUNG_ID = i.TBDAYCHUNG_ID;
hdtsl.GOICUOCID = i.GOICUOCID;
//hdtsl.ISDATMOI = 0;
//hdtsl.ISHUY = 0;
//hdtsl.ISTTT = 0;
//hdtsl.ISDATCOC = 0;
hdtsl.NGAY_TTTB = i.NGAY_TTTB;
hdtsl.NGAY_SD = i.NGAY_SD;
hdtsl.NGAY_CN = i.NGAY_CN;
hdtsl.NGAY_HT = i.NGAY_HT;
hdtsl.NGAY_CAT = i.NGAY_CAT;
DataInsertHD.Add(hdtsl);
//Cập nhật danh bạ TSL
var _tmp = dbkh.FirstOrDefault(d => d.MA_TB == i.MA_TB);
if (_tmp != null)
{
if (DataUpdate.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
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.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.MS_THUE)) _tmp.MS_THUE = i.MS_THUE.Trim();
//_tmp.BANKNUMBER = null;
if (!string.IsNullOrEmpty(i.MA_DVI))
{
_tmp.MA_DVI = i.MA_DVI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TUYEN))
{
_tmp.MA_TUYEN = i.MA_TUYEN.Trim().ToUpper();
}
//if (!string.IsNullOrEmpty(i.MA_CBT)) _tmp.MA_CBT = i.MA_CBT.Trim();
var ma_cbt = dbdt.FirstOrDefault(d => d.MA_DT == _tmp.MA_TUYEN);
_tmp.MA_CBT = ma_cbt != null ? ma_cbt.MA_DT_GOC : null;
//if (!string.IsNullOrEmpty(i.CUSTCATE)) _tmp.CUSTCATE = i.CUSTCATE.Trim();
//_tmp.STK = null;
_tmp.DONVI_ID = i.DONVI_ID;
_tmp.KHACHHANG_ID = i.KHACHHANG_ID;
_tmp.THANHTOAN_ID = i.THANHTOAN_ID;
_tmp.MA_DT = i.MA_DT;
_tmp.TH_SD = i.TH_SD;
_tmp.ISNULL = 0;
_tmp.ISNULLMT = 0;
_tmp.FIX = 0;
_tmp.FLAG = 1;
//MA_TB Json
//var MA_TB_json = Newtonsoft.Json.JsonConvert.DeserializeObject<Models.MA_TB_JSON>(_tmp.MA_TB);
//MA_TB_json.TSL = i.MA_TB;
//_tmp.MA_TB = Newtonsoft.Json.JsonConvert.SerializeObject(MA_TB_json);
_tmp.MA_TB = i.MA_TB;
//
_dbkh_id = _tmp.ID;
DataUpdate.Add(_tmp);
//SQLServer.Connection.Update(_tmp);
}
else
{
if (DataInsert.Any(d => d.MA_TB == i.MA_TB))
{
continue;
}
var _d = new Models.DB_THANHTOAN_BKN();
_d.ID = _dbkh_id = Guid.NewGuid();
_d.TYPE_BILL = int.Parse(TYPE_BILL);
_d.MA_TB = _d.MA_TB = i.MA_TB;
if (!string.IsNullOrEmpty(i.MA_KH))
{
_d.MA_KH = i.MA_KH.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TT))
{
_d.MA_TT = i.MA_TT.Trim();
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_d.TEN_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.TEN_TT))
{
_d.DIACHI_TT = i.TEN_TT.Trim();
}
if (!string.IsNullOrEmpty(i.DIENTHOAI))
{
_d.DIENTHOAI = i.DIENTHOAI.Trim();
}
//if (!string.IsNullOrEmpty(i.MA_ST)) _d.MS_THUE = i.MA_ST.Trim();
//_tmp.BANKNUMBER = null;
if (!string.IsNullOrEmpty(i.MA_DVI))
{
_d.MA_DVI = i.MA_DVI.Trim();
}
if (!string.IsNullOrEmpty(i.MA_TUYEN))
{
_d.MA_TUYEN = i.MA_TUYEN.Trim().ToUpper();
}
//if (!string.IsNullOrEmpty(i.MA_CBT)) _d.MA_CBT = i.MA_CBT.Trim();
var ma_cbt = dbdt.FirstOrDefault(d => d.MA_DT == _d.MA_TUYEN);
_d.MA_CBT = ma_cbt != null ? ma_cbt.MA_DT_GOC : null;
//if (!string.IsNullOrEmpty(i.CUSTCATE)) _d.CUSTCATE = i.CUSTCATE.Trim();
//_tmp.STK = null;
_d.DONVI_ID = i.DONVI_ID;
_d.KHACHHANG_ID = i.KHACHHANG_ID;
_d.THANHTOAN_ID = i.THANHTOAN_ID;
_d.MA_DT = i.MA_DT;
_d.TH_SD = i.TH_SD;
_d.ISNULL = 0;
_d.ISNULLMT = 0;
_d.FIX = 0;
_d.FLAG = 1;
//MA_TB Json
//var MA_TB_json = new Models.MA_TB_JSON();
//MA_TB_json.TSL = i.MA_TB;
//_d.MA_TB = Newtonsoft.Json.JsonConvert.SerializeObject(MA_TB_json);
_d.MA_TB = i.MA_TB;
DataInsert.Add(_d);
}
}
//
if (DataInsert.Count > 0)
{
SQLServer.Connection.Insert(DataInsert);
}
if (DataUpdate.Count > 0)
{
SQLServer.Connection.Update(DataUpdate);
}
//
if (DataInsertHD.Count > 0)
{
SQLServer.Connection.Insert(DataInsertHD);
}
//UPDATE NULL
qry = $@"UPDATE {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} SET ISNULLMT=1,MA_TUYEN=REPLACE(MA_TUYEN,'000','001') WHERE MA_TUYEN LIKE '%000' AND FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL});
UPDATE {Common.Objects.TYPE_HD.DB_THANHTOAN_BKN} SET MA_CBT=CAST(CAST(ma_dvi as varchar)+'01' as int) WHERE ISNULLMT=1 AND FIX=0 AND FLAG=1 AND TYPE_BILL IN({TYPE_BILL});";
SQLServer.Connection.Query(qry);
//
return(Json(new { success = $"TSL - Cập nhật: {DataUpdate.Count} - Thêm mới: {DataInsert.Count}" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message + " - Index: " + index }, JsonRequestBehavior.AllowGet)); }
finally
{
SQLServer.Close();
Oracle.Close();
}
}
public JsonResult UpdateDataHNI(Models.DONVI_BKN obj)
{
//var provider = System.Globalization.CultureInfo.InvariantCulture;
var index = 0;
var qry = "";
try
{
SQLServer = new TM.Connection.SQLServer();
Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
//
qry = $"SELECT * FROM DONVI_BKN WHERE CAPDONVI_ID IN(29,30)";
var donvi_hni = Oracle.Connection.Query <Models.DONVI_BKN>(qry);
qry = $"SELECT * FROM DONVI_BKN";
var data = SQLServer.Connection.Query <Models.DONVI_BKN>(qry);
//
var DataInsert = new List <Models.DONVI_BKN>();
var DataUpdate = new List <Models.DONVI_BKN>();
foreach (var i in donvi_hni)
{
var _tmp = data.FirstOrDefault(d => d.DONVI_ID == i.DONVI_ID);
if (_tmp != null)
{
_tmp.DONVI_CHA_ID = i.DONVI_CHA_ID;
_tmp.CAPDONVI_ID = i.CAPDONVI_ID;
_tmp.MA_DV = i.MA_DV;
_tmp.TEN_DV = i.TEN_DV;
_tmp.DIACHI_DV = i.DIACHI_DV;
_tmp.SO_FAX = i.SO_FAX;
_tmp.MST = i.MST;
_tmp.SO_TK = i.SO_TK;
_tmp.SO_DT = i.SO_DT;
_tmp.GHICHU = i.GHICHU;
_tmp.DV_NOITHANH = i.DV_NOITHANH;
_tmp.DV_DAUVAO = i.DV_DAUVAO;
_tmp.DV_TT = i.DV_TT;
_tmp.DV_CAP1 = i.DV_CAP1;
_tmp.TRANGTHAI = i.TRANGTHAI;
_tmp.LOAIHOST_ID = i.LOAIHOST_ID;
_tmp.TINHTP_ID = i.TINHTP_ID;
_tmp.DONVI_ID_MAP = i.DONVI_ID_MAP;
_tmp.FLAG = 1;
DataUpdate.Add(_tmp);
}
else
{
var tmp = new Models.DONVI_BKN();
tmp.DONVI_ID = i.DONVI_ID;
tmp.DONVI_CHA_ID = i.DONVI_CHA_ID;
tmp.CAPDONVI_ID = i.CAPDONVI_ID;
tmp.MA_DV = i.MA_DV;
tmp.TEN_DV = i.TEN_DV;
tmp.DIACHI_DV = i.DIACHI_DV;
tmp.SO_FAX = i.SO_FAX;
tmp.MST = i.MST;
tmp.SO_TK = i.SO_TK;
tmp.SO_DT = i.SO_DT;
tmp.GHICHU = i.GHICHU;
tmp.DV_NOITHANH = i.DV_NOITHANH;
tmp.DV_DAUVAO = i.DV_DAUVAO;
tmp.DV_TT = i.DV_TT;
tmp.DV_CAP1 = i.DV_CAP1;
tmp.TRANGTHAI = i.TRANGTHAI;
tmp.LOAIHOST_ID = i.LOAIHOST_ID;
tmp.TINHTP_ID = i.TINHTP_ID;
tmp.DONVI_ID_MAP = i.DONVI_ID_MAP;
tmp.FLAG = 1;
DataInsert.Add(tmp);
}
}
//
if (DataInsert.Count > 0)
{
SQLServer.Connection.Insert(DataInsert);
}
if (DataUpdate.Count > 0)
{
SQLServer.Connection.Update(DataUpdate);
}
//
var ReturnJson = Json(new { success = $"DONVI_BKN - Cập nhật: {DataUpdate.Count} - Thêm mới: {DataInsert.Count}" }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception ex) { return(Json(new { danger = "Lỗi hệ thống vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
}
public ActionResult Update()
{
try
{
SQLServer = new TM.Connection.SQLServer();
Oracle = new TM.Connection.Oracle("HNIVNPTBACKAN1");
var qry = "SELECT a.*,a.VI_TRI AS ORDERS FROM DICHVU_VT_BKN a";
var hni = Oracle.Connection.Query <Models.DICHVU_VT_BKN>(qry).ToList();
qry = "SELECT * FROM DICHVU_VT_BKN";
var data = SQLServer.Connection.Query <Models.DICHVU_VT_BKN>(qry).ToList();
//
if (hni.Count < 0)
{
this.warning("Hệ thống HNI không có dữ liệu!");
return(RedirectToAction("Index"));
}
//
if (data.Count < 1)
{
SQLServer.Connection.Insert(hni);
return(RedirectToAction("Index"));
}
//
var Update_Upload = new[] { 6, 8, 9 };
hni.AddRange(DichVuAddition());
//
var insert = hni;
foreach (var i in data)
{
var tmp = hni.FirstOrDefault(d => d.DICHVUVT_ID == i.DICHVUVT_ID);
if (tmp != null)
{
i.MA_DVVT = tmp.MA_DVVT;
i.TEN_DVVT = tmp.TEN_DVVT;
i.GHICHU = tmp.GHICHU;
i.TINHTP_ID = tmp.TINHTP_ID;
i.LOAI_HD = tmp.LOAI_HD;
i.MENU = tmp.MENU;
i.QUYCHIEU = tmp.QUYCHIEU;
i.ORDERS = tmp.ORDERS;
i.UPDATEDBY = "Admin";
i.UPDATEDAT = DateTime.Now;
i.TT_SUDUNG = tmp.TT_SUDUNG;
i.TT_UPLOAD = tmp.TT_UPLOAD;
if (i.TT_UPLOAD == 0 && Update_Upload.Contains(i.DICHVUVT_ID))
{
i.TT_UPLOAD = 1;
}
i.FLAG = 1;
insert.Remove(tmp);
}
}
foreach (var i in insert)
{
i.CREATEDBY = "Admin";
i.CREATEDAT = DateTime.Now;
i.UPDATEDBY = i.CREATEDBY;
i.UPDATEDAT = i.CREATEDAT;
if (i.TT_UPLOAD == 0 && Update_Upload.Contains(i.DICHVUVT_ID))
{
i.TT_UPLOAD = 1;
}
i.FLAG = 1;
}
SQLServer.Connection.Update(data);
if (insert.Count > 0)
{
SQLServer.Connection.Insert(insert);
}
this.success("Cập nhật dịch vụ viễn thông thành công!");
qry = "UPDATE DICHVU_VT_BKN SET ORDERS=999 WHERE orders IS NULL";
SQLServer.Connection.Query(qry);
}
catch (Exception ex)
{
this.danger(ex.Message);
}
finally
{
SQLServer.Close();
Oracle.Close();
}
return(RedirectToAction("Index"));
}
public JsonResult CreateExportSave(ExportCustom obj, List <Models.EXPORT_TABLE> DataList)
{
var OWNER = "TTKD_BKN";
var Oracle = new TM.Connection.Oracle(OWNER);
var index = 0;
try
{
if (string.IsNullOrEmpty(obj.ExportName) && string.IsNullOrEmpty(obj.ExportTableName))
{
return(Json(new { danger = "Vui lòng nhập tên và tên bảng kết xuất!" }, JsonRequestBehavior.AllowGet));
}
var checkUpdate = true;
var tables = new List <string>();
var cols = new List <string>();
var qry = "";
var qry_table = "FROM ";
var qry_create = $"CREATE TABLE {obj.ExportTableName}(";//CreateTable(Common.Directories.HDData, ExportTableName, data);
var qry_select = "SELECT ";
var qry_insert = "INSERT INTO";
var qry_Condition = !string.IsNullOrEmpty(obj.Condition) ? $"WHERE {obj.Condition}" : "";
qry = $"SELECT * FROM EXPORT_CUSTOM WHERE LOWER(NAME)=N'{obj.ExportName.ToLower()}' AND TYPE_NAME='{obj.ExportType}'";
var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry);
if (EXPORT_CUSTOM == null)
{
EXPORT_CUSTOM = new Models.EXPORT_CUSTOM();
EXPORT_CUSTOM.ID = Guid.NewGuid().ToString("N");
EXPORT_CUSTOM.NAME = obj.ExportName;
EXPORT_CUSTOM.TABLE_NAME = obj.ExportTableName.ToLower();
checkUpdate = false;
}
EXPORT_CUSTOM.TABLE_LIST = ",";
//
if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.DEFAULT.ToString())
{
foreach (var i in DataList)
{
if (!tables.Contains(i.TABLE_NAME))
{
tables.Add(i.TABLE_NAME);
}
//Create
if (!cols.Contains(i.COLUMN_NAME_EXPORT))
{
cols.Add(i.COLUMN_NAME_EXPORT);
qry_create += $"[{i.COLUMN_NAME_EXPORT}] {i.COLUMN_TYPE_EXPORT}({i.COLUMN_LENGTH_EXPORT}),";
}
//Select
qry_select += $"{i.TABLE_NAME}.{i.COLUMN_NAME},";
//
i.APP_KEY = EXPORT_CUSTOM.ID;
i.TABLE_TYPE = (int)Common.Objects.TABLE_TYPE.EXPORT_CUSTOM;
i.COLUMN_TYPE = i.COLUMN_TYPE.ToLower();
i.COLUMN_TYPE_EXPORT = i.COLUMN_TYPE_EXPORT.ToLower();
i.ORDERS = index;
i.FLAG = 1;
index++;
}
foreach (var i in tables)
{
qry_table += $"{i},";
EXPORT_CUSTOM.TABLE_LIST += $"{i},";
}
//
qry_create = $"{qry_create.Trim(',')})";
qry_select = $"{qry_select.Trim(',')} {qry_table.Trim(',')} {qry_Condition}";
//
if (obj.chkUpdateQuery)
{
EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate;
EXPORT_CUSTOM.QUERY_SELECT = obj.ExportQuerySelect;
//EXPORT_CUSTOM.QUERY_INSERT = ExportQueryInsert;
EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd;
}
else
{
EXPORT_CUSTOM.QUERY_CREATE = qry_create;
EXPORT_CUSTOM.QUERY_SELECT = qry_select;
EXPORT_CUSTOM.QUERY_INSERT = qry_insert;
}
}
else if (obj.ExportType == Common.Objects.EXPORT_CUSTOM.MODULES.ToString())
{
EXPORT_CUSTOM.TABLE_LIST = !string.IsNullOrEmpty(obj.ExportTableList) ? ("," + obj.ExportTableList.Trim().Trim(',') + ",") : "";
EXPORT_CUSTOM.QUERY_CREATE = obj.ExportQueryCreate;
EXPORT_CUSTOM.QUERY_SELECT = ""; // qry_select;
EXPORT_CUSTOM.QUERY_INSERT = ""; // qry_insert;
EXPORT_CUSTOM.QUERY_END = obj.ExportQueryEnd;
}
else
{
return(Json(new { danger = "Sai định dạng kết suất, Vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet));
}
EXPORT_CUSTOM.TYPE_NAME = obj.ExportType.ToString();
EXPORT_CUSTOM.CONDITION = obj.Condition;
EXPORT_CUSTOM.FLAG = 1;
if (checkUpdate)
{
Oracle.Connection.Update(EXPORT_CUSTOM);
//Remove EXPORT_TABLE Old
qry = $"DELETE FROM EXPORT_TABLE WHERE APP_KEY='{EXPORT_CUSTOM.ID}'";
Oracle.Connection.Query(qry);
}
else
{
Oracle.Connection.Insert(EXPORT_CUSTOM);
}
//var x = new Models.TEST();
//x.ID = Guid.NewGuid().ToString("N");
//x.NAME = "tuanmjnh";
//x.LEVELS = 2;
//x.MONEY = 3.5M;
//x.TIME = DateTime.Now;
//Oracle.Connection.Insert(x);
//Insert EXPORT_TABLE New
if (DataList != null && DataList.Count > 0)
{
Oracle.Connection.Insert(DataList);
}
return(Json(new { data = DataList, success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); }
finally { Oracle.Connection.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(); }
}
