public static void PushGpDataToZudello() { string Token = ZudelloLogin.Login(); SQLCredentials ConnectionString = new SQLCredentials(); Dictionary <int, string> Connection = ConnectionString.ConnectionStringBuilder(); /**** * * Add threading into here for the foreach loop * will need to chunk based on amount of connections */ //get the query from mappings table foreach (var Mappings in getBody()) { //bool isXml = false; //Get the SQL statement string mySql = Mappings.Value.Body; //Get the Mappings dynamic zudelloObject = Mappings.Value; string SQLQuery = GpTools.RenderToSql(mySql, zudelloObject); //if (mySql.Contains("XML PATH")) isXml = true; #warning make this in config also for process method string cmd = SQLQuery; //Open SQL connection and run the SQL query //Get connection details by Key ID SqlConnection con = new SqlConnection(Connection[Mappings.Value.connection_id]); SqlCommand SelectCommand = new SqlCommand(cmd, con); // SqlDataReader myreader; con.Open(); var jsonResult = new StringBuilder(); var myreader = SelectCommand.ExecuteReader(); if (!myreader.HasRows) { //if there is no data then close connection and next loop con.Close(); continue; } else { while (myreader.Read()) { jsonResult.Append(myreader.GetValue(0).ToString()); } dynamic obj; //Console.WriteLine(jsonResult); obj = JsonConvert.DeserializeObject <ExpandoObject>(jsonResult.ToString()); foreach (var ObjectType in obj) { foreach (var dataValues in ObjectType.Value) { MyQueueObject dataWrapper = new MyQueueObject(); dataWrapper.data = dataValues; string data = JsonConvert.SerializeObject(dataWrapper); Console.WriteLine(data); Console.WriteLine(ZudelloLogin.SendToZudelloQueue(Token, data)); } } //Update last sync time for this mapping ID using (var db = new ZudelloContext()) { var lastSync = db.Zlastsync.Where(s => s.MappingId == Mappings.Value.Id).FirstOrDefault(); lastSync.LastSync = DateTime.Now.ToString(); db.SaveChanges(); } } con.Close(); } }
public static GpValidations SupplierInvoiceValidate(string Supplierinvoice, GpMappingAndDatabase mapping) { dynamic validator = JsonConvert.DeserializeObject <ExpandoObject>(Supplierinvoice); GpValidations validate = new GpValidations(); string ExoCode = validator.document.supplier.code.ToString().ToUpper(); SQLCredentials ConnectionString = new SQLCredentials(); Dictionary <int, string> Connection = ConnectionString.ConnectionStringBuilder(); string SQLQuery = String.Format("SELECT Accno FROM CR_ACCS where Name = '{0}' or Alphacode = '{0}'", ExoCode); try { SqlConnection con = new SqlConnection(Connection[mapping.Id]); // SqlDataReader myreader; con.Open(); using (SqlCommand command = new SqlCommand(SQLQuery, con)) { var reader = command.ExecuteReader(); if (reader.Read()) { //Do nothing Item Exists in Exo } else { validate.CreateSupplier = true; } reader.Close(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } List <string> SkuList = new List <string>(); foreach (var line in validator.document.lines) { string stkItem = ""; try { stkItem = line.item.sku.ToString().ToUpper(); } catch (Exception ex) { // if the sku is null will thow runtime binding error, this is fine as it is a GL invoice. Console.WriteLine(ex.Message + " GL invioce"); continue; } string selectItems = String.Format("Select stockcode from IV00101 where stockcode = '{0}'", stkItem); try { SqlConnection con = new SqlConnection(Connection[mapping.Id]); // SqlDataReader myreader; con.Open(); //Get a list of all ther table names in the Database using (SqlCommand command = new SqlCommand(selectItems, con)) { var reader = command.ExecuteReader(); if (reader.Read()) { //Do nothing Item Exists in Exo } else { validate.InventoryToCreate.Add(stkItem); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } } if (validate.InventoryToCreate.Count() > 0) { validate.CreateInventory = true; } return(validate); }
public static Validations ExoSupplierInvoice(string Supplierinvoice, ExoMappingAndDB mapping) { dynamic validator = JsonConvert.DeserializeObject <ExpandoObject>(Supplierinvoice); Validations validate = new Validations(); string ExoCode = ""; string SQLQuery = ""; try { ExoCode = validator.document.supplier.linked.accountNumber.ToString().ToUpper().Trim(); SQLQuery = String.Format("SELECT Accno FROM CR_ACCS where accno = '{0}'", ExoCode); } catch (Exception ex) { try { ExoCode = validator.document.supplier.code; SQLQuery = String.Format("SELECT X_ZCode FROM CR_ACCS where X_ZCode = '{0}'", ExoCode.ToUpper().Trim()); } catch (Exception ex2) { } } SQLCredentials ConnectionString = new SQLCredentials(); Dictionary <int, string> Connection = ConnectionString.ConnectionStringBuilder(); try { SqlConnection con = new SqlConnection(Connection[mapping.Id]); // SqlDataReader myreader; con.Open(); using (SqlCommand command = new SqlCommand(SQLQuery, con)) { var reader = command.ExecuteReader(); if (reader.Read()) { //Do nothing Item Exists in Exo } else { validate.CreateSupplier = true; } reader.Close(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } List <string> SkuList = new List <string>(); foreach (var line in validator.document.lines) { if (validator.document.docType.ToString() == "PURCHASING::EXPENSE") { //Maybe need to revise in future break; } string stkItem = ""; try { stkItem = line.item.sku.ToString().ToUpper(); } catch (Exception ex) { // if the sku is null will thow runtime binding error, this is fine as it is a GL invoice. Console.WriteLine(ex.Message + " GL invioce"); continue; } string selectItems = String.Format("Select stockcode from stock_items where stockcode = '{0}'", stkItem); try { SqlConnection con = new SqlConnection(Connection[mapping.Id]); // SqlDataReader myreader; con.Open(); //Get a list of all ther table names in the Database using (SqlCommand command = new SqlCommand(selectItems, con)) { var reader = command.ExecuteReader(); if (reader.Read()) { //Do nothing Item Exists in Exo } else { validate.InventoryToCreate.Add(stkItem); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } } if (validate.InventoryToCreate.Count() > 0) { validate.CreateInventory = true; } return(validate); }
public static async Task PushExoDataToZudello() { string Token = ZudelloLogin.Login(); SQLCredentials ConnectionString = new SQLCredentials(); Dictionary <int, string> Connection = ConnectionString.ConnectionStringBuilder(); /**** * * Add threading into here for the foreach loop * will need to chunk based on amount of connections */ //get the query from mappings table Dictionary <int, Zmapping> MappingsBody = getBody(); int numberOfLogicalCores = Environment.ProcessorCount; List <Thread> threads = new List <Thread>(numberOfLogicalCores); int sizeOfOneChunk = (MappingsBody.Count / numberOfLogicalCores) + 1; ConcurrentBag <string> cb = new ConcurrentBag <string>(); ConcurrentBag <int> cbMaps = new ConcurrentBag <int>(); for (int i = 0; i < numberOfLogicalCores; i++) { int ab = i; var thr = new Thread( () => { try { int count = 0; Dictionary <int, Zmapping> MappingsChunked = MappingsBody.Skip(ab * sizeOfOneChunk) .Take(sizeOfOneChunk).ToDictionary(p => p.Key, p => p.Value); foreach (var Mappings in MappingsChunked) { bool isXMl = true; //Get the SQL statement string mySql = Mappings.Value.Body; //Get the Mappings dynamic zudelloObject = Mappings.Value; string SQLQuery = ExoTools.RenderToSql(mySql, zudelloObject); #warning make this in config also for process method string cmd = SQLQuery; //Open SQL connection and run the SQL query //Get connection details by Key ID SqlConnection con = new SqlConnection(Connection[Mappings.Value.connection_id]); SqlCommand SelectCommand = new SqlCommand(cmd, con); // SqlDataReader myreader; con.Open(); if (cmd.ToLower().Contains("for xml")) { isXMl = true; } if (String.IsNullOrEmpty(cmd)) { continue; } var jsonResult = new StringBuilder(); var xmlConvert = new StringBuilder(); //SelectCommand.CommandText = cmd; var myreader = SelectCommand.ExecuteReader(); if (!myreader.HasRows) { //if there is no data then close connection and next loop con.Close(); con.Dispose(); continue; } else { while (myreader.Read()) { jsonResult.Append(myreader.GetValue(0).ToString()); } //Console.WriteLine(jsonResult); dynamic obj = ""; string ConvertedXml = ""; if (isXMl == true) { ConvertedXml = ExoTools.ZudelloXMLConverter(jsonResult.ToString()); // string ConvertedXml = ExoTools.ZudelloXMLConverter(jsonResult.ToString()); obj = JsonConvert.DeserializeObject <ExpandoObject>(ConvertedXml); } else { obj = JsonConvert.DeserializeObject <ExpandoObject>(jsonResult.ToString()); } foreach (var ObjectType in obj) { //not sure why .Value is causing error but not affecting build foreach (var dataValues in ObjectType.Value) { string data = ""; if (isXMl == true) { data = JsonConvert.SerializeObject(dataValues); } else { MyQueueObject dataWrapper = new MyQueueObject(); dataWrapper.data = dataValues; data = JsonConvert.SerializeObject(dataWrapper); } // add to concurrent bag cb.Add(data); count++; // Console.WriteLine(data); } } cbMaps.Add(Mappings.Value.Id); } con.Close(); con.Dispose(); } } catch (Exception ex) { } } ); threads.Add(thr); } foreach (var thread in threads) { thread.Start(); } foreach (var thread in threads) { thread.Join(); } threads.Clear(); int sizeOfOneChunkQueue = (cb.Count / numberOfLogicalCores) + 1; for (int x = 0; x < numberOfLogicalCores; x++) { int abx = x; var SendToQueuethr = new Thread( () => { try { int count = 0; List <string> queuedBag = cb.Skip(abx * sizeOfOneChunkQueue) .Take(sizeOfOneChunkQueue).ToList(); foreach (var Mappings in queuedBag) { Console.WriteLine(ZudelloLogin.SendToZudelloQueue(Token, Mappings)); } } catch (Exception ex) { } }); threads.Add(SendToQueuethr); } foreach (var qthread in threads) { qthread.Start(); } foreach (var qthread in threads) { qthread.Join(); } /* * List<Task> bagConsumeTasks = new List<Task>(); * int itemsInBag = 0; * while (!cb.IsEmpty) * { * bagConsumeTasks.Add(Task.Run(() => * { * string item; * if (cb.TryTake(out item)) * { * * Console.WriteLine(ZudelloLogin.SendToZudelloQueue(Token, item)); * itemsInBag++; * } * })); * } */ // Task.WaitAll(bagConsumeTasks.ToArray()); foreach (int id in cbMaps) { using (var db = new ZudelloContext()) { var lastSync = db.Zlastsync.Where(s => s.MappingId == id).FirstOrDefault(); lastSync.LastSync = DateTime.Now.ToString(); db.SaveChanges(); db.DisposeAsync(); } } }
public static ProccessResponse ProcessMethod(dynamic queueData) { ProccessResponse pr = new ProccessResponse(); //Get list of Databases SQLCredentials ConnectionString = new SQLCredentials(); Dictionary <int, string> Connection = ConnectionString.ConnectionStringBuilder(); Console.WriteLine(queueData.queue.Id.ToString()); dynamic zudelloObject = ""; try { zudelloObject = JsonConvert.DeserializeObject <ExpandoObject>(queueData.queue.Body); string order = queueData.map.ProcessOrder.ToString(); //Created at string obj = queueData.map.DocType.ToString(); string uuid = "";//zudelloObject.invoiceUUID.ToString(); string queueID = queueData.queue.Id.ToString(); //Generate the insert Query string SQLQuery = ExoTools.RenderToSql(queueData.map.Body, zudelloObject); if (zudelloObject.uuid != null) { pr.SyncHistryUuid = zudelloObject.uuid; pr.Team = zudelloObject.document.teamUuid; } string cmd = SQLQuery; //Connect to correct database using (SqlConnection mConnection = new SqlConnection(Connection[queueData.map.connection_id])) { mConnection.Open(); //Get a list of all ther table names in the Database using (SqlCommand command = new SqlCommand(cmd, mConnection)) { command.ExecuteNonQuery(); } mConnection.Dispose(); // mConnection.Dispose(); } pr.Successful = true; pr.Information = "Success"; return(pr); } catch (Exception ex) { // Console.WriteLine(ex.Message); pr.Successful = false; pr.Information = String.Format("Exception Message: {0} ||" + " Exception Stack Trace: {1} ||" + " Exception Inner Ex: {2} ", ex.Message, ex.StackTrace, ex.InnerException); return(pr); } }