public void RunPayablesRecord(string id)
{
//收款日期 date
//客户名称 guestName
//支票号码 invoiceNum
//发生金额 historyAlreadyMoney
//拖欠总金额 arrearsTotalMoney
//发票号 TheIncludeMoney
//备注 backup
SqlHelper helper = new SqlHelper();
string strSQL = "SELECT fundsRecorder.inputdate as date,providerInfo.name as guestName,fundsRecorder.checkNo as invoiceNum,";
strSQL += " fundsRecorder.paidUp as historyAlreadyMoney,fundsRecorder.cash2 as arrearsTotalMoney,";
strSQL += " fundsRecorder.invoiceNo as TheIncludeMoney,fundsRecorder.remark as [backup]";
strSQL += " FROM dbo.fundsRecorder LEFT OUTER JOIN";
strSQL += " dbo.providerInfo ON dbo.fundsRecorder.customerId = dbo.providerInfo.id";
strSQL += " where type='付款'";
strSQL += " and customerId=" + id;
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(strSQL);
DataSet ds = helper.QueryForDateSet(oArrayList);
PayablesRecord oPayablesRecord = new PayablesRecord();
oPayablesRecord.Source = ds;
oPayablesRecord.ShowReport();
}
public void RunShouldOutRecord(string id)
{
//单据号码 docNum
//供应商/运输单位名称 supplierName
//合同名称 invoiceNum
//货物总重 shouldMoney
//总金额 alreadyMoney
//票据数 arrearsMoney
SqlHelper helper = new SqlHelper();
string strSQL = string.Format(@"
select
a.name as supplierName,
c.id as docNum,
b.name as invoiceNum,
c.totalWeight as shouldMoney,
c.sum as alreadyMoney,
c.count as arrearsMoney
from providerInfo a
left join stockContract b on b.piid = a.id
right join stockMaterialSettlement c on c.scid = b.id
where a.id = {0} and c.checkupMan is not null
", id);
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(strSQL);
DataSet ds = helper.QueryForDateSet(oArrayList);
ShouldOutRecord oShouldOutRecord = new ShouldOutRecord();
oShouldOutRecord.Source = ds;
oShouldOutRecord.ShowReport();
}
public DataSet QueryEPInfo()
{
string strSQL = "select id,no from equipmentInformation";
using (DasherStation.common.SqlHelper h = new DasherStation.common.SqlHelper())
{
return(h.QueryForDateSet(strSQL));
}
}
public void RunTransportCheck(string id)
{
//材料种类 sort
//材料名称 materialName
//规格 model
//起运地 startSite
//止运地 endSite
//运输数量 quantity
//运输距离 distance
//单价 unitPrice
//金额（元） money
SqlHelper helper = new SqlHelper();
String sql = "select tsmd.id, coalesce(pn.name,mn.name) as materialName,coalesce(pm.model,mm.model) as model,coalesce(pk.sort,mk.sort) as sort ";
sql += ",site1.site as startSite,site2.site as endSite,distance,price as unitPrice,tsmId,case mark when 1 then '已终止' else '执行中' end mark,tsmd.count as quantity,tsmd.sum as money,tsm.remark as formula";
sql += " from transportGoodsInformationCorresponding tgi inner join transportSettlementMethod tsm on tsmid=tsm.id";
sql += " inner join transportSettlementDetail tsmd on tsmd.tgicId=tgi.id ";
sql += " left join material m on m.id=mid";
sql += " left join materialName mn on m.mnid=mn.id left join materialModel mm on m.mmid=mm.id ";
sql += " left join materialKind mk on mn.mkid=mk.id";
sql += " left join product p on p.id=pid left join productName pn on p.pnid=pn.id ";
sql += " left join productModel pm on p.pmid=pm.id ";
sql += " left join productKind pk on pn.pkid=pk.id";
sql += " left join site site1 on tgi.sid1=site1.id";
sql += " left join site site2 on tgi.sid1=site2.id";
sql += " where tsmd.tsid=" + id;
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(sql);
DataSet ds = helper.QueryForDateSet(oArrayList);
TransportCheck oYearStockPlan = new TransportCheck();
oYearStockPlan.Source = ds;
CReportTools tools = new CReportTools();
DataTable dt = ds.Tables[0];
Decimal money = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
money += Convert.ToDecimal(dt.Rows[i]["money"]);
}
oYearStockPlan.SumMoney = tools.ToUpper(money);
oYearStockPlan.AccountNum = "3444";
oYearStockPlan.ContractNum = "100086";
oYearStockPlan.CoName = "中百";
oYearStockPlan.Num = "1111";
oYearStockPlan.InvoiceNo = "0001";
oYearStockPlan.Supplier = "中百";
oYearStockPlan.Telephone = "138888888";
oYearStockPlan.Auditor = "刘淼";
oYearStockPlan.Examine = "bbb";
oYearStockPlan.Approve = "ccc";
oYearStockPlan.AuditorYmd = "2008年5月21号";
oYearStockPlan.ExamineYmd = "2008年5月22号";
oYearStockPlan.ApproveYmd = "2008年5月23号";
oYearStockPlan.ShowReport();
}
public void RunTransportDayStatistics(string startyear, string endyear)
{
SqlHelper sqlHelper = new SqlHelper();
StringBuilder sqlStr = new StringBuilder();
ArrayList list = new ArrayList();
sqlStr.Append("SELECT tu.name AS transportName, site.site as startSite , mk.sort, mn.name AS materialName, mm.model,");
sqlStr.Append("SUM(sn.suttle) AS num ");
sqlStr.Append("FROM materialKind AS mk INNER JOIN ");
sqlStr.Append("indent AS it INNER JOIN ");
sqlStr.Append("transportContract AS tc INNER JOIN ");
sqlStr.Append(" transportGoodsInformationCorresponding AS tgic ON tgic.tcId = tc.id INNER JOIN ");
sqlStr.Append("stockNote AS sn ON tgic.id = sn.tgicId INNER JOIN ");
sqlStr.Append("transportUnit AS tu ON tc.tuId = tu.id ON it.id = sn.iId INNER JOIN ");
sqlStr.Append("material AS m ON it.mId = m.id INNER JOIN ");
sqlStr.Append("materialName AS mn ON m.mnId = mn.id INNER JOIN ");
sqlStr.Append("materialModel AS mm ON m.mmId = mm.id ON mk.id = mn.mkId INNER JOIN ");
sqlStr.Append("site ON sn.sId1 = site.id ");
sqlStr.Append("WHERE (sn.inputDate >= " + "'" + startyear + "'" + ")");
sqlStr.Append("AND (sn.inputDate <= " + "'" + endyear + "'" + ")");
sqlStr.Append("GROUP BY tu.name, site.site, mk.sort, mn.name, mm.model ");
list.Add(sqlStr.ToString());
DataSet ds = sqlHelper.QueryForDateSet(list);
//日期 date
//运输单位名称 transportName
//起运地 startSite
//运输距离 distance
//材料种类 sort
//材料名称 materialName
//材料规格 model
//入库数量 num
TransportDayStatistics oTransportStatistics = new TransportDayStatistics();
oTransportStatistics.Source = ds;
oTransportStatistics.CoName = "中百";
oTransportStatistics.Num = "1112222";
oTransportStatistics.Table = "刘淼";
oTransportStatistics.Examine = "刘淼";
oTransportStatistics.Date = startyear + "--" + endyear;
oTransportStatistics.ShowReport();
}
public void RunInputDayStatistics(string startyear, string endyear)
{
//日期 date
//材料种类 sort
//材料名称 materialName
//规格型号 model
//入库数量 inputNum
//计量单位 measure
//单据数量 num
//累计入库量 aggregateInputNum
SqlHelper sqlHelper = new SqlHelper();
StringBuilder sqlStr = new StringBuilder();
ArrayList list = new ArrayList();
sqlStr.Append(" SELECT f.sort, d.name as materialName, a.mnId, a.mmId, e.model, SUM(c.suttle) AS inputNum,");
sqlStr.Append(" COUNT(*) AS num, b.mId,");
sqlStr.Append("(SELECT SUM(sn.suttle) AS ysuttle FROM stockNote AS sn INNER JOIN");
sqlStr.Append(" indent AS mi ON sn.iId = mi.id ");
sqlStr.Append(" WHERE (mi.mId = b.mId) AND (sn.inputDate< = " + "'" + endyear + "'" + ")) AS aggregateInputNum ");
sqlStr.Append("FROM material AS a INNER JOIN");
sqlStr.Append(" indent AS b ON a.id = b.mId INNER JOIN ");
sqlStr.Append("stockNote AS c ON b.id = c.iId INNER JOIN ");
sqlStr.Append("materialName AS d ON a.mnId = d.id INNER JOIN ");
sqlStr.Append("materialModel AS e ON a.mmId = e.id INNER JOIN ");
sqlStr.Append("materialKind AS f ON d.mkId = f.id ");
//sqlStr.Append(" WHERE (c.inputDate BETWEEN '2009-1-1' AND '2009-3-5') ");
sqlStr.Append(" WHERE (c.inputDate >= " + "'" + startyear + "'" + ")");
sqlStr.Append(" and (c.inputDate<=" + "'" + endyear + "'" + ")");
sqlStr.Append("GROUP BY d.name, f.sort, e.model, a.mnId, a.mmId, b.mId");
list.Add(sqlStr.ToString());
DataSet ds = sqlHelper.QueryForDateSet(list);
InputDayStatistics oInputDayStatistics = new InputDayStatistics();
oInputDayStatistics.Source = ds;
oInputDayStatistics.CoName = "中百";
oInputDayStatistics.Num = "1112222";
oInputDayStatistics.Table = "刘淼";
oInputDayStatistics.Examine = "刘淼";
oInputDayStatistics.Date = startyear + "--" + endyear;
oInputDayStatistics.ShowReport();
}
public void RunShouldInputRecord(string id)
{
//单据号码 docNum
//供应商/运输单位名称 supplierName
//合同名称 invoiceNum
//总重量 shouldMoney
//总金额 alreadyMoney
//票具总数 arrearsMoney
SqlHelper helper = new SqlHelper();
String sql = "select a.[name] as supplierName,c.id as docNum,b.name as invoiceNum,c.totalWeight as shouldMoney,c.sum as alreadyMoney,c.count as arrearsMoney,c.remark as '备注'from clientInfo a left join sellContract b on b.ciid = a.id right join sellProductSettlement c on c.scid = b.id ";
sql += " where a.id = " + id;
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(sql);
DataSet ds = helper.QueryForDateSet(oArrayList);
ShouldInputRecord oShouldInputRecord = new ShouldInputRecord();
oShouldInputRecord.Source = ds;
oShouldInputRecord.ShowReport();
}
public void RunYearProducePlan(string id)
{
SqlHelper helper = new SqlHelper();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT productionPlanDetail.*,name ,model ,sort");
sql.Append(" FROM dbo.productionPlan RIGHT OUTER JOIN");
sql.Append(" dbo.productionPlanDetail ON dbo.productionPlan.id = dbo.productionPlanDetail.ppId LEFT OUTER JOIN");
sql.Append(" dbo.productKind INNER JOIN");
sql.Append(" dbo.productName ON dbo.productKind.id = dbo.productName.pkId INNER JOIN");
sql.Append(" dbo.product INNER JOIN");
sql.Append(" dbo.productModel ON dbo.product.pmId = dbo.productModel.id ON dbo.productName.id = dbo.product.pnId ON ");
sql.Append(" dbo.productionPlanDetail.pId = dbo.product.id");
sql.Append(" where productionPlan.id=" + id);
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(sql.ToString());
DataSet ds = helper.QueryForDateSet(oArrayList);
YearProducePlan oYearProducePlan = new YearProducePlan();
oYearProducePlan.Source = ds;
//年度
oYearProducePlan.Year = "2009";
//公司名称
oYearProducePlan.CoName = "中百";
//文档编号
oYearProducePlan.Number = "20090101";
//制表
oYearProducePlan.Tabler = "张三";
//审核
oYearProducePlan.Auditer = "李四";
//审批
oYearProducePlan.Approvaler = "王五";
// 显示报表
oYearProducePlan.ShowReport();
}
public DataTable GetConfirmName(Auditing auditing)
{
return(sqlHelper.QueryForDateSet("select assessor,checkupMan from " + auditing.TableName + " where " + auditing.IdName + "=" + auditing.Id.ToString()).Tables[0]);
}
public void RunMixingMachineProduceNotice(string id)
{
SqlHelper helper = new SqlHelper();
StringBuilder sql = new StringBuilder();
sql.Append(" select pk.sort, pnt.id,p.pnid,p.pmid,pn.name,pm.model,pnt.planQuantity,Convert(varchar ");
sql.Append(" (10),pnt.startDate,120) as startDate,ei.no,pnt.assessor,pnt.checkupMan,pnt.notifyMan,Convert ");
sql.Append(" (varchar(10),pnt.notifyDate,120) as notifyDate,psp.ppid,tsp.tpid,ptId,psp.eiid from ");
sql.Append(" produceNotice pnt,produceSendProportionDetail psp,produceProportion ");
sql.Append(" pp,product p,productName pn,productModel pm,productKind pk,targetSendProportionDetail ");
sql.Append(" tsp,equipmentInformation ei where ei.id=psp.eiid and pnt.spId=psp.spId and psp.ppid=pp.id and pp.pid=p.id and ");
sql.Append(" p.pnid=pn.id and p.pmid=pm.id and pn.pkid=pk.id and pnt.spid=tsp.spid ");
sql.Append(" and pnt.id=" + id);
sql.Append(" order by notifyDate desc ");
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(sql.ToString());
DataSet ds = helper.QueryForDateSet(oArrayList);
//if (ds.Tables[0].Rows.Count > 0)
//{
// string sendProportionId = ds.Tables[0].Rows[0]["id"];
// ArrayList sqlStr = new ArrayList();
// sqlStr.Add("");
// ProduceDataSet.生产配合比信息DataTable o生产配合比信息DataTable = new ProduceDataSet.生产配合比信息DataTable();
// ProduceDataSet.生产配合比信息Row o生产配合比信息Row = (ProduceDataSet.生产配合比信息Row)o生产配合比信息DataTable.NewRow();
// o生产配合比信息Row.Proportion1 = "生产配合比编号001";
// o生产配合比信息Row.Proportion2 = "沥青1";
// o生产配合比信息Row.Proportion3 = "石粉1";
// o生产配合比信息Row.Proportion4 = "4_热料仓1";
// o生产配合比信息Row.Proportion5 = "5_热料仓1";
// o生产配合比信息Row.Proportion6 = "生产配合比编号001";
// o生产配合比信息Row.Proportion7 = "沥青1";
// o生产配合比信息Row.Proportion8 = "石粉1";
// o生产配合比信息Row.ProportionValue1 = "生产配合比编号001";
// o生产配合比信息Row.ProportionValue2 = "沥青1";
// o生产配合比信息Row.ProportionValue3 = "石粉1";
// o生产配合比信息Row.ProportionValue4 = "4_热料仓1";
// o生产配合比信息Row.ProportionValue5 = "5_热料仓1";
// o生产配合比信息Row.ProportionValue6 = "生产配合比编号001";
// o生产配合比信息Row.ProportionValue7 = "沥青1";
// o生产配合比信息Row.ProportionValue8 = "石粉1";
// o生产配合比信息DataTable.Add生产配合比信息Row(o生产配合比信息Row);
// ds.Tables.Add(o生产配合比信息DataTable);
// ProduceDataSet.目标配合比信息DataTable o目标配合比信息DataTable = new ProduceDataSet.目标配合比信息DataTable();
// ProduceDataSet.目标配合比信息Row o目标配合比信息Row1 = (ProduceDataSet.目标配合比信息Row)o目标配合比信息DataTable.NewRow();
// ProduceDataSet.目标配合比信息Row o目标配合比信息Row2 = (ProduceDataSet.目标配合比信息Row)o目标配合比信息DataTable.NewRow();
// o目标配合比信息Row1.目标配合比编号 = "目标配合比编号001";
// o目标配合比信息Row1.序号 = "序号1";
// o目标配合比信息Row1.材料种类 = "材料种类1";
// o目标配合比信息Row1.材料名称 = "材料名称1";
// o目标配合比信息Row1.规格型号 = "规格型号1";
// o目标配合比信息Row1.油石比 = "油石比1";
// o目标配合比信息Row1.产地 = "产地1";
// o目标配合比信息Row1.生产厂家 = "生产厂家1";
// o目标配合比信息Row2.目标配合比编号 = "目标配合比编号001";
// o目标配合比信息Row2.序号 = "序号2";
// o目标配合比信息Row2.材料种类 = "材料种类2";
// o目标配合比信息Row2.材料名称 = "材料名称2";
// o目标配合比信息Row2.规格型号 = "规格型号2";
// o目标配合比信息Row2.油石比 = "油石比2";
// o目标配合比信息Row2.产地 = "产地2";
// o目标配合比信息Row2.生产厂家 = "生产厂家2";
// o目标配合比信息DataTable.Add目标配合比信息Row(o目标配合比信息Row1);
// o目标配合比信息DataTable.Add目标配合比信息Row(o目标配合比信息Row2);
// ds.Tables.Add(o目标配合比信息DataTable);
//}
MixingMachineProduceNotice oMixingMachineProduceNotice = new MixingMachineProduceNotice();
oMixingMachineProduceNotice.Source = ds;
//公司名称
oMixingMachineProduceNotice.CoName = "中百";
//编号
oMixingMachineProduceNotice.Number = "20090101";
//制表
oMixingMachineProduceNotice.Tabler = "张三";
// 部门主管
oMixingMachineProduceNotice.Manager = "李四";
//制表日期
oMixingMachineProduceNotice.CDate = "2009年13月36日";
oMixingMachineProduceNotice.备注 = "备注";
// 显示报表
oMixingMachineProduceNotice.ShowReport();
}
public void RunYearMaterialRequirementsPlanning(string ppid)
{
SqlHelper helper = new SqlHelper();
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append(" select tpd.mid,mn.name,mm.model,mk.sort ");
sqlstr.Append(",cast(produceQuantity*Proportion as decimal(38, 2))quantity ");
sqlstr.Append(",cast(january*Proportion as decimal(38, 2))january");
sqlstr.Append(",cast(february*Proportion as decimal(38, 2)) february");
sqlstr.Append(",cast(march*Proportion as decimal(38, 2)) march");
sqlstr.Append(",cast(april*Proportion as decimal(38, 2)) april");
sqlstr.Append(",cast(may*Proportion as decimal(38, 2)) may");
sqlstr.Append(",cast(june*Proportion as decimal(38, 2)) june");
sqlstr.Append(",cast(july*Proportion as decimal(38, 2))july");
sqlstr.Append(",cast(august*Proportion as decimal(38, 2)) august");
sqlstr.Append(",cast(september*Proportion as decimal(38, 2))september");
sqlstr.Append(",cast(october*Proportion as decimal(38, 2))october");
sqlstr.Append(",cast(november*Proportion as decimal(38, 2))november");
sqlstr.Append(",cast(december*Proportion as decimal(38, 2))december");
//sqlstr.Append(",getdate() ");
sqlstr.Append(" from productionPlanDetail ppd");
sqlstr.Append(",(");
sqlstr.Append(" select tpd.tpId,mId,targetProportionValue value,'1' type from targetProportionDetail tpd");
sqlstr.Append(" union all");
sqlstr.Append(" select pd.pid tpid,mid,proportionValue value,'2' type from proportionDetail pd");
sqlstr.Append(") as tpd");
sqlstr.Append(",(");
sqlstr.Append(" select tpd.mid,tpd.tpid,tpd.targetProportionValue/(select sum(targetProportionValue) from targetProportionDetail where tpId=tpd.tpid) Proportion,'1' type from targetProportionDetail tpd");
sqlstr.Append(" union all");
sqlstr.Append(" select pd.mid,pd.pid as tpid,pd.proportionValue/(select sum(proportionValue) from proportionDetail where pId=pd.pid) Proportion,'2' type from proportionDetail pd");
sqlstr.Append(") as Proportion");
sqlstr.Append(",(");
sqlstr.Append(" select p.id,sort from product p,productName pn,productKind pk where p.pnid=pn.id and pn.pkid=pk.id");
sqlstr.Append(") as p ,material m,materialName mn,materialModel mm, materialKind mk ");
sqlstr.Append("where ppd.tpid=tpd.tpid and ppd.tpid=Proportion.tpid and tpd.mid=Proportion.mid and ppd.pid=p.id ");
sqlstr.Append(" and tpd.mid=m.id and m.mnid=mn.id and m.mmid=mm.id and mn.mkId=mk.id ");
sqlstr.Append("and Proportion.type=(select case when p.sort='混合料' then 1 else 2 end) ");
sqlstr.Append("and tpd.type=(select case when p.sort='混合料' then 1 else 2 end) ");
sqlstr.Append("and ppid=" + ppid);
System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
oArrayList.Add(sqlstr.ToString());
DataSet ds = helper.QueryForDateSet(oArrayList);
YearMaterialRequirementsPlanning oYearMaterialRequirementsPlanning = new YearMaterialRequirementsPlanning();
oYearMaterialRequirementsPlanning.Source = ds;
//年度
oYearMaterialRequirementsPlanning.Year = "2009";
//公司名称
oYearMaterialRequirementsPlanning.CoName = "中百";
//文档编号
oYearMaterialRequirementsPlanning.Number = "20090101";
//制表
oYearMaterialRequirementsPlanning.Tabler = "张三";
//审核
oYearMaterialRequirementsPlanning.Auditer = "李四";
//审批
oYearMaterialRequirementsPlanning.Approvaler = "王五";
// 显示报表
oYearMaterialRequirementsPlanning.ShowReport();
}
public void RunTransportCheckDetail(string id)
{
string sql = "select coalesce(pn.name,mn.name) as materialName,coalesce(pm.model,mm.model) as model ";
sql += ",site1.site,site2.site,suttle,tgic.id did,tnc.inputDate,barcode,checkAccountMark2 mark, sn.inputdate as date";
sql += " ,vi.[no],providerInfo.name as supplierName,transportUnit.[name] as transportName,coalesce(pk.sort,mk.sort) as sort";
sql += ", sn.grossWeight,sn.tare,sn.suttle,personnelInfo.[name] as personnel";
sql += " from transportGoodsInformationCorresponding tgic ";
sql += " inner join transportSettlementDetail tsd on tsd.tgicid=tgic.id ";
sql += " inner join transportNoteCorresponding tnc on tnc.tsdid=tsd.id ";
sql += " inner join stockNote sn on sn.id=tnc.snid ";
sql += " left join material m on mid=m.id ";
sql += " left join materialName mn on m.mnid=mn.id left join materialModel mm on m.mmid=mm.id ";
sql += " left join product p on p.id=pid left join productName pn on p.pnid=pn.id ";
sql += " left join productModel pm on p.pmid=pm.id ";
sql += " left join site site1 on tgic.sid1=site1.id ";
sql += " left join site site2 on tgic.sid2=site2.id ";
sql += " left join transportSettlement ts on tsd.tsid=ts.id ";
sql += " left join voitureInfo vi on sn.viid=vi.id";
sql += " left join indent on sn.iId=indent.id";
sql += " left join stockContract on indent.scid=stockContract.id";
sql += " left join providerInfo on stockContract.piId=providerInfo.id";
sql += " left join personnelInfo on sn.piId1=personnelInfo.id";
sql += " left join transportUnit on vi.tuid=transportUnit.id";
sql += " left join materialKind mk on mn.mkid=mk.id";
sql += " left join productKind pk on pn.pkid=pk.id";
if (!string.IsNullOrEmpty(id))
{
sql += " where ts.id=" + id;
}
sql += " union all ";
sql += "select coalesce(pn.name,mn.name) as materialName,coalesce(pm.model,mm.model) as model ";
sql += ",site1.site,site2.site,suttle,tgic.id did,tnc.inputDate,barcode,checkAccountMark2 mark,cn.inputdate as date";
sql += " ,vi.[no],clientInfo.name as supplierName,transportUnit.[name] as transportName,coalesce(pk.sort,mk.sort) as sort";
sql += ", cn.grossWeight,cn.tare,cn.suttle,personnelInfo.[name] as personnel";
sql += " from transportGoodsInformationCorresponding tgic ";
sql += " inner join transportSettlementDetail tsd on tsd.tgicid=tgic.id ";
sql += " inner join transportNoteCorresponding tnc on tnc.tsdid=tsd.id ";
sql += " inner join consignmentNote cn on cn.id=tnc.snid ";
sql += " left join material m on mid=m.id ";
sql += " left join materialName mn on m.mnid=mn.id left join materialModel mm on m.mmid=mm.id ";
sql += " left join product p on p.id=pid left join productName pn on p.pnid=pn.id ";
sql += " left join productModel pm on p.pmid=pm.id ";
sql += " left join site site1 on tgic.sid1=site1.id ";
sql += " left join site site2 on tgic.sid2=site2.id ";
sql += " left join transportSettlement ts on tsd.tsid=ts.id ";
sql += " left join voitureInfo vi on cn.viid=vi.id";
sql += " left join invoice on cn.iId=invoice.id";
sql += " left join sellContract on invoice.scid=sellContract.id";
sql += " left join clientInfo on sellContract.ciId=clientInfo.id";
sql += " left join personnelInfo on cn.piId1=personnelInfo.id";
sql += " left join transportUnit on vi.tuid=transportUnit.id";
sql += " left join materialKind mk on mn.mkid=mk.id";
sql += " left join productKind pk on pn.pkid=pk.id";
if (!string.IsNullOrEmpty(id))
{
sql += " where ts.id=" + id;
}
//日期 date
//车牌号码 no
//供应商名称 supplierName
//运输单位名称 transportName
//材料种类 sort
//材料名称 materialName
//规格型号 model
//毛重 grossWeight
//皮重 tare
//净重 suttle
//检斤员 personnel
SqlHelper helper = new SqlHelper();
ArrayList oArrayList = new ArrayList();
oArrayList.Add(sql);
DataSet ds = helper.QueryForDateSet(oArrayList);
TransportCheckDetail oTransportCheckDetail = new TransportCheckDetail();
oTransportCheckDetail.Source = ds;
oTransportCheckDetail.CoName = "中百";
oTransportCheckDetail.ShowReport();
}
public void RunSupplierDayStatistics(string startyear, string endyear)
{
//日期 date
//供应商名称 supplierName
//材料种类 sort
//材料名称 materialName
//规格型号 model
//入库数量 inputNum
//单据数量 num
//累计入库量 aggregateInputNum
StringBuilder sqlStr = new StringBuilder();
ArrayList list = new ArrayList();
SqlHelper sqlHelper = new SqlHelper();
sqlStr.Append("SELECT pr.name AS supplierName, mk.sort, mn.name AS materialName, mm.model,");
//sqlStr.Append("SUM(sn.suttle) AS inweight,COUNT(*) AS notecount,(select SUM(isn.suttle) as suttle ");
sqlStr.Append("SUM(sn.suttle) AS inputNum,COUNT(*) AS num, ");
sqlStr.Append("(SELECT SUM(isn.suttle) AS suttle ");
sqlStr.Append(" FROM stockNote AS isn INNER JOIN ");
sqlStr.Append("indent AS ii ON isn.iId = ii.id INNER JOIN ");
sqlStr.Append("stockContract AS sc ON ii.scId = sc.id INNER JOIN ");
sqlStr.Append("providerInfo AS ipr ON sc.piId = ipr.id ");
sqlStr.Append(" WHERE (ii.mId = it.mId) AND ");
sqlStr.Append("(ipr.name = pr.name)");
//sqlStr.Append("from stockNote isn,indent ii where isn.iid=ii.id and ii.mid=it.mid ");
//sqlStr.Append("and year(isn.inputDate)='2009' group by ii.mid) as ysuttle ");
sqlStr.Append("and isn.inputDate <= " + "'" + endyear + "'" + " group by ii.mid,ipr.name) as aggregateInputNum ");
sqlStr.Append("FROM stockNote AS sn INNER JOIN ");
sqlStr.Append("indent AS it ON sn.iId = it.id INNER JOIN ");
sqlStr.Append("stockContract AS sc ON it.scId = sc.id INNER JOIN ");
sqlStr.Append(" providerInfo AS pr ON sc.piId = pr.id INNER JOIN ");
sqlStr.Append("material AS m ON it.mId = m.id INNER JOIN ");
sqlStr.Append(" materialName AS mn ON m.mnId = mn.id INNER JOIN ");
sqlStr.Append("materialModel AS mm ON m.mmId = mm.id INNER JOIN ");
sqlStr.Append("materialKind AS mk ON mn.mkId = mk.id ");
//sqlStr.Append("WHERE (sn.inputDate >= '2009-3-9') AND (sn.inputDate <= '2009-4-9') ");
sqlStr.Append("WHERE (sn.inputDate >= " + "'" + startyear + "'" + ") AND (sn.inputDate <= " + "'" + endyear + "'" + ") ");
sqlStr.Append("GROUP BY it.mid,pr.name, mk.sort, mn.name, mm.model ");
sqlStr.Append(" union all ");
sqlStr.Append("SELECT pr.name AS provider, mk.sort, mn.name AS mname, mm.model,0, 0, ");
sqlStr.Append("(select SUM(isn.suttle) as suttle ");
//sqlStr.Append("from stockNote isn,indent ii where isn.iid=ii.id and ii.mid=it.mid and year(isn.inputDate)='2009' ");
sqlStr.Append("from stockNote isn,indent ii where isn.iid=ii.id and ii.mid=it.mid and isn.inputDate<=" + "'" + endyear + "' ");
sqlStr.Append("group by ii.mid) as ysuttle ");
sqlStr.Append("FROM stockNote AS sn INNER JOIN ");
sqlStr.Append("indent AS it ON sn.iId = it.id INNER JOIN ");
sqlStr.Append("stockContract AS sc ON it.scId = sc.id INNER JOIN ");
sqlStr.Append("providerInfo AS pr ON sc.piId = pr.id INNER JOIN ");
sqlStr.Append("material AS m ON it.mId = m.id INNER JOIN ");
sqlStr.Append(" materialName AS mn ON m.mnId = mn.id INNER JOIN ");
sqlStr.Append("materialModel AS mm ON m.mmId = mm.id INNER JOIN ");
sqlStr.Append("materialKind AS mk ON mn.mkId = mk.id ");
//sqlStr.Append("WHERE (sn.inputDate >= '2009-1-1') AND (sn.inputDate <= '2009-3-9') and ");
sqlStr.Append("WHERE (sn.inputDate >= " + "'" + startyear + "'" + ") AND (sn.inputDate <=" + "'" + endyear + "'" + ") and ");
sqlStr.Append("m.id not in(select i.mid from stockNote sn,indent i where sn.iid=i.id and ");
sqlStr.Append(" sn.inputDate>=" + "'" + startyear + "'" + "and sn.inputdate <=" + "'" + endyear + "'");
sqlStr.Append("group by i.mid) ");
sqlStr.Append("GROUP BY it.mid,pr.name, mk.sort, mn.name, mm.model ");
list.Add(sqlStr.ToString());
DataSet ds = sqlHelper.QueryForDateSet(list);
SupplierDayStatistics oSupplierStatistics = new SupplierDayStatistics();
oSupplierStatistics.Source = ds;
oSupplierStatistics.CoName = "中百";
oSupplierStatistics.Num = "1112222";
oSupplierStatistics.Table = "刘淼";
oSupplierStatistics.Examine = "刘淼";
oSupplierStatistics.Date = startyear + "--" + endyear;
oSupplierStatistics.ShowReport();
}