from django.http import response
from API.views import jalan
from django.contrib.auth.models import User, Group
from django.shortcuts import render, redirect
# from django.views.generic import ListView, DetailView,  DeleteView, UpdateView, CreateView
from django.views import View, generic
from django.contrib.auth.decorators import login_required, user_passes_test
from django.utils.decorators import method_decorator
from django.db import connection as conn
# from edit import *

# Create your views here.
class Dashboard(generic.TemplateView):
    template_name = "layout/layout.html"
    # @method_decorator(login_required(login_url='/login/'))
    def get(self, request):
        print(request.user.id)
        print(Group.objects.all())
        # print(Group.objects.get(user=request.user))
        with conn.cursor() as all_:
            all_.execute("SELECT * from global_oku")
            all = all_.fetchall()
        
        with conn.cursor() as kel:
            kel.execute("SELECT json_build_object('type', 'Feature', 'administrasi', (kode_desa, desa, kecamatan, kab_kota, provinsi, jumlah_pen, jumlah_kk, luas_desa, gambar),'penduduk', (pria, wanita, belum_kawin, kawin, cerai_hidup, cerai_mati, wajib_ktp, islam, kristen, khatolik, hindu, budha, konghucu, kepercayaan_lain, u0, u5, u10, u15, u20, u25, u30, u35, u40, u45, u50, u55, u60, u65, u70, u75),'pekerjaan_pendidikan', (tidak_sekolah, belum_tamat, tamat_sd, sltp, slta, diploma_i, diploma_ii, diploma_iv, strata_ii, strata_iii, tidak_bekerja, aparatur_pemerintah, tenaga_pendidik, wiraswasta, pertanian, tenaga_kesehatan, pensiunan, pegawai, tentara, kepolisian, pedagang, petani, peternak, nelayan, karyawan, buruh, pembantu, tukang, pendeta, pastor, ustadz, dosen, guru, pilot, pengacara, notaris, arsitek, akuntan, konsultan, dokter, bidan, perawat, psikiater, sopir, lainnya), 'poi',(jml_puskesma, jml_sekolah, jml_sarib),'geometry', ST_AsGeoJSON(geom :: geometry) :: json) from geo_data_kelurahan")
            kel_res_ = kel.fetchall()
        
        with conn.cursor() as kec:
            kec.execute("SELECT kecamatan, json_build_object('type', 'Feature', 'administrasi', (id, kecamatan),'geometry', ST_AsGeoJSON(geom :: geometry) :: json) from geo_data_kecamatan")
            kec_res_ = kec.fetchall()
        
        # with conn.cursor() as knt:
        #     knt.execute("SELECT json_build_object('type', 'Feature', 'administrasi', (a.namobj, a.remark),'geometry', ST_AsGeoJSON(a.geom :: geometry) :: json) from geo_kantor_administrasi a join geo_prov_kab b on st_intersects(a.geom,b.geom)")
        #     knt_res = knt.fetchall()
        
        kec_res = []
        for i in kec_res_:
            kec_res.append(i[1])
        
        kel_res = []
        for i in kel_res_:
            kel_res.append(i[0])
        
        # kntl = []
        # for i in knt_res:
        #     kntl.append(i[0])
        
        with conn.cursor() as kantor_administrasi:
            kantor_administrasi.execute("""SELECT
                                            json_build_object (
                                                'type',
                                                'Feature',
                                                'administrasi',
                                                (
                                                    A.namobj,
                                                    A.remark,
                                                    A.alamat,
                                                    A.luas_bangunan,
                                                    A.luas_tanah,
                                                    A.satuan_luas_bangunan,
                                                    A.satuan_luas_tanah,
                                                    A.gambar,
                                                    A.tinggi_bangunan,
                                                    A.perangkat,
                                                    A.tipe_gambar,
                                                    b.kabupaten,
                                                    c.kecamatan,
                                                    d.desa,
                                                    a.id
                                                ),
                                                'geometry',
                                                ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                            ) 
                                        FROM
                                            geo_kantor_administrasi A 
                                            JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                            JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                            join geo_data_kelurahan d on st_intersects(A.geom, d.geom)""")
            kantor_administrasi_res = kantor_administrasi.fetchall()
            
        kantor_administrasi_result = []
        for i in kantor_administrasi_res:
            kantor_administrasi_result.append(i[0])
            
        with conn.cursor() as layanan_kesehatan:
            layanan_kesehatan.execute("""SELECT
                                            json_build_object (
                                                'type',
                                                'Feature',
                                                'administrasi',
                                                (
                                                    A.namobj,
                                                    A.remark,
                                                    A.alamat,
                                                    A.luas_bangunan,
                                                    A.luas_tanah,
                                                    A.satuan_luas_bangunan,
                                                    A.satuan_luas_tanah,
                                                    A.gambar,
                                                    A.tinggi_bangunan,
                                                    A.perangkat,
                                                    A.tipe_gambar,
                                                    b.kabupaten,
                                                    c.kecamatan,
                                                    d.desa,
                                                    a.id
                                                ),
                                                'geometry',
                                                ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                            ) 
                                        FROM
                                            geo_layanan_kesehatan A 
                                            JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                            JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                            join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                            """)
            layanan_kesehatan_res = layanan_kesehatan.fetchall()
        
        layanan_kesehatan_result = []
        for i in layanan_kesehatan_res:
            layanan_kesehatan_result.append(i[0])
            
            
        with conn.cursor() as puskesmas:
            puskesmas.execute("""SELECT
                                    json_build_object (
                                        'type',
                                        'Feature',
                                        'administrasi',
                                        (
                                            A.namobj,
                                            A.remark,
                                            A.alamat,
                                            A.luas_bangunan,
                                            A.luas_tanah,
                                            A.satuan_luas_bangunan,
                                            A.satuan_luas_tanah,
                                            A.gambar,
                                            A.tinggi_bangunan,
                                            A.perangkat,
                                            A.tipe_gambar,
                                            b.kabupaten,
                                            c.kecamatan,
                                            d.desa,
                                            a.id
                                        ),
                                        'geometry',
                                        ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                    ) 
                                FROM
                                    geo_puskesmas A 
                                    JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                    JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                    join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                    """)
            puskesmas_res = puskesmas.fetchall()
        
        puskesmas_result = []
        for i in puskesmas_res:
            puskesmas_result.append(i[0])
            
        with conn.cursor() as rumah_sakit:
            rumah_sakit.execute("""SELECT
                                        json_build_object (
                                            'type',
                                            'Feature',
                                            'administrasi',
                                            (
                                                A.namobj,
                                                A.remark,
                                                A.alamat,
                                                A.luas_bangunan,
                                                A.luas_tanah,
                                                A.satuan_luas_bangunan,
                                                A.satuan_luas_tanah,
                                                A.gambar,
                                                A.tinggi_bangunan,
                                                A.perangkat,
                                                A.tipe_gambar,
                                                b.kabupaten,
                                                c.kecamatan,
                                                d.desa,
                                                a.id
                                            ),
                                            'geometry',
                                            ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                        ) 
                                    FROM
                                        geo_rumah_sakit A 
                                        JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                        JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                        join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                        """)
            rumah_sakit_res = rumah_sakit.fetchall()
        
        rumah_sakit_result = []
        for i in rumah_sakit_res:
            rumah_sakit_result.append(i[0])
            
        with conn.cursor() as sarana_ibadah:
            sarana_ibadah.execute("""SELECT
                                        json_build_object (
                                            'type',
                                            'Feature',
                                            'administrasi',
                                            (
                                                A.namobj,
                                                A.remark,
                                                A.alamat,
                                                A.luas_bangunan,
                                                A.luas_tanah,
                                                A.satuan_luas_bangunan,
                                                A.satuan_luas_tanah,
                                                A.gambar,
                                                A.tinggi_bangunan,
                                                A.perangkat,
                                                A.tipe_gambar,
                                                b.kabupaten,
                                                c.kecamatan,
                                                d.desa,
                                                a.id
                                            ),
                                            'geometry',
                                            ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                        ) 
                                    FROM
                                        geo_sarana_ibadah A 
                                        JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                        JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                        join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                        """)
            sarana_ibadah_res = sarana_ibadah.fetchall()
        
        sarana_ibadah_result = []
        for i in sarana_ibadah_res:
            if i[0]["administrasi"]['f1'] == None:
                i[0]["administrasi"]['f1'] = "None"
            sarana_ibadah_result.append(i[0])
            
        with conn.cursor() as sarana_pendidikan:
            sarana_pendidikan.execute("""SELECT
                                            json_build_object (
                                                'type',
                                                'Feature',
                                                'administrasi',
                                                (
                                                    A.namobj,
                                                    A.remark,
                                                    A.alamat,
                                                    A.luas_bangunan,
                                                    A.luas_tanah,
                                                    A.satuan_luas_bangunan,
                                                    A.satuan_luas_tanah,
                                                    A.gambar,
                                                    A.tinggi_bangunan,
                                                    A.perangkat,
                                                    A.tipe_gambar,
                                                    b.kabupaten,
                                                    c.kecamatan,
                                                    d.desa,
                                                    a.id
                                                ),
                                                'geometry',
                                                ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                            ) 
                                        FROM
                                            geo_sarana_pendidikan A 
                                            JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                            JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                            join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                            """)
            sarana_pendidikan_res = sarana_pendidikan.fetchall()
        
        sarana_pendidikan_result = []
        for i in sarana_pendidikan_res:
            if i[0]["administrasi"]['f1'] == None:
                i[0]["administrasi"]['f1'] = "None"
            sarana_pendidikan_result.append(i[0])
            
        with conn.cursor() as spbu:
            spbu.execute("""SELECT
                                json_build_object (
                                    'type',
                                    'Feature',
                                    'administrasi',
                                    (
                                        A.namobj,
                                        A.remark,
                                        A.alamat,
                                        A.luas_bangunan,
                                        A.luas_tanah,
                                        A.satuan_luas_bangunan,
                                        A.satuan_luas_tanah,
                                        A.gambar,
                                        A.tinggi_bangunan,
                                        A.perangkat,
                                        A.tipe_gambar,
                                        b.kabupaten,
                                        c.kecamatan,
                                        d.desa,
                                        a.id
                                    ),
                                    'geometry',
                                    ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                ) 
                            FROM
                                geo_spbu A 
                                JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                """)
            spbu_res = spbu.fetchall()
        
        spbu_result = []
        for i in spbu_res:
            spbu_result.append(i[0])
            
        with conn.cursor() as stasiun:
            stasiun.execute("""SELECT
                                    json_build_object (
                                        'type',
                                        'Feature',
                                        'administrasi',
                                        (
                                            A.namobj,
                                            A.remark,
                                            A.alamat,
                                            A.luas_bangunan,
                                            A.luas_tanah,
                                            A.satuan_luas_bangunan,
                                            A.satuan_luas_tanah,
                                            A.gambar,
                                            A.tinggi_bangunan,
                                            A.perangkat,
                                            A.tipe_gambar,
                                            b.kabupaten,
                                            c.kecamatan,
                                            d.desa,
                                            a.id
                                        ),
                                        'geometry',
                                        ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                    ) 
                                FROM
                                    geo_stasiun A 
                                    JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                    JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                    join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                    """)
            stasiun_res = stasiun.fetchall()
        
        stasiun_result = []
        for i in stasiun_res:
            stasiun_result.append(i[0])
            
        with conn.cursor() as terminal:
            terminal.execute("""SELECT
                                    json_build_object (
                                        'type',
                                        'Feature',
                                        'administrasi',
                                        (
                                            A.namobj,
                                            A.remark,
                                            A.alamat,
                                            A.luas_bangunan,
                                            A.luas_tanah,
                                            A.satuan_luas_bangunan,
                                            A.satuan_luas_tanah,
                                            A.gambar,
                                            A.tinggi_bangunan,
                                            A.perangkat,
                                            A.tipe_gambar,
                                            b.kabupaten,
                                            c.kecamatan,
                                            d.desa,
                                            a.luas,
                                            a.id
                                        ),
                                        'geometry',
                                        ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                    ) 
                                FROM
                                    geo_terminal_bus A 
                                    JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                    JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                    join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                    """)
            terminal_res = terminal.fetchall()
        
        terminal_result = []
        for i in terminal_res:
            terminal_result.append(i[0])
            
        with conn.cursor() as cagar_budaya:
            cagar_budaya.execute("""SELECT
                                        json_build_object (
                                            'type',
                                            'Feature',
                                            'administrasi',
                                            (
                                                A.namobj,
                                                A.remark,
                                                A.alamat,
                                                A.gambar,
                                                A.perangkat,
                                                A.tipe_gambar,
                                                b.kabupaten,
                                                c.kecamatan,
                                                d.desa,
                                                a.luas,
                                                a.id
                                            ),
                                            'geometry',
                                            ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                        ) 
                                    FROM
                                        geo_cagar_budaya A 
                                        JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                        JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                        join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                        """)
            cagar_budaya_res = cagar_budaya.fetchall()
        
        cagar_budaya_result = []
        for i in cagar_budaya_res:
            cagar_budaya_result.append(i[0])
            
        with conn.cursor() as gardu_listrik:
            gardu_listrik.execute("""SELECT
                                        json_build_object (
                                            'type',
                                            'Feature',
                                            'administrasi',
                                            (
                                                A.namobj,
                                                A.remark,
                                                A.alamat,
                                                A.luas_bangunan,
                                                A.luas_tanah,
                                                A.satuan_luas_bangunan,
                                                A.satuan_luas_tanah,
                                                A.gambar,
                                                A.tinggi_bangunan,
                                                A.perangkat,
                                                A.tipe_gambar,
                                                b.kabupaten,
                                                c.kecamatan,
                                                d.desa,
                                                a.id
                                            ),
                                            'geometry',
                                            ST_AsGeoJSON ( A.geom :: geometry ) :: json 
                                        ) 
                                    FROM
                                        geo_gardu_listrik A 
                                        JOIN geo_prov_kab b ON st_intersects ( A.geom, b.geom )
                                        JOIN geo_data_kecamatan c ON st_intersects(A.geom, c.geom)
                                        join geo_data_kelurahan d on st_intersects(A.geom, d.geom)
                                        """)
            gardu_listrik_res = gardu_listrik.fetchall()
        
        gardu_listrik_result = []
        for i in gardu_listrik_res:
            gardu_listrik_result.append(i[0])
        
        

            
        context = {
        'title':'Dashboard',
        'kelurahan':kel_res,
        'kecamatan':kec_res,
        'kantor_administrasi':kantor_administrasi_result,
        'layanan_kesehatan':layanan_kesehatan_result,
        'puskesmas':puskesmas_result,
        'rumah_sakit':rumah_sakit_result,
        'sarana_ibadah':sarana_ibadah_result,
        'sarana_pendidikan':sarana_pendidikan_result,
        'spbu':spbu_result,
        'stasiun':stasiun_result,
        'terminal':terminal_result,
        'cagar_budaya':cagar_budaya_result,
        'gardu_listrik':gardu_listrik_result,
        'global':all[0]
        # 'kntl':kntl
        }
        return render (request, self.template_name, context)
    

from django import template
from django.contrib.auth.models import Group
from rest_framework.decorators import api_view, renderer_classes
from rest_framework.response import Response


register = template.Library()

@register.filter(name='has_group')
def has_group(user, group_name):
    group = Group.objects.get(name=group_name)
    return True if group in user.groups.all() else False


def perbaikanrenovasi(request):
    with conn.cursor() as data_per:
        # data_per.execute("""
        #                  SELECT
        #                     gl.NAME,
        #                     tp.name,
        #                     tp.type,
        #                     st_length ( gj.geom :: geography ),
        #                     tp.tgl_pembuatan,
        #                     tp.tgl_perbaikan,
        #                     tp.anggaran,
        #                     tp.proposal,
        #                     tp.pj,
        #                     tp.pj_lapangan,
        #                     tp.kontraktor,
        #                     ST_AsGeoJSON ( tp.geom :: geometry ) :: json,
        #                     tp.pjg_perbaikan,
        #                     tp.lbr_perbaikan,
        #                     tp.id_object
        #                 FROM
        #                     tbl_perbaikan tp
        #                     JOIN geo_label gl ON gl.ID = tp.type_id
        #                     JOIN geo_jalan gj ON gj.ID = tp.id_object
        #                  """)
        data_per.execute("""
                         SELECT
                            gl.NAME,
                            tp.id_object,
                            tp.NAME,
                            tp.TYPE,
                            tp.tgl_perbaikan,
                            tp.anggaran,
                            tp.tgl_selesai
                        FROM
                            ( SELECT tbl_perbaikan.*, ROW_NUMBER ( ) OVER ( PARTITION BY id_object ORDER BY tgl_selesai DESC ) AS rn FROM tbl_perbaikan ) tp
                            JOIN geo_label gl ON gl.ID = tp.type_id 
                        WHERE
                            rn = 1
                         """)
        res_perbaikan = data_per.fetchall()
    
    res_all = []
    for j in res_perbaikan:
        print(j[0])
        # if j[0] == 'jalan':
        data_all = {
            'name_perbaikan':j[0],
            'id_object':j[1],
            'name':j[2],
            'type':j[3],
            'tgl_perbaikan':j[4],
            'anggaran':j[5],
            'tgl_selesai':j[6],
        }
        res_all.append(data_all)
            
    context = {'res_all':res_all}
    return render(request, 'maps/perbaikan.html', context)

@api_view(('POST',))
def loadDetail(request):
    id_object = request.POST.get('id')
    with conn.cursor() as data_per:
        data_per.execute("""
                         SELECT
                            gl.NAME,
                            tp.name,
                            gj.remark,
                            st_length ( gj.geom :: geography ),
                            tp.tgl_pembuatan,
                            tp.tgl_perbaikan,
                            tp.tgl_selesai,
                            tp.anggaran,
                            tp.pj,
                            tp.pj_lapangan,
                            tp.kontraktor,
                            ST_AsGeoJSON ( tp.geom :: geometry ) :: json,
                            tp.pjg_perbaikan,
                            tp.lbr_perbaikan,
                            tp.id_object,
                            tp.id,
                            tp.proposal,
                            tp.bukti_selesai
                        FROM
                            tbl_perbaikan tp
                            JOIN geo_label gl ON gl.ID = tp.type_id
                            JOIN geo_jalan gj ON gj.ID = tp.id_object
                        where tp.id_object = %s"""% id_object)
        res_perbaikan = data_per.fetchall()
        
    res_all = []
    for j in res_perbaikan:
        data_all = {
                'name':j[1],
                'type':j[2],
                'panjang':j[3],
                'tgl_pembuatan':j[4],
                'tgl_perbaikan':j[5],
                'tgl_selesai':j[6],
                'anggaran':j[7],
                'pj':j[8],
                'pj_lapangan':j[9],
                'kontraktor':j[10],
                'geometry':j[11],
                'pjg_jln_reno':j[12],
                'lbr_jln_reno':j[13],
                'id_object':j[14],
                'id':j[15],
                'proposal':j[16],
                'bukti_selesai':j[17]
            }
        res_all.append(data_all)

    respon =res_all
    return Response(respon)

@api_view(('GET',))
def inpuperbaikan(request):
    id = request.GET.get('id_jalan')
    with conn.cursor() as data_per:
        data_per.execute("""
                         SELECT
                            namrjl,
                            remark,
                            st_length ( geom :: geography ),
                            json_build_object (
                                        'type', 'Feature',
                                        'geometry', ST_AsGeoJSON ( geom :: geometry ) :: json ) ,
                            id,
                            type_id
                        FROM
                            geo_jalan
                        where id = %s
                         """% id)
        res_data = data_per.fetchall()
    
    respon = res_data[0]
    return Response(respon)

from django.core.files.storage import FileSystemStorage
import random
import re
import json
@api_view(('POST',))
def saveperbaikan(request):
    type_id         = request.POST.get("type_id")
    id_object       = request.POST.get("object_id")
    name            = request.POST.get("name")
    type            = request.POST.get("type")
    tgl_pembuatan   = request.POST.get("tgl_pembuatan")
    tgl_perbaikan   = request.POST.get("tgl_perbaikan")
    tgl_disetujui   = request.POST.get("tgl_disetujui")
    anggaran        = request.POST.get("anggaran")
    
    
    pj              = request.POST.get("pj")
    pj_lapangan     = request.POST.get("pj_lapangan")
    kontraktor      = request.POST.get("kontraktor")
    pjg_perbaikan   = request.POST.get("pjg_perbaikan")
    lbr_perbaikan   = request.POST.get("lbr_perbaikan")
    jenis_perbaikan = request.POST.get("jenis_perbaikan")
    luas            = request.POST.get("luas")
    coor            = request.POST.get("coord")
    tgl_selesai     = request.POST.get("tgl_selesai")
    
    proposal        = request.FILES.get("proposal")
    bukti_selesai   = request.FILES.get("bukti_selesai")
    print(coor, proposal)
    
    
    
    fss_p = FileSystemStorage(location='media/proposal/')
    fss_b = FileSystemStorage(location='media/bukti_penyelesaian/')
    
    coord = json.loads(str(coor))
    
    pjg_perbaikan = re.sub('[^0-9.]+', '', str(pjg_perbaikan))
    lbr_perbaikan = re.sub('[^0-9.]+', '', str(lbr_perbaikan))
    luas = re.sub('[^0-9.]+', '', str(luas))
    
    if coord['features'][0]['geometry']['type'] == 'LineString':
        print("oke")
        for co in coord['features']:
            ll = str(co["geometry"]['coordinates'])
            ll += ll
        ll = ll.replace(", "," ")
        ll = ll.replace("]][[","),(")
        ll = ll.replace("] [",", ")
        ll = ll.replace("]]",")")
        ll = ll.replace("[[","(")

        coordinat = f'MULTILINESTRING({ll})'
    if coord['features'][0]['geometry']['type'] == 'Polygon':
        print('tidakoke')
        
    try:
        format = (proposal.name).split(".")
        num = random.randrange(1, 10000000000000)
        nproposal = f"{name}_{num}.{format[-1:][0]}"
        nproposal = nproposal.replace(" ","-")
        filename_p = fss_p.save(nproposal, proposal)
        url_p = fss_p.url(filename_p)

        nbukti = f"{name}_{num}.{format[-1:][0]}"
        nbukti = nbukti.replace(" ","-")
        filename_b = fss_b.save(nbukti, bukti_selesai)
        url_b = fss_b.url(filename_b)
        
        with conn.cursor() as data_per:
            data_per.execute("""
                        INSERT INTO tbl_perbaikan (
                            type_id,
                            id_object,
                            name,
                            type,
                            tgl_selesai,
                            tgl_perbaikan,
                            tgl_disetujui,
                            anggaran,
                            proposal,
                            bukti_selesai,
                            pj,
                            pj_lapangan,
                            kontraktor,
                            pjg_perbaikan,
                            lbr_perbaikan,
                            jenis_perbaikan,
                            geom
                        )
                        VALUES
                            (
                                %s,
                                %s,
                                '%s',
                                '%s',
                                '%s',
                                '%s',
                                '%s',
                                %s,
                                '%s',
                                '%s',
                                '%s',
                                '%s',
                                '%s',
                                %s,
                                %s,
                                '%s',
                                ST_GeomFromText('%s')
                            )
                            """% (
                                int(type_id),
                                int(id_object),
                                name,
                                type,
                                tgl_selesai,
                                tgl_perbaikan,
                                tgl_disetujui,
                                int(anggaran),
                                nproposal,
                                nbukti,
                                pj,
                                pj_lapangan,
                                kontraktor,
                                float(pjg_perbaikan),
                                float(lbr_perbaikan),
                                jenis_perbaikan,
                                coordinat))
        conn.commit()
        data = "Alhamdulilah"
        info = "Data Telah Berhasil di Perbaharui"
        code = 0

    except:
        data = "Pastikan Semua Data Terisi Termasuk Proposal dan Bukti Selesai"
        info = "Oops"
        code = 1
    respon = {"data":data,"info":info,"code":code}
    return Response(respon)

@api_view(('GET',))
def loadbyiddetail(request):
    id = request.GET.get('id')
    id_jl = request.GET.get('id_jl')
    with conn.cursor() as detail_maps:
        detail_maps.execute("""
                            SELECT
                                json_build_object (
                                        'type', 'Feature',
                                        'geometry', ST_AsGeoJSON ( a.geom :: geometry ) :: json ),
                                json_build_object (
                                        'type', 'Feature',
                                        'geometry', ST_AsGeoJSON ( b.geom :: geometry ) :: json )
                            from 
                                tbl_perbaikan a, geo_jalan b
                            where
                                a.id = %s and b.id = %s
                            """% (id, id_jl))
        detail_ = detail_maps.fetchone()
    print(detail_)
    respon=detail_
    return Response(respon)

@api_view(('GET',))
def getjalanperbaikan(request):
    id = request.GET.get('id')

    with conn.cursor() as detail_maps:
        detail_maps.execute("""
                            SELECT
                                json_build_object (
                                        'type', 'Feature',
                                        'geometry', ST_AsGeoJSON ( a.geom :: geometry ) :: json )
                            from 
                                tbl_perbaikan a
                            where
                                a.id = %s
                            """% id)
        detail_ = detail_maps.fetchone()
    print(detail_)
    respon=detail_
    return Response(respon)

@api_view(('GET',))
def getdetailperbaikan(request):
    id_object = request.GET.get('id')
    with conn.cursor() as data_per:
        data_per.execute("""
                         SELECT
                            gl.NAME,
                            tp.name,
                            gj.remark,
                            st_length ( gj.geom :: geography ),
                            tp.tgl_pembuatan,
                            tp.tgl_perbaikan,
                            tp.tgl_selesai,
                            tp.anggaran,
                            tp.pj,
                            tp.pj_lapangan,
                            tp.kontraktor,
                            ST_AsGeoJSON ( tp.geom :: geometry ) :: json,
                            tp.pjg_perbaikan,
                            tp.lbr_perbaikan,
                            tp.id_object,
                            tp.id,
                            tp.proposal,
                            tp.bukti_selesai
                        FROM
                            tbl_perbaikan tp
                            JOIN geo_label gl ON gl.ID = tp.type_id
                            JOIN geo_jalan gj ON gj.ID = tp.id_object
                        where tp.id = %s"""% id_object)
        res_perbaikan = data_per.fetchall()
                
    res_all = []
    for j in res_perbaikan:
        data_all = {
                'name':j[1],
                'type':j[2],
                'panjang':j[3],
                'tgl_pembuatan':j[4],
                'tgl_perbaikan':j[5],
                'tgl_selesai':j[6],
                'anggaran':j[7],
                'pj':j[8],
                'pj_lapangan':j[9],
                'kontraktor':j[10],
                'geometry':j[11],
                'pjg_jln_reno':j[12],
                'lbr_jln_reno':j[13],
                'id_object':j[14],
                'id':j[15],
                'proposal':j[16],
                'bukti_selesai':j[17]
            }
        res_all.append(data_all)

    respon =res_all
    return Response(respon)

@api_view(('GET',))
def editkelurahan(request):
    kode_dagri = request.GET.get('kode_dagri')
    
    with conn.cursor() as edit_poly:
        edit_poly.execute("SELECT json_build_object('type', 'Feature', 'administrasi', json_build_array(kode_desa, desa, kecamatan, kab_kota, provinsi, jumlah_pen, jumlah_kk, luas_desa),'penduduk', json_build_array(pria, wanita, belum_kawin, kawin, cerai_hidup, cerai_mati, wajib_ktp, islam, kristen, khatolik, hindu, budha, konghucu, kepercayaan_lain, u0, u5, u10, u15, u20, u25, u30, u35, u40, u45, u50, u55, u60, u65, u70, u75),'pekerjaan_pendidikan', json_build_array(tidak_sekolah, belum_tamat, tamat_sd, sltp, slta, diploma_i, diploma_ii, diploma_iv, strata_ii, strata_iii, tidak_bekerja, aparatur_pemerintah, tenaga_pendidik, wiraswasta, pertanian, tenaga_kesehatan, pensiunan, pegawai, tentara, kepolisian, pedagang, petani, peternak, nelayan, karyawan, buruh, pembantu, tukang, pendeta, pastor, ustadz, dosen, guru, pilot, pengacara, notaris, arsitek, akuntan, konsultan, dokter, bidan, perawat, psikiater, sopir, lainnya), 'poi',json_build_array(jml_puskesma, jml_sekolah, jml_sarib),'geometry', ST_AsGeoJSON(geom :: geometry) :: json) from geo_data_kelurahan where kode_desa = '%s'"% kode_dagri)
        res_edit_poly = edit_poly.fetchall()
        
    print(res_edit_poly)
    
    la = []
    lo = []
    for i in res_edit_poly[0][0]['geometry']['coordinates'][0][0]:
        la.append(i[0])
        lo.append(i[1])
        
    avg_la = sum(la)/len(la)
    avg_lo = sum(lo)/len(lo)
    
    desa = res_edit_poly[0][0]['administrasi'][1]
    kec = res_edit_poly[0][0]['administrasi'][2]
    kab = res_edit_poly[0][0]['administrasi'][3]
    prov = res_edit_poly[0][0]['administrasi'][4]
    context = {'desa':desa, 'kec':kec, 'kab':kab, 'prov':prov,'result_poly': res_edit_poly[0][0], 'avg_la':avg_la, 'avg_lo':avg_lo}
    return Response(context)