func QueryBooks(limit int, offset int) (books []Book) {
db, err := GetBookDB()
if err != nil {
panic(err)
}
defer db.Close()
qb, _ := orm.NewQueryBuilder(driverName)
qb.Select("ID", "NAME", "TAGS").From(tableName).OrderBy("ID").Limit(limit).Offset(offset)
logs.Logger.Info(qb.String())
rows, err := db.Query(qb.String())
if err != nil {
panic(err)
}
defer rows.Close()
books = []Book{}
for rows.Next() {
book := Book{}
err = rows.Scan(&book.Id, &book.Name, &book.Tags)
if err != nil {
panic(err)
}
books = append(books, book)
}
return books
}
func SearchUser(key string, limit int) *[]UserSearchModel {
var users []UserSearchModel
// 获取 QueryBuilder 对象. 需要指定数据库驱动参数。
// 第二个返回值是错误对象，在这里略过
qb, _ := orm.NewQueryBuilder("mysql")
// 构建查询对象
qb.Select(
"user.id user_id",
"user.user_name",
"user_profile.*",
).
From(
"user",
).
InnerJoin(
"user_profile",
).
On(
"user.user_profile_id = user_profile.id",
).
Where("user_profile.identity like ? or user_profile.real_name like ? or user.user_name like ? or user_profile.phone like ?").
Limit(limit).Offset(0)
// 导出SQL语句
sql := qb.String()
// 执行SQL语句
o := orm.NewOrm()
o.Raw(sql, key, key, key, key).QueryRows(&users)
return &users
}
func SearchGroup(key string, limit int) *[]GroupSearchModel {
var groups []GroupSearchModel
// 获取 QueryBuilder 对象. 需要指定数据库驱动参数。
// 第二个返回值是错误对象，在这里略过
qb, _ := orm.NewQueryBuilder("mysql")
// 构建查询对象
qb.Select(
"`group`.id group_id",
"`group`.group_name",
"group_profile.*",
).
From(
"`group`",
).
InnerJoin(
"group_profile",
).
On(
"`group`.group_profile_id = group_profile.id",
).
Where("`group`.group_name like ? or group_profile.group_real_name like ? or group_profile.contact_phone like ? or group_profile.contact_name like ?").
Limit(limit).Offset(0)
// 导出SQL语句
sql := qb.String()
// 执行SQL语句
o := orm.NewOrm()
o.Raw(sql, key, key, key, key).QueryRows(&groups)
return &groups
}
func (this *TrackingEvent) QueryEvent(cvid string, guid string, startDate time.Time, endDate time.Time) ([]TrackingEvent, int) {
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder("mysql")
var conditions []interface{}
conditions = append(conditions, startDate)
conditions = append(conditions, endDate)
qb.Select("*").From("tracking_event").
Where("createDate between ? and ?")
if cvid != "" {
qb.And("cvid = ?")
conditions = append(conditions, cvid)
}
if guid != "" {
qb.And("guid = ?")
conditions = append(conditions, guid)
}
qb.Limit(1000)
rows := []TrackingEvent{}
o.Raw(qb.String(), conditions).QueryRows(&rows)
return rows, len(rows)
}
func UpdateTags(filePath string) {
tags := ReadXlxs(filePath)
logs.Logger.Info(tags)
limit := 20
offset := 0
db, err := GetBookDB()
if err != nil {
panic(err)
}
defer db.Close()
books := QueryBooks(limit, offset)
leng := len(books)
var buffer bytes.Buffer
for leng == limit {
for _, book := range books {
// logs.Logger.Info("result:", book.Id, book.Name, book.Tags)
tag := tags[book.Name]
if tag != "" {
qb, _ := orm.NewQueryBuilder(driverName)
qb.Update(tableName).Set("TAGS = '" + tag + "'").Where("NAME = '" + book.Name + "'")
// db.Exec(qb.String())
logs.Logger.Info(qb.String())
buffer.WriteString(qb.String() + ";")
}
}
offset = offset + 1
books = QueryBooks(limit, offset*limit)
leng = len(books)
}
write2File(buffer.String(), "d:\\updatetag.sql")
}
// 根据某个字查询统计字段值的求和
func (t *Transaction) Sum(table string, field string, where string, value interface{}) (int64, bool) {
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select(fmt.Sprintf("SUM(%s)", field)).From(table).Where(where + " = ?")
var sum int64 = 0
if err := t.o.Raw(qb.String(), value).QueryRow(&sum); err != nil {
return 0, false
}
return sum, true
}
func QueryEndpointidbyNames(endpoints []string, limit int) (enp []Endpoint, err error) {
q := orm.NewOrm()
q.Using("graph")
q.QueryTable("endpoint")
qb, _ := orm.NewQueryBuilder("mysql")
qt := qb.Select("*").From("endpoint").Where("endpoint").In(endpoints...).Limit(limit)
_, err = q.Raw(qt.String()).QueryRows(&enp)
return
}
func OrmGetAll() (entity []Entity) {
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select("id", "title", "content", "created").From("entity").Limit(10).Offset(0)
sql := qb.String()
ormer := orm.NewOrm()
ormer.Raw(sql).QueryRows(&entity)
logs.Logger.Info("results:", entity)
return entity
}
func ReadTags(limit int, offset int) (books []Book) {
qb, _ := orm.NewQueryBuilder(driverName)
qb.Select("NAME", "TAGS").From(tableName).OrderBy("ID").Limit(limit).Offset(offset)
qbStr := qb.String()
ormer := orm.NewOrm()
ormer.Raw(qbStr).QueryRows(&books)
logs.Logger.Info("results:", books)
return books
}
// 统计分类总数
func GetClassifyCount() (classifyCount []*ClassifyCount, err error) {
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select("t.class_i_d, count(1) num", "c.title").From("topic t").LeftJoin("classify c").On("t.class_i_d = c.id").Where("t.class_i_d in (c.id)").GroupBy("t.class_i_d").OrderBy("num desc")
sql := qb.String()
o := orm.NewOrm()
_, err = o.Raw(sql).QueryRows(&classifyCount)
return classifyCount, err
}
func QueryUserById(userId int64) *POIUser {
var user *POIUser
qb, _ := orm.NewQueryBuilder(libs.GetDBDriverName())
qb.Select("id,nickname,avatar,gender,access_right,status,balance,phone,last_login_time").From("users").Where("id = ?")
sql := qb.String()
o := orm.NewOrm()
err := o.Raw(sql, userId).QueryRow(&user)
if err != nil {
return nil
}
return user
}
func QueryUsers(start, pageCount int) POIUsers {
users := make(POIUsers, 0)
qb, _ := orm.NewQueryBuilder(libs.GetDBDriverName())
qb.Select("id,nickname,avatar,gender,access_right,status,balance,phone,last_login_time").
From("users").OrderBy("id").Desc().Limit(pageCount).Offset(start)
sql := qb.String()
o := orm.NewOrm()
_, err := o.Raw(sql).QueryRows(&users)
if err != nil {
return nil
}
return users
}
func QuerySessionById(sessionId int64) *POISession {
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder(utils.DB_TYPE)
qb.Select("id,order_id, creator, tutor,create_time, plan_time, length, status, rating, comment, time_from, time_to").
From("sessions").Where("id = ?")
sql := qb.String()
session := POISession{}
err := o.Raw(sql, sessionId).QueryRow(&session)
if err != nil {
seelog.Error("sessionId:", sessionId, " ", err.Error())
return nil
}
session.Creator = QueryUserById(session.Created)
session.Teacher = QueryUserById(session.Tutor)
return &session
}
func (this *S2sActiveLog) QueryS2sActiveLog(campaignId string, guid string, startDate time.Time, endDate time.Time) ([]S2sActiveLog, int) {
var rows []S2sActiveLog
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select("*").
From("s2s_activeLog").
Where("createTime between ? and ?")
if campaignId != "" {
qb.And("offerId = ?")
}
if guid != "" {
qb.And("guid = ?")
}
qb.OrderBy("createTime").Desc().
Limit(1000)
// param := struct {
// CampaignId string
// Guid string
// StartDate time.Time
// EndDate time.Time
// } {
// CampaignId: campaignId,
// Guid: guid,
// StartDate: startDate,
// EndDate: endDate,
// }
var vals []interface{}
vals = append(vals, startDate)
vals = append(vals, endDate)
if campaignId != "" {
vals = append(vals, campaignId)
}
if guid != "" {
vals = append(vals, guid)
}
o.Raw(qb.String(), vals).QueryRows(&rows)
beego.Debug("Search s2s log count: ", len(rows))
return rows, len(rows)
}
func QuerySessions(start, pageCount int) POISessions {
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder(utils.DB_TYPE)
qb.Select("id,order_id, creator, tutor,create_time ,plan_time, length, status, rating, comment, time_from, time_to").
From("sessions").OrderBy("id").Desc().Limit(pageCount).Offset(start)
sql := qb.String()
sessions := make(POISessions, 0)
_, err := o.Raw(sql).QueryRows(&sessions)
if err != nil {
return nil
}
for _, session := range sessions {
session.Creator = QueryUserById(session.Created)
session.Teacher = QueryUserById(session.Tutor)
}
return sessions
}
func QueryOrderById(orderId int64) *POIOrder {
order := POIOrder{}
o := orm.NewOrm()
db, _ := orm.NewQueryBuilder(utils.DB_TYPE)
db.Select("id,creator,grade_id,subject_id,date,period_id,length,type,status,price_per_hour,real_price_per_hour,teacher_id").
From("orders").Where("id = ?")
sql := db.String()
err := o.Raw(sql, orderId).QueryRow(&order)
if err != nil {
seelog.Error("orderId:", orderId, " ", err.Error())
return nil
}
order.Type = OrderTypeRevDict[order.OrderType]
creator := QueryUserById(order.Created)
order.Creator = creator
return &order
}
func QueryOrders(start, pageCount int) POIOrders {
orders := make(POIOrders, 0)
o := orm.NewOrm()
db, _ := orm.NewQueryBuilder(utils.DB_TYPE)
db.Select("orders.id,orders.creator,orders.grade_id,orders.subject_id,orders.date,orders.period_id,orders.create_time," +
"orders.length,orders.type,orders.status,orders.price_per_hour,orders.real_price_per_hour,orders.teacher_id").
From("orders").InnerJoin("users").On("orders.creator = users.id").OrderBy("orders.id").Desc().Limit(pageCount).Offset(start)
sql := db.String()
_, err := o.Raw(sql).QueryRows(&orders)
if err != nil {
return nil
}
for _, order := range orders {
order.Type = OrderTypeRevDict[order.OrderType]
creator := QueryUserById(order.Created)
order.Creator = creator
}
return orders
}
func QueryOrderDispatchByOrderId(orderId int64) POIOrderDispatchs {
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder(utils.DB_TYPE)
qb.Select("id,order_id,teacher_id,dispatch_time,reply_time,plan_time,result").From("order_dispatch").
Where("order_id = ?")
sql := qb.String()
orderDispatchs := make(POIOrderDispatchs, 0)
_, err := o.Raw(sql, orderId).QueryRows(&orderDispatchs)
for _, orderDispatch := range orderDispatchs {
orderDispatch.Order = QueryOrderById(orderDispatch.OrderId)
orderDispatch.Teacher = QueryUserById(orderDispatch.TeacherId)
}
if err != nil {
seelog.Error("orderId:", orderId, " ", err.Error())
return nil
}
return orderDispatchs
}
func SearchTerminal(key string, limit int) *[]TerminalSearchModel {
var terminals []TerminalSearchModel
// 获取 QueryBuilder 对象. 需要指定数据库驱动参数。
// 第二个返回值是错误对象，在这里略过
qb, _ := orm.NewQueryBuilder("mysql")
// 构建查询对象
qb.Select(
"terminal.id terminal_id",
"terminal.offline_on",
"terminal.online_on",
"terminal_profile.*",
"terminal_carrier.*",
"terminal.terminal_sn",
).
From(
"terminal",
).
InnerJoin(
"terminal_profile",
).
On(
"terminal.terminal_profile_id = terminal_profile.id",
).
InnerJoin(
"terminal_carrier",
).
On(
"terminal.terminal_carrier_id = terminal_carrier.id",
).
Where("terminal.terminal_sn like ?").
Limit(limit).Offset(0)
// 导出SQL语句
sql := qb.String()
// 执行SQL语句
o := orm.NewOrm()
o.Raw(sql, key).QueryRows(&terminals)
return &terminals
}
func (this *PostController) ListPosts() {
page, err := strconv.Atoi(this.Ctx.Input.Param(":page"))
if err != nil {
beego.Error(err)
}
var posts []models.Post
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select("id", "title", "tag", "publish_at").
From("post").
OrderBy("publish_at").Desc().
Limit(PAGE_SIZE).Offset((page - 1) * PAGE_SIZE)
sql := qb.String()
o := orm.NewOrm()
o.Raw(sql).QueryRows(&posts)
this.Data["json"] = posts
this.ServeJson()
return
}
func (this *MainController) ListMessage() {
page, err := strconv.Atoi(this.Ctx.Input.Param(":page"))
if err != nil {
beego.Error(err)
}
var messages []models.Message
qb, _ := orm.NewQueryBuilder("mysql")
qb.Select("id", "guest_name", "reply", "content", "post_title", "created_at").
From("message").
OrderBy("created_at").Desc().
Limit(PAGE_SIZE + 10).Offset((page - 1) * PAGE_SIZE)
sql := qb.String()
o := orm.NewOrm()
o.Raw(sql).QueryRows(&messages)
this.Data["json"] = messages
this.ServeJson()
return
}
/**
groupFields might be:
1. nil,
2. ["0"]
3. ["1"]
4 ["0", "1"]
0 表示 PDB广告位
1 表示 日期
*/
func GetGroupedPmpDemandDailyReport(groupFields []string, medias []string, startDate time.Time, endDate time.Time, sortby string, order string,
offset int, limit int) (ml []PdbDemandReportVo, count int, err error) {
o := orm.NewOrm()
qb, _ := orm.NewQueryBuilder("mysql")
selectFields := []string{
"ddr.ad_date",
"pda.demand_id as demand_id",
"dpd.name as demand_name",
"pda.id as demand_adspace_id",
"pda.name as demand_adspace_name",
"pmp_media.id as pmp_media_id",
"pmp_media.name as pmp_media_name",
"pmp_adspace.id as pmp_adspace_id",
"pmp_adspace.name as pmp_adspace_name",
"sum(ddr.req_success) as req_success",
"sum(ddr.req_noad) as req_noad",
"sum(ddr.req_timeout) as req_timeout",
"sum(ddr.req_error) as req_error",
"sum(pdr.imp) as imp",
"sum(pdr.clk) as clk",
}
possibleGroupFields := map[string]string{
"0": "pda.demand_id",
"1": "pda.id",
"2": "pmp_media.id",
"3": "pmp_adspace.id",
}
groupby := "ddr.ad_date"
if groupFields != nil && len(groupFields) > 0 {
for _, fldIdx := range groupFields {
groupby += "," + possibleGroupFields[fldIdx]
}
}
qb.Select(strings.Join(selectFields, ", ")).
From("pmp_demand_daily_report ddr").
InnerJoin("pmp_daily_report pdr").On("ddr.demand_adspace_id=pdr.demand_adspace_id").
InnerJoin("pmp_adspace").On("pdr.pmp_adspace_id=pmp_adspace.id").
InnerJoin("pmp_media").On("pmp_adspace.media_id=pmp_media.id").
InnerJoin("pmp_demand_adspace pda").On("pda.id=ddr.demand_adspace_id").
InnerJoin("pmp_demand_platform_desk dpd").On("pda.demand_id=dpd.id")
qb.Where("1=1")
// if medias != nil {
// qb.And("pmp_media.id in (" + strings.Join(medias, ",") + ")")
// }
qb.And("ddr.ad_date >= ?")
qb.And("ddr.ad_date <= ?")
qb.GroupBy(groupby)
// order by:
if sortby != "" {
qb.OrderBy(sortby)
if order == "desc" {
qb.Desc()
} else {
qb.Asc()
}
}
// TODO default order by ???
qbCount, _ := orm.NewQueryBuilder("mysql")
qbCount.Select("count(*) as cnt").
From("(" + qb.String() + ") as sub")
var countResult []orm.Params
o.Raw(qbCount.String(), startDate, endDate).Values(&countResult)
count, _ = strconv.Atoi(countResult[0]["cnt"].(string))
qb.Limit(limit)
qb.Offset(offset)
report := []PdbDemandReportVo{}
o.Raw(qb.String(), startDate, endDate).QueryRows(&report)
return report, count, err
}
func NewQueryBuilder() (orm.QueryBuilder, error) {
result, err := orm.NewQueryBuilder(beego.AppConfig.String(DB_DRIVER))
return result, err
}