public async Task <IActionResult> TransferDataPortal(string dataVal, string database = "SQL_Portal")
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
var SQLServer = new TM.Core.Connection.SQLServer(database);
var Oracle = new TM.Core.Connection.Oracle("PORTAL");
var qry = $"SELECT * FROM {dataVal}";
var table = await SQLServer.Connection.QueryAsync <Authentication.Core.Users>(qry);
foreach (var i in table)
{
i.id = i.id.ToUpper();
i.full_name = string.IsNullOrEmpty(i.full_name) ? i.full_name : i.full_name;
i.created_by = string.IsNullOrEmpty(i.created_by) ? "Admin" : i.created_by;
i.created_at = i.created_at.HasValue ? i.created_at.Value : DateTime.Now;
}
Oracle.Connection.Insert(table);
return(Json(new { msg = TM.Core.Common.Message.success.ToString() }));
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
}
public JsonResult TransferData(string dataVal, string database = "SQL_CUOC")
{
var SQLServer = new TM.Core.Connection.SQLServer(database);
var qry = $"SELECT * FROM {dataVal}";
var tableOld = SQLServer.Connection.Query(qry);
var tableNew = _Con.Connection.Query(qry).ToList();
return(Json(""));
}
public JsonResult GetTable(string database = "SQL_CUOC")
{
try {
var SQLServer = new TM.Core.Connection.SQLServer(database);
var qry = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME";
var data = SQLServer.Connection.Query <Models.TABLES>(qry).ToList();
return(Json(new { data = data, success = "Xử lý thành công!" }));
} catch (System.Exception ex) {
return(Json(new { danger = ex.Message }));
}
}
public async Task <IActionResult> TransferDataCuoc(string table, string database = "SQL_CUOC")
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
var SQLServer = new TM.Core.Connection.SQLServer(database);
var Oracle = new TM.Core.Connection.Oracle("VNPTBK");
var qry = $"SELECT * FROM {table}";
var data = await SQLServer.Connection.QueryAsync <Models.Core.Groups>(qry);
Oracle.Connection.InsertOra(data);
return(Json(new { msg = TM.Core.Common.Message.success.ToString() }));
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
}
public JsonResult TransferDataPortal(string dataVal, string database = "SQL_Portal")
{
try {
var SQLServer = new TM.Core.Connection.SQLServer(database);
var Oracle = new TM.Core.Connection.Oracle("ORA_PORTAL");
var qry = $"SELECT * FROM {dataVal}";
var table = SQLServer.Connection.Query <Authentication.Core.Users>(qry);
foreach (var i in table)
{
i.UserID = i.UserID.Replace("-", "").ToUpper();
i.fullname = string.IsNullOrEmpty(i.fullname) ? i.username : i.fullname;
i.createdBy = string.IsNullOrEmpty(i.createdBy) ? "Admin" : i.createdBy;
i.createdAt = i.createdAt.HasValue ? i.createdAt.Value : DateTime.Now;
}
Oracle.Connection.Insert(table);
return(Json(new { success = "Cập nhật thành công" }));
} catch (System.Exception ex) {
return(Json(new { danger = ex.Message }));
}
}
public JsonResult Select(objBST obj) //string sort, string order, string search, int offset = 0, int limit = 10, int flag = 1
{
var index = 0;
var qry = "";
var cdt = "";
try {
SQLServer = new TM.Core.Connection.SQLServer();
obj.extension = "'.dbf','.txt','.xls','.xlsx','.zip','.xml'";
//Select
qry = $"SELECT * FROM FILE_MANAGER WHERE FLAG={obj.flag}";
//Extension
if (!Authentication.Core.Auth.inRoles(new [] { Authentication.Core.Roles.admin, Authentication.Core.Roles.superadmin }))
{
if (!string.IsNullOrEmpty(obj.extension))
{
cdt += $"(EXTENSION IN({obj.extension}) OR TYPE='{Common.FileManager.directory}') AND ";
}
}
//Sub Dir
if (!string.IsNullOrEmpty(obj.subDir))
{
cdt += $"SUBDIRECTORY IN('{obj.subDir}') AND ";
}
else
{
cdt += $"LEVELS=0 AND ";
}
//Get data for Search
//if (!String.IsNullOrEmpty(obj.search) && obj.search.isNumber())
// cdt += $"(GOICUOCID={obj.search}) AND ";
if (!string.IsNullOrEmpty(obj.search))
{
cdt += $"(PARENT LIKE '%{obj.search}%' OR NAME LIKE '%{obj.search}%' OR SUBDIRECTORY LIKE '%,{obj.search},%' OR EXTENSION LIKE '%,{obj.search},%') AND ";
}
//
if (!string.IsNullOrEmpty(cdt))
{
qry += $" AND {cdt.Substring(0, cdt.Length - 5)}";
}
//export
if (obj.export == 1)
{
//var startDate = DateTime.ParseExact($"{obj.startDate}", "dd/MM/yyyy HH:mm", provider);
//var endDate = DateTime.ParseExact($"{obj.endDate}", "dd/MM/yyyy HH:mm", provider);
//qry += $" AND tb.FLAG=2 AND tb.UPDATEDAT>=CAST('{startDate.ToString("yyyy-MM-dd")}' as datetime) AND tb.UPDATEDAT<=CAST('{endDate.ToString("yyyy-MM-dd")}' as datetime) ORDER BY tb.MA_DVI,tb.UPDATEDAT";
//var export = SQLServer.Connection.Query<Portal.Areas.ND49.Models.ND49Export>(qry);
//qry = "SELECT * FROM users";
//var user = SQLServer.Connection.Query<Authentication.user>(qry);
//foreach (var i in export)
//{
// var tmp = user.FirstOrDefault(d => d.username == i.NVQL);
// i.TEN_NVQL = tmp != null ? tmp.full_name : null;
//}
//var rsJson = Json(new { data = export, SHA = Guid.NewGuid() });
//rsJson.MaxJsonLength = int.MaxValue;
//return rsJson;
}
//
var data = SQLServer.Connection.Query <Models.FILE_MANAGER>(qry);
if (data.ToList().Count < 1)
{
return(Json(new { total = 0, rows = data }));
}
//Get total item
var total = data.Count();
//Sort And Orders
if (!string.IsNullOrEmpty(obj.sort))
{
if (obj.sort.ToUpper() == "NAME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.NAME);
}
else if (obj.sort.ToUpper() == "NAME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.NAME);
}
else if (obj.sort.ToUpper() == "ATTRIBUTES" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.ATTRIBUTES);
}
else if (obj.sort.ToUpper() == "ATTRIBUTES" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.ATTRIBUTES);
}
else if (obj.sort.ToUpper() == "LENGTH" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.LENGTH);
}
else if (obj.sort.ToUpper() == "LENGTH" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.LENGTH);
}
else if (obj.sort.ToUpper() == "CREATIONTIME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.CREATIONTIME);
}
else if (obj.sort.ToUpper() == "CREATIONTIME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.CREATIONTIME);
}
else if (obj.sort.ToUpper() == "LASTWRITETIME" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.LASTWRITETIME);
}
else if (obj.sort.ToUpper() == "LASTWRITETIME" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.LASTWRITETIME);
}
else
{
data = data.OrderByDescending(m => m.CREATIONTIME).ThenBy(m => m.NAME);
}
}
else
{
data = data.OrderByDescending(m => m.CREATIONTIME).ThenBy(m => m.NAME);
}
//Page Site
var rs = data.Skip(obj.offset).Take(obj.limit).ToList();
var ReturnJson = Json(new { total = total, rows = rs });
//ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
} catch (Exception) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" })); } finally { SQLServer.Close(); }
//return Json(new { success = "Cập nhật thành công!" });
}