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; }