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 Stock_Report_InventoryBook_Flow_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 = "Stock_Report_InventoryBook_Flow";//模块
        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();//每页显示条数
        if (Request.QueryString["page_size"] != null)
            page_size = Convert.ToInt32(Request.QueryString["page_size"]);
        
        Hashtable ht = new Hashtable();
        //排序
        string order_field = "order by new_date,io_id";
        ht.Add("order_field", order_field);
        ht.Add("page", page);
        ht.Add("page_size", page_size);
        //查询条件
        string sql_end = "";

        if (!Request["begin_date"].Equals(""))
        {
            sql_list = sql_list.Replace("@begin_date", "'" + Request["begin_date"] + "'");
        }
        if (!Request["end_date"].Equals(""))
        {
            sql_list = sql_list.Replace("@end_date", "'" + Request["end_date"] + "'");
        }


        //查仓库
        if (!Request["wh_id"].Equals(""))
        {
            sql_list = sql_list.Replace("@wh_id", "'" + Request["wh_id"] + "'");
        }
        if (!Request["i_id"].Equals(""))
        {
            sql_list = sql_list.Replace("@i_id", "'" + Request["i_id"] + "'");
        }
        if (!Request["free1"].Equals(""))
        {
            sql_end += "and free1 = '" + Request["free1"] + "'";
        }
        if (!Request["free2"].Equals(""))
        {
            sql_end += "and free2 = '" + Request["free2"] + "'";
        }
        if (!Request["batch"].Equals(""))
        {
            sql_end += "and batch = '" + Request["batch"] + "'";
        }

        ht.Add("sql_end", sql_end);
        //查询后返回DataTable
        DataTable dt = GetList(ht, sql_select_field);
        //生面结存数
        dt = ComputeDataTable(dt);
        //把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);
        //供导出Excel用
        string field_for_export = mfd.field_for_export;
        Session[sql_print_detail_session_id + "_export_field"] = field_for_export;
        //得到总记录数为分页提供依据
        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 @begin_date datetime,@end_date datetime,@wh_id int,@i_id int
            set @begin_date='2014-06-01'
            set @end_date='2014-12-09'
            set @wh_id=2
            set @i_id=4
    */
    //列表查询用的sql
    string sql_list = @"
            select top 1000000000
                wh.wh_name,t.flag,t.new_date,t.io_id,t.单号 as order_number,t.order_type,t.单据类型 as ot_name,
	            t.free1,t.free2,t.batch,
	            isnull(t.入库数量,0) as rk_sl,isnull(t.出库数量,0) as ck_sl,cast(t.结存数量 as float) as jc_sl,
                isnull(t.入库件数,0) as rk_js,isnull(t.出库件数,0) as ck_js,cast(t.结存件数 as float) as jc_js,
	            isnull(t.入库金额,0) as rk_je,isnull(t.出库金额,0) as ck_je,cast(t.结存金额 as float) as jc_je
            from
            (
	            select '期初' as flag,NULL as new_date,
	            '' as io_id,'' as 单号,'' as order_type,'' as 单据类型,
				'' wh_id,isnull(d.free1,'') as free1,isnull(d.free2,'') as free2,isnull(d.batch,'') as batch,
	            sum(case when t.io_flag=1 then d.quantity else 0 end) as 入库数量,
	            sum(case when t.io_flag=0 then d.quantity else 0 end) as 出库数量,
	            NULL as 结存数量,
	            sum(case when t.io_flag=1 then d.num else 0 end) as 入库件数,
	            sum(case when t.io_flag=0 then d.num else 0 end) as 出库件数,
	            NULL as 结存件数,
	            sum(case when t.io_flag=1 then d.total_price else 0 end) as 入库金额,
	            sum(case when t.io_flag=0 then d.total_price else 0 end) as 出库金额,
	            NULL as 结存金额
	            from Stock_InOuts d
	            left join Stock_InOut t on t.io_id=d.io_id
	            where isnull(t.verify_user,'')<>'' and t.new_date<@begin_date
	            and d.d_wh_id=@wh_id and d.i_id=@i_id
	            group by d.d_wh_id,d.i_id,isnull(d.free1,''),isnull(d.free2,''),isnull(d.batch,'')
	            union all
	            select  top 100000000 '' as flag,convert(char(10),t.new_date,23) as new_date,
	            t.io_id,t.order_number as 单号,t.order_type,ot.ot_name as 单据类型,
	            d.d_wh_id as wh_id,isnull(d.free1,'') as free1,isnull(d.free2,'') as free2,isnull(d.batch,'') as batch,
	            case when t.io_flag=1 then d.quantity else 0 end as 本期入库数量,
	            case when t.io_flag=0 then d.quantity else 0 end as 本期出库数量,
	            NULL as 结存数量,
	            case when t.io_flag=1 then d.num else 0 end as 本期入库件数,
	            case when t.io_flag=0 then d.num else 0 end as 本期出库件数,
	            NULL as 结存件数,
	            case when t.io_flag=1 then d.total_price else 0 end as 本期入库金额,
	            case when t.io_flag=0 then d.total_price else 0 end as 本期出库金额,
	            NULL as 结存金额
	            from Stock_InOuts d
	            left join Stock_InOut t on t.io_id=d.io_id
	            left join Base_OrderType ot on ot.ot_code=t.order_type
	            where t.new_date>=@begin_date and t.new_date<=@end_date and isnull(t.verify_user,'')<>''
	            and d.d_wh_id=@wh_id and d.i_id=@i_id
            ) as t left join Base_Warehouse wh on wh.wh_id=t.wh_id
            order by t.new_date,t.io_id
            ";
    /// <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("@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
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        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;
    }
    /// <summary>
    /// 为DataTable计划结存数
    /// </summary>
    /// <param name="dt"></param>
    DataTable ComputeDataTable(DataTable dt)
    {
        decimal sum_sl = 0;
        decimal rk_sl = 0;
        decimal ck_sl = 0;
        decimal jc_sl = 0;

        decimal sum_js = 0;
        decimal rk_js = 0;
        decimal ck_js = 0;
        decimal jc_js = 0;

        decimal sum_je = 0;
        decimal rk_je = 0;
        decimal ck_je = 0;
        decimal jc_je = 0;
        int row_count = dt.Rows.Count;
        int current_row = 0;
        foreach (DataRow dr in dt.Rows)
        {
            current_row ++;
            rk_sl = 0;
            ck_sl = 0;

            rk_js = 0;
            ck_js = 0;

            rk_je = 0;
            ck_je = 0;
 
            //累加结存数量
            rk_sl = Convert.ToDecimal(dr["rk_sl"].ToString());
            ck_sl = Convert.ToDecimal(dr["ck_sl"].ToString());
            //累加结存件数
            rk_js = Convert.ToDecimal(dr["rk_js"].ToString());
            ck_js = Convert.ToDecimal(dr["ck_js"].ToString());
            //累加结存金额
            rk_je = Convert.ToDecimal(dr["rk_je"].ToString());
            ck_je = Convert.ToDecimal(dr["ck_je"].ToString());
 
            jc_sl = rk_sl - ck_sl;
            jc_js = rk_js - ck_js;
            jc_je = rk_je - ck_je;

            sum_sl = sum_sl + jc_sl;
            sum_je = sum_je + jc_je;
            sum_js = sum_js + jc_js; 

            dr["jc_sl"] = sum_sl;            
            dr["jc_js"] = sum_js;
            dr["jc_je"] = sum_je;

        }
        return dt;
    }
}
