public function index()
{
if (Auth::check()) {
$user_id = Auth::user()->id;
$colonia = Session::get("colonia");
//obtener el rol que desempeña en la colonia
$rol_id = AssigmentRole::where('user_id', '=', $user_id)->where('colony_id', '=', $colonia)->pluck('role_id');
Session::put("rol_usuario", $rol_id);
$permisos = array();
$permisos = DB::table('permission_role')->where("role_id", "=", $rol_id)->select("permission_role.id", "permission_role.state")->orderBy("permission_role.id")->get();
Session::put("dato", $permisos);
$urbanism = Urbanism::where('colony_id', '=', $colonia)->first();
$urbanismo = $urbanism->id;
$urb_name = $urbanism->Colony->name;
$breadcrumbs = Neighbors::where('user_id', '=', $user_id)->first();
$breadcrumbs_data = $breadcrumbs->name . " " . $breadcrumbs->last_name . " [ " . $urb_name . " ]";
$Total = DB::table('neighbors_properties')->select(DB::raw('Count(neighbors_properties.id) as total'))->where('neighbors_properties.urbanism_id', '=', $urbanismo)->get();
$Total = $Total[0]->total;
$PorAceptar = DB::table('invited_neighbors')->select(DB::raw('count(invited_neighbors.id) as porAceptar'))->where('invited_neighbors.confirmed', '=', 0)->where('invited_neighbors.urbanism_id', '=', $urbanismo)->get();
$PorAceptar = $PorAceptar[0]->porAceptar;
$Aceptadas = DB::table('invited_neighbors')->select(DB::raw('count(invited_neighbors.id) as Aceptadas'))->where('invited_neighbors.confirmed', '=', 1)->where('invited_neighbors.urbanism_id', '=', $urbanismo)->get();
$Aceptadas = $Aceptadas[0]->Aceptadas;
$ano = date("Y");
$monthly_all = MonthlyFee::where('monthly_fee.urbanism_id', '=', $urbanismo)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->get();
$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Decem");
$cuotas = array();
foreach ($monthly_all as $cuota_mensual) {
$ini = (int) date("m", strtotime($cuota_mensual->since));
$fin = (int) date("m", strtotime($cuota_mensual->until));
if ($cuota_mensual->until == NULL) {
$fin = (int) date("m");
}
for ($i = $ini; $i <= $fin; $i++) {
$cuotas[$months[$i - 1]] = $cuota_mensual->amount;
}
}
$j = (int) date("m");
$monthly_fee = $cuotas[$months[$j - 1]];
$mes = date("M");
$pagoMensual = PaymentStates::join('neighbors_properties', 'payment_states.neighbor_property_id', '=', 'neighbors_properties.id')->where($mes, '>=', $monthly_fee)->where('neighbors_properties.urbanism_id', '=', $urbanismo)->select(DB::raw('count(neighbors_properties.id) as cant'))->get();
if ($pagoMensual) {
$pagoMensual = $pagoMensual[0]->cant;
} else {
$pagoMensual = 0;
}
$Egresos = Expense::join('sub_accounts', 'expenses.sub_account_id', '=', 'sub_accounts.id')->where('expenses.urbanism_id', '=', $urbanismo)->select(DB::raw('sum(expenses.amount) as amount'), 'sub_accounts.description')->groupBy('sub_accounts.description')->get();
$Ingresos = Payment::join('sub_accounts', 'payments.sub_account_id', '=', 'sub_accounts.id')->join('neighbors_properties', 'payments.neighbor_property_id', '=', 'neighbors_properties.id')->where('neighbors_properties.urbanism_id', '=', $urbanismo)->select(DB::raw('sum(payments.amount) as amount'), 'sub_accounts.description')->groupBy('sub_accounts.description')->get();
$EgresosMensual = Expense::where('expenses.urbanism_id', '=', $urbanismo)->select(DB::raw('sum(expenses.amount) as amount'), DB::raw('DATE_FORMAT(expenses.created_at,\'%m\') as mes'))->groupBy(DB::raw('Month(expenses.created_at)'))->get();
$IngresosMensual = Payment::join('neighbors_properties', 'payments.neighbor_property_id', '=', 'neighbors_properties.id')->where('neighbors_properties.urbanism_id', '=', $urbanismo)->select(DB::raw('sum(payments.amount) as amount'), DB::raw('DATE_FORMAT(payments.created_at,\'%m\') as mes'))->groupBy(DB::raw('Month(payments.created_at)'))->get();
return View::make('dashboard.home', ['Total' => $Total, 'PorAceptar' => $PorAceptar, 'Aceptadas' => $Aceptadas, 'monthly_fee' => $monthly_fee, 'pagoMensual' => $pagoMensual, 'Egresos' => $Egresos, 'Ingresos' => $Ingresos, 'EgresosMensual' => $EgresosMensual, 'IngresosMensual' => $IngresosMensual, 'Nombre' => $breadcrumbs_data]);
} else {
return Redirect::action('UsersController@login');
}
}
public function modalEstadoCuenta()
{
$neighbor_property_id = Input::get('neighbor_property_id');
$user_id = Auth::user()->id;
$colonia = Session::get("colonia");
$urbanism = Urbanism::where('colony_id', '=', $colonia)->first();
$urbanismo = $urbanism->id;
$urb_name = $urbanism->name;
$property_id = NeighborProperty::where('urbanism_id', '=', $urbanismo)->pluck('id');
$vigencia = date("Y-m");
$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Decem");
$today = date("Y-m-d");
$ano = date("Y");
$selano = Payment::select(DB::raw('DATE_FORMAT(payments.created_at,\'%Y\') as y'))->groupBy(DB::raw('Year(payments.created_at)'))->orderBy(DB::raw('Year(payments.created_at)'))->get();
$monthly_all = MonthlyFee::where('monthly_fee.urbanism_id', '=', $urbanismo)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->get();
$monthly_ini = MonthlyFee::where('monthly_fee.urbanism_id', '=', $urbanismo)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->orderBy('monthly_fee.created_at', 'ASC')->pluck('since');
$mes_ini = (int) date("m", strtotime($monthly_ini));
$cuotas = array();
foreach ($monthly_all as $cuota_mensual) {
$ini = (int) date("m", strtotime($cuota_mensual->since));
$fin = (int) date("m", strtotime($cuota_mensual->until));
if ($cuota_mensual->until == NULL) {
$fin = (int) date("m");
}
for ($i = $ini; $i <= $fin; $i++) {
$cuotas[$months[$i - 1]] = $cuota_mensual->amount;
}
}
for ($j = 0; $j <= 11; $j++) {
if ($j + 1 < $mes_ini) {
$saldoAnteriol[$j] = "";
$cuataMes[$j] = "";
$totalDebe[$j] = "";
$pagos[$j] = "";
$saldoTotal[$j] = "";
} elseif ($j + 1 <= date("m")) {
$saldoAnteriol[$j] = $saldoTotal[$j - 1];
$cuataMes[$j] = -$cuotas[$months[$j]];
$totalDebe[$j] = $saldoAnteriol[$j] + $cuataMes[$j];
$neighbor_payments = PaymentStates::with('NeighborProperty')->where('neighbor_property_id', '=', $neighbor_property_id)->first();
if ($neighbor_payments) {
$pagos[$j] = $neighbor_payments->{$months}[$j] == NULL ? 0 : $neighbor_payments->{$months}[$j];
} else {
$pagos[$j] = 0;
}
$saldoTotal[$j] = $totalDebe[$j] + $pagos[$j];
} elseif ($j + 1 > date("m")) {
$saldoAnteriol[$j] = "";
$cuataMes[$j] = "";
$totalDebe[$j] = "";
$pagos[$j] = "";
$saldoTotal[$j] = "";
}
}
$neighbor = NeighborProperty::find($neighbor_property_id);
return View::make('dashboard.payments.neighbors.table_neigbor', compact('breadcrumbs_data', 'saldoAnteriol', 'cuataMes', 'totalDebe', 'pagos', 'saldoTotal', 'mes_ini', 'ini', 'months', 'color', 'neighbor'));
}
public function store_cuota()
{
$post = Input::All();
$urbanism_id = Input::get('urbanism');
$fecha_actual = date("Y-m") . "-01";
$until_fecha = strtotime('-1 day', strtotime($fecha_actual));
$cuota_ant = MonthlyFee::where('urbanism_id', '=', $urbanism_id)->orderBy('created_at', 'DESC')->first();
$cuota_ant->until = date('Y-m-d', $until_fecha);
$cuota_ant->update(['id']);
$monthly_fee = new MonthlyFee();
$monthly_fee->urbanism_id = $urbanism_id;
$monthly_fee->amount = Input::get('monthly_fee');
$monthly_fee->since = $fecha_actual;
$monthly_fee->until = NULL;
$monthly_fee->save();
$notice_msg = 'Cuota agregada exitosamente';
return Redirect::action('ColonyController@edit_cuota')->with('error', false)->with('msg', $notice_msg)->with('class', 'info');
}
public function charges_store()
{
$user_id = Auth::user()->id;
$colonia = Session::get("colonia");
$urbanism = Urbanism::where('colony_id', '=', $colonia)->first();
$collector = Collector::where('user_id', '=', $user_id)->where('urbanism_id', '=', $urbanism->id)->first();
$ano = date("Y");
$neighbor = Input::get('valorid');
$amount = Input::get('amount');
$income_charge = new Payment();
$income_charge->neighbor_property_id = $neighbor;
$income_charge->collector_id = $collector->id;
$income_charge->amount = $amount;
$income_charge->sub_account_id = Input::get('sub_account_id');
$income_charge->coments = Input::get('coments');
$income_charge->deposit = null;
$income_charge->debt = null;
$income_charge->status_id = 1;
$income_charge->updated_at = date('Y') . '-01-01 ' . date('H:i:s');
if ($income_charge->save()) {
$neighbor_payments = $income_charge;
$payment_state = PaymentStates::where('neighbor_property_id', '=', $neighbor)->where('year', '=', $ano)->orderBy('created_at', 'desc')->first();
$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
$monthly_all = MonthlyFee::where('monthly_fee.urbanism_id', '=', $collector->urbanism_id)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->get();
$monthly_ini = MonthlyFee::where('monthly_fee.urbanism_id', '=', $collector->urbanism_id)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->orderBy('monthly_fee.created_at', 'ASC')->pluck('since');
$mes_ini = (int) date("m", strtotime($monthly_ini));
$cuotas = array();
foreach ($monthly_all as $cuota_mensual) {
$ini = (int) date("m", strtotime($cuota_mensual->since));
$fin = (int) date("m", strtotime($cuota_mensual->until));
if ($cuota_mensual->until == NULL) {
$fin = (int) date("m");
}
for ($i = $ini; $i <= $fin; $i++) {
$cuotas[$months[$i - 1]] = $cuota_mensual->amount;
}
}
$acumulado = $amount;
//para guardar el monto
$ValorInicial = $amount;
//para guardar el valor inicial del monto ingresado
$ValorResto = 0;
//para comparar con el valor inicial
$balance = Balance::where('neighbor_property_id', '=', $neighbor)->pluck('amount');
$balance_saldo = Balance::where('neighbor_property_id', '=', $neighbor)->first();
if ($balance) {
$saldo = $balance;
if ($saldo > $amount) {
$saldo = $saldo - $amount;
$amount = 0;
$balance_saldo->amount = $saldo;
$balance_saldo->update(['id']);
} else {
if ($saldo < $amount) {
$saldo = $amount - $saldo;
$amount = $saldo;
$balance_saldo->delete(['id']);
} else {
$saldo = 0;
$amount = 0;
$balance_saldo->delete(['id']);
}
}
} else {
$saldo = 0;
}
//Si el usuario no ha realizado ningún pago, se llena la tabla Payment_States por primera vez
if (!$payment_state) {
$TotalCuotas = 0;
$payment_state_detail = new PaymentStates();
$payment_state_detail->neighbor_property_id = $neighbor_payments->neighbor_property_id;
$payment_state_detail->year = date('Y', strtotime($neighbor_payments->created_at));
//Ciclo hasta el mes actual
for ($i = $mes_ini - 1; $i < date('m'); $i++) {
if (date('m') != $i + 1) {
if ($amount > $cuotas[$months[$i]]) {
$payment_state_detail->{$months}[$i] = $cuotas[$months[$i]];
$amount = $amount - $cuotas[$months[$i]];
} else {
if ($amount < $cuotas[$months[$i]] && $amount != 0) {
$payment_state_detail->{$months}[$i] = $amount;
$amount = 0;
} else {
$payment_state_detail->{$months}[$i] = $cuotas[$months[$i]];
$amount = 0;
}
}
} else {
//si es el mes actual
// for: suma todas las cuotas de cada mes, las q se deben
for ($j = $mes_ini - 1; $j < date('m'); $j++) {
if ($payment_state_detail->{$months}[$j] == null) {
$TotalCuotas = $TotalCuotas + $cuotas[$months[$j]];
} elseif ($payment_state_detail->{$months}[$j] < $cuotas[$months[$j]] && $payment_state_detail->{$months}[$j] > 0) {
$resto_mes = $cuotas[$months[$j]] - $payment_state_detail->{$months}[$j];
$TotalCuotas = $TotalCuotas + $resto_mes;
}
}
if ($TotalCuotas > $amount) {
$TotalCuotas = $TotalCuotas - $amount;
$payment_state_detail->{$months}[$i] = "-" . $TotalCuotas;
} elseif ($TotalCuotas < $amount) {
$TotalCuotas = $amount - $TotalCuotas;
$payment_state_detail->{$months}[$i] = $cuotas[$months[$i]] + $TotalCuotas;
$amount = $TotalCuotas;
} elseif ($TotalCuotas == $amount) {
//si la cuota es igual al monto que esta pagando
$payment_state_detail->{$months}[$i] = $cuotas[$months[$i]];
$amount = 0;
}
}
}
//fin del ciclo
$payment_state_detail->accumulated = $acumulado;
//Se guardan los datos en Payment_States
$payment_state_detail->save();
} else {
//si ya tiene registros en payment_state
$fondo = $payment_state->accumulated + $amount;
$valorResto = 0;
$abono = 0;
for ($i = $mes_ini - 1; $i < date('m'); $i++) {
if (date('m') != $i + 1) {
if ($payment_state->{$months}[$i]) {
if ($payment_state->{$months}[$i] != $cuotas[$months[$i]] && $payment_state->{$months}[$i] > 0) {
$valorResto = $ValorResto + $payment_state->{$months}[$i];
$amount = $amount + $valorResto;
if ($amount > $cuotas[$months[$i]]) {
$payment_state->{$months}[$i] = $cuotas[$months[$i]];
$amount = $amount - $cuotas[$months[$i]];
} else {
$payment_state->{$months}[$i] = $amount;
$amount = 0;
}
}
} else {
if ($amount > $cuotas[$months[$i]]) {
$payment_state->{$months}[$i] = $cuotas[$months[$i]];
$amount = $amount - $cuotas[$months[$i]];
} else {
if ($amount < $cuotas[$months[$i]]) {
$payment_state->{$months}[$i] = $amount;
$amount = 0;
} else {
$payment_state->{$months}[$i] = $cuotas[$months[$i]];
$amount = 0;
}
}
}
} else {
//si es el mes actual
if ($payment_state->{$months}[$i] < 0) {
$deuda = -1 * $payment_state->{$months}[$i];
$deuda = $amount - $deuda;
if ($deuda < 0) {
$payment_state->{$months}[$i] = $deuda;
} else {
if ($deuda == 0) {
$payment_state->{$months}[$i] = $cuotas[$months[$i]];
} else {
$payment_state->{$months}[$i] = $deuda + $cuotas[$months[$i]];
}
}
} else {
$abono = $payment_state->{$months}[$i] + $amount;
$payment_state->{$months}[$i] = $abono;
}
}
}
$payment_state->accumulated = $fondo;
$payment_state->update(['id']);
}
//---envio por email de recibo al pagador---
$mensaje = null;
$monto = Input::get('amount');
$infoCobrador = Neighbors::with('NeighborProperty')->where('user_id', '=', $user_id)->first();
$infoPagador = NeighborProperty::where('id', '=', $neighbor)->first();
$email = UserNeighbors::where('id', '=', $infoPagador->Neighbors->user_id)->pluck('email');
$tipoUrb = $infoPagador->Urbanism->UrbanismType->id;
if ($tipoUrb == 3) {
$Domicilio = "Piso " . $infoPagador->Building->description . ' - Apartamento ' . $infoPagador->num_house_or_apartment;
} else {
$Domicilio = "Calle " . $infoPagador->Street->name . ' - Casa ' . $infoPagador->num_house_or_apartment;
}
$Colony = Urbanism::with('Colony.City')->where('id', '=', $infoPagador->Urbanism->id)->first();
$state = DB::table('states')->where('id', $Colony->Colony->City->state_id)->first();
$dias = array("Domingo", "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado");
$meses = array("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre");
$fecha = $dias[date('w')] . " " . date('d') . " de " . $meses[date('n') - 1] . " del " . date('Y');
$pay_states = PaymentStates::where('neighbor_property_id', '=', $neighbor)->where('year', '=', $ano)->get();
$data = array('Cobrador' => $infoCobrador->name . " " . $infoCobrador->last_name, 'Nombre' => $infoPagador->Neighbors->name . " " . $infoPagador->Neighbors->last_name, 'Domicilio' => $Domicilio, 'monto' => $monto, 'Fecha' => $fecha, 'colonia' => $Colony->Colony->name, 'urbanismo' => $infoPagador->Urbanism->name, 'estado' => $state->name, 'ciudad' => $Colony->Colony->City->name, 'payments' => $pay_states, 'months' => $months, 'cuotas' => $cuotas, 'mes_ini' => $mes_ini);
try {
Mail::send('emails.Recibo', $data, function ($message) use($email) {
$message->to($email);
$message->subject('Recibo de pago');
});
} catch (Exception $exc) {
Mail::send('emails.Recibo', $data, function ($message) use($email) {
$message->to($email);
$message->subject('Recibo de pago');
});
}
// ---/fin de envio por email de recibo al pagador/----
//se agrego el logs
Event::fire('logs', 'hizo un Cobro al Domicilio ' . $Domicilio . ' de un Monto de' . $monto);
//fin logs
return Redirect::action('IncomesController@charges_index')->with('error', false)->with('msg', 'Cobro ingresado con éxito.')->with('class', 'info');
} else {
return Redirect::back()->with('error', true)->with('msg', '¡Algo salió mal! Contacte con administrador.')->with('class', 'danger');
}
}
public function reportStatus()
{
$user_id = Auth::user()->id;
$colonia = Session::get("colonia");
$urbanism = Urbanism::where('colony_id', '=', $colonia)->first();
$urbanismo = $urbanism->id;
$urb_name = $urbanism->name;
$property_id = NeighborProperty::where('urbanism_id', '=', $urbanismo)->pluck('id');
$vigencia = date("Y-m");
$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Decem");
$today = date("Y-m-d");
$ano = date("Y");
$selano = Payment::select(DB::raw('DATE_FORMAT(payments.created_at,\'%Y\') as y'))->groupBy(DB::raw('Year(payments.created_at)'))->orderBy(DB::raw('Year(payments.created_at)'))->get();
$monthly_all = MonthlyFee::where('monthly_fee.urbanism_id', '=', $urbanismo)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->get();
$monthly_ini = MonthlyFee::where('monthly_fee.urbanism_id', '=', $urbanismo)->where(DB::raw('DATE_FORMAT(monthly_fee.since,\'%Y\')'), '=', $ano)->orderBy('monthly_fee.created_at', 'ASC')->pluck('since');
$mes_ini = (int) date("m", strtotime($monthly_ini));
$cuotas = array();
foreach ($monthly_all as $cuota_mensual) {
$ini = (int) date("m", strtotime($cuota_mensual->since));
$fin = (int) date("m", strtotime($cuota_mensual->until));
if ($cuota_mensual->until == NULL) {
$fin = (int) date("m");
}
for ($i = $ini; $i <= $fin; $i++) {
$cuotas[$months[$i - 1]] = $cuota_mensual->amount;
}
}
$neighbors = NeighborProperty::with('Neighbors')->where('urbanism_id', '=', $urbanismo)->get();
$breadcrumbs = Neighbors::select('neighbors.name as name_ne', 'neighbors.last_name', 'urbanisms.name as name_ur ')->join('neighbors_properties', 'neighbors.id', '=', 'neighbors_properties.neighbors_id')->join('urbanisms', 'neighbors_properties.urbanism_id', '=', 'urbanisms.id')->where('neighbors.user_id', '=', self::getUserId())->first();
$breadcrumbs_data = $breadcrumbs->name_ne . " " . $breadcrumbs->last_name . " [" . $breadcrumbs->name_ur . "]";
$data = ['mes_ini' => $mes_ini, 'months' => $months, 'cuotas' => $cuotas, 'selano' => $selano, 'urbanism' => $urbanismo, 'breadcrumbs_data' => $breadcrumbs_data, 'neighbors' => $neighbors, 'ini' => $ini, 'fin' => $fin, 'ano' => $ano, 'breadcrumbs_data' => $breadcrumbs_data];
$pdf = PDF::loadView('dashboard.reports.status.pdf', $data);
return $pdf->download('reporte_Estato_cuanta_general.pdf');
}
