public ActionResult Share(string SearchType = "", string SearchValue = "", string StartDate = "", string EndDate = "", int page = 1) { var list = db.WordEventShares.AsQueryable(); if (SearchType.Equals("Name")) { list = list.Where(e => e.WordEvent.PersonalInfo.Name == SearchValue); } if (SearchType.Equals("Mobile")) { list = list.Where(e => e.WordEvent.PersonalInfo.Mobile == EventUtil.FormatMobile(SearchValue, "")); } if (SearchType.Equals("Age")) { int age = Int32.Parse(SearchValue); list = list.Where(e => e.WordEvent.PersonalInfo.Age == age); } if (SearchType.Equals("Gender")) { list = list.Where(e => e.WordEvent.PersonalInfo.Gender == SearchValue); } if (SearchType.Equals("GiftType")) { list = list.Where(e => e.WordEvent.GiftType == SearchValue); } if (SearchType.Equals("SnsType")) { list = list.Where(e => e.SnsType == 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); ViewBag.SearchType = SearchType; ViewBag.SearchValue = SearchValue; ViewBag.StartDate = StartDate; ViewBag.EndDate = EndDate; return(View(list.ToPagedList(page, 30))); }
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 ActionResult Winners(string SearchType = "", string SearchValue = "", int page = 1, int St = 1) { ViewBag.SearchType = SearchType; ViewBag.SearchValue = SearchValue; ViewBag.St = St; IEnumerable <WordWinners> list = db.Database.SqlQuery <WordWinners>(@" SELECT Name, Mobile, Age, MaleCount, FemaleCount, GiftMaleCount, GiftFemaleCount, TotalEntry, TotalShare 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 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); } return(View(list.ToPagedList(page, 30))); }
public WordEvent EntryWord(WordEntry entry, string ip) { PersonalInfos personal = new PersonalInfos { Name = entry.Name, Mobile = EventUtil.FormatMobile(entry.Mobile, ""), Age = entry.Age, Gender = entry.Genger, IpAddress = ip, EventId = "20150801-Word" }; int St = this.CalcSt(); WordEvent wordEvent = new WordEvent { Channel = entry.Channel, GiftType = entry.GiftType, Ip = ip, St = St }; using (var transaction = db.Database.BeginTransaction()) { try { db.PersonalInfos.Add(personal); db.SaveChanges(); wordEvent.PersonalInfoId = personal.PersonalInfoId; db.WordEvents.Add(wordEvent); db.SaveChanges(); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw new InvalidOperationException("일시적인 오류가 발생 하였습니다. 새로고침 후 다시 시도해 주세요."); } } return(wordEvent); }
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); }