/// <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 }));
}
