mainunit.pas

晋业ERP扩展模块 独立的商品铺货计划及后确认进度

   DataForm.SQL.Close;
   DataForm.SQL.SQL.Clear;
   DataForm.SQL.SQL.Add('Delete From a_PlanList ');
   DataForm.SQL.SQL.Add('WHERE PLarea = '''+ CB50.Text +'''and PBNO ='''+ CB11.Text +'''');
   DataForm.SQL.ExecSQL;

   DataForm.T_PL.Close;
   DataForm.T_PL.Open;


   For i:=0 To LB01.Items.Count-1 do
   Begin
  //添加记录
   DataForm.SQL.Close;
   DataForm.SQL.SQL.Clear;
   DataForm.SQL.SQL.Add('INSERT INTO a_PlanList (PBNO,PLanNo,PLcustID,PLArea)');
   DataForm.SQL.SQL.Add('VALUES ('''+CB11.Text+''','''+CB11.Text+'-'+IntToStr(i+1)+''','''+DataForm.V_Cust.Lookup('客户名称',LB01.Items.Strings[i],'客户代码')+''','''+CB50.Text+''')');
   DataForm.SQL.ExecSQL;
   //
   end;
   end;
   DataForm.T_PL.Close;
   DataForm.T_PL.Open;
   DataForm.V_PL.Close;
   DataForm.V_PL.Open;
   DataForm.T_PB.Close;
   DataForm.T_PB.Open;


   DataForm.SQL_PB.Close;
   DataForm.SQL_PB.SQL.Clear;
   DataForm.SQL_PB.SQL.Add('SELECT ');
   DataForm.SQL_PB.SQL.Add('a_PlanBefordhan.PBNO AS 序号, PBProd AS 商品代码, PBName AS 商品名称, PlanType AS 计划类别,PBCust AS 家数,');
   DataForm.SQL_PB.SQL.Add('SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END)+SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 已计划数,');
   DataForm.SQL_PB.SQL.Add('PSDate AS 计划始日, PEDate AS 计划止日, SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 已完成数,SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 未完成数,SUM(CASE WHEN (PLstate = ''未计划已'') THEN 1 ELSE 0 END) AS 计划外完成数,');
   DataForm.SQL_PB.SQL.Add('PBCust - SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 预计未完成,');
   DataForm.SQL_PB.SQL.Add('PBCheck AS 计划状态, PBMemo AS 备注');
   DataForm.SQL_PB.SQL.Add('From a_PlanBefordhan LEFT JOIN  a_Planlist ON a_PlanBefordhan.PBNO = a_Planlist.PBNO');
   DataForm.SQL_PB.SQL.Add('  Where a_PlanBefordhan.PBCheck = '''+'未转单'+'''' );
   DataForm.SQL_PB.SQL.Add('Group By' );
   DataForm.SQL_PB.SQL.Add(' a_PlanBefordhan.PBNO,PBProd,PBName,PlanType,PBCust,PSDate,PEDate,PBCheck,PBMemo' );
   DataForm.SQL_PB.SQL.Add('ORDER BY PEDate');
   DataForm.SQL_PB.Open;
  SB31.Enabled := False;
  SB32.Enabled := False;
  SB33.Enabled := False;
  SB34.Enabled := False;
  LB00.Enabled := False;
  LB01.Enabled := False;

end;

procedure TMainForm.SB44Click(Sender: TObject);
begin
  SB45.Enabled := True;
  SB46.Enabled := True;
  SB47.Enabled := True;
  CB50.Enabled := True;
  CB11.Enabled := True;
  SB31.Enabled := True;
  SB32.Enabled := True;
  SB33.Enabled := True;
  SB34.Enabled := True;
  if CB50.Text <> '' THen
  BEgin
  LB00.Enabled := True;
  LB01.Enabled := True;
  end;

  DDL := 'CHAN';

end;

procedure TMainForm.SB45Click(Sender: TObject);
begin
  IF Application.MessageBox('确定删除该商品在本区的计划信息吗?' , '确认', mb_yesno + mb_iconquestion) = idyes then
  Begin
   DataForm.SQL.Close;
   DataForm.SQL.SQL.Clear;
   DataForm.SQL.SQL.Add('Delete From a_PlanList ');
   DataForm.SQL.SQL.Add('WHERE PLarea = '''+ CB50.Text +'''and PBNO ='''+ CB11.Text +'''');
   DataForm.SQL.ExecSQL;
  END ;
   DataForm.T_PL.Close;
   DataForm.T_PL.Open;
   DataForm.V_PL.Close;
   DataForm.V_PL.Open;
   DataForm.T_PB.Close;
   DataForm.T_PB.Open;


   DataForm.SQL_PB.Close;
   DataForm.SQL_PB.SQL.Clear;
   DataForm.SQL_PB.SQL.Add('SELECT ');
   DataForm.SQL_PB.SQL.Add('a_PlanBefordhan.PBNO AS 序号, PBProd AS 商品代码, PBName AS 商品名称, PlanType AS 计划类别,PBCust AS 家数,');
   DataForm.SQL_PB.SQL.Add('SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END)+SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 已计划数,');
   DataForm.SQL_PB.SQL.Add('PSDate AS 计划始日, PEDate AS 计划止日, SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 已完成数,SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 未完成数,SUM(CASE WHEN (PLstate = ''未计划已'') THEN 1 ELSE 0 END) AS 计划外完成数,');
   DataForm.SQL_PB.SQL.Add('PBCust - SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 预计未完成,');
   DataForm.SQL_PB.SQL.Add('PBCheck AS 计划状态, PBMemo AS 备注');
   DataForm.SQL_PB.SQL.Add('From a_PlanBefordhan LEFT JOIN  a_Planlist ON a_PlanBefordhan.PBNO = a_Planlist.PBNO');
   DataForm.SQL_PB.SQL.Add('  Where a_PlanBefordhan.PBCheck = '''+'未转单'+'''' );
   DataForm.SQL_PB.SQL.Add('Group By' );
   DataForm.SQL_PB.SQL.Add(' a_PlanBefordhan.PBNO,PBProd,PBName,PlanType,PBCust,PSDate,PEDate,PBCheck,PBMemo' );
   DataForm.SQL_PB.SQL.Add('ORDER BY PEDate');
   DataForm.SQL_PB.Open;

end;

procedure TMainForm.cxButton1Click(Sender: TObject);
Var
 i: Integer;
 SD,ED:string;
begin
   DataForm.T_PB.First;
   While NOT DataForm.T_PB.Eof Do
   Begin
       SD:=DATETOSTR(DataForm.T_PB.FieldValues['PSDATE']);
       ED:=DATETOSTR(DataForm.T_PB.FieldValues['PEDATE']);

       DataForm.SQL_TEMP.Close;
       DataForm.SQL_TEMP.SQL.Clear;
       DataForm.SQL_TEMP.SQL.Add('Select jxc_dj_2.kh_Kmdm AS 客户代码, jxc_dj_2.yyyymmdd AS 日期, jxc_dj_2.sl AS 数量 From jxc_dj_2 ');
       DataForm.SQL_TEMP.SQL.Add('WHERE jxc_dj_2.sp_kmdm = '''+ DataForm.T_PB.FieldValues['PBPROD'] +'''');
       DataForm.SQL_TEMP.SQL.Add(' AND jxc_dj_2.yyyymmdd >='''+ COPY(SD,1,4)+ COPY(SD,6,2)+ COPY(SD,9,2) +'''');
       DataForm.SQL_TEMP.SQL.Add(' AND jxc_dj_2.yyyymmdd <='''+ COPY(ED,1,4)+ COPY(ED,6,2)+ COPY(ED,9,2) +'''');
       if DataForm.T_PB.FieldValues['PlanType'] = '商品调回' Then
           Begin
           DataForm.SQL_TEMP.SQL.Add(' AND jxc_dj_2.CRKLB in (''B04'')')
           End Else Begin
           DataForm.SQL_TEMP.SQL.Add(' AND jxc_dj_2.CRKLB in (''B01'')');
           End;
       DataForm.SQL_TEMP.Open;

//   SHOWMESSaGE('OK1!');

       DataForm.SQL.Close;
       DataForm.SQL.SQL.Clear;
       DataForm.SQL.SQL.Add('Select PLCustID AS 客户代码,PLState AS 状态,PLmemo AS 备注 From a_Planlist ');
       DataForm.SQL.SQL.Add('WHERE PBNO = '''+ INTTOSTR(DataForm.T_PB.FieldValues['PBNO']) +'''');
       DataForm.SQL.Open;

//   SHOWMESSaGE('OK2!');

      While NOT DataForm.SQL_TEMP.Eof Do
      Begin
//   SHOWMESSaGE('OK3!');
      DataForm.SQL.First;

      For i:=1 to  DataForm.SQL.RecordCount Do
      Begin
          IF DataForm.SQL_TEMP.FieldValues['客户代码'] = DataForm.SQL.FieldValues['客户代码'] Then
              Begin
              if CB41.State = cbchecked then
                  Begin
                     DataForm.SQL.Edit;
                     DataForm.SQL.FieldValues['状态'] :='已处理';
                         DataForm.SQL.FieldValues['备注'] :='执行日期: '+INTTOSTR(DataForm.SQL_TEMP.FieldValues['日期'])+'  数量: ' +INTTOSTR(DataForm.SQL_TEMP.FieldValues['数量']);
                     DataForm.SQL.Post;
                  end
                  Else
                  Begin
                     if DataForm.SQL.FieldValues['状态']='未处理' Then
                         Begin
                         DataForm.SQL.Edit;
                         DataForm.SQL.FieldValues['状态'] :='已处理';
                         DataForm.SQL.FieldValues['备注'] :='执行日期: '+INTTOSTR(DataForm.SQL_TEMP.FieldValues['日期'])+'  数量: ' +INTTOSTR(DataForm.SQL_TEMP.FieldValues['数量']);
                         DataForm.SQL.Post;
                         End;
                  End;
//              SHOWMESSaGE('OK6.5!');
              break;
              End
              Else
              Begin
//              SHOWMESSaGE('OK7!');
              DataForm.SQL.Next;

              IF DataForm.SQL.Eof = True  Then
                  Begin
                  DataForm.T_PL.Append;
                  DataForm.T_PL.FieldValues['PBNO'] :=DataForm.T_PB.FieldValues['PBNO'];
                  DataForm.T_PL.FieldValues['PLanNo'] :=DataForm.T_PB.FieldValues['PBPROD']+'-A'+IntToStr(i);
                  DataForm.T_PL.FieldValues['PLcustID'] :=DataForm.SQL_TEMP.FieldValues['客户代码'];
                  DataForm.T_PL.FieldValues['PLArea'] :=DataForm.V_Cust.Lookup('客户代码',DataForm.SQL_TEMP.FieldValues['客户代码'],'区域');
                  DataForm.T_PL.FieldValues['PLstate'] :='未计划已处理';
                  DataForm.T_PL.FieldValues['PLmemo'] :='执行日期: '+INTTOSTR(DataForm.SQL_TEMP.FieldValues['日期'])+'  数量: ' +INTTOSTR(DataForm.SQL_TEMP.FieldValues['数量']);
                  DataForm.T_PL.Post;
             end;
         end;


     end;
   DataForm.SQL_TEMP.Next;
   end;

//   SHOWMESSaGE('OK8!');

   DataForm.T_PB.Next;
   end;
//   SHOWMESSaGE('OK9!');

         DataForm.T_PL.Close;
         DataForm.T_PL.Open;
         DataForm.V_PL.Close;
         DataForm.V_PL.Open;
         DataForm.T_PB.Close;
         DataForm.T_PB.Open;

   DataForm.SQL_PB.Close;
   DataForm.SQL_PB.SQL.Clear;
   DataForm.SQL_PB.SQL.Add('SELECT ');
   DataForm.SQL_PB.SQL.Add('a_PlanBefordhan.PBNO AS 序号, PBProd AS 商品代码, PBName AS 商品名称, PlanType AS 计划类别,PBCust AS 家数,');
   DataForm.SQL_PB.SQL.Add('SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END)+SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 已计划数,');
   DataForm.SQL_PB.SQL.Add('PSDate AS 计划始日, PEDate AS 计划止日, SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 已完成数,SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 未完成数,SUM(CASE WHEN (PLstate = ''未计划已'') THEN 1 ELSE 0 END) AS 计划外完成数,');
   DataForm.SQL_PB.SQL.Add('PBCust - SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 预计未完成,');
   DataForm.SQL_PB.SQL.Add('PBCheck AS 计划状态, PBMemo AS 备注');
   DataForm.SQL_PB.SQL.Add('From a_PlanBefordhan LEFT JOIN  a_Planlist ON a_PlanBefordhan.PBNO = a_Planlist.PBNO');
   DataForm.SQL_PB.SQL.Add('  Where a_PlanBefordhan.PBCheck = '''+'未转单'+'''' );
   DataForm.SQL_PB.SQL.Add('Group By' );
   DataForm.SQL_PB.SQL.Add(' a_PlanBefordhan.PBNO,PBProd,PBName,PlanType,PBCust,PSDate,PEDate,PBCheck,PBMemo' );
   DataForm.SQL_PB.SQL.Add('ORDER BY PEDate');
   DataForm.SQL_PB.Open;
   SHOWMESSaGE('OK!');
end;

procedure TMainForm.SB47Click(Sender: TObject);
begin
  SB45.Enabled := False;
  SB46.Enabled := False;
  SB47.Enabled := False;
  LB00.Enabled := False;
  LB01.Enabled := False;
  SB31.Enabled := False;
  SB32.Enabled := False;
  SB33.Enabled := False;
  SB34.Enabled := False;

end;

procedure TMainForm.cxDBTextEdit1PropertiesChange(Sender: TObject);
begin
   DataForm.SQL_TEMPA.Close;
   DataForm.SQL_TEMPA.SQL.Clear;
   DataForm.SQL_TEMPA.SQL.Add('SELECT ');
   DataForm.SQL_TEMPA.SQL.Add('PLAREA AS 区域,');
   DataForm.SQL_TEMPA.SQL.Add('PBCust AS 预计划家数,');
   DataForm.SQL_TEMPA.SQL.Add('SUM(CASE WHEN ((PLstate = ''已处理'') OR (PLstate = ''未处理'')) THEN 1 ELSE 0 END) AS 实际计划数,');
   DataForm.SQL_TEMPA.SQL.Add('SUM(CASE WHEN (PLstate = ''已处理'') THEN 1 ELSE 0 END) AS 计划已完成,' );
   DataForm.SQL_TEMPA.SQL.Add('SUM(CASE WHEN (PLstate = ''未处理'') THEN 1 ELSE 0 END) AS 计划未完成,' );
   DataForm.SQL_TEMPA.SQL.Add('SUM(CASE WHEN (PLstate = ''未计划已'') THEN 1 ELSE 0 END) AS 计划外完成' );
   DataForm.SQL_TEMPA.SQL.Add('FROM a_PlanBefordhan LEFT JOIN  a_Planlist ON a_PlanBefordhan.PBNO = a_Planlist.PBNO');
   DataForm.SQL_TEMPA.SQL.Add('WHERE a_Planlist.PBNO = '''+ DTE01.Text +'''');
   DataForm.SQL_TEMPA.SQL.Add('Group By' );
   DataForm.SQL_TEMPA.SQL.Add(' PLAREA,PBCust' );
   DataForm.SQL_TEMPA.SQL.Add('ORDER BY PLAREA');
   DataForm.SQL_TEMPA.Open;

end;

procedure TMainForm.DTE02PropertiesChange(Sender: TObject);
begin
   IF DTE02.Text <>'' THen
   Begin
   DataForm.SQL_PL.Close;
   DataForm.SQL_PL.SQL.Clear;
   DataForm.SQL_PL.SQL.Add('SELECT * FROM Z_PL');
   DataForm.SQL_PL.SQL.Add('WHERE 序号 = '''+ DTE01.Text +''' AND  区域 = '''+ DTE02.Text +'''');
   DataForm.SQL_PL.Open;
   DBG04.Columns.Items[0].Width := 36;
   DBG04.Columns.Items[1].Width := 60;
   DBG04.Columns.Items[2].Width := 52;
   DBG04.Columns.Items[3].Width := 46;
   DBG04.Columns.Items[4].Width := 46;
   DBG04.Columns.Items[5].Width := 46;
   DBG04.Columns.Items[6].Width := 200;
   end;
end;

procedure TMainForm.cxComboBox1PropertiesChange(Sender: TObject);
begin
  if CB60.Text <> '' Then
  Begin
  DataForm.SQL_CM.Close;
  DataForm.SQL_CM.SQL.Clear;
  DataForm.SQL_CM.SQL.Add('SELECT dbo.Z_CUST.客户名称 ');
  DataForm.SQL_CM.SQL.Add('FROM dbo.Z_CUST ');
  DataForm.SQL_CM.SQL.Add('WHERE (dbo.Z_CUST.区域 = '''+ CB60.Text +''')');
  DataForm.SQL_CM.Open;

   DataForm.SQL_CMM.Close;
   DataForm.SQL_CMM.SQL.Clear;
   DataForm.SQL_CMM.SQL.Add('SELECT * FROM Z_PLB');
   DataForm.SQL_CMM.SQL.Add('WHERE 客户名称= '''+ DBE03.Text +'''');
   DataForm.SQL_CMM.Open;
   DBG06.Columns.Items[0].Width := 36;
   DBG06.Columns.Items[1].Width := 64;
   DBG06.Columns.Items[2].Width := 120;
   DBG06.Columns.Items[3].Width := 64;
   DBG06.Columns.Items[4].Width := 64;
   DBG06.Columns.Items[5].Width := 64;
   DBG06.Columns.Items[6].Width := 72;
   DBG06.Columns.Items[7].Width := 72;
   DBG06.Columns.Items[8].Width := 64;

  end;
end;

procedure TMainForm.DBE03Change(Sender: TObject);
begin
   DataForm.SQL_CMM.Close;
   DataForm.SQL_CMM.SQL.Clear;
   DataForm.SQL_CMM.SQL.Add('SELECT * FROM Z_PLB');
   DataForm.SQL_CMM.SQL.Add('WHERE 客户名称= '''+ DBE03.Text +'''');
   DataForm.SQL_CMM.Open;
   DBG06.Columns.Items[0].Width := 36;
   DBG06.Columns.Items[1].Width := 64;
   DBG06.Columns.Items[2].Width := 120;
   DBG06.Columns.Items[3].Width := 64;
   DBG06.Columns.Items[4].Width := 64;
   DBG06.Columns.Items[5].Width := 64;
   DBG06.Columns.Items[6].Width := 72;
   DBG06.Columns.Items[7].Width := 72;
   DBG06.Columns.Items[8].Width := 64;
end;

procedure TMainForm.CB80Click(Sender: TObject);
begin
  IF CB80.State = cbChecked then
  Begin
  DataForm.SQL_CMM.Filter := '计划状态 = ''未处理''';
  DataForm.SQL_CMM.Filtered := True;
  end Else Begin
  DataForm.SQL_CMM.Filter := '计划状态 <> ''未处理''';
  DataForm.SQL_CMM.Filtered := True;
  End;

end;

procedure TMainForm.BT80Click(Sender: TObject);
VAR
  ExcelGL: OleVariant;
  G01,G02:integer;
begin
  try
    ExcelGL := CreateOLEObject('Excel.Application');
  except
    Application.MessageBox('Excel is Not Install! ', '????', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
    Exit;
  end;
  ExcelGL.Visible := False;
  ExcelGL.WorkBooks.Add;
  G01:=2;
  G02:=0;

  if DataForm.SQL_CMM.FieldCount > 65536 then showmessage('Data is to big !');
    while G02 < DataForm.SQL_CMM.FieldCount do
    begin
   ExcelGL.WorkSheets[1].Cells[1,G02+1].Value :=DBG01.Columns[G02].Title.Caption ;
    G02:=G02+1;
    end;
    G02:=0;
 while NOT DataForm.SQL_CMM.Eof  do
  begin
    while G02 < DataForm.SQL_CMM.FieldCount do
    begin
    if DataForm.SQL_CMM.Fields[G02].DataType = ftString Then ExcelGL.WorkSheets[1].Cells[G01,G02+1].Value :=''''+DataForm.SQL_CMM.Fields[G02].AsString
    Else ExcelGL.WorkSheets[1].Cells[G01,G02+1].Value :=DataForm.SQL_CMM.Fields[G02].AsString;
    G02:=G02+1;
    end;
   DataForm.SQL_CMM.Next;
   G01:=G01+1;
   G02:=0;
   end;
  ExcelGL.Visible := True;

end;

end.