C#改變查詢出來的值

標籤：

List<YXToolSystem.SQLStruct> col = new List<YXToolSystem.SQLStruct>();
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_NO", "編號"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_TypeNO", "問題類型編號"));
            col.Add(new YXToolSystem.SQLStruct(colsl("ServicesList_NO"), "排班編號"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_CNO", "客戶編碼"));
            col.Add(new YXToolSystem.SQLStruct(colsl("ServicesList_EPTNO"), "排班類型編號"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_ServicesNO", "服務費編號"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_CName", "客戶名稱"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_TypeName", "問題類型"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_Question", "問題"));
            col.Add(new YXToolSystem.SQLStruct("ServicesQuestion_Time", "售後次數"));

            col.Add(new YXToolSystem.SQLStruct(colsl("ServicesList_EName"), "技術員名稱"));
            ClassServicesSpeed cs = new ClassServicesSpeed();
            col.Add(new YXToolSystem.SQLStruct(cs.GetSeeSQL("isnull(" + colsl("ServicesList_Speed") + ",-1)"), "狀態"));
            col.Add(new YXToolSystem.SQLStruct("SUBSTRING(CONVERT(varchar(16)," + colsl("ServicesList_ExpectStart") + ", 25),6,11)", "預計開始時間"));
            col.Add(new YXToolSystem.SQLStruct("SUBSTRING(CONVERT(varchar(16)," + colsl("ServicesList_ExpectEnd") + ", 25),6,11)", "預計完成時間"));
            col.Add(new YXToolSystem.SQLStruct(colsl("ServicesList_ExpectEnd"), "endTime"));

            col.Add(new YXToolSystem.SQLStruct("(case isnull(" + colsl("ServicesList_IsFinish") + ",0) when 1 then ‘是‘ else ‘否‘ end )", "此單結束"));

            col.Add(new YXToolSystem.SQLStruct(cs.GetSeeSQL_ifserver("isnull(" + colsl("ServicesList_IsReturn") + ",0)"), "是否解決(技術員)"));
          

            col.Add(new YXToolSystem.SQLStruct("isnull(" + colsl("ServicesList_Speed") + ",-1)", "售後步驟"));

            string sql = YXToolSystem.SQLGet.Select(col, "ServicesQuestion") + " where ServicesQuestion_IsOK=‘0‘";
            switch (butIndex)
            {
                case 0:
                    sql += " order by 編號 asc";
                    break;
                case 1:
                    sql += " and isnull(" + colsl("ServicesList_Speed") + ",-1)=-1  order by 編號 asc";
                    break;
                case 2:
                    sql += " and isnull(" + colsl("ServicesList_Speed") + ",-1)<>-1 and isnull(" + colsl("ServicesList_IsFinish") + ",0)= 0  order by 編號 asc";
                    break;
                case 3:
                    sql += " and isnull(" + colsl("ServicesList_IsFinish") + ",0)=1 and isnull(" + colsl("ServicesList_IsSolve") + ",0)=0  order by 編號 asc";
                    break;
                case 4:
                    sql += " and isnull(" + colsl("ServicesList_IsSolve") + ",0)=1  order by 編號 asc";
                    break;
            }

namespace YXServicesSystem
{
    class ClassServices
    {
        public static int Expect_Minute = 2;
        public static int Expect_Hours = 3;
    }

    class ClassServicesSpeed
    {
        public string GetSeeSQL(string col)
        {
            string sql = " case " + col + "";
            sql += "  when 0 then ‘開始‘  ";
            sql += "  when 8 then ‘接收進入‘  ";
            sql += "  when 10 then ‘接收‘  ";
            sql += "  when 18 then ‘出發進入‘  ";
            sql += "  when 20 then ‘出發‘  ";
            sql += "  when 28 then ‘到達進入‘  ";
            sql += "  when 30 then ‘到達‘  ";
            sql += "  when 31 then ‘延時‘  ";
            sql += "  when 48 then ‘售後單填寫進入‘  ";
            sql += "  when 50 then ‘售後單填寫完成‘  ";
            sql += "  when 58 then ‘技術員簽字‘  ";
            sql += "  when 62 then ‘查看售後單‘  ";
            sql += "  when 64 then ‘客戶評分‘  ";
            sql += "  when 68 then ‘客戶簽字進入‘  ";
            sql += "  when 70 then ‘客戶簽字‘  ";
            sql += "  when 78 then ‘完成進入‘  ";
            sql += "  when 80 then ‘完成‘  ";
            sql += "  when 100 then ‘返回公司‘  ";
            sql += "  when 110 then ‘下班回家‘  ";
            sql += "  when 120 then ‘繼續其他售後‘  ";
            sql += "  when 158 then ‘返回公司到達進入‘  ";
            sql += "  when 160 then ‘返回公司到達‘  ";
            sql += "  when 200 then ‘回訪‘  ";
            sql += "  else ‘無記錄‘ end ";

            return sql;
        }
        public string GetSeeSQL_ifserver(string col)
        {
            string sql = " case " + col + "";
            sql += "  when 0 then ‘無記錄‘  ";
            sql += "  when 1 then ‘已解決‘  ";
            sql += "  when 2 then ‘未解決‘  ";
            sql += "  else ‘無記錄‘ end ";

            return sql;
        }
        /// <summary>
        /// 刻度尺背景圖片
        /// </summary>
        //public static void back_image(PictureBox p)
        //{
        //    p.Size = new Size(118, 10);
        //    p.BackgroundImage = YXServicesSystem.Properties.Resources.刻度;
        //}
    }

    /// <summary>
    /// 售後步驟值
    /// </summary>
    enum ServicesSpeed
    {
        開始 = 0,
        接收進入 = 8,
        接收 = 10,
        出發進入 = 18,
        出發 = 20,
        到達進入 = 28,
        到達 = 30,
        延時 = 31,//不修改Speed值
        售後單填寫進入 = 48,
        售後單填寫完成 = 50,
        技術員簽字 = 58,
        查看售後單 = 62,
        客戶評分 = 64,
        客戶簽字進入 = 68,
        客戶簽字 = 70,
        完成進入 = 78,
        完成 = 80,
        返回公司 = 100,
        下班回家 = 110,
        繼續其他售後 = 120,
        返回公司到達進入 = 158,
        返回公司到達 = 160,
        回訪 = 200,
    }
}

C#改變查詢出來的值