using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LogisticsDAL.Query.Booking
{
    public static class BookingQB
    {
        public const string GET_BOOKING = @"SELECT
    -- ==================== BookingDTO Fields ====================
    B.BOOKINGID AS BookingId,
    B.BOOKINGDATE AS BookingDate,
    B.BOOKINGNUMBER AS BookingNumber,
    B.REFERENCEDATE AS BookingReferenceDate,
    B.REFERENCENUMBER AS BookingReferenceNumber,
    B.DELIVERYREMARKS AS BookingDeliveryRemarks,
    B.PAX AS BookingPax,
    B.DOCTYPE AS BookingDocType,
    B.PAYMENTTYPE AS BookingPaymentType,
    B.RISK AS BookingRisk,
    B.RATETYPE AS BookingRateType,
    B.RATE AS BookingRate,
    B.BASICFAREVALUE AS BookingBasicFareValue,
    B.AMOUNTPLUS AS BookingAmountPlus,
    B.AMOUNTMINUS AS BookingAmountMinus,
    B.TOTALVALUE AS BookingTotalValue,
    MPT.PAYMENTTERMCODE AS PaymentTypesCode,
    MPT.PAYMENTTERMNAME AS PaymentTypesName,
    B.BILLID AS AlfrescoId,
    PART.PARTYNAME AS FileName,
    B.PROMOCODE AS BookingPromoCode,
    B.BOOKINGNUMBER AS BookingSmsNumber,
    B.PODREMARKS AS BookingMail,
    BT.BIZTRANSACTIONTYPECODE AS DeliveryBizTransactionTypeCode,
    BT.BIZTRANSACTIONTYPENAME AS DeliveryBizTransactionTypeName,
    MOU1.ORGANIZATIONUNITCODE AS UnloadOUCode,
    MOU1.ORGANIZATIONUNITNAME AS UnloadOUName,
    MST1.STORECODE AS UnloadStoreCode,
    MST1.STORENAME AS UnloadStoreName,
    -- Add fields like BookingTotalValuesInWords if available in DB
    B.ACTUALWEIGHT AS BookingActualWeight,
    B.VOLUMEWEIGHT AS BookingVolumeWeight,
    B.BILLABLEWEIGHT AS BookingBillableWeight,
    B.VALUEOFGOODS AS BookingValueOfGoods,
    B.ISCOD AS BookingIsCOD,
    B.CODAMOUNT AS BookingCODAmount,
    B.ISDELIVERED AS BookingIsDelivered,
    B.DELIVERYDATE AS BookingDeliveryDate,
    B.DELIVERYTIME AS BookingDeliveryTime,
    B.GRNDATE AS BookingGRNDate,
    B.GRNNUMBER AS BookingGRNNumber,
    B.RECEIVEDBY AS BookingReceivedBy,
    B.ISSIGNATURE AS BookingIsSignature,
    B.ISSEAL AS BookingIsSeal,
    B.BILLSTATUS AS BookingBillStatus,
    B.MEASUREMENTTYPE AS BookingMessermentType,
    B.LENGTH AS BookingLength,
    B.WEIGHT AS BookingWeight,
    B.HEIGHT AS BookingHeight,
    B.CMSID AS BookingCMSID,
    B.PAYMENTDETAILS AS BookingPaymentDetails,
    B.CODPAYMENT AS BookingCODPayment,
    B.DELIVERYMODE AS BookingDeliveryMode,
    B.BALENO AS BookingBaleno,
    B.STATUS AS BookingStatus,
    B.VERSION AS BookingVersion,
    B.CREATEDBYID AS BookingCreatedById,
    B.CREATEDON AS BookingCreatedOn,
    B.MODIFIEDBYID AS BookingModifiedById,
    B.MODIFIEDON AS BookingModifiedOn,
    B.NOOFPRINT AS BookingNoOfPrint,
    B.REQUIREDPICKUPON AS BookingRequiredPickupOn,
    B.ISSPECIALSERVICE AS BookingIsSpecialService,
    B.DISTANCE AS BookingDistance,
    B.BILLABLEDISTANCE AS BookingBillableDistance,
    B.TMSRATETYPE AS BookingTMSRateType,
    M2.GCMCODE AS BookingStatussCode,
    M2.GCMNAME AS BookingStatussName,
    B.BOOKINGNUMBER AS BillNumber,
    TEM.ITEMCODE AS ItemCode,
    TEM.ITEMNAME AS ItemName,
    SKU.SKUCODE AS SKUCode,
    SKU.SKUNAME AS SKUName,
    -- ==================== BookingDTO Foreign Keys ====================
    BT.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    OU.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,
    OU.ORGANIZATIONUNITNAME AS OUCompanyName,
    P.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,
    FC.CITYID AS FromCityId,
    FC.CITYCODE AS FromCityCode,
    FC.CITYNAME AS FromCityName,
    TC.CITYID AS ToCityId,
    TC.CITYCODE AS ToCityCode,
    TC.CITYNAME AS ToCityName,
    C.PARTYBRANCHID AS ConsignorId,
    C.PARTYBRANCHCODE AS ConsignorCode,
    C.PARTYBRANCHNAME AS ConsignorName,
    CA.ADDRESSID AS ConsignorAddressId,
    CA.ADDRESSLINE1 AS ConsignorAddressLine1,
    CA.ADDRESSLINE2 AS ConsignorAddressLine2,
    CA.ADDRESSLINE3 AS ConsignorAddressLine3,
    CA.ADDRESSLINE4 AS ConsignorAddressLine4,
    CA.ADDRESSLINE5 AS ConsignorAddressLine5,
    CA.ZIPCODE AS ConsignorAddressZipCode,
    CA.CITYID AS ConsignorAddressCityName,
    CA.PHONE AS ConsignorAddressPhone,
    CA.MOBILE AS ConsignorAddressMobile,
    CA.GSTIN AS ConsignorAddressGstin,
    CA.VATTINNUMBER AS ConsignorAddressVATTINNumber,
    CA.VATCSTNUMBER AS ConsignorAddressVATCSTNumber,
    CC.CONTACTID AS ConsignorContactId,
    CC.NAME AS ConsignorContactName,
    CC.FIRSTNAME AS ConsignorContactFirstName,
    CG.PARTYBRANCHID AS ConsigneeId,
    CG.PARTYBRANCHCODE AS ConsigneeCode,
    CG.PARTYBRANCHNAME AS ConsigneeName,
    CGA.ADDRESSID AS ConsigneeAddressId,
    CGA.ADDRESSLINE1 AS ConsigneeAddressLine1,
    CGA.ADDRESSLINE2 AS ConsigneeAddressLine2,
    CGA.ADDRESSLINE3 AS ConsigneeAddressLine3,
    CGA.ADDRESSLINE4 AS ConsigneeAddressLine4,
    CGA.ADDRESSLINE5 AS ConsigneeAddressLine5,
    CGA.ZIPCODE AS ConsigneeAddressZipCode,
    CGA.MAIL AS ConsigneeAddressMail,
    CGA.ADDRESSNATURE AS ConsigneeAddressCityName,
    CGA.PHONE AS ConsigneeAddressPhone,
    CGA.MOBILE AS ConsigneeAddressMobile,
    CGA.GSTIN AS ConsigneeAddressGstin,
    CGA.VATTINNUMBER AS ConsigneeAddressVATTINNumber,
    CGA.VATCSTNUMBER AS ConsigneeAddressVATCSTNumber,
    CGC.CONTACTID AS ConsigneeContactId,
    CGC.NAME AS ConsigneeContactName,
    CGC.FIRSTNAME AS ConsigneeContactFirstName,
    CH.CHARGEID AS ChargeId,
    CH.CHARGECODE AS ChargeCode,
    CH.CHARGENAME AS ChargeName,
    EEMP.EMPLOYEEID AS BookedByEmployeeId,
    EEMP.EMPLOYEECODE AS BookedByEmployeeCode,
    EEMP.EMPLOYEENAME AS BookedByEmployeeName,
    BCONTACT.CONTACTID AS BookedByContactId,
    BCONTACT.NAME AS BookedByContactName,
    DEMP.EMPLOYEEID AS DeliveredByEmployeeId,
    DEMP.EMPLOYEECODE AS DeliveredByEmployeeCode,
    DEMP.EMPLOYEENAME AS DeliveredByEmployeeName,
    DCONTACT.CONTACTID AS DeliveredByContactId,
    DCONTACT.NAME AS DeliveredByContactName,
    GT.GCMID AS GoodsTypeId,
    GT.GCMCODE AS GoodsTypeCode,
    GT.GCMNAME AS GoodsTypeName,
    GC.GCMID AS GoodsContentId,
    GC.GCMCODE AS GoodsContentCode,
    GC.GCMNAME AS GoodsContentName,
    B.STOREID AS StoreId,
    S.STORECODE AS StoreCode,
    S.STORENAME AS StoreName,
    VEH.GCMID AS VehicleTypeId,
    VEH.GCMCODE AS VehicleTypeCode,
    VEH.GCMNAME AS VehicleTypeName,
    TPB.PARTYBRANCHID AS ThroughPartyBranchId,
    TPB.PARTYBRANCHCODE AS ThroughPartyBranchCode,
    TPB.PARTYBRANCHNAME AS ThroughPartyBranchName,
    CCOPY.GCMID AS ConsigneeCopyId,
    CCOPY.GCMCODE AS ConsigneeCopyCode,
    CCOPY.GCMNAME AS ConsigneeCopyName,
    FL.LOCATIONID AS FromLocationId,
    FL.LOCATIONCODE AS FromLocationCode,
    FL.LOCATIONNAME AS FromLocationName,
    TL.LOCATIONID AS ToLocationId,
    TL.LOCATIONCODE AS ToLocationCode,
    TL.LOCATIONNAME AS ToLocationName,

    -- ==================== BookingDetailDTO Fields ====================
    BD.BOOKINGDETAILID AS BookingDetailId,
    BD.SLNO AS BookingDetailSlNo,
    BD.INVOICENUMBER AS BookingDetailInvoiceNumber,
    BD.INVOICEDATE AS BookingDetailInvoiceDate,
    BD.REFERENCENUMBER AS BookingDetailReferenceNumber,
    BD.PARTNUMBER AS BookingDetailPartNumber,
    BD.DESCRIPTION AS BookingDetailDiscription,
    BD.QUANTITY AS BookingDetailQuantity,
    BD.INVOICEVALUE AS BookingDetailInvoiceValue,
    BD.CINVOICENUMBER AS BookingDetailCInvoiceNumber,
    BD.DELIVEREDQUANTITY AS DeliveredQuantity,
    BD.LOADEDQUANTITY AS LoadedQuantity,
    BD.CANCELEDQUANTITY AS CanceledQuantity,
    BD.EWAYBILLNO AS BookingDetailEwayBillNo,
    BD.CUSTOMERCONTRACTID AS CustomerContractId,
    BD.CUSTOMERCONTRACTSUBTYPEID AS CustomerContractSubTypeId,
    BD.TMSITEMRATE AS BookingDetailTMSItemRate,
    BD.TMSITEMVALUE AS BookingDetailTMSItemValue,
    BD.ITEMID AS ItemId,
    BD.SKUID AS SKUId,
    BD.UOMID AS UOMId,
    B.STOREID AS StoreId,
    MRE.REASONCODE AS ReasonCode,
    MRE.REASONNAME AS ReasonName,
    UOM.UOMCODE AS UOMCode,
    UOM.UOMNAME AS UOMName,
    PART.PARTYCODE AS CustomerContractCode,
    PART.PARTYNAME AS CustomerContractName,
    PARTB.PARTYBRANCHNAME AS CustomerContractSubTypeName,
    TEM1.ITEMCODE AS ItemCode,
    TEM1.ITEMNAME AS ItemName,
    SKU1.SKUCODE AS SKUCode,
    SKU1.SKUNAME AS SKUName,
    -- ==================== BookingChargesDTO Fields ====================
    BC.BOOKINGCHARGESID AS BookingChargesId,
    BC.SLNO AS BookingChargesSlNo,
    BC.INPUTVALUE AS BookingChargesInputValue,
    BC.INPUTTYPE AS BookingChargesInputType,
    BC.CALCULATEDON AS BookingChargesCalculatedon,
    BC.CALCULATEDVALUE AS BookingChargesCalculatedValue,
    BC.AMOUNTPLUS AS BookingChargesPlusAmount,
    BC.AMOUNTMINUS AS BookingChargesMinusAmount,
    BC.FCCALCULATEDVALUE AS BookingChargesFCCalculatedValue,
    BC.CURRENCYCONVERSION AS BookingChargesCurrencyConversion,
    BC.REMARKS AS BookingChargesRemarks,
    BC.OBJECTHEADERID AS ObjectHeaderId,
    BC.OBJECTID AS ObjectId,
    BC.FORMULAID AS FormulaId,
    F.FORMULACODE AS FormulaCode,
    F.FORMULANAME AS FormulaName,
    F.PRINTINGNAME AS FormulaPrintingName,
    BC.CHARGEDETAILID AS ChargeDetailId,
    CD.FORMULAID AS ChargeDetailFormulaId,
    F.FORMULACODE AS ChargeDetailFormulaCode,
    F.FORMULANAME AS ChargeDetailFormulaName,
    F.EXPRESSION AS ChargeDetailFormulaExpression,
    F.PRINTINGNAME AS ChargeDetailFormulaPrintingName,
    CD.CHARGENATURE AS ChargeDetailChargeNature,
    CD.OPERATIONTYPE AS ChargeDetailOperationType,
    CD.PARTYACCOUNTTYPE AS ChargeDetailPartyAccountType,
    CD.PARTYACCOUNTID AS ChargeDetailPartyAccountId,
    PA.ACCOUNTCODE AS PartyAccountCode,
    PA.ACCOUNTNAME AS PartyAccountName,
    CD.PARTYACCOUNTTYPE AS ChargeDetailPartyAccountBillAllocationType,
    CD.CONTRAACCOUNTTYPE AS ChargeDetailContraAccountType,
    CD.CONTRAACCOUNTID AS ChargeDetailContraAccountId,
    CA2.ACCOUNTCODE AS ContraAccountCode,
    CA2.ACCOUNTNAME AS ContraAccountName,
    CD.CONTRAACCOUNTTYPE AS ChargeDetailContraAccountBillAllocationType,
    CD.INPUTTYPE AS ChargeDetailInputType,
    CD.DEFAULTMINIMUMVALUE AS ChargeDetailDefaultMinimumValue,
    CD.DEFAULTMAXIMUMVALUE AS ChargeDetailDefaultMaximumValue,
    CD.INCLUDEINCOST AS ChargeDetailIncludeInCost,
    CD.INCLUDEINREALISATION AS ChargeDetailIncludeInRealisation,
    CD.DEPENDSONCHARGEELEMENT AS ChargeDetailDependsOnChargeElementId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName
    
FROM TBOOKING B
LEFT JOIN TBOOKINGDETAIL BD ON BD.BOOKINGID = B.BOOKINGID
LEFT JOIN TBOOKINGCHARGES BC ON BC.BOOKINGID = B.BOOKINGID
-- BookingDTO joins
LEFT JOIN MBIZTRANSACTIONTYPE BT ON BT.BIZTRANSACTIONTYPEID = B.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = B.OUID
LEFT JOIN MPERIOD P ON P.PERIODID = B.PERIODID
LEFT JOIN MCITY FC ON FC.CITYID = B.FROMCITYID
LEFT JOIN MCITY TC ON TC.CITYID = B.TOCITYID
LEFT JOIN MPARTYBRANCH C ON C.PARTYBRANCHID = B.CONSIGNORID
LEFT JOIN MADDRESS CA ON CA.ADDRESSID = B.CONSIGNORADDRESSID
LEFT JOIN MCONTACT CC ON CC.CONTACTID = B.CONSIGNORCONTACTID
LEFT JOIN MPARTYBRANCH CG ON CG.PARTYBRANCHID = B.CONSIGNEEID
LEFT JOIN MADDRESS CGA ON CGA.ADDRESSID = B.CONSIGNEEADDRESSID
LEFT JOIN MCONTACT CGC ON CGC.CONTACTID = B.CONSIGNEECONTACTID
LEFT JOIN MCHARGE CH ON CH.CHARGEID = B.CHARGEID
LEFT JOIN MEMPLOYEE EEMP ON EEMP.EMPLOYEEID = B.BOOKEDBYEMPLOYEEID
LEFT JOIN MCONTACT BCONTACT ON BCONTACT.CONTACTID = B.BOOKEDBYCONTACTID
LEFT JOIN MEMPLOYEE DEMP ON DEMP.EMPLOYEEID = B.DELIVEREDBYEMPLOYEEID
LEFT JOIN MCONTACT DCONTACT ON DCONTACT.CONTACTID = B.DELIVEREDBYCONTACTID
LEFT JOIN MGCM GT ON GT.GCMID = B.GOODSTYPEID
LEFT JOIN MGCM GC ON GC.GCMID = B.GOODSCONTENTID
LEFT JOIN MSTORE S ON S.STOREID = B.STOREID
LEFT JOIN MGCM VEH ON VEH.GCMID = B.VEHICLETYPEID
LEFT JOIN MPARTYBRANCH TPB ON TPB.PARTYBRANCHID = B.THROUGHPARTYBRANCHID
LEFT JOIN MGCM CCOPY ON CCOPY.GCMID = B.CONSIGNEECOPYID
LEFT JOIN MLOCATION FL ON FL.LOCATIONID = B.FROMLOCATIONID
LEFT JOIN MLOCATION TL ON TL.LOCATIONID = B.TOLOCATIONID
LEFT JOIN MPAYMENTTERM MPT ON MPT.PAYMENTTERMID = B.PAYMENTTYPEID
LEFT JOIN MGCM M2 ON M2.GCMID = B.BOOKINGID
LEFT JOIN MREASON MRE ON MRE.REASONID = B.REASONID
LEFT JOIN MITEM TEM ON TEM.ITEMID = B.ITEMID
LEFT JOIN MUOM UOM ON UOM.UOMID = B.SKUID
LEFT JOIN MSKU SKU ON SKU.SKUID = B.SKUID
LEFT JOIN MPARTY PART ON PART.PARTYID = B.SKUID
LEFT JOIN MPARTYBRANCH PARTB ON PARTB.PARTYBRANCHID = B.SKUID
LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = BC.CURRENCYID
LEFT JOIN MORGANIZATIONUNIT MOU1 ON MOU1.OUID = B.UNLOADOUID
LEFT JOIN MSTORE MST1 ON MST1.STOREID = B.UNLOADSTOREID
-- BookingCharges joins
LEFT JOIN MFORMULA F ON F.FORMULAID = BC.FORMULAID
LEFT JOIN MCHARGEDETAIL CD ON CD.CHARGEDETAILID = BC.CHARGEDETAILID
LEFT JOIN MACCOUNT PA ON PA.ACCOUNTID = CD.PARTYACCOUNTID
LEFT JOIN MACCOUNT CA2 ON CA2.ACCOUNTID = CD.CONTRAACCOUNTID
LEFT JOIN MITEM TEM1 ON TEM1.ITEMID = B.ITEMID
LEFT JOIN MSKU SKU1 ON SKU1.SKUID = B.SKUID
WHERE
B.BOOKINGID = @bookingid

;";

    }
}