public Dictionary <int, string> BackBooks(int subject, int edition)
{
List <C_E> _listdn = OMS.Common.CacheHelper.GetCache("DirectoryName") as List <C_E>;
Dictionary <int, string> _dic = new Dictionary <int, string>();
using (var db = new MOD_MetaDatabase_BranchEntities())
{
//_dic = db.tb_StandardBook.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_StandardBook]
//WHERE Deleted=0 AND Subject={0} AND Edition={1}", subject, edition)).ToDictionary(_ => _.ID, _ => _.BooKName);
var _list = db.tb_StandardBook.SqlQuery(string.Format(@" SELECT *,
RANK() OVER(PARTITION BY Edition, Subject, Grade, Booklet ORDER BY ID asc) AS Rank
FROM [dbo].[tb_StandardBook] where Deleted = 0 AND Subject={0} AND Edition={1}", subject, edition)).ToList();
if (_list.Count > 0)
{
foreach (var item in _list)
{
var _ce = _listdn.Where(_ => _.ID == item.ID.ToString()).FirstOrDefault();
if (_ce == null)
{
continue;
}
_dic.Add(item.ID, item.BooKName + "|" + _ce.Name);
}
}
}
return(_dic);
}
public Dictionary <int, string> BackEditions(int subject)
{
Dictionary <int, string> _dic = new Dictionary <int, string>();
using (var db = new MOD_MetaDatabase_BranchEntities())
{
string _edit = BackEdition(subject.ToString());
if (string.IsNullOrEmpty(_edit))
{
return(_dic);
}
_dic = db.tb_Code_ListTable3.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_Code_ListTable3]
WHERE Deleted=0 AND ID IN(
SELECT Edition FROM [dbo].[tb_StandardBook]
WHERE Deleted=0 AND Subject={0} AND Edition IN({1}) GROUP BY Edition)", subject, _edit)).ToDictionary(_ => _.ID, _ => _.CodeName);
}
return(_dic);
}
private void btnok_Click(object sender, EventArgs e)
{
//电子书资源存放位置
string _ResourceUrl = ConfigurationManager.AppSettings["ResourceUrl"].ToString();
//资源文件存放目录
string _OtherUrl = ConfigurationManager.AppSettings["OtherUrl"].ToString();
//缩略图存放位置
string _ImgUrl = ConfigurationManager.AppSettings["ImgUrl"].ToString();
string _directory = System.IO.Directory.GetCurrentDirectory() + "/" + DateTime.Now.ToString("yyyyMMddHHmmss");
Dictionary <int, string> _dicChinese = new Dictionary <int, string>();
Dictionary <int, string> _dicMath = new Dictionary <int, string>();
Dictionary <int, string> _dicEnglish = new Dictionary <int, string>();
Dictionary <int, string> _dicC = Helper.BackEditions(1);
Dictionary <int, string> _dicM = Helper.BackEditions(2);
Dictionary <int, string> _dicE = Helper.BackEditions(3);
bool _chinese = this.chkChinese.Checked;
bool _math = this.chkMath.Checked;
bool _english = this.chkEnglish.Checked;
#region 验证信息
if (!_chinese && !_math && !_english)
{
MessageBox.Show("请至少勾选一个科目！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
_dicChinese = Helper.Vali(cklChinese, _chinese);
_dicMath = Helper.Vali(cklMath, _math);
_dicEnglish = Helper.Vali(cklEnglish, _english);
if (_chinese)
{
if (!Helper.ValiData(_dicC, _dicChinese))
{
MessageBox.Show("请至少勾选一个语文版本！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (_math)
{
if (!Helper.ValiData(_dicM, _dicMath))
{
MessageBox.Show("请至少勾选一个数学版本！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (_english)
{
if (!Helper.ValiData(_dicE, _dicEnglish))
{
MessageBox.Show("请至少勾选一个英语版本！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
#endregion
this.btnok.Visible = false;//隐藏导出按钮，避免用户在前一次没完成之前，开始第二次
StringBuilder _strb = new StringBuilder();
StringBuilder _subject = new StringBuilder();
if (_chinese)
{
Helper.JoinData(_dicChinese, _strb, 1, _subject);
}
if (_math)
{
Helper.JoinData(_dicMath, _strb, 2, _subject);
}
if (_english)
{
Helper.JoinData(_dicEnglish, _strb, 3, _subject);
}
List <Editions> _list = new List <Editions>();
string _url = _directory + "/Data/Common/";
if (!Directory.Exists(_url))
{
Directory.CreateDirectory(_url);
}
List <Books> _lb = new List <Books>();
string _bookids = string.Empty; //记录书本
List <Resources> _lrs = new List <Resources>(); //记录碎片化资源
StringBuilder _where = new StringBuilder(); //查询条件
using (var db = new MOD_MetaDatabase_BranchEntities())
{
var query = db.tb_Code_ListTable3.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_Code_ListTable3]
WHERE Deleted = 0 AND CodeName IN({0})", _strb.ToString().TrimEnd(','))).ToList();
if (query.Count > 0)
{
#region 导出版本
int i = 1;
foreach (var item in query)
{
_list.Add(new Editions
{
EditionId = item.ID,
EditionName = item.CodeName,
Sort = i
});
i++;
}
Helper.CreateXML(_url + "/edition.xml", _list);
#endregion
#region 拼接查询条件
if (_chinese)
{
Helper.JoinCondi(_dicChinese, _where, 1);
}
if (_math)
{
Helper.JoinCondi(_dicMath, _where, 2);
}
if (_english)
{
Helper.JoinCondi(_dicEnglish, _where, 3);
}
#endregion
var query1 = db.tb_StandardBook.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_StandardBook]
WHERE Deleted=0 AND ({0})", _where.ToString().Remove(_where.ToString().Length - 3, 3))).ToList();
List <tb_TextBook> _ltb = Helper.BackTB();
if (query1.Count > 0 && _ltb.Count > 0)
{
var _list_result = (from t in query1
from r in _ltb
where t.ID == r.ClssId
select t).ToList();
if (_list_result.Count == 0)
{
return;
}
#region 导出书
foreach (var item in _list_result)
{
_lb.Add(new Books
{
BookId = item.ID,
Booklet = item.Booklet,
BookName = item.BooKName,
Subject = item.Subject,
Grade = item.Grade,
Edition = item.Edition,
Remark = item.Remark,
});
}
Helper.CreateXML(_url + "/book.xml", _lb);
#endregion
_list_result.ForEach(_ => _bookids += _.ID + ",");
var query2 = db.tb_StandardCatalog.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_StandardCatalog]
WHERE Deleted = 0 AND BookID IN({0})", _bookids.TrimEnd(','))).ToList();
if (query2.Count > 0)
{
#region 导出目录
List <BookCatalogs> _lbc = new List <BookCatalogs>();
foreach (var item in query2)
{
_lbc.Add(new BookCatalogs
{
CatalogId = item.ID,
BookId = item.BookID,
PId = item.ParentID,
CatalogName = item.FolderName,
Sort = item.Seq
});
}
Helper.CreateXML(_url + "/bookcatalog.xml", _lbc);
#endregion
}
}
}
var query3 = db.tb_Code_TreeTable2.Where(_ => _.Deleted == 0).ToList();
if (query3.Count > 0)
{
#region 导出资源类型
List <ResourceTypes> _lrty = new List <ResourceTypes>();
foreach (var item in query3)
{
_lrty.Add(new ResourceTypes()
{
ID = item.ID,
CodeName = item.CodeName,
ParentID = item.ParentID,
Sort = item.Seq == null ? "" : item.Seq.ToString()
});
}
Helper.CreateXML(_url + "/resourcetype.xml", _lrty);
#endregion
}
}
#region 导出教材每页默认关联资源
using (var db = new fz_basicEntities())
{
List <BookRes> _lbr = new List <BookRes>();
var query = db.dict_textbook_resource.SqlQuery(string.Format(@" SELECT * FROM [dbo].[dict_textbook_resource]
WHERE BookId IN({0})", _bookids.TrimEnd(','))).ToList();
if (query.Count > 0)
{
foreach (var item in query)
{
_lbr.Add(new BookRes
{
BookId = item.BookId,
Content = item.Content,
IsSys = 1,
PageIndex = item.PageIndex
});
}
Helper.CreateXML(_url + "/bookres.xml", _lbr);
}
}
#endregion
using (var db = new MOD_kingfiles_ZYC_BranchEntities())
{
var query = db.Database.SqlQuery <Files>(string.Format(@" SELECT a.*,b.Catalog,b.SchoolStage,b.Grade,b.Subject,
b.Edition,b.BookReel,b.ResourceClass,b.ResourceStyle,b.ResourceType,b.ComeFrom,b.KeyWords,b.Title,b.Description,c.BookID
FROM [MOD_kingfiles_ZYC_Branch].[dbo].[tb_Files] a
INNER JOIN [MOD_Resource_Branch].[dbo].[tb_Resource] b ON a.ID=b.FileID AND b.IsDelete=0
INNER JOIN [MOD_Meta_Branch].[dbo].[tb_StandardCatalog] c ON b.Catalog=c.ID AND c.Deleted=0
WHERE a.ID IN(SELECT FileID FROM [MOD_Resource_Branch].[dbo].[tb_Resource] WHERE IsDelete=0)
AND c.BookID IN({0})", _bookids.TrimEnd(','))).ToList();
if (query.Count > 0)
{
#region 导出碎片化资源
foreach (var item in query)
{
_lrs.Add(new Resources
{
BookId = item.BookID.ToString(),
Catalog1 = item.Catalog.ToString(),
ResId = item.ID.ToString(),
Url = item.FilePath,
FileName = item.FileName,
BookReel = item.BookReel == null ? "" : item.BookReel.ToString(),
ComeFrom = item.ComeFrom,
Cover = "/Resources/KingsunFiles/" + item.FilePath.Replace("\\", "/") + "/thumb/" + item.ID + ".jpg",
Edition = item.Edition == null ? "" : item.Edition.ToString(),
ResourceClass = item.ResourceClass == null ? "" : item.ResourceClass.ToString(),
ResourceStyle = item.ResourceStyle == null ? "" : item.ResourceStyle.ToString(),
ResourceType = item.ResourceType == null ? "" : item.ResourceType.ToString(),
Title = item.Title,
KeyWords = item.KeyWords,
Description = item.Description,
SchoolStage = item.SchoolStage == null ? "" : item.SchoolStage.ToString(),
Subject = item.Subject == null ? "" : item.Subject.ToString(),
Grade = item.Grade == null ? "" : item.Grade.ToString(),
Extension = item.FileExtension
});
if (this.chkResource.Checked)
{
Helper.CopyImg(_ImgUrl + "/" + item.ID + ".jpg", _directory + "/KingsunFiles/" + item.FilePath + "/thumb");
}
}
Helper.CreateXML(_url + "/resources.xml", _lrs);
#endregion
}
}
#region 制资源到指定目录
if (this.chkBook.Checked)
{
List <tb_TextBook> _listtb = Helper.BackTB();
if (_lb.Count > 0 && _listtb.Count > 0)
{
var _list_result = (from t in _listtb
from r in _lb
where t.ClssId == r.BookId
select t).ToList();
if (_list_result.Count == 0)
{
return;
}
Dictionary <int, string> _dicBook = new Dictionary <int, string>();
_list_result.ForEach(_ =>
{
if (!_dicBook.ContainsKey(Convert.ToInt32(_.ClssId)))
{
_dicBook.Add(Convert.ToInt32(_.ClssId), _.TextBookPath.Split('/')[2].ToString());
}
});
foreach (var item in _dicBook)
{
Helper.CopyFile(_ResourceUrl + "/" + item.Value, _directory + "/TextBook/" + item.Key);
}
//导出json数据
Helper.CreateJson(_directory + "/TextBook/", _lb);
}
}
#endregion
#region 制碎片化资源到指定文件夹
if (this.chkResource.Checked)
{
if (_lrs.Count > 0)
{
ArrayList _arr = new ArrayList();
foreach (var item in _lrs)
{
Helper.CopyFile(_OtherUrl + "/" + item.Url + "/" + item.ResId, _directory + "/KingsunFiles/" + item.Url + "/" + item.ResId);
if (_arr.Contains(item.Url))
{
continue;
}
Helper.CopyOtherFile(_OtherUrl + "/" + item.Url, _directory + "/KingsunFiles/" + item.Url, _lrs);
_arr.Add(item.Url);
}
}
}
#endregion
if (this.chkDec.Checked)
{
//加密数据
Helper.EncryData(_directory, _directory + "Encry");
}
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
this.btnok.Visible = true;//导出完成后，显示导出按钮
}
private void btnExport_Click(object sender, EventArgs e)
{
CallRecursive(this.treeView1);
if (_list.Count == 0)
{
MessageBox.Show("请至少勾选一本书！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
this.btnExport.Visible = false;//隐藏导出按钮，避免用户在前一次没完成之前，开始第二次
//电子书资源存放位置
string _ResourceUrl = ConfigurationManager.AppSettings["ResourceUrl"].ToString();
//资源文件存放目录
string _OtherUrl = ConfigurationManager.AppSettings["OtherUrl"].ToString();
//缩略图存放位置
string _ImgUrl = ConfigurationManager.AppSettings["ImgUrl"].ToString();
string _directory = System.IO.Directory.GetCurrentDirectory() + "/" + DateTime.Now.ToString("yyyyMMddHHmmss");
List <C_E> _listdn = OMS.Common.CacheHelper.GetCache("DirectoryName") as List <C_E>;
foreach (var items in _list)
{
if (items.Text.Split('|').Count() <= 1)
{
continue;
}
int _bookId = Convert.ToInt32(_listdn.Where(_ => _.Name == items.Text.Split('|')[1]).FirstOrDefault().ID);
if (_bookId == 0)
{
continue;
}
List <Editions> _listediti = new List <Editions>();
List <Books> _lb = new List <Books>();
List <SE> _lse = new List <SE>(); //用于记录用户所勾选的科目、版本
List <Resources> _lrs = new List <Resources>(); //记录碎片化资源
string _url = _directory + "/" + _listdn.Where(_ => _.ID == _bookId.ToString()).FirstOrDefault().Name + "/Applications/";
if (!Directory.Exists(_url))
{
Directory.CreateDirectory(_url);
}
string _xmlurl = _url + "/Data";
if (!Directory.Exists(_xmlurl))
{
Directory.CreateDirectory(_xmlurl);
}
using (var db = new MOD_MetaDatabase_BranchEntities())
{
var query = db.tb_Code_ListTable3.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_Code_ListTable3]
WHERE Deleted = 0 AND ID IN(SELECT Edition FROM [dbo].[tb_StandardBook] WHERE Deleted=0 AND ID IN({0}))", _bookId)).ToList();
if (query.Count > 0)
{
var query1 = db.tb_StandardBook.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_StandardBook]
WHERE Deleted=0 AND ID IN ({0})", _bookId)).ToList();
// _lse = db.Database.SqlQuery<SE>(string.Format(@"SELECT Subject,Edition FROM [dbo].[tb_StandardBook]
//WHERE Deleted=0 AND ID IN({0})
//GROUP BY Subject,Edition", _bookId)).ToList();
if (query1.Count > 0)
{
var query2 = db.tb_StandardCatalog.SqlQuery(string.Format(@" SELECT * FROM [dbo].[tb_StandardCatalog]
WHERE Deleted = 0 AND BookID IN(SELECT ID FROM [dbo].[tb_StandardBook] WHERE Deleted=0 AND ID IN({0}))", _bookId)).ToList();
if (query2.Count > 0)
{
#region 导出目录
List <BookCatalogs> _lbc = new List <BookCatalogs>();
foreach (var item in query2)
{
_lbc.Add(new BookCatalogs
{
CatalogId = item.ID,
BookId = item.BookID,
PId = item.ParentID,
CatalogName = item.FolderName,
Sort = item.Seq
});
}
Helper.CreateXML(_xmlurl + "/bookcatalog.xml", _lbc);
#endregion
}
}
}
var query3 = db.tb_Code_TreeTable2.Where(_ => _.Deleted == 0).ToList();
if (query3.Count > 0)
{
#region 导出资源类型
List <ResourceTypes> _lrty = new List <ResourceTypes>();
foreach (var item in query3)
{
if (string.IsNullOrEmpty(mdt.dict.ResType.GetVal(Convert.ToInt32(item.ID))))
{
continue;
}
_lrty.Add(new ResourceTypes()
{
ID = item.ID,
CodeName = item.CodeName,
ParentID = item.ParentID,
Sort = item.Seq == null ? "" : item.Seq.ToString()
});
}
Helper.CreateXML(_xmlurl + "/resourcetype.xml", _lrty);
#endregion
}
}
List <Resource> _list_resource = new List <Resource>();
using (var db = new MOD_Resource_BranchEntities())
{
_list_resource = db.Database.SqlQuery <Resource>(string.Format(@"SELECT b.FileID,b.Catalog,b.SchoolStage,b.Grade,b.Subject,b.Edition,b.BookReel,b.ResourceClass,b.ResourceStyle,b.ResourceType,b.ComeFrom,b.KeyWords,b.Title,b.Description,c.ParentID,c.BookID
FROM [MOD_ResourceLibrary].[dbo].[tb_Resource] b
INNER JOIN [MOD_MetaDatabase].[dbo].[tb_StandardCatalog] c ON b.Catalog=c.ID AND c.Deleted=0
WHERE c.BookID= {0} and b.IsDelete= 0", _bookId)).ToList();
}
if (_list_resource.Count > 0)
{
using (var db = new MOD_kingfiles_ZYC_BranchEntities())
{
foreach (var res in _list_resource)
{
if (string.IsNullOrEmpty(mdt.dict.ResType.GetVal(Convert.ToInt32(res.ResourceStyle))) && res.ResourceType != 10)
{
continue;
}
var _qu = db.tb_Files.Where(_ => _.ID == res.FileID).ToList();
if (_qu.Count > 0)
{
foreach (var itqu in _qu)
{
_lrs.Add(new Resources
{
BookId = res.BookID.ToString(),
Catalog1 = res.Catalog.ToString(),
ResId = itqu.ID.ToString(),
Url = itqu.FilePath,
FileName = itqu.FileName,
BookReel = res.BookReel == null ? "" : res.BookReel.ToString(),
ComeFrom = res.ComeFrom,
Cover = Helper.IsExist(_ImgUrl + "/" + itqu.ID + ".jpg") == true ? _listdn.Where(_ => _.ID == _bookId.ToString()).FirstOrDefault().Name + "/Applications/KingsunFiles/" + itqu.FilePath.Replace("\\", "/") + "/thumb/" + itqu.ID + ".jpg" : "",
Edition = res.Edition == null ? "" : res.Edition.ToString(),
ResourceClass = res.ResourceClass == null ? "" : res.ResourceClass.ToString(),
ResourceStyle = res.ResourceStyle == null ? "" : res.ResourceStyle.ToString(),
ResourceType = res.ResourceType == null ? "" : res.ResourceType.ToString(),
Title = res.Title,
KeyWords = res.KeyWords,
Description = res.Description,
SchoolStage = res.SchoolStage == null ? "" : res.SchoolStage.ToString(),
Subject = res.Subject == null ? "" : res.Subject.ToString(),
Grade = res.Grade == null ? "" : res.Grade.ToString(),
Extension = itqu.FileExtension,
ParentID = res.ParentID
});
if (this.chkResource.Checked)
{
Helper.CopyImg(_ImgUrl + "/" + itqu.ID + ".jpg", _url + "/KingsunFiles/" + itqu.FilePath + "/thumb");
}
}
}
}
}
}
if (_lrs.Count > 0)
{
Helper.CreateXML(_xmlurl + "/resources.xml", _lrs);
}
//using (var db = new MOD_kingfiles_ZYC_BranchEntities())
//{
// var query = db.Database.SqlQuery<Files>(string.Format(@" SELECT a.*,b.Catalog,b.SchoolStage,b.Grade,b.Subject,
// b.Edition,b.BookReel,b.ResourceClass,b.ResourceStyle,b.ResourceType,b.ComeFrom,b.KeyWords,b.Title,b.Description,c.ParentID,c.BookID
// FROM [MOD_KingsunFiles].[dbo].[tb_Files] a
// INNER JOIN [MOD_ResourceLibrary].[dbo].[tb_Resource] b ON a.ID=b.FileID AND b.IsDelete=0
// INNER JOIN [MOD_MetaDatabase].[dbo].[tb_StandardCatalog] c ON b.Catalog=c.ID AND c.Deleted=0
// WHERE a.ID IN(SELECT FileID FROM [MOD_ResourceLibrary].[dbo].[tb_Resource] WHERE IsDelete=0)
// AND c.BookID= {0}", _bookId)).ToList();
// if (query.Count > 0)
// {
// #region 导出碎片化资源
// foreach (var item in query)
// {
// if (item.ParentID == 0)
// {
// if (item.ResourceType != 10)
// continue;
// }
// else
// {
// if (string.IsNullOrEmpty(mdt.dict.ResType.GetVal(Convert.ToInt32(item.ResourceStyle))))
// continue;
// }
// _lrs.Add(new Resources
// {
// BookId = item.BookID.ToString(),
// Catalog1 = item.Catalog.ToString(),
// ResId = item.ID.ToString(),
// Url = item.FilePath,
// FileName = item.FileName,
// BookReel = item.BookReel == null ? "" : item.BookReel.ToString(),
// ComeFrom = item.ComeFrom,
// Cover = Helper.IsExist(_ImgUrl + "/" + item.ID + ".jpg") == true ? "/Resources/KingsunFiles/" + item.FilePath.Replace("\\", "/") + "/thumb/" + item.ID + ".jpg" : "",
// Edition = item.Edition == null ? "" : item.Edition.ToString(),
// ResourceClass = item.ResourceClass == null ? "" : item.ResourceClass.ToString(),
// ResourceStyle = item.ResourceStyle == null ? "" : item.ResourceStyle.ToString(),
// ResourceType = item.ResourceType == null ? "" : item.ResourceType.ToString(),
// Title = item.Title,
// KeyWords = item.KeyWords,
// Description = item.Description,
// SchoolStage = item.SchoolStage == null ? "" : item.SchoolStage.ToString(),
// Subject = item.Subject == null ? "" : item.Subject.ToString(),
// Grade = item.Grade == null ? "" : item.Grade.ToString(),
// Extension = item.FileExtension,
// ParentID = item.ParentID
// });
// if (this.chkResource.Checked)
// Helper.CopyImg(_ImgUrl + "/" + item.ID + ".jpg", _url + "/KingsunFiles/" + item.FilePath + "/thumb");
// }
// if (_lrs.Count > 0)
// Helper.CreateXML(_xmlurl + "/resources.xml", _lrs);
// #endregion
// }
// //}
//}
#region 制碎片化资源到指定文件夹
if (this.chkResource.Checked)
{
if (_lrs.Count > 0)
{
ArrayList _arr = new ArrayList();
foreach (var item in _lrs)
{
//Helper.CopyFile(_OtherUrl + "/" + item.Url + "/" + item.ResId, _url + "/KingsunFiles/" + item.Url + "/" + item.ResId);
if (_arr.Contains(item.Url))
{
continue;
}
Helper.CopyOtherFile(_OtherUrl + "/" + item.Url, _url + "/KingsunFiles/" + item.Url, _lrs);
_arr.Add(item.Url);
}
}
}
#endregion
}
if (this.chkDec.Checked)
{
//加密数据
Helper.EncryData(_directory, _directory + "Encry");
}
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
this.btnExport.Visible = true;//导出完成后，显示导出按钮
}