ProductionSystem/ProductionSystem/Forms/SearchResultForm.cs

using Custom.Utils.Framework;
using DevExpress.XtraGrid.Columns;
using ProductionSystem.Forms.CommonForms;
using ProductionSystem.Untils.System;
using ProductionSystem.UserComponent.CommonComponent;
using ProductionSystem_Log;
using ProductionSystem_Model.ViewModel.Request;
using ProductionSystem_Model.ViewModel.Response;
using ProductionSystem_Service;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Web.UI.WebControls;
using System.Windows.Forms;

namespace ProductionSystem.Forms
{
    public partial class SearchResultForm : MyBaseForm
    {
        private readonly ResultService _resultService = new ResultService();
        private readonly PlcPointService _plcPointService = new PlcPointService();

        public SearchResultForm()
        {
            InitializeComponent();
            UcTitle("数据查询");
            gridPage.InitDataPager();
            gridPage.OnPagerEvents += SearthResultData;

        }

        private void SearchResultForm_Load(object sender, EventArgs e)
        {
            BindDropDownHelper.BindProductType(cmbProductType, true, false, false);
            dtBegin.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            dtEnd.Text = DateTime.Now.ToString("yyyy-MM-dd");
        }

        /// <summary>
        /// 点击查询按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearch_Click(object sender, EventArgs e)
        {
            SearthResultData();
        }

        /// <summary>
        /// 点击重置按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnReset_Click(object sender, EventArgs e)
        {
            cmbProductType.SelectedIndex = 0;
            txtProductCode.Text = string.Empty;
            dtBegin.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            dtEnd.Text = DateTime.Now.ToString("yyyy-MM-dd");
        }

        /// <summary>
        /// 点击导出按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                FolderBrowserDialog path = new FolderBrowserDialog();
                if (path.ShowDialog() == DialogResult.OK)
                {
                    Loading.ShowWaitForm();
                    int totalCount = 0;
                    var list = DataSource(ref totalCount, false);
                    var filePath = string.Format("{0}\\{1}.xlsx", path.SelectedPath, DateTime.Now.ToString("yyyyMMddHHmmss"));

                    #region 设置列名称
                    var mappingColumns = new List<ExportColumns>();
                    var co1 = new ExportColumns { Field = "CreatedTime", Title = "创建时间" };
                    var co2 = new ExportColumns { Field = "ProductType", Title = "产品型号" };
                    var co3 = new ExportColumns { Field = "ProductCode", Title = "产品条码" };
                    mappingColumns.Add(co1);
                    mappingColumns.Add(co2);
                    mappingColumns.Add(co3);
                    //查询点位信息
                    var plcPoints = _plcPointService.QueryActiveSaveDbPlcPoints();
                    foreach (var item in plcPoints)
                    {
                        var resultField = item.ResultField;
                        var fieldName = resultField.Substring(0, 1).ToUpper() + resultField.Substring(1);
                        var column = new ExportColumns { Field = fieldName, Title = item.PointName };
                        mappingColumns.Add(column);
                    }
                    #endregion

                    if (list == null || list.Count <= 0)
                    {
                        Loading.CloseWaitForm();
                        CustomMessageBoxHelper.XtraMessageBoxShow("无数据！");
                        return;
                    }
                    ExcelHelper.ToExcel(list, filePath, mappingColumns, true);
                    Loading.CloseWaitForm();
                    CustomMessageBoxHelper.XtraMessageBoxShow("导出成功！");
                }
            }
            catch (Exception ex)
            {
                Loading.CloseWaitForm();
                CustomMessageBoxHelper.XtraMessageBoxShow("导出结果数据异常！");
                LogHelper.Error(ex, "导出结果数据异常!", ex);
            }
        }

        /// <summary>
        /// 查询结果表数据
        /// </summary>
        private void SearthResultData()
        {
            Loading.ShowWaitForm();
            int totalCount = 0;
            var list = DataSource(ref totalCount);
            Loading.CloseWaitForm();
            this.gridResult.DataSource = list;
            gridPage.RefreshPager(gridPage.PageIndex, gridPage.PageSize, totalCount);
        }

        /// <summary>
        /// 数据源
        /// </summary>
        /// <param name="isPage"></param>
        /// <returns></returns>
        private List<ResultVM> DataSource(ref int totalCount, bool isPage = true)
        {
            try
            {
                totalCount = 0;
                QueryResultVM queryResultVM = new QueryResultVM
                {
                    PageIndex = gridPage.PageIndex,
                    PageSize = gridPage.PageSize
                };
                string sqlCol = "";

                var beginParseResult = DateTime.TryParse(this.dtBegin.Text, out var dtBeginDate);
                var endParseResult = DateTime.TryParse(string.Format("{0} 23:59:59", this.dtEnd.Text), out var dtEndDate);
                if (!beginParseResult || !endParseResult)
                {
                    CustomMessageBoxHelper.XtraMessageBoxShow("时间格式有误，请重新设置");
                    return null;
                }
                else if (dtBeginDate > dtEndDate)
                {
                    CustomMessageBoxHelper.XtraMessageBoxShow("开始时间不能大于结束时间，请重新设置");
                    return null;
                }

                #region 查询条件
                var strWhere = @" WHERE createdtime>=@begindate AND createdtime<=@enddate";
                var paras = new List<SugarParameter>
                             {
                                 new SugarParameter("@begindate", dtBeginDate),
                                 new SugarParameter("@enddate", dtEndDate)
                             };
                var productType = ((ListItem)cmbProductType.EditValue).Value;
                if (!string.IsNullOrWhiteSpace(productType))
                {
                    strWhere += @" and product_type = @producttype";
                    paras.Add(new SugarParameter("@producttype", productType));
                }
                if (!string.IsNullOrWhiteSpace(txtProductCode.Text.Trim()))
                {
                    strWhere += @" and product_code like @productcode";
                    paras.Add(new SugarParameter("@productcode", string.Format("%{0}%", txtProductCode.Text.Trim())));
                }
                #endregion

                //查询需要存数据库的点位信息
                var plcPoints = _plcPointService.QueryActiveSaveDbPlcPoints();
                string sql;
                if (plcPoints.Count > 0)
                {
                    int visibleIndex = 3;
                    for (int i = 0; i < plcPoints.Count; i++)
                    {
                        #region 动态添加列
                        GridColumn column = new GridColumn();
                        column.AppearanceCell.Font = new Font("Tahoma", 10.5F);
                        column.AppearanceCell.Options.UseFont = true;
                        column.AppearanceCell.ForeColor = Color.Black;
                        column.AppearanceCell.Options.UseForeColor = true;
                        //column.AppearanceCell.BackColor = Color.FromArgb(((int)(((byte)(9)))), ((int)(((byte)(28)))), ((int)(((byte)(77)))));
                        //column.AppearanceCell.Options.UseBackColor = true;
                        column.AppearanceHeader.BackColor = Color.White;
                        column.AppearanceHeader.Font = new Font("Tahoma", 10.5F, FontStyle.Bold);
                        column.AppearanceHeader.Options.UseBackColor = true;
                        column.AppearanceHeader.ForeColor = Color.Black;
                        column.AppearanceHeader.Options.UseFont = true;
                        column.Caption = plcPoints[i].PointName;
                        var resultField = plcPoints[i].ResultField;
                        if (!string.IsNullOrEmpty(resultField) && resultField.Length>2)
                        {
                            column.FieldName = resultField.Substring(0, 1).ToUpper() + resultField.Substring(1);
                        }

                       
                        column.MinWidth = 250;
                        column.Name = plcPoints[i].ResultField;
                        column.Visible = true;
                        column.VisibleIndex = visibleIndex;
                        column.Width = 250;
                        column.OptionsColumn.AllowEdit = false;
                        column.OptionsColumn.AllowMerge = DevExpress.Utils.DefaultBoolean.False;
                        column.OptionsColumn.AllowMove = false;
                        column.OptionsColumn.AllowSort = DevExpress.Utils.DefaultBoolean.False;
                        column.OptionsFilter.AllowFilter = false;
                        sqlCol += $"coalesce({plcPoints[i].ResultField},'') as \"{plcPoints[i].ResultField}\",";
                        var col = grid_Result.Columns.ColumnByName(plcPoints[i].ResultField);
                        if (col == null)
                        {
                            grid_Result.Columns.Add(column);
                        }

                        #endregion

                        visibleIndex++;
                    }

                    sql = $"select {sqlCol} createdtime as \"CreatedTime\",product_type as \"ProductType\",product_code as \"ProductCode\" from t_result {strWhere} ORDER BY t_result.createdtime DESC";
                }
                else
                {
                    sql = $"select createdtime as \"CreatedTime\",product_type as \"ProductType\",product_code as \"ProductCode\" from t_result {strWhere} ORDER BY t_result.createdtime DESC";
                }
                if (isPage)
                {
                    var data = _resultService.QueryBySql(sql, paras.ToArray()).ToPageList(queryResultVM.PageIndex, queryResultVM.PageSize, ref totalCount);
                    return data;
                }
                else
                {
                    var data = _resultService.QueryBySql(sql, paras.ToArray()).ToList();
                    return data;
                }
            }
            catch (Exception ex)
            {
                var msg = ex == null ? "执行SearchResult中DataSource方法时异常" : ex.Message;
                LogHelper.Error(ex, $"执行SearchResult中DataSource方法时异常：{msg}");
                return null;
            }
        }

    }
}