public YWSettingDB()
{
using (DasherStation.common.SqlHelper h = new DasherStation.common.SqlHelper())
{
_strConn = h.SqlConn;
}
}
public PurchaseBalanceDB()
{
using (DasherStation.common.SqlHelper oHelper = new DasherStation.common.SqlHelper())
{
this.strConn = oHelper.SqlConn;
}
}
public SparePartBanlanceDB()
{
using (DasherStation.common.SqlHelper oHelper = new DasherStation.common.SqlHelper())
{
this.strConn = oHelper.SqlConn;
}
}
/// <summary>
/// 获取沥青罐当前库存数据
/// </summary>
/// <returns></returns>
public DataSet QueryData()
{
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
string strSQL = @"
select
a.pId as '产品标识',a.mId as '材料标识',a.eiId as '设备标识',
case
when a.pid is not null then pk.sort
when a.mid is not null then mk.sort
end as '材料种类',
b.no as '沥青罐编号',
case
when a.pid is not null then g.name
when a.mid is not null then f.name
end as '沥青名称',
case
when a.pid is not null then pm.model
when a.mid is not null then mm.model
end as '规格型号',
b.diameter as '罐直径',b.height as '罐子高度',b.kind as '罐子样式',c.value as '液位高度',
case
when a.pid is not null and e.density is null then 1
when a.pid is not null and e.density is not null then cast(e.density as float)
when a.mid is not null and d.density is null then 1
when a.mid is not null and d.density is not null then cast(d.density as float)
end as '沥青密度', d.density as '材料密度',e.density as '产品密度',
getDate() as '日期',
0.0 as '存储数量',
psl.suttle1 as '产品库存量',
msl.quantity1 as '材料库存量',
case
when a.pid is not null then psl.suttle1
when a.mid is not null then msl.quantity1
end as '理论库存',
0.0 as '差额',
0.0 as '误差率'
from liquidMatterStockList a
join equipmentInformation b on a.eiid = b.id
left join liquidPositionHistory c on a.lpnid = c.lpnid
left join material d on a.mid = d.id
left join materialName f on d.mnid = f.id
left join materialModel mm on d.mmid = mm.id
left join product e on a.pid = e.id
left join productName g on e.pnid = g.id
left join productModel pm on e.pmid=pm.id
left join productStockList psl on a.pid = psl.pid
left join materialStockList msl on a.mid = msl.mid
left join productKind pk on g.pkid = pk.id
left join materialKind mk on f.mkid = mk.id
join (select lpnid,max(date) as date from liquidPositionHistory group by lpnid) mmm on a.lpnid = mmm.lpnid and c.date = mmm.date
";
return(oData.QueryForDateSet(strSQL));
}
}
/// <summary>
/// 获取沥青罐当前数据
/// </summary>
/// <param name="dStart">开始日期</param>
/// <param name="dEnd">结束日期</param>
/// <param name="strNo">沥青罐编号</param>
/// <returns></returns>
public DataSet QueryData(DateTime?dStart, DateTime?dEnd, string strNo)
{
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
string strSQL = string.Format(@"
select
a.pId as '产品标识',
a.mId as '材料标识',
a.eiId as '设备标识',
b.no as '沥青罐编号',
case
when a.pid is not null then g.name
when a.mid is not null then f.name
end as '沥青名称',
case
when a.pid is not null then pm.model
when a.mid is not null then mm.model
end as '规格型号',
b.diameter as '罐直径',
b.height as '罐子高度',
b.kind as '罐子样式',
c.value as '液位高度',
case
when a.pid is not null and e.density is null then 1
when a.pid is not null and e.density is not null then cast(e.density as float)
when a.mid is not null and d.density is null then 1
when a.mid is not null and d.density is not null then cast(d.density as float)
end as '沥青密度',
d.density as '材料密度',
e.density as '产品密度',
c.date as '日期',
0.0 as '存储数量'
from liquidMatterStockList a
inner join equipmentInformation b on a.eiid = b.id
inner join liquidPositionHistory c on a.lpnid = c.lpnid
left join material d on a.mid = d.id
left join materialName f on d.mnid = f.id
left join materialModel mm on d.mmid = mm.id
left join product e on a.pid = e.id
left join productName g on e.pnid = g.id
left join productModel pm on e.pmid=pm.id
join (select lpnid,max(date) as date from liquidPositionHistory group by lpnid) mmm on a.lpnid = mmm.lpnid and c.date = mmm.date
where 1=1
and c.date >= '{0}'
and c.date <= '{1}'
and b.no = '{2}'
", dStart.Value.ToString(), dEnd.Value.ToString(), strNo);
return(oData.QueryForDateSet(strSQL));
}
}
/// <summary>
/// 获取可用材料名称
/// </summary>
/// <returns></returns>
public DataSet QueryMaterialName()
{
string strSQL = @"
select distinct
e.id,e.name
from stockNote a
left join voitureInfo b on a.viid = b.id
left join indent c on a.iid = c.id
left join material d on c.mid = d.id
left join materialName e on d.mnid = e.id
where a.flowmetervalue is not null
";
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
return(oData.QueryForDateSet(strSQL));
}
}
/// <summary>
/// 获取可用沥青罐编号
/// </summary>
/// <returns></returns>
public DataSet QueryNo()
{
string strSQL = @"
select distinct b.id,b.no
from liquidMatterStockList a
inner join equipmentInformation b on a.eiid = b.id
inner join liquidPositionHistory c on a.lpnid = c.id
left join material d on a.mid = d.id
left join materialName f on d.mnid = f.id
left join materialModel mm on d.mmid = mm.id
left join product e on a.pid = e.id
left join productName g on e.pnid = g.id
left join productModel pm on e.pmid=pm.id
";
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
return(oData.QueryForDateSet(strSQL));
}
}
/// <summary>
/// 获取汽车衡数据与流量数据对比数据集
/// </summary>
/// <param name="dStart">开始日期</param>
/// <param name="dEnd">结束日期</param>
/// <param name="strNameID">材料名称标识</param>
/// <param name="strModelID">规格型号标识</param>
/// <returns></returns>
public DataSet Query(DateTime?dStart, DateTime?dEnd, string strNameID, string strModelID)
{
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
if (strNameID != null && strNameID != "")
{
strNameID = "and e.id = " + strNameID;
}
if (strModelID != null && strModelID != "")
{
strModelID = "and f.id= " + strModelID;
}
string strSQL = string.Format(@"
select
a.inputdate as 日期,
b.no as 车牌号码,
e.name as 沥青名称,
f.model as 规格型号,
a.suttle as 称重数量,
a.flowmetervalue 流量数量,
a.flowmetervalue - a.suttle as 差异量,
(a.flowmetervalue - a.suttle)/a.suttle as 误差率
from stockNote a
left join voitureInfo b on a.viid = b.id
left join indent c on a.iid = c.id
left join material d on c.mid = d.id
left join materialName e on d.mnid = e.id
left join materialModel f on d.mmid = f.id
where a.flowmetervalue is not null
and a.inputDate >= '{0}'
and a.inputDate <= '{1}'
{2}
{3}
", dStart.Value.ToString(), dEnd.Value.ToString(), strNameID, strModelID);
return(oData.QueryForDateSet(strSQL));
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
this.BindMark();
SetKind();
EquipmentInformation entity = new EquipmentInfoLogic().GetDataByID(this.id);
using (DasherStation.common.SqlHelper oData = new DasherStation.common.SqlHelper())
{
try
{
this.cbxKind.SelectedValue = oData.QueryForDateSet(string.Format(@"
select a.id from equmentKind a
join equipmentName b on a.id = b.ekid
where b.id = {0}", entity.enId)).Tables[0].Rows[0][0].ToString();
}
catch
{
}
}
SetModel();
setDepartment();
setDocument();
this.txtNo.Text = entity.no;
this.cbxName.SelectedValue = entity.enId;
this.cbxModel.SelectedValue = entity.emId;
this.txtCount.Text = entity.count.ToString();
this.txtRMark.Text = entity.registrationMark;
this.dtpBegin.Value = entity.beginUseTime.Value;
if (entity.dId.HasValue)
{
this.cbxDId.SelectedValue = entity.dId;
}
this.txtPosition.Text = entity.installationPosition;
this.txtSumPower.Text = entity.sumPower.ToString();
this.txtAddMethod.Text = entity.addMethod;
this.txtPValue.Text = entity.primaryValue.ToString();
this.txtRemainsValue.Text = entity.remainsValue.ToString();
this.txtDYear.Text = entity.depreciationYear.ToString();
this.txtFactoryNo.Text = entity.factoryNo;
if (entity.produceDate.HasValue)
{
this.dtpProduce.Value = entity.produceDate.Value;
}
this.txtWorkman.Text = entity.workMan;
this.txtContackman.Text = entity.contactMan;
this.txtContactMethod.Text = entity.contactMethod;
this.txtZip.Text = entity.postCode;
this.txtProducer.Text = entity.producer;
if (entity.dId2.HasValue)
{
this.cbxDocument.SelectedValue = entity.dId2;
}
this.txtAdress.Text = entity.factoryAddress;
if (entity.mark != null)
{
this.cbx_mark.SelectedValue = entity.mark;
}
}
