/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "user_oauth_app");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.article_attach GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,article_id,file_name,file_path,file_size,file_ext,down_num,point,add_time from " + databaseprefix + "article_attach ");
strSql.Append(" where id=@id");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
Model.article_attach model = new Model.article_attach();
DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["article_id"].ToString() != "")
{
model.article_id = int.Parse(ds.Tables[0].Rows[0]["article_id"].ToString());
}
model.file_name = ds.Tables[0].Rows[0]["file_name"].ToString();
model.file_path = ds.Tables[0].Rows[0]["file_path"].ToString();
if (ds.Tables[0].Rows[0]["file_size"].ToString() != "")
{
model.file_size = int.Parse(ds.Tables[0].Rows[0]["file_size"].ToString());
}
model.file_ext = ds.Tables[0].Rows[0]["file_ext"].ToString();
if (ds.Tables[0].Rows[0]["down_num"].ToString() != "")
{
model.down_num = int.Parse(ds.Tables[0].Rows[0]["down_num"].ToString());
}
if (ds.Tables[0].Rows[0]["point"].ToString() != "")
{
model.point = int.Parse(ds.Tables[0].Rows[0]["point"].ToString());
}
if (ds.Tables[0].Rows[0]["add_time"].ToString() != "")
{
model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 获得出库指令明细
/// </summary>
public DataSet GetCKZLMX(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select CKZLBH,CKRQ,CLBH,PM,PH,KHBH,ZF,DJ,ZSSLHJ,ZSXS,ZSDDS,ZSXDS,CKZLXDZ,CKDDZ,CKWCS,WCQF,BZ,DMQF,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM,CKZLRQ ");
strSql.Append(" FROM ffd050 ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by CKRQ desc,PM,PH,CLBH");
return(DbHelperMySql.Query(strSql.ToString()));
}
/// <summary>
/// 得到配合表
/// </summary>
/// <param name="CLBH">材料编号</param>
/// <returns></returns>
public ArrayList GetPhbs(string CLBH)
{
ArrayList lstPhb = new ArrayList();
string strSql = "select PHBBH from FMD070 where CLBH='" + CLBH + "'";
DataTable myDt = DbHelperMySql.Query(strSql).Tables[0];
for (int i = 0; i < myDt.Rows.Count; i++)
{
lstPhb.Add(myDt.Rows[i]["PHBBH"]);
}
return(lstPhb);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,title,express_code,express_fee,website,remark,sort_id,is_lock ");
strSql.Append(" FROM " + databaseprefix + "express ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by sort_id asc,id desc");
return(DbHelperMySql.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,title,img_url,remark,type,poundage_type,poundage_amount,sort_id,is_lock,api_path ");
strSql.Append(" FROM " + databaseprefix + "payment ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by sort_id asc,id desc");
return(DbHelperMySql.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select PHBBH,YCLBH,JLDW,ZL,GC,XSS,BZ,XS,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" FROM fmd080 ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by XSS asc ");
return(DbHelperMySql.Query(strSql.ToString()));
}
public DataTable GetDaTaForPC08Spread(string PM, string PH, string CLBH, string FLH, string ECLH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("a.PM, ");
strSql.Append("a.PH, ");
strSql.Append("a.CLBH, ");
strSql.Append("a.JLDW JLDW_BH, ");
strSql.Append("b.ZSMC JLDW, ");
strSql.Append("c.ZSMC FLH, ");
strSql.Append("a.ZXKC, ");
strSql.Append("a.ZDKC, ");
strSql.Append("a.CPMS, ");
strSql.Append("a.XDSLBZ, ");
strSql.Append("a.DDSLBZ, ");
strSql.Append("d.ZSMC XQF, ");
strSql.Append("a.XSLBZ, ");
strSql.Append("a.ZXBZ , case IFNULL(a.RCLH,'0') when '1' then '是' when '0' then '否' end as ECLH ");
strSql.Append("FROM ");
strSql.Append("fmd060 a ");
strSql.Append("LEFT JOIN (SELECT ZSMC,MCKEY FROM fmd000 WHERE glbh='06') ");
strSql.Append("b ON (a.JLDW=b.MCKEY) ");
strSql.Append("LEFT JOIN (SELECT ZSMC,MCKEY FROM fmd000 WHERE glbh='05') c on (a.FLH=c.MCKEY) ");
strSql.Append("LEFT JOIN (SELECT ZSMC,MCKEY FROM fmd000 WHERE glbh='10') d on (a.XQF=d.MCKEY) ");
strSql.Append(" where 1=1 ");
if (string.IsNullOrEmpty(PM) == false)
{
strSql.Append(" and PM like '%" + PM + "%'");
}
if (string.IsNullOrEmpty(PH) == false)
{
strSql.Append(" and PH like '%" + PH + "%'");
}
if (string.IsNullOrEmpty(CLBH) == false)
{
strSql.Append(" and CLBH like '%" + CLBH + "%'");
}
if (string.IsNullOrEmpty(FLH) == false)
{
strSql.Append(" and FLH like '%" + FLH + "%'");
}
if (string.IsNullOrEmpty(ECLH) == false)
{
strSql.Append("and IFNULL(a.RCLH,'0')='" + ECLH + "' ");
}
strSql.Append(" order by a.PM,a.PH,a.CLBH ");
return(DbHelperMySql.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 编辑扩展字段及频道数据表
/// </summary>
private void FieldUpdate(MySqlConnection conn, MySqlTransaction trans, Model.site_channel newModel, Model.site_channel oldModel)
{
if (newModel.channel_fields != null)
{
string newFieldIds = string.Empty; //用来存储新增的字段ID
//添加扩展字段
StringBuilder strSql1;
foreach (Model.site_channel_field modelt in newModel.channel_fields)
{
strSql1 = new StringBuilder();
Model.site_channel_field fieldModel = null;
if (oldModel.channel_fields != null)
{
fieldModel = oldModel.channel_fields.Find(p => p.field_id == modelt.field_id); //查找是否已经存在
}
if (fieldModel == null) //如果不存在则添加
{
newFieldIds += modelt.field_id + ","; //以逗号分隔开存储
strSql1.Append("insert into " + databaseprefix + "site_channel_field(");
strSql1.Append("channel_id,field_id)");
strSql1.Append(" values (");
strSql1.Append("@channel_id,@field_id)");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@field_id", MySqlDbType.Int32, 4)
};
parameters1[0].Value = modelt.channel_id;
parameters1[1].Value = modelt.field_id;
DbHelperMySql.ExecuteSql(conn, trans, strSql1.ToString(), parameters1);
}
}
//添加频道数据表列
if (newFieldIds.Length > 0)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("select id,[name],data_type from " + databaseprefix + "article_attribute_field");
strSql2.Append(" where id in(" + newFieldIds.TrimEnd(',') + ")");
DataSet ds = DbHelperMySql.Query(conn, trans, strSql2.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
DbHelperMySql.ExecuteSql(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + " add " + dr["name"].ToString() + " " + dr["data_type"].ToString());
}
}
}
//如果频道名称改变则需要更改数据表名
if (newModel.name != oldModel.name)
{
DbHelperMySql.ExecuteSql(conn, trans, "exec sp_rename '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + "', '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + newModel.name + "'");
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.manager GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,role_id,role_type,user_name,password,salt,real_name,telephone,email,is_lock,add_time from " + databaseprefix + "manager ");
strSql.Append(" where id=@id");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
Model.manager model = new Model.manager();
DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["role_id"].ToString() != "")
{
model.role_id = int.Parse(ds.Tables[0].Rows[0]["role_id"].ToString());
}
if (ds.Tables[0].Rows[0]["role_type"].ToString() != "")
{
model.role_type = int.Parse(ds.Tables[0].Rows[0]["role_type"].ToString());
}
model.user_name = ds.Tables[0].Rows[0]["user_name"].ToString();
model.password = ds.Tables[0].Rows[0]["password"].ToString();
model.salt = ds.Tables[0].Rows[0]["salt"].ToString();
model.real_name = ds.Tables[0].Rows[0]["real_name"].ToString();
model.telephone = ds.Tables[0].Rows[0]["telephone"].ToString();
model.email = ds.Tables[0].Rows[0]["email"].ToString();
if (ds.Tables[0].Rows[0]["is_lock"].ToString() != "")
{
model.is_lock = int.Parse(ds.Tables[0].Rows[0]["is_lock"].ToString());
}
if (ds.Tables[0].Rows[0]["add_time"].ToString() != "")
{
model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
}
return(model);
}
else
{
return(null);
}
}
public DataTable Getsub3Fmd060(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.khbh KHBH,b.KHMC KHMC,a.PMGC PMGC, ");
strSql.Append("a.PHGC PHGC,a.PMKH PMKH,a.PHKH PHKH from fmd100 a ");
strSql.Append(" left join fmd030 b on a.khbh = b.khbh ");
if (strWhere.Trim() != "")
{
strSql.Append("where b.KHMC = '" + strWhere + "' ");
}
return(DbHelperMySql.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 获得频道对应的数据
/// </summary>
public DataSet GetList(int channel_id, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.* ");
strSql.Append(" FROM " + databaseprefix + "article_attribute_field as A INNER JOIN " + databaseprefix + "site_channel_field as S ON A.id=S.field_id");
strSql.Append(" where S.channel_id=" + channel_id);
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
strSql.Append(" order by A.is_sys desc,A.sort_id asc,A.id desc");
return(DbHelperMySql.Query(strSql.ToString()));
}
public string Get_YCLLX(string YCLBH)
{
string strSql = "select YCLBHBS,YCLLX,b.ZSMC ZSMC from fmd040 a LEFT JOIN fmd000 b ON (a.YCLLX=b.MCKEY and b.GLBH='03') WHERE a.YCLBHBS='" + YCLBH + "'";
DataTable myDt = DbHelperMySql.Query(strSql.ToString()).Tables[0];
if (myDt.Rows.Count > 0)
{
return(myDt.Rows[0]["ZSMC"].ToString());
}
else
{
return("");
}
}
public DataTable GetSpdData(string rqS, string rqE, string CLBH, string PHBBH, string ZCR, string PZR)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.CLBH,a.PHBBH,a.ZDRQ,YCLBH, c.ZSMC JLDW,b.ZL,b.GC, d.symc SYBH,e.symc PZRBH,a.BZ,a.ZT FROM ");
strSql.Append(" fmd070 a LEFT JOIN ");
strSql.Append(" (SELECT * FROM fmd080 ) b ");
strSql.Append(" ON (a.PHBBH=b.PHBBH) ");
strSql.Append(" LEFT JOIN (select MCKEY,ZSMC from fmd000 where glbh='06')c on (b.jldw=c.MCKEY) ");
strSql.Append(" left join (SELECT sybh,symc from fmd010 ) d on (a.SYBH=d.sybh) ");
strSql.Append(" left join (SELECT sybh,symc from fmd010 ) e on (a.PZRBH=e.sybh) ");
strSql.Append(" where 1=1 ");
if (string.IsNullOrEmpty(rqS) == false)
{
strSql.Append(" and a.ZDRQ>='" + rqS + "' ");
}
if (string.IsNullOrEmpty(rqE) == false)
{
strSql.Append(" and a.ZDRQ<='" + rqE + "' ");
}
if (string.IsNullOrEmpty(CLBH) == false)
{
strSql.Append(" and a.CLBH like '%" + CLBH + "%' ");
}
if (string.IsNullOrEmpty(PHBBH) == false)
{
strSql.Append(" and a.PHBBH like'%" + PHBBH + "%' ");
}
if (string.IsNullOrEmpty(ZCR) == false)
{
strSql.Append(" and a.SYBH ='" + ZCR + "' ");
}
if (string.IsNullOrEmpty(PZR) == false)
{
strSql.Append(" and a.PZRBH ='" + PZR + "' ");
}
strSql.Append(" ORDER BY a.CLBH,a.PHBBH,a.ZDRQ, b.xss");
return(DbHelperMySql.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 得到原材料明细
/// </summary>
/// <param name="YCLBH"></param>
/// <returns></returns>
public DataTable GetYCLBH_MX(string YCLBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ");
strSql.Append(" date_add(func_get_split_string('" + YCLBH + "','_',4),INTERVAL YXTS DAY) as 使用期限 , ");
strSql.Append(" func_get_split_string('" + YCLBH + "','_',3) as 原材料编号, ");
strSql.Append(" '辅料' as 原材料类型, ");
strSql.Append(" '" + YCLBH + "' as 入桶单号 ");
strSql.Append(" from FMD040 where YCLBHBS=func_get_split_string('" + YCLBH + "','_',3); ");
DataTable myDt = DbHelperMySql.Query(strSql.ToString()).Tables[0];
return(myDt);
}
/// <summary>
/// 获得出库指令明细
/// </summary>
public DataSet GetCKZLMXEXCEL(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select CKZLBH,CKZLRQ,b.KHMC,PM,PH,CLBH,ZSSLHJ,DJ,ZSSLHJ*DJ,");
strSql.Append(" case when WCQF = '0' then '未出库' when WCQF = '1' then '已出库' end WCQF ");
strSql.Append(" FROM ffd050 a left join fmd030 b on b.khbh = a.khbh ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by CKRQ desc,PM,PH,CLBH");
return(DbHelperMySql.Query(strSql.ToString()));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM(");
strSql.Append("select S.*,A.title as ptitle,A.img_url,A.remark,A.api_path,A.is_lock");
strSql.Append(" from " + databaseprefix + "oauth_app as A INNER JOIN " + databaseprefix + "site_oauth as S ON A.id=S.oauth_id");
strSql.Append(") as temp_oauth");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 根据管理编号和名称Key取到正式名称
/// </summary>
/// <param name="GLBH"></param>
/// <param name="MCKEY"></param>
/// <returns></returns>
public string getZSMC(string GLBH, string MCKEY)
{
string strResult = string.Empty;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ZSMC FROM FMD000 WHERE GLBH='" + GLBH + "' AND MCKEY='" + MCKEY + "' ");
DataTable dTableZsmc = new DataTable();
dTableZsmc = DbHelperMySql.Query(strSql.ToString()).Tables[0];
if (dTableZsmc.Rows.Count > 0)
{
strResult = dTableZsmc.Rows[0]["ZSMC"].ToString();
}
return(strResult);
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM(");
strSql.Append("select S.*,P.img_url,P.title as ptitle,P.remark,P.type,P.poundage_type,P.poundage_amount,P.redirect_url,P.return_url,P.notify_url,P.is_lock");
strSql.Append(" from " + databaseprefix + "payment as P INNER JOIN " + databaseprefix + "site_payment as S ON P.id=S.payment_id");
strSql.Append(") as temp_payment");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 权限 获取用户名密码
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetQuanxian_SpdGetXM(string DMQF)
{
StringBuilder StrSQL = new StringBuilder();
// StrSQL.Append("SELECT DISTINCT a.USERNAME,b.XM,a.PASSWORD,b.TSRQ FROM FMD020 a LEFT JOIN FMD310 b ON a.USERNAME = b.SYBH ");
//// StrSQL.Append(" where b.scqf <>'1' ");
// StrSQL.Append("ORDER BY IFNULL(b.TSRQ,''),a.USERNAME ");
StrSQL.Append("SELECT DISTINCT a.USERNAME,b.SYMC XM,a.PASSWORD,ZT TSQF FROM FMD020 a ");
StrSQL.Append("LEFT JOIN FMD010 b ON a.USERNAME = b.SYBH where a.DMQF='" + DMQF + "' ORDER BY a.USERNAME ");
return(DbHelperMySql.Query(StrSQL.ToString()).Tables[0]);
}
/// <summary>
/// 获取站点未添加数据
/// </summary>
public DataSet GetList(int site_id, int oauth_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "oauth_app");
strSql.Append(" where is_lock=0 and id not in(");
strSql.Append("select oauth_id from " + databaseprefix + "site_oauth where site_id=" + site_id);
if (oauth_id > 0)
{
strSql.Append(" and oauth_id<>" + oauth_id);
}
strSql.Append(")");
strSql.Append(" order by sort_id asc,id desc");
return(DbHelperMySql.Query(strSql.ToString()));
}
/// <summary>
/// cpj
/// 传 日期时间区分 返回日期或者返回时间
/// </summary>
/// <param name="QF">取值： ComConst.Date 或者 ComConst.Time</param>
/// <returns></returns>
public static string GetSystemDateTime(string QF, string dateStyle)
{
string strSystemDT = string.Empty;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("CALL getsysDateTime()");
DataTable dt = DbHelperMySql.Query(strSql.ToString()).Tables[0];
if (dt.Rows.Count > 0)
{
if (QF.Equals(ComConst.Date))
{
if (dateStyle.Equals(ComConst.dateStyle_YMD))
{
strSystemDT = DateTime.Parse(dt.Rows[0][0].ToString()).ToString("yyyy^MM^dd").Replace("^", "");
}
else if (dateStyle.Equals(ComConst.dateStyle_Y_M_D))
{
strSystemDT = DateTime.Parse(dt.Rows[0][0].ToString()).ToString("yyyy^MM^dd").Replace("^", "/");
}
}
else
{
strSystemDT = dt.Rows[0][1].ToString();
}
return(strSystemDT);
}
else
{
return(strSystemDT);
}
}
catch (Exception ex)
{
if (QF.Equals(ComConst.Date))
{
strSystemDT = DateTime.Parse(System.DateTime.Now.ToShortDateString()).ToString(dateStyle);
}
else
{
strSystemDT = System.DateTime.Now.ToShortTimeString();
}
ComForm.InsertErrLog(ex.ToString(), "GetSystemDateTime");
return(strSystemDT);
}
}
/// <summary>
/// 得到合格证明细(BSR)
/// </summary>
/// <param name="qrCode">条形码</param>
/// <param name="checkDt">检品日期</param>
/// <param name="checkUser">检品人</param>
/// <returns></returns>
public DataTable GetHgzBsrMX(string qrCode, string strKHBH)
{
DataTable mydt = new DataTable();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("case ");
strSql.Append(" when ifnull(z.PHKH,'')!='' then z.PHKH ");
strSql.Append(" else c.PH ");
strSql.Append("end as '品号', ");
strSql.Append("case ");
strSql.Append(" when ifnull(z.PMKH,'')!='' then z.PMKH ");
strSql.Append(" else c.PM ");
strSql.Append("end as '品名', ");
strSql.Append("substring_index(substring_index(a.SSXDLOTNO,',',1),'_',-2) as 'LOTNo.', ");
strSql.Append("CONCAT(cast(sum(a.SL) as SIGNED),'个') as '数量' , ");
strSql.Append("case ");
strSql.Append(" when ifnull(z.CLBHKH,'')!='' then z.CLBHKH ");
strSql.Append(" else c.CLBH ");
strSql.Append("end as '材质', ");
//strSql.Append("c.CLBH as '材质', ");
strSql.Append("DATE_FORMAT(c.JPRQ,'%Y年%m月%d日') as '检查日',a.JPRBH as '检品人' ");
strSql.Append("FROM `ffd020` a ");
strSql.Append("left join `ffd010` c ON(a.id=c.id) ");
strSql.Append("left join `fmd100` z ON(KHBH='" + strKHBH + "' and (c.PM = z.PMGC and c.PH = z.PHGC and c.CLBH = z.CLBHGC)) ");
strSql.Append("where a.SSXDLOTNO='" + qrCode + "' ");
//strSql.Append("SELECT c.PH as '品号',c.PM as '品名', ");
//strSql.Append("substring_index(substring_index(a.SSXDLOTNO,',',1),'_',-2) as 'LOTNo.', ");
//strSql.Append("CONCAT(cast(a.SL as SIGNED),'个') as '数量' ,c.CLBH as '材质', ");
//strSql.Append("DATE_FORMAT(c.JPRQ,'%Y年%m月%d日') as '检查日',a.JPRBH as '检品人' FROM `ffd020` a ");
//strSql.Append("left join `ffd010` c ON(a.id=c.id) ");
//strSql.Append("where a.SSXDLOTNO='" + qrCode + "' ");
//strSql.Append("SELECT c.PH as '品号',c.PM as '品名',a.SSXDLOTNO as 'LOTNo.',CONCAT(((b.SL) as SIGNED),'个') as '数量' ,c.CLBH as '材质', ");
//strSql.Append("DATE_FORMAT(c.JPRQ,'%Y年%m月%d日') as '检查日',d.SYMC as '检品人' ");
//strSql.Append("FROM `ffd020` a ");
//strSql.Append("LEFT JOIN `ffd020` b ON(a.id=b.id) ");
//strSql.Append("left join `ffd010` c ON(a.id=c.id) ");
//strSql.Append("left join `fmd010` d ON(a.JPRBH=d.SYBH) ");
//strSql.Append("where a.SSXDLOTNO='" + qrCode + "' ");
mydt = DbHelperMySql.Query(strSql.ToString()).Tables[0];
return(mydt);
}
/// <summary>
/// »ñµÃÊý¾ÝÁбí
/// </summary>
public List <Model.article_albums> GetList(int article_id)
{
List <Model.article_albums> modelList = new List <Model.article_albums>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,article_id,thumb_path,original_path,remark,add_time ");
strSql.Append(" FROM " + databaseprefix + "article_albums ");
strSql.Append(" where article_id=" + article_id);
DataTable dt = DbHelperMySql.Query(strSql.ToString()).Tables[0];
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
Model.article_albums model;
for (int n = 0; n < rowsCount; n++)
{
model = new Model.article_albums();
if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
{
model.id = int.Parse(dt.Rows[n]["id"].ToString());
}
if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
{
model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
}
if (dt.Rows[n]["thumb_path"] != null && dt.Rows[n]["thumb_path"].ToString() != "")
{
model.thumb_path = dt.Rows[n]["thumb_path"].ToString();
}
if (dt.Rows[n]["original_path"] != null && dt.Rows[n]["original_path"].ToString() != "")
{
model.original_path = dt.Rows[n]["original_path"].ToString();
}
if (dt.Rows[n]["remark"] != null && dt.Rows[n]["remark"].ToString() != "")
{
model.remark = dt.Rows[n]["remark"].ToString();
}
if (dt.Rows[0]["add_time"].ToString() != "")
{
model.add_time = DateTime.Parse(dt.Rows[0]["add_time"].ToString());
}
modelList.Add(model);
}
}
return(modelList);
}
public DataTable GetYCLBH()
{
DataTable dt = new DataTable();
try
{
string RQ_sql = "";
RQ_sql = "select DISTINCT YCLBHBS ZSMC, YCLBHBS MCKEY from FMD040 where zt='0' AND YCLLX in ('001','003') order by YCLBHBS ";
dt = DbHelperMySql.Query(RQ_sql).Tables[0];
}
catch (Exception ex)
{
ex.ToString();
}
return(dt);
}
public string GetFMD030MC_KEY(string GLBH, string ZSMC)
{
string dt = "";
try
{
string RQ_sql = "";
RQ_sql = "select DISTINCT MCKEY from FMD000 WHERE GLBH='" + GLBH + "' and ZSMC='" + ZSMC + "' order by MCKEY ";
dt = DbHelperMySql.Query(RQ_sql).Tables[0].Rows[0][0].ToString();
}
catch (Exception ex)
{
ex.ToString();
}
return(dt);
}
/// <summary>
/// 权限表去用户权限
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetQuanxian_YongHu(Model.fmd020 model)
{
StringBuilder StrSQL = new StringBuilder();
if (model.DMQF.Equals("0") == true)
{
StrSQL.Append("select a.pgid,b.p_name from FMD020 a left join Project b on a.pgid = b.p_porname ");
StrSQL.Append("where a.username = '******' and a.DMQF='" + model.DMQF + "'");
}
else
{
StrSQL.Append(" select a.pgid,b.M_NAME p_name from FMD020 a left join menupro b on a.pgid = b.M_ID ");
StrSQL.Append(" where a.username = '******' and a.DMQF='" + model.DMQF + "' ");
}
return(DbHelperMySql.Query(StrSQL.ToString()).Tables[0]);
}
/// <summary>
/// 取得指定类别下的列表(一层)
/// </summary>
/// <param name="top">显示条数</param>
/// <param name="parent_id">父ID</param>
/// <param name="channel_id">频道ID</param>
/// <returns>DataTable</returns>
public DataTable GetChildList(int top, int parent_id, int channel_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (top > 0)
{
strSql.Append(" top " + top.ToString());
}
strSql.Append(" * ");
strSql.Append(" from " + databaseprefix + "article_category");
strSql.Append(" where channel_id=" + channel_id + " and parent_id=" + parent_id + " order by sort_id asc,id desc");
DataSet ds = DbHelperMySql.Query(strSql.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, string orderby)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select CLBH,PHBBH,ZDRQ,ZT,BL,SYBH,PZRBH,BZ,PHBN,ZF,XS,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" FROM fmd070 ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by " + orderby);
}
return(DbHelperMySql.Query(strSql.ToString()));
}
public DataTable GetFMD030MC_KEY(string GLBH)
{
DataTable dt = new DataTable();
try
{
string RQ_sql = "";
RQ_sql = "select DISTINCT ZSMC,MCKEY from FMD000 WHERE GLBH='" + GLBH + "' order by MCKEY ";
dt = DbHelperMySql.Query(RQ_sql).Tables[0];
}
catch (Exception ex)
{
ex.ToString();
}
return(dt);
}