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);
}