public ActionResult AddSimCode(AddSimCodeModels model)
{
if (ModelState.IsValid)
{
var result = SimCodeBLL.AddSimCode(model, base.UserIDForLog);
base.DoLog(OperationTypeEnum.Add, result, "ID:" + model.ID);
return(Json(result));
}
else
{
return(PartialView("_AddSimCode", model));
}
}
public ActionResult AddSimCode()
{
AddSimCodeModels model = new AddSimCodeModels();
model.OpeningDate = DateTime.Today.ToString("yyyy-MM-dd");
model.PurchaseDate = DateTime.Today.ToString("yyyy-MM-dd");
model.ExpiryDate = DateTime.Today.ToString("yyyy-MM-dd");
model.CommModeSelectList = new SelectList(SimCodeBLL.GetCommMode(), "ID", "Name");
model.ServiceProviderSelectList = new SelectList(SimCodeBLL.GetServiceProvider(), "ID", "Name");
//var structures = StructureBLL.GetStructures();
//model.OwnerStrucName = new SelectList(structures, "ID", "StrucName");
//model.UseStrucName = new SelectList(structures, "ID", "StrucName");
return(PartialView("_AddSimCode", model));
}
public static OperationResult AddSimCode(AddSimCodeModels model, int CreateUserID)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@ID", SqlDbType.BigInt),
new SqlParameter("@SimCode", SqlDbType.NVarChar, 13),
new SqlParameter("@CommMode", SqlDbType.TinyInt),
new SqlParameter("@OwnerStrucID", SqlDbType.Int),
new SqlParameter("@UseStrucID", SqlDbType.Int),
new SqlParameter("@ServiceProviderID", SqlDbType.Int),
new SqlParameter("@PurchaseDate", SqlDbType.NVarChar, 50),
new SqlParameter("@OpeningDate", SqlDbType.NVarChar, 50),
new SqlParameter("@ExpiryDate", SqlDbType.NVarChar, 50),
new SqlParameter("@Remark", SqlDbType.NVarChar, 50),
new SqlParameter("@CreateUserID", SqlDbType.Int),
};
paras[0].Value = model.ID;
paras[1].Value = model.SimCode.Trim();
paras[2].Value = model.CommMode;
paras[3].Value = model.OwnerStrucID;
paras[4].Value = model.UseStrucID;
paras[5].Value = model.ServiceProviderID;
paras[6].Value = model.PurchaseDate;
paras[7].Value = model.OpeningDate;
paras[8].Value = model.ExpiryDate;
if (string.IsNullOrWhiteSpace(model.Remark))
{
paras[9].Value = DBNull.Value;
}
else
{
paras[9].Value = model.Remark;
}
paras[10].Value = CreateUserID;
#region SQL
string sql = @"INSERT INTO dbo.SimCodeList(SimCode,CommMode,OwnerStrucID,UseStrucID,ServiceProviderID,PurchaseDate,
OpeningDate,ExpiryDate,Remark,Status,CreateUserID) VALUES (@SimCode,@CommMode,@OwnerStrucID,
@UseStrucID,@ServiceProviderID,@PurchaseDate,@OpeningDate,@ExpiryDate,@Remark,'0',@CreateUserID)";
#endregion
bool result = MSSQLHelper.ExecuteNonQuery(CommandType.Text, sql, paras.ToArray()) > 0;
return(new OperationResult()
{
Success = result,
Message = result ? PromptInformation.OperationSuccess : PromptInformation.DBError
});
}
/// <summary>
/// 检查导入的sim资料合法性
/// </summary>
private static bool CheckImportSimInfo(DataRow currentRow, List <string> errorMessages, List <string> SimCodes, AddSimCodeModels model)
{
string fieldName = string.Empty;
#region 获取当前行数据
string SimCodeStr = currentRow[0].ToString().Trim(); // Sim卡号
string CommModeStr = currentRow[1].ToString().Trim(); // 通信方式
string OwnerStrucNameStr = currentRow[2].ToString().Trim(); // 归属单位
string UseStrucNameStr = currentRow[3].ToString().Trim(); // 使用单位
string ServiceProviderStr = currentRow[4].ToString().Trim(); // 服务商
string PurchaseDateStr = currentRow[5].ToString().Trim(); // 购买日期
string OpeningDateStr = currentRow[6].ToString().Trim(); // 开通日期
string ExpiryDateStr = currentRow[7].ToString().Trim(); // 过期日期
string RemarkStr = currentRow[8].ToString().Trim(); // 备注
#endregion
#region 检查Sim卡号
fieldName = DisplayText.SIMCode;
Regex reg = new Regex(@"^([0-9]{11}|[0-9]{13})$");
if (string.IsNullOrWhiteSpace(SimCodeStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
//检查Sim卡格式
else if (!reg.IsMatch(SimCodeStr))
{
errorMessages.Add(DataAnnotations.SIMCodeError);
}
//检查Sim卡在excel中是否重复
else if (SimCodes.Exists(sc => sc == SimCodeStr))
{
errorMessages.Add(PromptInformation.SameSIMInExcel + "：" + SimCodeStr);
}
//检查Sim卡在数据库中是否重复
else if (CheckSIMCodeExists(SimCodeStr))
{
errorMessages.Add(string.Format(DataAnnotations.FieldExists, fieldName));
}
else
{
SimCodes.Add(SimCodeStr);
}
#endregion
#region 通信方式
fieldName = DisplayText.CommMode;
int CommModeValue = -1;
if (string.IsNullOrWhiteSpace(CommModeStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
//检查通信方式合法性
else if (!CommunicationTypeBLL.TryGetCodeByName(CommModeStr, out CommModeValue))
{
errorMessages.Add(string.Format("{0}：{1}{2}", fieldName, CommModeStr, PromptInformation.NotExists));
}
#endregion
#region 归属单位
fieldName = DisplayText.OwnerStrucCode;
int OwnerStrucID = -1;
if (string.IsNullOrWhiteSpace(OwnerStrucNameStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
//检查单位名是否存在，并且获取真正的单位ID
else if (!StructureBLL.TryGetStructureIDByName(OwnerStrucNameStr, out OwnerStrucID))
{
errorMessages.Add(string.Format("{0}：{1}{2}", fieldName, OwnerStrucNameStr, PromptInformation.NotExists));
}
#endregion
#region 使用单位
fieldName = DisplayText.UseStrucCode;
int UseStrucID = -1;
if (string.IsNullOrWhiteSpace(UseStrucNameStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
//检查单位名是否存在，并且获取真正的单位ID
else if (!StructureBLL.TryGetStructureIDByName(UseStrucNameStr, out UseStrucID))
{
errorMessages.Add(string.Format("{0}：{1}{2}", fieldName, UseStrucNameStr, PromptInformation.NotExists));
}
#endregion
#region 务商
fieldName = DisplayText.ServiceProvider;
int ServiceProviderValue = -1;
if (string.IsNullOrWhiteSpace(ServiceProviderStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
//检查服务商合法性
else if (!ServiceProviderTypeBLL.TryGetCodeByName(ServiceProviderStr, out ServiceProviderValue))
{
errorMessages.Add(string.Format("{0}：{1}{2}", fieldName, ServiceProviderStr, PromptInformation.NotExists));
}
#endregion
#region 购买日期
fieldName = DisplayText.PurchaseDate;
DateTime PurchaseDateValue = DateTime.Today;
bool PurchaseDateIsVaild = true;
if (string.IsNullOrWhiteSpace(PurchaseDateStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
PurchaseDateIsVaild = false;
}
else if (!DateTime.TryParse(PurchaseDateStr, out PurchaseDateValue))
{
errorMessages.Add(fieldName + " " + DataAnnotations.DateError);
PurchaseDateIsVaild = false;
}
#endregion
#region 开通日期
fieldName = DisplayText.OpeningDate;
DateTime OpeningDateValue = DateTime.Today;
bool OpeningDateIsVaild = true;
if (string.IsNullOrWhiteSpace(OpeningDateStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
OpeningDateIsVaild = false;
}
else if (!DateTime.TryParse(OpeningDateStr, out OpeningDateValue))
{
errorMessages.Add(fieldName + " " + DataAnnotations.DateError);
OpeningDateIsVaild = false;
}
else if (PurchaseDateIsVaild &&
OpeningDateValue.Subtract(PurchaseDateValue).TotalMilliseconds < 0)
{
errorMessages.Add(fieldName + DataAnnotations.NotLessThan + DisplayText.PurchaseDate);
}
#endregion
#region 过期日期
fieldName = DisplayText.ExpiryDate;
DateTime ExpiryDateValue = DateTime.Today;
if (string.IsNullOrWhiteSpace(ExpiryDateStr))
{
errorMessages.Add(PromptInformation.MissingField + fieldName);
}
else if (!DateTime.TryParse(ExpiryDateStr, out ExpiryDateValue))
{
errorMessages.Add(fieldName + " " + DataAnnotations.DateError);
}
else if (PurchaseDateIsVaild && OpeningDateIsVaild &&
ExpiryDateValue.Subtract(PurchaseDateValue).TotalMilliseconds < 0 ||
ExpiryDateValue.Subtract(OpeningDateValue).TotalMilliseconds < 0)
{
errorMessages.Add(fieldName + DataAnnotations.NotLessThan + DisplayText.PurchaseDate + DataAnnotations.Or + DisplayText.OpeningDate);
}
#endregion
model.SimCode = SimCodeStr;
model.CommMode = CommModeValue;
model.OwnerStrucID = OwnerStrucID;
model.UseStrucID = UseStrucID;
model.ServiceProviderID = ServiceProviderValue;
model.PurchaseDate = PurchaseDateValue.ToString("yyyy-MM-dd");
model.OpeningDate = OpeningDateValue.ToString("yyyy-MM-dd");
model.ExpiryDate = ExpiryDateValue.ToString("yyyy-MM-dd");
model.Remark = RemarkStr;
model.Status = "0";
return(errorMessages.Count == 0);
}
public static OperationResult ImportSimCodes(string excelFilePath, string sheetName, int createUserID)
{
bool success = false;
string message = string.Empty;
string brStr = "<br/>";
var datas = ExcelHelper.ExcelToDataTable(excelFilePath, sheetName);
if (datas == null)
{
message = PromptInformation.AccessExcelFailed;
}
else if (datas.Rows.Count == 0)
{
message = PromptInformation.NoSIMs;
}
else
{
//存储Sim卡号，用于判断DataTable中本身是否存在重复的Sim卡号
List <string> SimCodes = new List <string>();
//存储待新增的Sim卡号
Dictionary <string, AddSimCodeModels> addDic = new Dictionary <string, AddSimCodeModels>();
int i = 0;
for (i = 0; i < datas.Rows.Count; i++)
{
int rowNum = i + 2;
string rowMessage = string.Format(PromptInformation.RowIndex, rowNum);
AddSimCodeModels addModel = new AddSimCodeModels();
List <string> errorMessage = new List <string>();
var currentRow = datas.Rows[i];
if (CheckImportSimInfo(currentRow, errorMessage, SimCodes, addModel))
{
addDic.Add(rowMessage, addModel);
}
else//只要有一条失败，马上返回提示，不继续检查
{
message = rowMessage + brStr;
errorMessage.ForEach(msg =>
{
message += string.Format("{0}{1}", msg, brStr);
});
break;
}
}
if (i == datas.Rows.Count)//全部都检查无误才对数据进行新增
{
int failedCount = 0;
addDic.ToList().ForEach(kv =>
{
if (!AddSimCode(kv.Value, createUserID).Success)
{
message += string.Format("{0}{1}", kv.Key, brStr);
failedCount++;
}
});
success = failedCount == 0;
}
}
return(new OperationResult()
{
Success = success,
Message = success ? PromptInformation.ImportSuccess : PromptInformation.ImportFailed + brStr + message
});
}
