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