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