Create invoice from contract - monthly

Hi there,

I know it has been asked before, but I can’t get it to work - creating an invoice from a contract seems to be impossible. One suggested using the converToInvoice.php from the AOS_Quotes, but that’s just a bit to technical for me.

What I need shouldn’t be too difficult - creating a monthly invoice from the contract. Is there anyone out there willing to help me - if so, please let me know how much it’ll cost. It’ll save me a horribly lot of monthly manual labor.

Thanks in advance!

Anyone?

Hello,

It is possible with few changes in code. if you wish trial and error here are some steps.

  1. You need to create convert to invoice button, code reference you can get it from modules/AOS_Quotes/metadata/detailviewdefs.php and copy button code and add it to AOS_Contracts module’s metadata
  2. Copy modules/AOS_Quotes/converToInvoice.php and paste it to your AOS_Contracts module and change the module reference
  3. If you want to relate those invoice list with contract you need to create relationship from studio and It should be one contract have multiple invoices!

Hope it helps you

1 Like

well, it’s not only the button, I mean, it should automatically create those invoices monthly?

Why is this fucntionality not default? I think all contracts needs to be invoiced, don’t they?

A bit weird I think. Can anyone explain the logic of not having the ability to create an invoice from a contract?

1 Like

Why is this functionality not default? I think all contracts needs to be invoiced, don’t they?

A bit weird I think. Can anyone explain the logic of not having the ability to create an invoice from a contract?

These kind of thing makes me uncertain if SuiteCRM is a fully functional open source alternative.

SuiteCRM is not an ERP, and it is not an accounting app. The Quotes and Invoices module is exactly that, a module.

On the other hand, the money you save on a single month of the monthly licenses of another program might be quite enough to pay for the necessary customizations to get what you want with SuiteCRM.

And in the end you can contribute that code to the Community and then, behold! SuiteCRM is a “fully functional open-source” (until somebody comes up with the next requirement)

:slight_smile:

You are probably right, but as a small business the technology know-how or the budget is lacking to support these changes. From this perspective it should just work.

Nevertheless, the software is amazing. But unfortunately not for us.t work and is SuiteCRM possible not a sweet fit :slight_smile:

As a tech lover myself I might dive into it.

Dear SuiteCRM community,

I am also working on the topic “converting an contract to invoice”.

My workflow: quote -> create contract -> convert invoice.

But there are still a few challenges from convert contract to invoice.

I already have created an button “coverToInvoice”.
As soon as I press the “convertToInvoice” button (from an contract), will opened an invoice.
Some information is taken from the contract, but unfortunately some is not.
I have graphed this for better understanding.



Can someone help me, please.

Here is my modified “converToInvoice”:

 * @author SalesAgility <info@salesagility.com>
 */

    if (!(ACLController::checkAccess('AOS_Invoices', 'edit', true))) {
        ACLController::displayNoAccess();
        die;
    }

    require_once('modules/AOS_Contracts/AOS_Contracts.php');
    require_once('modules/AOS_Invoices/AOS_Invoices.php');
    require_once('modules/AOS_Products/AOS_Products.php');

    global $timedate;
    //Setting values in Contracts
    $contract = new AOS_Contracts();
    $contract->retrieve($_REQUEST['record']);
    $contract->invoice_status = 'Invoiced';
    $contract->total_amt = format_number($contract->total_amt);
    $contract->discount_amount = format_number($contract->discount_amount);
    $contract->subtotal_amount = format_number($contract->subtotal_amount);
    $contract->tax_amount = format_number($contract->tax_amount);
    if ($contract->shipping_amount != null) {
        $contract->shipping_amount = format_number($contract->shipping_amount);
    }
    $contract->total_amount = format_number($contract->total_amount);
    $contract->save();

    //Setting Invoice Values
    $invoice = new AOS_Invoices();
    $rawRow = $contract->fetched_row;
    $rawRow['id'] = '';
    $rawRow['template_ddown_c'] = ' ';
    $rawRow['contract_number'] = $rawRow['number'];
    $rawRow['number'] = '';
    $dt = explode(' ', $rawRow['date_entered']);
    $rawRow['contact'] = '';
    $rawRow['account'] = '';
    $rawRow['billing_address_street'] = '';
    $rawRow['billing_account'] = '';
    $rawRow['billing_contact'] = '';
    $rawRow['date_created'] = $dt[0];
    $rawRow['invoice_date'] = date('Y-m-d');
    $rawRow['total_amt'] = format_number($rawRow['total_amt']);
    $rawRow['discount_amount'] = format_number($rawRow['discount_amount']);
    $rawRow['subtotal_amount'] = format_number($rawRow['subtotal_amount']);
    $rawRow['tax_amount'] = format_number($rawRow['tax_amount']);
    $rawRow['delivery_address_auftrag_c'] = '';
    $rawRow['date_entered'] = '';
    $rawRow['date_modified'] = '';
    if ($rawRow['shipping_amount'] != null) {
        $rawRow['shipping_amount'] = format_number($rawRow['shipping_amount']);
    }
    $rawRow['total_amount'] = format_number($rawRow['total_amount']);
    $invoice->populateFromRow($rawRow);
    $invoice->process_save_dates =false;
    $invoice->save();

    //Setting invoice contract relationship
    require_once('modules/Relationships/Relationship.php');
    $key = Relationship::retrieve_by_modules('AOS_Contracts', 'AOS_Invoices', $GLOBALS['db']);
    if (!empty($key)) {
        $contract->load_relationship($key);
        $contract->$key->add($invoice->id);
    }

    //Setting Group Line Items
    $sql = "SELECT * FROM aos_line_item_groups WHERE parent_type = 'AOS_Contracts' AND parent_id = '".$contract->id."' AND deleted = 0";
    $result = $this->bean->db->query($sql);
    $ContractToInvoiceGroupIds = array();
    while ($row = $this->bean->db->fetchByAssoc($result)) {
        $ContractGroupId = $row['id'];
        $row['id'] = '';
        $row['parent_id'] = $invoice->id;
        $row['parent_type'] = 'AOS_Invoices';
        if ($row['total_amt'] != null) {
            $row['total_amt'] = format_number($row['total_amt']);
        }
        if ($row['discount_amount'] != null) {
            $row['discount_amount'] = format_number($row['discount_amount']);
        }
        if ($row['subtotal_amount'] != null) {
            $row['subtotal_amount'] = format_number($row['subtotal_amount']);
        }
        if ($row['tax_amount'] != null) {
            $row['tax_amount'] = format_number($row['tax_amount']);
        }
        if ($row['subtotal_tax_amount'] != null) {
            $row['subtotal_tax_amount'] = format_number($row['subtotal_tax_amount']);
        }
        if ($row['total_amount'] != null) {
            $row['total_amount'] = format_number($row['total_amount']);
        }
        $group_invoice = new AOS_Line_Item_Groups();
        $group_invoice->populateFromRow($row);
        $group_invoice->save();
        $ContractToInvoiceGroupIds[$ContractGroupId] = $group_invoice->id;
    }

    //Setting Line Items
    $sql = "SELECT * FROM aos_products_contracts WHERE parent_type = 'AOS_Contracts' AND parent_id = '".$contract->id."' AND deleted = 0";
    $result = $this->bean->db->query($sql);
    while ($row = $this->bean->db->fetchByAssoc($result)) {
        $row['id'] = '';
        $row['parent_id'] = $invoice->id;
        $row['parent_type'] = 'AOS_Invoices';
        $row['group_id'] = $ContractToInvoiceGroupIds[$row['group_id']];
        if ($row['product_cost_price'] != null) {
            $row['product_cost_price'] = format_number($row['product_cost_price']);
        }
        $row['product_list_price'] = format_number($row['product_list_price']);
        if ($row['product_discount'] != null) {
            $row['product_discount'] = format_number($row['product_discount']);
            $row['product_discount_amount'] = format_number($row['product_discount_amount']);
        }
        $row['product_unit_price'] = format_number($row['product_unit_price']);
        $row['vat_amt'] = format_number($row['vat_amt']);
        $row['product_total_price'] = format_number($row['product_total_price']);
        $row['product_qty'] = format_number($row['product_qty']);
        $prod_invoice = new AOS_Products_Contracts();
        $prod_invoice->populateFromRow($row);
        $prod_invoice->save();
    }
    ob_clean();
    header('Location: index.php?module=AOS_Invoices&action=EditView&record='.$invoice->id);

You’re not retrieving account information anywhere in the code you pasted. Basically it doesn’t get set for the $invoice. In fact, you’re setting billing_account and billing_contact of $rawRow to empty strings, so you’re getting exactly what is expected.

$invoice = new AOS_Invoices();

$rawRow[‘billing_account’] = ‘’;
$rawRow[‘billing_contact’] = ‘’;

$invoice->populateFromRow($rawRow);

P.S. I think you could’ve started a new thread for this instead of resurrecting an old one :wink:

Dear TLi, many thanks for your kindly reply. :slight_smile:
In the meantime I have got a little bit further. The products are included in the invoice - I’m really happy about it :slight_smile:

Unfortunately, I do not quite understand what you are saying with your kind advice.

I have modified the contact information accordingly:

$rawRow[‘contact’] = ‘contact’;
$rawRow[‘account’] = ‘account’;
$rawRow[‘billing_address_street’] = ‘billing_address_street’;
$rawRow[‘billing_account’] = ‘billing_account’;
$rawRow[‘billing_contact’] = ‘billing_contact’;

The result look like this:

I have the feeling that I am close to the solution. But I don’t know what I’m doing wrong. Can you help me, please.

This is my current “converToInvoice.php”:

  • @author SalesAgility info@salesagility.com
    */

    if (!(ACLController::checkAccess(‘AOS_Invoices’, ‘edit’, true))) {
    ACLController::displayNoAccess();
    die;
    }

    require_once(‘modules/AOS_Contracts/AOS_Contracts.php’);
    require_once(‘modules/AOS_Invoices/AOS_Invoices.php’);

    global $timedate;
    //Setting values in Contracts
    $contract = new AOS_Contracts();
    $contract->retrieve($_REQUEST[‘record’]);
    $contract->invoice_status = ‘Invoiced’;
    $contract->total_amt = format_number($contract->total_amt);
    $contract->discount_amount = format_number($contract->discount_amount);
    $contract->subtotal_amount = format_number($contract->subtotal_amount);
    $contract->tax_amount = format_number($contract->tax_amount);
    if ($contract->shipping_amount != null) {
    $contract->shipping_amount = format_number($contract->shipping_amount);
    }
    $contract->total_amount = format_number($contract->total_amount);
    $contract->save();

    //Setting Invoice Values
    $invoice = new AOS_Invoices();
    $rawRow = $contract->fetched_row;
    $rawRow[‘id’] = ‘’;
    $rawRow[‘template_ddown_c’] = ’ ‘;
    $rawRow[‘contract_number’] = $rawRow[‘number’];
    $rawRow[‘number’] = ‘’;
    $dt = explode(’ ', $rawRow[‘date_entered’]);
    $rawRow[‘contact’] = ‘contact’;
    $rawRow[‘account’] = ‘account’;
    $rawRow[‘billing_address_street’] = ‘billing_address_street’;
    $rawRow[‘billing_account’] = ‘billing_account’;
    $rawRow[‘billing_contact’] = ‘billing_contact’;
    $rawRow[‘date_created’] = $dt[0];
    $rawRow[‘invoice_date’] = date(‘Y-m-d’);
    $rawRow[‘total_amt’] = format_number($rawRow[‘total_amt’]);
    $rawRow[‘discount_amount’] = format_number($rawRow[‘discount_amount’]);
    $rawRow[‘subtotal_amount’] = format_number($rawRow[‘subtotal_amount’]);
    $rawRow[‘tax_amount’] = format_number($rawRow[‘tax_amount’]);
    $rawRow[‘delivery_address_auftrag_c’] = ‘’;
    $rawRow[‘date_entered’] = ‘’;
    $rawRow[‘date_modified’] = ‘’;
    if ($rawRow[‘shipping_amount’] != null) {
    $rawRow[‘shipping_amount’] = format_number($rawRow[‘shipping_amount’]);
    }
    $rawRow[‘total_amount’] = format_number($rawRow[‘total_amount’]);
    $invoice->populateFromRow($rawRow);
    $invoice->process_save_dates =false;
    $invoice->save();

    $group_id_map = array();

    //Setting Group Line Items
    $sql = “SELECT * FROM aos_line_item_groups WHERE parent_type = ‘AOS_Contracts’ AND parent_id = '”.$contract->id."’ AND deleted = 0";

    $result = $this->bean->db->query($sql);
    while ($row = $this->bean->db->fetchByAssoc($result)) {
    $old_id = $row[‘id’];
    $row[‘id’] = ‘’;
    $row[‘parent_id’] = $invoice->id;
    $row[‘parent_type’] = ‘AOS_Invoices’;
    if ($row[‘total_amt’] != null) {
    $row[‘total_amt’] = format_number($row[‘total_amt’]);
    }
    if ($row[‘discount_amount’] != null) {
    $row[‘discount_amount’] = format_number($row[‘discount_amount’]);
    }
    if ($row[‘subtotal_amount’] != null) {
    $row[‘subtotal_amount’] = format_number($row[‘subtotal_amount’]);
    }
    if ($row[‘tax_amount’] != null) {
    $row[‘tax_amount’] = format_number($row[‘tax_amount’]);
    }
    if ($row[‘subtotal_tax_amount’] != null) {
    $row[‘subtotal_tax_amount’] = format_number($row[‘subtotal_tax_amount’]);
    }
    if ($row[‘total_amount’] != null) {
    $row[‘total_amount’] = format_number($row[‘total_amount’]);
    }
    $group_invoice = new AOS_Line_Item_Groups();
    $group_invoice->populateFromRow($row);
    $group_invoice->save();
    $group_id_map[$old_id] = $group_invoice->id;
    }

    //Setting Line Items
    $sql = “SELECT * FROM aos_products_quotes WHERE parent_type = ‘AOS_Contracts’ AND parent_id = '”.$contract->id."’ AND deleted = 0";
    $result = $this->bean->db->query($sql);
    while ($row = $this->bean->db->fetchByAssoc($result)) {
    $row[‘id’] = ‘’;
    $row[‘parent_id’] = $invoice->id;
    $row[‘parent_type’] = ‘AOS_Invoices’;
    if ($row[‘product_cost_price’] != null) {
    $row[‘product_cost_price’] = format_number($row[‘product_cost_price’]);
    }
    $row[‘product_list_price’] = format_number($row[‘product_list_price’]);
    if ($row[‘product_discount’] != null) {
    $row[‘product_discount’] = format_number($row[‘product_discount’]);
    $row[‘product_discount_amount’] = format_number($row[‘product_discount_amount’]);
    }
    $row[‘product_unit_price’] = format_number($row[‘product_unit_price’]);
    $row[‘vat_amt’] = format_number($row[‘vat_amt’]);
    $row[‘product_total_price’] = format_number($row[‘product_total_price’]);
    $row[‘product_qty’] = format_number($row[‘product_qty’]);
    $row[‘group_id’] = $group_id_map[$row[‘group_id’]];

      $prod_invoice = new AOS_Products_Quotes();
      $prod_invoice->populateFromRow($row);
      $prod_invoice->save();
    

    }

    //Setting invoice contract relationship
    require_once(‘modules/Relationships/Relationship.php’);
    $key = Relationship::retrieve_by_modules(‘AOS_Contracts’, ‘AOS_Invoices’, $GLOBALS[‘db’]);
    if (!empty($key)) {
    $contract->load_relationship($key);
    $contract->$key->add($invoice->id);
    }
    ob_clean();
    header(‘Location: index.php?module=AOS_Invoices&action=EditView&record=’.$invoice->id);

I think in the long run you’d be better off completely redoing the conversion action using BeanFactory instead of direct queries and so on. IMO the one in SuiteCRM isn’'t very well made. But that is for another discussion.

For the account and contact values I think you’re actually using wrong field names. IIRC you need billing_account_id instead of billing_account (check database tables for the correct names). The thing is that even though in the view you see the name of an account and/or contact, SuiteCRM is dealing with their UUIDs. So make sure you’re using the correct field names.

Also at $rawRow[‘billing_address_street’] = ‘billing_address_street’; you are actually setting the value as a string called billing_address_street. Are you sure you shouldn’t be looking for a variable or object property?

If neither helps. I can later check how I’ve recreated the conversion in the past and see if I can give any other pointers. I remember I ended up pretty muchredoing the conversion altogether.

You are very friendly. Thanks a lot for this!

I have now tested a few things:

$rawRow[‘contact_id’] = ‘’;
$rawRow[‘contract_account’] = ‘’;
$rawRow[‘contract_account_id’] = ‘’;
$rawRow[‘billing_address_street’] = ‘’;
$rawRow[‘billing_account’] = ‘billing_account_id’;
$rawRow[‘billing_contact’] = ‘billing_contact_id’;
$rawRow[‘delivery_address_auftrag_c’] = ‘’;

and some other variations, but the result is always the same :frowning:
It would be very kind if you could help further.
Many thanks!

You gave me a good hint about the database. Now, I have compared aos_quotes and aos_contracts in the database. In the aos_contracts table there are no fields: billing_account_id, billing_contact_id, billing_address_street, etc. I think that’s the reason. What is your opinion?
Here my aos_quotes:


The marked fields do not exist in the aos_contracts table.

The contract doesn’t record account’s address; if you make a PDF template, the up-to-date address details are fetched from the Account and Contact’s individual records. Contract only keeps track of the UUIDs.

I’m assuming (can’t check right now) that in the contract DB table there is account_id or something like that (and contact_id) - basically the Account(/Contact) you are connecting the contract with. What you should do is use the BeanFactory to get the correct account bean and populate the Invoice’s billing details from there. Here’s a quick and scratchy pseudocode for illustration; I’m letting you figure out the rest :wink: :

$accountBean = Beanfactory::getBean('Accounts', $contract['account_id']);
$invoice['billing_account_id'] = $accountBean->id;
$invoice['billing_address_street'] = $accountBean->billing_address_street;

And so on. While you are at it, consider changing all work to use BeanFactory - that’s the recommended way nowadays and good practice for you. It’s also simpler IMO.

EDIT: Cleaned up the code a bit.

1 Like

Dear TLi and all other friendly supporter :slight_smile:

at this point I’m stuck.

I spent now 5 days, to transferring the contract address (billing_account and billing_contact) datas to the respective invoice.

I do not understand why I cannot transfer this account data from the contract to the invoice. All other data is transferred from the contract to the invoice.

I also worked with BeanFactory. But apparently I lack understanding Probably it´s just missing one single line or tow, to the destination.

If I enter and save the address manually, so I can see the addresse (not the account name or contact name) in the table aos_contracts_cstm (via phpmyadmin).

Can anyone help me, please?