public bool SelfInvoiceDelete(SelfInvoicesMaster selfInvoice)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
var selfInvoices = conn.Query <SelfInvoices>("SELECT * FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId AND CashFlowId IS NULL", new
{
selfInvoice.InvoiceGroupId
});
var totInCashFlow = selfInvoices != null?selfInvoices.Count() : 0;
if (totInCashFlow == 0)
{
return(false);
}
conn.Execute("DELETE FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId",
new
{
selfInvoice.InvoiceGroupId
});
}
return(true);
}
public void SelfInvoiceAddToCashFlow(SelfInvoicesMaster selfInvoice)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
var selfInvoices =
conn.Query <SelfInvoices>("SELECT * FROM SelfInvoices WHERE InvoiceGroupId = @invoiceGroupId", new
{
invoiceGroupId = selfInvoice.InvoiceGroupId
}).ToList();
var invoiceYear = selfInvoices[0].InvoiceYear;
var common =
conn.Query <Common>("SELECT * FROM Common WHERE WorkYear = @WorkYear", new { WorkYear = invoiceYear })
.SingleOrDefault();
long invoiceNumber = 1;
if (common != null)
{
invoiceNumber = common.CurrentAvailableInvoiceNumber;
}
else
{
conn.Execute(
"INSERT INTO Common (CurrentAvailableInvoiceNumber, WorkYear) VALUES (@CurrentAvailableInvoiceNumber, @WorkYear)",
new { invoiceNumber, invoiceYear });
}
foreach (var selfInv in selfInvoices)
{
conn.Execute(
"INSERT INTO CashFlow (Cash, Description, FlowDate, CashFlowType) VALUES (@Cash, @Description, @FlowDate, @CashFlowType)",
new
{
Cash = selfInv.InvoiceCost,
Description =
string.Format("Pagata autofattura {0}/{1}", invoiceNumber,
selfInv.InvoiceYear.ToString(CultureInfo.InvariantCulture).Substring(2)),
FlowDate = selfInv.InvoiceDate,
CashFlowType = "SelfInvoice"
});
var id = conn.Query("SELECT @@IDENTITY AS id").SingleOrDefault();
if (id != null)
{
conn.Execute(
"UPDATE SelfInvoices SET InCashFlow = 1, CashFlowId = @CashFlowId, InvoiceNumber = @InvoiceNumber WHERE InvoiceGroupId = @InvoiceGroupId AND InvoiceId = @InvoiceId",
new
{
CashFlowId = (long)id.id,
selfInv.InvoiceGroupId,
selfInv.InvoiceId,
InvoiceNumber = invoiceNumber
});
}
invoiceNumber++;
}
conn.Execute("UPDATE Common SET CurrentAvailableInvoiceNumber = @CurrentAvailableInvoiceNumber WHERE WorkYear = @WorkYear",
new { CurrentAvailableInvoiceNumber = invoiceNumber, WorkYear = invoiceYear });
}
}