Версия 11:42, 19 июня 2017

TVR_DAV_ZAKAZPR - это программа из справочника R266

Заказ производству EXCELL

Текст

local dt,dtz,dtr,fld_t,t,usl3939,sql_t,_usl3939,tt,tt_y,ttt,msg,dict

dt:=date()// текущая дата
dtz:=dt+1 // прогноз на завтра

if YesNo('Задать дату заказа?', true)
   dtz:= inputdate(dtz)
endif
dtr:=dtz-7
if YesNo('Задать дату прошлой недели?', true)
   dtr:= inputdate(dtr)
endif

try

msg=WaitMsg('Выборка данных по отгрузке и заказам за '+dtr+,msg)

fld_t:='kodf,N,5;nazv1,C,230;ves1,N,5,3;vessr,n,10,3;vessz,N,10,3;vess,N,13,3;;
codegroup,C,12'
t:= CreateTmpFile(fld_t,'t',~IsFreeDel:=True,~Own:=PrintForm())

//выбрка отгрузки и заказов за заданную дату
SQL_t:=[select A.KANAL2 AS KODf,b.pname AS NAZV1,b.weight AS ves1,;
SUM(A.NATTR9) AS VESSR,0.000 as vessz,0.000 as vess,b.codegroup  from ];
+OpDataPath+[\DOCS]+Ret_f_ext(dtr)+[ A,]+ReflisPath+[\r11 B ;
 where A.KINDDOC='RTH' AND A.LVLNUM='3' AND A.KANAL2=B.KOD and a.cattr10='Н' ;
and a.datdoc=']+dtr+[' and a.danal2>0 and a.kanal1<>70 ;
GROUP BY a.kanal2,b.pname,b.weight,b.codegroup ;
union ;
select a.kanal2 as kodf,b.pname AS nazv1,b.weight AS ves1,;
0.0 as vessr,sum(a.nattr3) AS vessz,0.000 as vess,b.codegroup ;
 from ]+OpDataPath+[\DOCZ]+Ret_f_ext(dtr)+[ A,]+ReflisPath+[\r11 B ;
 where A.KINDDOC='ZAK' ;
 AND A.LVLNUM='2' AND A.kanal2=B.KOD ;
and a.datdoc=']+dtr+[' and a.danal2>0 and a.kanal1<>70 ;
gROUP BY a.kanal2,b.pname,b.weight,b.codegroup]
SimpleSQL([Insert into] +TmpFilePath(t)+[ ]+sql_t,OpDataPath)

//выборка по заказам за вчерашний день
fld_t:='kodf,N,5;vess_y,n,10,3'
tt_y:= CreateTmpFile(fld_t,'tt_y',~IsFreeDel:=True,~Own:=PrintForm())
SQL_t:=[select kanal2 as kodf,sum(nattr3) AS vess_y from ]+OpDataPath+[\DOCZ]+Ret_f_ext(dtz-1)+[ where KINDDOC='ZAK' ;
     AND LVLNUM='2' and datdoc=']+(dtz-1)+[' and danal2>0 and kanal1<>70 GROUP BY kanal2]
SimpleSQL([Insert into] +TmpFilePath(tt_y)+[ ]+sql_t,OpDataPath)

//суммирование по коду заказов и факт.отгрузки
fld_t:='kodf,N,5;nazv1,C,230;ves1,N,5,3;vessr,n,10,3;vessz,N,10,3;vess,N,13,3;codegroup,C,12;usl,N,1;nazv,C,90'
tt:= CreateTmpFile(fld_t,'tt',~IsFreeDel:=True,~Own:=PrintForm())

sql_t:=[select kodf,nazv1,ves1,sum(vessr) as vessr,sum(vessz) as vessz,;
0.0 as vess,codegroup,0.0 as usl,' ' as nazv from ];
+TmpFilePath(t)+[ group by kodf,nazv1,ves1,codegroup]
SimpleSQL([Insert into] +TmpFilePath(tt)+[ ]+sql_t,OpDataPath)

(tt)->(DbGoTop())
while (tt)->(!eof())
      (tt)->vess:=iif((tt)->vessr>=(tt)->vessz,(tt)->vessr,(tt)->vessz)
      (tt)->usl:=0
      if substr((tt)->codegroup,1,2)='01'
         (tt)->usl:=1
      endif
      if substr((tt)->codegroup,1,4)='0103'
         (tt)->usl:=0
      endif
      (tt)->nazv:=substr((tt)->nazv1,1,89)
      if (tt)->kodf=494 or (tt)->kodf=74 or (tt)->kodf=985 or (tt)->kodf=1351   // Отпускается в эксп ХБЦ
         (tt)->usl:=1
      endif
   (tt)->DbSkip()
end

//отбор нужных кодов по группе товаров
fld_t:='nazv,C,90;kodf,N,5;ves1,N,5,3;vess,N,10,3;ves_y,N,10,3;usl,N,1'
ttt:= CreateTmpFile(fld_t,'ttt',~IsFreeDel:=True,~Own:=PrintForm())

sql_t:=[select nazv,kodf,ves1,sum(vess) as vess,0 as ves_y,usl from ];
+TmpFilePath(tt)+[ where usl=1 group by nazv,kodf,ves1,usl]
SimpleSQL([Insert into] +TmpFilePath(ttt)+[ ]+sql_t,OpDataPath)

//добавление заказов вчерашнего дня
sql_t:=[Update t Set ves_y=vess_y From ]+TmpFilePath(ttt)+[ t left outer join ]+TmpFilePath(tt_y)+[ t1 ON t.kodf=t1.kodf]
SimpleSql(sql_t, ReflisPath )

dict := { { 'dtz', dtz } }

ExcelReport('ZakazPr.xlt',,ttt,,dict)
HideMsg(msg)
DeleteTMPFile(t)
DeleteTMPFile(tt)
DeleteTMPFile(tt_y)
DeleteTMPFile(ttt)

finally
STOPREPORT()
end

Смотрите также

Полезные функции

Функции Delphi