Database

public class WEBSITE_COUNTRIES
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public ObjectId InternalId { get; set; }

    public string Id { get; set; }
    public string LongName { get; set; }
    public string ShortName { get; set; }
    public bool Enabled { get; set; }
    public WEBSITE_STATES States { get; set; }
}
public class WEBSITE_STATES
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public ObjectId InternalId { get; set; }
    public string StateId { get; set; }
    public string CountryCodee { get; set; }
    public string LongName { get; set; }
    public string ShortName { get; set; }
    public bool Enabled { get; set; }

}

hi all... i am having data in transaction table. the table structure is 

trans_Id, Order_Id, Item_No, Order_Qty,Supply_Qty,Trans_Type,amnt_Climed_Type,amnt_Climed_Prcntage

Generally we are getting orders from client and based on the orders we are supplying material and at the
time of supply itself we are raising invoice. The invoice claiming conditions are based on the order terms and conditions. the conditions are like this.
                  1) total quantity with total amount 
		  2) partial quantity with toal amount
		  3) total quantity with partial amount ( like 10% on supply)
		  4) partial quantity with partial amount (like 30% of supply with 50% of amount on supply)..

the transaction table contains data like this.

-------------------------------------------------------------------------------------------------------------
trans_Id | Order_Id | Item_No | Order_Qty | Supply_Qty | Trans_Type | amnt_Climed_Type | amnt_Climed_Prcntage
-------------------------------------------------------------------------------------------------------------
	1		ordxyz		1			500			NULL		O			NULL				NULL
	2		ordxyz		2			1000		NULL		O			NULL				NULL
	3		ordxyz		3			100			NULL		O			NULL				NULL
	4		ordxyz		4			700			NULL		O			NULL				NULL
	5		ordxyz		5			600			NULL		O 			NULL				NULL
	6		ordxyz		1			NULL		500			I			F					100 
	7		ordxyz		2			NULL		300			I			F					100
	8		ordxyz		2			NULL		700			I			P					30
	9		ordxyz		4			NULL		500			I			F					100
	10		ordxyz		5			NULL		200			I			P					70
	11		ordxyz		5			NULL		150			I			P					40
	12		ordxyz		5			NULL		200			I			P					30
	13		ordxyz		5			NULL		120			I			F					100
---------------------------------------------------------------------------------------------------------------
Trans_Type --- order or supply (o- for order) (I- for Invoice)
     amnt_Climed_Type---- full amount climed or partially climed ( F-FULL 100% CLIMED ,  P-Partial amount climed.)

in the above data total we have 5 orders.. on that, 
FOR item_No.1(row 1) full quantity supplyed(in row 6) and rised invoice 100% so item_No. 1 is completed.
FOR item_No.2(row 2) 300 quantity supplyed(in row 7) and rised invoice 100% so item_no.2 300 quantity completed. balance is 700 quantity 
FOR item_No.2(row 2) 700 quantity supplyed(in row 8) and rised invoice 30% so item_no.2 700 quantity completed. but againg we have to clime same quantity for 70% (pending)
FOR item_No.3(row 3) 100 quantity not supplyed so item_no.3 (pending)
FOR item_No.4 (row 4) 500 Quantity supplyed (in row 9) and risied invoice for 100%. so 200 quantity is pending (pending)
FOR item_No.5 (row 5) we climed 200 quantity for 70% (in row 10) and again climed 200 quanty for 30% (in row 12) so this is also completed.
FOR item_No.5 (row 5) we climed 150 quantity for 40% (in row 11) and remaining 60% not climed (pending)
FOR item_No.5 (row 5) we climed 120 quantity for 100% and this is completed.
FOR item_No.5 (row 5) total 130 quantity has to clime for 100% and 150 quantity has to clime for 60% (pending)

now i want the output like only pending orders and pending amount orders for climing. i.e

---------------------------------------------------------------------------------------------------
 Order_Id | Item_No | Order_Qty | pending_Supply_Qty | pending_Prcntage
---------------------------------------------------------------------------------------------------
	ordxyz		2			1000		700				70		
	ordxyz		3			100			100				100		
	ordxyz		4			700			200				100		
	ordxyz		5			600			150				60
	ordxyz		5			600			130				100
------------------------------------------------------------------------------------------------------