jeudi 30 juillet 2015

dry code from sql to view

I have seven locations and 14 statuses. I've hard-coded a lot of things and I want to make it DRY. I made everything into an int array. I tried to do some studying on enumerator but I wasn't sure on how exactly it'd fit.

At the moment, I need to show every location to count how many units with each status.

"SELECT
SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) AS A
,SUM(CASE WHEN status = 'B' THEN 1 ELSE 0 END) AS B
,SUM(CASE WHEN status = 'C' THEN 1 ELSE 0 END) AS C
,SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS D
,SUM(CASE WHEN status = 'E' THEN 1 ELSE 0 END) AS E
,SUM(CASE WHEN status = 'F' THEN 1 ELSE 0 END) AS F
,SUM(CASE WHEN status = 'G' THEN 1 ELSE 0 END) AS G...
FROM TABLE WHERE location1 = @location"
//..
                    int[] result = new int[15];
                    //..
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    foreach (DataRow dRow in ds.Tables[0].Rows)
                    {
                        result[0] = (int)dRow["A"];
                        result[1] = (int)dRow["B"];
                        result[2] = (int)dRow["C"];
                        result[3] = (int)dRow["D"];
                        result[4] = (int)dRow["E"];
                        //..
                    }
                    return result;

My Controller

    private IEquipmentDapper dapper = new EquipmentDapper();

    public ActionResult Index()
    {
        int[] AllLocationStatuses = dapper.CountAllStatuses();

        ViewBag.TotalA = AllLocationStatuses[0];
        ViewBag.TotalB = AllLocationStatuses[1];
        ViewBag.TotalC = AllLocationStatuses[2];
        ViewBag.TotalD = AllLocationStatuses[3];
        ViewBag.TotalE = AllLocationStatuses[4];
        //..
        int[] Location1Statuses = dapper.CountStatusesByLocation();

        ViewBag.Location1A = Location1Statuses[0];
        ViewBag.Location1B = Location1Statuses[1];
        ViewBag.Location1C = Location1Statuses[2];
        //..

And my view

    <td>Total</td>
    <td><span class="A"></span>@ViewBag.A A</td>
    <td><span class="B"></span>@ViewBag.B B</td>
    <td><span class="C"></span>@ViewBag.C C</td>
    <td><span class="D"></span>@ViewBag.D D</td>

Aucun commentaire:

Enregistrer un commentaire