|
|
using System;
|
|
|
using System.Data;
|
|
|
using System.Text;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Data.SqlClient;
|
|
|
using ApServerProvider;
|
|
|
using DbCommon;
|
|
|
using System.Collections;
|
|
|
|
|
|
namespace Estsh.Core.Repositories
|
|
|
{
|
|
|
|
|
|
/**
|
|
|
* 客户产线对应关系
|
|
|
*
|
|
|
* NOAH
|
|
|
*
|
|
|
*/
|
|
|
public class SRMDefineDal : BaseApp
|
|
|
{
|
|
|
public SRMDefineDal(RemotingProxy remotingProxy)
|
|
|
: base(remotingProxy)
|
|
|
{
|
|
|
}
|
|
|
#region 成员方法
|
|
|
/// <summary>
|
|
|
/// 获得菜单列表数据
|
|
|
/// </summary>
|
|
|
public DataTable getList(string strWhere, string filedOrder)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select a.*,b.cust_pdline_name from sys_model_type a LEFT JOIN dbo.sys_cust_pdline b ON a.cust_pdline_id = b.cust_pdline_id ");
|
|
|
if (!strWhere.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" where " + strWhere);
|
|
|
}
|
|
|
if (filedOrder != null && !filedOrder.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" order by " + filedOrder);
|
|
|
}
|
|
|
return this._remotingProxy.GetDataTable(strSql.ToString());
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public DataTable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
if (strWhere.Trim() != "")
|
|
|
{
|
|
|
strWhere = strWhere.Remove(0, 6);//因为存储过程中已经有where，这里需要把之前加入的where去掉，这里截除前六个字符
|
|
|
}
|
|
|
|
|
|
OrderBy = " importNo DESC,crossingPointNo "; //排序
|
|
|
|
|
|
Hashtable Params = new Hashtable(6);
|
|
|
|
|
|
Params.Add("@TotalCount", 100);
|
|
|
Params.Add("@TotalPage", 100);
|
|
|
Params.Add("@GroupColumn", "");
|
|
|
Params.Add("@Table", "g_srm");
|
|
|
Params.Add("@Column", @"ruid,importNo,crossingPointNo,partNo,partName,vendorCode,vendorName,xuQiuQty,VIN,
|
|
|
Convert(varchar(20),dingDanTime,120) as dingDanTime ,dingDanNo,
|
|
|
Convert(varchar(20),zhiDanTime,120) as zhiDanTime,dingDanStatus,danJuPrint,
|
|
|
Convert(varchar(20),huiZhiTime,120) as huiZhiTime,Convert(varchar(20),updateTime,120) as updateTime,
|
|
|
serial_number,Convert(varchar(20),inTime,120) as inTime,rfid,CASE status WHEN 0 THEN '未校验' WHEN 1 THEN '已校验' END AS status,
|
|
|
CASE scan_status WHEN 0 THEN '未扫描' WHEN 1 THEN '已扫描' END AS scan_status,create_ymd+' '+create_hms AS importTime");
|
|
|
Params.Add("@PageSize", PageSize);
|
|
|
Params.Add("@CurrentPage", PageIndex);
|
|
|
Params.Add("@Condition", strWhere);
|
|
|
Params.Add("@OrderColumn", OrderBy);
|
|
|
Params.Add("@Group", 0);
|
|
|
|
|
|
String strProcedure = "EXEC Com_Pagination @TotalCount, @TotalPage, @Table, @Column,@OrderColumn,@GroupColumn,@PageSize, @CurrentPage,@Group,@Condition";
|
|
|
return _remotingProxy.GetDataTable(strProcedure, Params);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页记录总数
|
|
|
/// </summary>
|
|
|
public int getCountWhere(string strWhere)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select count(*) as Total FROM g_srm ");
|
|
|
if (strWhere.Trim() != "")
|
|
|
{
|
|
|
strSql.Append(" " + strWhere);
|
|
|
}
|
|
|
int i = 0;
|
|
|
DataTable dt = _remotingProxy.GetDataTable(strSql.ToString());
|
|
|
if (dt.Rows.Count > 0)
|
|
|
{
|
|
|
if (dt.Rows[0]["Total"].ToString() != "")
|
|
|
{
|
|
|
i = int.Parse(dt.Rows[0]["Total"].ToString());
|
|
|
}
|
|
|
}
|
|
|
return i;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 插入菜单数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int saveModelType(Hashtable htParams)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.sys_model_type(type_code,type_name,type_alias,type_desc,cust_pdline_id,create_userid,create_ymd,create_hms) values");
|
|
|
SqlStringBuilder.Append("(@type_code,@type_name,@type_alias,@type_desc,@cust_pdline_id,@create_userid,Convert(varchar(10),getdate(),23),Convert(varchar(10),getdate(),108))");
|
|
|
|
|
|
return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 更新菜单数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int updateModelType(Hashtable htParams)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("UPDATE sys_model_type SET ");
|
|
|
SqlStringBuilder.Append("type_code=@type_code,type_name=@type_name,type_alias=@type_alias,type_desc=@type_desc,cust_pdline_id=@cust_pdline_id,update_userid=@update_userid,update_ymd=Convert(varchar(10),getdate(),23),update_hms=Convert(varchar(10),getdate(),108) ");
|
|
|
SqlStringBuilder.Append("WHERE type_id=@type_id ");
|
|
|
|
|
|
return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 删除菜单数据
|
|
|
/// </summary>
|
|
|
/// <param name="type_id"></param>
|
|
|
/// <returns></returns>
|
|
|
public int deleteSRM(String ruid)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
//status=0 AND del ='N'已发运的不能删除，已删除的不显示
|
|
|
String delStr = @"UPDATE g_srm
|
|
|
SET status=1,del='Y',importNo=0,VIN = ( SELECT VIN
|
|
|
FROM dbo.g_srm
|
|
|
WHERE ruid = @ruid
|
|
|
) + '-DEL' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),121),'-',''),':',''),' ',''),'.','')
|
|
|
WHERE ruid = @ruid AND status=0 AND del ='N'";
|
|
|
Hashtable htparams = new Hashtable();
|
|
|
htparams.Add("@ruid", ruid);
|
|
|
return _remotingProxy.ExecuteNonQuery(delStr, htparams);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取VIN号
|
|
|
/// </summary>
|
|
|
/// <param name="VIN"></param>
|
|
|
/// <returns></returns>
|
|
|
public string GetVIN(string VIN)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
string SqlString = "SELECT VIN FROM dbo.g_srm WHERE VIN =@VIN";
|
|
|
|
|
|
Hashtable Params = new Hashtable(1);
|
|
|
|
|
|
Params.Add("@VIN", VIN);
|
|
|
|
|
|
object obj = _remotingProxy.GetScalar(SqlString, Params);
|
|
|
|
|
|
return obj != null ? Convert.ToString(obj) : null;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 客户零件号是否存在
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetCustPart(string custPart)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
string SqlString = "SELECT cust_part_no FROM dbo.sys_part WHERE part_type = 1 AND cust_part_no = @custPart";
|
|
|
Hashtable Params = new Hashtable(1);
|
|
|
|
|
|
Params.Add("@custPart", custPart);
|
|
|
|
|
|
object obj = _remotingProxy.GetScalar(SqlString, Params);
|
|
|
|
|
|
return obj != null ? Convert.ToString(obj) : null;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取系统最后一次导入序号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public int GetImportNo()
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
string SqlString = "SELECT ISNULL(MAX(importNo),0) FROM dbo.g_srm";
|
|
|
|
|
|
object obj = _remotingProxy.GetScalar(SqlString);
|
|
|
|
|
|
return obj != null ? Convert.ToInt32(obj) : 0;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 导入EXCEL时插入数据
|
|
|
/// </summary>
|
|
|
/// <param name="dt"></param>
|
|
|
/// <returns></returns>
|
|
|
public int InsertData(DataTable dt, int importNo, int userId, string pdlineOff)
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
List<String> SqlStrings = new List<string>(dt.Rows.Count);
|
|
|
|
|
|
List<String> ModelCache = new List<string>(dt.Rows.Count);
|
|
|
|
|
|
List<Hashtable> hs = new List<Hashtable>(dt.Rows.Count);
|
|
|
DataTable dataTime = getDataTime();
|
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
|
{
|
|
|
|
|
|
|
|
|
string CplID = dt.Rows[i][4].ToString().Trim();
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
|
|
|
|
|
|
SqlStringBuilder.Append(@"INSERT INTO dbo.g_srm
|
|
|
( importNo ,dingDanNo ,zhiDanTime ,dingDanTime ,peiSongType ,
|
|
|
dingDanStatus ,danJuPrint , factory ,vendorCode ,vendorName ,
|
|
|
partNo ,partName ,danWei ,zhuangPeiNo ,kuWei ,
|
|
|
daoKou ,xuQiuQty ,shouHuoQty ,line ,partTypeNo ,
|
|
|
partTypeName ,pickTime ,path ,jianYiDaoDaTime ,qiWangDaoDaTime ,
|
|
|
vendorShipTime ,firstCrossingPointTime ,chaiFenNo ,chaiFenPartNo ,chaiFenBoxNo ,
|
|
|
partQty ,crossingPointNo ,VIN ,daoHuoTime ,shouHuoRen ,
|
|
|
shouHuoSynTime ,huiZhiTime ,updateTime ,zhiDanRen ,enabled ,
|
|
|
planStatus ,shipStatus ,create_userid ,create_ymd ,create_hms,status,location_status
|
|
|
)");
|
|
|
SqlStringBuilder.Append(" values( ");
|
|
|
SqlStringBuilder.Append(@"@importNo ,@dingDanNo ,@zhiDanTime ,@dingDanTime ,@peiSongType ,
|
|
|
@dingDanStatus ,@danJuPrint , @factory ,@vendorCode ,@vendorName ,
|
|
|
@partNo ,@partName ,@danWei ,@zhuangPeiNo ,@kuWei ,
|
|
|
@daoKou ,@xuQiuQty ,@shouHuoQty ,@line ,@partTypeNo ,
|
|
|
@partTypeName ,@pickTime ,@path ,@jianYiDaoDaTime ,@qiWangDaoDaTime ,
|
|
|
@vendorShipTime ,@firstCrossingPointTime ,@chaiFenNo ,@chaiFenPartNo ,@chaiFenBoxNo ,
|
|
|
@partQty ,@crossingPointNo ,@VIN ,@daoHuoTime ,@shouHuoRen ,
|
|
|
@shouHuoSynTime ,@huiZhiTime ,@updateTime ,@zhiDanRen ,@enabled ,
|
|
|
@planStatus ,@shipStatus ,@create_userid ,@create_ymd ,@create_hms,@status,@location_status
|
|
|
)");
|
|
|
|
|
|
|
|
|
|
|
|
Hashtable values = new Hashtable(51);
|
|
|
values.Add("@importNo", importNo);
|
|
|
values.Add("@dingDanNo", dt.Rows[i][0].ToString());
|
|
|
values.Add("@zhiDanTime", dt.Rows[i][1].ToString());
|
|
|
values.Add("@dingDanTime", dt.Rows[i][2].ToString());
|
|
|
values.Add("@peiSongType", dt.Rows[i][3].ToString());
|
|
|
values.Add("@dingDanStatus", dt.Rows[i][4].ToString());
|
|
|
values.Add("@danJuPrint", dt.Rows[i][5].ToString());
|
|
|
values.Add("@factory", dt.Rows[i][6].ToString());
|
|
|
values.Add("@vendorCode", dt.Rows[i][7].ToString());
|
|
|
values.Add("@vendorName", dt.Rows[i][8].ToString());
|
|
|
values.Add("@partNo", dt.Rows[i][9].ToString());
|
|
|
values.Add("@partName", dt.Rows[i][10].ToString());
|
|
|
values.Add("@danWei", dt.Rows[i][11].ToString());
|
|
|
values.Add("@zhuangPeiNo", dt.Rows[i][12].ToString());
|
|
|
values.Add("@kuWei", dt.Rows[i][13].ToString());
|
|
|
values.Add("@daoKou", dt.Rows[i][14].ToString());
|
|
|
values.Add("@xuQiuQty", dt.Rows[i][15].ToString());
|
|
|
values.Add("@shouHuoQty", dt.Rows[i][16].ToString());
|
|
|
values.Add("@line", dt.Rows[i][17].ToString());
|
|
|
values.Add("@partTypeNo", dt.Rows[i][18].ToString());
|
|
|
values.Add("@partTypeName", dt.Rows[i][19].ToString());
|
|
|
values.Add("@pickTime", dt.Rows[i][20].ToString());
|
|
|
values.Add("@path", dt.Rows[i][21].ToString());
|
|
|
values.Add("@jianYiDaoDaTime", dt.Rows[i][22].ToString());
|
|
|
values.Add("@qiWangDaoDaTime", dt.Rows[i][23].ToString());
|
|
|
values.Add("@vendorShipTime", dt.Rows[i][24].ToString());
|
|
|
values.Add("@firstCrossingPointTime", dt.Rows[i][25].ToString());
|
|
|
values.Add("@chaiFenNo", dt.Rows[i][26].ToString());
|
|
|
values.Add("@chaiFenPartNo", dt.Rows[i][27].ToString());
|
|
|
values.Add("@chaiFenBoxNo", dt.Rows[i][28].ToString());
|
|
|
values.Add("@partQty", dt.Rows[i][29].ToString());
|
|
|
values.Add("@crossingPointNo", dt.Rows[i][30].ToString());
|
|
|
values.Add("@VIN", dt.Rows[i][31].ToString());
|
|
|
values.Add("@daoHuoTime", dt.Rows[i][32].ToString());
|
|
|
values.Add("@shouHuoRen", dt.Rows[i][33].ToString());
|
|
|
values.Add("@shouHuoSynTime", dt.Rows[i][34].ToString());
|
|
|
values.Add("@huiZhiTime", dt.Rows[i][35].ToString());
|
|
|
values.Add("@updateTime", dt.Rows[i][36].ToString());
|
|
|
values.Add("@zhiDanRen", dt.Rows[i][37].ToString());
|
|
|
|
|
|
values.Add("@enabled", "Y");
|
|
|
if (pdlineOff.Equals("1"))
|
|
|
{
|
|
|
values.Add("@planStatus", 0);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
values.Add("@planStatus", 1);
|
|
|
}
|
|
|
values.Add("@shipStatus", 0);
|
|
|
|
|
|
values.Add("@create_userid", userId);
|
|
|
|
|
|
|
|
|
values.Add("@create_ymd", dataTime.Rows[0][0].ToString());
|
|
|
values.Add("@create_hms", dataTime.Rows[0][1].ToString());
|
|
|
values.Add("@status", 0);
|
|
|
values.Add("@location_status", 0);
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
|
|
|
hs.Add(values);
|
|
|
}
|
|
|
if (pdlineOff.Equals("1"))
|
|
|
{
|
|
|
//生成排产计划
|
|
|
if (_remotingProxy.ExecuteSqlTransaction(SqlStrings, hs))
|
|
|
{
|
|
|
List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();
|
|
|
|
|
|
Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));
|
|
|
|
|
|
Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.sys_release_order_srm", Parameters);
|
|
|
|
|
|
if (!ht.ContainsKey("@tres"))
|
|
|
{
|
|
|
//导入成功释放失败
|
|
|
return 2;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
//导入成功并释放成功
|
|
|
return 1;
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
//导入失败
|
|
|
return 3;
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else if (pdlineOff.Equals("2"))
|
|
|
{
|
|
|
if (_remotingProxy.ExecuteSqlTransaction(SqlStrings, hs))
|
|
|
{
|
|
|
//只生成SRM发运计划
|
|
|
return 4;
|
|
|
}
|
|
|
|
|
|
}
|
|
|
return 3;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取时间
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public DataTable getDataTime()
|
|
|
{
|
|
|
lock (_remotingProxy)
|
|
|
{
|
|
|
string SqlString = "select Convert(varchar(10),getdate(),23),Convert(varchar(10),getdate(),108)";
|
|
|
|
|
|
|
|
|
|
|
|
return _remotingProxy.GetDataTable(SqlString);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#endregion 成员方法
|
|
|
|
|
|
}
|
|
|
}
|