using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Threading.Tasks;

namespace MySqlTest

{

    class Program

    {

        static void Main(string[] args)

        {

            string sql = "select id,code,name,modifytime,storeid from retail_cashier where profileid=@profileid and  storeId=@storeId order by id";

            var parts = MysqlPageHelper.BuildPageQuery(sql, , );

            Console.WriteLine("sql：{0}；",parts.Sql);

            Console.WriteLine("SqlCount：{0}", parts.SqlCount);

            Console.WriteLine("SqlPage：{0}",parts.SqlPage);

            Console.WriteLine("SqlOrderBy：{0}", parts.SqlOrderBy);

            Console.ReadKey();

        }

    }

    public class MysqlPageHelper

    {

        public Regex RegexColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b",

            RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        public Regex RegexDistinct = new Regex(@"\ADISTINCT\s",

            RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        public Regex RegexOrderBy =

            new Regex(

                @"\bORDER\s+BY\s+(?!.*?(?:\)|\s+)AS\s)(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*",

                RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        private string _sqlSelectRemoved = null;

        public void SplitSql(string sql, long skip, long take)

        {

            this.Sql = sql;

            this.SqlCount = null;

            this.SqlOrderBy = null;

            // 从中提取列

            var m = RegexColumns.Match(this.Sql);

            if (!m.Success)

                throw new Exception("无法解析分页查询的SQL语句");

            // sql语句中columns替换成count(*)

            var g = m.Groups[];

            this._sqlSelectRemoved = this.Sql.Substring(g.Index);

            if (RegexDistinct.IsMatch(this._sqlSelectRemoved))

                this.SqlCount = this.Sql.Substring(, g.Index) + "COUNT(" + m.Groups[].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);

            else

                this.SqlCount = this.Sql.Substring(, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);

            // sql语句最后order by

            m = RegexOrderBy.Match(this.SqlCount);

            if (m.Success)

            {

                g = m.Groups[];

                this.SqlOrderBy = g.ToString();

                this.SqlCount = this.SqlCount.Substring(, g.Index) + this.SqlCount.Substring(g.Index + g.Length);

            }

            //分页读取数据

            SqlPage = string.Format("{0}\nLIMIT {1} OFFSET {2}", this.Sql, take, skip);

        }

        public static MysqlPageHelper BuildPageQuery(string sql, long skip, long take)

        {

            var page=new MysqlPageHelper();

            page.SplitSql(sql,skip,take);

            return page;

        }

        public string Sql { get; private set; }

        public string SqlPage { get;private set; }

        public string SqlCount { get;private set; }

        public string SqlOrderBy { get;private set; }

    }

}