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

namespace AdminDAL.Query.FinanceBook
{
    public static class FinanceBookQB
    {
        #region GET_FINANCEBOOK
        public const string GET_FINANCEBOOK = @"
SELECT
    F.FINANCEBOOKID                 AS FinanceBookId,
    F.FINANCEBOOKNAME               AS FinanceBookName,
    F.PURPOSE                       AS FinanceBookPurpose,
    F.BOOKTYPE                      AS FinanceBookBookType,
    F.BASEBOOKID                    AS BaseBookId,
    BB.FINANCEBOOKNAME              AS BaseBookName,
    F.PERIODGROUP                   AS FinanceBookPeriodGroup,

    F.OUID                          AS OuId,
    OU.ORGANIZATIONUNITCODE         AS OuCode,      
    OU.ORGANIZATIONUNITNAME         AS OuName,      

    F.FROMCURRENCYID                AS FromCurrencyId,
    FC.CURRENCYCODE                 AS FromCurrencyCode,
    FC.CURRENCYNAME                 AS FromCurrencyName,

    F.TOCURRENCYID                  AS ToCurrencyId,
    TC.CURRENCYCODE                 AS ToCurrencyCode,
    TC.CURRENCYNAME                 AS ToCurrencyName,

    F.TRANSFERLEVEL                 AS FinanceBookTransferLevel,
    F.BALANCESHEETRATETYPE          AS FinanceBookBalanceSheetRateType,
    F.PANDLRATETYPE                 AS FinanceBookPanDLRateType,
    F.EQUITYRATETYPE                AS FinanceBookEquityRateType,

    F.BIZTRANSACTIONID              AS BIZTransactionId,
    BT.BIZTRANSACTIONCODE           AS BIZTransactionCode,  
    BT.BIZTRANSACTIONNAME           AS BIZTransactionName,  

    F.DIFFERENCEACCOUNTID           AS DifferenceAccountId,
    AC.ACCOUNTCODE                  AS DifferenceAccountCode,
    AC.ACCOUNTNAME                  AS DifferenceAccountName,

    F.STANDARDRATE                  AS FinanceBookStandardRate,
    F.BOOKIDS                       AS FinanceBookBookIds,
    F.SORTORDER                     AS FinanceBookSortOrder,
    F.STATUS                        AS FinanceBookStatus,
    F.VERSION                       AS FinanceBookVersion,
    F.SOURCETYPE                    AS FinanceBookSourceType,

    F.CREATEDBYID                   AS FinanceBookCreatedById,
    UC.USERNAME                     AS FinanceBookCreatedByName,
    F.CREATEDON                     AS FinanceBookCreatedOn,

    F.MODIFIEDBYID                  AS FinanceBookModifiedById,
    UM.USERNAME                     AS FinanceBookModifiedByName,
    F.MODIFIEDON                    AS FinanceBookModifiedOn,

    F.OUIDS                         AS FinanceBookOUIds
FROM MFINANCEBOOK F
LEFT JOIN MFINANCEBOOK BB ON F.BASEBOOKID = BB.FINANCEBOOKID
LEFT JOIN MORGANIZATIONUNIT OU ON F.OUID = OU.OUID
LEFT JOIN MCURRENCY FC ON F.FROMCURRENCYID = FC.CURRENCYID
LEFT JOIN MCURRENCY TC ON F.TOCURRENCYID = TC.CURRENCYID
LEFT JOIN MBIZTRANSACTION BT ON F.BIZTRANSACTIONID = BT.BIZTRANSACTIONID
LEFT JOIN MACCOUNT AC ON F.DIFFERENCEACCOUNTID = AC.ACCOUNTID
LEFT JOIN MUSER UC ON F.CREATEDBYID = UC.USERID
LEFT JOIN MUSER UM ON F.MODIFIEDBYID = UM.USERID
WHERE F.FINANCEBOOKID = @FinanceBookId;";



        #endregion

        #region INSERT
        public const string SAVE_FINANCEBOOK = @"
INSERT INTO MFINANCEBOOK (
    FINANCEBOOKID,
    FINANCEBOOKNAME,
    PURPOSE,
    BOOKTYPE,
    BASEBOOKID,
    PERIODGROUP,
    OUID,
    FROMCURRENCYID,
    TOCURRENCYID,
    TRANSFERLEVEL,
    BALANCESHEETRATETYPE,
    PANDLRATETYPE,
    EQUITYRATETYPE,
    BIZTRANSACTIONID,
    DIFFERENCEACCOUNTID,
    STANDARDRATE,
    BOOKIDS,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    OUIDS
)
VALUES (
    @FinanceBookId,
    @FinanceBookName,
    @FinanceBookPurpose,
    @FinanceBookBookType,
    @BaseBookId,
    @FinanceBookPeriodGroup,
    @OuId,
    @FromCurrencyId,
    @ToCurrencyId,
    @FinanceBookTransferLevel,
    @FinanceBookBalanceSheetRateType,
    @FinanceBookPanDLRateType,
    @FinanceBookEquityRateType,
    @BIZTransactionId,
    @DifferenceAccountId,
    @FinanceBookStandardRate,
    @FinanceBookBookIds,
    @FinanceBookSortOrder,
    @FinanceBookStatus,
    @FinanceBookVersion,
    @FinanceBookSourceType,
    @FinanceBookCreatedById,
    @FinanceBookCreatedOn,
    @FinanceBookModifiedById,
    @FinanceBookModifiedOn,
    @FinanceBookOUIds
);
";
        #endregion

        #region UPDATE
        public const string UPDATE_FINANCEBOOK = @"
UPDATE MFINANCEBOOK
SET
    FINANCEBOOKNAME           = @FinanceBookName,
    PURPOSE                   = @FinanceBookPurpose,
    BOOKTYPE                  = @FinanceBookBookType,
    BASEBOOKID                = @BaseBookId,
    PERIODGROUP               = @FinanceBookPeriodGroup,
    OUID                      = @OuId,
    FROMCURRENCYID            = @FromCurrencyId,
    TOCURRENCYID              = @ToCurrencyId,
    TRANSFERLEVEL             = @FinanceBookTransferLevel,
    BALANCESHEETRATETYPE      = @FinanceBookBalanceSheetRateType,
    PANDLRATETYPE             = @FinanceBookPanDLRateType,
    EQUITYRATETYPE            = @FinanceBookEquityRateType,
    BIZTRANSACTIONID          = @BIZTransactionId,
    DIFFERENCEACCOUNTID       = @DifferenceAccountId,
    STANDARDRATE              = @FinanceBookStandardRate,
    BOOKIDS                   = @FinanceBookBookIds,
    SORTORDER                 = @FinanceBookSortOrder,
    STATUS                    = @FinanceBookStatus,
    VERSION                   = @FinanceBookVersion,
    SOURCETYPE                = @FinanceBookSourceType,
    MODIFIEDBYID              = @FinanceBookModifiedById,
    MODIFIEDON                = @FinanceBookModifiedOn,
    OUIDS                     = @FinanceBookOUIds
WHERE FINANCEBOOKID = @FinanceBookId;
";
        #endregion

        #region DELETE
        public const string DELETE_FINANCEBOOK = @"
DELETE FROM MFINANCEBOOK WHERE FINANCEBOOKID = @FinanceBookId;
";
        #endregion

        #region GET_SELECTLIST
        public const string GET_SELECTLIST_FINANCEBOOK = @"
SELECT
    F.FINANCEBOOKID     AS Id,
    F.FINANCEBOOKNAME   AS Name
FROM MFINANCEBOOK F;
";
        #endregion
    }
}