Stored Procedure برای سامانه مدیریت حسابداری

DELIMITER $$

CREATE PROCEDURE sp_GetChartOfAccounts(
    IN p_CompanyID INT,
    IN p_AccountType VARCHAR(20),
    IN p_IsActive BOOLEAN,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM ChartOfAccounts 
    WHERE CompanyID = p_CompanyID 
    AND (p_AccountType IS NULL OR AccountType = p_AccountType)
    AND IsActive = p_IsActive;
    
    -- گرفتن لیست با صفحه‌بندی
    SELECT 
        a.AccountID,
        a.AccountCode,
        a.AccountName,
        a.AccountNameEnglish,
        a.AccountType,
        a.NormalBalance,
        a.IsDetailAccount,
        a.IsCashAccount,
        a.IsBankAccount,
        a.IsReceivableAccount,
        a.IsPayableAccount,
        a.CurrentBalanceDebit,
        a.CurrentBalanceCredit,
        (a.CurrentBalanceDebit - a.CurrentBalanceCredit) as Balance,
        a.IsActive,
        g.GroupName,
        p.AccountCode as ParentAccountCode,
        p.AccountName as ParentAccountName,
        c.CurrencyCode
    FROM ChartOfAccounts a
    LEFT JOIN AccountGroups g ON a.GroupID = g.GroupID
    LEFT JOIN ChartOfAccounts p ON a.ParentAccountID = p.AccountID
    LEFT JOIN Currencies c ON a.CurrencyID = c.CurrencyID
    WHERE a.CompanyID = p_CompanyID 
    AND (p_AccountType IS NULL OR a.AccountType = p_AccountType)
    AND a.IsActive = p_IsActive
    ORDER BY a.AccountCode
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAccountInfo(
    IN p_AccountID INT
)
BEGIN
    SELECT 
        a.*,
        g.GroupName,
        g.AccountType as GroupAccountType,
        p.AccountCode as ParentAccountCode,
        p.AccountName as ParentAccountName,
        c.CurrencyCode,
        c.CurrencyName,
        (a.CurrentBalanceDebit - a.CurrentBalanceCredit) as Balance,
        CASE a.NormalBalance
            WHEN 'Debit' THEN (a.CurrentBalanceDebit - a.CurrentBalanceCredit)
            ELSE (a.CurrentBalanceCredit - a.CurrentBalanceDebit)
        END as SignedBalance
    FROM ChartOfAccounts a
    LEFT JOIN AccountGroups g ON a.GroupID = g.GroupID
    LEFT JOIN ChartOfAccounts p ON a.ParentAccountID = p.AccountID
    LEFT JOIN Currencies c ON a.CurrencyID = c.CurrencyID
    WHERE a.AccountID = p_AccountID;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateAccount(
    IN p_CompanyID INT,
    IN p_AccountCode VARCHAR(20),
    IN p_AccountName NVARCHAR(200),
    IN p_AccountNameEnglish VARCHAR(200),
    IN p_GroupID INT,
    IN p_ParentAccountID INT,
    IN p_AccountType ENUM('Asset', 'Liability', 'Equity', 'Income', 'Expense'),
    IN p_NormalBalance ENUM('Debit', 'Credit'),
    IN p_IsDetailAccount BOOLEAN,
    IN p_IsCashAccount BOOLEAN,
    IN p_IsBankAccount BOOLEAN,
    IN p_CurrencyID INT,
    IN p_Notes TEXT,
    OUT p_AccountID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_AccountLevel INT DEFAULT 1;
    DECLARE v_Count INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'Error occurred while creating account';
        SET p_AccountID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد حساب
    SELECT COUNT(*) INTO v_Count 
    FROM ChartOfAccounts 
    WHERE CompanyID = p_CompanyID AND AccountCode = p_AccountCode;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'Account code already exists';
        SET p_AccountID = -1;
        ROLLBACK;
    ELSE
        -- تعیین سطح حساب
        IF p_ParentAccountID IS NOT NULL THEN
            SELECT AccountLevel + 1 INTO v_AccountLevel 
            FROM ChartOfAccounts 
            WHERE AccountID = p_ParentAccountID;
        END IF;
        
        -- ایجاد حساب
        INSERT INTO ChartOfAccounts (
            CompanyID, AccountCode, AccountName, AccountNameEnglish,
            GroupID, ParentAccountID, AccountLevel, AccountType, NormalBalance,
            IsDetailAccount, IsCashAccount, IsBankAccount, CurrencyID, Notes
        ) VALUES (
            p_CompanyID, p_AccountCode, p_AccountName, p_AccountNameEnglish,
            p_GroupID, p_ParentAccountID, v_AccountLevel, p_AccountType, p_NormalBalance,
            p_IsDetailAccount, p_IsCashAccount, p_IsBankAccount, p_CurrencyID, p_Notes
        );
        
        SET p_AccountID = LAST_INSERT_ID();
        SET p_ResultMessage = 'Account created successfully';
        
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateAccount(
    IN p_AccountID INT,
    IN p_AccountName NVARCHAR(200),
    IN p_AccountNameEnglish VARCHAR(200),
    IN p_GroupID INT,
    IN p_ParentAccountID INT,
    IN p_AccountType ENUM('Asset', 'Liability', 'Equity', 'Income', 'Expense'),
    IN p_NormalBalance ENUM('Debit', 'Credit'),
    IN p_IsDetailAccount BOOLEAN,
    IN p_IsCashAccount BOOLEAN,
    IN p_IsBankAccount BOOLEAN,
    IN p_CurrencyID INT,
    IN p_Notes TEXT,
    IN p_IsActive BOOLEAN,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_AccountLevel INT DEFAULT 1;
    DECLARE v_OldParentID INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'Error occurred while updating account';
    END;
    
    START TRANSACTION;
    
    -- گرفتن Parent قبلی
    SELECT ParentAccountID INTO v_OldParentID 
    FROM ChartOfAccounts WHERE AccountID = p_AccountID;
    
    -- بررسی تغییر Parent
    IF (v_OldParentID IS NULL AND p_ParentAccountID IS NOT NULL) OR 
       (v_OldParentID IS NOT NULL AND p_ParentAccountID IS NULL) OR
       (v_OldParentID != p_ParentAccountID) THEN
        
        -- تعیین سطح جدید
        IF p_ParentAccountID IS NOT NULL THEN
            SELECT AccountLevel + 1 INTO v_AccountLevel 
            FROM ChartOfAccounts 
            WHERE AccountID = p_ParentAccountID;
        ELSE
            SET v_AccountLevel = 1;
        END IF;
        
        -- به‌روزرسانی سطح حساب و فرزندانش
        UPDATE ChartOfAccounts 
        SET AccountLevel = v_AccountLevel 
        WHERE AccountID = p_AccountID;
        
        -- به‌روزرسانی سطح حساب‌های فرزند (اگر وجود داشته باشند)
        CALL sp_UpdateAccountLevels(p_AccountID, v_AccountLevel + 1);
    END IF;
    
    -- به‌روزرسانی حساب
    UPDATE ChartOfAccounts 
    SET 
        AccountName = p_AccountName,
        AccountNameEnglish = p_AccountNameEnglish,
        GroupID = p_GroupID,
        ParentAccountID = p_ParentAccountID,
        AccountType = p_AccountType,
        NormalBalance = p_NormalBalance,
        IsDetailAccount = p_IsDetailAccount,
        IsCashAccount = p_IsCashAccount,
        IsBankAccount = p_IsBankAccount,
        CurrencyID = p_CurrencyID,
        Notes = p_Notes,
        IsActive = p_IsActive,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE AccountID = p_AccountID;
    
    SET p_ResultMessage = 'Account updated successfully';
    COMMIT;
END$$

DELIMITER ;

-- Stored Procedure کمکی برای به‌روزرسانی سطوح حساب‌های فرزند
DELIMITER $$

CREATE PROCEDURE sp_UpdateAccountLevels(
    IN p_ParentAccountID INT,
    IN p_NewLevel INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_ChildAccountID INT;
    
    -- Cursor برای گرفتن تمام حساب‌های فرزند
    DECLARE cur CURSOR FOR 
        SELECT AccountID 
        FROM ChartOfAccounts 
        WHERE ParentAccountID = p_ParentAccountID;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_ChildAccountID;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- به‌روزرسانی سطح حساب فرزند
        UPDATE ChartOfAccounts 
        SET AccountLevel = p_NewLevel 
        WHERE AccountID = v_ChildAccountID;
        
        -- بازگشت برای به‌روزرسانی نوه‌ها
        CALL sp_UpdateAccountLevels(v_ChildAccountID, p_NewLevel + 1);
    END LOOP;
    
    CLOSE cur;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAccountingVouchers(
    IN p_CompanyID INT,
    IN p_PeriodID INT,
    IN p_VoucherType VARCHAR(20),
    IN p_Status VARCHAR(20),
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalDebit DECIMAL(15,2),
    OUT p_TotalCredit DECIMAL(15,2)
)
BEGIN
    -- محاسبه رکوردهای کل و مجموع‌ها
    SELECT 
        COUNT(*),
        COALESCE(SUM(TotalDebit), 0),
        COALESCE(SUM(TotalCredit), 0)
    INTO p_TotalRecords, p_TotalDebit, p_TotalCredit
    FROM AccountingVouchers 
    WHERE CompanyID = p_CompanyID 
    AND (p_PeriodID IS NULL OR PeriodID = p_PeriodID)
    AND (p_VoucherType IS NULL OR VoucherType = p_VoucherType)
    AND (p_Status IS NULL OR Status = p_Status)
    AND (p_StartDate IS NULL OR VoucherDate >= p_StartDate)
    AND (p_EndDate IS NULL OR VoucherDate <= p_EndDate);
    
    -- گرفتن لیست اسناد
    SELECT 
        v.VoucherID,
        v.VoucherNumber,
        v.VoucherDate,
        v.VoucherType,
        v.ReferenceNumber,
        v.Description,
        v.TotalDebit,
        v.TotalCredit,
        v.Status,
        v.CurrencyID,
        v.ExchangeRate,
        p.PeriodName,
        c.CurrencyCode,
        prep.FirstName as PreparedByFirstName,
        prep.LastName as PreparedByLastName,
        app.FirstName as ApprovedByFirstName,
        app.LastName as ApprovedByLastName,
        DATEDIFF(v.CreatedAt, CURRENT_TIMESTAMP) as DaysAgo
    FROM AccountingVouchers v
    INNER JOIN FiscalPeriods p ON v.PeriodID = p.PeriodID
    INNER JOIN Currencies c ON v.CurrencyID = c.CurrencyID
    LEFT JOIN Users prep ON v.PreparedBy = prep.UserID
    LEFT JOIN Users app ON v.ApprovedBy = app.UserID
    WHERE v.CompanyID = p_CompanyID 
    AND (p_PeriodID IS NULL OR v.PeriodID = p_PeriodID)
    AND (p_VoucherType IS NULL OR v.VoucherType = p_VoucherType)
    AND (p_Status IS NULL OR v.Status = p_Status)
    AND (p_StartDate IS NULL OR v.VoucherDate >= p_StartDate)
    AND (p_EndDate IS NULL OR v.VoucherDate <= p_EndDate)
    ORDER BY v.VoucherDate DESC, v.VoucherID DESC
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetVoucherDetails(
    IN p_VoucherID INT
)
BEGIN
    -- اطلاعات اصلی سند
    SELECT 
        v.*,
        p.PeriodName,
        c.CurrencyCode,
        prep.FirstName as PreparedByFirstName,
        prep.LastName as PreparedByLastName,
        app.FirstName as ApprovedByFirstName,
        app.LastName as ApprovedByLastName,
        post.FirstName as PostedByFirstName,
        post.LastName as PostedByLastName
    FROM AccountingVouchers v
    INNER JOIN FiscalPeriods p ON v.PeriodID = p.PeriodID
    INNER JOIN Currencies c ON v.CurrencyID = c.CurrencyID
    LEFT JOIN Users prep ON v.PreparedBy = prep.UserID
    LEFT JOIN Users app ON v.ApprovedBy = app.UserID
    LEFT JOIN Users post ON v.PostedBy = post.UserID
    WHERE v.VoucherID = p_VoucherID;
    
    -- سطرهای سند
    SELECT 
        vi.VoucherItemID,
        vi.AccountID,
        vi.Description,
        vi.DebitAmount,
        vi.CreditAmount,
        vi.BaseDebitAmount,
        vi.BaseCreditAmount,
        vi.CostCenterID,
        vi.ProjectID,
        vi.CustomerID,
        vi.SupplierID,
        vi.EmployeeID,
        vi.ReferenceType,
        vi.ReferenceID,
        vi.LineNumber,
        a.AccountCode,
        a.AccountName,
        cc.CostCenterCode,
        cc.CostCenterName,
        p.ProjectCode,
        p.ProjectName,
        cust.CustomerCode,
        cust.CustomerName,
        supp.SupplierCode,
        supp.SupplierName,
        emp.FirstName as EmployeeFirstName,
        emp.LastName as EmployeeLastName
    FROM VoucherItems vi
    INNER JOIN ChartOfAccounts a ON vi.AccountID = a.AccountID
    LEFT JOIN CostCenters cc ON vi.CostCenterID = cc.CostCenterID
    LEFT JOIN Projects p ON vi.ProjectID = p.ProjectID
    LEFT JOIN Customers cust ON vi.CustomerID = cust.CustomerID
    LEFT JOIN Suppliers supp ON vi.SupplierID = supp.SupplierID
    LEFT JOIN Employees emp ON vi.EmployeeID = emp.EmployeeID
    WHERE vi.VoucherID = p_VoucherID
    ORDER BY vi.LineNumber;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateVoucher(
    IN p_CompanyID INT,
    IN p_VoucherDate DATE,
    IN p_VoucherType ENUM('General', 'Receipt', 'Payment', 'Sales', 'Purchase', 'Journal'),
    IN p_PeriodID INT,
    IN p_ReferenceNumber VARCHAR(100),
    IN p_ReferenceDate DATE,
    IN p_Description TEXT,
    IN p_CurrencyID INT,
    IN p_ExchangeRate DECIMAL(15,6),
    IN p_PreparedBy INT,
    IN p_Items JSON, -- آرایه‌ای از سطرهای سند
    OUT p_VoucherID INT,
    OUT p_VoucherNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_TotalDebit DECIMAL(15,2) DEFAULT 0;
    DECLARE v_TotalCredit DECIMAL(15,2) DEFAULT 0;
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_AccountID INT;
    DECLARE v_ItemDescription TEXT;
    DECLARE v_DebitAmount DECIMAL(15,2);
    DECLARE v_CreditAmount DECIMAL(15,2);
    DECLARE v_CostCenterID INT;
    DECLARE v_ProjectID INT;
    DECLARE v_CustomerID INT;
    DECLARE v_SupplierID INT;
    DECLARE v_EmployeeID INT;
    DECLARE v_ReferenceType VARCHAR(20);
    DECLARE v_ReferenceID INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'Error occurred while creating voucher';
        SET p_VoucherID = -1;
        SET p_VoucherNumber = '';
    END;
    
    START TRANSACTION;
    
    -- اعتبارسنجی دوره مالی
    IF NOT EXISTS (SELECT 1 FROM FiscalPeriods WHERE PeriodID = p_PeriodID AND Status = 'Open') THEN
        SET p_ResultMessage = 'Fiscal period is not open or does not exist';
        SET p_VoucherID = -1;
        SET p_VoucherNumber = '';
        ROLLBACK;
    ELSE
        -- محاسبه جمع سطرها
        SET v_ItemCount = JSON_LENGTH(p_Items);
        SET i = 0;
        
        WHILE i < v_ItemCount DO
            SET v_DebitAmount = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].DebitAmount'));
            SET v_CreditAmount = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].CreditAmount'));
            
            SET v_TotalDebit = v_TotalDebit + v_DebitAmount;
            SET v_TotalCredit = v_TotalCredit + v_CreditAmount;
            
            SET i = i + 1;
        END WHILE;
        
        -- بررسی توازن بدهکار و بستانکار
        IF ABS(v_TotalDebit - v_TotalCredit) > 0.01 THEN
            SET p_ResultMessage = 'Debit and credit totals are not equal';
            SET p_VoucherID = -1;
            SET p_VoucherNumber = '';
            ROLLBACK;
        ELSE
            -- تولید شماره سند
            SET p_VoucherNumber = fn_GenerateVoucherNumber(p_CompanyID, p_VoucherType, p_VoucherDate);
            
            -- ایجاد سند
            INSERT INTO AccountingVouchers (
                CompanyID, VoucherNumber, VoucherDate, VoucherType, PeriodID,
                ReferenceNumber, ReferenceDate, Description, TotalDebit, TotalCredit,
                CurrencyID, ExchangeRate, PreparedBy
            ) VALUES (
                p_CompanyID, p_VoucherNumber, p_VoucherDate, p_VoucherType, p_PeriodID,
                p_ReferenceNumber, p_ReferenceDate, p_Description, v_TotalDebit, v_TotalCredit,
                p_CurrencyID, p_ExchangeRate, p_PreparedBy
            );
            
            SET p_VoucherID = LAST_INSERT_ID();
            
            -- ایجاد سطرهای سند
            SET i = 0;
            WHILE i < v_ItemCount DO
                SET v_AccountID = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].AccountID'));
                SET v_ItemDescription = JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].Description')));
                SET v_DebitAmount = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].DebitAmount'));
                SET v_CreditAmount = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].CreditAmount'));
                SET v_CostCenterID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].CostCenterID')), 'null');
                SET v_ProjectID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProjectID')), 'null');
                SET v_CustomerID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].CustomerID')), 'null');
                SET v_SupplierID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].SupplierID')), 'null');
                SET v_EmployeeID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].EmployeeID')), 'null');
                SET v_ReferenceType = NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ReferenceType'))), 'null');
                SET v_ReferenceID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ReferenceID')), 'null');
                
                INSERT INTO VoucherItems (
                    VoucherID, AccountID, Description, DebitAmount, CreditAmount,
                    CurrencyID, ExchangeRate, CostCenterID, ProjectID,
                    CustomerID, SupplierID, EmployeeID, ReferenceType, ReferenceID, LineNumber
                ) VALUES (
                    p_VoucherID, v_AccountID, v_ItemDescription, v_DebitAmount, v_CreditAmount,
                    p_CurrencyID, p_ExchangeRate, v_CostCenterID, v_ProjectID,
                    v_CustomerID, v_SupplierID, v_EmployeeID, v_ReferenceType, v_ReferenceID, i + 1
                );
                
                SET i = i + 1;
            END WHILE;
            
            SET p_ResultMessage = 'Voucher created successfully';
            COMMIT;
        END IF;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION fn_GenerateVoucherNumber(
    p_CompanyID INT,
    p_VoucherType VARCHAR(20),
    p_VoucherDate DATE
) 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;
    
    -- تعیین پیشوند بر اساس نوع سند
    CASE p_VoucherType
        WHEN 'General' THEN SET v_Prefix = 'GV';
        WHEN 'Receipt' THEN SET v_Prefix = 'RC';
        WHEN 'Payment' THEN SET v_Prefix = 'PY';
        WHEN 'Sales' THEN SET v_Prefix = 'SL';
        WHEN 'Purchase' THEN SET v_Prefix = 'PR';
        WHEN 'Journal' THEN SET v_Prefix = 'JV';
        ELSE SET v_Prefix = 'GV';
    END CASE;
    
    SET v_Year = YEAR(p_VoucherDate);
    SET v_Month = LPAD(MONTH(p_VoucherDate), 2, '0');
    
    -- پیدا کردن آخرین شماره توالی
    SELECT COALESCE(MAX(CAST(SUBSTRING(VoucherNumber, -4) AS UNSIGNED)), 0) + 1 INTO v_Sequence
    FROM AccountingVouchers
    WHERE CompanyID = p_CompanyID 
    AND VoucherType = p_VoucherType
    AND YEAR(VoucherDate) = v_Year
    AND MONTH(VoucherDate) = v_Month;
    
    RETURN CONCAT(v_Prefix, v_Year, v_Month, LPAD(v_Sequence, 4, '0'));
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_PostVoucher(
    IN p_VoucherID INT,
    IN p_PostedBy INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_CompanyID INT;
    DECLARE v_PeriodID INT;
    DECLARE v_CurrentStatus VARCHAR(20);
    DECLARE v_TotalDebit DECIMAL(15,2);
    DECLARE v_TotalCredit DECIMAL(15,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'Error occurred while posting voucher';
    END;
    
    START TRANSACTION;
    
    -- گرفتن اطلاعات سند
    SELECT CompanyID, PeriodID, Status, TotalDebit, TotalCredit
    INTO v_CompanyID, v_PeriodID, v_CurrentStatus, v_TotalDebit, v_TotalCredit
    FROM AccountingVouchers
    WHERE VoucherID = p_VoucherID;
    
    -- بررسی وضعیت سند
    IF v_CurrentStatus != 'Draft' THEN
        SET p_ResultMessage = 'Voucher is not in draft status';
        ROLLBACK;
    ELSEIF ABS(v_TotalDebit - v_TotalCredit) > 0.01 THEN
        SET p_ResultMessage = 'Debit and credit totals are not equal';
        ROLLBACK;
    ELSE
        -- به‌روزرسانی موجودی حساب‌ها
        UPDATE ChartOfAccounts ca
        INNER JOIN VoucherItems vi ON ca.AccountID = vi.AccountID
        SET 
            ca.CurrentBalanceDebit = ca.CurrentBalanceDebit + vi.BaseDebitAmount,
            ca.CurrentBalanceCredit = ca.CurrentBalanceCredit + vi.BaseCreditAmount
        WHERE vi.VoucherID = p_VoucherID;
        
        -- به‌روزرسانی وضعیت سند
        UPDATE AccountingVouchers 
        SET 
            Status = 'Posted',
            PostedBy = p_PostedBy,
            PostedAt = CURRENT_TIMESTAMP
        WHERE VoucherID = p_VoucherID;
        
        SET p_ResultMessage = 'Voucher posted successfully';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetSalesInvoices(
    IN p_CompanyID INT,
    IN p_CustomerID INT,
    IN p_Status VARCHAR(20),
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalAmount DECIMAL(15,2)
)
BEGIN
    -- محاسبه رکوردهای کل و مجموع مبالغ
    SELECT 
        COUNT(*),
        COALESCE(SUM(TotalAmount), 0)
    INTO p_TotalRecords, p_TotalAmount
    FROM SalesInvoices 
    WHERE CompanyID = p_CompanyID 
    AND (p_CustomerID IS NULL OR CustomerID = p_CustomerID)
    AND (p_Status IS NULL OR Status = p_Status)
    AND (p_StartDate IS NULL OR InvoiceDate >= p_StartDate)
    AND (p_EndDate IS NULL OR InvoiceDate <= p_EndDate);
    
    -- گرفتن لیست فاکتورها
    SELECT 
        si.InvoiceID,
        si.InvoiceNumber,
        si.InvoiceDate,
        si.CustomerID,
        si.TotalAmount,
        si.Status,
        si.DueDate,
        si.CurrencyID,
        si.ExchangeRate,
        c.CustomerCode,
        c.CustomerName,
        curr.CurrencyCode,
        u.FirstName as CreatedByFirstName,
        u.LastName as CreatedByLastName,
        DATEDIFF(si.DueDate, CURRENT_DATE) as DaysToDue,
        CASE 
            WHEN si.Status = 'Issued' AND si.DueDate < CURRENT_DATE THEN 'Overdue'
            ELSE si.Status
        END as InvoiceStatus
    FROM SalesInvoices si
    INNER JOIN Customers c ON si.CustomerID = c.CustomerID
    INNER JOIN Currencies curr ON si.CurrencyID = curr.CurrencyID
    INNER JOIN Users u ON si.CreatedBy = u.UserID
    WHERE si.CompanyID = p_CompanyID 
    AND (p_CustomerID IS NULL OR si.CustomerID = p_CustomerID)
    AND (p_Status IS NULL OR si.Status = p_Status)
    AND (p_StartDate IS NULL OR si.InvoiceDate >= p_StartDate)
    AND (p_EndDate IS NULL OR si.InvoiceDate <= p_EndDate)
    ORDER BY si.InvoiceDate DESC, si.InvoiceID DESC
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateSalesInvoice(
    IN p_CompanyID INT,
    IN p_InvoiceDate DATE,
    IN p_CustomerID INT,
    IN p_CurrencyID INT,
    IN p_ExchangeRate DECIMAL(15,6),
    IN p_DueDate DATE,
    IN p_PaymentTerms VARCHAR(10),
    IN p_ReceivableAccountID INT,
    IN p_SalesAccountID INT,
    IN p_TaxAccountID INT,
    IN p_ProjectID INT,
    IN p_Notes TEXT,
    IN p_CreatedBy INT,
    IN p_Items JSON,
    OUT p_InvoiceID INT,
    OUT p_InvoiceNumber VARCHAR(50),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_SubTotal DECIMAL(15,2) DEFAULT 0;
    DECLARE v_TaxAmount DECIMAL(15,2) DEFAULT 0;
    DECLARE v_DiscountAmount DECIMAL(15,2) DEFAULT 0;
    DECLARE v_TotalAmount DECIMAL(15,2) DEFAULT 0;
    DECLARE v_ItemCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_ProductID INT;
    DECLARE v_Description TEXT;
    DECLARE v_Quantity DECIMAL(12,3);
    DECLARE v_UnitPrice DECIMAL(15,2);
    DECLARE v_DiscountRate DECIMAL(5,2);
    DECLARE v_DiscountAmountItem DECIMAL(15,2);
    DECLARE v_TaxRate DECIMAL(5,2);
    DECLARE v_TaxAmountItem DECIMAL(15,2);
    DECLARE v_LineTotal DECIMAL(15,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'Error occurred while creating sales invoice';
        SET p_InvoiceID = -1;
        SET p_InvoiceNumber = '';
    END;
    
    START TRANSACTION;
    
    -- محاسبه مبالغ
    SET v_ItemCount = JSON_LENGTH(p_Items);
    SET i = 0;
    
    WHILE i < v_ItemCount DO
        SET v_Quantity = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].Quantity'));
        SET v_UnitPrice = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].UnitPrice'));
        SET v_DiscountRate = COALESCE(NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].DiscountRate')), 'null'), 0);
        SET v_TaxRate = COALESCE(NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].TaxRate')), 'null'), 0);
        
        SET v_DiscountAmountItem = (v_Quantity * v_UnitPrice) * (v_DiscountRate / 100);
        SET v_LineTotal = (v_Quantity * v_UnitPrice) - v_DiscountAmountItem;
        SET v_TaxAmountItem = v_LineTotal * (v_TaxRate / 100);
        
        SET v_SubTotal = v_SubTotal + v_LineTotal;
        SET v_DiscountAmount = v_DiscountAmount + v_DiscountAmountItem;
        SET v_TaxAmount = v_TaxAmount + v_TaxAmountItem;
        
        SET i = i + 1;
    END WHILE;
    
    SET v_TotalAmount = v_SubTotal + v_TaxAmount;
    
    -- تولید شماره فاکتور
    SET p_InvoiceNumber = fn_GenerateInvoiceNumber(p_CompanyID, p_InvoiceDate);
    
    -- ایجاد فاکتور
    INSERT INTO SalesInvoices (
        CompanyID, InvoiceNumber, InvoiceDate, CustomerID, CurrencyID, ExchangeRate,
        SubTotal, TaxAmount, DiscountAmount, TotalAmount, DueDate, PaymentTerms,
        ReceivableAccountID, SalesAccountID, TaxAccountID, ProjectID, Notes, CreatedBy
    ) VALUES (
        p_CompanyID, p_InvoiceNumber, p_InvoiceDate, p_CustomerID, p_CurrencyID, p_ExchangeRate,
        v_SubTotal, v_TaxAmount, v_DiscountAmount, v_TotalAmount, p_DueDate, p_PaymentTerms,
        p_ReceivableAccountID, p_SalesAccountID, p_TaxAccountID, p_ProjectID, p_Notes, p_CreatedBy
    );
    
    SET p_InvoiceID = LAST_INSERT_ID();
    
    -- ایجاد اقلام فاکتور
    SET i = 0;
    WHILE i < v_ItemCount DO
        SET v_ProductID = NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].ProductID')), 'null');
        SET v_Description = JSON_UNQUOTE(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].Description')));
        SET v_Quantity = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].Quantity'));
        SET v_UnitPrice = JSON_EXTRACT(p_Items, CONCAT('$[', i, '].UnitPrice'));
        SET v_DiscountRate = COALESCE(NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].DiscountRate')), 'null'), 0);
        SET v_TaxRate = COALESCE(NULLIF(JSON_EXTRACT(p_Items, CONCAT('$[', i, '].TaxRate')), 'null'), 0);
        
        SET v_DiscountAmountItem = (v_Quantity * v_UnitPrice) * (v_DiscountRate / 100);
        SET v_LineTotal = (v_Quantity * v_UnitPrice) - v_DiscountAmountItem;
        SET v_TaxAmountItem = v_LineTotal * (v_TaxRate / 100);
        
        INSERT INTO SalesInvoiceItems (
            InvoiceID, ProductID, Description, Quantity, UnitPrice,
            DiscountRate, DiscountAmount, TaxRate, TaxAmount, LineTotal
        ) VALUES (
            p_InvoiceID, v_ProductID, v_Description, v_Quantity, v_UnitPrice,
            v_DiscountRate, v_DiscountAmountItem, v_TaxRate, v_TaxAmountItem, v_LineTotal
        );
        
        SET i = i + 1;
    END WHILE;
    
    -- ایجاد سند حسابداری خودکار
    CALL sp_CreateVoucherFromSalesInvoice(p_InvoiceID, p_CreatedBy, @v_VoucherID, @v_Result);
    
    SET p_ResultMessage = 'Sales invoice created successfully';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetTrialBalance(
    IN p_CompanyID INT,
    IN p_PeriodID INT,
    IN p_AsOfDate DATE
)
BEGIN
    IF p_PeriodID IS NULL THEN
        -- پیدا کردن دوره بر اساس تاریخ
        SELECT PeriodID INTO p_PeriodID
        FROM FiscalPeriods 
        WHERE CompanyID = p_CompanyID 
        AND p_AsOfDate BETWEEN StartDate AND EndDate
        LIMIT 1;
    END IF;
    
    SELECT 
        a.AccountID,
        a.AccountCode,
        a.AccountName,
        a.AccountType,
        a.NormalBalance,
        COALESCE(SUM(CASE WHEN vi.DebitAmount > 0 THEN vi.BaseDebitAmount ELSE 0 END), 0) as TotalDebit,
        COALESCE(SUM(CASE WHEN vi.CreditAmount > 0 THEN vi.BaseCreditAmount ELSE 0 END), 0) as TotalCredit,
        CASE 
            WHEN a.NormalBalance = 'Debit' THEN 
                COALESCE(SUM(vi.BaseDebitAmount - vi.BaseCreditAmount), 0)
            ELSE 
                COALESCE(SUM(vi.BaseCreditAmount - vi.BaseDebitAmount), 0)
        END as Balance,
        g.GroupName
    FROM ChartOfAccounts a
    LEFT JOIN VoucherItems vi ON a.AccountID = vi.AccountID
    LEFT JOIN AccountingVouchers v ON vi.VoucherID = v.VoucherID
    LEFT JOIN AccountGroups g ON a.GroupID = g.GroupID
    WHERE a.CompanyID = p_CompanyID 
    AND a.IsActive = 1
    AND v.PeriodID = p_PeriodID 
    AND v.Status = 'Posted'
    GROUP BY a.AccountID, a.AccountCode, a.AccountName, a.AccountType, a.NormalBalance, g.GroupName
    HAVING TotalDebit != 0 OR TotalCredit != 0
    ORDER BY a.AccountCode;
    
    -- جمع کل
    SELECT 
        COALESCE(SUM(CASE WHEN vi.DebitAmount > 0 THEN vi.BaseDebitAmount ELSE 0 END), 0) as GrandTotalDebit,
        COALESCE(SUM(CASE WHEN vi.CreditAmount > 0 THEN vi.BaseCreditAmount ELSE 0 END), 0) as GrandTotalCredit,
        COALESCE(SUM(CASE WHEN vi.DebitAmount > 0 THEN vi.BaseDebitAmount ELSE 0 END), 0) - 
        COALESCE(SUM(CASE WHEN vi.CreditAmount > 0 THEN vi.BaseCreditAmount ELSE 0 END), 0) as Difference
    FROM VoucherItems vi
    INNER JOIN AccountingVouchers v ON vi.VoucherID = v.VoucherID
    WHERE v.CompanyID = p_CompanyID 
    AND v.PeriodID = p_PeriodID 
    AND v.Status = 'Posted';
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetGeneralLedger(
    IN p_CompanyID INT,
    IN p_AccountID INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM VoucherItems vi
    INNER JOIN AccountingVouchers v ON vi.VoucherID = v.VoucherID
    WHERE v.CompanyID = p_CompanyID 
    AND vi.AccountID = p_AccountID
    AND (p_StartDate IS NULL OR v.VoucherDate >= p_StartDate)
    AND (p_EndDate IS NULL OR v.VoucherDate <= p_EndDate)
    AND v.Status = 'Posted';
    
    -- گرفتن سوابق حساب
    SELECT 
        v.VoucherID,
        v.VoucherNumber,
        v.VoucherDate,
        v.VoucherType,
        vi.Description as ItemDescription,
        vi.DebitAmount,
        vi.CreditAmount,
        vi.BaseDebitAmount,
        vi.BaseCreditAmount,
        @RunningBalance := @RunningBalance + (vi.BaseDebitAmount - vi.BaseCreditAmount) as RunningBalance,
        v.ReferenceNumber,
        cc.CostCenterCode,
        cc.CostCenterName,
        p.ProjectCode,
        p.ProjectName
    FROM VoucherItems vi
    INNER JOIN AccountingVouchers v ON vi.VoucherID = v.VoucherID
    LEFT JOIN CostCenters cc ON vi.CostCenterID = cc.CostCenterID
    LEFT JOIN Projects p ON vi.ProjectID = p.ProjectID
    CROSS JOIN (SELECT @RunningBalance := 0) rb
    WHERE v.CompanyID = p_CompanyID 
    AND vi.AccountID = p_AccountID
    AND (p_StartDate IS NULL OR v.VoucherDate >= p_StartDate)
    AND (p_EndDate IS NULL OR v.VoucherDate <= p_EndDate)
    AND v.Status = 'Posted'
    ORDER BY v.VoucherDate, v.VoucherID
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
    
    -- مانده اولیه
    SELECT 
        COALESCE(SUM(vi.BaseDebitAmount - vi.BaseCreditAmount), 0) as OpeningBalance
    FROM VoucherItems vi
    INNER JOIN AccountingVouchers v ON vi.VoucherID = v.VoucherID
    WHERE v.CompanyID = p_CompanyID 
    AND vi.AccountID = p_AccountID
    AND v.VoucherDate < p_StartDate
    AND v.Status = 'Posted';
END$$

DELIMITER ;

<?php
// گرفتن لیست حساب‌ها
$companyId = 1;
$accountType = 'Asset';
$page = 1;
$pageSize = 20;

$query = "CALL sp_GetChartOfAccounts(?, ?, 1, ?, ?, @total)";
$stmt = $conn->prepare($query);
$stmt->bind_param("isii", $companyId, $accountType, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

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

// گرفتن تعداد کل
$countResult = $conn->query("SELECT @total as total");
$total = $countResult->fetch_assoc()['total'];

// ایجاد سند جدید
$voucherData = [
    'CompanyID' => 1,
    'VoucherDate' => '2024-01-15',
    'VoucherType' => 'General',
    'PeriodID' => 1,
    'ReferenceNumber' => 'REF001',
    'Description' => 'سند آزمایشی',
    'CurrencyID' => 1,
    'ExchangeRate' => 1,
    'PreparedBy' => 1,
    'Items' => json_encode([
        [
            'AccountID' => 10101,
            'Description' => 'بدهکار',
            'DebitAmount' => 1000000,
            'CreditAmount' => 0
        ],
        [
            'AccountID' => 10102,
            'Description' => 'بستانکار', 
            'DebitAmount' => 0,
            'CreditAmount' => 1000000
        ]
    ])
];

$query = "CALL sp_CreateVoucher(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, @voucherId, @voucherNumber, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param(
    "ississsddis",
    $voucherData['CompanyID'],
    $voucherData['VoucherDate'],
    $voucherData['VoucherType'],
    $voucherData['PeriodID'],
    $voucherData['ReferenceNumber'],
    $voucherData['ReferenceDate'] ?? null,
    $voucherData['Description'],
    $voucherData['CurrencyID'],
    $voucherData['ExchangeRate'],
    $voucherData['PreparedBy'],
    $voucherData['Items']
);
$stmt->execute();

// گرفتن خروجی‌ها
$output = $conn->query("SELECT @voucherId as voucher_id, @voucherNumber as voucher_number, @resultMessage as message");
$result = $output->fetch_assoc();

echo "Voucher ID: " . $result['voucher_id'];
echo "Voucher Number: " . $result['voucher_number']; 
echo "Message: " . $result['message'];
?>