Import data transaksi penjualan ke dalam database

Atau download file ini untuk sebagai latihan
Sekarang siapkan tabel pada database Anda:

CREATE TABLE `t_transaksi` (
  `id_transaksi` int(11) NOT NULL AUTO_INCREMENT,
  `tanggal` datetime DEFAULT NULL,
  `nama_pelanggan` varchar(200) DEFAULT NULL,
  `alamat` varchar(200) DEFAULT NULL,
  `kode_sales` varchar(100) DEFAULT NULL,
  `grandtotal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id_transaksi`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `t_transaksi_detail` (
  `id_transaksi_detail` int(11) NOT NULL AUTO_INCREMENT,  
  `id_transaksi` int(11) NOT NULL,
  `no_urut` datetime DEFAULT NULL,
  `nama_barang` varchar(200) DEFAULT NULL,
  `jumlah` varchar(200) DEFAULT NULL,
  `harga_satuan` varchar(100) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id_transaksi_detail`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Sekarang siapkan formnya seeprti dibawah ini :
Daftar nama komponen yang digunakan sebagai berikut : Edit1, button, CXSpreedsheet, AdvProgressBar1
Program bisa di minta melalui kontak

unit Unit1;

interface

uses
  Windows, Messages, Variants, Classes, Graphics, Controls, Forms,
  Dialogs;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Edit1: TEdit;
    Button1: TButton;
    Panel2: TPanel;
    cxSpreadSheetBook1: TcxSpreadSheetBook;
    OpenDialog1: TOpenDialog;
    Panel3: TPanel;
    Button2: TButton;
    Button3: TButton;
    MyConnection1: TMyConnection;
    MQTransaksi: TMyQuery;
    MQTransaksiDetail: TMyQuery;
    MQCari: TMyQuery;
    AdvProgress1: TAdvProgress;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    Function CariQuery(Query : String; Dataset : TMyQuery) : Boolean;
    function StartLoadingProgres(NamaProgress : TAdvProgress): Boolean;
    function PosisiloadingProgres(Countbaris: Integer; NamaProgress : TAdvProgress): Boolean;
  end;

var
  Form1: TForm1;
  Value_bar_posisi : Double;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
     if OpenDialog1.Execute Then
     Begin
          cxSpreadSheetBook1.LoadFromFile(OpenDialog1.FileName);
     End;
end;

Function TForm1.CariQuery(Query : String; Dataset : TMyQuery) : Boolean;
begin
    Result := False;
    Dataset.Close;
    Dataset.SQL.Clear;
    Dataset.SQL.Add(Query);
    Dataset.Open;
    if Not Dataset.Eof Then
    Begin
       Result:=true;
    end else
    begin
       Result:=False;
    end;
end;

function TForm1.StartLoadingProgres(NamaProgress : TAdvProgress): Boolean;
Begin
     Result := False;
     NamaProgress.Position := 0;
     NamaProgress.Show;
     Value_bar_posisi := 0;
     Application.ProcessMessages;
     Result := True;
End;

function TForm1.PosisiloadingProgres(Countbaris: Integer; NamaProgress : TAdvProgress): Boolean;
Begin
     Result := False;
     Value_bar_posisi := Value_bar_posisi + (100 / Countbaris);
     NamaProgress.Position := round(Value_bar_posisi);

     if Value_bar_posisi >= 99 Then
        NamaProgress.Position := 100;

     Application.ProcessMessages;
     Result := True;
End;

procedure TForm1.Button2Click(Sender: TObject);
Var i : integer;
    id_transaksi : integer;
    Gatot : Double;
begin
     i := 0;  StartLoadingProgres(AdvProgress1);
     for i := 1 to cxSpreadSheetBook1.ActiveSheet.RowCount - 1 do
     Begin
          Try
               if CariQuery('select * from t_transaksi where kode_transaksi='''+ cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).Text +''' ', MQTransaksi) Then
               Begin

               End else
               Begin
                    MQTransaksi.SQL.Clear;
                    MQTransaksi.SQL.Add('insert into t_transaksi(tanggal, nama_pelanggan, alamat, kode_sales, kode_transaksi) '+
                                        'values(:tgl, :nama_plg, :alamat_plg, :kode_ss, :kodetrs) ');

                    MQTransaksi.Params.ParamByName('tgl').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(1,i).Text;
                    MQTransaksi.Params.ParamByName('nama_plg').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(2,i).Text;
                    MQTransaksi.Params.ParamByName('alamat_plg').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(3,i).Text;
                    MQTransaksi.Params.ParamByName('kode_ss').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(4,i).Text;
                    MQTransaksi.Params.ParamByName('kodetrs').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).Text;
                    MQTransaksi.Execute;
               End;
          Finally

          end;

          Try
               MQTransaksiDetail.SQL.Clear;
               MQTransaksiDetail.SQL.Add('insert into t_transaksi_detail(kode_transaksi, no_urut, nama_barang, jumlah, harga_satuan, total) '+
                                         'values(:kodetrs, :nourut, :namabrg, :jml, :hrg_satuan, :ttl) ');

               MQTransaksiDetail.Params.ParamByName('kodetrs').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).Text;
               MQTransaksiDetail.Params.ParamByName('nourut').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(6,i).Text;
               MQTransaksiDetail.Params.ParamByName('namabrg').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(7,i).Text;
               MQTransaksiDetail.Params.ParamByName('jml').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(8,i).Text;
               MQTransaksiDetail.Params.ParamByName('hrg_satuan').Value := cxSpreadSheetBook1.ActiveSheet.GetCellObject(9,i).Text;
               MQTransaksiDetail.Params.ParamByName('ttl').Value := MQTransaksiDetail.Params.ParamByName('jml').Value *
                                                                    MQTransaksiDetail.Params.ParamByName('hrg_satuan').Value;
               MQTransaksiDetail.Execute;

          Finally

          end;

          Try
               Gatot :=0;
               if CariQuery('select sum(total) as tot from t_transaksi_detail where '+
                            'kode_transaksi='''+ cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).Text +''' ', MQCari) Then
               Begin
                    Gatot := MQCari.fieldbyname('tot').AsFloat;
               End;

               MQTransaksiDetail.SQL.Clear;
               MQTransaksiDetail.SQL.Add('update t_transaksi set grandtotal ='+ floattostr(Gatot) +' '+
                                         'where kode_transaksi='''+ cxSpreadSheetBook1.ActiveSheet.GetCellObject(5,i).Text +''' ');
               MQTransaksiDetail.Execute;
          Finally

          end;
          PosisiloadingProgres(cxSpreadSheetBook1.ActiveSheet.RowCount, AdvProgress1);
     End;
     AdvProgress1.Position := 100;
     ShowMessage('Import selesai');
end;

end.