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 Sale_Report_SalesOut_Details_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 = "Sale_Report_SalesOut_Details";//模块
        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 desc,io_id desc,ios_id 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 = "do_order_number,so_order_number,order_number,i_code,i_name,standard,munit,aunit,dep_name,iot_name,"
                                + "new_user,verify_user,cus_name,st_name,cus_name,ele_name,"
                                + "d_sdefine1,d_sdefine2,d_sdefine3,d_sdefine4,d_sdefine5,"
                                + "sdefine1,sdefine2,sdefine3,sdefine4,sdefine5";

            sql_end = FastSearh.MakeSearchSqlForWhere(keyword, search_fields);
        }
        if (!Request["do_id"].Equals(""))
        {
            sql_end += " and do_id='" + Request["do_id"] + "' ";
        }
        if (!Request["begin_date"].Equals(""))
        {
            sql_end += " and t.new_date>='" + Request["begin_date"] + "'";
        }
        if (!Request["end_date"].Equals(""))
        {
            sql_end += " and t.new_date<='" + Request["end_date"] + "'";
        }
        if (!Request["do_order_number"].Equals(""))
        {
            sql_end += " and do_order_number like '%" + Request["do_order_number"] + "%'";
        }
        if (!Request["so_order_number"].Equals(""))
        {
            sql_end += " and so_order_number like '%" + Request["so_order_number"] + "%'";
        }
        if (Request["is_verify"].Equals("0"))
        {
            sql_end += " and isnull(verify_user,'')='' ";
        }
        if (Request["is_verify"].Equals("1"))
        {
            sql_end += " and isnull(verify_user,'')!='' ";
        }
        //查仓库
        if (!Request["wh_id"].Equals(""))
        {
            sql_end += " and wh_id=" + Request["wh_id"];
        }
        if (!Request["i_code"].Equals(""))
        {
            sql_end += " and i_code like '%" + Request["i_code"] + "%'";
        }
        if (!Request["i_name"].Equals(""))
        {
            sql_end += " and i_name like '%" + Request["i_name"] + "%'";
        }
        if (!Request["standard"].Equals(""))
        {
            sql_end += " and standard like '%" + Request["standard"] + "%'";
        }
        if (!Request["free1"].Equals(""))
        {
            sql_end += " and free1 like '%" + Request["free1"] + "%'";
        }
        if (!Request["free2"].Equals(""))
        {
            sql_end += " and free2 like '%" + Request["free2"] + "%'";
        }
        if (!Request["cus_name"].Equals(""))
        {
            sql_end += " and cus_name='" + Request["cus_name"] + "'";
        }
        if (!Request["dep_name"].Equals(""))
        {
            sql_end += " and dep_name='" + Request["dep_name"] + "'";
        }
        if (!Request["st_name"].Equals(""))
        {
            sql_end += " and st_name='" + Request["st_name"] + "'";
        }
        //当前用户是否管理员
        bool is_admin = new AuthUI().CheckLoginUserIsAdmin();
        if (!is_admin)
        {
            //值为1代表启用业务员权限控制
            string sale_employee_auth = new ConfigReader().GetConfig("sale_employee_auth");
            if (sale_employee_auth.Equals("1"))
            {
                //只有客户专管业务员或业务主管才有权限看到
                sql_end += " and cus_ele_id in (" + new AuthEx().GetSqlForMyEmployee() + ")";
            }
            //值为1代表启用部门权限控制
            string system_dep_auth = new ConfigReader().GetConfig("system_dep_auth");
            if (system_dep_auth.Equals("1"))
            {
                sql_end += " and dep_id in (" + new AuthEx().GetSqlForMyDepartment() + ")";
            }
            //值为1代表启用仓库权限控制
            string system_wh_auth = new ConfigReader().GetConfig("system_wh_auth");
            if (system_wh_auth.Equals("1"))
            {
                sql_end += " and t_wh_id in (" + new AuthEx().GetSqlForMyWarehouse() + ")";
            }
        }
        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;
        //Response.Write(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 = "";
    //列表查询用的sql
    string sql_list = @"
                    select top 1000000000 d.ios_id,t.io_id,t.wh_id as t_wh_id,wh.wh_id,wh.wh_code,wh.wh_name,d.i_id,dos.do_id,dos.dos_id,
                    d.free1,d.free2,d.unit_price,d.tax_unit_price,
                    d.quantity,d.num,
                    d.convert_rate,d.total_price,d.tax_total_price,
                    d.tax,d.d_tax_rate,
                    d.d_remark,
                    d.d_sdefine1,d.d_sdefine2,d.d_sdefine3,d.d_sdefine4,d.d_sdefine5,
                    d.d_ndefine1,d.d_ndefine2,d.d_ndefine3,d.d_ndefine4,d.d_ndefine5, 
                    t.order_number,convert(char(10),t.new_date,23) as new_date,cus.cus_name,
                    ele.ele_name,cus.ele_id as cus_ele_id,t.dep_id,
                    iot.iot_name,st.st_name,dep.dep_name,t.remark,
                    t.sdefine1,t.sdefine2,t.sdefine3,t.sdefine4,t.sdefine5,
                    t.ndefine1,t.ndefine2,t.ndefine3,t.ndefine4,t.ndefine5,
                    t.new_user,t.verify_user,i.i_code,i.i_name,i.standard,i.munit,i.aunit,
                    do.order_number as do_order_number,so.order_number as so_order_number,
                    i.sdefine1 as d_inv_sdefine1,i.sdefine2 as d_inv_sdefine2,i.sdefine3 as d_inv_sdefine3,
                    i.sdefine4 as d_inv_sdefine4,i.sdefine5 as d_inv_sdefine5,
                    i.ndefine1 as d_inv_ndefine1,i.ndefine2 as d_inv_ndefine2,i.ndefine3 as d_inv_ndefine3,
                    i.ndefine4 as d_inv_ndefine4,i.ndefine5 as d_inv_ndefine5
                    from Stock_InOut t
                    left join Stock_InOuts d on t.io_id=d.io_id
                    left join Base_Customer cus on t.cus_id=cus.cus_id
                    left join Base_Employee ele on t.ele_id=ele.ele_id
                    left join Base_SaleType st on t.st_id=st.st_id
                    left join Base_Department dep on t.dep_id=dep.dep_id
                    left join Base_Warehouse wh on d.d_wh_id=wh.wh_id
                    left join Base_Inventory i on d.i_id=i.i_id
                    left join Base_IOType iot on t.iot_id=iot.iot_id
                    left join Sale_DeliveryOrders dos on dos.dos_id=d.dos_id
                    left join Sale_DeliveryOrder do on do.do_id=dos.do_id
                    left join Sale_SalesOrders sos on sos.sos_id=dos.sos_id
                    left join Sale_SalesOrder so on so.so_id=sos.so_id
                    where order_type=6 ";
    /// <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
        //Response.Write(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;
    }
}
