/// <summary> /// 添加 /// </summary> /// <param name="item"></param> /// <returns></returns> public bool AddBaoYangBatteryCoverArea(BaoYangBatteryCoverArea item) { var success = false; try { item.PKID = dbScopeManagerBaoYang.Execute(conn => _battery.AddBaoYangBatteryCoverArea(conn, item)); if (item.PKID > 0) { success = true; } if (success) { var key = $"BaoYangBatteryCoverAreaConfigs/{{0}}/{item.CityId}"; RemoveCache(new[] { key }); InsertBaoYangOprLog(new BaoYangOprLog { Remarks = "Add", OperateUser = _operator, NewValue = JsonConvert.SerializeObject(item), OldValue = string.Empty, IdentityID = item.PKID.ToString(), LogType = "BaoYangBatteryCoverArea", }); } } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(success); }
/// <summary> /// 新增 /// </summary> /// <param name="dbHelper"></param> /// <param name="item"></param> /// <returns></returns> public long AddBaoYangBatteryCoverArea(SqlConnection conn, BaoYangBatteryCoverArea item) { const string sql = @"INSERT INTO BaoYang.dbo.BaoYangBatteryCoverArea ( Brand , ProvinceId , CityId , Channels , IsEnabled , CreateDatetime , LastUpdateDateTime ) OUTPUT Inserted.PKID VALUES ( @Brand , @ProvinceId , @CityId , @Channels , @IsEnabled , GETDATE() , GETDATE() )"; var value = conn.ExecuteScalar(sql, new { item.ProvinceId, item.CityId, item.Channels, item.Brand, item.IsEnabled, }, commandType: CommandType.Text); return(value != null?Convert.ToInt32(value) : 0); }
/// <summary> /// 添加或者修改 /// </summary> /// <param name="item"></param> /// <returns></returns> public ActionResult AddOrUpdateBaoYangBatteryCoverArea(BaoYangBatteryCoverArea item) { Func <string> validFunc = () => { var msg = string.Empty; if (item == null || string.IsNullOrWhiteSpace(item.Brand) || item.CityId <= 0 || item.ProvinceId <= 0) { msg = "参数不能为空"; } if (_batteryManager.ExistsBaoYangBatteryCoverArea(item)) { msg = "已存在相同数据"; } var channels = item.Channels?.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Distinct().ToList() ?? new List <string>(); var tmplChannels = new List <string> { UShopChannel, OnlineChannel }; if (!channels.All(channel => tmplChannels.Contains(channel))) { msg = "渠道不是有效值"; } item.Channels = string.Join(",", channels); return(msg); }; var validResult = validFunc(); if (!string.IsNullOrEmpty(validResult)) { return(Json(new { status = false, msg = validResult })); } var success = item.PKID > 0 ? _batteryManager.UpdateBaoYangBatteryCoverArea(item) : _batteryManager.AddBaoYangBatteryCoverArea(item); return(Json(new { status = success })); }
/// <summary> /// 是否存在相同数据 /// </summary> /// <param name="item"></param> /// <returns></returns> public bool ExistsBaoYangBatteryCoverArea(BaoYangBatteryCoverArea item) { var exists = true; try { exists = dbScopeManagerBaoYangRead.Execute(conn => _battery.IsExistsBaoYangBatteryCoverArea(conn, item)); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(exists); }
/// <summary> /// 根据品牌,地区获取蓄电池数据 /// </summary> /// <param name="dbHelper"></param> /// <param name="item"></param> /// <returns></returns> public bool IsExistsBaoYangBatteryCoverArea(SqlConnection conn, BaoYangBatteryCoverArea item) { const string sql = @"SELECT COUNT(1) FROM BaoYang.dbo.BaoYangBatteryCoverArea AS t WITH ( NOLOCK ) WHERE t.Brand = @Brand AND t.ProvinceId = @ProvinceId AND t.CityId = @CityId AND t.PKID <> @PKID"; var value = conn.ExecuteScalar(sql, new { item.CityId, item.ProvinceId, item.PKID, item.Brand, }, commandType: CommandType.Text); return(Convert.ToInt32(value) > 0); }
/// <summary> /// 修改 /// </summary> /// <param name="dbHelper"></param> /// <param name="item"></param> /// <returns></returns> public bool UpdateBaoYangBatteryCoverArea(SqlConnection conn, BaoYangBatteryCoverArea item) { const string sql = @"UPDATE BaoYang.dbo.BaoYangBatteryCoverArea SET CityId = @CityId , ProvinceId = @ProvinceId , Channels = @Channels , IsEnabled = @IsEnabled WHERE PKID = @PKID"; var rows = conn.Execute(sql, new { item.PKID, item.ProvinceId, item.CityId, item.Channels, item.Brand, item.IsEnabled, }, commandType: CommandType.Text); return(rows > 0); }
/// <summary> /// 修改 /// </summary> /// <param name="item"></param> /// <returns></returns> public bool UpdateBaoYangBatteryCoverArea(BaoYangBatteryCoverArea item) { var success = false; try { var oldData = dbScopeManagerBaoYangRead.Execute(conn => _battery.SelectBaoYangBatteryCoverAreaById(conn, item.PKID)); if (oldData != null) { item.Brand = oldData.Brand; item.CreateDatetime = DateTime.Now; success = dbScopeManagerBaoYang.Execute(conn => _battery.UpdateBaoYangBatteryCoverArea(conn, item)); item.LastUpdateDateTime = DateTime.Now; } if (success) { var key1 = $"BaoYangBatteryCoverAreaConfigs/{{0}}/{item.CityId}"; var key2 = $"BaoYangBatteryCoverAreaConfigs/{{0}}/{oldData.CityId}"; RemoveCache(new[] { key1, key2 }); InsertBaoYangOprLog(new BaoYangOprLog { Remarks = "Update", OperateUser = _operator, NewValue = JsonConvert.SerializeObject(item), OldValue = JsonConvert.SerializeObject(oldData), IdentityID = item.PKID.ToString(), LogType = "BaoYangBatteryCoverArea", }); } } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(success); }
/// <summary> /// 导入数据 /// </summary> /// <returns></returns> public ActionResult ImportBaoYangBatteryCoverArea() { var list = new List <BaoYangBatteryCoverArea>(); #region 验证文件 var files = Request.Files; if (files == null || files.Count <= 0) { return(Json(new { status = false, msg = "请先选择文件上传" })); } var file = files[0]; if (file.ContentLength > 200 * 1024) { return(Json(new { Status = false, Msg = "文件大小不得超过200KB(不同品牌分批上传)" })); } if (file.ContentType != "application/vnd.ms-excel" && file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { return(Json(new { status = false, msg = "文件格式不正确, 请上传Excel文件" })); } #endregion var stream = file.InputStream; var buffer = new byte[stream.Length]; stream.Read(buffer, 0, buffer.Length); var workBook = new XSSFWorkbook(new MemoryStream(buffer)); var sheet = workBook.GetSheetAt(0); #region 读取验证excel信息 var titleRow = sheet.GetRow(sheet.FirstRowNum); var cellNum = titleRow.FirstCellNum; if (titleRow.GetCell(cellNum++)?.StringCellValue != "品牌" || titleRow.GetCell(cellNum++)?.StringCellValue != "省份" || titleRow.GetCell(cellNum++)?.StringCellValue != "城市" || titleRow.GetCell(cellNum++)?.StringCellValue != "线上展示" || titleRow.GetCell(cellNum++)?.StringCellValue != "线下展示" || titleRow.GetCell(cellNum++)?.StringCellValue != "是否隐藏") { return(Json(new { status = false, msg = "导入模板不正确,请用正确的模板导入!" })); } Func <ICell, string> getStringValueFunc = cell => { if (cell != null) { if (cell.CellType == CellType.Numeric) { return(DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff") : cell.NumericCellValue.ToString()); } return(cell.StringCellValue?.Trim()); } return(null); }; Func <string, bool> getBooleanFunc = input => { return("1".Equals(input) || "是".Equals(input) || "true".Equals(input, StringComparison.OrdinalIgnoreCase)); }; var msgs = new List <string>(); for (var rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++) { var row = sheet.GetRow(rowIndex); if (row != null) { var cellIndex = row.FirstCellNum; var brand = getStringValueFunc(row.GetCell(cellIndex++)); var province = getStringValueFunc(row.GetCell(cellIndex++)); var city = getStringValueFunc(row.GetCell(cellIndex++)); var online = getStringValueFunc(row.GetCell(cellIndex++)); var uShop = getStringValueFunc(row.GetCell(cellIndex++)); var disabled = getStringValueFunc(row.GetCell(cellIndex++)); if (!string.IsNullOrEmpty(brand) && !string.IsNullOrEmpty(city) && !string.IsNullOrEmpty(province)) { var onlineChannel = getBooleanFunc(online) ? OnlineChannel : string.Empty; var uShopChannel = getBooleanFunc(uShop) ? UShopChannel : string.Empty; var channels = $"{OnlineChannel},{uShopChannel}".Trim(','); var item = new BaoYangBatteryCoverArea { PKID = rowIndex, Brand = brand, ProvinceName = province, CityName = city, IsEnabled = !getBooleanFunc(disabled), Channels = channels, }; var existsItem = list.FirstOrDefault(x => x.Brand == item.Brand && x.ProvinceName == item.ProvinceName && x.CityName == item.CityName ); if (existsItem != null) { msgs.Add($"第{existsItem.PKID}和{item.PKID}行数据重复"); } else { list.Add(item); } } } } if (!list.Any()) { return(Json(new { status = false, msg = "导入数据不能为空!" })); } if (msgs.Any()) { return(Json(new { status = false, msg = string.Join(Environment.NewLine, msgs) })); } #endregion #region 填充核对地区信息 var provinceNames = list.Select(x => x.ProvinceName).Distinct().ToList(); var regions = provinceNames.Select(p => _batteryManager.GetRegionByRegionName(p)).ToList(); list.ForEach(x => { var region = regions.FirstOrDefault(r => r.ProvinceName == x.ProvinceName); if (region != null) { //直辖市 var city = region.IsMunicipality ? region : region.ChildRegions?.FirstOrDefault(cr => cr.CityName == x.CityName); if (city != null && city.RegionName == x.CityName) { x.CityId = city.RegionId; x.ProvinceId = city.ProvinceId; } } }); var indexs = list.Where(x => x.ProvinceId <= 0 || x.CityId <= 0).Select(x => x.PKID).ToList(); if (indexs.Any()) { return(Json(new { status = false, msg = $"第{string.Join(",", indexs)}行地区信息有误(直辖市只支持到市), 请核对" })); } #endregion #region 蓄电池品牌验证 var byManager = new BaoYangManager(); var brands = _batteryManager.GetBatteryBrands(); var ids = list.Where(x => !brands.Contains(x.Brand)).Select(x => x.PKID); if (ids.Any()) { return(Json(new { status = false, msg = $"第{string.Join(",", ids)}行蓄电池品牌不合法" })); } #endregion bool result = _batteryManager.BatchUpdateBaoYangBatteryCoverArea(list); return(Json(new { status = result })); }