/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows)
{
string strSQL = "select TOP (100) PERCENT * from TSRole order by roleId asc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows, string cardId, string status, string supplierCode, string supplierBatch, string agentId, string siteId)
{
string strSQL = "select s.sitename,a.agentname,c.* from TBCard c left join tbsite s on c.siteid=s.siteid left join tbagent a on c.agentid=a.agentid where 1=1 ";
if (!string.IsNullOrEmpty(cardId))
{
strSQL += " and c.cardId='" + cardId + "'";
}
if (!string.IsNullOrEmpty(status))
{
strSQL += " and c.status='" + status + "'";
}
if (!string.IsNullOrEmpty(supplierCode))
{
strSQL += " and c.supplierCode='" + supplierCode + "'";
}
if (!string.IsNullOrEmpty(supplierBatch))
{
strSQL += " and c.supplierBatch='" + supplierBatch + "'";
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and c.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSQL += " and c.siteId='" + siteId + "'";
}
strSQL += " order by c.cardId";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载中奖数据报表数据
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
public void WinPrizeDataLoadGrid(int page, int rows, string startDate, string endDate, string city, string county, string siteId, string terminalId, string lotteryType, string gameId)
{
string strSQL = WinPrizeDataReportSql(startDate, endDate, city, county, siteId, terminalId, lotteryType, gameId);
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void WLoadGrid(int page, int rows, string agentId, string siteId, string period, string awardResult, string clientQuery, string clientQueryText, string startDate, string endDate)
{
string strSQL = @"select t.orderid, t.agentid, t.siteid, t.terminalid, t.clientid, t.clientname, t.cardid,g.gamename gameid,
l.lotterytypename lotterytype,t.period, t.userBetCodes,t.centerBetCodes, t.multiple, t.betfee, t.bettime, t.ischase,
t.chaseorderid, t.orderstatus, t.ordertime, t.awardresult, t.awardmoney, t.awardtime, t.settlestatus,
t.settletime, t.createtime
from ttlotteryorder t,tbgame g,tblottery l
where t.gameid=g.gameid
and t.lotterytype=l.lotterytype
and t.orderStatus=" + (int)OrderStatus.Ticket + "" +
" and t.awardResult in (" + ((int)AwardResult.SmallAward).ToString() + "," + ((int)AwardResult.BigAward).ToString() + ")";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and t.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSQL += " and t.siteId='" + siteId + "'";
}
if (!string.IsNullOrEmpty(period))
{
strSQL += " and t.period='" + period + "'";
}
if (!string.IsNullOrEmpty(awardResult))
{
strSQL += " and t.awardResult='" + awardResult + "'";
}
if (!string.IsNullOrEmpty(clientQueryText) && !string.IsNullOrEmpty(clientQuery))
{
if (clientQuery == ((int)ClientQuery.CardId).ToString())
{
strSQL += " and t.cardId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientId).ToString())
{
strSQL += " and t.clientId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientName).ToString())
{
strSQL += " and t.clientName='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClinetPhone).ToString())
{
strSQL += " and bc.telephone='" + clientQueryText + "'";
}
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and t.awardTime>= '" + startDate + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and t.awardTime<='" + endDate + " 23:59:59'";
}
strSQL += " order by t.betTime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载客户提现报表数据
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
public void ClientTakeCashLoadGrid(int page, int rows, string startDate, string endDate, string agentId, string clientId)
{
string strSQL = ClientTakeCashReportSql(startDate, endDate, agentId, clientId);
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 推荐人佣金
/// </summary>
/// <param name="startDate">起始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="clientQuery">0 客户编号 1 客户卡号</param>
/// <param name="clientId">条件</param>
public void RefererCommiss(string startDate, string endDate, string clientId)
{
string str = "";
if (string.IsNullOrEmpty(clientId))
{
Message.success(context, "客户卡号或客户编号不能为空!");
}
else
{
string strSQL = @" select tc.clientid, tc.cardId,tc.clientName,b.salefee,b.commissfee,b.startdate,b.endDate,tc.refererrebate,substr(tc.referervaliddate,1,10) referervaliddate " +
" from (select l.clientid, sum(l.betfee) salefee, sum(l.betfee*(c.refererrebate/100)) commissfee,'" + startDate + "' startDate,'" + endDate + "' endDate from TTLOTTERYORDER l " +
" inner join (select t.clientid,t.refererrebate,t.referervaliddate from TBClient t where t.refererclientid='" + clientId + "') c " +
" on l.clientid=c.clientid " +
" where l.bettime>='" + startDate + " 00:00:00' " +
" and l.bettime<='" + endDate + " 23:59:59' " +
" and l.orderstatus='" + (int)OrderStatus.Ticket + "'" +
" and l.bettime<=c.referervaliddate " +
" group by l.clientid) b " +
" left join TBClient tc on b.clientId=tc.clientId " +
" order by tc.createtime desc";
str = commonDao.DataGrid(strSQL, null);
WebJson.Write(context, str);
}
}
/// <summary>
/// 加载DataGrid
/// </summary>
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
public void LoadGrid(int page, int rows, string period, string gameId, string startDate, string endDate)
{
string strSQL = @"select * from (select t.flowid,t.gameid,g.gamename,t.period,t.starttime,t.endtime,t.publishtime,t.numbers
from TBPNUMNOTICE t
left join TBGame g on t.gameid=g.gameid ) tt where 1=1 ";
if (!string.IsNullOrEmpty(period))
{
strSQL += " and tt.period='" + period + "'";
}
if (!string.IsNullOrEmpty(gameId))
{
strSQL += " and tt.gameId='" + gameId + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and tt.publishTime>= '" + startDate + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and tt.publishTime<= '" + endDate + " 23:59:59'";
}
strSQL += " order by tt.period desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows)
{
string strSQL = "select * from TBGame";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows, string agentId, string agentName, string startDate, string endDate)
{
string strSQL = "select a.*,p.modename from TTAgentTakeCash a,TBPayMode p where a.handlemode=p.modeid ";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and a.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(agentName))
{
strSQL += " and a.agentName like '%" + agentName + "%'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and a.createTime >= To_date('" + startDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and a.createTime <= To_date('" + endDate + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
}
strSQL += " order by agentId,createTime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGridOnLine(int page, int rows, string agentId, string siteId, string terminalId, string onlineStatus, int offlineMinutes, string startDate, string endDate)
{
string strSQL = @"select * from (select s.sitename,a.agentname,case when ceil(((sysdate - checktime)) * 24 * 60)>" + offlineMinutes + @" then 1 else 0 end as onlinestatus,c.* from TBTerminal c
left join tbsite s on c.siteid=s.siteid
left join tbagent a on c.agentid=a.agentid) t where 1=1";
if (!string.IsNullOrEmpty(terminalId))
{
strSQL += " and t.terminalId='" + terminalId + "'";
}
if (!string.IsNullOrEmpty(onlineStatus))
{
strSQL += " and t.onlineStatus='" + onlineStatus + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and t.checkTime>=to_date('" + startDate + " 00:00:00','yyyy-MM-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and t.checkTime<=to_date('" + endDate + " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and t.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSQL += " and t.siteId='" + siteId + "'";
}
strSQL += " order by t.terminalId desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows, string agentId, string agentName, string payStatus, string startDate, string endDate)
{
string strSQL = @"select flowid, agentid, agentname, startdate, enddate, salefee, rebate, commissionfee, paystatus,
modeid, settlementtime, settlementid, settlementname, cashierid, cashiername,
case when payStatus='0' then '' else to_char(paytime,'yyyy-mm-dd hh24:mi:ss') end paytime
from TTAGENTSETTLEMENT t where 1=1";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(agentName))
{
strSQL += " and agentName like '%" + agentName + "%'";
}
if (!string.IsNullOrEmpty(payStatus))
{
strSQL += " and payStatus='" + payStatus + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and settlementTime>=to_date('" + startDate + " 00:00:00','yyyy-MM-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and settlementTime<=to_date('" + endDate + " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
}
strSQL += " order by settlementTime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// </summary>
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
public void LoadGrid(int page, int rows, string agentId, string auditStatus, string startDate, string endDate)
{
string strSQL = "select * from TTAgentPreRechg where 1=1 ";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(auditStatus))
{
strSQL += " and auditStatus='" + auditStatus + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and createtime>=to_date('" + startDate + " 00:00:00','yyyy-MM-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and createtime<=to_date('" + endDate + " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
}
strSQL += " order by auditStatus asc, createtime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// <param name="agentName">代理商名称</param>
/// <param name="agentId">代理商编号</param>
/// <param name="bankCardId">银行卡号</param>
/// <param name="auditStatus">审核状态</param>
/// </summary>
public void LoadGrid(int page, int rows, string agentName, string agentId, string bankCardId, string auditStatus)
{
string strSQL = "select a.*,b.areaName from TBAgent a,TBArea b where a.areaId=b.areaId ";
if (!string.IsNullOrEmpty(agentName))
{
strSQL += " and a.agentName like '%" + agentName + "%'";
}
if (!string.IsNullOrEmpty(auditStatus))
{
strSQL += " and a.auditStatus in(" + auditStatus + ")";
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and a.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(bankCardId))
{
strSQL += " and a.bankCardId='" + bankCardId + "'";
}
strSQL += " order by a.agentId ";
Param param = new Param();
param.Clear();
string str = commonDao.DataGrid(strSQL, param, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows, string uKeyId, string status, string siteName, string agentName, string agentId)
{
string strSQL = "select s.sitename,a.agentname,c.* from TBUKey c left join tbsite s on c.siteid=s.siteid left join tbagent a on c.agentid=a.agentid where 1=1";
if (!string.IsNullOrEmpty(uKeyId))
{
strSQL += " and c.uKeyId='" + uKeyId + "'";
}
if (!string.IsNullOrEmpty(status))
{
strSQL += " and c.status='" + status + "'";
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and c.agentId ='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteName))
{
strSQL += " and s.siteName like '%" + siteName + "%'";
}
if (!string.IsNullOrEmpty(agentName))
{
strSQL += " and a.agentName like '%" + agentName + "%'";
}
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
public void TakeCash(string flowid, string withdrawperson)
{
Param Param = new Param();
Param.Add(":flowid", flowid);
string sqlStr = string.Format("update TTClientAccDetail set withDrawState=1,withdrawtime=sysdate,withdrawperson='{0}' where flowid=@flowid", withdrawperson);
WebJson.Write(context, ttClientTakeCashDAO.TakeCash(sqlStr, Param).ToString());
}
/// <summary>
/// 获取代理商终端维护状态
/// </summary>
public void AgentTerminalStatusCombobox()
{
DataTable dt = EnumSelectListHelper.CreatSelectListByEnum(typeof(TerminalStatus));
DataView dv = new DataView(dt);
dv.RowFilter = "value not in (" + ((int)TerminalStatus.InStore).ToString() + ")";
DataTable dt_New = dv.ToTable();
WebJson.Write(context, commonDao.Combobox(dt_New));
}
/// <summary>
/// 获取代理商中奖状态下拉列表
/// </summary>
public void WinnerLotteryCombobox()
{
DataTable dt = EnumSelectListHelper.CreatSelectListByEnum(typeof(AwardResult));
DataView dv = new DataView(dt);
dv.RowFilter = "value in (" + ((int)AwardResult.SmallAward).ToString() + "," + ((int)AwardResult.BigAward).ToString() + ")";
DataTable dt_New = dv.ToTable();
WebJson.Write(context, commonDao.Combobox(dt_New));
}
/// <summary>
/// 获取快开卡片状态下拉列表
/// </summary>
public void AgentCardStatusCombobox()
{
DataTable dt = EnumSelectListHelper.CreatSelectListByEnum(typeof(CardStatus));
DataView dv = new DataView(dt);
dv.RowFilter = "value not in (" + (int)CardStatus.InStore + "," + (int)CardStatus.StayUsed + ")";
DataTable dt_New = dv.ToTable();
WebJson.Write(context, commonDao.Combobox(dt_New));
}
/// <summary>
/// 获取区县下拉列表
/// </summary>
/// <param name="city">市</param>
public void CountyCombobox(string city)
{
if (!string.IsNullOrEmpty(city))
{
WebJson.Write(context, commonDao.Combobox("select t.areaname name,t.areaid id from tbarea t where t.parentid='" + city + "'", null));
}
else
{
WebJson.Write(context, commonDao.Combobox("select t.areaname name,t.areaid id from tbarea t where t.parentid!=0", null));
}
}
public void LoadEnforcementGrid(string caseId)
{
Param param = new Param();
string strSQL = " select distinct tbet.statu as typeStatu, ce.*,et.TemplateCode,et.TemplateName,et.Height,et.Width from TBCase_EnforementName ce join TBCase_EnforcementType tbet on tbet.EnforcementTypeId=ce.EnforcementTypeId left join TBEnforcementTemplate et on ce.EnforcementTemplateId=et.TemplateId where ce.CaseId=@caseId and tbet.CaseId=@caseId and ce.EnforcementTypeId=(select CaseCurrentTypeId from TBCase where CaseId=@caseId)";
param.Add("@caseId", caseId);
string str = commonDao.DataGridTemp(strSQL, param);
param.Clear();
WebJson.Write(context, str);
}
/// <summary>
/// 根据条件获取门店下拉列表
/// </summary>
/// <param name="userId">关联用户编号</param>
/// <param name="roleId">角色编号</param>
public void SiteByRoleCombobox(string userId, string roleType)
{
if (roleType == "0")
{
WebJson.Write(context, commonDao.Combobox("select SiteName name,SiteId id from TBSite where status='1' and agentId='" + userId + "'", null));
}
else
{
string siteId = new TBStaffDAO().Get(userId).siteId;
WebJson.Write(context, commonDao.Combobox("select SiteName name,SiteId id from TBSite where status='1' and siteId='" + siteId + "'", null));
}
}
/// <summary>
///
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="agentId"></param>
/// <param name="siteId"></param>
/// <param name="clientQuery"></param>
/// <param name="clientQueryText"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
public void LoadGridCopy(int page, int rows, string agentId, string siteId, string clientQuery, string clientQueryText, string startDate, string endDate, string sroState)
{
string strSQL = @"select c.*,a.agentName,s.siteName from TTClientPoint c,TBClient bc,TBAgent a,TBSite s
where c.agentId=a.agentId and c.siteId=s.siteId and c.clientId=bc.clientId";
if (!string.IsNullOrEmpty(clientQueryText) && !string.IsNullOrEmpty(clientQuery))
{
if (clientQuery == ((int)ClientQuery.CardId).ToString())
{
strSQL += " and c.cardId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientId).ToString())
{
strSQL += " and c.clientId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientName).ToString())
{
strSQL += " and c.clientName='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClinetPhone).ToString())
{
strSQL += " and bc.telephone='" + clientQueryText + "'";
}
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and c.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSQL += " and c.siteId='" + siteId + "'";
}
if (!string.IsNullOrEmpty(sroState))
{
strSQL += " and c.gameId='" + sroState + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and c.changeTime>= '" + startDate + " 00:00:00'";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and c.changeTime<= '" + endDate + " 23:59:59'";
}
strSQL += " order by c.agentId,c.siteId,c.clientId desc,c.createTime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载佣金DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void CommissionLoadGrid(int page, int rows, string startDate, string endDate)
{
string strSQL = @"select t.agentId,a.agentname ,'" + startDate + "' startDate,'" + endDate + "' endDate,t.salefee,a.rebate,a.rebate/100*t.salefee commissionFee" +
" from (select sum(l.betfee) salefee,l.agentid from TTLotteryOrder l" +
" where l.bettime>='" + startDate + " 00:00:00' " +
" and l.bettime<='" + endDate + " 23:59:59' " +
" and l.orderstatus='" + (int)OrderStatus.Ticket + "' " +
" and l.settleStatus='0'" +
" group by agentId) t " +
" left join TBAgent a" +
" on t.agentId=a.agentId ";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 根据市区县获取快开厅
/// </summary>
/// <param name="city"></param>
/// <param name="county"></param>
public void SiteByAreaCombobox(string city, string county)
{
if (!string.IsNullOrEmpty(county))
{
WebJson.Write(context, commonDao.Combobox("SELECT s.siteName name,s.siteId id FROM tbSite s,TBAgent a where s.agentid=a.agentid and a.areaid='" + county + "'", null));
}
else if (!string.IsNullOrEmpty(city) && string.IsNullOrEmpty(county))
{
WebJson.Write(context, commonDao.Combobox("SELECT s.siteName name,s.siteId id FROM tbSite s,TBAgent a where s.agentid=a.agentid and a.areaid in (select areaid from TBAREA t where t.parentid='" + city + "')", null));
}
else
{
WebJson.Write(context, commonDao.Combobox("SELECT s.siteName name,s.siteId id FROM tbSite s,TBAgent a where s.agentid=a.agentid ", null));
}
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(int page, int rows, string agentId, string siteId, string clientQuery, string clientQueryText, string startDate, string endDate)
{
string strSQL = @"select c.*,a.agentName,s.siteName,p.modename
from TTClientRecharge c,TBAgent a,TBSite s ,TBPayMode p,TBClient bc
where c.agentId=a.agentId and c.siteId=s.siteId and c.handlemode=p.modeid
and c.clientId=bc.clientId ";
if (!string.IsNullOrEmpty(clientQueryText) && !string.IsNullOrEmpty(clientQuery))
{
if (clientQuery == ((int)ClientQuery.CardId).ToString())
{
strSQL += " and c.cardId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientId).ToString())
{
strSQL += " and c.clientId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientName).ToString())
{
strSQL += " and c.clientName='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClinetPhone).ToString())
{
strSQL += " and bc.telephone='" + clientQueryText + "'";
}
}
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and c.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSQL += " and c.siteId='" + siteId + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSQL += " and c.createTime>= To_date('" + startDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSQL += " and c.createTime<= To_date('" + endDate + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
}
strSQL += " order by c.agentId,c.siteId,c.clientId desc,c.createTime desc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载GRID
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="agentId"></param>
/// <param name="siteId"></param>
/// <param name="clientQuery"></param>
/// <param name="clientQueryText"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
public void LoadGrid(int page, int rows, string agentId, string siteId, string clientQuery, string clientQueryText, string startDate, string endDate)
{
//客户编号，客户名称，代理商编号，代理商名称，卡号，银行卡号，提现用户名 ， 提现金额， 提现时间，发生时间，备注
string strSql = @"select t.flowId, t.ClientId,c.clientname,AgentName,t.CardId,t.CardNo as cardno,t.UserName as username,t.telphoneno as telphoneno,t.Fee,withDrawTime,t.createtime,t.withDrawState,t.remark from TTClientAccDetail t inner join tbagent a on t.agentid=a.agentid
inner join tbclient c on t.clientid=c.clientid where t.SRCMODE in (7,8,9,10)";
if (!string.IsNullOrEmpty(clientQueryText) && !string.IsNullOrEmpty(clientQuery))
{
if (clientQuery == ((int)ClientQuery.CardId).ToString())
{
strSql += " and c.cardId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientId).ToString())
{
strSql += " and c.clientId='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClientName).ToString())
{
strSql += " and c.clientName='" + clientQueryText + "'";
}
if (clientQuery == ((int)ClientQuery.ClinetPhone).ToString())
{
strSql += " and c.telephone='" + clientQueryText + "'";
}
}
if (!string.IsNullOrEmpty(agentId))
{
strSql += " and c.agentId='" + agentId + "'";
}
if (!string.IsNullOrEmpty(siteId))
{
strSql += " and c.siteId='" + siteId + "'";
}
if (!string.IsNullOrEmpty(startDate))
{
strSql += " and c.createTime>= To_date('" + startDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(endDate))
{
strSql += " and c.createTime<=To_date('" + endDate + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
}
strSql += " order by c.agentId,c.siteId,c.clientId desc,c.createTime desc";
string str = commonDao.DataGrid(strSql, null, page, rows);
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// </summary>
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// <param name="orgId">机构编号</param>
/// <param name="deptId">部门编号</param>
/// <param name="userName">用户名称</param>
public void LoadGrid(int page, int rows, string deptId, string userName)
{
string strSQL = " select TOP (100) PERCENT a.*,b.deptName from TSUser a ,TSDept b";
strSQL += " where a.deptId=b.deptId and b.status='1' ";
if (deptId != null && !"".Equals(deptId))
{
strSQL += " and b.deptId = '" + deptId + "' ";
}
if (userName != null && !"".Equals(userName))
{
strSQL += " and a.userName = '******' ";
}
strSQL += " order by a.userId asc";
string str = commonDao.DataGrid(strSQL, null, page, rows);
WebJson.Write(context, str);
}
public void LoadCarGrid(int page, int rows, string stationName, string carNo, string fromDate, string toDate, string axialType)
{
Param param = new Param();
string strSQL = " select t1.*,cast(round(车辆宽度,2)as numeric(20,2)) 车辆宽度1,cast(round(超宽比例,2)as numeric(20,2)) 超宽比例1,cast(round(超宽宽度,2)as numeric(20,2)) 超宽宽度1,cast(round(车辆重量,2)as numeric(20,2)) 车辆重量1,cast(round(超重重量,2)as numeric(20,2)) 超重重量1,cast(round(超重比例,2)as numeric(20,2)) 超重比例1,cast(round(车辆高度,2)as numeric(20,2)) 车辆高度1,cast(round(超高高度,2)as numeric(20,2)) 超高高度1,cast(round(超高比例,2)as numeric(20,2)) 超高比例1,convert(varchar, 日期时间, 120) as timed,t2.站点名称 from 车辆数据 as t1,主机登记 as t2 where t1.站点编号=t2.主机编号 ";
if (!string.IsNullOrEmpty(stationName))
{
strSQL += " and t2.主机编号 =@stationNo";
param.Add("@stationNo", stationName);
}
if (!string.IsNullOrEmpty(carNo))
{
strSQL += " and t1.车牌号码 like @carNo";
param.Add("@carNo", "%" + carNo + "%");
}
if (!string.IsNullOrEmpty(fromDate) && !string.IsNullOrEmpty(toDate))
{
strSQL += " and t1.日期时间 between @obj and @obj1";
param.Add("@obj", fromDate);
param.Add("@obj1", toDate);
}
else if (!string.IsNullOrEmpty(fromDate) && string.IsNullOrEmpty(toDate))
{
strSQL += " and t1.日期时间 > @obj";
param.Add("@obj", fromDate);
}
else if (string.IsNullOrEmpty(fromDate) && !string.IsNullOrEmpty(toDate))
{
strSQL += " and t1.日期时间< @obj";
param.Add("@obj", toDate);
}
else
{
}
if (!string.IsNullOrEmpty(axialType))
{
strSQL += " and t1.车辆轴数 = @axialType";
param.Add("@axialType", axialType);
}
string str = commonDao.DataGrid(strSQL, param, page, rows);
param.Clear();
WebJson.Write(context, str);
}
/// <summary>
/// 加载DataGrid
/// <param name="page">查询页数</param>
/// <param name="rows">每页记录数</param>
/// </summary>
public void LoadGrid(string chaseOrderId, string chaseStatus, string alreadyOrNotChase, string agentId)
{
string strSQL = "";
if (chaseStatus == "0")//计划追号订单
{
if (alreadyOrNotChase == "1")
{
strSQL = @"select * from Ttchaseplan c
where c.period>
(select case when max(l.period) is null then '0' else max(l.period) end
from TTLotteryOrder l where 1=1 ";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and l.agentId='" + agentId + "'";
}
strSQL += " and l.chaseorderid='" + chaseOrderId + "') and c.chaseorderid='" + chaseOrderId + "'";
strSQL += " order by c.period desc";
}
else if (alreadyOrNotChase == "0")//已追号的订单
{
strSQL = @"select * from TTLotteryOrder l where l.chaseorderid='" + chaseOrderId + "'";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and l.agentId='" + agentId + "'";
}
strSQL += " order by l.period desc";
}
}
else if (chaseStatus != "0")//已经完成追号的订单
{
strSQL = @"select * from TTLotteryOrder l where l.chaseorderid='" + chaseOrderId + "'";
if (!string.IsNullOrEmpty(agentId))
{
strSQL += " and l.agentId='" + agentId + "'";
}
strSQL += " order by l.period desc";
}
string str = commonDao.DataGrid(strSQL, null);
WebJson.Write(context, str);
}
/// <summary>
/// 加载销售统计报表数据
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">行数</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="city">市</param>
/// <param name="county">区县</param>
/// <param name="siteId">快开厅</param>
/// <param name="agentId">代理商</param>
/// <param name="terminalId">终端号</param>
public void SalesInvoiceSummaryLoadGrid(int page, int rows, string startDate, string endDate, string city, string county, string siteId, string agentId, string terminalId)
{
string strSQL = SalesInvoiceSummarySql(startDate, endDate, city, county, siteId, agentId, terminalId);
Param param = new Param();
DataAccess db = new DataAccess(DataAccess.DBConn);
int dataTotal = 0;
try
{
db.Open();
dataTotal = int.Parse(db.ExecuteScalar(CommandType.Text, "select count(*) from (" + strSQL + ")", param).ToString());
}
catch (Exception e)
{
throw e;
}
finally
{
db.Close();
}
string strSqlData = "select * from ( select AP.* , rownum r from (" + strSQL + ") AP ";
strSqlData += " where rownum <= " + Convert.ToString(page * rows) + " ) BP where r > " + Convert.ToString((page - 1) * rows);
DataTable dt = db.ExecuteDataView(CommandType.Text, strSqlData, param).Table;
AgentAccDetailBLL agentAccDetailBLL = new AgentAccDetailBLL(context, loginSession);
List <TTAgentAccDetail> balanceList = agentAccDetailBLL.GetTTAgentAccDetailList(city, county, siteId, agentId, terminalId);
foreach (DataRow dr in dt.Rows)
{
var balanceGroupList = balanceList.Where(c => DateTime.Parse(c.createTime.Substring(0, 10)) <= DateTime.Parse(dr["dateday"].ToString()))
.OrderByDescending(x => x.createTime).GroupBy(x => x.agentId) //必须排序
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b)
.Zip(Enumerable.Range(1, t.count), (j, i) =>
new { j.balance, j.createTime, rn = i }));
var advanceBalance = balanceGroupList.Where(c => c.rn == 1).Sum(c => c.balance);
dr["advanceBalance"] = advanceBalance;
}
string str = commonDao.DataGrid(dt, dataTotal);
WebJson.Write(context, str);
}