B
    CDb^¶  ã               @   sš  d dl mZmZmZ d dlmZ d dlmZ d dlmZ d dlm	Z	 d dlm
Z
 d dlmZ d dlmZ d d	lmZ d d
lmZ d dlZd dlmZmZ d dlmZ dZdZdZdZdZdZd add„ Zdd„ Z dd„ Z!d:dd„Z"dd„ Z#dd„ Z$d d!„ Z%d"d#„ Z&ed;d$d%„ƒZ'ed<d&d'„ƒZ(ed=d(d)„ƒZ)ed>d*d+„ƒZ*ed?d,d-„ƒZ+ed@d.d/„ƒZ,edAd0d1„ƒZ-edBd2d3„ƒZ.edCd4d5„ƒZ/edDd6d7„ƒZ0d8d9„ Z1dS )Eé    )Úget_connectionÚget_insert_sqlÚ	test_conn)ÚEmployee)ÚTerminal)ÚArea)Ú
Department)ÚPosition)ÚBioData)Úsettings)Úcache)ÚwrapsN)Úcustom_sql_query_advÚcustom_sql_query)Ú
multi_execÚ1Ú2Ú3Ú4Ú5Ú6c                s   t ˆ ƒ‡ fdd„ƒ}|S )Nc                 sL   t j  ¡ }ˆ | |Ž}t j  ¡ }tdƒ td ˆ jˆ j|| ¡ƒ tdƒ |S )Nz=
============================================================z
{}.{} : {}z=============================================================
)ÚdatetimeÚnowÚprintÚformatÚ
__module__Ú__name__)ÚargsÚkwargsÚstartÚrÚend)Úfunc© ú5G:\easytimepro\master/mysite/base\views\db_migrate.pyÚwrapper   s    


ztimethis.<locals>.wrapper)r   )r"   r%   r#   )r"   r$   Útimethis   s    
r&   c        	      C   s¸   dd l } dd l}|  ¡ }| | j¡ tjd }|j | 	¡ |¡}|j 
|¡sV| |¡ |d }| j|dd}| | j¡ |  d¡}| |¡ |  ¡ }| | j¡ | |¡ | |¡ |S )Nr   Zlogsz/migration_log.txtzw+)Úmodez8%(asctime)s - %(filename)s - %(levelname)s : %(message)s)ÚloggingÚosZ	getLoggerZsetLevelÚINFOr   ZADDITION_FILE_ROOTÚpathÚjoinÚgetcwdÚexistsÚmakedirsZFileHandlerZ	FormatterZsetFormatterZStreamHandlerZERRORZ
addHandler)	r(   r)   ÚloggerZfile_dirZlog_pathZlog_fileZfile_handlerZ	formatterZconsole_handlerr#   r#   r$   Ú
get_logger'   s$    





r1   c          
   C   s°  t ƒ }| | ¡ y$t| dd}|r2| d |¡¡ W n,   dd l}| ¡  |jddd Y nLX | d }| d }| d	 }| d
 }| d }ttt	t
tttdœ}	züy¢t| ƒ}
t|||
d x†|D ]~}|	 |d ¡}|d k	ròt|ƒrò||||||
d q¾|dkrt||||
d t||||
d q¾|dkr¾t||||f||
d q¾W W nT tk
r– } z4dd l}| ¡  |jddd | d¡ t|ƒ‚W d d }~X Y nX W d dat d¡ X d S )NT)Útestz Failed to connect to database:{}r   z#Error happend when connect database)Úexc_infoÚmigrate_software_typeÚmigrate_database_tablesÚmigrate_transaction_startÚmigrate_transaction_endÚmigrate_database_type)ÚareaÚ
departmentÚpositionÚdeviceÚfingerprintÚfacetempÚbiotemp)Úconn)Údbtyper@   ÚemployeeÚtransactionzError raised during migrate.ZFinishÚis_migrate_transaction)r1   Úinfor   r   Ú	tracebackÚ	print_excÚerrorÚarea_migrateÚdept_migrateÚposition_migrateÚdevice_migrateÚfingertemp_migrateÚfacetemp_migrateÚbiotemp_migrater   Úcompany_migrateÚgetÚcallableÚemployee_migrateÚatt_area_migrateÚtransaction_migrateÚ	ExceptionÚMULTI_COMPANYr   Údelete)Úparamsr0   ÚmsgrF   Úmigrate_softwareZmigrate_tablesZtrans_startZ	trans_endZdb_typeZfuncsr@   Útbr"   Úer#   r#   r$   Ú
to_migrate>   sT    



r^   c             C   s’  ddl m} ddlm} ddlm} ddlm} |j}d 	| 
d¡¡}| tfkrŽ|ƒ }	|	j ¡ }
d}t||d	\}}|rŽd
ad}t||d	\}}xø|D ]ð}|d |ksš|d |kr¸qš|jj|d d ¡ rÐqš||d |d
 |d |d |d |d |d |d |d |d |d tj ¡ tj ¡ |d |d |d |d |d d}| ¡  x8|
D ]0}|jrV|jj||dd }d
|_| ¡  qVW qšW d S )Nr   )r   )Úget_user_model)ÚCompany)Ú
MembershipÚ ú-z:select id from [sysobjects] where name='personnel_company')r@   é   a§  
                select c.id, c.name, c.company_phone, c.sub_domain, c.address_1, c.country, c.city, c.payment_type, 
                c.contact_name, c.contact_phone, c.company_address, c.subscription_start_date, c.subscription_end_date,
                c.max_employees_limit, c.max_devices_limit, c.max_transactions_limit, c.max_mobile_applications_limit,
                c.max_whatsapp_push_limit from personnel_company c)Úidé   é   é   é   é   é   é   é	   é
   é   é   é   é   é   )re   ÚnameZcompany_phoneZ
sub_domainZ	address_1ZcountryZcityZpayment_typeZcontact_nameZcontact_phoneZcompany_addressZsubscription_start_dateZsubscription_end_dateZmax_employees_limitZmax_devices_limitZmax_transactions_limitZmax_mobile_applications_limitZmax_whatsapp_push_limit)ZcompanyÚuser)Údjango.confr   Zdjango.contrib.authr_   Zmysite.cloud.modelsr`   Zmysite.base.modelsra   ÚDEFAULT_COMPANY_IDr,   ÚsplitÚBIOTIME8ÚobjectsÚallr   rW   Úfilterr.   r   r   ÚsaveZis_superuserZget_or_createZrole)r[   r0   r@   r   r_   r`   ra   Zdefault_company_id_lineZdefault_company_idZ
class_userZusersÚsqlÚcursorÚrowsÚrowÚobjru   Z
membershipr#   r#   r$   rP   p   sZ    


rP   c             C   sF   | r>t jj| |d ¡ }|s.t ƒ }| |_||_||_| ¡  nd }|S )N)Ú	dept_codeÚ
company_id)r   rz   r|   Úfirstrƒ   r„   Ú	dept_namer}   )Úcodert   r„   Údeptr#   r#   r$   Úget_dept¦   s    
r‰   c             C   sF   | r>t jj| |d ¡ }|s.t ƒ }| |_||_||_| ¡  nd }|S )N)Úposition_coder„   )r	   rz   r|   r…   rŠ   r„   Úposition_namer}   )r‡   rt   r„   r;   r#   r#   r$   Úget_position´   s    
rŒ   c             C   s@   | r8t jj| |d ¡ }|s(t ƒ }| |_||_| ¡  nd }|S )N)Ú	area_coder„   )r   rz   r|   r…   r   Ú	area_namer}   )r‡   rt   r„   r9   r#   r#   r$   Úget_areaÂ   s    
r   c             C   s4   t r0|  dd¡\}}|d |¡7 }d ||f¡} | S )NÚfromrd   z, {}.company_idz from )rW   rx   r   r,   )r~   Z
table_nameZsql_headZsql_endr#   r#   r$   Úupdate_sql_for_companyÏ   s
    r‘   c             C   sr  ddl m} d|krx| d¡ | ttfkr0d}nH| tfkr@d}n8| tfkrPd}n(| tfkr`d}n| tfkrxd	}t	|d
ƒ}t
||d\}}|rnxÞ|D ]Ö}	tr¤|	d n|j}
|	d }|	d }|	d }|	d }t|||
ƒ}tjj||
d}|rô|d }ntƒ }||_|
|_||_|r||_| tfkr.|	d |_y| ¡  W q” tk
rh } ztd|ƒ W d d }~X Y q”X q”W d S )Nr   )r   r9   z[*]Area MigartezÜ
                   select a.areaid as area_code, a.areaname as area_name, b.areaid as pcode, b.areaname as pname from personnel_area  a
                  left join personnel_area b on a.parent_id = b.id
                z…
                    select companyid as area_code, companyname as area_name, '' as pcode, '' as pname from company
                 z
                select a.area_id, a.area_name, c.company_id, c.company_name from area a left join company c on c.id = a.company_id
            za
                select zone_code, ZoneName, '' as pcode, '' as pname from  att_zone
            zÖ
                select a.area_code, a.area_name, b.area_code as pcode, b.area_name as pname, a.is_default from personnel_area a
                  left join personnel_area b on a.parent_area_id = b.id 
            Úa)r@   ri   rd   rf   rg   )r   r„   rh   Z22222222222222)rv   r   rE   Ú
ZKTIMEWEB2ÚBIOTIME7ÚWDMS5ÚWDMS6Ú	ZKTIMENETry   r‘   r   rW   rw   r   r   rz   r|   r   r„   rŽ   Zparent_areaÚ
is_defaultr}   rV   r   )ÚcategoryÚfieldsr0   r@   rA   r   r~   r   r€   r    r„   ÚareaidÚareanameZ	parent_idÚparent_nameZpareaZareasr9   r]   r#   r#   r$   rI   ×   sL    








rI   c             C   sX  ddl m} d|krT| d¡ | tfkr0d}nf| tfkrN|dkrHd}q–d}nH| tfkr^d	}n8| tfkrnd
}n(| tfkr~d}n| tfkr–d}t	|dƒ}t
||d\}}|rTx¦|D ]ž}	trÂ|	d n|j}
|	d }|	d }|	d }|	d }t|||
ƒ}tjj||
d ¡ }|s tƒ }||_|
|_||_|r2||_| tfkrH|	d |_| ¡  q²W d S )Nr   )r   r:   z[*]Department Migratez˜
                    select d.code, d.deptname, p.code, p.deptname from departments d left join departments p on d.supdeptid = p.deptid
                Ú
postgresqlz˜
                    select d.code, d.DeptName, p.code, p.DeptName from departments d left join departments p on d.supdeptid = p.DeptID
                zž
                    select d.code, d."DeptName", p.code, p."DeptName" from departments d left join departments p on d.supdeptid = p."DeptID"
                z
            select d.DeptID, d.DeptName, p.DeptID, p.DeptName from  departments d left join departments p on d.supdeptid=p.DeptID
            zZ
            select id, depart_name, '' as pcode, '' as pname from department
            z¥
            select d.dept_code, d.dept_name, p.dept_code, p.dept_name from hr_department d left join hr_department p on d.dept_parentcode = p.dept_code
            z½
                select d.dept_code, d.dept_name, p.dept_code, p.dept_name, d.is_default from personnel_department d left join personnel_department p on d.parent_dept_id = p.id
            Úd)r@   ri   rd   rf   rg   )rƒ   r„   rh   )rv   r   rE   r“   r”   r•   r–   r—   ry   r‘   r   rW   rw   r‰   r   rz   r|   r…   rƒ   r„   r†   Zparent_deptr˜   r}   )r™   rš   r0   r@   rA   r   r~   r   r€   r   r„   ZdeptcodeZdeptnameZ
parentcodeZ
parentnameÚparentrˆ   r#   r#   r$   rJ     sL    










rJ   c             C   s(  d|kr$|  d¡ d}d}| tttfkr,n8| tfkr<d}n(| tfkrLd}n| tfkrdd}t|dƒ}|rxt||d\}}|r$x¤|D ]œ}t	r”|d	 nt
j}	|d
 }
|d }|d }|d }t|||	ƒ}tjj|
|	d ¡ }|sðtƒ }|
|_|	|_||_|r||_| tfkr|d |_| ¡  q„W d S )Nr;   z[*]Position Migarterb   z”
            select a.code, a.name, b.code, b.name from personnel_positions a left join personnel_positions b on a.suppositionid = b.id
            z˜
                select a.code, a.name, b.code, b.name from personnel_positions a left join personnel_positions b on a.parent_id = b.id
                zâ
                select a.position_code, a.position_name, b.position_code, b.position_name, a.is_default 
                from personnel_position a left join personnel_position b on a.parent_position_id = b.id
                r’   )r@   ri   r   rd   rf   rg   )rŠ   r„   rh   )rE   r•   r–   r—   r“   r”   ry   r‘   r   rW   r   rw   rŒ   r	   rz   r|   r…   rŠ   r„   r‹   Zparent_positionr˜   r}   )r™   rš   r0   r@   rA   r€   r~   r   r   r„   rŠ   r‹   Zparent_coder   Ztemp_parentZtemp_positionr#   r#   r$   rK   G  sD    







rK   c       (      C   sR  ddl m} d}d|krN| d¡ d}| tfkr8d}nj| tfkrV|dkrPd	}q¢d
}nL| tfkrfd}n<| tfkrvd}n,| tfkrŠd}d}n| tfkr¢d}t	|dƒ}t
||d\}	}|rNyTtj ¡ }
tj ¡ tjdd }x,|D ]"}trö|d ntj}|d }|d }|rj|dkr"d}n|dkr2d}nd}|rjt|tjƒrT| ¡ }ntj |d d… d¡}d}d}|d  d¡ dd¡}|d }|d }|d  }|d! }|}|d" p¸d }|rÆ|pÎ|
pÎd }|d }|d# rì|d# pîd}|d$ r|d$ pd }|d% }|d& r.|d& dkr.d }n|d& rB|d& pDd }tjj||d'}t|||ƒ} | ttfkr¨yt|d( |d) |ƒj}!W n tk
r¤   d }!Y nX n|d( p´d }!d}"d}#d }$| tfkræ|d* }$|d+ }#|d, }"i }%|rä|rúd}n||%d-< ||%d.< ||%d/< | |%d0< |!|%d1< ||%d2< ||%d3< ||%d4< ||%d5< ||%d6< ||%d7< |rb||ƒph|d8ƒ|%d9< ||%d:< ||%d;< |#|%d<< |$|%d=< |"|%d>< | ¡ |%d?< |r¶|jf |%Ž qäytf |%Ž  ¡  W qä t!k
r }& zt"d@|&ƒ dd l#}'|' $¡  W d d }&~&X Y qäX qäW W n> t!k
rL }& zt"dA|&ƒ dd l#}'|' $¡  W d d }&~&X Y nX d S )BNr   )Úmake_passwordFrB   z[*]Employee Migarterb   a™  
            select u.badgenumber, u.name, u.lastname, d.code, d.deptname, u.position_id,
            u.Gender, u.Birthday, u.Hiredday, u.hiretype, u.email, u.isatt, u.emptype, u.selfpassword, c.cardno
            from userinfo u
            left join departments d on u.defaultdeptid = d.DeptID
            left join personnel_issuecard c on c.userid_id = u.userid
            where u.status = 0
            rž   a.  
                    select u.badgenumber, u.name, u.lastname, d.code, d.DeptName, p.code as p_code,
                    u.Gender, u.Birthday, u.Hiredday, u.hiretype, u.email, u.isatt, u.emptype, u.selfpassword, c.cardno, p.name as p_name
                    from userinfo u
                    left join departments d on u.defaultdeptid = d.DeptID
                    left join personnel_issuecard c on c.UserID_id = u.userid
                    left join personnel_positions p on p.id = u.position_id
                    where u.status = 0
                a:  
                    select u.badgenumber, u.name, u.lastname, d.code, d."DeptName", p.code as p_code,
                    u."Gender", u."Birthday", u."Hiredday", u.hiretype, u.email, u.isatt, u.emptype, u.selfpassword, c.cardno, p.name as p_name
                    from userinfo u
                    left join departments d on u.defaultdeptid = d."DeptID"
                    left join personnel_issuecard c on c."UserID_id" = u.userid
                    left join personnel_positions p on p.id = u.position_id
                    where u.status = 0
                ae  
            select u.badgenumber, u.name, '' as lastname, d.DeptID, d.DeptName, '' as position_id,
            u.Gender, u.Birthday, '' as hiredate, '' as hiretype, '' as email, '' as isatt, '' as emptype, u.Password, u.Card
            from userinfo u
            left join departments d on u.defaultdeptid = d.DeptID
            where 1 = 1 
            a(  
            select u.pin, u.emp_name, '' as lastname, d.id, d.depart_name, '' as position_id, u.gender, '' as birthday, '' as hiredata, 
            '' as hiretype, u.email, u.status, '' as emptype, u.password, u.card from employee u left join department d on u.department_id = d.id
            Taf  
            select u.emp_pin, u.emp_firstname, u.emp_lastname, d.dept_code, d.dept_name, '' as position, u.emp_gender, u.emp_birthday,
            u.emp_hiredate, '' as hiretype, u.emp_email, u.emp_active, '' as emptype, u.emp_pwd, u.emp_cardNumber
            from hr_employee u 
            left join hr_department d on u.department_id = d.id
            a	  
                select u.emp_code, u.first_name, u.last_name, d.dept_code, d.dept_name, p.position_code, 
                u.gender, u.birthday, u.hire_date, '' as hiretype, u.email, u.enable_att, u.emp_type, 
                u.self_password, u.card_no, p.position_name, u.device_password, u.verify_mode, u.dev_privilege
                from personnel_employee u
                left join personnel_department d on u.department_id = d.id
                left join personnel_position p on u.position_id = p.id
            Úu)r@   é   )Zdaysé   rj   rl   éÿÿÿÿÚMÚFrn   z%Y-%M-%dÚ0ú rd   rf   rg   rh   rk   é   é   ro   rp   )Úemp_coder„   ri   rq   rr   rs   é   r¬   Z
first_nameÚ	last_namer:   Zposition_idÚgenderZbirthdayÚ	hire_dateZemailZ
enable_attÚemp_typeZ123456Zself_passwordZcard_noÚupdate_timeÚverify_modeÚdevice_passwordZdev_privileger„   zsave Employee errorzemployee_migrate error)%Zdjango.contrib.auth.hashersr¡   rE   r“   r”   r•   r–   r—   ry   r‘   r   r   Ztodayr   Z	timedeltarW   r   rw   Ú
isinstanceÚdateÚstrptimeÚlstripÚreplacer   Zall_objectsr|   r‰   rŒ   re   ÚAttributeErrorÚstripÚupdater}   rV   r   rF   rG   )(r™   rš   r0   r@   rA   r¡   ZZKNETr€   r~   r   r°   r²   r   r„   r¯   Zhire_dayZ	is_updateZ	is_insertr¬   Zemp_firstnameZemp_lastnameZemp_deptr†   Z
emp_genderZemp_birthdayZemp_hiredayZ	emp_emailZ	emp_isattr±   Zemp_pwdZemp_cardZtmp_emprˆ   Zemp_position_idZdevice_privileger³   r´   Zemp_dictr]   rF   r#   r#   r$   rS   t  sÔ    



	










"
rS   c             C   s>  d|kr:|  d¡ | ttfkr&d}nH| tfkr6d}n8| tfkrFd}n(| tfkrVd}n| tfkrnd}t|dƒ}t||d	\}}|r:x´|D ]¬}yŠt	rœ|d
 nt
j}	|d  d¡}
|d }|d }tjj|
|	d ¡ }|sÚwŠt|||	ƒ}t|j ¡ ƒ}||kr| |¡ ||_| ¡  W qŠ   dd l}| ¡  Y qŠX qŠW d S )NrB   z[*]Att Area MigarteaH  
                    select u.userid as uid, u.badgenumber as emp_code, pa.areaid as area_code, pa.areaname as area_name
                    from userinfo_attarea ua
                    inner join userinfo u on ua.employee_id = u.userid
                    inner join personnel_area pa on ua.area_id = pa.id
                    a  
                    select u.userid as uid, u.badgenumber as emp_code, cpy.companyid as area_code, cpy.companyname as area_name
                    from userinfo u
                    left join company cpy on u.company_id = cpy.companyid 
                    zü
            select u.id as uid, u.pin as emp_code, pa.area_id as area_code, pa.area_name as area_name from employee_area ua 
            inner join employee u on ua.employee_id = u.id
            inner join area pa on  ua.area_id = pa.id 
            a  
            select u.id as uid, u.emp_pin as emp_code, az.zone_code as area_code, az.ZoneName as area_name
            from att_employee_zone as e
            inner join hr_employee u on e.employee_id = u.id
            inner join att_zone az on az.id = e.zone_id
            a  
                select u.id as uid, u.emp_code, pa.area_code, pa.area_name
                from personnel_employee_area ua
                inner join personnel_employee u on ua.employee_id = u.id
                inner join personnel_area pa on ua.area_id = pa.id
            r¢   )r@   rh   rd   r¨   rf   rg   )r¬   r„   r   )rE   r“   r”   r•   r–   r—   ry   r‘   r   rW   r   rw   r¸   r   rz   r|   r…   r   Úlistr9   r{   Úappendr}   rF   rG   )r™   rš   r0   r@   rA   r~   r   r€   r    r„   r¬   r   rŽ   Zempr9   Z	emp_areasrF   r#   r#   r$   rT     sB    









rT   c             C   s   d|krœ|  d¡ | tfkr$d}nd| tfkrB|dkr<d}qˆd}nF| tfkrRd}n6| tfkrbd}n&| tfkrpd S | tfkrˆd	}t|d
ƒ}ddlm	} t
||d\}}tj ¡ }	|rœxæ|D ]Þ}
trÊ|
d ntj}|
d }|
d }|
d }|
d }|
d }|
d r|
d pd}t|||ƒ}tjj|d ¡ }|rTtjj|dj|||||	d n8tƒ }||_||_||_||_||_||_|	|_| ¡  ||jdƒ qºW d S )Nr<   z[*]Device Migartez³
            select i.sn, i.alias, pa.areaid, pa.areaname, i.TZAdj, i.ipaddress
            from iclock i
            left join personnel_area pa on pa.id = i.area_id
            rž   zÃ
                select i.sn, i.alias, pa.areaid, pa.areaname, i.TZAdj, i.ipaddress
                from iclock i
                left join personnel_area pa on pa.id = i.area_id
                zÅ
                select i.sn, i.alias, pa.areaid, pa.areaname, i."TZAdj", i.ipaddress
                from iclock i
                left join personnel_area pa on pa.id = i.area_id
                z¸
            select i.SN, i.Alias, c.companyid, c.companyname, i.TZAdj, i.IPAddress
            from iclock i
            left join company c on c.companyid = i.company_id
            z­
            select i.sn, i.alias, a.area_id, a.area_name , i.timezone, i.ip_address
            from iclock i 
            left join area a on a.id = i.area_id
            zÐ
                select i.sn, i.alias, a.area_code, a.area_name, i.terminal_tz, i.ip_address
                from iclock_terminal i
                left join personnel_area a on i.area_id = a.id 
            Úir   )ÚsaveCmd)r@   rj   rd   rf   rg   rh   ri   z	127.0.0.1)Úsn)ÚaliasÚterminal_tzr9   Ú
ip_addressÚ	push_timer*   )rE   r“   r”   r•   r–   r—   ry   r‘   Úmysite.utilsrÀ   r   r   r   rW   r   rw   r   r   rz   r|   r…   r¼   r„   rÁ   rÂ   rÃ   r9   rÄ   rÅ   r}   Úpk)r[   rš   r0   r@   rA   r~   rÀ   r   r€   r   r    r„   rÁ   rÂ   r›   rœ   Zdev_tzrÄ   r9   Zdevr#   r#   r$   rL   S  sX    










rL   c          
   C   sP  t  d¡}|rd S t  dd¡ d|krL| d¡ ddi}|\}}	| tfkrn|rd|	rddd ||	¡i}d| }
nL| tfkrš|r|	rdd ||	¡i}d	| }
n |r²|	r²dd
 ||	¡i}d| }
d}|rLtj 	dd¡}t
|ƒ}tj 	dd¡}t
|ƒ}| tfkrd}nf| tfkr$|dkrd}nd}nD| tfkr6d}n2| tfkrHd}n | tfkrZd}nd}t|dƒ}d}tƒ }d}d}t|| |d\}}g }x`|D ]V}|d }d}i }tr¾|d nd tj d¡¡|d< |d  d¡ d d¡|d< |d |d!< |d" |d#< |d$ r|d$ nd|d%< |d& r4|d& p6d'|d(< |d) rP|d) pRd'|d*< |d+ }|d |kr€||d  |d,< nd-}|d( |kr¤||d&  |d.< nd'|d.< |rÞ| || ¡ t|ƒd/krÞt|ƒ\}}g }|dkrš|d7 }qšW y|r
t|ƒ\}}W n> tk
rJ } ztd0|ƒ dd l}|  ¡  W d d }~X Y nX d S )1NrD   rd   rC   z[*]Transaction MigarteÚandz2=2z)punch_time >='{0}' and punch_time <='{1}'zAselect count(id) as tcount from att_punches where 1=1 and %(and)szHselect count(id) as tcount from iclock_transaction where 1=1 and %(and)sz'checktime >='{0}' and checktime <='{1}'z@select count(id) as tcount from checkinout where 1=1 and %(and)sr¬   rÇ   rÁ   a  
                select u.badgenumber, c.checktime, c.checktype, c.verifycode, c.sn_name, c.workcode, c.id from checkinout c 
                inner join userinfo u on u.badgenumber = c.pin 
                where c.id >=0 and %(and)s order by c.id
                rž   a  
                    select u.badgenumber, c.checktime, c.checktype, c.verifycode, c.SN, c.WorkCode, c.id from checkinout c 
                    inner join userinfo u on u.badgenumber = c.pin 
                    where c.id>=0 and %(and)s order by c.id
                    a  
                    select u.badgenumber, c.checktime, c.checktype, c.verifycode, c."SN", c."WorkCode", c.id from checkinout c 
                    inner join userinfo u on u.badgenumber = c.pin 
                    where c.id >=0 and %(and)s order by c.id
                    zý
                select u.badgenumber, c.checktime, c.checktype, c.verifycode, c.SN,c.WorkCode, c.id
                from checkinout c
                inner join userinfo u on c.userid = u.userid  where c.id >= 0 and %(and)s order by id
                zò
                select u.pin, c.time, c.status, c.verify, i.sn, c.workcode, c.id from transaction_  c 
                inner join employee u on u.id = c.employee_id 
                inner join iclock i on  i.id = c.iclock_id
                a  
                select u.emp_pin, c.punch_time, c.workstate, c.verifycode, '' as sn, c.workcode, c.id from att_punches c 
                inner join hr_employee u on u.id = c.employee_id 
                where c.id >=0 and %(and)s order by c.id
                zý
                select u.emp_code,c.punch_time, c.punch_state, c.verify_type, c.terminal_sn, c.work_code, c.id
                from iclock_transaction c 
                inner join personnel_employee u on c.emp_id = u.id  order by c.id
                Úcr   )r@   Trk   rb   rc   r„   r¨   r©   Z
punch_timerf   Zpunch_staterg   Zverify_typerh   ZNullZterminal_snri   Z	work_coderj   Zemp_idFZterminal_idiè  z>>insert trans error:)!r   rQ   ÚsetrE   r—   r   ry   r   rz   Úvalues_listÚdictr   r“   r”   r•   r–   r‘   r   r   rW   r,   r   rw   rx   r¸   r¹   r¾   Úlenr   rV   r   rF   rG   )r[   rš   Zperiodr0   r@   rA   ÚflagrY   r   r!   ZsqlcountÚcountsÚempsÚ	emps_dictÚdevsZ	devs_dictr~   Zlast_idZ
insert_sqlÚtrÉ   r   r€   Zinsert_listr    Úinsert_flagZatt_dictÚresr]   rF   r#   r#   r$   rU   ž  sš    
 







$

rU   c             C   s‚  d|kr~|  d¡ td| t| ƒƒ | ttfkrL|dkr>d}nd}d| }n| tfkr\d}n| tfkrjd	}t||d
\}}|r~tj	 
dd¡}	t|	ƒ}
tj	 
d¡}xÚ|D ]Ò}tj ¡ }d}i }|d  d¡}|d }||
krê|
| |d< nd}|r¨|d |d< |d |d< |d |d< |d |d< |d |d< d|d< ||d< tj	j|d |d dd}|sntf |Ž ¡  q¨|jf |Ž q¨W d S )Nr=   z[*] Finger Migratezmigrate_software:Úoraclezdbms_lob.substr(f.template)z
f.templatez·
            select u.badgenumber, %s, f.fingerid, f.valid, f.sn, f.fpversion
            from finger_template f
            left join userinfo u on f.pin = u.badgenumber
            z¾
            select u.badgenumber, f.Template, f.FingerID, f.Valid, f.SN, '10.0' as fpversion
            from template f
            left join userinfo u on f.userid = u.userid
            zÄ
            select u.pin, f.template, f.template_id, f.valid, '' as sn,  '10.0' as fpversion
            from finger_template
            left join employee u on u.id = f.employee_id
            )r@   r¬   rÇ   rÁ   Tr   r¨   rh   Úemployee_idFrd   Úbio_tmprf   Úbio_norg   Úvalidri   Ú	major_verÚbio_typer²   )Úemployee__idrÙ   rÜ   )rE   r   Útyper“   r”   r•   r–   r   r   rz   rË   rÌ   r   r   r   r¸   r
   r|   r}   r¼   )r[   rš   r0   r@   rA   Úbio_tempr~   r   r€   rÐ   rÑ   rÒ   r    r²   rÔ   Ú	temp_dictr¬   Zdev_snZ
tmp_fingerr#   r#   r$   rM   *  sL    






rM   c             C   sb  d|kr^|  d¡ | ttfkr<|dkr.d}nd}d| }n| tfkrLd}n| tfkrZd}t||d	\}}|r^tj d
d¡}	t	|	ƒ}
xÖ|D ]Î}t
j
 ¡ }d}i }|d  d¡}|d }||
krÎ|
| |d< nd}|rŒ|d |d< |d |d< |d |d< |d |d< |d |d< d|d< ||d< tjj|d |dd}|sNtf |Ž ¡  qŒ|jf |Ž qŒW d S )Nr>   z[*]Face MigraterÖ   zdbms_lob.substr(f.facetemp)z
f.facetempz¾
                select u.badgenumber, %s, f.faceid, f.sn, f.valid, f.face_ver
                from face_template f
                left join userinfo u on f.pin = u.badgenumber
            zÚ
                select u.badgenumber, f.Template as template, f.FaceID,'-' as sn, f.Valid, '7.0' as faceVersion
                from template2 f
                left join userinfo u on f.userid = u.userid
            zÄ
            select u.pin, f.template, f.template_id, '-' as sn, f.valid, '7.0' as faceversion
            from face_template
            left join employoee u on u.id = f.employee_id
            )r@   r¬   rÇ   Tr   r¨   rf   r×   Frd   rØ   Ú	bio_indexrg   rÁ   rh   rÚ   ri   rÛ   rÜ   r²   )rÝ   rá   rÜ   )rE   r“   r”   r•   r–   r   r   rz   rË   rÌ   r   r   r¸   r
   r|   r}   r¼   )r[   rš   r0   r@   rA   rß   r~   r   r€   rÐ   rÑ   r    r²   rÔ   rà   r¬   Zface_idZtmp_facer#   r#   r$   rN   e  sJ    






rN   c             C   sö  d|krò|  d¡ d}| tfkr<d}|dkr2d}d| }nŽ| tfkr`d}|dkrVd	}d
| }nj| tfkrˆd}t||d\}}	|	rÊd}nB| tfkr˜d}n2| tfkr¨d}n"| tfkrÊd}|dkrÂd	}d| }t||d\}}
|
ròtj	 
dd¡}t|ƒ}xú|
D ]ò}tj ¡ }d}i }|d  d¡}|d |d< |d |d< |d |d< ||kr\|| |d< nd}|rütj	j|d |d |d |d d}|d |d < |d! |d"< |d# |d$< |d% |d&< |d' |d(< ||d)< |sâtf |Ž ¡  qü|jf |Ž qüW d S )*Nr?   z[] Biodata Migraterb   z
b.templaterÖ   zdbms_lob.substr(b.template)zÇselect u.badgenumber, %s, b.bio_index, b.bio_no, b.bio_type, b.valid, b.majorver, b.format, b.duress
            from bio_data b
            left join userinfo u on b.pin = u.badgenumber
            z	b.bio_tmpzdbms_lob.substr(b.bio_tmp)zÑselect u.badgenumber, %s, b.bio_index, b.bio_no, b.bio_type, b.valid,b.major_ver, b.bio_format, b.duress
            from iclock_biodata b
            left join userinfo u on b.pin = u.badgenumber
            zLselect TABLE_NAME from information_schema.TABLES where TABLE_NAME='bio_data')r@   zÐselect u.badgenumber, b.template, b.bio_index, b.bio_no, b.bio_type, b.valid, b.majorver, b.format, b.duress
                    from bio_data b
                    left join userinfo u on b.userid = u.useridzè
            select u.pin, b.template, b.template, b.bio_index, b.bio_no, b.bio_type, b.valid, b.majorver, b.bio_format, b.duress
            from bio_template b 
            left join employee u on b.employee_id = u.id
            zÿ
            select u.emp_pin, b.template_data, b.template_no_index, b.template_no, b.bio_type, b.valid_flag, b.version, b.data_format, b.is_duress
            from hr_biotemplate b 
            left join hr_employee u on b.employee_id = u.id
            zûselect u.emp_code, %s, b.bio_index, b.bio_no, b.bio_type, b.valid,b.major_ver, b.bio_format, b.duress
                        from iclock_biodata b
                        left join personnel_employee u on b.employee_id = u.id
                        r¬   rÇ   Tr   r¨   rh   rÜ   rf   rá   rg   rÙ   r×   F)rÝ   rÙ   rá   rÜ   rd   rØ   ri   rÚ   r¥   Zduressrj   rÛ   rk   Z
bio_formatr²   )rE   r“   r”   r•   r   r–   r—   ry   r   rz   rË   rÌ   r   r   r¸   r
   r|   r}   r¼   )r[   rš   r0   r@   rA   r~   rß   Z	check_sqlr   Z	is_existsr€   rÐ   rÑ   r    r²   rÔ   rà   r¬   Zbio_datar#   r#   r$   rO     sn    












rO   c             C   s\   ddddddddd	g	}d
dddddœidddiig}ddddddddgdddœ
}t |ƒ d S )Nr9   r:   r;   rB   r<   rC   r=   r>   r?   Z	sqlserverZwebtrunkZbiotime7ZWDMSr–   )r   r   r   r   Zmysqlr   zweb2.0Zsqlserver_adoz10.120.11.27Z1433Zzkteco_databaseZsaZ1qazCDe3z2019-06-14 00:00:00z2019-06-01 00:00:00)
r4   r8   Zmigrate_database_addressZmigrate_database_portZmigrate_database_nameZmigrate_database_userZmigrate_database_passwordr5   r7   r6   )r^   )Zfield_indexrš   Zcategroy_databaserY   r#   r#   r$   Útest_migrateí  s    râ   )N)NN)NN)NN)NN)NN)NN)NN)NN)NN)NN)2Zmysite.base.views.intergrationr   r   r   Zmysite.personnel.modelsr   Zmysite.iclock.modelsr   r   r   r	   r
   rv   r   Zdjango.core.cacher   Ú	functoolsr   r   rÆ   r   r   Zmysite.django_sql_utilsr   r“   r”   r•   r–   r—   ry   rW   r&   r1   r^   rP   r‰   rŒ   r   r‘   rI   rJ   rK   rS   rT   rL   rU   rM   rN   rO   râ   r#   r#   r#   r$   Ú<module>   sd   2
668, #;J :7O