us_rpt2.~pas

是分布式粮库程序,是采用Delphi实现的

        for i:= 0 to FID_cnt do
                for j:=1 to HouseYear_cnt+OutYear_cnt+2 do
                        XLS_DrawBorder(j,i+7,j,i+7);


        XLS_FillCell(1,i+8,'单位负责人:',2);
        XLS_FillCell(4,i+8,'制表人:',2);
        XLS_FillCell(6,i+8,'制表日期:',2);


end;

procedure us_FillTable;
var s : string;
    iPos : integer;
    col,row : integer;
    i : integer;
    fSum1,fSum2 : integer;
    fTmp        : single;
    item:integer;
begin

        for i:=0 to FID_cnt-1 do
        begin
                FID_Sums[i] := 0;
                FID_House_sum[i] := 0;
                FID_Out_sum[i] := 0;
        end;

        if HouseYear_cnt>0 then
        for i:=0 to HouseYear_cnt-2 do
                HouseSum[i] := 0;

        if OutYear_cnt>0 then
        for i:=0 to OutYear_cnt-2 do
                OutSum[i] := 0;

        {s := 'select FS_FOODID,IY=YEAR(FS_INDATETIME),FS=sum(FS_SUM) '
            +'from ent_foodsstock t1,ent_depot t2 '
            +'where substring(FS_DEPOTID,1,4)='''+sUID+''' '
            +'and FS_DEPOTID=DEPOTID '
            +'and DEPOTTYPE<>''露天桩'''
            +'and FS_ATTRIBUTE='''+sAttr+''' '
            +'group by FS_FOODID,YEAR(FS_INDATETIME) '
            +'order by FS_FOODID';   }
       s:=' select t1.in_food,IY=year(IN_TIME) ,FS=sum(STORAGE) '+
          '  from ENT_INSTACK t1,ENT_DEPOT_INFO t2  '+
          '  where t1.Unit_ID='+sUID+'  and t1.Unit_ID=t2.Unit_ID and t1.Site_ID=t2.Site_ID and t1.Depot_ID=t2.Depot_ID '+
          '  and t2.DEPOT_TYPE<>''露天桩''  and t1.ATTRIBUTE='''+sAttr+''' '+
          '  group by t1.in_food,year(IN_TIME) '+
          '  order by t1.in_food ';

        us_qry.SQL.Clear;
        us_qry.SQL.Add(s);
        us_qry.Open;

        while not us_qry.Eof do
        begin
                {iPos := LocateHouse(us_qry.FieldByname('FS_FOODID').AsInteger,
                                    us_qry.FieldByName('IY').AsInteger);  }

                iPos := LocateHouse(trim(us_qry.FieldByName('in_food').AsString),
                                    us_qry.FieldByName('IY').AsInteger);
                col := iPos div 100;
                row := iPos mod 100;

                XLS_FillCell(col,row,us_qry.FieldByName('FS').AsString,1);
                Housesum[col-4] :=Housesum[col-4]+Round(us_qry.FieldByName('FS').AsFloat * 10);
                FID_House_sum[row-8] :=FID_House_sum[row-8]+Round(us_qry.FieldByName('FS').AsFloat * 10);
                us_qry.Next;
        end;
        if HouseYear_Cnt>0 then
        begin
                for i:=0 to HouseYear_Cnt-2 do
                begin
                        XLS_FillCell(4+i,7,intToStr(round(HouseSum[i]/10.0)),1);
                end;

                fSum1 := 0;
                for i:=0 to FID_cnt-1 do
                begin
                        if FID_House_Sum[i]>0 then
                        begin
                                XLS_FillCell(3,8+i,intToStr(round(FID_House_Sum[i] / 10.0)),1);
                        end;
                        fSum1 := fSum1 + FID_House_Sum[i];
                end;
                XLS_FillCell(3,7,intToStr(round(fSum1 / 10.0)),1);
        end;


        {s := 'select FS_FOODID,IY=YEAR(FS_INDATETIME),FS=sum(FS_SUM) '
            +'from ent_foodsstock t1,ent_depot t2 '
            +'where substring(FS_DEPOTID,1,4)='''+sUID+''' '
            +'and FS_DEPOTID=DEPOTID '
            +'and DEPOTTYPE=''露天桩'''
            +'and FS_ATTRIBUTE='''+sAttr+''' '
            +'group by FS_FOODID,YEAR(FS_INDATETIME) '
            +'order by FS_FOODID'; }
        s:=' select t1.in_food,IY=year(IN_TIME) ,FS=sum(STORAGE) '+
          '  from ENT_INSTACK t1,ENT_DEPOT_INFO t2  '+
          '  where t1.Unit_ID='+sUID+'  and t1.Unit_ID=t2.Unit_ID and t1.Site_ID=t2.Site_ID and t1.Depot_ID=t2.Depot_ID '+
          '  and t2.DEPOT_TYPE=''露天桩''  and t1.ATTRIBUTE='''+sAttr+''' '+
          '  group by t1.in_food,year(IN_TIME) '+
          '  order by t1.in_food  ';

        us_qry.SQL.Clear;
        us_qry.SQL.Add(s);
        us_qry.Open;

        while not us_qry.Eof do
        begin
                iPos := LocateOut(trim(us_qry.FieldByname('in_food').Asstring),
                                    us_qry.FieldByName('IY').AsInteger);
               { iPos := LocateHouse(us_qry.FieldByName('in_food').AsString,
                                    us_qry.FieldByName('IY').AsInteger); }
                col := (iPos div 100);
                row := iPos mod 100;

                XLS_FillCell(col,row,us_qry.FieldByName('FS').AsString,1);
                Outsum[col-HouseYear_cnt-4] :=Outsum[col-HouseYear_cnt-4]+Round(us_qry.FieldByName('FS').AsFloat * 10);
                FID_Out_sum[row-8] :=FID_Out_sum[row-8]+Round(us_qry.FieldByName('FS').AsFloat * 10);
                us_qry.Next;
        end;
        if OutYear_Cnt>0 then
        begin
                for i:=0 to OutYear_Cnt-2 do
                        XLS_FillCell(4+i+HouseYear_cnt,7,intToStr(round(OutSum[i]/10.0)),1);

                fSum2 := 0;
                for i:=0 to FID_cnt-1 do
                begin
                        if FID_Out_Sum[i]>0 then
                                XLS_FillCell(3+HouseYear_cnt,8+i,intToStr(round(FID_Out_Sum[i]/10.0)),1);
                        fSum2 := fSUm2 + FID_Out_Sum[i];
                end;
                XLS_FillCell(3+HouseYear_cnt,7,intToStr(round(fSum2/10.0)),1);
        end;

        for i:=0 to FID_cnt-1 do
        begin
                FID_sums[i] := FID_House_sum[i]+FID_Out_sum[i];
                if FID_Sums[i]>0 then
                begin
                        XLS_FillCell(2,8+i,intToStr(round(FID_Sums[i]/10.0)),1);
                end;
        end;
        XLS_FillCell(2,7,intToStr(round((fSum1+fSum2) / 10.0)),1);

end;

function LocateHouse(iFID : string; iYear : integer) : integer;
var i,j,re:integer;
begin
        for i:=0 to HouseYear_cnt-1 do
                if HouseYears[i]=iYear then break;
        re := i+4;

        for j:=0 to FID_cnt-1 do
                if trim(FIDs[j])=trim(iFID) then break;

        re := (i+4)*100+(j+8);

        Result := re;
end;

function LocateOut(iFID : string; iYear : integer) : integer;
var i,j,re:integer;
begin
        for i:=0 to OutYear_cnt-1 do
                if OutYears[i]=iYear then break;
        re := i+HouseYear_cnt+4;

        for j:=0 to FID_cnt-1 do
                if trim(FIDs[j])=trim(iFID) then break;

        re := re*100+(j+8);

        Result := re;
end;

procedure US_CreateReport2(iUID : integer ;const pAttr : PChar);
begin
  {us_db2 := TDatabase.Create(nil);
  with us_db2 do
  begin
        AliasName := 'HZ_US';
        DatabaseName :='HZ_US';
        Params.Clear;
        Params.Add('USER NAME=hzdba');
        Params.Add('PASSWORD=project');
        KeepConnection := True;
        LoginPrompt := False;
        Connected := True;
  end;
  }
  us_qry := TQuery.Create(nil);
  us_qry.DatabaseName := 'LYDB';

  //sUID := copy(IntToStr(iUID),2,4);
  sUID:=inttostr(iUID);
  sAttr := pAttr;

  us_qry.SQL.Clear;
  us_qry.SQL.Add('select * from ent_Unit_info where UNIT_ID = '+sUID );
  us_qry.Open;
  sUName := us_qry.FieldByName('UNIT_NAME').AsString;

  XLS_Create;
  XLS_SetWorkSheetFontSize('宋体',10);
  us_DrawFrame;

  us_FillTable;

  XLS_Show;
end;

procedure US_FreeReport2;
begin
  us_qry.Destroy;
end;

end.