unit U_JYSQlistsel;

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, RM_Common,
  RM_Class, RM_e_Xls, RM_GridReport, RM_System, RM_Dataset, cxPC, cxLookAndFeels,
  cxLookAndFeelPainters, cxNavigator, dxBarBuiltInMenu, cxTextEdit, cxCalendar,
  cxCheckBox;

type
  TfrmJYSQLISTSEL = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    Label1: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    Label3: TLabel;
    Label4: TLabel;
    P_CodeName: TEdit;
    P_Code: TEdit;
    ADOQueryCmd: TADOQuery;
    ADOQueryTmp: TADOQuery;
    DataSource1: TDataSource;
    CDS_Main: TClientDataSet;
    cxGridPopupMenu1: TcxGridPopupMenu;
    ckName: TComboBox;
    Label6: TLabel;
    ADOPrint: TADOQuery;
    RMDB_MD: TRMDBDataSet;
    RMGridReport1: TRMGridReport;
    RMXLSExport1: TRMXLSExport;
    kuwei: TEdit;
    Label8: TLabel;
    cxTabControl1: TcxTabControl;
    P_Color: TEdit;
    Label9: TLabel;
    custName: TEdit;
    Label2: TLabel;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1P_Code: TcxGridDBColumn;
    v1P_CodeName: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    v1P_SPEC: TcxGridDBColumn;
    v1P_Color: TcxGridDBColumn;
    v1RollNum: TcxGridDBColumn;
    v1Qty: TcxGridDBColumn;
    v1QtyUnit: TcxGridDBColumn;
    v1Note: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1Column6: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    v1P_MF: TcxGridDBColumn;
    v1P_KZ: TcxGridDBColumn;
    v1Column10: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    v1Column7: TcxGridDBColumn;
    v1Column11: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    cxGrid1Level1: TcxGridLevel;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    Tv1Column8: TcxGridDBColumn;
    ToolButton1: TToolButton;
    Tv1Column1: TcxGridDBColumn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure TBAddClick(Sender: TObject);
    procedure P_CodeNameChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure TBEditClick(Sender: TObject);
    procedure TBDelClick(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure TBPrintClick(Sender: TObject);
    procedure TBExportClick(Sender: TObject);
    procedure TbPrint1Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure TchkClick(Sender: TObject);
    procedure TnochkClick(Sender: TObject);
    procedure btnDelDataClick(Sender: TObject);
    procedure btnDelCXClick(Sender: TObject);
    procedure Tv1CustomDrawIndicatorCell(Sender: TcxGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxCustomGridIndicatorItemViewInfo; var ADone: Boolean);
    procedure ToolButton1Click(Sender: TObject);
    procedure Tv1DblClick(Sender: TObject);
    procedure Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
  private
    procedure InitGrid();
    procedure SetComboBox();
    procedure SetStatus();
    { Private declarations }
  public
    fCKName: string;
    canshu1: string;
    { Public declarations }
  end;

var
  frmJYSQLISTSEL: TfrmJYSQLISTSEL;

implementation

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

procedure TfrmJYSQLISTSEL.SetStatus();
begin

end;

procedure TfrmJYSQLISTSEL.SetComboBox();
begin
  ckName.Items.Clear;
  with adoQueryTmp do
  begin
    close;
    sql.Clear;
    sql.Add('select * from KH_ZDY where Type=''YPCK'' ');
    if trim(fCKName) <> '' then
      sql.Add('and zdyName=' + quotedstr(trim(fCKName)));
    open;
    while not eof do
    begin
      ckName.Items.Add(trim(fieldbyname('zdyName').AsString));
      next;
    end;
  end;
  if ckName.Items.Count > 0 then
    ckName.ItemIndex := 0;

end;

procedure TfrmJYSQLISTSEL.InitGrid();
begin
  try
    with adoqueryTmp do
    begin
      close;
      sql.Clear;
      sql.Add('select A.*   from YP_InOut2 A');
      sql.Add('where CRTime>=''' + formatdateTime('yyyy-MM-dd', begdate.Date) + ''' ');
      sql.Add('and CRTime<''' + formatdateTime('yyyy-MM-dd', enddate.Date + 1) + ''' ');
      sql.Add('and CRFlag=''出库'' ');
      sql.Add('and ckname=''申请单''');
      sql.Add('and valid=''Y''');
      if cxTabControl1.TabIndex = 0 then
      begin
        SQL.Add('AND status=''0''');
        sql.Add('and  not  exists (select * from CK_Sample_CR c where c.JYSQD=A.CRID  and valid=''Y'' )');
      end
      else if cxTabControl1.TabIndex = 1 then
      begin
        sql.Add('and   exists (select * from CK_Sample_CR c where c.JYSQD=A.CRId and valid=''Y'' )');
      end;
      sql.add('order by filltime desc');

      open;
    end;
    SCreateCDS20(adoqueryTmp, CDS_Main);
    SInitCDSData20(adoqueryTmp, CDS_Main);
  finally
  end;
end;

procedure TfrmJYSQLISTSEL.FormClose(Sender: TObject; var Action: TCloseAction);
begin

  Action := cafree;
end;

procedure TfrmJYSQLISTSEL.FormDestroy(Sender: TObject);
begin
  frmJYSQLISTsel := nil;
end;

procedure TfrmJYSQLISTSEL.TBAddClick(Sender: TObject);
begin
  frmJYSQinput := TfrmJYSQinput.create(self);
  with frmJYSQinput do
  begin
    fkeyNo := '';
    fType := 0;
    fckName := self.fckName;

    if showmodal = 1 then
    begin
      TBRafresh.Click;
      self.CDS_Main.Locate('crNo', trim(fkeyNo), []);
    end;
    free;
  end;
end;

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

procedure TfrmJYSQLISTSEL.FormShow(Sender: TObject);
var
  fsj: string;
begin
  readCxGrid(self.Caption, Tv1, '样品仓库999');

  SetComboBox();
  SetStatus();
  InitGrid();
end;

procedure TfrmJYSQLISTSEL.FormCreate(Sender: TObject);
begin
  BegDate.Date := SGetServerDate10(ADOQueryTmp) - 7;
  EndDate.Date := SGetServerDate10(ADOQueryTmp);
  cxGrid1.Align := alclient;

end;

procedure TfrmJYSQLISTSEL.TBCloseClick(Sender: TObject);
begin
  writeCxGrid(self.Caption, Tv1, '样品仓库999');
  close;
end;

procedure TfrmJYSQLISTSEL.TBEditClick(Sender: TObject);
begin
  if cds_main.IsEmpty then
    exit;

  frmJYSQinput := TfrmJYSQinput.create(self);
  with frmJYSQinput do
  begin
    fkeyNo := self.cds_main.fieldbyname('CRNO').AsString;
    fType := 1;
    fckName := self.fckName;
    if showmodal = 1 then
    begin
      TBRafresh.Click;
      self.CDS_Main.Locate('crNo', trim(fkeyNo), []);
    end;
    free;
  end;
end;

procedure TfrmJYSQLISTSEL.TBDelClick(Sender: TObject);
var
  YFID, CRID: string;
begin
  if CDS_Main.IsEmpty then
    Exit;

  if Application.MessageBox('是否要整单删除？', '提示', 32 + 4) <> IDYES then
    Exit;

  try

    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;

      sql.Add('update  YP_InOut2 SET valid=''N'' ');
      sql.Add(',editer=' + quotedstr(trim(DName)));
      sql.Add(',edittime=getdate()');
      SQL.Add('where  CRNO=''' + Trim(CDS_Main.fieldbyname('CRNO').AsString) + ''' ');
//
//      sql.Add('delete from YF_money_CR  ');
//      SQL.Add('where mainID=''' + Trim(CDS_Main.fieldbyname('CRNO').AsString) + ''' ');
//      SQL.Add('and subID=''' + Trim(CDS_Main.fieldbyname('CRID').AsString) + ''' ');

      execsql;
    end;
    TBRafresh.Click;
  except
    Application.MessageBox('数据删除失败！', '提示信息', 0);
  end;
end;

procedure TfrmJYSQLISTSEL.TBRafreshClick(Sender: TObject);
begin
  initGrid();
end;

procedure TfrmJYSQLISTSEL.TBPrintClick(Sender: TObject);
var
  filepath: string;
begin
  if CDS_Main.IsEmpty then
    exit;

  with AdoPrint do
  begin
    close;
    sql.Clear;
    sql.Add('exec P_Print_cpCkMd ');
    sql.Add('@inoutNo=' + quotedstr(trim(CDS_Main.fieldbyname('inoutNO').AsString)));
    sql.Add(',@flag=' + quotedstr(trim('')));
    open;
  end;

  try
    filepath := ExtractFilePath(Application.ExeName) + 'report\出库码单.rmf';
    if not FileExists(Pchar(filepath)) then
    begin
      application.MessageBox(pchar('文件[' + filepath + ']不存在！'), '提示信息', MB_IConError);
      exit;
    end;
    RMVariables['dtxz'] := FormatDateTime('yyyy-mm-dd', date());
    RMVariables['zdr'] := trim(DName);
    RMVariables['Filler'] := trim(CDS_Main.fieldbyname('Filler').AsString);
    RMGridReport1.LoadFromFile(filepath);
    RMGridReport1.ShowReport;
  finally

  end;

end;

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

procedure TfrmJYSQLISTSEL.TbPrint1Click(Sender: TObject);
var
  filepath: string;
begin
  if CDS_Main.IsEmpty then
    exit;

  with AdoPrint do
  begin
    close;
    sql.Clear;
    sql.Add(' select A.*,B.CYMF,B.CYKZ,A.SHAZHI defnote5,       ');
    sql.Add(' FilesOther=(select top 1 X1.FilesOther from TP_File X1,CP_YDang X2 where X1.WBID=X2.CYID and X2.CYNo=A.P_Code and X1.TFType=''样品'') ');
    sql.Add(' from YP_INout2 A ');
    sql.Add(' left join CP_YDang B on B.CYNO=A.P_Code');
    sql.Add(' where A.CRNo=' + quotedstr(trim(CDS_Main.fieldbyname('CRNO').AsString)));
    open;
  end;

  try
    filepath := ExtractFilePath(Application.ExeName) + 'report\样品发货单.rmf';
    if not FileExists(Pchar(filepath)) then
    begin
      application.MessageBox(pchar('文件[' + filepath + ']不存在！'), '提示信息', MB_IConError);
      exit;
    end;
    RMVariables['dtxz'] := FormatDateTime('yyyy-mm-dd', date());
    RMVariables['zdr'] := trim(DName);
    RMVariables['Filler'] := trim(CDS_Main.fieldbyname('Filler').AsString);
    RMGridReport1.LoadFromFile(filepath);
    RMGridReport1.ShowReport;
  finally

  end;

end;

procedure TfrmJYSQLISTSEL.cxTabControl1Change(Sender: TObject);
begin
  SetStatus();
  initGrid();
end;

procedure TfrmJYSQLISTSEL.TchkClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  YP_InOut2 SET status=''1'' ');
      sql.Add('where CRNO=' + quotedstr(trim(CDS_Main.fieldbyname('CRNO').AsString)));
      execsql;
    end;
    application.MessageBox('审核成功！', '提示信息');
    TBRafresh.Click;
  except
    application.MessageBox('审核失败！', '提示信息', 0);
  end;
end;

procedure TfrmJYSQLISTSEL.TnochkClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  try
    with ADOQueryCmd do
    begin
      close;
      sql.Clear;
      sql.Add('update  YP_InOut2 SET status=''0'' ');
      sql.Add('where CRNO=' + quotedstr(trim(CDS_Main.fieldbyname('CRNO').AsString)));
      execsql;
    end;
    application.MessageBox('撤销审核成功！', '提示信息');
    TBRafresh.Click;
  except
    application.MessageBox('撤销审核失败！', '提示信息', 0);
  end;
end;

procedure TfrmJYSQLISTSEL.btnDelDataClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    Exit;

  if Application.MessageBox('是否要删除数据？', '提示', 32 + 4) <> IDYES then
    Exit;

  try
    ADOQueryCmd.Connection.BeginTrans;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('delete  YP_InOut2  where CRID=' + quotedstr(CDS_Main.fieldbyname('CRID').AsString));
      execsql;
    end;

    ADOQueryCmd.Connection.CommitTrans;
    CDS_Main.Delete;
  except
    ADOQueryCmd.Connection.RollbackTrans;

    Application.MessageBox('数据删除失败！', '提示信息', 0);
  end;
end;

procedure TfrmJYSQLISTSEL.btnDelCXClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    Exit;

  if Application.MessageBox('是否撤销删除？', '提示', 32 + 4) <> IDYES then
    Exit;

  try

    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;

      sql.Add('update  YP_InOut2 SET valid=''Y'' ');
      sql.Add(',editer=' + quotedstr(trim(DName)));
      sql.Add(',edittime=getdate()');
      SQL.Add('where  CRNO=''' + Trim(CDS_Main.fieldbyname('CRNO').AsString) + ''' ');

      execsql;
    end;
    TBRafresh.Click;
  except
    Application.MessageBox('数据撤销删除失败！', '提示信息', 0);
  end;
end;

procedure TfrmJYSQLISTSEL.Tv1CustomDrawIndicatorCell(Sender: TcxGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxCustomGridIndicatorItemViewInfo; var ADone: Boolean);
var
  FValue: string;
  FBounds: TRect;
begin
  FBounds := AViewInfo.Bounds;
  if (AViewInfo is TcxGridIndicatorRowItemViewInfo) then
  begin
    ACanvas.FillRect(FBounds);
    ACanvas.DrawComplexFrame(FBounds, clBtnHighlight, clBtnShadow, [bBottom, bLeft, bRight], 1);
    FValue := IntToStr(TcxGridIndicatorRowItemViewInfo(AViewInfo).GridRecord.Index + 1);
    InflateRect(FBounds, -1, -1);    //Platform   specific.   May   not   work   on   Linux.
    ACanvas.Font.Color := clBlack;
    ACanvas.Brush.Style := bsClear;
    ACanvas.DrawText(FValue, FBounds, cxAlignCenter or cxAlignTop);
    ADone := True;
  end;
end;

procedure TfrmJYSQLISTSEL.ToolButton1Click(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  if cxTabControl1.TabIndex <> 0 then
    Exit;
  ModalResult := 1;
end;

procedure TfrmJYSQLISTSEL.Tv1DblClick(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    exit;
  if cxTabControl1.TabIndex <> 0 then
    Exit;
  ModalResult := 1;
end;

procedure TfrmJYSQLISTSEL.Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
begin
  if (AViewInfo.GridRecord.Values[Tv1Column1.Index] = 1) then
  begin
    ACanvas.font.Color := clRed;
  end;
end;

end.