📄 fdeptgx.pas
字号:
begin
ff:='ff'+inttostr(i);
ffs:='ffs'+inttostr(i);
ffg:='ffg'+inttostr(i);
schqy.SQL.Clear;
schqy.sql.add('update cb set ');
schqy.sql.add(ff) ;
schqy.sql.add('=');
schqy.sql.add('round(');
schqy.sql.add(ff);
schqy.sql.add('/:tt,2)*12');
schqy.SQL.Add(' where pno=:pno');
schqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
schqy.ParamByName('tt').asfloat:=finishqy.fieldbyname('zxnum').AsFloat;
schqy.ExecSQL;
schqy.SQL.Clear;
schqy.sql.add('update cb set ');
schqy.sql.add(ffs) ;
schqy.sql.add('=');
schqy.sql.add('round(');
schqy.sql.add(ffs);
schqy.sql.add('/:tt,2)*12');
schqy.SQL.Add(' where pno=:pno');
schqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
schqy.ParamByName('tt').asfloat:=finishqy.fieldbyname('zxnum').AsFloat;
schqy.ExecSQL;
end;
// showmessage(datas1);
// schqy.sql.add(datas1);
schqy.SQL.Clear;
schqy.sql.add('update cb set fz_num=:fz,KH_num=:kh,zd_num=:zd,sp_num=:sp,ch_num=:ch');
schqy.SQL.Add('where pno=:pno');
schqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
schqy.ParamByName('fz').Asfloat:=finishqy.fieldbyname('pnonum').Asfloat;
schqy.ParamByName('kh').Asstring:=finishqy.fieldbyname('kno').AsString;
schqy.ParamByName('zd').Asfloat:=finishqy.fieldbyname('pnonum').Asfloat;
schqy.ParamByName('sp').Asfloat:=finishqy.fieldbyname('spnum').Asfloat;
schqy.ParamByName('ch').Asfloat:=finishqy.fieldbyname('zxnum').Asfloat;
schqy.ExecSQL;
finishqy.Next;
end;
with saveqy do
begin
sql.clear;
sql.add('select sum(ch_num) chnum from cb');
open;
end;
// showmessage('aaaaa');
///////////////////算 包装部 单价资料
with schqy do
begin
sql.clear;
sql.add(' select sum(b.totalin) as total from scintb a,scinmx b');
sql.add(' where a.idno=b.idno and a.years=:yy and months=:mm');
sql.add(' and b.dept1=''包装''');
ParamByName('yy').AsString:=spinedit1.Text;
ParamByName('mm').asstring:=spinedit2.Text;
open;
end;
with sch2 do
begin
sql.clear;
sql.add('update cb set ff48=:ff48');
parambyname('ff48').AsFloat:= schqy.FieldByName('total').AsFloat/saveqy.FieldByName('chnum').AsFloat*12;
execsql;
end;
/////////////////// 包装部单价资料完毕
///////////////////算 洗水部 单价资料
with schqy do
begin
sql.clear;
sql.add(' select sum(b.totalin) as total from scintb a,scinmx b');
sql.add(' where a.idno=b.idno and a.years=:yy and months=:mm');
sql.add(' and b.dept1=''洗水'' and note=null');
ParamByName('yy').AsString:=spinedit1.Text;
ParamByName('mm').asstring:=spinedit2.Text;
open;
end;
with sch2 do
begin
sql.clear;
sql.add('update cb set ff49=:ff49');
parambyname('ff49').AsFloat:= schqy.FieldByName('total').AsFloat/saveqy.FieldByName('chnum').AsFloat*12;
execsql;
end;
/////////////////// 洗水部单价资料完毕
///////////////////算 杂工部 单价资料
with schqy do
begin
sql.clear;
sql.add(' select sum(b.totalin) as total from scintb a,scinmx b');
sql.add(' where a.idno=b.idno and a.years=:yy and months=:mm');
sql.add(' and b.dept1=''杂工''');
ParamByName('yy').AsString:=spinedit1.Text;
ParamByName('mm').asstring:=spinedit2.Text;
open;
end;
with sch2 do
begin
sql.clear;
sql.add('update cb set ff50=:ff50');
parambyname('ff50').AsFloat:= schqy.FieldByName('total').AsFloat/saveqy.FieldByName('chnum').AsFloat*12;
execsql;
end;
/////////////////// 洗水部单价资料完毕
///////////////////////////////// EXCEL开始
with finishqy do
begin
sql.clear;
sql.add(' select * from cb');
open;
end ;
//showmessage(inttostr(finishqy.fieldcount));
finishqy.DisableControls;
try
excelid := createoleobject('excel.application');
mybook := createoleobject('excel.sheet');
mybook := excelid.workbooks.add;
row := 1;
col := 1;
excelflg := true;
except
excelflg := false;
application.MessageBox('请确定EXCEL是否正确安装!', '提示信息', mb_ok);
end;
if excelflg then
begin
colcount := finishqy.fieldcount;
excelid.ActiveSheet.PageSetup.Orientation := xlPortrait ;
// excelid.ActiveSheet.PageSetup.Orientation := xlLandscape;
mybook.worksheets[1].cells.item[row, 3] := company+' '+ spinedit1.Text+' 年' +spinedit2.Text+' 月' + '完工批号成本表';
mybook.worksheets[1].cells.item[row, 3].font.size := 14;
mybook.worksheets[1].cells.item[row, 3].font.bold := true;
mybook.worksheets[1].cells.item[row, 3].HorizontalAlignment := xlcenter;
row := row + 1;
mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
datetostr(date);
row := row + 1;
frow := row;
finishqy.First;
mybook.worksheets[1].cells.item[row, 1] :=' ';
mybook.worksheets[1].cells.item[row, 2] :='制单资料';
mybook.worksheets[1].cells.item[row, 3] :=' ';
mybook.worksheets[1].cells.item[row, 4] :=' ';
mybook.worksheets[1].cells.item[row, 5] :='工序';
// mybook.worksheets[1].cells.item[row, 6] :=' ';
mybook.worksheets[1].cells.item[row, 47] :='备 注';
ii:=0;
for i:=6 to 107 do
begin
if not odd(i) then
begin
ii:=i div 2-3;
// FF:='FF'+floattostr(jj); // 106/2 53-3
mybook.worksheets[1].cells.item[row,i] := a[ii];
end else // 107/2 53.5-3.5
begin
// jj:=i / 2-3.5;
// FF:='FFS'+floattostr(jj);
mybook.worksheets[1].cells.item[row,i] := '时工';
end;
end;
mybook.worksheets[1].cells.item[row,102] :='包装';
mybook.worksheets[1].cells.item[row,104] :='洗水';
mybook.worksheets[1].cells.item[row,106] :='杂工';
ii:=0;
row := row +1;
while not finishqy.Eof do
begin
mybook.worksheets[1].cells.item[row,1] := '批号';
mybook.worksheets[1].cells.item[row,2] := finishqy.Fields[0].AsString;
mybook.worksheets[1].cells.item[row,3] := '发织数';
mybook.worksheets[1].cells.item[row,4] := finishqy.Fields[1].AsString;
mybook.worksheets[1].cells.item[row,5] := '元/打';
///////////////////////////////////////////////////////////////////////////////
mybook.worksheets[1].cells.item[row+1,1] := '款号';
mybook.worksheets[1].cells.item[row+1,2] := finishqy.Fields[2].AsString;
mybook.worksheets[1].cells.item[row+1,3] := '收片数';
mybook.worksheets[1].cells.item[row+1,4] := finishqy.Fields[3].AsString;;
mybook.worksheets[1].cells.item[row+1,5] := '返 数量';
//////////////////////////////////////////////////
mybook.worksheets[1].cells.item[row+2,1] := '制单数';
mybook.worksheets[1].cells.item[row+2,2] := finishqy.Fields[4].AsString;;
mybook.worksheets[1].cells.item[row+2,3] := '出货数';
mybook.worksheets[1].cells.item[row+2,4] := finishqy.Fields[5].AsString;;
mybook.worksheets[1].cells.item[row+2,5] := '工 费用';
///////////////////////////////////////////////////////////
for i:=6 to 107 do
begin
if not odd(i) then
begin
jj:=i/2-3;
FF:='FF'+floattostr(jj); // 106/2 53-3
FFq:='FFgq'+floattostr(jj); // 106/2 53-3
ffg:='ffg'+floattostr(jj);
mybook.worksheets[1].cells.item[row,i] := finishqy.Fieldbyname(ff).AsString;
mybook.worksheets[1].cells.item[row+1,i] := finishqy.Fieldbyname(ffq).AsString;
mybook.worksheets[1].cells.item[row+2,i] := finishqy.Fieldbyname(ffg).AsString;
end else // 107/2 53.5-3.5
begin
jj:=i / 2-3.5;
FF:='FFS'+floattostr(jj);
FFq:='FFgq'+floattostr(jj); // 107/2 53.5-3.5
ffg:='ffg'+floattostr(jj);
mybook.worksheets[1].cells.item[row,i] := finishqy.Fieldbyname(ff).AsString;
// mybook.worksheets[1].cells.item[row+1,i] := finishqy.Fieldbyname(ffq).AsString; 时工一输入的时候不填返工
// mybook.worksheets[1].cells.item[row+2,i] := finishqy.Fieldbyname(ffg).AsString;
end;
end;
{ for i := 86 to 123 do
mybook.worksheets[1].cells.item[row+2,i-80] := finishqy.Fields[i].AsString;
mybook.worksheets[1].cells.item[row+2,45] := finishqy.Fields[124].AsString; //加工费
mybook.worksheets[1].cells.item[row+2,47] := finishqy.Fields[125].AsString; //备注
}
row := row + 3;
finishqy.Next;
end;
row:=row+1;
mybook.worksheets[1].cells.item[row, col].font.size := 12;
mybook.worksheets[1].cells.item[row, col].font.bold := true;
mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
mybook.worksheets[1].cells.item[row, col] := '总记录数:' + inttostr(finishqy.RecordCount) + '条';
row := row + 1;
rpstr := '报表条件:';
excelid.visible := true;
end;
finishqy.EnableControls;
/////////////////////////////
end;
procedure Tfdeptgxfm.Button4Click(Sender: TObject);
var
ii:integer;
excelid, mybook: variant;
k,row, col, i, j, colcount,rowcount ,frow: integer;
excelflg: boolean;
ffs,ffg, ff,rpstr,ffq: string;
datasum, dsum1, dsum2: integer;
datas1, datas2, datas3: string;
jj:double;
tf:bool;
begin
schqy.sql.clear;
schqy.sql.add('select b.pno,b.dept1,sum(b.innum) num,sum(b.totalin) total from scintb a,scinmx b,scpno c');
schqy.SQL.Add(' where a.idno=b.idno and b.pno = c.pno and b.dept1<>'''' ');
if checkbox4.Checked then
schqy.sql.add(' and a.years=:my and months=:mm ');
if checkbox3.Checked then
schqy.sql.add('and c.finishflg=:flg');
schqy.SQL.add(' group by b.pno,b.dept1 order by b.pno,b.dept1 ');
if checkbox4.Checked then
begin
schqy.ParamByName('my').AsString:=spinedit1.Text;
schqy.ParamByName('mm').AsString:=spinedit2.Text;
end;
if checkbox3.Checked then
schqy.ParamByName('flg').AsBoolean:=true;
schqy.Open;
schqy.DisableControls;
/////////////////////////EXCEL START
try
excelid := createoleobject('excel.application');
mybook := createoleobject('excel.sheet');
mybook := excelid.workbooks.add;
row := 1;
col := 1;
excelflg := true;
except
excelflg := false;
application.MessageBox('请确定EXCEL是否正确安装!', '提示信息', mb_ok);
end;
if excelflg then
begin
colcount := schqy.fieldcount;
excelid.ActiveSheet.PageSetup.Orientation := xlPortrait ;
// excelid.ActiveSheet.PageSetup.Orientation := xlLandscape;
mybook.worksheets[1].cells.item[row, col] := company + '汇总报表';
mybook.worksheets[1].cells.item[row, col].font.size := 9;
mybook.worksheets[1].cells.item[row, col].font.bold := true;
mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
row := row + 1;
mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
datetostr(date);
row := row + 1;
frow := row;
schqy.First;
{ for i := 1 to colcount do
begin
mybook.worksheets[1].cells.item[row,i].font.size := 9;
excelid.columns[i].ColumnWidth :=12;
mybook.worksheets[1].cells.item[row, i] :=schqy.Fields[i-1].fieldname;
mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
end;}
mybook.worksheets[1].cells.item[row, 1] :='批号';
mybook.worksheets[1].cells.item[row, 2] :='工序';
mybook.worksheets[1].cells.item[row, 3] :='件数';
mybook.worksheets[1].cells.item[row, 4] :='金额';
row := row + 1;
while not schqy.Eof do
begin
for i := 1 to colcount do
mybook.worksheets[1].cells.item[row,i] := schqy.Fields[i-1].AsString;
row := row + 1;
schqy.Next;
end;
{ for i := 1 to colcount do
mybook.worksheets[1].cells.item[row,i] := schscdayqy.Fields[i-1].AsString; }
row:=row+1;
mybook.worksheets[1].cells.item[row, col].font.size := 12;
mybook.worksheets[1].cells.item[row, col].font.bold := true;
mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
mybook.worksheets[1].cells.item[row, col] := '总记录数:' + inttostr(schqy.RecordCount) + '条';
row := row + 1;
excelid.visible := true;
schqy.EnableControls;
end;
end;
end.
procedure Tfdeptgxfm.BitBtn3Click(Sender: TObject);
begin
end;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -