basicbill.pas

仲裁委仲裁案件计酬程序.有基础资料设置、分级设置

unit BasicBill;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs,MDIChild, ComCtrls, ImgList, ToolWin, fcStatusBar, ActnList,
  ExtCtrls, StdCtrls, Grids, DBGridEh, DB, ADODB, DBCtrlsEh, DBLookupEh,
  Mask,Pub, DBCtrls;

type
  TBasicBillFrm = class(TMDIChild)
    CoolBar1: TCoolBar;
    ToolBar1: TToolBar;
    ImgList: TImageList;
    MainToolBar: TToolBar;
    BLoad: TToolButton;
    BCheck: TToolButton;
    BSaveBill: TToolButton;
    ToolButton12: TToolButton;
    BQuit: TToolButton;
    ActList: TActionList;
    ActInsertBill: TAction;
    ActDataCheck: TAction;
    ActBuild: TAction;
    ActQuit: TAction;
    fcStatusBar1: TfcStatusBar;
    Movie: TAnimate;
    Panel_Left: TPanel;
    GroupBox3: TGroupBox;
    SheetList: TListBox;
    Splitter1: TSplitter;
    ROBType: TRadioGroup;
    GroupBox5: TGroupBox;
    ErrGrid: TDBGridEh;
    Splitter2: TSplitter;
    Panel_Right: TPanel;
    CoolBar2: TCoolBar;
    ToolBar2: TToolBar;
    ToolBar3: TToolBar;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    BillForm: TPanel;
    Label1: TLabel;
    Label17: TLabel;
    Label8: TLabel;
    Label2: TLabel;
    EBillDate: TDBDateTimeEditEh;
    EBiller: TDBLookupComboboxEh;
    EBillNO: TDBEditEh;
    ExcelCon: TADOConnection;
    ExcelTab: TADOTable;
    OpenExcel: TOpenDialog;
    SourceTab: TADOTable;
    _ExcelTab: TDataSource;
    InitCMD: TADOCommand;
    ItemGrid: TDBGridEh;
    ActSave: TAction;
    ErrQry: TADOQuery;
    _ErrQry: TDataSource;
    Shape1: TShape;
    Shape2: TShape;
    Shape3: TShape;
    Panel_Bill: TPanel;
    ICBillGrid: TDBGridEh;
    BillHead: TADOQuery;
    _BillHead: TDataSource;
    BillEntry: TADOQuery;
    BillEntryFBrNo: TStringField;
    BillEntryFInterID: TIntegerField;
    BillEntryFEntryID: TIntegerField;
    BillEntryFItemNumber: TStringField;
    BillEntryFItemName: TStringField;
    BillEntryFModel: TStringField;
    BillEntryFItemID: TIntegerField;
    BillEntryFUnitName: TStringField;
    BillEntryFQty: TBCDField;
    BillEntryFPrice: TBCDField;
    BillEntryFAmount: TBCDField;
    BillEntryFNote: TStringField;
    BillEntryFUnitID: TIntegerField;
    BillEntryFAuxPrice: TBCDField;
    BillEntryFAuxQty: TBCDField;
    BillEntryFStockQty: TBCDField;
    BillEntryFAllAmount: TBCDField;
    BillEntryFDate: TDateTimeField;
    BillEntryFCommitQty: TBCDField;
    BillEntryFTaxRate: TBCDField;
    BillEntryFTax: TBCDField;
    BillEntryFTaxAmount: TBCDField;
    BillEntryFAuxCommitQty: TBCDField;
    BillEntryFSourceEntryID: TSmallintField;
    BillEntryFCess: TBCDField;
    BillEntryFAuxStockQty: TBCDField;
    BillEntryFMapNumber: TStringField;
    BillEntryFMapName: TStringField;
    BillEntryFAuxPropID: TIntegerField;
    BillEntryFAuxPriceDiscount: TBCDField;
    BillEntryFPriceDiscount: TBCDField;
    BillEntryFQtyInvoice: TBCDField;
    BillEntryFQtyInvoiceBase: TBCDField;
    BillEntryFAuxTaxPrice: TBCDField;
    BillEntryFTaxPrice: TBCDField;
    BillEntryFReceiveAmountFor_Commit: TBCDField;
    BillEntryFReceiveAmount_Commit: TBCDField;
    BillEntryFSecCoefficient: TBCDField;
    BillEntryFSecQty: TBCDField;
    BillEntryFSecCommitQty: TBCDField;
    BillEntryFSourceTranType: TIntegerField;
    BillEntryFSourceInterId: TIntegerField;
    BillEntryFSourceBillNo: TWideStringField;
    BillEntryFContractInterID: TIntegerField;
    BillEntryFContractEntryID: TIntegerField;
    BillEntryFContractBillNo: TWideStringField;
    BillEntryFAuxQtyInvoice: TBCDField;
    BillEntryFMRPLockFlag: TIntegerField;
    BillEntryFMrpClosed: TIntegerField;
    _BillEntry: TDataSource;
    People: TADOQuery;
    PeopleFUserID: TSmallintField;
    PeopleFName: TStringField;
    _People: TDataSource;
    ENote: TDBMemo;
    Label27: TLabel;
    Shape4: TShape;
    ActCheckBill: TAction;
    procedure ActInsertBillExecute(Sender: TObject);
    procedure ActQuitExecute(Sender: TObject);
    procedure ActDataCheckExecute(Sender: TObject);
  private
  Function ItemCheck:Boolean;
  Procedure CreateTempTable;
  Procedure OptimizeGridEH(GridEh:TDBGridEH);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  BasicBillFrm: TBasicBillFrm;

implementation

{$R *.dfm}
Uses Wait;
Procedure TBasicBillFrm.OptimizeGridEH(GridEh:TDBGridEH);
Begin
  ShowWaitText('正在进行数据查询和显示优化,请稍候...');
  try
    OptimizeGrid(TDBGridEh(GridEh));
  finally
    ShowWaitText;//不带入参数,则是关闭等待窗口
  end;
End;
Function TBasicBillFrm.ItemCheck:Boolean;
Var j:Integer;
begin
Try
ErrQry.Close;
ErrQry.Prepared ;
ErrQry.Open;
For j:=0 to ErrGrid.Columns.Count-1 Do
ErrGrid.Columns[j].Width :=80;

If ErrQry.RecordCount>0 Then
Result:=False
Else
Result:=True;

Except On E: Exception Do
Begin
Application.MessageBox(PChar('发生错误，错误描述如下：'#13#10 + E.Message), '提示',MB_ICONSTOP);
Result:=False
End;
End;
End;
Procedure  TBasicBillFrm.CreateTempTable;
Var j:Integer;
    SQLStr,AddField:String;
Begin
Try
  With InitCMD do
  begin
    CommandText:='';
    CommandText:='Drop TABLE #Transfer_Source ';
    Prepared;
    Execute;
  end;
Except
end;
  With InitCMD do
  begin
    CommandText:='';
    CommandText:='CREATE TABLE #Transfer_Source (FID int Identity(1,1))';
    Prepared;
    Execute;
  end;


  For j:=0 to ExcelTab.FieldCount-1 do
  Begin
  AddField:=ExcelTab.Fields[j].FieldName ;

    With InitCMD do    
    begin
    CommandText:='';
    CommandText:='ALTER TABLE #Transfer_Source ADD ['+AddField+'] VarChar(255) Null';
    Execute;
    end;
  End;
ExcelTab.First;
While Not ExcelTab.Eof Do
Begin
  SQLStr:='';
  If ExcelTab.FieldCount  >1 Then
  Begin
    For j:=0 To ExcelTab.FieldCount-2 Do
    SQLStr:=SQLStr+''''+ExcelTab.Fields.Fields[j].AsString+''''+',' ;
  End ;
  SQLStr:='('+SQLStr+''''+ExcelTab.Fields.Fields[ExcelTab.FieldCount-1].AsString+''''+')'  ;
  With InitCMD do
  begin
    CommandText:='';
    CommandText:='Insert Into #Transfer_Source Values'+SQLStr;
    Prepared;
    Execute;
  end;
ExcelTab.Next;
end;
SourceTab.Active:=False;
SourceTab.TableName:='#Transfer_Source';
SourceTab.Active:=true;
OptimizeGridEH(TDBGridEh(ItemGrid));
End;
procedure TBasicBillFrm.ActInsertBillExecute(Sender: TObject);
var
  ConnStr:string;
begin
With OpenExcel Do
Begin
  Title := '请选择输入文件名';
  DefaultExt := 'xls';
  Filter := 'Excel文件(*.xls)|*.xls';
  Options := [ofOverwritePrompt, ofHideReadOnly, ofPathMustExist, ofNoReadOnlyReturn, ofEnableSizing];
end;
if OpenExcel.Execute then
begin
//读取
  ExcelCon.Close;
  ConnStr:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';
  ConnStr:=ConnStr+OpenExcel.FileName;
  ConnStr:=ConnStr+';Mode=ReadWrite;Extended Properties=Excel 8.0;Persist Security Info=False';
  ExcelCon.ConnectionString:=ConnStr;
  ExcelCon.Connected:=True;
  ExcelCon.GetTableNames(SheetList.Items,False);
  SheetList.Selected[0];
  ExcelTab.TableName:=SheetList.Items[0];
  ExcelTab.Active:=True;
  CreateTempTable;

end;
end;

procedure TBasicBillFrm.ActQuitExecute(Sender: TObject);
begin
Close
end;

procedure TBasicBillFrm.ActDataCheckExecute(Sender: TObject);
begin
If ExcelTab.Active=False Then
Begin
  Application.MessageBox('未打开Excel数据文件！','错误提示',MB_ICONINFORMATION);
  Exit;
End;
If (Not ItemCheck) Then
Begin
OptimizeGridEH(TDBGridEh(ErrGrid));

End;
end;

end.