--1. aktualizacja wlasciciela
update product.product set  [ownerFirmID] 	 =  ca.remoteid
	from product.product pp inner join common.address ca on pp.addressID = ca.addressID
where ownerFirmID is  null



--2 przenumerowanie operacji technologichnych +/-
update wms.technologyLine SET technologyOrder=x.x
from wms.technologyLine  t
inner join 
(
select *
from (
select 
( ROW_NUMBER()OVER (PARTITION BY remoteID ORDER BY technologyOrder) )x,
* from wms.technologyLine where status>-1
and remoteSource='structure'
)w
where  w.x = w.technologyOrder
)x ON x.technologyLineID=t.technologyLineID







SELECT ZL.SYMBOL,
       ZL.NAZWA,
       Z.NUMER,
       AP.NAZWA,
       AP.INDEKS,
       Z.ILOSC_PLAN,
       Z.ILOSC_RZECZ,
       Z.ILOSC_PLAN- Z.ILOSC_RZECZ
FROM TETA_ADMIN.PR_ZUZYCIA Z 
LEFT JOIN TETA_ADMIN.PR_ZLECENIA ZL ON Z.ZLEC_ID=ZL.ID
LEFT JOIN TETA_ADMIN.AP_INDEKSY_MATERIALOWE AP ON Z.INMA_ID=AP.ID
WHERE ZLEC_ID = 55947 
ORDER BY NUMER;



INSERT INTO [common].[dictionary]
								  (
									[dictionaryID] ,[dictionaryName],[dictionaryKey],[dictionaryKeyGuid],[dictionaryValue],[dictionaryUrl],[dictionaryOrder],
									[dictionaryXML],[dictionaryDefaultValueInGroup],[dictionaryMasterKey],[groupID],[adddate],[addusername],[moddate],
									[modusername]
								  )
	SELECT			NEWID()					,
					'system.accountingVat'		,
					dictionaryKey			,
					dictionaryKey			,
					nazwa	,
					kod						,
					id,
					NULL		,
					NULL		,
					NULL		,
					NULL		,
					GETDATE()	,
					'sys'		,
					GETDATE()	,
					'sys'			 
	FROM					
		(
			SELECT 
					NEWID()		dictionaryKey,
					x.*			
			FROM OPENQUERY(TETA, 
							'SELECT * FROM TETA_ADMIN.RK_RODZAJE_VAT'
						) x
		)s	
;




INSERT INTO [common].[dictionary]
								  (
									[dictionaryID] ,[dictionaryName],[dictionaryKey],[dictionaryKeyGuid],[dictionaryValue],[dictionaryUrl],[dictionaryOrder],
									[dictionaryXML],[dictionaryDefaultValueInGroup],[dictionaryMasterKey],[groupID],[adddate],[addusername],[moddate],
									[modusername],accounting
								  )
	SELECT			NEWID()					,
					'system.accountingAccountName'		,
					dictionaryKey			,
					dictionaryKey			,
					nazwa	,
					SYNTETYKA						,
					row_number() over(order by id),
					NULL		,
					NULL		,
					NULL		,
					NULL		,
					GETDATE()	,
					'sys'		,
					GETDATE()	,
					'sys'	,
                    SYNTETYKA
	FROM					
		(
			SELECT 
					NEWID()		dictionaryKey,
					x.*			
			FROM OPENQUERY(TETA, 
							'SELECT * FROM TETA_ADMIN.RK_STRUKTURY_KONT WHERE ROOB_ID=1000069'
						) x
		)s	
;
 
 UPDATE common.dictionary set isSystem=1 where dictionaryName IN ('system.accountingAccountName','system.accountingVat')
/*
CHAR(39)++CHAR(39)+','+
 
'
INSERT INTO common.dictionary
(
    dictionaryID,dictionaryName,dictionaryKey,dictionaryKeyGuid,dictionaryValue,dictionaryUrl,dictionaryOrder
    ,adddate,addusername,isSystem,accounting
)
'*/
 SELECT 
 'select '+CHAR(39)+CAST(dictionaryID AS nvarchar(36))+CHAR(39)+','+
           CHAR(39)+dictionaryName+CHAR(39)+','+
           CHAR(39)+dictionaryKey+CHAR(39)+','+
           CHAR(39)+CAST(dictionaryKeyGuid AS nvarchar(36))+CHAR(39)+','+
           CHAR(39)+dictionaryValue+CHAR(39)+ ','+
           CHAR(39)+dictionaryUrl+CHAR(39)+','+
           CHAR(39)+cast(dictionaryOrder AS nvarchar(10))+CHAR(39)+','+
        --   CHAR(39)+cast(dictionaryDefaultValueInGroup AS nvarchar(10))+CHAR(39)+','+
    --       CHAR(39)+CAST(groupID AS nvarchar(36))+CHAR(39)+','+
           CHAR(39)+CAST(adddate AS nvarchar(36))+CHAR(39)+','+
           CHAR(39)+addusername+CHAR(39)+','+
           CHAR(39)+CAST(moddate AS nvarchar(36))+CHAR(39)+','+
           CHAR(39)+modusername+CHAR(39)+','+
           CHAR(39)+cast(isSystem AS nvarchar(10))+CHAR(39)+','+
           CASE WHEN accounting IS NULL THEN 'null' else
           CHAR(39)+CAST(accounting AS nvarchar(36))+CHAR(39) end+' union'
 ,  
 * FROM common.dictionary where 
 
 
 
 
 
 
 
 
 
 
 
 
 INSERT INTO DBO.TEST_ZEST
SELECT WZTY_KOD,
       MAGA_ID,
       DATA_WYSTAWIENIA,
       SYMBOL,
       INDEKS,
      CAST( '0'+ILOSC AS decimal(18,3)),
       NR_SERII,
       CAST( '0'+CENA AS decimal(18,2)),
       LOKA_KOD
FROM OPENQUERY(TETA, 
'
SELECT 
T.WZTY_KOD,
T.MAGA_ID,
T.DATA_WYSTAWIENIA,
Z.SYMBOL,
A.INDEKS,
I.ILOSC,
I.NR_SERII,
I.CENA,
I.LOKA_KOD
 FROM TETA_ADMIN.AP_DOKUMENTY_OBROT T
INNER JOIN TETA_ADMIN.AP_DOKUMENTY_OBROT_IT I ON I.DOOB_ID =T.ID
LEFT JOIN TETA_ADMIN.PR_ZLECENIA Z ON Z.ID = T.ZLEC_ID
LEFT JOIN TETA_ADMIN.AP_INDEKSY_MATERIALOWE A ON A.ID=I.INMA_ID
WHERE T.WSKAZNIK_ZATWIERDZENIA=''T''
AND TO_CHAR(T.DATA_WYSTAWIENIA,''YYYY-MM'')=''2013-12''

')X

SELECT TOP 100 * FROM DBO.TEST_ZEST

UPDATE DBO.TEST_ZEST SET date=REPLACE(date ,' 00:00:00.0000000','')
 
 
 
 insert into wms.stockMinimum
SELECT newid(),
        l.locationID,
        i.itemID, 
 CAST('0'+replace(ZAPAS_MIN,',','.') AS decimal(18,3)),
 GETUTCDATE(),
 'sys'
FROM OPENQUERY(TETA, 

'   SELECT 
          INMA_ID,
          MAGA_ID,
          ZAPAS_MIN,
          ZAPAS_MAX
FROM TETA_ADMIN.AP_INMA_MAGA_ZAPASY where ZAPAS_MAX is not null or ZAPAS_MIN is not null
'
) x inner join wms.location l ON l.locationCode= cast(x.MAGA_ID as nvarchar(max))
INNER JOIN wms.item i ON i.externalItemID= x.INMA_ID


 
 
 
 
 
 
 dictionaryName IN  ('system.accountingAccountName','system.accountingVat') --AND adddate='2013-10-21 11:42:00'

 
 