fdeptgx.pas

实用的毛织生产管理系统

      row := row + 1;
      mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
        datetostr(date);
      row := row + 1;
      frow := row;
      sch2.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] :=sch2.Fields[i-1].fieldname;
        mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
      end;
      row := row + 1;
      while not sch2.Eof do
      begin
        for i := 1 to colcount do
         mybook.worksheets[1].cells.item[row,i] := sch2.Fields[i-1].AsString;
         row := row + 1;
         sch2.Next;
      end;

 {   schscdayqy.SQL.clear;
    schscdayqy.SQL.add('select char(null),char(null),char(null),char(null),char(null),sum(fznum) 发织量,sum(sbdaynum) 日收片,sum(sbtolnum) 总收片,char(null),sum(ffnum) 发外数,sum(sfdaynum) 日收外,sum(sftolnum) 总收外,');
    schscdayqy.SQL.add(' sum(bfdaynum) 缝盘,sum(bftolnum) 厂缝,sum(btdaynum) 日挑,sum(bttolnum) 总挑,sum(xsdaynum),sum(xstolnum) 总洗,sum(dcdaynum) 日电,sum(dctolnum) 总电,sum(tsdaynum) 日烫,');
    schscdayqy.SQL.add(' sum(tstolnum) 总烫,sum(zmdaynum) 日车,sum(zmtolnum) 总车,sum(cbdaynum) 日查,sum(cbtolnum) 总查,sum(zxdaynum) 日装,sum(zxtolnum) 总装,sum(pnonum) 制单,sum(subzx) 装差,char(null) from scday a');
    schscdayqy.SQL.add('  inner join  dayscpno b on a.pno=b.pno');
    schscdayqy.Open; }

    {  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(sch2.RecordCount) + '条';
      row := row + 1;
      rpstr := '报表条件:';

        for i := 0 to sch2.recordcount - 1 do
          for j := 0 to 3 do

    excelid.visible := true;
  end;
  sch2.EnableControls;

end;

procedure Tfdeptgxfm.BitBtn2Click(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
 with finishqy do
 begin
  sql.clear;
  sql.add('select a.dept1 from scinmx a,scintb b where');
  sql.add('( a.idno=b.idno and a.sg='''' and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'') or  ');
  sql.add('( a.idno=b.idno and a.sg=null and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'') ');
  sql.add('group by a.dept1');
  parambyname('my').asstring:=spinedit1.Text;
  parambyname('mm').asstring:=spinedit2.Text;
  open;
 end;
  rowcount:=finishqy.RecordCount-1;
  k:=finishqy.RecordCount-1;
 for  i:=0 to finishqy.RecordCount-1 do
 begin
 A [i]:=finishqy.fields[0].AsString;
 memo1.Lines.Add(A[i]);
 finishqy.Next;
 end;
{ A[1]:='查片';
 A[2]:='裁剪';
 A[20]:='拼纱';}
 with finishqy do
 begin
  sql.clear;
  sql.add('select a.dept1 from scinmx a,scintb b where');
  sql.add('( a.idno=b.idno and a.sg=''时工'' and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm');
  sql.add(' and finishflg =1) and a.dept1>''1'')');
  sql.add('group by a.dept1');
  parambyname('my').asstring:=spinedit1.Text;
  parambyname('mm').asstring:=spinedit2.Text;
  open;
 end;
 colcount:=finishqy.RecordCount-1;
 for  i:=0 to finishqy.RecordCount-1 do
 begin
 b [i]:=finishqy.fields[0].AsString;
 memo1.Lines.Add(A[i]);
 finishqy.Next;
 end;
// showmessage(inttostr(k)+'aa');    以下排除B与A重复的工序，如果不重复就统一加到A中
 for i:=0 to colcount do
   begin
     for j:=0 to rowcount do
     begin
      if  b[i]=a[j] then
         begin
         tf:=false;
         break;
         end
         else
         tf:=true;
     end;
       if  tf  then
         begin
         k:=k+1;
         a[k]:=b[i];
//         showmessage(b[i]);
         end;
       tf:=false;
   end;
// showmessage(a[7]+'aa')   ;
// showmessage(a[8]+'bb')   ;
//////////////////////////////所有批号中的成本工序
 with finishqy do
 begin
    sql.clear;
    sql.add('delete from cb');
    execsql;
 end;    ///清空成本表资料

 ////////////////////////////////////////    /////完工的非时工 及未返工部分开始
 with finishqy do
 begin
  sql.clear;
  sql.add('select a.pno,a.dept1 ,sum(a.totalin) totalin,sum(a.innum) innum  from scinmx a,scintb b where'); //,sum(innum) innum
  sql.add('( a.idno=b.idno and a.sg='''' and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'') or ');
  sql.add('( a.idno=b.idno and a.sg=null and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'')');
  sql.add('group by a.pno,a.dept1');
  parambyname('my').asstring:=spinedit1.Text;
  parambyname('mm').asstring:=spinedit2.Text;
  open;
  first;
 end;

 //////
 while not finishqy.Eof do
 begin
   for  i:=0 to 50 do
    begin
      if  finishqy.Fields[1].Asstring=A[i] then
     begin
       ff:='FF'+inttostr(i);
       ffq:='FFq'+inttostr(i);
       pnoqy.SQL.Clear;
       pnoqy.sql.add('select pno from cb where pno=:pno');
       pnoqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
       pnoqy.Open;
      if pnoqy.RecordCount>0 then
        begin
        schqy.SQL.Clear;
        schqy.sql.add('update cb set ');
        schqy.SQL.Add(ff);
        schqy.sql.add('=');
        schqy.sql.Add(' :oo ');
        schqy.SQL.Add(',');
        schqy.SQL.add(ffq);
        schqy.sql.add('=');
        schqy.sql.Add(' :ooq ');
        schqy.SQL.Add('where pno=:pno');
        schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
        schqy.ParamByName('ooq').AsFloat:=finishqy.fieldbyname('innum').AsFloat;
        schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
        schqy.ExecSQL;
        end else
       begin
       schqy.sql.clear;
       schqy.SQL.add('insert into cb(pno,');
       schqy.sql.add(FF);
       schqy.SQL.Add(') values(:pno,:oo)');
       schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
       schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
       schqy.ExecSQL;
     end;
      end;
    end;
    finishqy.Next;
 end;
////////////////////////////////////////////// 完工的非及未返工时工结束
 ////////////////////////////////////////    /////完工的非时工 及返工部分开始
 with finishqy do
 begin
  sql.clear;
  sql.add('select a.pno,a.dept1 ,sum(a.totalin) totalin,sum(a.innum) innum  from scinmx a,scintb b where'); //,sum(innum) innum
  sql.add('( a.idno=b.idno and a.sg='''' and a.work_again=1 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'') or ');
  sql.add('( a.idno=b.idno and a.sg=null and a.work_again=1 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm');
  sql.add(' and finishflg =1) and a.dept1>''1'')');
  sql.add('group by a.pno,a.dept1');
  parambyname('my').asstring:=spinedit1.Text;
  parambyname('mm').asstring:=spinedit2.Text;
  open;
  first;
 end;

 //////
 while not finishqy.Eof do
 begin
   for  i:=0 to 50 do
    begin
      if  finishqy.Fields[1].Asstring=A[i] then
     begin
       ff:='FFg'+inttostr(i);
       ffq:='FFgq'+inttostr(i);
       pnoqy.SQL.Clear;
       pnoqy.sql.add('select pno from cb where pno=:pno');
       pnoqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
       pnoqy.Open;
      if pnoqy.RecordCount>0 then
        begin
        schqy.SQL.Clear;
        schqy.sql.add('update cb set ');
        schqy.SQL.Add(ff);
        schqy.sql.add('=');
        schqy.sql.Add(' :oo ');
        schqy.SQL.Add(',');
        schqy.SQL.add(ffq);
        schqy.sql.add('=');
        schqy.sql.Add(' :ooq ');
        schqy.SQL.Add('where pno=:pno');
        schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
        schqy.ParamByName('ooq').AsFloat:=finishqy.fieldbyname('innum').AsFloat;
        schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
        schqy.ExecSQL;
        end else
       begin
       schqy.sql.clear;
       schqy.SQL.add('insert into cb(pno,');
       schqy.sql.add(FF);
       schqy.sql.add(',');
       schqy.sql.add(FFq);
       schqy.SQL.Add(') values(:pno,:oo,:ooq)');
       schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
       schqy.ParamByName('ooq').AsFloat:=finishqy.fieldbyname('innum').AsFloat;       
       schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
       schqy.ExecSQL;
     end;
      end;
    end;
    finishqy.Next;
 end;
/////////////////////////////////////////////  完工的返工结束
/////////////////////////////////////////////  完工的时工的未返工开始
 with finishqy do
 begin
  sql.clear;
  sql.add('select a.pno,a.dept1 ,sum(a.totalin) totalin,sum(a.innum) innum  from scinmx a,scintb b where'); //,sum(innum) innum
  sql.add(' a.idno=b.idno and a.sg=''时工'' and a.work_again=0 and a.pno in (select distinct pno from scpno c ');
  sql.add(' where  year(c.finishdate)=:my and month(c.finishdate)=:mm ');
  sql.add(' and finishflg =1) and a.dept1>''1'' ');
  sql.add('group by a.pno,a.dept1');
  parambyname('my').asstring:=spinedit1.Text;
  parambyname('mm').asstring:=spinedit2.Text;
  open;
  first;
 end;

 //////
 while not finishqy.Eof do
 begin
   for  i:=0 to 50 do
    begin
      if  finishqy.Fields[1].Asstring=A[i] then
     begin
       ff:='FFs'+inttostr(i);
       ffq:='FFsq'+inttostr(i);
       pnoqy.SQL.Clear;
       pnoqy.sql.add('select pno from cb where pno=:pno');
       pnoqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
       pnoqy.Open;
      if pnoqy.RecordCount>0 then
        begin
        schqy.SQL.Clear;
        schqy.sql.add('update cb set ');
        schqy.SQL.Add(ff);
        schqy.sql.add('=');
        schqy.sql.Add(' :oo ');
        schqy.SQL.Add(',');
        schqy.SQL.add(ffq);
        schqy.sql.add('=');
        schqy.sql.Add(' :ooq ');
        schqy.SQL.Add('where pno=:pno');
        schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
        schqy.ParamByName('ooq').AsFloat:=finishqy.fieldbyname('innum').AsFloat;
        schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
        schqy.ExecSQL;
       end else
       begin
       schqy.sql.clear;
       schqy.SQL.add('insert into cb(pno,');
       schqy.sql.add(FF);
       schqy.SQL.Add(') values(:pno,:oo)');
       schqy.ParamByName('oo').AsFloat:=finishqy.fieldbyname('totalin').AsFloat;
       schqy.ParamByName('pno').Asstring:=finishqy.fieldbyname('pno').Asstring;
       schqy.ExecSQL;
     end;
//       showmessage(a[i]);
     end;
   end;
    finishqy.Next;
 end;
//////////////////////////////////////////////////////////完工的时工结束

  finishqy.SQL.Clear;
  finishqy.SQL.Add('select * from  scpno where year(finishdate)=:yy and month(finishdate)=:mm');
  finishqy.ParamByName('yy').AsString:=spinedit1.Text;
  finishqy.ParamByName('mm').asstring:=spinedit2.Text;
  finishqy.Open;
  while not finishqy.Eof do
  begin
    schqy.sql.clear;
    schqy.SQL.Add(' update cb set fgbjqty=');
    for i:=0 to 50 do
     begin
        ff:='ff'+inttostr(i);
        ffs:='ffs'+inttostr(i);
        ffg:='ffg'+inttostr(i);
        if i<50 then
        begin
        schqy.sql.add(ff);
        schqy.sql.add('+');
        schqy.SQL.add(ffs);
        schqy.sql.add('+');
        schqy.sql.add(ffg);
        schqy.sql.add('+');
        end else
        begin
        schqy.sql.add(ff);
        schqy.sql.add('+');
        schqy.SQL.add(ffs);
        schqy.sql.add('+');
        schqy.sql.add(ffg);
        end;
     end;
    schqy.SQL.Add('where pno=:pno');
    schqy.ParamByName('pno').AsString:=finishqy.fieldbyname('pno').AsString;
    schqy.execsql;   //更新总金额


    for i:=0 to 50 do