public TableauCommandHelper(string view, string saveLocation,string filename,string filetype,string server, string username, string password)
{
server = new CryptLib.CryptLib().Decrypt3DES(server);
username = new CryptLib.CryptLib().Decrypt3DES(username);
password = new CryptLib.CryptLib().Decrypt3DES(password);
_commmand = EXPORT + " \"" + view + "\" " + PDF + " \"" + saveLocation + filename + filetype + "\" -s " + server + " -u " + username + " -p " + password;
}
static void Main(string[] args)
{
//var command = new TableauCommandHelper("");
try
{
var logpath = ConfigurationManager.AppSettings["LogPath"] + ConfigurationManager.AppSettings["LogName"];
var filepath = ConfigurationManager.AppSettings["FilePath"];
var avian = ConfigurationManager.AppSettings["AvianView"];
var seasonal = ConfigurationManager.AppSettings["SeasonalView"];
var country = ConfigurationManager.AppSettings["CountryView"];
var server = ConfigurationManager.AppSettings["TableauServer"];
var username = ConfigurationManager.AppSettings["Username"];
var password = new CryptLib.CryptLib().Decrypt3DES(ConfigurationManager.AppSettings["Password"]);
var fileType = ConfigurationManager.AppSettings["FileType"];
var waitingTime = Convert.ToInt32(ConfigurationManager.AppSettings["WaitingTime"]);
var tabserver = ConfigurationManager.AppSettings["TableauCommandPath"];
var lastWeek = LastWeekNumber().ToString();
var tabCommandExecutor = new TableauCommandExecutor(tabserver, logpath, avian, seasonal,
country, lastWeek, fileType, username, password, server, filepath, waitingTime);
//var filenames = new List<string>();
//filenames.Add(@"C:\Applics\ESRArchive\2015-31-Avian.pdf");
//filenames.Add(@"C:\Applics\ESRArchive\2015-31-Country.pdf");
//filenames.Add(@"C:\Applics\ESRArchive\2015-31-Seasonal.pdf");
//var target = @"C:\Applics\ESRArchive\x.pdf";
//tabCommandExecutor.PdfMerger(filenames.ToArray(), target);
tabCommandExecutor.ExecuteCommand();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadKey();
}
// Console.ReadKey();
}
public static bool BulkInsertLinelist(string FilePath,
string Extension, string isHDR,
string LinelistType, string user)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
using (OleDbConnection connExcel = new OleDbConnection(conStr))
{
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
string SheetName = string.Empty;
if (dtExcelSchema.Rows.Count == 1)
SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
else
{
var count = 0;
foreach (DataRow row in dtExcelSchema.Rows)
{
if (dtExcelSchema.Rows[count]["TABLE_NAME"].ToString().Contains("Linelist"))
{
SheetName = dtExcelSchema.Rows[count]["TABLE_NAME"].ToString();
}
count++;
}
}
var pathogen = string.Empty;
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N1).ToString())
pathogen = ((int)LineListType.H5N1).ToString();
else if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N6).ToString())
pathogen = ((int)LineListType.H5N6).ToString();
else
pathogen = ((int)LineListType.H7N9).ToString();
var now = DateTime.Now.ToString();
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT *,('" + now + "') as date_created ,('" + user + "') as created_by,('" + pathogen + "') as pathogen_id From [" + SheetName + "]"
, connExcel))
{
oda.Fill(dt);
}
connExcel.Close();
try
{
var constring = new CryptLib.CryptLib().Decrypt3DES(ConfigurationManager.ConnectionStrings["ESRSITUPD"].ConnectionString);
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
using (SqlTransaction transaction = con.BeginTransaction())
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepNulls, transaction))
{
// sqlBulkCopy.DestinationTableName = "[ESRSITUPD].[dbo].[T_LINE_LIST]";
/*
* Add Column mapping here
* In LINELIST_H5N1
Admin1st_Province => Region
Admin2nd_District => City
In LINELIST_H5N6
Region => Region
Province => City
In LINELIST_H7N9
Province or State (reporting) => Region
City => City
District or County
*/
if (LinelistType == LineListType.Standard.ToString())
{
//sqlBulkCopy.ColumnMappings.Add("pathogen_id", "pathogen_id");
//sqlBulkCopy.ColumnMappings.Add("case_id", "case_id");
//sqlBulkCopy.ColumnMappings.Add("ctry_code", "ctry_code");
//sqlBulkCopy.ColumnMappings.Add("admin1", "Country");
//sqlBulkCopy.ColumnMappings.Add("admin2", "Region");
//sqlBulkCopy.ColumnMappings.Add("age", "Age");
//sqlBulkCopy.ColumnMappings.Add("gender", "Gender");
//sqlBulkCopy.ColumnMappings.Add("onset_date", "Onset Date");
//sqlBulkCopy.ColumnMappings.Add("created_by", "created_by");
//sqlBulkCopy.ColumnMappings.Add("date_created", "date_created");
//sqlBulkCopy.ColumnMappings.Add("updated_by", "updated_by");
//sqlBulkCopy.ColumnMappings.Add("date_updated", "date_updated");
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N1).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
where tbl1["ID"].ToString() != String.Empty
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["ID"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Admin1st_Province"],
admin2 = tbl1["Admin2nd_District"],
age = tbl1["Age"],
gender = tbl1["Sex"],
onset_date = tbl1["Onset Date"],
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
ro = tbl1["RO"]
};
dt = new DataTable();
dt.Columns.Add("pathogen_id", typeof(string));
dt.Columns.Add("case_id", typeof(string));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(string));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N6).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["No"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Region"],
admin2 = tbl1["Province"],
age = tbl1["Age"].ToString(),
gender = tbl1["Sex"],
onset_date = tbl1["Onset"] ?? DBNull.Value,
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
};
dt = new DataTable();
dt.Columns.Add("pathogen_id", typeof(int));
dt.Columns.Add("case_id", typeof(int));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(int));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H7N9).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
where tbl1["Case ID no"].ToString() != String.Empty
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["Case ID no"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Province or State (reporting)"],
admin2 = tbl1["City"],
age = tbl1["Age (years)"],
gender = tbl1["Sex (M/F)"],
onset_date = tbl1["Onset date (dd/mm/yyyy)"] ?? DBNull.Value,
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
};
dt = new DataTable();
//dt.Columns.Add("pathogen_id", typeof(string));
//dt.Columns.Add("case_id", typeof(string));
//dt.Columns.Add("ctry_code", typeof(string));
//dt.Columns.Add("admin1", typeof(string));
//dt.Columns.Add("admin2", typeof(string));
//dt.Columns.Add("age", typeof(string));
//dt.Columns.Add("gender", typeof(string));
//dt.Columns.Add("onset_date", typeof(string));
//dt.Columns.Add("created_by", typeof(string));
//dt.Columns.Add("date_created", typeof(string));
//dt.Columns.Add("updated_by", typeof(string));
//dt.Columns.Add("date_updated", typeof(string));
dt.Columns.Add("pathogen_id", typeof(string));
dt.Columns.Add("case_id", typeof(string));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(string));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
string tableName = "#tmp_linelist";
BulkUploader<Model.Linelist> linelistUploader = new BulkUploader<Model.Linelist>(tableName);
linelistUploader.AddMapping(x => x.admin1, "admin1", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.admin2, "admin2", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.age, "age", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.case_id, "case_id", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.flu_week, "flu_week", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.flu_year, "flu_year", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.created_by, "created_by", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.ctry_code, "ctry_code", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.date_created, "date_created", "datetime");
linelistUploader.AddMapping(x => x.date_updated, "date_updated", "datetime");
linelistUploader.AddMapping(x => x.gender, "gender", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.onset_date, "onset_date", "date");
linelistUploader.AddMapping(x => x.pathogen_id, "pathogen_id", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.updated_by, "updated_by", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.remarks, "remarks", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.region_code, "region_code", "nvarchar(Max)");
linelistUploader.CreateTable(con, transaction);
sqlBulkCopy.BulkCopyTimeout = 60;
//var records = dt.AsEnumerable();
linelistUploader.Upload(sqlBulkCopy, MiscHelper.DataTableToListLinelist(dt));
// USP Clean
/*
* Remarking and linelist cleaning
*
*/
var cmd = new SqlCommand(StoredProcedure.Linelist_Import, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
}
}
return true;
}
catch (Exception e)
{
throw e;
}
}
}
public bool BulkInsertData(string sourceConnectionString, string targetConnectionString,
Action<int> valueSetter, Action progressAction,string command)
{
try
{
var sourceconstring = new CryptLib.CryptLib().Decrypt3DES(sourceConnectionString);
var targetconstring = new CryptLib.CryptLib().Decrypt3DES(targetConnectionString);
_progressAction = progressAction;
using (SqlConnection targetcon = new SqlConnection(targetconstring))
{
targetcon.Open();
var clear = new SqlCommand();
clear.Connection = targetcon;
clear.CommandText = StoredProcedure.Viro_Truncate;
clear.CommandType = CommandType.Text;
clear.ExecuteNonQuery();
using (SqlTransaction transaction = targetcon.BeginTransaction())
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(targetcon, SqlBulkCopyOptions.KeepNulls, transaction))
{
sqlBulkCopy.DestinationTableName = "dbo.T_VIRO_FLUNETWIDE";
sqlBulkCopy.BulkCopyTimeout = 500000000;
sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
sqlBulkCopy.NotifyAfter = 1;
var sourcecon = new SqlConnection(sourceconstring);
var sourceCommand = new SqlCommand();
sourceCommand.Connection = sourcecon;
sourceCommand.CommandText = command;
//sourceCommand.CommandText = "SELECT * FROM ESRSITUPD.dbo.T_USER_EPI";
sourceCommand.CommandType = System.Data.CommandType.Text;
sourcecon.Open();
var dataReader = sourceCommand.ExecuteReader();
var dt = new DataTable();
dt.Load(dataReader);
var totalRow = dt.Rows.Count;
valueSetter(totalRow);
try
{
sqlBulkCopy.WriteToServer(dt);
}
catch (Exception e)
{
throw e;
}
sourcecon.Close();
}
transaction.Commit();
}
targetcon.Close();
}
return true;
}
catch (Exception e)
{
throw e;
}
}
public Impersonate(string user, string pass, string domain)
{
_user = user;
_domain = domain;
_pass = new CryptLib.CryptLib().Decrypt3DES(pass);
}
