Sunji-posv-net/pos/JwKdsV.Core/SqlConstant.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace JwKdsV.Core
{
    public class SqlConstant
    {
        /// <summary>
        /// 获取指定key设置
        /// </summary>
        public const string ConfigQueryByKey = "select [id],[tenantId],[group],[name],[keys],[values],[createUser],[createDate], [modifyDate], [modifyUser] from [pos_config] where [keys] = '{0}';";

        /// <summary>
        /// 获取指定key设置
        /// </summary>
        public const string ConfigQueryByGroupAndKey = "select [id],[tenantId],[group],[name],[keys],[values],[createUser],[createDate], [modifyDate], [modifyUser] from [pos_config] where [group] = '{0}' and [keys] = '{1}';";

        /// <summary>
        /// 获取参数组
        /// </summary>
        public const string ConfigQueryByGroups = "select [id],[tenantId],[group],[name],[keys],[values],[createUser],[createDate], [modifyDate], [modifyUser] from [pos_config] where [group] in ({0});";

        /// <summary>
        /// 获取全部系统参数
        /// </summary>
        public const string ConfigQueryAllToDictionary = "select [keys],[values] from [pos_config];";

        /// <summary>
        /// 按指定Group获取系统参数
        /// </summary>
        public const string ConfigQueryByGroupToDictionary = "select [keys],[values] from [pos_config] where [group] = '{0}';";

        /// <summary>
        /// 更新或者插入配置参数信息
        /// </summary>
        public const string ConfigSaveOrUpdate = "replace into [pos_config]([id],[tenantId],[group],[name],[keys],[values],[createUser],[createDate], [modifyUser], [modifyDate]) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');";

        /// <summary>
        /// 获取posversion
        /// </summary>
        public const string PosVersionQuery = "SELECT [id], [version], [dbVersion], [createDate], [createUser], [modifyDate], [modifyUser] FROM [pos_version];";

        /// <summary>
        /// 系统全部模块查询
        /// </summary>
        public const string ModuleQueryAll = "select [id], [tenantId], [area], [parentId], [name], [alias], [keycode], [keydata], [color1], [color2],[color3],[fontSize], [shortcut], [orderNo], [icon], [enable], [resourceId], [width], [height], [createUser], [createDate], [modifyUser], [modifyDate] from [pos_module];";
        //, [width], [height]

        /// <summary>
        /// 查询全部功能资源
        /// </summary>
        public const string ResourcesQueryAll = "select [id], [tenantId], [group], [name], [keycode], [keydata], [enable], [createUser], [createDate], [modifyUser], [modifyDate] from [pos_resources];";

        /// <summary>
        /// 根据keycode查询资源
        /// </summary>
        public const string ResourcesQueryByKeycode = "select [id], [tenantId], [group], [name], [keycode], [keydata], [enable],[createUser], [createDate], [modifyUser], [modifyDate] from [pos_resources] where [keycode] = '{0}';";

        /// <summary>
        /// 获取全部小类
        /// </summary>
        public const string QueryAllDishType = "SELECT [id], [no], [name], [seriesId], [color], [createUser], [createDate], [modifyUser], [modifyDate] FROM [food_dish_type];";

        /// <summary>
        /// 获取全部大类
        /// </summary>
        public const string QueryAllDishSeries = "SELECT [id], [no], [name], [createUser], [createDate], [modifyUser], [modifyDate] FROM [food_dish_series]; ";

        #region V2
        /// <summary>
        /// 查询全部API接口
        /// </summary>
        public const string ApisQueryAll = "SELECT id, tenantId, apiType, appKey, appSecret, locale, format, client, version, routing, memo, ext1, ext2, ext3, createUser, createDate, modifyUser, modifyDate FROM pos_apis;";

        /// <summary>
        /// 查询API对应的URL数据
        /// </summary>
        public const string UrlsQueryAll = "SELECT id, tenantId, apiType, protocol, url, contextPath, userDefined, enable, memo, ext1, ext2, ext3, createUser, createDate, modifyUser, modifyDate FROM pos_urls;";

        #endregion

        /// <summary>
        /// 查询所有商品
        /// </summary>
        public const string ProductExtAll = @"select p.[id],p.[tenantId],p.[brandId],p.[typeId],p.[typePath],p.[no],p.[name],p.[shortName],p.[spell],p.[assistNo],p.[otherNo],p.[barCode],p.[english],p.[unitId],p.[memo],p.[commissionType],p.[commissionValue],p.[discountFlag],p.[suitFlag],p.[tapleFlag],p.[weighFlag],p.[currentFlag],p.[labelPrintFlag],p.[stopFlag],p.[groupName],p.[picture],p.[mebDiscountFlag],p.[giveFlag],p.[promotionFlag],p.[type],p.[stockFlag],p.[pointType],p.[pointValue],p.[purchaseTax],p.[saleTax],p.[lyRate],p.[specCount],p.[ext1],p.[ext3],p.[ext2],p.[createUser],p.[createDate],p.[modifyUser],p.[modifyDate],t.name as typeName,u.name as unitName,s.name as specName,s.id as specId,s.[price],s.[memberPrice],s.[otherPrice],s.[costPrice],s.[purchasePrice],s.[dispatchPrice],s.[minPrice],
                                p.[kdsFlag]
                                from pos_product p
                                left join pos_product_unit u on p.unitId = u.id
                                left join pos_product_type t on p.typeId = t.id
                                left join
                                (
                                select sp.* , sps.* from
                                (select productId, min(no) as minNo from  pos_product_spec where deleteFlag = 0 group by productId) sps
                               left join pos_product_spec sp on(sps.productId = sp.productId and sp.no = sps.minNo) where sp.deleteFlag = 0
                                ) s on s.productId = p.id   WHERE p.`type` >= 0 AND p.`type` <= 9 AND p.`stopFlag` = 0 and s.id is not null ";

        public const string ProductExtAll2 = @"select p.[id],p.[tenantId],p.[brandId],p.[typeId],p.[typePath],p.[no],p.[name],p.[shortName],p.[spell],p.[assistNo],p.[otherNo],p.[barCode],p.[english],p.[unitId],p.[memo],p.[commissionType],p.[commissionValue],p.[discountFlag],p.[suitFlag],p.[tapleFlag],p.[weighFlag],p.[currentFlag],p.[labelPrintFlag],p.[stopFlag],p.[groupName],p.[picture],p.[mebDiscountFlag],p.[giveFlag],p.[promotionFlag],p.[type],p.[stockFlag],p.[pointType],p.[pointValue],p.[purchaseTax],p.[saleTax],p.[lyRate],p.[specCount],p.[ext1],p.[ext3],p.[ext2],p.[createUser],p.[createDate],p.[modifyUser],p.[modifyDate],t.name as typeName,u.name as unitName,s.name as specName,s.id as specId ,k.chuda,k.chudaFlag,k.chupin,k.chupinFlag ,k.[labelFlag] as chuDaLabelFlag, k.[labelValue] as chuDaLabel,s.[price],s.[memberPrice],s.[otherPrice],s.[costPrice],s.[purchasePrice],s.[dispatchPrice],s.[minPrice],d.[chuxian],d.[chuxianFlag],d.[chuxianTime],d.[chupin] as kdsChupin,d.[chupinFlag] as kdsChupinFlag,d.[chupinTime] as kdsChupinTime,
                                p.[kdsFlag]
                                from pos_product p
                                left join pos_product_unit u on p.unitId = u.id
                                left join pos_product_type t on p.typeId = t.id
                                left join pos_kit_product k on p.id = k.productId
                                left join pos_kds_product d on p.id = d.productId
                                left join
                                (
                                select sp.* , sps.* from
                                (select productId, min(no) as minNo from  pos_product_spec where deleteFlag = 0 group by productId) sps
                               left join pos_product_spec sp on(sps.productId = sp.productId and sp.no = sps.minNo) where sp.deleteFlag = 0
                                ) s on s.productId = p.id   WHERE p.`type` >= 0 AND p.`type` <= 9 AND p.`stopFlag` = 0 and s.id is not null ";
    }
}