Click here to Skip to main content
15,908,112 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I need to get the latest purchases cost for every group based on Item , supplier, unit. and synchronization it to another location ..
item 1, pack, Jone, ...
item 1, pack, Mich, ...
item 1, pack, Yasser, ...
item 2, pack, Jone, ...
item 2, pack, Mich, ...
item 3, pack, Yasser, ...


with some conditions:
 u.cost != u.syns_cost
u.Unit != u.syns_unit
 u.pmain_Date >= last30DaysDate


i tried to created Full MySQL view but i failed

What I have tried:

I created MySQL View:
CREATE VIEW purchases_data_view AS
SELECT 
    i.stitems_ID AS item_id,
    i.stitems_Name AS item_name,
    i.stitems_Code AS general_code,
    i.stitems_JeptusCode AS vatoce_code,
    i.stitems_Pharma_ActiveIngredient as main_component,
    f.manu_Name AS manufacturer_name,
    cat.stcate_Name AS category_name,
    pi.item_NetUnitPrice AS cost,
    uns.Unit,
    syns.syns_cost,
    syns.syns_unit,
	pm.pmain_Date,
	pm.vendor_ID as supplier_id,
    p.vatoce_code as supplier_vatoce_code,
    p.pepole_Name as supplier_name,
    p.pepole_City as supplier_city,
    p.pepole_Address as supllier_address,
    p.pepole_Mobile as supplier_phone,
  sunits.Unit as small_unit_name,
  sunits.stitemsu_PurchasesPrice as small_unit_purchase_price,
  sunits.stitemsu_SalesPrice as small_unit_sales_price,
  lunits.Unit as large_unit_name,
  lunits.stitemsu_UnitNum as unit_count,
  lunits.stitemsu_PurchasesPrice as large_unit_purchase_price, 
  lunits.stitemsu_SalesPrice as large_unit_sales_price,
  lunits.stitemsu_SalesPrice as audience_price
FROM
    st_items i

INNER JOIN (SELECT    
				MAX(pr_item.pitem_ID) as pitem_ID, 
				pr_item.pmain_ID, 
				pr_item.stitems_ID, 
				pr_item.unit_ID,
				pr_item.item_NetUnitPrice
	  FROM purchases_item pr_item
	  GROUP BY 
				pr_item.pmain_ID, 
				pr_item.stitems_ID, 
				pr_item.unit_ID,
				pr_item.item_NetUnitPrice) pi
	ON i.stitems_ID = pi.stitems_ID

JOIN (SELECT 
				pr_main.pmain_ID,
				pr_main.pmain_Date,
				pr_main.vendor_ID
				FROM purchases_main pr_main
				where pr_main.vendor_ID NOT IN (1, 2, 3)
			 ) pm
		on pi.pmain_ID = pm.pmain_ID
        
JOIN (SELECT 
		units.stitemsu_ID,
		units.stitemsu_UnitName,
        units.stitemsu_UnitNum,
        units.stitemsu_SalesPrice,
        un.Unit
		FROM st_items_units units
					JOIN (SELECT 
					unname.ID,
					unname.Unit
					FROM st_units_name unname
				 ) un
			on un.ID = units.stitemsu_UnitName
	 ) uns
on pi.unit_ID = uns.stitemsu_ID

 LEFT JOIN (SELECT 
							  sunit.stitems_ID,
							  sunit.UnitDetID,
                              sunit.Unit,
                              sunit.stitemsu_PurchasesPrice,
                              sunit.stitemsu_SalesPrice,
							  sunit.stitemsu_SalesDiscount
								  FROM view_units_1stunit sunit
								  GROUP BY sunit.stitems_ID,sunit.UnitDetID,sunit.Unit,sunit.stitemsu_PurchasesPrice,sunit.stitemsu_SalesPrice, sunit.stitemsu_UnitNum, sunit.stitemsu_SalesDiscount) sunits
					   ON sunits.stitems_ID = i.stitems_ID
                       
                       INNER JOIN (SELECT 
							  lunit.stitems_ID,
                              lunit.UnitDetID,
							  lunit.Unit,
                              lunit.stitemsu_PurchasesPrice,
                              lunit.stitemsu_SalesPrice,
                              lunit.stitemsu_UnitNum ,
							  lunit.stitemsu_SalesDiscount
								  FROM view_units_lastunit lunit
								  GROUP BY lunit.stitems_ID,lunit.UnitDetID,lunit.Unit,lunit.stitemsu_PurchasesPrice,lunit.stitemsu_SalesPrice, lunit.stitemsu_UnitNum , lunit.stitemsu_SalesDiscount) lunits
					   ON lunits.stitems_ID = i.stitems_ID

LEFT JOIN
    st_categories cat ON i.stcate_ID = cat.stcate_ID
LEFT JOIN
    st_plug_manufacturer f ON i.stitems_Manufacturer = f.manu_ID
    
LEFT JOIN
    people_data p ON pm.vendor_ID = p.pepole_ID
    
LEFT JOIN (SELECT 
			MAX(sns.id) as id,
				sns.stitems_ID,
				sns.pepole_ID,
                sns.last_price as syns_cost,
                sns.unit as syns_unit
				FROM suppliers_prices_syns sns
				group by sns.id, sns.stitems_ID, sns.pepole_ID  order by sns.id desc) syns
		 ON i.stitems_ID = syns.stitems_ID and pm.vendor_ID = syns.pepole_ID and  uns.Unit = syns.syns_unit


also EF6 Query :
var DB1 = ConnectionTools.OpenConn();
            var virusalPeople = new List<int> { 1, 2, 3 };

            var last30DaysDate = DateTime.Now.AddDays(-3000);

var query = DB1.purchases_data_view
                        .Where(u => u.cost != u.syns_cost &&
                u.Unit != u.syns_unit &&
                !virusalPeople.Contains(u.supplier_id.Value) &&
                u.pmain_Date >= last30DaysDate
                && u.item_id == 14989)
                    .GroupBy(u => new
                    {
                        u.item_id,
                        u.Unit,
                        u.supplier_id,
                    })
                    .Select(g => g.OrderByDescending(u => u.pmain_Date).FirstOrDefault())
                    .Take(100)
                    .ToList();




                var resultAsString = string.Join(Environment.NewLine, query.Select(r => $"{r.supplier_id}"));

                Console.WriteLine(resultAsString);


testing that with item id =
14989


the result (not expected it should one record only per group but here many repeated data):

14989, قطعة, 76
14989, قطعة, 12
14989, قطعة, 12
14989, قطعة, 119
14989, قطعة, 119
14989, قطعة, 76
14989, قطعة, 76
14989, قطعة, 67
14989, قطعة, 273
14989, قطعة, 67
14989, قطعة, 76
14989, قطعة, 76
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 113
14989, قطعة, 113
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 113
14989, قطعة, 67
14989, قطعة, 414
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 414
14989, قطعة, 414
14989, قطعة, 67
Posted
Updated 20-Apr-24 4:03am
v2
Comments
Graeme_Grant 20-Apr-24 9:54am    
Fails? Fails how? What happens versus what is expected? By looking at the answers to these questions lies the answer. Understanding why it fails is how you work a solution. Please update your question with more details.
Golden Basim 20-Apr-24 10:07am    
the result (not expected every group should contains one record only but here many repeated data):

1 solution

So the issue is the uniqueness of the key. Using a dynamic object is the cause of your issue. Each object is unique. This is easy to demonstrate. Create two instances of a class with the same values, then do a comparison. You will find that they are not equal.

So how do you create a key that is not unique? Use a compound string key for the grouping:
C#
$"{u.item_id}|{u.Unit}|{u.supplier_id}"
 
Share this answer
 

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