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