//Van de warehouse id, code, name; can phai xem xet lai!LEMINHHIEP
private void GetQuotationViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @QuotationID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM Quotations WHERE QuotationID = @QuotationID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM QuotationDetails WHERE QuotationID = @QuotationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT QuotationDetails.QuotationDetailID, QuotationDetails.QuotationID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, " + "\r\n";
queryString = queryString + " CommoditiesAvailable.WarehouseID, CommoditiesAvailable.WarehouseCode, ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, QuotationDetails.Quantity, QuotationDetails.QuantityInvoice, QuotationDetails.ListedPrice, QuotationDetails.DiscountPercent, QuotationDetails.UnitPrice, QuotationDetails.VATPercent, QuotationDetails.GrossPrice, QuotationDetails.Amount, QuotationDetails.VATAmount, QuotationDetails.GrossAmount, QuotationDetails.IsBonus, QuotationDetails.IsWarrantyClaim, QuotationDetails.Remarks " + "\r\n";
queryString = queryString + " FROM QuotationDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON QuotationDetails.QuotationID = @QuotationID AND QuotationDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT CommodityID, MIN(WarehouseID) AS WarehouseID, MIN(WarehouseCode) AS WarehouseCode, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY CommodityID) CommoditiesAvailable ON QuotationDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetQuotationViewDetails", queryString);
}
private void GetPartTransferViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @StockTransferID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM StockTransfers WHERE StockTransferID = @StockTransferID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM StockTransferDetails WHERE StockTransferID = @StockTransferID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT StockTransferDetails.StockTransferDetailID, StockTransferDetails.StockTransferID, StockTransferDetails.TransferOrderDetailID, StockTransferDetails.SupplierID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(ISNULL(CommoditiesAvailable.QuantityAvailable, 0) + StockTransferDetails.Quantity, 0) AS QuantityAvailable, StockTransferDetails.Quantity, StockTransferDetails.Remarks " + "\r\n";
queryString = queryString + " FROM StockTransferDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON StockTransferDetails.StockTransferID = @StockTransferID AND StockTransferDetails.CommodityID = Commodities.CommodityID INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON StockTransferDetails.WarehouseID = Warehouses.WarehouseID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON StockTransferDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND StockTransferDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPartTransferViewDetails", queryString);
}
private void GetPartTransferViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @StockTransferID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM StockTransfers WHERE StockTransferID = @StockTransferID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM StockTransferDetails WHERE StockTransferID = @StockTransferID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT StockTransferDetails.StockTransferDetailID, StockTransferDetails.StockTransferID, StockTransferDetails.TransferOrderDetailID, StockTransferDetails.SupplierID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(ISNULL(CommoditiesAvailable.QuantityAvailable, 0) + StockTransferDetails.Quantity, 0) AS QuantityAvailable, StockTransferDetails.Quantity, StockTransferDetails.Remarks " + "\r\n";
queryString = queryString + " FROM StockTransferDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON StockTransferDetails.StockTransferID = @StockTransferID AND StockTransferDetails.CommodityID = Commodities.CommodityID INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON StockTransferDetails.WarehouseID = Warehouses.WarehouseID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON StockTransferDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND StockTransferDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPartTransferViewDetails", queryString);
}
private void GetPartAdjustmentViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @InventoryAdjustmentID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM InventoryAdjustments WHERE InventoryAdjustmentID = @InventoryAdjustmentID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";//The best way is get the @WarehouseIDList from table InventoryAdjustmentDetails, but we don't want the stored procedure read from InventoryAdjustmentDetails to save the resource
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM InventoryAdjustmentDetails WHERE InventoryAdjustmentID = @InventoryAdjustmentID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT InventoryAdjustmentDetails.InventoryAdjustmentDetailID, InventoryAdjustmentDetails.InventoryAdjustmentID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, InventoryAdjustmentDetails.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(ISNULL(CommoditiesAvailable.QuantityAvailable, 0) - InventoryAdjustmentDetails.Quantity, 0) AS QuantityAvailable, InventoryAdjustmentDetails.Quantity, InventoryAdjustmentDetails.UnitPrice, InventoryAdjustmentDetails.VATPercent, InventoryAdjustmentDetails.GrossPrice, InventoryAdjustmentDetails.Amount, InventoryAdjustmentDetails.VATAmount, InventoryAdjustmentDetails.GrossAmount, InventoryAdjustmentDetails.Remarks " + "\r\n";
queryString = queryString + " FROM InventoryAdjustmentDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON InventoryAdjustmentDetails.InventoryAdjustmentID = @InventoryAdjustmentID AND InventoryAdjustmentDetails.CommodityID = Commodities.CommodityID INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON InventoryAdjustmentDetails.WarehouseID = Warehouses.WarehouseID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON InventoryAdjustmentDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND InventoryAdjustmentDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPartAdjustmentViewDetails", queryString);
}
private void GetPartsInvoiceViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @SalesInvoiceID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM SalesInvoices WHERE SalesInvoiceID = @SalesInvoiceID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";//The best way is get the @WarehouseIDList from table SalesInvoiceDetails, but we don't want the stored procedure read from SalesInvoiceDetails to save the resource
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM SalesInvoiceDetails WHERE SalesInvoiceID = @SalesInvoiceID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT SalesInvoiceDetails.SalesInvoiceDetailID, SalesInvoiceDetails.SalesInvoiceID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, SalesInvoiceDetails.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(ISNULL(CommoditiesAvailable.QuantityAvailable, 0) + SalesInvoiceDetails.Quantity, 0) AS QuantityAvailable, SalesInvoiceDetails.Quantity, SalesInvoiceDetails.ListedPrice, SalesInvoiceDetails.DiscountPercent, SalesInvoiceDetails.UnitPrice, SalesInvoiceDetails.VATPercent, SalesInvoiceDetails.GrossPrice, SalesInvoiceDetails.Amount, SalesInvoiceDetails.VATAmount, SalesInvoiceDetails.GrossAmount, SalesInvoiceDetails.IsBonus, SalesInvoiceDetails.IsWarrantyClaim, SalesInvoiceDetails.Remarks " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON SalesInvoiceDetails.SalesInvoiceID = @SalesInvoiceID AND SalesInvoiceDetails.CommodityID = Commodities.CommodityID INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON SalesInvoiceDetails.WarehouseID = Warehouses.WarehouseID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON SalesInvoiceDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND SalesInvoiceDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPartsInvoiceViewDetails", queryString);
}
//Van de warehouse id, code, name; can phai xem xet lai!LEMINHHIEP
private void GetQuotationViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @QuotationID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @LocationID varchar(35) DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID FROM Quotations WHERE QuotationID = @QuotationID " + "\r\n";
queryString = queryString + " IF @EntryDate IS NULL SET @EntryDate = CONVERT(Datetime, '31/12/2000', 103)" + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM QuotationDetails WHERE QuotationID = @QuotationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " SELECT QuotationDetails.QuotationDetailID, QuotationDetails.QuotationID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, " + "\r\n";
queryString = queryString + " CommoditiesAvailable.WarehouseID, CommoditiesAvailable.WarehouseCode, ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, QuotationDetails.Quantity, QuotationDetails.QuantityInvoice, QuotationDetails.ListedPrice, QuotationDetails.DiscountPercent, QuotationDetails.UnitPrice, QuotationDetails.VATPercent, QuotationDetails.GrossPrice, QuotationDetails.Amount, QuotationDetails.VATAmount, QuotationDetails.GrossAmount, QuotationDetails.IsBonus, QuotationDetails.IsWarrantyClaim, QuotationDetails.Remarks " + "\r\n";
queryString = queryString + " FROM QuotationDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON QuotationDetails.QuotationID = @QuotationID AND QuotationDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT CommodityID, MIN(WarehouseID) AS WarehouseID, MIN(WarehouseCode) AS WarehouseCode, SUM(QuantityEndREC) AS QuantityAvailable FROM @WarehouseJournalTable GROUP BY CommodityID) CommoditiesAvailable ON QuotationDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetQuotationViewDetails", queryString);
}
private string GetCommoditiesInWarehousesGETAvailable(bool withCommoditiesInGoodsReceipts)
{
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
string queryString = " DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM @Commodities FOR XML PATH('')) ,1,1,'') " + "\r\n";
if (withCommoditiesInGoodsReceipts)
{//GET QuantityAvailable IN GoodsReceiptDetails FOR GlobalEnums.CommodityTypeID.Vehicles
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, ROUND(Quantity - QuantityIssue, 0) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") AND ROUND(Quantity - QuantityIssue, 0) > 0 AND WarehouseID IN (SELECT WarehouseID FROM Warehouses WHERE LocationID = @LocationID) AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @HasCommoditiesAvailable + @@ROWCOUNT " + "\r\n";
}
else
{//GET SavedData
queryString = queryString + " IF (@SalesInvoiceID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, Quantity AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails " + "\r\n";
queryString = queryString + " WHERE SalesInvoiceID = @SalesInvoiceID AND LocationID = @LocationID AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @@ROWCOUNT " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " IF (@StockTransferID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, Quantity AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM StockTransferDetails " + "\r\n";
queryString = queryString + " WHERE StockTransferID = @StockTransferID AND LocationID = @LocationID AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @@ROWCOUNT " + "\r\n";
queryString = queryString + " END " + "\r\n";
//queryString = queryString + " IF (@StockAdjustID > 0) " + "\r\n";
}
//GET QuantityEndREC IN WarehouseJournal
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, QuantityEndREC AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @HasCommoditiesAvailable + @@ROWCOUNT " + "\r\n";
return(queryString);
}
private string GetCommoditiesInWarehousesGETAvailable(bool withCommoditiesInGoodsReceipts)
{
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
string queryString = " DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT ',' + CAST(WarehouseID as varchar) FROM Warehouses WHERE LocationID = @LocationID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID as varchar) FROM @Commodities FOR XML PATH('')) ,1,1,'') " + "\r\n";
if (withCommoditiesInGoodsReceipts)
{//GET QuantityAvailable IN GoodsReceiptDetails FOR GlobalEnums.CommodityTypeID.Vehicles
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, ROUND(Quantity - QuantityIssue, 0) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") AND ROUND(Quantity - QuantityIssue, 0) > 0 AND WarehouseID IN (SELECT WarehouseID FROM Warehouses WHERE LocationID = @LocationID) AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @HasCommoditiesAvailable + @@ROWCOUNT " + "\r\n";
}
else
{//GET SavedData
queryString = queryString + " IF (@SalesInvoiceID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, Quantity AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails " + "\r\n";
queryString = queryString + " WHERE SalesInvoiceID = @SalesInvoiceID AND LocationID = @LocationID AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @@ROWCOUNT " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " IF (@StockTransferID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, Quantity AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM StockTransferDetails " + "\r\n";
queryString = queryString + " WHERE StockTransferID = @StockTransferID AND LocationID = @LocationID AND CommodityID IN (SELECT CommodityID FROM @Commodities) " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @@ROWCOUNT " + "\r\n";
queryString = queryString + " END " + "\r\n";
//queryString = queryString + " IF (@StockAdjustID > 0) " + "\r\n";
}
//GET QuantityEndREC IN WarehouseJournal
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, QuantityEndREC AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " SET @HasCommoditiesAvailable = @HasCommoditiesAvailable + @@ROWCOUNT " + "\r\n";
return queryString;
}
private void GetTransferOrderViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @TransferOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " DECLARE @CommoditiesAvailable TABLE (WarehouseID int NOT NULL, CommodityID int NOT NULL, QuantityAvailable decimal(18, 2) NOT NULL)" + "\r\n";
queryString = queryString + " DECLARE @TransferOrderDetails TABLE (TransferOrderDetailID int NOT NULL, EntryDate datetime NOT NULL, TransferOrderID int NOT NULL, CommodityID int NOT NULL, CommodityTypeID int NOT NULL, WarehouseID int NOT NULL, Quantity decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @TransferOrderDetails (TransferOrderDetailID, EntryDate, TransferOrderID, CommodityID, CommodityTypeID, WarehouseID, Quantity, Remarks) SELECT TransferOrderDetailID, EntryDate, TransferOrderID, CommodityID, CommodityTypeID, WarehouseID, Quantity, Remarks FROM TransferOrderDetails WHERE TransferOrderID = @TransferOrderID " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT DISTINCT ',' + CAST(WarehouseID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT DISTINCT ',' + CAST(CommodityID as varchar) FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Parts + ", " + (int)GlobalEnums.CommodityTypeID.Consumables + ") FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " IF NOT @CommodityIDList IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SET @EntryDate = GETDATE() " + "\r\n"; //GET INVENTORY UP TO DATE
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, QuantityEndREC AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " IF NOT (SELECT TOP 1 TransferOrderDetailID FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") ) IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, ROUND(Quantity - QuantityIssue, 0) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " WHERE ROUND(Quantity - QuantityIssue, 0) > 0 AND WarehouseID IN (SELECT DISTINCT WarehouseID FROM @TransferOrderDetails) AND CommodityID IN (SELECT DISTINCT CommodityID FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") ) " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " SELECT TransferOrderDetails.TransferOrderDetailID, TransferOrderDetails.TransferOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, TransferOrderDetails.Quantity, TransferOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM @TransferOrderDetails TransferOrderDetails INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON TransferOrderDetails.WarehouseID = Warehouses.WarehouseID INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON TransferOrderDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityAvailable) AS QuantityAvailable FROM @CommoditiesAvailable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON TransferOrderDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND TransferOrderDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetTransferOrderViewDetails", queryString);
}
private void GetTransferOrderViewDetails()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @TransferOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " DECLARE @CommoditiesAvailable TABLE (WarehouseID int NOT NULL, CommodityID int NOT NULL, QuantityAvailable decimal(18, 2) NOT NULL)" + "\r\n";
queryString = queryString + " DECLARE @TransferOrderDetails TABLE (TransferOrderDetailID int NOT NULL, EntryDate datetime NOT NULL, TransferOrderID int NOT NULL, CommodityID int NOT NULL, CommodityTypeID int NOT NULL, WarehouseID int NOT NULL, Quantity decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @TransferOrderDetails (TransferOrderDetailID, EntryDate, TransferOrderID, CommodityID, CommodityTypeID, WarehouseID, Quantity, Remarks) SELECT TransferOrderDetailID, EntryDate, TransferOrderID, CommodityID, CommodityTypeID, WarehouseID, Quantity, Remarks FROM TransferOrderDetails WHERE TransferOrderID = @TransferOrderID " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT DISTINCT ',' + CAST(WarehouseID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT DISTINCT ',' + CAST(CommodityID as varchar) FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Parts + ", " + (int)GlobalEnums.CommodityTypeID.Consumables + ") FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " IF NOT @CommodityIDList IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SET @EntryDate = GETDATE() " + "\r\n"; //GET INVENTORY UP TO DATE
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, QuantityEndREC AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " IF NOT (SELECT TOP 1 TransferOrderDetailID FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") ) IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, ROUND(Quantity - QuantityIssue, 0) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " WHERE ROUND(Quantity - QuantityIssue, 0) > 0 AND WarehouseID IN (SELECT DISTINCT WarehouseID FROM @TransferOrderDetails) AND CommodityID IN (SELECT DISTINCT CommodityID FROM @TransferOrderDetails WHERE CommodityTypeID IN (" + (int)GlobalEnums.CommodityTypeID.Vehicles + ") ) " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " SELECT TransferOrderDetails.TransferOrderDetailID, TransferOrderDetails.TransferOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, TransferOrderDetails.Quantity, TransferOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM @TransferOrderDetails TransferOrderDetails INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON TransferOrderDetails.WarehouseID = Warehouses.WarehouseID INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON TransferOrderDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " (SELECT WarehouseID, CommodityID, SUM(QuantityAvailable) AS QuantityAvailable FROM @CommoditiesAvailable GROUP BY WarehouseID, CommodityID) CommoditiesAvailable ON TransferOrderDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND TransferOrderDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetTransferOrderViewDetails", queryString);
}
private void GetPendingPartTransferOrders()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @LocationID Int, @TransferOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " DECLARE @CommoditiesAvailable TABLE (WarehouseID int NOT NULL, CommodityID int NOT NULL, QuantityAvailable decimal(18, 2) NOT NULL)" + "\r\n";
queryString = queryString + " DECLARE @TransferOrderDetails TABLE (TransferOrderDetailID int NOT NULL, CommodityID int NOT NULL, WarehouseID int NOT NULL, Quantity decimal(18, 2) NOT NULL, QuantityTransfer decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @TransferOrderDetails (TransferOrderDetailID, CommodityID, WarehouseID, Quantity, QuantityTransfer, Remarks) SELECT TransferOrderDetailID, CommodityID, WarehouseID, Quantity, QuantityTransfer, Remarks FROM TransferOrderDetails WHERE TransferOrderID = @TransferOrderID AND (CommodityTypeID = " + (int)GlobalEnums.CommodityTypeID.Parts + " OR CommodityTypeID = " + (int)GlobalEnums.CommodityTypeID.Consumables + ") AND ROUND(Quantity - QuantityTransfer, " + GlobalEnums.rndQuantity + ") > 0 " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT DISTINCT ',' + CAST(WarehouseID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT DISTINCT ',' + CAST(CommodityID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " IF NOT @CommodityIDList IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SET @EntryDate = GETDATE() " + "\r\n"; //GET INVENTORY UP TO DATE
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " GROUP BY WarehouseID, CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " SELECT TransferOrderDetails.TransferOrderDetailID, NULL AS SupplierID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(TransferOrderDetails.Quantity - TransferOrderDetails.QuantityTransfer, " + GlobalEnums.rndQuantity + ") AS QuantityOrderPending, ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, TransferOrderDetails.Remarks, CAST(IIF(CommoditiesAvailable.QuantityAvailable > 0, 1, 0) AS bit) AS IsSelected " + "\r\n";
queryString = queryString + " FROM @TransferOrderDetails TransferOrderDetails INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON TransferOrderDetails.WarehouseID = Warehouses.WarehouseID AND Warehouses.LocationID = @LocationID INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON TransferOrderDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " @CommoditiesAvailable CommoditiesAvailable ON TransferOrderDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND TransferOrderDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPendingPartTransferOrders", queryString);
}
private void GetPendingPartTransferOrders()
{
string queryString;
SqlProgrammability.StockTasks.Inventories inventories = new StockTasks.Inventories(this.totalBikePortalsEntities);
queryString = " @LocationID Int, @TransferOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @EntryDate DateTime DECLARE @WarehouseIDList varchar(35) DECLARE @CommodityIDList varchar(3999) " + "\r\n";
queryString = queryString + " DECLARE @CommoditiesAvailable TABLE (WarehouseID int NOT NULL, CommodityID int NOT NULL, QuantityAvailable decimal(18, 2) NOT NULL)" + "\r\n";
queryString = queryString + " DECLARE @TransferOrderDetails TABLE (TransferOrderDetailID int NOT NULL, CommodityID int NOT NULL, WarehouseID int NOT NULL, Quantity decimal(18, 2) NOT NULL, QuantityTransfer decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @TransferOrderDetails (TransferOrderDetailID, CommodityID, WarehouseID, Quantity, QuantityTransfer, Remarks) SELECT TransferOrderDetailID, CommodityID, WarehouseID, Quantity, QuantityTransfer, Remarks FROM TransferOrderDetails WHERE TransferOrderID = @TransferOrderID AND (CommodityTypeID = " + (int)GlobalEnums.CommodityTypeID.Parts + " OR CommodityTypeID = " + (int)GlobalEnums.CommodityTypeID.Consumables + ") AND ROUND(Quantity - QuantityTransfer, " + GlobalEnums.rndQuantity + ") > 0 " + "\r\n";
queryString = queryString + " SELECT @WarehouseIDList = STUFF((SELECT DISTINCT ',' + CAST(WarehouseID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT DISTINCT ',' + CAST(CommodityID as varchar) FROM @TransferOrderDetails FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " IF NOT @CommodityIDList IS NULL " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SET @EntryDate = GETDATE() " + "\r\n"; //GET INVENTORY UP TO DATE
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@WarehouseJournalTable", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0") + "\r\n";
queryString = queryString + " INSERT INTO @CommoditiesAvailable (WarehouseID, CommodityID, QuantityAvailable) " + "\r\n";
queryString = queryString + " SELECT WarehouseID, CommodityID, SUM(QuantityEndREC) AS QuantityAvailable " + "\r\n";
queryString = queryString + " FROM @WarehouseJournalTable " + "\r\n";
queryString = queryString + " GROUP BY WarehouseID, CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " SELECT TransferOrderDetails.TransferOrderDetailID, NULL AS SupplierID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, " + "\r\n";
queryString = queryString + " ROUND(TransferOrderDetails.Quantity - TransferOrderDetails.QuantityTransfer, " + GlobalEnums.rndQuantity + ") AS QuantityOrderPending, ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, TransferOrderDetails.Remarks, CAST(IIF(CommoditiesAvailable.QuantityAvailable > 0, 1, 0) AS bit) AS IsSelected " + "\r\n";
queryString = queryString + " FROM @TransferOrderDetails TransferOrderDetails INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON TransferOrderDetails.WarehouseID = Warehouses.WarehouseID AND Warehouses.LocationID = @LocationID INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON TransferOrderDetails.CommodityID = Commodities.CommodityID LEFT JOIN" + "\r\n";
queryString = queryString + " @CommoditiesAvailable CommoditiesAvailable ON TransferOrderDetails.WarehouseID = CommoditiesAvailable.WarehouseID AND TransferOrderDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure("GetPendingPartTransferOrders", queryString);
}
