/// <summary>
/// 判断年检配置是否重复
/// 同PID同城市(车牌前缀)，只能有一条启用的数据
/// PID、车牌前缀、联系电话 作唯一性约束
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public static bool IsExistAnnualInspectionAgent
(SqlConnection conn, VehicleAnnualInspectionAgentModel model)
{
#region Sql
var sql = @"SELECT COUNT(1)
FROM Configuration..VehicleAnnualInspectionAgent AS s WITH ( NOLOCK )
WHERE s.ServicePid = @ServicePid
AND s.CarNoPrefix = @CarNoPrefix
AND s.IsDeleted=0
AND ( ( s.IsEnabled = 1
AND s.IsEnabled = @IsEnabled
)
OR s.TelNum = @TelNum
)
AND s.PKID <> @PKID;";
#endregion
var parameters = new[]
{
new SqlParameter("@ServicePid", model.ServicePid),
new SqlParameter("@CarNoPrefix", model.CarNoPrefix),
new SqlParameter("@IsEnabled", model.IsEnabled),
new SqlParameter("@TelNum", model.TelNum),
new SqlParameter("@PKID", model.PKID)
};
var count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters));
return(count > 0);
}
/// <summary>
/// 更新年检代办配置
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateVehicleAnnualInspectionAgent(SqlConnection conn, VehicleAnnualInspectionAgentModel model)
{
#region Sql
var sql = @"UPDATE Configuration..VehicleAnnualInspectionAgent
SET VenderShortName = @VenderShortName ,
SalePrice = @SalePrice ,
CostPrice = @CostPrice ,
IsEnabled = @IsEnabled ,
IsDeleted = @IsDeleted ,
Contact = @Contact ,
TelNum = @TelNum ,
OfficeAddress = @OfficeAddress ,
Remarks = @Remarks ,
LastUpdateDateTime = GETDATE()
WHERE PKID = @PKID;";
#endregion
var parameters = new[]
{
new SqlParameter("@VenderShortName", model.VenderShortName),
new SqlParameter("@SalePrice", model.SalePrice),
new SqlParameter("@CostPrice", model.CostPrice),
new SqlParameter("@IsEnabled", model.IsEnabled),
new SqlParameter("@IsDeleted", model.IsDeleted),
new SqlParameter("@Contact", model.Contact),
new SqlParameter("@TelNum", model.TelNum),
new SqlParameter("@OfficeAddress", model.OfficeAddress),
new SqlParameter("@PKID", model.PKID),
new SqlParameter("@Remarks", model.Remarks)
};
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters) > 0);
}
public static int AddVehicleOnlineAnnualInspectionAgent(SqlConnection conn, VehicleAnnualInspectionAgentModel model)
{
#region SQL
var sql = @"INSERT INTO Configuration..VehicleAnnualInspectionAgent
( ServicePid ,
CarNoPrefix ,
VenderShortName ,
SalePrice ,
CostPrice ,
IsEnabled ,
Contact ,
TelNum ,
OfficeAddress,
Remarks,
Province,
City,
Village,
Email
)
VALUES ( @ServicePid ,
@CarNoPrefix ,
@VenderShortName ,
@SalePrice ,
@CostPrice ,
@IsEnabled ,
@Contact ,
@TelNum ,
@OfficeAddress,
@Remarks,
@Province,
@City,
@Village,
@Email
)
SELECT SCOPE_IDENTITY();";
#endregion
var parameters = new[]
{
new SqlParameter("@ServicePid", model.ServicePid ?? ""),
new SqlParameter("@CarNoPrefix", model.CarNoPrefix ?? ""),
new SqlParameter("@VenderShortName", model.VenderShortName ?? ""),
new SqlParameter("@SalePrice", model.SalePrice),
new SqlParameter("@CostPrice", model.CostPrice),
new SqlParameter("@IsEnabled", model.IsEnabled),
new SqlParameter("@Contact", model.Contact ?? ""),
new SqlParameter("@TelNum", model.TelNum ?? ""),
new SqlParameter("@OfficeAddress", model.OfficeAddress ?? ""),
new SqlParameter("@Remarks", model.Remarks ?? ""),
new SqlParameter("@Province", model.Province ?? ""),
new SqlParameter("@City", model.City ?? ""),
new SqlParameter("@Village", model.Village ?? ""),
new SqlParameter("@Email", model.Email ?? "")
};
return(Convert.ToInt32(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters)));
}
/// <summary>
/// 判断年检配置唯一性
/// 同PID同城市(车牌前缀)，只能有一条启用的数据
/// PID、车牌前缀、供应商简称 作唯一性约束
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool IsExistAnnualInspectionAgent(VehicleAnnualInspectionAgentModel model)
{
var result = true;
try
{
result = dbScopeManagerConfigurationRead.Execute
(conn => DalVehicleAnnualInspectionAgent.IsExistAnnualInspectionAgent(conn, model));
}
catch (Exception ex)
{
Logger.Error("IsExistAnnualInspectionAgent", ex);
}
return(result);
}
public ActionResult AddAgentConfig(VehicleAnnualInspectionAgentModel model)
{
if (model == null)
{
return(Json(new { Status = false, Msg = "未知的对象" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.ServicePid))
{
return(Json(new { Status = false, Msg = "请选择服务Pid" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.CarNoPrefix))
{
return(Json(new { Status = false, Msg = "请选择车牌前缀" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.VenderShortName))
{
return(Json(new { Status = false, Msg = "供应商简称不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.Contact))
{
return(Json(new { Status = false, Msg = "联系人不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.TelNum))
{
return(Json(new { Status = false, Msg = "联系电话不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.OfficeAddress))
{
return(Json(new { Status = false, Msg = "邮寄地址不能为空" }, JsonRequestBehavior.AllowGet));
}
if (model.SalePrice < 0 || model.CostPrice < 0)
{
return(Json(new { Status = false, Msg = "成本或售价不能为负数" }, JsonRequestBehavior.AllowGet));
}
var manager = new VehicleAnnualInspectionAgentManager();
var user = User.Identity.Name;
var isExist = manager.IsExistAnnualInspectionAgent(model);
if (isExist)
{
return(Json(new { Status = false, Msg = "已存在重复的数据，不能重复添加" }, JsonRequestBehavior.AllowGet));
}
var result = manager.AddVehicleAnnualInspectionAgent(model, user);
return(Json(new { Status = result, Msg = $"添加{(result ? "成功" : "失败")}" }, JsonRequestBehavior.AllowGet));
}
public ActionResult UpdateAgentConfig(VehicleAnnualInspectionAgentModel model)
{
if (model == null || string.IsNullOrWhiteSpace(model.ServicePid) || string.IsNullOrWhiteSpace(model.CarNoPrefix) || string.IsNullOrWhiteSpace(model.TelNum))
{
return(Json(new { Status = false, Msg = "未知的编辑对象" }, JsonRequestBehavior.AllowGet));
}
var manager = new VehicleAnnualInspectionAgentManager();
var isExist = manager.IsExistAnnualInspectionAgent(model);
if (isExist)
{
return(Json(new { Status = false, Msg = "已存在重复的数据，不能重复编辑" }, JsonRequestBehavior.AllowGet));
}
var user = User.Identity.Name;
var result = manager.UpdateVehicleAnnualInspectionAgent(model, user);
return(Json(new { Status = result, Msg = $"编辑{(result ? "成功" : "失败")}" }, JsonRequestBehavior.AllowGet));
}
public bool AddVehicleOnlineAnnualInspectionAgent(VehicleAnnualInspectionAgentModel model, string user)
{
var result = false;
try
{
var oldValue = GetVehicleOnlineAnnualInspectionAgent(model.ServicePid, model.Province, model.City, model.Village);
if (oldValue == null)
{
var pkid = dbScopeManagerConfiguration.Execute
(conn => DalVehicleAnnualInspectionAgent.AddVehicleOnlineAnnualInspectionAgent(conn, model));
result = pkid > 0;
model.PKID = pkid;
}
else if (oldValue.IsDeleted)
{
model.PKID = oldValue.PKID;
result = dbScopeManagerConfiguration.Execute
(conn => DalVehicleAnnualInspectionAgent.UpdateVehicleOnlineAnnualInspectionAgent(conn, model));
}
model.CreateDateTime = DateTime.Now;
model.LastUpdateDateTime = DateTime.Now;
var log = new AnnualInspectionOprLogModel
{
LogType = "VehicleOnlineAnnualInspectionAgent",
IdentityId = $"{model.Province}_{model.City}_{model.Village}",
OperationType = "Add",
OldValue = null,
NewValue = JsonConvert.SerializeObject(model),
Remarks = $"添加{model.Province}_{model.City}_{model.Village},供应商:{model.VenderShortName}," +
$"服务Pid:{model.ServicePid},联系电话:{model.TelNum},联系邮箱:{model.Email} " +
$"的{model.ServiceName}服务",
Operator = user,
};
LoggerManager.InsertLog("AnnualInspectOprLog", log);
}
catch (Exception ex)
{
result = false;
Logger.Error("AddVehicleOnlineAnnualInspectionAgent", ex);
}
return(result);
}
/// <summary>
/// 判断线上年检配置是否重复
/// 同城市，只能有一条启用的数据
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public static bool IsExistOnlineAnnualInspectionAgent
(SqlConnection conn, VehicleAnnualInspectionAgentModel model)
{
#region Sql
var sql = @"SELECT COUNT(1)
FROM Configuration..VehicleAnnualInspectionAgent AS s WITH ( NOLOCK )
WHERE s.ServicePid = @ServicePid
AND s.Province = @Province AND s.City = @City AND s.Village = @Village
AND s.IsDeleted=0
";
#endregion
var parameters = new[]
{
new SqlParameter("@ServicePid", model.ServicePid),
new SqlParameter("@Province", model.Province),
new SqlParameter("@City", model.City),
new SqlParameter("@Village", model.Village)
};
var count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters));
return(count > 0);
}
/// <summary>
/// 更新年检代办配置
/// </summary>
/// <param name="model"></param>
/// <param name="user"></param>
/// <returns></returns>
public bool UpdateVehicleAnnualInspectionAgent
(VehicleAnnualInspectionAgentModel model, string user)
{
var result = false;
try
{
var oldValue = GetVehicleAnnualInspectionAgent(model.ServicePid, model.TelNum, model.CarNoPrefix);
if (oldValue != null)
{
result = dbScopeManagerConfiguration.Execute(conn =>
DalVehicleAnnualInspectionAgent.UpdateVehicleAnnualInspectionAgent(conn, model));
if (!result)
{
throw new Exception($"UpdateVehicleAnnualInspectionAgent失败,待更新数据{JsonConvert.SerializeObject(model)}");
}
model.CreateDateTime = oldValue.CreateDateTime;
model.LastUpdateDateTime = DateTime.Now;
var log = new AnnualInspectionOprLogModel
{
LogType = "VehicleAnnualInspectionAgent",
IdentityId = $"{model.CarNoPrefix}_{model.ServicePid}_{model.TelNum}",
OperationType = "Update",
OldValue = JsonConvert.SerializeObject(oldValue),
NewValue = JsonConvert.SerializeObject(model),
Remarks = $"更新{model.CarNoPrefix},供应商:{model.VenderShortName},服务Pid:{model.ServicePid}," +
$"联系人:{model.Contact},联系电话{model.TelNum}的{model.ServiceName}服务",
Operator = user,
};
LoggerManager.InsertLog("AnnualInspectOprLog", log);
}
}
catch (Exception ex)
{
result = false;
Logger.Error("UpdateVehicleAnnualInspectionAgent", ex);
}
return(result);
}
public ActionResult AddAgentConfig(VehicleAnnualInspectionAgentModel model)
{
if (model == null)
{
return(Json(new { Status = false, Msg = "未知的对象" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.ServicePid))
{
return(Json(new { Status = false, Msg = "请选择服务Pid" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.TelNum))
{
return(Json(new { Status = false, Msg = "联系电话不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.VenderShortName))
{
return(Json(new { Status = false, Msg = "供应商简称不能为空" }, JsonRequestBehavior.AllowGet));
}
if (model.SalePrice < 0 || model.CostPrice < 0)
{
return(Json(new { Status = false, Msg = "成本或售价不能为负数" }, JsonRequestBehavior.AllowGet));
}
if (model.ServicePid != "FU-NJDB|3")
{
if (string.IsNullOrWhiteSpace(model.CarNoPrefix))
{
return(Json(new { Status = false, Msg = "请选择车牌前缀" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.Contact))
{
return(Json(new { Status = false, Msg = "联系人不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.OfficeAddress))
{
return(Json(new { Status = false, Msg = "邮寄地址不能为空" }, JsonRequestBehavior.AllowGet));
}
}
else
{
if (string.IsNullOrWhiteSpace(model.Province))
{
return(Json(new { Status = false, Msg = "请选择省份" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.City))
{
return(Json(new { Status = false, Msg = "请选择城市或者县" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.Village))
{
return(Json(new { Status = false, Msg = "请选择乡" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.Email))
{
return(Json(new { Status = false, Msg = "联系邮箱不能为空" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.Remarks))
{
return(Json(new { Status = false, Msg = "备注不能为空" }, JsonRequestBehavior.AllowGet));
}
}
var manager = new VehicleAnnualInspectionAgentManager();
var user = User.Identity.Name;
bool isExist = false;
bool result = false;
if (model.ServicePid != "FU-NJDB|3")
{
isExist = manager.IsExistAnnualInspectionAgent(model);
}
else
{
isExist = manager.IsExistOnlineAnnualInspectionAgent(model);
}
if (isExist)
{
return(Json(new { Status = false, Msg = "已存在重复的数据，不能重复添加" }, JsonRequestBehavior.AllowGet));
}
if (model.ServicePid != "FU-NJDB|3")
{
result = manager.AddVehicleAnnualInspectionAgent(model, user);
}
else
{
result = manager.AddVehicleOnlineAnnualInspectionAgent(model, user);
}
return(Json(new { Status = result, Msg = $"添加{(result ? "成功" : "失败")}" }, JsonRequestBehavior.AllowGet));
}