|
|
using System;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Linq;
|
|
|
using System.Text;
|
|
|
using Estsh.Web.Util;
|
|
|
using ApServerProvider;
|
|
|
using System.Collections;
|
|
|
using System.Data;
|
|
|
using DbCommon;
|
|
|
|
|
|
namespace Estsh.Core.Repositories
|
|
|
{
|
|
|
/***************************************************************************************************
|
|
|
*
|
|
|
* 作者：任华松
|
|
|
* 创建时间：2013.07.08
|
|
|
* 描述：产品下线未发运查询DAL层
|
|
|
* 修改日志：
|
|
|
*
|
|
|
*
|
|
|
* *************************************************************************************************/
|
|
|
public class XTStockDataQueryDal : BaseApp
|
|
|
{
|
|
|
public XTStockDataQueryDal(RemotingProxy remotingProxy)
|
|
|
: base(remotingProxy)
|
|
|
{
|
|
|
this.TABLE_NAME = "";
|
|
|
this.PK_NAME = "";
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 汇总
|
|
|
/// </summary>
|
|
|
/// <param name="aWhere"></param>
|
|
|
/// <returns></returns>
|
|
|
public DataTable GetStockData(string aWhere, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
Hashtable ht = new Hashtable();
|
|
|
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", @" dbo.g_sn_status a
|
|
|
LEFT JOIN sys_part b ON a.part_id=b.part_id
|
|
|
LEFT JOIN dbo.sys_model c ON a.model_id=c.model_id
|
|
|
LEFT JOIN dbo.sys_pdline d ON a.pdline_id=d.pdline_id
|
|
|
LEFT JOIN dbo.sys_model_type f ON c.model_type_id=f.type_id "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"f.type_name,d.pdline_name,c.model_name,b.part_no,b.part_spec,SUM(1) qty "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderColumn", @"f.type_name,d.pdline_name,c.model_name,b.part_no,b.part_spec "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", @"type_name,pdline_name,c.model_name,b.part_no,b.part_spec "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@PageSize", pager.pageSize));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Group", 1));
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append(" a.work_flag = 1 and a.current_status=0 and a.prod_type IN ('S','E','C','X') " + aWhere);
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", SqlStringBuilder.ToString()));
|
|
|
Hashtable values = new Hashtable(2);
|
|
|
DataTable dt = new DataTable();
|
|
|
dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
|
|
|
ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
ht.Add("dataList", dataList);
|
|
|
ht.Add("totalCount", values["@TotalCount"].ToString());
|
|
|
totalCount = Convert.ToInt32(values["@TotalCount"]);
|
|
|
return dt;
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 明细
|
|
|
/// </summary>
|
|
|
/// <param name="aWhere"></param>
|
|
|
/// <returns></returns>
|
|
|
public DataTable GetDetailData(string aWhere, Pager pager, ref int totalCount2)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
Hashtable ht = new Hashtable();
|
|
|
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", @"dbo.g_sn_status a
|
|
|
LEFT JOIN sys_part b ON a.part_id=b.part_id
|
|
|
LEFT JOIN dbo.sys_model c ON a.model_id=c.model_id
|
|
|
LEFT JOIN dbo.sys_pdline d ON a.pdline_id=d.pdline_id
|
|
|
LEFT JOIN dbo.sys_model_type f ON c.model_type_id=f.type_id "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"a.workorder,f.type_name ,d.pdline_name,c.model_name ,b.part_no , b.part_spec ,a.serial_number ,CONVERT(varchar(100),a.out_pdline_time,20) AS update_date "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderColumn", @"a.out_pdline_time "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", @""));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@PageSize", pager.pageSize));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Group", 0));
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append(" a.work_flag = 1 and a.current_status=0 and a.prod_type IN ('S','E','C','X') " + aWhere);
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", SqlStringBuilder.ToString()));
|
|
|
Hashtable values = new Hashtable(2);
|
|
|
DataTable dt = new DataTable();
|
|
|
dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
|
|
|
ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
ht.Add("dataList", dataList);
|
|
|
ht.Add("totalCount", values["@TotalCount"].ToString());
|
|
|
totalCount2 = Convert.ToInt32(values["@TotalCount"]);
|
|
|
return dt;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 配置集合
|
|
|
/// </summary>
|
|
|
/// <returns>数据集</returns>
|
|
|
public ArrayList GetModelName(string typeName)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
string quernSen = "";
|
|
|
if (string.IsNullOrEmpty(typeName))
|
|
|
{
|
|
|
quernSen = @" SELECT DISTINCT model_name as [value],model_name as [key] FROM dbo.sys_model WHERE enabled = 'Y' ";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
quernSen = @" SELECT DISTINCT model_name as [value],model_name as [key] FROM dbo.sys_model a JOIN dbo.sys_model_type b ON a.model_type_id = b.type_id WHERE a.enabled = 'Y' AND b.type_name = '" + typeName + "' ";
|
|
|
}
|
|
|
DataTable dt = _remotingProxy.GetDataTable(quernSen);
|
|
|
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
///车型集合
|
|
|
/// </summary>
|
|
|
/// <returns>数据集</returns>
|
|
|
public ArrayList GetTypeName()
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select type_name as [value],type_name as [key] from sys_model_type where enabled='Y' ");
|
|
|
|
|
|
DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
|
|
|
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|