B
    CDbKY                 @   s   d dl mZmZ d dlT d dlZd dlmZmZ d dlm	Z	m
Z
mZ d dlmZ d dlmZ ejZdd	 Zd
d Zg df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add  Zd!d" Zd#d$ Z d%d& Z!d'd( Z"dS ))    )models
connection)*N)render_to_responserender)loaderContextRequestContext)settings)cachec             C   sh   | j dd}d}|dkr d}n6|dkr.d}n(|dkr<d}n|d	krJd
}n|dkrVd}tdt|iS )Nindex 1upgrade_progress_userinfo5upgrade_progress_attlog22upgrade_progress_carcashsz23upgrade_progress_icconsumerlist24upgrade_progress_allowanceret)GETgetgetJSResponser   )requestr   pg r   1G:\easytimepro\master/mysite/base\transferdata.pyupgrade_progress   s    r    c       
   
   C   sr  | j dd}| j dd}|rd| }y4d}t|}| }t|dkrZttd| }W n6 tk
r } zt	  dtd }W d d }~X Y nX t
t||d	S |rX| j d
d}t|t| |dkrt|rt
ddiS nd|dkrt|rLt
ddiS n@|dkr0t|rLt
ddiS n|dkrRt|rLt
ddiS n|dkrtt|rLt
ddiS n|dkrt|rLt
ddiS n|dkrt|rLt
ddiS n|dkrt|rLt
ddiS nr|dkrt|rLt
ddiS nP|dkrt|rLt
ddiS n.|dkr@t|rLt
ddiS nt
ddiS t
ddiS i }	td|	t| i S d S )NZdb_namer   stepz5select * From master.dbo.sysdatabases where name='%s'r   zDatabase (%s) does not existz%szDatabase exception)r   messageZeco_db0r   r   234r   Z20Z21r   r   r      zdatabase_upgrade_list.html)r   r   Exe_SqlfetchalllenZunicode_	Exception	traceback	print_excr   printtypeguide_departmentguide_userinfoguide_finger
guide_faceguide_iclockguide_attlogguide_issuecardguide_losecardguide_carcashszguide_icconsumerlistguide_allowancer   r	   )
r   Zdbnamer!   sqlinfocsrowseZeco_nameZccr   r   r   database_upgrade    sl    



















rA   Fc          
   C   sz   d }zVyt  }||  W n: tk
rV } ztjrFt|  td| W d d }~X Y nX W d |rt|rtt   |  X |S )NzExe_Sql ERROR:)	r   cursorZexecuter,   r
   DEBUGr/   Zcommitclose)r<   ZparamsactionrB   r@   r   r   r   r(   Z   s     
r(   c       	      C   s  dd| i }ylt |}| }xV|D ]N}d|d |d d }yt |dd W q$ tk
rp } zW d d }~X Y q$X q$W W n   t  d	S d
|  }ydt |}| }xN|D ]F}dt| |d |d d }yt |dd W q   td| Y qX qW W n
   d	S dS )Nz7 select code, DeptName from  %(zkeco)s.dbo.departments zkecoz
            if not exists(select DeptNumber from departments where DeptNumber= '%(code)s') 
                insert into departments(DeptNumber, DeptName, supdeptid) values ('%(code)s', '%(name)s', 0)
            r   r'   )codenameT)rE   FzKselect code,supdeptid from %s.dbo.departments where [supdeptid] is not NULLzupdate %(ecopro)s.dbo.departments set supdeptid = (select [DeptID] from %(ecopro)s.dbo.departments 
            where DeptNumber = (select code from %(zkeco)s.dbo.departments where DeptID = %(supdeptid)s))
            where DeptNumber = '%(code)s')ecoprorF   	supdeptidrG   zERROR:)r(   r)   r,   r-   r.   	ecopro_dbr/   )	zkeco_dbr<   r>   r?   rowi_sqlr@   rJ   updsqlr   r   r   r1   i   s4    

r1   c       
      C   s  dd| i }d}yt |}| }t|}d}t|d x|D ]}dt| |d d }yt |dd W n   td	|	 Y nX |d
 }|dkr|d dkrt||d |  dt|d
 |d d }	yt |	dd W q@   td	|	 Y q@X q@W t|d dS    dS d S )Nzselect userinfo.badgenumber, dept.code from %(zkeco)s.dbo.userinfo userinfo, %(zkeco)s.dbo.departments dept  
        where userinfo.defaultdeptid = dept.DeptID and userinfo.status=0rF   r   r   aJ  
                if not exists(select badgenumber from %(ecopro)s.dbo.userinfo where badgenumber= '%(pin)s')
                insert into %(ecopro)s.dbo.userinfo ([badgenumber],[name],[Password],[MVerifyPass],[Privilege],[AccGroup],[Gender],[Birthday],[street],[zip],[ophone],[FPHONE],[pager],[SSN],[OffDuty],[del_flag])
                select [badgenumber],[name],[Password],[MVerifyPass],[Privilege],[AccGroup],[Gender],[Birthday],[street],[zip],[ophone],[FPHONE],[pager],[SSN],0,0 
                from %(zkeco)s.dbo.userinfo where status=0 and badgenumber= '%(pin)s'
                )rI   rF   pinT)rE   zERROR:r'   i  d   z
            update %(ecopro)s.dbo.userinfo set defaultdeptid = (select deptid from %(ecopro)s.dbo.departments where DeptNumber = '%(deptid)s') where badgenumber = '%(pin)s'
            )rI   ZdeptidrP   F)r(   r)   r*   r   setrK   r/   )
rL   Zs_userr   r>   r?   countirM   ins_sqlrO   r   r   r   r2      s4    
r2   c             C   sj   ddt i }dt | d }yt|dd W n tk
rH   td| Y nX yt|dd dS    dS d S )	Nz5delete from %(ecopro)s.dbo.bio_data where bio_type =1rI   aT  
    insert into  %(ecopro)s.dbo.bio_data(userid,bio_tmp, bio_no, bio_index, bio_type, majorver, valid, duress)
    select userid, Template, FingerID, 0, 1,Fpversion, 1, 0 from
    (select userinfo.userid,zkeco.* from %(zkeco)s.dbo.finger_template zkeco, %(ecopro)s.dbo.userinfo userinfo where zkeco.pin = userinfo.badgenumber) tab    
    )rI   rF   T)rE   zError:F)rK   r(   r,   r/   )rL   Zclear_fingerZ
finger_sqlr   r   r   r3      s    r3   c             C   s   ddt i }dt | d }yt|dd t|}| }x|D ]}i }d| |d |d	 d
 }y`t|}| }	|	rx|	D ]}
|
d ||
d < qzW dt |d t||d	 d }t|dd W q>   t  Y q>X q>W dS    t  dS d S )Nz5delete from %(ecopro)s.dbo.bio_data where bio_type =2rI   zselect userinfo.userid,userinfo.badgenumber, zkeco.face_ver from %(zkeco)s.dbo.face_template zkeco,%(ecopro)s.dbo.userinfo userinfo where zkeco.pin = userinfo.badgenumber
        group by userinfo.userid,userinfo.badgenumber, zkeco.face_ver)rI   rF   T)rE   zwselect faceid, facetemp,face_ver,valid from %(zkeco)s.dbo.face_template where pin = '%(pin)s' and face_ver=%(face_ver)sr'      )rF   rP   face_verr   zinsert into  %(ecopro)s.dbo.bio_data(userid,bio_tmp, bio_no, bio_index, bio_type, majorver, valid, duress)
                        values (%(userid)s, '%(bio_tmp)s', 0, 0, 2, '%(face_ver)s', 1, 0)
                    )rI   useridZbio_tmprW   F)rK   r(   r)   Zdumps1r-   r.   )rL   Z
clear_faceZ
face_plistr>   r?   rM   Zface_dicZface_sqlZdsZfacesZfacerU   r   r   r   r4      s0    


r4   c             C   s0   d}| dkrd}n| dkr d}n| dkr,d}|S )N	   r'   rV         r   )Zdtypedev_typer   r   r   get_producttype   s    r]   c             C   s   dd| i }ytt |}| }x^|D ]V}t|d }dt|d |d |d |d }yt |d	d
 W q$   t  Y q$X q$W d	S    t  dS d S )NzB SELECT sn, alias, UpdateDB, device_type FROM %(zkeco)s.dbo.iclockrF      z
            if not exists(select sn from %(ecopro)s.dbo.iclock where SN= '%(sn)s') insert into %(ecopro)s.dbo.iclock(State,AccFun,TZAdj,SN, Alias, UpdateDB, ProductType) 
            values (1,0,8,'%(sn)s', '%(alias)s', '%(UpdateDB)s', %(ProductType)s)r   r'   rV   )rI   snaliasZUpdateDBZProductTypeT)rE   F)r(   r)   r]   rK   r-   r.   )rL   r<   r>   r?   rM   r\   rN   r   r   r   r5      s     
 
r5   c          	   C   s  dd l }|jdd}|j  | }dt| |dd }d}yt|}| }t|}d}	t	|d x|D ]}
d	t|
d |
d |
d
 |
d |
d d }y@t|dd |	d }	|dkr|	d dkrt	||	d |  W ql   t
  Y qlX qlW t	|d dS    t
  dS d S )Nr      )Zdaysad   select checkinout.checktime, checkinout.checktype,checkinout.verifycode,iclock.SN,userinfo.userid 
            from %(zkeco)s.dbo.checkinout as checkinout, %(ecopro)s.dbo.userinfo as userinfo, %(ecopro)s.dbo.iclock as iclock 
            where checkinout.pin = userinfo.badgenumber and checkinout.sn_name = iclock.SN and checkinout.checktime > '%(stime)s'z%Y-%m-01)rI   rF   Zstimer   r'   a0  
            if not exists(select userid from %(ecopro)s.dbo.checkinout where userid= '%(userid)s' and checktime='%(checktime)s') insert into %(ecopro)s.dbo.checkinout(checktime, checktype, verifycode, SN, userid) 
            values ('%(checktime)s', '%(checktype)s', %(verifycode)s,'%(SN)s',%(userid)s)rV   r^      )rI   	checktimeZ	checktypeZ
verifycodeZSNrX   T)rE   i'  i  rQ   F)datetimeZ	timedeltaZnowrK   strftimer(   r)   r*   r   rR   r-   r.   )rL   rd   ZdeltaZvtimer<   r   r>   r?   rS   rT   rM   rN   r   r   r   r6   	  s2    
*r6   c              C   sT   i } d}y2t |}| }x|D ]}|d | |d < q W W n   t  Y nX | S )Nz) SELECT badgenumber,userid from userinfo r'   r   )r(   r)   r-   r.   )Z	dict_userr<   r>   r?   rM   r   r   r   load_userinfo,  s    
rf   c             C   s   t s
t a dd| i }yt|}| }x|D ]}|d t kr.dtt |d  |d |d |d |d |d	 |d
 |d |d |d |d |d d }yt|dd W q.   t  Y q.X q.W dS    t  dS d S )Nad  select userinfo.badgenumber, c.cardno, c.cardstatus, CONVERT(varchar(100),c.issuedate,20) issuedate, c.type_id, c.blance, c.mng_cost, c.card_cost, c.Password, c.card_privage, c.sys_card_no, c.card_serial_num 
        from %(zkeco)s.dbo.userinfo userinfo, %(zkeco)s.dbo.personnel_issuecard c where userinfo.userid = c.UserID_id and (sys_card_no is not NULL)rF   r   aJ  
                if not exists(select UserID_id from %(ecopro)s.dbo.ipos_issuecard where UserID_id='%(userid)s' and cardno='%(cardno)s' and sys_card_no='%(sys_card_no)s')
                insert into %(ecopro)s.dbo.ipos_issuecard(UserID_id, cardno, cardstatus, issuedate, itype_id, blance, mng_cost, card_cost, Password, card_privage, sys_card_no, card_serial_num) 
                values ('%(userid)s', '%(cardno)s', %(cardstatus)s,'%(issuedate)s',%(itype_id)s,%(blance)s,%(mng_cost)s,%(card_cost)s,'%(Password)s',%(card_privage)s,'%(sys_card_no)s',%(card_serial_num)s)
                r'   rV   r^   rb   rZ            rY   
   r[   )rI   rX   cardno
cardstatusZ	issuedateZitype_idblanceZmng_costZ	card_costPasswordZcard_privagesys_card_nocard_serial_numT)rE   F)	user_dictrf   r(   r)   rK   r-   r.   )rL   r<   r>   r?   rM   rN   r   r   r   r7   :  s"     
Xr7   c             C   s   t s
t a dd| i }yt|}| }xx|D ]p}|d t kr.dtt |d  |d |d |d |d |d	 |d
 d }yt|dd W q.   t  Y q.X q.W dS    t  dS d S )NzSELECT userinfo.badgenumber, l.sys_card_no,l.cardno,l.cardstatus,l.Password,CONVERT(varchar(100),l.time,20) time,l.type_id
        FROM  %(zkeco)s.dbo.userinfo userinfo, %(zkeco)s.dbo.pos_loseunitecard l where userinfo.userid = l.UserID_idrF   r   a  
                if not exists(select UserID_id from %(ecopro)s.dbo.ipos_loseunitecard where sys_card_no= '%(sys_card_no)s' and cardno='%(cardno)s')
                insert into %(ecopro)s.dbo.ipos_loseunitecard(UserID_id, sys_card_no, cardno, cardstatus, Password, Losetime, itype_id) 
                values ('%(userid)s', '%(sys_card_no)s', '%(cardno)s',%(cardstatus)s,'%(Password)s','%(time)s',%(type_id)s)
                r'   rV   r^   rb   rZ   rg   )rI   rX   ro   rk   rl   rn   timetype_idT)rE   F)rq   rf   r(   r)   rK   r-   r.   )rL   r<   r>   r?   rM   rN   r   r   r   r8   V  s"     
:r8   c       	      C   sN  t s
t a dd| i }d}yt|}| }t|}d}t|d x|D ]}|d t krLdtt |d  |d |d |d |d	 |d
 |d |d |d |d |d |d |d d }y@t|dd |d }|dkr|d dkrt||d |  W qL tk
r"   t	
  td| Y qLX qLW t|d dS    t	
  dS d S )Na  select userinfo.badgenumber,cz.card,cz.sys_card_no,ISNULL(sn_name,'') as sn_name,  ISNULL(cz.cardserial,0) as cardserial, ISNULL(serialnum,0) as serialnum,CONVERT(varchar(100),cz.checktime,20) checktime,cz.money,cz.discount,cz.hide_column,cz.blance,cz.log_flag,cz.type_id  
    from %(zkeco)s.dbo.userinfo userinfo, %(zkeco)s.dbo.pos_carcashsz cz where (userinfo.userid = cz.user_id) and (cz.sys_card_no is not NULL)
    rF   r   r   a<  
                if not exists(select UserID_id from %(ecopro)s.dbo.ipos_cardcashsz where sys_card_no='%(sys_card_no)s' and cardserial=%(cardserial)s and hide_column=%(hide_column)s)
                insert into %(ecopro)s.dbo.ipos_cardcashsz(UserID_id,card,sys_card_no,sn,cardserial,serialnum,checktime,money,discount,hide_column,blance,log_flag,CashType_id) 
                values (%(userid)s, '%(card)s','%(sys_card_no)s', '%(sn)s',%(cardserial)s,%(serialnum)s,'%(checktime)s',%(money)s,%(discount)s,%(hide_column)s,%(blance)s,%(log_flag)s,%(type_id)s)
                r'   rV   r^   rb   rZ   rg   rh   ri   rY   rj   r[      )rI   rX   cardro   r_   Z
cardserialZ	serialnumrc   moneyZdiscountZhide_columnrm   log_flagrs   T)rE   i'  i  rQ   ZERRORF)rq   rf   r(   r)   r*   r   rR   rK   r,   r-   r.   r/   )	rL   r<   r   r>   r?   rS   rT   rM   rN   r   r   r   r9   p  s4     
^r9   c       	      C   sV  t s
t a dd| i }d}y"t|}| }t|}d}t|d x|D ]}|d t krLdtt |d  |d |d |d |d |d	 |d
 |d |d |d |d |d |d |d |d |d |d d }yDt|dd |d }|dkr|d dkrt||d |  W qL   t	  Y qLX qLW t|d dS    t	  dS d S )Na<  SELECT user_pin,user_name,card,sys_card_no,dev_sn,card_serial_num,dev_serial_num,CONVERT(varchar(100),pos_time,20) pos_time,CONVERT(varchar(100),convey_time,20) convey_time,type_name,money,balance,pos_model,CONVERT(varchar(100),meal_data,20) meal_data, log_flag,meal_id 
        FROM %(zkeco)s.dbo.pos_icconsumerlistrF   r   r   a  
                if not exists(select sys_card_no from %(ecopro)s.dbo.ipos_icconsumerlist where sys_card_no='%(sys_card_no)s' and card_serial_num = '%(card_serial_num)s' and pos_time = '%(pos_time)s')
                insert into %(ecopro)s.dbo.ipos_icconsumerlist(user_id,user_pin,user_name,card,sys_card_no,dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,type_name,money,balance,pos_model,meal_data, log_flag,meal_id,create_operator) 
                values (%(userid)s, '%(user_pin)s','%(user_name)s','%(card)s','%(sys_card_no)s','%(dev_sn)s',%(card_serial_num)s,%(dev_serial_num)s,'%(pos_time)s','%(convey_time)s',%(type_name)s,%(money)s,%(balance)s,%(pos_model)s,'%(meal_data)s',%(log_flag)s,%(meal_id)s,'')
                r'   rV   r^   rb   rZ   rg   rh   ri   rY   rj   r[   rt            )rI   rX   Zuser_pinZ	user_nameru   ro   Zdev_snrp   Zdev_serial_numZpos_timeZconvey_timeZ	type_namerv   ZbalanceZ	pos_modelZ	meal_datarw   Zmeal_idT)rE   i'  i  rQ   F)
rq   rf   r(   r)   r*   r   rR   rK   r-   r.   )	rL   r<   r   r>   r?   rS   rT   rM   rN   r   r   r   r:     s2     
vr:   c       	      C   s4  t s
t a dd| i }d}y t|}| }t|}d}t|d x|D ]}|d t krLdtt |d  |d |d |d |d	 |d
 |d |d |d |d |d |d d }y@t|dd |d }|dkr|d dkrt||d |  W qL   t	  Y qLX qLW t|d dS    t	  dS d S )Na  SELECT userinfo.badgenumber,a.money,isnull(a.receive_money,0) receive_money,a.is_pass,ISNULL(CONVERT(varchar(100),a.valid_date,20),'') valid_date,ISNULL(CONVERT(varchar(100),a.allow_date,20),'') allow_date,ISNULL(CONVERT(varchar(100),a.receive_date,20),'') receive_date,ISNULL(a.batch,0) batch,isnull(a.base_batch,0) base_batch,a.is_ok,a.is_transfer,a.sys_card_no
        FROM %(zkeco)s.dbo.userinfo userinfo,%(zkeco)s.dbo.pos_allowance a where userinfo.userid = a.user_idrF   r   r   a  
                if not exists(select UserID_id from %(ecopro)s.dbo.ipos_allowance where sys_card_no='%(sys_card_no)s' and batch = '%(batch)s')
                insert into %(ecopro)s.dbo.ipos_allowance(UserID_id,money,receive_money,is_pass,valid_date,allow_date,receive_date,batch,base_batch,is_ok,is_transfer,sys_card_no) 
                values (%(userid)s, %(money)s,%(receive_money)s,%(is_pass)s,'%(valid_date)s','%(allow_date)s','%(receive_date)s',%(batch)s,%(base_batch)s,%(is_ok)s,%(is_transfer)s,'%(sys_card_no)s')
                r'   rV   r^   rb   rZ   rg   rh   ri   rY   rj   r[   )rI   rX   rv   Zreceive_moneyZis_passZ
valid_dateZ
allow_dateZreceive_dateZbatchZ
base_batchZis_okZis_transferro   T)rE   i'  i  rQ   F)
rq   rf   r(   r)   r*   r   rR   rK   r-   r.   )	rL   r<   r   r>   r?   rS   rT   rM   rN   r   r   r   r;     s2     
Xr;   )#Z	django.dbr   r   Zmysite.utilsr-   Zdjango.shortcutsr   r   Zdjango.templater   r   r	   mysiter
   Zdjango.core.cacher   ZDATABASE_NAMErK   r    rA   r(   r1   r2   r3   r4   r]   r5   r6   rf   rq   r7   r8   r9   r:   r;   r   r   r   r   <module>   s0   :&'
#$"