class JKG_CustomerPriceDownload extends RunBaseBatch
{
DialogField customer;
DialogField dlgFilePath;
Dialog dialog;
str dlgFilePathVal;
CustAccount custAccount;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
custAccount,
dlgFilePathVal
#endmacro
}
protected Object dialog()
{
;
dialog = super();
dialog.caption("@JKG6832");
customer = dialog.addField(typeid(CustAccount),'@SYS74095','@SYS302');
dlgFilePath = dialog.addField(typeid(FileNameSave),'@SYS89006','@SYS72080');
//dialog.filenameLookupInitialPath('C:\\');
dialog.filenameLookupFilter(['@SYS28576', '*.xls']);
return dialog;
}
public boolean getFromDialog()
{
;
custAccount = customer.value();
dlgFilePathVal = dlgFilePath.value();
return super();
}
void insertToExcelFile()
{
COM comApplication;
COM comWorkbooks;
COM comWorkbook;
COM comWorksheet;
COM comShapes;
COMVariant variant = new COMVariant();
COMVariant xpos = new COMVariant();
COMVariant ypos = new COMVariant();
COMVariant state = new COMVariant();
COMVariant width = new COMVariant();
COMVariant height = new COMVariant();
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
SysExcelWorkSheet sysExcelWorkSheet;
int row = 1;
int rowcount;
CustTable custTable;
InventTable inventTable;
ItemOwningLocationJKG itemOwningLocationJKG;
JKG_TemperatureCodes JKG_TemperatureCodes;
JKG_ItemCodes JKG_ItemCodes;
// B2AnalysisTableJKG b2AnalysisTableJKG;
PriceDiscTable priceDiscTable,priceDiscTable1;
// ItemSalesAnalysisCodesJKG itemSalesAnalysisCodesJKG;
ItemSalesAnalysisTableJKG analysisTable;
SalesAnalysisNumBaseJKG analysisNum;
Description description;
ResourceNode resourceNode;
FilePath imagename;
boolean workSheetAdded = false;
;
//Create the Excel app and grab the workbooks
comApplication = new COM('Excel.application');
comWorkBooks = comApplication.workbooks();
//Wrap the rest in an exception to make sure excel is closed
try
{
//Create a new workbook and get a reference to it
variant.int(-4167);
comWorkBook = comWorkBooks.add(variant);
comWorkSheet = comApplication.activeSheet();
//Add some text to the worksheet
sysExcelWorkSheet = SysExcelWorkSheet::construct(MSOfficeVersion::Office2007, comWorksheet);
styles = comWorkbook.styles();
style = styles.add('@SYS4925');
font = style.font();
font.bold(true);
//font.color(255);
cells = sysExcelWorkSheet.cells();
cells.range('A:A').numberFormat('@');
// Make a title row
//sysExcelWorkSheet.name('@JKG6826');
sysExcelWorkSheet.cells().item(6,1).value('@SYS59617');
sysExcelWorkSheet.cells().item(6,2).value(Date2Str(systemDateGet(),123,2,2,2,2,4));
sysExcelWorkSheet.cells().item(7,1).value('@JKG6827');
sysExcelWorkSheet.cells().item(8,1).value('@SYS24704');
sysExcelWorkSheet.cells().item(9,1).value('@SYS80109');
sysExcelWorkSheet.cells().item(10,1).value('@SYS7572');
sysExcelWorkSheet.cells().item(11,1).value('@SYS30257');
sysExcelWorkSheet.cells().item(12,1).value('@SYS26890');
sysExcelWorkSheet.cells().item(13,1).value();
sysExcelWorkSheet.cells().item(8,2).value(custAccount);
select custTable where custTable.AccountNum==custAccount;
sysExcelWorkSheet.cells().item(8,4).value(custTable.Name);
sysExcelWorkSheet.cells().item(9,2).value('@SYS1150');
select priceDiscTable where priceDiscTable.AccountRelation==custAccount;
sysExcelWorksheet.cells().item(10,2).value(priceDiscTable.Currency);
sysExcelWorkSheet.cells().item(14,1).value('@SYS80470');
sysExcelWorksheet.cells().item(14,2).value('@SYS8631');
sysExcelWorksheet.cells().item(14,3).value('@SYS59620');
sysExcelWorksheet.cells().item(14,4).value('@JKG86');
sysExcelWorksheet.cells().item(14,5).value('@JKG6828');
sysExcelWorksheet.cells().item(14,6).value('@SYS74604');
sysExcelWorksheet.cells().item(14,7).value('@JKG6829');
sysExcelWorksheet.cells().item(14,8).value('@JKG4935');
sysExcelWorksheet.cells().item(14,9).value('@JKG6830');
sysExcelWorksheet.cells().item(14,10).value('@SYS79913');
sysExcelWorksheet.cells().item(14,11).value('@SYS6437');
sysExcelWorksheet.cells().item(14,12).value('@JKG6831');
sysExcelWorksheet.cells().item(14,13).value('@SYS7437');
sysExcelWorksheet.cells().item(14,14).value('@SYS28731');
sysExcelWorksheet.cells().item(14,15).value('@JKG1552');
sysExcelWorksheet.cells().item(14,16).value('@JKG1417');
sysExcelWorksheet.cells().item(14,17).value('@SYS41043');
sysExcelWorksheet.rows().item(14).style('@SYS4925');
row = 14;
while select inventTable exists join priceDiscTable1
where priceDiscTable1.ItemRelation==inventTable.ItemId &&
priceDiscTable1.relation==PriceType::PriceSales &&
priceDiscTable1.AccountCode==TableGroupAll::Table &&
priceDiscTable1.AccountRelation==custAccount
{
select priceDiscTable order by FromDate desc
where priceDiscTable.ItemRelation==inventTable.ItemId
&& priceDiscTable.relation==PriceType::PriceSales
&& priceDiscTable.AccountCode==TableGroupAll::Table
&& priceDiscTable.AccountRelation==custAccount ;
row++;
sysExcelWorksheet.cells().item(row,3).value(inventTable.itemName);
sysExcelWorksheet.cells().item(row,10).value(inventTable.ItemOwningLocationJKG);
sysExcelWorksheet.cells().item(row,13).value(inventTable.OrigCountryRegionId);
sysExcelWorksheet.cells().item(row,14).value(inventTable.Intracode);
sysExcelWorksheet.cells().item(row,4).value(inventTable.B2CodeJKG[1]);
sysExcelWorksheet.cells().item(row,2).value(priceDiscTable.ItemRelation);
sysExcelWorksheet.cells().item(row,6).value(priceDiscTable.UnitId);
sysExcelWorksheet.cells().item(row,15).value(priceDiscTable.Amount);
select firstOnly analysisTable
where analysisTable.ItemId == inventTable.ItemId;
analysisNum = analysisTable.AnalysisNum[1];
description = ItemSalesAnalysisCodesJKG::find(0,analysisNum).Description;
sysExcelWorksheet.cells().item(row,1).value(description);
select JKG_ItemCodes where JKG_ItemCodes.ItemId==inventTable.ItemId;
sysExcelWorksheet.cells().item(row,7).value(enum2str(JKG_ItemCodes.CEMark_JKG));
sysExcelWorksheet.cells().item(row,9).value(enum2str(JKG_ItemCodes.HazardousMaterialFlag_JKG));
select JKG_TemperatureCodes where JKG_TemperatureCodes.Code==JKG_ItemCodes.TransitTempCode_JKG;
sysExcelWorksheet.cells().item(row,8).value(JKG_TemperatureCodes.Description);
select itemOwningLocationJKG where itemOwningLocationJKG.LocationId==inventTable.ItemOwningLocationJKG;
sysExcelWorksheet.cells().item(row,12).value(itemOwningLocationJKG.Description);
sysExcelWorksheet.cells().item(row,5).value(inventTable.B2CodeDescriptionJKG());
select custTable where custTable.AccountNum==custAccount;
sysExcelWorksheet.cells().item(row,11).value(custTable.InventLocation);
sysExcelWorksheet.cells().item(row,16).value();
sysExcelWorksheet.cells().item(row,17).value(strfmt('=O%1*P%1',row));
sysExcelWorksheet.cells().item(row,17).value();
rowcount++;
}
cells.range('O1:O'+int2str(14+rowcount)).numberFormat('0.00');
cells.range('P1:P'+int2str(14+rowcount)).numberFormat('0.00');
cells.range('Q1:Q'+int2str(14+rowcount)).numberFormat('0.00');
sysExcelWorkSheet.cells().item(11,2).value(strfmt('=SUM(Q1:Q%1)',int2str(14+rowcount)));
sysExcelWorkSheet.cells().item(12,2).value(strfmt('=SUM(P1:P%1)',int2str(14+rowcount)));
//Set up image parameters
resourceNode = SysResource::getResourceNode(resourcestr(JKG_CompanyLogo));
if (resourceNode)
{
resourceNode. AOTload();
imagename = SysResource::saveToTempFile(resourceNode);
}
else
{
throw Error('@AXT51164');
}
variant.bStr(imagename);
xpos.int(1);
ypos.int(1);
state.int(1);
width.int(200);
height.int(050);
//Add the image to the worksheet
comShapes = comWorkSheet.shapes();
comShapes.addPicture(variant,xpos,ypos,state,state,width,height);
//Autofitt and protect the sheet
sysExcelWorksheet.columns().autoFit();
sysExcelWorksheet.protect('',false,false);
//Save the sheet and close the app
comWorkBook.saveas(dlgFilePathVal);
comWorkBooks.close();
comApplication.quit();
}
catch(Exception::Error)
{
//Force app to quit
comWorkBook.saveas(dlgFilePathVal);
comWorkBooks.close();
comApplication.quit();
}
}
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;
}
public void run()
{
#OCCRetryCount
if (! this.validate())
throw error("");
try
{
ttsbegin;
this.insertToExcelFile();
Info("@JKG6833");
ttscommit;
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
public boolean validate()
{
if (!custAccount)
return checkFailed(strfmt("@SYS26332",'@SYS24704'));
if (!dlgFilePathVal)
return checkFailed(strfmt("@SYS26332",'@SYS124357'));
return true;
}
static void main(Args _args)
{
JKG_CustomerPriceDownload JKG_customerPriceDownload = new JKG_CustomerPriceDownload();
;
if (JKG_customerPriceDownload.prompt())
JKG_customerPriceDownload.run();
}