public void TimestampTz()
{
var tzOffset = TimeZoneInfo.Local.BaseUtcOffset;
if (tzOffset == TimeSpan.Zero)
{
TestUtil.IgnoreExceptOnBuildServer("Test cannot run when machine timezone is UTC");
}
var dateTimeUtc = new DateTime(2015, 6, 27, 8, 45, 12, 345, DateTimeKind.Utc);
var dateTimeLocal = dateTimeUtc.ToLocalTime();
var dateTimeUnspecified = new DateTime(dateTimeUtc.Ticks, DateTimeKind.Unspecified);
var nDateTimeUtc = new NpgsqlDateTime(dateTimeUtc);
var nDateTimeLocal = nDateTimeUtc.ToLocalTime();
var nDateTimeUnspecified = new NpgsqlDateTime(nDateTimeUtc.Ticks, DateTimeKind.Unspecified);
var dateTimeOffset = new DateTimeOffset(dateTimeLocal, dateTimeLocal - dateTimeUtc);
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9", Conn))
{
cmd.Parameters.AddWithValue("p1", NpgsqlDbType.TimestampTZ, dateTimeUtc);
cmd.Parameters.AddWithValue("p2", NpgsqlDbType.TimestampTZ, dateTimeLocal);
cmd.Parameters.AddWithValue("p3", NpgsqlDbType.TimestampTZ, dateTimeUnspecified);
cmd.Parameters.AddWithValue("p4", NpgsqlDbType.TimestampTZ, nDateTimeUtc);
cmd.Parameters.AddWithValue("p5", NpgsqlDbType.TimestampTZ, nDateTimeLocal);
cmd.Parameters.AddWithValue("p6", NpgsqlDbType.TimestampTZ, nDateTimeUnspecified);
cmd.Parameters.AddWithValue("p7", dateTimeUtc);
cmd.Parameters.AddWithValue("p8", nDateTimeUtc);
cmd.Parameters.AddWithValue("p9", dateTimeOffset);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
// Regular type (DateTime)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(DateTime)));
Assert.That(reader.GetDateTime(i), Is.EqualTo(dateTimeLocal));
Assert.That(reader.GetFieldValue <DateTime>(i).Kind, Is.EqualTo(DateTimeKind.Local));
Assert.That(reader[i], Is.EqualTo(dateTimeLocal));
Assert.That(reader.GetValue(i), Is.EqualTo(dateTimeLocal));
// Provider-specific type (NpgsqlDateTime)
Assert.That(reader.GetTimeStamp(i), Is.EqualTo(nDateTimeLocal));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlDateTime)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(nDateTimeLocal));
Assert.That(reader.GetFieldValue <NpgsqlDateTime>(i), Is.EqualTo(nDateTimeLocal));
// DateTimeOffset
Assert.That(reader.GetFieldValue <DateTimeOffset>(i), Is.EqualTo(dateTimeOffset.ToUniversalTime()));
}
}
}
Assert.AreEqual(nDateTimeUtc, nDateTimeLocal.ToUniversalTime());
Assert.AreEqual(nDateTimeUtc, new NpgsqlDateTime(nDateTimeLocal.Ticks, DateTimeKind.Unspecified).ToUniversalTime());
Assert.AreEqual(nDateTimeLocal, nDateTimeUnspecified.ToLocalTime());
}
private int GetEXP(Guid id)
{
string query = "SELECT expvalue, lastSnapshotAt FROM \"exp_snapshot\" WHERE playerId = @id";
NpgsqlDateTime lastCreatedOn = new NpgsqlDateTime(0);
int sum = 0;
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("id", id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
sum = reader.GetInt32(0);
lastCreatedOn = reader.GetTimeStamp(1);
}
}
}
query = "SELECT coalesce(SUM(xpValue), 0) FROM exp WHERE playerId = @id AND created_at > @lastCreate";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("lastCreate", lastCreatedOn);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
sum += reader.GetInt32(0);
}
}
return(sum);
}
public async Task Timestamp_read(DateTime dateTime, string s)
{
await using var conn = await OpenConnectionAsync();
await using var cmd = new NpgsqlCommand($"SELECT '{s}'::timestamp without time zone", conn);
await using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("timestamp without time zone"));
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(DateTime)));
Assert.That(reader[0], Is.EqualTo(dateTime));
Assert.That(reader.GetDateTime(0), Is.EqualTo(dateTime));
Assert.That(reader.GetDateTime(0).Kind, Is.EqualTo(DateTimeKind.Unspecified));
Assert.That(reader.GetFieldValue <DateTime>(0), Is.EqualTo(dateTime));
// Provider-specific type (NpgsqlTimeStamp)
var npgsqlDateTime = new NpgsqlDateTime(dateTime.Ticks);
Assert.That(reader.GetProviderSpecificFieldType(0), Is.EqualTo(typeof(NpgsqlDateTime)));
Assert.That(reader.GetTimeStamp(0), Is.EqualTo(npgsqlDateTime));
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(npgsqlDateTime));
Assert.That(reader.GetFieldValue <NpgsqlDateTime>(0), Is.EqualTo(npgsqlDateTime));
// DateTimeOffset
Assert.That(() => reader.GetFieldValue <DateTimeOffset>(0), Throws.Exception.TypeOf <InvalidCastException>());
}
public override long Create(Accident entity)
{
var connection = Dbconnection.Open();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO public.accident (location, date, \"damage-amount\", person) VALUES (:location, :date, :dmg, :person)";
command.Parameters.Add(new NpgsqlParameter("location", entity.Location));
command.Parameters.Add(new NpgsqlParameter("date", NpgsqlDateTime.ToNpgsqlDateTime(entity.Date)));
command.Parameters.Add(new NpgsqlParameter("dmg", entity.Damage_Amount));
command.Parameters.Add(new NpgsqlParameter("person", entity.Person.Id));
command.ExecuteNonQuery();
command.CommandText = "SELECT \"record-number\" FROM public.accident WHERE location = :loc AND person = :pers";
command.Parameters.Add(new NpgsqlParameter("loc", entity.Location));
command.Parameters.Add(new NpgsqlParameter("pers", entity.Person.Id));
var reader = command.ExecuteReader();
long id = -1;
if (reader.Read())
{
id = reader.GetInt64(0);
}
Dbconnection.Close();
return(id);
}
/// <inheritdoc />
public override void Write(DateTime value, NpgsqlWriteBuffer buf, NpgsqlParameter?parameter)
{
switch (value.Kind)
{
case DateTimeKind.Unspecified:
case DateTimeKind.Utc:
break;
case DateTimeKind.Local:
value = value.ToUniversalTime();
break;
default:
throw new InvalidOperationException($"Internal Npgsql bug: unexpected value {value.Kind} of enum {nameof(DateTimeKind)}. Please file a bug.");
}
NpgsqlDateTime pgValue = value;
if (ConvertInfinityDateTime)
{
if (value == DateTime.MinValue)
{
pgValue = NpgsqlDateTime.NegativeInfinity;
}
else if (value == DateTime.MaxValue)
{
pgValue = NpgsqlDateTime.Infinity;
}
}
// We cannot pass the DateTime value due to it implicitly converting to the NpgsqlDateTime anyway
base.Write(pgValue, buf, parameter);
}
public void Timestamp(DateTime dateTime)
{
using (var conn = OpenConnection())
{
var npgsqlTimeStamp = new NpgsqlDateTime(dateTime.Ticks);
var offset = TimeSpan.FromHours(2);
var dateTimeOffset = new DateTimeOffset(dateTime, offset);
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4, @p5, @p6", conn))
{
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Timestamp);
var p2 = new NpgsqlParameter("p2", DbType.DateTime);
var p3 = new NpgsqlParameter("p3", DbType.DateTime2);
var p4 = new NpgsqlParameter {
ParameterName = "p4", Value = npgsqlTimeStamp
};
var p5 = new NpgsqlParameter {
ParameterName = "p5", Value = dateTime
};
var p6 = new NpgsqlParameter("p6", NpgsqlDbType.Timestamp);
Assert.That(p4.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(p4.DbType, Is.EqualTo(DbType.DateTime));
Assert.That(p5.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(p5.DbType, Is.EqualTo(DbType.DateTime));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
p1.Value = p2.Value = p3.Value = npgsqlTimeStamp;
p6.Value = dateTimeOffset;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
// Regular type (DateTime)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(DateTime)));
Assert.That(reader.GetDateTime(i), Is.EqualTo(dateTime));
Assert.That(reader.GetDateTime(i).Kind, Is.EqualTo(DateTimeKind.Unspecified));
Assert.That(reader.GetFieldValue <DateTime>(i), Is.EqualTo(dateTime));
Assert.That(reader[i], Is.EqualTo(dateTime));
Assert.That(reader.GetValue(i), Is.EqualTo(dateTime));
// Provider-specific type (NpgsqlTimeStamp)
Assert.That(reader.GetTimeStamp(i), Is.EqualTo(npgsqlTimeStamp));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlDateTime)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(npgsqlTimeStamp));
Assert.That(reader.GetFieldValue <NpgsqlDateTime>(i), Is.EqualTo(npgsqlTimeStamp));
// DateTimeOffset
Assert.That(() => reader.GetFieldValue <DateTimeOffset>(i), Throws.Exception.TypeOf <InvalidCastException>());
}
}
}
}
}
public Catch(int kalalajinvalinta, string fish, int weight, int lenght, NpgsqlDateTime fishtime)
{
_kalalajinvalinta = kalalajinvalinta;
_fish = fish;
_lenght = lenght;
_weight = weight;
_fishtime = fishtime;
kgsumma.Add(weight);
_fishCount++;
}
public void TimeStampSpecial(NpgsqlDateTime value)
{
using (var cmd = new NpgsqlCommand("SELECT @p", Conn)) {
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p", Value = value
});
using (var reader = cmd.ExecuteReader()) {
reader.Read();
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(value));
Assert.That(() => reader.GetDateTime(0), Throws.Exception);
}
}
}
public void TimeStampSpecial(NpgsqlDateTime value)
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand("SELECT @p", conn)) {
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p", Value = value
});
using (var reader = cmd.ExecuteReader()) {
reader.Read();
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(value));
Assert.That(() => reader.GetDateTime(0), Throws.Exception.TypeOf <InvalidCastException>());
}
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public void NpgsqlDateTimeTest(string context)
{
PostgreSQLTools.GetDataProvider().CreateConnection(DataConnection.GetConnectionString(context));
var d = new NpgsqlDateTime(DateTime.Today);
var o = new DateTimeOffset(DateTime.Today);
var c1 = PostgreSQLTools.GetDataProvider().MappingSchema.GetConvertExpression <NpgsqlDateTime, DateTimeOffset>();
var c2 = PostgreSQLTools.GetDataProvider().MappingSchema.GetConvertExpression <NpgsqlDateTime, DateTimeOffset?>();
Assert.IsNotNull(c1);
Assert.IsNotNull(c2);
Assert.AreEqual(o, c1.Compile()(d));
Assert.AreEqual(o, c2.Compile()(d).Value);
}
public override void Update(Accident entity)
{
var connection = Dbconnection.Open();
var command = connection.CreateCommand();
command.CommandText = "UPDATE public.accident SET location = :location, date = :date, " +
"\"damage-amount\" = :dmg, person = :personid " +
"WHERE \"record-number\" = :rec_id";
command.Parameters.Add(new NpgsqlParameter("location", entity.Location));
command.Parameters.Add(new NpgsqlParameter("date", NpgsqlDateTime.ToNpgsqlDateTime(entity.Date)));
command.Parameters.Add(new NpgsqlParameter("dmg", entity.Damage_Amount));
command.Parameters.Add(new NpgsqlParameter("personid", entity.Person.Id));
command.Parameters.Add(new NpgsqlParameter("rec_id", entity.Record_Number));
command.ExecuteNonQuery();
Dbconnection.Close();
}
public int GetRows(DateTime date1, DateTime date2)
{
NpgsqlDateTime start = date1, end = date2;
int total = 0;
using (NpgsqlCommand filterRows = new NpgsqlCommand("SELECT COUNT(*) FROM calls WHERE Created_date IS NOT NULL " +
"AND Created_date <= " + end + " AND Created_date >= " + start, _dbConnect))
using (NpgsqlDataReader reader = filterRows.ExecuteReader())
{
while (reader.Read())
{
total = reader.GetInt32(0);
}
}
return(total);
}
public override void Write(NpgsqlDateTime value, NpgsqlWriteBuffer buf, NpgsqlParameter parameter)
{
switch (value.Kind)
{
case DateTimeKind.Unspecified:
case DateTimeKind.Utc:
break;
case DateTimeKind.Local:
value = value.ToUniversalTime();
break;
default:
throw new InvalidOperationException($"Internal Npgsql bug: unexpected value {value.Kind} of enum {nameof(DateTimeKind)}. Please file a bug.");
}
base.Write(value, buf, parameter);
}
private int getCountSnapshot(Guid habit_id, Guid user_id)
{
NpgsqlDateTime lastCreatedDate = new NpgsqlDateTime(0);
int count = 0;
string query = @" select created_at, countlogs from ""logs_snapshot"" where habit_id = @habit_id and user_id = @user_id order by created_at DESC LIMIT 1 ";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habit_id", habit_id);
cmd.Parameters.AddWithValue("user_id", user_id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
lastCreatedDate = reader.GetDateTime(0);
count = reader.GetInt32(1);
}
}
if (count % 100 == 0)
{
CreateSnapShotLogs(habit_id, user_id);
}
}
query = @"select created_at, coalesce(count(1), 0) from ""logs"" where habit_id = @habit_id and user_id = @user_id and created_at > @last_created";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habit_id", habit_id);
cmd.Parameters.AddWithValue("user_id", user_id);
cmd.Parameters.AddWithValue("last_created", lastCreatedDate);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int c = reader.GetInt32(1);
count += c;
}
reader.Close();
}
}
return(count);
}
/// <summary>
/// Gets the number of rows that match the time period selected
/// </summary>
/// <param name="start">The Start Date</param>
/// <param name="end">The End Date</param>
/// <returns>returns the number of rows in the set</returns>
public int GetRows(DateTime date1, DateTime date2)
{
String connString = (string)Application.Current.Resources["connString"];
NpgsqlDateTime start = date1, end = date2;
int total = 0;
using (NpgsqlConnection conn = new NpgsqlConnection(connString))
{
conn.Open();
using (NpgsqlCommand filterRows = new NpgsqlCommand("SELECT COUNT(*) FROM calls WHERE Created_date IS NOT NULL " +
"AND Created_date <= " + end + " AND Created_date >= " + start, conn))
using (NpgsqlDataReader reader = filterRows.ExecuteReader())
{
while (reader.Read())
{
total = reader.GetInt32(0);
}
}
}
return(total);
}
private int getExp(Guid id)
{
NpgsqlDateTime lastExpCreatedAt = new NpgsqlDateTime(0);
int sumExp = 0;
string query = "SELECT exp, last_exp_created_at FROM exp_snapshot WHERE user_id = @user_id ORDER BY created_at DESC LIMIT 1";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("user_id", id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
sumExp = reader.GetInt32(0);
lastExpCreatedAt = reader.GetTimeStamp(1);
}
}
}
query = "SELECT coalesce(sum(exp),0) FROM exp WHERE user_id = @user_id AND created_at > @last_exp_created_at";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("user_id", id);
cmd.Parameters.AddWithValue("last_exp_created_at", lastExpCreatedAt);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int exp = reader.GetInt32(0);
sumExp += exp;
}
}
}
return(sumExp);
}
/// <summary>
/// Gets the Filter if the user selects to see all data between 2 dates
/// </summary>
/// <param name="limit">the number of rows to get</param>
/// <param name="offset">the offset to move around the set</param>
/// <param name="date1">the start date</param>
/// <param name="date2">the end date</param>
/// <returns></returns>
public List <Json311> GetDateFilteredList(int limit, int offset, DateTime date1, DateTime date2)
{
String connString = (string)Application.Current.Resources["connString"];
List <Json311> toDisplay = new List <Json311>();
NpgsqlDateTime start = date1, end = date2;
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
NpgsqlCommand query = new NpgsqlCommand("SELECT Unique_key, Created_date, Closed_date, Agency_name " +
"Complaint_type, Status, Incident_address, Incident_zip, Borough FROM calls WHERE Created_date IS NOT NULL " +
"AND Created_date >= " + start + " AND Created_date <= " + end + " LIMIT " + limit + " OFFSET " + offset);
query.Connection = conn;
NpgsqlDataAdapter myAdapter = new NpgsqlDataAdapter(query);
DataSet data = new DataSet();
myAdapter.Fill(data, "calls");
int total = data.Tables["calls"].Rows.Count;
int counter = 0;
while (total > counter)
{
toDisplay.Add(new Json311()
{
Unique_key = data.Tables["calls"].Rows[counter]["Unique_key"].ToString(),
Created_date = Convert.ToDateTime(data.Tables["calls"].Rows[counter]["Created_date"]),
Closed_date = Convert.ToDateTime(data.Tables["calls"].Rows[counter]["Closed_date"]),
Agency_name = data.Tables["calls"].Rows[counter]["Agency_name"].ToString(),
Complaint_type = data.Tables["calls"].Rows[counter]["Complaint_type"].ToString(),
Status = data.Tables["calls"].Rows[counter]["Status"].ToString(),
Incident_address = data.Tables["calls"].Rows[counter]["Incident_address"].ToString(),
Incident_zip = data.Tables["calls"].Rows[counter]["Incident_zip"].ToString(),
Borough = data.Tables["calls"].Rows[counter]["Borough"].ToString(),
});
}
}
return(toDisplay);
}
public void Import(List <Json311> dataset, String connString, string tableName = "calls", Boolean updateTime = true)
{
NpgsqlDateTime last_date = new NpgsqlDateTime();
DateTime construct = new DateTime(0);
NpgsqlDateTime most_recent = new NpgsqlDateTime(construct);
if (updateTime == true)
{
using (NpgsqlCommand checkDate = new NpgsqlCommand("SELECT * FROM checktime", _dbConnect))
using (NpgsqlDataReader reader = checkDate.ExecuteReader())
{
try
{
while (reader.Read())
{
last_date = reader.GetTimeStamp(0);
reader.Close();
}
}
catch (Exception a) { Console.WriteLine(a); }
}
}
int oldC = 0, newC = 0;
using (var writer = _dbConnect.BeginBinaryImport("COPY " + tableName + " FROM STDIN (FORMAT BINARY)"))
{
foreach (Json311 entry in dataset)
{
NpgsqlDateTime entryDate = Convert.ToDateTime(entry.Created_date);
if (entryDate < last_date)
{
if (most_recent < entryDate)
{
most_recent = entryDate;
}
oldC++;
continue;
}
writer.StartRow();
writer.Write(entry.Unique_key);
if (entry.Created_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime cdate = Convert.ToDateTime(entry.Created_date);
writer.Write(cdate);
}
if (entry.Closed_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime cdate = Convert.ToDateTime(entry.Closed_date);
writer.Write(cdate);
}
writer.Write(entry.Agency);
writer.Write(entry.Agency_name);
writer.Write(entry.Complaint_type);
writer.Write(entry.Descriptor);
writer.Write(entry.Location_type);
writer.Write(entry.Incident_zip);
writer.Write(entry.Incident_address);
writer.Write(entry.Street_name);
writer.Write(entry.Cross_street_1);
writer.Write(entry.Cross_street_2);
writer.Write(entry.Intersection_street_1);
writer.Write(entry.Intersection_street_2);
writer.Write(entry.Address_type);
writer.Write(entry.City);
writer.Write(entry.Landmark);
writer.Write(entry.Facility_type);
writer.Write(entry.Status);
if (entry.Due_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime dDate = Convert.ToDateTime(entry.Due_date);
writer.Write(dDate);
}
writer.Write(entry.Resolution_description);
if (entry.Resolution_action_updated_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime rDate = Convert.ToDateTime(entry.Resolution_action_updated_date);
writer.Write(rDate);
}
writer.Write(entry.Community_board);
writer.Write(entry.Bbl);
writer.Write(entry.Borough);
writer.Write(entry.X_coordinate_state_plane);
writer.Write(entry.Y_coordinate_state_plane);
writer.Write(entry.Open_data_channel_type);
writer.Write(entry.Park_facility_name);
writer.Write(entry.Park_borough);
writer.Write(entry.Vehicle_type);
writer.Write(entry.Taxi_company_borough);
writer.Write(entry.Taxi_pick_up_location);
writer.Write(entry.Bridge_highway_name);
writer.Write(entry.Bridge_highway_direction);
writer.Write(entry.Road_ramp);
writer.Write(entry.Bridge_highway_segment);
writer.Write(entry.Latitude);
writer.Write(entry.Longitude);
writer.Write(entry.Location_city);
if (entry.Location == null)
{
writer.WriteNull();
}
else
{
var dat = entry.Location.SelectToken("coordinates");
double x = (double)dat[0];
double y = (double)dat[1];
NpgsqlPoint nPoint = new NpgsqlPoint(x, y);
writer.Write(nPoint);
}
writer.Write(entry.Location_zip);
writer.Write(entry.Location_state);
newC++;
}
writer.Complete();
}
Console.WriteLine("New Records:" + newC + " Old Records: " + oldC);
if (updateTime == true)
{
NpgsqlCommand dropCheck = new NpgsqlCommand("DROP TABLE checktime", _dbConnect);
dropCheck.ExecuteNonQuery();
NpgsqlCommand newCheck = new NpgsqlCommand("CREATE TABLE checktime (curr_up_date timestamp)", _dbConnect);
newCheck.ExecuteNonQuery();
using (var writer = _dbConnect.BeginBinaryImport("COPY checktime FROM STDIN (FORMAT BINARY)"))
{
writer.StartRow();
writer.Write(most_recent);
writer.Complete();
}
}
}
public IActionResult AddOrder(Cart cart)
{
customer = HttpContext.Session.GetObject <Customer>("Customer");
if (customer == null)
{
Log.Error("Customer session was not found");
return(RedirectToAction("LoginC", "Home"));
}
if (ModelState.IsValid)
{
Order order = new Order();
order.totalCost = cart.totalCost;
order.locationId = customer.locationId;
order.dateOfOrder = DateTime.Now;
NpgsqlDateTime npgsqlDateTime = order.dateOfOrder;
order.custId = customer.Id;
using (var client = new HttpClient())
{
client.BaseAddress = new Uri(url);
var json = JsonConvert.SerializeObject(order);
var data = new StringContent(json, Encoding.UTF8, "application/json");
var response = client.PostAsync("order/add", data);
response.Wait();
while (response.Result.IsSuccessStatusCode)
{
response = client.GetAsync($"order/get?dateTime={npgsqlDateTime}");
response.Wait();
var result = response.Result.Content.ReadAsStringAsync();
var newOrder = JsonConvert.DeserializeObject <Order>(result.Result);
Log.Information($"Successfully created order: {newOrder.Id}");
order.Id = newOrder.Id;
foreach (var item in customer.cart.cartItems)
{
Stick stick = item.stick;
LineItem lineItem = new LineItem();
lineItem.orderId = order.Id;
lineItem.stickId = item.stickId;
lineItem.cost = stick.cost;
lineItem.quantity = item.quantity;
json = JsonConvert.SerializeObject(lineItem);
data = new StringContent(json, Encoding.UTF8, "application/json");
response = client.PostAsync("lineitem/add", data);
response.Wait();
Log.Information($"Successfully create lineItem: {json}");
response = client.GetAsync($"inventory/get/{customer.locationId}/{item.stickId}");
response.Wait();
result = response.Result.Content.ReadAsStringAsync();
var inventoryItem = JsonConvert.DeserializeObject <Inventory>(result.Result);
inventoryItem.quantity -= item.quantity;
json = JsonConvert.SerializeObject(inventoryItem);
data = new StringContent(json, Encoding.UTF8, "application/json");
response = client.PutAsync("inventory/update", data);
response.Wait();
Log.Information($"Successfully updated inventoryItem: {json}");
}
customer.cart.cartItems.Clear();
HttpContext.Session.SetObject("Customer", customer);
return(RedirectToAction("GetReceipt", order));
}
}
}
Log.Error("Unable to create a new order");
return(RedirectToAction("GetInventory", "Customer"));
}
private int getCurrentStreak(Guid habitID)
{
NpgsqlDateTime lastStreakCreatedAt = new NpgsqlDateTime(0);
int sumCurrentStreak = 0;
string query = "SELECT current_streak, last_streak_created_at FROM \"Streak_snapshot\" WHERE habit_id = @habitID ORDER BY created_at DESC LIMIT 1";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habitID", @habitID);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
sumCurrentStreak = reader.GetInt32(0);
lastStreakCreatedAt = reader.GetTimeStamp(1);
}
reader.Close();
}
}
query = "SELECT coalesce(sum(current_streak),0) FROM \"Streak\" WHERE habit_id = @habitID AND created_at > @lastStreakCreatedAt";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habitID", habitID);
cmd.Parameters.AddWithValue("lastStreakCreatedAt", lastStreakCreatedAt);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int current = reader.GetInt32(0);
sumCurrentStreak += current;
}
reader.Close();
}
}
if (getLastLog(habitID) == 0)
{
sumCurrentStreak = 0;
query = "UPDATE \"Streak\" SET current_streak = @sumCurrentStreak where habit_id = @habitID";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("sumCurrentStreak", sumCurrentStreak);
cmd.Parameters.AddWithValue("habitID", habitID);
cmd.ExecuteNonQuery();
}
NpgsqlDateTime CreatedAt = new NpgsqlDateTime(0);
query = "SELECT created_at FROM \"Streak\" WHERE habit_id = @habitID ORDER BY created_at DESC LIMIT 1";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habitID", habitID);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
CreatedAt = reader.GetTimeStamp(0);
}
reader.Close();
}
}
query = "UPDATE \"Streak\" SET longest_streak = @sumCurrentStreak WHERE habit_id = @habitID and created_at = @created";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("sumCurrentStreak", sumCurrentStreak);
cmd.Parameters.AddWithValue("habitID", habitID);
cmd.Parameters.AddWithValue("created", CreatedAt);
cmd.ExecuteNonQuery();
}
}
return(sumCurrentStreak);
}
static async System.Threading.Tasks.Task Main(string[] args)
{
// Luodaan tietokantayhteys
// tietokannan SQL scripti löytyy Githubin repositoriosta
SQL.SqlConnection();
{
// Listat tietojen tallentamista varten
List <Fishingsession> kalastussuoritus = new List <Fishingsession>();
List <Fishingtrip> kalastusmatka = new List <Fishingtrip>();
List <Catch> Catch = new List <Catch>();
// Tulostetaan valikko
Mainoperations.TulostaValikko();
// Jatketaan ohjelmaa
bool jatkaohjelmaa = true;
while (jatkaohjelmaa)
{
// Kysytään käyttäjältä suoritettava toiminto
string komento = Console.ReadLine();
switch (komento)
{
case "m": // aloitetaan kalastusmatka ja lisätään kalastusmatkan tiedot
Console.Clear();
Console.WriteLine("Anna kalastusmatkan tiedot");
Console.WriteLine("-----------------------------------------------");
Console.Write("Paikka: ");
string place = Console.ReadLine();
Console.Write("Kalastajan nimi: ");
String fisherName = Console.ReadLine();
bool competition = false;
bool valinta = true;
do // Ohjelma kysyy käyttäjältä onko kalastusmatka kisa? Ohjelma tarkistaa syötetyn arvon, että se on oikein.
{
Console.Write("Onko kalastusmatka kisa? K/E: ");
String vastaus = Console.ReadLine();
if (vastaus == "K") // Lisätään listaan ja tallennetaan myös kisannimi
{
Console.Write("Anna kalastuskisan nimi:");
String competitionName = Console.ReadLine();
valinta = false;
competition = true;
// Lisätään kalastusmatkan tiedot listaan
kalastusmatka.Add(new Fishingtrip(place, fisherName, competition));
kalastusmatka[0].SetCompetitionName(competitionName);
}
else if (vastaus == "E") // Lisätään listaan, mutta ei tallenneta kisan nimeä
{
Console.WriteLine("Kalastusmatka ei ole kilpailu");
competition = false;
valinta = false;
kalastusmatka.Add(new Fishingtrip(place, fisherName, competition));
}
else
{
Console.WriteLine("Väärä arvo");
}
} while (valinta);
// Lisätään matkanaloitusaika aikaleimalla
DateTime startTime = DateTime.Now;
string format1 = "d.M.yyyy HH:mm";
kalastusmatka[0].SetTripStartTime(startTime);
Console.Clear();
// Tulostetaan kalastusmatkan tiedot
Console.WriteLine($"Paikka: {kalastusmatka[0].GetPlace()}");
Console.WriteLine($"Kalastaja: {kalastusmatka[0].GetFisherName()}");
if (competition) // Jos kalastusmatka on kisa, tulostetaan kisan nimi
{
Console.WriteLine($"Kalastuskisan nimi: {kalastusmatka[0].GetCompetitionName()}");
}
Console.WriteLine($"Päivämäärä ja aika: { startTime.ToString(format1) }");
Console.WriteLine("-----------------------------------------------\n");
Console.WriteLine("Uusi kalastusmatka aloitettu\n");
Console.WriteLine("Seuraavaksi voit aloittaa kalastussuorituksen");
// Tulostetaan valikko kalastussuoritukselle
Mainoperations.TulostaSuoritusValikko();
break;
case "s": // aloiteaan kalastussuoritus, kysytään käyttäjältä vieheen tiedot, kalastustapa ja merkitään suorituksen aloitusaika
Console.Clear();
Console.Write("Anna vieheen nimi: ");
String lureName = Console.ReadLine();
Console.Write("Anna vieheen tyyppi: ");
String lureType = Console.ReadLine();
Console.Write("Anna kalastustapa: ");
String fishingStyle = Console.ReadLine();
Console.Write(" \n");
DateTime sessionStartTime = DateTime.Now;
string format2 = "d.M.yyyy HH:mm";
Console.Clear();
// Luodaan ilmentymä ja tallennetaan tiedot listaan
kalastussuoritus.Add(new Fishingsession(lureName, lureType, fishingStyle, sessionStartTime));
// Tulostetaan suorituksen tiedot
Console.WriteLine($"Vieheen nimi: {kalastussuoritus[0].GetLureName()}");
Console.WriteLine($"Vieheen tyyppi: {kalastussuoritus[0].GetLureType()}");
Console.WriteLine($"Kalastustapa: {kalastussuoritus[0].GetFishingStyle()}");
Console.WriteLine($"Päivämäärä ja aika: { sessionStartTime.ToString(format2) }\n");
Console.WriteLine("Kalastus suoritus aloitettu\n");
// Kalastus suoritukselle voidaan luoda useita saaliita ja suoritus päätetään käyttäjän niin valitessa ja asettamalla lopetusaika.
int kalalajinvalinta = 0;
string fish = "";
bool jatkasessiota = true;
while (jatkasessiota)
{
Console.WriteLine("Lisää saalis valitsemalla [1] \n");
Console.WriteLine("Lopeta kalastussuoritus valitsemalla [0] \n");
string valitse = Console.ReadLine();
switch (valitse)
{
case "1": // Käyttäjältä kysytään saaliin tiedot, kalalaji, paino ja pituus
do // kysytään kalalaji
{
try
{
Console.WriteLine("Valitse kalalaji: Hauki[1], Kuha[2], Ahven[3] ");
Console.Write("Anna numero: ");
kalalajinvalinta = int.Parse(Console.ReadLine());
}
catch (Exception)
{
Console.WriteLine("Vain numerot hyväksytään, Anna numero 1, 2 tai 3");
}
} while (kalalajinvalinta != (1) && kalalajinvalinta != (2) && kalalajinvalinta != (3));
if (kalalajinvalinta == 1)
{
fish = "Hauki";
kalalajinvalinta = 1;
}
if (kalalajinvalinta == 2)
{
fish = "Kuha";
kalalajinvalinta = 2;
}
if (kalalajinvalinta == 3)
{
fish = "Ahven";
kalalajinvalinta = 3;
}
// Lisätään paino ja pituus ja varmistetaan, että arvo on numero
do
{
Console.Write("Kalan paino numeroina (kg): ");
} while (!int.TryParse(Console.ReadLine(), out weight));
do
{
Console.Write("Kalan pituus numeroina (Cm): ");
} while (!int.TryParse(Console.ReadLine(), out lenght));
// Lisätään pvm ja aika
NpgsqlDateTime fishtime = NpgsqlDateTime.Now;
Console.Clear();
// Luodaan olio
Catch uusisaalis = new Catch(kalalajinvalinta, fish, weight, lenght, fishtime);
// Lisätään kala listaan ja tulostetaan
Catch.Add(uusisaalis);
Console.WriteLine($"{fish} , {weight} Kg , {lenght} Cm , saalistusaika {fishtime}");
Console.WriteLine("Lisätty suoritukselle\n");
// Lisätään kala tietokantaan
SQL.AddFish(uusisaalis);
break;
case "0": // Lopetetaan kalastussuoritus ja lisätään lopetusaika
Console.Clear();
kalastussuoritus[0].SetSessionEndTime(DateTime.Now);
Console.WriteLine($"Kalastus suoritus lopetettiin aikaan {kalastussuoritus[0].GetSessionEndTime()} ");
// Tulostetaan valikko
Mainoperations.TulostaMatkaKeskenValikko();
jatkasessiota = false;
break;
default:
Console.WriteLine("Valitsit väärin");
break;
}
}
break;
case "a": // Analysoidaan kalastusta ja tulostetaan historia tietoja
// Lasketaan tietyllä vieheellä saadut kalat.
bool jatkaanalyysia = true;
while (jatkaanalyysia)
{
Console.WriteLine("Näytä kalastusmatkan kalat ja yhteispaino [1] \n");
Console.WriteLine("Näytä kaikki saadut kalat [2] \n");
Console.WriteLine("Näytä kalastusmatkan kesto [3] \n");
Console.WriteLine("Näytä valikko [4] \n");
string valitse = Console.ReadLine();
switch (valitse)
{
case "1": //Tulostaa kalastusmatkalla saadut kalat ja näytetään kalojen yhteispaino.
Console.Clear();
foreach (Catch item in Catch)
{
Console.Write($"{item.GetFishSpecies()} ");
Console.Write($"{item.GetFishWeight()} Kg ");
Console.Write($"{item.GetFishLenght()} Cm ");
Console.WriteLine($"Saalistusaika: {item.GetFishTime()} ");
Console.WriteLine("");
}
Console.WriteLine($"Kaloja saatu tällä kalastusmatkalla: {Catch[0].GetFishCount()} ");
Console.WriteLine($"Kalojen yhteispaino: {Catch[0].GetWeightSum()} kiloa");
Console.WriteLine();
break;
case "2": // Haetaan kaikki saadut kalat tietokannasta
Console.Clear();
Console.WriteLine("Kaikki kalat tulostetaan tietokannasta");
SQL.SelectKaikkiSaaliit();
break;
case "3": // Näytetään Kalastusmatkan kesto
Console.Clear();
TimeSpan fishingTime = (kalastusmatka[0].GetStartTime() - DateTime.Now);
Console.WriteLine($"Kalastusmatkan kesto: {fishingTime.ToString("hh\\:mm\\:ss")}");
break;
case "4": // Poistutaan analyysiosiosta
Console.Clear();
Mainoperations.TulostaMatkaKeskenValikko();
jatkaanalyysia = false;
break;
default:
Console.WriteLine("Valitsit väärin");
break;
}
}
break;
case "l": // Lopetetaan kalastusmatka ja tulostetaan lopetusaika.
Console.Clear();
kalastusmatka[0].SetTripEndTime(DateTime.Now);
Console.WriteLine($"Kalastusmatka lopetettiin aikaan: {kalastusmatka[0].GetTripEndTime()} ");
Mainoperations.TulostaValikko();
break;
case "q": // ohjelma lopetetaan
jatkaohjelmaa = false;
break;
default:
Console.WriteLine("Valitsit väärin");
break;
}
}
}
}
public void TimeStampSpecial(NpgsqlDateTime value)
{
using (var cmd = new NpgsqlCommand("SELECT @p", Conn)) {
cmd.Parameters.Add(new NpgsqlParameter { ParameterName = "p", Value = value });
using (var reader = cmd.ExecuteReader()) {
reader.Read();
Assert.That(reader.GetProviderSpecificValue(0), Is.EqualTo(value));
Assert.That(() => reader.GetDateTime(0), Throws.Exception);
}
Assert.That(ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
/// <summary>
/// Our import command to import the data for the day into our pgsql database,
/// will be broken up into multiple functions for increased readability
/// </summary>
/// <param name="dataset">Our current DataSet to load into our DB</param>
/// <param name="connString">The string to initialize the connection to the database</param>
/// <param name="tableName">The tableName, set to calls by default, allows for testing
/// to a different table without adding fake data to the database, the testing database is
/// otherwise functionally identical to the calls database
/// </param>
public void Import(List <Json311> dataset, String connString, string tableName = "calls", Boolean updateTime = true)
{
using (var conn = new NpgsqlConnection(connString))
{
/// <remarks>
/// Make Sure data is not duplicated
/// </remarks>
conn.Open();
this.CheckConnection(conn);
//conn.TypeMapper.UseJsonNet();
NpgsqlDateTime last_date = new NpgsqlDateTime();
DateTime construct = new DateTime(0);
NpgsqlDateTime most_recent = new NpgsqlDateTime(construct);
/// <remarks>
/// Get the DateStamp frmo our checktime table
/// We do this in order to check the stored datestamp so as not to import duplicate DATA
/// We cannot read and write over the same connection so we need to retrieve the dateTime so we can check it against the timestamp
/// of the entries later
/// </remarks>
if (updateTime == true)
{
using (NpgsqlCommand checkDate = new NpgsqlCommand("SELECT * FROM checktime", conn))
using (NpgsqlDataReader reader = checkDate.ExecuteReader())
{
try
{
while (reader.Read())
{
last_date = reader.GetTimeStamp(0);
reader.Close();
}
}
catch (Exception a) { Console.WriteLine(a); }
}
}
int oldC = 0, newC = 0;
/// <remarks>
/// postgres does not support nullable datetimes so we check the DateTime fields and
/// write a null if the value is null
/// if not we cast the nullable datetime to datetime and then write that
/// Using the tablename variable allows us to change the target table without changing all the code
/// </remarks>
using (var writer = conn.BeginBinaryImport("COPY " + tableName + " FROM STDIN (FORMAT BINARY)"))
{
foreach (Json311 entry in dataset)
{
/// <remarks>
/// Check the retrieved dateTime against the entries dateTime
/// </remarks>
NpgsqlDateTime entryDate = Convert.ToDateTime(entry.Created_date);
if (entryDate < last_date)
{
if (most_recent < entryDate)
{
most_recent = entryDate;
}
oldC++;
continue;
}
writer.StartRow();
writer.Write(entry.Unique_key);
if (entry.Created_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime cdate = Convert.ToDateTime(entry.Created_date);
writer.Write(cdate);
}
if (entry.Closed_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime cdate = Convert.ToDateTime(entry.Closed_date);
writer.Write(cdate);
}
writer.Write(entry.Agency);
writer.Write(entry.Agency_name);
writer.Write(entry.Complaint_type);
writer.Write(entry.Descriptor);
writer.Write(entry.Location_type);
writer.Write(entry.Incident_zip);
writer.Write(entry.Incident_address);
writer.Write(entry.Street_name);
writer.Write(entry.Cross_street_1);
writer.Write(entry.Cross_street_2);
writer.Write(entry.Intersection_street_1);
writer.Write(entry.Intersection_street_2);
writer.Write(entry.Address_type);
writer.Write(entry.City);
writer.Write(entry.Landmark);
writer.Write(entry.Facility_type);
writer.Write(entry.Status);
if (entry.Due_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime dDate = Convert.ToDateTime(entry.Due_date);
writer.Write(dDate);
}
writer.Write(entry.Resolution_description);
if (entry.Resolution_action_updated_date == null)
{
writer.WriteNull();
}
else
{
NpgsqlDateTime rDate = Convert.ToDateTime(entry.Resolution_action_updated_date);
writer.Write(rDate);
}
writer.Write(entry.Community_board);
writer.Write(entry.Bbl);
writer.Write(entry.Borough);
writer.Write(entry.X_coordinate_state_plane);
writer.Write(entry.Y_coordinate_state_plane);
writer.Write(entry.Open_data_channel_type);
writer.Write(entry.Park_facility_name);
writer.Write(entry.Park_borough);
writer.Write(entry.Vehicle_type);
writer.Write(entry.Taxi_company_borough);
writer.Write(entry.Taxi_pick_up_location);
writer.Write(entry.Bridge_highway_name);
writer.Write(entry.Bridge_highway_direction);
writer.Write(entry.Road_ramp);
writer.Write(entry.Bridge_highway_segment);
writer.Write(entry.Latitude);
writer.Write(entry.Longitude);
writer.Write(entry.Location_city);
if (entry.Location == null)
{
writer.WriteNull();
}
else
{
var dat = entry.Location.SelectToken("coordinates");
double x = (double)dat[0];
double y = (double)dat[1];
NpgsqlPoint nPoint = new NpgsqlPoint(x, y);
writer.Write(nPoint);
}
writer.Write(entry.Location_zip);
writer.Write(entry.Location_state);
newC++;
}
writer.Complete();
}
Console.WriteLine("New Records:" + newC + " Old Records: " + oldC);
/// <remarks>
/// Update the stored Date in the checktime table, only update the time
/// if we are actually adding data and not just for a test
/// </remarks>
if (updateTime == true)
{
NpgsqlCommand dropCheck = new NpgsqlCommand("DROP TABLE checktime", conn);
dropCheck.ExecuteNonQuery();
NpgsqlCommand newCheck = new NpgsqlCommand("CREATE TABLE checktime (curr_up_date timestamp)", conn);
newCheck.ExecuteNonQuery();
using (var writer = conn.BeginBinaryImport("COPY checktime FROM STDIN (FORMAT BINARY)"))
{
writer.StartRow();
writer.Write(most_recent);
writer.Complete();
}
}
/// <remarks>
/// Closes the connection when we are finished with it
/// </remarks>
conn.Close();
}
}
private Log getLog(Guid habit_id)
{
NpgsqlDateTime lastExpCreatedAt = new NpgsqlDateTime(0);
DateTime[] AllLog = { };
string[] Alldays = { };
int currentStreak = 0;
int longestStreak = 0;
string query = "SELECT log, day, last_log_created_at FROM log_snapshot WHERE habit_id = @habit_id ORDER BY created_at DESC LIMIT 1";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habit_id", habit_id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
AllLog = (DateTime[])reader.GetValue(0);
Alldays = (string[])reader.GetValue(1);
lastExpCreatedAt = reader.GetTimeStamp(2);
}
reader.Close();
}
}
query = "SELECT log, day FROM logs WHERE habit_id = @habit_id AND created_at > @last_exp_created_at";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habit_id", habit_id);
cmd.Parameters.AddWithValue("last_exp_created_at", lastExpCreatedAt);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DateTime log = reader.GetDateTime(0);
string days = reader.GetString(1);
Array.Resize(ref AllLog, AllLog.Length + 1);
Array.Resize(ref Alldays, Alldays.Length + 1);
AllLog[AllLog.Length - 1] = log;
Alldays[Alldays.Length - 1] = days;
}
reader.Close();
}
}
query = "SELECT current_streak, longest_streak FROM logs WHERE habit_id = @habit_id ORDER BY created_at DESC LIMIT 1";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("habit_id", habit_id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
currentStreak = reader.GetInt32(0);
longestStreak = reader.GetInt32(1);
}
reader.Close();
}
}
return(LogFactory.Create(AllLog, currentStreak, longestStreak, Alldays));
}
public void Timestamp(DateTime dateTime)
{
var npgsqlTimeStamp = new NpgsqlDateTime(dateTime.Ticks);
var offset = TimeSpan.FromHours(2);
var dateTimeOffset = new DateTimeOffset(dateTime, offset);
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4, @p5, @p6", Conn))
{
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Timestamp);
var p2 = new NpgsqlParameter("p2", DbType.DateTime);
var p3 = new NpgsqlParameter("p3", DbType.DateTime2);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = npgsqlTimeStamp };
var p5 = new NpgsqlParameter { ParameterName = "p5", Value = dateTime };
var p6 = new NpgsqlParameter("p6", NpgsqlDbType.Timestamp);
Assert.That(p4.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(p4.DbType, Is.EqualTo(DbType.DateTime));
Assert.That(p5.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(p5.DbType, Is.EqualTo(DbType.DateTime));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
p1.Value = p2.Value = p3.Value = npgsqlTimeStamp;
p6.Value = dateTimeOffset;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
// Regular type (DateTime)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (DateTime)));
Assert.That(reader.GetDateTime(i), Is.EqualTo(dateTime));
Assert.That(reader.GetDateTime(i).Kind, Is.EqualTo(DateTimeKind.Unspecified));
Assert.That(reader.GetFieldValue<DateTime>(i), Is.EqualTo(dateTime));
Assert.That(reader[i], Is.EqualTo(dateTime));
Assert.That(reader.GetValue(i), Is.EqualTo(dateTime));
// Provider-specific type (NpgsqlTimeStamp)
Assert.That(reader.GetTimeStamp(i), Is.EqualTo(npgsqlTimeStamp));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof (NpgsqlDateTime)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(npgsqlTimeStamp));
Assert.That(reader.GetFieldValue<NpgsqlDateTime>(i), Is.EqualTo(npgsqlTimeStamp));
// DateTimeOffset
Assert.That(() => reader.GetFieldValue<DateTimeOffset>(i), Throws.Exception);
}
}
}
}
/// <summary>
/// Adds order to database
/// </summary>
/// <param name="cart">The cart of the user who is making the order</param>
/// <returns>ReceiptView</returns>
public IActionResult AddOrder(Cart cart)
{
// Get User
user = HttpContext.Session.GetObject <User>("User");
if (user == null)
{
Log.Error("User session was not found");
return(RedirectToAction("Login", "Home"));
}
// Ensure model state
if (ModelState.IsValid)
{
// Create new order object and set values
Order order = new Order();
order.totalCost = cart.totalCost;
order.locationId = user.locationId;
order.orderDate = DateTime.Now;
NpgsqlDateTime npgsqlDateTime = order.orderDate; // Used for API call
order.userId = user.id;
// Set up API calls
using (var client = new HttpClient())
{
client.BaseAddress = new Uri(url);
// Serialize order objecto to be added
var json = JsonConvert.SerializeObject(order);
var data = new StringContent(json, Encoding.UTF8, "application/json");
// Use POST method to add to DB
var response = client.PostAsync("order/add", data);
response.Wait();
while (response.Result.IsSuccessStatusCode)
{
// Added new order successfully
// Now get order we just added to map orderId
response = client.GetAsync($"order/get?dateTime={npgsqlDateTime}");
response.Wait();
var result = response.Result.Content.ReadAsStringAsync();
var newOrder = JsonConvert.DeserializeObject <Order>(result.Result);
Log.Information($"Successfully created order: {newOrder.id}");
// Got order back successfully
order.id = newOrder.id;
foreach (var item in user.cart.cartItems)
{
// For each item in cart, map to LineItem object
VideoGame videoGame = item.videoGame;
LineItem lineItem = new LineItem();
lineItem.orderId = order.id;
lineItem.videoGameId = item.videoGameId;
lineItem.cost = videoGame.cost;
lineItem.quantity = item.quantity;
// Serialize LineItem object and add to db using POST method
json = JsonConvert.SerializeObject(lineItem);
data = new StringContent(json, Encoding.UTF8, "application/json");
response = client.PostAsync("lineitem/add", data);
response.Wait();
Log.Information($"Successfully create lineItem: {json}");
// Added new line item successfully
// Get inventory item and update quantity
response = client.GetAsync($"inventoryitem/get/{user.locationId}/{item.videoGameId}");
response.Wait();
result = response.Result.Content.ReadAsStringAsync();
var inventoryItem = JsonConvert.DeserializeObject <InventoryItem>(result.Result);
inventoryItem.quantity -= item.quantity;
json = JsonConvert.SerializeObject(inventoryItem);
data = new StringContent(json, Encoding.UTF8, "application/json");
response = client.PutAsync("inventoryitem/update", data);
response.Wait();
Log.Information($"Successfully updated inventoryItem: {json}");
}
// Clear cart items and redirect to receipt view
user.cart.cartItems.Clear();
user.cart.totalCost = 0;
HttpContext.Session.SetObject("User", user);
return(RedirectToAction("Receipt", order));
}
}
}
Log.Error("Unable to create order");
alertService.Warning("Unable to complete order");
return(RedirectToAction("GetInventory", "Customer"));
}
public void TimestampTz()
{
var tzOffset = TimeZoneInfo.Local.BaseUtcOffset;
if (tzOffset == TimeSpan.Zero)
TestUtil.Inconclusive("Test cannot run when machine timezone is UTC");
var dateTimeUtc = new DateTime(2015, 1, 27, 8, 45, 12, 345, DateTimeKind.Utc);
var dateTimeLocal = dateTimeUtc.ToLocalTime();
var dateTimeUnspecified = new DateTime(dateTimeUtc.Ticks, DateTimeKind.Unspecified);
var nDateTimeUtc = new NpgsqlDateTime(dateTimeUtc);
var nDateTimeLocal = nDateTimeUtc.ToLocalTime();
var nDateTimeUnspecified = new NpgsqlDateTime(nDateTimeUtc.Ticks, DateTimeKind.Unspecified);
var dateTimeOffset = new DateTimeOffset(dateTimeLocal, tzOffset);
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9", Conn))
{
cmd.Parameters.AddWithValue("p1", NpgsqlDbType.TimestampTZ, dateTimeUtc);
cmd.Parameters.AddWithValue("p2", NpgsqlDbType.TimestampTZ, dateTimeLocal);
cmd.Parameters.AddWithValue("p3", NpgsqlDbType.TimestampTZ, dateTimeUnspecified);
cmd.Parameters.AddWithValue("p4", NpgsqlDbType.TimestampTZ, nDateTimeUtc);
cmd.Parameters.AddWithValue("p5", NpgsqlDbType.TimestampTZ, nDateTimeLocal);
cmd.Parameters.AddWithValue("p6", NpgsqlDbType.TimestampTZ, nDateTimeUnspecified);
cmd.Parameters.AddWithValue("p7", dateTimeUtc);
cmd.Parameters.AddWithValue("p8", nDateTimeUtc);
cmd.Parameters.AddWithValue("p9", dateTimeOffset);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
// Regular type (DateTime)
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(DateTime)));
Assert.That(reader.GetDateTime(i), Is.EqualTo(dateTimeUtc));
Assert.That(reader.GetFieldValue<DateTime>(i).Kind, Is.EqualTo(DateTimeKind.Utc));
Assert.That(reader[i], Is.EqualTo(dateTimeUtc));
Assert.That(reader.GetValue(i), Is.EqualTo(dateTimeUtc));
// Provider-specific type (NpgsqlDateTime)
Assert.That(reader.GetTimeStamp(i), Is.EqualTo(nDateTimeUtc));
Assert.That(reader.GetProviderSpecificFieldType(i), Is.EqualTo(typeof(NpgsqlDateTime)));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(nDateTimeUtc));
Assert.That(reader.GetFieldValue<NpgsqlDateTime>(i), Is.EqualTo(nDateTimeUtc));
// DateTimeOffset
Assert.That(reader.GetFieldValue<DateTimeOffset>(i), Is.EqualTo(dateTimeOffset.ToUniversalTime()));
}
}
}
}
public static DateTime timestampToDateTime(NpgsqlDateTime timestamp)
{
return(timestamp.ToDateTime());
}
