private static DataMember.Product BuildProduct(SqlDataReader reader)
    {
        DataMember.Product product = new DataMember.Product();

        product.ID           = reader.IsDBNull(0) ? int.MinValue : reader.GetInt32(0);
        product.Requester    = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
        product.Product_Name = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
        product.Quantity     = reader.IsDBNull(3) ? Decimal.MinValue : reader.GetDecimal(3);
        product.Date         = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
        product.Is_Read      = (Boolean)reader.GetValue(5);
        product.Status       = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
        product.IndentId     = reader.IsDBNull(7) ? int.MinValue : reader.GetInt32(7);
        return(product);
    }
    public DataMember.PagedResult <DataMember.Product> GetProductList(int start, int max, string sortColumn, string sortOrder, string cond)
    {
        string cond1  = "";
        string deptid = "";

        if (cond != "")
        {
            System.Array acond = cond.Split('|');
            cond1  = acond.GetValue(0).ToString();
            deptid = acond.GetValue(1).ToString();
        }
        string Cond_SQL = string.Empty;

        if (max == 0)
        {
            max = 10;
        }

        if (string.IsNullOrEmpty(sortColumn))
        {
            sortColumn = "ID";
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "DESC";
        }
        if (cond != string.Empty)
        {
            Cond_SQL = cond1;
            if (cond1 == "A")
            {
                Cond_SQL = "True";
            }
            if (cond1 == "NA")
            {
                Cond_SQL = "False";
            }
        }
        else
        {
            cond1 = "NPP";
        }
        //string SQL = "Select DISTINCT [ID],[Requester],([Prod_Name]+' Quantity:'+cast(Qty as nvarchar)) as Prod_Name,[Qty],convert(nvarchar,IndentDate,109) as IndentDate,Is_Read,case indentstatus WHEN 'P' THEN 'Pending' WHEN 'C' THEN 'Closed' ELSE 'New' END AS Status From ( " +
        //                       "select Indent.ID,dbo.ConcateName(first_name,middle_name,last_name) as Requester,(Prod_Name+' ['+Prod_code+']') as Prod_Name,Qty,IndentDate,Is_Read,indent.indentstatus, " +
        //                       "ROW_NUMBER() OVER (ORDER BY Indent.ID DESC) AS [RowIndex] " +
        //                       "from Indent,Client_Regs,Prod_Manager,college " +
        //                       "where	prod_manager.Ctrl_Id = indent.Ctrl_Id and " +
        //                        " prod_manager.instid = indent.instid and " +
        //                       "Client_Regs.id=Indent.userid and " +
        //                       "college.collegeid=Indent.instid and " +
        //                       "college.collegeid=Client_Regs.instid and " +
        //                       "college.collegeid={4} AND " + Cond_SQL + ") as rec " +
        //                       "where ([RowIndex] > {2}) AND ([RowIndex] <= ({2} + {3})) " +
        //                       "SELECT COUNT(ID) FROM [Indent] WHERE " + Cond_SQL + "";

        int total = 0;
        List <DataMember.Product> list = new List <DataMember.Product>();

        SqlCommand com = null;

        //SqlCommand com1 = null;

        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            con.Open();

            //com1 = new SqlCommand("select departmentid from iemployeeaction_vw where userid=" + Int32.Parse(Session["uid"].ToString()), con);
            //SqlDataReader drd = com1.ExecuteReader();

            //while (drd.Read())
            //{
            //    if (deptid != "")
            //    {
            //        deptid = deptid + "," + drd[0].ToString();
            //    }
            //    else
            //    {
            //        deptid = drd[0].ToString();
            //    }
            //}
            //if (deptid != "")
            //{
            //    deptid = "(" + deptid + ")";
            //}
            //else
            //{
            //    deptid = "(0)";
            //}

            //drd.Close();
            com            = new SqlCommand();
            com.Connection = con;
            com.Parameters.AddWithValue("flag", cond1);
            com.Parameters.AddWithValue("start", start);
            com.Parameters.AddWithValue("max", max);
            com.Parameters.AddWithValue("Condition", Cond_SQL);
            com.Parameters.AddWithValue("InstId", Int32.Parse(Session["instID"].ToString()));
            com.Parameters.AddWithValue("deptid", deptid);
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "SP_Indent_Box";
            SqlDataReader Sreader = com.ExecuteReader();
            while (Sreader.Read())
            {
                DataMember.Product p = BuildProduct(Sreader);
                list.Add(p);
            }
        }
        //using (IDbConnection cnn = CreateConnection())
        //{
        //    using (IDbCommand cmd = cnn.CreateCommand())
        //    {
        //        //cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max, Session["InstId"].ToString());
        //        //cmd.CreateParameter();
        //        //cmd.CommandType = CommandType.StoredProcedure;
        //        //cmd.Parameters.Add(sta
        //        //cmd.CommandText = "SP_Indent_Box";
        //        using (IDataReader rdr = cmd.ExecuteReader())
        //        {
        //            while (rdr.Read())
        //            {
        //                DataMember.Product p = BuildProduct(rdr);

        //                list.Add(p);
        //            }

        //            if ((rdr.NextResult()) && (rdr.Read()))
        //            {
        //                total = rdr.GetInt32(0);
        //            }
        //        }
        //    }
        //}

        if ((list.Count == 0))
        {
            return(null);
        }

        DataMember.PagedResult <DataMember.Product> result = new DataMember.PagedResult <DataMember.Product>();

        result.Rows  = list;
        result.Total = total;

        return(result);
    }
    public DataMember.PagedResult <DataMember.Product> GetProductListadm(int start, int max, string sortColumn, string sortOrder, string cond)
    {
        string Cond_SQL = string.Empty;

        if (max == 0)
        {
            max = 10;
        }

        if (string.IsNullOrEmpty(sortColumn))
        {
            sortColumn = "ID";
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "DESC";
        }
        if (cond != string.Empty)
        {
            Cond_SQL = cond;
            if (cond == "A")
            {
                Cond_SQL = "True";
            }
            if (cond == "NA")
            {
                Cond_SQL = "False";
            }
        }
        else
        {
            cond = "Al";
        }

        int total = 0;
        List <DataMember.Product> list = new List <DataMember.Product>();

        SqlCommand com  = null;
        SqlCommand com1 = null;

        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            con.Open();

            com1 = new SqlCommand("select deptid from iemployeeusers_vw where userid=" + Int32.Parse(Session["uid"].ToString()), con);
            object retdid = com1.ExecuteScalar();
            int    rval   = retdid != null?int.Parse(retdid.ToString()) : 0;

            com            = new SqlCommand();
            com.Connection = con;
            com.Parameters.AddWithValue("flag", cond);
            com.Parameters.AddWithValue("start", start);
            com.Parameters.AddWithValue("max", max);
            com.Parameters.AddWithValue("Condition", Cond_SQL);
            com.Parameters.AddWithValue("InstId", Int32.Parse(Session["instID"].ToString()));
            com.Parameters.AddWithValue("deptid", rval);
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "SP_Indent_Boxadm";
            SqlDataReader Sreader = com.ExecuteReader();
            while (Sreader.Read())
            {
                DataMember.Product p = BuildProduct(Sreader);
                list.Add(p);
            }
        }

        if ((list.Count == 0))
        {
            return(null);
        }

        DataMember.PagedResult <DataMember.Product> result = new DataMember.PagedResult <DataMember.Product>();

        result.Rows  = list;
        result.Total = total;

        return(result);
    }