urpt2_1.pas

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

unit URpt2_1;

interface
uses    dbtables,Sysutils;

var
        u2_db           : TDataBase;
        u2_qry          : TQuery;
        sUID            : string;
        sUName          : string;
        iAttrCnt        : integer;

        iGrainNR        : integer;
        iOilNR          : integer;
        GrainList       : array of string;
        OilList         : array of string;

        Grain_Sum       : single;
        Grain_PK_N      : integer;
        Grain_PK_O      : integer;
        Grain_PK_M      : integer;

        Oil_Sum         : single;

        procedure u2_DrawFrame;
        procedure u2_FillTable;
        function  FindFoodID(FID : string) : integer;
        procedure CreateReport_2(const UID : integer);
        procedure FreeReport_2;

implementation
uses uRpt4;

procedure CreateReport_2(const UID : integer);
begin
 { u2_db := TDatabase.Create(nil);
  with u2_db do
  begin
        AliasName := 'HZ_BS';
        DatabaseName :='HZ_BS';
        Params.Clear;
        Params.Add('USER NAME=hzdba');
        Params.Add('PASSWORD=project');
        KeepConnection := True;
        LoginPrompt := False;
        Connected := True;
  end;   }

  u2_qry := TQuery.Create(nil);
  u2_qry.DatabaseName := 'LYDB';

  //sUID := copy(IntToStr(UID),2,4);
  sUID := IntToStr(UID);

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

  u2_DrawFrame;

  u2_FillTable;

  XLS_Show;

end;


procedure FreeReport_2;
begin
  //u2_db.Connected := False;
  //u2_db.Destroy;
end;

procedure u2_DrawFrame;
var s : string;
    iWidth : integer;
    i,j : integer;
    y,m,d : word;
begin
        s := 'select * from ent_unit_info where UNIT_ID='''+sUID+'''';
        u2_qry.SQL.Clear;
        u2_qry.SQL.Add(s);
        u2_qry.Open;
        sUName := u2_qry.FieldByName('UNIT_NAME').AsString;

        s := 'select distinct ATTRIBUTE '
            +'from ENT_INSTACK where '
            +'UNIT_ID='''+sUID+'''';
        u2_qry.SQL.Clear;
        u2_qry.SQL.Add(s);
        u2_qry.Open;
        iAttrCnt := u2_qry.RecordCount;
        u2_qry.First;

        iWidth := 2+5*iAttrCnt;

        XLS_Merge(1,1,iWidth,1);
        //XLS_DrawBorder(1,1,iWidth,1);
        XLS_SetHeight(1,26);
        XLS_FillRange(1,1,iWidth,1,'杭州市粮食收储公司');

        XLS_Merge(1,2,iWidth,2);
        //XLS_DrawBorder(1,2,iWidth,2);
        XLS_SetHeight(2,20);
        XLS_FillRange(1,2,iWidth,2,'储备粮油实物台帐汇总表');


        DecodeDate(Date,y,m,d);
        XLS_Merge(1,3,iWidth,3);
        //XLS_DrawBorder(1,2,iWidth,2);
        XLS_SetHeight(2,20);
        XLS_FillRange(1,3,iWidth,3,IntToStr(y)+'年'+IntToStr(m)+'月'+IntToStr(d)+'日');

        XLS_FillCell(1,4,'填报单位:(盖章) '+sUName,2);

        XLS_FillCell(iWidth,4,'单位:公斤',1);


        XLS_DrawBorder(1,5,2,8);

        for i:=0 to iAttrCnt-1 do
        begin
                XLS_DrawBorder(3+i*5,5,7+i*5,8);

                XLS_Merge(3+i*5,5,7+i*5,5);
                XLS_FillRange(3+i*5,5,7+i*5,5,u2_qry.FieldByName('ATTRIBUTE').AsString);
                u2_qry.Next;

                XLS_DrawBorder(3+i*5,6,3+i*5,8);
                XLS_FillCell(3+i*5,7,'数量',0);

                XLS_DrawBorder(4+i*5,6,7+i*5,8);
                XLS_FillCell(4+i*5,7,'麻袋',0);
                XLS_FillCell(4+i*5,8,'(条)',0);

                XLS_DrawBorder(5+i*5,7,7+i*5,8);
                XLS_DrawBorder(6+i*5,7,6+i*5,8);
                XLS_FillCell(5+i*5,7,'标新麻袋',0);
                XLS_FillCell(6+i*5,7,'标旧麻袋',0);
                XLS_FillCell(7+i*5,7,'杂麻袋',0);
        end;

       { s := 'select distinct FDSTK_FOODID from ent_foodsstock '
            +'where substring(depot_id,1,4)='''+sUID+''' and FDSTK_FOODID<100'
            +'order by FDSTK_FOODID';   }
       s := 'select distinct IN_FOOD from ENT_INSTACK  '
            +'where Unit_ID='+sUID   //' and FDSTK_FOODID<100'
            +'  order by IN_FOOD';
        u2_qry.SQL.Clear;
        u2_qry.SQL.Add(s);
        u2_qry.Open;
        iGrainNR := u2_qry.RecordCount;
        SetLength(GrainList,iGrainNR);
        for i:= 1 to iGrainNR do
        begin
                GrainList[i-1] := u2_qry.FieldByName('IN_FOOD').Asstring;
                u2_qry.Next;
        end;

        {s := 'select distinct FDSTK_FOODID from ent_foodsstock '
            +'where substring(depot_id,1,4)='''+sUID+''' and FDSTK_FOODID>100'
            +'order by FDSTK_FOODID';   }
        s := 'select distinct IN_FOOD from ENT_INSTACK  '
            +'where Unit_ID=1111'    //' and FDSTK_FOODID<100'
            +'  order by IN_FOOD';
        u2_qry.SQL.Clear;
        u2_qry.SQL.Add(s);
        u2_qry.Open;
        iOilNR := u2_qry.RecordCount;
        SetLength(OilList,iOilNR);
        for i:= 1 to iOilNR do
        begin
                OilList[i-1] := u2_qry.FieldByName('IN_FOOD').Asstring;
                u2_qry.Next;
        end;

        if iGrainNR>0 then
        begin
                XLS_Merge(1,9,2,9);
                XLS_FillRange(1,9,2,9,'粮食合计');
                for i:=3 to 2+iAttrCnt*5 do
                        XLS_DrawBorder(i,9,i,9);
                XLS_DrawBorder(1,10,1,iGrainNR+9);
                for i:=10 to iGrainNR+9 do
                begin
                        //s := 'select FOODTP_NAME  from ENT_FOODTYPE where FOODTP_ID='+IntToStr(GrainList[i-10]);
                        //u2_qry.SQL.Clear;
                       // u2_qry.SQL.Add(s);
                       // u2_qry.Open;
                       // XLS_FillCell(2,i,u2_qry.FieldByName('FOODTP_NAME').AsString,1);
                        XLS_FillCell(2,i,GrainList[i-10],1);
                        XLS_DrawBorder(2,i,2,i);
                        for j:=3 to 2+iAttrCnt*5 do
                                XLS_DrawBorder(j,i,j,i);
                end;
        end;

        if iOilNR>0 then
        begin
                XLS_Merge(1,10+iGrainNR,2,10+iGrainNR);
                XLS_FillRange(1,10+iGrainNR,2,10+iGrainNR,'食油合计');
                for i:=3 to 2+iAttrCnt*5 do
                        XLS_DrawBorder(i,10+iGrainNR,i,10+iGrainNR);
                XLS_DrawBorder(1,iGrainNR+11,1,iOilNR+iGrainNR+10);
                for i:=iGrainNR+11 to iOilNR+iGrainNR+10 do
                begin
                        //s := 'select FOODTP_NAME  from ENT_FOODTYPE where FOODTP_ID='+IntToStr(OilList[i-iGrainNR-11]);
                        //u2_qry.SQL.Clear;
                       // u2_qry.SQL.Add(s);
                       // u2_qry.Open;
                       // XLS_FillCell(2,i,u2_qry.FieldByName('FOODTP_NAME').AsString,1);
                        XLS_FillCell(2,i,OilList[i-iGrainNR-11],1);
                        XLS_DrawBorder(2,i,2,i);
                        for j:=3 to 2+iAttrCnt*5 do
                                XLS_DrawBorder(j,i,j,i);
                end;
        end;

        XLS_FillCell(1,iOilNR+iGrainNR+11,'单位负责人:',2);
        XLS_FillCell(4,iOilNR+iGrainNR+11,'统计负责人:',2);
        XLS_FillCell(7,iOilNR+iGrainNR+11,'制表人:',2);
        XLS_FillCell(10,iOilNR+iGrainNR+11,'制表日期:',2);

end;

procedure u2_FillTable;
var i: integer;
    s : string;
    sAttr : string;
    qry : TQuery;
    Col,Row : integer;
    iNew,iOld,iMisc : integer;
    fid : string;
begin
    {s := 'select ATTRIBUTE,FDSTK_FOODID,FS=sum(FDSTK_SUM),NS=ISNULL(sum(PK_NEW),0),OS=ISNULL(sum(PK_OLD),0),MS=ISNULL(sum(PK_MISC),0) '
        +'from ENT_FOODSSTOCK t1 left join ENT_PACKAGE t2 '
        +'on t1.DEPOT_ID = t2.DEPOT_ID '
        +'and   t1.FDSTK_DEPOTPOS = t2.PK_DEPOTPOS '
        +'and    t1.FDSTK_INDATETIME = t2.PK_INDATETIME '
        +'where substring(t1.DEPOT_ID,1,4)='''+sUID+''' '
        +'group by ATTRIBUTE,FDSTK_FOODID '
        +'order by ATTRIBUTE';  }
    s :=' select ATTRIBUTE,IN_FOOD,FS=sum(STORAGE),NS=ISNULL(sum(R_NEW),0),OS=ISNULL(sum(R_OLD),0),MS=ISNULL(sum(R_MISC),0)'+
        ' from ENT_INSTACK  where Unit_ID='+sUID+'   group by ATTRIBUTE,IN_FOOD  order by ATTRIBUTE ';
    u2_qry.SQL.Clear;
    u2_qry.SQL.Add(s);
    u2_qry.Open;
    if u2_qry.IsEmpty then Exit;

    sAttr := u2_qry.FieldByName('ATTRIBUTE').AsString;
    Col := 3;

    Grain_Sum   := 0;
    Grain_PK_N  := 0;
    Grain_PK_O  := 0;
    Grain_PK_M  := 0;

    Oil_Sum     := 0;

    while not u2_qry.Eof do
    begin
        if trim(sAttr)<>trim(u2_qry.FieldByName('ATTRIBUTE').AsString) then
        begin
                if Grain_Sum>0 then
                        XLS_FillCell(Col,9,FloatToStr(Grain_Sum),1);
                if (Grain_PK_N+Grain_PK_O+Grain_PK_M)>0 then
                        XLS_FillCell(Col+1,9,IntToStr(Grain_PK_N+Grain_PK_O+Grain_PK_M),1);
                if Grain_PK_N>0 then
                        XLS_FillCell(Col+2,9,IntToStr(Grain_PK_N),1);
                if Grain_PK_O>0 then
                        XLS_FillCell(Col+3,9,IntToStr(Grain_PK_O),1);
                if Grain_PK_M>0 then
                        XLS_FillCell(Col+4,9,IntToStr(Grain_PK_M),1);

                if Oil_Sum>0 then
                        XLS_FillCell(Col,10+iGrainNR,FloatToStr(Oil_Sum),1);


                sAttr := u2_qry.FieldByName('ATTRIBUTE').AsString;
                Col := Col+5;

                Grain_Sum   := 0;
                Grain_PK_N  := 0;
                Grain_PK_O  := 0;
                Grain_PK_M  := 0;
                Oil_Sum     := 0;
        end;

        fid := u2_qry.FieldByName('IN_FOOD').Asstring;
        Row := FindFoodID(fid);

        XLS_FillCell(Col,Row,u2_qry.FieldByname('FS').AsString,1);
        iNew := u2_qry.FieldByname('NS').AsInteger;
        iOld := u2_qry.FieldByname('OS').AsInteger;
        iMisc := u2_qry.FieldByname('MS').AsInteger;
        if iNew>0 then
                XLS_FillCell(Col+2,Row,IntToStr(iNew),1);
        if iOld>0 then
                XLS_FillCell(Col+3,Row,IntToStr(iOld),1);
        if iMisc>0 then
                XLS_FillCell(Col+4,Row,IntToStr(iMisc),1);

        i := iNew+iOld+iMisc;
        if i>0 then
                XLS_FillCell(Col+1,Row,IntToStr(i),1);

        //if fid<100 then
        if true then
        begin
                Grain_Sum := Grain_Sum+u2_qry.FieldByname('FS').AsFloat;
                Grain_PK_N := Grain_PK_N + iNew;
                Grain_PK_O := Grain_PK_O + iOld;
                Grain_PK_M := Grain_PK_M + iMisc;
        end
        else
        begin
                Oil_Sum := Oil_Sum +u2_qry.FieldByname('FS').AsFloat;
        end;

        u2_qry.Next;
    end;

    if Grain_Sum>0 then
            XLS_FillCell(Col,9,FloatToStr(Grain_Sum),1);
    if (Grain_PK_N+Grain_PK_O+Grain_PK_M)>0 then
            XLS_FillCell(Col+1,9,IntToStr(Grain_PK_N+Grain_PK_O+Grain_PK_M),1);
    if Grain_PK_N>0 then
            XLS_FillCell(Col+2,9,IntToStr(Grain_PK_N),1);
    if Grain_PK_O>0 then
            XLS_FillCell(Col+3,9,IntToStr(Grain_PK_O),1);
    if Grain_PK_M>0 then
            XLS_FillCell(Col+4,9,IntToStr(Grain_PK_M),1);

    if Oil_Sum>0 then
            XLS_FillCell(Col,10+iGrainNR,FloatToStr(Oil_Sum),1);


end;


function FindFoodID(FID : string) : integer;
var i : integer;
    r : integer;
begin
       // if FID<100 then
         if true then
        begin
                for i:= 1 to iGrainNR do
                        if trim(GrainList[i-1])=trim(FID) then
                        begin
                                r := i+9;
                                Break;
                        end;
        end
        else
        begin
                for i:=1 to iOilNR do
                        if Trim(OilList[i-1]) = trim(FID) then
                        begin
                                r := i+iGrainNR+10;
                                Break;
                        end;
        end;
        Result := r;
end;

end.