ExtensionMethods.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Drawing;
  4. using System.Linq;
  5. using System.Linq.Dynamic.Core;
  6. using FMSAdmin.Entities;
  7. using FMSAdmin.Models;
  8. using OfficeOpenXml;
  9. using OfficeOpenXml.Style;
  10. namespace FMSAdmin.Helpers {
  11. public static class ExtensionMethods {
  12. public static IEnumerable<CmUser> WithoutPasswords(this IEnumerable<CmUser> users) {
  13. if (users == null) return null;
  14. return users.Select(x => x.WithoutPassword());
  15. }
  16. public static CmUser WithoutPassword(this CmUser user) {
  17. if (user == null) return null;
  18. user.Passwd = null;
  19. return user;
  20. }
  21. private static Type GetValueType(Type type, string path) {
  22. //typeof(T).Namespace == "FMSAdmin.Entities"
  23. string[] fields = path.Split(".");
  24. Type currentType = type;
  25. Type findType = null;
  26. foreach (var field in fields) {
  27. findType = null;
  28. foreach (var prop in currentType.GetProperties()) {
  29. if (field == prop.Name) {
  30. findType = prop.PropertyType;
  31. break;
  32. }
  33. }
  34. if (findType == null) {
  35. currentType = null;
  36. break;
  37. } else {
  38. currentType = findType;
  39. }
  40. }
  41. return currentType;
  42. }
  43. // IQueryable 기본 Entity 검색
  44. public static IQueryable<T> Filter<T>(this IQueryable<T> query, PagingRequest.Condition[] conds) {
  45. if (conds == null) {
  46. return query;
  47. }
  48. foreach (var cond in conds) {
  49. var type = GetValueType(typeof(T), cond.field);
  50. object value = cond.value;
  51. if (type != null) {
  52. switch (Type.GetTypeCode(type)) {
  53. case TypeCode.Int16:
  54. case TypeCode.Int32:
  55. case TypeCode.Int64:
  56. case TypeCode.UInt16:
  57. case TypeCode.UInt32:
  58. case TypeCode.UInt64:
  59. case TypeCode.Byte:
  60. case TypeCode.Decimal:
  61. case TypeCode.Single:
  62. case TypeCode.Double:
  63. value = int.Parse(cond.value.ToString());
  64. break;
  65. case TypeCode.Boolean:
  66. value = bool.Parse(cond.value.ToString());
  67. break;
  68. default:
  69. value = cond.value.ToString();
  70. break;
  71. }
  72. if (cond.op == "eq") {
  73. if (cond.value == null) {
  74. query = query.Where(cond.field + " == null");
  75. } else {
  76. if (cond.value == ":null") {
  77. query = query.Where(cond.field + " == null");
  78. } else if (cond.value == ":empty") {
  79. query = query.Where(cond.field + " == @0 ", "");
  80. } else {
  81. query = query.Where(cond.field + " == @0 ", value);
  82. }
  83. }
  84. } else if (cond.op == "cn") {
  85. query = query.Where(cond.field + ".Replace(\" \", \"\").Contains(@0) ", value?.ToString().Replace(" ", ""));
  86. } else if (cond.op == "sw") {
  87. query = query.Where(cond.field + ".StartsWith(@0) ", value);
  88. } else if (cond.op == "ew") {
  89. query = query.Where(cond.field + ".EndsWith(@0) ", value);
  90. } else if (cond.op == "ne") {
  91. if (cond.value == null) {
  92. query = query.Where(cond.field + " != null");
  93. } else {
  94. if (cond.value == ":null") {
  95. query = query.Where(cond.field + " != null");
  96. } else if (cond.value == ":empty") {
  97. query = query.Where(cond.field + " != @0 ", "");
  98. } else {
  99. query = query.Where(cond.field + " != @0 ", value);
  100. }
  101. }
  102. } else if (cond.op == "in") {
  103. // 여기 배열 변환 나중에..
  104. var jsonArray = cond.value;
  105. if (jsonArray != null) {
  106. query = query.Where("@0.Contains(outerIt.Account)", jsonArray.ToList());
  107. }
  108. } else if (cond.op == "gt") {
  109. query = query.Where(cond.field + " > @0", value);
  110. } else if (cond.op == "ge") {
  111. query = query.Where(cond.field + " >= @0", value);
  112. } else if (cond.op == "lt") {
  113. query = query.Where(cond.field + " < @0", value);
  114. } else if (cond.op == "le") {
  115. query = query.Where(cond.field + " <= @0", value);
  116. }
  117. }
  118. }
  119. return query;
  120. }
  121. // IQueryable 기본 Entity 정렬
  122. public static IQueryable<T> Sort<T>(this IQueryable<T> query, PagingRequest.Sort sort) {
  123. if (sort == null) {
  124. return query;
  125. }
  126. if (sort.field == null) {
  127. return query;
  128. }
  129. var type = GetValueType(typeof(T), sort.field);
  130. if (type != null) {
  131. if (sort.order == "asc") {
  132. query = query.OrderBy(sort.field + " ascending");
  133. } else if (sort.order == "desc") {
  134. query = query.OrderBy(sort.field + " descending");
  135. }
  136. }
  137. return query;
  138. }
  139. public static IOrderedQueryable<T> ThenSort<T>(this IOrderedQueryable<T> query, PagingRequest.Sort sort) {
  140. if (sort == null) {
  141. return query;
  142. }
  143. if (sort.field == null) {
  144. return query;
  145. }
  146. var type = GetValueType(typeof(T), sort.field);
  147. if (type != null) {
  148. if (sort.order == "asc") {
  149. query = query.ThenBy(sort.field + " ascending");
  150. } else if (sort.order == "desc") {
  151. query = query.ThenBy(sort.field + " descending");
  152. }
  153. }
  154. return query;
  155. }
  156. // IQueryable 기본 Entity 정렬 (멀티정렬)
  157. public static IQueryable<T> Sort<T>(this IQueryable<T> query, params PagingRequest.Sort[] sorts) {
  158. IList<string> sortList = new List<string>();
  159. foreach (var sort in sorts) {
  160. if (sort == null) {
  161. continue;
  162. }
  163. if (sort.field == null) {
  164. continue;
  165. }
  166. var type = GetValueType(typeof(T), sort.field);
  167. if (type != null) {
  168. if (sort.order == "asc") {
  169. sortList.Add(sort.field + " ascending");
  170. } else if (sort.order == "desc") {
  171. sortList.Add(sort.field + " descending");
  172. }
  173. }
  174. }
  175. if (sortList.Count > 0) {
  176. query = query.OrderBy(string.Join(",", sortList));
  177. }
  178. return query;
  179. }
  180. // 첫글자 대문자
  181. public static string FirstCharToUpper(this string input) {
  182. return input.First().ToString().ToUpper() + String.Join("", input.Skip(1));
  183. }
  184. // Dictionary값 쉽게 사용
  185. public static TV GetValue<TK, TV>(this IDictionary<TK, TV> dict, TK key, TV defaultValue = default(TV)) {
  186. TV value;
  187. return dict.TryGetValue(key, out value) ? value : defaultValue;
  188. }
  189. // 요일 한글로 변환?
  190. public static string GetDayofWeek(this DateTime datetime) {
  191. if (datetime.DayOfWeek == DayOfWeek.Monday) {
  192. return "월";
  193. } else if (datetime.DayOfWeek == DayOfWeek.Tuesday) {
  194. return "화";
  195. } else if (datetime.DayOfWeek == DayOfWeek.Wednesday) {
  196. return "수";
  197. } else if (datetime.DayOfWeek == DayOfWeek.Thursday) {
  198. return "목";
  199. } else if (datetime.DayOfWeek == DayOfWeek.Friday) {
  200. return "금";
  201. } else if (datetime.DayOfWeek == DayOfWeek.Saturday) {
  202. return "토";
  203. } else if (datetime.DayOfWeek == DayOfWeek.Sunday) {
  204. return "일";
  205. }
  206. return "";
  207. }
  208. // 엑셀 스타일 & 번호 넣기
  209. public static void AddStyle(this ExcelWorksheet workSheet, PagingRequest.Column[] columns = null, bool sort = true, string title = null) {
  210. var lastcol = 1;
  211. for (int i = 1; i <= workSheet.Dimension.End.Column; i++) {
  212. // 배경색
  213. workSheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
  214. workSheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
  215. if (columns != null) {
  216. var col = columns.SingleOrDefault(c => c.field == workSheet.Cells[1, i].Text);
  217. if (col != null) {
  218. workSheet.Cells[1, i].Value = col.name;
  219. workSheet.Column(i).AutoFit();
  220. if (col.align == "right") {
  221. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  222. } else if (col.align == "left") {
  223. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
  224. } else {
  225. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
  226. }
  227. if (col.format == "price") {
  228. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  229. workSheet.Column(i).Style.Numberformat.Format = "#,###";
  230. } else if (col.format == "number") {
  231. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  232. } else if (col.format == "pricePoint") {
  233. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  234. workSheet.Column(i).Style.Numberformat.Format = "#,##0.0?";
  235. } else if (col.format == "price2") {
  236. workSheet.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  237. workSheet.Column(i).Style.Numberformat.Format = "#,##0";
  238. }
  239. lastcol = i;
  240. } else {
  241. workSheet.Column(i).Width = 0;
  242. }
  243. }
  244. }
  245. // 번호넣기
  246. workSheet.InsertColumn(1, 1);
  247. workSheet.Cells[1, 1].Value = "번호";
  248. workSheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
  249. workSheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
  250. if (sort) {
  251. int num = 1;
  252. for (int i = workSheet.Dimension.Start.Row + 1; i <= workSheet.Dimension.End.Row; i++) {
  253. workSheet.Cells[i, 1].Value = num++;
  254. }
  255. } else {
  256. int num = workSheet.Dimension.End.Row - workSheet.Dimension.Start.Row;
  257. for (int i = workSheet.Dimension.Start.Row + 1; i <= workSheet.Dimension.End.Row; i++) {
  258. workSheet.Cells[i, 1].Value = num--;
  259. }
  260. }
  261. lastcol++;
  262. // 타이틀 & 날짜 넣기
  263. workSheet.InsertRow(1, 2);
  264. if (string.IsNullOrEmpty(title)) {
  265. title = "엑셀파일";
  266. }
  267. workSheet.Cells[1, 1].Value = title;
  268. workSheet.Cells[2, lastcol].Value = DateTime.Now.ToString("yyyy-MM-dd");
  269. workSheet.Cells[2, lastcol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
  270. }
  271. }
  272. }