public int AddItemToOrder(CreateOrderItemDTO entityToCreate)
{
try
{
string query = @"
INSERT INTO OrderItems (OrderHeaderID, ItemID, OrderItemStatusID, OrderItemUnitPrice, OrderItemUnitPriceAfterDiscount, OrderItemQty, OrderItemDescription)
VALUES(@OrderID, @ItemID, 1, @OrderItemUnitPrice, @OrderItemUnitPriceAfterDiscount, @OrderItemQty, @OrderItemDescription)
SELECT SCOPE_IDENTITY()";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderID", entityToCreate.OrderID);
queryParameters.Add("@ItemID", entityToCreate.ItemID);
queryParameters.Add("@OrderItemUnitPrice", entityToCreate.OrderItemUnitPrice);
queryParameters.Add("@OrderItemUnitPriceAfterDiscount", entityToCreate.OrderItemUnitPriceAfterDiscount);
queryParameters.Add("@OrderItemQty", entityToCreate.OrderItemQty);
queryParameters.Add("@OrderItemDescription", entityToCreate.OrderItemDescription);
return(Connection.QueryFirst <int>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public CustomerDTO Update(CustomerDTO updatedRecord)
{
try
{
string query = @"
UPDATE Customers
SET CustomerCode = @CustomerCode,
CustomerContactNumber = @CustomerContactNumber,
CustomerEmailAddress = @CustomerEmailAddress,
CustomerName = @CustomerName,
CustomerTypeID = @CustomerTypeID,
DefaultAddressID = @DefaultAddressID
WHERE CustomerID = @CustomerID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CustomerID", updatedRecord.CustomerID);
queryParameters.Add("@CustomerCode", updatedRecord.CustomerCode);
queryParameters.Add("@CustomerContactNumber", updatedRecord.CustomerContactNumber);
queryParameters.Add("@CustomerEmailAddress", updatedRecord.CustomerEmailAddress);
queryParameters.Add("@CustomerName", updatedRecord.CustomerName);
queryParameters.Add("@CustomerTypeID", updatedRecord.CustomerTypeID);
queryParameters.Add("@DefaultAddressID", updatedRecord.DefaultAddressID);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.CustomerID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public RMAItemDTO UpdateRMAItem(UpdateRMAItemDTO updatedRecord)
{
try
{
string query = @"
UPDATE RMAItems
SET ReturnQty = @ReturnQty,
ReturnReason = @ReturnReason,
ReturnToInventory = @ReturnToInventory
WHERE RMAItemID = @RMAItemID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@RMAItemID", updatedRecord.RMAItemID);
queryParameters.Add("@ReturnQty", updatedRecord.ReturnQty);
queryParameters.Add("@ReturnReason", updatedRecord.ReturnReason);
queryParameters.Add("@ReturnToInventory", updatedRecord.ReturnToInventory ? 1 : 0);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetRMAItemByID(updatedRecord.RMAItemID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <RMAItemDTO> GetItemsForRMA(int rmaID)
{
try
{
string query = @"
WITH UnprocessedReturns AS(
SELECT OrderItemID,SUM(ReturnQty) AS ReturnQty
FROM RMAItems
WHERE RMAItemStatusID = 1
GROUP BY OrderItemID
)
SELECT ri.RMAItemID,ri.OrderItemID,ri.ReturnQty,
oi.OrderItemDescription AS OrderItemDescription,
rs.RMAStatusValue AS [RMAItemStatus],
ri.ReturnToInventory,ri.ReturnReason,
oi.OrderItemQty - (oi.TotalReturnedQty + ISNULL(up.ReturnQty,0)) AS AllowedReturnQty
FROM RMAItems ri
INNER JOIN OrderItems oi ON oi.OrderItemID = ri.OrderItemID
INNER JOIN RMAStatus rs ON ri.RMAItemStatusID = rs.RMAStatusID
LEFT OUTER JOIN UnprocessedReturns up ON up.OrderItemID = oi.OrderItemID
WHERE ri.RMAHeaderID = @RMAID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@RMAID", rmaID);
return(Connection.Query <RMAItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <RMAPreviewDTO> GetRMASForOrder(int orderID)
{
try
{
string query = @"
SELECT rh.RMAHeaderID AS [RMAID],rh.OrderHeaderID AS [OrderID]
,rh.CreatedDate,c.CustomerName
,rs.RMAStatusValue AS [RMAStatusText]
FROM RMAHeaders rh
INNER JOIN OrderHeaders oh ON rh.OrderHeaderID = oh.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN RMAStatus rs ON rs.RMAStatusID = rh.RMAStatusID
WHERE oh.OrderHeaderID = @OrderID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderID", orderID);
return(Connection.Query <RMAPreviewDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public AboutPageDTO UpdateAboutPage(AboutPageDTO newPage)
{
try
{
string query = @"
UPDATE p
SET p.PageTitle = @PageTitle
,p.PageDescription = @PageDescription
,p.PageRoute = @PageRoute
,p.IsHTML = @IsHTML
FROM PageInfo p
INNER JOIN PageTypes pt on pt.PageTypeID = p.PageTypeID
WHERE pt.PageType = 'About'";
var queryParameters = new DynamicParameters();
queryParameters.Add("@PageTitle", newPage.PageTitle);
queryParameters.Add("@PageDescription", newPage.PageDescription);
queryParameters.Add("@PageRoute", newPage.PageRoute);
queryParameters.Add("@IsHTML", newPage.IsHTML);
return((Connection.Execute(query, queryParameters, CurrentTrans) > 0) ? GetAboutPage() : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public OrderItemDTO UpdateOrderItem(UpdateOrderItemDTO updatedRecord)
{
try
{
string query = @"
UPDATE OrderItems
SET ItemID = @ItemID,
OrderItemUnitPrice = @OrderItemUnitPrice,
OrderItemUnitPriceAfterDiscount = @OrderItemUnitPriceAfterDiscount,
OrderItemQty = @OrderItemQty,
OrderItemDescription = @OrderItemDescription
WHERE OrderItemID = @OrderItemID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@ItemID", updatedRecord.ItemID);
queryParameters.Add("@OrderItemDescription", updatedRecord.OrderItemDescription);
queryParameters.Add("@OrderItemID", updatedRecord.OrderItemID);
queryParameters.Add("@OrderItemQty", updatedRecord.OrderItemQty);
queryParameters.Add("@OrderItemUnitPrice", updatedRecord.OrderItemUnitPrice);
queryParameters.Add("@OrderItemUnitPriceAfterDiscount", updatedRecord.OrderItemUnitPriceAfterDiscount);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetOrderItemByID(updatedRecord.OrderItemID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <CreditNoteItemDTO> GetByID(int creditNoteID)
{
try
{
string query = @"
SELECT cn.CreditNoteID,cni.CreditNoteItemID,oh.OrderHeaderID AS [OrderID], rh.RMAHeaderID AS [RMAID],
cn.CreditDate, oi.OrderItemDescription AS [ItemDescription], ri.ReturnQty AS [CreditQty],oi.OrderItemUnitPriceAfterDiscount AS [ItemPrice],
ri.ReturnQty * oi.OrderItemUnitPriceAfterDiscount AS [CreditItemTotal],cnis.CreditNoteStatusValue AS [CreditNoteItemStatus],
cnhs.CreditNoteStatusValue AS [CreditNoteStatus], c.CustomerName,i.ItemCode
FROM CreditNotes cn
INNER JOIN CreditNoteItems cni ON cn.CreditNoteID = cni.CreditNoteID
INNER JOIN RMAHeaders rh ON rh.RMAHeaderID = cn.RMAHeaderID
INNER JOIN RMAItems ri ON ri.RMAHeaderID = rh.RMAHeaderID
INNER JOIN OrderItems oi ON oi.OrderItemID = ri.OrderItemID
INNER JOIN CreditNoteStatus cnis ON cnis.CreditNoteStatusID = cni.CreditNoteItemStatusID
INNER JOIN CreditNoteStatus cnhs ON cnhs.CreditNoteStatusID = cn.CreditNoteStatusID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = rh.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN Items i ON i.ItemID= oi.ItemID
WHERE cn.CreditNoteID = @CreditNoteID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CreditNoteID", creditNoteID);
return(Connection.Query <CreditNoteItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public OrderDTO GetByID(int id)
{
try
{
string query = @"
SELECT oh.OrderHeaderID AS [OrderID], oh.CustomerID, oh.AddressID,oh.OrderStatusID,
OrderDate, DeliveryDate, AddressLine1, AddressLine2, CityAreaName, CityName,
PostCode, CountryName, CustomerName, os.OrderstatusValue AS [OrderStatusText]
FROM OrderHeaders oh
INNER JOIN OrderStatus os ON os.OrderStatusID = oh.OrderStatusID
INNER JOIN Customers cust ON oh.CustomerID = cust.CustomerID
INNER JOIN AddressLocations addloc on addloc.AddressLocationID = oh.AddressID
INNER JOIN CityAreas ca ON ca.CityAreaID = addloc.CityAreaID
INNER JOIN Cities cit ON cit.CityID = ca.CityID
INNER JOIN Countries co ON co.CountryID = cit.CountryID
WHERE oh.OrderHeaderID = @OrderID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderID", id);
return(Connection.QueryFirst <OrderDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public CustomerDTO GetByID(int id)
{
try
{ //CONSIDER DEFAULTADDRESSTEXT BEING SENT BACK HERE.......
string query = @"
SELECT CustomerID,c.CustomerTypeID,CONVERT(VARCHAR(10),ct.CustomerTypeID)+ ' - ' + ct.CustomerTypeCode + ' - ' + ct.CustomerTypeName AS [CustomerTypeText],
DefaultAddressID,a.AddressLine1 + ' - ' + a.AddressLine2 + ' - ' + ca.CityAreaName + ' - ' + ci.CityName AS DefaultAddressText,
CustomerCode,CustomerName,CustomerContactNumber,CustomerEmailAddress
FROM Customers c
INNER JOIN CustomerTypes ct on c.CustomerTypeID = ct.CustomerTypeID
INNER JOIN AddressLocations a on c.DefaultAddressID = a.AddressLocationID
INNER JOIN CityAreas ca on a.CityAreaID = ca.CityAreaID
INNER JOIN Cities ci on ci.CityID = ca.CityID
WHERE c.CustomerID = @CustomerID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CustomerID", id);
return(Connection.QueryFirst <CustomerDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public SubGroupDTO Update(SubGroupDTO updatedRecord)
{
try
{
string query = @"
UPDATE SubGroups
SET SubGroupCode = @SubGroupCode
,SubGroupName = @SubGroupName
,SubGroupDescription = @SubGroupDescription
,ProductGroupID = @ProductGroupID
WHERE SubGroupID = @SubGroupID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@SubGroupID", updatedRecord.SubGroupID);
queryParameters.Add("@ProductGroupID", updatedRecord.ProductGroupID);
queryParameters.Add("@SubGroupCode", updatedRecord.SubGroupCode);
queryParameters.Add("@SubGroupName", updatedRecord.SubGroupName);
queryParameters.Add("@SubGroupDescription", updatedRecord.SubGroupDescription);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.SubGroupID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public UserProfileDTO Update(UserProfileDTO updatedRecord)
{
try
{
string query = @"
UPDATE Users
SET Username = @Username,
EncryptedPassword = @EncryptedPassword,
KnownAs = @KnownAs,
EmailAddress = @EmailAddress,
UserRoleTypeID = @UserRoleTypeID
WHERE UserID = @UserID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@Username", updatedRecord.Username);
queryParameters.Add("@EncryptedPassword", updatedRecord.EncryptedPassword);
queryParameters.Add("@KnownAs", updatedRecord.KnownAs);
queryParameters.Add("@EmailAddress", updatedRecord.EmailAddress);
queryParameters.Add("@UserID", updatedRecord.UserID);
queryParameters.Add("@UserRoleTypeID", updatedRecord.UserRoleTypeID);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.UserID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public SupplierDTO Update(SupplierDTO updatedRecord)
{
try
{
string query = @"
UPDATE Suppliers
SET SupplierCode = @SupplierCode,
SupplierName = @SupplierName,
SupplierContactNumber = @SupplierContactNumber,
SupplierEmailAddress = @SupplierEmailAddress
WHERE SupplierID = @SupplierID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@SupplierCode", updatedRecord.SupplierCode);
queryParameters.Add("@SupplierName", updatedRecord.SupplierName);
queryParameters.Add("@SupplierContactNumber", updatedRecord.SupplierContactNumber);
queryParameters.Add("@SupplierEmailAddress", updatedRecord.SupplierEmailAddress);
queryParameters.Add("@SupplierID", updatedRecord.SupplierID);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.SupplierID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public int Create(ItemCreateDTO entityToCreate)
{
try
{
string query = @"
INSERT INTO Items(ItemCode,SubGroupID,ItemName,ItemDescription,ItemUnitPrice,ItemUnitPriceWithMaxDiscount,ItemAvailableQty,ItemReorderQtyReminder,ItemImageFilename,IsFeaturedItem)
VALUES (@ItemCode,@SubGroupID,@ItemName,@ItemDescription,@ItemUnitPrice,@ItemUnitPriceWithMaxDiscount,@ItemAvailableQty,@ItemReorderQtyReminder,@ItemImageFilename,@IsFeaturedItem)
SELECT SCOPE_IDENTITY()";
var queryParameters = new DynamicParameters();
queryParameters.Add("@ItemCode", entityToCreate.ItemCode);
queryParameters.Add("@SubGroupID", entityToCreate.SubGroupID);
queryParameters.Add("@ItemName", entityToCreate.ItemName);
queryParameters.Add("@ItemDescription", entityToCreate.ItemDescription);
queryParameters.Add("@ItemUnitPrice", entityToCreate.ItemUnitPrice);
queryParameters.Add("@ItemUnitPriceWithMaxDiscount", entityToCreate.ItemUnitPriceWithMaxDiscount);
queryParameters.Add("@ItemAvailableQty", entityToCreate.ItemAvailableQty);
queryParameters.Add("@ItemReorderQtyReminder", entityToCreate.ItemReorderQtyReminder);
queryParameters.Add("@ItemImageFilename", ""); //Set Image to empty as this is available through the image update function.
queryParameters.Add("@IsFeaturedItem", entityToCreate.IsFeaturedItem ? 1 : 0);
return(Connection.QueryFirst <int>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public bool SetUserClaim(int userID, string claimType, string claimValue)
{
try
{
string query = @"
UPDATE uc
SET uc.ClaimValue = @ClaimValue
FROM UserClaims uc
INNER JOIN UserClaimTypes uct ON uct.UserClaimTypeID = uc.UserClaimTypeID
INNER JOIN Users u on u.UserID = uc.UserID
WHERE uct.UserClaimTypeName = @ClaimType
AND u.UserID = @UserID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@ClaimValue", claimValue);
queryParameters.Add("@ClaimType", claimType);
queryParameters.Add("@UserID", userID);
return(Connection.Execute(query, queryParameters, CurrentTrans) > 0);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <ReturnNoteItemDTO> GetByID(int returnNoteID)
{
try
{
string query = @"
SELECT rni.ReturnNoteItemID,rni.ReturnNoteID,rn.RMAHeaderID AS [RMAID],
oi.OrderHeaderID AS [OrderID], rn.ReturnedDate, oi.OrderItemID, oi.OrderItemDescription,
rmi.ReturnQty,c.CustomerName,i.ItemCode
FROM ReturnNoteItems rni
INNER JOIN ReturnNotes rn ON rni.ReturnNoteID = rn.ReturnNoteID
INNER JOIN RMAItems rmi ON rmi.RMAItemID = rni.RMAItemID
INNER JOIN OrderItems oi ON oi.OrderItemID = rmi.OrderItemID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = oi.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN Items i ON i.ItemID = oi.ItemID
WHERE rn.ReturnNoteID = @ReturnNoteID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@ReturnNoteID", returnNoteID);
return(Connection.Query <ReturnNoteItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public AddressDTO Update(AddressDTO updatedRecord)
{
try
{
string query = @"
UPDATE AddressLocations
SET AddressLine1 = @AddressLine1
,AddressLine2 = @AddressLine2
,PostCode = @PostCode
,CityAreaID = @CityAreaID
WHERE AddressLocationID = @AddressLocationID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@AddressLine1", updatedRecord.AddressLine1);
queryParameters.Add("@AddressLine2", updatedRecord.AddressLine2);
queryParameters.Add("@PostCode", updatedRecord.PostCode);
queryParameters.Add("@CityAreaID", updatedRecord.CityAreaID);
queryParameters.Add("@AddressLocationID", updatedRecord.AddressID);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.AddressID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public int Create(CustomerCreateDTO entityToCreate)
{
try
{
string query = @"
INSERT INTO Customers(CustomerCode, CustomerContactNumber, CustomerEmailAddress, CustomerName, CustomerTypeID, DefaultAddressID)
VALUES (@CustomerCode, @CustomerContactNumber, @CustomerEmailAddress,@CustomerName,@CustomerTypeID,@DefaultAddressID)
SELECT SCOPE_IDENTITY()";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CustomerCode", entityToCreate.CustomerCode);
queryParameters.Add("@CustomerContactNumber", entityToCreate.CustomerContactNumber);
queryParameters.Add("@CustomerEmailAddress", entityToCreate.CustomerEmailAddress);
queryParameters.Add("@CustomerName", entityToCreate.CustomerName);
queryParameters.Add("@CustomerTypeID", entityToCreate.CustomerTypeID);
queryParameters.Add("@DefaultAddressID", entityToCreate.DefaultAddressID);
return(Connection.QueryFirst <int>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public OrderDTO Update(UpdatedOrderDTO updatedRecord)
{
try
{
string query = @"
UPDATE OrderHeaders
SET CustomerID = @CustomerID,
AddressID = @AddressID,
OrderDate = @OrderDate,
DeliveryDate = @DeliveryDate
WHERE OrderHeaderID = @OrderID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CustomerID", updatedRecord.CustomerID);
queryParameters.Add("@AddressID", updatedRecord.AddressID);
queryParameters.Add("@OrderDate", updatedRecord.OrderDate);
queryParameters.Add("@DeliveryDate", updatedRecord.DeliveryDate);
queryParameters.Add("@OrderID", updatedRecord.OrderID);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.OrderID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <CreditNotePreviewDTO> GetCreditNotesForOrder(int orderID)
{
try
{
string query = @"
SELECT cn.CreditNoteID,rh.RMAHeaderID AS [RMAID],rh.OrderHeaderID AS [OrderID],cns.CreditNoteStatusValue AS [CreditNoteStatus],
cn.CreditDate AS [CreditNoteDate],SUM(oi.OrderItemUnitPriceAfterDiscount * ri.ReturnQty) AS [CreditTotal],c.CustomerName
FROM CreditNotes cn
INNER JOIN CreditNoteItems cni ON cn.CreditNoteID = cni.CreditNoteID
INNER JOIN RMAHeaders rh ON rh.RMAHeaderID = cn.RMAHeaderID
INNER JOIN CreditNoteStatus cns ON cns.CreditNoteStatusID = cn.CreditNoteStatusID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = rh.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN RMAItems ri ON ri.RMAHeaderID = rh.RMAHeaderID
INNER JOIN OrderItems oi ON ri.OrderItemID = oi.OrderItemID
WHERE rh.OrderHeaderID = @OrderHeaderID
GROUP BY cn.CreditNoteID, rh.RMAHeaderID, rh.OrderHeaderID, cns.CreditNoteStatusValue, cn.CreditDate, c.CustomerName";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderHeaderID", orderID);
return(Connection.Query <CreditNotePreviewDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <DeliveryNoteItemDTO> GetByID(int id)
{
try
{
string query = @"
SELECT DeliveryNoteItemID,dn.DeliveryNoteID,oh.OrderHeaderID,dn.DeliveryDate,
oi.OrderItemID,oi.OrderItemDescription,oi.OrderItemQty,c.CustomerName, i.ItemCode
FROM DeliveryNotes dn
INNER JOIN DeliveryNoteItems dni ON dn.DeliveryNoteID = dni.DeliveryNoteID
INNER JOIN OrderItems oi ON oi.OrderItemID = dni.OrderItemID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = oi.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN Items i ON i.ItemID = oi.ItemID
WHERE dn.DeliveryNoteID = @DeliveryNoteID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@DeliveryNoteID", id);
return(Connection.Query <DeliveryNoteItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <OrderItemDTO> GetReturnableItemsForOrder(int orderID)
{
try
{
string query = @"
WITH UnprocessedReturns AS(
SELECT OrderItemID,SUM(ReturnQty) AS ReturnQty
FROM RMAItems
WHERE RMAItemStatusID = 1
GROUP BY OrderItemID
)
SELECT oi.OrderItemID, oi.OrderHeaderID AS [OrderID], i.ItemID, OrderItemStatusID,
OrderItemUnitPrice, OrderItemUnitPriceAfterDiscount, OrderItemQty, i.ItemCode,
OrderItemDescription, os.OrderstatusValue AS [OrderItemStatusText],
oi.OrderItemUnitPriceAfterDiscount * oi.OrderItemQty AS [OrderItemTotal],
oi.OrderItemQty - (oi.TotalReturnedQty + ISNULL(up.ReturnQty,0)) AS AllowedReturnQty
FROM OrderItems oi
INNER JOIN Items i ON i.ItemID = oi.ItemID
INNER JOIN OrderStatus os ON os.OrderStatusID = oi.OrderItemStatusID
LEFT OUTER JOIN UnprocessedReturns up ON up.OrderItemID = oi.OrderItemID
WHERE OrderHeaderID = @OrderID
AND oi.OrderItemQty - (oi.TotalReturnedQty + ISNULL(up.ReturnQty,0)) > 0";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderID", orderID);
return(Connection.Query <OrderItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public CityAreaDTO Update(CityAreaDTO updatedRecord)
{
try
{
string query = @"
UPDATE CityAreas
SET CityAreaCode = @CityAreaCode
,CityAreaName = @CityAreaName
,CityID = @CityID
WHERE CityAreaID = @CityAreaID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@CityID", updatedRecord.CityID);
queryParameters.Add("@CityAreaID", updatedRecord.CityAreaID);
queryParameters.Add("@CityAreaCode", updatedRecord.CityAreaCode);
queryParameters.Add("@CityAreaName", updatedRecord.CityAreaName);
int rowsUpdated = Connection.Execute(query, queryParameters, CurrentTrans);
return((rowsUpdated > 0) ? GetByID(updatedRecord.CityAreaID) : throw noRecordEX);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <InvoicePreviewDTO> GetInvoicesForOrder(int orderID)
{
try
{
string query = @"
SELECT ih.InvoiceHeaderID AS [InvoiceID], ih.OrderHeaderID AS [OrderID], ins.InvoiceStatusValue AS [InvoiceStatus],ih.InvoiceDate,
CAST(ROUND(SUM(oi.OrderItemUnitPriceAfterDiscount * ii.InvoiceItemQty),2) AS NUMERIC(36,2)) AS [InvoiceTotal], c.CustomerName
FROM InvoiceHeaders ih
INNER JOIN InvoiceItems ii ON ih.InvoiceHeaderID = ii.InvoiceHeaderID
INNER JOIN OrderItems oi ON oi.OrderItemID = ii.OrderItemID
INNER JOIN InvoiceStatus ins ON ins.InvoiceStatusID = ih.InvoiceStatusID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = oi.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
WHERE ih.OrderHeaderID = @OrderID
GROUP BY ih.InvoiceHeaderID,ih.OrderHeaderID, ins.InvoiceStatusValue, ih.InvoiceDate, c.CustomerName
";
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderID", orderID);
return(Connection.Query <InvoicePreviewDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <InvoiceItemDTO> GetByID(int id)
{
try
{
string query = @"
SELECT ih.InvoiceHeaderID AS [InvoiceID], ii.InvoiceItemID, ih.OrderHeaderID AS [OrderID],
ih.InvoiceDate, oi.OrderItemDescription AS [ItemDescription],
oi.OrderItemQty AS [ItemQty], oi.OrderItemUnitPriceAfterDiscount AS [ItemPrice],
oi.OrderItemUnitPriceAfterDiscount * ii.InvoiceItemQty AS [ItemTotal],
ins.InvoiceStatusValue AS [InvoiceItemStatus], c.CustomerName, i.itemCode
FROM InvoiceHeaders ih
INNER JOIN InvoiceItems ii ON ih.InvoiceHeaderID = ii.InvoiceHeaderID
INNER JOIN OrderItems oi ON oi.OrderItemID = ii.OrderItemID
INNER JOIN InvoiceStatus ins ON ins.InvoiceStatusID = ii.InvoiceItemStatusID
INNER JOIN OrderHeaders oh ON oh.OrderHeaderID = oi.OrderHeaderID
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
INNER JOIN Items i ON i.ItemID = oi.ItemID
WHERE ih.InvoiceHeaderID = @InvoiceID";
var queryParameters = new DynamicParameters();
queryParameters.Add("@InvoiceID", id);
return(Connection.Query <InvoiceItemDTO>(query, queryParameters, CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public int InvoiceOrder(int orderID)
{
try
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderHeaderID", orderID);
int invoiceID = Connection.QueryFirst <int>("GenerateInvoiceForOrder", queryParameters, CurrentTrans, commandType: CommandType.StoredProcedure);
return(invoiceID);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public int DeliverOrder(int orderID)
{
try
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@OrderHeaderID", orderID);
int deliveryNoteID = Connection.QueryFirst <int>("DeliverExistingItems", queryParameters, CurrentTrans, commandType: CommandType.StoredProcedure);
return(deliveryNoteID);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public int ProcessReturn(int rmaID)
{
try
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@RMAHeaderID", rmaID);
int returnNoteID = Connection.QueryFirst <int>("ProcessReturn", queryParameters, CurrentTrans, commandType: CommandType.StoredProcedure);
return(returnNoteID);
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <CustomerPreviewDTO> GetAllPreview()
{
try
{
string query = @"
SELECT CustomerID,CustomerCode,CustomerName, CustomerContactNumber
FROM Customers c";
return(Connection.Query <CustomerPreviewDTO>(query, transaction: CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}
public IEnumerable <CustomerTypeDTO> GetCustomerTypes()
{
try
{
string query = @"
SELECT CustomerTypeID, CustomerTypeName
FROM CustomerTypes";
return(Connection.Query <CustomerTypeDTO>(query, transaction: CurrentTrans));
}
catch (Exception ex)
{
throw SqlExceptionHandler.HandleSqlException(ex) ?? ex;
}
}