public void SetAccountInfo([FromBody] SetAccountInfo accountInfo)
{
//TODO: use transactions here and in any other multi-call endpoints
var deletedRecords = DBFacilitator.GetInteger(
PostgreSQLConnectionString,
DELETE_ACCOUNT,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":UniqueId", accountInfo.UniqueId.ToString(), NpgsqlDbType.Integer) }
});
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
INSERT_ACCOUNT,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":UniqueId", accountInfo.UniqueId.ToString(), NpgsqlDbType.Integer) },
{ new Tuple <string, string, NpgsqlDbType>(":Email", accountInfo.AddressInfo.Email, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":FirstName", accountInfo.AddressInfo.FirstName, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":LastName", accountInfo.AddressInfo.LastName, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Address1", accountInfo.AddressInfo.Address1, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Address2", accountInfo.AddressInfo.Address2, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":City", accountInfo.AddressInfo.City, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":State", accountInfo.AddressInfo.State, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Zip", accountInfo.AddressInfo.Zip, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Country", accountInfo.AddressInfo.Country, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Phone", accountInfo.AddressInfo.Phone, NpgsqlDbType.Text) }
});
}
public void UpdateActivityDates([FromBody] ActivityDates dates)
{
if (dates.ActivityOnly)
{
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
UPDATE_ACTIVITY_DATES_ACTIVITY_ONLY,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":Username", dates.Username, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":ApplicationName", dates.AppName, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":LastActivityDate", dates.ActivityOnly ? "Y" : "N", NpgsqlDbType.Date) }
}
);
}
else
{
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
UPDATE_ACTIVITY_DATES,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":Username", dates.Username, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":ApplicationName", dates.AppName, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":LastActivityDate", DateTime.Now.ToString(), NpgsqlDbType.Date) },
{ new Tuple <string, string, NpgsqlDbType>(":LastUpdatedDate", DateTime.Now.ToString(), NpgsqlDbType.Date) }
}
);
}
}
public void PostOrder([FromBody] OrderInfo orderInfo)
{
int?highestOrderId = DBFacilitator.GetInteger(PostgreSQLConnectionString, SQL_GET_HIGHEST_ORDERID, new List <Tuple <string, string, NpgsqlDbType> >());
highestOrderId = highestOrderId.HasValue ? highestOrderId + 1 : 0;
var sb = new StringBuilder("");
sb.Append("INSERT INTO \"MSPETSHOP4ORDERS\".Orders VALUES(");
sb.Append("'" + highestOrderId + "', ");
sb.Append("'" + orderInfo.UserId + "', ");
sb.Append("'" + orderInfo.Date + "', ");
sb.Append("'" + orderInfo.ShippingAddress.Address1 + "', ");
sb.Append("'" + orderInfo.ShippingAddress.Address2 + "', ");
sb.Append("'" + orderInfo.ShippingAddress.City + "', ");
sb.Append("'" + orderInfo.ShippingAddress.State + "', ");
sb.Append("'" + orderInfo.ShippingAddress.Zip + "', ");
sb.Append("'" + orderInfo.ShippingAddress.Country + "', ");
sb.Append("'" + orderInfo.BillingAddress.Address1 + "', ");
sb.Append("'" + orderInfo.BillingAddress.Address2 + "', ");
sb.Append("'" + orderInfo.BillingAddress.City + "', ");
sb.Append("'" + orderInfo.BillingAddress.State + "', ");
sb.Append("'" + orderInfo.BillingAddress.Zip + "', ");
sb.Append("'" + orderInfo.BillingAddress.Country + "', ");
sb.Append("'" + " UPS',");
sb.Append("'" + orderInfo.OrderTotal + "', ");
sb.Append("'" + orderInfo.BillingAddress.FirstName + "', ");
sb.Append("'" + orderInfo.BillingAddress.LastName + "', ");
sb.Append("'" + orderInfo.ShippingAddress.FirstName + "', ");
sb.Append("'" + orderInfo.ShippingAddress.LastName + "', ");
sb.Append("'" + orderInfo.AuthorizationNumber + "', ");
sb.Append("'US-en');\n");
sb.Append("INSERT INTO \"MSPETSHOP4ORDERS\".OrderStatus VALUES(");
sb.Append("'" + highestOrderId + "', ");
sb.Append("'" + "0', ");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', ");
sb.Append("'P'");
sb.Append(");\n");
foreach (LineItemInfo info in orderInfo.LineItems)
{
sb.Append("INSERT INTO \"MSPETSHOP4ORDERS\".LineItem VALUES(");
sb.Append("'" + highestOrderId + "', ");
sb.Append("'" + info.Line + "', ");
sb.Append("'" + info.ItemId + "', ");
sb.Append("'" + info.Quantity + "', ");
sb.Append("'" + info.Price);
sb.Append("');\n");
sb.Append("UPDATE \"MSPETSHOP4\".Inventory SET Qty = Qty - " + info.Quantity + " WHERE ItemId = '" + info.ItemId + "';\n");
}
DBFacilitator.ExecuteCommand(PostgreSQLConnectionString, sb.ToString(), new List <Tuple <string, string, NpgsqlDbType> >());
}
public void Post([FromBody] LineItemInfo[] info)
{
foreach (var lineitem in info)
{
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
SQL_UPDATE_INVENTORY,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>("itemId", lineitem.ItemId, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>("quantity", lineitem.Quantity.ToString(), NpgsqlDbType.Integer) }
}
);
}
}
public void SetCartItems([FromBody] SetCartItems cartItemInfo)
{
string uniqueId = cartItemInfo.UniqueID.ToString();
string isCart = cartItemInfo.IsShoppingCart ? "Y" : "N";
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
DELETE_CART,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":UniqueId", uniqueId, NpgsqlDbType.Integer) },
{ new Tuple <string, string, NpgsqlDbType>(":IsShoppingCart", isCart, NpgsqlDbType.Char) }
}
);
foreach (var item in cartItemInfo.CartItems)
{
DBFacilitator.ExecuteCommand(
PostgreSQLConnectionString,
INSERT_CART_ITEMS,
new List <Tuple <string, string, NpgsqlDbType> >()
{
{ new Tuple <string, string, NpgsqlDbType>(":UniqueId", uniqueId, NpgsqlDbType.Integer) },
{ new Tuple <string, string, NpgsqlDbType>(":IsShoppingCart", isCart, NpgsqlDbType.Char) },
{ new Tuple <string, string, NpgsqlDbType>(":ItemId", item.ItemId, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Name", item.Name, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Type", item.Type, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":CategoryId", item.CategoryId, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":ProductId", item.ProductId, NpgsqlDbType.Text) },
{ new Tuple <string, string, NpgsqlDbType>(":Quantity", item.Quantity.ToString(), NpgsqlDbType.Numeric) },
{ new Tuple <string, string, NpgsqlDbType>(":Price", item.Price.ToString(), NpgsqlDbType.Numeric) }
}
);
}
}