static void Main(string[] args)
{
String fileName = @"D:\test.db";
// create a database "context" object t
String connectionString = String.Format("Data Source={0};Version=3", fileName);
DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);
#region 创建测试表1
//string dropQuery = "DROP TABLE qrtz_operatelog";
//db.Execute(dropQuery);
//String createQuery =
// @"CREATE TABLE IF NOT EXISTS
// [User] (
// [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
// [Name] VARCHAR(20) NOT NULL DEFAULT 'tww',
// [Phone] VARCHAR(20) NOT NULL,
// [Email] VARCHAR(20) NOT NULL,
// [Password] VARCHAR(20) NOT NULL,
// [Active] INTEGER NOT NULL DEFAULT 1,
// [CreateTime] TEXT NOT NULL
// )";
String createQuery =
@"DROP TABLE qrtz_operatelog;
CREATE TABLE IF NOT EXISTS
[qrtz_operatelog] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[TableName] VARCHAR(255) ,
[Describe] VARCHAR(255) ,
[Type] INTEGER NOT NULL,
[CreateTime] datetime NOT NULL,
[UpdateTime] datetime
)";
db.Execute(createQuery);
createQuery =
@"DROP TABLE Schedule;
CREATE TABLE IF NOT EXISTS
[Schedule] (
[JobId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[JobName] VARCHAR(255) ,
[JobGroup] VARCHAR(255) ,
[JobStatus] INTEGER NOT NULL,
[Cron] varchar(50) ,
[AssemblyName] varchar(50) NOT NULL,
[ClassName] varchar(50) NOT NULL,
[Remark] VARCHAR(255) ,
[CreateTime] datetime NOT NULL,
[UpdateTime] datetime ,
[RunTimes] INTEGER NOT NULL,
[BeginTime] datetime NOT NULL,
[EndTime] datetime ,
[TriggerType] INTEGER NOT NULL,
[IntervalSecond] INTEGER NOT NULL,
[Url] VARCHAR(255)
)";
db.Execute(createQuery);
}
/// <summary>
/// 添加或替换项目
/// </summary>
/// <param name="zipFile">压缩包</param>
/// <param name="catalogNumber">编号</param>
/// <param name="sourceFile">源文件</param>
/// <returns>CatalogID</returns>
public string addOrReplaceProject(string zipFile, string catalogNumber, string sourceFile)
{
//SQLite数据库工厂
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
//NDEY数据库连接
Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory);
//是否在执入后执行查询（主要针对Sqlite）
context.IsSupportSelectIdentityAfterInsert = false;
//是否在Dispose后执行GC用于解决Dispose后无法删除的问题（主要针对Sqlite）
context.IsSupportGCAfterDispose = true;
try
{
return(importDB(zipFile, catalogNumber, sourceFile, context));
}
catch (Exception ex)
{
PublicManager.Modules.Module_A.PkgImporter.Forms.ImporterForm.writeImportLog(PublicManager.Modules.Module_A.PkgImporter.Forms.ImporterForm.errorlogFilePath, "错误", "对不起，压缩文件(" + sourceFile + ")导入时出错！请检查！Ex:" + ex.ToString());
BaseModuleMainFormWithNoUIConfig.writeLog(ex.ToString());
return(string.Empty);
}
finally
{
factory.Dispose();
context.Dispose();
}
}
public SQLite(string connectionstring)
{
DbProviderFactory fact = new System.Data.SQLite.SQLiteFactory(); //DbProviderFactories.GetFactory("System.Data.SQLite");
this.connection = fact.CreateConnection();
this.connection.ConnectionString = connectionstring;
}
/// <summary>
/// 添加或替换项目
/// </summary>
/// <param name="catalogNumber">catalogNumber</param>
/// <param name="sourceFile">源文件</param>
/// <returns>CatalogID</returns>
public string addOrReplaceProject(string catalogNumber, string sourceFile)
{
//SQLite数据库工厂
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
//NDEY数据库连接
Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory);
//是否在执入后执行查询（主要针对Sqlite）
context.IsSupportSelectIdentityAfterInsert = false;
//是否在Dispose后执行GC用于解决Dispose后无法删除的问题（主要针对Sqlite）
context.IsSupportGCAfterDispose = true;
try
{
return(importDB(catalogNumber, sourceFile, context));
}
catch (Exception ex)
{
System.Console.WriteLine(ex.ToString());
return(string.Empty);
}
finally
{
factory.Dispose();
context.Dispose();
}
}
/// <summary>
/// 初始化数据库连接
/// </summary>
/// <param name="dbFile"></param>
public static void Open(string dbFile)
{
factory = new System.Data.SQLite.SQLiteFactory();
Context = new DbContext("main", "Data Source=" + dbFile, factory);
Context.IsSupportInsertAfterSelectIdentity = false;
Context.IsSupportGCAfterDispose = true;
}
private static PetaPoco.Database GetDatabase()
{
// A sqlite database is just a file.
string connectionString = "Data Source=" + HttpContext.Current.Server.MapPath("~/ingress.db") + ";Version=3;";
DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);
return(db);
}
static void Test1(string connString)
{
Console.WriteLine("Begin Test1");
using (var factory = new System.Data.SQLite.SQLiteFactory())
using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connString;
dbConn.Open();
using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
{
//create table
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
cmd.ExecuteNonQuery();
//parameterized insert
cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
var p1 = cmd.CreateParameter();
p1.ParameterName = "@id";
p1.Value = 1;
var p2 = cmd.CreateParameter();
p2.ParameterName = "@t";
p2.Value = "test1";
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
//read from the table
cmd.CommandText = @"SELECT ID, T FROM T1";
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
string t = reader.GetString(1);
Console.WriteLine("record read as id: {0} t: {1}", id, t);
}
}
cmd.Dispose();
}
if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
dbConn.Dispose();
factory.Dispose();
}
Console.WriteLine("End Test1");
}
public static void Close()
{
try
{
factory.Dispose();
}
catch (Exception ex) { }
factory = null;
try
{
Context.Dispose();
}
catch (Exception ex) { }
Context = null;
}
public static void OpenDB(string DBFile = @"veekun-pokedex.sqlite")
{
if (con != null) return;
try
{
var connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=true;", DBFile);
using (var factory = new System.Data.SQLite.SQLiteFactory())
con = factory.CreateConnection();
con.ConnectionString = connString;
con.Open();
}
catch (Exception ex)
{
Debug.WriteLine("Error opening database: {0}", ex.Message);
}
}
public static void OpenDB(string DBFile)
{
if (con != null)
{
return;
}
try
{
var connString = string.Format(@"Data Source=""{0}""; Pooling=false; FailIfMissing=false;", DBFile);
using (var factory = new System.Data.SQLite.SQLiteFactory())
con = factory.CreateConnection();
con.ConnectionString = connString;
con.Open();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Error opening database: {0}", ex.Message);
}
}
/// <summary>
/// 获得项目信息
/// </summary>
/// <param name="projectDir"></param>
/// <returns></returns>
public JiBenXinXiBiao getProjectObject(string projectDir)
{
JiBenXinXiBiao proj = null;
string dbFile = System.IO.Path.Combine(projectDir, "static.db");
if (System.IO.File.Exists(dbFile))
{
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source=" + dbFile, factory);
context.IsSupportInsertAfterSelectIdentity = false;
context.IsSupportGCAfterDispose = true;
try
{
proj = context.table("JiBenXinXiBiao").select("*").getItem <JiBenXinXiBiao>(new JiBenXinXiBiao());
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
try
{
factory.Dispose();
}
catch (Exception ex) { }
factory = null;
try
{
context.Dispose();
}
catch (Exception ex) { }
context = null;
}
}
return(proj);
}
/// <summary>
/// 添加或替换项目
/// </summary>
/// <param name="catalogNumber">catalogNumber</param>
/// <param name="sourceFile">源文件</param>
/// <returns>CatalogID</returns>
public static string addOrReplaceProject(string catalogNumber, string sourceFile)
{
//SQLite数据库工厂
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
//NDEY数据库连接
Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + sourceFile, factory);
//是否在执入后执行查询（主要针对Sqlite）
context.IsSupportInsertAfterSelectIdentity = false;
//是否在Dispose后执行GC用于解决Dispose后无法删除的问题（主要针对Sqlite）
context.IsSupportGCAfterDispose = true;
try
{
//提取所有旧的Project数据
List <Project> oldProjects = context.table("Project").select("*").getList <Project>(new Project());
//项目信息
Project mainProject = context.table("Project").where ("Type = '项目'").select("*").getItem <Project>(new Project());
//判断数据库是否有内容
if (oldProjects != null && oldProjects.Count >= 1 && !string.IsNullOrEmpty(mainProject.ID))
{
//项目名称
string projectName = mainProject.Name;
//申请人
string projectCreater = context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and Role = '负责人')").select("Name").getValue <string>(string.Empty);
//目录ID
string currentCatalogID = string.Empty;
//查询Catalog记录
Catalog catalogItem = ConnectionManager.Context.table("Catalog").where ("ProjectName='" + projectName + "' and ProjectCreater='" + projectCreater + "'").select("*").getItem <Catalog>(new Catalog());
//如果没有找到则添加新的
if (string.IsNullOrEmpty(catalogItem.CatalogID))
{
//创建一个CatalogID
currentCatalogID = Guid.NewGuid().ToString();
//添加记录
catalogItem.CatalogID = currentCatalogID;
catalogItem.CatalogNumber = catalogNumber;
catalogItem.ProjectName = projectName;
catalogItem.ProjectCreater = projectCreater;
catalogItem.ProjectCreaterUnitID = mainProject.UnitID;
catalogItem.copyTo(ConnectionManager.Context.table("Catalog")).insert();
}
else
{
//设置CatalogID
currentCatalogID = catalogItem.CatalogID;
//更新记录
catalogItem.CatalogNumber = catalogNumber;
catalogItem.ProjectCreaterUnitID = mainProject.UnitID;
catalogItem.copyTo(ConnectionManager.Context.table("Catalog")).where ("CatalogID = '" + currentCatalogID + "'").update();
}
//根据CatalogID清空本地数据库(除了Catalog表)
clearProjectDataWithCatalogID(currentCatalogID);
//先导入除Task表之外的所有表格
#region 直接导入Project ExtFileList MoneyAndYear Step ProjectAndStep WhiteList 这些表格的数据
//插入Project表
foreach (Project p in oldProjects)
{
//设置CatalogID
p.CatalogID = currentCatalogID;
//插入Project
p.copyTo(ConnectionManager.Context.table("Project")).insert();
}
//插入ExtFileList表
List <ExtFileList> oldExtFileLists = context.table("ExtFileList").select("*").getList <ExtFileList>(new ExtFileList());
foreach (ExtFileList efl in oldExtFileLists)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//插入ExtFileList
efl.copyTo(ConnectionManager.Context.table("ExtFileList")).insert();
}
//插入MoneyAndYear表
List <MoneyAndYear> oldMoneyAndYears = context.table("MoneyAndYear").select("*").getList <MoneyAndYear>(new MoneyAndYear());
foreach (MoneyAndYear efl in oldMoneyAndYears)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//插入MoneyAndYear
efl.copyTo(ConnectionManager.Context.table("MoneyAndYear")).insert();
}
//插入ProjectAndStep表
List <ProjectAndStep> oldProjectAndSteps = context.table("ProjectAndStep").select("*").getList <ProjectAndStep>(new ProjectAndStep());
foreach (ProjectAndStep efl in oldProjectAndSteps)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//插入ProjectAndStep
efl.copyTo(ConnectionManager.Context.table("ProjectAndStep")).insert();
}
//插入Step表
List <Step> oldSteps = context.table("Step").select("*").getList <Step>(new Step());
foreach (Step efl in oldSteps)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//插入Step
efl.copyTo(ConnectionManager.Context.table("Step")).insert();
}
//插入WhiteList表
List <WhiteList> oldWhiteLists = context.table("WhiteList").select("*").getList <WhiteList>(new WhiteList());
foreach (WhiteList efl in oldWhiteLists)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//插入WhiteList
efl.copyTo(ConnectionManager.Context.table("WhiteList")).insert();
}
#endregion
#region 对于Person和Unit表进行选择性导入
//插入Person表
List <Person> oldPersons = context.table("Person").select("*").getList <Person>(new Person());
foreach (Person efl in oldPersons)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
try
{
//是否被使用
object objResult = context.table("Task").where ("PersonID = '" + efl.ID + "'").select("count(*)").getValue();
if (objResult != null && int.Parse(objResult.ToString()) >= 1)
{
//有被使用，直接插入Person就好
efl.copyTo(ConnectionManager.Context.table("Person")).insert();
}
}
catch (Exception ex) { }
}
//插入Unit表
List <Unit> oldUnits = context.table("Unit").select("*").getList <Unit>(new Unit());
foreach (Unit efl in oldUnits)
{
//设置CatalogID
efl.CatalogID = currentCatalogID;
//查询Person,Project,WhiteList检查Unit有没有被使用
object objUnitExist11 = context.table("Person").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue();
object objUnitExist22 = context.table("Project").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue();
object objUnitExist33 = context.table("WhiteList").where ("UnitID='" + efl.ID + "'").select("count(*)").getValue();
int useUnitCount = 0;
try
{
if (objUnitExist11 != null && int.Parse(objUnitExist11.ToString()) >= 1)
{
useUnitCount++;
}
}
catch (Exception ex) { }
try
{
if (objUnitExist22 != null && int.Parse(objUnitExist22.ToString()) >= 1)
{
useUnitCount++;
}
}
catch (Exception ex) { }
try
{
if (objUnitExist33 != null && int.Parse(objUnitExist33.ToString()) >= 1)
{
useUnitCount++;
}
}
catch (Exception ex) { }
if (useUnitCount >= 1)
{
//插入Unit
efl.copyTo(ConnectionManager.Context.table("Unit")).insert();
}
}
#endregion
//处理Task表
#region 处理Task错误
/**
* 第一步先检查Task表是否正常(Role字段是否只有成员或负责人这二个词)
* 如果正常直接导入
* 如果不正常则尝试修复，操作如下：
* 优先处理负责人
* 先找到正确的那条负责人记录，以这条记录为基准，把错误的那条记录的任务分工和每年工作时间（月）往这个里面合并
* 再处理成员
* 先找到正确的那条成员记录，以这条记录为基准，把错误的那条记录的任务分工和每年工作时间（月）往这个里面合并
*/
//取出所有旧的Task记录
List <Task> oldTasks = context.table("Task").select("*").getList <Task>(new Task());
//正确的职务信息数量
int rightPersonCount = 0;
//给所有记录设置CatalogID并且检查数据是否正常或被修复过
foreach (Task t in oldTasks)
{
//设置CatalogID
t.CatalogID = currentCatalogID;
//判断Role里的数值是否正确并计数
if (t.Role == "成员" || t.Role == "负责人")
{
rightPersonCount++;
}
}
//判断数据库是否正常或被修复过
if (rightPersonCount == oldTasks.Count)
{
#region 可以正常导入
//1 导入项目负责人
foreach (Task t in oldTasks)
{
if (t.Type == "项目" && t.Role == "负责人")
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
break;
}
}
//2 导入课题负责人
foreach (Project p in oldProjects)
{
if (p.ID == mainProject.ID)
{
//忽略项目
continue;
}
else
{
foreach (Task t in oldTasks)
{
if (t.Type == "课题" && t.Role == "负责人" && t.ProjectID == p.ID)
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
break;
}
}
}
}
//3 导入课题成员
foreach (Project p in oldProjects)
{
if (p.ID == mainProject.ID)
{
//忽略项目
continue;
}
else
{
foreach (Task t in oldTasks)
{
if (t.Type == "课题" && t.Role == "成员" && t.ProjectID == p.ID)
{
object objExistResult = ConnectionManager.Context.table("Task").where ("IDCard='" + t.IDCard + "' and ProjectID = '" + t.ProjectID + "'").select("count(*)").getValue();
if (objExistResult != null && int.Parse(objExistResult.ToString()) >= 1)
{
continue;
}
else
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
}
}
}
}
}
#endregion
}
else
{
List <Task> tempTasks = new List <Task>();
tempTasks.AddRange(oldTasks);
oldTasks.Clear();
#region 修复课题负责人及成员
//过滤出项目负责人和课题成员
foreach (Task t in tempTasks)
{
if (t.Type == "项目")
{
if (t.Role == "负责人")
{
oldTasks.Add(t);
}
}
else if (t.Type == "课题")
{
if (t.Role.Contains("成员"))
{
t.Role = "成员";
oldTasks.Add(t);
}
}
else
{
throw new Exception("对不起，数据库中Task表有错误，请检查！文件路径：" + sourceFile);
}
}
//修复课题负责人
foreach (Project p in oldProjects)
{
if (p.Type == "项目")
{
continue;
}
Task master = null;
List <Task> masterList = new List <Task>();
foreach (Task t in tempTasks)
{
if (t.ProjectID == p.ID && t.Type == "课题" && t.Role.Contains("负责人"))
{
if (t.Role == "负责人")
{
master = t;
}
else
{
masterList.Add(t);
}
}
}
//如果没有找到不正常的负责人，只有一个正常的那就直接添加进去
if (masterList.Count == 0 && master != null)
{
oldTasks.Add(master);
}
else
{
//判断是不是有角色名称包括负责人的记录
if (masterList.Count >= 1)
{
//如果没有找到正确的记录，就取最后一个
if (master == null && masterList.Count >= 1)
{
//取最后一个记录
master = masterList[masterList.Count - 1];
//删除这个记录
masterList.Remove(master);
}
//设置Role为负责人
master.Role = "负责人";
//如果只有一个不正常的负责人，那就把Role替换成负责人直接加进去
if (masterList.Count == 0 && master != null)
{
oldTasks.Add(master);
}
else
{
//反转MasterList，从最后一个记录开始取数
masterList.Reverse();
//检查课题金额、工作时间（月）、任务分工这三个属性是否需要填充，这个时候就需要从错的那些记录里找金额
foreach (Task t in masterList)
{
//检查是否需要填充金额
if (t.TotalMoney > 0 && master.TotalMoney <= 0)
{
master.TotalMoney = t.TotalMoney;
}
//检查是否需要填充工作时间
if (t.TotalTime > 0 && master.TotalTime <= 0)
{
master.TotalTime = t.TotalTime;
}
//检查是否需要填充任务分工
if (t.Content != null && t.Content.Trim().Length >= 1 && string.IsNullOrEmpty(master.Content))
{
master.Content = t.Content;
}
}
//将修改过的负责人添加到列表中
oldTasks.Add(master);
}
}
else
{
throw new Exception("对不起，数据库中Task表有错误，请检查！文件路径：" + sourceFile);
}
}
}
#endregion
#region 导入数据
//1 导入项目负责人
foreach (Task t in oldTasks)
{
if (t.Type == "项目" && t.Role == "负责人")
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
break;
}
}
//2 导入课题负责人
foreach (Project p in oldProjects)
{
if (p.ID == mainProject.ID)
{
//忽略项目
continue;
}
else
{
foreach (Task t in oldTasks)
{
if (t.Type == "课题" && t.Role == "负责人" && t.ProjectID == p.ID)
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
break;
}
}
}
}
//3 导入课题成员
foreach (Project p in oldProjects)
{
if (p.ID == mainProject.ID)
{
//忽略项目
continue;
}
else
{
foreach (Task t in oldTasks)
{
if (t.Type == "课题" && t.Role == "成员" && t.ProjectID == p.ID)
{
object objExistResult = ConnectionManager.Context.table("Task").where ("IDCard='" + t.IDCard + "' and ProjectID = '" + t.ProjectID + "'").select("count(*)").getValue();
if (objExistResult != null && int.Parse(objExistResult.ToString()) >= 1)
{
continue;
}
else
{
t.copyTo(ConnectionManager.Context.table("Task")).insert();
}
}
}
}
}
#endregion
}
#endregion
return(currentCatalogID);
}
else
{
//空数据库
throw new Exception("对不起，数据库有错误，请检查！文件路径：" + sourceFile);
}
}
finally
{
factory.Dispose();
context.Dispose();
}
}
public void switchDB(string dbFile)
{
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
DbContext context = new DbContext("main", "Data Source=" + dbFile, factory);
//是否在执入后执行查询（主要针对Sqlite）
context.IsSupportInsertAfterSelectIdentity = false;
//是否在Dispose后执行GC用于解决Dispose后无法删除的问题（主要针对Sqlite）
context.IsSupportGCAfterDispose = true;
//ID字典(Key=表名,Value=<Key=旧的ID,新的ID>)
Dictionary <string, Dictionary <string, string> > keyDicts = new Dictionary <string, Dictionary <string, string> >();
try
{
#region 更新Project表的项目ID
List <Project> lProjects = context.table("Project").select("*").getList <Project>(new Project());
//更新项目主键ID
foreach (Project p in lProjects)
{
//检查是否需要创建字典
if (keyDicts.ContainsKey("Project") == false)
{
keyDicts["Project"] = new Dictionary <string, string>();
}
//旧的记录ID
string oldID = p.ID;
//生成新的ID
p.ID = Guid.NewGuid().ToString();
//保存新的ID
keyDicts["Project"][oldID] = p.ID;
//更新记录ID
p.copyTo(context.table("Project")).where ("ID=\"" + oldID + "\"").update();
}
//更新课题的ParentID
foreach (Project p in lProjects)
{
//遇到项目信息就不处理
if (string.IsNullOrEmpty(p.ParentID))
{
continue;
}
else
{
//在字典中查找指定的项目ID，获得新的项目ID
if (keyDicts["Project"].ContainsKey(p.ParentID))
{
p.ParentID = keyDicts["Project"][p.ParentID];
p.copyTo(context.table("Project")).where ("ID=\"" + p.ID + "\"").update();
}
else
{
writeLog("对不起，没有找到这个Project，ID为" + p.ParentID);
}
}
}
#endregion
#region 更新所有包含ProjectID的表格(除了Step和ProjectAndStep表)
List <ExtFileList> lFileList = context.table("ExtFileList").select("*").getList <ExtFileList>(new ExtFileList());
List <MoneyAndYear> lAndYear = context.table("MoneyAndYear").select("*").getList <MoneyAndYear>(new MoneyAndYear());
List <Task> lTaskList = context.table("Task").select("*").getList <Task>(new Task());
List <WhiteList> lWhiteListList = context.table("WhiteList").select("*").getList <WhiteList>(new WhiteList());
//更新表ExtFileList的ID字段
foreach (ExtFileList obj in lFileList)
{
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//替换ProjectID
if (keyDicts["Project"].ContainsKey(obj.ProjectID))
{
obj.ProjectID = keyDicts["Project"][obj.ProjectID];
}
else
{
writeLog("对不起，没有找到ProjectID为" + obj.ProjectID + "的记录！");
}
//更新记录
obj.copyTo(context.table("ExtFileList")).where ("ID=\"" + oldID + "\"").update();
}
//更新表MoneyAndYear的ID字段
foreach (MoneyAndYear obj in lAndYear)
{
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//替换ProjectID
if (keyDicts["Project"].ContainsKey(obj.ProjectID))
{
obj.ProjectID = keyDicts["Project"][obj.ProjectID];
}
else
{
writeLog("对不起，没有找到ProjectID为" + obj.ProjectID + "的记录！");
}
//更新记录
obj.copyTo(context.table("MoneyAndYear")).where ("ID=\"" + oldID + "\"").update();
}
//更新表Task的ID字段
foreach (Task obj in lTaskList)
{
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//替换ProjectID
if (keyDicts["Project"].ContainsKey(obj.ProjectID))
{
obj.ProjectID = keyDicts["Project"][obj.ProjectID];
}
else
{
writeLog("对不起，没有找到ProjectID为" + obj.ProjectID + "的记录！");
}
//更新记录
obj.copyTo(context.table("Task")).where ("ID=\"" + oldID + "\"").update();
}
//更新表WhiteList的ID字段
foreach (WhiteList obj in lWhiteListList)
{
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//替换ProjectID
if (keyDicts["Project"].ContainsKey(obj.ProjectID))
{
obj.ProjectID = keyDicts["Project"][obj.ProjectID];
}
else
{
writeLog("对不起，没有找到ProjectID为" + obj.ProjectID + "的记录！");
}
//更新记录
obj.copyTo(context.table("WhiteList")).where ("ID=\"" + oldID + "\"").update();
}
#endregion
#region 更新阶段数据
List <Step> lStepList = context.table("Step").select("*").getList <Step>(new Step());
List <ProjectAndStep> lAndStepList = context.table("ProjectAndStep").select("*").getList <ProjectAndStep>(new ProjectAndStep());
//更新Step数据
foreach (Step obj in lStepList)
{
//检查是否需要创建字典
if (keyDicts.ContainsKey("Step") == false)
{
keyDicts["Step"] = new Dictionary <string, string>();
}
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//保存新的StepID
keyDicts["Step"][oldID] = obj.ID;
//替换ProjectID
if (keyDicts["Project"].ContainsKey(obj.ProjectID))
{
obj.ProjectID = keyDicts["Project"][obj.ProjectID];
}
else
{
writeLog("对不起，没有找到ProjectID为" + obj.ProjectID + "的记录！");
}
//更新记录
obj.copyTo(context.table("Step")).where ("ID=\"" + oldID + "\"").update();
}
//更新ProjectAndStep数据
foreach (ProjectAndStep obj in lAndStepList)
{
//旧的ID
string oldID = obj.ID;
//生成新的ID
obj.ID = Guid.NewGuid().ToString();
//替换StepID
if (keyDicts["Step"].ContainsKey(obj.StepID))
{
obj.StepID = keyDicts["Step"][obj.StepID];
}
else
{
writeLog("对不起，没有找到StepID为" + obj.StepID + "的记录！");
}
//更新记录
obj.copyTo(context.table("ProjectAndStep")).where ("ID=\"" + oldID + "\"").update();
}
#endregion
}
catch (Exception ex)
{
writeLog(ex.ToString());
}
finally
{
context.Dispose();
}
}
/// <summary>
/// 从申报包中解压文档
/// </summary>
/// <param name="projectNumber">项目编号</param>
private void unZipDocFiles(string projectNumber)
{
try
{
//查找指定申报包
string[] subDirs = Directory.GetDirectories(MainForm.Config.TotalDir);
if (subDirs != null)
{
//循环子目录,查找与项目编号相符的目录名称
foreach (string s in subDirs)
{
//获得目录信息
DirectoryInfo di = new DirectoryInfo(s);
//判断目录是否符合条件
if (di.Name.Contains(projectNumber) && di.Name.Length >= 12)
{
MainForm.writeLog("开始进行项目" + projectNumber + "的解包......");
//查找子文件
string[] subFiless = Directory.GetFiles(s);
if (subFiless != null && subFiless.Length >= 1)
{
//解压这个Zip包
string pkgDir = Path.Combine(MainForm.Config.UnZipDir, projectNumber);
//删除解压目录
try
{
if (Directory.Exists(pkgDir))
{
Directory.Delete(pkgDir, true);
}
}
catch (Exception ex) { MainForm.writeLog(ex.ToString()); }
//创建解压目录
try
{
Directory.CreateDirectory(pkgDir);
}
catch (Exception ex) { MainForm.writeLog(ex.ToString()); }
//ZIP文件
string pkgZipFile = string.Empty;
//查找ZIP文件
foreach (string sssss in subFiless)
{
if (sssss.EndsWith(".zip"))
{
pkgZipFile = sssss;
break;
}
}
//判断ZIP文件是否存在
if (pkgZipFile != null && pkgZipFile.EndsWith(".zip"))
{
MainForm.writeLog("项目" + projectNumber + "的解包操作，开始ZIP文件解压");
//解压这个包
new NdeyDocFilesUnZip().UnZipFile(pkgZipFile, pkgDir, string.Empty, true);
MainForm.writeLog("项目" + projectNumber + "的解包操作，结束ZIP文件解压");
//文件目录
string fileDir = pkgDir;
//判断申报包是否有效
//生成DB文件路径
string dbFile = System.IO.Path.Combine(fileDir, "myData.db");
//判断文件是否存在
if (System.IO.File.Exists(dbFile))
{
//SQLite数据库工厂
System.Data.SQLite.SQLiteFactory factory = new System.Data.SQLite.SQLiteFactory();
//NDEY数据库连接
Noear.Weed.DbContext context = new Noear.Weed.DbContext("main", "Data Source = " + dbFile, factory);
try
{
MainForm.writeLog("项目" + projectNumber + "的解包操作，读取论文详细，科技奖项，专利情况信息...");
//提取论文详细
DataList dlRTreatises = context.table("RTreatises").orderBy("RTreatisesOrder desc").select("RTreatisesName,RTreatisesPDF").getDataList();
//提取科技奖项
DataList dlTechnologyAwards = context.table("TechnologyAwards").orderBy("TechnologyAwardsOrder desc").select("TechnologyAwardsPName,TechnologyAwardsPDF").getDataList();
//提取专利情况
DataList dlNDPatent = context.table("NDPatent").orderBy("NDPatentOrder desc").select("NDPatentName,NDPatentPDF").getDataList();
//提取推荐意见
DataList dlBaseInfor = context.table("BaseInfor").select("UnitRecommend,ExpertRecommend1,ExpertRecommend2,ExpertRecommend3").getDataList();
//提取UnitID
string unitID = context.table("BaseInfor").select("UnitID").getValue <string>(string.Empty);
string personName = context.table("BaseInfor").select("UserName").getValue <string>(string.Empty);
//需要删除的文件列表
List <string> needDeleteList = new List <string>();
needDeleteList.AddRange(Directory.GetFiles(fileDir));
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(dbFile);
MainForm.writeLog("项目" + projectNumber + "的解包操作，开始替换附件名称...");
//附件序号
int fileIndex = 0;
//整理附件名称
foreach (DataItem item in dlRTreatises.getRows())
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, item.getString("RTreatisesPDF")));
//获得文件扩展名
string extName = new FileInfo(Path.Combine(fileDir, item.getString("RTreatisesPDF"))).Extension;
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, item.getString("RTreatisesPDF")), fileIndex, item.getString("RTreatisesName"), extName);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, item.getString("RTreatisesPDF"));
}
}
foreach (DataItem item in dlTechnologyAwards.getRows())
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF")));
//获得文件扩展名
string extName = new FileInfo(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF"))).Extension;
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, item.getString("TechnologyAwardsPDF")), fileIndex, item.getString("TechnologyAwardsPName"), extName);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, item.getString("TechnologyAwardsPDF"));
}
}
foreach (DataItem item in dlNDPatent.getRows())
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, item.getString("NDPatentPDF")));
//获得文件扩展名
string extName = new FileInfo(Path.Combine(fileDir, item.getString("NDPatentPDF"))).Extension;
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, item.getString("NDPatentPDF")), fileIndex, item.getString("NDPatentName"), extName);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, item.getString("NDPatentPDF"));
}
}
//整理推荐意见附件
string unitFileA = dlBaseInfor.getRow(0).get("UnitRecommend") != null?dlBaseInfor.getRow(0).get("UnitRecommend").ToString() : string.Empty;
string personFileA = dlBaseInfor.getRow(0).get("ExpertRecommend1") != null?dlBaseInfor.getRow(0).get("ExpertRecommend1").ToString() : string.Empty;
string personFileB = dlBaseInfor.getRow(0).get("ExpertRecommend2") != null?dlBaseInfor.getRow(0).get("ExpertRecommend2").ToString() : string.Empty;
string personFileC = dlBaseInfor.getRow(0).get("ExpertRecommend3") != null?dlBaseInfor.getRow(0).get("ExpertRecommend3").ToString() : string.Empty;
//查找单位推荐意见附件
if (File.Exists(Path.Combine(fileDir, unitFileA)))
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, unitFileA));
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, unitFileA), fileIndex, "单位推荐意见", new FileInfo(Path.Combine(fileDir, unitFileA)).Extension);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, unitFileA);
}
}
//查找专家提名附件
if (File.Exists(Path.Combine(fileDir, personFileA)))
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, personFileA));
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, personFileA), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileA)).Extension);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, personFileA);
}
}
//查找专家提名附件
if (File.Exists(Path.Combine(fileDir, personFileB)))
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, personFileB));
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, personFileB), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileB)).Extension);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, personFileB);
}
}
//查找专家提名附件
if (File.Exists(Path.Combine(fileDir, personFileC)))
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(Path.Combine(fileDir, personFileC));
//文件序号+1
fileIndex++;
//文件重命名
try
{
renameFile(Path.Combine(fileDir, personFileC), fileIndex, "专家提名", new FileInfo(Path.Combine(fileDir, personFileC)).Extension);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, personFileC);
}
}
MainForm.writeLog("项目" + projectNumber + "的解包操作，结束替换附件名称...");
MainForm.writeLog("项目" + projectNumber + "的解包操作，开始处理保密资质附件和申报书文档转PDF...");
//是否已经找到Doc文件
bool isFoundDocFile = false;
//上一个保密资质修改时间
DateTime lastExtFileModifyTime = DateTime.MinValue;
//整理保密资质命名,查找Doc文件
string[] extFiles = Directory.GetFiles(fileDir);
foreach (string sss in extFiles)
{
FileInfo fii = new FileInfo(sss);
if (fii.Name.StartsWith("extFile_"))
{
try
{
//查看最后创建的文件
if (fii.LastWriteTime > lastExtFileModifyTime)
{
//只有第一个extFile才分配文件序号
if (lastExtFileModifyTime == DateTime.MinValue)
{
//文件序号+1
fileIndex++;
}
//记录当前创建时间
lastExtFileModifyTime = fii.LastWriteTime;
}
else
{
continue;
}
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(sss);
//移动文件
renameFile(sss, fileIndex, "保密资质复印件", ".png");
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, fii.Name);
}
}
else if (fii.Name.EndsWith(".doc"))
{
//移除正常的申报包文件，把不属于本次申报内容的文件留下
needDeleteList.Remove(sss);
if (fii.Name.StartsWith(unitID) && fii.Name.Contains(personName))
{
//找到Doc文件
isFoundDocFile = true;
//真实的申报书路径
string destDocFile = Path.Combine(fii.DirectoryName, "项目申报书.doc");
//文件改名
try
{
File.Move(sss, destDocFile);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
addErrorFile(projectNumber, fii.Name);
}
//转换成PDF
convertToPDF(projectNumber, destDocFile);
}
else
{
if (fii.Name.EndsWith("项目申报书.doc"))
{
try
{
File.Delete(sss);
}
catch (Exception ex) { MainForm.writeLog(ex.ToString()); }
}
}
}
}
//判断是否存在不属于本次申报包的文件
if (needDeleteList != null && needDeleteList.Count >= 1)
{
MainForm.writeLog("项目" + projectNumber + "的解包操作，开始移除不属于本次申报包的文件");
//删除不属于本次申报包的内容
foreach (string needdelete in needDeleteList)
{
try
{
File.Delete(needdelete);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
}
}
MainForm.writeLog("项目" + projectNumber + "的解包操作，开始移除不属于本次申报包的文件");
}
//判断是否找到Doc文件
if (isFoundDocFile)
{
//找到DOC文件
//MainForm.writeLog("项目申报书已找到！");
}
else
{
//没有找到Doc文件
MainForm.writeLog("对不起，没有找到项目申报书！");
//输出缺失的文件
addErrorFile(projectNumber, "项目申报书.doc");
}
MainForm.writeLog("项目" + projectNumber + "的解包操作，结束处理保密资质附件和申报书文档转PDF...");
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
}
finally
{
factory.Dispose();
context.Dispose();
context = null;
//**链接close（）和dispose（）之后任然不能释放与db文件的连接,原因是sqlite在执行SQLiteConnectionHandle.Dispose()操作时候，其实并没有真正的释放连接，只有显式调用 CLR垃圾回收之后才能真正释放连接
GC.Collect();
GC.WaitForPendingFinalizers();
//删除数据库文件
try
{
File.Delete(dbFile);
}
catch (Exception ex) { }
}
}
else
{
MainForm.writeLog("没有找到DB文件__" + projectNumber);
}
}
else
{
MainForm.writeLog("没有找到ZIP文件__" + projectNumber);
}
}
else
{
MainForm.writeLog("没有找到ZIP文件__" + projectNumber);
}
MainForm.writeLog("结束进行项目" + projectNumber + "的解包......");
}
}
}
}
catch (Exception ex)
{
MainForm.writeLog("项目编号" + projectNumber + "解包错误，Ex:" + ex.ToString());
}
}
