U iw@sPddlmZddlmZmZmZmZddlmZGdddZGdddZ dS) datetime)OptionalListDictUnionget_connectionc@sdeZdZdZd eeeeeeeeeeeeeeeeeeeeeeeeeeeedddZedddZ dS) OpportunityAndCostingDTOuIDTO para la información unificada de Oportunidad y Encabezado de Costeo.N costing_idcrm_opportunity_numbercrm_contact_namecrm_contact_typecrm_assigned_salespersoncrm_contact_adresscrm_contact_coloniacrm_contact_citycrm_contact_numbercrm_contact_countrycrm_contact_legal_identifiercrm_contact_zipcrm_contact_statecrm_contact_email case_costsale_price_listsale_price_mindiscount_max_percentrun_time_number run_time_typetechnical_terms_and_conditionsFinancePercentTaxCode CurrencyCodeQ_TaxRate_FrontESQ_TaxRate_FrontENQ_Currency_FrontESQ_Currency_FrontENcCs||_||_||_||_||_||_||_||_| |_| |_ | |_ | |_ | |_ ||_ ||_||_||_||_||_||_|f|_||_|f|_|f|_|f|_|f|_|f|_||_dS)N CostingIDCRM_OpportunityNumberCRM_ContactNameCRM_ContactTypeCRM_AssignedSalespersonCRM_ContactAdressCRM_ContactColoniaCRM_ContactCityCRM_ContactNumberCRM_ContactCountryCRM_ContactLegalIdentifierCRM_ContactZipCRM_ContactStateCRM_ContactEmailCaseCost SalePriceList SalePriceMinDiscountMaxPercent RunTimeNumber RunTimeTypeTechnicalTermsAndConditionsr!r"r#r$r%r&r')selfr r rrrrrrrrrrrrrrrrrrr r!r"r#r$r%r&r'r?hC:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\App\api\services\quote_reception_services.py__init__ s8z!OpportunityAndCostingDTO.__init__returncCs|j|j|j|j|j|j|j|j|j|j |j |j |j |j |jt|jt|jt|j|j|j|jd|jdk rxt|jnd|jd|jd|jd|jd|jd|jdS)z2Convierte el objeto a un diccionario serializable.rNr()r)r*r+r,r-r.r/r0r1r2r3r4r5r6r7floatr8r9r:r;r<r=r!r"r#r$r%r&r')r>r?r?r@to_dict8s:z OpportunityAndCostingDTO.to_dict) NNNNNNNNNN) __name__ __module__ __qualname____doc__strrDintrAdictrEr?r?r?r@r sN -r c@seZdZdZeeeedddZee e e e e e ffdddZee e e e fddd Zeee e dd d Zeee d d dZeeed ddZee edddZdS)Quote_Reception_ServiceuYClase de servicio para manejar la lógica de recepción de datos de costeo y oportunidad.) costing_numrCcCsRd}zt}|}||||}W5QRX|rt|d|d|d|d|d|d|d|d |d |d |d |d |d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|ddW5QRWSW5QRWdSQRXWn8tk rL}ztd |WYdSd}~XYnXdS)!z{ Consulta unificada que obtiene los datos de Q_CostingHead y Q_OpportunityCRM en una sola llamada. aO SELECT TOP 1 Q_CostingHead.CostingID, Q_OpportunityCRM.CRM_OpportunityNumber, Q_OpportunityCRM.CRM_ContactName, Q_OpportunityCRM.CRM_ContactType, Q_OpportunityCRM.CRM_AssignedSalesperson, Q_OpportunityCRM.CRM_ContactAdress, Q_OpportunityCRM.CRM_ContactColonia, Q_OpportunityCRM.CRM_ContactCity, Q_OpportunityCRM.CRM_ContactNumber, Q_OpportunityCRM.CRM_ContactCountry, Q_OpportunityCRM.CRM_ContactLegalIdentifier, Q_OpportunityCRM.CRM_ContactZip, Q_OpportunityCRM.CRM_ContactState, Q_OpportunityCRM.CRM_ContactEmail, Q_CostingHead.CaseCost, Q_CostingHead.SalePriceList, Q_CostingHead.SalePriceMin, Q_CostingHead.DiscountMaxPercent, Q_CostingHead.RunTimeNumber, Q_CostingHead.RunTimeType, Q_CostingHead.TechnicalTermsAndConditions, Q_CostingHead.FinancePercent, Q_CostingHead.TaxCode, Q_CostingHead.CurrencyCode, Q_TaxRate.FrontES as Q_TaxRate_FrontES, Q_TaxRate.FrontEN as Q_TaxRate_FrontEN, Q_Currency.FrontES as Q_Currency_FrontES, Q_Currency.FrontEN as Q_Currency_FrontEN FROM Q_CostingHead INNER JOIN Q_OpportunityCRM ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID LEFT JOIN Q_TaxRate ON Q_TaxRate.TaxCode = Q_CostingHead.TaxCode AND Q_TaxRate.CurrencyCode = Q_CostingHead.CurrencyCode AND Q_TaxRate.Active >= 1 LEFT JOIN Q_Currency ON Q_Currency.CurrencyCode = Q_CostingHead.CurrencyCode AND Q_Currency.Active >= 1 WHERE Q_CostingHead.CostingNum = ? ORDER BY Q_CostingHead.Version DESC; r r Nz#Error al obtener datos unificados: )r cursorexecutefetchoner Exceptionprint)rNqueryconnrjrower?r?r@get_unified_data_by_costing_num_sR.   z7Quote_Reception_Service.get_unified_data_by_costing_numrBc Csd}g}zpt^}|J}|||}|D],}||d|dt|d|ddq2W5QRXW5QRX|WStk r}ztd|gWYSd}~XYnXdS) ui Consulta la tabla Q_TaxRate para obtener el código y la descripción de los impuestos. zTSELECT TaxCode, FrontES, TaxAmount, CurrencyCode FROM Q_TaxRate WHERE Active = 1;rrOrPrQ)r"FrontES TaxAmountr#z Error al obtener los impuestos: Nr rjrkfetchallappendrDrmrn)rotaxesrprjrowsrqrrr?r?r@ get_taxess"  z!Quote_Reception_Service.get_taxesc Csd}g}zftT}|@}|||}|D]"}||d|d|ddq2W5QRXW5QRX|WStk r}ztd|gWYSd}~XYnXdS)uw Consulta la tabla Q_Currency para obtener el código, el nombre y el símbolo de las monedas activas. zJSELECT CurrencyCode, FrontES, CurrSymbol FROM Q_Currency WHERE Active = 1;rrOrP)r#rt CurrSymbolzError al obtener las monedas: N)r rjrkrwrxrmrn)ro currenciesrprjrzrqrrr?r?r@get_currenciess  z&Quote_Reception_Service.get_currenciescCsd}g}ztz}|f}||||}|D]F}||d|d|dt|d|dt|dt|dd q4W5QRXW5QRX|WStk r}ztd |gWYSd }~XYnXd S) uN Consulta Q_CostingDetail para obtener las líneas de costeo. a SELECT CostingLine, PartNum, PartDescription, Qty, UOMCode, UnitPrice, Amount FROM Q_CostingDetail WHERE CostingID = ( SELECT TOP 1 CostingID FROM Q_CostingHead WHERE CostingNum = ? ORDER BY Version DESC ) ORDER BY CostingLine ASC; rrOrPrQrRrSrT) CostingLinePartNumPartDescriptionQtyUOMCode UnitPriceAmountz%Error al obtener detalles de costeo: Nrv)rNrodetailsrprjrzrqrrr?r?r@get_costing_detailss*      z+Quote_Reception_Service.get_costing_details)datarCc7Cszddlm}ddlm}|d}d}t^}|H}||||}|d}d} || |f|} | r| dnd} |std|W5QRW5QRWd S|d} |d r|d nd} |d | |}|dsd n |dd }|d }|}|r:|d ||}|r:|dr:|d}td|d|td|ddtd|ddtd|dd|dr|d nd }|dr|d nd }|dr|d nd }| |||d|d|d|d|d|d|d|d|d|d |d!|d"|d#|p|d?|d@|dA|dBdC3}tdD|dE|dH|}|W5QRW5QRWSQRXW5QRXWnHtk r}z(tdG|dd l}|WYd Sd }~XYnXd S)Iuw Genera HTML de vista previa de la cotización SIN guardar en BD. Retorna el HTML renderizado. r)render_templaterr)u SELECT TOP 1 Q_CostingHead.CostingNum, Q_CostingHead.Version, -- Cliente Q_OpportunityCRM.CRM_ContactName, Q_OpportunityCRM.CRM_ContactType, Q_OpportunityCRM.CRM_OpportunityNumber, Q_OpportunityCRM.CRM_ContactEmail, Q_OpportunityCRM.CRM_ContactNumber, Q_OpportunityCRM.CRM_ContactCity, Q_OpportunityCRM.CRM_ContactState, Q_OpportunityCRM.CRM_ContactCountry, Q_OpportunityCRM.CRM_ContactAdress, -- ✅ El vendedor es el UserID de la oportunidad TRIM(CONCAT( ISNULL(Seller.FirstName, ''), ' ', ISNULL(Seller.MiddleName, ''), ' ', ISNULL(Seller.LastName, ''), ' ', ISNULL(Seller.SecondLastName, '') )) AS VendedorNombre, ISNULL(Seller.Email, '') AS VendedorEmail, ISNULL(Seller.ContactPhone, '') AS VendedorTelefono, Q_CostingHead.TaxCode FROM Q_CostingHead INNER JOIN Q_OpportunityCRM ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID -- ✅ JOIN directo: El vendedor es Q_OpportunityCRM.UserID LEFT JOIN Profiles AS Seller ON Q_OpportunityCRM.UserID = Seller.UserID WHERE Q_CostingHead.CostingID = ? r\z select TaxPercent from Q_TaxRate where TaxCode = ? u❌ No se encontró CostingID: NrOm SELECT MAX(Version) FROM Q_QuotationHead WHERE QuotationNum = ? r#zk SELECT FrontES FROM Q_Currency WHERE CurrencyCode = ? u 💱 Moneda: z -> u🔍 DEBUG - VendedorNombre: 'rY'u🔍 DEBUG - VendedorEmail: 'rZu 🔍 DEBUG - VendedorTelefono: 'r[r7r;r<r=rPrQrRrSrTrUrVrWrXz No asignadozventas@igsa.comzN/A SalePriceDiscountPercentr TotalAmountOvercostFactorQuotationLinesz%d/%m/%Yz%H:%MIGSAz"Integradora de Servicios Avanzadosproyecto_nombrez Por definirproyecto_requerimientosZNinguno caseSelectedcase1AnticipoPercentcase1FiniquitoPercentcase2AnticipoPercentcase2FrequencyTypecase2Periodicidad case2Cantidadcase2CantidadPeriodicidadcase3FrequencyTypecase3Periodicidad case3Cantidadcase3CantidadPeriodicidadcase4DiasFinanciamientocase5PlazoCreditoFlag)3 quotation_numversionr caso_costeor;r<r=cliente_nombre tipo_contactooportunidad_crm cliente_emailcliente_telefonocliente_ciudadcliente_estado cliente_paisZcliente_direccionvendedor_asignadovendedor_emailvendedor_telefono precio_listadescuento_porcentaje precio_ofertaimpuesto_codigoZimpuedesto_porcentaje precio_totalmoneda moneda_codigofactor_sobrecostolineas total_lineas fecha_actualZ hora_actualu año_actualempresaempresa_completarrrrrrrrrrrrrrrru ✅ Vendedor final en template: r*Emails/Ventas/Cotiz/PreviewCotizacion.htmlu#❌ Error al generar vista previa: )r)flaskrrgetr rjrkrlrnstriplennowstrftimeyearrm traceback print_exc)rrrr rorprjrqZtaxCodequery2tax_rowZ tax_percentrZcurrent_versionversion_resultZ next_version currency_codeZ currency_nameZcurrency_resultvendedor_nombrerr template_datahtmlrrrr?r?r@generate_quotation_previews   +              T 2z2Quote_Reception_Service.generate_quotation_previewcCsrz&t}|}|d}|d||}|s`dddW5QRW5QRWS|d}|d||}|dsdn |dd}|d |}td |d } || |||d |d |dd|dd|d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|d |d!f|d"g} d#} | D]T} || || d$| d%| d&| d'| d(| d)| d*| df q|td+|d,W5QRXd-d.||d/W5QRWSQRXWnDtk rl} z$td0| dt| dWYSd1} ~ XYnXd1S)2u Crea una nueva cotización en Q_QuotationHead y Q_QuotationDetail. Envía notificación por correo al departamento de ingeniería. r)zf SELECT CostingNum FROM Q_CostingHead WHERE CostingID = ? FzCostingID no encontrado)successerrorrrrO-uCreando cotización: a INSERT INTO Q_QuotationHead ( QuotationNum, Version, CaseCost, SalePrice, DiscountPercent, OvercostFactor, Amount, TaxCode, TotalAmount, CurrencyCode, Active, caseSelected, case1AnticipoPercent, case1FiniquitoPercent, case2AnticipoPercent, case2FrequencyType, case2Periodicidad, case2Cantidad, case2CantidadPeriodicidad, case3FrequencyType, case3Periodicidad, case3Cantidad, case3CantidadPeriodicidad, case4DiasFinanciamiento, case5PlazoCreditoFlag ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) r7rrrrr"rr#rrrrrrrrrrrrrrra INSERT INTO Q_QuotationDetail ( QuotationID, QuotationLine, CostingLineID, PartNum, PartDescription, Qty, UOMCode, UnitPrice, Amount ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) Z QuotationLineZ CostingLineIDrrrrru Cotización z creada exitosamente en BDTuCotización creada exitosamente)rmessageZ QuotationIDVersionuError al crear cotización: N) r rjrrkrlrncommitrmrJ)rrprjr Zcosting_resultrrr quotation_idinsert_head_queryZquotation_linesinsert_detail_querylinerrr?r?r@create_quotations   $    z(Quote_Reception_Service.create_quotation)r rCc Cszddlm}|}|}|d||}|r|drL|dnd|drb|dnd|drx|dnddW5QRW5QRWSW5QRXW5QRXWn0tk r}ztd|W5d}~XYnXddddS) u!Obtiene información del vendedorrra SELECT TOP 1 TRIM(CONCAT( ISNULL(Seller.FirstName, ''), ' ', ISNULL(Seller.MiddleName, ''), ' ', ISNULL(Seller.LastName, ''), ' ', ISNULL(Seller.SecondLastName, '') )) AS VendedorNombre, ISNULL(Seller.Email, '') AS VendedorEmail, ISNULL(Seller.ContactPhone, '') AS VendedorTelefono FROM Q_CostingHead INNER JOIN Q_OpportunityCRM ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID LEFT JOIN Profiles AS Seller ON Q_OpportunityCRM.UserID = Seller.UserID WHERE Q_CostingHead.CostingID = ? NrOrP)nombreemailtelefonou+⚠️ Error al obtener info del vendedor: )Consultas_SQL.conexionr rjrkrlrrmrn)r r rprjrqrrr?r?r@get_seller_infoas   8 z'Quote_Reception_Service.get_seller_infoN)rFrGrHrI staticmethodrKrr rsrrrJrrDr{r~rrLrrrr?r?r?r@rM\s"O$-S rMN) rtypingrrrrrr r rMr?r?r?r@s  T