public IEnumerable <cParameter> Run(сRequest pSQL)
{
List <cParameter> res = new List <cParameter>();
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "sqlsrv2.vopak.local";
builder.UserID = "dwreader";
builder.Password = "******";
builder.InitialCatalog = "for_cubes";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
// String sql = "SELECT name, collation_name FROM sys.databases";
using (SqlCommand command = new SqlCommand(pSQL.Request, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
int i = 0;
if (pSQL.IsHead)
{
foreach (DataColumn c in dt.Columns)
{
pSQL.Sheet.Cells[pSQL.Row, pSQL.Column + i++].value = c.ColumnName;
}
pSQL.Row++;
}
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
ExcelApp.Range c1 = (ExcelApp.Range)pSQL.Sheet.Cells[pSQL.Row, pSQL.Column];
ExcelApp.Range c2 = (ExcelApp.Range)pSQL.Sheet.Cells[pSQL.Row + dt.Rows.Count - 1, dt.Columns.Count + pSQL.Column - 1];
ExcelApp.Range range = pSQL.Sheet.get_Range(c1, c2);
range.Value = arr;
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
return(res);
}
public IEnumerable <cParameter> RunMsSQL(сRequest pSQL)
{
List <cParameter> res = new List <cParameter>();
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "sqlsrv2.vopak.local";
builder.UserID = "dwreader";
builder.Password = "******";
builder.InitialCatalog = "for_cubes";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
// String sql = "SELECT name, collation_name FROM sys.databases";
using (SqlCommand command = new SqlCommand(pSQL.Request, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
cParameter r = new cParameter()
{
Par1 = reader.GetString(0), Name = reader.GetString(1), EMail = reader.GetString(2)
};
if (reader.FieldCount > 3)
{
r.Par2 = reader.GetString(2);
}
res.Add(r);
// Console.WriteLine("{0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
return(res);
}
void RunMDX(сRequest pRequest, cParameter pParameter)
{
AdomdConnection conn = new AdomdConnection("Data Source=localhost;Catalog=YourDatabase");
conn.Open();
string commandText = @"SELECT FLATTENED
PredictAssociation()
From
[Mining Structure Name]
NATURAL PREDICTION JOIN
(SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t ";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(Convert.ToString(dr[0]));
}
dr.Close();
conn.Close();
}
public void ExecuteExcelMacro(string pSourceFile, StringBuilder pSuccess, StringBuilder pError)
{
pSuccess.Append($"{DateTime.Now} File {pSourceFile}{Environment.NewLine}");
ExcelApp.Application ExcelApp = null;
ExcelApp.Workbook ExcelWorkBook = null;
List <cParameter> ResPar = null;
bool Result = true, IsSendFile = true, IsMoveOldFile = false;
DeleteSend DeleteSend = null;
string DeletePage = null, HidePage = null, PathCopy = null;
cArx Arx = null;
try
{
сRequest ParRequest = null;
List <сRequest> Requests = new List <сRequest>();
string Macro = "Main", StartMacro = null;
string Email = null;
ExcelApp = new ExcelApp.Application();
ExcelApp.DisplayAlerts = false;
ExcelApp.Visible = false;
//ExcelApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
ExcelWorkBook = ExcelApp.Workbooks.Open(pSourceFile);
ExcelApp.Worksheet worksheet = (ExcelApp.Worksheet)ExcelWorkBook.Worksheets["config"];
var range = worksheet.UsedRange;
int rows_count = range.Rows.Count;
for (int i = 1; i <= rows_count; i++)
{
string str = worksheet.Cells[i, 1].value;
//string str = String.Format("[{0}] ", s);
if (str != null)
{
if (str.Equals("StartMacro"))
{
StartMacro = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("Macro"))
{
Macro = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("e-mail"))
{
Email = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("pSQL"))
{
ParRequest = GetRequest(worksheet, ExcelWorkBook, i, eClient.MsSql, true);
}
else
if (str.Equals("pMDX"))
{
ParRequest = GetRequest(worksheet, ExcelWorkBook, i, eClient.MDX, true);
}
else
if (str.Equals("SQL"))
{
Requests.Add(GetRequest(worksheet, ExcelWorkBook, i, eClient.MsSql));
}
else
if (str.Equals("MDX"))
{
Requests.Add(GetRequest(worksheet, ExcelWorkBook, i, eClient.MDX));
}
else
if (str.Equals("DeletePage"))
{
DeletePage = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("HidePage"))
{
HidePage = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("PathCopy"))
{
PathCopy = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("IsMoveOldFile"))
{
IsMoveOldFile = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("IsSendFile"))
{
IsSendFile = worksheet.Cells[i, 2].value;
}
else
if (str.Equals("DeleteSend"))
{
DeleteSend = new DeleteSend()
{
Pages = worksheet.Cells[i, 2].value, eMails = worksheet.Cells[i, 3].value
}
}
;
else
if (str.Equals("Arx"))
{
Arx = new cArx()
{
Row = i, Days = Convert.ToInt32(worksheet.Cells[i, 3].value), PathMove = worksheet.Cells[i, 4].value, EMail = worksheet.Cells[i, 5].value, DateFormatFile = worksheet.Cells[i, 6].value
}
}
;
if (str.Equals("YearAgoDay"))
{
worksheet.Cells[i, 2].value = YearAgoDay;
}
if (str.Equals("TwoYearAgoDay"))
{
worksheet.Cells[i, 2].value = TwoYearAgoDay;
}
}
}
//var r = pSourceFile.Split('.');
var path = Path.Combine(Path.GetDirectoryName(pSourceFile), "Result");
var FileName = Path.GetFileNameWithoutExtension(pSourceFile);
var Extension = Path.GetExtension(pSourceFile);
if (ParRequest != null)
{
ResPar = MsSQL.RunMsSQL(ParRequest).ToList();
}
else
{
ResPar = new List <cParameter>()
{
new cParameter()
{
EMail = Email, Name = ""
}
}
};
if (Arx != null && Arx.Days >= DateTime.Now.Day)
{
var ParArx = new List <cParameter>();
foreach (var el in ResPar)
{
var ArxEl = new cParameter(el);
if (!string.IsNullOrEmpty(Arx.EMail))
{
ArxEl.EMail = Arx.EMail;
}
if (!string.IsNullOrEmpty(Arx.PathMove))
{
ArxEl.PathMove = Arx.PathMove;
}
if (!string.IsNullOrEmpty(Arx.DateFormatFile))
{
ArxEl.DateFormatFile = Arx.DateFormatFile;
}
ArxEl.DateReport = Arx.FirstDayMonth;
ParArx.Add(ArxEl);
}
ResPar.AddRange(ParArx);
}
if (IsMoveOldFile)
{
var ext = ParRequest == null?"":"_*";
// var MoveFileName = Path.Combine(path, $"{FileName}_????????{ext}{Extension}");
UtilDisc.MoveAllFilesMask(path, $"{FileName}_????????{ext}{Extension}", Path.Combine(path, "Arx"));
}
foreach (var el in ResPar)
{
if (Arx != null)
{
worksheet.Cells[Arx.Row, 2].value = el.DateReport;
}
if (ParRequest != null)
{
worksheet.Cells[ParRequest.Row, ParRequest.Column].value = el.Par1;
worksheet.Cells[ParRequest.Row, ParRequest.Column + 1].value = el.Name;
worksheet.Cells[ParRequest.Row, ParRequest.Column + 2].value = el.EMail;
if (!string.IsNullOrEmpty(el.Par2))
{
worksheet.Cells[ParRequest.Row, ParRequest.Column + 3].value = el.Par2;
}
}
foreach (var r in Requests)
{
if (r.Client == eClient.MsSql)
{
pSuccess.Append($"{DateTime.Now} Start SQL = {r}{Environment.NewLine}");
MsSQL.Run(r);
pSuccess.Append($"{DateTime.Now} End SQL = {r}{Environment.NewLine}");
}
}
pSuccess.Append($"{DateTime.Now} Start Macro = {Macro}{Environment.NewLine}");
ExcelApp.Run(Macro);
pSuccess.Append($"{DateTime.Now} End Macro = {Macro}{Environment.NewLine}");
var elName = (string.IsNullOrEmpty(el.Name) ? "" : "_" + el.Name.Trim());
el.FileName = Path.Combine(path, $"{FileName}_{el.strDateReportFile}{elName}{Extension}");
if (File.Exists(el.FileName))
{
File.Delete(el.FileName);
}
ExcelWorkBook.SaveAs(el.FileName);
pSuccess.Append($"{DateTime.Now} Save file {el.FileName}{Environment.NewLine}");
}
}
catch (Exception ex)
{
var err = $"{DateTime.Now} SourceFile=> {pSourceFile} Error=> {ex.Message}{Environment.NewLine}{Environment.StackTrace}{Environment.NewLine}";
pError.Append(err);
pSuccess.Append(err);
}
finally
{
// Закриваємо ексель.
if (ExcelWorkBook != null)
{
ExcelWorkBook.Close(false);
}
if (ExcelWorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook);
}
}
if (Result && ResPar != null)
{
if (!string.IsNullOrEmpty(DeletePage) || !string.IsNullOrEmpty(HidePage) || !string.IsNullOrEmpty(PathCopy))
{
string[] DeletePages = null;
string[] HidePages = null;
if (!string.IsNullOrEmpty(DeletePage))
{
DeletePages = DeletePage.Split(',');
}
if (!string.IsNullOrEmpty(HidePage))
{
HidePages = HidePage.Split(',');
}
//Видаляємо сторінки
HideDeletePage(ExcelApp, ResPar, DeletePages, HidePages, pSuccess, pError, ref Result, PathCopy);
}
//Відправляємо Листи
SendMail(ResPar, pSuccess, pError, IsSendFile);
//Готуємо і відправляємо скорочену версію звіта.
if (DeleteSend != null && !string.IsNullOrEmpty(DeleteSend.Pages) && ResPar.Count == 1)
{
var DeletePages = DeleteSend.Pages.Split(',');
HideDeletePage(ExcelApp, ResPar, DeletePages, null, pSuccess, pError, ref Result, PathCopy, true);
SendMail(ResPar, pSuccess, pError, IsSendFile);
}
}
if (ExcelApp != null)
{
ExcelApp.Application.Quit();
ExcelApp.Quit();
}
if (ExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}