using FrameworkDAL.DTO.Checking;
using FrameworkDAL.Query.BIZTransactionClass;
using FrameworkDAL.Query.Checking;
using Google.Rpc;
using Microsoft.Data.SqlClient;
using Org.BouncyCastle.Asn1.Ocsp;
using Org.BouncyCastle.Asn1.X509;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.CheckingOption
{
    public static class CheckingOptionQB
    {
        public const string GET_CHECKINGOPTION = @"
        
SELECT 

-- Parent: CheckingOptionDTO


-- Checking
CO.CHECKINGOPTIONID AS CheckingOptionId,
CO.CHECKINGOPTIONTYPE AS CheckingOptionType,

-- Entity
CO.ENTITYID AS EntityId,
E.ENTITYCODE AS EntityCode,
E.ENTITYNAME AS EntityName,

-- BIZTransactionClass
CO.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
BTC.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
BTC.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,

-- Base
CO.CREATEDBYID AS CheckingOptionCreatedById,
UC.USERNAME AS CheckingOptionCreatedByName,
CO.CREATEDON AS CheckingOptionCreatedOn,
CO.MODIFIEDBYID AS CheckingOptionModifiedById,
UM.USERNAME AS CheckingOptionModifiedByName,
CO.MODIFIEDON AS CheckingOptionModifiedOn,

-- Standard
CO.SORTORDER AS CheckingOptionSortOrder,
CO.STATUS AS CheckingOptionStatus,
CO.VERSION AS CheckingOptionVersion,
CO.SOURCETYPE AS CheckingOptionSourceType,

-- Child: CheckingOptionDetailDTO

-- CheckingOptionDetail
COD.CHECKINGOPTIONDETAILID AS CheckingOptionDetailId,
COD.CHECKINGOPTIONID AS CheckingOptionId,
COD.SLNO AS CheckingOptionDetailSlNo,

-- Checking
COD.CHECKINGID AS CheckingId,
CH.CHECKINGCODE AS CheckingCode,
CH.CHECKINGNAME AS CheckingName,

-- Other
COD.DISPLAYTEXT AS CheckingOptionDetailDisplayText

FROM MCHECKINGOPTION CO

-- Join Entity
LEFT JOIN MENTITY E ON CO.ENTITYID = E.ENTITYID

-- Join BIZTransactionClass
LEFT JOIN MBIZTRANSACTIONCLASS BTC ON CO.BIZTRANSACTIONCLASSID = BTC.BIZTRANSACTIONCLASSID

-- Join Users
LEFT JOIN MUSER UC ON CO.CREATEDBYID = UC.USERID
LEFT JOIN MUSER UM ON CO.MODIFIEDBYID = UM.USERID

-- Join Child: CheckingOptionDetail
LEFT JOIN MCHECKINGOPTIONDETAIL COD ON CO.CHECKINGOPTIONID = COD.CHECKINGOPTIONID

-- Join Checking
LEFT JOIN MCHECKING CH ON COD.CHECKINGID = CH.CHECKINGID

WHERE CO.CHECKINGOPTIONID = @checkingoptionid;
";



        public const string SAVE_CHECKINGOPTION = @"INSERT INTO MCHECKINGOPTION
(
    CHECKINGOPTIONID,
    CHECKINGOPTIONTYPE,
    ENTITYID,
    BIZTRANSACTIONCLASSID,
    SORTORDER,
    VERSION,
    STATUS,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @CheckingOptionId,
    @CheckingOptionType,
    @EntityId,
    @BIZTransactionClassId,
    @CheckingOptionSortOrder,
    @CheckingOptionVersion,
    @CheckingOptionStatus,
    @CheckingOptionSourceType,
    @CheckingOptionCreatedById,
    @CheckingOptionCreatedOn,
    @CheckingOptionModifiedById,
    @CheckingOptionModifiedOn
);
";

        public const string UPDATE_CHECKINGOPTION = @"UPDATE MCHECKINGOPTION
SET
    CHECKINGOPTIONTYPE = @CheckingOptionType,
    ENTITYID = @EntityId,
    BIZTRANSACTIONCLASSID = @BIZTransactionClassId,
    SORTORDER = @CheckingOptionSortOrder,
    VERSION = @CheckingOptionVersion,
    STATUS = @CheckingOptionStatus,
    SOURCETYPE = @CheckingOptionSourceType,
    MODIFIEDBYID = @CheckingOptionModifiedById,
    MODIFIEDON = @CheckingOptionModifiedOn
WHERE
    CHECKINGOPTIONID = @CheckingOptionId;";

        public const string DELETE_CHECKINGOPTION = @"DELETE FROM MCHECKINGOPTION
                                            WHERE CHECKINGOPTIONID = @CheckingOptionId;";

    }
}