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

namespace PayRollDAL.Query.TDSContact
{
    public class TDSContactQB
    {
        public const string GET_SELECTLIST_TDSCONTACT = @"WITH PagedTDSContact AS
                                                        (
                                                            SELECT
                                                                TC.TDSCONTACTID AS Id,
                                                                TC.NAME         AS Name,
                                                                TC.PAN          AS PAN,
                                                                TC.TAN          AS TAN,
                                                                ROW_NUMBER() OVER (ORDER BY TC.NAME) AS RowNum
                                                            FROM MTDSCONTACT TC
                                                            WHERE 
                                                                TC.STATUS = 1
                                                                /**where**/
                                                        )
                                                        SELECT
                                                            Id,
                                                            Name,
                                                            PAN,
                                                            TAN
                                                        FROM PagedTDSContact
                                                        WHERE 
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    

        public const string GET_SELECTLIST_TDSCONTACT_PG = @"WITH PagedTDSContact AS
                                                            (
                                                                SELECT
                                                                    TC.TDSCONTACTID AS ""Id"",
                                                                    TC.NAME         AS ""Name"",
                                                                    TC.PAN          AS ""PAN"",
                                                                    TC.TAN          AS ""TAN"",
                                                                    ROW_NUMBER() OVER (ORDER BY TC.NAME) AS ""RowNum""
                                                                FROM MTDSCONTACT TC
                                                                WHERE
                                                                    TC.STATUS = 1
                                                                    /**where**/
                                                            )
                                                            SELECT
                                                                ""Id"",
                                                                ""Name"",
                                                                ""PAN"",
                                                                ""TAN""
                                                            FROM PagedTDSContact
                                                            WHERE
                                                                (@firstnumber = -1 AND @maxresult = -1)
                                                                OR (""RowNum"" BETWEEN @firstnumber AND @maxresult)
                                                            ORDER BY ""RowNum"";";

        public const string GET_SELECTLIST_TDSCONTACT_ORACLE = @"";

        public const string GET_SELECTLIST_TDSCONTACT_MYSQL = @"";

        public const string GET_TDSCONTACT = @"SELECT 
                                                TC.TDSCONTACTID                AS TDSContactId,
                                                TC.EMPLOYEEID                 AS EmployeeId,
                                                EMP.EMPLOYEECODE              AS EmployeeCode,
                                                EMP.EMPLOYEENAME              AS EmployeeName,
                                                TC.CONTACTTYPE                AS TDSContactContactType,
                                                TC.NAME                       AS TDSContactName,
                                                TC.PAN                        AS TDSContactPAN,
                                                TC.TAN                        AS TDSContactTAN,
                                                TC.ADDRESSLINE1               AS TDSContactAddressLine1,
                                                TC.ADDRESSLINE2               AS TDSContactAddressLine2,
                                                TC.ADDRESSLINE3               AS TDSContactAddressLine3,
                                                TC.ADDRESSLINE4               AS TDSContactAddressLine4,
                                                TC.ADDRESSLINE5               AS TDSContactAddressLine5,
                                                TC.SOURCETYPE                 AS TDSContactSourceType,
                                                TC.SORTORDER                  AS TDSContactSortOrder,
                                                TC.STATUS                     AS TDSContactStatus,
                                                TC.VERSION                    AS TDSContactVersion,
                                                TC.CREATEDBYID                AS TDSContactCreatedById,
                                                TC.CREATEDON                  AS TDSContactCreatedOn,
                                                TC.MODIFIEDBYID               AS TDSContactModifiedById,
                                                TC.MODIFIEDON                 AS TDSContactModifiedOn

                                            FROM MTDSCONTACT TC
                                            LEFT JOIN MEMPLOYEE EMP ON EMP.EMPLOYEEID = TC.EMPLOYEEID
                                            WHERE TC.TDSCONTACTID = @TDScontactid;";

        public const string SAVE_TDSCONTACT = @"INSERT INTO MTDSCONTACT
                                                (
                                                    TDSCONTACTID,
                                                    EMPLOYEEID,
                                                    CONTACTTYPE,
                                                    NAME,
                                                    PAN,
                                                    TAN,
                                                    ADDRESSLINE1,
                                                    ADDRESSLINE2,
                                                    ADDRESSLINE3,
                                                    ADDRESSLINE4,
                                                    ADDRESSLINE5,
                                                    VERSION,
                                                    STATUS,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @TDSContactId,
                                                    @EmployeeId,
                                                    @TDSContactContactType,
                                                    @TDSContactName,
                                                    @TDSContactPAN,
                                                    @TDSContactTAN,
                                                    @TDSContactAddressLine1,
                                                    @TDSContactAddressLine2,
                                                    @TDSContactAddressLine3,
                                                    @TDSContactAddressLine4,
                                                    @TDSContactAddressLine5,
                                                    @TDSContactVersion,
                                                    @TDSContactStatus,
                                                    @TDSContactCreatedById,
                                                    @TDSContactCreatedOn,
                                                    @TDSContactModifiedById,
                                                    @TDSContactModifiedOn
                                                );";

        public const string UPDATE_TDSCONTACT = @"UPDATE MTDSCONTACT
                                                SET
                                                    EMPLOYEEID     = @EmployeeId,
                                                    CONTACTTYPE    = @TDSContactContactType,
                                                    NAME           = @TDSContactName,
                                                    PAN            = @TDSContactPAN,
                                                    TAN            = @TDSContactTAN,
                                                    ADDRESSLINE1   = @TDSContactAddressLine1,
                                                    ADDRESSLINE2   = @TDSContactAddressLine2,
                                                    ADDRESSLINE3   = @TDSContactAddressLine3,
                                                    ADDRESSLINE4   = @TDSContactAddressLine4,
                                                    ADDRESSLINE5   = @TDSContactAddressLine5,
                                                    VERSION        = @TDSContactVersion,
                                                    STATUS         = @TDSContactStatus,
                                                    MODIFIEDBYID   = @TDSContactModifiedById,
                                                    MODIFIEDON     = @TDSContactModifiedOn
                                                WHERE TDSCONTACTID = @TDSContactId;";

        public const string DELETE_TDSCONTACT = @"DELETE FROM MTDSCONTACT 
                                                  WHERE TDSCONTACTID = @TDScontactid";
    }
}