SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='￥#,##0.00;￥-#,##0.00';
SET TimeFormat='h:mm:ss';
SET DateFormat='YYYY/M/D';
SET TimestampFormat='YYYY/M/D h:mm:ss[.fff]';
SET MonthNames='一月;二月;三月;四月;五月;六月;七月;八月;九月;十月;十一月;十二月';
SET DayNames='周一;周二;周三;周四;周五;周六;周日';

Directory;

//CONNECT TO [Provider=SQLNCLI.1;Persist Security Info=True;User ID=sa;Initial Catalog=Emax;Data Source=192.105.179.3;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISERVER;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False] (XPassword is ESUNCVJNMTaUGWRX);

//OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=Emax;Data Source=192.105.179.3;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISERVER;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is WQPIOVJNMTaUGWBX);

CONNECT TO [Provider=SQLOLEDB.1;Password=jjljjl;Persist Security Info=True;User ID=sa;Initial Catalog=Emax;Data Source=192.105.179.3;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISERVER;Use Encryption for Data=False;Tag with column collation when possible=False](MODE IS WRITE);

LET i=0;
LET j=0;
LET START_YEAR_MONTH=DATE('2011-08-01','YYYY-MM'); //定义库存起始月份。合并月库存用。
LET NOWDAY=TODAY()-1; //当前日期，自动更新库存用
//LET NOWDAY='2011-11-01'; //当前日期，手动调整抽取时用
LET DAYNUM=DAY(NOWDAY); //截止当前日期当月的天数
LET YEAR_MONTH=DATE(NOWDAY,'YYYY-MM'); //当前月份
LET MONTH_START_DAY=MONTHSTART(NOWDAY); //当前月起始日
LET LAST_YEAR_MONTH=DATE(ADDMONTHS(NOWDAY,-1),'YYYY-MM'); //计算上一个月
LET LAST_MONTH_END_DAY=DAY(MONTHEND(NOWDAY,-1)); //上一个月月末日期
LET LAST_MONTH_START_DAY=MONTHSTART(NOWDAY,-1); //上一个月月初日期


//上月日库存抽取 月结后
SUB LASTMONTH_EVERYDAY_STOCK(_LAST_MONTH_END_DAY)
DO WHILE _LAST_MONTH_END_DAY>j
LET _LAST_STOCK_DATE=DATE(LAST_MONTH_START_DAY+j,'YYYY-MM-DD');
上月临时表:
SQL exec BI_History_Stock @Stock_Date='$(_LAST_STOCK_DATE)';

SQL select * from TRptDcubeStoc003dududu;

上月日库存临时表:
NOCONCATENATE LOAD
[Store] AS 店仓ID,
Style AS 款号,
Sku AS SKU,
DPrice AS 标准零售价,
Fob AS 成本价,
Qty AS 库存数量,
Amount AS 库存金额,
Cost AS 库存成本,
'$(_LAST_STOCK_DATE)' AS 库存日期
RESIDENT
上月临时表;
STORE 上月日库存临时表 INTO QVD\$(LAST_YEAR_MONTH)\$(_LAST_STOCK_DATE)库存.QVD;
DROP Table 上月临时表;
DROP Table 上月日库存临时表;
LET j=j+1;
LOOP
END SUB

//上月日库存合并
SUB LASTMONTH_ALL_STOCK(_LAST_MONTH_END_DAY)
LET j=0;
DO WHILE _LAST_MONTH_END_DAY>j
LET _LAST_STOCK_DATE=DATE(LAST_MONTH_START_DAY+j,'YYYY-MM-DD');
上月汇总库存临时表:
LOAD
店仓ID,
款号,
SKU,
标准零售价,
成本价,
库存数量,
库存金额,
库存成本,
库存日期
FROM
QVD\$(LAST_YEAR_MONTH)\$(_LAST_STOCK_DATE)库存.QVD(qvd);
LET j=j+1;
LOOP
STORE 上月汇总库存临时表 INTO QVD\$(LAST_YEAR_MONTH)\$(LAST_YEAR_MONTH)库存.QVD;
DROP TABLE 上月汇总库存临时表;
END SUB

//当月日库存合并
SUB NOWMONTH_ALL_STOCK(_DAYNUM)
LET j=0;
DO WHILE _DAYNUM>j
LET _NOW_STOCK_DATE=DATE(MONTH_START_DAY+j,'YYYY-MM-DD');
当月汇总库存临时表:
LOAD
店仓ID,
款号,
SKU,
标准零售价,
成本价,
库存数量,
库存金额,
库存成本,
库存日期
FROM
QVD\$(YEAR_MONTH)\$(_NOW_STOCK_DATE)库存.QVD(qvd);
LET j=j+1;
LOOP
STORE 当月汇总库存临时表 INTO QVD\$(YEAR_MONTH)\$(YEAR_MONTH)库存.QVD;
DROP TABLE 当月汇总库存临时表;
END SUB

//历史月库存合并
SUB EVERYMONTH_ALL_STOCK(X)
DO WHILE START_YEAR_MONTH<=YEAR_MONTH
历史月库存临时表:
LOAD
店仓ID,
款号,
SKU,
标准零售价,
成本价,
库存数量,
库存金额,
库存成本,
库存日期
FROM
QVD\$(START_YEAR_MONTH)\$(START_YEAR_MONTH)库存.QVD(qvd);
LET START_YEAR_MONTH=DATE(ADDMONTHS(START_YEAR_MONTH,1),'YYYY-MM');
LOOP
STORE 历史月库存临时表 INTO QVD\历史日库存表_TEMP.QVD;
DROP TABLE 历史月库存临时表;
END SUB


DO WHILE DAYNUM>i
LET STOCKDATE=DATE(MONTH_START_DAY+i,'YYYY-MM-DD');
// LET PATH=QvWorkPath;
// LET vCreateFolder = CreateFolder(PATH&'\QVD\'&YEAR_MONTH);

IF DAYNUM=1 THEN //每月第一天执行前一个月的库存处理 1表示月结日
CALL LASTMONTH_EVERYDAY_STOCK('$(LAST_MONTH_END_DAY)'); //重新抽取一遍上个月每天的库存 一般是月结后的库存
CALL LASTMONTH_ALL_STOCK('$(LAST_MONTH_END_DAY)') //上月日库存合并
END IF

临时表:
SQL exec BI_History_Stock @Stock_Date='$(STOCKDATE)'; //业务系统的库存查询接口，每个系统不一样。
SQL select * from TRptDcubeStoc003dududu; //查询出来的日库存记录（临时表）

日库存临时表:
NOCONCATENATE LOAD
[Store] AS 店仓ID,
Style AS 款号,
Sku AS SKU,
DPrice AS 标准零售价,
Fob AS 成本价,
Qty AS 库存数量,
Amount AS 库存金额,
Cost AS 库存成本,
'$(STOCKDATE)' AS 库存日期
RESIDENT
临时表;

STORE 日库存临时表 INTO QVD\$(YEAR_MONTH)\$(STOCKDATE)库存.QVD;
DROP Table 临时表;
DROP Table 日库存临时表;
LET i=i+1;
LOOP

CALL NOWMONTH_ALL_STOCK('$(DAYNUM)') //当月日库存合并
CALL EVERYMONTH_ALL_STOCK('$(YEAR_MONTH)') //月库存存合并成一个完整库存