private void AddInvoiceParameter(NpgsqlCommand command, InvoiceSearchData data) { if (data.From != null) { command.Parameters.Add("from", NpgsqlTypes.NpgsqlDbType.Date); } if (data.To != null) { command.Parameters.Add("to", NpgsqlTypes.NpgsqlDbType.Date); } if (data.Contact != null) { command.Parameters.Add("contact", NpgsqlTypes.NpgsqlDbType.Text); } command.Prepare(); if (data.From != null) { command.Parameters["from"].Value = data.From.Value; } if (data.To != null) { command.Parameters["to"].Value = data.To.Value; } if (data.Contact != null) { command.Parameters["contact"].Value = "%" + data.Contact + "%"; } }
private DataTable SelectInvoices(InvoiceSearchData data) { // variables string from = data.From != null ? "Invoice.Date >= :from" : null; string to = data.To != null ? "Invoice.Date <= :to" : null; string contact = data.Contact != null ? "lower(Contact.UID) LIKE lower(:contact) OR lower(Contact.Name) LIKE lower(:contact) OR lower(Contact.Forename) LIKE lower(:contact) OR lower(Contact.Surname) LIKE lower(:contact)" : null; string query = "SELECT Contact.UID, Contact.Name, Contact.Forename, Contact.Surname, Invoice.fk_Contact, " + "Invoice.ID, Invoice.Date, Invoice.Maturity, Invoice.Comment, Invoice.Message, Invoice.Type " + "FROM Invoice JOIN Contact ON Invoice.fk_Contact = Contact.ID WHERE TRUE"; // create where clause if (from != null) { query += " AND (" + from + ")"; } if (to != null) { query += " AND (" + to + ")"; } if (contact != null) { query += " AND (" + contact + ")"; } // execute NpgsqlCommand command = new NpgsqlCommand(query, _connection); AddInvoiceParameter(command, data); return(Select(command)); }
public async Task <RPResult> SearchInvoicesAsync(InvoiceSearchData data) { BinaryFormatter binaryFormatter = new BinaryFormatter(); MemoryStream memoryStream = new MemoryStream(); binaryFormatter.Serialize(memoryStream, data); RPCall call = new RPCall("CommandInvoice", memoryStream.GetBuffer()); return(await _client.SendAndReceiveAsync(call)); }
public List <Invoice> SearchInvoices(InvoiceSearchData data) { // variables DataTable invoices = SelectInvoices(data); // check result if (invoices == null) { return(new List <Invoice>()); } return(CreateInvoiceList(invoices)); }
public RPResult Execute(RPCall call) { List <Invoice> invoices = null; DataTable table = null; if ((call.Buffer == null || call.Buffer.Length < 1) && (call.procedureArgs == null || call.procedureArgs.Length < 1)) { throw new InvalidOperationException(); } else if (call.Buffer != null) { BinaryFormatter binaryFormatter = new BinaryFormatter(); MemoryStream memoryStream = new MemoryStream(call.Buffer); InvoiceSearchData data = (InvoiceSearchData)binaryFormatter.Deserialize(memoryStream); invoices = DatabaseFactory.Factory().SearchInvoices(data); } else { invoices = DatabaseFactory.Factory().SearchInvoices(Int32.Parse(call.procedureArgs[0])); } table = Invoice.CreateTable(); foreach (Invoice invoice in invoices) { table.Rows.Add(invoice.ToDataRow(table)); } table.AcceptChanges(); RPResult retVal = new RPResult(); retVal.dt = table; retVal.dt.TableName = "Invoices"; return(retVal); }
public List <Invoice> SearchInvoices(InvoiceSearchData data) { throw new NotImplementedException(); }