protected void LastAction_RiferimentiFornitori(ref DBHelper2 cm) { _logger.Info("Inizio inserimento distinte base fornitori"); Dictionary <string, string> lista = Get_Lista_Articoli_Fornitori_Sage("SAURO"); //per ogni fornitore/articolo inserisco una nuova distinta base con magazzino diverso string query = @" INSERT INTO CYB_COMPONENT ( c2.C_BOM_CODE ,c2.C_BOM_ALT ,c2.C_COMPONENT_CODE ,c2.C_COMPONENT_PLANT ,c2.C_OPNUM ,c2.C_NSEQ ,c2.C_PHANTOM ,c2.C_VAR_QTY ,c2.C_SCRAP_TYPE ,c2.C_PCT_SCRAP ,c2.C_WAREHOUSE_CODE ,c2.C_EFFECTIVE_DATE ,c2.C_EXPIRE_DATE ,c2.C_USER_STRING01 ,c2.C_USER_INT01 ,c2.C_USER_INT02 ,c2.C_USER_CHAR01 ,c2.C_USER_CHAR02 ) SELECT C_BOM_CODE ,C_BOM_ALT ,C_COMPONENT_CODE ,C_COMPONENT_PLANT ,C_OPNUM ,C_NSEQ ,C_PHANTOM ,C_VAR_QTY ,C_SCRAP_TYPE ,C_PCT_SCRAP ,'%%1%%' as C_WAREHOUSE_CODE ,C_EFFECTIVE_DATE ,C_EXPIRE_DATE ,C_USER_STRING01 ,C_USER_INT01 ,C_USER_INT02 ,C_USER_CHAR01 ,C_USER_CHAR02 FROM CYB_COMPONENT c2 WHERE C_WAREHOUSE_CODE = '" + __MAGAZZINO_INTERNO + @"' and C_BOM_CODE ='%%2%%' "; foreach (KeyValuePair <string, string> itm in lista) { string ins_query = query.Replace("%%1%%", itm.Value); //fornitore ins_query = ins_query.Replace("%%2%%", itm.Key); //articolo int i = DBHelper2.EseguiSuDBCyberPlan(ref cm, ins_query); } _logger.Info("Fine inserimento distinte base fornitori"); }
public override void DeleteTaskNumber(int taskNumberToDelete) { string qry = @"DELETE FROM [CyberPlanFrontiera].[dbo].[EXP_CORDER] WHERE [TASK_NUMBER] = " + taskNumberToDelete; DBHelper2.EseguiSuDBCyberPlan(ref _db, qry); _logger.Info("task number deleted =" + taskNumberToDelete); }
static public void Init() { DBHelper2 db = DBHelper2.getCyberDBHelper(); string exec_store_procedure = "EXECUTE [CyberPlanFrontiera].[dbo].[FILL_E_ROUTING]"; _logger.Info("start execution"); int i = DBHelper2.EseguiSuDBCyberPlan(ref db, exec_store_procedure, 600); _logger.Info("end execution"); }
static public void SetStatus(string stato) { DBHelper2 db = DBHelper2.getCyberDBHelper(); string command = "UPDATE [CyberPlanFrontiera].[dbo].[TRANSFER_STATUS] " + " SET [STATUS] = '" + stato + "' ," + " TIMESTAMP = '" + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'" + " WHERE ID='TR_STATUS' "; _logger.Info("start execution"); int i = DBHelper2.EseguiSuDBCyberPlan(ref db, command, 600); _logger.Info("end execution"); }
static void Update_FAMP(ref DBHelper2 cm) { //// caratteristiche //FAMP ... //E estrattori //DKTHQ plurime // X tutto il resto /* * Query per avere le caratteristiche valide: * quelle dell'articolo hanno la precedenza su quelle della famiglia versione * quelle della famiglia vanno considerate solo se l'articolo non ha la stessa caratteristica impostata * * SELECT a.itmref, a.valcod, a.valval, a.valtxt,a.PRIORITA * FROM [CyberPlanFrontiera].[dbo].[YITMCAR] a * INNER JOIN ( * SELECT itmref, valcod, MAX([PRIORITA]) PRIORITA * FROM [CyberPlanFrontiera].[dbo].[YITMCAR] * GROUP BY itmref,valcod * ) b ON a.itmref = b.itmref and a.valcod=b.valcod AND a.priorita = b.priorita * order by a.itmref,a.valcod */ /* * QUERY per recuperare le caratteristiche FAMP * * SELECT a.itmref, * --a.valcod, a.valval, a.valtxt,a.PRIORITA, * case a.VALCOD * when '028' then 'F' * when '006' then 'A' * when '038' then 'M' * when '051' then 'P' * end AS FAMP * FROM [CyberPlanFrontiera].[dbo].[YITMCAR] a * INNER JOIN ( * SELECT itmref, valcod, MAX([PRIORITA]) PRIORITA * FROM [CyberPlanFrontiera].[dbo].[YITMCAR] * GROUP BY itmref,valcod * ) b ON a.itmref = b.itmref and a.valcod=b.valcod AND a.priorita = b.priorita * where * ( * a.valcod = '028' or * a.valcod = '006' or * a.valcod = '038' or * a.valcod = '051' * ) * order by a.itmref,a.valcod desc * * * */ //query che prende le caratt FAMP (articlo e famiglia versione) e aggiorna gli articoli //nei casi ci siano caratt multiple ne viene presa una a caso (l'ultima nell'ordine di sql) /* * Con una query grop by seleziono per ogni articolo/caratteristica la MAX priorità * Poi con un join recupero valore con priorità massima ed eventuale valore TESTO (per plurime) */ string upd_query = @"update [CyberPlanFrontiera].dbo.CYB_ITEM set C_USER_CHAR03 = C.FAMP from ( SELECT a.itmref, --a.valcod, a.valval, a.valtxt,a.PRIORITA, case a.VALCOD when '028' then 'F' when '006' then 'A' when '038' then 'M' when '051' then 'P' when '057' then 'E' when '150' then a.VALTXT end AS FAMP FROM [CyberPlanFrontiera].[dbo].[YITMCAR] a INNER JOIN ( SELECT itmref, valcod, MAX([PRIORITA]) PRIORITA FROM [CyberPlanFrontiera].[dbo].[YITMCAR] GROUP BY itmref,valcod ) b ON a.itmref = b.itmref and a.valcod=b.valcod AND a.priorita = b.priorita where a.valcod = '028' or a.valcod = '006' or a.valcod = '038' or a.valcod = '051' or a.valcod = '057' or a.valcod = '150' --order by a.itmref,a.valcod desc ) C where C_CODE = C.ITMREF; update CyberPlanFrontiera.dbo.CYB_ITEM set [C_USER_CHAR03 ]='X' where [C_USER_CHAR03 ] =' 'and [C_USER_STRING01 ] like 'PLAS' "; int i = DBHelper2.EseguiSuDBCyberPlan(ref cm, upd_query); }
public void WriteToCyberPlan <T>(bool mode_all, string codice_like, string filtro, bool delete, string option) where T : Item, new() { Item.ResetMailMessage(); string startedAt = DateTime.Now.ToString(); string message_error = "Command started at " + startedAt + " " + "\n Parameters: mode_all=" + mode_all + ", codice_like=" + codice_like + ", filtro=" + filtro + ", delete=" + delete + ", option=" + option + "\n\n"; _logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- started at " + startedAt); T tmp = new T(); _logger.Info("Oggetto: " + typeof(T).ToString().PadRight(60) + " su tabella " + tmp._CP_tabella); DBHelper2 cm = DBHelper2.getCyberDBHelper(); if (delete) { DBHelper2.EseguiSuDBCyberPlan(ref cm, tmp.GetDeletedAllQuery()); _logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- deleting ended at " + DateTime.Now.ToString()); return; } //if (string.IsNullOrWhiteSpace(codice_like)) //{ // codice_like = "%"; //} //string qry = tmp.GetSelectQuery(mode_all, _libreria_dossier+"TEST", codice_like, filtro); _logger.Info("Oggetto: " + typeof(T).ToString().PadRight(60) + " Disab Blocco chifra suffisso TEST a " + _libreria_dossier); string qry = tmp.GetSelectQuery(mode_all, _libreria_dossier, codice_like, filtro); DbDataReader dtr = GetReaderSelectCommand(qry); object[] row = new object[dtr.FieldCount]; int i = 0; int j = 0; while (dtr.Read()) { //x debug //if (i==34529) //{ // _logger.Info("i=" + i.ToString() ); //} i++; j++; dtr.GetValues(row); //tmp.Init__AddRow(row); try { tmp.Init(row); } catch (Exception ex) { _logger.Info("I=" + i.ToString() + "Exception: " + ex.Message); } try { tmp.AddCyberRow();//tmp.GetRow(row); } catch (Exception ex) { _logger.Info(ex.Message); } if (j % 1000 == 0) { //int res= DBHelper2.EseguiSuDBCyberPlan(ref cm, tmp_qry); int res = DBHelper2.EseguiSuDBCyberPlan_Bulk(ref cm, tmp._CP_tabella, Item._dataTable); _logger.Info(i + " items... [" + tmp.GetID() + "]"); j = 0; } } int added = tmp.AddLastCyberRows(); if (j > 0 || added > 0) { //DBHelper2.EseguiSuDBCyberPlan(ref cm, tmp_qry); int res = DBHelper2.EseguiSuDBCyberPlan_Bulk(ref cm, tmp._CP_tabella, Item._dataTable); _logger.Info(i + added + " items [last= " + tmp.GetID() + "]"); } tmp.DoLastAction(ref cm, this); dtr.Close(); //if (thereIsMessage) //{ // Utils.SendMail("*****@*****.**", "*****@*****.**", message_error); // //Utils.SendMail("*****@*****.**", "[email protected],[email protected]", message_error); //} _logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- ended at " + DateTime.Now.ToString()); }