// 获取返现记录
func (this *MemberQuery) QueryIncomeLog(memberId, page, size int,
where, orderBy string) (num int, rows []map[string]interface{}) {
d := this.Connector
if where != "" {
where = "WHERE " + where
}
if orderBy != "" {
orderBy = "ORDER BY " + orderBy
}
d.ExecScalar(fmt.Sprintf(`SELECT COUNT(0)
FROM mm_income_log l INNER JOIN mm_member m ON m.id=l.member_id
WHERE member_id=? %s`, where), &num, memberId)
sqlLine := fmt.Sprintf(`SELECT l.*,
record_time,
convert(l.fee,CHAR(10)) as fee
FROM mm_income_log l INNER JOIN mm_member m ON m.id=l.member_id
WHERE member_id=? %s %s LIMIT ?,?`,
where, orderBy)
d.Query(sqlLine, func(_rows *sql.Rows) {
rows = db.RowsToMarshalMap(_rows)
_rows.Close()
}, memberId, (page-1)*size, size)
return num, rows
}
// 查询分页订单
func (this *MemberQuery) QueryPagerOrder(memberId, page, size int,
where, orderBy string) (num int, rows []map[string]interface{}) {
d := this.Connector
if where != "" {
where = "AND " + where
}
if orderBy != "" {
orderBy = "ORDER BY " + orderBy
} else {
orderBy = " ORDER BY update_time DESC,create_time desc "
}
d.ExecScalar(fmt.Sprintf(`SELECT COUNT(0) FROM pt_order WHERE
member_id=? %s`, where), &num, memberId)
d.Query(fmt.Sprintf(` SELECT id,
order_no,
member_id,
partner_id,
shop_id,
replace(items_info,'\n','<br />') as items_info,
total_fee,
fee,
pay_fee,
payment_opt,
is_paid,
note,
status,
paid_time,
create_time,
deliver_time,
update_time
FROM pt_order WHERE member_id=? %s %s LIMIT ?,?`,
where, orderBy),
func(_rows *sql.Rows) {
rows = db.RowsToMarshalMap(_rows)
_rows.Close()
}, memberId, (page-1)*size, size)
return num, rows
}
// 获取返现记录
func (this *MemberQuery) QueryBalanceLog(memberId, page, size int,
where, orderBy string) (num int, rows []map[string]interface{}) {
d := this.Connector
if orderBy != "" {
orderBy = "ORDER BY " + orderBy
}
d.ExecScalar(fmt.Sprintf(`SELECT COUNT(0) FROM mm_balance_info bi
INNER JOIN mm_member m ON m.id=bi.member_id
WHERE bi.member_id=? %s`, where), &num, memberId)
sqlLine := fmt.Sprintf(`SELECT bi.* FROM mm_balance_info bi
INNER JOIN mm_member m ON m.id=bi.member_id
WHERE member_id=? %s %s LIMIT ?,?`,
where, orderBy)
d.Query(sqlLine, func(_rows *sql.Rows) {
rows = db.RowsToMarshalMap(_rows)
_rows.Close()
}, memberId, (page-1)*size, size)
return num, rows
}
func (portal *ExportItem) GetSchemaAndData(ht map[string]string) (rows []map[string]interface{}, total int, err error) {
total = 0
var _rows *sql.Rows
if portal.sqlConfig == nil {
dir, _ := os.Getwd()
portal.sqlConfig, err = LoadExportConfigFromXml(
strings.Join([]string{dir, "/conf/query/", portal.PortalKey, ".xml"}, ""))
if err != nil {
portal.sqlConfig = nil
return nil, 0, err
}
}
_db := portal.ItemManager.DbGetter.GetDB()
//初始化添加参数
if _, e := ht["pageSize"]; !e {
ht["pageSize"] = "10000000000"
}
if _, e := ht["pageIndex"]; !e {
ht["pageIndex"] = "1"
}
pi, _ := ht["pageIndex"]
ps, _ := ht["pageSize"]
pageIndex, _ := strconv.Atoi(pi)
pageSize, _ := strconv.Atoi(ps)
if pageIndex > 0 {
ht["page_start"] = strconv.Itoa((pageIndex - 1) * pageSize)
} else {
ht["page_start"] = "0"
}
ht["page_end"] = strconv.Itoa(pageIndex * pageSize)
ht["page_size"] = strconv.Itoa(pageSize)
//统计总行数
if portal.sqlConfig.Total != "" {
sql := SqlFormat(portal.sqlConfig.Total, ht)
smt, err := _db.Prepare(sql)
if err != nil {
log.Println("[ Export][ Error] -", err.Error(), "\n", sql)
return nil, 0, err
}
row := smt.QueryRow()
if row != nil {
err = row.Scan(&total)
if err != nil {
log.Println("[ Export][ Error] -", err.Error(), "\n", sql)
return nil, total, err
}
}
}
//获得数据
if portal.sqlConfig.Query != "" {
sql := SqlFormat(portal.sqlConfig.Query, ht)
sqlLines := strings.Split(sql, ";\n")
if t := len(sqlLines); t > 1 {
for i, v := range sqlLines {
if i != t-1 {
if smt, err := _db.Prepare(v); err == nil {
smt.Exec()
}
}
}
sql = sqlLines[t-1]
}
smt, err := _db.Prepare(sql)
if err != nil {
log.Println("[ Export][ Error] -", err.Error(), "\n", sql)
return nil, total, err
}
_rows, err = smt.Query()
if err != nil {
log.Println("[ Export][ Error] -", err.Error(), "\n", sql)
return nil, total, err
}
defer _rows.Close()
}
return db.RowsToMarshalMap(_rows), total, err
}