public function builProductUrlRewrite($pid, $dorewrite = false)
{
//new url
$sql = "SELECT ea.attribute_code,cpei.value,cpev.attribute_id,cpev.value \n\t\t\t FROM {$this->tns["cpe"]} AS cpe\n\t\t\t JOIN {$this->tns["ea"]} as ea ON ea.attribute_code IN ('url_key','name')\n\t\t\t JOIN {$this->tns["cpev"]} as cpev ON cpev.entity_id=cpe.entity_id AND cpev.attribute_id=ea.attribute_id\n\t\t\t JOIN {$this->tns["cpei"]} as cpei ON cpei.entity_id=cpe.entity_id AND cpei.attribute_id=? AND cpei.value>1\n\t\t\t WHERE cpe.entity_id=?";
$result = $this->selectAll($sql, array($this->visinf["attribute_id"], $pid));
// nothing to build, product is not visible,return
if (count($result) == 0) {
return;
}
// see what we get as available product attributes
foreach ($result as $row) {
if ($row["attribute_code"] == "url_key") {
$pburlk = nullifempty($row["value"]);
}
if ($row["attribute_code"] == "name") {
$pname = $row["value"];
}
}
$baseurl = isset($pburlk) ? $pburlk : Slugger::slug($pname);
// if we've got an url key use it, otherwise , make a slug from the product name as url key
$urlend = $this->getParam("OTFI:useurlending", 1) == 1 ? $this->getParam("OTFI:urlending", ".html") : "";
//check duplicates
if (!isset($this->usedurls)) {
$this->usedurls = array();
$sql = "SELECT product_id,request_path as path FROM {$this->tns["curw"]}";
$data = $this->selectAll($sql);
foreach ($data as $line) {
$this->usedurls[$line['path']] = $line['product_id'];
}
unset($data);
}
$candidate = $baseurl . $urlend;
$index = 0;
while (isset($this->usedurls[$candidate]) && $this->usedurls[$candidate] != $pid) {
$index++;
$candidate = $baseurl . '-' . $index . $urlend;
}
$purlk = $candidate;
$this->usedurls[$candidate] = $pid;
if ($dorewrite) {
//rewrites SQL
$rewurlsql = "SELECT cpe.entity_id,cs.store_id,\n \t\t\t\t UUID() as id_path,\n \t\t\t\t curw.request_path as target_path,\n \t\t\t\t ? AS request_path,\n \t\t\t\t 0 as is_system,\n \t\t\t\t 'RP' as options\n \t\t\t\t FROM {$this->tns["cpe"]} as cpe\n \t\t\t\t JOIN {$this->tns["cpw"]} as cpw ON cpw.product_id=cpe.entity_id\n \t\t\t\t JOIN {$this->tns["cs"]} as cs ON cs.website_id=cpw.website_id\n \t\t\t\t JOIN {$this->tns["ccp"]} as ccp ON ccp.product_id=cpe.entity_id\n \t\t\t\t JOIN {$this->tns["cce"]} as cce ON ccp.category_id=cce.entity_id\n \t\t\t\t JOIN {$this->tns["curw"]} as curw ON curw.product_id=cpe.entity_id\n AND curw.store_id=cs.store_id AND curw.request_path!=?\n \t\t\t\t WHERE cpe.entity_id=?";
//read rewrites
$rewrites = $this->selectAll($rewurlsql, array($purlk, $purlk, $pid));
}
// delete old "system" url rewrite entries for product
$sql = "DELETE FROM {$this->tns["curw"]} WHERE product_id=? AND is_system=1";
$this->delete($sql, $pid);
// product url index info
$produrlsql = "SELECT cpe.entity_id,cs.store_id,\n\t\t\t\t CONCAT('product/',cpe.entity_id) as id_path,\n\t\t\t\t CONCAT('catalog/product/view/id/',cpe.entity_id) as target_path,\n\t\t\t\t ? AS request_path,\n\t\t\t\t 1 as is_system\n\t\t\t\t FROM {$this->tns["cpe"]} as cpe\n\t\t\t\t JOIN {$this->tns["cpw"]} as cpw ON cpw.product_id=cpe.entity_id\n\t\t\t\t JOIN {$this->tns["cs"]} as cs ON cs.website_id=cpw.website_id\n\t\t\t\t JOIN {$this->tns["ccp"]} as ccp ON ccp.product_id=cpe.entity_id\n\t\t\t\t JOIN {$this->tns["cce"]} as cce ON ccp.category_id=cce.entity_id\n\t\t\t\t WHERE cpe.entity_id=?";
// insert lines
$sqlprod = "INSERT INTO {$this->tns["curw"]} (product_id,store_id,id_path,target_path,request_path,is_system) {$produrlsql} ON DUPLICATE KEY UPDATE request_path=VALUES(`request_path`)";
$this->insert($sqlprod, array($purlk, $pid));
//insert rewrites
if ($dorewrite && count($rewrites) > 0) {
$fields = $this->arr2values(array_values($rewrites[0]));
$data = array();
$ins = array();
for ($i = 0; $i < count($rewrites); $i++) {
$data = array_merge($data, array_values($rewrites[$i]));
$ins[] = "({$fields})";
}
//insert rewrites
$sqlrew = "INSERT INTO {$this->tns["curw"]} (product_id,store_id,id_path,target_path,request_path,is_system,options) VALUES " . implode(",", $ins) . " ON DUPLICATE KEY UPDATE request_path=VALUES(`request_path`)";
$this->insert($sqlrew, $data);
unset($ins);
unset($data);
}
return $purlk;
}
public function builProductUrlRewrite($pid)
{
$sql = "SELECT ea.attribute_code,cpei.value,cpev.attribute_id,cpev.value \n\t\t\t FROM {$this->tns["cpe"]} AS cpe\n\t\t\t JOIN {$this->tns["ea"]} as ea ON ea.attribute_code IN ('url_key','name')\n\t\t\t JOIN {$this->tns["cpev"]} as cpev ON cpev.entity_id=cpe.entity_id AND cpev.attribute_id=ea.attribute_id\n\t\t\t JOIN {$this->tns["cpei"]} as cpei ON cpei.entity_id=cpe.entity_id AND cpei.attribute_id=? AND cpei.value>1\n\t\t\t WHERE cpe.entity_id=?";
$result = $this->selectAll($sql, array($this->visinf["attribute_id"], $pid));
//nothing to build, product is not visible,return
if (count($result) == 0) {
return;
}
//see what we get as available product attributes
foreach ($result as $row) {
if ($row["attribute_code"] == "url_key") {
$pburlk = nullifempty($row["value"]);
}
if ($row["attribute_code"] == "name") {
$pname = $row["value"];
}
}
//if we've got an url key use it, otherwise , make a slug from the product name as url key
$urlend = $this->getParam("OTFI:urlending", ".html");
$purlk = (isset($pburlk) ? $pburlk : Slugger::slug($pname)) . $urlend;
//delete old "system" url rewrite entries for product
$sql = "DELETE FROM {$this->tns["curw"]} WHERE product_id=? AND is_system=1";
$this->delete($sql, $pid);
//product url index info
$produrlsql = "SELECT cpe.entity_id,cs.store_id,\n\t\t\t\t CONCAT('product/',cpe.entity_id) as id_path,\n\t\t\t\t CONCAT('catalog/product/view/id/',cpe.entity_id) as target_path,\n\t\t\t\t ? AS request_path,\n\t\t\t\t 1 as is_system\n\t\t\t\t FROM {$this->tns["cpe"]} as cpe\n\t\t\t\t JOIN {$this->tns["cpw"]} as cpw ON cpw.product_id=cpe.entity_id\n\t\t\t\t JOIN {$this->tns["cs"]} as cs ON cs.website_id=cpw.website_id\n\t\t\t\t JOIN {$this->tns["ccp"]} as ccp ON ccp.product_id=cpe.entity_id\n\t\t\t\t JOIN {$this->tns["cce"]} as cce ON ccp.category_id=cce.entity_id\n\t\t\t\t WHERE cpe.entity_id=?";
//insert lines
$sqlprod = "INSERT INTO {$this->tns["curw"]} (product_id,store_id,id_path,target_path,request_path,is_system) {$produrlsql} ON DUPLICATE KEY UPDATE request_path=VALUES(`request_path`)";
$this->insert($sqlprod, array($purlk, $pid));
return $purlk;
}
