public Object EditSave(string parameters) { using (DBHelper db = new DBHelper()) { var options = db.First("select * from option"); if (options.content["initoverdate"] != null) { return(new { message = StringHelper.GetString("系统已经开账,不能再修改期初数据!") }); } var product = JsonConvert.DeserializeObject <TableModel>(parameters); if (product.content["initstorage"] != null) { var items = JsonConvert.DeserializeObject <Dictionary <string, InitStorageModel> >(JsonConvert.SerializeObject(product.content["initstorage"])); Dictionary <string, InitStorageModel> list = new Dictionary <string, InitStorageModel>(); foreach (string key in items.Keys) { if (key != "0" && items[key].qty.HasValue) { items[key].price = items[key].price ?? decimal.Zero; items[key].total = items[key].total ?? decimal.Zero; list.Add(key, items[key]); } } InitStorageModel stock0 = new InitStorageModel() { qty = list.Values.Sum(c => c.qty.Value), total = list.Values.Sum(c => c.total.Value) }; stock0.price = stock0.qty.Value == decimal.Zero ? decimal.Zero : Math.Round(stock0.total.Value / stock0.qty.Value, 4); list.Add("0", stock0); product.content["initstorage"] = JsonConvert.DeserializeObject <JObject>(JsonConvert.SerializeObject(list)); } var p = db.First("select * from product where id=" + product.id); p.content["initstorage"] = product.content["initstorage"]; db.Edit("product", p); db.SaveChanges(); } return(new { message = "ok" }); }
public Object ImportData(string parameters) { using (DBHelper db = new DBHelper()) { var options = db.First("select * from option"); if (options.content["initoverdate"] != null) { return(new { message = StringHelper.GetString("系统已经开账,不能再修改期初数据!") }); } } IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters); string path = dic["path"].ToString(); int rowno = 0; try { string ext = Path.GetExtension(path).ToLower(); //导入数据 IWorkbook workbook = null; FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read); if (ext == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = new XSSFWorkbook(fs); } var sheet = workbook.GetSheetAt(0); int rowcount = sheet.LastRowNum + 1; StringBuilder sb = new StringBuilder(); using (DBHelper db = new DBHelper()) { var stocks = db.Where("select * from stock where coalesce(content->>'stop','')='' order by content->>'code'"); for (int i = 1; i < rowcount; i++) { #region 逐行导入 rowno = i + 1; IRow row = sheet.GetRow(i); string code = row.GetCell(0).GetCellValue().ToString(); if (string.IsNullOrEmpty(code)) { continue; } int col = 5; var product = db.First("select * from product where content->>'code'='" + code + "'"); Dictionary <string, InitStorageModel> initstorage = new Dictionary <string, InitStorageModel>(); InitStorageModel storage0 = new InitStorageModel() { qty = decimal.Zero, total = decimal.Zero }; foreach (var stock in stocks) { decimal qty = row.GetCell(col).GetCellValue().ToString() == "" ? decimal.Zero : Convert.ToDecimal(row.GetCell(col).GetCellValue()); decimal price = row.GetCell(col + 1).GetCellValue().ToString() == "" ? decimal.Zero : Convert.ToDecimal(row.GetCell(col + 1).GetCellValue()); decimal total = qty * price; if (qty != decimal.Zero) { initstorage.Add(stock.id.ToString(), new InitStorageModel() { price = price, qty = qty, total = total }); } storage0.qty += qty; storage0.total += total; col += 2; } storage0.price = storage0.qty.Value == decimal.Zero ? decimal.Zero : Math.Round(storage0.total.Value / storage0.qty.Value, 4); initstorage.Add("0", storage0); product.content["initstorage"] = JsonConvert.DeserializeObject <JObject>(JsonConvert.SerializeObject(initstorage)); db.Edit("product", product); #endregion } if (sb.Length > 0) { db.Discard(); return(new { message = sb.ToString() }); } db.SaveChanges(); } return(new { message = "ok" }); } catch (Exception ex) { //Elmah.ErrorSignal.FromCurrentContext().Raise(ex); Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex)); return(new { message = "导入出错(" + rowno + ")" + ex.Message }); } }