/// <summary> /// 获取编号 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static string getNUMBER(DC_DETAILS_SW sw) { string total = ""; StringBuilder sb = new StringBuilder(); sb.AppendFormat(" from DC_DETAILS a "); sb.AppendFormat("where "); sb.AppendFormat("NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER)); string sqlC = "select count(distinct NUMBER) " + sb.ToString(); //total = DataBaseClass.ReturnSqlField(sqlC); total = (int.Parse(DataBaseClass.ReturnSqlField(sqlC)) + 1).ToString(); return(total); }
/// <summary> /// 获取列表分页 /// </summary> /// <param name="sw"></param> /// <param name="total"></param> /// <returns></returns> public static IEnumerable <DC_DETAILS_Model> getModelPager(DC_DETAILS_SW sw, out int total) { var result = new List <DC_DETAILS_Model>(); DataTable dtUser = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { }); DataTable dt = BaseDT.DC_DETAILS.getDT(sw, out total);//列表 for (int i = 0; i < dt.Rows.Count; i++) { DC_DETAILS_Model m = new DC_DETAILS_Model(); m.DCDETAILSID = dt.Rows[i]["DCDETAILSID"].ToString(); m.SUPID = dt.Rows[i]["SUPID"].ToString(); m.SUPNAME = DC_SUPPLIESPROPCls.getsupname(m.SUPID); m.DCSUPPROPMODEL = BaseDT.DC_SUPPLIESPROP.getmodel(new DC_SUPPLIESPROP_SW { DC_SUPPLIESPROP_ID = m.SUPID }); m.DCSUPPROPUNIT = BaseDT.DC_SUPPLIESPROP.getunit(new DC_SUPPLIESPROP_SW { DC_SUPPLIESPROP_ID = m.SUPID }); m.REPID = dt.Rows[i]["REPID"].ToString(); m.DPNAME = DC_REPOSITORYCls.getdepotname(m.REPID); m.DCREPTIME = ClsSwitch.SwitDate(dt.Rows[i]["DCREPTIME"].ToString());; m.DCREPFLAG = dt.Rows[i]["DCREPFLAG"].ToString(); m.DCREPSUPCOUNT = dt.Rows[i]["DCREPSUPCOUNT"].ToString(); m.DCENTYMANID = dt.Rows[i]["DCENTYMANID"].ToString(); m.DCUSERID = dt.Rows[i]["DCUSERID"].ToString(); m.DCCUSTODIANID = dt.Rows[i]["DCCUSTODIANID"].ToString(); m.DCUSERORG = dt.Rows[i]["DCUSERORG"].ToString(); m.PRICE = dt.Rows[i]["PRICE"].ToString(); m.MARK = dt.Rows[i]["MARK"].ToString(); m.REPERTORYCOUNT = dt.Rows[i]["REPERTORYCOUNT"].ToString(); m.DCFAFANGREN = dt.Rows[i]["DCFAFANGREN"].ToString(); m.DCZHIBIAOREN = dt.Rows[i]["DCZHIBIAOREN"].ToString(); //m.DCENTYMANNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCENTYMANID); //m.DCUSERNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCUSERID); //m.DCCUSTODIANNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCCUSTODIANID); m.NUMBER = dt.Rows[i]["NUMBER"].ToString(); m.SUM = (float.Parse(m.DCREPSUPCOUNT) * float.Parse(m.PRICE)).ToString("F2"); result.Add(m); } dt.Clear(); dt.Dispose(); dtUser.Clear(); dtUser.Dispose(); return(result); }
/// <summary> /// 根据查询条件获取某一条信息记录 /// </summary> /// <param name="sw">参见模型</param> /// <returns>参见模型</returns> public static DC_DETAILS_Model getModel(DC_DETAILS_SW sw) { DataTable dt = BaseDT.DC_DETAILS.getDT(sw);//列表 DataTable dtUser = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { }); DC_DETAILS_Model m = new DC_DETAILS_Model(); if (dt.Rows.Count > 0) { int i = 0; m.DCDETAILSID = dt.Rows[i]["DCDETAILSID"].ToString(); m.SUPID = dt.Rows[i]["SUPID"].ToString(); m.SUPNAME = DC_SUPPLIESPROPCls.getsupname(m.SUPID); //获取物资的名称 m.REPID = dt.Rows[i]["REPID"].ToString(); m.DPNAME = DC_REPOSITORYCls.getdepotname(m.REPID); //获取仓库的名称 m.DCSUPPROPMODEL = BaseDT.DC_SUPPLIESPROP.getmodel(new DC_SUPPLIESPROP_SW { DC_SUPPLIESPROP_ID = m.SUPID }); //获取物资的型号 m.DCSUPPROPUNIT = BaseDT.DC_SUPPLIESPROP.getunit(new DC_SUPPLIESPROP_SW { DC_SUPPLIESPROP_ID = m.SUPID }); //获取物资的单位 m.RESPONSIBLEMAN = DC_REPOSITORYCls.getdepotman(m.REPID); //获取仓库负责人 m.DCREPTIME = ClsSwitch.SwitDate(dt.Rows[i]["DCREPTIME"].ToString());; m.DCREPFLAG = dt.Rows[i]["DCREPFLAG"].ToString(); m.DCREPSUPCOUNT = dt.Rows[i]["DCREPSUPCOUNT"].ToString(); m.DCENTYMANID = dt.Rows[i]["DCENTYMANID"].ToString(); m.DCUSERID = dt.Rows[i]["DCUSERID"].ToString(); m.DCCUSTODIANID = dt.Rows[i]["DCCUSTODIANID"].ToString(); m.DCUSERORG = dt.Rows[i]["DCUSERORG"].ToString(); m.PRICE = dt.Rows[i]["PRICE"].ToString(); m.MARK = dt.Rows[i]["MARK"].ToString(); m.REPERTORYCOUNT = dt.Rows[i]["REPERTORYCOUNT"].ToString(); m.DCFAFANGREN = dt.Rows[i]["DCFAFANGREN"].ToString(); m.DCZHIBIAOREN = dt.Rows[i]["DCZHIBIAOREN"].ToString(); m.NUMBER = dt.Rows[i]["NUMBER"].ToString(); //m.DCENTYMANNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCENTYMANID); //m.DCUSERNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCUSERID); //m.DCCUSTODIANNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DCCUSTODIANID); m.SUM = (float.Parse(m.DCREPSUPCOUNT) * float.Parse(m.PRICE)).ToString("F2");//金额 } dt.Clear(); dt.Dispose(); return(m); }
/// <summary> /// 获取物资名称+型号json /// </summary> /// <param name="sw"></param> /// <returns></returns> public static string getSupCKJsonStr(DC_DETAILS_SW sw) { DataTable dt = BaseDT.DC_DETAILS.getcombox(sw); char[] specialChars = new char[] { ',' }; string JSONstring = "["; for (int i = 0; i < dt.Rows.Count; i++) { string detailid = dt.Rows[i]["DCDETAILSID"].ToString(); string Id = dt.Rows[i]["SUPID"].ToString(); string Name = dt.Rows[i]["DCSUPPROPNAME"].ToString(); string Model = dt.Rows[i]["DCSUPPROPMODEL"].ToString(); string PRICE = dt.Rows[i]["PRICE"].ToString(); JSONstring += "{"; JSONstring += "\"id\":\"" + detailid + "\","; JSONstring += "\"text\":\"" + Name + "【" + Model + "】" + "¥" + PRICE + "\""; JSONstring += "},"; } JSONstring = JSONstring.TrimEnd(specialChars); JSONstring += "]"; return(JSONstring.ToString()); }
/// <summary> /// 获取数据 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getDT(DC_DETAILS_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" from DC_DETAILS a "); sb.AppendFormat("where 1=1"); if (string.IsNullOrEmpty(sw.DCDETAILSID) == false) { sb.AppendFormat("and DCDETAILSID = '{0}'", ClsSql.EncodeSql(sw.DCDETAILSID)); } //if (string.IsNullOrEmpty(sw.SUPID) == false) // sb.AppendFormat("and SUPID in (select SUPID from DC_SUPPLIESPROP where DCSUPPROPNAME like '%{0}%')", ClsSql.EncodeSql(sw.SUPNAME)); //if (string.IsNullOrEmpty(sw.REPID) == false) // sb.AppendFormat("and REPID = (select DCREPOSITORYID from DC_REPOSITORY where NAME ='{0}')", ClsSql.EncodeSql(sw.DPNAME)); if (string.IsNullOrEmpty(sw.SUPID) == false) { sb.AppendFormat("and SUPID = '{0}'", ClsSql.EncodeSql(sw.SUPID)); } if (string.IsNullOrEmpty(sw.REPID) == false) { sb.AppendFormat("and REPID = '{0}'", ClsSql.EncodeSql(sw.REPID)); } if (!string.IsNullOrEmpty(sw.DateBegin)) { sb.AppendFormat(" and DCREPTIME>='{0} 00:00:00'", sw.DateBegin); } if (!string.IsNullOrEmpty(sw.DateEnd)) { sb.AppendFormat(" and DCREPTIME<='{0} 23:59:59'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.DCREPFLAG) == false) { sb.AppendFormat("and DCREPFLAG = '{0}'", ClsSql.EncodeSql(sw.DCREPFLAG)); } //if (string.IsNullOrEmpty(sw.DCREPTIME) == false) // sb.AppendFormat("and DCREPTIME = '{0}'", ClsSql.EncodeSql(sw.DCREPTIME)); if (string.IsNullOrEmpty(sw.DCENTYMANID) == false) { sb.AppendFormat("and DCENTYMANID = '{0}'", ClsSql.EncodeSql(sw.DCENTYMANID)); } if (string.IsNullOrEmpty(sw.DCUSERID) == false) { sb.AppendFormat("and DCUSERID = '{0}'", ClsSql.EncodeSql(sw.DCUSERID)); } if (string.IsNullOrEmpty(sw.DCCUSTODIANID) == false) { sb.AppendFormat("and DCCUSTODIANID = '{0}'", ClsSql.EncodeSql(sw.DCCUSTODIANID)); } if (string.IsNullOrEmpty(sw.REPERTORYCOUNT) == false) { sb.AppendFormat("and REPERTORYCOUNT = '{0}'", ClsSql.EncodeSql(sw.REPERTORYCOUNT)); } if (string.IsNullOrEmpty(sw.NUMBER) == false) { sb.AppendFormat("and NUMBER = '{0}'", ClsSql.EncodeSql(sw.NUMBER)); } string sql = ("select DCDETAILSID,SUPID,REPID,DCREPTIME,DCREPFLAG,DCREPSUPCOUNT,DCENTYMANID,DCUSERID,DCCUSTODIANID,DCUSERORG,PRICE,MARK,REPERTORYCOUNT,DCZHIBIAOREN,DCFAFANGREN,NUMBER") + sb.ToString() + (" order by DCREPTIME desc "); DataSet ds = DataBaseClass.FullDataSet(sql); return(ds.Tables[0]); }
/// <summary> /// 获取分页数据 /// </summary> /// <param name="sw"></param> /// <param name="total"></param> /// <returns></returns> public static DataTable getDT(DC_DETAILS_SW sw, out int total) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" from DC_DETAILS a "); sb.AppendFormat("where 1=1"); if (string.IsNullOrEmpty(sw.DCDETAILSID) == false) { sb.AppendFormat("and DCDETAILSID = '{0}'", ClsSql.EncodeSql(sw.DCDETAILSID)); } if (string.IsNullOrEmpty(sw.SUPID) == false) { sb.AppendFormat("and SUPID = '{0}'", ClsSql.EncodeSql(sw.SUPID)); } if (string.IsNullOrEmpty(sw.REPID) == false) { sb.AppendFormat("and REPID = '{0}'", ClsSql.EncodeSql(sw.REPID)); } //if (string.IsNullOrEmpty(sw.SUPNAME) == false) // sb.AppendFormat("and SUPID in (select SUPID from DC_SUPPLIESPROP where DCSUPPROPNAME ='{0}')", ClsSql.EncodeSql(sw.SUPNAME)); //if (string.IsNullOrEmpty(sw.REPID) == false) // sb.AppendFormat("and REPID = (select DCREPOSITORYID from DC_REPOSITORY where NAME like '%{0}%)", ClsSql.EncodeSql(sw.DPNAME)); if (!string.IsNullOrEmpty(sw.DateBegin)) { sb.AppendFormat(" and DCREPTIME>='{0} 00:00:00'", sw.DateBegin); } if (!string.IsNullOrEmpty(sw.DateEnd)) { sb.AppendFormat(" and DCREPTIME<='{0} 23:59:59'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.DCREPFLAG) == false) { sb.AppendFormat("and DCREPFLAG = '{0}'", ClsSql.EncodeSql(sw.DCREPFLAG)); } if (string.IsNullOrEmpty(sw.DCREPSUPCOUNT) == false) { sb.AppendFormat("and DCREPSUPCOUNT = '{0}'", ClsSql.EncodeSql(sw.DCREPSUPCOUNT)); } if (string.IsNullOrEmpty(sw.DCENTYMANID) == false) { sb.AppendFormat("and DCENTYMANID = '{0}'", ClsSql.EncodeSql(sw.DCENTYMANID)); } if (string.IsNullOrEmpty(sw.DCUSERID) == false) { sb.AppendFormat("and DCUSERID = '{0}'", ClsSql.EncodeSql(sw.DCUSERID)); } if (string.IsNullOrEmpty(sw.DCCUSTODIANID) == false) { sb.AppendFormat("and DCCUSTODIANID = '{0}'", ClsSql.EncodeSql(sw.DCCUSTODIANID)); } if (string.IsNullOrEmpty(sw.REPERTORYCOUNT) == false) { sb.AppendFormat("and REPERTORYCOUNT = '{0}'", ClsSql.EncodeSql(sw.REPERTORYCOUNT)); } string sql = ("select DCDETAILSID,SUPID,REPID,DCREPTIME,DCREPFLAG,DCREPSUPCOUNT,DCENTYMANID,DCUSERID,DCCUSTODIANID,DCUSERORG,PRICE,MARK,REPERTORYCOUNT,DCZHIBIAOREN,DCFAFANGREN,NUMBER") + sb.ToString() + (" order by DCREPTIME desc "); string sqlC = "select count(1) " + sb.ToString(); total = int.Parse(DataBaseClass.ReturnSqlField(sqlC)); sw.curPage = PagerCls.getCurPage(new PagerSW { curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total }); DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a"); return(ds.Tables[0]); }
/// <summary> ///combox根据日期和物资名称排列 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getcombox(DC_DETAILS_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" from DC_DETAILS b left join DC_SUPPLIESPROP a on b.SUPID =a.DC_SUPPLIESPROP_ID "); sb.AppendFormat(" WHERE 1=1 "); if (string.IsNullOrEmpty(sw.DCDETAILSID) == false) { sb.AppendFormat("and b.DCDETAILSID = '{0}'", ClsSql.EncodeSql(sw.DCDETAILSID)); } if (string.IsNullOrEmpty(sw.SUPID) == false) { sb.AppendFormat("and b.SUPID = '{0}'", ClsSql.EncodeSql(sw.SUPID)); } if (string.IsNullOrEmpty(sw.REPID) == false) { sb.AppendFormat("and b.REPID = '{0}'", ClsSql.EncodeSql(sw.REPID)); } if (!string.IsNullOrEmpty(sw.DateBegin)) { sb.AppendFormat(" and b.DCREPTIME>='{0} 00:00:00'", sw.DateBegin); } if (!string.IsNullOrEmpty(sw.DateEnd)) { sb.AppendFormat(" and b.DCREPTIME<='{0} 23:59:59'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.DCREPFLAG) == false) { sb.AppendFormat("and b.DCREPFLAG = '{0}'", ClsSql.EncodeSql(sw.DCREPFLAG)); } if (string.IsNullOrEmpty(sw.DCENTYMANID) == false) { sb.AppendFormat("and b.DCENTYMANID = '{0}'", ClsSql.EncodeSql(sw.DCENTYMANID)); } if (string.IsNullOrEmpty(sw.DCUSERID) == false) { sb.AppendFormat("and b.DCUSERID = '{0}'", ClsSql.EncodeSql(sw.DCUSERID)); } if (string.IsNullOrEmpty(sw.DCCUSTODIANID) == false) { sb.AppendFormat("and b.DCCUSTODIANID = '{0}'", ClsSql.EncodeSql(sw.DCCUSTODIANID)); } if (string.IsNullOrEmpty(sw.REPERTORYCOUNT) == false) { sb.AppendFormat("and b.REPERTORYCOUNT = '{0}'", ClsSql.EncodeSql(sw.REPERTORYCOUNT)); } if (string.IsNullOrEmpty(sw.NUMBER) == false) { sb.AppendFormat("and b.NUMBER = '{0}'", ClsSql.EncodeSql(sw.NUMBER)); } if (string.IsNullOrEmpty(sw.SUPNAME) == false) { sb.AppendFormat("and a.DCSUPPROPNAME = '{0}'", ClsSql.EncodeSql(sw.SUPNAME)); } string sql = "select a.*,b.*" + sb.ToString() + " order by b.DCREPTIME,a.DCSUPPROPNAME DESC"; DataSet ds = DataBaseClass.FullDataSet(sql); return(ds.Tables[0]); }