unit U_CPKCTMXLIST;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData, cxDataStorage,
  cxEdit, DB, cxDBData, cxGridCustomTableView, cxGridTableView,
  cxGridBandedTableView, cxGridDBBandedTableView, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridDBTableView, cxGrid, StdCtrls, ComCtrls,
  ExtCtrls, ToolWin, cxGridCustomPopupMenu, cxGridPopupMenu, ADODB, DBClient,
  cxDropDownEdit, MovePanel, cxButtonEdit, cxLookAndFeels, cxLookAndFeelPainters,
  cxNavigator, dxBarBuiltInMenu, cxPC, cxCheckBox, cxSplitter, Menus;

type
  TfrmCPKCTMXLISTSEL = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    Label3: TLabel;
    c_Code: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    Tv1: TcxGridDBTableView;
    cxGrid2Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    v2Column1: TcxGridDBColumn;
    v2Column5: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    Label6: TLabel;
    MJID: TEdit;
    v1Column6: TcxGridDBColumn;
    MovePanel2: TMovePanel;
    cds_color: TClientDataSet;
    DataSource2: TDataSource;
    cxStyleRepository1: TcxStyleRepository;
    cxStyle1: TcxStyle;
    CDS_MX: TClientDataSet;
    DataSource3: TDataSource;
    cxStyleRepository2: TcxStyleRepository;
    cxStyle2: TcxStyle;
    Tv1Column7: TcxGridDBColumn;
    Tv1Column8: TcxGridDBColumn;
    Tv1Column9: TcxGridDBColumn;
    Tv1Column10: TcxGridDBColumn;
    Panel2: TPanel;
    cxGrid1: TcxGrid;
    TV2: TcxGridDBTableView;
    cxGridDBColumn2: TcxGridDBColumn;
    cxGridDBColumn5: TcxGridDBColumn;
    cxGridDBColumn7: TcxGridDBColumn;
    cxGridDBColumn8: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    TV2Column1: TcxGridDBColumn;
    TV2Column2: TcxGridDBColumn;
    TV2Column3: TcxGridDBColumn;
    TV2Column4: TcxGridDBColumn;
    TV2Column5: TcxGridDBColumn;
    TV2Column6: TcxGridDBColumn;
    TV2Column7: TcxGridDBColumn;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Label5: TLabel;
    C_COLOR: TEdit;
    Label7: TLabel;
    C_GANGNO: TEdit;
    Label8: TLabel;
    C_KUWEI: TEdit;
    cxTabControl1: TcxTabControl;
    TV2Column8: TcxGridDBColumn;
    Label4: TLabel;
    cxStyleRepository3: TcxStyleRepository;
    cxStyle3: TcxStyle;
    crtype: TComboBox;
    cxSplitter1: TcxSplitter;
    TV2Column9: TcxGridDBColumn;
    TV2Column10: TcxGridDBColumn;
    TV2Column11: TcxGridDBColumn;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    ToolButton1: TToolButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ConNoMChange(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure c_CodeChange(Sender: TObject);
    procedure v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure PRTColorChange(Sender: TObject);
    procedure Tv1FocusedRecordChanged(Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean);
    procedure tv2FocusedRecordChanged(Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean);
    procedure cxTabControl1Change(Sender: TObject);
    procedure C_COLORKeyPress(Sender: TObject; var Key: Char);
    procedure C_GANGNOKeyPress(Sender: TObject; var Key: Char);
    procedure C_KUWEIKeyPress(Sender: TObject; var Key: Char);
    procedure c_CodeKeyPress(Sender: TObject; var Key: Char);
    procedure CheckBox1Click(Sender: TObject);
    procedure crtypeChange(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
  private
    procedure InitGrid();
    procedure Initsub();
    procedure Initsubb();
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmCPKCTMXLISTSEL: TfrmCPKCTMXLISTSEL;

implementation

uses
  U_DataLink, U_Fun, U_ZDYHelp;

{$R *.dfm}

procedure TfrmCPKCTMXLISTSEL.FormDestroy(Sender: TObject);
begin
  frmCPKCTMXLISTsel := nil;
end;

procedure TfrmCPKCTMXLISTSEL.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TfrmCPKCTMXLISTSEL.FormCreate(Sender: TObject);
var
  fsj: string;
begin
  //cxGrid1.Align:=alClient;
  BegDate.DateTime := SGetServerDateTime(ADOQueryTemp);
  EndDate.DateTime := SGetServerDateTime(ADOQueryTemp);
  fsj := 'select distinct name=crtype  from CK_BanCP_CR  where crflag=''出库'' order by crtype  ';
  SInitComBoxBySql(ADOQueryCmd, crtype, False, fsj);

  CRType.ItemIndex := CRType.Items.IndexOf('销售出库');
end;

procedure TfrmCPKCTMXLISTSEL.InitGrid();
var
  fwhere, Pwhere: string;
begin
  Pwhere := SGetFilters(Panel1, 1, 2);

  if SGetFilters(Panel1, 1, 2) <> '' then
    Pwhere := ' and ' + SGetFilters(Panel1, 1, 2);
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered := False;
      Close;
      sql.Clear;
      SQL.Add('EXEC P_View_cpkcMX2 ');
      if cxTabControl1.TabIndex = 0 then
      begin
        SQL.Add('@DJ=''正品''');
      end
      else if cxTabControl1.TabIndex = 1 then
      begin
        SQL.Add('@DJ=''次品''');
      end
      else
      begin
        SQL.Add('@DJ=''''');
      end;
      if CRTYPE.Text <> '' then
      begin
        SQL.Add(',@CKTYPE=' + QuotedStr(Trim(CRTYPE.Text)));
      end
      else
      begin
        SQL.Add(',@CKTYPE=''''');
      end;

      if Pwhere <> '' then
      begin
        SQL.Add(',@mssql=' + QuotedStr(Pwhere));
      end;

//      ShowMessage(sql.Text);
//      SQL.Add('select sum(KCKGQTY)KGQTY,SUM(KCQTY) QTY,COUNT(MJID)pS,C_CODE,C_COLOR,C_GANGNO,C_KUWEI,C_SALESCLASS,C_YWY, ');
//      SQL.Add('(CASE WHEN C_DJ=''次品'' or c_dj=''次布'' then ''次品'' else  ''正品'' end)dj');
//      sql.Add('FROM CK_BanCP_KC WHERE KCVALID=''Y'' ');
//      SQL.ADD('group by C_SALESCLASS,C_YWY,C_CODE,C_COLOR,C_GANGNO,C_KUWEI ,(CASE WHEN C_DJ=''次品'' or c_dj=''次布'' then ''次品'' else  ''正品'' end)');
//      if cxTabControl1.TabIndex = 0 then
//      begin
//        SQL.Add('and C_DJ<>''次品'' and c_dj<>''次布''');
//      end
//      else if cxTabControl1.TabIndex = 1 then
//      begin
//        SQL.Add('and C_DJ in (''次品'',''次布'')');
//      end;
//      showmessage(sql.Text);
      Open;
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
end;

procedure TfrmCPKCTMXLISTSEL.InitSUB();
begin
  try
    ADOQueryCmd.DisableControls;
    with ADOQueryCmd do
    begin
      Filtered := False;
      Close;
      sql.Clear;

      SQL.Add('SELECT B.FILLER,A.KCKGQTY,A.KCQTY,A.C_DJ,A.MJID,B.JTTYPE,B.FILLTIME,A.C_KUWEI,B.MJXH,b.jynote,b.jbs,A.C_ORDERNO,B.MAINID ,B.SUBID  ');
      SQL.Add(',isSD=(CASE WHEN ((SELECT COUNT (MJID ) FROM FHSQ_Sub g inner join FHSQ_Main f on f.fsid=g.fsid where g.mjid=A.mjid');
      SQL.Add('and f.sqtype<>''调样'' AND F.Status<>''已取消''  )>=(select Cycle from CK_BanCP_KC k where k.mjid=A.mjid)) 	OR ((SELECT COUNT(MJID) FROM CK_SXPB_CR_mx K  inner join CK_SXPB_CR J ON K.FSID=J.SPID WHERE K.MJID=A.MJID AND ISNULL(K.YCJ,'''')<>''1''');
      SQL.Add('AND CRType=''客户检验'')>0) then ''已锁定'' else ''未锁定'' end)');
      SQL.Add(',sdr=(select top 1 f.filler from FHSQ_Main f inner join  FHSQ_Sub g on f.fsid=g.fsid where g.mjid=A.mjid and f.sqtype<>''调样'' AND F.Status<>''已取消'' order by F.filltime desc)');
      SQL.Add(',sddate=(select top 1 f.FillTime from FHSQ_Main f inner join  FHSQ_Sub g on f.fsid=g.fsid where g.mjid=A.mjid and f.sqtype<>''调样'' AND F.Status<>''已取消'' order by F.filltime desc )');
      SQL.Add(' ,SCSPRICE=(SELECT SCSPRICE FROM SalesContract_Sub E WHERE E.SUBID=(SELECT CONSUBID FROM JYORDER_SUB H WHERE H.SUBID=B.SUBID ))');
      SQL.Add(' FROM CK_BanCP_KC A INNER JOIN WFB_MJJY B ON A.MJID=B.MJID');
      SQL.Add(' WHERE 1=1 ');    //KCVALID=''Y''
      SQL.Add('AND ISNULL(C_CODE,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_CODE').AsString)));
      SQL.Add('AND ISNULL(C_COLOR,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_COLOR').AsString)));
      SQL.Add('AND ISNULL(C_GANGNO,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_GANGNO').AsString)));
      SQL.Add('AND ISNULL(C_SALESCLASS,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_SALESCLASS').AsString)));
      SQL.Add('AND ISNULL(C_YWY,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_YWY').AsString)));
      SQL.Add('AND ISNULL(C_KUWEI,'''')=' + QuotedStr(Trim(CDS_Main.fieldbyname('C_KUWEI').AsString)));
      SQL.Add('AND (CASE WHEN C_DJ=''次品'' or c_dj=''次布'' then ''次品'' else  ''正品'' end)=' + QuotedStr(Trim(CDS_Main.fieldbyname('DJ').AsString)));
      SQL.Add('and KCVALID=''Y''');
//      ShowMessage(SQL.Text);
      Open;
    end;
    SCreateCDS20(ADOQueryCmd, cds_color);
    SInitCDSData20(ADOQueryCmd, cds_color);
  finally
    ADOQueryCmd.EnableControls;
  end;
end;

procedure TfrmCPKCTMXLISTSEL.InitSUBb();
begin
  try
    ADOQueryTemp.DisableControls;
    with ADOQueryTemp do
    begin
      Filtered := False;
      Close;
      sql.Clear;
      SQL.Add('select ISNULL(C_CODE,'''')C_CODE,ISNULL(C_COLOR,'''')C_COLOR,ISNULL(C_GANGNO,'''')C_GANGNO,C_DJ');
      sql.Add(',sum(ISNULL(KCKGQty,0))ZPKCKGQty,sum(ISNULL(KCQty,0))ZPKCQty,count(mjid)ZPps');
      SQL.Add(' from  CK_BanCP_KC A where kcvalid=''Y''');
      SQL.Add(' and a.Cycle=(select max(e.Cycle) from CK_BanCP_CR e where e.mjid=a.mjid)');
      SQL.Add('AND C_CODE=' + QuotedStr(Trim(cds_color.fieldbyname('C_CODE').AsString)));
      SQL.Add('AND C_COLOR=' + QuotedStr(Trim(cds_color.fieldbyname('C_COLOR').AsString)));
      SQL.Add('group by C_CODE,C_COLOR,C_GANGNO,C_DJ');
//         ShowMessage(SQL.Text);
      Open;
    end;
    SCreateCDS20(ADOQueryTemp, cds_mx);
    SInitCDSData20(ADOQueryTemp, cds_mx);
  finally
    ADOQueryTemp.EnableControls;
  end;
end;

procedure TfrmCPKCTMXLISTSEL.TBRafreshClick(Sender: TObject);
begin
  //BegDate.SetFocus;
  MovePanel2.Visible := True;
  MovePanel2.Refresh;
  InitGrid();
  MovePanel2.Visible := False;
end;

procedure TfrmCPKCTMXLISTSEL.ConNoMChange(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain, SGetFilters(Panel1, 1, 2));
  end;
end;

procedure TfrmCPKCTMXLISTSEL.TBCloseClick(Sender: TObject);
begin

  WriteCxGrid(trim(self.Caption), Tv1, '成品仓库');
//  WriteCxGrid(trim(self.Caption), Tv2, '成品仓库');
//  WriteCxGrid(trim(self.Caption), Tv3, '成品仓库');
  Close;
end;

procedure TfrmCPKCTMXLISTSEL.FormShow(Sender: TObject);
begin
  ReadCxGrid(trim(self.Caption), Tv1, '成品仓库');
//  ReadCxGrid(trim(self.Caption), Tv2, '成品仓库');
//  ReadCxGrid(trim(self.Caption), Tv3, '成品仓库');
end;

procedure TfrmCPKCTMXLISTSEL.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then
    exit;
  TcxGridToExcel('库存汇总列表', cxGrid2);
end;

procedure TfrmCPKCTMXLISTSEL.TBFindClick(Sender: TObject);
begin
  if ADOQueryMain.Active then
  begin
    SDofilter(ADOQueryMain, SGetFilters(Panel1, 1, 2));
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  end;
end;

procedure TfrmCPKCTMXLISTSEL.c_CodeChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCPKCTMXLISTSEL.v1Column5PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'SOrdDefStr10';
      flagname := '库存存放地点';
      if ShowModal = 1 then
      begin
        with CDS_Main do
        begin
          Edit;
          FieldByName('SOrdDefStr10').Value := Trim(ClientDataSet1.fieldbyname('ZdyName').AsString);
        end;
        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add('Update JYOrder_Sub Set SOrdDefStr10=''' + Trim(ClientDataSet1.fieldbyname('ZdyName').AsString) + '''');
          sql.Add(' where SubId=''' + Trim(Self.CDS_Main.fieldbyname('SubId').AsString) + '''');
          ExecSQL;
        end;
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmCPKCTMXLISTSEL.PRTColorChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmCPKCTMXLISTSEL.Tv1FocusedRecordChanged(Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean);
begin
  if CDS_Main.IsEmpty = True then
    Exit;
  Initsub();
end;

procedure TfrmCPKCTMXLISTSEL.tv2FocusedRecordChanged(Sender: TcxCustomGridTableView; APrevFocusedRecord, AFocusedRecord: TcxCustomGridRecord; ANewItemRecordFocusingChanged: Boolean);
begin
//  if CDS_COLOR.IsEmpty = True then
//    Exit;
//  initsubb();
end;

procedure TfrmCPKCTMXLISTSEL.cxTabControl1Change(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmCPKCTMXLISTSEL.C_COLORKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    InitGrid();
  end;
end;

procedure TfrmCPKCTMXLISTSEL.C_GANGNOKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    InitGrid();
  end;
end;

procedure TfrmCPKCTMXLISTSEL.C_KUWEIKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    InitGrid();
  end;
end;

procedure TfrmCPKCTMXLISTSEL.c_CodeKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    InitGrid();
  end;
end;

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

procedure TfrmCPKCTMXLISTSEL.crtypeChange(Sender: TObject);
begin
  initgrid();
end;

procedure TfrmCPKCTMXLISTSEL.N1Click(Sender: TObject);
begin
  SelOKNo(cds_color, True);
end;

procedure TfrmCPKCTMXLISTSEL.N2Click(Sender: TObject);
begin
  SelOKNo(cds_color, False);
end;

procedure TfrmCPKCTMXLISTSEL.ToolButton1Click(Sender: TObject);
begin
  if CDS_color.IsEmpty then
    exit;
  ModalResult := 1;
end;

end.