i whant to count a number of policy that have the status 2 and filter by date and by health_facilities
i have 4 tables this are the model
class Policy(core_models.VersionedModel):
id = models.AutoField(db_column='PolicyID', primary_key=True)
uuid = models.CharField(db_column='PolicyUUID', max_length=36, default=uuid.uuid4, unique=True)
stage = models.CharField(db_column='PolicyStage', max_length=1, blank=True, null=True)
status = models.SmallIntegerField(db_column='PolicyStatus', blank=True, null=True)
value = models.DecimalField(db_column='PolicyValue', max_digits=18, decimal_places=2, blank=True, null=True)
family = models.ForeignKey(Family, models.DO_NOTHING, db_column='FamilyID', related_name="policies")
enroll_date = fields.DateField(db_column='EnrollDate')
start_date = fields.DateField(db_column='StartDate')
effective_date = fields.DateField(db_column='EffectiveDate', blank=True, null=True)
expiry_date = fields.DateField(db_column='ExpiryDate', blank=True, null=True)
product = models.ForeignKey(Product, models.DO_NOTHING, db_column='ProdID', related_name="policies")
officer = models.ForeignKey(Officer, models.DO_NOTHING, db_column='OfficerID', blank=True, null=True,
related_name="policies")
offline = models.BooleanField(db_column='isOffline', blank=True, null=True)
audit_user_id = models.IntegerField(db_column='AuditUserID')
class InsureePolicy(core_models.VersionedModel):
id = models.AutoField(db_column='InsureePolicyID', primary_key=True)
insuree = models.ForeignKey(Insuree, models.DO_NOTHING, db_column='InsureeId', related_name="insuree_policies")
policy = models.ForeignKey("policy.Policy", models.DO_NOTHING, db_column='PolicyId',
related_name="insuree_policies")
enrollment_date = core.fields.DateField(db_column='EnrollmentDate', blank=True, null=True)
start_date = core.fields.DateField(db_column='StartDate', blank=True, null=True)
effective_date = core.fields.DateField(db_column='EffectiveDate', blank=True, null=True)
expiry_date = core.fields.DateField(db_column='ExpiryDate', blank=True, null=True)
offline = models.BooleanField(db_column='isOffline', blank=True, null=True)
audit_user_id = models.IntegerField(db_column='AuditUserID')
class Insuree(core_models.VersionedModel, core_models.ExtendableModel):
id = models.AutoField(db_column='InsureeID', primary_key=True)
uuid = models.CharField(db_column='InsureeUUID', max_length=36, default=uuid.uuid4, unique=True)
family = models.ForeignKey(Family, models.DO_NOTHING, blank=True, null=True,
db_column='FamilyID', related_name="members")
chf_id = models.CharField(db_column='CHFID', max_length=12, blank=True, null=True)
last_name = models.CharField(db_column='LastName', max_length=100)
other_names = models.CharField(db_column='OtherNames', max_length=100)
gender = models.ForeignKey(Gender, models.DO_NOTHING, db_column='Gender', blank=True, null=True,
related_name='insurees')
dob = core.fields.DateField(db_column='DOB')
dead = models.BooleanField(db_column='Dead')
dod = core.fields.DateField(db_column='DOD', null=True,)
deathReason = models.CharField(db_column='DeathReason', max_length=500, null=True,)
def age(self, reference_date=None):
head = models.BooleanField(db_column='IsHead')
marital = models.CharField(db_column='Marital', max_length=1, blank=True, null=True)
passport = models.CharField(max_length=25, blank=True, null=True)
phone = models.CharField(db_column='Phone', max_length=50, blank=True, null=True)
email = models.CharField(db_column='Email', max_length=100, blank=True, null=True)
current_address = models.CharField(db_column='CurrentAddress', max_length=200, blank=True, null=True)
geolocation = models.CharField(db_column='GeoLocation', max_length=250, blank=True, null=True)
current_village = models.ForeignKey(
location_models.Location, models.DO_NOTHING, db_column='CurrentVillage', blank=True, null=True)
photo = models.OneToOneField(InsureePhoto, models.DO_NOTHING,
db_column='PhotoID', blank=True, null=True, related_name=' ')
photo_date = core.fields.DateField(db_column='PhotoDate', blank=True, null=True)
card_issued = models.BooleanField(db_column='CardIssued')
relationship = models.ForeignKey(
Relation, models.DO_NOTHING, db_column='Relationship', blank=True, null=True,
related_name='insurees')
profession = models.ForeignKey(
Profession, models.DO_NOTHING, db_column='Profession', blank=True, null=True,
related_name='insurees')
education = models.ForeignKey(
Education, models.DO_NOTHING, db_column='Education', blank=True, null=True,
related_name='insurees')
type_of_id = models.ForeignKey(
IdentificationType, models.DO_NOTHING, db_column='TypeOfId', blank=True, null=True)
health_facility = models.ForeignKey(
location_models.HealthFacility, models.DO_NOTHING, db_column='HFID', blank=True, null=True,
related_name='insurees')
class HealthFacility(core_models.VersionedModel):
id = models.AutoField(db_column='HfID', primary_key=True)
uuid = models.CharField(
db_column='HfUUID', max_length=36, default=uuid.uuid4, unique=True)
code = models.CharField(db_column='HFCode', max_length=8)
name = models.CharField(db_column='HFName', max_length=100)
acc_code = models.CharField(
db_column='AccCode', max_length=25, blank=True, null=True)
legal_form = models.ForeignKey(
HealthFacilityLegalForm, models.DO_NOTHING,
db_column='LegalForm',
related_name="health_facilities")
level = models.CharField(db_column='HFLevel', max_length=1)
sub_level = models.ForeignKey(
HealthFacilitySubLevel, models.DO_NOTHING,
db_column='HFSublevel', blank=True, null=True,
related_name="health_facilities")
location = models.ForeignKey(
Location, models.DO_NOTHING, db_column='LocationId')
address = models.CharField(
db_column='HFAddress', max_length=100, blank=True, null=True)
phone = models.CharField(
db_column='Phone', max_length=50, blank=True, null=True)
fax = models.CharField(
db_column='Fax', max_length=50, blank=True, null=True)
email = models.CharField(
db_column='eMail', max_length=50, blank=True, null=True)
care_type = models.CharField(db_column='HFCareType', max_length=1)
services_pricelist = models.ForeignKey('medical_pricelist.ServicesPricelist', models.DO_NOTHING,
db_column='PLServiceID', blank=True, null=True,
related_name="health_facilities")
items_pricelist = models.ForeignKey('medical_pricelist.ItemsPricelist', models.DO_NOTHING, db_column='PLItemID',
blank=True, null=True, related_name="health_facilities")
offline = models.BooleanField(db_column='OffLine')
# row_id = models.BinaryField(db_column='RowID', blank=True, null=True)
audit_user_id = models.IntegerField(db_column='AuditUserID')
def __str__(self):
return self.code " " self.name
@classmethod
def get_queryset(cls, queryset, user, **kwargs):
# GraphQL calls with an info object while Rest calls with the user itself
if isinstance(user, ResolveInfo):
user = user.context.user
if user.has_perms(LocationConfig.gql_query_health_facilities_perms) and queryset is None:
queryset = HealthFacility.objects
else:
queryset = cls.filter_queryset(queryset)
if settings.ROW_SECURITY and user.is_anonymous:
return queryset.filter(id=-1)
if settings.ROW_SECURITY:
dist = UserDistrict.get_user_districts(user._u)
return queryset.filter(
location_id__in=[l.location_id for l in dist]
)
return queryset
def cs_in_use_query(user, **kwargs):
date_from = kwargs.get("date_from")
date_to = kwargs.get("date_to")
hflocation = kwargs.get("hflocation")
hfid = kwargs.get("hfid")
policy1 = kwargs.get("policy1")
format = "%Y-%m-%d"
date_from_object = datetime.datetime.strptime(date_from, format)
date_from_str = date_from_object.strftime("%d/%m/%Y")
date_to_object = datetime.datetime.strptime(date_to, format)
date_to_str = date_to_object.strftime("%d/%m/%Y")
dictBase = {}
dictBase = {
"dateFrom": date_from_str,
"dateTo": date_to_str,
}
dict1 = {}
policy = Policy.objects.values_list("id").filter(
validity_from__gte = date_from,
validity_to__lte = date_to,
status = 2)
list1 = list(policy)
dict2 = {}
policyinsuree = InsureePolicy.objects.values_list( 'policy_id', 'insuree_id'
).filter(**dict1)
list2 = list(policyinsuree)
dict3= {}
insuree = Insuree.objects.values_list('code', 'id').filter(**dict2)
list3 = list(insuree)
dictGeo = {}
if hflocation and hflocation!="0" :
hflocationObj = HealthFacility.objects.filter(
code=hflocation,
validity_to__isnull=True
).first()
dictBase["fosa"] = hflocationObj.name
dictGeo['health_facility'] = hflocationObj.id
dictBase["post"]= str(policy)
return dictBase
CodePudding user response：
Your question is not clear.
If you want to get the count of all the insurance policies for a specific health facility, filtered by date and status, you can do so:
from django.db.models import Q
count = InsureePolicy.objects.filter(
Q(insuree__health_facility=health_facility) &
Q(policy__status=2) &
Q(start_date__gte=date_from) &
Q(expiry_date__lte=date_to)
).count()
This will return how many insurance policies a specific health facility has, where the parent policy has status 2 and filtered by date.
EDIT:
If however you want to see how many different types of Policies a specific health facility has, you can do this:
count = Policy.objects.filter(
Q(insuree_policies__insuree__health_facility=health_facility)
Q(status=2) &
Q(start_date__gte=date_from) &
Q(expiry_date__lte=date_to)
CodePudding user response：
Try doing:
policies = (Policy.objects.filter(status=2)
.filter(start_date__gte=date_from
.filter(expiry_date__lte=date_to).count()
This will return the count of your Policy objects falling into that filter category.
If you just want the queryset remove the count and you'll get a queryset with all the Policy instances.