/// <summary>
/// 骑士交易流水API add by caoheyang 20150511
/// </summary>
/// <param name="clienterId">骑士id</param>
/// <returns></returns>
public IList <FinanceRecordsDM> GetByClienterId(int clienterId)
{
IList <FinanceRecordsDM> models = new List <FinanceRecordsDM>();
const string querysql = @"
select Id,ClienterId as UserId,Amount,Status,Balance,RecordType,Operator,OperateTime,WithwardId,RelationNo,Remark,
substring(convert(varchar(100),OperateTime,24),1,5) as TimeInfo,
case convert(varchar(100), OperateTime, 23)
when convert(varchar(100), getdate(), 23) then '今日'
else substring(convert(varchar(100), OperateTime, 23),6,5) end
as DateInfo,
case substring(convert(varchar(100), OperateTime, 23),1,7)
when substring(convert(varchar(100), getdate(), 23),1,7) then '本月'
else convert(varchar(4),datepart(Year,OperateTime))+'年'+convert(varchar(4),datepart(month,OperateTime)) +'月' end
as MonthInfo
from ClienterBalanceRecord (nolock)
where ClienterId=@ClienterId
order by Id desc";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("ClienterId", clienterId);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <FinanceRecordsDM>(dt);
}
return(models);
}
/// <summary>
/// 根据消息Id获取消息信息
/// danny-20150617
/// </summary>
/// <param name="messageId">消息Id</param>
/// <returns></returns>
public MessageModel GetMessageById(int messageId)
{
string selSql = @"
SELECT [Id]
,[PushWay]
,[MessageType]
,[Content]
,[SentStatus]
,[PushType]
,[PushTarget]
,[PushCity]
,[PushPhone]
,[SendType]
,[SendTime]
,[OverTime]
,[CreateBy]
,[CreateTime]
,[UpdateBy]
,[UpdateTime]
FROM [Message] msg WITH(NOLOCK)
WHERE Id=@MessageId; ";
IDbParameters parm = DbHelper.CreateDbParameters();
parm.AddWithValue("@MessageId", messageId);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, selSql, parm));
if (dt != null && dt.Rows.Count > 0)
{
return(DataTableHelper.ConvertDataTableList <MessageModel>(dt)[0]);
}
return(null);
}
/// <summary>
/// 根据条件查询
/// </summary>
/// <param name="clienterFinanceAccountPm">参数实体</param>
/// <returns></returns>
public IList <ClienterFinanceAccount> Query(ClienterFinanceAccountPM clienterFinanceAccountPm)
{
IList <ClienterFinanceAccount> models = new List <ClienterFinanceAccount>();
string condition = BindQueryCriteria(clienterFinanceAccountPm);
string querysql = @"
select Id,ClienterId,TrueName,AccountNo,IsEnable,AccountType,OpenBank,OpenSubBank,CreateBy,CreateTime,UpdateBy,UpdateTime
from ClienterFinanceAccount (nolock)" + condition;
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ClienterFinanceAccount>(dt);
}
return(models);
}
/// <summary>
/// 存储过程分页
/// </summary>
/// <param name="connectionString">连接字符串（DBConfig）</param>
/// <param name="currentPage">当前页</param>
/// <param name="where_">条件</param>
/// <param name="OrderByColumn">排序条件</param>
/// <param name="ColumnList">列名</param>
/// <param name="TableList">表名</param>
/// <param name="PageSize">每页显示条数</param>
/// <param name="IsAccount">是否返回总条数、总页数</param>
/// <returns></returns>
public PageInfo <T> GetPages <T>(string connectionString, int currentPage, string where_, string OrderByColumn, string ColumnList, string TableList, int PageSize, bool IsAccounte)
{
try
{
int TotalRecord = 0;
int TotalPage = 0;
var _table = GetPages(connectionString, currentPage, where_, OrderByColumn, ColumnList, TableList, PageSize, IsAccounte, out TotalRecord, out TotalPage);
var pageInfo = new PageInfo <T>(TotalRecord, currentPage, DataTableHelper.ConvertDataTableList <T>(_table), TotalPage, PageSize);
return(pageInfo);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 查询对象
/// </summary>
/// <param name="clienterWithdrawLogPm">参数实体</param>
/// <returns></returns>
public IList <ClienterWithdrawLog> Query(ClienterWithdrawLogPM clienterWithdrawLogPm)
{
IList <ClienterWithdrawLog> models = new List <ClienterWithdrawLog>();
string condition = BindQueryCriteria(clienterWithdrawLogPm);
string querysql = @"
select Id,WithwardId,Status,Remark,Operator,OperatTime
from ClienterWithdrawLog (nolock)" + condition;
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ClienterWithdrawLog>(dt);
}
return(models);
}
/// <summary>
/// 查询对象
/// </summary>
/// <param name="clienterBalanceRecordPm">参数实体</param>
/// <returns></returns>
public IList <ClienterBalanceRecord> Query(ClienterBalanceRecordPM clienterBalanceRecordPm)
{
string condition = BindQueryCriteria(clienterBalanceRecordPm);
IList <ClienterBalanceRecord> models = new List <ClienterBalanceRecord>();
string querysql = @"
select Id,ClienterId,Amount,Status,Balance,RecordType,Operator,OperateTime,RelationNo,Remark
from ClienterBalanceRecord (nolock)" + condition;
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ClienterBalanceRecord>(dt);
}
return(models);
}
/// <summary>
/// 查询对象
/// <param name="clienterWithdrawFormPm"> 参数实体</param>
/// </summary>
public IList <ClienterWithdrawForm> Query(ClienterWithdrawFormPM clienterWithdrawFormPm)
{
IList <ClienterWithdrawForm> models = new List <ClienterWithdrawForm>();
string condition = BindQueryCriteria(clienterWithdrawFormPm);
string querysql = @"
select Id,WithwardNo,ClienterId,BalancePrice,AllowWithdrawPrice,Status,Amount,Balance,WithdrawTime,Auditor,AuditTime,AuditFailedReason,Payer,PayTime,PayFailedReason
from ClienterWithdrawForm (nolock)" + condition;
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ClienterWithdrawForm>(dt);
}
return(models);
}
/// <summary>
/// 查询对象
/// </summary>
public IList <OrderChild> Query(OrderChlidPM orderChildPM)
{
IList <OrderChild> models = new List <OrderChild>();
string condition = BindQueryCriteria(orderChildPM);
string querysql = @"
select Id,OrderId,ChildId,TotalPrice,GoodPrice,DeliveryPrice,PayStyle,PayType,PayStatus,
PayBy,PayTime,PayPrice,HasUploadTicket,TicketUrl,CreateBy,CreateTime,UpdateBy,UpdateTime
from OrderChild (nolock)" + condition;
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <OrderChild>(dt);
}
return(models);
}
/// <summary>
/// 查询今日尚未执行的服务
/// </summary>
public IList <ExportSqlManage> QueryForWindows(DataManageSearchCriteria search)
{
IList <ExportSqlManage> models = new List <ExportSqlManage>();
const string querysql = @"
select Id,Name,SqlText,Executetime,ReceiveEmail,IsEnable
from ExportSqlManage (nolock) where IsEnable=0
and ABS(datediff(minute,getdate(),CONVERT(DATETIME, CONVERT(varchar(100), GETDATE(), 23)+' '+Executetime) ) ) <8
and ABS( datediff(minute,getdate(),CONVERT(DATETIME, CONVERT(varchar(100), GETDATE(), 23)+' '+Executetime) )) >0";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ExportSqlManage>(dt);
}
return(models);
}
/// <summary>
/// 根据ID获取对象
/// </summary>
public BusinessFinanceAccount GetById(int id)
{
BusinessFinanceAccount model = null;
const string querysql = @"
select Id,BusinessId,TrueName,AccountNo,IsEnable,AccountType,BelongType,OpenBank,OpenSubBank,CreateBy,CreateTime,UpdateBy,UpdateTime
from BusinessFinanceAccount (nolock)
where Id=@Id and IsEnable=1";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("Id", id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <BusinessFinanceAccount>(dt)[0];
}
return(model);
}
/// <summary>
/// 根据ID获取对象
/// <param name="id">id</param>
/// </summary>
public ClienterWithdrawForm GetById(long id)
{
ClienterWithdrawForm model = new ClienterWithdrawForm();
const string querysql = @"
select Id,WithwardNo,ClienterId,BalancePrice,AllowWithdrawPrice,Status,Amount,Balance,WithdrawTime,Auditor,AuditTime,AuditFailedReason,Payer,PayTime,PayFailedReason
from ClienterWithdrawForm (nolock)
where Id=@Id ";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("Id", id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <ClienterWithdrawForm>(dt)[0];
}
return(model);
}
/// <summary>
/// 根据id查询对象
/// </summary>
public ExportSqlManage GetById(long id)
{
ExportSqlManage model = null;
const string getbyidSql = @"
select Id,Name,SqlText,Executetime,ReceiveEmail,IsEnable
from ExportSqlManage (nolock)
where id=@ID";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("ID", id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, getbyidSql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <ExportSqlManage>(dt)[0];
}
return(model);
}
/// <summary>
/// 根据商户ID获取当前商户的所有有效金融账户 add by caoheyang 20150511
/// </summary>
/// <param name="businessId">商户ID</param>
/// <returns></returns>
public IList <BusinessFinanceAccount> GetByBusinessId(int businessId)
{
IList <BusinessFinanceAccount> models = new List <BusinessFinanceAccount>();
const string querysql = @"
select Id,BusinessId,TrueName,AccountNo,IsEnable,AccountType,BelongType,OpenBank,OpenSubBank,CreateBy,CreateTime,UpdateBy,UpdateTime
from BusinessFinanceAccount (nolock)
where BusinessId=@BusinessId and IsEnable=1"; //事物内不加锁
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("BusinessId", businessId);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <BusinessFinanceAccount>(dt);
}
return(models);
}
/// <summary>
/// 根据ID获取对象
/// </summary>
/// <param name="id">id</param>
/// <returns></returns>
public ClienterBalanceRecord GetById(long id)
{
ClienterBalanceRecord model = new ClienterBalanceRecord();
const string querysql = @"
select Id,ClienterId,Amount,Status,Balance,RecordType,Operator,OperateTime,RelationNo,Remark
from ClienterBalanceRecord (nolock)
where Id=@Id ";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("Id", id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <ClienterBalanceRecord>(dt)[0];
}
return(model);
}
/// <summary>
/// 根据ID获取对象
/// </summary>
public ClienterWithdrawLog GetById(long id)
{
ClienterWithdrawLog model = new ClienterWithdrawLog();
const string querysql = @"
select Id,WithwardId,Status,Remark,Operator,OperatTime
from ClienterWithdrawLog (nolock)
where Id=@Id ";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("Id", id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <ClienterWithdrawLog>(dt)[0];
}
return(model);
}
/// <summary>
/// 根据ID获取对象
/// </summary>
public OrderChild GetById(long id)
{
OrderChild model = new OrderChild();
const string querysql = @"
select Id,OrderId,ChildId,TotalPrice,GoodPrice,DeliveryPrice,PayStyle,PayType,PayStatus,
PayBy,PayTime,PayPrice,HasUploadTicket,TicketUrl,CreateBy,CreateTime,UpdateBy,UpdateTime
from OrderChild (nolock)
where Id=@Id ";
IDbParameters dbParameters = DbHelper.CreateDbParameters("Id", DbType.Int64, 8, id);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
model = DataTableHelper.ConvertDataTableList <OrderChild>(dt)[0];
}
return(model);
}
/// <summary>
/// B端商户拉取收货人地址缓存到本地 add By caoheyang 20150702
/// </summary>
/// <param name="model">参数实体</param>
/// <returns></returns>
public IList <ConsigneeAddressBDM> ConsigneeAddressB(ConsigneeAddressBPM model)
{
IList <ConsigneeAddressBDM> models = new List <ConsigneeAddressBDM>();
const string querysql = @"
select Id ,PhoneNo,Address,CONVERT(varchar(100),PubDate, 120) as PubDate
from dbo.ReceviceAddress
where PubDate > @PubDate
and BusinessId = @BusinessId";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.Add("BusinessId", DbType.Int32, 4).Value = model.BusinessId;
dbParameters.Add("PubDate", DbType.DateTime).Value = (model.PubDate == null
? Convert.ToDateTime("2015-01-01 00:00:00")
: Convert.ToDateTime(model.PubDate));
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ConsigneeAddressBDM>(dt);
}
return(models);
}
/// <summary>
/// 获取最新 的 一条 补贴设置
/// wc
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public SubsidyResultModel GetCurrentSubsidy(int groupId)
{
SubsidyResultModel subsidyResultModel = new SubsidyResultModel();
StringBuilder selSql = new StringBuilder();
selSql.Append(@"SELECT
TOP 1
OrderCommission ,
DistribSubsidy ,
WebsiteSubsidy
FROM dbo.subsidy sub WITH ( NOLOCK )
WHERE sub.[Status] = 1 ");
if (groupId > 0)
{
selSql.Append(" AND sub.GroupId = @groupId ");
}
else
{
selSql.Append(" AND sub.GroupId = 0 "); //没有集团就 取 系统中默认设置的 补贴记录
}
selSql.Append(" ORDER BY id DESC ");
IDbParameters parm = DbHelper.CreateDbParameters();
parm.AddWithValue("@groupId", groupId);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, selSql.ToString(), parm));
if (dt != null && dt.Rows.Count != 0)
{
subsidyResultModel = DataTableHelper.ConvertDataTableList <SubsidyResultModel>(dt)[0];
}
return(subsidyResultModel);
}
