/// <summary>
/// 取得列表
/// </summary>
/// <param name="memberid"></param>
/// <returns>a:username; b:levelId; c:externalId; d:loginDateTime; e:loginIP; f:createDateTime; g:updatedatetime; h:updateUserId</returns>
public JArray AgentListStructHandle(AgentListStruct br)
{
JArray ja = new JArray();
Define define = new Define();
string select_str = "select * from [lottery].[dbo].[member] with(nolock) ";
string where_str = "";
where_str += glbf.GetSQLSameLine();
if (br.L8 != null)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " l8 = " + br.L8.ToString();
}
if (br.L7 != null)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " l7 = " + br.L7.ToString();
}
if (br.LevelId != null)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " levelId = " + br.LevelId.ToString();
}
if (br.CreateBeginDateTime.HasValue)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " createDateTime >= '" + ((DateTime)br.CreateBeginDateTime).ToString("yyyy/MM/dd HH:mm:ss") + "'";
}
if (br.CreateEndDateTime.HasValue)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " createDateTime <= '" + ((DateTime)br.CreateEndDateTime).ToString("yyyy/MM/dd HH:mm:ss") + "'";
}
if (br.status != null)
{
if (where_str != "")
{
where_str += " and ";
}
where_str += " status = '" + br.status + "'";
}
List <member> agentList = new List <member>();
if (where_str != "")
{
agentList.AddRange(conn.Query <member>(select_str + " where " + where_str).ToList());
}
else
{
agentList.AddRange(conn.Query <member>(select_str).ToList());
}
foreach (var m in agentList)
{
JToken jt = new JObject();
jt["a"] = m.username;
jt["b"] = m.levelId;
jt["c"] = m.companyId;
if (m.loginDateTime.HasValue == false)
{
jt["d"] = "";
}
else
{
jt["d"] = m.loginDateTime.Value.ToString("yyyy/MM/dd HH:mm:ss");
}
if (m.loginIP == null)
{
jt["e"] = "";
}
else
{
jt["e"] = m.loginIP;
}
jt["f"] = m.createDateTime.Value.ToString("yyyy/MM/dd HH:mm:ss");
if (m.updateDateTime.HasValue == false)
{
jt["g"] = "";
}
else
{
jt["g"] = m.updateDateTime.Value.ToString("yyyy/MM/dd HH:mm:ss");
}
if (m.updateUserId.HasValue)
{
jt["h"] = "";
}
else
{
jt["h"] = m.updateUserId;
}
jt["i"] = m.id;
jt["j"] = m.l9;
jt["k"] = m.l8;
jt["l"] = m.l7;
jt["m"] = m.l1;
jt["n"] = m.status;
jt["o"] = m.nickname;
ja.Add(jt);
}
return(ja);
}
public JArray BetFormSummaryStructHandle(BetFormSummaryStruct br)
{
JArray ja = new JArray();
string where_str_bet = glbf.GetSQLSameLine();
if (br.MemberIdList != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
string memberid = "";
foreach (var item in br.MemberIdList)
{
memberid += item.ToString() + ",";
}
memberid.TrimEnd(',');
where_str_bet += " id in (" + memberid + ")";
}
if (br.L8 != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " l8 = " + br.L8;
}
if (br.L7 != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " l7 = " + br.L7;
}
if (br.L1 != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " l1 = " + br.L1;
}
if (br.LevelId != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " levelId = " + br.LevelId;
}
string select_str = "SELECT id, username, externalId , companyId, levelId FROM [lottery].[dbo].[member] with(nolock) ";
List <member> memberList;
if (where_str_bet == "")
{
memberList = conn.Query <member>(select_str).ToList();
}
else
{
memberList = conn.Query <member>(select_str + " where " + where_str_bet).ToList();
}
foreach (var mem in memberList)
{
where_str_bet = "";
if (br.IsDeliver != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " isDeliver = " + Convert.ToInt32(br.IsDeliver).ToString();
}
if (br.Status != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " status = " + br.Status.ToString();
}
if (br.PeriodId != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " periodId = '" + br.PeriodId + "'";
}
if (br.BetType != null)
{
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " betType = " + br.BetType.ToString();
}
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " beginDateTime >= '" + br.BeginDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'";
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " beginDateTime <= '" + br.EndDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'";
if (where_str_bet != "")
{
where_str_bet += " and ";
}
where_str_bet += " l" + mem.levelId.ToString() + " = " + mem.id.ToString();
select_str = "SELECT count(id) as id, sum(r8) as r8, sum(r7) as r7, sum(r1) as r1 FROM [lottery].[dbo].[betForm] with(nolock)";
var betFormSum = conn.Query <betForm>(select_str + " where " + where_str_bet).FirstOrDefault();
JToken jt = new JObject();
jt["a"] = mem.id;
if (mem.levelId == Member.Define.MemberLevels.SA)
{
jt["b"] = betFormSum.r8;
jt["c"] = betFormSum.r7;
jt["d"] = betFormSum.r1;
}
if (mem.levelId == Member.Define.MemberLevels.UpAgent)
{
jt["b"] = betFormSum.r8;
jt["c"] = betFormSum.r7;
jt["d"] = betFormSum.r1;
}
if (mem.levelId == Member.Define.MemberLevels.Agent)
{
jt["b"] = "";
jt["c"] = betFormSum.r7;
jt["d"] = betFormSum.r1;
}
if (mem.levelId == Member.Define.MemberLevels.Member)
{
jt["b"] = "";
jt["c"] = "";
jt["d"] = betFormSum.r1;
}
jt["e"] = betFormSum.id; // betform count
jt["f"] = mem.companyId;
jt["g"] = mem.username;
jt["h"] = mem.externalId;
ja.Add(jt);
}
return(ja);
}