/****** Object:  StoredProcedure [dbo].[P_EST_PED_VD_IT_REPROCESSAR_FRETE_RATEIO]    Script Date: 04/08/2014 09:57:57 ******/

IF EXISTS(SELECT * FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PED_VD_IT_REPROCESSAR_FRETE_RATEIO]') AND type IN(N'P', N'PC')
)
    DROP PROCEDURE [dbo].[P_EST_PED_VD_IT_REPROCESSAR_FRETE_RATEIO];
GO

--Versao 2939
--EXEC P_EST_PED_VD_IT_REPROCESSAR_FRETE_RATEIO 0,0,12,3858,30.99

CREATE PROCEDURE [dbo].[P_EST_PED_VD_IT_REPROCESSAR_FRETE_RATEIO](@MES           INT
																, @ANO           INT
																, @CD_FILIAL_PAR INT   = 0
																, @CD_PED        INT   = 0
																, @VLR_FRETE     MONEY = 0
															)
WITH ENCRYPTION
AS
     DECLARE @CD_VD INT;
     DECLARE @CD_IT INT;
     DECLARE @TIRA_POE MONEY;
     DECLARE @TIRA_POE_LOOP MONEY;
     DECLARE @DT_INI DATETIME;
     DECLARE @DT_FIM DATETIME;
     DECLARE @DIA DATETIME;
     DECLARE @CD_FILIAL AS INT;
     DECLARE @AGORA DATETIME;
     DECLARE @CD_EMP INT;
     DECLARE @Abate MONEY;
     SET NOCOUNT ON;

     CREATE TABLE #Tbl(CD_EMP    INT , CD_FILIAL INT , CD_PED    INT, VLR_TOT_PROD MONEY, VLR_FRETE MONEY);


     SET @CD_EMP = 1;
-------------------------------------------------------------------------------------------------------------------------------------------
     IF @CD_PED = 0
         BEGIN
             IF ISNULL(@ANO,0) = 0 OR ISNULL(@MES,0) = 0
                 BEGIN
                     SELECT
                            @AGORA = DATEADD(month, -1, GETDATE())
                          , @ANO = YEAR(@AGORA)
                          , @MES = MONTH(@AGORA);
                 END;


             SET @DT_INI = CONVERT(DATETIME, (CONVERT(VARCHAR, (@ANO * 100 + @MES))+'01'));
             SET @DT_FIM = dbo.F_FINAL_MES(@DT_INI);


			 --Roda so ate hoje
             IF @DT_FIM > dbo.F_HOJE()
                 BEGIN
                     SET @DT_FIM = dbo.F_HOJE(); --1 
                 END;

             DECLARE cFilial CURSOR FAST_FORWARD READ_ONLY
             FOR SELECT
                        CD_FILIAL
                 FROM
                      PRC_FILIAL
                 WHERE CD_EMP = 1
                       AND CD_FILIAL = CASE
                                           WHEN @CD_FILIAL_PAR = 0 THEN CD_FILIAL
                                           ELSE @CD_FILIAL_PAR
                                       END;
             OPEN CFILIAL;
             FETCH NEXT FROM cFilial INTO @CD_FILIAL;
             WHILE @@FETCH_STATUS = 0
                 BEGIN
                     SET @DIA = @DT_INI;
                     WHILE @DT_FIM >= @DIA
                         BEGIN
                             PRINT 'Filial '+CONVERT(VARCHAR, @CD_FILIAL)+' -  Processando dia  '+CONVERT(VARCHAR, @DIA, 112)+' '+CONVERT(VARCHAR, GETDATE(), 108);

							 --Buscando vendas com diferenca
                             INSERT INTO #Tbl
                                SELECT
                                    EST_PED_VD.CD_EMP
                                    , EST_PED_VD.CD_FILIAL
                                    , EST_PED_VD.CD_PED
									, EST_PED_VD.VLR_TOT_PROD
									, EST_PED_VD.VLR_FRETE
                                FROM
                                        EST_PED_VD_IT
                                        INNER JOIN EST_PED_VD ON EST_PED_VD_IT.CD_EMP = EST_PED_VD.CD_EMP
                                                                AND EST_PED_VD_IT.CD_FILIAL = EST_PED_VD.CD_FILIAL
                                                                AND EST_PED_VD_IT.CD_PED = EST_PED_VD.CD_PED
                                WHERE EST_PED_VD.CD_EMP = @CD_EMP
                                        AND EST_PED_VD.CD_FILIAL = @CD_FILIAL
                                        AND EST_PED_VD.DT_EMI_PED = @DIA
                                        AND EST_PED_VD.VLR_FRETE > 0
                                GROUP BY
                                            EST_PED_VD.CD_EMP
                                        , EST_PED_VD.CD_FILIAL
                                        , EST_PED_VD.CD_PED
                                        , EST_PED_VD.VLR_FRETE
										, EST_PED_VD.VLR_TOT_PROD
										, EST_PED_VD.VLR_FRETE
                                HAVING SUM(EST_PED_VD_IT.VLR_PROD_FRETE) <> EST_PED_VD.VLR_FRETE;				
							--------------------------------------------------------------------------------------
							--Se tem movimento para processar no dia
							IF EXISTS(SELECT TOP 1 CD_EMP FROM #Tbl )
							BEGIN
								--PRINT ' Dia com valores:  ' + CONVERT(VARCHAR, @DIA, 112) + ' ' + CONVERT(VARCHAR, GETDATE (),108)
								--Zerando rateios
								UPDATE EST_PED_VD_IT
								SET
									EST_PED_VD_IT.VLR_PROD_FRETE = 0
								FROM EST_PED_VD_IT
									INNER JOIN #Tbl v ON EST_PED_VD_IT.CD_EMP = v.CD_EMP
														AND EST_PED_VD_IT.CD_FILIAL = v.CD_FILIAL
														AND EST_PED_VD_IT.CD_PED = v.CD_PED;
								--------------------------------------------------------------------------------------	
				
								--Atualizando vendas selecionadas
								UPDATE I
								SET
									I.VLR_PROD_FRETE = ROUND((((I.QT_IT * I.VLR_IT) - I.VLR_DESC) / V.VLR_TOT_PROD) * V.VLR_FRETE, 2)
								FROM 
									EST_PED_VD_IT I 
									INNER JOIN #Tbl v ON I.CD_EMP = v.CD_EMP
														AND I.CD_FILIAL = v.CD_FILIAL
														AND I.CD_PED = v.CD_PED;

								--------------------------------------------------------------------------------------
								--Fim 
								DECLARE cPedidoVendas CURSOR
								FOR SELECT
										EST_PED_VD_IT.CD_PED
										, EST_PED_VD.VLR_FRETE - SUM(EST_PED_VD_IT.VLR_PROD_FRETE)
									FROM
										EST_PED_VD_IT
										INNER JOIN EST_PED_VD ON EST_PED_VD_IT.CD_EMP = EST_PED_VD.CD_EMP
																AND EST_PED_VD_IT.CD_FILIAL = EST_PED_VD.CD_FILIAL
																AND EST_PED_VD_IT.CD_PED = EST_PED_VD.CD_PED
										INNER JOIN #Tbl v ON EST_PED_VD.CD_EMP = v.CD_EMP
															AND EST_PED_VD.CD_FILIAL = v.CD_FILIAL
															AND EST_PED_VD.CD_PED = v.CD_PED
									GROUP BY
											EST_PED_VD_IT.CD_PED
										, EST_PED_VD.VLR_FRETE  
									HAVING SUM(EST_PED_VD_IT.VLR_PROD_FRETE) <> EST_PED_VD.VLR_FRETE;
								OPEN cPedidoVendas;
								FETCH NEXT FROM cPedidoVendas INTO @CD_VD
																, @TIRA_POE ;
								WHILE @@FETCH_STATUS = 0
									BEGIN
										PRINT 'Corrigindo a venda '+CONVERT(VARCHAR, @CD_VD)+' Dif. '+CONVERT(VARCHAR, @TIRA_POE);
										SET @TIRA_POE_LOOP = @TIRA_POE;
										WHILE @TIRA_POE_LOOP <> 0
											BEGIN
												IF @tira_poe > 0
													SET @Abate = 0.01;
												IF @tira_poe < 0
													SET @Abate = -0.01;
												PRINT @Abate;

												SET @CD_IT =(SELECT TOP 1 CD_PROD FROM EST_PED_VD_IT
																			WHERE EST_PED_VD_IT.CD_EMP = @CD_EMP
																				  AND EST_PED_VD_IT.CD_FILIAL = @CD_FILIAL
																				  AND EST_PED_VD_IT.CD_PED = @CD_VD
																			ORDER BY  VLR_PROD_FRETE DESC
																		);

												UPDATE EST_PED_VD_IT
												SET
													EST_PED_VD_IT.VLR_PROD_FRETE = (VLR_PROD_FRETE + @Abate)
												WHERE
													EST_PED_VD_IT.CD_EMP = @CD_EMP
													AND EST_PED_VD_IT.CD_FILIAL = @CD_FILIAL
													AND EST_PED_VD_IT.CD_PED = @CD_VD
													AND EST_PED_VD_IT.CD_PROD = @CD_IT;

												SET @TIRA_POE_LOOP = (@TIRA_POE_LOOP - @Abate);

											END;
									
										FETCH NEXT FROM cPedidoVendas INTO @CD_VD , @TIRA_POE ;

										END;


                                    CLOSE cPedidoVendas;
                                    DEALLOCATE cPedidoVendas;
                                 END;

                             TRUNCATE TABLE #Tbl;
                             SELECT @DIA = @DIA + 1;

                         END;
                     FETCH NEXT FROM cFilial INTO @CD_FILIAL;
                 END;

             CLOSE cFilial;
             DEALLOCATE cFilial;
         END;
         ELSE
         BEGIN

            UPDATE EST_PED_VD
			SET
                   EST_PED_VD.VLR_FRETE = @VLR_FRETE
             WHERE
                   EST_PED_VD.CD_EMP = @CD_EMP
                   AND EST_PED_VD.CD_FILIAL = @CD_FILIAL_PAR
                   AND EST_PED_VD.CD_PED = @CD_PED;

			--Buscando vendas com diferenca
             INSERT INTO #Tbl
				SELECT
					  V.CD_EMP
					, V.CD_FILIAL
					, V.CD_PED
					, SUM((V.QT_IT * V.VLR_IT) - V.VLR_DESC)
					, @VLR_FRETE
				FROM
					EST_PED_VD_IT V
				WHERE V.CD_EMP = @CD_EMP
					AND V.CD_FILIAL = @CD_FILIAL_PAR
					AND V.CD_PED = @CD_PED 
				GROUP BY 
					V.CD_EMP, V.CD_FILIAL, V.CD_PED;
	 
             IF EXISTS (SELECT TOP 1 CD_EMP FROM #Tbl )
                 BEGIN
					 
                     UPDATE EST_PED_VD_IT
                       SET
                           EST_PED_VD_IT.VLR_PROD_FRETE = 0
                     FROM EST_PED_VD_IT
                          INNER JOIN #Tbl v ON EST_PED_VD_IT.CD_EMP = v.CD_EMP
                                               AND EST_PED_VD_IT.CD_FILIAL = v.CD_FILIAL
                                               AND EST_PED_VD_IT.CD_PED = v.CD_PED;
					UPDATE I 
						SET
							I.VLR_PROD_FRETE = ROUND((((I.QT_IT * I.VLR_IT) - I.VLR_DESC) / (v.VLR_TOT_PROD)) * v.VLR_FRETE, 2)
					FROM EST_PED_VD_IT I
							INNER JOIN #Tbl v ON I.CD_EMP = v.CD_EMP
												AND I.CD_FILIAL = v.CD_FILIAL
												AND I.CD_PED = v.CD_PED;

					--Fim 

                     DECLARE cPedidoVendas CURSOR
                     FOR SELECT
                                EST_PED_VD_IT.CD_PED
                              , EST_PED_VD.VLR_FRETE - SUM(EST_PED_VD_IT.VLR_PROD_FRETE)
							  , EST_PED_VD.CD_FILIAL
                         FROM
                              EST_PED_VD_IT
                              INNER JOIN EST_PED_VD ON EST_PED_VD_IT.CD_EMP = EST_PED_VD.CD_EMP
                                                       AND EST_PED_VD_IT.CD_FILIAL = EST_PED_VD.CD_FILIAL
                                                       AND EST_PED_VD_IT.CD_PED = EST_PED_VD.CD_PED
                              INNER JOIN #Tbl v ON EST_PED_VD.CD_EMP = v.CD_EMP
                                                   AND EST_PED_VD.CD_FILIAL = v.CD_FILIAL
                                                   AND EST_PED_VD.CD_PED = v.CD_PED
                         GROUP BY
                                  EST_PED_VD_IT.CD_PED
                                , EST_PED_VD.VLR_FRETE
								, EST_PED_VD.CD_FILIAL
                         HAVING SUM(EST_PED_VD_IT.VLR_PROD_FRETE) <> EST_PED_VD.VLR_FRETE;


                     OPEN cPedidoVendas;
                     FETCH NEXT FROM cPedidoVendas INTO  @CD_VD, @TIRA_POE, @CD_FILIAL  ;
                     WHILE @@FETCH_STATUS = 0
                         BEGIN
                             PRINT 'Corrigindo a venda '+CONVERT(VARCHAR, @CD_VD)+' Dif. '+CONVERT(VARCHAR, @TIRA_POE);
                             SET @TIRA_POE_LOOP = @TIRA_POE;
                             WHILE @TIRA_POE_LOOP <> 0.
                                 BEGIN
                                     IF @tira_poe > 0.
                                         SET @Abate = 0.01;
                                     IF @tira_poe < 0.
                                         SET @Abate = -0.01;

                                     PRINT @Abate;  
                                     SET @CD_IT = ( SELECT TOP 1 CD_PROD FROM EST_PED_VD_IT
														WHERE EST_PED_VD_IT.CD_EMP = @CD_EMP
																AND EST_PED_VD_IT.CD_FILIAL = @CD_FILIAL
																AND EST_PED_VD_IT.CD_PED = @CD_VD
														ORDER BY VLR_PROD_FRETE DESC ); 

                                     UPDATE EST_PED_VD_IT
                                     SET
                                           EST_PED_VD_IT.VLR_PROD_FRETE = (VLR_PROD_FRETE + @Abate) 
                                     WHERE
                                           EST_PED_VD_IT.CD_EMP = @CD_EMP
                                           AND EST_PED_VD_IT.CD_FILIAL	= @CD_FILIAL
                                           AND EST_PED_VD_IT.CD_PED		= @CD_VD
                                           AND EST_PED_VD_IT.CD_PROD	= @CD_IT;

                                     SET @TIRA_POE_LOOP = (@TIRA_POE_LOOP - @Abate);

                                 END;
                             FETCH NEXT FROM cPedidoVendas INTO @CD_VD, @TIRA_POE, @CD_FILIAL ;
                         END;
                     CLOSE cPedidoVendas;
                     DEALLOCATE cPedidoVendas;
                 END;
         END;