Hey everyone i have changed the script to the following:
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,
FINAL AS INV_FINAL,
ORD AS INV_ORD,
DISCOUNT 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 key/Invoices.qvd]
(qvd)
where FINAL = 'Y'
and STORNOFLAG <> ('Y')
// and Date (IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'
and
(TYPE='A' //חשבונית מס
OR TYPE='C' //חשבונית מרכזת
OR TYPE='F'
OR TYPE='E'); //חשבונית מס קבלה; //חשבונית חול;
//פרטיי תנועת יומן
LEFT JOIN (INV_SALES_TEMP)
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 key/Fnctrans.qvd]
(qvd) Where FINAL ='Y';
// אני יכול להוריד את זה במידה ואני רוצה חשבוניות זמניות ולא סופיות
//שורות החשבונית
LEFT JOIN (INV_SALES_TEMP)
LOAD
IV,
PART AS IV_PART,
PRICE AS IV_PRICE,
QUANT/1000 AS IV_QUANT,
QPRICE AS IV_QPRICE,
"CURRENCY" AS IV_CURRENCY,
"LINE" AS IV_LINE,
ORDI AS IV_ORDI,
"T$PERCENT" AS IV_PERCENT,
UDATE AS IV_UDATE,
KLINE AS IV_KLINE,
TQUANT/1000 AS IV_TQUANT,
TUNIT,
TRANS AS IV_TRANS,
TOTPERCENT AS IV_TOTPERCENT ,
DUTYCOST AS IV_DUTYCOST,
COSTC AS IV_COSTC,
// IVCOST AS IV_IVCOST,
(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,
TYPE AS IV_TYPE,
CREDITCOST AS IV_CREDITCOST ,
BRANCH AS IV_BRANCH,
IVDATE AS IV_IVDATE,
ICURRENCY AS IV_ICURRENCY ,
IEXCHANGE AS IV_IEXCHANGE ,
CREDITFLAG AS IV_CREDITFLAG ,
AGENT AS IV_AGENT,
COMMISSION AS IV_COMMISSION
FROM [lib://Clinton key/InvoiceItems.qvd]
(qvd)
WHERE
(TYPE='A' //חשבונית מס
OR TYPE='C' //חשבונית מרכזת
OR TYPE='F' //חשבונית חול
OR TYPE='E' )//חשבונית מס קבלה)
;
// נתונים נוספים לשורת חשבונית
LEFT JOIN (INV_SALES_TEMP)
LOAD
IV,
KLINE AS IV_KLINE,
COSTC2 AS IVA_COSTC2 ,
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 key/InvoiceItemsa.qvd]
(qvd);
// isn't oblogated
// נתונים מכרטיס לקוח
LEFT JOIN (INV_SALES_TEMP)
LOAD
CUST_CUST AS INV_CUST,
// CUST_CUSTNAME AS INV_CUSTNAME,
CUST_CUSTDES AS INV_CUSTDES
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
// CUST_AGENTNAME
RESIDENT
CUSTOMERS;
// NOT ALL IS isn't oblogated
//נתונים מכרטיס פריט משורשרים לטבלת המכירות
LEFT JOIN (INV_SALES_TEMP)
LOAD
PART_PART AS IV_PART,
PART_COSTQUANT,
PART_COST as IV_PART_COST,
/*PART_FAMILYNAME as IV_FNAME ,*/
PART_SECONDCOST
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,
(QUANT/1000 *(IF (TYPE='N',(-1),1))) AS TRANS_QUANT,
ORDI AS TRANS_ORDI,
CUST AS TRANS_CUST,
PRICE AS TRANS_PRICE,
IV,
COST AS TRANS_COST,
// מייצג עלות 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 key/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;
INV_SALES:
LOAD
Distinct INV_CUST,
INV_CUSTDES,
INV_IVDATE AS IVDATE,
// INV_TOTPRICE,
// IF (INV_TOTPRICE > 0 AND DEBIT ='D','pos','neg') AS INV_TOTSIGN,
INV_INVNUM,
// CUST_AGENTNAME AS INV_AGENTNAME,
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
//שיניתי בכדי למנוע לופ//
IV_AGENT,
IV_BRANCH,
INV_TYPE,
IV_PART,
// IV_PRICE,
TRANS_COST,
// הוספתי בכדי לקבל את העלות//
IV_QUANT,
IV_QUANT*IF(DEBIT ='C',-1,1) AS RIV_QUANT,
IV_PERCENT,
// IV_FAMILYFTCODE,
// IV_COMMISSION,
// IV_IVCOST,
(IF(ISNULL(IV_TRANS) =-1,0,IV_TRANS)) AS IV_TRANS,
// IV_CREDITFLAG,
// מכפיל לזיכויים//
// ככה מוודאים שחשבוניות זיכוי בר
// (IF(DEBIT='C',(-1),1)) AS IVCAL_CREDIT,
// מחיר בשקלים לשורה
(IV_IVCOST * (IF (DEBIT ='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))
AS NIS_TOTPRICE,
// מחיר בדולרים לשורה
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2))
AS DOLLAR_TOTPRICE,
// // מחיר במטבע שלישי
// POSSIBLE EURO COIN
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(CURREG_EXCHANGE3 > 0 ,
CURREG_EXCHANGE3 , CUR_EXCHANGE3)
AS CURRENCY_TOTPRICE,
//חישוב רווח ועלות לשורה
// במרכאות
// TRANS_COST,TRANS_COSTFLAG,DATE(TRANS_CURDATE/60/24 +32143,'DD/MM/YYYY') AS TRANS_CURDATE ,MAXACC_COSTDATE,IV_PART_COST,
//עלות בשקל לתנועה
// ILS FOR A TRANSCATION
IF ((IsNull(TRANS_COST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
(IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1)))) AS NIS_COST,
// //עלות בדולר לשורת תנועה
IF ((IsNull(TRANS_SECONDCOST)= 0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <= MAXACC_COSTDATE),
TRANS_SECONDCOST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
PART_SECONDCOST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT ='C',(-1),1)) * (IF (IV_CREDITFLAG = 'Y',0,1))) AS DOLLAR_COST,
// // // עלות במטבע שלישי לשורת תנועה
// POSSIBLE EURO COIN
(IF ((IsNull(TRANS_SECONDCOST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1))) ) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) AS CURRENCY_COST,
// // עלויות ייצוא
// // המשתנה מייצג את השער שברצוננו לקחת האם מתוך תיק הייצוא (שורת החשבונית) או לפי שער המרה לאותו היום
// ILS
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))))) AS NIS_DUTYCOST,
// DOLLAR
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_DUTYCOST,
// POSSIBLE EURO COIN
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_DUTYCOST,
//עלויותת נוספות
//ILS
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) AS NIS_EXTCOST,
// DOLLAR
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_EXTCOST,
// POSSIBLE EURO COIN
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_EXTCOST,
/*
*/
//מפתח בטבלת מכירות
INV_CUST&'_'&IV_AGENT&'_'&IV_BRANCH&'_'&IV_PART&'_'&INV_IVDATE AS SALES_KEY
RESIDENT INV_SALES_TEMP
WHERE
(INV_TYPE='A' //חשבונית מס
OR INV_TYPE='C' //חשבונית מרכזת
OR INV_TYPE='F' //חשבונית חול
OR INV_TYPE='E' )
// and Date (INV_IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'
// ORDER BY
// IV_AGENT,
// IV_PART,
// INV_IVDATE
;
DROP TABLE INV_SALES_TEMP;
and the most important thing was to do: Distinct INV_CUST,
thank you all for helping!
