Click here to Skip to main content
14,828,891 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

i created this view :
CREATE OR REPLACE VIEW `view_items_accounts_tree_with_date` as
							SELECT Acitems.accitem_AccID,
                            acc.accdet_Cat,
                            acc.accdet_AccountCode,
										CONCAT_WS(' - ', acc.accdet_AccountCode, acc.accdet_LangAccName) AS MergName ,
									 	AcM.accm_ID,
                                        Acitems.accitem_ID,
                                        Acitems.accitem_Debit AS Debit,
										Acitems.accitem_Credit AS Credit,
										AcM.accitem_Date
                                         
						    FROM account_items Acitems
                                        
                               LEFT JOIN (SELECT Acmains.accm_ID,
                                            Acmains.accitem_Date
								  FROM account_main Acmains
								  GROUP BY Acmains.accm_ID,Acmains.accitem_Date) AcM
								ON Acitems.accm_ID = AcM.accm_ID
                                
                                LEFT JOIN (SELECT acct.accdet_ID,
									  acct.accdet_Cat,
									  acct.accdet_AccountCode,
									  acct.accdet_LangAccName
								  FROM accounts_detials acct
								  GROUP BY acct.accdet_ID) acc
								
				          	    ON Acitems.accitem_AccID = acc.accdet_ID;


then i created this Stored procedure to select sum of the credits , sum of the debit and the balance between two dates


CREATE PROCEDURE `getAccountsItems_WithDate_byDates`(in startDate Datetime, in endDate Datetime)
BEGIN
		SELECT 
        accdet_Cat,
        accdet_AccountCode,
        accitem_AccID,
		MergName,
		accm_ID,
        accitem_ID,
        accitem_Date,
			FORMAT(SUM(Debit),2) Debit ,
			FORMAT(SUM(Credit),2) Credit ,
			FORMAT((SUM(Debit)-SUM(Credit)),2) Balance
        FROM view_items_accounts_tree_with_date 
        where accitem_Date between startDate And  endDate
        Group By accitem_AccID;
END


What I have tried:

i used EF6 to fill a TreeList or Gridview with the data after creating an import function but Balance (this is custom filed not exist in the VIEWS and not exist in the table) return null.

treeList1.DataSource = DB1.fncgetAccountsItems_WithDate_byDates(strat, end).ToList();
Posted
Updated 15-Apr-19 0:42am

1 solution

Please, refer this: Use Stored Procedure in Entity Framework[^]

The most important thing is that, that you need to set "Import selected stored procedure into entity model" to create complex type and return all fields as a result.

Note, that "Balance" field is calculated on "Debit" and "Credit" fields, so you can always calculate it in run-time.

C#
treeList1.DataSource = DB1.fncgetAccountsItems_WithDate_byDates(strat, end)
    .ToList()
    .Select(x=> new
    {
        //re-write fields here
        //and add
        Balance = x.Debit - x.Credit
    })
    .ToList();


Good luck!
   
Comments
Golden Basim 15-Apr-19 8:11am
   
thank you
Maciej Los 15-Apr-19 8:31am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900