|
|
| (не показана одна промежуточная версия этого же участника) |
| Строка 1: |
Строка 1: |
| | [[TVR_DAV_ZAKAZPR]] - это программа из справочника [[R266]] | | [[TVR_DAV_ZAKAZPR]] - это программа из справочника [[R266]] |
| − | Заказ производству [[EXCEL]]L | + | Заказ производству [[EXCEL]] |
| − | = Текст =
| + | |
| − | 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]] | | [[Функции Delphi]] |
