Блог

У меня есть 3 модели :

 class Master(models.Model):
    upload = models.ForeignKey(UploadMaster, on_delete=models.CASCADE, null=True)
    npp = models.CharField(max_length=12)
    periode = models.ForeignKey(ParameterPeriode, on_delete=models.CASCADE, null=True)
    client = models.ForeignKey(Client, on_delete=models.CASCADE, null=True)
    branch = models.ForeignKey(Branch, on_delete=models.CASCADE, null=True)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, null=True)
    vendor = models.ForeignKey(Vendor, on_delete=models.CASCADE, null=True)
    angsuran = models.CharField(max_length=20)
    tenor = models.SmallIntegerField(default=0)
    due_date = models.DateField(null=True)
    odh = models.SmallIntegerField(default=0)
    sisa_op = models.BigIntegerField(default=0)
    angsuran_tertunggak = models.BigIntegerField(default=0)
    angsuran_terbayar = models.BigIntegerField(default=0)
    angsuran_perbulan = models.BigIntegerField(default=0)
    total_denda = models.BigIntegerField(default=0)
    biaya_tagih = models.IntegerField(default=0)
    bayar_lancar = models.BigIntegerField(default=0)
    lunas_normal = models.BigIntegerField(default=0)
    pelsus = models.BigIntegerField(default=0)
    total_tagihan = models.BigIntegerField(default=0)
    credit_type = models.SmallIntegerField(choices=PRODUCT)
    product = models.ForeignKey(Product, on_delete=models.CASCADE, null=True)
    plate_number = models.CharField(max_length=20)
    salary_date = models.DateField(null=True)
    nama_bpkb = models.CharField(max_length=120, null=True)
    last_payment = models.DateField(null=True)
    last_location_payment = models.CharField(max_length=60, null=True)
    is_active = models.BooleanField(default=True)
    update_time = models.DateTimeField(auto_now_add=True)
    create_time = models.DateTimeField(auto_now_add=True)

class StatusCollect(models.Model):
   code = models.CharField(max_length=10)
   name = models.CharField(max_length=120)


class DataResult(models.Model):
   from reports.models import StatusCollect

   master = models.ForeignKey(Master, on_delete=models.CASCADE, null=True)
   vendor = models.ForeignKey(Vendor, on_delete=models.CASCADE, null=True)
   staff = models.ForeignKey(Pic, on_delete=models.CASCADE, null=True)
   assign_by = models.ForeignKey(User, on_delete=models.CASCADE, null=True)
   upload_master = models.ForeignKey(UploadMaster, on_delete=models.CASCADE, null=True)
   tgl_waktu_assign = models.DateTimeField(null=True)
   tgl_waktu_update = models.DateTimeField(null=True)
   tgl_ptp = models.DateField(null=True)
   total_ptp = models.BigIntegerField(default=0)
   phone_number = models.CharField(max_length=14, null=True)
   keterangan = models.TextField(null=True)
   cek = models.BooleanField(default=False)
   status_penyelesaian = models.SmallIntegerField(choices=STATUS_PENYELESAIAN, null=True)
   status = models.ForeignKey(StatusCollect, on_delete=models.CASCADE, null=True)
   is_active = models.BooleanField(default=True)
   create_time = models.DateTimeField(auto_now_add=True)
 

Это внутренняя таблица StatusCollect:

  | id  | code  |     name        |
 | --- | ----- | --------------  |
 |  1  | RTP   | Refused To Pay  |
 |  2  | PTP   | Promosed To Pay |
 |  3  | AP    | Already Paid    |
 

И это внутри таблицы DataResult:

  | id  |tgl_waktu_assign            |tgl_waktu_update          |tgl_ptp   | total_ptp | phone_number |keterangan|cek|status_penyelesaian|is_active|  create_time             |assign_by_id|master_id |staff_id |upload_master_id|vendor_id |status_id |
 | --- | ---------------------------| -------------------------|----------|-----------|--------------|----------|---|-------------------|---------|--------------------------|------------|----------|---------|----------------|----------|----------|
 |  1  |2021-12-01 13:42:13.000000  |2021-12-01 13:42:13.000000|2021-12-09| 20000000  |99999999999999|          | 0 |     1             | 1       |2021-12-01 13:42:13.000000|   3        |    367   |   2     |       1        |   1      |   1      |
 |  2  |2021-12-01 13:42:13.000000  |2021-12-01 13:42:13.000000|2021-12-09| 50000000  |99999999999999|          | 0 |     1             | 1       |2021-12-01 13:42:13.000000|   3        |    367   |   2     |       1        |   1      |   2      |
 |  3  |2021-12-01 13:42:13.000000  |2021-12-01 13:42:13.000000|2021-12-09| 80000000  |99999999999999|          | 0 |     1             | 1       |2021-12-01 13:42:13.000000|   3        |    367   |   2     |       1        |   1      |   2     |
 |  4  |2021-12-02 13:42:13.000000  |2021-12-01 13:42:13.000000|2021-12-09| 80000000  |99999999999999|          | 0 |     1             | 1       |2021-12-01 13:42:13.000000|   3        |    367   |   2     |       1        |   1      |   3      |
 

Поэтому я хочу каждый день подсчитывать, сколько данных находится в статусе сбора, а также в DataResult вместе с общим статусом сбора, содержащимся в DataResult.
И результат должен быть таким:

  |   Tanggal   | RTP | PTP | AP | Total |
 | ----------- | ----| ----|----|-------|
 |  2021-12-01 |  1  |  2  |  0 |   3   |
 |  2021-12-02 |  0  |  0  |  1 |   1   |
 

и из старого проекта, который использует CI, используя SQL-запрос, подобный этому:

  $status_collect = $this->db->from('status_collect')->get()->result();

    $sql="SELECT date_format(tglwaktu_update,'%Y-%m-%d') AS tanggal, ";
    foreach ($status_collect as $data) {
         $sql .= "COUNT( IF( status = '$data->code', no, NULL) ) AS $data->code, ";
    }
    $sql .= "COUNT( IF( status != '', no, NULL) ) AS total ";
    $sql .= "FROM data_assign LEFT JOIN data_master ON data_assign.data_id=data_master.id ";
    $sql .= "WHERE tglwaktu_update != '0000-00-00 00:00:00' AND id_period='$id_period' AND cek='0' ";
    $sql .= "GROUP BY status,tanggal ";
    $sql .= "ORDER BY tanggal";
    $sql = rtrim($sql, ', ');
 

В SQL-запросе выше DataAssign в этом случае я изменил его на DataResult

И я пытался создать подобные наборы запросов, но это не дает желаемого результата.

 1.  dataResult = DataResult.objects.filter(master__in=dataMaster.values('id'),cek = 0).values('status_id','tgl_waktu_update__date').annotate(count = Count('tgl_waktu_update__date')).annotate(total = Count('status_id')).order_by('-tgl_waktu_update__date')
data_resultAll = DataResult.objects.filter(master__in=dataMaster.values('id')).filter(cek = 0)

2.  
status_id = StatusCollect.objects.all()
for i in status_id:
    stats_id = i.id
    result = DataResult.objects.filter(master__in=dataMaster.values('id') , cek = 0
                                       ).values('status_id','tgl_waktu_update__date'
                                                ).annotate(count = Coalesce(Count('status', filter= ~Q(status = stats_id)),V(0)),
                                                                         total = Count('status_id')
                                                           ).order_by('-tgl_waktu_update__date')
 

Пожалуйста, помогите мне, как это решить, я застрял на 3 дня.