AX 2009 销售订单导入导出
在AX 2009 中EXCEL的导入导出是经常会做的事情，很多客户用习惯了EXCEL，都喜欢直接在EXCEL里面去添加数据，不习惯在AX系统里去录数据，这样的话，我们就需要将EXCEL录入的数据导入到AX系统。
要想没有任何问题的将EXCEL里的数据,准备的录入到AX中，我们需要得先在AX中生成EXCEL的模版，用户将根据AX系统提供的模版进行录入数据。这样达到准确无误的将数据添加到AX系统中，具体的实现代码如下。
1，类，全局变量定义
public class RGD_SalesOrderLead extends runBaseBatch
{
#Excel
dialogfield dialogFilename;
dialogTabPage dialogTabPageOut;
dialogfield dialogFileNameOut;
dialogTabPage dialogTabPageIn;
dialogfield ifLeadOut;
NoYes leadOut;
DialogField dialogShowTemplate;
NoYesId showTemplate;
FileNameOpen fileName,fileNameAll,fileNameOut,fileNameOutAll;
Str callParm;
FormStringControl fileNameControl;
boolean retLead;
LedgerJournalTrans ledgerJournalTrans;
SalesId _salesId;
Object obj;
FormDataSource fds;
SalesTable salesTable;
Str 20 useridTime;
#File
#define.CurrentVersion(1)
#define.version1(1)
#localmacro.CurrentList
//filename
fileNameOut
#endmacro
}
2，画出操作界面
protected Object dialog()
{
DialogRunbase dialog = super();
Str filenameStr;
Str TxtName;
container Con;
;
dialogTabPageIn = dialog.addTabPage("销售订单导入");
dialogFilename = dialog.addFieldValue(typeid(FileNameOpen),fileName);
dialogTabPageOut = dialog.addTabPage("销售订单模板生成");
dialogFileNameOut = dialog.addFieldValue(typeid(FileNameSave),fileNameOut);
dialogShowTemplate = dialog.addFieldValue(typeid(NoYesId),showTemplate,"@INT3","@INT4");
//filenameStr = WinAPI::getSaveFileName(infolog.hWnd(), ["文本文件","*.xsl"],dialogFileNameOut.value() , "文件保存","",TxtName);
dialog.addMenuItemButton(MenuItemType::Action,identifierstr(Templates),DialogMenuItemGroup::CurrentGrp);
this.dialogSetFilenameLookup(dialog);
dialog.filenameLookupFilter();
//ifLeadOut = dialog.addFieldValue(typeid(noyes),leadOut,"确认生成模板");
return dialog;
}
public boolean getFromDialog()
{
int position;
int positionOut;
;
filename = dialogFilename.value();
fileNameOut = dialogFileNameOut.value();
//leadOut = ifLeadOut.value();
filenameAll = filename;
fileNameOutAll = fileNameOut;
position = strfind(filenameAll,"\\",1,strlen(filenameAll));
positionOut = strfind(fileNameOutAll,"\\",1,strlen(fileNameOutAll));
showTemplate = dialogShowTemplate.value();
if(filenameAll && position)
{
do
{
filenameAll = strins(filenameAll,"\\",position+1);
position = strfind(filenameAll,"\\",position + 2,strlen(filenameAll));
} while (position);
}
if(fileNameOutAll && positionOut)
{
do
{
fileNameOutAll = strins(fileNameOutAll,"\\",positionOut+1);
positionOut = strfind(fileNameOutAll,"\\",positionOut + 2,strlen(fileNameOutAll));
} while (positionOut);
}
return true;
}
public void dialogPostRun(DialogRunbase dialog)
{
super(dialog);
dialog.dialogForm().formRun().controlMethodOverload(true);
dialog.formRun().controlMethodOverloadObject(this);
}
void dialogSetFilenameLookup(DialogRunbase dialog)
{
FilePath filePath;
//fileNameOut.lookupButton(FormLookupButton::Always);
dialogFileNameOut.lookupButton(FormLookupButton::Always);
dialog.filenameLookupFileName(this.parmFileName());
dialog.filenameLookupFilter(this.filenameLookupFilter());
[filePath] = fileNameSplit(this.parmFileName());
dialog.filenameLookupInitialPath(filePath);
}
container filenameLookupFilter()
{
return ["@SYS28576",#AllFilesName+#XLS+';'+#XLSM+';'+#XLSX];
}
private container fileNameLookupFilterCon()
{
;
return ["SXL文件", #AllFilesName + #AllFilesExt];
}
3，值的类型验证判断
str 1024 getValue(COMVariant _comVariant)
{
str 1024 value;
str tmpSubStr;
;
//info(enum2str(_comVariant.variantType()));
switch(_comVariant.variantType())
{
case COMVariantType::VT_BSTR :
value=_comVariant.bStr();
break;
case COMVariantType::VT_EMPTY:
value='';
break;
case COMVariantType::VT_I1:
value=num2char(_comVariant.char());
break;
case COMVariantType::VT_I2:
value=int2str(_comVariant.short());
break;
case COMVariantType::VT_I4:
value= int2str(_comvariant.int());
if (!value)
value = int2str(_comVariant.long());
break;
case COMVariantType::VT_UI1:
value = int2str(_comvariant.byte());
break;
case COMVariantType::VT_UI2:
value = int2str(_comvariant.uShort());
break;
case COMVariantType::VT_UI4:
value= int2str(_comVariant.uInt());
if (!value)
value = int2str(_comVariant.uLong());
break;
case COMVariantType::VT_R4 :
value = num2str(_comVariant.float(),20,6,1,0);
tmpSubStr=substr(value,strfind(value,'.',1,strlen(value))+1,strLen(value));
if(str2int(tmpSubStr)==0)
value=num2str(_comVariant.float(),20,0,1,0);
break;
case COMVariantType::VT_R8 :
value = num2str(_comVariant.double(),20,6,1,0);
tmpSubStr=substr(value,strfind(value,'.',1,strlen(value))+1,strLen(value));
if(str2int(tmpSubStr)==0)
value=num2str(_comVariant.double(),20,0,1,0);
break;
case COMVariantType::VT_DECIMAL :
value = num2str(_comVariant.decimal(),20,6,1,0);
tmpSubStr=substr(value,strfind(value,'.',1,strlen(value))+1,strLen(value));
if(str2int(tmpSubStr)==0)
value=num2str(_comVariant.decimal(),20,0,1,0);
break;
case COMVariantType::VT_DATE :
value = strfmt('%1',_comVariant.date());
break;
case COMVariantType::VT_CY :
value = num2str(_comVariant.currency(),20,6,1,0);
tmpSubStr=substr(value,strfind(value,'.',1,strlen(value))+1,strLen(value));
if(str2int(tmpSubStr)==0)
value=num2str(_comVariant.currency(),20,0,1,0);
}
return strLtrim(strRtrim(value));
}
4，缓存上次输入的路径
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container packedClass)
{
Version version = runbase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
5，属性的定义
Str parmCallParm(Str _parm = callParm)
{
;
callParm = _parm;
return callParm;
}
FormDataSource parmfds(FormDataSource _fds = fds)
{
;
fds = _fds;
return fds;
}
FileName parmFileName(Filename _filename = filename)
{
;
filename = _filename;
return filename;
}
Object parmobj(Object _obj = obj)
{
;
obj = _obj;
return obj;
}
SalesTable parmSalesTable(SalesTable _salesTable = salesTable)
{
;
salesTable = _salesTable;
return salesTable;
}
6，类构造
static client public RGD_SalesOrderLead construct()
{
return new RGD_SalesOrderLead();
}
7，按钮事件
void mnuItm_1_Clicked()
{
retlead = true;
//this.getFromDialog();
this.run();
}
8，逻辑调用
static void main(Args _args)
{
RGD_SalesOrderLead import;
;
import = RGD_SalesOrderLead::construct();
import.parmCallParm(_args.parm());
if(_args.parm() == "SalesTable" && _args.record())
import.parmSalesTable(_args.record());
import.parmobj(_args.caller());
if(_args.record())
{
import.parmfds(_args.caller().dataSource());
}
if (import && import.prompt())
{
import.run();
}
}
void run()
{
salesLine _salesLine;
;
startLengthyOperation();
if(callParm == "SalesTable")
{
if(retlead)
{
this.runTmpSalesTable();
info("@RGD420");
}
else
{
if(filename == "")
{
info("@RGD421");
return;
}
this.runImportSalesTable();
info("@HFM229");
}
}
endLengthyOperation();
}
void runImportSalesTable()
{
SysExcelApplication excelApp;
SysExcelWorkbooks sysExcelWorkbooks;
SysExcelWorkbook sysExcelWorkbook;
SysExcelWorksheets sysExcelWorksheets;
SysExcelWorksheet sysExcelWorksheet;
SysExcelWorksheet sysExcelWorksheetLine;
SysExcelCells sysExcelCells;
SysExcelCells sysExcelCellsLine;
SysExcelCell sysExcelCell;
SysExcelCell sysExcelCellLine;
SalesLine salesline;
SalesTable _stl;
SalesLine _sl;
SalesLine salesUpd;
InventDim inventDim;
InventTable inventTable;
CustTable tcustTable;
real salesPriceDiscCustAccount;
RGD_ImportBatchId importBatchId;
PriceDiscTable priceDiscTable;
Address taddress;
SalesId _sid;
SalesId _sidLine;
RGD_SalesorderNum _excelOrderNum;
AccountNum _accountNum;
RGD_SalesTableHelper _salesTableHelper;
SalesTable _sth;
str 100 sheetname;
int _count = 1;
int _countLine = 1;
//str itemid;
Str unitid;
Str InventLocationId;
boolean canInsertflag;
SalesPrice _SalesPrice;
TaxItemGroup _taxItemGroup;
boolean ifemptyBreak;
boolean insertFlg;
name shipTo;
NumberSeq num;
NumberSeq numBatchCode;
boolean insertSalesTableFlg = true;
boolean insertSalesLineFlg = true;
Str 30 custSearchName;
CustTable _custTable;
DirPartyTable dirPartTable;
str test;
boolean retRun;
;
useridTime = curuserid()+int2str(timenow());
insertFlg = true;
sheetname = "Header";
if( filename == "" )
return;
excelApp = SysExcelApplication::construct();
excelApp.displayAlerts(false);
if (excelApp.workbooks().count())
{
excelApp.workbooks().close();
}
sysExcelWorkbooks = excelApp.workbooks();
sysExcelWorkbooks.open(filenameAll);
excelApp.displayAlerts(true);
sysExcelWorkbook = sysExcelWorkbooks.item(1);
sysExcelWorksheets = sysExcelWorkbook.worksheets();
sysExcelWorksheet = sysExcelWorksheets.itemFromName(sheetname);
sysExcelWorksheetLine = sysExcelWorksheets.itemFromName("Line");
if (!sysExcelWorksheet)
{
throw error("@RGD116");
}
sysExcelCells = sysExcelWorksheet.cells();
sysExcelCellsLine = sysExcelWorksheetLine.cells();
retRun = false;
numBatchCode = NumberSeq::newGetNum(SalesParameters::RGD_ImportBatchId());
importBatchId = numBatchCode.num();
try
{
ttsbegin;
_salesTableHelper.clear();
do
{
_count++;
_salesTableHelper.RGD_SalesorderNum = this.getValue(sysExcelCells.item(_count,1).value());
if(!_salesTableHelper.RGD_SalesorderNum)
{
insertSalesTableFlg = false;
continue;
}
select firstonly * from _custTable
where _custTable.AccountNum == this.getValue(sysExcelCells.item(_count,3).value());
if(!_custTable)
{
info(strfmt("@RGD171",this.getValue(sysExcelCells.item(_count,3).value()),int2str(_count)));
retRun = true;
continue;
}
num = NumberSeq::newGetNum(SalesParameters::numRefSalesIdNRR());
_stl.SalesId = num.num();
_stl.RGD_SalesorderSourse = RGD_SalesorderSourse::NRR;
_stl.CustAccount = this.getValue(sysExcelCells.item(_count,3).value());
_stl.InvoiceAccount = this.getValue(sysExcelCells.item(_count,3).value());
_stl.RGD_SalesRemark = this.getValue(sysExcelCells.item(_count,5).value());//Ryan Add 20110823 begin
_stl.RGD_ImportBatchId = importBatchId; //Ryan Add 20110823 end
_stl.CurrencyCode = companyinfo::find().CurrencyCode;
_stl.SalesType = SalesType::Sales;
_stl.RGD_OrderDate = str2date(this.getValue(sysExcelCells.item(_count,4).value()),321);
_stl.initValue();
_stl.initFromCustTable();
_stl.initInvoiceAccount();
_stl.insert();
_salesTableHelper.UseridTime = useridTime;
_salesTableHelper.Salesid = _stl.SalesId;
_salesTableHelper.insert();
} while(insertSalesTableFlg);
do
{
_countLine++;
_excelOrderNum = this.getValue(sysExcelCellsLine.item(_countLine,1).value());
if(!_excelOrderNum)
{
insertSalesLineFlg = false;
continue;
}
select firstonly * from _salesTableHelper
where _salesTableHelper.RGD_SalesorderNum == _excelOrderNum
&& _salesTableHelper.UseridTime == useridTime;
if(!_salesTableHelper.RGD_SalesorderNum)
{
info(strfmt("@RGD169",this.getValue(sysExcelCellsLine.item(_countLine,1).value()),int2str(_countLine)));
retRun = true;
continue;
}
select firstonly * from inventTable
where inventTable.ItemId == this.getValue(sysExcelCellsLine.item(_countLine,2).value());
if(!inventTable)
{
info(strfmt("@RGD170",this.getValue(sysExcelCellsLine.item(_countLine,2).value()),int2str(_countLine)));
retRun = true;
continue;
}
_sl.clear();
_sl.SalesId = _salesTableHelper.Salesid;
_sl.ItemId = this.getValue(sysExcelCellsLine.item(_countLine,2).value());
_sl.initValue();
_sl.SalesPrice = inventTable.RGD_CoverPrice;
_sl.initFromSalesTable(SalesTable::find(_salesTableHelper.Salesid));
_sl.initFromInventTable(inventTable::find(_sl.ItemId));
_sl.SalesQty = str2num(this.getValue(sysExcelCellsLine.item(_countLine,3).value()));
_sl.RGD_GrossPrice = Str2num(this.getValue(sysExcelCellsLine.item(_countLine,4).value()));
// CUS-Add by sherr on 2011-09-08 Begin
/*
_sl.HD_VendAccount = this.getValue(sysExcelCellsLine.item(_countLine,9).value()) ;
_sl.HD_PurchId = this.getValue(sysExcelCellsLine.item(_countLine,10).value()) ;
_sl.HD_LinePercent = Str2num(this.getValue(sysExcelCellsLine.item(_countLine,11).value())) ;
_sl.HD_PurchVatCode = this.getValue(sysExcelCellsLine.item(_countLine,12).value()) ;
_sl.HD_SalesPackingSlipId = this.getValue(sysExcelCellsLine.item(_countLine,13).value()) ;
_sl.HD_PurchPackingSlipId = this.getValue(sysExcelCellsLine.item(_countLine,14).value()) ;
*/
// CUS-Add by sherr on 2011-09-08 End
_sth = SalesTable::find(_sl.SalesId);
tcustTable = CustTable::find(_sth.CustAccount);
if(tcustTable.InventSiteId != "")
{
inventDim.InventSiteId = tcustTable.InventSiteId;
}
else
{
//info(strfmt("@RGD472",tcustTable.AccountNum,int2str(_countLine)));//Ryan modify 20111014 begin
info(tcustTable.AccountNum + "客户的站点不存在!"); //Ryan modify 20111014 end
retRun = true;
continue;
}
if(tcustTable.InventLocation != "")
{
inventDim.InventLocationId = tcustTable.InventLocation;
}
else
{
//info(strfmt("@RGD475",tcustTable.AccountNum,int2str(_countLine)));//Ryan modify 20111014 begin
info(tcustTable.AccountNum+"客户的仓库不存在!"); //Ryan modify 20111014 end
retRun = true;
continue;
}
inventDim = InventDim::findOrCreate(inventDim);
_sl.InventDimId = inventDim.inventDimId;
test = this.getValue(sysExcelCellsLine.item(_countLine,7).value());
if(this.getValue(sysExcelCellsLine.item(_countLine,7).value()) == "1")
{
_sl.RGD_SalesType = RGD_SalesType::replaceSales;//Ryan modify 1,2 Correspond RGD_SalesType
}
else
{
_sl.RGD_SalesType = RGD_SalesType::BagSlaes;
}
shipTo = this.getValue(sysExcelCellsLine.item(_countLine,8).value());
select firstonly * from taddress
where taddress.Name == shipTo
&& taddress.AddrTableId == tcustTable.TableId
&& taddress.AddrRecId == tcustTable.RecId
&& taddress.type == AddressType::Delivery;
if(taddress)
{
_sl.DeliveryName = taddress.Name;
}
else
{
info(strfmt("@RGD487",tcustTable.AccountNum,shipTo,int2str(_countLine)));
retRun = true;
continue;
}
salesPriceDiscCustAccount = RGD_SalesPriceDiscCalc::CalcSODisc(inventTable.ItemGroupId,tcustTable.AccountNum,SystemDateGet(),tcustTable.CustGroup);
_sl.RGD_SalesPriceAct = inventTable.RGD_CoverPrice * ((100- salesPriceDiscCustAccount) / 100);
_sl.ShippingDateRequested = str2date(this.getValue(sysExcelCellsLine.item(_countLine,6).value()),321);
//_sl.ReceiptDateRequested = datenull(); //Ryan Remark 20110905
_sl.createLine(true,
false,
false,
true,
true,
_sl.salesPrice ? false : true,
_stl.Reservation==ItemReservation::Automatic,
true);
salesUpd = SalesLine::findRecId(_sl.RecId,true);
if(salesUpd.RecId)
{
salesUpd.ShippingDateRequested = str2date(this.getValue(sysExcelCellsLine.item(_countLine,6).value()),321);
//salesUpd.ReceiptDateRequested = datenull(); //Ryan modify 20110905 begin
salesUpd.ReceiptDateRequested = salesUpd.ShippingDateRequested;//Ryan modify 20110905 end
salesUpd.SalesPrice = inventTable.RGD_CoverPrice;
salesUpd.LinePercent = salesPriceDiscCustAccount;
salesUpd.LineAmount = _sl.SalesQty * _sl.RGD_SalesPriceAct;
salesUpd.DeliveryName = shipTo;
salesUpd.RGD_ImportBatchId = importBatchId;
salesUpd.Dimension[2] = inventTable.Dimension[2];
salesUpd.Dimension[5] = inventTable.Dimension[5];
salesUpd.doUpdate();
}
} while(insertSalesLineFlg);
if(retRun)
{
throw error("@RGD477");
}
ttscommit;
}
catch (Exception::Error)
{
if(excelApp)
excelApp.quit();
}
if(excelApp)
excelApp.quit();
else
{
if(excelApp)
excelApp.quit();
}
if(fds)
{
fds.reread();
fds.refresh();
}
delete_from _salesTableHelper
where _salesTableHelper.UseridTime == useridTime;
}
void RGD_importHistoryData()//add by sherry
{
SysExcelApplication excelApp;
SysExcelWorkbooks sysExcelWorkbooks;
SysExcelWorkbook sysExcelWorkbook;
SysExcelWorksheets sysExcelWorksheets;
SysExcelWorksheet sysExcelWorksheet;
SysExcelWorksheet sysExcelWorksheetLine;
SysExcelCells sysExcelCells;
SysExcelCells sysExcelCellsLine;
SysExcelCell sysExcelCell;
SysExcelCell sysExcelCellLine;
SalesTable newSalesTable ; //add by sherry
SalesLine newSalesLine ;
SalesLine salesline;
SalesTable _stl;
SalesLine _sl;
SalesLine salesUpd;
InventDim inventDim;
InventTable inventTable;
CustTable tcustTable;
real salesPriceDiscCustAccount;
RGD_ImportBatchId importBatchId;
PriceDiscTable priceDiscTable;
Address taddress;
SalesId _sid;
SalesId _sidLine;
RGD_SalesorderNum _excelOrderNum;
AccountNum _accountNum;
RGD_SalesTableHelper _salesTableHelper;
SalesTable _sth;
str 100 sheetname;
int _count = 1;
int _countLine = 1;
Str unitid;
Str InventLocationId;
boolean canInsertflag;
SalesPrice _SalesPrice;
TaxItemGroup _taxItemGroup;
boolean ifemptyBreak;
boolean insertFlg;
name shipTo;
NumberSeq num;
NumberSeq numBatchCode;
boolean insertSalesTableFlg = true;
boolean insertSalesLineFlg = true;
Str 30 custSearchName;
CustTable _custTable;
DirPartyTable dirPartTable;
str test;
boolean retRun;
;
useridTime = curuserid()+int2str(timenow());
insertFlg = true;
sheetname = "Header";
if( filename == "" )
return;
excelApp = SysExcelApplication::construct();
excelApp.displayAlerts(false);
if (excelApp.workbooks().count())
{
excelApp.workbooks().close();
}
sysExcelWorkbooks = excelApp.workbooks();
sysExcelWorkbooks.open(filenameAll);
excelApp.displayAlerts(true);
sysExcelWorkbook = sysExcelWorkbooks.item(1);
sysExcelWorksheets = sysExcelWorkbook.worksheets();
sysExcelWorksheet = sysExcelWorksheets.itemFromName(sheetname);
sysExcelWorksheetLine = sysExcelWorksheets.itemFromName("Line");
if (!sysExcelWorksheet)
{
throw error("@RGD116");
}
sysExcelCells = sysExcelWorksheet.cells();
sysExcelCellsLine = sysExcelWorksheetLine.cells();
retRun = false;
numBatchCode = NumberSeq::newGetNum(SalesParameters::RGD_ImportBatchId());
importBatchId = numBatchCode.num();
try
{
ttsbegin;
_salesTableHelper.clear();
//insert salesTable
do
{
_count++;
_salesTableHelper.RGD_SalesorderNum = this.getValue(sysExcelCells.item(_count,1).value());
if(!_salesTableHelper.RGD_SalesorderNum)
{
insertSalesTableFlg = false;
continue;
}
select firstonly * from _custTable
where _custTable.AccountNum == this.getValue(sysExcelCells.item(_count,3).value());
if(!_custTable)
{
info(strfmt("@RGD171",this.getValue(sysExcelCells.item(_count,3).value()),int2str(_count)));
retRun = true;
continue;
}
newSalesTable.SalesId = this.getValue(sysExcelCells.item(_count,1).value());
newSalesTable.RGD_SalesorderSourse = RGD_SalesorderSourse::NRR;
newSalesTable.CustAccount = this.getValue(sysExcelCells.item(_count,3).value());
newSalesTable.InvoiceAccount = this.getValue(sysExcelCells.item(_count,3).value());
newSalesTable.RGD_SalesRemark = this.getValue(sysExcelCells.item(_count,5).value());
newSalesTable.RGD_ImportBatchId = importBatchId;
newSalesTable.CurrencyCode = companyinfo::find().CurrencyCode;
newSalesTable.SalesType = SalesType::Sales;
newSalesTable.RGD_OrderDate = str2date(this.getValue(sysExcelCells.item(_count,4).value()),321);
newSalesTable.initValue();
newSalesTable.initFromCustTable();
newSalesTable.initInvoiceAccount();
newSalesTable.insert();
} while(insertSalesTableFlg);
do
{
_countLine++;
_excelOrderNum = this.getValue(sysExcelCellsLine.item(_countLine,1).value());
if(!_excelOrderNum)
{
insertSalesLineFlg = false;
continue;
}
select firstonly * from _salesTableHelper
where _salesTableHelper.RGD_SalesorderNum == _excelOrderNum
&& _salesTableHelper.UseridTime == useridTime;
if(!_salesTableHelper.RGD_SalesorderNum)
{
info(strfmt("@RGD169",this.getValue(sysExcelCellsLine.item(_countLine,1).value()),int2str(_countLine)));
retRun = true;
continue;
}
newSalesLine.clear();
newSalesLine.SalesId = this.getValue(sysExcelCellsLine.item(_countLine,1).value()) ;
newSalesLine.ItemId = this.getValue(sysExcelCellsLine.item(_countLine,2).value());
newSalesLine.initValue();
newSalesLine.SalesPrice = inventTable.RGD_CoverPrice;
newSalesLine.initFromSalesTable(SalesTable::find(_salesTableHelper.Salesid));
newSalesLine.initFromInventTable(inventTable::find(_sl.ItemId));
newSalesLine.SalesQty = str2num(this.getValue(sysExcelCellsLine.item(_countLine,3).value()));
newSalesLine.RGD_GrossPrice = Str2num(this.getValue(sysExcelCellsLine.item(_countLine,4).value()));
// CUS-Add by sherr on 2011-09-08 Begin
newSalesLine.HD_VendAccount = this.getValue(sysExcelCellsLine.item(_countLine,9).value()) ;
newSalesLine.HD_PurchId = this.getValue(sysExcelCellsLine.item(_countLine,10).value()) ;
newSalesLine.HD_LinePercent = Str2num(this.getValue(sysExcelCellsLine.item(_countLine,11).value())) ;
newSalesLine.HD_PurchVatCode = this.getValue(sysExcelCellsLine.item(_countLine,12).value()) ;
newSalesLine.HD_SalesPackingSlipId = this.getValue(sysExcelCellsLine.item(_countLine,13).value()) ;
newSalesLine.HD_PurchPackingSlipId = this.getValue(sysExcelCellsLine.item(_countLine,14).value()) ;
// CUS-Add by sherr on 2011-09-08 End
newSalesLine.createLine();
test = this.getValue(sysExcelCellsLine.item(_countLine,7).value());
if(this.getValue(sysExcelCellsLine.item(_countLine,7).value()) == "1")
{
_sl.RGD_SalesType = RGD_SalesType::replaceSales;//Ryan modify 1,2 Correspond RGD_SalesType
}
else
{
_sl.RGD_SalesType = RGD_SalesType::BagSlaes;
}
shipTo = this.getValue(sysExcelCellsLine.item(_countLine,8).value());
select firstonly * from taddress
where taddress.Name == shipTo
&& taddress.AddrTableId == tcustTable.TableId
&& taddress.AddrRecId == tcustTable.RecId
&& taddress.type == AddressType::Delivery;
if(taddress)
{
_sl.DeliveryName = taddress.Name;
}
else
{
info(strfmt("@RGD487",tcustTable.AccountNum,shipTo,int2str(_countLine)));
retRun = true;
continue;
}
salesPriceDiscCustAccount = RGD_SalesPriceDiscCalc::CalcSODisc(inventTable.ItemGroupId,tcustTable.AccountNum,SystemDateGet(),tcustTable.CustGroup);
_sl.RGD_SalesPriceAct = inventTable.RGD_CoverPrice * ((100- salesPriceDiscCustAccount) / 100);
_sl.ShippingDateRequested = str2date(this.getValue(sysExcelCellsLine.item(_countLine,6).value()),321);
//_sl.ReceiptDateRequested = datenull(); //Ryan Remark 20110905
_sl.createLine(true,
false,
false,
true,
true,
_sl.salesPrice ? false : true,
_stl.Reservation==ItemReservation::Automatic,
true);
salesUpd = SalesLine::findRecId(_sl.RecId,true);
if(salesUpd.RecId)
{
salesUpd.ShippingDateRequested = str2date(this.getValue(sysExcelCellsLine.item(_countLine,6).value()),321);
//salesUpd.ReceiptDateRequested = datenull(); //Ryan modify 20110905 begin
salesUpd.ReceiptDateRequested = salesUpd.ShippingDateRequested;//Ryan modify 20110905 end
salesUpd.SalesPrice = inventTable.RGD_CoverPrice;
salesUpd.LinePercent = salesPriceDiscCustAccount;
salesUpd.LineAmount = _sl.SalesQty * _sl.RGD_SalesPriceAct;
salesUpd.DeliveryName = shipTo;
salesUpd.RGD_ImportBatchId = importBatchId;
salesUpd.Dimension[2] = inventTable.Dimension[2];
salesUpd.Dimension[5] = inventTable.Dimension[5];
salesUpd.doUpdate();
}
} while(insertSalesLineFlg);
if(retRun)
{
throw error("@RGD477");
}
ttscommit;
}
catch (Exception::Error)
{
if(excelApp)
excelApp.quit();
}
if(excelApp)
excelApp.quit();
else
{
if(excelApp)
excelApp.quit();
}
if(fds)
{
fds.reread();
fds.refresh();
}
delete_from _salesTableHelper
where _salesTableHelper.UseridTime == useridTime;
}
void runTmpSalesTable()
{
SysExcelApplication excApp;
SysExcelWorkbooks excBooks;
SysExcelWorkbook excBook;
SysExcelWorksheets excSheets;
SysExcelWorksheet excSheet;
SysExcelWorksheet excSheetLine;
SysExcelCells excCells;
SysExcelCells excCellsLine;
SysExcelCell excCell;
SysExcelCell excCellLine;
SysExcelStyles excStyles;
SysExcelStyle ExcelStyle;
SysExcelFont ExcelFont;
SysExcelRange excRange;
boolean retl;
COM cell;
com columns;
com Rows;
COM xslInterior;
;
try
{
excApp = SysExcelApplication::construct();
excBooks = excApp.workbooks();
excBook = excBooks.add();
excStyles = excBook.styles();
ExcelStyle = excStyles.add("bold");
ExcelFont = ExcelStyle.font();
ExcelFont.bold(true);
ExcelFont.italic(true);
excSheets = excBook.worksheets();
excSheet = excSheets.itemFromNum(1);
excSheet.name("Header");
excCells = excSheet.cells();
excCells.item(1,1).value("Sales Order Number");
excCells.item(1,1).comObject().ColumnWidth(25);
excCells.item(1,2).value("Sales Order Type");
excCells.item(1,2).comObject().ColumnWidth(25);
excCells.item(1,3).value("Wholessalor");
excCells.item(1,3).comObject().ColumnWidth(18);
excCells.item(1,4).value("Order Date");
excCells.item(1,4).comObject().ColumnWidth(18);
excCells.item(1,5).value("Order Reference");
excCells.item(1,5).comObject().ColumnWidth(25);
//excCells.item(1,6).value("Order Remark");//Ryan Add 20110823 begin
//excCells.item(1,6).comObject().ColumnWidth(60);
excSheet.cells().range('A1:E1').style('bold');
excSheet.cells().range('A1:E1').horizontalAlignment(3);//Ryan Add 20110823 begin
excSheetLine = excSheets.itemFromNum(2);
excSheetLine.name("Line");
excCellsLine = excSheetLine.cells();
excCellsLine.item(1,1).value("Sales Order Number");
excCellsLine.item(1,1).comObject().ColumnWidth(25);
excCellsLine.item(1,2).value("Edition");
excCellsLine.item(1,2).comObject().ColumnWidth(10);
excCellsLine.item(1,3).value("QTY");
excCellsLine.item(1,3).comObject().ColumnWidth(8);
excCellsLine.item(1,4).value("Gross Price");
excCellsLine.item(1,4).comObject().ColumnWidth(15);
excCellsLine.item(1,5).value("Discount");
excCellsLine.item(1,5).comObject().ColumnWidth(15);
excCellsLine.item(1,6).value("DELDATE");
excCellsLine.item(1,6).comObject().ColumnWidth(15);
excCellsLine.item(1,7).value("SalesType");
excCellsLine.item(1,7).comObject().ColumnWidth(15);
excCellsLine.item(1,8).value("Ship-to");
excCellsLine.item(1,8).comObject().ColumnWidth(12);
excSheetLine.cells().range('A1:H1').style('bold');
excSheetLine.cells().range('A1:H1').horizontalAlignment(3);
retl = this.getFromDialog();
excBook.saveAs(fileNameOut);
if(showTemplate)
{
excApp.visible(true);
}
else
{
excApp.visible(false);
}
}
catch(exception::Error)
{
if(!excApp)
excApp.quit();
}
}
