use sales1390
go
ALTER TABLE [dbo].[review] ADD   [salescode]   INT    NULL;
go
ALTER TABLE [dbo].[review] ADD   [mablag]      INT    NULL;
go
ALTER TABLE [dbo].[review] ADD   [ghestnumber] INT    NULL;
go
ALTER TABLE [dbo].[review] ADD [ScheduleID] INT    NULL;
GO

ALTER TABLE [dbo].[usergroup]
DROP CONSTRAINT [PK_usergroup];
GO


ALTER TABLE [dbo].[usergroup]  ALTER COLUMN [groupname]   NVARCHAR(50)  NOT NULL;
go
ALTER TABLE [dbo].[usergroup]  ALTER COLUMN [optionallow] NVARCHAR(200) NOT NULL;
GO

ALTER TABLE [dbo].[usergroup]
ADD CONSTRAINT [PK_usergroup] PRIMARY KEY CLUSTERED 
(
    [groupname]   ASC,
    [optionallow] ASC
)
WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90
) ON [PRIMARY];
GO

INSERT INTO [dbo].[usergroup] ([groupname], [optionallow])
VALUES (N'مديرفروش', N'گزارش فروش اقساطي');
GO

IF NOT EXISTS (
    SELECT 1
    FROM [dbo].[usergroup]
    WHERE [groupname] = N'مديرفروش'
      AND [optionallow] = N'گزارش فروش اقساطي'
)
BEGIN
    INSERT INTO [dbo].[usergroup] ([groupname], [optionallow])
    VALUES (N'مديرفروش', N'گزارش فروش اقساطي');
END;
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = N'ghest'
      AND s.name = N'dbo'
)
BEGIN
    CREATE TABLE [dbo].[ghest](
        [ScheduleID]   INT            IDENTITY(1,1) NOT NULL,
        [salescode]    INT            NOT NULL,
        [customercode] INT            NOT NULL,
        [totalamount]  NUMERIC(19,4)  NOT NULL,
        [ghestnumber]  INT            NOT NULL,
        [ghestdate]    INT            NOT NULL,
        [amount]       NUMERIC(19,4)  NOT NULL,
        [Status]       NVARCHAR(20)   NOT NULL,
        CONSTRAINT [PK_ghest] PRIMARY KEY CLUSTERED ([ScheduleID] ASC)
        WITH (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
    ) ON [PRIMARY];
END;
GO
ALTER TABLE [dbo].[company]
ADD 
[adar] int   DEFAULT (1312) ,
[apar] int   DEFAULT (1412) 
GO

ALTER TABLE [dbo].[ghest]  ALTER COLUMN [cash]    NUMERIC(19,4)  NOT NULL;
go
DECLARE @codemoen_1312 INT;
DECLARE @codemoen_1412 INT;

-- بررسی وجود حساب دریافتنی
SELECT @codemoen_1312 = codemoen 
FROM moen 
WHERE description = N'حساب هاي دريافتني';

-- اگر وجود نداشت، ایجادش کن
IF @codemoen_1312 IS NULL
BEGIN
    SELECT @codemoen_1312 = ISNULL((SELECT MAX(codemoen) + 1 FROM moen WHERE codemoen BETWEEN 1300 AND 1399), 1312);

    INSERT INTO moen (codemoen, description, typeaccount)
    VALUES (@codemoen_1312, N'حساب هاي دريافتني', 13);
END

-- بررسی وجود حساب پرداختنی
SELECT @codemoen_1412 = codemoen 
FROM moen 
WHERE description = N'حساب هاي پرداختني';

-- اگر وجود نداشت، ایجادش کن
IF @codemoen_1412 IS NULL
BEGIN
    SELECT @codemoen_1412 = ISNULL((SELECT MAX(codemoen) + 1 FROM moen WHERE codemoen BETWEEN 1400 AND 1499), 1412);

    INSERT INTO moen (codemoen, description, typeaccount)
    VALUES (@codemoen_1412, N'حساب هاي پرداختني', 14);
END

-- به‌روزرسانی سند فقط در صورتی که قبلاً مقدار نداشته باشد
UPDATE sanad 
SET codemoen = @codemoen_1312 
WHERE codecustomer > 0 AND bed > 0;

UPDATE sanad 
SET codemoen = @codemoen_1412 
WHERE codecustomer > 0 AND best > 0 ;

-- فقط اگر فیلد مرتبط در company مقدار نداشته باشد
UPDATE company
SET adar = @codemoen_1312


UPDATE company
SET apar = @codemoen_1412
update kol set sharh=N'حساب های دریافتنی' where codekol=13
update kol set sharh=N'حساب های پرداختنی' where codekol=14
go
ALTER TABLE ghest ADD cash NUMERIC(38,4);
go
/****** Object:  StoredProcedure [dbo].[deletereviewcheck]    Script Date: 05/21/2025 12:41:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletereviewcheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[deletereviewcheck]
GO

CREATE PROCEDURE [dbo].[deletereviewcheck]
AS 
BEGIN
    DELETE FROM dbo.review WHERE serialcheck > 0;
END


go
ALTER PROCEDURE [dbo].[reviewcheck]
AS 
BEGIN
    INSERT INTO dbo.review
        (sharh, state, reviewdate, [check], serialcheck,customercode)
    SELECT 
        'چک شماره[' + RTRIM(dbo.[check].checkcode) + '] به تاريخ[' + 
        SUBSTRING(STR(dbo.[check].checkdate, 8), 1, 4) + '/' + 
        SUBSTRING(STR(dbo.[check].checkdate, 8), 5, 2) + '/' + 
        SUBSTRING(STR(dbo.[check].checkdate, 8), 7, 2) + 
        ']به مبلغ[' + STR(dbo.[check].mablag) + '] به نام ' + 
        RTRIM(dbo.customer.customername) + '   ' + RTRIM(dbo.[check].bankname),
        CASE dbo.[CHECK].type WHEN 'دريافتني' THEN 'دريافتني پاس نشده' ELSE 'پرداختني پاس نشده' END,
        dbo.[check].checkdate,
        1, -- check = true
        dbo.[check].serial,[check].customercode
    FROM dbo.[check]
    INNER JOIN dbo.customer ON dbo.[check].customercode = dbo.customer.customercode
    WHERE dbo.[check].serial IN (SELECT serial FROM checknotpass)
    AND dbo.[check].serial NOT IN (SELECT serial FROM checkvagozar)
END
go

GO
ALTER TABLE kala ADD endingstock NUMERIC(38,4);
ALTER TABLE kala ADD endingprice NUMERIC(24,6);
ALTER TABLE kala ADD endingvalue NUMERIC(38,2);

/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 05/21/2025 12:35:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbuykala]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbuykala]
GO


/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 05/21/2025 12:35:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


GO
/****** Object:  StoredProcedure [dbo].[deletereviewcheck]    Script Date: 05/21/2025 12:41:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletereviewcheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[deletereviewcheck]
GO





CREATE PROCEDURE [dbo].[deletereviewcheck]
AS 
BEGIN
    DELETE FROM dbo.review WHERE serialcheck > 0;
END

GO
/****** Object:  StoredProcedure [dbo].[deletereviewghest]    Script Date: 05/21/2025 12:41:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletereviewghest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[deletereviewghest]
GO




CREATE PROCEDURE [dbo].[deletereviewghest]
AS
BEGIN
    DELETE FROM dbo.review WHERE ScheduleID > 0;
END

GO
/****** Object:  StoredProcedure [dbo].[deletereviewghest]    Script Date: 05/21/2025 14:20:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletereviewghest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[deletereviewghest]
GO

CREATE PROCEDURE [dbo].[deletereviewghest]
AS
BEGIN
    DELETE FROM dbo.review WHERE ScheduleID > 0 
END


GO
/****** Object:  StoredProcedure [dbo].[reviewghest]    Script Date: 05/21/2025 14:21:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reviewghest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[reviewghest]
GO



CREATE PROCEDURE [dbo].[reviewghest]
AS
BEGIN
    INSERT INTO dbo.review
        (sharh, state, reviewdate, [check], customercode, salescode, mablag, ghestnumber, ScheduleID)
    SELECT 
        'قسط شماره ' + CAST(g.ghestnumber AS nvarchar) + ' به تاریخ [' + 
        SUBSTRING(STR(g.ghestdate, 8), 1, 4) + '/' + 
        SUBSTRING(STR(g.ghestdate, 8), 5, 2) + '/' + 
        SUBSTRING(STR(g.ghestdate, 8), 7, 2) + 
        '] به مبلغ [' + STR(g.amount, 19, 0) + '] برای مشتری ' + RTRIM(c.customername),
        'قسط پرداخت نشده',
        g.ghestdate,
        0, -- check = false
        g.customercode,
        g.salescode,
        g.amount,
        g.ghestnumber,
        g.ScheduleID
    FROM dbo.ghest g
    INNER JOIN dbo.customer c ON g.customercode = c.customercode
    AND NOT EXISTS (
        SELECT 1 FROM dbo.review r 
        WHERE r.ScheduleID = g.ScheduleID and (r.ScheduleID IS NOT NULL)
    ) and g.Status <>N'پرداخت شده'
END

GO
ALTER PROCEDURE [dbo].[sortsanad]
AS 
WITH SortedRecords AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY sdate) AS ncodesanad,
        sdate,
        [year]
    FROM 
        sanad group by sdate,year
    having [year] = (SELECT RIGHT(DB_NAME(), 4) )  -- فقط رکوردهایی که سال آن‌ها برابر بیشترین سال است
)
UPDATE sanad
SET codesanad = ncodesanad
FROM SortedRecords
WHERE sanad.sdate = SortedRecords.sdate
AND sanad.[year] = SortedRecords.[year];  -- مطابقت سال در جدول اصلی و رکوردهای مرتب شده
go
/****** Object:  Table [dbo].[RemainingLots]    Script Date: 05/24/2025 21:03:45 ******/
IF  Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemainingLots]') AND type in (N'U'))


CREATE TABLE [dbo].[RemainingLots](
	[LotID] [int] IDENTITY(1,1) NOT NULL,
	[codekala] [int] NOT NULL,
	[fdate] [int] NULL,
	[quantity] [numeric](38, 4) NOT NULL,
	[unit_price] [numeric](38, 6) NOT NULL,
	[remaining_quantity] [numeric](38, 4) NOT NULL,
	[fyear] [int] NOT NULL,
	[transaction_type] [varchar](20) NULL,
 CONSTRAINT [PK__Remainin__4160EF4D550B8C31] PRIMARY KEY CLUSTERED 
(
	[LotID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE dbo.kala ADD cogs DECIMAL(18, 2) NULL;
go
/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 06/02/2025 12:09:47 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbuykala]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbuykala]
GO

create PROCEDURE [dbo].[sbuykala](@todate INT = 99999999)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @db_name SYSNAME;
    SET @db_name = DB_NAME();
    DECLARE @fyear INT;
    SET @fyear = CAST(RIGHT(@db_name, 4) AS INT);

    IF @fyear NOT BETWEEN 1300 AND 1500
    BEGIN
        RAISERROR(N'نام دیتابیس حاوی یک سال مالی معتبر نیست.', 16, 1);
        RETURN;
    END

    -- Drop Temp Tables
    IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL DROP TABLE #Transactions;
    IF OBJECT_ID('tempdb..#PurchaseLots') IS NOT NULL DROP TABLE #PurchaseLots;
    IF OBJECT_ID('tempdb..#NegativeSales') IS NOT NULL DROP TABLE #NegativeSales;
    IF OBJECT_ID('tempdb..#KalaState') IS NOT NULL DROP TABLE #KalaState;
    IF OBJECT_ID('tempdb..#FIFO_Results') IS NOT NULL DROP TABLE #FIFO_Results;

    -- Create Temp Tables
    CREATE TABLE #Transactions (RowNum INT IDENTITY(1,1) PRIMARY KEY, codekala INT NOT NULL, transaction_type VARCHAR(20) NOT NULL, quantity DECIMAL(18,4) NOT NULL, unit_price DECIMAL(18,6) NOT NULL, fdate INT NOT NULL);
    CREATE TABLE #PurchaseLots (LotID INT IDENTITY(1,1) PRIMARY KEY, codekala INT NOT NULL, fdate INT NOT NULL, quantity DECIMAL(18,4) NOT NULL, unit_price DECIMAL(18,6) NOT NULL, remaining_quantity DECIMAL(18,4) NOT NULL, transaction_type VARCHAR(20) NOT NULL);
    CREATE TABLE #NegativeSales (kalacode INT PRIMARY KEY, quantity DECIMAL(18,4) NOT NULL);
    CREATE TABLE #KalaState (kalacode INT PRIMARY KEY, current_quantity DECIMAL(18,4) NOT NULL DEFAULT 0, total_input DECIMAL(18,4) NOT NULL DEFAULT 0, total_output DECIMAL(18,4) NOT NULL DEFAULT 0, total_cogs DECIMAL(18,2) NOT NULL DEFAULT 0);
    CREATE TABLE #FIFO_Results (codekala INT PRIMARY KEY, endingstock DECIMAL(18,4) NOT NULL, endingprice DECIMAL(18,6) NOT NULL, endingvalue DECIMAL(18,2) NOT NULL);

    CREATE NONCLUSTERED INDEX IX_Transactions_Kala_Date ON #Transactions (codekala, fdate, RowNum);
    CREATE NONCLUSTERED INDEX IX_PurchaseLots_Kala_Date ON #PurchaseLots (codekala, fdate, LotID);

    -- Initial Stock
    INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type) SELECT codekala, fdate, quantity, unit_price, remaining_quantity, 'LOT_CARRY_FORWARD' FROM dbo.RemainingLots WHERE fyear = @fyear - 1;
    INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type) SELECT k.kalacode, 0, ISNULL(k.firststock, 0), ISNULL(k.firstprice, 0), ISNULL(k.firststock, 0), 'INITIAL_STOCK' FROM dbo.kala k WHERE NOT EXISTS (SELECT 1 FROM dbo.RemainingLots rl WHERE rl.codekala = k.kalacode AND rl.fyear = @fyear - 1) AND ISNULL(k.firststock, 0) > 0;

    -- Initialize KalaState for ALL items
    INSERT INTO #KalaState (kalacode) SELECT kalacode FROM dbo.kala;
    UPDATE ks SET ks.current_quantity = ISNULL(ps.initial_stock, 0), ks.total_input = ISNULL(ps.initial_stock, 0) FROM #KalaState ks INNER JOIN (SELECT codekala, SUM(remaining_quantity) AS initial_stock FROM #PurchaseLots GROUP BY codekala) AS ps ON ks.kalacode = ps.codekala;

    -- Load Transactions
    INSERT INTO #Transactions (codekala, transaction_type, quantity, unit_price, fdate)
    SELECT d.codekala, CASE WHEN h.buycode > 0 THEN 'PURCHASE' WHEN h.bbuycode > 0 THEN 'PURCHASE_RETURN' WHEN h.salescode > 0 THEN 'SALE' WHEN h.bsalescode > 0 THEN 'SALE_RETURN' END, d.number, d.fe, h.fdate
    FROM dbo.dfactor d INNER JOIN dbo.hfactor h ON d.factorcode = h.factorcode
    WHERE (h.buycode > 0 OR h.bbuycode > 0 OR h.salescode > 0 OR h.bsalescode > 0) AND h.fdate <= @todate AND d.sum > 0 AND d.codekala IS NOT NULL
    ORDER BY h.fdate, ISNULL(h.ftime, 0), CASE WHEN h.buycode > 0 THEN 1 WHEN h.bbuycode > 0 THEN 2 WHEN h.salescode > 0 THEN 3 ELSE 4 END;

    DECLARE @RowNum INT = 1, @MaxRows INT; SELECT @MaxRows = ISNULL(MAX(RowNum), 0) FROM #Transactions;
    DECLARE @codekala INT, @type VARCHAR(20), @qty DECIMAL(18,4), @price DECIMAL(18,6), @date INT;

    WHILE @RowNum <= @MaxRows
    BEGIN
        SELECT @codekala = codekala, @type = transaction_type, @qty = quantity, @price = unit_price, @date = fdate FROM #Transactions WHERE RowNum = @RowNum;

        IF @type = 'PURCHASE'
        BEGIN
            -- Logic for PURCHASE
            DECLARE @purchase_original_qty DECIMAL(18,4) = @qty;
            IF EXISTS (SELECT 1 FROM #NegativeSales WHERE kalacode = @codekala)
            BEGIN
                DECLARE @pending DECIMAL(18,4);
                SELECT @pending = quantity FROM #NegativeSales WHERE kalacode = @codekala;
                DECLARE @use DECIMAL(18,4) = CASE WHEN @qty > @pending THEN @pending ELSE @qty END;
                UPDATE #NegativeSales SET quantity = quantity - @use WHERE kalacode = @codekala;
                IF (SELECT quantity FROM #NegativeSales WHERE kalacode = @codekala) <= 0 DELETE FROM #NegativeSales WHERE kalacode = @codekala;
                SET @qty = @qty - @use;
            END
            UPDATE #KalaState SET current_quantity = current_quantity + @purchase_original_qty, total_input = total_input + @purchase_original_qty WHERE kalacode = @codekala;
            IF @qty > 0 INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type) VALUES (@codekala, @date, @qty, @price, @qty, @type);
        END
        ELSE IF @type = 'PURCHASE_RETURN'
        BEGIN
            -- Logic for PURCHASE_RETURN
            DECLARE @return_remaining DECIMAL(18,4) = @qty, @lot_id INT, @lot_rem DECIMAL(18,4);
            WHILE @return_remaining > 0 AND EXISTS (SELECT 1 FROM #PurchaseLots WHERE codekala = @codekala AND remaining_quantity > 0)
            BEGIN
                SELECT TOP 1 @lot_id = LotID, @lot_rem = remaining_quantity FROM #PurchaseLots WHERE codekala = @codekala AND remaining_quantity > 0 ORDER BY fdate, LotID;
                DECLARE @use_return DECIMAL(18,4) = CASE WHEN @lot_rem > @return_remaining THEN @return_remaining ELSE @lot_rem END;
                UPDATE #PurchaseLots SET remaining_quantity = remaining_quantity - @use_return WHERE LotID = @lot_id;
                SET @return_remaining = @return_remaining - @use_return;
            END
            UPDATE #KalaState SET current_quantity = current_quantity - @qty, total_input = total_input - @qty WHERE kalacode = @codekala;
        END
        ELSE IF @type = 'SALE'
        BEGIN
            -- <<<<<<< UPDATED SALE BLOCK / بلوک فروش اصلاح شده >>>>>>>
            DECLARE @sale_remaining DECIMAL(18,4) = @qty;
            DECLARE @cogs DECIMAL(18,2) = 0, @lot_price DECIMAL(18,6);

            -- Consume from existing lots
            WHILE @sale_remaining > 0 AND EXISTS (SELECT 1 FROM #PurchaseLots WHERE codekala = @codekala AND remaining_quantity > 0)
            BEGIN
                SELECT TOP 1 @lot_id = LotID, @lot_rem = remaining_quantity, @lot_price = unit_price FROM #PurchaseLots WHERE codekala = @codekala AND remaining_quantity > 0 ORDER BY fdate, LotID;
                DECLARE @use_sale DECIMAL(18,4) = CASE WHEN @lot_rem > @sale_remaining THEN @sale_remaining ELSE @lot_rem END;
                SET @cogs = @cogs + (@use_sale * @lot_price);
                UPDATE #PurchaseLots SET remaining_quantity = remaining_quantity - @use_sale WHERE LotID = @lot_id;
                SET @sale_remaining = @sale_remaining - @use_sale;
            END

            -- Handle sales without enough stock
            IF @sale_remaining > 0
            BEGIN
                -- Register the quantity deficit
                IF NOT EXISTS (SELECT 1 FROM #NegativeSales WHERE kalacode = @codekala)
                    INSERT INTO #NegativeSales VALUES (@codekala, @sale_remaining);
                ELSE
                    UPDATE #NegativeSales SET quantity = quantity + @sale_remaining WHERE kalacode = @codekala;

                -- <<<<< NEW LOGIC / منطق جدید >>>>>
                -- Calculate a provisional COGS for the deficit
                DECLARE @provisional_price DECIMAL(18,6);
                SELECT @provisional_price = ISNULL(firstprice, 0) FROM dbo.kala WHERE kalacode = @codekala;
                SET @cogs = @cogs + (@sale_remaining * @provisional_price);
            END

            -- Update the final state
            UPDATE #KalaState SET current_quantity = current_quantity - @qty, total_output = total_output + @qty, total_cogs = total_cogs + @cogs WHERE kalacode = @codekala;
        END
        ELSE IF @type = 'SALE_RETURN'
        BEGIN
            -- Logic for SALE_RETURN
            DECLARE @effective_return_price DECIMAL(18,6), @fallback_price DECIMAL(18,6);
            SELECT TOP 1 @effective_return_price = unit_price FROM #Transactions WHERE codekala = @codekala AND transaction_type = 'PURCHASE' AND fdate <= @date ORDER BY fdate DESC, RowNum DESC;
            SELECT @fallback_price = ISNULL(firstprice, 0) FROM dbo.kala WHERE kalacode = @codekala;
            SET @effective_return_price = ISNULL(@effective_return_price, @fallback_price);
            INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type) VALUES (@codekala, @date, @qty, @effective_return_price, @qty, @type);
            UPDATE #KalaState SET current_quantity = current_quantity + @qty, total_output = total_output - @qty WHERE kalacode = @codekala;
        END

        SET @RowNum = @RowNum + 1;
    END

    -- Final cleanup and calculations...
    DELETE FROM #PurchaseLots WHERE remaining_quantity <= 0;
    DELETE FROM dbo.RemainingLots WHERE fyear = @fyear;
    INSERT INTO dbo.RemainingLots (codekala, fyear, fdate, quantity, unit_price, remaining_quantity, transaction_type) SELECT codekala, @fyear, fdate, quantity, unit_price, remaining_quantity, transaction_type FROM #PurchaseLots WHERE remaining_quantity > 0;

    WITH FinalData AS (
        SELECT
            k.kalacode,
            ISNULL(s.current_quantity, 0) AS endingstock,
            CASE
                WHEN ISNULL(s.current_quantity, 0) > 0 THEN ISNULL(p.positive_value, 0) / NULLIF(p.positive_stock, 0)
                WHEN ISNULL(s.current_quantity, 0) < 0 THEN ISNULL(lpp.unit_price, k.firstprice)
                ELSE 0
            END AS endingprice,
            ISNULL(s.total_cogs, 0) AS cogs,
            ISNULL(pt.total_input, 0) AS numberinput,
            ISNULL(pt.total_output, 0) AS numberoutput,
            ISNULL(lpp.unit_price, k.firstprice) as buy
        FROM
            dbo.kala k
            LEFT JOIN #KalaState s ON k.kalacode = s.kalacode
            LEFT JOIN (SELECT codekala, SUM(remaining_quantity) AS positive_stock, SUM(remaining_quantity * unit_price) AS positive_value FROM #PurchaseLots GROUP BY codekala) p ON k.kalacode = p.codekala
            LEFT JOIN (SELECT codekala, unit_price, ROW_NUMBER() OVER (PARTITION BY codekala ORDER BY fdate DESC, RowNum DESC) AS rn FROM #Transactions WHERE transaction_type = 'PURCHASE') lpp ON k.kalacode = lpp.codekala AND lpp.rn = 1
            LEFT JOIN (SELECT codekala, SUM(CASE WHEN transaction_type IN ('PURCHASE','SALE_RETURN') THEN quantity ELSE 0 END) AS total_input, SUM(CASE WHEN transaction_type IN ('SALE','PURCHASE_RETURN') THEN quantity ELSE 0 END) AS total_output FROM #Transactions GROUP BY codekala) pt ON k.kalacode = pt.codekala
    )
    UPDATE k
    SET
        k.endingstock = fd.endingstock,
        k.endingprice = ISNULL(fd.endingprice, 0),
        k.endingvalue = ISNULL(fd.endingstock, 0) * ISNULL(fd.endingprice, 0),
        k.cogs = ISNULL(fd.cogs, 0),
        k.buy = ISNULL(fd.buy, 0),
        k.numberinput = ISNULL(fd.numberinput, 0),
        k.numberoutput = ISNULL(fd.numberoutput, 0)
    FROM
        dbo.kala k
    INNER JOIN
        FinalData fd ON k.kalacode = fd.kalacode;
END;



GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[mojodi_aval]'))
DROP VIEW [dbo].[mojodi_aval]
GO


CREATE VIEW [dbo].[mojodi_aval]
AS
SELECT     SUM(beginning_value) AS mojodi_aval
FROM         (SELECT     codekala, SUM(remaining_quantity * unit_price) AS beginning_value
                        FROM         dbo.RemainingLots
                        WHERE     (fyear =
                                                  (SELECT     CAST(RIGHT(DB_NAME(), 4) AS INT) - 1 AS Expr1)) AND (remaining_quantity > 0)
                        GROUP BY codekala
                        UNION ALL
                        SELECT     kalacode, ISNULL(firststock * firstprice, 0) AS beginning_value
                        FROM         dbo.kala AS k
                        WHERE     (NOT EXISTS
                                                  (SELECT     1 AS Expr1
                                                     FROM         dbo.RemainingLots AS rl
                                                     WHERE     (codekala = k.kalacode) AND (fyear =
                                                                               (SELECT     CAST(RIGHT(DB_NAME(), 4) AS INT) - 1 AS Expr1)))) AND (ISNULL(firststock, 0) > 0)) AS combined_beginning


GO

-- 1. ایجاد جدول جدید با ستون IDENTITY
CREATE TABLE [dbo].[review_new](
	[serial] [int] IDENTITY(1,1) NOT NULL,
	[sharh] [nvarchar](500)  NULL,
	[reviewdate] [numeric](18, 0) NOT NULL,
	[state] [nvarchar](500)  NULL,
	[usercode] [numeric](18, 0) NULL,
	[enable] [bit] NULL,
	[check] [bit] NULL,
	[customercode] [int] NULL,
	[serialcheck] [int] NULL,
	[salescode] [int] NULL,
	[mablag] [int] NULL,
	[ghestnumber] [int] NULL,
	[ScheduleID] [int] NULL,
 CONSTRAINT [PK_review_new] PRIMARY KEY CLUSTERED ([serial] ASC)
);

-- 2. کپی داده‌ها از جدول قبلی به جدول جدید (به جز ستون serial چون خودش پر می‌شود)
INSERT INTO [dbo].[review_new]
(
	[sharh],
	[reviewdate],
	[state],
	[usercode],
	[enable],
	[check],
	[customercode],
	[serialcheck],
	[salescode],
	[mablag],
	[ghestnumber],
	[ScheduleID]
)
SELECT
	[sharh],
	[reviewdate],
	[state],
	[usercode],
	[enable],
	[check],
	[customercode],
	[serialcheck],
	[salescode],
	[mablag],
	[ghestnumber],
	[ScheduleID]
FROM [dbo].[review];

-- 3. حذف جدول قدیمی
DROP TABLE [dbo].[review];

-- 4. تغییر نام جدول جدید به نام جدول اصلی
EXEC sp_rename 'dbo.review_new', 'review';
/****** Object:  View [dbo].[kalakham]    Script Date: 05/28/2025 18:24:45 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[kalakham]'))
DROP VIEW [dbo].[kalakham]
GO


CREATE VIEW [dbo].[kalakham]
AS
SELECT      kalacode, price
FROM          dbo.kala
WHERE      (NOT EXISTS
                            (SELECT      *
                               FROM          formula
                               WHERE      codekalafromkala = kalacode))

GO

/****** Object:  View [dbo].[gheymat_kharid]    Script Date: 05/28/2025 18:25:03 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[gheymat_kharid]'))
DROP VIEW [dbo].[gheymat_kharid]
GO



/* روش دوم: استفاده از MAX برای یافتن آخرین رکورد بر اساس تاریخ*/
CREATE VIEW [dbo].[gheymat_kharid]
AS
SELECT     TOP (100) PERCENT kalacode AS kod_kala, kalaname AS nam_kala, CASE WHEN k.buy > 0 THEN k.buy WHEN k.firststock > 0 AND k.firstprice > 0 THEN k.firstprice ELSE 0 END AS gheymat_kharid, 
                      CASE WHEN k.buy > 0 THEN N'آخرین خرید (از فیلد buy)' WHEN k.firststock > 0 AND k.firstprice > 0 THEN N'اول دوره' ELSE N'بدون قیمت' END AS mansha_gheymat
FROM         dbo.kala AS k
ORDER BY kod_kala


GO

ALTER TABLE [dbo].[customeracc] ADD [sharh]  NVARCHAR(MAX)    NULL;
GO
ALTER TABLE [dbo].[sanaddeleted] ADD [sharh]  NVARCHAR(MAX)   NULL ;
GO