
    KVh:                        d dl Z d dlZd dlmZ d dlmZ d dlmZmZmZm	Z	m
Z
mZmZmZ e j                  j                  e j                  j!                  e            Ze j                  j'                  ed      Ze j                  j'                  ed      Zh dZd Zd	 Zdd
Zd ZdedefdZd Zedk(  r e       Z e jC                  ddd       yy)    N)datetime)
guess_type)Flaskgrender_templaterequestredirecturl_forflashsend_from_directoryzbugtracker.dbuploads>   bmpgifjpgpdfpngjpegwebpc                  :    t        j                  t        d       y )NT)exist_ok)osmakedirsUPLOAD_FOLDER     =C:\Users\Technik\Desktop\temp\_Kunden\_WOW\Bug-Tracker\app.pyensure_dirsr      s    KK-r   c                      t        t        dd       } | Jt        j                  t              x} t        _        t        j                  | _        | j                  d       | S )N_dbPRAGMA foreign_keys = ON;)	getattrr   sqlite3connectDB_PATHr   Rowrow_factoryexecute)dbs    r   get_dbr)      sG    	E4	 B	z__W--QU 


./Ir   c                 L    t        t        dd       }||j                          y y )Nr   )r!   r   close)er(   s     r   close_dbr-      s#    	E4	 B	~

 r   c                  @   t        j                  t              } | j                  d       | j	                         }|j                  d       |j                  d       |j                  d       |j                  d       | j                          | j                          y )Nr    z
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            created_at TEXT NOT NULL
        );
        a  
        CREATE TABLE IF NOT EXISTS tickets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            status TEXT NOT NULL DEFAULT 'open',
            priority TEXT NOT NULL DEFAULT 'normal',
            reporter TEXT,
            assignee TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
        );
        a|  
        CREATE TABLE IF NOT EXISTS attachments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            stored_name TEXT NOT NULL,
            content_type TEXT,
            uploaded_at TEXT NOT NULL,
            FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
        );
        al  
        CREATE TABLE IF NOT EXISTS activities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER NOT NULL,
            type TEXT NOT NULL,
            author TEXT,
            message TEXT NOT NULL,
            created_at TEXT NOT NULL,
            FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
        );
        )r"   r#   r$   r'   cursorcommitr+   )r(   curs     r   init_dbr2   "   s    		!BJJ*+
))+CKK		 KK	" KK
	 KK
	 IIKHHJr   filenamereturnc                 `    d| v xr) | j                  dd      d   j                         t        v S )N.   )rsplitlowerALLOWED_EXTENSIONSr3   s    r   allowed_filer<   _   s0    (?WxsA6q9??AEWWWr   c                  ,   t                t                t        t              } t        j
                  j                  dd      | j                  d<   t        | j                  d<   | j                  d        }| j                  d      d        }| j                  ddd	g
      d        }| j                  d      dt        fd       }| j                  d      d        }| j                  ddd	g
      d        }| j                  ddd	g
      dt        fd       }| j                  dd	g
      dt        fd       }| j                  dd	g
      dt        fd       }	| j                  dd	g
      dt        fd       }
| j                  d      d        }| S )N
SECRET_KEYzdev-secret-key-change-mer   c                     t                y N)r-   )	exceptions    r   teardown_dbzcreate_app.<locals>.teardown_dbk   s    
r   /c                  *    t        t        d            S )Nlist_tickets)r	   r
   r   r   r   homezcreate_app.<locals>.homeo   s    /00r   z	/projectsGETPOST)methodsc                  4   t               } t        j                  dk(  rt        j                  j	                  dd      j                         }t        j                  j	                  dd      j                         }|st        dd       ne| j                  d||t        j                         j                         f       | j                          t        dd	       t        t        d
            S | j                  d      j                         }t        d|      S )NrH   name descriptionz!Projektname darf nicht leer sein.dangerzEINSERT INTO projects (name, description, created_at) VALUES (?, ?, ?)zProjekt erstellt.successlist_projectsz/SELECT * FROM projects ORDER BY created_at DESCzprojects_list.htmlprojects)r)   r   methodformgetstripr   r'   r   utcnow	isoformatr0   r	   r
   fetchallr   )r(   rK   rM   rR   s       r   rP   z!create_app.<locals>.list_projectst   s    X>>V#<<##FB/557D!,,**="=CCEK98D

[;(9(C(C(EF 		)95 899::OPYY[3hGGr   z/projects/<int:project_id>
project_idc                     t               }|j                  d| f      j                         }| t        dd       t	        t        d            S |j                  d| f      j                         }t        d||      S )Nz!SELECT * FROM projects WHERE id=?zProjekt nicht gefunden.warningrP   zASELECT * FROM tickets WHERE project_id=? ORDER BY created_at DESCzproject_detail.html)projecttickets)r)   r'   fetchoner   r	   r
   rY   r   )rZ   r(   r]   r^   s       r   project_detailz"create_app.<locals>.project_detail   sy    X**@:-PYY[?+Y7GO455**OM
 (* 	 4gwWWr   z/ticketsc            	         t               } t        j                  j                  d      }t        j                  j                  dd      j	                         }t        j                  j                  d      }t        j                  j                  d      }dg}g }g }|r"|j                  d       |j                  |       |r*|j                  d       d	| d	}|j                  ||g       h d
}	||	v r"|j                  d       |j                  |       h d}
||
v r"|j                  d       |j                  |       |r#|j                  ddj                  |      z          |j                  d       dj                  |      }| j                  |t        |            j                         }| j                  d      j                         }t        d||||||      S )NrZ   qrL   statuspriorityzVSELECT t.*, p.name as project_name FROM tickets t JOIN projects p ON t.project_id=p.idzt.project_id = ?z((t.title LIKE ? OR t.description LIKE ?)%>   openclosedin_progresszt.status = ?>   lowhighnormalurgentzt.priority = ?zWHERE z AND zORDER BY t.created_at DESC
(SELECT * FROM projects ORDER BY name ASCztickets_list.html)r^   rR   selected_projectrb   selected_statusselected_priority)r)   r   argsrU   rV   appendextendjoinr'   tuplerY   r   )r(   rZ   rb   rc   rd   sqlwhereparamslikeallowed_statusallowed_priorityqueryr^   rR   s                 r   rE   z create_app.<locals>.list_tickets   s   X\\%%l3
LLS"%++-!!(+<<##J/ e
 LL+,MM*%LLCDqc8DMM4,':^#LL(MM&!>''LL)*MM(#JJx',,u"556

/0		#**UE&M2;;=::HIRRT'"&
 	
r   z/tickets/newc                  |   t               } | j                  d      j                         }|s t        dd       t	        t        d            S t        j                  dk(  rt        j                  j                  dd      j                         }t        j                  j                  dd      j                         }t        j                  j                  d	      }t        j                  j                  d
d      }t        j                  j                  dd      }t        j                  j                  dd      j                         }t        j                  j                  dd      j                         }|r|st        dd       nt        j                         j                         }	| j                  d||||||||	|	f	      }
| j                          |
j                  }| j                  d|d|xs d d| |	f       | j                          t        dd       t	        t        d|            S t!        d|      S )Nrn   z!Bitte zuerst ein Projekt anlegen.inforP   rH   titlerL   rM   rZ   rc   rf   rd   rk   reporterassigneez%Titel und Projekt sind Pflichtfelder.rN   z
                    INSERT INTO tickets (project_id, title, description, status, priority, reporter, assignee, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    \INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)ticket_createzTicket erstellt: zTicket erstellt.rO   ticket_detail	ticket_idzticket_new.htmlrQ   )r)   r'   rY   r   r	   r
   r   rS   rT   rU   rV   r   rW   rX   r0   	lastrowidr   )r(   rR   r   rM   rZ   rc   rd   r   r   nowr1   r   s               r   
new_ticketzcreate_app.<locals>.new_ticket   s   X::HIRRT5v>GO455>>V#LL$$Wb1779E!,,**="=CCEK )),7J\\%%h7F||''
H=H||''
B7==?H||''
B7==?H
=xHoo'113jj  VXxQY[^`cd 		MM	

r1ATEVW\V]C^`cd 		()49 MNN08DDr   z/tickets/<int:ticket_id>r   c                    t               }|j                  d| f      j                         }| t        dd       t	        t        d            S t        j                  dk(  r\|d   }|d   }|d   }t        j                  j                  d|      xs |}t        j                  j                  d|      xs |}t        j                  j                  d|      xs |}t        j                         j                         }	|j                  d	||||	| f       ||k7  r|j                  d
| dd d| d| |	f       ||k7  r|j                  d
| dd d| d| |	f       |xs d|xs dk7  r%|j                  d
| dd d|xs d d|xs d |	f       |j                          t        dd       t	        t        d|             S |j                  d| f      j                         }
|j                  d| f      j                         }t        d||
|      S )NzcSELECT t.*, p.name as project_name FROM tickets t JOIN projects p ON t.project_id=p.id WHERE t.id=?Ticket nicht gefunden.r\   rE   rH   rc   rd   r   zLUPDATE tickets SET status=?, priority=?, assignee=?, updated_at=? WHERE id=?r   status_changezStatus: u    → priority_changeu   Priorität: rL   assignee_changeu   Zuständig: u   –zTicket aktualisiert.rO   r   r   zESELECT * FROM attachments WHERE ticket_id=? ORDER BY uploaded_at DESCzLSELECT * FROM activities WHERE ticket_id=? ORDER BY created_at DESC, id DESCzticket_detail.html)ticketattachments
activities)r)   r'   r_   r   r	   r
   r   rS   rT   rU   r   rW   rX   r0   rY   r   )r   r(   r   
old_statusold_priorityold_assigneerc   rd   r   r   r   r   s               r   r   z!create_app.<locals>.ticket_detail   s=   XqL
 (* 	 >*I6GN344>>V#)J!*-L!*-L\\%%h
;IzF||''
LAQ\H||''
LAQ\H//#--/CJJ^8S)<
 #

r*USYRZ7[]`a <'

r 14<~UZ[cZd9egjk BL$6B7

r 14<H]X]G^^cdldupucv9wy|}
 IIK()4GOyIJJjjSL
 (* 	 ZZZL
 (* 	 3FP[hrssr   z/tickets/<int:ticket_id>/uploadc           	         t               }|j                  d| f      j                         }|s t        dd       t	        t        d            S dt        j                  vr"t        dd       t	        t        d| 	            S t        j                  d   }|r|j                  rt        |j                        rt        j                  j                  |j                        }|j                  d
d      d   j                         }t        j                  j                  |      d   j!                  dd      d d }d|  dt#        t%        j&                         j)                                d| d
| }t        j                  j+                  t,        |      }|j/                  |       t1        |      d   }	|j                  d| |||	t%        j&                         j3                         f       |j                  d| dd d| t%        j&                         j3                         f       |j5                          t        dd       nt        dd       t	        t        d| 	            S )N!SELECT id FROM tickets WHERE id=?r   r\   rE   fileu   Keine Datei ausgewählt.rN   r   r   r6   r7   r    _<   tzlINSERT INTO attachments (ticket_id, filename, stored_name, content_type, uploaded_at) VALUES (?, ?, ?, ?, ?)r   attachment_addzAnhang hochgeladen: zDatei hochgeladen.rO   u"   Ungültige Datei oder Dateiendung.)r)   r'   r_   r   r	   r
   r   filesr3   r<   r   pathbasenamer8   r9   splitextreplaceintr   rW   	timestampru   r   saver   rX   r0   )
r   r(   rowr   origext	safe_basestored_namer   content_types
             r   upload_attachmentz%create_app.<locals>.upload_attachment'  s   Xjj<ylKTTV*I6GN344&,h7GOyIJJ}}V$DMMl4==&A77##DMM2D++c1%b)//1C((.q199#sCCRHIi[#hoo.?.I.I.K*L)MQykYZ[^Z_`K77<<{;DIIdO%d+A.LJJ~D+|X__=N=X=X=Z[ JJn,d6J4&4QS[SbSbSdSnSnSpq IIK&	26A9EFFr   z'/attachments/<int:attachment_id>/deleteattachment_idc           	      >   t               }|j                  d| f      j                         }|s t        dd       t	        t        d            S |d   }	 t        j                  t        j                  j                  t        |d                |j                  d| f       |j                  d|d	d d
|d    t        j                         j                         f       |j                          t        dd       t	        t        d|            S # t        $ r Y w xY w)Nz$SELECT * FROM attachments WHERE id=?zAnhang nicht gefunden.r\   rE   r   r   z"DELETE FROM attachments WHERE id=?r   attachment_deleteu   Anhang gelöscht: r3   u   Anhang gelöscht.rO   r   r   )r)   r'   r_   r   r	   r
   r   remover   ru   r   FileNotFoundErrorr   rW   rX   r0   )r   r(   attr   s       r   delete_attachmentz%create_app.<locals>.delete_attachmentJ  s   Xjj?-AQR[[]*I6GN344$		IIbggll=#m2DEF 	

7-9IJ


j+T5GJGX3Y[c[j[j[l[v[v[xy	
 			!9-9EFF ! 		s   :D 	DDz /tickets/<int:ticket_id>/commentc           	      v   t               }|j                  d| f      j                         }|s t        dd       t	        t        d            S t        j                  j                  dd      j                         xs d }t        j                  j                  dd      j                         }|s"t        dd	       t	        t        d
|             S |j                  d| d||t        j                         j                         f       |j                          t        dd       t	        t        d
|             S )Nr   r   r\   rE   authorrL   contentzKommentar darf nicht leer sein.rN   r   r   r   commentu   Kommentar hinzugefügt.rO   )r)   r'   r_   r   r	   r
   r   rT   rU   rV   r   rW   rX   r0   )r   r(   r   r   r   s        r   add_commentzcreate_app.<locals>.add_comment_  s    X?)NWWY*I6GN344!!(B/557?4,,""9b17793X>GOyIJJ


j	67HOO4E4O4O4QR	
 			'39EFFr   z/uploads/<path:filename>c                 &    t        t        | d      S )NF)as_attachment)r   r   r;   s    r   uploaded_filez!create_app.<locals>.uploaded_files  s    "=(%PPr   )r   r2   r   __name__r   environrU   configr   teardown_appcontextrouter   )apprB   rF   rP   r`   rE   r   r   r   r   r   r   s               r   
create_appr   c   s   MI
/C!zz~~l<VWCJJ|"/CJJ  	YYs^1 1 	YY{UFOY4H 5H$ 	YY+,
X3 
X -
X 	YYz/
 /
b 	YY~vY7$E 8$EL 	YY)E6?YC6t 6t D6tp 	YY06(YC GS  G D GD 	YY86(YKG G LG( 	YY1F8YDGs G EG& 	YY)*Q +Q Jr   __main__Tz	127.0.0.1i  )debughostportr@   )"r   r"   r   	mimetypesr   flaskr   r   r   r   r	   r
   r   r   r   dirnameabspath__file__BASE_DIRru   r$   r   r:   r   r)   r-   r2   strboolr<   r   r   r   runr   r   r   <module>r      s    	     c c c 77??277??845
'',,x
1Xy1H .:zX3 X4 XTn z
,CGG$[tG4 r   