//核算单查询
public DataTable SelectStockMaterialSettlement(string ContractNo, string ContractName, string providerName)
{
LinkSelect ls = new LinkSelect();
DataSet ds = new DataSet();
ls.TableNames.Add("stockMaterialSettlement");
ls.TableNames.Add("stockContract");
ls.LinkConds.Add("stockMaterialSettlement.scId=stockContract.id");
ls.TableNames.Add("providerInfo");
ls.LinkConds.Add("stockContract.piId=providerInfo.id");
ls.Viewfields.Add("stockMaterialSettlement.id");
ls.Viewfields.Add("stockContract.[name] as 合同名称");
ls.Viewfields.Add("stockContract.[no] as 合同编号");
ls.Viewfields.Add("providerInfo.[name] as 供应商名");
ls.Viewfields.Add("stockMaterialSettlement.sum as 总金额");
ls.Viewfields.Add("stockMaterialSettlement.inputdate as 日期时间");
ls.Viewfields.Add("stockMaterialSettlement.[name] as 录入人");
ls.Viewfields.Add("stockMaterialSettlement.count as 票具总数");
ls.Viewfields.Add("stockMaterialSettlement.remark as 备注");
if (!ContractNo.Trim().Equals(""))
{
ls.Conds.Add("stockContract.[no]=" + "'" + ContractNo + "'");
}
if (!ContractName.Trim().Equals(""))
{
ls.Conds.Add("stockContract.[name]=" + "'" + ContractName + "'");
}
if (!providerName.Trim().Equals(""))
{
ls.Conds.Add("providerInfo.[name]=" + "'" + providerName + "'");
}
ds = ls.LeftLinkOpen();
return(ds.Tables[0]);
}
//材料年度采购计划
public void RunStockPlan(string id)
{
LinkSelect linkS = new LinkSelect();
linkS.TableNames.Add("stockPlanDetail");
linkS.TableNames.Add("stockPlan");
linkS.LinkConds.Add("stockPlanDetail.spId=stockPlan.Id");
linkS.TableNames.Add("material");
linkS.LinkConds.Add("stockPlanDetail.mId=material.id");
linkS.TableNames.Add("materialName");
linkS.LinkConds.Add("material.mnId=materialName.id");
linkS.TableNames.Add("materialModel");
linkS.LinkConds.Add("material.mmId=materialModel.id");
linkS.TableNames.Add("materialKind");
linkS.LinkConds.Add("materialName.mkId=materialKind.id");
linkS.Viewfields.Add(" * ");
linkS.Conds.Add("stockPlan.id=" + id);
DataSet ds = linkS.LeftLinkOpen();
StockPlan oStockPlan = new StockPlan();
oStockPlan.Source = ds;
oStockPlan.Year = "2009";
oStockPlan.CoName = "中百";
oStockPlan.DocNum = "11000111";
oStockPlan.Table = "刘淼";
oStockPlan.Examine = "bb";
oStockPlan.Approve = "ccc";
oStockPlan.ShowReport();
}
public void RunStockCheck(string id)
{
LinkSelect linkS = new LinkSelect();
linkS.TableNames.Add("stockMaterialSettlementDetail");
linkS.TableNames.Add("stockMaterialSettlement");
linkS.LinkConds.Add("stockMaterialSettlementDetail.smsId=stockMaterialSettlement.id");
linkS.TableNames.Add("indent");
linkS.LinkConds.Add("stockMaterialSettlementDetail.iId=indent.id");
linkS.TableNames.Add("material");
linkS.LinkConds.Add("indent.mId=material.id");
linkS.TableNames.Add("materialName");
linkS.LinkConds.Add("material.mnId=materialName.id");
linkS.TableNames.Add("materialModel");
linkS.LinkConds.Add("material.mmId=materialModel.id");
linkS.TableNames.Add("materialKind");
linkS.LinkConds.Add("materialName.mkId=materialKind.id");
linkS.Viewfields.Add("materialKind.sort");
linkS.Viewfields.Add("materialName.[name]");
linkS.Viewfields.Add("materialModel.model");
linkS.Viewfields.Add("stockMaterialSettlementDetail.[sum] as money");
linkS.Viewfields.Add("stockMaterialSettlementDetail.count as suttle");
linkS.Viewfields.Add("indent.unitPrice");
linkS.Conds.Add("stockMaterialSettlement.id=" + id);
DataSet ds = linkS.LeftLinkOpen();
//SqlHelper helper = new SqlHelper();
//String sql = "Select voitureInfo.[no] as sort,stockContract.[name],stockNote.iId as 合同明细编号,materialName.[name] as 材料名称,materialModel.model,stockNote.grossWeight as 车辆毛重,stockNote.tare as 车辆皮重,stockNote.suttle,site1.site as 起运地,site2.site as 止运地,stockNote.inputdate as 日期时间,stockNote.remark as 备注,stockNote.barCode as 条形码,unitPrice,unitPrice * stockNote.suttle as money From stockMaterialNoteCorresponding Left Outer Join stockNote ON stockMaterialNoteCorresponding.snId=stockNote.id Left Outer Join stockMaterialSettlementDetail ON stockMaterialNoteCorresponding.smsdId=stockMaterialSettlementDetail.id Left Outer Join stockMaterialSettlement ON stockMaterialSettlementDetail.smsId=stockMaterialSettlement.id Left Outer Join stockContract ON stockMaterialSettlement.scId=stockContract.id Left Outer Join voitureInfo ON stockNote.viId=voitureInfo.id Left Outer Join transportUnit ON voitureInfo.tuId=transportUnit.id Left Outer Join site site1 ON stockNote.sId1=site1.id Left Outer Join site site2 ON stockNote.sId2=site2.id Left Outer Join transportGoodsInformationCorresponding ON stockNote.tgicId=transportGoodsInformationCorresponding.id Left Outer Join transportContract ON transportGoodsInformationCorresponding.tcId=transportContract.id Left Outer Join indent ON stockNote.iId=indent.id Left Outer Join material ON indent.mId=material.id Left Outer Join materialName ON material.mnId=materialName.id Left Outer Join materialModel ON material.mmId=materialModel.id where 1=1 ";
//System.Collections.ArrayList oArrayList = new System.Collections.ArrayList();
//oArrayList.Add(sql);
//DataSet ds = helper.QueryForDateSet(oArrayList);
StockCheck oStockCheck = new StockCheck();
oStockCheck.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"]);
}
oStockCheck.SumMoney = tools.ToUpper(money);
oStockCheck.AccountNum = id.ToString();
oStockCheck.ContractNum = "100086";
oStockCheck.CoName = "中百科技有限公司";
oStockCheck.Num = "1111";
oStockCheck.InvoiceNo = "0001";
oStockCheck.Supplier = "中百";
oStockCheck.Telephone = "138888888";
oStockCheck.Auditor = "刘淼";
oStockCheck.Examine = "bbb";
oStockCheck.Approve = "ccc";
oStockCheck.AuditorYmd = "2008年5月21号";
oStockCheck.ExamineYmd = "2008年5月22号";
oStockCheck.ApproveYmd = "2008年5月23号";
oStockCheck.ShowReport();
}
public DataTable SelectMixBitumenDetachData(string year, string month, string pName, string pModel)
{
LinkSelect linkS = new LinkSelect();
linkS.TableNames.Add("WaitDetachExpenditure");
linkS.TableNames.Add("expenditureName expenditureName1");
linkS.LinkConds.Add("WaitDetachExpenditure.ekId=expenditureName1.id");
linkS.TableNames.Add("expenditureName expenditureName2");
linkS.LinkConds.Add("WaitDetachExpenditure.enId=expenditureName2.id");
linkS.TableNames.Add("expenditureName expenditureName3");
linkS.LinkConds.Add("WaitDetachExpenditure.ecId=expenditureName3.id");
linkS.TableNames.Add("equipmentInformation");
linkS.LinkConds.Add("WaitDetachExpenditure.eiId=equipmentInformation.id");
linkS.TableNames.Add("product");
linkS.LinkConds.Add("WaitDetachExpenditure.pId=product.id");
linkS.TableNames.Add("productName");
linkS.LinkConds.Add("product.pnId=productName.id");
linkS.TableNames.Add("productModel");
linkS.LinkConds.Add("product.pmId=productModel.id");
linkS.Viewfields.Add("WaitDetachExpenditure.id");
linkS.Viewfields.Add("WaitDetachExpenditure.ekId");
linkS.Viewfields.Add("WaitDetachExpenditure.enId");
linkS.Viewfields.Add("WaitDetachExpenditure.ecId");
linkS.Viewfields.Add("WaitDetachExpenditure.pId");
linkS.Viewfields.Add("expenditureName1.name as 费用类别");
linkS.Viewfields.Add("expenditureName2.name as 费用名称");
linkS.Viewfields.Add("expenditureName3.name as 费用明细");
linkS.Viewfields.Add("WaitDetachExpenditure.expenditureDepict as 费用描述");
linkS.Viewfields.Add("WaitDetachExpenditure.year as 年");
linkS.Viewfields.Add("WaitDetachExpenditure.month as 月");
linkS.Viewfields.Add("WaitDetachExpenditure.eiId");
linkS.Viewfields.Add("equipmentInformation.[no] as 设备编号");
linkS.Viewfields.Add("WaitDetachExpenditure.number as 数量");
linkS.Viewfields.Add("WaitDetachExpenditure.unitPrice as 单价");
linkS.Viewfields.Add("WaitDetachExpenditure.money as 金额");
linkS.Viewfields.Add("WaitDetachExpenditure.inputDate as 录入时间");
linkS.Viewfields.Add("WaitDetachExpenditure.inputMan as 录入人");
linkS.Viewfields.Add("WaitDetachExpenditure.assessor as 审核人");
linkS.Viewfields.Add("WaitDetachExpenditure.checkupMan as 审批人");
linkS.Viewfields.Add("WaitDetachExpenditure.[convert] as 折算系数");
linkS.Viewfields.Add("WaitDetachExpenditure.isAuto as 自动还是手工");
linkS.Viewfields.Add("WaitDetachExpenditure.remark as 备注");
if (!year.Trim().Equals(""))
{
linkS.Conds.Add("WaitDetachExpenditure.year=" + year);
}
if (!month.Trim().Equals(""))
{
linkS.Conds.Add("WaitDetachExpenditure.month=" + month);
}
if (!pName.Trim().Equals(""))
{
linkS.Conds.Add("productName.name=" + "'" + pName + "'");
}
if (!pModel.Trim().Equals(""))
{
linkS.Conds.Add("productModel.model=" + "'" + pModel + "'");
}
linkS.Conds.Add("WaitDetachExpenditure.isDetach=0");
return(linkS.LeftLinkOpen().Tables[0]);
}
/*
* 方法名称： RunStuffInAccount
* 方法功能描述：打印材料入库台帐相关信息;
*
* 创建人：冯雪
* 创建时间：2009-04-21
*
* 修改人：
* 修改时间：
* 修改内容：
*
*/
public void RunStuffInAccount(StockInRClass stockclass, int mkId, int mnId)
{
LinkSelect ls = new LinkSelect();
ls.Viewfields.Add("stockNote.inputDate as snInputDate");
ls.Viewfields.Add("materialKind.sort as mkSort");
ls.Viewfields.Add("materialName.name as mnName");
ls.Viewfields.Add("materialModel.model as mmModel");
ls.Viewfields.Add("providerInfo.name as piName");
ls.Viewfields.Add("voitureInfo.no as viNo");
ls.Viewfields.Add("transportUnit.name as tuName");
ls.Viewfields.Add("stockNote.suttle as snSuttle");
ls.Viewfields.Add("indent.unitPrice as unitPrice");
ls.Viewfields.Add("(stockNote.suttle * indent.unitPrice) as totalPrice");
ls.Viewfields.Add("personnelInfo.name as snSurveyor ");
ls.TableNames.Add("stockNote");
ls.TableNames.Add("voitureInfo");
ls.LinkConds.Add("stockNote.viId = voitureInfo.id");
ls.TableNames.Add("personnelInfo");
ls.LinkConds.Add("stockNote.piId1 = personnelInfo.id");
ls.TableNames.Add("transportGoodsInformationCorresponding");
ls.LinkConds.Add("stockNote.tgicId = transportGoodsInformationCorresponding.id");
ls.TableNames.Add("transportContract");
ls.LinkConds.Add("transportGoodsInformationCorresponding.tcId = transportContract.id");
ls.TableNames.Add("transportUnit");
ls.LinkConds.Add("transportContract.tuId = transportUnit.id");
ls.TableNames.Add("indent");
ls.LinkConds.Add("stockNote.iId = indent.id");
ls.TableNames.Add("stockContract");
ls.LinkConds.Add("indent.scId = stockContract.id");
ls.TableNames.Add("providerInfo");
ls.LinkConds.Add("stockContract.piId = providerInfo.id");
ls.TableNames.Add("material");
ls.LinkConds.Add("indent.mId = material.id");
ls.TableNames.Add("materialModel");
ls.LinkConds.Add("material.mmId = materialModel.id");
ls.TableNames.Add("materialName");
ls.LinkConds.Add("material.mnId = materialName.id");
ls.TableNames.Add("materialKind");
ls.LinkConds.Add("materialName.mKId = materialKind.id");
if (!stockclass.Mid.Equals(0))
{
ls.Conds.Add("indent.mId = " + stockclass.Mid);
}
if (!stockclass.Providerid.Equals(0))
{
ls.Conds.Add("providerInfo.id = " + stockclass.Providerid);
}
if (!stockclass.Transportid.Equals(0))
{
ls.Conds.Add("transportUnit.id = " + stockclass.Transportid);
}
if (!stockclass.Voitureid.Equals(0))
{
ls.Conds.Add("voitureInfo.id = " + stockclass.Voitureid);
}
if ((stockclass.BeginTime != null) && (!stockclass.BeginTime.Equals("")))
{
ls.Conds.Add("stockNote.inputdate>=" + "'" + stockclass.BeginTime + "'");
}
if ((stockclass.EndTime != null) && (!stockclass.EndTime.Equals("")))
{
ls.Conds.Add("stockNote.inputdate<=" + "'" + stockclass.EndTime + "'");
}
if ((stockclass.State != null) && (!stockclass.State.Equals("")))
{
switch (stockclass.State)
{
case "已审核":
{
ls.Conds.Add("stockNote.assessor is not NULL");
break;
}
case "已审批":
{
ls.Conds.Add("stockNote.checkupMan is not NULL");
break;
}
case "未审核、审批":
{
ls.Conds.Add("stockNote.assessor is NULL");
ls.Conds.Add("stockNote.checkupMan is NULL");
break;
}
}
}
if ((mkId != 0) && (stockclass.Mid == 0))
{
ls.Conds.Add("materialKind.id = " + mkId);
}
if ((mnId != 0) && (stockclass.Mid == 0))
{
ls.Conds.Add("materialName.id = " + mnId);
}
DataSet ds = ls.LeftLinkOpen();
StuffInAccount oStuffInAccount = new StuffInAccount();
oStuffInAccount.Source = ds;
oStuffInAccount.Unit = name_value();
oStuffInAccount.Num = "";
oStuffInAccount.MakeTable = "";
oStuffInAccount.StuffCheck = "";
oStuffInAccount.Check = "";
oStuffInAccount.ShowReport();
}
/*
* 方法名称： RunStuffOutAccount
* 方法功能描述：打印产品出库台帐相关信息;
*
* 创建人：冯雪
* 创建时间：2009-04-21
*
* 修改人：
* 修改时间：
* 修改内容：
*
*/
public void RunProductOutAccount(StockOutRClass stockORClass, int pkId, int pnId)
{
LinkSelect ls = new LinkSelect();
ls.Viewfields.Add("consignmentNote.inputDate as 时间");
ls.Viewfields.Add("productKind.sort as 产品种类");
ls.Viewfields.Add("productName.name as 产品名称");
ls.Viewfields.Add("productModel.model as 产品规格");
ls.Viewfields.Add("clientInfo.name as 客户名称");
ls.Viewfields.Add("voitureInfo.no as 车牌号");
ls.Viewfields.Add("transportUnit.name as 运输单位");
ls.Viewfields.Add("consignmentNote.suttle as 净重");
ls.Viewfields.Add("invoice.unitPrice as 销售单价");
ls.Viewfields.Add("consignmentNote.suttle * invoice.unitPrice as 销售金额");
ls.Viewfields.Add("personnelInfo.name as 检斤员 ");
ls.TableNames.Add("consignmentNote");
ls.TableNames.Add("voitureInfo");
ls.LinkConds.Add("consignmentNote.viId = voitureInfo.id");
ls.TableNames.Add("personnelInfo");
ls.LinkConds.Add("consignmentNote.piId1 = personnelInfo.id");
ls.TableNames.Add("transportGoodsInformationCorresponding");
ls.LinkConds.Add("consignmentNote.tgicId = transportGoodsInformationCorresponding.id");
ls.TableNames.Add("transportContract");
ls.LinkConds.Add("transportGoodsInformationCorresponding.tcId = transportContract.id");
ls.TableNames.Add("transportUnit");
ls.LinkConds.Add("transportContract.tuId = transportUnit.id");
ls.TableNames.Add("invoice");
ls.LinkConds.Add("consignmentNote.iId = invoice.id");
ls.TableNames.Add("sellContract");
ls.LinkConds.Add("invoice.scId = sellContract.id");
ls.TableNames.Add("clientInfo");
ls.LinkConds.Add("sellContract.ciId = clientInfo.id");
ls.TableNames.Add("product");
ls.LinkConds.Add("invoice.pId = product.id");
ls.TableNames.Add("productModel");
ls.LinkConds.Add("product.pmId = productModel.id");
ls.TableNames.Add("productName");
ls.LinkConds.Add("product.pnId = productName.id");
ls.TableNames.Add("productKind");
ls.LinkConds.Add("productName.pkId = productKind.id");
if ((pkId != 0) && (stockORClass.Pid == 0))
{
ls.Conds.Add("productKind.id = " + pkId);
}
if ((pnId != 0) && (stockORClass.Pid == 0))
{
ls.Conds.Add("productName.id = " + pnId);
}
if (!stockORClass.Pid.Equals(0))
{
ls.Conds.Add("invoice.pId = " + stockORClass.Pid);
}
if (!stockORClass.Clientid.Equals(0))
{
ls.Conds.Add("clientInfo.id = " + stockORClass.Clientid);
}
if (!stockORClass.Transportid.Equals(0))
{
ls.Conds.Add("transportUnit.id = " + stockORClass.Transportid);
}
if (!stockORClass.Voitureid.Equals(0))
{
ls.Conds.Add("voitureInfo.id = " + stockORClass.Voitureid);
}
if ((stockORClass.BeginTime != null) && (!stockORClass.BeginTime.Equals("")))
{
ls.Conds.Add("consignmentNote.inputdate>=" + "'" + stockORClass.BeginTime + "'");
}
if ((stockORClass.EndTime != null) && (!stockORClass.EndTime.Equals("")))
{
ls.Conds.Add("consignmentNote.inputdate<=" + "'" + stockORClass.EndTime + "'");
}
if ((stockORClass.State != null) && (!stockORClass.State.Equals("")))
{
switch (stockORClass.State)
{
case "已审核":
{
ls.Conds.Add("consignmentNote.assessor is not NULL");
break;
}
case "已审批":
{
ls.Conds.Add("consignmentNote.checkupMan is not NULL");
break;
}
case "未审核、审批":
{
ls.Conds.Add("consignmentNote.assessor is NULL");
ls.Conds.Add("consignmentNote.checkupMan is NULL");
break;
}
}
}
DataSet ds = ls.LeftLinkOpen();
ProductOutAccount oProductOutAccount = new ProductOutAccount();
oProductOutAccount.Source = ds;
oProductOutAccount.Unit = name_value();
oProductOutAccount.Num = "";
oProductOutAccount.MakeTable = "";
oProductOutAccount.Check = "";
oProductOutAccount.Approve = "";
oProductOutAccount.ShowReport();
}
public void RunStockCheckDetail(string id)
{
//日期 date
//车牌号码 no
//供应商名称 supplierName
//运输单位名称 transportName
//材料种类 sort
//材料名称 materialName
//规格型号 model
//毛重 grossWeight
//皮重 tare
//净重 suttle
//检斤员 personnel
LinkSelect linkS = new LinkSelect();
linkS.TableNames.Add("stockMaterialNoteCorresponding");
linkS.TableNames.Add("stockMaterialSettlementDetail");
linkS.LinkConds.Add("stockMaterialNoteCorresponding.smsdId=stockMaterialSettlementDetail.id");
linkS.TableNames.Add("stockMaterialSettlement");
linkS.LinkConds.Add("stockMaterialSettlementDetail.smsId=stockMaterialSettlement.id");
linkS.TableNames.Add("stockNote");
linkS.LinkConds.Add("stockMaterialNoteCorresponding.snId=stockNote.id");
linkS.TableNames.Add("personnelInfo");
linkS.LinkConds.Add("stockNote.piId1=personnelInfo.id");
linkS.TableNames.Add("indent");
linkS.LinkConds.Add("stockNote.iId=indent.id");
linkS.TableNames.Add("material");
linkS.LinkConds.Add("indent.mid=material.id");
linkS.TableNames.Add("materialName");
linkS.LinkConds.Add("material.mnid=materialName.id");
linkS.TableNames.Add("materialModel");
linkS.LinkConds.Add("material.mmid=materialModel.id");
linkS.TableNames.Add("materialKind");
linkS.LinkConds.Add("materialName.mkId=materialKind.id");
linkS.TableNames.Add("stockContract");
linkS.LinkConds.Add("indent.scId=stockContract.id");
linkS.TableNames.Add("providerInfo");
linkS.LinkConds.Add("stockContract.piId=providerInfo.Id");
linkS.TableNames.Add("voitureInfo");
linkS.LinkConds.Add("stockNote.viId=voitureInfo.id");
linkS.TableNames.Add("transportUnit");
linkS.LinkConds.Add("voitureInfo.tuId=transportUnit.id");
linkS.TableNames.Add("transportGoodsInformationCorresponding");
linkS.LinkConds.Add("stockNote.tgicId=transportGoodsInformationCorresponding.id");
linkS.TableNames.Add("transportContract");
linkS.LinkConds.Add("transportGoodsInformationCorresponding.tcId=transportContract.id");
linkS.Viewfields.Add("stockMaterialNoteCorresponding.inputDate as date");
linkS.Viewfields.Add("voitureInfo.[no]");
linkS.Viewfields.Add("providerInfo.[name] as supplierName");
linkS.Viewfields.Add("transportUnit.[name] as transportName");
linkS.Viewfields.Add("materialKind.sort");
linkS.Viewfields.Add("materialModel.model");
linkS.Viewfields.Add("materialName.[name] as materialName");
linkS.Viewfields.Add("stockNote.grossWeight");
linkS.Viewfields.Add("stockNote.tare");
linkS.Viewfields.Add("stockNote.suttle");
linkS.Viewfields.Add("personnelInfo.[name] as personnel");
linkS.Conds.Add("stockMaterialSettlement.id=" + id);
DataSet ds = linkS.LeftLinkOpen();
StockCheckDetail oStockCheckDetail = new StockCheckDetail();
oStockCheckDetail.Source = ds;
oStockCheckDetail.CoName = "中百";
oStockCheckDetail.ShowReport();
}