pears.CollectionsGetChat

CREATE PROCEDURE "pears"."CollectionsGetChat"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pCollectionID" CHAR(20) ) 
RESULT( "CollectionChatID" CHAR(20),"CollectionID" CHAR(20),"StaffID" CHAR(20),"StaffName" CHAR(60),"UserID" CHAR(25),"ShortID" CHAR(2),"ChatDate" VARCHAR(20),"ChatMessage" long VARCHAR,"Whichside" CHAR(1),"ChatDatetime" TIMESTAMP ) 
BEGIN
  DECLARE LOCAL TEMPORARY TABLE "collchat"(
    "chatid" CHAR(20) NOT NULL,
    "ino" INTEGER NULL,
    PRIMARY KEY("chatid"),) NOT transactional;INSERT INTO "collchat"( "chatid","ino" ) SELECT "CollectionChatID","number"() FROM "collectionchat" WHERE "collectionid" = "pcollectionid" ORDER BY "chatdate" ASC;
  SELECT "c"."CollectionChatID","CollectionID","Staff"."StaffID","staff"."Name" AS "StaffName","staff"."userid","staff"."shortid","dateformat"("ChatDate",'DDD dd/MM/yyyy HH:mm') AS "Chatdate","ChatMessage",
    IF "staff"."staffid" = "userstaffid" THEN 'R' ELSE 'L' endif AS "WhichSide","c"."chatdate"
    FROM "pears"."CollectionChat" AS "c" KEY JOIN "Staff","collchat" AS "cc" WHERE "cc"."chatid" = "c"."collectionchatid" ORDER BY "ino" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CollectionsGetChat" IS 
{CREATE PROCEDURE CollectionsGetChat 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN pCollectionID CHAR(20))
 RESULT(CollectionChatID CHAR(20) ,  CollectionID CHAR(20), StaffID CHAR(20), StaffName  CHAR(60), UserID CHAR(25), ShortID CHAR(2), ChatDate VARCHAR(20), ChatMessage Long VARCHAR, Whichside CHAR(1), ChatDatetime TIMESTAMP) 
BEGIN
  DECLARE LOCAL TEMPORARY TABLE collchat(chatid CHAR(20), ino INTEGER,  PRIMARY KEY(chatid))  NOT transactional;
  INSERT INTO collchat (chatid, ino) SELECT CollectionChatID, NUMBER()  FROM collectionchat WHERE collectionid =pcollectionid ORDER BY chatdate ASC;
  SELECT c.CollectionChatID , CollectionID,Staff.StaffID, staff.Name AS StaffName, staff.userid, staff.shortid, dateformat(ChatDate,'DDD dd/MM/yyyy HH:mm') AS Chatdate, ChatMessage,
   IF staff.staffid = userstaffid THEN 'R' ELSE 'L' endif  AS WhichSide, c.chatdate
    FROM "pears".CollectionChat c KEY JOIN   Staff, collchat cc WHERE cc.chatid = c.collectionchatid  ORDER BY ino;
END
}