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

namespace FrameworkDAL.Query.Party
{
    public static class PartyQB
    {
        public const string GET_PARTY = @"
SELECT
    p.PARTYID                    AS PartyId,
    p.PARTYCODE                  AS PartyCode,
    p.PARTYNAME                  AS PartyName,
    p.PARTYTYPE                  AS PartyType,

    p.CODEDEFINEID               AS PartyCodeDefineId,
    p.PARTYFAVOURINGNAME         AS PartyFavouringName,
    p.PARTYSHORTNAME             AS PartyShortName,
    p.PARTYCOMBINEID             AS PartyCombineId,

    p.ISVENDOR                   AS PartyIsVendor,
    p.ISSERVICEPROVIDER          AS PartyIsServiceProvider,
    p.ISACCOUNT                  AS PartyIsAccount,
    p.ISCUSTOMER                 AS PartyIsCustomer,
    p.ISPROSPECT                 AS PartyIsProspect,
    p.ISBANK                     AS PartyIsBank,

    p.CONTROLACCOUNTID           AS PartyControlAccountId,
    p.ACCOUNTLINKID              AS PartyAccountLinkId,

    p.ACCOUNTGROUPID             AS PartyAccountGroupId,
    p.CURRENCYID                 AS PartyCurrencyId,
    p.DEFAULTADDRESSID           AS PartyDefaultAddressId,

    p.THUMBNAIL                  AS PartyThumbnail,

    p.SORTORDER                  AS PartySortOrder,
    p.STATUS                     AS PartyStatus,
    p.VERSION                    AS PartyVersion,

    p.CREATEDBYID                AS PartyCreatedById,
    cb.USERNAME                  AS PartyCreatedByName,
    p.CREATEDON                  AS PartyCreatedOn,

    p.MODIFIEDBYID               AS PartyModifiedById,
    mb.USERNAME                  AS PartyModifiedByName,
    p.MODIFIEDON                 AS PartyModifiedOn,

    p.SOURCETYPE                 AS PartySourceType,
    p.TENANTID                   AS PartyTenantId

FROM dbo.MPARTY p

LEFT JOIN dbo.MUSER cb
       ON p.CREATEDBYID = cb.USERID

LEFT JOIN dbo.MUSER mb
       ON p.MODIFIEDBYID = mb.USERID

WHERE p.PARTYID = @PartyId;
";

        public const string SAVE_PARTY = @"
INSERT INTO MPARTY
(
    PARTYCODE,
    PARTYNAME,
    PARTYTYPE,
    CODEDEFINEID,
    PARTYFAVOURINGNAME,
    PARTYSHORTNAME,
    PARTYCOMBINEID,
    ISVENDOR,
    ISSERVICEPROVIDER,
    ISACCOUNT,
    ISCUSTOMER,
    ISPROSPECT,
    ISBANK,
    CONTROLACCOUNTID,
    ACCOUNTLINKID,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    ACCOUNTGROUPID,
    CURRENCYID,
    DEFAULTADDRESSID,
    SOURCETYPE,
    TENANTID,
    THUMBNAIL
)
VALUES
(
    @PartyCode,
    @PartyName,
    @PartyType,
    @PartyCodeDefineId,
    @PartyFavouringName,
    @PartyShortName,
    @PartyCombineId,
    @PartyIsVendor,
    @PartyIsServiceProvider,
    @PartyIsAccount,
    @PartyIsCustomer,
    @PartyIsProspect,
    @PartyIsBank,
    @PartyControlAccountId,
    @PartyAccountLinkId,
    @PartySortOrder,
    @PartyStatus,
    @PartyVersion,
    @PartyCreatedById,
    @PartyCreatedOn,
    @PartyModifiedById,
    @PartyModifiedOn,
    @PartyAccountGroupId,
    @PartyCurrencyId,
    @PartyDefaultAddressId,
    @PartySourceType,
    @PartyTenantId,
    @PartyThumbnail
);";

        public const string UPDATE_PARTY = @"
UPDATE MPARTY
SET
    PARTYCODE = @PartyCode,
    PARTYNAME = @PartyName,
    PARTYTYPE = @PartyType,
    CODEDEFINEID = @PartyCodeDefineId,
    PARTYFAVOURINGNAME = @PartyFavouringName,
    PARTYSHORTNAME = @PartyShortName,
    PARTYCOMBINEID = @PartyCombineId,
    ISVENDOR = @PartyIsVendor,
    ISSERVICEPROVIDER = @PartyIsServiceProvider,
    ISACCOUNT = @PartyIsAccount,
    ISCUSTOMER = @PartyIsCustomer,
    ISPROSPECT = @PartyIsProspect,
    ISBANK = @PartyIsBank,
    CONTROLACCOUNTID = @PartyControlAccountId,
    ACCOUNTLINKID = @PartyAccountLinkId,
    ACCOUNTGROUPID = @PartyAccountGroupId,
    CURRENCYID = @PartyCurrencyId,
    DEFAULTADDRESSID = @PartyDefaultAddressId,
    THUMBNAIL = @PartyThumbnail,
    SORTORDER = @PartySortOrder,
    STATUS = @PartyStatus,
    VERSION = @PartyVersion,
    MODIFIEDBYID = @PartyModifiedById,
    MODIFIEDON = @PartyModifiedOn,
    SOURCETYPE = @PartySourceType
WHERE
    PARTYID = @PartyId;";

        public const string DELETE_PARTY = @"
DELETE FROM MPARTY
WHERE PARTYID = @PartyId;";

        public const string GET_SELECTLIST_PARTY = @"
WITH Party AS
(
    SELECT
        p.PARTYID                    AS PartyId,
        p.PARTYCODE                  AS PartyCode,
        p.PARTYNAME                  AS PartyName,
        p.PARTYTYPE                  AS PartyType,

        p.CODEDEFINEID               AS PartyCodeDefineId,
        p.PARTYFAVOURINGNAME         AS PartyFavouringName,
        p.PARTYSHORTNAME             AS PartyShortName,
        p.PARTYCOMBINEID             AS PartyCombineId,

        p.ISVENDOR                   AS PartyIsVendor,
        p.ISSERVICEPROVIDER          AS PartyIsServiceProvider,
        p.ISACCOUNT                  AS PartyIsAccount,
        p.ISCUSTOMER                 AS PartyIsCustomer,
        p.ISPROSPECT                 AS PartyIsProspect,
        p.ISBANK                     AS PartyIsBank,

        p.CONTROLACCOUNTID           AS PartyControlAccountId,
        p.ACCOUNTLINKID              AS PartyAccountLinkId,

        p.ACCOUNTGROUPID             AS PartyAccountGroupId,
        p.CURRENCYID                 AS PartyCurrencyId,
        p.DEFAULTADDRESSID           AS PartyDefaultAddressId,

        p.THUMBNAIL                  AS PartyThumbnail,

        p.SORTORDER                  AS PartySortOrder,
        p.STATUS                     AS PartyStatus,
        p.VERSION                    AS PartyVersion,

        p.CREATEDBYID                AS PartyCreatedById,
        cb.USERNAME                  AS PartyCreatedByName,
        p.CREATEDON                  AS PartyCreatedOn,

        p.MODIFIEDBYID               AS PartyModifiedById,
        mb.USERNAME                  AS PartyModifiedByName,
        p.MODIFIEDON                 AS PartyModifiedOn,

        p.SOURCETYPE                 AS PartySourceType,
        p.TENANTID                   AS PartyTenantId,

        ROW_NUMBER() OVER (ORDER BY p.PARTYID) AS RowNum

    FROM dbo.MPARTY p

    LEFT JOIN dbo.MUSER cb
           ON p.CREATEDBYID = cb.USERID

    LEFT JOIN dbo.MUSER mb
           ON p.MODIFIEDBYID = mb.USERID
)

SELECT *
FROM Party
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_PARTY_LIST = @"
SELECT
    p.PARTYID                    AS PartyId,
    p.PARTYCODE                  AS PartyCode,
    p.PARTYNAME                  AS PartyName,
    p.PARTYTYPE                  AS PartyType,

    p.ISVENDOR                   AS PartyIsVendor,
    p.ISSERVICEPROVIDER          AS PartyIsServiceProvider,
    p.ISACCOUNT                  AS PartyIsAccount,
    p.ISCUSTOMER                 AS PartyIsCustomer,
    p.ISPROSPECT                 AS PartyIsProspect,
    p.ISBANK                     AS PartyIsBank,

    p.ACCOUNTGROUPID             AS PartyAccountGroupId,
    p.CURRENCYID                 AS PartyCurrencyId,
    p.DEFAULTADDRESSID           AS PartyDefaultAddressId,

    p.THUMBNAIL                  AS PartyThumbnail,

    p.SORTORDER                  AS PartySortOrder,
    p.STATUS                     AS PartyStatus,
    p.VERSION                    AS PartyVersion,

    p.CREATEDBYID                AS PartyCreatedById,
    p.CREATEDON                  AS PartyCreatedOn,

    p.MODIFIEDBYID               AS PartyModifiedById,
    p.MODIFIEDON                 AS PartyModifiedOn,

    p.SOURCETYPE                 AS PartySourceType,
    p.TENANTID                   AS PartyTenantId

FROM dbo.MPARTY p;";
    }
}