public static List <KPITarget> GetKPITargetCategoriesByKpiId(int kpiId)
{
if (kpiId <= 0)
{
throw new ArgumentException("El ID del KPI no puede ser cero.");
}
List <KPITarget> theList = new List <KPITarget>();
KPITarget theData = null;
try
{
KPITargetTableAdapter localAdapter = new KPITargetTableAdapter();
KPITargetDS.KPITargetDataTable theTable = localAdapter.GetKPITargetCategoriesByKpiId(kpiId);
if (theTable != null && theTable.Rows.Count > 0)
{
foreach (KPITargetDS.KPITargetRow theRow in theTable)
{
theData = FillRecord(theRow);
theData.Detalle = theRow.IsdetalleNull() ? "" : theRow.detalle;
theData.Categories = theRow.IscategoriesNull() ? "" : theRow.categories;
theList.Add(theData);
}
}
}
catch (Exception exc)
{
log.Error("Error en GetKPITargetCategoriesByKpiId para kpiId: " + kpiId, exc);
throw exc;
}
return(theList);
}
public static KPITarget GetKPITargetByKpiId(int kpiId)
{
if (kpiId <= 0)
{
throw new ArgumentException("El ID del KPI no puede ser cero.");
}
KPITarget theData = null;
try
{
KPITargetTableAdapter localAdapter = new KPITargetTableAdapter();
KPITargetDS.KPITargetDataTable theTable = localAdapter.GetKPITargetById(kpiId);
if (theTable != null && theTable.Rows.Count > 0)
{
KPITargetDS.KPITargetRow theRow = theTable[0];
theData = FillRecord(theRow);
}
}
catch (Exception exc)
{
log.Error("Ocurrió un error mientras se obtenÃa el KPI Target de id: " + kpiId, exc);
throw exc;
}
return(theData);
}
private static KPITarget FillRecord(KPITargetDS.KPITargetRow row)
{
KPITarget theNewRecord = new KPITarget(
row.targetID,
row.kpiID,
row.target);
return(theNewRecord);
}
public bool SaveTarget(KPITargetDTO target)
{
var oldTarget = operationalDataContext.KPITargets.FirstOrDefault(x => x.TargetID == target.TargetID);
if (oldTarget == null)
{
oldTarget = new KPITarget();
}
oldTarget.CreatedBy = target.CreatedBy;
oldTarget.DateCreated = target.DateCreated;
oldTarget.DateModified = target.DateModified;
oldTarget.ModifiedBy = target.ModifiedBy;
oldTarget.TaregtDescriptionEn = target.TaregtDescriptionEn;
oldTarget.TargetDescriptionAr = target.TargetDescriptionAr;
oldTarget.TargetID = target.TargetID;
oldTarget.TargetName = target.TargetName;
oldTarget.TargetValue = target.TargetValue;
operationalDataContext.KPITargets.Add(oldTarget);
return(operationalDataContext.SaveChanges() > 0);
}
public static void UpdateKPI(KPI theKpi, KPITarget theTarget, List <KPITarget> theTargetCategories)
{
try
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString))
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection.Open();
// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand("usp_KPI_UpdateKPI", connection);
command1.CommandType = System.Data.CommandType.StoredProcedure;
// NOTE that we pass zeros to areaID, projectID, activityID, etc., sicne the stored procedure will convert
// these to nulls!
command1.Parameters.Add("@kpiId", System.Data.SqlDbType.Int).Value = theKpi.KpiID;
command1.Parameters.Add("@organizationID", System.Data.SqlDbType.Int).Value = theKpi.OrganizationID;
command1.Parameters.Add("@areaID", System.Data.SqlDbType.Int).Value = theKpi.AreaID;
command1.Parameters.Add("@projectID", System.Data.SqlDbType.Int).Value = theKpi.ProjectID;
command1.Parameters.Add("@activityID", System.Data.SqlDbType.Int).Value = theKpi.ActivityID;
command1.Parameters.Add("@personID", System.Data.SqlDbType.Int).Value = theKpi.PersonID;
command1.Parameters.Add("@kpiName", System.Data.SqlDbType.NVarChar, 250).Value = theKpi.Name;
command1.Parameters.Add("@unit", System.Data.SqlDbType.VarChar, 10).Value = theKpi.UnitID;
command1.Parameters.Add("@direction", System.Data.SqlDbType.Char, 3).Value = theKpi.DirectionID;
command1.Parameters.Add("@strategy", System.Data.SqlDbType.Char, 3).Value = theKpi.StrategyID;
SqlParameter dateParam = new SqlParameter("@startDate", System.Data.SqlDbType.Date);
if (theKpi.StartDate == DateTime.MinValue)
{
dateParam.Value = DBNull.Value;
}
else
{
dateParam.Value = theKpi.StartDate;
}
command1.Parameters.Add(dateParam);
command1.Parameters.Add("@reportingUnit", System.Data.SqlDbType.Char, 15).Value = theKpi.ReportingUnitID;
SqlParameter targetPeriodParam = new SqlParameter("@targetPeriod", System.Data.SqlDbType.Int);
if (theKpi.TargetPeriod == 0)
{
targetPeriodParam.Value = DBNull.Value;
}
else
{
targetPeriodParam.Value = theKpi.TargetPeriod;
}
command1.Parameters.Add(targetPeriodParam);
// If we have categories, then set the flag.
command1.Parameters.Add("@allowsCategories", System.Data.SqlDbType.Bit).Value = theKpi.AllowCategories;
SqlParameter currencyParam = new SqlParameter("@currency", System.Data.SqlDbType.Char, 3);
if (String.IsNullOrWhiteSpace(theKpi.Currency))
{
currencyParam.Value = DBNull.Value;
}
else
{
currencyParam.Value = theKpi.Currency;
}
command1.Parameters.Add(currencyParam);
SqlParameter currencyUnitParam = new SqlParameter("@currencyUnit", System.Data.SqlDbType.Char, 3);
if (String.IsNullOrWhiteSpace(theKpi.CurrencyUnitID))
{
currencyUnitParam.Value = DBNull.Value;
}
else
{
currencyUnitParam.Value = theKpi.CurrencyUnitID;
}
command1.Parameters.Add(currencyUnitParam);
command1.Parameters.Add("@kpiTypeID", System.Data.SqlDbType.VarChar, 10).Value = theKpi.KpiTypeID;
command1.ExecuteNonQuery();
if (!theKpi.AllowCategories)
{
if (theTarget != null && theTarget.Target > 0)
{
//Insert or update the single target
SqlCommand command2 = new SqlCommand("usp_KPI_UpdateKPITargetNoCategories", connection);
command2.CommandType = System.Data.CommandType.StoredProcedure;
command2.Parameters.Add("@kpiID", System.Data.SqlDbType.Int).Value = theKpi.KpiID;
command2.Parameters.Add("@target", System.Data.SqlDbType.Decimal).Value = theTarget.Target;
command2.ExecuteNonQuery();
}
else
{
//Delete de target
SqlCommand command3 = new SqlCommand("usp_KPI_DeleteAllKPITarget", connection);
command3.CommandType = System.Data.CommandType.StoredProcedure;
command3.Parameters.Add("@kpiID", System.Data.SqlDbType.Int).Value = theKpi.KpiID;
command3.ExecuteNonQuery();
}
}
// If the KPI has categories and targets, create them
int firstDelete = 0;
if (theKpi.AllowCategories && theTargetCategories.Count > 0)
{
foreach (KPITarget theItem in theTargetCategories)
{
//If not exists values in TARGETID clean all and recreate the categories
if (firstDelete == 0 && theItem.TargetID == 0)
{
//Delete de target
SqlCommand command4 = new SqlCommand("usp_KPI_DeleteAllKPITarget", connection);
command4.CommandType = System.Data.CommandType.StoredProcedure;
command4.Parameters.Add("@kpiID", System.Data.SqlDbType.Int).Value = theKpi.KpiID;
command4.ExecuteNonQuery();
}
firstDelete++;
//Update the target category
SqlCommand command5 = new SqlCommand("usp_KPI_UpdateKPITargetCategory", connection);
command5.CommandType = System.Data.CommandType.StoredProcedure;
command5.Parameters.Add("@kpiID", System.Data.SqlDbType.Int).Value = theKpi.KpiID;
command5.Parameters.Add("@targetID", System.Data.SqlDbType.Int).Value = theItem.TargetID;
command5.Parameters.Add("@items", System.Data.SqlDbType.VarChar, 1000).Value = theItem.Detalle;
command5.Parameters.Add("@categories", System.Data.SqlDbType.VarChar, 1000).Value = theItem.Categories;
command5.Parameters.Add("@target", System.Data.SqlDbType.Decimal).Value = theItem.Target;
command5.ExecuteNonQuery();
}
}
}
// The Complete method commits the transaction. If an exception has been thrown,
// Complete is not called and the transaction is rolled back.
scope.Complete();
}
}
catch (Exception ex)
{
log.Error(Resources.Kpi.MessageErrorUpdate, ex);
throw new Exception(Resources.Kpi.MessageErrorUpdate);
}
}