Update balance with revenue and expense

Asked

Viewed 526 times

0

I have an application, where there is an option to register an ACCOUNT with initial balance equal to zero and two options to register REVENUE and EXPENSES.

And when I register an IRS, the balance of the account is updated according to the amount registered in the revenue.

If an EXPENSE is registered, the same amount is subtracted from the balance of the account

Setting:
REVENUE = 80,00
BALANCE OF THE ACCOUNT = 80,00

EXPENDITURE = 30,00
BALANCE OF THE ACCOUNT = 50,00

So far, so good.(working properly) The problem is that I don’t know how to update the BALANCE when editing or deleting a REVENUE or EXPENSE.

Example:
When editing the value of a REVENUE I want the balance to be updated.
When editing the value of an EXPENSE I want the balance to be updated.
When excluding an EXPENDITURE I want the value of EXPENDITURE to add to the balance.
When excluding a REVENUE I want the value of REVENUE, subtracted from the balance.

You can help me with this function and query?

The table where REVENUE and EXPENDITURE are registered has the following columns:

CREATE TABLE IF NOT EXISTS `lancamentos` (
  `idLancamentos` INT(11) NOT NULL AUTO_INCREMENT,
  `descricao` VARCHAR(255) NULL DEFAULT NULL,
  `valor` VARCHAR(15) NOT NULL,
  `data_vencimento` DATE NOT NULL,
  `categoria_id` INT(11) NULL DEFAULT NULL,
  `conta_id` INT(11) NULL DEFAULT NULL,
  `data_pagamento` DATE NULL DEFAULT NULL,
  `baixado` TINYINT(1) NULL DEFAULT NULL,
  `cliente_fornecedor` VARCHAR(255) NULL DEFAULT NULL,
  `forma_pgto` VARCHAR(100) NULL DEFAULT NULL,
  `tipo` VARCHAR(45) NULL DEFAULT NULL,
  `anexo` VARCHAR(250) NULL,
  `clientes_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`idLancamentos`),
  INDEX `fk_lancamentos_clientes1` (`clientes_id` ASC),
  CONSTRAINT `fk_lancamentos_clientes1`
    FOREIGN KEY (`clientes_id`)
    REFERENCES `clientes` (`idClientes`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

The table where the categories are registered has the following columns:

//1 para RECEITA
//0 para DESPESA

CREATE TABLE IF NOT EXISTS `categorias` (
  `idCategoria` INT(11) NOT NULL AUTO_INCREMENT,
  `nome_categoria` VARCHAR(45) NOT NULL,
  `tipo_categoria` TINYINT(1) NOT NULL,
  `status_categoria` TINYINT(1) NOT NULL,
  `data_cadastro_categoria` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`idCategoria`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = latin1;

The table where the accounts and balances are recorded has the following columns:

 CREATE TABLE IF NOT EXISTS 'contas' (
  'idConta' INT(11) NOT NULL AUTO_INCREMENT,
  'conta' VARCHAR(45) NOT NULL,
  'banco' VARCHAR(45) NOT NULL,
  'numero' VARCHAR(45) NOT NULL,
  'saldo' DECIMAL(10,2) NOT NULL,
  'status' TINYINT(1) NOT NULL,
  'data_cadastro' DATE NULL DEFAULT NULL,
  PRIMARY KEY (idConta))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = latin1;

To add EXPENSE, I am using the function:

function adicionarDespesa() {

    if(!$this->permission->checkPermission($this->session->userdata('permissao'),'aLancamento')){
       $this->session->set_flashdata('error','Você não tem permissão para adicionar lançamentos.');
       redirect(base_url());
    }

    $this->load->library('form_validation');
    $this->data['custom_error'] = '';
    $urlAtual = $this->input->post('urlAtual');
    if ($this->form_validation->run('despesa') == false) {
        $this->data['custom_error'] = (validation_errors() ? '<div class="form_error">' . validation_errors() . '</div>' : false);
    } else {

        $vencimento = $this->input->post('vencimento');
        $pagamento = $this->input->post('pagamento');
        $valor = $this->input->post('valor');
        $conta = $this->input->post('conta_id');

        if($pagamento != null){
            $pagamento = explode('/', $pagamento);
            $pagamento = $pagamento[2].'-'.$pagamento[1].'-'.$pagamento[0];
        }

        if($vencimento == null){
            $vencimento = date('d/m/Y');
        }

        try {

            $vencimento = explode('/', $vencimento);
            $vencimento = $vencimento[2].'-'.$vencimento[1].'-'.$vencimento[0];

        } catch (Exception $e) {
           $vencimento = date('Y/m/d'); 
        }

        $data = array(
            'descricao' => set_value('descricao'),
            //'valor' => set_value('valor'),
            'valor' => $valor,
            'data_vencimento' => $vencimento,
            'categoria_id' => $this->input->post('categoria_id'),
            'conta_id'=> $conta,
            //'conta_id' => $this->input->post('conta_id'),
            'baixado' => $this->input->post('pago'),
            'data_pagamento' => $pagamento != null ? $pagamento : date('Y-m-d'),
            'baixado' => $this->input->post('pago'),
            'cliente_fornecedor' => set_value('fornecedor'),
            'forma_pgto' => $this->input->post('formaPgto'),
            'tipo' => set_value('tipo')
        );

        if ($this->financeiro_model->add('lancamentos',$data) == TRUE) {

            $sql = "UPDATE contas set saldo = saldo - ? WHERE idConta = ?";
            $this->db->query($sql, array($valor, $conta));
            echo json_encode(array('result'=> true));

            $this->session->set_flashdata('success','Despesa adicionada com sucesso!');
            redirect($urlAtual);
        } else {
            $this->session->set_flashdata('error','Ocorreu um erro ao tentar adicionar despesa!');
            echo json_encode(array('result'=> false));
            redirect($urlAtual);
        }
    }

    $this->session->set_flashdata('error','Ocorreu um erro ao tentar adicionar despesa.');
    redirect($urlAtual);


}

To add RECIPE, I am using the function:

    function adicionarReceita() {

    if(!$this->permission->checkPermission($this->session->userdata('permissao'),'aLancamento')){
       $this->session->set_flashdata('error','Você não tem permissão para adicionar lançamentos.');
       redirect(base_url());
    }

    $this->load->library('form_validation');
    $this->data['custom_error'] = '';
    $urlAtual = $this->input->post('urlAtual');

    if ($this->form_validation->run('receita') == false) {
        $this->data['custom_error'] = (validation_errors() ? '<div class="form_error">' . validation_errors() . '</div>' : false);
    } else {


        $valor = $this->input->post('valor');
        $vencimento = $this->input->post('vencimento');
        $recebimento = $this->input->post('recebimento');
        $conta = $this->input->post('conta_id');

        if($recebimento != null){
            $recebimento = explode('/', $recebimento);
            $recebimento = $recebimento[2].'-'.$recebimento[1].'-'.$recebimento[0];
        }

        if($vencimento == null){
            $vencimento = date('d/m/Y');
        }

        try {

            $vencimento = explode('/', $vencimento);
            $vencimento = $vencimento[2].'-'.$vencimento[1].'-'.$vencimento[0];   

        } catch (Exception $e) {
           $vencimento = date('Y/m/d'); 
        }

        $data = array(
            'descricao' => set_value('descricao'),
            //'valor' => set_value('valor'),
            'valor' => $valor,
            'data_vencimento' => $vencimento,
            'data_pagamento' => $recebimento != null ? $recebimento : date('Y-m-d'),
            'categoria_id' => $this->input->post('categoria_id'),
            'conta_id'=> $conta,
            //'conta_id' => $this->input->post('conta_id'),
            'baixado' => $this->input->post('recebido'),
            'cliente_fornecedor' => set_value('cliente'),
            'forma_pgto' => $this->input->post('formaPgto'),
            'tipo' => set_value('tipo')
        );

        if ($this->financeiro_model->add('lancamentos',$data) == TRUE) {

            $sql = "UPDATE contas set saldo = saldo + ? WHERE idConta = ?";
            $this->db->query($sql, array($valor, $conta));
            echo json_encode(array('result'=> true));

            $this->session->set_flashdata('success','Receita adicionada com sucesso!');
            redirect($urlAtual);

        } else {
            $this->data['custom_error'] = '<div class="form_error"><p>Ocorreu um erro.</p></div>';

            echo json_encode(array('result'=> false));
        }
    }

    $this->session->set_flashdata('error','Ocorreu um erro ao tentar adicionar receita.');
    redirect($urlAtual);

}

To edit RECIPE and DEPESA, I am using the function:

    public function editar(){   
    if(!$this->permission->checkPermission($this->session->userdata('permissao'),'eLancamento')){
       $this->session->set_flashdata('error','Você não tem permissão para editar lançamentos.');
       redirect(base_url());
    }

    $this->load->library('form_validation');
    $this->data['custom_error'] = '';
    $urlAtual = $this->input->post('urlAtual');

    $this->form_validation->set_rules('descricao', '', 'trim|required|xss_clean');
    $this->form_validation->set_rules('fornecedor', '', 'trim|required|xss_clean');
    $this->form_validation->set_rules('valor', '', 'trim|required|xss_clean');
    $this->form_validation->set_rules('vencimento', '', 'trim|required|xss_clean');
    $this->form_validation->set_rules('pagamento', '', 'trim|xss_clean');

    if ($this->form_validation->run() == false) {
        $this->data['custom_error'] = (validation_errors() ? '<div class="form_error">' . validation_errors() . '</div>' : false);
    } else {

        $vencimento = $this->input->post('vencimento');
        $pagamento = $this->input->post('pagamento');
        $valor = $this->input->post('valor');
        $conta = $this->input->post('conta_id');

        try {

            $vencimento = explode('/', $vencimento);
            $vencimento = $vencimento[2].'-'.$vencimento[1].'-'.$vencimento[0];

            $pagamento = explode('/', $pagamento);
            $pagamento = $pagamento[2].'-'.$pagamento[1].'-'.$pagamento[0];

        } catch (Exception $e) {
           $vencimento = date('Y/m/d'); 
        }

        $data = array(
            'descricao' => $this->input->post('descricao'),
            //'valor' => $this->input->post('valor'),
            'valor' => $valor,
            'categoria_id' => $this->input->post('categoria_id'),
            'conta_id'=> $conta,
            'data_vencimento' => $vencimento,
            'data_pagamento' => $pagamento,
            'baixado' => $this->input->post('pago'),
            'cliente_fornecedor' => $this->input->post('fornecedor'),
            'forma_pgto' => $this->input->post('formaPgto'),
            'tipo' => $this->input->post('tipo')
        );

        if ($this->financeiro_model->edit('lancamentos',$data,'idLancamentos',$this->input->post('id')) == TRUE) {


            $sql = "UPDATE contas set saldo = saldo - ? WHERE idConta = ?";
            $this->db->query($sql, array($valor, $conta));
            echo json_encode(array('result'=> true));

            $this->session->set_flashdata('success','lançamento editado com sucesso!');
            redirect($urlAtual);
        } else {

            $this->session->set_flashdata('error','Ocorreu um erro ao tentar editar lançamento!');
            echo json_encode(array('result'=> false));
            redirect($urlAtual);
        }

        $sql = "UPDATE contas set saldo = saldo + ? WHERE idConta = ?";
            $this->db->query($sql, array($valor, $conta));
            echo json_encode(array('result'=> true));
    }

    $this->session->set_flashdata('error','Ocorreu um erro ao tentar editar lançamento.');
    echo json_encode(array('result'=> false));
    redirect($urlAtual);

    $data = array(
            'descricao' => $this->input->post('descricao'),
            //'valor' => $this->input->post('valor'),
            'valor' => $valor,
            'categoria_id' => $this->input->post('categoria_id'),
            'conta_id'=> $conta,
            'data_vencimento' => $this->input->post('vencimento'),
            'data_pagamento' => $this->input->post('pagamento'),
            'baixado' => $this->input->post('pago'),
            'cliente_fornecedor' => set_value('fornecedor'),
            'forma_pgto' => $this->input->post('formaPgto'),
            'tipo' => $this->input->post('tipo')
        );
    print_r($data);

}

To exclude REVENUE and DEPESA, I am using the function:

    public function excluirLancamento(){   

    if(!$this->permission->checkPermission($this->session->userdata('permissao'),'dLancamento')){
       $this->session->set_flashdata('error','Você não tem permissão para excluir lançamentos.');
       redirect(base_url());
    }

    $id = $this->input->post('id');

    if($id == null || ! is_numeric($id)){
        $json = array('result'=>  false);
        echo json_encode($json);
    }
    else{

        $result = $this->financeiro_model->delete('lancamentos','idLancamentos',$id); 
        if($result){
            $json = array('result'=>  true);
            echo json_encode($json);
        }
        else{
            $json = array('result'=>  false);
            echo json_encode($json);
        }

    }
}

FINANCIAL MODEL

function get($table,$fields,$where='',$perpage=0,$start=0,$one=false,$array='array'){

    $this->db->select($fields);
    $this->db->from($table);
    $this->db->order_by('data_vencimento', 'asc');
    $this->db->limit($perpage,$start);
    if($where){
        $this->db->where($where);
    }

    $query = $this->db->get();

    $result =  !$one  ? $query->result() : $query->row();
    return $result;
}


function getById($id){
    $this->db->where('idClientes',$id);
    $this->db->limit(1);
    return $this->db->get('clientes')->row();
}

function add($table,$data){
    $this->db->insert($table, $data);         
    if ($this->db->affected_rows() == '1')
    {
        return TRUE;
    }

    return FALSE;       
}

function edit($table,$data,$fieldID,$ID){
    $this->db->where($fieldID,$ID);
    $this->db->update($table, $data);

    if ($this->db->affected_rows() >= 0)
    {
        return TRUE;
    }

    return FALSE;       
}

function delete($table,$fieldID,$ID){
    $this->db->where($fieldID,$ID);
    $this->db->delete($table);
    if ($this->db->affected_rows() == '1')
    {
        return TRUE;
    }

    return FALSE;        
}

function count($table, $where) {

    $this->db->from($table);
    if($where){
        $this->db->where($where);
    }
    return $this->db->count_all_results();
}
php mysql codeigniter

  • At what point do you want to update your balance? In your table you do not have your account balance separated by launch. Since then you only do the calculation by subtracting the expense from the revenue. Plus correct and have the balance at the time of release

  • I want to update the balance when editing or deleting a release. There is another table(accounts) that contains the balance column. Wanted all calculations to be done in this table(accounts).

  • @Williancoqueiro Do you have any suggestions how I can do this? Would create a column in the tebela "throws", calculating the balance at the time of release?

  • It is the correct one. At launch time he calculate the balance at the moment by calculating the balance together with the launch and save to the bank.

  • Even so you can fetch the balance in the period.

  • @Williancoqueiro I will try to do something like this, but I still don’t know how the query will look. You know how to do, based on the controllers above?

  • Usually banks make daily balances. It would look better. Then every time you update a release, update your daily balance and balances for later days. And to differentiate expense revenue, create a fact_key column with D values for expense and C for credit. That’s a lot of stuff. But I believe it’s the best way.

  • The daily balance I refer to the balance of the day. When the consultation is done he makes the calculation of every day to find the balance of the account.

Show 3 more comments

2 answers

1


In this case you can work with TRIGGERS, which are triggers configured in a table, which execute a query whenever a record is inserted, updated or deleted.

Documentation: https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

Example:

CREATE TRIGGER trg_lancamentos_ai AFTER INSERT ON lancamentos
    FOR EACH ROW
    BEGIN
        UPDATE contas SET
            saldo = (
              CASE NEW.tipo
                WHEN '1' THEN saldo + NEW.valor
                WHEN '0' THEN saldo - NEW.valor
                ELSE saldo
              END
            )
        WHERE
            (idConta = NEW.conta_id);
    END;

CREATE TRIGGER trg_lancamentos_au AFTER UPDATE ON lancamentos
    FOR EACH ROW
    BEGIN
        UPDATE contas SET
            saldo = (
              CASE NEW.tipo
                WHEN '1' THEN saldo - OLD.valor + NEW.valor
                WHEN '0' THEN saldo + OLD.valor - NEW.valor
                ELSE saldo
              END
            )
        WHERE
            (idConta = NEW.conta_id);
    END;

CREATE TRIGGER trg_lancamentos_ad AFTER DELETE ON lancamentos
    FOR EACH ROW
    BEGIN
        UPDATE contas SET
            saldo = (
              CASE OLD.tipo
                WHEN '1' THEN saldo - OLD.valor
                WHEN '0' THEN saldo + OLD.valor
                ELSE saldo
              END
            )
        WHERE
            (idConta = OLD.conta_id);
    END;

Fiddle with example in action: https://www.db-fiddle.com/f/jL5RjoCtnM9yxz3eALTb3w/0

0

Instead of registering the expense with the value 10, for example, register as -10. In addition to saving a database column, it contributes to a better understanding. Return the sum of everything with the command SUM, example:

The table value column is populated with the following values:

150 -10 -30 180 -500

With the querry: SELECT SUM(valor) FROM tabela is returned the sum number of all values, profit (which in this case is -210)

  • What do you mean? I didn’t understand the reason for making an Insert with negative values. This query is already doing this: $sql = "UPDATE accounts set balance = balance - ? WHERE idConta = ?" ; $this->db->query($sql, array($value, $account); .

Browser other questions tagged php mysql codeigniter

You are not signed in. Login or sign up in order to post.