public SigmaResultType RemoveProjectCostCode(TypeProjectCostCode objProjectCostCode)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ProjectCostCodeId", objProjectCostCode.ProjectCostCodeId)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveProjectCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType ImportProjectCostCodeFromExcel(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)
{
TypeProjectCostCode obj = new TypeProjectCostCode();
obj.CostCode = r[0].ToString();
obj.UomName = r[2].ToString();
obj.ProjectId = userinfo.CurrentProjectId;
obj.CreatedBy = userinfo.SigmaUserId;
if (string.IsNullOrEmpty(GetFailreasonForRequiredProjectCostCode(r)))
{
if (!string.IsNullOrEmpty(r[3].ToString()))
obj.EstimatedQty = Convert.ToDecimal(r[3].ToString());
if (!string.IsNullOrEmpty(r[4].ToString()))
obj.EstimatedManhour = Convert.ToDecimal(r[4].ToString());
SigmaResultType rst = AddProjectCostCodeByTemplate(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"] = GetFailreasonForRequiredProjectCostCode(r);
failCnt++;
}
}
TypeImportHistory ImportHistory = new TypeImportHistory();
ImportHistory.ImportCategory = "ProjectCostCode";
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 UpdateProjectCostCode(TypeProjectCostCode objProjectCostCode)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ProjectCostCodeId", objProjectCostCode.ProjectCostCodeId),
new SqlParameter("@CostCodeId", objProjectCostCode.CostCodeId),
new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId),
new SqlParameter("@UomCode", objProjectCostCode.UomCode),
new SqlParameter("@EstimatedQty", objProjectCostCode.EstimatedQty),
new SqlParameter("@EstimatedManhour", objProjectCostCode.EstimatedManhour),
new SqlParameter("@UpdatedBy", AuthMgr.GetUserInfo().SigmaUserId),
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateProjectCostCode", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType AddProjectCostCodeByTemplate(TypeProjectCostCode objProjectCostCode)
{
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", objProjectCostCode.CostCode));
paramList.Add(new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId));
paramList.Add(new SqlParameter("@UomName", objProjectCostCode.UomName));
paramList.Add(new SqlParameter("@EstimatedQty", objProjectCostCode.EstimatedQty));
paramList.Add(new SqlParameter("@EstimatedManhour", objProjectCostCode.EstimatedManhour));
paramList.Add(new SqlParameter("@CreatedBy", AuthMgr.GetUserInfo().SigmaUserId));
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddProjectCostCodeByTemplate", paramList.ToArray());
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType AddProjectCostCode(TypeProjectCostCode objProjectCostCode)
{
SigmaResultType result = new SigmaResultType();
try
{
CostCodeMgr costCodeMgr = new CostCodeMgr();
result = costCodeMgr.AddProjectCostCode(objProjectCostCode);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}