private void GetSalesOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @SalesOrderID 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 @CustomerID int DECLARE @WarehouseIDList varchar(555) DECLARE @CommodityIDList varchar(3999) DECLARE @WarehouseClassList varchar(555) " + "\r\n";
queryString = queryString + " SELECT @EntryDate = EntryDate, @LocationID = LocationID, @CustomerID = CustomerID FROM SalesOrders WHERE SalesOrderID = @SalesOrderID " + "\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 SalesOrderDetails WHERE SalesOrderID = @SalesOrderID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @CommodityIDList = STUFF((SELECT ',' + CAST(CommodityID AS varchar) FROM SalesOrderDetails WHERE SalesOrderID = @SalesOrderID FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " SELECT @WarehouseClassList = STUFF((SELECT ',' + CAST(WarehouseClassID AS varchar) FROM Warehouses WHERE WarehouseID IN (SELECT * FROM FNSplitUpIds(@WarehouseIDList)) FOR XML PATH('')) ,1,1,'') " + "\r\n";
queryString = queryString + " " + inventories.GET_WarehouseJournal_BUILD_SQL("@CommoditiesBalance", "@EntryDate", "@EntryDate", "@WarehouseIDList", "@CommodityIDList", "0", "0", "@WarehouseClassList", null) + "\r\n";
queryString = queryString + " SELECT SalesOrderDetails.SalesOrderDetailID, SalesOrderDetails.SalesOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, SalesOrderDetails.CommodityTypeID, Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, SalesOrderDetails.CalculatingTypeID, " + "\r\n";
queryString = queryString + " ISNULL(CommoditiesBalance.QuantityBalance, 0) AS QuantityAvailable, SalesOrderDetails.Quantity, SalesOrderDetails.ControlFreeQuantity, SalesOrderDetails.FreeQuantity, SalesOrderDetails.ListedPrice, SalesOrderDetails.DiscountPercent, SalesOrderDetails.UnitPrice, SalesOrderDetails.TradeDiscountRate, SalesOrderDetails.VATPercent, SalesOrderDetails.ListedGrossPrice, SalesOrderDetails.GrossPrice, SalesOrderDetails.ListedAmount, SalesOrderDetails.Amount, SalesOrderDetails.ListedVATAmount, SalesOrderDetails.VATAmount, SalesOrderDetails.ListedGrossAmount, SalesOrderDetails.GrossAmount, SalesOrderDetails.IsBonus, SalesOrderDetails.InActivePartial, SalesOrderDetails.InActivePartialDate, SalesOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM SalesOrderDetails INNER JOIN" + "\r\n";
queryString = queryString + " Commodities ON SalesOrderDetails.SalesOrderID = @SalesOrderID AND SalesOrderDetails.CommodityID = Commodities.CommodityID INNER JOIN" + "\r\n";
queryString = queryString + " Warehouses ON SalesOrderDetails.WarehouseID = Warehouses.WarehouseID LEFT JOIN" + "\r\n";
queryString = queryString + " VoidTypes ON SalesOrderDetails.VoidTypeID = VoidTypes.VoidTypeID LEFT JOIN" + "\r\n";
queryString = queryString + " @CommoditiesBalance CommoditiesBalance ON SalesOrderDetails.WarehouseID = CommoditiesBalance.WarehouseID AND SalesOrderDetails.CommodityID = CommoditiesBalance.CommodityID " + "\r\n"; //SUM(QuantityBeginQuantityEndREC)
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetSalesOrderViewDetails", queryString);
}
private void GetProductionOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @ProductionOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT ProductionOrderDetails.ProductionOrderDetailID, ProductionOrderDetails.ProductionOrderID, ProductionOrderDetails.PlannedOrderID, ProductionOrderDetails.FirmOrderID, FirmOrders.Reference AS FirmOrderReference, FirmOrders.Code AS FirmOrderCode, FirmOrders.EntryDate AS FirmOrderEntryDate, FirmOrders.DeliveryDate AS FirmOrderDeliveryDate, " + "\r\n";
queryString = queryString + " ProductionOrderDetails.CustomerID, Customers.Code AS CustomerCode, Customers.Name AS CustomerName, ProductionOrderDetails.BomID, Boms.Code AS BomCode, " + "\r\n";
queryString = queryString + " FirmOrders.Specs, FirmOrders.Specification, VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, " + "\r\n";
queryString = queryString + " ROUND(FirmOrderDetails.QuantityRemains, " + (int)GlobalEnums.rndQuantity + ") AS QuantityRemains, " + "\r\n";
queryString = queryString + " ProductionOrderDetails.InActivePartial, ProductionOrderDetails.InActivePartialDate, ProductionOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM ProductionOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Customers ON ProductionOrderDetails.ProductionOrderID = @ProductionOrderID AND ProductionOrderDetails.CustomerID = Customers.CustomerID " + "\r\n";
queryString = queryString + " INNER JOIN (SELECT FirmOrderID, SUM(IIF(Approved = 1 AND InActive = 0 AND InActivePartial = 0, Quantity - QuantitySemifinished, 0)) AS QuantityRemains FROM FirmOrderDetails WHERE FirmOrderID IN (SELECT FirmOrderID FROM ProductionOrderDetails WHERE ProductionOrderID = @ProductionOrderID) GROUP BY FirmOrderID) FirmOrderDetails ON ProductionOrderDetails.FirmOrderID = FirmOrderDetails.FirmOrderID " + "\r\n";
queryString = queryString + " INNER JOIN FirmOrders ON FirmOrderDetails.FirmOrderID = FirmOrders.FirmOrderID " + "\r\n";
queryString = queryString + " INNER JOIN Boms ON ProductionOrderDetails.BomID = Boms.BomID " + "\r\n";
queryString = queryString + " LEFT JOIN VoidTypes ON ProductionOrderDetails.VoidTypeID = VoidTypes.VoidTypeID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetProductionOrderViewDetails", queryString);
}
private void GetPlannedOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @PlannedOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT PlannedOrderDetails.PlannedOrderDetailID, PlannedOrderDetails.PlannedOrderID, " + "\r\n";
queryString = queryString + " Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, PlannedOrderDetails.CommodityTypeID, PlannedOrderDetails.PiecePerPack, " + "\r\n";
queryString = queryString + " PlannedOrderDetails.CombineIndex, PlannedOrderDetails.MoldID, Molds.Code AS MoldCode, PlannedOrderDetails.MoldQuantity, PlannedOrderDetails.BomID, Boms.Code AS BomCode, Boms.Name AS BomName, PlannedOrderDetails.BlockUnit, PlannedOrderDetails.BlockQuantity, " + "\r\n";
queryString = queryString + " VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, " + "\r\n";
queryString = queryString + " PlannedOrderDetails.QuantityRequested, PlannedOrderDetails.QuantityOnhand, PlannedOrderDetails.Quantity, PlannedOrderDetails.InActivePartial, PlannedOrderDetails.InActivePartialDate, PlannedOrderDetails.Specs, PlannedOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM PlannedOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON PlannedOrderDetails.PlannedOrderID = @PlannedOrderID AND PlannedOrderDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN Molds ON PlannedOrderDetails.MoldID = Molds.MoldID " + "\r\n";
queryString = queryString + " INNER JOIN Boms ON PlannedOrderDetails.BomID = Boms.BomID " + "\r\n";
queryString = queryString + " LEFT JOIN VoidTypes ON PlannedOrderDetails.VoidTypeID = VoidTypes.VoidTypeID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetPlannedOrderViewDetails", queryString);
}
private void GetPlannedOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @PlannedOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT PlannedOrderDetails.PlannedOrderDetailID, PlannedOrderDetails.PlannedOrderID, " + "\r\n";
queryString = queryString + " Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, PlannedOrderDetails.CommodityTypeID, " + "\r\n";
queryString = queryString + " PlannedOrderDetails.MoldID, Molds.Code AS MoldCode, PlannedOrderDetails.CommodityMaterialID, CommodityMaterials.Code AS CommodityMaterialCode, CommodityMaterials.Name AS CommodityMaterialName, " + "\r\n";
queryString = queryString + " VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, " + "\r\n";
queryString = queryString + " PlannedOrderDetails.Quantity, PlannedOrderDetails.InActivePartial, PlannedOrderDetails.InActivePartialDate, PlannedOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM PlannedOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON PlannedOrderDetails.PlannedOrderID = @PlannedOrderID AND PlannedOrderDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN Molds ON PlannedOrderDetails.MoldID = Molds.MoldID " + "\r\n";
queryString = queryString + " INNER JOIN CommodityMaterials ON PlannedOrderDetails.CommodityMaterialID = CommodityMaterials.CommodityMaterialID " + "\r\n";
queryString = queryString + " LEFT JOIN VoidTypes ON PlannedOrderDetails.VoidTypeID = VoidTypes.VoidTypeID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetPlannedOrderViewDetails", queryString);
}
private void GetProductionOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @ProductionOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT ProductionOrderDetails.ProductionOrderDetailID, ProductionOrderDetails.ProductionOrderID, ProductionOrderDetails.PlannedOrderID, ProductionOrderDetails.PlannedOrderDetailID, PlannedOrders.Reference AS PlannedOrderReference, PlannedOrders.Code AS PlannedOrderCode, PlannedOrders.EntryDate AS PlannedOrderEntryDate, " + "\r\n";
queryString = queryString + " Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, ProductionOrderDetails.CommodityTypeID, " + "\r\n";
queryString = queryString + " ProductionOrderDetails.CustomerID, Customers.Code AS CustomerCode, Customers.Name AS CustomerName, ProductionOrderDetails.ProductionLineID, ProductionLines.Code AS ProductionLineCode, ProductionOrderDetails.MoldID, Molds.Code AS MoldCode, ProductionOrderDetails.CommodityMaterialID, CommodityMaterials.Code AS CommodityMaterialCode, " + "\r\n";
queryString = queryString + " VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, " + "\r\n";
queryString = queryString + " ROUND(CASE WHEN PlannedOrderDetails.Approved = 1 AND PlannedOrderDetails.InActive = 0 AND PlannedOrderDetails.InActivePartial = 0 THEN PlannedOrderDetails.Quantity - PlannedOrderDetails.QuantitySemifinished ELSE 0 END, " + (int)GlobalEnums.rndQuantity + ") AS QuantityRemains, " + "\r\n";
queryString = queryString + " ProductionOrderDetails.InActivePartial, ProductionOrderDetails.InActivePartialDate, ProductionOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM ProductionOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON ProductionOrderDetails.ProductionOrderID = @ProductionOrderID AND ProductionOrderDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN PlannedOrderDetails ON ProductionOrderDetails.PlannedOrderDetailID = PlannedOrderDetails.PlannedOrderDetailID " + "\r\n";
queryString = queryString + " INNER JOIN PlannedOrders ON PlannedOrderDetails.PlannedOrderID = PlannedOrders.PlannedOrderID " + "\r\n";
queryString = queryString + " INNER JOIN Customers ON ProductionOrderDetails.CustomerID = Customers.CustomerID " + "\r\n";
queryString = queryString + " INNER JOIN ProductionLines ON ProductionOrderDetails.ProductionLineID = ProductionLines.ProductionLineID " + "\r\n";
queryString = queryString + " INNER JOIN Molds ON ProductionOrderDetails.MoldID = Molds.MoldID " + "\r\n";
queryString = queryString + " INNER JOIN CommodityMaterials ON ProductionOrderDetails.CommodityMaterialID = CommodityMaterials.CommodityMaterialID " + "\r\n";
queryString = queryString + " LEFT JOIN VoidTypes ON ProductionOrderDetails.VoidTypeID = VoidTypes.VoidTypeID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetProductionOrderViewDetails", queryString);
}
private void GetPurchaseOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.totalSmartPortalEntities);
queryString = " @PurchaseOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT PurchaseOrderDetails.PurchaseOrderDetailID, PurchaseOrderDetails.PurchaseOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, PurchaseOrderDetails.CommodityTypeID, VoidTypes.VoidTypeID, VoidTypes.Code AS VoidTypeCode, VoidTypes.Name AS VoidTypeName, VoidTypes.VoidClassID, " + "\r\n";
queryString = queryString + " PurchaseOrderDetails.Quantity, PurchaseOrderDetails.InActivePartial, PurchaseOrderDetails.InActivePartialDate, PurchaseOrderDetails.Remarks, PurchaseOrderDetails.LabCode, PurchaseOrderDetails.ProductionDate, PurchaseOrderDetails.ExpiryDate " + "\r\n";
queryString = queryString + " FROM PurchaseOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON PurchaseOrderDetails.PurchaseOrderID = @PurchaseOrderID AND PurchaseOrderDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " LEFT JOIN VoidTypes ON PurchaseOrderDetails.VoidTypeID = VoidTypes.VoidTypeID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalSmartPortalEntities.CreateStoredProcedure("GetPurchaseOrderViewDetails", queryString);
}
private void GetPurchaseOrderViewDetails()
{
string queryString;
SqlProgrammability.Inventories.Inventories inventories = new Inventories.Inventories(this.projectManagerEntities);
queryString = " @PurchaseOrderID Int " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT PurchaseOrderDetails.PurchaseOrderDetailID, PurchaseOrderDetails.PurchaseOrderID, Commodities.CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, PurchaseOrderDetails.CommodityTypeID, PurchaseOrderDetails.CalculatingTypeID, PurchaseOrderDetails.RowCategory, PurchaseOrderDetails.RowDescription, PurchaseOrderDetails.RowSpecification, PurchaseOrderDetails.RowUnit, PurchaseOrderDetails.RequestNo, PurchaseOrderDetails.JobType, PurchaseOrderDetails.JobNo, PurchaseOrderDetails.CodeSection, PurchaseOrderDetails.CodeItem, " + "\r\n";
queryString = queryString + " PurchaseOrderDetails.Quantity, PurchaseOrderDetails.UnitPrice, PurchaseOrderDetails.VATPercent, PurchaseOrderDetails.GrossPrice, PurchaseOrderDetails.Amount, PurchaseOrderDetails.VATAmount, PurchaseOrderDetails.GrossAmount, PurchaseOrderDetails.IsBonus, PurchaseOrderDetails.InActivePartial, PurchaseOrderDetails.InActivePartialDate, PurchaseOrderDetails.Remarks " + "\r\n";
queryString = queryString + " FROM PurchaseOrderDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON PurchaseOrderDetails.PurchaseOrderID = @PurchaseOrderID AND PurchaseOrderDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " ORDER BY PurchaseOrderDetails.RowCategory, PurchaseOrderDetails.PurchaseOrderDetailID " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.projectManagerEntities.CreateStoredProcedure("GetPurchaseOrderViewDetails", queryString);
}
