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

namespace MarketingDAL.Query.LeadSource
{
    public class LeadSourceQB
    {
        public const string GET_LEADSOURCE = @"SELECT
                                            --Parent : LeadSourceDTO
                                            LS.LEADSOURCEID              AS LeadSourceId,
                                            LS.LEADSOURCECODE            AS LeadSourceCode,
                                            LS.LEADSOURCENAME            AS LeadSourceName,
                                            LS.LEADSOURCETYPEID          AS LeadActualSourceTypeId,
                                            LS.REMARKS                   AS LeadSourceRemarks,
                                            LS.SORTORDER                 AS LeadSourceSortOrder,
                                            LS.STATUS                    AS LeadSourceStatus,
                                            LS.VERSION                   AS LeadSourceVersion,
                                            LS.CREATEDBYID               AS LeadSourceCreatedById,
                                            LS.CREATEDON                 AS LeadSourceCreatedOn,
                                            CB.USERNAME                  AS LeadSourceCreatedByName,
                                            LS.MODIFIEDBYID              AS LeadSourceModifiedById,
                                            LS.MODIFIEDON                AS LeadSourceModifiedOn,
                                            MB.USERNAME                  AS LeadSourceModifiedByName,
                                            LS.SOURCETYPE                AS LeadSourceSourceType,
                                            LS.TENANTID                  AS LeadSourceTenantId,

                                                    --Child : LeadSourceDetailDTO 
                                                    LSD.LEADSOURCEDETAILID       AS LeadSourceDetailId,
                                                    LSD.LEADSOURCEID             AS LeadSourceId,
                                                    LSD.SLNO                     AS LeadSourceDetailSlNo,
                                                    LSD.DETAIL                   AS LeadSourceDetailDetail,
                                                    LSD.REMARKS                  AS LeadSourceDetailRemarks,
                                                    LSD.ISACTIVE                 AS LeadSourceDetailIsActive

                                        FROM MLEADSOURCE LS
                                        LEFT JOIN MLEADSOURCEDETAIL LSD  ON LS.LEADSOURCEID = LSD.LEADSOURCEID
                                        LEFT JOIN MUSER CB ON LS.CREATEDBYID = CB.USERID
                                        LEFT JOIN MUSER MB ON LS.MODIFIEDBYID = MB.USERID
                                        WHERE LS.LEADSOURCEID =@leadsourceid;";

        public const string SAVE_LEADSOURCE= @"INSERT INTO MLEADSOURCE
                                                (
                                                    LEADSOURCEID,
                                                    LEADSOURCECODE,
                                                    LEADSOURCENAME,
                                                    LEADSOURCETYPEID,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE,
                                                    TENANTID
                                                )
                                                VALUES
                                                (
                                                    @LeadSourceId,
                                                    @LeadSourceCode,
                                                    @LeadSourceName,
                                                    @LeadActualSourceTypeId,
                                                    @LeadSourceRemarks,
                                                    @LeadSourceSortOrder,
                                                    @LeadSourceStatus,
                                                    @LeadSourceVersion,
                                                    @LeadSourceCreatedById,
                                                    @LeadSourceCreatedOn,
                                                    @LeadSourceModifiedById,
                                                    @LeadSourceModifiedOn,
                                                    @LeadSourceSourceType,
                                                    @LeadSourceTenantId
                                                );";

        public const string SAVE_LEADSOURCE_DETAIL = @"INSERT INTO MLEADSOURCEDETAIL
                                                        (
                                                            LEADSOURCEDETAILID,
                                                            LEADSOURCEID,
                                                            SLNO,
                                                            DETAIL,
                                                            REMARKS,
                                                            ISACTIVE
                                                        )
                                                        VALUES
                                                        (
                                                            @LeadSourceDetailId,
                                                            @LeadSourceId,
                                                            @LeadSourceDetailSlNo,
                                                            @LeadSourceDetailDetail,
                                                            @LeadSourceDetailRemarks,
                                                            @LeadSourceDetailIsActive
                                                        );";

        public const string UPDATE_LEADSOURCE = @"UPDATE MLEADSOURCE
                                                SET
                                                    LEADSOURCECODE     = @LeadSourceCode,
                                                    LEADSOURCENAME     = @LeadSourceName,
                                                    LEADSOURCETYPEID   = @LeadActualSourceTypeId,
                                                    REMARKS            = @LeadSourceRemarks,
                                                    SORTORDER          = @LeadSourceSortOrder,
                                                    STATUS             = @LeadSourceStatus,
                                                    VERSION            = @LeadSourceVersion,
                                                    MODIFIEDBYID       = @LeadSourceModifiedById,
                                                    MODIFIEDON         = @LeadSourceModifiedOn,
                                                    SOURCETYPE         = @LeadSourceSourceType,
                                                    TENANTID           = @LeadSourceTenantId
                                                WHERE
                                                    LEADSOURCEID = @LeadSourceId;";

        public const string UPDATE_LEADSOURCE_DETAIL = @"UPDATE MLEADSOURCEDETAIL
                                                        SET
                                                            LEADSOURCEID   = @LeadSourceId,
                                                            SLNO           = @LeadSourceDetailSlNo,
                                                            [DETAIL]       = @LeadSourceDetailDetail,
                                                            REMARKS        = @LeadSourceDetailRemarks,
                                                            ISACTIVE       = @LeadSourceDetailIsActive
                                                        WHERE
                                                            LEADSOURCEDETAILID = @LeadSourceDetailId;";

        public const string DELETE_LEADSOURCE = @"DELETE FROM MLEADSOURCE
                                                    WHERE LEADSOURCEID = @leadsourceid;";

        public const string DELETE_LEADSOURCE_DETAIL = @"DELETE FROM MLEADSOURCEDETAIL
                                                            WHERE LEADSOURCEID = @leadsourceid;";

        public const string GET_LEADSOURCE_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MLEADSOURCE
                                                    WHERE
                                                        (
                                                            @LeadSourceId <> -1 AND LEADSOURCEID = @LeadSourceId
                                                        )
                                                        AND LEADSOURCEID <> @LeadSourceId";


        public const string GET_SELECTLIST_LEADSOURCE = @"SELECT
                                                            LEADSOURCEID           AS Id,
                                                            LEADSOURCECODE         AS Code,
                                                            LEADSOURCENAME         AS Name
                                                        FROM MLEADSOURCE;";

    }
}