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

namespace AccountsDAL.Query.PartyDetail
{
    public static class PartyDetailQB
    {
        public const string GET_PARTY_DETAILS_INVENTORY = @"
                  
             SELECT DISTINCT
                    PB.PARTYBRANCHID AS PartyBranchId,
                    ISNULL(PB.PARTYBRANCHCODE,'NONE') AS PartyBranchCode,
                    ISNULL(PB.PARTYBRANCHNAME,'NONE') AS PartyBranchName,

                    P.PARTYID AS PartyBranchPartyId,
                    ISNULL(P.PARTYCODE,'NONE') AS PartyBranchPartyCode,
                    ISNULL(P.PARTYNAME,'NONE') AS PartyBranchPartyName,

                    ISNULL(PB.PARTYBRANCHSHORTNAME,'NONE') AS PartyBranchShortName,

                    ISNULL(PB.CURRENCYID,-1) AS PartyBranchCurrencyId,
                    ISNULL(C.CURRENCYCODE,'NONE') AS PartyBranchCurrencyCode,
                    ISNULL(C.CURRENCYNAME,'NONE') AS PartyBranchCurrencyName,
                    ISNULL(C.CURRENCYDECIMAL,1) AS PartyBranchCurrencyDecimal,

                    ISNULL(PAO.CREDITALLOWED,1) AS CreditAllowed,
                    ISNULL(PAO.DEBITALLOWED,1) AS DebitAllowed,
                    ISNULL(PAO.CREDITLIMIT,-1) AS CreditLimit,

                    ISNULL(PAO.TAXTYPEID,-1500000000) AS TaxTypeId,
                    ISNULL(TT.PARTYTAXTYPENAME,'NONE') AS TaxTypeName,

                    ISNULL(PAO.SALESPAYMENTTERMID,-1) AS SalesPaymentTermId,
                    ISNULL(SPT.PAYMENTTERMNAME,'NONE') AS SalesPaymentTermName,

                    ISNULL(PAO.PURCHASEPAYMENTTERMID,-1) AS PurchasePaymentTermId,
                    ISNULL(PPT.PAYMENTTERMNAME,'NONE') AS PurchasePaymentTermName,

                    ISNULL(PAO.SALESINCHARGEID,-1) AS SalesInChargeId,
                    ISNULL(SI.EMPLOYEENAME,'NONE') AS SalesInChargeName,

                    ISNULL(PAO.INCHARGEID,-1) AS PurchaseInChargeId,
                    ISNULL(PI.EMPLOYEENAME,'NONE') AS PurchaseInChargeName,

                    ISNULL(PAO.ROUTEID,-1) AS RouteId,
                    ISNULL(R.ROUTENAME,'NONE') AS RouteName,

                    ISNULL(PB.DEFAULTADDRESSID,-1) AS PartyBranchAddressId,

                    ISNULL(AD.GSTIN,'') AS GSTNumber,

                    ISNULL(AG.PARTYID,-1) AS SalesAgentId,
                    ISNULL(AC.ACCOUNTNAME,'NONE') AS SalesAgentName,
                    ISNULL(AC.ACCOUNTTYPE,1) AS SalesAgentAccountType

                FROM MPARTYBRANCH PB

                INNER JOIN MPARTY P
                    ON P.PARTYID = PB.PARTYID

                OUTER APPLY (
                    SELECT TOP 1 *
                    FROM MPARTYACCOUNTOU PAO
                    WHERE PAO.PARTYBRANCHID = PB.PARTYBRANCHID
                      AND PAO.OUID = @WorkOUId
                      AND PAO.STATUS = 1
                ) PAO

                LEFT JOIN MCURRENCY C
                    ON C.CURRENCYID = PB.CURRENCYID

                LEFT JOIN MPARTYTAXTYPE TT
                    ON TT.PARTYTAXTYPEID = PAO.TAXTYPEID

                LEFT JOIN MPAYMENTTERM PPT
                    ON PPT.PAYMENTTERMID = PAO.PURCHASEPAYMENTTERMID

                LEFT JOIN MPAYMENTTERM SPT
                    ON SPT.PAYMENTTERMID = PAO.SALESPAYMENTTERMID

                LEFT JOIN MEMPLOYEE PI
                    ON PI.EMPLOYEEID = PAO.INCHARGEID

                LEFT JOIN MEMPLOYEE SI
                    ON SI.EMPLOYEEID = PAO.SALESINCHARGEID

                LEFT JOIN MROUTE R
                    ON R.ROUTEID = PAO.ROUTEID

                LEFT JOIN MADDRESS AD
                    ON AD.ADDRESSID = PB.DEFAULTADDRESSID

                LEFT JOIN MPARTY AG
                    ON AG.PARTYID = PB.AGENTID

                LEFT JOIN MACCOUNT AC
                    ON AC.ACCOUNTID = AG.ACCOUNTLINKID

                WHERE PB.PARTYBRANCHID = @PartyBranchId;";

                 //  OU Currency Query
                 public const string GET_OU_CURRENCY = @"
                         SELECT CurrencyId
                         FROM MORGANIZATIONUNIT
                         WHERE OUID = @WorkOUId";
             //   GST Query
                 public const string GET_GST_NUMBER = @"
                 SELECT GSTIN
                 FROM MADDRESS
                 WHERE OBJECTTYPEID = -1899999839
                 AND OBJECTID = @PartyBranchId";
             //Closing Balance Query
             public const string GET_CLOSING_BALANCE = @"
             SELECT TOP 1
                 CLOSING AS PartyClosingBalance
             FROM TACCOUNTPOST
             WHERE ACCOUNTID = @AccountId";
              // Account Details Query
                 public const string GET_ACCOUNT_DETAILS = @"
                 SELECT *
                 FROM MACCOUNT
                 WHERE ACCOUNTID = @AccountId";
            // Party Tax Type Details
                 public const string GET_PARTY_TAXTYPEDETAILS = @"
                 SELECT
                     TRANSACTIONTYPEID,
                     DUTYTRANSACTIONTYPEID
                 FROM MPARTYTAXTYPEDETAIL
                 WHERE PARTYTAXTYPEID = @TaxTypeId";
        //Addon Queries
             public const string GET_PARTYBRANCH_ADDON_FIELDS = @"
                 SELECT
                     FIELDNAME AS DBObjectFieldsName
                 FROM MADDONFIELDS
                 WHERE ENTITYID = @EntityId";

        public const string GET_PARTYBRANCH_ADDON_BASED_ON_ID = @"
         
           SELECT * FROM MPARTYBRANCHADDON WHERE PARTYBRANCHID = @PartyBranchId";

        public const string GET_EXCHANGE_BY_CURRENCY = @"
                SELECT TOP 1
                    E.EXCHANGEID AS ExchangeId,
                    E.CONVERSIONRATE AS ExchangeConversionRate
                FROM MEXCHANGE E
                WHERE E.BASECURRENCYID = @BaseCurrencyId
                  AND E.CURRENCYID = @CurrencyId
                ORDER BY E.EXCHANGEDATE DESC";

        // Unique Phone — matches GB4 AddressContactSearchBLL.GetUniquePhone (MobileNo filter over MCONTACT)
        // MCONTACT has no tenant/client column (see CRMDAL.Query.Contact.ContactQB) — no other
        // query against this table scopes by tenant.
        public const string GET_UNIQUE_PHONE = @"
                SELECT
                    CT.MOBILENO AS ContactMobile
                FROM MCONTACT CT
                WHERE (@MobileNo IS NULL OR CT.MOBILENO = @MobileNo)";
    }
}