示例#1
0
        private DataTable UpdateGraphInternalChunk(string argKat, DateTime dtFraArg, DateTime dtTilArg, BackgroundWorker bw, string sk = "")
        {
            Log.d("Oppdaterer graf: " + argKat + " | Fra: " + dtFraArg.ToShortDateString() + " | Til: " + dtTilArg.ToShortDateString());

            var dt = new DataTable();
            string skString = "";
            if (!String.IsNullOrEmpty(sk))
                skString = " AND Selgerkode = '" + sk + "' ";

            string product = "";
            if (argKat == "KnowHow")
                product = "Varegruppe = '531' OR Varegruppe = '533' OR Varegruppe = '534' OR Varegruppe = '224' OR Varegruppe = '431'";
            else if (argKat == "Data")
                product = "Varegruppe = '531' OR Varegruppe = '533'";
            else if (argKat == "Nettbrett")
                product = "Varegruppe = '534'";
            else if (argKat == "AudioVideo")
                product = "Varegruppe = '224'";
            else if (argKat == "Tele")
                product = "Varegruppe = '431'";
            else
                return dt;

            dt.Columns.Add("Index", typeof(int));
            dt.Columns.Add("Dato", typeof(DateTime));

            DataTable dtResult;
            int days = (dtTilArg - dtFraArg).Days + 1;

            for (int d = 0; d < Favoritter.Count; d++)
            {
                if (!String.IsNullOrEmpty(sk) && d > 0)
                    break;

                string command = "SELECT SUM(CASE WHEN " + product + " THEN Antall ELSE 0 END) AS Product, "
                    + "SUM(CASE WHEN " + GetVarekodeSqlString(argKat) + " THEN Antall ELSE 0 END) AS Service, "
                    + "Dato FROM tblSalg WHERE (Avdeling = '" + Favoritter[d] + "') AND (Dato >= '"
                    + dtFraArg.ToString("yyy-MM-dd") + "') AND (Dato <= '" + dtTilArg.ToString("yyy-MM-dd")
                    + "') " + skString + " GROUP BY Dato";

                dtResult = main.database.GetSqlDataTable(command);

                dt.Columns.Add(Favoritter[d], typeof(StorageData));
                if (dt.Rows.Count == 0)
                {
                    for (int o = 0; o < days; o++)
                    {
                        if (bw != null)
                            if (bw.WorkerReportsProgress)
                                bw.ReportProgress(o, new StatusProgress(days, "Oppdaterer graf..", 0, 40));
                        if (FormMain._graphReqStop)
                            return null;

                        DataRow row = dt.NewRow();
                        row[0] = o; // Index
                        int count = dtResult.Rows.Count;
                        for (int i = 0; i < count; i++)
                        {
                            if (Convert.ToDateTime(dtResult.Rows[i][2]) == dtFraArg.AddDays(o))
                            {
                                row[1] = (DateTime)dtResult.Rows[i][2]; // Dato
                                var store = new StorageData((int)dtResult.Rows[i][0], (int)dtResult.Rows[i][1]);
                                row[d + 2] = store;
                                break;
                            }
                            else
                            {
                                row[1] = dtFraArg.AddDays(o); // Dato
                                row[d + 2] = new StorageData(0, 0);
                            }
                        }
                        dt.Rows.Add(row);
                    }
                }
                else
                    for (int b = 0; b < dt.Rows.Count; b++)
                        for (int i = 0; i < dtResult.Rows.Count; i++)
                            if ((DateTime)dt.Rows[b][1] == (DateTime)dtResult.Rows[i][2])
                                dt.Rows[b][Favoritter[d]] = new StorageData((int)dtResult.Rows[i][0], (int)dtResult.Rows[i][1]);

                for (int b = 0; b < dt.Rows.Count; b++)
                    if (DBNull.Value.Equals(dt.Rows[b][Favoritter[d]]))
                        dt.Rows[b][Favoritter[d]] = new StorageData(0, 0);
            }

            if (main.appConfig.graphAdvanced && String.IsNullOrEmpty(sk))
            {
                if (FormMain._graphReqStop)
                    return null;

                dt.Columns.Add("TOP", typeof(StorageTop));

                string command = "SELECT Dato, Selgerkode, SUM(CASE WHEN " + GetVarekodeSqlString(argKat) + " THEN Antall ELSE 0 END) AS Antall FROM tblSalg WHERE (Avdeling = '" + Favoritter[0] + "') AND (Dato >= '" + dtFraArg.ToString("yyy-MM-dd") + "') AND (Dato <= '" + dtTilArg.ToString("yyy-MM-dd") + "') GROUP BY Dato, Selgerkode";
                dtResult = main.database.GetSqlDataTable(command);

                if (dtResult.Rows.Count == 0)
                    return dt;

                int count = dt.Rows.Count;
                for (int b = 0; b < count; b++)
                {
                    if (bw != null)
                        if (bw.WorkerReportsProgress)
                            bw.ReportProgress(b, new StatusProgress(count, "Oppdaterer graf detaljer.. ", 40, 100));
                    if (FormMain._graphReqStop)
                        return null;

                    DateTime date = (DateTime)dt.Rows[b][1];

                    DataView view = new DataView(dtResult);
                    view.RowFilter = string.Format(CultureInfo.InvariantCulture, "Dato = '{0}'", date.ToString("o", CultureInfo.InvariantCulture));
                    view.Sort = "Antall DESC";
                    DataTable res = view.ToTable();

                    var store = new StorageTop();

                    int resCount = res.Rows.Count;
                    for (int i = 0; i < resCount && i < 5; i++)
                    {
                        int value = Convert.ToInt32(res.Rows[i][2]);
                        if (value > 0)
                        {
                            store.selgere.Add(new StorageSelger(res.Rows[i][1].ToString(), value));
                            dt.Rows[b]["TOP"] = store;
                        }
                    }
                }
            }

            return dt;
        }
示例#2
0
        private DataTable UpdateButikkGraphChunk(DateTime dtFraArg, DateTime dtTilArg, BackgroundWorker bw)
        {
            Log.d("Oppdaterer graf: Butikk | Fra: " + dtFraArg.ToShortDateString() + " | Til: " + dtTilArg.ToShortDateString());

            var dt = new DataTable();
            dt.Columns.Add("Index", typeof(int));
            dt.Columns.Add("Dato", typeof(DateTime));

            DataTable dtResult;
            int days = (dtTilArg - dtFraArg).Days + 1;

            for (int d = 0; d < Favoritter.Count; d++)
            {
                string sqlstring = "SELECT SUM(Btokr) AS Btokr, SUM(Salgspris) AS Salgspris, SUM(Salgspris / Mva) AS Salgsexmva, Dato FROM tblSalg " +
                    "WHERE (Avdeling = '" + Favoritter[d] + "') AND (Dato >= '" + dtFraArg.ToString("yyy-MM-dd") + "') AND (Dato <= '" + dtTilArg.ToString("yyy-MM-dd") + "') GROUP BY Dato";
                dtResult = main.database.GetSqlDataTable(sqlstring);

                dt.Columns.Add(Favoritter[d], typeof(StorageButikk));
                if (dt.Rows.Count == 0)
                {
                    for (int o = 0; o < days; o++)
                    {
                        if (bw != null)
                            if (bw.WorkerReportsProgress)
                                bw.ReportProgress(o, new StatusProgress(days, "Oppdaterer graf.. ", 0, 40));
                        if (FormMain._graphReqStop)
                            return null;

                        DataRow row = dt.NewRow();
                        row[0] = o; // Index
                        for (int i = 0; i < dtResult.Rows.Count; i++)
                        {
                            if (Convert.ToDateTime(dtResult.Rows[i][3]) == dtFraArg.AddDays(o))
                            {
                                row[1] = (DateTime)dtResult.Rows[i][3]; // Dato
                                var store = new StorageButikk(Convert.ToInt32(dtResult.Rows[i][0]), Convert.ToInt32(dtResult.Rows[i][1]), Convert.ToInt32(dtResult.Rows[i][2]));
                                row[d + 2] = store;
                                break;
                            }
                            else
                            {
                                row[1] = dtFraArg.AddDays(o); // Dato
                                row[d + 2] = new StorageButikk(0, 0, 0);
                            }
                        }
                        dt.Rows.Add(row);
                    }
                }
                else
                    for (int b = 0; b < dt.Rows.Count; b++)
                        for (int i = 0; i < dtResult.Rows.Count; i++)
                            if ((DateTime)dt.Rows[b][1] == (DateTime)dtResult.Rows[i][3])
                                dt.Rows[b][Favoritter[d]] = new StorageButikk(Convert.ToInt32(dtResult.Rows[i][0]), Convert.ToInt32(dtResult.Rows[i][1]), Convert.ToInt32(dtResult.Rows[i][2]));

                for (int b = 0; b < dt.Rows.Count; b++)
                    if (DBNull.Value.Equals(dt.Rows[b][Favoritter[d]]))
                        dt.Rows[b][Favoritter[d]] = new StorageButikk(0, 0, 0);
            }

            if (main.appConfig.rankingCompareLastyear > 0 && dt.Columns.Count > 2)
            {
                string sqlstring = "SELECT SUM(Btokr) AS Btokr, Dato FROM tblSalg " +
                    "WHERE (Avdeling = '" + main.appConfig.Avdeling + "') AND (Dato >= '" + dtFraArg.AddYears(-1).ToString("yyy-MM-dd") +
                    "') AND (Dato <= '" + dtTilArg.AddYears(-1).ToString("yyy-MM-dd") + "') GROUP BY Dato";

                using (dtResult = main.database.GetSqlDataTable(sqlstring))
                {
                    for (int b = 0; b < dt.Rows.Count; b++)
                        for (int i = 0; i < dtResult.Rows.Count; i++)
                            if (Convert.ToDateTime(dt.Rows[b][1]) == Convert.ToDateTime(dtResult.Rows[i][1]).AddYears(1))
                            {
                                var store = (StorageButikk)dt.Rows[b][2];
                                store.ifjor_btokr = Convert.ToInt32(dtResult.Rows[i][0]);
                                dt.Rows[b][2] = store;
                            }
                }
            }

            if (main.appConfig.graphAdvanced)
            {
                Log.Status("Oppdaterer graf detaljer..");

                dt.Columns.Add("TOP", typeof(StorageTop));

                string command = "SELECT Dato, Selgerkode, SUM(Btokr) AS Btokr FROM tblSalg WHERE (Avdeling = '" + Favoritter[0] + "') AND (Dato >= '" + dtFraArg.ToString("yyy-MM-dd") + "') AND (Dato <= '" + dtTilArg.ToString("yyy-MM-dd") + "') GROUP BY Dato, Selgerkode";
                dtResult = main.database.GetSqlDataTable(command);

                int count = dt.Rows.Count;
                for (int b = 0; b < dt.Rows.Count; b++)
                {
                    if (bw != null)
                        if (bw.WorkerReportsProgress)
                            bw.ReportProgress(b, new StatusProgress(count, "Oppdaterer graf detaljer.. ", 40, 100));
                    if (FormMain._graphReqStop)
                        return null;

                    DateTime date = (DateTime)dt.Rows[b][1];

                    DataView view = new DataView(dtResult);
                    view.RowFilter = string.Format(CultureInfo.InvariantCulture, "Dato = '{0}'", date.ToString("o", CultureInfo.InvariantCulture));
                    view.Sort = "Btokr DESC";
                    DataTable res = view.ToTable();

                    var store = new StorageTop();

                    for (int i = 0; i < res.Rows.Count && i < 6; i++)
                    {
                        int value = Convert.ToInt32(res.Rows[i][2]);
                        if (value > 0)
                        {
                            store.selgere.Add(new StorageSelger(res.Rows[i][1].ToString(), value));
                            dt.Rows[b]["TOP"] = store;
                        }

                    }
                }
            }

            return dt;
        }