public DataSet QueryUserWallet(DataPagingModelGet dataPagingModelGet)
{
string sqlStr = string.Format(@"SELECT SQL_CALC_FOUND_ROWS W.UID ID,U.`NAME`,U.PHONEAREAID,U.PHONE,W.ALLAMOUNTQQX,W.FROZENAMOUNTQQX,W.CANUSEAMOUNTQQX,W.ALLAMOUNTQQY,W.FROZENAMOUNTQQY,W.CANUSEAMOUNTQQY,W.ALLAMOUNTQQL,W.FROZENAMOUNTQQL,W.CANUSEAMOUNTQQL
FROM USER_INFO U
LEFT JOIN
(
SELECT wi.UID,SUM(wi.ALLAMOUNTQQX) ALLAMOUNTQQX,SUM(wi.FROZENAMOUNTQQX) FROZENAMOUNTQQX,SUM(wi.CANUSEAMOUNTQQX) CANUSEAMOUNTQQX,
SUM(wi.ALLAMOUNTQQY) ALLAMOUNTQQY,SUM(wi.FROZENAMOUNTQQY) FROZENAMOUNTQQY,SUM(wi.CANUSEAMOUNTQQY) CANUSEAMOUNTQQY,
SUM(wi.ALLAMOUNTQQL) ALLAMOUNTQQL,SUM(wi.FROZENAMOUNTQQL) FROZENAMOUNTQQL,SUM(wi.CANUSEAMOUNTQQL) CANUSEAMOUNTQQL
FROM(
SELECT UID,ALLAMOUNT,FROZENAMOUNT,CANUSEAMOUNT,
CASE CURRENCYID WHEN 0 THEN SUM(ALLAMOUNT)ELSE 0 END ALLAMOUNTQQX,
CASE CURRENCYID WHEN 0 THEN SUM(FROZENAMOUNT)ELSE 0 END FROZENAMOUNTQQX,
CASE CURRENCYID WHEN 0 THEN SUM(CANUSEAMOUNT)ELSE 0 END CANUSEAMOUNTQQX,
CASE CURRENCYID WHEN 1 THEN SUM(ALLAMOUNT)ELSE 0 END ALLAMOUNTQQY,
CASE CURRENCYID WHEN 1 THEN SUM(FROZENAMOUNT)ELSE 0 END FROZENAMOUNTQQY,
CASE CURRENCYID WHEN 1 THEN SUM(CANUSEAMOUNT)ELSE 0 END CANUSEAMOUNTQQY,
CASE CURRENCYID WHEN 2 THEN SUM(ALLAMOUNT)ELSE 0 END ALLAMOUNTQQL,
CASE CURRENCYID WHEN 2 THEN SUM(FROZENAMOUNT)ELSE 0 END FROZENAMOUNTQQL,
CASE CURRENCYID WHEN 2 THEN SUM(CANUSEAMOUNT)ELSE 0 END CANUSEAMOUNTQQL
FROM WALLET_INFO
GROUP BY UID,ALLAMOUNT,FROZENAMOUNT,CANUSEAMOUNT
) wi
GROUP BY wi.UID
)
W ON W.UID=U.ID
WHERE U.Type!='3' ");
return(MySqlHelper.Single.ExecuteDataSet(sqlStr + dataPagingModelGet.LimitString()));
}
public DataSet QueryUserCloudMinerProductionRecord(DataPagingModelGet dataPagingModelGet)
{
string sqlStr =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS date_format(r.ProductionTime, '%Y-%m-%d') ProductionTime,sum(pd.Amount) Amount ,sum(pd.QQX) QQX,sum(pd.QQY) QQY,sum(pd.QQL) QQL,r.UserId,u.Nickname,u.PhoneAreaId ,u.Phone,u.Name
FROM cloud_miner_production_record r
LEFT JOIN user_info as u ON r.UserId=u.Id
LEFT JOIN user_cloud_miner as ucm ON ucm.Id =r.UcmId
LEFT JOIN (
SELECT d.OrderId ,
SUM(d.Amount) Amount,
SUM(
CASE WHEN d.CurrencyID=0 THEN
d.Amount ELSE 0 END )QQX,
SUM(CASE WHEN d.CurrencyID=1 THEN
d.Amount ELSE 0 END) QQY,
SUM(case WHEN d.CurrencyID=2 THEN
d.Amount ELSE 0 END) QQL
FROM cloud_miner_production_detail d GROUP BY d.OrderId
) as pd ON r.Id=pd.OrderId
WHERE r.State='1'
GROUP BY date_format(r.ProductionTime, '%Y-%m-%d'),r.UserId,u.Nickname,u.PhoneAreaId,u.Phone
Order by r.ProductionTime DESC,r.UserId ASC " + dataPagingModelGet.LimitString());
return(MySqlHelper.Single.ExecuteDataSet(sqlStr));
}
public DataSet QueryUserCloudMinerDistribution(DataPagingModelGet dataPagingModelGet)
{
string sqlStr =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS date_format(r.DistributionTime, '%Y-%m-%d') DistributionTime,sum(cd.Amount) Amount ,sum(cd.QQX) QQX,sum(cd.QQY) QQY,sum(cd.QQL) QQL,r.DistributionId UserId,u.Nickname,u.PhoneAreaId,u.Phone,u.Name
FROM cloud_miner_distribution_record r
LEFT JOIN commodity as c ON r.CommodityId =c.Id
LEFT JOIN user_cloud_miner as ucm ON ucm.id=r.UcmId
LEFT JOIN user_info as u ON u.id = r.DistributionId
LEFT JOIN
(
SELECT d.OrderId,
SUM(d.Amount) Amount,
SUM(CASE WHEN d.CurrencyID=0 THEN
d.Amount ELSE 0 END )QQX,
SUM(CASE WHEN d.CurrencyID=1 THEN
d.Amount ELSE 0 END) QQY,
SUM(case WHEN d.CurrencyID=2 THEN
d.Amount ELSE 0 END) QQL
FROM cloud_miner_distribution_detail d
GROUP BY d.OrderId
) cd ON cd.OrderId=r.id
GROUP BY date_format(r.DistributionTime, '%Y-%m-%d'),r.DistributionId,u.Nickname,u.PhoneAreaId,u.Phone
Order by r.DistributionTime DESC,ucm.UserId ASC " + dataPagingModelGet.LimitString());
return(MySqlHelper.Single.ExecuteDataSet(sqlStr));
}
/// <summary>
/// 查询订单
/// </summary>
/// <returns></returns>
// public DataSet QueryStoreOrder(int userId, DataPagingModelGet dataPagingModelGet)
// {
// string strSql = string.Format(@"SELECT SQL_CALC_FOUND_ROWS s.Id,s.OrderNo,s.CommodityId,c.`Name` CommodityName,s.Count,s.UnitPrice,s.TotalPrice,date_format(s.OrderTime, '%Y-%m-%d %H:%i:%s') OrderTime,s.State,s.Type,c.DisplayImage
// FROM store_order s
// LEFT JOIN commodity as c on s.CommodityId=c.Id
// WHERE s.BuyUserId={0}
// Order BY s.OrderTime Desc " + dataPagingModelGet.LimitString(), userId);
// return MySqlHelper.Single.ExecuteDataSet(strSql);
// }
/// <summary>
/// 查询订单
/// </summary>
/// <returns></returns>
public DataSet QueryStoreOrder(int userId, DataPagingModelGet dataPagingModelGet)
{
string strSql = string.Format(@"SELECT SQL_CALC_FOUND_ROWS s.Id,s.OrderNo,s.CommodityId,c.`Name` CommodityName,s.Count,s.UnitPrice,s.TotalPrice,date_format(s.OrderTime, '%Y-%m-%d %H:%i:%s') OrderTime,date_format(s.BuyTime, '%Y-%m-%d %H:%i:%s') BuyTime,IF(s.State=0 and TIMESTAMPDIFF(SECOND,s.OrderTime,NOW())>24*60*60,'2',s.State) State,s.Type,c.DisplayImage,s.ConsignorUserId,u.PhoneAreaId ConsignorPhoneAreaId ,u.Phone ConsignorPhone,u.`Name` ConsignorName
FROM store_order s
LEFT JOIN commodity as c on s.CommodityId=c.Id
LEFT JOIN user_info as u ON s.ConsignorUserId=u.id
WHERE s.BuyUserId={0}
Order BY s.OrderTime Desc " + dataPagingModelGet.LimitString(), userId);
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
/// <summary>
/// 查询转账订单记录
/// </summary>
/// <param name="userId"></param>
/// <param name="dataPagingModelGet"></param>
/// <returns></returns>
public DataSet QueryTransactionP2PRecord(int userId, DataPagingModelGet dataPagingModelGet)
{
string strSql = string.Format(@"SELECT SQL_CALC_FOUND_ROWS tr.Id,tr.OrderNo,date_format(tr.OrderTime, '%Y-%m-%d %H:%i:%s') OrderTime,
(SELECT WalletAddress FROM wallet_info WHERE UId=tr.PayeeUserId LIMIT 1) PayeeWalletAddress,
tr.State,tr.Remark
FROM transaction_p2p_record tr
WHERE tr.PayUserId ={0}
ORDER BY tr.OrderTime DESC" + dataPagingModelGet.LimitString(), userId);
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
/// <summary>
/// 查询转账订单记录
/// </summary>
/// <param name="userId"></param>
/// <param name="dataPagingModelGet"></param>
/// <returns></returns>
public DataSet QueryCloudMinerProductionRecord(int userId, DataPagingModelGet dataPagingModelGet)
{
string strSql = string.Format(@"SELECT SQL_CALC_FOUND_ROWS cmd.Id,date_format(cmd.ProductionTime, '%Y-%m-%d') ProductionTime,ui.Nickname UserNickname,c.`Name` CommodityName
FROM cloud_miner_production_record cmd
LEFT JOIN user_cloud_miner as ucm ON cmd.ucmId=ucm.id
LEFT JOIN commodity as c ON c.id=ucm.commodityId
LEFT JOIN user_info as ui ON ucm.UserId=ui.id
WHERE cmd.UserId={0}
ORDER BY cmd.ProductionTime DESC" + dataPagingModelGet.LimitString(), userId);
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
/// <summary>
/// 查询用户云矿机
/// </summary>
/// <returns></returns>
public DataSet QueryUserCloudMiner(DataPagingModelGet dataPagingModel)
{
string strSql =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS ucm.Id,ucm.OrderId,ucm.OrderNo,ucm.UserId,u.PhoneAreaId,u.Phone,u.`Name` UserName,ucm.CommodityId,c.`Name` CommodityName,cms.ProductionCycle,cms.ProductionAmount,c.Amount,date_format(ucm.BuyTime,'%Y-%m-%d %H:%m:%s') BuyTime,ucm.ProductionCount,o.Type,ucm.State
FROM user_cloud_miner ucm
LEFT JOIN user_info as u ON ucm.UserId =u.id
LEFT JOIN commodity AS c ON ucm.CommodityId=c.Id
LEFT JOIN store_order as o ON o.id=ucm.OrderId
LEFT JOIN cloud_miner_specification as cms ON c.Id=cms.CommodityId
ORDER BY ucm.BuyTime DESC,ucm.UserId
" + dataPagingModel.LimitString());
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
public DataSet QueryCurrencyPriceRecord(DataPagingModelGet dataPagingModelGet)
{
string sqlStr = string.Format(@"
SELECT SQL_CALC_FOUND_ROWS cpr.Id,ci.`Code` CurrencyCode,cpr.CurrencyId,ci.Caption CurrencyCaption,cpr.Price,cpr.UpdateEmployeeId,date_format(cpr.UpdateTime,'%Y-%m-%d %H:%i:%s') UpdateTime,cpr.Type,
CASE cpr.Type
WHEN '0' THEN
(select Nickname FROM employee_info WHERE ID= cpr.UpdateEmployeeId LIMIT 1 )
ELSE
(select Nickname FROM user_info WHERE ID= cpr.UpdateEmployeeId LIMIT 1 )
END UpdateEmployeeName
FROM currency_price_record cpr
LEFT JOIN currency_info as ci on ci.Id=cpr.CurrencyId
ORDER BY cpr.UpdateTime DESC
");
return(MySqlHelper.Single.ExecuteDataSet(sqlStr + dataPagingModelGet.LimitString()));
}
public DataSet QueryUser(string phone, string name, DataPagingModelGet dataPagingModelGet)
{
StringBuilder sbString = new StringBuilder();
if (!string.IsNullOrEmpty(phone))
{
sbString.Append(string.Format(" AND phone LIKE '%{0}%' ", phone));
}
if (!string.IsNullOrEmpty(name))
{
sbString.Append(string.Format(" AND name LIKE '%{0}%' ", name));
}
string strSql =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS ID,AccountId,Nickname,Phone,PhoneAreaId,Name,IdCard,Email,Sex,date_format(Birthday, '%Y-%m-%d %H:%i:%s') Birthday,Type,State
FROM user_info
WHERE type!=3 {0} " + dataPagingModelGet.LimitString(), sbString.ToString());
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
/// <summary>
/// 查询订单
/// </summary>
/// <returns></returns>
public DataSet QueryStoreOrder_(DataPagingModelGet dataPagingModelGet)
{
string strSql = string.Format(@"SELECT SQL_CALC_FOUND_ROWS s.Id,s.OrderNo,s.CommodityId,c.`Name` CommodityName,s.Count,s.UnitPrice,s.TotalPrice,pd.QQX,pd.QQY,pd.QQL,date_format(s.OrderTime, '%Y-%m-%d %H:%i:%s') OrderTime,date_format(s.BuyTime, '%Y-%m-%d %H:%i:%s') BuyTime,s.BuyUserId,bu.Nickname BuyUserNickname,bu.PhoneAreaId BuyUserPhoneAreaId,bu.Phone BuyUserPhone,bu.Name BuyUserName,s.ConsignorUserId,cu.Nickname ConsignorUserNickname,cu.PhoneAreaId ConsignorUserPhoneAreaId,cu.Phone ConsignorUserPhone,cu.Name ConsignorUserName,s.Type,IF(s.State=0 and TIMESTAMPDIFF(SECOND,s.OrderTime,NOW())>24*60*60,'2',s.State) State
FROM store_order s
LEFT JOIN commodity as c ON c.Id =s.CommodityId
LEFT JOIN user_info as bu ON s.BuyUserId=bu.Id
LEFT JOIN user_info as cu ON cu.Id =s.ConsignorUserId
LEFT JOIN
(
SELECT d.OrderId,
SUM(CASE WHEN d.CurrencyID=0 THEN
d.Amount ELSE 0 END )QQX,
SUM(CASE WHEN d.CurrencyID=1 THEN
d.Amount ELSE 0 END) QQY,
SUM(case WHEN d.CurrencyID=2 THEN
d.Amount ELSE 0 END) QQL
FROM store_order_pay_detail d
GROUP BY d.OrderId
) pd ON pd.OrderId =s.Id
ORDER BY s.OrderTime DESC " + dataPagingModelGet.LimitString());
return(MySqlHelper.Single.ExecuteDataSet(strSql));
}
/// <summary>
/// 查询云矿机产币记录
/// </summary>
/// <param name="userId"></param>
/// <param name="productionDate"></param>
/// <param name="dataPagingModelGet"></param>
/// <returns></returns>
public DataSet QueryCloudMinerProductionRecord(int userId, string productionDate, DataPagingModelGet dataPagingModelGet)
{
StringBuilder whereStr = new StringBuilder();
if (userId > 0)
{
whereStr.Append(" AND r.UserId = " + userId);
}
if (!string.IsNullOrEmpty(productionDate))
{
whereStr.Append(string.Format(" AND r.ProductionTime >= '{0}' AND r.ProductionTime<'{1}' ", Convert.ToDateTime(productionDate).ToString("yyyy-MM-dd"), Convert.ToDateTime(productionDate).AddDays(1).ToString("yyyy-MM-dd")));
}
string sqlStr =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS r.Id,r.CommodityId,c.`Name` CommodityName,date_format( ucm.BuyTime, '%Y-%m-%d %H:%i:%s') BuyTime,date_format( r.ProductionTime, '%Y-%m-%d %H:%i:%s') ProductionTime,r.ProductionCount,pd.Amount,pd.QQX,pd.QQY,pd.QQL,r.UserId,u.Nickname UserNickname,u.PhoneAreaId UserPhoneAreaId,u.Phone UserPhone,u.Name UserName,r.State
FROM cloud_miner_production_record r
LEFT JOIN user_info as u ON r.UserId=u.Id
LEFT JOIN user_cloud_miner as ucm ON ucm.Id =r.UcmId
LEFT JOIN commodity as c ON r.CommodityId=c.Id
LEFT JOIN (
SELECT d.OrderId ,
SUM(d.Amount) Amount,
SUM(
CASE WHEN d.CurrencyID=0 THEN
d.Amount ELSE 0 END )QQX,
SUM(CASE WHEN d.CurrencyID=1 THEN
d.Amount ELSE 0 END) QQY,
SUM(case WHEN d.CurrencyID=2 THEN
d.Amount ELSE 0 END) QQL
FROM cloud_miner_production_detail d GROUP BY d.OrderId
) as pd ON r.Id=pd.OrderId
where 1=1 {0}
Order by r.ProductionTime DESC ,r.UserId ASC ", whereStr);
return(MySqlHelper.Single.ExecuteDataSet(sqlStr + dataPagingModelGet.LimitString()));
}
public DataSet QueryCloudMinerDistribution(int userId, string distributionDate, DataPagingModelGet dataPagingModelGet)
{
StringBuilder sbWhere = new StringBuilder();
if (userId > 0)
{
sbWhere.Append(string.Format(" AND r.DistributionId ={0} ", userId));
}
if (!string.IsNullOrEmpty(distributionDate))
{
sbWhere.Append(string.Format(" AND r.DistributionTime >= '{0}' AND r.DistributionTime<'{1}' ", Convert.ToDateTime(distributionDate).ToString("yyyy-MM-dd"), Convert.ToDateTime(distributionDate).AddDays(1).ToString("yyyy-MM-dd")));
}
string sqlStr =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS date_format(r.DistributionTime, '%Y-%m-%d') DistributionTime,r.Id,ucm.Id UcmId,c.`Name` CommodityName, date_format(ucm.BuyTime , '%Y-%m-%d') BuyTime ,cd.Amount ,cd.QQX ,cd.QQY ,cd.QQL,ucm.UserId FansUserId,f.Nickname FansNickname,f.PhoneAreaId FansPhoneAreaId,f.Phone FansPhone,f.Name FansName
FROM cloud_miner_distribution_record r
LEFT JOIN commodity as c ON r.CommodityId =c.Id
LEFT JOIN user_cloud_miner as ucm ON ucm.id=r.UcmId
LEFT JOIN user_info as f ON f.id=ucm.UserId
LEFT JOIN
(
SELECT d.OrderId,
SUM(d.Amount) Amount,
SUM(CASE WHEN d.CurrencyID=0 THEN
d.Amount ELSE 0 END )QQX,
SUM(CASE WHEN d.CurrencyID=1 THEN
d.Amount ELSE 0 END) QQY,
SUM(case WHEN d.CurrencyID=2 THEN
d.Amount ELSE 0 END) QQL
FROM cloud_miner_distribution_detail d
GROUP BY d.OrderId
) cd ON cd.OrderId=r.id
where 1=1 {0}
Order by r.DistributionTime DESC,ucm.UserId ASC " + dataPagingModelGet.LimitString(), sbWhere);
return(MySqlHelper.Single.ExecuteDataSet(sqlStr));
}
public DataSet QuerySystemMaintenance(DataPagingModelGet dataPagingModelGet)
{
string sqlStr =
string.Format(
@"SELECT SQL_CALC_FOUND_ROWS s.Id,date_format(s.MaintenanceTimeBegin, '%Y-%m-%d %H:%i:%s') MaintenanceTimeBegin,date_format(s.MaintenanceTimeEnd, '%Y-%m-%d %H:%i:%s') MaintenanceTimeEnd,
s.InsertEmployeeId,ie.AccountId InsertEmployeeAccountId,ie.Nickname InsertEmployeeNickname,date_format(s.InsertTime, '%Y-%m-%d %H:%i:%s') InsertTime,
s.UpdateEmployeeId,ue.AccountId UpdateEmployeeAccountId,ue.Nickname UpdateEmployeeNickname,date_format(s.UpdateTime, '%Y-%m-%d %H:%i:%s') UpdateTime ,s.State
FROM system_maintenance s
LEFT JOIN employee_info as ie ON s.InsertEmployeeId=ie.ID
LEFT JOIN employee_info as ue ON s.UpdateEmployeeId=ue.ID where s.InsertEmployeeId!=6 order by s.InsertTime desc" + dataPagingModelGet.LimitString());
return(MySqlHelper.Single.ExecuteDataSet(sqlStr));
}