/// <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>
/// 获取当前sql执行之后的datatable add by caoheyang 20150603
/// </summary>
public DataTable ExecuteForExport(string sql)
{
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, sql));
if (DataTableHelper.CheckDt(dt))
{
return(dt);
}
return(null);
}
/// <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>
/// <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="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>
/// 查询对象
/// </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获取当前商户的所有有效金融账户 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>
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获取对象
/// </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>
/// 获取商户骑士对应关系
/// </summary>
/// <UpdateBy>hulingbo</UpdateBy>
/// <UpdateTime>20150609</UpdateTime>
/// <param name="id"></param>
/// <returns></returns>
public BusinessClienterRelation GetDetails(BusinessClienterRelationPM businessClienterRelation)
{
BusinessClienterRelation model = null;
string querySql = @"
select Id,BusinessId,ClienterId,IsEnable,CreateBy,CreateTime,UpdateBy,UpdateTime,IsBind
from dbo.[BusinessClienterRelation] (nolock )
where IsEnable=1 and BusinessId=@BusinessId and ClienterId=@ClienterId";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.AddWithValue("BusinessId", businessClienterRelation.BusinessId);
dbParameters.AddWithValue("ClienterId", businessClienterRelation.ClienterId);
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querySql, dbParameters));
if (DataTableHelper.CheckDt(dt) && dt.Rows.Count > 0)
{
model = MapRows <BusinessClienterRelation>(dt)[0];
}
return(model);
}
