public ActionResult EditMaintenanceSchedule(EditMaintenanceScheduleModel model)
{
if (ModelState.IsValid)
{
var result = MaintenanceScheduleBLL.EditMaintenanceScheduleModel(model, base.CurrentUserID);
base.DoLog(OperationTypeEnum.Edit, result, "ScheduleID:" + model.ID);
return(Json(result));
}
else
{
return(PartialView("_EditMaintenanceSchedule", model));
}
}
public static OperationResult EditMaintenanceScheduleModel(EditMaintenanceScheduleModel model, int currentUserID)
{
int result = 0;
using (TransactionScope transaction = new TransactionScope())//使用事务
{
try
{
if (model.BindVehicleNum == 0) //无绑定车辆，可修改
{
#region 更新保养方案表
var updateSql = @"UPDATE dbo.MaintenanceSchedule
SET ScheduleName = @ScheduleName
,RulesType = @RulesType
,SettingMile = @SettingMile
,PreMile = @PreMile
,SettingDay = @SettingDay
,PreDay = @PreDay
,Remark = @Remark
,UpdateTime = GetDate()
,UpdateUser = @UpdateUser
WHERE ID = @ID ";
SqlParameter[] updateParas = new SqlParameter[9];
updateParas[0] = new SqlParameter()
{
ParameterName = "@ScheduleName",
SqlDbType = SqlDbType.NVarChar,
Value = model.ScheduleName.Trim()
};
updateParas[1] = new SqlParameter()
{
ParameterName = "@RulesType",
SqlDbType = SqlDbType.Int,
Value = model.RulesType
};
if (model.SettingMile == null)
{
updateParas[2] = new SqlParameter()
{
ParameterName = "@SettingMile",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
updateParas[2] = new SqlParameter()
{
ParameterName = "@SettingMile",
SqlDbType = SqlDbType.Int,
Value = model.SettingMile
};
}
if (model.PreMile == null)
{
updateParas[3] = new SqlParameter()
{
ParameterName = "@PreMile",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
updateParas[3] = new SqlParameter()
{
ParameterName = "@PreMile",
SqlDbType = SqlDbType.Int,
Value = model.PreMile
};
}
if (model.SettingDay == null)
{
updateParas[4] = new SqlParameter()
{
ParameterName = "@SettingDay",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
updateParas[4] = new SqlParameter()
{
ParameterName = "@SettingDay",
SqlDbType = SqlDbType.Int,
Value = model.SettingDay
};
}
if (model.PreDay == null)
{
updateParas[5] = new SqlParameter()
{
ParameterName = "@PreDay",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
updateParas[5] = new SqlParameter()
{
ParameterName = "@PreDay",
SqlDbType = SqlDbType.Int,
Value = model.PreDay
};
}
if (string.IsNullOrEmpty(model.Remark))
{
updateParas[6] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
updateParas[6] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remark
};
}
updateParas[7] = new SqlParameter()
{
ParameterName = "@UpdateUser",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
updateParas[8] = new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
Value = model.ID
};
int updateResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, updateSql, updateParas);
#endregion
#region 除保养项目表中关联数据
var delSql = @"DELETE FROM dbo.MaintenanceProject WHERE ScheduleID=@ScheduleID";
SqlParameter[] delParas = new SqlParameter[1];
delParas[0] = new SqlParameter()
{
ParameterName = "@ScheduleID",
SqlDbType = SqlDbType.Int,
Value = model.ID
};
int delResult = MSSQLHelper.ExecuteNonQuery(CommandType.Text, delSql, delParas);
#endregion
#region 添加保养项目
int len = model.MaintenanceProjectDetails.Count;
string[] sqls = new string[len];
SqlParameter[][] paras = new SqlParameter[len][];
int num = 0;
for (int i = 0; i < len; i++)
{
string tempSql = string.Empty;
tempSql = @" INSERT INTO dbo.MaintenanceProject( ScheduleID
, ProjectName
, PartsName
, PartsBrand
, PartsVersion
, Num
, Unit
, UnitPrice
, CreateUser
, CreateTime ) VALUES ( @ScheduleID,@ProjectName,@PartsName,@PartsBrand,@PartsVersion,@Num,@Unit,@UnitPrice,@CreateUser1,@CreateTime1)";
sqls[num] = tempSql;
paras[num] = new SqlParameter[10];
paras[num][0] = new SqlParameter()
{
ParameterName = "@ScheduleID",
SqlDbType = SqlDbType.Int,
Value = model.ID
};
paras[num][1] = new SqlParameter()
{
ParameterName = "@ProjectName",
SqlDbType = SqlDbType.NVarChar,
Value = model.MaintenanceProjectDetails[i].ProjectName.Trim()
};
if (string.IsNullOrEmpty(model.MaintenanceProjectDetails[i].PartsName))
{
paras[num][2] = new SqlParameter()
{
ParameterName = "@PartsName",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
paras[num][2] = new SqlParameter()
{
ParameterName = "@PartsName",
SqlDbType = SqlDbType.NVarChar,
Value = model.MaintenanceProjectDetails[i].PartsName.Trim()
};
}
if (string.IsNullOrEmpty(model.MaintenanceProjectDetails[i].PartsBrand))
{
paras[num][3] = new SqlParameter()
{
ParameterName = "@PartsBrand",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
paras[num][3] = new SqlParameter()
{
ParameterName = "@PartsBrand",
SqlDbType = SqlDbType.NVarChar,
Value = model.MaintenanceProjectDetails[i].PartsBrand.Trim()
};
}
if (string.IsNullOrEmpty(model.MaintenanceProjectDetails[i].PartsVersion))
{
paras[num][4] = new SqlParameter()
{
ParameterName = "@PartsVersion",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
paras[num][4] = new SqlParameter()
{
ParameterName = "@PartsVersion",
SqlDbType = SqlDbType.NVarChar,
Value = model.MaintenanceProjectDetails[i].PartsVersion.Trim()
};
}
if (model.MaintenanceProjectDetails[i].Num == null)
{
paras[num][5] = new SqlParameter()
{
ParameterName = "@Num",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
paras[num][5] = new SqlParameter()
{
ParameterName = "@Num",
SqlDbType = SqlDbType.Int,
Value = model.MaintenanceProjectDetails[i].Num
};
}
if (string.IsNullOrEmpty(model.MaintenanceProjectDetails[i].Unit))
{
paras[num][6] = new SqlParameter()
{
ParameterName = "@Unit",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
paras[num][6] = new SqlParameter()
{
ParameterName = "@Unit",
SqlDbType = SqlDbType.NVarChar,
Value = model.MaintenanceProjectDetails[i].Unit.Trim()
};
}
if (model.MaintenanceProjectDetails[i].UnitPrice == null)
{
paras[num][7] = new SqlParameter()
{
ParameterName = "@UnitPrice",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value
};
}
else
{
paras[num][7] = new SqlParameter()
{
ParameterName = "@UnitPrice",
SqlDbType = SqlDbType.Int,
Value = model.MaintenanceProjectDetails[i].UnitPrice
};
}
paras[num][8] = new SqlParameter()
{
ParameterName = "@CreateUser1",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
paras[num][9] = new SqlParameter()
{
ParameterName = "@CreateTime1",
SqlDbType = SqlDbType.DateTime,
Value = DateTime.Now
};
num++;
}
#endregion
bool insertResult = MSSQLHelper.ExecuteTransaction(CommandType.Text, sqls, paras);
if (updateResult > 0 && delResult != 0 && insertResult == true)
{
result = 1;
transaction.Complete();//成功再执行这句话
}
}
else //只能修改名称和备注
{
var updateSql2 = @"UPDATE dbo.MaintenanceSchedule
SET ScheduleName = @ScheduleName
,Remark = @Remark
,UpdateTime = GetDate()
,UpdateUser = @UpdateUser
WHERE ID = @ID ";
SqlParameter[] updateParas2 = new SqlParameter[4];
updateParas2[0] = new SqlParameter()
{
ParameterName = "@ScheduleName",
SqlDbType = SqlDbType.NVarChar,
Value = model.ScheduleName.Trim()
};
if (string.IsNullOrEmpty(model.Remark))
{
updateParas2[1] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Value = DBNull.Value
};
}
else
{
updateParas2[1] = new SqlParameter()
{
ParameterName = "@Remark",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remark
};
}
updateParas2[2] = new SqlParameter()
{
ParameterName = "@UpdateUser",
SqlDbType = SqlDbType.Int,
Value = currentUserID
};
updateParas2[3] = new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
Value = model.ID
};
result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, updateSql2, updateParas2);
if (result == 1)
{
transaction.Complete();//成功再执行这句话
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
string msg = string.Empty;
switch (result)
{
case 1:
msg = PromptInformation.OperationSuccess;
break;
case 0:
msg = PromptInformation.NotExists;
break;
case -1:
msg = PromptInformation.DBError;
break;
}
return(new OperationResult()
{
Success = result > 0,
Message = msg
});
}
public static SelectResult <EditMaintenanceScheduleModel> GetMaintenanceScheduleByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
paras[0].Value = id;
string sql = @"SELECT ms.ID
, ms.ScheduleName
, ms.RulesType
, ms.SettingMile
, ms.PreMile
, ms.SettingDay
, ms.PreDay
, ms.Remark
,num.BindVehicleNum
FROM dbo.MaintenanceSchedule ms
LEFT JOIN (SELECT COUNT(*) AS BindVehicleNum,ScheduleID FROM dbo.VehicleMaintenance GROUP BY ScheduleID) num ON num.ScheduleID=ms.ID
WHERE ms.ID=@ID";
List <EditMaintenanceScheduleModel> list = ConvertToList <EditMaintenanceScheduleModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
EditMaintenanceScheduleModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
//保养方案表有数据的情况下，查询保养内容表，填充Model.MaintenanceProjectDetails数据
List <SqlParameter> detailParas = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
detailParas[0].Value = id;
string detailSql = @"SELECT ID AS ProjectID,
ProjectName
, ScheduleID
, PartsName
, PartsBrand
, PartsVersion
, Num
, Unit
, UnitPrice
FROM dbo.MaintenanceProject
WHERE ScheduleID=@ID";
List <MaintenanceProjectModel> detailList = ConvertToList <MaintenanceProjectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, detailSql, detailParas.ToArray()));
list[0].MaintenanceProjectDetails = detailList;
data = list[0];
}
return(new SelectResult <EditMaintenanceScheduleModel>()
{
DataResult = data,
Message = msg
});
}