unit U_DHXSTJList;

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,
  cxLookAndFeels, cxLookAndFeelPainters, cxNavigator, DateUtils;

type
  TfrmDHXSTJList = 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;
    Panel1: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label8: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    SPRTCODE: TEdit;
    CustomerNoName: TEdit;
    chkdate: TCheckBox;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    cxGrid1Level1: TcxGridLevel;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    Tv1Column8: TcxGridDBColumn;
    Label1: TLabel;
    season: TEdit;
    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 N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SPIDKeyPress(Sender: TObject; var Key: Char);
    procedure PIHAOChange(Sender: TObject);
    procedure FactoryNameChange(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
    procedure ToolButton1Click(Sender: TObject);
    procedure TBFindClick(Sender: TObject);
    procedure SPRTCODEChange(Sender: TObject);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure RadioButton3Click(Sender: TObject);
    procedure chkdateClick(Sender: TObject);
  private
    procedure SetStatus();
    procedure InitGrid();
    { Private declarations }
  public
    canshu1: string;
    { Public declarations }
  end;

var
  frmDHXSTJList: TfrmDHXSTJList;

implementation

uses
  U_DataLink, U_RTFun, U_ZDYHelp;

{$R *.dfm}

procedure TfrmDHXSTJList.SetStatus();
begin

end;

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

procedure TfrmDHXSTJList.FormCreate(Sender: TObject);
begin
  EndDate.DateTime := SGetServerDate10(ADOQueryTemp);
  BegDate.DateTime := StartOfTheYear(Now);
end;

procedure TfrmDHXSTJList.InitGrid();
begin
  if RadioButton1.Checked = True then
  begin
    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Close;
        sql.Clear;
        sql.Add('select *,price=ROUND((money/qty),2) from (');
        SQL.Add('select SPrtCode,SUM(FHPS)PS,ISNULL(HZUnit,ORDERUNIT)UNIT, qty=(case when ISNULL(HZUnit,ORDERUNIT)=''公斤'' then SUM(FHQTY) else   sum(FHMS) end ) ,SUM( ');
        SQL.Add('PRICE*(case WHEN ISNULL(HZUnit,ORDERUNIT)=''公斤'' THEN fhqty ELSE FHMS END)    )money');
        SQL.Add(' from FHSQ_Sub  B INNER JOIN FHSQ_Main A ON A.FSID=B.FSID');
        SQL.Add('WHERE FHPS>0 and crtype=''销售出库''   and SQType=''订单'' and iSNULL(HZUnit,ORDERUNIT)<>'''' ');
        if chkdate.Checked then
        begin
          sql.Add(' and A.FHDATE>=' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', Begdate.DateTime))));
          sql.Add(' and A.FHDATE<' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', EndDate.DateTime + 1))));
        end;
        SQL.Add('GROUP BY SPrtCode,ISNULL(HZUnit,ORDERUNIT) )AA');
        sql.Add('order by [dbo].[getNum](SPrtCode)  ');
        Open;
      end;
      SCreateCDS20(ADOQueryMain, CDS_Main);
      SInitCDSData20(ADOQueryMain, CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end;
  end
  else if RadioButton2.Checked = True then
  begin

    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Close;
        sql.Clear;
        sql.Add('select *,price=ROUND((money/qty),2) from (');
        SQL.Add('select SPrtCode,CustomerNoName,SUM(FHPS)PS,ISNULL(HZUnit,ORDERUNIT)UNIT, qty=(case when ISNULL(HZUnit,ORDERUNIT)=''公斤'' then SUM(FHQTY) else   sum(FHMS) end ) ,SUM( ');
        SQL.Add('PRICE*(case WHEN ISNULL(HZUnit,ORDERUNIT)=''公斤'' THEN fhqty ELSE FHMS END)    )money');
        SQL.Add(' from FHSQ_Sub  B INNER JOIN FHSQ_Main A ON A.FSID=B.FSID');
        SQL.Add('WHERE FHPS>0 and crtype=''销售出库''   and SQType=''订单'' and iSNULL(HZUnit,ORDERUNIT)<>'''' ');
        if chkdate.Checked then
        begin
          sql.Add(' and A.FHDATE>=' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', Begdate.DateTime))));
          sql.Add(' and A.FHDATE<' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', EndDate.DateTime + 1))));
        end;
        SQL.Add('GROUP BY CustomerNoName,SPrtCode,ISNULL(HZUnit,ORDERUNIT) )AA');
        sql.Add('order by [dbo].[getNum](SPrtCode)  ');
        Open;
      end;
      SCreateCDS20(ADOQueryMain, CDS_Main);
      SInitCDSData20(ADOQueryMain, CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end;
  end
  else if RadioButton3.Checked = True then
  begin

    try
      ADOQueryMain.DisableControls;
      with ADOQueryMain do
      begin
        Close;
        sql.Clear;
        sql.Add('select *,price=ROUND((money/qty),2) from (');
        SQL.Add('select SPrtCode,CustomerNoName,season,SUM(FHPS)PS,ISNULL(HZUnit,ORDERUNIT)UNIT, qty=(case when ISNULL(HZUnit,ORDERUNIT)=''公斤'' then SUM(FHQTY) else   sum(FHMS) end ) ,SUM( ');
        SQL.Add('PRICE*(case WHEN ISNULL(HZUnit,ORDERUNIT)=''公斤'' THEN fhqty ELSE FHMS END)    )money');
        SQL.Add(' from FHSQ_Sub  B INNER JOIN FHSQ_Main A ON A.FSID=B.FSID');
        SQL.Add('WHERE FHPS>0 and crtype=''销售出库''   and SQType=''订单'' and iSNULL(HZUnit,ORDERUNIT)<>'''' ');
        if chkdate.Checked then
        begin
          sql.Add(' and A.FHDATE>=' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', Begdate.DateTime))));
          sql.Add(' and A.FHDATE<' + quotedstr(trim(FormatDateTime('yyyy-MM-dd', EndDate.DateTime + 1))));
        end;
        SQL.Add('GROUP BY SPrtCode,CustomerNoName,season,ISNULL(HZUnit,ORDERUNIT) )AA');
        sql.Add('order by [dbo].[getNum](SPrtCode)  ');
        Open;
      end;
      SCreateCDS20(ADOQueryMain, CDS_Main);
      SInitCDSData20(ADOQueryMain, CDS_Main);
    finally
      ADOQueryMain.EnableControls;
    end;
  end;
end;

procedure TfrmDHXSTJList.TBRafreshClick(Sender: TObject);
begin
  BegDate.SetFocus;
  InitGrid();
end;

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

procedure TfrmDHXSTJList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(trim(self.Caption), Tv1, '大货销售统计表');
  Close;
end;

procedure TfrmDHXSTJList.FormShow(Sender: TObject);
begin

  ReadCxGrid(trim(self.Caption), Tv1, '大货销售统计表');
//  ReadCxGrid(trim(self.Caption), Tv1, '开卡管理');
  InitGrid();
end;

procedure TfrmDHXSTJList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then
    exit;
  TcxGridToExcel('坯布进出对照表', cxGrid1);
end;

procedure TfrmDHXSTJList.N1Click(Sender: TObject);
begin
  SelOKNo(CDS_Main, True);
end;

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

procedure TfrmDHXSTJList.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 TfrmDHXSTJList.PIHAOChange(Sender: TObject);
begin
  TBFind.Click;
end;

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

procedure TfrmDHXSTJList.FormDestroy(Sender: TObject);
begin
  frmDHXSTJList := nil;
end;

procedure TfrmDHXSTJList.Tv1CustomDrawCell(Sender: TcxCustomGridTableView; ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo; var ADone: Boolean);
var
  Fsunhao: double;
begin

  if AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('sunhao').Index] = null then
    Exit;
  Fsunhao := AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('sunhao').Index];
  if AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('ZPPS').Index] = null then
    Exit;
  if (AViewInfo.GridRecord.Values[TcxGridDBTableView(Sender).GetColumnByFieldName('ZPPS').Index]) > 0 then
  begin
      //损耗±2% 绿色、 损耗±2% ~ ±5% 黄色、 损耗大于±5% 红色
    if Fsunhao < 0 then
      Fsunhao := -Fsunhao;
    if Fsunhao <= 2 then
      ACanvas.Brush.Color := clMoneyGreen;
    if (Fsunhao > 2) and (Fsunhao <= 5) then
      ACanvas.Brush.Color := $00FFFF80;
    if Fsunhao > 5 then
      ACanvas.Brush.Color := $008080FF;
  end;
end;

procedure TfrmDHXSTJList.ToolButton1Click(Sender: TObject);
begin
  if CDS_Main.IsEmpty then
    Exit;

  try
    ADOQueryCmd.Connection.BeginTrans;
    CDS_Main.DisableControls;
    with CDS_Main do
    begin
      First;
      while not eof do
      begin
        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add(' exec P_UPdate_LCKJY ''' + Trim(CDS_Main.fieldbyname('SPID').AsString) + '''');
          execsql;
        end;
        CDS_Main.Next;
      end;
    end;
    ADOQueryCmd.Connection.CommitTrans;
    CDS_Main.EnableControls;
  except
    ADOQueryCmd.Connection.RollbackTrans;
    CDS_Main.EnableControls;
    Application.MessageBox('更新异常!', '提示', 0);
  end;
  InitGrid();
end;

procedure TfrmDHXSTJList.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 TfrmDHXSTJList.SPRTCODEChange(Sender: TObject);
begin
  TBFind.Click();
end;

procedure TfrmDHXSTJList.RadioButton1Click(Sender: TObject);
begin
  if RadioButton1.Checked = True then
  begin
    RadioButton2.Checked := False;
    RadioButton3.Checked := False;
    Tv1Column7.Visible := False;
    Tv1Column7.hidden := True;
    Tv1Column8.Visible := False;
    Tv1Column8.hidden := True;
    season.Text := '';
    CustomerNoName.Text := '';
    season.Enabled := False;
    CustomerNoName.Enabled := False;
  end;

  InitGrid();
end;

procedure TfrmDHXSTJList.RadioButton2Click(Sender: TObject);
begin
  if RadioButton2.Checked = True then
  begin
    RadioButton1.Checked := False;
    RadioButton3.Checked := False;
    Tv1Column7.Visible := true;
    Tv1Column7.hidden := False;

    Tv1Column8.Visible := False;
    Tv1Column8.hidden := True;
    season.Text := '';

    season.Enabled := False;
    CustomerNoName.Enabled := True;
  end;
  InitGrid();

end;

procedure TfrmDHXSTJList.RadioButton3Click(Sender: TObject);
begin
  if RadioButton3.Checked = True then
  begin
    RadioButton2.Checked := False;
    RadioButton1.Checked := False;
    Tv1Column7.Visible := true;
    Tv1Column7.hidden := False;

    Tv1Column8.Visible := true;
    Tv1Column8.hidden := False;

    season.Enabled := true;
    CustomerNoName.Enabled := True;
  end;
  InitGrid();
end;

procedure TfrmDHXSTJList.chkdateClick(Sender: TObject);
begin
  InitGrid();
end;

end.