====== pears.CollectionsGetChat ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>