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