public DbInvoiceModel GetInvoiceDetails(long invoiceId, string sql)
{
using (var connection = new SqlConnection(DbString))
{
var command = new SqlCommand(sql, connection);
command.Parameters.Add(new SqlParameter("invoiceId", invoiceId));
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
DbInvoiceModel model = new DbInvoiceModel();
model.InvoiceId = (long)reader["InvoiceId"];
model.InvoiceNo = (string)reader["InvoiceNo"];
model.CurrencyCode = (string)reader["CurrencyCode"];
model.CustomerName = (string)reader["CustomerName"];
model.AccountDate = reader.GetFieldValue <DateTime>(reader.GetOrdinal("AccountDate"));
model.DeliveryDate = reader.GetFieldValue <DateTime>(reader.GetOrdinal("DeliveryDate"));
model.TotalAmt = (decimal)reader["TotalAmt"];
model.TotalLocalAmt = (decimal)reader["TotalLocalAmt"];
model.ExRate = (int)reader["ExRate"];
model.VesselName = (string)reader["VesselName"];
model.InvoiceStatus = (int)reader["InvoiceStatus"];
model.Remarks = reader["remarks"] != DBNull.Value ? (string)reader["remarks"] : string.Empty;
return(model);
}
}
}
return(null);
}
public List <DbInvoiceModel> GetInvoices(string sql, DateTime startDate, DateTime endDate)
{
List <DbInvoiceModel> invoices = new List <DbInvoiceModel>();
using (var connection = new SqlConnection(DbString))
{
var command = new SqlCommand(sql, connection);
if (startDate != DateTime.MinValue)
{
DateTime start = (DateTime)startDate.Date;
command.Parameters.Add(new SqlParameter("@StartDate", startDate));
}
if (endDate != DateTime.MinValue)
{
DateTime end = (DateTime)endDate.Date;
command.Parameters.Add(new SqlParameter("@EndDate", endDate));
}
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
DbInvoiceModel model = new DbInvoiceModel();
model.InvoiceId = (long)reader["InvoiceId"];
model.InvoiceNo = (string)reader["InvoiceNo"];
model.CurrencyCode = (string)reader["CurrencyCode"];
model.CustomerName = (string)reader["CustomerName"];
model.AccountDate = reader.GetFieldValue <DateTime>(reader.GetOrdinal("AccountDate"));
model.DeliveryDate = reader.GetFieldValue <DateTime>(reader.GetOrdinal("DeliveryDate"));
model.DueDate = reader.GetFieldValue <DateTime>(reader.GetOrdinal("DueDate"));
model.TotalAmt = (decimal)reader["TotalAmt"];
model.TotalLocalAmt = (decimal)reader["TotalLocalAmt"];
model.ExRate = (int)reader["ExRate"];
model.VesselName = (string)reader["VesselName"];
invoices.Add(model);
}
}
}
}
return(invoices);
}
