/**
* show method
*
* @return void
*/
public function show()
{
$this->set('title_for_layout', __d('webzash', 'Chart Of Accounts'));
$this->set('actionlinks', array(array('controller' => 'groups', 'action' => 'add', 'title' => 'Add Group'), array('controller' => 'ledgers', 'action' => 'add', 'title' => 'Add Ledger')));
$accountlist = new AccountList();
$accountlist->Group =& $this->Group;
$accountlist->Ledger =& $this->Ledger;
$accountlist->only_opening = false;
$accountlist->start_date = null;
$accountlist->end_date = null;
$accountlist->affects_gross = -1;
$accountlist->start(0);
$this->set('accountlist', $accountlist);
$opdiff = $this->Ledger->getOpeningDiff();
$this->set('opdiff', $opdiff);
return;
}
/**
* carry forward to next financial year method
*
* @return void
*/
public function cf()
{
$this->set('title_for_layout', __d('webzash', 'Carry Forward Account'));
$this->Wzaccount->useDbConfig = 'wz';
/* on POST */
if ($this->request->is('post') || $this->request->is('put')) {
/* Check if database engine is supported */
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Sqlserver') {
$this->Session->setFlash(__d('webzash', 'Sorry, currently MS SQL Server is not supported. We might add it soon, if you want to help let us know.'), 'danger');
return;
}
/* Check if label already exists */
$count = $this->Wzaccount->find('count', array('conditions' => array('Wzaccount.label' => $this->request->data['Wzaccount']['label'])));
if ($count != 0) {
$this->Session->setFlash(__d('webzash', 'Label is already in use. Please, try again.'), 'danger');
return;
}
/* Check if all values required are present */
if (empty($this->request->data['Wzaccount']['label'])) {
$this->Session->setFlash(__d('webzash', 'Label is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['name'])) {
$this->Session->setFlash(__d('webzash', 'Company / Personal Name is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['date_format'])) {
$this->Session->setFlash(__d('webzash', 'Date format is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['fy_start'])) {
$this->Session->setFlash(__d('webzash', 'Financial year start is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['fy_end'])) {
$this->Session->setFlash(__d('webzash', 'Financial year end is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['db_database'])) {
$this->Session->setFlash(__d('webzash', 'Database name is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['db_host'])) {
$this->Session->setFlash(__d('webzash', 'Database host is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['db_port'])) {
$this->Session->setFlash(__d('webzash', 'Database port is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['db_login'])) {
$this->Session->setFlash(__d('webzash', 'Database login is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['db_password'])) {
$this->Session->setFlash(__d('webzash', 'Database password is required.'), 'danger');
return;
}
/* Check financial year start is before end */
$fy_start = strtotime($this->request->data['Wzaccount']['fy_start'] . ' 00:00:00');
$fy_end = strtotime($this->request->data['Wzaccount']['fy_end'] . ' 00:00:00');
if ($fy_start >= $fy_end) {
$this->Session->setFlash(__d('webzash', 'Financial year start date cannot be after end date.'), 'danger');
return;
}
/* Only check for valid input data, save later */
$check_data = array('Wzaccount' => array('label' => $this->request->data['Wzaccount']['label'], 'db_datasource' => $this->request->data['Wzaccount']['db_datasource'], 'db_database' => $this->request->data['Wzaccount']['db_database'], 'db_host' => $this->request->data['Wzaccount']['db_host'], 'db_port' => $this->request->data['Wzaccount']['db_port'], 'db_login' => $this->request->data['Wzaccount']['db_login'], 'db_password' => $this->request->data['Wzaccount']['db_password'], 'db_prefix' => strtolower($this->request->data['Wzaccount']['db_prefix']), 'db_schema' => $this->request->data['Wzaccount']['db_schema'], 'db_unixsocket' => '', 'db_settings' => $this->request->data['Wzaccount']['db_settings']));
if ($this->request->data['Wzaccount']['db_persistent'] == 1) {
$check_data['Wzaccount']['db_persistent'] = 1;
} else {
$check_data['Wzaccount']['db_persistent'] = 0;
}
$this->Wzaccount->set($check_data);
if (!$this->Wzaccount->validates()) {
foreach ($this->Wzaccount->validationErrors as $field => $msg) {
$errmsg = $msg[0];
break;
}
$this->Session->setFlash($errmsg, 'danger');
return;
}
/* Create account database configuration */
$wz_newconfig['datasource'] = $this->request->data['Wzaccount']['db_datasource'];
$wz_newconfig['database'] = $this->request->data['Wzaccount']['db_database'];
$wz_newconfig['host'] = $this->request->data['Wzaccount']['db_host'];
$wz_newconfig['port'] = $this->request->data['Wzaccount']['db_port'];
$wz_newconfig['login'] = $this->request->data['Wzaccount']['db_login'];
$wz_newconfig['password'] = $this->request->data['Wzaccount']['db_password'];
$wz_newconfig['prefix'] = strtolower($this->request->data['Wzaccount']['db_prefix']);
$wz_newconfig['schema'] = $this->request->data['Wzaccount']['db_schema'];
if ($this->request->data['Wzaccount']['db_persistent'] == 1) {
$wz_newconfig['persistent'] = TRUE;
} else {
$wz_newconfig['persistent'] = FALSE;
}
/**
* TODO
* $wz_newconfig['schema'] = $this->request->data['Wzaccount']['db_schema'];
* $wz_newconfig['unixsocket'] = $this->request->data['Wzaccount']['db_unixsocket'];
*/
$wz_newconfig['settings'] = $this->request->data['Wzaccount']['db_settings'];
/* Create account database config and try to connect to it */
try {
ConnectionManager::create('wz_newconfig', $wz_newconfig);
} catch (Exception $e) {
$this->Session->setFlash(__d('webzash', 'Cound not connect to database. Please, check your database settings.'), 'danger');
return;
}
/* Read old settings */
$this->OldSetting = new $this->Setting();
$old_account_setting = $this->OldSetting->findById(1);
if (!$old_account_setting) {
$this->Session->setFlash(__d('webzash', 'Could not read original settings. Please, try again.'), 'danger');
return;
}
/* Connection successfull, next check if any table names clash */
$db = ConnectionManager::getDataSource('wz_newconfig');
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Mysql') {
$existing_tables = $db->query("show tables");
/*
Format of $existing_tables is
array(
0 => array(
'TABLE_NAMES' => array(
'Tables_in_<dbname>' => 'entries'
)
),
...
*/
/* Array of new tables that are to be created */
$new_tables = array($wz_newconfig['prefix'] . 'entries', $wz_newconfig['prefix'] . 'entryitems', $wz_newconfig['prefix'] . 'entrytypes', $wz_newconfig['prefix'] . 'groups', $wz_newconfig['prefix'] . 'ledgers', $wz_newconfig['prefix'] . 'logs', $wz_newconfig['prefix'] . 'settings', $wz_newconfig['prefix'] . 'tags');
/* Check if any table from $new_table already exists */
$table_exisits = false;
foreach ($existing_tables as $row => $table_1) {
foreach ($table_1 as $row => $table_2) {
foreach ($table_2 as $row => $table) {
if (in_array(strtolower($table), $new_tables)) {
$table_exisits = TRUE;
$this->Session->setFlash(__d('webzash', 'Table with the same name as "%s" already existsin the "%s" database. Please, use another database or use a different prefix.', $table, $wz_newconfig['database']), 'danger');
}
}
}
}
if ($table_exisits == TRUE) {
return;
}
}
/**
* At this point the connection is successfull and there are no table clashes,
* we can create the application specific tables.
*/
/* Read the MySQL database creation schema from the Config folder */
App::uses('File', 'Utility');
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Mysql') {
$schema_filepath = App::pluginPath('Webzash') . 'Config/Schema.Mysql.sql';
} else {
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Postgres') {
$schema_filepath = App::pluginPath('Webzash') . 'Config/Schema.Postgres.sql';
}
}
$schema_file = new File($schema_filepath, false);
$schema = $schema_file->read(true, 'r');
/* Add prefix to the table names in the schema */
$prefix_schema = str_replace('%_PREFIX_%', $wz_newconfig['prefix'], $schema);
/* Add decimal places */
$final_schema = str_replace('%_DECIMAL_%', $old_account_setting['Setting']['decimal_places'], $prefix_schema);
/* Create tables */
try {
$db->rawQuery($final_schema);
} catch (Exception $e) {
$this->Session->setFlash(__d('webzash', 'Oh Snap ! Something went wrong while creating the database tables. Please check your settings and try again.'), 'danger');
return;
}
/******* Add initial data ********/
/* CF groups and ledgers */
$assetsList = new AccountList();
$assetsList->Group =& $this->Group;
$assetsList->Ledger =& $this->Ledger;
$assetsList->only_opening = false;
$assetsList->start_date = null;
$assetsList->end_date = null;
$assetsList->affects_gross = -1;
$assetsList->start(1);
$this->_extract_groups_ledgers($assetsList, true);
$liabilitiesList = new AccountList();
$liabilitiesList->Group =& $this->Group;
$liabilitiesList->Ledger =& $this->Ledger;
$liabilitiesList->only_opening = false;
$liabilitiesList->start_date = null;
$liabilitiesList->end_date = null;
$liabilitiesList->affects_gross = -1;
$liabilitiesList->start(2);
$this->_extract_groups_ledgers($liabilitiesList, true);
$incomesList = new AccountList();
$incomesList->Group =& $this->Group;
$incomesList->Ledger =& $this->Ledger;
$incomesList->only_opening = false;
$incomesList->start_date = null;
$incomesList->end_date = null;
$incomesList->affects_gross = -1;
$incomesList->start(3);
$this->_extract_groups_ledgers($incomesList, false);
$expenseList = new AccountList();
$expenseList->Group =& $this->Group;
$expenseList->Ledger =& $this->Ledger;
$expenseList->only_opening = false;
$expenseList->start_date = null;
$expenseList->end_date = null;
$expenseList->affects_gross = -1;
$expenseList->start(4);
$this->_extract_groups_ledgers($expenseList, false);
$this->NewGroup = new $this->Group();
$this->NewGroup->useDbConfig = 'wz_newconfig';
foreach ($this->groups_list as $row => $group) {
$this->NewGroup->create();
if (!$this->NewGroup->save($group, false)) {
$this->Session->setFlash(__d('webzash', 'Account database created, but could not carry forward account groups. Please, try again.'), 'danger');
return;
}
}
$this->NewLedger = new $this->Ledger();
$this->NewLedger->useDbConfig = 'wz_newconfig';
foreach ($this->ledgers_list as $row => $ledger) {
$this->NewLedger->create();
if (!$this->NewLedger->save($ledger, false)) {
$this->Session->setFlash(__d('webzash', 'Account database created, but could not carry forward account ledgers. Please, try again.'), 'danger');
return;
}
}
/* CF Entrytypes */
$this->OldEntrytype = new $this->Entrytype();
$old_entrytypes = $this->OldEntrytype->find('all');
$this->NewEntrytype = new $this->Entrytype();
$this->NewEntrytype->useDbConfig = 'wz_newconfig';
foreach ($old_entrytypes as $row => $entrytype) {
$this->NewEntrytype->create();
if (!$this->NewEntrytype->save($entrytype)) {
$this->Session->setFlash(__d('webzash', 'Account database created, but could not carry forward entrytypes. Please, try again.'), 'danger');
return;
}
}
/* CF Tags */
$this->OldTag = new $this->Tag();
$old_tags = $this->OldTag->find('all');
$this->NewTag = new $this->Tag();
$this->NewTag->useDbConfig = 'wz_newconfig';
foreach ($old_tags as $row => $tag) {
$this->NewTag->create();
if (!$this->NewTag->save($tag)) {
$this->Session->setFlash(__d('webzash', 'Account database created, but could not carry forward tags. Please, try again.'), 'danger');
return;
}
}
/* CF settings */
$this->NewSetting = new $this->Setting();
$this->NewSetting->useDbConfig = 'wz_newconfig';
$new_account_setting = array('Setting' => array('id' => '1', 'name' => $this->request->data['Wzaccount']['name'], 'address' => $old_account_setting['Setting']['address'], 'email' => $old_account_setting['Setting']['email'], 'fy_start' => dateToSql($this->request->data['Wzaccount']['fy_start']), 'fy_end' => dateToSql($this->request->data['Wzaccount']['fy_end']), 'currency_symbol' => $old_account_setting['Setting']['currency_symbol'], 'currency_format' => $old_account_setting['Setting']['currency_format'], 'decimal_places' => $old_account_setting['Setting']['decimal_places'], 'date_format' => $this->request->data['Wzaccount']['date_format'], 'timezone' => 'UTC', 'manage_inventory' => 0, 'account_locked' => 0, 'email_use_default' => $old_account_setting['Setting']['email_use_default'], 'email_protocol' => $old_account_setting['Setting']['email_protocol'], 'email_host' => $old_account_setting['Setting']['email_host'], 'email_port' => $old_account_setting['Setting']['email_port'], 'email_tls' => $old_account_setting['Setting']['email_tls'], 'email_username' => $old_account_setting['Setting']['email_username'], 'email_password' => $old_account_setting['Setting']['email_password'], 'email_from' => $old_account_setting['Setting']['email_from'], 'print_paper_height' => $old_account_setting['Setting']['print_paper_height'], 'print_paper_width' => $old_account_setting['Setting']['print_paper_width'], 'print_margin_top' => $old_account_setting['Setting']['print_margin_top'], 'print_margin_bottom' => $old_account_setting['Setting']['print_margin_bottom'], 'print_margin_left' => $old_account_setting['Setting']['print_margin_left'], 'print_margin_right' => $old_account_setting['Setting']['print_margin_right'], 'print_orientation' => $old_account_setting['Setting']['print_orientation'], 'print_page_format' => $old_account_setting['Setting']['print_page_format'], 'database_version' => $old_account_setting['Setting']['database_version'], 'settings' => $old_account_setting['Setting']['settings']));
$this->NewSetting->create();
if (!$this->NewSetting->save($new_account_setting)) {
foreach ($this->NewSetting->validationErrors as $field => $msg) {
$errmsg = $msg[0];
break;
}
$this->Session->setFlash(__d('webzash', 'Account database created, but account settings could not be saved. Please, try again. Error is : "%s".', $errmsg), 'danger');
return;
}
/******* Add to wzaccount table *******/
$account_config = array('Wzaccount' => array('label' => $this->request->data['Wzaccount']['label'], 'db_datasource' => $this->request->data['Wzaccount']['db_datasource'], 'db_database' => $this->request->data['Wzaccount']['db_database'], 'db_host' => $this->request->data['Wzaccount']['db_host'], 'db_port' => $this->request->data['Wzaccount']['db_port'], 'db_login' => $this->request->data['Wzaccount']['db_login'], 'db_password' => $this->request->data['Wzaccount']['db_password'], 'db_prefix' => strtolower($this->request->data['Wzaccount']['db_prefix']), 'db_schema' => $this->request->data['Wzaccount']['db_schema'], 'db_unixsocket' => '', 'db_settings' => $this->request->data['Wzaccount']['db_settings']));
if ($this->request->data['Wzaccount']['db_persistent'] == 1) {
$account_config['Wzaccount']['db_persistent'] = 1;
} else {
$account_config['Wzaccount']['db_persistent'] = 0;
}
/* Save database configuration */
$this->Wzaccount->create();
if ($this->Wzaccount->save($account_config)) {
$this->Session->setFlash(__d('webzash', 'Account created.'), 'success');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzaccounts', 'action' => 'index'));
} else {
foreach ($this->Wzaccount->validationErrors as $field => $msg) {
$errmsg = $msg[0];
break;
}
$this->Session->setFlash(__d('webzash', 'Account database created, but account config could not be saved. Please, try again. Error is : "%s".', $errmsg), 'danger');
return;
}
}
}
/**
* trialbalance method
*
* @return void
*/
public function trialbalance()
{
$this->set('title_for_layout', __d('webzash', 'Trial Balance'));
/* Sub-title*/
$this->set('subtitle', __d('webzash', 'Trial Balance from %s to %s', dateFromSql(Configure::read('Account.startdate')), dateFromSql(Configure::read('Account.enddate'))));
$accountlist = new AccountList();
$accountlist->Group =& $this->Group;
$accountlist->Ledger =& $this->Ledger;
$accountlist->only_opening = false;
$accountlist->start_date = null;
$accountlist->end_date = null;
$accountlist->affects_gross = -1;
$accountlist->start(0);
$this->set('accountlist', $accountlist);
/* Download report */
if (isset($this->passedArgs['downloadcsv'])) {
Configure::write('Account.currency_format', 'none');
$this->layout = false;
$view = new View($this, false);
$response = $view->render('Reports/downloadcsv/trialbalance');
$this->response->body($response);
$this->response->type('text/csv');
$this->response->download('trialbalance.csv');
return $this->response;
}
/* Download report */
if (isset($this->passedArgs['downloadxls'])) {
Configure::write('Account.currency_format', 'none');
$this->layout = 'xls';
$view = new View($this, false);
$response = $view->render('Reports/downloadxls/trialbalance');
$this->response->body($response);
$this->response->type('application/vnd.ms-excel');
$this->response->download('trialbalance.xls');
return $this->response;
}
/* Print report */
if (isset($this->passedArgs['print'])) {
$this->layout = 'print';
$view = new View($this, false);
$response = $view->render('Reports/print/trialbalance');
$this->response->body($response);
return $this->response;
}
return;
}
/**
* index method
*
* @return void
*/
public function index()
{
$this->set('title_for_layout', __d('webzash', 'Account Dashboard'));
/**** Start initial check if all tables are present ****/
try {
$this->Group->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Groups table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
try {
$this->Ledger->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Ledgers table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
try {
$this->Entry->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Entries table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
try {
$this->Entryitem->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Entryitems table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
try {
$this->Tag->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Tags table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
try {
$this->Log->find('first');
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Logs table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
/* End intial check */
/* Cash and bank sumary */
$ledgers = '';
try {
$ledgers = $this->Ledger->find('all', array('order' => array('Ledger.name'), 'conditions' => array('Ledger.type' => 1)));
} catch (Exception $e) {
CakeSession::delete('ActiveAccount.id');
CakeSession::delete('ActiveAccount.account_role');
$this->Session->setFlash(__d('webzash', 'Ledgers table is missing. Please check whether this is a valid account database.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'wzusers', 'action' => 'account'));
}
$ledgersCB = array();
foreach ($ledgers as $ledger) {
$ledgersCB[] = array('name' => h(toCodeWithName($ledger['Ledger']['code'], $ledger['Ledger']['name'])), 'balance' => $this->Ledger->closingBalance($ledger['Ledger']['id']));
}
$this->set('ledgers', $ledgersCB);
/* Account summary */
$assets = new AccountList();
$assets->Group =& $this->Group;
$assets->Ledger =& $this->Ledger;
$assets->only_opening = false;
$assets->start_date = null;
$assets->end_date = null;
$assets->affects_gross = -1;
$assets->start(1);
$liabilities = new AccountList();
$liabilities->Group =& $this->Group;
$liabilities->Ledger =& $this->Ledger;
$liabilities->only_opening = false;
$liabilities->start_date = null;
$liabilities->end_date = null;
$liabilities->affects_gross = -1;
$liabilities->start(2);
$income = new AccountList();
$income->Group =& $this->Group;
$income->Ledger =& $this->Ledger;
$income->only_opening = false;
$income->start_date = null;
$income->end_date = null;
$income->affects_gross = -1;
$income->start(3);
$expense = new AccountList();
$expense->Group =& $this->Group;
$expense->Ledger =& $this->Ledger;
$expense->only_opening = false;
$expense->start_date = null;
$expense->end_date = null;
$expense->affects_gross = -1;
$expense->start(4);
$accsummary = array('assets_total_dc' => $assets->cl_total_dc, 'assets_total' => $assets->cl_total, 'liabilities_total_dc' => $liabilities->cl_total_dc, 'liabilities_total' => $liabilities->cl_total, 'income_total_dc' => $income->cl_total_dc, 'income_total' => $income->cl_total, 'expense_total_dc' => $expense->cl_total_dc, 'expense_total' => $expense->cl_total);
$this->set('accsummary', $accsummary);
$logs = $this->Log->find('all', array('limit' => 17, 'order' => array('Log.date DESC')));
$this->set('logs', $logs);
return;
}