private async Task InvokeCreditNoteSP(CreditNoteDto creditNoteData, CreditNote res)
{
//Create the credit note number
await _dbCtx.Database.OpenConnectionAsync();
var connection = _dbCtx.Database.GetDbConnection();
var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SP_Save_CreditNote";
command.Parameters.Add(new MySqlParameter("selectedIdCreditNote", creditNoteData.Id));
command.Parameters.Add(new MySqlParameter("selectedYear", creditNoteData.Year));
command.Parameters.Add(new MySqlParameter("issuedBy", new Guid(_claimPrincipal.Value.Identity.Name)));
var cnNum = (int?)command.ExecuteScalar();
await _dbCtx.Database.CloseConnectionAsync();
if (cnNum.HasValue)
{
var desc = "No error description avaialle";
if (cnNum.Value == 1062)
{
desc = "Duplicate credit note number!!";
}
_dbCtx.CreditNotes.Remove(res);
throw new Exception($"Unable to create ad store credit note number. ERR-NO={cnNum} {desc}");
}
}
public static CreditNoteDto ToDto(this CreditNote e, bool includeReceipt = false)
{
if (e == null)
{
return(null);
}
var res = new CreditNoteDto();
res.Id = e.Id;
res.Description = e.Description;
res.InvoiceNumber = e.InvoiceNumber;
res.CreditNoteNumber = e.CreditNoteNumber;
res.IdReceipt = e.IdReceipt;
res.InvoiceDate = e.InvoiceDate;
res.IssueDate = e.IssueDate;
res.IssuedBy = e.IssuedBy;
res.Owner = e.Owner != null?e.Owner.ToDto() : null;
if (includeReceipt)
{
res.PaymentReceipt = e.PaymentReceipt.ToDto();
}
return(res);
}
private async Task CreateCreditNote(CreditNoteDto creditNoteData)
{
// SPECIFIC FOR SQLite DB
var trx = await _dbCtx.Database.BeginTransactionAsync();
await _dbCtx.Database.OpenConnectionAsync();
var connection = _dbCtx.Database.GetDbConnection();
try {
var command = connection.CreateCommand();
command.CommandText = @"SELECT COALESCE(MAX(number),0)
FROM `creditnote_number`
WHERE `year` = $selectedYear ";
command.Parameters.Add(new SqliteParameter("$selectedYear", creditNoteData.Year));
var creditNoteNumber = int.Parse((await command.ExecuteScalarAsync()).ToString());
creditNoteNumber = creditNoteNumber + 1;
command = connection.CreateCommand();
command.CommandText = @"INSERT INTO `creditnote_number` (
`IdCreditNote`,
`number`,
`year`)
VALUES (
$selectedIdCreditNote,
$cnNumber,
$selectedYear
);";
command.Parameters.Add(new SqliteParameter("$selectedIdCreditNote", creditNoteData.Id));
command.Parameters.Add(new SqliteParameter("$cnNumber", creditNoteNumber));
command.Parameters.Add(new SqliteParameter("$selectedYear", creditNoteData.Year));
await command.ExecuteNonQueryAsync();
command = connection.CreateCommand();
command.CommandText = @" UPDATE `credit_note`
SET `CreditNoteNumber` = $cnNumber,
`IssueDate` = CURRENT_TIMESTAMP,
`IssuedBy` = $issuedBy
WHERE
`Id` = $selectedIdCreditNote" ;
command.Parameters.Add(new SqliteParameter("$cnNumber", $"NC_{creditNoteData.Year}_" + creditNoteNumber.ToString().PadLeft(4, '0')));
command.Parameters.Add(new SqliteParameter("$issuedBy", new Guid(_claimPrincipal.Value.Identity.Name)));
command.Parameters.Add(new SqliteParameter("$selectedIdCreditNote", creditNoteData.Id));
await command.ExecuteNonQueryAsync();
await trx.CommitAsync();
}
catch (Exception ex)
{
await trx.RollbackAsync();
throw ex;
}
}
/// <summary>
/// InvoiceXpress <a href="https://invoicexpress.com/api/simplified-invoices/update">CreditNotes Update</a> Method
/// </summary>
public static void Update(string apiKey, string accountName, int creditNoteId, CreditNoteDto inputData)
{
Rest_Update(apiKey, accountName, creditNoteId, inputData.XmlSerializeToString());
}
/// <summary>
/// InvoiceXpress <a href="https://invoicexpress.com/api/credit-notes/create">CreditNotes Create</a> Method
/// </summary>
public static CreditNoteDto Create(string apiKey, string accountName, CreditNoteDto inputData)
{
HttpResponseInfo result = Rest_Create(apiKey, accountName, inputData.XmlSerializeToString());
return(result.Text.DeserializeXml <CreditNoteDto>());
}
