123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293 |
- 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;
- }
- }
- }
|