lyfz_repos/铂金版源码/LYFZ.Software.MainBusiness/DecisionAssistant/AnalyzedScenerys.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LYFZ.Software.MainBusiness.DecisionAssistant
{
    class AnalyzedScenerys: LYFZ.Software.UI.DecisionAssistant.AnalyzedScenerys
    {
        #region 变量
        public string strSelectYear = "";
        #endregion

        #region 系统函数;
        public AnalyzedScenerys()
        {
            // 查询函数;
            this.btnQuery.Click += BtnQuery_Click;
            // 加载窗口;
            this.Load += AnalyzedScenerys_Load;
            // 显示窗口;
            this.Shown += AnalyzedScenerys_Shown;
        }

        private void AnalyzedScenerys_Shown(object sender, EventArgs e)
        {
            //throw new NotImplementedException();
        }

        private void AnalyzedScenerys_Load(object sender, EventArgs e)
        {
            this.dt_end.Value = DateTime.Now;
            this.dt_start.Value = DateTime.Now.AddMonths(-1);
        }

        private void BtnQuery_Click(object sender, EventArgs e)
        {
            System.Data.DataSet dt = GetScenerysData(this.dt_start.Value, this.dt_end.Value);
            if ( dt != null && dt.Tables.Count > 0 )
            {
                dgv.DataSource = dt.Tables[0];
            }
        }
        #endregion

        #region 自定义函数;
        public System.Data.DataSet GetScenerysData(System.DateTime startDate, System.DateTime endDate)
        {
           /* 调用存储过程;
             System.Data.SqlClient.SqlParameter[] parameters = {
                    new System.Data.SqlClient.SqlParameter("@startDate", System.Data.SqlDbType.DateTime),
                    new System.Data.SqlClient.SqlParameter("@endDate", System.Data.SqlDbType.DateTime)
                    };

            parameters[0].Value = startDate;
            parameters[1].Value = endDate;
            return LYFZ.Helper.SQLHelper.RunProcedure("proc_getSceneryUsage", parameters, "sceneryUsage");
            */
            string strQuery = "with t as(" +
                "select top 100 percent " +
                "Ordpg_Sights" +
                ",count(*) as 使用总数" +
                ",count(case Ordpg_PhotographyStatus when 1 then '已拍照'end) 已拍照" +
                ",count(case Ordpg_PhotographyStatus when 2 then '拍照中'end) 拍照中" +
                ",count(case Ordpg_PhotographyStatus when 0 then '未拍照'end) 未拍照" +
                " from tb_ErpOrdersPhotography Left Join tb_ErpOrder On tb_ErpOrdersPhotography.Ordpg_Number = tb_ErpOrder.Ord_Number" +
                " where tb_ErpOrder.Ord_CreateDateTime >= '"+ startDate.ToString("yyyy-MM-dd 00:00:00") +"' and tb_ErpOrder.Ord_CreateDateTime <= '" + endDate.ToString("yyyy-MM-dd 23:59:59") + "'" +
                " group by Ordpg_Sights order by Ordpg_Sights " +
                ")" +
                "select Tsc_Name as 景点名, isnull(t.使用总数, 0) as 使用总数, isnull(t.已拍照, 0) as 已拍照, isnull(t.拍照中, 0) as 拍照中, isnull(t.未拍照, 0) as 未拍照" +
                " from t right join tb_ErpTheScenery on t.Ordpg_Sights = tb_ErpTheScenery.Tsc_Name";

            return LYFZ.Helper.SQLHelper.Query(strQuery);
        }
        #endregion
    }
}