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

namespace CRMDAL.Query.Call
{
    public static class CallPicklistQB
    {
        public const string GET_SELECTLIST_CALL_SQL = @"
                                                    WITH CallList AS (
                                                        SELECT
                                                            call.CALLID            AS CallId,
                                                            call.CALLNUMBER        AS CallNumber,
                                                            call.CALLDATE          AS CallDate,
                                                            call.REPORTEDPROBLEM   AS CallReportedProblem,
                                                            emp.EMPLOYEECODE       AS AllotedToEmployeeCode,
                                                            emp.EMPLOYEENAME       AS AllotedToEmployeeName,
                                                            par.PARTYCODE          AS PartyCode,
                                                            par.PARTYNAME          AS PartyName,
                                                            recd.EMPLOYEECODE      AS ReceivedByCode,
                                                            recd.EMPLOYEENAME      AS ReceivedByName,
                                                            CASE call.CALLSTATUS
                                                                WHEN 0 THEN 'Pending'
                                                                WHEN 1 THEN 'Alloted'
                                                                WHEN 2 THEN 'Completed'
                                                                WHEN 3 THEN 'PostPoned'
                                                                WHEN 4 THEN 'CallBack'
                                                                ELSE 'Closed'
                                                            END                    AS OriginalStatus,
                                                            ROW_NUMBER() OVER (ORDER BY call.CALLID DESC) AS RowNum
                                                        FROM TCALL call, MEMPLOYEE emp, MEMPLOYEE recd, MPARTY par
                                                        WHERE call.ALLOTEDTOEMPLOYEEID = emp.EMPLOYEEID
                                                          AND call.PARTYID = par.PARTYID
                                                          AND call.RECEIVEDBYID = recd.EMPLOYEEID
                                                    )
                                                    SELECT
                                                        CallId,
                                                        CallNumber,
                                                        CallDate,
                                                        CallReportedProblem,
                                                        AllotedToEmployeeCode,
                                                        AllotedToEmployeeName,
                                                        PartyCode,
                                                        PartyName,
                                                        ReceivedByCode,
                                                        ReceivedByName,
                                                        OriginalStatus
                                                    FROM CallList
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string GET_SELECTLIST_CALL_PG = @"
                                                    WITH CallList AS (
                                                        SELECT
                                                            call.CALLID            AS ""CallId"",
                                                            call.CALLNUMBER        AS ""CallNumber"",
                                                            call.CALLDATE          AS ""CallDate"",
                                                            call.REPORTEDPROBLEM   AS ""CallReportedProblem"",
                                                            emp.EMPLOYEECODE       AS ""AllotedToEmployeeCode"",
                                                            emp.EMPLOYEENAME       AS ""AllotedToEmployeeName"",
                                                            par.PARTYCODE          AS ""PartyCode"",
                                                            par.PARTYNAME          AS ""PartyName"",
                                                            recd.EMPLOYEECODE      AS ""ReceivedByCode"",
                                                            recd.EMPLOYEENAME      AS ""ReceivedByName"",
                                                            CASE call.CALLSTATUS
                                                                WHEN 0 THEN 'Pending'
                                                                WHEN 1 THEN 'Alloted'
                                                                WHEN 2 THEN 'Completed'
                                                                WHEN 3 THEN 'PostPoned'
                                                                WHEN 4 THEN 'CallBack'
                                                                ELSE 'Closed'
                                                            END                    AS ""OriginalStatus"",
                                                            ROW_NUMBER() OVER (ORDER BY call.CALLID DESC) AS ""RowNum""
                                                        FROM TCALL call, MEMPLOYEE emp, MEMPLOYEE recd, MPARTY par
                                                        WHERE call.ALLOTEDTOEMPLOYEEID = emp.EMPLOYEEID
                                                          AND call.PARTYID = par.PARTYID
                                                          AND call.RECEIVEDBYID = recd.EMPLOYEEID
                                                    )
                                                    SELECT
                                                        ""CallId"",
                                                        ""CallNumber"",
                                                        ""CallDate"",
                                                        ""CallReportedProblem"",
                                                        ""AllotedToEmployeeCode"",
                                                        ""AllotedToEmployeeName"",
                                                        ""PartyCode"",
                                                        ""PartyName"",
                                                        ""ReceivedByCode"",
                                                        ""ReceivedByName"",
                                                        ""OriginalStatus""
                                                    FROM CallList
                                                    WHERE
                                                          (:firstnumber = -1 AND :maxresult = -1)
                                                       OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
                                                    ";

        public const string GET_SELECTLIST_CALL_ORACLE = @";";

        public const string GET_SELECTLIST_CALL_MYSQL = @";";
    }
}