using System; using System.Data; using System.Text; using System.Collections.Generic; using System.Data.SqlClient; using ApServerProvider; using System.Collections; using DbCommon; using Estsh.Web.Util; /*************************************************************************************************** * *作者：龚泽宇 * 创建时间：2015.12.21 * 描述：发运管理 * 修改日志：龚泽宇 * * *************************************************************************************************/ namespace Estsh.Core.Repositories { /// /// 数据库访问类，用户管理 /// public class ShipManagementDal : BaseApp { public ShipManagementDal(RemotingProxy remotingProxy) : base(remotingProxy) { } public int GetCustPdlineId(int partId) { lock (_remotingProxy) { string SqlString = " SELECT top 1 d.cust_pdline_id FROM dbo.sys_part AS a " + " LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id " + " LEFT JOIN dbo.sys_model_type AS c ON b.model_type_id=c.type_id" + " LEFT JOIN dbo.sys_cust_pdline AS d ON c.cust_pdline_id=d.cust_pdline_id" + " WHERE a.part_type=2 AND a.part_id=" + partId; //" SELECT (CASE WHEN MAX(order_id) IS NULL THEN '0' ELSE '0' END) +1 as order_id FROM dbo.g_vw_order "; Hashtable Params = new Hashtable(1); object obj = _remotingProxy.GetScalar(SqlString, Params); return obj == null ? 0 : Convert.ToInt32(obj); } } /// /// 根据分页条件获取分页数据列表 /// public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string cbCustPDLine, string txtStartTime, string txtEndTime, string txtStartSeq, string txtEndSeq) { 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 sb = new StringBuilder(); sb.Append(" ( SELECT DISTINCT TOP 1000 a.order_id,"); sb.Append(" c.model_name ,c.model_desc,f.on_line_time,order_seq,"); sb.Append(" car_no ,vin,d.cust_pdline_name,"); sb.Append(" (CASE count WHEN '0' THEN '未打印' ELSE '已打印' END ) AS count,"); sb.Append(" a.create_ymd ,a.create_hms FROM dbo.g_order AS a"); sb.Append(" LEFT JOIN dbo.sys_part AS b ON a.model_part_id=b.part_id"); sb.Append(" LEFT JOIN dbo.sys_model AS c ON b.model_id=c.model_id"); sb.Append(" LEFT JOIN dbo.sys_cust_pdline AS d ON d.cust_pdline_id=a.cust_pdline"); sb.Append(" LEFT JOIN dbo.g_order_items AS e ON a.order_id=e.order_id"); sb.Append(" LEFT JOIN dbo.g_order_detail AS f ON a.order_id=f.order_id"); sb.Append(" WHERE cust_pdline=" + cbCustPDLine); if (!string.IsNullOrEmpty(txtStartSeq) && !string.IsNullOrEmpty(txtEndSeq)) { sb.Append(" AND order_seq BETWEEN '" + txtStartSeq + "' AND '" + txtEndSeq + "'"); } else { sb.Append(" AND a.create_ymd+' ' +a.create_hms BETWEEN '" + txtStartTime + "' AND '" + txtEndTime + "'"); } sb.Append(" ) AS a"); parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", sb.ToString())); parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "*")); if (cbCustPDLine == "1007") { parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "on_line_time")); } else { parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "order_seq")); } 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); ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt); result.Add("dataList", dataList); result.Add("totalCount", values["@TotalCount"].ToString()); return result; } } /// /// 获取状态变更 /// /// public int stateChanges(string ids, string a) { lock (_remotingProxy) { StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.g_order SET count=" + a + " WHERE order_id IN (" + ids + ") "); int count = _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString()); return count; } } /// /// 获取客户产线列表 /// /// public DataTable GetCustPDLine() { lock (_remotingProxy) { StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("SELECT cust_pdline_id as [value], "); SqlStringBuilder.Append("cust_pdline_name as [key] "); SqlStringBuilder.Append("FROM dbo.sys_cust_pdline "); SqlStringBuilder.Append("WHERE enabled = 'Y' "); SqlStringBuilder.Append("ORDER BY cust_pdline_name "); return _remotingProxy.GetDataTable(SqlStringBuilder.ToString()); } } public bool ifOrderId(string order_seq, int part_id) { lock (_remotingProxy) { string SqlString = "select * from g_vw_order where order_seq= @order_seq AND cust_pdline=(SELECT cust_pdline_id FROM sys_part AS A LEFT JOIN sys_model AS B ON A.model_id=B.model_id LEFT JOIN sys_model_type AS C ON B.model_type_id=C.type_id WHERE A.part_id=" + part_id + " ) "; Hashtable Params = new Hashtable(1); Params.Add("@order_seq", order_seq); return _remotingProxy.Exists(SqlString, Params); } } public bool ifOrderId(string order_seq, int part_id, string PackageUnitNumber) { lock (_remotingProxy) { string SqlString = "select * from g_vw_order where package_unit_number+order_seq='" + PackageUnitNumber + order_seq + "' AND cust_pdline=(SELECT cust_pdline_id FROM sys_part AS A LEFT JOIN sys_model AS B ON A.model_id=B.model_id LEFT JOIN sys_model_type AS C ON B.model_type_id=C.type_id WHERE A.part_id=" + part_id + " ) "; Hashtable Params = new Hashtable(1); return _remotingProxy.Exists(SqlString, Params); } } public bool selectRelationData(int part_id) { lock (_remotingProxy) { string SqlString = "select top 1 * from g_part_relation as a left join sys_part as b on a.model_id=b.model_id where b.part_id= " + part_id + ""; Hashtable Params = new Hashtable(1); return _remotingProxy.Exists(SqlString, Params); } } public bool DeleteData(string order_id) { lock (_remotingProxy) { string SqlString = " delete g_ve_order where order_id=@order_id "; Hashtable Params = new Hashtable(1); Params.Add("@order_id", order_id); return _remotingProxy.Exists(SqlString, Params); } } } }