public static int DeleteClinician(string hcpId)
{
return(SqlDataAccess.Execute <ClinicianModel>
(
@"DELETE FROM dbo.Clinician
WHERE HCPId = @HCPId",
new ClinicianModel {
HCPId = hcpId
}
));
}
public static int RemoveProduct(int productID)
{
ProductModel data = new ProductModel
{
ProductID = productID
};
string sql = @"delete from dbo.product
WHERE ProductID = @ProductID;";
return(SqlDataAccess.Execute(sql, data));
}
public static int RemoveBin(int binID)
{
BinModel data = new BinModel
{
BinID = binID
};
string sql = @"delete from dbo.bins
WHERE BinID = @BinID;";
return(SqlDataAccess.Execute(sql, data));
}
public static int RemoveOrder(int orderID)
{
OrderModel data = new OrderModel
{
OrderID = orderID
};
string sql = @"delete from dbo.orders
WHERE OrderID = @OrderID;";
return(SqlDataAccess.Execute(sql, data));
}
public static void RemoveInventory(int inventoryID)
{
InventoryModel data = new InventoryModel
{
InventoryID = inventoryID
};
string sql = @"delete from dbo.inventory
WHERE InventoryID = @InventoryID;";
SqlDataAccess.Execute(sql, data);
}
public static int CreateProduct(string sku, string description)
{
ProductModel data = new ProductModel
{
SKU = sku,
ProductDescription = description
};
string sql = @"insert into dbo.product (SKU, ProductDescription)
values (@SKU, @ProductDescription);";
return(SqlDataAccess.Execute(sql, data));
}
public static int CreateBin(int binID, string binName)
{
BinModel data = new BinModel
{
BinID = binID,
BinName = binName
};
string sql = @"insert into dbo.bins (BinName)
values (@BinName);";
return(SqlDataAccess.Execute(sql, data));
}
public static int UpdateBin(int binID, string binName)
{
BinModel data = new BinModel
{
BinID = binID,
BinName = binName
};
string sql = @"update dbo.bins
set BinName = @BinName WHERE BinID = @BinID;";
return(SqlDataAccess.Execute(sql, data));
}
public static int UpdateProduct(int productID, string sku, string description)
{
ProductModel data = new ProductModel
{
ProductID = productID,
SKU = sku,
ProductDescription = description
};
string sql = @"update dbo.product
set SKU = @SKU, ProductDescription = @ProductDescription
WHERE ProductID = @ProductID;";
return(SqlDataAccess.Execute(sql, data));
}
public static void CreateInventory(int inventoryID, int productID, int binID, int qty)
{
InventoryModel data = new InventoryModel
{
InventoryID = inventoryID,
ProductID = productID,
BinID = binID,
QTY = qty
};
string sql = @"insert into dbo.inventory (ProductID, BinID, QTY)
values (@ProductID, @BinID, @QTY);";
SqlDataAccess.Execute(sql, data);
}
public static int RemoveOrderLine(int orderLineID)
{
var previousState = LoadOrderLine(orderLineID);
AdjustInventory(previousState.ProductID, previousState.QTY * -1);
OrderLinesModel data = new OrderLinesModel
{
OrderLineID = orderLineID
};
string sql = @"delete from dbo.orderlines
WHERE OrderLineID = @OrderLineID;";
return(SqlDataAccess.Execute(sql, data));
}
public static int UpdateOrderLines(int orderLineID, int orderID, int productID, int qty)
{
OrderLinesModel data = new OrderLinesModel
{
OrderLineID = orderLineID,
OrderID = orderID,
ProductID = productID,
QTY = qty
};
string sql = @"update dbo.orderlines
set ProductID = @ProductID, QTY = @QTY
WHERE OrderLineID = @OrderLineID;";
return(SqlDataAccess.Execute(sql, data));
}
public static void UpdateInventory(int inventoryID, int productID, int binID, int qty)
{
InventoryModel data = new InventoryModel
{
InventoryID = inventoryID,
ProductID = productID,
BinID = binID,
QTY = qty
};
string sql = @"update dbo.inventory
set ProductID = @ProductID, BinID = @BinID,
QTY = @qty
WHERE InventoryID = @InventoryID;";
SqlDataAccess.Execute(sql, data);
}
public static int UpdateOrder(int orderID, string orderNumber, DateTime dateordered, string customerName, string customerAddress)
{
OrderModel data = new OrderModel
{
OrderID = orderID,
OrderNumber = orderNumber,
DateOrdered = dateordered,
CustomerName = customerName,
CustomerAddress = customerAddress
};
string sql = @"update dbo.orders
set OrderNumber = @OrderNumber, DateOrdered = @DateOrdered,
CustomerName = @CustomerName, CustomerAddress = @CustomerAddress
WHERE OrderID = @OrderID;";
return(SqlDataAccess.Execute(sql, data));
}
public static void CreateOrderLine(int orderID, int productID, int totalQuantity)
{
AdjustInventory(productID, totalQuantity);
OrderLinesModel data = new OrderLinesModel
{
OrderID = orderID,
ProductID = productID,
QTY = totalQuantity
};
string sql = @"insert into dbo.orderlines (OrderID, ProductID, QTY)
values (@OrderID, @ProductID, @QTY);";
try
{
SqlDataAccess.Execute(sql, data);
}
catch (System.Exception)
{
AdjustInventory(productID, totalQuantity * -1);
throw;
}
}