Using this a basis
viewtopic.php?f=26&t=825
I am trying to add lines to a stocktake form based on an Excel sheet. The above was for 7.0.157 and uses the FindStock function from the stocktake business logic.
Back in that version, the FindStock took arguments for PartNo, BinLocation, categories, GetZeroSOH and MaxRows and worked fine according to the OP.
In version 7.2 FindStock only takes arguments for GetZeroSOH and MaxRows & picks up the other arguments from the ranges grid. I want to ignore the ranges specified in the grid & just use the values I specify.
I am having 2 issues:
1. How can I just pass in a single PartNo into FindStock & have the StocktakeLines populate with the relevant data - and also critically set the QuantityFound with a value? The value is coming from Excel.
2. If I write my own FindStock function which is pretty much a copy of yours, you are setting inventoryID, PartNo, UseSerialNo, UseExpiryDate i.e
- Code: Select all
current = this.Manager.CollectionItemFactory.CreateCollectionItem<StockTakeLine>();
current.BinLocation = Conversions.ToString(_database.Sanitise(sqlDataReader, "BinLocationDesc"));
current.InventoryID = Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_InventoryID"));
current.PartNo = Conversions.ToString(_database.Sanitise(sqlDataReader, "PartNo"));
current.Description = Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_Description"));
current.UseSerialNo = Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseSerialNo"));
current.QuantityDecimalPlaces = Conversions.ToInteger(_database.Sanitise(sqlDataReader, "DecimalPlaces"));
current.UseExpiryDate = Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseExpiryDate"));
current.DefaultBinLocation = Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_BinLocation_Description"));
current.LastCost = Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "IN_Main_LCost"));
current.UseStandardCost = Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseStandardCost"));
current.StandardCost = Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "StandardCost"));
this.StockTakeLines.Add(current);
current.FindSOH();
The thing is, all those settings are read only so how can I set them? And the last line calls FindSOH which doesn't exist in stocktakeLine?
If I get the line added successfully, I then need to populate the QuantityFound.
Any help would be appreciated.
This is my full FindStock function
- Code: Select all
public void MyFindStock(bool GetZeroSOH, int MaxRows, string PartNo, JiwaFinancials.Jiwa.JiwaStockTake.StockTake stocktake)
{
JiwaFinancials.Jiwa.JiwaStockTake.StockTakeLine current;
IEnumerator enumerator = null;
string str = "";
SqlDataReader sqlDataReader = null;
SqlParameter sqlParameter = null;
bool reading = stocktakeForm.StockTake.StockTakeLines.Reading;
JiwaFinancials.Jiwa.JiwaODBC.database database = stocktake.Manager.Database;
try
{
if (stocktake.Mode != JiwaFinancials.Jiwa.JiwaStockTake.StockTake.StockTakeModes.PartNo)
{
str = string.Concat("SELECT DISTINCT TOP ", Microsoft.VisualBasic.CompilerServices.Conversions.ToString(MaxRows), "IN_SOH.BinLocationDesc, IN_Main.InventoryID [IN_Main_InventoryID], IN_Main.PartNo, ");
str = string.Concat(str, "IN_Main.Description [IN_Main_Description], IN_Main.UseSerialNo, IN_Main.DecimalPlaces, IN_Main.UseExpiryDate, IN_BinLocationLookup.Description ");
str = string.Concat(str, "[IN_BinLocation_Description], IN_Main.LCost [IN_Main_LCost], IN_Main.UseStandardCost, IN_Main.StandardCost FROM IN_Main LEFT JOIN IN_SOH ON ");
str = string.Concat(str, "IN_Main.InventoryID = IN_SOH.InventoryID LEFT JOIN IN_BinLocation ON IN_Main.InventoryID = IN_BinLocation.IN_MainID LEFT JOIN IN_BinLocationLookup");
str = string.Concat(str, "ON IN_BinLocation.IN_BinLocationLookup_INBinLookupID = IN_BinLocationLookup.INBinLookupID JOIN IN_Category1 as Cat1 ON Cat1.Category1ID = IN_Main.Catagory1ID JOIN IN_Classification ON IN_Classification.InventoryClassificationID = IN_Main.ClassificationID JOIN IN_Category2 as Cat2 ON Cat2.Category2ID = IN_Main.Catagory2ID JOIN IN_Category3 as Cat3 ON Cat3.Category3ID = IN_Main.Catagory3ID WHERE PhysicalItem <> 0 AND IN_Main.PartNo >= @FromPartNo AND IN_Main.PartNo <= @ToPartNo AND Cat1.Description >= @FromCat1 AND Cat1.Description <= @ToCat1 AND Cat2.Description >= @FromCat2 AND Cat2.Description <= @ToCat2 AND Cat3.Description >= @FromCat3 AND Cat3.Description <= @ToCat3 AND IN_Classification.Description >= @FromClassification AND IN_Classification.Description <= @ToClassification ");
str = string.Concat(str, " AND ( IN_SOH.BinLocationDesc >= @FromBinLocation AND IN_SOH.BinLocationDesc <= @ToBinLocation OR ( (IN_SOH.BinLocationDesc = '' OR IN_SOH.BinLocationDesc IS NULL) ");
str = string.Concat(str, "AND (IN_BinLocationLookup.Description >= @FromBinLocation AND IN_BinLocationLookup.Description <= @ToBinLocation) ) ) ");
str = string.Concat(str, "AND (IN_BinLocation.IN_LogicalID = @LogicalID OR IN_BinLocation.IN_LogicalID IS NULL) AND (IN_SOH.IN_LogicalID = @LogicalID OR IN_SOH.IN_LogicalID IS NULL) AND IN_Main.BOMObject < 2 AND IN_Main.Status <> 2");
if (!GetZeroSOH)
{
str = string.Concat(str, " AND IN_SOH.QuantityLeft <> 0");
str = string.Concat(str, " AND IN_SOH.IN_LogicalID = @LogicalID ");
}
str = string.Concat(str, " ORDER BY IN_SOH.BinLocationDesc, IN_Main.PartNo");
}
else
{
str = string.Concat("SELECT DISTINCT TOP ", Microsoft.VisualBasic.CompilerServices.Conversions.ToString(MaxRows), " '' [BinLocationDesc], IN_Main.InventoryID [IN_Main_InventoryID], IN_Main.PartNo, IN_Main.Description [IN_Main_Description], IN_Main.UseSerialNo, IN_Main.DecimalPlaces, IN_Main.UseExpiryDate, IN_BinLocationLookup.Description [IN_BinLocation_Description], IN_Main.LCost [IN_Main_LCost], IN_Main.UseStandardCost, IN_Main.StandardCost FROM IN_Main LEFT JOIN IN_BinLocation ON (IN_Main.InventoryID = IN_BinLocation.IN_MainID) LEFT JOIN IN_BinLocationLookup ON (IN_BinLocation.IN_BinLocationLookup_INBinLookupID = IN_BinLocationLookup.INBinLookupID) LEFT JOIN IN_Logical ON (IN_Logical.IN_LogicalID = IN_BinLocation.IN_LogicalID) JOIN IN_Category1 as Cat1 ON (Cat1.Category1ID = IN_Main.Catagory1ID) JOIN IN_Classification ON (IN_Classification.InventoryClassificationID = IN_Main.ClassificationID) JOIN IN_Category2 as Cat2 ON (Cat2.Category2ID = IN_Main.Catagory2ID) JOIN IN_Category3 as Cat3 ON (Cat3.Category3ID = IN_Main.Catagory3ID) LEFT JOIN IN_SOH ON (IN_SOH.InventoryID = IN_Main.InventoryID) WHERE PhysicalItem <> 0 AND IN_Main.PartNo >= @FromPartNo AND IN_Main.PartNo <= @ToPartNo AND Cat1.Description >= @FromCat1 AND Cat1.Description <= @ToCat1 AND Cat2.Description >= @FromCat2 AND Cat2.Description <= @ToCat2 AND Cat3.Description >= @FromCat3 AND Cat3.Description <= @ToCat3 AND IN_Classification.Description >= @FromClassification AND IN_Classification.Description <= @ToClassification AND (IN_BinLocation.IN_LogicalID = @LogicalID OR ISNULL(IN_BinLocation.IN_LogicalID,'ISNULL') = 'ISNULL') AND IN_Main.BOMObject < 2 AND IN_Main.Status <> 2");
if (!GetZeroSOH)
{
str = string.Concat(str, " AND IN_SOH.QuantityLeft <> 0");
str = string.Concat(str, " AND IN_SOH.IN_LogicalID = @LogicalID ");
}
str = string.Concat(str, " ORDER BY IN_Main.PartNo ");
}
using (SqlCommand sqlCommand = new SqlCommand(str, stocktake.Manager.Database.SQLConnection, stocktake.Manager.Database.SQLTransaction))
{
if (stocktake.Mode == JiwaFinancials.Jiwa.JiwaStockTake.StockTake.StockTakeModes.BinLocation)
{
sqlParameter = new SqlParameter("@FromBinLocation", SqlDbType.VarChar)
{
Value = stocktake.Ranges.BinLocation.From.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToBinLocation", SqlDbType.VarChar)
{
Value = stocktake.Ranges.BinLocation.To.Description
};
sqlCommand.Parameters.Add(sqlParameter);
}
sqlParameter = new SqlParameter("@FromPartNo", SqlDbType.VarChar)
{
Value = PartNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToPartNo", SqlDbType.VarChar)
{
Value = PartNo
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@FromCat1", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category1.From.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToCat1", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category1.To.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@FromCat2", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category2.From.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToCat2", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category2.To.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@FromCat3", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category3.From.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToCat3", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Category3.To.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@FromClassification", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Classification.From.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ToClassification", SqlDbType.VarChar)
{
Value = stocktake.Ranges.Classification.To.Description
};
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@LogicalID", SqlDbType.VarChar)
{
Value = stocktake.LogicalWarehouseResidingIn.IN_LogicalID
};
sqlCommand.Parameters.Add(sqlParameter);
System.ComponentModel.CancelEventArgs cancelEventArg = new System.ComponentModel.CancelEventArgs();
stocktake.OnFindStockStart(sqlCommand, cancelEventArg);
if (!cancelEventArg.Cancel)
{
sqlDataReader = database.ExecuteReader(sqlCommand);
JiwaFinancials.Jiwa.JiwaODBC.database _database = stocktake.Manager.Database;
while (sqlDataReader.Read())
{
string str1 = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "PartNo"));
string str2 = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "BinLocationDesc"));
bool flag = false;
try
{
enumerator = stocktake.StockTakeLines.GetEnumerator();
while (enumerator.MoveNext())
{
current = (JiwaFinancials.Jiwa.JiwaStockTake.StockTakeLine)enumerator.Current;
if (Microsoft.VisualBasic.CompilerServices.Operators.CompareString(current.PartNo, str1, false) != 0)
{
continue;
}
if (stocktake.Mode != JiwaFinancials.Jiwa.JiwaStockTake.StockTake.StockTakeModes.PartNo)
{
if (stocktake.Mode != JiwaFinancials.Jiwa.JiwaStockTake.StockTake.StockTakeModes.BinLocation || Microsoft.VisualBasic.CompilerServices.Operators.CompareString(current.BinLocation, str2, false) != 0)
{
continue;
}
flag = true;
}
else
{
flag = true;
}
}
}
finally
{
if (enumerator is IDisposable)
{
(enumerator as IDisposable).Dispose();
}
}
if (flag)
{
continue;
}
current = stocktake.Manager.CollectionItemFactory.CreateCollectionItem<JiwaFinancials.Jiwa.JiwaStockTake.StockTakeLine>();
current.BinLocation = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "BinLocationDesc"));
current.InventoryID = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_InventoryID"));
current.PartNo = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "PartNo"));
current.Description = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_Description"));
current.UseSerialNo = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseSerialNo"));
current.QuantityDecimalPlaces = Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(_database.Sanitise(sqlDataReader, "DecimalPlaces"));
current.UseExpiryDate = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseExpiryDate"));
current.DefaultBinLocation = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_BinLocation_Description"));
current.LastCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "IN_Main_LCost"));
current.UseStandardCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseStandardCost"));
current.StandardCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "StandardCost"));
stocktake.StockTakeLines.Add(current);
current.FindSOH();
}
_database = null;
stocktake.OnFindStockEnd(sqlCommand);
}
}
}
finally
{
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
}
database = null;
}