public string UpInspectionRe(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat(" update inspection_report set certificate_expdate='{0}',certificate_desc='{1}',certificate_filename='{2}',m_user='******',m_date='{4}',sort='{5}' where rowID='{6}' ; ", CommonFunction.DateTimeToString(query.certificate_expdate), query.certificate_desc, query.certificate_filename, query.m_user, CommonFunction.DateTimeToString(query.m_date), query.sort, query.rowID);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->InsertInspectionRe-->" + sql.ToString() + ex.Message, ex);
}
}
public DataTable Export(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
query.Replace4MySQL();
try
{
sql.Append(" select insr.rowID, p.brand_id ,vb.brand_name,p.product_id,p.product_name,insr.certificate_type1, cc1.certificate_categorycode as 'code1',cc1.certificate_categoryname as 'certificate_type1_name', insr.certificate_type2,cc2.certificate_categorycode as 'code2' ,cc2.certificate_categoryname as 'certificate_type2_name', ");
sql.Append(" insr.certificate_expdate,insr.certificate_desc,insr.certificate_filename,mu1.user_username as'create_user',insr.k_date,mu2.user_username as 'update_user' ,insr.m_date ");
sqlFrom.Append(" from inspection_report insr ");
sqlFrom.Append(" LEFT JOIN product p on p.product_id=insr.product_id LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id ");
sqlFrom.Append(" LEFT JOIN manage_user mu1 on insr.k_user =mu1.user_id LEFT JOIN manage_user mu2 on insr.m_user=mu2.user_id ");
sqlFrom.Append(" LEFT JOIN certificate_category cc1 on cc1.rowID=insr.certificate_type1 LEFT JOIN certificate_category cc2 on cc2.rowID=insr.certificate_type2 ");
sqlWhere.Append(" where 1=1 ");
if (query.brand_id != 0)
{
sqlWhere.AppendFormat(" and p.brand_id='{0}' ", query.brand_id);
}
if (query.name_code != "")
{
sqlWhere.AppendFormat(" and ( p.product_id='{0}' or p.product_name like N'%{0}%' ) ", query.name_code);
}
if (query.certificate_type1 != "")
{
sqlWhere.AppendFormat(" and insr.certificate_type1='{0}' ", query.certificate_type1);
}
if (query.certificate_type2 != "")
{
sqlWhere.AppendFormat(" and insr.certificate_type2='{0}' ", query.certificate_type2);
}
if (query.search_date != 0)
{
sqlWhere.AppendFormat(" and insr.certificate_expdate >='{0}' and insr.certificate_expdate <='{1}' ", CommonFunction.DateTimeToString(query.start_time), CommonFunction.DateTimeToString(query.end_time));
}
if (query.last_day != 0)
{
sqlWhere.AppendFormat(" and insr.certificate_expdate<='{0}' and insr.certificate_expdate>='{1}' ", CommonFunction.DateTimeToString(DateTime.Now.AddDays(query.last_day)), DateTime.Now);
}
sqlWhere.AppendFormat(" order by insr.rowID desc; ");
sql.Append(sqlFrom.ToString() + sqlWhere.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->Export-->" + sql.ToString() + ex.Message, ex);
}
}
public string InsertInspectionRe(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.Append(" insert into inspection_report(product_id,certificate_type1,certificate_type2,certificate_expdate,certificate_desc,certificate_filename,k_user,k_date,m_user,m_date,sort) ");
sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}', ", query.product_id, query.certificate_type1, query.certificate_type2, CommonFunction.DateTimeToString(query.certificate_expdate), query.certificate_desc);
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}'); ", query.certificate_filename, query.k_user,CommonFunction.DateTimeToString(query.k_date), query.m_user,CommonFunction.DateTimeToString(query.m_date),query.sort);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->InsertInspectionRe-->" + sql.ToString() + ex.Message, ex);
}
}
public List<InspectionReportQuery> InspectionReportList(InspectionReportQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
query.Replace4MySQL();
totalCount = 0;
try
{
sql.Append(" select insr.rowID, p.brand_id ,vb.brand_name,p.product_id,p.product_name,insr.certificate_type1,cc1.certificate_categoryname as 'certificate_type1_name', insr.certificate_type2,cc2.certificate_categoryname as 'certificate_type2_name',insr.sort, ");
sql.Append(" insr.certificate_expdate,insr.certificate_desc,insr.certificate_filename,mu1.user_username as'create_user',insr.k_date,mu2.user_username as 'update_user' ,insr.m_date ");
sqlFrom.Append(" from inspection_report insr ");
sqlFrom.Append(" LEFT JOIN product p on p.product_id=insr.product_id LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id ");
sqlFrom.Append(" LEFT JOIN manage_user mu1 on insr.k_user =mu1.user_id LEFT JOIN manage_user mu2 on insr.m_user=mu2.user_id ");
sqlFrom.Append(" LEFT JOIN certificate_category cc1 on cc1.rowID=insr.certificate_type1 LEFT JOIN certificate_category cc2 on cc2.rowID=insr.certificate_type2 ");
sqlWhere.Append(" where 1=1 ");
if (query.brand_id != 0)
{
sqlWhere.AppendFormat(" and p.brand_id='{0}' ",query.brand_id);
}
if (query.name_code !="")
{
sqlWhere.AppendFormat(" and ( p.product_id='{0}' or p.product_name like N'%{0}%' ) ", query.name_code);
}
if (query.certificate_type1 != "")
{
sqlWhere.AppendFormat(" and insr.certificate_type1='{0}' ", query.certificate_type1);
}
if (query.certificate_type2 != "")
{
sqlWhere.AppendFormat(" and insr.certificate_type2='{0}' ", query.certificate_type2);
}
if (query.search_date != 0)
{
sqlWhere.AppendFormat(" and insr.certificate_expdate >='{0}' and insr.certificate_expdate <='{1}' ", CommonFunction.DateTimeToString(query.start_time), CommonFunction.DateTimeToString(query.end_time));
}
if (query.last_day != 0)
{
sqlWhere.AppendFormat(" and insr.certificate_expdate<='{0}' and insr.certificate_expdate>='{1}' ", CommonFunction.DateTimeToString(DateTime.Now.AddDays(query.last_day)),DateTime.Now);
}
if (query.IsPage)
{
sqlCount.Append(" select count( insr.rowID) as 'totalCount' " + sqlFrom.ToString()+sqlWhere.ToString());
DataTable _dt = _access.getDataTable(sqlCount.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
}
sqlWhere.AppendFormat(" order by insr.rowID desc limit {0},{1}; ",query.Start,query.Limit);
sql.Append(sqlFrom.ToString()+sqlWhere.ToString());
return _access.getDataTableForObj<InspectionReportQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->InspectionReportList-->"+sql.ToString()+ex.Message,ex);
}
}