sync contract data to ebs

php

     /**
     * sync contract data to oracle
     */
    public function insertIntoCustomDffUpdateStgTable($contract_id)
    {
        $contract = $this->CI->cm->find($contract_id);
        $variables = json_decode($contract->variables);
        $charges = (array)$variables->contract_data->charge_data->charges;

        $contractToEBSfields = $this->CI->config->item("contractToEBSfields");

        $total_records = count($charges);
        $chunk_size = 5;
        $offset = 0;

        $failed_chunks = [];
        $failed_child_circuit_ids = [];

        $marketing_person_cache = [];

        $account_status = null;
        $sql = "SELECT ATTR_LINE_CODE from WL_ATTRIBUTE_LINES_ALL WHERE value = 'Active'";
        $result = $this->ebs->query($sql);

        if ($result) {
            $account_status =  $result->row()->ATTR_LINE_CODE;
        }

        while ($offset < $total_records) {
            $charge_chunk = array_slice($charges, $offset, $chunk_size);
            $sql = "";
            try {
                foreach ($charge_chunk as $key => $charge) {

                    $curl = curl_init();

                    curl_setopt_array($curl, array(
                        CURLOPT_URL => CORPORATE_SUPPORT_DASHBOARD . 'v2/customer-list/' . $key,
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_ENCODING => '',
                        CURLOPT_MAXREDIRS => 10,
                        CURLOPT_TIMEOUT => 0,
                        CURLOPT_FOLLOWLOCATION => true,
                        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                        CURLOPT_CUSTOMREQUEST => 'GET',
                        CURLOPT_HTTPHEADER => array(
                            'Authorization: Bearer ' . CORPORATE_TOKEN
                        ),
                    ));

                    $response = curl_exec($curl);


                    curl_close($curl);
                    $response = json_decode($response);


                    if (is_null($response->data)) {
                        throw new Exception('SQL execution failed: ' . 'customer-list doesnot exist for circuit-id:' . $key);
                    }

                    $customer_list = $response->data;

                    $fixedValues = [
                        "mechanism_for_auto_renewal" => function ($contract) {
                            return isset($contract->is_auto_renewal_enabled) ? ($contract->is_auto_renewal_enabled == "t" ? 'Y' : 'N') : null;
                        },
                        "parent_circuit_id" => function ($contract) use ($key) {
                            $sql = "select * from cr_v2_customer_list cvcl where cvcl.customer_code= ? and is_parent=true LIMIT 1";
                            $result  = $this->corporate->query($sql, [$contract->organization_code])->row();

                            if (!$result) {
                                return null;
                            } else {
                                return $result->circuit_id;
                            }
                        },
                        "child_circuit_id" => function () use ($key) {
                            return $key;
                        },
                        "marketing_person" => function () use ($variables, $marketing_person_cache) {
                            $email =  $variables->contract_data->on_behalf->worldlink->witness->email;

                            if (isset($marketing_person_cache[$email])) {
                                return $marketing_person_cache[$email];
                            }

                            $sql = "SELECT * from wl_username_v WHERE EMAIL_ADDRESS = '$email'";
                            $result = $this->ebs->query($sql);

                            if (!$result) {
                                throw new Exception('SQL execution failed: ' . $this->ebs->error()['message']);
                            }

                            $person_id = $result->row()->PERSON_ID;
                            $marketing_person_cache[$email] = $person_id;
                            return $person_id;
                        },
                        "bandwidth_local" => function () use ($charge) {
                            list($bandwidth, $attr) = explode(' ', $charge->service_charge->bandwidth);
                            return $bandwidth;
                        },
                        "bandwidth_international" => function () use ($charge) {
                            list($bandwidth, $attr) = explode(' ', $charge->service_charge->bandwidth);
                            return $bandwidth;
                        },
                        "contract_amount" => function () use ($charge) {
                            return $charge->service_charge->mrc;
                        },
                        "agreement_start_date" => function ($contract) {
                            if ($contract->start_date) {
                                return "TO_DATE('{$contract->start_date}', 'YYYY-MM-DD')";
                            }
                        },
                        "agreement_end_date" => function ($contract) {
                            if ($contract->end_date) {
                                return "TO_DATE('{$contract->end_date}', 'YYYY-MM-DD')";
                            }
                        },
                        "creation_date" => function () {
                            return "SYSTIMESTAMP";
                        },
                        "billing_period" => function () use ($variables) {
                            return $variables->billing_term;
                        },
                        "sector" => function () use ($variables) {
                            return $variables->contract_data->customer_information->organization_nature;
                        },
                        "billing_branch" => function () use ($charge) {
                            return $charge->service_charge->billing_branch_id;
                        },
                        "connectivity_status" => function () use ($customer_list) {
                            return $this->getCircuitTypeShortcut($customer_list->connectivity_type);
                        },
                        "account_status" => $account_status,
                        "child_user_name" => $customer_list->uname
                    ];


                    $mappedEbsFields = [];


                    foreach ($contractToEBSfields as $ebsField => $contractField) {
                        if (array_key_exists($ebsField, $fixedValues)) {
                            $value = $fixedValues[$ebsField];

                            if (is_callable($value)) {
                                $mappedEbsFields[$ebsField] = $value($contract); // $value is callback fn
                            } else {
                                $mappedEbsFields[$ebsField] = $value;
                            }
                        } else {
                            if (isset($contract->{$contractField})) {
                                $mappedEbsFields[$ebsField] = $contract->{$contractField};
                            } else {
                                $mappedEbsFields[$ebsField] = null;
                            }
                        }
                    }

                    $columns = implode(", ", array_keys($mappedEbsFields));
                    $values = implode(", ", array_map(function ($value) {
                        if (strpos($value, "TO_DATE") !== false || strpos($value, "SYSTIMESTAMP") !== false) {
                            return $value;
                        }
                        return "'$value'";
                    }, $mappedEbsFields));

                    // $existing_count = $this->ebs->query("SELECT  COUNT(*) as COUNT FROM WL_CUSTOMER_DFF_UPDATE_STG WHERE child_circuit_id = '$key'")->row()->COUNT;
                    // if ($existing_count && $existing_count >= 1) {

                    if (false) {
                        $set_values = implode(', ', array_map(function ($key, $value) {

                            if ($value === true) {
                                $value = "1";
                            } else if ($value === false) {
                                $value = "0";
                            }

                            if (strpos($value, "TO_DATE") !== false || strpos($value, "SYSTIMESTAMP") !== false) {
                                return "$key = $value";
                            }

                            return "$key = '$value'";
                        }, array_keys($mappedEbsFields), $mappedEbsFields));

                        $sql .= "UPDATE WL_CUSTOMER_DFF_UPDATE_STG SET $set_values WHERE CHILD_CIRCUIT_ID = '$key'; ";
                    } else {
                        $sql .= "INSERT INTO WL_CUSTOMER_DFF_UPDATE_STG ($columns) VALUES ($values);";
                    }
                }

                if (!empty($mappedEbsFields)) {
                    $sql = "BEGIN $sql END;";

                    if (!$this->ebs->query($sql)) {
                        throw new Exception('SQL execution failed: ' . $this->ebs->error()['message']);
                    }

                    internal_logger(__FUNCTION__, 'syncContractToEBS SUCCESS', $this->exchange_username, ['chunk' => "{$offset}-" . ($offset + $chunk_size), 'data' => $sql]);
                }
            } catch (Exception $e) {
                $failed_chunks[] = "{$offset}-" . ($offset + $chunk_size);
                $failed_child_circuit_ids = array_merge($failed_child_circuit_ids, array_keys($charge_chunk));
                internal_logger(__FUNCTION__, 'syncContractToEBS ERROR', $this->exchange_username, ['chunk' => "{$offset}-" . ($offset + $chunk_size), 'data' => $sql, 'message' => $e->getMessage()]);
            }

            $offset += $chunk_size;
        }


        if (count($failed_child_circuit_ids) > 0) {

            $arr =  [
                "msg" => "error in syncing sql for chunks " . implode(",", $failed_chunks),
                "failed_child_circuit_ids" => $failed_child_circuit_ids
            ];

            $failed_corporate_remarks = json_encode($arr);


            $query = "UPDATE cr_v2_servicecontract 
            SET is_pushed_to_ebs = '0', 
                ebs_remarks = ?::jsonb 
            WHERE id = ?";

            $this->corporate->query($query, [$failed_corporate_remarks, $contract_id]);
        } else {
            $this->corporate->query("UPDATE cr_v2_servicecontract SET is_pushed_to_ebs = '1', ebs_remarks = 'successfully synced' WHERE id = $contract_id");
        }

        return true;
    }







// application/config/contract.php

$config['contractToEBSfields'] = [
    "party_name" => "organization_name",
    "parent_circuit_id" => "-",
    "child_circuit_id" => "-",
    "child_user_name" => "-",
    "agreement_number" => "agreement_number",
    "agreement_start_date" => "start_date",
    "agreement_end_date" => "end_date",
    "contract_amount" => "-",
    "revised_start_date" => "-",
    "revised_end_date" => "-",
    "subscription_number" => "-",
    "billing_period" => "-",
    "marketing_person" => "-",
    "bandwidth_local" => "-",
    "bandwidth_international" => "-",
    "mechanism_for_auto_renewal" => "is_auto_renewal_enabled",
    "mac_number" => "-",
    "sector" => "-",
    "dealt_amount" => "-",
    "remarks" => "-",
    "location_division" => "-",
    "attribute19" => "-",
    "billing_branch" => "-",
    "creation_date" => "-",
    "last_updated_date" => "-",
    "status" => "-",
    "message" => "-",
    "ebill_ref" => "-",
    "connectivity_status" => "-",
    "account_status" => "-",
];