a cgC @sddlZddlZddlmZddlmZddlmZddlm Z ddl m Z m Z ddl mZddlmZdd lmZeje_eejd d d d Ze eZedddZeeedddZedddZeeedddZeeedddZedddddedddd dedd!dd"dedd#dd$dedd%dd&dgZd'Z edd(d)d*Z!e "d+e fd,e d-gZ#ee!eed.d e#d/Z$d4d2d3Z%dS)5N) app_config) ChatOpenAI)create_sql_agent) SQLDatabase)ChatPromptTemplateMessagesPlaceholder)text) create_engine)Tool2F) pool_size max_overflowecho)namecCshtL}|tdd|i}|}|r6d|dindd|diWdS1sZ0YdS)Nz@SELECT ListingId FROM Listings WHERE InternalListingName = :namerZ ListingIdrerrorzNo property found for '')engineconnectexecuterfetchone)rconnresultrowr6/var/www/html/cobodadashboardai.evdpl.com/app/utils.pyget_listing_id_by_names r) start_dateend_date property_idcCsptT}td|d|d||td|||d}|}dd|DWdS1sb0YdS)Nz start Datezend datez property idz;CALL GetPropertyRevenue (:StartDate, :EndDate, :PropertyId)Z StartDateZEndDate PropertyIdcSs(g|] }|dddvrt|qSZStatus)newmodifiedgetlowerdict.0rrrr #z(get_property_revenue..)rrprintrrmappingsallrrrrrrowsrrrget_property_revenues   r3)rcCsZt>}|tdd|i}|}dd|DWdS1sL0YdS)Nz,CALL GetPropertyRevenueByMonth (:PropertyId)r!cSs(g|] }|dddvrt|qSr"r&r*rrrr,,r-z(get_revenue_by_month..rrrrr/r0)rrrr2rrrget_revenue_by_month%s  r5cCshtL}|td|||d}|}td|dd|DWdS1sZ0YdS)Nz7CALL GetDailyReport (:StartDate, :EndDate, :PropertyId)r r2cSs(g|] }|dddvrt|qSr"r&r*rrrr,6r-z$get_daily_report..)rrrrr/r0r.r1rrrget_daily_report.s    r6cCs^tB}|td|||d}|}dd|DWdS1sP0YdS)Nz.r4r1rrrget_new_booking_report8s   r7cCstfi|SN)rargsrrrDr-r;zGet ListingId from Listings table given a property name (InternalListingName). Use this BEFORE calling stored procedures if only property name is given.)rfunc descriptioncCstfi|Sr8)r3r9rrrr;Ir-zCALL GetPropertyRevenue(StartDate, EndDate, PropertyId) to calculate total revenue for a property. Only use rows with Status in ('new', 'modified') and use SubTotalAmount instead of TotalPrice.cCstfi|Sr8)r5r9rrrr;Nr-zCALL GetPropertyRevenueByMonth(PropertyId) to get monthly grouped revenue for a property. Only use rows with Status in ('new', 'modified') and use SubTotalAmount.cCstfi|Sr8)r6r9rrrr;Sr-zCALL GetDailyReport(Date, PropertyId) to retrieve daily performance metrics, including total available rooms via `TotalRoomCount`. Only use rows with Status in ('new', 'modified') and use SubTotalAmount. Use TotalRoomCount for available rooms.cCstfi|Sr8)r7r9rrrr;Xr-zCALL GetNewBookingsReport(StartDate, EndDate, PropertyId) to get details about newly created bookings. Use SubTotalAmount and only include rows with Status in ('new', 'modified').u You are an AI agent designed to interact with a SQL Server database. You must only use the tools (stored procedures) provided to retrieve revenue, booking, and performance data. Do not query raw tables directly for financial or booking metrics. Do not query raw tables directly for financial or booking metrics. You MUST double check your query before executing it. Only use the information returned by the tools to construct your final answer. For current period revenue, use the `Revenue` field only. Do not use `PrevYearRevenue` unless the question explicitly asks for "previous year" or "last year". Do not confuse RevenueByCheckIn with Revenue. Use Revenue unless specifically asked for check-in-based calculation. You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again. DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database. If you need to filter on a proper noun, you must ALWAYS first look up the filter value using the "search_proper_nouns" tool! You have access to the following tables: {table_names}. You have all access of database so please check proper keys for properties. ### DO NOT: - NEVER use EXEC. Always use CALL for stored procedures. - Only use stored procedures via the registered tools. Never write raw CALL/EXEC SQL yourself. - Do not write raw SQL for revenue, occupancy, previous year occupancy, ADR, or booking metrics. - Do not pass property names directly into stored procedures. Use ListingId. - If a property name like 'Kensington 217' is mentioned, first call `get_listing_id_by_name` to get the `ListingId`. - If Property name is not found, consider it for all properties (propertyId=0 instead of NULL). - If user asks for guest details, use the reservations table to get guest information. If user asks for specific date, consider it for that date only. - If the question involves a specific day of the week (e.g., Tuesdays), filter those days only. - Always include only records where `Status IN ('new', 'modified')`. - If the user asks for a day of the week (e.g., "Tuesdays"), use calendar logic to filter those dates from the result. - Use only rows where Status IN ('new', 'modified'). ### AVAILABILITY & ROOM METRICS LOGIC: - If user asks about **room availability** (e.g., "rooms available", "vacant rooms", "room availability") for specific dates, call `GetDailyReport(StartDate, EndDate, PropertyId)` and use the **`TotalRoomsCount`** field. - If user asks for **total rooms sold** or **rooms booked**, use the **`ReservationCount`** field from `GetDailyReport`. - DO NOT use `SoldUnits`, `OccupiedRooms`, or mix `ReservationCount` and `TotalRoomCount`. - Ensure you clearly differentiate: - "Rooms available" → use `TotalRoomsCount` - "Rooms sold" or "rooms booked" → use `ReservationCount` ### INSTRUCTIONS: - If a property name like 'Kensington 217' is mentioned, first call `get_listing_id_by_name` to get the `ListingId`. - If we have a single date than pass it as `StartDate` and `EndDate` in the stored procedure. - If a date range is provided, use `StartDate` and `EndDate` in the stored procedure. - If no property name is provided, use `get_property_revenue` for all properties. - Use ONLY the following stored procedures: - `GetPropertyRevenue(StartDate, EndDate, PropertyId)` - `GetPropertyRevenueByMonth(PropertyId)` - `GetDailyReport(StartDate, EndDate, PropertyId)` - `GetNewBookingsReport(StartDate, EndDate, PropertyId)` Use only records where `Status IN ('new', 'modified')` and always prefer `SubTotalAmount` over `TotalPrice` for revenue. ### SCHEMA HIGHLIGHTS: - `Listings`: ListingId (PK), InternalListingName, Price, LatestActivityStart - `Reservations`: ListingMapId (FK → Listings.ListingId), SubTotalAmount, BookingDate, GuestFirstName, GuestLastName, CheckInDate, CheckOutDate ### Additional Tables: - `AspNetUsers`, `AspNetRoles`, `AspNetUserRoles`, `AspNetUserClaims`, `AspNetUserLogins`, `AspNetUserTokens` for user/role data - `AdminAccess` (ParentId hierarchy) - `ApplicationConfiguration` for app-level settings - `EmailTemplate` for communication templates ### BEHAVIOR: - Only respond with answers from the tools (procedures) if questions are related to property. - Always ensure ListingId is valid by calling the lookup tool. - Do not guess values or generate fake data. - Use joins only when working with standard tables like Listings and Reservations. -Additionally, fetch property listings from Listings with ListingId, InternalListingName, Price, and timestamps, and reservation details from Reservations with Id, Platform, GuestFirstName, and GuestLastName. - Retrieve comprehensive details of users, roles, claims, and access levels by fetching UserId, UserName, Email,FirstName, LastName and associated roles from AspNetUsers, AspNetUserRoles, and AspNetRoles, along with user claims from AspNetUserClaims and role claims from AspNetRoleClaims. Also provide result with FirstName and also provide proper results for multiple user details from AspNetUsers. Include user login details from AspNetUserLogins and user tokens from AspNetUserTokens. Fetch administrative access levels from AdminAccess, ensuring hierarchical relationships via ParentId. Retrieve active application settings from ApplicationConfiguration with Key, Value, and status details. Extract email templates from EmailTemplate with Id, Key, Title, Subject, and related metadata. If the user question is ambiguous or not related to the database, respond with: "I'm sorry, but I couldn't find an answer to your question. Can you try rephrasing it or providing more details?" Remove special characters like *, #, / from final output. zgpt-4oi)Z temperatureZ model_nameZ max_tokenssystem)Zhumanz{input}Zagent_scratchpadzopenai-functions)dbZtoolsZ agent_typeverbosepromptc Csd}||krz$td|i}|r&|WStdWn2tyb}zdt|}WYd}~n d}~00|d7}||kr|d|d}t|qd|iS)NrinputzEmpty responsezUnexpected error: rCr)agent_executorinvoke ValueError Exceptionstrtimesleep)natural_language_queryZ max_retriesZ base_delayZattemptreZ error_messageZ wait_timerrrgenerate_sql_querys $ rO)rBrC)&rKZopenaiconfigrZlangchain_openairZ"langchain_community.agent_toolkitsrZlangchain_community.utilitiesrZlangchain_core.promptsrrZsqlalchemy.sqlr sqlalchemyr Zlangchain.toolsr OPENAI_API_KEYZapi_keySQLALCHEMY_DATABASE_URIrr?rJrintr3r5r6r7Zstored_procedure_toolsrAZllmZ from_messagesZ custom_promptrFrOrrrrsp            P