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