using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Model;
using SysCore.DAL;
using SysCore.Control;
using SysReport;
using SysReport.Model;
using SysReport.DAL;

public partial class System_XReport_ViewReport_Grid : System.Web.UI.Page
{
    public Hashtable xview = new Hashtable();
    AWGridFun aw = new AWGridFun();
    XReport xr;
    string field_for_export = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            CreateGrid();
        }
    }
    /// <summary>
    /// 绑定控件数据源
    /// </summary>
    public void CreateGrid()
    {
        
        XReportDAL dal = new XReportDAL();
        string module_code = Request["module_code"];
        int xr_id = dal.GetIdByCode(module_code);
        xr = dal.Get(xr_id);
        try
        {
            if (!xr.temp_sql.Trim().Equals(""))
            {
                new SqlUtil().ExecuteNonQuery(xr.temp_sql, null);
            }
        }
        catch (Exception err)
        {
            Response.Write("临表SQL出错！<br/>" + err.Message);
            Response.End();
        }
        Init_Sql_List();
        //设置标题与样式
        //ModuleFormatDesigner mfd = new ModuleFormatDesigner();
        string sql_select_field = "";//SQL所需选择列
        string module = "System_XReport_ViewReport";//模块
        string fields_index = "";//列索引
        //mfd.CreateTitlesAndStyles(module, ref aw, ref sql_select_field, ref fields_index);
        aw.SetColumn("行号", "width:60px;");
        string last_row_info = "0,";//设置哪些列要小计,第一列是序号列
        //设置标题与样式
        string column_names = xr.column_names;
        string[] arr_column_names = column_names.Split(',');
        foreach (string column_name in arr_column_names)
        {
            string[] arr_column_name = column_name.Split('|');
            string title = "";
            string css = "";
            string sum = "";
            try
            {
                title = arr_column_name[0];//标题
            }
            catch { }
            try
            {
                css = arr_column_name[1];//CSS样式
            }
            catch { }
            try
            {
                sum = arr_column_name[2];//是否合计
                if (sum.Equals("SUM"))
                {
                    last_row_info += "1,";
                }
                else
                {
                    last_row_info += "0,";
                }
            }
            catch
            {
                last_row_info += "0,";
            }
            aw.SetColumn(title, css); //为Grid添加列
        }
        //小计设置去除最后个逗号
        last_row_info = last_row_info.Substring(0,last_row_info.Length - 1);
        //Response.Write(last_row_info);
        //Response.End();
        xview.Add("fields_index", fields_index);
        //把样式传递到页面
        xview.Add("aw_column_style",aw.GetStyles());
        int page = 1;//当前页
        if (Request.QueryString["page"] != null)
            page = Convert.ToInt32(Request.QueryString["page"]);
        int page_size = aw.GetDefaultPageSize();//每页显示条数
        if (Request.QueryString["page_size"] != null)
            page_size = Convert.ToInt32(Request.QueryString["page_size"]);
        
        Hashtable ht = new Hashtable();
        //排序
        string order_field = "order by " + xr.order_field;
        ht.Add("order_field", order_field);
        ht.Add("page", page);
        ht.Add("page_size", page_size);
        //查询条件
        string sql_end = "";
        if (!Request["keyword"].Equals(""))
        {
            //要搜索关键字
            string keyword = Request["keyword"].ToString();
            //要搜索的字段
            string search_fields = xr.keywords;
            sql_end = FastSearh.MakeSearchSqlForWhere(keyword, search_fields);
        }

        if (!xr.date1.Equals(""))
        {
            string[] arr_date1 = xr.date1.Split('|');
            if (arr_date1[1].Equals("Y"))
            {
                if (!Request["date1"].Equals(""))
                {
                    sql_end += " and new_date>='" + Request["date1"] + "'";
                }
            }
        }
        if (!xr.date2.Equals(""))
        {
            string[] arr_date2 = xr.date2.Split('|');
            if (arr_date2[1].Equals("Y"))
            {
                if (!Request["date2"].Equals(""))
                {
                    sql_end += " and new_date<='" + Request["date2"] + "'";
                }
            }
        }
        if (!xr.textbox1.Equals(""))
        {
            string[] arr_textbox1 = xr.textbox1.Split('|');
            if (arr_textbox1[1].Equals("Y"))
            {
                if (!Request["textbox1"].Equals(""))
                {
                    sql_end += " and textbox1='" + Request["textbox1"] + "'";
                }
            }
        }
        if (!xr.textbox2.Equals(""))
        {
            string[] arr_textbox2 = xr.textbox2.Split('|');
            if (arr_textbox2[1].Equals("Y"))
            {
                if (!Request["textbox2"].Equals(""))
                {
                    sql_end += " and textbox2='" + Request["textbox2"] + "'";
                }
            }
        }
        string filters = xr.filters;
        if (filters != null && !filters.Equals(""))
        {
            foreach (string code in filters.Split(','))
            {
                if (!Request[code].Equals(""))
                {
                    sql_end += " and " + code + "='" + Request[code] + "'";
                }
            }
        }

        ht.Add("sql_end", sql_end);
        //查询后返回DataTable
        DataTable dt = GetList(ht, sql_select_field);
        //把sql放入session中供打印用
        string sql_print_detail_session_id = "sql_print_detail_" + module;
        Session[sql_print_detail_session_id] = sql_print;
        xview.Add("sql_print_detail_session_id", sql_print_detail_session_id);
        string[] arr_fields = field_for_export.Split(',');
        string new_field_for_export = "";
        //供导出Excel用
        for(int i=0;i<arr_column_names.Length;i++)
        {
            string[] arr_column_name = arr_column_names[i].Split('|');
            string title = "";
            title = arr_column_name[0];//标题
            new_field_for_export += arr_fields[i] + " as [" + title + "],";

        }
        new_field_for_export = new_field_for_export.Substring(0, new_field_for_export.Length - 1);
        Session[sql_print_detail_session_id + "_export_field"] = new_field_for_export;
        //得到总记录数为分页提供依据
        int rows_count = GetList_TotalNumbers(ht);
        //开启合计行功能
        aw.to_sum_row = true;
        //从模板读出哪些列需要合计
        aw.last_row_info = last_row_info;
        try
        {
            string aw_grid_info = aw.MakeGridObject(dt, rows_count, page_size, page);
            //把控件参数与数据源传递到页面
            xview.Add("aw_grid_info", aw_grid_info);
        }
        catch (Exception err)
        {
            string info = err.Message;
            Response.Write("生成表格出错，请检查是否标题列与SQL查询结果列的数目不一致！<br/>详细信息：" + err.Message);
            Response.End();
        }
    }
    //打印用的sql
    public string sql_print = "";
    //列表查询用的sql
    string sql_list = "";
    public void Init_Sql_List()
    {
        sql_list = xr.report_sql;
        if (!xr.date1.Equals(""))
        {
            sql_list = sql_list.Replace("@date1", xr.date1);
        }
        if (!xr.date2.Equals(""))
        {
            sql_list = sql_list.Replace("@date2", xr.date2);
        }
        if (!xr.textbox1.Equals(""))
        {
            sql_list = sql_list.Replace("@textbox1", xr.textbox1);
        }
        if (!xr.textbox2.Equals(""))
        {
            sql_list = sql_list.Replace("@textbox2", xr.textbox2);
        }
    }
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,* 
                from(@sql_list)t where 1=1 ";
        sql = sql.Replace("@sql_list", sql_list)
                 .Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);
        sql_print = "select top 1000000000 * from (" + sql_list + ") t where 1=1 " + pg["sql_end"] + " " + pg["order_field"].ToString();
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        if (Request["to_debug"] != null)
        {
            if (Request["to_debug"].Equals("1"))
            {
                string debug_box = "<textarea style='width:100%;height:500px'>" + sql + "</textarea>";
                Response.Write(debug_box);
                Response.End();
            }
        }
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        string column_names = "";
        for (int i = 1; i < dt.Columns.Count; i++)
        {
            string column_name = dt.Columns[i].ColumnName;
            column_names += column_name + ",";
        }
        field_for_export = column_names.Substring(0, column_names.Length - 1);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from (" + sql_list + ")t where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }


}
