class PreviewHiscarResultado(models.Model):
    rules= models.CharField(max_length=300)
    cargo= models.CharField(max_length=15)
    periodo= models.FloatField()
    basico_new= models.FloatField()
    basico= models.FloatField()
    antiguedad_new= models.FloatField()
    antiguedad= models.FloatField()
    sumafija1_new= models.FloatField()
    sumafija1= models.FloatField()
    sumafija2_new= models.FloatField()
    sumafija2= models.FloatField()
    sumafija3_new= models.FloatField()
    sumafija3= models.FloatField()
    suplemento1_new= models.FloatField()
    suplemento1= models.FloatField()
    suplemento2_new= models.FloatField()
    suplemento2= models.FloatField()
    class Meta:
        managed=False

    def serialize(previewHiscar):

        dic={
                'periodo': int(previewHiscar.periodo),
                'cargo': previewHiscar.cargo,
                'basico': format(float(0 if not previewHiscar.basico else previewHiscar.basico),'.2f'),
                'basico_new': format(float(0 if not previewHiscar.basico_new else previewHiscar.basico_new),'.2f'),
                'antiguedad': format(float(0 if not previewHiscar.antiguedad else previewHiscar.antiguedad),'.2f'),
                'antiguedad_new': format(float(0 if not previewHiscar.antiguedad_new else previewHiscar.antiguedad_new),'.2f'),
                'suplemento1': format(float(0 if not previewHiscar.suplemento1 else previewHiscar.suplemento1),'.2f'),
                'suplemento1_new': format(float(0 if not previewHiscar.suplemento1_new else previewHiscar.suplemento1_new),'.2f'),
                'suplemento2': format(float(0 if not previewHiscar.suplemento2 else previewHiscar.suplemento2),'.2f'),
                'suplemento2_new': format(float(0 if not previewHiscar.suplemento2_new else previewHiscar.suplemento2_new),'.2f'),
                'sumafija1': format(float(0 if not previewHiscar.sumafija1 else previewHiscar.sumafija1),'.2f'),
                'sumafija1_new': format(float(0 if not previewHiscar.sumafija1_new else previewHiscar.sumafija1_new),'.2f'),
                'sumafija2': format(float(0 if not previewHiscar.sumafija2 else previewHiscar.sumafija2),'.2f'),
                'sumafija2_new': format(float(0 if not previewHiscar.sumafija2_new else previewHiscar.sumafija2_new),'.2f'),
                'sumafija3': format(float(0 if not previewHiscar.sumafija3 else previewHiscar.sumafija3),'.2f'),
                'sumafija3_new': format(float(0 if not previewHiscar.sumafija3_new else previewHiscar.sumafija3_new),'.2f'),
                'rules': previewHiscar.rules
        }
        return json.dumps(dic)

    def devolverHiscarPeriodo(movimiento):
        c=connection.cursor()
        query=        """
        DECLARE @cant AS INT
        SELECT @cant = COUNT(1) FROM previews_hiscar_temporal WHERE reparticion_id = %s and movimiento_id= %s

        SELECT top(@cant) t.cargo,
        t.periodo,
        t.basico as basico_new,
        ISNULL(ISNULL(h.basico,u.basico),0) basico_hiscar,
        t.antiguedad as antiguedad_new,
        ISNULL(ISNULL(h.antiguedad,u.antiguedad),0) antiguedad_hiscar,
        t.sumafija1 as_sumafija1_new,
        ISNULL(ISNULL(h.sumafija1,u.sumafija1),0) sumafija1_hiscar,
        t.sumafija2 as sumafija2_new,
        ISNULL(ISNULL(h.sumafija2,u.sumafija2),0) sumafija2_hiscar,
        t.sumafija3 as sumafija3_new,
        ISNULL(ISNULL(h.sumafija3,u.sumafija3),0) sumafija3_hiscar,
        t.suplemento1 as suplemento1_new,
        ISNULL(ISNULL(h.suplemento1,u.suplemento1),0) suplemento1_hiscar,
        t.suplemento2 as suplemento2_new,
        ISNULL(ISNULL(h.suplemento2,u.suplemento2),0) suplemento2_hiscar,
        t.rules

        FROM previews_hiscar_temporal t
        LEFT JOIN hiscars h on t.cargo = h.cargo and t.periodo = h.periodo
        LEFT JOIN hiscars u on t.cargo = u.cargo and u.periodo = (SELECT MAX(periodo) FROM hiscars)
        WHERE reparticion_id = %s and movimiento_id= %s and proyecto_id = %s"""
        c.execute(query % (movimiento.reparticion.id, movimiento.id, movimiento.reparticion.id, movimiento.id,movimiento.proyecto.id))
        rows=c.fetchall()
        c.close()
        connection.close()

        resultado=[]
        i=1
        for hiscar in rows:
            resultado.append(
                PreviewHiscarResultado(id=i,
                    basico_new=hiscar[2],basico=hiscar[3],antiguedad_new=hiscar[4],antiguedad=hiscar[5],
                    sumafija1_new=hiscar[6],sumafija1=hiscar[7],sumafija2_new=hiscar[8],
                    sumafija2=hiscar[9],sumafija3_new=hiscar[10],sumafija3=hiscar[11],
                    suplemento1_new=hiscar[12],suplemento1=hiscar[13],
                    suplemento2_new=hiscar[14],suplemento2=hiscar[15],cargo=hiscar[0],periodo=hiscar[1],rules=hiscar[16]
                )
            )
            i+=1
        c.close()
        return resultado