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!" }); }