private void FreshJch() { string sql = "select JCDate ,JCTYPE ,JCGoodsName,JCGoodsID ,JCHGSL ,JCKLSL ,JCPSSL ," + " JCKHSL ,JCCarNO , JCWorknum ,JCOperator ,JCSerialNum,JCModelno,JCItemID,JCMaterial,FImagePath,FRYHGSL,FHGL,FKilnNO " + " from tb_jch where JCTYPE = 1 "; if (dtdate.Text != "") { sql += "and JCDate >= '" + dtdate.Value.Date.ToShortDateString() + "'"; } if (dtdate2.Text != "") { sql += "and JCDate <= '" + dtdate2.Value.Date.ToShortDateString() + "'"; } if (cbgoods.Text.Trim() != "") { sql += "and JCGoodsName = '" + cbgoods.Text.Trim() + "'"; } if (tbcar.Text.Trim() != "") { sql += "and JCCarNO like '%" + tbcar.Text.Trim() + "%'"; } if (cbkilnno.Text.Trim() != "") { sql += "and FKilnNO = '" + cbkilnno.SelectedValue.ToString().Trim() + "'"; } sql += " order by JCDate desc,JCCarNO desc "; ds = dbl.GetDataset(sql, "TB_JCH"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; supergrid.PrimaryGrid.DataSource = ds; supergrid.PrimaryGrid.DataMember = "TB_JCH"; Bm = (dataSource != null) ? supergrid.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar.btnEditEnabled = true; toolbar.btndelete.Enabled = true; } //UpdateBindings(context); int zysl = 0; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { zysl += Convert.ToInt16(ds.Tables[0].Rows[i]["JCHGSL"].ToString()); } lblcysl.Text = String.Format("{0}", zysl); } else { supergrid.PrimaryGrid.DataSource = null; lblcysl.Text = ""; } }
private void FreshBase(string _class) { string sql = "select FBaseID,FBaseName,FRemark from tb_base where FClass = '" + _class + "' order by FBaseID,FBaseName"; ds = dbl.GetDataset(sql, "tb_base"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "tb_Base"; Bm = (dataSource != null) ? superGridControl1.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar1.btnEditEnabled = true; toolbar1.btndelete.Enabled = true; } UpdateBindings(context); } else { superGridControl1.PrimaryGrid.DataSource = null; ClearControls(); } }
private void FreshGoods() { string sql = "select GoodId,GoodName,ItemNO,ModelNO,FImagePath from tb_GoodsInfo order by FCreateDate desc,GoodName asc"; ds = dbl.GetDataset(sql, "tb_GoodsInfo"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "tb_GoodsInfo"; Bm = (dataSource != null) ? superGridControl1.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar1.btnEditEnabled = true; toolbar1.btndelete.Enabled = true; } } else { superGridControl1.PrimaryGrid.DataSource = null; } }
private void FreshFh() { string sql = "select FDate,FWorkNum,FOperator,FWareID,FGoodID,FHGSL,FType,FSerialNum,FGoodsName,FItemID,FMaterial,FImagePath,FModelNo,FInvoiceType " + " from WareInOut where FTYPE = 0 "; if (dtdate.Text != "") { sql += "and FDate >= '" + dtdate.Value.Date.ToShortDateString() + "'"; } if (dtdate2.Text != "") { sql += "and FDate <= '" + dtdate2.Value.Date.ToShortDateString() + "'"; } if (cbgoods.Text.Trim() != "") { sql += "and FGoodsName = '" + cbgoods.Text.Trim() + "'"; } if (tbwarehourse.Text.Trim() != "") { sql += "and FWareID = '" + tbwarehourse.SelectedValue + "'"; } if (cbintype.Text.Trim() != "") { sql += "and FInvoiceType = '" + cbintype.SelectedValue + "'"; } sql += " order by FSerialNum desc "; ds = dbl.GetDataset(sql, "WareInOut"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; supergrid.PrimaryGrid.DataSource = ds; supergrid.PrimaryGrid.DataMember = "WareInOut"; Bm = (dataSource != null) ? supergrid.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar1.btnEditEnabled = true; toolbar1.btndelete.Enabled = true; } //UpdateBindings(context); int sl = 0; for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { sl += Convert.ToInt16(Convert.ToInt16(ds.Tables[0].Rows[i]["FHGSL"].ToString())); } lblsl.Text = String.Format("{0} ", sl);//碰损数量: {1} } else { supergrid.PrimaryGrid.DataSource = null; lblsl.Text = ""; } }
private void FreshJch() { string tosql = "select * , case when (cksl + pssl)=0 then 0 else convert(numeric(18,4),convert(numeric(18,4),pssl)/convert(numeric(18,4),(cksl + pssl))) end as psl from ({0}) as a "; string sql = ""; if (date.Text.Trim() != "") { sql = "select b.warename ,FGoodsName ,FMaterial,(select sum(case when FType = 0 then FHGSL else -(FHGSL + FPSSL) end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FMaterial = a.FMaterial and FDate<'{0}') as zrjc, " + " (select sum(case when FType = 0 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FMaterial = a.FMaterial and FDate ='" + date.Text.Trim() + "') as rksl, (select sum(case when FType = 1 then ISNULL(FPSSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FMaterial = a.FMaterial and FDate ='" + date.Text.Trim() + "') as pssl, (select sum(case when FType = 1 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FMaterial = a.FMaterial and FDate ='" + date.Text.Trim() + "') as CKSL ," + " sum(case when FType = 0 then FHGSL else -(FHGSL + FPSSL) end) as JCSL" + " from WareInOut a " + " left join tb_warehouse b on a.FWareID = b.wareid " + " where 1= 1 "; sql += string.Format(" and FDate <= '{0}'", date.Text.Trim()); sql = string.Format(sql, date.Value); } if (tbgoodsname.Text.Trim() != "") { sql += " and a.FGoodsname = '" + tbgoodsname.Text.Trim() + "'"; } if (cbwarehouse.Text.Trim() != "") { sql += " and a.FWareID = '" + cbwarehouse.SelectedValue.ToString() + "'"; } sql += " group by a.FWareID, b.warename,FGoodsname,FMaterial "; //sql = string.Format(sql, date.Value.AddDays(-1), date.Value); tosql = string.Format(tosql, sql); ds = dbl.GetDataset(tosql, "WareInOut"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "WareInOut"; int sl = 0; for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { sl += Convert.ToInt16(Convert.ToInt16(ds.Tables[0].Rows[i]["JCSL"].ToString())); } lblsl.Text = sl.ToString(); } else { superGridControl1.PrimaryGrid.DataSource = null; lblsl.Text = ""; } }
private void buttonX1_Click(object sender, EventArgs e) { object dataSource = null; string tosql = "select *,case when (JCPSSL + JCKLSL + JCKHSL+CYHGSL)=0 then 0 else convert(numeric(18,4),convert(numeric(18,4),(JCPSSL + JCKLSL + JCKHSL))/convert(numeric(18,4),(JCPSSL + JCKLSL + JCKHSL+CYHGSL))) end as bhgl from ({0}) as a "; string sql = "select JCDATE, JCGoodsName,JCMaterial,FKilnNO,sum(case when jctype =0 then JCHGSL else 0 end) as ZYHGSL ,SUM(JCKLSL) AS JCKLSL ," + " sum(case when jctype =1 then JCHGSL else 0 end) as CYHGSL,sum(JCPSSL) as JCPSSL ," + " sum(JCKHSL) as JCKHSL ,JCCarNO " + " from tb_jch where 1=1 "; if (date.Text != "") { sql += " and JCDate >='" + date.Text.Trim() + "'"; } if (enddate.Text != "") { sql += " and JCDate <='" + enddate.Text.Trim() + "'"; } if (tbgood.Text != "") { sql += " and JCGoodsName ='" + tbgood.Text.Trim() + "'"; } if (tbcar.Text != "") { sql += "and Jccarno = '" + tbcar.Text.Trim() + "'"; } if (cmbkilnno.Text.Trim() != "") { sql += "and FKilnNO = '" + cmbkilnno.SelectedValue.ToString().Trim() + "'"; } sql += " group by JCDATE,JCGoodsName,JCCarNO,JCMaterial,FKilnNO "; tosql = string.Format(tosql, sql); ds = dbl.GetDataset(tosql, "TB_JCH"); if (ds != null) { dataSource = ds.Tables[0]; //context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "TB_JCH"; } else { superGridControl1.PrimaryGrid.DataSource = null; //superGridControl1.PrimaryGrid.DataMember = "TB_JCH"; } }
private void FreshWare() { string sql = "select WareID,WareName,Remark from tb_WareHouse"; ds = dbl.GetDataset(sql, "tb_WareHouse"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "tb_WareHouse"; Bm = (dataSource != null) ? superGridControl1.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar1.btnEditEnabled = true; toolbar1.btndelete.Enabled = true; } UpdateBindings(context); } }
private void FreshSp() { string sql = @"SELECT [PlApplyDate] ,[PlApplyTime] ,[PlOperator] ,[PlSerialNum] ,[PlWorkNumID] ,[PlGoodsID] ,[PlKilnNo] ,[PlColor] ,[PlWorkshopId] ,[PlNeedGlazed] ,[PlModelFaults] ,[PlGlazeFaults] ,[PlFireFaults] ,[PlLevels] ,PlModelNo ,[PlQty] ,[PlThisQty] ,FImagePath ,FMaterial FROM [dbo].[SelPorceLain] where 1=1 "; if (dtdate.Text != "") { sql += " and PlApplyDate >= '" + dtdate.Value.Date.ToShortDateString() + "'"; } if (dtdate2.Text != "") { sql += " and PlApplyDate <= '" + dtdate2.Value.Date.ToShortDateString() + "'"; } if (cbgoods.Text.Trim() != "") { sql += " and PlGoodsName = '" + cbgoods.Text.Trim() + "'"; } if (cmbOpr.Text.Trim() != "") { sql += " and PlOperator like '%" + cmbOpr.Text.Trim() + "%' "; } if (cmbMaterial.SelectedValue != null) { sql += " and FMaterial = '" + cmbMaterial.SelectedValue.ToString() + "'"; } if (cmbcolor.SelectedValue != null) { sql += " and PlColor = '" + cmbcolor.SelectedValue.ToString() + "'"; } sql += " order by PlApplyDate DESC,PlSerialNum desc "; ds = dbl.GetDataset(sql, "SelPorceLain"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; supergrid.PrimaryGrid.DataSource = ds; supergrid.PrimaryGrid.DataMember = "SelPorceLain"; Bm = (dataSource != null) ? supergrid.BindingContext[context] : null; if (Bm.Count >= 0) { toolbar1.btnEditEnabled = true; toolbar1.btndelete.Enabled = true; } int zysl = 0; int qtyA = 0; int qtyAd = 0; int qtyCa = 0; int qtyB = 0; int qtyBJ = 0; int qtyBu = 0; int qtyFp = 0; int qtyC = 0; for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { zysl += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "A+") { qtyA += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "A等") { qtyAd += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "CA等") { qtyCa += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "B甲") { qtyBJ += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "B等") { qtyB += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "C等") { qtyC += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "补釉") { qtyBu += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } if (ds.Tables[0].Rows[i]["PlLevels"].ToString() == "废品") { qtyFp += Convert.ToInt32(Convert.ToInt32(ds.Tables[0].Rows[i]["PlQty"].ToString())); } //pssl += Convert.ToInt16(Convert.ToInt16(ds.Tables[0].Rows[i]["FPSSL"].ToString())); } lblzysl.Text = String.Format("{0} ", zysl); //碰损数量: {1} lblA.Text = String.Format("{0} ", qtyA); //A+: {1} lblAd.Text = String.Format("{0} ", qtyAd); //A等: {1} lblCa.Text = String.Format("{0} ", qtyCa); //CA: {1} lblBJ.Text = String.Format("{0} ", qtyBJ); //B甲: {1} lblB.Text = String.Format("{0} ", qtyB); //B: {1} lblC.Text = String.Format("{0} ", qtyC); //B: {1} lblBu.Text = String.Format("{0} ", qtyBu); //补釉: {1} lblFp.Text = String.Format("{0} ", qtyFp); //废品: {1} } else { supergrid.PrimaryGrid.DataSource = null; lblzysl.Text = ""; } }
private void FreshJch() { //string tosql = "select * , case when (cksl + pssl)=0 then 0 else convert(numeric(18,4),convert(numeric(18,4),pssl)/convert(numeric(18,4),(cksl + pssl))) end as psl from ({0}) as a "; string sql = "select b.warename ,a.FDate,FGoodsName , case when FType = 0 then FHGSL else 0 end as rksl, FPssl as pssl,case when FType = 1 then FHGSL else 0 end as CKSL ," + "case when (FHGSL + FPSSL)=0 then 0 else convert(numeric(18,4),convert(numeric(18,4),FPSSL)/convert(numeric(18,4),(FHGSL + FPSSL))) end as psl," + " sum(case when FType = 0 then FHGSL else -(FHGSL+FPSSL) end) over(partition by a.FGOODSNAME ORDER BY A.ID) as JCSL " + "from WareInOut a " + " left join tb_warehouse b on a.FWareID = b.wareid " + " where 1= 1 "; /* if (date.Text.Trim() != "" && enddate.Text.Trim() == "") * { * sql = "select b.warename ,FGoodsName , " + * " (select sum(case when FType = 0 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as rksl, (select sum(case when FType = 1 then ISNULL(FPSSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as pssl, (select sum(case when FType = 1 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as CKSL ," + * " sum(case when FType = 0 then FHGSL else -(FHGSL + FPSSL) end) as JCSL" + * " from WareInOut a " + * " left join tb_warehouse b on a.FWareID = b.wareid " + * " where 1= 1 "; * sql += string.Format(" and FDate <= '{0}'",date.Text.Trim()); * sql = string.Format(sql, date.Value); * } * if (date.Text.Trim() == "" && enddate.Text.Trim() != "") * { * sql = "select b.warename ,FGoodsName , " + * " (select sum(case when FType = 0 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as rksl, (select sum(case when FType = 1 then ISNULL(FPSSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as pssl, (select sum(case when FType = 1 then ISNULL(FHGSL,0) else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as CKSL ,"+ * " sum(case when FType = 0 then FHGSL else -(FHGSL + FPSSL) end) as JCSL" + * " from WareInOut a " + * " left join tb_warehouse b on a.FWareID = b.wareid " + * " where 1= 1 "; * sql += string.Format(" and FDate <= '{0}'", date.Text.Trim()); * sql = string.Format(sql, enddate.Value, "(select sum(case when FType = 0 then FHGSL else 0 end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate ='" + date.Text.Trim() + "') as rksl"); * } * if (date.Text.Trim() != "" && enddate.Text.Trim() != "") * { * sql = "select b.warename ,FGoodsName ," + * " sum(case when FType = 0 then FHGSL else 0 end) as rksl, sum(fpssl) as pssl,sum(case when FType = 1 then FHGSL else 0 end) as CKSL ,(select sum(case when FType = 0 then FHGSL else -(FHGSL + FPSSL) end) from WareInOut where FWareID=a.FWareID and FGoodsName = a.FGoodsName and FDate<='{1}') as JCSL" + * " from WareInOut a " + * " left join tb_warehouse b on a.FWareID = b.wareid " + * " where 1= 1 "; * sql += " and FDate >= '" + date.Text.Trim() + "' and FDate <= '" + enddate.Text.Trim() + "'"; * sql = string.Format(sql, date.Value,enddate.Value); * } * */ if (date.Text.Trim() != "") { sql += "and FDate >= '" + date.Text.Trim() + "' "; } if (enddate.Text.Trim() != "") { sql += "and FDate <= '" + enddate.Text.Trim() + "'"; } if (tbgoodsname.Text.Trim() != "") { sql += " and a.FGoodsname = '" + tbgoodsname.Text.Trim() + "'"; } if (cbwarehouse.Text.Trim() != "") { sql += " and a.FWareID = '" + cbwarehouse.SelectedValue.ToString() + "'"; } //sql += " group by a.FWareID, b.warename,FGoodsname "; //sql = string.Format(sql, date.Value.AddDays(-1), date.Value); //tosql = string.Format(tosql, sql); ds = dbl.GetDataset(sql, "WareInOut"); if (ds != null) { dataSource = ds.Tables[0]; context = dataSource;//((DataSet)dataSource).Tables[0]; //dataMember = ((DataTable)dataSource).DefaultView; superGridControl1.PrimaryGrid.DataSource = ds; superGridControl1.PrimaryGrid.DataMember = "WareInOut"; } else { superGridControl1.PrimaryGrid.DataSource = null; } }