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