private void BinData()
{
string m_scrq1 = dateedtSCRQ1.Text.Trim();
string m_scrq2 = dateedtSCRQ2.Text.Trim();
string m_gxid = cbGX.Value.ToString().Trim();
string m_xxlx = cbXXLX.Text.ToString().Trim();
//获取工序对应的班组作业明细的数据表名称
string tablename = Wage_Helper.GetBzMxTableName(m_gxid);
string where = "SELECT sum(CZSL)CZSL,sum(SJCZBS)SJCZBS,sum(HGBS)HGBS,sum(BHGBS)BHGBS ";
where = string.Format(" {0},Cast(Cast(sum(BHGBS)As decimal(18,3))/Cast(sum(CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", where);
where = string.Format(" {0},'{1}'as TABLENAME ,'{2}' as M_XXLX ,'{3}'as CZRQ1 ,'{4}'as CZRQ2", where, tablename, m_xxlx, m_scrq1, m_scrq2);
where = string.Format(" {0} FROM {1} MX ", where, tablename);
where = string.Format(" {0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID WHERE 1=1", where);
if (!string.IsNullOrEmpty(m_scrq1) && !string.IsNullOrEmpty(m_scrq2))
{
where = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", where, m_scrq1, m_scrq2);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
where = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", where, "");
}
else if (m_xxlx == "三相")
{
where = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", where, m_xxlx);
}
else
{
where = string.Format(" {0} and XXLX = '{1}'", where, m_xxlx);
}
}
//where = string.Format(" {0} GROUP BY CZRQ", where);
GG_BZZYMX valBzzymx = new GG_BZZYMX();
GG_BZZYMX condBzzymx = new GG_BZZYMX();
JC_CPXX valCpxx = new JC_CPXX();
JC_CPXX condCpxx = new JC_CPXX();
JC_KHXX valKhxx = new JC_KHXX();
JC_KHXX condKhxx = new JC_KHXX();
valCpxx._XXLX = "";
valCpxx._SAPRemark = "";
valCpxx._XH = "";
valKhxx._KHMC = "";
conn = "connMES";
DataTable dtb = BLLTable <GG_BZZYMX> .Factory(conn).qDataTable(where);
this.grid1.DataSource = dtb;
grid1.DataBind(); //执行绑定
ViewState["detailtable"] = dtb; //dtbRGZMX;
}
/// <summary>
/// 一次不良率报表 在班组作业明细表中按照班组获取作业单
/// </summary>
/// <param name="tablename">班组作业明细表名</param>
/// <param name="czrq">操作日期</param>
/// <param name="xh">型号</param>
/// <param name="strwhere">查询条件</param>
/// <returns></returns>
public DataTable GetSczydByBZ(string bzid, string xh, string czrq1, string czrq2, string tbname, string m_xxlx)
{
GG_BZZYMX valYgzmx = new GG_BZZYMX();
GG_BZZYMX conYgzmx = new GG_BZZYMX();
string detailwhere = " SELECT SCZYDID,KHMC,MX.CPID,SAPRemark,CZSL,SJCZBS,HGBS,BHGBS ";
detailwhere = string.Format(" {0},Cast(Cast((BHGBS)As decimal(18,3))/Cast((CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", detailwhere);
detailwhere = string.Format(" {0},'{1}'as TABLENAME ,XXLX as M_XXLX ", detailwhere, tbname);
detailwhere = string.Format(" {0} FROM {1} MX ", detailwhere, tbname);
detailwhere = string.Format("{0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID", detailwhere);
detailwhere = string.Format("{0} JOIN JC_KHXX KHXX ON MX.KHID=KHXX.KHID WHERE 1=1", detailwhere);
if (!string.IsNullOrEmpty(czrq1) && !string.IsNullOrEmpty(czrq2))
{
detailwhere = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", detailwhere, czrq1, czrq2);
}
if (!string.IsNullOrEmpty(xh))
{
detailwhere = string.Format(" {0} and XH='{1}'", detailwhere, xh);
}
if (!string.IsNullOrEmpty(bzid))
{
detailwhere = string.Format(" {0} and BZID='{1}'", detailwhere, bzid);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
detailwhere = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", detailwhere, "");
}
else if (m_xxlx == "三相")
{
detailwhere = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", detailwhere, m_xxlx);
}
else
{
detailwhere = string.Format(" {0} and XXLX = '{1}'", detailwhere, m_xxlx);
}
}
//detailwhere = string.Format("{0} GROUP BY SCZYDID,KHMC,XXLX", detailwhere);
conn = "connMES";
DataTable dtbYGZMX = BLLTable <GG_BZZYMX> .Factory(conn).qDataTable(detailwhere);
return(dtbYGZMX);
}
/// <summary>
/// 一次不良率报表 按照型号获取班组作业信息
/// </summary>
/// <param name="tablename">班组作业明细表名</param>
/// <param name="czrq">操作日期</param>
/// <param name="xh">型号</param>
/// <param name="strwhere">查询条件</param>
/// <returns></returns>
public DataTable GetBzzyByXH(string czrq1, string czrq2, string xh, string tbname, string m_xxlx)
{
GG_BZZYMX valYgzmx = new GG_BZZYMX();
GG_BZZYMX conYgzmx = new GG_BZZYMX();
string detailwhere = " SELECT XH,BZID,JGMC,sum(CZSL)CZSL,sum(SJCZBS)SJCZBS,sum(HGBS)HGBS,sum(BHGBS)BHGBS ";
detailwhere = string.Format(" {0},Cast(Cast(sum(BHGBS)As decimal(18,3))/Cast(sum(CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", detailwhere);
detailwhere = string.Format(" {0},'{1}'as TABLENAME ,'{2}' as M_XXLX ,'{3}'as CZRQ1 ,'{4}'as CZRQ2 ", detailwhere, tbname, m_xxlx, czrq1, czrq2);
detailwhere = string.Format(" {0} FROM {1} MX ", detailwhere, tbname);
detailwhere = string.Format("{0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID ", detailwhere);
detailwhere = string.Format("{0} JOIN JC_ZZJG ZZJG ON MX.BZID=ZZJG.JGID WHERE 1=1", detailwhere);
if (!string.IsNullOrEmpty(czrq1) && !string.IsNullOrEmpty(czrq2))
{
detailwhere = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", detailwhere, czrq1, czrq2);
}
if (!string.IsNullOrEmpty(xh))
{
detailwhere = string.Format(" {0} and XH='{1}'", detailwhere, xh);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
detailwhere = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", detailwhere, "");
}
else if (m_xxlx == "三相")
{
detailwhere = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", detailwhere, m_xxlx);
}
else
{
detailwhere = string.Format(" {0} and XXLX = '{1}'", detailwhere, m_xxlx);
}
}
detailwhere = string.Format("{0} GROUP BY XH,BZID,JGMC", detailwhere);
conn = "connMES";
DataTable dtbYGZMX = BLLTable <GG_BZZYMX> .Factory(conn).qDataTable(detailwhere);
return(dtbYGZMX);
}
private void BinData()
{
string m_scrq1 = dateedtSCRQ1.Date.ToString("yyy-MM-dd");
string m_scrq2 = dateedtSCRQ2.Date.ToString("yyy-MM-dd");
string m_gxid = cbGX.Value.ToString().Trim();
string m_xxlx = cbXXLX.Text.ToString().Trim();
//获取工序对应的班组作业明细的数据表名称
string tablename = Wage_Helper.GetBzMxTableName(m_gxid);
string where = "SELECT sum(CZSL)CZSL,sum(SJCZBS)SJCZBS,sum(HGBS)HGBS,sum(BHGBS)BHGBS ";
where = string.Format(" {0},Cast(Cast(sum(BHGBS)As decimal(18,3))/Cast(sum(CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", where);
where = string.Format(" {0},'{1}'as TABLENAME ,'{2}' as M_XXLX ,'{3}'as CZRQ1 ,'{4}'as CZRQ2", where, tablename, m_xxlx, m_scrq1, m_scrq2);
where = string.Format(" {0} FROM {1} MX ", where, tablename);
where = string.Format(" {0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID WHERE 1=1", where);
if (!string.IsNullOrEmpty(m_scrq1) && !string.IsNullOrEmpty(m_scrq2))
{
where = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", where, m_scrq1, m_scrq2);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
where = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", where, "");
}
else if (m_xxlx == "三相")
{
where = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", where, m_xxlx);
}
else
{
where = string.Format(" {0} and XXLX = '{1}'", where, m_xxlx);
}
}
//where = string.Format(" {0} GROUP BY CZRQ", where);
GG_BZZYMX valBzzymx = new GG_BZZYMX();
GG_BZZYMX condBzzymx = new GG_BZZYMX();
JC_CPXX valCpxx = new JC_CPXX();
JC_CPXX condCpxx = new JC_CPXX();
JC_KHXX valKhxx = new JC_KHXX();
JC_KHXX condKhxx = new JC_KHXX();
valCpxx._XXLX = "";
valCpxx._SAPRemark = "";
valCpxx._XH = "";
valKhxx._KHMC = "";
conn = "connMES";
DataTable dtb = BLLTable<GG_BZZYMX>.Factory(conn).qDataTable(where);
this.grid1.DataSource = dtb;
grid1.DataBind(); //执行绑定
ViewState["detailtable"] = dtb;//dtbRGZMX;
}
/// <summary>
/// 一次不良率报表 在班组作业明细表中按照班组获取作业单
/// </summary>
/// <param name="tablename">班组作业明细表名</param>
/// <param name="czrq">操作日期</param>
/// <param name="xh">型号</param>
/// <param name="strwhere">查询条件</param>
/// <returns></returns>
public DataTable GetSczydByBZ(string bzid, string xh, string czrq1, string czrq2, string tbname, string m_xxlx)
{
GG_BZZYMX valYgzmx = new GG_BZZYMX();
GG_BZZYMX conYgzmx = new GG_BZZYMX();
string detailwhere = " SELECT SCZYDID,KHMC,MX.CPID,SAPRemark,CZSL,SJCZBS,HGBS,BHGBS ";
detailwhere = string.Format(" {0},Cast(Cast((BHGBS)As decimal(18,3))/Cast((CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", detailwhere);
detailwhere = string.Format(" {0},'{1}'as TABLENAME ,XXLX as M_XXLX ", detailwhere, tbname);
detailwhere = string.Format(" {0} FROM {1} MX ", detailwhere, tbname);
detailwhere = string.Format("{0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID", detailwhere);
detailwhere = string.Format("{0} JOIN JC_KHXX KHXX ON MX.KHID=KHXX.KHID WHERE 1=1", detailwhere);
if (!string.IsNullOrEmpty(czrq1) && !string.IsNullOrEmpty(czrq2))
{
detailwhere = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", detailwhere, czrq1, czrq2);
}
if (!string.IsNullOrEmpty(xh))
{
detailwhere = string.Format(" {0} and XH='{1}'", detailwhere, xh);
}
if (!string.IsNullOrEmpty(bzid))
{
detailwhere = string.Format(" {0} and BZID='{1}'", detailwhere, bzid);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
detailwhere = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", detailwhere, "");
}
else if (m_xxlx == "三相")
{
detailwhere = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", detailwhere, m_xxlx);
}
else
{
detailwhere = string.Format(" {0} and XXLX = '{1}'", detailwhere, m_xxlx);
}
}
//detailwhere = string.Format("{0} GROUP BY SCZYDID,KHMC,XXLX", detailwhere);
conn = "connMES";
DataTable dtbYGZMX = BLLTable<GG_BZZYMX>.Factory(conn).qDataTable(detailwhere);
return dtbYGZMX;
}
/// <summary>
/// 一次不良率报表 按照型号获取班组作业信息
/// </summary>
/// <param name="tablename">班组作业明细表名</param>
/// <param name="czrq">操作日期</param>
/// <param name="xh">型号</param>
/// <param name="strwhere">查询条件</param>
/// <returns></returns>
public DataTable GetBzzyByXH(string czrq1, string czrq2, string xh, string tbname, string m_xxlx)
{
GG_BZZYMX valYgzmx = new GG_BZZYMX();
GG_BZZYMX conYgzmx = new GG_BZZYMX();
string detailwhere = " SELECT XH,BZID,JGMC,sum(CZSL)CZSL,sum(SJCZBS)SJCZBS,sum(HGBS)HGBS,sum(BHGBS)BHGBS ";
detailwhere = string.Format(" {0},Cast(Cast(sum(BHGBS)As decimal(18,3))/Cast(sum(CZSL)As decimal(18,3))*1000 as decimal(18,3)) YCBLL ", detailwhere);
detailwhere = string.Format(" {0},'{1}'as TABLENAME ,'{2}' as M_XXLX ,'{3}'as CZRQ1 ,'{4}'as CZRQ2 ", detailwhere, tbname, m_xxlx, czrq1, czrq2);
detailwhere = string.Format(" {0} FROM {1} MX ", detailwhere, tbname);
detailwhere = string.Format("{0} JOIN JC_CPXX CPXX ON MX.CPID=CPXX.CPID ", detailwhere);
detailwhere = string.Format("{0} JOIN JC_ZZJG ZZJG ON MX.BZID=ZZJG.JGID WHERE 1=1", detailwhere);
if (!string.IsNullOrEmpty(czrq1) && !string.IsNullOrEmpty(czrq2))
{
detailwhere = string.Format(" {0} and CZRQ >= '{1}' AND CZRQ <= '{2}'", detailwhere, czrq1, czrq2);
}
if (!string.IsNullOrEmpty(xh))
{
detailwhere = string.Format(" {0} and XH='{1}'", detailwhere, xh);
}
if (!string.IsNullOrEmpty(m_xxlx) && m_xxlx != "全部")
{
if (m_xxlx == "其它")
{
detailwhere = string.Format(" {0} and ( XXLX = '{1}' OR XXLX = NULL)", detailwhere, "");
}
else if (m_xxlx == "三相")
{
detailwhere = string.Format(" {0} and substring(XXLX,1,2) = '{1}'", detailwhere, m_xxlx);
}
else
{
detailwhere = string.Format(" {0} and XXLX = '{1}'", detailwhere, m_xxlx);
}
}
detailwhere = string.Format("{0} GROUP BY XH,BZID,JGMC", detailwhere);
conn = "connMES";
DataTable dtbYGZMX = BLLTable<GG_BZZYMX>.Factory(conn).qDataTable(detailwhere);
return dtbYGZMX;
}
