datamod1.~pas

this is sample for traders

    qPayEdit: TZQuery;
    SalesByPiutangTOTALPAYED: TFloatField;
    qSalesTOTALPAYED: TFloatField;
    qSalesTOTALBAL: TFloatField;
    SalesByPiutangTOTALBAL: TFloatField;
    qEdLgnADDRESS: TMemoField;
    qEdLgnCITY: TStringField;
    qEdLgnPOSCODE: TStringField;
    qEdLgnCOUNTRY: TStringField;
    qLgnADDRESS: TMemoField;
    qLgnCITY: TStringField;
    qLgnPOSCODE: TStringField;
    qLgnCOUNTRY: TStringField;
    procedure qItemAfterOpen(DataSet: TDataSet);
    procedure DtaConAfterConnect(Sender: TObject);
    procedure DtaConRollback(Sender: TObject);
    procedure qLgnAfterOpen(DataSet: TDataSet);
    procedure qLgnPhoneNewRecord(DataSet: TDataSet);
    procedure qLgnCtcNewRecord(DataSet: TDataSet);
    procedure qPurcAfterOpen(DataSet: TDataSet);
    procedure qSPLAfterOpen(DataSet: TDataSet);
    procedure qPurcSPLCODEChange(Sender: TField);
    procedure qSPLNewRecord(DataSet: TDataSet);
    procedure qSplAddressNewRecord(DataSet: TDataSet);
    procedure qSplPhoneNewRecord(DataSet: TDataSet);
    procedure qSplPersonNewRecord(DataSet: TDataSet);
    procedure qSplAddressAfterDelete(DataSet: TDataSet);
    procedure qSplPersonAfterDelete(DataSet: TDataSet);
    procedure qSplPhoneAfterDelete(DataSet: TDataSet);
    procedure qPurcCalcFields(DataSet: TDataSet);
    procedure qPurcNewRecord(DataSet: TDataSet);
    procedure qPurcLineAfterDelete(DataSet: TDataSet);
    procedure qPurcLineITEMCODEChange(Sender: TField);
    procedure qPurcLineAfterPost(DataSet: TDataSet);
    procedure qPurcLineCalcFields(DataSet: TDataSet);
    procedure qPurcLineNewRecord(DataSet: TDataSet);
    procedure qSalesLGNCODEChange(Sender: TField);
    procedure qSalesNewRecord(DataSet: TDataSet);
    procedure qSalesTRNOChange(Sender: TField);
    procedure qSaleslineITEMCODEChange(Sender: TField);
    procedure qSaleslineNewRecord(DataSet: TDataSet);
    procedure ItemBySalesCalcFields(DataSet: TDataSet);
    procedure qSaleslineCalcFields(DataSet: TDataSet);
    procedure qSaleslineAfterDelete(DataSet: TDataSet);
    procedure qSaleslineAfterPost(DataSet: TDataSet);
    procedure qSalesCalcFields(DataSet: TDataSet);
    procedure qPayEditAfterApplyUpdates(Sender: TObject);
    procedure qEdLgnNewRecord(DataSet: TDataSet);
  private
  public
    function dbconnect: boolean;
  end;

var
  DM1: TDM1;

implementation

{$R *.dfm}

uses SelectGudang, funclib, Dialogs, Variants;

function TDM1.dbconnect: boolean;
var
  i : integer;
  FileKoneksi : TextFile;
  DataKoneksi : string[30];
begin
  AssignFile(FileKoneksi, 'sprofile');
  Reset(FileKoneksi);
    for i := 1 to 5 do
    begin
      Readln(FileKoneksi, DataKoneksi);
      case i of
        1 : DM1.dtaCon.HostName := DecryptText(DataKoneksi);
        2 : DM1.dtaCon.Port := StrToInt(DecryptText(DataKoneksi));
        3 : DM1.dtaCon.Database := DecryptText(DataKoneksi);
        4 : DM1.dtaCon.User := DecryptText(DataKoneksi);
        5 : DM1.dtaCon.Password := DecryptText(DataKoneksi);
      end;
    end;
    CloseFile(FileKoneksi);
    DM1.dtaCon.LoginPrompt:=False;
    try
      DM1.dtaCon.Connect;
    except
      raise Exception.Create('Database Server Error...');
    end;
    if DM1.dtaCon.Connected then result:=true else result:=false;
end;

procedure TDM1.qItemAfterOpen(DataSet: TDataSet);
begin
  with DM1.qItemPrice do
  begin
    Open;
  end;
end;

procedure TDM1.DtaConAfterConnect(Sender: TObject);
begin
  sysreg.Open;
  tbCurrency.Open;
  tbsat.Open;
  tbTitleName.Open;
  tbDepartment.Open;
  tbJabatan.Open;
  tbJtempo.Open;
end;

procedure TDM1.DtaConRollback(Sender: TObject);
begin
  ShowMessage('Transaksi SQL Error..., RollBack Data !'+#13#10+'Data Tidak di-proses...');
end;

procedure TDM1.qLgnAfterOpen(DataSet: TDataSet);
begin
  //qLgnAddr.Open;
  qLGNPhone.Open;
  qLGNCtc.Open;
end;

procedure TDM1.qLgnPhoneNewRecord(DataSet: TDataSet);
begin
  qLgnphoneLGNCODE.Value := qEdLGNLGNCODE.Value;
  qLgnPhoneLINENO.Value := qLGNPHONE.RecordCount+1;
end;

procedure TDM1.qLgnCtcNewRecord(DataSet: TDataSet);
begin
  qLgnCtcLGNCODE.Value := qEdLGNLGNCODE.Value;
  qLgnCtcLINENO.Value := qLGNCTC.RecordCount+1;
end;

procedure TDM1.qPurcAfterOpen(DataSet: TDataSet);
begin
  qSPL.Open;
  qPurcLine.Open;
end;

procedure TDM1.qSPLAfterOpen(DataSet: TDataSet);
begin
  qSplAddress.Open;
  qSplPhone.Open;
  qSplPerson.Open;
end;

procedure TDM1.qPurcSPLCODEChange(Sender: TField);
begin
    with DM1.SplbyPurc do
    begin
      Close;
      Open;
    end;
end;

procedure TDM1.qSPLNewRecord(DataSet: TDataSet);
begin
  qSplCREDLIMIT.Value := 0;
  qSplOPBAL.Value := 0;
  qSplCURBAL.Value := 0;
end;

procedure TDM1.qSplAddressNewRecord(DataSet: TDataSet);
begin
  qSplAddressSPLCODE.Value := qEdSPLSPLCODE.Value;
  qSPLADDRESSLINENO.Value := qSPLADDRESS.RecordCount+1;
end;

procedure TDM1.qSplPhoneNewRecord(DataSet: TDataSet);
begin
  qSplphoneSPLCODE.Value := qEdSPLSPLCODE.Value;
  qSPLPHONELINENO.Value := qSPLPHONE.RecordCount+1;
end;

procedure TDM1.qSplPersonNewRecord(DataSet: TDataSet);
begin
  qSplPersonSPLCODE.Value := qEdSPLSPLCODE.Value;
  qSPLPERSONLINENO.Value := qSPLPERSON.RecordCount+1;
end;

procedure TDM1.qSplAddressAfterDelete(DataSet: TDataSet);
begin
  SortLine(qSplAddress);
end;

procedure TDM1.qSplPersonAfterDelete(DataSet: TDataSet);
begin
  SortLine(qSplPerson);
end;

procedure TDM1.qSplPhoneAfterDelete(DataSet: TDataSet);
begin
  SortLine(qSplPhone);
end;

procedure TDM1.qPurcCalcFields(DataSet: TDataSet);
begin
  qPURCGRANDTOTAL.Value :=
  (qPURCTRTOTAL.Value - qPURCTRDISC.Value) + qPURCTRPPN.Value + qPURCBIAYA.Value;
end;

procedure TDM1.qPurcNewRecord(DataSet: TDataSet);
begin
  qPurc.FieldByName('TRDISC').Value:=0;
  qPurc.FieldByName('TRPPN').value:=0;
  qPurc.FieldByName('TRTOTAL').value:=0;
  qPurc.FieldByName('TRDATE').value:=date;
  qPurc.FieldByName('USERNAME').Value:=AppUserName;
  qPurc.FieldByName('PCNODE').value:= mnode;
  qPurc.FieldByName('PCNAME').Value:= mpcname;
  qPurc.FieldByName('PERIOD').Value:=mper;
  qPurc.FieldByName('POSTED').Value:=0;
  qPurc.FieldByName('PPN').Value:='0';
  qPurc.FieldByName('STATUS').Value:='D';
  qPurc.FieldByName('BIAYA').Value:=0;
  qPurc.FieldByName('CURR').Value:='RP';
  qPurc.FieldByName('EXCHANGE_R').Value:=1;
  qPurc.FieldByName('TERMDAYS').Value:=0;
  qPurc.FieldByName('TAXREPNO').Value:=0;
  qPurc.FieldByName('FTAXDATE').value:=date;
end;

procedure TDM1.qPurcLineAfterDelete(DataSet: TDataSet);
begin
  SortLine(qPurcLine);
  SumTotal(DM1.qPurcline,'CSUBTOTAL',DM1.qPurc,'TRTOTAL',False);
end;

procedure TDM1.qPurcLineITEMCODEChange(Sender: TField);
begin
  ItemByPurc.Close;
  ItemByPurc.Open;
end;

procedure TDM1.qPurcLineAfterPost(DataSet: TDataSet);
begin
  SumTotal(DM1.qPurcline,'CSUBTOTAL',DM1.qPurc,'TRTOTAL',False);
end;

procedure TDM1.qPurcLineCalcFields(DataSet: TDataSet);
var
  Hasil: Double;
begin
  Hasil := qPurclineQTY.Value *
  (qPurclinePRICE.Value - (qPurclineDISC_1.Value / 100 * qPurclinePRICE.Value));
  Hasil:=Hasil-(Hasil*(qPurcLineDISC_2.Value/100));
  qPurcLineCSUBTOTAL.Value:=Hasil;
end;

procedure TDM1.qPurcLineNewRecord(DataSet: TDataSet);
begin
  qPURCLINELINENO.Value := qPURCLINE.RecordCount+1;
  qPURCLINETRNO.Value := qPURCTRNO.Value;
  qPURCLINEQTY.Value := 0;
  qPURCLINEDISC_1.value := 0;
  qPURCLINEDISC_2.value := 0;
  qPURCLINEPOSTED.Value := 0;
  qPURCLINEPRICE.Value := 0;
  qPURCLINE.FieldByName('BONUS').Value := 0;
end;

procedure TDM1.qSalesLGNCODEChange(Sender: TField);
begin
  LgnBySales.Close; LgnBySales.Open;
  //qSalesline.Close; qSalesline.Open;
end;

procedure TDM1.qSalesNewRecord(DataSet: TDataSet);
begin
  qSales.FieldByName('TRDISC').Value:=0;
  qSales.FieldByName('TRTOTAL').value:=0;
  qSales.FieldByName('TRDP').value:=0;
  qSales.FieldByName('ANGSURAN').value:=0;
  qSales.FieldByName('J_ANGSURAN').value:=0;
  qSales.FieldByName('TOTALPAYED').value:=0;
  qSales.FieldByName('TOTALBAL').value:=0;
  qSales.FieldByName('TRDATE').value:=date;
  qSales.FieldByName('USERNAME').Value:=AppUserName;
  qSales.FieldByName('PCNODE').value:= mnode;
  qSales.FieldByName('PCNAME').Value:= mpcname;
  qSales.FieldByName('PERIOD').Value:=mper;
  qSales.FieldByName('POSTED').Value:=0;
  qSales.FieldByName('STATUS').Value:='D';
end;

procedure TDM1.qSalesTRNOChange(Sender: TField);
begin
  if qSales.State in [dsInsert, dsEdit] then
  begin
    qPegawai.Close; qPegawai.Open;
    qSalesline.Close; qSalesline.Open;
  end;
end;

procedure TDM1.qSaleslineITEMCODEChange(Sender: TField);
var
  sqltext: String;
begin
    with DM1.qItemQTY do
    begin
      Close;
      SQL.Clear;
      sqltext:=
      ('SELECT * ')+
      ('FROM item_qty WHERE gdgcode=:gdgcode ORDER BY ITEMCODE ASC ');
      SQL.Text:=sqltext;
      Params.ParamByName('gdgcode').Value:=SELECTGUDANGFORM.CODE;
      Open;
    end;
    ItemBySales.Close; ItemBySales.Open;
end;

procedure TDM1.qSaleslineNewRecord(DataSet: TDataSet);
begin
  qSalesline.FieldByName('TRNO').Value:=qSales.FieldValues['TRNO'];
  qSalesline.FieldByName('LINENO').Value:=qSalesline.RecordCount+1;
  qSalesline.FieldByName('QTY').Value := 1;
  qSalesline.FieldByName('DISC_1').value := 0;
  qSalesline.FieldByName('POSTED').Value := 0;
end;

procedure TDM1.ItemBySalesCalcFields(DataSet: TDataSet);
begin
  ItemBySalesHITSALDO.Value:=ItemBySalesONHAND.Value-qSalesLineQTY.Value;
end;

procedure TDM1.qSaleslineCalcFields(DataSet: TDataSet);
var
  Hasil: Double;
begin
  Hasil := qsaleslineQTY.Value *
  (qsaleslinePRICE.Value - (qsaleslineDISC_1.Value / 100 * qsaleslinePRICE.Value));
  qSalesLineCSUBTOTAL.Value:=Hasil;
end;

procedure TDM1.qSaleslineAfterDelete(DataSet: TDataSet);
begin
  SortLine(qSalesLine);
  SumTotal(qsalesline,'CSUBTOTAL',qsales,'TRTOTAL',False);
end;

procedure TDM1.qSaleslineAfterPost(DataSet: TDataSet);
begin
  SumTotal(qsalesline,'CSUBTOTAL',qsales,'TRTOTAL',False);
end;

procedure TDM1.qSalesCalcFields(DataSet: TDataSet);
begin
  qSales.FieldByName('GRANDTOTAL').Value:=qsales.FieldValues['TRTOTAL']-
  qsales.FieldValues['TRDISC'] ;
  DM1.qSalesCSISA.Value:=DM1.qSalesGRANDTOTAL.Value-DM1.qSalesTRDP.Value;
  if DM1.qSalesANGSURAN.Value>0 then
  begin
    DM1.qSalesCJANG.Value:=DM1.qSalesCSISA.Value/DM1.qSalesANGSURAN.Value;
  end else
  begin
    DM1.qSalesCJANG.Value:=DM1.qSalesCSISA.Value;
  end;
end;

procedure TDM1.qPayEditAfterApplyUpdates(Sender: TObject);
begin
  with SQLp do
  begin
    Script.Clear;
    Script.Add('UPDATE SALES SET TOTALPAYED=(SELECT SUM(AMOUNT) FROM SALESPAYCARD WHERE PAYED=1 AND SPNO=:nSP), ');
    Script.Add('TOTALBAL=TRSISA-TOTALPAYED ;');
    ParamByName('nSP').Value:=DM1.SalesByPiutangTRNO.Value;
    Execute;
  end;
end;

procedure TDM1.qEdLgnNewRecord(DataSet: TDataSet);
begin
  qEdLGNCREDLIMIT.Value := 0;
  qEdLGNOPBAL.Value := 0;
  qEdLGNCURBAL.Value := 0;
end;

end.