using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.UI.WebControls;

namespace mynamespace
{
public partial class GridViewClass : System.Web.UI.Page
{
    static int gender = 1;
    static bool DataLevel = true;

    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void btnshowgrid_Click(object sender, EventArgs e)
    {


        string constr = "Data Source= MyMachineName,1433;Initial Catalog= dbname;Connection Timeout=1 ;User ID= 'username' ;Password= 'password'";
        MyGlobalVariables.con = new SqlConnection(constr);
        MyGlobalVariables.con.Open();



        string uname;


        uname = "myuser";

        int monthvar = ddlmonth.SelectedIndex + 1;
        int yearvar = Convert.ToInt16(ddlyear.SelectedValue);

        SqlCommand queryEmpCount;
        if(DataLevel)
        {
            queryEmpCount = new SqlCommand("StoredProDeptAndEmpCount", MyGlobalVariables.con);

        }
        else
        {
            queryEmpCount = new SqlCommand("StoredProAreaAndEmpCount", MyGlobalVariables.con);
        }

        queryEmpCount.CommandType = CommandType.StoredProcedure;
        SqlParameter Gender = queryEmpCount.Parameters.AddWithValue("Gender", gender);

        SqlParameter Uname = queryEmpCount.Parameters.AddWithValue("Uname", uname);

        SqlParameter joindate = queryEmpCount.Parameters.AddWithValue("joindate", "01-05-2016");


        SqlDataReader EmpCountdata = queryEmpCount.ExecuteReader();
        DataTable dtDeptorArea = new DataTable();

        dtDeptorArea.Load(EmpCountdata);

        if(dtDeptorArea.Rows.Count > 0)
        {
            SqlDataAdapter queryTotalSalary;

            if(DataLevel)
            {
                dtDeptorArea.PrimaryKey = new DataColumn[] { dtDeptorArea.Columns["DeptCode"] };
                queryTotalSalary = new SqlDataAdapter("select  Hierarchy.DeptCode , mydate.CountEmptotal from Hierarchy full outer join  (select COUNT(EmpCode) as  CountEmptotal,  Hierarchy.DeptCode  from [SALARY EMP] join Hierarchy on Hierarchy.BCODE= [SALARY EMP].BCODE where [SALARY EMP].BCODE in (select BCODE from [SALARY EMP] where (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and SMonth = " + monthvar + " and SYear = " + yearvar + " and Hierarchy.gender =" + gender + ") and (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and SMonth = " + monthvar + " and SYear = " + yearvar + "   group by Hierarchy.DeptCode)   as mydate  on Hierarchy.DeptCode= mydate.DeptCode where (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and gender =" + gender + " group by Hierarchy.Majlisname, Hierarchy.DeptCode, mydate.DeptCode, mydate.CountEmptotal order by Hierarchy.DeptCode", MyGlobalVariables.con);
            }
            else
            {
                dtDeptorArea.PrimaryKey = new DataColumn[] { dtDeptorArea.Columns["AreaCode"] };

                queryTotalSalary = new SqlDataAdapter("select  Hierarchy.AreaCode , mydate.CountEmptotal from Hierarchy full outer join  (select COUNT(EmpCode) as  CountEmptotal,  Hierarchy.AreaCode  from [SALARY EMP] join Hierarchy on Hierarchy.BCODE= [SALARY EMP].BCODE where [SALARY EMP].BCODE in (select BCODE from [SALARY EMP] where (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and SMonth = " + monthvar + " and SYear = " + yearvar + " and Hierarchy.gender =" + gender + ") and (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and SMonth = " + monthvar + " and SYear = " + yearvar + "   group by Hierarchy.AreaCode)   as mydate  on Hierarchy.AreaCode= mydate.AreaCode where (UAdmin like '%" + uname + "%' or UserGroup like '%" + uname + "%') and gender =" + gender + " group by Hierarchy.kbname, Hierarchy.AreaCode, mydate.AreaCode, mydate.CountEmptotal order by Hierarchy.AreaCode", MyGlobalVariables.con);
            }
            DataTable dtTotalsalaries = new DataTable();
            queryTotalSalary.Fill(dtTotalsalaries);

            if(DataLevel)
            {

                dtTotalsalaries.PrimaryKey = new DataColumn[] { dtTotalsalaries.Columns["DeptCode"] };

            }
            else
            {
                dtTotalsalaries.PrimaryKey = new DataColumn[] { dtTotalsalaries.Columns["AreaCode"] };
            }

            dtDeptorArea.Merge(dtTotalsalaries);
            dtDeptorArea.AcceptChanges();

            grdvsalaryreport.DataSource = dtDeptorArea;
            grdvsalaryreport.DataBind();
        }
    }

dtDeptorArea.Merge(dtTotalsalaries);