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_IOTypeSummary_Grid : System.Web.UI.Page
{
    public Hashtable xview = new Hashtable();
    AWGridFun aw = new AWGridFun();
    public ConfigReader cfg = new ConfigReader();
    string sql_field = "";
    string sql_field_qc = "";
    string sql_field_sum = "";
    string sql_where = "";
    string free1_title;
    string free2_title;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            CreateGrid();
        }
    }
    /// <summary>
    /// 绑定控件数据源
    /// </summary>
    public void CreateGrid()
    {
        //设置标题与样式
        string sql_select_field = "";//SQL所需选择列
 
        string module = "Stock_Report_IOTypeSummary";//模块
        string fields_index = "";//列索引
        xview.Add("fields_index", fields_index);
        string sql_it = "select * from Base_IOType";
        if (!Request["layer"].Equals(""))
        {
            sql_it += " where layer='" + Request["layer"] + "'";
        }
        sql_it += " order by iot_flag desc,iot_code asc";

        free1_title = cfg.Free1Title();
        free2_title = cfg.Free2Title();
        if (free1_title.Equals(""))
        {
            free1_title = "自由项1";
        }
        if (free2_title.Equals(""))
        {
            free2_title = "自由项2";
        }
        //按自由项汇总
        string group_by_free = Request["group_by_free"];

        sql_select_field = "分类名称,存货ID,存货编码,存货名称,期初数量,@it_select_field"
                         + "结存数量,规格型号,单位,";

        string group_by_warehouse = Request["group_by_warehouse"];
        if (group_by_warehouse.Equals("1"))
        {
            sql_select_field = "仓库名称," + sql_select_field ;
        }
        if(group_by_free.Equals("1")){
                sql_select_field += free1_title + "," + free2_title + ",批次,";
        }
        sql_select_field += "sdefine1,sdefine2,sdefine3,sdefine4,sdefine5";
        //Response.Write(sql_select_field);
        
        SqlUtil su = new SqlUtil();
        DataTable dt_it = su.GetDataTable(sql_it, null);

        //设置标题与样式
        aw.SetColumn("行号", "width:70px;display:none");
        if (group_by_warehouse.Equals("1"))
        {
            aw.SetColumn("仓库名称", "width:70px;");
        }
        aw.SetColumn("存货分类", "width:70px;");
        aw.SetColumn("存货ID", "width:100px;display:none");
        aw.SetColumn("存货编码", "width:100px;");
        aw.SetColumn("存货名称", "width:100px;");
        aw.SetColumn("期初数量", "width:80px;");

        string it_select_field = "";
        foreach (DataRow dr in dt_it.Rows)
        {
            string iot_id = dr["iot_id"].ToString();
            string iot_code = dr["iot_code"].ToString();
            string iot_name = dr["iot_name"].ToString();
            aw.SetColumn(iot_name + "数量", "width:100px;text-align:right");
            sql_field += "case when isnull(iot_code,0) like '" + iot_code + "%' then quantity else 0 end as [" + iot_name + "数量],";
            sql_field_qc += "0 as [" + iot_name + "数量],";
            sql_field_sum += "sum([" + iot_name + "数量]) as [" + iot_name + "数量],";
            it_select_field += "[" + iot_name + "数量],";
        }
        sql_select_field = sql_select_field.Replace("@it_select_field", it_select_field);
        aw.SetColumn("结存数量", "width:80px;");
        aw.SetColumn("规格型号", "width:100px;");
        aw.SetColumn("单位", "width:50px;");

        if (group_by_free.Equals("1"))
        {
            if (free1_title.Equals("自由项1"))
            {
                aw.SetColumn(free1_title, "width:60px;display:none;");
            }
            else
            {
                aw.SetColumn(free1_title, "width:60px;");
            }
            if (free2_title.Equals("自由项2"))
            {
                aw.SetColumn(free2_title, "width:60px;display:none;");
            }
            else
            {
                aw.SetColumn(free2_title, "width:60px;");
            }
            aw.SetColumn("批次", "width:60px;");
        }

        
        

        //把样式传递到页面
        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 仓库名称,存货编码 asc";
        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 = @"分类名称,
                                存货编码,存货名称,规格型号,单位,
                                sdefine1,sdefine2,sdefine3,sdefine4,sdefine5";
            sql_end = FastSearh.MakeSearchSqlForWhere(keyword, search_fields);
        }

        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_where += " and 仓库ID in (" + Request["wh_id"] + ")";
        }
        if (!Request["ic_code"].Equals(""))
        {
            sql_where += " and ic.ic_code like '" + Request["ic_code"] + "'";
        }
        if (!Request["i_code"].Equals(""))
        {
            sql_where += " and i.i_code like '" + Request["i_code"] + "'";
        }
        if (!Request["i_name"].Equals(""))
        {
            sql_where += " and i.i_name like '%" + Request["i_name"] + "%'";
        }
        if (!Request["standard"].Equals(""))
        {
            sql_where += " and i.standard like '%" + Request["standard"] + "%'";
        }
        if (!Request["free1"].Equals(""))
        {
            sql_where += " and 自由项1 like '%" + Request["free1"] + "%'";
        }
        if (!Request["free2"].Equals(""))
        {
            sql_where += " and 自由项2 like '%" + Request["free2"] + "%'";
        }
        if (!Request["batch"].Equals(""))
        {
            sql_where += " and 批次 like '%" + Request["batch"] + "%'";
        }
        //当前用户是否管理员
        bool is_admin = new AuthUI().CheckLoginUserIsAdmin();
        if (!is_admin)
        {
            //值为1代表启用仓库权限控制
            string system_wh_auth = new ConfigReader().GetConfig("system_wh_auth");
            if (system_wh_auth.Equals("1"))
            {
                sql_where += " and 仓库ID in (" + new AuthEx().GetSqlForMyWarehouse() + ")";
            }
        }

        string sql_quan_area = "";
        //'结存数量>0
        if(Request["quan_DaYu_0"].Equals("1"))
        {
          sql_quan_area = sql_quan_area + " or isnull(结存数量,0)>0 ";
        }
        //结存数量=0
        if(Request["quan_DengYu_0"].Equals("1"))
        {
          sql_quan_area = sql_quan_area + " or isnull(结存数量,0)=0 ";
        }
        //结存数量<0
        if(Request["quan_XiaoYu_0"].Equals("1"))
        {
          sql_quan_area = sql_quan_area + " or isnull(结存数量,0)<0 ";
        }
        sql_end += " and (1=2 " + sql_quan_area + ") ";

        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);
        //供导出Excel用
        string field_for_export = sql_select_field;
        field_for_export = field_for_export.Replace("存货ID,", "")
                            .Replace("自由项1,", "")
                            .Replace("自由项2,", "");
        Session[sql_print_detail_session_id + "_export_field"] = field_for_export;
        //得到总记录数为分页提供依据
        int rows_count = GetList_TotalNumbers(ht);
        //开启合计行功能
        aw.to_sum_row = false;
        //从模板读出哪些列需要合计
        //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
        set @begin_date='2014-05-11'
        set @end_date='2014-07-31'
    */
    //列表查询用的sql
    string sql_list = @"
        select top 1000000000 wh.wh_id as 仓库ID,wh.wh_name as 仓库名称,
        ic.ic_code as 分类编码,ic.ic_name as 分类名称,
        t.存货ID,i.i_code as 存货编码,i.i_name as 存货名称,
        i.standard as 规格型号,i.munit as 单位,i.aunit as 辅计单位,
        t.自由项1,t.自由项2,t.批次,
        sum(t.期初数量) as 期初数量,
        @sql_field_sum,
        sum(t.入库数量) as 入库数量,sum(t.出库数量) as 出库数量,
        isnull(sum(t.期初数量),0)+isnull(sum(t.入库数量),0)-isnull(sum(t.出库数量),0) as 结存数量,
        i.sdefine1,i.sdefine2,i.sdefine3,i.sdefine4,i.sdefine5,
        i.ndefine1,i.ndefine2,i.ndefine3,i.ndefine4,i.ndefine5
        from 
        (
	        select d.d_wh_id as 仓库ID,d.i_id as 存货ID,
            isnull(d.free1,'') as 自由项1,isnull(d.free2,'') as 自由项2,isnull(d.batch,'') as 批次,
	        case when t.io_flag=1 then isnull(d.quantity,0) else isnull(-d.quantity,0) end as 期初数量,
            @sql_field_qc
	        0 as 入库数量,0 as 出库数量
	        from Stock_InOuts d
	        left join Stock_InOut t on d.io_id=t.io_id
	        where isnull(t.verify_user,'')<>'' and t.new_date<@begin_date
	        union all
	        select d.d_wh_id,d.i_id,isnull(d.free1,'') as free1,
	        isnull(d.free2,'') as free2,isnull(d.batch,'') as batch,
	        0 as 期初数量,
            @sql_field
	        case when t.io_flag=1 then d.quantity end as 入库数量,
	        case when t.io_flag=0 then d.quantity end as 出库数量

	        from Stock_InOuts d
	        left join Stock_InOut t on d.io_id=t.io_id
            left join Base_IOType iot on t.iot_id=iot.iot_id
	        where isnull(t.verify_user,'')<>'' 
	        and t.new_date >= @begin_date and t.new_date <= @end_date
        )as t
        left join Base_Inventory i on t.存货ID=i.i_id
        left join Base_InventoryClass ic on i.ic_id=ic.ic_id
        left join Base_Warehouse wh on t.仓库ID=wh.wh_id
        where 1=1 @sql_where
        group by wh.wh_id,wh.wh_name,ic.ic_id,ic.ic_code,ic.ic_name,
        t.存货ID,i.i_code,i.i_name,t.自由项1,t.自由项2,t.批次,i.i_name,i.standard,
        i.munit,i.aunit,i.sdefine1,i.sdefine2,i.sdefine3,i.sdefine4,i.sdefine5,
        i.ndefine1,i.ndefine2,i.ndefine3,i.ndefine4,i.ndefine5
        ";
    /// <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();
        sql_list = sql_list.Replace("@sql_where", sql_where);
        sql_list = sql_list.Replace("@sql_field_qc", sql_field_qc)
                 .Replace("@sql_field_sum,", sql_field_sum)
                 .Replace("@sql_field", sql_field);

        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);

        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        sql_print = "select top 1000000000 * from (" + sql_list + ") t where 1=1 " + pg["sql_end"] + " "                   + pg["order_field"].ToString();

        sql = replace_sql(sql);
        sql_print = replace_sql(sql_print);
        //Response.Write(sql);
        //Response.End();
        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>
    /// 替换sql关键字，实现按需汇总
    /// </summary>
    /// <param name="sql">原始sql</param>
    /// <returns>新sql</returns>
    string replace_sql(string sql)
    {
        string group_by_warehouse = Request["group_by_warehouse"];
        if (group_by_warehouse.Equals("0"))
        {
            sql = sql.Replace("wh.wh_id as 仓库ID,wh.wh_name as 仓库名称,", "")
                .Replace("wh.wh_id,wh.wh_name,", "")
                .Replace("仓库名称,", "");
        }

        string group_by_free = Request["group_by_free"];
        if (group_by_free.Equals("0"))
        {
            sql = sql.Replace("t.free1,t.free2,t.batch,", "")
                .Replace("自由项1,自由项2,批次,", "")
                .Replace("t.自由项1,t.自由项2,t.批次,", "")
                .Replace(free1_title + "," + free2_title + ",批次,", "");
        }
        else
        {
            if (!free1_title.Equals(""))
            {
                sql = sql.Replace("自由项1", free1_title);
            }
            if (!free2_title.Equals(""))
            {
                sql = sql.Replace("自由项2", free2_title);
            }
        }
        return sql;
    }
}
