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;
public partial class Ling_BanZu_Grid : System.Web.UI.Page
{
    public Hashtable xview = new Hashtable();
    AWGridFun aw = new AWGridFun();
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            CreateGrid();
        }
    }
    /// <summary>
    /// 绑定控件数据源
    /// </summary>
    public void CreateGrid()
    {
        //设置标题与样式
        ModuleFormatDesigner mfd = new ModuleFormatDesigner();
        string sql_select_field = "";//SQL所需选择列
        string module = "Ling_BanZu_List";//模块
        string fields_index = "";//列索引
        mfd.CreateTitlesAndStyles(module, ref aw, ref sql_select_field, ref fields_index);
        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();//每页显示条数
        page_size = 300;//每页显示条数
        if (Request.QueryString["page_size"] != null)
            page_size = Convert.ToInt32(Request.QueryString["page_size"]);
        
        Hashtable ht = new Hashtable();
        //排序
        string sort = Request.QueryString["sort"];
        string order_field = "order by ele_name " + sort;
        ht.Add("order_field", order_field);
        ht.Add("page", page);
        ht.Add("page_size", page_size);
        //查询条件
        string sql_end = "";

        if (!Request["keyword"].Equals(""))
        {
            if (!Request["keyword"].Equals(""))
            {
                string keyword = Request["keyword"].ToString();
                sql_end = FastSearh.MakeSearchSqlForWhere(keyword, "cus_name,ele_name,cst_name,cellphone");
            }
        }

        if (!Request["cus_name"].Equals(""))
        {
            if (Request.QueryString["cus_fit"].Equals("1"))
            {
                sql_end += " and cus_name='" + Request["cus_name"] + "'";
            }
            else
            {
                sql_end += " and cus_name like '%" + Request["cus_name"] + "%'";
            }
        }

        if (!Request["ele_name"].Equals(""))
        {
            sql_end += " and ele_name like '" + Request["ele_name"] + "%'";
        }
        if (!Request["cst_name"].Equals(""))
        {
            sql_end += " and cst_name like '" + Request["cst_name"] + "%'";
        }
        if (Request["jie_qing"].Equals("0"))
        {
            sql_end += " and round(isnull(qian_kuan,0),2)<>0 ";
        }
        if (Request["jie_qing"].Equals("1"))
        {
            sql_end += " and round(isnull(qian_kuan,0),2)=0 ";
        }
        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_BanZu";
        Session[sql_print_detail_session_id] = sql_print;
        //Response.Write(dal.sql_print);
        xview.Add("sql_print_detail_session_id", sql_print_detail_session_id);
        //得到总记录数为分页提供依据
        int rows_count = GetList_TotalNumbers(ht);
        //开启合计行功能
        aw.to_sum_row = true;
        //从模板读出哪些列需要合计
        aw.last_row_info = mfd.last_row_info;
        string aw_grid_info = aw.MakeGridObject(dt, rows_count, page_size, page);
        //把控件参数与数据源传递到页面
        xview.Add("aw_grid_info", aw_grid_info); 
    }
    //打印用的sql
    public string sql_print = "";
    /*
                declare @year as int
                set @year=2014
     */
    string sql_list = @"
                select a.ele_id,ele.ele_name,ele.cellphone,a.cus_id,cus.cus_name,a.cst_id,cst.cst_name,
                isnull(p_yf.yf_kuan_he_ji,0)-isnull(p_sf.sf_kuan_he_ji,0) as jiu_kuan,
                yf.yf1,sf.m1,yf.yf2,sf.m2,yf.yf3,sf.m3,yf.yf4,sf.m4,yf.yf5,sf.m5,yf.yf6,sf.m6,yf.yf7,sf.m7,
                yf.yf8,sf.m8,yf.yf9,sf.m9,yf.yf10,sf.m10,yf.yf11,sf.m11,yf.yf12,sf.m12,
                isnull(p_yf.yf_kuan_he_ji,0)-isnull(p_sf.sf_kuan_he_ji,0)+isnull(yf.yf_kuan_he_ji,0) as yf_kuan_he_ji,
                sf.sf_kuan_he_ji,
                isnull(yf.yf_kuan_he_ji,0) as bn_yf_kuan_he_ji,
                isnull(p_yf.yf_kuan_he_ji,0)-isnull(p_sf.sf_kuan_he_ji,0)
                +isnull(yf.yf_kuan_he_ji,0)-isnull(sf.sf_kuan_he_ji,0) as qian_kuan
                from
                (
	                SELECT ele_id,cus_id,max(cst_id) as cst_id
	                FROM
	                (
		                select ele_id,cus_id,cst_id
		                from Ling_DayAccount
		                where isnull(ele_id,'')<>''
		                and direction='支出'
		                union all
		                select ele_id,cus_id,cst_id
		                from Ling_YingFu
		                where isnull(ele_id,'')<>''
	                ) t 
	                group by ele_id,cus_id
                )a 
                left join Base_Customer cus on cus.cus_id=a.cus_id
                left join Base_Employee ele on ele.ele_id=a.ele_id
                left join Base_Department dep on ele.dep_id=dep.dep_id
                left join Base_Cost cst on cst.cst_id=a.cst_id
                left join
                (
	                SELECT ele_id,cus_id,cst_id,
	                sum(m1) as m1,sum(m2) as m2,sum(m3) as m3,sum(m4) as m4,sum(m5) as m5,sum(m6) as m6,
	                sum(m7) as m7,sum(m8) as m8,sum(m9) as m9,sum(m10) as m10,sum(m11) as m11,sum(m12) as m12,
	                sum(sf_kuan_he_ji) as sf_kuan_he_ji
	                FROM(
		                select da.ele_id,da.cus_id,da.cst_id,
		                case when month(da.add_date)=1 then da.amount else 0 end as m1,
		                case when month(da.add_date)=2 then da.amount else 0 end as m2,
		                case when month(da.add_date)=3 then da.amount else 0 end as m3,
		                case when month(da.add_date)=4 then da.amount else 0 end as m4,
		                case when month(da.add_date)=5 then da.amount else 0 end as m5,
		                case when month(da.add_date)=6 then da.amount else 0 end as m6,
		                case when month(da.add_date)=7 then da.amount else 0 end as m7,
		                case when month(da.add_date)=8 then da.amount else 0 end as m8,
		                case when month(da.add_date)=9 then da.amount else 0 end as m9,
		                case when month(da.add_date)=10 then da.amount else 0 end as m10,
		                case when month(da.add_date)=11 then da.amount else 0 end as m11,
		                case when month(da.add_date)=12 then da.amount else 0 end as m12,
		                da.amount as sf_kuan_he_ji
		                from Ling_DayAccount da
		                where isnull(da.ele_id,'')<>''
		                and da.direction='支出'
		                and year(da.add_date)=@year
	                )t
	                group by ele_id,cus_id,cst_id
                ) as sf on sf.cus_id=a.cus_id and sf.ele_id=a.ele_id 
                and sf.cst_id=a.cst_id
                left join
                (
	                SELECT cus_id,ele_id,sum(yf_kuan_he_ji) as yf_kuan_he_ji,
	                sum(yf1) yf1,sum(yf2) yf2,sum(yf3) yf3,sum(yf4) yf4,sum(yf5) yf5,
	                sum(yf6) yf6,sum(yf7) yf7,sum(yf8) yf8,sum(yf9) yf9,sum(yf10) yf10,
	                sum(yf11) yf11,sum(yf12) yf12
                    --,cst_id
	                FROM(
		                select amount as yf_kuan_he_ji,
		                cus_id,ele_id,cst_id,
		                case when month(add_date)=1 then amount else 0 end as yf1,
		                case when month(add_date)=2 then amount else 0 end as yf2,
		                case when month(add_date)=3 then amount else 0 end as yf3,
		                case when month(add_date)=4 then amount else 0 end as yf4,
		                case when month(add_date)=5 then amount else 0 end as yf5,
		                case when month(add_date)=6 then amount else 0 end as yf6,
		                case when month(add_date)=7 then amount else 0 end as yf7,
		                case when month(add_date)=8 then amount else 0 end as yf8,
		                case when month(add_date)=9 then amount else 0 end as yf9,
		                case when month(add_date)=10 then amount else 0 end as yf10,
		                case when month(add_date)=11 then amount else 0 end as yf11,
		                case when month(add_date)=12 then amount else 0 end as yf12
		                from Ling_YingFu
		                where isnull(ele_id,'')<>''
		                and year(add_date)=@year
	                )t
	                group by cus_id,ele_id
                    --,cst_id
                ) as yf on a.cus_id=yf.cus_id and a.ele_id=yf.ele_id 
                --and a.cst_id=yf.cst_id
                left join
                (
	                select cus_id,cst_id,ele_id,
	                sum(amount) as sf_kuan_he_ji
	                from Ling_DayAccount
	                where isnull(ele_id,'')<>''
	                and direction='支出'
	                and year(add_date)<@year
	                group by cus_id,cst_id,ele_id
                )p_sf on p_sf.cus_id=a.cus_id and p_sf.ele_id=a.ele_id 
                and p_sf.cst_id=a.cst_id
                left join
                (
	                select cus_id,ele_id,
	                sum(amount) as yf_kuan_he_ji
	                from Ling_YingFu
	                where isnull(ele_id,'')<>''
	                and year(add_date)<@year
	                group by cus_id,ele_id
                )p_yf on a.cus_id=p_yf.cus_id and a.ele_id=p_yf.ele_id 
                --and a.cst_id=p_yf.cst_id 
                where dep.dep_name='工地'
";
    /// <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,@sql_select_field from(" + sql_list + ")t where 1=1 ";

        sql = sql.Replace("@order_field", order_field)
                 .Replace("@year", Request.QueryString["year"])
                 .Replace("@sql_select_field", sql_select_field);

        sql_print = " select * from (" + sql_list + ")t where 1=1 " + pg["sql_end"] + " " +  pg["order_field"].ToString();
        sql_print = sql_print.Replace("@year", Request.QueryString["year"]);
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = sql_list.Replace("@year", Request.QueryString["year"]);
        sql = "select count(1) from (" + sql + ")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;
    }
}
