
    `oiG                         d Z d ZdZddlZddlZddlmZ ddZddZ	ddZ
ddZddZddZdd	Zd
 Zedk    r e              ed           dS dS )       Nc                 ^   | d k    r6t          d           d}t          d|           t          j        |          }np| dk    rjt          d           	 t          j        dddd	d
          }n@# t          j        $ r.}t          d|           t          j        d           Y d }~nd }~ww xY w|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	127.0.0.1i  itransdb)userpasswordhostportdatabasez%Error connecting to MariaDB Platform:r   )printpyodbcconnectmariadbErrorsysexit)whichDBconn_strcnxnes       (/srv/django_bis/apps/parsefile/dbintf.py	DbConnectr   "   s    $ 	=>>>* 	 	lH%%%~h''	N	"	"
 	=>>>	? & !#	 	 	DD } 	 	 	9!<<<HQKKKKKKKK	 Ks   A- -B*<$B%%B*c                     |d k    rt          d           n|dk    rt          d           |                                  d S )Nu$   Disconnecting 勤務系統 database.r   u$   Disconnecting 愛豐系統 database.)r   close)connr   s     r   DbDisconnectr   N   sI    $45555	N	"	"4555JJLLL
F    c                 2   t          d           d|z   dz   |z   dz   }|rFd|z   dz   |z   dz   }t          |          }d}|D ]$}|dz  }||k     r|dz   |z   d	z   }|dz   |z   d
z   } |                     |           g }| D ]}	|                    |	           |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rows
             r   DbQueryDriversVacationr2   p   s    	
0111?)KNddgnnquuG 
CiORhhkrru{##" 	 	GFA8||!M1G;hF!M1G;eC
NN7J  #r   c                    t          d           d}|r:d}t          |          }d}|D ]$}|dz  }||k     r|dz   |z   dz   }|dz   |z   dz   }%|                     |           g }| D ]}|                    |           |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/   r0   r1   s           r   DbQueryDriversInfor5      s    	
0111eG 	Dq{##" 	D 	DGFA8||!M1G;hF!M1G;eC
NN7J  #r   c                     t          d           d}|r:d}t          |          }d}|D ]$}|dz  }||k     r|dz   |z   dz   }|dz   |z   dz   }%t          |           |                     |           g }| D ]}|                    |           |S )	Nu'   Querying 勤務系統: 車籍資料表.z6SELECT car_licence, DeptName, OwnerTypeName FROM V_Carr4   r   r   zCarLicense='r"   r#   r$   )r(   carLicensesr,   nCarsr.   
carLicenser0   r1   s           r   DbQueryCarsInfor:      s    	
3444FG 	IqK  % 	I 	IJFA5yy!O3j@8K!O3j@5H	'NNN
NN7J  #r   c                    d|z   dz   |z   dz   }|rFd|z   dz   |z   dz   }t          |          }d}|D ]$}|dz  }||k     r|dz   |z   dz   }|dz   |z   dz   } |r=|d	z   }t          |          }	d}|D ]$}
|dz  }||	k     r|d
z   |
z   dz   }|d
z   |
z   dz   } |                     |           g }| D ]}|                    |           |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+   ordShortNamesr,   r-   r.   r/   nCustomernamescustomerNamer0   r1   s                r   DbQueryOrderShipInforA     sd   ?)KNeehoorwwG 
CiORiilss  wB  B{##" 	 	GFA8||!M1G;hF!M1G;eC 
H$]++) 	 	LFA>!!!$66EP!$66EM NN7J  #r   c                    d|z   dz   |z   dz   }|r`d|z   dz   |z   dz   }t          |          }d}|D ]>}|dz  }||k     r|dz   t          |          z   dz   }&|dz   t          |          z   dz   } |r=|d	z   }t          |          }	d}|D ]$}
|dz  }||	k     r|d
z   |
z   dz   }|d
z   |
z   dz   } |                     |           g }| D ]}|                    |           |S )Nz,SELECT * FROM V_EmpWorkRec WHERE (WR_DT >= 'z' AND WR_DT <= 'r#   r<   r   r   r!   r"   r=   zCar_License=')r%   strr&   r'   )r(   r)   r*   r+   r7   r,   r-   r.   r/   r?   r9   r0   r1   s                r   DbQueryEmpWorkRecInforD   B  si   =	IL``cjjmrrG 
AIMPddgnnq||{##" 	 	GFA8||!M1CLL@8K!M1CLL@5H 
H$[))% 	 	JFA>!!!$44zAHL!$44zAEI NN7J  #r   c                     t                      } |                                 }	 	 	 	 t          |d
d
          }	 	 d}|D ]*}t          d                    ||                     |dz  }+|	                                 t          |            d S )NF2021092920210930u	   蕭家源u	   張哲綸)r+   u	   翁廷隆u	   謝家興T202110182021091120210916r   z{}: {})r   r(   r2   r5   r:   rA   rD   r   formatr   r   )r   r(   r0   r.   r1   s        r   mainrL   r  s    ;;D[[]]FtL-J)&*jII
K
 	 	C(//!S))***FAA LLNNN
Fr   __main__z	All done!)N)NN)	VER_MAJOR	VER_MINOR	VER_BUILDr   r   mysql.connector	connectorr   r   r   r2   r5   r:   rA   rD   rL   __name__r    r   r   <module>rU      s  . 			 



  ! ! ! ! ! !
' ' ' 'X   D   ^   Z   v" " " "v# # # #`" " "J zDFFF	E+ r   