コード例 #1
0
ファイル: RankingBudget.cs プロジェクト: tborgund/kgsa
        private DataTable MakeTableBudgetProduct(BudgetCategory cat, BudgetType product, BudgetValueType type)
        {
            try
            {
                DataTable dtWork = ReadyTableProductNew();

                sqlce = main.database.CallMonthTable(dtTil, main.appConfig.Avdeling);
                if (sqlce.Rows.Count == 0)
                    return dtWork;

                string strSel = ""; DataRow[] rowsSel;
                for (int i = 0; i < budgetInfo.selgere.Count; i++)
                    strSel += " OR Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'";
                strSel = strSel.Substring(4, strSel.Length - 4);
                rowsSel = sqlce.Select(strSel + " AND (Dato >= '" + dtFra.ToString("yyy-MM-dd") + "' AND Dato <= '" + dtTil.ToString("yyy-MM-dd") + "')");
                DataTable dtSel = rowsSel.Any() ? rowsSel.CopyToDataTable() : sqlce.Clone();

                object r;
                decimal omset_total = 0, inntjen_total = 0, omsetExMva_total = 0;
                int d = 0;
                if (cat == BudgetCategory.MDA)
                    d = 1;
                else if (cat == BudgetCategory.AudioVideo)
                    d = 2;
                else if (cat == BudgetCategory.SDA)
                    d = 3;
                else if (cat == BudgetCategory.Tele)
                    d = 4;
                else if (cat == BudgetCategory.Data)
                    d = 5;
                decimal target_value = 0;
                if (product == BudgetType.Acc)
                    target_value = budgetInfo.acc;
                else if (product == BudgetType.Finans)
                    target_value = budgetInfo.finans;
                else if (product == BudgetType.Rtgsa)
                    target_value = budgetInfo.rtgsa;
                else if (product == BudgetType.Strom)
                    target_value = budgetInfo.strom;
                else if (product == BudgetType.TA)
                    target_value = budgetInfo.ta;
                else if (product == BudgetType.Vinnprodukt)
                    target_value = budgetInfo.vinn;

                inntjen_total = Compute(dtSel, "Sum(Btokr)");
                omset_total = Compute(dtSel, "Sum(Salgspris)");
                omsetExMva_total = Compute(dtSel, "Sum(SalgsprisExMva)");

                DataRow dtRowAvd = dtWork.NewRow();
                dtRowAvd["Kategori"] = product;
                dtRowAvd["Selgerkode"] = "Totalt";

                dtRowAvd["Actual_inntjen"] = inntjen_total;
                dtRowAvd["Actual_omset"] = omset_total;
                dtRowAvd["Actual_omsetExMva"] = omsetExMva_total;
                if (omsetExMva_total != 0)
                    dtRowAvd["Actual_margin"] = Math.Round(inntjen_total / omsetExMva_total, 2);
                else
                    dtRowAvd["Actual_margin"] = 0;

                if (sqlce.Rows.Count > 0)
                {
                    decimal inntjen_prod = 0, antall_prod = 0, omset_prod = 0, hovedprod_antall = 0, vinnprodukt_poeng = 0;
                    KgsaBudgetProduct Actual = new KgsaBudgetProduct(product, type);
                    KgsaBudgetField actual_1 = new KgsaBudgetField();
                    KgsaBudgetField actual_2 = new KgsaBudgetField();
                    KgsaBudgetField actual_3 = new KgsaBudgetField();

                    KgsaBudgetProduct Target = new KgsaBudgetProduct(product, type);
                    KgsaBudgetField target_1 = new KgsaBudgetField();
                    KgsaBudgetField target_2 = new KgsaBudgetField();

                    KgsaBudgetProduct Difference = new KgsaBudgetProduct(product, type);
                    KgsaBudgetField difference_1 = new KgsaBudgetField();
                    KgsaBudgetField difference_2 = new KgsaBudgetField();

                    if (product == BudgetType.Vinnprodukt)
                    {
                        decimal antall = 0;
                        List<VinnproduktItem> items = main.vinnprodukt.GetList();
                        foreach (VinnproduktItem item in items)
                        {
                            inntjen_prod += Compute(dtSel, "Sum(Btokr)", "[Varekode] = '" + item.varekode + "'");
                            omset_prod += Compute(dtSel, "Sum(Salgspris)", "[Varekode] = '" + item.varekode + "'");
                            antall = Compute(dtSel, "Sum(Antall)", "[Varekode] = '" + item.varekode + "'");
                            antall_prod += antall;
                            vinnprodukt_poeng += item.poeng * antall;
                        }
                    }
                    else if (product == BudgetType.Acc)
                    {
                        int[] accessoriesGrpList = main.appConfig.GetAccessorieGroups(0);
                        foreach (int ac in accessoriesGrpList)
                        {
                            inntjen_prod += Compute(dtSel, "Sum(Btokr)", "[Varegruppe] = " + ac);
                            omset_prod += Compute(dtSel, "Sum(Salgspris)", "[Varegruppe] = " + ac);
                            antall_prod += Compute(dtSel, "Sum(Antall)", "[Varegruppe] = " + ac);
                        }
                    }
                    else if (product == BudgetType.Finans)
                    {
                        var rowf = dtSel.Select("[Varegruppe] = 961");
                        for (int f = 0; f < rowf.Length; f++)
                        {
                            var rows2 = sqlce.Select("[Bilagsnr] = " + rowf[f]["Bilagsnr"]);
                            DataTable dtFinans = rows2.Any() ? rows2.CopyToDataTable() : sqlce.Clone();
                            dtFinans.DefaultView.Sort = "Salgspris DESC";
                            int gruppe = Convert.ToInt32(dtFinans.Rows[0]["Varegruppe"].ToString().Substring(0, 1));
                            if (gruppe == d || d == 0)
                            {
                                r = sqlce.Compute("Sum(Btokr)", "[Varegruppe] = 961 AND [Bilagsnr] = " + dtFinans.Rows[0]["Bilagsnr"].ToString());
                                if (!DBNull.Value.Equals(r))
                                    inntjen_prod += Convert.ToDecimal(r);

                                r = sqlce.Compute("Sum(Antall)", "[Varegruppe] = 961 AND [Bilagsnr] = " + dtFinans.Rows[0]["Bilagsnr"].ToString());
                                if (!DBNull.Value.Equals(r))
                                    antall_prod += Convert.ToInt32(r);
                            }
                        }
                    }
                    else if (product == BudgetType.Rtgsa)
                    {
                        r = dtSel.Compute("Sum(Antall)", "([Varegruppe]=531 OR [Varegruppe]=533 OR [Varegruppe]=534 OR [Varegruppe]=224 OR [Varegruppe]=431)");
                        if (!DBNull.Value.Equals(r))
                            hovedprod_antall = Convert.ToInt32(r);

                        foreach (var varekode in varekoderAlle)
                        {
                            r = dtSel.Compute("Sum(Btokr)", "[Varekode]='" + varekode.kode + "'");
                            if (!DBNull.Value.Equals(r))
                                inntjen_prod += Convert.ToDecimal(r);

                            r = dtSel.Compute("Sum(Salgspris)", "[Varekode]='" + varekode.kode + "'");
                            if (!DBNull.Value.Equals(r))
                                omset_prod += Convert.ToDecimal(r);

                            if (varekode.synlig)
                            {
                                r = dtSel.Compute("Sum(Antall)", "[Varekode]='" + varekode.kode + "'");
                                if (!DBNull.Value.Equals(r))
                                    antall_prod += Convert.ToInt32(r);
                            }
                        }
                    }
                    else if (product == BudgetType.Strom)
                    {
                        r = dtSel.Compute("Sum(Btokr)", "[Varekode] LIKE 'ELSTROM*' OR [Varekode] LIKE 'ELRABATT*'");
                        if (!DBNull.Value.Equals(r))
                            inntjen_prod = Convert.ToDecimal(r);

                        r = dtSel.Compute("Sum(Salgspris)", "[Varekode] LIKE 'ELSTROM*' OR [Varekode] LIKE 'ELRABATT*'");
                        if (!DBNull.Value.Equals(r))
                            omset_prod = Convert.ToDecimal(r);

                        r = dtSel.Compute("Sum(Antall)", "[Varekode] LIKE 'ELSTROM*'");
                        if (!DBNull.Value.Equals(r))
                            antall_prod = Convert.ToInt32(r);
                    }
                    else if (product == BudgetType.TA)
                    {
                        r = dtSel.Compute("Sum(Btokr)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*'");
                        if (!DBNull.Value.Equals(r))
                            inntjen_prod = Convert.ToDecimal(r);

                        r = dtSel.Compute("Sum(Salgspris)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*'");
                        if (!DBNull.Value.Equals(r))
                            omset_prod = Convert.ToDecimal(r);

                        r = dtSel.Compute("Sum(Antall)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*'");
                        if (!DBNull.Value.Equals(r))
                            antall_prod = Convert.ToInt32(r);
                    }

                    if (type == BudgetValueType.Poeng)
                    {
                        actual_1.value = vinnprodukt_poeng;
                        actual_1.type = BudgetValueType.Poeng;
                        actual_2.value = antall_prod;
                        actual_2.type = BudgetValueType.Antall;
                        actual_3.value = inntjen_prod;
                        actual_3.type = BudgetValueType.Inntjening;

                        target_1.value = target_value * budgetInfo.timeElapsedCoefficient;
                        target_1.type = BudgetValueType.Poeng;
                        target_2.value = (target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed;
                        target_2.type = BudgetValueType.AntallPerDag;

                        difference_1.value = actual_1.value - target_1.value;
                        difference_1.type = BudgetValueType.Poeng;
                        difference_2.value = ((antall_prod * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) - target_2.value;
                        difference_2.type = BudgetValueType.AntallPerDag;
                    }
                    else if (type == BudgetValueType.Hitrate)
                    {
                        if (hovedprod_antall != 0)
                            actual_1.value = antall_prod / hovedprod_antall;
                        else
                            actual_1.value = 0;
                        actual_1.type = BudgetValueType.Hitrate;
                        actual_2.value = antall_prod;
                        actual_2.type = BudgetValueType.Antall;
                        actual_3.value = inntjen_prod;
                        actual_3.type = BudgetValueType.Inntjening;

                        target_1.value = target_value;
                        target_1.type = BudgetValueType.Hitrate;
                        target_2.value = (hovedprod_antall * target_value) / budgetInfo.daysElapsed;
                        target_2.type = BudgetValueType.AntallPerDag;

                        difference_1.value = actual_1.value - target_1.value;
                        difference_1.type = BudgetValueType.Hitrate;
                        difference_2.value = ((hovedprod_antall * actual_1.value) / budgetInfo.daysElapsed) - target_2.value;
                        difference_2.type = BudgetValueType.AntallPerDag;
                    }
                    else if (type == BudgetValueType.Antall)
                    {
                        actual_1.value = antall_prod;
                        actual_1.type = BudgetValueType.Antall;
                        actual_2.value = inntjen_prod;
                        actual_2.type = BudgetValueType.Inntjening;
                        if (inntjen_total != 0)
                            actual_3.value = inntjen_prod / inntjen_total;
                        else
                            actual_3.value = 0;
                        actual_3.type = BudgetValueType.SoM;

                        target_1.value = target_value * budgetInfo.timeElapsedCoefficient;
                        target_1.type = BudgetValueType.Antall;
                        target_2.value = (target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed;
                        target_2.type = BudgetValueType.AntallPerDag;

                        difference_1.value = actual_1.value - target_1.value;
                        difference_1.type = BudgetValueType.Antall;
                        difference_2.value = ((antall_prod * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) - target_2.value;
                        difference_2.type = BudgetValueType.AntallPerDag;
                    }
                    if (type == BudgetValueType.Inntjening)
                    {
                        actual_1.value = antall_prod;
                        actual_1.type = BudgetValueType.Antall;
                        actual_2.value = inntjen_prod;
                        actual_2.type = BudgetValueType.Inntjening;
                        if (inntjen_total != 0)
                            actual_3.value = inntjen_prod / inntjen_total;
                        else
                            actual_3.value = 0;
                        actual_3.type = BudgetValueType.SoM;

                        target_1.value = target_value * budgetInfo.timeElapsedCoefficient;
                        target_1.type = BudgetValueType.Inntjening;
                        target_2.value = (target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed;
                        target_2.type = BudgetValueType.InntjeningPerDag;

                        difference_1.value = actual_2.value - target_1.value;
                        difference_1.type = BudgetValueType.Inntjening;
                        difference_2.value = (inntjen_prod / budgetInfo.daysElapsed) - target_2.value;
                        difference_2.type = BudgetValueType.InntjeningPerDag;
                    }
                    if (type == BudgetValueType.Omsetning)
                    {
                        actual_1.value = antall_prod;
                        actual_1.type = BudgetValueType.Antall;
                        actual_2.value = omset_prod;
                        actual_2.type = BudgetValueType.Omsetning;
                        if (omset_prod != 0)
                            actual_3.value = omset_total / omset_prod;
                        else
                            actual_3.value = 0;
                        actual_3.type = BudgetValueType.SoB;

                        target_1.value = target_value * budgetInfo.timeElapsedCoefficient;
                        target_1.type = BudgetValueType.Omsetning;
                        target_2.value = (target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed;
                        target_2.type = BudgetValueType.OmsetningPerDag;

                        difference_1.value = actual_2.value - target_1.value;
                        difference_1.type = BudgetValueType.Omsetning;
                        difference_2.value = (omset_prod / budgetInfo.daysElapsed) - target_2.value;
                        difference_2.type = BudgetValueType.OmsetningPerDag;
                    }
                    if (type == BudgetValueType.SoB)
                    {
                        actual_1.value = antall_prod;
                        actual_1.type = BudgetValueType.Antall;
                        actual_2.value = omset_prod;
                        actual_2.type = BudgetValueType.Omsetning;
                        if (omset_total != 0)
                            actual_3.value = omset_prod / omset_total;
                        else
                            actual_3.value = 0;
                        actual_3.type = BudgetValueType.SoB;

                        target_1.value = target_value;
                        target_1.type = BudgetValueType.SoB;
                        target_2.value = omset_total * target_value;
                        target_2.type = BudgetValueType.Omsetning;

                        difference_1.value = actual_3.value - target_1.value;
                        difference_1.type = BudgetValueType.SoB;
                        difference_2.value = actual_2.value - target_2.value;
                        difference_2.type = BudgetValueType.Omsetning;
                    }
                    if (type == BudgetValueType.SoM)
                    {
                        actual_1.value = antall_prod;
                        actual_1.type = BudgetValueType.Antall;
                        actual_2.value = inntjen_prod;
                        actual_2.type = BudgetValueType.Inntjening;
                        if (inntjen_total != 0)
                            actual_3.value = inntjen_prod / inntjen_total;
                        else
                            actual_3.value = 0;
                        actual_3.type = BudgetValueType.SoM;

                        target_1.value = target_value;
                        target_1.type = BudgetValueType.SoM;
                        target_2.value = target_1.value * inntjen_total;
                        target_2.type = BudgetValueType.Inntjening;

                        difference_1.value = actual_3.value - target_1.value;
                        difference_1.type = BudgetValueType.SoM;
                        difference_2.value = actual_2.value - target_2.value;
                        difference_2.type = BudgetValueType.Inntjening;
                    }

                    Actual.fields.Add(actual_1); // kolonne 1 av 3
                    Actual.fields.Add(actual_2); // kolonne 2 av 3
                    Actual.fields.Add(actual_3); // kolonne 3 av 3

                    Target.fields.Add(target_1); // kolonne 1 av 3
                    Target.fields.Add(target_2); // kolonne 2 av 3

                    Difference.fields.Add(difference_1); // kolonne 1 av 3
                    Difference.fields.Add(difference_2); // kolonne 2 av 3

                    dtRowAvd["Field1"] = PrepFields(Actual, false, true);
                    dtRowAvd["Field2"] = PrepFields(Target);
                    dtRowAvd["Field3"] = PrepFields(Difference, true);

                    dtRowAvd["Sort_value"] = -9999999;
                }

                budgetInfo.chartdata = new List<BudgetChartData>() { };

                for (int i = 0; i < budgetInfo.selgere.Count; i++)
                {
                    decimal omset_sel = 0, inntjen_sel = 0, omsetExMva_sel = 0;

                    inntjen_sel = Compute(sqlce, "Sum(Btokr)", "Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                    omset_sel = Compute(sqlce, "Sum(Salgspris)", "Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                    omsetExMva_sel = Compute(sqlce, "Sum(SalgsprisExMva)", "Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");

                    DataRow dtRowSel = dtWork.NewRow();
                    dtRowSel["Kategori"] = product;
                    dtRowSel["Selgerkode"] = budgetInfo.selgere[i].selgerkode;

                    dtRowSel["Actual_inntjen"] = inntjen_sel;
                    dtRowSel["Actual_omset"] = omset_sel;
                    dtRowSel["Actual_omsetExMva"] = omsetExMva_sel;
                    if (omsetExMva_sel != 0)
                        dtRowSel["Actual_margin"] = Math.Round(inntjen_sel / omsetExMva_sel, 2);
                    else
                        dtRowSel["Actual_margin"] = 0;

                    if (sqlce.Rows.Count > 0)
                    {
                        decimal inntjen_prod = 0, antall_prod = 0, omset_prod = 0, hovedprod_antall = 0, vinnprodukt_poeng = 0;
                        KgsaBudgetProduct Actual = new KgsaBudgetProduct(product, type);
                        KgsaBudgetField actual_1 = new KgsaBudgetField();
                        KgsaBudgetField actual_2 = new KgsaBudgetField();
                        KgsaBudgetField actual_3 = new KgsaBudgetField();
                        KgsaBudgetProduct Target = new KgsaBudgetProduct(product, type);
                        KgsaBudgetField target_1 = new KgsaBudgetField();
                        KgsaBudgetField target_2 = new KgsaBudgetField();
                        KgsaBudgetProduct Difference = new KgsaBudgetProduct(product, type);
                        KgsaBudgetField diff_1 = new KgsaBudgetField();
                        KgsaBudgetField diff_2 = new KgsaBudgetField();

                        if (product == BudgetType.Vinnprodukt)
                        {
                            decimal antall = 0;
                            List<VinnproduktItem> items = main.vinnprodukt.GetList();
                            foreach (VinnproduktItem item in items)
                            {
                                inntjen_prod += Compute(sqlce, "Sum(Btokr)", "[Varekode] = '" + item.varekode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                omset_prod += Compute(sqlce, "Sum(Salgspris)", "[Varekode] = '" + item.varekode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                antall = Compute(sqlce, "Sum(Antall)", "[Varekode] = '" + item.varekode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                antall_prod += antall;
                                vinnprodukt_poeng += item.poeng * antall;
                            }
                        }
                        else if (product == BudgetType.Acc)
                        {
                            int[] accessoriesGrpList = main.appConfig.GetAccessorieGroups(0);
                            foreach (int ac in accessoriesGrpList)
                            {
                                r = sqlce.Compute("Sum(Antall)", "[Varegruppe] = " + ac + " AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                if (!DBNull.Value.Equals(r))
                                    antall_prod += Convert.ToInt32(r);

                                r = sqlce.Compute("Sum(Btokr)", "[Varegruppe] = " + ac + " AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                if (!DBNull.Value.Equals(r))
                                    inntjen_prod += Convert.ToDecimal(r);

                                r = sqlce.Compute("Sum(Salgspris)", "[Varegruppe] = " + ac + " AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                if (!DBNull.Value.Equals(r))
                                    omset_prod += Convert.ToDecimal(r);
                            }
                        }
                        else if (product == BudgetType.Finans)
                        {
                            var rowf = sqlce.Select("[Varegruppe] = 961 AND [Selgerkode] = '" + budgetInfo.selgere[i].selgerkode + "'");
                            for (int f = 0; f < rowf.Length; f++)
                            {
                                var rows2 = sqlce.Select("[Bilagsnr] = " + rowf[f]["Bilagsnr"]);
                                DataTable dtFinans = rows2.Any() ? rows2.CopyToDataTable() : sqlce.Clone();
                                dtFinans.DefaultView.Sort = "Salgspris DESC";
                                int gruppe = Convert.ToInt32(dtFinans.Rows[0]["Varegruppe"].ToString().Substring(0, 1));
                                if (gruppe == d || d == 0)
                                {
                                    r = sqlce.Compute("Sum(Btokr)", "[Varegruppe] = 961 AND [Selgerkode] = '" + budgetInfo.selgere[i].selgerkode + "' AND [Bilagsnr] = " + dtFinans.Rows[0]["Bilagsnr"].ToString());
                                    if (!DBNull.Value.Equals(r))
                                        inntjen_prod += Convert.ToDecimal(r);

                                    r = sqlce.Compute("Sum(Antall)", "[Varegruppe] = 961 AND [Selgerkode] = '" + budgetInfo.selgere[i].selgerkode + "' AND [Bilagsnr] = " + dtFinans.Rows[0]["Bilagsnr"].ToString());
                                    if (!DBNull.Value.Equals(r))
                                        antall_prod += Convert.ToInt32(r);
                                }
                            }
                        }
                        else if (product == BudgetType.Rtgsa)
                        {
                            r = sqlce.Compute("Sum(Antall)", "([Varegruppe]=531 OR [Varegruppe]=533 OR [Varegruppe]=534 OR [Varegruppe]=224 OR [Varegruppe]=431) AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                hovedprod_antall = Convert.ToInt32(r);

                            foreach (var varekode in varekoderAlle)
                            {
                                r = sqlce.Compute("Sum(Salgspris)", "[Varekode]='" + varekode.kode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                if (!DBNull.Value.Equals(r))
                                    omset_prod += Convert.ToDecimal(r);

                                r = sqlce.Compute("Sum(Btokr)", "[Varekode]='" + varekode.kode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                if (!DBNull.Value.Equals(r))
                                    inntjen_prod += Convert.ToDecimal(r);

                                if (varekode.synlig)
                                {
                                    r = sqlce.Compute("Sum(Antall)", "[Varekode]='" + varekode.kode + "' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                                    if (!DBNull.Value.Equals(r))
                                        antall_prod += Convert.ToInt32(r);
                                }
                            }
                        }
                        else if (product == BudgetType.Strom)
                        {
                            r = sqlce.Compute("Sum(Btokr)", "([Varekode] LIKE 'ELSTROM*' OR [Varekode] LIKE 'ELRABATT*') AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                inntjen_prod = Convert.ToDecimal(r);

                            r = sqlce.Compute("Sum(Salgspris)", "([Varekode] LIKE 'ELSTROM*' OR [Varekode] LIKE 'ELRABATT*') AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                omset_prod = Convert.ToDecimal(r);

                            r = sqlce.Compute("Sum(Antall)", "[Varekode] LIKE 'ELSTROM*' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                antall_prod = Convert.ToInt32(r);
                        }
                        else if (product == BudgetType.TA)
                        {
                            r = sqlce.Compute("Sum(Btokr)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                inntjen_prod = Convert.ToDecimal(r);

                            r = sqlce.Compute("Sum(Salgspris)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                omset_prod = Convert.ToDecimal(r);

                            r = sqlce.Compute("Sum(Antall)", "[Varegruppe] % 100 = 83 AND [Varekode] LIKE 'MOD*' AND Selgerkode = '" + budgetInfo.selgere[i].selgerkode + "'");
                            if (!DBNull.Value.Equals(r))
                                antall_prod = Convert.ToInt32(r);
                        }

                        if (type == BudgetValueType.Poeng)
                        {
                            actual_1.value = vinnprodukt_poeng;
                            actual_1.type = BudgetValueType.Poeng;
                            actual_2.value = antall_prod;
                            actual_2.type = BudgetValueType.Antall;
                            actual_3.value = inntjen_prod;
                            actual_3.type = BudgetValueType.Inntjening;

                            target_1.value = target_value * budgetInfo.timeElapsedCoefficient * budgetInfo.selgere[i].weight;
                            target_1.type = BudgetValueType.Poeng;
                            target_2.value = ((target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) * budgetInfo.selgere[i].weight;
                            target_2.type = BudgetValueType.AntallPerDag;

                            diff_1.value = actual_1.value - target_1.value;
                            diff_1.type = BudgetValueType.Poeng;
                            diff_2.value = ((antall_prod * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) - target_2.value;
                            diff_2.type = BudgetValueType.AntallPerDag;
                        }
                        else  if (type == BudgetValueType.Hitrate)
                        {
                            if (hovedprod_antall != 0)
                                actual_1.value = antall_prod / hovedprod_antall;
                            else
                                actual_1.value = 0;
                            actual_1.type = BudgetValueType.Hitrate;
                            actual_2.value = antall_prod;
                            actual_2.type = BudgetValueType.Antall;
                            actual_3.value = inntjen_prod;
                            actual_3.type = BudgetValueType.Inntjening;

                            target_1.value = target_value;
                            target_1.type = BudgetValueType.Hitrate;
                            target_2.value = (hovedprod_antall * target_value) / budgetInfo.daysElapsed;
                            target_2.type = BudgetValueType.AntallPerDag;

                            diff_1.value = actual_1.value - target_1.value;
                            diff_1.type = BudgetValueType.Hitrate;
                            diff_2.value = ((hovedprod_antall * actual_1.value) / budgetInfo.daysElapsed) - target_2.value;
                            diff_2.type = BudgetValueType.AntallPerDag;
                        }
                        else if (type == BudgetValueType.Antall)
                        {
                            actual_1.value = antall_prod;
                            actual_1.type = BudgetValueType.Antall;
                            actual_2.value = inntjen_prod;
                            actual_2.type = BudgetValueType.Inntjening;
                            if (inntjen_sel != 0)
                                actual_3.value = inntjen_prod / inntjen_sel;
                            else
                                actual_3.value = 0;
                            actual_3.type = BudgetValueType.SoM;

                            target_1.value = target_value * budgetInfo.timeElapsedCoefficient * budgetInfo.selgere[i].weight;
                            target_1.type = BudgetValueType.Antall;
                            target_2.value = ((target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) * budgetInfo.selgere[i].weight;
                            target_2.type = BudgetValueType.AntallPerDag;

                            diff_1.value = actual_1.value - target_1.value;
                            diff_1.type = BudgetValueType.Antall;
                            diff_2.value = antall_prod / budgetInfo.daysElapsed - target_2.value;
                            diff_2.type = BudgetValueType.AntallPerDag;
                        }
                        else if (type == BudgetValueType.Inntjening)
                        {
                            actual_1.value = antall_prod;
                            actual_1.type = BudgetValueType.Antall;
                            actual_2.value = inntjen_prod;
                            actual_2.type = BudgetValueType.Inntjening;
                            if (inntjen_sel != 0)
                                actual_3.value = inntjen_prod / inntjen_sel;
                            else
                                actual_3.value = 0;
                            actual_3.type = BudgetValueType.SoM;

                            target_1.value = target_value * budgetInfo.timeElapsedCoefficient * budgetInfo.selgere[i].weight;
                            target_1.type = BudgetValueType.Inntjening;
                            target_2.value = ((target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) * budgetInfo.selgere[i].weight;
                            target_2.type = BudgetValueType.InntjeningPerDag;

                            diff_1.value = actual_2.value - target_1.value;
                            diff_1.type = BudgetValueType.Inntjening;
                            diff_2.value = inntjen_prod / budgetInfo.daysElapsed - target_2.value;
                            diff_2.type = BudgetValueType.InntjeningPerDag;
                        }
                        else if (type == BudgetValueType.Omsetning)
                        {
                            actual_1.value = antall_prod;
                            actual_1.type = BudgetValueType.Antall;
                            actual_2.value = omset_prod;
                            actual_2.type = BudgetValueType.Omsetning;
                            if (omset_sel != 0)
                                actual_3.value = omset_prod / omset_sel;
                            else
                                actual_3.value = 0;
                            actual_3.type = BudgetValueType.SoB;

                            target_1.value = target_value * budgetInfo.timeElapsedCoefficient * budgetInfo.selgere[i].weight;
                            target_1.type = BudgetValueType.Omsetning;
                            target_2.value = ((target_value * budgetInfo.timeElapsedCoefficient) / budgetInfo.daysElapsed) * budgetInfo.selgere[i].weight;
                            target_2.type = BudgetValueType.OmsetningPerDag;

                            diff_1.value = actual_2.value - target_1.value;
                            diff_1.type = BudgetValueType.Omsetning;
                            diff_2.value = omset_prod / budgetInfo.daysElapsed - target_2.value;
                            diff_2.type = BudgetValueType.OmsetningPerDag;
                        }
                        else if (type == BudgetValueType.SoB)
                        {
                            actual_1.value = antall_prod;
                            actual_1.type = BudgetValueType.Antall;
                            actual_2.value = omset_prod;
                            actual_2.type = BudgetValueType.Omsetning;
                            if (omset_sel != 0)
                                actual_3.value = omset_prod / omset_sel;
                            else
                                actual_3.value = 0;
                            actual_3.type = BudgetValueType.SoB;

                            target_1.value = target_value;
                            target_1.type = BudgetValueType.SoB;
                            target_2.value = omset_sel * target_value;
                            target_2.type = BudgetValueType.Omsetning;

                            diff_1.value = actual_3.value - target_1.value;
                            diff_1.type = BudgetValueType.SoB;
                            diff_2.value = actual_2.value - target_2.value;
                            diff_2.type = BudgetValueType.Omsetning;
                        }
                        else if (type == BudgetValueType.SoM)
                        {
                            actual_1.value = antall_prod;
                            actual_1.type = BudgetValueType.Antall;
                            actual_2.value = inntjen_prod;
                            actual_2.type = BudgetValueType.Inntjening;
                            if (inntjen_sel != 0)
                                actual_3.value = inntjen_prod / inntjen_sel;
                            else
                                actual_3.value = 0;
                            actual_3.type = BudgetValueType.SoM;

                            target_1.value = target_value;
                            target_1.type = BudgetValueType.SoM;
                            target_2.value = inntjen_sel * target_value;
                            target_2.type = BudgetValueType.Inntjening;

                            diff_1.value = actual_3.value - target_1.value;
                            diff_1.type = BudgetValueType.SoM;
                            diff_2.value = actual_2.value - target_2.value;
                            diff_2.type = BudgetValueType.Inntjening;
                        }
                        Actual.fields.Add(actual_1); // kolonne 1 av 3
                        Actual.fields.Add(actual_2); // kolonne 2 av 3
                        Actual.fields.Add(actual_3); // kolonne 3 av 3

                        Target.fields.Add(target_1); // kolonne 1 av 2
                        Target.fields.Add(target_2); // kolonne 2 av 2

                        Difference.fields.Add(diff_1); // kolonne 1 av 2
                        Difference.fields.Add(diff_2); // kolonne 2 av 2

                        dtRowSel["Field1"] = PrepFields(Actual, false, true);
                        dtRowSel["Field2"] = PrepFields(Target);
                        dtRowSel["Field3"] = PrepFields(Difference, true);

                        if (type == BudgetValueType.Antall || type == BudgetValueType.Poeng)
                        {
                            dtRowSel["Sort_value"] = actual_1.value;
                            budgetInfo.chartdata.Add(new BudgetChartData(budgetInfo.selgere[i].selgerkode, target_1.value, actual_1.value));
                        }
                        else if (type == BudgetValueType.Inntjening || type == BudgetValueType.Omsetning)
                        {
                            dtRowSel["Sort_value"] = actual_2.value;
                            budgetInfo.chartdata.Add(new BudgetChartData(budgetInfo.selgere[i].selgerkode, target_1.value, actual_2.value));
                        }
                        else if (type == BudgetValueType.SoM || type == BudgetValueType.SoB)
                        {
                            dtRowSel["Sort_value"] = actual_3.value;
                            budgetInfo.chartdata.Add(new BudgetChartData(budgetInfo.selgere[i].selgerkode, target_1.value, actual_3.value));
                        }
                        else if (type == BudgetValueType.Hitrate)
                        {
                            dtRowSel["Sort_value"] = actual_1.value;
                            budgetInfo.chartdata.Add(new BudgetChartData(budgetInfo.selgere[i].selgerkode, target_1.value, actual_1.value));
                        }
                    }
                    dtWork.Rows.Add(dtRowSel);
                }

                dtWork.Rows.Add(dtRowAvd);
                DataView dv = dtWork.DefaultView;
                dv.Sort = "Sort_value desc";

                budgetInfo.chartdata = budgetInfo.chartdata.OrderByDescending(x => x.actual).ToList();

                return dv.ToTable();
            }
            catch (Exception ex)
            {
                Log.Unhandled(ex);
                return null;
            }
        }
コード例 #2
0
ファイル: RankingBudget.cs プロジェクト: tborgund/kgsa
        private KgsaBudgetProduct PrepFields(KgsaBudgetProduct obj, bool positiveIsGreen = false, bool markMainFiel = false)
        {
            var mainType = obj.mainType;
            foreach(KgsaBudgetField field in obj.fields)
            {
                if (field.type == BudgetValueType.Poeng)
                {
                    field.value = Math.Round(field.value, 0);
                    field.header = "Poeng";
                    field.suffix = "";
                    field.style = "numbers-small";
                    field.sorter = "digit";
                    field.filter = "#,##0";
                }
                else if (field.type == BudgetValueType.Antall)
                {
                    field.value = Math.Round(field.value, 0);
                    field.header = "Antall";
                    field.suffix = "";
                    field.style = "numbers-small";
                    field.sorter = "digit";
                    field.filter = "#,##0";
                }
                else if (field.type == BudgetValueType.AntallPerDag)
                {
                    field.value = Math.Round(field.value, 1);
                    field.header = "Antall pr.dag";
                    field.suffix = " stk /dag";
                    field.style = "numbers-small";
                    field.sorter = "digit";
                    field.filter = "";
                }
                else if (field.type == BudgetValueType.Empty)
                {
                    field.header = "";
                    field.suffix = "";
                    field.style = "numbers-gen";
                    field.sorter = "digit";
                    field.filter = "";
                }
                else if (field.type == BudgetValueType.Hitrate)
                {
                    field.value = Math.Round(field.value * 100, 2);
                    field.header = "Hitrate";
                    field.suffix = " %";
                    field.style = "numbers-percent";
                    field.sorter = "procent";
                    field.filter = "";
                }
                else if (field.type == BudgetValueType.Inntjening)
                {
                    field.value = Math.Round(field.value, 0);
                    field.header = "Inntjen.";
                    field.suffix = "";
                    field.style = "numbers-gen";
                    field.sorter = "digit";
                    field.filter = "#,##0";
                }
                else if (field.type == BudgetValueType.InntjeningPerDag)
                {
                    field.value = Math.Round(field.value, 2);
                    field.header = "Inntjen. pr.dag";
                    field.suffix = " kr /dag";
                    field.style = "numbers-gen";
                    field.sorter = "digit";
                    field.filter = "#,##0.00";
                }
                else if (field.type == BudgetValueType.Omsetning)
                {
                    field.value = Math.Round(field.value, 0);
                    field.header = "Omset.";
                    field.suffix = "";
                    field.style = "numbers-gen";
                    field.sorter = "digit";
                    field.filter = "#,##0.00";
                }
                else if (field.type == BudgetValueType.OmsetningPerDag)
                {
                    field.value = Math.Round(field.value, 2);
                    field.header = "Omset. pr.dag";
                    field.suffix = " kr /dag";
                    field.style = "numbers-gen";
                    field.sorter = "digit";
                    field.filter = "";
                }
                else if (field.type == BudgetValueType.SoB)
                {
                    field.value = Math.Round(field.value * 100, 2);
                    field.header = "SoB";
                    field.suffix = " %";
                    field.style = "numbers-percent";
                    field.sorter = "procent";
                    field.filter = "";
                }
                else if (field.type == BudgetValueType.SoM)
                {
                    field.value = Math.Round(field.value * 100, 2);
                    field.header = "SoM";
                    field.suffix = " %";
                    field.style = "numbers-percent";
                    field.sorter = "procent";
                    field.filter = "";
                }

                if (mainType == field.type && markMainFiel)
                {
                    if (obj.product == BudgetType.Vinnprodukt)
                        field.style = "numbers-gen";
                    else if (obj.product == BudgetType.Acc)
                        field.style = "numbers-finans";
                    else if (obj.product == BudgetType.Finans)
                        field.style = "numbers-finans";
                    else if (obj.product == BudgetType.Rtgsa)
                        field.style = "numbers-service";
                    else if (obj.product == BudgetType.Strom)
                        field.style = "numbers-strom";
                    else if (obj.product == BudgetType.TA)
                        field.style = "numbers-moderna";
                    else if (obj.product == BudgetType.Inntjening)
                        field.style = "numbers-gen";
                    else if (obj.product == BudgetType.Omsetning)
                        field.style = "numbers-gen";
                }

                if (field.value < 0)
                    field.text = "<span style='color:red'>" + field.value.ToString(field.filter) + field.suffix + "</span>";
                else if (field.value > 0)
                {
                    if (positiveIsGreen)
                        field.text = "<span style='color:green'>+" + field.value.ToString(field.filter) + field.suffix + "</span>";
                    else
                        field.text = field.value.ToString(field.filter) + field.suffix;
                }
                else
                    field.text = main.appConfig.visningNull;
            }

            return obj;
        }