B
    b                 @   s   d dl Z d dlmZ d dlZd dl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 G d	d
 d
eZdS )    N)datetime)IntegrityError)
connection)	AutoField)gettext_lazy)xldate_as_tuple)	get_modelc               @   s   e Zd Zd9ddZdd Zdd Zd	d
 Zdd Zddg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!d" Zd#d$ Zd%d& Zd'd( Zd)d* Zd+d, Zd-d. Zd/d0 Zd1d2 Zd3d4 Zd5d6 Zd7d8 ZdS ):
ImportDataimport_dataNc             C   s   d| _ ddddg| _|| _d| _g | _d| _d| _d| _d| _d| _	g | _
g | _g | _g | _g | _i | _g | _d| _|| _ d| _| jjd	 | _d
| _| jdkrd| _nt| j| _|r|rt||| _	n| j
dtd  dS )u   初始化基础类r
   xlscsvtxtxlsxN
sql_serverTZimport_typeP   0Fz%sZmodule_parameter_error)
input_nameZvalid_formatrequestheadrecordsformatupload_file	app_label
model_name	model_cls
error_infovalid_head_indexsvalid_model_fieldsother_fieldscalculate_fields_verbosecalculate_fields_indexmust_fieldscurrent_dbtypeneed_read_dataZPOSTneed_update_old_recordsql_batch_cntsboolr   append_)selfZreqr   r   r    r*   5G:\easytimepro\master/mysite/personnel\import_data.py__init__#   s8    
zImportData.__init__c             C   s   d}| j jr| j jd }t|d}ddddg}y&|t|d  |d | _|| _W q   d	}| jd
t	d  Y qX nd	}| jd
t	d  |S )uC   验证文件格式,True标示验证成功，False标示验证失败TZimport_file.r   r   r   r      Fz%sZinvalid_file_formatZplease_select_file)
r   ZFILESstrsplitindexr   r   r   r'   r(   )r)   retfZf_formatformat_listr*   r*   r+   validate_formatI   s    

zImportData.validate_formatc             C   sP   d}| j dkr|  }| j dkr(|  }| j dkr:|  }| j dkrL|  }|S )u$   得到文件的头部数据和记录Tr   r   r   r   )r   xls_readtxt_readcsv_read	xlsx_read)r)   r2   r*   r*   r+   get_file_data\   s    



zImportData.get_file_datac             C   sj   | j rfx^| j D ]T}d}x| jD ]}||krd}P qW |r>d}q| jddtdd|i i  dS qW dS )NFTz%(nd)sZndzthe_%(index)s_field_is_requirdr1   )r!   r   r   r'   r(   )r)   eflagelemr*   r*   r+   check_must_fieldsj   s    zImportData.check_must_fieldsc          
   C   s  zny| j  }tj|d}W nN tk
rj } z0ddl}|  | jdt	d d|i  dS d}~X Y nX W d| j 
  X |jdkr| jdt	d	  dS |d}g }|jd
k r| jdt	d  dS x0t|jD ] }g }xt|jD ]}	|||	j}
|j||	d}|
d
kr4|d dkr4t|}n@|
dkrXtt|d }|d}n|
dkrt|dkrpdnd}t|tdtdfkrt|}|d|  qW |dkr|| _|   | js| jdt	d  dS |  }|sdS q|| qW || _dS )u   读xls数据)Zfile_contentsr   Nz%szinvalid_file_format:%(index)sr1   Fr.   Zfile_sheet_shoulb_be_one   file_data_must_be_more_than_two)ZrowxZcolx   z%Y-%m-%d   Tg333333?Zno_matched_fields)r   readxlrdZopen_workbook	Exception	traceback	print_excr   r'   r(   closeZnsheetsZsheet_by_indexZnrowsrangeZncolsZcellctypeZ
cell_valueintr   r   strftimetyper   mapping_modelr   r>   r   )r)   fdZbookr;   rF   ZshZsh_datairowjrJ   vdater2   r*   r*   r+   r6   |   sX    







zImportData.xls_read	,c       	      C   s  g }zp| j   }tjrVt|tsy|d}W q tk
rR   |d}Y qX n8t|t	sy|d}W n tk
r   |d}Y nX |s| j
dtd  dS |d}t|dk r| j
dtd  dS xtt|D ]}|| |d	 d
kr|d	 }n|d }||  |}|dkrfdd || |D | _|   |  }|spdS q|| qW W d| j   X || _dS )uK   默认txt,csv只支持	隔开,这个可以根据自己的需要自己重写zutf-8gb18030z%sZfile_data_cannot_be_emptyFz
r?   r@   r.   r   c             S   s   g | ]}|  qS r*   )strip).0r;   r*   r*   r+   
<listcomp>   s    z*ImportData.simple_read.<locals>.<listcomp>NT)r   rC   rY   sixZPY3
isinstancer/   decoderE   Zunicoder   r'   r(   r0   lenrI   findr   rN   r>   rH   r   )	r)   Z
split_charr   Zall_dataZrowsr1   Z	split_strZrow_listr2   r*   r*   r+   simple_read   sH    




zImportData.simple_readc             C   s   |   S )u   读txt文件)ra   )r)   r*   r*   r+   r7      s    zImportData.txt_readc             C   s   |   S )u   读csv文件)ra   )r)   r*   r*   r+   r8      s    zImportData.csv_readc             C   s   |   S )N)r6   )r)   r*   r*   r+   r9      s    zImportData.xlsx_readc          
   C   s   ylddl m} d|jkr |  S d|jkr2|  S d|jkrD|  S d|jkr\d| _|  S d| _|  S W nF tk
r } z(dd l	}|
  | jdtd	  W d d }~X Y nX d
S )Nr   )settingsZmysqlr   Zoracle
postgresqlZpostgresql_psycopg2z%sZOthere_was_a_database_error_when_importing_please_check_the_validity_of_the_dataF)mysiterb   ZDATABASE_ENGINEmysql_insertsqlserver_insertoracle_insertr"   postgresql_insertrE   rF   rG   r   r'   r(   )r)   rb   r;   rF   r*   r*   r+   insert_data   s&    



 zImportData.insert_datac             C   sF   |s:|dkrdt | }qB| }|dkr4d| }qBd}nd| }|S )u0   分析字段和值，得到正确的数据库值)TFz%sN)rK   get_default)r)   	tmp_field	tmp_valuedfvaluer*   r*   r+   get_db_value  s    
zImportData.get_db_valuec             C   sF   |s:|dkrdt | }qB| }|dkr4d| }qBd}nd| }|S )u0   分析字段和值，得到正确的数据库值)TFz%sN)rK   rj   )r)   rk   rl   rm   r*   r*   r+   get_oracle_db_value  s    
zImportData.get_oracle_db_valuec       
      C   s   g }g }| j jj}g }i }x~tt| jD ]l}| j|  }|| jkrV||krV|||< x>|D ]6}t|t	kr\d|j
 }	|	|kr\|| || q\W q*W x*|D ]"}||krt|t	kr|| qW || _|| _|| _|| _| jrdS dS )uB   找到模型对象的字段对应导入文件中的头的列序号z%sTF)r   _metafieldsrI   r_   r   rY   r   rM   r   verbose_namer'   r   r   r   r    )
r)   r   r   rq   r   r    r1   Z	head_elemr3   rr   r*   r*   r+   rN   '  s4    




zImportData.mapping_modelc             C   s   dS )u(   插入前的准备,提供给特殊处理Tr*   )r)   r*   r*   r+   before_insertG  s    zImportData.before_insertc             C   s   dS )u   分析插入的数据Tr*   )r)   Zrecord_dictr*   r*   r+   records_analysisK  s    zImportData.records_analysisc                s.  | j jj}dd | jD }dd | jD }t }t| j}g }x| j	D ]}i }i }	d}
xt
|D ]}x*| j D ]\}}||krv|| |	|< qvW || jkrf||  | j|
 }|jr؇ fdd|jD }|r|d  ||
 }| | }d||
  d }|||< |
d }
qfW d}xh| jD ]^}| }|dkr8d}n |d	krPd
t| }nd
| }d||  d }|||< |d }qW | ||	}|| t|| jkrJt }y| ||| |  W n   Y nX g }qJW |ry"t }| ||| |  W n   Y nX yt  W n   Y nX dS )u   sqlserver 数据插入c             S   s   g | ]}|  d  qS )r.   )get_attname_column)rZ   r;   r*   r*   r+   r[   R  s    z0ImportData.postgresql_insert.<locals>.<listcomp>c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[   S  s    r   c                s    g | ]}|d   kr|d qS )r.   r   r*   )rZ   r;   )rl   r*   r+   r[   f  s    "r.   N)TFz%s)r   rp   db_tabler   r   r   cursorr_   r   r   rI   r    itemsr   choicesrn   rj   rK   process_rowr'   r%   exe_sqlserverrH   _commit)r)   model_tablev_f_db_nameso_f_db_namesrx   
count_headsql_listrQ   row_fields_selectcalculate_dictcount_vr1   krS   rk   tvkeyvaluecount_or3   default_valuer*   )rl   r+   rh   O  st    






zImportData.postgresql_insertc                s  | j jj}dd | jD }dd | jD }t }t| j}g }xj| j	D ]^}i }i }	d}
xt
|D ]}x*| j D ]\}}||krv|| |	|< qvW || jkrf||  | j|
 }|jr؇ fdd|jD }|r|d  ||
 }| | }|dkr|sP |||< |
d }
qfW d}x`| jD ]V}| }|dkr6d}n |d	krNd
t| }nd
| }|| }|||< |d }qW | ||	}|| t|| jkrJ| ||| g }qJW |r| ||| yt  W n   Y nX dS )u   sqlserver 数据插入c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[     s    z/ImportData.sqlserver_insert.<locals>.<listcomp>c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[     s    r   c                s    g | ]}|d   kr|d qS )r.   r   r*   )rZ   r;   )rl   r*   r+   r[     s    Zbadgenumberr.   N)TFz%s)r   rp   rw   r   r   r   rx   r_   r   r   rI   r    ry   r   rz   rn   rj   rK   r{   r'   r%   r|   r}   )r)   r~   r   r   rx   r   r   rQ   r   r   r   r1   r   rS   rk   r   r   r   r   r3   r   r*   )rl   r+   rf     sb    






zImportData.sqlserver_insertc          
   C   s`  |dkrd}nd}d}g }|}||||||| j d}| | |d rvg }	g }
g }x|d D ]}t| }d}xt|D ]}|d7 }q|W |d	d
 }||d| |d }dd | D }|
|7 }
|| |	| q^W y"d|	}t|
}
|	||
 W nn t
k
rt } zNxHt|	D ]<\}}y$|rN|| rN|	|||  W n   Y nX q$W W d	d	}~X Y nX | j r\|d r\|d d r\|d o|d d }|d o|d d }
|d o|d d }y"d|}t|
}
|	||
 W n` tk
rZ   xHt|D ]<\}}y$|r@|| r@|	|||  W n   Y nX qW Y nX d	S )u3   当批量插入报错后，改为一条一条插入ZuserinfozxSET IDENTITY_INSERT userinfo ON;INSERT INTO %(table)s ( %(fields)s ) VALUES ( %(row)s );SET IDENTITY_INSERT userinfo OFFz7INSERT INTO %(table)s ( %(fields)s ) VALUES ( %(row)s )z:UPDATE  %(table)s SET %(fields_value)s WHERE %(condition)s)
INSERT_SQL
UPDATE_SQLupdate_recordsinsert_recordsr   r~   update_old_recordr    z%s,NrX   rV   )tablerq   rQ   c             S   s   g | ]}|qS r*   r*   )rZ   rS   r*   r*   r+   r[     s    z,ImportData.exe_sqlserver.<locals>.<listcomp>;r   r   r.   r?   )r$   rt   r_   keysrI   joinvaluesr'   tupleexecuterE   	enumerater   )r)   rx   r~   r   r   r   r   r   r2   temp_insert_recordsparamsmulti_sql_paramsrecordfield_count	sql_paramrP   
insert_sqlparamr;   sqlmulti_params
update_sqlr*   r*   r+   r|     st    





 
zImportData.exe_sqlserverc                s
  | j jj}dd | jD }dd | jD }t| j}g }i }g }x| jD ]x}i }	d}
xt|D ]}x*| j	
 D ]\}}||krr|| ||< qrW || jkrb||  | j|
 }|jrԇ fdd|jD }|r|d  ||
 }| | }||	|< |
d }
qbW d}xj| jD ]`}| }|dks(|dkr.d}n |d	krFd
t| }nd
| }|| }||	|< |d }qW | |	|}	||	 |sd|	 }t|dkrJt }| ||| |  g }qJW |rt }| ||| |  yt  W n   Y nX dS )u   mysql 数据插入c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[      s    z+ImportData.mysql_insert.<locals>.<listcomp>c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[   !  s    r   c                s    g | ]}|d   kr|d qS )r.   r   r*   )rZ   r;   )rl   r*   r+   r[   3  s    r.   Nr   )TFz%srV      )r   rp   rw   r   r   r_   r   r   rI   r    ry   r   rz   rn   rj   rK   r{   r'   r   r   r   rx   	exe_mysqlrH   r}   )r)   r~   r   r   r   r   r   insert_keysrQ   r   r   r1   r   rS   rk   r   r   r   r   r3   r   rx   r*   )rl   r+   re     sj    





zImportData.mysql_insertc          
   C   sD  d}d}g }|}||||||| j d}| | |d rZg }	g }
g }x|d D ]}t| }d}xt|D ]}|d7 }qnW |dd }||d	| |d
 }dd | D }|
|7 }
|| |	| qPW y"d|	}t|
}
|	||
 W n` t
k
rX   xHt|	D ]<\}}y$|r>|| r>|	|||  W n   Y nX qW Y nX | j r@|d r@|d d r@|d o|d d }|d o|d d }
|d o|d d }y"d|}t|
}
|	||
 W n` t
k
r>   xHt|D ]<\}}y$|r$|| r$|	|||  W n   Y nX qW Y nX dS )u3   当批量插入报错后，改为一条一条插入z7INSERT INTO %(table)s ( %(fields)s ) VALUES ( %(row)s )z:UPDATE  %(table)s SET %(fields_value)s WHERE %(condition)s)r   r   r   r   r   r~   r   r   r   z%s,NrX   rV   )r   rq   rQ   c             S   s   g | ]}|qS r*   r*   )rZ   rS   r*   r*   r+   r[   ~  s    z(ImportData.exe_mysql.<locals>.<listcomp>r   r   r   r.   r?   )r$   rt   r_   r   rI   r   r   r'   r   r   r   r   )r)   rx   r~   r   r   r   r   r   r2   r   r   r   r   r   r   rP   r   r   r   r   r   r*   r*   r+   r   \  sp    





 
zImportData.exe_mysqlc                s  | j jj}dd | jD }dd | jD }t| j}g }i }g }x~| jD ]r}i }	d}
xt|D ]}x*| j	
 D ]\}}||krr|| ||< qrW || jkrb||  | j|
 }|jrԇ fdd|jD }|r|d  ||
 }| | }d| |	|< |
d }
qbW d}x`| jD ]V}| }|dkr(d}n |d	kr@dt| }nd| }|| }||	|< |d }qW | |	|}	||	 |sd
|	 }t|dkrJt }| ||| |  g }qJW |rt }| ||| |  yt  W n   Y nX dS )u   oracle 数据插入c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[     s    z,ImportData.oracle_insert.<locals>.<listcomp>c             S   s   g | ]}|  d  qS )r.   )ru   )rZ   r;   r*   r*   r+   r[     s    r   c                s    g | ]}|d   kr|d qS )r.   r   r*   )rZ   r;   )rl   r*   r+   r[     s    z%sr.   N)TFrV   r   )r   rp   rw   r   r   r_   r   r   rI   r    ry   r   rz   ro   rj   rK   r{   r'   r   r   r   rx   
exe_oraclerH   r}   )r)   r~   r   r   r   r   r   r   rQ   r   r   r1   r   rS   rk   r   r   r   r   r3   r   rx   r*   )rl   r+   rg     sj    






zImportData.oracle_insertc          
   C   s4  d}d}g }|}||||||| j d}| | |d rLg }	g }
g }x|d D ]}t| }d}xt|D ]}|d7 }qnW |dd }||d	| |d
 }dd | D }|
|7 }
|t| |	| qPW y|	|| W n` t
k
rJ   xHt|	D ]<\}}y$|r0|| r0||||  W n   Y nX qW Y nX | j r0|d r0|d d r0|d o|d d }|d o|d d }
|d o|d d }y |d d d }|	|| W n` t
k
r.   xHt|D ]<\}}y$|r|| r||||  W n   Y nX qW Y nX dS )u3   当批量插入报错后，改为一条一条插入z7INSERT INTO %(table)s ( %(fields)s ) VALUES ( %(row)s )z:UPDATE  %(table)s SET %(fields_value)s WHERE %(condition)s)r   r   r   r   r   r~   r   r   r   z%s,NrX   rV   )r   rq   rQ   c             S   s   g | ]}|qS r*   r*   )rZ   rS   r*   r*   r+   r[     s    z)ImportData.exe_oracle.<locals>.<listcomp>r   r   r.   r?   )r$   rt   r_   r   rI   r   r   r'   r   Zexecutemanyr   r   r   )r)   rx   r~   r   r   r   r   r   r2   r   r   r   r   r   r   rP   r   r   r   r   r   r*   r*   r+   r     sj    



 zImportData.exe_oraclec             C   s   dS )u   插入之后Tr*   )r)   r*   r*   r+   after_insert/  s    zImportData.after_insertc             C   s   |S )u*   特殊情况给开发人员提供的接口r*   )r)   Zrow_datar   r*   r*   r+   r{   3  s    zImportData.process_rowc             C   s   dS )u   postgresql 数据插入Nr*   )r)   r*   r*   r+   rc   7  s    zImportData.postgresqlc             C   sn   | j r
dS | jr4|   | j r"dS |   | j r4dS |   | j rFdS |   | j rXdS |   | j rjdS dS )ua   导入默认流程,成功返回True,不成功返回False,错误信息存储在error_info列表中FN)r   r#   r5   r:   rs   ri   r   )r)   r*   r*   r+   exe_import_data;  s$    zImportData.exe_import_data)r
   NN)__name__
__module____qualname__r,   r5   r:   r>   r6   ra   r7   r8   r9   ri   rn   ro   rN   rs   rt   rh   rf   r|   re   r   rg   r   r   r{   rc   r   r*   r*   r*   r+   r	   "   s4   
&7/ E=L?R?Br	   )typesr   r\   rD   Z	django.dbr   r   Zdjango.db.models.fieldsr   Zdjango.utils.translationr   r(   r   Zmysite.admin.utilsr   objectr	   r*   r*   r*   r+   <module>   s   