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].[company] ADD   costing_method VARCHAR(20)
go
update   [dbo].[company] set costing_method='FIFO'
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,enable)
    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,1
    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, [enable], 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,
        1, 
        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,
    @costing_method VARCHAR(20) = 'FIFO' -- FIFO, LAST_PURCHASE, AVERAGE
)
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

    -- اعتبارسنجی روش محاسبه
    IF @costing_method NOT IN ('FIFO', 'LAST_PURCHASE', 'AVERAGE')
    BEGIN
        RAISERROR(N'روش محاسبه باید یکی از مقادیر FIFO, LAST_PURCHASE, AVERAGE باشد.', 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..#CostingResults') IS NOT NULL DROP TABLE #CostingResults;
    IF OBJECT_ID('tempdb..#TransactionSummary') IS NOT NULL DROP TABLE #TransactionSummary;

    -- 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 DEFAULT 0, -- مقدار پیش‌فرض برای جلوگیری از NULL
        fdate INT NOT NULL,
        factorcode BIGINT 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,
        total_input_value DECIMAL(18,2) NOT NULL DEFAULT 0
    );

    CREATE TABLE #CostingResults (
        codekala INT PRIMARY KEY,
        endingstock DECIMAL(18,4) NOT NULL,
        endingprice DECIMAL(18,6) NOT NULL,
        endingvalue DECIMAL(18,2) NOT NULL,
        last_purchase_price DECIMAL(18,6) NULL,
        average_price DECIMAL(18,6) NULL,
        method_price DECIMAL(18,6) NOT NULL  -- قیمت بر اساس روش انتخابی
    );

    CREATE TABLE #TransactionSummary (
        codekala INT PRIMARY KEY,
        total_purchase DECIMAL(18,4) NOT NULL DEFAULT 0,
        total_purchase_value DECIMAL(18,2) NOT NULL DEFAULT 0,
        total_sale DECIMAL(18,4) NOT NULL DEFAULT 0,
        last_purchase_date INT NULL,
        last_purchase_price DECIMAL(18,6) NULL,
        total_input_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
        total_input_value DECIMAL(18,2) NOT NULL DEFAULT 0
    );

    -- ایجاد ایندکس‌ها
    CREATE NONCLUSTERED INDEX IX_Transactions_Kala_Date ON #Transactions (codekala, fdate, RowNum);
    CREATE NONCLUSTERED INDEX IX_PurchaseLots_Kala_Date ON #PurchaseLots (codekala, fdate, LotID);
    CREATE NONCLUSTERED INDEX IX_PurchaseLots_Remaining ON #PurchaseLots (codekala, remaining_quantity);
    CREATE NONCLUSTERED INDEX IX_TransactionSummary_Code ON #TransactionSummary (codekala);

    -- بارگذاری موجودی اولیه فقط برای روش FIFO
    IF @costing_method = 'FIFO'
    BEGIN
        -- موجودی اولیه از RemainingLots
        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;

        -- موجودی اولیه از kala
        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;
    END

    -- مقداردهی اولیه KalaState
    IF @costing_method = 'FIFO'
    BEGIN
        INSERT INTO #KalaState (kalacode, current_quantity, total_input, total_output, total_cogs, total_input_value)
        SELECT codekala, SUM(remaining_quantity), SUM(quantity), 0, 0, SUM(quantity * unit_price)
        FROM #PurchaseLots 
        GROUP BY codekala;

        -- اضافه کردن کالاهایی که موجودی اولیه ندارند
        INSERT INTO #KalaState (kalacode, current_quantity, total_input, total_output, total_cogs, total_input_value)
        SELECT k.kalacode, 0, 0, 0, 0, 0
        FROM dbo.kala k
        WHERE NOT EXISTS (SELECT 1 FROM #KalaState ks WHERE ks.kalacode = k.kalacode);
    END
    ELSE
    BEGIN
        -- برای روش‌های دیگر، موجودی اولیه از جدول kala
        INSERT INTO #KalaState (kalacode, current_quantity, total_input, total_output, total_cogs, total_input_value)
        SELECT k.kalacode, 
               ISNULL(k.firststock, 0),
               ISNULL(k.firststock, 0),
               0,
               0,
               ISNULL(k.firststock * k.firstprice, 0)
        FROM dbo.kala k
        WHERE ISNULL(k.firststock, 0) > 0;

        -- اضافه کردن کالاهایی که موجودی اولیه ندارند
        INSERT INTO #KalaState (kalacode, current_quantity, total_input, total_output, total_cogs, total_input_value)
        SELECT k.kalacode, 0, 0, 0, 0, 0
        FROM dbo.kala k
        WHERE NOT EXISTS (SELECT 1 FROM #KalaState ks WHERE ks.kalacode = k.kalacode);
    END

    -- بارگذاری تراکنش‌ها با مدیریت مقادیر NULL
    INSERT INTO #Transactions (codekala, transaction_type, quantity, unit_price, fdate, factorcode)
    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,
        ISNULL(d.number, 0), 
        ISNULL(d.fe, 0), -- مدیریت مقدار NULL در unit_price
        h.fdate,
        h.factorcode
    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 ISNULL(d.number, 0) <> 0  -- استفاده از ISNULL برای مدیریت NULL
      AND d.codekala IS NOT NULL
    ORDER BY h.fdate, ISNULL(h.ftime, 0), h.factorcode;

    -- خلاصه تراکنش‌ها برای همه روش‌ها (برای گزارش‌دهی)
    INSERT INTO #TransactionSummary (codekala, total_purchase, total_purchase_value, total_sale, last_purchase_date, total_input_quantity, total_input_value)
    SELECT 
        codekala,
        SUM(CASE WHEN transaction_type = 'PURCHASE' THEN quantity
                 WHEN transaction_type = 'PURCHASE_RETURN' THEN -quantity
                 ELSE 0 END) as total_purchase,
        SUM(CASE WHEN transaction_type = 'PURCHASE' THEN quantity * unit_price
                 WHEN transaction_type = 'PURCHASE_RETURN' THEN -quantity * unit_price
                 ELSE 0 END) as total_purchase_value,
        SUM(CASE WHEN transaction_type = 'SALE' THEN quantity
                 WHEN transaction_type = 'SALE_RETURN' THEN -quantity
                 ELSE 0 END) as total_sale,
        MAX(CASE WHEN transaction_type = 'PURCHASE' THEN fdate END) as last_purchase_date,
        SUM(CASE WHEN transaction_type IN ('PURCHASE', 'SALE_RETURN') THEN quantity
                 WHEN transaction_type IN ('PURCHASE_RETURN') THEN -quantity
                 ELSE 0 END) as total_input_quantity,
        SUM(CASE WHEN transaction_type IN ('PURCHASE', 'SALE_RETURN') THEN quantity * unit_price
                 WHEN transaction_type IN ('PURCHASE_RETURN') THEN -quantity * unit_price
                 ELSE 0 END) as total_input_value
    FROM #Transactions
    GROUP BY codekala;

    -- محاسبه آخرین قیمت خرید
    WITH LastPurchase AS (
        SELECT 
            t.codekala,
            t.unit_price,
            ROW_NUMBER() OVER (PARTITION BY t.codekala ORDER BY t.fdate DESC, t.RowNum DESC) as rn
        FROM #Transactions t
        WHERE t.transaction_type = 'PURCHASE' AND t.unit_price > 0
    )
    UPDATE ts SET last_purchase_price = lp.unit_price
    FROM #TransactionSummary ts
    INNER JOIN LastPurchase lp ON ts.codekala = lp.codekala AND lp.rn = 1;

    -- پردازش بر اساس روش انتخابی
    IF @costing_method = 'FIFO'
    BEGIN
        -- پردازش FIFO
        DECLARE @RowNum INT = 1;
        DECLARE @MaxRows INT;
        SELECT @MaxRows = MAX(RowNum) 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 @@ROWCOUNT = 0 
            BEGIN
                SET @RowNum = @RowNum + 1;
                CONTINUE;
            END

            DECLARE @initial_current_qty DECIMAL(18,4);
            SELECT @initial_current_qty = current_quantity FROM #KalaState WHERE kalacode = @codekala;
            
            IF @initial_current_qty IS NULL
            BEGIN
                INSERT INTO #KalaState (kalacode, current_quantity, total_input, total_output, total_cogs, total_input_value)
                VALUES (@codekala, 0, 0, 0, 0, 0);
                SET @initial_current_qty = 0;
            END

            -- PURCHASE
            IF @type = 'PURCHASE'
            BEGIN
                DECLARE @purchase_original_qty DECIMAL(18,4) = @qty;
                DECLARE @pending DECIMAL(18,4) = 0;
                
                IF EXISTS (SELECT 1 FROM #NegativeSales WHERE kalacode = @codekala)
                BEGIN
                    SELECT @pending = quantity FROM #NegativeSales WHERE kalacode = @codekala;
                END

                IF @pending > 0
                BEGIN
                    DECLARE @use DECIMAL(18,4);
                    SET @use = CASE WHEN @qty > @pending THEN @pending ELSE @qty END;
                    
                    UPDATE #NegativeSales SET quantity = quantity - @use WHERE kalacode = @codekala;

                    IF EXISTS (SELECT 1 FROM #NegativeSales WHERE kalacode = @codekala AND quantity <= 0)
                    BEGIN
                        DELETE FROM #NegativeSales WHERE kalacode = @codekala;
                    END
                    
                    SET @qty = @qty - @use;
                END

                UPDATE #KalaState SET 
                    current_quantity = current_quantity + @purchase_original_qty, 
                    total_input = total_input + @purchase_original_qty,
                    total_input_value = total_input_value + (@purchase_original_qty * @price)
                WHERE kalacode = @codekala;

                IF @qty > 0
                BEGIN
                    INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type)
                    VALUES (@codekala, @date, @qty, @price, @qty, @type);
                END
            END

            -- PURCHASE_RETURN
            ELSE IF @type = 'PURCHASE_RETURN'
            BEGIN
                DECLARE @return_remaining DECIMAL(18,4) = @qty;
                DECLARE @lot_id INT, @lot_rem DECIMAL(18,4), @lot_price DECIMAL(18,6);

                WHILE @return_remaining > 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;
                    
                    IF @@ROWCOUNT = 0 BREAK;

                    DECLARE @use_return DECIMAL(18,4);
                    SET @use_return = CASE WHEN @lot_rem > @return_remaining THEN @return_remaining ELSE @lot_rem END;
                    SET @return_remaining = @return_remaining - @use_return;

                    UPDATE #PurchaseLots SET remaining_quantity = remaining_quantity - @use_return WHERE LotID = @lot_id;
                END

                IF @return_remaining > 0
                BEGIN
                    IF NOT EXISTS (SELECT 1 FROM #NegativeSales WHERE kalacode = @codekala)
                        INSERT INTO #NegativeSales VALUES (@codekala, @return_remaining);
                    ELSE
                        UPDATE #NegativeSales SET quantity = quantity + @return_remaining WHERE kalacode = @codekala;
                END

                UPDATE #KalaState SET 
                    current_quantity = current_quantity - @qty, 
                    total_input = total_input - @qty,
                    total_input_value = total_input_value - (@qty * @price)
                WHERE kalacode = @codekala;
            END

            -- SALE
            ELSE IF @type = 'SALE'
            BEGIN
                DECLARE @sale_remaining DECIMAL(18,4) = @qty;
                DECLARE @cogs DECIMAL(18,2) = 0;

                -- Process Initial Stock first
                WHILE @sale_remaining > 0
                BEGIN
                    SELECT TOP 1 @lot_id = LotID, @lot_rem = remaining_quantity, @lot_price = unit_price 
                    FROM #PurchaseLots 
                    WHERE codekala = @codekala AND fdate = 0 AND remaining_quantity > 0 
                    ORDER BY LotID;
                    
                    IF @@ROWCOUNT = 0 BREAK;

                    DECLARE @use_sale DECIMAL(18,4);
                    SET @use_sale = CASE WHEN @lot_rem > @sale_remaining THEN @sale_remaining ELSE @lot_rem END;
                    SET @cogs = @cogs + (@use_sale * @lot_price);
                    SET @sale_remaining = @sale_remaining - @use_sale;

                    UPDATE #PurchaseLots SET remaining_quantity = remaining_quantity - @use_sale WHERE LotID = @lot_id;
                END

                -- Process Current Lots using FIFO
                WHILE @sale_remaining > 0
                BEGIN
                    SELECT TOP 1 @lot_id = LotID, @lot_rem = remaining_quantity, @lot_price = unit_price 
                    FROM #PurchaseLots 
                    WHERE codekala = @codekala AND fdate > 0 AND remaining_quantity > 0 
                    ORDER BY fdate, LotID;
                    
                    IF @@ROWCOUNT = 0 BREAK;

                    SET @use_sale = CASE WHEN @lot_rem > @sale_remaining THEN @sale_remaining ELSE @lot_rem END;
                    SET @cogs = @cogs + (@use_sale * @lot_price);
                    SET @sale_remaining = @sale_remaining - @use_sale;

                    UPDATE #PurchaseLots SET remaining_quantity = remaining_quantity - @use_sale WHERE LotID = @lot_id;
                END

                IF @sale_remaining > 0
                BEGIN
                    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;
                END
                
                UPDATE #KalaState SET 
                    current_quantity = current_quantity - @qty, 
                    total_output = total_output + @qty,
                    total_cogs = total_cogs + @cogs
                WHERE kalacode = @codekala;
            END

            -- SALE_RETURN
            ELSE IF @type = 'SALE_RETURN'
            BEGIN
                INSERT INTO #PurchaseLots (codekala, fdate, quantity, unit_price, remaining_quantity, transaction_type)
                VALUES (@codekala, @date, @qty, @price, @qty, @type);

                UPDATE #KalaState SET 
                    current_quantity = current_quantity + @qty, 
                    total_output = total_output - @qty,
                    total_input_value = total_input_value + (@qty * @price)
                WHERE kalacode = @codekala;
            END

            SET @RowNum = @RowNum + 1;
        END

        -- پاکسازی لات‌های خالی
        DELETE FROM #PurchaseLots WHERE remaining_quantity <= 0;

        -- بروزرسانی RemainingLots فقط برای FIFO
        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;

        -- محاسبه نتایج FIFO
        WITH PositiveLotsSum AS (
            SELECT codekala,
                   SUM(remaining_quantity) AS positive_stock,
                   SUM(remaining_quantity * unit_price) AS positive_value
            FROM #PurchaseLots
            WHERE remaining_quantity > 0
            GROUP BY codekala
        ),
        LastPurchasePrice AS ( 
            SELECT codekala, unit_price,
                   ROW_NUMBER() OVER(PARTITION BY codekala ORDER BY fdate DESC, RowNum DESC) AS rn
            FROM #Transactions 
            WHERE transaction_type = 'PURCHASE' AND unit_price > 0
        ),
        WeightedAverage AS (
            SELECT 
                codekala,
                CASE 
                    WHEN SUM(remaining_quantity) > 0 
                    THEN SUM(remaining_quantity * unit_price) / SUM(remaining_quantity)
                    ELSE 0
                END as avg_price
            FROM #PurchaseLots
            WHERE remaining_quantity > 0
            GROUP BY codekala
        )
        INSERT INTO #CostingResults (codekala, endingstock, endingprice, endingvalue, last_purchase_price, average_price, method_price)
        SELECT
            ks.kalacode,
            ks.current_quantity as endingstock,
            CASE
                WHEN ks.current_quantity > 0 THEN
                    COALESCE(CAST(pls.positive_value / NULLIF(pls.positive_stock, 0) AS DECIMAL(18,6)), 0)
                WHEN ks.current_quantity < 0 THEN
                    COALESCE(lpp.unit_price, k.firstprice, 0)
                ELSE 0 
            END as endingprice,
            CASE
                WHEN ks.current_quantity > 0 THEN
                    CAST(COALESCE(pls.positive_value, 0) AS DECIMAL(18,2))
                WHEN ks.current_quantity < 0 THEN
                    CAST(ks.current_quantity * COALESCE(lpp.unit_price, k.firstprice, 0) AS DECIMAL(18,2))
                ELSE 0
            END as endingvalue,
            COALESCE(lpp.unit_price, k.firstprice, 0) as last_purchase_price,
            COALESCE(wa.avg_price, k.firstprice, 0) as average_price,
            CASE
                WHEN ks.current_quantity > 0 THEN
                    COALESCE(CAST(pls.positive_value / NULLIF(pls.positive_stock, 0) AS DECIMAL(18,6)), 0)
                ELSE
                    COALESCE(lpp.unit_price, k.firstprice, 0)
            END as method_price
        FROM #KalaState ks
        LEFT JOIN PositiveLotsSum pls ON ks.kalacode = pls.codekala
        LEFT JOIN LastPurchasePrice lpp ON ks.kalacode = lpp.codekala AND lpp.rn = 1
        LEFT JOIN WeightedAverage wa ON ks.kalacode = wa.codekala
        LEFT JOIN dbo.kala k ON ks.kalacode = k.kalacode;
    END
    ELSE IF @costing_method = 'LAST_PURCHASE'
    BEGIN
        -- روش آخرین قیمت خرید
        WITH KalaBase AS (
            SELECT 
                k.kalacode,
                ISNULL(k.firststock, 0) as firststock,
                ISNULL(k.firstprice, 0) as firstprice,
                ISNULL(ts.total_input_quantity, 0) as total_input,
                ISNULL(ts.total_sale, 0) as total_sale,
                COALESCE(ts.last_purchase_price, k.firstprice, 0) as last_purchase_price,
                ISNULL(ts.total_input_value, 0) as total_input_value
            FROM dbo.kala k
            LEFT JOIN #TransactionSummary ts ON k.kalacode = ts.codekala
        ),
        WeightedAverage AS (
            SELECT 
                kb.kalacode,
                CASE 
                    WHEN kb.firststock + kb.total_input > 0 
                    THEN (kb.firststock * kb.firstprice + kb.total_input_value) / (kb.firststock + kb.total_input)
                    ELSE kb.last_purchase_price
                END as avg_price
            FROM KalaBase kb
        )
        INSERT INTO #CostingResults (codekala, endingstock, endingprice, endingvalue, last_purchase_price, average_price, method_price)
        SELECT 
            kb.kalacode,
            kb.firststock + kb.total_input - kb.total_sale as endingstock,
            kb.last_purchase_price as endingprice,
            (kb.firststock + kb.total_input - kb.total_sale) * kb.last_purchase_price as endingvalue,
            kb.last_purchase_price,
            wa.avg_price,
            kb.last_purchase_price as method_price
        FROM KalaBase kb
        LEFT JOIN WeightedAverage wa ON kb.kalacode = wa.kalacode;
    END
    ELSE IF @costing_method = 'AVERAGE'
    BEGIN
        -- روش میانگین موزون
        WITH KalaBase AS (
            SELECT 
                k.kalacode,
                ISNULL(k.firststock, 0) as firststock,
                ISNULL(k.firstprice, 0) as firstprice,
                ISNULL(k.firststock * k.firstprice, 0) as firstvalue,
                ISNULL(ts.total_input_quantity, 0) as total_input,
                ISNULL(ts.total_input_value, 0) as total_input_value,
                ISNULL(ts.total_sale, 0) as total_sale,
                COALESCE(ts.last_purchase_price, k.firstprice, 0) as last_purchase_price
            FROM dbo.kala k
            LEFT JOIN #TransactionSummary ts ON k.kalacode = ts.codekala
        ),
        WeightedAverage AS (
            SELECT 
                kb.kalacode,
                CASE 
                    WHEN kb.firststock + kb.total_input > 0 
                    THEN (kb.firstvalue + kb.total_input_value) / (kb.firststock + kb.total_input)
                    ELSE kb.last_purchase_price
                END as avg_price
            FROM KalaBase kb
        )
        INSERT INTO #CostingResults (codekala, endingstock, endingprice, endingvalue, last_purchase_price, average_price, method_price)
        SELECT 
            kb.kalacode,
            kb.firststock + kb.total_input - kb.total_sale as endingstock,
            wa.avg_price as endingprice,
            (kb.firststock + kb.total_input - kb.total_sale) * wa.avg_price as endingvalue,
            kb.last_purchase_price,
            wa.avg_price,
            wa.avg_price as method_price
        FROM KalaBase kb
        INNER JOIN WeightedAverage wa ON kb.kalacode = wa.kalacode;
    END

    -- اضافه کردن مقادیر پیش‌فرض برای کالاهای پردازش نشده
    INSERT INTO #CostingResults (codekala, endingstock, endingprice, endingvalue, last_purchase_price, average_price, method_price)
    SELECT 
        k.kalacode, 
        ISNULL(k.firststock, 0), 
        ISNULL(k.firstprice, 0), 
        CAST(ISNULL(k.firststock * k.firstprice, 0) AS DECIMAL(18,2)),
        ISNULL(k.firstprice, 0),
        ISNULL(k.firstprice, 0),
        ISNULL(k.firstprice, 0)
    FROM dbo.kala k
    WHERE NOT EXISTS (SELECT 1 FROM #CostingResults WHERE codekala = k.kalacode);

    -- بروزرسانی جدول kala - این قسمت حیاتی است
    UPDATE k SET 
        k.endingstock = cr.endingstock,
        k.endingprice = cr.endingprice,
        k.endingvalue = cr.endingvalue,
        k.buy = cr.method_price,  -- استفاده از method_price که بر اساس روش انتخابی محاسبه شده
        k.numberinput = ISNULL(ts.total_input_quantity, 0),
        k.numberoutput = ISNULL(ts.total_sale, 0)
    FROM dbo.kala k
    INNER JOIN #CostingResults cr ON k.kalacode = cr.codekala
    LEFT JOIN #TransactionSummary ts ON k.kalacode = ts.codekala;

    -- بررسی نتایج (اختیاری - برای دیباگ)
    /*
    SELECT 
        k.kalacode,
        k.name,
        k.endingstock,
        k.endingprice,
        k.endingvalue,
        k.buy,
        k.numberinput,
        k.numberoutput,
        @costing_method as costing_method
    FROM dbo.kala k
    WHERE k.endingstock <> 0;
    */

END;
GO



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
ALTER FUNCTION [dbo].[detailaccount]()
RETURNS TABLE
AS
RETURN ( SELECT      TOP 100 PERCENT dbo.hfactor.factorcode, dbo.hfactor.cash, SUM(dbo.dfactor.[sum]) AS sumall, SUM(dbo.dfactor.rebate) AS sumrebate, dbo.hfactor.customercode AS customercode, dbo.hfactor.fdate, 
                        dbo.customer.customername, dbo.hfactor.comment, dbo.hfactor.factortype, dbo.hfactor.salescode, dbo.hfactor.buycode, dbo.hfactor.spacialsales, dbo.hfactor.codeuser, dbo.[user].usercode, 
                        dbo.[user].username, dbo.customer.customertel, dbo.customer.customermobil, dbo.customer.customeraddress, ISNULL(dbo.hfactor.bsalescode, 0) AS bsalescode, ISNULL(dbo.hfactor.bbuycode, 0) 
                        AS bbuycode, dbo.hfactor.year, dbo.hfactor.bsalescode AS Expr1, dbo.hfactor.bbuycode AS Expr2, dbo.hfactor.psalescode, dbo.customergroup.customergroup,sum(dbo.dfactor.tax) as sumtax
FROM          dbo.customergroup INNER JOIN
                        dbo.customer ON dbo.customergroup.customergroupcode = dbo.customer.customergroup RIGHT OUTER JOIN
                        dbo.[user] INNER JOIN
                        dbo.hfactor INNER JOIN
                        dbo.dfactor ON dbo.hfactor.factorcode = dbo.dfactor.factorcode ON dbo.[user].usercode = dbo.hfactor.codeuser ON dbo.customer.customercode = dbo.hfactor.customercode
GROUP BY dbo.hfactor.factorcode, dbo.hfactor.cash, dbo.hfactor.customercode, dbo.hfactor.fdate, dbo.customer.customername, dbo.hfactor.comment, dbo.hfactor.factortype, dbo.hfactor.salescode, 
                        dbo.hfactor.buycode, dbo.hfactor.spacialsales, dbo.hfactor.codeuser, dbo.[user].usercode, dbo.[user].username, dbo.customer.customertel, dbo.customer.customermobil, dbo.customer.customeraddress, 
                        dbo.hfactor.bsalescode, dbo.hfactor.bbuycode, dbo.hfactor.year, dbo.hfactor.psalescode, dbo.customergroup.customergroup
ORDER BY dbo.hfactor.factorcode )
go
ALTER FUNCTION [dbo].[sumdetailaccount](@fromdate int = 0,
@todate int = 99999999)
RETURNS TABLE
AS
RETURN ( SELECT      factortype, SUM(sumrebate) AS sumrebate, SUM(sumall) AS sumall, customercode, customergroup,sum(sumtax) as sumtax
FROM          dbo.detailaccount() detailaccount
WHERE      (fdate >= @fromdate) AND (fdate <= @todate)
GROUP BY factortype, customercode, customergroup )
go
/****** Object:  View [dbo].[Moadian]    Script Date: 11/16/2025 14:27:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[Moadian]
AS
SELECT     dbo.hfactor.ftime, dbo.hfactor.fdate AS Indatim, dbo.hfactor.factorcode, dbo.dfactor.sum AS Tsstam, dbo.dfactor.rebate AS Dis, dbo.dfactor.tax AS Vam, 
                      dbo.kala.tax AS Vra, dbo.dfactor.number AS Am, dbo.kala.irancodekala AS Sstid, dbo.kala.kalaname AS Sstt, dbo.dfactor.fe AS Fee, dbo.customer.codemeli AS Bid, 
                      dbo.company.codeeghtesadi AS Tins, dbo.company.privatekey, dbo.company.clientid, dbo.kala.unit1 AS Mu, dbo.hfactor.cash, dbo.hfactor.factortype, 
                      dbo.hfactor.moadianuid, dbo.customer.codeposti AS Bpc, dbo.customer.codeeghtesadi AS Tinb, dbo.customer.branchcode AS bbc
FROM         dbo.dfactor INNER JOIN
                      dbo.hfactor ON dbo.dfactor.factorcode = dbo.hfactor.factorcode INNER JOIN
                      dbo.kala ON dbo.dfactor.codekala = dbo.kala.kalacode INNER JOIN
                      dbo.customer ON dbo.hfactor.customercode = dbo.customer.customercode CROSS JOIN
                      dbo.company


GO