Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- let query = oo.query.raw();
- query.sql = "SELECT i.Code AS Code, i.Name AS Name, IFNULL(SUM(gri.Qty), 0) TotalReceived, IFNULL(SUM(soi.Qty),0) TotalSales, i.Unit ";
- query.sql += "FROM Item i ";
- query.sql += "LEFT JOIN GoodsReceiptItemRow gri on gri.ArtCode = i.Code ";
- query.sql += "LEFT JOIN SalesOrderItemRow soi on soi.ArtCode = i.Code ";
- query.sql += "LEFT JOIN GoodsReceipt gr on gri.masterId = gr.internalId ";
- query.sql += "LEFT JOIN SalesOrder so on soi.masterId = so.internalId ";
- query.sql += `WHERE gr.TransDate BETWEEN '${specs.Date[0].format('YYYY-MM-DD')}' AND '${specs.Date[1].format('YYYY-MM-DD')}' `;
- query.sql += "AND gr.Status = 1 AND gr.Invalid = 0 AND so.Status = 1 "; //AND so.Status = 1 AND so.Invalid = 0
- if (specs.ItemsWithSales) query.sql += `AND so.TransDate BETWEEN '${specs.Date[0].format('YYYY-MM-DD')}' AND '${specs.Date[1].format('YYYY-MM-DD')}' `;
- if (specs.ItemGroup) query.sql += `AND i.ItemGroup = '${specs.ItemGroup}' `;
- query.sql += "GROUP BY i.Code";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement