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