public override void ExecuteExport(ServiceJobContext context, IDictionary <string, object> parameters)
{
string currentTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
_logger.LogInfo($"Execution Proces Started for {context.JobKey} at {currentTime}.");
dataProvider = SumtContainer.Resolve <IDataProvider>();
dataProvider.Open();
try
{
string settingstoExport = string.Empty;
//Load job parameters
settingstoExport = parameters["SettingsExport"].ToString();
string reportPath = parameters["ExtractPath"].ToString();
ISystemJobRepository systemJobRepository = SumtContainer.Resolve <ISystemJobRepository>();
ICodeDefinitionFacade facade = SumtContainer.Resolve <ICodeDefinitionFacade>();
CodeDefinitionDTO codeDefinition = facade.GetCodeDefinitionWithAttributes(_configurationParameters.MasterCategoryCode);
IList <CodeDTO> codes = codeDefinition.Codes.Where(c => c.CodeAttributeDTO.Attr1Val.Equals(_configurationParameters.Group)).ToList();
// codeDefinition.Codes.Where(c => c.ItemText.Equals(settingstoExport)).ToList();
string sectionLookupClause = string.Empty;
foreach (CodeDTO code in codes)
{
String[] sectionLookups = code.CodeAttributeDTO.Attr2Val.Split(',');
for (int i = 0; i < sectionLookups.Length; i++)
{
sectionLookups[i] = " p.Section like '%" + sectionLookups[i] + "%'";
}
sectionLookupClause += String.Join(" OR ", sectionLookups);
}
orgDomains = GetOrgDetails(_configurationParameters.Domains);
foreach (KeyValuePair <string, int> kvp in orgDomains)
{
GetPersistData(reportPath, sectionLookupClause, kvp);
}
}
catch (Exception ex)
{
_logger.LogError("Error while processing configurations extract.", ex);
throw (ex);
}
finally
{
dataProvider.Close();
}
}
public override void Execute(ServiceJobContext context, IDictionary <string, object> parameters)
{
string currentTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
_logger.LogInfo($"Execution Proces Started for {context.JobKey} at {currentTime}.");
dataProvider = SumtContainer.Resolve <IDataProvider>();
dataProvider.Open();
try
{
string reportPath = parameters["ExtractPath"].ToString();
string filePrefix = parameters["FilePrefix"].ToString();
//added new line code test
ISystemJobRepository systemJobRepository = SumtContainer.Resolve <ISystemJobRepository>();
ICodeDefinitionFacade facade = SumtContainer.Resolve <ICodeDefinitionFacade>();
CodeDefinitionDTO codeDefinition = facade.GetCodeDefinitionWithAttributes(_configurationParameters.MasterCategoryCode);
var finalPath = Path.Combine(reportPath, filePrefix + "_" + DateTime.Now.ToString("yyyyMMdd'_'HHmmss") + ".xlsx");
IList <CodeDTO> codes = codeDefinition.Codes.Where(c => c.CodeAttributeDTO.Attr1Val.Equals(_configurationParameters.Group)).ToList();
if (codes.Count == 0)
{
_logger.LogError("Please recheck master category code in system job parameters");
return;
}
var workbook = new XLWorkbook();
foreach (CodeDTO code in codes)
{
CodeDefinitionDTO sectionCode = facade.GetCodeDefinitionWithAttributes(Convert.ToInt32(code.ItemCode));
try
{
var sectionData = sectionCode.Codes.Select(c => new SectionTemplate()
{
SettingName = c.CodeAttributeDTO.Attr1Val,
SettingKey = c.ItemCode,
Itmtxt = c.ItemText,
Sequence = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr2Val) ? 0 : Convert.ToInt32(c.CodeAttributeDTO.Attr2Val),
CodeLookupCatcd = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr4Val) ? "" : c.CodeAttributeDTO.Attr4Val.ToString()
}).ToList();
DataTable dt2 = new DataTable();
dt2.Columns.Add("Securitykey", typeof(String));
dt2.Columns.Add("SecurityName", typeof(String));
dt2.Columns.Add("bitvalue", typeof(String));
dt2.Columns.Add("Sequence", typeof(Decimal));
object[] values = new object[4];
foreach (SectionTemplate sec in sectionData)
{
values[0] = sec.SettingKey;
values[1] = sec.SettingKey;
values[3] = sec.Sequence;
dt2.Rows.Add(values);
string[] securitykeys = sec.SettingName.Split(',');
int i = 0;
// Part 3: loop over result array.
foreach (string securitykey in securitykeys)
{
if (securitykeys[0] != "")
{
values[0] = securitykey;
values[1] = sec.SettingKey;
values[3] = sec.Sequence + "." + i;
dt2.Rows.Add(values);
i++;
}
}
}
//Get Security name hexadecimal value and bit value
CodeDefinitionDTO hexadecimal = facade.GetCodeDefinitionWithAttributes(Convert.ToInt32("100900"));
var hexadecimalData = hexadecimal.Codes.Select(c => new RoleMaskSettings()
{
SecurityName = c.ItemCode,
HexaDecimal = c.CodeAttributeDTO.Attr1Val,
bitvalue = c.CodeAttributeDTO.Attr2Val,
Itmtxt = c.ItemText
}).ToList();
//convert list to data table
DataTable code100900 = new DataTable();
code100900 = Helper.ToDataTable(hexadecimalData);
dt2.AsEnumerable()
.Join(code100900.AsEnumerable(),
dt1_Row => dt1_Row.ItemArray[0],
dt2_Row => dt2_Row.ItemArray[0],
(dt1_Row, dt2_Row) => new { dt1_Row, dt2_Row })
.ToList()
.ForEach(o =>
o.dt1_Row.SetField(2, o.dt2_Row.ItemArray[2]));
dt2.AsEnumerable()
.Join(code100900.AsEnumerable(),
dt1_Row => dt1_Row.ItemArray[0],
dt2_Row => dt2_Row.ItemArray[0],
(dt1_Row, dt2_Row) => new { dt1_Row, dt2_Row })
.ToList()
.ForEach(o =>
o.dt1_Row.SetField(0, o.dt2_Row.ItemArray[3]));
Tuple <DataTable, DataTable> RoleMaskOrgData = GetRoleMaskData();
DataTable RoleMaskData = RoleMaskOrgData.Item1;
DataTable OrgData = RoleMaskOrgData.Item2;
var distinctIds = RoleMaskData.AsEnumerable()
.Select(s => new {
id = s.Field <int>("Role_DomainFK"),
})
.Distinct().ToList();
for (int i = 3; i < distinctIds.Count + 3; i++)
{
DataTable rolemaskDomainData = new DataTable();
int domainId = Int32.Parse(distinctIds[i - 3].id.ToString());
string Domian_Name = null;
var DomainName = from row in OrgData.AsEnumerable()
where row.Field <int>("OrganizationPK") == domainId
select new
{
DomainName = row.Field <string>("Name")
};
rolemaskDomainData = GetRolemaskDetails(domainId, RoleMaskData, dt2);
rolemaskDomainData.Columns.Remove("bitvalue");
rolemaskDomainData.Columns.Remove("Sequence");
foreach (var dom in DomainName)
{
Domian_Name = dom.DomainName;
}
workbook = Helper.AddWorkSheet(rolemaskDomainData, Domian_Name, workbook);
}
foreach (IXLWorksheet xLWorksheet in workbook.Worksheets)
{
xLWorksheet.Style.Border.RightBorder = XLBorderStyleValues.Thick;
xLWorksheet.Style.Border.RightBorderColor = XLColor.Black;
xLWorksheet.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
var rows = xLWorksheet.RangeUsed().RowsUsed().Skip(1);
// First possible address of the company table:
var firstPossibleAddress = xLWorksheet.Row(1).FirstCell().Address;
// Last possible address of the company table:
var lastPossibleAddress = xLWorksheet.LastCellUsed().Address;
// Get a range with the remainder of the worksheet data (the range used)
var companyRange = xLWorksheet.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed();
var firstTableCell = xLWorksheet.FirstCellUsed();
var lastTableCell = xLWorksheet.LastCellUsed();
xLWorksheet.Style.Font.FontSize = 9;
var rngData = xLWorksheet.Range(firstTableCell.Address, lastTableCell.Address);
rngData.Rows(); // From all rows
rngData.Style.Border.SetInsideBorder(XLBorderStyleValues.Thin);
var range = xLWorksheet.Range("A1:" + lastTableCell.Address.ColumnLetter + "1");
range.Style.Font.FontSize = 11;
range.Style.Font.FontColor = XLColor.White;
range.Style.Fill.BackgroundColor = XLColor.RichElectricBlue;
foreach (var row in rows)
{
if (row.Cell(1).Value.ToString() == row.Cell(2).Value.ToString())
{
var rowNumber = row.RowNumber();
string pagebreakValue = xLWorksheet.Cell(rowNumber, 2).Value.ToString();
xLWorksheet.Cell(rowNumber, 1).Style.Font.Bold = true;
}
}
xLWorksheet.Column(2).Delete();
xLWorksheet.Columns(1, lastTableCell.Address.ColumnNumber).Width = 35;
xLWorksheet.Style.Alignment.WrapText = true;
xLWorksheet.Columns(1, 1).Width = 50;
xLWorksheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
xLWorksheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
}
workbook.SaveAs(finalPath);
}
catch (Exception ex2)
{
_logger.LogError("No codes for catcd " + sectionCode.CodeName);
}
}
}
finally
{
dataProvider.Close();
}
}
public override void Execute(ServiceJobContext context, IDictionary <string, object> parameters)
{
string currentTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
_logger.LogInfo($"Execution Proces Started for {context.JobKey} at {currentTime}.");
dataProvider = SumtContainer.Resolve <IDataProvider>();
dataProvider.Open();
try
{
string reportPath = parameters["ExtractPath"].ToString();
string filePrefix = parameters["FilePrefix"].ToString();
ISystemJobRepository systemJobRepository = SumtContainer.Resolve <ISystemJobRepository>();
ICodeDefinitionFacade facade = SumtContainer.Resolve <ICodeDefinitionFacade>();
CodeDefinitionDTO codeDefinition = facade.GetCodeDefinitionWithAttributes(_configurationParameters.MasterCategoryCode);
var finalPath = Path.Combine(reportPath, filePrefix + "_" + DateTime.Now.ToString("yyyyMMdd'_'HHmmss") + ".xlsx");
IList <CodeDTO> codes = codeDefinition.Codes.Where(c => c.CodeAttributeDTO.Attr1Val.Equals(_configurationParameters.Group)).ToList();
if (codes.Count == 0)
{
_logger.LogError("Please recheck master category code in system job parameters");
return;
}
var workbook = new XLWorkbook();
foreach (CodeDTO code in codes)
{
CodeDefinitionDTO sectionCode = facade.GetCodeDefinitionWithAttributes(Convert.ToInt32(code.ItemCode));
try
{
var sectionData = sectionCode.Codes.Select(c => new SectionTemplate()
{
SettingName = c.CodeAttributeDTO.Attr1Val,
SettingKey = c.ItemCode,
Sequence = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr2Val) ? 0 : Convert.ToInt32(c.CodeAttributeDTO.Attr2Val),
HasLookup = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr3Val) ? false : Convert.ToBoolean(c.CodeAttributeDTO.Attr3Val),
IsPerson = c.CodeAttributeDTO.Attr4Val,
IsRule = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr5Val) ? false : Convert.ToBoolean(c.CodeAttributeDTO.Attr5Val),
MergeColumns = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr6Val) ? 0 : Convert.ToInt32(c.CodeAttributeDTO.Attr6Val),
LookupData = String.IsNullOrEmpty(c.CodeAttributeDTO.Attr7Val) ? "" : c.CodeAttributeDTO.Attr7Val.ToString()
}).ToList();
String[] sectionLookups = code.CodeAttributeDTO.Attr2Val.Split(',');
string sectionLookupClause = string.Empty;
for (int i = 0; i < sectionLookups.Length; i++)
{
sectionLookups[i] = " p.Section like '%" + sectionLookups[i] + "%'";
}
sectionLookupClause = String.Join(" OR ", sectionLookups);
bool checkOnlyGlobal = false;
if (sectionLookupClause.Contains("GeneralSetting"))
{
checkOnlyGlobal = true;
}
var tupleSectionData = PullSectionSettingvaluesIntoTemp(sectionLookupClause, checkOnlyGlobal);
DataTable settingsData = JoinTwoDataTablesOnOneColumn(Helper.ToDataTable(sectionData), tupleSectionData, "SettingKey");
workbook = Helper.AddWorkSheet(settingsData, code.ItemText, workbook);
}
catch (Exception ex2)
{
_logger.LogError("No codes for catcd " + sectionCode.CodeName);
}
}
foreach (IXLWorksheet xLWorksheet in workbook.Worksheets)
{
xLWorksheet.Style.Border.RightBorder = XLBorderStyleValues.Thick;
xLWorksheet.Style.Border.RightBorderColor = XLColor.Black;
xLWorksheet.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
var rows = xLWorksheet.RangeUsed().RowsUsed().Skip(1);
// First possible address of the company table:
var firstPossibleAddress = xLWorksheet.Row(1).FirstCell().Address;
// Last possible address of the company table:
var lastPossibleAddress = xLWorksheet.LastCellUsed().Address;
// Get a range with the remainder of the worksheet data (the range used)
var companyRange = xLWorksheet.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed();
var firstTableCell = xLWorksheet.FirstCellUsed();
var lastTableCell = xLWorksheet.LastCellUsed();
xLWorksheet.Style.Font.FontSize = 9;
var rngData = xLWorksheet.Range(firstTableCell.Address, lastTableCell.Address);
rngData.Rows(); // From all rows
rngData.Style.Border.SetInsideBorder(XLBorderStyleValues.Thin);
var range = xLWorksheet.Range("A1:" + lastTableCell.Address.ColumnLetter + "1");
range.Style.Font.FontSize = 11;
range.Style.Font.FontColor = XLColor.White;
range.Style.Fill.BackgroundColor = XLColor.RichElectricBlue;
xLWorksheet.Columns(2, 2).Style.Alignment.WrapText = true;
foreach (var row in rows)
{
if (row.Cell(1).Value.ToString().Contains("PageBreak"))
{
var rowNumber = row.RowNumber();
string pagebreakValue = xLWorksheet.Cell(rowNumber, 2).Value.ToString();
string[] splitValue = pagebreakValue.Split(':');
if (splitValue.Length == 2)
{
xLWorksheet.Cell(rowNumber, 2).Value = splitValue[0].ToString();
xLWorksheet.Cell(rowNumber, 2).Style.Font.FontSize = 10;
xLWorksheet.Range("C" + rowNumber.ToString() + ":" + lastTableCell.Address.ColumnLetter + rowNumber.ToString()).Row(1).Merge();
xLWorksheet.Range("C" + rowNumber.ToString() + ":" + lastTableCell.Address.ColumnLetter + rowNumber.ToString()).Style.Font.FontSize = 10;
xLWorksheet.Cell("C" + rowNumber.ToString()).Value = splitValue[1].ToString();
}
xLWorksheet.Cell(rowNumber, 2).Style.Font.Bold = true;
}
if (row.Cell(1).Value.ToString().Contains("EmptyRow"))
{
var rowNumber = row.RowNumber();
xLWorksheet.Cell(rowNumber, 2).Value = "";
}
}
xLWorksheet.Column(1).Delete();
//xLWorksheet.ColumnWidth = 28;
//xLWorksheet.Columns(1, 1).Style.Alignment.WrapText = true;
xLWorksheet.Columns(1, lastTableCell.Address.ColumnNumber).Width = 35;
xLWorksheet.Style.Alignment.WrapText = true;
xLWorksheet.Columns(1, 1).Width = 50;
xLWorksheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
xLWorksheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
//xLWorksheet.Style.Alignment.SetVertical(XLDrawingVerticalAlignment.Bottom);
//xLWorksheet.Comment.Style
// .Alignment.SetVertical(XLDrawingVerticalAlignment.Bottom)
// .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Right);
// xLWorksheet.ColumnWidth = 60;
}
workbook.SaveAs(finalPath);
}
catch (Exception ex)
{
_logger.LogError("Error while processing configurations extract.", ex);
throw (ex);
}
finally
{
dataProvider.Close();
}
}
public override void Execute(ServiceJobContext context, IDictionary <string, object> parameters)
{
string currentTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
_logger.LogInfo($"Execution Proces Started for {context.JobKey} at {currentTime}.");
dataProvider = SumtContainer.Resolve <IDataProvider>();
dataProvider.Open();
try
{
int masterCategoryCode;
string group;
string settingstoExport = string.Empty;
//Load job parameters
masterCategoryCode = Convert.ToInt32(parameters["MasterCategoryCode"]);
group = parameters["Group"].ToString();
settingstoExport = parameters["SettingsExport"].ToString();
string reportPath = parameters["ExtractPath"].ToString();
string orgCodestoExport = parameters["OrgCodestoExport"].ToString();
DataTable dtOrgDetails = GetOrgDetails(orgCodestoExport);
if (string.IsNullOrEmpty(settingstoExport))
{
ISystemJobRepository systemJobRepository = SumtContainer.Resolve <ISystemJobRepository>();
ICodeDefinitionFacade facade = SumtContainer.Resolve <ICodeDefinitionFacade>();
CodeDefinitionDTO codeDefinition = facade.GetCodeDefinitionWithAttributes(masterCategoryCode);
List <string> settings = settingstoExport.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
IList <CodeDTO> codes = codeDefinition.Codes.Where(c => settings.Any(b => string.Compare(c.ItemCode, b, true) == 0)).ToList();
string domainCondition = "";
if (group == "I")
{
string sectionLookupClause = string.Empty;
foreach (CodeDTO code in codes)
{
String[] sectionLookups = code.CodeAttributeDTO.Attr2Val.Split(',');
for (int i = 0; i < sectionLookups.Length; i++)
{
if (dtOrgDetails != null && dtOrgDetails.Rows.Count != 0)
{
domainCondition = "";
foreach (DataRow row in dtOrgDetails.Rows)
{
domainCondition += " p.Section like '%" + "Domain/" + row[0] + "/" + sectionLookups[i] + "%'";
}
sectionLookups[i] = domainCondition;
}
else
{
sectionLookups[i] = " p.Section like '%" + sectionLookups[i] + "%'";
}
}
sectionLookupClause += String.Join(" OR ", sectionLookups);
}
GetPersistData(reportPath, sectionLookupClause);
}
else if (group == "II")
{
}
}
}
catch (Exception ex)
{
_logger.LogError("Error while processing configurations extract.", ex);
throw (ex);
}
finally
{
dataProvider.Close();
}
}
