private void frmQuery_Load(object sender, EventArgs e) { try { selectSQL = ""; dtPU_AppVouchDateH.Enabled = false; dtPU_AppVouchDateL.Enabled = false; dtPO_PomainDateL.Enabled = false; dtPO_PomainDateH.Enabled = false; dtPU_ArrivalVouchDateL.Enabled = false; dtPU_ArrivalVouchDateH.Enabled = false; DateTime dt = DateTime.Now; DateTime startMonth = dt.AddDays(1 - dt.Day); //本月月初 DateTime endMonth = startMonth.AddMonths(1).AddDays(-1); //本月月末// dtPO_PomainDateH.Value = endMonth; dtPO_PomainDateL.Value = startMonth; dtPU_AppVouchDateL.Value = startMonth; dtPU_AppVouchDateH.Value = endMonth; dtPU_ArrivalVouchDateL.Value = startMonth; dtPU_ArrivalVouchDateH.Value = endMonth; } catch (Exception ex) { frmMessege frmmsg = new frmMessege(ex.ToString(), "frmQuery_Load()"); frmmsg.ShowDialog(this); } }
public frmPUR442Main(string[] args) { try { InitializeComponent(); //37:442-到货单查询(按请购单分类) iLoginEx.Initialize(args, AuthID);//必须先初始化LoginEx SLbAccID.Text = iLoginEx.AccID(); SLbAccName.Text = iLoginEx.AccName(); SLbServer.Text = iLoginEx.DBServerHost(); SLbYear.Text = iLoginEx.iYear(); SLbUser.Text = iLoginEx.UserId() + "[" + iLoginEx.UserName() + "]"; } catch (Exception ex) { frmMessege frmmsg = new frmMessege(ex.ToString(), "frmPUR442Main()"); frmmsg.ShowDialog(this); } }
private void btnQurey_Click(object sender, EventArgs e) { try { if (!chkShowDedtail.Checked && !chkShowTotal.Checked) { selectSQL = ""; MessageBox.Show(this, "未选【显示明细】或【显示分类汇总】", "到货查询", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string cinvCodeChild = ""; string[] paraCinvCode = null; selectSQL = " select sumType,分类,到货单号,到货日期,采购单号,供应商,采购员,请购人,请购单号,物料编码,inventory.cinvname as '物料名称', inventory.cinvstd '规格型号',inventory.cinvdefine7 as '图号版次',到货数量,请购数量,采购数量,请购欠数,采购欠数,ComputationUnit.cComUnitName as '单位' from ( \r\n"; if (chkShowDedtail.Checked) { selectSQL += " select sumType=0,分类='', PU_ArrivalVouch.cCode as '到货单号',PU_ArrivalVouch.ddate as '到货日期',Po_Pomain.cpoid as '采购单号',vendor.cvenabbname as '供应商',person.cpersonname as '采购员', \r\n"; selectSQL += " isnull(PU_AppVouch.cMaker,'') as'请购人',isnull(PU_AppVouch.cCode,'空白') as '请购单号', \r\n"; selectSQL += " pu_arrivalvouchs.cinvcode as '物料编码',PU_ArrivalVouchs.iquantity as '到货数量',case when isnull(PU_AppVouch.cCloser,'')<>'' or isnull(PU_AppVouchs.cbcloser,'')<>'' then 0 else isnull(PU_AppVouchs.fQuantity,0) end as '请购数量', \r\n"; selectSQL += " case when isnull(Po_Pomain.cCloser,'')<>'' or isnull(Po_podetails.cbcloser,'')<>'' then 0 else isnull(Po_podetails.iQuantity,0) end as '采购数量',0 as '请购欠数',0 as '采购欠数' FROM PU_ArrivalVouchs (nolock) \r\n"; selectSQL += " left join PU_ArrivalVouch (nolock) on (PU_ArrivalVouch.id=PU_ArrivalVouchs.id) \r\n"; selectSQL += " LEFT JOIN Po_podetails (nolock) ON PU_ArrivalVouchs.iposid=Po_podetails.id and PU_ArrivalVouch.cbustype not in ( N'委外加工' ) \r\n"; selectSQL += " LEFT JOIN Po_Pomain (nolock) on Po_Podetails.poid=Po_Pomain.poid and PU_ArrivalVouch.cbustype not in ( N'委外加工' ) \r\n"; selectSQL += " left join vendor (nolock) on pu_arrivalvouch.cvencode = vendor.cvencode \r\n"; selectSQL += " left join person (nolock) on Po_Pomain.cpersoncode = person.cpersoncode \r\n"; selectSQL += " left join PU_AppVouchs (nolock) on PU_AppVouchs.AutoID=Po_podetails.iAppIds \r\n"; selectSQL += " left join PU_AppVouch (nolock) on PU_AppVouchs.id=PU_AppVouch.id \r\n"; selectSQL += " where isnull(PU_ArrivalVouch.cverifier,'')<>'' and isnull(PU_ArrivalVouch.ccloser,'')='' and isnull(PU_ArrivalVouchs.cbcloser,'')='' and \r\n"; selectSQL += " PU_ArrivalVouch.cbustype <>'委外加工' \r\n"; //请购单号 txtPU_AppVouchCode.Text = txtPU_AppVouchCode.Text.Trim(); txtPU_AppVouchCode.Text = txtPU_AppVouchCode.Text.Replace(";", ";"); if (txtPU_AppVouchCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPU_AppVouchCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_AppVouch.cCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_AppVouch.cCode ='" + txtPU_AppVouchCode.Text + "' \r\n"; } } //采购单号 txtPO_PomainCode.Text = txtPO_PomainCode.Text.Trim(); txtPO_PomainCode.Text = txtPO_PomainCode.Text.Replace(";", ";"); if (txtPO_PomainCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPO_PomainCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and Po_Pomain.cpoid in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and Po_Pomain.cpoid ='" + txtPO_PomainCode.Text + "' \r\n"; } } //到货单号 txtPU_ArrivalVouchsCode.Text = txtPU_ArrivalVouchsCode.Text.Trim(); txtPU_ArrivalVouchsCode.Text = txtPU_ArrivalVouchsCode.Text.Replace(";", ";"); if (txtPU_ArrivalVouchsCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPU_ArrivalVouchsCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_ArrivalVouch.cCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_ArrivalVouch.cCode ='" + txtPU_ArrivalVouchsCode.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 pu_arrivalvouchs.cinvcode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and pu_arrivalvouchs.cinvcode ='" + txtcInvCode.Text + "' \r\n"; } } //请购人 txtcMaker.Text = txtcMaker.Text.Trim(); txtcMaker.Text = txtcMaker.Text.Replace(";", ";"); if (txtcMaker.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtcMaker.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_AppVouch.cMaker in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_AppVouch.cMaker ='" + txtcMaker.Text + "' \r\n"; } } if (chkPU_AppVouch.Checked) { selectSQL += " and PU_AppVouch.ddate >= N'" + dtPU_AppVouchDateL.Value.ToString("yyyy-MM-dd") + "' And PU_AppVouch.ddate <= N'" + dtPU_AppVouchDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } if (chkPO_Pomain.Checked) { selectSQL += " and Po_Pomain.ddate >= N'" + dtPO_PomainDateL.Value.ToString("yyyy-MM-dd") + "' And Po_Pomain.ddate <= N'" + dtPO_PomainDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } if (chkPU_ArrivalVouch.Checked) { selectSQL += " and PU_ArrivalVouch.ddate >= N'" + dtPU_ArrivalVouchDateL.Value.ToString("yyyy-MM-dd") + "' And PU_ArrivalVouch.ddate <= N'" + dtPU_ArrivalVouchDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } } if (chkShowTotal.Checked) { if (chkShowDedtail.Checked) { selectSQL += " \r\n"; selectSQL += " union all \r\n"; selectSQL += " \r\n"; } selectSQL += " select sumType=1,分类='小计', '' as '到货单号',null as '到货日期','' as '采购单号','' as '供应商','' as '采购员', \r\n"; selectSQL += " isnull(PU_AppVouch.cMaker,'') as'请购人',isnull(PU_AppVouch.cCode,'空白')+'小计' as '请购单号', \r\n"; selectSQL += " pu_arrivalvouchs.cinvcode as '物料编码',sum(isnull(PU_ArrivalVouchs.iquantity,0)) as '到货数量',sum(case when isnull(PU_AppVouch.cCloser,'')<>'' or isnull(PU_AppVouchs.cbcloser,'')<>'' then 0 else isnull(PU_AppVouchs.fQuantity,0) end )as '请购数量', \r\n"; selectSQL += " sum(case when isnull(Po_Pomain.cCloser,'')<>'' or isnull(Po_podetails.cbcloser,'')<>'' then 0 else isnull(Po_podetails.iQuantity,0) end) as '采购数量', \r\n"; selectSQL += " sum(case when isnull(PU_AppVouch.cCloser,'')<>'' or isnull(PU_AppVouchs.cbcloser,'')<>'' then 0 else isnull(PU_AppVouchs.fQuantity,0) end )-sum(isnull(PU_ArrivalVouchs.iquantity,0)) as '请购欠数', \r\n"; selectSQL += " sum(case when isnull(Po_Pomain.cCloser,'')<>'' or isnull(Po_podetails.cbcloser,'')<>'' then 0 else isnull(Po_podetails.iQuantity,0) end)-sum(isnull(PU_ArrivalVouchs.iquantity,0)) as '采购欠数' FROM PU_ArrivalVouchs (nolock) \r\n"; selectSQL += " left join PU_ArrivalVouch (nolock) on (PU_ArrivalVouch.id=PU_ArrivalVouchs.id) \r\n"; selectSQL += " LEFT JOIN Po_podetails (nolock) ON PU_ArrivalVouchs.iposid=Po_podetails.id and PU_ArrivalVouch.cbustype not in ( N'委外加工' ) \r\n"; selectSQL += " LEFT JOIN Po_Pomain (nolock) on Po_Podetails.poid=Po_Pomain.poid and PU_ArrivalVouch.cbustype not in ( N'委外加工' ) \r\n"; selectSQL += " left join vendor (nolock) on pu_arrivalvouch.cvencode = vendor.cvencode \r\n"; selectSQL += " left join person (nolock) on Po_Pomain.cpersoncode = person.cpersoncode \r\n"; selectSQL += " left join PU_AppVouchs (nolock) on PU_AppVouchs.AutoID=Po_podetails.iAppIds \r\n"; selectSQL += " left join PU_AppVouch (nolock) on PU_AppVouchs.id=PU_AppVouch.id \r\n"; selectSQL += " where isnull(PU_ArrivalVouch.cverifier,'')<>'' and isnull(PU_ArrivalVouch.ccloser,'')='' and isnull(PU_ArrivalVouchs.cbcloser,'')='' and \r\n"; selectSQL += " PU_ArrivalVouch.cbustype <>'委外加工' \r\n"; //请购单号 txtPU_AppVouchCode.Text = txtPU_AppVouchCode.Text.Trim(); txtPU_AppVouchCode.Text = txtPU_AppVouchCode.Text.Replace(";", ";"); if (txtPU_AppVouchCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPU_AppVouchCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_AppVouch.cCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_AppVouch.cCode ='" + txtPU_AppVouchCode.Text + "' \r\n"; } } //采购单号 txtPO_PomainCode.Text = txtPO_PomainCode.Text.Trim(); txtPO_PomainCode.Text = txtPO_PomainCode.Text.Replace(";", ";"); if (txtPO_PomainCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPO_PomainCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and Po_Pomain.cpoid in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and Po_Pomain.cpoid ='" + txtPO_PomainCode.Text + "' \r\n"; } } //到货单号 txtPU_ArrivalVouchsCode.Text = txtPU_ArrivalVouchsCode.Text.Trim(); txtPU_ArrivalVouchsCode.Text = txtPU_ArrivalVouchsCode.Text.Replace(";", ";"); if (txtPU_ArrivalVouchsCode.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtPU_ArrivalVouchsCode.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_ArrivalVouch.cCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_ArrivalVouch.cCode ='" + txtPU_ArrivalVouchsCode.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 pu_arrivalvouchs.cinvcode in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and pu_arrivalvouchs.cinvcode ='" + txtcInvCode.Text + "' \r\n"; } } //请购人 txtcMaker.Text = txtcMaker.Text.Trim(); txtcMaker.Text = txtcMaker.Text.Replace(";", ";"); if (txtcMaker.Text.Length > 0) { cinvCodeChild = ""; paraCinvCode = txtcMaker.Text.Split(';'); if (paraCinvCode.Length > 0) { for (int i = 0; i < paraCinvCode.Length; i++) { cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',"; } selectSQL += " and PU_AppVouch.cMaker in (" + cinvCodeChild + "'\r\n'" + ") \r\n"; } else { selectSQL += " and PU_AppVouch.cMaker ='" + txtcMaker.Text + "' \r\n"; } } if (chkPU_AppVouch.Checked) { selectSQL += " and PU_AppVouch.ddate >= N'" + dtPU_AppVouchDateL.Value.ToString("yyyy-MM-dd") + "' And PU_AppVouch.ddate <= N'" + dtPU_AppVouchDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } if (chkPO_Pomain.Checked) { selectSQL += " and Po_Pomain.ddate >= N'" + dtPO_PomainDateL.Value.ToString("yyyy-MM-dd") + "' And Po_Pomain.ddate <= N'" + dtPO_PomainDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } if (chkPU_ArrivalVouch.Checked) { selectSQL += " and PU_ArrivalVouch.ddate >= N'" + dtPU_ArrivalVouchDateL.Value.ToString("yyyy-MM-dd") + "' And PU_ArrivalVouch.ddate <= N'" + dtPU_ArrivalVouchDateH.Value.ToString("yyyy-MM-dd") + "' \r\n"; } selectSQL += " group by isnull(PU_AppVouch.cCode,'空白')+'小计',isnull(PU_AppVouch.cMaker,''),pu_arrivalvouchs.cinvcode \r\n"; } selectSQL += " ) m \r\n"; selectSQL += " LEFT JOIN Inventory (nolock) ON m.物料编码=Inventory.cInvCode \r\n"; selectSQL += " left join ComputationUnit (nolock) on Inventory.cComUnitCode =ComputationUnit.cComunitCode \r\n"; selectSQL += " order by 物料编码,请购单号,sumType \r\n"; selectSQL += " \r\n"; this.Close(); } catch (Exception ex) { frmMessege frmmsg = new frmMessege(ex.ToString(), "btnQurey_Click()"); frmmsg.ShowDialog(this); } }