def insert_interessenbindung_parlamentarische_gruppe(parlamentarier_id, organisation_id, stichdatum, praesidum: bool, beschreibung, beschreibung_fr, funktion_im_gremium, url, date, pdf_date): query = "INSERT INTO `interessenbindung` (`parlamentarier_id`, `organisation_id`, `art`, `funktion_im_gremium`, `beschreibung`, `beschreibung_fr`,`deklarationstyp`, `status`, `behoerden_vertreter`, `quelle`, `quelle_url`, `von`, `created_visa`,`created_date`, `updated_visa`, `updated_by_import`, `notizen`, `eingabe_abgeschlossen_visa`, `eingabe_abgeschlossen_datum`, `freigabe_visa`, `freigabe_datum`) VALUES ({}, {}, '{}', {}, '{}', '{}', '{}', '{}', '{}', '{}', {}, STR_TO_DATE('{}', '%d.%m.%Y'), '{}',STR_TO_DATE('{}', '%d.%m.%Y %T'), '{}', STR_TO_DATE('{}', '%d.%m.%Y %T'), '{}', '{}', STR_TO_DATE('{}', '%d.%m.%Y %T'), '{}', STR_TO_DATE('{}', '%d.%m.%Y %T'));\n".format( parlamentarier_id, organisation_id, "vorstand" if praesidum else "mitglied", _quote_str_or_NULL(funktion_im_gremium), beschreibung, beschreibung_fr, "deklarationspflichtig", "deklariert", "N", "PDF vom {}".format(_date_as_sql_string(pdf_date)), _quote_str_or_NULL(_escape_string(url)), _date_as_sql_string(stichdatum), "import", _datetime_as_sql_string(date), "import", _datetime_as_sql_string(date), "{0}/import/{1}: Erzeugt (PDF {2})".format(_date_as_sql_string(date), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(date), "import", _datetime_as_sql_string(date) ) return query
def insert_parlamentarische_gruppe(group_type, name_de, name_fr, name_it, beschreibung, sekretariat, adresse_str, adresse_zusatz, adresse_plz, adresse_ort, homepage, alias, date, pdf_date): query = """INSERT INTO `organisation` (`name_de`, `name_fr`, `name_it`, `beschreibung`, `sekretariat`, `adresse_strasse`, `adresse_zusatz`, `adresse_plz`, `ort`, `homepage`, `alias_namen_de`, `land_id`, `rechtsform`, `typ`, `vernehmlassung`, `created_visa`, `created_date`, `updated_visa`, `updated_by_import`, `notizen`, `eingabe_abgeschlossen_visa`, `eingabe_abgeschlossen_datum`) VALUES ('{}', {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, '{}', '{}', '{}', '{}', STR_TO_DATE('{}', '%d.%m.%Y %T'), '{}', STR_TO_DATE('{}', '%d.%m.%Y %T'), '{}', '{}', STR_TO_DATE('{}', '%d.%m.%Y %T')); SET @last_parlamentarische_gruppe = LAST_INSERT_ID(); """.format( _escape_string(name_de), "'" + _escape_string(name_fr) + "'" if name_fr else 'NULL', "'" + _escape_string(name_it) + "'" if name_it else 'NULL', _quote_str_or_NULL(_escape_string(beschreibung)), _quote_str_or_NULL(_escape_string(sekretariat)), _quote_str_or_NULL(_escape_string(adresse_str)), _quote_str_or_NULL(_escape_string(adresse_zusatz)), _quote_str_or_NULL(_escape_string(adresse_plz)), _quote_str_or_NULL(_escape_string(adresse_ort)), _quote_str_or_NULL(_escape_string(homepage)), _quote_str_or_NULL(_escape_string(alias)), 191, # Schweiz "Parlamentarische Freundschaftsgruppe" if group_type == 'friendship_group' else "Parlamentarische Gruppe", "EinzelOrganisation,dezidierteLobby", "nie", "import", _datetime_as_sql_string(date), "import", _datetime_as_sql_string(date), "{0}/import/{1}: Erzeugt (PDF {2})".format(_date_as_sql_string(date), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(date) ) return query
def update_adresse_organisation(organisation_id, adresse_str, adresse_zusatz, adresse_plz, adresse_ort, batch_time, pdf_date): query = "UPDATE `organisation` SET `adresse_strasse` = {0}, `adresse_zusatz` = {1}, `adresse_plz` = {2}, `ort` = {3}, `notizen` = CONCAT_WS('\\n\\n', '{4}', notizen), `updated_visa` = '{5}', `updated_date` = STR_TO_DATE('{6}', '%d.%m.%Y %T'), `updated_by_import` = STR_TO_DATE('{6}', '%d.%m.%Y %T') WHERE `id` = {7};\n".format( _quote_str_or_NULL(_escape_string(adresse_str)), _quote_str_or_NULL(_escape_string(adresse_zusatz)), _quote_str_or_NULL(_escape_string(adresse_plz)), _quote_str_or_NULL(_escape_string(adresse_ort)), "{0}/import/{1}: Adresse geändert (PDF {2})".format( _date_as_sql_string(batch_time), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(batch_time), organisation_id ) return query
def update_beschreibung_interessenbindung(interessenbindung_id, funktion_im_gremium, beschreibung, beschreibung_fr, url, batch_time, pdf_date): query = "UPDATE `interessenbindung` SET `funktion_im_gremium`={7}, `beschreibung` = {0}, `beschreibung_fr` = {5},`quelle` = '{8}', `quelle_url` = {6}, `notizen` = CONCAT_WS('\\n\\n', '{1}', notizen), `updated_visa` = '{2}', `updated_date` = STR_TO_DATE('{3}', '%d.%m.%Y %T'), `updated_by_import` = STR_TO_DATE('{3}', '%d.%m.%Y %T') WHERE `id` = {4};\n".format( _quote_str_or_NULL(_escape_string(beschreibung)), "{0}/import/{1}: Beschreibung geändert (PDF {2})".format( _date_as_sql_string(batch_time), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(batch_time), interessenbindung_id, _quote_str_or_NULL(_escape_string(beschreibung_fr)), _quote_str_or_NULL(_escape_string(url)), _quote_str_or_NULL(_escape_string(funktion_im_gremium)), "PDF vom {}".format(_date_as_sql_string(pdf_date)) ) return query
def update_beschreibung_organisation(organisation_id, beschreibung, batch_time, pdf_date): query = "UPDATE `organisation` SET `beschreibung` = {0}, `notizen` = CONCAT_WS('\\n\\n', '{1}', notizen), `updated_visa` = '{2}', `updated_date` = STR_TO_DATE('{3}', '%d.%m.%Y %T'), `updated_by_import` = STR_TO_DATE('{3}', '%d.%m.%Y %T') WHERE `id` = {4};\n".format( _quote_str_or_NULL(_escape_string(beschreibung)), "{0}/import/{1}: Beschreibung geändert (PDF {2})".format( _date_as_sql_string(batch_time), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(batch_time), organisation_id ) return query
def update_name_it_organisation(organisation_id, name_it, batch_time, pdf_date): query = "UPDATE `organisation` SET `name_it` = {}, `notizen` = CONCAT_WS('\\n\\n', '{}', notizen), `updated_visa` = '{}', `updated_date` = STR_TO_DATE('{}', '%d.%m.%Y %T'), `updated_by_import` = STR_TO_DATE('{}', '%d.%m.%Y %T') WHERE `id` = {};\n".format( _quote_str_or_NULL(_escape_string(name_it)), "{0}/import/{1}: Name IT geändert (PDF {2})".format(_date_as_sql_string(batch_time), user, _datetime_as_sql_string(pdf_date)), "import", _datetime_as_sql_string(batch_time), _datetime_as_sql_string(batch_time), organisation_id ) return query