20.11.2013, 17:44
|
#4 |
|
Участник
|
все не могу сделать тотал для каждой строки/столбца...
вот код, еще раз..( Код: xlWorkSheet.Cells.Item(1,2,'Àíàëèç çàðïëàòû ïî èçìåðåíèÿì');
xlWorkSheet.Cells.Item(2,2,'Ïåðèîä ñ '+FORMAT(periodstart)+' ïî '+FORMAT(periodend));
xlWorkSheet.Range(ColInInteger(2)+FORMAT(1)+':'+ColInInteger(2)+FORMAT(1)).Font.Bold:=TRUE;
xlWorkSheet.Range(ColInInteger(2)+FORMAT(2)+':'+ColInInteger(2)+FORMAT(2)).EntireColumn.AutoFit;
xlWorkSheet.Cells.Item(toprow,2,'Íàçâàíèå');
xlWorkSheet.Range(ColInInteger(2)+FORMAT(toprow)+':'+ColInInteger(2)+FORMAT(toprow)).Font.Bold:=TRUE;
xlWorkSheet.Range(ColInInteger(2)+FORMAT(toprow)+':'+ColInInteger(2)+FORMAT(toprow)).HorizontalAlignment:=-4108;
xlWorkSheet.Range(ColInInteger(2)+FORMAT(toprow)+':'+ColInInteger(2)+FORMAT(toprow)).VerticalAlignment:=-4108;
xlWorkSheet.Range(ColInInteger(2)+FORMAT(toprow)+':'+ColInInteger(2)+FORMAT(toprow+2))._BorderAround(1,2);
xlWorkSheet.Range(ColInInteger(2)+FORMAT(toprow)+':'+ColInInteger(2)+FORMAT(toprow+2)).Merge;
TempColumnDimOption.RESET;
TempLineDimOption.RESET;
TempColumnDimOption.DELETEALL;
TempLineDimOption.DELETEALL;
pav.RESET;
pav.SETRANGE(Code,salary);
pav.FINDFIRST;
totaltotal:=0;
TotalSum:=0;
CASE LineDimOption OF
LineDimOption::Element:
BEGIN
PayrollElement.RESET;
IF element<>'' THEN
PayrollElement.SETFILTER(Code,element);
IF PayrollElement.FINDSET THEN
REPEAT
TempLineDimOption.INIT;
TempLineDimOption.Code:=PayrollElement.Code;
TempLineDimOption.Name:=PayrollElement.Code;
TempLineDimOption.INSERT;
UNTIL PayrollElement.NEXT=0;
END;
LineDimOption::"Element Group":
BEGIN
PayrollElementGroup.RESET;
IF elementgroup<>'' THEN
PayrollElementGroup.SETFILTER(Code,elementgroup);
IF PayrollElementGroup.FINDSET THEN
REPEAT
TempLineDimOption.INIT;
TempLineDimOption.Code:=PayrollElementGroup.Code;
TempLineDimOption.Name:=PayrollElementGroup.Code;
TempLineDimOption.INSERT;
UNTIL PayrollElementGroup.NEXT=0;
END;
LineDimOption::Employee:
BEGIN
Employee.RESET;
IF emp<>'' THEN
Employee.SETFILTER("No.",emp);
IF Employee.FINDSET THEN
REPEAT
TempLineDimOption.INIT;
TempLineDimOption.Code:=Employee."No.";
TempLineDimOption.Name:=Employee."Full Name";
TempLineDimOption.INSERT;
UNTIL Employee.NEXT=0;
END;
LineDimOption::"Org. Unit":
BEGIN
OrganizationUnit.RESET;
IF organizatunit<>'' THEN
OrganizationUnit.SETFILTER(Code,organizatunit);
IF OrganizationUnit.FINDSET THEN
REPEAT
TempLineDimOption.INIT;
TempLineDimOption.Code:=OrganizationUnit.Code;
TempLineDimOption.Name:=OrganizationUnit.Code;
TempLineDimOption.INSERT;
UNTIL OrganizationUnit.NEXT=0;
END;
LineDimOption::Period:
BEGIN
CurDate:=periodstart;
ii:=1;
CASE PeriodType OF
PeriodType::Day:
Formula:='<1D>';
PeriodType::Week:
Formula:='<1W>';
PeriodType::Month:
Formula:='<1M>';
PeriodType::Quarter:
Formula:='<1Q>';
PeriodType::Year:
Formula:='<1Y>';
END;
REPEAT
CASE PeriodType OF
PeriodType::Day:
LastDate:=CurDate;
PeriodType::Week:
LastDate:=CALCDATE('<1W-1D>',CurDate);
PeriodType::Month:
LastDate:=CALCDATE('<1M-1D>',CurDate);
PeriodType::Quarter:
LastDate:=CALCDATE('<1Q-1D>',CurDate);
PeriodType::Year:
LastDate:=CALCDATE('<1Y-1D>',CurDate);
END;
TempLineDimOption.INIT;
TempLineDimOption.Code:=FORMAT(ii);
TempLineDimOption.Name:=FORMAT(CurDate);
TempLineDimOption."Period Start":=CurDate;
TempLineDimOption."Period End":=LastDate;
TempLineDimOption.INSERT;
ii:=ii+1;
CurDate:=CALCDATE(Formula,CurDate);
UNTIL CurDate>periodend;
END;
END;
CASE ColumnDimOption OF
ColumnDimOption::Element:
BEGIN
PayrollElement.RESET;
IF element<>'' THEN
PayrollElement.SETFILTER(Code,element);
IF PayrollElement.FINDSET THEN
REPEAT
TempColumnDimOption.INIT;
TempColumnDimOption.Code:=PayrollElement.Code;
TempColumnDimOption.Name:=PayrollElement.Code;
TempColumnDimOption.INSERT;
UNTIL PayrollElement.NEXT=0;
END;
ColumnDimOption::"Element Group":
BEGIN
PayrollElementGroup.RESET;
IF elementgroup<>'' THEN
PayrollElementGroup.SETFILTER(Code,elementgroup);
IF PayrollElementGroup.FINDSET THEN
REPEAT
TempColumnDimOption.INIT;
TempColumnDimOption.Code:=PayrollElementGroup.Code;
TempColumnDimOption.Name:=PayrollElementGroup.Code;
TempColumnDimOption.INSERT;
UNTIL PayrollElementGroup.NEXT=0;
END;
ColumnDimOption::Employee:
BEGIN
Employee.RESET;
IF emp<>'' THEN
Employee.SETFILTER("No.",emp);
IF Employee.FINDSET THEN
REPEAT
TempColumnDimOption.INIT;
TempColumnDimOption.Code:=Employee."No.";
TempColumnDimOption.Name:=Employee."Full Name";
TempColumnDimOption.INSERT;
UNTIL Employee.NEXT=0;
END;
ColumnDimOption::"Org. Unit":
BEGIN
OrganizationUnit.RESET;
IF organizatunit<>'' THEN
OrganizationUnit.SETFILTER(Code,organizatunit);
IF OrganizationUnit.FINDSET THEN
REPEAT
TempColumnDimOption.INIT;
TempColumnDimOption.Code:=OrganizationUnit.Code;
TempColumnDimOption.Name:=OrganizationUnit.Code;
TempColumnDimOption.INSERT;
UNTIL OrganizationUnit.NEXT=0;
END;
ColumnDimOption::Period:
BEGIN
CurDate:=periodstart;
ii:=1;
CASE PeriodType OF
PeriodType::Day:
Formula:='<1D>';
PeriodType::Week:
Formula:='<1W>';
PeriodType::Month:
Formula:='<1M>';
PeriodType::Quarter:
Formula:='<1Q>';
PeriodType::Year:
Formula:='<1Y>';
END;
REPEAT
CASE PeriodType OF
PeriodType::Day:
LastDate:=CurDate;
PeriodType::Week:
LastDate:=CALCDATE('<1W-1D>',CurDate);
PeriodType::Month:
LastDate:=CALCDATE('<1M-1D>',CurDate);
PeriodType::Quarter:
LastDate:=CALCDATE('<1Q-1D>',CurDate);
PeriodType::Year:
LastDate:=CALCDATE('<1Y-1D>',CurDate);
END;
TempColumnDimOption.INIT;
TempColumnDimOption.Code:=FORMAT(ii);
TempColumnDimOption.Name:=FORMAT(CurDate);
TempColumnDimOption."Period Start":=CurDate;
TempColumnDimOption."Period End":=LastDate;
TempColumnDimOption.INSERT;
ii:=ii+1;
CurDate:=CALCDATE(Formula,CurDate);
UNTIL CurDate>periodend;
END;
END;
Col:=3;
TempColumnDimOption.RESET;
cnt:=TempColumnDimOption.COUNT;
IF TempColumnDimOption.FINDSET THEN
REPEAT
xlWorkSheet.Cells.Item(toprow,Col,FORMAT(TempColumnDimOption.Name));
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow)).Font.Bold:=TRUE;
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow)).HorizontalAlignment:=-4108;
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow)).VerticalAlignment:=-4108;
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow+2))._BorderAround(1,2);
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow+2)).Merge;
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(toprow)+':'+ColInInteger(Col)+FORMAT(toprow)).EntireColumn.AutoFit;
Col:=Col+1;
UNTIL TempColumnDimOption.NEXT=0;
w:=0;
Window.OPEN('Ïîñòðîåíèå îò÷åòà @1@@@@@@@@@@@@@@@@@@@@@');
i:=7;
TempLineDimOption.RESET;
cnt:=TempLineDimOption.COUNT;
IF TempLineDimOption.FINDSET THEN
REPEAT
w+=1;
Window.UPDATE(1,ROUND(w/cnt*10000,1));
element1:='';
emp1:='';
organizatunit1:='';
elementgroup1:='';
CASE LineDimOption OF
LineDimOption::Element:
element1:=TempLineDimOption.Code;
LineDimOption::"Element Group":
elementgroup1:=TempLineDimOption.Code;
LineDimOption::Employee:
emp1:=TempLineDimOption.Code;
LineDimOption::"Org. Unit":
organizatunit1:=TempLineDimOption.Code;
END;
Date1:=periodstart;
Date2:=periodend;
IF LineDimOption=LineDimOption::Period THEN
BEGIN
Date1:=TempLineDimOption."Period Start";
Date2:=TempLineDimOption."Period End";
END;
TotalSum:=0;
IF HideNull THEN
BEGIN
TempColumnDimOption.RESET;
IF TempColumnDimOption.FINDSET THEN
REPEAT
IF ColumnDimOption=ColumnDimOption::Period THEN
BEGIN
Date1:=TempColumnDimOption."Period Start";
Date2:=TempColumnDimOption."Period End";
END;
CASE ColumnDimOption OF
ColumnDimOption::Element:
element1:=TempColumnDimOption.Code;
ColumnDimOption::"Element Group":
elementgroup1:=TempColumnDimOption.Code;
ColumnDimOption::Employee:
emp1:=TempColumnDimOption.Code;
ColumnDimOption::"Org. Unit":
organizatunit1:=TempColumnDimOption.Code;
END;
SumAmount1:=CalcPayroll1(COMPANYNAME,Date1,Date2,element1,emp1,organizatunit1,elementgroup1);
TotalSum+=SumAmount1;
totaltotal+=SumAmount1;
UNTIL TempColumnDimOption.NEXT=0;
END;
IF ((TotalSum<>0) AND HideNull) OR (NOT HideNull) THEN
BEGIN
xlWorkSheet.Cells.Item(i,2,FORMAT(TempLineDimOption.Name));
xlWorkSheet.Range(ColInInteger(2)+FORMAT(i)+':'+ColInInteger(2)+FORMAT(i))._BorderAround(1,2);
xlWorkSheet.Range(ColInInteger(2)+FORMAT(i)+':'+ColInInteger(2)+FORMAT(i)).EntireColumn.AutoFit;
Col:=3;
TempColumnDimOption.RESET;
cnt:=TempColumnDimOption.COUNT;
IF TempColumnDimOption.FINDSET THEN
REPEAT
IF ColumnDimOption=ColumnDimOption::Period THEN
BEGIN
Date1:=TempColumnDimOption."Period Start";
Date2:=TempColumnDimOption."Period End";
END;
CASE ColumnDimOption OF
ColumnDimOption::Element:
element1:=TempColumnDimOption.Code;
ColumnDimOption::"Element Group":
elementgroup1:=TempColumnDimOption.Code;
ColumnDimOption::Employee:
emp1:=TempColumnDimOption.Code;
ColumnDimOption::"Org. Unit":
organizatunit1:=TempColumnDimOption.Code;
END;
SumAmount1:=CalcPayroll1(COMPANYNAME,Date1,Date2,element1,emp1,organizatunit1,elementgroup1);
IF SumAmount1<>0 THEN
xlWorkSheet.Cells.Item(i,Col,FORMAT(SumAmount1));
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(i)+':'+ColInInteger(Col)+FORMAT(i))._BorderAround(1,2);
xlWorkSheet.Range(ColInInteger(Col)+FORMAT(i)+':'+ColInInteger(Col)+FORMAT(i)).EntireColumn.AutoFit;
Col:=Col+1;
UNTIL TempColumnDimOption.NEXT=0;
i:=i+1;
END;
UNTIL TempLineDimOption.NEXT=0;
xlWorkSheet.Cells.Item(i,Col-1,FORMAT(totaltotal));
xlWorkSheet.Range(ColInInteger(Col-1)+FORMAT(i)+':'+ColInInteger(Col-1)+FORMAT(i)).EntireColumn.AutoFit;
Window.CLOSE; |
|
|
Древовидный вид