-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2013.11.28
-- Description:	Procedura per conversioen e tipizzazione dati da tmp a ext x ProductionLogRaw
-- =============================================
CREATE PROCEDURE [tmp].[convertProductionLogRaw]
(
	 @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 ext.ProductionLogRaw

			-- inserisco con cast valori...
			INSERT ext.ProductionLogRaw
            WITH (TABLOCK)
            ( 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 )

            SELECT
                   dbo.f_dtFromRaw( EventStart ) AS EventStart,  -- record nulli sono già stati cancellati
                   dbo.f_dtFromRaw( EventEnd )   AS EventEnd,    -- record nulli sono già stati cancellati
                   dbo.f_trim(ISNULL(CodPlant   ,'')),
                   dbo.f_trim(ISNULL(ProcessNum ,'')),
                   dbo.f_trim(ISNULL(NrPos      ,'')),
                   dbo.f_trim(ISNULL(CodDies    ,'')),
                   dbo.f_trim(ISNULL(CodClient  ,'')),
                   dbo.f_trim(ISNULL(OrderNum   ,'')),
                   dbo.f_trim(ISNULL(BatchNum   ,'')),
                   dbo.f_trim(ISNULL(ProcessType,'')),
                   dbo.f_trim(ISNULL(EventType  ,'')),
                   dbo.f_trim(ISNULL(RawMat     ,'')),
                   dbo.f_trim(ISNULL(CodPackag  ,'')),
                   CAST( CAST( REPLACE( '0' + ISNULL(QtyIN,'') ,',','.' ) AS float )  AS decimal( 18,4 ))  AS QtyIN,
                   CAST( CAST( REPLACE( '0' + ISNULL(QtyOUT,''),',','.' ) AS float ) AS decimal( 18,4 ))   AS QtyOUT,
                   CAST( CAST( REPLACE( '0' + ISNULL(QtyEXT,''),',','.' ) AS float ) AS decimal( 18,4 ))   AS QtyEXT,
                   CAST( ISNULL(NumIN   ,'') AS int )AS NumIN,
                   CAST( ISNULL(NumOUT  ,'') AS int )AS NumOUT,
                   CAST( CAST( REPLACE( '0' + ISNULL(QuotaPlant,''),',','.' ) AS float )AS decimal( 9,4 )) AS QuotaPlant,
                   CAST( CAST( REPLACE( '0' + ISNULL(QuotaMan,''),',','.' )   AS float )AS decimal( 9,4 )) AS QuotaMan,
                   dbo.f_trim(ISNULL(Tags  ,'')),
				   okPr,
				   okQM,
				   okSc,
				   okDi
            FROM tmp.ProductionLogRaw;

			SELECT COUNT(*)  as RecordsIn FROM ext.ProductionLogRaw
		END

END