Home > Blockchain >  Queryset filter
Queryset filter

Time:12-06

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.

  • Related