using System;
using System.Configuration;
using System.Text;
using Curtain.DataAccess;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
///
/// RollingCapacity 的摘要说明
///
public class RollingCapacity
{
///
/// 三水陶瓷厂东科MES
///
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";
///
/// 拉取恒洁中台计划产销量
///
///
///
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(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(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;
}
}