Nurdin27

Bisa Model

Jun 10th, 2021
486
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import datetime
  2. import uuid
  3.  
  4. from _api import db
  5. from sqlalchemy import and_, sql
  6.  
  7.  
  8. def timeconvert(str1):
  9.     if str1[-2:] == "AM" and str1[:2] == "12":
  10.         return "00" + str1[2:-2]
  11.     elif str1[-2:] == "AM":
  12.         return str1[:-2]
  13.     elif str1[-2:] == "PM" and str1[:2] == "12":
  14.         return str1[:-2]
  15.     else:
  16.         return str(int(str1[:2]) + 12) + str1[2:8]
  17.  
  18.  
  19. class Gender(db.Model):
  20.     __tablename__ = '_gender'
  21.  
  22.     id = db.Column(db.Integer, primary_key=True)
  23.     gender_name = db.Column(db.String)
  24.  
  25.     def __init__(
  26.             self,
  27.             _id,
  28.             gender_name
  29.     ):
  30.         self.id = _id
  31.         self.gender_name = gender_name
  32.  
  33.     @classmethod
  34.     def get_all(cls):
  35.         return cls.query.order_by(cls.id).all()
  36.  
  37.  
  38. class Auth(db.Model):
  39.     __tablename__ = '_auth'
  40.     id = db.Column(db.Integer, primary_key=True)
  41.     auth_name = db.Column(db.String)
  42.  
  43.     def __init__(
  44.             self,
  45.             _id,
  46.             auth_name
  47.     ):
  48.         self.id = _id
  49.         self.auth_name = auth_name
  50.  
  51.     @classmethod
  52.     def get_all(cls):
  53.         return cls.query.order_by(cls.id).all()
  54.  
  55.  
  56. class User(db.Model):
  57.     __tablename__ = '_user'
  58.     id = db.Column(db.String, primary_key=True)
  59.     full_name = db.Column(db.String)
  60.     phone = db.Column(db.String)
  61.     birth_date = db.Column(db.DATE)
  62.     gender_id = db.Column(db.Integer, db.ForeignKey(Gender.id))
  63.     gender = db.relationship(Gender, foreign_keys=gender_id)
  64.     address = db.Column(db.String)
  65.     email = db.Column(db.String)
  66.     add_by = db.Column(db.String)
  67.     ktp = db.Column(db.String)
  68.     kta = db.Column(db.String)
  69.  
  70.     def __init__(
  71.             self,
  72.             _id,
  73.             full_name,
  74.             phone,
  75.             birth_date,
  76.             gender_id,
  77.             address,
  78.             email,
  79.             add_by,
  80.             ktp,
  81.             kta
  82.     ):
  83.         self.id = _id
  84.         self.full_name = full_name
  85.         self.phone = phone
  86.         self.birth_date = birth_date
  87.         self.gender_id = gender_id
  88.         self.address = address
  89.         self.email = email
  90.         self.add_by = add_by
  91.         self.ktp = ktp
  92.         self.kta = kta
  93.  
  94.     @classmethod
  95.     def by_id(cls, _id):
  96.         return cls.query.filter_by(id=_id).first()
  97.  
  98.     @classmethod
  99.     def by_phone(cls, _phone):
  100.         return cls.query.filter_by(phone=_phone).first()
  101.  
  102.     @classmethod
  103.     def by_email(cls, _email):
  104.         return cls.query.filter_by(email=_email).first()
  105.  
  106.  
  107. class Account(db.Model):
  108.     __tablename__ = '_account'
  109.     id = db.Column(db.String, primary_key=True)
  110.     nik_nrp = db.Column(db.String)
  111.     hash_password = db.Column(db.String)
  112.     picture = db.Column(db.String)
  113.     auth_id = db.Column(db.Integer, db.ForeignKey(Auth.id))
  114.     auth = db.relationship(Auth, foreign_keys=auth_id)
  115.     user_id = db.Column(db.String, db.ForeignKey(User.id))
  116.     user = db.relationship(User, foreign_keys=user_id)
  117.     active = db.Column(db.Integer)
  118.     add_by = db.Column(db.String)
  119.     notification = db.Column(db.Integer)
  120.  
  121.     def __init__(
  122.             self,
  123.             _id,
  124.             nik_nrp,
  125.             hash_password,
  126.             picture,
  127.             auth_id,
  128.             user_id,
  129.             active,
  130.             add_by,
  131.             notification
  132.     ):
  133.         self.id = _id
  134.         self.nik_nrp = nik_nrp
  135.         self.hash_password = hash_password
  136.         self.picture = picture
  137.         self.auth_id = auth_id
  138.         self.user_id = user_id
  139.         self.active = active
  140.         self.add_by = add_by
  141.         self.notification = notification
  142.  
  143.     @classmethod
  144.     def get_all(cls):
  145.         return cls.query.order_by(cls.id).all()
  146.  
  147.     @classmethod
  148.     def get_satgas(cls):
  149.         return cls.query.filter_by(auth_id=2).all()
  150.  
  151.     @classmethod
  152.     def by_nik(cls, nik_nrp_):
  153.         return cls.query.filter_by(nik_nrp=nik_nrp_).first()
  154.  
  155.     @classmethod
  156.     def by_id(cls, _id):
  157.         return cls.query.filter_by(id=_id).first()
  158.  
  159.     @classmethod
  160.     def by_userid(cls, _userid):
  161.         return cls.query.filter_by(user_id=_userid).first()
  162.  
  163.     def json(self):
  164.         return {
  165.             "id": self.id,
  166.             "nik_nrp": self.nik_nrp,
  167.             "picture": self.picture,
  168.             "auth_id": self.auth_id,
  169.             "auth_name": self.auth.auth_name,
  170.             "iduser": self.user_id,
  171.             "full_name": self.user.full_name,
  172.             "email": self.user.email,
  173.             "phone": self.user.phone,
  174.             "address": self.user.address,
  175.             "birth_date": self.user.birth_date,
  176.             "ktp": self.user.ktp,
  177.         }
  178.  
  179.  
  180. class News(db.Model):
  181.     __tablename__ = '_news'
  182.     id = db.Column(db.String, primary_key=True)
  183.     subject = db.Column(db.String)
  184.     description = db.Column(db.String)
  185.     add_by = db.Column(db.String)
  186.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
  187.  
  188.     def __init__(
  189.             self,
  190.             _id,
  191.             subject,
  192.             description,
  193.             add_by
  194.     ):
  195.         self.id = _id
  196.         self.subject = subject
  197.         self.description = description
  198.         self.add_by = add_by
  199.  
  200.     @classmethod
  201.     def get_all(cls):
  202.         return cls.query.order_by(cls.id).all()
  203.  
  204.     @classmethod
  205.     def by_id(cls, _id):
  206.         return cls.query.filter_by(id=_id).first()
  207.  
  208.     def json(self):
  209.         return {
  210.             "id": self.id,
  211.             "subject": self.subject,
  212.             "description": self.description,
  213.             "add_time": self.add_time.strftime('%Y-%m-%d')
  214.         }
  215.  
  216.  
  217. class Newspict(db.Model):
  218.     __tablename__ = '_news_picture'
  219.     id = db.Column(db.String, primary_key=True)
  220.     news_id = db.Column(db.String, db.ForeignKey(News.id))
  221.     news = db.relationship(News, foreign_keys=news_id)
  222.     picture = db.Column(db.String)
  223.  
  224.     def __init__(
  225.             self,
  226.             _id,
  227.             news_id,
  228.             picture
  229.     ):
  230.         self.id = _id
  231.         self.news_id = news_id
  232.         self.picture = picture
  233.  
  234.     @classmethod
  235.     def get_all(cls):
  236.         return cls.query.order_by(cls.id).all()
  237.  
  238.     @classmethod
  239.     def by_news_id(cls, _news_id):
  240.         return cls.query.filter_by(news_id=_news_id).all()
  241.  
  242.     def json(self):
  243.         return {
  244.             "id": self.id,
  245.             "news_id": self.news_id,
  246.             "picture": self.picture
  247.         }
  248.  
  249.  
  250. class ComplaintCategory(db.Model):
  251.     __tablename__ = '_complaint_category'
  252.  
  253.     id = db.Column(db.Integer, primary_key=True)
  254.     name = db.Column(db.String)
  255.  
  256.     def __init__(
  257.             self,
  258.             name
  259.     ):
  260.         self.name = name
  261.  
  262.     @classmethod
  263.     def get_all(cls):
  264.         return cls.query.order_by(cls.id).all()
  265.  
  266.     def json(self):
  267.         return {
  268.             "id": self.id,
  269.             "name": self.name
  270.         }
  271.  
  272.  
  273. class ComplaintStatus(db.Model):
  274.     __tablename__ = '_complaint_status'
  275.  
  276.     id = db.Column(db.Integer, primary_key=True)
  277.     status = db.Column(db.Integer)
  278.     name = db.Column(db.String)
  279.  
  280.     def __init__(
  281.             self,
  282.             status,
  283.             name
  284.     ):
  285.         self.status = status
  286.         self.name = name
  287.  
  288.  
  289. class Complaint(db.Model):
  290.     __tablename__ = '_complaint'
  291.     id = db.Column(db.String, primary_key=True)
  292.     category_id = db.Column(db.Integer, db.ForeignKey(ComplaintCategory.id))
  293.     category = db.relationship(ComplaintCategory, foreign_keys=category_id)
  294.     subject = db.Column(db.String)
  295.     description = db.Column(db.String)
  296.     status = db.Column(db.Integer, db.ForeignKey(ComplaintStatus.id), default=0)
  297.     statusdata = db.relationship(ComplaintStatus, foreign_keys=status)
  298.     reason = db.Column(db.String)
  299.     add_by = db.Column(db.String)
  300.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
  301.     lat = db.Column(db.Float)
  302.     lon = db.Column(db.Float)
  303.     address = db.Column(db.String)
  304.  
  305.     def __init__(
  306.             self,
  307.             _id,
  308.             category_id,
  309.             subject,
  310.             description,
  311.             status_id,
  312.             reason,
  313.             add_by,
  314.             lat,
  315.             lon,
  316.             address
  317.     ):
  318.         self.id = _id
  319.         self.category_id = category_id
  320.         self.subject = subject
  321.         self.description = description
  322.         self.status_id = status_id
  323.         self.reason = reason
  324.         self.add_by = add_by
  325.         self.lat = lat
  326.         self.lon = lon
  327.         self.address = address
  328.  
  329.     @classmethod
  330.     def get_all(cls):
  331.         return cls.query.order_by(cls.id).all()
  332.  
  333.     @classmethod
  334.     def get_all_auth3(cls, _id):
  335.         return cls.query.filter_by(add_by=_id).all()
  336.  
  337.     @classmethod
  338.     def by_id(cls, _id):
  339.         return cls.query.filter_by(id=_id).first()
  340.  
  341.     def json(self):
  342.         return {
  343.             "id": self.id,
  344.             "category_id": self.category_id,
  345.             "category": self.category.name,
  346.             "subject": self.subject,
  347.             "description": self.description,
  348.             "status": self.status,
  349.             "status_name": self.statusdata.name,
  350.             "reason": self.reason,
  351.             "lat": self.lat,
  352.             "lon": self.lon,
  353.             "address": self.address,
  354.             "add_time": self.add_time.strftime('%Y-%m-%d')
  355.         }
  356.  
  357.  
  358. class Complaintpict(db.Model):
  359.     __tablename__ = '_complaint_picture'
  360.     id = db.Column(db.String, primary_key=True)
  361.     complaint_id = db.Column(db.String, db.ForeignKey(Complaint.id))
  362.     complaint = db.relationship(Complaint, foreign_keys=complaint_id)
  363.     picture = db.Column(db.String)
  364.  
  365.     def __init__(
  366.             self,
  367.             _id,
  368.             complaint_id,
  369.             picture
  370.     ):
  371.         self.id = _id
  372.         self.complaint_id = complaint_id
  373.         self.picture = picture
  374.  
  375.     @classmethod
  376.     def by_complaint_id(cls, _complaint_id):
  377.         return cls.query.filter_by(complaint_id=_complaint_id).all()
  378.  
  379.     def json(self):
  380.         return {
  381.             "id": self.id,
  382.             "picture": self.picture
  383.         }
  384.  
  385.  
  386. class ComplaintHistory(db.Model):
  387.     __tablename__ = '_complaint_history'
  388.     id = db.Column(db.String, primary_key=True)
  389.     complaint_id = db.Column(db.String, db.ForeignKey(Complaint.id))
  390.     complaint = db.relationship(Complaint, foreign_keys=complaint_id)
  391.     status_id = db.Column(db.Integer, db.ForeignKey(ComplaintStatus.id))
  392.     status = db.relationship(ComplaintStatus, foreign_keys=status_id)
  393.     add_by = db.Column(db.String)
  394.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.now())
  395.  
  396.     def __init__(
  397.             self,
  398.             _id,
  399.             complaint_id,
  400.             status_id,
  401.             add_by
  402.     ):
  403.         self.id = _id
  404.         self.complaint_id = complaint_id
  405.         self.status_id = status_id
  406.         self.add_by = add_by
  407.  
  408.     @classmethod
  409.     def by_complaint_id(cls, _complaint_id):
  410.         return cls.query.filter_by(complaint_id=_complaint_id).order_by(cls.add_time.asc()).all()
  411.  
  412.     def json(self):
  413.         if self.status_id == 0:
  414.             _status_name = 'Menunggu Respon'
  415.         elif self.status_id == 1:
  416.             _status_name = 'Pengaduan Sedang Di Proses'
  417.         elif self.status_id == 2:
  418.             _status_name = 'Pengaduan Ditolak'
  419.         else:
  420.             _status_name = 'Pengaduan Telah Selesai Ditindak Lanjuti'
  421.         return {
  422.             "add_time": self.add_time,
  423.             "status_name": _status_name
  424.         }
  425.  
  426.  
  427. class Activity(db.Model):
  428.     __tablename__ = '_activity'
  429.     id = db.Column(db.String, primary_key=True)
  430.     subject = db.Column(db.String)
  431.     description = db.Column(db.String)
  432.     add_by = db.Column(db.String)
  433.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
  434.  
  435.     def __init__(
  436.             self,
  437.             _id,
  438.             subject,
  439.             description,
  440.             add_by
  441.     ):
  442.         self.id = _id
  443.         self.subject = subject
  444.         self.description = description
  445.         self.add_by = add_by
  446.  
  447.     @classmethod
  448.     def get_all(cls):
  449.         return cls.query.order_by(cls.id).all()
  450.  
  451.     @classmethod
  452.     def by_id(cls, _id):
  453.         return cls.query.filter_by(id=_id).first()
  454.  
  455.     def json(self):
  456.         return {
  457.             "id": self.id,
  458.             "subject": self.subject,
  459.             "description": self.description,
  460.             "add_time": self.add_time.strftime('%Y-%m-%d')
  461.         }
  462.  
  463.  
  464. class Activitypict(db.Model):
  465.     __tablename__ = '_activity_picture'
  466.     id = db.Column(db.String, primary_key=True)
  467.     activity_id = db.Column(db.String, db.ForeignKey(Activity.id))
  468.     activity = db.relationship(Activity, foreign_keys=activity_id)
  469.     picture = db.Column(db.String)
  470.  
  471.     def __init__(
  472.             self,
  473.             _id,
  474.             activity_id,
  475.             picture
  476.     ):
  477.         self.id = _id
  478.         self.activity_id = activity_id
  479.         self.picture = picture
  480.  
  481.     @classmethod
  482.     def by_activity_id(cls, _activity_id):
  483.         return cls.query.filter_by(activity_id=_activity_id).all()
  484.  
  485.     def json(self):
  486.         return {
  487.             "id": self.id,
  488.             "picture": self.picture
  489.         }
  490.  
  491.  
  492. class Incident(db.Model):
  493.     __tablename__ = '_incident'
  494.     id = db.Column(db.String, primary_key=True)
  495.     subject = db.Column(db.String)
  496.     description = db.Column(db.String)
  497.     add_by = db.Column(db.String)
  498.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow)
  499.  
  500.     def __init__(
  501.             self,
  502.             _id,
  503.             subject,
  504.             description,
  505.             add_by
  506.     ):
  507.         self.id = _id
  508.         self.subject = subject
  509.         self.description = description
  510.         self.add_by = add_by
  511.  
  512.     @classmethod
  513.     def get_all(cls):
  514.         return cls.query.order_by(cls.id).all()
  515.  
  516.     @classmethod
  517.     def by_id(cls, _id):
  518.         return cls.query.filter_by(id=_id).first()
  519.  
  520.     def json(self):
  521.         return {
  522.             "id": self.id,
  523.             "subject": self.subject,
  524.             "description": self.description,
  525.             "add_time": self.add_time.strftime('%Y-%m-%d')
  526.         }
  527.  
  528.  
  529. class Incidentpict(db.Model):
  530.     __tablename__ = '_incident_picture'
  531.     id = db.Column(db.String, primary_key=True)
  532.     incident_id = db.Column(db.String, db.ForeignKey(Incident.id))
  533.     incident = db.relationship(Incident, foreign_keys=incident_id)
  534.     picture = db.Column(db.String)
  535.  
  536.     def __init__(
  537.             self,
  538.             _id,
  539.             incident_id,
  540.             picture
  541.     ):
  542.         self.id = _id
  543.         self.incident_id = incident_id
  544.         self.picture = picture
  545.  
  546.     @classmethod
  547.     def by_incident_id(cls, _incident_id):
  548.         return cls.query.filter_by(incident_id=_incident_id).all()
  549.  
  550.     def json(self):
  551.         return {
  552.             "id": self.id,
  553.             "picture": self.picture
  554.         }
  555.  
  556.  
  557. class ComplaintNews(db.Model):
  558.     __tablename__ = '_complaint_news'
  559.     id = db.Column(db.String, primary_key=True)
  560.     complaint_id = db.Column(db.String, db.ForeignKey(Complaint.id))
  561.     complaint = db.relationship(Complaint, foreign_keys=complaint_id)
  562.     description = db.Column(db.String)
  563.     image = db.Column(db.String)
  564.     add_by = db.Column(db.String, db.ForeignKey(Account.id))
  565.     add = db.relationship(Account, foreign_keys=add_by)
  566.     add_time = db.Column(db.TIMESTAMP, default=datetime.datetime.now())
  567.  
  568.     def __init__(
  569.             self,
  570.             _id,
  571.             complaint_id,
  572.             description,
  573.             image,
  574.             add_by
  575.     ):
  576.         self.id = _id
  577.         self.complaint_id = complaint_id
  578.         self.description = description
  579.         self.image = image
  580.         self.add_by = add_by
  581.  
  582.     @classmethod
  583.     def by_complaint_id(cls, _complaint_id):
  584.         return cls.query.filter_by(complaint_id=_complaint_id).first()
  585.  
  586.     def json(self):
  587.         return {
  588.             "id": self.id,
  589.             "complaint_id": self.complaint_id,
  590.             "complaint": self.complaint.subject,
  591.             "description": self.description,
  592.             "image": self.image,
  593.             "add_by": self.add.user.full_name,
  594.             "image_add_by": self.add.picture,
  595.             "add_time": timeconvert(self.add_time.strftime("%H:%M:%S")),
  596.             "date": self.add_time.strftime('%d %B %Y')
  597.         }
  598.  
  599.  
  600. class Inbox(db.Model):
  601.     __tablename__ = '_inbox'
  602.  
  603.     id = db.Column(db.String, nullable=False, primary_key=True, unique=True, default=lambda: uuid.uuid4().hex)
  604.     description = db.Column(db.String, nullable=False)
  605.     stats = db.Column(db.Integer, nullable=False, default=0)
  606.     send_to = db.Column(db.String, db.ForeignKey('_account.id'), nullable=False)
  607.     add_by = db.Column(db.String, db.ForeignKey('_account.id'), nullable=False)
  608.     add_time = db.Column(db.DateTime, nullable=False, default=lambda: datetime.datetime.now())
  609.     sender = db.relationship(Account, foreign_keys=add_by)
  610.     receiver = db.relationship(Account, foreign_keys=send_to)
  611.  
  612.     def __init__(self, description, add_by, send_to):
  613.         self.description = description
  614.         self.add_by = add_by
  615.         self.send_to = send_to
  616.  
  617.     def json(self):
  618.         return {
  619.             "id": self.id,
  620.             "description": self.description,
  621.             "stats": self.stats,
  622.             "add_by": self.add_by,
  623.             "add_time": str(self.add_time),
  624.             "send_to": self.send_to,
  625.             "receiver": self.receiver.user.full_name,
  626.             "receiver_picture": self.receiver.picture if self.receiver.picture is not None else None
  627.         }
  628.  
  629.     @classmethod
  630.     def select_all_by_add_by(cls, account_id):
  631.         return cls.query.filter_by(add_by=account_id).order_by(cls.add_time.desc()).all()
  632.  
  633.     @classmethod
  634.     def select_all_by_send_to(cls, account_id):
  635.         return cls.query.filter_by(send_to=account_id).order_by(cls.add_time.desc()).all()
  636.  
  637.     @classmethod
  638.     def count_unread(cls, account_id):
  639.         return cls.query.filter(and_(cls.send_to == account_id, cls.stats == 0)).count()
  640.  
  641.     @classmethod
  642.     def find_by_id(cls, _id):
  643.         return cls.query.filter_by(id=_id).first()
  644.  
  645.  
  646. class Support(db.Model):
  647.     __tablename__ = '_support'
  648.  
  649.     id = db.Column(db.String, primary_key=True, nullable=False, unique=True, default=lambda: uuid.uuid4().hex)
  650.     add_by = db.Column(db.String, db.ForeignKey('_account.id'), nullable=False)
  651.     maker = db.relationship(Account, foreign_keys=add_by)
  652.     send_to = db.Column(db.String, db.ForeignKey('_account.id'), nullable=False)
  653.     receiver = db.relationship(Account, foreign_keys=send_to)
  654.     picture = db.Column(db.String, nullable=True)
  655.     description = db.Column(db.String, nullable=False)
  656.     add_time = db.Column(db.DateTime, nullable=False, default=lambda: datetime.datetime.now())
  657.  
  658.     def __init__(self, add_by, send_to, picture, description):
  659.         self.add_by = add_by
  660.         self.send_to = send_to
  661.         self.picture = picture
  662.         self.description = description
  663.  
  664.     def json(self):
  665.         return {
  666.             "id": self.id,
  667.             "add_by": self.add_by,
  668.             "maker": self.maker.user.full_name,
  669.             "send_to": self.send_to,
  670.             "receiver": self.receiver.user.full_name,
  671.             "picture": self.picture,
  672.             "add_time": str(self.add_time)
  673.         }
  674.  
  675.  
  676. def getBerita():
  677.     return db.session.execute("SELECT DISTINCT on(_news.id) _news.id, _news.subject,"
  678.                               "_news_picture.picture,_news.description from _news INNER JOIN _news_picture ON _news.id = "
  679.                               "_news_picture.news_id").fetchall()
  680.  
  681.  
  682. def getBeritaNot(_id):
  683.     return db.session.execute("SELECT DISTINCT on(_news.id) _news.id, _news.subject,"
  684.                               "_news_picture.picture from _news INNER JOIN _news_picture ON _news.id = "
  685.                               "_news_picture.news_id WHERE _news.id !=:param", {'param': _id}).fetchall()
  686.  
  687.  
  688. def getPengaduan(param_id):
  689.     return db.session.execute(
  690.         "SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject,_complaint_picture.picture,_complaint.status,_complaint_category.name,_complaint_status.name as statusname,_complaint.add_time,_complaint.lat,_complaint.lon,_complaint.address,_complaint.reason,_complaint.add_by,_user.full_name,_user.phone,_account.picture from _complaint INNER JOIN _complaint_category ON _complaint.category_id = _complaint_category.id INNER JOIN _complaint_picture ON _complaint.id = _complaint_picture.complaint_id INNER JOIN _complaint_status ON _complaint.status = _complaint_status.status INNER JOIN _account ON _complaint.add_by = _account.id INNER JOIN _user ON _account.user_id = _user.id WHERE _complaint.add_by= :param",
  691.         {'param': param_id}).fetchall()
  692.  
  693.  
  694. def getPengaduanSatgas():
  695.     return db.session.execute(
  696.         "SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject, _complaint_picture.picture, "
  697.         "_complaint.status, _complaint_category.name, _complaint_status.name as statusname, _complaint.add_time, "
  698.         "_complaint.lat, _complaint.lon, _complaint.address, _complaint.reason, _complaint.add_by, _user.full_name, "
  699.         "_user.phone, _account.picture from _complaint "
  700.         "INNER JOIN _complaint_category ON _complaint.category_id = _complaint_category.id "
  701.         "INNER JOIN _complaint_picture ON _complaint.id = _complaint_picture.complaint_id "
  702.         "INNER JOIN _complaint_status ON _complaint.status = _complaint_status.status "
  703.         "INNER JOIN _account ON _complaint.add_by = _account.id "
  704.         "INNER JOIN _user ON _account.user_id = _user.id "
  705.         "WHERE _complaint.status = 0 or _complaint.status = 1"
  706.     ).fetchall()
  707.  
  708.  
  709. def getPengaduanTerkirim():
  710.     return db.session.execute("SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject,"
  711.                               "_complaint_picture.picture,_complaint.status,_complaint_category.name from _complaint INNER JOIN _complaint_category ON _complaint.category_id = "
  712.                               "_complaint_category.id INNER JOIN _complaint_picture ON _complaint.id = "
  713.                               "_complaint_picture.complaint_id WHERE _complaint.status=0 or _complaint.status=1").fetchall()
  714.  
  715.  
  716. def getPengaduanHistory():
  717.     return db.session.execute(
  718.         "SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject,"
  719.         "_complaint_picture.picture, _complaint.status, _complaint_category.name from _complaint "
  720.         "INNER JOIN _complaint_category ON _complaint.category_id = _complaint_category.id "
  721.         "INNER JOIN _complaint_picture ON _complaint.id = _complaint_picture.complaint_id "
  722.         "WHERE _complaint.status=2 or _complaint.status=3 or _complaint.status=4"
  723.     ).fetchall()
  724.  
  725.  
  726. def getPengaduanTerkirimAuth3(_id):
  727.     return db.session.execute(
  728.         "SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject, _complaint_picture.picture, "
  729.         "_complaint.status as status_id, _complaint_status.name as status, _complaint_category.name "
  730.         "from _complaint "
  731.         "INNER JOIN _complaint_category ON _complaint.category_id = _complaint_category.id "
  732.         "INNER JOIN _complaint_status ON _complaint.status = _complaint_status.status "
  733.         "INNER JOIN _complaint_picture ON _complaint.id = _complaint_picture.complaint_id "
  734.         "JOIN _account ON _complaint.add_by = _account.id "
  735.         "JOIN _user ON _account.user_id = _user.id "
  736.         "WHERE _complaint.add_by=:param and _complaint.status=0 or _complaint.status=1",
  737.         {'param': _id}
  738.     ).fetchall()
  739.  
  740.  
  741. def getPengaduanHistoryAuth3(_id):
  742.     return db.session.execute(
  743.         "SELECT DISTINCT on(_complaint.id) _complaint.id, _complaint.subject, _complaint_picture.picture, "
  744.         "_complaint.status as status_id, _complaint_status.name, _complaint_category.name "
  745.         "from _complaint "
  746.         "JOIN _complaint_status ON _complaint_status.status = _complaint.status "
  747.         "INNER JOIN _complaint_category ON _complaint.category_id = _complaint_category.id "
  748.         "INNER JOIN _complaint_picture ON _complaint.id = _complaint_picture.complaint_id "
  749.         "WHERE _complaint.add_by=:param and _complaint.status = 2 or _complaint.status = 4",
  750.         {'param': _id}
  751.     ).fetchall()
  752.  
  753.  
  754. def PengaduanById(_id):
  755.     return db.session.execute(
  756.         "SELECT DISTINCT on(c.id) c.id, c.subject, c.description, cp.picture, c.add_time, u.full_name, u.phone, a.picture "
  757.         "from _complaint c "
  758.         "INNER JOIN _complaint_picture cp ON c.id = cp.complaint_id "
  759.         "JOIN _account a ON a.id = c.add_by JOIN _user u ON u.id = a.user_id "
  760.         "WHERE c.id=:param",
  761.         {'param': _id}).fetchone()
  762.  
  763.  
  764. def get_cuaca_yesterday():
  765.     getdate = db.session.execute("SELECT current_date - INTEGER '1'").fetchone()
  766.     date = getdate[0]
  767.     return db.session.execute("SELECT _weather_category.name as kategori,_weather.date_forecast,_weather.temperature "
  768.                               "FROM _weather INNER JOIN _weather_category ON _weather.category_id=_weather_category.id "
  769.                               "WHERE _weather.date_forecast=:param", {"param": date}).fetchone()
  770.  
  771.  
  772. def get_cuaca_now():
  773.     getdate = db.session.execute("SELECT current_date").fetchone()
  774.     date = getdate[0]
  775.     return db.session.execute("SELECT _weather_category.name as kategori,_weather.date_forecast,_weather.temperature "
  776.                               "FROM _weather INNER JOIN _weather_category ON _weather.category_id=_weather_category.id "
  777.                               "WHERE _weather.date_forecast=:param", {"param": date}).fetchone()
  778.  
  779.  
  780. def get_cuaca_tomorrow():
  781.     getdate = db.session.execute("SELECT current_date + INTEGER '1'").fetchone()
  782.     date = getdate[0]
  783.     return db.session.execute("SELECT _weather_category.name as kategori,_weather.date_forecast,_weather.temperature "
  784.                               "FROM _weather INNER JOIN _weather_category ON _weather.category_id=_weather_category.id "
  785.                               "WHERE _weather.date_forecast=:param", {"param": date}).fetchone()
  786.  
  787.  
  788. def get_cuaca_da_tomorrow():
  789.     getdate = db.session.execute("SELECT current_date + INTEGER '2'").fetchone()
  790.     date = getdate[0]
  791.     return db.session.execute("SELECT _weather_category.name as kategori,_weather.date_forecast,_weather.temperature "
  792.                               "FROM _weather INNER JOIN _weather_category ON _weather.category_id=_weather_category.id "
  793.                               "WHERE _weather.date_forecast=:param", {"param": date}).fetchone()
  794.  
RAW Paste Data