private List<Order> OrderGetAll(string commandText)
{
Log.Debug("OrderGetAll");
DataTable dataTable = GetDataTable(commandText);
DataRowCollection dataRowCollection = dataTable.Rows;
if (dataRowCollection.Count > 0)
{
List<Order> orders = new List<Order>();
foreach (DataRow dataRow in dataRowCollection)
{
object[] itemArray = dataRow.ItemArray;
Order order = new Order
{
Id = Misc.String2Number(itemArray[0].ToString()),
ItemId = Misc.String2Number(itemArray[1].ToString()),
Count = Misc.String2Number(itemArray[2].ToString()),
Price = itemArray[3].ToString(),
Postage = itemArray[4].ToString(),
ItemStatusId = Misc.String2Number(itemArray[5].ToString()),
PersonInfoId = Misc.String2Number(itemArray[6].ToString()),
LegalEntity = Misc.String2Number(itemArray[7].ToString()) > 0 ? true : false,
DateTime = itemArray[8].ToString(),
Tax = itemArray[9].ToString(),
BankId = Misc.String2Number(itemArray[10].ToString()),
};
Log.Debug("Have order: '{0}'", order.Format());
orders.Add(order);
}
return orders;
}
return null;
}
public int OrderInsert(Order order)
{
string insertCommand =
String.Format(
@"INSERT INTO Orders ( ItemId, Count, Price, Postage, ItemStatus, PersonInfoId, LegalEntity, Tax, BankId) VALUES({0}, {1}, '{2}', '{3}', {4}, {5}, {6}, '{7}', {8})",
order.ItemId,
order.Count,
order.Price,
order.Postage,
order.ItemStatusId,
order.PersonInfoId,
order.LegalEntity ? 1 : 0,
order.Tax ?? "0",
order.BankId);
return ExecuteNonQuery(insertCommand);
}
public int OrderUpdate(Order order)
{
try
{
string commandText =
@"UPDATE Orders SET ItemId = @itemId, Count = @count, Price = @price, Postage = @postage, ItemStatus = @itemStatusId, PersonInfoId = @personInfoId, LegalEntity = @legalEntity, Tax = @tax, BankId = @bankId WHERE Id = " +
order.Id;
Log.Debug("OrderUpdate: [{0}], Order={1}", commandText, order.Format());
using (sqLiteConnection)
{
using (SQLiteCommand sqLiteCommand = new SQLiteCommand(commandText, sqLiteConnection))
{
sqLiteConnection.Open();
sqLiteCommand.Parameters.AddWithValue("@itemId", order.ItemId);
sqLiteCommand.Parameters.AddWithValue("@count", order.Count);
sqLiteCommand.Parameters.AddWithValue("@price", order.Price);
sqLiteCommand.Parameters.AddWithValue("@postage", order.Postage);
sqLiteCommand.Parameters.AddWithValue("@itemStatusId", order.ItemStatusId);
sqLiteCommand.Parameters.AddWithValue("@personInfoId", order.PersonInfoId);
sqLiteCommand.Parameters.AddWithValue("@legalEntity", order.LegalEntity);
sqLiteCommand.Parameters.AddWithValue("@tax", order.Tax ?? "0");
sqLiteCommand.Parameters.AddWithValue("@bankId", order.BankId);
int rowsUpdated = sqLiteCommand.ExecuteNonQuery();
sqLiteConnection.Close();
return rowsUpdated;
}
}
}
catch (Exception exception)
{
Log.Error(exception.ToString);
return 0;
}
}
public List<Order> OrderGetAll()
{
const string commandText =
@"SELECT o.Id, o.ItemId, o.Count, o.Price, o.Postage, o.ItemStatus, o.PersonInfoId, o.LegalEntity, o.DateTime, o.Tax, p.Name, p.SurName, p.NickName, i.Name, s.Name, o.BankId FROM Orders o, PersonInfo p, Item i, ItemStatus s WHERE o.PersonInfoId = p.Id AND o.ItemId = i.Id AND o.ItemStatus = s.Id";
Log.Debug("OrderGetAll");
DataTable dataTable = GetDataTable(commandText);
DataRowCollection dataRowCollection = dataTable.Rows;
if (dataRowCollection.Count > 0)
{
List<Order> orders = new List<Order>();
foreach (DataRow dataRow in dataRowCollection)
{
object[] itemArray = dataRow.ItemArray;
Order order = new Order
{
Id = Misc.String2Number(itemArray[0].ToString()),
ItemId = Misc.String2Number(itemArray[1].ToString()),
Count = Misc.String2Number(itemArray[2].ToString()),
Price = itemArray[3].ToString(),
Postage = itemArray[4].ToString(),
ItemStatusId = Misc.String2Number(itemArray[5].ToString()),
PersonInfoId = Misc.String2Number(itemArray[6].ToString()),
LegalEntity = Misc.String2Number(itemArray[7].ToString()) > 0 ? true : false,
DateTime = itemArray[8].ToString(),
Tax = itemArray[9].ToString(),
PersonInfoText =
String.Format("{0}, {1} - [{2}]", itemArray[10], itemArray[11], itemArray[12]),
ItemText = itemArray[13].ToString(),
ItemStatusText = itemArray[14].ToString(),
BankId = Misc.String2Number(itemArray[15].ToString()),
};
Log.Debug("have order: '{0}'", order.Format());
orders.Add(order);
}
return orders;
}
return null;
}