// Get media list
func GetMediaList(page int64, page_size int64, sort string, name string) (medias []PmpMedia, count int64) {
var offset int64
if page <= 1 {
offset = 0
} else {
offset = (page - 1) * page_size
}
o := orm.NewOrm()
var r orm.RawSeter
sql := "select m.id, m.name, m.description from pmp_media m where (m.del_flg is null or m.del_flg != 1) "
if name == "" && sort == "" {
sql += "limit ? offset ? "
r = o.Raw(sql, page_size, offset)
} else if name != "" && sort != "" {
name = "%" + name + "%"
sql += "and m.name like ? order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, name, page_size, offset)
} else if sort != "" {
sql += "order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, page_size, offset)
} else if name != "" {
name = "%" + name + "%"
sql += "and d.name like ? limit ? offset ?"
r = o.Raw(sql, name, page_size, offset)
}
num, err := r.QueryRows(&medias)
if err != nil {
fmt.Println(err)
return nil, 0
}
fmt.Println("medias nums: ", num)
return medias, num
}
// Get demand list
func GetDemandList(page int64, page_size int64, sort string, name string) (demands []PmpDemandPlatformDesk, count int64) {
var offset int64
if page <= 1 {
offset = 0
} else {
offset = (page - 1) * page_size
}
o := orm.NewOrm()
var r orm.RawSeter
sql := "select d.id, d.name, d.request_url_template, d.timeout from pmp_demand_platform_desk d where (d.del_flg is null or d.del_flg != 1) "
if name == "" && sort == "" {
sql += "limit ? offset ? "
r = o.Raw(sql, page_size, offset)
} else if name != "" && sort != "" {
name = "%" + name + "%"
sql += "and d.name like ? order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, name, page_size, offset)
} else if sort != "" {
sql += "order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, page_size, offset)
} else if name != "" {
name = "%" + name + "%"
sql += "and d.name like ? limit ? offset ?"
r = o.Raw(sql, name, page_size, offset)
}
num, err := r.QueryRows(&demands)
if err != nil {
fmt.Println(err)
return nil, 0
}
fmt.Println("demands nums: ", num)
return demands, num
}
// get demand adspace list by demand id , may filtered by adspace name
func GetDemandAdspaceListByDemandId(page int64, page_size int64, sort string, demandid int, adspacename string) (adspaceVos []DemandAdspaceVo, count int64) {
var sql = "SELECT demandadspace.*, demand.name as demand_name, demand.id as demand_id FROM pmp_demand_adspace demandadspace INNER JOIN pmp_demand_platform_desk demand on demandadspace.demand_id = demand.id WHERE demand_id=? AND demandadspace.del_flg != 1 "
var offset int64
if page <= 1 {
offset = 0
} else {
offset = (page - 1) * page_size
}
o := orm.NewOrm()
var r orm.RawSeter
if adspacename == "" {
if sort == "" {
sort = "ORDER BY demandadspace.id ASC limit ? offset ?"
} else {
sort = "ORDER BY " + sort + " " + "limit ? offset ?"
}
sql = sql + sort
r = o.Raw(sql, demandid, page_size, offset)
} else {
sql = sql + "and demandadspace.name like ? "
if sort == "" {
sort = "ORDER BY demandadspace.id ASC limit ? offset ?"
} else {
sort = "ORDER BY " + sort + " " + "limit ? offset ?"
}
sql = sql + sort
adspacename = "%" + adspacename + "%"
r = o.Raw(sql, demandid, adspacename, page_size, offset)
}
count, err := r.QueryRows(&adspaceVos)
if err == nil {
return adspaceVos, count
} else {
return nil, 0
}
}
// get the all the demands information to map to a specified adspace
func GetDemandsMappingInfo(page int64, page_size int64, sort string, name string, adspaceid int, demandid int) (v []DemandMappingVo, err error) {
o := orm.NewOrm()
var offset int64
if page <= 1 {
offset = 0
} else {
offset = (page - 1) * page_size
}
var r orm.RawSeter
sql := "SELECT demandadspace.id, demandadspace.name, d.name as demand_name, d.id as demand_id, CASE WHEN matrix.mapped_adspace_id IS NOT NULL THEN 1 ELSE 0 END AS ck, matrix.mapped_adspace_id, matrix.mapped_adspace_name FROM pmp_demand_platform_desk d inner join pmp_demand_adspace demandadspace on d.id=demandadspace.demand_id left join (SELECT m.demand_adspace_id, m.pmp_adspace_id as mapped_adspace_id, a.name as mapped_adspace_name, m.demand_id FROM pmp_adspace_matrix m INNER JOIN pmp_adspace a on m.pmp_adspace_id = a.id WHERE m.pmp_adspace_id=?) as matrix on demandadspace.id = matrix.demand_adspace_id WHERE (d.del_flg is null OR d.del_flg != 1) AND (demandadspace.del_flg is null OR demandadspace.del_flg != 1) "
if demandid > 0 {
name = "%" + name + "%"
sql = sql + "AND d.id=? AND d.name LIKE ?"
r = o.Raw(sql, adspaceid, demandid, name)
} else {
if name == "" && sort == "" {
if page > 0 {
sql += "limit ? offset ? "
r = o.Raw(sql, adspaceid, page_size, offset)
} else {
r = o.Raw(sql, adspaceid)
}
} else if name != "" && sort != "" {
name = "%" + name + "%"
if page > 0 {
sql += "and d.name like ? order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, adspaceid, name, page_size, offset)
} else {
sql += "and d.name like ? order by " + sort
r = o.Raw(sql, adspaceid, name)
}
} else if sort != "" {
if page > 0 {
sql += "order by " + sort + " " + "limit ? offset ?"
r = o.Raw(sql, adspaceid, page_size, offset)
} else {
sql += "order by " + sort
r = o.Raw(sql, adspaceid)
}
} else if name != "" {
if page > 0 {
name = "%" + name + "%"
sql += "and d.name like ? limit ? offset ?"
r = o.Raw(sql, adspaceid, name, page_size, offset)
} else {
name = "%" + name + "%"
sql += "and d.name like ? "
r = o.Raw(sql, adspaceid, name)
}
}
}
_, err = r.QueryRows(&v)
if err != nil {
return nil, err
}
p := PmpAdspace{Id: adspaceid}
o.Read(&p)
for index, _ := range v {
v[index].MappedAdspaceId = p.Id
v[index].MappedAdspaceName = p.Name
}
return v, nil
}