public SigmaResultType AddClientCostCode(TypeClientCostCode objClientCostCode)
{
SigmaResultType result = new SigmaResultType();
try
{
CostCodeMgr costCodeMgr = new CostCodeMgr();
result = costCodeMgr.AddClientCostCode(objClientCostCode);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType AddClientCostCode(TypeClientCostCode objClientCostCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objClientCostCode.ClientCompanyId = userinfo.ClientCompanyId;
objClientCostCode.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("@ClientCompanyId", objClientCostCode.ClientCompanyId));
paramList.Add(new SqlParameter("@ClientCostCode", objClientCostCode.ClientCostCode));
paramList.Add(new SqlParameter("@ClientCostCodeName", objClientCostCode.ClientCostCodeName));
paramList.Add(new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId));
paramList.Add(new SqlParameter("@CreatedBy", objClientCostCode.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_AddClientCostCode", 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 UpdateClientCostCode(TypeClientCostCode objClientCostCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objClientCostCode.ClientCompanyId = userinfo.ClientCompanyId;
objClientCostCode.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("@ClientCostCodeId", objClientCostCode.ClientCostCodeId),
new SqlParameter("@ClientCompanyId", objClientCostCode.ClientCompanyId),
new SqlParameter("@ClientCostCode", objClientCostCode.ClientCostCode),
new SqlParameter("@ClientCostCodeName", objClientCostCode.ClientCostCodeName),
new SqlParameter("@UpdatedBy", objClientCostCode.UpdatedBy),
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateClientCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType RemoveClientCostCode(TypeClientCostCode objClientCostCode)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ClientCostCodeId", objClientCostCode.ClientCostCodeId)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveClientCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType ImportClientCostCodeFromExcel(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)
{
TypeClientCostCode obj = new TypeClientCostCode();
obj.ClientCostCode = r[0].ToString();
obj.ClientCostCodeName = r[1].ToString();
obj.ClientCompanyId = userinfo.ClientCompanyId;
obj.CreatedBy = userinfo.SigmaUserId;
if (string.IsNullOrEmpty(GetFailreasonForRequiredClientCostCode(r)))
{
SigmaResultType rst = AddClientCostCode(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"] = GetFailreasonForRequiredClientCostCode(r);
failCnt++;
}
}
TypeImportHistory ImportHistory = new TypeImportHistory();
ImportHistory.ImportCategory = "ClientCostCode";
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;
}
