Example #1
0
        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);
        }
Example #4
0
        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);
        }