public SigmaResultType AddCostCode(TypeCostCode objCostCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objCostCode.CompanyId = userinfo.CompanyId.ToString();
objCostCode.CreatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@CostCode", objCostCode.CostCode));
paramList.Add(new SqlParameter("@Description", objCostCode.Description));
paramList.Add(new SqlParameter("@CompanyId", objCostCode.CompanyId));
paramList.Add(new SqlParameter("@CodeRegisterCompanyId", objCostCode.CodeRegisterCompanyId));
paramList.Add(new SqlParameter("@CostCodeType", objCostCode.CostCodeType));
paramList.Add(new SqlParameter("@UomCode", objCostCode.UomCode));
paramList.Add(new SqlParameter("@CreatedBy", objCostCode.CreatedBy));
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_AddCostCode", paramList.ToArray());
if (result.AffectedRow > 0)
{
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
}
else
{
result.IsSuccessful = false;
result.ErrorMessage = "CostCode is duplicated";
}
scope.Complete();
}
return result;
}
public SigmaResultType UpdateCostCode(TypeCostCode objCostCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objCostCode.CompanyId = userinfo.CompanyId.ToString();
objCostCode.UpdatedBy = userinfo.SigmaUserId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@CostCodeId", objCostCode.CostCodeId),
new SqlParameter("@CostCode", objCostCode.CostCode),
new SqlParameter("@Description", objCostCode.Description),
new SqlParameter("@CompanyId", objCostCode.CompanyId),
new SqlParameter("@CodeRegisterCompanyId", objCostCode.CodeRegisterCompanyId),
new SqlParameter("@CostCodeType", objCostCode.CostCodeType),
new SqlParameter("@UomCode", objCostCode.UomCode),
new SqlParameter("@UpdatedBy", objCostCode.UpdatedBy),
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType RemoveCostCode(TypeCostCode objCostCode)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@CostCodeId", objCostCode.CostCodeId)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType ImportCostCodeFromExcel(string filePath, string exportfilepath)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
DataTable dt = new DataTable();
DataTable tmpDt = new DataTable();
SigmaResultType result = new SigmaResultType();
dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true);
DataTable tmpdt = new DataTable();
tmpdt = dt.Copy();
tmpdt.Rows.Clear();
tmpdt.Columns.Add("Fail reason");
int failCnt = 0;
foreach (DataRow r in dt.Rows)
{
TypeCostCode obj = new TypeCostCode();
obj.CostCode = r[0].ToString();
obj.Description = r[1].ToString();
obj.CostCodeType = r[2].ToString();
obj.CompanyId = userinfo.CompanyId.ToString();
obj.UpdatedBy = userinfo.SigmaUserId;
if (string.IsNullOrEmpty(GetFailreasonForRequired(r)))
{
SigmaResultType rst = AddCostCode(obj);
if (!rst.IsSuccessful)
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
failCnt++;
}
}
else
{
tmpdt.Rows.Add(r.ItemArray);
tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequired(r);
failCnt++;
}
}
TypeImportHistory ImportHistory = new TypeImportHistory();
ImportHistory.ImportCategory = "CostCode";
ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
ImportHistory.ImportedDate = DateTime.Now.ToString();
ImportHistory.TotalCount = dt.Rows.Count;
ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
ImportHistory.FailCount = failCnt;
ImportHistory.CreatedBy = userinfo.SigmaUserId;
ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
result = HistoryMgr.AddImportHistory(ImportHistory);
//if exists error list
if (tmpdt.Rows.Count > 0)
{
if (!System.IO.Directory.Exists(exportfilepath))
{
System.IO.Directory.CreateDirectory(exportfilepath);
}
//excel file generate for direct call 'export' link
Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(filePath), exportfilepath);
//csv file generate for import error list view
Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);
}
return result;
}
public SigmaResultType UpdateCostCode(TypeCostCode objCostCode)
{
SigmaResultType result = new SigmaResultType();
try
{
CostCodeMgr costCodeMgr = new CostCodeMgr();
result = costCodeMgr.UpdateCostCode(objCostCode);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
