{    "require": {
        "google/apiclient": "^2.0"    }
}

php composer.phar install --no-dev

DBM_CLIENT_ID=8990-ru2amnc17f5jchhgsepp0uro96smu16b.apps.googleusercontent.com

DBM_CLIENT_SECRET=XXXXXXXXXXXXX

DBM_REFRESH_TOKEN=1/VeTb6k8T291WCKWsPfnBrjOBxtwwCxO11Q6FGH8DZJk

return [

    'client_id' => env('DBM_CLIENT_ID', null),




    'client_secret' => env('DBM_CLIENT_SECRET', null),

];

php artisan make:console GoogleDbmService

<?php




namespace App\Console\Commands;


use Illuminate\Console\Command;

class GoogleDbmService extends Command

{   

   /**

    * The name and signature of the console command.

    */

   protected $signature = 'google-dbm:service {--date=today}';

    /** 

     *The console command description.

     * @var string 

     */ 

   protected $description = 'google ads api for campaign 

                            service, adset etc';    /** 

     * Create a new command instance.

     * @return void

     */    

   public function __construct(){

        parent::__construct();    

   }

   /** 

    * Execute the console command.

    * @return mixed 

    */    

   public function handle()    {

      //Your Code Goes Hare

    }

}

protected $commands = [    

   Commands\GoogleDbmService::class
];

use Google_Client;

use Google_Service_DoubleClickBidManager;

use Google_Service_DoubleClickBidManager_DownloadLineItemsRequest;

use Google_Service_DoubleClickBidManager_DownloadRequest;

use Google_Service_DoubleClickBidManager_FilterPair;

use Google_Service_DoubleClickBidManager_Parameters;

use Google_Service_DoubleClickBidManager_Query;

use Google_Service_DoubleClickBidManager_QueryMetadata;

use Google_Service_DoubleClickBidManager_QuerySchedule;

use Google_Service_Exception;

$client = new Google_Client();

$client->setAccessType('offline');

$client->setClientId(env('DBM_CLIENT_ID'));

$client->setClientSecret(env('DBM_CLIENT_SECRET'));

$client->addScope('https://www.googleapis.com/auth/doubleclickbidmanager');




$service = new Google_Service_DoubleClickBidManager($client);

$data = $client->refreshToken(env('DBM_REFRESH_TOKEN'));

$client->setAccessToken($data['access_token']);

public function downloadCmp($service, $adv_ids){

    // Setup any filtering on the API request

    $dliRequest = new

    Google_Service_DoubleClickBidManager_DownloadRequest();




    $dliRequest->setFilterType("ADVERTISER_ID");

    $dliRequest->setFileTypes(array("CAMPAIGN"));

    $dliRequest->setFilterIds(array($adv_ids));

    try {

      $result = $service->sdf->download($dliRequest);

    } catch (Google_Service_Exception $e) {

      printf('<p>Exception: %s</p>', $e->getMessage());

      print '<p>Consider filtering by ADVERTISER_ID</p>';

      return;

    } catch (Exception $e) {

      printf('<p>Exception: %s</p>', $e->getMessage());

      return;

    }

    $csv_content = $result['campaigns'];

    $csvDelimiter = '';

    $csvLines = str_getcsv($csv_content, "\n");

    $linesToImport = [];

    foreach($csvLines as $row)

    {

      $linesToImport[] = str_getcsv($row, $csvDelimiter);

    }

    return $linesToImport;

  }

//Create Query

  public function createQuery($service, $dateRange, $filters){

    // Setup any filtering on the API request

    $qryRequest_prm = new  Google_Service_DoubleClickBidManager_Query();

    $qryRequest = new Google_Service_DoubleClickBidManager_QueryMetadata();

    $qryRequest->setDataRange($dateRange);

    $qryRequest->setFormat('csv');

    $qryRequest->setTitle('query:'.time());

    $qryRequest->setRunning('true');

    $a = [];




    foreach($filters as $k=>$v){

      $filterPairRequest = new Google_Service_DoubleClickBidManager_FilterPair();

      $filterPairRequest->setType($k);

      $filterPairRequest->setValue($filters[$k]);

      $a[] = $filterPairRequest;

    }




    $groupBY = array(

      //"FILTER_ADVERTISER",

      //"FILTER_ORDER_ID",

      "FILTER_MEDIA_PLAN",

      "FILTER_LINE_ITEM",

      //"FILTER_BUDGET_SEGMENT_DESCRIPTION",

      "FILTER_DATE"

    );




    $prmRequest = new Google_Service_DoubleClickBidManager_Parameters();

    //$prmRequest->setFilters(["FILTER_PARTNER"]);

    $params = array(

      "METRIC_CLICKS",

      "METRIC_TOTAL_CONVERSIONS",

      "METRIC_CONVERSIONS_PER_MILLE",

      "METRIC_IMPRESSIONS",

      "METRIC_CTR",

      "METRIC_REVENUE_PARTNER",

      //'METRIC_REVENUE_ADVERTISER',

    );

    $prmRequest->setMetrics($params);

    $prmRequest->setType("TYPE_GENERAL");

    $prmRequest->setFilters($a);

    $prmRequest->setGroupBys($groupBY);

    //$prmRequest->setGroupBys();




    $schRequest = new Google_Service_DoubleClickBidManager_QuerySchedule();

    $schRequest->setFrequency('ONE_TIME');




    $qryRequest_prm->setKind('doubleclickbidmanager#query');

    $qryRequest_prm->setMetadata($qryRequest);

    $qryRequest_prm->setParams($prmRequest);

    $qryRequest_prm->setQueryId(123456);

    $qryRequest_prm->setSchedule($schRequest);

    try{

      $result = $service->queries->createquery($qryRequest_prm);

    }catch (Exception $e){

      print($e);

    }

    return $result->queryId;

  }

//Delete query after operations

  public function deleteQuery($service, $queryId){

    try{

      $result = $service->queries->deletequery($queryId);

    }catch (Exception $e){

      print($e);

    }

    return $result;

  }

public function handle()
  {
    if ($this->option('date') == "today") {
      $dateRange = 'CURRENT_DAY';
    } elseif ($this->option('date') == "yesterday") {
      $dateRange = 'PREVIOUS_DAY';
    } elseif ($this->option('date') == "last_month") {
      $dateRange = 'PREVIOUS_MONTH';
    }elseif ($this->option('date') == "this_month") {
      $dateRange = 'MONTH_TO_DATE';
    }elseif ($this->option('date') == "last_year") {
      $dateRange = 'PREVIOUS_YEAR';
    }elseif ($this->option('date') == "this_year") {
      $dateRange = 'YEAR_TO_DATE';
    }elseif ($this->option('date') == "all_time") {
      $dateRange = 'ALL_TIME';
    } else {
      $this->info('Please choose date param between today/yesterday/last_month/this_month/last_year/this_year');
      return;
    }

    $prefix = "DBM_";
    $partner_id = 20534561234;
    $requestParam = [];
    $requestParam['account_type'] = "DBM";
    $account = Account::getAccountListByAccountType($requestParam);
    if(count($account) <= 0)
    {
      $this->error('You have not any account please set up your account');
      return;
    }
    $this->info("Total Ad Account:-" . count($account));

    if($account)
    {
      foreach ($account as $key => $value)
      {
        if($value['access_token'] && $value['ext_account_id'])
        {
          $requestParam['user_id'] = $value['user_id'];
          $requestParam['account_id'] = $value['account_id'];
          $requestParam['account_name'] = $value['name'];
          $requestParam['ext_account_id'] = $value['ext_account_id'];
          $requestParam['adv_id'] = $value['adv_id'];
          $requestParam['manager_id'] = $value['manager_id'];

          $this->info("Account:-".$value['ext_account_id']);

          $client = new Google_Client();
          $client->setAccessType('offline');
          $client->setClientId(env('DBM_CLIENT_ID'));
          $client->setClientSecret(env('DBM_CLIENT_SECRET'));
          $client->addScope('https://www.googleapis.com/auth/doubleclickbidmanager');

          $service = new Google_Service_DoubleClickBidManager($client);
          $data = $client->refreshToken($value['access_token']);
          $client->setAccessToken($data['access_token']);

          try{
            $cmpByAdvId = $this->downloadCmp($service, $requestParam['ext_account_id']);
            $this->info('Campaign count:-' . (count($cmpByAdvId)-1));
            if(count($cmpByAdvId) > 0) {
              foreach ($cmpByAdvId as $key_id => $cmp_value) {
                if ($key_id == 0) {
                  continue;
                }
                else{
                  $filters = array(
                    'FILTER_PARTNER'=>$partner_id,
                    'FILTER_ADVERTISER'=>$requestParam['ext_account_id'],
                    'FILTER_MEDIA_PLAN'=>$cmp_value[0],
                  );
                  $queryId = $this->createQuery($service, $dateRange, $filters);
                  if ($queryId != '' || $queryId != 0) {
                    sleep(60); #coz csv takes time to write on google cloud
                    // Call the API, getting a list of queries.
                    $result = $service->queries->getquery($queryId);
                    $csv_content = file_get_contents($result->getMetadata()->getGoogleCloudStoragePathForLatestReport());

                    if (isset($csv_content)) {
                      $csvDelimiter = '';
                      $csvLines = str_getcsv($csv_content, "\n");
                      $linesToImport = [];
                      foreach ($csvLines as $row) {
                        $linesToImport[] = str_getcsv($row, $csvDelimiter);
                      }
                      $this->info('Campaign Id:-'.$cmp_value[0]);
                      $i = 0;
                      if (count($linesToImport) > 0) {
                        foreach ($linesToImport as $key => $value) {
                          if ($key == 0) {
                            continue;
                          }
                          else if($key > 0 && count($linesToImport[$key])<5){
                            continue;
                          }
                          else{
                            if(!empty($value[10])){
                              if(!empty($cmp_value[10])){
                                $sdate = strtotime($cmp_value[10]);
                                $cmp_sdate = date('Y-m-d h:i:s', $sdate);
                              }else{
                                $cmp_sdate='';
                              }

                              if(!empty($cmp_value[11])){
                                $edate = strtotime($cmp_value[11]);
                                $cmp_edate = date('Y-m-d h:i:s', $edate);
                              }else{
                                $cmp_edate='';
                              }
                              
                              $requestParam['cmp_id'] = $cmp_value[0];
                              $requestParam['flight_id'] = $value[3]; //adsGroup id
                              $requestParam['conversions'] = $value[10];
                              $requestParam['impressions'] = $value[12];
                              $requestParam['clicks'] = $value[9];
                              $requestParam['ctr'] = $value[13];
                              $requestParam['date'] = $value[7];
                              $requestParam['wkno'] = (int)date("W", strtotime($value[7]));
                              $requestParam['rev_model'] = NULL;
                              $requestParam['cmp_name'] = $prefix . $cmp_value[2];
                              $requestParam['cmp_status'] = $value[2];
                              $requestParam['campaign_name'] = $cmp_value[2];
                              $requestParam['cmp_type'] = NULL;
                              $requestParam['flight_name'] = $prefix . $value[2];
                              $requestParam['start_date'] = $cmp_sdate;
                              $requestParam['end_date'] = $cmp_edate;
                              $requestParam['budget'] = $cmp_value[9];
                              $requestParam['payout'] = $value[14];
                              $requestParam['hour'] = 0;
                              $requestParam['ms_id'] = '178';//DBM media source id

                              $saveCmpRptData = DbmOverviewTableForTest::saveYourCmpRptData($requestParam);
                              if ($saveCmpRptData) {
                                if (isset($saveCmpRptData['status']) && $saveCmpRptData['status'] == 9) {
                                  DbmOverviewTableForTest::updateYourCmpRptData($requestParam);
                                }
                                
                              } else {
                                $this->info('database not connected and something went wrong');
                                return;
                              }
                              $i++;
                            }
                          }
                        }
                        $this->info('Count Report:-' . $i);
                        $this->info('*************************');
                        $this->deleteQuery($service, $queryId);
                      }
                    }
                    else{
                      $this->info('@Error:csv_content variable is not found');
                      $this->deleteQuery($service, $queryId);
                    }
                  }
                }
              }
            }
          }catch (\Execption $e){
            print($e)
            return;
          }

        }

      }

    }
  }