/// <summary>
/// 插入信息(单表)
/// </summary>
/// <param name="">信息</param>
/// <returns>插入行数</returns>
public int Insert(WorkStation model)
{
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//插入基本信息
count = session.Insert <WorkStation>(model);
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 删除信息
/// </summary>
/// <param name=""></param>
/// <returns>删除个数</returns>
public int Delete(SchedulingInfo model)
{
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//删除基本信息
count = session.Delete <SchedulingInfo>(model);
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新信息
/// </summary>
/// <param name=""></param>
/// <returns>更新行数</returns>
public int Update(WHArea model)
{
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//更新基本信息
count = session.Update <WHArea>(model);
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
public void SignPlanFinished(ProducePlan plan)
{
string sql = null;
List <DataParameter> parameters = new List <DataParameter>();
try
{
sql = "update T_FP_PRODUCEPLAN set STATUS = '1' where PID = @PID";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
session.ExecuteSql(sql, new DataParameter("PID", plan.PID));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 删除信息
/// </summary>
/// <param name=""></param>
/// <returns>删除个数</returns>
public int Delete(ProducePlan model)
{
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//删除基本信息
count = session.Delete <ProducePlan>(model);
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取角色列表
/// </summary>
/// <param name="condition">条件</param>
/// <param name="page">数据页</param>
/// <returns>数据页</returns>
public DataPage GetList(RoleInfo condition, DataPage page)
{
try
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
sqlBuilder.Append("SELECT ROLEID,ROLEDESC,CREATEUSER,CREATETIME,UPDATEUSER,UPDATETIME FROM T_ROLE WHERE 1=1 ");
//查询条件
if (string.IsNullOrEmpty(condition.RoleDESC) == false)
{
whereBuilder.Append(" AND ROLEDESC=@ROLEDESC ");
parameters.Add(new DataParameter {
ParameterName = "ROLEDESC", DataType = DbType.String, Value = condition.RoleDESC
});
}
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(whereBuilder.ToString());
}
//分页关键字段及排序
page.KeyName = "ROLEID";
if (string.IsNullOrEmpty(page.SortExpression))
{
page.SortExpression = "UPDATETIME DESC";
}
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = this.ChangeSqlByDB(sqlBuilder.ToString(), session);
page = session.GetDataPage <RoleInfo>(sql, parameters.ToArray(), page);
}
return(page);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 获取物料组成信息
/// </summary>
/// <param name="condition">获取条件</param>
/// <returns>物料组成信息</returns>
public List <TraceMaterial> GetTraceMaterial(TraceGood condition)
{
List <TraceMaterial> list = null;
string sql = @"select pt.PID,pt.MatBarCode,pt.MatID,ml.MatCode,ml.MatName,pt.CREATETIME,ml.ProductPlace
from T_FP_ProduceTrack pt
left outer join T_WH_Mat ml on pt.MatID=ml.ID
where BatchNumber = @BatchNumber and pt.WPID = 'start'
order by pt.CREATETIME";
List <DataParameter> parameters = new List <DataParameter>();
parameters.Add(new DataParameter("BatchNumber", condition.BatchNumber));
using (IDataSession session = AppDataFactory.CreateMainSession())
{
list = session.GetList <TraceMaterial>(sql, parameters.ToArray()).ToList();
}
return(list);
}
/// <summary>
/// 获取根据条码库存信息
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public WHMatAmount GetStockByBarCode(string matBarCode)
{
string sql = null;
WHMatAmount info = null;
sql = @"select t1.*,twms.UnitName,t2.MatCode,t2.MatName,twms.Description as MatSpec
from T_WH_MatAmount t1
left outer join T_WH_MatSpec twms on t1.Unit = twms.ID
left outer join T_WH_Mat t2 on t1.MatID=t2.ID
where t1.MatBarCode = @MatBarCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
info = session.Get <WHMatAmount>(sql
, new DataParameter("MatBarCode", matBarCode));
}
return(info);
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="">条件</param>
/// <returns>*信息</returns>
public SupplyInfo Get(SupplyInfo model)
{
SupplyInfo info = null;
StringBuilder sqlBuilder = new StringBuilder();
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sqlBuilder.Append(@"SELECT si.*,fi.PNAME AS FACTORYNAME,pi.PNAME AS ProduceName,pl.PLNAME AS PLName
FROM T_FP_SUPPLYINFO si
LEFT OUTER JOIN T_FP_ProducePlan pp ON si.PLANID = pp.PID
LEFT OUTER JOIN T_FP_FACTORYINFO fi ON pp.FACTORYPID =fi.PID
LEFT OUTER JOIN T_FP_PRODUCTINFO pi ON pp.PRODUCTIONID = pi.PID
LEFT OUTER JOIN T_FP_PRODUCTLINE pl on pp.PRID = pl.PID
where si.PID = @SUPPLYID");
info = session.Get <SupplyInfo>(sqlBuilder.ToString(), new DataParameter("SUPPLYID", model.PID));
}
return(info);
}
public void Delete(UseMatBill bill)
{
string sql = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
session.OpenTs();
session.Delete <UseMatBill>(bill);
sql = "delete from T_WH_UseMatAmount where USEID = @USEID";
session.ExecuteSql(sql, new DataParameter("USEID", bill.PID));
session.Insert <UseMatAmount>(bill.Amounts);
sql = "delete from T_WH_UseMatDetail where USEID = @USEID";
session.ExecuteSql(sql, new DataParameter("USEID", bill.PID));
session.Insert <UseMatDetail>(bill.Details);
session.CommitTs();
}
}
/// <summary>
/// 获取可用库存货品
/// </summary>
/// <param name="condition">获取条件</param>
/// <returns>可用库存货品</returns>
public List <WHMatAmount> GetMayUseStock(WHMatAmount condition)
{
List <WHMatAmount> list = null;
string sql = null;
List <DataParameter> parameters = new List <DataParameter>();
sql = @"select * from T_WH_MatAmount
where Warehouse = @Warehouse and MatID = @MatID and ProductAmount>0 order by UpdateTime asc";
parameters.Add(new DataParameter("Warehouse", condition.Warehouse));
parameters.Add(new DataParameter("MatID", condition.MatID));
using (IDataSession session = AppDataFactory.CreateMainSession())
{
list = session.GetList <WHMatAmount>(sql, parameters.ToArray()).ToList();
}
return(list);
}
/// <summary>
/// 逻辑删除信息
/// </summary>
/// <param name=""></param>
/// <returns>删除个数</returns>
public int Delete(WHOutMode model)
{
StringBuilder sqlBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = session.Delete <WHOutMode>(model);
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="produceID">产品主键</param>
/// <returns>*信息</returns>
public ProduceBOM GetByProduceID(string produceID)
{
ProduceBOM info = null;
try
{
string sql = "select * from T_FP_PRODUCEBOM where PRODUCEID = @PRODUCEID";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//获取信息
info = session.Get <ProduceBOM>(sql, new DataParameter("PRODUCEID", produceID));
}
return(info);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取指定仓库货品库存信息
/// </summary>
/// <param name="whID">仓库主键</param>
/// <returns></returns>
public List <WHMatAmount> GetMatStockListByWH(string whID)
{
List <WHMatAmount> list = null;
string sql = null;
List <DataParameter> parameters = new List <DataParameter>();
sql = @"select t2.MatName,sum(t1.MainAmount) as MainAmount
from T_WH_MatAmount t1
left outer join T_WH_Mat t2 on t1.MatID = t2.ID
where t1.Warehouse = @whID
group by t2.MatName";
parameters.Add(new DataParameter("whID", whID));
using (IDataSession session = AppDataFactory.CreateMainSession())
{
list = session.GetList <WHMatAmount>(sql, parameters.ToArray()).ToList();
}
return(list);
}
/// <summary>
/// 判断条码是否已入库
/// </summary>
/// <param name="matBarCode"></param>
/// <returns></returns>
public bool ExistsMatBarCode(string matBarCode)
{
bool exists = false;
string sql = null;
int count = 0;
sql = "select count(*) from T_WH_MatAmount where MatBarCode = @MatBarCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = int.Parse(session.ExecuteSqlScalar(sql, new DataParameter("MatBarCode", matBarCode)).ToString());
}
if (count > 0)
{
exists = true;
}
return(exists);
}
/// <summary>
/// 获取所有组织机构数列表
/// </summary>
/// <param name="condition">条件</param>
/// <returns>同级别菜单列表</returns>
public List <Orgaization> GetAllList()
{
try
{
List <DataParameter> parameters = new List <DataParameter>();
List <Orgaization> list = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = "SELECT * FROM T_ORGANIZATION WHERE DELFLAG='0'";
list = session.GetList <Orgaization>(sql, parameters.ToArray()).ToList();
}
return(list);
}
catch (Exception ex)
{
throw;
}
}
public void Execute(IJobExecutionContext context)
{
List <Orgaization> organList = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = "SELECT * FROM aifishingep.t_organization T WHERE T.DELFLAG = '0'";
List <DataParameter> dataParameter = new List <DataParameter>();
//插入基本信息
organList = session.GetList <Orgaization>(sql, dataParameter.ToArray()).ToList <Orgaization>();
}
if (organList != null && organList.Count > 0)
{
foreach (Orgaization di in organList)
{
CommonCache.organList.AddOrUpdate(di.OrganID, di, (oldKey, oldValue) => di);
}
}
}
/// <summary>
/// 获取货品最新唯一识别码
/// </summary>
/// <param name="info">货品信息</param>
public string GetNewIDCodeSeq(WHMat info)
{
string no = null;
object value = null;
string sql = "select max(Seq) from T_WH_MatIDCode where MatID = @MatID and BuildDate = @BuildDate";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = this.ChangeSqlByDB(sql, session);
value = session.ExecuteSqlScalar(sql, new DataParameter("MatID", info.ID)
, new DataParameter("BuildDate", DateTime.Now.ToString("yyyyMMdd")));
}
if (value != null && value != System.DBNull.Value)
{
no = value.ToString();
}
return(no);
}
/// <summary>
/// 根据货品编号获取货品信息
/// </summary>
/// <param name="matCode">货品编号</param>
/// <returns>货品信息</returns>
public WHMat GetMatByMatCode(string matCode)
{
WHMat mat = null;
string sql = null;
sql = @"select t1.ID,t1.ID as MatID,t1.MatCode,t1.MatName,t3.Description as SpecCode,t4.Description as UnitName,t4.ID as UnitCode
from T_WH_Mat t1
left join T_WH_Spec t3 on t1.SpecCode=t3.ID
left join T_WH_MatUnit t4 on t1.UnitCode=t4.ID
where t1.MatCode = @MatCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = this.ChangeSqlByDB(sql, session);
mat = session.Get <WHMat>(sql, new DataParameter("MatCode", matCode));
}
return(mat);
}
/// <summary>
/// 根据产品bom获取要货明细
/// </summary>
/// <param name="pdID">产品主键</param>
/// <returns>要货明细</returns>
public List <SupplyMaterialInfo> GetMaterialListByBOM(string pdID)
{
List <SupplyMaterialInfo> list = null;
List <DataParameter> parameters = new List <DataParameter>();
string sql = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = @"SELECT bd.MATRIALID,mat.MatName AS MATRIALNAME, bd.Amount,bd.Unit,mu.Description AS UNITNAME
FROM t_fp_producebom pb,T_FP_BOMDETAIL bd
LEFT OUTER JOIN T_WH_Mat mat ON bd.MATRIALID = mat.ID
LEFT OUTER JOIN T_WH_MatUnit mu ON bd.Unit = mu.ID
WHERE bd.BOMID = pb.PID AND pb.PRODUCEID = @PRODUCEID";
parameters.Add(new DataParameter("PRODUCEID", pdID));
list = session.GetList <SupplyMaterialInfo>(sql, parameters.ToArray()).ToList <SupplyMaterialInfo>();
}
return(list);
}
/// <summary>
/// 物料列表
/// </summary>
/// <param name="condition">条件</param>
/// <returns>仓位列表</returns>
public List <SupplyMaterialInfo> GetMaterialList(string supplyID)
{
List <SupplyMaterialInfo> list = null;
List <DataParameter> parameters = new List <DataParameter>();
string sql = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = @"SELECT smi.PID,smi.MATRIALID,mat.MatName AS MATRIALNAME, smi.Amount,smi.Unit,mu.Description AS UNITNAME
FROM T_FP_SUPPLYMATERIALINFO smi
LEFT OUTER JOIN T_WH_Mat mat ON smi.MATRIALID = mat.ID
LEFT OUTER JOIN T_WH_MatUnit mu ON smi.Unit = mu.ID
where SUPPLYID = @SUPPLYID ";
parameters.Add(new DataParameter("SUPPLYID", supplyID));
list = session.GetList <SupplyMaterialInfo>(sql, parameters.ToArray()).ToList <SupplyMaterialInfo>();
}
return(list);
}
/// <summary>
/// 获取最大入库单号
/// </summary>
/// <returns></returns>
public string GetMaxBillNO()
{
string no = null;
object value = null;
string sql = "select max(BillNO) from T_WH_MoveStockBill Where BillDate >= @StartDate and BillDate <=@EndDate";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
value = session.ExecuteSqlScalar(sql
, new DataParameter("StartDate", DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
, new DataParameter("EndDate", DateTime.Now));
}
if (value != null && value != System.DBNull.Value)
{
no = value.ToString();
}
return(no);
}
/// <summary>
/// 获取全部角色信息
/// </summary>
/// <returns>数据列表</returns>
public List <RoleInfo> GetAll()
{
try
{
List <RoleInfo> list = null;
List <DataParameter> parameters = new List <DataParameter>();
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = "SELECT * FROM T_ROLE ";
list = session.GetList <RoleInfo>(sql, parameters.ToArray()).ToList();
}
return(list);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns>数据库时间</returns>
public DateTime GetDbNowTime()
{
DateTime nowTime;
string sql = null;
try
{
sql = "SELECT GETDATE()";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
nowTime = (DateTime)session.ExecuteSqlScalar(sql);
}
return(nowTime);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断入库单号是否存在
/// </summary>
/// <param name="billNO">入库单号</param>
/// <returns>true:存在;false:不存在</returns>
public bool ExistsBillNO(string billNO)
{
string sql = null;
object value = null;
sql = "select BillNO from T_WH_MoveStockBill where BillNO = @BillNO";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
value = session.ExecuteSqlScalar(sql, new DataParameter("BillNO", billNO));
}
if (value == null || value == System.DBNull.Value)
{
return(false);
}
else
{
return(true);
}
}
public void Execute(IJobExecutionContext context)
{
List <DeviceInfo> deviceList = null;
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = "SELECT * FROM deviceinfo T ORDER BY DeviceCode";
List <DataParameter> dataParameter = new List <DataParameter>();
//插入基本信息
deviceList = session.GetList <DeviceInfo>(sql, dataParameter.ToArray()).ToList <DeviceInfo>();
}
if (deviceList != null && deviceList.Count > 0)
{
foreach (DeviceInfo di in deviceList)
{
DeviceCache.deviceCacheList.AddOrUpdate(di.DeviceSN, di, (oldKey, oldValue) => di);
}
}
}
/// <summary>
/// 获取出库单浏览信息
/// </summary>
/// <param name="info">获取条件</param>
/// <returns>出库单浏览信息</returns>
public OutStockBillView GetViewInfo(OutStockBillView info)
{
string sql = null;
List <DataParameter> parameters = new List <DataParameter>();
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//获取基本信息
sql = @"select t1.ID,t1.BillNO,t1.BillDate,t2.Clientname as Client,t3.Description as OutStockMode,
t4.Description as Warehouse,u1.USERNAME as Header,u2.USERNAME as WHHeader,
u3.USERNAME as HandlePerson,t1.Remark
from T_WH_OutStockBill t1
left outer join T_WH_Client t2 on t1.ClientCode=t2.ID
left outer join T_WH_OutMode t3 on t1.OutStockMode=t3.ID
left outer join T_WH_Warehouse t4 on t1.Warehouse=t4.ID
left outer join T_USER u1 on t1.Header=u1.USERID
left outer join T_USER u2 on t1.WHHeader=u2.USERID
left outer join T_USER u3 on t1.HandlePerson=u3.USERID
where t1.ID=?ID";
info = session.Get <OutStockBillView>(sql, new DataParameter("ID", info.ID));
//获取明细信息
sql = @"select t1.ID,t1.Seq,t1.IDCode,t1.MatID,t2.MatCode as MatCode,t2.MatName as MatName,t1.MainUnitAmount,
t1.OutAmount,t1.OutPrice,t1.OutSum,t3.Description as SaveSite,t1.Remark,twms.Description as SpecCode,
twms.UnitName,t5.Description as MainUnitName
from T_WH_OutStockDetail t1
left outer join T_WH_Mat t2 on t1.MatID=t2.ID
left outer join T_WH_MatIDCode mic on t1.IDCode=mic.IDCode
left outer join T_WH_MatSpec twms on mic.MatSpec = twms.ID
left outer join T_WH_Site t3 on t1.SaveSite=t3.ID
left outer join T_WH_Spec t4 on t2.SpecCode=t4.ID
left outer join T_WH_MatUnit t5 on t2.UnitCode=t5.ID
where t1.BillID=@BillID";
parameters.Add(new DataParameter("BillID", info.ID));
info.Details = session.GetList <OutStockDetailView>(sql, parameters.ToArray()).ToList();
}
return(info);
}
public List <CheckStockQueryResult> GetCheckAmountList(CheckStockQueryResult condition)
{
List <CheckStockQueryResult> list = null;
List <DataParameter> parameters = new List <DataParameter>();
string sql = null;
sql = @"select t1.Warehouse as WarehouseID,t2.MatID,sum(t2.ProfitAmount) as ProfitAmount,sum(t2.LossAmount) as LossAmount
from T_WH_CheckStock t1
inner join T_WH_CheckStockDetail t2 on t1.ID = t2.BillID
where BillDate >= @StartDate and BillDate < @EndDate
group by t1.Warehouse,t2.MatID";
parameters.Add(new DataParameter("StartDate", DateTime.Parse(condition.StartDate)));
parameters.Add(new DataParameter("EndDate", DateTime.Parse(condition.EndDate)));
using (IDataSession session = AppDataFactory.CreateMainSession())
{
list = session.GetList <CheckStockQueryResult>(sql, parameters.ToArray()).ToList();
}
return(list);
}
/// <summary>
/// 判断是否被使用
/// </summary>
/// <param name="info">仓位</param>
/// <returns>true:已使用;false:未使用</returns>
public bool IsUse(WHSite info)
{
int count = 0;
string sql = null;
sql = "select count(ID) from T_WH_MatAmount where SaveSite = @SaveSite";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = int.Parse(session.ExecuteSqlScalar(sql, new DataParameter("SaveSite", info.ID)).ToString());
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除信息
/// </summary>
/// <param name=""></param>
/// <returns>删除个数</returns>
public int Delete(WHQualityCheck model)
{
int count = 0;
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//删除基本信息
count = session.Delete <WHQualityCheck>(model);
//删除明细信息
string sql = "DELETE FROM T_WH_QualityCheckResult WHERE CheckID = @CheckID";
session.ExecuteSql(sql, new DataParameter("CheckID", model.ID));
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
