HEGII.ss.mes.packingline/wwwroot/App_Code/RollingCapacity.cs

using System;
using System.Configuration;
using System.Text;

using Curtain.DataAccess;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

/// <summary>
/// RollingCapacity 的摘要说明
/// </summary>
public class RollingCapacity
{
    /// <summary>
    /// 三水陶瓷厂东科MES
    /// </summary>
    public const string http_domain = "https://hapcloud.hegii.com/api";
    public const string http_auth = "/auth/oauth/token";
    public const string http_url = "/mf/schemeForecast/getSchemeForecast";
    public const string grant_typ = "client_credentials";
    public const string client_id = "11f6868d1f7d4e8f8a66143c4740bea1";
    public const string client_secret = "da9086157c054fe5a4f9b273d66fd959";

    /// <summary>
    /// 拉取恒洁中台计划产销量
    /// </summary>
    /// <param name="today"></param>
    /// <returns></returns>
    public static string PullSchemeForecast(DateTime today)
    {
        string msg = "";
        string token = "";
        string json_data = "";
        string json_value = "";
        int year = today.Year;
        if (today.Month == 12)
        {
            year = year + 1;
        }
        try
        {
            token = HttpOAuth2.GetToken(http_domain + http_auth, new string[] { grant_typ, client_id, client_secret });
            Curtain.Log.Logger.Debug("token : " + token);

            JObject tokenObj = JsonConvert.DeserializeObject<JObject>(token);
            token = tokenObj["access_token"] + "";

            string SAP_werks = ConfigurationManager.AppSettings["SAP_werks"].ToString();

            json_data = @"{'werks':'" + SAP_werks + "', 'productCode':null, 'wealthYear': '" + year + "'}";
            json_value = HttpOAuth2.Post(http_domain + http_url, json_data, token);
            Curtain.Log.Logger.Debug("data : " + json_value);

            JObject returnObj = JsonConvert.DeserializeObject<JObject>(json_value);

            if (json_value.Contains("code"))
            {
                // 插入 中台 计划产量预测
                using (IDataAccess conn = DataAccess.Create())
                {
                    JArray paramsArray = (JArray)returnObj["data"];
                    conn.BeginTransaction();
                    conn.ExecuteNonQuery("delete from t_reckon_prod_diff t where t.data_type = 100 and t.cw_year = @cw_year@", new CDAParameter("cw_year", year));
                    //string sql = "insert into t_reckon_prod_diff (cw_year, cw_month, reckon_month, sap_sku, goodsid, data_type, data_value) " +
                    //    "values (@cw_year@, @cw_month@, @reckon_month@, @sap_sku@, @goodsid@, 100, @data_value@)";
                    //foreach (JToken param in paramsArray)
                    //{
                    //    string productCode = param["productCode"] + "";
                    //    //CDAParameter p_year = new CDAParameter("cw_year", today.Year);
                    //    //CDAParameter p_month = new CDAParameter("cw_month", 12);
                    //    //CDAParameter p_reckon_month = new CDAParameter("reckon_month", new DateTime(today.Year, 12, 1));
                    //    //CDAParameter p_sap_sku = new CDAParameter("sap_sku", productCode);
                    //    //CDAParameter p_goodsid = new CDAParameter("goodsid", null);
                    //    //CDAParameter p_data_value = new CDAParameter("data_value", 0);
                    //    // 12月
                    //    //p_data_value.Value = (int)param["qty12"];
                    //    //conn.ExecuteNonQuery();
                    //}

                    StringBuilder tmp_sql = new StringBuilder(" insert all ");
                    tmp_sql.AppendLine();
                    string sql_in = "into t_reckon_prod_diff (cw_year, cw_month, reckon_month, sap_sku, goodsid, data_type, data_value, SKU_NAME, SALES_CATEGORY_THREE, SAP_DATATYPE,SAP_DATAVERSION,SAP_DATASOURCE) values (";
                    foreach (JToken param in paramsArray)
                    {
                        string productCode = "'" + param["productCode"] + "'";
                        string product_name = ",'" + param["product_name"] + "'";
                        string sales_category_three = ",'" + param["sales_category_three"] + "'";
                        string dataType = ",'" + param["dataType"] + "'";
                        string dataVersion = ",'" + param["dataVersion"] + "'";
                        string dataSource = ",'" + param["dataSource"] + "'";
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(12);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year - 1) + "-12-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty12"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(1);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-01-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty1"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(2);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-02-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty2"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(3);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-03-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty3"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(4);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-04-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty4"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(5);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-05-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty5"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(6);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-06-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty6"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(7);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-07-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty7"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(8);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-08-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty8"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(9);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-09-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty9"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(10);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-10-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty10"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                        tmp_sql.Append(sql_in);
                        tmp_sql.Append(year);
                        tmp_sql.Append(",");
                        tmp_sql.Append(11);
                        tmp_sql.Append(",");
                        tmp_sql.Append("date'" + (year) + "-11-01'");
                        tmp_sql.Append(",");
                        tmp_sql.Append(productCode);
                        tmp_sql.Append(",");
                        tmp_sql.Append("null");
                        tmp_sql.Append(",");
                        tmp_sql.Append(100);
                        tmp_sql.Append(",");
                        tmp_sql.Append(GetQty(param["qty11"]));
                        tmp_sql.Append(product_name);
                        tmp_sql.Append(sales_category_three);
                        tmp_sql.Append(dataType);
                        tmp_sql.Append(dataVersion);
                        tmp_sql.Append(dataSource);
                        tmp_sql.AppendLine(")");
                    }
                    tmp_sql.AppendLine("select 1 from dual");

                    int result = conn.ExecuteNonQuery(tmp_sql.ToString());
                    conn.Commit();
                    msg += " 【t_reckon_prod_diff】操作成功";
                }

                // 刷新差异
                using (IDataAccess conn = DataAccess.Create())
                {
                    conn.BeginTransaction();
                    int result = conn.ExecuteSPNonQuery("pro_auto_reckon_prod_diff", new CDAParameter("@in_accountid@", 1), new CDAParameter("@in_date@", today));
                    conn.Commit();
                    msg += " 【pro_auto_reckon_prod_diff】操作成功";
                }
            }
            return msg;
        }
        catch (Exception e)
        {
            return "操作失败：" + e.Message;
        }
    }

    private static string GetQty(object value)
    {
        string s = value + "";
        if (s == null || s == "" || s == "null")
        {
            return "0";
        }
        return s;
    }
}