dimanche 28 février 2021

How to give rest to mysql cpu while process 100000 queriesne by one in laravel?

I am using laravel jobs to do calculation on week end during calculation i need to run almost 100000 queries in a row one by one due to this my mysql server usage reaches 100% i am thinking of to give rest between this process .How i can do this?

Here is the function which i am calling about 25000 times inside loop which has almost 30 queries.

public function calculateConsignmentOneByOne($consignment, $total_consignments, $current_iteration, $user_id, $notify)
{

    if ($consignment->finalize == 0) {

        $type = 'Income';
        $customer = $consignment->customers;
        $delivery_run_id = $consignment->delivery_run_id;
        if ($consignment->consignment_type == 'pickup' || $consignment->consignment_type == 'return') {
            $delivery_address_id = $consignment->pickup_address;
        } else {
            $delivery_address_id = $consignment->delivery_address;
        }
        $customer_id = $consignment->customer_id;
        $rate_zone = $consignment->rate_zone_id;
        if ($rate_zone == null) {
            $address_to_rate_zone_mapping = getRateZoneByAddressMapping($consignment, $type);

            if ($address_to_rate_zone_mapping != false) {
                $rate_zone = 0;
                $rate_zone = $address_to_rate_zone_mapping;
            } else {
                $rate_zone =   $this->getRateZone($delivery_run_id, $delivery_address_id, $type);
            }
        }
        if (!empty($consignment->invoice)) {
            if ($consignment->invoice->status === 3) {
                $error = 'Related Invoice is Approved,Now you Can not modify charges';
                chargeError('Consignment', $consignment->id, 'invoice_approved', $error, $type);
            }
        }
        $productType = $consignment->product_type_id;
        if ($rate_zone === 'wrong_delivery_run') {
            $error = 'No Delivery Run is Assigned to this consignment';
            chargeError('Consignment', $consignment->id, 'wrong_delivery_run', $error, $type);
        } else if ($rate_zone === 'wrong_delivery_zone') {
            if ($consignment->consignment_type == 'pickup' || $consignment->consignment_type == 'return') {
                $address_msg = 'Pickup address';
            } else {
                $address_msg = 'Delivery address';
            }
            $error = 'Delivery Zone not found with current suburb and postcode given in ' . $address_msg;

            chargeError('Consignment', $consignment->id, 'wrong_delivery_zone', $error, $type);
        } else if (empty($productType)) {
            $error = 'Please Attach Product Type to Consignment';
            chargeError('Consignment', $consignment->id, 'product_type_error', $error, $type);
        } else if ($rate_zone === 'wrong_delivery_address') {
            $error = 'Delivery Address not Found , May be Deleted';
            chargeError('Consignment', $consignment->id, 'wrong_delivery_address', $error, $type);
        } else {
            $rate_charge = $this->getRateChargeByRateZoneAndCustomer($rate_zone, $type, $consignment, $customer);

            if ($rate_charge === 'rate_not_found') {
                $error = 'Rate Card in Customer Charge with "' . RateZone::find($rate_zone)->name . '" Rate zone or Charge Type not found';
                chargeError('Consignment', $consignment->id, 'rate_not_found', $error, $type);
            }
            if ($rate_charge === 'customer_not_found') {
                $error = 'Customer Not Found, May be Deleted';
                chargeError('Consignment', $consignment->id, 'customer_not_found', $error, $type);
            }
            if ($rate_charge === "invalid_carton_pallet") {
                $error = 'No pallets or cartons given, check the consignment data has been loaded correctly.';
                chargeError('Consignment', $consignment->id, 'invalid_carton_pallet', $error, $type);
            }
            if ($rate_charge === "invalid_invoice_value") {
                $error = 'No invoice value given, check the consignment data has been loaded correctly.';
                chargeError('Consignment', $consignment->id, 'invalid_invoice_value', $error, $type);
            }
            if ($rate_charge === "invalid_weight") {
                $error = 'No Weight value given, check the consignment data has been loaded correctly.';
                chargeError('Consignment', $consignment->id, 'invalid_weight', $error, $type);
            } else {


                if ($type == 'Income') {
                    $rate_charge_array =   array('class' => null, 'type' => $type, 'description' => $this->chargeDescription, 'income' => $rate_charge, 'expense' => 0.00, 'rate_zone_id' => $rate_zone, 'customer_id' => $customer->id, 'apply_fuel_levy' => $this->fuelLevy, 'object_id' => $consignment->id, 'model' => 'Consignment', 'automatic' => 1);
                } else {
                    $rate_charge_array = array('class' => null, 'type' => $type, 'description' => $this->chargeDescription, 'income' => 0.00, 'expense' => $rate_charge, 'rate_zone_id' => $rate_zone, 'customer_id' => $customer->id, 'apply_fuel_levy' => $this->fuelLevy, 'object_id' => $consignment->id, 'model' => 'Consignment', 'automatic' => 1);
                }
                if ($type == 'Income') {
                    $final_charges = $this->getAdhocChargeRate($consignment, 'Consignment');
                }

                $final_charges[] = $rate_charge_array;
                $final_charges = collect($final_charges);

                Charge::where('object_id', $consignment->id)->where('automatic', 1)->where('model', 'Consignment')->delete();
                ChargeError::where('object_id', $consignment->id)->where('model', 'Consignment')->delete();

                $this->storeAutoCalculatedConsignmentCharges($final_charges);
                $this->calculateFuelLevyForConsignment($consignment->id);



                //this will keep updating fuel levy with each consignment calculation
                // if (InvoiceTotal::where('invoice_id', $consignment->invoice_id)->where('name', 'Fuel Levy')->exists()) {
                //     $income = 0;
                //     $expense = 0;
                //     $total = InvoiceTotal::where('invoice_id', $consignment->invoice_id)->where('name', 'Fuel Levy')->first();
                //     $income = (float)$total->income + (float)$consignment->charges()->sum('fuel_levy_value');
                //     $expense += 0;
                //     InvoiceTotal::where('invoice_id', $consignment->invoice_id)->where('name', 'Fuel Levy')->update(['income' => $income, 'expense' => $expense]);
                //     $invoice = Invoice::find($consignment->invoice_id);


                //     Invoice::where('id', $consignment->invoice_id)->update(['income' => $invoice->invoice_totals()->sum('income'), 'expense' => $invoice->invoice_totals()->sum('expense')]);
                // }
                Invoice::find($consignment->invoice_id)->update(['sum_up' => 0]);
            }
        }



        $last_consignment = Consignment::select('id', 'invoice_id')->where('invoice_id', $consignment->invoice_id)->where('finalize', 0)->orderBy('id', 'desc')->first();
        $invoice_id = $consignment->invoice_id;

        //if its last consignment to calculate and notify is true
        if ($notify && $total_consignments == $current_iteration) {
            Notification_Helper::sendNotification($user_id, 'Invoice Calculation is Completed ', route('invoices.show', $consignment->invoice_id));
        }
        $upload_to_xero = 0;
        if (XeroCustomer::where('customer_id', $consignment->customer_id)->exists()) {
            if (XeroCustomer::where('customer_id', $consignment->customer_id)->first()->automatic_invoice_upload_xero == 1) {
                $upload_to_xero = 1;
            }
        }

        if ($consignment->id == $last_consignment->id) {
            Invoice::find($invoice_id)->update(['calculation_complete' => 1]);
            $this->consignmentSumup($invoice_id);
        }


        //if its last consignment to calculate and has no charge error then
        //invoice will be uploaded to xero
        if ($consignment->id == $last_consignment->id) {
            //checking if error exists Related to current invoice
            $consignment_charge_exists = ChargeError::where('model', 'Consignment')->whereIn('object_id', function ($query) use ($invoice_id) {
                $query->select('id')
                    ->from(with(new Consignment)->getTable())
                    ->where('invoice_id', $invoice_id);
            })->exists();

            $po_charge_exist = ChargeError::where('model', 'Purchase Order')->whereIn('object_id', function ($query) use ($invoice_id) {
                $query->select('id')
                    ->from(with(new PurchaseOrder)->getTable())
                    ->where('invoice_id', $invoice_id);
            })->exists();

            $so_charge_exist = ChargeError::where('model', 'Sale Order')->whereIn('object_id', function ($query) use ($invoice_id) {
                $query->select('id')
                    ->from(with(new SaleOrder)->getTable())
                    ->where('invoice_id', $invoice_id);
            })->exists();

            $sp_charge_exist = ChargeError::where('model', 'Storage Period')->whereIn('object_id', function ($query) use ($invoice_id) {
                $query->select('id')
                    ->from(with(new StoragePeriod)->getTable())
                    ->where('invoice_id', $invoice_id);
            })->exists();
            if (!$consignment_charge_exists && !$po_charge_exist && !$so_charge_exist && !$sp_charge_exist && $upload_to_xero == 1 && $this->consignmentSumup($invoice_id)) {
                UploadInvoiceToXero::dispatch($consignment->invoice_id)->onQueue('invoice')->delay(Carbon::now()->addMinutes(2));
                // CustomerController::uploadAutoInvoiceToXero($consignment->invoice_id, $xeroCredential, new CustomerSettingService);
                Invoice::find($consignment->invoice_id)->update(['sum_up' => 1]);
            } else {
                Invoice::find($consignment->invoice_id)->update(['sum_up' => 1, 'charge_error' => 1]);
            }
        } else {
            Invoice::find($consignment->invoice_id)->update(['sum_up' => 1, 'charge_error' => 1]);
        }
    }
}



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire