Exemple #1
0
 public function registerSchedule($schedule)
 {
     $schedule->call(function () {
         CampaignWorker::instance()->process();
     })->everyFiveMinutes();
 }
 /**
  * Execute the console command.
  * @return void
  */
 public function fire()
 {
     define('MAIL_STARTED', time());
     // L / blacklisted - unsubscribed / iu_company
     //		$sql="update operations.users set L=NULL";
     //    	$dbr = DB::raw($sql);
     $sql = "UPDATE operations.users SET A=null,B=null,C=null,D=null,E=null,F=null,G=null,H=null,I=null,J=null,K=null,L='X' where L is NULL AND (ok_unsubscribed_at IS NOT NULL OR ok_blacklisted_at IS NOT NULL OR mailing_list_id=150";
     $dbr = DB::raw($sql);
     /*
         	$dbr = DB::raw('operations.users')->select('id')
         	    ->whereNull('L')
         	    ->whereNotNull('ok_unsubscribed_at')
         	    ->orwhereNotNull('ok_blacklisted_at')
         	    ->orwhere('mailing_list_id',150)
         	    ->get();
     		$this->output->writeln("Making sure all blacklisted & unsubscribed & errors are marked only in 'L' column... (".count($dbr).")");
             foreach($dbr as $row){
             	DB::table('operations.users')
             	    ->where('id',$row->id)
             	    ->update(['A'=>null,'B'=>null,'C'=>null,'D'=>null,'E'=>null,'F'=>null,'G'=>null,'H'=>null,'I'=>null,'J'=>null,'K'=>null,'L'=>'X']);
             }
     */
     /*
             $num = DB::table('leancode_campaign_lists_subscribers')->whereBetween('list_id', array(1, 7))->update(['list_id'=>99]);
     		$this->output->writeln("Reset subscriber table... (".count($num).")");
             $num = DB::table('operations.users')
                         ->update(['A'=>null,'B'=>null,'C'=>null,'D'=>null,'E'=>null,'F'=>null,'G'=>null,'H'=>null,'I'=>null,'J'=>null,'K'=>null,'L'=>null]);
     		$this->output->writeln("Reset user table A-L table... (".count($num).")");
     */
     // reseting any special list items in A
     DB::table('operations.users')->where('mailing_list_id', '<>', 140)->where('A', 'S')->update(['A' => null]);
     // A / Mailed to / iu_gender
     $num = DB::table('operations.users')->whereNull('L')->where('is_activated', 1)->update(['A' => 'Y', 'C' => 'Y']);
     $total = DB::table('operations.users')->select('id')->whereNull('L')->whereNull('A')->where('mailing_list_id', '<>', 60)->where('mailing_list_id', '<>', 70)->where('mailing_list_id', '<>', 90)->where('mailing_list_id', '<>', 100)->where('mailing_list_id', '<>', 110)->where('mailing_list_id', '<>', 120)->where('mailing_list_id', '<>', 140)->where('mailing_list_id', '<>', 150)->leftJoin('leancode_campaign_messages_subscribers', 'id', '=', 'subscriber_id')->whereNotNull('sent_at')->distinct()->count();
     for ($c = 0; $c < $total; $c = $c + 1000) {
         $dbr = DB::table('operations.users')->select('id')->whereNull('L')->whereNull('A')->where('mailing_list_id', '<>', 60)->where('mailing_list_id', '<>', 70)->where('mailing_list_id', '<>', 90)->where('mailing_list_id', '<>', 100)->where('mailing_list_id', '<>', 110)->where('mailing_list_id', '<>', 120)->where('mailing_list_id', '<>', 140)->where('mailing_list_id', '<>', 150)->leftJoin('leancode_campaign_messages_subscribers', 'id', '=', 'subscriber_id')->whereNotNull('sent_at')->skip($c)->take(1000)->distinct()->get();
         $this->output->writeln("Updating those we've mailed to... (" . $c . " of {$total})");
         foreach ($dbr as $row) {
             DB::table('operations.users')->where('id', $row->id)->update(['A' => 'Y']);
         }
     }
     // B / pingback / iu_job
     $dbr = DB::table('operations.users')->select('id')->where('A', 'Y')->leftJoin('leancode_campaign_messages_subscribers', 'id', '=', 'subscriber_id')->whereNotNull('read_at')->whereNull('B')->get();
     $this->output->writeln("Updating those we've received pingback from... (" . count($dbr) . ")");
     foreach ($dbr as $row) {
         DB::table('operations.users')->where('id', $row->id)->update(['B' => 'Y']);
     }
     // C / activated / iu_about
     //done in A above
     //    	$num = DB::table('operations.users')->whereNotNull('A')->whereNull('C')->where('is_activated',1)->update(['C'=>'Y']);
     //		$this->output->writeln("Updating those who clicked the email and therefore activated... ($num)");
     // D / activated but no FCFL / iu_webpage
     $num = DB::table('operations.users')->whereNotNull('C')->whereNull('ok_free_credits_datetime')->update(['D' => 'N', 'E' => null]);
     $this->output->writeln("Updating those who activated but did not take the offer... ({$num})");
     // E / activated & FCFL / iu_blog
     $num = DB::table('operations.users')->whereNotNull('C')->whereNotNull('ok_free_credits_datetime')->update(['D' => null, 'E' => 'Y']);
     $this->output->writeln("Updating those who activated and took the offer... ({$num})");
     // F / FCFL but NOT using credits / iu_facebook
     $dbr = DB::table('operations.users')->select('users.id')->whereNotNull('E')->leftJoin('operations.bp_sponsors', 'users.id', '=', 'user_id')->whereNull('user_id')->whereNull('F')->get();
     $this->output->writeln("Updating those who are not applying credits... (" . count($dbr) . ")");
     foreach ($dbr as $row) {
         DB::table('operations.users')->where('id', $row->id)->update(['F' => 'N', 'G' => null, 'H' => null, 'I' => null]);
     }
     // G / FCFL but NOT using credits / iu_twitter
     $dbr = DB::table('operations.users')->select('users.id')->whereNotNull('E')->leftJoin('operations.bp_sponsors', 'users.id', '=', 'user_id')->whereNotNull('user_id')->whereNull('G')->get();
     $this->output->writeln("Updating those who ARE currently applying credits... (" . count($dbr) . ")");
     foreach ($dbr as $row) {
         DB::table('operations.users')->where('id', $row->id)->update(['G' => 'Y', 'F' => null]);
     }
     // H / FCFL but NOT max / iu_skype
     $date = date("Y-m-d H:i:s", strtotime("- 7 days"));
     $num = DB::table('operations.users')->whereNotNull('G')->where('ok_credits', '>=', 25)->where('ok_free_credits_datetime', '>', $date)->update(['H' => 'N', 'I' => null]);
     $this->output->writeln("Applying credits but NOT to the max - more than half left on account yet he renewed in last 7 days... ({$num})");
     // I / FCFL to the MAX - Perfect! / iu_icq
     $num = DB::table('operations.users')->whereNotNull('G')->where('ok_credits', '<', 25)->where('ok_free_credits_datetime', '>', $date)->update(['I' => 'Y', 'H' => null]);
     $this->output->writeln("Applying credits to the max - using more than half and renewing in last 7 days... ({$num})");
     // J / Credits but not updated / iu_comment
     $num = DB::table('operations.users')->whereNotNull('E')->where('ok_credits', '>=', 1)->where('ok_credits', '<', 50)->where('ok_free_credits_datetime', '<', $date)->update(['J' => 'N', 'H' => null, 'I' => null]);
     $this->output->writeln("Having credits left and not renewing in last 7 days... ({$num})");
     // K / Credits but not updated / iu_telephone
     $num = DB::table('operations.users')->whereNotNull('E')->where('ok_credits', '<', 1)->where('ok_free_credits_datetime', '<', $date)->update(['K' => 'N', 'J' => null, 'H' => null, 'I' => null]);
     $this->output->writeln("Currently not having any credits left and not renewing them... ({$num})");
     // 1 = A
     $total = DB::table('operations.users')->where('A', 'Y')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 1]);
     $this->output->writeln("Updating mailed to in subscriber table... ({$total})");
     // 2 = D
     $total = DB::table('operations.users')->whereNotNull('D')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 2]);
     $this->output->writeln("Updating 'Clicked but NOT accepted the FCFL' in subscriber table... ({$total})");
     // 3 = F
     $total = DB::table('operations.users')->whereNotNull('F')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 3]);
     $this->output->writeln("Updating 'Accepted FCFL but currently NOT applying credits' in subscriber table... ({$total})");
     // 4 = H
     $total = DB::table('operations.users')->whereNotNull('H')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 4]);
     $this->output->writeln("Updating 'Applying credits but NOT to the max' in subscriber table... ({$total})");
     // 5 = J
     $total = DB::table('operations.users')->whereNotNull('J')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 5]);
     $this->output->writeln("Updating 'Having credits left but not renewing them in last 7 days' in subscriber table... ({$total})");
     // 6 = K
     $total = DB::table('operations.users')->whereNotNull('K')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 6]);
     $this->output->writeln("Updating 'Having no credits left and not renewing them  in last 7 days' in subscriber table... ({$total})");
     // 7 = I
     $total = DB::table('operations.users')->whereNotNull('I')->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 7]);
     $this->output->writeln("Updating 'Applying credits to the max' in subscriber table... ({$total})");
     // now enough products
     $total = DB::table('operations.users')->where('ok_company_products_count', '>', 1)->where('mailing_list_id', 130)->update(['mailing_list_id' => 99]);
     $this->output->writeln("Updating 'not enough products' in subscriber table... ({$total})");
     // not enough products
     $total = DB::table('operations.users')->where('ok_company_products_count', '<', 2)->where('mailing_list_id', '<>', 150)->update(['mailing_list_id' => 130]);
     $this->output->writeln("Updating 'not enough products' in subscriber table... ({$total})");
     // no company
     $total = DB::table('operations.users')->wherenull('ok_company_id')->update(['mailing_list_id' => 120]);
     $this->output->writeln("Updating 'no company' in subscriber table... ({$total})");
     DB::table('operations.users')->where('id', 2)->update(['mailing_list_id' => 60]);
     $this->output->writeln("Updating Dom and moving him back into his category... ({$num})");
     DB::table('operations.users')->where('id', 10)->update(['mailing_list_id' => 70]);
     $this->output->writeln("Updating Clive and moving him back into his category... ({$num})");
     $total = DB::table('operations.users')->whereNotNull('ok_unsubscribed_at')->where('mailing_list_id', '<>', '90')->update(['mailing_list_id' => 90]);
     $this->output->writeln("Moving unsubscribed to list 90... ({$total})");
     $total = DB::table('operations.users')->whereNotNull('ok_blacklisted_at')->where('mailing_list_id', '<>', '100')->update(['mailing_list_id' => 100]);
     $this->output->writeln("Moving blacklisted to list 100... ({$total})");
     $message = CampaignWorker::instance()->process();
     $this->output->writeln($message);
 }