------------------------------------------------- Login POST https://diggerss.com/api/login email: test@unitdsp.com password: Secure-Login-US-2024 ------------------------------------------------- Countries GET https://diggerss.com/api/countries Get json of all countries ------------------------------------------------- Get State GET https://diggerss.com/api/get-state?id={country} country = the country you want Get json of all states of the selected country ------------------------------------------------- Get Tiktok stats GET https://diggerss.com/api/tiktok/{tag_search} tag search = the hash you want to search Get json with Tiktok response ------------------------------------------------- Get Domains of each keyword GET https://diggerss.com/api/domain-traffic/{keyword} keyword = the keyword you want to search domains Get json with domains of specific keyword ------------------------------------------------- Get Census information GET https://www.diggerss.com/api/census/{code} code = the id of census you want to get data Get json with Census information ------------------------------------------------- Get Meta information GET https://diggerss.com/api/facebook/interest/{keyword}/{lang} keyword = the keyword you want to get interest lang = (en, es) Language of your query Get json with Interest information ------------------------------------------------- Get Inegi information (only for México queries) GET https://diggerss.com/api/inegi/{resource} resource = the state you want get information Get json with INEGIs data ------------------------------------------------- Get Meta volume GET https://diggerss.com/api/facebook/volume?state={state}&interest={interest}&origin={country} state = the state you want to get information interest = the meta id for specific interest origin = country (US) ------------------------------------------------- jobscan.co pradeepsudeep02@gmail.com sudo ssh -p 982 root@69.61.32.138 password : btBCwvEj56U-BzEx+R4fvXG6x2@a@E clickhouse-client --host=localhost --port=9000 --user=default --password=qwer use convrsus_DSP show tables select * from dj_hourly_table where datatime between '2024-03-17 00:00:00' and '2024-03-17 23:59:59'; SELECT sum(total_amount) as dsp_sum from dj_hourly_table where datatime between '2024-03-15 00:00:00' and '2024-03-17 23:59:59' select b.bannerid as banner_id from rv_banners as b,rv_campaigns as c,rv_clients as cl,rv_agency as ag where b.campaignid = c.campaignid AND c.clientid = cl.clientid AND cl.agencyid =ag.agencyid AND ag.agencyid=8 select c.selected_dsp from rv_campaigns as c,rv_clients as cl,rv_agency as ag where c.clientid = cl.clientid AND cl.agencyid =ag.agencyid AND ag.agencyid=8 SELECT c.selected_dsp FROM rv_campaigns AS c LEFT JOIN rv_clients AS cl ON cl.clientid = c.clientid LEFT JOIN rv_agency AS ag ON ag.agencyid = cl.agencyid WHERE ag.agencyid = 38 SELECT sum(total_amount) as dsp_sum from dj_hourly_table where ad_id in(303,304,305306,307,308,309,310,311,312,313,314) AND bidderid in(37) AND datatime between '2022-03-15 00:00:00' AND '2024-03-17 23:00:00' SELECT CAST(round(avg(c.revenue) , 2),'Decimal64(2)') as cpm_real_avg from dj_hourly_table h LEFT JOIN rv_banners AS b ON h.ad_id = b.bannerid LEFT JOIN rv_campaigns AS c ON c.campaignid = b.campaignid LEFT JOIN rv_clients AS cl ON cl.clientid = c.clientid LEFT JOIN rv_agency AS ag ON ag.agencyid = cl.agencyid where ag.agencyid = 1 AND h.datatime between '2024-01-01 00:00:00' AND '2024-01-30 23:59:59' GROUP BY c.revenue SELECT CAST(AVG(c.revenue) AS DECIMAL(10,2)) as cpm_avg FROM dj_hourly_table AS h LEFT JOIN rv_banners AS b ON h.ad_id = b.bannerid LEFT JOIN rv_campaigns AS c ON c.campaignid = b.campaignid LEFT JOIN rv_clients AS cl ON cl.clientid = c.clientid LEFT JOIN rv_agency AS ag ON ag.agencyid = cl.agencyid WHERE ag.agencyid = 1 AND ((h.datatime >= '2024-03-01 00:00:00') AND (h.datatime <= '2024-03-09 00:00:00')) GROUP BY c.revenue DSP Profit = Total Spend /Unit Profit x 100 Real CPM = CPM Rate + DSP Profit/1-Rebate Percentage Agency Table = Unit Profit,Rebate Percentage Rebate Percentage = Rebate Value / 100 1-Rebate Percentage Agency Share = Real CPM + Agency Profit INSERT INTO `dj_admin_settings` (`id`, `paypalid`, `emailid`, `payment_type`, `approval`, `payment_approval`, `postpaid_amt`, `paymentgate_type`, `publishershare`, `secretkey`, `minnimumamount`, `criticalamount`, `buffer`, `buffer_value`, `agencyid`) VALUES (NULL, 'sb-plhka28842428@business.example.com', 'sb-plhka28842428@business.example.com', '1', '0', '0', '0', '1', '0.00', 'EMV9VMlyVaMln_lIUqZDtPkLvWW_xu8RRXHnhRC4-zbpHfWQW6p7I1M8p1c_uEZReg9Dz_goCpLdjJym', '0', '10', '2', '10.00', '34'); ALTER TABLE `dj_payment_history` ADD `agency_id` INT(11) NOT NULL AFTER `client_id`; ALTER TABLE `dj_payment_details` ADD `agency_id` INT NOT NULL AFTER `client_id`; ALTER TABLE `rv_campaigns` ADD `agency_profit` DOUBLE(10,2) NOT NULL AFTER `selected_site`; ALTER TABLE `rv_banners` ADD `admin_approve` INT(10) NOT NULL AFTER `iframe_friendly`; $app->get('/get_agency_payment_invoice/[{agency_id}]', function ($request, $response, $args) { if ($request->getAttribute('has_errors')) { //There are errors, read them group by campaignid h.ad_id as ad_id,b.bannerid as bannerid,b.description as bannername,, /*" . $table_prefix . "agency as ag, " . $table_prefix . "clients as cl pi.agencyid = ag.agencyid And cl.agencyid = ag.agencyid AND*/ $errors = $request->getAttribute('errors'); return $this->response->withJson($errors); return $response; } else { $input = $request->getParsedBody(); $table_prefix = $this->get('settings')['path']['table_prefix']; $agency_id = $args['agency_id']; $stc = "select * from " . $table_prefix . "agency where agencyid=:agency_id"; $std = $this->db->prepare($stc); $std->bindParam("agency_id", $agency_id); $std->execute(); $client = $std->fetchAll(); $start_date = ''; $end_date = ''; if ($client) { $sql = "select pi.agencyid as agencyid, pi.invoice_no, pi.invoice_date, pi.start_date, pi.end_date, pi.agency_share, pi.total_amount, pi.status from dj_payment_invoice as pi where pi.agencyid = :agency_id"; $sth = $this->db->prepare($sql); $sth->bindParam(":agency_id", $agency_id); $sth->execute(); $todos = $sth->fetchAll(); if ($todos) { $response = array('response' => 'ok', 'msg' => 'Success', 'data' => $todos); return $this->response->withJson($response); } else { $response = array('response' => 'ok', 'msg' => 'No Stats Available'); return $this->response->withJson($response); } } else { $response = array('response' => 'error', 'msg' => 'Please enter valid Agency Id'); return $this->response->withJson($response); } } }); 7305888260 Clientwise Website Report Query select sum(h.impressions) as impressions, sum(h.clicks) as clicks, sum(h.conversions) as conversions, round(sum(h.total_revenue),2) as revenue, round((sum(h.clicks)/sum(h.impressions)) * 100,2) as ctr, round((sum(h.total_revenue)/sum(h.impressions)) * 1000,2) as ecpm, p.affiliateid as affiliateid, p.name as website from rv_data_summary_ad_hourly as h, rv_banners as b, rv_campaigns as c, rv_clients as cl, rv_affiliates as p, rv_zones as z where h.ad_id = b.bannerid AND c.clientid = 29 AND cl.clientid = c.clientid AND b.campaignid = c.campaignid and p.affiliateid=z.affiliateid AND z.zoneid = h.zone_id group by h.zone_id; Clintwise Zone Report Query select DATE(h.date_time) as date_time, sum(h.impressions) as impressions, sum(h.clicks) as clicks, sum(h.conversions) as conversions, round(sum(h.total_revenue),2) as revenue, round((sum(h.clicks)/sum(h.impressions)) * 100,2) as ctr, round((sum(h.total_revenue)/sum(h.impressions)) * 1000,2) as ecpm, p.affiliateid as affiliateid, p.name as affiliatename, z.zoneid AS zoneid, z.zonename as zonename from rv_data_summary_ad_hourly as h, rv_banners as b, rv_campaigns as c, rv_clients as cl, rv_affiliates as p, rv_zones as z where h.ad_id = b.bannerid AND c.clientid = 29 AND cl.clientid = c.clientid AND b.campaignid = c.campaignid AND p.affiliateid = z.affiliateid AND z.zoneid = h.zone_id revNew Competitive