public HttpResponseBase InspectionReportList()
{
string json = string.Empty;
try
{
InspectionReportQuery query = new InspectionReportQuery();
List<InspectionReportQuery> store = new List<InspectionReportQuery>();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["brand"]))
{
query.brand_id = Convert.ToUInt32(Request.Params["brand"]);
}
if (!string.IsNullOrEmpty(Request.Params["name_code"]))
{
query.name_code = Request.Params["name_code"].ToString().Trim();
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type1"]))
{
query.certificate_type1 = (Request.Params["certificate_type1"]);
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type2"]))
{
query.certificate_type2 = (Request.Params["certificate_type2"]);
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time =Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["last_day"]))
{
query.last_day = Convert.ToInt32(Request.Params["last_day"]);
}
if (!string.IsNullOrEmpty(Request.Params["search_date"]))
{
query.search_date = Convert.ToInt32(Request.Params["search_date"]);
}
_inspectionReport = new InspectionReportMgr(mySqlConnectionString);
store = _inspectionReport.InspectionReportList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public bool CheckInspectionReport(InspectionReportQuery query)
{
List<InspectionReportQuery> list = new List<Model.Query.InspectionReportQuery>();
try
{
list = _inspectionReport.CheckInspectionReport(query);
if (list != null && list.Count > 0)
{
return true;
}
else {
return false;
}
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->CheckInspectionReport-->" + ex.Message, ex);
}
}
public bool UpdateInspectionReport(InspectionReportQuery query)
{
try
{
if (_inspectionReport.UpdateInspectionReport(query) > 0)
{
return true;
}
else {
return false;
}
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->UpdateInspectionReport-->" + ex.Message, ex);
}
}
public bool IsExist(InspectionReportQuery query)
{
string json = string.Empty;
bool b = true;
try
{
DataTable _dt = _inspectionReport.IsExist(query);
if (_dt.Rows.Count > 0)
{
b = true;
}
else
{
b = false;
}
return b;
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->IsExist-->"+ex.Message,ex);
}
}
public string GetType2(InspectionReportQuery query)
{
try
{
return _inspectionReport.GetType2(query);
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->GetType2-->" + ex.Message, ex);
}
}
public DataTable Export(InspectionReportQuery query)
{
try
{
return _inspectionReport.Export(query);
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->SaveInspectionRe-->" + ex.Message, ex);
}
}
public string GetType2Folder(InspectionReportQuery query)
{
string folder = string.Empty;
try
{
folder = _inspectionReport.GetType2Folder(query).ToString() + "/";
return folder;
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->GetTypeFolder-->" + ex.Message, ex);
}
}
public string DeleteInspectionRe(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("delete from inspection_report where rowID='{0}';",query.rowID);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->DeleteInspectionRe-->" + sql.ToString() + ex.Message, ex);
}
}
public InspectionReportQuery oldQuery(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select certificate_filename from inspection_report where rowID='{0}';",query.rowID);
return _access.getSinggleObj<InspectionReportQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->oldQuery-->" + 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 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 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);
}
}
public HttpResponseBase SaveInspectionRe()
{
string json = "{success:'false',msg:'5'}";
try
{
_inspectionReport = new InspectionReportMgr(mySqlConnectionString);
InspectionReportQuery query = new InspectionReportQuery();
InspectionReportQuery oldQuery = new InspectionReportQuery();
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
query.product_id = Convert.ToUInt32(Request.Params["product_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.brand_id = Convert.ToUInt32(Request.Params["brand_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type1"]))
{
query.certificate_type1 = Request.Params["certificate_type1"];
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type2"]))
{
query.certificate_type2 = Request.Params["certificate_type2"];
}
if (!string.IsNullOrEmpty(Request.Params["certificate_expdate"]))
{
query.certificate_expdate = Convert.ToDateTime(Request.Params["certificate_expdate"]);
}
if (!string.IsNullOrEmpty(Request.Params["certificate_desc"]))
{
query.certificate_desc = Request.Params["certificate_desc"];
}
if (!string.IsNullOrEmpty(Request.Params["certificate_filename"]))
{
query.certificate_filename = Request.Params["certificate_filename"];
}
if (!string.IsNullOrEmpty(Request.Params["rowID"]))
{
query.rowID = Convert.ToInt32(Request.Params["rowID"]);
oldQuery = _inspectionReport.oldQuery(query);
}
if (!string.IsNullOrEmpty(Request.Params["sort"]))
{
query.sort = Convert.ToInt32(Request.Params["sort"]);
}
if (!string.IsNullOrEmpty(Request.Params["old_sort"]))
{
query.old_sort = Convert.ToInt32(Request.Params["old_sort"]);
}
query.k_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
query.m_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
#region 排序是否重複
if (_inspectionReport.IsSortExist(query) || query.old_sort == query.sort)//sort不重複
{
if (!_inspectionReport.IsExist(query))
{
HttpPostedFileBase picFile = Request.Files["certificate_filename"];
//檢查上傳的圖片名稱和拼出來的圖片名稱是否一樣，不一樣則不能上傳
//拼成圖片名稱
#region 上傳圖片
try
{
string path = Server.MapPath(xmlPath);
SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path);
SiteConfig extention_config = _siteConfigMgr.GetConfigByName("PIC_Extention_Format");
SiteConfig minValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_Min_Element");
SiteConfig maxValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MaxValue");
SiteConfig admin_userName = _siteConfigMgr.GetConfigByName("ADMIN_USERNAME");
SiteConfig admin_passwd = _siteConfigMgr.GetConfigByName("ADMIN_PASSWD");
//擴展名、最小值、最大值
string extention = extention_config.Value == "" ? extention_config.DefaultValue : extention_config.Value;
string minValue = minValue_config.Value == "" ? minValue_config.DefaultValue : minValue_config.Value;
string maxValue = maxValue_config.Value == "" ? maxValue_config.DefaultValue : maxValue_config.Value;
string localInspectionReportPath = imgLocalPath + InspectionReportPath;//圖片存儲地址
FileManagement fileLoad = new FileManagement();
if (Request.Files.Count > 0)
{
HttpPostedFileBase file = Request.Files[0];
string fileName = string.Empty;//當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
//
//文件夾名稱
string[] dirPath = new string[2];
dirPath[0] = query.brand_id.ToString() + "/".ToString();
dirPath[1] = query.product_id.ToString() + "/".ToString();
//
fileName = fileLoad.NewFileName(file.FileName);
if (fileName != "")
{
string[] mapPath = new string[2];
mapPath[0] = _inspectionReport.GetType1Folder(query);
mapPath[1] = _inspectionReport.GetType2Folder(query);
string NewFileName = string.Empty;
NewFileName = query.brand_id + "-" + query.product_id + "-" + mapPath[0].Replace('/', ' ').Replace(" ", "") + "-" + mapPath[1].Replace('/', ' ').Replace(" ", "");
#region 判斷圖片名稱是否正確，正確則保存
if (picFile.FileName.Substring(0, picFile.FileName.LastIndexOf(".")) == NewFileName)
{
fileName = fileName.Substring(0, fileName.LastIndexOf("."));
fileExtention = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower().ToString();
string ServerPath = string.Empty;
//判斷目錄是否存在，不存在則創建
FTP f_cf = new FTP();
CreateFolder(localInspectionReportPath, dirPath);
//壓縮圖片 源文件
string sourcePath = Server.MapPath(imgLocalServerPath + InspectionReportPath + dirPath[0] + dirPath[1] + fileName + fileExtention);
if (!Directory.Exists(Server.MapPath(imgLocalServerPath + InspectionReportPath + dirPath[0] + dirPath[1])))
{
Directory.CreateDirectory(Server.MapPath(imgLocalServerPath + InspectionReportPath + dirPath[0] + dirPath[1]));
}
file.SaveAs(sourcePath);
fileName = NewFileName + fileExtention;
NewFileName = localInspectionReportPath + dirPath[0] + dirPath[1] + NewFileName + fileExtention;
ServerPath = Server.MapPath(imgLocalServerPath + InspectionReportPath + dirPath[0] + dirPath[1]);
string ErrorMsg = string.Empty;
if (query.rowID != 0)
{
string oldFileName = oldQuery.certificate_filename;
CommonFunction.DeletePicFile(ServerPath + oldFileName);
FTP ftp = new FTP(localInspectionReportPath + dirPath[0] + dirPath[1], ftpuser, ftppwd);
List<string> tem = ftp.GetFileList();
if (tem.Contains(oldFileName))
{
FTP ftps = new FTP(localInspectionReportPath + dirPath[0] + dirPath[1] + oldFileName, ftpuser, ftppwd);
ftps.DeleteFile(localInspectionReportPath + dirPath[0] + dirPath[1] + oldFileName);
}
}
try
{
Resource.CoreMessage = new CoreResource("Product");//尋找product.resx中的資源文件
bool result = fileLoad.ZIPUpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd, sourcePath);
if (result)//上傳成功
{
query.certificate_filename = fileName;
//刪除本地圖片
CommonFunction.DeletePicFile(sourcePath);
json = _inspectionReport.SaveInspectionRe(query);
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
query.certificate_filename = oldQuery.certificate_filename;
}
if (!string.IsNullOrEmpty(ErrorMsg))
{
string jsonPic = string.Empty;
jsonPic = "{success:true,msg:\"" + ErrorMsg + "\"}";
this.Response.Clear();
this.Response.Write(jsonPic);
this.Response.End();
return this.Response;
}
}
else
{
json = "{success:'false',msg:'3'}";//圖片名稱不對
}
#endregion
}
else
{
query.certificate_filename = oldQuery.certificate_filename;
json = _inspectionReport.SaveInspectionRe(query);
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
query.certificate_filename = oldQuery.certificate_filename;
}
#endregion
// picFile.SaveAs();
}
else
{
json = "{success:'false',msg:'2'}";//重複數據
}
}
else
{
json = "{success:'false',msg:'4'}";//重複數據
}
#endregion
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int IsSortExist(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" select sort from inspection_report where product_id='{0}' and certificate_type1='{1}' and sort='{2}';", query.product_id, query.certificate_type1,query.sort);
return _access.getDataTable(sql.ToString()).Rows.Count;
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->IsSortExist-->" + sql.ToString() + ex.Message, ex);
}
}
public DataTable IsExist(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
DataTable _dt = new System.Data.DataTable();
try
{
if (query.rowID == 0)
{
sql.AppendFormat("select product_id from inspection_report where product_id='{0}' and certificate_type1='{1}' and certificate_type2='{2}';", query.product_id, query.certificate_type1, query.certificate_type2);
_dt=_access.getDataTable(sql.ToString());
}
else
{
//sql.AppendFormat("select p.product_id from product p LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id LEFT JOIN inspection_report insr on insr.product_id=p.product_id where p.product_id='{0}' and vb.brand_id='{1}' and insr.certificate_type1='{2}' and insr.certificate_type2='{3}' and insr.rowID !='{4}';", query.product_id, query.brand_id, query.certificate_type1, query.certificate_type2, query.rowID);
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->IsExist-->"+sql.ToString()+ex.Message,ex);
}
}
public List<InspectionReportQuery> InspectionReportList(InspectionReportQuery query, out int totalCount)
{
try
{
List<InspectionReportQuery> store = new List<Model.Query.InspectionReportQuery>();
store = _inspectionReport.InspectionReportList(query, out totalCount);
foreach (var item in store)
{
query=new Model.Query.InspectionReportQuery ();
query.certificate_type1=item.certificate_type1;
query.certificate_type2=item.certificate_type2;
if (item.certificate_filename != "")
{
item.certificate_filename_string = imgServerPath + InspectionReportPath + item.brand_id+"/" +item.product_id+"/" + item.certificate_filename;
}
else
{
item.certificate_filename_string = defaultImg;
}
}
return store;
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->InspectionReportList-->" + ex.Message, ex);
}
}
public DataTable GetBrandID(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.product_id != 0)
{
sql.AppendFormat(" select vb.brand_id,vb.brand_name from product p LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id where p.product_id='{0}';", query.product_id);
}
else
//if(query.brand_name!="")
{
sql.AppendFormat(" select vb.brand_id,vb.brand_name from vendor_brand vb where vb.brand_name='{0}' and brand_status='{1}' ;", query.brand_name,query.brand_status);
}
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->GetBrandID-->" + sql.ToString() + ex.Message, ex);
}
}
public string SaveInspectionRe(InspectionReportQuery query)
{
string json = string.Empty;
ArrayList arrList = new ArrayList();
try
{
if (query.rowID != 0)
{
query.m_date = DateTime.Now;
arrList.Add(_inspectionReport.UpInspectionRe(query));
}
else
{
query.k_date = DateTime.Now;
query.m_date = query.k_date;
arrList.Add(_inspectionReport.InsertInspectionRe(query));
}
if (_inspectionReport.ExecSql(arrList))
{
json = "{success:'true',msg:'0'}";//保存成功
}
else
{
json = "{success:'false',msg:'1'}";//保存失敗
}
return json;
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->SaveInspectionRe-->" + ex.Message, ex);
}
}
public string GetType2(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" select certificate_type2 from inspection_report where rowID='{0}';", query.rowID);
DataTable _dt = _access.getDataTable(sql.ToString());
return _dt.Rows[0][0].ToString();
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->GetType2-->" + sql.ToString() + ex.Message, ex);
}
}
public InspectionReportQuery oldQuery(InspectionReportQuery query)
{
try
{
return _inspectionReport.oldQuery(query);
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->oldQuery-->" + ex.Message, ex);
}
}
public List<ProductQuery> GetProductById(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" SELECT product_id FROM product WHERE product_id='{0}';", query.product_id);
return _access.getDataTableForObj<ProductQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->GetProductById-->" + sql.ToString() + ex.Message, ex);
}
}
public string GetBrandID(InspectionReportQuery query)
{
string json = string.Empty;
DataTable _dt = new DataTable();
try
{
_dt=_inspectionReport.GetBrandID(query);
if (_dt.Rows.Count > 0)
{
string brand_id = _dt.Rows[0][0].ToString();
string brand_name = _dt.Rows[0][1].ToString();
json = "{success:true,brand_id:'" + brand_id + "',brand_name:'" + brand_name + "'}";
}
else
{
json = "{success:false}";
}
return json;
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->IsExist-->" + ex.Message, ex);
}
}
public List<InspectionReportQuery> CheckInspectionReport(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" SELECT rowID FROM inspection_report WHERE product_id='{0}' AND certificate_type1='{1}' AND certificate_type2='{2}';", query.product_id,query.certificate_type1,query.certificate_type2);
return _access.getDataTableForObj<InspectionReportQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->CheckInspectionReport-->" + sql.ToString() + ex.Message, ex);
}
}
public bool GetProductById(InspectionReportQuery query)
{
List<ProductQuery> list = new List<Model.Query.ProductQuery>();
try
{
list = _inspectionReport.GetProductById(query);
if (list!=null&&list.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->GetProductById-->" + ex.Message, ex);
}
}
public int InsertInspectionReport(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
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)");
sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}') ",query.product_id,query.certificate_type1,query.certificate_type2,Common.CommonFunction.DateTimeToString(query.certificate_expdate),query.certificate_desc,query.certificate_filename,query.k_user,Common.CommonFunction.DateTimeToString(query.k_date),query.m_user,Common.CommonFunction.DateTimeToString(query.m_date));
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->InsertInspectionReport-->" + sql.ToString() + ex.Message, ex);
}
}
public bool InsertInspectionReport(InspectionReportQuery query)
{
try
{
if (query.certificate_desc.Length >= 50)
{
query.certificate_desc=query.certificate_desc.Substring(0,50);
}
if (_inspectionReport.InsertInspectionReport(query) > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->InsertInspectionReport-->" + ex.Message, ex);
}
}
public int UpdateInspectionReport(InspectionReportQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" set sql_safe_updates=0; ");
sql.AppendFormat(" UPDATE inspection_report SET certificate_desc='{0}',certificate_expdate='{1}',certificate_filename='{2}',m_user='******',m_date='{4}'",query.certificate_desc,Common.CommonFunction.DateTimeToString(query.certificate_expdate),query.certificate_filename,query.m_user,Common.CommonFunction.DateTimeToString(query.m_date));
sql.AppendFormat(" WHERE product_id='{0}' AND certificate_type1='{1}' AND certificate_type2='{2}' ;",query.product_id,query.certificate_type1,query.certificate_type2);
sql.Append(" set sql_safe_updates=1; ");
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("InspectionReportDao-->UpdateInspectionReport-->" + sql.ToString() + ex.Message, ex);
}
}
public bool IsSortExist(InspectionReportQuery query)
{
try
{
if (_inspectionReport.IsSortExist(query) == 0)
{
return true;//不重複
}
else
{
return false;//重複
}
}
catch (Exception ex)
{
throw new Exception("InspectionReportMgr-->IsSortExist-->" + ex.Message, ex);
}
}
public HttpResponseBase Export()
{
InspectionReportQuery query= new InspectionReportQuery();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["brand"]))
{
query.brand_id = Convert.ToUInt32(Request.Params["brand"]);
}
if (!string.IsNullOrEmpty(Request.Params["name_code"]))
{
query.name_code = Request.Params["name_code"].ToString().Trim();
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type1"]))
{
query.certificate_type1 = (Request.Params["certificate_type1"]);
}
if (!string.IsNullOrEmpty(Request.Params["certificate_type2"]))
{
query.certificate_type2 = (Request.Params["certificate_type2"]);
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"));
}
if (!string.IsNullOrEmpty(Request.Params["last_day"]))
{
query.last_day = Convert.ToInt32(Request.Params["last_day"]);
}
if (!string.IsNullOrEmpty(Request.Params["search_date"]))
{
query.search_date = Convert.ToInt32(Request.Params["search_date"]);
}
DataTable _newDt = new DataTable();
string newExcelName = string.Empty;
_newDt.Columns.Add("商品編號", typeof(string));
_newDt.Columns.Add("證書大類", typeof(string));
_newDt.Columns.Add("證書小類", typeof(string));
_newDt.Columns.Add("有效期限", typeof(string));
_newDt.Columns.Add("說明", typeof(string));
_newDt.Columns.Add("檔案名稱", typeof(string));
_inspectionReport = new BLL.gigade.Mgr.InspectionReportMgr(mySqlConnectionString);
DataTable _dt = _inspectionReport.Export(query);
foreach (DataRow row in _dt.Rows)
{
DataRow newRow=_newDt.NewRow();
newRow[0] = row["product_id"].ToString();
newRow[1] = row["code1"].ToString();
newRow[2] = row["code2"].ToString();
newRow[3] = row["certificate_expdate"].ToString();
newRow[4] = row["certificate_desc"];
newRow[5] = row["certificate_filename"].ToString();
_newDt.Rows.Add(newRow);
}
string[]colname=new string[_newDt.Columns.Count];
string filename = "InspectionReport" +DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
newExcelName = Server.MapPath(excelPath_export) + filename;
for (int i = 0; i < _newDt.Columns.Count; i++)
{
colname[i] = _newDt.Columns[i].ColumnName;
}
if (System.IO.File.Exists(newExcelName))
{
System.IO.File.Delete(newExcelName);
}
ExcelHelperXhf.ExportDTtoExcel(_newDt, "", newExcelName);
json = "{success:true,ExcelName:\'" + filename + "\'}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
