using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Quartz;

using System.Data;

namespace ICSSoft.FromERP
{
    /// <summary>
    /// 物料档案(凯尔克)
    /// </summary>
    public class ICSInventory : IJob
    {
        private static object key = new object();
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public void Execute(IJobExecutionContext context)
        {
            try
            {
                lock (key)
                {
                    log.Info("开始……………………………………………………………………");
                    Execute();
                    log.Info("结束……………………………………………………………………");
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }

        public void Execute()
        {
            try
            {
                //string conERPStr = ICSHelper.GetERPConnectString();
                string conStr = ICSHelper.GetConnectString();
                string Namespace = this.GetType().Namespace;
                //string Class = this.GetType().Name;

                DataTable dt = ICSHelper.GetERPDB(conStr);
                foreach (DataRow dr in dt.Rows)
                {
                    string erpName = ICSHelper.GetConfigString()["ERPDB"];
                    string TenantId = dr["TenantId"].ToString();
                    string TenantCode = dr["TenantCode"].ToString();
                    string ErpId = dr["ErpID"].ToString();      //erpID
                    string Class = this.GetType().Name + TenantCode;
                    erpName = string.Format(erpName, TenantId);

                    #region SQL
                    string sql = @" select A.ID AS ID,
    A.Code AS ItemCode
,a.ItemFormAttribute
    ,dbo.GetReplaceStr(A.Name) AS ItemName,
a.DescFlexField_PrivateDescSeg2 ItemDrawingNo
	,b.Description ItemDesc,
    dbo.GetReplaceStr(A.SPECS) AS ItemStd,
    D.Name AS ItemUnit,
    G.Code as ItemMainCategoryCode,
    H.Name as ClassName,
    1 as ItemRate,
    1 as EffectiveDays,
   A.ModifiedOn as MTime
     ,I.id as Org
    ,Case When Q.IsSueOverType=0 THEN 1 WHEN  Q.IsSueOverType=1 THEN 2 WHEN  Q.IsSueOverType=2 THEN 3 ELSE 0 END AS 备料超额类型
    ,Case When Q.IsSueOverType=0 Then Q.StandardMaterialScale When Q.IsSueOverType=1 Then Q.StandardMaterialQty
    ELSE 0 END AS 备料超额值
    INTO #TempINV
                                        from {1}.dbo.[CBO_ItemMaster] AS A 
                                        LEFT JOIN {1}.dbo.[CBO_ItemMaster_Trl] AS B ON ( A.[ID] = B.[ID]  AND B.SysMLFlag='zh-CN')
                                        LEFT JOIN {1}.dbo.[Base_UOM] AS C ON ( A.[CostUOM] = C.[ID] )
                                        LEFT JOIN {1}.dbo.[Base_UOM_Trl] AS D ON ( C.[ID] = D.[ID]   AND D.SysMLFlag='zh-CN')
                                        LEFT JOIN {1}.dbo.[Base_UOM] AS E ON ( A.[InventoryUOM] = E.[ID] )
                                        LEFT JOIN {1}.dbo.[Base_UOM_Trl] AS F ON  ( E.[ID] = F.[ID]   AND F.SysMLFlag='zh-CN')
                                        LEFT JOIN {1}.dbo.[CBO_Category] AS G ON ( A.[MainItemCategory] = G.[ID] )
                                        LEFT JOIN {1}.dbo.[CBO_Category_Trl] AS H ON ( G.[ID] = H.[ID]   AND H.SysMLFlag='zh-CN')
                                        LEFT JOIN {1}.dbo.[Base_Organization] AS I ON ( A.[Org] = I.[ID] )
                                        LEFT JOIN {1}.dbo.[Base_Organization_Trl] AS J ON ( I.[ID] = J.[ID]   AND J.SysMLFlag='zh-CN') 
                                        LEFT JOIN {1}.dbo.CBO_InventoryInfo L on A.id=L.ItemMaster  
                                        LEFT JOIN {1}.dbo.CBO_Wh M on L.warehouse=M.id and A.org=M.org
									LEFT JOIN {1}.dbo.CBO_Category_Trl P ON P.ID=A.MainItemCategory
                                        LEFT JOIN {1}.dbo.CBO_MfgInfo Q ON Q.ItemMaster=A.ID
                                        where A.ModifiedOn>=@LastTime AND I.id={0}
                                        ORDER BY A.Code

                                        IF NOT EXISTS(SELECT * FROM #TempINV)
                                        RETURN";
                    sql = string.Format(sql, ErpId, erpName);
                    sql = ICSHelper.Time(Namespace, Class, TenantId, sql, "#TempINV");

                    sql += @"--删除数据
                               
                                SELECT a.ItemCode INTO #tableICSINVENTORY
                                FROM ICSINVENTORY a With(NoLock)
                                LEFT JOIN {1}.dbo.[CBO_ItemMaster] b With(NoLock) ON a.ItemCode=b.code
	                            WHERE b.code is null 
						        and  a.TenantId='{0}'
						        AND b.Org={2}

                                DELETE ICSINVENTORY 
                                WHERE TenantId='{0}' and  ItemCode IN ( SELECT ItemCode from #tableICSINVENTORY) ";
                    Dictionary<string, string> values = new Dictionary<string, string>();
                    values.Add("ItemCode", "a.ItemCode");//物料编号
                    values.Add("ItemName", "a.ItemName");//物料名称
                    values.Add("ItemDesc", "a.ItemDesc");//物料描述
                    values.Add("ItemStd", "a.ItemStd");//规格型号
                    values.Add("ItemAttribute", "a.ItemFormAttribute");//规格型号
                    values.Add("ItemUnit", "a.ItemUnit");//主计量单位
                    values.Add("ItemMainCategoryCode", "a.ItemMainCategoryCode");//大类编码
                    values.Add("ItemRate", "a.ItemRate");
                    values.Add("InvVersion", "''");
                    values.Add("InvBrand", "''");
                    values.Add("InvIQC", "0");//大类编码
                    values.Add("InvFQC", "0");//大类编码
                    values.Add("EffectiveEnable", "0");//大类名称
                    values.Add("EffectiveDays", "a.EffectiveDays");//大类名称
                    values.Add("BatchEnable", "1");//大类名称
                    values.Add("LotEnable", "0");//大类名称
                    values.Add("PrintEnable", "0");//大类名称
                    values.Add("OverType", "a.备料超额类型");
                    values.Add("OverValue", "a.备料超额值");
                    values.Add("LastModificationTime", "a.Mtime");//操作时间
                    values.Add("ItemType", "d.ItemType");//操作时间
                    values.Add("ItemContype", "d.ItemContype");//操作时间
                    values.Add("ItemControlType", "d.ItemControlType");//操作时间
                    values.Add("ItemDrawingNo", "a.ItemDrawingNo");//操作时间


                    //更新存在数据
                    sql += ICSHelper.UpdateSQL("b", values)
                        + @" #TempINV a
                            INNER JOIN ICSINVENTORY b ON a.ItemCode=b.ItemCode  
	left join IcsMainCategoryCode d on b.ItemMainCategoryCode=d.ItemMainCategoryCode and b.TenantId=d.TenantId
                            WHERE b.TenantId='" + TenantId + "' and  a.Org={2} ";

                    values.Add("CreationTime", "a.MTime");//操作人
                    values.Add("CreatorUserId", "'" + ConstWorkPoint.Muser + "'");//操作人
                    values.Add("CreatorUserName", "'" + ConstWorkPoint.Musername + "'");//操作人名称
                    values.Add("TenantId", "'" + TenantId + "'");//站点
                                                                 //插入新增数据
                    sql += ICSHelper.InsertSQL("ICSINVENTORY", values)
                        + @"  #TempINV a
                            LEFT JOIN ICSINVENTORY b ON a.ItemCode=b.ItemCode and b.TenantId='{0}'
	left join IcsMainCategoryCode d on b.ItemMainCategoryCode=d.ItemMainCategoryCode and b.TenantId=d.TenantId
                            WHERE 1=1	and b.ItemCode is null 
	and  a.Org={2} ";
                    sql += " DROP TABLE #TempINV " +
                        " DROP TABLE #tableICSINVENTORY ";
                    sql = string.Format(sql, TenantId, erpName, ErpId);
                    // return;
                    log.Info(sql);
                   // return;
                    ICSHelper.ExecuteDate(conStr, sql);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}