USE [namedatabase];
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;

    DECLARE @Factor BIGINT = 42279;
    DECLARE @Sanad NVARCHAR(50) = N'171868';
    DECLARE @Order BIGINT = 26626;
    DECLARE @Person NVARCHAR(50) = N'11035';

    IF NOT EXISTS (
        SELECT 1 FROM tblForoosh
        WHERE fldShomareFactorForoosh = @Factor
          AND fldShomareSanad = CONVERT(BIGINT,@Sanad)
    )
        RAISERROR(N'فاکتور 42279 پیدا نشد.',16,1);

    IF NOT EXISTS (
        SELECT 1 FROM tblPishPishForoosh
        WHERE fldShomareFactorForoosh = @Order
    )
        RAISERROR(N'سفارش 26626 پیدا نشد.',16,1);

    DECLARE @OldSales DECIMAL(18,2);
    DECLARE @OldCommission DECIMAL(18,2);

    SELECT @OldSales = ISNULL(SUM(fldBestankar),0)
    FROM tblSodooreSanad
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'713';

    SELECT @OldCommission = ISNULL(SUM(fldBedehkar),0)
    FROM tblSodooreSanad
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'612';

    DELETE FROM tblRizeForoosh
    WHERE fldShomareFactor = @Factor;

    INSERT INTO tblRizeForoosh
    (
        fldShomareSanad, fldShomareFactor, fldCodeVasete,
        fldCodeTafsili, fldCodeUser, fldRadif, fldCodeAnbar,
        fldCodeKala, fldSharhKala, fldTedadCarton, fldTedadJoz,
        fldFeeJoz, fldFeeYekCarton, fldFeeKol,
        fldDarsadTakhfif, fldMablaghTakhfif,
        fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,
        fldArzeshAfzode, fldFeeKolBadAzTakhfif, fldMablaghKhales,
        fldType, fldDate, fldTime, fldShomareEnteghal,
        fldShomareBargasht, fldDateEngheza, fldShomareAmani,
        fldEshantion, fldDarsadArzeshAfzoode,
        fldShomareBargashtAmani, fldCodeMasterEshantion,
        fldDateUpdate, fldCodeVasete2, fldGhymatMasrafkonande,
        fldBatchNumber, fldDateSakht,
        fldDarsadTakhfifDarGhalam, fldMablaghTakhfifDarGhalam,
        fldDarsadTakhfifHajmi, fldMablaghTakhfifHajmi,
        fldDarsadTakhfifHajmiGoroohi,
        fldMablaghTakhfifHajmiGoroohi
    )
    SELECT
        CONVERT(BIGINT,@Sanad),
        @Factor,
        P.fldCodeVasete,
        P.fldCodeTafsili,
        P.fldCodeUser,
        P.fldRadif,
        P.fldCodeAnbar,
        P.fldCodeKala,
        P.fldSharhKala,
        P.fldTedadCarton,
        P.fldTedadJoz,
        P.fldFeeJoz,
        P.fldFeeYekCarton,
        P.fldFeeKol,
        P.fldDarsadTakhfif,
        P.fldMablaghTakhfif,
        P.fldFeeVahed1BadAzTakhfif,
        P.fldFeeVahed2BadAzTakhfif,
        P.fldArzeshAfzode,
        P.fldFeeKolBadAzTakhfif,
        P.fldMablaghKhales,
        P.fldType,
        P.fldDate,
        P.fldTime,
        P.fldShomareEnteghal,
        P.fldShomareBargasht,
        P.fldDateEngheza,
        NULL,
        NULL,
        CASE WHEN P.fldArzeshAfzode > 0 THEN 10 ELSE 0 END,
        NULL,
        P.fldCodeMasterEshantion,
        GETDATE(),
        0,
        0,
        N'0',
        NULL,
        0,
        0,
        0,
        0,
        0,
        0
    FROM tblRizePishPishForoosh P
    WHERE P.fldShomareFactor = @Order;

    DECLARE @Net DECIMAL(18,2);
    DECLARE @Vat DECIMAL(18,2);
    DECLARE @Gross DECIMAL(18,2);

    SELECT
        @Net = SUM(fldFeeKolBadAzTakhfif),
        @Vat = SUM(fldArzeshAfzode),
        @Gross = SUM(fldMablaghKhales)
    FROM tblRizeForoosh
    WHERE fldShomareFactor = @Factor;

    UPDATE tblSodooreSanad
    SET fldBedehkar = @Gross,
        fldBestankar = 0,
        fldSharh = N'شماره فاکتور 42279'
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'113'
      AND fldCodeMoin = N'01'
      AND fldCodeTafzili = @Person;

    UPDATE tblSodooreSanad
    SET fldBedehkar = 0,
        fldBestankar = @Net
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'713'
      AND fldCodeMoin = N'01';

    IF @OldSales > 0 AND @OldCommission > 0
    BEGIN
        DECLARE @NewCommission DECIMAL(18,2);
        SET @NewCommission = ROUND(@OldCommission * @Net / @OldSales, 0);

        UPDATE tblSodooreSanad
        SET fldBedehkar = @NewCommission
        WHERE fldSanad = @Sanad
          AND fldCodeKol = N'612';

        UPDATE tblSodooreSanad
        SET fldBestankar = @NewCommission
        WHERE fldSanad = @Sanad
          AND fldCodeKol = N'313'
          AND fldCodeMoin = N'03';
    END;

    DELETE FROM tblSodooreSanad
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'313'
      AND fldCodeMoin = N'02';

    INSERT INTO tblSodooreSanad
    (
        fldSanad, fldDay, fldMonth, fldYear,
        fldCodeKol, fldCodeMoin, fldCodeTafzili,
        fldBedehkar, fldBestankar, fldRadif,
        fldCodeSharh, fldDate, fldSharh,
        fldMoinKamel, fldCodeNoeTafzili, fldType
    )
    SELECT TOP 1
        @Sanad,
        fldDay,
        fldMonth,
        fldYear,
        N'313',
        N'02',
        N'',
        0,
        @Vat,
        N'2',
        N'',
        fldDate,
        N'ارزش افزوده فاکتور 42279',
        N'31302',
        N'4',
        N'foroosh'
    FROM tblSodooreSanad
    WHERE fldSanad = @Sanad
      AND fldCodeKol = N'113';

    IF (
        SELECT ROUND(SUM(fldBedehkar),2)
        FROM tblSodooreSanad
        WHERE fldSanad = @Sanad
    ) <>
    (
        SELECT ROUND(SUM(fldBestankar),2)
        FROM tblSodooreSanad
        WHERE fldSanad = @Sanad
    )
        RAISERROR(N'سند تراز نیست؛ عملیات لغو شد.',16,1);

    SELECT
        @Net AS مبلغ_خالص,
        @Vat AS ارزش_افزوده,
        @Gross AS مبلغ_نهایی,
        COUNT(*) AS تعداد_ریز_اقلام
    FROM tblRizeForoosh
    WHERE fldShomareFactor = @Factor;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK;

    DECLARE @Error NVARCHAR(4000) = ERROR_MESSAGE();
    RAISERROR(@Error,16,1);
END CATCH;