public void GetSqlStatements(ref ExcelToSql.ExcelToSqlService ExcelToSql)
{
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
parameters.SqlStatement.SqlScript = "select * from dbo.tbl_SqlStatements order by Command, ProcessOrder";
parameters.SqlStatement.WorksheetName = rngp_SqlStatements.WorksheetName;
parameters.SqlStatement.RangeName = rngp_SqlStatements.RangeName;
var rn = rngp_SqlStatements.GetRange();
if (rn != null)
{
rn.Cells.ClearContents();
}
ExcelToSql.GetCommand(parameters);
}
private void TeamFile_UploadPlanningArea()
{
if (!_ConnectionOK)
{
return;
}
try
{
//Validate("DuplicateCombo");
Validate("DuplicateCombo");
Validate("InvalidRow");
if (!ValidationResult())
{
return;
};
ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService();
excelToSql.ExcelApplictaion = this.ExcelApplication;
excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
//runtime parameterek létrehozása
MyParameters myparams = GetMyParameters();
parameters.RuntimeParameters = myparams;
var statements = _SqlSatementModule.GetSqlStatementList();
var query = CreateUploadAreaList();
excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_UploadPlanningArea", _tblTeamFileTempDefinition, query, null);
System.Windows.Forms.MessageBox.Show("Done!");
rngp_LastUpload.SetValue(DateTime.Now);
//rngp_PlanningAreaChanged.SetValue("FALSE");
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
}
private void TeamFile_DownloadPlanningArea()
{
if (!_ConnectionOK)
{
return;
}
ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService();
excelToSql.ExcelApplictaion = this.ExcelApplication;
try
{
excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
MyParameters myparams = GetMyParameters();
parameters.RuntimeParameters = myparams;
var statements = _SqlSatementModule.GetSqlStatementList();
this.ExcelApplication.ScreenUpdating = false;
this.ExcelApplication.EnableEvents = false;
var query = CreateUploadAreaList();
excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_DownloadPlanningArea",_tblTeamFileTempDefinition,query,null);
//ClearCellsWithTypeWhereColumnEmpty(rngcl_PlanningAreaFixed.GetRange(),InteropExcel.XlCellType.xlCellTypeConstants, 2);
rngp_LastDownload.SetValue(DateTime.Now);
//rngp_PlanningAreaChanged.SetValue("FALSE");
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
finally
{
this.ExcelApplication.ScreenUpdating = true;
this.ExcelApplication.EnableEvents = true;
}
}
private void DownloadMasterData()
{
ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService();
_excelToSql.ExcelApplictaion = this.ExcelApplication;
_excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
try
{
MyParameters myparams = GetMyParameters();
parameters.RuntimeParameters = myparams;
if (_SqlSatementModule.TestQuery(ref _excelToSql))
{
_ConnectionOK = true;
_SqlSatementModule.GetSqlStatements(ref _excelToSql);
var statements = _SqlSatementModule.GetSqlStatementList();
_excelToSql.CommandRunner<object>(parameters, statements, "TeamFile_DownloadMasterData", null, null);
}
else
{
_ConnectionOK = false;
}
}
catch (Exception ex)
{
_ConnectionOK = false;
ExceptionHandler.HandleException(ex);
}
}
private void UploadSummaryArea()
{
if (!_ConnectionOK)
{
return;
}
ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService();
excelToSql.ExcelApplictaion = this.ExcelApplication;
try
{
excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
var statements = _SqlSatementModule.GetSqlStatementList();
excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_UploadSummaryArea", _tblSummaryFileTempDefinition, null);
System.Windows.Forms.MessageBox.Show("Done!");
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
finally
{
}
}
private void DownloadSummaryReport()
{
if (!_ConnectionOK)
{
return;
}
ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService();
excelToSql.ExcelApplictaion = this.ExcelApplication;
this.ExcelApplication.ScreenUpdating = false;
this.ExcelApplication.EnableEvents = false;
this.ExcelApplication.DisplayAlerts = false;
try
{
excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
MyParameters myparams = GetMyParameters();
parameters.RuntimeParameters = myparams;
this.ExcelApplication.TryGetSheet("TeamApproval").Delete();
CreateModuleSheets(this.ActiveWorkbook);
InitModuleObjectRanges(this.ActiveWorkbook, true);
var statements = _SqlSatementModule.GetSqlStatementList();
excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_DownloadSummaryReport", null, null);
FormatReport();
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
finally
{
this.ExcelApplication.ScreenUpdating = true;
this.ExcelApplication.EnableEvents = true;
this.ExcelApplication.DisplayAlerts = true;
}
}
public bool TestQuery(ref ExcelToSql.ExcelToSqlService ExcelToSql)
{
bool blOK;
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
parameters.SqlStatement.SqlScript = "select top (1) Id from dbo.tbl_SqlStatements";
try
{
ExcelToSql.ExecuteCommand(parameters);
blOK = true;
}
catch (Exception)
{
blOK = false;
throw;
}
return blOK;
}
private void DownloadMasterData()
{
ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService();
_excelToSql.ExcelApplictaion = this.ExcelApplication;
_excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
try
{
if (_SqlSatementModule.TestQuery(ref _excelToSql))
{
_ConnectionOK = true;
_SqlSatementModule.GetSqlStatements(ref _excelToSql);
}
else
{
_ConnectionOK = false;
}
}
catch (Exception ex)
{
_ConnectionOK = false;
ExceptionHandler.HandleException(ex);
}
}
private void CurrentTeamFile_Upload()
{
if (!_ConnectionOK)
{
return;
}
ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService();
excelToSql.ExcelApplictaion = this.ExcelApplication;
try
{
InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("CurrentTeam");
ws.Activate();
excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
var statements = _SqlSatementModule.GetSqlStatementList();
this.ExcelApplication.ScreenUpdating = false;
this.ExcelApplication.EnableEvents = false;
var query = CreateUploadAreaList();
if (query.Count > 0)
{
excelToSql.CommandRunner<CurrentTeam>(parameters, statements, "CurrentTeamFile_Upload", _tblCurrentTeam, query, null);
rngp_UploadArea.GetRange().ClearContents();
}
System.Windows.Forms.MessageBox.Show("Done!");
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
finally
{
this.ExcelApplication.ScreenUpdating = true;
this.ExcelApplication.EnableEvents = true;
}
}
private void CurrentTeamFile_DownloadReport()
{
if (!_ConnectionOK)
{
return;
}
ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService();
_excelToSql.ExcelApplictaion = this.ExcelApplication;
_excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW);
ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters();
try
{
InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("Report");
ws.Activate();
var statements = _SqlSatementModule.GetSqlStatementList();
this.ExcelApplication.ScreenUpdating = false;
this.ExcelApplication.EnableEvents = false;
_excelToSql.CommandRunner<object>(parameters, statements, "CurrentTeamFile_DownloadReport", null, null);
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
}
finally
{
this.ExcelApplication.ScreenUpdating = true;
this.ExcelApplication.EnableEvents = true;
}
}
