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 }); } }