/// <summary> /// Update table with Capacity /// </summary> /// <returns></returns> public async Task <int> Update_capacity_table() { try { //rw = new Update_pstgr_from_Ora<Crp>(); List <Crp> list_ora = new List <Crp>(); List <Crp> list_pstgr = new List <Crp>(); Parallel.Invoke( async() => { list_ora = await Ora_CRP(); list_ora.Sort(); }, async() => { list_pstgr = await PSTGR_CRP(); list_pstgr.Sort(); }); Changes_List <Crp> tmp = rw.Changes(list_pstgr, list_ora, new[] { "id" }, "id", "id"); list_ora = null; list_pstgr = null; return(await PSTRG_Changes_to_dataTable(tmp, "\"CRP\"", "id", null, null)); } catch (Exception e) { Loger.Log("Błąd importu CRP:" + e); return(1); } }
/// <summary> /// Update table with Calendar Days /// </summary> /// <returns></returns> public async Task <int> Update_calendar_table(string calendar_id) { try { if (calendar_id != "") { Update_pstgr_from_Ora <Calendar> rw = new Update_pstgr_from_Ora <Calendar>(); List <Calendar> list_ora = new List <Calendar>(); List <Calendar> list_pstgr = new List <Calendar>(); var dataObject = new ExpandoObject() as IDictionary <string, Object>; ORA_parameters Command_prepare = new ORA_parameters(); Parallel.Invoke(async() => { list_ora = await rw.Get_Ora("" + "SELECT calendar_id, counter, to_date(work_day) work_day, day_type, working_time, working_periods, objid, objversion " + "FROM ifsapp.work_time_counter " + "WHERE CALENDAR_ID='SITS' ", "Calendar_ORA"); list_ora.Sort(); }, async() => { list_pstgr = await rw.Get_PSTGR("Select * from work_cal WHERE CALENDAR_ID='SITS' order by counter", "Calendar_Pstgr"); list_pstgr.Sort(); }); Changes_List <Calendar> tmp = rw.Changes(list_pstgr, list_ora, new[] { "id" }, "id", "id"); list_ora = null; list_pstgr = null; return(await PSTRG_Changes_to_dataTable(tmp, "work_cal", "id", null, null)); } else { throw new Exception("Service Calendar not set in settings.xml file "); } } catch (Exception e) { Loger.Log("Błąd importu CRP:" + e); return(1); } }
/// <summary> /// Find changes /// </summary> /// <param name="Old_list"></param> /// <param name="New_list"></param> /// <param name="ID_column"></param> /// <param name="IntSorted_by"></param> /// <param name="guid_col"></param> /// <returns></returns> public IChanges_list <T> Changes(List <T> Old_list, List <T> New_list, string[] ID_column, string IntSorted_by, string guid_col) { Changes_List <T> modyfications = new Changes_List <T>(); try { List <T> _operDEl = new List <T>(); List <T> _operINS = new List <T>(); List <T> _operMOD = new List <T>(); int[] ID = new[] { 100000 }; int srt = 1000; int counter = 0; int guid_id = 10000; Dictionary <int, Type> P_types = new Dictionary <int, Type>(); T Row = new T(); IPropertyAccessor[] Accessors = Row.GetType().GetProperties() .Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray(); foreach (var p in Accessors) { string pt_name = p.PropertyInfo.Name.ToLower(); if (ID_column.Contains(pt_name)) { ID = (ID ?? Enumerable.Empty <int>()).Concat(Enumerable.Repeat(counter, 1)).ToArray(); } if (pt_name == IntSorted_by.ToLower()) { srt = counter; } if (pt_name == guid_col.ToLower()) { guid_id = counter; } P_types.Add(counter, p.PropertyInfo.PropertyType); counter++; } counter = 0; int max_old_rows = Old_list.Count; bool add_Record = false; foreach (T rows in New_list) { if (max_old_rows > counter) { while (Convert.ToInt64(Accessors[srt].GetValue(rows)) > Convert.ToInt64(Accessors[srt].GetValue(Old_list[counter]))) { _operDEl.Add(Old_list[counter]); counter++; if (max_old_rows <= counter) { break; } } if (max_old_rows > counter) { if (Convert.ToInt64(Accessors[srt].GetValue(rows)) == Convert.ToInt64(Accessors[srt].GetValue(Old_list[counter]))) { bool changed = false; int col = 0; foreach (var rw in Accessors) { if (!ID.Contains(col)) { Type pt = P_types[col]; var val1 = rw.GetValue(rows) == null ? null : Convert.ChangeType(rw.GetValue(rows), Nullable.GetUnderlyingType(pt) ?? pt, null); var val2 = rw.GetValue(Old_list[counter]) == null ? null : Convert.ChangeType(rw.GetValue(Old_list[counter]), Nullable.GetUnderlyingType(pt) ?? pt, null); if (val1 == null) { if (val2 != null) { changed = true; } } else { if (val2 == null) { if (val1 != null) { changed = true; } } else { if (!val1.Equals(val2)) { changed = true; break; } } } } col++; } if (changed) { Row = new T(); col = 0; foreach (var p in Accessors) { if (guid_id == col) { p.SetValue(Row, Accessors[guid_id].GetValue(Old_list[counter])); } else { p.SetValue(Row, p.GetValue(rows)); } col++; } _operMOD.Add(Row); } counter++; } else { add_Record = true; } } else { add_Record = true; } } else { add_Record = true; } if (add_Record) { _operINS.Add(rows); counter++; add_Record = false; } } var dataset = new Changes_List <T> { Insert = _operINS, Delete = _operDEl, Update = _operMOD }; modyfications = dataset; return(modyfications); } catch (Exception e) { Loger.Log("Błąd w procedurze porównania :" + e); return(modyfications); } }
/// <summary> /// Update customer order table /// </summary> /// <returns></returns> public async Task <int> Update_cust() { try { //rw = new Update_pstgr_from_Ora<Orders_row>(); List <Orders_row> list_ora = new List <Orders_row>(); List <Orders_row> list_pstgr = new List <Orders_row>(); Parallel.Invoke(async() => { list_ora = await Get_Ora_list(); list_ora.Sort(); Orders_list = list_ora; }, async() => { list_pstgr = await Get_PSTGR_List(); list_pstgr.Sort(); }); Changes_List <Orders_row> tmp = rw.Changes(list_pstgr, list_ora, new[] { "id", "zest", "objversion" }, "Custid", "id"); list_ora = null; list_pstgr = null; return(await PSTRG_Changes_to_dataTable(tmp, "cust_ord", "id", null, new[] { "update public.cust_ord a " + "SET zest=case when a.dop_connection_db = 'AUT' then " + "case when a.line_state='Aktywowana' then " + "case when dop_made=0 then " + "case when substring(a.part_no,1,1) not in ('5','6','2') " + "then b.zs " + "else null end "+ "else null end " + "else null end else null end " + "from " + "(select ZEST_ID,CASE WHEN zest>1 THEN zest_id ELSE null END as zs " + "from " + "(select a.order_no,a.line_no,b.zest,a.order_no||'_'||coalesce(a.customer_po_line_no,a.line_no)||'_'||a.prom_week ZEST_ID " + "from " + "cust_ord a " + "left join " + "(select id,count(zest) zest " + "from " + "(select order_no||'_'||coalesce(customer_po_line_no,line_no)||'_'||prom_week id,part_no zest " + "from cust_ord " + "where line_state!='Zarezerwowana' and dop_connection_db='AUT' and seria0=false " + "and data0 is null group by order_no||'_'||coalesce(customer_po_line_no,line_no)||'_'||prom_week,part_no ) a " + "group by id) b " + "on b.id=a.order_no||'_'||coalesce(a.customer_po_line_no,a.line_no)||'_'||a.prom_week " + "where substring(part_no,1,1) not in ('5','6','2') ) a) b " + "where a.order_no||'_'||coalesce(a.customer_po_line_no,a.line_no)||'_'||a.prom_week=b.ZEST_ID", "Delete from public.late_ord " + "where cust_id in (SELECT a.cust_id " + "FROM public.late_ord a " + "left join " + "public.cust_ord b " + "on a.cust_id=b.id " + "where b.id is null or b.line_state='Zarezerwowana' or b.dop_qty=b.dop_made)", "Delete from public.cust_ord_history " + "where id in " + "(SELECT a.id FROM " + "public.cust_ord_history a " + "left join " + "public.cust_ord b " + "on a.id=b.id " + "where b.id is null)" })); } catch (Exception e) { Loger.Log("Błąd importu zamówień klienta:" + e); return(1); } }
/// <summary> /// Set changes in database table /// </summary> /// <param name="_list"></param> /// <param name="name_table"></param> /// <param name="guid_col"></param> /// <returns></returns> public async Task <int> PSTRG_Changes_to_dataTable(Changes_List <T> _list, string name_table, string guid_col, string[] query_before, string [] query_after) { try { Dictionary <string, int> P_columns = new Dictionary <string, int>(); Dictionary <int, Type> P_types = new Dictionary <int, Type>(); T Row = new T(); IPropertyAccessor[] Accessors = Row.GetType().GetProperties() .Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray(); int counter = 0; foreach (var p in Accessors) { P_types.Add(counter, p.PropertyInfo.PropertyType); P_columns.Add(p.PropertyInfo.Name.ToLower(), counter); counter++; } List <Npgsql_Schema_fields> Schema = await Get_shema(name_table, P_columns); using (NpgsqlConnection conO = new NpgsqlConnection(npC)) { await conO.OpenAsync(); using (NpgsqlTransaction npgsqlTransaction = conO.BeginTransaction()) { using (NpgsqlCommand cmd = new NpgsqlCommand("" + "UPDATE public.datatbles " + "SET start_update=current_timestamp, in_progress=true,updt_errors=false " + "WHERE table_name=@table_name", conO)) { cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table; cmd.Prepare(); cmd.ExecuteNonQuery(); } if (query_before != null) { foreach (string comm in query_before) { using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO)) { cmd.ExecuteNonQuery(); } } } if (_list.Delete.Count > 0) { string comand = "DELETE FROM " + name_table; string tbl_values = " WHERE "; string param_values = "="; using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.Connection = conO; foreach (Npgsql_Schema_fields _Fields in Schema) { string nam = _Fields.Field_name; if (guid_col == nam && _Fields.Dtst_col != 10000) { tbl_values += nam; param_values = "=@" + nam.ToLower(); cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type); } } cmd.CommandText = comand + tbl_values + param_values; cmd.Prepare(); foreach (T row in _list.Delete) { cmd.Parameters[0].Value = Accessors[P_columns[guid_col]].GetValue(row); cmd.ExecuteNonQuery(); } } } if (_list.Update.Count > 0) { string comand = "UPDATE " + name_table + " SET"; string tbl_values = " "; string param_values = " WHERE "; using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.Connection = conO; foreach (Npgsql_Schema_fields _Fields in Schema) { string nam = _Fields.Field_name; if (_Fields.Dtst_col != 10000) { if (nam.ToLower() == guid_col.ToLower()) { param_values = param_values + nam + "=@" + nam.ToLower(); } else { tbl_values = tbl_values + nam + "=@" + nam.ToLower() + ","; } cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type); } } cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + " " + param_values; cmd.Prepare(); foreach (T row in _list.Update) { foreach (Npgsql_Schema_fields _field in Schema) { if (_field.Dtst_col != 10000) { cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value; } } cmd.ExecuteNonQuery(); } } } if (_list.Insert.Count > 0) { string comand = "INSERT INTO " + name_table; string tbl_values = "("; string param_values = " VALUES ("; using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.Connection = conO; foreach (Npgsql_Schema_fields _Fields in Schema) { string nam = _Fields.Field_name; if (_Fields.Dtst_col != 10000) { tbl_values = tbl_values + nam + ","; param_values = param_values + "@" + nam.ToLower() + ","; cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type); } } cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + ")" + param_values.Substring(0, param_values.Length - 1) + ")"; cmd.Prepare(); foreach (T row in _list.Insert) { foreach (Npgsql_Schema_fields _field in Schema) { if (_field.Field_name.ToLower() == guid_col.ToLower()) { if (_field.Field_type == NpgsqlTypes.NpgsqlDbType.Uuid) { cmd.Parameters[_field.DB_Col_number].Value = Guid.NewGuid(); } else { cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value; } } else if (_field.Dtst_col != 10000) { cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value; } } cmd.ExecuteNonQuery(); } } } if (query_after != null) { foreach (string comm in query_after) { using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO)) { cmd.ExecuteNonQuery(); } } } using (NpgsqlCommand cmd = new NpgsqlCommand("" + "UPDATE public.datatbles " + "SET last_modify=current_timestamp, in_progress=false " + "WHERE table_name=@table_name", conO)) { cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table; cmd.Prepare(); cmd.ExecuteNonQuery(); } npgsqlTransaction.Commit(); } } return(0); } catch (Exception e) { using (NpgsqlConnection conO = new NpgsqlConnection(npC)) { await conO.OpenAsync(); using (NpgsqlCommand cmd = new NpgsqlCommand("" + "UPDATE public.datatbles " + "SET in_progress=false,updt_errors=true " + "WHERE table_name=@table_name", conO)) { cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table; cmd.Prepare(); cmd.ExecuteNonQuery(); } } Loger.Log("Error in update table :" + name_table + " :" + e.StackTrace); return(1); } }