public static void ImportTables(string saveMdbFile, StockFile stock, List <StockTable> tables, string XZSDM, string XZSMC)
{
using (var connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", saveMdbFile)))
{
connection.Open();
foreach (var table in tables)
{
if (table.IsSpace == true)
{
//var sql= string.Format("UPDATE {0} SET XZSDM = '{1}', XZSMC = '{2}' WHERE XZSDM IS NULL", table.Name, XZSDM, XZSMC);
//var rows= ADOSQLHelper.ExecuteNoQuery(connection, sql);
//if (rows <= 0)
//{
// Console.WriteLine("更新矢量中的行政市代码和行政市名称失败");
//}
continue;
}
var sqlText = string.Format("INSERT INTO {0} SELECT {1} FROM [{2}].[{0}]", table.Name, string.Join(",", table.Fields.Select(e => e.Name).ToArray()), stock.FullName);
var rows1 = ADOSQLHelper.ExecuteNoQuery(connection, sqlText);
sqlText = string.Format("UPDATE {0} SET XZSDM = '{1}', XZSMC = '{2}',XZXDM = '{3}',XZXMC = '{4}' WHERE XZSDM IS NULL", table.Name, XZSDM, XZSMC, stock.XZQDM, stock.XZQMC);
var rows2 = ADOSQLHelper.ExecuteNoQuery(connection, sqlText);
if (rows1 == 0 || rows2 == 0 || rows1 != rows2)
{
Console.WriteLine("导入表格数据失败！");
}
}
connection.Close();
}
}
public ActionResult CreateStock(StockFile stock, IEnumerable <HttpPostedFileBase> file)
{
//if (ModelState.IsValid)
//{
foreach (var upload in file)
{
if (upload.ContentLength > 0)
{
var uploaddocs = Path.GetFileName(upload.FileName);
var path = Path.Combine(Server.MapPath("~/Content/Files"), uploaddocs);
stock.albumArtUrl = uploaddocs;
upload.SaveAs(path);
db.StockFiles.Add(stock);
db.SaveChanges();
Session["Category"].ToString();
Session["Description"].ToString();
Session["Status"].ToString();
Session["Quantity"].ToString();
Session["Cost Price"].ToString();
Session["Selling Price"].ToString();
return(View(stock));
}
}
//}
return(RedirectToAction("Index"));
}
public bool ExtractStockFilesFromSites()
{
using (var context = new StockEntities())
{
bool isExtracted = false;
var sitesWithSymbol = (from site in context.Sites
from symbol in context.StockSymbols
select new { site, symbol }).ToList();
for (int count = 0; count < sitesWithSymbol.Count; count++)
{
string fileContent = ExtractDataFromSites(sitesWithSymbol[count].site.Url.Replace("{0}", sitesWithSymbol[count].symbol.Symbol));
if(!String.IsNullOrEmpty(fileContent))
{
var stockFile = new StockFile();
stockFile.SiteId = sitesWithSymbol[count].site.ID;
stockFile.StockId = sitesWithSymbol[count].symbol.ID;
stockFile.FileName = sitesWithSymbol[count].site.Name + "_" + sitesWithSymbol[count].symbol.Symbol;
stockFile.File = fileContent;
stockFile.DownloadedDate = DateTime.Now;
context.StockFiles.AddObject(stockFile);
}
}
context.SaveChanges();
isExtracted = true;
return isExtracted;
}
}
public ActionResult DeleteConfirmed(int id)
{
StockFile stockFile = db.StockFiles.Find(id);
db.StockFiles.Remove(stockFile);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "stockNumber,stockCode,category,description,albumArtUrl,status,quantity,pricePerItem,costPrice,sellingPrice")] StockFile stockFile)
{
if (ModelState.IsValid)
{
db.Entry(stockFile).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(stockFile));
}
public ActionResult Create([Bind(Include = "stockNumber,stockCode,category,description,albumArtUrl,status,quantity,pricePerItem,costPrice,sellingPrice")] StockFile stockFile)
{
if (ModelState.IsValid)
{
stockFile.pricePerItem = stockFile.costPrice / stockFile.quantity;
db.StockFiles.Add(stockFile);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(stockFile));
}
// GET: StockFiles/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
StockFile stockFile = db.StockFiles.Find(id);
if (stockFile == null)
{
return(HttpNotFound());
}
return(View(stockFile));
}
public void TestWatcherFileFindEventAddItemIntoObservableCollection()
{
//given
var watcher = new Mock <IFolderWatcher>();
var file = new StockFile();
var vm = new MainViewModel(watcher.Object);
//when
watcher.Raise(w => w.FileFound += null, new FileFoundEventHandlerArgs {
File = file
});
//then
Assert.That(vm.Files.Count, Is.EqualTo(1));
Assert.That(vm.Files[0], Is.SameAs(file));
}
public static List <StockFile> GetFiles(string folder, string ext)
{
var list = new List <StockFile>();
#region 读取相关文件
DirectoryInfo di = new DirectoryInfo(folder);
foreach (FileInfo file in di.GetFiles(ext))
{
var entry = new StockFile
{
Path = folder,
Ext = file.Extension,
FileName = file.Name,
FullName = file.FullName
};
var fileNameString = file.Name.Substring(0, 18);
foreach (var replace in _replaceArray)
{
fileNameString = fileNameString.Replace(replace, "");
}
var index = fileNameString.IndexOf("(");
if (index > 0)
{
entry.XZQDM = fileNameString.Substring(index + 1, 6);
entry.XZQMC = fileNameString.Substring(0, index);
}
list.Add(entry);
}
#endregion
#region 读取当前目录下的文件夹
DirectoryInfo[] dis = di.GetDirectories();
foreach (var childFolder in dis)
{
var result = GetFiles(childFolder.FullName, ext);
if (result.Count > 0)
{
list.AddRange(result);
}
}
#endregion
return(list);
}
public async void TestReadFile()
{
//given
const string filePath = "c:\\d\\d\\test.txt";
var fileLines = new[] { "" };
var stockFile = new StockFile();
var fileHelper = Mock.Of <IFileReaderHelper>(h => h.ReadText(filePath) == fileLines);
var mapper = Mock.Of <ITextStockFileMapper>(m => m.Map("test", fileLines) == stockFile);
var reader = new TextStockFileReader(mapper, fileHelper);
//when
var result = await reader.ReadFile(filePath);
//then
Assert.That(result, Is.SameAs(stockFile));
}
public async void TestReadFile()
{
//given
const string filePath = "c:\\d\\d\\test.xml";
var xmlFile = new XmlStockFile();
var stockFile = new StockFile();
var fileHelper = Mock.Of <IXmlStockFileReaderHelper>(h => h.ReadXmlFile(filePath) == xmlFile);
var mapper = Mock.Of <IXmlStockFileMapper>(m => m.Map("test", xmlFile) == stockFile);
var reader = new XmlStockFileReader(mapper, fileHelper);
//when
var result = await reader.ReadFile(filePath);
//then
Assert.That(result, Is.SameAs(stockFile));
}
public static List <StockFile> GetExcelFiles(string folder, string ext)
{
var list = new List <StockFile>();
#region 读取文件夹
DirectoryInfo di = new DirectoryInfo(folder);
foreach (FileInfo file in di.GetFiles(ext))
{
var fileName = file.Name;
var key = fileName.Substring(0, 2);
if (_keyHead.Contains(key))
{
var entry = new StockFile
{
Path = folder,
Ext = file.Extension,
FileName = file.Name,
FullName = file.FullName,
TableName = key
};
var index = fileName.IndexOf("(");
if (index > 0)
{
entry.XZQDM = fileName.Substring(index + 1, 6);
entry.XZQMC = fileName.Substring(3, index - 3);
}
list.Add(entry);
}
}
#endregion
#region 读取当前目录下的文件夹
DirectoryInfo[] dis = di.GetDirectories();
foreach (var childFolder in dis)
{
var result = GetExcelFiles(childFolder.FullName, ext);
if (result.Count > 0)
{
list.AddRange(result);
}
}
#endregion
return(list);
}
private CollectExcel Program(StockFile stockfile, List <ExcelField> fields)
{
IWorkbook workbook = stockfile.FullName.OpenExcel();
if (workbook != null)
{
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
int count = 0;
IRow row = null;
ICell cell = null;
for (var i = CollectTable.StartIndex; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
break;
}
cell = row.GetCell(0);
if (cell.ToString().ToLower() == "合计".ToLower())
{
var result = Analyze(row, fields);
var collect = new CollectExcel
{
XZQDM = stockfile.XZQDM,
XZQMC = stockfile.XZQMC,
Count = count,
Values = result
};
return(collect);
}
else if (Regex.IsMatch(cell.ToString(), "^33[0-9]{7}$"))
{
count++;
}
}
}
}
return(null);
}
// GET: StockFiles/Edit/5
public ActionResult Edit(int?id)
{
var catList = new List <SelectListItem>();
var DirQuery = from e in db.StockCategories select e;
foreach (var m in DirQuery)
{
catList.Add(new SelectListItem {
Value = m.category, Text = m.category
});
}
ViewBag.catelist = catList;
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
StockFile stockFile = db.StockFiles.Find(id);
if (stockFile == null)
{
return(HttpNotFound());
}
return(View(stockFile));
}
public static void Import3(string savemdbFile, StockFile stock, List <StockTable> tables, string XZSDM, string XZSMC)
{
IWorkspace workspaceA = savemdbFile.OpenGDBWorkspace();
//IFeatureWorkspace featureWorkspaceA = workspaceA as IFeatureWorkspace;
//IWorkspaceEdit workspaceEditA = featureWorkspaceA as IWorkspaceEdit;
//workspaceEditA.StartEditing(true);
//workspaceEditA.StartEditOperation();
IWorkspace workspaceB = stock.FullName.OpenAccessFileWorkSpace();
#region 插入表
foreach (var table in tables)
{
if (table.IsSpace == false)
{
continue;
}
IFeatureClass featureClassA = workspaceA.GetFeatureClass(table.Name);
var dictA = GetFieldIndex(featureClassA, table.Fields);//字段 对应Index
#region 2
//IFeatureClassLoad featureClassLoadA = featureClassA as IFeatureClassLoad;
//ISchemaLock schemaLock = featureClassA as ISchemaLock;
//try
//{
// //schemaLock.ChangeSchemaLock(esriSchemaLock.esriExclusiveSchemaLock);
// featureClassLoadA.LoadOnlyMode = true;
// using (ComReleaser comReleaser=new ComReleaser())
// {
// IFeatureBuffer featureBufferA = featureClassA.CreateFeatureBuffer();
// comReleaser.ManageLifetime(featureBufferA);
// #region 设置地级市代码和名称
// int XZSDMIndex = featureClassA.Fields.FindField("XZSDM");
// var XZSMCIndex = featureClassA.Fields.FindField("XZSMC");
// featureBufferA.set_Value(XZSDMIndex, XZSDM);
// featureBufferA.set_Value(XZSMCIndex, XZSMC);
// #endregion
// IFeatureCursor featureCursorA = featureClassA.Insert(true);
// comReleaser.ManageLifetime(featureCursorA);
// IFeatureClass featureClassB = workspaceB.GetFeatureClass(table.Name);
// var dictB = GetFieldIndex(featureClassB, table.Fields);
// IFeatureCursor featureCursorB = featureClassB.Search(null, true);
// IFeature featureB = featureCursorB.NextFeature();
// while (featureB != null)
// {
// featureBufferA.Shape = featureB.ShapeCopy;
// #region 给每个字段赋值
// foreach (var field in table.Fields)
// {
// object val = null;
// if (dictB.ContainsKey(field.Name))
// {
// val = featureB.get_Value(dictB[field.Name]);
// }
// if (val != null)
// {
// if (dictA.ContainsKey(field.Name))
// {
// featureBufferA.set_Value(dictA[field.Name], val);
// }
// }
// }
// #endregion
// featureCursorA.InsertFeature(featureBufferA);
// featureB = featureCursorB.NextFeature();
// }
// featureCursorA.Flush();
// }
//}
//catch (Exception)
//{
//}
//finally
//{
// featureClassLoadA.LoadOnlyMode = false;
// schemaLock.ChangeSchemaLock(esriSchemaLock.esriSharedSchemaLock);
//}
#endregion
#region 1
IFeatureBuffer featureBufferA = featureClassA.CreateFeatureBuffer();
#region 设置地级市代码和名称
int XZSDMIndex = featureClassA.Fields.FindField("XZSDM");
var XZSMCIndex = featureClassA.Fields.FindField("XZSMC");
var XZXDMIndex = featureClassA.Fields.FindField("XZXDM");
var XZXMCIndex = featureClassA.Fields.FindField("XZXMC");
featureBufferA.set_Value(XZSDMIndex, XZSDM);
featureBufferA.set_Value(XZSMCIndex, XZSMC);
featureBufferA.set_Value(XZSDMIndex, stock.XZQDM);
featureBufferA.set_Value(XZXMCIndex, stock.XZQMC);
#endregion
IFeatureCursor featureCursorA = featureClassA.Insert(true);
IFeatureClass featureClassB = workspaceB.GetFeatureClass(table.Name);
var dictB = GetFieldIndex(featureClassB, table.Fields);
IFeatureCursor featureCursorB = featureClassB.Search(null, true);
IFeature featureB = featureCursorB.NextFeature();
var index = 0;
while (featureB != null)
{
featureBufferA.Shape = featureB.ShapeCopy;
#region 给每个字段赋值
object val = null;
foreach (var field in table.Fields)
{
if (dictB.ContainsKey(field.Name))
{
val = featureB.get_Value(dictB[field.Name]);
}
if (val != null)
{
if (dictA.ContainsKey(field.Name))
{
featureBufferA.set_Value(dictA[field.Name], val);
}
}
}
#endregion
featureCursorA.InsertFeature(featureBufferA);
Marshal.ReleaseComObject(featureB);
if (index % 1000 == 0)
{
featureCursorA.Flush();
}
featureB = featureCursorB.NextFeature();
}
featureCursorA.Flush();
Marshal.ReleaseComObject(featureCursorA);
Marshal.ReleaseComObject(featureCursorB);
Marshal.ReleaseComObject(featureClassB);
Marshal.ReleaseComObject(featureBufferA);
#endregion
Marshal.ReleaseComObject(featureClassA);
}
#endregion
//workspaceEditA.StopEditOperation();
//workspaceEditA.StopEditing(true);
Marshal.ReleaseComObject(workspaceB);
//Marshal.ReleaseComObject(workspaceEditA);
//Marshal.ReleaseComObject(featureWorkspaceA);
Marshal.ReleaseComObject(workspaceA);
}
