Busy & Marg ERP RFID Connector | Pharma & FMCG Sync

using System;
using System.Data.Odbc;
using System.Threading.Tasks;

namespace RfidSoftwares.MargBusy.Connector
{
    public class MargBusyDirectDbSync
    {
        private readonly string _connectionString;

        public MargBusyDirectDbSync(string dsnName)
        {
            _connectionString = $"Dsn={dsnName};Uid=admin;Pwd=;";
        }

        // Direct ODBC transaction posting into Marg / BUSY inventory tables with ACID rollback
        public async Task<int> PostStockBatchAsync(string itemCode, int verifiedCount, string batchNo)
        {
            using var conn = new OdbcConnection(_connectionString);
            await conn.OpenAsync();

            using var trans = conn.BeginTransaction();
            try
            {
                using var cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = "UPDATE ItemMaster SET StockQty = StockQty + ? WHERE ItemCode = ? AND BatchNo = ?";
                cmd.Parameters.AddWithValue("StockQty", verifiedCount);
                cmd.Parameters.AddWithValue("ItemCode", itemCode);
                cmd.Parameters.AddWithValue("BatchNo", batchNo);

                int rows = await cmd.ExecuteNonQueryAsync();
                trans.Commit();
                return rows;
            }
            catch
            {
                trans.Rollback();
                throw;
            }
        }
    }
}