110,529
社区成员
发帖
与我相关
我的任务
分享
///执行的sql拼接
public DataTable Query()
{
string strCommand ="";
string strByGoods="";
string strByProduction = "";
string strGuiGe = "";
string strProductionType = "";
string strBOM = "";
switch (_BillType)
{ if (ProductionName != null)
{
if (ProductionName[0] == "模糊匹配")
{
strByProduction = "and HuoPinID in (select ID from tbHuoPin where HuoPinMingCheng like '%"+_ProductionName [1]+"%')";
}
else
{
strByProduction = "and HuoPinID in (select ID from tbHuoPin where HuoPinMingCheng = '" + _ProductionName[1] + "')";
}
}
if (ProductionType != null)
{
if ( ProductionType[0] == "模糊匹配")
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%')";
}
else
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "')";
}
}
if (BOM != null)
{
if (BOM[0]=="模糊匹配")
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao like '%"+_BOM[1] +"%')";
}
else
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao = '" + _BOM[1] + "')";
}
}
break ;
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe like '%" +_ProductionType[1] + "%')";
}
else
{
strProductionType = "and HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "')";
}
}
if (BOM != null)
{
if (BOM[0] == "模糊匹配")
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao like '%" + _BOM[1] + "%')";
}
else
{
strBOM = "and BOM in (select ID from tbWuLiaoQingDan where BanBenHao ='" + _BOM[1] + "')";
}
}
break ;
case "退料单":
{
strCommand = "select * from tbTuiLiaoDan where ID!=-1";
if (GoodsType != null)
{
if (GoodsType[0] == "模糊匹配")
{
strGuiGe = "and ID in (select FuDanID from tbTuiLiaoDetail where HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _GoodsType[1] + "%'))";
}
else if (GoodsType[0] == "精确匹配")
{
strGuiGe = "and ID in (select FuDanID from tbTuiLiaoDetail where HuoPinID in (select ID from tbHuoPin where GuiGe ='" +_GoodsType[1] + "'))";
}
}
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = " and ID in( select FuDanID from tbTuiLiaoDetail where ChanPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%'))";
}
else
{
strProductionType = "and ID in (select FuDanID from tbTuiLiaoDetail where ChanPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "'))";
}
}
} break;
if (ProductionType != null)
{
if (ProductionType[0] == "模糊匹配")
{
strProductionType = " and ID in( select FuDanID from tbShengChanJiHuaDetail where HuoPinID in (select ID from tbHuoPin where GuiGe like '%" + _ProductionType[1] + "%'))";
}
else
{
strProductionType = "and ID in (select FuDanID from tbShengChanJiHuaDetail where HuoPinID in (select ID from tbHuoPin where GuiGe = '" + _ProductionType[1] + "'))";
}
}
if (BOM != null)
{
if (BOM[0] == "模糊匹配")
{
//strBOM = "and BOM in (select ID from tbShengChanJiHuaDetail where BanBenHao like '%" + _BOM[1] + "%')";
strBOM = "and ID in (select FuDanID from tbShengChanJiHuaDetail where BOM in ( select ID from tbWuLiaoQingDan where BanBenHao like '%" + _BOM[1] + "%'))";
}
else
{
//strBOM = "and BOM in (select ID from tbShengChanJiHuaDetail where BanBenHao = " + _BOM[1] + "";
strBOM = "and ID in (select FuDanID from tbShengChanJiHuaDetail where BOM in ( select ID from tbWuLiaoQingDan where BanBenHao= '"+_BOM[1] + "'))";
}
}
} break;
}
strCommand += strByGoods + strByProduction+strBOM+strGuiGe+strProductionType ;
if (_BillCode != null)
{
if (_BillCode[0] == "模糊匹配")
{
strCommand += "and DanJuBianHao like '%"+_BillCode[1] +"%'";
}
else
{
strCommand += "and DanJuBianHao ='"+_BillCode[1] +"'";
}
}
if (_BillTime != null)
{
if (_BillTime[0] != null)
{
strCommand += " and KaiDanRiQi>='"+_BillTime [0]+"' ";
}
if (_BillTime[1] != null)
{
strCommand += "and KaiDanRiQi<='"+_BillTime [1]+"'";
}
}
DataTable dt = new DataTable();
UserObject.Conn.ExecuteDataTable(strCommand ,ref dt );
return dt;
}
///数据显示和条件输入界面
private void btn_Definite_Click(object sender, EventArgs e)
{
QueryProducingOdersObject QPOObj = new QueryProducingOdersObject();
QPOObj ._BillType = BillType ;
ShowLastCondition(lbCondition .Text );
ShowLastCondition(lbCondition .Text );
foreach (ListViewItem lvi in lvCondition.Items)
{
switch (lvi.Text)
{
case "单据编号":
{
if (cbbBillCode .Text != "")
{
QPOObj.BillCode = new string[2];
QPOObj.BillCode[0] = cbbBillCode.Text;
QPOObj.BillCode[1] = txtbBillCode.Text;
}
} break;
case "开单日期":
{
QPOObj.BillTime = new DateTime?[2];
QPOObj.BillTime[0] = null;
QPOObj.BillTime[1] = null;
if (chkbStartTime .Checked ==true )
{
QPOObj.BillTime[0] = Convert .ToDateTime (dtpStartTime.Value.Date .ToString ());
}
if (chkbOver.Checked == true)
{
QPOObj.BillTime[1] = Convert.ToDateTime(dtpOverTime.Value.Date.ToString("yyyy-MM-dd") + " 23:59:59");
}
} break;
case "制单人":
{
if (cbbCreateMan.Text != "")
{
QPOObj.CreateMan = Convert .ToInt64 (cbbCreateMan .SelectedValue );
}
else
{
QPOObj.CreateMan = null;
}
} break;
case "单据状态":
{
if (cbbBillState.Text != "")
{
QPOObj.BillState = cbbBillState.SelectedIndex ;
}
else
{
QPOObj.BillState = null;
}
} break;
case "货品名称":
{
if (cbbGoods.Text != "")
{
QPOObj.GoodsName = new string[2];
QPOObj.GoodsName[0] = cbbGoods.Text;
QPOObj.GoodsName[1] = txtbGoods.Text;
}
else
{
QPOObj.GoodsName = null;
}
} break;
case "货品规格":
{
if (cbx_GoodsType.Text != "")
{
QPOObj.GoodsType = new string[2];
QPOObj.GoodsType[0]=cbx_GoodsType.Text;
QPOObj.GoodsType[1]=txbGodsType.Text;
}
else
{
QPOObj.GoodsType=null;
}
}break;
case "产品名称":
{
if (cbbProduction . Text != "")
{
QPOObj.ProductionName = new string[2];
QPOObj.ProductionName [0] = cbbProduction .Text ;
QPOObj.ProductionName [1] = txtbProduction .Text ;
}
else
{
QPOObj.ProductionName = null;
}
} break;
case "产品规格":
{
if (cbxProductionType.Text != "")
{
QPOObj.ProductionType = new string[2];
QPOObj.ProductionType[0] = cbxProductionType.Text;
QPOObj.ProductionType[1] = tbxProductionType.Text;
}
else
{
QPOObj.ProductionType = null;
}
} break;
case "BOM版本号":
{
if (cbbBOM.Text != "")
{
QPOObj .BOM =new string [2];
QPOObj.BOM[0]=cbbBOM .Text ;
QPOObj.BOM[1]=txtbBOM .Text ;
}
else
{
QPOObj.BOM = null;
}
} break;
}
}
DataTable dt = QPOObj . Query();
// SpreadMethods.ClearSpreadContent(spreadResult);
SpreadMethods.ClearSpreadText(spreadResult );
if (spreadResult.MaxRows <= dt.Rows.Count)
{
spreadResult.MaxRows = dt.Rows.Count + 1;
}
DrawspreadResults(dt);
}
///各种属性的定义
private DateTime?[] _BillTime;
public DateTime?[] BillTime
{
get { return _BillTime ; }
set { _BillTime = value; }
}
private long? _CreateMan;
public long ? CreateMan
{
get {return _CreateMan; }
set { _CreateMan = value; }
}
private int ? _BillState;
public int ? BillState
{
get { return _BillState ; }
set { _BillState = value; }
}
private string[] _GoodsName;
public string[] GoodsName
{
get { return _GoodsName; }
set { _GoodsName = value; }
}
//货品规格
private string[] _GoodsType;
public string[] GoodsType
{
get { return _GoodsType; }
set { _GoodsType = value; }
}
//页面代码
private void btn_Definite_Click(object sender, EventArgs e)
{
if (lstConditionItem.SelectedItems.Count > 0)
{
ShowCondition(lstConditionItem.SelectedItems[0].Text);
}
objQueryBase = new QueryBaseObject();
foreach ( ListViewItem lvi in lsvCondition.Items )
{
switch (lvi.Text)
{
case "业务类型":
objQueryBase.StrBusinessType = strBusinessType;
_strBillType = strBusinessType[0];
break;
case "单据编号":
if (cbx_BillNo.Text != "任意" && cbx_BillNo.Text!="")
{
objQueryBase.BillNoCondition = new string[2];
objQueryBase.BillNoCondition[1] = txb_BillNo.Text;
objQueryBase.BillNoCondition[0] = cbx_BillNo.Text;
}
break;
case "开单日期":
if (ckb_CreatTimeBegin.Checked || ckb_CreatTimeEnd.Checked)
{
objQueryBase.CreatDatetime = new string[2];
if (ckb_CreatTimeBegin.Checked)
{
objQueryBase.CreatDatetime[0] = dtp_CreatBillBegin.Value.ToShortDateString();
}
if (ckb_CreatTimeEnd.Checked)
{
objQueryBase.CreatDatetime[1] = dtp_CreatBillEnd.Value.ToShortDateString();
}
}
break;
case "单据金额":
{
objQueryBase.BillCurrency = new string[3];
objQueryBase.BillCurrency[0] = cbx_BillCurrency.Text;
if (txb_BillCurrencyBegin.Text != "")
{
objQueryBase.BillCurrency[1] = txb_BillCurrencyBegin.Text;
}
else
{
objQueryBase.BillCurrency[1] = "0";
}
if (txb_CurrencyEnd.Text != "")
{
objQueryBase.BillCurrency[2] = txb_CurrencyEnd.Text;
}
else
{
objQueryBase.BillCurrency[2] = "0";
}
}
break;
case "客户":
{
objQueryBase.CustomerID = Convert .ToInt64 ( cbx_Customer.SelectedValue);
} break;
case "客户类型":
{
objQueryBase.CustomerTypeID = Convert.ToInt64(cbx_CustomerType.SelectedValue);
}
break;
case "客户编号":
{
if (cbx_CustomerCode.Text != "任意" && txb_CustomerCode.Text!="")
{
objQueryBase.CustomerCode = new string[2];
objQueryBase.CustomerCode [0] = cbx_CustomerCode.Text;
objQueryBase.CustomerCode [1] = txb_CustomerCode.Text;
}
}break ;
case "货品":
{
if (cbx_Goods.Text != "")
{
objQueryBase.GoodsID = GoodsID;
}
} break;
case "产品":
{
if (cbx_ChanPin.Text != "")
{
objQueryBase.ChanPinID = ChanPinID;
}
} break;
case "货品编码":
{
if (cbx_GoodsCodeCondition.Text != "任意" && txb_GoodsCode.Text!="")
{
objQueryBase.GoodsCode = new string[2];
objQueryBase.GoodsCode[0] = cbx_GoodsCodeCondition.Text;
objQueryBase.GoodsCode[1] = txb_GoodsCode .Text;
}
} break;
case "货品名称":
{
if (cbx_GoodsName.Text != "任意" && txb_GoodsName.Text != "")
{
objQueryBase.GoodsName = new string[2];
objQueryBase.GoodsName[0] = cbx_GoodsName.Text;
objQueryBase.GoodsName[1] = txb_GoodsName.Text;
}
}break ;
case "货品规格":
{
if (cbxGoodsType.Text != "任意" && tbxGoodsType.Text != "")
{
objQueryBase.GoodsType = new string[2];
objQueryBase.GoodsType[0] = cbxGoodsType.Text;
objQueryBase.GoodsType[1] = tbxGoodsType.Text;
}
} break;
case "发票号":
{
if (cbx_InvoiceNum.Text != "任意" && txb_InvoiceNum .Text !="")
{
objQueryBase.InvoiceNum = new string[2];
objQueryBase.InvoiceNum[0] = cbx_InvoiceNum.Text;
objQueryBase.InvoiceNum[1] = txb_InvoiceNum.Text;
}
} break;
case "合同编号":
{
if (cbx_ContractNo.Text != "任意" && txb_ContractNo.Text != "")
{
objQueryBase.ContractNo = new string[2];
objQueryBase.ContractNo[0] = cbx_ContractNo.Text;
objQueryBase.ContractNo[1] = txb_ContractNo.Text;
}
} break;
case "录入用户":
{
if (cbx_Creator.Text != "")
{
objQueryBase.Creator = CreatorID;
}
} break;
case "审核用户":
{
if (cbx_Auditor.Text != "")
{
objQueryBase.Auditor = AuditorID;
}
} break;
case "审核日期":
{
if (ckb_AuditorDateBegin.Checked || ckb_AuditorDateEnd.Checked)
{
objQueryBase.AuditorDate = new string [2];
if (ckb_AuditorDateBegin.Checked)
{
objQueryBase.AuditorDate[0] = dtp_AuditDateBegin.Value.ToShortDateString();
}
if (ckb_AuditorDateEnd.Checked)
{
objQueryBase.AuditorDate[1] = dtp_AuditDateEnd.Value.ToShortDateString();
}
}
} break;
case "单据状态":
{
objQueryBase .BillStatus =cbx_BillStatus .SelectedIndex -1;
} break;
case "仓库":
{
objQueryBase.DepotID = DepotID;
}
break;
case "调出仓库":
{
objQueryBase.OutDepotID = DiaoChuDepot;
} break;
}
}
if (DataLock != "")
{
objQueryBase.DataLock = DataLock;
}
DataTable dt = objQueryBase.Query();
SpreadMethods.ClearSpreadContent(spreadResults);
if (dt != null)
{
if (spreadResults.MaxRows <= dt.Rows.Count)
{
spreadResults.MaxRows = dt.Rows.Count;
}
DrawspreadResults(dt);
}
}