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
{
var json = new TM.Helper.TMHelperJson(Server.MapPath("~/Systems/config.json"));
_Config = json.LoadJson <Models.Config>();
if (_Config.connection.Count < 1)
{
return(Json(new { total = 0, rows = new List <string>() }, JsonRequestBehavior.AllowGet));
}
//Get total item
var total = _Config.connection.Count;
//Sort And Orders
//Page Site
var rs = _Config.connection.Skip(obj.offset).Take(obj.limit).ToList();
var ReturnJson = Json(new { total = total, rows = rs }, JsonRequestBehavior.AllowGet);
ReturnJson.MaxJsonLength = int.MaxValue;
return(ReturnJson);
}
catch (Exception ex) { return(Json(new { danger = "Không tìm thấy dữ liệu, vui lòng thực hiện lại!" }, JsonRequestBehavior.AllowGet)); }
finally { }
//return Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet);
}
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 {
qry = $"SELECT * FROM DONVI_BKN WHERE FLAG={obj.flag}";
//Get data for Search
if (!String.IsNullOrEmpty(obj.search) && TM.Core.Regex.RegEx.isNumber(obj.search))
{
cdt += $"(DONVI_ID={obj.search} OR MA_QUANHUYEN={obj.search}) AND ";
}
else if (!string.IsNullOrEmpty(obj.search))
{
cdt += $"(TEN_DV LIKE '%{obj.search}%' OR DIACHI_DV 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 = _Con.Connection.Query<Portal.Areas.ND49.Models.ND49Export>(qry);
//qry = "SELECT * FROM users";
//var user = _Con.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 = _Con.Connection.Query <Models.DONVI_BKN>(qry);
////Get data for Search
//if (!string.IsNullOrEmpty(obj.search))
// data = data.Where(d =>
// d.MA_KH.Contains(obj.search) ||
// d.MA_TT_HNI.Contains(obj.search) ||
// d.ACCOUNT.Contains(obj.search) ||
// d.MA_TB.Contains(obj.search) ||
// d.TEN_TT.Contains(obj.search) ||
// d.DIACHI_TT.Contains(obj.search));
//
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() == "TEN_DV" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.TEN_DV);
}
else if (obj.sort.ToUpper() == "TEN_DV" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.TEN_DV);
}
else if (obj.sort.ToUpper() == "MA_QUANHUYEN" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.MA_QUANHUYEN);
}
else if (obj.sort.ToUpper() == "MA_QUANHUYEN" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.MA_QUANHUYEN);
}
else
{
data = data.OrderBy(m => m.MA_QUANHUYEN).ThenBy(m => m.TEN_DV);
}
}
else
{
data = data.OrderBy(m => m.MA_QUANHUYEN).ThenBy(m => m.TEN_DV);
}
//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 { _Con.Close(); }
//return Json(new { success = "Cập nhật thành công!" });
}
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.Connection.SQLServer();
obj.extension = "'.dbf','.txt','.xls','.xlsx','.zip','.xml'";
//Select
qry = $"SELECT * FROM FILE_MANAGER WHERE FLAG={obj.flag}";
//Extension
if (!Authentication.Auth.inRoles(new[] { Authentication.Roles.admin, Authentication.Roles.superadmin }))
{
if (!string.IsNullOrEmpty(obj.extension))
{
cdt += $"(EXTENSION IN({obj.extension}) OR TYPE='{TM.Common.Objects.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() }, JsonRequestBehavior.AllowGet);
//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 }, JsonRequestBehavior.AllowGet));
}
//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 }, JsonRequestBehavior.AllowGet);
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!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
//return Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet);
}
public ActionResult 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.Connection.SQLServer();
//
qry = $"SELECT i.*,g.TITLE AS GROUPTITLE FROM ITEMS i,GROUPS g WHERE i.GROUPID=g.GROUPID AND i.FLAG={obj.flag} AND g.FLAG=1";
//Get data for Search
if (!string.IsNullOrEmpty(obj.search))
{
cdt += $"(i.TITLE LIKE '%{obj.search}%' OR g.TITLE LIKE '%{obj.search}%') AND ";
}
if (!string.IsNullOrEmpty(obj.groupID) && obj.groupID != "0")
{
cdt += $"(i.GROUPID='{obj.groupID}') 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() }, JsonRequestBehavior.AllowGet);
//rsJson.MaxJsonLength = int.MaxValue;
//return rsJson;
}
//
var data = SQLServer.Connection.Query <Models.ITEMS_G>(qry);
////Get data for Search
//if (!string.IsNullOrEmpty(obj.search))
// data = data.Where(d =>
// d.MA_KH.Contains(obj.search) ||
// d.MA_TT_HNI.Contains(obj.search) ||
// d.ACCOUNT.Contains(obj.search) ||
// d.MA_TB.Contains(obj.search) ||
// d.TEN_TT.Contains(obj.search) ||
// d.DIACHI_TT.Contains(obj.search));
//
if (data.ToList().Count < 1)
{
return(Json(new { total = 0, rows = data }, JsonRequestBehavior.AllowGet));
}
//Get total item
var total = data.Count();
//Sort And Orders
if (!string.IsNullOrEmpty(obj.sort))
{
if (obj.sort.ToUpper() == "TITLE" && obj.order.ToLower() == "asc")
{
data = data.OrderBy(m => m.TITLE);
}
else if (obj.sort.ToUpper() == "TITLE" && obj.order.ToLower() == "desc")
{
data = data.OrderByDescending(m => m.TITLE);
}
else
{
data = data.OrderBy(m => m.ORDERS).ThenBy(m => m.GROUPID).ThenBy(m => m.TITLE);
}
}
else
{
data = data.OrderBy(m => m.ORDERS).ThenBy(m => m.GROUPID).ThenBy(m => m.TITLE);
}
//Page Site
var rs = data.Skip(obj.offset).Take(obj.limit).ToList();
var ReturnJson = Json(new { total = total, rows = rs }, JsonRequestBehavior.AllowGet);
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!" }, JsonRequestBehavior.AllowGet)); }
finally { SQLServer.Close(); }
//return Json(new { success = "Cập nhật thành công!" }, JsonRequestBehavior.AllowGet);
}