// 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
}
func initCron() {
tk1 := toolbox.NewTask("deleteOldPaste", "0 0 * * * *", func() error {
o := orm.NewOrm()
var r orm.RawSeter
r = o.Raw("DELETE FROM paste WHERE Timestamp != 0 AND Timestamp < " + strconv.Itoa(int(time.Now().Unix())))
res, err := r.Exec()
fmt.Println("Deleting old pasts")
if err == nil {
num, _ := res.RowsAffected()
fmt.Println("Affected Rows", num)
} else {
fmt.Println("Database Error: ", err)
}
return nil
})
toolbox.AddTask("deleteOldPaste", tk1)
toolbox.StartTask()
}
// 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 adspace list, may filtered by adspace name
func GetAdspaceList(page int64, page_size int64, sort string, mediaid int, adspacename string) (adspaceVos []AdspaceVo, count int64) {
var offset int64
if page <= 1 {
offset = 0
} else {
offset = (page - 1) * page_size
}
var maps []orm.Params
o := orm.NewOrm()
var r orm.RawSeter
var query string = "SELECT a.id, a.name, a.media_id, a.description, a.pmp_adspace_key, m.name as media, a.est_daily_imp, a.est_daily_clk, a.est_daily_ctr FROM pmp_adspace a, pmp_media m WHERE a.media_id = m.id AND a.del_flg != 1 "
var adspacenamecon string = "%" + adspacename + "%"
if mediaid == -1 && adspacename == "" {
query = query + "ORDER BY a.id ASC limit ? offset ?"
r = o.Raw(query, page_size, offset)
} else if mediaid != -1 && adspacename != "" {
query = query + "AND m.id=? AND a.name like ? ORDER BY a.id ASC limit ? offset ?"
r = o.Raw(query, mediaid, adspacenamecon, page_size, offset)
} else if mediaid != -1 {
query = query + "AND m.id=? ORDER BY a.id ASC limit ? offset ?"
r = o.Raw(query, mediaid, page_size, offset)
} else {
query = query + "AND a.name like ? ORDER BY a.id ASC limit ? offset ?"
r = o.Raw(query, adspacenamecon, page_size, offset)
}
num, err := r.Values(&maps)
if err == nil {
fmt.Println("adspace nums: ", num)
} else {
fmt.Println(err)
return nil, 0
}
if maps == nil || len(maps) == 0 {
fmt.Println("query return with no rows. ")
return nil, 0
}
fmt.Println(maps)
var impstr, clkstr, ctrstr, descstr string
var idint, mediaidint int
var namestr, mediastr, pmpAdspaceKeystr string
for index := 0; index < len(maps); index++ {
imp := maps[index]["est_daily_imp"]
clk := maps[index]["est_daily_clk"]
ctr := maps[index]["est_daily_ctr"]
if impv, ok := imp.(string); ok {
impstr = impv
}
if clkv, ok := clk.(string); ok {
clkstr = clkv
}
if ctrv, ok := ctr.(string); ok {
ctrstr = ctrv
}
est := impstr + "," + clkstr + "," + ctrstr
// fmt.Println("**********" + est + "**********")
if idv, ok := maps[index]["id"].(string); ok {
idint, _ = strconv.Atoi(idv)
}
if idv, ok := maps[index]["media_id"].(string); ok {
mediaidint, _ = strconv.Atoi(idv)
}
if namev, ok := maps[index]["name"].(string); ok {
namestr = namev
}
if mediav, ok := maps[index]["media"].(string); ok {
mediastr = mediav
}
if descv, ok := maps[index]["description"].(string); ok {
descstr = descv
}
if v, ok := maps[index]["pmp_adspace_key"].(string); ok {
pmpAdspaceKeystr = v
}
adspaceVos = append(adspaceVos, AdspaceVo{Id: idint, Name: namestr, MediaName: mediastr, EstDaily: est, MediaId: mediaidint, Description: descstr, PmpAdspaceKey: pmpAdspaceKeystr})
}
return adspaceVos, int64(len(adspaceVos))
}
// 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
}
