|
|
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 YYEDIProdOrderDal : BaseApp
|
|
|
{
|
|
|
public YYEDIProdOrderDal(RemotingProxy remotingProxy)
|
|
|
: base(remotingProxy)
|
|
|
{
|
|
|
this.TABLE_NAME = "";
|
|
|
this.PK_NAME = "";
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 汇总
|
|
|
/// </summary>
|
|
|
/// <param name="aWhere"></param>
|
|
|
/// <returns></returns>
|
|
|
public DataTable GetYYEDIProdData(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));
|
|
|
//lvf 20210728 加入生产线显示
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" (SELECT DISTINCT
|
|
|
case when b.appoint_pdline = '100001' then 'SX12' when b.appoint_pdline = '100002' then 'DCY11' end as appoint_pdline
|
|
|
,a.billId,
|
|
|
CASE g.plantId WHEN '3320' THEN '大江东客户' WHEN '3302' THEN '余姚领克' ELSE '其他' END AS plantId,
|
|
|
e.type_name,a.csn,b.order_no,c.serial_number,f.part_no,f.part_no_3c,f.part_spec,d.model_name,a.vin,
|
|
|
CASE a.edi_type WHEN '0' THEN 'EDI抓取' WHEN '10' THEN 'EDI新增' ELSE '其他' END AS edi_type,
|
|
|
CASE a.enabled WHEN 'Y' THEN '未转生成订单' WHEN 'N' THEN '已转生产订单' ELSE '其他' END AS edi_enabled,
|
|
|
CASE WHEN c.work_flag=-1 AND c.current_status=0 THEN '未上线良品'
|
|
|
WHEN c.work_flag=0 AND c.current_status=0 THEN '上线良品'
|
|
|
WHEN c.work_flag=1 AND c.current_status=0 THEN '下线良品'
|
|
|
WHEN c.work_flag=2 AND c.current_status=0 THEN '发运良品'
|
|
|
WHEN c.work_flag=3 AND c.current_status=0 THEN '报废'
|
|
|
WHEN c.work_flag=-1 AND c.current_status=1 THEN '未上线不良'
|
|
|
WHEN c.work_flag=0 AND c.current_status=1 THEN '上线不良'
|
|
|
WHEN c.work_flag=1 AND c.current_status=1 THEN '下线不良'
|
|
|
WHEN c.work_flag=2 AND c.current_status=1 THEN '退回不良'
|
|
|
WHEN c.work_flag=3 AND c.current_status=1 THEN '报废不良'
|
|
|
ELSE '其他'
|
|
|
END AS flag,
|
|
|
CASE a.status WHEN '0' THEN '未匹配配置' WHEN '10' THEN '已匹配配置' WHEN '20' THEN '已匹配配置' WHEN '30' THEN '已发运' WHEN '40' THEN '已解锁发运' END AS status,
|
|
|
is_production,is_shipping,
|
|
|
a.create_ymd+' '+a.create_hms AS ediTime, b.create_ymd+' '+b.create_hms AS prodTime
|
|
|
FROM dbo.magne_edi_detail(NOLOCK) a
|
|
|
LEFT JOIN dbo.g_stock_order(NOLOCK) b ON a.csn=edi_id
|
|
|
INNER JOIN dbo.g_sn_status(NOLOCK) c ON b.order_no=c.workorder AND b.model_id=c.model_id
|
|
|
LEFT JOIN dbo.sys_model(NOLOCK) d ON b.model_id=d.model_id
|
|
|
LEFT JOIN dbo.sys_model_type(NOLOCK) e ON d.model_type_id=e.type_id
|
|
|
LEFT JOIN dbo.sys_part(NOLOCK) f ON c.part_id=f.part_id
|
|
|
LEFT JOIN dbo.magna_edi(NOLOCK) g ON a.billId=g.billId
|
|
|
WHERE 1=1 and f.enabled='Y' and a.supplierId not in ('993311') " + aWhere + " ) aa "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" * "));
|
|
|
parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderColumn", " csn "));
|
|
|
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(" 1=1 " );
|
|
|
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>
|
|
|
/// <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);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|