U
    j'pe                     @   s.   d dl Zd dlZd dl mZ G dd dZdS )    N)poolingc                   @   s   e Zd ZdZdZdZdZdZdZd7ddZ	dd Z
dd Zdd	 Zd8d
dZdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zd0d1 Z d2d3 Z!d4d5 Z"d6S )9GC_MYSQL c                 C   s*   ||||d}t jf ddd|| _d S )N)hostuserpassworddatabaseZmypool   )Z	pool_nameZ	pool_size)r   ZMySQLConnectionPoolconnection_pool)selfhostnameZ
login_userZlogin_passwordZuse_databaseZdbconfig r   +/var/www/html/game_dice_py/./class_mysql.pysetConnectInfo   s    zGC_MYSQL.setConnectInfoc              
   C   s@   z| j  | _W n* tk
r: } zt| W 5 d }~X Y nX d S )N)r
   Zget_connectionmydb	Exceptionprint)r   er   r   r   connect   s    zGC_MYSQL.connectc              
   C   s   d}zb|    | j }d| d }|| | }|r`|D ]}t|d dkrXd}q>d}q>nd}W nB tk
r } z$| jd k	r| j  t| d}W 5 d }~X Y nX |	  | j	  |S )	Nr   z0SELECT lineToken FROM member WHERE lineToken = ''r   NoneFTerror)
r   r   cursorexecutefetchallstrr   rollbackr   close)r   user_idstatusdbcursorsqldatametar   r   r   r   
check_bind   s*    




zGC_MYSQL.check_bindc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz0SELECT remaining FROM member WHERE lineToken = 'r   Fr   r   
r   r   r   r   r   r   r   r   r   r   r   r   r    r!   r"   r#   r   r   r   r   get_member_remaining7   s$    




zGC_MYSQL.get_member_remainingc              
   C   s   z|    | j }d| d | d | d }|| | }|  | j  |s^W dS |D ]*}t|d dkr~ W dS |d   W S qbW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )NSELECT z  FROM member WHERE lineToken = 'z' OR account = 'r   Fr   r   r%   )r   r   meta_keyr    r!   r"   r#   r   r   r   r   get_member_metaN   s$    




zGC_MYSQL.get_member_metac                 C   s,   |dkrdS |dkrdS |dkr$dS dS d S )Nwinu   勝loseu   負drawu   和r   r   )r   msgr   r   r   get_win_lose_drawd   s    zGC_MYSQL.get_win_lose_drawc              
   C   s&  d}z|    | j }d| d | d }|| | }|  | j  tt|}|rd}d}d}	|D ]B}
|t|
d d d		|
d
  kr@|rd}n |dt|	 d 7 }d}	|d7 }| 
|
d }| 
|
d }| 
|
d }t|
d d d		|
d
  }|d| d 7 }|d| d | d | d 7 }|
d dkrTd}n|
d }|d| d t|
d  d 7 }|
d dkr|d7 }|
d }t|dkrd}|	t|7 }	qt|dt|	 d 7 }n|d 7 }W nF tk
r  } z&| jd k	r| j  t| d!}W 5 d }~X Y nX |S )"N   ━━━下注紀錄━━━
SELECT b.date, b.seq, a.betOn, a.bet, b.resultA, b.resultB, b.resultC ,a.win FROM betResult as a JOIN gameResultTianjiu as b ON a.gameId = b.gameId  WHERE a.memberId IN ( SELECT memberId FROM member WHERE lineToken = 'z' )  AND b.gameId = 'z1' AND a.status = 'end' order by a.created_at DESCr   Tr   #{:03d}   F   輸贏總額 | 
%   ━━━━━━━━━━━━
   r	      	   局號 | 
   開獎 | A B C   banker   莊家         下注項目 |      金額 |             上莊費 | 300
   r   0   查無紀錄r   )r   r   r   r   r   r   listreversedr   formatr/   intr   r   r   )r   r   game_id	reply_msgr    r!   r"   tmprow_1
member_betr#   resultAresultBresultCbetOn	win_moneyr   r   r   r   get_game_bet_histryn   sZ    



$  
zGC_MYSQL.get_game_bet_histryc              
   C   sD  d}z|  d|}|s&|d7 }|W S |   | j }d| d | d }|| | }|  | j  tt|}|rd}d}d	}	|D ]B}
|t	|
d	 d
 d
|
d  kr^|rd}n |dt	|	 d 7 }d	}	|d7 }| |
d }| |
d }| |
d }t	|
d	 d
 d
|
d  }|d| d 7 }|d| d | d | d 7 }|
d dkrrd}n|
d }|d| d t	|
d  d 7 }|
d dkr|d7 }|
d }t	|dkrd }|	t|7 }	q|dt	|	 d 7 }n|d7 }W nF tk
r> } z&| jd k	r"| j  t| d!}W 5 d }~X Y nX |S )"Nr0   rC   rG   r1   z' )  AND b.gameId IN z order by a.created_at DESCr   Tr   r2   r3   r4   Fr5   r6   r7   r8   r	   r9   r:   r;   r<   r=   r>   r?   r@   rA   rB   rD   rE   r   rF   r   )get_game_idsr   r   r   r   r   r   rH   rI   r   rJ   r/   rK   r   r   r   )r   r   rM   ZgameIdsr    r!   r"   rN   rO   rP   r#   rQ   rR   rS   rT   rU   r   r   r   r   get_bet_histry   sb    



$  
zGC_MYSQL.get_bet_histryc              
   C   s   d}z|    | j }d| d }|| | }|  | j  tt|}|r|D ],}|dt|d  d t|d  d 7 }q\n|d	7 }W nB t	k
r } z$| jd k	r| j
  t| d
}W 5 d }~X Y nX |S )Nu   ━━━打賞紀錄━━━
zbSELECT tip,created_at FROM tip WHERE memberId IN ( SELECT memberId FROM member WHERE lineToken = 'z&' ) order by created_at DESC LIMIT 10 u   打賞金額 | r   u    打賞時間 | r4   r6   rG   r   )r   r   r   r   r   r   rH   rI   r   r   r   r   )r   r   rM   r    r!   r"   r#   r   r   r   r   get_tip_histry   s(    


,

zGC_MYSQL.get_tip_histryc           
   
   C   s   z|    | j }d| d t| }|| | }|  | j  d}d}|s^W dS |D ]L}|d7 }t|d dkr W dS |d|d  d 7 }|t|k rb|d	7 }qb|d
 W S  tk
r }	 z | jd k	r| j	  t
|	 W 5 d }	~	X Y nX d S )NzSELECT gameId FROM `gameResultTianjiu` WHERE gameId IN ( SELECT gameId FROM betResult WHERE status='end' AND memberId IN ( SELECT memberId FROM member WHERE lineToken = 'z%' ) ) ORDER BY created_at DESC LIMIT (r   Fr4   r   ",))r   r   r   r   r   r   r   lenr   r   r   )
r   countr   r    r!   r"   Zidsir#   r   r   r   r   rW      s0    






zGC_MYSQL.get_game_idsc              
   C   s   z|    | j }d| d | d }|| | }|  | j  |sVW dS |D ]*}t|d dkrv W dS |d   W S qZW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nr(   z( FROM gameResultTianjiu WHERE gameId = 'r   Fr   r   r%   )r   gameIdr)   r    r!   r"   r#   r   r   r   r   get_game_meta  s$    




zGC_MYSQL.get_game_metac              
   C   s   zx|    | j }d}|| | }|  | j  |sFW dS |D ]*}t|d dkrf W dS |d   W S qJW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )NzTSELECT gameId FROM gameResultTianjiu WHERE status = 'begin' order by created_at DESCFr   r   r%   )r   r    r!   r"   r#   r   r   r   r   get_begin_game_id%  s$    




zGC_MYSQL.get_begin_game_idc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz`SELECT name FROM member WHERE memberId IN (SELECT banker FROM gameResultTianjiu WHERE gameId = '')rF   r   r   r%   r   ra   r    r!   r"   r#   r   r   r   r   
get_banker<  s$    




zGC_MYSQL.get_bankerc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz-SELECT master FROM member WHERE lineToken = 'r   Fr   r   r%   r&   r   r   r   check_game_managerR  s$    




zGC_MYSQL.check_game_managerc              
   C   s  d}zd}d}d}d}d}d}d}	d}
i }d|d< i |d< d|d d< d|d d< g |d d	< |    | j }d
| d }|| | }|D ]}dt|d  d d|d  }t|d d dkr|d7 }n|d7 }|d d	 d|ddd |d d	 dddddd |d d	 ddddd |d d	 ddi qg }g }g }g }d| d }|| | }|D ]N}|d d kr|dd!|d  d" d#|d   ddd$ |d7 }t|d }d%}
|d d&kr*|dd!|d  d" d#|d   ddd$ |d7 }|t|d 7 }|d d'kr||dd!|d  d" d#|d   ddd$ |d7 }|t|d 7 }|d d(kr|dd!|d  d" d#|d   ddd$ |d7 }|t|d 7 }q|d d	 dd)d*d+dd, |d d	 | |d d	< |d d	 ddi |d d	 dd&d*d+dd, |d d	 | |d d	< |d d	 ddi |d d	 dd'd*d+dd, |d d	 | |d d	< |d d	 ddi |d d	 dd(d*d+dd, |d d	 | |d d	< |d d	 ddi |d d	 dd-d*d+dd, |d d	 dd.d#|  ddd$ |d d	 dd/t|
 ddd$ |d d	 dd0d#||
   ddd$ |d d	 dd1t| ddd$ |d d	 ddi |d d	 dd2d*d+dd, |d d	 dd3d#|  ddd$ |d d	 dd4d#|  ddd$ |d d	 dd5d#|  ddd$ |d d	 dd0d#|| |   ddd$ |d d	 dd1t| ddd$ |	  | j	  |W S  t
k
r } z(| jd k	r| j  t| W Y d6S d }~X Y nX d S )7Nr   r   bubbletypebodyboxverticallayoutcontentsz7SELECT date,seq FROM gameResultTianjiu WHERE gameId = 'r   	   局號：r2   r3   r4   r>      -下巡   -上巡textsm#069142ri   rr   sizeZcoloru   🎲牌局快訊🎲xxlcenterri   rr   rv   marginalignu   有效下注玩家)ri   rr   r{   rz   	separatorzoSELECT b.name,a.bet,a.betOn FROM betResult as a JOIN member as b ON a.memberId = b.memberId  WHERE a.gameId = '%' AND a.bet > 0 ORDER BY a.created_atr?   [] %dri   rr   rz   rv   i,  ABC   莊mdboldri   rr   rv   Zweightrz   	   莊金額   莊 | u   上莊費 |    押注總額 | 	   押注 | u   下注總表   A門 |    B門 |    C門 | Fr   r   r   r   r   r   rJ   rK   appendr   r   r   r   )r   ra   rM   banker_bet_totala_totalb_totalc_total	bet_countbanker_countmember_bet_total
banker_feeZgame_begin_flex_msgr    r!   r"   r#   game_nobet_lista_listb_listc_listr   r   r   r   get_begin_game_infoh  s   

"
 

    	


 





zGC_MYSQL.get_begin_game_infoc               
   C   s  d}zd}d}d}d}d}d}d}	d}
d}d}i }d|d< i |d< d|d d< d|d d< g |d d	< |    | j }d
| d }|| | }|D ]}dt|d  d d|d  }t|d d dkr|d7 }n|d7 }|d d	 d|ddd |d d	 dddddd |d d	 ddi |d }
|d }|d }qd}d}d }d!}|
d"krv|d#7 }n|
d$kr|d%7 }n|d&7 }|d"kr|d#7 }n|d$kr|d%7 }n|d&7 }|d"kr|d#7 }n|d$kr|d%7 }n|d&7 }g }g }g }g }d'| d( }|| | }|D ]}|d d)krd*|d  d+ d,|d   }t|d }|dkrr|d-7 }|d|ddd. |d7 }|d dkrd*|d  d+ d,|d   }|
d/kr|d-7 }|d|ddd. |d7 }|t|d 7 }|d dkr^d*|d  d+ d,|d   }|d/kr2|d-7 }|d|ddd. |d7 }|t|d 7 }|d d kr$d*|d  d+ d,|d   }|d/kr|d-7 }|d|ddd. |d7 }|t|d 7 }q$|d d	 dd!d0d1dd2 |d d	 | |d d	< |d d	 ddi |d d	 d|d0d1dd2 |d d	 | |d d	< |d d	 ddi |d d	 d|d0d1dd2 |d d	 | |d d	< |d d	 ddi |d d	 d|d0d1dd2 |d d	 | |d d	< |d d	 ddi |d d	 dd3d0d1dd2 |d d	 dd4d,|  ddd. |d d	 dd5d,|  ddd. |d d	 dd6t| ddd. |d d	 ddi |d d	 dd7d0d1dd2 |d d	 dd8d,|  ddd. |d d	 dd9d,|  ddd. |d d	 dd:d,|  ddd. |d d	 dd;d,|| |   ddd. |d d	 dd6t| ddd. |	  | j	  |W S  t
k
r } z(| jd k	r| j  t| W Y d<S d }~X Y nX d S )=Nr   r   rh   ri   rj   rk   rl   rm   rn   zOSELECT date,seq,resultA,resultB,resultC FROM gameResultTianjiu WHERE gameId = 'r   ro   r2   r3   r4   r>   rp   rq   rr   rs   rt   ru   u   🎲開牌結果🎲rw   rx   ry   r|   rC   r8   r   r   r   r   r,   u    負r-   u    和u    勝zoSELECT b.name,a.win,a.betOn FROM betResult as a JOIN member as b ON a.memberId = b.memberId  WHERE a.gameId = 'r}   r?   r~   r   r   u   🀄️r   r+   r   r   r   r   r   u   莊家輸贏 | r   u   輸嬴總表r   r   r   r   Fr   ) r   ra   rM   r   r   r   r   r   r   r   ZA_is_winZB_is_winZC_is_winZgame_result_flex_msgr    r!   r"   r#   r   Za_msgZb_msgZc_msg
banker_msgr   r   r   r   bet_msgZa_f_msgZb_f_msgZc_f_msgr   r   r   r   get_game_resultb  s   

"
 


















    	


 





zGC_MYSQL.get_game_resultc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz/SELECT SUM(bet) FROM betResult WHERE gameId = 'z' AND betOn != 'banker' Fr   r   r%   re   r   r   r   get_bet_total  s$    




zGC_MYSQL.get_bet_totalc              
   C   s   zR|  |}| j  | |d}| j  t|t| }|dkrJW dS W dS W n> tk
r } z | jd k	rx| j  t| W 5 d }~X Y nX d S )N	bankerBetr   TF)r   r   r   rb   rK   r   r   r   )r   ra   	bet_totalr   can_bet_totalr   r   r   r   is_full_bet  s    





zGC_MYSQL.is_full_betc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]8}t|d dkrn W dS |d |kr W dS  W dS qRW nD tk
r } z&| jd k	r| j  t	| W Y dS d }~X Y nX d S )NzeSELECT lineToken FROM member WHERE memberId IN (SELECT banker FROM gameResultTianjiu WHERE gameId = 'z') Fr   r   Tr%   )r   ra   r   r    r!   r"   r#   r   r   r   r   	is_banker  s*    




zGC_MYSQL.is_bankerc              
   C   s   z|    | j }dt| d | d }|| | }|  | j  |sZW dS |D ]*}t|d dkrz W dS |d   W S q^W n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )NzbSELECT lineToken FROM member WHERE memberId IN (SELECT banker FROM gameResultTianjiu WHERE seq = '' AND date ='rd   Fr   r   
r   r   r   r   r   r   r   r   r   r   r   seqdater    r!   r"   r#   r   r   r   r   get_first_round_banker  s$    




zGC_MYSQL.get_first_round_bankerc              
   C   s   z|    | j }dt| d | d }|| | }|  | j  |sZW dS |D ]*}t|d dkrz W dS |d   W S q^W n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz2SELECT gameId FROM gameResultTianjiu WHERE seq = 'r   r   Fr   r   r   r   r   r   r   get_fist_game_id  s$    




zGC_MYSQL.get_fist_game_idc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz-SELECT memberId FROM member WHERE account = 'r   Z	no_memberr   r   r%   r   accountr    r!   r"   r#   r   r   r   r   get_member_id_by_account  s$    




z!GC_MYSQL.get_member_id_by_accountc              
   C   s   z|    | j }d| d }|| | }|  | j  |sNW dS |D ]*}t|d dkrn W dS |d   W S qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz.SELECT lineToken FROM member WHERE memberId ='r   Fr   r   r%   )r   r?   r    r!   r"   r#   r   r   r   r   get_banker_lineToken  s$    




zGC_MYSQL.get_banker_lineTokenc              
   C   s   z|    | j }dt| d t| d }|| | }|  | j  |s^W dS |D ]*}t|d dkr~ W dS |d   W S qbW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz3SELECT gameId FROM gameResultTianjiu WHERE date = 'z' AND seq = 'r   Fr   r   r   )r   r   r   r    r!   r"   r#   r   r   r   r   get_game_id_by_date_seq  s$    




z GC_MYSQL.get_game_id_by_date_seqc              
   C   s   zv|    | j }d| d }|| | }|  | j  |sNW dS |D ] }|d dkrj W dS  W dS qRW n> tk
r } z | jd k	r| j  t| W 5 d }~X Y nX d S )Nz3SELECT count(account) FROM member WHERE account = 'r   Fr   T)	r   r   r   r   r   r   r   r   r   r   r   r   r   check_account3  s$    




zGC_MYSQL.check_accountc              
   C   s   zz|    | j }d| d }|| | }|  | j  |sNW dS |D ]$}t|d dkrn W dS  W dS qRW n> tk
r } z | jd k	r| j  t	| W 5 d }~X Y nX d S )Nz.SELECT lineToken FROM member WHERE account = 'r   Fr   r   Tr%   r   r   r   r   was_bindJ  s$    




zGC_MYSQL.was_bindN)r   )r   )#__name__
__module____qualname__r   r   r   Zpasswdr   r
   r   r   r$   r'   r*   r/   rV   rX   rY   rW   rb   rc   rf   rg   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r      sF   


48 {  "r   )Zmysql.connectormysqlZuuidr   r   r   r   r   r   <module>   s   