Beispiel #1
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);
        }
Beispiel #2
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);
        }