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