{------------------------------------------------------------------------------}
{µ¥ÔªÃû³Æ£ºuSaveData.pas                                                       }
{Ä£¿éÃû³Æ£º¹ý³ÌÊý¾Ý´æ´¢Ä£¿é                                                    }
{¹¦ÄÜÃèÊö£º½«ÏµÍ³²ÎÊý´æ´¢µ½±¾µØSQLiteÊý¾Ý¿â                                    }
{½¨Á¢ÈÕÆÚ£º2023-10-31                                                          }
{ÐÞ¸ÄÈÕÆÚ£º2023-10-31                                                          }
{°æȨËùÓУºÀîÁ¼Í¥ liangtingli@outlook.com                                      }
{------------------------------------------------------------------------------}
unit uSaveData;

interface

uses
  Windows, Classes, SysUtils, Dialogs, StrUtils, DateUtils, Variants, Grids, Math,
  StdCtrls, SQLite3, SQLiteTable3, sListBox, Global, log4me, PubUtils;

  {ÉêÃ÷ÄÚ²¿µ÷Óú¯Êý}
  procedure createConfigSql(table:string);
  procedure insertConfigSql(table:string; no:Integer; nam,val:String);
  procedure updateConfigSql(table:string; nam,val:String);
  function  queryConfigSql(table,nam:string):string;
  function  queryConfigCnt(table,nam:string):integer;

  procedure createGrainSql(table:string);
  procedure insertGrainSql(table:string; cod:Integer; no,nam:String;coef1,coef2,coef3,coef4,coef5,intercept:String; flag:Integer=0);
  function  queryGrainSql(table,nam:string):TGrainRec;
  function  queryGrainSql1(table:string; cod:Integer):TGrainRec;
  procedure queryGrainlistSql(table:string; lb:TsListBox; flag:Integer=2);

  procedure createTestSql(table:string);    
  procedure insertTestSql(table:string; mode,is_auto,tim,cod:Integer; nam,info:String; mois,temp,water:double);

  {ÉêÃ÷Íⲿµ÷Óú¯Êý}
  procedure InitConfig();
  procedure InitConn();
  procedure InitGrain();
  procedure InitTestDB();
  procedure InitWeight();
  procedure InitManager();
  procedure AddGrainRecord(nam:string; rec:TGrainRec);
  procedure deleteGrainSql(table,nam: string);
  procedure updateNameSql(table,new,nam: string; newcod,cod: Integer);
  function  queryGrainNumSql(table:string; nam:string; cod:Integer):Integer;
  procedure queryGrainTypeSql(table:string; typ:Integer; cb:TComboBox);

  procedure LoadParam();
  procedure SaveParam();
  procedure SaveWgParam();
  procedure SaveVerifyParam();
  procedure SaveInterceptParam(val:string);

implementation

//--------configÅäÖÃÊý¾Ý¿âº¯Êý--------------------------------------------
//´´½¨configÊý¾Ý¿â
procedure createConfigSql(table:string);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  //½¨ configÊý¾Ý±í
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    if not sqldb.TableExists(table) then begin
      sql := 'CREATE TABLE [' + table + '] ('
            +'[id] INTEGER PRIMARY KEY,'
            +'[nam] VARCHAR(20) NULL,'
            +'[val] VARCHAR(200) NULL );';
      sqldb.ExecSQL(sql);
      //н¨Ë÷Òý
      sql := 'CREATE INDEX index_'+table+' ON ['+table+']([id]);';
      sqldb.ExecSQL(sql);
    end;
  finally
    FreeAndNil(sqldb);
  end;
end;

//²åÈëÒ»ÌõconfigÊý¾Ý
procedure insertConfigSql(table:string; no:Integer; nam,val:String);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    //¿ªÆôÊÂÎñ£¬²åÈëÊý¾Ý
    sqldb.BeginTransaction;
    sql := Format('INSERT INTO [%s](id,nam,val) VALUES(%d,"%s","%s");',
                  [table,no,nam,String(AnsiToUtf8(val))]);
    sqldb.ExecSQL(sql);
    sqldb.Commit; //Ö´ÐÐÊÂÎñ
  finally
    FreeAndNil(sqldb);
  end;
end;

//¸üÐÂÒ»ÌõconfigÊý¾Ý
procedure updateConfigSql(table:string; nam,val:String);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('UPDATE [%s] SET val="%s" WHERE nam="%s";',
                  [table,String(AnsiToUtf8(val)),nam]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//²éѯһÌõconfigÊý¾Ý£¨Ö¸¶¨name£©
function queryConfigSql(table,nam:string):string;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //³õʼ»¯·µ»ØÖµ
  Result := '';
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT * FROM [%s] WHERE nam="%s"',[table,nam]);
    sqltb := sqldb.GetTable(sql);
    if sqltb.Count>0 then
      Result := Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['val']));
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//²éѯconfigÊý¾ÝÊýÁ¿
function queryConfigCnt(table,nam:string):integer;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //³õʼ»¯·µ»ØÖµ
  //Result := 0;
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT * FROM [%s] WHERE nam="%s"',[table,nam]);
    sqltb := sqldb.GetTable(sql);
    Result := sqltb.Count;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//-------- GrainÊý¾Ý¿âº¯Êý--------------------------------------------
//´´½¨ grainÊý¾Ý¿â£¬Ôö¼Ó½Ø¾à×Ö¶Î
procedure createGrainSql(table:string);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  //½¨ configÊý¾Ý±í
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    if not sqldb.TableExists(table) then begin
      sql := 'CREATE TABLE [' + table + '] ('
            +'[id] INTEGER PRIMARY KEY,'        //id񅧏
            +'[cod] INTEGER NULL,'              //Ò»¼¶·ÖÀà´úÂë
            +'[no] VARCHAR(10) NULL,'           //Á¸ÖÖ±àºÅ
            +'[nam] VARCHAR(20) NULL,'          //Á¸ÖÖÃû³Æ
            +'[coef1] VARCHAR(32) NULL,'        //У׼ϵÊý
            +'[coef2] VARCHAR(32) NULL,'
            +'[coef3] VARCHAR(32) NULL,'
            +'[coef4] VARCHAR(32) NULL,'
            +'[coef5] VARCHAR(32) NULL,'
            +'[intercept] VARCHAR(32) NULL,'    //½Ø¾à
            +'[flag] INTEGER NULL );';          //ÊÇ·ñÒ»¼¶·ÖÀà
      sqldb.ExecSQL(sql);
      //н¨Ë÷Òý
      sql := 'CREATE INDEX index_'+table+' ON ['+table+']([id]);';
      sqldb.ExecSQL(sql);
    end;
  finally
    FreeAndNil(sqldb);
  end;
end;

//²åÈëÒ»ÌõconfigÊý¾Ý£¬Ôö¼Ó½Ø¾à
procedure insertGrainSql(table:string; cod:Integer; no,nam:String;coef1,coef2,coef3,coef4,coef5,intercept:String; flag:Integer=0);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    //¿ªÆôÊÂÎñ£¬²åÈëÊý¾Ý
    sqldb.BeginTransaction;
    sql := Format('INSERT INTO [%s](cod,no,nam,coef1,coef2,coef3,coef4,coef5,intercept,flag) '+
                  'VALUES(%d,"%s","%s","%s","%s","%s","%s","%s","%s",%d);',
                  [table,
                   cod,
                   no,
                   String(AnsiToUtf8(nam)),
                   coef1,
                   coef2,
                   coef3,
                   coef4,
                   coef5,
                   intercept,
                   flag]);
    sqldb.ExecSQL(sql);
    sqldb.Commit; //Ö´ÐÐÊÂÎñ
  finally
    FreeAndNil(sqldb);
  end;
end;

//---------------------------------------------------------------------
//ͨ¹ý´óÀànam£¬²éѯ´óÀàCod
function queryGrainMainCodSql(table:string; nam:string):Integer;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('select cod from [%s] where flag=1 and nam="%s";',[table,AnsiToUtf8(nam)]);
    sqltb := sqldb.GetTable(sql);
    Result := sqltb.FieldAsInteger(sqltb.FieldIndex['cod']);
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//ʹÓÃnam²éѯһÌõgrainÊý¾Ý
//À©³äÒ»¸ö½Ø¾à²ÎÊý£¬liliangting 2025.1.16
function queryGrainSql(table,nam:string):TGrainRec;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql,scod : String;
  cod : Integer;
begin
  //³õʼ»¯·µ»ØÖµ
  Result.Code := 0;
  Result.No   := '';
  Result.Name := '';
  Result.Coef[0] := '0';
  Result.Coef[1] := '0';
  Result.Coef[2] := '0';
  Result.Coef[3] := '0';
  Result.Coef[4] := '0';
  Result.Intercept := '0';
  Result.flag := false;

  //Èç¹ûnamÊÇ£ºÓñÃ×-¶«±±-->²ð·ÖΪ¶«±±£¬´«ÈëÕæʵnam
  if  Pos('-', nam)>0 then begin
    scod:= SplitStr(nam, '-', 0);
    nam := SplitStr(nam, '-', 1);
    //ͨ¹ý´óÀàÃû³Æ²écod
    cod := queryGrainMainCodSql(t_Grain, scod);
  end
  else begin//Èç¹ûnamÊÇ£ºÓñÃ×£¬Í¨¹ýÃû³Æ²écod
    cod := queryGrainMainCodSql(t_Grain, nam);
  end;

  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT * FROM [%s] WHERE nam="%s" and cod=%d;',[table,AnsiToUtf8(nam),cod]);
    sqltb := sqldb.GetTable(sql);
    if sqltb.Count>0 then begin
      Result.Code := sqltb.FieldAsInteger(sqltb.FieldIndex['cod']);
      Result.No   := sqltb.FieldAsString(sqltb.FieldIndex['no']);               //Ôö¼ÓÁ¸ÖÖ±àÂë
      Result.Name := Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['nam']));
      Result.Coef[0] := sqltb.FieldAsString(sqltb.FieldIndex['coef1']);
      Result.Coef[1] := sqltb.FieldAsString(sqltb.FieldIndex['coef2']);
      Result.Coef[2] := sqltb.FieldAsString(sqltb.FieldIndex['coef3']);
      Result.Coef[3] := sqltb.FieldAsString(sqltb.FieldIndex['coef4']);
      Result.Coef[4] := sqltb.FieldAsString(sqltb.FieldIndex['coef5']);
      Result.Intercept := sqltb.FieldAsString(sqltb.FieldIndex['intercept']);   //Ôö¼Ó½Ø¾à
      Result.flag := IntToBool(sqltb.FieldAsInteger(sqltb.FieldIndex['flag'])); //Ôö¼Ó´óÀà±êÖ¾
    end;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//////////////////////////////////////////////////////////////////////////
//ʹÓÃcod²éѯһÌõgrainÊý¾Ý
//×¢Ò⣺´ÓCod²éѯÁ¸ÖÖÐÅÏ¢»áÖظ´£¬Ôö¼Óflag=1£¬½ö²é´óÀàÐÅÏ¢£¡£¡£¡
function queryGrainSql1(table:string; cod:Integer):TGrainRec;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //³õʼ»¯·µ»ØÖµ
  Result.Code := 0;
  Result.No   := '';
  Result.Name := '';
  Result.Coef[0] := '0';
  Result.Coef[1] := '0';
  Result.Coef[2] := '0';
  Result.Coef[3] := '0';
  Result.Coef[4] := '0';
  Result.Intercept := '0';
  Result.flag := false;

  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    ///////////////////////////////////////////////////////////////
    //ÕâÀïµÄ²éѯÌõ¼þ¹ýÉÙ£¬²éѯ½á¹û´óÓÚ1£¡£¡£¡
    sql := Format('SELECT * FROM [%s] WHERE cod=%d and flag=1;',[table,cod]);
    ///////////////////////////////////////////////////////////////
    sqltb := sqldb.GetTable(sql);
    if sqltb.Count>0 then begin
      Result.Code := sqltb.FieldAsInteger(sqltb.FieldIndex['cod']);
      Result.No   := sqltb.FieldAsString(sqltb.FieldIndex['no']);               //Ôö¼ÓÁ¸ÖÖ±àÂë
      Result.Name := Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['nam']));
      Result.Coef[0] := sqltb.FieldAsString(sqltb.FieldIndex['coef1']);
      Result.Coef[1] := sqltb.FieldAsString(sqltb.FieldIndex['coef2']);
      Result.Coef[2] := sqltb.FieldAsString(sqltb.FieldIndex['coef3']);
      Result.Coef[3] := sqltb.FieldAsString(sqltb.FieldIndex['coef4']);
      Result.Coef[4] := sqltb.FieldAsString(sqltb.FieldIndex['coef5']);
      Result.Intercept := sqltb.FieldAsString(sqltb.FieldIndex['intercept']);   //Ôö¼Ó½Ø¾à
      Result.flag := IntToBool(sqltb.FieldAsInteger(sqltb.FieldIndex['flag'])); //Ôö¼Ó´óÀà±êÖ¾
    end;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;
/////////////////////////////////////////////////////////////////////////

//²éѯÁ¸ÖÖÇåµ¥
procedure queryGrainlistSql(table:string; lb:TsListBox; flag:Integer=2);
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql  : String;
begin
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    if (flag=0)or(flag=1) then
      sql := Format('select a.*,b.nam as codnam from [%s] a left join grain b on a.cod = b.no where a.flag=%d;',[table,flag])
    else
      sql := Format('select a.*,b.nam as codnam from [%s] a left join grain b on a.cod = b.no ;',[table]);
    sqltb := sqldb.GetTable(sql);
    lb.Items.Clear;
    while not sqltb.EOF do begin
      if sqltb.FieldAsInteger(sqltb.FieldIndex['flag'])=1 then
        lb.Items.Add(Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['nam'])))
      else
        lb.Items.Add(Format('%s-%s', [Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['codnam'])),
                                      Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['nam']))]));
      sqltb.Next;
    end;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;


//²éѯ×îеÄÁ¸ÖÖ±àºÅno
function queryGrainCodSql(table:string):String;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT MAX(no)+1 max_no FROM [%s];',[table]);
    sqltb := sqldb.GetTable(sql);
    Result := sqltb.FieldAsString(sqltb.FieldIndex['max_no']);
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//°´Á¸ÖÖnam²éѯ¼Ç¼ÊýÁ¿
function queryGrainNumSql(table:string; nam:string; cod:Integer):Integer;
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT * FROM [%s] WHERE [nam]="%s" and [cod]=%d;',[table ,AnsiToUtf8(nam), cod]);
    sqltb := sqldb.GetTable(sql);
    Result := sqltb.Count;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;

//²éѯÁ¸ÖÖ´óÀ࣬¼ÓÔص½ComboBox
procedure queryGrainTypeSql(table:string; typ:Integer; cb:TComboBox);
var
  sqldb : TSQLiteDatabase;
  sqltb : TSQLiteTable;
  sql : String;
begin
  //²éѯtable±ívalÁÐ
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('SELECT nam FROM [%s] WHERE [flag]=%d order by cod asc;',[table, typ]);
    sqltb := sqldb.GetTable(sql);
    cb.Items.Clear;
    while not sqltb.EOF do begin
      cb.Items.Add(Utf8ToAnsi(sqltb.FieldAsString(sqltb.FieldIndex['nam'])));
      sqltb.Next;
    end;
    cb.ItemIndex :=0;
  finally
    FreeAndNil(sqltb);
    FreeAndNil(sqldb);
  end;
end;


//¸üÐÂÒ»ÌõgrainÊý¾Ý£¨°´Ãû³ÆºÍ´óÀà´úÂ룩
procedure updateGrainSql(table:string; rec:TGrainRec);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('UPDATE [%s] SET no="%s",coef1="%s",coef2="%s",coef3="%s",coef4="%s",coef5="%s",flag=%d WHERE nam="%s" and cod=%d;',
                  [table,
                   rec.No,
                   rec.Coef[0],
                   rec.Coef[1],
                   rec.Coef[2],
                   rec.Coef[3],
                   rec.Coef[4],
                   Integer(rec.flag),
                   AnsiToUtf8(rec.Name), rec.Code]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//¸üÐÂÒ»ÌõgrainÊý¾Ý£¨°´´óÀà´úÂ룩
procedure updateGrainSql1(table:string; rec:TGrainRec);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('UPDATE [%s] SET coef1="%s",coef2="%s",coef3="%s",coef4="%s",coef5="%s" WHERE cod=%d;',
                  [table,
                   rec.Coef[0],
                   rec.Coef[1],
                   rec.Coef[2],
                   rec.Coef[3],
                   rec.Coef[4],
                   rec.Code]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//¸üнؾà²ÎÊý
procedure updateInterceptSql(table,val,name: string; cod: Integer);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('UPDATE [%s] SET intercept="%s" WHERE nam="%s" and cod=%d;',
                  [table, val, AnsiToUtf8(name), cod]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//¸üÐÂÁ¸ÖÖÃû³Æ
procedure updateNameSql(table,new,nam: string; newcod,cod: Integer);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('UPDATE [%s] SET nam="%s",cod=%d WHERE nam="%s" and cod=%d;',
                  [table, AnsiToUtf8(new), newcod, AnsiToUtf8(nam), cod]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//ɾ³ýÁ¸ÖÖÐÅÏ¢--¸ù¾ÝnamºÍcodɾ³ý
procedure deleteGrainSql(table, nam: string);
var
  sqldb : TSQLiteDatabase;
  sql,scod : String;
  cod : Integer;
begin
  //Èç¹ûnamÊÇ£ºÓñÃ×-¶«±±-->²ð·ÖΪ¶«±±£¬´«ÈëÕæʵnam
  if  Pos('-', nam)>0 then begin
    scod:= SplitStr(nam, '-', 0);
    nam := SplitStr(nam, '-', 1);
    //ͨ¹ý´óÀàÃû³Æ²écod
    cod := queryGrainMainCodSql(t_Grain, scod);
  end
  else begin//Èç¹ûnamÊÇ£ºÓñÃ×£¬Í¨¹ýÃû³Æ²écod
    cod := queryGrainMainCodSql(t_Grain, nam);
  end;

  sqldb := TSQLiteDatabase.Create(glAppPath + CONFIG_DIR + table + '.db');
  try
    sql := Format('DELETE FROM [%s] WHERE [nam]="%s" and [cod]=%d;', [table, AnsiToUtf8(nam), cod]);
    sqldb.ExecSQL(sql);
  finally
    FreeAndNil(sqldb);
  end;
end;

//-------- TestÊý¾Ý¿âº¯Êý--------------------------------------------
//´´½¨ testÊý¾Ý¿â
procedure createTestSql(table:string);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  //½¨ configÊý¾Ý±í
  sqldb := TSQLiteDatabase.Create(glAppPath + DATA_DIR + table + '.db');
  try
    if not sqldb.TableExists(table) then begin
      sql := 'CREATE TABLE [' + table + '] ('
            +'[id] INTEGER PRIMARY KEY,'                      //×Ô¶¯±àºÅ
            +'[mod] INTEGER NULL,'                            //×Ô¶¯Ä£Ê½=0£¬ÊÖ¶¯Ä£Ê½=1
            +'[is_auto] INTEGER CHECK ([is_auto] IN (0, 1)),' //Ôö¼Ó×Ô¶¯±£´æ±êÖ¾
            +'[tim] INTEGER NULL,'                            //ÈÕÆÚʱ¼ä
            +'[cod] INTEGER NULL,'                            //Á¸ÖÖ´úÂë
            +'[nam] VARCHAR(20) NULL,'                        //Á¸ÖÖÃû³Æ
            +'[info] VARCHAR(50) NULL,'                       //Ñù±¾ÐÅÏ¢
            +'[mois]  FLOAT NULL,'                            //º¬Ë®ÂÊ
            //+'[ratio] FLOAT NULL,'
            //+'[weight] FLOAT NULL,'
            +'[temp]  FLOAT NULL,'                            //ζÈ
            +'[water] FLOAT NULL );';                         //ʪ¶È
      sqldb.ExecSQL(sql);
      //н¨Ë÷Òý
      sql := 'CREATE INDEX index_'+table+' ON ['+table+']([id]);';
      sqldb.ExecSQL(sql);
    end;
  finally
    FreeAndNil(sqldb);
  end;
end;

//²åÈëÒ»Ìõ testÊý¾Ý
procedure insertTestSql(table:string; mode,is_auto,tim,cod:Integer; nam,info:String; mois,temp,water:double);
var
  sql : String;
  sqldb : TSQLiteDatabase;
begin
  sqldb := TSQLiteDatabase.Create(glAppPath + DATA_DIR + table + '.db');
  try
    //¿ªÆôÊÂÎñ£¬²åÈëÊý¾Ý
    sqldb.BeginTransaction;
    sql := Format('INSERT INTO [%s](mod,is_auto,tim,cod,nam,info,mois,temp,water) VALUES(%d,%d,%d,%d,"%s","%s",%.6f,%.6f,%.6f);',
                  [table,mode,is_auto,tim,cod,String(AnsiToUtf8(nam)),String(AnsiToUtf8(info)),mois,temp,water]);
    sqldb.ExecSQL(sql);
    sqldb.Commit; //Ö´ÐÐÊÂÎñ
  finally
    FreeAndNil(sqldb);
  end;
end;

//-------µÚÒ»ÖÖȫд´½¨configÊý¾Ý¿âµÄ·½·¨---------------------------------------
//´´½¨Ð嵀 configÊý¾Ý¿â
procedure InitConfig();
begin
  if not FileExists(glConfig_db) then begin
    //½¨ configÊý¾Ý¿â±í
    createConfigSql(t_Config);
    //³õʼ»¯Êý¾ÝÐÐ
    insertConfigSql(t_Config,1,'StartFreq','10500');
    insertConfigSql(t_Config,2,'StopFreq','12000');
    insertConfigSql(t_Config,3,'Step','50');
    insertConfigSql(t_Config,4,'Freq','0');
    insertConfigSql(t_Config,5,'Decay','1');
    insertConfigSql(t_Config,6,'ASK','1');
    insertConfigSql(t_Config,7,'Volume','0.52');
    insertConfigSql(t_Config,8,'Tare','1040');
    //ÐÂÔö¿ª»úÆô¶¯ÑÓʱʱ¼ä
    insertConfigSql(t_Config,9,'Delay','10');
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-²ÎÊýÊý¾Ý¿â.',[t_Config]));
  end;
end;

//´´½¨Ð嵀 connÊý¾Ý¿â
procedure InitConn();
begin
  if not FileExists(glConn_db) then begin
    //½¨ configÊý¾Ý¿â±í
    createConfigSql(t_Conn);
    //³õʼ»¯Êý¾ÝÐÐ
    insertConfigSql(t_Conn,1,'Ip','127.0.0.1');
    insertConfigSql(t_Conn,2,'Port','1502');
    insertConfigSql(t_Conn,3,'SerialPort','COM8');
    insertConfigSql(t_Conn,4,'Baud','115200');
    insertConfigSql(t_Conn,5,'Parity','N');
    insertConfigSql(t_Conn,6,'Data_bit','8');
    insertConfigSql(t_Conn,7,'Stop_bit','1');   
    insertConfigSql(t_Conn,8,'AutoRun','false'); 
    insertConfigSql(t_Conn,9,'Time1','20');
    insertConfigSql(t_Conn,10,'Time2','30');
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-²ÎÊýÊý¾Ý¿â.',[t_Conn]));
  end;
end;

//´´½¨Ð嵀 grainÊý¾Ý¿â£¬Ôö¼Ó½Ø¾à
procedure InitGrain();
begin
  if not FileExists(glGrain_db) then begin
    //½¨ configÊý¾Ý¿â±í
    createGrainSql(t_Grain);
    //³õʼ»¯Êý¾ÝÐÐ
    {insertGrainSql(t_Grain,1,'1','СÂó','65.160129655062','-0.107375833331','0.000063694808','-0.000000011576','0','0',1);
    insertGrainSql(t_Grain,2,'2','µ¾¹È','-8.443205789262','0.021404282111','-0.000008683206','0.000000001917','0','0',1);
    insertGrainSql(t_Grain,3,'3','»Æ¶¹','-6.204673384923','0.025161776479','0.000014765722','0.000000003440','0','0',1);
    insertGrainSql(t_Grain,4,'4','ÓñÃ×','-16.278037347152','0.033766686581','-0.000015923042','0.000000002881','0','0',1);  }
    insertGrainSql(t_Grain,1,'1','µ¾¹È','0','0.01','0','0','0','0',1);
    insertGrainSql(t_Grain,2,'2','ÓñÃ×','0','0.01','0','0','0','0',1);
    insertGrainSql(t_Grain,3,'3','´ó¶¹','0','0.01','0','0','0','0',1);
    insertGrainSql(t_Grain,4,'4','СÂó','0','0.01','0','0','0','0',1);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-²ÎÊýÊý¾Ý¿â.',[t_Grain]));
  end;
end;

//´´½¨Ð嵀 testÊý¾Ý¿â
procedure InitTestDB();
begin
  if not FileExists(glData_db) then begin
    //½¨ configÊý¾Ý¿â±í
    createTestSql(t_Data);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-´æ´¢Êý¾Ý¿â.',[t_Data]));
  end;
end;

//´´½¨Ð嵀 ÖØÁ¿Ð£×¼Êý¾Ý¿â
procedure InitWeight();
begin
  if not FileExists(glWeight_db) then begin
    //½¨ weightÊý¾Ý¿â±í
    createConfigSql(t_Weight);
    //³õʼ»¯Êý¾ÝÐÐ
    insertConfigSql(t_Weight,1,'k','1');
    insertConfigSql(t_Weight,2,'b','1');
    insertConfigSql(t_Weight,3,'tare','0');
    insertConfigSql(t_Weight,4,'range','0'); //Îó²î·¶Î§
    insertConfigSql(t_Weight,5,'test1','0');
    insertConfigSql(t_Weight,6,'test2','0');
    insertConfigSql(t_Weight,7,'test3','0');
    insertConfigSql(t_Weight,8,'std1','0');
    insertConfigSql(t_Weight,9,'std2','0');
    insertConfigSql(t_Weight,10,'std3','0');
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-²ÎÊýÊý¾Ý¿â.',[t_Weight]));
  end;
end;

//´´½¨Ð嵀 manager Êý¾Ý¿â
procedure InitManager();
begin
  if not FileExists(glManager_db) then begin
    //½¨ configÊý¾Ý¿â±í
    createConfigSql(t_Manager);
    //³õʼ»¯Êý¾ÝÐÐ
    insertConfigSql(t_Manager,1,'Admin','123456');
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º³õʼ»¯´´½¨ %s-Êý¾Ý¿â.',[t_Manager]));
  end;
end;

//н¨Ò»Ìõ grainÊý¾Ý
procedure AddGrainRecord(nam:string; rec:TGrainRec);
var
  no : String;
begin
  if FileExists(glGrain_db) then begin
    //ÏȲéѯ×îеÄcod
    no := queryGrainCodSql(t_Grain);
    //ÐÂÔöÁ¸ÖÖÊý¾ÝÐÐ
    insertGrainSql(t_Grain,
                   GrainType_rec.Code,
                   no,nam,
                   GrainType_rec.Coef[0],
                   GrainType_rec.Coef[1],
                   GrainType_rec.Coef[2],
                   GrainType_rec.Coef[3],
                   GrainType_rec.Coef[4],
                   '0',0);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£ºÊý¾Ý¿â [%s] ÐÂÔöÒ»ÌõÁ¸ÖÖÐÅÏ¢.',[t_Grain]));
  end;
end;
//------- ¼ÓÔغͱ£´æ¸÷ÀàConfigÅäÖÃÐÅÏ¢-----------------------------------------
//¼ÓÔظ÷Àà²ÎÊý½á¹¹
procedure LoadParam();
var
  c : Char;
begin
  //¼ÓÔØϵͳ²ÎÊý
  if FileExists(glConfig_db) then begin
    SysConfig.StartFreq := StrToFloatDef(queryConfigSql(t_Config,'StartFreq'), 10500);
    SysConfig.StopFreq := StrToFloatDef(queryConfigSql(t_Config,'StopFreq'), 12000);
    SysConfig.Step := StrToIntDef(queryConfigSql(t_Config,'Step'), 50);
    SysConfig.Freq := StrToFloatDef(queryConfigSql(t_Config,'Freq'), 0);
    SysConfig.Decay := StrToFloatDef(queryConfigSql(t_Config,'Decay'), 1);
    SysConfig.ASK := StrToIntDef(queryConfigSql(t_Config,'ASK'), 1);
    SysConfig.Volume := StrToFloatDef(queryConfigSql(t_Config,'Volume'), 0);
    SysConfig.Tare := StrToFloatDef(queryConfigSql(t_Config,'Tare'), 1000);

    //ÐÂÔö¿ª»úÆô¶¯ÑÓʱʱ¼ä
    if queryConfigSql(t_Config,'Delay')='' then
      insertConfigSql(t_Config,9,'Delay','10');
    SysConfig.delay := StrToIntDef(queryConfigSql(t_Config,'Delay'), 0);
    
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£ºÔØÈë %s-ϵͳ²ÎÊý (É豸²ÎÊý).',[t_Config]));
  end;

  //¼ÓÔØͨѶ²ÎÊý
  if FileExists(glConn_db) then begin
    PlcConn.ip := queryConfigSql(t_Conn,'Ip');
    PlcConn.port := StrToIntDef(queryConfigSql(t_Conn,'Port'), 1502);
    WrConn.SerialPort := queryConfigSql(t_Conn,'SerialPort');
    WrConn.baud := StrToIntDef(queryConfigSql(t_Conn,'Baud'), 115200);
    WrConn.data_bit := StrToIntDef(queryConfigSql(t_Conn,'Data_bit'), 8);
    WrConn.stop_bit := StrToIntDef(queryConfigSql(t_Conn,'Stop_bit'), 1);
    glAutoRun := StrToBoolDef(queryConfigSql(t_Conn,'AutoRun'), False);
    glTime1 := StrToIntDef(queryConfigSql(t_Conn,'Time1'), 20);
    glTime2 := StrToIntDef(queryConfigSql(t_Conn,'Time2'), 30);
    //µ¥¶À´¦ÀícharÀàÐÍ
    CopyMemory(@c, PChar(queryConfigSql(t_Conn,'Parity')), 1);
    WrConn.parity := c;

    //ÐÂÔöÊý¾Ý´æ´¢²ÎÊý
    if queryConfigCnt(t_Conn, 'AutoSave')=0 then
      insertConfigSql(t_Conn, 11, 'AutoSave', '0');
    if queryConfigCnt(t_Conn, 'SamplePrefix')=0 then
      insertConfigSql(t_Conn, 12, 'SamplePrefix', '');
    if queryConfigCnt(t_Conn, 'SampleDir')=0 then
      insertConfigSql(t_Conn, 13, 'SampleDir', '');
    if queryConfigCnt(t_Conn, 'SampleName')=0 then
      insertConfigSql(t_Conn, 14, 'SampleName', '');
    smp.flag := StrToBoolDef(queryConfigSql(t_Conn,'AutoSave'), False);
    smp.prefix := queryConfigSql(t_Conn,'SamplePrefix');
    smp.dir := queryConfigSql(t_Conn,'SampleDir');
    smp.info := queryConfigSql(t_Conn,'SampleName');

    //ÐÂÔöÐÄÌø²ÎÊý
    if queryConfigCnt(t_Conn, 'HeartIp')=0 then
      insertConfigSql(t_Conn, 15, 'HeartIp', '192.168.0.200');
    PlcConn.heart_ip := queryConfigSql(t_Conn,'HeartIp');
    if queryConfigCnt(t_Conn, 'HeartPort')=0 then
      insertConfigSql(t_Conn, 16, 'HeartPort', '8868');
    PlcConn.heart_port := StrToIntDef(queryConfigSql(t_Conn,'HeartPort'), 8868);

    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£ºÔØÈë %s-ϵͳ²ÎÊý (Ǥ¼ñϵͳ¡¢Ë®·ÖÒÇ¡¢´æ´¢²ÎÊý).',[t_Conn]));
  end;

  //¼ÓÔØÁ¸ÖÖУ׼ϵÊý
  if FileExists(glGrain_db) then begin
    Grain := queryGrainSql(t_Grain,'СÂó');
    log4info(Format('ϵͳ£ºÔØÈë %s-Á¸ÖÖ²ÎÊý (Á¸ÖÖ´úÂë¡¢Ãû³Æ¡¢Ð£×¼ÏµÊý).',[t_Grain]));
  end;

  //¼ÓÔØÖØÁ¿Ð£×¼ÏµÊý
  if FileExists(glWeight_db) then begin
    Wg.k      := StrToFloatDef(queryConfigSql(t_Weight,'k'), 1);
    Wg.b      := StrToFloatDef(queryConfigSql(t_Weight,'b'), 1);
    Wg.tare   := StrToFloatDef(queryConfigSql(t_Weight,'tare'), 0);
    Wg.range  := StrToFloatDef(queryConfigSql(t_Weight,'range'), 0);
    Wg.test1  := StrToFloatDef(queryConfigSql(t_Weight,'test1'),0);
    Wg.test2  := StrToFloatDef(queryConfigSql(t_Weight,'test2'),0);
    Wg.test3  := StrToFloatDef(queryConfigSql(t_Weight,'test3'),0);
    Wg.std1   := StrToFloatDef(queryConfigSql(t_Weight,'std1'), 0);
    Wg.std2   := StrToFloatDef(queryConfigSql(t_Weight,'std2'), 0);
    Wg.std3   := StrToFloatDef(queryConfigSql(t_Weight,'std3'), 0);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£ºÔØÈë %s-ϵͳ²ÎÊý (y=kx+b).',[t_Weight]));
  end;

  //¼ÓÔصǼ¿ÚÁî
  if FileExists(glManager_db) then begin
    SysConfig.password := queryConfigSql(t_Manager,'Admin');
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£ºÔØÈë %s-¹ÜÀí²ÎÊý (ÊÖ¶¯Ä£Ê½¿ÚÁî).',[t_Manager]));
  end;
  //µ÷ÊÔ²ÎÊý
  //showmessage(Format('PLC IP: %s:%d',[plcconn.ip, plcconn.prot]));
  //showmessage(Format('Wr :%s,%d,%s,%d,%d',[wrconn.SerialPort,wrconn.baud,wrconn.parity,wrconn.data_bit,wrconn.stop_bit]));
end;

//±£´æ¸÷Àà²ÎÊý½á¹¹
procedure SaveParam();
begin
  //±£´æϵͳ²ÎÊý
  if FileExists(glConfig_db) then begin
    updateConfigSql(t_Config,'StartFreq',FloatToStr(SysConfig.StartFreq));
    updateConfigSql(t_Config,'StopFreq',FloatToStr(SysConfig.StopFreq));
    updateConfigSql(t_Config,'Step', IntToStr(SysConfig.Step));
    updateConfigSql(t_Config,'Freq',FloatToStr(SysConfig.Freq));
    updateConfigSql(t_Config,'Decay',FloatToStr(SysConfig.Decay));
    updateConfigSql(t_Config,'ASK', IntToStr(SysConfig.ASK));
    updateConfigSql(t_Config,'Volume',FloatToStr(SysConfig.Volume));
    updateConfigSql(t_Config,'Tare',FloatToStr(SysConfig.Tare));  
    updateConfigSql(t_Config,'Delay', IntToStr(SysConfig.delay));
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º±£´æ %s-ϵͳ²ÎÊý (É豸²ÎÊý).',[t_Config]));
  end;
  //±£´æͨѶ²ÎÊý
  if FileExists(glConn_db) then begin
    updateConfigSql(t_Conn,'Ip',PlcConn.ip);
    updateConfigSql(t_Conn,'Port',IntToStr(PlcConn.port));
    updateConfigSql(t_Conn,'HeartIp',PlcConn.heart_ip);
    updateConfigSql(t_Conn,'HeartPort',IntToStr(PlcConn.heart_port));
    updateConfigSql(t_Conn,'SerialPort',WrConn.SerialPort);
    updateConfigSql(t_Conn,'Baud',IntToStr(WrConn.baud));
    updateConfigSql(t_Conn,'Parity',WrConn.Parity);
    updateConfigSql(t_Conn,'Data_bit',IntToStr(WrConn.Data_bit));
    updateConfigSql(t_Conn,'Stop_bit',IntToStr(WrConn.Stop_bit));
    updateConfigSql(t_Conn,'AutoRun',BoolToString(glAutoRun));
    updateConfigSql(t_Conn,'Time1',IntToStr(glTime1));
    updateConfigSql(t_Conn,'Time2',IntToStr(glTime2));
    //Ôö¼ÓÊý¾Ý´æ´¢²ÎÊý
    updateConfigSql(t_Conn,'AutoSave',BoolToString(smp.flag));
    updateConfigSql(t_Conn,'SamplePrefix',smp.prefix);
    updateConfigSql(t_Conn,'SampleDir',   smp.dir);
    updateConfigSql(t_Conn,'SampleName',  smp.info);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º±£´æ %s-ϵͳ²ÎÊý (Ǥ¼ñϵͳ¡¢Ë®·ÖÒÇ¡¢´æ´¢²ÎÊý).',[t_Conn]));
  end;

  //±£´æµÇ¼¿ÚÁî
  if FileExists(glManager_db) then begin
    updateConfigSql(t_Manager,'Admin',SysConfig.password);
    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º±£´æ %s-¹ÜÀí²ÎÊý (ÊÖ¶¯Ä£Ê½¿ÚÁî).',[t_Manager]));
  end;

  //±£´æÖØÁ¿ÏµÊý
  SaveWgParam();
end;

//±£´æÖØÁ¿²ÎÊý
procedure SaveWgParam();
begin
  //±£´æÖØÁ¿ÏµÊý
  if FileExists(glWeight_db) then begin
    updateConfigSql(t_Weight,'k',FloatToStr(Wg.k));
    updateConfigSql(t_Weight,'b',FloatToStr(Wg.b));
    updateConfigSql(t_Weight,'tare',FloatToStr(Wg.tare));
    updateConfigSql(t_Weight,'range',FloatToStr(Wg.range));
    updateConfigSql(t_Weight,'test1',FloatToStr(Wg.test1));
    updateConfigSql(t_Weight,'test2',FloatToStr(Wg.test2));
    updateConfigSql(t_Weight,'test3',FloatToStr(Wg.test3));  
    updateConfigSql(t_Weight,'std1',FloatToStr(Wg.std1));
    updateConfigSql(t_Weight,'std2',FloatToStr(Wg.std2));
    updateConfigSql(t_Weight,'std3',FloatToStr(Wg.std3));

    //¼Ç¼ÈÕÖ¾
    log4info(Format('ϵͳ£º±£´æ %s-²ÎÊý (y=kx+b).',[t_Weight]));
  end;
end;

//±£´æУ׼²ÎÊýµ½Êý¾Ý¿â
procedure SaveVerifyParam();
begin
  //±£´æÁ¸ÖÖ¹ØÁªµÄУ׼ϵÊý
  if FileExists(glGrain_db) then begin
    //Ð޸ĴóÀàʱ£¬Ð¡Ààͬ²½Ë¢Ð£¬·´Ö®²»¸Ä£¡
    if Grain.flag then
      updateGrainSql1(t_Grain, Grain)
    else
      updateGrainSql(t_Grain, Grain);
    log4info(Format('ϵͳ£º±£´æ %s-Á¸ÖÖ²ÎÊý (Îå¸öУ׼ϵÊý).',[t_Grain]));
  end;
end;

//±£´æ½Ø¾à²ÎÊýµ½Êý¾Ý¿â
procedure SaveInterceptParam(val:string);
begin
  //grain.dbÐÂÔöÒ»ÁУ¬ÓÃÓÚ±£´æ½Ø¾à²ÎÊý
  if FileExists(glGrain_db) then begin
    updateInterceptSql(t_Grain,val,Grain.Name,Grain.Code);
    log4info(Format('ϵͳ£º±£´æ½Ø¾à²ÎÊý-[%s] µ½ [%s]Á¸ÖÖÐÅÏ¢.',[val, Grain.Name]));
  end;
end;

end.