public static string QueryOrderTj(string bdate, string edate, string emcode, string pzt, string tabcode) { string r = ""; StringBuilder hstr = new StringBuilder(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); if (svt == null) { } else { string b = ""; string e = ""; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate);; } if (pzt != "") { if (pzt == "scz") { where.Append(" and sid in (select sid from CB_orderflow where wcode='0094' and state<1)"); } if (pzt == "ysc") { where.Append(" and sid in (select sid from CB_orderflow where wcode='0094' and state=1)"); } } string sqlw = svt.sqlcondition.Replace("[dcode]", iv.u.dcode).Replace("[bdate]", b).Replace("[edate]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); string sfield = svt.sqlcols; lsr = tsb.QueryList(svt.tname, sfield, where.ToString(), " order by scode desc"); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { int rnum = 0; List <B_AfterGroupProduction> lbp = bgb.QueryList(" and sid='" + dr["sid"].ToString() + "' order by id asc"); if (lbp != null) { rnum = lbp.Count; } hstr.Append("<tr>"); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, i.ToString()); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["scode"].ToString()); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["oscode"].ToString()); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["dname"].ToString()); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["customer"].ToString()); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["isfc"].ToString() == "True"?"是":"否"); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, dr["adremark"].ToString()); if (rnum > 0) { hstr.AppendFormat("<td>{0}</td>", "1." + lbp[0].place); hstr.AppendFormat("<td>{0}</td>", lbp[0].itype); hstr.AppendFormat("<td>{0}</td>", lbp[0].sitype); hstr.AppendFormat("<td>{0}</td>", lbp[0].msname); hstr.AppendFormat("<td>{0}</td>", lbp[0].direction); hstr.AppendFormat("<td>{0}</td>", lbp[0].stype); hstr.AppendFormat("<td>{0}</td>", lbp[0].height.ToString() + "*" + lbp[0].width.ToString() + "*" + lbp[0].deep.ToString()); if (lbp[0].itype == "门板") { hstr.AppendFormat("<td>{0}</td>", lbp[0].deep.ToString()); } else { hstr.AppendFormat("<td>{0}</td>", lbp[0].pmsd.ToString()); } // hstr.AppendFormat("<td>{0}</td>", lbp[0].pnum.ToString()); hstr.AppendFormat("<td>{0}</td>", lbp[0].mname); hstr.AppendFormat("<td>{0}</td>", lbp[0].glass); hstr.AppendFormat("<td>{0}</td>", lbp[0].gsize); hstr.AppendFormat("<td>{0}</td>", lbp[0].fixs); hstr.AppendFormat("<td>{0}</td>", lbp[0].locks); hstr.AppendFormat("<td>{0}</td>", lbp[0].workform); hstr.AppendFormat("<td>{0}</td>", lbp[0].pname + lbp[0].remark); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, CommonBll.GetBdate(dr["scdate"].ToString())); hstr.AppendFormat("<td rowspan='{0}'>{1}</td>", rnum, CommonBll.GetBdate(dr["sodate"].ToString())); } else { hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); hstr.AppendFormat("<td>{0}</td>", ""); } hstr.Append("</tr>"); if (rnum > 1) { int pxh = 2; for (int k = 1; k < rnum; k++) { hstr.Append("<tr>"); hstr.AppendFormat("<td>{0}</td>", pxh.ToString() + "." + lbp[k].place); hstr.AppendFormat("<td>{0}</td>", lbp[k].itype); hstr.AppendFormat("<td>{0}</td>", lbp[k].sitype); hstr.AppendFormat("<td>{0}</td>", lbp[k].msname); hstr.AppendFormat("<td>{0}</td>", lbp[k].direction); hstr.AppendFormat("<td>{0}</td>", lbp[k].stype); hstr.AppendFormat("<td>{0}</td>", lbp[k].height.ToString() + "*" + lbp[k].width.ToString() + "*" + lbp[k].deep.ToString()); if (lbp[k].itype == "门板") { hstr.AppendFormat("<td>{0}</td>", lbp[k].deep.ToString()); } else { hstr.AppendFormat("<td>{0}</td>", lbp[k].pmsd.ToString()); } hstr.AppendFormat("<td>{0}</td>", lbp[k].mname); hstr.AppendFormat("<td>{0}</td>", lbp[k].glass); hstr.AppendFormat("<td>{0}</td>", lbp[k].gsize); hstr.AppendFormat("<td>{0}</td>", lbp[k].fixs); hstr.AppendFormat("<td>{0}</td>", lbp[k].locks); hstr.AppendFormat("<td>{0}</td>", lbp[k].workform); hstr.AppendFormat("<td>{0}</td>", lbp[k].pname + lbp[k].remark); hstr.Append("</tr>"); pxh++; } } i++; } } r = hstr.ToString(); } } else { r = iv.badstr; } return(r); }
public static ArrayList QueryBalanceTj(string bdate, string city, string dcode, string edate, string emcode, string tabcode) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); StringBuilder htmstr = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); string b = ""; string e = ""; int rcount = 0; int pcount = 0; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate); } if (dcode == "") { } else { where.AppendFormat(" and dcode = '{0}'", dcode); } if (city == "") { } else { where.AppendFormat(" and e_city like '%{0}%'", city); } Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); string sqlw = svt.sqlcondition.Replace("[BDATE]", b).Replace("[EDATE]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); lsr = bsob.QueryList(1, 1000, " * ", where.ToString(), " substring(scode,len(scode)-4,5) asc", ref rcount, ref pcount); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { int k = 1; DataTable plistfwj = new DataTable(); DataTable plistwj = new DataTable(); DataTable allplist = new DataTable(); string sqlfwj = "[iname],[sid], [psid], [gnum],[icode],[uname], [mname], [psize],[place],[direction], [fix], [locktype],[gmoney] , [govermoney],[gothermoney],[pnum]"; string sqlwj = "distinct([iname]),'' as [sid],'' as [psid],0 as [gnum],'' as [icode],[uname],'' as [mname],'' as [psize],'' as [place],'' as [direction],'' as [fix],'' as [locktype],sum([gmoney]) as [gmoney],sum([govermoney])as [govermoney],sum([gothermoney]) as[gothermoney],sum([pnum]) as[pnum]"; decimal omoney = 0; plistfwj = tsb.QueryList("View_Tj_SaleBalance", sqlfwj + ", gmoney+govermoney+gothermoney as mm ", " and sid='" + dr["sid"].ToString() + "' and substring(icode,1,2)<>'04'", ""); plistwj = tsb.QueryList("View_Tj_SaleBalance", sqlwj + ", sum(gmoney+govermoney+gothermoney) as mm", " and sid='" + dr["sid"].ToString() + "' and substring(icode,1,2)='04' group by [iname] ,[uname] ", ""); if (plistfwj != null) { k = k + plistfwj.Rows.Count; allplist = plistfwj.Clone(); object[] obj = new object[allplist.Columns.Count]; if (plistfwj != null) { for (int ii = 0; ii < plistfwj.Rows.Count; ii++) { plistfwj.Rows[ii].ItemArray.CopyTo(obj, 0); allplist.Rows.Add(obj); } } if (plistwj != null) { k = k + plistwj.Rows.Count; for (int ii = 0; ii < plistwj.Rows.Count; ii++) { plistwj.Rows[ii].ItemArray.CopyTo(obj, 0); allplist.Rows.Add(obj); } } } else { if (plistwj != null) { //k = k + plistwj.Rows.Count; //allplist = plistwj.Clone(); k = k + plistwj.Rows.Count; allplist = plistwj.Clone(); object[] obj = new object[allplist.Columns.Count]; for (int ii = 0; ii < plistwj.Rows.Count; ii++) { plistwj.Rows[ii].ItemArray.CopyTo(obj, 0); allplist.Rows.Add(obj); } } } #region htmstr.AppendFormat("<tr>"); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, i); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["dname"].ToString()); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["scode"].ToString()); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["customer"].ToString()); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["gzname"].ToString()); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["e_city"].ToString()); htmstr.AppendFormat("<td rowspan='{0}'>{1}</td>", k, dr["address"].ToString()); if (k == 1) { htmstr.AppendFormat("<td colspan='11' style='background:#cccccc'>{0}</td>", "小计"); htmstr.AppendFormat("<td>{0}</td>", "0"); htmstr.AppendFormat("</tr>"); } else { if (allplist.Rows.Count > 0) { htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["place"].ToString()); htmstr.AppendFormat("<td>{0}</td>", "派的门"); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["iname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["mname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["uname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["psize"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["pnum"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["gmoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["govermoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["gothermoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", "0"); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[0]["mm"].ToString()); htmstr.AppendFormat("</tr>"); omoney = omoney + Convert.ToDecimal(allplist.Rows[0]["mm"].ToString()); for (int l = 1; l < allplist.Rows.Count; l++) { htmstr.AppendFormat("<tr>"); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["place"].ToString()); htmstr.AppendFormat("<td>{0}</td>", "派的门"); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["iname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["mname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["uname"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["psize"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["pnum"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["gmoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["govermoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["gothermoney"].ToString()); htmstr.AppendFormat("<td>{0}</td>", "0"); htmstr.AppendFormat("<td>{0}</td>", allplist.Rows[l]["mm"].ToString()); htmstr.AppendFormat("</tr>"); omoney = omoney + Convert.ToDecimal(allplist.Rows[l]["mm"].ToString()); } htmstr.AppendFormat("<tr>"); htmstr.AppendFormat("<td colspan='11' style='background:#cccccc'>{0}</td>", "小计"); htmstr.AppendFormat("<td>{0}</td>", omoney.ToString()); htmstr.AppendFormat("</tr>"); htmstr.AppendFormat("</tr>"); } } #endregion i = i + 1; } } r.Add(htmstr.ToString()); } else { r.Add(iv.badstr); } return(r); }
public static ArrayList QuerySubmitTj(string bdate, string dcode, string edate, string emcode, string tabcode) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); if (svt == null) { } else { string b = ""; string e = ""; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate); } if (dcode == "") { } else { where.AppendFormat(" and dcode = '{0}'", dcode); } string sqlw = svt.sqlcondition.Replace("[BDATE]", b).Replace("[EDATE]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); string sfield = svt.sqlcols; lsr = tsb.QueryList(svt.tname, sfield, where.ToString(), " order by substring(scode,len(scode)-4,5) asc"); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { string sid = dr[0].ToString(); string[] wjarr = bgpb.Sjtj(sid, "0401"); string[] gdarr = bgpb.Sjtj(sid, "0402"); ArrayList al = new ArrayList(); foreach (DataColumn column in lsr.Columns) { switch (column.Caption) { case "xh": al.Add(i); break; case "sjname": al.Add(wjarr[0]); break; case "sjnum": al.Add(wjarr[1]); break; case "gdname": al.Add(gdarr[0]); break; case "gdnum": al.Add(gdarr[1]); break; default: al.Add(dr[column].ToString()); break; } } i++; r.Add(al); } } } } else { r.Add(iv.badstr); } return(r); }
public static ArrayList QueryProductions(string emcode, string tabcode, string bdate, string edate) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); if (bdate != "" && bdate != null) { where.AppendFormat(" and scdate >='{0}'", bdate); } else { where.AppendFormat(" and scdate>='{0}'", CommonBll.GetBdate2()); } if (edate != "" && edate != null) { where.AppendFormat(" and scdate <='{0}'", edate); } else { DateTime dv = DateTime.Now.AddDays(1); where.AppendFormat(" and scdate<='{0}'", dv.ToString("yyyy-MM-dd")); } Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); lsr = tsb.QueryList(svt.tname, svt.sqlcols, where.ToString(), " order by pid "); if (lsr != null) { int xh = 1; foreach (DataRow dr in lsr.Rows) { ArrayList al = new ArrayList(); foreach (DataColumn column in lsr.Columns) { switch (column.Caption) { case "xh": al.Add(xh); break; case "e_ptype": switch (dr[column].ToString()) { case "f": case "s": al.Add("门扇"); break; case "mt": al.Add("横挺"); break; case "lb": al.Add("立挺"); break; case "stl": al.Add("横L线"); break; case "ltl": al.Add("竖L线"); break; default: al.Add(" "); break; } break; default: al.Add(dr[column].ToString()); break; } } xh++; r.Add(al); } } } else { r.Add(iv.badstr); } return(r); }
public static ArrayList QueryCheckRedoTj(string bdate, string edate, string emcode, string tabcode) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); if (svt == null) { } else { string b = ""; string e = ""; string dn = ""; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate); } string sqlw = svt.sqlcondition.Replace("[DNAME]", dn).Replace("[bdate]", b).Replace("[edate]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); string sfield = svt.sqlcols; lsr = tsb.QueryList(svt.tname, sfield, where.ToString(), " order by sdate desc"); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { ArrayList al = new ArrayList(); foreach (DataColumn column in lsr.Columns) { string sid = dr[0].ToString(); switch (column.Caption) { case "xh": al.Add(i); break; case "plist": string pl = bagpb.QueryStrList(sid); al.Add(pl); break; default: al.Add(dr[column].ToString()); break; } } i++; r.Add(al); } } } } else { r.Add(iv.badstr); } return(r); }
public static ArrayList QueryOrderTj(string bdate, string dname, string edate, string emcode, string tabcode) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); if (svt == null) { } else { string b = ""; string e = ""; string dn = ""; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate); } if (dname == "") { } else { dn = " and dname like '" + dname + "%'"; } string sqlw = svt.sqlcondition.Replace("[DNAME]", dn).Replace("[BDATE]", b).Replace("[EDATE]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); string sfield = svt.sqlcols; lsr = tsb.QueryList(svt.tname, sfield, where.ToString(), " order by scode desc"); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { ArrayList al = new ArrayList(); foreach (DataColumn column in lsr.Columns) { string sid = dr[0].ToString(); switch (column.Caption) { case "xh": al.Add(i); break; case "money": al.Add(0); break; case "pmoney": al.Add(0); break; case "mss": al.Add(bgb.TjProductionMsNum(sid)); break; case "mts": al.Add(bgb.TjProductionCNum(" and sid='" + sid + "' and substring(icode,1,2)='02'")); break; case "cts": al.Add(bgb.TjProductionCNum(" and sid='" + sid + "' and substring(icode,1,2)='06'")); break; case "yks": al.Add(bgb.TjProductionCNum(" and sid='" + sid + "' and substring(icode,1,2)='07'")); break; case "wjs": al.Add(bgb.TjProductionQtNum(" and sid='" + sid + "' and substring(icode,1,2)='04'")); break; case "qts": al.Add(bgb.TjProductionQtNum(" and sid='" + sid + "' and( substring(icode,1,2)='08'or substring(icode,1,2)='09'")); break; case "azfs": al.Add(""); break; default: al.Add(dr[column].ToString()); break; } } i++; r.Add(al); } } } } else { r.Add(iv.badstr); } return(r); }
public static ArrayList QueryafterTj(string bdate, string duty, string edate, string fname, string emcode, string stype, string tabcode) { ArrayList r = new ArrayList(); DataTable lsr = new DataTable(); StringBuilder where = new StringBuilder(); SessionUserValidate iv = SysValidateBll.ValidateSession(); if (iv.f) { r.Add(iv.badstr); Sys_ViewTable svt = svtb.QuerySelCols(emcode, tabcode, iv.u.rcode); if (svt == null) { } else { string b = ""; string e = ""; string dn = ""; if (bdate == null || bdate == "") { b = CommonBll.GetBdate2(); } else { b = CommonBll.GetBdate(bdate); } if (edate == null || edate == "") { e = CommonBll.GetEdate(); } else { e = CommonBll.GetEdate(edate); } if (duty != "" && duty != null) { string rs = ""; string[] ar = duty.Split(';'); for (int i = 0; i < ar.Length; i++) { if (i == 0) { rs = " aduty='" + ar[i] + "'"; } else { rs = " or aduty='" + ar[i] + "'"; } } where.AppendFormat(" and ({0})", rs); } if (fname != "" && fname != null) { where.AppendFormat(" and fname='{0}'", fname); } if (stype != "" && stype != null) { where.AppendFormat(" and sendtype='{0}'", stype); } string sqlw = svt.sqlcondition.Replace("[DNAME]", dn).Replace("[BDATE]", b).Replace("[EDATE]", e); where.Append(CommonBll.SqlWhereReplace(iv.u, sqlw)); string sfield = svt.sqlcols; lsr = tsb.QueryList(svt.tname, sfield, where.ToString(), " order by senddate desc"); if (lsr != null) { int i = 1; foreach (DataRow dr in lsr.Rows) { ArrayList al = new ArrayList(); foreach (DataColumn column in lsr.Columns) { string sid = dr[0].ToString(); switch (column.Caption) { case "xh": al.Add(i); break; default: al.Add(dr[column].ToString()); break; } } i++; r.Add(al); } } } } else { r.Add(iv.badstr); } return(r); }