public static List <LotteryOrder> GetTodayUserBett(string btime, string etime, string cpcode = "")
{
string sqlwhere = "";
if (!string.IsNullOrEmpty(btime))
{
sqlwhere += " and CreateTime >='" + btime + "'";
}
if (!string.IsNullOrEmpty(etime))
{
sqlwhere += " and CreateTime <'" + etime + "'";
}
if (!string.IsNullOrEmpty(cpcode))
{
sqlwhere += " and CpCode in ('" + cpcode.TrimEnd(',').Replace(",", "','") + "')";
}
string sqlstr = @" select a.CPCode,a.CPName ,b.UserID ,c.LoginName as UserName,ISNULL(b.PayFee,0) PayFee from Lottery a
join (select UserID,SUM(PayFee) PayFee,CPcode from LotteryOrder (nolock) where Status<>3 " + sqlwhere + " group by UserID,CPCode ) b on a.CPCode=b.CPCode" +
" join M_Users c on c.UserID=b.UserID where c.Status<>9 order by a.CPCode ";
DataTable dt = LotteryOrderDAL.GetDataTable(sqlstr);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetLotteryWin(string cpCode, int status, decimal winFee, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string btime = "", string etime = "")
{
string sqlwhere = " b.cpCode='" + cpCode + "'";
if (status > -1)
{
sqlwhere += " and b.Status=" + status;
}
if (!string.IsNullOrEmpty(btime))
{
sqlwhere += " and b.CreateTime>='" + btime + "'";
}
if (winFee > 0)
{
sqlwhere += " and b.WinFee>=" + winFee;
}
if (!string.IsNullOrEmpty(etime))
{
sqlwhere += " and b.CreateTime<='" + etime + " 23:59:59:999'";
}
DataTable dt = CommonBusiness.GetPagerData(" LotteryOrder b join M_Users a on a.UserID=b.UserID ",
" b.* ,a.UserName", sqlwhere, " b.AutoID ", pageSize, pageIndex,
out totalCount, out pageCount);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetAccReportList(string btime, string etime, int playtype, string userid, int pageIndex, int pageSize, ref int totalCount, ref int pageCount, int self = 0, string username = "")
{
string whereSql = " a.AutoID>0 ";
if (!string.IsNullOrEmpty(btime))
{
whereSql += " and a.CreateTime>='" + btime + "'";
}
if (!string.IsNullOrEmpty(etime))
{
whereSql += " and a.CreateTime<'" + etime + "'";
}
if (playtype > -1)
{
whereSql += " and a.playtype=" + playtype;
}
if (!string.IsNullOrEmpty(userid))
{
if (self > 0)
{
if (self == 1)
{
whereSql += " and a.UserID in(select UserID from UserRelation where ParentID='" + userid + "')";
}
else if (self == 2)
{
whereSql += " and a.UserID in(select UserID from UserRelation where Parents like '%" + userid +
"%')";
}
else
{
whereSql += " and a.UserID='" + userid + "' ";
}
}
else
{
whereSql += " and ( a.UserID in(select UserID from UserRelation where Parents like '%" + userid + "%') or a.UserID='" + userid + "')";
}
}
if (!string.IsNullOrEmpty(username))
{
whereSql += " and c.LoginName='" + username.Trim() + "' ";
}
string clumstr = @" a.AccountChange, a.Type AccountType, a.CreateTime , '' Remark, a.AutoID,a.Account,a.PlayType,a.Remark PlayTypeName,c.LoginName UserName ,a.Type ChangeType ";
DataTable dt = CommonBusiness.GetPagerData(" AccountOperateRecord a join M_Users c on a.UseriD=c.Userid ", clumstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount, false);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow item in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static LotteryOrder GetUserOrderDetail(string lcode)
{
DataTable dt = LotteryOrderDAL.BaseProvider.GetLotteryOrderDetail(lcode);
LotteryOrder model = null;
if (dt.Rows.Count == 1)
{
model = new LotteryOrder();
model.FillData(dt.Rows[0]);
}
return(model);
}
public static List <LotteryOrder> GetIssueNumReport(string cpcode, string issuenum = "")
{
string swhere = "";
if (!string.IsNullOrEmpty(issuenum))
{
swhere = " and issuenum='" + issuenum + "'";
}
DataTable dt =
LotteryOrderDAL.GetDataTable(
"select CPCOde,CPName,IssueNum,Type+'_'+Content Type,TypeName,Sum(payFee) payFee from LotteryOrder (nolock) where CPCode='" + cpcode + "' " + swhere + " group by CPCOde,CPName, IssueNum,Type+'_'+Content,TypeName ");
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetTodayBett(string btime, string etime)
{
string sqlwhere = "";
if (!string.IsNullOrEmpty(btime))
{
sqlwhere += " and CreateTime >='" + btime + "'";
}
if (!string.IsNullOrEmpty(etime))
{
sqlwhere += " and CreateTime <'" + etime + "'";
}
string sqlstr = @" select a.CPCode,a.CPName,ISNULL(b.PayFee,0) PayFee from Lottery a left join (select CPCode,SUM(PayFee) PayFee from LotteryOrder (nolock) where Status<>3 " + sqlwhere + " group by CPCode ) b on a.CPCode=b.CPCode ";
DataTable dt = LotteryOrderDAL.GetDataTable(sqlstr);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetLotteryOrder(string keyWords, string cpcode, string userid, string lcode, string issuenum, string type, string status, int winType, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, int self = 0, string begintime = "", string endtime = "", string username = "", string orderby = " a.AutoID ", string bcode = "", string winfee = "", string opentime = "", string opentimeend = "")
{
string tablename = "LotteryOrder (nolock) a left join M_Users b on a.UserID =b.UserID " +
"left join lotteryResult (nolock) c on a.IssueNum=c.IssueNum and a.CPCode=c.CPCode " +
" left join lotteryBettAuto (nolock) d on a.bcode=d.Bcode ";
string sqlwhere = " a.status<>9 ";
if (!string.IsNullOrEmpty(keyWords))
{
sqlwhere += " and (b.LoginName like '%" + keyWords + "%' or a.IssueNum like '%" + keyWords + "%' or a.LCode like '%" + keyWords + "%' or a.TypeName like '%" + keyWords + "%')";
}
if (!string.IsNullOrEmpty(type))
{
sqlwhere += " and a.Type like '%" + type + "%'";
}
if (!string.IsNullOrEmpty(bcode))
{
sqlwhere += " and a.bcode='" + bcode + "'";
}
if (!string.IsNullOrEmpty(status) && status != "-1")
{
sqlwhere += " and a.status in(" + status + ")";
}
if (!string.IsNullOrEmpty(cpcode))
{
sqlwhere += " and a.cpcode='" + cpcode + "'";
}
if (!string.IsNullOrEmpty(username))
{
sqlwhere += " and b.LoginName='" + username + "'";
}
if (winType > -1)
{
sqlwhere += " and a.WinType=" + winType;
}
if (!string.IsNullOrEmpty(winfee))
{
sqlwhere += " and a.WinFee>" + Convert.ToDecimal(winfee);
}
if (!string.IsNullOrEmpty(opentime))
{
sqlwhere += " and c.opentime>='" + opentime + "'";
}
if (!string.IsNullOrEmpty(opentimeend))
{
sqlwhere += " and c.opentime<'" + opentimeend + "'";
}
if (!string.IsNullOrEmpty(userid))
{
if (self > 0)
{
if (self == 1)
{
sqlwhere += " and a.UserID in(select UserID from UserRelation where ParentID='" + userid + "') ";
}
else if (self == 2)
{
sqlwhere += " and a.UserID in(select UserID from UserRelation where Parents like '%" + userid + "%')";
}
else
{
sqlwhere += " and a.UserID='" + userid + "' ";
}
}
else
{
sqlwhere += " and (a.UserID in(select UserID from UserRelation where Parents like '%" + userid + "%') or a.UserID='" + userid + "' )";
}
}
if (!string.IsNullOrEmpty(begintime))
{
sqlwhere += " and a.CreateTime>='" + begintime + "'";
}
if (!string.IsNullOrEmpty(endtime))
{
sqlwhere += " and a.CreateTime<'" + endtime + " 23:59:59:999'";
}
if (!string.IsNullOrWhiteSpace(lcode))
{
sqlwhere += " and a.LCode ='" + lcode + "'";
}
if (!string.IsNullOrEmpty(issuenum))
{
sqlwhere += " and a.IssueNum ='" + issuenum + "'";
}
bool isdesc = true;
if (!string.IsNullOrEmpty(orderby))
{
if (orderby.ToLower().IndexOf(" desc") > -1)
{
isdesc = false;
}
orderby = orderby.Replace("desc", " ").Replace("asc", "");
}
DataTable dt = CommonBusiness.GetPagerData(tablename, " a.*,b.LoginName as UserName,c.ResultNum, isnull(d.IsStart,-1) IsStart,isnull(d.Status,-1) BettStatus", sqlwhere, orderby, pageSize, pageIndex, out totalCount, out pageCount, isdesc);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetLotteryOrderReportList(string btime, string etime, int playtype, string cpcode, string userid, string lcode, string issuenum, string type, string state, int winType, int pageIndex, int pageSize, ref int totalCount, ref int pageCount, int self = 0)
{
string whereSql = " a.AutoID>0 ";
if (!string.IsNullOrEmpty(btime))
{
whereSql += " and a.CreateTime>='" + btime + "'";
}
if (!string.IsNullOrEmpty(etime))
{
whereSql += " and a.CreateTime<'" + etime + "'";
}
if (playtype > -1)
{
whereSql += " and a.playtype=" + playtype;
}
if (!string.IsNullOrEmpty(type))
{
whereSql += " and b.Type like '%" + type + "%'";
}
if (!string.IsNullOrEmpty(cpcode))
{
whereSql += " and b.cpcode='" + cpcode + "'";
}
if (winType > -1)
{
whereSql += " and b.WinType=" + winType;
}
if (!string.IsNullOrEmpty(userid))
{
if (self > 0)
{
if (self == 1)
{
whereSql += " and b.UserID in(select UserID from UserRelation where ParentID='" + userid + "')";
}
else if (self == 2)
{
whereSql += " and b.UserID in(select UserID from UserRelation where Parents like '%" + userid + "%')";
}
else
{
whereSql += " and b.UserID='" + userid + "' ";
}
}
}
if (!string.IsNullOrWhiteSpace(lcode))
{
if (!string.IsNullOrEmpty(state))
{
whereSql += " and b." + state + " ='" + lcode + "'";
}
else
{
whereSql += " and (b.LCode like '%" + lcode + "%' or b.BCode like '%" + lcode + "%') ";
}
}
if (!string.IsNullOrEmpty(issuenum))
{
whereSql += " and b.IssueNum ='" + issuenum + "'";
}
string clumstr = " b.LCode,b.IssueNum,b.Type,b.TypeName,b.CPCode,b.CPName, case when a.Type=0 then a.AccountChange else b.WinFee end WinFee,case when a.Type=1 then a.AccountChange else isnull(b.PayFee,0.00) end PayFee ,isnull(b.Remark,'') Remark, a.AutoID,a.Account ,a.PlayType,a.Remark PlayTypeName,c.UserName ,a.Type ChangeType ";
DataTable dt = CommonBusiness.GetPagerData(" AccountOperateRecord a join M_Users c on a.UseriD=c.Userid left join LotteryOrder b on a.Userid=b.Userid and a.FkCode=b.LCode and b.Status<>9 ", clumstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow item in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <LotteryOrder> GetLotteryOrder(string keyWords, string cpcode, string userid, string lcode, string issuenum, string type, int status, int winType, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, int self = 0, string begintime = "", string endtime = "")
{
string tablename = "LotteryOrder a left join M_Users b on a.UserID =b.UserID left join lotteryResult c on a.IssueNum=c.IssueNum and a.CPCode=c.CPCode ";
string sqlwhere = " a.status<>9 ";
if (!string.IsNullOrEmpty(keyWords))
{
sqlwhere += " and (b.UserName like '%" + keyWords + "%' or a.IssueNum like '%" + keyWords + "%' or a.LCode like '%" + keyWords + "%' or a.TypeName like '%" + keyWords + "%')";
}
if (!string.IsNullOrEmpty(type))
{
sqlwhere += " and a.Type like '%" + type + "%'";
}
if (status > -1)
{
sqlwhere += " and a.status=" + status;
}
if (!string.IsNullOrEmpty(cpcode))
{
sqlwhere += " and a.cpcode='" + cpcode + "'";
}
if (winType > -1)
{
sqlwhere += " and a.WinType=" + winType;
}
if (!string.IsNullOrEmpty(userid))
{
if (self > 0)
{
if (self == 1)
{
sqlwhere += " and a.UserID in(select UserID from UserRelation where ParentID='" + userid + "')";
}
else if (self == 2)
{
sqlwhere += " and a.UserID in(select UserID from UserRelation where Parents like '%" + userid + "%')";
}
else
{
sqlwhere += " and a.UserID='" + userid + "' ";
}
}
}
if (!string.IsNullOrEmpty(begintime))
{
sqlwhere += " and a.CreateTime>='" + begintime + "'";
}
if (!string.IsNullOrEmpty(endtime))
{
sqlwhere += " and a.CreateTime<'" + endtime + " 23:59:59:999'";
}
if (!string.IsNullOrWhiteSpace(lcode))
{
sqlwhere += " and a.LCode ='" + lcode + "'";
}
if (!string.IsNullOrEmpty(issuenum))
{
sqlwhere += " and a.IssueNum ='" + issuenum + "'";
}
DataTable dt = CommonBusiness.GetPagerData(tablename, "a.*,b.UserName,c.ResultNum ", sqlwhere, "a.AutoID ", pageSize, pageIndex, out totalCount, out pageCount);
List <LotteryOrder> list = new List <LotteryOrder>();
foreach (DataRow dr in dt.Rows)
{
LotteryOrder model = new LotteryOrder();
model.FillData(dr);
list.Add(model);
}
return(list);
}
