Gsmarket/Sources/GS-MarketMG-2018/GSMarketSys/Controllers/ReportsController.cs

using SysBaseLibs;
using SysDataLibs.TableClass;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace GSMarketSys.Controllers
{
    public class ReportsController : BaseController
    {
        // GET: Reports
        #region RptBulletinInfoRead
        [CheckPowerFilter]
        public ActionResult RptBulletinInfoRead()
        {
            return View();
        }
        public ActionResult GetRptBulletinInfoRead()
        {
            string lcSql = " select * from vwBulletinInfoNoRead ";

            return Content(GetData.Instance.GetResult(lcSql, UserSessionInfo.DBConn));
        }
        [ValidateInput(false)]
        public ActionResult ExportExcelRptBulletinInfoRead()
        {
            string lcSql = " select Title as '通知标题',MarketName as '市场' from vwBulletinInfoNoRead ";
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            string UploadFileDestination = System.Web.HttpContext.Current.Request.PhysicalApplicationPath;
            UploadFileDestination += SysDataLibs.AppEnv.SysSetObj.GetString("DOWNLOADPATH") +"\\ExcelExport\\"+ "最近7天公告通知市场未读情况表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
            string lcMsg = "/"+ SysDataLibs.AppEnv.SysSetObj.GetString("DOWNLOADPATH") + "/ExcelExport/" + "最近7天公告通知市场未读情况表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
            string lcRetval = "";
            if(ExcelHelper.dataTableToCsv(loQuery.CurrentTable, UploadFileDestination))
            {
                lcRetval = ErrorRebackInfo.GetErrorBackInfo(true,"0", lcMsg);
            }else
            {
                lcRetval = ErrorRebackInfo.GetErrorBackInfo(false, "0", "下载失败！");
            }
            return Content(lcRetval);
        }
        #endregion

        #region  市场证照到期过期  RptQMarketWrntDueExpire
        public ActionResult RptQMarketWrntDueExpire()
        {
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            ViewBag.SelWarrantType = GetData.Instance.GetSelStr(WarrantType_info.cWarrantTypeID, WarrantType_info.cName, " "+Tn.WarrantType, WarrantType_info.cFlag + "=0 AND " + WarrantType_info.cIsLock + "=0", UserSessionInfo.DBConn);
            return View();
        }

		

        public ActionResult RptPMarketWrntDueExpire()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string ExpireFlag = Request["ExpireFlag"];
            string WarrantTypeID = Utils.AreaToSQLcs(Request["WarrantTypeID"]).Trim();
            // 视图中已经对Markets.IsLock=0进行了过滤
            string lcSql = "SELECT * FROM " + Tn.vwMarketWrntDueExpire + " WHERE " + UserSessionInfo.MarketDataSql(false);

            //string strDEFlag = ddlDueExpireFlag.SelectedValue.Trim();
            if (ExpireFlag != "0")
            {
                lcSql += " AND " + vwMarketWrntDueExpire_info.cDEFlag + "=" + ExpireFlag;
            }

            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + vwMarketWrntDueExpire_info.cMarketId + "='" + MarketID + "' ";
            }

            if (!string.IsNullOrEmpty(WarrantTypeID))
            {
                lcSql += " AND " + vwMarketWrntDueExpire_info.cWarrantTypeId + "=" + WarrantTypeID;
            }
            lcSql += " ORDER BY " + vwMarketWrntDueExpire_info.cMarketId;
            lcSql += "," + vwMarketWrntDueExpire_info.cWarrantTypeId;
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                    // 序号
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");


                    // 市场名称
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketWrntDueExpire_info.cMktName));
                    sb.Append("</td>");


                    // 证照类型
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketWrntDueExpire_info.cWarrantName));
                    sb.Append("</td>");


                    // 证照号
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketWrntDueExpire_info.cWarrantNumber));
                    sb.Append("</td>");

                    // 证照有限期截止日期
                    sb.Append("<td align='left'>");
                    DateTime dtValidEnd = loQuery.GetDateTime(vwMarketWrntDueExpire_info.cValidEndTime);
                    sb.Append(dtValidEnd.ToString("yyyy-MM-dd"));
                    sb.Append("</td>");


                    // 到期过期
                    sb.Append("<td align='left'>");
                    if (loQuery.GetInt(vwMarketWrntDueExpire_info.cDEFlag) == 1)
                        sb.Append("三个月到期"); 
                    else
                        sb.Append("过期");
                    sb.Append("</td>");
                   

                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
            //ViewBag.Hid_WhereSql = lcSql;
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        public ActionResult GetRptPMarketWrntDueExpire()
        {
            string lcWhereSql = Request["WhereSql"];
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcWhereSql);
            StringBuilder sb = new StringBuilder();
            sb.Append("{\"total\":" + loQuery.RecCount + ",\"rows\":");
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                loQuery.CurrentTable.Columns.Add("DEFlagName", typeof(string));
                loQuery.MoveFirst();
                for(int i = 0; i < loQuery.RecCount; i++)
                {
                    loQuery.SetField("DEFlagName", loQuery.GetString("DEFlag") == "1" ? "三个月到期" : "过期");

                    loQuery.MoveNext();
                }
                sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
            }
            else
            {
                sb.Append("[]");
            }
            sb.Append("}");
            return Content(sb.ToString());
            //return Content(GetData.Instance.GetResult(lcWhereSql, UserSessionInfo.DBConn));
        }
        [CheckPowerFilter]
        public ActionResult CheckIsHasBrowerAuth()
        {
            return Content(ErrorRebackInfo.GetErrorBackInfo(true, "0",""));
        }

        #endregion

        public ActionResult GetMarkets()
        {
            string lcSql = "select top 100 *  from " + Tn.Markets + " where IsLock='0' " + UserSessionInfo.MarketDataSql(true);

            string RegionId = Request["RegionID"];
            string MarketName = Request["MarketName"];
            if (!string.IsNullOrEmpty(RegionId))
            {
                RegionId = Regions_info.GetAllRegion(UserSessionInfo, RegionId);
                lcSql += " and " + Markets_info.cRegionID + " in (" + RegionId + ")";
            }
            if (!string.IsNullOrEmpty(MarketName))
            {
                lcSql += " and  names like '%" + MarketName + "%'";
            }
            lcSql += " and  MarketTypeID <> '6' ";
            return Content(GetData.Instance.GetResult(lcSql, UserSessionInfo.DBConn));
        }

        public ActionResult GetMarketJsons()
        {
            string lcSql = "select MarketID ,Names   from " + Tn.Markets + " where IsLock='0' " + UserSessionInfo.MarketDataSql(true);
            string RegionId = Request["RegionID"];
            string MarketName = Request["MarketName"];
            if (!string.IsNullOrEmpty(RegionId))
            {
                RegionId = Regions_info.GetAllRegion(UserSessionInfo, RegionId);
                lcSql += " and " + Markets_info.cRegionID + " in (" + RegionId + ")";
            }
            if (!string.IsNullOrEmpty(MarketName))
            {
                lcSql += " and  names like '%" + MarketName + "%'";
            }
            lcSql += " and  MarketTypeID <> '6' ";
            return Content(GetData.Instance.GetJsonResult(lcSql, UserSessionInfo.DBConn));
        }

        #region RptQSellerWrntDueExpire
        public ActionResult RptQSellerWrntDueExpire()
        {
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            ViewBag.SelWarrantType = GetData.Instance.GetSelStr(WarrantType_info.cWarrantTypeID, WarrantType_info.cName, " " + Tn.WarrantType, WarrantType_info.cFlag + "=1 AND " + WarrantType_info.cIsLock + "=0", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult RptPSellerWrntDueExpire()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string ExpireFlag = Request["ExpireFlag"];
            string WarrantTypeID = Utils.AreaToSQLcs(Request["WarrantTypeID"]).Trim();
            string SellerNames = Utils.AreaToSQLcs(Request["SellerNames"]).Trim();
            // 视图中已经对Markets.IsLock=0进行了过滤
            string lcSql = "SELECT * FROM " + Tn.vwSellerWrntDueExpire + " WHERE " + UserSessionInfo.MarketDataSql(false);

            //string strDEFlag = ddlDueExpireFlag.SelectedValue.Trim();
            if (ExpireFlag != "0")
            {
                lcSql += " AND " + vwSellerWrntDueExpire_info.cDEFlag + "=" + ExpireFlag;
            }
            if (!string.IsNullOrEmpty(SellerNames))
            {
                lcSql += " AND " + vwSellerWrntDueExpire_info.cSellerName + "='" + SellerNames + "' ";
            }else
            {
                if (!string.IsNullOrEmpty(MarketID))
                {
                    lcSql += " AND " + vwSellerWrntDueExpire_info.cMarketId + "='" + MarketID + "' ";
                }
            }
            if (!string.IsNullOrEmpty(WarrantTypeID))
            {
                lcSql += " AND " + vwSellerWrntDueExpire_info.cWarrantTypeId + "=" + WarrantTypeID;
            }
            lcSql += " ORDER BY " + vwSellerWrntDueExpire_info.cMarketId;
            lcSql += "," + vwSellerWrntDueExpire_info.cWarrantTypeId;
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                    // 序号
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");


                    // 经营户
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpire_info.cMarketName));
                    sb.Append("</td>");


                    // 证照类型
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpire_info.cSellerName));
                    sb.Append("</td>");


                    // 证照类型
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpire_info.cWarrantName));
                    sb.Append("</td>");

                    // 证照类型
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpire_info.cWarrantNumber));
                    sb.Append("</td>");

                    // 证照有限期截止日期
                    sb.Append("<td align='left'>");
                    DateTime dtValidEnd = loQuery.GetDateTime(vwSellerWrntDueExpire_info.cValidEndTime);
                    sb.Append(dtValidEnd.ToString("yyyy-MM-dd"));
                    sb.Append("</td>");


                    // 到期过期
                    sb.Append("<td align='left'>");
                    if (loQuery.GetInt(vwSellerWrntDueExpire_info.cDEFlag) == 1)
                        sb.Append("三个月到期");
                    else
                        sb.Append("过期");
                    sb.Append("</td>");


                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
            //ViewBag.Hid_WhereSql = lcSql;
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        public ActionResult GetMarketSeller()
        {
            string RegionId = Request["RegionId"];
            string MarketID = Request["MarketID"];
            string Names = Utils.AreaToSQLcs(Request["SellerName"]).Trim();
            string lcSql = "select top 250 *  from " + Tn.MarketSellers + " Where 1=1 and IsLock='N' ";
          
            if (!string.IsNullOrEmpty(MarketID))
            {
                //选定市场
                lcSql += " and " + MarketSellers_info.cMarketID + " ='" + MarketID + "'";
            } 
            else
            {
                string whereMarketSql = "select MarketID from Markets where IsLock='0' " + UserSessionInfo.MarketDataSql(true);
                //全部市场
                lcSql += " and " + MarketSellers_info.cMarketID + " in(" + whereMarketSql + ")";
            }
            if (!string.IsNullOrEmpty(Names))
            {
                lcSql += " and " + MarketSellers_info.cName + " like '%" + Names + "%'";
            }
            return Content(GetData.Instance.GetResult(lcSql, UserSessionInfo.DBConn));
        }
        #endregion

        #region RptQSellerWrntDueExpireCnt RptPSellerWrntDueExpireCnt
        public ActionResult RptQSellerWrntDueExpireCnt()
        {
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            ViewBag.SelWarrantType = GetData.Instance.GetSelStr(WarrantType_info.cWarrantTypeID, WarrantType_info.cName, " " + Tn.WarrantType, WarrantType_info.cFlag + "=1 AND " + WarrantType_info.cIsLock + "=0", UserSessionInfo.DBConn);
            return View();
        }

        public ActionResult RptPSellerWrntDueExpireCnt()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
          
            string WarrantTypeID = Utils.AreaToSQLcs(Request["WarrantTypeID"]).Trim();
           
            // 视图中已经对Markets.IsLock=0进行了过滤
            string lcSql = "SELECT * FROM " + Tn.vwSellerWrntDueExpireCalc + " WHERE " + UserSessionInfo.MarketDataSql(false);

            //string strDEFlag = ddlDueExpireFlag.SelectedValue.Trim();

            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + vwSellerWrntDueExpire_info.cMarketId + "='" + MarketID + "' ";
            }
            if (!string.IsNullOrEmpty(WarrantTypeID))
            {
                lcSql += " AND " + vwSellerWrntDueExpire_info.cWarrantTypeId + "=" + WarrantTypeID;
            }
            lcSql += " ORDER BY " + vwSellerWrntDueExpireCalc_info.cMarketId + "," + vwSellerWrntDueExpireCalc_info.cWarrantTypeId;
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();
                int liSumAll = 0;
                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                    // 序号
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");


                    // 经营户
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpireCalc_info.cMarketName));
                    sb.Append("</td>");


                    // 证照类型
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwSellerWrntDueExpireCalc_info.cWarrantName));
                    sb.Append("</td>");

                    int liTotal, liDueCnt, liExpireCnt;
                    // 到期数
                    sb.Append("<td align='left'>");
                    liDueCnt = loQuery.GetInt(vwSellerWrntDueExpireCalc_info.cSDueCnt);
                    sb.Append(liDueCnt.ToString());
                    sb.Append("</td>");

                    // 过期数
                    sb.Append("<td align='left'>");
                    liExpireCnt = loQuery.GetInt(vwSellerWrntDueExpireCalc_info.cSExpireCnt);
                    sb.Append(liExpireCnt.ToString());
                    sb.Append("</td>");




                    // 合计
                    sb.Append("<td align='left'>");
                    liTotal = liDueCnt + liExpireCnt;
                    sb.Append(liTotal.ToString());
                    sb.Append("</td>");


                    sb.Append("</tr>");
                    liSumAll += liTotal;
                    loQuery.MoveNext();
                }
                if (liSumAll > 0)
                {
                    sb.Append("<tr>");
                    for (int i = 0; i < 5; i++)
                    {
                        sb.Append("<td align='left'>");
                        
                        sb.Append("");
                        sb.Append("</td>");
                    }
                    sb.Append("<td align='left'>");

                    sb.Append(liSumAll.ToString());
                    sb.Append("</td>");

                    sb.Append("</tr>");
                }

            }
            //ViewBag.Hid_WhereSql = lcSql;
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion

        #region RptQAgriFoodCheck 农产品(食品)检测情况表 
        public ActionResult RptQAgriFoodCheck()
        {
            DateTime EndDate = DateTime.Now;
            ViewBag.StartTime = EndDate.AddMonths(-2).ToString("yyyy-MM-dd");
            ViewBag.EndTime = EndDate.ToString("yyyy-MM-dd");
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            // 检测单位
           // string strFillDDL = " SELECT DISTINCT " + CheckData_info.cCheckUnit + " FROM " + Tn.CheckData + " WHERE " + UserSessionInfo.MarketDataSql(false);
            ViewBag.SelCheckUnit = GetData.Instance.GetSelStrDistinct(CheckData_info.cCheckUnit, CheckData_info.cCheckUnit, Tn.CheckData,  UserSessionInfo.MarketDataSql(false), UserSessionInfo.DBConn);
           
           // 检测方法
         
            ViewBag.SelCheckMethod = GetData.Instance.GetSelStrDistinct(CheckProjectType_info.cCheckMethod, CheckProjectType_info.cCheckMethod, Tn.CheckProjectType, CheckProjectType_info.cIsLock + "=0 ", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult RptPAgriFoodCheck()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string CheckUnit = Utils.AreaToSQLcs(Request["CheckUnit"]).Trim();
            string CheckMethod = Utils.AreaToSQLcs(Request["CheckMethod"]).Trim();
            // 检测时间
            string lcTimeSql = " ";
            if (!string.IsNullOrEmpty(StartTime))
            {
                if (!string.IsNullOrEmpty(EndTime))
                {
                    lcTimeSql = vwCheckData_info.cCheckTime + ">='" + StartTime;
                    lcTimeSql += "' AND " + vwCheckData_info.cCheckTime + "<='" + EndTime + "' ";
                }
                else
                {
                    lcTimeSql = vwCheckData_info.cCheckTime + "='" + StartTime + "' ";
                }
            }
            string lcSql = "";
            lcSql = " SELECT M." + Markets_info.cMarketID + ",M." + Markets_info.cNames + " AS MarketName,";
            lcSql += " ISNULL(TotalCnt,0) AS TotalCnt, ISNULL(SuccCnt,0) AS SuccCnt, ";
            lcSql += " ISNULL(TotalCnt1,0) AS TotalCnt1, ISNULL(SuccCnt1,0) AS SuccCnt1 ";
            lcSql += " FROM ";

            // 过滤市场编号及锁定状态
            lcSql += " (SELECT " + Markets_info.cMarketID + "," + Markets_info.cNames;
            lcSql += " FROM " + Tn.Markets + " WHERE " + UserSessionInfo.MarketDataSql(false);
            lcSql += " AND " + Markets_info.cIsLock + "=0) AS M ";

            // 检测方法
            string strCheckMethodSql = " ";
            if (!string.IsNullOrEmpty(CheckMethod))
            {
                strCheckMethodSql += " and " + vwCheckData_info.cCheckMethod + "= '" + CheckMethod + "' ";
            }

            // 检测单位
            string strCheckUnitSql = " ";
            if (!string.IsNullOrEmpty(CheckUnit))
            {
                strCheckUnitSql += " and " + vwCheckData_info.cCheckUnit + "='" + CheckUnit + "' ";
            }
            // 农药残留的检测项目
            string strNyclItemsSql = vwCheckData_info.cCheckProjectTypeID + " IN (5,6) ";

            lcSql += " LEFT JOIN ";
            lcSql += " (SELECT " + vwCheckData_info.cMarketID + ",COUNT(*) AS TotalCnt FROM " + Tn.vwCheckData;
            lcSql += " WHERE " + UserSessionInfo.MarketDataSql(false);
            lcSql += " AND " + lcTimeSql + " AND " + strNyclItemsSql + strCheckMethodSql + strCheckUnitSql;
            lcSql += " GROUP BY " + vwCheckData_info.cMarketID + ") t1 ";
            lcSql += " ON M." + Markets_info.cMarketID + "=t1." + vwCheckData_info.cMarketID;

            lcSql += " LEFT JOIN ";
            lcSql += " (SELECT " + vwCheckData_info.cMarketID + ",COUNT(*) AS SuccCnt FROM " + Tn.vwCheckData;
            lcSql += " WHERE " + UserSessionInfo.MarketDataSql(false);
            lcSql += " AND " + lcTimeSql + " AND " + strNyclItemsSql + strCheckMethodSql + strCheckUnitSql;
            lcSql += " AND " + vwCheckData_info.cCheckResult + "='合格'";
            lcSql += " GROUP BY " + vwCheckData_info.cMarketID + ") s1 ";
            lcSql += " ON M." + Markets_info.cMarketID + "=s1." + vwCheckData_info.cMarketID;

            // 常规五项检测项目的配置从数据库获取
            string strNormItemsSql = SysDataLibs.AppEnv.SysSetObj.GetString("AgriCheckNormItems");
            if (strNormItemsSql.Trim().Length < 1)
            {
                strNormItemsSql = " CheckProjectTypeId in (2,4,7,9,10) ";
            }
            lcSql += " LEFT JOIN ";
            lcSql += " (SELECT " + vwCheckData_info.cMarketID + ",COUNT(*) AS TotalCnt1 FROM " + Tn.vwCheckData;
            lcSql += " WHERE " + UserSessionInfo.MarketDataSql(false);
            lcSql += " AND " + lcTimeSql + " AND " + strNormItemsSql + strCheckMethodSql + strCheckUnitSql;
            lcSql += " GROUP BY " + vwCheckData_info.cMarketID + ") t2 ";
            lcSql += " ON M." + Markets_info.cMarketID + "=t2." + vwCheckData_info.cMarketID;

            lcSql += " LEFT JOIN ";
            lcSql += " (SELECT " + vwCheckData_info.cMarketID + ",COUNT(*) AS SuccCnt1 FROM " + Tn.vwCheckData;
            lcSql += " WHERE " + UserSessionInfo.MarketDataSql(false);
            lcSql += " AND " + lcTimeSql + " AND " + strNormItemsSql + strCheckMethodSql + strCheckUnitSql;
            lcSql += " AND " + vwCheckData_info.cCheckResult + "='合格'";
            lcSql += " GROUP BY " + vwCheckData_info.cMarketID + ") s2 ";
            lcSql += " ON M." + Markets_info.cMarketID + "=s2." + vwCheckData_info.cMarketID;

            // 如指定市场则加上过滤条件
            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " WHERE M." + Markets_info.cMarketID + "='" + MarketID + "' ";
            }
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                    // 序号
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");


                    // 市场
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString("MarketName"));
                    sb.Append("</td>");

                   

                    // 农药残留----->
                    int liTotal, liSucc, liFail;
                    // 检测批次
                    sb.Append("<td align='center'>");
                    liTotal = loQuery.GetInt("TotalCnt");
                    sb.Append(liTotal.ToString());
                    sb.Append("</td>");


                    // 合格批次
                    sb.Append("<td align='center'>");
                    liSucc = loQuery.GetInt("SuccCnt");
                    sb.Append(liSucc.ToString());
                    sb.Append("</td>");


                    // 不合格批次
                    sb.Append("<td align='center'>");
                    liFail = liTotal - liSucc;
                    sb.Append(liFail.ToString());
                    sb.Append("</td>");
                   

                    // 常规五项----->
                    int liTotal1, liSucc1, liFail1;
                    // 检测批次
                    sb.Append("<td align='center'>");
                    liTotal1 = loQuery.GetInt("TotalCnt1");
                    sb.Append(liTotal1.ToString());
                    sb.Append("</td>");


                    // 合格批次
                    sb.Append("<td align='center'>");
                    liSucc1 = loQuery.GetInt("SuccCnt1");
                    sb.Append(liSucc1.ToString());
                    sb.Append("</td>");



                    // 不合格批次
                    sb.Append("<td align='center'>");
                    liFail1 = liTotal1 - liSucc1;
                    sb.Append(liFail1.ToString());
                    sb.Append("</td>");
                  

                    // 市场合计
                    int liSumTotal = liTotal + liTotal1;
                    sb.Append("<td align='center'>");
                    sb.Append(liSumTotal.ToString());
                    sb.Append("</td>");
                   
                    int liSumSucc = liSucc + liSucc1;
                    sb.Append("<td align='center'>");
                    sb.Append(liSumSucc.ToString());
                    sb.Append("</td>");

                    int liSumFail = liFail + liFail1;
                    sb.Append("<td align='center'>");
                    sb.Append(liSumFail.ToString());
                    sb.Append("</td>");

                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion
        #region RptQUnitedCenterCheckData  联合检测中心检测情况报表
        public ActionResult RptQUnitedCenterCheckData()
        {
            DateTime EndDate = DateTime.Now;
            ViewBag.StartTime = EndDate.AddMonths(-2).ToString("yyyy-MM-dd");
            ViewBag.EndTime = EndDate.ToString("yyyy-MM-dd");
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
           
            // 检测方法
           
            ViewBag.SelCheckProjectTypeID = GetData.Instance.GetSelStrDistinct(CheckProjectType_info.cCheckProjectTypeID, CheckProjectType_info.cName, Tn.CheckProjectType, CheckProjectType_info.cIsLock + "=0 ", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult RptPUnitedCenterCheckData()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string CheckProjectTypeID = Utils.AreaToSQLcs(Request["CheckProjectTypeID"]).Trim();
            //string CheckMethod = Utils.AreaToSQLcs(Request["CheckMethod"]).Trim();
            // 检测时间
            string lcTimeSql = " ";
            if (!string.IsNullOrEmpty(StartTime))
            {
                if (!string.IsNullOrEmpty(EndTime))
                {
                    lcTimeSql = vwCheckData_info.cCheckTime + ">='" + StartTime;
                    lcTimeSql += "' AND " + vwCheckData_info.cCheckTime + "<='" + EndTime + "' ";
                }
                else
                {
                    lcTimeSql = vwCheckData_info.cCheckTime + "='" + StartTime + "' ";
                }
            }
            // 检测项目
            string strCheckProjectTypeSql = " ";
            if (!string.IsNullOrEmpty(CheckProjectTypeID))
            {
                strCheckProjectTypeSql += " and " + vwCheckData_info.cCheckProjectTypeID + "= '" + CheckProjectTypeID + "' ";
            }

            // 如指定市场则加上过滤条件
            string strMarketIdSql = " ";
            if (!string.IsNullOrEmpty(MarketID))
            {
                strMarketIdSql += " AND " + vwCheckData_info.cMarketID + "='" + MarketID + "' ";
            }

            // 检测单位
            string lcCheckUnitSql = " AND " + vwCheckData_info.cCheckUnit + " like '%联合检测中心%' ";

            // 检测方法
            string lcMethodSql = vwCheckData_info.cCheckMethod + " IN ('速测仪','光谱仪','试剂速测') ";

            string lcSql = "";
            lcSql = " SELECT T." + vwCheckData_info.cCheckMethod + ", TotalCnt, ISNULL(SuccCnt,0) AS SuccCnt FROM ";
            lcSql += " (SELECT " + vwCheckData_info.cCheckMethod + ", COUNT(*) AS TotalCnt FROM " + Tn.vwCheckData + " WHERE ";
            if (lcTimeSql != "")
                lcSql += lcTimeSql + " AND ";
            lcSql += lcMethodSql + strCheckProjectTypeSql + strMarketIdSql + lcCheckUnitSql;
            lcSql += " GROUP BY " + vwCheckData_info.cCheckMethod + ") T ";
            lcSql += " LEFT JOIN ";
            lcSql += " (SELECT " + vwCheckData_info.cCheckMethod + ", COUNT(*) AS SuccCnt FROM " + Tn.vwCheckData + " WHERE ";
            if (lcTimeSql != "")
                lcSql += lcTimeSql + " AND ";
            lcSql += lcMethodSql + strCheckProjectTypeSql + strMarketIdSql + lcCheckUnitSql;
            lcSql += " AND " + vwCheckData_info.cCheckResult + "='合格' ";
            lcSql += " GROUP BY " + vwCheckData_info.cCheckMethod + ") H ";
            lcSql += " ON H." + vwCheckData_info.cCheckMethod + "=T." + vwCheckData_info.cCheckMethod;

            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
             // 合计
            int liSumTotal = 0, liSumSucc = 0, liSumFail = 0;
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                    // 项目
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString("CheckMethod"));
                    sb.Append("</td>");

                    int liTotal, liSucc, liFail;
                    // 检测批次
                    sb.Append("<td align='center'>");
                    liTotal = loQuery.GetInt("TotalCnt");
                    sb.Append(liTotal.ToString());
                    sb.Append("</td>");

                    // 合格批次
                    sb.Append("<td align='center'>");
                    liSucc = loQuery.GetInt("SuccCnt");
                    sb.Append(liSucc.ToString());
                    sb.Append("</td>");

                    // 不合格批次
                    sb.Append("<td align='center'>");
                    liFail = liTotal - liSucc;
                    sb.Append(liFail.ToString());
                    sb.Append("</td>");

                    //  累加到合计
                    liSumTotal += liTotal;
                    liSumSucc += liSucc;
                    liSumFail += liFail;

                    // 备注
                    sb.Append("<td align='center'>");
                    sb.Append("");
                    sb.Append("</td>");

                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
            sb.Append("<tr>");

            sb.Append("<td align='center'>");
            sb.Append("合计：");
            sb.Append("</td>");

            sb.Append("<td align='center'>");
            sb.Append(liSumTotal.ToString());
            sb.Append("</td>");

            sb.Append("<td align='center'>");
            sb.Append(liSumSucc.ToString());
            sb.Append("</td>");

            sb.Append("<td align='center'>");
            sb.Append(liSumFail.ToString());
            sb.Append("</td>");

            sb.Append("<td align='center'>");
            sb.Append("");
            sb.Append("</td>");

            sb.Append("</tr>");
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion

        #region RptQMarketBaseInfo  市场、超市主体基础信息报表
        public ActionResult RptQMarketBaseInfo()
        {
           
           
            ViewBag.SelMarketTypeID = GetData.Instance.GetSelStr("MarketType", "MarketTypeForBaseInfo",UserSessionInfo.DBConn);

            // 检测方法

            ViewBag.SelCheckProjectTypeID = GetData.Instance.GetSelStrDistinct(CheckProjectType_info.cCheckProjectTypeID, CheckProjectType_info.cName, Tn.CheckProjectType, CheckProjectType_info.cIsLock + "=0 ", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult RptPMarketBaseInfo()
        {
            string MarketTypeID = Utils.AreaToSQLcs(Request["MarketTypeID"]).Trim();
            
            // 检测时间
            string lcSql = "";
            lcSql = " SELECT * FROM " + Tn.vwMarketBaseInfo + " WHERE " + UserSessionInfo.MarketDataSql(false);
            //按照市场类型查询
            if (!string.IsNullOrEmpty(MarketTypeID))
            {
                lcSql += " AND " + vwMarketBaseInfo_info.cMarketTypeID + " " + MarketTypeID + " ";
            }
            // 过滤市场
            lcSql += " AND " + UserSessionInfo.MarketDataSql(false);

            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                 
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");

               
                    sb.Append("<td align='center'>");
                
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cNames));
                    sb.Append("</td>");

                  
                    sb.Append("<td align='center'>");
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cArea));
                    sb.Append("</td>");

                  
                    sb.Append("<td align='center'>");
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cMktStallCnt));
                    sb.Append("</td>");

                    sb.Append("<td align='center'>");
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cMktSellerCnt));
                    sb.Append("</td>");

                    sb.Append("<td align='center'>");
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cLinkMan));
                    sb.Append("</td>");

                    sb.Append("<td align='center'>");
                    sb.Append(loQuery.GetString(vwMarketBaseInfo_info.cTelephone));
                    sb.Append("</td>");


                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
           
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion

        #region RptQConsumerComplain  投诉情况月统计报表
        public ActionResult RptQConsumerComplain()
        {
            DateTime loDate = DateTime.Now;
            string lcYears = "";
            string lcMonths = "";
            for (int i = 0; i > -12; i--)
            {
                lcYears += "<option value='"+ loDate.AddYears(i).Year+ "'>"+ loDate.AddYears(i).Year + "  年" + "</option>";
                lcMonths += "<option value='" + loDate.AddMonths(i).Month + "'>" + loDate.AddMonths(i).Month + "  月份" + "</option>";
            }

            ViewBag.SelYears = lcYears;
            ViewBag.SelMonths = lcMonths;
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);

            return View();
        }
        public ActionResult RptPConsumerComplain()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string Year = Utils.AreaToSQLcs(Request["Year"]).Trim();
            string Month = Utils.AreaToSQLcs(Request["Month"]).Trim();
            // 月份格式为yyyy/mm
            string strDateSql = Year + "/";
            if (Month.Length == 1)
                strDateSql += "0";
            strDateSql += Month;

            // 从视图查询结果
            string lcSql = " SELECT " + viewConsumerComplain_info.cMarketID + "," + viewConsumerComplain_info.cNames;
            lcSql += " ,COUNT(*) AS ComplainCnt FROM " + Tn.viewConsumerComplain;
            lcSql += " WHERE " + UserSessionInfo.MarketDataSql(false);
            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + viewConsumerComplain_info.cMarketID + "='" + MarketID + "' ";
            }
            lcSql += " AND CONVERT(Char(7)," + viewConsumerComplain_info.cLogDate + ",111)='" + strDateSql + "' ";
            lcSql += " GROUP BY " + viewConsumerComplain_info.cMarketID + "," + viewConsumerComplain_info.cNames;
            lcSql += " ORDER BY " + viewConsumerComplain_info.cMarketID;

            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");
                 
                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");

                    // 市场名称
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(viewConsumerComplain_info.cNames));
                    sb.Append("</td>");

                    // 市场合计
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString("ComplainCnt"));
                    sb.Append("</td>");
                   
                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }
           
            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion

        #region RptQMarketEvaluationHist  市场月度信用评分情况
        public ActionResult RptQMarketEvaluationHist()
        {
          
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);

            return View();
        }
        public ActionResult RptPMarketEvaluationHist()
        {
            string MarketID = Utils.AreaToSQLcs(Request["MarketID"]).Trim();
            string lcSql = "SELECT TOP 24 * FROM " + Tn.vwMarketEvalRpt + " WHERE " + UserSessionInfo.MarketDataSql(false);
            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + vwMarketEvalRpt_info.cMarketId + "='" + MarketID + "' ";
            }
         
            lcSql += " ORDER BY " + vwMarketEvalRpt_info.cMarketId;
            lcSql += "," + vwMarketEvalRpt_info.cYear + "," + vwMarketEvalRpt_info.cMonth + " DESC";

            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery != null && loQuery.IsOpened)
            {
                loQuery.MoveFirst();

                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    sb.Append("<tr>");

                    sb.Append("<td align='left'>");
                    sb.Append((i + 1) + "");
                    sb.Append("</td>");

                    // 市场名称
                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketEvalRpt_info.cMarketName));
                    sb.Append("</td>");

                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketEvalRpt_info.cMarketTypeName));
                    sb.Append("</td>");

                    sb.Append("<td align='left'>");
                    sb.Append(loQuery.GetString(vwMarketEvalRpt_info.cRegionName));
                    sb.Append("</td>");

                    // 月度
                    string strYear = loQuery.GetString(vwMarketEvalRpt_info.cYear);
                    string strMonth = loQuery.GetString(vwMarketEvalRpt_info.cMonth);
                    sb.Append("<td align='left'>");
                    sb.Append(strYear + "年" + strMonth + "月");
                    sb.Append("</td>");

                    sb.Append("<td align='left'>");
                    int liScore = loQuery.GetInt(vwMarketEvalRpt_info.cTotalScore);
                    sb.Append(liScore.ToString());
                    sb.Append("</td>");

                    sb.Append("<td align='left'>");
                   
                    sb.Append(MarketEvalGrade_info.GetRankByScore(liScore));
                    sb.Append("</td>");


                  


                    sb.Append("</tr>");
                    loQuery.MoveNext();
                }
            }

            ViewBag.TableMsg = sb.ToString();
            return View();
        }
        #endregion

        public ActionResult MajorQuoteTrendHighCharts()
        {

            return View();
        }

    }
}