#!/usr/bin/env python3 """ Sync Jira issues -> MySQL table jira_tasks Requires: pip install requests mysql-connector-python """ import requests import mysql.connector from datetime import datetime, timezone # ========================= # CONFIG # ========================= JIRA_BASE_URL = "https://hydron.atlassian.net" JIRA_EMAIL = "michal@hydron.tech" JIRA_API_TOKEN = "ATATT3xFfGF0ASp4BBzZX1v313ukofMhup7pTahIf31nYFAa5L-Ss6K71naMDE2nziOX5O0P-OBOL1exx4CeNqSdXxbeR2aJW60RzJOwvy1BToN2FUPDGbOnOBGRyCQa7otKCBkEdQirtg0fRQHDYI3A0-tLK-ffb-cu3bFtEv1VcPFpNmm8uLg=35692069" # JQL: PROD, resolved last 2 days, path = Warsztat CNC JIRA_JQL = ( 'project = PROD ' 'AND resolved >= -400d ' 'AND customfield_10132 = "Warsztat CNC" ' 'ORDER BY created DESC' ) MYSQL_CONFIG = { "host": "localhost", "user": "hydronun_ml", "password": "Hydron112-", "database": "hydronun_cnc", "port": 3306 } # Jira custom field IDs (from PROD-7946 /names) CF_TEAM = "customfield_10196" # Zespół (if needed) CF_WAIT_TIME_MATERIAL = "customfield_10146" # Czas oczekiwania na material CF_MILLING_HOURS = "customfield_10174" # Frezowanie (h) CF_QTY_TO_MAKE = "customfield_10137" # Ilosc do zrobienia CF_QTY_ORDERED = "customfield_10120" # Ilość w zamowieniu CF_LINK_GCODE = "customfield_10128" # Link do g-code CF_LINK_CAD_MODEL = "customfield_10176" # Link do modelu CAD CF_LINK_DRAWING = "customfield_10129" # Link do rysunku (PDF/CAD) CF_MATERIAL_DEADLINE = "customfield_10171" # Materiał - termin CF_PLANNED_COMPLETION_DATE = "customfield_10175" # Planowana data zakonczenia produkcji CF_PATH_INFO = "customfield_10132" # Sciezka CF_TURNING_HOURS = "customfield_10173" # Toczenie (h) CF_QC_RESULT = "customfield_10199" # Wynik QC CF_WORK_ORDER_NUMBER = "customfield_10135" # Zlecenie # # ========================= # HELPERS # ========================= def parse_jira_datetime(value): """Parse Jira datetime string -> naive UTC datetime, or None.""" if not value: return None for fmt in ("%Y-%m-%dT%H:%M:%S.%f%z", "%Y-%m-%dT%H:%M:%S%z"): try: dt = datetime.strptime(value, fmt) return dt.astimezone(timezone.utc).replace(tzinfo=None) except ValueError: continue return None def extract_adf_text(adf): """ Extract plain text from Atlassian Document Format (ADF) JSON. Returns a string or None. """ if adf is None: return None parts = [] def walk(node): if isinstance(node, dict): # text node if node.get("type") == "text" and "text" in node: parts.append(node["text"]) # children content = node.get("content") if isinstance(content, list): for child in content: walk(child) elif isinstance(content, dict): walk(content) elif isinstance(node, list): for child in node: walk(child) walk(adf) text = "".join(parts).strip() return text or None def parse_jira_date(value): """Parse Jira date (YYYY-MM-DD) -> date, or None.""" if not value: return None try: return datetime.strptime(value, "%Y-%m-%d").date() except ValueError: return None def to_float(value): if value in (None, "", " "): return None try: return float(value) except (TypeError, ValueError): return None def to_int(value): if value in (None, "", " "): return None try: return int(value) except (TypeError, ValueError): return None def clean_value(value): """Convert Jira values to something MySQL can store.""" if isinstance(value, (dict, list)): return str(value) return value def extract_option_value(field_value): """ Extract human-readable value from a Jira select / multiselect / etc. If it's already a string or number, return as-is. """ if isinstance(field_value, dict): return field_value.get("value") or field_value.get("name") if isinstance(field_value, list) and field_value: first = field_value[0] if isinstance(first, dict): return first.get("value") or first.get("name") return field_value # ========================= # JIRA FETCH # ========================= def fetch_jira_issues(): """Fetch all issues matching JQL using /rest/api/3/search/jql (GET, nextPageToken pagination).""" issues = [] auth = (JIRA_EMAIL, JIRA_API_TOKEN) headers = {"Accept": "application/json"} next_page_token = None while True: params = { "jql": JIRA_JQL, "maxResults": 100, # Jira will cap this internally "fields": "*all", # return all fields (you can restrict later) } if next_page_token: params["nextPageToken"] = next_page_token url = f"{JIRA_BASE_URL}/rest/api/3/search/jql" resp = requests.get(url, headers=headers, params=params, auth=auth) if not resp.ok: print("Jira search error:", resp.status_code, resp.text) resp.raise_for_status() data = resp.json() # New endpoint returns issues + nextPageToken batch = data.get("issues", []) issues.extend(batch) next_page_token = data.get("nextPageToken") if not next_page_token: break return issues # ========================= # MYSQL UPSERT # ========================= def upsert_issue(cursor, issue): fields = issue.get("fields", {}) summary = fields.get("summary") issue_key = issue.get("key") status = (fields.get("status") or {}).get("name") project_key = (fields.get("project") or {}).get("key") priority = (fields.get("priority") or {}).get("name") resolution = (fields.get("resolution") or {}).get("name") created_at = parse_jira_datetime(fields.get("created")) updated_at = parse_jira_datetime(fields.get("updated")) resolved_at = parse_jira_datetime(fields.get("resolutiondate")) due_date = parse_jira_date(fields.get("duedate")) description_raw = fields.get("description") description = extract_adf_text(description_raw) # Custom fields wait_time_material = fields.get(CF_WAIT_TIME_MATERIAL) # text/number; table column is VARCHAR milling_hours = to_float(fields.get(CF_MILLING_HOURS)) qty_to_make = to_int(fields.get(CF_QTY_TO_MAKE)) qty_ordered = to_int(fields.get(CF_QTY_ORDERED)) link_gcode = fields.get(CF_LINK_GCODE) link_cad_model = fields.get(CF_LINK_CAD_MODEL) link_drawing = fields.get(CF_LINK_DRAWING) material_deadline = parse_jira_date(fields.get(CF_MATERIAL_DEADLINE)) planned_completion_date = parse_jira_date(fields.get(CF_PLANNED_COMPLETION_DATE)) path_info = extract_option_value(fields.get(CF_PATH_INFO)) turning_hours = to_float(fields.get(CF_TURNING_HOURS)) qc_result = extract_option_value(fields.get(CF_QC_RESULT)) work_order_number = fields.get(CF_WORK_ORDER_NUMBER) sql = """ INSERT INTO jira_tasks_new ( summary, issue_key, status, project_key, priority, resolution, created_at, updated_at, resolved_at, due_date, description, wait_time_material, milling_hours, qty_to_make, qty_ordered, link_gcode, link_cad_model, link_drawing, material_deadline, planned_completion_date, path_info, turning_hours, qc_result, work_order_number ) VALUES ( %(summary)s, %(issue_key)s, %(status)s, %(project_key)s, %(priority)s, %(resolution)s, %(created_at)s, %(updated_at)s, %(resolved_at)s, %(due_date)s, %(description)s, %(wait_time_material)s, %(milling_hours)s, %(qty_to_make)s, %(qty_ordered)s, %(link_gcode)s, %(link_cad_model)s, %(link_drawing)s, %(material_deadline)s, %(planned_completion_date)s, %(path_info)s, %(turning_hours)s, %(qc_result)s, %(work_order_number)s ) ON DUPLICATE KEY UPDATE summary = VALUES(summary), status = VALUES(status), project_key = VALUES(project_key), priority = VALUES(priority), resolution = VALUES(resolution), created_at = VALUES(created_at), updated_at = VALUES(updated_at), resolved_at = VALUES(resolved_at), due_date = VALUES(due_date), description = VALUES(description), wait_time_material = VALUES(wait_time_material), milling_hours = VALUES(milling_hours), qty_to_make = VALUES(qty_to_make), qty_ordered = VALUES(qty_ordered), link_gcode = VALUES(link_gcode), link_cad_model = VALUES(link_cad_model), link_drawing = VALUES(link_drawing), material_deadline = VALUES(material_deadline), planned_completion_date = VALUES(planned_completion_date), path_info = VALUES(path_info), turning_hours = VALUES(turning_hours), qc_result = VALUES(qc_result), work_order_number = VALUES(work_order_number) """ params = { "summary": summary, "issue_key": issue_key, "status": status, "project_key": project_key, "priority": priority, "resolution": resolution, "created_at": created_at, "updated_at": updated_at, "resolved_at": resolved_at, "due_date": due_date, "description": description, "wait_time_material": wait_time_material, "milling_hours": milling_hours, "qty_to_make": qty_to_make, "qty_ordered": qty_ordered, "link_gcode": link_gcode, "link_cad_model": link_cad_model, "link_drawing": link_drawing, "material_deadline": material_deadline, "planned_completion_date": planned_completion_date, "path_info": path_info, "turning_hours": turning_hours, "qc_result": qc_result, "work_order_number": work_order_number, } # ensure no dict/list is sent directly to MySQL params = {k: clean_value(v) for k, v in params.items()} cursor.execute(sql, params) # ========================= # MAIN # ========================= def main(): issues = fetch_jira_issues() print(f"Fetched {len(issues)} issues from Jira.") if not issues: return conn = mysql.connector.connect(**MYSQL_CONFIG) try: cur = conn.cursor() for idx, issue in enumerate(issues, start=1): upsert_issue(cur, issue) if idx % 50 == 0: conn.commit() print(f"Committed {idx} issues…") conn.commit() print("Done.") finally: conn.close() if __name__ == "__main__": main()