using System; using System.Collections.Generic; using System.Drawing; using System.Linq; using System.Linq.Dynamic.Core; using FMSAdmin.Entities; using FMSAdmin.Models; using OfficeOpenXml; using OfficeOpenXml.Style; namespace FMSAdmin.Helpers { public static class ExtensionMethods { public static IEnumerable<CmUser> WithoutPasswords(this IEnumerable<CmUser> users) { if (users == null) return null; return users.Select(x => x.WithoutPassword()); } public static CmUser WithoutPassword(this CmUser user) { if (user == null) return null; user.Passwd = null; return user; } private static Type GetValueType(Type type, string path) { //typeof(T).Namespace == "FMSAdmin.Entities" string[] fields = path.Split("."); Type currentType = type; Type findType = null; foreach (var field in fields) { findType = null; foreach (var prop in currentType.GetProperties()) { if (field == prop.Name) { findType = prop.PropertyType; break; } } if (findType == null) { currentType = null; break; } else { currentType = findType; } } return currentType; } // IQueryable 기본 Entity 검색 public static IQueryable<T> Filter<T>(this IQueryable<T> query, PagingRequest.Condition[] conds) { if (conds == null) { return query; } foreach (var cond in conds) { var type = GetValueType(typeof(T), cond.field); object value = cond.value; if (type != null) { switch (Type.GetTypeCode(type)) { case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.UInt16: case TypeCode.UInt32: case TypeCode.UInt64: case TypeCode.Byte: case TypeCode.Decimal: case TypeCode.Single: case TypeCode.Double: value = int.Parse(cond.value.ToString()); break; case TypeCode.Boolean: value = bool.Parse(cond.value.ToString()); break; default: value = cond.value.ToString(); break; } if (cond.op == "eq") { if (cond.value == null) { query = query.Where(cond.field + " == null"); } else { if (cond.value == ":null") { query = query.Where(cond.field + " == null"); } else if (cond.value == ":empty") { query = query.Where(cond.field + " == @0 ", ""); } else { query = query.Where(cond.field + " == @0 ", value); } } } else if (cond.op == "cn") { query = query.Where(cond.field + ".Replace(\" \", \"\").Contains(@0) ", value?.ToString().Replace(" ", "")); } else if (cond.op == "sw") { query = query.Where(cond.field + ".StartsWith(@0) ", value); } else if (cond.op == "ew") { query = query.Where(cond.field + ".EndsWith(@0) ", value); } else if (cond.op == "ne") { if (cond.value == null) { query = query.Where(cond.field + " != null"); } else { if (cond.value == ":null") { query = query.Where(cond.field + " != null"); } else if (cond.value == ":empty") { query = query.Where(cond.field + " != @0 ", ""); } else { query = query.Where(cond.field + " != @0 ", value); } } } else if (cond.op == "in") { // 여기 배열 변환 나중에.. var jsonArray = cond.value; if (jsonArray != null) { query = query.Where("@0.Contains(outerIt.Account)", jsonArray.ToList()); } } else if (cond.op == "gt") { query = query.Where(cond.field + " > @0", value); } else if (cond.op == "ge") { query = query.Where(cond.field + " >= @0", value); } else if (cond.op == "lt") { query = query.Where(cond.field + " < @0", value); } else if (cond.op == "le") { query = query.Where(cond.field + " <= @0", value); } } } return query; } // IQueryable 기본 Entity 정렬 public static IQueryable<T> Sort<T>(this IQueryable<T> query, PagingRequest.Sort sort) { if (sort == null) { return query; } if (sort.field == null) { return query; } var type = GetValueType(typeof(T), sort.field); if (type != null) { if (sort.order == "asc") { query = query.OrderBy(sort.field + " ascending"); } else if (sort.order == "desc") { query = query.OrderBy(sort.field + " descending"); } } return query; } public static IOrderedQueryable<T> ThenSort<T>(this IOrderedQueryable<T> query, PagingRequest.Sort sort) { if (sort == null) { return query; } if (sort.field == null) { return query; } var type = GetValueType(typeof(T), sort.field); if (type != null) { if (sort.order == "asc") { query = query.ThenBy(sort.field + " ascending"); } else if (sort.order == "desc") { query = query.ThenBy(sort.field + " descending"); } } return query; } // IQueryable 기본 Entity 정렬 (멀티정렬) public static IQueryable<T> Sort<T>(this IQueryable<T> query, params PagingRequest.Sort[] sorts) { IList<string> sortList = new List<string>(); foreach (var sort in sorts) { if (sort == null) { continue; } if (sort.field == null) { continue; } var type = GetValueType(typeof(T), sort.field); if (type != null) { if (sort.order == "asc") { sortList.Add(sort.field + " ascending"); } else if (sort.order == "desc") { sortList.Add(sort.field + " descending"); } } } if (sortList.Count > 0) { query = query.OrderBy(string.Join(",", sortList)); } return query; } // 첫글자 대문자 public static string FirstCharToUpper(this string input) { return input.First().ToString().ToUpper() + String.Join("", input.Skip(1)); } // Dictionary값 쉽게 사용 public static TV GetValue<TK, TV>(this IDictionary<TK, TV> dict, TK key, TV defaultValue = default(TV)) { TV value; return dict.TryGetValue(key, out value) ? value : defaultValue; } // 요일 한글로 변환? public static string GetDayofWeek(this DateTime datetime) { if (datetime.DayOfWeek == DayOfWeek.Monday) { return "월"; } else if (datetime.DayOfWeek == DayOfWeek.Tuesday) { return "화"; } else if (datetime.DayOfWeek == DayOfWeek.Wednesday) { return "수"; } else if (datetime.DayOfWeek == DayOfWeek.Thursday) { return "목"; } else if (datetime.DayOfWeek == DayOfWeek.Friday) { return "금"; } else if (datetime.DayOfWeek == DayOfWeek.Saturday) { return "토"; } else if (datetime.DayOfWeek == DayOfWeek.Sunday) { return "일"; } return ""; } // 엑셀 스타일 & 번호 넣기 public static void AddStyle(this ExcelWorksheet workSheet, PagingRequest.Column[] columns = null, bool sort = true, string title = null) { var lastcol = 1; for (int i = 1; i <= workSheet.Dimension.End.Column; i++) { // 배경색 workSheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid; workSheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.LightBlue); if (columns != null) { var col = columns.SingleOrDefault(c => c.field == workSheet.Cells[1, i].Text); if (col != null) { workSheet.Cells[1, i].Value = col.name; workSheet.Column(i).AutoFit(); if (col.align == "right") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } else if (col.align == "left") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; } else { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } if (col.format == "price") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; workSheet.Column(i).Style.Numberformat.Format = "#,###"; } else if (col.format == "number") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } else if (col.format == "pricePoint") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; workSheet.Column(i).Style.Numberformat.Format = "#,##0.0?"; } else if (col.format == "price2") { workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; workSheet.Column(i).Style.Numberformat.Format = "#,##0"; } lastcol = i; } else { workSheet.Column(i).Width = 0; } } } // 번호넣기 workSheet.InsertColumn(1, 1); workSheet.Cells[1, 1].Value = "번호"; workSheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid; workSheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.LightBlue); if (sort) { int num = 1; for (int i = workSheet.Dimension.Start.Row + 1; i <= workSheet.Dimension.End.Row; i++) { workSheet.Cells[i, 1].Value = num++; } } else { int num = workSheet.Dimension.End.Row - workSheet.Dimension.Start.Row; for (int i = workSheet.Dimension.Start.Row + 1; i <= workSheet.Dimension.End.Row; i++) { workSheet.Cells[i, 1].Value = num--; } } lastcol++; // 타이틀 & 날짜 넣기 workSheet.InsertRow(1, 2); if (string.IsNullOrEmpty(title)) { title = "엑셀파일"; } workSheet.Cells[1, 1].Value = title; workSheet.Cells[2, lastcol].Value = DateTime.Now.ToString("yyyy-MM-dd"); workSheet.Cells[2, lastcol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } } }