private void RunTest()
{
var extraFields = "p.ProductID,pv.Price,AltPrice1,AltPageLink1";
string altFeedUrl = "http://webadapters.channeladvisor.com/CSEAdapter/Default.aspx?pid=YZP%5e%5eI%5cRA%3bHJ%40fW%2bQ%5bdwd%27QjEN%40_%25-P5%5bH%5e%25YjANr7X%60VKaC2%25%27gCM%3dj*%2f%24%5bbG%5dWQ8FMu_V-%24%5e2JbSU%3dwG";
int _storeId = 1;
int maxRows = 10000;
int startingRow = 1;
string[] rows;
////initialize extra field list
//List<string> extraFieldList = string.IsNullOrEmpty(extraFields) ? new List<string>() :
// extraFields.Split(new[] { ',' }).Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
//initialize extra field list
List<string> extraFieldList = new List<string>();
if (!string.IsNullOrEmpty(extraFields))
{
var tempList = extraFields.Split(new[] { ',' });
foreach (var e in tempList)
{
if (string.IsNullOrEmpty(e.Trim())) continue;
extraFieldList.Add(e);
}
}
//remove standard fields
var standardFields = new List<string>
{
"p.ProductID",
"p.SKU",
"p.Name",
"p.Deleted",
"p.Published",
"pv.Price",
"pv.SalePrice",
"pv.MSRP",
"pv.Cost",
"pv.Inventory",
"pm.ManufacturerID",
"pc.CategoryID",
"ps.SectionID",
"ProductId",
"Name",
"Att1Id",
"Att2Id",
"Price",
"SalePrice",
"ListPrice",
"Cost",
"Inventory",
"Visible",
"Link",
"ImageLink",
"Rating",
"StandardCode",
"Departments",
"CategoryList",
"ManufacturerID",
"SectionList"
};
extraFieldList.RemoveAll(x => standardFields.Contains(x));
//-------begin: CA Alt Feed Handling-------
//Read alternate eBay prices and links from separate CA feed
string[] altHeader = null;
var altFeedLookup = new Dictionary<string, List<string>>();
//string altFeedUrl = AppLogic.AppConfig("4Tell.CaFeedUrl");
var caFeedExists = !string.IsNullOrEmpty(altFeedUrl);
if (caFeedExists)
{
//Note: could make these config parameters
var colEnd = new[] { '\t' };
var rowEnd = new[] { '\n' };
var trimRow = new[] { '\r' };
string feedData = null;
using (var feed = new WebClient())
{
feed.Encoding = Encoding.UTF8;
feed.BaseAddress = "";
feed.Proxy = null;
feed.Headers.Add("user-agent", "Mozilla/5.0 (Windows NT 6.1; rv:18.0) Gecko/20100101 Firefox/18.0");
feed.Headers.Add("accept", "*/*");
feed.Headers.Add("content-type", "application/json");
using (var data = feed.OpenRead(altFeedUrl))
{
if (data != null)
{
using (var reader = new StreamReader(data))
{
feedData = reader.ReadToEnd();
}
}
}
}
//parse into dictionary for lookup below
if (!string.IsNullOrEmpty(feedData))
{
var feedRows = feedData.Split(rowEnd);
var rowCount = feedRows.Length;
if (rowCount > 1)
{
altHeader = feedRows[0].Trim(trimRow).Split(colEnd);
var columnCount = altHeader.Length;
for (var i = 1; i < rowCount; i++)
{
var columns = feedRows[i].Trim(trimRow).Split(colEnd);
if (columns.Length != columnCount) continue;
List<string> data;
if (!altFeedLookup.TryGetValue(columns[0], out data))
{
data = new List<string>();
for (var j = 1; j < columnCount; j++)
data.Add(columns[j]);
altFeedLookup.Add(columns[0], data);
}
}
}
}
if (altFeedLookup.Any())
{
//remove alt fields from the extrafieldList
extraFieldList.RemoveAll(x => altHeader.Contains(x));
//and add then to the catalog header list
//for (var i = 1; i < altHeader.Length; i++) ProductRecord.AddField(altHeader[i]);
}
else //no data
caFeedExists = false;
}
//-------end: CA Alt Feed Handling-------
//throw new Exception("made it here");
//add extra fields to the output catalog header
//foreach (var f in extraFieldList) ProductRecord.AddField(f);
//setup database query (and adjust extraFields)
var totalRows = 50000;// GetRowCount(DataGroup.Catalog);
var query = "SELECT";
if (maxRows > 0)
query += string.Format(" TOP {0} * FROM (SELECT TOP {1}", maxRows, totalRows - startingRow);
query += " p.ProductID, p.SKU, p.Name, p.Deleted, p.Published, pv.Price, pv.SalePrice, pv.MSRP, pv.Cost, pv.Inventory,"
+ " isnull(pm.ManufacturerID, 0) as ManufacturerID, "
+ " stuff((select ',' + cast(pc.CategoryID as varchar)"
+ " FROM ProductCategory pc WHERE p.ProductID = pc.ProductID"
+ " FOR xml path('')),1,1,'') as CategoryList,"
+ " stuff((select ',' + cast(ps.SectionID as varchar)"
+ " FROM ProductSection ps WHERE p.ProductID = ps.ProductID"
+ " FOR xml path('')),1,1,'') as SectionList";
if (extraFieldList.Any())
{
foreach (var f in extraFieldList) query += ", " + f;
}
query += " from Product p inner join ProductVariant pv on p.ProductID = pv.ProductID and pv.IsDefault = 1";
if (_storeId > 0)
query += string.Format(" inner join ProductStore ps on p.ProductID = ps.ProductID and ps.StoreID = {0}", _storeId);
query += " left join ProductManufacturer pm on p.ProductID = pm.ProductID";
if (maxRows > 0)
query += " ORDER BY p.ProductID DESC) as sub ORDER BY sub.ProductID ASC";
else
query += " ORDER BY p.ProductID ASC";
}
public List<string> SplitRow(string row, string columnEnd = null, string columnStart = "[")
{
if (string.IsNullOrEmpty(row)) return new List<string>();
if (columnEnd == null || columnEnd.Length < 1)
columnEnd = ","; //default to comma-separated (works with Json or CSV)
//Logic Considerations:
//can't just split on columnEnd as these characters could exist inside a field
//can't assume field ends with a quote because numerical fields may not have quotes
//and some fields could have internal \"'s
//so if it starts with a quote, then it must end with a quote-columnEnd
//if no quote at the start then end at next columnEnd
bool startsWithQuote = false;
var trimStart = new[] { ' ', '[', ']', '\t', '\r', '\n' }; //commas and quotes trimmed separately
var trimChars = new[] { ' ', '[', ']', ',', '\t' }; //quotes trimmed separately
var trimQuotes = new[] { '\"' };
string separator1 = "\"" + columnEnd; //if startswithquote
string separator2 = columnEnd;
var cols = new List<string>();
//trim off any extra characters before the first '[' (or other columnStart characters)
var start = 0;
if (columnStart != null && columnStart.Length > 0)
{
start = row.IndexOf(columnStart);
if (start > 0) row = row.Substring(start);
}
while (true)
{
row = row.TrimStart(trimStart); //don't trim quotes or commas yet
startsWithQuote = row.StartsWith("\"");
var separator = startsWithQuote ? separator1 : separator2;
start = startsWithQuote ? 1 : 0; //look past the first quote
var end = row.IndexOf(separator, start, StringComparison.Ordinal);
var item = end < 0 ? row : row.Substring(0, end);
if (startsWithQuote) item = item.Trim(trimQuotes); //only trim qoutes here
item = item.Trim(trimChars);
cols.Add(item);
if (end < 0) break;
row = startsWithQuote ? row.Substring(end + 2) : row.Substring(end + 1);
}
return cols;
}
