You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
466 lines
26 KiB
C#
466 lines
26 KiB
C#
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 SGAJISOrderQueryDal : BaseApp
|
|
{
|
|
public SGAJISOrderQueryDal(RemotingProxy remotingProxy)
|
|
: base(remotingProxy)
|
|
{ }
|
|
#if(false)
|
|
public DataSet GetAllData(string SGAJISOrder_noStart, string SGAJISorder_noEnd)
|
|
{
|
|
string strsql = "select param_value from sys_base where param_name='SGAOrderCount' and enabled='Y'";
|
|
|
|
object ddd = _remotingProxy.GetScalar(strsql);
|
|
|
|
int ParamValue = 0;
|
|
|
|
if (ddd != null)
|
|
{
|
|
ParamValue = Convert.ToInt32(ddd.ToString());
|
|
}
|
|
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
// SqlStringBuilder.Append("-- 统计表 ");
|
|
SqlStringBuilder.Append(" SELECT car_desc , ");
|
|
SqlStringBuilder.Append(" model_name , ");
|
|
if (ParamValue > 0)
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1)/@ParamValue AS qty ");
|
|
}
|
|
else
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1) AS qty ");
|
|
}
|
|
SqlStringBuilder.Append("FROM dbo.g_ga_order_detail ");
|
|
SqlStringBuilder.Append("WHERE right(order_no,10) BETWEEN right(@order_noStart,10) ");
|
|
SqlStringBuilder.Append(" AND right(@order_noEnd,10) ");
|
|
SqlStringBuilder.Append("GROUP BY car_desc , ");
|
|
SqlStringBuilder.Append(" model_name ");
|
|
SqlStringBuilder.Append("ORDER BY car_desc ");
|
|
//SqlStringBuilder.Append("-- 汇总表 ");
|
|
SqlStringBuilder.Append(" SELECT a.order_id , ");
|
|
SqlStringBuilder.Append(" a.order_no , ");
|
|
SqlStringBuilder.Append(" a.car_desc , ");
|
|
SqlStringBuilder.Append(" a.model_name , ");
|
|
SqlStringBuilder.Append(" a.car_no ,a.vin, ");
|
|
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
|
|
SqlStringBuilder.Append(" a.update_ymd + ' ' + a.update_hms as trans_time, ");
|
|
SqlStringBuilder.Append(" b.request_time , ");
|
|
SqlStringBuilder.Append(" CASE b.order_type ");
|
|
SqlStringBuilder.Append(" WHEN 'E' THEN 'Y' ");
|
|
SqlStringBuilder.Append(" ELSE 'N' ");
|
|
SqlStringBuilder.Append(" END AS order_type ");
|
|
SqlStringBuilder.Append("FROM dbo.g_ga_order_detail a ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
SqlStringBuilder.Append("WHERE right(a.order_no,10) BETWEEN right(@order_noStart,10) ");
|
|
SqlStringBuilder.Append(" AND right(@order_noEnd,10) ");
|
|
SqlStringBuilder.Append("ORDER BY a.order_id ");
|
|
// SqlStringBuilder.Append("-- 明细表 ");
|
|
SqlStringBuilder.Append("SELECT a.order_id , ");
|
|
SqlStringBuilder.Append(" a.order_no , ");
|
|
SqlStringBuilder.Append(" c.part_no , ");
|
|
SqlStringBuilder.Append(" d.part_no as yfjc_part_no, ");
|
|
SqlStringBuilder.Append(" d.part_spec , ");
|
|
SqlStringBuilder.Append(" a.car_desc , ");
|
|
SqlStringBuilder.Append(" a.model_name , ");
|
|
SqlStringBuilder.Append(" a.car_no ,a.vin, ");
|
|
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
|
|
SqlStringBuilder.Append(" b.request_time , ");
|
|
SqlStringBuilder.Append(" CASE b.order_type ");
|
|
SqlStringBuilder.Append(" WHEN 'E' THEN 'Y' ");
|
|
SqlStringBuilder.Append(" ELSE 'N' ");
|
|
SqlStringBuilder.Append(" END AS order_type , ");
|
|
SqlStringBuilder.Append(" e.serial_number , ");
|
|
SqlStringBuilder.Append(" f.enum_desc AS work_flag ");
|
|
SqlStringBuilder.Append(" FROM dbo.g_ga_order_detail a ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_ga_order_items c ON a.car_no = c.car_no ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON ( c.part_no = d.cust_part_no ) ");
|
|
SqlStringBuilder.Append(" OR ( c.part_no = d.part_no ) ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status e ON a.car_no = e.car_no ");
|
|
SqlStringBuilder.Append(" AND e.part_id = d.part_id ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum f ON e.work_flag = f.enum_value ");
|
|
SqlStringBuilder.Append(" AND f.enum_type = 'g_sn_work_flag' ");
|
|
SqlStringBuilder.Append("WHERE right(a.order_no,10) BETWEEN right(@order_noStart,10) ");
|
|
SqlStringBuilder.Append(" AND right(@order_noEnd,10) ");
|
|
SqlStringBuilder.Append(" ORDER BY a.order_id ");
|
|
|
|
|
|
|
|
// SqlStringBuilder.Append("-- ");内部统计表
|
|
SqlStringBuilder.Append(" SELECT ");
|
|
SqlStringBuilder.Append(" model_name , ");
|
|
if (ParamValue > 0)
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1)/@ParamValue AS qty ");
|
|
}
|
|
else
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1) AS qty ");
|
|
}
|
|
SqlStringBuilder.Append("FROM dbo.g_ga_order_detail ");
|
|
SqlStringBuilder.Append("WHERE right(order_no,10) BETWEEN right(@order_noStart,10) ");
|
|
SqlStringBuilder.Append(" AND right(@order_noEnd,10) ");
|
|
SqlStringBuilder.Append("GROUP BY ");
|
|
SqlStringBuilder.Append(" model_name ");
|
|
SqlStringBuilder.Append("ORDER BY model_name ");
|
|
|
|
// SqlStringBuilder.Append("-- 零件汇总表 ");
|
|
SqlStringBuilder.Append("SELECT ");
|
|
SqlStringBuilder.Append(" c.part_no , ");
|
|
SqlStringBuilder.Append(" d.part_no as yfjc_part_no, ");
|
|
SqlStringBuilder.Append(" d.part_spec ,COUNT(1) AS qty ");
|
|
SqlStringBuilder.Append(" FROM dbo.g_ga_order_detail a ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_ga_order_items c ON a.car_no = c.car_no ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON ( c.part_no = d.cust_part_no ) ");
|
|
SqlStringBuilder.Append(" OR ( c.part_no = d.part_no ) ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status e ON a.car_no = e.car_no ");
|
|
SqlStringBuilder.Append(" AND e.part_id = d.part_id ");
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum f ON e.work_flag = f.enum_value ");
|
|
SqlStringBuilder.Append(" AND f.enum_type = 'g_sn_work_flag' ");
|
|
SqlStringBuilder.Append("WHERE right(a.order_no,10) BETWEEN right(@order_noStart,10) ");
|
|
SqlStringBuilder.Append(" AND right(@order_noEnd,10) ");
|
|
SqlStringBuilder.Append(" GROUP BY c.part_no,d.part_no,d.part_spec ");
|
|
|
|
|
|
|
|
|
|
|
|
Hashtable ht = new Hashtable(2);
|
|
ht.Add("@order_noStart", SGAJISOrder_noStart);
|
|
ht.Add("@order_noEnd", SGAJISorder_noEnd);
|
|
|
|
if (ParamValue > 0)
|
|
{
|
|
ht.Add("@ParamValue", ParamValue);
|
|
}
|
|
|
|
return _remotingProxy.GetDataSet(SqlStringBuilder.ToString(), ht);
|
|
}
|
|
#endif
|
|
/// <summary>
|
|
/// 回的车型汇总
|
|
/// </summary>
|
|
/// <param name="SGAJISOrder_noStart"></param>
|
|
/// <param name="SGAJISorder_noEnd"></param>
|
|
/// <param name="dateStart"></param>
|
|
/// <param name="dateEnd"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetModelSummary(string SGAJISOrder_noStart, string SGAJISorder_noEnd, DateTime dateStart, DateTime dateEnd)
|
|
{
|
|
lock (_remotingProxy)
|
|
{
|
|
string strsql = "select param_value from sys_base where param_name='SGAOrderCount' and enabled='Y'";
|
|
object obj = _remotingProxy.GetScalar(strsql);
|
|
int ParamValue = 0;
|
|
if (obj != null)
|
|
{
|
|
ParamValue = Convert.ToInt32(obj.ToString());
|
|
}
|
|
StringBuilder SqlStringBuilder = new StringBuilder();
|
|
SqlStringBuilder.Append(" SELECT car_desc , ");
|
|
SqlStringBuilder.Append(" model_name , ");
|
|
if (ParamValue > 0)
|
|
{
|
|
SqlStringBuilder.AppendFormat(" COUNT(1)/{0} AS qty ", ParamValue);
|
|
}
|
|
else
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1) AS qty ");
|
|
}
|
|
SqlStringBuilder.Append("FROM dbo.g_ga_order_detail ");
|
|
SqlStringBuilder.Append("WHERE 1=1 ");
|
|
if (!string.IsNullOrEmpty(SGAJISOrder_noStart))
|
|
{
|
|
SqlStringBuilder.AppendFormat(" AND right(order_no,10) >= right('{0}',10) ", SGAJISOrder_noStart);
|
|
}
|
|
if (!string.IsNullOrEmpty(SGAJISorder_noEnd))
|
|
{
|
|
SqlStringBuilder.AppendFormat(" AND right(order_no,10) <= right('{0}',10) ", SGAJISorder_noEnd);
|
|
}
|
|
SqlStringBuilder.AppendFormat(" AND create_ymd >= '{0}' and create_ymd <= '{1}'", dateStart, dateEnd);
|
|
|
|
SqlStringBuilder.Append(" GROUP BY car_desc ,");
|
|
SqlStringBuilder.Append(" model_name ");
|
|
SqlStringBuilder.Append(" ORDER BY car_desc ");
|
|
|
|
return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获得配置汇总
|
|
/// </summary>
|
|
/// <param name="SGAJISOrder_noStart"></param>
|
|
/// <param name="SGAJISorder_noEnd"></param>
|
|
/// <param name="dateStart"></param>
|
|
/// <param name="dateEnd"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetConfigurationSummary(string SGAJISOrder_noStart, string SGAJISorder_noEnd, DateTime dateStart, DateTime dateEnd)
|
|
{
|
|
lock (_remotingProxy)
|
|
{
|
|
string strsql = "select param_value from sys_base where param_name='SGAOrderCount' and enabled='Y'";
|
|
object obj = _remotingProxy.GetScalar(strsql);
|
|
int ParamValue = 0;
|
|
if (obj != null)
|
|
{
|
|
ParamValue = Convert.ToInt32(obj.ToString());
|
|
}
|
|
StringBuilder SqlStringBuilder = new StringBuilder();
|
|
SqlStringBuilder.Append(" SELECT ");
|
|
SqlStringBuilder.Append(" model_name , ");
|
|
if (ParamValue > 0)
|
|
{
|
|
SqlStringBuilder.AppendFormat(" COUNT(1)/{0} AS qty ", ParamValue);
|
|
}
|
|
else
|
|
{
|
|
SqlStringBuilder.Append(" COUNT(1) AS qty ");
|
|
}
|
|
SqlStringBuilder.Append("FROM dbo.g_ga_order_detail ");
|
|
SqlStringBuilder.Append("WHERE 1=1 ");
|
|
if (!string.IsNullOrEmpty(SGAJISOrder_noStart))
|
|
{
|
|
SqlStringBuilder.AppendFormat(" AND right(order_no,10) >= right('{0}',10) ", SGAJISOrder_noStart);
|
|
}
|
|
if (!string.IsNullOrEmpty(SGAJISorder_noEnd))
|
|
{
|
|
SqlStringBuilder.AppendFormat(" AND right(order_no,10) <= right('{0}',10) ", SGAJISorder_noEnd);
|
|
}
|
|
SqlStringBuilder.AppendFormat(" AND create_ymd >= '{0}' and create_ymd <= '{1}'", dateStart, dateEnd);
|
|
SqlStringBuilder.Append(" GROUP BY ");
|
|
SqlStringBuilder.Append(" model_name ");
|
|
SqlStringBuilder.Append(" ORDER BY model_name ");
|
|
return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获得车号汇总
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="SGAJISOrder_noStart"></param>
|
|
/// <param name="SGAJISorder_noEnd"></param>
|
|
/// <param name="dateStart"></param>
|
|
/// <param name="dateEnd"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetCarNoSummary(ref Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd, DateTime dateStart, DateTime dateEnd)
|
|
{
|
|
lock (_remotingProxy)
|
|
{
|
|
StringBuilder StrColumn = new StringBuilder();
|
|
StrColumn.Append(" a.order_id , ");
|
|
StrColumn.Append(" a.order_no , ");
|
|
StrColumn.Append(" a.car_desc , ");
|
|
StrColumn.Append(" a.model_name , ");
|
|
StrColumn.Append(" a.car_no ,a.vin, ");
|
|
StrColumn.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
|
|
StrColumn.Append(" a.update_ymd + ' ' + a.update_hms as trans_time, ");
|
|
StrColumn.Append(" b.request_time , ");
|
|
StrColumn.Append(" CASE b.order_type ");
|
|
StrColumn.Append(" WHEN 'E' THEN 'Y' ");
|
|
StrColumn.Append(" ELSE 'N' ");
|
|
StrColumn.Append(" END AS order_type ");
|
|
|
|
StringBuilder strTable = new StringBuilder();
|
|
strTable.Append(" dbo.g_ga_order_detail a ");
|
|
strTable.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
|
|
StringBuilder strWhere = new StringBuilder();
|
|
strWhere.AppendFormat(" a.create_ymd >= '{0}' and a.create_ymd <= '{1}'", dateStart, dateEnd);
|
|
if (!string.IsNullOrEmpty(SGAJISOrder_noStart))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) >= right('{0}',10) ", SGAJISOrder_noStart);
|
|
}
|
|
if (!string.IsNullOrEmpty(SGAJISorder_noEnd))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) <= right('{0}',10) ", SGAJISorder_noEnd);
|
|
}
|
|
|
|
StringBuilder strOrder = new StringBuilder();
|
|
strOrder.Append(" a.order_id");
|
|
|
|
List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", strTable.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", StrColumn.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", strOrder.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", page.pageSize));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", page.pageNo));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));
|
|
Hashtable values = new Hashtable(2);
|
|
DataTable dt = new DataTable();
|
|
dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
|
|
if (values["@TotalCount"] != null && values["@TotalCount"] != DBNull.Value)
|
|
{
|
|
page.totalRows = Convert.ToInt32(values["@TotalCount"]);
|
|
}
|
|
return dt;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 获得零件号统计
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="SGAJISOrder_noStart"></param>
|
|
/// <param name="SGAJISorder_noEnd"></param>
|
|
/// <param name="dateStart"></param>
|
|
/// <param name="dateEnd"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetPartNoCount(ref Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd, DateTime dateStart, DateTime dateEnd)
|
|
{
|
|
lock (_remotingProxy)
|
|
{
|
|
StringBuilder StrColumn = new StringBuilder();
|
|
StrColumn.Append(" a.order_id , ");
|
|
StrColumn.Append(" a.order_no , ");
|
|
StrColumn.Append(" c.part_no , ");
|
|
StrColumn.Append(" d.part_no as yfjc_part_no, ");
|
|
StrColumn.Append(" d.part_spec , ");
|
|
StrColumn.Append(" a.car_desc , ");
|
|
StrColumn.Append(" a.model_name , ");
|
|
StrColumn.Append(" a.car_no ,a.vin, ");
|
|
StrColumn.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
|
|
StrColumn.Append(" b.request_time , ");
|
|
StrColumn.Append(" CASE b.order_type ");
|
|
StrColumn.Append(" WHEN 'E' THEN 'Y' ");
|
|
StrColumn.Append(" ELSE 'N' ");
|
|
StrColumn.Append(" END AS order_type , ");
|
|
StrColumn.Append(" e.serial_number , ");
|
|
StrColumn.Append(" f.enum_desc AS work_flag ");
|
|
|
|
StringBuilder strTable = new StringBuilder();
|
|
strTable.Append(" dbo.g_ga_order_detail a ");
|
|
strTable.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
strTable.Append(" LEFT JOIN dbo.g_ga_order_items c ON a.car_no = c.car_no ");
|
|
strTable.Append(" LEFT JOIN dbo.sys_part d ON ( c.part_no = d.cust_part_no ) ");
|
|
strTable.Append(" OR ( c.part_no = d.part_no ) ");
|
|
strTable.Append(" LEFT JOIN dbo.g_sn_status e ON a.car_no = e.car_no ");
|
|
strTable.Append(" AND e.part_id = d.part_id ");
|
|
strTable.Append(" LEFT JOIN dbo.sys_enum f ON e.work_flag = f.enum_value ");
|
|
strTable.Append(" AND f.enum_type = 'g_sn_work_flag' ");
|
|
|
|
StringBuilder strWhere = new StringBuilder();
|
|
strWhere.AppendFormat(" a.create_ymd >= '{0}' and a.create_ymd <= '{1}'", dateStart, dateEnd);
|
|
if (!string.IsNullOrEmpty(SGAJISOrder_noStart))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) >= right('{0}',10) ", SGAJISOrder_noStart);
|
|
}
|
|
if (!string.IsNullOrEmpty(SGAJISorder_noEnd))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) <= right('{0}',10) ", SGAJISorder_noEnd);
|
|
}
|
|
|
|
StringBuilder strOrder = new StringBuilder();
|
|
strOrder.Append(" a.order_id");
|
|
|
|
List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", strTable.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", StrColumn.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", strOrder.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", page.pageSize));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", page.pageNo));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));
|
|
Hashtable values = new Hashtable(2);
|
|
DataTable dt = new DataTable();
|
|
dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
|
|
if (values["@TotalCount"] != null && values["@TotalCount"] != DBNull.Value)
|
|
{
|
|
page.totalRows = Convert.ToInt32(values["@TotalCount"]);
|
|
}
|
|
return dt;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 获得零件号汇总
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="SGAJISOrder_noStart"></param>
|
|
/// <param name="SGAJISorder_noEnd"></param>
|
|
/// <param name="dateStart"></param>
|
|
/// <param name="dateEnd"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetPartNoSummary(ref Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd, DateTime dateStart, DateTime dateEnd)
|
|
{
|
|
lock (_remotingProxy)
|
|
{
|
|
StringBuilder StrColumn = new StringBuilder();
|
|
StrColumn.Append(" c.part_no , ");
|
|
StrColumn.Append(" d.part_no as yfjc_part_no, ");
|
|
StrColumn.Append(" d.part_spec ,COUNT(1) AS qty ");
|
|
|
|
|
|
StringBuilder strTable = new StringBuilder();
|
|
strTable.Append(" g_ga_order_detail a ");
|
|
strTable.Append(" LEFT JOIN dbo.g_ga_order b ON a.order_id = b.order_id ");
|
|
strTable.Append(" LEFT JOIN dbo.g_ga_order_items c ON a.car_no = c.car_no ");
|
|
strTable.Append(" LEFT JOIN dbo.sys_part d ON ( c.part_no = d.cust_part_no) ");
|
|
strTable.Append(" OR ( c.part_no = d.part_no ) ");
|
|
strTable.Append(" LEFT JOIN dbo.g_sn_status e ON a.car_no = e.car_no ");
|
|
strTable.Append(" AND e.part_id = d.part_id ");
|
|
strTable.Append(" LEFT JOIN dbo.sys_enum f ON e.work_flag = f.enum_value ");
|
|
strTable.Append(" AND f.enum_type = 'g_sn_work_flag' ");
|
|
|
|
StringBuilder strWhere = new StringBuilder();
|
|
strWhere.AppendFormat(" a.create_ymd >= '{0}' and a.create_ymd <= '{1}' ", dateStart, dateEnd);
|
|
if (!string.IsNullOrEmpty(SGAJISOrder_noStart))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) >= right('{0}',10) ", SGAJISOrder_noStart);
|
|
}
|
|
if (!string.IsNullOrEmpty(SGAJISorder_noEnd))
|
|
{
|
|
strWhere.AppendFormat(" AND right(a.order_no,10) <= right('{0}',10) ", SGAJISorder_noEnd);
|
|
}
|
|
|
|
StringBuilder strOrder = new StringBuilder();
|
|
strOrder.Append(" c.part_no ");
|
|
|
|
StringBuilder strGroup = new StringBuilder();
|
|
strGroup.Append(" c.part_no,d.part_no,d.part_spec ");
|
|
|
|
List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", strTable.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", StrColumn.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", strOrder.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", strGroup.ToString()));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", page.pageSize));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", page.pageNo));
|
|
parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 1));
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));
|
|
Hashtable values = new Hashtable(2);
|
|
DataTable dt = new DataTable();
|
|
dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
|
|
if (values["@TotalCount"] != null && values["@TotalCount"] != DBNull.Value)
|
|
{
|
|
page.totalRows = Convert.ToInt32(values["@TotalCount"]);
|
|
}
|
|
return dt;
|
|
}
|
|
}
|
|
}
|
|
}
|