public SigmaResultType AddSigmaCode(TypeSigmaCode objSigmaCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if (!(string.IsNullOrEmpty(objSigmaCode.CodeName))
&& !(string.IsNullOrEmpty(objSigmaCode.CodeCategory))
)
{
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Code", objSigmaCode.CodeCategory + objSigmaCode.CodeName.ToUpper().Replace(" ", "_")));
paramList.Add(new SqlParameter("@CodeCategory", objSigmaCode.CodeCategory.Trim()));
paramList.Add(new SqlParameter("@CodeName", objSigmaCode.CodeName.Trim()));
paramList.Add(new SqlParameter("@CodeShortName", objSigmaCode.CodeShortName.Trim()));
paramList.Add(new SqlParameter("@RefChar", objSigmaCode.RefChar));
paramList.Add(new SqlParameter("@RefNo", Utilities.ToInt32(objSigmaCode.RefNo)));
paramList.Add(new SqlParameter("@Description", objSigmaCode.Description.Trim()));
paramList.Add(new SqlParameter("@IsActive", objSigmaCode.IsActive));
paramList.Add(new SqlParameter("@SortOrder", objSigmaCode.SortOrder));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaCode", paramList.ToArray());
result.IsSuccessful = true;
//result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
}
else
{
result.AffectedRow = -1;
result.ErrorCode = "GlobalSetting0001";
result.ErrorMessage = "Validation";
result.IsSuccessful = false;
}
return result;
}
public SigmaResultType ImportDrawingTypeLib(string filePath, string exportfilepath)
{
SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr();
CommonCodeMgr commonCodeMgr = new CommonCodeMgr();
SigmaResultType sigmaResult = new SigmaResultType();
TypeSigmaCode typeSigmaCode = new TypeSigmaCode();
DataLoaderDrawingType loader = new DataLoaderDrawingType();
DataTable Exceldt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true);
DataTable ErrDataTable = SetErrTable(Exceldt);
int failCount = 0;
int rowCount = Exceldt.Rows.Count;
int columnCount = Exceldt.Columns.Count;
if (rowCount > 0)
{
loader = MTOImportHelper.GetDataLoaderDrawingType(Exceldt);
string codeCategory = "DRAWING_TYPE";
foreach (DataRow row in Exceldt.Rows)
{
bool isValidation = true;
#region Mandatory Check (*)
for (int i = 0; i < columnCount; i++)
{
if (Exceldt.Columns[i].ToString().Substring(0, 1).ToUpper() == "*" && string.IsNullOrEmpty(row.ItemArray.GetValue(i).ToString()))
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The value of [" + Exceldt.Columns[i].ToString() + "] is required.";
failCount = failCount + 1;
isValidation = false;
break;
}
}
#endregion Mandatory Check
#region Reference Check (SigmaCode Table)
//DataRow DrawingType = null;
//if (isValidation)
//{
// DrawingType = SigmaCodeDT.Select("CodeName = '" + row[loader.Ord_DrawingType] + "'").FirstOrDefault();
// if (DrawingType == null)
// {
// ErrDataTable.Rows.Add(row.ItemArray);
// ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_DrawingType].ToString() + "]";
// failCount = failCount + 1;
// isValidation = false;
// }
//}
#endregion Reference Check
#region Duplication Check
if (isValidation)
{
string codeName = row[loader.Ord_DrawingType].ToString();
DataSet SigmaCodeDS = sigmaCodeMgr.ListSigmaCodeByCodeCategory(codeName, codeCategory);
if (SigmaCodeDS.Tables[0].Rows.Count > 0)
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "This data[Drawing Type] is duplicated.";
failCount = failCount + 1;
isValidation = false;
}
}
#endregion Duplication Check
#region AddSigmaCode
if (isValidation)
{
typeSigmaCode.Code = "";
typeSigmaCode.CodeCategory = codeCategory;
typeSigmaCode.CodeName = row[loader.Ord_DrawingType].ToString().Trim();
typeSigmaCode.CodeShortName = row[loader.Ord_DrawingType].ToString().Trim();
typeSigmaCode.RefChar = "";
typeSigmaCode.RefNo = "";
typeSigmaCode.Description = row[loader.Ord_Description].ToString();
typeSigmaCode.IsActive = "Y";
typeSigmaCode.SortOrder = "";
typeSigmaCode.SigmaOperation = "C";
typeSigmaCode.CreatedBy = userinfo.SigmaUserId;
sigmaResult = sigmaCodeMgr.AddSigmaCode(typeSigmaCode);
}
#endregion AddSigmaCode
}
// Set ImportHistory(SuccessCount/FailCount)
sigmaResult = AddImportHistory(Exceldt.Rows.Count, failCount, Path.GetFileName(filePath).ToString(), "DrawingType");
// ConvertExcel file && CSV file
Export2Excel.ConvertExcelfromData(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".xlsx", exportfilepath);
Export2Excel.ConvertCSVFile(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".csv", exportfilepath);
sigmaResult.IsSuccessful = true;
}
else
{
sigmaResult.IsSuccessful = false;
sigmaResult.ErrorMessage = "no record from file.";
}
return sigmaResult;
}
public SigmaResultType UpdateSigmaCode(TypeSigmaCode objSigmaCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if (!(string.IsNullOrEmpty(objSigmaCode.CodeName))
&& !(string.IsNullOrEmpty(objSigmaCode.CodeCategory))
)
{
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Code", objSigmaCode.Code),
new SqlParameter("@CodeCategory", objSigmaCode.CodeCategory.Trim()),
new SqlParameter("@CodeName", objSigmaCode.CodeName.Trim()),
new SqlParameter("@CodeShortName", objSigmaCode.CodeShortName),
new SqlParameter("@RefChar", objSigmaCode.RefChar),
new SqlParameter("@RefNo", Utilities.ToInt32(objSigmaCode.RefNo)),
new SqlParameter("@Description", objSigmaCode.Description.Trim()),
new SqlParameter("@IsActive", objSigmaCode.IsActive),
new SqlParameter("@SortOrder", objSigmaCode.SortOrder),
new SqlParameter("@UpdatedBy", userinfo.SigmaUserId.Trim())
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateSigmaCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
}
else
{
result.AffectedRow = -1;
result.ErrorCode = "GlobalSetting0001";
result.ErrorMessage = "Validation";
result.IsSuccessful = false;
}
return result;
}
public SigmaResultType RemoveSigmaCode(TypeSigmaCode objSigmaCode)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Code", objSigmaCode.Code.Trim()),
new SqlParameter("@CodeCategory", objSigmaCode.CodeCategory.Trim())
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveSigmaCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType UpdateSigmaCode(TypeSigmaCode objSigmaCode)
{
SigmaResultType result = new SigmaResultType();
try
{
SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr();
result = sigmaCodeMgr.UpdateSigmaCode(objSigmaCode);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}