private DataTable UpdateOversiktGraphChunk(DateTime dtFraArg, DateTime dtTilArg, BackgroundWorker bw)
{
Log.d("Oppdaterer graf: Oversikt | 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 = '" + main.appConfig.Avdeling + "') 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
int count = dtResult.Rows.Count;
for (int i = 0; i < 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);
}
dt.Columns.Add("Tjenester", typeof(StorageTjenester));
Log.Status("Oppdaterer graf detaljer..");
string command = "SELECT Dato, " +
"SUM(CASE WHEN Varegruppe LIKE '961%' THEN Btokr ELSE 0 END) AS Finans, " +
"SUM(CASE WHEN Varegruppe LIKE '_83' AND Varekode LIKE 'MOD%' THEN Btokr ELSE 0 END) AS Ta, " +
"SUM(CASE WHEN Varekode LIKE 'ELSTROM%' OR Varekode LIKE 'ELRABATT%' THEN Btokr ELSE 0 END) AS Strom, " +
"SUM(CASE WHEN " + GetVarekodeSqlString() + " THEN Btokr ELSE 0 END) AS Tjen, " +
"SUM(CASE WHEN Varegruppe LIKE '_83' AND Varekode LIKE 'MOD%' THEN Salgspris ELSE 0 END) AS TaOmset " +
"FROM tblSalg WHERE (Avdeling = '" + Favoritter[0] + "') AND (Dato >= '" + dtFraArg.ToString("yyy-MM-dd") + "') AND (Dato <= '" + dtTilArg.ToString("yyy-MM-dd") + "') GROUP BY Dato";
dtResult = main.database.GetSqlDataTable(command);
int countDetaljer = dt.Rows.Count;
for (int b = 0; b < countDetaljer; b++)
{
if (bw != null)
if (bw.WorkerReportsProgress)
bw.ReportProgress(b, new StatusProgress(countDetaljer, "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));
DataTable day = view.ToTable();
if (day.Rows.Count > 0)
dt.Rows[b]["Tjenester"] = new StorageTjenester(Convert.ToInt32(day.Rows[0][1]), Convert.ToInt32(day.Rows[0][2]), Convert.ToInt32(day.Rows[0][3]), Convert.ToInt32(day.Rows[0][4]), Convert.ToInt32(day.Rows[0][5]));
else
dt.Rows[b]["Tjenester"] = new StorageTjenester(0, 0, 0, 0, 0);
}
return dt;
}
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;
}