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

namespace MarketingDAL.Query.Lead
{
    public static class LeadPicklistQB
    {
        public const string GET_SELECTLIST_LEAD_SQL = @"
                                                    WITH Lead AS (
                                                        SELECT
                                                            lead.LEADID          AS Id,
                                                            lead.LEADNAME        AS Name,
                                                            lead.LEADCODE        AS Code,
                                                            lead.LEADSTATUS      AS LeadStatus,
                                                            lead.REFERENCENUMBER AS ReferenceNumber,
                                                            CASE
                                                                WHEN lead.SUCESSSTATUS = 0 THEN 'Progress'
                                                                WHEN lead.SUCESSSTATUS = 1 THEN 'Hold'
                                                                WHEN lead.SUCESSSTATUS = 2 THEN 'Won/Fulfilled'
                                                                WHEN lead.SUCESSSTATUS = 3 THEN 'Lost'
                                                                WHEN lead.SUCESSSTATUS = 4 THEN 'Drop/Close'
                                                                ELSE 'Re-Open'
                                                            END                  AS SuccessStatusName,
                                                            lcd.STAGENAME        AS Stage,
                                                            ROW_NUMBER() OVER (ORDER BY lead.LEADID DESC) AS RowNum
                                                        FROM TLEAD lead, MLEADCYCLEDETAIL lcd
                                                        WHERE lcd.LEADCYCLEDETAILID = lead.LEADCYCLEDETAILID
                                                    )
                                                    SELECT
                                                        Id,
                                                        Name,
                                                        Code,
                                                        LeadStatus,
                                                        ReferenceNumber,
                                                        SuccessStatusName,
                                                        Stage
                                                    FROM Lead
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string GET_SELECTLIST_LEAD_PG = @"
                                                    WITH Lead AS (
                                                        SELECT
                                                            lead.LEADID          AS ""Id"",
                                                            lead.LEADNAME        AS ""Name"",
                                                            lead.LEADCODE        AS ""Code"",
                                                            lead.LEADSTATUS      AS ""LeadStatus"",
                                                            lead.REFERENCENUMBER AS ""ReferenceNumber"",
                                                            CASE
                                                                WHEN lead.SUCESSSTATUS = 0 THEN 'Progress'
                                                                WHEN lead.SUCESSSTATUS = 1 THEN 'Hold'
                                                                WHEN lead.SUCESSSTATUS = 2 THEN 'Won/Fulfilled'
                                                                WHEN lead.SUCESSSTATUS = 3 THEN 'Lost'
                                                                WHEN lead.SUCESSSTATUS = 4 THEN 'Drop/Close'
                                                                ELSE 'Re-Open'
                                                            END                  AS ""SuccessStatusName"",
                                                            lcd.STAGENAME        AS ""Stage"",
                                                            ROW_NUMBER() OVER (ORDER BY lead.LEADID DESC) AS ""RowNum""
                                                        FROM TLEAD lead, MLEADCYCLEDETAIL lcd
                                                        WHERE lcd.LEADCYCLEDETAILID = lead.LEADCYCLEDETAILID
                                                    )
                                                    SELECT
                                                        ""Id"",
                                                        ""Name"",
                                                        ""Code"",
                                                        ""LeadStatus"",
                                                        ""ReferenceNumber"",
                                                        ""SuccessStatusName"",
                                                        ""Stage""
                                                    FROM Lead
                                                    WHERE
                                                          (:firstnumber = -1 AND :maxresult = -1)
                                                       OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
                                                    ";

        public const string GET_SELECTLIST_LEAD_ORACLE = @";";
        public const string GET_SELECTLIST_LEAD_MYSQL = @";";
    }
}