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

namespace VehicleDAL.Query.OTF
{
    public static class OTFQB
    {
        public const string GET_OTF_HEADER = @"SELECT
                                            OTF.OTFID AS OTFId,
                                            OTF.OTFNUMBER AS OTFNumber,
                                            OTF.OTFDATE AS OTFDate,

                                            OTF.ITEMID AS ItemId,
                                            MI.ITEMCODE AS ItemCode,
                                            MI.ITEMNAME AS ItemName,

                                            OTF.PARTYID AS PartyId,
                                            MP.PARTYCODE AS PartyCode,
                                            MP.PARTYNAME AS PartyName,

                                            OTF.PARTYBRANCHID AS PartyBranchId,
                                            PB.PARTYBRANCHCODE AS PartyBranchCode,
                                            PB.PARTYBRANCHNAME AS PartyBranchName,
                                            PB.PARTYBRANCHSHORTNAME AS PartyBranchShortName,

                                            OTF.BUYERCONTACTID AS BuyerContactId,
                                            BC.NAME AS BuyerContactName,
                                            BC.FIRSTNAME AS BuyerContactFirstName,
                                            BC.MIDDLENAME AS BuyerContactMiddleName,
                                            BC.LASTNAME AS BuyerContactLastName,

                                            OTF.CONSULTANTID AS ConsultantId,
                                            C.PARTYCODE AS ConsultantCode,
                                            C.PARTYNAME AS ConsultantName,

                                            OTF.MANAGERID AS ManagerId,
                                            M.PARTYCODE AS ManagerCode,
                                            M.PARTYNAME AS ManagerName,

                                            OTF.DATASHEETSTATUS AS OTFDataSheetStatus,

                                            OTF.TEAMLEADERID AS TeamLeaderId,
                                            TL.PARTYCODE AS TeamLeaderCode,
                                            TL.PARTYNAME AS TeamLeaderName,

                                            OTF.DATASHEETDATE AS OTFDataSheetDate

                                        FROM TOTF OTF
                                        LEFT JOIN MITEM MI ON MI.ITEMID = OTF.ITEMID
                                        LEFT JOIN MPARTY MP ON MP.PARTYID = OTF.PARTYID
                                        LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = OTF.PARTYBRANCHID
                                        LEFT JOIN MCONTACT BC ON BC.CONTACTID = OTF.BUYERCONTACTID
                                        LEFT JOIN MPARTY C ON C.PARTYID = OTF.CONSULTANTID
                                        LEFT JOIN MPARTY M ON M.PARTYID = OTF.MANAGERID
                                        LEFT JOIN MPARTY TL ON TL.PARTYID = OTF.TEAMLEADERID
                                        WHERE
                                        OTF.OTFID = @otfid;";
    }
}