Loading a stocktake from Excel  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Loading a stocktake from Excel

Postby DannyC » Fri Jun 28, 2019 2:03 pm

Hi,

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;
    }
DannyC
Jiwa Kohai
Jiwa Kohai
 
Posts: 375
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 10

Re: Loading a stocktake from Excel

Postby Mike.Sheen » Fri Jun 28, 2019 4:39 pm

DannyC wrote: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.


Well, technically we pick up the values from the Ranges business logic collection, which has values poked into it by the grid.

We cleaned a few things up as part of DEV-6415 and that included changing that method FindStock to no longer accept as method arguments the ranges, but to use a ranges object collection instead.

DannyC wrote: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.

You can't use our FindStock function then, it's not designed to be extended like that.

DannyC wrote:2. If I write my own FindStock function which is pretty much a copy of yours, you are setting inventoryID, PartNo, UseSerialNo, UseExpiryDate
...
The thing is, all those settings are read only so how can I set them?


You can set the properties using reflection. This is demonstrated in this plugin provided to you recently for the Manual BO Release form - which is a very similar pattern to the stock take form in this respect.

DannyC wrote: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.

Using the same technique - reflection - you can invoke the Friend method FindSOH of the StockTakeLine class.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1674
Joined: Tue Feb 12, 2008 11:12 am
Location: North Sydney
Topics Solved: 522

Re: Loading a stocktake from Excel

Postby DannyC » Fri Jun 28, 2019 7:04 pm

You can set the properties using reflection


Way beyond my skill level! Anyway I dived in blindly and managed to get this MyFindStock function to compile. I thought that's a pretty good achievement but alas it errors on the second line dynMethod.Invoke(stocktake, null);
"Object reference not set to an instance of an object"

Code: Select all
   public void MyFindStock(bool GetZeroSOH, int MaxRows, string PartNo, JiwaFinancials.Jiwa.JiwaStockTake.StockTake stocktake)
    {
      MethodInfo dynMethod = stocktake.GetType().GetMethod("FindStock", BindingFlags.NonPublic | BindingFlags.Instance);
      dynMethod.Invoke(stocktake, null);

        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 = stocktakeForm.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"));
                  FieldInfo fieldinfo = current.GetType().GetField("InventoryID", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_InventoryID")));
                        //current.PartNo = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "PartNo"));
                  fieldinfo = current.GetType().GetField("PartNo", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "PartNo")));
                        //current.Description = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_Description"));
                  fieldinfo = current.GetType().GetField("Description", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_Description")));
                        //current.UseSerialNo = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseSerialNo"));
                  fieldinfo = current.GetType().GetField("UseSerialNo", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "UseSerialNo")));                  
                        //current.QuantityDecimalPlaces = Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(_database.Sanitise(sqlDataReader, "DecimalPlaces"));
                  fieldinfo = current.GetType().GetField("QuantityDecimalPlaces", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "DecimalPlaces")));
                        //current.UseExpiryDate = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseExpiryDate"));
                  fieldinfo = current.GetType().GetField("UseExpiryDate", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "UseExpiryDate")));
                        //current.DefaultBinLocation = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_BinLocation_Description"));
                  fieldinfo = current.GetType().GetField("DefaultBinLocation", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_BinLocation_Description")));
                        //current.LastCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "IN_Main_LCost"));
                  fieldinfo = current.GetType().GetField("LastCost", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "IN_Main_LCost")));
                        //current.UseStandardCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(_database.Sanitise(sqlDataReader, "UseStandardCost"));
                  fieldinfo = current.GetType().GetField("UseStandardCost", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "UseStandardCost")));
                        //current.StandardCost = Microsoft.VisualBasic.CompilerServices.Conversions.ToDecimal(_database.Sanitise(sqlDataReader, "StandardCost"));
                  fieldinfo = current.GetType().GetField("StandardCost", BindingFlags.Instance);
                  fieldinfo.SetValue(current,Microsoft.VisualBasic.CompilerServices.Conversions.ToString(_database.Sanitise(sqlDataReader, "StandardCost")));                  
                        stocktake.StockTakeLines.Add(current);
                        //current.FindSOH();
                  
                  dynMethod = current.GetType().GetMethod("FindSOH", BindingFlags.NonPublic);
                  dynMethod.Invoke(current, null);
                  
                  
                  
                    }
                    _database = null;
                    stocktake.OnFindStockEnd(sqlCommand);
                }
            }
        }
        finally
        {
            if (sqlDataReader != null)
            {
                sqlDataReader.Close();
            }
        }
        database = null;
    }
DannyC
Jiwa Kohai
Jiwa Kohai
 
Posts: 375
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 10

Re: Loading a stocktake from Excel

Postby SBarnes » Sat Jun 29, 2019 1:33 pm

Maybe I am missing something but if you are trying to load the stock take screen from a spreadsheet shouldn't the concentration be on the screen rather than the business object?

By this I mean shouldn't you just work on the theory that when you do the import the current loaded stock take will have the necessary rows and if it doesn't whilst processing the spreadsheet produce an error or alternatively when the import is done mimic the screen creating a new stock take, setting the values in the criteria grid from the data in the spreadsheet then press the load button and then process the spreadsheet against the lines once the load is finished.

The point I am making like most Jiwa screens is there a fairly strong interaction of events between the business object and the screen due to the hooking up of events that most screens do so you need to let the screen talk to the business object so you don't get any nasty side effects for example once the find stock happens the actual screen draws the results grid and does other things when the load button is clicked.
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 845
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 74

Re: Loading a stocktake from Excel

Postby DannyC » Sun Jun 30, 2019 3:50 pm

There wont be a currently loaded stocktake.
It will be a new stocktake, yes - but without any loaded lines.
I'm wanting to just load the lines grid from a spreadsheet, including the counted stock.
DannyC
Jiwa Kohai
Jiwa Kohai
 
Posts: 375
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 10

Re: Loading a stocktake from Excel

Postby SBarnes » Sun Jun 30, 2019 4:52 pm

Ok in short here's what you will need to do

To create a new stock take on the actual stock take form call NewRecord();
The grid with the ranges is called grdRanges so set the cells to what you need to get the correct load.
The find stock button is called FindStockUltraButton to click the button the following should do it

Code: Select all
            FindStockUltraButton.Focus();
            FindStockUltraButton.PerformClick();


Register your own click event in the form plugin as shown below which should then fire after Jiwa has loaded the grid and process your spreadsheet there


Code: Select all
stocktakeform.FindStockUltraButton.Click += new EventHandler(FindStockUltraButton_ClickAfterJiwa);

private void FindStockUltraButton_ClickAfterJiwa(object sender, EventArgs e)
{

//Loop over the spreadsheet and work off the stock take of the form that Jiwa loaded up
}
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 845
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 74

Re: Loading a stocktake from Excel  Topic is solved

Postby DannyC » Wed Jul 10, 2019 3:30 pm

Worked out the trick by setting the stocktake.Ranges values then firing the FindStock function.
Pretty easy in the end.
DannyC
Jiwa Kohai
Jiwa Kohai
 
Posts: 375
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 10


Return to Technical / Programming

Who is online

Users browsing this forum: No registered users and 3 guests