Click here to Skip to main content
14,302,218 members
Rate this:
Please Sign up or sign in to vote.
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 :
//Shortages النواقص
            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);
Posted
Updated 28-Dec-18 10:02am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 2

Not sure if I understood the question correctly, but you could try something like
WITH CurrentAmounts AS (
   SELECT sns.stitems_ID, 
          SUM(sns.StockQnty) AS Amount
   FROM stock_noserials sns
   GROUP BY sns.stitems_ID
) 
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN CurrentAmounts ca ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance

And to create a view for the query above, just place it inside a CREATE VIEW statement[^]

ADDITION:

Without CTE
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance


If needs to be added into amount, something like
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
     LEFT JOIN (SELECT pis.stitems_ID, 
                       COUNT(*) AS Amount
                FROM purchases_item_seriels pis
                WHERE pis.pis_Statues IN (1, 5, 6)
                GROUP BY pis.stitems_ID) pa
     ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance
   
v3
Comments
Golden Basim 28-Dec-18 16:43pm
   
thanks for reply , 1- it say unexpected 'with' (with)
2- i went to this table "purchases_item_seriels" to your query
Wendelius 28-Dec-18 17:14pm
   
For the item 1. Perhaps you're using an older version of MySQL which doesn't recognize CTE. I updated the question to use an inline view for the query.

What comes to the item 2, not sure if I understand the meaning of the table purchases_item_seriels. Should those rows be added to the amount in stock? If that is the case, have a look at the third variation in the answer.
Golden Basim 29-Dec-18 4:53am
   
yes the last query i think that it what i mean .. i will try it now
thanks
Golden Basim 29-Dec-18 4:56am
   
i have error with syntax again in (LEFT ,and SUM) , i use MySQL server 5.7.21

CREATE OR REPLACE VIEW `shortages` as
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = si.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (1, 5, 6)
GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance;
Wendelius 29-Dec-18 5:08am
   
Sorry there's an extra comma after si. Try with
CREATE OR REPLACE VIEW `shortages` as
   SELECT si.stitems_ID,
          si.stitems_MinBalance,
          COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
   FROM st_items si
   LEFT JOIN (SELECT sns.stitems_ID,
                     SUM(sns.StockQnty) AS Amount
              FROM stock_noserials sns
              GROUP BY sns.stitems_ID) ca
   ON ca.stitems_ID = si.stitems_ID
   LEFT JOIN (SELECT pis.stitems_ID,
                     COUNT(*) AS Amount
              FROM purchases_item_seriels pis
              WHERE pis.pis_Statues IN (1, 5, 6)
              GROUP BY pis.stitems_ID) pa
   ON pa.stitems_ID = si.stitems_ID
   WHERE (COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0)) < si.stitems_MinBalance;


But before making the view, you can try the SELECT statement on itself, just to test if it's giving correct results
Golden Basim 29-Dec-18 6:55am
   
i tested it with real values .. it is work very very very good .. thank you i will build many VIEWs based on you query ,,, so what you suggest it will be good if i select * from this view with EF or i should to use Stored Procedure also ?
Wendelius 29-Dec-18 9:35am
   
You're welcome :)

I don't think a stored procedure would give any extra benefit here so I personally would select the view directly.
Golden Basim 29-Dec-18 13:58pm
   
thanks :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

You should create the View on the database server to take advantage of indexing and database server performance. I would be surprised if LINQ was designed to handle complex scenarious like this even though you can formulate them in theory (or practice).
   
Comments
Golden Basim 28-Dec-18 15:43pm
   
thank you , i checked this documentation and others .. but i can't create the required query

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100