private void GetDeliveryAdviceViewDetails()
{
string queryString;
queryString = " @DeliveryAdviceID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @DeliveryAdviceDetails TABLE (DeliveryAdviceID int NOT NULL, DeliveryAdviceDetailID int NOT NULL, SalesOrderID int NULL, SalesOrderDetailID int NULL, EntryDate datetime NOT NULL, LocationID int NOT NULL, CommodityID int NOT NULL, BatchID int NULL, Quantity decimal(18, 2) NOT NULL, LineVolume decimal(18, 2) NOT NULL, QuantityIssue decimal(18, 2) NOT NULL, LineVolumeIssue decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @DeliveryAdviceDetails (DeliveryAdviceID, DeliveryAdviceDetailID, SalesOrderID, SalesOrderDetailID, EntryDate, LocationID, CommodityID, BatchID, Quantity, LineVolume, QuantityIssue, LineVolumeIssue, Remarks) SELECT DeliveryAdviceID, DeliveryAdviceDetailID, SalesOrderID, SalesOrderDetailID, EntryDate, LocationID, CommodityID, BatchID, Quantity, LineVolume, QuantityIssue, LineVolumeIssue, Remarks FROM DeliveryAdviceDetails WHERE DeliveryAdviceID = @DeliveryAdviceID " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@DeliveryAdviceDetails", true, false, true, true, true, true, false) + "\r\n";
queryString = queryString + " SELECT DeliveryAdviceDetails.DeliveryAdviceDetailID, DeliveryAdviceDetails.DeliveryAdviceID, DeliveryAdviceDetails.SalesOrderID, DeliveryAdviceDetails.SalesOrderDetailID, SalesOrderDetails.Reference AS SalesOrderReference, SalesOrderDetails.EntryDate AS SalesOrderEntryDate, SalesOrderDetails.VoucherCode, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.Unit, Commodities.PackageSize, Commodities.Volume, Commodities.PackageVolume, DeliveryAdviceDetails.BatchID, Batches.Code AS BatchCode, " + "\r\n";
queryString = queryString + " ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, ISNULL(CommoditiesAvailable.LineVolumeAvailable, 0) AS LineVolumeAvailable, ISNULL(CommoditiesAvailableByBatches.QuantityAvailable, 0) AS QuantityBatchAvailable, ISNULL(CommoditiesAvailableByBatches.LineVolumeAvailable, 0) AS LineVolumeBatchAvailable, ROUND(ISNULL(SalesOrderDetails.Quantity - SalesOrderDetails.QuantityAdvice, 0) + DeliveryAdviceDetails.Quantity, " + (int)GlobalEnums.rndQuantity + ") AS QuantityRemains, ROUND(ISNULL(SalesOrderDetails.LineVolume - SalesOrderDetails.LineVolumeAdvice, 0) + DeliveryAdviceDetails.LineVolume, " + (int)GlobalEnums.rndVolume + ") AS LineVolumeRemains, DeliveryAdviceDetails.Quantity, DeliveryAdviceDetails.LineVolume, DeliveryAdviceDetails.QuantityIssue, DeliveryAdviceDetails.LineVolumeIssue, DeliveryAdviceDetails.Remarks " + "\r\n";
queryString = queryString + " FROM @DeliveryAdviceDetails DeliveryAdviceDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON DeliveryAdviceDetails.CommodityID = Commodities.CommodityID" + "\r\n";
queryString = queryString + " LEFT JOIN Batches ON DeliveryAdviceDetails.BatchID = Batches.BatchID " + "\r\n";
queryString = queryString + " LEFT JOIN SalesOrderDetails ON DeliveryAdviceDetails.SalesOrderDetailID = SalesOrderDetails.SalesOrderDetailID " + "\r\n";
queryString = queryString + " LEFT JOIN (SELECT CommodityID, SUM(QuantityAvailable) AS QuantityAvailable, SUM(LineVolumeAvailable) AS LineVolumeAvailable FROM @CommoditiesAvailable GROUP BY CommodityID) CommoditiesAvailable ON DeliveryAdviceDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " LEFT JOIN (SELECT CommodityID, BatchID, SUM(QuantityAvailable) AS QuantityAvailable, SUM(LineVolumeAvailable) AS LineVolumeAvailable FROM @CommoditiesAvailableByBatches GROUP BY CommodityID, BatchID) CommoditiesAvailableByBatches ON DeliveryAdviceDetails.BatchID = CommoditiesAvailableByBatches.BatchID AND DeliveryAdviceDetails.CommodityID = CommoditiesAvailableByBatches.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartCodingEntities.CreateStoredProcedure("GetDeliveryAdviceViewDetails", queryString);
}
private void GetTransferOrderViewDetails()
{
string queryString;
queryString = " @TransferOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @TransferOrderDetails TABLE (TransferOrderID int NOT NULL, TransferOrderDetailID int NOT NULL, EntryDate datetime NOT NULL, LocationID int NOT NULL, WarehouseID int NOT NULL, CommodityID int NOT NULL, BatchID int NULL, Quantity decimal(18, 2) NOT NULL, LineVolume decimal(18, 2) NOT NULL, QuantityIssue decimal(18, 2) NOT NULL, LineVolumeIssue decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " INSERT INTO @TransferOrderDetails (TransferOrderID, TransferOrderDetailID, EntryDate, LocationID, WarehouseID, CommodityID, BatchID, Quantity, LineVolume, QuantityIssue, LineVolumeIssue, Remarks) SELECT TransferOrderID, TransferOrderDetailID, EntryDate, LocationID, WarehouseID, CommodityID, BatchID, Quantity, LineVolume, QuantityIssue, LineVolumeIssue, Remarks FROM TransferOrderDetails WHERE TransferOrderID = @TransferOrderID " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@TransferOrderDetails", false, true, true, true, true, true, false) + "\r\n";
queryString = queryString + " SELECT TransferOrderDetails.TransferOrderDetailID, TransferOrderDetails.TransferOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.Unit, Commodities.PackageSize, Commodities.Volume, Commodities.PackageVolume, TransferOrderDetails.BatchID, Batches.Code AS BatchCode, " + "\r\n";
queryString = queryString + " ISNULL(CommoditiesAvailable.QuantityAvailable, 0) AS QuantityAvailable, ISNULL(CommoditiesAvailable.LineVolumeAvailable, 0) AS LineVolumeAvailable, ISNULL(CommoditiesAvailableByBatches.QuantityAvailable, 0) AS QuantityBatchAvailable, ISNULL(CommoditiesAvailableByBatches.LineVolumeAvailable, 0) AS LineVolumeBatchAvailable, TransferOrderDetails.Quantity, TransferOrderDetails.LineVolume, TransferOrderDetails.QuantityIssue, TransferOrderDetails.LineVolumeIssue, TransferOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM @TransferOrderDetails TransferOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON TransferOrderDetails.CommodityID = Commodities.CommodityID" + "\r\n";
queryString = queryString + " LEFT JOIN Batches ON TransferOrderDetails.BatchID = Batches.BatchID " + "\r\n";
queryString = queryString + " LEFT JOIN (SELECT CommodityID, SUM(QuantityAvailable) AS QuantityAvailable, SUM(LineVolumeAvailable) AS LineVolumeAvailable FROM @CommoditiesAvailable GROUP BY CommodityID) CommoditiesAvailable ON TransferOrderDetails.CommodityID = CommoditiesAvailable.CommodityID " + "\r\n";
queryString = queryString + " LEFT JOIN (SELECT CommodityID, BatchID, SUM(QuantityAvailable) AS QuantityAvailable, SUM(LineVolumeAvailable) AS LineVolumeAvailable FROM @CommoditiesAvailableByBatches GROUP BY CommodityID, BatchID) CommoditiesAvailableByBatches ON TransferOrderDetails.BatchID = CommoditiesAvailableByBatches.BatchID AND TransferOrderDetails.CommodityID = CommoditiesAvailableByBatches.CommodityID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartCodingEntities.CreateStoredProcedure("GetTransferOrderViewDetails", queryString);
}
private void GetPendingSalesOrderDetails()
{
string queryString;
queryString = " @LocationID Int, @DeliveryAdviceID Int, @SalesOrderID Int, @CustomerID Int, @ReceiverID Int, @SalesOrderDetailIDs varchar(3999), @IsReadonly bit " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @SalesOrderDetails TABLE (IsNewOrEdit bit NOT NULL, SalesOrderID int NOT NULL, SalesOrderDetailID int NOT NULL, EntryDate datetime NOT NULL, Reference nvarchar(10) NULL, VoucherCode nvarchar(60) NULL, LocationID int NOT NULL, CommodityID int NOT NULL, QuantityRemains decimal(18, 2) NOT NULL, LineVolumeRemains decimal(18, 2) NOT NULL, Remarks nvarchar(100) NULL) " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SalesOrderDetails", true, false, true, false, false, false, false) + "\r\n";
queryString = queryString + " IF (@SalesOrderID <> 0) " + "\r\n";
queryString = queryString + " " + this.BuildSQLSalesOrder(true) + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " " + this.BuildSQLSalesOrder(false) + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartCodingEntities.CreateStoredProcedure("GetPendingSalesOrderDetails", queryString);
}
private void GetBatchAvailables()
{
string queryString;
queryString = " @LocationID Int, @DeliveryAdviceID Int, @TransferOrderID Int, @CommodityID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " DECLARE @SearchTable TABLE (LocationID int NOT NULL, CommodityID int NOT NULL) " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SearchTable", false, false, true, false, false, true, false) + "\r\n";
queryString = queryString + " INSERT INTO @SearchTable (LocationID, CommodityID) VALUES(@LocationID, @CommodityID) " + "\r\n";
queryString = queryString + " IF (@DeliveryAdviceID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SearchTable", true, false, false, true, true, true, true) + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " IF (@TransferOrderID > 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SearchTable", false, true, false, true, true, true, true) + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SearchTable", false, false, false, true, false, true, true) + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " SELECT CommoditiesAvailableByBatches.BatchID, Batches.EntryDate, Batches.Code, CommoditiesAvailableByBatches.QuantityAvailable, CommoditiesAvailableByBatches.LineVolumeAvailable " + "\r\n";
queryString = queryString + " FROM (SELECT BatchID, SUM(QuantityAvailable) AS QuantityAvailable, SUM(LineVolumeAvailable) AS LineVolumeAvailable FROM @CommoditiesAvailableByBatches GROUP BY BatchID) CommoditiesAvailableByBatches " + "\r\n";
queryString = queryString + " INNER JOIN Batches ON CommoditiesAvailableByBatches.BatchID = Batches.BatchID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartCodingEntities.CreateStoredProcedure("GetBatchAvailables", queryString);
}
private string BuildSQLPendingAvailable(bool isSalesOrderID, bool isSalesOrderDetailIDs, bool sqlNew, bool sqlEdit)
{
string queryString = "";
if (sqlNew)
{
queryString = queryString + " INSERT INTO @SalesOrderDetails (IsNewOrEdit, SalesOrderID, SalesOrderDetailID, EntryDate, Reference, VoucherCode, LocationID, CommodityID, QuantityRemains, LineVolumeRemains, Remarks) " + "\r\n";
queryString = queryString + " SELECT 1 AS IsNewOrEdit, SalesOrderID, SalesOrderDetailID, EntryDate, Reference, VoucherCode, LocationID, CommodityID, ROUND(Quantity - QuantityAdvice, " + (int)GlobalEnums.rndQuantity + ") AS QuantityRemains, ROUND(LineVolume - LineVolumeAdvice, " + (int)GlobalEnums.rndVolume + ") AS LineVolumeRemains, Remarks " + "\r\n";
queryString = queryString + " FROM SalesOrderDetails " + "\r\n";
queryString = queryString + " WHERE " + (isSalesOrderID ? " SalesOrderID = @SalesOrderID " : "LocationID = @LocationID AND CustomerID = @CustomerID AND ReceiverID = @ReceiverID ") + " AND Approved = 1 AND ROUND(Quantity - QuantityAdvice, " + (int)GlobalEnums.rndQuantity + ") > 0 " + (isSalesOrderDetailIDs ? " AND SalesOrderDetailID NOT IN (SELECT Id FROM dbo.SplitToIntList (@SalesOrderDetailIDs))" : "") + (sqlNew && sqlEdit ? " AND SalesOrderDetailID NOT IN (SELECT SalesOrderDetailID FROM DeliveryAdviceDetails WHERE DeliveryAdviceID = @DeliveryAdviceID) " : "") + "\r\n";
}
if (sqlEdit)
{
queryString = queryString + " INSERT INTO @SalesOrderDetails (IsNewOrEdit, SalesOrderID, SalesOrderDetailID, EntryDate, Reference, VoucherCode, LocationID, CommodityID, QuantityRemains, LineVolumeRemains, Remarks) " + "\r\n";
queryString = queryString + " SELECT 0 AS IsNewOrEdit, SalesOrderDetails.SalesOrderID, SalesOrderDetails.SalesOrderDetailID, SalesOrderDetails.EntryDate, SalesOrderDetails.Reference, SalesOrderDetails.VoucherCode, SalesOrderDetails.LocationID, SalesOrderDetails.CommodityID, ROUND(SalesOrderDetails.Quantity - SalesOrderDetails.QuantityAdvice + DeliveryAdviceDetails.Quantity, " + (int)GlobalEnums.rndQuantity + ") AS QuantityRemains, ROUND(SalesOrderDetails.LineVolume - SalesOrderDetails.LineVolumeAdvice + DeliveryAdviceDetails.LineVolume, " + (int)GlobalEnums.rndVolume + ") AS LineVolumeRemains, SalesOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM SalesOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN DeliveryAdviceDetails ON DeliveryAdviceDetails.DeliveryAdviceID = @DeliveryAdviceID AND SalesOrderDetails.SalesOrderDetailID = DeliveryAdviceDetails.SalesOrderDetailID" + (isSalesOrderDetailIDs ? " AND SalesOrderDetails.SalesOrderDetailID NOT IN (SELECT Id FROM dbo.SplitToIntList (@SalesOrderDetailIDs))" : "") + "\r\n";
}
queryString = queryString + " " + GenerateSQLCommoditiesAvailable.BuildSQL("@SalesOrderDetails", true, false, false, true, sqlEdit, false, false) + "\r\n";
return queryString;
}