/// <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]);
}
