/// <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);
}
}
