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; }