|
Hello Dave,
It is really good accounting system with simple database structure.
I working on a cargo processing system which I try to post the invoice generated in the cargo system into PhreeBooks. So far I have done that. The invoice shown in cargo system and shown at PhreeBooks. I am planning to use all other functions in PhreeBooks such as payment receipts. However I got error message " trail balance out of balance ....". After I did GL Balance Errors, the message is gone.
To make an invoice, I update these tables: pb_journal_main, pb_journal_item,pb_accounts_history,pb_chart_of_accounts_history and pb_current_status.
The following is my php function:
function add_outbound_invoice($shpmnt_id)
{
$this->db->where('id', $shpmnt_id);
$query = $this->db->get('shipments');
$row = $query->row(1);
$client_id = $row->client_id;
// Get client address(only one)
$this->db->where('ref_id', $client_id);
$address = $this->db->get('pb_address_book');
$addr_row = $address->row(1);
$bill_address_id = $addr_row->address_id;
$bill_primary_name = $addr_row->primary_name;
if($addr_row->address1){
$bill_address1 = $addr_row->address1;
}else{
$bill_address1 = ' ';
}
if($addr_row->city_town){
$bill_city_town = $addr_row->city_town;
}else{
$bill_city_town = ' ';
}
if($addr_row->state_province){
$bill_state_province = $addr_row->state_province;
}else{
$bill_state_province = ' ';
}
if($addr_row->postal_code){
$bill_postal_code = $addr_row->postal_code;
}else{
$bill_postal_code = ' ';
}
$bill_country_code = $addr_row->country_code;
// Add Invoices
//get charge list not billed
$this->db->where('chg_prepaid', 1);
$this->db->where('billed', 0);
$this->db->where('`a_chg`*`vol` >', 0.00);
$this->db->where('shpmnt_id', $shpmnt_id);
$query = $this->db->get('charges');
if ($query->num_rows() > 0){
//Get ttl a_chg
$this->db->select_sum('`a_chg`*`vol`', 'ttl_amnt');
$this->db->where('chg_prepaid', 1);
$this->db->where('billed', 0);
$this->db->where('a_chg >', 0.00);
$this->db->where('shpmnt_id', $shpmnt_id);
$ttl = $this->db->get('charges');
$amnt = $ttl->row(1);
$ttl_amnt = $amnt->ttl_amnt;
// Get last invoice number
$inv_num = $this->db->get('pb_current_status');
$inv = $inv_num->row(1);
$last_invoice_number = $inv->next_inv_num;
//Prepare Data For pb_journal_main
$new_journal = array(
'period' => date("n"),
'journal_id' => 12,
'post_date' => date("Y-m-d"),
'store_id' => 0,
'description' => 'Sales/Invoice Entry',
'total_amount' => $ttl_amnt,
'purchase_invoice_id' => $last_invoice_number,
'gl_acct_id' => 1200,
'bill_acct_id' => $client_id,
'so_po_ref_id' => $shpmnt_id,
'bill_address_id' => $bill_address_id,
'bill_primary_name' => $bill_primary_name,
'bill_address1' => $bill_address1,
'bill_city_town' => $bill_city_town,
'bill_state_province' => $bill_state_province,
'bill_postal_code' => $bill_postal_code,
'bill_country_code' => $bill_country_code,
'ship_primary_name' => $bill_primary_name,
'ship_address1' => $bill_address1,
'ship_city_town' => $bill_city_town,
'ship_state_province' => $bill_state_province,
'ship_postal_code' => $bill_postal_code,
'ship_country_code' => $bill_country_code);
$this->db->insert('pb_journal_main', $new_journal);
$ref_id = $this->db->insert_id();
// Add to pb_journal_item
foreach ($query->result() as $row){
$new_item = array(
'ref_id' => $ref_id,
'so_po_item_ref_id' => $row->id,
'gl_type' => 'sos',
'reconciled' => 0,
'sku' => $row->chg_code,
'qty' => 1,
'credit_amount' => ($row->vol)*($row->a_chg),
'gl_account' => 4430,
'post_date' => date("Y-m-d"),
'debit_amount' => 0.00,
'description' => $row->comment);
$this->db->insert('pb_journal_item', $new_item);
// Flag table - charges as billed
$flag = array(
'billed' => 1);
$this->db->where('id', $row->id);
$this->db->update('charges', $flag);
}
// Add the total debit_amount to pb_journal_item
$ttl_debit = array(
'ref_id' => $ref_id,
'so_po_item_ref_id' => 0,
'gl_type' => 'ttl',
'reconciled' => 0,
'qty' => 0,
'debit_amount' => $ttl_amnt,
'gl_account' => 1200,
'post_date' => date("Y-m-d"),
'credit_amount' => 0.00,
'description' => 'Sales/Invoice Total');
$this->db->insert('pb_journal_item', $ttl_debit);
//Add to table pb_account_history
$new_acct_history = array(
'ref_id' => $ref_id,
'acct_id' => $client_id,
'amount' => $ttl_amnt,
'purchase_invoice_id' => $last_invoice_number,
'so_po_ref_id' => $shpmnt_id,
'post_date' => date("Y-m-d"));
$this->db->insert('pb_accounts_history', $new_acct_history);
// Update table pb_chart_account_history
$this->db->where('account_id', 4430);
$this->db->where('period', date("n"));
$query = $this->db->get('pb_chart_of_accounts_history');
$row = $query->row(1);
$credit_amount = $row->credit_amount + $ttl_amnt;
$update_chart = array(
'credit_amount' => $credit_amount,
'last_update' => date("Y-m-d"));
$this->db->where('id', $row->id);
$this->db->update('pb_chart_of_accounts_history', $update_chart);
// Update last_invoice_number for pb_current_status
$new_status = array(
'next_inv_num' => ($last_invoice_number+1));
$this->db->where('id', 1);
$this->db->update('pb_current_status', $new_status);
}
// Add Credit
//get charge list not billed and minus as credit
$this->db->where('chg_prepaid', 1);
$this->db->where('billed', 0);
$this->db->where('`a_chg`*`vol` <', 0.00);
$this->db->where('shpmnt_id', $shpmnt_id);
$query = $this->db->get('charges');
if ($query->num_rows() > 0){
//Get ttl a_chg
$this->db->select_sum('`a_chg`*`vol`', 'ttl_amnt');
$this->db->where('chg_prepaid', 1);
$this->db->where('billed', 0);
$this->db->where('a_chg >', 0.00);
$this->db->where('shpmnt_id', $shpmnt_id);
$ttl = $this->db->get('charges');
$amnt = $ttl->row(1);
$ttl_amnt = $amnt->ttl_amnt;
// Get last invoice number
$inv_num = $this->db->get('pb_current_status');
$inv = $inv_num->row(1);
$last_invoice_number = $inv->next_cm_num;
//Prepare Data For pb_journal_main
$new_journal = array(
'period' => date("n"),
'journal_id' => 13,
'post_date' => date("Y-m-d"),
'store_id' => 0,
'description' => 'Customer Credit Memo Entry',
'total_amount' => abs($ttl_amnt),
'purchase_invoice_id' => $last_invoice_number,
'gl_acct_id' => 1200,
'bill_acct_id' => $client_id,
'so_po_ref_id' => $shpmnt_id,
'bill_address_id' => $bill_address_id,
'bill_primary_name' => $bill_primary_name,
'bill_address1' => $bill_address1,
'bill_city_town' => $bill_city_town,
'bill_state_province' => $bill_state_province,
'bill_postal_code' => $bill_postal_code,
'bill_country_code' => $bill_country_code,
'ship_primary_name' => $bill_primary_name,
'ship_address1' => $bill_address1,
'ship_city_town' => $bill_city_town,
'ship_state_province' => $bill_state_province,
'ship_postal_code' => $bill_postal_code,
'ship_country_code' => $bill_country_code);
$this->db->insert('pb_journal_main', $new_journal);
$ref_id = $this->db->insert_id();
// Add to pb_journal_item
foreach ($query->result() as $row){
$new_item = array(
'ref_id' => $ref_id,
'so_po_item_ref_id' => $row->id,
'gl_type' => 'sos',
'reconciled' => 0,
'sku' => $row->chg_code,
'qty' => 1,
'debit_amount' => abs(($row->vol)*($row->a_chg)),
'gl_account' => 1200,
'post_date' => date("Y-m-d"),
'credit_amount' => 0.00,
'description' => 'Credit For '.$row->comment);
$this->db->insert('pb_journal_item', $new_item);
// Flag table - charges as billed
$flag = array(
'billed' => 1);
$this->db->where('id', $row->id);
$this->db->update('charges', $flag);
}
// Add the total debit_amount to pb_journal_item
$ttl_debit = array(
'ref_id' => $ref_id,
'so_po_item_ref_id' => 0,
'gl_type' => 'ttl',
'reconciled' => 0,
'qty' => 0,
'credit_amount' => abs($ttl_amnt),
'gl_account' => 1200,
'post_date' => date("Y-m-d"),
'debit_amount' => 0.00,
'description' => 'Customer Credit Memo Total');
$this->db->insert('pb_journal_item', $ttl_debit);
//Add to table pb_account_history
$new_acct_history = array(
'ref_id' => $ref_id,
'acct_id' => $client_id,
'amount' => abs($ttl_amnt),
'purchase_invoice_id' => $last_invoice_number,
'so_po_ref_id' => $shpmnt_id,
'post_date' => date("Y-m-d"));
$this->db->insert('pb_accounts_history', $new_acct_history);
// Update table pb_chart_accounts_history
$this->db->where('account_id', 4430);
$this->db->where('period', date("n"));
$query = $this->db->get('pb_chart_of_accounts_history');
$row = $query->row(1);
$debit_amount = $row->debit_amount + abs($ttl_amnt);
$update_chart = array(
'debit_amount' => $debit_amount,
'last_update' => date("Y-m-d"));
$this->db->where('id', $row->id);
$this->db->update('pb_chart_of_accounts_history', $update_chart);
// Update credit memo number for pb_current_status
$next_cm_num_end = substr($last_invoice_number,2)+1;
$new_status = array(
'next_cm_num' => 'CM'.$next_cm_num_end);
$this->db->where('id', 1);
$this->db->update('pb_current_status', $new_status);
}
This function does two things: One add invoice and other add a credit.
I appreciate that you can give me any hint.
Thanks.
|
