You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
729 lines
31 KiB
729 lines
31 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Collections.ObjectModel;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Utils.Datas;
|
|
using WeighBusiness.BO;
|
|
using WeighBusiness.Utils.SqlUtils;
|
|
|
|
namespace WeighBusiness.Utils
|
|
{
|
|
public class LocalQueryUtil
|
|
{
|
|
/// <summary>
|
|
/// 从本地数据库中查询所有车辆信息。
|
|
/// </summary>
|
|
public static IList<Car> GetAllLocalCars()
|
|
{
|
|
return GetLocalCars(null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 从本地数据库中查询指定过滤条件的所有车辆信息。
|
|
/// </summary>
|
|
public static IList<Car> GetLocalCars(string carNameOrSpell)
|
|
{
|
|
string querySql = "select * from {0} ".FormatWith(WeighBusiness.Utils.TableNames.车辆表);
|
|
//if (!string.IsNullOrEmpty(carNameOrSpell))
|
|
// querySql += "where Buyer_Spell like '%" + carNameOrSpell + "%' or Buyer_Spell like '%" + carNameOrSpell + "%'";
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
var result = new List<Car>();
|
|
foreach (DataRow row in table.Rows) {
|
|
result.Add(row.ToCar());
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static Car GetLocalCar(string carNumber)
|
|
{
|
|
string querySql = "select * from {0} ".FormatWith(WeighBusiness.Utils.TableNames.车辆表);
|
|
if (!string.IsNullOrEmpty(carNumber))
|
|
querySql += "where Car_Number = '" + carNumber+"'";
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
foreach (DataRow row in table.Rows) {
|
|
return row.ToCar();
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 从本地数据库中查询所有会计单位信息。
|
|
/// </summary>
|
|
public static IList<AccountingUnit> GetAllAccountingUnits()
|
|
{
|
|
string querySql = "select * from {0} ".FormatWith(WeighBusiness.Utils.TableNames.会计单位表);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
var result = new List<AccountingUnit>();
|
|
foreach (DataRow row in table.Rows) {
|
|
result.Add(row.ToAccountingUnit());
|
|
}
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取本地数据库中称重表中指定ID号的信息。如果没有在返回null
|
|
/// </summary>
|
|
public static Weigh GetLocalWeighHead(long id)
|
|
{
|
|
string querySql = @"select w.ID,w.IsChecked,w.CreateDateTime,w.Car_ID,c.Car_Name,w.CarNumber,w.Number,w.HeadWeight,w.Remote_Weigh_ID,w.RowVersion
|
|
from {0} w
|
|
left join {1} c on w.Car_ID=c.Car_ID
|
|
where w.ID = {2}";
|
|
querySql = querySql.FormatWith(TableNames.称重表, TableNames.车辆表, id);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
Weigh result = new Weigh() {
|
|
ID = id,
|
|
IsChecked = DataTypeUtil.GetBoolData(table.Rows[0]["IsChecked"]),
|
|
CreateDateTime = DataTypeUtil.GetDateTimeNullData(table.Rows[0]["CreateDateTime"]),
|
|
Car_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Car_ID"]),
|
|
Car_Name = table.Rows[0]["Car_Name"].ToString(),
|
|
CarNumber = DataTypeUtil.GetIntNullNum(table.Rows[0]["CarNumber"]),
|
|
Number = DataTypeUtil.GetIntNullNum(table.Rows[0]["Number"]),
|
|
HeadWeight = DataTypeUtil.GetDecimalNullNum(table.Rows[0]["HeadWeight"]),
|
|
Remote_Weigh_ID = DataTypeUtil.GetLongNullNum(table.Rows[0]["Remote_Weigh_ID"]),
|
|
RowVersion = DataTypeUtil.GetIntNum(table.Rows[0]["RowVersion"]),
|
|
};
|
|
return result;
|
|
}
|
|
|
|
const string QueryUserSql_Where = @"select ID,ERP_User_Name,ERP_User_Password,ERP_User_ID,IsDomainManager,UrlPath,IsAdmin from {0} where {1} = '{2}'";
|
|
const string QueryUserSql_NoWhere = "select ID,ERP_User_Name,ERP_User_Password,ERP_User_ID,IsDomainManager,UrlPath from {0}";
|
|
/// <summary>
|
|
/// 获取本地数据库中的用户信息
|
|
/// </summary>
|
|
/// <param name="userName">终端用户名</param>
|
|
public static User GetLocalUser(string userName)
|
|
{
|
|
return GetLocalUserFromSql(QueryUserSql_Where, "ERP_User_Name", userName);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取本地数据库中的用户信息
|
|
/// </summary>
|
|
/// <param name="userName">终端用户ID</param>
|
|
public static User GetLocalUser(long userID)
|
|
{
|
|
return GetLocalUserFromSql(QueryUserSql_Where, "ID", userID);
|
|
}
|
|
|
|
private static User GetLocalUserFromSql(string querySql, string key, object value)
|
|
{
|
|
querySql = querySql.FormatWith(TableNames.用户表, key, value);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
|
|
return GetUser(table.Rows[0]);
|
|
}
|
|
|
|
private static User GetUser(DataRow row)
|
|
{
|
|
return new User() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
ERP_User_Name = row["ERP_User_Name"].ToString(),
|
|
ERP_User_Password = row["ERP_User_Password"].ToString(),
|
|
ERP_User_ID = DataTypeUtil.GetLongNum(row["ERP_User_ID"]),
|
|
IsDomainManager = DataTypeUtil.GetBoolData(row["IsDomainManager"]),
|
|
UrlPath = row["UrlPath"].ToString(),
|
|
IsAdmin = DataTypeUtil.GetBoolData(row["IsAdmin"])
|
|
};
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询经过验证的用户，以及系统用户（system）
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static List<User> GetAllLocalUsers_Valid()
|
|
{
|
|
var sql = QueryUserSql_NoWhere.FormatWith(TableNames.用户表) + " where (IsCheckTerminalUser = 'True' and IsCheckSystemUser = 'True') or User_Name = '{0}'".FormatWith("system");
|
|
return GetAllLocalUsers(sql);
|
|
}
|
|
|
|
public static List<User> GetAllLocalUsers()
|
|
{
|
|
var sql = QueryUserSql_NoWhere.FormatWith(TableNames.用户表);
|
|
return GetAllLocalUsers(sql);
|
|
}
|
|
|
|
private static List<User> GetAllLocalUsers(string sql)
|
|
{
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
|
|
var users = new List<User>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var u = GetUser(row);
|
|
users.Add(u);
|
|
}
|
|
return users;
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 获取本地数据库中指定本地称重单号的清单信息。如果没有在返回null
|
|
/// </summary>
|
|
public static IList<Weigh_Detail> GetLocalWeighDetails(long localWeighID)
|
|
{
|
|
const string QuerySql =
|
|
@"select ID,Weight,WeighTime
|
|
from {0}
|
|
where Local_Weigh_ID = {1}";
|
|
var querySql = QuerySql.FormatWith(TableNames.称重清单表, localWeighID);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
var result = new List<Weigh_Detail>();
|
|
foreach (DataRow row in table.Rows) {
|
|
Weigh_Detail detail = new Weigh_Detail() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
Local_Weigh_ID = localWeighID,
|
|
Weight = DataTypeUtil.GetDecimalNullNum(row["Weight"]),
|
|
WeighTime = DataTypeUtil.GetDateTimeNullData(row["WeighTime"]),
|
|
};
|
|
result.Add(detail);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static object GetLocalTableLastRowField(string table, string field)
|
|
{
|
|
string querySql = string.Format("select first 1 {1} from {0} order by {1} desc", table, field);
|
|
return SqlHelperEx.DoQuery<object>(querySql, obj => obj);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 从本地数据库中查询所有车辆信息。
|
|
/// </summary>
|
|
public static IList<Car> GetLocalCars()
|
|
{
|
|
string querySql = string.Format("select * from {0} ", TableNames.车辆表);
|
|
//if (!string.IsNullOrEmpty(buyerNameOrSpell))
|
|
// querySql += "where Buyer_Spell like '%" + buyerNameOrSpell + "%' or Buyer_Spell like '%" + buyerNameOrSpell + "%'";
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
var cars = new List<Car>();
|
|
foreach (DataRow row in table.Rows) {
|
|
cars.Add(row.ToCar());
|
|
}
|
|
return cars;
|
|
}
|
|
|
|
const string QueryWpfUserIDSql_NoWhere = "select WpfUser_ID,RowVersion from {0}";
|
|
public static List<WpfUser> GetAllLocalWpfUserRowVersion()
|
|
{
|
|
var sql = QueryWpfUserIDSql_NoWhere.FormatWith(TableNames.ERP员工);
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
var wpfUsers = new List<WpfUser>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var wpfUser = new WpfUser();
|
|
wpfUser.WpfUser_ID = (long)((int)row[0]);
|
|
wpfUser.RowVersion = DataTypeUtil.ToStringEmptyIfNull(row[1]);
|
|
wpfUsers.Add(wpfUser);
|
|
}
|
|
return wpfUsers;
|
|
}
|
|
|
|
//private static List<Employee> GetAllLocalEmployees(string sql)
|
|
//{
|
|
// var table = SqlHelperEx.DoQuery(sql);
|
|
// if (table.Rows.Count == 0)
|
|
// return null;
|
|
|
|
// var employees = new List<Employee>();
|
|
// foreach (DataRow row in table.Rows) {
|
|
// var u = GetEmployee(row);
|
|
// employees.Add(u);
|
|
// }
|
|
// return employees;
|
|
//}
|
|
|
|
//const string QueryEmployeeSql_NoWhere = "select ID,Employee_ID,Employee_Code,Employee_Name,RowVersion from {0}";
|
|
//public static List<Employee> GetAllLocalEmployee()
|
|
//{
|
|
// var sql = QueryUserSql_NoWhere.FormatWith(TableNames.员工);
|
|
// return GetAllLocalEmployees(sql);
|
|
//}
|
|
|
|
private static WpfUser GetWpfUser(DataRow row)
|
|
{
|
|
return new WpfUser() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
Employee_Code = row["Employee_Code"].ToString(),
|
|
WpfUser_Name = row["WpfUser_Name"].ToString(),
|
|
WpfUser_ID = DataTypeUtil.GetLongNum(row["WpfUser_ID"]),
|
|
Employee_ID = DataTypeUtil.GetLongNullNum(row["Employee_ID"]),
|
|
Department_ID = DataTypeUtil.GetLongNullNum(row["Department_ID"])
|
|
};
|
|
}
|
|
|
|
const string QueryWpfUserSql_Where = @"select ID,WpfUser_ID,Employee_Code,WpfUser_Name,Employee_ID,Department_ID from {0} where {1} = '{2}'";
|
|
|
|
public static WpfUser GetLocalWpfUser(long wpfUSerID)
|
|
{
|
|
return GetLocalWpfUserFromSql(QueryWpfUserSql_Where, "WpfUser_ID", wpfUSerID);
|
|
}
|
|
|
|
public static WpfUser GetLocalEmployeeByCode(string code)
|
|
{
|
|
return GetLocalWpfUserFromSql(QueryWpfUserSql_Where, "Employee_Code", code);
|
|
}
|
|
|
|
private static WpfUser GetLocalWpfUserFromSql(string querySql, string key, object value)
|
|
{
|
|
querySql = querySql.FormatWith(TableNames.ERP员工, key, value);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
|
|
return GetWpfUser(table.Rows[0]);
|
|
}
|
|
|
|
const string QueryRoadMapVersionSql_NoWhere = "select RoadMap_ID,RowVersion from {0}";
|
|
public static List<RoadMap_InputDetail> GetAllLocalRoadMapVersion()
|
|
{
|
|
var sql = QueryRoadMapVersionSql_NoWhere.FormatWith(TableNames.工艺投入明细);
|
|
var sql2 = QueryRoadMapVersionSql_NoWhere.FormatWith(TableNames.工艺产出明细);
|
|
return GetRoadMapDetail(sql).Union(GetRoadMapDetail(sql2)).ToList();
|
|
}
|
|
|
|
private static List<RoadMap_InputDetail> GetRoadMapDetail(string sql)
|
|
{
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return new List<RoadMap_InputDetail>();
|
|
var list = new List<RoadMap_InputDetail>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var detail = new RoadMap_InputDetail();
|
|
detail.RoadMap_ID = (long)((int)row[0]);
|
|
detail.RowVersion = (string)row[1];
|
|
list.Add(detail);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
const string QueryRoadMap_InputDetailSql_Where = "select * from(select detail.Detail_ID,detail.Goods_ID,detail.Goods_Name,detail.Goods_Code,detail.RoadMap_ID,detail.RowVersion,detail.RoadMap_Name, detail.ProductShift_ID,sum(wInfo.NetWeight) as totalNetWeight,Goods.AllowDisplay from {0} detail left outer join {1} wInfo on detail.Detail_ID = wInfo.Detail_ID and wInfo.WeightType = 'Input' and wInfo.IsBatch = {2} and wInfo.BatchNumber_Id = '{3}' left outer join Goods on detail.Goods_ID = Goods.Goods_ID where detail.Routing_ID='{4}' and detail.ProductShift_ID = '{5}' and detail.RoadMap_Name = '{6}' group by detail.Detail_ID,detail.Goods_ID,detail.Goods_Name,detail.Goods_Code,detail.RoadMap_ID,detail.RowVersion,detail.RoadMap_Name, detail.ProductShift_ID,Goods.AllowDisplay) rows {7} to {8}";
|
|
public static ObservableCollection<RoadMap_InputDetail> GetAllLocalRoadMap_InputDetail(long batchNumberId,long? Routing_ID,string roadMap_Name, long shiftId,bool isBatch, int pageIndex)
|
|
{
|
|
var sql = QueryRoadMap_InputDetailSql_Where.FormatWith(TableNames.工艺投入明细, TableNames.称重记录, isBatch ? "1" : "0", batchNumberId, Routing_ID, shiftId, roadMap_Name, pageIndex == 1 ? 1 : (pageIndex - 1) * 6 + 1, pageIndex * 6);
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return new ObservableCollection<RoadMap_InputDetail>();
|
|
var list = new ObservableCollection<RoadMap_InputDetail>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var detail = new RoadMap_InputDetail();
|
|
detail.Detail_ID = DataTypeUtil.GetLongNum(row[0]);
|
|
detail.Goods_ID = DataTypeUtil.GetLongNum(row[1]);
|
|
detail.Goods_Name = DataTypeUtil.ToStringEmptyIfNull(row[2]);
|
|
detail.Goods_Code = DataTypeUtil.ToStringEmptyIfNull(row[3]);
|
|
detail.RoadMap_ID = DataTypeUtil.GetLongNullNum(row[4]);
|
|
detail.RowVersion = DataTypeUtil.ToStringEmptyIfNull(row[5]);
|
|
detail.RoadMap_Name = DataTypeUtil.ToStringEmptyIfNull(row[6]);
|
|
detail.ProductShift_ID = DataTypeUtil.GetLongNullNum(row[7]);
|
|
detail.InputNum = DataTypeUtil.GetDecimalNullNum(row[8]);
|
|
detail.AllowDisplay = DataTypeUtil.GetBoolNullData(row[9]);
|
|
list.Add(detail);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
const string QueryRoadMap_OutputDetailSql_Where = "select Detail_ID,Goods_ID,Goods_Name,Goods_Code,RoadMap_Name,Routing_ID,RowVersion,ProductShift_ID,ProductShift_Name,RoadMap_ID from {0} where Routing_ID={1} and ProductShift_ID = {2}";
|
|
public static ObservableCollection<RoadMap_OutputDetail> GetAllLocalRoadMap_OutputDetail(long? Routing_ID, long shiftId)
|
|
{
|
|
var sql = QueryRoadMap_OutputDetailSql_Where.FormatWith(TableNames.工艺产出明细, Routing_ID, shiftId);
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return new ObservableCollection<RoadMap_OutputDetail>();
|
|
var list = new ObservableCollection<RoadMap_OutputDetail>();
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var detail = new RoadMap_OutputDetail();
|
|
detail.Detail_ID = DataTypeUtil.GetLongNum(row[0]);
|
|
detail.Goods_ID = DataTypeUtil.GetLongNum(row[1]);
|
|
detail.Goods_Name = DataTypeUtil.ToStringEmptyIfNull(row[2]);
|
|
detail.Goods_Code = DataTypeUtil.ToStringEmptyIfNull(row[3]);
|
|
detail.RoadMap_Name = DataTypeUtil.ToStringEmptyIfNull(row[4]);
|
|
detail.Routing_ID = DataTypeUtil.GetLongNullNum(row[5]);
|
|
detail.RowVersion = DataTypeUtil.ToStringEmptyIfNull(row[6]);
|
|
detail.ProductShift_ID = DataTypeUtil.GetLongNullNum(row[7]);
|
|
detail.ProductShift_Name = DataTypeUtil.ToStringEmptyIfNull(row[8]);
|
|
detail.RoadMap_ID = DataTypeUtil.GetLongNullNum(row[9]);
|
|
list.Add(detail);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
const string QueryRoadMap_OutputDetailSql_Where2 = "select * from(select detail.Detail_ID,detail.Goods_ID,detail.Goods_Name,detail.Goods_Code,detail.RoadMap_Name,detail.Routing_ID,detail.RowVersion, detail.ProductShift_ID,detail.ProductShift_Name,detail.RoadMap_ID,sum(wInfo.NetWeight) as totalNetWeight,Goods.AllowDisplay from {0} detail left outer join {1} wInfo on detail.Detail_ID = wInfo.Detail_ID and wInfo.WeightType = 'Output' and wInfo.IsBatch = {2} and wInfo.BatchNumber_ID = {3} left outer join Goods on detail.Goods_ID = Goods.Goods_ID where detail.Routing_ID={4} and detail.ProductShift_ID = {5} group by detail.Detail_ID,detail.Goods_ID,detail.Goods_Name,detail.Goods_Code,detail.RoadMap_Name,detail.Routing_ID,detail.RowVersion,detail.ProductShift_ID,detail.ProductShift_Name,detail.RoadMap_ID,Goods.AllowDisplay ) rows {6} to {7}";
|
|
|
|
public static ObservableCollection<RoadMap_OutputDetail> GetAllLocalRoadMap_OutputDetail2(long? Routing_ID,long? batchNumberId, long shiftId, int pageIndex)
|
|
{
|
|
var sql = QueryRoadMap_OutputDetailSql_Where2.FormatWith(TableNames.工艺产出明细, TableNames.称重记录, "0", batchNumberId, Routing_ID, shiftId, pageIndex == 1 ? 1 : (pageIndex - 1) * 6 + 1, pageIndex * 6);
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return new ObservableCollection<RoadMap_OutputDetail>();
|
|
var list = new ObservableCollection<RoadMap_OutputDetail>();
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var detail = new RoadMap_OutputDetail();
|
|
detail.Detail_ID = DataTypeUtil.GetLongNum(row[0]);
|
|
detail.Goods_ID = DataTypeUtil.GetLongNum(row[1]);
|
|
detail.Goods_Name = DataTypeUtil.ToStringEmptyIfNull(row[2]);
|
|
detail.Goods_Code = DataTypeUtil.ToStringEmptyIfNull(row[3]);
|
|
detail.RoadMap_Name = DataTypeUtil.ToStringEmptyIfNull(row[4]);
|
|
detail.Routing_ID = DataTypeUtil.GetLongNullNum(row[5]);
|
|
detail.RowVersion = DataTypeUtil.ToStringEmptyIfNull(row[6]);
|
|
detail.ProductShift_ID = DataTypeUtil.GetLongNullNum(row[7]);
|
|
detail.ProductShift_Name = DataTypeUtil.ToStringEmptyIfNull(row[8]);
|
|
detail.RoadMap_ID = DataTypeUtil.GetLongNullNum(row[9]);
|
|
detail.WeightNum = DataTypeUtil.GetDecimalNullNum(row[10]);
|
|
detail.AllowDisplay = DataTypeUtil.GetBoolNullData(row[11]);
|
|
list.Add(detail);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
private static ProductShift GetProductShift(DataRow row)
|
|
{
|
|
return new ProductShift() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
WpfUser_ID = DataTypeUtil.GetLongNum(row["WpfUser_ID"]),
|
|
ProductShift_ID = DataTypeUtil.GetLongNum(row["ProductShift_ID"]),
|
|
ProductShift_Name = row["ProductShift_Name"].ToString(),
|
|
};
|
|
}
|
|
|
|
|
|
const string QueryProductShiftSql_Where = @"select ID,WpfUser_ID,ProductShift_ID,ProductShift_Name from {0} where {1} = '{2}'";
|
|
|
|
public static ProductShift GetLocalProductShift(long userID)
|
|
{
|
|
return GetLocalProductShiftFromSql(QueryProductShiftSql_Where, "WpfUser_ID", userID);
|
|
}
|
|
|
|
private static ProductShift GetLocalProductShiftFromSql(string querySql, string key, object value)
|
|
{
|
|
querySql = querySql.FormatWith(TableNames.生产班组, key, value);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
|
|
return GetProductShift(table.Rows[0]);
|
|
}
|
|
|
|
public static List<ProductShift> GetLocalProductShifts(long wpfUser_ID)
|
|
{
|
|
var list = new List<ProductShift>();
|
|
var querySql = QueryProductShiftSql_Where.FormatWith(TableNames.生产班组, "WpfUser_ID", wpfUser_ID);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
|
|
foreach (DataRow row in table.Rows) {
|
|
list.Add(GetProductShift(row));
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static Tuple<decimal?, int?> GetTotalWeightByDetailID(long? BatchNumber_ID,long Detail_ID, WeightType type, bool isBatch = false)
|
|
{
|
|
var querySql = "select sum(NetWeight),count(ID) from {0} where Detail_ID = {1} and IsBatch = {2} and WeightType = '{3}' and BatchNumber_ID = {4} ".FormatWith(TableNames.称重记录, Detail_ID, !isBatch ? "0" : "1", type.ToString(), BatchNumber_ID);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
return new Tuple<decimal?, int?>(DataTypeUtil.GetDecimalNullNum(table.Rows[0][0]),
|
|
DataTypeUtil.GetIntNullNum(table.Rows[0][1]));
|
|
}
|
|
|
|
public static ProduceOutput GetLocalProduceOutputHead(long id)
|
|
{
|
|
string querySql = @"select AccountingUnit_ID,Department_ID,Employee_ID,CreateTime,BatchNumber_ID,ProductLink,Routing_ID,ProductShift_ID,Domain_ID
|
|
from {0} where ID = {1}";
|
|
querySql = querySql.FormatWith(TableNames.产出单, id);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
ProduceOutput result = new ProduceOutput() {
|
|
ID = id,
|
|
AccountingUnit_ID = DataTypeUtil.GetLongNum(table.Rows[0]["AccountingUnit_ID"]),
|
|
Department_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Department_ID"]),
|
|
Employee_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Employee_ID"]),
|
|
CreateTime = DataTypeUtil.GetDateTimeNullData(table.Rows[0]["CreateTime"]),
|
|
BatchNumber_ID = DataTypeUtil.GetLongNum(table.Rows[0]["BatchNumber_ID"]),
|
|
ProductLink = table.Rows[0]["ProductLink"].ToString(),
|
|
Routing_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Routing_ID"]),
|
|
ProductShift_ID = DataTypeUtil.GetLongNum(table.Rows[0]["ProductShift_ID"]),
|
|
Domain_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Domain_ID"])
|
|
};
|
|
return result;
|
|
}
|
|
|
|
public static IList<ProduceOutput_Detail> GetLocalProduceOutputDetails(long produceOutputID)
|
|
{
|
|
const string QuerySql =
|
|
@"select ID,Goods_ID,MainNumber
|
|
from {0}
|
|
where ProduceOutput_ID = {1}";
|
|
var querySql = QuerySql.FormatWith(TableNames.产出单明细, produceOutputID);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
var result = new List<ProduceOutput_Detail>();
|
|
foreach (DataRow row in table.Rows) {
|
|
ProduceOutput_Detail detail = new ProduceOutput_Detail() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
ProduceOutput_ID = produceOutputID,
|
|
Goods_ID = DataTypeUtil.GetLongNullNum(row["Goods_ID"]),
|
|
MainNumber = DataTypeUtil.GetDecimalNullNum(row["MainNumber"])
|
|
};
|
|
result.Add(detail);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static ProduceIntake GetLocalProduceIntakeHead(long id)
|
|
{
|
|
string querySql = @"select AccountingUnit_ID,Department_ID,Employee_ID,CreateTime,BatchNumber_ID,ProductLink,Routing_ID,ProductShift_ID,Domain_ID
|
|
from {0} where ID = {1}";
|
|
querySql = querySql.FormatWith(TableNames.投料单, id);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
ProduceIntake result = new ProduceIntake() {
|
|
ID = id,
|
|
AccountingUnit_ID = DataTypeUtil.GetLongNum(table.Rows[0]["AccountingUnit_ID"]),
|
|
Department_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Department_ID"]),
|
|
Employee_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Employee_ID"]),
|
|
CreateTime = DataTypeUtil.GetDateTimeNullData(table.Rows[0]["CreateTime"]),
|
|
BatchNumber_ID = DataTypeUtil.GetLongNum(table.Rows[0]["BatchNumber_ID"]),
|
|
ProductLink = table.Rows[0]["ProductLink"].ToString(),
|
|
Routing_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Routing_ID"]),
|
|
ProductShift_ID = DataTypeUtil.GetLongNum(table.Rows[0]["ProductShift_ID"]),
|
|
Domain_ID = DataTypeUtil.GetLongNum(table.Rows[0]["Domain_ID"])
|
|
};
|
|
return result;
|
|
}
|
|
|
|
public static IList<ProduceIntake_Detail> GetLocalProduceIntakeDetails(long produceIntakeID)
|
|
{
|
|
const string QuerySql =
|
|
@"select ID,Goods_ID,Number
|
|
from {0}
|
|
where ProduceIntake_ID = {1}";
|
|
var querySql = QuerySql.FormatWith(TableNames.投料单明细, produceIntakeID);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
var result = new List<ProduceIntake_Detail>();
|
|
foreach (DataRow row in table.Rows) {
|
|
ProduceIntake_Detail detail = new ProduceIntake_Detail() {
|
|
ID = DataTypeUtil.GetLongNum(row["ID"]),
|
|
ProduceIntake_ID = produceIntakeID,
|
|
Goods_ID = DataTypeUtil.GetLongNullNum(row["Goods_ID"]),
|
|
Number = DataTypeUtil.GetDecimalNullNum(row["Number"])
|
|
};
|
|
result.Add(detail);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static List<long> GetNotSendProduceOutputID()
|
|
{
|
|
var list = new List<long>();
|
|
string querySql = @"select ID
|
|
from {0} where IsSend = 0";
|
|
querySql = querySql.FormatWith(TableNames.产出单);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
foreach (DataRow row in table.Rows) {
|
|
list.Add(DataTypeUtil.GetLongNum(row["ID"]));
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static List<ProduceOutputData> GetNotSendProduceOutputByID(List<long> idList)
|
|
{
|
|
var list = new List<ProduceOutputData>();
|
|
string querySql = @"select a.ID ,a.AccountingUnit_ID,a.Department_ID,a.Employee_ID,a.CreateTime,a.BatchNumber_ID,a.ProductLink,a.Routing_ID,a.ProductShift_ID,a.Domain_ID,b.Goods_ID,b.MainNumber
|
|
from {0} a left outer join {1} b on a.ID = b.ProduceOutput_ID where a.ID in ({2}) and a.IsSend = 0";
|
|
querySql = querySql.FormatWith(TableNames.产出单,TableNames.产出单明细, string.Join(",",idList));
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
foreach (DataRow row in table.Rows) {
|
|
var data = new ProduceOutputData();
|
|
data.ProduceOutput_ID = DataTypeUtil.GetLongNum(row["ID"]);
|
|
data.AccountingUnit_ID = DataTypeUtil.GetLongNum(row["AccountingUnit_ID"]);
|
|
data.Department_ID = DataTypeUtil.GetLongNum(row["Department_ID"]);
|
|
data.Employee_ID = DataTypeUtil.GetLongNum(row["Employee_ID"]);
|
|
data.CreateTime = DataTypeUtil.GetDateTimeNullData(row["CreateTime"]);
|
|
data.BatchNumber_ID = DataTypeUtil.GetLongNum(row["BatchNumber_ID"]);
|
|
data.ProductLink = row["ProductLink"].ToString();
|
|
data.Routing_ID = DataTypeUtil.GetLongNum(row["Routing_ID"]);
|
|
data.ProductShift_ID = DataTypeUtil.GetLongNum(row["ProductShift_ID"]);
|
|
data.Domain_ID = DataTypeUtil.GetLongNum(row["Domain_ID"]);
|
|
data.Goods_ID = DataTypeUtil.GetLongNullNum(row["Goods_ID"]);
|
|
data.MainNumber = DataTypeUtil.GetDecimalNullNum(row["MainNumber"]);
|
|
list.Add(data);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static List<long> GetNotSendProduceIntakeID()
|
|
{
|
|
var list = new List<long>();
|
|
string querySql = @"select ID
|
|
from {0} where IsSend = 0";
|
|
querySql = querySql.FormatWith(TableNames.投料单);
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return null;
|
|
foreach (DataRow row in table.Rows) {
|
|
list.Add(DataTypeUtil.GetLongNum(row["ID"]));
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static List<ProduceOutputData> GetNotSendProduceIntakeByID(InputType inputType)
|
|
{
|
|
var list = new List<ProduceOutputData>();
|
|
string querySql = @"select a.ID ,a.AccountingUnit_ID,a.Department_ID,a.Employee_ID,a.CreateTime,a.BatchNumber_ID,a.ProductLink,a.Routing_ID,a.ProductShift_ID,a.Domain_ID,b.Goods_ID,b.Number,b.Times,a.OutGoods_ID,a.OutGoodsNum,b.ProductDate
|
|
from {0} a left outer join {1} b on a.ID = b.ProduceIntake_ID where a.IsSend = 0 and a.InputType='{2}'";
|
|
querySql = querySql.FormatWith(TableNames.投料单, TableNames.投料单明细,inputType.ToString());
|
|
var table = SqlHelperEx.DoQuery(querySql);
|
|
if (table.Rows.Count == 0)
|
|
return new List<ProduceOutputData>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var data = new ProduceOutputData();
|
|
data.ProduceOutput_ID = DataTypeUtil.GetLongNum(row["ID"]);
|
|
data.AccountingUnit_ID = DataTypeUtil.GetLongNum(row["AccountingUnit_ID"]);
|
|
data.Department_ID = DataTypeUtil.GetLongNum(row["Department_ID"]);
|
|
data.Employee_ID = DataTypeUtil.GetLongNum(row["Employee_ID"]);
|
|
data.CreateTime = DataTypeUtil.GetDateTimeNullData(row["CreateTime"]);
|
|
data.BatchNumber_ID = DataTypeUtil.GetLongNum(row["BatchNumber_ID"]);
|
|
data.ProductLink = row["ProductLink"].ToString();
|
|
data.Routing_ID = DataTypeUtil.GetLongNum(row["Routing_ID"]);
|
|
data.ProductShift_ID = DataTypeUtil.GetLongNum(row["ProductShift_ID"]);
|
|
data.Domain_ID = DataTypeUtil.GetLongNum(row["Domain_ID"]);
|
|
data.Goods_ID = DataTypeUtil.GetLongNullNum(row["Goods_ID"]);
|
|
data.MainNumber = DataTypeUtil.GetDecimalNullNum(row["Number"]);
|
|
data.Times = DataTypeUtil.GetDecimalNullNum(row["Times"]);
|
|
data.OutGoods_ID = DataTypeUtil.GetLongNullNum(row["OutGoods_ID"]);
|
|
data.OutGoodsNum = DataTypeUtil.GetDecimalNullNum(row["OutGoodsNum"]);
|
|
data.ProductDate = DataTypeUtil.GetDateTimeNullData(row["CreateTime"]);
|
|
list.Add(data);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
const string LittleCarSql_NoWhere = "select ERP_ID,RowVersion,Name,Weight from LittleCar";
|
|
public static List<LittleCar> GetAllLocalLittleCar()
|
|
{
|
|
return GetNameWeight<LittleCar>(LittleCarSql_NoWhere);
|
|
}
|
|
|
|
private static List<T> GetNameWeight<T>(string sql) where T : INameWeight, new()
|
|
{
|
|
var table = SqlHelperEx.DoQuery(sql);
|
|
if (table.Rows.Count == 0)
|
|
return new List<T>();
|
|
var list = new List<T>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var car = new T();
|
|
car.ERP_ID = (long)(int.Parse(row[0].ToString()));
|
|
car.RowVersion = int.Parse(row[1].ToString());
|
|
car.Name = row[2].ToString();
|
|
var weight = row[3].ToString();
|
|
if (!string.IsNullOrEmpty(weight)) {
|
|
car.Weight = decimal.Parse(weight);
|
|
}
|
|
list.Add(car);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
const string PoleSql_NoWhere = "select ERP_ID,RowVersion,Name,Weight from Pole";
|
|
public static List<Pole> GetAllLocalPole()
|
|
{
|
|
return GetNameWeight<Pole>(PoleSql_NoWhere);
|
|
}
|
|
|
|
const string DishSql_NoWhere = "select ERP_ID,RowVersion,Name,Weight from Dish";
|
|
public static List<Dish> GetAllLocalDish()
|
|
{
|
|
return GetNameWeight<Dish>(DishSql_NoWhere);
|
|
}
|
|
|
|
const string PSInfoSql_NoWhere = "select ProductShift_ID,RowVersion from PSInfo";
|
|
public static List<PSInfo> GetAllLocalPSInfoVersion()
|
|
{
|
|
var table = SqlHelperEx.DoQuery(PSInfoSql_NoWhere);
|
|
if (table.Rows.Count == 0)
|
|
return new List<PSInfo>();
|
|
var list = new List<PSInfo>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var psInfo = new PSInfo();
|
|
var productShift_ID = row[0].ToString();
|
|
if (!string.IsNullOrEmpty(productShift_ID)) {
|
|
psInfo.ProductShift_ID = (long)(int.Parse(productShift_ID));
|
|
}
|
|
psInfo.RowVersion = int.Parse(row[1].ToString());
|
|
list.Add(psInfo);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static List<PSInfo> GetAllLocalPSInfo(long productShift_ID)
|
|
{
|
|
var table = SqlHelperEx.DoQuery(string.Format("select Store_ID,TareSet,BillSet,WindowSet,GoodsInStore_ID from PSInfo where ProductShift_ID = {0}", productShift_ID));
|
|
if (table.Rows.Count == 0)
|
|
return new List<PSInfo>();
|
|
var list = new List<PSInfo>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var psInfo = new PSInfo();
|
|
var store_ID = row[0].ToString();
|
|
if (!string.IsNullOrEmpty(store_ID)) {
|
|
psInfo.Store_ID = (long)(int.Parse(store_ID));
|
|
}
|
|
psInfo.TareSet = row[1].ToString();
|
|
psInfo.BillSet = row[2].ToString();
|
|
psInfo.WindowSet = row[3].ToString();
|
|
psInfo.ProductShift_ID = productShift_ID;
|
|
var goodsInStore_ID = row[4].ToString();
|
|
if (!string.IsNullOrEmpty(goodsInStore_ID)) {
|
|
psInfo.GoodsInStore_ID = (long)(int.Parse(goodsInStore_ID));
|
|
}
|
|
list.Add(psInfo);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
public static List<BomSet> GetBomSetRowVersion()
|
|
{
|
|
var table = SqlHelperEx.DoQuery("select BomSet_ID,RowVersion from BomSet");
|
|
if (table.Rows.Count == 0)
|
|
return new List<BomSet>();
|
|
var list = new List<BomSet>();
|
|
foreach (DataRow row in table.Rows) {
|
|
var bomSet = new BomSet();
|
|
bomSet.BomSet_ID = (long)(int.Parse(row[0].ToString()));
|
|
bomSet.RowVersion = int.Parse(row[1].ToString());
|
|
list.Add(bomSet);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|