예제 #1
0
        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)));
        }
예제 #2
0
        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);
        }
예제 #3
0
        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)));
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }