
    |rh                        d Z ddlmZ ddlZddlZddlmZmZ ddlZddlZdZ	dZ
dZdZd	Zd
ZdZdZdZdZdZdZdZdZdZdZdZdZdZdZdZdZdZdZ dZ!dZ"dZ#d Z$d!Z%d"Z&d#Z'd$Z(d%Z)d&Z*d'Z+d(Z,d)Z-d*Z.d+Z/d,Z0d-Z1d.Z2d/Z3d0Z4d1Z5d2Z6d3Z7d4Z8d5 Z9d6 Z:d7 Z;d8 Z<d9 Z=d: Z>d; Z?d< Z@d= ZAd> ZBd? ZCd@ ZDdA ZEdB ZFdC ZGdD ZHdE ZIdF ZJdudGZKdudHZLdudIZMdvdJZNdwdKZOdL ZPdM ZQdN ZRdO ZSdP ZTdQ ZUdR ZVdS ZWdT ZXdU ZYdVeZdWe[dXe[dYeZfdZZ\d[ Z]d\ Z^d] Z_d^ Z`d_ Zad` Zbda Zcdb Zddc Zedd Zfde Zgdf Zhdg Zidh Zjdi Zkdj Zldk ZmdlZ/dm Zndn Zodo Zpdp Zqdq Zrdr Zsds Ztdt Zuy)xu   
Dieses Modul bietet Zugriffsfunktionen für Benutzerdaten in der Datenbank.

Hier sind die Zugriffe wo die Applikation benötigt um die Daten anzuzeigen und zu bearbeiten. 
    )mysqlN)datetimedateziSELECT CONCAT(firstname, ' ', lastname) as user_name, password, admin FROM webapp_logins WHERE email = %sa  SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, users_region.region AS region, users_typ.typ AS typ, users.prefix AS prefix, users.phone AS phone, users.email AS email, users_approval.approval AS approval, users.approval_until, users.able_to_drive, users_status_application.status_application_state AS status_application, users_status_work.status AS status_work, users.doc_url FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id LEFT JOIN users_fleet ON users.fleet = users_fleet.id LEFT JOIN users_region ON users.region = users_region.id LEFT JOIN users_gender ON users.gender = users_gender.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_typ ON users.typ = users_typ.id WHERE deleted = 0 AND users.status_work NOT IN (8, 10, 12, 14, 7) and family_member = 0 ORDER BY users.id DESCa-  SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, users_region.region as region , users.prefix AS prefix, users.phone AS phone, users.email AS email, users.able_to_drive,  users_status_work.status AS status_work FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id LEFT JOIN users_fleet ON users.fleet = users_fleet.id LEFT JOIN users_region ON users.region = users_region.id LEFT JOIN users_gender ON users.gender = users_gender.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_typ ON users.typ = users_typ.id WHERE deleted = 0 and users.status_work in (12) a  SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, users_region.region as region ,users_typ.typ as typ, users.prefix AS prefix, users.phone AS phone, users.email AS email, users_approval.approval AS approval, users.approval_until, users.able_to_drive,  users_status_application.status_application_state AS status_application, users_status_work.status AS status_work, users.doc_url FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id LEFT JOIN users_fleet ON users.fleet = users_fleet.id LEFT JOIN users_region ON users.region = users_region.id LEFT JOIN users_gender ON users.gender = users_gender.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_typ ON users.typ = users_typ.id WHERE deleted = 0 and users.status_work in (8,10,14,7) and family_member = 0 ORDER BY users.id DESCa  SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, users.email,  users_region.region AS region, users_typ.typ as typ, users.able_to_drive, users.tookan_id as Tookan, users.planday_id as Planday, users.uuid_id as Uber, users.telegram_id as Telegram FROM users LEFT JOIN users_region ON users.region = users_region.id LEFT JOIN users_typ ON users.typ = users_typ.id WHERE deleted = 0a	  SELECT users.user_id AS user_id, users.user_name AS user_name, users.first_name AS first_name, users.last_name AS last_name, users.phone AS phone, users.prefix AS prefix, users.email AS email, users.uuid_id AS uuid_id, users.STATUS AS STATUS, users_fleet.fleet_name AS fleet, users.planday_id, users.tookan_id, users.navision_id, users_gender.description AS gender, users_typ.typ AS typ, users.application_date AS application_date, users.vehicle AS vehicle, users_approval.approval AS approval, users.address AS address, users.postal_code AS postal_code, users.location AS location, users.country AS country, users.ahv_number AS ahv_number, users.birth_date AS birth_date, users.language AS language, users.nationality AS nationality, users.first_conversation AS first_conversation, users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, users.approval_until AS approval_until, users_civil_status.civil_status AS civil_status, users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, users.iban AS iban,  users.termination_date AS termination_date, users.termination_final_date AS termination_final_date, users.termination_reason AS termination_reason, users.note AS note, users_status_application.status_application_state AS status_application, users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, users.application_from_where AS application_from_where, users_region.region AS region, users_status_work.status AS status_work, users.introduction_with AS introduction_with, users.introduction_date AS introduction_date, users.start_short_operation AS start_short_operation, users.private_mail AS private_mail, users.current_state AS current_state, users.doc_url AS doc_url FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id LEFT JOIN users_fleet ON users.fleet = users_fleet.id LEFT JOIN users_region ON users.region = users_region.id LEFT JOIN users_gender ON users.gender = users_gender.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_typ ON users.typ = users_typ.id WHERE users.user_id = %s;z2SELECT * FROM users WHERE email = %s OR phone = %szFSELECT * FROM users WHERE (email = %s OR phone = %s) AND user_id != %sa  SELECT users.user_id AS user_id, users.first_name AS first_name, users.last_name AS last_name, users.company_name AS company_name, users.phone AS phone, users.prefix AS prefix, users.email AS email, users.uuid_id AS uuid_id, users.planday_id, users.tookan_id, users.navision_id AS navision_id, users.telegram_id AS telegram_id,  users.STATUS AS STATUS, users.fleet AS fleet, users.gender AS gender, users.typ AS typ, users.application_date AS application_date, users.vehicle AS vehicle, users.approval AS approval, users.address AS address, users.postal_code AS postal_code, users.location AS location, users.country AS country, users.ahv_number AS ahv_number, users.birth_date AS birth_date, users.language AS language, users.nationality AS nationality, users.first_conversation AS first_conversation, users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, users.approval_until AS approval_until, users.civil_status AS civil_status, users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, users.partner_name AS partner_name, users.partner_surname AS partner_surname, users.partner_ahv_number AS partner_ahv_number, users.partner_address AS partner_address, users.partner_job AS partner_job, users.iban AS iban, users.missing_documents AS missing_documents, users.termination_date AS termination_date, users.termination_final_date AS termination_final_date, users.termination_reason AS termination_reason, users.out_of_system AS out_of_system, users.note AS note, users.status_application AS status_application, users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, users.application_from_where AS application_from_where, users.region AS region, users.status_work AS status_work, users.introduction_with AS introduction_with, users.introduction_date AS introduction_date, users.start_short_operation AS start_short_operation, users.private_mail AS private_mail, users.current_state AS current_state, users.doc_url AS doc_url, users.partner_nationality FROM users LIMIT 1;zSELECT `case_id`, `user_id`, `transmitted_over`, `receiver`, `concerns_topic`, `should_state`, `is_state`, `controller`, `control_report`, `decision`, `note`, `control_status`, `completed` FROM talentsphere_administration LIMIT 1a  SELECT `case_id`, `talentsphere_administration`.`user_id`, users.user_name, `transmitted_over`, `receiver`, `concerns_topic`, `should_state`, `is_state`, `controller`, `control_report`, `decision`, `talentsphere_administration`.`note`, `control_status`, `completed`,`file_url` FROM talentsphere_administration LEFT JOIN users on users.user_id = talentsphere_administration.user_id WHERE case_id = %sz*UPDATE users SET deleted = 1 where id = %sa	  SELECT users.user_id AS user_id,  users.first_name AS first_name, users.last_name AS last_name, users.company_name AS company_name, users.phone AS phone, users.prefix AS prefix, users.email AS email, users.uuid_id as uuid_id, users.planday_id AS planday_id, users.tookan_id as tookan_id, users.navision_id AS navision_id, users.telegram_id AS telegram_id, users.STATUS AS STATUS, users.fleet AS fleet, users.gender AS gender, users.typ AS typ, users.application_date AS application_date, users.vehicle AS vehicle, users.approval AS approval, users.address AS address, users.postal_code AS postal_code, users.location AS location, users.country AS country, users.ahv_number AS ahv_number, users.birth_date AS birth_date, users.language AS language, users.nationality AS nationality, users.first_conversation AS first_conversation, users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, users.approval_until AS approval_until, users.civil_status AS civil_status, users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, users.partner_name AS partner_name, users.partner_surname AS partner_surname, users.partner_ahv_number AS partner_ahv_number, users.partner_address AS partner_address, users.partner_job AS partner_job, users.iban AS iban, users.missing_documents AS missing_documents, users.termination_date AS termination_date, users.termination_final_date AS termination_final_date, users.termination_reason AS termination_reason, users.out_of_system AS out_of_system, users.note AS note, users.status_application AS status_application, users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, users.application_from_where AS application_from_where, users.region AS region, users.status_work AS status_work, users.introduction_with AS introduction_with, users.introduction_date AS introduction_date, users.start_short_operation AS start_short_operation, users.private_mail AS private_mail, users.current_state AS current_state, users.doc_url AS doc_url FROM users WHERE user_id = %sz2UPDATE users SET tookan_id = %s WHERE user_id = %sz3UPDATE users SET planday_id = %s WHERE user_id = %szzINSERT INTO f2023_logs (user_id, content_old, content_new, func, editor, webtable) VALUES (%s, %s, %s, %s, %s, 'employee')zINSERT INTO f2023_logs (user_id, content_old, content_new, func, editor, webtable) VALUES (%s, %s, %s, %s, %s, 'administration')zSELECT id, user_id, content_old, content_new, func, editor, ts FROM f2023_logs WHERE webtable = 'employee' and user_id = %s ORDER BY ts DESC;zSELECT id, user_id, content_old, content_new, func, editor, ts FROM f2023_logs WHERE webtable = 'administration' and user_id = %s ORDER BY ts DESC;z0SELECT content, ts FROM f2023_logs WHERE id = %sa  SELECT users.id, users.user_id, user_name,users.email, users.prefix, users.phone, users_approval.approval, CASE WHEN users.approval = 1 THEN 2 WHEN users.approval != 1 AND users.approval_until IS NULL THEN 0 WHEN users.approval != 1 AND users.approval_until IS NOT NULL THEN 1 ELSE 0 END AS approval_until, users_civil_status.civil_status, users.number_of_children, users.ahv_number, users.other_jobs, CASE WHEN users.civil_status IN (1, 4) THEN 2 WHEN users.civil_status = 2 AND users_approval.approval = 1 THEN 2 WHEN users.civil_status = 2 AND users.partner_name IS NOT NULL AND users.partner_surname IS NOT NULL AND users.partner_ahv_number IS NOT NULL AND users.marriage_date IS NOT NULL AND users.partner_nationality IS NOT NULL AND users.partner_address IS NOT NULL AND users.partner_job IS NOT NULL THEN 1 WHEN users.civil_status = 3 AND users.divorce_date IS NOT NULL THEN 1 ELSE 0 END AS partner_info_complete, users.planday_id FROM `users` LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id WHERE users.status_work NOT IN (7, 8, 9, 10, 14) and family_member = 0 AND users.approval IS NOT NULL;a  SELECT   t.case_id as case_id,   t.id as id,   DATE(t.date) as date,   CONCAT(t.user_id, ' ', user_id_users.user_name) AS applicant,   talentsphere_transmission_method.name AS transmission_method,   receiver_users.user_name AS receiver,   talentsphere_concerns.name AS concerns_topic,   t.should_state,   t.is_state,   controller_users.user_name AS controller,   t.control_report,   t.decision,   t.note,   t.file_url,   t.control_status,   t.completed FROM talentsphere_administration AS t LEFT JOIN users AS user_id_users ON t.user_id = user_id_users.user_id LEFT JOIN users AS receiver_users ON t.receiver = receiver_users.user_id LEFT JOIN users AS controller_users ON t.controller = controller_users.user_id LEFT JOIN talentsphere_transmission_method ON t.transmitted_over = talentsphere_transmission_method.id LEFT JOIN talentsphere_concerns ON t.concerns_topic = talentsphere_concerns.id WHERE t.completed = 0 or t.completed = 2 ORDER BY t.date ASCa  SELECT   t.case_id as case_id,   t.id as id,   DATE(t.date) as date,    CONCAT(t.user_id, ' ', user_id_users.user_name) AS applicant,   talentsphere_transmission_method.name AS transmission_method,   receiver_users.user_name AS receiver,   talentsphere_concerns.name AS concerns_topic,   t.should_state,   t.is_state,   controller_users.user_name AS controller,   t.control_report,   t.decision,   t.note,   t.file_url,   t.control_status,   t.completed FROM talentsphere_administration AS t LEFT JOIN users AS user_id_users ON t.user_id = user_id_users.user_id LEFT JOIN users AS receiver_users ON t.receiver = receiver_users.user_id LEFT JOIN users AS controller_users ON t.controller = controller_users.user_id LEFT JOIN talentsphere_transmission_method ON t.transmitted_over = talentsphere_transmission_method.id LEFT JOIN talentsphere_concerns ON t.concerns_topic = talentsphere_concerns.id WHERE t.completed = 1 ORDER BY t.date DESCzSELECT user_id from usersa  SELECT users.id,users.user_id, users.user_name AS user_name, users.email AS email, users_typ.typ, users.prefix AS prefix, users.phone AS phone, users_status_work.status AS status_work, users_status_application.status_application_state AS status_application, users_approval.approval AS approval, users.approval_until FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_typ ON users_typ.id = users.typ WHERE approval_until <= DATE(NOW()) AND users.status_work NOT IN (8, 9, 10, 14, 12, 1) AND users.able_to_drive = 1 UNION SELECT users.id,users.user_id, users.user_name AS user_name, users.email AS email,users_typ.typ , users.prefix AS prefix, users.phone AS phone, users_status_work.status AS status_work, users_status_application.status_application_state AS status_application, users_approval.approval AS approval, users.approval_until FROM users LEFT JOIN users_approval ON users.approval = users_approval.id LEFT JOIN users_status_work ON users.status_work = users_status_work.id LEFT JOIN users_status_application ON users.status_application = users_status_application.id LEFT JOIN users_typ ON users_typ.id = users.typ WHERE MONTH(approval_until) = MONTH(NOW()) AND YEAR(approval_until) = YEAR(NOW()) AND users.status_work NOT IN (8, 9, 10, 14, 12, 1) and family_member = 0 AND users.able_to_drive = 1 ORDER BY approval_until ASCu  SELECT y, m, CASE     WHEN type = 12 THEN 'Kurzstrecke'     WHEN type = 18 THEN 'Langstrecke'     ELSE 'Other' END AS Typ, total, CASE     WHEN Source = 'Addons' THEN 'Zusätze'     WHEN Source = 'Driverapp' THEN 'Normale Fahrten'     WHEN Source = 'Guarantee' THEN 'Garantierte Fahrten'     ELSE Source END AS Source FROM `Ansicht_payroll_total` WHERE user_id = %s AND y = %s AND m = %szSELECT date, qty, comment, users.user_name as Ersteller FROM `drivers_remarks` LEFT JOIN users on users.user_id = drivers_remarks.disponent_id WHERE drivers_remarks.user_id = %s and YEAR(date) = %s and MONTH(date) = %sz>select json from credentials where name = 'google_service_acc'zSELECT Y as y, M as m, CASE   WHEN art = 'WT_NT' THEN 'weekend'   WHEN art = 'NT_NT' THEN 'week'   ELSE art END AS art_type, value FROM uber_cache_payroll_total WHERE user_id = %s AND Y = %s AND M = %sa	  SELECT users_documet_characteristics.name,users_approval_documents_dependency.doc_id, users_documet_characteristics.sites, users_documet_characteristics.needed_date, users_approval_documents_dependency.approval_id, users_approval_documents_dependency.typ_id FROM `users_approval_documents_dependency` LEFT JOIN users_documet_characteristics on users_documet_characteristics.id = users_approval_documents_dependency.doc_id and users_approval_documents_dependency.igno = 0 and users_documet_characteristics.users_table = 1;zbSELECT name, sites, needed_date FROM users_documet_characteristics where administrations_table = 1z>INSERT INTO users_family (user_id, children_id) VALUES (%s,%s)a$  select users.user_id,users.first_name, users.last_name, users.gender, users.birth_date, users.note, users.child_allowances from users LEFT JOIN users_family on users.user_id = users_family.children_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;aj  select users.user_id,users.first_name, users.last_name, users_gender.description, users.birth_date, users.note, users.child_allowances  from users LEFT JOIN users_family on users.user_id = users_family.children_id LEFT JOIN users_gender on users_gender.id = users.gender WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;aZ  select users.user_id as partner_id,users.first_name, users.last_name, users.birth_date, users.ahv_number, users.approval, users.other_jobs, users.address, users.gender as gender from users LEFT JOIN users_family on users.user_id = users_family.partner_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;a  select users.user_id as partner_id,users.first_name as partner_first_name, users.last_name as partner_last_name, users.birth_date as partner_birth_date, users.ahv_number as partner_ahv_number, users.approval as partner_approval, users.other_jobs as partner_other_jobs, users.address as partner_address, users.gender as partner_gender from users LEFT JOIN users_family on users.user_id = users_family.partner_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;zUPDATE users set first_name = %s, last_name = %s, gender = %s, birth_date = %s,  note = %s, child_allowances = %s where user_id = %szINSERT INTO users (user_id,user_name, first_name, last_name, gender, birth_date,note,child_allowances,family_member) VALUES (%s,%s, %s, %s, %s, %s, %s, %s, 1)zINSERT INTO users (user_id, first_name, last_name, birth_date,address, ahv_number, approval, other_jobs, gender,family_member) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 1)zUPDATE users SET first_name = %s, last_name = %s, birth_date = %s, address = %s, ahv_number = %s, approval = %s, other_jobs = %s, gender = %s WHERE user_id = %sz?INSERT INTO users_family (user_id, children_id) VALUES (%s, %s)z>INSERT INTO users_family (user_id, partner_id) VALUES (%s, %s)z:UPDATE users_family set deleted = 1 where children_id = %sz9UPDATE users_family set deleted = 1 where partner_id = %sa  SELECT users_documet_characteristics.name,users_approval_documents_dependency.doc_id, users_documet_characteristics.sites, users_documet_characteristics.needed_date FROM `users_approval_documents_dependency` LEFT JOIN users_documet_characteristics on users_documet_characteristics.id = users_approval_documents_dependency.doc_id WHERE users_approval_documents_dependency.approval_id = %s or users_approval_documents_dependency.typ_id = %s AND doc_id != 3 AND users_approval_documents_dependency.igno = 0;z4SELECT id, name FROM `users_documet_characteristics`z|INSERT INTO `users_documents`(`user_id`, `doc_typ`, `date_of_expire`, `date_of_begin`, `source`) VALUES (%s, %s, %s, %s, %s)a)  
    SELECT
    CONCAT_WS(', ',
        NULLIF(TRIM(address), ''),
        NULLIF(TRIM(postal_code), ''),
        NULLIF(TRIM(location), ''),
        NULLIF(TRIM(country), '')
    ) AS full_address,
    address, postal_code, location, country
    FROM users
    WHERE user_id = %s
    LIMIT 1
    c           	          t         j                  j                         5 }|j                  t        | ||||f       t         j                  j                          d d d        y # 1 sw Y   y xY wN)r   
connectioncursorexecuteINSERT_USERS_DOCcommit)user_iddoc_id
start_dateend_datesourcer	   s         -/home/janakan/Projects/talentsphere/models.pyinsert_docs_dbr     sW    				 	 	" "f'&HV(UV!" " "   :A""A+c            
      H   t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }|D cg c]  }t        t        ||             }}|cd d d        S c c}w c c}w # 1 sw Y   y xY wNr   )	r   r   r	   r
   SELECT_DOCSdescriptionfetchalldictzipr	   desccolumn_namesrowsrow
final_datas         r   get_doc_fk_tabler"     s    				 	 	" f{#,2,>,>?DQ?? >BCsd3|S12C
C ?C	 )   $BBB%BB
BB!c           
      N   t         j                  j                         5 }|j                  t        | |f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|cd d d        S c c}w c c}w # 1 sw Y   y xY wr   )	r   r   r	   r
   SELECT_SPECIFIC_DOCS_REQUIr   r   r   r   )approval_idtyp_idr	   r   r   r   r    r!   s           r   get_required_docsr(     s    				 	 	" f1;v2GH,2,>,>?DQ?? >BCsd3|S12C
C ?C	 s)   'BBB(BB
BB$c            
      H   t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }|D cg c]  }t        t        ||             }}|cd d d        S c c}w c c}w # 1 sw Y   y xY wr   )	r   r   r	   r
   SELECT_NEEDED_DOCS_FORMr   r   r   r   r   s         r   get_needed_docs_fromr+     s    				 	 	" f./,2,>,>?DQ?? >BCsd3|S12C
C ? D r#   c            
      H   t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }|D cg c]  }t        t        ||             }}|cd d d        S c c}w c c}w # 1 sw Y   y xY wr   )	r   r   r	   r
   -SELECT_DOCS_FORMATE_FORM_ADMINISTRATION_TABLEr   r   r   r   r   s         r   *get_docs_formate_from_administration_tabler.     s    				 	 	" fDE,2,>,>?DQ?? >BCsd3|S12C
C ? D r#   c                      t         j                  j                         5 } | j                  t               | j                         }t        j                  |d   d         }|cd d d        S # 1 sw Y   y xY wr   )r   r   r	   r
   SELECT_GOOGLE_SERVICE_ACCr   jsonloads)r	   data	cred_jsons      r   get_cred_google_service_accr5     s]    				 	 	" f01 JJtAwqz*		  s   AA++A4c                    t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }||fcd d d        S c c}w # 1 sw Y   y xY wr   )r   r   r	   r
   
SELECT_LOGr   r   )r   r	   r   r   r   s        r   log_viewr8     sy    				 	 	" 	"fzG:.,2,>,>?DQ??  \!	" 	" @		" 	"s   &A5A0A50A55A>c                 H   t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }|st        |       }}||d}|cd d d        S c c}w c c}w # 1 sw Y   y xY w)Nr   )columnsr3   )r   r   r	   r
   SELECT_ADMINISTRATION_LOGr   r   list)case_idr	   r   r   r   row_data	data_rowsr3   s           r   log_administration_viewr@     s    				 	 	" f07*= -3,>,>?DQ??   59EHT(^E	E $
 ' 
 @ F s/   &BBB'B/B<B
BB!c                    t         j                  j                         5 }|j                  t        | f       |j                         }t        j                  |d   d         }|g|d   d   fcd d d        S # 1 sw Y   y xY w)Nr      )r   r   r	   r
   SELECT_LOG_ROW_IDr   astliteral_eval)row_idr	   r3   resulting_dicts       r   log_detailsrH     ss    				 	 	" ,f(6)4 ))$q'!*5a+, , ,s   AA66A?c           	      p   t         j                  j                         5 }|dk(  r.|j                  t        | t        |      t        |      ||f       n2|dk(  r-|j                  t        | t        |      t        |      ||f       t         j                  j                          d d d        y # 1 sw Y   y xY w)Nemployeeadministration)r   r   r	   r
   
INSERT_LOGstrINSERT_ADMINISTRATION_LOGr   )r   content_oldcontent_newfunceditortabler	   s          r   logrT     s    				 	 	" "fJNN:wK0@#kBRTXZ`&bc&&^^5wK@PRUVaRbdhjp6rs!" " "s   BB,,B5c                     t         j                  j                         5 }|j                  t        | f       |j                         }|cddd       S # 1 sw Y   yxY w)zq
    Args:
        login_email (String): Email Input von User

    Returns:
        Tuple: Vorname, Passwort
    N)r   r   r	   r
   LOGIN_QUERYfetchone)login_emailr	   results      r   get_login_userrZ     sL     
			 	 	" f{K>2"  s   )AAc                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wz<
    Returns:
        Tuple: Spaltennamen und Userdaten
    r   N)r   r   r	   r
   
VIEW_QUERYr   r   r	   r   r   r   s       r   get_users_view_datar_     su    
 
			 	 	" "fz",2,>,>?DQ?? T!" " @" "   $A3A.A3.A33A<c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wr\   )r   r   r	   r
   EXTERNAL_VIEW_QUERYr   r   r^   s       r   get_users_external_view_datarc     v    
 
			 	 	" "f*+,2,>,>?DQ?? T!" " @" "r`   c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wr\   )r   r   r	   r
   INACTIVE_VIEW_QUERYr   r   r^   s       r   get_inactive_users_view_datarg   *  rd   r`   c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wr\   )r   r   r	   r
   TECH_VIEW_QUERYr   r   r^   s       r   get_users_tech_datarj   7  su    
 
			 	 	" "f',2,>,>?DQ?? T!" " @" "r`   c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wr\   )r   r   r	   r
   ADMINISTRATION_NOT_COMP_QUERYr   r   r^   s       r    get_administration_not_comp_datarm   D  sv    
 
			 	 	" "f45,2,>,>?DQ?? T!" " @" "r`   c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcddd       S c c}w # 1 sw Y   yxY wr\   )r   r   r	   r
   ADMINISTRATION_COMP_QUERYr   r   r^   s       r   get_administration_comp_datarp   Q  sv    
 
			 	 	" "f01,2,>,>?DQ?? T!" " @" "r`   c                    t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]2  }t        t        |            D ci c]  }||   ||   ||   nd c}4 }}}|cddd       S c c}w c c}w c c}}w # 1 sw Y   yxY w)   
    Details über den spezifischen Fahrer

    Args:
        user_id String: row_id für den die Spezifischen Daten

    Returns:
        data List: Liste mit die Spaltennamen und deren Wert
    r   N )	r   r   r	   r
   DETAILS_QUERYr   r   rangelenr   r	   r   r   r   r>   ir3   s           r   get_users_details_datary   ^  s     
			 	 	" f}gZ0,2,>,>?DQ??  !
 Y^^abn^oXpqST\!_hqk.Ex{2Mq
 

   @
 r
 A   &B=B-B=(B7B2B7B=-B=2B77B==Cc           
      P   t         j                  j                         5 }|j                  t        | ||f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|cddd       S c c}w c c}w # 1 sw Y   yxY wrr   r   N)	r   r   r	   r
   SELECT_PAYCHECK_DATAr   r   r   r   	r   yearmonthr	   r   r   r   r    r!   s	            r   get_users_paycheck_datar   v  s     
			 	 	" 	f+WT5,AB,2,>,>?DQ?? >BCsd3|S12C
C	 	 @ D	 	)   (BBB)BB
BB%c           
      P   t         j                  j                         5 }|j                  t        | ||f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|cddd       S c c}w c c}w # 1 sw Y   yxY wr|   )	r   r   r	   r
   SELECT_ADDONST_DETAILS_DATAr   r   r   r   r~   s	            r   get_users_addons_details_datar     s     
			 	 	" 	f2GD%3HI,2,>,>?DQ?? >BCsd3|S12C
C	 	 @ D	 	r   c           
      P   t         j                  j                         5 }|j                  t        | ||f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|cddd       S c c}w c c}w # 1 sw Y   yxY wr|   )	r   r   r	   r
   SELECT_UBER_DATAr   r   r   r   r~   s	            r   get_users_paycheck_data_uberr     s     
			 	 	" 	f'u(=>,2,>,>?DQ?? >BCsd3|S12C
C	 	 @ D	 	r   c                 .   t         j                  j                         5 }|)|j                  t        | |f       |j                         }n)|j                  t        | ||f       |j                         }|r|cddd       S 	 ddd       y# 1 sw Y   yxY w)u+  
    Kontrolliert ob der email oder Telefonummer schon existiert

    Args:
        email String: _description_
        phone String: _description_
        func_row_id (Boolean), optional): Row id muss keine value haben. Defaults to None.

    Returns:
        dict: Gibt dict zurück mit daten
    N)r   r   r	   r
   CHECK_DUPLICATE_INSERT_QUERYrW   CHECK_DUPLICATE_EDIT_QUERY)emailphoner   r	   r3   s        r   check_duplicater     s     
			 	 	" 
f?NN7%H??$DNN5uW7MN??$D
 
 
 
 
s   ABBBc                 (   t         j                  j                         5 }|du r|j                  d| z   dz          n|j                  d| z          |j	                         }|D cg c]  }|d   ||   d }}|cddd       S c c}w # 1 sw Y   yxY w)   
    Vorbereitung daten für FK Tabellen für Dropdown liste

    Args:
        table_name String: Tabellen name
        description_pos int: welche Spalte steht Beschreibung

    Returns:
        List: Tabelle mit id und beschreibung
    TzSELECT * FROM z WHERE igno = 0r   )idr   N)r   r   r	   r
   r   )
table_namedescription_posignorer	   
data_fetchitemr!   s          r   get_data_from_fk_tabler     s     
			 	 	" fT>NN+j8;LLMNN+j89__&
XbcPTT!WT/5JKc
c  d s   AB$B6BBBc           
      L   t         j                  j                         5 }d|  d}|j                  |       |j                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|cddd       S c c}w c c}w # 1 sw Y   yxY w)r   zSELECT * FROM `z`;r   Nr   r   r	   r
   r   r   r   r   )r   r	   queryr   r:   r   r    r!   s           r   get_all_data_from_fk_tabler     s     
			 	 	" f!*R0u (.'9'9:t47::__&
 :DD#d3w,-D
D 
 ; E s)   &BBB'BB
BB#c            
      D   t         j                  j                         5 } d}| j                  |       | j                  D cg c]  }|d   	 }}| j                         }|D cg c]  }t        t        ||             }}|cddd       S c c}w c c}w # 1 sw Y   yxY w)a  
    Prepares data for foreign key tables for dropdown lists.

    Returns:
        List[dict]: A list of dictionaries where each dictionary represents a row with user_id, user_name,
                    region_name, and office information, sorted alphabetically by user_name.
    ae  
            SELECT user_id, user_name, users_region.region AS region_name,
                   CASE WHEN users.region = 36 AND users.status_work != 10 THEN 1 ELSE 0 END AS office
            FROM users
            LEFT JOIN users_region ON users_region.id = users.region
            WHERE users.family_member = 0
            ORDER BY user_name ASC;
        r   Nr   )r	   r   r   r:   r   r    r!   s          r   get_userid_usernamer     s     
			 	 	" f 	u (.'9'9:t47::__&
 :DD#d3w,-D
D)  ; E% s)   "BBB#B?B
BBc                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}|cddd       S c c}w # 1 sw Y   yxY wzC
    Erhalte Columnamen

    Returns:
        List: Columnamen
    r   N)r   r   r	   r
   INSERT_QUERYr   r	   r   r   s      r   get_insert_descriptionr     sa     
			 	 	" f|$,2,>,>?DQ?? ?    $A!AA!A!!A*c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}|cddd       S c c}w # 1 sw Y   yxY wr   )r   r   r	   r
   INSERT_ADMINISTRATION_QUERYr   r   s      r   %get_insert_administration_descriptionr   &  sb     
			 	 	" f23,2,>,>?DQ?? ? r   c                     t         j                  j                         5 }d|  d| d}|j                  ||       t         j                  j	                          ddd       y# 1 sw Y   yxY w)   
    Inserten von der Daten

    Args:
        keys String: Columnamen
        placeholders String: Platzhalter für die Datem
        converted_values Dict: Die Daten
    zINSERT INTO users (
) VALUES ()Nr   r   r	   r
   r   keysplaceholdersvaluesr	   insert_statements        r   insert_data_dbr   2  sc     
			 	 	" "f0jaP'/!" " "r   c                     t         j                  j                         5 }d|  d| d}|j                  ||       t         j                  j	                          ddd       y# 1 sw Y   yxY w)r   z)INSERT INTO talentsphere_administration (r   r   Nr   r   s        r   insert_data_db_administrationr   A  sf     
			 	 	" "fFtfJWcVddef'/!" " "r   c                    t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]2  }t        t        |            D ci c]  }||   ||   ||   nd c}4 }}}|cddd       S c c}w c c}w c c}}w # 1 sw Y   yxY wau  
    Retrieves column names and data based on the row ID.

    Args:
        user_id (str): The ID of the user to fetch data for.

    Returns:
        list[Dict]: A list containing a dictionary for the specific row, 
                    with column names as keys and data as values. If a data value is None,
                    it is replaced with an empty string ''.
    r   Nrs   )	r   r   r	   r
   
EDIT_QUERYr   r   ru   rv   rw   s           r   get_user_data_for_editr   O  s     
			 	 	" 
fzG:.,2,>,>?DQ?? 
 !
 Y^^abn^oXpqST\!_hqk.Ex{2Mq
 
 
 
?
 r

 
rz   c                    t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]2  }t        t        |            D ci c]  }||   ||   ||   nd c}4 }}}|cddd       S c c}w c c}w c c}}w # 1 sw Y   yxY wr   )	r   r   r	   r
   EDIT_ADMINISTRATION_QUERYr   r   ru   rv   )r=   r	   r   r   r   r>   rx   r3   s           r   $get_administration_row_data_for_editr   h  s     
			 	 	" 
f07*=,2,>,>?DQ?? 
 !
 Y^^abn^oXpqST\!_hqk.Ex{2Mq
 
 
 
?
 r

 
rz   c                 h   i }t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }g }|D ]d  }i }t        ||      D ]@  \  }	}
t        |
t              r|
j                  d      ||	<   +|
d||	<   3t        |
      ||	<   B |j                  |       f |D ]3  }|d   dk(  r|d   dz   |d   z   dz   d	z   |d
<   #|d   dz   |d   z   |d
<   5 ||d<   |j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }g }|D ]  }i }t        ||      D ]V  \  }	}
t        |
t              r|
j                  d      ||	<   +|
d||	<   3|	dk(  r|
t        |
      ||	<   It        |
      ||	<   X d|d<   |j                  |        ||d<   |j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }g }|D ]d  }i }t        ||      D ]@  \  }	}
t        |
t              r|
j                  d      ||	<   +|
d||	<   3t        |
      ||	<   B |j                  |       f ||d<   ddd       |S c c}w c c}w c c}w # 1 sw Y   |S xY w)a  
    Fetch column names and data for a specific row ID from two separate queries, converting all values to string format,
    except for `None` which remains unmodified. Child data is stored in separate dictionaries with an additional key 'child': True.

    Args:
        user_id (str): The ID of the user to retrieve data for.

    Returns:
        dict: A dictionary containing two keys: 'user_data' and 'children_data'. 'user_data' is a list of dictionaries
              with column names as keys and corresponding data as strings. 'children_data' is a list of dictionaries,
              each containing child data with an additional key 'child': True.
    r   %Y-%m-%d %H:%M:%SNable_to_drive0
first_name 	last_namez(DNF)	user_name	user_datachild_allowancesTchildchildren_datapartner_data)r   r   r	   r
   r   r   r   r   
isinstancer   strftimerM   appendSELECT_CHILDREN_DATAintSELECT_PARTNER_DATA_COMPARE)r   r!   r	   r   user_column_names	user_rowsr   r>   row_dictcolvalueuserchild_column_names
child_rowsr   partner_column_namespartner_rowsr   s                     r    get_user_data_for_edit_as_stringr     s6    J				 	 	" K2fzG:.171C1CDT!WDDOO%	 	! 
	'HH!"3X> /
UeX.$)NN3F$GHSM]$(HSM$'JHSM/ X&
	'  	QDO$+$($6$<tK?P$PSV$VY`$`[!$($6$<tK?P$P[!		Q #,
; 	+gZ8282D2DE$d1gEE__&
 " 	+HH!"4h? 	/
UeX.$)NN3F$GHSM]$(HSM..53D$'JHSM$'JHSM	/ !%HW  *	+" '4
?# 	2WJ?4:4F4FGDQGG( $ 		*HH!"6A /
UeX.$)NN3F$GHSM]$(HSM$'JHSM/ )		* &2
>"WK2Z U E: F4  HuK2Z s8   &J'JCJ'2J>CJ' J"BJ'J''J1c                    t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }g }|D ][  }i }t        ||      D ]7  \  }}	t        |	t              r|	j                  d      ||<   +|	d||<   3|	||<   9 |j                  |       ] |cddd       S c c}w # 1 sw Y   yxY w)au  
    Fetch column names and data for a specific row ID, converting all values to string format, except for `None` which remains unmodified.

    Args:
        user_id (str): The ID of the user to retrieve data for.

    Returns:
        list[dict]: A list of dictionaries where keys are column names and values are the corresponding data as strings, except for `None`.
    r   r   N)r   r   r	   r
   r   r   r   r   r   r   r   r   )
r=   r	   r   r   r   r3   r>   r   r   r   s
             r   *get_administration_data_for_edit_as_stringr     s     
			 	 	" f07*=,2,>,>?DQ??  	"HH!,9 *
UeX.$)NN3F$GHSM]$(HSM$)HSM* KK!	" +  @ s   &CCA5CCCdnew_key	after_keyreturnc                 v    i }d}| j                         D ]  \  }}|||<   ||k(  s|r|||<   d} |s|||<   |S )z
    Return a new dict with (new_key:new_value) inserted immediately after `after_key`.
    If `after_key` doesn't exist, append at the end.
    Preserves order for Python 3.7+.
    FT)items)r   r   	new_valuer   outinsertedkvs           r   _insert_key_afterr     sZ     CH	 1A	>($CLH	
  GJ    c           
         t        |       }t        |       |sg S t        j                  j	                         5 }t        |      D ]  \  }}|j                  d      }d}|t        |      dv r|j                  d      }|rr|j                  t        |f       |j                         }|It        |t        t        f      r|d   }n-t        |t              r|j                  d      }nt        |      }||d<   t        |j!                         D 	
ci c]  \  }	}
|	dk7  s|	|
 c}
}	d|d      ||<    	 ddd       |S c c}
}	w # 1 sw Y   |S xY w)	z
    Uses your get_administration_data_for_edit_as_string(case_id).
    Adds ONLY 'full_address' when concerns_topic in (17, 18) else sets None.
    Ensures 'full_address' is placed immediately after 'user_name' in each row.
    concerns_topicN)1718r   r   full_addressr   )r   )r   printr   r   r	   	enumerategetrM   r
   ADDRESS_BY_USERID_QUERYrW   r   r<   tupler   r   r   )r=   r   currx   r    topic	full_addrr   resr   r   s              r   2get_administration_data_for_edit_with_full_addressr     sE    6g>D	$K	 
			 	 	" co 	FAsGG,-EI SZ<%?''),KK 7'D,,.C%cD%=9(+AI'T2(+(?I(+CI #,C ("%))+E$!Qn1DAE%	DG-	< K F1< Ks$   CED>D>"E>EEc                     t         j                  j                         5 }|j                  t        | f       t         j                  j                          ddd       y# 1 sw Y   yxY w)uj   
    Anhand der id den Row für diesen Applikation Deaktivieren

    Args:
        row_id int: Row ID
    N)r   r   r	   r
   DELETE_QUERYr   )rF   r	   s     r   update_user_deleter   >  sO     
			 	 	" "f|VI.!" " "   6AA'c                     t         j                  j                         5 }|j                  d|  d||gz          t         j                  j	                          ddd       y# 1 sw Y   yxY w)z
    Zeile Updaten mit neue Daten

    Args:
        update_set String: Update string mit Spaltennamen und dazue den Platzhalter
        values String: Die neue Values um zu Updaten
        row_id String: Zeilen ID um die Zeile zu bestimmen
    zUPDATE users SET z WHERE user_id = %sNr   )
update_setr   r   r	   s       r   update_userr   J  sa     
			 	 	" "f*:,6IJ6T[S\K\]!" " "s   9A!!A*c                 :   t         j                  j                         5 }	 t        |      |fz   }|j	                  d|  d|       t         j                  j                          ddd       y# t        $ r}t        d|        Y d}~%d}~ww xY w# 1 sw Y   yxY w)z
    Zeile Updaten mit neue Daten

    Args:
        update_set String: Update string mit Spaltennamen und dazue den Platzhalter
        values List: Die neuen Values um zu Updaten
        row_id String: Zeilen ID um die Zeile zu bestimmen
    z'UPDATE talentsphere_administration SET z WHERE case_id = %sz$Error updating user administration: N)r   r   r	   r   r
   r   	Exceptionr   )r   r   r=   r	   
all_valueses         r   update_user_administrationr  W  s     
			 	 	" >f	>v'3JNNDZLPcdfpq##%> >  	>8<==	>> >s/   BAA--	B6B	B	BBBc                     t         j                  j                         5 }|j                  t        | |f       t         j                  j                          d d d        y # 1 sw Y   y xY wr   )r   r   r	   r
   UPDATE_TOOKAN_ID_QUERYr   )	tookan_idr   r	   s      r   update_tookan_idr  j  sQ    				 	 	" "f-y.BC!" " "   7AA(c                     t         j                  j                         5 }|j                  t        | |f       t         j                  j                          d d d        y # 1 sw Y   y xY wr   )r   r   r	   r
   UPDATE_PLANDAY_ID_QUERYr   )
planday_idr   r	   s      r   update_planday_idr  o  sQ    				 	 	" "f.
7/DE!" " "r  c                     t         j                  j                         5 }d| d|  d| d}|j                  ||f       |j	                         }|r|d   cddd       S 	 ddd       y# 1 sw Y   yxY w)a2  
    Retrieves a specific value from a specified column in a row identified by an ID in a database table.

    Args:
        table_name (str): The name of the table from which to retrieve the data.
        id_column (str): The name of the column that holds the identifier.
        id_value (int or str): The identifier value used to select the specific row.
        target_column (str): The name of the column from which to retrieve the value.

    Returns:
        Any: The value from the specified column of the identified row, or None if no row is found.
    zSELECT `z` FROM `z	` WHERE `z` = %s;r   Nr   r   r	   r
   rW   )r   column_nameid_valuetarget_columnr	   r   rY   s          r   get_value_by_idr  t  s     
			 	 	" f=/*Y{mSZ[uxk* "!9    s   6A)A))A2c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcd d d        S c c}w # 1 sw Y   y xY wr   )r   r   r	   r
   SELECT_NEEDED_INFOr   r   r^   s       r   get_needed_infor    sq    				 	 	" "f)*,2,>,>?DQ?? T!" "?" "r`   c                      t         j                  j                         5 } | j                  t               t        d | j                         D              }|cd d d        S # 1 sw Y   y xY w)Nc              3   &   K   | ]	  }|d      yw)r   N ).0r    s     r   	<genexpr>z"get_all_user_id.<locals>.<genexpr>  s     @sCF@s   )r   r   r	   r
   SELECT_USER_IDsetr   )r	   existing_uidss     r   get_all_user_idr    sQ    				 	 	" f~&@foo.?@@  s   7A  A)c                      t         j                  j                         5 } | j                  t               | j
                  D cg c]  }|d   	 }}| j                         }||fcd d d        S c c}w # 1 sw Y   y xY wr   )r   r   r	   r
   EXPIRED_APPROVALr   r   r^   s       r   get_expired_approval_employeer     sq    				 	 	" "f'(,2,>,>?DQ?? T!" "?" "r`   c           
         t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|D ]E  }|j                         D ]0  \  }}	t        |	t        t        f      s|	j                  d      ||<   2 G |cd d d        S c c}w c c}w # 1 sw Y   y xY wNr   z%Y-%m-%d)r   r   r	   r
   r   r   r   r   r   r   r   r   r   r   
r   r	   r   r:   r   r    r!   r   keyr   s
             r   get_childrens_datar%    s    				 	 	" f+gZ8'-'9'9:t47::__&
 :DD#d3w,-D
D  	;D"jjl ;
UedH%56 %z :DI;	;
  : E /   &C$CC$'C3C$7C$
C$$C-c           
         t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|D cg c]  }t        t        ||             }}|D ]E  }|j                         D ]0  \  }}	t        |	t        t        f      s|	j                  d      ||<   2 G |cd d d        S c c}w c c}w # 1 sw Y   y xY wr"  )r   r   r	   r
   SELECT_CHILDREN_DETAILS_DATAr   r   r   r   r   r   r   r   r   r#  s
             r   get_childrens_details_datar)    s    				 	 	" f3gZ@'-'9'9:t47::__&
 :DD#d3w,-D
D  	;D"jjl ;
UedH%56 %z :DI;	;
  : E r&  c           
      $   t         j                  j                         5 }|j                  t        | f       |j
                  D cg c]  }|d   	 }}|j                         }|s|D ci c]  }|d  c}gcd d d        S |D cg c]  }t        t        ||             }}|D ]E  }|j                         D ]0  \  }	}
t        |
t        t        f      s|
j                  d      ||	<   2 G |cd d d        S c c}w c c}w c c}w # 1 sw Y   y xY wr"  )r   r   r	   r
   SELECT_PARTNER_DATAr   r   r   r   r   r   r   r   r   )r   r	   r   r:   r   r   r    r!   r   r$  r   s              r   get_partner_datar,    s   				 	 	" f*WJ7'-'9'9:t47::__&
 *123S$Y23  :DD#d3w,-D
D  	;D"jjl ;
UedH%56 %z :DI;	;
 # :
 3D sA   &DC7D)
C<3D DD 3DD7DDc                 
   t         j                  j                         5 }|j                  t        | d   | d   | d   | d   | d   | d   | d   f       t         j                  j                          d d d        y # 1 sw Y   y xY w)Nr   r   gender
birth_datenoter   r   )r   r   r	   r
   UPDATE_CHILDREN_DATAr   r3   r	   s     r   update_child_datar3    s    				 	 	" "f+d<.@kARSWX`Sabfgsbtuy  {A  vB  CG  HZ  C[  \`  aj  \k  .m  	n!" " "s   AA99Bc                    t         j                  j                         5 }|D ]j  }d }|d   r|d   r|d   dz   |d   z   }|j                  t        |d   ||d   |d   |d   |d   |d   |d   f       |j                  t
        | |d   f       l t         j                  j                          d d d        y # 1 sw Y   y xY w)	Nr   r   r   r   r.  r/  r0  r   )r   r   r	   r
   INSERT_CHILD_DATAINSERT_FAMILY_CHILD_DATAr   )r   childrens_listr	   children	full_names        r   insert_childerns_datar:    s   				 	 	" "f& 	UHI%(;*?$\2S88K;PP	NN,hy.A)XVbMcemnyez  }E  FN  }O  QY  Zf  Qg  iq  rx  iy  zB  CU  zV  .X  YNN3Why>Q4ST	U 	!" " "s   BB66B?c                 j   t         j                  j                         5 }d }| d   r| d   r| d   dz   | d   z   }|j                  t        ||| d   | d   | d   | d   | d   | d   f       |j                  t
        ||f       t         j                  j                          d d d        y # 1 sw Y   y xY w)Nr   r   r   r.  r/  r0  r   )r   r   r	   r
   r5  INSERT_CHILDREN_FAMILY_DATAr   )r3   r   child_idr	   r9  s        r   insert_child_datar>    s    				 	 	" "f	${"3\*S043DDI((9T,=OPTU`Pabfgobprv  xD  sE  GK  LR  GS  TX  Yk  Tl  *n  	o2GH3FG!" " "s   BB))B2c                     t         j                  j                         5 }|j                  t        | f       t         j                  j                          d d d        y # 1 sw Y   y xY wr   )r   r   r	   r
   DELETE_CHILDREN_FAMILY_DATAr   )r=  r	   s     r   delete_child_datarA    sN    				 	 	" "f2H;?!" " "r   zINSERT INTO users (user_id, first_name, last_name, birth_date,address, ahv_number, approval, other_jobs, gender,family_member) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,%s, 1)c                 D   t         j                  j                         5 }|j                  t        || d   | d   | d   | d   | d   | d   | d   | d   f	       |j                  t
        ||f       t         j                  j                          d d d        y # 1 sw Y   y xY w)	Npartner_first_namepartner_last_namepartner_birth_datepartner_addresspartner_ahv_numberpartner_approvalpartner_other_jobspartner_gender)r   r   r	   r
   INSERT_PARTNER_DATAINSERT_PARTNER_FAMILY_DATAr   )r3   r   
partner_idr	   s       r   insert_partner_dbrN    s    				 	 	" "f*Jt<P7QRVWjRklp  rF  mG  HL  M^  H_  `d  ey  `z  {  @R  {S  TX  Ym  Tn  os  tD  oE  ,G  	H17:2GH!" " "s   A.BBc                    t         j                  j                         5 }|j                  t        | d   | d   | d   | d   | d   | d   | d   | d   | d	   f	       t         j                  j                          d d d        y # 1 sw Y   y xY w)
NrC  rD  rE  rF  rG  rH  rI  rJ  rM  )r   r   r	   r
   UPDATE_PARTNER_DATAr   r2  s     r   update_partner_datarQ    s    				 	 	" "f*D1E,FtL_G`aefza{  }A  BS  }T  UY  Zn  Uo  pt  uG  pH  IM  Nb  Ic  dh  iy  dz  {  @L  {M  ,O  	P!" " "s   ABB
c                     t         j                  j                         5 }|j                  t        | f       t         j                  j                          d d d        y # 1 sw Y   y xY wr   )r   r   r	   r
   DELETE_PARTNER_FAMILY_DATAr   )rM  r	   s     r   delete_partner_datarT    sN    				 	 	" "f1:-@!" " "r   c                     t         j                  j                         5 }d}|j                  || ||f       t         j                  j	                          d d d        y # 1 sw Y   y xY w)NzdINSERT INTO users_external_acc_status (user_id ,name, status, updated_at) VALUES (%s, %s, %s, NOW())r   r   namestatusr	   sqls        r   insert_external_acc_statusrZ    sW    				 	 	" "ftsWdF45!" " "r   c                     t         j                  j                         5 }d}|j                  ||| |f       t         j                  j	                          d d d        y # 1 sw Y   y xY w)NzeUPDATE users_external_acc_status SET status = %s, updated_at = NOW() WHERE user_id = %s AND name = %sr   rV  s        r   update_external_acc_statusr\    sW    				 	 	" "fusVWd45!" " "r   c           	      
   t         j                  j                         5 }d}|j                  || f       |j	                         }|r"|D cg c]  }|d   t        |d         d c}ng cddd       S c c}w # 1 sw Y   yxY w)a  
    Retrieves the external account status for all services for a specific user.

    Args:
        user_id (int): The ID of the user.

    Returns:
        list: A list of dictionaries like:
              [
                  {"name": "tookan", "status": "0"},
                  {"name": "planday", "status": "1"}
              ]
              or an empty list if no entries are found.
    zg
        SELECT name, status
        FROM users_external_acc_status
        WHERE user_id = %s
        r   rB   )rW  rX  N)r   r   r	   r
   r   rM   )r   r	   rY  r   r    s        r   get_external_account_statusr^    s     
			 	 	" 	Yf
 	sWJ' LP4HCQ3s1v;7HVX	Y 	Y I	Y 	Ys   ,A9A4&A94A99Bc                     d|i}t         j                  j                         5 }d}|j                  || t	        j
                  |      f       t         j                  j                          d d d        y # 1 sw Y   y xY w)NemployeeGroupszWINSERT INTO users_last_planday_department (planday_id ,employee_groups) VALUES (%s, %s))r   r   r	   r
   r1   dumpsr   )r   r`  r3   r	   rY  s        r   $insert_last_planday_department_usersrb  6  sh    n-D				 	 	" "fgsWdjj&689!" " "s   AA44A=c                     t         j                  j                         5 }d}|j                  || f       |j	                         }|r|d   nd cd d d        S # 1 sw Y   y xY w)NzWSELECT employee_groups FROM users_last_planday_department WHERE planday_id = %s limit 1r   r  )r   r	   rY  r    s       r   $select_last_planday_department_usersrd  =  sW    				 	 	" 'fgsWJ'oos1v$	' ' 's   .AA )NNr   )F)v__doc__dbr   r1   rD   r   r   csviorV   r]   rb   rf   ri   rt   r   r   r   r   r   r   r   r  r	  rL   rN   r7   r;   rC   r  rl   ro   r  r  r}   r   r0   r   r*   r-   r6  r   r(  r+  r   r1  r5  rK  rP  r<  rL  r@  rS  r%   r   r   r   r   r"   r(   r+   r.   r5   r8   r@   rH   rT   rZ   r_   rc   rg   rj   rm   rp   ry   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rM   r   r   r   r   r  r  r  r  r  r  r   r%  r)  r,  r3  r:  r>  rA  rN  rQ  rT  rZ  r\  r^  rb  rd  r  r   r   <module>ri     s     
 #   ,7 $ ( "  L  T e  <  G  o ; @ O P  K
 `  ^
 s  H 8 "W 4E 4 ." :/ *R  ^ 
/  g  2V - E    !M  t   X  _  v  C    a ^ [ Y  Y E S  "

".,"""""""0,*,0(0>

""20\~D  3 4 "*X	"">&"
"
6""""("
"""
 F ""
"""Y6"'r   