using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.Collections;
using ApServerProvider;
using Estsh.Web.Util;
using DbCommon;
namespace Estsh.Core.Repositories
{
public class XTEDIReportDefineDal : BaseApp
{
public XTEDIReportDefineDal(RemotingProxy remotingProxy)
: base(remotingProxy)
{ }
///
/// 获取配置名称
///
///
public ArrayList GetPdlineName()
{
lock (_remotingProxy)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT pdline_name as [value],pdline_name as [key] FROM dbo.sys_pdline where enabled='Y' ");
DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
}
}
public ArrayList GetTerminalName()
{
lock (_remotingProxy)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT terminal_name as [value],terminal_name as [key] FROM dbo.sys_terminal where enabled='Y' ");
DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
}
}
//
/// 根据分页条件获取分页数据列表
///
public Hashtable getEDIRelationshipListByPage(int PageSize, int PageIndex, string txtStartTime)
{
lock (_remotingProxy)
{
Hashtable result = new Hashtable();
List parameters = new List();
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT * FROM ( SELECT a1.model_name , ");
//SqlStringBuilder.Append(" ISNULL(a1.qty, 0) AS '00:00:00-04:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a2.qty, 0) AS '02:00:00-04:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a3.qty, 0) AS '04:00:00-06:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a4.qty, 0) AS '06:00:00-08:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a5.qty, 0) AS '08:00:00-10:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a6.qty, 0) AS '10:00:00-12:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a7.qty, 0) AS '12:00:00-14:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a8.qty, 0) AS '14:00:00-16:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a9.qty, 0) AS '16:00:00-18:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a10.qty, 0) AS '18:00:00-20:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a11.qty, 0) AS '20:00:00-22:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a12.qty, 0) AS '22:00:00-24:00:00' ");
SqlStringBuilder.Append(" ISNULL(a1.qty, 0) AS 'A' , ");
SqlStringBuilder.Append(" ISNULL(a2.qty, 0) AS 'B' , ");
SqlStringBuilder.Append(" ISNULL(a3.qty, 0) AS 'C' , ");
SqlStringBuilder.Append(" ISNULL(a4.qty, 0) AS 'D' , ");
SqlStringBuilder.Append(" ISNULL(a5.qty, 0) AS 'E' , ");
SqlStringBuilder.Append(" ISNULL(a6.qty, 0) AS 'F' , ");
SqlStringBuilder.Append(" ISNULL(a7.qty, 0) AS 'G' , ");
SqlStringBuilder.Append(" ISNULL(a8.qty, 0) AS 'H' , ");
SqlStringBuilder.Append(" ISNULL(a9.qty, 0) AS 'I' , ");
SqlStringBuilder.Append(" ISNULL(a10.qty, 0) AS 'J' , ");
SqlStringBuilder.Append(" ISNULL(a11.qty, 0) AS 'K' , ");
SqlStringBuilder.Append(" ISNULL(a12.qty, 0) AS 'L' ");
SqlStringBuilder.Append(" FROM ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '00:00:00' AND '01:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a1 ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '02:00:00' AND '03:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a2 ON a2.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '04:00:00' AND '05:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a3 ON a3.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '06:00:00' AND '07:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a4 ON a4.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '08:00:00' AND '9:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a5 ON a5.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '10:00:00' AND '11:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a6 ON a6.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '12:00:00' AND '13:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a7 ON a7.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '14:00:00' AND '15:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a8 ON a8.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '16:00:00' AND '17:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a9 ON a9.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '18:00:00' AND '19:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a10 ON a10.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
//SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= '" + txtStartTime + "' ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '20:00:00' AND '21:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a11 ON a11.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '22:00:00' AND '23:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a12 ON a12.model_name = a1.model_name ");
SqlStringBuilder.Append(" ) AS aa ");
Hashtable values = new Hashtable(1);
values.Add("@txtStartTime", txtStartTime);
DataTable dt = new DataTable();
dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), values);
ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
result.Add("dataList", dataList);
result.Add("totalCount", dt.Rows.Count);
return result;
}
}
///
/// 插入菜单数据
///
///
///
public int saveModelType(Hashtable htParams)
{
lock (_remotingProxy)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.g_edi_model_relationship ");
SqlStringBuilder.Append(" ( model_id, edi_model_name ) ");
SqlStringBuilder.Append("VALUES ( @model_id, @edi_model_name ) ");
return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
}
}
///
/// 更新菜单数据
///
///
///
public int updateModelType(Hashtable htParams)
{
lock (_remotingProxy)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("UPDATE dbo.g_edi_model_relationship ");
SqlStringBuilder.Append("SET model_id = @model_id , ");
SqlStringBuilder.Append(" edi_model_name = @edi_model_name ");
SqlStringBuilder.Append("WHERE ruid = @ruid ");
return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
}
}
///
/// 删除菜单数据
///
///
///
public int deleteModelType(String ruid)
{
lock (_remotingProxy)
{
String delStr = "DELETE dbo.g_edi_model_relationship WHERE ruid=@ruid";
Hashtable htparams = new Hashtable();
htparams.Add("@ruid", ruid);
return _remotingProxy.ExecuteNonQuery(delStr, htparams);
}
}
public int GetModelID(string model_name)
{
lock (_remotingProxy)
{
string SqlString = "select model_id from dbo.sys_model where model_name=@model_name";
Hashtable Params = new Hashtable(1);
Params.Add("@model_name", model_name);
object obj = _remotingProxy.GetScalar(SqlString, Params);
return obj != null ? Convert.ToInt32(obj) : 0;
}
}
///
/// 根据分页条件获取分页数据列表(导出时使用)
///
public DataTable getShippingExport(int PageSize, int PageIndex, string txtStartTime)
{
//lock (_remotingProxy)
//{
// Hashtable result = new Hashtable();
// List parameters = new List();
// parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
// parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
// StringBuilder SqlStringBuilder = new StringBuilder(1024);
// SqlStringBuilder.Append(" ( SELECT a1.model_name , ");
// SqlStringBuilder.Append(" ISNULL(a1.qty, 0) AS '00:00:00-02:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a2.qty, 0) AS '02:00:00-04:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a3.qty, 0) AS '04:00:00-06:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a4.qty, 0) AS '06:00:00-08:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a5.qty, 0) AS '08:00:00-10:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a6.qty, 0) AS '10:00:00-12:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a7.qty, 0) AS '12:00:00-14:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a8.qty, 0) AS '14:00:00-16:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a9.qty, 0) AS '16:00:00-18:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a10.qty, 0) AS '18:00:00-20:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a11.qty, 0) AS '20:00:00-22:00:00' , ");
// SqlStringBuilder.Append(" ISNULL(a12.qty, 0) AS '22:00:00-24:00:00' ");
// SqlStringBuilder.Append(" FROM ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '00:00:00' AND '01:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a1 ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '02:00:00' AND '03:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a2 ON a2.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '04:00:00' AND '05:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a3 ON a3.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '06:00:00' AND '07:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a4 ON a4.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '08:00:00' AND '9:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a5 ON a5.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '09:00:00' AND '10:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a6 ON a6.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '10:00:00' AND '11:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a7 ON a7.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '12:00:00' AND '13:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a8 ON a8.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '14:00:00' AND '15:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a9 ON a9.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '16:00:00' AND '17:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a10 ON a10.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '18:00:00' AND '19:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a11 ON a11.model_name = a1.model_name ");
// SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
// SqlStringBuilder.Append(" COUNT(*) AS qty ");
// SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
// SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
// SqlStringBuilder.Append(" WHERE status = 1 ");
// SqlStringBuilder.Append(" AND a.model_id <> 0 ");
// SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
// SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= ( '2019-06-01' ) ");
// SqlStringBuilder.Append(" AND a.create_hms BETWEEN '20:00:00' AND '05:59:59' ");
// SqlStringBuilder.Append(" GROUP BY model_name ");
// SqlStringBuilder.Append(" ) AS a12 ON a12.model_name = a1.model_name ");
// SqlStringBuilder.Append(" WHERE ");
// SqlStringBuilder.Append(strWhere);
// SqlStringBuilder.Append(" ) AS aa ");
// parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", SqlStringBuilder.ToString()));
// parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a1.model_name "));
// parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " model_name "));
// parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
// parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
// parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
// parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
// parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere));
// Hashtable values = new Hashtable(2);
// DataTable dt = new DataTable();
// dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
// return dt;
Hashtable result = new Hashtable();
List parameters = new List();
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT * FROM ( SELECT a1.model_name , ");
//SqlStringBuilder.Append(" ISNULL(a1.qty, 0) AS '00:00:00-04:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a2.qty, 0) AS '02:00:00-04:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a3.qty, 0) AS '04:00:00-06:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a4.qty, 0) AS '06:00:00-08:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a5.qty, 0) AS '08:00:00-10:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a6.qty, 0) AS '10:00:00-12:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a7.qty, 0) AS '12:00:00-14:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a8.qty, 0) AS '14:00:00-16:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a9.qty, 0) AS '16:00:00-18:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a10.qty, 0) AS '18:00:00-20:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a11.qty, 0) AS '20:00:00-22:00:00' , ");
//SqlStringBuilder.Append(" ISNULL(a12.qty, 0) AS '22:00:00-24:00:00' ");
SqlStringBuilder.Append(" ISNULL(a1.qty, 0) AS 'A' , ");
SqlStringBuilder.Append(" ISNULL(a2.qty, 0) AS 'B' , ");
SqlStringBuilder.Append(" ISNULL(a3.qty, 0) AS 'C' , ");
SqlStringBuilder.Append(" ISNULL(a4.qty, 0) AS 'D' , ");
SqlStringBuilder.Append(" ISNULL(a5.qty, 0) AS 'E' , ");
SqlStringBuilder.Append(" ISNULL(a6.qty, 0) AS 'F' , ");
SqlStringBuilder.Append(" ISNULL(a7.qty, 0) AS 'G' , ");
SqlStringBuilder.Append(" ISNULL(a8.qty, 0) AS 'H' , ");
SqlStringBuilder.Append(" ISNULL(a9.qty, 0) AS 'I' , ");
SqlStringBuilder.Append(" ISNULL(a10.qty, 0) AS 'J' , ");
SqlStringBuilder.Append(" ISNULL(a11.qty, 0) AS 'K' , ");
SqlStringBuilder.Append(" ISNULL(a12.qty, 0) AS 'L' ");
SqlStringBuilder.Append(" FROM ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '00:00:00' AND '01:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a1 ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '02:00:00' AND '03:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a2 ON a2.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '04:00:00' AND '05:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a3 ON a3.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '06:00:00' AND '07:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a4 ON a4.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '08:00:00' AND '9:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a5 ON a5.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '10:00:00' AND '11:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a6 ON a6.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '12:00:00' AND '13:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a7 ON a7.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '14:00:00' AND '15:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a8 ON a8.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '16:00:00' AND '17:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a9 ON a9.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '18:00:00' AND '19:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a10 ON a10.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
//SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= '" + txtStartTime + "' ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '20:00:00' AND '21:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a11 ON a11.model_name = a1.model_name ");
SqlStringBuilder.Append(" LEFT JOIN ( SELECT model_name , ");
SqlStringBuilder.Append(" COUNT(*) AS qty ");
SqlStringBuilder.Append(" FROM dbo.g_edi AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id = b.model_id ");
SqlStringBuilder.Append(" WHERE status = 1 ");
SqlStringBuilder.Append(" AND a.model_id <> 0 ");
SqlStringBuilder.Append(" AND ERROR_MESSAGE IS NOT NULL ");
SqlStringBuilder.Append(" AND CONVERT(DATE, a.create_ymd) >= @txtStartTime ");
SqlStringBuilder.Append(" AND a.create_hms BETWEEN '22:00:00' AND '23:59:59' ");
SqlStringBuilder.Append(" GROUP BY model_name ");
SqlStringBuilder.Append(" ) AS a12 ON a12.model_name = a1.model_name ");
SqlStringBuilder.Append(" ) AS aa ");
Hashtable values = new Hashtable(1);
values.Add("@txtStartTime", txtStartTime);
DataTable dt = new DataTable();
dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), values);
ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
result.Add("dataList", dataList);
result.Add("totalCount", dt.Rows.Count);
return dt;
}
}
}