-- =============================================
-- Author:		Steamware
-- Mod. date: 2014.05.22
-- Description:	Procedura per conversione e tipizzazione dati da tmp a ext x OrdersHistRaw
-- =============================================
CREATE PROCEDURE [tmp].[convertOrdersHistRaw]
(
     @fileType NVARCHAR(50) = 'OrdersHistRaw'
)
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 = 'OrdersHistRaw'
        BEGIN
            -- svuoto tabella temp
            TRUNCATE TABLE ext.OrdersHistRaw

            -- inserisco con cast valori...
            INSERT ext.OrdersHistRaw
            WITH (TABLOCK)
            ( OrdNum, OrdRow, OrdDate, CodClient, CodItem, RawMat, RawMatCost, BatchQty, CodInco, OrdPrice, RawMatExtraCost, PriceOff, OrdQty, ProvvCost) 
            SELECT
                    OrdNum,
                    OrdRow,
                    dbo.f_dtFromRaw( OrdDate ) AS OrdDate,
                    CodClient,
                    CodItem, 
                    RawMat, 
                    CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(RawMatCost,'.',''),',','.' ) ))      AS RawMatCost,
                    CONVERT(INT, CONVERT ( FLOAT , REPLACE( '0' + REPLACE(BatchQty,'.',''),',','.' ) ))                     AS BatchQty,         -- lotto
                    CodInco, 
                    CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(OrdPrice,'.',''),',','.' ) ))        AS OrdPrice,
                    CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(RawMatExtraCost,'.',''),',','.' ) )) AS RawMatExtraCost,
                    CONVERT( DECIMAL( 9,6 ),  CONVERT ( FLOAT , REPLACE( '0' + REPLACE(PriceOff,'.',''),',','.' ) ))        AS PriceOff,         -- trasformazione
                    CONVERT( INT,             CONVERT ( FLOAT , REPLACE( '0' + REPLACE(OrdQty,'.',''),',','.' ) ))          AS OrdQty,
                    CONVERT( DECIMAL( 9,6 ),  CONVERT ( FLOAT , REPLACE( '0' + REPLACE(ProvvCost,'.',''),',','.' ) ))       AS ProvvCost         -- provvigioni
            FROM tmp.OrdersHistRaw;

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

END