this is the first part:
CUSTOMERS:
LOAD
CUSTNAME AS CUST_CUSTNAME,
CUST AS CUST_CUST,
CUSTDES AS CUST_CUSTDES,
CTYPE AS CUST_CTYPE,
TERRITORY AS CUST_TERRITORY,
CUSTSTAT AS CUST_CUSTAT
// AGENT AS CUST_AGENTNAME
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
FROM [lib://Clinton key/customers.qvd]
(qvd)
WHERE CUSTSTAT <> -5;
Part_Balance: // מלאי לפריט לפי מחסנים
Load
PART AS BALANCE_PART,
BALANCE AS BALANCE_BALANCE,
WARHS AS BALANCE_WARHS
FROM [lib://CLINTON_SAMPLE/Balance.qvd]
(qvd);
LEFT JOIN (Part_Balance)
LOAD
WARHS as BALANCE_WARHS,
WARHSDES AS BALANCE_WARHSDES,
WARHSNAME AS BALANCE_WARHSNAME,
TYPE AS WARHS_TYPE
FROM [lib://CLINTON_SAMPLE/Warhs.qvd]
(qvd);
Parts: //טבלת פריטים
LOAD
PARTNAME,
PART AS PART_PART,
TYPE AS PART_TYPE,
PARTDES,
UNIT AS PART_UNIT,
STATUS AS PART_STATUS,
PRICE AS PART_PRICE,
SECONDPRICE AS PART_SECONDPRICE ,
// MGSP_CATEGORY as PART_MGCAT,
// MGSP_DEPARTMENT AS PART_MGDEP,
LASTPRICE AS PART_LASTPRICE,
LASTCURRENCY AS PART_LASTCURRENCY,
COST AS PART_COST,
FAMILY AS PART_FAMILY,
SECONDCOST AS PART_SECONDCOST,
COSTQUANT AS PART_COSTQUANT
FROM [lib://CLINTON_SAMPLE/parts.qvd]
(qvd);
LEFT JOIN (Parts)
LOAD
BALANCE_PART AS PART_PART,
sum( BALANCE_BALANCE) AS PART_BALANCE
Resident Part_Balance
where WARHS_TYPE <> 'W'
Group by BALANCE_PART;
PartFam: // משפחות מוצר
LOAD
FAMILY AS FAMILY_FAMILY,
FAMILYNAME,
FAMILYDES,
FAMILYTYPE,
TYPE AS FAMILY_TYPE
FROM [lib://CLINTON_SAMPLE/Family.qvd]
(qvd);
Branches: //סניפים
LOAD
BRANCH AS BRANCH_BRANCH ,
BRANCHNAME,
BRANCHDES
FROM [lib://CLINTON_SAMPLE/Branches.qvd]
(qvd);
Currencies: // מטבעות ושערי חליפין
LOAD
NAME,
CODE AS CUR_CODE,
CURRENCY AS CUR_CURRENCY,
EXCHANGE AS CUR_EXCHANGE,
EXCHDATE AS CUR_EXDATE
FROM [lib://CLINTON_SAMPLE/Currency.qvd]
(qvd);
Curreg: //שערי חליפין
LOAD
CURRENCY AS CUREG_CURRENCY,
EXCHANGE AS CURREG_EXCHANGE,
CURDATE AS CURREG_CURDATE
FROM [lib://CLINTON_SAMPLE/CurReg.qvd]
(qvd);
ACCDATES:
LOAD
CURDATE AS ACC_CURDATE,
COSTFLAG AS ACC_COSTFLAG,
COSTDATE AS ACC_COSTDATE
FROM [lib://CLINTON_SAMPLE/Accdates.qvd]
(qvd);
this is the second part:
Inv_Sales_temp: //טבלת מכירות כללית
LOAD
Date (IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
IVNUM AS INV_INVNUM,
DOC AS INV_DOC,
IV,
QPRICE AS INV_QPRICE,
TOTPRICE AS INV_TOTPRICE,
"CURRENCY" AS INV_CURRENCY,
CUST AS INV_CUST,
DEBIT,
VAT AS INV_VAT,
UDATE AS INV_UDATE, // "T$USER",
PRINTED AS INV_PRINTED ,
FINAL AS INV_FINAL,
ORD AS INV_ORD ,
DISCOUNT,
DISPRICE AS INV_DISCOUNT,
PAYDATE AS INV_PAYDATE,
FNCTRANS AS INV_FNCTRANS,
BOOKNUM AS INV_BOOKNUM,
AGENT AS INV_AGENT,
TYPE AS INV_TYPE,
CHECKING AS INV_CHECKING,
IMPFILE AS INV_IMPFILE,
STORNOFLAG AS INV_STORNOFLAG,
BRANCH AS INV_BRANCH
FROM [lib://CLINTON_SAMPLE/Invoices.qvd]
(qvd);
//פרטי ת.יומן
Left join
LOAD
FNCTRANS AS INV_FNCTRANS,
FNCNUM,
FINAL as FNC_FINAL,
CURDATE AS FNC_CURDATE,
FNCDATE,
EXCHANGE3 AS FNC_EXCHANGE3,
CURRENCY3 AS FNC_CURRENCY3,
IVNUM AS FNC_IVNUM,
EXCHANGE2 AS FNC_EXCHANGE2
FROM [lib://CLINTON_SAMPLE/Fnctrans.qvd]
(qvd)
Where FINAL ='Y'
;
Left Join (Inv_Sales_temp)
LOAD
IV,
PART AS IV_PART,
PRICE AS IV_PRICE,
QPRICE AS IV_QPRICE,
QUANT/1000 AS IV_QUANT,
"CURRENCY" AS IV_CURRENCY,
LINE AS IV_LINE,
ORDI AS IV_ORDI,
UDATE AS IV_UDATE,
KLINE AS IV_KLINE,
TQUANT/1000 AS IV_TQUANT,
TUNIT AS TUNIT,
TRANS AS IV_TRANS,
TOTPERCENT AS IV_TOTPERCENT,
(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,
TYPE AS IV_TYPE,
//PRSOURCE,
//STORNOFLAG,
COSTC AS IV_COSTC,
CREDITCOST AS IV_CREDITCOST,
//ACCOUNT,
BRANCH AS IV_BRANCH,
IVDATE AS IV_IVDATE,
ICURRENCY AS IV_ICURRENCY,
IEXCHANGE AS IV_IEXCHANGE,
//COUNTRY,
CREDITFLAG AS IV_CREDITFLAG,
AGENT AS IV_AGENT,
COMMISSION AS IV_COMMISSION,
DUTYCOST AS IV_DUTYCOST
FROM [lib://CLINTON_SAMPLE/InvoiceItems.qvd]
(qvd);
//נתונים נוספים לשורת חשבונית
Left Join
LOAD IV,
KLINE AS IV_KLINE,
COSTC2 AS IVA_COSTSC2,
COSTC3 AS IVA_COSTC3,
COSTC4 AS IVA_COSTC4,
COSTC5 AS IVA_COSTC5,
TOTPRICE AS IVA_TOTPRICE,
IVTAX AS IVA_IVTAX,
EXTCOST AS IVA_EXTCOST,
EXTCOST2 AS IVA_EXTCOST2,
BONUSFLAG AS IVA_BONUSFLAG
FROM [lib://CLINTON_SAMPLE/InvoiceItemsa.qvd]
(qvd);
// נתונים מכרטיס לקוח
left Join (Inv_Sales_temp)
LOAD
CUST_CUST,
CUST_CUSTNAME AS INV_CUSTNAME,
CUST_CUSTDES AS INV_CUSTDES
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
// CUST_AGENTNAME
RESIDENT
CUSTOMERS;
//נתונים מכרטיס הלקוח
/*
left Join (Inv_Sales_temp)
LOAD
CUST_CUST AS INV_CUST,
CUST_CUSTNAME AS INV_CUSTNAME,
CUST_CUSTDES AS INV_CUSTDES
Resident Custs;*/
//נתונים מכרטיס הפריט משורשרים לטבלת המכירות
left Join (Inv_Sales_temp)
LOAD
PARTNAME AS IV_PARTNAME,
PART_PART AS IV_PART,
PART_PRICE AS IV_PART_PRICE,
PART_SECONDPRICE AS IV_PART_SECONDPRICE,
PART_LASTPRICE AS IV_PARTLASTPRICE,
PART_LASTCURRENCY AS IV_PART_LASTCURRENCY,
PART_COSTQUANT,
PART_COST as IV_PART_COST,
PART_SECONDCOST
// PART_MGCAT as IV_PART_MGCAT,
// PART_MGDEP as IV_PART_MGDEP
Resident Parts;
//נתונים משורות התנועה המקושרת לחשבונית- עלות וכדומה
left Join (Inv_Sales_temp)
LOAD
(IF(ISNULL(TRANS)=-1,0, TRANS)) AS IV_TRANS,
DOC as TRANS_DOC,
PART AS TRANS_PART,
QUANT/1000 AS TRANS_QUANT,
ORDI AS TRANS_ORDI,
CUST AS TRANS_CUST,
PRICE AS TRANS_PRICE,
IV,
COST AS TRANS_COST,
SECONDCOST AS TRANS_SECONDCOST,
BRANCH AS TRANS_BRANCH,
CURRENCY AS TRANS_CURRENCY,
COGFLAG AS TRANS_COSTFLAG,
CURDATE AS TRANS_CURDATE
FROM [lib://CLINTON_SAMPLE/Trans.qvd]
(qvd);
//שער מטבע לתאריך החשבונית ולמטבע השורה
Left JOIN (INV_SALES_TEMP)
LOAD
CUR_CURRENCY AS INV_CURRENCY,
CUR_EXCHANGE
Resident Currencies;
LEFT JOIN (INV_SALES_TEMP)
LOAD
Date (CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
CURREG_CURRENCY AS INV_CURRENCY,
CURREG_EXCHANGE
Resident Curreg;
left JOIN (INV_SALES_TEMP)
LOAD
CUR_EXCHANGE AS CUR_EXCHANGE2
Resident Currencies Where CUR_CURRENCY = -2;
LEFT JOIN (INV_SALES_TEMP)
LOAD
Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
CURREG_EXCHANGE AS CURREG_EXCHANGE2
Resident Curreg Where CURREG_CURRENCY = -2;
// POSSIBLE EURO COIN
Left JOIN (INV_SALES_TEMP)
LOAD
CUR_EXCHANGE AS CUR_EXCHANGE3
Resident Currencies
Where CUR_CURRENCY =$(vForeignCurrency);
// שיניתי כי לא עבד בפעם הראשונה לא זהה את השדה
// POSSIBLE EURO COIN
left JOIN (INV_SALES_TEMP)
LOAD
Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')AS INV_IVDATE ,
CURREG_EXCHANGE AS CURREG_EXCHANGE3
Resident Curreg
Where CURREG_CURRENCY =$(vForeignCurrency);
// LINE 183 IN THE SALES CHART//
LEFT JOIN(INV_SALES_TEMP)
LOAD
ACC_COSTFLAG,
DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE
RESIDENT ACCDATES
WHERE ACC_COSTDATE<>0
GROUP BY ACC_COSTFLAG;
DROP TABLE ACCDATES;
