unit U_PBCKSQDList;

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, cxPC;

type
  TfrmPBCKSQDList = class(TForm)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBFind: TToolButton;
    TBExport: TToolButton;
    TBClose: TToolButton;
    Panel1: TPanel;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    CDS_Main: TClientDataSet;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    C_CodeName: TEdit;
    FactoryName10: TEdit;
    CKOrdNo: TEdit;
    ToolButton1: TToolButton;
    cxTabControl1: TcxTabControl;
    cxGridPopupMenu1: TcxGridPopupMenu;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    cxGridDBColumn2: TcxGridDBColumn;
    cxGridDBColumn13: TcxGridDBColumn;
    cxGridDBColumn3: TcxGridDBColumn;
    cxGridDBColumn4: TcxGridDBColumn;
    cxGridDBColumn6: TcxGridDBColumn;
    cxGridDBColumn7: TcxGridDBColumn;
    cxGridDBColumn11: TcxGridDBColumn;
    cxGridDBColumn14: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    v0Column2: TcxGridDBColumn;
    v0Column3: TcxGridDBColumn;
    v0Column4: TcxGridDBColumn;
    Label6: TLabel;
    ConNo: TEdit;
    v1Column1: TcxGridDBColumn;
    v1Column2: TcxGridDBColumn;
    Label7: TLabel;
    C_Code: TEdit;
    Label8: TLabel;
    C_Spec: TEdit;
    ToolButton2: TToolButton;
    v1Column3: TcxGridDBColumn;
    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 N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure CKOrdNoChange(Sender: TObject);
    procedure SPSpecChange(Sender: TObject);
    procedure FactoryName10Change(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure cxTabControl1Change(Sender: TObject);
    procedure v0Column2PropertiesEditValueChanged(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
  private
    canshu1,canshu2:string;
    procedure InitGrid();
    function  YFData():Boolean;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmPBCKSQDList: TfrmPBCKSQDList;

implementation
uses
  U_DataLink,U_Fun,U_RTFun;

{$R *.dfm}
function TfrmPBCKSQDList.YFData():Boolean;
var
  CRID,OrdMainId,YFID,FComTaiTou,FCRID,FFactoryName:String;
begin
  Result:=False;
  with ADOQueryTemp do
  begin
    Close;
    SQL.Clear;
    sql.Add('select * from YF_Money_KC where FactoryName='+quotedstr(Trim(CDS_Main.fieldbyname('factoryName').AsString)));
    sql.Add(' and KCType=''KH'' ');
    Open;
  end;
  if ADOQueryTemp.IsEmpty=False then
  begin
    CRID:=ADOQueryTemp.fieldbyname('CRID').AsString;
  end
  else
  begin
    with ADOQueryTemp do
    begin
      Close;
      sql.Clear;
      sql.Add('select * from YF_Money_CRID');
      Open;
    end;
    if ADOQueryTemp.IsEmpty=False then
    begin
      with ADOQueryCmd do
      begin
        Close;
        sql.Clear;
        sql.Add('Update YF_Money_CRID set CRID=CRID+1');
        sql.Add('select * from YF_Money_CRID ');
        Open;
      end;
    end else
    begin
      with ADOQueryCmd do
      begin
        Close;
        sql.Clear;
        sql.Add('insert into YF_Money_CRID select 0 ');
        sql.Add('select * from YF_Money_CRID ');
        Open;
      end;
    end;

    CRID:=ADOQueryCmd.fieldbyname('CRID').AsString;
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      sql.Add('select * from YF_Money_KC where 1<>1');
      Open;
    end;
    with ADOQueryCmd do
    begin
      Append;
      FieldByName('CRID').Value:=StrToInt(CRID);
      FieldByName('FactoryName').Value:=Trim(CDS_Main.fieldbyname('factoryName').AsString);
      FieldByName('ZdyStr1').Value:='应收收';
      Post;
    end;
  end;
  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where FactoryName='+quotedstr(Trim(CDS_Main.fieldbyname('factoryName').AsString)));
    sql.Add(' and mainID='''+Trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
    Open;
  end;
  if ADOQueryTemp.IsEmpty then
  begin
    if GetLSNo(ADOQueryCmd,YFID,'YS','YF_Money_CR',3,1)=False then
    begin
      Application.MessageBox('取应收款最大号失败!','提示',0);
      Exit;
    end;
  end
  else
  begin
    YFID:=Trim(ADOQueryTemp.fieldbyname('YFID').AsString);
  end;
  with ADOQueryCmd do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from YF_Money_CR where YFID='''+Trim(YFID)+'''');
    Open;
  end;
  with ADOQueryCmd do
  begin
    if ADOQueryTemp.IsEmpty then
    begin
      Append ;
      FieldByName('SCQty').Value:=CDS_Main.fieldbyname('HZQty').AsFloat;
      FieldByName('SCPS').Value:=CDS_Main.fieldbyname('HZPS').AsFloat;
    end
    else
    Edit;
    FieldByName('YFID').Value:=Trim(YFID);
    FieldByName('YFTypeId').Value:=Trim(CDS_Main.fieldbyname('CKOrdNo').AsString);
    FieldByName('OrderNo').Value:=Trim(CDS_Main.fieldbyname('ConNo').AsString);
    FieldByName('CRID').Value:=StrToInt(CRID);
    FieldByName('Filler').Value:=Trim(DName);
    FieldByName('CRType').Value:='应收款登记';
    FieldByName('CRFlag').Value:='应收收';
    FieldByName('QtyFlag').Value:=1;
    FieldByName('FactoryName').Value:=Trim(CDS_Main.fieldbyname('factoryName').AsString);
    FieldByName('CRTime').Value:=CDS_Main.fieldbyname('CRTime').AsDateTime;
    FieldByName('Qty').Value:=CDS_Main.fieldbyname('HZQty').AsFloat;
    FieldByName('PS').Value:=CDS_Main.fieldbyname('HZPS').AsFloat;
    FieldByName('YFType').Value:='自动生成';
    FieldByName('Price').Value:=CDS_Main.fieldbyname('CHPrice').Value;
    FieldByName('money').Value:=CDS_Main.fieldbyname('money').Value;
    FieldByName('QtyUnit').Value:='Kg';
    FieldByName('P_CodeName').Value:=Trim(CDS_Main.fieldbyname('C_CodeName').AsString);
    FieldByName('P_Code').Value:=Trim(CDS_Main.fieldbyname('C_Code').AsString);
    FieldByName('P_Spec').Value:=Trim(CDS_Main.fieldbyname('C_Spec').AsString);
    FieldByName('YFName').Value:='销售金额';
    Post;
  end;
  Result:=True;
end;
procedure TfrmPBCKSQDList.FormDestroy(Sender: TObject);
begin
  frmPBCKSQDList:=nil;
end;

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

procedure TfrmPBCKSQDList.FormCreate(Sender: TObject);
begin
  //cxGrid1.Align:=alClient;
  EndDate.DateTime:=SGetServerDate10(ADOQueryTemp);
  BegDate.DateTime:=SGetServerDateMBeg(ADOQueryTemp);
  canshu1:=Trim(DParameters1);
  canshu2:=Trim(DParameters2);
end;

procedure TfrmPBCKSQDList.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add('');

      sql.Add('select A.* ,FactoryName10=Rtrim(dbo.getPinYin(KHName))+RTrim(KHName)');
      sql.Add(',B.ConNo,C.C_CodeName,C.C_Code,C.C_Spec,FactoryName=KHName');
      sql.Add(',Money=Cast(CHPrice*HZQty as decimal(18,2))');
      sql.Add(',CanKaoPrice=(select Top 1 Price from YF_Money_CR YS where YS.P_Code=C.C_Code and YS.FactoryName=A.KHName order by CRTime desc)');
      sql.Add('from(');
      sql.Add('select MainId,SubId,CKOrdNo,CRTime=Convert(varchar(10),CRTime,120),HZPS=Count(*),HZQty=Sum(KGQty),CHPrice,KHName');
      sql.Add(' from CK_PBCP_CR PB');
      sql.add('where CRTime>='''+Trim(FormatDateTime('yyyy-MM-dd',BegDate.DateTime))+''' ');
      sql.Add(' and CRTime<'''+Trim(FormatDateTime('yyyy-MM-dd',enddate.DateTime+1))+'''');
      SQL.Add(' and CRFlag=''出库'' ');
      sql.Add('Group by MainId,SubId,CKOrdNo,Convert(varchar(10),CRTime,120),CHPrice,KHName )A');
      Sql.add(' inner join Cloth_Main B on A.MainId=B.MainId');
      Sql.add(' inner join Cloth_Sub C on A.MainId=C.MainId');
      IF cxTabControl1.TabIndex=0 then
      begin
        SQL.Add(' and not exists(select maiNID from YF_Money_CR X where RTrim(isnull(X.YFTypeId,''''))=RTrim(isnull(A.CKOrdNo,''''))  ');
        sql.Add(' and RTrim(isnull(X.OrderNo,''''))=RTrim(isnull(B.ConNo,'''')) )');
      end
      else
      begin
        SQL.Add(' and  exists(select maiNID from YF_Money_CR X where RTrim(isnull(X.YFTypeId,''''))=RTrim(isnull(A.CKOrdNo,'''')) ');
        sql.Add(' and RTrim(isnull(X.OrderNo,''''))=RTrim(isnull(B.ConNo,'''')) )');
      end;
      Open;
      //ShowMessage(SQL.Text);
    end;
    SCreateCDS20(ADOQueryMain,CDS_Main);
    SInitCDSData20(ADOQueryMain,CDS_Main);
  finally
    ADOQueryMain.EnableControls;
  end;
end;

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

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

procedure TfrmPBCKSQDList.TBCloseClick(Sender: TObject);
begin
  WriteCxGrid(self.Caption,Tv1,'坯布仓库');
  Close;
end;

procedure TfrmPBCKSQDList.FormShow(Sender: TObject);
begin
  ReadCxGrid(self.Caption,Tv1,'坯布仓库');
 { if Trim(canshu2)='查看' then
  begin
    TBAdd.Visible:=False;
    TBDel.Visible:=False;
    TBEdit.Visible:=False;
  end else
  begin
    TBAdd.Visible:=True;
    TBDel.Visible:=True;
    TBEdit.Visible:=True;
  end;}
  //InitGrid();
end;

procedure TfrmPBCKSQDList.TBExportClick(Sender: TObject);
begin
  if ADOQueryMain.IsEmpty then exit;
  TcxGridToExcel('坯布出库列表',cxGrid1);
end;

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

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

procedure TfrmPBCKSQDList.CKOrdNoChange(Sender: TObject);
begin
  if Length(Trim(CKOrdNo.Text))<4 then
  begin
    if Trim(CKOrdNo.Text)<>'' then Exit;
  end;
  TBFind.Click;
end;

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

procedure TfrmPBCKSQDList.FactoryName10Change(Sender: TObject);
begin
  TBFind.Click;
end;

procedure TfrmPBCKSQDList.ToolButton1Click(Sender: TObject);
begin
  IF CDS_Main.IsEmpty then exit;
  if cxTabControl1.TabIndex<>0 then Exit;
  BegDate.SetFocus;
  IF not CDS_Main.Locate('ssel',true,[]) then
  begin
    application.MessageBox('没有选择数据！','提示信息',0);
    exit;
  end;
  if CDS_Main.Locate('SSel;CHPrice', VarArrayOf([True, Null]), [loPartialKey])=True then
  begin
    Application.MessageBox('单价不能为空!','提示',0);
    Exit;
  end;
  if Application.MessageBox('确定要生成应收款吗？','提示',32+4)<>IDYES then Exit;
  try
    ADOQueryCmd.Connection.BeginTrans;
    with CDS_Main do
    begin
      DisableControls;
      first;
      while Locate('SSel',True,[]) do
      begin
        IF not YFData() then
        begin
           ADOQueryCmd.Connection.RollbackTrans;
           application.MessageBox('生成应收款失败！','提示信息',0);
        end;
        CDS_Main.Delete;
      end;
      EnableControls;
    end;
    ADOQueryCmd.Connection.CommitTrans;
    //application.MessageBox('生成应收款成功！','提示信息');
  except
     ADOQueryCmd.Connection.RollbackTrans;
    application.MessageBox('生成应收款失败！','提示信息',0);

  end;
end;

procedure TfrmPBCKSQDList.cxTabControl1Change(Sender: TObject);
begin
  INITGrid();
end;

procedure TfrmPBCKSQDList.v0Column2PropertiesEditValueChanged(
  Sender: TObject);
var
  mvalue,FPrice,FQty:string;
begin
  mvalue:=TcxTextEdit(Sender).EditingValue;
  with CDS_Main do
  begin
    Edit;
    if Trim(mvalue)='' then
    begin
      FieldByName('CHPrice').Value:=Null;
    end else
    begin
      FieldByName('CHPrice').Value:=mvalue;
    end;
  end;
  FQty:=CDS_Main.fieldbyname('HZQty').AsString;
  if Trim(mvalue)='' then
  begin
    with CDS_Main do
    begin
      Edit;
      FieldByName('Money').Value:=Null;
    end;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update CK_PBCP_CR Set CHPrice=Null,Money=Null ');
      sql.Add(',EditTime=getdate(),Editer='''+Trim(DName)+'''');
      sql.Add('where CKOrdNo='''+Trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
      ExecSQL;
    end;
  end else
  begin
    with CDS_Main do
    begin
      Edit;
      FieldByName('Money').Value:=StrToFloat(FQty)*StrToFloat(mvalue);
    end;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update CK_PBCP_CR Set CHPrice='+mvalue);
      sql.Add('where CKOrdNo='''+Trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
      ExecSQL;
    end;
  end;
end;

procedure TfrmPBCKSQDList.ToolButton2Click(Sender: TObject);
begin
  IF CDS_Main.IsEmpty then exit;
  BegDate.SetFocus;
  if cxTabControl1.TabIndex<>1 then Exit;
  IF not CDS_Main.Locate('ssel',true,[]) then
  begin
    application.MessageBox('没有选择数据！','提示信息',0);
    exit;
  end;
  if Application.MessageBox('确定要撤销吗？','提示',32+4)<>IDYES then Exit;
  ADOQueryCmd.Connection.BeginTrans;
  try
    with CDS_Main do
    begin
      DisableControls;
      first;
      while Locate('SSel',True,[]) do
      begin
        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add(' delete YF_Money_CR where YFTypeId='''+Trim(CDS_Main.fieldbyname('CKOrdNo').AsString)+'''');
          sql.Add(' and FactoryName='''+Trim(CDS_Main.fieldbyname('KHName').AsString)+'''');
          sql.Add(' and OrderNo='''+Trim(CDS_Main.fieldbyname('ConNo').AsString)+'''');
          ExecSQL;
        end;
        CDS_Main.Delete;
      end;
      EnableControls;
    end;
    ADOQueryCmd.Connection.CommitTrans;
  except
     ADOQueryCmd.Connection.RollbackTrans;
    application.MessageBox('撤销异常！','提示信息',0);

  end;
end;

end.