public PurchasedItemList()
            : base()
        {
            SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings;
            SqlCommand             objCmd;
            SqlDataAdapter         objAdapter;
            DataSet                objDataSet, objLoadDataSet;
            TimePhasedItem         objPurchasedItem;
            RepletionDepletionItem objRepletionDepletionItem;
            int intTemp;

            objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings();
            objSL8_WTF_DataBaseSettings.SQLConnection.Open();
            objCmd = new SqlCommand("Rpt_PurchaseRequirementsSp", objSL8_WTF_DataBaseSettings.SQLConnection);

            objCmd.CommandTimeout = 60;//via the vpn I kept getting a timeout; this fixed it.

            objCmd.CommandType = CommandType.StoredProcedure;
            //objCmd.Parameters.Add(new SqlParameter("@ItemStarting", "WIQ.3045-4 1018"));
            //objCmd.Parameters.Add(new SqlParameter("@ItemEnding", "WIQ.3045-4 1018"));
            objCmd.Parameters.Add(new SqlParameter("@ShowDepl", 1));
            objCmd.Parameters.Add(new SqlParameter("@ShowRepl", 1));
            objCmd.Parameters.Add(new SqlParameter("@TimePhaseDetail", 1));
            objCmd.Parameters.Add(new SqlParameter("@JobStatus", "FR"));//can make this "FR" to show Firm AND Released Jobs
            objCmd.Parameters.Add(new SqlParameter("@POStatus", "O"));
            objCmd.Parameters.Add(new SqlParameter("@Source", "P"));

            objAdapter = new SqlDataAdapter(objCmd);
            objDataSet = new DataSet();
            objAdapter.Fill(objDataSet);

            objCmd             = new SqlCommand("CLM_ResourceGroupScheduleSp", objSL8_WTF_DataBaseSettings.SQLConnection);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add(new SqlParameter("@StartDate", "1/1/1753"));
            objCmd.Parameters.Add(new SqlParameter("@EndDate", "12/31/9999"));
            objCmd.Parameters.Add(new SqlParameter("@AltNum", 0));

            objAdapter     = new SqlDataAdapter(objCmd);
            objLoadDataSet = new DataSet();
            objAdapter.Fill(objLoadDataSet);

            objSL8_WTF_DataBaseSettings.SQLConnection.Close();

            var objGroupedItems = objDataSet.Tables[0].AsEnumerable()
                                  .GroupBy(f => new
            {
                ItemID = f.Field <string>("item")
            })
                                  .Select(g =>
                                          new
            {
                ItemID                  = g.Key.ItemID,
                ItemDescription         = g.First().Field <string>("item_description"),
                UnitOfMeasure           = g.First().Field <string>("u_m"),
                QtyOnHand               = g.First().Field <decimal>("on_hand"),
                QtyOnOrder              = g.First().Field <decimal>("on_order"),
                QtyAllocated            = g.First().Field <decimal>("alloc_mfg"),
                NetQtyRequired          = g.First().Field <decimal>("net_req"),
                VendorNumber            = g.First().Field <string>("vend1"),
                VendorName              = g.First().Field <string>("vendname1"),
                RepletionsAndDepletions = g
            });

            foreach (var objAnonymousPurchasedItem in objGroupedItems)
            {
                objPurchasedItem = new TimePhasedItem
                {
                    ItemID          = objAnonymousPurchasedItem.ItemID,
                    ItemDescription = objAnonymousPurchasedItem.ItemDescription,
                    UnitOfMeasure   = objAnonymousPurchasedItem.UnitOfMeasure,
                    QtyOnHand       = objAnonymousPurchasedItem.QtyOnHand,
                    QtyOnOrder      = objAnonymousPurchasedItem.QtyOnOrder,
                    QtyAllocated    = objAnonymousPurchasedItem.QtyAllocated,
                    NetQtyRequired  = objAnonymousPurchasedItem.NetQtyRequired,
                    VendorNumber    = objAnonymousPurchasedItem.VendorNumber,
                    VendorName      = objAnonymousPurchasedItem.VendorName
                };

                objPurchasedItem.RepletionsAndDepletions = new SortableBindingList <RepletionDepletionItem>();
                foreach (var objAnonymousRepletionDepletion in objAnonymousPurchasedItem.RepletionsAndDepletions)
                {
                    objRepletionDepletionItem = new RepletionDepletionItem
                    {
                        DueDate                = objAnonymousRepletionDepletion.Field <DateTime>("sub10_due_date"),
                        ProjectedOnHand        = objAnonymousRepletionDepletion.Field <decimal>("sub10_on_hand"),
                        OutstandingRequirement = objAnonymousRepletionDepletion.Field <decimal>("sub10_reqmt"),
                        OutstandingReciept     = objAnonymousRepletionDepletion.Field <decimal>("sub10_receipt"),
                        Status        = objAnonymousRepletionDepletion.Field <string>("sub10_s"),
                        ReferenceData = objAnonymousRepletionDepletion.Field <string>("sub10_reference"),
                        VendorName    = objAnonymousRepletionDepletion.Field <string>("sub10_name")
                    };

                    if (!string.IsNullOrEmpty(objRepletionDepletionItem.Status) && objRepletionDepletionItem.Status.Equals("R"))//it is a released job, so get the workcenter
                    {
                        var objArray = objRepletionDepletionItem.ReferenceData.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        objRepletionDepletionItem.Job       = objArray[1].Substring(0, objArray[1].IndexOf('-'));
                        objRepletionDepletionItem.Operation = int.TryParse(objArray[2], out intTemp) ? intTemp : 0;
                        objRepletionDepletionItem.Suffix    = int.TryParse(objArray[1].Substring(objArray[1].IndexOf('-') + 1, objArray[1].Length - objArray[1].IndexOf('-') - 1), out intTemp) ? intTemp : 0;


                        var query = objLoadDataSet.Tables[0].AsEnumerable()
                                    .Where(w => !string.IsNullOrEmpty(w.Field <string>("DerJob")) && w.Field <string>("DerJob").Equals(objRepletionDepletionItem.Job))
                                    .Where(w => !w.IsNull("DerOperNum") && w.Field <int>("DerOperNum") == objRepletionDepletionItem.Operation)
                                    .Where(w => !w.IsNull("DerSuffix") && w.Field <Int16>("DerSuffix") == objRepletionDepletionItem.Suffix)
                                    .FirstOrDefault();
                        //I kept getting unhandled null exceptions when executing the below query. turns out that there were nulls in the stored procedure and I needed to check for them; see altered query above.
                        //var query = objLoadDataSet.Tables[0].AsEnumerable()
                        //    .Where(w => w.Field<string>("DerJob").Equals(objRepletionDepletionItem.Job) &&
                        //        w.Field<int>("DerOperNum") == objRepletionDepletionItem.Operation &&
                        //        w.Field<Int16>("DerSuffix") == objRepletionDepletionItem.Suffix)
                        //    .FirstOrDefault();

                        if (query != null)
                        {
                            objRepletionDepletionItem.WorkCenter = query.Field <string>("DerWC");
                            objRepletionDepletionItem.ItemID     = query.Field <string>("PARTID");
                        }

                        //Console.WriteLine();
                    }
                    objPurchasedItem.RepletionsAndDepletions.Add(objRepletionDepletionItem);
                }

                this.Add(objPurchasedItem);
            }
        }
        public TimePhasedItemList(bool SetDueDateByProjectedSchedule)
            : base()
        {
            SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings;
            SqlCommand             objCmd;
            SqlDataAdapter         objAdapter;
            DataSet                objDataSet, objLoadDataSet;
            TimePhasedItem         objTimePhasedInventoryItem;
            RepletionDepletionItem objRepletionDepletionItem;
            SL8DbDataContext       objDB;
            int intTemp;


            objDB = new SL8DbDataContext();

            objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings();
            objSL8_WTF_DataBaseSettings.SQLConnection.Open();

            objCmd             = new SqlCommand("CLM_ResourceGroupScheduleSp", objSL8_WTF_DataBaseSettings.SQLConnection);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add(new SqlParameter("@StartDate", "1/1/1753"));
            objCmd.Parameters.Add(new SqlParameter("@EndDate", "12/31/9999"));
            objCmd.Parameters.Add(new SqlParameter("@AltNum", 0));
            objAdapter     = new SqlDataAdapter(objCmd);
            objLoadDataSet = new DataSet();
            objAdapter.Fill(objLoadDataSet);


            objCmd             = new SqlCommand("SupDemBuildSp", objSL8_WTF_DataBaseSettings.SQLConnection);
            objCmd.CommandType = CommandType.StoredProcedure;


            foreach (var objItem in objDB.PurchasedItems)
            {
                objTimePhasedInventoryItem                 = new TimePhasedItem();
                objTimePhasedInventoryItem.ItemID          = objItem.ItemID;
                objTimePhasedInventoryItem.ItemDescription = objItem.Description;
                objTimePhasedInventoryItem.UnitOfMeasure   = objItem.UnitOfMeasure;

                objCmd.Parameters.Clear();
                objCmd.Parameters.Add(new SqlParameter("@Item", objTimePhasedInventoryItem.ItemID));
                objCmd.Parameters.Add(new SqlParameter("@Whse", "MAIN"));
                objCmd.Parameters.Add(new SqlParameter("@StartingDate", DateTime.Now.AddYears(-1).ToShortDateString()));
                objCmd.Parameters.Add(new SqlParameter("@Filter", DBNull.Value));//This was the only way I could get null passed as a parameter to the stored proc

                objAdapter = new SqlDataAdapter(objCmd);
                objDataSet = new DataSet();
                objAdapter.Fill(objDataSet);

                objTimePhasedInventoryItem.RepletionsAndDepletions = new SortableBindingList <RepletionDepletionItem>();
                foreach (var objAnonymousRepletionDepletion in objDataSet.Tables[0].AsEnumerable())
                {
                    objRepletionDepletionItem = new RepletionDepletionItem
                    {
                        DueDate         = objAnonymousRepletionDepletion.Field <DateTime?>("DisplayDate") ?? DateTime.MinValue,//This was how I stopped the null errors from the null dates in the db
                        ProjectedOnHand = objAnonymousRepletionDepletion.Field <decimal>("QtyOnHand"),
                        Qty             = objAnonymousRepletionDepletion.Field <decimal>("Qty"),
                        Status          = objAnonymousRepletionDepletion.Field <string>("RcptRqmt"),
                        ReferenceData   = objAnonymousRepletionDepletion.Field <string>("Reference"),
                        ItemID          = objAnonymousRepletionDepletion.Field <string>("Parent"),
                        Job             = objAnonymousRepletionDepletion.Field <string>("RefNum")
                    };

                    if (objRepletionDepletionItem.Job.ToUpper().Trim().Equals("INIT"))
                    {
                        objTimePhasedInventoryItem.QtyOnHand = objRepletionDepletionItem.ProjectedOnHand;
                        objRepletionDepletionItem.DueDate    = DateTime.Now;
                    }
                    else if (!string.IsNullOrEmpty(objRepletionDepletionItem.Status) && objRepletionDepletionItem.Status.Equals("Q"))//it is a job, so get the workcenter
                    {
                        var objArray = objRepletionDepletionItem.ReferenceData.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        objRepletionDepletionItem.Job       = objArray[1].Substring(0, objArray[1].IndexOf('-'));
                        objRepletionDepletionItem.Operation = int.TryParse(objArray[2], out intTemp) ? intTemp : 0;
                        objRepletionDepletionItem.Suffix    = int.TryParse(objArray[1].Substring(objArray[1].IndexOf('-') + 1, objArray[1].Length - objArray[1].IndexOf('-') - 1), out intTemp) ? intTemp : 0;


                        var query = objLoadDataSet.Tables[0].AsEnumerable()
                                    .Where(w => !string.IsNullOrEmpty(w.Field <string>("DerJob")) && w.Field <string>("DerJob").Equals(objRepletionDepletionItem.Job))
                                    .Where(w => !w.IsNull("DerOperNum") && w.Field <int>("DerOperNum") == objRepletionDepletionItem.Operation)
                                    .Where(w => !w.IsNull("DerSuffix") && w.Field <Int16>("DerSuffix") == objRepletionDepletionItem.Suffix)
                                    .OrderBy(w => w.Field <DateTime>("STARTDATE")) //sort the load so I get the earliest date...
                                    .FirstOrDefault();
                        //I kept getting unhandled null exceptions when executing the below query. turns out that there were nulls in the stored procedure and I needed to check for them; see altered query above.
                        //var query = objLoadDataSet.Tables[0].AsEnumerable()
                        //    .Where(w => w.Field<string>("DerJob").Equals(objRepletionDepletionItem.Job) &&
                        //        w.Field<int>("DerOperNum") == objRepletionDepletionItem.Operation &&
                        //        w.Field<Int16>("DerSuffix") == objRepletionDepletionItem.Suffix)
                        //    .FirstOrDefault();

                        if (query != null)
                        {
                            objRepletionDepletionItem.WorkCenter = query.Field <string>("DerWC");
                            //objRepletionDepletionItem.ItemID = query.Field<string>("PARTID");
                            if (SetDueDateByProjectedSchedule)
                            {
                                objRepletionDepletionItem.DueDate = query.Field <DateTime?>("STARTDATE") ?? DateTime.MinValue; //set the DueDate to the earliest operation start date...
                            }
                        }
                    }

                    objTimePhasedInventoryItem.RepletionsAndDepletions.Add(objRepletionDepletionItem);
                }
                this.Add(objTimePhasedInventoryItem);
            }

            objSL8_WTF_DataBaseSettings.SQLConnection.Close();
        }