using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;



namespace FangYar.OracleDAL
{
    public class CommonDAL : IDAL.CommomIDAL
    {
         
        #region 公共方法

        /// <summary>
        /// 获取记录数
        /// </summary>
        public int Count(string table,string strwhere)
        {
            string sql = "select count(1) from " + table ;
            if (strwhere != null && strwhere != "")
            {
                sql += " where " + strwhere;
            }
            try
            {

                return FangYar.Common.MySqlHelper.GetCount(sql);

            }
            catch
            {
                return 0;
            }
        }

       
        /// <summary>
        /// 获取列表
        /// </summary>
         
        /// <returns></returns>
        public string GetJsonList(string field, string table, string strwhere)
        {
            return FangYar.Common.JsonHelper.ToJson(GetTableList( field,  table,  strwhere));
        }
        

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableList(string field, string table, string strwhere)
        {
            string sql = "select " + field  + " from " + table;
            if (strwhere != null && strwhere != "")
            {
                sql += " where " + strwhere;
            }
            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableList(string sql)
        {
            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        /// <summary>
        /// 获取列表
        /// </summary>

        /// <returns></returns>
        public string GetJsonMonthCount(string field, string table, string year, string where)
        {
            return FangYar.Common.JsonHelper.ToJson(GetMonthCount(field, table, year, where));
        }
        public DataTable GetMonthCount(string field,string talbe,string year,string where ) {
            string sql = "SELECT IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 1, 1, 0)),0) AS 一月,";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 2, 1, 0)),0) AS 二月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 3, 1, 0)),0) AS 三月, ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 4, 1, 0)),0) AS 四月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 5, 1, 0)),0) AS 五月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 6, 1, 0)),0) AS 六月, ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 7, 1, 0)),0) AS 七月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 8, 1, 0)),0) AS 八月,";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 9, 1, 0)),0) AS 九月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 10, 1, 0)),0) AS 十月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 11, 1, 0)),0) AS 十一月,  ";
            sql += "IFNULL(SUM(DECODE(EXTRACT(MONTH FROM B." + field + "), 12, 1, 0)),0) AS 十二月  ";
            sql += " FROM " + talbe + " B WHERE ";

            if (!string.IsNullOrEmpty(where)) {
                sql += where+" and " ;
            }
            sql += "  EXTRACT(YEAR FROM B." + field + ") = '" + year + "' ";
            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }


        #endregion

        #region 分页

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string table, string strwhere)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, table, strwhere));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string table, string strwhere, string order)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, table, strwhere, order));
        }
        
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string table, string strwhere)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, table, strwhere);
        }

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="strwhere">排序</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string table, string strwhere, string order)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, table, strwhere, order);
        }
        
        #endregion
   
    }
}