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