A small SQL in the previous projects, check to 10 minutes again,,, than the complex SQL and several projects, and helpless,
SELECT
A.s VendorCode,
A.s VendorNameCn,
A. distribution center stock + a. goods to fill a single,
A. return,
A. the approved amount,
A. distribution center stock + a. cargo to fill a single + a. return - a. the approved amount,
A. has a payment,
A. distribution center stock + a. cargo to fill a single + a. return - a. payment amount,
A. distribution center the current inventory amount,
A. stores the current inventory amount,
A. napa stores the current inventory amount,
A. meijia current inventory amount,
IF (IFNULL (a. last supply single audit date,
'1900-01-01 00:00:00) & gt; IFNULL (a. last goods to fill a single audit date,
'1900-01-01 00:00:00),
A. supply single audit date finally,
A. the final goods to fill a single audit date),
IF (IFNULL (a. the final delivery date of single reconciliation,
'1900-01-01 00:00:00) & gt; IFNULL (a. the last date of goods to fill a single reconciliation,
'1900-01-01 00:00:00),
A. the final delivery date of single reconciliation,
A. the final goods to fill a single reconciliation date),
Make out an invoice amount
The FROM
(SELECT
V. VendorCode,
V. VendorNameCn,
IFNULL ((SELECT
The SUM (h.f ReceivePurchaseAmount)
The FROM
Logis_supply_header h
WHERE
H.b Enable=TRUE AND h.f SupplyStatus=0
AND h.f SupplierId=v. VendorId), 0) distribution center of replenish onr's stock,
IFNULL ((SELECT
The SUM (h.f FinalPurchaseAmount)
The FROM
Logis_delivery_header h
The JOIN supp_supplier_wh w ON h.f ShipFrom=w.f SupplierWhId
WHERE
H.b Enable=TRUE
AND h.f DeliveryStatus=0
AND h.s DeliveryType LIKE '% 1'
AND w.f SupplierId=v. VendorId), 0) goods to fill a single,
IFNULL ((SELECT
- the SUM (h.f PurchaseAmount)
The FROM
Logis_return_header h
WHERE
H.b Enable=TRUE AND h.f ReturnStatus=0
AND h.f SupplierId=v. VendorId), 0) returns,
IFNULL ((SELECT
The SUM (b. TotalPrice)
The FROM
Vendor_bill b
WHERE
B.b Enable=TRUE
AND b. VendorId=v. VendorId), 0) the approved amount,
IFNULL ((SELECT
The SUM (b. PayPrice)
The FROM
Vendor_bill b
WHERE
B.b Enable=TRUE
AND b. VendorId=v. VendorId), 0) has a payment,
IFNULL ((SELECT
The SUM (b. BillAmout)
The FROM
Vendor_bill b
WHERE
B.b Enable=TRUE
AND b. VendorId=v. VendorId), 0), amount of make out an invoice,
IFNULL ((SELECT
The SUM ((o.f Quantity + IFNULL (t.q ty, 0)) * SPP., fPurchasePrice)
The FROM
Logis_onhand o
The JOIN shop_item I ON i.f ItemId=o.f ItemId
The JOIN shop_product p ON p.f ProductId=i.f ProductId
The JOIN shop_product_price SPP ON SPP. FProductId=i.f ProductId
AND SPP. DStartDate & lt;=CURDATE ()
AND IFNULL (SPP) dEndDate, CURDATE ()) & gt;=CURDATE ()
LEFT the JOIN (SELECT
T.f ItemId,
T.f OwnerId,
T.f SubinventoryId,
The SUM (t.f TransQuantity) qty
The FROM
Logis_transaction_temp t
WHERE
T.b Enable=TRUE
AND t.f SubinventoryId & gt; 4
GROUP BY t.f ItemId, t.f OwnerId, t.f SubinventoryId) t ON t.f ItemId=o.f ItemId
AND t.f OwnerId=o.f OwnerId
AND t.f SubinventoryId=o.f SubinventoryId
WHERE
P.f=SupplierId v. VendorId
AND SPP. FVendorId=v. VendorId
AND o.f SubinventoryId & gt; 4), 0) distribution center of the current inventory amount,
IFNULL ((SELECT
The SUM ((o.f Quantity + IFNULL (t.q ty, 0)) * SPP., fPurchasePrice)
The FROM
Logis_onhand o
The JOIN shop_item I ON i.f ItemId=o.f ItemId
The JOIN shop_product p ON p.f ProductId=i.f ProductId
The JOIN shop_product_price SPP ON SPP. FProductId=i.f ProductId
The JOIN comm_store s ON s. StoreId=o.f OwnerId
AND s. StoreType=1
AND SPP. DStartDate & lt;=CURDATE ()
AND IFNULL (SPP) dEndDate, CURDATE ()) & gt;=CURDATE ()
LEFT the JOIN (SELECT
T.f ItemId,
T.f OwnerId,
T.f SubinventoryId,
The SUM (t.f TransQuantity) qty
The FROM
Logis_transaction_temp t
WHERE
T.b Enable=TRUE
AND t.f SubinventoryId & lt; 5
GROUP BY t.f ItemId, t.f OwnerId, t.f SubinventoryId) t ON t.f ItemId=o.f ItemId
AND t.f OwnerId=o.f OwnerId
AND t.f SubinventoryId=o.f SubinventoryId
WHERE
P.f=SupplierId v. VendorId
AND SPP. FVendorId=v. VendorId
AND o.f SubinventoryId & lt; 5), 0) stores the current inventory amount,
IFNULL ((SELECT
The SUM ((o.f Quantity + IFNULL (t.q ty, 0)) * SPP., fPurchasePrice)
The FROM
Logis_onhand o
The JOIN shop_item I ON i.f ItemId=o.f ItemId
The JOIN shop_product p ON p.f ProductId=i.f ProductId
The JOIN shop_product_price SPP ON SPP. FProductId=i.f ProductId
The JOIN comm_store s ON s. StoreId=o.f OwnerId
AND s. StoreType=2
AND SPP. DStartDate & lt;=CURDATE ()
AND IFNULL (SPP) dEndDate, CURDATE ()) & gt;=CURDATE ()
LEFT the JOIN (SELECT
T.f ItemId,
T.f OwnerId,
T.f SubinventoryId,
The SUM (t.f TransQuantity) qty
The FROM
Logis_transaction_temp t
WHERE
T.b Enable=TRUE
AND t.f SubinventoryId & lt; 5
GROUP BY t.f ItemId, t.f OwnerId, t.f SubinventoryId) t ON t.f ItemId=o.f ItemId
AND t.f OwnerId=o.f OwnerId
AND t.f SubinventoryId=o.f SubinventoryId
WHERE
P.f=SupplierId v. VendorId
AND SPP. FVendorId=v. VendorId
AND o.f SubinventoryId & lt; 5), the current inventory amount, 0) napa stores
IFNULL ((SELECT
The SUM ((o.f Quantity + IFNULL (t.q ty, 0)) * SPP., fPurchasePrice)
The FROM
Logis_onhand o
The JOIN shop_item I ON i.f ItemId=o.f ItemId
The JOIN shop_product p ON p.f ProductId=i.f ProductId
The JOIN shop_product_price SPP ON SPP. FProductId=i.f ProductId
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull