urpt1_1.pas

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

unit URpt1_1;

interface
uses dbtables,Sysutils;
type
  PFoodType=^TFoodType;
  PCost=^TCost;
  PData=^TData;
  TFoodType=Record
    FoodID : string;
    FoodName:string;
    Cost:PCost;           //point to a series of different cost of one type of food
    Count:integer;        //how many different costs one type of food have
    Next:PFoodType;
  end;
  TCost=Record
    CostPerUnit:string;
    Pos:integer;          //the colum position of a cost
    TotalInCol:Single;    //this item is -1 when created,it is  used to calculate sum in a colum when we add datas to the report,function GetCostPos uses it.
    Next:PCost;
  end;
  TData=Record
    UnitID:string;
    UnitName:string;
    FoodID:string;
    CostPerUnit:string;
    Sum:string;
    Next:PData;
  end;

  procedure CreateReport_1(const attr : PChar);
  procedure FreeReport_1;


  procedure MakeReportHead(Title,Date,Firmname,unitname:string);
  procedure MakeFrame(h,v:integer);
  procedure WriteIn(h,v:integer);
  //Get one cost's colum position and calculate TotalInCol(Contained in Record TCost)
  function GetCostPos(foodid:string;cost:string;sum:string):integer;

var
  Typehead:PFoodType;
  WidthOfReport:integer;
  HeightOfReport:integer;
  SumOfFoodType:integer;
  u1_Query1 : TQuery;
  u1_attr : string;
  u1_DB : TDatabase;

implementation
uses URpt4;



procedure CreateReport_1(const attr : PChar);
var
  y,m,d : word;
begin
  {u1_db := TDatabase.Create(nil);
  with u1_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;  }

  u1_Attr := attr;
  u1_Query1 := TQuery.Create(nil);
  u1_Query1.DatabaseName := 'LYDB';
  XLS_Create;
  XLS_SetWorkSheetFontSize('宋体',10);
  XLS_Line(1,5,1,6);
  DecodeDate(Date,y,m,d);
  MakeReportHead(u1_Attr+'粮油总库存情况表',IntToStr(y)+'年'+IntToStr(m)+'月'+IntToStr(d)+'日','杭州市粮食收储有限公司','单位：公斤');
  MakeFrame(3,6);
  XLS_FillCell(1,7,'合计',0);
  WriteIn(1,8);
  XLS_BorderLine(1,7,WidthOfReport,HeightOfReport+7);
  XLS_SetWidth(1,22);
  XLS_Show;

end;

procedure FreeReport_1;
begin
  //u1_db.Connected := False;
  //u1_db.Destroy;
end;

procedure MakeReportHead(Title,Date,Firmname,unitname:string);
begin
  XLS_Merge(4,2,11,2);
  XLS_SetSize(4,2,11,2,15);
  XLS_fillrange(4,2,11,2,Title);

  XLS_Merge(7,3,8,3);
  XLS_fillrange(7,3,8,3,Date);

  XLS_FillCell(1,4,'填报单位：',0);

  XLS_Merge(2,4,5,4);
  XLS_fillrange(2,4,5,4,Firmname);

  XLS_Merge(10,4,12,4);
  XLS_fillrange(10,4,12,4,unitname);
end;

procedure MakeFrame(h,v:integer);
var s:string;
    temp:PFoodType;
    TempCost:PCost;
    previous:string;
    SumOfType:integer;
    ExistOil:Boolean;
begin
  // 查询粮油品种名称,价格分布
  s:='SELECT DISTINCT IN_FOOD,isnull(PRICE,0) as price'
    +' FROM ENT_INSTACK where In_FOOD is not null '
    +' ORDER BY IN_FOOD';
  with u1_Query1 do
  begin
    SQL.Clear;
    SQL.Add(s);
    Open;
    First;
    New(Typehead);
    temp:=Typehead;
    temp^.Count:=1;
    temp^.FoodID:=FieldByName('IN_FOOD').Asstring;
    temp^.FoodName:=FieldByName('IN_FOOD').AsString;
    previous:=temp^.FoodID;
    temp^.Next:=nil;
    New(TempCost);
    temp^.Cost:=TempCost;
    TempCost^.CostPerUnit:=FieldByName('PRICE').AsString;
    TempCost^.Pos:=-1;
    TempCost^.TotalInCol:=0;
    TempCost^.Next:=nil;
    Next;
    while not EOF do
    begin
      if trim(FieldByName('IN_FOOD').Asstring)=trim(previous) then
      begin
        New(TempCost^.Next);
        TempCost:=TempCost^.Next;
        TempCost^.CostPerUnit:=FieldByName('PRICE').AsString;
        TempCost^.Pos:=-1;
        TempCost^.TotalInCol:=0;
        TempCost^.Next:=nil;
        inc(temp^.Count);
        Next;
        Continue;
      end;
      New(temp^.Next);
      temp:=temp^.Next;
      temp^.Count:=1;
      temp^.FoodID:=FieldByName('IN_FOOD').Asstring;
      temp^.FoodName:=FieldByName('IN_FOOD').AsString;
      previous:=temp^.FoodID;
      temp^.Next:=nil;
      New(TempCost);
      temp^.Cost:=TempCost;
      TempCost^.CostPerUnit:=FieldByName('PRICE').AsString;
      TempCost^.Pos:=-1;
      TempCost^.TotalInCol:=0;
      TempCost^.Next:=nil;
      Next;
    end;
  end;

  SumOfType:=0;
  SumOfFoodType:=0;
  temp:=Typehead;
  ExistOil:=False;
  while temp<>nil do
  begin
    //if (temp^.FoodID > 100) and (ExistOil=False) then  //it is some oil,the prerequisite is that Oil's id is greater than foods' id
    if (false) and (ExistOil=False) then
    begin
      ExistOil:=True;
      XLS_FillCell(h,v,'合',0);
      XLS_FillCell(h,v+1,'计',0);
      inc(h);
      SumOfFoodType:=SumOfType+1;   //include the '合计' colum of oil
      SumOfType:=0;
    end;
    SumOfType:=SumOfType+temp^.Count;
    XLS_Merge(h,v,h+temp^.Count-1,v);
    XLS_FillRange(h,v,h+temp^.Count-1,v,temp^.FoodName);
    XLS_DrawBorder(h,v,h+temp^.Count-1,v);
    TempCost:=temp^.Cost;
    while TempCost<>nil do
    begin
      XLS_FillCell(h,v+1,TempCost^.CostPerUnit,0);
      XLS_DrawBorder(h,v+1,h,v+1);
      TempCost^.Pos:=h;
      inc(h);
      TempCost:=TempCost.Next;
    end;
    temp:=temp^.Next;
  end;

  if ExistOil=False then
  begin
    XLS_Merge(2,5,2+SumOfType,5);
    XLS_FillRange(2,5,2+SumOfType,5,'粮食');
    XLS_DrawBorder(2,5,2+SumOfType,5);
  end
  else
  begin
    XLS_Merge(2,5,1+SumOfFoodType,5);
    XLS_FillRange(2,5,1+SumOfFoodType,5,'粮食');
    XLS_DrawBorder(2,5,1+SumOfFoodType,5);

    XLS_Merge(2+SumOfFoodType,5,2+SumOfFoodType+SumOfType,5);
    XLS_FillRange(2+SumOfFoodType,5,2+SumOfFoodType+SumOfType,5,'食油');
    XLS_DrawBorder(2+SumOfFoodType,5,2+SumOfFoodType+SumOfType,5);
  end;

  WidthOfReport:=SumOfFoodType+2+SumOfType;

  XLS_FillCell(1,5,'粮油',1);
  XLS_FillCell(1,6,'单位',-1);
  XLS_DrawBorder(1,5,1,6);

  XLS_FillCell(2,6,'合计',0);
 // XLS_FillCell(2,7,'计',0);
 // XLS_DrawBorder(2,6,2,7);
end;


procedure WriteIn(h,v:integer);
var s,previousid:string;
    DataHead,temp:PData;
    Typetemp:PFoodType;
    TotalFoodInRow:Single;
    TotalOilInRow:Single;
    TotalFoodInSheet:Single;
    TotalOilInSheet:Single;
begin
  // 查询
 { s:='SELECT id=SUBSTRING(DEPOT_ID,1,4),UNIT_NAME,FDSTK_FOODID,FDSTK_COST,Total=SUM(FDSTK_SUM)'
     +' FROM ENT_FOODSSTOCK,ENT_UNIT_INFO'
     +' WHERE UNIT_ID=SUBSTRING(DEPOT_ID,1,4)'
     +' AND ATTRIBUTE='''+u1_Attr+''''
     +' GROUP BY SUBSTRING(DEPOT_ID,1,4),UNIT_NAME,FDSTK_FOODID,FDSTK_COST'; }
 { s:=' SELECT id=t1.Unit_iD,t2.UNIT_NAME,IN_FOOD, price=isnull(price,0) ,Total=isnull(SUM(storage),0) '+
     ' FROM ENT_INSTACK t1,ENT_UNIT_INFO t2  WHERE     t1.UNIT_ID=t2.UNIT_ID '+
     ' AND ATTRIBUTE='''+u1_Attr+''''+
     ' GROUP BY t1.Unit_iD,t2.UNIT_NAME,IN_FOOD,price ';  }

  //修改2005/3/22
  s:=' SELECT id=t1.Unit_iD,t2.UNIT_NAME,IN_FOOD ,price=isnull(price,0),Total=isnull(SUM(storage),0) '+
     ' FROM ENT_INSTACK t1,ENT_UNIT_INFO t2  WHERE     t1.UNIT_ID=t2.UNIT_ID '+
     ' AND ATTRIBUTE='''+u1_Attr+''' and IN_FOOD is not null'+
     ' GROUP BY t1.Unit_iD,t2.UNIT_NAME,IN_FOOD,price order by t1.Unit_ID ';


  with u1_Query1 do
  begin
    SQL.Clear;
    SQL.Add(s);
    Open;
    if isempty then exit;
    First;
    New(DataHead);
    temp:=DataHead;
    temp^.UnitID:=FieldByName('id').AsString;
    temp^.UnitName:=FieldByName('UNIT_NAME').AsString;
    temp^.FoodID:=FieldByName('IN_FOOD').Asstring;
    temp^.CostPerUnit:=FieldByName('price').AsString;      //修改2005/3/22
    temp^.Sum:=FieldByName('Total').AsString;
    temp^.Next:=nil;
    Next;
    while not EOF do
    begin
      New(temp^.Next);
      temp:=temp^.Next;
      temp^.UnitID:=FieldByName('id').AsString;
      temp^.UnitName:=FieldByName('UNIT_NAME').AsString;
      temp^.FoodID:=FieldByName('IN_FOOD').Asstring;
      temp^.CostPerUnit:=FieldByName('price').AsString;      //修改2005/3/22
      temp^.Sum:=FieldByName('Total').AsString;
      temp^.Next:=nil;
      Next;
    end;
  end;

  temp:=DataHead;
  TotalOilInRow:=0;
  TotalFoodInRow:=0;
  previousid:=temp^.UnitID;
  XLS_FillCell(h,v,temp^.UnitName,0);
  XLS_FillCell(GetCostPos(temp^.FoodID,temp^.CostPerUnit,temp^.Sum),v,temp^.Sum,1);
  //if temp^.FoodID > 100 then
  if false then
    TotalOilInRow:=TotalOilInRow+StrToFloat(temp^.Sum)
  else
    TotalFoodInRow:=TotalFoodInRow+StrToFloat(temp^.Sum);
  HeightOfReport:=1;
  temp:=temp^.Next;
  while temp<>nil do
  begin
    if trim(temp^.UnitID)=trim(previousid) then
    begin
      XLS_FillCell(GetCostPos(temp^.FoodID,temp^.CostPerUnit,temp^.Sum),v,temp^.Sum,1);
      //if temp^.FoodID > 100 then
      if false then
        TotalOilInRow:=TotalOilInRow+StrToFloat(temp^.Sum)
      else
        TotalFoodInRow:=TotalFoodInRow+StrToFloat(temp^.Sum);
      temp:=temp^.Next;
      if (temp=nil) or (temp^.UnitID<>previousid)  then
      begin
        XLS_FillCell(2,v,FloatToStr(TotalFoodInRow),1);
        //XLS_FillCell(SumOfFoodType+2,v,FloatToStr(TotalOilInRow),0);
      end;
      Continue;
    end;
    TotalFoodInRow:=0;
    TotalOilInRow:=0;
    previousid:=temp^.UnitID;
    inc(v);
    XLS_FillCell(h,v,temp^.UnitName,0);
    XLS_FillCell(GetCostPos(temp^.FoodID,temp^.CostPerUnit,temp^.Sum),v,temp^.Sum,1);
    //if temp^.FoodID > 100 then
    if false then
      TotalOilInRow:=TotalOilInRow+StrToFloat(temp^.Sum)
    else
      TotalFoodInRow:=TotalFoodInRow+StrToFloat(temp^.Sum);
    inc(HeightOfReport);
    temp:=temp^.Next;
    if temp=nil then  XLS_FillCell(2,v,FloatToStr(TotalFoodInRow),1);
  end;


  Typetemp:=TypeHead;
  TotalFoodInSheet:=0;
  TotalOilInSheet:=0;
  while Typetemp<>nil do
  begin
    while Typetemp^.Cost<>nil do
    begin
      if false then
      //if Typetemp^.FoodID > 100 then
        TotalOilInSheet:=TotalOilInSheet+Typetemp^.Cost^.TotalInCol
      else
        TotalFoodInSheet:=TotalFoodInSheet+Typetemp^.Cost^.TotalInCol;
      XLS_FillCell(Typetemp^.Cost^.Pos,7,FloatToStr(Typetemp^.Cost^.TotalInCol),1);
      Typetemp^.Cost:=Typetemp^.Cost^.Next;
    end;
    Typetemp:=Typetemp^.Next;
  end;
  XLS_FillCell(2,7,FloatToStr(TotalFoodInSheet),1);
  //XLS_FillCell(SumOfFoodType+2,7,FloatToStr(TotalOilInSheet),0);
end;

function GetCostPos(foodid:string;cost: string;sum:string):integer;
var temp:PFoodType;
    Costtemp:PCost;
begin
  temp:=TypeHead;
  while temp<>nil do
  begin
    if trim(temp^.FoodID)=trim(foodid) then
      Break;
    temp:=temp^.Next;
  end;
  Costtemp:=temp^.Cost;
  while Costtemp<>nil do
  begin
    if trim(Costtemp^.CostPerUnit)=trim(cost) then
    begin
      Costtemp^.TotalInCol:=Costtemp^.TotalInCol+StrToFloat(sum);
      Break;
    end;
    Costtemp:=Costtemp^.Next;
  end;
  Result:=Costtemp^.Pos;
end;
end.