public List <ViewModels.FiiiPosMerchantInfoListModel> GetFiiiPosMerchantPager(string merchantName, string userName, string cellPhone, int?countryId, int?verifyStatus, ref GridPager pager)
{
string sql = "SELECT a.Id,b.Username,b.Cellphone,a.MerchantName,a.CountryId,a.Status,a.VerifyStatus FROM dbo.MerchantInformations a LEFT JOIN dbo.MerchantAccounts b on a.MerchantAccountId=b.Id WHERE AccountType=" + (int)AccountType.Merchant;
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(userName))
{
sql += " AND b.[Username]=@Username";
paramList.Add(new SqlSugar.SugarParameter("@Username", userName));
}
if (!string.IsNullOrEmpty(merchantName))
{
sql += " AND a.[MerchantName] LIKE @MerchantName";
paramList.Add(new SqlSugar.SugarParameter("@MerchantName", "%" + merchantName + "%"));
}
if (!string.IsNullOrEmpty(cellPhone))
{
sql += " AND b.[Cellphone]=@Cellphone";
paramList.Add(new SqlSugar.SugarParameter("@Cellphone", cellPhone));
}
if (countryId.HasValue)
{
sql += " AND a.[CountryId]=@CountryId";
paramList.Add(new SqlSugar.SugarParameter("@CountryId", countryId.Value));
}
if (verifyStatus.HasValue)
{
sql += " AND a.[VerifyStatus]=@VerifyStatus";
paramList.Add(new SqlSugar.SugarParameter("@VerifyStatus", verifyStatus.Value));
}
var data = QueryPager.Query <ViewModels.FiiiPosMerchantInfoListModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public List <POSViewModel> GetPOSInfoList(string userName, string cellPhone, string sn, ref GridPager pager)
{
string sql = @" select b.Id,b.Username,a.SN,b.Cellphone,b.MerchantName,b.Email,
b.Status,b.IsAllowWithdrawal,b.IsAllowAcceptPayment,b.CountryId
from MerchantAccounts b
left join pos a on b.POSId = a.Id where 1=1 ";
var paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(userName))
{
sql += " and b.UserName like @userName ";
paramList.Add(new SqlSugar.SugarParameter("@userName", "%" + userName + "%"));
}
if (!string.IsNullOrEmpty(cellPhone))
{
sql += " and b.CellPhone like @cellPhone ";
paramList.Add(new SqlSugar.SugarParameter("@cellPhone", "%" + cellPhone + "%"));
}
if (!string.IsNullOrEmpty(sn))
{
sql += " and a.SN like @sn ";
paramList.Add(new SqlSugar.SugarParameter("@sn", "%" + sn + "%"));
}
var data = QueryPager.Query <POSViewModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public async Task <Result <ListResult <RouteInfo> > > GetRoutesInRange(RoutesInRangeRequestModel model)
{
try
{
var query = new QueryPager();
ApplyPaging(model, query);
string point = $"POINT({model.Point.Lng} {model.Point.Lat})";
var results = await _routeRepository.GetRoutesInRange(query, point, model.Kilometers);
var listResult = new ListResult <RouteInfo>()
{
Count = results.Count,
HasNext = results.HasNext,
Results = results.Results.Select(r => new RouteInfo()
{
Id = r.ID, Name = r.Name, Difficulty = (Difficulty)r.Difficulty, UserId = r.UserId
}).ToList()
};
return(new Result <ListResult <RouteInfo> >(listResult));
}
catch (Exception ex)
{
ErrorResult error = GenerateError(ex);
return(new Result <ListResult <RouteInfo> >(error));
}
}
public async Task <Result <ListResult <RoutePoint> > > GetPoints(PagingRequestModel model, string routes)
{
try
{
var pager = new QueryPager();
ApplyPaging(model, pager);
var results = await _pointRepository.Search(pager);
var listResult = new ListResult <RoutePoint>()
{
Count = results.Count,
HasNext = results.HasNext,
Results = results.Results.Select(r => new RoutePoint()
{
Id = r.RouteInfo.ID,
Point = GeoPointConverter.GetPoint(r.Point)
}).ToList()
};
return(new Result <ListResult <RoutePoint> >(listResult));
}
catch (Exception ex)
{
ErrorResult error = GenerateError(ex);
return(new Result <ListResult <RoutePoint> >(error));
}
}
public List <AgentViewModel> GetAgentPersonPager(string companyName, int?countryId, int?stateId, int?cityId, ref GridPager pager)
{
string sql = "SELECT t1.*,t2.SaleCode,t2.SaleName FROM [dbo].[Agent] t1";
sql += " LEFT JOIN [dbo].[Salesperson] t2 ON t1.SaleId=t2.Id";
sql += " WHERE 1=1";
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(companyName))
{
sql += " AND t1.CompanyName LIKE @CompanyName";
paramList.Add(new SqlSugar.SugarParameter("@CompanyName", "%" + companyName + "%"));
}
if (countryId.HasValue && countryId.Value > 0)
{
sql += " AND t1.CountryId=@CountryId";
paramList.Add(new SqlSugar.SugarParameter("@CountryId", countryId.Value));
}
if (stateId.HasValue && stateId.Value > 0)
{
sql += " AND t1.StateId=@StateId";
paramList.Add(new SqlSugar.SugarParameter("@StateId", stateId.Value));
}
if (cityId.HasValue && cityId.Value > 0)
{
sql += " AND t1.CityId=@CityId";
paramList.Add(new SqlSugar.SugarParameter("@CityId", cityId.Value));
}
var data = QueryPager.Query <AgentViewModel>(BoDB.DB, sql, ref pager, paramList);
return(data);
}
public List <FiiipayMerchantInfoListModel> GetMerchantPager(string fiiipayAccount, string merchantName, int?countryId, byte?merchantStatus, byte?verifyStatus, ref GridPager pager)
{
string sql = "SELECT a.CreateTime, a.Id,b.Cellphone FiiiPayAccount,a.MerchantName,a.CountryId,a.VerifyStatus,a.Status,a.IsAllowExpense,FromType FROM dbo.MerchantInformations a LEFT JOIN dbo.UserAccounts b on a.MerchantAccountId=b.Id WHERE AccountType=" + (int)Entities.Enums.AccountType.User;
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(fiiipayAccount))
{
sql += " AND b.[Cellphone]=@Cellphone";
paramList.Add(new SqlSugar.SugarParameter("@Cellphone", fiiipayAccount));
}
if (!string.IsNullOrEmpty(merchantName))
{
sql += " AND a.[MerchantName] LIKE @MerchantName";
paramList.Add(new SqlSugar.SugarParameter("@MerchantName", "%" + merchantName + "%"));
}
if (countryId.HasValue)
{
sql += " AND a.[CountryId]=@CountryId";
paramList.Add(new SqlSugar.SugarParameter("@CountryId", countryId.Value));
}
if (merchantStatus.HasValue)
{
sql += " AND a.[Status]=@Status";
paramList.Add(new SqlSugar.SugarParameter("@Status", merchantStatus.Value));
}
if (verifyStatus.HasValue)
{
sql += " AND a.[VerifyStatus]=@VerifyStatus";
paramList.Add(new SqlSugar.SugarParameter("@VerifyStatus", verifyStatus.Value));
}
var data = QueryPager.Query <FiiipayMerchantInfoListModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public List <OrderViewModel> GetOrderViewList(string orderNo, string possn, ref GridPager pager)
{
string sql = @" select a.Id,a.OrderNo,b.MerchantName,c.SN as PostSN,D.Cellphone,a.FiatAmount,a.Markup,a.ActualFiatAmount,a.CryptoId,a.ExchangeRate,
a.CryptoAmount,a.TransactionFee,a.ActualCryptoAmount,a.Status,a.Timestamp from Orders a
left join MerchantAccounts b on a.MerchantAccountId = b.Id
left join POS c on b.POSId = c.Id
left join UserAccounts d on a.UserAccountId = d.Id
where 1 =1 ";
var paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(orderNo))
{
sql += " and a.OrderNo like @OrderNo ";
paramList.Add(new SqlSugar.SugarParameter("@OrderNo", "%" + orderNo + "%"));
}
if (!string.IsNullOrEmpty(possn))
{
sql += " and c.SN like @POSSN ";
paramList.Add(new SqlSugar.SugarParameter("@POSSN", "%" + possn + "%"));
}
var data = QueryPager.Query <OrderViewModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public List <FeedbackViewModel> GetFeedbacksList(string type, bool?hasProcessor, ref GridPager pager)
{
string sql = " SELECT t1.*,(CASE when t1.Type = 'FiiiPOS' then t3.Username else t2.Cellphone end) as AccountName," +
"(CASE when t1.Type = 'FiiiPOS' then t3.CountryId else t2.CountryId end) as CountryId from Feedbacks t1 ";
sql += " LEFT JOIN UserAccounts t2 on t1.AccountId = t2.id ";
sql += " LEFT JOIN MerchantAccounts t3 on t1.AccountId = t3.Id where 1=1 ";
var paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(type))
{
sql += " and t1.Type=@Type ";
paramList.Add(new SqlSugar.SugarParameter("@Type", type));
}
if (hasProcessor.HasValue)
{
sql += "and t1.HasProcessor=@HasProcessor";
paramList.Add(new SqlSugar.SugarParameter("@HasProcessor", hasProcessor));
}
var data = QueryPager.Query <FeedbackViewModel>(FiiiPayDB.DB, sql, ref pager, paramList);
//var data = FiiiPayDB.DB.Ado.SqlQuery<Feedbacks>(sql, new { Type = type, HasProcessor = hasProcessor }).ToList();
return(data);
}
public List <DepositViewModel> GetMerchantDepositViewList(string orderNo, string username, string address, string txid, string status, string cryptoId, ref GridPager pager)
{
string sql = @" select a.Id,a.OrderNo,c.Username,a.FromAddress as Address,b.CryptoId,c.CountryId,a.RequestId,a.Amount,a.Status,a.Timestamp,a.TransactionId as TXID,a.FromTag as Tag from [dbo].[MerchantDeposits] a
left join [dbo].[MerchantWallets] b on a.MerchantWalletId = b.Id
left join [dbo].[MerchantAccounts] c on b.MerchantAccountId = c.Id
left join [dbo].[MerchantWithdrawalFee] d on a.Id = d.WithdrawalId
where 1 =1 ";
var paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(orderNo))
{
sql += " and a.OrderNo like @OrderNo ";
paramList.Add(new SqlSugar.SugarParameter("@OrderNo", "%" + orderNo + "%"));
}
if (!string.IsNullOrEmpty(username))
{
sql += " and c.Username like @Username ";
paramList.Add(new SqlSugar.SugarParameter("@Username", "%" + username + "%"));
}
if (!string.IsNullOrEmpty(address))
{
sql += " and a.FromAddress like @Address ";
paramList.Add(new SqlSugar.SugarParameter("@Address", "%" + address + "%"));
}
if (!string.IsNullOrEmpty(txid))
{
sql += " and a.TransactionId like @TXID ";
paramList.Add(new SqlSugar.SugarParameter("@TXID", "%" + txid + "%"));
}
if (!string.IsNullOrEmpty(status))
{
sql += " and a.Status=@Status ";
paramList.Add(new SqlSugar.SugarParameter("@Status", status));
}
if (!string.IsNullOrEmpty(cryptoId))
{
sql += " and b.CryptoId=@CryptoId ";
paramList.Add(new SqlSugar.SugarParameter("@CryptoId", cryptoId));
}
var data = QueryPager.Query <DepositViewModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public async Task <PagingResult <RouteInfoEntity> > GetRoutesInRange(QueryPager pager, string point, int kilometers)
{
List <int> ids = new List <int>(pager.Take);
long count = 0;
using (NpgsqlConnection conn = new NpgsqlConnection(_context.Database.GetDbConnection().ConnectionString))
{
conn.Open();
var getInRangeCountCommand = new NpgsqlCommand(RouteGeomEntityProcedures.GETINRANGECOUNT_PROCEDURE, conn);
getInRangeCountCommand.CommandType = CommandType.StoredProcedure;
getInRangeCountCommand.Parameters.AddWithValue(@"point", point);
getInRangeCountCommand.Parameters.AddWithValue(@"dist", kilometers);
NpgsqlDataReader countDataReader = getInRangeCountCommand.ExecuteReader();
while (countDataReader.Read())
{
count = (long)countDataReader[0];
}
countDataReader.Close();
var getInRangeCommand = new NpgsqlCommand(RouteGeomEntityProcedures.GETINRANGE_PROCEDURE, conn);
getInRangeCommand.CommandType = CommandType.StoredProcedure;
getInRangeCommand.Parameters.AddWithValue("@point", point);
getInRangeCommand.Parameters.AddWithValue("@dist", kilometers);
getInRangeCommand.Parameters.AddWithValue("@take", pager.Take);
getInRangeCommand.Parameters.AddWithValue("@skip", pager.Skip);
var rangeDataReader = getInRangeCommand.ExecuteReader();
while (rangeDataReader.Read())
{
ids.Add((int)rangeDataReader[0]);
}
rangeDataReader.Close();
conn.Close();
}
return(new PagingResult <RouteInfoEntity>()
{
Count = (int)count,
HasNext = (pager.Skip <= 0 && pager.Take <= 0) ? false : (pager.Skip + (pager.Take - 1) < count),
Results = await _context.Routes.Where(r => ids.Any(id => id == r.ID)).ToListAsync()
});
}
public List <POSViewModel> GetPOSInfoList(string merchantName, string username, string cellPhone, AccountStatus?status, string possn, int?countryId, ref GridPager pager)
{
string sql = @" select b.Id,b.Username,a.SN,b.Cellphone,b.MerchantName,b.CountryId,b.Email,
b.Status,b.IsAllowWithdrawal,b.IsAllowAcceptPayment
from MerchantAccounts b
left join pos a on b.POSId = a.Id where 1=1 ";
var paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(merchantName))
{
sql += " and b.MerchantName like @MerchantName ";
paramList.Add(new SqlSugar.SugarParameter("@MerchantName", "%" + merchantName + "%"));
}
if (!string.IsNullOrEmpty(cellPhone))
{
sql += " and b.CellPhone like @CellPhone ";
paramList.Add(new SqlSugar.SugarParameter("@CellPhone", "%" + cellPhone + "%"));
}
if (!string.IsNullOrEmpty(username))
{
sql += " and b.Username like @Username ";
paramList.Add(new SqlSugar.SugarParameter("@Username", "%" + username + "%"));
}
if (!string.IsNullOrEmpty(possn))
{
sql += " and a.SN like @POSSN ";
paramList.Add(new SqlSugar.SugarParameter("@POSSN", "%" + possn + "%"));
}
if (status.HasValue)
{
sql += "and b.Status=@Status";
paramList.Add(new SqlSugar.SugarParameter("@Status", Convert.ToInt32(status)));
}
if (countryId.HasValue && countryId.Value > 0)
{
sql += " AND b.CountryId=@CountryId";
paramList.Add(new SqlSugar.SugarParameter("@CountryId", countryId.Value));
}
var data = QueryPager.Query <POSViewModel>(FiiiPayDB.DB, sql, ref pager, paramList);
return(data);
}
public List <SalespersonViewModel> GetSalesPersonPager(string salesCode, string salesName, string mobile, ref GridPager pager)
{
string sql = "SELECT *,(SELECT COUNT(1) FROM dbo.Agent t2 WHERE t2.SaleId=t1.Id) AS AgentCount FROM dbo.Salesperson t1 WHERE 1=1";
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(salesCode))
{
sql += " AND t1.SaleCode LIKE @SaleCode";
paramList.Add(new SqlSugar.SugarParameter("@SaleCode", "%" + salesCode + "%"));
}
if (!string.IsNullOrEmpty(salesName))
{
sql += " AND t1.SaleName LIKE @SaleName";
paramList.Add(new SqlSugar.SugarParameter("@SaleName", "%" + salesName + "%"));
}
if (!string.IsNullOrEmpty(mobile))
{
sql += " AND t1.Mobile=@Mobile";
paramList.Add(new SqlSugar.SugarParameter("@Mobile", mobile));
}
var data = QueryPager.Query <SalespersonViewModel>(BoDB.DB, sql, ref pager, paramList);
return(data);
}
public List <VerifyRecordViewModel> GetVerifyCount(string verifyAccount, string type, DateTime?startDate, DateTime?endDate, ref GridPager pager)
{
string sql = @"select AccountId,Username as VerifyAccount,count(*) as [VerifyCount] from ActionLog where 1=1 ";
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(verifyAccount))
{
sql += " and Username like @VerifyAccount ";
paramList.Add(new SqlSugar.SugarParameter("@VerifyAccount", "%" + verifyAccount + "%"));
}
if (!string.IsNullOrEmpty(type))
{
sql += " and ModuleCode like @Type ";
paramList.Add(new SqlSugar.SugarParameter("@Type", "%" + type + "%"));
}
else
{
sql += " and ModuleCode like @Type ";
paramList.Add(new SqlSugar.SugarParameter("@Type", "%Verify%"));
}
if (startDate.HasValue)
{
sql += " and CreateTime >= @StartDate ";
paramList.Add(new SqlSugar.SugarParameter("@StartDate", startDate.Value));
}
if (endDate.HasValue)
{
sql += " and CreateTime <= @EndDate ";
paramList.Add(new SqlSugar.SugarParameter("@EndDate", endDate.Value));
}
sql += " group by AccountId,Username ";
var data = QueryPager.Query <VerifyRecordViewModel>(BoDB.DB, sql, ref pager, paramList);
return(data);
}
/// <summary>
/// 查询POS机预订单
/// </summary>
/// <param name="OrderName">预定人</param>
/// <param name="Paymentstatus">付款状态</param>
/// <param name="Shippingstatus">发货状态</param>
/// <param name="pager"></param>
/// <returns></returns>
public List <AdvanceOrders> GetCodeQuerAll(string OrderName, string Paymentstatus, string Shippingstatus, ref GridPager pager)
{
string sql = "select * from AdvanceOrders where 1=1 ";
List <SqlSugar.SugarParameter> paramList = new List <SqlSugar.SugarParameter>();
if (!string.IsNullOrEmpty(OrderName))
{
sql += " AND OrderName=@OrderName";
paramList.Add(new SqlSugar.SugarParameter("@OrderName", OrderName));
}
if (!string.IsNullOrEmpty(Paymentstatus))
{
sql += " AND Paymentstatus=@Paymentstatus";
paramList.Add(new SqlSugar.SugarParameter("@Paymentstatus", Paymentstatus));
}
if (!string.IsNullOrEmpty(Shippingstatus))
{
sql += " AND Shippingstatus=@Shippingstatus";
paramList.Add(new SqlSugar.SugarParameter("@Shippingstatus", Shippingstatus));
}
var data = QueryPager.Query <AdvanceOrders>(BoDB.DB, sql, ref pager, paramList);
return(data);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="requestParams"></param>
/// <param name="expression"></param>
/// <param name="listOrderBy"></param>
/// <returns></returns>
protected Models.Response.PageResult GetByPage(QueryPager requestParams, Expression <Func <T, bool> > expression = null, IList <OrderBy> listOrderBy = null)
{
return(this.GetByPage(requestParams.pn, requestParams.ps, expression, listOrderBy));
}