private static DataMember.RFQDetails BuildRFQ(IDataReader reader)
{
DataMember.RFQDetails RFQ = new DataMember.RFQDetails();
RFQ.ID = reader.IsDBNull(0) ? int.MinValue : reader.GetInt32(0);
RFQ.RFQNo = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
RFQ.RFQDate = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
//RFQ.ProductName = reader.IsDBNull(1) ? string.Empty : reader.GetString(3).Trim();
RFQ.Supplier = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
RFQ.Filename = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
RFQ.Filetype = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
RFQ.SupplierID = reader.IsDBNull(6) ? int.MinValue : reader.GetInt32(6);
return(RFQ);
}
public DataMember.PagedResult <DataMember.RFQDetails> GetRFQDetails(int start, int max, string sortColumn, string sortOrder)
{
if (max == 0)
{
max = 10;
}
if (string.IsNullOrEmpty(sortColumn))
{
sortColumn = "ID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "DESC";
}
string SQL = " Select [ID], [RFQNo],convert(nvarchar,RFQDate,109) as RFQDate, [vendor_name],[filename],[filetype],id_vendor From ( " +
" select Rfq_mst.[ID], Rfq_mst.[RFQNo],convert(nvarchar,Rfq_mst.RFQDate,109) as RFQDate,AddRelation.[vendor_name], RFQFileInfo.[filename],RFQFileInfo.[filetype],AddRelation.id_vendor, " +
" ROW_NUMBER() OVER (ORDER BY Rfq_mst.RFQDate DESC) AS [RowIndex] " +
" from Rfq_mst,RFQ_Child,college,Pur_Req_Child,pur_request,AddRelation,RFQFileInfo " +
" where RFQ_mst.id = RFQ_Child.RFQId and RFQ_mst.instid = RFQ_Child.instid and " +
" RFQ_Child.prid = Pur_Req_Child.Id and RFQ_Child.instid = Pur_Req_Child.instid and " +
" RFQ_mst.id = RFQFileInfo.RFQNo and RFQ_mst.instid = RFQFileInfo.instid and " +
" AddRelation.id_vendor = RFQFileInfo.supplierid and AddRelation.instid = RFQFileInfo.instid and " +
" Pur_Req_Child.pr_mst_id = pur_request.Id and Pur_Req_Child.instid = pur_request.instid and " +
" RFQ_Child.supplierid = AddRelation.id_vendor and RFQ_Child.instid = AddRelation.instid and " +
" college.collegeid=Rfq_mst.instid and " +
" college.collegeid=Pur_Req_Child.instid and " +
" college.collegeid=RFQ_Child.instid and " +
" college.collegeid=pur_request.instid and " +
" college.collegeid=AddRelation.instid and " +
" college.collegeid={4} GROUP BY Rfq_mst.[ID], Rfq_mst.[RFQNo],Rfq_mst.RFQDate, [vendor_name],[filename],[filetype],AddRelation.id_vendor) as rec " +
" where ([RowIndex] > {2}) AND ([RowIndex] <= ({2} + {3})) " +
" SELECT COUNT(ID) FROM [Rfq_mst]";
int total = 0;
List <DataMember.RFQDetails> list = new List <DataMember.RFQDetails>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max, Session["InstId"].ToString());
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataMember.RFQDetails p = BuildRFQ(rdr);
list.Add(p);
}
if ((rdr.NextResult()) && (rdr.Read()))
{
total = rdr.GetInt32(0);
}
}
}
}
if ((list.Count == 0) || (total == 0))
{
return(null);
}
DataMember.PagedResult <DataMember.RFQDetails> result = new DataMember.PagedResult <DataMember.RFQDetails>();
result.Rows = list;
result.Total = total;
return(result);
}