public static ImportResult <Test1ImportResult> ImportTest1(HttpPostedFileBase httpPostedFileBase)
{
var fileData = ImporterHelper.ImportFromFile(httpPostedFileBase, new Test1ImportFileStructure());
var venuesImport = new Test1Import(fileData);
return(venuesImport.Import());
}
private void load(List <FieldMatch> fields)
{
if (fields.Count == 0)
{
return;
}
var parameters = new List <Interfaces.Params>();
var insertSql = "insert into {0}.{1} (";
var selectSql = "select ";
string valuesStr = ") values (";
DataTable datas = new DataTable();
foreach (var f in fields)
{
insertSql += "\"" + f.Dest.Name + "\",";
valuesStr += ":" + f.Dest.Name + ",";
selectSql += f.Src + ",";
var p = new Interfaces.Params()
{
paramName = f.Dest.Name
};
ImporterHelper.SetParamType(f.Dest, p);
parameters.Add(p);
datas.Columns.Add(f.Src);
}
selectSql = selectSql.TrimEnd(',');
insertSql = insertSql.TrimEnd(',');
valuesStr = valuesStr.TrimEnd(',');
insertSql += valuesStr + ")";
selectSql += string.Format(" FROM [{0}#dbf]", Name);
//инициируем считывание
var reader = (new OleDbCommand(selectSql, Connection)).ExecuteReader();
using (SqlWork sw = new SqlWork(_pgConnect, true))
{
sw.BeginTransaction();
sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name);
for (int i = 0, report = 0; reader.Read(); report++)
{
//добавляем в таблицу
List <object> colData = new List <object>();
for (int j = 0; j < datas.Columns.Count; j++)
{
colData.Add(reader[j]);
}
datas.Rows.Add(colData.ToArray());
i++;
if (i >= _rowsInSect)
{
//кладем в базу
for (int j = 0; j < datas.Rows.Count; j++)
{
for (int k = 0; k < datas.Columns.Count; k++)
{
ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]);
}
sw.ExecuteNonQuery(parameters.ToArray());
}
i = 0;
datas.Clear();
}
ReportProgress(0, report);
}
reader.Close();
sw.EndTransaction();
}
}
/// <summary>
/// Выгружаем поля
/// </summary>
private string FormQuerySQL(List <FieldMatch> fields, ref List <Interfaces.Params>[] paramList, ref List <String> columns)
{
for (int i = 0; i < paramList.Count(); i++)
{
paramList[i] = new List <Interfaces.Params>();
}
string fieldsStr = String.Empty;
string valuesStr = String.Empty;
foreach (var f in fields)
{
fieldsStr += String.Format("\"{0}\",", f.Dest.Name);
valuesStr += String.Format(":{0},", f.Dest.Name);
var p = new Interfaces.Params()
{
paramName = f.Dest.Name
};
ImporterHelper.SetParamType(f.Dest, p);
foreach (var par in paramList)
{
par.Add(p.Clone());
}
columns.Add(f.Src);
}
if (!_loadGeom)
{
fieldsStr = fieldsStr.TrimEnd(',');
valuesStr = valuesStr.TrimEnd(',');
}
else
{
// добавляем поле геометрии
string geomSql = string.Format("st_geomfromwkb(:{0},{1})", _pgTable.GeomField, _pgTable.Srid);
// нужно ли привести геометрию к мульти
if (_isMulti)
{
geomSql = String.Format("st_multi({0})", geomSql);
}
valuesStr += geomSql;
fieldsStr += _pgTable.GeomField;
columns.Add(_pgTable.GeomField);
var p = new Interfaces.Params()
{
paramName = _pgTable.GeomField, type = DbType.Binary, typeData = NpgsqlTypes.NpgsqlDbType.Bytea
};
foreach (var par in paramList)
{
par.Add(p.Clone());
}
}
return(string.Format("INSERT INTO {0}.{1} ({2}) VALUES ({3});",
_pgTable.SchemeName, _pgTable.Name, fieldsStr, valuesStr));
}
private void load(List <FieldMatch> fields)
{
if (fields.Count == 0 && _loadGeom == false)
{
return;
}
// размер блока для загрузки в БД
int rowsInSect = Math.Min(100, Math.Max(1, RowsCount / 10));
// список параметров для загрузки в БД
List <Interfaces.Params>[] paramList = new List <Interfaces.Params> [rowsInSect];
// названия колонок таблицы
List <String> columns = new List <string>();
string wktTo = GetSRText(_pgTable.Srid);
string wktFrom = GetSRText(_srid);
//инициируем считывание
_shpWork.StartRead();
List <string> errors = new List <string>();
using (var sw = new SqlWork(_connect, true))
{
sw.BeginTransaction();
sw.sql = FormQuerySQL(fields, ref paramList, ref columns);
for (int i = 0, report = 0; _shpWork.Read() && report < RowsCount; report++)
{
//добавляем в таблицу
for (int j = 0; j < columns.Count; j++)
{
try
{
if (_loadGeom == true && j == columns.Count - 1)
{
ImporterHelper.SetParamValue(
paramList[i][j],
_shpWork.TransformedCurrentGeometry(wktTo, wktFrom));
}
else
{
ImporterHelper.SetParamValue(
paramList[i][j],
_shpWork[columns[j]]);
}
}
catch (Exception e)
{
if (!errors.Contains(e.Message))
{
errors.Add(e.Message);
}
}
}
if (++i >= rowsInSect || report == RowsCount - 1)
{
//кладем в базу
for (int k = 0; k < i; k++)
{
try
{
sw.ExecuteNonQuery(paramList[k].ToArray());
}
catch (Exception e)
{
if (!errors.Contains(e.Message))
{
errors.Add(e.Message);
}
}
}
i = 0;
ReportProgress(0, report);
}
}
_shpWork.EndRead();
sw.EndTransaction();
if (errors.Count > 0)
{
throw new Exception(String.Join(Environment.NewLine, errors));
}
}
}
private void load(List <FieldMatch> fields, string rngString)
{
if (fields.Count == 0)
{
return;
}
var parameters = new List <Interfaces.Params>();
var insertSql = "insert into {0}.{1} (";
string valuesStr = ") values (";
foreach (var f in fields)
{
insertSql += "\"" + f.Dest.Name + "\",";
valuesStr += ":" + f.Dest.Name + ",";
var p = new Interfaces.Params()
{
paramName = f.Dest.Name
};
ImporterHelper.SetParamType(f.Dest, p);
parameters.Add(p);
}
insertSql = insertSql.TrimEnd(',');
valuesStr = valuesStr.TrimEnd(',');
insertSql += valuesStr + ")";
//читаем excel
String sheetString = rngString.Split('$')[0];
String rangeString = rngString.Split('$')[1];
var excelAppl = new Excel.Application()
{
Visible = false
};
var workbook = excelAppl.Workbooks.Open(_inputFile.FullName);
Excel.Worksheet workSheet = (Excel.Worksheet)(workbook.Sheets.Item[sheetString]);
Excel.Range workRange = workSheet.get_Range(rangeString);
// Где на странице Excel находятся данные
int lastRow = workRange.Row + workRange.Rows.Count - 1;
int firstRow = workRange.Row;
int lastColumn = workRange.Column + workRange.Columns.Count - 1;
int firstColumn = workRange.Column;
// Получаются данные из excel в виде массива объектов
var excValues = (object[, ])(workRange.Value);
workbook.Close();
// Сколько строк и сколько столбцов
_rowsCount = excValues.GetLength(0) - 1; //первая строка заголовки
var colsCount = excValues.GetLength(1);
excelAppl.Quit();
DataTable datas = new DataTable();
for (int j = 1; j <= colsCount; j++)
{
datas.Columns.Add(excValues[1, j].ToString());
}
using (SqlWork sw = new SqlWork(_pgConnect, true))
{
sw.BeginTransaction();
sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name);
for (int i = 2, report = 0; i <= _rowsCount + 1; i++)
{
//добавляем в таблицу
datas.Rows.Add(getRow(excValues, i, colsCount));
report++;
if (report >= _rowsInSect)
{
//убираем ненужные колонки
List <object> cols2delete = new List <object>();
for (int j = 0; j < datas.Columns.Count; j++)
{
if (fields.Find(w => w.Src == datas.Columns[j].Caption) == null)
{
cols2delete.Add(datas.Columns[j]);
}
}
foreach (var c in cols2delete)
{
datas.Columns.Remove((DataColumn)c);
}
//кладем в базу
for (int j = 0; j < datas.Rows.Count; j++)
{
for (int k = 0; k < datas.Columns.Count; k++)
{
ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]);
}
sw.ExecuteNonQuery(parameters.ToArray());
}
report = 0;
datas = new DataTable();
for (int j = 1; j <= colsCount; j++)
{
datas.Columns.Add(excValues[1, j].ToString());
}
}
ReportProgress(0, i - 2);
}
sw.EndTransaction();
}
}
public void Execute()
{
if (!IsValidDateRange)
{
Log.Debug(string.Format("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask: Out of Time Range; Start: {0}, End: {1}; Now: {2}", StartUpWindow, EndingWindow, DateTime.Now), this);
return;
}
List <KeyValuePair <string, string> > summary = new List <KeyValuePair <string, string> >();
try
{
Log.Debug(string.Format("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask:Execute - start; Time Range; Start: {0}, End: {1}; Now: {2}", StartUpWindow, EndingWindow, DateTime.Now), this);
var templates = ImporterHelper.GetExtendedDocumentTemplates();
foreach (var template in templates)
{
var items = GetItemsByTemplate(template);
Log.Debug(string.Format("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask: Found {1} in {0}", template.TemplateFullName, items.Count()), this);
foreach (var item in items)
{
var createdDate = item.GetField("Statistics", "__created").GetDate();
var ownerEmailsDictionary = item.GetNameValueDictionary("Repository Owner Emails");
var documentStateDictionary = item.GetNameValueDictionary("Repository Document State").Where(f => (ownerEmailsDictionary.ContainsKey(f.Key)) && (f.Value == "P" || f.Value == "R"));
if (documentStateDictionary.Count() < 1)
{
continue;
}
var repositoriesDictionary = GetRepositoryDictionary(item.GetMultilistItems("Repositories"));
// Add different emails.
var senderEmails = GetDifferentEmails(documentStateDictionary, ownerEmailsDictionary);
// Add submiter in case it is not included
AddDifferentString(senderEmails, item.GetText("Submitter Name"));
// Greate mail body
StringBuilder body = new StringBuilder();
body.AppendFormat("<h2>'{0}' file has repository status pending for approval</h2>", item.DisplayName);
body.AppendFormat("<p><b>Date: </b>{0}<br />", item.GetField("Statistics", "__created").GetDate());
body.AppendFormat("<b>Path: </b>{0}</p>", item.Paths.FullPath);
foreach (var ds in documentStateDictionary)
{
string key = null;
repositoriesDictionary.TryGetValue(ds.Key, out key);
body.AppendFormat("<b>Repository</b> '{0}' <b>status</b> '{1}'<br />", key ?? ds.Key, ds.Value);
}
string sBody = body.ToString();
foreach (var email in senderEmails)
{
// Add to summary
summary.Add(new KeyValuePair <string, string>(email.ToLower(), sBody));
}
}
}
var groups = summary.ToLookup(f => f.Key);
Log.Debug(string.Format("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask: Sending {0} emails", groups.Count()), this);
MailQProvider mailq = new MailQProvider();
foreach (var item in groups)
{
var toAddress = item.Key;
var bodys = item.Select(f => f.Value);
mailq.SendEmailWithOutTemplate(
"*****@*****.**",
null,
null,
"Marketing Collateral Distribution Utility - Repository status report " + toAddress,
string.Concat(bodys) + "<br/><br/>" + DateTime.Now
);
}
}
catch (Exception ex)
{
Log.Error(String.Format("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask:Execute failed due to {0}", ex.Message), this);
}
finally
{
Log.Debug("Genworth.SitecoreExt.ScheduledTasks.MarketingUtilityStatusTask: Ends", this);
}
}
