U
    ƢfG                     @   s   d Z d ZdZddlZddlZddlmZ dddZdddZ	dddZ
dd	d
ZdddZdddZdddZdd Zedkre  ed dS )       Nc              
   C   s   | d kr*t d d}t d| t|}nb| dkrt d ztjdddd	d
d}W n8 tjk
r } zt d| td W 5 d }~X Y nX |S )Nu-   Opening and connecting 勤務系統 database.zSERVER=tcp:itrans.database.windows.net,1433;DATABASE=SkyEyesWeb_Itrans;UID=Userv1_itrans@itrans;PWD=Auser@%#$202105;DRIVER={ODBC Driver 17 for SQL Server};Encrypt=yes;TrustServerCertificate=no;z
conn_str:    愛豐系統u-   Opening and connecting 愛豐系統 database.	itranswlcz1tr@nsb1rryz52.163.246.241i  itransdb)userpasswordhostportdatabasez%Error connecting to MariaDB Platform:r   )printpyodbcconnectmariadbErrorsysexit)whichDBZconn_strZcnxne r   '/home/mars/bis/apps/parsefile/dbintf.py	DbConnect"   s(    	


r   c                 C   s.   |d krt d n|dkr"t d |   d S )Nu$   Disconnecting 勤務系統 database.r   u$   Disconnecting 愛豐系統 database.)r   close)connr   r   r   r   DbDisconnectK   s    
r   c           
      C   s   t d d| d | d }|rd| d | d }t|}d}|D ]:}|d7 }||k rj|d | d	 }qD|d | d
 } qqD| | g }| D ]}	||	 q|S )Nu$   Querying 勤務系統: 排班資料.z.SELECT * FROM V_EmpSchedule WHERE ES_Date >= 'z' AND ES_Date <= ''z' AND (r   r   
Emp_name='' OR ')r   lenexecuteappend)
cursor	startDateendDatedriverNamessqlStmtnDriversidrvName
listResultrowr   r   r   DbQueryDriversVacationm   s"    
r,   c                 C   s   t d d}|r\d}t|}d}|D ]6}|d7 }||k rJ|d | d }q$|d | d }q$| | g }| D ]}|| qn|S )	Nu$   Querying 勤務系統: 司機資料.zUSELECT Emp_name, Emp_Code, leave, dept_short, Emp_IdTypeName, Emp_TypeName FROM V_emp]SELECT Emp_name, Emp_Code, leave, dept_short, Emp_IdTypeName, Emp_TypeName FROM V_emp WHERE (r   r   r   r   r   r   )r"   r%   r&   r'   r(   r)   r*   r+   r   r   r   DbQueryDriversInfo   s     
r.   c                 C   s   t d d}|r\d}t|}d}|D ]6}|d7 }||k rJ|d | d }q$|d | d }q$t | | | g }| D ]}|| qv|S )	Nu'   Querying 勤務系統: 車籍資料表.z6SELECT car_licence, DeptName, OwnerTypeName FROM V_Carr-   r   r   zCarLicense='r   r   r   )r"   carLicensesr&   ZnCarsr(   
carLicenser*   r+   r   r   r   DbQueryCarsInfo   s"    
r1   c                 C   s   d| d | d }|rxd| d | d }t |}d}|D ]:}|d7 }||k rb|d | d }q<|d | d } qxq<|r|d	 }t |}	d}|D ]:}
|d7 }||	k r|d
 |
 d }q|d
 |
 d } qq| | g }| D ]}|| q|S )Nz.SELECT * FROM V_OrderShip WHERE (Ord_Date >= 'z' AND Ord_Date <= 'r   ') AND (r   r   r   r    AND (zCus_shortName=')r   r    r!   )r"   r#   r$   r%   ZordShortNamesr&   r'   r(   r)   nCustomernamesZcustomerNamer*   r+   r   r   r   DbQueryOrderShipInfo  s4    
r5   c                 C   s   d| d | d }|rd| d | d }t |}d}|D ]B}|d7 }||k rf|d t| d }q<|d t| d } qq<|r|d	 }t |}	d}|D ]:}
|d7 }||	k r|d
 |
 d }q|d
 |
 d } qq| | g }| D ]}|| q|S )Nz,SELECT * FROM V_EmpWorkRec WHERE (WR_DT >= 'z' AND WR_DT <= 'r   r2   r   r   r   r   r3   zCar_License=')r   strr    r!   )r"   r#   r$   r%   r/   r&   r'   r(   r)   r4   r0   r*   r+   r   r   r   DbQueryEmpWorkRecInfo?  s4    
r7   c                  C   sT   t  } |  }t|dd}d}|D ]}td|| |d7 }q"|	  t
|  d S )NZ20211018r   z{}: {})r   r"   r,   r.   r1   r5   r7   r   formatr   r   )r   r"   r*   r(   r+   r   r   r   maino  s    

r9   __main__z	All done!)N)N)N)N)N)NN)NN)	VER_MAJOR	VER_MINOR	VER_BUILDr   r   mysql.connector	connectorr   r   r   r,   r.   r1   r5   r7   r9   __name__r   r   r   r   r   <module>   s    
)
"
/
-
;
;
0%