us_rpt2.pas

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

unit us_rpt2;

interface
uses    dbtables,Sysutils,dmPub;

var
        //us_db2  : TDatabase;
        us_qry  : TQuery;
        sUID    : string;
        sUNAme  : string;
        sAttr   : string;

        FIDs    : array of string;
        FID_Cnt : integer;
        FIDNames: array of string;
        FID_Sums : array of integer;
        FID_House_sum,FID_Out_sum : array of integer;

        HouseYears      : array of integer;
        HouseYear_cnt   : integer;
        HouseSum        : array of integer;

        OutYears        : array of integer;
        OutYear_cnt     : integer;
        OutSum          : array of integer;


        procedure US_CreateReport2(iUID : integer ;const pAttr : PChar);
        procedure US_FreeReport2;

        procedure us_DrawFrame;
        procedure us_FillTable;

        function LocateHouse(iFID : string; iYear : integer) : integer;
        function LocateOut(iFID : string; iYear : integer) : integer;
        function GetTotalFoodID(iUnit_ID,iSite_ID,iDepot_ID,iINStack_ID:integer):string;

implementation
uses URpt4;



function GetTotalFoodID(iUnit_ID,iSite_ID,iDepot_ID,iINStack_ID:integer):string;
var
  sStr,STemp:string;
begin
  STemp:='0000'+inttostr(iUnit_ID);
  sStr:=copy(sTemp,length(sTemp)-3,4);
  STemp:='0000'+inttostr(iSite_ID);
  sStr:=sStr+copy(sTemp,length(sTemp)-3,4);
  STemp:='0000'+inttostr(iDepot_ID);
  sStr:=sStr+copy(sTemp,length(sTemp)-3,4);
  STemp:='0000'+inttostr(iINStack_ID);
  sStr:=sStr+copy(sTemp,length(sTemp)-3,4);
  result:=sStr;

end;

procedure us_DrawFrame;
var s: string;
    i,j : integer;
    y,m,d : word;
begin

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

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

        HouseYear_cnt := us_qry.RecordCount;
        if HouseYear_cnt>0 then
        begin
                SetLength(HouseYears,HouseYear_cnt);
                SetLength(HouseSum,HouseYear_cnt);
                Inc(HouseYear_cnt);
                i := 0;
                while not us_qry.Eof do
                begin
                        HouseYears[i] := us_qry.FieldByName('IY').AsInteger;
                        Inc(i);
                        us_qry.Next;
                end;
        end;

        {s := 'select distinct IY=YEAR(FS_INDATETIME) '
            +'from ent_foodsstock t1,ent_depot t2 '
            +'where substring(FS_DEPOTID,1,4)='''+sUID+''' '
            +'and FS_DEPOTID=DEPOTID '
            +'and DEPOTTYPE=''露天桩'' '
            +'and FS_ATTRIBUTE='''+sAttr+'''';  }
        s:='select distinct IY=year(IN_TIME) ' +
            'from ENT_INSTACK t1,ENT_DEPOT_INFO t2 ' +
            ' where  t1.Unit_ID=t2.Unit_ID and t1.Site_ID=t2.Site_ID and t1.Depot_ID=t2.Depot_ID '+
            ' and DEPOT_TYPE=''露天桩'''+
            ' and ATTRIBUTE='''+sAttr+''' and t1.Unit_ID='+sUID;
        us_qry.SQL.Clear;
        us_qry.SQL.Add(s);
        us_qry.Open;

        OutYear_cnt := us_qry.RecordCount;
        if OutYear_cnt>0 then
        begin
                SetLength(OutYears,OutYear_cnt);
                SetLength(OutSum,OutYear_cnt);
                Inc(OutYear_cnt);
                i := 0;
                while not us_qry.Eof do
                begin
                        OutYears[i] := us_qry.FieldByName('IY').AsInteger;
                        Inc(i);
                        us_qry.Next;
                end;
        end;

       { s := 'select distinct FS_FOODID,FT_NAME '
            +'from ent_foodsstock,ent_foodtype '
            +'where substring(FS_DEPOTID,1,4)='''+sUID+''' '
            +'and FS_FOODID =FT_ID '
            +'and FS_ATTRIBUTE='''+sAttr+''' order by FS_FOODID';  }
       {s:=' select substring(''0000''+ltrim(str(t1.Unit_ID)),len(''0000''+ltrim(str(t1.Unit_ID)))-3,4)+ '+
          ' substring(''0000''+ltrim(str(t1.Site_ID)),len(''0000''+ltrim(str(t1.Site_ID)))-3,4)+'+
          ' substring(''0000''+ltrim(str(t1.Depot_ID)),len(''0000''+ltrim(str(t1.Depot_ID)))-3,4)+'+
          ' substring(''0000''+ltrim(str(t1.INStack_ID)),len(''0000''+ltrim(str(t1.INStack_ID)))-3,4) as FS_FOODID  '+
          ' IN_FOOD from ENT_INSTACK t1,ENT_FOOD_TYPE t2    '+
          '  where t1.Unit_ID='+sUID+' and t1.IN_FOOD =t2.food_type  '+
          '  and t1.ATTRIBUTE='''+sAttr+''' order by FS_FOODID ';     }
       s:=' select distinct t2.food_type from ENT_INSTACK t1,ENT_FOOD_TYPE t2    '+
          '  where t1.Unit_ID='+sUID+' and t1.IN_FOOD =t2.food_type  '+
          '  and t1.ATTRIBUTE='''+sAttr+''' order by t2.food_type ';

        us_qry.SQL.Clear;
        us_qry.SQL.Add(s);
        us_qry.Open;
        FID_cnt := us_qry.RecordCount;
        if FID_cnt<0 then Exit;
        SetLength(FIDs,FID_cnt);
        SetLength(FIDNames,FID_cnt);
        SetLength(FID_Sums,FID_cnt);
        SetLength(FID_House_sum,FID_cnt);
        SetLength(FID_Out_sum,FID_cnt);
        for i:=0 to FID_cnt-1 do
        begin
                FID_Sums[i] := 0;
                FID_House_sum[i] := 0;
                FID_Out_sum[i] := 0;
        end;
        i := 0;
        while not us_qry.Eof do
        begin
                FIDs[i] :=trim(us_qry.FieldByName('food_type').AsString);//GetTotalFoodID(us_qry.FieldByName('Unit_ID').Asinteger,us_qry.FieldByName('Site_ID').asinteger,us_qry.FieldByName('Depot_ID').Asinteger,us_qry.FieldByName('FOODPOSITION_ID').Asinteger);
                FIDNames[i] := trim(us_qry.FieldByName('food_type').AsString);
                Inc(i);
                us_qry.Next;
        end;

        if (HouseYear_cnt+OutYear_cnt)=0 then
                Exit;
        XLS_Merge(1,1,2+HouseYear_cnt+OutYear_cnt,1);
        XLS_FillRange(1,1,2+HouseYear_cnt+OutYear_cnt,1,sAttr+'储藏情况统计表(汇总)');

        DecodeDate(Date,y,m,d);
        XLS_Merge(1,2,2+HouseYear_cnt+OutYear_cnt,2);
        XLS_FillRange(1,2,2+HouseYear_cnt+OutYear_cnt,2,IntToStr(y)+'年'+IntToStr(m)+'月');

        XLS_FillCell(1,3,'填报单位:'+sUName,2);

        XLS_DrawBorder(1,4,2+HouseYear_cnt+OutYear_cnt,6);
        XLS_FillCell(1,5,'品种',0);

        XLS_FillCell(2,5,'合计数量',0);
        XLS_FillCell(2,6,'公斤',0);
        XLS_DrawBorder(2,4,2,6);

        XLS_Merge(3,4,HouseYear_cnt+OutYear_cnt+2,4);
        XLS_DrawBorder(3,4,HouseYear_cnt+OutYear_cnt+2,4);
        XLS_FillRange(3,4,HouseYear_cnt+OutYear_cnt+2,4,'储藏年限及数量(公斤)');

        if HouseYear_cnt>0 then
        begin
                XLS_Merge(3,5,HouseYear_cnt+2,5);
                XLS_DrawBorder(3,5,HouseYear_cnt+2,5);
                XLS_FillRange(3,5,HouseYear_cnt+2,5,'仓库');

                XLS_FillCell(3,6,'数量',0);
                XLS_DrawBorder(3,6,3,6);
                for i:=1 to HouseYear_cnt-1 do
                begin
                        XLS_FillCell(i+3,6,IntToStr(HouseYears[i-1]),0);
                        XLS_DrawBorder(i+3,6,i+3,6);
                end;
        end;

        if OutYear_cnt>0 then
        begin
                XLS_Merge(HouseYear_cnt+3,5,HouseYear_cnt+OutYear_cnt+2,5);
                XLS_DrawBorder(HouseYear_cnt+3,5,HouseYear_cnt+OutYear_cnt+2,5);
                XLS_FillRange(HouseYear_cnt+3,5,HouseYear_cnt+OutYear_cnt+2,5,'露天');

                XLS_FillCell(HouseYear_cnt+3,6,'数量',0);
                XLS_DrawBorder(HouseYear_cnt+3,6,HouseYear_cnt+3,6);
                for i:=1 to OutYear_cnt-1 do
                begin
                        XLS_FillCell(i+HouseYear_cnt+3,6,IntToStr(OutYears[i-1]),0);
                        XLS_DrawBorder(i+HouseYear_cnt+3,6,i+HouseYear_cnt+3,6);
                end;
        end;

        XLS_FillCell(1,7,'合计',0);
        for i:=1 to FID_cnt do
        begin
                XLS_FillCell(1,7+i,FIDNames[i-1],0);
        end;