public function listPagamentos()
{
if ($this->input->is_ajax_request()) {
$datainicial = $this->input->post("datainicial");
$datafinal = $this->input->post("datafinal");
$produto = $this->input->post("produto");
if ($datafinal !== "" && $datainicial !== null) {
if ($datafinal !== "" && $datafinal !== null) {
$data = array("itens_produto_id" => $produto, "datainicial" => dateToSql($datainicial), "datafinal" => dateToSql($datafinal));
$retorno = $this->contaspagar->pagamentosperiodo($data);
if ($retorno) {
echo json_encode($retorno);
} else {
returnErroJson(2, "Nenhum resultado encontrado");
}
} else {
returnErroJson(2, "Preencha o campo 'Data Final'");
}
} else {
returnErroJson(2, "Preencha o campo 'Data Inicial'");
}
} else {
redirect("dashboard");
}
}
public function save()
{
if ($this->input->is_ajax_request()) {
$contabancaria = $this->input->post("contabancaria");
$usuario = $this->session->userdata("usuario_logado");
$descricao = $this->input->post("descricao");
$this->contaspagar->startTransaction();
if ($contabancaria !== null && $contabancaria !== "") {
foreach ($_POST["valortotal"] as $key => $value) {
$valortotal = str_replace(".", "", $this->input->post("valortotal")[$key]);
$valortotal = str_replace(",", ".", $valortotal);
$qtdeparcelas = $this->input->post("totalparcela")[$key];
$mesinicial = dateToSql($this->input->post("mesinicial")[$key]);
$proximomes = $mesinicial;
intval($qtdeparcelas) > 0 ? $valorunitario = round(intval($valortotal) / intval($qtdeparcelas), 2) : ($valorunitario = 0);
$data = array("contaspagar_total" => $value, "contaspagar_totalparcelas" => $qtdeparcelas, "contaspagar_datacadastro" => date("Y-m-d"), "contaspagar_total" => $valortotal, "contaspagar_descricao" => $descricao, "contaspagar_usuario_id" => $usuario["usuario_id"], "contaspagar_contabancaria_id" => $contabancaria);
$retorno = $this->contaspagar->saveCP($data);
if ($retorno) {
$valorunitario = str_replace(",", ".", $valorunitario);
for ($i = 0; $i < $qtdeparcelas; $i++) {
$dataitens = array("itens_contaspagar_id" => $retorno, "itens_produto_id" => $key, "itens_valorunitario" => $valorunitario, "itens_valortotal" => $valortotal, "itens_numparcela" => $i + 1, "iten_totalparcela" => $qtdeparcelas, "itens_datavencimento" => $proximomes);
$retorno2 = $this->contaspagar->saveItensCP($dataitens);
if (!$retorno2) {
$erros[] = $retorno2;
}
$proximomes = addMonth($proximomes);
}
} else {
$erros[] = $retorno;
}
}
if ($this->contaspagar->statusTransaction() === FALSE) {
$this->contaspagar->rollbackTransaction();
returnErroJson(2, "Erro ao inserir");
} else {
$this->contaspagar->commitTransaction();
returnErroJson(1, "Inserido com sucesso!");
}
} else {
returnErroJson(2, "Selecione uma conta bancária");
}
} else {
echo "Erro";
}
}
/**
* edit method
*
* @param string $entrytypeLabel
* @param string $id
* @return void
*/
public function edit($entrytypeLabel = null, $id = null)
{
/* Check for valid entry type */
if (!$entrytypeLabel) {
$this->Session->setFlash(__d('webzash', 'Entry type not specified.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
}
$entrytype = $this->Entrytype->find('first', array('conditions' => array('Entrytype.label' => $entrytypeLabel)));
if (!$entrytype) {
$this->Session->setFlash(__d('webzash', 'Entry type not found.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
}
$this->set('entrytype', $entrytype);
$this->set('title_for_layout', __d('webzash', 'Edit %s Entry', $entrytype['Entrytype']['name']));
$this->set('tag_options', $this->Tag->listAll());
/* Ledger selection */
$ledgers = new LedgerTree();
$ledgers->Group =& $this->Group;
$ledgers->Ledger =& $this->Ledger;
$ledgers->current_id = -1;
$ledgers->restriction_bankcash = $entrytype['Entrytype']['restriction_bankcash'];
$ledgers->build(0);
$ledgers->toList($ledgers, -1);
$ledgers_disabled = array();
foreach ($ledgers->ledgerList as $row => $data) {
if ($row < 0) {
$ledgers_disabled[] = $row;
}
}
$this->set('ledger_options', $ledgers->ledgerList);
$this->set('ledgers_disabled', $ledgers_disabled);
/* Check for valid entry id */
if (empty($id)) {
$this->Session->setFlash(__d('webzash', 'Entry not specified.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
}
$entry = $this->Entry->findById($id);
if (!$entry) {
$this->Session->setFlash(__d('webzash', 'Entry not found.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
}
/* Initial data */
if ($this->request->is('post') || $this->request->is('put')) {
$curEntryitems = array();
foreach ($this->request->data['Entryitem'] as $row => $entryitem) {
$curEntryitems[$row] = array('dc' => $entryitem['dc'], 'ledger_id' => $entryitem['ledger_id'], 'dr_amount' => isset($entryitem['dr_amount']) ? $entryitem['dr_amount'] : '', 'cr_amount' => isset($entryitem['cr_amount']) ? $entryitem['cr_amount'] : '');
}
$this->set('curEntryitems', $curEntryitems);
} else {
$curEntryitems = array();
$curEntryitemsData = $this->Entryitem->find('all', array('conditions' => array('Entryitem.entry_id' => $id)));
foreach ($curEntryitemsData as $row => $data) {
if ($data['Entryitem']['dc'] == 'D') {
$curEntryitems[$row] = array('dc' => $data['Entryitem']['dc'], 'ledger_id' => $data['Entryitem']['ledger_id'], 'dr_amount' => $data['Entryitem']['amount'], 'cr_amount' => '');
} else {
$curEntryitems[$row] = array('dc' => $data['Entryitem']['dc'], 'ledger_id' => $data['Entryitem']['ledger_id'], 'dr_amount' => '', 'cr_amount' => $data['Entryitem']['amount']);
}
}
$curEntryitems[] = array('dc' => 'D');
$curEntryitems[] = array('dc' => 'D');
$curEntryitems[] = array('dc' => 'D');
$this->set('curEntryitems', $curEntryitems);
}
/* On POST */
if ($this->request->is('post') || $this->request->is('put')) {
if (!empty($this->request->data)) {
/* Check if acccount is locked */
if (Configure::read('Account.locked') == 1) {
$this->Session->setFlash(__d('webzash', 'Sorry, no changes are possible since the account is locked.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
}
/***************************************************************************/
/*********************************** ENTRY *********************************/
/***************************************************************************/
$entrydata = null;
/* Entry id */
unset($this->request->data['Entry']['id']);
$this->Entry->id = $id;
$entrydata['Entry']['id'] = $id;
/***** Entry number ******/
$entrydata['Entry']['number'] = $this->request->data['Entry']['number'];
/****** Entrytype remains the same *****/
$entrydata['Entry']['entrytype_id'] = $entrytype['Entrytype']['id'];
/****** Check tag ******/
if (empty($this->request->data['Entry']['tag_id'])) {
$entrydata['Entry']['tag_id'] = null;
} else {
$entrydata['Entry']['tag_id'] = $this->request->data['Entry']['tag_id'];
}
/***** Narration *****/
$entrydata['Entry']['narration'] = $this->request->data['Entry']['narration'];
/***** Date *****/
$entrydata['Entry']['date'] = dateToSql($this->request->data['Entry']['date']);
/***************************************************************************/
/***************************** ENTRY ITEMS *********************************/
/***************************************************************************/
/* Check ledger restriction */
$dc_valid = false;
foreach ($this->request->data['Entryitem'] as $row => $entryitem) {
if ($entryitem['ledger_id'] <= 0) {
continue;
}
$ledger = $this->Ledger->findById($entryitem['ledger_id']);
if (!$ledger) {
$this->Session->setFlash(__d('webzash', 'Invalid ledger selected.'), 'danger');
return;
}
if ($entrytype['Entrytype']['restriction_bankcash'] == 4) {
if ($ledger['Ledger']['type'] != 1) {
$this->Session->setFlash(__d('webzash', 'Only bank or cash ledgers are allowed for this entry type.'), 'danger');
return;
}
}
if ($entrytype['Entrytype']['restriction_bankcash'] == 5) {
if ($ledger['Ledger']['type'] == 1) {
$this->Session->setFlash(__d('webzash', 'Bank or cash ledgers are not allowed for this entry type.'), 'danger');
return;
}
}
if ($entryitem['dc'] == 'D') {
if ($entrytype['Entrytype']['restriction_bankcash'] == 2) {
if ($ledger['Ledger']['type'] == 1) {
$dc_valid = true;
}
}
} else {
if ($entryitem['dc'] == 'C') {
if ($entrytype['Entrytype']['restriction_bankcash'] == 3) {
if ($ledger['Ledger']['type'] == 1) {
$dc_valid = true;
}
}
}
}
}
if ($entrytype['Entrytype']['restriction_bankcash'] == 2) {
if (!$dc_valid) {
$this->Session->setFlash(__d('webzash', 'Atleast one bank or cash ledger has to be on debit side for this entry type.'), 'danger');
return;
}
}
if ($entrytype['Entrytype']['restriction_bankcash'] == 3) {
if (!$dc_valid) {
$this->Session->setFlash(__d('webzash', 'Atleast one bank or cash ledger has to be on credit side for this entry type.'), 'danger');
return;
}
}
$dr_total = 0;
$cr_total = 0;
/* Check equality of debit and credit total */
foreach ($this->request->data['Entryitem'] as $row => $entryitem) {
if ($entryitem['ledger_id'] <= 0) {
continue;
}
if ($entryitem['dc'] == 'D') {
if ($entryitem['dr_amount'] <= 0) {
$this->Session->setFlash(__d('webzash', 'Invalid amount specified. Amount cannot be negative or zero.'), 'danger');
return;
}
if (countDecimal($entryitem['dr_amount']) > Configure::read('Account.decimal_places')) {
$this->Session->setFlash(__d('webzash', 'Invalid amount specified. Maximum %s decimal places allowed.', Configure::read('Account.decimal_places')), 'danger');
return;
}
$dr_total = calculate($dr_total, $entryitem['dr_amount'], '+');
} else {
if ($entryitem['dc'] == 'C') {
if ($entryitem['cr_amount'] <= 0) {
$this->Session->setFlash(__d('webzash', 'Invalid amount specified. Amount cannot be negative or zero.'), 'danger');
return;
}
if (countDecimal($entryitem['cr_amount']) > Configure::read('Account.decimal_places')) {
$this->Session->setFlash(__d('webzash', 'Invalid amount specified. Maximum %s decimal places allowed.', Configure::read('Account.decimal_places')), 'danger');
return;
}
$cr_total = calculate($cr_total, $entryitem['cr_amount'], '+');
} else {
$this->Session->setFlash(__d('webzash', 'Invalid Dr/Cr option selected.'), 'danger');
return;
}
}
}
if (calculate($dr_total, $cr_total, '!=')) {
$this->Session->setFlash(__d('webzash', 'Debit and Credit total do not match.'), 'danger');
return;
}
$entrydata['Entry']['dr_total'] = $dr_total;
$entrydata['Entry']['cr_total'] = $cr_total;
/* Add item to entryitemdata array if everything is ok */
$entryitemdata = array();
foreach ($this->request->data['Entryitem'] as $row => $entryitem) {
if ($entryitem['ledger_id'] <= 0) {
continue;
}
if ($entryitem['dc'] == 'D') {
$entryitemdata[] = array('Entryitem' => array('dc' => $entryitem['dc'], 'ledger_id' => $entryitem['ledger_id'], 'amount' => $entryitem['dr_amount']));
} else {
$entryitemdata[] = array('Entryitem' => array('dc' => $entryitem['dc'], 'ledger_id' => $entryitem['ledger_id'], 'amount' => $entryitem['cr_amount']));
}
}
/* Save entry */
$ds = $this->Entry->getDataSource();
$ds->begin();
if ($this->Entry->save($entrydata)) {
/* Delete all original entryitems */
if (!$this->Entryitem->deleteAll(array('Entryitem.entry_id' => $id))) {
$ds->rollback();
$this->Session->setFlash(__d('webzash', 'Previous entry items could not be deleted.'), 'danger');
return;
}
/* Save new entry items */
foreach ($entryitemdata as $id => $itemdata) {
$itemdata['Entryitem']['entry_id'] = $this->Entry->id;
$this->Entryitem->create();
if (!$this->Entryitem->save($itemdata)) {
foreach ($this->Entryitem->validationErrors as $field => $msg) {
$errmsg = $msg[0];
break;
}
$ds->rollback();
$this->Session->setFlash(__d('webzash', 'Failed to save entry ledgers. Error is : %s', $errmsg), 'danger');
return;
}
}
$tempentry = $this->Entry->read(null, $this->Entry->id);
if (!$tempentry) {
$this->Session->setFlash(__d('webzash', 'Oh snap ! Failed to update entry. Please, try again.'), 'danger');
$ds->rollback();
return;
}
$entryNumber = h(toEntryNumber($tempentry['Entry']['number'], $entrytype['Entrytype']['id']));
$this->Log->add('Edited ' . $entrytype['Entrytype']['name'] . ' entry numbered ' . $entryNumber, 1);
$ds->commit();
$this->Session->setFlash(__d('webzash', '%s entry numbered "%s" updated.', $entrytype['Entrytype']['name'], $entryNumber), 'success');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'entries', 'action' => 'index'));
} else {
$ds->rollback();
$this->Session->setFlash(__d('webzash', 'Failed to update entry. Please, try again.'), 'danger');
return;
}
} else {
$this->Session->setFlash(__d('webzash', 'No data. Please, try again.'), 'danger');
return;
}
} else {
$entry['Entry']['date'] = dateFromSql($entry['Entry']['date']);
$this->request->data = $entry;
return;
}
}
/**
* create method
*
* @return void
*/
public function create()
{
$this->set('title_for_layout', __d('webzash', 'Create new 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']['currency_format'])) {
$this->Session->setFlash(__d('webzash', 'Currency format is required.'), 'danger');
return;
}
if (empty($this->request->data['Wzaccount']['decimal_places'])) {
$this->Session->setFlash(__d('webzash', 'Decimal places 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;
}
/* 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;
}
/* Check email */
if (!filter_var($this->request->data['Wzaccount']['email'], FILTER_VALIDATE_EMAIL)) {
$this->Session->setFlash(__d('webzash', 'Email address is invalid.'), 'danger');
return;
}
/* Check for valid decimal places */
if (!($this->request->data['Wzaccount']['decimal_places'] == 2 || $this->request->data['Wzaccount']['decimal_places'] == 3)) {
$this->Session->setFlash(__d('webzash', 'Decimal places can only be 2 or 3.'), '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;
}
/* 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 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_%', $this->request->data['Wzaccount']['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;
}
/* Read the intial data from the Config folder */
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Mysql') {
$initdata_filepath = App::pluginPath('Webzash') . 'Config/InitialData.Mysql.sql';
} else {
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Postgres') {
$initdata_filepath = App::pluginPath('Webzash') . 'Config/InitialData.Postgres.sql';
}
}
$initdata_file = new File($initdata_filepath, false);
$initdata = $initdata_file->read(true, 'r');
/* Add prefix to the table names in the intial data */
$final_initdata = str_replace('%_PREFIX_%', $wz_newconfig['prefix'], $initdata);
/* Add initial data */
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Mysql') {
try {
$db->rawQuery($final_initdata);
} catch (Exception $e) {
$this->Session->setFlash(__d('webzash', 'Oh Snap ! Something went wrong while adding initial data. Please try again.'), 'danger');
return;
}
} else {
if ($this->request->data['Wzaccount']['db_datasource'] == 'Database/Postgres') {
/* Execute each individual querry one at a time as per Postgres limitations */
$final_querries = explode(PHP_EOL, $final_initdata);
foreach ($final_querries as $init_query) {
if (strlen($init_query) < 5) {
continue;
}
try {
$db->rawQuery($init_query);
} catch (Exception $e) {
$this->Session->setFlash(__d('webzash', 'Oh Snap ! Something went wrong while adding initial data. Please try again.'), 'danger');
return;
}
}
}
}
/******* Create settings *******/
$this->Setting->useDbConfig = 'wz_newconfig';
$account_setting = array('Setting' => array('id' => '1', 'name' => $this->request->data['Wzaccount']['name'], 'address' => $this->request->data['Wzaccount']['address'], 'email' => $this->request->data['Wzaccount']['email'], 'fy_start' => dateToSql($this->request->data['Wzaccount']['fy_start']), 'fy_end' => dateToSql($this->request->data['Wzaccount']['fy_end']), 'currency_symbol' => $this->request->data['Wzaccount']['currency_symbol'], 'currency_format' => $this->request->data['Wzaccount']['currency_format'], 'decimal_places' => $this->request->data['Wzaccount']['decimal_places'], 'date_format' => $this->request->data['Wzaccount']['date_format'], 'timezone' => 'UTC', 'manage_inventory' => 0, 'account_locked' => 0, 'email_use_default' => 1, 'email_protocol' => 'Smtp', 'email_host' => '', 'email_port' => 0, 'email_tls' => 0, 'email_username' => '', 'email_password' => '', 'email_from' => '', 'print_paper_height' => 0.0, 'print_paper_width' => 0.0, 'print_margin_top' => 0.0, 'print_margin_bottom' => 0.0, 'print_margin_left' => 0.0, 'print_margin_right' => 0.0, 'print_orientation' => 'P', 'print_page_format' => 'H', 'database_version' => Configure::read('Webzash.AppDatabaseVersion'), 'settings' => NULL));
$this->Setting->create();
if (!$this->Setting->save($account_setting)) {
foreach ($this->Setting->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;
}
}
}
/**
* 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;
}
}
}
/**
* reconciliation method
*
* @return void
*/
public function reconciliation()
{
$this->set('title_for_layout', __d('webzash', 'Ledger Reconciliation'));
/* Create list of ledgers to pass to view */
$ledgers_q = $this->Ledger->find('all', array('fields' => array('Ledger.id', 'Ledger.name', 'Ledger.code'), 'order' => array('Ledger.name'), 'conditions' => array('Ledger.reconciliation' => '1')));
$ledgers = array(0 => __d('webzash', 'Please select...'));
foreach ($ledgers_q as $row) {
$ledgers[$row['Ledger']['id']] = toCodeWithName($row['Ledger']['code'], $row['Ledger']['name']);
}
$this->set('ledgers', $ledgers);
if ($this->request->is('post')) {
/* Ledger selection form submitted */
if (!empty($this->request->data['Report']['submitledger'])) {
/* If valid data then redirect with POST values are URL parameters so that pagination works */
if (empty($this->request->data['Report']['ledger_id'])) {
$this->Session->setFlash(__d('webzash', 'Invalid ledger.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation'));
}
if (!empty($this->request->data['Report']['startdate']) || !empty($this->request->data['Report']['enddate']) || !empty($this->request->data['Report']['showall'])) {
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation', 'ledgerid' => $this->request->data['Report']['ledger_id'], 'options' => 1, 'showall' => $this->request->data['Report']['showall'], 'startdate' => $this->request->data['Report']['startdate'], 'enddate' => $this->request->data['Report']['enddate']));
} else {
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation', 'ledgerid' => $this->request->data['Report']['ledger_id']));
}
} else {
if (!empty($this->request->data['ReportRec']['submitrec'])) {
/* Check if acccount is locked */
if (Configure::read('Account.locked') == 1) {
$this->Session->setFlash(__d('webzash', 'Sorry, no changes are possible since the account is locked.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation'));
}
/* Reconciliation form submitted */
foreach ($this->request->data['ReportRec'] as $row => $recitem) {
if (empty($recitem['id'])) {
continue;
}
if (!empty($recitem['recdate'])) {
$recdate = dateToSql($recitem['recdate']);
if (!$recdate) {
$this->Session->setFlash(__d('webzash', 'Invalid reconciliation date.'), 'danger');
continue;
}
} else {
$recdate = '';
}
$this->Entryitem->id = $recitem['id'];
if (!$this->Entryitem->read()) {
continue;
}
$this->Entryitem->saveField('reconciliation_date', $recdate);
}
/* Unset all POST data so that data for reconciliation date is loaded from database */
unset($this->request->data['ReportRec']);
} else {
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation'));
}
}
}
$this->set('showEntries', false);
$this->set('options', false);
/* Check if ledger id is set in parameters, if not return and end view here */
if (empty($this->passedArgs['ledgerid'])) {
return;
}
$ledgerId = $this->passedArgs['ledgerid'];
/* Check if ledger exists */
$ledger = $this->Ledger->findById($ledgerId);
if (!$ledger) {
$this->Session->setFlash(__d('webzash', 'Ledger not found.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'reconciliation'));
}
$this->set('ledger', $ledger);
$this->request->data['Report']['ledger_id'] = $ledgerId;
/* Set the approprite search conditions */
$conditions = array();
$conditions['Entryitem.ledger_id'] = $ledgerId;
/* Set the approprite search conditions if custom date is selected */
$startdate = null;
$enddate = null;
if (empty($this->passedArgs['options'])) {
$this->set('options', false);
/* Sub-title*/
$this->set('subtitle', __d('webzash', 'Reconciliation report for %s from %s to %s', h(toCodeWithName($ledger['Ledger']['code'], $ledger['Ledger']['name'])), dateFromSql(Configure::read('Account.startdate')), dateFromSql(Configure::read('Account.enddate'))));
} else {
$this->set('options', true);
if (!empty($this->passedArgs['showall'])) {
$this->request->data['Report']['showall'] = 1;
}
if (!empty($this->passedArgs['startdate'])) {
/* TODO : Validate date */
$startdate = dateToSql($this->passedArgs['startdate']);
$this->request->data['Report']['startdate'] = $this->passedArgs['startdate'];
$conditions['Entry.date >='] = $startdate;
}
if (!empty($this->passedArgs['enddate'])) {
/* TODO : Validate date */
$enddate = dateToSql($this->passedArgs['enddate']);
$this->request->data['Report']['enddate'] = $this->passedArgs['enddate'];
$conditions['Entry.date <='] = $enddate;
}
/* Sub-title*/
if (!empty($this->passedArgs['startdate']) && !empty($this->passedArgs['enddate'])) {
$this->set('subtitle', __d('webzash', 'Reconciliation report for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(dateToSQL($this->passedArgs['startdate'])), dateFromSql(dateToSQL($this->passedArgs['enddate']))));
} else {
if (!empty($this->passedArgs['startdate'])) {
$this->set('subtitle', __d('webzash', 'Reconciliation report for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(dateToSQL($this->passedArgs['startdate'])), dateFromSql(Configure::read('Account.enddate'))));
} else {
if (!empty($this->passedArgs['enddate'])) {
$this->set('subtitle', __d('webzash', 'Reconciliation report for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(Configure::read('Account.startdate')), dateFromSql(dateToSQL($this->passedArgs['enddate']))));
} else {
if (empty($this->passedArgs['startdate']) && empty($this->passedArgs['enddate'])) {
$this->set('subtitle', __d('webzash', 'Reconciliation report for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(Configure::read('Account.startdate')), dateFromSql(Configure::read('Account.enddate'))));
}
}
}
}
}
if (!empty($this->passedArgs['showall'])) {
/* Nothing to do */
} else {
$conditions['Entryitem.reconciliation_date'] = NULL;
}
/* Opening and closing titles */
if (is_null($startdate)) {
$this->set('opening_title', __d('webzash', 'Opening balance as on %s', dateFromSql(Configure::read('Account.startdate'))));
} else {
$this->set('opening_title', __d('webzash', 'Opening balance as on %s', dateFromSql($startdate)));
}
if (is_null($enddate)) {
$this->set('closing_title', __d('webzash', 'Closing balance as on %s', dateFromSql(Configure::read('Account.enddate'))));
} else {
$this->set('closing_title', __d('webzash', 'Closing balance as on %s', dateFromSql($enddate)));
}
/* Reconciliation pending title */
$this->set('recpending_title', '');
if (is_null($startdate) && is_null($enddate)) {
$this->set('recpending_title', __d('webzash', 'Reconciliation pending from %s to %s', dateFromSql(Configure::read('Account.startdate')), dateFromSql(Configure::read('Account.enddate'))));
} else {
if (!is_null($startdate) && !is_null($enddate)) {
$this->set('recpending_title', __d('webzash', 'Reconciliation pending from %s to %s', dateFromSql($startdate), dateFromSql($enddate)));
} else {
if (is_null($startdate)) {
$this->set('recpending_title', __d('webzash', 'Reconciliation pending from %s to %s', dateFromSql(Configure::read('Account.startdate')), dateFromSql($enddate)));
} else {
if (is_null($enddate)) {
$this->set('recpending_title', __d('webzash', 'Reconciliation pending from %s to %s', dateFromSql($startdate), dateFromSql(Configure::read('Account.enddate'))));
}
}
}
}
/* Calculating opening balance */
$op = $this->Ledger->openingBalance($ledgerId, $startdate);
$this->set('op', $op);
/* Calculating closing balance */
$cl = $this->Ledger->closingBalance($ledgerId, null, $enddate);
$this->set('cl', $cl);
/* Calculating reconciliation pending balance */
$rp = $this->Ledger->reconciliationPending($ledgerId, $startdate, $enddate);
$this->set('rp', $rp);
/* Setup pagination */
if (isset($this->passedArgs['download']) || isset($this->passedArgs['downloadxls']) || isset($this->passedArgs['print'])) {
$this->CustomPaginator->settings = array('Entry' => array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => 100000000000, 'order' => array('Entry.date' => 'desc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
} else {
$this->CustomPaginator->settings = array('Entry' => array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => $this->Session->read('Wzsetting.row_count'), 'order' => array('Entry.date' => 'desc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
}
/* Pass varaibles to view which are used in Helpers */
$this->set('allTags', $this->Tag->fetchAll());
$this->set('entries', $this->CustomPaginator->paginate('Entry'));
$this->set('showEntries', true);
/* 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/reconciliation');
$this->response->body($response);
$this->response->type('text/csv');
$this->response->download('reconciliation.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/reconciliation');
$this->response->body($response);
$this->response->type('application/vnd.ms-excel');
$this->response->download('reconciliation.xls');
return $this->response;
}
/* Print report */
if (isset($this->passedArgs['print'])) {
$this->layout = 'print';
$view = new View($this, false);
$response = $view->render('Reports/print/reconciliation');
$this->response->body($response);
return $this->response;
}
return;
}
public function pegarDoPost($movimento = null)
{
if (is_null($movimento)) {
$movimento = new stdClass();
}
if (array_key_exists('id_pessoa', $_POST)) {
$movimento->id_pessoa = $_POST['id_pessoa'];
}
if (array_key_exists('cod_tipo', $_POST)) {
$movimento->cod_tipo = $_POST['cod_tipo'];
}
if (array_key_exists('id_aluno', $_POST)) {
$movimento->id_aluno = $_POST['id_aluno'];
}
if (array_key_exists('tipo', $_POST)) {
$movimento->tipo = $_POST['tipo'];
}
if (array_key_exists('data_vencimento', $_POST)) {
$movimento->data_vencimento = dateToSql($_POST['data_vencimento']);
}
if (array_key_exists('valor', $_POST)) {
if ($movimento->tipo == CREDITO) {
$movimento->credito = $_POST['valor'];
$movimento->debito = 0;
} elseif ($movimento->tipo == DEBITO) {
$movimento->credito = 0;
$movimento->debito = $_POST['valor'];
}
}
if (array_key_exists('credito', $_POST)) {
$movimento->credito = $_POST['credito'];
}
if (array_key_exists('debito', $_POST)) {
$movimento->debito = $_POST['debito'];
}
if (array_key_exists('cod_situacao', $_POST)) {
$movimento->cod_situacao = $_POST['cod_situacao'];
}
if (array_key_exists('observacao', $_POST)) {
$movimento->observacao = $_POST['observacao'];
}
return $movimento;
}
if (isset($_POST['set_user'])) {
$valid = array("user_id", "user_pass", "user_mail", "user_phone", "user_enable", "user_start_date", "user_end_date");
$set_field = $set_value = array();
// Only update what was modified
foreach ($_POST as $key => $value) {
if (in_array($key, $valid)) {
array_push($set_field, $key . "=?");
if ($key == "user_pass") {
if ($value == "") {
array_push($set_value, $value);
} else {
array_push($set_value, hashPass($value));
}
} else {
if ($key == "user_start_date" || $key == "user_end_date") {
array_push($set_value, dateToSql($value));
} else {
array_push($set_value, $value);
}
}
}
}
// Build the request
array_push($set_value, $_POST['set_user']);
$req_string = 'UPDATE user SET ' . implode(',', $set_field) . ' WHERE user_id = ?';
$req = $bdd->prepare($req_string);
$req->execute($set_value);
} else {
if (isset($_POST['del_user_id'])) {
$req = $bdd->prepare('DELETE FROM user WHERE user_id = ?');
$req->execute(array($_POST['del_user_id']));
/**
* index method
*
* @return void
*/
public function index()
{
$this->set('title_for_layout', __d('webzash', 'Search'));
$this->set('showEntries', false);
/* Ledger selection */
$ledgers = new LedgerTree();
$ledgers->Group =& $this->Group;
$ledgers->Ledger =& $this->Ledger;
$ledgers->current_id = -1;
$ledgers->restriction_bankcash = 1;
$ledgers->default_text = '(ALL)';
$ledgers->build(0);
$ledgers->toList($ledgers, -1);
$ledgers_disabled = array();
foreach ($ledgers->ledgerList as $row => $data) {
if ($row < 0) {
$ledgers_disabled[] = $row;
}
}
$this->set('ledger_options', $ledgers->ledgerList);
$this->set('ledgers_disabled', $ledgers_disabled);
/* Entrytypes */
$entrytype_options = array();
$entrytype_options[0] = '(ALL)';
$rawentrytypes = $this->Entrytype->find('all', array('order' => 'Entrytype.id'));
foreach ($rawentrytypes as $row => $rawentrytype) {
$entrytype_options[$rawentrytype['Entrytype']['id']] = h($rawentrytype['Entrytype']['name']);
}
$this->set('entrytype_options', $entrytype_options);
/* Tags */
$tag_options = array();
$tag_options[0] = '(ALL)';
$rawtags = $this->Tag->find('all', array('order' => 'Tag.title'));
foreach ($rawtags as $row => $rawtag) {
$tag_options[$rawtag['Tag']['id']] = h($rawtag['Tag']['title']);
}
$this->set('tag_options', $tag_options);
if ($this->request->is('post') || $this->request->is('put')) {
$ledger_ids = '';
if (empty($this->request->data['Search']['ledger_ids'])) {
$ledger_ids = '0';
} else {
if (in_array('0', $this->request->data['Search']['ledger_ids'])) {
$ledger_ids = '0';
} else {
$ledger_ids = implode(',', $this->request->data['Search']['ledger_ids']);
}
}
$entrytype_ids = '';
if (empty($this->request->data['Search']['entrytype_ids'])) {
$entrytype_ids = '0';
} else {
if (in_array('0', $this->request->data['Search']['entrytype_ids'])) {
$entrytype_ids = '0';
} else {
$entrytype_ids = implode(',', $this->request->data['Search']['entrytype_ids']);
}
}
$tag_ids = '';
if (empty($this->request->data['Search']['tag_ids'])) {
$tag_ids = '0';
} else {
if (in_array('0', $this->request->data['Search']['tag_ids'])) {
$tag_ids = '0';
} else {
$tag_ids = implode(',', $this->request->data['Search']['tag_ids']);
}
}
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'search', 'action' => 'index', 'search' => 1, 'ledger_ids' => $ledger_ids, 'entrytype_ids' => $entrytype_ids, 'entrynumber_restriction' => $this->request->data['Search']['entrynumber_restriction'], 'entrynumber1' => $this->request->data['Search']['entrynumber1'], 'entrynumber2' => $this->request->data['Search']['entrynumber2'], 'amount_dc' => $this->request->data['Search']['amount_dc'], 'amount_restriction' => $this->request->data['Search']['amount_restriction'], 'amount1' => $this->request->data['Search']['amount1'], 'amount2' => $this->request->data['Search']['amount2'], 'fromdate' => $this->request->data['Search']['fromdate'], 'todate' => $this->request->data['Search']['todate'], 'tag_ids' => $tag_ids, 'narration' => $this->request->data['Search']['narration']));
}
/* Check if search is active */
if (empty($this->passedArgs['search']) || $this->passedArgs['search'] != 1) {
return;
}
/* Initialize data from passedArgs */
$this->request->data['Search']['ledger_ids'] = explode(',', $this->passedArgs['ledger_ids']);
$this->request->data['Search']['entrytype_ids'] = explode(',', $this->passedArgs['entrytype_ids']);
$this->request->data['Search']['entrynumber_restriction'] = $this->passedArgs['entrynumber_restriction'];
$this->request->data['Search']['entrynumber1'] = $this->passedArgs['entrynumber1'];
$this->request->data['Search']['entrynumber2'] = $this->passedArgs['entrynumber2'];
$this->request->data['Search']['amount_dc'] = $this->passedArgs['amount_dc'];
$this->request->data['Search']['amount_restriction'] = $this->passedArgs['amount_restriction'];
$this->request->data['Search']['amount1'] = $this->passedArgs['amount1'];
$this->request->data['Search']['amount2'] = $this->passedArgs['amount2'];
$this->request->data['Search']['fromdate'] = $this->passedArgs['fromdate'];
$this->request->data['Search']['todate'] = $this->passedArgs['todate'];
$this->request->data['Search']['tag_ids'] = explode(',', $this->passedArgs['tag_ids']);
$this->request->data['Search']['narration'] = $this->passedArgs['narration'];
/* Setup search conditions */
$conditions = array();
if (!empty($this->passedArgs['ledger_ids'])) {
if (!in_array('0', $this->request->data['Search']['ledger_ids'])) {
$conditions['Entryitem.ledger_id'] = $this->request->data['Search']['ledger_ids'];
}
}
if (!empty($this->passedArgs['entrytype_ids'])) {
if (!in_array('0', $this->request->data['Search']['entrytype_ids'])) {
$conditions['Entry.entrytype_id'] = $this->request->data['Search']['entrytype_ids'];
}
}
if (!empty($this->passedArgs['entrynumber1'])) {
if ($this->passedArgs['entrynumber_restriction'] == 1) {
/* Equal to */
$conditions['Entry.number'] = $this->passedArgs['entrynumber1'];
} else {
if ($this->passedArgs['entrynumber_restriction'] == 2) {
/* Less than or equal to */
$conditions['Entry.number <='] = $this->passedArgs['entrynumber1'];
} else {
if ($this->passedArgs['entrynumber_restriction'] == 3) {
/* Greater than or equal to */
$conditions['Entry.number >='] = $this->passedArgs['entrynumber1'];
} else {
if ($this->passedArgs['entrynumber_restriction'] == 4) {
/* In between */
if (!empty($this->passedArgs['entrynumber2'])) {
$conditions['Entry.number >='] = $this->passedArgs['entrynumber1'];
$conditions['Entry.number <='] = $this->passedArgs['entrynumber2'];
} else {
$conditions['Entry.number >='] = $this->passedArgs['entrynumber1'];
}
}
}
}
}
}
if ($this->passedArgs['amount_dc'] == 'D') {
/* Dr */
$conditions['Entryitem.dc'] = 'D';
} else {
if ($this->passedArgs['amount_dc'] == 'C') {
/* Cr */
$conditions['Entryitem.dc'] = 'C';
}
}
if (!empty($this->passedArgs['amount1'])) {
if ($this->passedArgs['amount_restriction'] == 1) {
/* Equal to */
$conditions['Entryitem.amount'] = $this->passedArgs['amount1'];
} else {
if ($this->passedArgs['amount_restriction'] == 2) {
/* Less than or equal to */
$conditions['Entryitem.amount <='] = $this->passedArgs['amount1'];
} else {
if ($this->passedArgs['amount_restriction'] == 3) {
/* Greater than or equal to */
$conditions['Entryitem.amount >='] = $this->passedArgs['amount1'];
} else {
if ($this->passedArgs['amount_restriction'] == 4) {
/* In between */
if (!empty($this->passedArgs['amount2'])) {
$conditions['Entryitem.amount >='] = $this->passedArgs['amount1'];
$conditions['Entryitem.amount <='] = $this->passedArgs['amount2'];
} else {
$conditions['Entryitem.amount >='] = $this->passedArgs['amount1'];
}
}
}
}
}
}
if (!empty($this->passedArgs['fromdate'])) {
/* TODO : Validate date */
$fromdate = dateToSql($this->passedArgs['fromdate']);
$conditions['Entry.date >='] = $fromdate;
}
if (!empty($this->passedArgs['todate'])) {
/* TODO : Validate date */
$todate = dateToSql($this->passedArgs['todate']);
$conditions['Entry.date <='] = $todate;
}
if (!empty($this->passedArgs['tag_ids'])) {
if (!in_array('0', $this->request->data['Search']['tag_ids'])) {
$conditions['Entry.tag_id'] = $this->request->data['Search']['tag_ids'];
}
}
if (!empty($this->passedArgs['narration'])) {
$conditions['Entry.narration LIKE'] = '%' . $this->passedArgs['narration'] . '%';
}
/* Pass varaibles to view which are used in Helpers */
$this->set('allTags', $this->Tag->fetchAll());
/* Setup pagination */
$this->CustomPaginator->settings = array('Entry' => array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => $this->Session->read('Wzsetting.row_count'), 'order' => array('Entry.date' => 'asc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
$this->set('entries', $this->CustomPaginator->paginate('Entry'));
$this->set('showEntries', true);
return;
}
public function edit()
{
if ($this->input->is_ajax_request()) {
$cript = $this->input->post("cript");
$recebimento = $this->input->post("recebimento");
$valor = $this->input->post("valor");
$descricao = $this->input->post("descricao");
$frequencia = $this->input->post("frequencia");
$date = $this->input->post("data");
$contabancaria = $this->input->post("contabancaria");
$qtdemeses = $this->input->post("qtdemeses");
if ($cript !== "" && $cript !== null) {
if ($recebimento !== null && $recebimento !== "") {
if ($valor !== null && $valor !== "") {
if ($frequencia !== null && $frequencia !== "") {
if ($date !== null && $date !== "") {
if ($contabancaria !== null && $contabancaria !== "") {
if ($frequencia !== 'U' && $qtdemeses !== "" && $qtdemeses !== null) {
$usuario = $this->session->userdata("usuario_logado");
$data = array("contasreceber_id" => $this->encrypt->decode($cript), "contasreceber_total" => $valor, "contasreceber_descricao" => $descricao, "contasreceber_frequencia" => $frequencia, "contasreceber_qtdemeses" => $qtdemeses, "contasreceber_data" => dateToSql($date), "contasreceber_tiporecebimento_id" => $recebimento, "contasreceber_usuario_id" => $usuario["usuario_id"], "contasreceber_contabancaria_id" => $contabancaria);
$retorno = $this->contasreceber->save($data);
if ($retorno) {
returnErroJson(1, "Conta à Receber cadastrada com sucesso", $retorno);
} else {
returnErroJson(2, "Eerro ao cadastrar");
}
}
} else {
returnErroJson(2, "Preencha o campo 'Qtde Meses'");
}
} else {
returnErroJson(2, "Preencha o campo 'Data'");
}
} else {
returnErroJson(2, "Preencha o campo 'Frequência'");
}
} else {
returnErroJson(2, "Preencha o campo 'valor'");
}
} else {
returnErroJson(2, "Preencha o campo 'Recebimento'");
}
} else {
returnErroJson(2, "Selecione uma conta à receber");
}
} else {
echo "Erro";
}
}
public function checkEntries($data)
{
$values = array_values($data);
if (!isset($values)) {
return false;
}
$value = $values[0];
$startdate = dateToSql($this->data['Setting']['fy_start']);
$enddate = dateToSql($value);
/* Load the Ledger model */
App::import("Webzash.Model", "Entry");
$Entry = new Entry();
$count = $Entry->find('count', array('conditions' => array('OR' => array('Entry.date <' => $startdate, 'Entry.date >' => $enddate))));
if ($count != 0) {
return false;
} else {
return true;
}
}
public function pegarDoPost($pessoa = null)
{
if (is_null($pessoa)) {
$pessoa = new stdClass();
}
if (array_key_exists('id_pessoa', $_POST)) {
$pessoa->id_pessoa = intval($_POST['id_pessoa']);
}
if (array_key_exists('id_turma', $_POST)) {
$pessoa->id_turma = intval($_POST['id_turma']);
}
if (array_key_exists('id_aluno', $_POST)) {
$pessoa->id_aluno = intval($_POST['id_aluno']);
}
if (array_key_exists('id_responsavel', $_POST)) {
$pessoa->id_responsavel = intval($_POST['id_responsavel']);
}
if (array_key_exists('tipo', $_POST)) {
$pessoa->tipo = $_POST['tipo'];
}
if (array_key_exists('nome', $_POST)) {
$pessoa->nome = $_POST['nome'];
}
if (array_key_exists('data_nascimento', $_POST)) {
$pessoa->data_nascimento = dateToSql($_POST['data_nascimento']);
}
if (array_key_exists('genero', $_POST)) {
$pessoa->genero = $_POST['genero'];
}
if (array_key_exists('cpf_cnpj', $_POST)) {
$pessoa->cpf_cnpj = $_POST['cpf_cnpj'];
}
if (array_key_exists('valor_mensalidade', $_POST)) {
$pessoa->valor_mensalidade = $_POST['valor_mensalidade'];
}
if (array_key_exists('telefone1', $_POST)) {
$pessoa->telefone1 = $_POST['telefone1'];
}
if (array_key_exists('telefone2', $_POST)) {
$pessoa->telefone2 = $_POST['telefone2'];
}
if (array_key_exists('telefone3', $_POST)) {
$pessoa->telefone3 = $_POST['telefone3'];
}
if (array_key_exists('telefone4', $_POST)) {
$pessoa->telefone4 = $_POST['telefone4'];
}
if (array_key_exists('email1', $_POST)) {
$pessoa->email1 = $_POST['email1'];
}
if (array_key_exists('email2', $_POST)) {
$pessoa->email2 = $_POST['email2'];
}
if (array_key_exists('email3', $_POST)) {
$pessoa->email3 = $_POST['email3'];
}
if (array_key_exists('email4', $_POST)) {
$pessoa->email4 = $_POST['email4'];
}
if (array_key_exists('endereco', $_POST)) {
$pessoa->endereco = $_POST['endereco'];
}
if (array_key_exists('complemento', $_POST)) {
$pessoa->complemento = $_POST['complemento'];
}
if (array_key_exists('bairro', $_POST)) {
$pessoa->bairro = $_POST['bairro'];
}
if (array_key_exists('cidade', $_POST)) {
$pessoa->cidade = $_POST['cidade'];
}
if (array_key_exists('uf', $_POST)) {
$pessoa->uf = $_POST['uf'];
}
if (array_key_exists('cod_situacao', $_POST)) {
$pessoa->cod_situacao = intval($_POST['cod_situacao']);
}
return $pessoa;
}
