Help Query Sum

Asked

Viewed 77 times

0

I’m trying to retrieve a column and make a sum - using the SUM - according to the time zone, but without success. You can help me with this issue?

Detail: I’m not using exactly a table, but perform the call of a function of the SQL. The application works well with this function.

Function performed:

Função SQL

Follows excerpt from the controller

public function abertura( Request $request, $carteira, $dia, $servico, $segmento )
{

    $servico  = $servico == 'NULL' ? null : $servico;
    $segmento = $segmento == 'NULL' ? null : $segmento;

    $data = Date::createFromFormat( 'Y-m-d', $dia, tz() );

    $servicos = Receptivo::makeSegmento( $data->format( 'Ymd' ), $carteira )
        ->orderByRaw( 'REPLACE(REPLACE(REPLACE(REPLACE(NOME_SERVICO,\'SANTANDER_IN_VAREJO_\',\'\'),\'IN_\',\'\'),\'SERVICE_\',\'\'),\'VAREJO_\',\'\')' )
        ->get();

    $segmentos = Receptivo::makeSegmentoServico( ucfirst( strtolower( $carteira ) ) )
        ->selectRaw( 'distinct segmento' )->get();

    $datas = collect( range( 0, 90 ) )->map( function ( $incremento ) {
        return now_tz()->copy()->subDays( $incremento );
    } );

    $receb = Receptivo::makeAbertura( $data->format( 'Ymd' ), $carteira, $servico, $segmento )
        ->orderBy( 'Faixa_Horario' )->get();

    //Soma
    $Query = Receptivo::where('Faixa_Horario', 'Faixa_Horario')->sum('Atendimento_Cliente_com_Negocio');
    return view( 'reports.hora-a-hora.receptivo.abertura.index',
        compact( 'carteira', 'receb', 'datas', 'data', 'servico', 'servicos', 'segmento', 'segmentos', 'Query' ) );
}

Excerpt from the model:

    public static function makeAbertura( $date, $carteira, $servico = null, $segmento = null )
{
    /** @var static $instance */
    $instance = new static;

    $servico = $servico == null ? 'NULL' : "'" . $servico . "'";

    $segmento = $segmento == null ? 'NULL' : "'" . $segmento . "'";

    $table = sprintf( 'FN_HORA_HORA_RECEPTIVO_SANTANDER_%s_ABERTURA(\'%s\', %s, %s)',
        $carteira,
        $date,
        $servico,
        $segmento
    );
    //dd($table);
    return $instance->setTable( $table );
}
query laravel-5 querybuilder

2 answers

1


To give sum you have to have applied the function get before

$Query = Receptivo::where('Faixa_Horario', 'Faixa_Horario')->get()->sum('Atendimento_Cliente_com_Negocio');

I don’t understand why your Where is: where('Faixa_Horario', 'Faixa_Horario') in the second parameter you must pass the value you want to match in your Where, example:

$Query = Receptivo::where('Faixa_Horario', 8)->get()->sum('Atendimento_Cliente_com_Negocio');

  • Marcelo thanks for the return. add Faixa_horario pq hora tem q ser igual a hora, correto? Outra dúvidas na modelo tenho varia foções,?

  • 'time has to be equal to time' I did not quite understand what I meant by that, my doubt was why 'Faixa_horario' does not refer to numerical value number that is the type of column. To call only the makeAbertura function it must be Static model exactly as you put and called so, passing your parameters: Receptive::makeAbertura($date, $wallet);

1

Marcelo Zapatta, worked, performed as follows. 

 $receb = Receptivo::makeAbertura( $data->format( 'Ymd' ), $carteira, $servico, $segmento )
        ->whereIn('FAIXA_HORARIO', range(8, 20))
        ->groupBy('FAIXA_HORARIO')
        ->selectRaw('FAIXA_HORARIO, SUM(ATENDIMENTO_COM_NEGOCIO) AS SOMA')
        ->orderBy('FAIXA_HORARIO')
        ->get();

Browser other questions tagged query laravel-5 querybuilder

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