
    BɎe=                     T   d dl Z d dlmZ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lT d dlZd dlZ e            Ze                    d           ed          fd	ed
efd            Ze                    d          d	efd            Ze                    d          d	efd            Ze                    d           ed          fd	edefd            Ze                    d          d	efd            Ze                    d           ed          fd	ed
efd            ZdS )    N)	APIRouterRequestBodystatus)DictList)jsonable_encoder)JSONResponse)*z
/create_po.request
po_detailsc                   K   t          |          }| j        j                                        }|                    d          D ]M}g |}t          |d                   dk    rt          |d                   }5t          |d                   dz   }Nd                    t          j                                        d          t          j                                        d          |          |d<   t          j        |d         d	          j	        |d<   t          j        |d
         d	          j	        |d
<   t          j
                                        d          j	        |d<   	 |                    d|d         |d         |d         |d
         |d         |d         d|d         d 
  
         |d         D ]X}|                    d||d         |d         |d         |d         |d         |d         |d         d|d         |d                    Y| j        j                                         t          d|i          S # t          $ r)}| j        j                                         Y d }~dS d }~ww xY w)Nzbselect count(*) as cnt,(select IDENT_CURRENT('PurchaseOrderList')) as idnt  from PurchaseOrderListr      zAVPL/PSD/{}/{}/{}YYYYMM	po_ref_nopo_datez
DD/MM/YYYYdelivery_datezAsia/Calcutta)tzinfoLast_updatez@insert into PurchaseOrderList values (?, ?, ?, ?, ?, ?, ?, ?, ?)	vendor_idpurchase_eventprepared_byproduct_detailszFinsert into PurchaseOrderItem values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
product_idhsn_codeqtyuom
base_pricecgstsgst	nick_namepo_nozError.PO not created.)r	   appmssqlcursorexecuteintformatarrowgetdatetimenowreplacecommitr
   	Exceptionrollback)r   r   r&   itr	row_countprdes          (D:\Sites\api_v1\routes\purchase_order.py	create_por7      s     !*--J[%%''F ~~  C  D  D ( (fs1v;;!CFIICFaII 2889K9KF9S9SUZU^U`U`UgUghlUmUmoxyyJ{!Ij&;\JJSJy"')J,G"V"V"_J %	 3 3? 3 K K TJ}$'Y!+.!+.!),!/2!"23!-0!-0
	 
	 
	 /0 	 	CNNc$|,z?u:u:|,v;v;%m4{+    	  """Y
   	  ' ' '""$$$&&&&&&'s   +CI 
I6I11I6z /get_po/{type}/{details}/{po_id}c           	      

  K   | j         j                                        }|                                dk    ri }|                    d|f          }|j        dk    r|D ]rt                    D ]\  }}||j        |         d         <   t          |           g |d<   |dk    r|                    d|          }|j        dk    ro|D ]LrHi }	t                    D ]\  }}||	j        |         d         <   |d         	                    |	           Mt          |           t          |          c S  dS t          |          c S ndS |                                dk    r|                    d	|          }g }
|j        dk    r|D ]Di }t                    D ]\  }}||j        |         d         <   |
	                    |           Et          |
           g }|dk    rg d
}|                    d|
d         d                   }|j        dk    rt          |          D ]{\  }g 	                    t          j                            |
fd          d         d                    |	                    t          t          |                               |t          |           |
d         d         |
d         d         |
d         d         |
d         d         |
d         d         |
d         d         |d}|S ndS |                                dk    r|                    d	|          }g }|j        dk    r|D ]Di }t                    D ]\  }}||j        |         d         <   |	                    |           Et          |           g }|dk    rSg d
}|                    d|d         d                   }|j        dk    rt          |          D ]\  }g t!          t          j                            |fd                    dk    rr	                    t          j                            |fd          d         d                    |	                    t          t          |                               t          |           |d         d         |d         d         |d         d         |d         d         |d         d         |d         d         |d}|S d S dS d S )NPOav  SELECT POL.PurchaseOrderListId, POL.PORefferenceNo, POL.VendorId, POL.CreatedDate, POL.DeliveryDate, POL.PurchaseEvent, C.FirstName+''+C.LastName as POPreparedBy, POL.POStatus, POL.remarks, SUM(GRL.FreightCharge) as FreightCharge FROM PurchaseOrderList as POL LEFT JOIN Vaaak.GrnList as GRL ON GRL.PurchaseOrderListId = POL.PurchaseOrderListId join Contact as C ON C.CustomerId = POL.POPreparedBy WHERE POL.PurchaseOrderListId = ? and C.CustomerDefault=1 GROUP BY POL.PurchaseOrderListId, POL.PORefferenceNo, POL.VendorId, POL.CreatedDate, POL.DeliveryDate, POL.PurchaseEvent, C.FirstName+''+C.LastName, POL.POStatus, POL.remarks r   r   1a  select  distinct POL.PurchaseOrderListId , POI.PurchaseOrderItemId , POI.ProductID , POI.PdtNickName,  PAT.Name AS ProductName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice,  SP.Name AS UOM_Name, POI.CGST, POI.SGST,  V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks from PurchaseOrderItem as POI JOIN  PurchaseOrderList AS POL(NOLOCK) ON POI.PurchaseOrderListId = POL.PurchaseOrderListId JOIN Product AS PP (NOLOCK) ON PP.ProductId = POI.ProductId  LEFT JOIN Vaaak.Vendor AS V (NOLOCK) ON POL.VendorId = V.VendorId  LEFT JOIN Specification AS SP (NOLOCK) ON POI.UOM = SP.SpecificationId  JOIN ProductTranslation AS PT  (NOLOCK)  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 JOIN Vaaak.ProductAdditionalTranslation AS PAT  (NOLOCK)  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  LEFT JOIN vaaak.GrnItem AS GI (NOLOCK)  ON POI.PurchaseOrderItemId=GI.PurchaseOrderItemId  where POI.PurchaseOrderListId = ?zRequested PO not foundGRNaZ  select GRI.GrnId,GRI.PurchaseOrderItemId,GRL.PurchaseOrderListId,GRI.ReceivedQty,GRI.InvoiceNumber,GRL.FreightCharge,C.FirstName+' '+C.LastName as PreparedBy,GRL.LastUpdate from Vaaak.GrnItem as GRI JOIN Vaaak.GrnList as GRL on GRL.GrnId = GRI.GrnId  JOIN Contact as C on C.CustomerId = GRL.PreparedBy where GRI.GrnId = ? and C.CustomerDefault=1 )item_idr   product_namer"   r   order_quantityr   r   uom_namer    r!   VendorId
VendorNameVendorAddress1VendorAddress2CityStateCountryCountrycodemobilelandlinegstremarksreceived_qtyaL  select  distinct POI.PurchaseOrderItemId , POI.ProductID , PAT.Name AS ProductName, POI.PdtNickName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, SP.Name AS UOM_Name, POI.CGST, POI.SGST, V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks from PurchaseOrderItem as POI JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = POI.PurchaseOrderListId JOIN Product AS PP  ON PP.ProductId = POI.ProductId  JOIN Vaaak.Vendor AS V  ON POL.VendorId = V.VendorId JOIN Specification AS SP ON SP.SpecificationId = POI.UOM JOIN ProductTranslation AS PT  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 JOIN Vaaak.ProductAdditionalTranslation AS PAT  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  where POI.PurchaseOrderListId = ?PurchaseOrderListIdc                 (    | d         d         k    S NPurchaseOrderItemIdr    xrows    r6   <lambda>zget_po.<locals>.<lambda>   s    ANcLdhklmhnLn     ReceivedQtyGrnIdInvoiceNumber
LastUpdateFreightCharge
PreparedBy)grn_nor#   
invoice_nogrn_dateFreight_chargePrepared_Byr   zGRN not foundRNaK  select  distinct POI.PurchaseOrderItemId , POI.ProductID , PAT.Name AS ProductName,POI.PdtNickName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, SP.Name AS UOM_Name, POI.CGST, POI.SGST, V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks from PurchaseOrderItem as POI JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = POI.PurchaseOrderListId JOIN Product AS PP  ON PP.ProductId = POI.ProductId  JOIN Vaaak.Vendor AS V  ON POL.VendorId = V.VendorId JOIN Specification AS SP ON SP.SpecificationId = POI.UOM JOIN ProductTranslation AS PT  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 JOIN Vaaak.ProductAdditionalTranslation AS PAT  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  where POI.PurchaseOrderListId = ?c                 (    | d         d         k    S rO   rQ   rR   s    r6   rU   zget_po.<locals>.<lambda>   s    aPeNfjmnojpNp rV   c                 (    | d         d         k    S rO   rQ   rR   s    r6   rU   zget_po.<locals>.<lambda>   s    qQfOgknopkqOq rV   )rn_nor#   r^   rn_dater`   ra   r   zRN not found)r$   r%   r&   upperr'   rowcount	enumeratecursor_descriptionjson_serializerappendr
   pydashcollectionsfilter_dictziplen)r   typedetailspo_idr&   r   rowsr2   itr_itmproduct	grn_items	temp_dictr   columnsgrn_detailsrn_items
rn_detailsrT   s                    @r6   get_por   N   s=     [%%''Fzz||t
~~W H
 
 =A '8 '8 &8(1# M MWEL
3#9##>q#ABB
 $J///46J01#~~%~~[ " #  #  =A--'+ R R#& !R.0G8A# %Z %ZWRY0Fs0KA0N(O(O$./@$A$H$H$Q$Q$Q+J777#/
#;#;;;;#;#;#;+J77777M&8'8R ,+zz||u~~ M $	% %
 	=A , ,	$-cNN H HLC@GIc4S9!<==  ++++I&&& O#~~W W W~~8 :C1F[9\	^ 	^ =A%%$-dOO H HS$f

".66yBnBnBnBnoopqr -/0 0 0 (..tC4E4E/F/FGGGG000'l73&q\*?@"+A,"? )!\ :&/l?&C$-aL$>'6  #"A D #? zz||t~~ M OTU U =A + +	$-cNN H HLC@GIc4S9!<==	****H%%% O#~~W W W~~	8 :B!EZ9[
] 
] =A%%$-dOO L LS$fv199(DpDpDpDpqqrruvvvJJ & 2 : :8EqEqEqEq r rst u$1!34 4 4 ,224GS8I8I3J3JKKK  000%a[1%a[)>?"*1+o">'{<8&.qk/&B$,QK$='6 
 "!I ~L ">o rV   z/get_alias_name/{productid}c                    K   | j         j                                        }|                    d|          }|j        dk    r |                                }|r|d         ndS dS )N`select top 1 PdtNickName from  PurchaseOrderItem where ProductId = ? and PdtNickName IS NOT nullr    )r$   r%   r&   r'   rh   fetchone)r   	productidr&   rv   results        r6   get_valid_por      sm      [%%''F>>|  ~G  H  HD}"*vayy*rrV   z/set_alias_namert   c                 8  K   t          |          }| j        j                                        }|                    d|d                   }|j        dk    rC|                    d|d         |d                    | j        j                                         dS dS )Nr   r   r   z<update PurchaseOrderItem set PdtNickName=? where ProductId=?	aliasnamezAlias name Updatedzsomething went wrong)r	   r$   r%   r&   r'   rh   r/   )r   rt   aliasdetailsr&   rv   s        r6   r   r     s      #G,,L[%%''F>>|  ~J  KV  ~W  X  XD}UVbcnVoq}  J  rK  	L  	L  	L  """##%%rV   z/get_valid_poc                    K   | j         j                                        }g }|                    d          D ]}|                    |d                    |S )NzDselect PurchaseOrderListId from PurchaseOrderList where POStatus = 1r   )r$   r%   r&   r'   rl   )r   r&   r#   r2   s       r6   r   r     s[      [%%''FE~~dee  SVLrV   z
/cancel_poc                 j  K   t          |          }| j        j                                        }|                    d|d                   }|j        dk    r\|                    d|d         |d                    | j        j                                         d                    |d                   S dS )Nz9select * from Vaaak.GrnList where PurchaseOrderListId = ?r#   r   zUupdate PurchaseOrderList set POStatus = -1, remarks = ? where PurchaseOrderListId = ?rK   zPO - {} Cancelledz#GRN/RN already created for this PO.)r	   r$   r%   r&   r'   rh   r/   r)   )r   r   r&   rv   s       r6   	cancel_por     s      !*--J[%%''F>>UV`ahVijjD}noy  {D  pE  GQ  RY  GZ  	[  	[  	[  """"))*W*=>>>44rV   )r*   fastapir   r   r   r   typingr   r   fastapi.encodersr	   fastapi.responsesr
   
lib.commonjsonrm   routerpostr7   r+   r   r   r   rQ   rV   r6   <module>r      s(    4 4 4 4 4 4 4 4 4 4 4 4         - - - - - - * * * * * *      	 \9=c 8' 8'W 8'$ 8' 8' 8' 8'@ .//k"' k" k" k" 0/k"Z )**    +* 9=c 	& 	& 	&$ 	& 	& 	&  	& O     \9=c 5 5W 5$ 5 5 5 5 5 5rV   