Micros RES 3700 Above Store Menu Item Pricing Options

[zach1070]

Good morning to all!

Hoping to gather some insight on the Micros 3700 system. We currently have a client that has a large franchise community that updates menu items frequently. The current method to update pricing involves the franchisee updating the POS configurator on the server on a store by store basis. The corporate group is able to use the Micros Menu Item Import Wizard to push price changes (like seasonal changes) to all of the stores but the franchisee does not have that ability.

What we are trying to accomplish is give the franchisee the ability to update the pricing for their items in an application or pricing tool and push those price changes down to all of their stores. In theory, this would maybe programmatically update the prices on the SQL table using queries. Does anyone have any experiences setting up a process like this or have any thoughts?

Thanks in advance!

 

[Moregelen]

Is the concept using Enterprise Mangement? If they are, you'll have to keep in mind that sometimes the local database will have multiple price tiers. Also keep in mind how things like combo meals vs. regular menu items behave - is the concept linking the prices to the container item or the main item? Things like that. Also keep in mind it is possible to have multiple valid price records for an items because you can set activation/ending dates on those price records, and they can most certainly overlap and still be valid. The pricing isn't necessarily simple in Micros, though it isn't too hard either.

We are a franchise concept, and while we have a tool we built for stores that they can use to more easily manage their prices (normalizes everything so they don't have to know how to handle combo meals, regular meals, etc), we also provide packaged scripts that the manager can run on the location with one click that applies our suggested pricing to anything that changed. So for Q2 Promo, they'll have a "Apply Suggested 2019 Q2 Price Changes", and that's that. Doesn't help with larger ownership groups that don't want to use our suggested pricing, but do want to change everything of course.

It really is just a series of SQL commands, it is just that you need an understanding on how everything is structured before you monkey around with things.

 

[Moregelen]

To give you an idea of what we're doing (code is long in the tooth (pre-dates me so not my problem!), doesn't follow any best practices, and needs cleaning up.. but it works):

[COLOR=#0000FF]using[/color] System;
[COLOR=#0000FF]using[/color] System.ComponentModel;
[COLOR=#0000FF]using[/color] System.Linq;
[COLOR=#0000FF]using[/color] System.Data;
[COLOR=#0000FF]using[/color] System.Collections.Generic;
[COLOR=#0000FF]using[/color] Micros3700;
[COLOR=#0000FF]using[/color] System.Collections.ObjectModel;
 
[COLOR=#0000FF]namespace[/color] Concept.SupportCenter
{
    [COLOR=#0000FF]public[/color] [COLOR=#0000FF]class[/color] [COLOR=#2B91AF]PriceDefinition[/color] : INotifyPropertyChanged
    {
        [DisplayName([COLOR=#A31515]"Object Number"[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] ObjNum { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] PriceSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]string[/color] Name { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] PriceType Type { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] TypeSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] PriceLevels PriceLevels { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]bool[/color] Visible { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]private[/color] [COLOR=#0000FF]bool[/color] _OutOfMI = [COLOR=#0000FF]false[/color];
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]bool[/color] OutOfMI
        {
            [COLOR=#0000FF]get[/color] { [COLOR=#0000FF]return[/color] _OutOfMI; }
            [COLOR=#0000FF]set[/color]
            {
                [COLOR=#0000FF]if[/color] (_OutOfMI != value)
                {
                    _OutOfMI = value;
                    PropertyChanged?.Invoke([COLOR=#0000FF]this[/color], [COLOR=#0000FF]new[/color] PropertyChangedEventArgs([COLOR=#A31515]"OutOfMI"[/color]));
                }
            }
        }
 
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]event[/color] PropertyChangedEventHandler PropertyChanged;
 
        [COLOR=#808080]#region[/color] TYPE QUERIES
        [COLOR=#0000FF]readonly[/color] [COLOR=#0000FF]string[/color][] GetDefaultPriceQueries = [COLOR=#0000FF]new[/color] [COLOR=#0000FF]string[/color][]
        {
            [COLOR=#A31515]"SELECT * FROM MICROS.mi_price_def WHERE mi_seq = {0} AND effective_to IS NULL ORDER BY effective_from desc"[/color],
            [COLOR=#A31515]"SELECT * FROM MICROS.mi_combo_price_def WHERE combo_menu_item_seq = {0} AND effective_to IS NULL ORDER BY effective_from desc"[/color]
        };
        [COLOR=#0000FF]readonly[/color] [COLOR=#0000FF]string[/color][] GetTempPricesQueries = [COLOR=#0000FF]new[/color] [COLOR=#0000FF]string[/color][]
        {
            [COLOR=#A31515]"SELECT *, mi_seq as miseq, mi_price_seq as priceseq FROM MICROS.mi_price_def WHERE mi_seq = {0} AND effective_to IS NOT NULL AND effective_to > NOW() ORDER BY effective_to, effective_from desc"[/color],
            [COLOR=#A31515]"SELECT *, combo_menu_item_seq as miseq, combo_mi_price_seq as priceseq FROM MICROS.mi_combo_price_def WHERE combo_menu_item_seq = {0} AND effective_to IS NOT NULL AND effective_to > NOW() ORDER BY effective_to, effective_from desc"[/color]
        };
        [COLOR=#0000FF]readonly[/color] [COLOR=#0000FF]string[/color][] UpdateDefaultQueries = [COLOR=#0000FF]new[/color] [COLOR=#0000FF]string[/color][]
        {
            [COLOR=#A31515]"UPDATE MICROS.mi_price_def SET {0} WHERE mi_seq = {1} AND effective_to IS NULL"[/color],
            [COLOR=#A31515]"UPDATE MICROS.mi_combo_price_def SET {0} WHERE combo_menu_item_seq = {1} AND effective_to IS NULL"[/color]
        };
        [COLOR=#0000FF]readonly[/color] [COLOR=#0000FF]string[/color][] InsertQueries = [COLOR=#0000FF]new[/color] [COLOR=#0000FF]string[/color][]
        {
            [COLOR=#A31515]"INSERT INTO MICROS.mi_price_def (mi_seq) VALUES ({0})"[/color],
            [COLOR=#A31515]"INSERT INTO MICROS.mi_combo_price_def (combo_menu_item_seq) VALUES ({0})"[/color]
        };
        [COLOR=#0000FF]readonly[/color] [COLOR=#0000FF]string[/color][] InsertTempQueries = [COLOR=#0000FF]new[/color] [COLOR=#0000FF]string[/color][]
        {
            [COLOR=#A31515]"INSERT INTO MICROS.mi_price_def ({0}) VALUES ({1})"[/color],
            [COLOR=#A31515]"INSERT INTO MICROS.mi_combo_price_def ({0}) VALUES ({1})"[/color]
        };
        [COLOR=#808080]#endregion[/color]
 
        [COLOR=#0000FF]private[/color] [COLOR=#0000FF]double[/color] _TempPrice;
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]double[/color] NewPrice
        {
            [COLOR=#0000FF]get[/color]
            {
                [COLOR=#0000FF]return[/color] _TempPrice;
            }
            [COLOR=#0000FF]set[/color]
            {
                _TempPrice = value;
            }
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]double[/color] DefaultPrice
        {
            [COLOR=#0000FF]get[/color]
            {
                [COLOR=#0000FF]try[/color]
                {
                    [COLOR=#0000FF]return[/color] GetDefaultPrice();
                } [COLOR=#0000FF]catch[/color]
                {
                    [COLOR=#0000FF]return[/color] 0;
                }
            }
        }
[COLOR=#0000FF]        [/color][COLOR=#0000FF]public[/color] [COLOR=#0000FF]double[/color] GetDefaultPrice(Database md = [COLOR=#0000FF]null[/color])
        {
            [COLOR=#0000FF]if[/color] (md == [COLOR=#0000FF]null[/color])
            {
                md = Database.OdbcDbConnection();
                md.Connect();
            }
            [COLOR=#0000FF]string[/color] sql = [COLOR=#0000FF]string[/color].Format(GetDefaultPriceQueries[([COLOR=#0000FF]int[/color])Type], TypeSeq);
            [COLOR=#008000]//get first price on list we update; they all get mirrored when we update anyway[/color]
            [COLOR=#0000FF]int[/color] firstPriceToLookAt = 0;
            [COLOR=#0000FF]for[/color] ([COLOR=#0000FF]int[/color] i = 0; i < 10; i++)
            {
                PriceLevels pl = (PriceLevels)([COLOR=#0000FF]int[/color])Math.Pow(2, i);
                [COLOR=#0000FF]if[/color] ((PriceLevels & pl) != 0)
                {
                    firstPriceToLookAt = i + 1;
                    [COLOR=#0000FF]break[/color];
                }
            }
            [COLOR=#0000FF]if[/color] (firstPriceToLookAt == 0)
                [COLOR=#0000FF]return[/color] 0;
 
            [COLOR=#0000FF]string[/color] sqlCheck = [COLOR=#0000FF]string[/color].Format(GetDefaultPriceQueries[([COLOR=#0000FF]int[/color])Type], TypeSeq);
            [COLOR=#0000FF]var[/color] checkRows = md.Query(sqlCheck, 30);
            [COLOR=#0000FF]if[/color] (checkRows == [COLOR=#0000FF]null[/color] || checkRows.Rows.Count == 0)
                md.Execute([COLOR=#0000FF]string[/color].Format(InsertQueries[([COLOR=#0000FF]int[/color])Type], TypeSeq), 30);
 
            [COLOR=#0000FF]var[/color] prices = md.Query(sql, 30);
            [COLOR=#0000FF]if[/color] (prices != [COLOR=#0000FF]null[/color] && prices.Rows.Count > 0)
            {
                [COLOR=#0000FF]string[/color] price = prices.Rows[0][[COLOR=#A31515]"preset_amt_"[/color] + firstPriceToLookAt] != DBNull.Value ? prices.Rows[0][[COLOR=#A31515]"preset_amt_"[/color] + firstPriceToLookAt].ToString() : [COLOR=#A31515]"0.00"[/color];
                [COLOR=#0000FF]return[/color] [COLOR=#0000FF]double[/color].Parse(price);
            }
            [COLOR=#0000FF]return[/color] 0;
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]void[/color] SetDefaultPrice(Database md, [COLOR=#0000FF]double[/color] newPrice)
        {
            List<PriceDefinition> itemsToUpdate = [COLOR=#0000FF]new[/color] List<PriceDefinition>();
            itemsToUpdate.Add([COLOR=#0000FF]this[/color]);
            [COLOR=#0000FF]var[/color] otherPrices = PriceDefinition.GetLinkedDefs([COLOR=#0000FF]this[/color].PriceSeq, md);
            [COLOR=#0000FF]if[/color] (otherPrices != [COLOR=#0000FF]null[/color]) itemsToUpdate.AddRange(otherPrices);
 
            [COLOR=#0000FF]foreach[/color] (PriceDefinition pd [COLOR=#0000FF]in[/color] itemsToUpdate)
            {
                [COLOR=#0000FF]string[/color] priceFields = [COLOR=#A31515]""[/color];
                [COLOR=#0000FF]for[/color] ([COLOR=#0000FF]int[/color] i = 0; i < 10; i++)
                {
                    PriceLevels priceLevel = (PriceLevels)Math.Pow(2, i);
                    [COLOR=#0000FF]if[/color] ((pd.PriceLevels & priceLevel) != 0)
                    {
                        [COLOR=#0000FF]if[/color] (![COLOR=#0000FF]string[/color].IsNullOrEmpty(priceFields)) priceFields += [COLOR=#A31515]", "[/color];
                        priceFields += [COLOR=#0000FF]string[/color].Format([COLOR=#A31515]"preset_amt_{0} = '{1}'"[/color], i + 1, newPrice);
                    }
                }
 
                [COLOR=#0000FF]string[/color] sqlCheck = [COLOR=#0000FF]string[/color].Format(GetDefaultPriceQueries[([COLOR=#0000FF]int[/color])pd.Type], pd.TypeSeq);
                [COLOR=#0000FF]var[/color] checkRows = md.Query(sqlCheck, 30);
                [COLOR=#0000FF]if[/color] (checkRows == [COLOR=#0000FF]null[/color] || checkRows.Rows.Count == 0) md.Execute([COLOR=#0000FF]string[/color].Format(InsertQueries[([COLOR=#0000FF]int[/color])pd.Type], pd.TypeSeq), 30);
                md.Execute([COLOR=#0000FF]string[/color].Format(UpdateDefaultQueries[([COLOR=#0000FF]int[/color])pd.Type], priceFields, pd.TypeSeq), 30);
            }
            _TempPrice = 0;
            PropertyChanged?.Invoke([COLOR=#0000FF]this[/color], [COLOR=#0000FF]new[/color] PropertyChangedEventArgs([COLOR=#A31515]"NewPrice"[/color]));
            PropertyChanged?.Invoke([COLOR=#0000FF]this[/color], [COLOR=#0000FF]new[/color] PropertyChangedEventArgs([COLOR=#A31515]"DefaultPrice"[/color]));
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]void[/color] RemoveTempPrice(Database md, TemporaryPrice tprice)
        {
            [COLOR=#0000FF]switch[/color] (tprice.Type)
            {
                [COLOR=#0000FF]case[/color] PriceType.ComboMenuItem:
                    md.Execute([COLOR=#A31515]"DELETE FROM MICROS.mi_combo_price_def WHERE combo_menu_item_seq = ? AND combo_mi_price_seq = ?"[/color], 30, tprice.MiSeq, tprice.MiPriceSeq);
                    [COLOR=#0000FF]break[/color];
                [COLOR=#0000FF]case[/color] PriceType.MenuItem:
                    md.Execute([COLOR=#A31515]"DELETE FROM MICROS.mi_price_def WHERE mi_seq = ? AND mi_price_seq = ?"[/color], 30, tprice.MiSeq, tprice.MiPriceSeq);
                    [COLOR=#0000FF]break[/color];
            }
            [COLOR=#0000FF]var[/color] toRemove = TemporaryPrices.Where(t => t.MiSeq == tprice.MiSeq && t.MiPriceSeq == tprice.MiPriceSeq).ToArray();
            [COLOR=#0000FF]for[/color] ([COLOR=#0000FF]int[/color] i = toRemove.Count() - 1; i >= 0; i--)
                TemporaryPrices.Remove(toRemove[i]);
            [COLOR=#008000]//PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("TemporaryPrices"));[/color]
        }
        [COLOR=#0000FF]private[/color] ObservableCollection<TemporaryPrice> _TempPrices;
        [COLOR=#0000FF]public[/color] ObservableCollection<TemporaryPrice> TemporaryPrices
        {
            [COLOR=#0000FF]get[/color]
            {
                [COLOR=#0000FF]if[/color] (_TempPrices == [COLOR=#0000FF]null[/color])
                {
                    [COLOR=#0000FF]using[/color] (Database md = Database.OdbcDbConnection())
                    {
                        md.Connect();
                        [COLOR=#0000FF]var[/color] newCollection = [COLOR=#0000FF]new[/color] ObservableCollection<TemporaryPrice>(GetTemporaryPrices(md).OrderByDescending(p => p.Start).ThenBy(p => p.End));
                        newCollection.CollectionChanged += (s, e) => { PropertyChanged?.Invoke([COLOR=#0000FF]this[/color], [COLOR=#0000FF]new[/color] PropertyChangedEventArgs([COLOR=#A31515]"TemporaryPrices"[/color])); };
                        _TempPrices =  newCollection;
                    }
                }
                [COLOR=#0000FF]return[/color] _TempPrices;
            }
            [COLOR=#0000FF]set[/color]
            {
                _TempPrices = value;
                PropertyChanged?.Invoke([COLOR=#0000FF]this[/color], [COLOR=#0000FF]new[/color] PropertyChangedEventArgs([COLOR=#A31515]"TemporaryPrices"[/color]));
            }
        }
        [COLOR=#0000FF]public[/color] ObservableCollection<TemporaryPrice> GetTemporaryPrices(Database md)
        {
            ObservableCollection<TemporaryPrice> tPrices = [COLOR=#0000FF]new[/color] ObservableCollection<TemporaryPrice>();
            [COLOR=#0000FF]string[/color] sql = [COLOR=#0000FF]string[/color].Format(GetTempPricesQueries[([COLOR=#0000FF]int[/color])Type], TypeSeq);
 
            [COLOR=#0000FF]int[/color] firstPriceToLookAt = 0;
            [COLOR=#0000FF]for[/color] ([COLOR=#0000FF]int[/color] i = 0; i < 10; i++)
            {
                PriceLevels pl = (PriceLevels)([COLOR=#0000FF]int[/color])Math.Pow(2, i);
                [COLOR=#0000FF]if[/color] ((PriceLevels & pl) != 0)
                {
                    firstPriceToLookAt = i + 1;
                    [COLOR=#0000FF]break[/color];
                }
            }
            [COLOR=#0000FF]if[/color] (firstPriceToLookAt == 0)
                [COLOR=#0000FF]return[/color] tPrices;
 
            [COLOR=#0000FF]var[/color] prices = md.Query(sql, 30);
            [COLOR=#0000FF]if[/color] (prices != [COLOR=#0000FF]null[/color] && prices.Rows.Count > 0)
            {
                [COLOR=#0000FF]return[/color] [COLOR=#0000FF]new[/color] ObservableCollection<TemporaryPrice>(([COLOR=#0000FF]from[/color] row [COLOR=#0000FF]in[/color] prices.AsEnumerable()
                          [COLOR=#0000FF]select[/color] [COLOR=#0000FF]new[/color] TemporaryPrice(
                              row[[COLOR=#A31515]"preset_amt_"[/color] + firstPriceToLookAt] != DBNull.Value ? [COLOR=#0000FF]double[/color].Parse(row[[COLOR=#A31515]"preset_amt_"[/color] + firstPriceToLookAt].ToString()) : 0,
                              DateTime.Parse(row[[COLOR=#A31515]"effective_from"[/color]].ToString()),
                              DateTime.Parse(row[[COLOR=#A31515]"effective_to"[/color]].ToString()),
                              [COLOR=#0000FF]int[/color].Parse(row[[COLOR=#A31515]"miseq"[/color]].ToString()),
                              [COLOR=#0000FF]int[/color].Parse(row[[COLOR=#A31515]"priceseq"[/color]].ToString()),
                              Type,
                              [COLOR=#0000FF]this[/color]
                          )));
            }
 
            [COLOR=#0000FF]return[/color] tPrices;
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]void[/color] AddTemporaryPrice(Database md, [COLOR=#0000FF]double[/color] price, DateTime start, DateTime end)
        {
            List<PriceDefinition> itemsToUpdate = [COLOR=#0000FF]new[/color] List<PriceDefinition>();
            itemsToUpdate.Add([COLOR=#0000FF]this[/color]);
            [COLOR=#0000FF]var[/color] otherPrices = PriceDefinition.GetLinkedDefs([COLOR=#0000FF]this[/color].PriceSeq, md);
            [COLOR=#0000FF]if[/color] (otherPrices != [COLOR=#0000FF]null[/color]) itemsToUpdate.AddRange(otherPrices);
 
            [COLOR=#0000FF]foreach[/color] (PriceDefinition pd [COLOR=#0000FF]in[/color] itemsToUpdate)
            {
                [COLOR=#0000FF]string[/color] insertFields = (pd.Type == PriceType.MenuItem ? [COLOR=#A31515]"mi_seq"[/color] : [COLOR=#A31515]"combo_menu_item_seq"[/color]) + [COLOR=#A31515]", effective_from, effective_to"[/color];
                [COLOR=#0000FF]string[/color] insertValues = [COLOR=#0000FF]string[/color].Format([COLOR=#A31515]"{0}, '{1:yyyy-MM-dd HH:mm}', '{2:yyyy-MM-dd HH:mm}'"[/color], pd.TypeSeq, start, end);
                [COLOR=#0000FF]for[/color] ([COLOR=#0000FF]int[/color] i = 0; i < 10; i++)
                {
                    PriceLevels priceLevel = (PriceLevels)Math.Pow(2, i);
                    [COLOR=#0000FF]if[/color] ((pd.PriceLevels & priceLevel) != 0)
                    {
                        [COLOR=#008000]//if (!string.IsNullOrEmpty(priceFields)) priceFields += ", ";[/color]
                        insertFields += [COLOR=#0000FF]string[/color].Format([COLOR=#A31515]", preset_amt_{0}"[/color], i + 1);
                        insertValues += [COLOR=#A31515]", '"[/color] + price + [COLOR=#A31515]"'"[/color];
                    }
                }
 
                md.Execute([COLOR=#0000FF]string[/color].Format(InsertTempQueries[([COLOR=#0000FF]int[/color])pd.Type], insertFields, insertValues), 30);
            }
            TemporaryPrices = GetTemporaryPrices(md);
            [COLOR=#008000]//PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("TemporaryPrices"));[/color]
        }
 
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]static[/color] PriceDefinition GetPriceDef([COLOR=#0000FF]int[/color] priceSeq, Database md)
        {
            [COLOR=#0000FF]var[/color] priceDefs = GetPriceDefs(md);
            [COLOR=#0000FF]if[/color] (priceDefs == [COLOR=#0000FF]null[/color])
                [COLOR=#0000FF]return[/color] [COLOR=#0000FF]null[/color];
            [COLOR=#0000FF]return[/color] priceDefs.Where(p => p.PriceSeq == priceSeq).FirstOrDefault();
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]static[/color] List<PriceDefinition> GetPriceDefs(Database md, [COLOR=#0000FF]string[/color] filter = [COLOR=#A31515]""[/color], [COLOR=#0000FF]bool[/color] visibleOnly = [COLOR=#0000FF]false[/color])
        {
            [COLOR=#0000FF]if[/color] (md == [COLOR=#0000FF]null[/color] || !md.Connected)
                [COLOR=#0000FF]return[/color] [COLOR=#0000FF]null[/color];
 
            [COLOR=#0000FF]var[/color] defs = md.Query([COLOR=#0000FF]string[/color].Format([COLOR=#800000]@"[/color]
[COLOR=#800000]                                select [/color]
[COLOR=#800000]                                    zpd.*,[/color]
[COLOR=#800000]                                    (CASE type[/color]
[COLOR=#800000]                                    WHEN 0 THEN mdo.obj_num[/color]
[COLOR=#800000]                                    WHEN 1 THEN cmid.combo_menu_item_seq[/color]
[COLOR=#800000]                                    END) as obj_num,[/color]
[COLOR=#800000]                                    (CASE type[/color]
[COLOR=#800000]                                    WHEN 0 THEN mso.ob_mi32_out_of_mi[/color]
[COLOR=#800000]                                    WHEN 1 THEN ms.ob_mi32_out_of_mi[/color]
[COLOR=#800000]                                    END) as out_of_mi[/color]
[COLOR=#800000]                                from custom.zfl_price_def zpd[/color]
[COLOR=#800000]                                left join micros.mi_def mdo on mdo.mi_seq = zpd.type_seq[/color]
[COLOR=#800000]                                left join micros.mi_status mso on mso.mi_seq = zpd.type_seq[/color]
[COLOR=#800000]                                left join micros.combo_menu_item_def cmid on cmid.combo_menu_item_seq = zpd.type_seq[/color]
[COLOR=#800000]                                left join micros.mi_def md on cmid.mi_seq = md.mi_seq[/color]
[COLOR=#800000]                                left join micros.mi_status ms on ms.mi_seq = md.mi_seq[/color]
[COLOR=#800000]                                where obj_num is not null and zpd.name LIKE ? {0} order by name"[/color],
                                visibleOnly ? [COLOR=#A31515]" AND zpd.visible = 'T'"[/color] : [COLOR=#A31515]""[/color]),
                                30,
                                [COLOR=#A31515]"%"[/color] + filter + [COLOR=#A31515]"%"[/color]);
            [COLOR=#0000FF]return[/color] ([COLOR=#0000FF]from[/color] def [COLOR=#0000FF]in[/color] defs.AsEnumerable()
                    [COLOR=#0000FF]select[/color] [COLOR=#0000FF]new[/color] PriceDefinition()
                    {
                        PriceSeq = ([COLOR=#0000FF]int[/color])def[[COLOR=#A31515]"price_seq"[/color]],
                        Name = def[[COLOR=#A31515]"name"[/color]].ToString(),
                        Type = (PriceType)(([COLOR=#0000FF]int[/color])def[[COLOR=#A31515]"type"[/color]]),
                        TypeSeq = ([COLOR=#0000FF]int[/color])def[[COLOR=#A31515]"type_seq"[/color]],
                        PriceLevels = (PriceLevels)(([COLOR=#0000FF]int[/color])def[[COLOR=#A31515]"price_levels"[/color]]),
                        Visible = def[[COLOR=#A31515]"visible"[/color]].ToString() == [COLOR=#A31515]"T"[/color],
                        ObjNum = ([COLOR=#0000FF]int[/color])def[[COLOR=#A31515]"obj_num"[/color]],
                        OutOfMI = def[[COLOR=#A31515]"out_of_mi"[/color]].ToString() == [COLOR=#A31515]"T"[/color]
                    }).ToList();
        }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]static[/color] List<PriceDefinition> GetLinkedDefs([COLOR=#0000FF]int[/color] parent, Database md)
        {
            [COLOR=#0000FF]var[/color] links = LinkedItem.GetLinks(parent, md);
            [COLOR=#0000FF]return[/color] ([COLOR=#0000FF]from[/color] link [COLOR=#0000FF]in[/color] links.AsEnumerable()
                    [COLOR=#0000FF]select[/color] PriceDefinition.GetPriceDef(link.LinkedSeq, md)).ToList();
        }
    }
 
    [COLOR=#0000FF]public[/color] [COLOR=#0000FF]class[/color] [COLOR=#2B91AF]LinkedItem[/color]
    {
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] LinkedSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [Browsable([COLOR=#0000FF]false[/color])]
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] ParentSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]string[/color] LinkedName { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]set[/color]; }
 
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]static[/color] LinkedItem[] GetLinks([COLOR=#0000FF]int[/color] parentSeq, Database md)
        {
            [COLOR=#0000FF]if[/color] (md == [COLOR=#0000FF]null[/color] || !md.Connected)
                [COLOR=#0000FF]return[/color] [COLOR=#0000FF]null[/color];
 
            [COLOR=#0000FF]var[/color] linkedItems = md.Query([COLOR=#A31515]"SELECT zpl.parent_seq, zpl.link_seq, zpd.name FROM CUSTOM.zfl_price_links zpl JOIN CUSTOM.zfl_price_def zpd ON zpl.link_seq = zpd.price_seq  WHERE parent_seq = "[/color] + parentSeq, 30);
            [COLOR=#0000FF]return[/color] ([COLOR=#0000FF]from[/color] linkedItem [COLOR=#0000FF]in[/color] linkedItems.AsEnumerable()
                    [COLOR=#0000FF]select[/color] [COLOR=#0000FF]new[/color] LinkedItem()
                    {
                        LinkedSeq = ([COLOR=#0000FF]int[/color])linkedItem[[COLOR=#A31515]"link_seq"[/color]],
                        ParentSeq = ([COLOR=#0000FF]int[/color])linkedItem[[COLOR=#A31515]"parent_seq"[/color]],
                        LinkedName = linkedItem[[COLOR=#A31515]"name"[/color]].ToString()
                    }).ToArray();
        }
    }
    [COLOR=#0000FF]public[/color] [COLOR=#0000FF]class[/color] [COLOR=#2B91AF]TemporaryPrice[/color]
    {
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]double[/color] Price { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] DateTime Start { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] DateTime End { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] MiSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] [COLOR=#0000FF]int[/color] MiPriceSeq { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] PriceType Type { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
        [COLOR=#0000FF]public[/color] PriceDefinition Parent { [COLOR=#0000FF]get[/color]; [COLOR=#0000FF]private[/color] [COLOR=#0000FF]set[/color]; }
 
        [COLOR=#0000FF]public[/color] TemporaryPrice([COLOR=#0000FF]double[/color] p, DateTime s, DateTime e, [COLOR=#0000FF]int[/color] miseq, [COLOR=#0000FF]int[/color] priceseq, PriceType type, PriceDefinition parent)
        {
            Price = p;
            Start = s;
            End = e;
            MiSeq = miseq;
            MiPriceSeq = priceseq;
            Type = type;
            Parent = parent;
        }
    }
 
    [Flags]
    [COLOR=#0000FF]public[/color] [COLOR=#0000FF]enum[/color] [COLOR=#2B91AF]PriceLevels[/color]
    {
        Level1,
        Level2,
        Level3,
        Level4,
        Level5,
        Level6,
        Level7,
        Level8,
        Level9,
        Level10
    }
    [COLOR=#0000FF]public[/color] [COLOR=#0000FF]enum[/color] [COLOR=#2B91AF]PriceType[/color]
    {
        MenuItem,
        ComboMenuItem
    }
}