^h<dZddlmZddlZddlZddlZddlmZmZdZ dZ dZ dZ d Z dd Zd Zd Zd ZdZdZdZdZdZdZdZdZdZdZddlZdZdZdZy)u 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)datetimedatecd}tjj5}|j||f|j }|cdddS#1swYyxYw)Nzselect first_name, last_name, logins.passwd from users left join logins on logins.user_id = users.id left join users_contact on users_contact.user_id = users.id where users_contact.company_email = %sr connectioncursorexecutefetchone)email LOGIN_QUERYr results /var/www/html/crm/models.pyget_login_userrsR\K    "f{E8,"s %AAcd}tjj5}|j||jDcgc]}|d }}|j }||fcdddScc}w#1swYyxYw)Nzselect company.id , company.name, company_industry.name as industry, prefix, phone, email, address, postal_code, location, country from company left join company_industry on company.industry = company_industry.id rrrr r descriptionfetchallTABLE_DATA_QUERYr desc column_namesrowss rget_company_view_datars}o    ""f'(,2,>,>?DQ? ? T! ""@"" A1 A, A1,A11A:cd}tjj5}|j||jDcgc]}|d }}|j }||fcdddScc}w#1swYyxYw)NaSELECT customer_interactions.id, customer_interactions.date, customer_interactions.follow_up, company.name as company_name, company_contact_person.name as contact_person,communication_method.name as communication_method, customer_interactions.note FROM `customer_interactions` left join communication_method on communication_method.id = customer_interactions.communicated_over LEFT JOIN company on company.id = customer_interactions.company LEFT JOIN company_contact_person on company_contact_person.id = customer_interactions.company_user;rrrs r#get_customer_interactions_view_datar s}w    ""f'(,2,>,>?DQ? ? T! ""@""rc tjj5}|jd|f|jDcgc]}|d }}|j }|Dcgc]2}t t|Dcic]}||||||ndc}4}}}|cdddScc}wcc}wcc}}w#1swYyxYw)NzSELECT `id`, `date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note` FROM `customer_interactions` WHERE id = %srrrr r rrrangelen)row_idr rcompany_column_names company_rowsrow_dataicustomer_interactionss r"get_customer_interactions_for_editr)+s    " %f]`f_h i4:4F4FGDQGG( )! affij~faA B[\ !! $hqk6Mx{SU U B! ! % % %G B! % %sA"B9 B) B9$B3?B.B3B9)B9.B33B99Cc tjj5}|jd|f|jDcgc]}|d }}|j }|Dcgc]2}t t|Dcic]}||||||ndc}4}}}|jd|f|jDcgc]}|d }}|j } | Dcgc]2}t t|Dcic]}||||||ndc}4} }}|| fcdddScc}wcc}wcc}}wcc}wcc}wcc}}w#1swYyxYw)av 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 ''. z{select id ,name, prefix, phone, email, address, postal_code, location, country, industry, status from company where id = %srNrztselect id ,name, prefix, phone, job_title, email, job_title, phone from company_contact_person where company_id = %sr ) r#r rr$r%r&r' company_data#company_contact_person_column_namescompany_contact_person_rowscompany_contact_person_datas rget_company_data_for_editr/9s    "9fUX^W` a4:4F4FGDQGG( ) affij~faA B[\ !! $hqk6Mx{SU U B  NQWPY ZCICUCU.V4tAw.V+.V&,oo&7#8' puuxz]v^p_ `jk 0 3Xa[E\hqkbd d `' #' 88)99G B /W `' 99sq"E  D) E $D3?D.D3$E ? D9 E "E=D>EE )E .D33 E >EE  Ec(tjj5}|dur|jd|zdzn|jd|z|j }|Dcgc] }|d||d}}|cdddScc}w#1swYyxYw)u 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 TzSELECT * FROM z WHERE igno = 0ridrNrrr r r) table_namedescription_posignorer data_fetchitem final_datas rget_data_from_fk_tabler:\s    "f T> NN+j8;LL M NN+j8 9__& XbcPTT!WT/5JKc c d sAB$B6BBBctjj5}|jd||f|j }|r |cdddS dddy#1swYyxYw)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 z2SELECT * FROM users WHERE email = %s OR phone = %sNr)r phoner datas rcheck_duplicater>psb    "fKeUZ^\   s(AAA$ctjj5}|jd|d||gztjj dddy#1swYyxYw) 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 zUPDATE company SET  WHERE id = %sNrrr r commit update_setvalues company_idr s rupdate_companyrHs`    ""f,ZLGQ[P\H\] !""" 9A!!A*ctjj5}|jd|d||gztjj dddy#1swYyxYw)r@z!UPDATE customer_interactions SET rANrBrDs rupdate_customer_interactionsrKsa    ""f::,nUV\_i^jVjk !"""rIc tjj5}d}|jd|jd|jd|jd|jd|jd|jdf}|j ||tjj |j cd d d S#1swYy xYw) 3 Fügt eine neue Firma in die company-Tabelle ein und gibt die neue ID zurück. Args: company_data (dict): Dictionary mit den Schlüsseln: 'name', 'email', 'prefix', 'phone', 'address', 'postal_code', 'industry' Returns: int: Die ID der neu eingefügten Firma. z INSERT INTO company (name, email, prefix, phone, address, postal_code, industry) VALUES (%s, %s, %s, %s, %s, %s, %s) namer prefixr<address postal_codeindustryNrrr getr rC lastrowidr+r sqlrFs rinsert_company_and_get_idrXs    " f    V $   W %   X &   W %   Y '   ] +   Z (  sF# !!   s B0CC"c tjj5}d}|jd|jd|jd|jd|jd|jdf}|j ||tjj |j cdddS#1swYyxYw) rMz INSERT INTO customer_interactions (`date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note`) VALUES (%s, %s, %s, %s, %s, %s) r follow_upcommunicated_overcompany company_usernoteNrSrVs r'insert_customer_interactions_and_get_idr_s    " f    V $   [ )   0 1   Y '   ^ ,   V $   sF# !   s B C  Cctjj5}|jd|j }t j |dd}|cdddS#1swYyxYw)Nz>select json from credentials where name = 'google_service_acc'r)rrr r rjsonloads)r r= cred_jsons rget_cred_google_service_accrds]    "fWX JJtAwqz*  s >A''A0ctjj5}|jd|jDcgc]}|d }}|cdddScc}w#1swYyxYw)C Erhalte Columnamen Returns: List: Columnamen zSselect id ,name, phone, email, address, postal_code, location, country from companyrNrrr r rr rrs rget_insert_descriptionrisb    "flm,2,>,>?DQ? ?? A A AAA&ctjj5}|jd|jDcgc]}|d }}|cdddScc}w#1swYyxYw)rfzvSELECT `id`, `date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note` FROM `customer_interactions` rNrgrhs r,get_insert_description_customer_interactionsrlsh    "fP Q,2,>,>?DQ? ??rjctjj5}|jd|d||gztjj dddy#1swYyxYw)a Updates a contact person with the provided values. Args: update_set (str): SQL SET clause, e.g., "name = %s, email = %s" values (list): List of values to substitute in the SQL query. person_id (str): The ID of the contact person to update. z"UPDATE company_contact_person SET rANrB)rErF person_idr s rupdate_contact_personrosa    ""f;J<~VX^bkalXlm !"""rIc tjj5}|jd|d|d|d|d|dftjj dddy#1swYyxYw)z Inserts a new contact person into the database. Args: data (dict): A dictionary containing 'name', 'email', 'phone', 'job_title', and 'company_id'. z INSERT INTO company_contact_person (name, email, phone, job_title, company_id) VALUES (%s, %s, %s, %s, %s) rNr r< job_titlerGNrB)r=r s rinsert_contact_personrrsz    ""f 6lDM4=${:KTR^M_ ` b ! """s AA--A6ctjj5}d}|j|||ftjj dddy#1swYyxYw)Nz/UPDATE company SET folder_id = %s WHERE id = %srB) google_urlrGr rWs rupdate_google_urlrusU    ""f?sZ45 !"""s 5AA&c ztjjtjj 5}|j d|f|jxsi}|j d|f|j}||Dcic]}t|d|c}dcdddScc}w#1swYyxYw)Na SELECT company.name as name, company_industry.name as industry , company_status.name as status, prefix, phone, email, address, postal_code, location, country, note FROM company left join company_industry on company_industry.id = company.industry left join company_status on company_status.id = company.status WHERE company.id = %s z SELECT id, name, email, prefix, phone, job_title FROM company_contact_person WHERE company_id = %s r2)r\contacts) rrr MySQLdbcursors DictCursorr r rstr)rGr r\rwcs rget_company_by_id_for_logr}s    !;!; <  ]   //#)r  ]   ??$2:;QQtWq; %  (<)  sAB1B,B1,B11B:ctjjtjj 5}|j d|f|jxsi}d|icdddS#1swYyxYw)Na SELECT date, follow_up, communication_method.name as communicated_over, company.name as company,company_contact_person.name as company_user, customer_interactions.note FROM customer_interactions left join communication_method on communication_method.id = customer_interactions.communicated_over left join company on company.id = customer_interactions.company left join company_contact_person on customer_interactions.company_user = company_contact_person.id WHERE customer_interactions.id = %s r()rrr rxryrzr r )customer_interactions_idr r(s r'get_customer_interactions_by_id_for_logr0sq    !;!; <   ' (  * !' 1 7R $%:     s +A--A6c d|jds#|jds|jdsytjj5}|j d|||t j |dftjjdddy#1swYyxYw)Nr\rwr(z INSERT INTO audit_log (table_name, record_id, changed_by, changed_at, change_summary) VALUES (%s, %s, %s, NOW(), %s) F) ensure_ascii)rTrrr r radumpsrC)r4 record_iduserchangesr s rinsert_audit_logrAs ;;y !'++j*A'++VmJn    " "f     JJwU 3    ! " " "s A B&&B/cVtjjtjj 5}|j d||f|j}|D]} tj|d|d< |cdddS#t$ri|d<YB BBBBB(ctjj5}|jd|f|j }|Dcgc] }|d|dd}}|cdddScc}w#1swYyxYw)u Bereitet Daten für Dropdowns mit Namen + zugehörigem Firmennamen vor. Returns: List[dict]: Liste mit {'id': ..., 'description': ...} aZ SELECT company_contact_person.id, company_contact_person.name FROM company_contact_person LEFT JOIN company ON company.id = company_contact_person.company_id WHERE company_contact_person.company_id = %s rr1Nr3)r#r r=rowr9s r-get_customer_contact_person_with_company_namerbs    "f  Y  GKLSVCF;L LMs(A+A&A+&A++A4)F)__doc__dbrMySQLdb.cursorsrxraastrrrrrr)r/r:r>rHrKrXr_rdrirlrorrrur}rrrrrrs  #"" %!9F(* " " : 8   " ""  0   " "r