unit DMaterial;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Unitmb, ImgList, Menus, ComCtrls, ToolWin, Grids, DBGridEh,
  Buttons, StdCtrls, ExtCtrls, FR_Ctrls, Mask, DBCtrls, dbcgrids, DBCtrlsEh;

type
  TDMaterialForm = class(TmbForm)
    edtSelfCode: TEdit;
    edtQty: TEdit;
  private
    { Private declarations }
    sSelfCode:string;
    //nMGoodsId,nMOldGoodsid:integer; //goodsid=CusId
    nMQty,nMOldQty:real;
    procedure InitInfo;override;
    procedure InsertDetail;override;
    procedure UpdateDetail;override;    
    procedure InsertMaster(sInvono:string);override;
    procedure UpdateMaster;override;
    procedure ShowMasterSpecial(bAdd:boolean);override;
  public
    { Public declarations }
  end;

var
  DMaterialForm: TDMaterialForm;

implementation
uses datamodule1, selPaytype;
{$R *.dfm}

procedure TDMaterialForm.InitInfo;
begin
  inherited;
  nStoreId:=100;
  nCusId:=100;
end;

procedure TDMaterialForm.InsertDetail;
var
  sSql:string;
begin
  with dataE2 do
  begin
    sSql:='( :billid, :itemno, :GoodsId, :units, :qty, :disc, :memo1)';
    adoCmd.commandtext:='insert into '+myTable+'D values '+sSql;
    adoCmd.parameters[0].value:=nBillId;
    adoCmd.parameters[1].value:=queryDetail.fieldbyname('itemno').asinteger;
    adoCmd.parameters[2].value:=nGoodsId;
    adoCmd.parameters[3].value:=queryDetail.fieldbyname('units').asstring;
    adoCmd.parameters[4].value:=nQty;
    adoCmd.parameters[5].value:=queryDetail.fieldbyname('disc').AsBoolean;
    adoCmd.parameters[6].value:=queryDetail.fieldbyname('memo1').asstring;

    adoCmd.execute;
  end;
end;

procedure TDMaterialForm.UpdateDetail;
begin
  with dataE2 do
  begin
    adoCmd.commandtext:='update '+myTable+'D set goodsId = :goodsid,units=:units,qty = :qty,disc=:disc, memo1= :memo1 where billid= :billid and itemno= :itemno';
    adoCmd.parameters[0].value:=queryDetail.fieldbyname('goodsId').asinteger;
    adoCmd.parameters[1].value:=queryDetail.fieldbyname('units').asstring;
    adoCmd.parameters[2].value:=queryDetail.fieldbyname('qty').asfloat;
    adoCmd.parameters[3].value:=queryDetail.fieldbyname('disc').AsBoolean;
    adoCmd.parameters[4].value:=queryDetail.fieldbyname('memo1').asstring;
    adoCmd.parameters[5].value:=nBillId;
    adoCmd.parameters[6].value:=queryDetail.fieldbyname('itemno').asstring;

    adoCmd.execute;
  end;
end;

procedure TDMaterialForm.InsertMaster(sInvoNo:string);
begin
  with dataE2 do
    InsertDMaterial(nBillid,nEmployId,nCusid,edtBillDate.date,edtInvoNo.text,edtmemo.text,edtSelfCode.text,strTofloat(edtQty.text));
end;

procedure TDMaterialForm.UpdateMaster;
var
  i:integer;
begin
  sMasterMemo:=trim(edtMemo.text);
  nMQty:=strTofloat(edtQty.text);

  //dynamic sql sentence
  i:=0;
  with dataE2 do
  begin
    adoCmd.CommandText:='update '+mytable+' set ';

    if  nEmployId <> nOldEmployId then
    begin
      adoCmd.CommandText :=adoCmd.CommandText +' Employid= :employid ';
      adoCmd.Parameters[i].value:=nEmployId;
      inc(i);
    end;

    if  nCusid <> nOldCusid then
    begin
      if i >0 then adoCmd.CommandText :=adoCmd.CommandText +',';

      adoCmd.CommandText :=adoCmd.CommandText +' GoodsId= :goodsId ';
      adoCmd.Parameters[i].value:=nCusid;
      inc(i);
    end;

    if  sSelfCode <> edtSelfCode.text then
    begin
      if i >0 then adoCmd.CommandText :=adoCmd.CommandText +',';

      adoCmd.CommandText :=adoCmd.CommandText +' selfcode= :selfcode ';
      adoCmd.Parameters[i].value:=edtSelfcode.text;
      inc(i);
    end;

    if nMQty <> nMOldqty then
    begin
      if i >0 then adoCmd.CommandText :=adoCmd.CommandText +',';

      adoCmd.CommandText :=adoCmd.CommandText +' qty= :qty ';
      adoCmd.Parameters[i].value:=nMQty;
      inc(i);
    end;

    if  sMasterMemo <> sOldMasterMemo then
    begin
      if i >0 then adoCmd.CommandText :=adoCmd.CommandText +',';

      adoCmd.CommandText :=adoCmd.CommandText +' Memo= :Memo ';
      adoCmd.Parameters[i].value:=sMastermemo;
      inc(i);
    end;

    if i>0 then
    begin
      adoCmd.CommandText :=adoCmd.CommandText +' where BillId= :Billid ';
      adoCmd.parameters[i].value:=nBillid;
      adoCmd.Execute ;
    end;
  end; //dataE2
end;

procedure TDMaterialForm.ShowMasterSpecial(bAdd:boolean);
var
  s,sSql:string;
  i:integer;
begin
  with DataE2 do
  if not bAdd then
  begin
    nEmployId:=adoquery1.fieldbyname('EmployId').asinteger;
    nCusid:=adoquery1.fieldbyname('goodsId').asinteger;
    sMasterMemo:=trim(adoquery1.fieldbyname('Memo').asstring);
    sSelfCode:=trim(adoquery1.fieldbyname('SelfCode').asstring);
    nMQty:=adoquery1.fieldbyname('Qty').asfloat;


    edtMemo.text:=sMastermemo;
    edtSelfCode.text:=sSelfCode;
    edtQty.text:=FloatTostr(nMQty);

    s:='';
    if OpenTable('select * from employ where EmployId= :employid',nEmployid) then
      s:=adoquery1.fieldbyname('name').asstring;

    edtEmp.Text:=s;

    s:='';
    if OpenTable('select * from goods where goodsId= :Goodsid',nCusid) then
      s:=adoquery1.fieldbyname('name').asstring;

    edtName.text:=s;
    adoQuery1.close;

  end
  else begin  //add emptye record,so clear last infomation
    edtQty.Text:='';
    //edtEmp.Text:='';
    edtSelfCode.Text:='';
    edtMemo.text:='';

    s:='';
    nEmployId:=1;
    if OpenTable('select * from employ where EmployId= :employid',nEmployid) then
      s:=adoquery1.fieldbyname('name').asstring;
    edtEmp.Text:=s;
    adoQuery1.Close;
  end;
end;

end.