private bool SaveStat(ServiceStat obj, string avd)
{
try
{
if (obj != null)
{
if (obj.avdeling == 0 || obj.totalt == 0)
return false;
var command = new SqlCeCommand("DELETE FROM tblServiceHistory WHERE (Dato = '" + obj.oppdatert.ToString("yyy-MM-dd") + "') AND (Avdeling = '" + avd + "')", main.connection);
var deleted = command.ExecuteNonQuery();
Log.d("Oppdatert historikk for " + obj.avdeling + " - " + obj.oppdatert);
string sql = "INSERT INTO tblServiceHistory (Avdeling, Dato, Totalt, Aktive, Ferdig, Tat, Over14, Over14prosent, Over21, Over21prosent, Tilarbeid) " +
"VALUES (@Avdeling, @Dato, @Totalt, @Aktive, @Ferdig, @Tat, @Over14, @Over14prosent, @Over21, @Over21prosent, @Tilarbeid)";
using (SqlCeCommand cmd = new SqlCeCommand(sql, main.connection))
{
cmd.Parameters.AddWithValue("@Avdeling", obj.avdeling);
cmd.Parameters.Add("@Dato", SqlDbType.DateTime).Value = obj.oppdatert;
cmd.Parameters.AddWithValue("@Totalt", obj.totalt);
cmd.Parameters.AddWithValue("@Aktive", obj.aktive);
cmd.Parameters.AddWithValue("@Ferdig", obj.ferdig);
cmd.Parameters.AddWithValue("@Tat", obj.tat60);
cmd.Parameters.AddWithValue("@Over14", obj.over14);
cmd.Parameters.AddWithValue("@Over14prosent", obj.over14prosent * 100);
cmd.Parameters.AddWithValue("@Over21", obj.over21);
cmd.Parameters.AddWithValue("@Over21prosent", obj.over21prosent * 100);
cmd.Parameters.AddWithValue("@Tilarbeid", obj.tilarbeid60);
cmd.CommandType = System.Data.CommandType.Text;
int result = cmd.ExecuteNonQuery();
if (result > 0)
return true;
else
return false;
}
}
return false;
}
catch (Exception ex)
{
Log.Unhandled(ex);
return false;
}
}
public void GenerateStat()
{
try
{
if (dbServiceDatoFra.Date != dbServiceDatoTil.Date && (dbServiceDatoTil - dbServiceDatoFra).Days >= 60)
{
favTat = new decimal[FormMain.Favoritter.Count];
for (int d = 0; d < FormMain.Favoritter.Count; d++)
{
DataTable dt = GetServiceData(dbServiceDatoTil, 60, FormMain.Favoritter[d]);
var obj = new ServiceStat();
MakeStat(dt, obj, FormMain.Favoritter[d]);
if (d == 0)
this.statLatest = obj;
SaveStat(obj, FormMain.Favoritter[d]);
dt.Dispose();
favTat[d] = obj.tat60;
}
}
else
Log.d("Service: Databasen oppfyller ikke krav for lagring av historikk.");
}
catch (Exception ex)
{
Log.Unhandled(ex);
}
}
private void MakeStat(DataTable dt, ServiceStat obj, string avd)
{
try
{
if (dt.Rows.Count == 0)
return;
DateTime firstdate = DateTime.Now;
DateTime lastdate = DateTime.Now;
firstdate = (DateTime)dt.Rows[0][1];
lastdate = (DateTime)dt.Rows[dt.Rows.Count - 1][1];
int pSize = dt.Rows.Count;
float totaltAktive = 0;
float totaltServicer = 0;
float totaltAktiveOver14 = 0;
float totaltAktiveOver21 = 0;
float totaltTat = 0;
float totaltTilarbeid = 0;
float totaltFerdig = 0;
float totalVenter = 0;
float totalOver14 = 0;
float totalOver21 = 0;
float totalOver30 = 0;
for (int d = 0; d < dt.Rows.Count; d++)
{
int I = Convert.ToInt32(dt.Rows[d][0]);
DateTime date = Convert.ToDateTime(dt.Rows[d][1]);
var store = (StorageService)dt.Rows[d][2];
var aktive = 0;
store.servicer = store.servicer.OrderBy(x => x.status).ToList();
for (int i = 0; i < store.servicer.Count; i++)
{
var sk = store.servicer[i].selgerkode;
var stat = store.servicer[i].status;
var iarbeid = store.servicer[i].Iarbeid;
var ferdig = store.servicer[i].Ferdig;
var utlevert = store.servicer[i].Utlevert;
var tat = store.servicer[i].tat;
var tilarbeid = store.servicer[i].tilarbeid;
totaltServicer++;
if (stat < 90)
aktive++;
else
{
totaltTat += tat;
totaltTilarbeid += tilarbeid;
totaltFerdig++;
if (tat > 14)
totalOver14++;
if (tat > 21)
totalOver21++;
if (tat > 30)
totalOver30++;
}
if (stat == 1)
totalVenter++;
}
if (dbServiceDatoTil == firstdate)
{
if ((dbServiceDatoTil - date).Days > 14)
totaltAktiveOver14 += aktive;
if ((dbServiceDatoTil - date).Days > 21)
totaltAktiveOver21 += aktive;
}
totaltAktive += aktive;
}
decimal tatAvg = 0;
decimal tilarbeidAvg = 0;
float over14Avg = 0;
float over21Avg = 0;
float over30Avg = 0;
if (totaltFerdig != 0)
{
tatAvg = Math.Round(Convert.ToDecimal(totaltTat / totaltFerdig), 2);
tilarbeidAvg = Math.Round(Convert.ToDecimal(totaltTilarbeid / totaltFerdig), 2);
over14Avg = totalOver14 / totaltFerdig;
over21Avg = totalOver21 / totaltFerdig;
over30Avg = totalOver30 / totaltFerdig;
}
float tat_ServiceFerdige = 0, tat_ServiceTotAktive = 0, tat_Totalt = 0, tat_Now = 0, tat_Prev = 0;
for (int d = dt.Rows.Count - 1; d >= 0; d--)
{
int I = Convert.ToInt32(dt.Rows[d][0]);
DateTime date = Convert.ToDateTime(dt.Rows[d][1]);
var store = (StorageService)dt.Rows[d][2];
store.servicer = store.servicer.OrderBy(x => x.status).ToList();
for (int i = 0; i < store.servicer.Count; i++)
{
var sk = store.servicer[i].selgerkode;
var stat = store.servicer[i].status;
var iarbeid = store.servicer[i].Iarbeid;
var ferdig = store.servicer[i].Ferdig;
var utlevert = store.servicer[i].Utlevert;
var tat = store.servicer[i].tat;
if (stat < 90)
tat_ServiceTotAktive++;
else
{
tat_Totalt += tat;
tat_ServiceFerdige++;
}
}
tat_Now = tat_Totalt / tat_ServiceFerdige;
tat_Prev = tat_Now;
}
if (dbServiceDatoTil == firstdate)
{
obj.avdeling = Convert.ToInt32(avd);
obj.tat60 = tatAvg;
obj.aktive = (int)totaltAktive;
obj.ferdig = (int)totaltFerdig;
obj.over14aktiv = (int)totaltAktiveOver14;
obj.over21aktiv = (int)totaltAktiveOver21;
obj.totalt = (int)totaltServicer;
obj.tilarbeid60 = tilarbeidAvg;
obj.oppdatert = dbServiceDatoTil;
obj.venter = (int)totalVenter;
obj.over14 = (int)totalOver14;
obj.over14prosent = over14Avg;
obj.over21 = (int)totalOver21;
obj.over21prosent = over21Avg;
obj.over30 = (int)totalOver30;
obj.over30prosent = over30Avg;
var percent14 = CalcPercent(totaltAktiveOver14, totaltAktive) * 100;
var percent21 = CalcPercent(totaltAktiveOver21, totaltAktive) * 100;
obj.over14aktivprosent = percent14;
obj.over21aktivprosent = percent21;
}
}
catch(Exception ex)
{
Log.Unhandled(ex);
}
}