RaUI/Source/ryControls/Controls/ctlMyPage.cs

//--------------------------日期:2014-03-18
//--------------------------版本:2.0.5.0
//--------------------------作者:itrycn
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
#pragma warning disable IDE1006 // 命名样式
namespace MyPage
{
    /// <summary>
    /// 分页控件
    /// </summary>
    [DefaultEvent("OnPageChange")]
    [Description("分页控件")]
    public partial class ctlMyPage : UserControl
    {
        //public delegate void TimeHandler(object sender, TimeInfo e);
        //public delegate void SomeHandler(object sender, DataTable e);
        /// <summary>
        /// 首页文本
        /// </summary>
        [Description("首页文本")]
        public string T_FirstCaption
        {
            get { return LblStartPage.Text; }
            set { LblStartPage.Text = value; ChangePosition(); }
        }
        /// <summary>
        /// 上一页文本
        /// </summary>
        [Description("上一页文本")]
        public string T_PreCaption
        {
            get { return LblPre.Text; }
            set { LblPre.Text = value; ChangePosition(); }
        }
        /// <summary>
        /// 下一页文本
        /// </summary>
        [Description("下一页文本")]
        public string T_NextCaption
        {
            get { return LblNext.Text; }
            set { LblNext.Text = value; ChangePosition(); }
        }
        /// <summary>
        /// 末页文本
        /// </summary>
        [Description("末页文本")]
        public string T_LastCaption
        {
            get { return LblLast.Text; }
            set { LblLast.Text = value; ChangePosition(); }
        }
        /// <summary>
        /// 跳页文本
        /// </summary>
        [Description("跳页文本")]
        public string T_SkipCaption
        {
            get { return label1.Text; }
            set { label1.Text = value; ChangePosition(); }
        }
        private string m_TipCaption = "{pageindex}/{pagecount} 页,共 {recordcount} 条记录,每页 {pagesize} 条";
        /// <summary>
        /// 页面信息文本
        /// </summary>
        [Description("页面信息文本")]
        public string T_TipCaption
        {
            get { return m_TipCaption; }
            set { m_TipCaption = value; ChangePosition(); }
        }
        private object[] m_Parameters;
        /// <summary>
        /// 存储SQL参数
        /// </summary>
        [Description("存储SQL参数")]
        public object[] T_Parameters
        {
            get { return m_Parameters; }
            set { m_Parameters = value; }
        }
        private int m_CurrPageIndex = 0; //当前页数
        /// <summary>
        /// 最后一页的页码
        /// </summary>
        [Description("最后一页的页码")]
        public int T_LastPageIndex
        {
            get { return m_PageCount; }
        }
        /// <summary>
        /// 在发生页码变化时激发
        /// </summary>
        [Description("在发生页码变化时激发")]
        public event EventHandler OnPageChange;
        /// <summary>
        /// 在发生页码变化前激发
        /// </summary>
        [Description("在发生页码变化前激发")]
        public event CancelEventHandler OnBeforePageChange;
        private string GetTipCaption(string sText)
        {
            //{pagecount}表示页数,{recordcount}表示记录总数,{pagesize}表示页面大小,{pageindex}
            string tmpText = sText.Replace("{pagecount}", m_PageCount.ToString());
            tmpText = tmpText.Replace("{recordcount}", m_RecordCount.ToString());
            tmpText = tmpText.Replace("{pagesize}", m_PageSize.ToString());
            tmpText = tmpText.Replace("{pageindex}", m_CurrPageIndex.ToString());
            return tmpText;
        }
        private void ChangePosition()
        {
            LblPre.Left = LblStartPage.Left + LblStartPage.Width + 5;
            LblNext.Left = LblPre.Left + LblPre.Width + 5;
            LblLast.Left = LblNext.Left + LblNext.Width + 5;
            label1.Left = LblLast.Left + LblLast.Width + 5;
            NumPage.Left = label1.Left + label1.Width + 5;
            lblTip.Left = NumPage.Left + NumPage.Width + 10;
            lblTip.Text = GetTipCaption(m_TipCaption);
        }
        private void ChangeState()
        {
            lblTip.Text = GetTipCaption(m_TipCaption);
            //m_CurrPageIndex + "/" + m_PageCount + " 页,共 " + m_RecordCount + " 条记录,每页 " + m_PageSize + " 条"
            if (m_CurrPageIndex == 1) //如果当前在第一页
            {
                LblStartPage.Enabled = false;
                LblPre.Enabled = false;
                if (m_PageCount > 1) { LblNext.Enabled = true; } else { LblNext.Enabled = false; }
                if (m_PageCount > 1) { LblLast.Enabled = true; } else { LblLast.Enabled = false; }
            }
            else if (m_CurrPageIndex == m_PageCount) //如果当前在最后一页
            {
                LblNext.Enabled = false;
                LblLast.Enabled = false;
                if (m_PageCount > 1) { LblStartPage.Enabled = true; } else { LblStartPage.Enabled = false; }
                if (m_PageCount > 1) { LblPre.Enabled = true; } else { LblPre.Enabled = false; }
            }
            else if (m_CurrPageIndex <= 0) //如果当前没页数
            {
                LblStartPage.Enabled = false;
                LblPre.Enabled = false;
                LblNext.Enabled = false;
                LblLast.Enabled = false;
            }
            else
            {
                LblStartPage.Enabled = true;
                LblPre.Enabled = true;
                LblNext.Enabled = true;
                LblLast.Enabled = true;
            }
        }
        private int m_PageSize = 50;
        /// <summary>
        /// 设置/返回每页显示的量大小
        /// </summary>
        [Description("设置/返回每页显示的量大小")]
        public int PageSize
        {
            get { if (m_PageSize <= 0) { m_PageSize = 50; } return m_PageSize; }
            set
            {
                m_PageSize = value;
                m_PageCount = Convert.ToInt32(Math.Ceiling((float)m_RecordCount / (float)PageSize).ToString());
            }
        }
        private int m_RecordCount = 0;
        private int m_PageCount = 0;
        /// <summary>
        /// 设置/返回记录总数
        /// </summary>
        [Description("设置/返回记录总数")]
        public int RecordCount
        {
            get { return m_RecordCount; }
            set
            {
                m_RecordCount = value;
                m_PageCount = Convert.ToInt32(Math.Ceiling((float)m_RecordCount / (float)PageSize).ToString());
            }
        }
        private string m_SQLText = "";
        /// <summary>
        /// 设置/返回分页SQL语句,{pagecount}表示页数,{recordcount}表示记录总数,{pagesize}表示页面大小,
        /// {pageindex}表示当前页数,{recordnum}表示当前页记录数,{pageendnum}表示当前页最后一条记录的记录数
        /// </summary>
        [Description("设置/返回分页SQL语句,{pagecount}表示页数,{recordcount}表示记录总数,{pagesize}表示页面大小,{pageindex}表示当前页数,{recordnum}表示当前页记录数,{pageendnum}表示当前页最后一条记录的记录数")]
        public string SQLText
        {
            get
            {
                return m_SQLText;
            }
            set { m_SQLText = value; }
        }
        private string m_SQLText2 = "";
        /// <summary>
        /// 设置/返回当{recordnum}小于等于0时的分页SQL语句(比如第一页),如果为空，则默认使用SQLText值。{pagecount}表示页数,
        /// {recordcount}表示记录总数,{pagesize}表示页面大小,{pageindex}表示当前页数,{recordnum}表示当前页记录数,{pageendnum}表示当前页最后一条记录的记录数
        /// </summary>
        [Description("设置/返回当{recordnum}小于等于0时的分页SQL语句,如果为空，则默认使用SQLText值。{pagecount}表示页数,{recordcount}表示记录总数,{pagesize}表示页面大小,{pageindex}表示当前页数,{recordnum}表示当前页记录数,{pageendnum}表示当前页最后一条记录的记录数")]
        public string SQLText2
        {
            get
            {
                return m_SQLText2;
            }
            set { m_SQLText2 = value; }
        }
        /// <summary>
        /// 获取当前页码
        /// </summary>
        /// <returns></returns>
        public int GetCurrPageIndex()
        {
            return m_CurrPageIndex;
        }
        /// <summary>
        /// 设置分页语句
        /// </summary>
        /// <param name="dp">数据库类型</param>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <param name="OrderBySQL">排序语句，必须包含Order By;,如果不存在排序语句，请为空</param>
        /// <returns></returns>
        public int SetPageSQL(ryCommonDb.DataProvider.DataProviderType dp, string tableName, string whereSQL, string OrderBySQL)
        {
            switch(dp)
            {
                case ryCommonDb.DataProvider.DataProviderType.MySqlDataProvider:
                    SetPageSQLByMySQL(tableName, whereSQL, OrderBySQL);
                    return 1;
                case ryCommonDb.DataProvider.DataProviderType.SQLiteDataProvider:
                    SetPageSQLBySQLite(tableName, whereSQL, OrderBySQL);
                    return 1;
                case ryCommonDb.DataProvider.DataProviderType.SqlDataProvider:
                    SetPageSQLByMSSQL(tableName, whereSQL, OrderBySQL);
                    return 1;
                default:
                    return -1;
            }
        }
        /// <summary>
        /// 获取数据库指定条件的记录总数
        /// </summary>
        /// <param name="dp">数据库类型</param>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <returns></returns>
        public string GetPageCountSQL(ryCommonDb.DataProvider.DataProviderType dp, string tableName, string whereSQL)
        {
            switch (dp)
            {
                case ryCommonDb.DataProvider.DataProviderType.MySqlDataProvider:
                    return GetPageCountSQLByMySQL(tableName, whereSQL);
                case ryCommonDb.DataProvider.DataProviderType.SQLiteDataProvider:
                    return GetPageCountSQLBySQLite(tableName, whereSQL);
                case ryCommonDb.DataProvider.DataProviderType.SqlDataProvider:
                    return GetPageCountSQLByMSSQL(tableName, whereSQL,"");
                default:
                    return "";
            }
        }
        /// <summary>
        /// 设置分页语句为MySQL分页语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <param name="OrderBySQL">排序语句，必须包含Order By;,如果不存在排序语句，请为空</param>
        /// <returns></returns>
        public int SetPageSQLByMySQL(string tableName, string whereSQL, string OrderBySQL)
        {
            string m_where = "";
            if (whereSQL.Length>0)
            {
                m_where = " where " + whereSQL;
            }
            string m_order = "";
            if (OrderBySQL.Length > 0)
            {
                m_order = " (" + OrderBySQL + ")";
            }
            m_SQLText = "select * from " + tableName + m_where + m_order + " limit {pagesize} offset {recordnum}";
            m_SQLText2 = "";
            return 1;
        }
        /// <summary>
        /// 获取MySQL数据库指定条件的记录总数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <returns></returns>
        public string GetPageCountSQLByMySQL(string tableName, string whereSQL)
        {
            string m_where = "";
            if (whereSQL.Length > 0)
            {
                m_where = " where " + whereSQL;
            }
            return "select count(*) from " + tableName + m_where;
        }
        /// <summary>
        /// 设置分页语句为MSSQL分页语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <param name="OrderBySQL">排序语句，必须包含Order By;,如果不存在排序语句，请为空</param>
        /// <returns></returns>
        public int SetPageSQLByMSSQL(string tableName, string whereSQL, string OrderBySQL)
        {
            string m_where = "";
            if (whereSQL.Length > 0)
            {
                m_where = " where " + whereSQL;
            }
            string m_order = "(order by id)";
            if (OrderBySQL.Length > 0)
            {
                m_order = "(" + OrderBySQL + ")";
            }
            m_SQLText = "select * from (select *,(ROW_NUMBER() OVER"+m_order+")as myrow from " + tableName + m_where + ") as t where t.myrow between {recordnum1} and {pageendnum1}";
            return 1;
        }
        /// <summary>
        /// 获取MSSQL数据库指定条件的记录总数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <param name="OrderBySQL">排序语句，必须包含Order By;,如果不存在排序语句，请为空</param>
        /// <returns></returns>
        public string GetPageCountSQLByMSSQL(string tableName, string whereSQL, string OrderBySQL)
        {
            string m_where = "";
            if (whereSQL != "")
            {
                m_where = " where " + whereSQL;
            }
            string m_order = "(order by id)";
            if (OrderBySQL != "")
            {
                m_order = "(" + OrderBySQL + ")";
            }
            return "select count(*) from (select *,(ROW_NUMBER() OVER" + m_order + ") as myrow from " + tableName + m_where + ") as t";
        }
        /// <summary>
        /// 设置分页语句为SQLite分页语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,以及不包含排序语句;,如果不存在条件判断语句，请为空</param>
        /// <param name="OrderBySQL">排序语句，必须包含Order By;,如果不存在排序语句，请为空</param>
        /// <returns></returns>
        public int SetPageSQLBySQLite(string tableName, string whereSQL, string OrderBySQL)
        {
            string m_where = "";
            if (whereSQL.Length > 0)
            {
                m_where = " where " + whereSQL;
            }
            string m_order = "";
            if (OrderBySQL.Length > 0)
            {
                m_order = " (" + OrderBySQL + ")";
            }
            m_SQLText = "select * from " + tableName + m_where + m_order + " limit {pagesize} offset {recordnum}";
            m_SQLText2 = "";
            return 1;
        }
        /// <summary>
        /// 设置分页语句为SQLite分页语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,可包含order by,如果不存在条件判断语句，请为空</param>
        /// <returns></returns>
        public int SetPageSQLBySQLite(string tableName, string whereSQL)
        {
            string m_where = "";
            if (whereSQL.Length > 0)
            {
                m_where = " where " + whereSQL;
            }
            m_SQLText = "select * from " + tableName + m_where + " limit {pagesize} offset {recordnum}";
            m_SQLText2 = "";
            return 1;
        }

        /// <summary>
        /// 获取SQLite数据库指定条件的记录总数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSQL">条件判断sql语句，注意不包含where,可包含order by,如果不存在条件判断语句，请为空</param>
        /// <returns></returns>
        public string GetPageCountSQLBySQLite(string tableName, string whereSQL)
        {
            string m_where = "";
            if (whereSQL.Length > 0)
            {
                m_where = " where " + whereSQL;
            }
            return "select count(*) from " + tableName + m_where;
        }
        /// <summary>
        /// 返回分页SQL语句
        /// </summary>
        [Description("返回分页SQL语句")]
        public string GetSQLText
        {
            get
            {
                if (m_CurrPageIndex == 0) { m_CurrPageIndex = 1; }
                int recordnum = m_PageSize * (m_CurrPageIndex - 1);
                if (recordnum < 0) { recordnum = 0; }
                string sxText;
                if ((recordnum > 0) || (m_SQLText2 == ""))
                {
                    sxText = m_SQLText.Replace("{pagecount}", m_PageCount.ToString());
                    sxText = sxText.Replace("{recordcount}", m_RecordCount.ToString());
                    sxText = sxText.Replace("{pagesize}", m_PageSize.ToString());
                    sxText = sxText.Replace("{pageindex}", m_CurrPageIndex.ToString());
                    sxText = sxText.Replace("{recordnum}", recordnum.ToString());
                    sxText = sxText.Replace("{recordnum1}", (recordnum+1).ToString());
                    sxText = sxText.Replace("{pageendnum}", (recordnum + m_PageSize-1).ToString());
                    sxText = sxText.Replace("{pageendnum1}", (recordnum + m_PageSize).ToString());
                    return sxText;
                }
                else
                {
                    
                    sxText = m_SQLText2.Replace("{pagecount}", m_PageCount.ToString());
                    sxText = sxText.Replace("{recordcount}", m_RecordCount.ToString());
                    sxText = sxText.Replace("{pagesize}", m_PageSize.ToString());
                    sxText = sxText.Replace("{pageindex}", m_CurrPageIndex.ToString());
                    sxText = sxText.Replace("{recordnum}", recordnum.ToString());
                    sxText = sxText.Replace("{recordnum1}", (recordnum + 1).ToString());
                    sxText = sxText.Replace("{pageendnum}", (recordnum + m_PageSize-1).ToString());
                    sxText = sxText.Replace("{pageendnum1}", (recordnum + m_PageSize).ToString());
                    return sxText;
                }
            }
        }
        /// <summary>
        /// 分页控件
        /// </summary>
        public ctlMyPage()
        {
            InitializeComponent();
        }

        private void LblStartPage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            GotoFirst();
        }

        private void LblLast_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            GotoLast();
        }

        private void LblPre_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            GotoPrev();
        }

        private void LblNext_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            GotoNext();
        }

        private void NumPage_ValueChanged(object sender, EventArgs e)
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            try
            {
                m_CurrPageIndex = Convert.ToInt32(NumPage.Value);
                if (m_CurrPageIndex > m_PageCount)
                {
                    m_CurrPageIndex = m_PageCount;
                    if (m_PageCount == 0)
                    {
                        NumPage.Value = 1;
                    }
                    else if (m_PageCount > NumPage.Maximum)
                    {
                        NumPage.Value = NumPage.Maximum;
                    }
                    else
                    {
                        NumPage.Value = m_PageCount;
                    }
                }
                else
                {
                    m_CurrPageIndex = Convert.ToInt32(NumPage.Value);
                    OnPageChange?.Invoke(this, new EventArgs());
                }
                ChangeState();
            }
            catch { }
        }
        /// <summary>
        /// 设置当前页为指定页
        /// </summary>
        /// <param name="mPageIndex"></param>
        public void GotoPageIndex(int mPageIndex)
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            if (mPageIndex >= m_PageCount)
            {
                m_CurrPageIndex = m_PageCount;
            }
            else
            {
                m_CurrPageIndex = mPageIndex;
            }
            OnPageChange?.Invoke(this, new EventArgs());
            ChangeState();
        }
        /// <summary>
        /// 下一页
        /// </summary>
        public void GotoNext()
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            if (m_CurrPageIndex >= m_PageCount)
            {
                m_CurrPageIndex = m_PageCount;
            }
            else
            {
                m_CurrPageIndex++;
            }
            OnPageChange?.Invoke(this, new EventArgs());
            ChangeState();
        }
        /// <summary>
        /// 上一页
        /// </summary>
        public void GotoPrev()
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            if (m_CurrPageIndex <= 1)
            {
                m_CurrPageIndex = 1;
            }
            else
            {
                m_CurrPageIndex--;
            }
            OnPageChange?.Invoke(this, new EventArgs());
            ChangeState();
        }
        /// <summary>
        /// 末页
        /// </summary>
        public void GotoLast()
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            if (m_PageCount == 0)
            {
                m_CurrPageIndex = 0;
            }
            else
            {
                m_CurrPageIndex = m_PageCount;
            }
            OnPageChange?.Invoke(this, new EventArgs());
            ChangeState();
        }
        /// <summary>
        /// 首页
        /// </summary>
        public void GotoFirst()
        {
            CancelEventArgs ev = new CancelEventArgs();
            OnBeforePageChange?.Invoke(this, ev);
            if (ev.Cancel) { return; }
            if (m_PageCount == 0)
            {
                m_CurrPageIndex = 0;
            }
            else
            {
                m_CurrPageIndex = 1;
            }
            OnPageChange?.Invoke(this, new EventArgs());
            ChangeState();
        }
        private void CtlMyPage_Resize(object sender, EventArgs e)
        {
            Height = 26;
        }

        private void CtlMyPage_Load(object sender, EventArgs e)
        {
            ChangeState();
        }
    }
}