U
    ˱}j;                     @   sn   d dl mZmZ d dlmZ d dlmZmZmZ d dl	m
Z
mZ d dlmZ d dlmZ ddd	Zd
d ZdS )    )requestjsonify)get_db)get_user_id_from_tokenbuild_locationget_admin_from_token)error_responsebuild_search_conditions)get_config_value)check_sensitive_wordsTc                 C   sv   t  }| }zX|d| f | }|s2W :dS td|}|rHW $dS |r`|d |kr`W dS W dS |  X dS )uf   
    检查用户是否有权限操作技能
    返回 (has_permission, is_admin, error_message)
    zCSELECT publisher_id FROM skills WHERE id = ? AND deleted_at IS NULL)FF   技能不存在N)TTNpublisher_id)FFu   无权操作此技能)TFN)r   cursorcloseexecutefetchoner   )skill_iduser_idrequire_ownerconnr   skillis_admin r   ;D:\Project\Python Project\BangBang\backend\routes\skills.pycheck_skill_permission   s    
r   c           	         s   | j ddgd fdd}| j ddgddd }| j d	dgdd
d }| j ddgddd }| j ddgddd }| j ddgddd }| j ddgddd }dd  dd }d S )Nz/api/skillsGET)methodsc                     sb  t jd} tt jdd}tt jdd}|d | }t }| }zdddd	d
dg}t|| \}}|r|d| | |	 d }	|d| d|||f n&|d |	 d }	|d||f |
 }
ttdd |
D } |}g }|
D ]<}t|}||d d|d< t|dd|d< || q t||	||dW S |  X d S )Nsearchpage   limit
   zs.titlezs.descriptionz
s.provincezs.cityz
s.districtzs.streetz8SELECT COUNT(*) FROM skills s WHERE s.deleted_at IS NULLr   z
                    SELECT s.*, u.phone as publisher_phone
                    FROM skills s
                    LEFT JOIN users u ON s.publisher_id = u.id
                    WHERE s.deleted_at IS NULLzQ
                    ORDER BY s.created_at DESC LIMIT ? OFFSET ?
                z4SELECT COUNT(*) FROM skills WHERE deleted_at IS NULLa  
                    SELECT s.*, u.phone as publisher_phone
                    FROM skills s
                    LEFT JOIN users u ON s.publisher_id = u.id
                    WHERE s.deleted_at IS NULL ORDER BY s.created_at DESC LIMIT ? OFFSET ?
                c                 s   s   | ]}|d  r|d  V  qdS )r   Nr   ).0rowr   r   r   	<genexpr>K   s      z<register_skill_routes.<locals>.get_skills.<locals>.<genexpr>r   -Zacceptor_rating_rateTinclude_detaillocation)itemstotalr   r    )r   argsgetintr   r   r   r	   r   r   fetchalllistsetdictr   appendr   )r   r   r    offsetr   r   search_fieldssearch_condsearch_paramsr*   rowspublisher_idsZacceptor_rating_mapr)   r#   r   get_bulk_user_rating_ratesr   r   
get_skills&   sL    

z)register_skill_routes.<locals>.get_skillsz/api/skills/<int:skill_id>c                 S   sh   t  }| }zJ|d| f | }|s6tdW &S t|}t|dd|d< t|W S |  X d S )Nz8SELECT * FROM skills WHERE id = ? AND deleted_at IS NULLr   Tr&   r(   )	r   r   r   r   r   r   r1   r   r   )r   r   r   r#   resultr   r   r   	get_skill^   s    z(register_skill_routes.<locals>.get_skillz/api/skills/countc                  S   s   t jd} | r| ds$tddS | dd }t|}|sHtddS t }| }z:|
d|f | d	 }ttd
d}t||dW S |	  X d S )NAuthorizationBearer 	   未登录   r      登录已过期ISELECT COUNT(*) FROM skills WHERE publisher_id = ? AND deleted_at IS NULLr   max_publish_skills1)countmax)r   headersr,   
startswithr   splitr   r   r   r   r   r   r-   r
   r   )auth_headertokenr   r   r   current_count
max_skillsr   r   r   get_skill_countp   s    

z.register_skill_routes.<locals>.get_skill_countPOSTc                  S   s  t j} t| d| d| d\}}|r4tdS t }| }z|zDd }t jdd	dd}|r|
d|f | }|r|
d	|d
 f | }|r|d dkrtddW W  S |d }ttdd}	|
d|f | d }
|
|	krtd|	 dW W S |
d| d| d| d| d| d| d| d|| d| df
 |j}|  td|i| W W 6S  tk
r } z|  |W 5 d }~X Y nX W 5 |  X d S )Ntitledescriptiondetail_location0   文字中存在敏感词，请修改后再发布r>    r?   z,SELECT user_id FROM sessions WHERE token = ?z7SELECT * FROM users WHERE id = ? AND deleted_at IS NULLr   enabledr   u'   账户已被禁用，无法发布技能  idrE   rF   rD   r   u   已发布的技能数最多为u   个，暂时无法发布z
                INSERT INTO skills (title, description, province, city, district, street, detail_location, publisher_id, publisher_name, contact)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            provincecitydistrictstreetpublisher_namecontact)r   jsonr   r,   r   r   r   r   rI   replacer   r   r-   r
   	lastrowidcommitr   	Exceptionrollback)datafoundwordr   r   r   Z
auth_tokensessionuserrO   rN   r   er   r   r   create_skill   s`    
z+register_skill_routes.<locals>.create_skillPUTc              
   S   s`  t jd}|r|ds$tddS |dd }t|}|sHtddS t| |dd	\}}}|sjt|d
S t j}t	|d|d|d\}}	|rtdS t
 }
|
 }zzjg }g }d|kr|d ||d  d|kr|d ||d  d|kr|d ||d  d|kr@|d ||d  d|krb|d ||d  d|kr|d ||d  d|kr|d ||d  |stdW W S ||  |dd| d| |
  |d| f | }t|}t||d< t|W W 6S  tk
rL } z|
  |W 5 d }~X Y nX W 5 |
  X d S ) Nr>   r?      请先登录rA   rB   r   !   登录已过期，请重新登录Tr   rX   rR   rS   rT   rU   ztitle=?zdescription=?rZ   z
province=?r[   zcity=?r\   z
district=?r]   zstreet=?zdetail_location=?u   没有需要更新的字段zUPDATE skills SET z, z WHERE id = ?z!SELECT * FROM skills WHERE id = ?r(   )r   rI   r,   rJ   r   rK   r   r   r`   r   r   r   r   r2   r   joinrc   r   r1   r   r   rd   re   )r   rL   rM   r   has_permissionr   	error_msgrf   rg   rh   r   r   update_fieldsupdate_valuesZupdated_skillr<   rk   r   r   r   update_skill   st    















z+register_skill_routes.<locals>.update_skillz/api/skills/myc            	      S   s   t jd} | r| ds$tddS | dd }t|}|sHtddS t }| }zR|
d|f | }g }|D ]&}t|}t|d	d
|d< || qvt|W S |	  X d S )Nr>   r?   r@   rA   rB   r   rC   zy
                SELECT * FROM skills WHERE publisher_id = ? AND deleted_at IS NULL ORDER BY created_at DESC
            Tr&   r(   )r   rI   r,   rJ   r   rK   r   r   r   r   r   r.   r1   r   r2   r   )	rL   rM   r   r   r   r7   skillsr#   r   r   r   r   get_my_skills  s*    

z,register_skill_routes.<locals>.get_my_skillsDELETEc           
   
   S   s   t jd}|r|ds$tddS |dd }t|}|sHtddS t| |dd	\}}}|sjt|d
S t }|	 }z^z*|d| f |  tdddW W 4S  tk
r }	 z|  |	W 5 d }	~	X Y nX W 5 |
  X d S )Nr>   r?   rn   rA   rB   r   ro   Trp   rX   z;UPDATE skills SET deleted_at = datetime('now') WHERE id = ?u   删除成功)successmessage)r   rI   r,   rJ   r   rK   r   r   r   r   r   r   rc   r   rd   re   )
r   rL   rM   r   rr   r   rs   r   r   rk   r   r   r   delete_skill1  s(    


z+register_skill_routes.<locals>.delete_skillc           	      S   s   | si S t  }| }zddgt|  }|d| d|  | }i }|D ]H}|d dkrt|d |d  d }| d	||d
 < qPd||d
 < qP| D ]}||krd||< q|W S |  X d S )N,?a;  
                SELECT
                    acceptor_id,
                    COUNT(*) as total,
                    SUM(CASE WHEN rating = 'good' THEN 1 ELSE 0 END) as positive_count
                FROM task_ratings
                WHERE rating_type = 'publisher_to_acceptor'
                  AND acceptor_id IN (z3)
                GROUP BY acceptor_id
            r*   r   positive_countd   %acceptor_idr%   r   r   r   rq   lenr   r.   round	user_idsr   r   placeholdersr7   
rating_mapr#   rater   r   r   r   r:   P  s.    	

z9register_skill_routes.<locals>.get_bulk_user_rating_ratesc           	      S   s   | si S t  }| }zddgt|  }|d| d|  | }i }|D ]H}|d dkrt|d |d  d }| d	||d
 < qPd||d
 < qP| D ]}||krd||< q|W S |  X d S )Nr}   r~   a|  
                SELECT
                    t.publisher_id,
                    COUNT(*) as total,
                    SUM(CASE WHEN tr.rating = 'good' THEN 1 ELSE 0 END) as positive_count
                FROM task_ratings tr
                JOIN tasks t ON tr.task_id = t.id
                WHERE tr.rating_type = 'acceptor_to_publisher'
                  AND t.publisher_id IN (z6)
                GROUP BY t.publisher_id
            r*   r   r   r   r   r   r%   r   r   r   r   r   get_bulk_publisher_rating_ratest  s.    

z>register_skill_routes.<locals>.get_bulk_publisher_rating_rates)route)	appr;   r=   rP   rl   rv   rx   r|   r   r   r9   r   register_skill_routes%   s     7


>
K

$r   N)T)flaskr   r   Zutils.databaser   Z
utils.authr   r   r   Zutils.helpersr   r	   utils.configr
   utils.sensitive_filterr   r   r   r   r   r   r   <module>   s   
