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;
}
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;
}