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; 

        }
Exemple #2
0
        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();
        }
Exemple #3
0
        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;
            }

            
        }
Exemple #5
0
 public Impersonate(string user, string pass, string domain)
 {
     _user = user;
     _domain = domain;
     _pass = new CryptLib.CryptLib().Decrypt3DES(pass);
 }