//private void btnfind_Click(object sender, EventArgs e) //{ // string sql = ""; // lblDVR.Text = "DVR"; // lblHD.Text = "硬盘"; // lblSp.Text = "配件"; // lblIPC.Text = "IPC"; // decimal psum = 0;//合计金额 // OleDbConnection conn = new OleDbConnection(iLoginEx.ConnString()); // conn.Open(); // OleDbCommand comm = new OleDbCommand(sql, conn); // if (cbDVR.Checked) // { // List<DVRHSI> dvrlst = new List<DVRHSI>(); // sql = "select distinct zt.Cinvcode,zt.COption1 from ( \r\n"; // sql += " select 销售订单号 as Csocode,存货编码 as cInvCode from tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " zv where zv.cinvccode= \r\n"; // sql += " (select cinvccode from InventoryClass where cinvcName='" + cInvCCode + "') and zv.ddate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "' \r\n"; // sql += " )a left join zhrs_t_zzcSO_SOAddSeriesInfo zt on a.Csocode=zt.Csocode where zt.COption1='DVR' and a.Csocode=zt.Csocode \r\n"; // comm.CommandText = sql; // OleDbDataReader dr = comm.ExecuteReader(); // while (dr.Read()) // { // DVRHSI dvr = new DVRHSI(); // dvr.Cinvcode = dr["Cinvcode"].ToString(); // dvr.COption1 = dr["COption1"].ToString(); // dvrlst.Add(dvr); // } // dr.Close(); // decimal dvrP=0 ; // decimal dvrN=0; // if (dvrlst.Count > 0) // { // foreach (DVRHSI d in dvrlst) // { // sql = "select * from (select Sum(Ciquantity)as Number,sum(SiSum) as SiSum,Cinvcode,COption1 from zhrs_t_zzcSO_SOAddSeriesInfo \r\n"; // sql += " group by Cinvcode,COption1) a where a.Cinvcode='" + d.Cinvcode + "' \r\n"; // comm.CommandText = sql; // OleDbDataReader drd = comm.ExecuteReader(); // if (drd.Read()) // { // dvrP += Convert.ToDecimal(drd["SiSum"]); // dvrN += Convert.ToDecimal(drd["Number"]); // } // drd.Close(); // } // lblDVR.Text = "DVR 数量:" + dvrN + " 金额:" + dvrP + ""; // psum += dvrP; // } // } // if (cbHD.Checked) // { // List<DVRHSI> HDlst = new List<DVRHSI>(); // sql = "select distinct zt.Cinvcode,zt.COption1 from ( \r\n"; // sql += " select 销售订单号 as Csocode,存货编码 as cInvCode from tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " zv where zv.cinvccode= \r\n"; // sql += " (select cinvccode from InventoryClass where cinvcName='" + cInvCCode + "') and zv.ddate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "' \r\n"; // sql += " )a left join zhrs_t_zzcSO_SOAddSeriesInfo zt on a.Csocode=zt.Csocode where zt.COption1='HD' and a.Csocode=zt.Csocode \r\n"; // comm.CommandText = sql; // OleDbDataReader drH = comm.ExecuteReader(); // while (drH.Read()) // { // DVRHSI Hd = new DVRHSI(); // Hd.Cinvcode = drH["Cinvcode"].ToString(); // Hd.COption1 = drH["COption1"].ToString(); // HDlst.Add(Hd); // } // drH.Close(); // decimal HP = 0;//金额 // decimal HN = 0;//数量 // if (HDlst.Count > 0) // { // foreach (DVRHSI h in HDlst) // { // sql = "select * from (select Sum(Ciquantity)as Number,sum(SiSum) as SiSum,Cinvcode,COption1 from zhrs_t_zzcSO_SOAddSeriesInfo \r\n"; // sql += " group by Cinvcode,COption1) a where a.Cinvcode='" + h.Cinvcode + "' \r\n"; // comm.CommandText = sql; // OleDbDataReader drhd = comm.ExecuteReader(); // if (drhd.Read()) // { // HP += Convert.ToDecimal(drhd["SiSum"]); // HN += Convert.ToDecimal(drhd["Number"]); // } // drhd.Close(); // } // lblHD.Text = "硬盘 数量:" + HN + " 金额:" + HP + ""; // psum += HP; // } // } // if (cbIPC.Checked) // { // List<DVRHSI> IPClst = new List<DVRHSI>(); // sql = "select distinct zt.Cinvcode,zt.COption1 from ( \r\n"; // sql += " select 销售订单号 as Csocode,存货编码 as cInvCode from tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " zv where zv.cinvccode= \r\n"; // sql += " (select cinvccode from InventoryClass where cinvcName='" + cInvCCode + "') and zv.ddate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "' \r\n"; // sql += " )a left join zhrs_t_zzcSO_SOAddSeriesInfo zt on a.Csocode=zt.Csocode where zt.COption1='IPC' and a.Csocode=zt.Csocode \r\n"; // comm.CommandText = sql; // OleDbDataReader dri = comm.ExecuteReader(); // while (dri.Read()) // { // DVRHSI ipc = new DVRHSI(); // ipc.Cinvcode = dri["Cinvcode"].ToString(); // ipc.COption1 = dri["COption1"].ToString(); // IPClst.Add(ipc); // } // dri.Close(); // decimal ipcP = 0;//金额 // decimal ipcN = 0;//数量 // if (IPClst.Count > 0) // { // foreach (DVRHSI i in IPClst) // { // sql = "select * from (select Sum(Ciquantity)as Number,sum(SiSum) as SiSum,Cinvcode,COption1 from zhrs_t_zzcSO_SOAddSeriesInfo \r\n"; // sql += " group by Cinvcode,COption1) a where a.Cinvcode='" + i.Cinvcode + "' \r\n"; // comm.CommandText = sql; // OleDbDataReader dripc = comm.ExecuteReader(); // if (dripc.Read()) // { // ipcP += Convert.ToDecimal(dripc["SiSum"]); // ipcN += Convert.ToDecimal(dripc["Number"]); // } // dripc.Close(); // } // lblIPC.Text = "IPC 数量:" + ipcN + " 金额:" + ipcP + ""; // psum += ipcP; // } // } // if (cbSp.Checked) // { // List<DVRHSI> SPlst = new List<DVRHSI>(); // sql = "select distinct zt.Cinvcode,zt.COption1 from ( \r\n"; // sql += " select 销售订单号 as Csocode,存货编码 as cInvCode from tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " zv where zv.cinvccode= \r\n"; // sql += " (select cinvccode from InventoryClass where cinvcName='" + cInvCCode + "') and zv.ddate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "' \r\n"; // sql += " )a left join zhrs_t_zzcSO_SOAddSeriesInfo zt on a.Csocode=zt.Csocode where zt.COption1='配件' and a.Csocode=zt.Csocode \r\n"; // comm.CommandText = sql; // OleDbDataReader drs = comm.ExecuteReader(); // while (drs.Read()) // { // DVRHSI sp = new DVRHSI(); // sp.Cinvcode = drs["Cinvcode"].ToString(); // sp.COption1 = drs["COption1"].ToString(); // SPlst.Add(sp); // } // drs.Close(); // decimal SP = 0;//金额 // decimal SN = 0;//数量 // if (SPlst.Count > 0) // { // foreach (DVRHSI s in SPlst) // { // sql = "select * from (select Sum(Ciquantity)as Number,sum(SiSum) as SiSum,Cinvcode,COption1 from zhrs_t_zzcSO_SOAddSeriesInfo \r\n"; // sql += " group by Cinvcode,COption1) a where a.Cinvcode='" + s.Cinvcode + "' \r\n"; // comm.CommandText = sql; // OleDbDataReader drsp = comm.ExecuteReader(); // if (drsp.Read()) // { // SP += Convert.ToDecimal(drsp["SiSum"]); // SN += Convert.ToDecimal(drsp["Number"]); // } // drsp.Close(); // } // lblSp.Text = "配件 数量:" + SN + " 金额:" + SP + ""; // psum += SP; // } // } // lblPSum.Text = psum.ToString(); //} private void frmSaleDVRHSI_Load(object sender, EventArgs e) { try { string sql = ""; OleDbConnection conn = new OleDbConnection(iLoginEx.ConnString()); conn.Open(); sql = "if Object_id('tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + "') is not null \r\n"; sql += " drop table tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " \r\n"; OleDbCommand comm = new OleDbCommand(sql, conn); comm.ExecuteNonQuery(); sql = " select * into tempdb..so321_zzcsale" + iLoginEx.GetMacAddress().Replace(":", "") + " from zhrs_v_saleTotal_2016"; comm.CommandText = sql; comm.ExecuteNonQuery(); conn.Close(); } catch (Exception ex) { MessageBox.Show("错误!" + ex.Message); } finally { GC.Collect(); } }
private void btnQurey_Click(object sender, EventArgs e) { iLoginEx.WriteUserProfileValue("APS21Query", "dtDateL", dtDateL.Value.ToString("yyyy-MM-dd")); iLoginEx.WriteUserProfileValue("APS21Query", "dtDateH", dtDateH.Value.ToString("yyyy-MM-dd")); iLoginEx.WriteUserProfileValue("APS21Query", "chkDate", chkDate.Checked ? "1" : "0"); iLoginEx.WriteUserProfileValue("APS21Query", "chkShowPreparedMO", chkShowPreparedMO.Checked ? "1" : "0"); iLoginEx.WriteUserProfileValue("APS21Query", "chkShowFinishedMO", chkShowFinishedMO.Checked ? "1" : "0"); iLoginEx.WriteUserProfileValue("APS21Query", "chkNoShowColor", chkNoShowColor.Checked ? "1" : "0"); iLoginEx.WriteUserProfileValue("APS21Query", "chkSkipFinish", chkSkipFinish.Checked ? "1" : "0"); iLoginEx.WriteUserProfileValue("APS21Query", "txtcPersonCode", txtcPersonCode.Text); iLoginEx.WriteUserProfileValue("APS21Query", "txtcInvStd", txtcInvStd.Text); iLoginEx.WriteUserProfileValue("APS21Query", "txtcInvCode", txtcInvCode.Text); iLoginEx.WriteUserProfileValue("APS21Query", "txtMoCode", txtMoCode.Text); iLoginEx.WriteUserProfileValue("APS21Query", "txtcSOCode", txtcSOCode.Text); iLoginEx.WriteUserProfileValue("APS21Query", "chkALLMaking", chkALLMaking.Checked ? "1" : "0"); try { OleDbConnection myConn = new OleDbConnection(iLoginEx.ConnString()); if (myConn.State == System.Data.ConnectionState.Open) { myConn.Close(); } myConn.Open(); OleDbCommand myCommand = new OleDbCommand("", myConn); string cinvCodeChild = ""; string[] paraCinvCode = null; selectSQL = " if exists (select 1 from tempdb.dbo.sysobjects (nolock) where upper(name) = upper('APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "') and type='U') \r\n"; selectSQL += " drop table tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + " ; \r\n"; myCommand.CommandText = selectSQL; myCommand.ExecuteNonQuery(); selectSQL = " create table tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "( \r\n"; selectSQL += " MoCode nvarchar(30) null,MoDId int null,SortSeq int null ) \r\n"; selectSQL += " \r\n"; selectSQL += " \r\n"; myCommand.CommandText = selectSQL; myCommand.ExecuteNonQuery(); selectSQL = "select UID=" + iLoginEx.UID().ToString() + ",isnull(ud.ViewSort,0) as 'ViewSort',a.MoClass,d.cDepName as '部门',ud.Priority as '优先级',c.MoCode as'制造单号',a.sortseq as'行号M',a.OrderCode as'销售订单',person.cpersonname as '业务员', \r\n"; selectSQL += " a.OrderSeq as'行号S',a.InvCode as '产品编码',i.cInvName as '产品名称',i.cInvStd as '规格型号',convert(bit,case when isnull(a.Status,0)=3 then 1 else 0 end) as'投产', \r\n"; selectSQL += " b.StartDate as'计划生产日期',isnull(a.qty,0) as '排产数量',isnull(a.QualifiedInQty,0) as '完工数量', \r\n"; selectSQL += " convert(bit,case when len(isnull(a.CloseUser,''))>0 or Status=4 then 1 else 0 end) as'结案',soa.cDefine31 as 'LOGO' ,soa.cDefine33 as '软件信息',a.Define29 as '备注',调库存=isnull(ud.ReProduce,0),a.MoId,a.ModId \r\n"; selectSQL += " from mom_orderdetail a (nolock) \r\n"; selectSQL += " left join mom_morder b (nolock)on a.MoId=b.MoId and a.ModId=b.ModId \r\n"; selectSQL += " left join mom_order c (nolock) on a.MoId=c.MoId \r\n"; selectSQL += " left join Department d (nolock) on d.cDepCode=a.MDeptCode \r\n"; selectSQL += " left join SO_SODetails soa (nolock) on a.OrderDId =soa.iSOsID \r\n"; selectSQL += " left join SO_SOMain sob (nolock) on soa.ID =sob.ID \r\n"; selectSQL += " left join person (nolock) on sob.cPersonCode= person.cpersoncode \r\n"; selectSQL += " left join Inventory i (nolock) on a.InvCode=i.cInvCode \r\n"; selectSQL += " left join zhrs_t_mom_orderdetail_userDefine ud (nolock) on ud.MoID=a.MoID and ud.MoDId=a.MoDId \r\n"; selectSQL += " where 1=1 \r\n"; if (!(chkShowFinishedMO.Checked && chkShowPreparedMO.Checked)) { if (chkShowFinishedMO.Checked) { selectSQL += " and isnull(a.OrderCode,'')<>'' "; } if (chkShowPreparedMO.Checked) { selectSQL += " and isnull(a.OrderCode,'')='' "; } } if (chkALLMaking.Checked) { selectSQL += " and isnull(a.CloseUser,'')='' and (isnull(a.qty,0)-isnull(a.QualifiedInQty,0))>=0 \r\n"; } else { if (chkSkipFinish.Checked) { selectSQL += " and isnull(a.CloseUser,'')='' and (isnull(a.qty,0)-isnull(a.QualifiedInQty,0))>0 \r\n"; } //销售订单号 txtcSOCode.Text = txtcSOCode.Text.Trim(); txtcSOCode.Text = txtcSOCode.Text.Replace(";", ";"); if (txtcSOCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtcSOCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and a.OrderCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and a.OrderCode ='" + txtcSOCode.Text + "' \r\n"; } } //制造单号 string[] paraSortSeq = null; txtMoCode.Text = txtMoCode.Text.Trim(); txtMoCode.Text = txtMoCode.Text.Replace(";", ";"); txtMoCode.Text = txtMoCode.Text.Replace(",", ","); if (txtMoCode.Text.Length > 0) { paraCinvCode = txtMoCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { if (paraCinvCode[i].Length > 0) { paraSortSeq = paraCinvCode[i].Split(','); if (paraSortSeq.Length > 1) { for (int k = 1; k < paraSortSeq.Length; k++) { if (paraSortSeq[k].Length > 0) { myCommand.CommandText = " insert into tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "(MoCode,SortSeq)values('" + paraSortSeq[0] + "'," + paraSortSeq[k] + ") \r\n"; myCommand.ExecuteNonQuery(); } } } else { myCommand.CommandText = " insert into tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "(MoCode)values('" + paraCinvCode[i].ToString() + "') \r\n"; myCommand.ExecuteNonQuery(); } } } selectSQL += " and exists (select 1 from tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + " q where q.MoCode=c.MoCode and (q.SortSeq=a.SortSeq or q.SortSeq is null)) \r\n"; } else { selectSQL += " and c.MoCode ='" + txtMoCode.Text + "' \r\n"; } } //物料编码 txtcInvCode.Text = txtcInvCode.Text.Trim(); txtcInvCode.Text = txtcInvCode.Text.Replace(";", ";"); if (txtcInvCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtcInvCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and a.InvCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and a.InvCode ='" + txtcInvCode.Text + "' \r\n"; } } //规格型号 txtcInvStd.Text = txtcInvStd.Text.Trim(); txtcInvStd.Text = txtcInvStd.Text.Replace(" ", ";"); txtcInvStd.Text = txtcInvStd.Text.Replace("\t", ";"); txtcInvStd.Text = txtcInvStd.Text.Replace("\r", ";"); txtcInvStd.Text = txtcInvStd.Text.Replace("\n", ";"); txtcInvStd.Text = txtcInvStd.Text.Replace(";", ";"); while (txtcInvStd.Text.IndexOf(";;") > -1) { txtcInvStd.Text = txtcInvStd.Text.Replace(";;", ";"); } if (txtcInvStd.Text.Length > 0) { string mySelectQuery = "select cInvCode from Inventory (nolock) where "; cinvCodeChild = ""; paraCinvCode = txtcInvStd.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += " cinvstd like '%" + paraCinvCode[i].ToString() + "%' or "; } cinvCodeChild += iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r"; mySelectQuery += cinvCodeChild.Replace("or " + iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r", ""); } else { mySelectQuery += " cinvstd like '%" + txtcInvStd.Text + "%' "; } cinvCodeChild = ""; myCommand.CommandText = mySelectQuery; OleDbDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { cinvCodeChild += "'" + Convert.ToString(myReader["cInvCode"]) + "',"; } myReader.Close(); myReader.Dispose(); cinvCodeChild += iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r"; selectSQL += " and a.InvCode in (" + cinvCodeChild.Replace("," + iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r", "") + ") \r\n"; } if (chkDate.Checked) { selectSQL += " and b.StartDate >= N'" + dtDateL.Value.ToString("yyyy-MM-dd") + "' And b.StartDate<= N'" + dtDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } } this.Close(); } catch (Exception ex) { frmMessege frmmsg = new frmMessege(ex.ToString(), "btnQurey_Click()"); frmmsg.ShowDialog(this); } }
private void btnOK_Click(object sender, EventArgs e) { //if (chkOutMonths.Checked && chkOutMonths2.Checked) //{ // txtOutMonths.Text = txtOutMonths2.Text; //} if (chkOutMonths2.Checked) { mOutMonth = Convert.ToInt32(txtOutMonths2.Text); } else { mOutMonth = Convert.ToInt32(txtOutMonths.Text); } mWhCode = txtWhCode.Text.Trim(); string RdRecords_out = "#zhrs_t_ST32RdRecords_out" + iLoginEx.GetMacAddress().Replace(":", ""); //材料出库 string RdRecords_havedPUR = "#zhrs_t_ST32RdRecords_havedPUR" + iLoginEx.GetMacAddress().Replace(":", ""); //有采购 string RdRecords_NoPUR = "#zhrs_t_ST32RdRdRecords_NoPUR" + iLoginEx.GetMacAddress().Replace(":", ""); //无采购 selectSQL = " if exists (select 1 from tempdb.dbo.sysobjects where id = object_id(N'tempdb.." + RdRecords_out + "') and type='U') \r\n"; selectSQL += " drop table " + RdRecords_out + " ; \r\n"; selectSQL += " \r\n"; selectSQL += " if exists (select 1 from tempdb.dbo.sysobjects where id = object_id(N'tempdb.." + RdRecords_havedPUR + "') and type='U') \r\n"; selectSQL += " drop table " + RdRecords_havedPUR + " ; \r\n"; selectSQL += " \r\n"; selectSQL += " if exists (select 1 from tempdb.dbo.sysobjects where id = object_id(N'tempdb.." + RdRecords_NoPUR + "') and type='U') \r\n"; selectSQL += " drop table " + RdRecords_NoPUR + " ; \r\n"; selectSQL += " \r\n"; selectSQL += " \r\n"; selectSQL += "select a.cinvcode,moQty=0,Now_PurArrQty=0,Now_PurQty=0,stQty=0,useQty=0, a.iquantity as 'OutQty' into " + RdRecords_out + " from RdRecords a (nolock) left join RdRecord b (nolock)on a.id=b.id where b.cVouchType='11' and b.ddate between left(convert(varchar,dateadd(mm,-"; if (chkOutMonths.Checked) { selectSQL += txtOutMonths.Text; } else if (chkOutMonths2.Checked) { selectSQL += txtOutMonths2.Text; } selectSQL += ",getdate()),20),10) and left(convert(varchar,getdate(),20),10) ; \r\n"; selectSQL += " \r\n"; selectSQL += " \r\n"; selectSQL += " select a.cinvcode into " + RdRecords_NoPUR + " from RdRecords a (nolock) left join RdRecord b (nolock)on a.id=b.id where b.cVouchType='01' and b.ddate between left(convert(varchar,dateadd(mm,-" + txtInMonths.Text + ",getdate()),20),10) and left(convert(varchar,getdate(),20),10) group by a.cinvcode ;\r\n"; selectSQL += " \r\n"; selectSQL += " select a.cinvcode into " + RdRecords_havedPUR + " from RdRecords a (nolock) left join RdRecord b (nolock)on a.id=b.id where b.cVouchType='01' and b.ddate between left(convert(varchar,dateadd(mm,-" + txtInMonths2.Text + ",getdate()),20),10) and left(convert(varchar,getdate(),20),10) group by a.cinvcode ; \r\n"; selectSQL += " \r\n"; selectSQL += " \r\n"; selectSQL += " CREATE INDEX " + RdRecords_NoPUR + "_idx ON " + RdRecords_NoPUR + " \r\n"; selectSQL += " ( \r\n"; selectSQL += " cinvcode \r\n"; selectSQL += " ) ; \r\n"; selectSQL += " CREATE INDEX " + RdRecords_havedPUR + "_idx ON " + RdRecords_havedPUR + " \r\n"; selectSQL += " ( \r\n"; selectSQL += " cinvcode \r\n"; selectSQL += " ) ; \r\n"; selectSQL += " CREATE INDEX " + RdRecords_out + "_idx ON " + RdRecords_out + " \r\n"; selectSQL += " ( \r\n"; selectSQL += " cinvcode \r\n"; selectSQL += " ) ; \r\n"; selectSQL2 = " \r\n"; selectSQL2 += " select out.cinvcode as '物料编码',i.cInvName as'物料名称',i.cInvStd as '规格型号',out.stQty as'库存数量', isnull(prc.unitcost*prc.exchangerate,0)*out.stQty as '库存金额' ,\r\n"; selectSQL2 += " out.OutQty as '出库数量',out.OutQty*isnull(prc.unitcost*prc.exchangerate,0) as '出库金额' \r\n"; if (chkComprehensiveStock.Checked) { selectSQL2 += ",out.useQty as '已分配量',out.Now_PurQty as '采购在途',out.Now_PurArrQty as '到货在检', out.moQty as '在制' \r\n"; } selectSQL2 += " from ( \r\n"; if (chkComprehensiveStock.Checked) { selectSQL2 += " select cinvcode, sum(isnull(stQty,0)) as 'stQty',sum(isnull(OutQty,0)) as 'OutQty',sum(useQty) as 'useQty',sum(Now_PurQty) as 'Now_PurQty',sum(Now_PurArrQty) as 'Now_PurArrQty',sum(moQty) as 'moQty' from \r\n"; } else { selectSQL2 += " select cinvcode, sum(isnull(stQty,0)) as 'stQty',sum(isnull(OutQty,0)) as 'OutQty' from \r\n"; } selectSQL2 += " ( \r\n"; if (chkComprehensiveStock.Checked) { selectSQL2 += " select vw.cinvcode,vw.moQty,vw.Now_PurArrQty,vw.Now_PurQty,vw.CurSotckQty as 'stQty',vw.useQty,OutQty=0 from \r\n"; selectSQL2 += wComprehensiveStock.ComprehensiveStockInfo(iLoginEx, 0, "", "", "", iLoginEx.pubDB_UF(), mWhCode, "") + " \r\n"; selectSQL2 += " vw \r\n"; } else { selectSQL2 += " select cinvcode, iquantity as 'stQty',0 as 'OutQty' from CurrentStock a (nolock) where a.iquantity>0 and exists(select 1 from Warehouse w (nolock) where a.cWhCode=w.cWhCode and w.bMRP=1) \r\n"; mWhCode = mWhCode.Replace("\r", ""); mWhCode = mWhCode.Replace("\n", ""); mWhCode = mWhCode.Replace(";", ";"); if (mWhCode.Length > 0) { string mWhCodeChild = ""; string[] paramWhCode = mWhCode.Split(';'); if (paramWhCode.Length > 0) { for (int i = 0; i < paramWhCode.Length; i++) { mWhCodeChild += "'" + paramWhCode[i].ToString() + "',"; } selectSQL2 += " and a.cWhCode in (" + mWhCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL2 += " and a.cWhCode ='" + mWhCode + "' \r\n"; } } } selectSQL2 += " union all \r\n"; if (chkComprehensiveStock.Checked) { selectSQL2 += " select a.cinvcode,moQty=0,Now_PurArrQty=0,Now_PurQty=0,stQty=0,useQty=0, a.OutQty "; } else { selectSQL2 += " select a.cinvcode,stQty=0, a.OutQty "; } selectSQL2 += " from " + RdRecords_out + " a ) m group by m.cinvcode \r\n"; selectSQL2 += " ) out \r\n"; selectSQL2 += " left join Inventory i (nolock) on i.cInvCode=out.cInvCode \r\n"; selectSQL2 += " left join zhrs_t_LastPoPrice prc(nolock) on prc.cInvCode=out.cInvCode where prc.ponotype=1 and out.stQty>0 \r\n"; if (chkOutMonths2.Checked) { selectSQL2 += " and out.OutQty=0 \r\n"; } else if (chkOutMonths.Checked) { selectSQL2 += " and out.OutQty<(out.stQty*" + txtPercent.Text + "/100) \r\n"; } if (!(chkInMonths.Checked && chkInMonths2.Checked))//二选1,当两个都选的时候,则表示,根本不考虑有没有采购入库这回事 { if (chkInMonths.Checked) { //n月内无采购 selectSQL2 += " and not exists \r\n"; selectSQL2 += " (select 1 from " + RdRecords_NoPUR + " pur where out.cinvcode =pur.cinvcode ) \r\n"; } if (chkInMonths2.Checked) { //n月内有采购 selectSQL2 += " and exists \r\n"; selectSQL2 += " (select 1 from " + RdRecords_havedPUR + " pur where out.cinvcode =pur.cinvcode ) \r\n"; } } selectSQL2 += " order by isnull(prc.unitcost*prc.exchangerate,0)*out.stQty desc \r\n"; this.Close(); }