X++:
static void CustCMRForm_ES(CustInvoiceJour CIJ, CurrencyCode _nCCode='EUR'){
str s;
QueryRun queryRun;
Query query;
QueryBuildDataSource qbdsITrans;
QueryBuildDataSource qbdsInventDim;
QueryBuildRange qbr;
CustInvoiceJour IJour;
SalesTable SalesTable;
CustTable Vendor,Customer;
InventSerial ISerial;
CustInvoiceTrans ITrans,ITrans1;
InventTrans InventTrans;
InventDim InventDim,InventDim1;
RContractTable RCT;
InventTable ITable;
CurrencyCode CCode,nCCode=_nCCode;
Address ADR;
Amount sSum=0, sVAT=0,wN=0,wB=0,wNl=0,wBl=0;
Qty sQty=0;
counter i,p,start;
counter RollCount=0,PalletCount=0, lRollCount=0,lPalletCount=0;
RNumDateInWordConverter converter = RNumDateInWordConverter::construct('en-us');
boolean ExOut=false;
#ExcelExport
ComExcelDocument_RU excl=new ComExcelDocument_RU();
Str Amount2Text(real Amount, CurrencyCode Curr){
return converter.numeralsToCurrency(Amount,Curr);
}
Str Qty2Text(real Amount){
return strfmt('%1 kg %2 g',converter.numeralsToTxt( abs(Amount) - frac(abs(Amount))),int2str(decround(frac(abs(Amount)) * 100, 2)));
}
Str RC(int r,int c){
return ComExcelDocument_RU::numtonamecell(c,r);//strfmt('R%1C%1',r,c);
}
Str RC_(int r,int c){
return strfmt('R[%1]C[%1]',r,c);
}
;
converter.initCurrencyAndConnection();
excl.newFile(#ExcelTemplateDir+#ExCMR_ES); // -= ????????? ?????? =-
excl.visible(false);
IJour=CustInvoiceJour::findFromCustInvoiceTrans(CIJ.SalesId,CIJ.InvoiceId,CIJ.InvoiceDate,CIJ.numberSequenceGroup);// ????
CCode=IJour.CurrencyCode?IJour.CurrencyCode:CompanyInfo::find().CurrencyCode;
SalesTable=SalesTable::find(CIJ.SalesId); // ?????
Vendor=CustTable::find(SalesTable.JuridicalPerson_NV); // ????????? - ???? ??????????? ????
Customer=CustTable::find(SalesTable.CustAccount); // ??????
p=1;
RCT=RContractTable::find(RContractPartnerType::Cust,IJour.RContractCode,IJour.RContractAccount);
excl.insertValue('Contract',RCT.RContractNumber,p); // ???????
excl.insertValue('ContractDate',date2StrUsr(RCT.ContractDate),p); // ???? ????????
excl.insertValue('ContractValidityDate',date2StrUsr(RCT.ContractEndDate),p); // ???? ????????? ????????
excl.insertValue('NumberSpecification',SalesTable.ExtNo_Spec_REP,p); // ????????????
excl.insertValue('DateSpecification',date2StrUsr(SalesTable.createdDate),p); // ???? ??????????
excl.insertValue('CustomerAdressIndex',Customer.invoiceAddress().ZipCode,p); // ?????? ??????????
excl.insertValue('CustomerCountryAdress',Customer.invoiceAddress().Country,p); // ?????? ??????????
excl.insertValue('CustomerCityAdress',Customer.invoiceAddress().City,p); // ????? ??????????
excl.insertValue('CustomerStreetAdress',Customer.invoiceAddress().Street,p); // ????? ??????????
excl.insertValue('CustomerHouseAdress',strfmt('%1 %2 %3',Customer.invoiceAddress().Estate_RU,
Customer.invoiceAddress().Building_RU,
Customer.invoiceAddress().Flat_RU),p); // ??? ??????????
excl.insertValue('Customer',Customer.Name); // ??????????
excl.insertValue('DeliveryPlace',Global_REP::CustExtAdr(Customer,Customer.invoiceAddress()),p); // ????? ??????????
excl.insertValue('DateShipping',date2strusr(IJour.InvoiceDate)/*systemdateget()*/,p); // ???? ????????
excl.insertValue('NumberShipping',IJour.InvoiceId,p); // ????? ????????
excl.insertValue('Amount',Currency::curAmount2CurAmount(IJour.InvoiceAmount,CCode,nCCode),p); // ?????
excl.insertValue('AmountStr',Amount2Text(Currency::curAmount2CurAmount(IJour.InvoiceAmount,CCode,nCCode),nCCode),p); // ????? ????????
excl.insertValue('NumberAutomobile',gReport_REP::VehicleNo(IJour));// ? ??????????
i=0;
query = new Query();
qbdsiTrans = query.addDataSource(tableNum(CustInvoiceTrans));
qbdsInventDim = qbdsiTrans.addDataSource(tableNum(InventDim));
qbdsInventDim.relations(true);
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,SalesId));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,InvoiceId));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,InvoiceDate));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,numberSequenceGroup));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,ItemId));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,SalesUnit));
qbdsITrans.addSortField(fieldNum(CustInvoiceTrans,SalesPrice));
qbdsInventDim.addSortField(fieldnum(InventDim,InventSizeId));
qbdsITrans.orderMode(ordermode::GroupBy);
qbdsInventDim.orderMode(ordermode::GroupBy);
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,SalesId));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,InvoiceId));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,InvoiceDate));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,numberSequenceGroup));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,ItemId));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,SalesUnit));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,SalesPrice));
qbdsInventDim.addSelectionField(fieldnum(InventDim,InventSizeId));
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,Qty),SelectionField::Sum);
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,lineAmount),SelectionField::Sum);
qbdsITrans.addSelectionField(fieldNum(CustInvoiceTrans,lineAmountTax),SelectionField::Sum);
qbdsITrans.addRange(fieldNum(CustInvoiceTrans,SalesId)).value(CIJ.SalesId);
qbdsITrans.addRange(fieldNum(CustInvoiceTrans,InvoiceId)).value(CIJ.InvoiceId);
qbdsITrans.addRange(fieldNum(CustInvoiceTrans,InvoiceDate)).value(queryvalue(CIJ.InvoiceDate));
qbdsITrans.addRange(fieldNum(CustInvoiceTrans,numberSequenceGroup)).value(CIJ.numberSequenceGroup);
queryRun = new QueryRun(query);
while
(queryrun.next())
{
ITrans=queryrun.get(tablenum(CustInvoiceTrans));
InventDim=queryrun.get(tablenum(inventdim));
// select * from ITrans where ITrans.SalesId==CIJ.SalesId &&
// ITrans.InvoiceId==CIJ.InvoiceId &&
// ITrans.InvoiceDate==CIJ.InvoiceDate &&
// ITrans.numberSequenceGroup==CIJ.numberSequenceGroup {
wNl=0;wBl=0;lRollCount=0;lPalletCount=0;ITable=InventTable::find(ITrans.ItemId);
while select * from ITrans1 where ITrans1.SalesId==ITrans.SalesId &&
ITrans1.InvoiceId==ITrans.InvoiceId &&
ITrans1.InvoiceDate==ITrans.InvoiceDate &&
ITrans1.numberSequenceGroup==ITrans.numberSequenceGroup &&
ITrans1.ItemId==ITrans.ItemId &&
ITrans1.SalesUnit==ITrans.SalesUnit &&
ITrans1.SalesPrice==ITrans.SalesPrice
{
while select sum(Qty) from inventTrans join inventdim1
group by inventserialid
where inventtrans.inventDimId==inventdim1.inventDimId &&
inventtrans.StatusIssue==StatusIssue::Sold &&
inventtrans.InventTransId==iTrans1.InventTransId &&
inventtrans.ItemId==iTrans.ItemId
&& inventdim1.InventSizeId==inventdim.InventSizeId
{
RollCount++;
lRollCount++;
}
while select sum(Qty) from inventTrans join inventdim1
group by wmspalletid
where inventtrans.inventDimId==inventdim1.inventDimId &&
inventtrans.StatusIssue==StatusIssue::Sold &&
inventtrans.InventTransId==iTrans1.InventTransId &&
inventtrans.ItemId==iTrans.ItemId
&& inventdim1.InventSizeId==inventdim.InventSizeId
{
// wB=wB+WMSPallet::find(InventDim.wMSPalletId).GrossWeight_NV;
// wBl=wBl+WMSPallet::find(InventDim.wMSPalletId).GrossWeight_NV;
PalletCount++;
lPalletCount++;
} // while sum(qty)
} // while ITrans1
wN=wN+ITrans.Qty;
wNl=wNl+ITrans.Qty;
wB=wB+ITrans.Qty*1.04;//WMSPallet::find(InventDim1.wMSPalletId).GrossWeight_NV;
wBl=wBl+ITrans.Qty*1.04;//WMSPallet::find(InventDim1.wMSPalletId).GrossWeight_NV;
//invoice
start=32;p=2;
excl.insertRow(start+i,p);
excl.insertValue(RC(start+i,1),int2str(i+1),p);
excl.insertValue(RC(start+i,3),strfmt('%1 ? %2 (BOPP coex)',ITable.ItemName,inventDim.InventSizeId),p);
excl.insertValue(RC(start+i,17),ITrans.SalesUnit,p);
excl.insertValue(RC(start+i,22),ITrans.Qty,p);
excl.insertValue(RC(start+i,28),Currency::curAmount2CurAmount(ITrans.SalesPrice,CCode,nCCode),p);
excl.insertValue(RC(start+i,33),Currency::curAmount2CurAmount(ITrans.LineAmount+ITrans.LineAmountTax,CCode,nCCode),p);
//detail packing
start=24;p=3;
excl.insertRow(start+i,p);
excl.insertValue(RC(start+i,1),int2str(i+1),p);
excl.insertValue(RC(start+i,3),ITable.ItemGroupId,p);
excl.insertValue(RC(start+i,7),'',p); // ???? ? ?. ?. - ?????? ????? ???????? - ?? ???????????? ??????? (??????? ??????) ?? ?????????
excl.insertValue(RC(start+i,13),ITable.ThicknessId_NV,p);
excl.insertValue(RC(start+i,17),inventDim.InventSizeId,p);
excl.insertValue(RC(start+i,21),lRollCount,p);
excl.insertValue(RC(start+i,25),lPalletCount,p);
excl.insertValue(RC(start+i,29),ITrans.Qty,p);
excl.insertValue(RC(start+i,34),wBl,p);
//Form A
// start=32;p=4;
// excl.insertValue(RC(start+i,9),strfmt('%1 ? %2 (BOPP coex) - %3 %4',ITable.itemName,inventDim.InventSizeId,iTrans.Qty,iTrans.SalesUnit),p);
// excl.insertValue(RC(start+i,24),'\"W3920\"',p);
//Attent
start=19;p=6;
excl.insertRow(start+i,p);
excl.insertValue(RC(start+i,1),int2str(i+1),p);
excl.insertValue(RC(start+i,3),strfmt('%1 ? %2 (BOPP coex)',ITable.itemName,inventDim.InventSizeId),p);
excl.insertValue(RC(start+i,19),ITrans.SalesUnit,p);
excl.insertValue(RC(start+i,21),ITrans.Qty,p);
i++;
}
excl.insertValue('Currency',nCCode,p);
excl.insertValue('RollCount',RollCount,p);
excl.insertValue('TotalPallet',PalletCount,p);
excl.insertValue('WeightNetto',wN,p);
excl.insertValue('WeightNettoStr',Qty2Text(wN),p);
excl.insertValue('WeightBrutto',wB,p);
excl.insertValue('TotalPackages','???? ??????!!!',1);
// excl.insertValue('sQty',sQty);
// excl.insertValue('sVAT_',sVAT);
// excl.insertValue('sSum_',sSum);
// excl.insertValue('sSum',Amount2Text(sSum,IJour.CurrencyCode)); // ????? ????????
// excl.insertValue('sVAT',Amount2Text(sVAT,IJour.CurrencyCode)); // ??? ????????
excl.visible(true);
}