public IEnumerable <PodWiseCountDto> GetPodWiseAllocationCount()
{
List <PodWiseCountDto> records = new List <PodWiseCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string query = PostgresSqlQueries.POD_WISE_EMPLOYEE_COUNT.Replace("__CURRENT_DATE__", $"{ DateTime.Today.Year}-{ DateTime.Today.Month}-{ DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(query, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new PodWiseCountDto
{
PracticeName = res.IsDBNull(0) == false ? res.GetString(0) : "",
SubCategoryName = res.IsDBNull(1) == false ? res.GetString(1) : "",
Count = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
});
}
}
}
catch (Exception exp) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
public int GetNonAllocatedResourcesCountFromPostgres(bool forDelivery = true)
{
int count = 0;
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_DELIVERY;
if (!forDelivery)
{
qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_NON_DELIVERY;
}
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
object res = cmd.ExecuteScalar();
int.TryParse(res.ToString(), out count);
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(count);
}
public IEnumerable <ManagerWiseAllocationDto> GetManagerWiseAllocationSummaryFromPostgres()
{
List <ManagerWiseAllocationDto> records = new List <ManagerWiseAllocationDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_MANAGER_WISE_PROJECTS_SUMMARY.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new ManagerWiseAllocationDto
{
ManagerName = res.IsDBNull(1) == false ? res.GetString(1) : "",
ProjectCount = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
ProjectManagerID = res.IsDBNull(0) == false ? res.GetInt32(0) : -1
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
public IEnumerable <EmployeeLoginDto> GetUserAndRoleMapEntries()
{
List <EmployeeLoginDto> records = new List <EmployeeLoginDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.SECURITY_DB_CONNECTION_STRING);
con.Open();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(PostgresSqlQueries.GET_USER_LOGIN_ROLE_ENTRIES, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new EmployeeLoginDto
{
Email = res.IsDBNull(1) == false ? res.GetString(1) : "",
RoleName = res.IsDBNull(2) == false ? res.GetString(2) : "",
UserID = res.IsDBNull(0) == false ? res.GetString(0) : ""
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
static void checkPgSqlConnection()
{
var npgsqlConnection = new Npgsql.NpgsqlConnection(Config.pgSqlConnectionString);
npgsqlConnection.Open();
npgsqlConnection.Dispose();
}
public void TestConnection()
{
Npgsql.NpgsqlConnection Conn = new Npgsql.NpgsqlConnection(this.ConnStr);
Conn.Open();
Conn.Close();
Conn.Dispose();
}
private async Task UpdateAllocations()
{
con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);
try
{
con.Open();
List <ProjectAllocation> oldPractices = sqlDataContext.ProjectAllocations.ToList();
// update primary column id
List <ProjectAllocation> newPractices = postgressDataContext.ProjectAllocations.ToList();
foreach (ProjectAllocation newRec in newPractices)
{
int oldPracticeID = oldPractices.FirstOrDefault(p => p.EmployeeID == newRec.EmployeeID && p.ProjectID == newRec.ProjectID).AllocationEntryID;
string qry = $"UPDATE \"TalentManager\".\"ProjectAllocation\" SET \"AllocationEntryID\"={oldPracticeID} WHERE \"AllocationEntryID\"={newRec.AllocationEntryID}";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
int val = await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
}
private async Task UpdateDropDownSubCategories()
{
con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);
try
{
con.Open();
List <DropDownSubCategory> oldCategories = sqlDataContext.DropDownSubCategories.ToList();
// update primary column id
List <DropDownSubCategory> newCategories = postgressDataContext.DropDownSubCategories.ToList();
foreach (DropDownSubCategory newRec in newCategories)
{
int oldPracticeID = oldCategories.FirstOrDefault(p => p.SubCategoryName == newRec.SubCategoryName).SubCategoryID;
string qry = $"UPDATE \"TalentManager\".\"DropDownSubCategory\" SET \"SubCategoryID\"={oldPracticeID} WHERE \"SubCategoryID\"={newRec.SubCategoryID}";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
int val = await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
}
private async Task UpdateSystemSettings()
{
con = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["TalentDataContextPostgres"].ConnectionString);
try
{
con.Open();
sqlDataContext = new Agilisium.TalentManager.Model.TalentManagerDataContext();
List <SystemSetting> oldSettings = sqlDataContext.SystemSettings.ToList();
// update primary column id
postgressDataContext = new Agilisium.TalentManager.PostgresModel.TalentManagerDataContext();
List <SystemSetting> newSettings = postgressDataContext.SystemSettings.ToList();
foreach (SystemSetting newRec in newSettings)
{
int oldPracticeID = oldSettings.FirstOrDefault(p => p.SettingName == newRec.SettingName).SettingEntryID;
string qry = $"UPDATE \"TalentManager\".\"SystemSettings\" SET \"SettingEntryID\"={oldPracticeID} WHERE \"SettingEntryID\"={newRec.SettingEntryID}";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
int val = await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
Npgsql.NpgsqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (Npgsql.NpgsqlCommand) CommandsCollection[storedProcedure];
}
else
{
Npgsql.NpgsqlConnection Conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new Npgsql.NpgsqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
Npgsql.NpgsqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (Npgsql.NpgsqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
public int DeleteRoleMappingEntryForUser(string userID)
{
Npgsql.NpgsqlConnection con = null;
int res = 0;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.SECURITY_DB_CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.DELETE_USER_AND_ROLE_MAPPING_QUERY.Replace("__USER_ID__", userID);
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
res = cmd.ExecuteNonQuery();
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(res);
}
public IEnumerable <SubPracticeHeadCountDto> GetSubPracticeWiseHeadCountFromPostgres()
{
List <SubPracticeHeadCountDto> records = new List <SubPracticeHeadCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_SUB_PRACTICE_WISE_HEAD_COUNT;
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
int?nullInt = null;
if (res.HasRows)
{
while (res.Read())
{
records.Add(new SubPracticeHeadCountDto
{
HeadCount = res.IsDBNull(4) == false ? (int)res.GetInt64(4) : nullInt,
Practice = res.IsDBNull(1) == false ? res.GetString(1) : "",
PracticeID = res.IsDBNull(0) == false ? (int)res.GetInt64(0) : nullInt,
SubPractice = res.IsDBNull(3) == false ? res.GetString(3) : "",
SubPracticeID = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : nullInt
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
public WindowArchive()
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
string sql = "Select table_name FROM information_schema.tables WHERE table_schema = 'public'";
Npgsql.NpgsqlCommand command = null;
try
{
conn.Open();
command = new Npgsql.NpgsqlCommand(sql, conn);
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string table = reader.GetString(0);
CollectionTables.Add(table.Replace('_', ' '));
}
reader.Close();
LBTables.ItemsSource = CollectionTables;
LBTables.SelectionMode = SelectionMode.Multiple;
Binding bindingLoadTable = new Binding();
bindingLoadTable.Source = LBTables;
bindingLoadTable.Path = new PropertyPath("SelectedItem");
bindingLoadTable.Converter = new RemoveButtonConverter();
Binding bindingLoadTableChart = new Binding();
bindingLoadTableChart.Source = LBTables;
bindingLoadTableChart.Path = new PropertyPath("SelectedItem");
bindingLoadTableChart.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData = new Binding();
bindingGetOptionalData.Source = LBTables;
bindingGetOptionalData.Path = new PropertyPath("SelectedItem");
bindingGetOptionalData.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData2 = new Binding();
bindingGetOptionalData2.Source = CHBAverage;
bindingGetOptionalData2.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData3 = new Binding();
bindingGetOptionalData3.Source = CHBSum;
bindingGetOptionalData3.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData4 = new Binding();
bindingGetOptionalData4.Source = CHBMax;
bindingGetOptionalData4.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData5 = new Binding();
bindingGetOptionalData5.Source = CHBMin;
bindingGetOptionalData5.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData6 = new Binding();
bindingGetOptionalData6.Source = CHBIntegralSum;
bindingGetOptionalData6.Path = new PropertyPath("IsChecked");
MultiBinding mBindingGetOptionalData = new MultiBinding();
mBindingGetOptionalData.Converter = new GetOptionalData();
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData2);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData3);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData4);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData5);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData6);
BLoadTable.SetBinding(Button.IsEnabledProperty, bindingLoadTable);
BLoadTableChart.SetBinding(Button.IsEnabledProperty, bindingLoadTableChart);
BGetOptionalData.SetBinding(Button.IsEnabledProperty, mBindingGetOptionalData);
DataPickerForm.DisplayDateEnd = DateTime.Now;
DataPickerForm.ToolTip = "Диапазон даты от";
DataPickerForm.SelectedDateChanged += DataPickerForm_SelectedDateChanged;
DataPickerForm.SelectedDate = DateTime.Now;
TimePickerForm.ValueChanged += TimePickerForm_ValueChanged;
TimePickerForm.ToolTip = "Диапазон времени от";
TimePickerForm.Format = TimeFormat.Custom;
TimePickerForm.FormatString = "HH:mm:ss";
if (DateTime.Now.Hour == 0)
{
TimePickerForm.Value = DateTime.Now;
}
else
{
TimePickerForm.Value = DateTime.Now.AddHours(-1);
}
DataPickerTo.DisplayDateEnd = DateTime.Now;
DataPickerTo.ToolTip = "Диапазон даты до";
DataPickerTo.SelectedDateChanged += DataPickerTo_SelectedDateChanged;
DataPickerTo.SelectedDate = DateTime.Now;
TimePickerTo.ValueChanged += TimePickerTo_ValueChanged;
TimePickerTo.ToolTip = "Диапазон времени до";
TimePickerTo.Format = TimeFormat.Custom;
TimePickerTo.FormatString = "HH:mm:ss";
TimePickerTo.Value = DateTime.Now;
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
if (command != null)
{
command.Dispose();
}
}
}
}
public WindowArchiveChart(List <string> colSelect, List <string> colTableName)
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
try
{
conn.Open();
PlotModel plotModel = new PlotModel();
if (colSelect.Count >= 2)
{
int count = 0;
while (true)
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[count], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[count]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
count++;
if (count == colSelect.Count)
{
break;
}
}
}
else
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[0], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[0]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
}
plotModel.LegendTitle = "Легенда";
plotModel.LegendOrientation = LegendOrientation.Horizontal;
plotModel.LegendPlacement = LegendPlacement.Outside;
plotModel.LegendPosition = LegendPosition.TopRight;
plotModel.LegendBackground = OxyColor.FromAColor(200, OxyColors.White);
plotModel.LegendBorder = OxyColors.Black;
var dateAxis = new OxyPlot.Axes.DateTimeAxis(OxyPlot.Axes.AxisPosition.Bottom, "Дата", "dd/MM HH:mm")
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, IntervalLength = 65
};
plotModel.Axes.Add(dateAxis);
var valueAxis = new OxyPlot.Axes.LinearAxis(AxisPosition.Left)
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, Title = "Значение"
};
valueAxis.MaximumPadding = 0.3;
valueAxis.MinimumPadding = 0.3;
plotModel.Axes.Add(valueAxis);
Plot = new Plot();
Plot.SetValue(Grid.RowProperty, 1);
Plot.Model = plotModel;
Plot.MinHeight = 100;
Plot.MinWidth = 100;
GridMain.Children.Add(Plot);
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public List <BillabilityWiseAllocationDetailDto> GetAllocationEntriesByAllocationTypeFromPostgres(int allocationType)
{
List <BillabilityWiseAllocationDetailDto> allocationDetailDtos = new List <BillabilityWiseAllocationDetailDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_VALID_ALLOCATIONS;
if (allocationType == -1)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_ALL_NON_ALLOCATED_DELIVERY_BU_RESOURCES;
}
else if (allocationType == -2)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_ALL_NON_ALLOCATED_OTHER_BU_RESOURCES;
}
else if (allocationType == -5)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_BENCH.Replace("__BENCH_CATEGORY__", "75");
}
else if (allocationType == -6)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_BENCH.Replace("__BENCH_CATEGORY__", "74");
}
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
qry = qry.Replace("__ALLOCATION_TYPE_ID__", allocationType.ToString());
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
DateTime?nullDate = null;
int? nullInt = null;
if (res.HasRows)
{
while (res.Read())
{
allocationDetailDtos.Add(new BillabilityWiseAllocationDetailDto
{
AccountName = res.IsDBNull(15) == false ? res.GetString(15) : "",
AllocationEndDate = res.IsDBNull(12) == false ? res.GetDateTime(12) : nullDate,
AllocationStartDate = res.IsDBNull(11) == false ? res.GetDateTime(11) : nullDate,
AllocationType = res.IsDBNull(6) == false ? res.GetString(6) : "",
AllocationTypeID = res.IsDBNull(5) == false ? res.GetInt32(5) : nullInt,
BusinessUnit = res.IsDBNull(8) == false ? res.GetString(8) : "",
BusinessUnitID = res.IsDBNull(7) == false ? res.GetInt32(7) : nullInt,
Comments = res.IsDBNull(18) == false ? res.GetString(18) : "",
EmployeeEntryID = res.IsDBNull(0) == false ? (int?)res.GetInt64(0) : nullInt,
EmployeeID = res.IsDBNull(1) == false ? res.GetString(1) : "",
EmployeeName = res.IsDBNull(2) == false ? res.GetString(2) : "",
PrimarySkills = res.IsDBNull(3) == false ? res.GetString(3) : "",
ProjectID = res.IsDBNull(13) == false ? (int?)res.GetInt64(13) : nullInt,
ProjectManager = res.IsDBNull(17) == false ? res.GetString(17) : "",
ProjectManagerID = res.IsDBNull(16) == false ? res.GetInt32(16) : nullInt,
ProjectName = res.IsDBNull(14) == false ? res.GetString(14) : "",
ProjectType = "",
ProjectTypeID = 0,
SecondarySkills = res.IsDBNull(4) == false ? res.GetString(4) : ""
});
}
}
}
catch (Exception exp) { }
finally
{
con.Close();
con.Dispose();
}
return(allocationDetailDtos);
}
public WindowArchiveTable(string colSelect, List <string> colTableName)
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
int count = 0;
while (true)
{
colTableName[count] = colTableName[count].Replace(' ', '_');
count++;
if (count == colTableName.Count)
{
break;
}
}
try
{
conn.Open();
if (colTableName.Count >= 2)
{
count = 0;
string tables = "SELECT ";
while (true)
{
tables += colTableName[count] + @".""Value"", ";
count++;
if (count == colTableName.Count)
{
break;
}
}
count = 0;
tables += "COALESCE(";
while (true)
{
if (count + 1 == colTableName.Count)
{
tables += colTableName[count] + @".""Time"") AS Время FROM " + colTableName[0];
}
else
{
tables += colTableName[count] + @".""Time"", ";
}
count++;
if (count == colTableName.Count)
{
break;
}
}
count = 1;
while (true)
{
if (colTableName.Count == 2)
{
tables += " FULL OUTER JOIN " + colTableName[count] + @" USING(""Time"")";
break;
}
else
{
tables += " FULL OUTER JOIN " + colTableName[count] + @" USING(""Time"")";
}
count++;
if (count == colTableName.Count)
{
break;
}
}
tables += colSelect;
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(tables, conn);
adapter.Fill(DataTable);
adapter.Dispose();
int countR = 0;
//List<int> colTimes = new List<int>();
//while (true)
//{
// if (DataTable.Columns[countR].ColumnName.LastIndexOf("Time") > 0)
// {
// DataTable.Columns.RemoveAt(countR);
// }
// countR++;
// if (countR == DataTable.Columns.Count)
// {
// break;
// }
//}
countR = 0;
int countInRow = 0;
DataRow rowNext;
DataRow row;
DataTable.DefaultView.Sort = "Время asc";
DataTable = DataTable.DefaultView.ToTable();
if (DataTable.Rows.Count > 0)
{
while (true)
{
row = DataTable.Rows[countR];
if (countR + 1 == DataTable.Rows.Count)
{
break;
}
rowNext = DataTable.Rows[countR + 1];
DateTime dt1 = (DateTime)row[row.ItemArray.Length - 1];
DateTime dt2 = (DateTime)rowNext[row.ItemArray.Length - 1];
if (dt1.Year == dt2.Year && dt1.Month == dt2.Month && dt1.Day == dt2.Day && dt1.Hour == dt2.Hour && dt1.Minute == dt2.Minute && dt1.Second == dt2.Second)
{
while (true)
{
if (row[countInRow] is DBNull && !(rowNext[countInRow] is DBNull))
{
row[countInRow] = rowNext[countInRow];
}
countInRow++;
if (countInRow == row.ItemArray.Length)
{
countInRow = 0;
DataTable.Rows.RemoveAt(countR + 1);
break;
}
}
}
countR++;
if (countR == DataTable.Rows.Count)
{
break;
}
}
}
count = 0;
while (true)
{
if (count == DataTable.Columns.Count - 1)
{
break;
}
colTableName[count] = colTableName[count].Replace('_', ' ');
DataTable.Columns[count].ColumnName = colTableName[count];
count++;
if (count == DataTable.Rows.Count)
{
break;
}
}
DGTable.IsReadOnly = true;
DGTable.ItemsSource = DataTable.DefaultView;
DGTable.AutoGenerateColumns = true;
DGTable.MinHeight = 400;
DGTable.MinWidth = 300;
}
else
{
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect, conn);
adapter.Fill(DataTable);
DataTable.Columns["Value"].ColumnName = colTableName[0].Replace('_', ' ');
DataTable.Columns["Time"].ColumnName = "Время";
adapter.Dispose();
DGTable.IsReadOnly = true;
DGTable.ItemsSource = DataTable.DefaultView;
DGTable.AutoGenerateColumns = true;
DGTable.MinHeight = 400;
DGTable.MinWidth = 300;
}
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}