public BulkControlNumbersForEO GetControlNumbersForEO(string officerCode, string productCode, int available)
{
var imisPayment = new ImisPayment(_configuration, _hostingEnvironment, _loggerFactory);
var threshold = Convert.ToInt32(_configuration["PaymentGateWay:MaxControlNumberForEO"]);
var requiredCNs = threshold - available;
if (requiredCNs <= 0)
{
var response = new BulkControlNumbersForEO();
var header = new ControlNumbersForEOHeader
{
Error = (int)Errors.ControlNumbers.ThresholdReached,
ErrorMessage = Errors.ControlNumbers.ThresholdReached.ToString()
};
var controlNumebrs = new List <ControlNumbersForEO>();
response.Header = header;
response.ControlNumbers = controlNumebrs;
return(response);
}
return(imisPayment.GetControlNumbersForEO(officerCode, productCode, requiredCNs));
}
public override BulkControlNumbersForEO GetControlNumbersForEO(string officerCode, string productCode, int count)
{
var bulkControlNumbers = new BulkControlNumbersForEO();
var header = new ControlNumbersForEOHeader();
var controlNumbers = new List <ControlNumbersForEO>();
var sSQL = $@"
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
DECLARE @dt TABLE
(
ControlNumberId INT,
BillId INT,
ProductCode NVARCHAR(50),
OfficerCode NVARCHAR(50),
PhoneNumber NVARCHAR(50),
ControlNumber NVARCHAR(15),
Amount DECIMAL(18, 2)
)
INSERT INTO @dt(ControlNumberId, BillId, ProductCode, OfficerCode, PhoneNumber, ControlNumber, Amount)
SELECT TOP {count} CN.ControlNumberID, CN.[PaymentID] BillId, PD.ProductCode, @OfficerCode OfficerCode, O.Phone PhoneNumber, CN.[ControlNumber], PD.ExpectedAmount Amount
FROM tblControlNumber CN
INNER JOIN tblPaymentDetails PD ON CN.PaymentID = PD.PaymentID
INNER JOIN tblPayment P ON PD.PaymentID = P.PaymentID
LEFT OUTER JOIN tblOfficer O ON Code = @OfficerCode
WHERE CN.ControlNumber IS NOT NULL
AND PD.ProductCode = @ProductCode
AND P.OfficerCode IS NULL
AND CN.ValidityTo IS NULL
AND PD.ValidityTo IS NULL
AND P.ValidityTo IS NULL
AND O.ValidityTo IS NULL;
UPDATE P SET OfficerCode = @OfficerCode, PhoneNumber = dt.PhoneNumber
FROM @dt dt
INNER JOIN tblPayment P ON P.PaymentId = dt.BillId;
SELECT ControlNumberId, BillId, ProductCode, OfficerCode, PhoneNumber, ControlNumber, Amount FROM @dt;
COMMIT TRANSACTION;";
var dh = new DataHelper(config);
SqlParameter[] parameters =
{
new SqlParameter("@OfficerCode", officerCode),
new SqlParameter("@ProductCode", productCode)
};
var dt = dh.GetDataTable(sSQL, parameters, CommandType.Text);
foreach (DataRow dr in dt.Rows)
{
var controlNumber = new ControlNumbersForEO
{
ControlNumberId = Convert.ToInt32(dr["ControlNumberID"]),
BillId = Convert.ToInt32(dr["BillId"]),
ProductCode = dr["ProductCode"].ToString(),
OfficerCode = dr["OfficerCode"].ToString(),
PhoneNumber = dr["PhoneNumber"].ToString(),
ControlNumber = dr["ControlNumber"].ToString(),
Amount = (decimal)dr["Amount"],
};
controlNumbers.Add(controlNumber);
}
// Prepare the header based on the result
if (dt.Rows.Count == 0)
{
header.Error = (int)Errors.ControlNumbers.NoControlNumberAvailable;
header.ErrorMessage = Errors.ControlNumbers.NoControlNumberAvailable.ToString();
}
else
{
header.Error = (int)Errors.ControlNumbers.Success;
header.ErrorMessage = Errors.ControlNumbers.Success.ToString();
}
bulkControlNumbers.Header = header;
bulkControlNumbers.ControlNumbers = controlNumbers;
return(bulkControlNumbers);
}
