📄 ufzsprep.pas
字号:
unit ufzsprep;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ubasereport, DBClient, DB, DBTables, Grids, wwdblook,comobj, Excel2000,
wwdbdatetimepicker, StdCtrls, Mask, wwdbedit, Buttons, ExtCtrls,common,MDIbaseinfo,
ADODB, DBGridEh;
type
Tfrmfzsprep = class(Tfrmbasereport)
StringGrid1: TStringGrid;
ADOQuery2: TADOQuery;
procedure FormCreate(Sender: TObject);
procedure spbtnprintClick(Sender: TObject);
procedure spbtnpriorClick(Sender: TObject);
private
{ Private declarations }
mxzl: array[1..100] of string;
public
{ Public declarations }
end;
var
frmfzsprep: Tfrmfzsprep;
implementation
uses global;
{$R *.dfm}
procedure Tfrmfzsprep.FormCreate(Sender: TObject);
begin
inherited;
stringgrid1.ColCount:=103;
StringGrid1.Cells[0,0]:='颜色'; //列,行
StringGrid1.Cells[0,1]:='尺码';
StringGrid1.Cells[0,2]:='生产单数';
StringGrid1.Cells[0,3]:='发织数';
{ StringGrid1.Cells[4,0]:='备注';
StringGrid1.Cells[5,0]:='交货日期';
StringGrid1.Cells[6,0]:='创建人';
StringGrid1.Cells[7,0]:='发制日期';
///////////////////////////////////////////以上是头
StringGrid1.Cells[8,0]:='物料代码';
StringGrid1.Cells[9,0]:='成分';
StringGrid1.Cells[10,0]:='支数';
StringGrid1.Cells[11,0]:='色号';
StringGrid1.Cells[12,0]:='颜色';
StringGrid1.Cells[13,0]:='缸号';
StringGrid1.Cells[14,0]:='单位';
StringGrid1.Cells[15,0]:='类型';
StringGrid1.Cells[16,0]:='重量';
StringGrid1.Cells[17,0]:='备注';
StringGrid1.Cells[18,0]:='发制编号';
StringGrid1.Cells[19,0]:='码数';
StringGrid1.Cells[20,0]:='码数';
StringGrid1.Cells[21,0]:='码数';
StringGrid1.Cells[22,0]:='码数';
StringGrid1.Cells[23,0]:='码数';
StringGrid1.Cells[24,0]:='码数';
StringGrid1.Cells[25,0]:='码数';
StringGrid1.Cells[26,0]:='码数';
StringGrid1.Cells[27,0]:='码数';
StringGrid1.Cells[28,0]:='码数';
StringGrid1.Cells[29,0]:='码数';
StringGrid1.Cells[30,0]:='码数';
StringGrid1.Cells[31,0]:='重量';
StringGrid1.Cells[32,0]:='件数';}
end;
procedure Tfrmfzsprep.spbtnprintClick(Sender: TObject);
var
vtotal:real;
mx,colorcount,j,i:integer;
ss1,ss:string;
begin
inherited;
if billno.Text='' then
begin
application.MessageBox('请输入批号!', '提示信息', mb_ok);
abort;
end;
with adoquery1 do
begin
sql.Clear;
sql.add(' select b.pno,a.color,sum(a.s1) s1,sum(a.sw1) sw1, sum(a.s2) s2,sum(a.sw2) sw2,sum(a.s3) s3,sum(a.sw3) sw3, sum(a.s4) s4,sum(a.sw4) sw4,');
sql.add(' sum(a.s5) s5,sum(a.sw5) sw5, sum(a.s6) s6,sum(a.sw6) sw6,sum(a.s7) s7,sum(a.sw7) sw7, sum(a.s8) s8,sum(a.sw8) sw8,sum(a.s9) s9,sum(a.sw9) sw9, sum(a.s10) s10,sum(a.sw10) sw10,');
sql.add(' sum(a.s11) s11,sum(a.sw11) sw11,sum(a.s12) s12,sum(a.sw12) sw12');
sql.add(' from tfzsub a,tfz b');
sql.add(' where a.fresid=b.fresid ');
end;
if billno.Text<>'' then
begin
adoquery1.SQL.add(' and b.pno =:pno');
adoquery1.Parameters.ParamByName('pno').Value:=billno.Text;
end;
if edit3.text<>'' then
begin
adoquery1.SQL.add(' and a.color like :color');
adoquery1.Parameters.ParamByName('color').value:='%'+edit3.text+'%';
end;
if edit4.text<>'' then
begin
adoquery1.SQL.add(' and a.colorno =:colorno');
adoquery1.Parameters.ParamByName('colorno').Value:=edit4.text;
end;
adoquery1.SQL.Add(' group by b.pno,a.color');
adoquery1.Open;
if adoquery1.RecordCount>0 then
begin
// stringgrid1.ColCount:=adoquery1.RecordCount*12+2;
// stringgrid1.Rowcount:=adoquery1.RecordCount+4;
adoquery2.SQL.Clear;
adoquery2.SQL.Add('select b.ss1,b.s2 ss2,b.s3 ss3,b.s4 ss4,b.s5 ss5 ,b.s6 ss6,b.s7 ss7,');
adoquery2.SQL.Add('b.s8 ss8,b.s9 ss9,b.s10 ss10,b.s11 ss11,b.s12 ss12 from tfz b where pno=:pno');
adoquery2.Parameters.ParamByName('pno').Value:=billno.Text;
adoquery2.Open;
mx:=0;
for i:=1 to adoquery1.recordcount do
begin
for j:=1 to 12 do
begin
ss:='ss'+inttostr(j);
if adoquery2.fieldbyname(ss).AsString<>'' then
begin
mx:=mx+1;
StringGrid1.Cells[mx,1]:=adoquery2.fieldbyname(ss).AsString;
end;
end;
end;
// showmessage(inttostr(colorcount)); 填码数
mx:=0;
for j:=1 to 12 do
begin
ss:='ss'+inttostr(j);
if adoquery2.fieldbyname(ss).AsString<>'' then
mx:=mx+1;
end; // showmessage(inttostr(colorcount)); 算出总的码数
adoquery1.First;
colorcount:=0;
while not adoquery1.Eof do
begin
for j:=1 to mx do
begin
ss:='s'+inttostr(j);
colorcount:=colorcount+1;
if adoquery1.fieldbyname(ss).AsFloat>0 then
StringGrid1.Cells[colorcount,3]:=adoquery1.fieldbyname(ss).AsString else
StringGrid1.Cells[colorcount,3]:='';
StringGrid1.Cells[colorcount,0]:=adoquery1.fieldbyname('color').AsString;
end;
adoquery1.Next;
end; ///////////填发织内容
end;
///////////////////////////制单数统计
with adoquery1 do
begin
sql.Clear;
sql.add(' select b.pno,a.color,sum(a.s1) s1,sum(a.sw1) sw1, sum(a.s2) s2,sum(a.sw2) sw2,sum(a.s3) s3,sum(a.sw3) sw3, sum(a.s4) s4,sum(a.sw4) sw4,');
sql.add(' sum(a.s5) s5,sum(a.sw5) sw5, sum(a.s6) s6,sum(a.sw6) sw6,sum(a.s7) s7,sum(a.sw7) sw7, sum(a.s8) s8,sum(a.sw8) sw8,sum(a.s9) s9,sum(a.sw9) sw9, sum(a.s10) s10,sum(a.sw10) sw10,');
sql.add(' sum(a.s11) s11,sum(a.sw11) sw11,sum(a.s12) s12,sum(a.sw12) sw12');
sql.add(' from tzdsub a,tzd b');
sql.add(' where a.fresid=b.fresid ');
end;
if billno.Text<>'' then
begin
adoquery1.SQL.add(' and b.pno =:pno');
adoquery1.Parameters.ParamByName('pno').Value:=billno.Text;
end;
if edit3.text<>'' then
begin
adoquery1.SQL.add(' and a.color like :color');
adoquery1.Parameters.ParamByName('color').value:='%'+edit3.text+'%';
end;
if edit4.text<>'' then
begin
adoquery1.SQL.add(' and a.colorno =:colorno');
adoquery1.Parameters.ParamByName('colorno').Value:=edit4.text;
end;
adoquery1.SQL.Add(' group by b.pno,a.color');
adoquery1.Open;
if adoquery1.RecordCount>0 then
begin
// stringgrid1.ColCount:=adoquery1.RecordCount*12+2;
// stringgrid1.Rowcount:=adoquery1.RecordCount+4;
{ adoquery2.SQL.Clear;
adoquery2.SQL.Add('select b.ss1,b.s2 ss2,b.s3 ss3,b.s4 ss4,b.s5 ss5 ,b.s6 ss6,b.s7 ss7,');
adoquery2.SQL.Add('b.s8 ss8,b.s9 ss9,b.s10 ss10,b.s11 ss11,b.s12 ss12 from tzd b where pno=:pno');
adoquery2.Parameters.ParamByName('pno').Value:=billno.Text;
adoquery2.Open;
mx:=0;
for j:=1 to 12 do
begin
ss:='ss'+inttostr(j);
if adoquery2.fieldbyname(ss).AsString<>'' then
mx:=mx+1;
end; } // showmessage(inttostr(colorcount)); 算出总的码数
adoquery1.First;
colorcount:=0;
while not adoquery1.Eof do
begin
for j:=1 to mx do
begin
ss:='s'+inttostr(j);
colorcount:=colorcount+1;
if adoquery1.fieldbyname(ss).AsFloat>0 then
StringGrid1.Cells[colorcount,2]:=adoquery1.fieldbyname(ss).AsString else
StringGrid1.Cells[colorcount,2]:='';
// StringGrid1.Cells[colorcount,0]:=adoquery1.fieldbyname('color').AsString;
end;
adoquery1.Next;
end; ///////////填制单数内容
end;
//////////////////////////////////以下开始收片明细与统计
if billno.Text='' then
begin
application.MessageBox('请输入批号!', '提示信息', mb_ok);
abort;
end;
with adoquery1 do
begin
sql.Clear;
sql.add(' select b.fdelivdate, b.pno,a.color,sum(a.s1) s1,sum(a.sw1) sw1, sum(a.s2) s2,sum(a.sw2) sw2,sum(a.s3) s3,sum(a.sw3) sw3, sum(a.s4) s4,sum(a.sw4) sw4,');
sql.add(' sum(a.s5) s5,sum(a.sw5) sw5, sum(a.s6) s6,sum(a.sw6) sw6,sum(a.s7) s7,sum(a.sw7) sw7, sum(a.s8) s8,sum(a.sw8) sw8,sum(a.s9) s9,sum(a.sw9) sw9, sum(a.s10) s10,sum(a.sw10) sw10,');
sql.add(' sum(a.s11) s11,sum(a.sw11) sw11,sum(a.s12) s12,sum(a.sw12) sw12');
sql.add(' from tspsub a,tsp b');
sql.add(' where a.fresid=b.fresid ');
end;
if billno.Text<>'' then
begin
adoquery1.SQL.add(' and b.pno =:pno');
adoquery1.Parameters.ParamByName('pno').Value:=billno.Text;
end;
if edit3.text<>'' then
begin
adoquery1.SQL.add(' and a.color like :color');
adoquery1.Parameters.ParamByName('color').value:='%'+edit3.text+'%';
end;
if edit4.text<>'' then
begin
adoquery1.SQL.add(' and a.colorno =:colorno');
adoquery1.Parameters.ParamByName('colorno').Value:=edit4.text;
end;
adoquery1.SQL.Add(' group by b.fdelivdate, b.pno,a.color');
adoquery1.SQL.Add(' order by b.fdelivdate');
adoquery1.Open;
if adoquery1.RecordCount>0 then
begin
stringgrid1.RowCount:=adoquery1.RecordCount+4;
end;
colorcount:=0;
i:=0;
while not adoquery1.Eof do
begin
for j:=1 to mx do
begin
ss:='s'+inttostr(j);
colorcount:=colorcount+1;
if adoquery1.fieldbyname(ss).AsFloat>0 then
StringGrid1.Cells[colorcount,i+4]:=adoquery1.fieldbyname(ss).AsString else
StringGrid1.Cells[colorcount,i+4]:='';
StringGrid1.Cells[0,i+4]:=adoquery1.fieldbyname('fdelivdate').AsString;
end;
adoquery1.Next;
if StringGrid1.Cells[0,i+4]<>adoquery1.fieldbyname('fdelivdate').AsString then
begin
i:=i+1;
colorcount:=0;
end;
end;
for i:=1 to stringGrid1.colCount do
begin
for j:=3 to stringgrid1.rowCount do
begin
if stringgrid1.Cells[i,j+1]<>'' then
vtotal:=vtotal+ strtofloat(stringgrid1.Cells[i,j+1]);
end;
StringGrid1.Cells[i,stringgrid1.RowCount-2]:=floattostr(vtotal);
StringGrid1.Cells[i,stringgrid1.RowCount-1]:=floattostr(strtofloat(StringGrid1.Cells[i,3])-vtotal);
vtotal:=0;
end;
end;
procedure Tfrmfzsprep.spbtnpriorClick(Sender: TObject);
var
excelid, mybook: variant;
row, col, i, j, colcount, frow: integer;
excelflg: boolean;
rpstr: string;
per_count, datasum, dsum1, dsum2: integer;
datas1, datas2, datas3: string;
begin
// adoquery1.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 :=stringgrid1.ColCount-1;
// excelid.range['A' + inttostr(row), chr(64 + colcount) + inttostr(row)].merge();
mybook.worksheets[1].cells.item[row, col] := '基本报表';
mybook.worksheets[1].cells.item[row, col].font.size := 18;
mybook.worksheets[1].cells.item[row, col].font.bold := true;
mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
row := row + 1;
//excelid.range['A' + inttostr(row), chr(64 + colcount) + inttostr(row)].merge();
mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
datetostr(date);
row := row + 1;
frow := row;
// adoquery1.First;
for i := 1 to colcount do
begin
/// excelid.columns[i].ColumnWidth := stringgrid1.Columns[i - 1].Width / 8;
excelid.columns[i].NumberFormatLocal := '@';
mybook.worksheets[1].cells.item[row, i] := stringgrid1.Cells[i-1,0];
mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
end;
row := row + 1;
j:=1;
for j:=1 to stringgrid1.RowCount do
begin
for i := 1 to colcount do
mybook.worksheets[1].cells.item[row, i] := StringGrid1.Cells[i-1,j];//
row := row + 1;
end;
// excelid.range['A' + inttostr(row), chr(64 + colcount) + inttostr(row)].merge();
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(adoquery1.RecordCount) + '条';
row := row + 1;
rpstr := '报表条件:';
{ excelid.range['A' + inttostr(row), chr(64 + colcount) +
inttostr(row)].merge();
mybook.worksheets[1].cells.item[row, col] := rpstr;
row := row + 1;
excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
- 1)].Borders[1].LineStyle := xlContinuous;
excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
- 1)].Borders[2].LineStyle := xlContinuous;
excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
- 1)].Borders[3].LineStyle := xlContinuous;
excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
- 1)].Borders[4].LineStyle := xlContinuous; }
excelid.visible := true;
end;
// adoquery1.EnableControls;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -