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

namespace MMDAL.Query.PaymentTerm
{
    public static class PaymentTermQB
    {
        public const string GET_PAYMENTTERM = @"SELECT
                                                pt.PAYMENTTERMID    AS PaymentTermId,
                                                pt.PAYMENTTERMCODE  AS PaymentTermCode,
                                                pt.PAYMENTTERMNAME  AS PaymentTermName,
                                                pt.REMARKS          AS PaymentTermRemarks,
                                                pt.CREATEDBYID      AS PaymentTermCreatedById,
                                                cr.USERNAME         AS PaymentTermCreatedByName, 
                                                pt.CREATEDON        AS PaymentTermCreatedOn,
                                                pt.MODIFIEDBYID     AS PaymentTermModifiedById,
                                                md.USERNAME         AS PaymentTermModifiedByName,
                                                pt.MODIFIEDON       AS PaymentTermModifiedOn,
                                                pt.STATUS           AS PaymentTermStatus,
                                                pt.VERSION          AS PaymentTermVersion,
                                                pt.SORTORDER        AS PaymentTermSortOrder,
                                                ptd.PAYMENTTERMDETAILID   AS PaymentTermDetailId,
                                                ptd.PAYMENTTERMID   AS PaymentTermId,
                                                pt.PAYMENTTERMCODE  AS PaymentTermCode,
                                                pt.PAYMENTTERMNAME  AS PaymentTermName,
                                                ptd.SLNO            AS PaymentTermDetailSlNo,
                                                ptd.PAYMENTTYPE     AS PaymentTermDetailPaymentType,
                                                ptd.NUMBEROFDAYS    AS PaymentTermDetailNumberOfDays,
                                                ptd.WEFDATETYPE     AS PaymentTermDetailWEFDateType,
                                                ptd.PAYMENTPERCENTAGE AS PaymentTermDetailPaymentPercentage,
                                                ptd.CASHDISCOUNTID  AS CashDiscountId,
                                                cd.CODE             AS CashDiscountCode,
                                                cd.NAME             AS CashDiscountName

                                            FROM MPAYMENTTERM pt
                                            LEFT JOIN MUSER cr ON pt.CREATEDBYID = cr.USERID
                                            LEFT JOIN MUSER md ON pt.MODIFIEDBYID = md.USERID
                                            LEFT JOIN MPAYMENTTERMDETAIL ptd ON pt.PAYMENTTERMID = ptd.PAYMENTTERMID
                                            LEFT JOIN MCASHDISCOUNT cd ON ptd.CASHDISCOUNTID = cd.CASHDISCOUNTID
                                            WHERE pt.PAYMENTTERMID = @paymenttermid
                                            ORDER BY pt.PAYMENTTERMID, ptd.SLNO;";


        public const string SAVE_PAYMENTTERM = @"INSERT INTO MPAYMENTTERM
                                                    (
                                                        PAYMENTTERMID,
                                                        PAYMENTTERMCODE,
                                                        PAYMENTTERMNAME,
                                                        REMARKS,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @PaymentTermId, 
                                                        @PaymentTermCode,
                                                        @PaymentTermName,
                                                        @PaymentTermRemarks,
                                                        @PaymentTermSortOrder,
                                                        @PaymentTermStatus,
                                                        @PaymentTermVersion,
                                                        @PaymentTermCreatedById,
                                                        @PaymentTermCreatedOn,
                                                        @PaymentTermModifiedById,
                                                        @PaymentTermModifiedOn
                                                    );";

        public const string SAVE_PAYMENTTERM_DETAIL = @"INSERT INTO MPAYMENTTERMDETAIL
                                                        (
                                                            PAYMENTTERMDETAILID,
                                                            PAYMENTTERMID,
                                                            SLNO,
                                                            PAYMENTTYPE,
                                                            NUMBEROFDAYS,
                                                            WEFDATETYPE,
                                                            PAYMENTPERCENTAGE,
                                                            CASHDISCOUNTID
                                                        )
                                                        VALUES
                                                        (
                                                            @PaymentTermDetailId,
                                                            @PaymentTermId,
                                                            @PaymentTermDetailSlNo,
                                                            @PaymentTermDetailPaymentType,
                                                            @PaymentTermDetailNumberOfDays,
                                                            @PaymentTermDetailWEFDateType,
                                                            @PaymentTermDetailPaymentPercentage,
                                                            @CashDiscountId
                                                        );";

        public const string UPDATE_PAYMENTTERM = @"UPDATE MPAYMENTTERM
                                                SET
                                                    PAYMENTTERMCODE = @PaymentTermCode,
                                                    PAYMENTTERMNAME = @PaymentTermName,
                                                    REMARKS = @PaymentTermRemarks,
                                                    SORTORDER = @PaymentTermSortOrder,
                                                    STATUS = @PaymentTermStatus,
                                                    VERSION = @PaymentTermVersion,
                                                    MODIFIEDBYID = @PaymentTermModifiedById,
                                                    MODIFIEDON = @PaymentTermModifiedOn
                                                WHERE
                                                    PAYMENTTERMID = @PaymentTermId;";

        public const string UPDATE_PAYMENTTERM_DETAIL= @"UPDATE MPAYMENTTERMDETAIL
                                                        SET
                                                            PAYMENTTERMID = @PaymentTermId,
                                                            SLNO = @PaymentTermDetailSlNo,
                                                            PAYMENTTYPE = @PaymentTermDetailPaymentType,
                                                            NUMBEROFDAYS = @PaymentTermDetailNumberOfDays,
                                                            WEFDATETYPE = @PaymentTermDetailWEFDateType,
                                                            PAYMENTPERCENTAGE = @PaymentTermDetailPaymentPercentage,
                                                            CASHDISCOUNTID = @CashDiscountId
                                                        WHERE PAYMENTTERMDETAILID = @PaymentTermDetailId;";

        public const string GET_SELECTLIST_PAYMENT_TERM = @"SELECT
                                                PT.PAYMENTTERMID            AS Id,
                                                PT.PAYMENTTERMCODE          AS Code,
                                                PT.PAYMENTTERMNAME          AS Name
                                            FROM MPAYMENTTERM PT;";

        public const string DELETE_PAYMENTTERM = @"DELETE FROM MPAYMENTTERM
                                                WHERE PAYMENTTERMID = @paymenttermid;";

        public const string DELETE_PAYMENTTERM_DETAIL = @"DELETE FROM MPAYMENTTERMDETAIL
                                                          WHERE PAYMENTTERMID = @paymenttermid;";

        public const string GET_PAYMENTTERM_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MPAYMENTTERM
                                                    WHERE
                                                        (
                                                            @PaymentTermId <> -1 AND PAYMENTTERMID = @PaymentTermId
                                                        )
                                                        AND PAYMENTTERMID <> @PaymentTermId";
    }
}