OPEN DATABASE F:\BUX1\MAG9\SKLAD 
*******************************
DATAN={^ 2010 - 01 - 01 } &&        *
DATAK={^ 2010 - 12 - 31 } &&        *
*******************************
DATNT=DTOT(DATAN)
DATKT=DTOT(DATAK)
oper= 0 
IDSK= 0 
*IF .T.
lnConn = SQLSTRINGCONNECT('DRIVER={Microsoft ODBC for Oracle};Server=ORA9I;Uid=LIDA;Pwd=LIDA;')
	WAIT WINDOW "КРАХ SQLCONNECT" 
	RETURN
ENDIF	
TEXT TO lcSQLSelectString TEXTMERGE NOSHOW  
SELECT DOC.CSSN,DOC.POPE,DOC.DDOC,DOC.NDOC,DOC.DTTN,DOC.COPE,NVL(MOL.NTAB,DOC.CPOL) CPOL,DOC.TPOL,
	NVL(ORG.IORG,'')||NVL(SPO.ISPO,'')||NVL(MOL.IFIO,' ') КОНТРАГЕНТ,
	NVL(MOL.NTAB, 00000 ) TAB ,
    NVL(MAT.nmat,' ') nmat, NVL(MAT.kedi, 0 ) kedi, NVL(SEI.iedi,' ') iedi,
    DOC.cdmat, DOC.cdoc,  DOC.noms, DOC.cmat, 
    MAT.SCH cshs,MAT.SSCH csshs, DOC.anal,
    DOC.qkol, 
    DOC.qcen,  DOC.qsum,
    NVL(DOC.qcenv,  0 ) qcenv, NVL(DOC.qsumv,  0 ) qsumv, NVL(DOC.qstvk,  0 ) qstvk,
    NVL(DOC.qnds,  0 )   qnds,  NVL(DOC.qndsv,  0 ) qndsv,
    NVL(MAT.kshifr, 0 ) kshifr, NVL(SHIFR.ishifr,' ') ishifr
FROM  (
............................
ENDTEXT
lnReturn = SQLEXEC(lnConn, lcSQLSelectString, "CDOKS")
IF lnReturn <=  0 
	WAIT WINDOW "КРАХ SQLEXEC" 
ENDIF	
TEXT TO lcSQLSelectString TEXTMERGE NOSHOW  
    SELECT MAT.cmat,MAT.nmat,MAT.kedi,NVL(SEI.iedi,' ') iedi,
    MAT.SCH* 100 +MAT.SSCH SCET,
    NVL(MAT.kshifr, 0 ) kshifr, NVL(SHIFR.ishifr,' ') ishifr
  FROM LIDA.NSI_MAT MAT  
  LEFT JOIN LIDA.NSI_SHIFR  SHIFR ON SHIFR.kshifr  = MAT.kshifr
  LEFT JOIN LIDA.NSI_SEI    SEI   ON SEI.kedi      = MAT.kedi
ENDTEXT


lnReturn = SQLEXEC(lnConn, lcSQLSelectString, "CSTOV")
IF lnReturn <=  0 
	WAIT WINDOW "КРАХ SQLEXEC" 
ENDIF	
= SQLDISCONNECT(lnConn)	 


wait wind nowait "*ОБНОВЛЕНИЕ СПРАВОЧНИКА ПРЕДПРИЯТИЙ"

SELECT DISTINCT CDOKS.CPOL KOD_P,CDOKS.КОНТРАГЕНТ NAIM_P FROM cdoks WHERE tpol= 1  AND cpol NOT in ;
	(SELECT KOD_P FROM SP_AG WHERE kod_p> 0 ) INTO CURSOR POST
INSERT INTO SP_AG (kod_p,nam) SELECT * FROM post

wait wind nowait "*ОБНОВЛЕНИЕ СПРАВОЧНИКА ЦЕХОВ ОТДЕЛОВ"
SELECT DISTINCT CDOKS.CPOL KOD_OTD,CDOKS.КОНТРАГЕНТ NAIM1 FROM cdoks WHERE tpol= 3  AND cpol NOT in ;
	(SELECT KOD_OTD FROM SP_AG WHERE KOD_OTD> 0 ) INTO CURSOR POST
INSERT INTO SP_AG (KOD_OTD,nam) SELECT * FROM post

wait wind nowait "*ОБНОВЛЕНИЕ СПРАВОЧНИКА ФАМИЛИЙ"
SELECT DISTINCT CDOKS.tab,CDOKS.КОНТРАГЕНТ NAM FROM cdoks WHERE tpol= 2  AND TAB NOT in ;
	(SELECT TAB FROM SP_AG WHERE TAB> 0 ) INTO CURSOR POST
INSERT INTO SP_AG (TAB,nam) SELECT * FROM post

wait wind nowait "*ДОБАВЛЕНИЕ СПРАВОЧНИКА ТОВАРОВ"
SELECT DISTINCT cmat KOD_T,nmat NAIM_T2,iedi ED_IZM,SCET,DATE() DATA_V, 0  SRI,ishifr shifr ;
	FROM CSTOV WHERE CMAT NOT IN ;
	(SELECT KOD_T FROM SP_TOV WHERE KOD_T> 0 ) INTO CURSOR CTV
INSERT INTO SP_TOV (KOD_T,NAIM_T2,ED_IZM,SCET,DATA_V,SRI,shifr) SELECT * FROM CTV

*ПРОВЕРКА НАИМЕНОВАНИЯ,СЧЕТА,ЕДИНИЦЫ ИЗМЕРЕНИЯ,ШИФРА В СПРАВОЧНИКЕ ТОВАРОВ

*СОЗДАЕМ КУРСОР КОРРЕКТУРЫ
sele  0 
create cursor ckr ;
(ID I( 4 ),D_DOK D( 8 ),D_OTC D( 8 ),D_NEK D( 8 ),OTP I( 4 ),POL I( 4 ),K_DOK C( 3 ),N_DOK C( 10 ),KOD_T N( 6 ),;
CENA N( 16 , 8 ),CENA_POK N( 16 , 8 ),KOL N( 15 , 3 ),SUMA N( 18 , 2 ),D_KOR N( 5 ),P_NDS N( 5 , 2 ),S_NDS N( 15 , 2 ),GOD N( 4 ))

wait wind nowait "* Копирование таблицы SDOK в курсор CKR"
INSERT INTO CKR ;
SELECT  1 ,TTOD(ddoc),TTOD(ddoc),TTOD(ddoc),INT(IIF(POPE= 0 ,ORG.ID_AGN,SKL.ID_AGN)),INT(IIF(POPE= 0 ,SKL.ID_AGN,ORG.ID_AGN)),CDOKS.COPE, ;
 NDOC,CMAT,QCEN, 0 ,QKOL,QSUM, 0 ,QSTVK,qnds, 9999  FROM CDOKS,SP_AG SKL,SP_AG ORG ;
 WHERE SKL.KOD_OTD=CSSN AND CPOL=ICASE(TPOL= 1 ,ORG.KOD_P,TPOL= 2 ,ORG.TAB,TPOL= 3 ,ORG.KOD_OTD)

*ENDIF


*ФОРМИРОВАНИЕ ТАБЛИЦЫ ПО ДОКУМЕНТАМ ИЗ БАЗЫ ORACLE
SELECT * FROM CKR  ORDER BY D_DOK,K_DOK,N_DOK,OTP,POL,KOD_T,GOD,CENA,KOL INTO CURSOR CDKS 

SELECT DIST D_DOK,K_DOK,N_DOK,OTP,POL FROM CDKS ORDER BY  1 , 2 , 3 , 4 , 5  INTO CURSOR CPR1

SELECT *,RECNO() AS ID_DKS FROM CPR1 INTO CURSOR CPRI

SELECT CDKS.*,CPRI.ID_DKS FROM CDKS,CPRI WHERE CPRI.D_DOK=CDKS.D_DOK AND CPRI.K_DOK=CDKS.K_DOK AND ;
	CPRI.N_DOK=CDKS.N_DOK AND CPRI.OTP=CDKS.OTP AND CPRI.POL=CDKS.POL ;
	ORDER BY CPRI.ID_DKS,KOD_T,GOD,CENA,KOL INTO CURSOR CPRS

IF TYPE("DATN")="D"
	NDAT=DATN
	KDAT=DATK
ENDIF	
DATN=DATAN
DATK=DATAK
*ФОРМИРОВАНИЕ ТАБЛИЦЫ ПО ДОКУМЕНТАМ ИЗ БАЗЫ VFP

SELECT Dok.id_dok, Dok.d_dok, Dok.otp, Dok.pol, Dok.k_dok, Dok.n_dok,;
  Dok.suma AS sumdok, Doks.id_str, Doks.d_otc, Doks.d_nek, Doks.id_tov,;
  Doks.cena, Doks.cena_pok, Doks.kol, Doks.suma, Sp_tov.kod_t, Doks.p_nds,;
  Doks.s_nds, Doks.god;
 FROM ;
     sklad!dok ;
    INNER JOIN  sklad!doks ;
    INNER JOIN sklad!sp_tov ;
   ON  Doks.id_tov = Sp_tov.id_tov ;
   ON  Dok.id_dok = Doks.id_dok;
 WHERE  Dok.d_dok BETWEEN datn AND datk;
   AND  VAL(Dok.k_dok) <  100  ;
 ORDER BY Dok.id_dok INTO CURSOR DKTV


SELECT * FROM DKTV WHERE IIF(IDSK= 0 ,.T.,OTP=IDSK OR POL=IDSK) ;
	ORDER BY D_DOK,K_DOK,N_DOK,OTP,POL,KOD_T,GOD,CENA,KOL INTO CURSOR CDKN

IF TYPE("NDAT")="D"
	DATN=NDAT
	DATK=KDAT
ENDIF	


SELECT DIST D_DOK,K_DOK,N_DOK,OTP,POL,ID_DOK FROM CDKN ORDER BY  1 , 2 , 3 , 4 , 5  INTO CURSOR CPRW
*ВЫБЕРЕМ  ДОКУМЕНТЫ ,БЕЗ ПОВТОРЕНИЙ
SELECT  D_DOK,K_DOK,N_DOK,OTP,POL,MIN(ID_DOK) AS ID_DOK FROM CPRW GROUP BY  1 , 2 , 3 , 4 , 5  ;
  INTO CURSOR CPRN
*ВЫБЕРЕМ И УДАЛИМ ЛИШНИЕ
SELECT ID_DOK FROM CPRW WHERE NOT EXISTS (SELECT  * FROM CPRN WHERE CPRN.ID_DOK=CPRW.ID_DOK) ;
	INTO CURSOR CUD
SCAN
	DOK_ID=CUD.ID_DOK
	DELETE FROM SKLAD!DOK WHERE ID_DOK=DOK_ID
ENDSCAN

*НОВЫЕ ДОКУМЕНТЫ(ДЛЯ ВСТАВКИ)
SELECT  D_DOK,K_DOK,N_DOK,OTP,POL,ID_DKS FROM CPRI WHERE NOT EXISTS ;
	(SELECT * FROM CPRN WHERE CPRN.D_DOK=CPRI.D_DOK AND CPRN.K_DOK=CPRI.K_DOK AND ;
	CPRN.N_DOK=CPRI.N_DOK AND CPRN.OTP=CPRI.OTP AND CPRN.POL=CPRI.POL) ;
	INTO CURSOR CDKW

SCAN
	DKS_ID=CDKW.ID_DKS
	SELECT CPRS.*,SP_TOV.ID_TOV FROM CPRS,SP_TOV WHERE SP_TOV.KOD_T=CPRS.KOD_T AND ;
		CPRS.ID_DKS=DKS_ID INTO CURSOR CDT
	SELE CDT
	GO TOP
	SCAN
			INSERT INTO SKLAD!DOKS (D_OTC,D_NEK,ID_TOV,CENA,CENA_POK,KOL,D_KOR,P_NDS,S_NDS,ID_DOK,GOD) ;
				VALUES (CDT.D_OTC,CDT.D_NEK,CDT.ID_TOV,CDT.CENA,CDT.CENA_POK,CDT.KOL,CDT.D_KOR,CDT.P_NDS,CDT.S_NDS,DOK_ID,CDT.GOD)
			STR_id=DOKS.ID_STR
	ENDSCAN

ENDSCAN

*УДАЛЁННЫЕ ДОКУМЕНТЫ(В СТАРОЙ БАЗЕ НЕТ)
SELECT * FROM CPRN WHERE NOT EXISTS ;
	(SELECT * FROM CPRI WHERE CPRN.D_DOK=CPRI.D_DOK AND CPRN.K_DOK=CPRI.K_DOK AND ;
	CPRN.N_DOK=CPRI.N_DOK AND CPRN.OTP=CPRI.OTP AND CPRN.POL=CPRI.POL) ;
	INTO CURSOR CDKU
SCAN
	DOK_ID=CDKU.ID_DOK
	DELETE FROM SKLAD!DOK WHERE DOK.ID_DOK=DOK_ID
ENDSCAN

*ДОКУМЕНТЫ КОТОРЫЕ ЕСТЬ В ОБЕИХ БАЗАХ
SELECT ID_DKS,ID_DOK FROM CPRI,CPRN WHERE CPRN.D_DOK=CPRI.D_DOK AND CPRN.K_DOK=CPRI.K_DOK AND ;
	CPRN.N_DOK=CPRI.N_DOK AND CPRN.OTP=CPRI.OTP AND CPRN.POL=CPRI.POL ;
	INTO CURSOR CDKK

SELE CDKK
GO TOP
SCAN
	DKS_ID=CDKK.ID_DKS
	SELECT * FROM CPRS WHERE ID_DKS=DKS_ID INTO CURSOR CST
	KZS=_TALLY
	DOK_ID=CDKK.ID_DOK
	SELECT * FROM CDKN WHERE ID_DOK=DOK_ID INTO CURSOR CNW
	KZN=_TALLY
	DO KRDOK
ENDSCAN


CLOS TABL ALL

PROC KRDOK
KLS=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 )
KLN=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 )
I= 0 
J= 0 
=NARI()
=NARJ()

DO WHILE NOT (  KLN=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 ) AND KLS=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 ) )


*замена кол,ндс,GOD
DO WHILE KLS=KLN AND KLN < STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 )
IF NOT (CST.KOL=CNW.KOL AND CST.P_NDS=CNW.P_NDS AND CST.S_NDS=CNW.S_NDS AND CST.GOD=CNW.GOD)
	STR_ID=CNW.ID_STR
	KOLK=CST.KOL
	P_NDSK=CST.P_NDS
	S_NDSK=CST.S_NDS
	GODK=CST.GOD
	UPDATE SKLAD!DOKS SET KOL=KOLK,P_NDS=P_NDSK,S_NDS=S_NDSK,GOD=GODK WHERE ID_STR=STR_ID
ENDIF
=NARI()
=NARJ()
ENDDO

*ВСТАВКА 
DO WHILE KLS<KLN
	T_KOD=CST.KOD_T
	SELECT SP_TOV.ID_TOV FROM SP_TOV WHERE KOD_T=T_KOD INTO CURSOR CID
	TOV_ID=CID.ID_TOV
	USE IN CID 
	INSERT INTO SKLAD!DOKS (D_OTC,D_NEK,ID_TOV,CENA,CENA_POK,KOL,D_KOR,P_NDS,S_NDS,ID_DOK,GOD) ;
		VALUES (CST.D_OTC,CST.D_NEK,TOV_ID,CST.CENA,CST.CENA_POK,CST.KOL,CST.D_KOR,CST.P_NDS,CST.S_NDS,DOK_ID,CST.GOD)
	=NARI()	
ENDDO
*УДАЛЕНИЕ
DO WHILE KLS>KLN
	STR_ID=CNW.ID_STR
	DELETE FROM SKLAD!DOKS WHERE ID_STR=STR_ID
	=NARJ()
ENDDO

ENDDO

FUNCTION NARI
I=I+ 1 
IF I>KZS
	KLS=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 )
ELSE
	SELE CST
	GO I
	KLS=STR(CST.KOD_T, 6 )+STR(CST.GOD, 4 )+str(CST.CENA, 16 , 8 )
ENDIF
ENDFUNC

FUNCTION NARJ
J=J+ 1 
IF J>KZN
	KLN=STR( 999999 , 6 )+STR( 9999 , 4 )+str( 9999999 . 99999999 , 16 , 8 )
ELSE
	SELE CNW
	GO J
	KLN=STR(CNW.KOD_T, 6 )+STR(CNW.GOD, 4 )+str(CNW.CENA, 16 , 8 )
ENDIF
ENDFUNC