unit U_KuLinList;

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, cxCheckBox, RM_Common, RM_Class, RM_e_Xls, RM_Dataset,
  RM_System, RM_GridReport, Menus, cxCalendar, cxButtonEdit, cxTextEdit, BtnEdit,
  cxPC;

type
  TfrmKuLinList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    cxGridPopupMenu1: TcxGridPopupMenu;
    CDS_Main: TClientDataSet;
    RM1: TRMGridReport;
    RMDBMain: TRMDBDataSet;
    RMXLSExport1: TRMXLSExport;
    RMDBHZ: TRMDBDataSet;
    CDS_HZ: TClientDataSet;
    CDS_PRT: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    ADOQueryPrint: TADOQuery;
    tbJS: TToolButton;
    cxGrid2: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1Column21: TcxGridDBColumn;
    v1Column13: TcxGridDBColumn;
    v2Column1: TcxGridDBColumn;
    v1Column22: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v1Column10: TcxGridDBColumn;
    v1Column1: TcxGridDBColumn;
    v1Column15: TcxGridDBColumn;
    v1Column17: TcxGridDBColumn;
    v1Column18: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1Column24: TcxGridDBColumn;
    cxGrid2Level1: TcxGridLevel;
    Panel1: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label1: TLabel;
    Label10: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    MPRTCodeName: TEdit;
    PRTColor: TEdit;
    ConNo: TEdit;
    chkdate: TCheckBox;
    lidan: TEdit;
    YWY: TEdit;
    cxTabControl1: TcxTabControl;
    tbJSCX: TToolButton;
    Panel2: TPanel;
    Panel4: TPanel;
    Panel3: TPanel;
    Panel5: TPanel;
    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 N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SPIDKeyPress(Sender: TObject; var Key: Char);
    procedure SPIDChange(Sender: TObject);
    procedure SPSpecChange(Sender: TObject);
    procedure FactoryNameChange(Sender: TObject);
    procedure Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
    procedure tbJSClick(Sender: TObject);
    procedure tbJSCXClick(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure Panel2Click(Sender: TObject);
    procedure Panel4Click(Sender: TObject);
    procedure Panel3Click(Sender: TObject);
    procedure Panel5Click(Sender: TObject);
  private
    procedure SetStatus();
    procedure InitGrid(KLScope: string);
    { Private declarations }
  public
    canshu1: string;
    { Public declarations }
  end;

implementation

uses
  U_DataLink, U_RTFun, U_ZDYHelp;

{$R *.dfm}

procedure TfrmKuLinList.SetStatus();
begin
  tbJS.Visible := False;
  tbJSCX.Visible := False;
//  if Trim(canshu1) = '管理' then
//  begin
//    case cxTabControl1.TabIndex of
//      0:
//        begin
//          tbJS.Visible := true;
//
//        end;
//      1:
//        begin
//
//          tbJSCX.Visible := true;
//        end;
//      2:
//        begin
//
//        end;
//
//    end;
//  end;
end;

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

procedure TfrmKuLinList.FormCreate(Sender: TObject);
begin
  EndDate.DateTime := SGetServerDate10(ADOQueryTemp);
  BegDate.DateTime := EndDate.DateTime;

end;

procedure TfrmKuLinList.InitGrid(KLScope: string);
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Close;
      sql.Clear;

      sql.Add('update CK_BanCP_KC set CKDate=DATEDIFF(DD,0,getdate()) where');
      sql.Add('not exists (select DATEDIFF(DD,0,CRTime) from CK_BanCP_CR X where X.MJID=CK_BanCP_KC.MJID and X.CRFlag=''出库'') ');
      sql.Add(' and  RKDate>0');

      sql.Add('select PJKuLin=AA.KuLin/AA.RKPS,AA.KCPS,AA.RKPS,CKPS=AA.RKPS-AA.KCPS,AA.RKDate,AA.CKDate,AA.KCMainId ');
      sql.Add(',B.ConNo,B.lidan,B.YWY,B.MPRTCodeName,B.MPRTCode,B.MPRTMF,B.MPRTKZ,B.ISHZKuLin ');
      sql.Add(',C.*');
      sql.Add(',zt=(case when ISHZKuLin=1 then ''未结束'' else ''已结束'' end)  ');
      sql.Add('from ( ');
      sql.Add('select A.KCMainId,A.KCSubId,SUM(A.RKDate) RKDate,SUM(A.CKDate) CKDate');
      sql.Add(',SUM(A.CKDate-A.RKDate) KuLin,COUNT(A.MJID) RKPS,KCPS=SUM(case when KCValid=''Y'' then 1 else 0 end) ');
      sql.Add('	from CK_BanCP_KC A where  A.RKDate > 0 ');
      sql.Add('	and exists (select MJID from CK_BanCP_CR X where X.MJID=A.MJID and X.CRFlag=''入库'')');
      sql.Add('group by A.KCMainId,A.KCSubId ');
      sql.Add('	) AA');
      sql.Add('inner join JYOrder_Main B on AA.KCMainId=B.MainId inner join JYOrder_Sub C on AA.KCSubId=C.SubId');
      case cxTabControl1.TabIndex of
        0:
          begin
            sql.Add(' where	B.ISHZKuLin=1 ');
          end;
        1:
          begin
            sql.Add(' where	B.ISHZKuLin=0 ');
          end;
        2:
          begin
            sql.Add(' where	1=1 ');
          end;
      end;
      if KLScope = '15到30天' then
      begin
        sql.Add(' and AA.KuLin/AA.RKPS>=15 ');
        sql.Add(' and AA.KuLin/AA.RKPS<30 ');
      end;

      if KLScope = '30到45天' then
      begin
        sql.Add(' and AA.KuLin/AA.RKPS>=30 ');
        sql.Add(' and AA.KuLin/AA.RKPS<45 ');
      end;

      if KLScope = '45到60天' then
      begin
        sql.Add(' and AA.KuLin/AA.RKPS>=45 ');
        sql.Add(' and AA.KuLin/AA.RKPS<60 ');
      end;

      if KLScope = '60天以上' then
      begin
        sql.Add(' and AA.KuLin/AA.RKPS>=60 ');
      end;
      sql.Add(' order by  AA.KuLin/AA.RKPS desc,KCPS desc ');
//      ShowMessage(SQL.Text);
//      sql.Add('exec P_View_KuLin ');
//      sql.Add(' @PageStatus=''' + inttostr() + '''');
      Open;
    end;
    SCreateCDS20(ADOQueryMain, CDS_Main);
    SInitCDSData20(ADOQueryMain, CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;

end;

procedure TfrmKuLinList.TBRafreshClick(Sender: TObject);
begin
  ConNo.SetFocus;

  InitGrid('');

end;

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

procedure TfrmKuLinList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(trim(self.Caption), Tv1, '库龄分析');
  Close;
end;

procedure TfrmKuLinList.FormShow(Sender: TObject);
begin
  SetStatus();

  ReadCxGrid(trim(self.Caption), Tv1, '库龄分析');

  InitGrid('');

end;

procedure TfrmKuLinList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then
    exit;
  TcxGridToExcel('待检布入库列表', cxGrid2);
end;

procedure TfrmKuLinList.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 TfrmKuLinList.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, True);
end;

procedure TfrmKuLinList.N2Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, False);
end;

procedure TfrmKuLinList.SPIDKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    {if Length(Trim(SPID.Text))<4 then Exit;
    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Filtered:=False;
        Close;
        sql.Clear;
        sql.Add(' select A.* ');
        sql.Add(',ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId)');
        //sql.Add(' ,ConNo=(select ConNo from Contract_Main CM where CM.MainId=A.CGMainId )');
        sql.Add(' from CK_SXPB_CR A');
        sql.add(' where SPID like :SXID');
        Parameters.ParamByName('SXID').Value:='%'+Trim(SXID.Text)+'%';
        Open;
        //ShowMessage(SQL.Text);
      end;
      SCreateCDS20(ADOQueryMain,CDS_Main);
      SInitCDSData20(ADOQueryMain,CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end; }
  end;
end;

procedure TfrmKuLinList.SPIDChange(Sender: TObject);
begin

  TBFind.Click;
end;

procedure TfrmKuLinList.SPSpecChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmKuLinList.FactoryNameChange(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmKuLinList.Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
var
  FKuLin: double;
begin
     // 库龄 白15天 绿15到30天 蓝30到45天  黄45到60天  红60天以上  是

  if AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('PJKuLin').Index] = null then
    Exit;
  FKuLin := AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('PJKuLin').Index];

  if ((FKuLin < 30) and (FKuLin >= 15)) then
  begin
    ACanvas.Brush.Color := $00FF00;
  end;
  if ((FKuLin < 45) and (FKuLin >= 30)) then
  begin
    ACanvas.Brush.Color := $ED9564;
  end;
  if ((FKuLin < 60) and (FKuLin >= 45)) then
  begin
    ACanvas.Brush.Color := $00FFFF;
  end;
  if FKuLin > 60 then
  begin
    ACanvas.Brush.Color := $4763FF;
  end;

end;

procedure TfrmKuLinList.tbJSClick(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  JYOrder_Sub SET ISKuLin=''0'' ');
      sql.Add('where SubId=' + quotedstr(trim(CDS_Main.fieldbyname('SubId').AsString)));
      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('orderNO').AsString) + ' SubId：' + trim(CDS_Main.FieldByName('SubId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('订单审核成功！', '提示信息');
    TBRafresh.Click;
  except
    application.MessageBox('订单审核失败！', '提示信息', 0);
  end;
end;

procedure TfrmKuLinList.tbJSCXClick(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  JYOrder_Sub SET ISKuLin=''0'' ');
      sql.Add('where SubId=' + quotedstr(trim(CDS_Main.fieldbyname('SubId').AsString)));
      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('orderNO').AsString) + ' SubId：' + trim(CDS_Main.FieldByName('SubId').AsString))));
      sql.Add(',' + quotedstr(trim('成功')));
      sql.Add(')');
      execsql;
    end;
    application.MessageBox('订单审核成功！', '提示信息');
    TBRafresh.Click;
  except
    application.MessageBox('订单审核失败！', '提示信息', 0);
  end;
end;

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

procedure TfrmKuLinList.Panel2Click(Sender: TObject);
begin
  InitGrid('15到30天');
end;

procedure TfrmKuLinList.Panel4Click(Sender: TObject);
begin
  InitGrid('30到45天');
end;

procedure TfrmKuLinList.Panel3Click(Sender: TObject);
begin
  InitGrid('45到60天');
end;

procedure TfrmKuLinList.Panel5Click(Sender: TObject);
begin
  InitGrid('60天以上');
end;

end.