"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

ACC_SVODKA_PROVIDER

Входные параметры

Выходные параметры

Описание

(Нет описания для процедуры ACC_SVODKA_PROVIDER)

Определение

CREATE PROCEDURE ACC_SVODKA_PROVIDER(
    BEGDATE DATE,
    ENDDATE DATE)
RETURNS (
    REGDATE DATE,
    PROVIDER VARCHAR(100),
    SENDER VARCHAR(100),
    OWNER VARCHAR(100),
    CARNUMBER VARCHAR(31),
    TRAILERNUMBER VARCHAR(31),
    NET INTEGER,
    CROPCLASS VARCHAR(100),
    INVOICENUMBER VARCHAR(31),
    DRIVER VARCHAR(30))
AS
DECLARE VARIABLE invoiceid INTEGER;
DECLARE VARIABLE trailernet INTEGER;
DECLARE VARIABLE classid INTEGER;
DECLARE VARIABLE trailercropclass VARCHAR(100);
DECLARE VARIABLE temptrailernet INTEGER;
DECLARE VARIABLE temptrailernumber VARCHAR(31);
begin
for

   SELECT it_car.regdate,
       cp.clientfullname providername,
       cs.clientfullname sendername,
       co.clientfullname ownername,
       ittransport_car.transportnumber car_number,
       coalesce (it_car.net, 0) net,
       qnc.cropfullname,
       invoices.invoicenumber, it_car.invoiceid, qc.classid, invoices.drivers
FROM  it it_car
  left join quality qc on (it_car.samplerqualityid = qc.qualityid)
  left join quality_cropfullname(qc.cropid, qc.classid, null, 1) qnc on (0 = 0)

  left join clients cp on (it_car.providerid = cp.clientid)
  left join clients co on (it_car.ownerid = co.clientid)
  left join invoices on (it_car.invoiceid = invoices.invoiceid)
  left join clients cs on (invoices.senderid = cs.clientid)
  left join storage on (it_car.storageid = storage.storageid)
  left join operation on (it_car.operationid = operation.operationid)
  left join ittransport ittransport_car on (it_car.inventoryid = ittransport_car.inventoryid)
 where  it_car.regdate between :begdate and :enddate
--  and it_car.partyid =  (select partyid from getpartyid(:PARTYID,:PARTYDATE, qc.cropid, qc.classid, it_car.storageid))
  and operationsubtype = 0
  and operation.operationtype not in (3,6)
  and it_car.operationid <> 4
  and storage.storagetypeid = 1

--  and (it_car.Net is not null and it_car.net <> 0)
  and it_car.parentid >= 0
  and it_car.state >= 0
  and (ittransport_car.transporttypeid  = 0)
  into
   :regdate,
   :provider,
   :sender,
   :owner,
   :carnumber,
   :net,
   :cropclass,
   :invoicenumber,
   :invoiceid,
   :classid,
   :driver
 do
 begin
   select it_trailer.net, ittransport_trailer.transportnumber,
     case when (qt.classid <> :classid)  then  qnt.cropfullname
     else '' end
   from it it_trailer
  left join quality qt on (it_trailer.samplerqualityid = qt.qualityid)
  left join quality_cropfullname(qt.cropid, qt.classid, null, 1) qnt on (0 = 0)

  left join storage on (it_trailer.storageid = storage.storageid)
 -- left join operation on (it_car.operationid = operation.operationid)
  left join ittransport ittransport_trailer on (it_trailer.inventoryid = ittransport_trailer.inventoryid)
   where
    it_trailer.regdate between :begdate and :enddate
--  and it_car.partyid =  (select partyid from getpartyid(:PARTYID,:PARTYDATE, qc.cropid, qc.classid, it_car.storageid))
/*  and operationsubtype = 0
  and operation.operationtype not in (3,6)
  and it.operationid <> 4      */
  and storage.storagetypeid = 1

--  and ((it_trailer.Net is not null and it_trailer.net <> 0))
  and it_trailer.parentid >= 0
  and it_trailer.state >= 0
  and (ittransport_trailer.transporttypeid = 1)
  and it_trailer.parentid >= 0
  and it_trailer.state >= 0
  and it_trailer.invoiceid = :invoiceid
--  and qt.classid = :classid
  into
   :temptrailernet,
   :temptrailernumber,
   :trailercropclass;

   if (:trailercropclass = '') then
   begin
     trailernet =  :temptrailernet;
     trailernumber = :temptrailernumber;
     net = coalesce(:net,0) + coalesce(:trailernet,0);
   end

  if (:net <> 0) then
     suspend;

   if (:trailercropclass <> '') then
   begin
     carnumber = '';
     net = :temptrailernet;
     cropclass = :trailercropclass;
     trailernumber = :temptrailernumber;
     if (:net <> 0) then
       suspend;
     trailercropclass = '';
   end

    trailernet =  0;
    trailernumber = '';
    temptrailernumber = '';
    temptrailernet =  0;
 end

 for
   SELECT it_car.regdate,
       cp.clientfullname providername,
       cs.clientfullname sendername,
       co.clientfullname ownername,
       ittransport_car.transportnumber car_number,
       it_car.net net,
       qnc.cropfullname,
       invoices.invoicenumber, invoices.drivers
FROM  it it_car              
  left join quality qc on (it_car.samplerqualityid = qc.qualityid)
  left join quality_cropfullname(qc.cropid, qc.classid, null, 1) qnc on (0 = 0)

  left join clients cp on (it_car.providerid = cp.clientid)
  left join clients co on (it_car.ownerid = co.clientid)
  left join invoices on (it_car.invoiceid = invoices.invoiceid)
  left join clients cs on (invoices.senderid = cs.clientid)
  left join storage on (it_car.storageid = storage.storageid)
  left join operation on (it_car.operationid = operation.operationid)
  left join ittransport ittransport_car on (it_car.inventoryid = ittransport_car.inventoryid)
 where  it_car.regdate between :begdate and :enddate
--  and it_car.partyid =  (select partyid from getpartyid(:PARTYID,:PARTYDATE, qc.cropid, qc.classid, it_car.storageid))
  and operationsubtype = 0
  and operation.operationtype not in (3,6)
  and it_car.operationid <> 4
  and storage.storagetypeid = 1

  --and (it_car.Net is not null and it_car.net <> 0)
  and it_car.parentid >= 0
  and it_car.state >= 0
  and (ittransport_car.transporttypeid  = 1)
  and not exists (select ittransport.transporttypeid from it
       left join ittransport on (it.inventoryid = ittransport.inventoryid)
       where it.invoiceid = it_car.invoiceid and ittransport.inventoryid <> it_car.inventoryid and ittransport.transporttypeid between 0 and 1 and it.state >= 0)
  into
   :regdate,
   :provider,
   :sender,
   :owner,
   :trailernumber,
   :net,
   :cropclass,
   :invoicenumber,
   :driver
 do
 begin
   carnumber = '';
   if (:net <> 0) then
     suspend;
 end
end