U ph1@sdZddlmZmZddlmZddlmZddlZddl Z e j e j de e ZeZedeeeZedded Zd d d gd dgddgddgddgdZddddddZddZddZdd Zd!d"Zd#d$Ze d%kred&eedS)'z Script to extract and update project names from window titles This script analyzes activity records and attempts to extract project names from window titles and file paths ) create_enginetext) sessionmaker)ConfigN)levelzConnecting to: F)Z autocommitZ autoflushZbindz+^(.+?)\s*-\s*(.+?)\s*-\s*Visual Studio Codeu2^(.+?)\s*●?\s*-\s*(.+?)\s*-\s*Visual Studio Codez+^\[(.+?)\]\s*(.+?)\s*-\s*Visual Studio Codez^(.+?)\s*-\s*(.+?)\s*-\s*Cursoru&^(.+?)\s*●?\s*-\s*(.+?)\s*-\s*Cursoru ^(.+?)\s*–\s*(.+?)\s*\[(.+?)\]z^(.+?)\s*-\s*PyCharmz^(.+?)\s*-\s*IntelliJ IDEAz%^(.+?)\s*-\s*(.+?)\s*-\s*Sublime Textu'^(.+?)\s*•\s*(.+?)\s*-\s*Sublime TextZvscodeZcursorpycharmintellijZsublimezgithub\.com/([^/]+)/([^/\s]+)zgitlab\.com/([^/]+)/([^/\s]+)z bitbucket\.org/([^/]+)/([^/\s]+)z localhost:\d+z([A-Z]{2,10}-\d+))githubgitlab bitbucket localhostjiracsL|sdS|r|ndtD]\}}|kr |D]}t||}|r4|dkrvt|dkrv|dSt|dkr|dSt|dkr4|dSq4q t fddd DrHt D]`\}}t||tj }|r|d kr|dS|d kr8d |dS|d krdSqdS)z;Extract project name from window title based on applicationN)rr c3s|]}|kVqdSN).0browser app_lowerr./extract_projects.py Msz4extract_project_from_window_title..)ZchromeZfirefoxZedgeZsafariZbraver r r rzJIRA-r zLocal Development) lower IDE_PATTERNSitemsresearchlengroupsgroupstripanyBROWSER_PATTERNS IGNORECASE) window_titleapplication_nameidepatternspatternmatchservicerrr!extract_project_from_window_title7s2   r/c Cs|sdSdddddddd d d d g }|D]&}t||tj}|r&|d Sq&dddddg}|D]\}||kr`|ddd}t|D]0\}}||kr|dkr||d Sqq`dS)z#Extract project name from file pathNz /([^/]+)/src/z /([^/]+)/app/z /([^/]+)/lib/z/([^/]+)/backend/z/([^/]+)/frontend/z/([^/]+)/client/z/([^/]+)/server/z\\([^\\]+)\\src\\z\\([^\\]+)\\app\\z\\([^\\]+)\\backend\\z\\([^\\]+)\\frontend\\rz package.jsonzpom.xmlz build.gradlezrequirements.txtzsetup.py\/r)rr r'r#rreplacesplit enumerate) file_pathZproject_indicatorsr,r-Z common_filesfilepartsipartrrrextract_project_from_file_pathZs4 r:cs|sdS|}|r|nd|r,|ndtfdddDrd|ks^d|ks^d|krbd Sd |kszd |kszd |kr~d Sd|ksd|krdSdStfdddDrdStfdddDrdStfdddDrdStfdddDrdSdS)z'Categorize the project based on context)ZOtherGeneralrc3s|]}|kVqdSrr)rr*rrrrsz%categorize_project..rZbackendZapiserver) DevelopmentZBackendZfrontendZclientZweb)r=ZFrontendZmobileZapp)r=ZMobile)r=r;c3s|]}|kVqdSrr)rdbrrrrs)ZdatagripZpgadminZmysqlZmongodb)ZDatabaseAdministrationc3s|]}|kp|kVqdSrr)rr<rZ title_lowerrrrs)ZpleskZcpanelZsshZputtyZ filezilla)zServer Managementr?c3s|]}|kVqdSrr)rdocrrrrs)ZwordZdocsZnotionZ confluence)Z DocumentationZWritingc3s|]}|kVqdSrr)rZcommrrrrs)ZteamsZslackZzoomZmeet)Z CommunicationZMeeting)ZWorkr;)rr%) project_namer(r)Z project_lowerrr@rcategorize_project~s0rCc Cst}z|td}tdt|dd}i}|D]Z}|\}}}}} d} t||} | st|rtt|} | s| rd|pd krt D]2\} } t | | t j} | r| dkr| d } qq| rB| } t| d kr| dd } t| ||\}}|td | ||d |d 7}| |kr:dttd|| <|| dd 7<|| d||rx|| d||d dkrB|td|dqB|tdtdt|td|tdt||rtdt| dddddd}|D]p\}}td|dtd |dtd!d"|dtd#d"t|ddd$qWn>tk r}ztd%||W5d}~XYnXW5QRXdS)&z:Update activity records with extracted project informationa' SELECT id, window_title, application_name, file_path, url FROM activity_records WHERE project_name IS NULL AND timestamp >= NOW() - INTERVAL '30 days' ORDER BY timestamp DESC LIMIT 5000 zFound z activities to processrNrrrrdz UPDATE activity_records SET project_name = :project_name, project_type = :project_type WHERE id = :id )rB project_typeidr)counttypesappsrGrHrIzUpdated z records...z$ === PROJECT EXTRACTION COMPLETE ===zTotal activities processed: z Activities with projects found: zUnique projects found: z === TOP PROJECTS ===cSs |ddS)NrrGr)xrrrz%update_project_info..T)keyreverse :z Activities: z Types: z, z Apps: rzError updating project info: ) SessionLocalexecuterfetchallloggerinfor!r/r:rr&rrr r'r#r$rCsetaddZcommitsortedjoinlist ExceptionerrorZrollback)r> activitiesupdatedZprojects_foundZactivityZ activity_idr(Zapp_namer5ZurlrBr.r,r-rEZproject_categoryZsorted_projectsprojectrVerrrupdate_project_infos               .rbc Cst}|td}tdtd|dtd|dtd|dd |d|dd d d td |d|td}td|D]D\}}}}}t|d |dtd|d d|d|dqW5QRXdS)z-Get statistics about projects in the databasea SELECT COUNT(DISTINCT project_name) as unique_projects, COUNT(*) as total_activities, COUNT(CASE WHEN project_name IS NOT NULL THEN 1 END) as activities_with_projects, COUNT(DISTINCT developer_id) as developers FROM activity_records WHERE timestamp >= NOW() - INTERVAL '30 days' z === PROJECT STATISTICS ===zUnique projects: rzTotal activities: rzActivities with projects: rz (rDz.1fz%)zActive developers: ra SELECT project_name, project_type, SUM(duration) / 3600.0 as total_hours, COUNT(*) as activity_count, COUNT(DISTINCT developer_id) as developers FROM activity_records WHERE project_name IS NOT NULL AND timestamp >= NOW() - INTERVAL '30 days' GROUP BY project_name, project_type ORDER BY total_hours DESC LIMIT 15 z === TOP PROJECTS BY HOURS ===z):z z hours, z activities, z developersN)rRrSrZfetchonerUrVrT)r>ZstatsZ top_projectsr`ZptypeZhoursr^Zdevsrrrget_project_statisticss . rc__main__z&Starting project extraction process...)__doc__Z sqlalchemyrrZsqlalchemy.ormrZconfigrrZloggingZ basicConfigINFOZ getLogger__name__rUZget_database_urlZ DATABASE_URLprintZenginerRrr&r/r:rCrbrcrVrrrrsV   #$'m)