public void ExcelWinners(string SearchType = "", string SearchValue = "", int St = 1) { IEnumerable <WordWinners> list = db.Database.SqlQuery <WordWinners>(@" SELECT Name, Mobile, Age, MaleCount, FemaleCount, GiftMaleCount, GiftFemaleCount, TotalEntry, TotalShare, St FROM ( SELECT MAX(p.Name) as Name ,p.Mobile ,p.Age ,(SELECT COUNT(*) FROM dbo.WordEvents inm INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile AND inp.Gender = 'M' ) as MaleCount ,(SELECT COUNT(*) FROM dbo.WordEvents inm INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile AND inp.Gender = 'F' ) as FemaleCount ,(SELECT COUNT(*) FROM dbo.WordEvents inm INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile AND inm.GiftType = 'M' ) as GiftMaleCount ,(SELECT COUNT(*) FROM dbo.WordEvents inm INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile AND inm.GiftType = 'F' ) as GiftFemaleCount ,(SELECT COUNT(*) FROM dbo.WordEvents inm INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile ) as TotalEntry ,(SELECT COUNT(*) FROM dbo.WordEventShares ins INNER JOIN dbo.WordEvents inm ON ins.WordEventId = inm.Id INNER JOIN dbo.PersonalInfos inp ON inm.PersonalInfoId = inp.PersonalInfoId WHERE inp.Mobile = p.Mobile ) as TotalShare ,Max(St) as St FROM dbo.WordEvents m INNER JOIN dbo.PersonalInfos p ON m.PersonalInfoId = p.PersonalInfoId WHERE m.St = " + St.ToString() + @" GROUP BY p.Mobile, p.Age ) tb ORDER BY tb.TotalEntry DESC, TotalShare DESC "); if (SearchType.Equals("Name")) { list = list.Where(e => e.Name == SearchValue); } if (SearchType.Equals("Mobile")) { list = list.Where(e => e.Mobile == EventUtil.FormatMobile(SearchValue, "")); } if (SearchType.Equals("Age")) { int age = Int32.Parse(SearchValue); list = list.Where(e => e.Age == age); } string filename = "[킨더조이]_이벤트_요약_" + DateTime.Now.ToString("yyyyMMddhhmmss"); EventUtil.ExcelDownLoad(list.ToList(), filename); }
public void Excel(string SearchType = "", string SearchValue = "", string StartDate = "", string EndDate = "") { var list = db.WordEvents.AsQueryable(); if (SearchType.Equals("Name")) { list = list.Where(e => e.PersonalInfo.Name == SearchValue); } if (SearchType.Equals("Mobile")) { list = list.Where(e => e.PersonalInfo.Mobile == EventUtil.FormatMobile(SearchValue, "")); } if (SearchType.Equals("Age")) { int age = Int32.Parse(SearchValue); list = list.Where(e => e.PersonalInfo.Age == age); } if (SearchType.Equals("Gender")) { list = list.Where(e => e.PersonalInfo.Gender == SearchValue); } if (SearchType.Equals("GiftType")) { list = list.Where(e => e.GiftType == SearchValue); } if (string.IsNullOrEmpty(StartDate) == false) { DateTime startDate = Convert.ToDateTime(StartDate); list = list.Where(e => e.RegisteredAt >= startDate); } if (string.IsNullOrEmpty(EndDate) == false) { DateTime endDate = Convert.ToDateTime(EndDate); endDate = endDate.AddHours(23); endDate = endDate.AddMinutes(59); endDate = endDate.AddSeconds(59); list = list.Where(e => e.RegisteredAt <= endDate); } list = list.OrderByDescending(e => e.Id); var excel = from e in list select new { 이름 = e.PersonalInfo.Name, 연락처 = e.PersonalInfo.Mobile, 성별 = e.PersonalInfo.Gender, 나이 = e.PersonalInfo.Age, 상품타입 = e.GiftType, 응모채널 = e.Channel, 아이피 = e.Ip, 등록일 = e.RegisteredAt }; string filename = "[킨더조이]_이벤트_" + DateTime.Now.ToString("yyyyMMddhhmmss"); EventUtil.ExcelDownLoad(excel.ToList(), filename); }