using System.Linq; using System.Text; using System.Threading.Tasks; using System.Reflection; using TrustDevKit.Accident_WebService; using TrustDevKit.localhost; using TrustDevKit.Misc; using System.Windows; using System.Data.SQLite; using System.Data; using System.Data.Common; using System.IO; using System.Configuration; using System; namespace TrustDevKit { namespace Accident { public partial class DataService { /// <summary> /// 更新本地字典、文书模板库 /// </summary> /// <returns>返回更新表数,失败出错返回-1</returns> public static int Upate_Code_TemplateDB() { int gxs = -1; try { using (SQLiteConnection connection = new SQLiteConnection(SQLiteConnectionString_Code_TemplateDB)) { connection.Open(); SQLiteCommand command = new SQLiteCommand(connection); #region 检查更新记录 string sql = "create table if not exists Update_Info(gxxm varchar2(64),gxsj date);"; command.CommandText = sql; command.ExecuteNonQuery(); sql = "select gxsj from Update_Info where gxxm='Code_TemplateDB';"; command.CommandText = sql; var result = command.ExecuteScalar(); DateTime last_update = new DateTime(); ; if (result == null) { sql = "insert into update_info (gxxm,gxsj) values ('Code_TemplateDB',datetime('2001-01-01'));"; command.CommandText = sql; command.ExecuteNonQuery(); } else { last_update = (DateTime)result; } gxs++; //查找最近更新的表，以服务器更新时间与config文件记录做对比 DataTable table_updateinfo = taps_WebService.Get_UpdateInfo(); //服务器更新信息表 bool frm_codetype_updated = (DateTime)table_updateinfo.Select("DM='Frm_Code'")[0]["GXSJ"] > last_update; bool frm_code_updated = (DateTime)table_updateinfo.Select("DM='Taps_CodeType'")[0]["GXSJ"] > last_update; bool taps_codetype_updated = (DateTime)table_updateinfo.Select("DM='Taps_Code'")[0]["GXSJ"] > last_update; bool taps_code_updated = (DateTime)table_updateinfo.Select("DM='Taps_Code'")[0]["GXSJ"] > last_update; bool doctemplatetype_updated = (DateTime)table_updateinfo.Select("DM='Taps_DocTemplateType'")[0]["GXSJ"] > last_update; bool doctemplate_updated = (DateTime)table_updateinfo.Select("DM='Taps_DocTemplate'")[0]["GXSJ"] > last_update; #endregion //开始事务 using (DbTransaction transaction = connection.BeginTransaction()) { try { #region 更新Frm_CodeType表 if (frm_code_updated) { string drop_From_CodeType = "drop table if exists Frm_codeType;"; string create_Frm_CodeType = " CREATE TABLE if not exists FRM_CODETYPE( xtlb VARCHAR2(2) not null, dmlb VARCHAR2(6) not null, lbsm VARCHAR2(256) not null, dmcd NUMBER(2), lbsx CHAR(1)," + " dmsx NUMBER(4), bz VARCHAR2(256), jznc CHAR(1), dmlx CHAR(1));"; string create_Frm_CodeType_Idx = "CREATE UNIQUE INDEX if not exists Frm_CodeType_Idx on Frm_CodeType (XTLB,DMLB);"; command.CommandText = drop_From_CodeType + create_Frm_CodeType + create_Frm_CodeType_Idx; command.ExecuteNonQuery(); DataTable frmcodetype = DataService.taps_WebService.Get_CodeTable_by_name("Frm_CodeType"); foreach (var item in frmcodetype.Rows) { DataRow dr = item as DataRow; sql = "INSERT INTO FRM_CODETYPE(xtlb,dmlb,lbsm,dmcd,lbsx,dmsx,bz,jznc,dmlx)values(@xtlb,@dmlb,@lbsm,@dmcd,@lbsx,@dmsx,@bz,@jznc,@dmlx)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@xtlb",dr["XTLB"]), new SQLiteParameter("@dmlb",dr["DMLB"]), new SQLiteParameter("@lbsm",dr["LBSM"]), new SQLiteParameter("@dmcd",dr["DMCD"]), new SQLiteParameter("@lbsx",dr["LBSX"]), new SQLiteParameter("@dmsx",dr["DMSX"]) , new SQLiteParameter("@bz",dr["BZ"]), new SQLiteParameter("@jznc",dr["JZNC"]), new SQLiteParameter("@dmlx",dr["DMLX"]) }; command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 更新Frm_Code if (frm_codetype_updated) { string drop_From_Code = "drop table if exists Frm_code;"; string create_Frm_Code = " CREATE TABLE if not exists FRM_CODE( xtlb CHAR(2) not null, dmlb VARCHAR2(6) not null, dmz VARCHAR2(30) not null, " + "dmsm1 VARCHAR2(256) not null, dmsm2 VARCHAR2(256), dmsm3 VARCHAR2(256), dmsm4 VARCHAR2(256), dmsx CHAR(1) default 0, sxh NUMBER(4), ywdx VARCHAR2(128), zt CHAR(1) default 1);"; string create_Frm_Code_Idx = "CREATE UNIQUE INDEX if not exists Frm_Code_Idx on Frm_Code (XTLB,DMLB,DMZ);"; command.CommandText = drop_From_Code + create_Frm_Code + create_Frm_Code_Idx; command.ExecuteNonQuery(); DataTable frmcode = DataService.taps_WebService.Get_CodeTable_by_name("Frm_Code"); foreach (var item in frmcode.Rows) { DataRow dr = item as DataRow; sql = "INSERT INTO FRM_CODE(xtlb,dmlb,dmz,dmsm1,dmsm2,dmsm3,dmsm4,dmsx,sxh,ywdx,zt)values(@xtlb,@dmlb,@dmz,@dmsm1,@dmsm2,@dmsm3,@dmsm4,@dmsx,@sxh,@ywdx,@zt)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@xtlb",dr["XTLB"]), new SQLiteParameter("@dmlb",dr["DMLB"]), new SQLiteParameter("@dmz",dr["DMZ"]), new SQLiteParameter("@dmsm1",dr["DMSM1"]), new SQLiteParameter("@dmsm2",dr["DMSM2"]), new SQLiteParameter("@dmsm3",dr["DMSM3"]) , new SQLiteParameter("@dmsm4",dr["DMSM4"]), new SQLiteParameter("@dmsx",dr["DMSX"]), new SQLiteParameter("@sxh",dr["SXH"]), new SQLiteParameter("@ywdx",dr["YWDX"]), new SQLiteParameter("@zt",dr["ZT"]) }; command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 更新Taps_CodeType if (taps_codetype_updated) { string drop_Taps_CodeType = "drop table if exists Taps_codeType;"; string create_TAPS_CODETYPE = "CREATE TABLE if not exists TAPS_CODETYPE( dmlb VARCHAR2(6) not null, lbsm VARCHAR2(256) not null, dmcd NUMBER(2), " + "lbsx CHAR(1), dmsx NUMBER(4), bz VARCHAR2(256), jznc CHAR(1), dmlx CHAR(1));"; string create_Taps_CodeType_Idx = "CREATE UNIQUE INDEX if not exists Taps_CodeType_Idx on Taps_CodeType (DMLB);"; command.CommandText = drop_Taps_CodeType + create_TAPS_CODETYPE + create_Taps_CodeType_Idx; command.ExecuteNonQuery(); DataTable tapscodetype = DataService.taps_WebService.Get_CodeTable_by_name("Taps_CodeType"); foreach (var item in tapscodetype.Rows) { DataRow dr = item as DataRow; sql = "INSERT INTO Taps_CODETYPE(dmlb,lbsm,dmcd,lbsx,dmsx,bz,jznc,dmlx)values(@dmlb,@lbsm,@dmcd,@lbsx,@dmsx,@bz,@jznc,@dmlx)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@dmlb",dr["DMLB"]), new SQLiteParameter("@lbsm",dr["LBSM"]), new SQLiteParameter("@dmcd",dr["DMCD"]), new SQLiteParameter("@lbsx",dr["LBSX"]), new SQLiteParameter("@dmsx",dr["DMSX"]) , new SQLiteParameter("@bz",dr["BZ"]), new SQLiteParameter("@jznc",dr["JZNC"]), new SQLiteParameter("@dmlx",dr["DMLX"]) }; command = new SQLiteCommand(connection); command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 更新Taps_Code if (taps_code_updated) { string drop_Taps_Code = "drop table if exists Taps_code;"; string create_TAPS_CODE = "CREATE TABLE if not exists TAPS_CODE( dmlb VARCHAR2(6) not null, dmz VARCHAR2(30) not null, dmsm1 VARCHAR2(256) not null, dmsm2 VARCHAR2(256), dmsm3 VARCHAR2(256)," + " dmsm4 VARCHAR2(256), dmsx CHAR(1) default 0, sxh NUMBER(4), ywdx VARCHAR2(128), zt CHAR(1) default 1);"; string create_Taps_Code_Idx = "CREATE UNIQUE INDEX if not exists Taps_Code_Idx on Taps_Code (DMLB,DMZ);"; command.CommandText = drop_Taps_Code + create_TAPS_CODE + create_Taps_Code_Idx; command.ExecuteNonQuery(); DataTable tapscode = DataService.taps_WebService.Get_CodeTable_by_name("Taps_Code"); foreach (var item in tapscode.Rows) { DataRow dr = item as DataRow; sql = "INSERT INTO Taps_CODE(dmlb,dmz,dmsm1,dmsm2,dmsm3,dmsm4,dmsx,sxh,ywdx,zt)values(@dmlb,@dmz,@dmsm1,@dmsm2,@dmsm3,@dmsm4,@dmsx,@sxh,@ywdx,@zt)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@dmlb",dr["DMLB"]), new SQLiteParameter("@dmz",dr["DMZ"]), new SQLiteParameter("@dmsm1",dr["DMSM1"]), new SQLiteParameter("@dmsm2",dr["DMSM2"]), new SQLiteParameter("@dmsm3",dr["DMSM3"]) , new SQLiteParameter("@dmsm4",dr["DMSM4"]), new SQLiteParameter("@dmsx",dr["DMSX"]), new SQLiteParameter("@sxh",dr["SXH"]), new SQLiteParameter("@ywdx",dr["YWDX"]), new SQLiteParameter("@zt",dr["ZT"]) }; command = new SQLiteCommand(connection); command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 更新DocTemplateType if (doctemplatetype_updated) { string drop_DocTemplateType = "drop table if exists DocTemplateType;"; string create_DocTemplateType = " CREATE TABLE if not exists DocTemplateType(XTLB varchar(2),MBLB varchar(6),LBSM varchar(256),MBSX int(4),BZ varchar(256));"; string create_DocTemplateType_Idx = "CREATE UNIQUE INDEX if not exists DocTemplateType_Idx on DocTemplateType (XTLB,MBLB);"; command.CommandText = drop_DocTemplateType + create_DocTemplateType + create_DocTemplateType_Idx; command.ExecuteNonQuery(); DataTable doctemplatetype = DataService.taps_WebService.Get_CodeTable_by_name("Taps_DocTemplateType"); foreach (var item in doctemplatetype.Rows) { DataRow dr = item as DataRow; sql = "INSERT INTO DocTemplateType(xtlb,mblb,lbsm,mbsx,bz)values(@xtlb,@mblb,@lbsm,@mbsx,@bz)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@xtlb",dr["XTLB"]), new SQLiteParameter("@mblb",dr["MBLB"]), new SQLiteParameter("@lbsm",dr["LBSM"]), new SQLiteParameter("@mbsx",dr["MBSX"]), new SQLiteParameter("@bz",dr["BZ"]) }; command = new SQLiteCommand(connection); command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 更新DocTemplate if (doctemplate_updated) { string drop_DocTemplate = "drop table if exists DocTemplate;"; string create_DocTemplate = "CREATE TABLE if not exists DocTemplate( xtlb CHAR(2) not null, mblb VARCHAR2(6) not null, mbdm VARCHAR2(30) not null, " + "mbmc VARCHAR2(256) not null, sxh NUMBER(4), zt CHAR(1) default 1, gxsj datatime, mb Blob);"; string create_DocTemplate_Idx = "CREATE UNIQUE INDEX if not exists DocTemplate_Idx on DocTemplate (XTLB,MBLB,MBDM);"; command.CommandText = drop_DocTemplate + create_DocTemplate + create_DocTemplate_Idx; command.ExecuteNonQuery(); DataTable doctemplate = DataService.taps_WebService.Get_CodeTable_by_name("Taps_DocTemplate"); foreach (var item in doctemplate.Rows) { DataRow dr = item as DataRow; string mb_file = dr["XTLB"] + "_" + dr["MBLB"] + "_" + dr["MBDM"]; byte[] mb_buffer = DataService.taps_WebService.Get_DocTemplate(mb_file); sql = "INSERT INTO DocTemplate(xtlb,mblb,mbdm,mbmc,sxh,zt,gxsj,mb)values(@xtlb,@mblb,@mbdm,@mbmc,@sxh,@zt,@gxsj,@mb)"; SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@xtlb",dr["XTLB"]), new SQLiteParameter("@mblb",dr["MBLB"]), new SQLiteParameter("@mbdm",dr["MBDM"]), new SQLiteParameter("@mbmc",dr["MBMC"]), new SQLiteParameter("@sxh",dr["SXH"]), new SQLiteParameter("@zt",dr["ZT"]), new SQLiteParameter("@gxsj",dr["GXSJ"]) , new SQLiteParameter("@mb",mb_buffer) }; command.CommandText = sql; command.Parameters.AddRange(parameters); command.ExecuteNonQuery(); } gxs++; } #endregion #region 记录本次更新时间 if (gxs > 0) { sql = "update update_info set gxsj=datetime('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');"; command.CommandText = sql; command.ExecuteNonQuery(); } #endregion transaction.Commit(); //提交事务，写入数据文件 } catch (Exception) { transaction.Rollback(); //出错回滚 return -1; } } connection.Close(); } return gxs; } catch { return -1; } } } } }