o
    Dj…- ã                   @   sê  d Z ddlZddlZddlZddlZddlZddlZddlZddlZ	ddl
Z	ddlmZ ddlZddlmZmZmZmZmZmZmZ ddlmZ zddlZW n eyY   dZY nw ddlmZmZmZmZmZmZm Z m!Z!m"Z" ede#ƒZ$eeeeeej%j&dd	œZ'ej( )ej( *ej( *e+¡¡dd
d¡Z,e,Z-dZ.g d¢Z/dd„ Z0dd„ Z1dd„ Z2dd„ Z3dd„ Z4dd„ Z5dd„ Z6dd„ Z7dd„ Z8d d!„ Z9d"d#„ Z:d$d%„ Z;d&d'„ Z<e$j=d(d)gd*�d+d,„ ƒZ>e$j=d-d)d.gd*�d/d0„ ƒZ?e$j=d1d2d3gd*�d4d5„ ƒZ@d6d7„ ZAe$j=d8d)d.gd*�d9d:„ ƒZBd;d<d<d=d>œd?d@d@d=d>œdAd@dBdCd>œdDœZCdEdF„ ZDdGdH„ ZEe$j=dId.gd*�dJdK„ ƒZFe$j=dLd2d3gd*�dMdN„ ƒZGe$j=dOdPgd*�dQdR„ ƒZHe$j=dSd)gd*�dTdU„ ƒZIdVdW„ ZJdXdY„ ZKe$j=dZd)gd*�d[d\„ ƒZLe$j=d]d)d.gd*�d^d_„ ƒZMe$j=d`d)gd*�dadb„ ƒZNe$j=dcd)gd*�ddde„ ƒZOe$j=dfd.gd*�dgdh„ ƒZPe$j=dig dj¢d*�dkdl„ ƒZQdmdn„ ZRdodp„ ZSdqdr„ ZTdsdt„ ZUe$j=dud.gd*�dvdw„ ƒZVe$j=dxd.gd*�dydz„ ƒZWe$j=d{d.gd*�d|d}„ ƒZXe$j=d~dPgd*�dd€„ ƒZYe$j=d�d3gd*�d‚dƒ„ ƒZZe$j=d„g d…¢d*�d†d‡„ ƒZ[e$j=dˆd)gd*�d‰dŠ„ ƒZ\e$j=d‹d)d2gd*�dŒd�„ ƒZ]e$j=dŽd)d2gd*�d�d�„ ƒZ^e$j=d‘d.gd*�d’d“„ ƒZ_e$j=d”d)d2gd*�d•d–„ ƒZ`e$j=d—d.gd*�d˜d™„ ƒZae$j=dšd)gd*�d›dœ„ ƒZbe$j=d�d)gd*�dždŸ„ ƒZce$j=d d¡d¢id£�e$ =d¤¡d¥d¦„ ƒƒZddS )§z‹
PartsInventory blueprint: Flask port of the PartsInventory server APIs.
Serves API endpoints and a built React SPA under /partsinventory.
é    N)Údatetime)Ú	BlueprintÚResponseÚjsonifyÚredirectÚrequestÚ	send_fileÚsend_from_directory)Úsecure_filename)	ÚPARTSINV_CLIENT_DISTÚPARTSINV_DB_HOSTÚPARTSINV_DB_NAMEÚPARTSINV_DB_PASSWORDÚPARTSINV_DB_PORTÚPARTSINV_DB_USERÚPARTSINV_IMAGE_SEARCH_API_KEYÚPARTSINV_IMAGE_SEARCH_CXÚPARTSINV_UPLOAD_DIRÚpartsinventoryF©ÚhostÚportÚuserÚpasswordÚdatabaseÚcursorclassÚ
autocommitÚdatazdb-config.jsonzPhttps://api.github.com/repos/KiCad/kicad-footprints/git/trees/master?recursive=1)2Ú0402Ú0603Ú0805Ú1206Ú1210Ú1812Ú2010Ú2512zSOT-23zSOT-223zSOT-89zSOT-143zSOT-323zSOT-363zSOD-123zSOD-323zSOD-523zDO-214AA (SMB)zDO-214AB (SMC)zDO-214AC (SMA)zTO-220zTO-92zQFN-16zQFN-24zQFN-32zQFN-48zQFP-32zQFP-44zQFP-64zTQFP-32zTQFP-44zTQFP-64zSOIC-8zSOIC-14zSOIC-16zSOP-8zTSSOP-8zTSSOP-14zTSSOP-16zSSOP-16zMSOP-8zDFN-8zDIP-8zDIP-14zDIP-16zLQFP-64zLQFP-100zBGA-64zBGA-100zUSB-C Receptaclec            	      C   sÞ   t jjtddid�} t jj| dd��}t | ¡  d¡¡}W d   ƒ n1 s'w   Y  g }| 	dg ¡D ]3}| 	d¡p<d	}| 
d
¡sCq4d|vrHq4| dd¡\}}| d
d¡d }|r\|s]q4| |› d|› �¡ q4tt |¡ƒS )Nz
User-AgentzPartsInventory/1.0)Úheadersé   ©Útimeoutúutf-8ÚtreeÚpathÚ z
.kicad_modz.pretty/é   r   ú:)Úurllibr   ÚRequestÚ_KICAD_TREE_API_URLÚurlopenÚjsonÚloadsÚreadÚdecodeÚgetÚendswithÚsplitÚrsplitÚappendÚlistÚdictÚfromkeys)	ÚreqÚresponseÚpayloadÚnamesÚitemr,   ÚlibÚmodÚfp_name© rH   ú1/var/www/html/Server/blueprints/partsinventory.pyÚ_fetch_kicad_footprintsj   s"   ÿ
rJ   c                 C   sº   t ƒ }|sddddœS |  d¡ |  ¡ }dd„ |D ƒ}d}d}g }ttƒ}t|ƒD ]#\}}	| |	¡}
|
d u r;|d7 }n|
dkrC|d7 }| |	|| f¡ q)|rU|  d|¡ ||t|ƒdœS )Nr   )ÚaddedÚreactivatedÚtotalzWSELECT name, COALESCE(is_active, 0) AS is_active FROM footprints WHERE source = 'kicad'c                 S   s$   i | ]}|d  t | d¡pdƒ“qS )ÚnameÚ	is_activer   )Úintr8   ©Ú.0ÚrrH   rH   rI   Ú
<dictcomp>…   s   $ z*_sync_kicad_footprints.<locals>.<dictcomp>r.   a*  
            INSERT INTO footprints (name, source, sort_order, is_active)
            VALUES (%s, 'kicad', %s, 1)
            ON DUPLICATE KEY UPDATE
                source = VALUES(source),
                sort_order = VALUES(sort_order),
                is_active = VALUES(is_active)
            )	rJ   ÚexecuteÚfetchallÚlenÚ_DEFAULT_FOOTPRINTSÚ	enumerater8   r<   Úexecutemany)ÚcurÚkicad_namesÚexisting_rowsÚexistingrK   rL   rB   Ú	base_sortÚirN   Ú
was_activerH   rH   rI   Ú_sync_kicad_footprints~   s.   


÷rb   c                  C   s   t ƒ } dt|  dd¡ƒiS )NÚ
showQrCodeT)Ú_load_overrideÚboolr8   ©ÚoverriderH   rH   rI   Ú_effective_ui_settings¢   s   ÿrh   c                  C   sh   t j t¡si S z ttddd��} t | ¡W  d   ƒ W S 1 s!w   Y  W d S  ty3   i  Y S w )NrS   r*   ©Úencoding)Úosr,   ÚexistsÚ_DB_OVERRIDE_FILEÚopenr4   ÚloadÚ	Exception)ÚfrH   rH   rI   rd   ©   s   (ÿÿrd   c                 C   sZ   t jt j t¡dd� ttddd��}tj| |dd� W d   ƒ d S 1 s&w   Y  d S )NT©Úexist_okÚwr*   ri   é   )Úindent)rk   Úmakedirsr,   Údirnamerm   rn   r4   Údump)r   rq   rH   rH   rI   Ú_save_override³   s   "ÿrz   c                  C   sJ   t ƒ } |  dt¡t|  dt¡ƒ|  dt¡|  dt¡|  dt¡tj	j
ddœS )Nr   r   r   r   r   Fr   )rd   r8   r   rP   r   r   r   r   ÚpymysqlÚcursorsÚ
DictCursorrf   rH   rH   rI   Ú_effective_db_config¹   s   



ùr~   c                  C   sV   t ƒ } |  d¡ptpd ¡ }tj |¡r|S tj tj t¡¡}tj 	tj 
||¡¡S )NÚ	uploadDirÚpartsinventory_uploads)rd   r8   r   Ústriprk   r,   Úisabsrx   Ú__file__ÚabspathÚjoin)rg   Ú
upload_dirÚbaserH   rH   rI   Ú_effective_upload_dirÆ   s   rˆ   c                    s  |pd  dd¡ ¡ }|sdS g ‰tƒ ‰ ‡ ‡fdd„}||ƒ | d¡r.||tdƒd… ƒ n|d|› �ƒ ˆD ]}tj | |¡}tj |¡rJ|  S q7ˆD ]?}tj 	|¡\}}tj 
|¡\}}	|sbqMtj | |¡}
tj |
¡spqMt tj |
|› d�¡¡D ]}tj |¡r‹|    S q}qMdS )	zFFind an on-disk upload file for a DB path across legacy path variants.r-   ú\ú/Nc                    sB   | pd  dd¡ ¡  d¡}|r|ˆ v rd S ˆ  |¡ ˆ |¡ d S )Nr-   r‰   rŠ   )Úreplacer�   ÚlstripÚaddr<   )ÚrelÚrel_norm©ÚseenÚvariantsrH   rI   r�   Ø   s
   
z*_resolve_existing_upload_file.<locals>.addúuploads/ú.*)r‹   r�   ÚsetÚ
startswithrW   rk   r,   r…   Úisfiler:   ÚsplitextÚisdirÚglob)Úupload_rootÚstored_pathÚrawr�   rŽ   Ú	candidateÚrel_dirÚrel_nameÚstemÚ_extÚabs_dirÚmatchrH   r�   rI   Ú_resolve_existing_upload_fileÏ   s:   
ÿÿÿr¥   c                   C   s   t jdi tƒ ¤ŽS )NrH   )r{   Úconnectr~   rH   rH   rH   rI   Ú_get_dbú   s   r§   c                  C   s6   t ƒ } | d | d | d | d | d rddœS d dœS )Nr   r   r   r   r   z****)r   r   r   r   r   )r~   )ÚcfgrH   rH   rI   Ú_db_health_payloadþ   s   
ûûr©   c                    s�  t  ¡ }| d¡ | dt ¡  ¡ › d�¡ | d¡ ˆ  ¡ �˜}| d¡ | ¡ }dd„ |D ƒ}|D ]}}| d|› d	�¡ | 	¡ }|rH| 
d
¡nd }|sMq2| d|› d�¡ | d|› d�¡ | |› d�¡ | d|› d	�¡ | ¡ }|swq2t|d  ¡ ƒ}	d dd„ |	D ƒ¡}
|D ]‰d ‡ ‡fdd„|	D ƒ¡}| d|› d|
› d|› d�¡ q‹| d¡ q2W d   ƒ n1 sºw   Y  | d¡ | ¡ S )Nz-- PartsInventory full backup
z-- Generated at z

zSET FOREIGN_KEY_CHECKS = 0;

zÎ
            SELECT TABLE_NAME
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_TYPE = 'BASE TABLE'
            ORDER BY TABLE_NAME
            c                 S   s   g | ]}|d  ‘qS )Ú
TABLE_NAMErH   rQ   rH   rH   rI   Ú
<listcomp>  ó    z,_sql_dump_for_current_db.<locals>.<listcomp>zSHOW CREATE TABLE `ú`zCreate Tablez
-- Table: Ú
zDROP TABLE IF EXISTS `z`;
z;

zSELECT * FROM `r   z, c                 S   s   g | ]}d |› d �‘qS )r­   rH   ©rR   ÚcrH   rH   rI   r«   +  ó    c                    s   g | ]
}ˆ   ˆ |¡¡‘qS rH   )Úliteralr8   r¯   ©ÚconnÚrowrH   rI   r«   -  ó    zINSERT INTO `z` (z
) VALUES (z);
zSET FOREIGN_KEY_CHECKS = 1;
)ÚioÚStringIOÚwriter   ÚnowÚ	isoformatÚcursorrU   rV   Úfetchoner8   r=   Úkeysr…   Úgetvalue)r´   Úoutr[   Ú
table_rowsÚtable_namesÚ
table_nameÚ
create_rowÚ
create_sqlÚrowsÚcolumnsÚcol_sqlÚvalsrH   r³   rI   Ú_sql_dump_for_current_db	  sB   


ÿ	 íó
"rÊ   c              	   C   sn  |   ¡ ��£}| d¡ | d¡ | d¡ | d¡ | d¡ z| d¡ W n
 tjy0   Y nw z| d¡ W n
 tjyB   Y nw z| d¡ W n
 tjyT   Y nw z| d	¡ W n
 tjyf   Y nw z| d
¡ W n
 tjyx   Y nw z| d¡ W n
 tjyŠ   Y nw z| d¡ W n
 tjyœ   Y nw z| d¡ W n
 tjy®   Y nw z| d¡ W n
 tjyÀ   Y nw z| d¡ W n
 tjyÒ   Y nw z| d¡ W n
 tjyä   Y nw z| d¡ W n
 tjyö   Y nw z| d¡ W n tj�y	   Y nw z| d¡ W n tj�y   Y nw z| d¡ W n tj�y/   Y nw z| d¡ W n tj�yB   Y nw z| d¡ W n tj�yU   Y nw z| d¡ W n tj�yh   Y nw z| d¡ W n tj�y{   Y nw z| d¡ W n tj�yŽ   Y nw ttƒD ]\}}| d||f¡ �q“W d   ƒ n	1 �s¬w   Y  |  ¡  d S )Nay  
            CREATE TABLE IF NOT EXISTS footprints (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                source VARCHAR(20) NOT NULL DEFAULT 'custom',
                sort_order INT NOT NULL DEFAULT 0,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uq_footprints_name (name),
                INDEX idx_footprints_active_sort (is_active, sort_order, name)
            )
            a#  
            CREATE TABLE IF NOT EXISTS categories (
                id INT AUTO_INCREMENT PRIMARY KEY,
                parent_id INT NULL,
                source_partkeepr_id INT NULL,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                sort_order INT DEFAULT 0,
                FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
                INDEX idx_parent (parent_id),
                UNIQUE KEY uq_categories_source_partkeepr_id (source_partkeepr_id)
            )
            a8  
            CREATE TABLE IF NOT EXISTS storage_locations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                parent_id INT NULL,
                source_partkeepr_id INT NULL,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                sort_order INT DEFAULT 0,
                FOREIGN KEY (parent_id) REFERENCES storage_locations(id) ON DELETE SET NULL,
                INDEX idx_parent (parent_id),
                UNIQUE KEY uq_storage_locations_source_partkeepr_id (source_partkeepr_id)
            )
            aL  
            CREATE TABLE IF NOT EXISTS parts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                category_id INT NULL,
                storage_location_id INT NULL,
                overstock_location_id INT NULL,
                source_partkeepr_id INT NULL,
                name VARCHAR(255) NOT NULL DEFAULT '',
                description TEXT,
                part_number VARCHAR(255) NOT NULL DEFAULT '',
                quantity INT NOT NULL DEFAULT 0,
                overstock_quantity INT NOT NULL DEFAULT 0,
                unit VARCHAR(50) DEFAULT '',
                average_price DECIMAL(12,4) NOT NULL DEFAULT 0,
                minimum_stock_level INT NOT NULL DEFAULT 0,
                comment TEXT,
                barcode VARCHAR(255) NULL,
                datasheet_url VARCHAR(1024) NULL,
                datasheet_file_path VARCHAR(1024) NULL,
                datasheet_thumbnail_path VARCHAR(1024) NULL,
                manufacturer VARCHAR(255) NULL,
                footprint VARCHAR(255) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
                FOREIGN KEY (storage_location_id) REFERENCES storage_locations(id) ON DELETE SET NULL,
                FOREIGN KEY (overstock_location_id) REFERENCES storage_locations(id) ON DELETE SET NULL,
                INDEX idx_category (category_id),
                INDEX idx_storage (storage_location_id),
                INDEX idx_overstock_storage (overstock_location_id),
                INDEX idx_barcode (barcode),
                UNIQUE KEY uq_parts_source_partkeepr_id (source_partkeepr_id),
                UNIQUE KEY unique_barcode (barcode)
            )
            ac  
            CREATE TABLE IF NOT EXISTS part_images (
                id INT AUTO_INCREMENT PRIMARY KEY,
                part_id INT NOT NULL,
                file_path VARCHAR(1024) NULL,
                original_filename VARCHAR(255) NULL,
                url VARCHAR(1024) NULL,
                source ENUM('uploaded', 'internet') NOT NULL,
                is_primary TINYINT(1) NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (part_id) REFERENCES parts(id) ON DELETE CASCADE,
                INDEX idx_part (part_id)
            )
            zVALTER TABLE part_images ADD COLUMN original_filename VARCHAR(255) NULL AFTER file_pathz>ALTER TABLE categories ADD COLUMN source_partkeepr_id INT NULLz]ALTER TABLE categories ADD UNIQUE KEY uq_categories_source_partkeepr_id (source_partkeepr_id)zEALTER TABLE storage_locations ADD COLUMN source_partkeepr_id INT NULLzkALTER TABLE storage_locations ADD UNIQUE KEY uq_storage_locations_source_partkeepr_id (source_partkeepr_id)zeALTER TABLE storage_locations ADD COLUMN slot_unusable TINYINT(1) NOT NULL DEFAULT 0 AFTER sort_orderz9ALTER TABLE parts ADD COLUMN source_partkeepr_id INT NULLzUALTER TABLE parts ADD COLUMN overstock_location_id INT NULL AFTER storage_location_idzUALTER TABLE parts ADD COLUMN overstock_quantity INT NOT NULL DEFAULT 0 AFTER quantityz”ALTER TABLE parts ADD CONSTRAINT fk_parts_overstock_location FOREIGN KEY (overstock_location_id) REFERENCES storage_locations(id) ON DELETE SET NULLzIALTER TABLE parts ADD INDEX idx_overstock_storage (overstock_location_id)zSALTER TABLE parts ADD UNIQUE KEY uq_parts_source_partkeepr_id (source_partkeepr_id)zVALTER TABLE parts ADD COLUMN average_price DECIMAL(12,4) NOT NULL DEFAULT 0 AFTER unitz[ALTER TABLE parts ADD COLUMN minimum_stock_level INT NOT NULL DEFAULT 0 AFTER average_pricezHALTER TABLE parts ADD COLUMN comment TEXT NULL AFTER minimum_stock_levelzbALTER TABLE parts ADD COLUMN datasheet_thumbnail_path VARCHAR(1024) NULL AFTER datasheet_file_pathzKALTER TABLE parts ADD COLUMN footprint VARCHAR(255) NULL AFTER manufacturerz;ALTER TABLE parts MODIFY COLUMN footprint VARCHAR(255) NULLzYALTER TABLE footprints ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'custom' AFTER namez?ALTER TABLE footprints MODIFY COLUMN name VARCHAR(255) NOT NULLa  
                INSERT INTO footprints (name, source, sort_order, is_active)
                VALUES (%s, 'default', %s, 1)
                ON DUPLICATE KEY UPDATE
                    sort_order = VALUES(sort_order),
                    is_active = VALUES(is_active)
                )r¼   rU   r{   ÚOperationalErrorrY   rX   Úcommit)r´   r[   r`   rN   rH   rH   rI   Ú_ensure_tables5  s  ÿÿÿÿ%ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿøÿ �È CrÍ   c                 C   s8   t | ƒ}t| ¡ ƒD ]\}}t|tƒr| ¡ ||< q
|S )N)r>   r=   ÚitemsÚ
isinstancer   r»   )rµ   rÀ   ÚkeyÚvaluerH   rH   rI   Ú_dateifyû  s   
€rÒ   z/api/healthÚGET)Úmethodsc                   C   s   t ddiƒdfS )NÚokTéÈ   )r   rH   rH   rH   rI   Úhealth  s   r×   z/api/categoriesÚPOSTc                  C   óT  t ƒ } z¡ztt| ƒ |  ¡ �b}tjdkr-| d¡ | ¡ }t|ƒW  d   ƒ W W |  ¡  S tj	dd�p4i }| d| 
d¡| 
dd¡| 
d	d¡| 
d
d¡f¡ | d|jf¡ |  ¡  t| ¡ ƒdfW  d   ƒ W W |  ¡  S 1 srw   Y  W n% ty� } z|  ¡  tdt|ƒiƒdfW  Y d }~W |  ¡  S d }~ww W |  ¡  d S |  ¡  w )NrÓ   z]SELECT id, parent_id, name, description, sort_order FROM categories ORDER BY sort_order, nameT©ÚsilentzYINSERT INTO categories (parent_id, name, description, sort_order) VALUES (%s, %s, %s, %s)Ú	parent_idrN   r-   ÚdescriptionÚ
sort_orderr   ú&SELECT * FROM categories WHERE id = %séÉ   Úerroréô  ©r§   rÍ   r¼   r   ÚmethodrU   rV   r   ÚcloseÚget_jsonr8   Ú	lastrowidrÌ   r½   rp   ÚrollbackÚstr©r´   r[   rÆ   rB   ÚerH   rH   rI   Úcategories_root  s:   


ü
ô(þô
ï 
€üórì   z/api/categories/<int:item_id>ÚPUTÚDELETEc                 C   óÈ  t ƒ }zÛz®t|ƒ | ¡ �œ}tjdkrntjdd�pi }| d| d¡| d¡| d¡| dd	¡| f¡ | d
| f¡ | ¡ }|sY| 	¡  t
ddiƒdfW  d   ƒ W W | ¡  S | ¡  t
|ƒW  d   ƒ W W | ¡  S | d| f¡ |jd	kr“| 	¡  t
ddiƒdfW  d   ƒ W W | ¡  S | ¡  tdd�W  d   ƒ W W | ¡  S 1 s¬w   Y  W n% ty× } z| 	¡  t
dt|ƒiƒdfW  Y d }~W | ¡  S d }~ww W | ¡  d S | ¡  w )Nrí   TrÚ   z`UPDATE categories SET parent_id = %s, name = %s, description = %s, sort_order = %s WHERE id = %srÜ   rN   rÝ   rÞ   r   rß   rá   ú	Not foundé”  z$DELETE FROM categories WHERE id = %séÌ   ©Ústatusrâ   ©r§   rÍ   r¼   r   rä   ræ   rU   r8   r½   rè   r   rå   rÌ   Úrowcountr   rp   ré   ©Úitem_idr´   r[   rB   rµ   rë   rH   rH   rI   Úcategories_id!  óP   

&þõ
ôó
ö
ï
úí
è 
€üìrù   c                 C   s8   |d u rdS |   dt|ƒf¡ |  ¡ }t|o| d¡ƒS )NFzKSELECT COALESCE(slot_unusable, 0) AS u FROM storage_locations WHERE id = %sÚu)rU   rP   r½   re   r8   )r[   Úlocation_idrµ   rH   rH   rI   Ú_storage_location_slot_unusableA  s   þrý   z/api/storage-locationsc                  C   rÙ   )NrÓ   zsSELECT id, parent_id, name, description, sort_order, slot_unusable FROM storage_locations ORDER BY sort_order, nameTrÚ   ú`INSERT INTO storage_locations (parent_id, name, description, sort_order) VALUES (%s, %s, %s, %s)rÜ   rN   r-   rÝ   rÞ   r   ú-SELECT * FROM storage_locations WHERE id = %srà   rá   râ   rã   rê   rH   rH   rI   Ústorage_locations_rootL  s>   

ÿú
ô(þò
í 
€üñr   ÚBé   é   )ÚprefixÚ
num_digitsrÆ   ÚcolsÚBAé   ÚBCé   é   )r  r  r	  c           	      C   sj   t | }|d }t dt |¡› d�¡}d}| D ]}|pd ¡ }| |¡}|r2t|t| d¡dƒƒ}q|S )Nr  ú^z(\d+)-r   r-   r.   é
   )	Ú_BATCH_BIN_SPECSÚreÚcompileÚescaper�   r¤   ÚmaxrP   Úgroup)	rC   Úbin_typeÚspecr  ÚpatternÚmax_nr�   rN   ÚmrH   rH   rI   Ú_batch_bin_max_serialn  s   
€r  c                 C   sR   g }t |ƒD ] }ttdƒ| ƒ}t d|d ƒD ]}| | › d|› |› �¡ qq|S )NÚAr.   ú-)ÚrangeÚchrÚordr<   )Úbin_baserÆ   r  rÀ   rS   Ú
row_letterr°   rH   rH   rI   Ú_batch_bin_slot_names{  s   ÿr!  z!/api/storage-locations/batch-binsc               
   C   sJ  t ƒ } �z›�zlt| ƒ tjdd�pi }| d¡pd ¡  ¡ }z
t| dd¡ƒ}W n tt	fyA   t
ddiƒd	f Y W W |  ¡  S w |tvrTt
dd
iƒd	fW W |  ¡  S |dk s\|dkrjt
ddiƒd	fW W |  ¡  S t| }|d }|d }|d }|d }|  ¡ �æ}	|	 d¡ |	 ¡ }
dd„ |
D ƒ}t||ƒd }g }g }t|ƒD ]k}|| }t|ƒ}t|ƒ|krÒt
dd|› d|› d|› �iƒd	f  W  d  ƒ W W |  ¡  S || |¡ }| |¡ t|||ƒD ](}||v �rt
dd|› �iƒd	f    W  d  ƒ W W |  ¡  S | |¡ qäq¢tƒ }|D ]'}||v �r4t
dd|› �iƒd	f  W  d  ƒ W W |  ¡  S | |¡ �qdd„ |D ƒ}|	 d|¡ |  ¡  t
t|ƒ|||| dœƒdfW  d  ƒ W W |  ¡  S 1 �slw   Y  W n& t�y˜ } z|  ¡  t
dt|ƒiƒdfW  Y d}~W |  ¡  S d}~ww W |  ¡  dS |  ¡  w ) u]   Create one or more standard bins (grid of pocket locations). Types: B 4Ã—6, BA 3Ã—6, BC 5Ã—7.TrÚ   r  r-   Úcountr.   rá   zcount must be an integeré�  z#bin_type must be "B", "BA", or "BC"é2   zcount must be between 1 and 50r  r  rÆ   r  z"SELECT name FROM storage_locationsc                 S   s   h | ]}|  d ¡p
d ¡ ’qS )rN   r-   )r8   r�   rQ   rH   rH   rI   Ú	<setcomp>Ÿ  s    z/storage_locations_batch_bins.<locals>.<setcomp>zNext bin number z	 exceeds z digit width for type NzLocation already exists: zInternal duplicate slot name: c                 S   s   g | ]}d |ddf‘qS )Nr-   r   rH   )rR   rN   rH   rH   rI   r«   ¸  r±   z0storage_locations_batch_bins.<locals>.<listcomp>rþ   )Úcreatedr  ÚbinsÚslots_per_binrà   râ   )r§   rÍ   r   ræ   r8   r�   ÚupperrP   Ú	TypeErrorÚ
ValueErrorr   rå   r  r¼   rU   rV   r  r  ré   rW   Úzfillr<   r!  r•   r�   rZ   rÌ   rp   rè   )r´   rB   r  r"  r  r  r  rÆ   r  r[   r]   Úexisting_namesÚstart_serialÚnames_to_insertÚ	bin_basesr`   ÚserialÚ
serial_strr  ÚslotÚ	dup_checkÚnÚinsert_rowsrë   rH   rH   rI   Ústorage_locations_batch_bins„  s¤   
>Á
;Æ
9É

ÿþô
1Þ

í
1ãý
ç
1éþüÿ÷Þ
1Ï- 
€üÓ1r7  z$/api/storage-locations/<int:item_id>c                 C   rï   )Nrí   TrÚ   zgUPDATE storage_locations SET parent_id = %s, name = %s, description = %s, sort_order = %s WHERE id = %srÜ   rN   rÝ   rÞ   r   rÿ   rá   rð   rñ   z+DELETE FROM storage_locations WHERE id = %srò   ró   râ   rõ   r÷   rH   rH   rI   Ústorage_locations_idÐ  rú   r8  z2/api/storage-locations/<int:item_id>/slot-unusableÚPATCHc              
   C   s°  t ƒ }zÏz¢t|ƒ tjdd�pi }d|vr#tddiƒdfW W | ¡  S t| d¡ƒ}| ¡ �o}| 	d| f¡ | 
¡ sOtddiƒd	fW  d
  ƒ W W | ¡  S |rq| 	d| f¡ | 
¡ rqtddiƒdfW  d
  ƒ W W | ¡  S | 	d|rxdnd| f¡ | 	d| f¡ | 
¡ }| ¡  t|ƒW  d
  ƒ W W | ¡  S 1 s w   Y  W n% tyË } z| ¡  tdt|ƒiƒdfW  Y d
}~W | ¡  S d
}~ww W | ¡  d
S | ¡  w )z>Mark a bin pocket unusable (empty slots only) or usable again.TrÚ   Úslot_unusablerá   zslot_unusable requiredr#  ú.SELECT id FROM storage_locations WHERE id = %srð   rñ   Nú;SELECT id FROM parts WHERE storage_location_id = %s LIMIT 1z4Cannot mark unusable while a part occupies this slotz=UPDATE storage_locations SET slot_unusable = %s WHERE id = %sr.   r   rÿ   râ   )r§   rÍ   r   ræ   r   rå   re   r8   r¼   rU   r½   rÌ   rp   rè   ré   )rø   r´   rB   Úwantr[   rµ   rë   rH   rH   rI   Ú$storage_location_slot_unusable_patchð  sT   
è
ý
íþö
ôþî
é 
€üír>  z/api/footprintsc               
   C   s<  t ƒ } z•zlt| ƒ |  ¡ �Z}tj d¡pd ¡ }zttj dd¡ƒ}W n tt	fy0   d}Y nw t
dt|dƒƒ}|rM| dd|› d�||› d�|f¡ n| d	|f¡ t| ¡ ƒW  d   ƒ W W |  ¡  S 1 sjw   Y  W n! ty‘ } ztd
t|ƒiƒdfW  Y d }~W |  ¡  S d }~ww W |  ¡  d S |  ¡  w )NÚqr-   Úlimitéd   r.   râ   aÓ  
                    SELECT id, name, sort_order, is_active
                    FROM footprints
                    WHERE is_active = 1
                      AND name LIKE %s
                    ORDER BY
                      CASE WHEN LOWER(name) = LOWER(%s) THEN 0 ELSE 1 END,
                      CASE WHEN LOWER(name) LIKE LOWER(%s) THEN 0 ELSE 1 END,
                      sort_order,
                      name
                    LIMIT %s
                    ú%zç
                    SELECT id, name, sort_order, is_active
                    FROM footprints
                    WHERE is_active = 1
                    ORDER BY sort_order, name
                    LIMIT %s
                    rá   )r§   rÍ   r¼   r   Úargsr8   r�   rP   r*  r+  r  ÚminrU   r   rV   rå   rp   ré   )r´   r[   r?  r@  rë   rH   rH   rI   Úfootprints_root  s>   
ÿóø

Þ
&Ú# 
€ýÝ&rE  c                 C   sD   |pd  ¡ }|s
d S |  d|f¡ |  ¡ }|r | d¡r |d S |S )Nr-   zASELECT name FROM footprints WHERE LOWER(name) = LOWER(%s) LIMIT 1rN   )r�   rU   r½   r8   )r[   Ú	raw_valuer�   ÚhitrH   rH   rI   Ú_normalize_footprintB  s   rH  c              	   C   sð   zt |ƒ}W n ttfy   g  Y S w |  d¡ |  ¡ }dd„ |D ƒ}||vr*g S i }|D ]}|d }| d¡}||vrAg ||< ||  |¡ q.g }	|g}
tƒ }|
rv|
 ¡ }||v r\qQ| 	|¡ |	 |¡ | |d¡D ]}|
 |¡ ql|
sS|	S )zOIDs of root category and all descendants (empty if root_id is missing from DB).z$SELECT id, parent_id FROM categoriesc                 S   s   h | ]}|d  ’qS )ÚidrH   rQ   rH   rH   rI   r%  U  r¬   z4_category_self_and_descendant_ids.<locals>.<setcomp>rI  rÜ   rH   )
rP   r*  r+  rU   rV   r8   r<   r•   Úpopr�   )r[   Úroot_idÚridrÆ   ÚknownÚchildrenrS   ÚcidÚpidrÀ   Ústackr‘   ÚchrH   rH   rI   Ú!_category_self_and_descendant_idsM  s<   ÿ



ùrS  z/api/parts/stock-valuec               
   C   sä   t ƒ } ziz@t| ƒ |  ¡ �.}| d¡ | ¡ }|r| d¡nd}|du r&d}tdt|ƒiƒW  d  ƒ W W |  ¡  S 1 s>w   Y  W n! t	ye } ztdt
|ƒiƒdfW  Y d}~W |  ¡  S d}~ww W |  ¡  dS |  ¡  w )ua   Aggregate inventory value: sum of (quantity + overstock_quantity) Ã— average_price for all parts.zuSELECT COALESCE(SUM((quantity + COALESCE(overstock_quantity, 0)) * average_price), 0) AS total_stock_value FROM partsÚtotal_stock_valuer   NÚtotalStockValuerá   râ   )r§   rÍ   r¼   rU   r½   r8   r   Úfloatrå   rp   ré   )r´   r[   rµ   r�   rë   rH   rH   rI   Úparts_stock_valuem  s,   
ÿø
ô	 
€ý÷rW  z
/api/partsc                  C   s’  t ƒ } �z?�zt| ƒ |  ¡ ��ü}tjdkr¸tj d¡}tj d¡}tj d¡}tj d¡}d}g }|p5d ¡  ¡ }|dkrC|d	7 }n#|rft	||ƒ}	|	sQ|d
7 }n|dd 
dgt|	ƒ ¡ d 7 }| |	¡ |rq|d7 }| |¡ |r�|d7 }| ||› d�g¡ |r–|d7 }d|› d�}
| |
|
|
|
g¡ |d7 }| ||¡ tdd„ | ¡ D ƒƒW  d   ƒ W W |  ¡  S tjdd�p¿i }t|| d¡ƒ}| d¡}| d¡}|dv rÙd }n,zt|ƒ}W n% ttf�y   |  ¡  tddiƒdf Y W  d   ƒ W W |  ¡  S w zt| d d!¡�pd!ƒ}W n% ttf�y7   |  ¡  tdd"iƒdf Y W  d   ƒ W W |  ¡  S w |d u�r\t||ƒ�r\|  ¡  tdd#iƒdfW  d   ƒ W W |  ¡  S |d!k �rz|  ¡  tdd"iƒdfW  d   ƒ W W |  ¡  S |d u�r¤| d$|f¡ | ¡ �s¤|  ¡  tdd%iƒdfW  d   ƒ W W |  ¡  S | d&| d'¡| d¡|| d(d¡| d)d¡| d*d¡| d+d!¡|| d,d¡| d-d!¡| d.d!¡| d/d¡| d0¡| d1¡| d2¡|f¡ | d3|jf¡ |  ¡  tt| ¡ ƒƒd4fW  d   ƒ W W |  ¡  S 1 �sw   Y  W n& t�y< } z|  ¡  tdt|ƒiƒd5fW  Y d }~W |  ¡  S d }~ww W |  ¡  d S |  ¡  w )6NrÓ   Ú
categoryIdÚstorageLocationIdÚstorageLocationPrefixr?  aC  
                    SELECT p.id, p.category_id, p.storage_location_id, p.name, p.description, p.part_number,
                           p.quantity, p.overstock_location_id, p.overstock_quantity, p.unit, p.average_price, p.minimum_stock_level, p.comment, p.barcode, p.datasheet_url, p.datasheet_file_path, p.manufacturer, p.footprint,
                           p.created_at, p.updated_at, c.name AS category_name, s.name AS storage_location_name, so.name AS overstock_location_name,
                           pi.url AS primary_image_url, pi.file_path AS primary_image_file_path,
                           pi2.url AS secondary_image_url, pi2.file_path AS secondary_image_file_path
                    FROM parts p
                    LEFT JOIN categories c ON p.category_id = c.id
                    LEFT JOIN storage_locations s ON p.storage_location_id = s.id
                    LEFT JOIN storage_locations so ON p.overstock_location_id = so.id
                    LEFT JOIN part_images pi ON pi.id = (
                        SELECT i.id
                        FROM part_images i
                        WHERE i.part_id = p.id
                        ORDER BY i.is_primary DESC, i.id
                        LIMIT 1
                    )
                    LEFT JOIN part_images pi2 ON pi2.id = (
                        SELECT i2.id
                        FROM part_images i2
                        WHERE i2.part_id = p.id
                        ORDER BY i2.is_primary DESC, i2.id
                        LIMIT 1 OFFSET 1
                    )
                    WHERE 1=1
                r-   Ú
unassignedú AND p.category_id IS NULLú AND 1=0ú AND p.category_id IN (ú,ú%sú)ú AND p.storage_location_id = %sú$ AND (s.name = %s OR s.name LIKE %s)ú-%z\ AND (p.name LIKE %s OR p.part_number LIKE %s OR p.description LIKE %s OR p.barcode LIKE %s)rB  z ORDER BY p.namec                 S   ó   g | ]}t |ƒ‘qS rH   ©rÒ   rQ   rH   rH   rI   r«   ¾  r¬   zparts_root.<locals>.<listcomp>TrÚ   Ú	footprintÚstorage_location_idÚoverstock_location_id©r-   Nrá   ú0overstock_location_id must be an integer or nullr#  Úoverstock_quantityr   ú1overstock_quantity must be a non-negative integerz4Cannot assign part to a storage slot marked unusabler;  úOverstock location not foundaw  
                INSERT INTO parts
                (category_id, storage_location_id, overstock_location_id, name, description, part_number, quantity, overstock_quantity, unit, average_price, minimum_stock_level, comment, barcode, datasheet_url, manufacturer, footprint)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                Úcategory_idrN   rÝ   Úpart_numberÚquantityÚunitÚaverage_priceÚminimum_stock_levelÚcommentÚbarcodeÚdatasheet_urlÚmanufacturerú!SELECT * FROM parts WHERE id = %srà   râ   )r§   rÍ   r¼   r   rä   rC  r8   r�   ÚlowerrS  r…   rW   Úextendr<   rU   r   rV   rå   ræ   rH  rP   r*  r+  rè   rý   r½   rç   rÌ   rÒ   rp   ré   )r´   r[   ro  rh  Ústorage_location_prefixr?  ÚsqlÚparamsÚcat_keyÚcat_idsÚlikerB   rg  Únew_locri  rl  rë   rH   rH   rI   Ú
parts_root‚  sØ   



 

É
uÄ

¼
uÍ·
uÒ´
u
Ø±
u
Û
¬
uà







ðú�
u‹q 
€ü�urƒ  z$/api/parts/by-barcode/<path:barcode>c              
   C   sð   t ƒ }zozF| ¡ �8}| d| f¡ | ¡ }|s,tddiƒdfW  d   ƒ W W | ¡  S td|d iƒW  d   ƒ W W | ¡  S 1 sDw   Y  W n! tyk } ztdt|ƒiƒdfW  Y d }~W | ¡  S d }~ww W | ¡  d S | ¡  w )Nz/SELECT id FROM parts WHERE barcode = %s LIMIT 1rá   rð   rñ   rI  râ   )r§   r¼   rU   r½   r   rå   rp   ré   )rv  r´   r[   rµ   rë   rH   rH   rI   Úparts_by_barcodeÿ  s(   
ü
	üû
	÷ 
€ýú	r„  z/api/parts/exportc                  C   sŽ  t j d¡dkrtddiƒdfS tƒ } �z-�z	|  ¡ ��}t j d¡}t j d¡}t j d¡}t j d	¡}d
}g }|p;d ¡  ¡ }|dkrI|d7 }n#|rlt||ƒ}	|	sW|d7 }n|dd 	dgt
|	ƒ ¡ d 7 }| |	¡ |rw|d7 }| |¡ |r‡|d7 }| ||› d�g¡ |rš|d7 }d|› d�}
| |
|
g¡ | ||¡ | ¡ }W d   ƒ n1 s®w   Y  t ¡ }tj|dd�}| g d¢¡ |D ]G}| | d¡| d¡| d¡| d¡| d¡| d ¡| d!¡| d"¡| d#¡| d$¡| d%¡| d&¡| d'¡| d(¡| d)¡| d*¡g¡ qÇt| ¡ d+d,d-id.�W W |  ¡  S  t�yA } ztdt|ƒiƒd/fW  Y d }~W |  ¡  S d }~ww |  ¡  w )0NÚformatÚcsvrá   zformat=csv requiredr#  rX  rY  rZ  r?  aj  
                SELECT p.part_number, p.name, p.barcode, p.quantity, p.overstock_quantity, p.unit, p.average_price, p.minimum_stock_level, p.comment, p.description, p.datasheet_url, p.manufacturer, p.footprint,
                       c.name AS category_name, s.name AS storage_location_name, so.name AS overstock_location_name
                FROM parts p
                LEFT JOIN categories c ON p.category_id = c.id
                LEFT JOIN storage_locations s ON p.storage_location_id = s.id
                LEFT JOIN storage_locations so ON p.overstock_location_id = so.id
                WHERE 1=1
            r-   r[  r\  r]  r^  r_  r`  ra  rb  rc  rd  z. AND (p.name LIKE %s OR p.part_number LIKE %s)rB  z
)Úlineterminator)rp  rN   rv  rq  rl  rr  rs  rt  ru  rÝ   rw  rx  rg  Úcategory_nameÚstorage_location_nameÚoverstock_location_namerp  rN   rv  rq  rl  rr  rs  rt  ru  rÝ   rw  rx  rg  rˆ  r‰  rŠ  ztext/csvúContent-Dispositionz%attachment; filename=parts-export.csv©Úmimetyper&   râ   )r   rC  r8   r   r§   r¼   r�   rz  rS  r…   rW   r{  r<   rU   rV   r·   r¸   r†  ÚwriterÚwriterowr   r¿   rå   rp   ré   )r´   r[   ro  rh  r|  r?  r}  r~  r  r€  r�  rÆ   rÀ   rŽ  rS   rë   rH   rH   rI   Úparts_export  sŒ   
	


 


Ü%ÿðÿý
ý 
€ý
r�  z/api/parts/importc            $      C   sp  t j d¡} | stddiƒdfS |  ¡ jddd�}t t 	|¡¡}t
|ƒ}|s0tddd	gd
œƒS tƒ }d}d}g }�zø�zÐ| ¡ ��°}t|dd�D �] \}	}
|
 d¡pTd ¡ }|
 d¡p]d ¡ }|se|seqI|
 d¡pkd ¡ pod }zt|
 d¡pxd ¡ p|dƒ}W n tyŠ   d}Y nw zt|
 d¡p“d ¡ p—dƒ}W n ty¥   d}Y nw |
 d¡p¬d ¡ }zt|
 d¡p·d ¡ p»dƒ}W n tyÉ   d}Y nw zt|
 d¡pÒd ¡ pÖdƒ}W n tyä   d}Y nw |
 d¡pëd ¡ }|
 d¡pôd ¡ }|
 d¡pýd ¡ �pd }|
 d¡�p
d ¡ �pd }t||
 d¡ƒ}|
 d¡�pd ¡ }|
 d¡�p)d ¡ }|
 d¡�p3d ¡ }d }d }d }|�rQ| d|f¡ | ¡ }|�rQ|d  }|�rf| d!|f¡ | ¡ }|�rf|d  }|�r{| d!|f¡ | ¡ } | �r{| d  }zQ| d"||�p„df¡ | ¡ }!|!�r®| d#||||||td|ƒ|||||||||!d  f¡ |d$7 }n| d%|||||||td|ƒ||||||||f¡ |d$7 }W qI t�yê }" z| d&|	› d'|"› �¡ W Y d }"~"qId }"~"ww W d   ƒ n	1 �söw   Y  | ¡  t|||d
œƒW W | ¡  S  t�y2 }# z| ¡  tdt|#ƒiƒd(fW  Y d }#~#W | ¡  S d }#~#ww | ¡  w ))NÚfilerá   úNo file uploadedr#  r*   Úignore)Úerrorsr   zNo data rows)r&  Úupdatedr”  ru   )Ústartrp  r-   rN   rv  rq  Ú0rl  rr  rs  rt  ru  rÝ   rw  rx  rg  rˆ  r‰  rŠ  z1SELECT id FROM categories WHERE name = %s LIMIT 1rI  z8SELECT id FROM storage_locations WHERE name = %s LIMIT 1zZSELECT id FROM parts WHERE part_number = %s OR (part_number = '' AND barcode = %s) LIMIT 1a(  
                            UPDATE parts
                            SET category_id = %s, storage_location_id = %s, name = %s, description = %s,
                                overstock_location_id = %s, quantity = %s, overstock_quantity = %s, unit = %s, average_price = %s, minimum_stock_level = %s, comment = %s,
                                barcode = %s, datasheet_url = %s, manufacturer = %s, footprint = %s,
                                updated_at = CURRENT_TIMESTAMP
                            WHERE id = %s
                            r.   a§  
                            INSERT INTO parts
                            (category_id, storage_location_id, overstock_location_id, name, description, part_number, quantity, overstock_quantity, unit, average_price, minimum_stock_level, comment, barcode, datasheet_url, manufacturer, footprint)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            zRow z: râ   )r   Úfilesr8   r   r6   r7   r†  Ú
DictReaderr·   r¸   r=   r§   r¼   rY   r�   rP   r+  rV  rH  rU   r½   r  rp   r<   rÌ   rå   rè   ré   )$r‘  ÚtextÚreaderrÆ   r´   r&  r•  r”  r[   r`   rµ   rp  rN   rv  rq  rl  rr  rs  rt  ru  rÝ   rw  rx  rg  rˆ  Úlocation_namerŠ  ro  rh  ri  ÚcatÚlocÚover_locr^   Úrow_errrë   rH   rH   rI   Úparts_importp  s
  ÿÿÿÿþ	ð÷
ðú€"€ÿ�ÿt
ü 
€ü
r¡  z/api/parts/<int:part_id>)rÓ   rí   rî   c                 C   s   t ƒ }�zv�zGt|ƒ | ¡ ��3}tjdkr]| d| f¡ | ¡ }|s8tddiƒdfW  d   ƒ W W | ¡  S | d| f¡ dd„ | 	¡ D ƒ|d	< tt
|ƒƒW  d   ƒ W W | ¡  S tjd
k�rtjdd�pji }t|| d¡ƒ}| d| f¡ | ¡ }|s™| ¡  tddiƒdfW  d   ƒ W W | ¡  S | d¡}| d¡}| d¡}	|	dv r¯d }	n+zt|	ƒ}	W n$ ttfyÙ   | ¡  tddiƒdf Y W  d   ƒ W W | ¡  S w zt| dd¡pãdƒ}
W n% ttf�y   | ¡  tddiƒdf Y W  d   ƒ W W | ¡  S w |d u�r@t||ƒ�r@|d u�o#t|ƒt|ƒk}|�s@| ¡  tddiƒdfW  d   ƒ W W | ¡  S |
dk �r^| ¡  tddiƒdfW  d   ƒ W W | ¡  S |	d u�rˆ| d|	f¡ | ¡ �sˆ| ¡  tddiƒdfW  d   ƒ W W | ¡  S | d| d¡| d¡| d¡| d¡|	| d¡| dd¡|
| d ¡| d!d¡| d"d¡| d#¡| d$¡| d%¡| d&¡|| f¡ | d'| f¡ | ¡ }|�sð| ¡  tddiƒdfW  d   ƒ W W | ¡  S | ¡  tt
|ƒƒW  d   ƒ W W | ¡  S | d(| f¡ |jdk�r-| ¡  tddiƒdfW  d   ƒ W W | ¡  S | ¡  td)d*�W  d   ƒ W W | ¡  S 1 �sGw   Y  W n& t�ys } z| ¡  tdt|ƒiƒd+fW  Y d }~W | ¡  S d }~ww W | ¡  d S | ¡  w ),NrÓ   a×  
                    SELECT p.*, c.name AS category_name, s.name AS storage_location_name
                    , so.name AS overstock_location_name
                    FROM parts p
                    LEFT JOIN categories c ON p.category_id = c.id
                    LEFT JOIN storage_locations s ON p.storage_location_id = s.id
                    LEFT JOIN storage_locations so ON p.overstock_location_id = so.id
                    WHERE p.id = %s
                    rá   rð   rñ   zó
                    SELECT id, file_path, original_filename, url, source, is_primary, created_at
                    FROM part_images
                    WHERE part_id = %s
                    ORDER BY is_primary DESC, id
                    c                 S   re  rH   rf  rQ   rH   rH   rI   r«     r¬   zparts_id.<locals>.<listcomp>Úimagesrí   TrÚ   rg  z3SELECT storage_location_id FROM parts WHERE id = %srh  ri  rj  rk  r#  rl  r   rm  z2Cannot move part to a storage slot marked unusabler;  rn  aê  
                    UPDATE parts
                    SET category_id = %s, storage_location_id = %s, name = %s, description = %s,
                        overstock_location_id = %s, part_number = %s, quantity = %s, overstock_quantity = %s, unit = %s, average_price = %s, minimum_stock_level = %s,
                        comment = %s, barcode = %s, datasheet_url = %s, manufacturer = %s, footprint = %s, updated_at = CURRENT_TIMESTAMP
                    WHERE id = %s
                    ro  rN   rÝ   rp  rq  rr  rs  rt  ru  rv  rw  rx  ry  zDELETE FROM parts WHERE id = %srò   ró   râ   )r§   rÍ   r¼   r   rä   rU   r½   r   rå   rV   rÒ   ræ   rH  r8   rè   rP   r*  r+  rý   rÌ   rö   r   rp   ré   )Úpart_idr´   r[   ÚpartrB   rg  Úexisting_rowÚold_locr‚  ri  rl  Ú	same_slotrµ   rë   rH   rH   rI   Úparts_idü  sö   
	öð
p¡ù	
å
p­Ü
p
µ

Ñ
p½Ì
pÂÇ
p
ÊÄ
p
Í
¿
pÒ


ïøž
pó
œ
pö—
pú•
p�l 
€ü”pr¨  c                 C   s   d| › d|› �  dd¡S )Nzparts/rŠ   r‰   ©r‹   )r£  ÚfilenamerH   rH   rI   Ú_image_upload_relativet  s   r«  c                 C   ó   d| › d�  dd¡S )Núdatasheets/z.pdfr‰   rŠ   r©  ©r£  rH   rH   rI   Ú_datasheet_upload_relativex  ó   r¯  c                 C   r¬  )Nr­  z
.thumb.jpgr‰   rŠ   r©  r®  rH   rH   rI   Ú_datasheet_thumbnail_relative|  r°  r±  c                 C   sÞ   t du rdS d}z\zFt  | ¡}|jdk r"W W |dur | ¡  dS dS | d¡}|jt  dd¡dd�}tjtj	 
|¡dd� | |¡ W W |durN| ¡  dS dS  tyd   Y W |durb| ¡  dS dS w |durn| ¡  w w )	zCRender first PDF page to JPEG thumbnail. Returns True if generated.NFr.   r   g      ø?)ÚmatrixÚalphaTrr   )Úfitzrn   Ú
page_countrå   Ú	load_pageÚ
get_pixmapÚMatrixrk   rw   r,   rx   Úsaverp   )Úpdf_pathÚ
thumb_pathÚdocÚpageÚpixrH   rH   rI   Ú_generate_datasheet_thumbnail€  s6   

	ÿ
ø
ÿýÿý
ÿr¿  z"/api/parts/<int:part_id>/duplicatec                 C   sÖ  t jdd�pi }| d¡}|du rtddiƒdfS zt|ƒ}W n ttfy2   tddiƒdf Y S w tƒ }tƒ }�z+�zt	|ƒ | 
¡ ��ß}| d	| f¡ | ¡ }|sitdd
iƒdfW  d  ƒ W W | ¡  S | d¡|kr…tddiƒdfW  d  ƒ W W | ¡  S | d|f¡ | ¡ r¥tddiƒdfW  d  ƒ W W | ¡  S t||ƒr¿tddiƒdfW  d  ƒ W W | ¡  S | d|d |d|d |d |d dd|d | d¡pÛd|d |d d|d dd|d t|| d¡ƒf¡ |j}| d| f¡ | ¡ D ]Š}| d¡�r| d||d |d  |d! f¡ �q| d"¡�r‹t||d" ƒ}	|	�s.�q| d#¡�p;tj |d" ¡}
tj |
¡d$ �pQtj |d" ¡d$ �pQd%}t d&¡ ¡ }|› |› �}t||ƒ}tj ||¡}tjtj |¡dd'� t |	|¡ | d(|||
|d  |d! f¡ �q| d)¡}|�rÇtj ||¡}tj |¡�rÇt |ƒ}tj ||¡}tjtj |¡dd'� t ||¡ | d*||f¡ | d+¡}|�rtj ||¡}tj |¡�rt!|ƒ}tj ||¡}tjtj |¡dd'� t ||¡ | d,||f¡ | d-|f¡ | ¡ }| d.|f¡ d/d0„ | ¡ D ƒ|d1< W d  ƒ n	1 �s)w   Y  | "¡  tt#|ƒƒd2fW W | ¡  S  t$�ye } z| %¡  tdt&|ƒiƒd3fW  Y d}~W | ¡  S d}~ww | ¡  w )4zeClone a part into another storage location (qty reset to 0); copy average_price and file attachments.TrÚ   rh  Nrá   zstorage_location_id requiredr#  z&storage_location_id must be an integerry  zSource part not foundrñ   z.Target location is the same as the source partr<  z$Target storage location is not emptyz&Target storage slot is marked unusableaÐ  
                INSERT INTO parts
                (category_id, storage_location_id, overstock_location_id, name, description, part_number,
                 quantity, overstock_quantity, unit, average_price, minimum_stock_level, comment,
                 barcode, datasheet_url, datasheet_file_path, datasheet_thumbnail_path, manufacturer, footprint)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ro  rN   rÝ   rp  r   rr  rs  rt  ru  rw  rx  rg  zÓ
                SELECT id, file_path, original_filename, url, source, is_primary
                FROM part_images
                WHERE part_id = %s
                ORDER BY is_primary DESC, id
                ÚurlúRINSERT INTO part_images (part_id, url, source, is_primary) VALUES (%s, %s, %s, %s)ÚsourceÚ
is_primaryÚ	file_pathÚoriginal_filenamer.   z.biné   rr   úoINSERT INTO part_images (part_id, file_path, original_filename, source, is_primary) VALUES (%s, %s, %s, %s, %s)Údatasheet_file_pathz7UPDATE parts SET datasheet_file_path = %s WHERE id = %sÚdatasheet_thumbnail_pathz<UPDATE parts SET datasheet_thumbnail_path = %s WHERE id = %sa0  
                SELECT p.*, c.name AS category_name, s.name AS storage_location_name
                FROM parts p
                LEFT JOIN categories c ON p.category_id = c.id
                LEFT JOIN storage_locations s ON p.storage_location_id = s.id
                WHERE p.id = %s
                zß
                SELECT id, file_path, original_filename, url, source, is_primary, created_at
                FROM part_images
                WHERE part_id = %s
                ORDER BY is_primary DESC, id
                c                 S   re  rH   rf  rQ   rH   rH   rI   r«   !  r¬   z"part_duplicate.<locals>.<listcomp>r¢  rà   râ   )'r   ræ   r8   r   rP   r*  r+  r§   rˆ   rÍ   r¼   rU   r½   rå   rý   rH  rç   rV   r¥   rk   r,   Úbasenamer˜   ÚurandomÚhexr«  r…   rw   rx   ÚshutilÚcopy2r—   r¯  r±  rÌ   rÒ   rp   rè   ré   )r£  rB   Útarget_storage_location_idr´   r›   r[   ÚsrcÚnew_idÚimgÚsrc_pathÚorigÚextÚrandÚnew_fnrŽ   Údst_pathÚdsÚsrc_pdfÚnew_relÚdst_pdfÚds_thumbÚ	src_thumbÚnew_thumb_relÚ	dst_thumbr¤  rë   rH   rH   rI   Úpart_duplicate•  s  
ÿü 
‚ú 
„þô 

Šò 
�îøù	þ,
þ€
þ
þø
ù	„}
ü 
€ü
rá  z/api/parts/<int:part_id>/imagesc              
   C   sœ  t j d¡}|stddiƒdfS tƒ }t|jpdƒpd}tj 	|¡d p%d}t 
d¡ ¡ }|› |› �}t| |ƒ}tj ||¡}tjtj |¡d	d
� | |¡ tƒ }	zvzP|	 ¡ �2}
|
 d| f¡ |
 ¡ d dkrjdnd}|
 d| ||d|f¡ |
 d|
jf¡ |
 ¡ }W d   ƒ n1 s�w   Y  |	 ¡  tt|ƒƒdfW W |	 ¡  S  tyÈ } z|	 ¡  tdt|ƒiƒdfW  Y d }~W |	 ¡  S d }~ww |	 ¡  w )NÚimagerá   r’  r#  z	image.jpgr.   z.jpgrÆ  Trr   ú8SELECT COUNT(*) AS c FROM part_images WHERE part_id = %sr°   r   rÇ  Úuploadedú'SELECT * FROM part_images WHERE id = %srà   râ   )r   r˜  r8   r   rˆ   r
   rª  rk   r,   r˜   rË  rÌ  r«  r…   rw   rx   r¹  r§   r¼   rU   r½   rç   rÌ   rÒ   rå   rp   rè   ré   )r£  râ  r›   Ú	safe_namerÕ  rÖ  rª  rŽ   Úfullr´   r[   rÃ  rµ   rë   rH   rH   rI   Úpart_upload_image+  sD   


þ
ø	
ü 
€ü
rè  z#/api/parts/<int:part_id>/images/urlc              
   C   s*  t jdd�pi }| d¡}|stddiƒdfS tƒ }zuzO| ¡ �1}| d| f¡ | ¡ d d	kr2d
nd	}| d| |d|f¡ | d|jf¡ | ¡ }W d   ƒ n1 sTw   Y  | 	¡  tt
|ƒƒdfW W | ¡  S  ty� } z| ¡  tdt|ƒiƒdfW  Y d }~W | ¡  S d }~ww | ¡  w )NTrÚ   rÀ  rá   zurl requiredr#  rã  r°   r   r.   rÁ  Úinternetrå  rà   râ   )r   ræ   r8   r   r§   r¼   rU   r½   rç   rÌ   rÒ   rå   rp   rè   ré   )r£  rB   Ú	image_urlr´   r[   rÃ  rµ   rë   rH   rH   rI   Úpart_add_image_urlN  s4   


þ
ø	
ü 
€ü
rë  z6/api/parts/<int:part_id>/images/<int:image_id>/primaryc              
   C   sà   t ƒ }zgzA| ¡ �'}| d| f¡ | d|| f¡ | d| f¡ dd„ | ¡ D ƒ}W d   ƒ n1 s3w   Y  | ¡  t|ƒW W | ¡  S  tyj } z| ¡  tdt	|ƒiƒdfW  Y d }~W | ¡  S d }~ww | ¡  w )Nz8UPDATE part_images SET is_primary = 0 WHERE part_id = %szDUPDATE part_images SET is_primary = 1 WHERE id = %s AND part_id = %szISELECT * FROM part_images WHERE part_id = %s ORDER BY is_primary DESC, idc                 S   re  rH   rf  rQ   rH   rH   rI   r«   p  r¬   z$part_set_primary.<locals>.<listcomp>rá   râ   )
r§   r¼   rU   rV   rÌ   r   rå   rp   rè   ré   )r£  Úimage_idr´   r[   rÆ   rë   rH   rH   rI   Úpart_set_primaryh  s$   
ü

ü 
€ü
rí  z./api/parts/<int:part_id>/images/<int:image_id>c              
   C   s0  t ƒ }z�zid }| ¡ �7}| d|| f¡ | ¡ }|s/tddiƒdfW  d   ƒ W W | ¡  S | d¡}| d|f¡ W d   ƒ n1 sEw   Y  | ¡  |rctj	 
tƒ |¡}tj	 |¡rct |¡ tdd�W W | ¡  S  ty’ } z| ¡  tdt|ƒiƒd	fW  Y d }~W | ¡  S d }~ww | ¡  w )
Nz@SELECT file_path FROM part_images WHERE id = %s AND part_id = %srá   rð   rñ   rÄ  z%DELETE FROM part_images WHERE id = %srò   ró   râ   )r§   r¼   rU   r½   r   rå   r8   rÌ   rk   r,   r…   rˆ   rl   Úremover   rp   rè   ré   )r£  rì  r´   rÄ  r[   rµ   rç  rë   rH   rH   rI   Úpart_delete_imagez  s6   
ü

ôú

ü 
€ü
rï  z"/api/parts/<int:part_id>/datasheet)rÓ   rØ   rî   c              
   C   sÔ  t ƒ }�zà�z±| ¡ ��¡}tjdkrØtj d¡}|s/tddiƒdfW  d   ƒ W W | ¡  S t| ƒ}t	j
 tƒ |¡}t| ƒ}t	j
 tƒ |¡}t	jt	j
 |¡dd� | |¡ | d| f¡ | ¡ }|sytdd	iƒd
fW  d   ƒ W W | ¡  S | d¡}	|	r—|	|kr—t	j
 tƒ |	¡}
t	j
 |
¡r—t	 |
¡ t||ƒ}|s©t	j
 |¡r©t	 |¡ | d||r±|nd | f¡ | d| f¡ | ¡ }| ¡  tt|ƒƒW  d   ƒ W W | ¡  S | d| f¡ | ¡ }|sútddiƒd
fW  d   ƒ W W | ¡  S tjdk�rX| d¡}|�r(t	j
 tƒ |¡}t	j
 |¡�r(t|ƒW  d   ƒ W W | ¡  S | d¡�rCt|d dd�W  d   ƒ W W | ¡  S tddiƒd
fW  d   ƒ W W | ¡  S | d¡}|�rtt	j
 tƒ |¡}t	j
 |¡�rtt	 |¡ | d¡}|�r�t	j
 tƒ |¡}t	j
 |¡�r�t	 |¡ | d| f¡ | ¡  tdd�W  d   ƒ W W | ¡  S 1 �s±w   Y  W n& t�yÝ } z| ¡  tdt|ƒiƒdfW  Y d }~W | ¡  S d }~ww W | ¡  d S | ¡  w )NrØ   Ú	datasheetrá   r’  r#  Trr   z3SELECT datasheet_file_path FROM parts WHERE id = %szPart not foundrñ   rÈ  zVUPDATE parts SET datasheet_file_path = %s, datasheet_thumbnail_path = %s WHERE id = %sry  z\SELECT datasheet_file_path, datasheet_thumbnail_path, datasheet_url FROM parts WHERE id = %srð   rÓ   rw  i.  )ÚcodezNo datasheetrÉ  zZUPDATE parts SET datasheet_file_path = NULL, datasheet_thumbnail_path = NULL WHERE id = %srò   ró   râ   )r§   r¼   r   rä   r˜  r8   r   rå   r¯  rk   r,   r…   rˆ   r±  rw   rx   r¹  rU   r½   rl   rî  r¿  rÌ   rÒ   r   r   r   rp   rè   ré   )r£  r´   r[   ÚpdfrŽ   rç  Ú	thumb_relÚ
thumb_fullr^   ÚoldÚold_fullÚ	has_thumbrµ   rÈ  rÉ  Úthumbrë   rH   rH   rI   Úpart_datasheet“  s®   
ü
BÃ
ò
B
Í


þ
â
BÞÝ
Bâ
×
BèÕ
BêÔ
B
ì


þÃ
B¾> 
€üÂBrù  z/api/images/searchc               
   C   s0  t j d¡} | stddiƒdfS trtstg ddœƒS z_tj tt| ddd	œ¡}tj j	d
|› �dd��}t
 | ¡  d¡¡}W d   ƒ n1 sHw   Y  g }| dg ¡D ]}| | d¡| d¡pci  d¡pk| d¡| d¡dœ¡ qUtd|iƒW S  ty— } ztt|ƒg dœƒdfW  Y d }~S d }~ww )Nr?  rá   z
q requiredr#  z\Image search not configured. Set PARTSINV_IMAGE_SEARCH_API_KEY and PARTSINV_IMAGE_SEARCH_CX.)rÎ   Úmessagerâ  r  )rÐ   Úcxr?  Ú
searchTypeÚnumz+https://www.googleapis.com/customsearch/v1?r(   r*   rÎ   ÚlinkÚthumbnailLinkÚtitle)rþ  Ú	thumbnailr   )rá   rÎ   râ   )r   rC  r8   r   r   r   r0   ÚparseÚ	urlencoder3   r4   r5   r6   r7   r<   rp   ré   )r?  r~  rA   rB   rÎ   rD   rë   rH   rH   rI   Úimage_searchÜ  s<   ûÿ	ÿýÿ"€ÿr  z/api/settings/upload-dirc                  C   sj   t ƒ } tjdkrtd|  dt¡iƒS tjdd�pi }t| d¡p!dƒ ¡ p&d}|| d< t	| ƒ td|iƒS )NrÓ   r   TrÚ   r€   )
rd   r   rä   r   r8   r   ræ   ré   r�   rz   )r   rB   r†   rH   rH   rI   Úsettings_upload_dirý  s   
r  z/api/settings/databasec                  C   sª   t jdkr
ttƒ ƒS t jdd�pi } tƒ }tƒ }|  d|d ¡|d< |  d|d ¡|d< |  d|d ¡|d< |  d|d ¡|d< d| v rL|  dd	¡|d< t|ƒ ttƒ ƒS )
NrÓ   TrÚ   r   r   r   r   r   r-   )	r   rä   r   r©   ræ   rd   r~   r8   rz   )rB   r   ÚcurrentrH   rH   rI   Úsettings_database	  s   


r  z/api/settings/database/testc               
   C   s  t jdd�pi } |  d¡pdt|  d¡pdƒ|  d¡|  dd	¡|  d
¡tjjdœ}d }zXz4tjdi |¤Ž}| ¡ �}| 	d¡ | 
¡  W d   ƒ n1 sMw   Y  tddiƒW W |r`| ¡  S S  tyƒ } ztdt|ƒdœƒW  Y d }~W |r~| ¡  S S d }~ww |r‹| ¡  w w )NTrÚ   r   Ú	localhostr   iê  r   r   r-   r   )r   r   r   r   r   r   zSELECT 1ÚsuccessF)r	  rú  rH   )r   ræ   r8   rP   r{   r|   r}   r¦   r¼   rU   r½   r   rå   rp   ré   )rB   Úconfigr´   r[   rë   rH   rH   rI   Úsettings_database_test  s:   
ú


þ
ÿý
ÿ€ý
ÿr  z/api/settings/uic                  C   sV   t ƒ } tjdkrttƒ ƒS tjdd�pi }d|v r"t| d¡ƒ| d< t| ƒ ttƒ ƒS )NrÓ   TrÚ   rc   )	rd   r   rä   r   rh   ræ   re   r8   rz   )r   rB   rH   rH   rI   Úsettings_ui3  s   


r  z#/api/settings/footprints/sync-kicadc               
   C   s¼   t ƒ } zUz.t| ƒ |  ¡ �}t|ƒ}W d   ƒ n1 sw   Y  |  ¡  tddi|¥ƒW W |  ¡  S  tyX } z|  ¡  tdt	|ƒdœƒdfW  Y d }~W |  ¡  S d }~ww |  ¡  w )Nr	  TF)r	  rá   râ   )
r§   rÍ   r¼   rb   rÌ   r   rå   rp   rè   ré   )r´   r[   Úresultrë   rH   rH   rI   Úsettings_footprints_sync_kicad?  s    

ÿ
ü"
€ü
r  z/api/settings/backup/fullc               
   C   s¢   t ƒ } zHz&t| ƒ t| ƒ}dt ¡  d¡› d�}t|ddd|› d�id�W W |  ¡  S  tyK } zt	d	t
|ƒiƒd
fW  Y d }~W |  ¡  S d }~ww |  ¡  w )NÚparts_inventory_backup_z%Y%m%d_%H%M%Sz.sqlzapplication/sqlr‹  zattachment; filename="ú"rŒ  rá   râ   )r§   rÍ   rÊ   r   rº   Ústrftimer   rå   rp   r   ré   )r´   Údump_sqlrª  rë   rH   rH   rI   Úsettings_backup_fullO  s"   ý
ý 
€ý
r  z/uploads/<path:filename>c                 C   s  t ƒ }| pd dd¡}tj ||¡}tj |¡rt||ƒS tj |¡\}}tj |¡\}}tj ||¡}|r…tj 	|¡r…|r]|rKtj ||¡ dd¡n|}	tj tj ||	¡¡r]t||	ƒS tj ||› d�¡}
dd„ t
 
|
¡D ƒ}|r…tj |d |¡ dd¡}t||ƒS tdd	iƒd
fS )Nr-   r‰   rŠ   r”   c                 S   s   g | ]
}t j |¡r|‘qS rH   )rk   r,   r—   )rR   r  rH   rH   rI   r«   t  r¶   zuploads.<locals>.<listcomp>r   rá   rð   rñ   )rˆ   r‹   rk   r,   r…   r—   r	   r:   r˜   r™   rš   Úrelpathr   )rª  r›   Ú
normalizedrž   rŸ   r    r¡   rÕ  r£   Ú
no_ext_relr  ÚmatchesÚ	rel_matchrH   rH   rI   Úuploadsa  s&   


r  rŠ   r,   r-   )Údefaultsz/<path:path>c                 C   s„   t }|  d¡s|  d¡rtddiƒdfS | r&tj tj || ¡¡r&t|| ƒS tj |d¡}tj |¡r8t|dƒS tdd|d	œƒd
fS )Nzapi/r“   rá   rð   rñ   z
index.htmlz!PartsInventory frontend not builtz@Build the React client and place output in PARTSINV_CLIENT_DIST.)rá   rú  Údisti÷  )r   r–   r   rk   r,   rl   r…   r	   )r,   Údist_dirÚ
index_pathrH   rH   rI   Úspa|  s    

ýÿúr  )eÚ__doc__r†  rš   r·   r4   rk   r  rÍ  Úurllib.parser0   Úurllib.requestr   r{   Úflaskr   r   r   r   r   r   r	   Úwerkzeug.utilsr
   r´  rp   r
  r   r   r   r   r   r   r   r   r   Ú__name__Úbpr|   r}   Ú	DB_CONFIGr,   r…   rx   rƒ   rm   Ú_UPLOAD_OVERRIDE_FILEr2   rX   rJ   rb   rh   rd   rz   r~   rˆ   r¥   r§   r©   rÊ   rÍ   rÒ   Úrouter×   rì   rù   rý   r   r  r  r!  r7  r8  r>  rE  rH  rS  rW  rƒ  r„  r�  r¡  r¨  r«  r¯  r±  r¿  rá  rè  rë  rí  rï  rù  r  r  r  r  r  r  r  r  r  rH   rH   rH   rI   Ú<module>   sò    $ÿ,
ù"
6$
	+, G



ý	
K

#
- 

|

`
 
w
 
"



H
 






