private static void CreateParticularAndSubType(IDBManager dbManager, string particularName, string particularSubType)
{
ParticularsModel pModel = new ParticularsModel();
ParticularsSubTypeModel pSubModel = new ParticularsSubTypeModel();
string sql;
int lastID;
try
{
pModel.CreateParticular(particularName);
dbManager.Open();
sql = "select seq from sqlite_sequence where name='Particulars'";
lastID = Convert.ToInt32(dbManager.ExecuteScalar(CommandType.Text, sql));
dbManager.Close();
pSubModel.CreateParticularsSubType(particularSubType, lastID);
}
catch
{
}
finally
{
}
}
public bool CreateAccountMonthWiseData(string MonthName, int Year, string AccountID, List <EmployeeDetails> empData)
{
AccountModel accModel = new AccountModel();
List <ErrorMessage> errorMsg = new List <ErrorMessage>();
ParticularsSubTypeModel pSubType = new ParticularsSubTypeModel();
List <ParticularsSubType> pSubTypeList = pSubType.GetParticularsSubTypeList();
IDBManager dbManager = new DBManager(DataProvider.SQLite);
dbManager.ConnectionString = BaseDbContext.databasestring;
bool isInsertSuccessful = true;
try
{
dbManager.Open();
#region Insert Data into Primary Table
string insertQuery = string.Format("INSERT INTO AccountMonthMasterData(AccountID,MonthName,Year) Values ('{0}','{1}','{2}')", AccountID, MonthName, Year);
int result = dbManager.ExecuteNonQuery(CommandType.Text, insertQuery);
if (result <= 0)
{
isInsertSuccessful = false;
}
#endregion
#region Get Vertyical Names
string sql = "select seq from sqlite_sequence where name='AccountMonthMasterData'";
int lastID = Convert.ToInt32(dbManager.ExecuteScalar(CommandType.Text, sql));
var excludedGroupNames = accModel.GetExcludedVerticalNames(AccountID);
var verticals = empData.Where(a => !excludedGroupNames.Contains(a.VerticalName)).Select(a => a.VerticalName).Distinct();
#endregion
#region Get Data for Calculation
var getFullRevenue = Calculate.CalculateSum(empData, CalculationType.Revenue, CalculationType.None);
var getFullSalary = Calculate.CalculateSum(empData, CalculationType.Salary, CalculationType.None);
var getOnsiteRevenue = Calculate.CalculateSum(empData, CalculationType.Revenue, CalculationType.IsOnsite);
var getOnsiteSalary = Calculate.CalculateSum(empData, CalculationType.Salary, CalculationType.IsOnsite);
var getOnShoreRevenue = Calculate.CalculateSum(empData, CalculationType.Revenue, CalculationType.IsOffShore);
var getOnShoreSalary = Calculate.CalculateSum(empData, CalculationType.Salary, CalculationType.IsOffShore);
decimal calculatedValue = 0;
#endregion
#region Financial Data
calculatedValue = getFullRevenue;
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Financial", "Avg Revenue");
calculatedValue = Calculate.CalculateGM(getFullRevenue, getFullSalary, true);
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Financial", "YTD GM");
calculatedValue = Calculate.CalculateGM(getOnsiteRevenue, getOnsiteSalary, true);
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Financial", "Onsite GM");
calculatedValue = Calculate.CalculateGM(getOnShoreRevenue, getOnShoreSalary, true);
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Financial", "Offshore GM");
#endregion
#region Resource Counts Data
calculatedValue = empData.Count();
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Resource Counts", "Avg Total");
calculatedValue = empData.Where(a => a.IsOnsite).Count();
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Resource Counts", "Avg Offshore");
calculatedValue = empData.Where(a => !a.IsOnsite).Count();
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Resource Counts", "Avg Onsite");
#endregion
#region Account Management Count
calculatedValue = empData.Where(a => a.AccountID == 1).Count();
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Account MGMT #", "Account MGMT #");
#endregion
#region Account Management Cost
var mgmCost = Calculate.CalculateSum(empData, CalculationType.Salary, CalculationType.IsAccMgmt);
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "Account MGMT Cost", "% of revenue");
#endregion
#region NB Count
calculatedValue = empData.Where(a => !a.IsBillable).Count();
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "NB #", "NB #");
#endregion
#region NB Cost
var nbCost = Calculate.CalculateSum(empData, CalculationType.Salary, CalculationType.IsNonBillable);
calculatedValue = Calculate.CalculatePercent(nbCost, getFullRevenue);
isInsertSuccessful = InsertDataInChildTable(dbManager, lastID, calculatedValue, "NB Cost", "% of revenue");
#endregion
#region Vertical Specific Data
foreach (var verticalName in verticals)
{
#region Vertical Wise Data
//1
var verticalrev = Calculate.CalculateSumWithVerticalName(empData, CalculationType.Revenue, CalculationType.None, verticalName);
var calculateValue = Calculate.CalculatePercent(verticalrev, getFullRevenue);
isInsertSuccessful = InsertDataInVerticalTable(dbManager, lastID, calculateValue, verticalName, "% of Rev");
//2
var verticalonsiterev = Calculate.CalculateSumWithVerticalName(empData, CalculationType.Revenue, CalculationType.IsOnsite, verticalName);
calculateValue = Calculate.CalculatePercent(verticalonsiterev, verticalrev);
isInsertSuccessful = InsertDataInVerticalTable(dbManager, lastID, calculateValue, verticalName, "Onsite % of Rev");
//3
var verticaloffshorerev = Calculate.CalculateSumWithVerticalName(empData, CalculationType.Revenue, CalculationType.IsOffShore, verticalName);
calculateValue = Calculate.CalculatePercent(verticaloffshorerev, verticalrev);
isInsertSuccessful = InsertDataInVerticalTable(dbManager, lastID, calculateValue, verticalName, "Offshore % of Rev");
//4
calculateValue = Calculate.CalculateGM(verticalonsiterev, verticalrev, true);
isInsertSuccessful = InsertDataInVerticalTable(dbManager, lastID, calculateValue, verticalName, "Onsite GM");
//5
var verticaloffshoregm = Calculate.CalculateSumWithVerticalName(empData, CalculationType.Salary, CalculationType.None, verticalName);
calculateValue = Calculate.CalculateGM(verticaloffshorerev, verticaloffshoregm, true);
isInsertSuccessful = InsertDataInVerticalTable(dbManager, lastID, calculateValue, verticalName, "Offshore GM");
#endregion
}
#endregion
return(isInsertSuccessful);
}
catch (Exception ex)
{
return(false);
}
finally
{
dbManager.Close();
dbManager.Dispose();
}
}