function ResortTable($Table, $ByField = false, $Where = false, $Direction = false) { if (!$Table) { return false; } global $Db; if (!$ByField) { $ByField = "POSITION"; } if ($Where) { $Where = "WHERE " . $Where; } if ($Direction == 1) { $Query = "SELECT MAX({$ByField}) AS MAX_POS FROM {$Table}"; $Check = $Db->Select($Query); $MaxPos = $Check->MAX_POS + 1; } $Query = "SELECT ID, {$ByField} FROM {$Table} {$Where} ORDER BY {$ByField} ASC"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { if ($Row->{$ByField} == 0 && $Direction == 1 && $MaxPos) { $Query = "UPDATE {$Table} SET {$ByField} = {$MaxPos} WHERE ID = " . $Row->ID; $Db->Query($Query); } if ($Row->{$ByField} != $Sql->Position + 1) { if ($Direction == 1 && $Row->{$ByField} == 0) { continue; } $Query = "UPDATE {$Table} SET {$ByField} = " . ($Sql->Position + 1) . " WHERE ID = " . $Row->ID . ""; $Db->Query($Query); } } }
function SaveSplitToFile($Id = false, $Filename, $SplitId = false) { global $Db; if (!$Id && !$SplitId) { return false; } $UseStore = $Db->ReturnValue("SELECT USE_STORE FROM " . PFX . "_tracker_config WHERE COMPANY_ID=0"); if (!$UseStore) { return true; } if (!$SplitId) { $SplitId = $Db->ReturnValue("SELECT ID FROM " . PFX . "_tracker_split_test WHERE SUB_ID={$Id}"); } $Query = "\n\t\tSELECT SP.FULL_PATH AS PAGE_PATH\n\t\t\tFROM " . PFX . "_tracker_split_page SP\n\t\t\tINNER JOIN " . PFX . "_tracker_site_page SPA\n\t\t\t\tON SPA.ID=SP.PAGE_ID\n\t\t\tINNER JOIN " . PFX . "_tracker_site S\n\t\t\t\tON S.ID=SPA.SITE_ID\n\t\t\tLEFT JOIN " . PFX . "_tracker_query Q\n\t\t\t\tON Q.ID=SP.QUERY_ID\n\t\t\tWHERE SP.SPLIT_ID={$SplitId}\n\t\t"; $Sql = new Query($Query, "ARR"); $PageArr = array(); while ($Row = $Sql->Row()) { if (!ValidVar($Row['HOST'])) { continue; } if (!$Row['PAGE_PATH']) { $Row['PAGE_PATH'] = "http://" . $Row['HOST'] . $Row['PATH']; if ($Row['QUERY_STRING']) { $Row['PAGE_PATH'] .= "?" . $Row['QUERY_STRING']; } } $PageArr[] = $Row['PAGE_PATH']; } if (!ValidArr($PageArr) || count($PageArr) < 1) { return false; } @clearstatcache(); $f = @fopen("store/{$Filename}", "a+"); if (!@is_writable("store/{$Filename}")) { return false; } if (!$f) { return false; } $Data = @fread($f, @filesize("store/{$Filename}")); if (!$Data) { $DataArr[$SplitId] = $PageArr; } else { $DataArr = @unserialize($Data); if (!ValidArr($DataArr)) { $DataArr = array(); } $DataArr[$SplitId] = $PageArr; } $Data = @serialize($DataArr); @flock($f, LOCK_EX); @ftruncate($f, 0); if (!@fwrite($f, $Data)) { return false; } @flock($f, LOCK_UN); @fclose($f); return true; }
/** * Returns a specific column * * @param $array * * @param $value * * @return null */ public static function SpecificColumn($array, $value) { $array = Query::Row($array); if (isset($array[$value])) { return $array[$value]; } return null; }
function ReturnOn($EventName) { global $Db, $nsProduct; $Query = "SELECT * FROM " . PFX . "_system_plugin WHERE EVENT_NAME = '{$EventName}' AND PRODUCT_ID=" . $nsProduct->ID . " ORDER BY ORD"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $fn = $this->Path($Row->DIRNAME, $EventName); if (!file_exists($fn)) { return false; } } $this->LastEvent = $EventName; return $fn; }
function GetSiteIds($CpId) { $Arr = array(); $Str = ""; $Query = "SELECT ID FROM " . PFX . "_tracker_site WHERE COMPANY_ID = {$CpId}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Arr[] = $Row->ID; } if (count($Arr) == 0) { return false; } $Str = implode(",", $Arr); return $Str; }
function GetPiecesList() { global $Get, $CurrentCompany, $Lang, $nsUser; $InCampArr = array(); $Query = "\n\t\tSELECT \n\t\t\tTCP.*,\n\t\t\tTST.ID AS SPLIT_TEST\n\t\t\tFROM " . PFX . "_tracker_camp_piece TCP\n\t\t\tINNER JOIN " . PFX . "_tracker_split_test TST\n\t\t\t\tON TST.SUB_ID=TCP.ID\n\t\t\tWHERE TST.COMPANY_ID=" . $CurrentCompany->ID . "\n\t\t\tORDER BY TCP.NAME\n\t"; $Sql = new Query($Query); $Sql->ReadSkinConfig(); while ($Row = $Sql->Row()) { if (!$Row->COMPANY_ID) { continue; } $Row->NAME = stripslashes($Row->NAME); $Row->DESCRIPTION = stripslashes($Row->DESCRIPTION); $Row->_EDITLINK = getURL("split_test", "EditId=" . $Row->ID); $Row->_CODELINK = getURL("campaign_link", "SplitId=" . $Row->ID); $Row->_DELETELINK = getURL("split_test", "DeleteId=" . $Row->ID); //$Row->_TYPE=$Lang['SplitTest']; $Row->_STAT_LINK = getURL("split_test", "SplitId=" . $Row->ID, "report"); $Row->Report = new SplitStat_v2(); $Row->Report->SplitId = $Row->ID; $Row->Report->CpId = $Row->COMPANY_ID; $Row->Report->DisableAll(); if ($nsUser->Columns->CLICKS) { $Row->Report->ShowVisitors = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowActionConv = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowSaleConv = true; } $Row->Report->Calculate(); $Row->SplitStat =& $Row->Report->SplitStat; $Row->_STYLE = $Sql->_STYLE; $InCampArr[$Sql->Position] = $Row; $PrevRow =& $InCampArr[$Sql->Position]; } if (count($InCampArr) > 0) { return $InCampArr; } else { return false; } }
function GetPiecesList() { global $Get, $CampId, $Lang, $nsUser; $InCampArr = array(); $Query = "\n\t\tSELECT \n\t\t\tTCP.*,\n\t\t\tTSC.ID AS SUB_CAMP,\n\t\t\tTST.ID AS SPLIT_TEST\n\t\t\tFROM " . PFX . "_tracker_camp_piece TCP\n\t\t\t\tLEFT JOIN " . PFX . "_tracker_sub_campaign TSC\n\t\t\t\t\tON TSC.SUB_ID=TCP.ID\n\t\t\t\tLEFT JOIN " . PFX . "_tracker_split_test TST\n\t\t\t\t\tON TST.SUB_ID=TCP.ID\n\t\t\tWHERE CAMPAIGN_ID={$CampId} \n\t\t\tORDER BY TCP.NAME\n\t"; $Sql = new Query($Query); $Sql->ReadSkinConfig(); $i = 0; while ($Row = $Sql->Row()) { $Row->NAME = stripslashes($Row->NAME); $Row->DESCRIPTION = stripslashes($Row->DESCRIPTION); if ($Row->SUB_CAMP) { $Row->_EDITLINK = getURL("sub_camp", "EditId=" . $Row->ID); $Row->_CODELINK = getURL("campaign_link", "CampId=" . $Row->ID); $Row->_DELETELINK = getURL("sub_camp", "GrpId={$CampId}&DeleteId=" . $Row->ID); $Row->_TYPE = $Lang['Campaign']; $Row->_STAT_LINK = getURL("paid_constructor", "CampId=" . $Row->ID, "report"); $Row->Report = new Paid_v2(); $Row->Report->CampId = $Row->ID; $Row->Report->CpId = $Row->COMPANY_ID; $Row->Report->ShowPerClick = true; $Row->Report->ShowTotalCost = true; $Row->Report->DisableAll(); if ($nsUser->Columns->ROI) { $Row->Report->ShowROI = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowActionConv = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowSaleConv = true; } $Row->Report->Calculate(); $Row->CampStat =& $Row->Report->CampStat; } if ($Row->SPLIT_TEST) { $Row->_EDITLINK = getURL("split_test", "EditId=" . $Row->ID); $Row->_CODELINK = getURL("campaign_link", "SplitId=" . $Row->ID); $Row->_DELETELINK = getURL("split_test", "GrpId={$CampId}&DeleteId=" . $Row->ID); $Row->_TYPE = $Lang['SplitTest']; $Row->_STAT_LINK = getURL("split_test", "SplitId=" . $Row->ID, "report"); $Row->Report = new SplitStat_v2(); $Row->Report->SplitId = $Row->ID; $Row->Report->CpId = $Row->COMPANY_ID; $Row->Report->DisableAll(); if ($nsUser->Columns->CLICKS) { $Row->Report->ShowVisitors = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowActionConv = true; } if ($nsUser->Columns->CONVERSIONS) { $Row->Report->ShowSaleConv = true; } $Row->Report->Calculate(); $Row->SplitStat =& $Row->Report->SplitStat; } if (!$Row->SUB_CAMP && !$Row->SPLIT_TEST) { continue; } $Row->_STYLE = $Sql->_STYLE; $InCampArr[$i] = $Row; $PrevRow =& $InCampArr[$i]; $i++; } $PrevRow->_DOWN = false; if (count($InCampArr) > 0) { return $InCampArr; } else { return false; } }
function DeleteCompany($Id, $NoRedir = false) { global $Db, $nsProduct, $Logs, $Lang; $Users = array(); $Query = "SELECT USER_ID FROM " . PFX . "_tracker_user WHERE COMPANY_ID={$Id}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Users[] = $Row->USER_ID; } for ($i = 0; $i < count($Users); $i++) { $Query = "DELETE FROM " . PFX . "_system_user WHERE ID = " . $Users[$i]; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_user WHERE USER_ID = " . $Users[$i]; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_user_column WHERE USER_ID = " . $Users[$i]; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_user_settings WHERE USER_ID = " . $Users[$i]; $Db->Query($Query); } $Query = "SELECT ID FROM " . PFX . "_tracker_site WHERE COMPANY_ID={$Id}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { DeleteHost($Id, $Row->ID, false, true); } DeleteCompanyActions($Id); DeleteCompanySaleItems($Id); $Query = "SELECT ID FROM " . PFX . "_tracker_campaign WHERE PARENT_ID=0 AND COMPANY_ID={$Id}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { DeleteCampaign($Row->ID, true); } $Query = "DELETE FROM " . PFX . "_tracker_config WHERE COMPANY_ID={$Id}"; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_const_group WHERE COMPANY_ID={$Id}"; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_user_report WHERE COMPANY_ID={$Id}"; $Db->Query($Query); $Query = "DELETE FROM " . PFX . "_tracker_watch WHERE COMPANY_ID={$Id}"; $Db->Query($Query); DropStatTables($Id); $Query = "DELETE FROM " . PFX . "_tracker_client WHERE ID = {$Id}"; $Db->Query($Query); if (!$NoRedir) { $nsProduct->Redir("company", "RDlt=1"); } }
function GetStat() { global $Logs; $StatArr = array(); $GrpItems = array(); $GrpList = ""; $SelectStr = ""; $JoinStr = ""; $OrderStr = ""; $GroupStr = ""; $WhereStr = ""; $StraightStr = ""; $LimitStr = ""; if (count($this->SelectArr) > 0) { $SelectStr = implode(", ", $this->SelectArr) . ", "; } if (count($this->JoinArr) > 0) { $JoinStr = implode(" \n", $this->JoinArr); } if (count($this->OrderArr) > 0) { $OrderStr = "ORDER BY " . implode(", ", $this->OrderArr); } if (count($this->GroupArr) > 0) { $GroupStr = "GROUP BY " . implode(", ", $this->GroupArr); } if (count($this->WhereArr) > 0) { $WhereStr = "WHERE " . implode(" AND ", $this->WhereArr); } if ($this->UseStraight) { $StraightStr = "STRAIGHT_JOIN"; } /////////////////////////////////////////// if ($this->ShowVisitors) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$SelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI\n\t\t\t\t\t# выбираем точки входа из лога\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t\t# точки входа определяются путями по сайту\n\t\t\t\t\t# вероятно, с наличием referer set \n\t\t\t\t\t#JOIN " . PFX . "_tracker_stat_path S_PATH\n\t\t\t\t\t#\tON S_PATH.LOG_ID=S_LOG.ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_split S_SPLIT\n\t\t\t\t\t\tON S_SPLIT.LOG_ID=S_LOG.ID\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; } $StatArr[$Grp]['CntClick'] = $Row->CNT; $StatArr[$Grp]['UniClick'] = $Row->UNI; $StatArr[$Grp]['CntAction'] = 0; $StatArr[$Grp]['UniAction'] = 0; $StatArr[$Grp]['CntSale'] = 0; $StatArr[$Grp]['UniSale'] = 0; $StatArr[$Grp]['Income'] = 0; $StatArr[$Grp]['Obj'] = $Row; if (count($this->GroupArr)) { $GrpItems[] = $Grp; } } if (count($GrpItems) > 0 && !ValidId($this->GroupArr[0])) { $GrpList = "AND " . $this->GroupArr[0] . " IN (" . implode(",", $GrpItems) . ")"; } } /////////////////////////////////////////// if ($this->ShowActions) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$SelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI\n\t\t\t\t\t# выбираем точки входа из лога\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t\t# присоединяем ту ветку, которая произошла от S_LOG.ID\n\t\t\t\t\t# по принципу, что в COOKIE_LOG хранится этот самый ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_log NODE\n\t\t\t\t\t\tON NODE.COOKIE_LOG=S_LOG.ID\n\t\t\t\t\t# присоединяем таблицу действий\n\t\t\t\t\t# только к выбранным веткам\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_action S_ACTION\n\t\t\t\t\t\tON S_ACTION.LOG_ID=NODE.ID\n\t\t\t\t\t# точки входа определяются путями по сайту\n\t\t\t\t\t# вероятно, с наличием referer set \n\t\t\t\t\t#JOIN " . PFX . "_tracker_stat_path S_PATH\n\t\t\t\t\t#\tON S_PATH.LOG_ID=S_LOG.ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_split S_SPLIT\n\t\t\t\t\t\tON S_SPLIT.LOG_ID=S_LOG.ID\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$GrpList}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } if (!isset($StatArr[$Grp]) && $this->ShowVisitors) { continue; } if (!$this->ShowVisitors) { if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; } } $StatArr[$Grp]['CntAction'] = $Row->CNT; $StatArr[$Grp]['UniAction'] = $Row->UNI; $StatArr[$Grp]['Obj'] = $Row; } } /////////////////////////////////////////// if ($this->ShowSales) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$SelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI,\n\t\t\t\t\tSUM(S_SALE.COST) AS SALE_SUM\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_log NODE\n\t\t\t\t\t\tON NODE.COOKIE_LOG=S_LOG.ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_sale S_SALE\n\t\t\t\t\t\tON S_SALE.LOG_ID=NODE.ID\n\t\t\t\t\t#JOIN " . PFX . "_tracker_stat_path S_PATH\n\t\t\t\t\t#\tON S_PATH.LOG_ID=S_LOG.ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_split S_SPLIT\n\t\t\t\t\t\tON S_SPLIT.LOG_ID=S_LOG.ID\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$GrpList}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } if (!isset($StatArr[$Grp]) && $this->ShowVisitors) { continue; } if (!$this->ShowVisitors) { if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; } } $StatArr[$Grp]['CntSale'] = $Row->CNT; $StatArr[$Grp]['UniSale'] = $Row->UNI; $StatArr[$Grp]['Income'] = $Row->SALE_SUM; $StatArr[$Grp]['Obj'] = $Row; } } foreach ($StatArr as $i => $Row) { if ($this->ShowActionConv) { $StatArr[$i]['ActionConv'] = $this->GetRatio($StatArr[$i]['UniClick'], $StatArr[$i]['UniAction']); } if ($this->ShowSaleConv) { $StatArr[$i]['SaleConv'] = $this->GetRatio($StatArr[$i]['UniClick'], $StatArr[$i]['UniSale']); } if (!$StatArr[$i]['Income']) { $StatArr[$i]['Income'] = 0; } } if (!ValidArr($StatArr) || count($StatArr) == 0) { return false; } if (count($this->GroupArr) < 1) { if (!isset($Grp)) { $Grp = 0; } return $StatArr[$Grp]; } return $StatArr; }
function GetTables() { $Key = "Tables_in_" . DB_NAME; $Query = "show tables"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Arr[] = $Row->{$Key}; } $this->TablesArr = $Arr; return $Arr; }
function GetCompanies() { $CompArr = array(); $Query = "SELECT * FROM " . PFX . "_tracker_client ORDER BY NAME ASC"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $CompArr[$Sql->Position]['Name'] = htmlspecialchars(stripslashes($Row->NAME)); $CompArr[$Sql->Position]['Value'] = $Row->ID; } return $CompArr; }
function UpdatePerClickEndDate($EditId) { global $Db; $Query = "SELECT * FROM " . PFX . "_tracker_camp_cost WHERE SUB_CAMPAIGN={$EditId} ORDER BY START_DATE"; $Sql = new Query($Query); $Cost = array(); while ($Row = $Sql->Row()) { $Cost[] = $Row; } for ($i = 0; $i < count($Cost); $i++) { if (isset($Cost[$i + 1])) { $Query = "UPDATE " . PFX . "_tracker_camp_cost SET END_DATE = '" . $Cost[$i + 1]->START_DATE . "' WHERE ID = " . $Cost[$i]->ID; $Db->Query($Query); } else { $Db->Query("UPDATE " . PFX . "_tracker_camp_cost SET END_DATE ='' WHERE ID =" . $Cost[$i]->ID); } } }
function ClearStat($EndTime = false, $CpId = false, $SiteId = false) { global $Logs, $Db, $Lang; if ($SiteId) { $Query = "SELECT MIN(S_LOG.STAMP) FROM " . PFX . "_tracker_" . $CpId . "_stat_log S_LOG WHERE S_LOG.SITE_ID={$SiteId}"; $StartTime = $Db->ReturnValue($Query); DeleteSiteStat($CpId, $SiteId, $StartTime, $EndTime); } if ($CpId && !$SiteId) { $Query = "SELECT MIN(S_LOG.STAMP) FROM " . PFX . "_tracker_" . $CpId . "_stat_log S_LOG INNER JOIN " . PFX . "_tracker_site S ON S.ID=S_LOG.SITE_ID WHERE S.COMPANY_ID={$CpId}"; $StartTime = $Db->ReturnValue($Query); global $ClientsArr; if (ValidArr($ClientsArr[$CpId]['Sites']) && count($ClientsArr[$CpId]['Sites']) > 0) { foreach ($ClientsArr[$CpId]['Sites'] as $SiteId => $Name) { DeleteSiteStat($CpId, $SiteId, $StartTime, $EndTime); } } } if (!$CpId && !$SiteId) { $Query = "SELECT ID, COMPANY_ID FROM " . PFX . "_tracker_site"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Query = "SELECT MIN(S_LOG.STAMP) FROM " . PFX . "_tracker_" . $Row->COMPANY_ID . "_stat_log S_LOG"; $StartTime = $Db->ReturnValue($Query); DeleteSiteStat($Row->COMPANY_ID, $Row->ID, $StartTime, $EndTime); } } $Logs->Msg($Lang['StatDeleted']); }
function GetCampIds($GrpId = false, $CpId = false) { $Grp = $GrpId ? $GrpId : $this->GrpId; $Cp = $CpId ? $CpId : $this->CpId; if (!ValidId($Grp) && !ValidId($Cp)) { return false; } if ($Cp) { $Fld = "COMPANY_ID"; $Value = $Cp; } if ($Grp) { $Fld = "CAMPAIGN_ID"; $Value = $Grp; } $CampArr = array(); $SubArr = array(); $Query = "\n\t\t\tSELECT CP.ID\n\t\t\tFROM " . PFX . "_tracker_camp_piece CP\n\t\t\tWHERE CP.{$Fld}={$Value}\n\t\t"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $CampArr[] = $Row->ID; } if ($Grp) { $Query = "SELECT ID FROM " . PFX . "_tracker_campaign WHERE PARENT_ID={$Grp}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $SubArr = $this->GetCampIds($Row->ID); $CampArr = array_merge($CampArr, $SubArr); } } $CampArr = array_unique($CampArr); if (count($CampArr) == 0) { $CampArr[] = 0; } return $CampArr; }
function GetFullLicenseArr() { $FullArr = array(); global $Db, $BF; $Query = "SELECT * FROM " . PFX . "_tracker_license"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Row->KEY_DECODE = $BF->decrypt($Row->LICENSE_KEY); $Row->License = GetLicenseText($Row->KEY_DECODE); if (!$Row->License) { continue; } foreach ($Row->License as $Key => $Value) { $FullArr[$Key][] = $Value; } } return $FullArr; }
function RebuildStoreFiles() { $ActionFile = "redir_action.nodb"; $f = @fopen(SELF . "/store/{$ActionFile}", "a+"); @flock($f, LOCK_EX); @ftruncate($f, 0); @flock($f, LOCK_UN); @fclose($f); $SplitFile = "split_test.nodb"; $f = @fopen(SELF . "/store/{$SplitFile}", "a+"); @flock($f, LOCK_EX); @ftruncate($f, 0); @flock($f, LOCK_UN); @fclose($f); $Query = "SELECT * FROM " . PFX . "_tracker_visitor_action WHERE REDIRECT_CATCH='1' "; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $ActionArr['ID'] = $Row->ID; $ActionArr['PAGE_ID'] = $Row->PAGE_ID; $ActionArr['SITE_ID'] = $Row->SITE_ID; $ActionArr['REDIRECT_URL'] = $Row->REDIRECT_URL; $ActionArr['PATH'] = $Row->PATH; SaveActionToFile($ActionArr, $ActionFile); } $Query = "SELECT ID FROM " . PFX . "_tracker_split_test"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { SaveSplitToFile(false, $SplitFile, $Row->ID); } }
function GetHostsList($Id) { global $ShowExpand; $HostsArr = array(); $Query = "SELECT * FROM " . PFX . "_tracker_site WHERE COMPANY_ID = {$Id} ORDER BY HOST"; $Sql = new Query($Query); $Sql->ReadSkinConfig(); while ($Row = $Sql->Row()) { $Row->_STYLE = $Sql->_STYLE; $Row->Hosts = array(); if ($Row->USE_HOSTS) { $ShowExpand += 1; } $Query = "SELECT * FROM " . PFX . "_tracker_site_host WHERE SITE_ID=" . $Row->ID . " ORDER BY HOST"; $SubSql = new Query($Query); while ($SubRow = $SubSql->Row()) { $Row->Hosts[] = $SubRow; } $HostsArr[] = $Row; } if (count($HostsArr) > 0) { return $HostsArr; } else { return false; } }
function UpdateStats() { global $Lang, $Logs, $Db, $Pages, $CurrentProgress, $AdditionalHead, $UnCountried, $UnCCnt; $PerPage = 100; $Pages = new PageNums($UnCCnt, $PerPage); $Pages->NoPrev = true; $Pages->NoPrevPrev = true; $Pages->NoNextNext = true; $Pages->NoPageLink = true; $Pages->Calculate(); $Pages->Args = "&DoConvert=1&UnCCnt={$UnCCnt}"; $CurrentProgress = ($Pages->PageCurrent + 1) * ceil(100 / $Pages->Pages); if ($CurrentProgress > 100) { $CurrentProgress = 100; } $AdditionalHead = "<meta http-equiv=\"refresh\" content=\"1;url=" . getURL("countries", "DoConvert=1&UnCCnt={$UnCCnt}&PS=" . ($Pages->PageStart + $Pages->Limit) . "&PC=" . ($Pages->PageCurrent + 1), "admin") . "\">"; $Query = "\n\t\t\tSELECT V.ID, I.IP\n\t\t\t\tFROM " . PFX . "_tracker_visitor V \n\t\t\t\tINNER JOIN " . PFX . "_tracker_ip I\n\t\t\t\t\tON I.ID=V.LAST_IP_ID \n\t\t\t\tWHERE V.FIRST_COUNTRY_ID = 0 AND V.LAST_IP_ID>0\n\t\t\t\tLIMIT 0, " . $Pages->Limit; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $IpLong = sprintf("%u\n", ip2long($Row->IP)); if (!$IpLong) { return 0; } global $Db; $Query = "\n\t\t\tSELECT C.ID\n\t\t\t\tFROM " . PFX . "_tracker_country_ip CI\n\t\t\t\tINNER JOIN " . PFX . "_tracker_country C\n\t\t\t\t\tON C.CODE=CI.COUNTRY_CODE\n\t\t\t\tWHERE {$IpLong} BETWEEN CI.IP_START AND CI.IP_END\n\t\t"; $CountryId = $Db->ReturnValue($Query); if (!$CountryId) { continue; } $Query = "UPDATE " . PFX . "_tracker_visitor SET FIRST_COUNTRY_ID = '{$CountryId}' WHERE ID = " . $Row->ID; $Db->Query($Query); } if ($CurrentProgress == 100) { $Logs->Msg($Lang['ConvertDone']); $Pages = new PageNums(0, 0); $AdditionalHead = false; } }
function GrpListPath($CurrentGrpId = false) { if (!$CurrentGrpId) { return false; } global $CompId, $Db; $GrpArr = array(); $OverArr = array(); $Query = "SELECT ID, NAME, PARENT_ID FROM " . PFX . "_tracker_campaign WHERE ID={$CurrentGrpId} AND COMPANY_ID={$CompId} ORDER BY POSITION ASC"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $GrpArr[] = $Row; if ($Row->PARENT_ID > 0) { $OverArr = GrpListPath($Row->PARENT_ID); } if (ValidArr($OverArr)) { $GrpArr = array_merge($GrpArr, $OverArr); } } return $GrpArr; }
function GetDomain($domain, $admin, $userid, $usertype, $logger) { $db = new MysqlManager(); $query = new Query($db); if ($usertype == 1) { $sql = "SELECT domain_name from domain where iddomain=" . $domain . ""; } if ($usertype == 2) { $sql = "SELECT domain_name from domain where iddomain=" . $domain . " and iduser="******" and idadmin=" . $admin . ""; } $query->raw_prepare($sql); $val = $query->Row(); $logger->debug("Domain for user is : " . $val); return $val; }
} } if (ValidVar($IP) && !ValidIp($IP)) { $Logs->Err($Lang['IpErr']); } $SitesArr = array(); if (!ValidId($SiteId) && !ValidId($CpId)) { $nsProduct->Redir("default"); } if (ValidId($CpId)) { $Query = "SELECT * FROM " . PFX . "_tracker_client WHERE ID = {$CpId}"; $Comp = $Db->Select($Query); $PageTitle = $Comp->NAME; $Query = "SELECT ID, HOST FROM " . PFX . "_tracker_site WHERE COMPANY_ID={$CpId}"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $SitesArr[] = $Row; } if (Count($SitesArr) == 1) { $SiteId = $SitesArr[0]->ID; } } if (ValidId($SiteId)) { $Query = "SELECT * FROM " . PFX . "_tracker_site WHERE ID = {$SiteId}"; $Site = $Db->Select($Query); $PageTitle = $Site->HOST; $SiteList = $SiteId; if (!ValidId($CpId)) { $CompId = $CpId = $Site->COMPANY_ID; } }
function GetGrps() { global $Lang; $Grps = array(); $Grps[0]['Name'] = $Lang['NotSorter']; $Grps[0]['Value'] = 0; $Query = "SELECT * FROM " . PFX . "_tracker_visitor_agent_grp ORDER BY NAME"; $Sql = new Query($Query); while ($Row = $Sql->Row()) { $Grps[$Sql->Position + 1]['Name'] = $Row->NAME; $Grps[$Sql->Position + 1]['Value'] = $Row->ID; } if (count($Grps) > 0) { return $Grps; } else { return false; } }
function GetSplitPages($Id) { $PagesArr = array(); $Query = "\n\t\tSELECT TS.*, SI.HOST, TQ.QUERY_STRING, TSP.ID AS TSP_ID, \n\t\t\tTSP.FULL_PATH\n\t\t\tFROM " . PFX . "_tracker_split_page TSP\n\t\t\tINNER JOIN " . PFX . "_tracker_site_page TS\n\t\t\t\tON TS.ID=TSP.PAGE_ID\n\t\t\tINNER JOIN " . PFX . "_tracker_site SI \n\t\t\t\tON SI.ID = TS.SITE_ID\n\t\t\tLEFT JOIN " . PFX . "_tracker_query TQ\n\t\t\t\tON TQ.ID=TSP.QUERY_ID\n\t\t\tWHERE SPLIT_ID={$Id}\n\t"; $Sql = new Query($Query); $Sql->ReadSkinConfig(); while ($Row = $Sql->Row()) { $Row->SCHEME = ""; if ($Row->FULL_PATH) { $Check = parse_url($Row->FULL_PATH); $Row->SCHEME = $Check['scheme'] . "://"; $Row->HOST = $Check['host']; } $Row->PATH = $Row->SCHEME . $Row->HOST . "<br>" . $Row->PATH; if ($Row->QUERY_STRING) { $Row->PATH .= "?" . $Row->QUERY_STRING; } $Row->_STYLE = $Sql->_STYLE; $PagesArr[] = $Row; } if (count($PagesArr) > 0) { return $PagesArr; } else { return false; } }
function GetStat() { global $Logs; $StatArr = array(); $GrpItems = array(); $GrpList = ""; $SelectStr = ""; $JoinStr = ""; $OrderStr = ""; $GroupStr = ""; $LimitStr = ""; $WhereStr = ""; $StraightStr = ""; if (count($this->SelectArr) > 0) { $SelectStr = implode(", \n", $this->SelectArr) . ", \n"; } if (count($this->JoinArr) > 0) { $JoinStr = implode(" \n", $this->JoinArr); } if (count($this->OrderArr) > 0) { $OrderStr = "ORDER BY " . implode(", ", $this->OrderArr); } if (count($this->GroupArr) > 0) { $GroupStr = "GROUP BY " . implode(", ", $this->GroupArr); } if (count($this->WhereArr) > 0) { $WhereStr = "WHERE " . implode(" AND ", $this->WhereArr); } //if ($this->UseStraight) $StraightStr="STRAIGHT_JOIN"; if ($this->PageLimit > 0) { $LimitStr = "LIMIT " . $this->PageLimit; } //echo $this->CookieJoin; if ($this->CookieJoin) { $ActionJoin = "\n\t\t\t\t\t# присоединяем ту ветку, которая произошла от S_LOG.ID\n\t\t\t\t\t# по принципу, что в COOKIE_LOG хранится этот самый ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_log NODE\n\t\t\t\t\t\tON NODE.COOKIE_LOG=S_LOG.ID\n\t\t\t\t\t# присоединяем таблицу действий\n\t\t\t\t\t# только к выбранным веткам\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_action S_ACTION\n\t\t\t\t\t\tON S_ACTION.LOG_ID=NODE.ID\n\t\t\t"; $SaleJoin = "\n\t\t\t\t\t# присоединяем ту ветку, которая произошла от S_LOG.ID\n\t\t\t\t\t# по принципу, что в COOKIE_LOG хранится этот самый ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_log NODE\n\t\t\t\t\t\tON NODE.COOKIE_LOG=S_LOG.ID\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_sale S_SALE\n\t\t\t\t\t\tON S_SALE.LOG_ID=NODE.ID\n\t\t\t"; } else { $ActionJoin = "\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_action S_ACTION\n\t\t\t\t\t\tON S_ACTION.LOG_ID=S_LOG.ID\n\t\t\t"; $SaleJoin = "\n\t\t\t\t\tINNER JOIN " . PFX . "_tracker_" . $this->CpId . "_stat_sale S_SALE\n\t\t\t\t\t\tON S_SALE.LOG_ID=S_LOG.ID\n\t\t\t"; } $ActionWhereAdd = ""; $SaleWhereAdd = ""; if ($this->SiteIds) { $ActionWhereAdd = "AND S_ACTION.SITE_ID IN (" . $this->SiteIds . ")"; $SaleWhereAdd = "AND S_SALE.SITE_ID IN (" . $this->SiteIds . ")"; } /////////////////////////////////////////// $HitSelectStr = $SelectStr; $HitSelectStr = preg_replace("/NODE\\.STAMP/", "S_LOG.STAMP", $HitSelectStr); if ($this->ShowVisitors) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$HitSelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI\n\t\t\t\t\t# выбираем точки входа из лога\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); if ($this->LoggerFunc) { $Sql->LoggerFunc = $this->LoggerFunc; $Sql->GetTime = true; } while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } $Row->ID = $Grp; if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; if ($this->NullGrpName && !$Grp) { $Row->{$Name} = $this->NullGrpName; $StatArr[$Grp]['Name'] = $this->NullGrpName; } } $StatArr[$Grp]['CntClick'] = $Row->CNT; $StatArr[$Grp]['UniClick'] = $Row->UNI; $StatArr[$Grp]['CntAction'] = 0; $StatArr[$Grp]['UniAction'] = 0; $StatArr[$Grp]['CntSale'] = 0; $StatArr[$Grp]['UniSale'] = 0; $StatArr[$Grp]['Income'] = 0; $StatArr[$Grp]['Obj'] = $Row; if ($this->MakeSum) { $this->CntClickSum += $Row->CNT; $this->UniClickSum += $Row->UNI; } if (count($this->GroupArr)) { $GrpItems[] = " '{$Grp}' "; } } if (count($GrpItems) > 0 && !ValidId($this->GroupArr[0])) { $GrpList = "AND " . $this->GroupArr[0] . " IN (" . implode(",", $GrpItems) . ")"; } } if ($this->CookieJoin) { $WhereStr = preg_replace("/S_LOG\\.STAMP/", "NODE.STAMP", $WhereStr); } /////////////////////////////////////////// if ($this->ShowActions) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$SelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI,\n\t\t\t\t\tCOUNT(DISTINCT S_ACTION.ACTION_ID) ACTION_UNI\n\t\t\t\t\t# выбираем точки входа из лога\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t\t{$ActionJoin}\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$ActionWhereAdd}\n\t\t\t\t{$GrpList}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); if ($this->LoggerFunc) { $Sql->LoggerFunc = $this->LoggerFunc; $Sql->GetTime = true; } while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } $Row->ID = $Grp; if (!isset($StatArr[$Grp]) && $this->ShowVisitors) { continue; } if (!$this->ShowVisitors) { if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; if ($this->NullGrpName && !$Grp) { $Row->{$Name} = $this->NullGrpName; $StatArr[$Grp]['Name'] = $this->NullGrpName; } } $StatArr[$Grp]['CntClick'] = 0; $StatArr[$Grp]['UniClick'] = 0; $StatArr[$Grp]['CntSale'] = 0; $StatArr[$Grp]['UniSale'] = 0; $StatArr[$Grp]['Income'] = 0; } $StatArr[$Grp]['CntAction'] = $Row->CNT; $StatArr[$Grp]['UniAction'] = $Row->UNI; $StatArr[$Grp]['Actions'] = $Row->ACTION_UNI; if (!ValidVar($StatArr[$Grp]['Obj'])) { $StatArr[$Grp]['Obj'] = $Row; } if ($this->MakeSum) { $this->CntActionSum += $Row->CNT; $this->UniActionSum += $Row->UNI; } } } /////////////////////////////////////////// if ($this->ShowSales) { $Query = "\n\t\t\t\tSELECT {$StraightStr}\n\t\t\t\t\t{$SelectStr}\n\t\t\t\t\tCOUNT(S_LOG.ID) CNT,\n\t\t\t\t\tCOUNT(DISTINCT S_LOG.VISITOR_ID) UNI,\n\t\t\t\t\tSUM(S_SALE.COST) AS SALE_SUM\n\t\t\t\t\t# выбираем точки входа из лога\n\t\t\t\t\tFROM " . PFX . "_tracker_" . $this->CpId . "_stat_log S_LOG\n\t\t\t\t\t{$SaleJoin}\n\t\t\t\t{$JoinStr}\n\t\t\t\t{$WhereStr}\n\t\t\t\t{$SaleWhereAdd}\n\t\t\t\t{$GrpList}\n\t\t\t\t{$GroupStr}\n\t\t\t\t{$OrderStr}\n\t\t\t\t{$LimitStr}\n\t\t\t"; //echo HLSQL($Query); $Sql = new Query($Query); if ($this->LoggerFunc) { $Sql->LoggerFunc = $this->LoggerFunc; $Sql->GetTime = true; } while ($Row = $Sql->Row()) { if ($this->GrpFld) { $Tmp = $this->GrpFld; $Grp = $Row->{$Tmp}; } else { $Grp = $Sql->Position; } $Row->ID = $Grp; if (!isset($StatArr[$Grp]) && $this->ShowVisitors) { continue; } if (!$this->ShowVisitors) { if ($this->GrpName) { $Name = $this->GrpName; $StatArr[$Grp]['Name'] = $Row->{$Name}; if ($this->NullGrpName && !$Grp) { $Row->{$Name} = $this->NullGrpName; $StatArr[$Grp]['Name'] = $this->NullGrpName; } } } if (!$this->ShowVisitors && !$this->ShowActions) { $StatArr[$Grp]['CntClick'] = 0; $StatArr[$Grp]['UniClick'] = 0; $StatArr[$Grp]['CntAction'] = 0; $StatArr[$Grp]['UniAction'] = 0; } $StatArr[$Grp]['CntSale'] = $Row->CNT; $StatArr[$Grp]['UniSale'] = $Row->UNI; $StatArr[$Grp]['Income'] = $Row->SALE_SUM; if (!ValidVar($StatArr[$Grp]['Obj'])) { $StatArr[$Grp]['Obj'] = $Row; } if ($this->MakeSum) { $this->CntSaleSum += $Row->CNT; $this->UniSaleSum += $Row->UNI; } } } foreach ($StatArr as $i => $Row) { $Uni = $StatArr[$i]['UniClick'] = $StatArr[$i]['UniClick'] ? $StatArr[$i]['UniClick'] : $this->PrevLevelUni; if ($this->ShowActionConv || $this->ShowPrevActionConv) { $StatArr[$i]['ActionConv'] = $this->GetRatio($Uni, $StatArr[$i]['UniAction']); } if ($this->ShowSaleConv) { $StatArr[$i]['SaleConv'] = $this->GetRatio($Uni, $StatArr[$i]['UniSale']); } } if (!ValidArr($StatArr) || count($StatArr) == 0) { return false; } if (count($this->GroupArr) < 1) { if (!isset($Grp)) { $Grp = 0; } return $StatArr[$Grp]; } return $StatArr; }