Currently I have a project in laravel but my programmer partner has disappeared and he is the one who is in charge of the heavy work, because I am less experienced.
I have a problem with a view, I have several tables:
1-Accounts
2-Users
3-Services
4-Subscriptions
5-Profiles
I have a view that generates subscriptions, which shows me through a select the services available in the system, then the accounts associated to the service and the User_id, then I can see the profiles that are associated to the accounts and that are available (that is to say that they have not been used), to be able to make a subscription of 1 Service in an account X, and a profile X of this account.
The situation I have is that I need that as well as it lists ONLY the profiles that are empty; then it lists only the accounts that have empty or available profiles. Well, when I choose an account that does not have available profiles, I have to select one by one the emails to see which one has available profiles.
This is the view of a new subscription
enter image description here
Services are listed
enter image description here
The accounts belonging to that service are displayed
enter image description here
Profiles available for the sample account [email protected]
enter image description here
For the case of the account [email protected] (no profiles in your name are available and therefore the profile list is empty)
enter image description here
What I need is to avoid the hassle of going account by account to see if it has free profiles or not. Inside my SubscriptionController I have these functions that go to this issue in question:
public function getAccounts($service_id){
$data = [];
$attr = [
'table'=>'accounts',
'columns'=>[
'id',
'email'
],
'compare'=>'service_id',
'compare_value'=>$service_id
];
$response = Helper::getDataSelect($attr,auth()->user()->id);
if($response){
$data = $response;
}
return response()->json(['data'=>$data]);
}
public function getProfiles($account_id){
$data = [];
$attr = [
'table'=>'profiles',
'columns'=>[
'id',
'name',
'pin'
],
'compare'=>'account_id',
'compare_value'=>$account_id
];
$response = Helper::getDataSelect($attr);
if($response){
foreach($response as $res){
$profile = Profile::find($res->id);
if($profile->subscriptions->count() == 0){
array_push($data, $res);
}
}
}
return response()->json(['data'=>$data]);
}
Inside the Helper I have this function
public static function getDataSelect($attr,$user_id=null){
if($attr){
if($user_id){
$data = DB::table($attr['table'])->select($attr['columns'])->where($attr['compare'],$attr['compare_value'])->where('user_id',$user_id)->get();
}else{
$data = DB::table($attr['table'])->select($attr['columns'])->where($attr['compare'],$attr['compare_value'])->get();
}
return $data;
}else{
return null;
}
}
And finally in my view I have a script that has to do with the selects
@section('content')
<div class="row" style="margin-top: 20px;">
<div class="col-md-12">
<div class="card card-success">
<div class="card-header">
<h2><i class="fas fa-star"></i> {{ $title }}</h2>
</div>
@include('admin.partials.form', ['element'=>'subscriptions', 'type'=>$type, 'id'=>@$data->id])
<div class="card-body">
<div class="form-group">
<label for="">Servicio:</label>
<select name="service_id" class="form-control form-control-sm @error('service_id') is-invalid @enderror">
<option value="-1">Seleccione</option>
@foreach($services as $service)
<option value="{{$service->id}}">{{$service->name}}</option>
@endforeach
</select>
@error('service_id')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Cuenta:</label>
<select name="account_id" class="form-control form-control-sm @error('account_id') is-invalid @enderror">
<option value="-1" >Seleccione</option>
</select>
@error('account_id')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Perfil:</label>
<select name="profile_id" class="form-control form-control-sm @error('profile_id') is-invalid @enderror">
<option value="-1">Seleccione</option>
</select>
@error('profile_id')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Cliente:</label>
<select name="customer_id" class="form-control form-control-sm @error('customer_id') is-invalid @enderror">
<option>Seleccione</option>
@foreach($customers as $customer)
<option value="{{$customer->id}}">{{$customer->name}} ({{$customer->phone}})</option>
@endforeach
</select>
@error('customer_id')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Monto:</label>
<input type="number" step="0.01" name="amount" class="form-control @error('amount') is-invalid @enderror" />
@error('amount')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Instrumento:</label>
<select name="paymeth_id" class="form-control form-control-sm @error('paymeth_id') is-invalid @enderror">
<option value="-1">Seleccione</option>
@foreach($paymeths as $paymeth)
<option value="{{$paymeth->id}}">{{$paymeth->instrumento}} ({{$paymeth->moneySymbol}})</option>
@endforeach
</select>
@error('paymeth_id')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Referencia:</label>
<input type="number" step="0.01" name="referencia" class="form-control @error('referencia') is-invalid @enderror" />
@error('referencia')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
<div class="form-group">
<label for="">Vencimiento:</label>
<input type="date" name="date_to" class="form-control @error('date_to') is-invalid @enderror" />
@error('date_to')
<span class="error invalid-feedback">{{ $message }}</span>
@enderror
</div>
</div>
<div class="card-footer">
@include('admin.partials.buttons',['cancelRoute'=>'subscriptions.index'])
</div>
</form>
</div>
</div>
</div>
@stop
@section('js')
<script>
$(document).ready(function(){
$("select").select2();
$("select[name='service_id']").change(function(){
let id = $(this).val();
if(id != '-1'){
$.get('/admin/get-accounts/'+id, function(response){
let data = response.data;
if(data.length > 0){
$("select[name='account_id']").html("<option value='-1'>Seleccione</option>");
$.each(data, function(v,e){
$("select[name='account_id']").append("<option value='"+e.id+"'>"+e.email+"</option>");
});
}
})
}else{
$("select[name='account_id']").html("<option>Seleccione</option>");
}
});
$("select[name='account_id']").change(function(){
let id = $(this).val();
if(id != '-1'){
$.get('/admin/get-profiles/'+id, function(response){
let data = response.data;
if(data.length > 0){
$("select[name='profile_id']").html("<option value='-1'>Seleccione</option>");
$.each(data, function(v,e){
$("select[name='profile_id']").append("<option value='"+e.id+"'>"+e.name+" ("+e.pin+")</option>");
});
}
})
}else{
$("select[name='profile_id']").html("<option value='-1'>Seleccione</option>");
}
});
});
</script>
@stop
I have tried a couple of things but I have no idea how to first collect the accounts that have those free profiles.
1
To solve the problem of listing only accounts that have available (unused) profiles, you need to modify the logic in your getAccounts function to only return accounts that have profiles available for subscription. Here’s how you can approach this:
Step 1: Modify the getAccounts function
You can filter accounts based on whether they have available profiles. Here’s an updated version of your getAccounts function:
public function getAccounts($service_id) {
$data = [];
// Fetch accounts that are associated with the service
$accounts = DB::table('accounts')
->where('service_id', $service_id)
->get();
foreach ($accounts as $account) {
// Check if the account has any available profiles
$availableProfiles = DB::table('profiles')
->where('account_id', $account->id)
->leftJoin('subscriptions', 'profiles.id', '=', 'subscriptions.profile_id')
->whereNull('subscriptions.profile_id') // Profiles not used in subscriptions
->count();
if ($availableProfiles > 0) {
// Add account to response if it has available profiles
$data[] = [
'id' => $account->id,
'email' => $account->email
];
}
}
return response()->json(['data' => $data]);
}
Explanation:
- The function fetches all accounts associated with the given service.
- For each account, it checks how many profiles are not yet associated with a subscription.
- Only accounts with available profiles are returned in the response.
Step 2: Modify the getProfiles function
Since the getProfiles function already filters profiles that are unused, you don’t need to modify it. But make sure it’s returning the profiles correctly.
Step 3: Update your JavaScript for selecting accounts
Ensure that your frontend correctly processes the available accounts:
$("select[name='service_id']").change(function() {
let id = $(this).val();
if(id != '-1'){
$.get('/admin/get-accounts/'+id, function(response) {
let data = response.data;
$("select[name='account_id']").html("<option value='-1'>Seleccione</option>");
if(data.length > 0){
$.each(data, function(v, e) {
$("select[name='account_id']").append("<option value='"+e.id+"'>"+e.email+"</option>");
});
}
});
} else {
$("select[name='account_id']").html("<option value='-1'>Seleccione</option>");
}
});
1