Beispiel #1
0
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
}
Beispiel #2
0
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
}
Beispiel #3
0
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
}
Beispiel #4
0
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)
}
Beispiel #5
0
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")
}
Beispiel #6
0
// 根据某个字查询统计字段值的求和
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
}
Beispiel #7
0
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
}
Beispiel #8
0
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
}
Beispiel #9
0
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
}
Beispiel #10
0
// 统计分类总数
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
}
Beispiel #14
0
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
}
Beispiel #19
0
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
}
Beispiel #20
0
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
}
Beispiel #21
0
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
}
Beispiel #23
0
func NewQueryBuilder() (orm.QueryBuilder, error) {
	result, err := orm.NewQueryBuilder(beego.AppConfig.String(DB_DRIVER))

	return result, err
}