예제 #1
0
    def select_config(self, tablename):
        """ Get data from table 'config' and fill widgets according to the name of the columns """

        sql = "SELECT * FROM " + self.schema_name + "." + tablename
        row = self.dao.get_row(sql)
        if not row:
            self.controller.show_warning("Any data found in table " + tablename)
            return None
        
        # Iterate over all columns and populate its corresponding widget
        columns = []
        for i in range(0, len(row)):

            column_name = self.dao.get_column_name(i)
            widget_type = utils_giswater.getWidgetType(column_name)
            if widget_type is QCheckBox:
                utils_giswater.setChecked(column_name, row[column_name])
            elif widget_type is QDateEdit:
                utils_giswater.setCalendarDate(column_name, datetime.strptime(row[column_name], '%Y-%m-%d'))
            elif widget_type is QTimeEdit:
                timeparts = str(row[column_name]).split(':')
                if len(timeparts) < 3:
                    timeparts.append("0")
                days = int(timeparts[0]) / 24
                hours = int(timeparts[0]) % 24
                minuts = int(timeparts[1])
                seconds = int(timeparts[2])
                time = QTime(hours, minuts, seconds)
                utils_giswater.setTimeEdit(column_name, time)
                utils_giswater.setText(column_name + "_day", days)
            else:
                utils_giswater.setWidgetText(column_name, row[column_name])
            columns.append(column_name)

        return columns
예제 #2
0
    def update_table(self, tablename, dialog):
        """ INSERT or UPDATE tables according :param update"""
        
        sql = "SELECT * FROM " + self.schema_name + "." + tablename
        row = self.controller.get_row(sql)

        columns = []
        for i in range(0, len(row)):
            column_name = self.dao.get_column_name(i)
            columns.append(column_name)

        if columns is not None:
            sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
            for column_name in columns:
                if column_name != 'id':
                    widget = dialog.findChild(QWidget, column_name)
                    widget_type = utils_giswater.getWidgetType(dialog, widget)
                    if widget_type is QCheckBox:
                        value = utils_giswater.isChecked(dialog, widget)
                    elif widget_type is QDateEdit:
                        date = dialog.findChild(QDateEdit, str(column_name))
                        value = date.dateTime().toString('dd/MM/yyyy')
                    elif widget_type is QTimeEdit:
                        aux = 0
                        widget_day = str(column_name) + "_day"
                        day = utils_giswater.getText(dialog, widget_day)
                        if day != "null":
                            aux = int(day) * 24
                        time = dialog.findChild(QTimeEdit, str(column_name))
                        timeparts = time.dateTime().toString('HH:mm:ss').split(':')
                        h = int(timeparts[0]) + int(aux)
                        aux = str(h) + ":" + str(timeparts[1]) + ":" + str(timeparts[2])
                        value = aux
                    elif widget_type is QSpinBox:
                        x = dialog.findChild(QSpinBox, str(column_name))
                        value = x.value()
                    elif widget_type is QComboBox:
                        value = utils_giswater.get_item_data(dialog, widget)
                    else:
                        value = utils_giswater.getWidgetText(dialog, widget)
                    if value == 'null':
                        sql += column_name + " = null, "
                    elif value is None:
                        pass
                    else:
                        if type(value) is not bool and widget_type is not QSpinBox:
                            value = value.replace(",", ".")
                        sql += column_name + " = '" + str(value) + "', "
            sql = sql[:len(sql) - 2]
        self.controller.execute_sql(sql)
        dialog.close()
예제 #3
0
    def update_config(self, tablename, dialog):
        """ Update table @tablename from values get from @dialog """

        sql = "SELECT * FROM " + self.schema_name + "." + tablename
        row = self.dao.get_row(sql)
        columns = []
        for i in range(0, len(row)):
            column_name = self.dao.get_column_name(i)
            if column_name != 'id': 
                columns.append(column_name)

        if columns is None:
            return
        
        sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
        for column_name in columns:         
            widget_type = utils_giswater.getWidgetType(column_name)
            if widget_type is QCheckBox:
                value = utils_giswater.isChecked(column_name)
            elif widget_type is QDateEdit:
                date = dialog.findChild(QDateEdit, str(column_name))
                value = date.dateTime().toString('yyyy-MM-dd')
            elif widget_type is QTimeEdit:
                aux = 0
                widget_day = str(column_name) + "_day"
                day = utils_giswater.getText(widget_day)
                if day != "null":
                    aux = int(day) * 24
                time = dialog.findChild(QTimeEdit, str(column_name))
                timeparts = time.dateTime().toString('HH:mm:ss').split(':')
                h = int(timeparts[0]) + int(aux)
                aux = str(h) + ":" + str(timeparts[1]) + ":00"
                value = aux
            elif widget_type is QSpinBox:
                x = dialog.findChild(QSpinBox, str(column_name))
                value = x.value()
            else:
                value = utils_giswater.getWidgetText(column_name)
              
            if value is not None:  
                if value == 'null':
                    sql += column_name + " = null, "
                else:
                    if type(value) is not bool and widget_type is not QSpinBox:
                        value = value.replace(",", ".")
                    sql += column_name + " = '" + str(value) + "', "

        sql = sql[:- 2]          
        self.controller.execute_sql(sql)
예제 #4
0
    def go2epa_options_get_data(self, tablename, dialog):
        """ Get data from selected table """
        
        sql = "SELECT * FROM " + self.schema_name + "." + tablename
        row = self.controller.get_row(sql)
        if not row:
            message = "Any data found in table"
            self.controller.show_warning(message, parameter=tablename)
            return None

        # Iterate over all columns and populate its corresponding widget
        columns = []
        for i in range(0, len(row)):
            column_name = self.dao.get_column_name(i)
            widget = dialog.findChild(QWidget, column_name)
            widget_type = utils_giswater.getWidgetType(dialog, widget)
            if row[column_name] is not None:
                if widget_type is QCheckBox:
                    utils_giswater.setChecked(dialog, widget, row[column_name])
                elif widget_type is QComboBox:
                    utils_giswater.set_combo_itemData(widget, row[column_name], 0)
                elif widget_type is QDateEdit:
                    dateaux = row[column_name].replace('/', '-')
                    date = QDate.fromString(dateaux, 'dd-MM-yyyy')
                    utils_giswater.setCalendarDate(dialog, widget, date)
                elif widget_type is QTimeEdit:
                    timeparts = str(row[column_name]).split(':')
                    if len(timeparts) < 3:
                        timeparts.append("0")
                    days = int(timeparts[0]) / 24
                    hours = int(timeparts[0]) % 24
                    minuts = int(timeparts[1])
                    seconds = int(timeparts[2])
                    time = QTime(hours, minuts, seconds)
                    utils_giswater.setTimeEdit(dialog, widget, time)
                    utils_giswater.setText(dialog, column_name + "_day", days)
                else:
                    utils_giswater.setWidgetText(dialog, widget, str(row[column_name]))

            columns.append(column_name)
            
        return columns
예제 #5
0
 def mg_config_get_data(self, tablename):                
     ''' Get data from selected table '''
     
     sql = "SELECT *"
     sql+= " FROM "+self.schema_name+"."+tablename
     row = self.dao.get_row(sql)
     if not row:
         self.controller.show_warning("Any data found in table "+tablename)
         return None
     
     # Iterate over all columns and populate its corresponding widget
     columns = []
     for i in range(0, len(row)):
         column_name = self.dao.get_column_name(i)
         widget_type = utils_giswater.getWidgetType(column_name)
         if widget_type is QCheckBox:
             utils_giswater.setChecked(column_name, row[column_name])                        
         else:
             utils_giswater.setWidgetText(column_name, row[column_name])
         columns.append(column_name) 
         
     return columns           
예제 #6
0
 def mg_config_accept_table(self, tablename, columns):
     ''' Update values of selected 'tablename' with the content of 'columns' '''
     
     if columns is not None:       
         sql = "UPDATE "+self.schema_name+"."+tablename+" SET "         
         for column_name in columns:
             if column_name != 'id':
                 widget_type = utils_giswater.getWidgetType(column_name)
                 if widget_type is QCheckBox:
                     value = utils_giswater.isChecked(column_name)                      
                 else:
                     value = utils_giswater.getWidgetText(column_name)
                 if value is None or value == 'null':
                     sql+= column_name+" = null, "     
                 else:
                     if type(value) is not bool:
                         value = value.replace(",", ".")
                     sql+= column_name+" = '"+str(value)+"', "           
         
         sql = sql[:-2]
         self.dao.execute_sql(sql)
                     
             
예제 #7
0
    def insert_or_update(self, update, tablename, dialog):
        """ INSERT or UPDATE tables according :param update"""

        sql = "SELECT *"
        sql += " FROM " + self.schema_name + "." + tablename
        row = self.dao.get_row(sql)

        columns = []
        for i in range(0, len(row)):
            column_name = self.dao.get_column_name(i)
            columns.append(column_name)

        if update:
            if columns is not None:
                sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
                for column_name in columns:
                    if column_name != 'id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is QCheckBox:
                            value = utils_giswater.isChecked(column_name)
                        elif widget_type is QDateEdit:
                            date = dialog.findChild(QDateEdit,
                                                    str(column_name))
                            value = date.dateTime().toString('yyyy-MM-dd')
                        elif widget_type is QTimeEdit:
                            aux = 0
                            widget_day = str(column_name) + "_day"
                            day = utils_giswater.getText(widget_day)
                            if day != "null":
                                aux = int(day) * 24
                            time = dialog.findChild(QTimeEdit,
                                                    str(column_name))
                            timeparts = time.dateTime().toString(
                                'HH:mm:ss').split(':')
                            h = int(timeparts[0]) + int(aux)
                            aux = str(h) + ":" + str(timeparts[1]) + ":00"
                            value = aux
                        elif widget_type is QSpinBox:
                            x = dialog.findChild(QSpinBox, str(column_name))
                            value = x.value()
                        else:
                            value = utils_giswater.getWidgetText(column_name)
                        if value == 'null':
                            sql += column_name + " = null, "
                        elif value is None:
                            pass
                        else:
                            if type(
                                    value
                            ) is not bool and widget_type is not QSpinBox:
                                value = value.replace(",", ".")
                            sql += column_name + " = '" + str(value) + "', "
                sql = sql[:len(sql) - 2]
        else:
            values = "VALUES("
            if columns is not None:
                sql = "INSERT INTO " + self.schema_name + "." + tablename + " ("
                for column_name in columns:
                    if column_name != 'id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is not None:
                            if widget_type is QCheckBox:
                                values += utils_giswater.isChecked(
                                    column_name) + ", "
                            elif widget_type is QDateEdit:
                                date = dialog.findChild(
                                    QDateEdit, str(column_name))
                                values += date.dateTime().toString(
                                    'yyyy-MM-dd') + ", "
                            else:
                                value = utils_giswater.getWidgetText(
                                    column_name)
                            if value is None or value == 'null':
                                sql += column_name + ", "
                                values += "null, "
                            else:
                                values += "'" + value + "',"
                                sql += column_name + ", "
                sql = sql[:len(sql) - 2] + ") "
                values = values[:len(values) - 2] + ")"
                sql += values
        self.controller.execute_sql(sql)
        dialog.close()
    def insert_or_update_new_psector(self, update, tablename, close_dlg=False):

        psector_name = utils_giswater.getWidgetText(self.dlg.name, return_string_null=False)
        if psector_name == "":
            message = "Mandatory field is missing. Please, set a value"
            self.controller.show_warning(message, parameter='Name')
            return

        rotation = utils_giswater.getWidgetText(self.dlg.rotation, return_string_null=False)
        if rotation == "":
            utils_giswater.setWidgetText(self.dlg.rotation, 0)

        name_exist = self.check_name(psector_name)
        if name_exist and not update:
            message = "The name is current in use"
            self.controller.show_warning(message)
            return
        else:
            self.enable_tabs(True)
            self.enable_buttons(True)

        sql = ("SELECT column_name FROM information_schema.columns"
               " WHERE table_name = '" + "v_edit_" + self.plan_om + "_psector'"
               " AND table_schema = '" + self.schema_name.replace('"', '') + "'"
               " ORDER BY ordinal_position")
        rows = self.controller.get_rows(sql)

        columns = []
        for i in range(0, len(rows)):
            column_name = rows[i]
            columns.append(str(column_name[0]))

        if update:
            if columns:
                sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
                for column_name in columns:
                    if column_name != 'psector_id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is QCheckBox:
                            value = utils_giswater.isChecked(column_name)
                        elif widget_type is QDateEdit:
                            date = self.dlg.findChild(QDateEdit, str(column_name))
                            value = date.dateTime().toString('yyyy-MM-dd HH:mm:ss')
                        elif (widget_type is QComboBox) and (column_name == 'expl_id' or column_name == 'sector_id'
                              or column_name == 'result_id' or column_name == 'psector_type'):
                            combo = utils_giswater.getWidget(column_name)
                            elem = combo.itemData(combo.currentIndex())
                            value = str(elem[0])
                        else:
                            value = utils_giswater.getWidgetText(column_name)
                        if value is None or value == 'null':
                            sql += column_name + " = null, "
                        else:
                            if type(value) is not bool:
                                value = value.replace(",", ".")
                            sql += column_name + " = '" + str(value) + "', "

                sql = sql[:len(sql) - 2]
                sql += " WHERE psector_id = '" + utils_giswater.getWidgetText(self.psector_id) + "'"

        else:
            values = "VALUES("
            if columns:
                sql = "INSERT INTO " + self.schema_name + "." + tablename + " ("
                for column_name in columns:
                    if column_name != 'psector_id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is not None:
                            if widget_type is QCheckBox:
                                value = str(utils_giswater.isChecked(column_name)).upper()
                            elif widget_type is QDateEdit:
                                date = self.dlg.findChild(QDateEdit, str(column_name))
                                values += date.dateTime().toString('yyyy-MM-dd HH:mm:ss') + ", "
                            elif (widget_type is QComboBox) and (column_name == 'expl_id' or column_name == 'sector_id'
                                or column_name == 'result_id' or column_name == 'psector_type'):
                                combo = utils_giswater.getWidget(column_name)
                                elem = combo.itemData(combo.currentIndex())
                                value = str(elem[0])
                            else:
                                value = utils_giswater.getWidgetText(column_name)
                            if value is None or value == 'null':
                                sql += column_name + ", "
                                values += "null, "
                            else:
                                values += "'" + value + "', "
                                sql += column_name + ", "

                sql = sql[:len(sql) - 2] + ") "
                values = values[:len(values) - 2] + ")"
                sql += values

        if not update:
            sql += " RETURNING psector_id"
            new_psector_id = self.controller.execute_returning(sql, search_audit=False, log_sql=True)
            utils_giswater.setText(self.dlg.psector_id, str(new_psector_id[0]))

            if new_psector_id and self.plan_om == 'plan':
                sql = ("SELECT parameter FROM " + self.schema_name + ".config_param_user "
                       " WHERE parameter = 'psector_vdefault' AND cur_user = current_user")
                row = self.controller.get_row(sql)
                if row:
                    sql = ("UPDATE " + self.schema_name + ".config_param_user "
                           " SET value = '" + str(new_psector_id[0]) + "' "
                           " WHERE parameter = 'psector_vdefault'")
                else:
                    sql = ("INSERT INTO " + self.schema_name + ".config_param_user (parameter, value, cur_user) "
                           " VALUES ('psector_vdefault', '" + str(new_psector_id[0]) + "', current_user)")
                self.controller.execute_sql(sql, log_sql=True)
        else:
            self.controller.execute_sql(sql, log_sql=True)
            
        self.dlg.tabWidget.setTabEnabled(1, True)
        self.insert_psector_selector(self.plan_om+'_psector_selector', 'psector_id', utils_giswater.getWidgetText(self.dlg.psector_id))

        if close_dlg:
            self.reload_states_selector()
            self.close_dialog()
예제 #9
0
    def insert_or_update_new_psector(self, update, tablename):

        sql = "SELECT *"
        sql += " FROM " + self.schema_name + "." + tablename
        row = self.dao.get_row(sql)
        columns = []
        for i in range(0, len(row)):
            column_name = self.dao.get_column_name(i)
            columns.append(column_name)

        if update:
            if columns is not None:
                sql = "UPDATE " + self.schema_name + "." + tablename + " SET "
                for column_name in columns:
                    if column_name != 'psector_id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is QCheckBox:
                            value = utils_giswater.isChecked(column_name)
                        elif widget_type is QDateEdit:
                            date = self.dlg.findChild(QDateEdit, str(column_name))
                            value = date.dateTime().toString('yyyy-MM-dd HH:mm:ss')
                        else:
                            value = utils_giswater.getWidgetText(column_name)
                        if value is None or value == 'null':
                            sql += column_name + " = null, "
                        else:
                            if type(value) is not bool:
                                value = value.replace(",", ".")
                            sql += column_name + " = '" + str(value) + "', "

                sql = sql[:len(sql) - 2]
                sql += " WHERE psector_id = '" + self.psector_id.text() + "'"

        else:
            values = "VALUES("
            if columns is not None:
                sql = "INSERT INTO " + self.schema_name + "." + tablename+" ("
                for column_name in columns:
                    if column_name != 'psector_id':
                        widget_type = utils_giswater.getWidgetType(column_name)
                        if widget_type is not None:
                            if widget_type is QCheckBox:
                                values += utils_giswater.isChecked(column_name)+", "
                            elif widget_type is QDateEdit:
                                date = self.dlg.findChild(QDateEdit, str(column_name))
                                values += date.dateTime().toString('yyyy-MM-dd HH:mm:ss')+", "
                            else:
                                value = utils_giswater.getWidgetText(column_name)
                            if value is None or value == 'null':
                                sql += column_name + ", "
                                values += "null, "
                            else:
                                values += "'" + value + "',"
                                sql += column_name + ", "
                sql = sql[:len(sql) - 2]+") "
                values = values[:len(values)-2] + ")"
                sql += values
                
        self.controller.execute_sql(sql)
        
        self.close_dialog()