using DBUtility;
using foda;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Web;

/// <summary>
/// Function 的摘要说明
/// </summary>
public class Function
{
    /// <summary>
    /// 获取页面所需数据
    /// </summary>
    /// <returns></returns>
    public static List<Model> GetData()
    {
        List<Model> list = new List<Model>();

        try
        {
            string sql = @"
                                  select a.CylinderNo, d.StationNo, b.BarCode, b.Time1
                                  from tb_Cylinder a
                                  left join tb_CylinderAndRaw b
                                  on a.CylinderID = b.CylinderID and b.Time2 is null
                                  left join tb_StationAndCylinder c
                                  on a.CylinderID = c.CylinderID and c.Time2 is null
                                  left join tb_Station d 
                                  on c.StationID = d.StationID
                                  --WHERE datediff(dd,b.Time1,GETDATE())=0 
                                  order by a.CylinderNo
                            ";
            DataTable dt1 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            if (dt1 != null && dt1.Rows.Count > 0)
            {
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    string stockNo = "";
                    string batchNo = "";
                    string partNo = "";
                    GetCode(dt1.Rows[i]["BarCode"].ToString(), out stockNo, out batchNo, out partNo);

                    DataTable dt2 = new DataTable();
                    if (!string.IsNullOrWhiteSpace(stockNo))
                    {
                        string sql2 = " select PartName, ProductName from tb_Product where StockNo = '" + stockNo.Trim() + "' ";
                        dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                    }
                    else if (!string.IsNullOrWhiteSpace(partNo))
                    {
                        string sql2 = " select PartName, ProductName from tb_Product where PartNo = '" + partNo.Trim() + "' ";
                        dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                    }

                    Model model = new Model();
                    model.Drum = dt1.Rows[i]["CylinderNo"].ToString();
                    model.Station = dt1.Rows[i]["StationNo"].ToString();
                    model.BatchNo = batchNo;
                    model.Time1 = dt1.Rows[i]["Time1"].ToString();
                    if (dt2 != null && dt2.Rows.Count > 0)
                    {
                        model.MaterialName = dt2.Rows[0]["ProductName"].ToString();
                        //model.ProductName = dt2.Rows[0]["PartName"].ToString();
                    }

                    DataTable dt_product = GetProductName(model.Station);
                    if (dt_product != null && dt_product.Rows.Count > 0)
                    {
                        model.ProductName = dt_product.Rows[0]["Plan"].ToString();
                    }

                    list.Add(model);
                }
            }

            return list;
        }
        catch (Exception ex)
        {
            LogHelper.WriteLogManager(ex);
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            return list;
        }
    }

    /// <summary>
    /// 根据注塑机查询计划要生产的产品信息
    /// </summary>
    /// <param name="machineCode"></param>
    /// <returns></returns>
    private static DataTable GetProductName(string machineCode)
    {
        DataTable res = new DataTable();
        try
        {
            string sql = @"                           
                                select top 1 ProductName as [Plan] from tb_Product where StockNo = (
                                select top 1 StockNo from tb_InjectionPlan where StationID = ( 
                                        select StationID from tb_Station where StationNo = '" + machineCode + @"' ) 
										AND (IsFinish is null or IsFinish = 0) 
										ORDER BY BeginTime ASC
										)
                            ";
            res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);

            if (res == null || res.Rows.Count < 1)
            {
                string sql2 = @" select top 1 ProductName as [Plan] from tb_Product where PartNo = (
                                        select top 1 PartNo from tb_InjectionPlan where StationID = ( 
                                        select StationID from tb_Station where StationNo = '" + machineCode + @"' ) 
										AND (IsFinish is null or IsFinish = 0) 
										ORDER BY BeginTime ASC
										)
                                ";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
            }

            return res;
        }
        catch (Exception ex)
        {
            LogHelper.WriteLogManager(ex);
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            return res;
        }
    }

    /// <summary>
    /// 解析条码（一维码返回存货代码，二维码返回零件号）
    /// </summary>
    /// <param name="code">条码</param>
    /// <param name="stockNo">存货代码</param>
    /// <param name="batchNo">批次</param>
    /// /// <param name="partNo">零件号</param>
    public static void GetCode(string code, out string stockNo, out string batchNo, out string partNo)
    {
        //解析塑料粒子条码，长度为20的为一维码22000000821906090201，否则为二维码
        //二维码样例Z-340.180411.000001;5000;S35001;20180411;P1710401.[#Line#];180411;
        //第一个分号之前的数据，即Z-340.180411.000001; Z-340为零件号，180411为批次号，000001为流水号
        //一维码前十位为零件号，tb_Product PartNo，11~16位为批次 

        stockNo = "";     //存货代码
        batchNo = "";    //批次
        partNo = "";    //零件号
        try
        {
            if (code.Contains(".") == false)
            {
                //一维码
                if (code.Length > 9)
                {
                    stockNo = code.Substring(0, 10);
                    batchNo = code.Substring(10, 6);
                }
            }
            else
            {
                //二维码
                string[] strs = code.Split(';');
                if (strs.Length > 0)
                {
                    string str = strs[0];
                    string[] props = str.Split('.');
                    if (props.Length >= 3)
                    {
                        partNo = props[0];
                        batchNo = props[1];
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogHelper.WriteLogManager(ex);
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
        }
    }

    /// <summary>
    /// 注塑车间计划看板数据
    /// lx 20190531
    /// </summary>
    /// <returns></returns>
    public static string GetPlanTable()
    {
        string res = "";
        PlanModel model = new PlanModel();
        try
        {
            //string stationNo = ConfigurationManager.AppSettings["StationNo"].ToString();

            #region 获取端口号，查工位号--注销

            //int ipPort = HttpContext.Current.Request.Url.Port;
            //string sqlStation = @" select StationNo from tb_PlanScreenConfig where IP = '" + ipPort + @"' ";
            //string stationNo = "";
            //object aa = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, CommandType.Text, sqlStation, null);
            //if (aa != null)
            //{
            //    stationNo = aa.ToString();
            //}

            #endregion

            #region 工位号

            string stationNo = ConfigurationManager.AppSettings["StationNo"].ToString();
            model.Station = stationNo;

            #endregion

            string stockNo = "";
            string partNo = "";
            string stockNosql = @" select top 1 StockNo,PartNo from tb_InjectionPlan where StationID = ( 
                                        select StationID from tb_Station where StationNo = '" + stationNo + @"' ) 
										AND (IsFinish IS NULL OR IsFinish = 0)
										ORDER BY BeginTime ASC ";
            DataTable dtt = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, stockNosql, null);
            if (dtt != null && dtt.Rows.Count > 0)
            {
                stockNo = dtt.Rows[0]["StockNo"].ToString();
                partNo = dtt.Rows[0]["PartNo"].ToString();
            }
            if (stockNo.Contains(","))
            {
                //生产多个产品
                #region 生产计划

                model.PartNo = dtt.Rows[0]["PartNo"].ToString();

                #endregion

                #region 产品信息

                model.ProductName = DF.GetProNameBySP(stockNo, partNo);

                #endregion

                #region 数量

                #region 计划数量

                #region 查询StationID

                string stationID = "";
                string sqlStationID = " select StationID from tb_Station where StationNo = '" + stationNo + "' ";
                stationID = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, CommandType.Text, sqlStationID, null).ToString();

                #endregion

                DataTable dt3 = new DataTable();
                string sql3 = "";
                sql3 = @" SELECT InjectionPlanID, PlanCount FROM tb_InjectionPlan WHERE StationID = '" + stationID + @"' AND ( StockNo = '" + stockNo +
                            "' OR PartNo = '" + partNo + "' ) AND (IsFinish is null or IsFinish = 0) ORDER BY BeginTime ASC ";
                dt3 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql3, null);
                string injectionPlanID = "";
                if (dt3 != null && dt3.Rows.Count > 0)
                {
                    model.PlanCount = dt3.Rows[0]["PlanCount"].ToString();
                    injectionPlanID = dt3.Rows[0]["InjectionPlanID"].ToString();
                }
                #endregion

                #region 完成数量等

                string[] stocks = stockNo.Split(',');
                string[] parts = partNo.Split(',');

                DataTable dt4 = new DataTable();
                string sql4 = "";
                DataTable dt5 = new DataTable();
                string sql5 = "";

                string date = DateTime.Now.ToString("yyyy-MM-dd");
                if (DateTime.Now.Hour >= 0 && DateTime.Now.Hour < 8)
                {
                    date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                }

                sql4 = @"   SELECT ISNULL(SUM(ISNULL(ProductCount,0)),0) ProductCount, ISNULL(SUM(ISNULL(BadCount,0)),0) BadCount
                                FROM tb_Product_Injection
                                WHERE StationID = '" + stationID + @"'
                                AND (StockNo = '" + stocks[0] + @"' OR PartNo = '" + parts[0] + @"')
                                AND ProductDate = '" + date + @"'
                                AND ClassName = '" + GetWorkClass() + @"'
                                AND PlanID = '" + injectionPlanID + @"'
                            ";
                dt4 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql4, null);

                sql5 = @"   SELECT ISNULL(SUM(ISNULL(ProductCount,0)),0) ProductCount, ISNULL(SUM(ISNULL(BadCount,0)),0) BadCount
                                FROM tb_Product_Injection
                                WHERE StationID = '" + stationID + @"'
                                AND (StockNo = '" + stocks[1] + @"' OR PartNo = '" + parts[1] + @"')
                                AND ProductDate = '" + date + @"'
                                AND ClassName = '" + GetWorkClass() + @"'
                                AND PlanID = '" + injectionPlanID + @"'
                            ";
                dt5 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql5, null);

                if (dt4 != null && dt4.Rows.Count > 0)
                {
                    int prca = 0; int badca = 0; int prcb = 0; int badcb = 0;
                    Int32.TryParse(dt4.Rows[0]["ProductCount"].ToString(), out prca);
                    Int32.TryParse(dt4.Rows[0]["BadCount"].ToString(), out badca);
                    if (dt5 != null && dt5.Rows.Count > 0)
                    {
                        Int32.TryParse(dt5.Rows[0]["ProductCount"].ToString(), out prcb);
                        Int32.TryParse(dt5.Rows[0]["BadCount"].ToString(), out badcb);
                    }
                    model.CompleteCount = (prca + prcb).ToString();
                    model.PassCount = (prca + prcb - badca - badcb).ToString();

                    int passc = 0;
                    double compc = 0.0;
                    Int32.TryParse(model.PassCount, out passc);
                    Double.TryParse(model.CompleteCount, out compc);
                    if (compc == 0.0)
                    {
                        model.CompleteRate = "0.0%";
                    }
                    else
                    {
                        model.CompleteRate = (passc / compc * 100).ToString("0.00") + "%";
                    }
                }

                #endregion

                #endregion

                string sql = @"
                            select a.CylinderNo, d.StationNo, d.StationID, b.BarCode, b.Time1
                                  from tb_Cylinder a
                                  left join tb_CylinderAndRaw b
                                  on a.CylinderID = b.CylinderID and b.Time2 is null
                                  left join tb_StationAndCylinder c
                                  on a.CylinderID = c.CylinderID and c.Time2 is null
                                  left join tb_Station d 
                                  on c.StationID = d.StationID
                                  where d.StationNo = '" + stationNo + @"'
                        ";
                DataTable dt = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);

                if (dt != null && dt.Rows.Count > 0)
                {
                    model.Drum = dt.Rows[0]["CylinderNo"].ToString();
                    model.Time1 = dt.Rows[0]["Time1"].ToString();

                    string batchNo = "";
                    GetCode(dt.Rows[0]["BarCode"].ToString(), out stockNo, out batchNo, out partNo);

                    model.BatchNo = batchNo;

                    #region 查询原料信息

                    DataTable dt2 = new DataTable();
                    string sql2 = "";
                    sql2 = " select PartNo, PartName, ProductName from tb_Product where StockNo = '" + stocks[0] + "' or PartNo = '" + parts[0] + "'  ";
                    dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                    if (dt2 != null && dt2.Rows.Count > 0)
                    {
                        model.MaterialName = dt2.Rows[0]["ProductName"].ToString();

                        sql2 = " select PartNo, PartName, ProductName from tb_Product where StockNo = '" + stocks[1] + "' or PartNo = '" + parts[1] + "'  ";
                        dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                        model.MaterialName = model.MaterialName + "," + dt2.Rows[0]["ProductName"].ToString();
                    }

                    #endregion

                }
            }
            else
                {
                    //生产一个产品
                    DataTable dtPlan = new DataTable();

                    #region 查询生产计划
                    string sqlPlan1 = @"                           
                                select top 1 ProductName as [Plan],StockNo,PartNo from tb_Product where StockNo = (
                                select top 1 StockNo from tb_InjectionPlan where StationID = ( 
                                        select StationID from tb_Station where StationNo = '" + stationNo + @"' ) 
										AND (IsFinish IS NULL OR IsFinish = 0)
										ORDER BY BeginTime ASC
										)
                            ";
                    dtPlan = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sqlPlan1, null);

                    if (dtPlan == null || dtPlan.Rows.Count < 1)
                    {
                        string sqlPlan2 = @" select top 1 ProductName as [Plan],StockNo,PartNo from tb_Product where PartNo = (
                                        select top 1 PartNo from tb_InjectionPlan where StationID = ( 
                                        select StationID from tb_Station where StationNo = '" + stationNo + @"' ) 
										AND (IsFinish IS NULL OR IsFinish = 0)
										ORDER BY BeginTime ASC
										)
                                ";
                        dtPlan = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sqlPlan2, null);
                    }
                    if (dtPlan != null && dtPlan.Rows.Count > 0)
                    {
                        model.PartNo = dtPlan.Rows[0]["PartNo"].ToString();
                        stockNo = dtPlan.Rows[0]["StockNo"].ToString();
                        partNo = dtPlan.Rows[0]["PartNo"].ToString();
                    }

                    #endregion

                    #region 查询产品信息

                    DataTable dtProduct = GetProductName(stationNo);
                    if (dtProduct != null && dtProduct.Rows.Count > 0)
                    {
                        model.ProductName = dtProduct.Rows[0][0].ToString();
                    }
                    #endregion

                    #region 查询数量

                    #region 查询StationID

                    string stationID = "";
                    string sqlStationID = " select StationID from tb_Station where StationNo = '" + stationNo + "' ";
                    stationID = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, CommandType.Text, sqlStationID, null).ToString();

                    #endregion


                    #region 计划数量
                    DataTable dt3 = new DataTable();
                    string sql3 = "";
                    if (!string.IsNullOrWhiteSpace(stockNo))
                    {
                        sql3 = @" SELECT InjectionPlanID, PlanCount FROM tb_InjectionPlan WHERE StationID = '" + stationID + @"' AND StockNo = '" + stockNo +
                                "' AND (IsFinish is null or IsFinish = 0) ORDER BY BeginTime ASC ";
                    }
                    else if (!string.IsNullOrWhiteSpace(partNo))
                    {
                        sql3 = @" SELECT InjectionPlanID, PlanCount FROM tb_InjectionPlan WHERE StationID = '" + stationID + @"' AND PartNo = '" + partNo +
                                "' AND (IsFinish is null or IsFinish = 0) ORDER BY BeginTime ASC ";
                    }
                    dt3 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql3, null);
                    string injectionPlanID = "";
                    if (dt3 != null && dt3.Rows.Count > 0)
                    {
                        model.PlanCount = dt3.Rows[0]["PlanCount"].ToString();
                        injectionPlanID = dt3.Rows[0]["InjectionPlanID"].ToString();
                    }
                    #endregion

                    #region 完成数量等

                    DataTable dt4 = new DataTable();
                    string sql4 = "";

                    string date = DateTime.Now.ToString("yyyy-MM-dd");
                    if (DateTime.Now.Hour >= 0 && DateTime.Now.Hour < 8)
                    {
                        date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                    }

                    if (!string.IsNullOrWhiteSpace(stockNo))
                    {
                        sql4 = @"   SELECT ISNULL(SUM(ISNULL(ProductCount,0)),0) ProductCount, ISNULL(SUM(ISNULL(BadCount,0)),0) BadCount
                                FROM tb_Product_Injection
                                WHERE StationID = '" + stationID + @"'
                                AND StockNo = '" + stockNo + @"'
                                AND ProductDate = '" + date + @"'
                                AND ClassName = '" + GetWorkClass() + @"'
                                AND PlanID = '" + injectionPlanID + @"'
                            ";
                    }
                    else if (!string.IsNullOrWhiteSpace(partNo))
                    {
                        sql4 = @"   SELECT ISNULL(SUM(ISNULL(ProductCount,0)),0) ProductCount, ISNULL(SUM(ISNULL(BadCount,0)),0) BadCount
                                FROM tb_Product_Injection
                                WHERE StationID = '" + stationID + @"'
                                AND PartNo = '" + partNo + @"'
                                AND ProductDate = '" + date + @"'
                                AND ClassName = '" + GetWorkClass() + @"'
                                AND PlanID = '" + injectionPlanID + @"'
                            ";
                    }
                    dt4 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql4, null);
                    if (dt4 != null && dt4.Rows.Count > 0)
                    {
                        model.CompleteCount = dt4.Rows[0]["ProductCount"].ToString();


                        int prc = 0; int badc = 0;
                        Int32.TryParse(dt4.Rows[0]["ProductCount"].ToString(), out prc);
                        Int32.TryParse(dt4.Rows[0]["BadCount"].ToString(), out badc);
                        model.PassCount = (prc - badc).ToString();

                        int passc = 0;
                        double compc = 0.0;
                        Int32.TryParse(model.PassCount, out passc);
                        Double.TryParse(model.CompleteCount, out compc);
                        if (compc == 0.0)
                        {
                            model.CompleteRate = "0.0%";
                        }
                        else
                        {
                            model.CompleteRate = (passc / compc * 100).ToString("0.00") + "%";
                        }
                    }


                    #endregion

                    #region 查询剩余计划零件号和计划数量

                    //string sql_plan = @" select PartNo, PlanCount from tb_InjectionPlan where StationID = '" + dt.Rows[0]["StationID"].ToString() + "' AND (IsFinish is null or IsFinish = 0) ORDER BY BeginTime ASC ";
                    string sql_plan = @"
                                          SELECT  tb_Product.ProductName ProductName, PlanCount from tb_InjectionPlan 
                                          JOIN tb_Product ON tb_Product.StockNo = tb_InjectionPlan.StockNo
                                          WHERE StationID = '" + stationID + @"' AND (IsFinish is null or IsFinish = 0) ORDER BY BeginTime ASC
                                  ";
                    DataTable dt_plan = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql_plan, null);
                    if (dt_plan != null && dt_plan.Rows.Count > 0)
                    {
                        if (dt_plan.Rows.Count >= 2)
                        {
                            //model.PartNo2 = dt_plan.Rows[1]["PartNo"].ToString();
                            model.PartNo2 = dt_plan.Rows[1]["ProductName"].ToString();
                            model.PlanCount2 = dt_plan.Rows[1]["PlanCount"].ToString();
                        }
                        if (dt_plan.Rows.Count >= 3)
                        {
                            //model.PartNo3 = dt_plan.Rows[2]["PartNo"].ToString();
                            model.PartNo3 = dt_plan.Rows[2]["ProductName"].ToString();
                            model.PlanCount3 = dt_plan.Rows[2]["PlanCount"].ToString();
                        }
                    }

                    #endregion

                    #endregion

                    string sql = @"
                            select a.CylinderNo, d.StationNo, d.StationID, b.BarCode, b.Time1
                                  from tb_Cylinder a
                                  left join tb_CylinderAndRaw b
                                  on a.CylinderID = b.CylinderID and b.Time2 is null
                                  left join tb_StationAndCylinder c
                                  on a.CylinderID = c.CylinderID and c.Time2 is null
                                  left join tb_Station d 
                                  on c.StationID = d.StationID
                                  where d.StationNo = '" + stationNo + @"'
                        ";
                    DataTable dt = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        model.Drum = dt.Rows[0]["CylinderNo"].ToString();
                        model.Time1 = dt.Rows[0]["Time1"].ToString();

                        string batchNo = "";
                        GetCode(dt.Rows[0]["BarCode"].ToString(), out stockNo, out batchNo, out partNo);

                        model.BatchNo = batchNo;

                        #region 查询原料信息

                        DataTable dt2 = new DataTable();
                        string sql2 = "";
                        if (!string.IsNullOrWhiteSpace(stockNo))
                        {
                            sql2 = " select PartNo, PartName, ProductName from tb_Product where StockNo = '" + stockNo + "' ";
                        }
                        else if (!string.IsNullOrWhiteSpace(partNo))
                        {
                            sql2 = " select PartNo, PartName, ProductName from tb_Product where PartNo = '" + partNo + "' ";
                        }
                        dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                        if (dt2 != null && dt2.Rows.Count > 0)
                        {
                            model.MaterialName = dt2.Rows[0]["ProductName"].ToString();
                        }

                        #endregion

                        #region 存储报表数据

                        Function.InsertInjectPlanReport(injectionPlanID, model.ProductName, model.MaterialName, model.Drum, model.BatchNo, model.Time1);

                        #endregion
                    }
                }

                res = JSONHelper.ObjectToJSON(model);
                return res;
        }
        catch (Exception ex)
        {
            LogHelper.WriteLogManager(ex);
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            return res;
        }
    }
    

    public static void InsertInjectPlanReport(string injectionPlanID, string ProductName, string MaterialName, string Drum, string BatchNo, string Time1)
    {
        try
        {
            string sql = @"
                             IF NOT EXISTS ( SELECT * FROM tb_InjectPlanReport WHERE InjectionPlanID = '"+ injectionPlanID +@"' )
                              BEGIN
                              INSERT INTO tb_InjectPlanReport
                                       ([ID]
                                       ,[InjectionPlanID]
                                       ,[ProductName]
                                       ,[MaterialName]
                                       ,[Drum]
                                       ,[BatchNo]
                                       ,[Time1]
		                               )
		                               VALUES(
		                               (SELECT NEWID())
                                        ,'"+ injectionPlanID +@"'
                                        ,'"+ ProductName +@"'
                                        ,'"+ MaterialName +@"'
                                        ,'"+ Drum +@"'
                                        ,'"+ BatchNo +@"'
                                        ,'"+ Time1 + @"'
		                               )
                             END
                             else
                             begin 
                                delete from tb_InjectPlanReport where InjectionPlanID = '"+ injectionPlanID +@"'
                                INSERT INTO tb_InjectPlanReport
                                       ([ID]
                                       ,[InjectionPlanID]
                                       ,[ProductName]
                                       ,[MaterialName]
                                       ,[Drum]
                                       ,[BatchNo]
                                       ,[Time1]
		                               )
		                               VALUES(
		                               (SELECT NEWID())
                                        ,'" + injectionPlanID + @"'
                                        ,'" + ProductName + @"'
                                        ,'" + MaterialName + @"'
                                        ,'" + Drum + @"'
                                        ,'" + BatchNo + @"'
                                        ,'" + Time1 + @"'
		                               )
                             end
                        ";
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sql, null);
        }
        catch (Exception ex)
        {
            LogHelper.WriteLogManager(ex);
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
        }
    }

    public static StockModel getBar()
    {
        StockModel md = new StockModel();
        md.c1 = new List<int>();
        md.c2 = new List<int>();
        md.h1 = new List<string>();
        string sql = "";
        try
        {
            if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 07:30:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00"))
            {
//                sql =
//                  @"DECLARE @barcode nvarchar(100),
//                        @productInfo nvarchar(100),
//                        @createTime dateTime
//
//                        IF OBJECT_ID('TEMPDB..#a1') IS NOT NULL
//						DROP TABLE #a1
//                        create table #a1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
//                        --truncate table #c
//                        DECLARE cursor_name CURSOR FOR --定义游标
//                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00') AND pass=1 
//                            ORDER BY createTime DESC
//                        OPEN cursor_name --打开游标
//                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
//                        WHILE @@FETCH_STATUS = 0
//                            BEGIN
//		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
//		                        
//		                        insert into #a1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
//                                --PRINT '用户ID：'+@productInfo          
//                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
//                            END
//                        CLOSE cursor_name --关闭游标
//                        DEALLOCATE cursor_name --释放游标
//
//							SELECT COUNT(0) AS c1 ,
//                        (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:29:59.999'))  AND productInfo LIKE '%槛%')  c2,08 AS h
//                            FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:29:59.999')) AND productInfo LIKE '%保%' 
//                           UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:29:59.999')) AND productInfo LIKE '%槛%')  c2,09 AS h
//                            FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:29:59.999')) AND productInfo LIKE '%保%'
//                           UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:29:59.999')) AND productInfo LIKE '%槛%')  c2,10 AS h
//                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:29:59.999')) AND productInfo LIKE '%槛%' )  c2,11 AS h
//                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:29:59.999')) AND productInfo LIKE '%槛%' )  c2,12 AS h
//                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:29:59.999')) AND productInfo LIKE '%保%'
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:29:59.999')) AND productInfo LIKE '%槛%' )  c2,13 AS h
//                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:29:59.999')) AND productInfo LIKE '%槛%')  c2,14 AS h
//                          FROM #a1 WHERE ([createTime]BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:29:59.999')) AND productInfo LIKE '%槛%')  c2,15 AS h
//                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:29:59.999')) AND productInfo LIKE '%槛%')  c2,16 AS h
//                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:29:59.999')) AND productInfo LIKE '%槛%')  c2,17 AS h
//                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:29:59.999')) AND productInfo LIKE '%槛%' )  c2,18 AS h
//                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:29:59.999')) AND productInfo LIKE '%保%'  
//						  UNION ALL SELECT COUNT(0) AS c1 ,
//                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:29:59.999')) AND productInfo LIKE '%槛%')  c2,19 AS h
//                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:29:59.999')) AND productInfo LIKE '%保%' 
//						  ORDER BY h asc";

                sql =
                    @"DECLARE @barcode nvarchar(100),
                        @productInfo nvarchar(100),
                        @createTime dateTime,
                        @c1 NVARCHAR(10),
						@c2 NVARCHAR(10),
						@h NVARCHAR(5)

                        IF OBJECT_ID('TEMPDB..#a1') IS NOT NULL
						DROP TABLE #a1
                        create table #a1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
                        --truncate table #c
                        DECLARE cursor_name CURSOR FOR --定义游标
                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:01')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00') AND pass=1 
                            ORDER BY createTime DESC
                        OPEN cursor_name --打开游标
                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
		                        
		                        insert into #a1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
                                --PRINT '用户ID：'+@productInfo          
                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
                            END
                        CLOSE cursor_name --关闭游标
                        DEALLOCATE cursor_name --释放游标

						IF OBJECT_ID('TEMPDB..#a2') IS NOT NULL
						DROP TABLE #a2
                        CREATE TABLE #a2 (id INT IDENTITY(1,1)NOT NULL, c1 NVARCHAR(10),c2 NVARCHAR(10),h NVARCHAR(5))

						DECLARE aa CURSOR FOR --定义游标

							SELECT COUNT(0) AS c1 ,
                        (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:29:59.999'))  AND productInfo LIKE '%槛%')  c2,08 AS h
                            FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:29:59.999')) AND productInfo LIKE '%保%' 
                           UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:29:59.999')) AND productInfo LIKE '%槛%')  c2,09 AS h
                            FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 08:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:29:59.999')) AND productInfo LIKE '%保%'
                           UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:29:59.999')) AND productInfo LIKE '%槛%')  c2,10 AS h
                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 09:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:29:59.999')) AND productInfo LIKE '%槛%' )  c2,11 AS h
                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 10:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:29:59.999')) AND productInfo LIKE '%槛%' )  c2,12 AS h
                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 11:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:29:59.999')) AND productInfo LIKE '%保%'
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:29:59.999')) AND productInfo LIKE '%槛%' )  c2,13 AS h
                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 12:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:29:59.999')) AND productInfo LIKE '%槛%')  c2,14 AS h
                          FROM #a1 WHERE ([createTime]BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 13:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:29:59.999')) AND productInfo LIKE '%槛%')  c2,15 AS h
                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 14:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:29:59.999')) AND productInfo LIKE '%槛%')  c2,16 AS h
                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 15:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:29:59.999')) AND productInfo LIKE '%槛%')  c2,17 AS h
                          FROM #a1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 16:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:29:59.999')) AND productInfo LIKE '%槛%' )  c2,18 AS h
                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 17:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:29:59.999')) AND productInfo LIKE '%保%'  
						  UNION ALL SELECT COUNT(0) AS c1 ,
                            (SELECT COUNT(0)  FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:29:59.999')) AND productInfo LIKE '%槛%')  c2,19 AS h
                          FROM #a1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 18:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:29:59.999')) AND productInfo LIKE '%保%' 
						  ORDER BY h asc 
                           
						OPEN aa --打开游标
                        FETCH NEXT FROM aa INTO  @c1 ,@c2,@h  --抓取下一行游标数据
                        WHILE @@FETCH_STATUS = 0
							BEGIN
								INSERT INTO #a2(c1,c2,h) VALUES(@c1,@c2,@h)
								FETCH NEXT FROM aa INTO @c1,@c2,@h
							END   
						 CLOSE aa --关闭游标
                        DEALLOCATE aa --释放游标  

						SELECT * FROM #a2 ORDER BY id";

            }
            else
            {
                if (DateTime.Now > DateTime.Parse(DateTime.Now.ToShortDateString() + " 19:30:00") && DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59"))
                {
                    sql = @"DECLARE @barcode nvarchar(100),
                        @productInfo nvarchar(100),
                        @createTime dateTime

                        IF OBJECT_ID('TEMPDB..#b1') IS NOT NULL
						DROP TABLE #b1
                        create table #b1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
                        --truncate table #c
                        DECLARE cursor_name CURSOR FOR --定义游标
                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59.999') AND pass=1 
                            ORDER BY createTime DESC
                        OPEN cursor_name --打开游标
                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
		                        
		                        insert into #b1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
                                --PRINT '用户ID：'+@productInfo          
                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
                            END
                        CLOSE cursor_name --关闭游标
                        DEALLOCATE cursor_name --释放游标

							SELECT COUNT(*) AS c1 ,
                        (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999'))  AND productInfo LIKE '%槛%')  c2,20 AS h
                            FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999')) AND productInfo LIKE '%保%' 
                           UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999')) AND productInfo LIKE '%槛%')  c2,21 AS h
                            FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999')) AND productInfo LIKE '%保%' 
                           UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999')) AND productInfo LIKE '%槛%')  c2,22 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999')) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000') AND  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999')) AND productInfo LIKE '%槛%' )  c2,23 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000')  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999'))) AND productInfo LIKE '%槛%')  c2,00 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000')  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999'))) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,01 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999'))) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,02 AS h
                          FROM #b1 WHERE  ([createTime]BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999'))) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,03 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999'))) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,04 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999'))) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999'))) AND productInfo LIKE '%槛%')  c2,05 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999'))) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,06 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999'))) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,07 AS h
                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999'))) AND productInfo LIKE '%保%' ";
//                    sql = @"DECLARE @barcode nvarchar(100),
//                        @productInfo nvarchar(100),
//                        @createTime dateTime
//
//                        IF OBJECT_ID('TEMPDB..#b1') IS NOT NULL
//						DROP TABLE #b1
//                        create table #b1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
//                        --truncate table #c
//                        DECLARE cursor_name CURSOR FOR --定义游标
//                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:59:59.999') AND pass=1 
//                            ORDER BY createTime DESC
//                        OPEN cursor_name --打开游标
//                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
//                        WHILE @@FETCH_STATUS = 0
//                            BEGIN
//		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
//		                        
//		                        insert into #b1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
//                                --PRINT '用户ID：'+@productInfo          
//                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
//                            END
//                        CLOSE cursor_name --关闭游标
//                        DEALLOCATE cursor_name --释放游标
//
//							SELECT COUNT(*) AS c1 ,
//                        (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999'))  AND productInfo LIKE '%槛%')  c2,20 AS h
//                            FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999')) AND productInfo LIKE '%保%' 
//                           UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999')) AND productInfo LIKE '%槛%')  c2,21 AS h
//                            FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999')) AND productInfo LIKE '%保%' 
//                           UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999')) AND productInfo LIKE '%槛%')  c2,22 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999')) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000') AND  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999')) AND productInfo LIKE '%槛%' )  c2,23 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000') AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN  CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000')  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999'))) AND productInfo LIKE '%槛%')  c2,00 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000')  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999'))) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,01 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999'))) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,02 AS h
//                          FROM #b1 WHERE  ([createTime]BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999'))) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,03 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999'))) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,04 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999'))) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999'))) AND productInfo LIKE '%槛%')  c2,05 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999'))) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,06 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999'))) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999'))) AND productInfo LIKE '%槛%' )  c2,07 AS h
//                          FROM #b1 WHERE  ([createTime] BETWEEN DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000'))  AND DATEADD(DAY,1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999'))) AND productInfo LIKE '%保%' ";
                }
                else
                {
//                    sql = @"DECLARE @barcode nvarchar(100),
//                        @productInfo nvarchar(100),
//                        @createTime dateTime
//
//                        IF OBJECT_ID('TEMPDB..#c1') IS NOT NULL
//						DROP TABLE #c1
//                        create table #c1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
//                        --truncate table #c
//                        DECLARE cursor_name CURSOR FOR --定义游标
//                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000') AND pass=1 
//                            ORDER BY createTime DESC
//                        OPEN cursor_name --打开游标
//                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
//                        WHILE @@FETCH_STATUS = 0
//                            BEGIN
//		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
//		                        
//		                        insert into #c1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
//                                --PRINT '用户ID：'+@productInfo          
//                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
//                            END
//                        CLOSE cursor_name --关闭游标
//                        DEALLOCATE cursor_name --释放游标
//
//							SELECT COUNT(*) AS c1 ,
//                        (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999')))  AND productInfo LIKE '%槛%')  c2,20 AS h
//                            FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999'))) AND productInfo LIKE '%保%'
//                           UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999'))) AND productInfo LIKE '%槛%')  c2,21 AS h
//                            FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999'))) AND productInfo LIKE '%保%'
//                           UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999'))) AND productInfo LIKE '%槛%')  c2,22 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999'))) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN  DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999'))) AND productInfo LIKE '%槛%')  c2,23 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999'))) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN  DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999')) AND productInfo LIKE '%槛%')  c2,00 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999')) AND productInfo LIKE '%槛%')  c2,01 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999')) AND productInfo LIKE '%保%'
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999')) AND productInfo LIKE '%槛%')  c2,02 AS h
//                          FROM #c1 WHERE ([createTime]BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999')) AND productInfo LIKE '%槛%' )  c2,03 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999')) AND productInfo LIKE '%槛%')  c2,04 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999')) AND productInfo LIKE '%槛%')  c2,05 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999')) AND productInfo LIKE '%槛%')  c2,06 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999')) AND productInfo LIKE '%保%' 
//						  UNION all SELECT COUNT(*) AS c1 ,
//                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999')) AND productInfo LIKE '%槛%')  c2,07 AS h
//                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999')) AND productInfo LIKE '%保%' ";
                    sql = @"DECLARE @barcode nvarchar(100),
                        @productInfo nvarchar(100),
                        @createTime dateTime,
                        @c1 NVARCHAR(10),
						@c2 NVARCHAR(10),
						@h NVARCHAR(5)

                        IF OBJECT_ID('TEMPDB..#c1') IS NOT NULL
						DROP TABLE #c1
                        create table #c1 (BarCode nvarchar(100),productInfo nvarchar(100),createTime dateTime)
                        --truncate table #c
                        DECLARE cursor_name CURSOR FOR --定义游标
                            SELECT  barcode,CreateTime FROM dbo.tb_StockIn WHERE createTime BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:30:00.000') AND pass=1 
                            ORDER BY createTime DESC
                        OPEN cursor_name --打开游标
                        FETCH NEXT FROM cursor_name INTO  @barcode ,@createTime  --抓取下一行游标数据
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
		                        SELECT TOP 1 @productInfo=productInfo FROM dbo.tb_InspectResult WHERE barcode=@barcode ORDER BY createTime DESC
		                        
		                        insert into #c1(BarCode,ProductInfo,CreateTime) values(@barcode,@productInfo,@createTime)
                                --PRINT '用户ID：'+@productInfo          
                                FETCH NEXT FROM cursor_name INTO @barcode,@createTime
                            END
                        CLOSE cursor_name --关闭游标
                        DEALLOCATE cursor_name --释放游标

						IF OBJECT_ID('TEMPDB..#a2') IS NOT NULL
						DROP TABLE #a2
                        CREATE TABLE #a2 (id INT IDENTITY(1,1)NOT NULL, c1 NVARCHAR(10),c2 NVARCHAR(10),h NVARCHAR(5))

						DECLARE aa CURSOR FOR --定义游标
							SELECT COUNT(*) AS c1 ,
                        (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999')))  AND productInfo LIKE '%槛%')  c2,20 AS h
                            FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 19:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:29:59.999'))) AND productInfo LIKE '%保%'
                           UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999'))) AND productInfo LIKE '%槛%')  c2,21 AS h
                            FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 20:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:29:59.999'))) AND productInfo LIKE '%保%'
                           UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999'))) AND productInfo LIKE '%槛%')  c2,22 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 21:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:29:59.999'))) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN  DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999'))) AND productInfo LIKE '%槛%')  c2,23 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 22:30:00.000'))  AND DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:29:59.999'))) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN  DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999')) AND productInfo LIKE '%槛%')  c2,00 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN DATEADD(DAY,-1,CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 23:30:00.000'))  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999')) AND productInfo LIKE '%槛%')  c2,01 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 00:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:29:59.999')) AND productInfo LIKE '%保%'
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999')) AND productInfo LIKE '%槛%')  c2,02 AS h
                          FROM #c1 WHERE ([createTime]BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 01:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999')) AND productInfo LIKE '%槛%' )  c2,03 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 02:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999')) AND productInfo LIKE '%槛%')  c2,04 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 03:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999')) AND productInfo LIKE '%槛%')  c2,05 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 04:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999')) AND productInfo LIKE '%槛%')  c2,06 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 05:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:29:59.999')) AND productInfo LIKE '%保%' 
						  UNION all SELECT COUNT(*) AS c1 ,
                            (SELECT COUNT(*)  FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999')) AND productInfo LIKE '%槛%')  c2,07 AS h
                          FROM #c1 WHERE ([createTime] BETWEEN CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 06:30:00.000')  AND CONVERT(DATETIME,SUBSTRING(CONVERT(NVARCHAR(50),GETDATE(),120),1,10)+ ' 07:29:59.999')) AND productInfo LIKE '%保%' 
						  OPEN aa --打开游标
                        FETCH NEXT FROM aa INTO  @c1 ,@c2,@h  --抓取下一行游标数据
                        WHILE @@FETCH_STATUS = 0
							BEGIN
								INSERT INTO #a2(c1,c2,h) VALUES(@c1,@c2,@h)
								FETCH NEXT FROM aa INTO @c1,@c2,@h
							END   
						 CLOSE aa --关闭游标
                        DEALLOCATE aa --释放游标  

						SELECT * FROM #a2 ORDER BY id";
                }
            }

            DataTable dt = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    md.c1.Add(int.Parse(dt.Rows[i]["c1"].ToString()));
                    md.c2.Add(int.Parse(dt.Rows[i]["c2"].ToString()));
                    md.h1.Add(dt.Rows[i]["h"].ToString() + ":30");
                }
            }
            return md;
        }
        catch (Exception ex)
        {
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            return md;
        }

    }

    /// <summary>
    /// 获取班次，规定早8至晚8为A班
    /// </summary>
    /// <returns></returns>
    public static string GetWorkClass()
    {
        bool classA = IsBetweenTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "2019-06-12 08:00:00", "2019-06-12 20:00:00");
        if (classA)
        {
            return "A班";
        }
        else
        {
            return "B班";
        }
    }

    /// <summary>
    /// 判断传入时间是否在工作时间段内
    /// </summary>
    /// <param name="timeStr"></param>
    /// <param name="startTime"></param>
    /// <param name="endTime"></param>
    /// <returns></returns>
    public static bool IsBetweenTime(string timeStr, string startTime, string endTime)
    {
        //判断传入时间是否在工作时间段内
        try
        {
            TimeSpan startSpan = DateTime.Parse(startTime).TimeOfDay;
            TimeSpan endSpan = DateTime.Parse(endTime).TimeOfDay;

            DateTime t1 = Convert.ToDateTime(timeStr);
            TimeSpan dspNow = t1.TimeOfDay;
            if (dspNow > startSpan && dspNow < endSpan)
            {
                return true;
            }
            return false;
        }
        catch (Exception ex)
        {
            LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            return false;
        }
    }
}