5
\$\begingroup\$

I am a newbie at programming and I helped my dad with making this program which orders multiple products to a website from a table of Excel.

It involves 3 processes:

  1. Passing orders from Excel to website by injecting JavaScript.

    The website provides a function for ordering a product: (in JavaScript)

    updateProduct('{reference}','0','/products/update'{quantity},110,'divAlertQteDispoMessage',0,900,'divAlertNbRowsMessage');
    
  2. Comparing the product list between the website and the Excel sheet.

    My current solution is to get all reference id found in website and compare it with the Excel's reference id

  3. Deleting products which have been ordered before.

    Using .cmd according to the reference, we can get the quantity of the product ordered before.

Here is the entire class:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Awesomium.Core;
using Awesomium.Windows.Controls;
using ClosedXML.Excel;
using FirstFloor.ModernUI.Windows.Controls;
using Brushes = System.Windows.Media.Brushes;
using SystemFonts = System.Windows.SystemFonts;

namespace ModernUINavigationApp1.Pages
{

    /// <summary>
    /// Interaction logic for Home.xaml
    /// </summary>
    /// 

    public partial class Home : UserControl
    {

        private Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        public Home()
        {
            InitializeComponent();
            Startup();
            MainwebBrowser.ViewType = WebViewType.Offscreen;

        }

        private async void Startup()
        {
            await Task.Delay(2000);
            while (!MainwebBrowser.IsDocumentReady)
            {
                await Task.Delay(1000);
            }
            await Task.Delay(1000);
                MainwebBrowser.ExecuteJavascript(
       "document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
                MainwebBrowser.ExecuteJavascript(
                    "document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
                    MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");


        }




        private void BrowseButton_Click(object sender, RoutedEventArgs e)
        {
            Nullable<bool> result = dlg.ShowDialog();
            if (result == true)
            {
                string filename = dlg.FileName;
                TextBoxFile.Text = filename;
                DataTable dt = LoadXls(dlg.FileName);
                _label.Content = dt.Rows.Count + " Produits";
            }
        }



        public bool _checkcancelled;

        private async void CommanderButtonImport_Click(object sender, RoutedEventArgs e)
        {
            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show(
                    "Vous n'avez pas entré l'emplacement du fichier!", "Attention", MessageBoxButton.OK,
                    MessageBoxImage.Exclamation);
            }
            else
            {
                _checkcancelled = false;
                DisableControl();
                CommanderButtonImport.IsEnabled = false;
                DataTable dt = LoadXls(dlg.FileName);
                if (dt != null)
                {
                    PassProduct(dt);
                    //CompareProduct();


                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                    //  button5.Visible = false;
                    //  label4.Text = "Annulé...";
                    await Task.Delay(2000);
                    //   label4.Text = "Libre";
                }
            }


        }

        private async void DeleteProduct()
        {
            _label2.Content = "Étape 3 : Supprimmer les produits dupliqués";
            _label3.Content = "0 %";
            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show(
                    "Vous n'avez pas entré l'emplacement du fichier!", "Attention", MessageBoxButton.OK,
                    MessageBoxImage.Exclamation);
            }
            else
            {
                do
                {
                    MainwebBrowser.ExecuteJavascript(
"function GetIsHome() {if (document.getElementById('topNav') != null) { return 'true'; }};");
                    if (MainwebBrowser.Source != new Uri("http://www.easyrea.net/cart/detail"))
                    {
                        MainwebBrowser.Source = new Uri("http://www.easyrea.net/cart/detail");
                    }
                    else
                    {
                        MainwebBrowser.Reload(true);
                    }
                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);
                    MainwebBrowser.ExecuteJavascript(
           "document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
                    MainwebBrowser.ExecuteJavascript(
                        "document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
                    MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");
                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);
                } while ((string)MainwebBrowser.ExecuteJavascriptWithResult("GetIsHome()") != "true");

            DataTable dt = LoadXls(dlg.FileName);
                progressBar1.Value = 0.0;
                progressBar1.Maximum = dt.Rows.Count;
                foreach (DataRow row in dt.Rows)
                {
                    if (!_checkcancelled)
                    {
                        string text = "#product" + row["Référence"];
                        string cmd =
                            MainwebBrowser.ExecuteJavascriptWithResult(
                                string.Format("$('{0}').find('.cmd').text();", text));
                        string existed =
                            MainwebBrowser.ExecuteJavascriptWithResult(
                                string.Format("$('{0}').find('#quantity').text();", text));
                        if (cmd != "" & existed != "")
                        {
                            int intcmd;
                            bool isintcmd = int.TryParse(Regex.Match(cmd, @"\d+").Value, out intcmd);

                            int intexisted;
                            bool isintexisted = int.TryParse(existed, out intexisted);
                            if (isintcmd & isintexisted)
                            {
                                if (intcmd >= intexisted)
                                {
                                    MainwebBrowser.ExecuteJavascriptWithResult(
                                        string.Format(
                                            "updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)",
                                            row["Référence"], intexisted));
                                }
                                else if (intcmd < intexisted)
                                {
                                    int inttobesubed = intexisted - (intexisted - intcmd);
                                    MainwebBrowser.ExecuteJavascriptWithResult(
                                        string.Format(
                                            "updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)",
                                            row["Référence"], inttobesubed));
                                }
                            }
                        }
                        progressBar1.Value++;
                        int percent = (int)(progressBar1.Value / progressBar1.Maximum * 100);
                        _label3.Content = percent + " %";


                    }
                    else
                    {
                        progressBar1.Value = 0;
                        EnableControl();
                        _label3.Content = "";
                        _label2.Content = "Annulé...";
                        await Task.Delay(2000);
                        _label2.Content = "Libre";
                        deleteProductSuccess = false;
                        CommanderButtonImport.IsEnabled = true;
                        break;
                    }
                    deleteProductSuccess = true;
                    await Task.Delay(1000);
                }
                if (!deleteProductSuccess)
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                      _label3.Content = "";
                      _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                      _label2.Content = "Libre";
                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                    MainwebBrowser.Reload(false);
                    await Task.Delay(5000);
                    _label2.Content = "Libre";
                    _label3.Content = "";
                    if (
                        ModernDialog1.ShowMessage(
                            "L'Opération est terminée." + Environment.NewLine +
                            "Voulez-vous ouvrir la ficher excel pour les produits manquant?", "Terminée",
                            MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                    {
                        System.Diagnostics.Process.Start("missingproduct.xlsx");
                    }
                }




            }
        }

        private DataTable LoadXls(string strFile)
        {
            var dtXLS = new DataTable("importedexcel");

            try
            {
                string strConnectionString = "";

                if (strFile.Trim().EndsWith(".xlsx"))
                {
                    strConnectionString =
                        string.Format(
                            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";",
                            strFile);
                }
                else if (strFile.Trim().EndsWith(".xls"))
                {
                    strConnectionString =
                        string.Format(
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";",
                            strFile);
                }

                var SQLConn = new OleDbConnection(strConnectionString);
                SQLConn.Open();

                var SQLAdapter = new OleDbDataAdapter();
                DataTable dtSchema = SQLConn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string Sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                string sql = string.Format("SELECT * FROM [{0}]", Sheet1);

                var selectCMD = new OleDbCommand(sql, SQLConn);
                SQLAdapter.SelectCommand = selectCMD;

                SQLAdapter.Fill(dtXLS);
                SQLConn.Close();
            }
            catch (Exception ex)
            {
                progressBar1.Maximum = 0;
                MessageBox.Show(
                    "Le fichier excel est en cour d'utilisation, il faut le fermer pour que nous puissions l'utiliser." +
                    ex.ToString());
                dtXLS = null;
            }
            return dtXLS;
        }

        private void DisableControl()
        {
            mainTabControl.IsEnabled = false;
        }

        private void EnableControl()
        {
            mainTabControl.IsEnabled = true;
        }

        private bool passProductSuccess;
        private bool deleteProductSuccess;
        private bool compareProductSuccess;

        private async void PassProduct(DataTable dt)
        {
            _label2.Content = "Étape 1 : Passer les produits";
            _label3.Content = "0 %";
            MainwebBrowser.ExecuteJavascript("window.alert = function() {};");
            progressBar1.Value = 0.0;
            progressBar1.Maximum = dt.Rows.Count;
            foreach (DataRow row in dt.Rows)
            {
                string text = row["Référence"].ToString();
                string qtestring = row["Nb# Colis"].ToString();
                int qte;
                int.TryParse(qtestring, out qte);
                if (text.Any(char.IsDigit) & _checkcancelled == false)
                {
                    if (qte < 1 || qte > 0)
                    {
                        qte = 1;

                    }

                    MainwebBrowser.ExecuteJavascript(
                        string.Format(
                            "updateProduct('{0}','0','/products/update',{1},110,'divAlertQteDispoMessage',0,900,'divAlertNbRowsMessage');",
                            text, qte));
                    //    label5.Text = "Sur:";
                    //     label4.Text = row["Libellé"].ToString();
                    //    label3.Text = text;
                    await Task.Delay(2500);
                    progressBar1.Value++;
                    int percent = (int) (progressBar1.Value / progressBar1.Maximum * 100);
                    _label3.Content = percent + " %";
                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    _label3.Content = "";
                    _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                    _label2.Content = "Libre";
                    passProductSuccess = false;
                    break;

                }
                passProductSuccess = true;
            }
            if (passProductSuccess)
            {
                _label2.Content = "Étape 1.5 : Attendre la systeme de JJA...";
                _label3.Content = "20 secondes restant";
                progressBar1.Value = 0.0;
                progressBar1.Maximum = 20;
                for (int a = 0; a < 20; a = a + 1)
                {
                    await Task.Delay(1000);
                    progressBar1.Value++;
                    _label3.Content = 20 - a + " secondes restant";
                }
                CompareProduct(); 

            }
            else
            {
                CommanderButtonImport.IsEnabled = true;
                progressBar1.Value = 0;
                EnableControl();
                _label3.Content = "";
                _label2.Content = "Annulé...";
                await Task.Delay(2000);
                _label2.Content = "Libre";
            }

        }

        private async void CompareProduct()
        {
            progressBar1.Value = 0.0;

            _label2.Content = "Étape 2 : Exporter les produits manqants au fichier Excel";
            _label3.Content = "0 %";

            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show(
                    "Vous n'avez pas entré l'emplacement du fichier!", "Attention", MessageBoxButton.OK,
                    MessageBoxImage.Exclamation);
            }
            else
            {
                do
                {
                        MainwebBrowser.Source = new Uri("http://www.easyrea.net/cart/detail");
                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);
                    MainwebBrowser.ExecuteJavascript(
           "document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
                    MainwebBrowser.ExecuteJavascript(
                        "document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
                    MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");
                    await Task.Delay(2000);
                    MainwebBrowser.ExecuteJavascript(
"function GetIsHome() {if (document.getElementById('topNav') != null) { return 'true'; }};");
                } while ((string)MainwebBrowser.ExecuteJavascriptWithResult("GetIsHome();") != "true");



                await Task.Delay(5000);
                DataTable dt = LoadXls(dlg.FileName);
                progressBar1.Maximum = dt.Rows.Count;
                DataTable missingProductTable = new DataTable();
                missingProductTable.Columns.Add("Libellé", typeof(String));
                missingProductTable.Columns.Add("Référence", typeof(String));
                missingProductTable.Columns.Add("Codein", typeof(String));
                missingProductTable.Columns.Add("Gencode", typeof(String));
                foreach (DataRow row in dt.Rows)
                {
                    if (!_checkcancelled)
                    {
                        string text = row["Référence"].ToString();
                        string name = row["Libellé"].ToString();
                        string code = row["Codein"].ToString();
                        string gene = row["Gencode"].ToString();
                        string caps = text.ToUpper();
                        if (caps.Any(char.IsDigit) & _checkcancelled == false)
                        {
                            MainwebBrowser.ExecuteJavascript(
                                "function GetMissingProduct() {if (document.getElementById('product" + caps +
                                "') == null) { return 'true'; }};");
                            var missingProduct =
                                (string) MainwebBrowser.ExecuteJavascriptWithResult("GetMissingProduct()");
                            if (missingProduct == "true")
                            {
                                DataRow missingProductRow = missingProductTable.NewRow();
                                missingProductRow["Libellé"] = name;
                                missingProductRow["Référence"] = text;
                                missingProductRow["Codein"] = code;
                                missingProductRow["Gencode"] = gene;
                                missingProductTable.Rows.Add(missingProductRow);
                            }
                        }
                        await Task.Delay(500);
                    }
                    else
                    {
                        progressBar1.Value = 0;
                        EnableControl();
                        _label3.Content = "";
                        _label2.Content = "Annulé...";
                        await Task.Delay(2000);
                        _label2.Content = "Libre";
                        compareProductSuccess = false;
                        MessageBox.Show("Failed");
                        CommanderButtonImport.IsEnabled = true;
                        break;
                    }
                    progressBar1.Value++;
                    int percent = (int)(progressBar1.Value / progressBar1.Maximum * 100);
                    _label3.Content = percent + " %";
                }
                XLWorkbook wb = new XLWorkbook();
                wb.Worksheets.Add(missingProductTable, "Produit Manquant");
                wb.SaveAs("missingproduct.xlsx");
                compareProductSuccess = true;
                if (compareProductSuccess)
                {
                    DeleteProduct();

                }
                else
                {
                    CommanderButtonImport.IsEnabled = true;
                    progressBar1.Value = 0;
                    EnableControl();
                    _label3.Content = "";
                    _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                    _label2.Content = "Libre";
                }
            }
        }









    }


}

Remark: CommanderButtonImport is a button for triggering everything.

Please provide some comments because I feel like it is unorganized but I don't know where to start.

\$\endgroup\$
  • 1
    \$\begingroup\$ Post-answer changes don't need to be added to the question. \$\endgroup\$ – Jamal Aug 4 '15 at 20:05
8
\$\begingroup\$

Alright, let's go through the code top-to-bottom.


using Brushes = System.Windows.Media.Brushes;
using SystemFonts = System.Windows.SystemFonts;

Any particular reason you're using this alias? Most people I see that use aliases (including me) have these as part of an automatic re-formatting of the document.


private Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

As far as I can tell you only show the dialog once (to get the location) and afterwards you only use the filename. I would keep a local field pointed to the filename and create the dialog inside BrowseButton_Click instead. That way you can also easily check for if(filename == null) instead of doing if (string.IsNullOrEmpty(TextBoxFile.Text)).


private async void Startup()

async void is only acceptable in two situations: events and partial methods. Async void methods are bad (tricky?) because you can't actually await them: they are fire-and-forget. Always make them return Task and perhaps use a library like AsyncEx to create an asynchronous context to work from.


await Task.Delay(2000);

This is a pointless delay. Why start your program and immediately put everything on hold? You're not waiting for anything to load but if you are then you should make that very clear (both to the user and the programmer).


while (!MainwebBrowser.IsDocumentReady)
{
    await Task.Delay(1000);
}

I'm not familiar with the library but isn't there an event you can subscribe to instead? I notice something like LoadingFrameComplete but there could be others too. Either way I would decrease the delay to, say, 100ms for better responsiveness and no difference in performance.


await Task.Delay(1000);

Again a seemingly pointless sleep.


string filename = dlg.FileName;
TextBoxFile.Text = filename;
DataTable dt = LoadXls(dlg.FileName);

I don't think it's likely for race conditions to pop up here but it's still a nice habit to take with you: what would happen if dlg.FileName changed its value between line 2 and 3? You would load the wrong data! I would suggest re-using the filename variable.


General note about naming: keep things descriptive. dlg, dt, _label, etc are all bad names because they don't convey what they are about. Don't abbreviate (unless a few specific known ones like db) and keep things expressive.


public bool _checkcancelled;

Expose variables using properties. That way you adhere to the "encapsulation" principle of Object Oriented programming. By using properties you can always decide later to provide setter/getter implementation without breaking the contract.


Naming conventions!

Look through the list here but keep in mind that private non-static, non-const fields are also accepted to be _lowerCamelCase.


I typically prefer string.IsNullOrWhiteSpace over string.IsNullOrEmpty because the former also takes care of.. well.. whitespace characters.


if (MainwebBrowser.Source != new Uri("http://www.easyrea.net/cart/detail"))
{
    MainwebBrowser.Source = new Uri("http://www.easyrea.net/cart/detail");
}

Nobody likes to copy things twice. It's extra work and prone to errors. Instead, define it once:

var targetUri = new Uri("http://www.easyrea.net/cart/detail");
if (MainwebBrowser.Source != targetUri)
{
    MainwebBrowser.Source = targetUri;
}

You're logging in in a method called DeleteProduct() -- this violates the method's Single Responsibility Principle. Perform the logging in in a separate method, this will also be easier for you to keep track of what happens where. On top of that, you also won't have to repeat yourself each time you want to log in.


Feels a little pointless to inject a JS function and then only call it once. Can't you directly call if (document.getElementById('topNav') != null) { return 'true'; } from ExecuteJavascriptWithResult()? I would use an intermediate variable to store the string for readability purposes.


I've noticed you're loading that datatable many times. Have you considered loading it once, doing the reading from that and only reloading it when you actually modify the data source?


if (cmd != "" & existed != "")

Stick to one way of comparison: string.IsNullOrX would be more conform to the rest of the code.


int intcmd;
bool isintcmd = int.TryParse(Regex.Match(cmd, @"\d+").Value, out intcmd);

int intexisted;
bool isintexisted = int.TryParse(existed, out intexisted);
if (isintcmd & isintexisted)

This could instead be written as

int intcmd = int.Parse(Regex.Match(cmd, @"\d+").Value);
int intexisted;
if (int.TryParse(existed, out intexisted))

You don't really need the boolean results so you might as well use them in the if directly. Since your regex matches only numbers, you will only receive numbers and your parse will succeed either way (aka: no need for the TryParse).


_label3.Content = "";

I prefer string.Empty because it clearly conveys your intent of wanting an empty string, and I don't have that nagging thing in the back of my head telling me it might be a typo.


System.Diagnostics.Process.Start("missingproduct.xlsx");

This might be a suitable candidate to place in a configuration file! That way your father can change the name of the file without needing you to entirely re-compile the program. The same thing can be said about the connection strings (though those will be for you then).


if (strFile.Trim().EndsWith(".xlsx"))

I would advise an actual extension check like Path.GetExtension(strFile).


var SQLConn = new OleDbConnection(strConnectionString);

Use using statements so you don't have to worry about manually closing. https://msdn.microsoft.com/en-us/library/yh598w02.aspx


Are you using C# 6? You can change this

string sql = string.Format("SELECT * FROM [{0}]", Sheet1);

into this

string sql = $"SELECT * FROM [{Sheet1}]";

ex.ToString()

I really don't like it when people use the .ToString() method outside of debugging situations. Sure, sometimes it is a useful representation of the object but that is its entire contract: represent the object. It does not say what data it shows and as such, can be changed in any new release. If you have the option available like for example ex.Message or ex.StackTrace then you should use these instead.


if (qte < 1 || qte > 0)
{
    qte = 1;
}

also known as:

qte = 1;

typeof(String)

String and string are aliases -- you can use either one but stick to that. Since you've already used string and others like int I would suggest you stick to string.


| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thank you very much for replying me, i modified the first 2 improvements. But for await Task.Delay(1000), in fact i was waiting for the page in the web browser to load.There is a event LoadingFrameComplete but i redirect different uri and they have different action to do. \$\endgroup\$ – thomasxd24 Aug 4 '15 at 20:41
  • \$\begingroup\$ In that case you should use the polling option (while(!finished) { sleep(100) }. \$\endgroup\$ – Jeroen Vannevel Aug 4 '15 at 22:01
  • \$\begingroup\$ Why not Task.Delay? I searched in the web and they say Task.Delay is better \$\endgroup\$ – thomasxd24 Aug 5 '15 at 4:27
  • \$\begingroup\$ And i don't understand how to put in a configuration file for System.Diagnostics.Process.Start("missingproduct.xlsx"); and i was thinking of put the file to a temporarily location and he must save to a location otherwise the file gets delete. \$\endgroup\$ – thomasxd24 Aug 5 '15 at 4:30
  • 1
    \$\begingroup\$ @jmoreno: assuming setting the Source property has no side-effects, that would be good too of course. \$\endgroup\$ – Jeroen Vannevel Aug 5 '15 at 12:19
6
\$\begingroup\$

The first thing I would do is spend a bit of time cleaning up your formatting. It's very hard to read as it stands.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Awesomium.Core;
using Awesomium.Windows.Controls;
using ClosedXML.Excel;
using FirstFloor.ModernUI.Windows.Controls;
using Brushes = System.Windows.Media.Brushes;
using SystemFonts = System.Windows.SystemFonts;

namespace ModernUINavigationApp1.Pages
{
    /// <summary>
    /// Interaction logic for Home.xaml
    /// </summary>
    public partial class Home : UserControl
    {
        private Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        public Home()
        {
            InitializeComponent();
            Startup();
            MainwebBrowser.ViewType = WebViewType.Offscreen;
        }

        private async void Startup()
        {
            await Task.Delay(2000);

            while (!MainwebBrowser.IsDocumentReady)
            {
                await Task.Delay(1000);
            }

            await Task.Delay(1000);
            MainwebBrowser.ExecuteJavascript("document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
            MainwebBrowser.ExecuteJavascript("document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
            MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");
        }

        private void BrowseButton_Click(object sender, RoutedEventArgs e)
        {
            Nullable<bool> result = dlg.ShowDialog();

            if (result == true)
            {
                string filename = dlg.FileName;
                TextBoxFile.Text = filename;
                DataTable dt = LoadXls(dlg.FileName);
                _label.Content = dt.Rows.Count + " Produits";
            }
        }

        public bool _checkcancelled;

        private async void CommanderButtonImport_Click(object sender, RoutedEventArgs e)
        {
            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                                "Attention",
                                MessageBoxButton.OK,
                                MessageBoxImage.Exclamation);
            }
            else
            {
                _checkcancelled = false;
                DisableControl();
                CommanderButtonImport.IsEnabled = false;
                DataTable dt = LoadXls(dlg.FileName);

                if (dt != null)
                {
                    PassProduct(dt);
                    //CompareProduct();
                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                    //  button5.Visible = false;
                    //  label4.Text = "Annulé...";
                    await Task.Delay(2000);
                    //   label4.Text = "Libre";
                }
            }
        }

        private async void DeleteProduct()
        {
            _label2.Content = "Étape 3 : Supprimmer les produits dupliqués";
            _label3.Content = "0 %";

            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                                "Attention",
                                MessageBoxButton.OK,
                                MessageBoxImage.Exclamation);
            }
            else
            {
                do
                {
                    MainwebBrowser.ExecuteJavascript("function GetIsHome() {if (document.getElementById('topNav') != null) { return 'true'; }};");

                    if (MainwebBrowser.Source != new Uri("http://www.easyrea.net/cart/detail"))
                    {
                        MainwebBrowser.Source = new Uri("http://www.easyrea.net/cart/detail");
                    }
                    else
                    {
                        MainwebBrowser.Reload(true);
                    }

                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);

                    MainwebBrowser.ExecuteJavascript("document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
                    MainwebBrowser.ExecuteJavascript("document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
                    MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");

                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);
                } while ((string)MainwebBrowser.ExecuteJavascriptWithResult("GetIsHome()") != "true");

                DataTable dt = LoadXls(dlg.FileName);
                progressBar1.Value = 0.0;
                progressBar1.Maximum = dt.Rows.Count;

                foreach (DataRow row in dt.Rows)
                {
                    if (!_checkcancelled)
                    {
                        string text = "#product" + row["Référence"];
                        string cmd = MainwebBrowser.ExecuteJavascriptWithResult(string.Format("$('{0}').find('.cmd').text();", text));
                        string existed = MainwebBrowser.ExecuteJavascriptWithResult(string.Format("$('{0}').find('#quantity').text();", text));

                        if (cmd != "" & existed != "")
                        {
                            int intcmd;
                            bool isintcmd = int.TryParse(Regex.Match(cmd, @"\d+").Value, out intcmd);

                            int intexisted;
                            bool isintexisted = int.TryParse(existed, out intexisted);

                            if (isintcmd & isintexisted)
                            {
                                if (intcmd >= intexisted)
                                {
                                    MainwebBrowser.ExecuteJavascriptWithResult(string.Format("updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)", row["Référence"], intexisted));
                                }
                                else if (intcmd < intexisted)
                                {
                                    int inttobesubed = intexisted - (intexisted - intcmd);
                                    MainwebBrowser.ExecuteJavascriptWithResult(string.Format("updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)", row["Référence"], inttobesubed));
                                }
                            }
                        }

                        progressBar1.Value++;
                        int percent = (int)(progressBar1.Value / progressBar1.Maximum * 100);
                        _label3.Content = percent + " %";
                    }
                    else
                    {
                        progressBar1.Value = 0;
                        EnableControl();
                        _label3.Content = "";
                        _label2.Content = "Annulé...";
                        await Task.Delay(2000);
                        _label2.Content = "Libre";
                        deleteProductSuccess = false;
                        CommanderButtonImport.IsEnabled = true;
                        break;
                    }

                    deleteProductSuccess = true;
                    await Task.Delay(1000);
                }

                if (!deleteProductSuccess)
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                    _label3.Content = "";
                    _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                    _label2.Content = "Libre";
                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    CommanderButtonImport.IsEnabled = true;
                    MainwebBrowser.Reload(false);
                    await Task.Delay(5000);
                    _label2.Content = "Libre";
                    _label3.Content = "";

                    if (ModernDialog1.ShowMessage("L'Opération est terminée." + Environment.NewLine + "Voulez-vous ouvrir la ficher excel pour les produits manquant?", "Terminée", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                    {
                        System.Diagnostics.Process.Start("missingproduct.xlsx");
                    }
                }
            }
        }

        private DataTable LoadXls(string strFile)
        {
            var dtXLS = new DataTable("importedexcel");

            try
            {
                string strConnectionString = "";

                if (strFile.Trim().EndsWith(".xlsx"))
                {
                    strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
                }
                else if (strFile.Trim().EndsWith(".xls"))
                {
                    strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
                }

                var SQLConn = new OleDbConnection(strConnectionString);
                SQLConn.Open();

                var SQLAdapter = new OleDbDataAdapter();
                DataTable dtSchema = SQLConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string Sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                string sql = string.Format("SELECT * FROM [{0}]", Sheet1);

                var selectCMD = new OleDbCommand(sql, SQLConn);
                SQLAdapter.SelectCommand = selectCMD;

                SQLAdapter.Fill(dtXLS);
                SQLConn.Close();
            }
            catch (Exception ex)
            {
                progressBar1.Maximum = 0;
                MessageBox.Show("Le fichier excel est en cour d'utilisation, il faut le fermer pour que nous puissions l'utiliser." + ex.ToString());
                dtXLS = null;
            }

            return dtXLS;
        }

        private void DisableControl()
        {
            mainTabControl.IsEnabled = false;
        }

        private void EnableControl()
        {
            mainTabControl.IsEnabled = true;
        }

        private bool passProductSuccess;
        private bool deleteProductSuccess;
        private bool compareProductSuccess;

        private async void PassProduct(DataTable dt)
        {
            _label2.Content = "Étape 1 : Passer les produits";
            _label3.Content = "0 %";
            MainwebBrowser.ExecuteJavascript("window.alert = function() {};");
            progressBar1.Value = 0.0;
            progressBar1.Maximum = dt.Rows.Count;

            foreach (DataRow row in dt.Rows)
            {
                string text = row["Référence"].ToString();
                string qtestring = row["Nb# Colis"].ToString();
                int qte;
                int.TryParse(qtestring, out qte);

                if (text.Any(char.IsDigit) & _checkcancelled == false)
                {
                    if (qte < 1 || qte > 0)
                    {
                        qte = 1;
                    }

                    MainwebBrowser.ExecuteJavascript(string.Format("updateProduct('{0}','0','/products/update',{1},110,'divAlertQteDispoMessage',0,900,'divAlertNbRowsMessage');", text, qte));
                    //    label5.Text = "Sur:";
                    //     label4.Text = row["Libellé"].ToString();
                    //    label3.Text = text;
                    await Task.Delay(2500);
                    progressBar1.Value++;
                    int percent = (int)(progressBar1.Value / progressBar1.Maximum * 100);
                    _label3.Content = percent + " %";
                }
                else
                {
                    progressBar1.Value = 0;
                    EnableControl();
                    _label3.Content = "";
                    _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                    _label2.Content = "Libre";
                    passProductSuccess = false;
                    break;
                }

                passProductSuccess = true;
            }

            if (passProductSuccess)
            {
                _label2.Content = "Étape 1.5 : Attendre la systeme de JJA...";
                _label3.Content = "20 secondes restant";
                progressBar1.Value = 0.0;
                progressBar1.Maximum = 20;

                for (int a = 0; a < 20; a = a + 1)
                {
                    await Task.Delay(1000);
                    progressBar1.Value++;
                    _label3.Content = 20 - a + " secondes restant";
                }

                CompareProduct();
            }
            else
            {
                CommanderButtonImport.IsEnabled = true;
                progressBar1.Value = 0;
                EnableControl();
                _label3.Content = "";
                _label2.Content = "Annulé...";
                await Task.Delay(2000);
                _label2.Content = "Libre";
            }
        }

        private async void CompareProduct()
        {
            progressBar1.Value = 0.0;

            _label2.Content = "Étape 2 : Exporter les produits manqants au fichier Excel";
            _label3.Content = "0 %";

            if (string.IsNullOrEmpty(TextBoxFile.Text))
            {
                MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                                "Attention",
                                MessageBoxButton.OK,
                                MessageBoxImage.Exclamation);
            }
            else
            {
                do
                {
                    MainwebBrowser.Source = new Uri("http://www.easyrea.net/cart/detail");

                    do
                    {
                        await Task.Delay(1000);
                    } while (!MainwebBrowser.IsDocumentReady);

                    MainwebBrowser.ExecuteJavascript("document.getElementById('login_login').value = '" + Properties.Settings.Default.Username + "';");
                    MainwebBrowser.ExecuteJavascript("document.getElementById('login_password').value = '" + Properties.Settings.Default.Password + "';");
                    MainwebBrowser.ExecuteJavascript("document.forms['loginForm'].submit()");
                    await Task.Delay(2000);
                    MainwebBrowser.ExecuteJavascript("function GetIsHome() {if (document.getElementById('topNav') != null) { return 'true'; }};");
                } while ((string)MainwebBrowser.ExecuteJavascriptWithResult("GetIsHome();") != "true");

                await Task.Delay(5000);
                DataTable dt = LoadXls(dlg.FileName);
                progressBar1.Maximum = dt.Rows.Count;
                DataTable missingProductTable = new DataTable();
                missingProductTable.Columns.Add("Libellé", typeof(String));
                missingProductTable.Columns.Add("Référence", typeof(String));
                missingProductTable.Columns.Add("Codein", typeof(String));
                missingProductTable.Columns.Add("Gencode", typeof(String));

                foreach (DataRow row in dt.Rows)
                {
                    if (!_checkcancelled)
                    {
                        string text = row["Référence"].ToString();
                        string name = row["Libellé"].ToString();
                        string code = row["Codein"].ToString();
                        string gene = row["Gencode"].ToString();
                        string caps = text.ToUpper();

                        if (caps.Any(char.IsDigit) & _checkcancelled == false)
                        {
                            MainwebBrowser.ExecuteJavascript("function GetMissingProduct() {if (document.getElementById('product" + caps + "') == null) { return 'true'; }};");
                            var missingProduct = (string)MainwebBrowser.ExecuteJavascriptWithResult("GetMissingProduct()");

                            if (missingProduct == "true")
                            {
                                DataRow missingProductRow = missingProductTable.NewRow();
                                missingProductRow["Libellé"] = name;
                                missingProductRow["Référence"] = text;
                                missingProductRow["Codein"] = code;
                                missingProductRow["Gencode"] = gene;
                                missingProductTable.Rows.Add(missingProductRow);
                            }
                        }

                        await Task.Delay(500);
                    }
                    else
                    {
                        progressBar1.Value = 0;
                        EnableControl();
                        _label3.Content = "";
                        _label2.Content = "Annulé...";
                        await Task.Delay(2000);
                        _label2.Content = "Libre";
                        compareProductSuccess = false;
                        MessageBox.Show("Failed");
                        CommanderButtonImport.IsEnabled = true;
                        break;
                    }

                    progressBar1.Value++;
                    int percent = (int)(progressBar1.Value / progressBar1.Maximum * 100);
                    _label3.Content = percent + " %";
                }

                XLWorkbook wb = new XLWorkbook();
                wb.Worksheets.Add(missingProductTable, "Produit Manquant");
                wb.SaveAs("missingproduct.xlsx");
                compareProductSuccess = true;

                if (compareProductSuccess)
                {
                    DeleteProduct();
                }
                else
                {
                    CommanderButtonImport.IsEnabled = true;
                    progressBar1.Value = 0;
                    EnableControl();
                    _label3.Content = "";
                    _label2.Content = "Annulé...";
                    await Task.Delay(2000);
                    _label2.Content = "Libre";
                }
            }
        }
    }
}

I know breaking parameters along multiple lines may seem like a good idea, but it breaks the flow of the code.

If (and this gets into one of those "holy wars" of programming) you must break parameters into multiple lines, you should put every parameter on a new line, and line them all up with whitespace until they all start at the same column.

I.e.:

           MessageBox.Show(
                "Vous n'avez pas entré l'emplacement du fichier!", "Attention", MessageBoxButton.OK,
                MessageBoxImage.Exclamation);

Would become:

                MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                                "Attention",
                                MessageBoxButton.OK,
                                MessageBoxImage.Exclamation);

You should always keep the first parameter on the same line as the method/assignment/etc. This keeps things in a logical format. It makes them easier to read in the future.


I would take some of these strings and make them constants/variables/configuration settings. This will allow you to change them easier and in one location.


Local variables, and private methods, fields, properties and events should be camelCase. I.e. _checkCancelled instead of _checkcancelled.


There is no need for int.TryParse(Regex.Match(cmd, @"\d+").Value, out intcmd); to be so complex. You do way more work than is needed. You can simply do: int.TryParse(cmd, out intcmd);.


Some of your if's can (should) be simplified:

if (isintcmd & isintexisted)
{
    if (intcmd >= intexisted)
    {
        MainwebBrowser.ExecuteJavascriptWithResult(string.Format("updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)", row["Référence"], intexisted));
    }
    else if (intcmd < intexisted)
    {
        int inttobesubed = intexisted - (intexisted - intcmd);
        MainwebBrowser.ExecuteJavascriptWithResult(string.Format("updateCartProduct('{0}','0','/cart/update',-{1},'72','1',0.65, 1)", row["Référence"], inttobesubed));
    }
}

You can remove the if (intcmd < intexisted) on the else portion. It's unnecessary and cannot be missed as it stands.


Some of your variables could go for more appropriate/meaningful names. You should consider renaming the base Controls from _label1 to something that indicates what the Label is.


You should consider defining all your private fields together. This will help you find them in the future. (Unlike C/C++, class level defines are valid throughout the entire class. Defining public bool _checkcancelled halfway through your class does not mean that it may only be used from then on.)


This large if/else block:

        if (string.IsNullOrEmpty(TextBoxFile.Text))
        {
            MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                            "Attention",
                            MessageBoxButton.OK,
                            MessageBoxImage.Exclamation);
        }
        else
        {

Which continues for a while could be simplified to:

        if (string.IsNullOrEmpty(TextBoxFile.Text))
        {
            MessageBox.Show("Vous n'avez pas entré l'emplacement du fichier!",
                            "Attention",
                            MessageBoxButton.OK,
                            MessageBoxImage.Exclamation);
            return;
        }

This eliminates the else portion and brings some of your code back to the right of your IDE. This is not counter-intuitive as it is considered part of parameter validation. From the moment you have determined that TextBoxFile.Text is not a null or empty string, it is considered valid throughout the rest of the method. Thus, the entire method does not need shifted to make room for this one validation.


Anywhere you use Nullable<T> can simply by T? This has the exact same effect but tends to be more accepted.

I.e.: Nullable<bool> result = dlg.ShowDialog(); would be: bool? result = dlg.ShowDialog();.


This is all I am leaving here for now, I may come back and add more later.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.