Autobio_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/GDLXSJContro...

using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.report
{
    public class GDLXSJController : BaseController
    {
        //// GET: GDLXYQ
        //public ActionResult Index()
        //{
        //    return View();
        //}
        /// <summary>
        /// 获取报表头
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHeadList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            DataTable dtNew = new DataTable();
            //添加第一行标题
            //DataColumn dc1 = new DataColumn("");
            //dtNew.Columns.Add(dc1);
            var sqlGDLY1 = "select F_CategoryId,F_CategoryName from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂')";
            var dtGDLY1 = DbHelperSQL.Query(sqlGDLY1).Tables[0];
            int dtGDLY1rowcount = dtGDLY1.Rows.Count;
            //列名
            string[] dtGDLY1_name = new string[dtGDLY1rowcount + 2];
            dtGDLY1_name[0] = "";
            //列数
            int[] dtGDLY1_count = new int[dtGDLY1rowcount + 2];
            dtGDLY1_count[0] = 1;

            for (int v = 0; v < dtGDLY1rowcount; v++)
            {
                string catename = dtGDLY1.Rows[v]["F_CategoryName"].ToString();
                var count_sub = int.Parse(DbHelperSQL.GetSingle("select count(*) from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_DeleteFlag = 0 and F_ParentId=" + int.Parse(dtGDLY1.Rows[v]["F_CategoryId"].ToString()) + "").ToString());
                dtGDLY1_name[v + 1] = catename;
                dtGDLY1_count[v + 1] = count_sub;
            }
            dtGDLY1_name[dtGDLY1rowcount + 1] = "总计";
            dtGDLY1_count[dtGDLY1rowcount + 1] = 1;
            res = Success("获取工单类型试剂报表表头成功", dtNew);

            return res;
        }

        /// <summary>
        /// 获取工单类型试剂报表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetDataList(string stime, string endtime)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            DataTable dtNew = new DataTable();
            //添加第一行标题
            //DataColumn dc1 = new DataColumn("");
            //dtNew.Columns.Add(dc1);
            var sqlGDLY1 = "select F_CategoryId,F_CategoryName from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂') and F_DeleteFlag = 0";
            var dtGDLY1 = DbHelperSQL.Query(sqlGDLY1).Tables[0];
            int dtGDLY1rowcount = dtGDLY1.Rows.Count;
            //列名
            string[] dtGDLY1_name = new string[dtGDLY1rowcount + 2];
            dtGDLY1_name[0] = "";
            //列数
            int[] dtGDLY1_count = new int[dtGDLY1rowcount + 2];
            dtGDLY1_count[0] = 1;

            for (int v = 0; v < dtGDLY1rowcount; v++)
            {
                string catename = dtGDLY1.Rows[v]["F_CategoryName"].ToString();
                var count_sub = int.Parse(DbHelperSQL.GetSingle("select count(*) from T_RepositoryCategory where 1=1 and F_DeleteFlag = 0 and F_CategoryType = 1 and F_ParentId=" + int.Parse(dtGDLY1.Rows[v]["F_CategoryId"].ToString()) + "").ToString());
                dtGDLY1_name[v + 1] = catename;
                dtGDLY1_count[v + 1] = count_sub;
            }
            dtGDLY1_name[dtGDLY1rowcount + 1] = "总计";
            dtGDLY1_count[dtGDLY1rowcount + 1] = 1;
            dtNew = getData(stime, endtime);

            #region 获取第二行标题
            //DataColumn dc = new DataColumn("姓名");
            //dtNew.Columns.Add(dc);
            List<string> colnames = new List<string>();
            colnames.Add("姓名");
            var sqlGDLY = " and F_CategoryType=1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂') and F_DeleteFlag = 0) and F_DeleteFlag = 0";
            var recordCount = 0;
            var dtGDLY = BLL.PagerBLL.GetListPager(
              "T_RepositoryCategory",
              "F_CategoryId",
              "*",
              sqlGDLY,
              "ORDER BY F_ParentId asc",
              10,
              1,
              true,
              out recordCount);
            int[] cateid = new int[recordCount];
            List<Model.T_RepositoryCategory> modelList = new BLL.T_RepositoryCategory().DataTableToList(dtGDLY);

            for (int i = 0; i < modelList.Count; i++)
            {
                string colname = modelList[i].F_CategoryName;
                //if (dtNew.Columns.Contains(colname))
                //    colname += "_1";
                colnames.Add(colname);
                //dtNew.Columns.Add(new DataColumn(colname));
                //cateid[i] = modelList[i].F_CategoryId;
            }
            //dtNew.Columns.Add("总计");
            colnames.Add("");
            #endregion
            var obj = new
            {
                linename = dtGDLY1_name,
                linecount = dtGDLY1_count,
                lineSeName = colnames,
                dtlist = dtNew
            };
            res = Success("获取工单类型数据成功", obj);

            return res;
        }

        private DataTable getData(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            #region 添加表格标题
            ////添加第一行标题
            ////DataColumn dc1 = new DataColumn("");
            ////dtNew.Columns.Add(dc1);
            //var sqlGDLY1 = "select F_CategoryId,F_CategoryName from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂'))";
            //var dtGDLY1 = DbHelperSQL.Query(sqlGDLY1).Tables[0];
            //int dtGDLY1rowcount = dtGDLY1.Rows.Count;
            ////列名
            //string[] dtGDLY1_name = new string[dtGDLY1rowcount + 1];
            //dtGDLY1_name[0] = "";
            ////列数
            //int[] dtGDLY1_count = new int[dtGDLY1rowcount + 1];
            //dtGDLY1_count[0] = 1;

            //for (int v = 0; v < dtGDLY1rowcount; v++)
            //{
            //    string catename = dtGDLY1.Rows[v]["F_CategoryName"].ToString();
            //    var count_sub = int.Parse(DbHelperSQL.GetSingle("select count(*) from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_ParentId=" + int.Parse(dtGDLY1.Rows[v]["F_CategoryId"].ToString()) + "").ToString());
            //    dtGDLY1_name[v + 1] = catename;
            //    dtGDLY1_count[v + 1] = count_sub;
            //}

            //添加第二行标题
            DataColumn dc = new DataColumn("姓名");
            dtNew.Columns.Add(dc);
            var sqlGDLY = " and F_CategoryType=1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂') and F_DeleteFlag = 0 ) and F_DeleteFlag = 0";
            var recordCount = 0;
            var dtGDLY = BLL.PagerBLL.GetListPager(
              "T_RepositoryCategory",
              "F_CategoryId",
              "*",
              sqlGDLY,
              "ORDER BY F_ParentId asc",
              10,
              1,
              true,
              out recordCount);
            int[] cateid = new int[recordCount];
            List<Model.T_RepositoryCategory> modelList = new BLL.T_RepositoryCategory().DataTableToList(dtGDLY);

            for (int i = 0; i < modelList.Count; i++)
            {
                string colname = modelList[i].F_CategoryName;
                if (dtNew.Columns.Contains(colname))
                    colname += "_" + i;
                dtNew.Columns.Add(new DataColumn(colname));
                cateid[i] = modelList[i].F_CategoryId;
            }
            dtNew.Columns.Add("总计");
            #endregion

            string sqltimeCallRecords = "";
            if (stime != null && stime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , CreateTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , CreateTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }

            double day = 0;
            if (stime != null && endtime != null && endtime.Trim() != "" && stime.Trim() != "")
            {
                TimeSpan time = Convert.ToDateTime(endtime) - Convert.ToDateTime(stime);
                day = time.Days + 1;
            }
            //获取坐席数据
            DataTable dt = new DataTable();
            string sql = "SELECT * from T_Sys_UserAccount where F_DeleteFlag=0 order by F_Userid asc";
            dt = DbHelperSQL.Query(sql).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow drNew = dtNew.NewRow();
                drNew["姓名"] = dt.Rows[i]["F_UserName"].ToString();
                //获取工单类型数据
                int sum = 0;
                for (int j = 0; j < cateid.Length; j++)
                {
                    string str = "select count(*) from T_Wo_WorkOrder where IsDel=0 and typeclass=" + cateid[j] + " and CreateUserID='" + dt.Rows[i]["F_Userid"].ToString() + "'" + sqltimeCallRecords;
                    int ecount = int.Parse(DbHelperSQL.GetSingle(str).ToString());

                    sum += ecount;
                    drNew[j + 1] = ecount.ToString();
                }
                drNew["总计"] = sum;
                dtNew.Rows.Add(drNew);
            }
            DataRow drsum = dtNew.NewRow();
            drsum[0] = "总计";

            for (int i = 1; i < dtNew.Columns.Count; i++)
            {
                int ecount = 0;
                int sum = 0;
                for (int j = 1; j < dtNew.Rows.Count; j++)
                {
                    if (dtNew.Rows[j][i] != null)
                    {
                        ecount = int.Parse(dtNew.Rows[j][i].ToString());
                        sum += ecount;
                    }
                }
                drsum[i] = sum;
            }
            dtNew.Rows.Add(drsum);
            return dtNew;
        }

        //导出数据
        public ActionResult ExptList(string stime, string endtime)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                DataTable dtNew = new DataTable();
                //添加第一行标题
                //DataColumn dc1 = new DataColumn("");
                //dtNew.Columns.Add(dc1);
                var sqlGDLY1 = "select F_CategoryId,F_CategoryName from T_RepositoryCategory where 1=1 and F_CategoryType = 1 and F_ParentId in (select F_CategoryId from T_RepositoryCategory where F_CategoryType = 1 and F_DeleteFlag = 0 and F_CategoryName = '试剂') and F_DeleteFlag = 0";
                var dtGDLY1 = DbHelperSQL.Query(sqlGDLY1).Tables[0];
                int dtGDLY1rowcount = dtGDLY1.Rows.Count;
                //每块占的列数
                int[] colpos = new int[dtGDLY1rowcount + 1];
                colpos[0] = 1;
                //列名
                string[] dtGDLY1_name = new string[dtGDLY1rowcount + 2];
                dtGDLY1_name[0] = "";
                //列数
                int[] dtGDLY1_count = new int[dtGDLY1rowcount + 2];
                dtGDLY1_count[0] = 1;

                for (int v = 0; v < dtGDLY1rowcount; v++)
                {
                    string catename = dtGDLY1.Rows[v]["F_CategoryName"].ToString();
                    var count_sub = int.Parse(DbHelperSQL.GetSingle("select count(*) from T_RepositoryCategory where 1=1 and F_DeleteFlag = 0 and F_CategoryType = 1 and F_ParentId=" + int.Parse(dtGDLY1.Rows[v]["F_CategoryId"].ToString()) + "").ToString());
                    dtGDLY1_name[v + 1] = catename;
                    dtGDLY1_count[v + 1] = count_sub;
                    colpos[v + 1] = count_sub;
                }
                dtGDLY1_name[dtGDLY1rowcount + 1] = "总计";
                dtGDLY1_count[dtGDLY1rowcount + 1] = 1;

                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = getData(stime, endtime);
                if (npoi.ExportToExcelForGDLX("工单类型试剂报表", dt, "试剂", dtGDLY1_name, colpos) == "")
                {
                    var obj = new
                    {
                        datatable = dt,
                        dtnames = dtGDLY1_name,
                        dtcols = colpos
                    };
                    return Success("导出成功", obj);
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return res;
        }

        ///// <summary>
        ///// 合并单元格
        ///// </summary>
        ///// <param name="sheet">要合并单元格所在的sheet</param>
        ///// <param name="rowstart">开始行的索引</param>
        ///// <param name="rowend">结束行的索引</param>
        ///// <param name="colstart">开始列的索引</param>
        ///// <param name="colend">结束列的索引</param>
        //public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        //{
        //    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
        //    sheet.AddMergedRegion(cellRangeAddress);
        //}
    }
}