unit U_skPieceSampleKcHZList;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ToolWin, StdCtrls, ExtCtrls, cxStyles, cxCustomData,
  cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData,
  cxButtonEdit, cxDropDownEdit, cxGridLevel, cxGridCustomTableView,
  cxGridTableView, cxGridDBTableView, cxClasses, cxControls, cxGridCustomView,
  cxGrid, DBClient, ADODB, cxGridCustomPopupMenu, cxGridPopupMenu, cxCheckBox,
  RM_Common, RM_Class, RM_GridReport, RM_System, RM_Dataset, MMSystem, Menus,
  BtnEdit, U_SLT, jpeg, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator,
  cxTextEdit;

type
  TfrmSKPieceSampleKcHZList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1Qty: TcxGridDBColumn;
    v1QtyUnit: TcxGridDBColumn;
    cxGrid1Level1: TcxGridLevel;
    Label3: TLabel;
    YPTYPE: TEdit;
    ADOQueryCmd: TADOQuery;
    ADOQueryTmp: TADOQuery;
    DataSource1: TDataSource;
    CDS_Main: TClientDataSet;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label7: TLabel;
    v1Column5: TcxGridDBColumn;
    ADOQueryPrint: TADOQuery;
    RMDB_Main: TRMDBDataSet;
    RM1: TRMGridReport;
    v1Column8: TcxGridDBColumn;
    ADOQueryMain: TADOQuery;
    P_Code: TEdit;
    Label18: TLabel;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    v1Column3: TcxGridDBColumn;
    ToolButton2: TToolButton;
    adoqueryPicture: TADOQuery;
    CheckBox1: TCheckBox;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    Label1: TLabel;
    Tv1Column3: TcxGridDBColumn;
    Label2: TLabel;
    HZZNOTE: TEdit;
    ToolButton1: TToolButton;
    Panel5: TPanel;
    Label4: TLabel;
    Button5: TButton;
    Button6: TButton;
    Edit2: TEdit;
    Tv1Column4: TcxGridDBColumn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure YPTYPEChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
    procedure Tv1CellClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean);
    procedure CheckBox1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Tv1Column3PropertiesEditValueChanged(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Tv1Column4PropertiesEditValueChanged(Sender: TObject);
  private
    procedure InitGrid();
    procedure SetStatus();

    { Private declarations }
  public
    fType: integer;
    fCKName: string;
    
    { Public declarations }
  end;

var
  frmSKPieceSampleKcHZList: TfrmSKPieceSampleKcHZList;

var
  Mach: array of TfrmSlt;

implementation

uses
  U_DataLink, U_Fun10, U_ZDYHelp;
{$R *.dfm}

procedure TfrmSKPieceSampleKcHZList.SetStatus();
begin

end;

procedure TfrmSKPieceSampleKcHZList.InitGrid();
begin
  try

    with adoqueryMain do
    begin
      close;
      sql.Clear;
      if CheckBox1.Checked = True then
      begin
        SQL.Add('select * from (');
        sql.Add(' select A.P_Code,yptype,A.QtyUnit,b.hzznote  ');
        sql.Add('  ,count(crqtyflag *A.RollNum) KCRollNum,sum(crqtyflag*A.Qty) KCQty ');
        SQL.Add(',rkqty=(select sum(qty) from CK_Skgb_CR b where b.p_code=a.p_code and b.yptype=a.yptype and b.qtyunit=a.qtyunit');
        sql.Add('and  crqtyflag=1  and  b.Valid=''Y''  ');
        sql.Add(' and b.CRTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        sql.Add(' and b.CRTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.Add(')');
        SQL.Add(',CKQTY=(select sum(qty) from CK_Skgb_CR b where b.p_code=a.p_code and b.yptype=a.yptype and b.qtyunit=a.qtyunit');
        sql.Add('and  crqtyflag=-1  and  b.Valid=''Y''  ');
        sql.Add(' and b.CRTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        sql.Add(' and b.CRTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.Add(')');

        SQL.Add(' from CK_Skgb_CR A left join hznote b on a.p_code=b.fla and a.YPTYPE=b.flb and b.fltype=''色卡库存'' ');
        sql.Add(' where  CKName=' + quotedstr(trim(fCKName)));
        sql.ADD(' and  A.Valid=''Y'' ');
        sql.Add(' group by A.P_Code,yptype,A.QtyUnit,b.hzznote');
        sql.Add(')aa where KCQty<0');
      end
      else
      begin
        SQL.Add('select * from (');
        sql.Add(' select A.P_Code,yptype,A.QtyUnit,b.hzznote,hzznote2  ');
        sql.Add('  ,count(crqtyflag *A.RollNum) KCRollNum,sum(crqtyflag*A.Qty) KCQty ');

        SQL.Add(',rkqty=(select sum(qty) from CK_Skgb_CR b where b.p_code=a.p_code and b.yptype=a.yptype and b.qtyunit=a.qtyunit');
        sql.Add('and  crqtyflag=1  and  b.Valid=''Y'' AND b.Chkstatus=''1'' ');
        sql.Add(' and b.CRTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        sql.Add(' and b.CRTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.Add(')');
        SQL.Add(',CKQTY=(select sum(qty) from CK_Skgb_CR b where b.p_code=a.p_code and b.yptype=a.yptype and b.qtyunit=a.qtyunit');
        sql.Add('and  crqtyflag=-1  and  b.Valid=''Y'' AND b.Chkstatus=''1'' ');
        sql.Add(' and b.CRTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
        sql.Add(' and b.CRTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
        sql.Add(')');

        SQL.Add(' from CK_Skgb_CR A left join hznote b on a.p_code=b.fla and a.YPTYPE=b.flb and b.fltype=''色卡库存''');
        sql.Add(' where  CKName=' + quotedstr(trim(fCKName)));
        sql.ADD(' and  A.Valid=''Y'' AND Chkstatus=''1''');
        sql.Add(' group by A.P_Code,yptype,A.QtyUnit,b.hzznote,hzznote2');
        sql.Add(')aa where KCQty>0 OR HZZNOTE IS NOT NULL ');
      end;
//  ShowMessage(sql.Text);
      open;
    end;
    SCreateCDS20(adoqueryMain, CDS_Main);
    SInitCDSData20(adoqueryMain, CDS_Main);
  finally
  end;
end;

procedure TfrmSKPieceSampleKcHZList.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  writeCxGrid(self.Caption + tv1.Name, Tv1, '面料仓库');
  if fType = 10 then
    Action := cahide
  else
    Action := cafree;
end;

procedure TfrmSKPieceSampleKcHZList.FormDestroy(Sender: TObject);
begin
  frmSKPieceSampleKcHZList := nil;
end;

procedure TfrmSKPieceSampleKcHZList.YPTYPEChange(Sender: TObject);
begin
  if adoqueryMain.Active = False then
    Exit;
  SDofilter(adoqueryMain, SGetFilters(Panel1, 1, 2));
  SCreateCDS20(adoqueryMain, CDS_Main);
  SInitCDSData20(adoqueryMain, CDS_Main);
end;

procedure TfrmSKPieceSampleKcHZList.FormShow(Sender: TObject);
begin
  readCxGrid(self.Caption + tv1.Name, Tv1, '面料仓库');
  with ADOQueryTmp do
  begin
    close;
    sql.Clear;
    sql.Add('select * from sy_user where username=' + QuotedStr(Trim(DName)));
    Open;
  end;
//  ToolButton1.Visible := True;
  if (ADOQueryTmp.fieldbyname('udept').AsString <> '总经办') and (Trim(DName) <> 'ADMIN') then
  begin
    Tv1Column3.Options.Editing := False;
//    ToolButton1.Visible := False;
  end;
  SetStatus();
  InitGrid();
end;

procedure TfrmSKPieceSampleKcHZList.TBCloseClick(Sender: TObject);
begin
  close;
end;

procedure TfrmSKPieceSampleKcHZList.TBRafreshClick(Sender: TObject);
begin
  P_Code.SetFocus;
  initGrid();
end;

procedure TfrmSKPieceSampleKcHZList.TBExportClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    Exit;
  TcxGridToExcel(self.Caption, cxgrid1);
end;

procedure TfrmSKPieceSampleKcHZList.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, true);
end;

procedure TfrmSKPieceSampleKcHZList.N2Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, false);

end;

procedure TfrmSKPieceSampleKcHZList.ToolButton2Click(Sender: TObject);
begin
  if adoqueryMain.Active = False then
    Exit;
  SDofilter(adoqueryMain, SGetFilters(Panel1, 1, 2));
  SCreateCDS20(adoqueryMain, CDS_Main);
  SInitCDSData20(adoqueryMain, CDS_Main);
end;

procedure TfrmSKPieceSampleKcHZList.Tv1CellClick(Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean);
begin
//  InitImage();
end;

procedure TfrmSKPieceSampleKcHZList.CheckBox1Click(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmSKPieceSampleKcHZList.FormCreate(Sender: TObject);
begin
  EndDate.Date := SGetServerDate10(ADOQueryTmp);
  BegDate.Date := EndDate.Date - 30;
end;

procedure TfrmSKPieceSampleKcHZList.Tv1Column3PropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin

  mvalue := TcxTextEdit(Sender).EditingText;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Main do
  begin
    Edit;
    FieldByName(FFieldName).Value := mvalue;
    Post;
  end;
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    sql.Add('SELECT * FROM HZNOTE WHERE FLA=' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
    SQL.Add(' AND FLB=' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
    SQL.Add('AND FlTYPE=''色卡库存''');

    Open;

  end;
  if ADOQueryTmp.IsEmpty = True then
  begin
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('insert into HZNOTE(fla,flb,hzznote2,hzznote,fltype) values (' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
      sql.Add(',' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
      sql.Add(',' + quotedstr(Trim(CDS_Main.fieldbyname('HZZNOTE2').AsString)));
      sql.Add(',' + quotedstr(Trim(mvalue)));
      sql.Add(',''色卡库存'')');
      ExecSQL;
    end;
  end
  else
  begin
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('update  HZNOTE set hzznote=' + quotedstr(Trim(mvalue)));
      sql.Add(' WHERE FLA=' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
      SQL.Add(' AND FLB=' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
      SQL.Add('AND FlTYPE=''色卡库存''');
      ExecSQL;
    end;
  end;

  tv1.Controller.EditingController.ShowEdit();
end;

procedure TfrmSKPieceSampleKcHZList.ToolButton1Click(Sender: TObject);
begin
  Panel5.Visible := True;
  Edit2.Text := Trim(CDS_Main.fieldbyname('kcqty').AsString);
end;

procedure TfrmSKPieceSampleKcHZList.Button5Click(Sender: TObject);
var
  MAXNO: string;
begin
  try
    ADOQueryCmd.Connection.BeginTrans;
    if GetLSNo(ADOQueryTmp, MAXNO, 'SKC', 'CK_Skgb_CR', 3, 1) = False then
    begin

      Application.MessageBox('取最大号失败!', '提示', 0);
      ADOQueryCmd.Connection.RollbackTrans;
      Exit;
    end;
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('select * from CK_Skgb_CR');
      sql.Add('where 1<>1');
      open;
    end;
    with ADOQueryCmd do
    begin

      Append;
      FieldByName('Filler').Value := trim(DName);
      FieldByName('FillTime').Value := SGetServerDate(ADOQueryTmp);
      FieldByName('crTime').Value := SGetServerDate(ADOQueryTmp);
      FieldByName('CKName').Value := trim(fCKName);
      FieldByName('CRNO').Value := trim(MAXNO);
      FieldByName('inoutno').Value := Trim(MAXNO);
      FieldByName('CRQtyFlag').Value := -1;
      FieldByName('CRFlag').Value := '出库';
      FieldByName('CRTYPE').Value := '库存调整';
//      FieldByName('kuwei').Value := trim(CDS_Main.fieldbyname('kuwei').AsString);
//      FieldByName('factoryName').Value := trim(CDS_Main.fieldbyname('factoryName').AsString);

      FieldByName('P_Code').Value := trim(CDS_Main.fieldbyname('P_Code').AsString);
//      FieldByName('YWY').Value := trim(CDS_Main.fieldbyname('YWY').AsString);


      FieldByName('yptype').Value := trim(CDS_Main.fieldbyname('yptype').AsString);
      FieldByName('QtyUnit').Value := trim(CDS_Main.fieldbyname('QtyUnit').AsString);

      FieldByName('QTY').Value := CDS_Main.fieldbyname('kcQty').AsFloat - STRTOFLOATDEF(Edit2.Text, 0);

      Post;

    end;

    with ADOQueryTmp do
    begin
      Close;
      SQL.Clear;
      sql.Add('insert into SY_SysLog(operor,opertime,Model,acction,opevent,result) values( ');
      sql.Add(' ' + quotedstr(trim(DName)));
      sql.Add(',getdate() ');
      sql.Add(',' + quotedstr(trim(self.Caption)));
      sql.Add(',' + quotedstr(trim('修改样布库存')));
      sql.Add(',' + quotedstr(trim('原库存:' + Trim(CDS_Main.fieldbyname('kcqty').AsString) + ';现库存:' + Trim(edit2.Text))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      ExecSQL;
    end;
    with CDS_Main do
    begin
      Edit;
      CDS_Main.fieldbyname('kcqty').AsString := edit2.text;
      Post;
    end;

    CDS_Main.EnableControls;
    Panel5.Visible := False;
    ADOQueryCmd.Connection.CommitTrans;
    Application.MessageBox('操作成功!', '提示', 0);
//    InitGrid();
  except
    CDS_Main.EnableControls;
    ADOQueryCmd.Connection.RollbackTrans;
  end;
end;

procedure TfrmSKPieceSampleKcHZList.Button6Click(Sender: TObject);
begin
  Panel5.Visible := False;
end;

procedure TfrmSKPieceSampleKcHZList.Tv1Column4PropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin

  mvalue := TcxTextEdit(Sender).EditingText;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Main do
  begin
    Edit;
    FieldByName(FFieldName).Value := mvalue;
    Post;
  end;
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    sql.Add('SELECT * FROM HZNOTE WHERE FLA=' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
    SQL.Add(' AND FLB=' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
    SQL.Add('AND FlTYPE=''色卡库存''');

    Open;

  end;
  if ADOQueryTmp.IsEmpty = True then
  begin
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('insert into HZNOTE(fla,flb,hzznote,hzznote2,fltype) values (' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
      sql.Add(',' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
      sql.Add(',' + quotedstr(Trim(CDS_Main.fieldbyname('HZZNOTE').AsString)));
      sql.Add(',' + quotedstr(Trim(mvalue)));
      sql.Add(',''色卡库存'')');
      ExecSQL;
    end;
  end
  else
  begin
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('update  HZNOTE set hzznote2=' + quotedstr(Trim(mvalue)));
      sql.Add(' WHERE FLA=' + quotedstr(Trim(CDS_Main.fieldbyname('P_Code').AsString)));
      SQL.Add(' AND FLB=' + quotedstr(Trim(CDS_Main.fieldbyname('YPTYPE').AsString)));
      SQL.Add('AND FlTYPE=''色卡库存''');
      ExecSQL;
    end;
  end;

  tv1.Controller.EditingController.ShowEdit();
end;

end.