private void BtnSearch_Click(object sender, EventArgs e)
{
string strSql = string.Format(@"SELECT I.FNumber [物料代码], I.FName [物料名称], I.FModel [物料规格]
--, S.FNumber [仓库代码]
, S.FName [仓库名称]
--, SP.FNumber [仓位代码]
, SP.FName [仓位名称], U.FName [单位], CONVERT(FLOAT,INV.FQty) [库存数量] FROM ICInventory AS INV
LEFT JOIN t_ICItem AS I ON I.FItemID = INV.FItemID
JOIN t_Stock AS S ON S.FItemID = INV.FStockID
JOIN t_StockPlace AS SP ON SP.FSPID = INV.FStockPlaceID
JOIN t_MeasureUnit U ON U.FItemID = I.FUnitID
WHERE S.FDeleted = 0
AND SP.FDeleted = 0
AND I.FNumber LIKE '{0}'
AND I.FName LIKE '{1}'
AND I.FModel LIKE '{2}'
AND SP.FName LIKE '{3}'
AND INV.FQty > {4}", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text
, chkInv.Checked ? 0 : -1);
DataTable dt = DBCONN.Fill(strSql);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
private void BtnSearch_Click(object sender, EventArgs e)
{
string strSql = string.Format(@"
SELECT P.FDate [采购日期], P.FCheckDate [采购审核日期] ,PE.FDATE [采购交货日期], P.FBILLNO [采购订单号], U.FName [采购员]
,PE.FEntryID [采购序号]
, I.FNumber [物料代码] ,I.FName [物料名称] ,I.FModel [物料规格]
,PE.FQty [采购数量] , PE.FCommitQty [收料数量] , PE.FStockQty [入库数量]
,PE.FQty - PE.FStockQty [未入库数量]
,PD.MIN_DATE [入库日期] , R.FDate [申请单日期], R.FCheckTime [申请单审核日期] , U2.FName [申请人]
,CASE WHEN DATEDIFF(DD,R.FCheckTime,P.FDate) < 0 THEN 0 ELSE DATEDIFF(DD,R.FCheckTime,P.FDate) END
[采购停留（天）]
,CASE WHEN DATEDIFF(DD,PE.FDate , ISNULL(PD.MIN_DATE , PE.FDate) ) < 0 THEN 0 ELSE DATEDIFF(DD,PE.FDate , ISNULL(PD.MIN_DATE , PE.FDate) ) END [延期到货（天）]
--, DATEDIFF(DD,PE.FDate , ISNULL(PD.MIN_DATE , PE.FDate) ) DIFF_DATE_SKT
, PJ.FNumber [项目代码] , PJ.FName [项目名称]
FROM POOrder P
JOIN POOrderEntry PE ON P.FInterID = PE.FInterID
JOIN t_Supplier S ON S.FITEMID = P.FSupplyID
JOIN T_User U ON U.FUserID = P.FBillerID
LEFT JOIN po_instock_date PD ON PD.FOrderInterID = P.FInterID AND PD.FOrderEntryID = PE.FEntryID
JOIN PORequest R ON R.FInterID = PE.FSourceInterId
JOIN t_User U2 ON U2.FUserID = R.FBillerID
JOIN t_ICItem I ON I.FItemID = PE.FItemID
JOIN t_Item_3003 PJ ON PJ.FItemID = PE.FEntrySelfP0272
WHERE 1=1
AND PJ.FName LIKE '{3}'
AND PJ.FNumber LIKE '{4}'
AND I.FNumber LIKE '{0}'
AND I.FName LIKE '{1}'
AND I.FModel LIKE '{2}'
ORDER BY PJ.FNumber", textBox1.Text, textBox2.Text, textBox3.Text
, textBox4.Text, textBox5.Text);
DataTable dt = DBCONN.Fill(strSql);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
private void BtnSearch_Click(object sender, EventArgs e)
{
//连接条件 -- 将'替换成''
string strFilter = "";
strFilter = strFilter + " AND I.FNumber " + "like '" + textBox1.Text.Replace("'", "''") + "'";
strFilter = strFilter + " AND I.FName " + "like '" + textBox2.Text.Replace("'", "''") + "'";
strFilter = strFilter + " AND I.FModel " + "like '" + textBox3.Text.Replace("'", "''") + "'";
string strSql = string.Format(@"SELECT I.FNumber [物料代码], I.FName [物料名称], I.FModel [物料规格], CONVERT(FLOAT,ISNULL(SUM(FQty),0)) [库存汇总]
FROM t_ICItem I
LEFT JOIN ICInventory INV ON INV.FItemID = I.FItemID
WHERE I.FDeleted = 0
AND I.FNumber <> '0002'
{0}
GROUP BY I.FNumber , I.FName , I.FModel ", strFilter);
DataTable dt = DBCONN.Fill(strSql);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
private void BtnSearch_Click(object sender, EventArgs e)
{
string strSql = string.Format(@"SELECT P1.FBillNo [采购订单号], CONVERT(VARCHAR(10),P1.FDate,121) [日期]
, P2.FEntryID [订单序号], I.FNumber [物料号], I.FName [物料名称]
, I.FModel [物料规格], CONVERT(float,P2.FQty) [采购数], CONVERT(float,P2.FCommitQty) [收料数]
,CONVERT(float,P2.FStockQty) [已入库数] , UN.FName [单位], PJ.FNumber [项目号], PJ.FName [项目名称]
FROM POOrder P1
JOIN POOrderEntry P2 ON P1.FInterID = P2.FInterID
LEFT JOIN t_Supplier S ON S.FItemID = P1.FSupplyID
LEFT JOIN t_ICItem I ON I.FItemID = P2.FItemID
LEFT JOIN t_MeasureUnit UN ON UN.FItemID = I.FUnitID
LEFT JOIN t_Item_3003 PJ ON PJ.FItemID = P2.FEntrySelfP0272
LEFT JOIN T_USER U ON U.FUSERID = P1.FBILLERID
WHERE 1=1
AND P1.FCancellation = 0
AND P1.FChangeMark = 0
AND P1.FStatus IN (1, 2)
AND P2.FMrpClosed = 0
AND P2.FQty > P2.FCommitQty
AND P1.FBillNo LIKE '{0}'
AND S.FName LIKE '{1}'
AND I.FNumber LIKE '{2}'
AND I.FName LIKE '{3}'
AND I.FModel LIKE '{4}'
AND PJ.FNumber LIKE '{5}'
AND PJ.FNAME LIKE '{6}'
ORDER BY P1.FDate DESC,P1.FBillNo DESC,FEntryID
", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text
, textBox6.Text, textBox7.Text);
DataTable dt = DBCONN.Fill(strSql);
dataGridView1.DataSource = dt;
dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
