datamodule.pas

电子充值系统:全球通的充值卡通过POS机传到销售点.

      if i=1 then sTableName:='AccountPayableBeginning';
      if i=2 then sTableName:='AccountPayableDetail';
      if i=3 then sTableName:='AccountPayableMaster';
      if i=4 then sTableName:='AccountReceivableBeginning';
      if i=5 then sTableName:='AccountReceivableDetail';
      if i=6 then sTableName:='AccountReceivableMaster';
      if i=7 then sTableName:='Bank';
      if i=8 then sTableName:='ChangeDetail';
      if i=9 then sTableName:='ChangeMaster';
      if i=10 then sTableName:='CustomerAddress';
      if i=11 then sTableName:='CustomerContact';
      if i=12 then sTableName:='CustomerInventory';
      if i=13 then sTableName:='CustomerMaster';
      if i=14 then sTableName:='CustomerType';
      if i=15 then sTableName:='DeliveryDetail';
      if i=16 then sTableName:='DeliveryMaster';
      if i=17 then sTableName:='Department';
      if i=18 then sTableName:='Employee';
      if i=19 then sTableName:='InventoryAnalyst';
      if i=20 then sTableName:='InventoryBeginning';
      if i=21 then sTableName:='InventoryChange';
      if i=22 then sTableName:='MorvPickDetail';
      if i=23 then sTableName:='MorvPickMaster';
      if i=24 then sTableName:='Product';
      if i=25 then sTableName:='ProductType';
      if i=26 then sTableName:='Programs';
      if i=27 then sTableName:='PurchaseAnalyst';
      if i=28 then sTableName:='PurchaseDetail';
      if i=29 then sTableName:='PurchaseMaster';
      if i=30 then sTableName:='Region';
      if i=31 then sTableName:='SalesAnalyst';
      if i=32 then sTableName:='SalesMan';
      if i=33 then sTableName:='Supplier';
      if i=34 then sTableName:='SupplierInventory';
      if i=35 then sTableName:='SupplierType';
      if i=36 then sTableName:='TransferDetail';
      if i=37 then sTableName:='TransferMaster';
      if i=38 then sTableName:='Users';
      if i=39 then sTableName:='Warehouse';
      if i=40 then sTableName:='UserAuthority';
      if i=41 then sTableName:='Company';
      if i=42 then sTableName:='AssembleDetail';
      if i=43 then sTableName:='Assemblemaster';
      if i=44 then sTableName:='ProductUnit';
      if i=45 then sTableName:='Unit';
      if i=46 then sTableName:='CurrencyType';


      with DM.qyExecute do
      begin
        Close;
        SQL.Clear;
        SQL.Add('delete from  '+ sTableName);
        ExecSQL;
      end;
    end;

    for i:=1 to 5 do
    begin
      if i=1 then
        ls_sql :='insert into company (CompanyID,AttribName,CompanyName,'+
           'InvoiceNo,OpenningYear,OpenningMonth,PeriodYear,PeriodMonth,'+
           'CreateMan,CreateDate,CreateTime,ModifyMan,ModifyDate,ModifyTime) '+
           'values (''SG'',''控制公司'',''控制公司'',''00000001'','+
           '''03'',''04'',''03'',''04'','+
           '''SUPERVISOR'',''03-04-25'',''18:03:53'','+
           '''SUPERVISOR'',''03-04-28'',''15:23:06'')';
      if i=2 then
          ls_sql :='insert into company (CompanyID,AttribName,CompanyName,'+
           'InvoiceNo,OpenningYear,OpenningMonth,PeriodYear,PeriodMonth,'+
           'CreateMan,CreateDate,CreateTime,ModifyMan,ModifyDate,ModifyTime) '+
           'values (''SD'',''欣达电脑'',''欣达电脑公司'',''00000002'','+
           '''03'',''04'',''03'',''04'','+
           '''SUPERVISOR'',''03-04-25'',''18:03:53'','+
           '''SUPERVISOR'',''03-04-28'',''15:23:06'')';
      if i=3 then
         ls_sql :=' insert into users(CompanyID,UserID,UserName,'+
         'UserOrGroup,GroupID,PasswordCode,CreateMan,CreateDate,'+
         'CreateTime,ModifyMan,ModifyDate,ModifyTime) values ('+
         '''SD'',''001'',''俞晓龙'',''U'',''001'',''001'','+
         '''SUPERVISOR'',''03-04-25'',''18:04:54'','+
         '''SUPERVISOR'',''03-04-25'',''18:05:52'')';
    
      if i=4 then
         ls_sql :='insert into programs (CompanyID,ProgramID,ProgramName,'+
         'ReportOrModify,CreateMan,CreateDate,CreateTime,'+
         'ModifyMan,ModifyDate,ModifyTime) values ('+
         '''SD'',''*'',''全部'',''2'',''SUPERVISOR'',''03-04-25'','+
         '''18:05:12'',''SUPERVISOR'',''03-04-25'',''18:05:12'')';
      if i=5 then
         ls_sql :='insert into UserAuthority (CompanyID,UserID,ProgramID,'+
           'Run,Append,Edit,Del,Report,BrowseAll,EditAll) values ('+
           '''SD'',''001'',''*'',''Y'',''Y'',''Y'',''Y'',''Y'',''Y'',''Y'')';

      with DM.qyExecute do
      begin
        Close;
        SQL.Clear;
        SQL.Add(ls_sql);
        ExecSQL;
      end;
    end;

    MyInformation('初始化数据库完成！');
  except
    MyError('初始化数据库失敗！');
    raise;
  end;
  }
end;



procedure TDM.DataSetOpen(DataSet: TDataSet);
begin
  //
end;

procedure TDM.DumpLog;
begin
  try
    with DM.qyExecute do
    begin
      Close;
      SQL.Clear;
      SQL.Add('DUMP TRAN PSM WITH NO_LOG ');
      ExecSQL;
    end;
    MyInformation('移除日誌檔完成！');
  except
    MyError('移除日誌檔失敗！');
    raise;
  end;
end;

procedure TDM.tbInputCompanyNoValidate(Sender: TField);
begin
  with qyGet do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT AttribName ');
    SQL.Add('FROM Company ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    ParamByName('CompanyID').AsString := tbInput.FieldByName('CompanyID').AsString;
    Open;
  end;
  tbInput.FieldByName('AttribName').AsString := qyGet.FieldByName('AttribName').AsString;
end; 

procedure UpdateCardStock(sCardType,sSaleCode:string;fCardParValue,fPayCode,fQuantity: Extended);
begin
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select cardno,cardtype from ( ');
    SQL.Add('select cardno,cardtype from pwpt_cardstock ');
    SQL.Add('where cardtype = :cardtype and ');
    SQL.Add('cardparvalue = :cardparvalue and bout =''0'' ');
    SQL.Add('order by enddate) where rownum <= :Quantity ');
    ParamByName('cardtype').AsString := scardtype;
    ParamByName('cardparvalue').AsFloat := fcardparvalue;
    ParamByName('Quantity').AsFloat := fQuantity;
    Open;
  end;

  if DM.qyTemp1.RecordCount <> fQuantity then
  begin
    MyWarning('卡库存不足！面值'+floattostr( fCardParValue) +'元的'+ sCardType +'只有'+floattostr(DM.qyTemp1.RecordCount)+'张');
    abort;
  end;

  while not DM.qyTemp1.Eof do
  Begin
    with DM.qyExecute do
    begin
      Close;
      SQL.Clear;
      SQL.Add('update pwpt_cardstock set bout =''1'', ');
      SQL.Add(' paycode = :paycode,SaleCode = :SaleCode, ');
      SQL.Add(' outMan = :outMan , outDate = sysdate ');
      SQL.Add('where cardno = :cardno and cardtype = :cardtype');
      ParamByName('paycode').AsFloat := fpaycode;
      ParamByName('SaleCode').AsString := sSaleCode;
      ParamByName('outMan').AsString := sAdmCode;
      ParamByName('cardno').AsString := DM.qyTemp1.fieldbyname('cardno').asstring;
      ParamByName('cardtype').AsString := DM.qyTemp1.fieldbyname('cardtype').asstring;
      ExecSQL;
    end;
    DM.qyTemp1.next; //
  end; 
end;

procedure UpdateInventoryAnalyst(ChangeDate, ProductID, WarehouseID, ChangeCode: String;
                                 Quantity, Amount: Extended);
begin
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ProductID ');
    SQL.Add('FROM InventoryAnalyst ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND InventoryYear = :InventoryYear AND InventoryMonth = :InventoryMonth ');
    SQL.Add('AND ProductID = :ProductID AND WarehouseID = :WarehouseID ');
    SQL.Add('AND ChangeCode = :ChangeCode ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('InventoryYear').AsString := LeftStr(ChangeDate, 2);
    ParamByName('InventoryMonth').AsString := SubStr(ChangeDate, 4, 2);
    ParamByName('ProductID').AsString := ProductID;
    ParamByName('WarehouseID').AsString := WarehouseID;
    ParamByName('ChangeCode').AsString := ChangeCode;
    Open;
  end;
  
  if DM.qyTemp1.FieldByName('ProductID').AsString = '' then
  begin
    with DM.qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO InventoryAnalyst ');
      SQL.Add(' (CompanyID, InventoryYear, InventoryMonth, ');
      SQL.Add('  ProductID, WarehouseID, ChangeCode, ');
      SQL.Add('  TotalQuantity, TotalAmount) ');
      SQL.Add('VALUES ');
      SQL.Add(' (:CompanyID, :InventoryYear, :InventoryMonth, ');
      SQL.Add('  :ProductID, :WarehouseID, :ChangeCode, ');
      SQL.Add('  :TotalQuantity, :TotalAmount) ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(ChangeDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(ChangeDate, 4, 2);
      ParamByName('ProductID').AsString := ProductID;
      ParamByName('WarehouseID').AsString := WarehouseID;
      ParamByName('ChangeCode').AsString := ChangeCode;
      ParamByName('TotalQuantity').AsFloat := Quantity;
      ParamByName('TotalAmount').AsFloat := Amount;
      ExecSQL;
    end;
  end
  else
  begin
    with DM.qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE InventoryAnalyst SET ');
      SQL.Add('TotalQuantity = TotalQuantity + :TotalQuantity, ');
      SQL.Add('TotalAmount = TotalAmount + :TotalAmount ');
      SQL.Add('WHERE CompanyID = :CompanyID ');
      SQL.Add('AND InventoryYear = :InventoryYear AND InventoryMonth = :InventoryMonth ');
      SQL.Add('AND ProductID = :ProductID AND WarehouseID = :WarehouseID ');
      SQL.Add('AND ChangeCode = :ChangeCode ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('InventoryYear').AsString := LeftStr(ChangeDate, 2);
      ParamByName('InventoryMonth').AsString := SubStr(ChangeDate, 4, 2);
      ParamByName('ProductID').AsString := ProductID;
      ParamByName('WarehouseID').AsString := WarehouseID;
      ParamByName('ChangeCode').AsString := ChangeCode;
      ParamByName('TotalQuantity').AsFloat := Quantity;
      ParamByName('TotalAmount').AsFloat := Amount;
      ExecSQL;
    end;
  end;
end;

procedure TDM.tbInputSupplierIDValidate(Sender: TField);
begin
  tbInput.FieldByName('SupplierAttribName').AsString :=
          GetSalesName(tbInput.FieldByName('SupplierID').AsString);
end;

procedure TDM.tbInputCustomerIDValidate(Sender: TField);
begin
  tbInput.FieldByName('CustomerAttribName').AsString :=
          GetSalesName(tbInput.FieldByName('CustomerID').AsString);
end;

procedure TDM.tbInputBeginSalesIDValidate(Sender: TField);
begin
  tbInput.FieldByName('BeginSalesName').AsString :=
          GetSalesName(tbInput.FieldByName('BeginSalesManID').AsString);
end;

procedure TDM.tbInputEndSalesIDValidate(Sender: TField);
begin
  tbInput.FieldByName('EndSalesName').AsString :=
          GetSalesName(tbInput.FieldByName('EndSalesManID').AsString);
end;

procedure TDM.tbInputBeginRegionIDValidate(Sender: TField);
begin
  tbInput.FieldByName('BeginRegionName').AsString :=
          GetRegionName(tbInput.FieldByName('BeginRegionID').AsString);
end;

procedure TDM.tbInputEndRegionIDValidate(Sender: TField);
begin
  tbInput.FieldByName('EndRegionName').AsString :=
          GetRegionName(tbInput.FieldByName('EndRegionID').AsString);
end;

procedure TDM.tbInputBeginCustomerTypeIDValidate(Sender: TField);
begin
  tbInput.FieldByName('BeginCustomerTypeName').AsString :=
          GetCustomerTypeName(tbInput.FieldByName('BeginCustomerTypeID').AsString);
end;

procedure TDM.tbInputEndCustomerTypeIDValidate(Sender: TField);
begin
  tbInput.FieldByName('EndCustomerTypeName').AsString :=
          GetCustomerTypeName(tbInput.FieldByName('EndCustomerTypeID').AsString);
end;

procedure TDM.tbInputBeginSupplierTypeIDValidate(Sender: TField);