unit MAIN;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ExtCtrls, Menus, ComCtrls, Grids, DBGrids,IniFiles,
  Spin, DB, ADODB;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    StaticText1: TStaticText;
    StaticText2: TStaticText;
    Label4: TLabel;
    MSpinEdit: TSpinEdit;
    WSpinEdit: TSpinEdit;
    Edit1: TEdit;
    adoInSert: TADOQuery;
    BitBtn1: TBitBtn;
    ComboBox1: TComboBox;
    Label5: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure WSpinEditChange(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);

  private
    { Private declarations }
    function GetSpecifiedWeekFirstDayOfYear(AYear,AWeekNo: integer): TDate;
    procedure YMD;
    procedure Chk_FCMSDT;
    procedure Chk_panel1;
    procedure Index;
//=Delet =============================
    procedure Del_A301; //即時一般銷退     (如有先Delet)
    procedure Pub_DB_CHECK; //即時定期存款     (如有先Delet)
  public
    { Public declarations }
  published    //以下為Batch動作
    function Qry_Batch(S : String): Boolean;
//=模具訂單===============
    procedure W101;  //週數量
    procedure W102;  //週金額
    procedure M101;  //月數量
    procedure M102;  //月金額
    procedure Y101;  //年數量
    procedure Y102;  //年金額
//=正式訂單=======================
    procedure W103;  //週金額
    procedure M103;  //月金額
    procedure Y103;  //年金額
//=一般銷貨=======================
    procedure W301;  //週銷貨
    procedure M301;  //月銷貨
    procedure Y301;  //年銷貨
//=一般銷退=======================
    procedure A301; //即時一般銷退_Insert (如有先Delet)

  end;

var
  Form1: TForm1;
  dt: TDate;
  iMB001 : String;
  Year,Month,Day,m_iYear,m_iWeek : Word;  //年,月,日,m_iYear民國年
  m_sYMD,m_sYear,m_sYMD_w0,m_sYMD_w30,m_sYMD_w31,m_sYMD_w60,m_sYMD_w61,m_sYMD_w90,m_sYMD_w91,m_sYMD_w120,m_sYMD_w121,m_sYMD_w150 :string;        //當天時間轉換字串
  m_iChkxx005 : Word;        //0 為查詢是否有資料 ,1為寫入StringGrid1
  m_sXX005 : String;      //xx005業務代號
implementation

uses DM;

{$R *.dfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  iMB001 :='C_FST';
  Pub_DB_CHECK;
  YMD;
  Chk_panel1;
  Index;

end;

procedure TForm1.Chk_panel1;
begin
  if m_iWeek-1 <>0 then begin
  //年
    Edit1.Text := IntToStr(Year);

  //月
    MSpinEdit.Value := Month;
    MSpinEdit.MinValue := 1;
    MSpinEdit.MaxValue := Month;
  //日
    WSpinEdit.Value := m_iWeek-1;
    WSpinEdit.MinValue := 1;
    WSpinEdit.MaxValue := m_iWeek-1;
  end else Begin
  //年
    Edit1.Text := IntToStr(Year-1);
  //月
    MSpinEdit.Value := 12;
    MSpinEdit.MinValue := 1;
    MSpinEdit.MaxValue := 12;
  //日
    WSpinEdit.Value := 53;
    WSpinEdit.MinValue := 1;
    WSpinEdit.MaxValue := 53;
  end;
  m_iYear := StrToInt(Edit1.Text)-1911;
  m_sYear := IntToStr(m_iYear);
  WSpinEditChange(Self);
end;

procedure TForm1.YMD;
begin
  DecodeDate(Now,Year,Month,Day);
  m_sYMD := FormatDateTime('YYYYMMDD',NOW);
  Chk_FCMSDT;
end;


procedure TForm1.WSpinEditChange(Sender: TObject);
var
  dt: TDate;
begin
  dt := GetSpecifiedWeekFirstDayOfYear(StrToInt(Trim(Edit1.Text)),WSpinEdit.Value);
  StaticText1.Caption := DateToStr(dt);
  StaticText2.Caption := DateToStr(dt+6);
  m_sYMD_w0 := FormatDateTime('YYYYMMDD',dt+6);
  m_sYMD_w30 := FormatDateTime('YYYYMMDD',dt+6-30);
  m_sYMD_w31 := FormatDateTime('YYYYMMDD',dt+6-31);
  m_sYMD_w60 := FormatDateTime('YYYYMMDD',dt+6-60);
  m_sYMD_w61 := FormatDateTime('YYYYMMDD',dt+6-61);
  m_sYMD_w90 := FormatDateTime('YYYYMMDD',dt+6-90);
  m_sYMD_w91 := FormatDateTime('YYYYMMDD',dt+6-91);
  m_sYMD_w120:= FormatDateTime('YYYYMMDD',dt+6-120);
  m_sYMD_w121:= FormatDateTime('YYYYMMDD',dt+6-121);
  m_sYMD_w150:= FormatDateTime('YYYYMMDD',dt+6-150);
  MSpinEdit.Value := StrToInt(copy(m_sYMD_w0,5,2));
end;

procedure TForm1.M101;
var
  SQL : String;
  i : Word;
begin
  SHOWMESSAGE('M101');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''M101'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Month XX003,SUM(TC031) XX004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Month , convert(datetime,TC003,112)) ='''+intToStr(MSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
      try
        adoInSert.SQL.Text := SQL;
        adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
        adoInSert.ExecSQL;

      except
        Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
        exit;
      end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''M101'','''+Trim(Edit1.Text)+''',:Month)';
      try
        adoInSert.SQL.Text := SQL;
        adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
        adoInSert.ExecSQL;

      except
        Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
        exit;
      end;

end;

procedure TForm1.M102;
var
  SQL : String;
begin
  SHOWMESSAGE('M102');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''M102'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Month XX003,ROUND(SUM((TC029+TC030)*TC009),2) XC004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Month , convert(datetime,TC003,112)) ='''+intToStr(MSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''M102'','''+Trim(Edit1.Text)+''',:Month)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

Function TForm1.Qry_Batch(S : String): Boolean;
Var
  StrMain :  String;
  QryBatch : TADOQuery;
begin
  QryBatch := TADOQuery.Create(Application);
  QryBatch.Connection := FDM.PUB_DB;
  StrMain := 'Select XY001 FROM FADMXY'+
            ' Where COMPANY = '''+iMB001+''''+
            ' AND XY001 = '''+S+''''+
            ' AND XY002 = :XY002'+
            ' AND XY003 = :XY003';
  QryBatch.SQL.Text := StrMain;
  QryBatch.Prepared := True;
  if Copy(S,1,1)='Y' then Begin
    QryBatch.Parameters.ParamByName('XY002').Value := Trim(Edit1.Text);
    QryBatch.Parameters.ParamByName('XY003').Value :=IntToStr(StrToInt(Trim(Edit1.Text))-1911);
  end else if Copy(S,1,1)='M' then Begin
    QryBatch.Parameters.ParamByName('XY002').Value := Trim(Edit1.Text);
    QryBatch.Parameters.ParamByName('XY003').Value := MSpinEdit.Value ;
  End else if Copy(S,1,1)='W' then Begin
    QryBatch.Parameters.ParamByName('XY002').Value := Trim(Edit1.Text);
    QryBatch.Parameters.ParamByName('XY003').Value := WSpinEdit.Value;
  End else if Copy(S,1,1)='A' then Begin
    QryBatch.Parameters.ParamByName('XY002').Value := '0';
    QryBatch.Parameters.ParamByName('XY003').Value := '0';
  End;
  QryBatch.open;
  if QryBatch.RecordCount = 0 then  Result := True else Result := False;
end;

procedure TForm1.W101;
var
  StrMain,SQL : String;
begin
  SHOWMESSAGE('W101');

  StrMain :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''W101'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Week XX003,SUM(TC031) XX004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( WEEK , convert(datetime,TC003,112)) ='''+intToStr(WSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := StrMain;
     adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''W101'','''+Trim(Edit1.Text)+''',:Week)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.W102;
var
  SQL : String;
begin
  SHOWMESSAGE('W102');

 SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''W102'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Week XX003,ROUND(SUM((TC029+TC030)*TC009),2) XC004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Week , convert(datetime,TC003,112)) ='''+intToStr(WSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''W102'','''+Trim(Edit1.Text)+''',:Week)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.Y101;
var
  SQL : String;
begin
  SHOWMESSAGE('Y101');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''Y101'' XX001,'''+Trim(Edit1.Text)+''' XX002,'''+m_sYear+''' XX003,SUM(TC031) XX004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''Y101'','''+Trim(Edit1.Text)+''','''+m_sYear+''')';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.Y102;
var
  SQL : String;
begin
  SHOWMESSAGE('Y102');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''Y102'' XX001,'''+Trim(Edit1.Text)+''' XX002,'''+m_sYear+''' XX003,ROUND(SUM((TC029+TC030)*TC009),2) XX004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''3221'',''5221'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''Y102'','''+Trim(Edit1.Text)+''','''+m_sYear+''')';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.W103;
var
  SQL : String;
begin
  SHOWMESSAGE('W103');
 SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''W103'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Week XX003,ROUND(SUM((TC029+TC030)*TC009),2) XC004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''2221'',''222U'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Week , convert(datetime,TC003,112)) ='''+intToStr(WSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;
  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''W103'','''+Trim(Edit1.Text)+''',:Week)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.M103;
var
  SQL : String;
begin
  SHOWMESSAGE('M103');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''M103'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Month XX003,ROUND(SUM((TC029+TC030)*TC009),2) XC004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''2221'',''222U'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Month , convert(datetime,TC003,112)) ='''+intToStr(MSpinEdit.Value)+''' '+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''M103'','''+Trim(Edit1.Text)+''',:Month)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.Y103;
var
  SQL : String;
begin
  SHOWMESSAGE('Y103');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''Y103'' XX001,'''+Trim(Edit1.Text)+''' XX002,'''+m_sYear+''' XX003,ROUND(SUM((TC029+TC030)*TC009),2) XX004,TC006 XX005,'''' XX006'+
        ' FROM COPTC'+
        ' WHERE  TC027=''Y'''+
        ' AND TC001 IN (''2221'',''222U'')'+
        ' AND DATEPART ( YEAR , convert(datetime,TC003,112)) ='''+Trim(Edit1.Text)+''''+
        ' GROUP BY TC006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''Y103'','''+Trim(Edit1.Text)+''','''+m_sYear+''')';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.W301;
var
  SQL : String;
begin
 SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''W301'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Week XX003,ROUND(SUM(TG045+TG046),2) XC004,TG006 XX005,'''' XX006'+
        ' FROM COPTG'+
        ' WHERE TG023 = ''Y'''+
        ' AND TG004 NOT IN ( ''C000'' ,''C0005'' ,''C0007'' ,''C003'' ,''C015'',''C016'',''C037'' ,''C059'' ,''C108'' ,''C122'' ,''C158'' ,''C163'' ,''C190'',''C198'' ,''C903'' ,''C997'' ,''C998'')'+
        ' AND TG004 NOT LIKE ''A%'''+
        ' AND DATEPART ( YEAR , convert(datetime,TG003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Week , convert(datetime,TG003,112)) ='''+intToStr(WSpinEdit.Value)+''' '+
        ' GROUP BY TG006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
     adoInSert.ExecSQL;

   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SHOWMESSAGE('W301');
  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''W301'','''+Trim(Edit1.Text)+''',:Week)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Week').Value := WSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.M301;
var
  SQL : String;
begin
  SHOWMESSAGE('M301');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''M301'' XX001,'''+Trim(Edit1.Text)+''' XX002,:Month XX003,ROUND(SUM(TG045+TG046),2) XX004,TG006 XX005,'''' XX006'+
        ' FROM COPTG'+
        ' WHERE TG023 = ''Y'''+
        ' AND TG004 NOT IN ( ''C000'' ,''C0005'' ,''C0007'' ,''C003'' ,''C015'',''C016'',''C037'' ,''C059'' ,''C108'' ,''C122'' ,''C158'' ,''C163'' ,''C190'',''C198'' ,''C903'' ,''C997'' ,''C998'')'+
        ' AND TG004 NOT LIKE ''A%'''+
        ' AND DATEPART ( YEAR , convert(datetime,TG003,112)) ='''+Trim(Edit1.Text)+''''+
        ' AND DATEPART ( Month , convert(datetime,TG003,112)) ='''+intToStr(MSpinEdit.Value)+''' '+
        ' GROUP BY TG006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''M301'','''+Trim(Edit1.Text)+''',:Month)';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.Parameters.ParamByName('Month').Value := MSpinEdit.Value;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.Y301;
var
  SQL : String;
begin
  SHOWMESSAGE('Y301');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''1'' XX000,''Y301'' XX001,'''+Trim(Edit1.Text)+''' XX002,'''+m_sYear+''' XX003,ROUND(SUM(TG045+TG046),2) XX004,TG006 XX005,'''' XX006'+
        ' FROM COPTG'+
        ' WHERE TG023 = ''Y'''+
        ' AND TG004 NOT IN ( ''C000'' ,''C0005'' ,''C0007'' ,''C003'' ,''C015'',''C016'',''C037'' ,''C059'' ,''C108'' ,''C122'' ,''C158'' ,''C163'' ,''C190'',''C198'' ,''C903'' ,''C997'' ,''C998'')'+
        ' AND TG004 NOT LIKE ''A%'''+
        ' AND DATEPART ( YEAR , convert(datetime,TG003,112)) ='''+Trim(Edit1.Text)+''''+
        ' GROUP BY TG006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''1'',''Y301'','''+Trim(Edit1.Text)+''','''+m_sYear+''')';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.Del_A301;
var
  SQL : String;
  Del_A401 : TADOQuery;
begin
  Del_A401 := TADOQuery.Create(Application);
  Del_A401.Connection := FDM.PUB_DB;
  SQL := 'Delete from FADMXX'+
        ' Where XX000=''0'' AND XX002=''0'' AND XX003=0 '+
        ' AND XX001=''A301''';
  try
    Del_A401.SQL.Text := SQL;
    Del_A401.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
  SQL := 'Delete from FADMXY'+
        ' Where XY000=''0'' AND XY002=''0'' AND XY003=''0'' '+
        ' AND XY001=''A301''';
  try
    Del_A401.SQL.Text := SQL;
    Del_A401.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.A301;
var
  SQL : String;
begin
  SHOWMESSAGE('A301');
  SQL :='Insert into FADMXX'+
        ' SELECT '''+iMB001+''' COMPANY,''0'' XX000,''A301'' XX001,''0'' XX002,0 XX003,ROUND(SUM(TI037+TI038),2) XX004,TI006 XX005,'''' XX006'+
        ' FROM COPTI'+
        ' WHERE TI019 <> ''V'''+
        ' AND TI004 NOT IN ( ''C000'' ,''C0005'' ,''C0007'' ,''C003'' ,''C015'',''C016'',''C037'' ,''C059'' ,''C108'' ,''C122'' ,''C158'' ,''C163'' ,''C190'',''C198'' ,''C903'' ,''C997'' ,''C998'')'+
        ' AND TI004 NOT LIKE ''A%'''+
        ' AND TI003 <= '''+m_sYMD_w0+''' '+
        ' GROUP BY TI006';
   try
     adoInSert.SQL.Text := SQL;
     adoInSert.ExecSQL;
   except
     Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
     exit;
   end;

  SQL :='Insert into FADMXY(COMPANY,XY000,XY001,XY002,XY003)'+
        ' Values ('''+iMB001+''',''0'',''A301'',''0'',''0'')';
  try
    adoInSert.SQL.Text := SQL;
    adoInSert.ExecSQL;
  except
    Application.MessageBox('資料發生錯誤,資料無法新增','錯誤',IDOK);
    exit;
  end;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
var
  i : Word;
  S,S1 : String;
  m_pBatch : procedure;
begin
{  for i :=0 to ComboBox1.Items.Count-1 do begin
    S := ComboBox1.Items[i];
    S1 := Copy(S,0,Pos('_',S)-1);
    @m_pBatch:= Form1.MethodAddress(S1);
    if @m_pBatch <> NIL then m_pBatch   ;
    m_pBatch:= Self.MethodAddress(S1);
    m_pBatch;
  end;   }

  if Qry_Batch('W101') then W101;
  if Qry_Batch('W102') then W102;
  if Qry_Batch('M101') then M101;
  if Qry_Batch('M101') then M102;
  if Qry_Batch('Y101') then Y101;
  if Qry_Batch('Y102') then Y102;
  if Qry_Batch('W103') then W103;
  if Qry_Batch('M103') then M103;
  if Qry_Batch('Y103') then Y103;
  if Qry_Batch('W301') then W301;
  if Qry_Batch('M301') then M301;
  if Qry_Batch('Y301') then Y301;
end;

procedure TForm1.Pub_DB_CHECK;
begin
  FDM.PUB_DB.LoginPrompt := False;
  if  FDM.PUB_DB.Connected =True then FDM.PUB_DB.Connected := False;
  with FDM.PUB_DB do begin
    if iMB001 <> 'C_FST' then ConnectionString :='Provider=SQLOLEDB.1;Password=36319254;Persist Security Info=True;User ID=sa;Initial Catalog='''+TRIM(iMB001)+''';Data Source=192.168.10.104'
    else ConnectionString :='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=FST;Data Source=CANDY';
  end;
end;

procedure TForm1.Index;
var
  SQL : String;
  Q3 : TADOQuery;
  i : Word;
begin
  Q3 := TADOQuery.Create(Application);
  Q3.Connection := FDM.PUB_DB;
  SQL :=' Select * from FCMSFL'+
        ' WHERE FL000 IN (''40'',''41'',''42'',''43'')';
  Q3.SQL.Text := SQL;
  Q3.Prepared := True;
  Q3.Open;
  for  i:=0  to Q3.RecordCount-1  do begin
    ComboBox1.Items.Add(Trim(Q3.FieldByName('FL001').Value)+'_'+Trim(Q3.FieldByName('FL002').Value));
    Q3.Next;
  end;
 ComboBox1.ItemIndex := 0;
 Q3.Close;
end;

function TForm1.GetSpecifiedWeekFirstDayOfYear(AYear,
  AWeekNo: integer): TDate;
begin
  dt := EncodeDate(AYear, 1, 1); // 取得 AYear 第一天
  AWeekNo := AWeekNo - 1;
  case DayOfWeek(dt) of // AYear 的第一天是週幾
    1: Result := dt + AWeekNo * 7; // Sunday
    2: Result := (dt - 1) + AWeekNo * 7; // Mon
    3: Result := (dt - 2) + AWeekNo * 7; // Tue
    4: Result := (dt - 3) + AWeekNo * 7; // Wed
    5: Result := (dt - 4) + AWeekNo * 7; // Thu
    6: Result := (dt - 5) + AWeekNo * 7; // Fri
    7: Result := (dt - 6) + AWeekNo * 7; // Sat
  end;
  {dt := GetSpecifiedWeekFirstDayOfYear(StrToInt(Trim(ComboBox1.Text)),SpinEdit1.Value);
  Edit1.text := DateToStr(dt);
  Edit2.text := DateToStr(dt+6);}
end;

procedure TForm1.Chk_FCMSDT;
var
  SQL : String;
  Q3 : TADOQuery;
begin
  Q3 := TADOQuery.Create(Application);
  Q3.Connection := FDM.PUB_DB;
  SQL := 'select DT002 from FCMSDT'+
        ' WHERE DT003 <='''+m_sYMD+''' AND DT004 >='''+m_sYMD+'''';
  Q3.SQL.Text := SQL;
  Q3.Prepared := True;
  Q3.Open;
  m_iWeek := Q3.FieldByName('DT002').Value;
  Q3.Close;
end;

end.
