using Produmex.Foundation.Data.Sbo;
using Produmex.Foundation.Data.Sbo.BusinessObjects;
using Produmex.Foundation.Data.Sbo.Utilities;
using Produmex.Foundation.Diagnostics;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.Providers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace AddUpdateData
{
    class Program
    {
        private static string CONNECTION_STRING = "ADDYOURCONNECTIONSTRINGHERE";
        private static readonly ILog s_log = LogProvider.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            try
            {
                DoSomething();
            }
            catch (Exception ex)
            {
                s_log.Error("", ex);
                Console.WriteLine(ex);
                //Console.ReadLine();
            }
        }

        public static void DoSomething()
        {
            using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
            {
                using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
                {
                    conn.Open();
                    PmxItemWarehouseDefaultLocationProvider pmxItemWarehouseDefaultLocationProvider = new PmxItemWarehouseDefaultLocationProvider(conn);

                    // Find Items that needs Update OR an Insert
                    string query = @"SELECT DISTINCT STOCK.""PmxWhsCode""
									, STOCK.""ItemCode""
									, STOCK.""StorLocCode""
									, DEF_LOC.""InternalKey""
									, DEF_LOC.""DefaultLocationZone""
									, DEF_LOC.""PmxOnHand""
									FROM
										(SELECT PMX_INVT.""ItemCode"", ""SEQUENCE"", PMX_INVT.""StorLocCode"", PMX_OSEL.""PmxWhsCode""
										FROM PMX_INVT 
										INNER JOIN PMX_OSSL ON PMX_INVT.""StorLocCode"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
										INNER JOIN PMX_OSEL ON PMX_INVT.""StorLocCode"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN'
										GROUP BY  PMX_INVT.""ItemCode"", ""SEQUENCE"", PMX_INVT.""StorLocCode"", PMX_OSEL.""PmxWhsCode""
										) AS STOCK
										LEFT JOIN	(SELECT PMX_ITWL.""InternalKey"",PMX_ITWL.""ItemCode"", PMX_ITWL.""DefaultLocationZone"", PMX_ITWL.""PmxWhsCode"", SUM(PMX_INVT.""Quantity"") AS ""PmxOnHand""
													FROM PMX_ITWL
													LEFT JOIN PMX_OSSL ON PMX_ITWL.""DefaultLocationZone"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
													LEFT JOIN PMX_OSEL ON PMX_ITWL.""DefaultLocationZone"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN' AND PMX_OSEL.""PmxWhsCode""=PMX_ITWL.""PmxWhsCode""
													LEFT JOIN PMX_INVT ON PMX_INVT.""StorLocCode""=PMX_ITWL.""DefaultLocationZone"" AND PMX_INVT.""ItemCode""=PMX_ITWL.""ItemCode""
													GROUP BY  PMX_ITWL.""InternalKey"",PMX_ITWL.""ItemCode"", PMX_ITWL.""DefaultLocationZone"", PMX_ITWL.""PmxWhsCode""
													) AS DEF_LOC ON DEF_LOC.""ItemCode"" = STOCK.""ItemCode"" AND DEF_LOC.""PmxWhsCode"" = STOCK.""PmxWhsCode""
									WHERE 1=1
									AND STOCK.""StorLocCode"" = (SELECT TOP 1 ""StorLocCode""
													FROM PMX_INVT 
													JOIN PMX_OSSL ON PMX_INVT.""StorLocCode"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
													JOIN PMX_OSEL ON PMX_INVT.""StorLocCode"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN'
													WHERE 1=1
													AND PMX_INVT.""ItemCode"" = STOCK.""ItemCode""
													AND PMX_OSEL.""PmxWhsCode"" = STOCK.""PmxWhsCode""
													ORDER BY ""SEQUENCE"",""StorLocCode""
													)
									--Only Location with a sequence (filter production input and output bin, dock, ...)
									AND STOCK.""SEQUENCE"" > 0
									--Only Items if stock on default location = 0
									AND ISNULL(DEF_LOC.""PmxOnHand"",0)=0
									";

                    // Run upper Query
                    using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
                    {
                        while (!rs1.EoF)
                        {
                            var key = rs1.GetTypedValue<int>("InternalKey");
							// INSERT Records if no existing InternalKey in PMX_ITWL
                            if (key == 0)
                            {
                                PmxItemWarehouseDefaultLocation entry = pmxItemWarehouseDefaultLocationProvider.GetNewBO();
                                entry.ItemCode = rs1.GetTypedValue<string>("ItemCode");
                                entry.PmxWhsCode = rs1.GetTypedValue<string>("PmxWhsCode");
                                entry.DefaultLocationZone = rs1.GetTypedValue<string>("StorLocCode");
                                pmxItemWarehouseDefaultLocationProvider.AddBO(entry);
                            }
                            // UPDATE existing Records using InternalKey in PMX_ITWL
							else
                            {
                                PmxItemWarehouseDefaultLocation entry = pmxItemWarehouseDefaultLocationProvider.GetBO(rs1.GetTypedValue<int>("InternalKey"));
                                entry.DefaultLocationZone = rs1.GetTypedValue<string>("StorLocCode");
                                pmxItemWarehouseDefaultLocationProvider.UpdateBO(entry, false, false);
                            }
                            rs1.MoveNext();
                        }
                    }
				}
                scope.Complete();
            }
        }
    }
}