using StoreHub.KJDWebService.Models;
using StoreHub.Framework.Repositorys;
using Microsoft.Extensions.Configuration;
using StoreHub.KJDWebService.Commons;
using MySql.Data.MySqlClient;
using NaiBa.Util.Extension;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace StoreHub.KJDWebService.Services
{
    public class PriceService : ServiceBase
	{
		public PriceService(SqlRepository sqlRepository, IConfiguration configuration) : base(sqlRepository, configuration)
		{
		}
        public string AddPrice(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                string user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                string ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "ext_code")).ParseToString();
                int original_price = RegexUtils.GetStringQuery(ref inputData, "original_price").ParseToInt();
                int markup_times = RegexUtils.GetStringQuery(ref inputData, "markup_times").ParseToInt();
                int extra_price = RegexUtils.GetStringQuery(ref inputData, "extra_price").ParseToInt();
                string notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "notes")).ParseToString();

                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);
                string sqlExist = "select count(ext_code) from kjd.price_markup where ext_code=@ext_code";
                string sql = "insert into kjd.price_markup " +
                    "(ext_code,original_price,markup_times,extra_price,notes) " +
                    "values (@ext_code,@original_price,@markup_times,@extra_price,@notes);";
                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@ext_code", Value = ext_code, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@original_price", Value = original_price, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@markup_times", Value = markup_times, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@extra_price", Value = extra_price, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@notes", Value = notes, });

                var has = mySql.ExecuteFirst(sqlExist, paramLst.ToArray());
                if (has != "0")
                {
                    mySql.Close();
                    return "{\"code\":501,\"msg\":\"货号 " + ext_code + " 价格数据已存在\"}";
                }

                var rst = mySql.ExecuteNonQuery(sql, paramLst.ToArray());
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                {
                    return "{\"code\":502,\"msg\":\"" + mySql.Message + "\"}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string GetPriceList(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                var password = RegexUtils.GetStringQuery(ref inputData, "password");

                string start_original_price = RegexUtils.GetStringQuery(ref inputData, "start_original_price");
                string end_original_price = RegexUtils.GetStringQuery(ref inputData, "end_original_price");
                string start_markup_times = RegexUtils.GetStringQuery(ref inputData, "start_markup_times");
                string end_markup_times = RegexUtils.GetStringQuery(ref inputData, "end_markup_times");
                string start_extra_price = RegexUtils.GetStringQuery(ref inputData, "start_extra_price");
                string end_extra_price = RegexUtils.GetStringQuery(ref inputData, "end_extra_price");
                var search_ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_ext_code"));
                var search_notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_notes"));
                var in_ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "in_ext_code"));
                int page = RegexUtils.GetStringQuery(ref inputData, "page").ParseToInt();
                int page_size = RegexUtils.GetStringQuery(ref inputData, "page_size").ParseToInt(50);
                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }


                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_ext_code", Value = "%" + search_ext_code + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_notes", Value = "%" + search_notes + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@start_original_price", Value = start_original_price.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@end_original_price", Value = end_original_price.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@start_markup_times", Value = start_markup_times.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@end_markup_times", Value = end_markup_times.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@start_extra_price", Value = start_extra_price.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@end_extra_price", Value = end_extra_price.ParseToInt(), });
                //paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@in_ext_code", Value = "'" + in_ext_code?.Replace(",", "','") + "'", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@in_ext_code", Value = in_ext_code.ParseToString() });

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);

                string sql = "select count(id) from kjd.price_markup p where 1=1";

                string where = "";
                if (!string.IsNullOrWhiteSpace(search_ext_code))
                    where += " and p.ext_code like @search_ext_code";
                if (!string.IsNullOrWhiteSpace(search_notes))
                    where += " and p.notes like @search_notes";
                if (!string.IsNullOrWhiteSpace(start_original_price))
                    where += " and p.original_price >= @start_original_price";
                if (!string.IsNullOrWhiteSpace(end_original_price))
                    where += " and p.original_price <= @end_original_price";
                if (!string.IsNullOrWhiteSpace(start_markup_times))
                    where += " and p.markup_times >= @start_markup_times";
                if (!string.IsNullOrWhiteSpace(end_markup_times))
                    where += " and p.markup_times <= @end_markup_times";
                if (!string.IsNullOrWhiteSpace(start_extra_price))
                    where += " and p.extra_price >= @start_extra_price";
                if (!string.IsNullOrWhiteSpace(end_extra_price))
                    where += " and p.extra_price <= @end_extra_price";
                if (!string.IsNullOrEmpty(in_ext_code))
                    where += " and p.ext_code in ('" + in_ext_code?.Replace(",", "','") + "')";
                sql += where;

                var count = mySql.ExecuteRowCount(sql, paramLst.ToArray());

                DataTable dt;
                if (count == 0)
                {
                    dt = new DataTable();
                }
                else
                {
                    sql = "select * from kjd.price_markup p where 1=1";

                    //where子句
                    sql += where;

                    sql += " order by p.id desc";
                    sql += " limit " + Math.Max(page - 1, 0) * page_size + "," + page_size;
                    dt = mySql.ExecuteDataTable(sql, paramLst.ToArray());
                }

                mySql.Close();
                if (dt != null)
                {
                    try
                    {
                        List<DBPriceResultParam> priceList = new List<DBPriceResultParam>();
                        foreach (DataRow row in dt.Rows)
                        {
                            DBPriceResultParam price = new DBPriceResultParam
                            {
                                id = row["id"].ParseToInt(),
                                extra_price = row["extra_price"].ParseToInt(),
                                markup_times = row["markup_times"].ParseToInt(),
                                original_price = row["original_price"].ParseToInt(),
                                notes = row["notes"].ParseToString(),
                                ext_code = row["ext_code"].ParseToString(),
                                add_time = Convert.ToDateTime(row["add_time"]),
                                mdy_time = Convert.ToDateTime(row["mdy_time"]),
                            };
                            priceList.Add(price);
                        }
                        var data = JsonUtils.SerializeObjectToString(priceList);
                        return "{\"code\":200,\"msg\":\"success\",\"data\":" + data + ",\"count\":" + count + "}";
                    }
                    catch (Exception ex)
                    {
                        return "{\"code\":500,\"msg\":\"" + ex.Message + "\",\"data\":[],\"count\":" + 0 + "}";
                    }
                }
                else
                {
                    return "{\"code\":500,\"msg\":\"" + mySql.Message + "\",\"data\":[],\"count\":" + 0 + "}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string DelDbPrice(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name")?.Replace("'", "\\'");
                var id = RegexUtils.GetStringQuery(ref inputData, "db_id")?.Replace("'", "\\'");

                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);
                string sql = "delete from kjd.price_markup where id='" + id + "'";

                var rst = mySql.ExecuteNonQuery(sql);
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                    return "{\"code\":500,\"msg\":\"删除失败：" + mySql.Message + "\"}";
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string MdyDBPrice(string inputData, bool isAdmin = false)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                var db_id = RegexUtils.GetStringQuery(ref inputData, "db_id");
                string notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "notes"));
                string ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "ext_code"));
                string original_price = RegexUtils.GetStringQuery(ref inputData, "original_price");
                string markup_times = RegexUtils.GetStringQuery(ref inputData, "markup_times");
                string extra_price = RegexUtils.GetStringQuery(ref inputData, "extra_price");

                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@ext_code", Value = ext_code, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@original_price", Value = original_price.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@markup_times", Value = markup_times.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@extra_price", Value = extra_price.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@notes", Value = notes, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@db_id", Value = db_id, });

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);

                string sqlExist = "select count(ext_code) from kjd.price_markup where ext_code=@ext_code and id!=@db_id";
                var has = mySql.ExecuteFirst(sqlExist, paramLst.ToArray());
                if (has != "0")
                {
                    mySql.Close();
                    return "{\"code\":501,\"msg\":\"货号 " + ext_code + " 价格数据已存在\"}";
                }

                string sql = "update kjd.price_markup set";
                if (notes != null)
                    sql += " notes=@notes,";
                if (ext_code != null)
                    sql += " ext_code=@ext_code,";
                if (original_price != null)
                    sql += " original_price=@original_price,";
                if (markup_times != null)
                    sql += " markup_times=@markup_times,";
                if (extra_price != null)
                    sql += " extra_price=@extra_price,";
                sql = sql.TrimEnd(',');
                sql += " where id=@db_id;";

                var rst = mySql.ExecuteNonQuery(sql, paramLst.ToArray());
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                {
                    return "{\"code\":500,\"msg\":\"更新失败：" + mySql.Message + "\"}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string AddPurchasePrice(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                string user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                string shop_id = RegexUtils.GetStringQuery(ref inputData, "shop_id");
                string shop_name = RegexUtils.GetStringQuery(ref inputData, "shop_name");
                string spu_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "spu_id")).ParseToString();
                string skc_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "skc_id")).ParseToString();
                string cat_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "cat_id")).ParseToString();
                string cat_name = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "cat_name")).ParseToString();
                string ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "ext_code")).ParseToString();
                string product_name = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "product_name")).ParseToString();
                string main_img = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "main_img")).ParseToString();
                string discount = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "discount")).ParseToString();
                string price_text = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "price_text")).ParseToString();
                string sku_purchase_price_json = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "sku_purchase_price_json")).ParseToString();
                string notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "notes")).ParseToString();


                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);
                string sqlExist = "select count(spu_id) from kjd.product_purchase_price where spu_id=@spu_id";
                string sql = "insert into kjd.product_purchase_price " +
                    "(shop_id,shop_name,spu_id,skc_id,cat_id,cat_name,ext_code,product_name,main_img,discount,price_text,sku_purchase_price_json,notes) " +
                    "values (@shop_id,@shop_name,@spu_id,@skc_id,@cat_id,@cat_name,@ext_code,@product_name,@main_img,@discount,@price_text,@sku_purchase_price_json,@notes);";
                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@shop_id", Value = shop_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@shop_name", Value = shop_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@spu_id", Value = spu_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@skc_id", Value = skc_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@cat_id", Value = cat_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@cat_name", Value = cat_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@ext_code", Value = ext_code, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@product_name", Value = product_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@main_img", Value = main_img, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@discount", Value = discount.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@price_text", Value = price_text, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@sku_purchase_price_json", Value = sku_purchase_price_json, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@notes", Value = notes, });

                var has = mySql.ExecuteFirst(sqlExist, paramLst.ToArray());
                if (has != "0")
                {
                    mySql.Close();
                    return "{\"code\":501,\"msg\":\"货号 " + ext_code + " 价格数据已存在\"}";
                }

                var rst = mySql.ExecuteNonQuery(sql, paramLst.ToArray());
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                {
                    return "{\"code\":502,\"msg\":\"" + mySql.Message + "\"}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string MdyPurchasePrice(string inputData, bool isAdmin = false)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                var db_id = RegexUtils.GetStringQuery(ref inputData, "db_id");
                string shop_id = RegexUtils.GetStringQuery(ref inputData, "shop_id");
                string shop_name = RegexUtils.GetStringQuery(ref inputData, "shop_name");
                string cat_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "cat_id")).ParseToString();
                string cat_name = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "cat_name")).ParseToString();
                string ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "ext_code")).ParseToString();
                string product_name = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "product_name")).ParseToString();
                string main_img = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "main_img")).ParseToString();
                string discount = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "discount")).ParseToString();
                string price_text = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "price_text")).ParseToString();
                string sku_purchase_price_json = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "sku_purchase_price_json")).ParseToString();
                string notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "notes")).ParseToString();

                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@db_id", Value = db_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@shop_id", Value = shop_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@shop_name", Value = shop_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@cat_id", Value = cat_id, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@cat_name", Value = cat_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@ext_code", Value = ext_code, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@product_name", Value = product_name, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@main_img", Value = main_img, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "@discount", Value = discount.ParseToInt(), });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@price_text", Value = price_text, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@sku_purchase_price_json", Value = sku_purchase_price_json, });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@notes", Value = notes, });

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);

                string sql = "update kjd.product_purchase_price set";
                if (shop_id != null)
                    sql += " shop_id=@shop_id,";
                if (shop_name != null)
                    sql += " shop_name=@shop_name,";
                if (cat_id != null)
                    sql += " cat_id=@cat_id,";
                if (cat_name != null)
                    sql += " cat_name=@cat_name,";
                if (ext_code != null)
                    sql += " ext_code=@ext_code,";
                if (product_name != null)
                    sql += " product_name=@product_name,";
                if (main_img != null)
                    sql += " main_img=@main_img,";
                if (discount != null)
                    sql += " discount=@discount,";
                if (price_text != null)
                    sql += " price_text=@price_text,";
                if (sku_purchase_price_json != null)
                    sql += " sku_purchase_price_json=@sku_purchase_price_json,";
                if (notes != null)
                    sql += " notes=@notes,";
                sql = sql.TrimEnd(',');
                sql += " where id=@db_id;";

                var rst = mySql.ExecuteNonQuery(sql, paramLst.ToArray());
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                {
                    return "{\"code\":500,\"msg\":\"更新失败：" + mySql.Message + "\"}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string GetPurchasePriceList(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name");
                var password = RegexUtils.GetStringQuery(ref inputData, "password");

                var search_spu_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_spu_id"));
                var search_skc_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_skc_id"));
                var search_ext_code = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_ext_code"));
                var search_notes = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "search_notes"));
                var in_spu_id = HtmlUtils.UrlDecodeUtf8(RegexUtils.GetStringQuery(ref inputData, "in_spu_id"));
                int page = RegexUtils.GetStringQuery(ref inputData, "page").ParseToInt();
                int page_size = RegexUtils.GetStringQuery(ref inputData, "page_size").ParseToInt(50);
                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }


                List<MySqlParameter> paramLst = new List<MySqlParameter>();
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_spu_id", Value = "%" + search_spu_id + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_skc_id", Value = "%" + search_skc_id + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_ext_code", Value = "%" + search_ext_code + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@search_notes", Value = "%" + search_notes + "%", });
                paramLst.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "@in_spu_id", Value = in_spu_id.ParseToString() });

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);

                string sql = "select count(id) from kjd.product_purchase_price p where 1=1";

                string where = "";
                if (!string.IsNullOrWhiteSpace(search_spu_id))
                    where += " and p.spu_id like @search_spu_id";
                if (!string.IsNullOrWhiteSpace(search_skc_id))
                    where += " and p.skc_id like @search_skc_id";
                if (!string.IsNullOrWhiteSpace(search_ext_code))
                    where += " and p.ext_code like @search_ext_code";
                if (!string.IsNullOrWhiteSpace(search_notes))
                    where += " and p.notes like @search_notes";
                if (!string.IsNullOrEmpty(in_spu_id))
                    where += " and p.spu_id in ('" + in_spu_id?.Replace(",", "','") + "')";
                sql += where;

                var count = mySql.ExecuteRowCount(sql, paramLst.ToArray());

                DataTable dt;
                if (count == 0)
                {
                    dt = new DataTable();
                }
                else
                {
                    sql = "select * from kjd.product_purchase_price p where 1=1";

                    //where子句
                    sql += where;

                    sql += " order by p.id desc";
                    sql += " limit " + Math.Max(page - 1, 0) * page_size + "," + page_size;
                    dt = mySql.ExecuteDataTable(sql, paramLst.ToArray());
                }

                mySql.Close();
                if (dt != null)
                {
                    try
                    {
                        List<ProductPurchasePriceResultParam> purchasePriceList = new List<ProductPurchasePriceResultParam>();
                        foreach (DataRow row in dt.Rows)
                        {
                            ProductPurchasePriceResultParam price = new ProductPurchasePriceResultParam
                            {
                                id = row["id"].ParseToInt(),
                                shop_id = row["shop_id"].ParseToString(),
                                shop_name = row["shop_name"].ParseToString(),
                                cat_id = row["cat_id"].ParseToString(),
                                cat_name = row["cat_name"].ParseToString(),
                                main_img = row["main_img"].ParseToString(),
                                discount = row["discount"].ParseToInt(),
                                price_text = row["price_text"].ParseToString(),
                                product_name = row["product_name"].ParseToString(),
                                skc_id = row["skc_id"].ParseToString(),
                                sku_purchase_price_json = row["sku_purchase_price_json"].ParseToString(),
                                spu_id = row["spu_id"].ParseToString(),
                                notes = row["notes"].ParseToString(),
                                ext_code = row["ext_code"].ParseToString(),
                                add_time = Convert.ToDateTime(row["add_time"]),
                                mdy_time = Convert.ToDateTime(row["mdy_time"]),
                            };
                            purchasePriceList.Add(price);
                        }
                        var data = JsonUtils.SerializeObjectToString(purchasePriceList);
                        return "{\"code\":200,\"msg\":\"success\",\"data\":" + data + ",\"count\":" + count + "}";
                    }
                    catch (Exception ex)
                    {
                        return "{\"code\":500,\"msg\":\"" + ex.Message + "\",\"data\":[],\"count\":" + 0 + "}";
                    }
                }
                else
                {
                    return "{\"code\":500,\"msg\":\"" + mySql.Message + "\",\"data\":[],\"count\":" + 0 + "}";
                }
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }

        public string DelPurchasePrice(string inputData)
        {
            if (!string.IsNullOrEmpty(inputData))
            {
                var time = RegexUtils.GetStringQuery(ref inputData, "time").ParseToInt();
                string time1 = RegexUtils.GetStringQuery(ref inputData, "code");
                if (time1 != EncrypUtil.Encryption(time))
                    return "{\"code\":50000,\"msg\":\"校验失败\"}";

                var user_name = RegexUtils.GetStringQuery(ref inputData, "user_name")?.Replace("'", "\\'");
                var id = RegexUtils.GetStringQuery(ref inputData, "db_id")?.Replace("'", "\\'");

                if (string.IsNullOrEmpty(user_name))
                {
                    return "{\"code\":500,\"msg\":\"没有用户名\"}";
                }

                string conn = "ConnKJD";
                MysqlUtils mySql = new MysqlUtils(conn);
                string sql = "delete from kjd.product_purchase_price where id='" + id + "'";

                var rst = mySql.ExecuteNonQuery(sql);
                mySql.Close();
                if (rst)
                {
                    return "{\"code\":200,\"msg\":\"success\"}";
                }
                else
                    return "{\"code\":500,\"msg\":\"删除失败：" + mySql.Message + "\"}";
            }
            return "{\"code\":19999,\"msg\":\"输入为空\"}";
        }
    }
}