public static SqlDataRecord ConvertforTeamLevel(ExcelSignleRow row, DataSourceNames name)
{
var record = new SqlDataRecord(Constants.Structures[name]);
for (var index = 0; index < Constants.Structures[name].Length; index++)
{
var month = row.JMetadata.TryGetValue<string>("$.Month");
var week = row.JMetadata.TryGetValue<string>("$.Week");
if (month == null && lastMonth != string.Empty)
month = lastMonth;
if (month != null && month.IndexOf("Grand Total") >= 0) return null;
if (string.IsNullOrEmpty(week))
week = "Week 1";
switch (Constants.Structures[name][index].Name)
{
case "Monthly":
record.SetStringIfNullOrEmpty(index, month);
break;
case "Weekly":
record.SetStringIfNullOrEmpty(index, week);
break;
case "IsTotalLine":
record.SetBoolean(index, month.IndexOf("Total") >= 0);
break;
case "BatchJobId":
record.SetStringIfNullOrEmpty(index, row.BatchJob?.Id);
break;
case "Metadata":
record.SetStringIfNullOrEmpty(index, row.JMetadata?.ToString());
break;
}
if (month != null)
lastMonth = month;
}
return record;
}
public static void Fix(this ExcelSignleRow row,
DataSourceNames source,
Mapping[] mappings)
{
var properties = new List <PropertyObject>();
properties.AddRange(row.Properties);
FixBatchJobID(source, row.BatchJob.Id, properties, mappings);
row.Properties = properties.ToArray();
}
public static TeamLevelReport Convertfrom(ExcelSignleRow row)
{
var month = row.JMetadata.TryGetValue<string>("$.Month");
return new TeamLevelReport()
{
Monthly = month,
Weekly = row.JMetadata.TryGetValue<string>("$.Week"),
BatchJob = row.BatchJob.Id,
IsTotalLine = (month != null && month.IndexOf("total", StringComparison.OrdinalIgnoreCase) >= 0),
Metadata = row.JMetadata.ToString()
};
}
public static bool PrimaryKeyRequired(this ExcelSignleRow row, DataSourceNames name)
{
switch (name)
{
case DataSourceNames.Avaya:
case DataSourceNames.Chat:
var agent = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Agent"))?.Value?.ToString();
var skillSet = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("SkillSet"))?.Value?.ToString();
bool bCreatedDateTime = DateTime.TryParse(
row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("CreatedDateTime"))?.Value?.ToString(),
out DateTime dt);
var createdDateTime = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("CreatedDateTime"))?.Value?.ToString();
return(string.IsNullOrEmpty(agent) == false && string.IsNullOrEmpty(skillSet) == false && bCreatedDateTime == true);
case DataSourceNames.MSXSQO:
var opportunityId = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("OpportunityId"))?.Value?.ToString();
return(string.IsNullOrEmpty(opportunityId) == false);
case DataSourceNames.MSXTQL:
var leadId = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("OpportunityId"))?.Value?.ToString();
return(string.IsNullOrEmpty(leadId) == false);
case DataSourceNames.PhoneVolume:
var datetime = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("CreatedDateTime"))?.Value?.ToString();
var channel = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Channel"))?.Value?.ToString();
var program = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Program"))?.Value?.ToString();
var region = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Region"))?.Value?.ToString();
var market = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Market"))?.Value?.ToString();
var supplier = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals("Supplier"))?.Value?.ToString();
return(string.IsNullOrEmpty(datetime) == false &&
string.IsNullOrEmpty(channel) == false &&
string.IsNullOrEmpty(program) == false &&
string.IsNullOrEmpty(region) == false &&
string.IsNullOrEmpty(market) == false &&
string.IsNullOrEmpty(supplier) == false);
}
return(false);
}
public static SqlDataRecord Convert(ExcelSignleRow row, DataSourceNames name)
{
try
{
if (row == null) throw new ArgumentNullException("data can't be null in convert");
if (row.PrimaryKeyRequired(name) == false)
{
throw new NullReferenceException($"Primary Key can't be null{name}");
}
var record = new SqlDataRecord(Constants.Structures[name]);
for (var index = 0; index < Constants.Structures[name].Length; index++)
{
var fieldName = Constants.Structures[name][index].Name;
var property = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals(fieldName, StringComparison.OrdinalIgnoreCase));
if (property == null)
record.SetDBNull(index);
else
{
if (property.Descriptor.Type == System.Data.SqlDbType.DateTime)
{
if (property.Value.TryToUnixStampDateTime(out long? timestamp))
{
record.SetInt64(index, timestamp ?? 0);
}
else
{
record.SetDBNull(index);
}
}
else if (property.Descriptor.Type == System.Data.SqlDbType.Int)
{
if (int.TryParse(property.Value?.ToString(), out int val))
{
record.SetInt32(index, val);
}
else
{
record.SetDBNull(index);
}
}
else if (property.Descriptor.Type == System.Data.SqlDbType.Money)
{
if (decimal.TryParse(property.Value?.ToString(), out decimal money))
{
record.SetSqlMoney(index, money);
}
else
{
record.SetDBNull(index);
}
}
else
{
record.SetStringIfNullOrEmpty(index, property.Value?.ToString());
}
}
}
return record;
}
catch (Exception ex)
{
return null;
}
}
