-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2013.11.28
-- Description:	Procedura per import dati in tabelal tmp da file csv
-- 
-- TRUNCATE TABLE tmp.ProductionLogRaw_KO
--
-- =============================================
CREATE PROCEDURE [tmp].[importProductionLogRaw]
(
	 @fileType NVARCHAR(50) = 'ProductionLogRaw'
)
AS
BEGIN
	SET NOCOUNT ON;

	-- per Giancarlo: rendere parametrico con esecuzione sql x poter inserire nei parametri il separatore ed il resto? http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
	
	IF @fileType = 'ProductionLogRaw'
		BEGIN
			-- svuoto tabella temp
			TRUNCATE TABLE tmp.ProductionLogRaw

			-- carico dati bulk!
			BULK INSERT tmp.ProductionLogRaw
			FROM 'e:\test\ProductRaw.csv'
			
			WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, ERRORFILE = 'e:\test\errorProduct.log')
			--WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, ERRORFILE = 'e:\test\errorProduct.log', CODEPAGE = '1252')

            -- ===============================================================================
            -- pulisco l tabella dai record con chiave duplicata ( se doppi li elimino tutti )
            -- ===============================================================================
            SET XACT_ABORT ON;
            BEGIN TRAN 

                -- salvo i dati con chiave duplicata
                INSERT INTO tmp.ProductionLogRaw_KO
                SELECT Prod.*, GETDATE() FROM tmp.ProductionLogRaw AS Prod
                INNER JOIN 
                            (
                            SELECT EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
                            FROM tmp.ProductionLogRaw
                            GROUP BY  EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
                            HAVING COUNT(*)>1
                            ) AS Dupl ON    Prod.EventStart  = Dupl.EventStart AND
                                            Prod.EventEnd    = Dupl.EventEnd AND
                                            Prod.CodPlant    = Dupl.CodPlant AND
                                            Prod.ProcessNum  = Dupl.ProcessNum AND
                                            Prod.NrPos       = Dupl.NrPos  AND
                                            Prod.CodDies     = Dupl.CodDies

                -- Cancello i record con chiave duplicata
                DELETE Prod
                WITH (TABLOCK)
                FROM tmp.ProductionLogRaw AS Prod
                INNER JOIN 
                            (
                            SELECT EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
                            FROM tmp.ProductionLogRaw
                            GROUP BY  EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
                            HAVING COUNT(*)>1
                            ) AS Dupl ON    Prod.EventStart  = Dupl.EventStart AND
                                            Prod.EventEnd    = Dupl.EventEnd AND
                                            Prod.CodPlant    = Dupl.CodPlant AND
                                            Prod.ProcessNum  = Dupl.ProcessNum AND
                                            Prod.NrPos       = Dupl.NrPos  AND
                                            Prod.CodDies     = Dupl.CodDies

            -- ===============================================================================
            -- pulisco la tabella dai record con date non corrette
            -- ===============================================================================
            DELETE FROM tmp.ProductionLogRaw
            WITH (TABLOCK)
            OUTPUT deleted.* INTO tmp.ProductionLogRaw_KO    -- salvo i record cancellati in tabella KO
                   ( EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies, CodClient, OrderNum, BatchNum, ProcessType, EventType, RawMat
                   , CodPackag, QtyIN, QtyOUT, QtyEXT, NumIN, NumOUT, QuotaPlant, QuotaMan, Tags, okPr, okQM, okSc, okDi )
            WHERE EventStart >= EventEnd
                  OR CONVERT( BIGINT , EventStart ) + 68000000000000 <= EventEnd   -- record che darebbero errore poi nel datediff > 68 anni
                  OR EventStart IS NULL
                  OR EventEnd   IS NULL

--            SELECT * FROM tmp.ProductionLogRaw_KO

            --ROLLBACK
            COMMIT

			SELECT COUNT(*) AS RecordsIn FROM tmp.ProductionLogRaw

		END
END