csv-generate-payment-cloud-consultations-list

/**
     * csvGeneratePaymentCloudConsultation function to CSV Generate Coverwallet Consultation
     *
     * @param
     * @return view
     * @author matellio
     * @Cron_time At 00:00  every day at midnight.
     * @url : 0 0 * * 0 curl http://54.92.241.100/v1/admin/public/csv-generate-payment-cloud-consultations-list
     */
    public function csvGeneratePaymentCloudConsultation()
    {
        try {
            ini_set('memory_limit', '6144M');
            ini_set('max_execution_time', 0); //300 seconds = 5 minutes
            $today = new \DateTime();
            $date = $today->format('Y-m-d');
            $weekDays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
            $yesterday = date("Y-m-d", strtotime( '-1 days' ) );
            if (in_array(date('l'), $weekDays)) {
                $query_order_status = Incorporations::select(
                    "incorporations.order_id",
                    "orders.customer_id",
                    "orders.order_completion_date",
                    "order_statuses.approval_date",
                    "states.abbr",
                    "business_types.bt_name",
                    "business_infos.biz_name",
                    "business_types.id As biz_type",
                    "business_infos.biz_address",
                    "states2.abbr as biz_formation_state",
                    "business_infos.biz_city",
                    "business_infos.biz_zip",
                    "business_infos.biz_category"
                )
                    ->join("orders", "orders.id", "=", "incorporations.order_id")
                    ->join("order_statuses", "order_statuses.order_id", "=", "incorporations.order_id")
                    ->leftJoin("business_infos", "incorporations.order_id", "=", "business_infos.order_id")
                    ->leftJoin("business_types", "business_infos.biz_type", "=", "business_types.id")
                    ->leftJoin("states", "business_infos.biz_state", "=", "states.id")
                    ->leftJoin("states As states2", "business_infos.biz_formation_state", "=", "states2.id")
                    ->where("orders.parent_order_status", $this->order_status_active)
                    //->where("orders.order_completion_date", ">=", '2020-11-03 00:00:00')
                ->where("incorporations.payment_cloud", "Yes");

                if(date('l') == "Monday"){
                    $from = date("Y-m-d", strtotime( '-3 days' ) );
                        $query_order_status = $query_order_status->whereRaw("(orders.order_completion_date >= ? AND orders.order_completion_date <= ?)",[$from." 00:00:00", $yesterday." 23:59:59"]);
                }else{
                        $query_order_status = $query_order_status->whereDate('orders.order_completion_date',$yesterday);
                }

                    $query_order_status = $query_order_status->get();
                    //->whereNull("order_statuses.payroll_processed")


                $excel_rows = 'First Name' . "\t" .
                    'Last Name' . "\t" .
                    'Business Name' . "\t" .
                    'Email' . "\t" .
                    'Phone Number' . "\t" .
                    'Address' . "\t" .
                    'City' . "\t" .
                    'State' . "\t" .
                    'Zip' . "\t" .
                    'Industry' . "\t" .
                    'Entity Type' . "\t" .
                    'Date Completed' . "\n";
                if ($query_order_status->count() > 0) {
                    foreach ($query_order_status as $key => $row_order_status) {
                        $order_id = $row_order_status->order_id;
                        // Get the customer's details
                        $customerInfo = User::where(['id' => $row_order_status->customer_id])->first();
                        if (preg_match('/[\'^£$%&*()}{@#~?><>,0-9|=_+¬-]/', $customerInfo->first_name) || preg_match('/[\'^£$%&*()}{@#~?><>,0-9|=_+¬-]/', $customerInfo->last_name)) {
                            continue;
                        }
                        $indicatorIncluded = false;

                        if (!empty($row_order_status->biz_type)) {
                            if ($row_order_status->biz_type == 1) {
                                $validIndicators = array('LLC', 'L.L.C.', 'LC', 'L.C.', 'Ltd. Co.', 'Ltd. Co', 'Ltd Co', 'Ltd Co.', 'Ltd. Company', 'Ltd. Liability Co.', 'Ltd. Liability Company', 'Limited Liability Co.', 'Limited Liability Company', 'PLLC');
                                for ($i = 0; $i <= count($validIndicators) - 1; $i++) {
                                    if (stripos($row_order_status->biz_name, $validIndicators[$i]) !== false) {
                                        $indicatorIncluded = true;
                                    }
                                }
                                if (!$indicatorIncluded) {
                                    // Customer did not supply a valid indicator. Default to ', LLC'
                                    $row_order_status->biz_name = $row_order_status->biz_name . ', LLC';
                                }
                            } else if ($row_order_status->biz_type == 2 || $row_order_status->biz_type == 3) {
                                $validIndicators = array('Inc', 'Inc.', ', Inc', ',Inc', 'Corp', 'Incorporated', 'Corporation', 'P.C.', 'Professional Corporation');
                                for ($i = 0; $i <= count($validIndicators) - 1; $i++) {
                                    if (stripos($row_order_status->biz_name, $validIndicators[$i]) !== false) {
                                        $indicatorIncluded = true;
                                    }
                                }
                                if (!$indicatorIncluded) {
                                    // Customer did not supply a valid indicator. Default to ', LLC'
                                    $row_order_status->biz_name = $row_order_status->biz_name . ', Inc';
                                }
                            } else {
                                // Nonprofit
                                $validIndicators = array('Inc', 'Inc.', ', Inc', ',Inc', 'Corp', 'Incorporated', 'Corporation', 'P.C.', 'Professional Corporation');
                                for ($i = 0; $i <= count($validIndicators) - 1; $i++) {
                                    if (stripos($row_order_status->biz_name, $validIndicators[$i]) !== false) {
                                        $indicatorIncluded = true;
                                    }
                                }
                                if (!$indicatorIncluded) {
                                    // Customer did not supply a valid indicator. Default to ', LLC'
                                    $row_order_status->biz_name = $row_order_status->biz_name . ', Inc';
                                }
                            }
                        }
                        $excel_rows .=
                            ucfirst(strtolower($customerInfo->first_name)) . "\t" .
                            ucfirst(strtolower($customerInfo->last_name)) . "\t" .
                            preg_replace('/[\t|\n\,]+/', '', $row_order_status->biz_name) . "\t" .
                            $customerInfo->email . "\t" .
                            $customerInfo->phone_number . "\t" .
                            ucwords(strtolower(preg_replace('/[\t|\n\,]+/', '', $row_order_status->biz_address))) . "\t" .
                            ucwords(strtolower($row_order_status->biz_city)) . "\t" .
                            (!empty($row_order_status->abbr) ? trim(preg_replace('/[\t,\,]+/', '', $row_order_status->abbr))  : "") . "\t" .
                            $row_order_status->biz_zip . "\t" .
                            $row_order_status->biz_category . "\t" .
                            $row_order_status->bt_name . "\t" .
                            date('m/d/Y', strtotime((empty($row_order_status->approval_date) ? $row_order_status->order_completion_date : $row_order_status->approval_date))) . "\t" . "\n";
                        // // Update the order status to show the tax consultation has been processed
                        // $updateOrder = OrderStatuses::where(['order_id' => $order_id])->first();
                        // $updateOrder->bank_of_america_processed = date("Y-m-d H:i:s");
                        // $updateOrder->save();

                        // Update the order status to show the tax consultation has been processed
                        $updateOrder = OrderStatuses::where(['order_id' => $order_id])->first();
                        $updateOrder->business_insurance_processed  = date('Y-m-d H:i:s', strtotime(gmdate("Y-m-d\TH:i:s\Z")));
                        $updateOrder->save();
                    } // end of for loop
                    // Get last week's date range
                    $previous_day = strtotime("-1 day");
                    $day = date("Y-m-d", $previous_day);
                    $file_name = "Payment Cloud" . $day . ".xls";
                    \Storage::disk('local')->put('Payment_Cloud/' . $file_name, $excel_rows);
                    $my_file = \Storage::disk('local')->path("Payment_Cloud" . "/" . $file_name);
                    // Email CSV to 'bpack@1800accountant.com'.
                    $emailToUsers = [
                        ["name"=>"David Costa","email"=>"david.costa@swyftfilings.com"],
                        //["name"=>"Mario Ramos","email"=>"mario.ramos@coverwallet.com"],
                        //["name"=>"Jeke Chen","email"=>"jeke.chen@coverwallet.com"],
                        ["name"=>"Wsy Wsy","email"=>"wsy@paymentcloudinc.com"]
                    ];
                    $emailTemplateKeywords['{data}'] = "Tax Consultations" . $day;
                    // $emailTemplateKeywords['{name}'] = 'David Costa';
                    // $emailTemplateKeywords['{email}'] = 'david.costa@swyftfilings.com';
                    foreach($emailToUsers as $userData){
                        $emailTemplateKeywords['{name}'] = $userData['name'];
                        $emailTemplateKeywords['{email}'] = $userData['email'];
                        $data = sendEmail('Overwallet_Consultations', $emailTemplateKeywords, $userData['name'], checkEmailSendToThis($userData['email']));
                        Mail::send('mails.common_mail', $data, function ($message) use ($data, $day, $my_file) {
                            $message->to($data['receiver'], $data['receiver_name'])->subject($data['subject']);
                            $message->from($data['sender'], $data['sender_name']);
                            $message->attach($my_file, [
                                // 'as' => 'Man', // If you want you can chnage original name to custom name
                                'mime' => 'application/xls'
                            ]);
                        });
                    }

                    //remove file after sned email
                    unlink($my_file);
                }
            }
            die("Success");

        } catch (\Exception $e) {
            $logmessage = 'Exception at Line : ' . $e->getLine().' | '.$e->getMessage().'.';
            Log::debug($logmessage);
        } catch (\Error $e) {
            $logmessage = 'Error at Line : ' . $e->getLine().' | '.$e->getMessage().'.';
            Log::error($logmessage);
        }
    }