static void Main(string[] args)
{
MyEventListener eventListener = new MyEventListener();
MyEventSource eventSource = new MyEventSource();
eventSource.AnEvent += eventListener.Receive;
MyEventArgs eventArgs = new MyEventArgs();
eventSource.InvokeAnEvent(eventArgs);
const int count = 5000000;
DateTime then = DateTime.Now;
for (int iter = 0; iter < count; ++iter)
{
eventSource.InvokeAnEvent(eventArgs);
}
TimeSpan diff = DateTime.Now - then;
Console.WriteLine(
"{0} calls took {1:0.00} seconds (listener received {2} calls)",
count,
diff.TotalSeconds,
eventListener.Count
);
Console.ReadKey();
}
public void GCTest()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.Listen(provider);
Assert.That(EventManager.Count, Is.EqualTo(1), "WeakEventManager.Count");
provider.Raise();
Assert.That(listener.Count, Is.EqualTo(1), "listener.Count");
// var weakListener = new WeakReference(listener);
listener = null;
// how long we have to wait until the GC collects?
GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
var stopwatch = new Stopwatch(); stopwatch.Start();
while (EventManager.Count > 0)
{
provider.Raise();
Thread.Sleep(10);
}
Trace.WriteLine("Collected after " + stopwatch.Elapsed);
//Assert.That(WeakEventManager.Count, Is.EqualTo(0),"WeakEventManager.Count");
}
static void Main(string[] args)
{
var eventListener = new MyEventListener();
var eventSource = new MyEventSource();
eventSource.AnEvent += eventListener.Receive;
var eventArgs = new EventArgs();
eventSource.InvokeAnEvent(eventArgs);
const int Count = 3000000;
var then = DateTime.Now;
for (var iter = 0; iter < Count; ++iter)
{
eventSource.InvokeAnEvent(eventArgs);
}
var diff = DateTime.Now - then;
Console.WriteLine(
"{0} calls took {1:0.00} seconds (listener received {2} calls)",
Count,
diff.TotalSeconds,
eventListener.Count
);
Console.ReadKey();
}
private static void InitListener(IEnumerable<EventSource> sources)
{
var listener = new MyEventListener();
foreach (var source in sources)
{
listener.EnableEvents(source, EventLevel.LogAlways);
}
}
public void EventHandler()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.Listen(provider);
provider.Raise();
Assert.That(listener.Count, Is.EqualTo(listener.Count), "listener.Count");
}
public void BasicˑTest()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.RegisterˑMyEvent(provider);
provider.RaiseˑMyEvent();
Assert.That(listener.EventCount, Is.EqualTo(1));
listener.ReleaseˑMyEvent();
provider.RaiseˑMyEvent();
Assert.That(listener.EventCount, Is.EqualTo(1));
}
public void GCCollectTest()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.Listen(provider);
Assert.That(EventManager.Count, Is.EqualTo(1), "WeakEventManager.Count");
provider.Raise();
Assert.That(listener.Count, Is.EqualTo(1), "listener.Count");
listener = null;
EventManager.Collect(true);
Assert.That(EventManager.Count, Is.EqualTo(0), "WeakEventManager.Count");
}
public void ProviderGarbageCollectionˑTest()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.RegisterˑMyEvent(provider);
provider.RaiseˑMyEvent();
Assert.That(listener.EventCount, Is.EqualTo(1));
var c = EventHandle.StatisticsːInstancesˑCurrent;
provider = null;
ForceGarbageCollection();
Assert.That(((EventHandle)listener._MyEventHandle).IsAlive, Is.EqualTo(false));
}
private static void Main(string[] args)
{
using (MyEventListener myListener = new MyEventListener())
using (MyEventSource source = new MyEventSource())
{
myListener.EnableEvents(source, EventLevel.Verbose);
if (source.ConstructionException != null)
{
throw source.ConstructionException;
}
source.String("Hello world");
source.Int(10);
source.Bool(true);
}
}
public void ListenerGarbageCollectionˑTest()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.RegisterˑMyEvent(provider);
provider.RaiseˑMyEvent();
Assert.That(listener.EventCount, Is.EqualTo(1));
var c = EventHandle.StatisticsːInstancesˑCurrent;
listener = null;
ForceGarbageCollection();
provider.RaiseˑMyEvent();
Assert.That(((IEventSourceInternal)provider.MyEvent).GetContainers().Count, Is.EqualTo(0));
}
protected void btnBulkCopy_Click(object sender, EventArgs e)
{
try
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
//pkgLocation = "D:\\dts\\TestDataPKG.dtsx";
app = new Application();
//Load Package from File system.
//pkg = app.LoadPackage(pkgLocation, eventListener);
//app.SaveToXml("D:\\test.xml", pkg, eventListener);
//pkgResults = pkg.Validate(null, null, null, null);
//pkgResults = pkg.Execute(null, null, eventListener, null, null);
string pkgName = String.Empty;
string sqlServer = String.Empty;
string userID = String.Empty;
string pwd = String.Empty;
pkgName = ConfigurationManager.AppSettings["PackageName"]; // Give the Name of the DTS Package in SQL Server.
sqlServer = ConfigurationManager.AppSettings["ServerName"]; // Name of the SQL Server.
userID = ConfigurationManager.AppSettings["UserName"]; // Sql Server User ID.
pwd = ConfigurationManager.AppSettings["Password"]; // Sql Server Password.
AdioDTS.AdioDTS adiodts = new AdioDTS.AdioDTS();
string retval = adiodts.RunDTS(sqlServer, userID, pwd, pkgName);
//Load Package from sql server.
//pkg = app.LoadFromDtsServer(pkgName, sqlServer, null);
//pkg = app.LoadFromSqlServer(pkgName, sqlServer, userID, pwd, null);
//pkgResults = pkg.Execute();//(null, null, eventListener, null, null);
lblResult.Text = "DTS Package Executed Successfully...";
btnBulkCopy.Enabled = false;
btnImportData.Enabled = true;
}
catch (Exception ex)
{
lblResult.Text = "Exception: " + ex.Message;
}
}
public void PerformanceTest1()
{
var provider = new MyEventProvider();
var listener = new MyEventListener();
listener.Listen(provider);
var count = 1000000;
var stopwatch = new Stopwatch(); stopwatch.Start();
for (int i = 0; i < count; i++)
{
provider.Raise();
}
stopwatch.Stop();
Assert.That(listener.Count, Is.EqualTo(count), "listener.Count");
Trace.WriteLine(string.Format("Time: {0}", new TimeSpan(stopwatch.ElapsedTicks * 1)));
}
private void btnBuildDaily_Click(object sender, EventArgs e)
{
try
{
string pkgLocation;
runtime.Package pkg;
runtime.Application app;
runtime.DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation = @"D:\SSIS\SSIS_Bank\Integration Services Project1\DB_TransToDaily.dtsx";;
app = new runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Metodo para lanzar las ETL
/// </summary>
/// <param name="pkgLocation"></param>
public void lanzarETL(string pkgLocation, ConfigGlassWindow parent)
{
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
parentWin = parent;
eventListener = new MyEventListener(parentWin);
//pkgLocation =
// @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
// @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
//pkg.Variables.Add("strNombreServidorLocal", false, "", "ASINE1159\\SQLEXPRESS");
//pkg.Variables["strNombreServidorLocal"].Value = "ASINE1159\\SQLEXPRESS";
pkgResults = pkg.Execute(null, pkg.Variables, eventListener, null, null);
//errores.Add(pkgResults.ToString());
//Console.WriteLine(pkgResults.ToString());
//Console.ReadKey();
}
private void btnYearly_Click(object sender, EventArgs e)
{
try
{
string pkgLocation;
runtime.Package pkg;
runtime.Application app;
runtime.DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation = @"D:\SSIS\SSIS_Bank\Integration Services Project1\DB_QuarterlyToYearly.dtsx";
app = new runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkg.Variables["User::nam"].Value = DateTime.Today.Year;
pkgResults = pkg.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void MassiveListenerGCTest()
{
var provider = new MyEventProvider();
var eventContainers = ((EventSource)provider.MyEvent).GetContainers();
var r = new System.Random();
var stopwatch = new Stopwatch(); stopwatch.Start();
const int max = 5000;
var c = eventContainers.Count;
for (int i = 1; i <= max; i++)
{
var listener = new MyEventListener();
listener.RegisterˑMyEvent(provider);
provider.RaiseˑMyEvent();
Thread.Sleep(r.Next(8, 12 + 1));
if (eventContainers.Count + c < i)
{
//OK any listener has been collected
Trace.WriteLine("GC collected at iteration " + i + " " + stopwatch.Elapsed + " " + eventContainers.Count + " alive");
c = i - eventContainers.Count;
}
listener.Dispose();
listener = null;
}
Trace.WriteLine(eventContainers.Count * 100 / max + "% alive after " + stopwatch.Elapsed);
var next = stopwatch.Elapsed.Add(TimeSpan.FromSeconds(1));
while (eventContainers.Count > 0)
{
if (stopwatch.Elapsed < next)
{
Thread.Sleep(100);
//ForceGarbageCollection();
provider.RaiseˑMyEvent(); continue;
}
Trace.WriteLine(eventContainers.Count * 100 / max + "% alive after " + stopwatch.Elapsed);
next = stopwatch.Elapsed.Add(TimeSpan.FromSeconds(1));
}
//Assert.Fail("Nothing collected!");
}
public void EjecutarETL(String path, String periodo)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation =
@"\\srvprodbd-ds\ETL\RO_CONSOLIDADO\CargaConcepto.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
Variables vars = pkg.Variables;
vars["ExcelPath"].Value = path;
pkgResults = pkg.Execute(null, null, eventListener, null, null);
Console.WriteLine(pkgResults.ToString());
Console.ReadKey();
}
private void runInsertDPtoServer(string location)
{
try
{
string pkgLocation;
runtime.Package pkg;
runtime.Application app;
runtime.DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation = location;
app = new runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
if (pkgResults.ToString() != "Success")
{
MessageBox.Show("Package run failed");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void MassiveGCTest()
{
var provider = new MyEventProvider();
var r = new System.Random();
var stopwatch = new Stopwatch(); stopwatch.Start();
const int max = 2000;
var c = EventManager.Count;
for (int i = 1; i <= max; i++)
{
var listener = new MyEventListener();
listener.Listen(provider);
provider.Raise();
Thread.Sleep(r.Next(8, 12 + 1));
if (EventManager.Count + c < i)
{
//OK any listener has been collected
Trace.WriteLine("GC collected at interation " + i + " " + stopwatch.Elapsed + " " + EventManager.Count + " alive");
c = i - EventManager.Count;
}
}
Trace.WriteLine(EventManager.Count * 100 / max + "% alive after " + stopwatch.Elapsed);
var next = stopwatch.Elapsed.Add(TimeSpan.FromSeconds(1));
while (EventManager.Count > 0)
{
if (stopwatch.Elapsed < next)
{
Thread.Sleep(10); EventManager.Collect(); continue;
}
Trace.WriteLine(EventManager.Count * 100 / max + "% alive after " + stopwatch.Elapsed);
next = stopwatch.Elapsed.Add(TimeSpan.FromSeconds(1));
}
//Assert.Fail("Nothing collected!");
}
public NUnitJson(string testAssemblyPath)
{
Eventqueue = new BlockingCollection <string>(new ConcurrentQueue <string>());
NunitListener = new MyEventListener(Eventqueue);
TestAssemblyPath = testAssemblyPath;
}
public ActionResult DownloadBrewReport(String _BatchKey, bool CreateLogFile = false)
{
// Parameterhandling
const string FILENAME_TYPE = "xlsx";
const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchBrew_Template" + "." + FILENAME_TYPE;
const string SSIS_PACKAGE = "ExcelReport_BatchBrew.dtsx";
const string FOLDER_BASE_SOURCE = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
const string FOLDER_BIN = "bin";
const string FOLDER_TEMPLATE = "template";
const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
const string LOG_FILE_NAME = "DownloadBrewBatch_Log.txt";
const int WAITTIME_IN_MS = 1000;
const int MAXWAITTIME_IN_MS = 30000;
String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory
Logger logger = null;
long nBatchKey = 0;
string sBatchName = String.Empty;
string sBatchNumber = String.Empty;
string sSAPMaterial = String.Empty;
string sSAP_Batch = String.Empty;
string sBrewLine = String.Empty;
string sShortMaterial = String.Empty;
string sSourcePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
string sDestinationPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
string sDestinationFilePath = String.Empty;
string sFileName = String.Empty;
DateTime dtCheckTime = DateTime.Now;
List <CBatch> listBatch = null;
// Log generieren
if (CreateLogFile)
{
logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
}
// Batch Key ermitteln
try
{
nBatchKey = Convert.ToInt64(_BatchKey);
}
catch
{
return(new HttpNotFoundResult(String.Format("Invalid BatchKey: {0}", _BatchKey)));
}
// Batchdaten ermitteln
String odataURI = String.Format("odata/ods/ZWebSAPInterfaceStatuses?$select=_BatchKey,BatchName,SAPMaterial,SAP_Batch,BatchProcessStartTime&$filter=_BatchKey eq {0}", _BatchKey);
PGatewayConnection gatewayConnection = DependencyResolver.Current.GetService(typeof(PGatewayConnection)) as PGatewayConnection;
try
{
listBatch = gatewayConnection.ReadItem <CODataResponse <CBatch> >(odataURI).Value;
}
catch
{
return(new HttpNotFoundResult(String.Format("OData query invalid for BatchKey: {0}", _BatchKey)));
}
// Daten prüfen
if (listBatch == null || listBatch.Where(x => x._BatchKey == nBatchKey).Count() == 0)
{
return(new HttpNotFoundResult(String.Format("Data not found for BatchKey: {0}", _BatchKey)));
}
// Namen von Auftrag ermitteln
sBatchName = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchName;
sBatchNumber = sBatchName.Substring(0, 12).Substring(sBatchName.Substring(0, 12).Length - 4, 4);
sSAPMaterial = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAPMaterial;
sSAP_Batch = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAP_Batch;
sBrewLine = sSAP_Batch.Substring(5, 1);
sShortMaterial = sSAPMaterial.Substring(sSAPMaterial.Length - 5);
string FILENAME_PREFIX = (listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchProcessStartTime).ToString("yy");
// BatchName prüfen
if (sBatchName.Equals(String.Empty))
{
return(new HttpNotFoundResult(String.Format("No Batchname found for BatchKey: {0}", _BatchKey)));
}
// Nach Datei suchen
sFileName = FILENAME_PREFIX + "_" + sBatchNumber + "_" + sBrewLine + "_1_" + sShortMaterial;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = null;
MyEventListener eventListener = new MyEventListener();
//Load the SSIS Package which will be executed
app.PackagePassword = "******";
String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);
// Execute
try
{
// Load package
package = app.LoadPackage(sPackageFilePath, eventListener);
// Set values
Variables vars = package.Variables;
vars["User::InputBatchKey"].Value = nBatchKey;
vars["User::InputDestinationFile"].Value = sFileName + "." + FILENAME_TYPE;
vars["User::InputDestinationPath"].Value = sDestinationPath;
vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
vars["User::InputCreateLogFile"].Value = CreateLogFile;
vars["User::InputLogFilePath"].Value = (logger != null) ? logger.FilePath : String.Empty;
// Log erzeugen
if (logger != null)
{
logger.Log("InputBatchKey", vars["User::InputBatchKey"].Value.ToString());
logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
}
vars["InputToOverwrite"].Value = true;
DTSExecResult results = package.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
if (logger != null)
{
logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
}
return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
}
// zyklisch prüfen ob Datei vorhanden ist, maximal MAXWAITTIME_IN_MS
for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
{
// erstmal eine Sekunde zeit geben, zyklisch prüfen
System.Threading.Thread.Sleep(WAITTIME_IN_MS);
// alle Dateien des Pfades holen die dem Suchmuster entsprechen
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(sDestinationPath));
var filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));
var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();
// nur welche die eben erstellt wurde
if (file == null)
{
continue;
}
// Falls eben erstellt...
if (file.LastWriteTime >= dtCheckTime)
{
// gefunden, Abbruch
sFileName = file.Name;
break;
}
}
// Pfad mit Datei erzeugen
sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);
// Datei vorhanden?
if (!System.IO.File.Exists(sDestinationFilePath))
{
return(new HttpNotFoundResult(String.Format("Excel file could not created for BatchKey: {0}", _BatchKey)));
}
// Create data return stream
byte[] fileData = System.IO.File.ReadAllBytes(sDestinationFilePath);
string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);
// Datei entfernen
System.IO.File.Delete(sDestinationFilePath);
var cd = new System.Net.Mime.ContentDisposition()
{
// for example foo.bak
FileName = sFileName,
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
return(File(fileData, contentType));
}
private static void tmr_Tick(Object myObject, EventArgs myEventArgs)
{
StreamWriter sw;
sw = new StreamWriter("Log.txt", true);
mailBody += "Attempting to process files ";
// + Convert.ToString(count++) + " of " + Convert.ToString(retr-1) + "\r\n\r\n\r\n";
if (Retries == 1)
{
exitFlag = true;
}
Retries--;
// tmr.Enabled = false;
tmr.Interval = 60000 * TimerInterval;
string OutputDirectory, TransactReportImportPath, UnsettledReportImportPath;
DateTime dt = DateTime.Now.AddDays(-1);
string year = string.Format("{0:D2}", dt.Year).Substring(2);
string month = string.Format("{0:D2}", dt.Month);
string day = string.Format("{0:D2}", dt.Day);
emailTo = ini.IniReadValue("Email", "To Email List");
emailCC = ini.IniReadValue("Email", "CC Email List");
emailBCC = ini.IniReadValue("Email", "BCC Email List");
TransactReportImportPath = ini.IniReadValue("Directories", "TransactReportImportPath");
UnsettledReportImportPath = ini.IniReadValue("Directories", "UnsettledReportImportPath");
OutputDirectory = ini.IniReadValue("Directories", "OutputDirectory");
/****************************************************/
sw.WriteLine("BEGIN\n");
try
{
/*both morning and noon*/
SqlConnectionStringBuilder sqConTransactB = new SqlConnectionStringBuilder();
sqConTransactB.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
sqConTransactB.IntegratedSecurity = true;
sqConTransactB.InitialCatalog = "ZacReporting";
sqConTransactB.ConnectTimeout = 0;
SqlConnection transactConn = new SqlConnection(sqConTransactB.ConnectionString);
transactConn.Open();
SqlCommand transactEmpty = new SqlCommand();
transactEmpty.Connection = transactConn;
transactEmpty.CommandTimeout = 0;
transactEmpty.CommandText = "Delete from [abc096].[IMP_TRANSACT_D]";
transactEmpty.ExecuteNonQuery();
transactConn.Close();
}
catch
{
}
try
{
if (morning == true)
{
//System.IO.File.Copy(TransactReportImportPath + "report_" + "20" + year + month + day + ".csv", OutputDirectory + "transactreport.csv", true);
System.IO.File.Copy(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report_" + "20" + year + month + day + ".csv", OutputDirectory + "transactreport.csv", true);
mailBody += "report_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n";
sw.WriteLine("report_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n");
}
else/*noon*/
{
//System.IO.File.Copy(TransactReportImportPath + "report2_" + "20" + year + month + day + ".csv", OutputDirectory + "transactreport.csv", true);
System.IO.File.Copy(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report2_" + "20" + year + month + day + ".csv", OutputDirectory + "transactreport.csv", true);
mailBody += "report2_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n";
sw.WriteLine("report2_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n");
}
}
catch
{
if (morning == true)
{
mailBody += "report_" + "20" + year + month + day + ".csv not found. \r\n";
Console.WriteLine("report_" + "20" + year + month + day + ".csv not found. \r\n");
sw.WriteLine("report_" + "20" + year + month + day + ".csv not found. \r\n");
}
else
{
mailBody += "report2_" + "20" + year + month + day + ".csv not found. \r\n";
Console.WriteLine("report2_" + "20" + year + month + day + ".csv not found. \r\n");
sw.WriteLine("report2_" + "20" + year + month + day + ".csv not found. \r\n");
}
try { sw.Close(); }
catch { }
return;
}
//try move up 21012015
//{
// /*both morning and noon*/
// SqlConnectionStringBuilder sqConTransactB = new SqlConnectionStringBuilder();
// sqConTransactB.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
// sqConTransactB.IntegratedSecurity = true;
// sqConTransactB.InitialCatalog = "ZacReporting";
// sqConTransactB.ConnectTimeout = 0;
// SqlConnection transactConn = new SqlConnection(sqConTransactB.ConnectionString);
// transactConn.Open();
// SqlCommand transactEmpty = new SqlCommand();
// transactEmpty.Connection = transactConn;
// transactEmpty.CommandTimeout = 0;
// transactEmpty.CommandText = "Delete from [abc096].[IMP_TRANSACT_D]";
// transactEmpty.ExecuteNonQuery();
// transactConn.Close();
//}
//catch
//{
//}
try
{ /*both morning and noon*/
string pkgLocation;
Package pkg;
DTSExecResult pkgResults;
Microsoft.SqlServer.Dts.Runtime.Application app;
MyEventListener eventListener = new MyEventListener();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @".\Package.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
mailBody += "transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n";
sw.WriteLine("transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n");
}
catch
{
mailBody += "transactreport.csv could not be imported. \r\n";
Console.WriteLine("transactreport.csv could not be imported. \r\n");
sw.WriteLine("transactreport.csv could not be imported. \r\n");
try { sw.Close(); }
catch { }
return;
}
try
{
/*both morning and noon*/
SqlConnectionStringBuilder sqConTransactB = new SqlConnectionStringBuilder();
sqConTransactB.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
sqConTransactB.IntegratedSecurity = true;
sqConTransactB.InitialCatalog = "ZacReporting";
sqConTransactB.ConnectTimeout = 0;
SqlConnection transactConn = new SqlConnection(sqConTransactB.ConnectionString);
transactConn.Open();
SqlCommand transactUpd = new SqlCommand();
transactUpd.Connection = transactConn;
transactUpd.CommandTimeout = 0;
transactUpd.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] " +
"set amount =0 where msgid='0200' " +
"and REVERSED='F' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid='0400' and amount=b.amount and " +
"mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='A') ";
transactUpd.ExecuteNonQuery();
SqlCommand transactDel = new SqlCommand();
transactDel.Connection = transactConn;
transactDel.CommandTimeout = 0;
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0400' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid<>'0400' " +
"and mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='F') ";
transactDel.ExecuteNonQuery();
mailBody += "Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n";
Console.WriteLine("Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n");
sw.WriteLine("Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n");
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_ALPHA' where [DESTCOMID]='NET_CLBICALPHA'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_EBNK' where [DESTCOMID]='NET_CLBICEBNK'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update BIC ports.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0100' and proccode ='080000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete inquiries.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0200' and proccode ='890000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete DCC inquiries.\r\n");
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='000001' " +
"where destcomid='NET_NTBNLTY' and msgid='0100' and proccode ='000000'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='020001' " +
"where destcomid='NET_NTBNLTY' and msgid='0100' and proccode ='020000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update Go4More.\r\n");
SqlCommand transactDelnul = new SqlCommand();
transactDelnul.Connection = transactConn;
transactDelnul.CommandTimeout = 0;
transactDelnul.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where mask is null or mask='' ";
transactDelnul.ExecuteNonQuery();
Console.WriteLine("delete null pan \r\n");
SqlCommand transactproduct = new SqlCommand();
transactproduct.Connection = transactConn;
transactproduct.CommandTimeout = 0;
transactproduct.CommandText = "update [abc096].[imp_TRansact_D] " +
" set productid= " +
" case " +
" when " +
" exists " +
" (select * from abc096.products b " +
" where " +
" (substring(mask,1,6)=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
" substring(mask,1,6)=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
" substring(mask,1,6) between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
" cast((substring(b.binu,1,6)) as dec(22,0)) ) )" +
" then " +
"(select min(b.id) from abc096.products b" +
" where " +
" productid=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
" productid=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
" productid between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
" cast((substring(b.binu,1,6)) as dec(22,0)) )" +
" when " +
" exists " +
" (select * from abc096.products b" +
" where" +
" (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
" substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
" substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
" cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
" then " +
" (select min(b.id) from abc096.products b" +
" where " +
" (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
" substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
" substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
" cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
" when " +
" exists " +
" (select * from abc096.products b" +
" where" +
" (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
" substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
" substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
" cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
" then " +
" (select min(b.id) from abc096.products b" +
" where" +
" (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
" substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
" substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
" cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
" when" +
" exists" +
" (select * from abc096.products b" +
" where" +
" (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
" substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
" substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
" cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
" then" +
" (select min(b.id) from abc096.products b" +
" where" +
" (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
" substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
" substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
" cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
" when" +
" exists" +
" (select * from abc096.products b" +
" where" +
" (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
" substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
" substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
" cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
" then" +
" (select min(b.id) from abc096.products b" +
" where" +
" (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
" substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
" substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
" cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
" when" +
" exists" +
" (select * from abc096.products b" +
" where" +
" (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
" substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
" substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
" cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
" then " +
" (select min(b.id) from abc096.products b" +
" where" +
" (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
" substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
" substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
" cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
" end";
transactproduct.ExecuteNonQuery();
transactConn.Close();
mailBody += "Fixed product in IMP_TRANSACT_D. \r\n";
Console.WriteLine("Fixed product in IMP_TRANSACT_D. \r\n");
sw.WriteLine("Fixed product in IMP_TRANSACT_D. \r\n");
}
catch
{
}
try
{
if (morning == true)
{
System.IO.File.Move(OutputDirectory + "transactreport.csv", OutputDirectory + "Done\\" + "report_" + "20" + year + month + day + ".csv");
// System.IO.File.Move(TransactReportImportPath + "report_" + "20" + year + month + day + ".csv", TransactReportImportPath + "Processed\\" + "report_" + "20" + year + month + day + ".csv");
System.IO.File.Move(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report_" + "20" + year + month + day + ".csv", @"\\\\192.168.241.252\\TransactReports\\Daily\\" + "Processed\\" + "report_" + "20" + year + month + day + ".csv");
mailBody += "report_" + "20" + year + month + day + ".csv DONE" + "\r\n";
}
else /*noon*/
{
System.IO.File.Move(OutputDirectory + "transactreport.csv", OutputDirectory + "Done\\" + "report2_" + "20" + year + month + day + ".csv");
// System.IO.File.Move(TransactReportImportPath + "report2_" + "20" + year + month + day + ".csv", TransactReportImportPath + "Processed\\" + "report2_" + "20" + year + month + day + ".csv");
System.IO.File.Move(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report2_" + "20" + year + month + day + ".csv", @"\\\\192.168.241.252\\TransactReports\\Daily\\" + "Processed\\" + "report2_" + "20" + year + month + day + ".csv");
mailBody += "report2_" + "20" + year + month + day + ".csv DONE" + "\r\n";
sw.WriteLine("report2_" + "20" + year + month + day + ".csv DONE" + "\r\n");
exitFlag = true;
try { sw.Close(); }
catch { }
return;
}
}
catch
{ try { sw.Close(); }
catch { }
return; }
try
{
if (morning == true)
{
DateTime dt1 = DateTime.Now;
string year1 = string.Format("{0:D2}", dt1.Year).Substring(2);
string month1 = string.Format("{0:D2}", dt1.Month);
string day1 = string.Format("{0:D2}", dt1.Day);
//System.IO.File.Copy(UnsettledReportImportPath + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", OutputDirectory + "unsettled.csv", true);
System.IO.File.Copy(@"\\\\192.168.241.252\\TransactReports\\Unsettled\\" + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", OutputDirectory + "unsettled.csv", true);
mailBody += "\r\nunsettled_" + "20" + year1 + month1 + day1 + ".csv FOUND.\r\n";
sw.WriteLine("\r\nunsettled_" + "20" + year1 + month1 + day1 + ".csv FOUND.\r\n");
string pkgLocation;
Package pkg;
DTSExecResult pkgResults;
Microsoft.SqlServer.Dts.Runtime.Application app;
MyEventListener eventListener = new MyEventListener();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @".\PackageUnsettled.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
SqlConnectionStringBuilder sqConTransactB = new SqlConnectionStringBuilder();
sqConTransactB.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
sqConTransactB.IntegratedSecurity = true;
sqConTransactB.InitialCatalog = "ZacReporting";
sqConTransactB.ConnectTimeout = 0;
SqlConnection transactConn = new SqlConnection(sqConTransactB.ConnectionString);
transactConn.Open();
SqlCommand transactDel = new SqlCommand();
transactDel.Connection = transactConn;
transactDel.CommandTimeout = 0;
transactDel.CommandText = "delete from [ZacReporting].[dbo].[V_TransactUnsettledBatches] where tid<'14000000' or tid>='90000000'";//donna 14102013 hellaspay unsettled should be processed
transactDel.ExecuteNonQuery();
SqlCommand transactDel1 = new SqlCommand();
transactDel1.Connection = transactConn;
transactDel1.CommandTimeout = 0;
transactDel1.CommandText = "delete from [ZacReporting].[dbo].[V_TransactUnsettledBatches] where Bank='NET_ALPMOR'";
transactDel1.ExecuteNonQuery();
transactConn.Close();
mailBody += "unsettled_" + "20" + year1 + month1 + day1 + ".csv IMPORTED." + pkgResults.ToString() + "\r\n";
System.IO.File.Move(OutputDirectory + "unsettled.csv", OutputDirectory + "unsettled\\" + "unsettled_" + "20" + year1 + month1 + day1 + ".csv");
// System.IO.File.Move(UnsettledReportImportPath + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", UnsettledReportImportPath + "Processed\\" + "report_" + "20" + year + month + day + ".csv");
System.IO.File.Move(@"\\\\192.168.241.252\\TransactReports\\Unsettled\\" + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", @"\\\\192.168.241.252\\TransactReports\\Unsettled\\" + "Processed\\" + "report_" + "20" + year + month + day + ".csv");
mailBody += "unsettled_" + "20" + year1 + month1 + day1 + ".csv DONE" + "\r\n";
sw.WriteLine("unsettled_" + "20" + year1 + month1 + day1 + ".csv DONE" + "\r\n");
SqlConnectionStringBuilder sqConTransactC = new SqlConnectionStringBuilder();
sqConTransactC.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
sqConTransactC.IntegratedSecurity = true;
sqConTransactC.InitialCatalog = "ZacReporting";
sqConTransactC.ConnectTimeout = 0;
SqlConnection transactConn1 = new SqlConnection(sqConTransactC.ConnectionString);
transactConn1.Open();
SqlCommand transactEmptyBanks = new SqlCommand();
transactEmptyBanks.Connection = transactConn1;
transactEmptyBanks.CommandTimeout = 0;
transactEmptyBanks.CommandText = "Delete from [abc096].[DEACTIVATED_BANKS]";
transactEmptyBanks.ExecuteNonQuery();
transactConn1.Close();
try
{
//System.IO.File.Copy(UnsettledReportImportPath + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", OutputDirectory + "unsettled.csv", true);
System.IO.File.Copy(@"\\\\192.168.241.252\\TransactReports\\DeactivatedBanks\\" + "deact_banks" + year1 + month1 + day1 + ".csv", OutputDirectory + "deact_banks.csv", true);
mailBody += "\r\ndeact_banks" + year1 + month1 + day1 + ".csv FOUND.\r\n";
sw.WriteLine("\r\ndeact_banks" + year1 + month1 + day1 + ".csv FOUND.\r\n");
string pkgLocation1;
Package pkg1;
DTSExecResult pkgResults1;
Microsoft.SqlServer.Dts.Runtime.Application app1;
MyEventListener eventListener1 = new MyEventListener();
app1 = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation1 = @".\Package_deactbanks.dtsx";
pkg1 = app1.LoadPackage(pkgLocation1, eventListener1);
pkgResults1 = pkg1.Execute(null, null, eventListener1, null, null);
mailBody += "deact_banks" + year1 + month1 + day1 + ".csv IMPORTED." + pkgResults1.ToString() + "\r\n";
System.IO.File.Move(OutputDirectory + "deact_banks.csv", OutputDirectory + "DeactivatedBanks\\" + "deact_banks" + year1 + month1 + day1 + ".csv");
// System.IO.File.Move(UnsettledReportImportPath + "unsettled_" + "20" + year1 + month1 + day1 + ".csv", UnsettledReportImportPath + "Processed\\" + "report_" + "20" + year + month + day + ".csv");
System.IO.File.Move(@"\\\\192.168.241.252\\TransactReports\\DeactivatedBanks\\" + "deact_banks" + year1 + month1 + day1 + ".csv", @"\\\\192.168.241.252\\TransactReports\\DeactivatedBanks\\" + "Processed\\" + "deact_banks" + year1 + month1 + day1 + ".csv");
mailBody += "deact_banks" + year1 + month1 + day1 + ".csv DONE" + "\r\n";
sw.WriteLine("deact_banks" + year1 + month1 + day1 + ".csv DONE" + "\r\n");
}
catch
{ }
SqlConnectionStringBuilder sqConTransactD = new SqlConnectionStringBuilder();
sqConTransactD.DataSource = @"10.7.46.2\sql2k5_dev"; //Production
sqConTransactD.IntegratedSecurity = true;
sqConTransactD.InitialCatalog = "ZacReporting";
sqConTransactD.ConnectTimeout = 0;
SqlConnection transactConn2 = new SqlConnection(sqConTransactD.ConnectionString);
transactConn2.Open();
SqlCommand transactEmptyBinRuling = new SqlCommand();
transactEmptyBinRuling.Connection = transactConn2;
transactEmptyBinRuling.CommandTimeout = 0;
transactEmptyBinRuling.CommandText = "Delete from [abc096].[FAILED_BINRULING]";
transactEmptyBinRuling.ExecuteNonQuery();
transactConn2.Close();
try
{
System.IO.File.Copy(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report4_" + "20" + year + month + day + ".csv", OutputDirectory + "failedbinruling.csv", true);
mailBody += "report4_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n";
sw.WriteLine("report4_" + "20" + year + month + day + ".csv FOUND.\r\n\r\n");
string pkgLocation2;
Package pkg2;
DTSExecResult pkgResults2;
Microsoft.SqlServer.Dts.Runtime.Application app2;
MyEventListener eventListener2 = new MyEventListener();
app2 = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation2 = @".\Package_failedbinruling.dtsx";
pkg2 = app2.LoadPackage(pkgLocation2, eventListener2);
pkgResults2 = pkg2.Execute(null, null, eventListener2, null, null);
mailBody += "report4_" + "20" + year + month + day + ".csv IMPORTED." + pkgResults2.ToString() + "\r\n";
System.IO.File.Move(OutputDirectory + "failedbinruling.csv", OutputDirectory + "Done\\" + "report4_" + "20" + year + month + day + ".csv");
System.IO.File.Move(@"\\\\192.168.241.252\\TransactReports\\Daily\\" + "report4_" + "20" + year + month + day + ".csv", @"\\\\192.168.241.252\\TransactReports\\Daily\\" + "Processed\\" + "report4_" + "20" + year + month + day + ".csv");
mailBody += "report4_" + "20" + year + month + day + ".csv DONE" + "\r\n";
sw.WriteLine("report4_" + "20" + year + month + day + ".csv DONE" + "\r\n");
}
catch {}
exitFlag = true;
try { sw.Close(); }
catch { }
return;
}
else /*noon*/
{
}
}
catch
{
}
try { sw.Close(); }
catch { }
}
public ActionResult DownloadBatchOverviewReport(String _TemplateKey, String _1stBatchKey, String _2ndBatchKey, String _BatchTypeKey, String _MaterialKey, String StartTime, String EndTime, bool CreateLogFile = false)
{
const string FILENAME_PREFIX = "BatchOverview_";
//const string FILENAME_PREFIX = "BatchOverview";
const string FILENAME_TYPE = "xlsx";
const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchOverview_Template.xlsx";
const string SSIS_PACKAGE = "ExcelReport_BatchOverview.dtsx";
const string FOLDER_BASE_SOURCE = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
const string FOLDER_BIN = "bin";
const string FOLDER_TEMPLATE = "template";
const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
//const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output\\";
const string LOG_FILE_NAME = "DownloadBatchOverview_Log.txt";
const int WAITTIME_IN_MS = 1000;
const int MAXWAITTIME_IN_MS = 30000;
String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory
Logger logger = null;
long nTemplateKey = 0;
long n1stBatchKey = 0;
long n2ndBatchKey = 0;
long nBatchTypeKey = 0;
long nMaterialKey = 0;
DateTime dtStartTime = DateTime.MaxValue;
DateTime dtEndTime = DateTime.MinValue;
string sSourcePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
string sDestinationPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
string sDestinationFilePath = String.Empty;
string sFileName = String.Empty;
DateTime dtCheckTime = DateTime.Now;
// Log generieren
if (CreateLogFile)
{
logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
}
// Parameter ermitteln
// TemplateKey
try
{
nTemplateKey = Convert.ToInt64(_TemplateKey);
}
catch { }
// 1stBatchKey
try
{
n1stBatchKey = Convert.ToInt64(_1stBatchKey);
}
catch { }
// 2ndBatchKey
try
{
n2ndBatchKey = Convert.ToInt64(_2ndBatchKey);
}
catch { }
// BatchTypeKey
try
{
nBatchTypeKey = Convert.ToInt64(_BatchTypeKey);
}
catch { }
// MaterialKey
try
{
nMaterialKey = Convert.ToInt64(_MaterialKey);
}
catch { }
// StartTime
try
{
dtStartTime = Convert.ToDateTime(StartTime);
}
catch { }
// EndTime
try
{
dtEndTime = Convert.ToDateTime(EndTime);
}
catch { }
// Nach Datei suchen
sFileName = FILENAME_PREFIX + DateTime.Now.ToString().Replace(":", "").Replace('/', '.').Replace('-', '.');
//sFileName = FILENAME_PREFIX;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = null;
MyEventListener eventListener = new MyEventListener();
//Load the SSIS Package which will be executed
app.PackagePassword = "******";
String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);
// Execute
try
{
// Load package
package = app.LoadPackage(sPackageFilePath, eventListener);
// Set values
Variables vars = package.Variables;
vars["User::InputTemplateKey"].Value = nTemplateKey;
vars["User::Input1stBatchKey"].Value = n1stBatchKey;
vars["User::Input2ndBatchKey"].Value = n2ndBatchKey;
vars["User::InputBatchTypeKey"].Value = nBatchTypeKey;
vars["User::InputMaterialKey"].Value = nMaterialKey;
vars["User::InputStartTime"].Value = dtStartTime;
vars["User::InputEndTime"].Value = dtEndTime;
vars["User::InputDestinationFile"].Value = sFileName + "." + FILENAME_TYPE;
vars["User::InputDestinationPath"].Value = sDestinationPath;
vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
vars["User::InputCreateLogFile"].Value = CreateLogFile;
vars["User::InputLogFilePath"].Value = (logger != null) ? logger.FilePath : String.Empty;
// Log erzeugen
if (logger != null)
{
logger.Log("InputTemplateKey", vars["User::InputTemplateKey"].Value.ToString());
logger.Log("Input1stBatchKey", vars["User::Input1stBatchKey"].Value.ToString());
logger.Log("Input2ndBatchKey", vars["User::Input2ndBatchKey"].Value.ToString());
logger.Log("InputBatchTypeKey", vars["User::InputBatchTypeKey"].Value.ToString());
logger.Log("InputMaterialKey", vars["User::InputMaterialKey"].Value.ToString());
logger.Log("InputStartTime", vars["User::InputStartTime"].Value.ToString());
logger.Log("InputEndTime", vars["User::InputEndTime"].Value.ToString());
logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
}
vars["InputToOverwrite"].Value = true;
DTSExecResult results = package.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
if (logger != null)
{
logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
}
return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
}
// zyklisch prüfen ob Datei vorhanden ist, maximal MAXWAITTIME_IN_MS
for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
{
if (package.Errors.Count > 0)
{
foreach (var item in package.Errors)
{
if (logger != null)
{
logger.Log("Error", item.Description);
}
}
break;
}
// erstmal eine Sekunde zeit geben, zyklisch prüfen
System.Threading.Thread.Sleep(WAITTIME_IN_MS);
// alle Dateien des Pfades holen die dem Suchmuster entsprechen
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(sDestinationPath));
var filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));
var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();
// nur welche die eben erstellt wurde
if (file == null)
{
continue;
}
// Falls eben erstellt...
if (file.LastWriteTime >= dtCheckTime)
{
// gefunden, Abbruch
sFileName = file.Name;
break;
}
}
// Pfad mit Datei erzeugen
sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);
// Datei vorhanden?
if (!System.IO.File.Exists(sDestinationFilePath))
{
return(new HttpNotFoundResult("Excel file could not created!"));
}
// Create data return stream
byte[] fileData = System.IO.File.ReadAllBytes(sDestinationFilePath);
string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);
// Datei entfernen
System.IO.File.Delete(sDestinationFilePath);
var cd = new System.Net.Mime.ContentDisposition()
{
// for example foo.bak
FileName = sFileName,
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
return(File(fileData, contentType));
}
static void Main(string[] args)
{
string date = "";//Fix Aegean report 20170206
bool selectedday = false;
if (args.Length == 1) //Fix Aegean report 20170206
{
date = args[0]; //ex 2017-02-06
selectedday = true;
}
try
{
System.IO.File.Move("D:\\Reporting\\Import\\report.csv", "D:\\Reporting\\transactreport.csv");
}
catch
{
Console.WriteLine("D:\\Reporting\\Import\\report.csv does not exist.\r\n");
}
SqlConnectionStringBuilder sqConTransactB = new SqlConnectionStringBuilder();
sqConTransactB.DataSource = @"grat1-dev-ap2t"; //Production
sqConTransactB.IntegratedSecurity = true;
sqConTransactB.InitialCatalog = "ZacReporting";
sqConTransactB.ConnectTimeout = 0;
SqlConnection transactConn = new SqlConnection(sqConTransactB.ConnectionString);
transactConn.Open();
SqlCommand transactEmpty = new SqlCommand();
transactEmpty.Connection = transactConn;
transactEmpty.CommandTimeout = 0;
transactEmpty.CommandText = "Delete from [abc096].[IMP_TRANSACT_D]";
transactEmpty.ExecuteNonQuery();
transactConn.Close();
string pkgLocation;
Package pkg;
DTSExecResult pkgResults;
Microsoft.SqlServer.Dts.Runtime.Application app;
MyEventListener eventListener = new MyEventListener();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @"D:\Reporting\DTSX Packages\ImportDailyDTSx -manual\Integration Services Project1\Package.dtsx";
//pkgLocation = @".\ImportDaily.dtsx";
//pkgLocation = @".\Package.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
SqlConnectionStringBuilder sqConTransactB1 = new SqlConnectionStringBuilder();
sqConTransactB1.DataSource = @"grat1-dev-ap2t"; //Production
sqConTransactB1.IntegratedSecurity = true;
sqConTransactB1.InitialCatalog = "ZacReporting";
sqConTransactB1.ConnectTimeout = 0;
SqlConnection transactConn1 = new SqlConnection(sqConTransactB1.ConnectionString);
transactConn1.Open();
SqlCommand transactUpd = new SqlCommand();
transactUpd.Connection = transactConn1;
transactUpd.CommandTimeout = 0;
transactUpd.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] " +
"set amount =0 where msgid='0200' " +
"and REVERSED='F' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid='0400' and amount=b.amount and " +
"mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='A') ";
transactUpd.ExecuteNonQuery();
SqlCommand transactDel = new SqlCommand();
transactDel.Connection = transactConn1;
transactDel.CommandTimeout = 0;
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0400' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid<>'0400' " +
"and mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='F') ";
transactDel.ExecuteNonQuery();
Console.WriteLine("Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n");
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_ALPHA' where [DESTCOMID]='NET_CLBICALPHA'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_EBNK' where [DESTCOMID]='NET_CLBICEBNK'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update BIC ports.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0100' and proccode ='080000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete inquiries.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0200' and proccode ='890000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete DCC inquiries.\r\n");
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='000001' " +
"where (destcomid='NET_NTBNLTY' or destcomid='NET_PBGLTY') and msgid='0100' and proccode ='000000'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='020001' " +
"where (destcomid='NET_NTBNLTY' or destcomid='NET_PBGLTY') and msgid='0100' and proccode ='020000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update Go4More and Yellow.\r\n");
SqlCommand transactDelnul = new SqlCommand();
transactDelnul.Connection = transactConn1;
transactDelnul.CommandTimeout = 0;
transactDelnul.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where mask is null or mask='' ";
transactDelnul.ExecuteNonQuery();
transactDelnul.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set mask='000000' where productid is null ";
transactDelnul.ExecuteNonQuery();
Console.WriteLine("delete null pan \r\n");
if (selectedday == true)
{
SqlCommand UpdateUPD1 = new SqlCommand(); //Fix Aegean report 20170206
UpdateUPD1.Connection = transactConn1;
UpdateUPD1.CommandTimeout = 0;
UpdateUPD1.CommandText = "delete FROM [ZacReporting].[abc096].[IMP_TRANSACT_D]where mid='000000120004600' and bdtstamp>'" + date + " 23:59:59.000'";
UpdateUPD1.ExecuteNonQuery();
Console.WriteLine("remove AEGEAN current day \r\n");
}
// ln 20170922 ---------- start ---------
SqlCommand UpdateUPD3 = new SqlCommand();
UpdateUPD3.Connection = transactConn1;
UpdateUPD3.CommandTimeout = 0;
UpdateUPD3.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set productid = 0 ";
UpdateUPD3.ExecuteNonQuery();
Console.WriteLine("Update productid = 0 \r\n");
// ln 20170922 ---------- end ---------
SqlCommand transactproduct = new SqlCommand();
transactproduct.Connection = transactConn1;
transactproduct.CommandTimeout = 0;
// ln 20170922 ---------- start ---------
transactproduct.CommandText = "update zacreporting.[abc096].[imp_TRansact_D] " +
"set productid = ( select top 1 b.id " +
"from zacreporting.abc096.Products b " +
"where substring(mask,1,6) = b.bin ); ";
// ln 20170922 ---------- end ---------
// LN 20170922 Commented ---------- start ---------
//transactproduct.CommandText = "update [abc096].[imp_TRansact_D] " +
// " set productid= " +
// " case " +
// " when " +
// " exists " +
// " (select * from abc096.products b " +
// " where " +
// " (substring(mask,1,6)=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
// " substring(mask,1,6)=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
// " substring(mask,1,6) between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
// " cast((substring(b.binu,1,6)) as dec(22,0)) ) )" +
// " then " +
// "(select min(b.id) from abc096.products b" +
// " where " +
// " productid=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
// " productid=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
// " productid between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
// " cast((substring(b.binu,1,6)) as dec(22,0)) )" +
// " when " +
// " exists " +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
// " substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
// " substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where " +
// " (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
// " substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
// " substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
// " when " +
// " exists " +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
// " substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
// " substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
// " substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
// " substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
// " substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
// " substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
// " then" +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
// " substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
// " substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
// " substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
// " substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
// " then" +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
// " substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
// " substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
// " substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
// " substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
// " substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
// " substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
// " end";
transactproduct.ExecuteNonQuery();
// ln 20170922 ---------- start ---------
SqlCommand UpdZeroPrdctID = new SqlCommand();
UpdZeroPrdctID.Connection = transactConn1;
UpdZeroPrdctID.CommandTimeout = 0;
UpdZeroPrdctID.CommandText = "update zacreporting.[abc096].[imp_TRansact_D] " +
"set productid = " +
"case " +
"when substring(mask,1,2) >= '40' and substring(mask,1,2) <= '49' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c " +
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) >= '60' and substring(mask,1,2) <= '69' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c " +
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '23' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '24' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '25' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '26' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '30' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
" group by b.id,b.bin) " +
"when substring(mask,1,2) = '34' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '35' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '36' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '37' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '38' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '50' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '51' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '52' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '53' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '54' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '55' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '56' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '57' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '58' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"end " +
"where productid = '0' ";
UpdZeroPrdctID.ExecuteNonQuery();
Console.WriteLine("Update productid for '0' \r\n");
// ln 20170922 ---------- end ---------
transactConn1.Close();
Console.WriteLine("Fixed product in IMP_TRANSACT_D. \r\n");
System.IO.File.Move("D:\\Reporting\\transactreport.csv", "D:\\Reporting\\Import\\report.csv");
}
private static void Import_transactreport(bool onboard, bool casino)
{
SqlConnectionStringBuilder sqCon = new SqlConnectionStringBuilder();
sqCon.DataSource = @"grat1-dev-ap2t"; //Production
sqCon.IntegratedSecurity = true;
sqCon.InitialCatalog = "ZacReporting";
sqCon.ConnectTimeout = 0;
SqlConnection Conn = new SqlConnection(sqCon.ConnectionString);
try
{ /*both morning and noon*/
string pkgLocation;
Package pkg;
DTSExecResult pkgResults;
Microsoft.SqlServer.Dts.Runtime.Application app;
MyEventListener eventListener = new MyEventListener();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @"D:\Reporting\\DTSX Packages\\ImportDailyDTSx\\Integration Services Project1\\bin\\Package.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
mailBody += "transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n\r\n";
// sw.WriteLine("transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n");
if (onboard == true)
{
try //20170609 AEGEAN
{
Conn.Open();
SqlCommand transactUpd = new SqlCommand();
transactUpd.Connection = Conn;
transactUpd.CommandTimeout = 0;
transactUpd.CommandText = "delete FROM [ZacReporting].[abc096].[IMP_TRANSACT_D] where mid='000000120004600'";
transactUpd.ExecuteNonQuery();
Conn.Close();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @"D:\\Reporting\\DTSX Packages\\ImportOnboard\\Integration Services Project1\\bin\\Package.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
mailBody += "onboard transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n\r\n";
}
catch
{
mailBody += "onboard_transactreport.csv PROBLEM!!!\r\n";
}
}
if (casino == true)
{
try //20170831 casino
{
Conn.Open();
SqlCommand transactUpd = new SqlCommand();
transactUpd.Connection = Conn;
transactUpd.CommandTimeout = 0;
transactUpd.CommandText = "delete FROM [ZacReporting].[abc096].[IMP_TRANSACT_D] where mid='000000120005300'";
transactUpd.ExecuteNonQuery();
Conn.Close();
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkgLocation = @"D:\\Reporting\\DTSX Packages\\ImportCasino\\Integration Services Project1\\bin\\Package.dtsx";
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
mailBody += "casino transactreport.csv IMPORTED." + pkgResults.ToString() + "\r\n\r\n";
}
catch
{
mailBody += "casino_transactreport.csv PROBLEM!!!\r\n";
}
}
try
{
/*both morning and noon*/
Conn.Open();
SqlCommand transactUpd = new SqlCommand();
transactUpd.Connection = Conn;
transactUpd.CommandTimeout = 0;
transactUpd.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] " +
"set amount =0 where msgid='0200' " +
"and REVERSED='F' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid='0400' and amount=b.amount and " +
"mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='A') ";
transactUpd.ExecuteNonQuery();
SqlCommand transactDel = new SqlCommand();
transactDel.Connection = Conn;
transactDel.CommandTimeout = 0;
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0400' " +
"and exists " +
"(select * from [ZacReporting].[abc096].[IMP_TRANSACT_D] B where b.msgid<>'0400' " +
"and mask=b.mask and mid=b.mid and tid=b.tid and DESTCOMID=B.DESTCOMID AND B.REVERSED='F') ";
transactDel.ExecuteNonQuery();
mailBody += "Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n";
Console.WriteLine("Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n");
// sw.WriteLine("Updated reversed and deleted reversals from IMP_TRANSACT_D. \r\n");
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_ALPHA' where [DESTCOMID]='NET_CLBICALPHA' or [DESTCOMID]='NET_BICALPHA'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "UPDATE [ZacReporting].[abc096].[IMP_TRANSACT_D] SET [DESTCOMID]='NET_EBNK' where [DESTCOMID]='NET_CLBICEBNK'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update BIC ports.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0100' and proccode ='080000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete inquiries.\r\n");
transactDel.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where msgid='0200' and proccode ='890000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Delete DCC inquiries.\r\n");
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='000001' " +
"where (destcomid='NET_NTBNLTY' or destcomid='NET_PBGLTY') and msgid='0100' and proccode ='000000'";
transactDel.ExecuteNonQuery();
transactDel.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set msgid='0200',proccode ='020001' " +
"where (destcomid='NET_NTBNLTY' or destcomid='NET_PBGLTY') and msgid='0100' and proccode ='020000'";
transactDel.ExecuteNonQuery();
Console.WriteLine("Update Go4More and Yellow.\r\n");
SqlCommand transactDelnul = new SqlCommand();
transactDelnul.Connection = Conn;
transactDelnul.CommandTimeout = 0;
transactDelnul.CommandText = "delete from [ZacReporting].[abc096].[IMP_TRANSACT_D] where mask is null or mask='' ";
transactDelnul.ExecuteNonQuery();
transactDelnul.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set mask='000000' where productid is null ";
transactDelnul.ExecuteNonQuery();
Console.WriteLine("delete null pan \r\n");
// ln 20170922 ---------- start ---------
SqlCommand UpdatePrdctID = new SqlCommand();
UpdatePrdctID.Connection = Conn;
UpdatePrdctID.CommandTimeout = 0;
UpdatePrdctID.CommandText = "update [ZacReporting].[abc096].[IMP_TRANSACT_D] set productid = 0 ";
UpdatePrdctID.ExecuteNonQuery();
Console.WriteLine("Update productid = 0 \r\n");
// ln 20170922 ---------- end ---------
SqlCommand transactproduct = new SqlCommand();
transactproduct.Connection = Conn;
transactproduct.CommandTimeout = 0;
// ln 20170922 ---------- start ---------
transactproduct.CommandText = "update zacreporting.[abc096].[imp_TRansact_D] " +
"set productid = ( select top 1 b.id " +
"from zacreporting.abc096.Products b " +
"where substring(mask,1,6) = b.bin ); ";
// ln 20170922 ---------- end ---------
// LN 20170922 Commented ---------- start ---------
//transactproduct.CommandText = "update [abc096].[imp_TRansact_D] " +
// " set productid= " +
// " case " +
// " when " +
// " exists " +
// " (select * from abc096.products b " +
// " where " +
// " (substring(mask,1,6)=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
// " substring(mask,1,6)=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
// " substring(mask,1,6) between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
// " cast((substring(b.binu,1,6)) as dec(22,0)) ) )" +
// " then " +
// "(select min(b.id) from abc096.products b" +
// " where " +
// " substring(mask,1,6)=cast((substring(b.bin,1,6 )) as dec(22,0)) or" +
// " substring(mask,1,6)=cast((substring(b.binu,1,6)) as dec(22,0)) or" +
// " substring(mask,1,6) between cast((substring(b.bin,1,6 )) as dec(22,0)) and" +
// " cast((substring(b.binu,1,6)) as dec(22,0)) )" +
// " when " +
// " exists " +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
// " substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
// " substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where " +
// " (substring(mask,1,5)=cast((substring(b.bin,1,5 )) as dec(22,0)) or" +
// " substring(mask,1,5)=cast((substring(b.binu,1,5)) as dec(22,0)) or" +
// " substring(mask,1,5) between cast((substring(b.bin,1,5)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,5)) as dec(22,0))) and len(b.bin)=5)" +
// " when " +
// " exists " +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
// " substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
// " substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,4)=cast((substring(b.bin,1,4 )) as dec(22,0)) or" +
// " substring(mask,1,4)=cast((substring(b.binu,1,4)) as dec(22,0)) or" +
// " substring(mask,1,4) between cast((substring(b.bin,1,4)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,4)) as dec(22,0))) and len(b.bin)=4)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
// " substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
// " substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
// " then" +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,3)=cast((substring(b.bin,1,3 )) as dec(22,0)) or" +
// " substring(mask,1,3)=cast((substring(b.binu,1,3)) as dec(22,0)) or" +
// " substring(mask,1,3) between cast((substring(b.bin,1,3)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,3)) as dec(22,0))) and len(b.bin)=3)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
// " substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
// " substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
// " then" +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,2)=cast((substring(b.bin,1,2 )) as dec(22,0)) or" +
// " substring(mask,1,2)=cast((substring(b.binu,1,2)) as dec(22,0)) or" +
// " substring(mask,1,2) between cast((substring(b.bin,1,2)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,2)) as dec(22,0))) and len(b.bin)=2)" +
// " when" +
// " exists" +
// " (select * from abc096.products b" +
// " where" +
// " (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
// " substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
// " substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
// " then " +
// " (select min(b.id) from abc096.products b" +
// " where" +
// " (substring(mask,1,1)=cast((substring(b.bin,1,1 )) as dec(22,0)) or" +
// " substring(mask,1,1)=cast((substring(b.binu,1,1)) as dec(22,0)) or" +
// " substring(mask,1,1) between cast((substring(b.bin,1,1)) as dec(22,0)) and" +
// " cast((substring(b.binu,1,1)) as dec(22,0))) and len(b.bin)=1)" +
// " end";
// LN 20170922 Commented ---------- end ---------
transactproduct.ExecuteNonQuery();
// ln 20170922 ---------- start ---------
SqlCommand UpdZeroPrdctID = new SqlCommand();
UpdZeroPrdctID.Connection = Conn;
UpdZeroPrdctID.CommandTimeout = 0;
UpdZeroPrdctID.CommandText = "update zacreporting.[abc096].[imp_TRansact_D] " +
"set productid = " +
"case " +
"when substring(mask,1,2) >= '40' and substring(mask,1,2) <= '49' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c " +
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) >= '60' and substring(mask,1,2) <= '69' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c " +
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '23' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '24' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '25' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '26' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '30' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
" group by b.id,b.bin) " +
"when substring(mask,1,2) = '34' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '35' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '36' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '37' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '38' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '50' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '51' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '52' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '53' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '54' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '55' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '56' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '57' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"when substring(mask,1,2) = '58' then (select b.id from zacreporting.[abc096].[imp_TRansact_D] c "+
"inner join zacreporting.dbo.Products b on substring(c.mask,1,2) = b.bin " +
"group by b.id,b.bin) " +
"end " +
"where productid = '0' ";
UpdZeroPrdctID.ExecuteNonQuery();
Console.WriteLine("Update productid for '0' \r\n");
// ln 20170922 ---------- end ---------
Conn.Close();
mailBody += "Fixed product in IMP_TRANSACT_D. \r\n";
Console.WriteLine("Fixed product in IMP_TRANSACT_D. \r\n");
// sw.WriteLine("Fixed product in IMP_TRANSACT_D. \r\n");
}
catch
{ mailBody += "transactreport.csv could not be imported. \r\n";
Console.WriteLine("transactreport.csv could not be imported. \r\n");
// sw.WriteLine("transactreport.csv could not be imported. \r\n");
// try { sw.Close(); }
// catch { }
}
}
catch
{ mailBody += "transactreport.csv could not be imported. \r\n";
Console.WriteLine("transactreport.csv could not be imported. \r\n");
//sw.WriteLine("transactreport.csv could not be imported. \r\n");
//try { sw.Close(); }
//catch { }
}
}
