function LoadFromDB() { $s = new SQLSelect(); $perPage = get_setting("prodlistprods"); $this->page = (int)max( 1, (int)$_GET["page"] ); $s = new BM_Query("prods"); if (is_array($_GET["type"])) { $cond = array(); foreach($_GET["type"] as $type) $cond[] = sprintf_esc("FIND_IN_SET('%s',prods.type)",$type); $s->AddWhere(implode(" OR ",$cond)); } if (is_array($_GET["platform"])) { global $PLATFORMS; $platforms = array(); foreach($_GET["platform"] as $platform) foreach($PLATFORMS as $k=>$v) if ($v["name"] == $platform) $platforms[] = $k; if ($platforms) { $s->AddJoin("LEFT","prods_platforms as pp","pp.prod = prods.id"); $s->AddWhere(sprintf_esc("pp.platform in (%s)",implode(",",$platforms))); } } $dir = "DESC"; if ($_GET["reverse"]) $dir = "ASC"; switch($_GET["order"]) { case "type": $s->AddOrder("prods.type ".$dir); break; case "name": $s->AddOrder("prods.name ".$dir); break; case "group": $s->AddOrder("prods.group1 ".$dir); $s->AddOrder("prods.group2 ".$dir); $s->AddOrder("prods.group3 ".$dir); break; case "party": $s->AddOrder("prods_party.name ".$dir); $s->AddOrder("prods.party_year ".$dir); $s->AddOrder("prods.party_place ".$dir); break; case "thumbup": $s->AddOrder("prods.voteup ".$dir); break; case "thumbpig": $s->AddOrder("prods.votepig ".$dir); break; case "thumbdown": $s->AddOrder("prods.votedown ".$dir); break; case "avg": $s->AddOrder("prods.voteavg ".$dir); break; case "views": $s->AddOrder("prods.views ".$dir); break; case "added": $s->AddOrder("prods.addedDate ".$dir); break; } $s->AddOrder("prods.releaseDate ".$dir); $s->AddOrder("prods.addedDate ".$dir); $s->SetLimit( $perPage, (int)(($this->page-1) * $perPage) ); //echo $s->GetQuery(); $this->prods = $s->performWithCalcRows( $this->count ); PouetCollectPlatforms($this->prods); PouetCollectAwards($this->prods); }
function LoadFromDB() { $s = new BM_Query("parties"); $s->AddField("count(*) as c"); $s->AddField("prods.party_year"); $s->AddJoin("","prods","prods.party=parties.id"); $s->AddWhere(sprintf_esc("parties.id != %d",NO_PARTY_ID)); //$s->AddWhere(sprintf_esc("prods.id is not null"); $s->AddGroup("prods.party,prods.party_year"); $s->AddOrder("prods.releaseDate desc, prods.id desc"); $s->SetLimit(POUET_CACHE_MAX); $this->data = $s->perform(); //PouetCollectPlatforms($this->data); }
function LoadFromDB() { $s = new SQLSelect(); $this->group = PouetGroup::Spawn($this->id); $this->addeduser = PouetUser::Spawn($this->group->addedUser); // not to boast or anything, but this is f*****g beautiful. $sub = new SQLSelect(); $sub->AddField("max(comments.addedDate) as maxDate"); $sub->AddField("comments.which"); $sub->AddTable("comments"); $sub->AddJoin("left","prods","prods.id = comments.which"); //$sub->AddOrder("comments.addedDate desc"); $sub->AddGroup("comments.which"); $sub->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id)); $s = new BM_Query("prods"); $s->AddField("cmts.addedDate as lastcomment"); $s->AddField("cmts.rating as lastcommentrating"); $s->AddJoin("left","(select comments.addedDate,comments.who,comments.which,comments.rating from (".$sub->GetQuery().") as dummy left join comments on dummy.maxDate = comments.addedDate and dummy.which = comments.which) as cmts","cmts.which=prods.id"); $s->attach(array("cmts"=>"who"),array("users as user"=>"id")); $s->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id)); $r = !!$_GET["reverse"]; switch($_GET["order"]) { case "type": $s->AddOrder("prods.type ".($r?"DESC":"ASC")); break; case "party": $s->AddOrder("prods_party.name ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_year ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_place ".($r?"DESC":"ASC")); break; case "release": $s->AddOrder("prods.releaseDate ".($r?"ASC":"DESC")); break; case "thumbup": $s->AddOrder("prods.voteup ".($r?"ASC":"DESC")); break; case "thumbpig": $s->AddOrder("prods.votepig ".($r?"ASC":"DESC")); break; case "thumbdown": $s->AddOrder("prods.votedown ".($r?"ASC":"DESC")); break; case "avg": $s->AddOrder("prods.voteavg ".($r?"ASC":"DESC")); break; case "views": $s->AddOrder("prods.views ".($r?"ASC":"DESC")); break; case "latestcomment": $s->AddOrder("lastcomment ".($r?"ASC":"DESC")); break; default: $s->AddOrder("prods.name ".($r?"DESC":"ASC")); break; } $this->prods = $s->perform(); PouetCollectPlatforms($this->prods); PouetCollectAwards($this->prods); $s = new BM_Query("affiliatedboards"); $s->attach(array("affiliatedboards"=>"board"),array("boards as board"=>"id")); $s->AddWhere(sprintf_esc("affiliatedboards.group=%d",$this->id)); $this->affil = $s->perform(); }
function LoadFromDB() { global $currentUser; if (!$currentUser) return; $ids = SQLLib::SelectRows(sprintf_esc("select prodID from watchlist where userID = %d",$currentUser->id)); if (!count($ids)) return; $i = array(); foreach($ids as $v) $i[] = $v->prodID; $s = new BM_Query(); //$s->AddTable(sprintf_esc("(select * from comments where comments.which in (%s) order by comments.addedDate desc) as c ",implode(",",$i))); $s->AddTable(sprintf_esc("(select *, max(comments.addedDate) as maxDate from comments where comments.which in (%s) group by comments.which) as c ",implode(",",$i))); $s->AddJoin("left","comments","c.maxDate = comments.addedDate and c.which = comments.which"); $s->attach(array("comments"=>"which"),array("prods as prod"=>"id")); $s->attach(array("comments"=>"who"),array("users as user"=>"id")); //$s->AddGroup("c.which"); $s->AddOrder("comments.addedDate desc"); $s->AddField("comments.id as commentID"); $s->SetLimit((int)$this->limit); $this->data = $s->perform(); }
$s->attach("addedUser",array("users as user"=>"id")); $s->SetLimit($limit); if ($_GET["type"]) { $s->AddWhere(sprintf_esc("FIND_IN_SET('%s',prods.type)",$_GET["type"])); } if ($_GET["platform"]) { $platformID = -1; foreach($PLATFORMS as $k=>$v) if ($v["name"] == $_GET["platform"]) $platformID = $k; if ($platformID != -1) { $s->AddJoin("LEFT","prods_platforms as pp","pp.prod = prods.id"); $s->AddWhere(sprintf_esc("pp.platform = %d",$platformID)); } } $data = $s->perform(); PouetCollectPlatforms($data); $rss = new PouetRSS(); foreach($data as $item) { $rss->AddItem(array( "title" => $item->name . ($item->groups ? " by ".$item->RenderGroupsPlain() : ""), "pouet:title" => $item->name, "pouet:group" => array_map(function($i){ return $i->name; },$item->groups),
function LoadFromDB() { $this->party = PouetParty::spawn($_GET["which"]); if (!$this->party) return; $this->party->addeduser = PouetUser::spawn( $this->party->addedUser ); if (isset($_GET["when"])) { $this->year = $_GET["when"]; } else { $r = SQLLib::selectRow(sprintf_esc("select party_year from prods where party = %d order by rand() limit 1",$_GET["which"])); $this->year = $r->party_year; } if ($this->year < 100) { $this->year += ($this->year < 50 ? 2000 : 1900); } $this->prods = array(); $s = new BM_Query("prods"); $s->AddWhere( sprintf_esc("(prods.party = %d AND prods.party_year = %d) or (prodotherparty.party = %d AND prodotherparty.party_year = %d)",$this->party->id,$this->year,$this->party->id,$this->year) ); // this is where it gets nasty; luckily we can fake it relatively elegantly: ORM won't notice if we override some of the field selections $s->AddJoin("left","prodotherparty",sprintf_esc("prodotherparty.prod = prods.id and (prodotherparty.party = %d AND prodotherparty.party_year = %d)",$this->party->id,$this->year)); foreach($s->fields as &$v) { if ($v == "prods.party_compo as prods_party_compo") { $v = "COALESCE(prodotherparty.party_compo,prods.party_compo) as prods_party_compo"; } if ($v == "prods.party_place as prods_party_place") { $v = "COALESCE(prodotherparty.party_place,prods.party_place) as prods_party_place"; } } $dir = "DESC"; if ($_GET["reverse"]) $dir = "ASC"; $this->sortByCompo = false; switch($_GET["order"]) { case "type": $s->AddOrder("prods.type ".$dir); break; case "name": $s->AddOrder("prods.name ".$dir); break; case "group": $s->AddOrder("prods.group1 ".$dir); $s->AddOrder("prods.group2 ".$dir); $s->AddOrder("prods.group3 ".$dir); break; case "party": $s->AddOrder("prods_party.name ".$dir); $s->AddOrder("prods.party_year ".$dir); $s->AddOrder("prods.party_place ".$dir); break; case "thumbup": $s->AddOrder("prods.voteup ".$dir); break; case "thumbpig": $s->AddOrder("prods.votepig ".$dir); break; case "thumbdown": $s->AddOrder("prods.votedown ".$dir); break; case "avg": $s->AddOrder("prods.voteavg ".$dir); break; case "views": $s->AddOrder("prods.views ".$dir); break; default: { $s->AddOrder( "COALESCE(prodotherparty.party_compo,prods.party_compo)" ); $s->AddOrder( "COALESCE(prodotherparty.party_place,prods.party_place)" ); $this->sortByCompo = true; // include invitations on top $inv = new BM_Query("prods"); $inv->AddWhere( sprintf_esc("(prods.invitation = %d AND prods.invitationyear = %d)",$this->party->id,$this->year,$this->party->id,$this->year) ); $inv->AddOrder( "prods.addedDate" ); $prods = $inv->perform(); foreach($prods as &$v) { $v->party_compo = 1; // invit unset($v->placings); } $this->prods = array_merge( $this->prods, $prods ); } break; } $prods = $s->perform(); $this->prods = array_merge( $this->prods, $prods ); PouetCollectPlatforms($this->prods); PouetCollectAwards($this->prods); }
function LoadFromDB() { global $PLATFORMS; $plat = array(); $plat[""] = "- none -"; foreach($PLATFORMS as $k=>$v) $plat[$k] = $v["name"]; uasort($plat,"strcasecmp"); $this->fields = array( "type"=>array( "name"=>"type", "type"=>"select", //"multiple"=>true, "assoc"=>true, "fields"=>$this->types, "info"=>" ", //"required"=>true, ), "platform"=>array( "name"=>"platform", "type"=>"select", //"multiple"=>true, "assoc"=>true, "fields"=>$plat, "info"=>" ", //"required"=>true, ), "days"=>array( "name"=>"days to go back", "type"=>"number", "value"=>0, "info"=>"0 means alltime", ), "limit"=>array( "name"=>"number of prods", "type"=>"number", "value"=>10, "max"=>50, ), ); if ($_GET) { foreach($_GET as $k=>$v) if ($this->fields[$k]) $this->fields[$k]["value"] = $v; } $s = new BM_Query("prods"); if ($_GET["days"]) { $s->AddOrder("(prods.views/((NOW()-prods.addedDate)/100000)+prods.views)*prods.voteavg*prods.voteup DESC"); $s->AddWhere(sprintf_esc("prods.addedDate > DATE_SUB(NOW(),INTERVAL %d DAY)",$_GET["days"])); } else if ($_GET["dateFrom"] || $_GET["dateTo"]) { $s->AddOrder("(prods.views/((NOW()-prods.addedDate)/100000)+prods.views)*prods.voteavg*prods.voteup DESC"); if ($_GET["dateFrom"]) $s->AddWhere(sprintf_esc("prods.addedDate >= '%s'",$_GET["dateFrom"])); if ($_GET["dateTo"]) $s->AddWhere(sprintf_esc("prods.addedDate <= '%s'",$_GET["dateTo"])); } else { $s->AddOrder("prods.rank"); $s->AddWhere("prods.rank > 0"); } if ($_GET["type"]) { $s->AddWhere(sprintf_esc("FIND_IN_SET('%s',prods.type)>0",$_GET["type"])); } if ($_GET["platform"]) { $s->AddJoin("","prods_platforms",sprintf_esc("prods_platforms.prod = prods.id AND prods_platforms.platform=%d",$_GET["platform"])); } $limit = (int)($_GET["limit"] ? $_GET["limit"] : 10); $limit = min($limit,50); $limit = max($limit,10); $s->SetLimit($limit); $this->prods = $s->perform(); }