Stored Procedure های کامل برای مدیریت جداول سیستم انبارداری

DELIMITER $$

CREATE PROCEDURE sp_GetProducts(
    IN p_CompanyID INT,
    IN p_CategoryID INT,
    IN p_IsActive BOOLEAN,
    IN p_SearchTerm NVARCHAR(200),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Products p
    WHERE p.CompanyID = p_CompanyID
    AND (p_CategoryID IS NULL OR p.CategoryID = p_CategoryID)
    AND p.IsActive = p_IsActive
    AND (p_SearchTerm IS NULL OR 
         p.ProductCode LIKE CONCAT('%', p_SearchTerm, '%') OR 
         p.ProductName LIKE CONCAT('%', p_SearchTerm, '%') OR
         p.Barcode LIKE CONCAT('%', p_SearchTerm, '%'));
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن لیست محصولات
    SELECT 
        p.ProductID,
        p.ProductCode,
        p.Barcode,
        p.ProductName,
        p.ProductDescription,
        p.CategoryID,
        c.CategoryName,
        p.UnitID,
        u.UnitName,
        p.Brand,
        p.Model,
        p.Weight,
        p.Volume,
        p.MinStockLevel,
        p.MaxStockLevel,
        p.ReorderPoint,
        p.SafetyStock,
        p.IsBatchTracked,
        p.IsSerialTracked,
        p.IsExpiryTracked,
        p.ShelfLife,
        p.IsActive,
        COALESCE(SUM(i.Quantity), 0) as TotalStock,
        COALESCE(SUM(i.AvailableQuantity), 0) as AvailableStock,
        COALESCE(SUM(i.ReservedQuantity), 0) as ReservedStock,
        CASE 
            WHEN COALESCE(SUM(i.AvailableQuantity), 0) <= p.MinStockLevel THEN 'Low'
            WHEN COALESCE(SUM(i.AvailableQuantity), 0) >= p.MaxStockLevel THEN 'High'
            ELSE 'Normal'
        END as StockStatus
    FROM Products p
    LEFT JOIN ProductCategories c ON p.CategoryID = c.CategoryID
    LEFT JOIN Units u ON p.UnitID = u.UnitID
    LEFT JOIN Inventory i ON p.ProductID = i.ProductID
    WHERE p.CompanyID = p_CompanyID
    AND (p_CategoryID IS NULL OR p.CategoryID = p_CategoryID)
    AND p.IsActive = p_IsActive
    AND (p_SearchTerm IS NULL OR 
         p.ProductCode LIKE CONCAT('%', p_SearchTerm, '%') OR 
         p.ProductName LIKE CONCAT('%', p_SearchTerm, '%') OR
         p.Barcode LIKE CONCAT('%', p_SearchTerm, '%'))
    GROUP BY p.ProductID
    ORDER BY p.ProductName
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetProductInfo(
    IN p_ProductID INT
)
BEGIN
    -- اطلاعات اصلی محصول
    SELECT 
        p.*,
        c.CategoryName,
        u.UnitName,
        u.UnitCode,
        COALESCE(SUM(i.Quantity), 0) as TotalStock,
        COALESCE(SUM(i.AvailableQuantity), 0) as AvailableStock,
        COALESCE(SUM(i.ReservedQuantity), 0) as ReservedStock,
        COALESCE(SUM(i.TotalValue), 0) as TotalValue,
        COUNT(DISTINCT i.WarehouseID) as WarehouseCount
    FROM Products p
    LEFT JOIN ProductCategories c ON p.CategoryID = c.CategoryID
    LEFT JOIN Units u ON p.UnitID = u.UnitID
    LEFT JOIN Inventory i ON p.ProductID = i.ProductID
    WHERE p.ProductID = p_ProductID
    GROUP BY p.ProductID;
    
    -- موجودی در انبارهای مختلف
    SELECT 
        i.InventoryID,
        i.WarehouseID,
        w.WarehouseCode,
        w.WarehouseName,
        i.ZoneID,
        z.ZoneCode,
        z.ZoneName,
        i.BatchNumber,
        i.SerialNumber,
        i.ExpiryDate,
        i.ProductionDate,
        i.Quantity,
        i.ReservedQuantity,
        i.AvailableQuantity,
        i.UnitCost,
        i.TotalValue,
        i.Status,
        DATEDIFF(i.ExpiryDate, CURDATE()) as DaysToExpiry
    FROM Inventory i
    INNER JOIN Warehouses w ON i.WarehouseID = w.WarehouseID
    INNER JOIN WarehouseZones z ON i.ZoneID = z.ZoneID
    WHERE i.ProductID = p_ProductID
    AND i.Quantity > 0
    ORDER BY w.WarehouseName, z.ZoneCode;
    
    -- آخرین تراکنش‌ها
    SELECT 
        it.TransactionID,
        it.TransactionDate,
        it.TransactionType,
        it.ReferenceNumber,
        it.QuantityIn,
        it.QuantityOut,
        it.UnitCost,
        it.TotalCost,
        w.WarehouseName,
        z.ZoneCode,
        it.Remarks
    FROM InventoryTransactions it
    LEFT JOIN Warehouses w ON it.WarehouseID = w.WarehouseID
    LEFT JOIN WarehouseZones z ON it.ZoneID = z.ZoneID
    WHERE it.ProductID = p_ProductID
    ORDER BY it.TransactionDate DESC, it.TransactionID DESC
    LIMIT 10;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateProduct(
    IN p_CompanyID INT,
    IN p_ProductCode VARCHAR(50),
    IN p_Barcode VARCHAR(100),
    IN p_ProductName NVARCHAR(200),
    IN p_ProductDescription TEXT,
    IN p_CategoryID INT,
    IN p_UnitID INT,
    IN p_Brand NVARCHAR(100),
    IN p_Model NVARCHAR(100),
    IN p_Weight DECIMAL(10,3),
    IN p_Volume DECIMAL(10,3),
    IN p_MinStockLevel DECIMAL(12,3),
    IN p_MaxStockLevel DECIMAL(12,3),
    IN p_ReorderPoint DECIMAL(12,3),
    IN p_SafetyStock DECIMAL(12,3),
    IN p_IsBatchTracked BOOLEAN,
    IN p_IsSerialTracked BOOLEAN,
    IN p_IsExpiryTracked BOOLEAN,
    IN p_ShelfLife INT,
    IN p_StorageConditions TEXT,
    IN p_Specifications JSON,
    OUT p_ProductID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_Count INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد محصول';
        SET p_ProductID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد محصول
    SELECT COUNT(*) INTO v_Count 
    FROM Products 
    WHERE CompanyID = p_CompanyID AND ProductCode = p_ProductCode;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'کد محصول تکراری است';
        SET p_ProductID = -1;
        ROLLBACK;
    ELSE
        -- بررسی تکراری نبودن بارکد
        IF p_Barcode IS NOT NULL THEN
            SELECT COUNT(*) INTO v_Count 
            FROM Products 
            WHERE CompanyID = p_CompanyID AND Barcode = p_Barcode;
            
            IF v_Count > 0 THEN
                SET p_ResultMessage = 'بارکد تکراری است';
                SET p_ProductID = -1;
                ROLLBACK;
            ELSE
                -- ایجاد محصول
                INSERT INTO Products (
                    CompanyID, ProductCode, Barcode, ProductName, ProductDescription,
                    CategoryID, UnitID, Brand, Model, Weight, Volume,
                    MinStockLevel, MaxStockLevel, ReorderPoint, SafetyStock,
                    IsBatchTracked, IsSerialTracked, IsExpiryTracked, ShelfLife,
                    StorageConditions, Specifications
                ) VALUES (
                    p_CompanyID, p_ProductCode, p_Barcode, p_ProductName, p_ProductDescription,
                    p_CategoryID, p_UnitID, p_Brand, p_Model, p_Weight, p_Volume,
                    p_MinStockLevel, p_MaxStockLevel, p_ReorderPoint, p_SafetyStock,
                    p_IsBatchTracked, p_IsSerialTracked, p_IsExpiryTracked, p_ShelfLife,
                    p_StorageConditions, p_Specifications
                );
                
                SET p_ProductID = LAST_INSERT_ID();
                SET p_ResultMessage = 'محصول با موفقیت ایجاد شد';
                COMMIT;
            END IF;
        ELSE
            -- ایجاد محصول بدون بارکد
            INSERT INTO Products (
                CompanyID, ProductCode, Barcode, ProductName, ProductDescription,
                CategoryID, UnitID, Brand, Model, Weight, Volume,
                MinStockLevel, MaxStockLevel, ReorderPoint, SafetyStock,
                IsBatchTracked, IsSerialTracked, IsExpiryTracked, ShelfLife,
                StorageConditions, Specifications
            ) VALUES (
                p_CompanyID, p_ProductCode, p_Barcode, p_ProductName, p_ProductDescription,
                p_CategoryID, p_UnitID, p_Brand, p_Model, p_Weight, p_Volume,
                p_MinStockLevel, p_MaxStockLevel, p_ReorderPoint, p_SafetyStock,
                p_IsBatchTracked, p_IsSerialTracked, p_IsExpiryTracked, p_ShelfLife,
                p_StorageConditions, p_Specifications
            );
            
            SET p_ProductID = LAST_INSERT_ID();
            SET p_ResultMessage = 'محصول با موفقیت ایجاد شد';
            COMMIT;
        END IF;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateProduct(
    IN p_ProductID INT,
    IN p_ProductName NVARCHAR(200),
    IN p_ProductDescription TEXT,
    IN p_CategoryID INT,
    IN p_UnitID INT,
    IN p_Brand NVARCHAR(100),
    IN p_Model NVARCHAR(100),
    IN p_Weight DECIMAL(10,3),
    IN p_Volume DECIMAL(10,3),
    IN p_MinStockLevel DECIMAL(12,3),
    IN p_MaxStockLevel DECIMAL(12,3),
    IN p_ReorderPoint DECIMAL(12,3),
    IN p_SafetyStock DECIMAL(12,3),
    IN p_IsBatchTracked BOOLEAN,
    IN p_IsSerialTracked BOOLEAN,
    IN p_IsExpiryTracked BOOLEAN,
    IN p_ShelfLife INT,
    IN p_StorageConditions TEXT,
    IN p_Specifications JSON,
    IN p_IsActive BOOLEAN,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی محصول';
    END;
    
    START TRANSACTION;
    
    -- به‌روزرسانی محصول
    UPDATE Products 
    SET 
        ProductName = p_ProductName,
        ProductDescription = p_ProductDescription,
        CategoryID = p_CategoryID,
        UnitID = p_UnitID,
        Brand = p_Brand,
        Model = p_Model,
        Weight = p_Weight,
        Volume = p_Volume,
        MinStockLevel = p_MinStockLevel,
        MaxStockLevel = p_MaxStockLevel,
        ReorderPoint = p_ReorderPoint,
        SafetyStock = p_SafetyStock,
        IsBatchTracked = p_IsBatchTracked,
        IsSerialTracked = p_IsSerialTracked,
        IsExpiryTracked = p_IsExpiryTracked,
        ShelfLife = p_ShelfLife,
        StorageConditions = p_StorageConditions,
        Specifications = p_Specifications,
        IsActive = p_IsActive,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE ProductID = p_ProductID;
    
    SET p_ResultMessage = 'محصول با موفقیت به‌روزرسانی شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetInventory(
    IN p_CompanyID INT,
    IN p_WarehouseID INT,
    IN p_ProductID INT,
    IN p_ShowLowStock BOOLEAN,
    IN p_ShowExpired BOOLEAN,
    IN p_ShowZeroStock BOOLEAN,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalValue DECIMAL(15,2)
)
BEGIN
    -- محاسبه رکوردهای کل و مجموع ارزش
    SELECT 
        COUNT(DISTINCT i.InventoryID),
        COALESCE(SUM(i.TotalValue), 0)
    INTO p_TotalRecords, p_TotalValue
    FROM Inventory i
    INNER JOIN Products p ON i.ProductID = p.ProductID
    INNER JOIN Warehouses w ON i.WarehouseID = w.WarehouseID
    WHERE w.CompanyID = p_CompanyID
    AND (p_WarehouseID IS NULL OR i.WarehouseID = p_WarehouseID)
    AND (p_ProductID IS NULL OR i.ProductID = p_ProductID)
    AND (p_ShowZeroStock = TRUE OR i.Quantity > 0)
    AND (p_ShowLowStock = FALSE OR i.AvailableQuantity <= p.MinStockLevel)
    AND (p_ShowExpired = FALSE OR (i.ExpiryDate IS NOT NULL AND i.ExpiryDate < CURDATE()));
    
    -- گرفتن لیست موجودی
    SELECT 
        i.InventoryID,
        i.ProductID,
        p.ProductCode,
        p.ProductName,
        p.Barcode,
        i.WarehouseID,
        w.WarehouseCode,
        w.WarehouseName,
        i.ZoneID,
        z.ZoneCode,
        z.ZoneName,
        i.BatchNumber,
        i.SerialNumber,
        i.ExpiryDate,
        i.ProductionDate,
        i.Quantity,
        i.ReservedQuantity,
        i.AvailableQuantity,
        i.UnitCost,
        i.TotalValue,
        i.Status,
        p.MinStockLevel,
        p.MaxStockLevel,
        DATEDIFF(i.ExpiryDate, CURDATE()) as DaysToExpiry,
        CASE 
            WHEN i.ExpiryDate IS NOT NULL AND i.ExpiryDate < CURDATE() THEN 'Expired'
            WHEN i.AvailableQuantity <= p.MinStockLevel THEN 'Low'
            WHEN i.AvailableQuantity >= p.MaxStockLevel THEN 'High'
            ELSE 'Normal'
        END as StockStatus
    FROM Inventory i
    INNER JOIN Products p ON i.ProductID = p.ProductID
    INNER JOIN Warehouses w ON i.WarehouseID = w.WarehouseID
    INNER JOIN WarehouseZones z ON i.ZoneID = z.ZoneID
    WHERE w.CompanyID = p_CompanyID
    AND (p_WarehouseID IS NULL OR i.WarehouseID = p_WarehouseID)
    AND (p_ProductID IS NULL OR i.ProductID = p_ProductID)
    AND (p_ShowZeroStock = TRUE OR i.Quantity > 0)
    AND (p_ShowLowStock = FALSE OR i.AvailableQuantity <= p.MinStockLevel)
    AND (p_ShowExpired = FALSE OR (i.ExpiryDate IS NOT NULL AND i.ExpiryDate < CURDATE()))
    ORDER BY p.ProductName, w.WarehouseName, z.ZoneCode
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateInventory(
    IN p_InventoryID INT,
    IN p_Quantity DECIMAL(12,3),
    IN p_UnitCost DECIMAL(15,2),
    IN p_BatchNumber VARCHAR(100),
    IN p_SerialNumber VARCHAR(100),
    IN p_ExpiryDate DATE,
    IN p_ProductionDate DATE,
    IN p_Status ENUM('Available', 'Quarantine', 'Blocked', 'Expired'),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_OldQuantity DECIMAL(12,3);
    DECLARE v_OldUnitCost DECIMAL(15,2);
    DECLARE v_ProductID INT;
    DECLARE v_WarehouseID INT;
    DECLARE v_ZoneID INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی موجودی';
    END;
    
    START TRANSACTION;
    
    -- گرفتن مقادیر قبلی
    SELECT Quantity, UnitCost, ProductID, WarehouseID, ZoneID
    INTO v_OldQuantity, v_OldUnitCost, v_ProductID, v_WarehouseID, v_ZoneID
    FROM Inventory
    WHERE InventoryID = p_InventoryID;
    
    -- به‌روزرسانی موجودی
    UPDATE Inventory 
    SET 
        Quantity = p_Quantity,
        UnitCost = p_UnitCost,
        BatchNumber = p_BatchNumber,
        SerialNumber = p_SerialNumber,
        ExpiryDate = p_ExpiryDate,
        ProductionDate = p_ProductionDate,
        Status = p_Status,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE InventoryID = p_InventoryID;
    
    -- ثبت تراکنش تعدیل
    INSERT INTO InventoryTransactions (
        ProductID, WarehouseID, ZoneID, TransactionType, ReferenceID,
        QuantityIn, QuantityOut, UnitCost, TotalCost, NewQuantity, NewUnitCost,
        Remarks
    ) VALUES (
        v_ProductID, v_WarehouseID, v_ZoneID, 'Adjustment', p_InventoryID,
        CASE WHEN p_Quantity > v_OldQuantity THEN p_Quantity - v_OldQuantity ELSE 0 END,
        CASE WHEN p_Quantity < v_OldQuantity THEN v_OldQuantity - p_Quantity ELSE 0 END,
        v_OldUnitCost,
        ABS(p_Quantity - v_OldQuantity) * v_OldUnitCost,
        p_Quantity,
        p_UnitCost,
        'Manual inventory adjustment'
    );
    
    SET p_ResultMessage = 'موجودی با موفقیت به‌روزرسانی شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateGoodsReceipt(
    IN p_CompanyID INT,
    IN p_OrderID INT,
    IN p_ReceiptDate DATE,
    IN p_ReceivedBy INT,
    IN p_WarehouseID INT,
    IN p_SupplierID INT,
    IN p_ReceiptType ENUM('Purchase', 'Return', 'Transfer', 'Production'),
    IN p_Notes TEXT,
    IN p_Items JSON,
    OUT p_ReceiptID INT,
    OUT p_ReceiptNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_TotalQuantity DECIMAL(12,3) DEFAULT 0;
    DECLARE v_TotalValue DECIMAL(15,2) DEFAULT 0;
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_OrderItemID INT;
    DECLARE v_ProductID INT;
    DECLARE v_ReceivedQuantity DECIMAL(12,3);
    DECLARE v_UnitPrice DECIMAL(12,2);
    DECLARE v_BatchNumber VARCHAR(100);
    DECLARE v_SerialNumber VARCHAR(100);
    DECLARE v_ExpiryDate DATE;
    DECLARE v_ProductionDate DATE;
    DECLARE v_ZoneID INT;
    DECLARE v_QualityStatus ENUM('Accepted', 'Rejected', 'Quarantine');
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد رسید دریافت';
        SET p_ReceiptID = -1;
        SET p_ReceiptNumber = '';
    END;
    
    START TRANSACTION;
    
    -- تولید شماره رسید
    SET p_ReceiptNumber = fn_GenerateReceiptNumber(p_CompanyID, p_ReceiptType);
    
    -- ایجاد رسید
    INSERT INTO GoodsReceipts (
        ReceiptNumber, OrderID, ReceiptDate, ReceivedBy, WarehouseID,
        SupplierID, ReceiptType, Notes
    ) VALUES (
        p_ReceiptNumber, p_OrderID, p_ReceiptDate, p_ReceivedBy, p_WarehouseID,
        p_SupplierID, p_ReceiptType, p_Notes
    );
    
    SET p_ReceiptID = LAST_INSERT_ID();
    
    -- پردازش اقلام
    SET v_ItemCount = JSON_LENGTH(p_Items);
    SET i = 0;
    
    WHILE i < v_ItemCount DO
        SET v_OrderItemID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].OrderItemID')), 'null');
        SET v_ProductID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProductID'));
        SET v_ReceivedQuantity = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ReceivedQuantity'));
        SET v_UnitPrice = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].UnitPrice'));
        SET v_BatchNumber = NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].BatchNumber'))), 'null');
        SET v_SerialNumber = NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].SerialNumber'))), 'null');
        SET v_ExpiryDate = NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ExpiryDate'))), 'null');
        SET v_ProductionDate = NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProductionDate'))), 'null');
        SET v_ZoneID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ZoneID'));
        SET v_QualityStatus = JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].QualityStatus')));
        
        -- ایجاد آیتم رسید
        INSERT INTO GoodsReceiptItems (
            ReceiptID, OrderItemID, ProductID, ReceivedQuantity, UnitPrice,
            BatchNumber, SerialNumber, ExpiryDate, ProductionDate, ZoneID, QualityStatus
        ) VALUES (
            p_ReceiptID, v_OrderItemID, v_ProductID, v_ReceivedQuantity, v_UnitPrice,
            v_BatchNumber, v_SerialNumber, v_ExpiryDate, v_ProductionDate, v_ZoneID, v_QualityStatus
        );
        
        -- به‌روزرسانی موجودی برای کالاهای پذیرفته شده
        IF v_QualityStatus = 'Accepted' THEN
            CALL sp_UpdateInventoryOnReceipt(
                v_ProductID, p_WarehouseID, v_ZoneID, v_ReceivedQuantity, v_UnitPrice,
                v_BatchNumber, v_SerialNumber, v_ExpiryDate, v_ProductionDate,
                p_ReceiptID, p_ReceiptNumber
            );
        END IF;
        
        SET v_TotalQuantity = v_TotalQuantity + v_ReceivedQuantity;
        SET v_TotalValue = v_TotalValue + (v_ReceivedQuantity * v_UnitPrice);
        
        SET i = i + 1;
    END WHILE;
    
    -- به‌روزرسانی مقادیر کل رسید
    UPDATE GoodsReceipts 
    SET TotalQuantity = v_TotalQuantity, TotalValue = v_TotalValue, Status = 'Received'
    WHERE ReceiptID = p_ReceiptID;
    
    SET p_ResultMessage = 'رسید دریافت با موفقیت ایجاد شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateInventoryOnReceipt(
    IN p_ProductID INT,
    IN p_WarehouseID INT,
    IN p_ZoneID INT,
    IN p_Quantity DECIMAL(12,3),
    IN p_UnitCost DECIMAL(12,2),
    IN p_BatchNumber VARCHAR(100),
    IN p_SerialNumber VARCHAR(100),
    IN p_ExpiryDate DATE,
    IN p_ProductionDate DATE,
    IN p_ReferenceID INT,
    IN p_ReferenceNumber VARCHAR(50)
)
BEGIN
    DECLARE v_InventoryID INT;
    DECLARE v_ExistingQuantity DECIMAL(12,3);
    DECLARE v_ExistingUnitCost DECIMAL(12,2);
    
    -- پیدا کردن یا ایجاد رکورد موجودی
    SELECT InventoryID, Quantity, UnitCost
    INTO v_InventoryID, v_ExistingQuantity, v_ExistingUnitCost
    FROM Inventory
    WHERE ProductID = p_ProductID 
    AND WarehouseID = p_WarehouseID 
    AND ZoneID = p_ZoneID
    AND (BatchNumber = p_BatchNumber OR (BatchNumber IS NULL AND p_BatchNumber IS NULL))
    AND (SerialNumber = p_SerialNumber OR (SerialNumber IS NULL AND p_SerialNumber IS NULL));
    
    IF v_InventoryID IS NOT NULL THEN
        -- محاسبه میانگین موزون قیمت تمام شده
        SET v_ExistingUnitCost = (
            (v_ExistingQuantity * v_ExistingUnitCost) + (p_Quantity * p_UnitCost)
        ) / (v_ExistingQuantity + p_Quantity);
        
        -- به‌روزرسانی موجودی موجود
        UPDATE Inventory 
        SET 
            Quantity = Quantity + p_Quantity,
            UnitCost = v_ExistingUnitCost,
            UpdatedAt = CURRENT_TIMESTAMP
        WHERE InventoryID = v_InventoryID;
    ELSE
        -- ایجاد رکورد جدید موجودی
        INSERT INTO Inventory (
            ProductID, WarehouseID, ZoneID, BatchNumber, SerialNumber,
            ExpiryDate, ProductionDate, Quantity, UnitCost, Status
        ) VALUES (
            p_ProductID, p_WarehouseID, p_ZoneID, p_BatchNumber, p_SerialNumber,
            p_ExpiryDate, p_ProductionDate, p_Quantity, p_UnitCost, 'Available'
        );
        
        SET v_InventoryID = LAST_INSERT_ID();
    END IF;
    
    -- ثبت تراکنش
    INSERT INTO InventoryTransactions (
        ProductID, WarehouseID, ZoneID, TransactionType, ReferenceID, ReferenceNumber,
        QuantityIn, UnitCost, TotalCost, NewQuantity, NewUnitCost, Remarks
    ) VALUES (
        p_ProductID, p_WarehouseID, p_ZoneID, 'PurchaseReceipt', p_ReferenceID, p_ReferenceNumber,
        p_Quantity, p_UnitCost, p_Quantity * p_UnitCost, 
        v_ExistingQuantity + p_Quantity, v_ExistingUnitCost,
        'Goods receipt'
    );
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateIssueRequest(
    IN p_CompanyID INT,
    IN p_IssueDate DATE,
    IN p_RequestedBy INT,
    IN p_DepartmentID INT,
    IN p_Purpose ENUM('Production', 'Sales', 'Maintenance', 'Sample', 'InternalUse'),
    IN p_Priority ENUM('Low', 'Medium', 'High', 'Urgent'),
    IN p_RequiredDate DATE,
    IN p_Notes TEXT,
    IN p_Items JSON,
    OUT p_IssueID INT,
    OUT p_IssueNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_TotalQuantity DECIMAL(12,3) DEFAULT 0;
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_ProductID INT;
    DECLARE v_RequestedQuantity DECIMAL(12,3);
    DECLARE v_AvailableStock DECIMAL(12,3);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد درخواست خروج';
        SET p_IssueID = -1;
        SET p_IssueNumber = '';
    END;
    
    START TRANSACTION;
    
    -- تولید شماره درخواست
    SET p_IssueNumber = fn_GenerateIssueNumber(p_CompanyID);
    
    -- ایجاد درخواست
    INSERT INTO IssueRequests (
        IssueNumber, IssueDate, RequestedBy, DepartmentID, Purpose,
        Priority, RequiredDate, Notes
    ) VALUES (
        p_IssueNumber, p_IssueDate, p_RequestedBy, p_DepartmentID, p_Purpose,
        p_Priority, p_RequiredDate, p_Notes
    );
    
    SET p_IssueID = LAST_INSERT_ID();
    
    -- پردازش اقلام
    SET v_ItemCount = JSON_LENGTH(p_Items);
    SET i = 0;
    
    WHILE i < v_ItemCount DO
        SET v_ProductID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProductID'));
        SET v_RequestedQuantity = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].RequestedQuantity'));
        
        -- بررسی موجودی
        SELECT COALESCE(SUM(AvailableQuantity), 0) INTO v_AvailableStock
        FROM Inventory
        WHERE ProductID = v_ProductID;
        
        IF v_AvailableStock < v_RequestedQuantity THEN
            SET p_ResultMessage = CONCAT('موجودی ناکافی برای محصول: ', CAST(v_ProductID AS CHAR));
            SET p_IssueID = -1;
            ROLLBACK;
        ELSE
            -- ایجاد آیتم درخواست
            INSERT INTO IssueRequestItems (
                IssueID, ProductID, RequestedQuantity, RequiredDate
            ) VALUES (
                p_IssueID, v_ProductID, v_RequestedQuantity, p_RequiredDate
            );
            
            SET v_TotalQuantity = v_TotalQuantity + v_RequestedQuantity;
            SET i = i + 1;
        END IF;
    END WHILE;
    
    -- به‌روزرسانی مقادیر کل
    UPDATE IssueRequests 
    SET TotalQuantity = v_TotalQuantity
    WHERE IssueID = p_IssueID;
    
    SET p_ResultMessage = 'درخواست خروج با موفقیت ایجاد شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateIssueVoucher(
    IN p_IssueID INT,
    IN p_VoucherDate DATE,
    IN p_IssuedBy INT,
    IN p_WarehouseID INT,
    IN p_IssuedTo NVARCHAR(200),
    IN p_DepartmentID INT,
    IN p_VoucherType ENUM('Production', 'Sales', 'Maintenance', 'Sample', 'InternalUse'),
    IN p_Items JSON,
    OUT p_VoucherID INT,
    OUT p_VoucherNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_TotalQuantity DECIMAL(12,3) DEFAULT 0;
    DECLARE v_TotalValue DECIMAL(15,2) DEFAULT 0;
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_IssueItemID INT;
    DECLARE v_ProductID INT;
    DECLARE v_IssuedQuantity DECIMAL(12,3);
    DECLARE v_UnitCost DECIMAL(12,2);
    DECLARE v_BatchNumber VARCHAR(100);
    DECLARE v_SerialNumber VARCHAR(100);
    DECLARE v_ZoneID INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد حواله خروج';
        SET p_VoucherID = -1;
        SET p_VoucherNumber = '';
    END;
    
    START TRANSACTION;
    
    -- تولید شماره حواله
    SET p_VoucherNumber = fn_GenerateVoucherNumber(p_WarehouseID);
    
    -- ایجاد حواله
    INSERT INTO IssueVouchers (
        VoucherNumber, IssueID, VoucherDate, IssuedBy, WarehouseID,
        IssuedTo, DepartmentID, VoucherType
    ) VALUES (
        p_VoucherNumber, p_IssueID, p_VoucherDate, p_IssuedBy, p_WarehouseID,
        p_IssuedTo, p_DepartmentID, p_VoucherType
    );
    
    SET p_VoucherID = LAST_INSERT_ID();
    
    -- پردازش اقلام
    SET v_ItemCount = JSON_LENGTH(p_Items);
    SET i = 0;
    
    WHILE i < v_ItemCount DO
        SET v_IssueItemID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].IssueItemID'));
        SET v_ProductID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProductID'));
        SET v_IssuedQuantity = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].IssuedQuantity'));
        SET v_ZoneID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ZoneID'));
        
        -- کسر از موجودی (FIFO)
        CALL sp_IssueStockFIFO(
            v_ProductID, p_WarehouseID, v_ZoneID, v_IssuedQuantity,
            p_VoucherID, v_IssueItemID,
            @v_TotalCost, @v_Success
        );
        
        IF @v_Success = 0 THEN
            SET p_ResultMessage = 'موجودی ناکافی برای صدور حواله';
            SET p_VoucherID = -1;
            ROLLBACK;
        ELSE
            SET v_TotalQuantity = v_TotalQuantity + v_IssuedQuantity;
            SET v_TotalValue = v_TotalValue + @v_TotalCost;
            SET i = i + 1;
        END IF;
    END WHILE;
    
    -- به‌روزرسانی مقادیر کل حواله
    UPDATE IssueVouchers 
    SET TotalQuantity = v_TotalQuantity, TotalValue = v_TotalValue, Status = 'Issued'
    WHERE VoucherID = p_VoucherID;
    
    -- به‌روزرسانی وضعیت درخواست
    UPDATE IssueRequests 
    SET Status = 'Completed'
    WHERE IssueID = p_IssueID;
    
    SET p_ResultMessage = 'حواله خروج با موفقیت ایجاد شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_IssueStockFIFO(
    IN p_ProductID INT,
    IN p_WarehouseID INT,
    IN p_ZoneID INT,
    IN p_Quantity DECIMAL(12,3),
    IN p_VoucherID INT,
    IN p_IssueItemID INT,
    OUT p_TotalCost DECIMAL(15,2),
    OUT p_Success BOOLEAN
)
BEGIN
    DECLARE v_RemainingQty DECIMAL(12,3) DEFAULT p_Quantity;
    DECLARE v_CurrentQty DECIMAL(12,3);
    DECLARE v_IssueQty DECIMAL(12,3);
    DECLARE v_UnitCost DECIMAL(12,2);
    DECLARE v_InventoryID INT;
    DECLARE v_BatchNumber VARCHAR(100);
    DECLARE v_SerialNumber VARCHAR(100);
    DECLARE v_CurrentZoneID INT;
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE cur CURSOR FOR 
        SELECT InventoryID, AvailableQuantity, UnitCost, BatchNumber, SerialNumber, ZoneID
        FROM Inventory 
        WHERE ProductID = p_ProductID 
        AND WarehouseID = p_WarehouseID
        AND (p_ZoneID IS NULL OR ZoneID = p_ZoneID)
        AND AvailableQuantity > 0
        ORDER BY 
            ExpiryDate ASC,
            ProductionDate ASC,
            CreatedAt ASC;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET p_TotalCost = 0;
    SET p_Success = FALSE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_InventoryID, v_CurrentQty, v_UnitCost, v_BatchNumber, v_SerialNumber, v_CurrentZoneID;
        
        IF done OR v_RemainingQty <= 0 THEN
            LEAVE read_loop;
        END IF;
        
        SET v_IssueQty = LEAST(v_CurrentQty, v_RemainingQty);
        
        -- کسر از موجودی
        UPDATE Inventory 
        SET Quantity = Quantity - v_IssueQty
        WHERE InventoryID = v_InventoryID;
        
        -- ایجاد آیتم حواله
        INSERT INTO IssueVoucherItems (
            VoucherID, IssueItemID, ProductID, IssuedQuantity, UnitCost,
            BatchNumber, SerialNumber, ZoneID
        ) VALUES (
            p_VoucherID, p_IssueItemID, p_ProductID, v_IssueQty, v_UnitCost,
            v_BatchNumber, v_SerialNumber, v_CurrentZoneID
        );
        
        -- ثبت تراکنش
        INSERT INTO InventoryTransactions (
            ProductID, WarehouseID, ZoneID, TransactionType, ReferenceID, ReferenceNumber,
            QuantityOut, UnitCost, TotalCost, NewQuantity, Remarks
        ) VALUES (
            p_ProductID, p_WarehouseID, v_CurrentZoneID, 'Issue', p_VoucherID, CONCAT('IV-', p_VoucherID),
            v_IssueQty, v_UnitCost, v_IssueQty * v_UnitCost, v_CurrentQty - v_IssueQty,
            'Stock issued via voucher'
        );
        
        SET p_TotalCost = p_TotalCost + (v_IssueQty * v_UnitCost);
        SET v_RemainingQty = v_RemainingQty - v_IssueQty;
    END LOOP;
    
    CLOSE cur;
    
    IF v_RemainingQty <= 0 THEN
        SET p_Success = TRUE;
    ELSE
        SET p_Success = FALSE;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

-- تابع تولید شماره رسید
CREATE FUNCTION fn_GenerateReceiptNumber(
    p_CompanyID INT,
    p_ReceiptType VARCHAR(20)
) RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_Prefix VARCHAR(5);
    DECLARE v_Year VARCHAR(4);
    DECLARE v_Month VARCHAR(2);
    DECLARE v_Sequence INT;
    
    SET v_Prefix = 'GR';
    SET v_Year = YEAR(CURDATE());
    SET v_Month = LPAD(MONTH(CURDATE()), 2, '0');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(ReceiptNumber, -4) AS UNSIGNED)), 0) + 1 INTO v_Sequence
    FROM GoodsReceipts gr
    INNER JOIN Warehouses w ON gr.WarehouseID = w.WarehouseID
    WHERE w.CompanyID = p_CompanyID
    AND YEAR(gr.ReceiptDate) = v_Year
    AND MONTH(gr.ReceiptDate) = v_Month;
    
    RETURN CONCAT(v_Prefix, v_Year, v_Month, LPAD(v_Sequence, 4, '0'));
END$$

-- تابع تولید شماره درخواست خروج
CREATE FUNCTION fn_GenerateIssueNumber(
    p_CompanyID INT
) RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_Year VARCHAR(4);
    DECLARE v_Month VARCHAR(2);
    DECLARE v_Sequence INT;
    
    SET v_Year = YEAR(CURDATE());
    SET v_Month = LPAD(MONTH(CURDATE()), 2, '0');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(IssueNumber, -4) AS UNSIGNED)), 0) + 1 INTO v_Sequence
    FROM IssueRequests ir
    INNER JOIN Departments d ON ir.DepartmentID = d.DepartmentID
    WHERE d.CompanyID = p_CompanyID
    AND YEAR(ir.IssueDate) = v_Year
    AND MONTH(ir.IssueDate) = v_Month;
    
    RETURN CONCAT('IR', v_Year, v_Month, LPAD(v_Sequence, 4, '0'));
END$$

-- تابع تولید شماره حواله
CREATE FUNCTION fn_GenerateVoucherNumber(
    p_WarehouseID INT
) RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_Year VARCHAR(4);
    DECLARE v_Month VARCHAR(2);
    DECLARE v_Sequence INT;
    
    SET v_Year = YEAR(CURDATE());
    SET v_Month = LPAD(MONTH(CURDATE()), 2, '0');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(VoucherNumber, -4) AS UNSIGNED)), 0) + 1 INTO v_Sequence
    FROM IssueVouchers
    WHERE WarehouseID = p_WarehouseID
    AND YEAR(VoucherDate) = v_Year
    AND MONTH(VoucherDate) = v_Month;
    
    RETURN CONCAT('IV', v_Year, v_Month, LPAD(v_Sequence, 4, '0'));
END$$

DELIMITER ;

<?php
// فراخوانی Stored Procedure برای گرفتن لیست محصولات
$companyId = 1;
$categoryId = null;
$page = 1;
$pageSize = 20;

$query = "CALL sp_GetProducts(?, ?, 1, NULL, ?, ?, @totalRecords, @totalPages)";
$stmt = $conn->prepare($query);
$stmt->bind_param("iiii", $companyId, $categoryId, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

$products = [];
while ($row = $result->fetch_assoc()) {
    $products[] = $row;
}

// گرفتن خروجی‌ها
$output = $conn->query("SELECT @totalRecords as total_records, @totalPages as total_pages");
$pagination = $output->fetch_assoc();

// فراخوانی برای ایجاد محصول جدید
$productData = [
    'CompanyID' => 1,
    'ProductCode' => 'PROD001',
    'ProductName' => 'محصول آزمایشی',
    'CategoryID' => 1,
    'UnitID' => 1,
    'MinStockLevel' => 10,
    'MaxStockLevel' => 100
];

$query = "CALL sp_CreateProduct(?, ?, NULL, ?, NULL, ?, ?, NULL, NULL, 0, 0, ?, ?, ?, ?, 0, 0, 0, NULL, NULL, NULL, @productId, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param(
    "isssidddd",
    $productData['CompanyID'],
    $productData['ProductCode'],
    $productData['ProductName'],
    $productData['CategoryID'],
    $productData['UnitID'],
    $productData['MinStockLevel'],
    $productData['MaxStockLevel'],
    $productData['ReorderPoint'] ?? 0,
    $productData['SafetyStock'] ?? 0
);
$stmt->execute();

// گرفتن نتیجه
$result = $conn->query("SELECT @productId as product_id, @resultMessage as message");
$output = $result->fetch_assoc();

if ($output['product_id'] > 0) {
    echo "محصول با شناسه " . $output['product_id'] . " ایجاد شد";
} else {
    echo "خطا: " . $output['message'];
}
?>