public static List <UserImgs> GetUserImgList(string userid, int status, int pageIndex, int pageSize, ref int totalCount, ref int pageCount)
{
string whereSql = " a.Status<>9 ";
if (status > -1)
{
whereSql += " and a.status=" + status;
}
if (!string.IsNullOrEmpty(userid))
{
whereSql += " and a.UserID='" + userid + "' ";
}
string clumstr = "a.*,b.Name as UserName ";
DataTable dt = CommonBusiness.GetPagerData("UserImgs a left join M_Users b on a.Userid=b.Userid ", clumstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <UserImgs> list = new List <UserImgs>();
foreach (DataRow item in dt.Rows)
{
UserImgs model = new UserImgs();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <M_Users> GetImgList(string userid, int sex, int pageIndex, int pageSize, ref int totalCount, ref int pageCount)
{
string whereSql = " a.Status<>9 and b.ImgCount>0";
if (sex > -1)
{
whereSql += " and a.Sex=" + sex;
}
if (!string.IsNullOrEmpty(userid))
{
whereSql += " and a.UserID='" + userid + "' ";
}
string clumstr = "a.userID,a.Avatar,a.Name,a.Age,a.LoginName,a.MyService,a.Province,a.City,a.District,a.CreateTime,a.Status,a.Sex,a.IsMarry,a.Education," +
"a.BHeight,a.Levelid,a.BWeight,a.MyContent,a.MyCharacter,a.TalkTo,a.BPay,a.Account,b.ImgCount,b.IsLogin,b.RecommendCount,b.SeeCount";
DataTable dt = CommonBusiness.GetPagerData("M_Users a join userReport b on a.Userid=b.Userid ", clumstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <M_Users> list = new List <M_Users>();
foreach (DataRow item in dt.Rows)
{
M_Users model = new M_Users();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <UserImgs> GetUserImgList(string keywords, int status, string begintime, string endtime, int pageIndex, int pageSize, ref int totalCount, ref int pageCount)
{
string whereSql = " a.Status<>9 ";
if (status > -1)
{
whereSql += " and a.status=" + status;
}
if (!string.IsNullOrEmpty(begintime))
{
whereSql += " and a.CreateTime>='" + begintime + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endtime))
{
whereSql += " and a.CreateTime<'" + endtime + " 23:59:59:999'";
}
if (!string.IsNullOrEmpty(keywords))
{
whereSql += " and b.Name like '%" + keywords + "%' ";
}
string clumstr = "a.*,b.Name as UserName ";
DataTable dt = CommonBusiness.GetPagerData("UserImgs a left join M_Users b on a.Userid=b.Userid ", clumstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <UserImgs> list = new List <UserImgs>();
foreach (DataRow item in dt.Rows)
{
UserImgs model = new UserImgs();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <Product> GetProductList(string pname, string typecode, string pcode, int page, int pagesize, ref int totalcount,
ref int pagecount, string orderclum = "", bool orderby = false)
{
string sqlwhere = " 1=1 ";
if (!string.IsNullOrEmpty(pname))
{
sqlwhere += " and productname like '%" + pname + "%' ";
}
if (!string.IsNullOrEmpty(typecode))
{
sqlwhere += " and typecode='" + typecode + "' ";
}
if (!string.IsNullOrEmpty(pcode))
{
sqlwhere += " and productcode='" + pcode + "' ";
}
DataTable dt = CommonBusiness.GetPagerData("products", "*", sqlwhere, "Autoid", orderclum, pagesize, page,
out totalcount, out page, orderby);
List <Product> list = new List <Product>();
foreach (DataRow row in dt.Rows)
{
Product pt = new Product();
pt.FillData(row);
list.Add(pt);
}
return(list);
}
public static List<UserBackSet> GetBackSet(string userid,int type,int pageSize, int pageIndex, ref int totalCount, ref int pageCount)
{
//string tablename = " UserBackSet a join BackModel b on a.Autoid=b.Backid ";
string tablename = " UserBackSet a ";
string sql = " 1=1 ";
if (!string.IsNullOrEmpty(userid))
{
sql += " and a.UserID='" + userid + "'";
}
if (type>-1)
{
sql += " and a.Type="+type;
}
// DataTable dt = CommonBusiness.GetPagerData(tablename, "a.* ,b.WinFee,b.SSCType,b.X5Type,b.FCType ", sql, " a.AutoID ", pageSize, pageIndex, out totalCount, out pageCount, true);
DataTable dt = CommonBusiness.GetPagerData(tablename, "a.* ", sql, " a.AutoID ", pageSize, pageIndex, out totalCount, out pageCount, true);
List<UserBackSet> list = new List<UserBackSet>();
foreach (DataRow dr in dt.Rows)
{
UserBackSet model = new UserBackSet();
model.FillData(dr);
BackModel bmodel = GetBackById(model.BackID);
model.Bmodel = bmodel;
list.Add(model);
}
return list;
}
public static List <M_Users> GetUsers(int pageSize, int pageIndex, ref int totalCount, ref int pageCount, int type = -1, int status = -1, string keyWords = "", string colmonasc = "", bool isasc = false)
{
string whereSql = " a.Status<>9";
if (type > -1)
{
whereSql += " and a.type=" + type + " ";
}
if (status > -1)
{
whereSql += " and a.Status=" + status;
}
if (!string.IsNullOrEmpty(keyWords))
{
whereSql += " and (a.UserName like '%" + keyWords + "%' or a.LoginName like'%" + keyWords + "%') ";
}
string cstr = @" a.* ";
DataTable dt = CommonBusiness.GetPagerData("M_Users a ", cstr, whereSql, "a.AutoID", colmonasc, pageSize, pageIndex, out totalCount, out pageCount, isasc);
List <M_Users> list = new List <M_Users>();
M_Users model;
foreach (DataRow item in dt.Rows)
{
model = new M_Users();
model.FillData(item);
if (!string.IsNullOrEmpty(model.RoleID))
{
model.Role = ManageSystemBusiness.GetRoleByIDCache(model.RoleID);
}
list.Add(model);
}
return(list);
}
public static List <LotteryResult> GetPagList(string cpCode, int status, bool orderby, 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 (!string.IsNullOrEmpty(etime))
{
sqlwhere += " and b.CreateTime<='" + etime + " 23:59:59:999'";
}
DataTable dt = CommonBusiness.GetPagerData(" LotteryResult (nolock) b ",
"b.*", sqlwhere, "b.AutoID ", pageSize, pageIndex,
out totalCount, out pageCount, orderby);
List <LotteryResult> list = new List <LotteryResult>();
foreach (DataRow dr in dt.Rows)
{
LotteryResult model = new LotteryResult();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <UserNeeds> FindNeedsRated(int type, string userId, int pageSize, int pageIndex, ref int totalCount, ref int pageCount)
{
string sqlwhere = " a.Status not in (6,9)";
if (!string.IsNullOrEmpty(userId))
{
sqlwhere += " and a.UserID='" + userId + "'";
}
if (type > -1)
{
sqlwhere += " and a.Type in(" + type + ")";
}
DataTable dt = CommonBusiness.GetPagerData(" UserNeeds a left join m_users b on a.UserID=b.UserID left join m_users c on a.InviteID=c.UserID ",
"a.AutoID,a.NeedDate,a.NeedCity,a.Type,a.UserID,a.UserName,a.Status,a.Title,a.LetDays,a.InviteName,a.NeedSex,a.NeedType,a.CreateTime,b.province,b.Avatar as UserAvatar ,c.Avatar as InviteAvatar", sqlwhere, "a.AutoID ", pageSize, pageIndex,
out totalCount, out pageCount);
List <UserNeeds> list = new List <UserNeeds>();
foreach (DataRow dr in dt.Rows)
{
UserNeeds model = new UserNeeds();
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 <M_Users> GetUsers(int sex, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string address = "", string age = "", int sourcetype = 0, int status = -1, string keyWords = "")
{
string whereSql = " a.Status<>9";
if (sex > -1)
{
whereSql += " and a.Sex=" + sex;
}
if (status > -1)
{
whereSql += " and a.Status=" + status;
}
if (!string.IsNullOrEmpty(keyWords))
{
whereSql += " and (a.Name like '%" + keyWords + "%' or a.LoginName like'%" + keyWords + "%') ";
}
if (sourcetype > -1)
{
whereSql += " and a.sourcetype=" + sourcetype;
}
if (!string.IsNullOrEmpty(address))
{
string[] strArr = address.Split(',');
for (int i = 0; i < strArr.Length; i++)
{
whereSql += (i == 0
? " and a. province='"
: i == 1 ? " and a.City='" : i == 2 ? " and a.District='" : "") + strArr[i] + "'";
}
}
if (!string.IsNullOrEmpty(age))
{
string[] strArr = age.Split('~');
for (int i = 0; i < strArr.Length; i++)
{
whereSql += (i == 0? " and a.Age>=": " and a.Age<=") + strArr[i];
}
}
string cstr = @"a.AuthorBTime,a.AuthorETime,a.AuthorType,a.userID,a.Avatar,a.Name,a.LoginName,a.Age,a.MyService,a.Province,a.City,a.District,a.CreateTime,a.Status,a.Sex,a.IsMarry,a.Education,a.ROleID,
a.BHeight,a.Levelid,a.BWeight,a.MyContent,a.MyCharacter,a.BPay,a.Account,a.TalkTo,a.Description";
DataTable dt = CommonBusiness.GetPagerData("M_Users a", cstr, whereSql, "a.AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <M_Users> list = new List <M_Users>();
M_Users model;
foreach (DataRow item in dt.Rows)
{
model = new M_Users();
model.FillData(item);
if (!string.IsNullOrEmpty(model.RoleID))
{
model.Role = ManageSystemBusiness.GetRoleByIDCache(model.RoleID);
}
list.Add(model);
}
return(list);
}
public static List <UserNeeds> FindNeedsList(string keyWords, string type, string userId, string status, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string begintime = "", string endtime = "", int needSex = -1, string age = "", string address = "")
{
string sqlwhere = " a.Status <>9";
if (!string.IsNullOrEmpty(userId))
{
sqlwhere += " and a.UserID='" + userId + "'";
}
if (type != "-1" && !string.IsNullOrEmpty(type))
{
sqlwhere += " and a.Type in (" + type + ")";
}
if (!string.IsNullOrEmpty(keyWords))
{
sqlwhere += " and a.Content like '%" + keyWords + "%' ";
}
if (needSex > -1)
{
sqlwhere += " and a.needSex=" + needSex;
}
if (!string.IsNullOrEmpty(status))
{
sqlwhere += " and a.status in(" + status + ") ";
}
if (!string.IsNullOrEmpty(begintime))
{
sqlwhere += " and a.CreateTime>='" + begintime + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endtime))
{
sqlwhere += " and a.CreateTime<'" + endtime + " 23:59:59:999'";
}
if (!string.IsNullOrEmpty(address))
{
string[] strArr = address.Split(',');
for (int i = 0; i < strArr.Length; i++)
{
sqlwhere += (i == 0
? " and b.province='"
: i == 1 ? " and b.City='" : i == 2 ? " and b.District='" : "") + strArr[i] + "'";
}
}
DataTable dt = CommonBusiness.GetPagerData(" UserNeeds a left join m_users b on a.UserID=b.UserID ",
"a.AutoID,a.NeedDate,a.NeedCity,a.UserID,a.Content,a.Status,a.Type,a.UserName,a.Title,a.LetDays,a.InviteName,a.NeedSex,a.NeedType,a.CreateTime,b.province,b.Avatar as UserAvatar", sqlwhere, "a.AutoID ", pageSize, pageIndex,
out totalCount, out pageCount);
List <UserNeeds> list = new List <UserNeeds>();
foreach (DataRow dr in dt.Rows)
{
UserNeeds model = new UserNeeds();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <M_Users> GetUsers(int pageSize, int pageIndex, ref int totalCount, ref int pageCount, int type = -1, int status = -1, int sourcetype = -1, string keyWords = "", string colmonasc = "", bool isasc = false,
string rebatemin = "", string rebatemax = "", string accountmin = "", string accountmax = "")
{
string whereSql = " a.Status<>9 and isadmin=0 ";
if (!string.IsNullOrEmpty(rebatemax))
{
whereSql += " and a.Rebate<='" + rebatemax + "' ";
}
if (!string.IsNullOrEmpty(rebatemin))
{
whereSql += " and a.Rebate>'" + rebatemin + "' ";
}
if (sourcetype > -1)
{
whereSql += " and a.sourcetype=" + sourcetype + " ";
}
if (type > -1)
{
whereSql += " and a.type=" + type + " ";
}
if (!string.IsNullOrEmpty(accountmax))
{
whereSql += " and b.AccountFee<='" + accountmax + "' ";
}
if (!string.IsNullOrEmpty(accountmin))
{
whereSql += " and b.AccountFee>'" + accountmin + "' ";
}
if (status > -1)
{
whereSql += " and a.Status=" + status;
}
if (!string.IsNullOrEmpty(keyWords))
{
whereSql += " and (a.UserName like '%" + keyWords + "%' or a.LoginName like'%" + keyWords + "%') ";
}
string cstr = @" a.*,b.AccountFee ";
DataTable dt = CommonBusiness.GetPagerData("M_Users a left join UserAccount b on a.UserID=b.UserID ", cstr, whereSql, "a.AutoID", colmonasc, pageSize, pageIndex, out totalCount, out pageCount, isasc);
List <M_Users> list = new List <M_Users>();
M_Users model;
foreach (DataRow item in dt.Rows)
{
model = new M_Users();
model.FillData(item);
if (!string.IsNullOrEmpty(model.RoleID))
{
model.Role = ManageSystemBusiness.GetRoleByIDCache(model.RoleID);
}
list.Add(model);
}
return(list);
}
public static List <FeedBack> GetFeedBacks(string keywords, string beginDate, string endDate, int type, int status, string userID, int pageSize, int pageIndex, out int totalCount, out int pageCount)
{
string sqlWhere = "1=1";
if (type != -1)
{
sqlWhere += " and type=" + type;
}
if (status != -1)
{
sqlWhere += " and status=" + status;
}
if (!string.IsNullOrEmpty(keywords))
{
sqlWhere += " and Reamrk like '%" + keywords + "%' ";
}
if (status > -1)
{
sqlWhere += " and status=" + status;
}
if (!string.IsNullOrEmpty(userID))
{
sqlWhere += " and CreateUserID='" + userID + "'";
}
if (!string.IsNullOrEmpty(beginDate))
{
sqlWhere += " and createtime>='" + beginDate + "'";
}
if (!string.IsNullOrEmpty(endDate))
{
sqlWhere += " and createtime<='" + DateTime.Parse(endDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
}
DataTable dt = CommonBusiness.GetPagerData("FeedBack", "*", sqlWhere, "AutoID", pageSize, pageIndex, out totalCount, out pageCount);
List <FeedBack> list = new List <FeedBack>();
FeedBack model;
foreach (DataRow item in dt.Rows)
{
model = new FeedBack();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List<BackModel> GetBackModel(int pageSize, int pageIndex, ref int totalCount, ref int pageCount,string sqlwhere="",bool asc=false)
{
string tablename = " BackModel a ";
string sql = " 1=1 ";
DataTable dt = CommonBusiness.GetPagerData(tablename, " a.* ", sql + sqlwhere, " a.WinFee ", pageSize, pageIndex, out totalCount, out pageCount, asc);
List<BackModel> list = new List<BackModel>();
foreach (DataRow dr in dt.Rows)
{
BackModel model = new BackModel();
model.FillData(dr);
list.Add(model);
}
return list;
}
public static List <UsersFocus> GetPagList(string userid, int pageSize, int pageIndex, ref int totalCount, ref int pageCount)
{
string sqlwhere = " b.UserID='" + userid + "'";
DataTable dt = CommonBusiness.GetPagerData(" UsersFocus b left join M_Users a on a.userid=b.focusid",
"b.*,a.LoginName as FocusName ,a.Avatar as FocusAvatar", sqlwhere, "b.AutoID ", pageSize, pageIndex,
out totalCount, out pageCount);
List <UsersFocus> list = new List <UsersFocus>();
foreach (DataRow dr in dt.Rows)
{
UsersFocus model = new UsersFocus();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <UserOrders> GetUserOrders(string keyWords, string userid, int type, int status, int payway, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string begintime = "", string endtime = "")
{
string tablename = "UserOrders a left join M_Users b on a.UserID =b.UserID ";
string sqlwhere = " a.status<>9 ";
if (!string.IsNullOrEmpty(keyWords))
{
sqlwhere += " and (b.UserName like '%" + keyWords + "%' or a.BankName like '%" + keyWords + "%' or a.OrderCode like '%" + keyWords + "%' or a.Sku like '%" + keyWords + "%' or a.OtherCode like '%" + keyWords + "%')";
}
if (type > -1)
{
sqlwhere += " and a.Type=" + type;
}
if (status > -1)
{
sqlwhere += " and a.status=" + status;
}
if (payway > -1)
{
sqlwhere += " and a.PayType=" + payway;
}
if (!string.IsNullOrEmpty(userid))
{
sqlwhere += " and a.UserID='" + userid + "' ";
}
if (!string.IsNullOrEmpty(begintime))
{
sqlwhere += " and a.CreateTime>='" + begintime + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endtime))
{
sqlwhere += " and a.CreateTime<'" + endtime + " 23:59:59:999'";
}
DataTable dt = CommonBusiness.GetPagerData(tablename, "a.*,b.UserName ", sqlwhere, "a.AutoID ", pageSize, pageIndex, out totalCount, out pageCount);
List <UserOrders> list = new List <UserOrders>();
foreach (DataRow dr in dt.Rows)
{
UserOrders model = new UserOrders();
model.FillData(dr);
list.Add(model);
}
return(list);
}
public static List <M_Users> GetUsersReCommen(int sex, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string address = "", string age = "", string cdesc = "")
{
string whereSql = " a.Status<>9";
if (sex > -1)
{
whereSql += " and a.Sex=" + sex;
}
if (!string.IsNullOrEmpty(address))
{
string[] strArr = address.Split(',');
for (int i = 0; i < strArr.Length; i++)
{
whereSql += (i == 0
? " and a.province='"
: i == 1 ? " and a.City='" : i == 2 ? " and a.District='" : "") + strArr[i] + "'";
}
}
if (!string.IsNullOrEmpty(age))
{
string[] strArr = age.Split('~');
for (int i = 0; i < strArr.Length; i++)
{
whereSql += (i == 0 ? " and a.Age>=" : " and a.Age<=") + strArr[i];
}
}
string clumstr = "a.AuthorBTime,a.AuthorETime,a.AuthorType,a.userID,a.Avatar,a.Name,a.Age,a.LoginName,a.MyService,a.Province,a.City,a.District,a.CreateTime,a.Status,a.Sex,a.IsMarry,a.Education," +
"a.BHeight,a.Levelid,a.BWeight,a.MyContent,a.MyCharacter,a.TalkTo,a.BPay,a.Account,b.ImgCount,b.IsLogin,b.RecommendCount,b.SeeCount";
DataTable dt = CommonBusiness.GetPagerData("M_Users a left join userReport b on a.Userid=b.Userid ", clumstr, whereSql, "a.AutoID" + (string.IsNullOrEmpty(cdesc) ? "" : "," + cdesc), pageSize, pageIndex, out totalCount, out pageCount);
List <M_Users> list = new List <M_Users>();
M_Users model;
foreach (DataRow item in dt.Rows)
{
model = new M_Users();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <UserAccount> GetTeamAccount(string userid, int pageIndex, int pageSize, ref int totalCount, ref int pageCount)
{
string whereSql = " b.status<>9 ";
if (!string.IsNullOrEmpty(userid))
{
whereSql += " and ( b.userid='" + userid + "' or a.Userid in(select userid from dbo.UserRelation where ParentID ='" + userid + "' ) ) ";
}
string clumstr = " a.*,b.LoginName UserName ";
DataTable dt = CommonBusiness.GetPagerData(" UserAccount a join M_Users b on a.Userid=b.Userid ", clumstr, whereSql, "b.autoid ", pageSize, pageIndex, out totalCount, out pageCount);
List <UserAccount> list = new List <UserAccount>();
foreach (DataRow item in dt.Rows)
{
UserAccount model = new UserAccount();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <LotteryBettAuto> GetBettAutoRecord(string keyWords, string cpcode, string userid, string bCode, 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 = "LotteryBettAuto a left join M_Users b on a.UserID =b.UserID ";
string sqlwhere = " a.status<>9 ";
if (!string.IsNullOrEmpty(keyWords))
{
sqlwhere += " and (b.LoginName like '%" + keyWords + "%' or a.StartNum like '%" + keyWords + "%' or a.BCode 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 (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(bCode))
{
sqlwhere += " and a.BCode ='" + bCode + "'";
}
if (!string.IsNullOrEmpty(issuenum))
{
sqlwhere += " and a.StartNum ='" + issuenum + "'";
}
DataTable dt = CommonBusiness.GetPagerData(tablename, "a.*,b.LoginName as UserName ", sqlwhere, "a.AutoID ", pageSize, pageIndex, out totalCount, out pageCount);
List <LotteryBettAuto> list = new List <LotteryBettAuto>();
foreach (DataRow dr in dt.Rows)
{
LotteryBettAuto model = new LotteryBettAuto();
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 <M_Users> GetUsersRelationList(int pageSize, int pageIndex, ref int totalCount, ref int pageCount, string userid, int type = -1, int status = -1, string keyWords = "", string colmonasc = " a.AutoID ", bool isasc = false,
string accountmin = "", string accountmax = "", bool myselft = false, int sourcetype = -1, int isline = -1)
{
string whereSql = " a.layers>0 and a.Status<>9 ";
if (type > -1)
{
whereSql += " and a.type=" + type + " ";
}
if (isline > -1)
{
whereSql += " and a.isline=" + isline + " ";
}
if (!string.IsNullOrEmpty(accountmax))
{
whereSql += " and b.AccountFee<='" + accountmax + "' ";
}
if (!string.IsNullOrEmpty(accountmin))
{
whereSql += " and b.AccountFee>'" + accountmin + "' ";
}
if (sourcetype > -1)
{
whereSql += " and a.sourcetype=" + sourcetype + " ";
}
if (status > -1)
{
whereSql += " and a.Status=" + status;
}
if (!string.IsNullOrEmpty(keyWords))
{
whereSql += " and (a.UserName like '%" + keyWords + "%' or a.LoginName like'%" + keyWords + "%') ";
}
string orswhere = "";
if (!string.IsNullOrEmpty(userid))
{
orswhere = " and ( c.ParentID='" + userid + "' " + (myselft ? " or a.userid='" + userid + "'" : "") + " ) ";;
}
if (string.IsNullOrEmpty(colmonasc))
{
colmonasc = " a.AutoID ";
}
//string clumstr = @" select a.*,b.AccountFee,f.* from M_Users a join UserAccount b on a.UserID=b.Userid left join UserRelation c on a.UserID=c.UserID " + orswhere + " left join (select d.userid as uid,d.BackID,d.SSCNum,d.SSCUseNum,d.FCNum,d.FCUseNum,d.X5Num,d.X5UseNum,e.SSCType,e.FCType,e.X5Type,e.WinFee " +
// "from UserBackSet d join BackModel e on d.Backid=e.AutoID where d.type=0) f on f.uid=a.userid " + whereSql;
DataTable dt = CommonBusiness.GetPagerData(" M_Users a join UserAccount b on a.UserID=b.Userid left join UserRelation c on a.UserID=c.UserID left join (select d.userid as uid,d.BackID,d.SSCNum,d.SSCUseNum,d.FCNum,d.FCUseNum,d.X5Num,d.X5UseNum,e.SSCType,e.FCType,e.X5Type,e.WinFee " +
"from UserBackSet d join BackModel e on d.Backid=e.AutoID where d.type=0) f on f.uid=a.userid ", " a.*,b.AccountFee,f.* ", whereSql + orswhere, colmonasc, pageSize, pageIndex, out totalCount, out pageCount, isasc);
//DataTable dt = M_UsersDAL.GetDataTable(clumstr);
List <M_Users> list = new List <M_Users>();
M_Users model;
foreach (DataRow item in dt.Rows)
{
model = new M_Users();
model.FillData(item);
list.Add(model);
}
return(list);
}
