private void WriteUserProfileValue()
{
iLoginEx.WriteUserProfileValue("Query", "InMonths", txtInMonths.Text);
iLoginEx.WriteUserProfileValue("Query", "OutMonths", txtOutMonths.Text);
iLoginEx.WriteUserProfileValue("Query", "Percent", txtPercent.Text);
iLoginEx.WriteUserProfileValue("Query", "chkInMonths", chkInMonths.Checked ? "1" : "0");
iLoginEx.WriteUserProfileValue("Query", "chkOutMonths", chkOutMonths.Checked ? "1" : "0");
iLoginEx.WriteUserProfileValue("Query", "chkInMonths2", chkInMonths2.Checked ? "1" : "0");
iLoginEx.WriteUserProfileValue("Query", "txtInMonths2", txtInMonths2.Text);
iLoginEx.WriteUserProfileValue("Query", "chkOutMonths2", chkOutMonths2.Checked ? "1" : "0");
iLoginEx.WriteUserProfileValue("Query", "txtOutMonths2", txtOutMonths2.Text);
iLoginEx.WriteUserProfileValue("Query", "chkComprehensiveStock", chkComprehensiveStock.Checked ? "1" : "0");
}
private void btnQuery_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.OK;
coscode = txtQuery.Text;
iLoginEx.WriteUserProfileValue("SO311_frmQuery", "txtQuery", txtQuery.Text);
this.Close();
}
private void btndate_Click(object sender, EventArgs e)
{
iLoginEx.WriteUserProfileValue("SO321", "dtpbegind", dtpbegin.Value.ToString());
iLoginEx.WriteUserProfileValue("SO321", "dtpendd", dtpend.Value.ToString());
iLoginEx.WriteUserProfileValue("SO321", "cbDetail", cbDetail.Checked == true ? "1" : "0");
if (cbDetail.Checked)
{
string sql = "";
switch (zz_Index)
{
case 10: //业务员
sql = "and c.cPersonName ='" + cPersonName + "' and cy.addate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "'";
break;
case 1: //客户
sql = "and b.ccusname ='" + cPersonName + "' and cy.addate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "'";
break;
case 9: //成品分类
if (cPersonName == "配件")
{
sql = "and cy.cinvccode ='' and cy.addate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "'";
}
else if (cPersonName == "硬盘")
{
sql = "and cy.cinvccode ='H' and cy.addate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "'";
}
else
{
sql = "and cls.cInvCName ='" + cPersonName + "' and cy.addate between '" + dtpbegin.Text + "' and '" + dtpend.Text + "'";
}
break;
}
dgvLoadInfo(sql);
}
else
{
frmDetailed_Load(null, null);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
try
{
iLoginEx.WriteUserProfileValue("StockQuery", "cInvCCode", txtcInvCCode.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "cWhCode", txtcWhCode.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "cInvCode", txtcInvCode.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "QtyL", txtQtyL.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "QtyH", txtQtyH.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "PriceL", txtPriceL.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "PriceH", txtPriceH.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "AmtL", txtAmtL.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "AmtH", txtAmtH.Text);
iLoginEx.WriteUserProfileValue("StockQuery", "QureyType", cmbQureyType.SelectedIndex.ToString());
iLoginEx.WriteUserProfileValue("StockQuery", "InvABC", cmbABC.SelectedIndex.ToString());
IsQuery = true;
this.Close();
}
catch (Exception ex)
{
frmMessege frmmsg = new frmMessege(ex.ToString(), "btnOK_Click()");
frmmsg.ShowDialog(this);
}
}
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);
}
}
/// <summary>
/// 查询事件方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnQuery_Click(object sender, EventArgs e)
{
iLoginEx.WriteUserProfileValue("SO321", "txtQuery_DVRHSI", txtQuery_DVRHSI.Text);
DgvSaleInfo();
}
private void btnOK_Click(object sender, EventArgs e)
{
this.Text = " 正在加载报表，请稍后.....";
iLoginEx.WriteUserProfileValue("SO311", "cboReport", cboReport.Text);
string selectSQL = "select sq.cbustype,sq.cstname,sq.csocode,sq.ddate ,sq.ccuscode,sq.ccusdefine6,sq.cexch_name,convert(varchar(100),convert(money,sq.iexchrate),2) as iexchrate,sq.iTaxRate,sq.cdepname,sq.cpersonname,sq.ccusabbname,sq.cpayname, \r\n";
selectSQL += " sq.cmaker,sq.cverifier,sq.cmemo,sq.ccusphone,sq.ccusaddress,sq.ccusdefine8,sq.ccusdefine9,sq.cscname as csccode, \r\n";
selectSQL += " sqs.cInvCode,sqs.cInvName,sqs.cInvStd,sqs.cinvm_unit as cinvmunit,convert(varchar(100),convert(money,sqs.iquantity),1) as iquantity, convert(varchar(100),convert(money,sqs.itaxunitprice),2) as itaxunitprice , convert(varchar(100),sqs.isum,1) as isum,sqs.isosid \r\n";
selectSQL += " ,sqs.cinvdefine6 as SQ_cinvdefine6,sqs.cinvdefine9,sqs.cdefine33,sqs.cdefine31,sqs.cdefine22,sqs.cdefine23,sqs.irowno,sqs.cmemo as SQ_cmemo,convert(varchar, sqs.dpredate,111) as 'dpredate' ,sqs.cinvdefine4,sqs.cfree4,sqs.cinvdefine7 \r\n";
selectSQL += " from SaleOrderQ sq inner join SaleOrderSQ sqs on sq.id=sqs.id where sq.id=" + SO_SOMain_Id + " order by irowno \r\n";
//string selectSQL = "select sq.cbustype,sq.cstname,sq.csocode,sq.ddate ,sq.ccuscode,sq.ccusdefine6,sq.cexch_name,sq.iexchrate,sq.iTaxRate,sq.cdepname, \r\n";
//selectSQL += " sq.cpersonname,sq.ccusabbname,sq.cpayname,sq.cmaker,sq.cverifier,sq.cmemo,sq.ccusphone,sq.ccusaddress,sq.ccusdefine8,sq.ccusdefine9,sq.cscname, \r\n";
//selectSQL += " \r\n";
//selectSQL += " sqs.cInvCode,sqs.cInvName,sqs.cInvStd,sqs.cinvm_unit,sqs.iquantity,sqs.itaxunitprice,sqs.isum,sqs.isosid,sqs.cinvdefine6 as SQ_cinvdefine6,sqs.cinvdefine9, \r\n";
//selectSQL += " sqs.cdefine33,sqs.cdefine31,sqs.cdefine22,sqs.cdefine23,sqs.irowno,sqs.cmemo as SQ_cmemo,sqs.dpredate,sqs.cinvdefine4,sqs.cfree4,sqs.cinvdefine7,sqs.cinvdefine8 \r\n";
//selectSQL += " from SaleOrderQ sq inner join SaleOrderSQ sqs on sq.id=sqs.id where sq.id=" + SO_SOMain_Id + " order by irowno \r\n";
//selectSQL += " \r\n";
try
{
OLEDBHelper.iLoginEx = iLoginEx;
string sql_as = "";
sql_as = "if object_id('tempdb..zhrs_t_CB_SG_Info') is not null \r\n";
sql_as += " drop table tempdb..zhrs_t_CB_SG_Info \r\n";
OLEDBHelper.ExecuteNonQuery(sql_as, CommandType.Text);
sql_as = "create table tempdb..zhrs_t_CB_SG_Info \r\n";
sql_as += " ( \r\n";
sql_as += " iRow int, \r\n";
sql_as += " Csocode varchar(50), \r\n";
sql_as += " Cinvcodes varchar(50), \r\n";
sql_as += " Cinvcode varchar(50), \r\n";
sql_as += " CinvName varchar(225), \r\n";
sql_as += " Cinvstd varchar(225), \r\n";
sql_as += " CcomunitName varchar(10), \r\n";
sql_as += " BaseQtyND decimal(28,4), \r\n";
sql_as += " Ciquantity decimal(28,4), \r\n";
sql_as += " SiQuotedPrice decimal(28,4), \r\n";
sql_as += " SiSum decimal(28,4) \r\n";
sql_as += " ) \r\n";
OLEDBHelper.ExecuteNonQuery(sql_as, CommandType.Text);
}
catch (Exception ex)
{
MessageBox.Show("发生错误！" + ex.Message);
}
string selectSQL_CB = "select zt.iRow, zt.Csocode,zt.Cinvcodes,zt.Cinvcode,zt.CinvName,zt.Cinvstd,zt.CcomunitName,zt.BaseQtyND,zt.Ciquantity,zt.SiQuotedPrice,zt.SiSum \r\n";
selectSQL_CB += " from zhrs_t_zzcSO_SOAddSeriesInfo zt \r\n";
selectSQL_CB += " where zt.SO_SOMain_Id=" + SO_SOMain_Id + " \r\n";
int n = cboReport.SelectedIndex;
string rpt = "";
switch (n)
{
case 0: rpt = "cR96"; break;
case 1: rpt = "CR30890"; break;
case 2: rpt = "CR30892"; break;
case 3: rpt = "CR30895"; break;
case 4: rpt = "CR30906"; break;
case 5: rpt = "CR30948"; break;
}
DataSet ds = new DataSet();
OleDbConnection sqlCon = new OleDbConnection(iLoginEx.ConnString());
sqlCon.Open();
OleDbCommand sqlCmd = new OleDbCommand(selectSQL, sqlCon);
try
{
OleDbDataAdapter sqlAd = new OleDbDataAdapter();
sqlAd.SelectCommand = new OleDbCommand(selectSQL_CB, sqlCon);
sqlAd.Fill(ds, "selectSQL_CB");
sqlAd.SelectCommand = sqlCmd;
sqlAd.Fill(ds, "sql");
selectSQL = "select convert(money,sum(sqs.iquantity),1)as iquantity,sum(isum) as isum from SaleOrderSQ sqs where sqs.id=" + SO_SOMain_Id + " group by sqs.id";
sqlCmd.CommandText = selectSQL;
OleDbDataReader dr = sqlCmd.ExecuteReader();
string sumiquantity = "";
string sumIsum = "";
if (dr.Read())
{
sumiquantity = String.Format("{0:F}", dr["iquantity"]);
sumIsum = dr["isum"].ToString();
}
dr.Close();
sqlCon.Close();
GC.Collect();
//获取报表路径
string path_CB = Application.StartupPath + "\\zzcReport\\CB_SG_Info.rpt";
string path = Application.StartupPath + "\\zzcReport\\" + rpt + ".rpt";
myReoprts.Close();
myReop.Load(path_CB);
myReoprts.Load(path);
myReoprts.SetDataSource(ds.Tables["sql"]);
myReop.SetDataSource(ds.Tables["selectSQL_CB"]);
frmSaleReport frm = new frmSaleReport();
frm.myReoprts = myReoprts;
frm.myReop = myReop;
frm.sumiquantity = sumiquantity;
frm.sumIsum = sumIsum;
frm.SO_SOMain_Id = SO_SOMain_Id;
frm.iLoginEx = iLoginEx;
frm.rpt = rpt;
this.Close();
frm.Show();
}
catch (Exception ex)
{
MessageBox.Show("错误！" + ex.Message);
}
}