public List<CategoryQuery> GetCategoryList(CategoryQuery store, out int totalCount)
{
StringBuilder strSql = new StringBuilder();
StringBuilder search = new StringBuilder();
List<VendorBrandSetQuery> list = new List<VendorBrandSetQuery>();
StringBuilder strCount = new StringBuilder();
DataTable _dt = new DataTable();
try
{
strSql.Append(@"SELECT b.category_id,b.category_name,case when a.amount>0 THEN amount ELSE '0' END AS amo from ");
strCount = strCount.Append("SELECT count(*) as search_total from ");
search.Append(" (select sum(od.single_money * buy_num) as amount,pcs.category_id from order_detail od inner join product_item pit using(item_id) INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) inner join product p using (product_id) inner join product_category_set pcs using(product_id) where od.detail_status <> 90 ");//
//if (store.brand_status == 2 || store.brand_status == 0)
//{
// search.Append(" AND od.detail_status <> 90 ");
//}
if (store.brand_status > 1)
{
search.Append(" AND om.money_collect_date > 0 ");
}
if (store.seldate != 0)
{
if (store.starttime != 0)
{
search.AppendFormat(" AND om.order_createdate >= '{0}' ", store.starttime);
}
if (store.endtime != 0)
{
search.AppendFormat(" AND om.order_createdate <= '{0}' ", store.endtime);
}
}
search.Append(" GROUP BY pcs.category_id ) a ");
if (store.serchs != 0)
{
search.AppendFormat(@"RIGHT JOIN (SELECT category_id , category_name FROM product_category WHERE category_id IN (SELECT category_id FROM product_category where (category_father_id='{0}' or category_id='{0}') and category_display = 1)) b ON a.category_id=b.category_id ", store.serchs);
}
else
{
search.Append(" RIGHT JOIN (SELECT category_id , category_name FROM product_category WHERE category_id IN (SELECT category_id FROM product_category where category_father_id='5' or category_id='5')) b ON a.category_id=b.category_id ");
}
strSql.Append(search.ToString());
totalCount = 0;
if (store.IsPage)
{
_dt = _dbAccess.getDataTable(strCount.ToString() + search.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]);
}
strSql.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
}
return _dbAccess.getDataTableForObj<CategoryQuery>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->GetCategoryList-->" + ex.Message + "sql:" + strSql.ToString(), ex);
}
}
public string GetSum(CategoryQuery query)
{
try
{
return _categoryDao.GetSum(query);
}
catch (Exception ex)
{
throw new Exception("CategoryMgr-->GetSum-->" + ex.Message, ex);
}
}
public List<CategoryQuery> GetCategoryList(CategoryQuery query, out int totalCount)
{
try
{
return _categoryDao.GetCategoryList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("CategoryMgr-->GetCategoryList-->" + ex.Message, ex);
}
}
/// <summary>
/// 查詢一條類別信息根據編號
/// </summary>
/// <param name="cq"></param>
/// <returns></returns>
public CategoryQuery GetProductCategoryById(CategoryQuery cq)
{
try
{
return _categoryDao.GetProductCategoryById(cq);
}
catch (Exception ex)
{
throw new Exception("CategoryMgr-->ProductCategorySave-->" + ex.Message, ex);
}
}
public HttpResponseBase GetProductCategoryList()
{
List<CategoryQuery> store = new List<CategoryQuery>();
string json = string.Empty;
try
{
CategoryQuery query = new CategoryQuery();
query.Start = Convert.ToInt32(Request["Start"] ?? "0");
if (!string.IsNullOrEmpty(Request["Limit"]))
{
query.Limit = Convert.ToInt32(Request["Limit"]);
}
if (!string.IsNullOrEmpty(Request.Params["father_id"]))
{
query.category_father_id = Convert.ToUInt32(Request.Params["father_id"]);
}
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
int totalCount = 0;
store = _proCategoryImplMgr.GetProductCategoryList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
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:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public string GetSum(CategoryQuery store)
{
DataTable _dt = new DataTable();
StringBuilder sql = new StringBuilder();
try
{
if (store.serchs == 0)
{
store.serchs = 5;
}
sql.AppendFormat("select sum(od.single_money * buy_num) as amount from order_detail od inner join product_item pit using(item_id) INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) inner join product p using (product_id) inner join product_category_set pcs using(product_id) where om.money_collect_date > 0 AND od.detail_status <> 90 AND category_id IN (SELECT category_id FROM product_category where category_father_id='{0}' or category_id='{0}') ", store.serchs);
_dt = _dbAccess.getDataTable(sql.ToString());
if (_dt.Rows.Count > 0)
{
return _dt.Rows[0][0].ToString();
}
else
return "0";
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->GetSum-->" + ex.Message + "sql:" + sql.ToString(), ex);
}
}
/// <summary>
/// 更改狀態
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateActive(CategoryQuery model)
{
return _categoryDao.UpdateActive(model);
}
/// <summary>
/// 更改狀態
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateActive(CategoryQuery model)
{
string strSql = string.Empty;
try
{
strSql = string.Format("update product_category set status='{0}' where category_id='{1}';", model.status, model.category_id);
return _dbAccess.execCommand(strSql);
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->UpdateActive-->" + ex.Message + strSql, ex);
}
}
public List<Model.ProductCategory> GetProductCategoryCSV(CategoryQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.Append("select category_id,category_name,category_father_id from product_category ");
return _dbAccess.getDataTableForObj<Model.ProductCategory>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->GetProductCategoryCSV-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 新增編輯類別信息
/// </summary>
/// <returns></returns>
public HttpResponseBase ProductCategorySave()
{
string json = string.Empty;
string jsonStr = string.Empty;
CategoryQuery cq = new CategoryQuery();
IAreaPactetImplMgr _iareaPacketMgr = new AreaPacketMgr(mySqlConnectionString);
string errorInfo = string.Empty;
try
{
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["category_id"]))
{
cq.category_id = Convert.ToUInt32(Request.Params["category_id"]);
}
CategoryQuery oldCq = _proCategoryImplMgr.GetProductCategoryById(cq);
if (oldCq != null)
{
cq.banner_image = oldCq.banner_image;
cq.category_image_in = oldCq.category_image_in;
cq.category_image_out = oldCq.category_image_out;
cq.category_image_app = oldCq.category_image_app;
}
#region 上傳圖片
try
{
if (Request.Files.Count != 0)
{
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_MinValue");
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 localPromoPath = imgLocalPath + promoPath;//圖片存儲地址
for (int a = 0; a < Request.Files.Count; a++)
{
string fileName = string.Empty; //當前文件名
string fileExtention = string.Empty;//當前文件的擴展名
HttpPostedFileBase file = Request.Files[a];
fileName = Path.GetFileName(file.FileName);
if (string.IsNullOrEmpty(fileName))
{
continue;
}
bool result = false;
string NewFileName = string.Empty;
string ServerPath = string.Empty;
string ErrorMsg = string.Empty;
string oldFileName = string.Empty; //舊文件名
//生成隨機數，用於圖片的重命名
Random rand = new Random();
int newRand = rand.Next(1000, 9999);
fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
NewFileName = newRand + fileExtention;
//判斷目錄是否存在，不存在則創建
CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), promoPath.Substring(1, promoPath.Length - 2).Split('/'));
fileName = NewFileName;
NewFileName = localPromoPath + NewFileName;//絕對路徑
ServerPath = Server.MapPath(imgLocalServerPath + promoPath);
FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd);
List<string> tem = ftp.GetFileList();
try
{
//上傳
FileManagement fileLoad = new FileManagement();
result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
if (result)//上傳成功
{
switch (a)
{
case 0:
cq.banner_image = fileName;
break;
case 1:
cq.category_image_in = fileName;
break;
case 2:
cq.category_image_out = fileName;
break;
case 3:
cq.category_image_app = fileName;
break;
default:
break;
}
}
else
{
//圖片上傳失敗則圖片名稱為原有圖片名稱
switch (a)
{
case 0:
cq.banner_image = fileName;
break;
case 1:
cq.category_image_in = fileName;
break;
case 2:
cq.category_image_out = fileName;
break;
case 3:
cq.category_image_app = fileName;
break;
default:
break;
}
errorInfo += "第" + (a + 1) + "張" + ErrorMsg + "<br/>";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name + "->fileLoad.UpLoadFile()", ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
jsonStr = "{success:false,msg:'圖片上傳失敗'}";
}
}
}
}
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);
jsonStr = "{success:false,msg:'圖片上傳失敗'}";
}
#endregion
if (!string.IsNullOrEmpty(Request.Params["comboFrontCage"]))
{
cq.category_father_id = Convert.ToUInt32(Request.Params["comboFrontCage"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["category_name"]))
{
cq.category_name = Request.Params["category_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["category_sort"]))
{
cq.category_sort = Convert.ToUInt32(Request.Params["category_sort"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["category_display"]))
{
cq.category_display = Convert.ToUInt32(Request.Params["category_display"]);
}
if (!string.IsNullOrEmpty(Request.Params["categorylinkmode"]))
{
cq.category_link_mode = Convert.ToUInt32(Request.Params["categorylinkmode"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["category_link_url"]))
{
cq.category_link_url = Request.Params["category_link_url"].ToString();
}
//if (!string.IsNullOrEmpty(Request.Params["photo"]))
//{
// #region 上傳圖片
// try
// {
// if (Request.Files.Count != 0)
// {
// 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_MinValue");
// 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 localPromoPath = imgLocalPath + promoPath;//圖片存儲地址
// //生成隨機數，用於圖片的重命名
// Random rand = new Random();
// int newRand = rand.Next(1000, 9999);
// //獲取上傳的圖片
// HttpPostedFileBase file = Request.Files[0];
// string fileName = string.Empty;//當前文件名
// string fileExtention = string.Empty;//當前文件的擴展名
// fileName = Path.GetFileName(file.FileName);
// if (!string.IsNullOrEmpty(fileName))
// {
// bool result = false;
// string NewFileName = string.Empty;
// fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
// //NewFileName = pacdModel.event_id + newRand + fileExtention;//圖片重命名為event_id+4位隨機數+擴展名
// NewFileName = newRand + fileExtention;
// string ServerPath = string.Empty;
// //判斷目錄是否存在，不存在則創建
// CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), promoPath.Substring(1, promoPath.Length - 2).Split('/'));
// // returnName += promoPath + NewFileName;
// fileName = NewFileName;
// NewFileName = localPromoPath + NewFileName;//絕對路徑
// ServerPath = Server.MapPath(imgLocalServerPath + promoPath);
// string ErrorMsg = string.Empty;
// //上傳之前刪除已有的圖片
// //string oldFileName = olderpcmodel.banner_image;
// FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd);
// List<string> tem = ftp.GetFileList();
// //if (tem.Contains(oldFileName))
// //{
// // //FTP ftps = new FTP(localPromoPath + oldFileName, ftpuser, ftppwd);
// // //ftps.DeleteFile(localPromoPath + oldFileName);//刪除ftp:71.159上的舊圖片
// // //DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
// //}
// try
// {
// //上傳
// FileManagement fileLoad = new FileManagement();
// result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
// if (result)//上傳成功
// {
// cq.banner_image = fileName;
// }
// }
// catch (Exception ex)
// {
// Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
// logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name + "->fileLoad.UpLoadFile()", ex.Source, ex.Message);
// logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
// log.Error(logMessage);
// jsonStr = "{success:false,msg:'圖片上傳失敗'}";
// //pacdModel.banner_image = olderpcmodel.banner_image;
// }
// }
// else
// {
// //pacdModel.banner_image = olderpcmodel.banner_image;
// }
// }
// }
// 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);
// jsonStr = "{success:false,msg:'圖片上傳失敗'}";
// //pacdModel.banner_image = olderpcmodel.banner_image;
// }
// #endregion
//}
if (!string.IsNullOrEmpty(Request.Params["banner_status"]))
{
cq.banner_status = Convert.ToUInt32(Request.Params["banner_status"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["banner_link_mode"]))
{
cq.banner_link_mode = Convert.ToUInt32(Request.Params["banner_link_mode"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["banner_link_url"]))
{
cq.banner_link_url = Request.Params["banner_link_url"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
cq.banner_show_start = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["startdate"].ToString()));
}
if (!string.IsNullOrEmpty(Request.Params["enddate"]))
{
cq.banner_show_end = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["enddate"].ToString()));
}
if (!string.IsNullOrEmpty(Request.Params["short_description"]))
{
cq.short_description = Request.Params["short_description"].ToString();
}
if (string.IsNullOrEmpty(Request.Params["category_id"]))
{
cq.category_ipfrom = CommonFunction.GetClientIP();
cq.category_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
cq.category_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
cq.status = 1;
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
int i = _proCategoryImplMgr.ProductCategorySave(cq);
if (i > 0)
{
json = "{success:true}";
}
}
else
{
//_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
//cq.category_id = Convert.ToUInt32(Request.Params["category_id"]);
//CategoryQuery oldCq = _proCategoryImplMgr.GetProductCategoryById(cq);
//if (!string.IsNullOrEmpty(Request.Params["comboFrontCage"]))
//{
// cq.category_father_id = Convert.ToUInt32(Request.Params["comboFrontCage"].ToString());
//}
//else
//{
// cq.category_father_id = oldCq.category_father_id;
//}
//if (!string.IsNullOrEmpty(Request.Params["category_name"]))
//{
// cq.category_name = Request.Params["category_name"].ToString();
//}
//else
//{
// cq.category_name = oldCq.category_name;
//}
//if (!string.IsNullOrEmpty(Request.Params["category_sort"]))
//{
// cq.category_sort = Convert.ToUInt32(Request.Params["category_sort"].ToString());
//}
//else
//{
// cq.category_sort = oldCq.category_sort;
//}
//if (!string.IsNullOrEmpty(Request.Params["category_display"]))
//{
// cq.category_display = Convert.ToUInt32(Request.Params["category_display"]);
//}
//else
//{
// cq.category_display = oldCq.category_display;
//}
//if (!string.IsNullOrEmpty(Request.Params["categorylinkmode"]))
//{
// cq.category_link_mode = Convert.ToUInt32(Request.Params["categorylinkmode"].ToString());
//}
//else
//{
// cq.category_link_mode = oldCq.category_link_mode;
//}
//cq.category_link_url = Request.Params["category_link_url"].ToString();
//if (!string.IsNullOrEmpty(Request.Params["photo"]))
//{
// #region 上傳圖片
// try
// {
// if (Request.Files.Count != 0)
// {
// 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_MinValue");
// 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 localPromoPath = imgLocalPath + promoPath;//圖片存儲地址
// 生成隨機數，用於圖片的重命名
// Random rand = new Random();
// int newRand = rand.Next(1000, 9999);
// 獲取上傳的圖片
// HttpPostedFileBase file = Request.Files[0];
// string fileName = string.Empty;//當前文件名
// string fileExtention = string.Empty;//當前文件的擴展名
// fileName = Path.GetFileName(file.FileName);
// if (!string.IsNullOrEmpty(fileName))
// {
// bool result = false;
// string NewFileName = string.Empty;
// fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower();
// NewFileName = pacdModel.event_id + newRand + fileExtention;//圖片重命名為event_id+4位隨機數+擴展名
// NewFileName = newRand + fileExtention;
// string ServerPath = string.Empty;
// 判斷目錄是否存在，不存在則創建
// CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), promoPath.Substring(1, promoPath.Length - 2).Split('/'));
// returnName += promoPath + NewFileName;
// fileName = NewFileName;
// NewFileName = localPromoPath + NewFileName;//絕對路徑
// ServerPath = Server.MapPath(imgLocalServerPath + promoPath);
// string ErrorMsg = string.Empty;
// 上傳之前刪除已有的圖片
// string oldFileName = oldCq.banner_image;
// FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd);
// List<string> tem = ftp.GetFileList();
// if (tem.Contains(oldFileName))
// {
// FTP ftps = new FTP(localPromoPath + oldFileName, ftpuser, ftppwd);
// ftps.DeleteFile(localPromoPath + oldFileName);//刪除ftp:71.159上的舊圖片
// DeletePicFile(ServerPath + oldFileName);//刪除本地圖片
// }
// try
// {
// 上傳
// FileManagement fileLoad = new FileManagement();
// result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd);
// if (result)//上傳成功
// {
// cq.banner_image = fileName;
// }
// }
// catch (Exception ex)
// {
// Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
// logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name + "->fileLoad.UpLoadFile()", ex.Source, ex.Message);
// logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
// log.Error(logMessage);
// jsonStr = "{success:false,msg:'圖片上傳失敗'}";
// cq.banner_image = oldCq.banner_image;
// }
// }
// else
// {
// cq.banner_image = oldCq.banner_image;
// }
// }
// }
// 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);
// jsonStr = "{success:false,msg:'圖片上傳失敗'}";
// cq.banner_image = oldCq.banner_image;
// }
// #endregion
//}
//else
//{
// cq.banner_image = oldCq.banner_image;
//}
//if (!string.IsNullOrEmpty(Request.Params["banner_status"]))
//{
// cq.banner_status = Convert.ToUInt32(Request.Params["banner_status"].ToString());
//}
//else
//{
// cq.banner_status = oldCq.banner_status;
//}
//if (!string.IsNullOrEmpty(Request.Params["banner_link_mode"]))
//{
// cq.banner_link_mode = Convert.ToUInt32(Request.Params["banner_link_mode"].ToString());
//}
//else
//{
// cq.banner_link_mode = oldCq.banner_link_mode;
//}
//cq.banner_link_url = Request.Params["banner_link_url"].ToString();
//cq.short_description = Request.Params["short_description"].ToString();
//cq.banner_show_start = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["startdate"].ToString()));
//cq.banner_show_end = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["enddate"].ToString()));
cq.category_ipfrom = CommonFunction.GetClientIP();
cq.category_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
int j = _proCategoryImplMgr.ProductCategorySave(cq);
if (j > 0)
{
json = "{success:true}";
}
}
}
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,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 查詢一條類別信息根據編號
/// </summary>
/// <param name="cq"></param>
/// <returns></returns>
public CategoryQuery GetProductCategoryById(CategoryQuery cq)
{
StringBuilder strSql = new StringBuilder();
try
{
strSql.AppendFormat("select category_id,category_father_id,category_name,category_sort,category_display,category_link_mode,category_link_url,banner_image,banner_status,banner_link_mode,banner_link_url,banner_show_start,banner_show_end,category_createdate,category_updatedate,category_ipfrom,short_description,status,category_image_in,category_image_out,category_image_app from product_category where category_id='{0}'", cq.category_id);
return _dbAccess.getSinggleObj<CategoryQuery>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->GetProductCategoryById-->" + strSql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 根據富類別查詢子類別列表
/// </summary>
/// <param name="cq"></param>
/// <returns></returns>
public List<CategoryQuery> GetProductCategoryList(CategoryQuery cq, out int totalCount)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSelect = new StringBuilder();
StringBuilder strCount = new StringBuilder();
StringBuilder strCondition = new StringBuilder();
try
{
strSelect.AppendFormat("select pc1.category_id,pc1.category_father_id,pc1.category_name,pc1.category_sort,pc1.category_display,");
strSelect.AppendFormat(" pc1.category_link_mode,pc1.category_link_url,pc1.banner_image,pc1.banner_status,pc1.banner_link_mode,");
strSelect.AppendFormat("pc1.banner_link_url,FROM_UNIXTIME(pc1.banner_show_start) as startdate,FROM_UNIXTIME(pc1.banner_show_end) as enddate,");
strSelect.AppendFormat("pc1.category_createdate,pc1.category_updatedate,pc1.category_ipfrom,pc1.status,pc2.category_name as category_father_name,pc1.short_description ");
strSelect.AppendFormat(",pc1.category_image_in,pc1.category_image_out ,pc1.category_image_app ");
strCount.AppendFormat(" select count(pc1.category_id) as search_total ");
strCondition.AppendFormat(" from product_category pc1 ");
strCondition.AppendFormat(" left join product_category pc2 on pc1.category_father_id=pc2.category_id ");
strCondition.AppendFormat(" where 1=1 ");
if (cq.category_father_id == 0)
{
strCondition.AppendFormat(" and pc1.category_father_id='{0}'", 2);
}
else
{
strCondition.AppendFormat(" and pc1.category_father_id='{0}'", cq.category_father_id);
}
totalCount = 0;
strSql.AppendFormat(strSelect.ToString() + strCondition.ToString());
if (cq.IsPage)
{
DataTable _dt = _dbAccess.getDataTable(strCount.ToString() + strCondition.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]);
}
strSql.AppendFormat(" limit {0},{1}", cq.Start, cq.Limit);
}
return _dbAccess.getDataTableForObj<CategoryQuery>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->GetProductCategoryList-->" + ex.Message + "sql:" + strSql.ToString(), ex);
}
}
/// <summary>
/// 保存類別信息
/// </summary>
/// <param name="cq"></param>
/// <returns></returns>
public int ProductCategorySave(CategoryQuery cq)
{
cq.Replace4MySQL();
StringBuilder strSql = new StringBuilder();
try
{
if (cq.category_id == 0)
{
strSql.AppendFormat("insert into product_category (category_father_id,category_name,category_sort,category_display,category_link_mode,");
strSql.AppendFormat("category_link_url,banner_image,banner_status,banner_link_mode,banner_link_url,banner_show_start,banner_show_end,");
strSql.AppendFormat("category_createdate,category_updatedate,category_ipfrom,short_description,status,category_image_in,category_image_out,category_image_app)");
strSql.AppendFormat(" values('{0}','{1}',", cq.category_father_id, cq.category_name);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", cq.category_sort, cq.category_display, cq.category_link_mode, cq.category_link_url, cq.banner_image);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", cq.banner_status, cq.banner_link_mode, cq.banner_link_url, cq.banner_show_start, cq.banner_show_end);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", cq.category_createdate, cq.category_updatedate, cq.category_ipfrom, cq.short_description, cq.status);
strSql.AppendFormat("'{0}','{1}','{2}')", cq.category_image_in, cq.category_image_out, cq.category_image_app);
return _dbAccess.execCommand(strSql.ToString());
}
else
{
strSql.AppendFormat("update product_category set category_father_id='{0}',category_name='{1}',", cq.category_father_id, cq.category_name);
strSql.AppendFormat("category_sort='{0}',category_display='{1}',category_link_mode='{2}',", cq.category_sort, cq.category_display, cq.category_link_mode);
strSql.AppendFormat("category_link_url='{0}',banner_image='{1}',banner_status='{2}',", cq.category_link_url, cq.banner_image, cq.banner_status);
strSql.AppendFormat("banner_link_mode='{0}',banner_link_url='{1}',banner_show_start='{2}',", cq.banner_link_mode, cq.banner_link_url, cq.banner_show_start);
strSql.AppendFormat("banner_show_end='{0}',category_updatedate='{1}',", cq.banner_show_end, cq.category_updatedate);
strSql.AppendFormat("short_description='{0}' , ", cq.short_description);
strSql.AppendFormat("category_image_in='{0}' ,category_image_out='{1}', category_image_app='{2}',", cq.category_image_in, cq.category_image_out, cq.category_image_app);
strSql.AppendFormat("category_ipfrom='{0}' where category_id='{1}'", cq.category_ipfrom, cq.category_id);
return _dbAccess.execCommand(strSql.ToString());
}
}
catch (Exception ex)
{
throw new Exception("CategoryDao-->ProductCategorySave-->" + strSql.ToString() + ex.Message, ex);
}
}
public HttpResponseBase GetCategoryList()
{
List<CategoryQuery> stores = new List<CategoryQuery>();
string json = string.Empty;
int sum = 0;
try
{
CategoryQuery query = new CategoryQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (string.IsNullOrEmpty(Request.Params["serchs"]))
{
query.serchs = 0;
}
else
{
query.serchs = Convert.ToInt32(Request.Params["serchs"]);
}
query.seldate = Convert.ToInt32(Request.Params["seldate"] ?? "0");
query.brand_status = Convert.ToInt32(Request.Params["brand_status"] ?? "0");
query.starttime = Convert.ToInt32(CommonFunction.GetPHPTime(Request.Params["starttime"] ?? "0"));
query.endtime = Convert.ToInt32(CommonFunction.GetPHPTime(Request.Params["endtime"] ?? "0"));
ICategoryImplMgr _IcategoryMgr = new CategoryMgr(mySqlConnectionString);
int totalCount = 0;
stores = _IcategoryMgr.GetCategoryList(query, out totalCount);
//sum = Int32.Parse(_IcategoryMgr.GetSum(query));
foreach (var item in stores)
{ //因為delivertime類型是int！
sum += int.Parse(item.amo);
}
foreach (var item in stores)
{ //因為delivertime類型是int！
item.amo = int.Parse(item.amo).ToString("###,###");
item.sum = sum.ToString("###,###");
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據
}
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:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public List<Model.ProductCategory> GetProductCategoryCSV(CategoryQuery query)
{
try
{
return _categoryDao.GetProductCategoryCSV(query);
}
catch (Exception ex)
{
throw new Exception("CategoryMgr-->GetProductCategoryCSV-->" + ex.Message, ex);
}
}
/// <summary>
/// 匯出csv
/// </summary>
/// <returns></returns>
public HttpResponseBase ProductCategoryCSV()
{
string newCsvName = string.Empty;
string json = string.Empty;
DataTable dt = new DataTable();
CategoryQuery query = new CategoryQuery();
if (!string.IsNullOrEmpty(Request.Params["Root"]))
{
query.category_father_id = Convert.ToUInt32(Request.Params["Root"]);
}
List<ProductCategory> categoryList = new List<ProductCategory>();
List<ProductCategoryCustom> cateList = new List<ProductCategoryCustom>();
// _procateMgr = new ProductCategoryMgr(connectionString);
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
dt.Columns.Add(new DataColumn("", typeof(string)));
DataRow dr = dt.NewRow();
dr[0] = query.category_father_id.ToString() + " " + _proCategoryImplMgr.GetProductCategoryById(new CategoryQuery { category_id = query.category_father_id }).category_name;//根
dt.Rows.Add(dr);
// categoryList = _procateMgr.GetProductCate(new ProductCategory { });//數據源
categoryList = _proCategoryImplMgr.GetProductCategoryCSV(query);
cateList = getCate(categoryList, query.category_father_id.ToString());//得到第二層分支
// cateList={1,2,3,4};
//調試resultlist是否為空
GetCategoryList(categoryList, ref cateList, ref dt, 1);
try
{
string filename = "ProductCategory_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
newCsvName = Server.MapPath(excelPath) + filename;
if (System.IO.File.Exists(newCsvName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newCsvName, FileAttributes.Normal);
System.IO.File.Delete(newCsvName);
}
string[] colname = { };
CsvHelper.ExportDataTableToCsv(dt, newCsvName, colname, false);
json = "true," + filename + "," + excelPath;
}
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 = "false, ";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 更改活動使用狀態
/// </summary>
/// <returns>數據庫操作結果</returns>
public JsonResult UpdateActive()
{
string jsonStr = string.Empty;
try
{
CategoryQuery model = new CategoryQuery();
if (!string.IsNullOrEmpty(Request.Params["active"]))
{
model.status = Convert.ToInt32(Request.Params["active"]);
}
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
model.category_id = Convert.ToUInt32(Request.Params["id"]);
}
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
if (_proCategoryImplMgr.UpdateActive(model) > 0)
{
return Json(new { success = "true" });
}
else
{
return Json(new { success = "false" });
}
}
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);
return Json(new { success = "false" });
}
}
/// <summary>
/// 獲取父類別編號
/// </summary>
/// <returns>數據庫操作結果</returns>
public JsonResult GetFatherId()
{
string jsonStr = string.Empty;
try
{
CategoryQuery model = new CategoryQuery();
if (!string.IsNullOrEmpty(Request.Params["fid"]))
{
model.category_id = Convert.ToUInt32(Request.Params["fid"]);
}
_proCategoryImplMgr = new CategoryMgr(mySqlConnectionString);
CategoryQuery cq = _proCategoryImplMgr.GetProductCategoryById(model);
return Json(new { success = "true", result = cq.category_father_id.ToString() });
}
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);
return Json(new { success = "false" });
}
}
