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;
}
