hi,
first i'm sorry if i cant write a good title and welcome to any recommendations
i have "
st_items" that have "
stitems_Type" field , this field determine if the item is "stock" or "stock with custom serial".
st_items data rows examples :-
----------------------------------------------------
stitems_ID | stitems_Type | stitems_MinBalance | ---
1 | 0 | 10 | ---
2 | 0 | 20 | ---
3 | 1 | 5 | ---
4 | 1 | 15 | ---
----------------------------------------------------
if "stitems_Type = 0" , the item is "stock"
in this case all stocks information that related to this item will stored in table called "
stock_noserials" with this rows example :
---------------------------------
id | stitems_ID | StockQnty | ---
1 | 1 | 5 | ---
2 | 1 | 3 | ---
3 | 2 | 500 | ---
4 | 2 | 150 | ---
---------------------------------
as "stock_noserials " table , the stock of
item 1 = 8 (sum of StockQnty colmun) and the stock of
item 2 = 650 (sum of StockQnty colmun)
if "stitems_Type = 1" , the item is "stock with serials"
in this case all stocks information that related to this item will stored in table called "
purchases_item_seriels" with this rows example :
-------------------------------------
pis_ID| stitems_ID | pis_Statues| ---
1 | 3 | 1 | ---
2 | 3 | 2 | ---
3 | 3 | 4 | ---
4 | 3 | 5 | ---
5 | 4 | 2 | ---
-------------------------------------
pis_Statues (1 = in stock , 2 = sold , 4 = lost , 5 = returned sales) so , to get the stock will count the item that have this pis_Statues values (1,5,6)
as "purchases_item_seriels" table , the stock of
item 3 = 2 (count of records where pis_Statues colmun value is (1,5,6)) and the stock of
item 4 = 0
i want to create VIEWS that will provide me with the Shortages Items as this results :
--------------------------------------------------
stitems_ID | stitems_MinBalance | stock | ---
1 | 10 | 8 | ---
3 | 5 | 2 | ---
4 | 15 | 0 | ---
--------------------------------------------------
so i can also create Stored Procedure that will based on this VIEW ..
What I have tried:
I'm thinking of creating a
View1 to join
st_itemswith
stock_noserials and creating a
View2 to join
st_itemswith
purchases_item_seriels . but i don't know how i can create view to combine the two views
now i get shortages with Entity Framework code but it is very slow ..
st_itemsBindingSource.DataSource = nDB01.st_items
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sumNoSerial = nDB01.stock_noserials.Where(u => u.stitems_ID == grp.Key).Sum(s => s.StockQnty),
sumWithSerial = nDB01.purchases_item_seriels.Where(u => (u.stitems_ID == grp.Key && u.pis_Statues == 0) ||
(u.stitems_ID == grp.Key && u.pis_Statues == 5) ||
(u.stitems_ID == grp.Key && u.pis_Statues == 6))
.Count(),
minBalance = grp.Select(s => s.stitems_MinBalance).FirstOrDefault()
})
.Where(u => u.minBalance >= (u.sumNoSerial+u.sumWithSerial)).ToList();
also i get its count with this code :
int Shortages_StockNoSer = nDB01.stock_noserials
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sum = grp.Sum(x => x.StockQnty),
min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
}).Where(u => u.sum < u.min && u.min != 0)
.Count();
int Shortages_StockWithSer = nDB01.purchases_item_seriels
.GroupBy(x => x.stitems_ID)
.Select(grp => new
{
itemID = grp.Key,
sum = grp.Count(),
min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
}).Where(u => u.sum < u.min && u.min != 0)
.Count();
int CountShortagesItems = Convert.ToInt32( Shortages_StockNoSer) + Convert.ToInt32(Shortages_StockWithSer);