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; } }