public override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
LastAction_CheckCoerenzaDB(ref cm);
return;
LastAction_RiferimentiFornitori(ref cm);
}
public override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
//
//controllo che gli OPR siano associati a ODV aperti
//
string chk_query = @"SELECT OPR.[C_CODE]
,OPR.[C_CORDER_CODE]
,OPR.[C_ITEM_CODE]
,SOH.C_CODE
FROM [CyberPlanFrontiera].[dbo].[CYB_ORDER] OPR
full join [CyberPlanFrontiera].[dbo].[CYB_CORDER] SOH
on OPR.C_CORDER_CODE= SOH.C_CODE
where (OPR.C_CODE like 'OPR%' or OPR.C_CODE like 'ODP%') and OPR.C_CORDER_CODE not like '000000000000' and SOH.C_CODE is null ";
string testo_mail = "";
DbDataReader dtr = cm.GetReaderSelectCommand(chk_query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
testo_mail += "OPR =" + getDBV <string>(row[0], "C_CODE") + " ODV =" + getDBV <string>(row[1], "C_CORDER_CODE") + " articolo " + getDBV <string>(row[2], "C_ITEM_CODE") + "; OPR aperto con ordine di vendita non presente (forse già chiuso)" + Utils.NewLineMail();
}
//
//controllo che gli OPR abbiano codici presenti in anagrafica articoli
//
chk_query = @"SELECT OPR.[C_CODE]
,OPR.[C_CORDER_CODE]
,OPR.[C_ITEM_CODE]
,ITM.[C_CODE]
FROM[CyberPlanFrontiera].[dbo].[CYB_ORDER] OPR
left join[CyberPlanFrontiera].[dbo].[CYB_ITEM] ITM
on OPR.[C_ITEM_CODE]= ITM.C_CODE
where OPR.C_CODE like 'OPR%' --and OPR.C_CORDER_CODE not like '000000000000'
and ITM.[C_ITEM_GROUP] not like '__TOOL__'
and ITM.C_CODE is null ";
dtr.Close();
dtr = cm.GetReaderSelectCommand(chk_query);
row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
testo_mail += "OPR =" + getDBV <string>(row[0], "C_CODE") + " ODV =" + getDBV <string>(row[1], "C_CORDER_CODE") + " articolo " + getDBV <string>(row[2], "C_ITEM_CODE") + "; OPR aperto con articolo non rilasciato" + Utils.NewLineMail();
}
//
//invio mail
//
Utils.SendMail_Plan(Settings.GetSettings(), testo_mail, "OPR");
}
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 static int?GetMinTaskNumber()
{
int?task_number = null;
string qry =
@" select * from (select top 1 TASK_NUMBER from [CyberPlanFrontiera].[dbo].EXP_CORDER order by TASK_NUMBER asc) A
union select * from (select top 1 TASK_NUMBER from [CyberPlanFrontiera].[dbo].EXP_ORDER order by TASK_NUMBER asc) B
union select * from (select top 1 TASK_NUMBER from [CyberPlanFrontiera].[dbo].EXP_OPERATION order by TASK_NUMBER asc) C
union select * from (select top 1 TASK_NUMBER from [CyberPlanFrontiera].[dbo].EXP_DEMAND order by TASK_NUMBER asc) D ";
DBHelper2 db = DBHelper2.getCyberDBHelper();
DbDataReader dtr = db.GetReaderSelectCommand(qry);
object[] row = new object[dtr.FieldCount];
if (dtr.HasRows)
{
while (dtr.Read())
{
dtr.GetValues(row);
task_number = (int)row[0];
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " Refresh min task number=" + task_number);
break;
}
}
else
{
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " task number = null");
}
dtr.Close();
return(task_number);
}
static private Dictionary <string, decimal> Init_List_AccantonamentiRigheORV(string libreria)
{
Dictionary <string, decimal> ret = new Dictionary <string, decimal>(1500);
string __libreriaAs400 = libreria;
string _tabORR = __libreriaAs400 + ".ORR00PF";
string _tabORV = __libreriaAs400 + ".ORV00PF";
//recupero totali accantonamenti per ogni articolo presente in ORR
string query = "SELECT "
+ _tabORR + ".ORRCART, Sum( " + _tabORR + ".ORRQACA) AS QTAALLOC "
+ " FROM " + _tabORR
+ " WHERE " + _tabORR + ".ORRSTAT ='RI' "
//+ " and " + _tabORR + ".ORRTORD<>'ORC' "
+ " and " + _tabORR + ".ORRCART not like 'WU%' \n"
+ " GROUP BY ORRCART "
+ " HAVING Sum(" + _tabORR + ".ORRQACA)<>0 "
+ " order by ORRCART ";
DBHelper2 db = DBHelper2.getAs400DBHelper(libreria);
DbDataReader dtr = db.GetReaderSelectCommand(query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
ret.Add(Item.GetDBV <string>(row[0], "ORRCART"), Item.GetDBV <decimal>(row[1], "Sum(ORRQACA)"));
}
return(ret);
}
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 GetFileImport_YMFG_Sage()
{
string libreriaAs400 = "MBM41LIB_M";
DBHelper2 sage = DBHelper2.getAs400DBHelper(libreriaAs400);
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " start...");
string qry = GetSelectQuery_opr_CON_ODV(libreriaAs400);
DbDataReader dtr = sage.GetReaderSelectCommand(qry);
object[] row = new object[dtr.FieldCount];
OrdiniAcq_OPR_As400_FIRSTIMPORT opr = null;
string pathfile = @"\\srvx3app1\S$\Sage\SAGEX3\folders\SAURO\YSAURO\IMPEXP\Import_Iniziale_OPR_Da_As400_CON_ODV.txt";
pathfile = @"Import_Iniziale_OPR_Da_As400_CON_ODV.txt";
using (System.IO.StreamWriter fs = new System.IO.StreamWriter(pathfile, false))
{
while (dtr.Read())
{
opr = new OrdiniAcq_OPR_As400_FIRSTIMPORT();
dtr.GetValues(row);
opr.Init(row);
fs.Write(opr.getSageImportString());
}
}
dtr.Close();
qry = GetSelectQuery_opr_SENZA_ODV(libreriaAs400);
dtr = sage.GetReaderSelectCommand(qry);
row = new object[dtr.FieldCount];
opr = null;
pathfile = @"\\srvx3app1\S$\Sage\SAGEX3\folders\SAURO\YSAURO\IMPEXP\Import_Iniziale_OPR_Da_As400_SENZA_ODV.txt";
pathfile = @"Import_Iniziale_OPR_Da_As400_SENZA_ODV.txt";
using (System.IO.StreamWriter fs = new System.IO.StreamWriter(pathfile, false))
{
while (dtr.Read())
{
opr = new OrdiniAcq_OPR_As400_FIRSTIMPORT();
dtr.GetValues(row);
opr.Init(row);
fs.Write(opr.getSageImportString());
}
}
dtr.Close();
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " end");
return;
}
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");
}
public static DBHelper2 getCyberDBHelper()
{
DBHelper2 ret = null;
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- started");
SqlConnection conn = new SqlConnection();
conn.ConnectionString = @"Server=srvsql1,1433\MSSQLSERVER;Database=CyberPlanFrontiera;User Id=Cyberplan; Password=C18£3r:okboo;";
ret = new DBHelper2(conn);
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- ended");
return(ret);
}
static private void FinalCheck_Ordini_di_Articoli_senza_cicli()
{
DBHelper2 db = DBHelper2.getCyberDBHelper();
string command = @" Select C_CODE,C_CORDER_CODE,C_ITEM_CODE,C_M_B --,ope.C_OPNUM, ope.C_USER_STRING01 AS Attrezzatura, ope.C_USER_STRING02 as Macchina
from [CyberPlanFrontiera].[dbo].[CYB_ORDER] od
left join [CyberPlanFrontiera].[dbo].[CYB_OPERATION] ope
on od.C_CODE = ope.C_ORDER_CODE
where C_M_B in ('M','D') and ope.C_OPNUM is null
order by C_ITEM_CODE ";
_logger.Info("start execution");
string testo_mail = "";
DBHelper2 cyber = DBHelper2.getCyberDBHelper();
DbDataReader dtr = cyber.GetReaderSelectCommand(command);
object[] row = new object[dtr.FieldCount];
string prec_articolo = "";
string articolo = "";
while (dtr.Read())
{
dtr.GetValues(row);
articolo = (string)row[2];
string C_M_B = (string)row[3];
if (C_M_B != "D") //se è di contolavoro non lo segnalo: devono avere il ciclo solo gli articolo di CL a capacità Finita, ma da qui non si riesce a suddividere in base al Cdl_MRP, quindi non riesco a fare il controllo
{
if (articolo != prec_articolo)
{
prec_articolo = articolo;
testo_mail += Utils.NewLineMail() + " codice =" + articolo + " non ha ciclo ma ha degli ordini di produzione " + Utils.NewLineMail();
if (articolo == "WM0662-03")
{
testo_mail += articolo + ": questo articolo non ha attrezzatura in As400, quindi non viene creato il ciclo; OK" + Utils.NewLineMail();
}
}
testo_mail += (string)row[0] + " " + Utils.NewLineMail();
}
}
Utils.SendMail_Anag(Settings.GetSettings(), testo_mail, "Ordini di articoli senza ciclo");
_logger.Info("end execution");
}
static public int EseguiSuDBCyberPlan(ref DBHelper2 cm, string query, int timeout)
{
#if DEBUG
//return 1;
#endif
int ret = -99;
if (!string.IsNullOrWhiteSpace(query))
{
if (cm._connection.State != ConnectionState.Open)
{
cm._connection.Open();
}
ret = cm.ExecuteCommand(query, timeout);
cm._connection.Close();
}
return(ret);
}
static private void FinalCheck_ARTICOLI_con_reparto_non_coerente(string dossier)
{
// DBHelper2 db = DBHelper2.getSageDBHelper("SAURO");
// string command = @" select F.YWCR_0, F.ITMREF_0, AM.MACREP_0, AM.MAC_0 from SAURO.ITMFACILIT F
//join SAURO.ITMMASTER M on F.ITMREF_0=M.ITMREF_0
//left join SAURO.YPRDITM P on P.ITMREF_0=F.ITMREF_0
//left join SAURO.YPRDAM AM on P.YATTCOD_0=AM.YCONATT_0
//where F.STOFCY_0='ITS01' and F.YWCR_0<>AM.MACREP_0 and MACREP_0<>'CL' and ITMSTA_0=1
//order by F.YWCR_0, AM.MACREP_0 ";
DBHelper2 db = DBHelper2.getSageDBHelper(dossier);
string command = @" select F.YWCR_0, F.ITMREF_0, AM.MACREP_0, AM.MAC_0 "
+ " from " + dossier + ".ITMFACILIT F "
+ " join " + dossier + ".ITMMASTER M on F.ITMREF_0=M.ITMREF_0 "
+ " left join " + dossier + ".YPRDITM P on P.ITMREF_0=F.ITMREF_0 "
+ " left join " + dossier + ".YPRDAM AM on P.YATTCOD_0=AM.YCONATT_0 "
+ " where F.STOFCY_0='ITS01' and F.YWCR_0<>AM.MACREP_0 and MACREP_0<>'CL' and ITMSTA_0=1 "
+ " order by F.YWCR_0, AM.MACREP_0 ";
_logger.Info("start execution");
string testo_mail = "";
DbDataReader dtr = db.GetReaderSelectCommand(command);
object[] row = new object[dtr.FieldCount];
string reparto_articolo = "", articolo = "";
string reparto_macchina = "", macchina = "";
while (dtr.Read())
{
dtr.GetValues(row);
reparto_articolo = (string)row[0];
articolo = (string)row[1];
reparto_macchina = (string)row[2];
macchina = (string)row[3];
testo_mail += articolo.PadRight(40) + " ha reparto " + reparto_articolo.PadRight(10) + " ma la macchina " + macchina.PadRight(20) + " ha reparto " + reparto_macchina + Utils.NewLineMail();
}
Utils.SendMail_Anag(Settings.GetSettings(), testo_mail, "Articoli ATTIVI con reparto non coerente con reparto macchina");
_logger.Info("end execution");
}
static private List <string> Get_Lista_Articoli_RI_Sage(bool mode, string dossier, string codice_like)
{
List <string> _lista_articoli_rilasciati_in_sage = new List <string>(30000);
//recupero totali accantonamenti per ogni articolo presente in ORR
string query = Articolo.SelectQuery(true, dossier, codice_like, null);
DBHelper2 db = DBHelper2.getSageDBHelper(dossier);
DbDataReader dtr = db.GetReaderSelectCommand(query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
_lista_articoli_rilasciati_in_sage.Add(Item.GetDBV <string>(row[0], "articolo"));
}
return(_lista_articoli_rilasciati_in_sage);
}
static protected DataTable InitTable_ConfigAttrezzura_ConMacchina(string dossier)
{
//recupero dati Configurazione Attrezzature Macchine dove c'è la macchina, non solo il gruppo
DataTable ret = new DataTable();
init_data_table(ret);
string query = @"SELECT
C.YCONATT_0
,C.YCONGRP_0
,C.YCONCDL_0 as WST_0
,C.YCONLOTSIZ_0
,C.YCONCAD_0
,C.YCONCADTIM_0
,C.YCONENAFLG_0
,C.YCONDATRIA_0
,W.WCR_0
from SAURO.YPRDCONF C
join SAURO.WORKSTATIO W on C.YCONCDL_0 = W.WST_0
left join SAURO.YPRDATT A on A.YATTCOD_0= C.YCONATT_0
where YCONCDL_0 <> '' " +
//and W.WCR_0 <> 'ASSE'
@" and A.YATTENAFLG_0=2
order by YCONATT_0, YCONCDL_0 desc";
DBHelper2 db = DBHelper2.getSageDBHelper(dossier);
DbDataReader dtr = db.GetReaderSelectCommand(query);
object[] row = new object[dtr.FieldCount];
//MEGA accrocchio per velocità: uso lo stesso oggetto
Std_Op_Machine tmp = null;
while (dtr.Read())
{
dtr.GetValues(row);
tmp = new Std_Op_Machine();
tmp.Init(row);
ret.Rows.Add(tmp.GetCyberRow(ret));//accrocchio per evitare che la riga derivi dall'altra tabella
}
return(ret);
}
public static DBHelper2 getAs400DBHelper(string libreria)
{
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- started");
DBHelper2 ret = null;
libreria = libreria.Trim().ToUpper();
if (libreria == "MBM41LIB_M" || libreria == "MBM41LIBMT")
{
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "Driver={Client Access ODBC Driver (32-bit)};System=isauro.sauro.dmn;Uid=DMZ;Pwd=6dmzadm;";
ret = new DBHelper2(conn);
ret._libreria_dossier = libreria;
}
else
{
throw new ArgumentException("libreria non previsto: " + libreria);
}
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- ended");
return(ret);
}
public static DBHelper2 getSageDBHelper(string dossier)
{
DBHelper2 ret = null;
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- started");
dossier = dossier.Trim().ToUpper();
if (dossier == "SAURO" || dossier == "SAURODEV" || dossier == "SAUROTEST" || dossier == "SAUROINT")
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = @"Server=srvx3app1\SAGEX3;Database=x3;User Id=" + dossier + "; Password=tiger;";
ret = new DBHelper2(conn);
ret._libreria_dossier = dossier;
}
else
{
throw new ArgumentException("dossier non previsto: " + dossier);
}
_logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " --- ended");
return(ret);
}
static private Dictionary <string, string> Get_Lista_Articoli_Fornitori_Sage(string dossier)
{
string db = "x3." + dossier;
Dictionary <string, string> _lista_articoli_fornitori_sage = new Dictionary <string, string>(1500);
//questa query va bene anche se non filtro gli articoli RILASCIATI
//perchè poi duplico solo le distinte base già filtrate per articoli rilasciati
string query = "select ITMREF_0, BPSNUM_0 from " + db + ".ITMBPS where BPSNUM_0 <> 'A000818' ";
DBHelper2 dbh = DBHelper2.getSageDBHelper(dossier);
DbDataReader dtr = dbh.GetReaderSelectCommand(query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
_lista_articoli_fornitori_sage.Add(Item.GetDBV <string>(row[0], "ITMREF_0"), Item.GetDBV <string>(row[1], "BPSNUM_0"));
}
return(_lista_articoli_fornitori_sage);
}
static int EseguiSuDBCyberPlan_Bulk(ref DBHelper2 cm, string tableName, DataTable dataTable)
{
#if DEBUG
//return 1;
#endif
if (cm._connection.State != ConnectionState.Open)
{
cm._connection.Open();
}
SqlBulkCopy bulk = new SqlBulkCopy(
(SqlConnection)cm._connection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
bulk.DestinationTableName = tableName;
bulk.WriteToServer(dataTable);
cm._connection.Close();
dataTable.Clear();
return(1);
}
public override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
//CONTROLLO che il terzo dell'ODV sia presente nell'anagrafica terzi
string testo_mail = "";
string chk_query =
@"SELECT distinct O.C_CODE
, O.C_CUSTOMER_CODE
FROM [CyberPlanFrontiera].[dbo].[CYB_CORDER] O
left join CyberPlanFrontiera.dbo.CYB_COMPANY T on O.C_CUSTOMER_CODE=T.C_CODE
where T.C_CODE is null ";
DbDataReader dtr = cm.GetReaderSelectCommand(chk_query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
testo_mail += "ODV =" + getDBV <string>(row[0], "C_CODE") + " non caricato; manca codice cliente =" + getDBV <string>(row[1], "C_CUSTOMER_CODE") + Utils.NewLineMail();
}
Utils.SendMail_IT(Settings.GetSettings(), testo_mail, "ODV");
}
static private void FinalCheck_ATTR_senza_macchina(string dossier)
{
// DBHelper2 db = DBHelper2.getSageDBHelper("SAURO");
// string command = @" select YATTCOD_0, YATTDES_0 from SAURO.YPRDATT A
//left join SAURO.YPRDCONF C on A.YATTCOD_0=C.YCONATT_0 and C.YCONENAFLG_0=2
//where A.YATTENAFLG_0=2 and C.YCONATT_0 is null
//order by YSTACOD_0 ";
DBHelper2 db = DBHelper2.getSageDBHelper(dossier);
string command = @" select YATTCOD_0, YATTDES_0 from "
+ dossier + ".YPRDATT A "
+ " left join " + dossier + ".YPRDCONF C on A.YATTCOD_0=C.YCONATT_0 and C.YCONENAFLG_0=2 "
+ " where A.YATTENAFLG_0=2 and C.YCONATT_0 is null "
+ " order by YSTACOD_0 ";
_logger.Info("start execution");
string testo_mail = "";
DbDataReader dtr = db.GetReaderSelectCommand(command);
object[] row = new object[dtr.FieldCount];
string attrezzatura = "", desc = "";
while (dtr.Read())
{
dtr.GetValues(row);
attrezzatura = (string)row[0];
desc = (string)row[1];
testo_mail += "attrezzatura senza macchine associate: " + attrezzatura.PadRight(40) + desc + Utils.NewLineMail();
}
Utils.SendMail_Plan(Settings.GetSettings(), testo_mail, "Attrezzature ATTIVE senza macchine associate");
_logger.Info("end execution");
}
public override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
Utils.SendMail_Plan(Settings.GetSettings(), __bulk_message, "Giacenze As400");
}
public void DoLastAction(ref DBHelper2 cm, DBHelper2 sage)
{
LastAction(ref cm, sage);
}
public virtual void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
}
public ExportItem(string file_prefix, X3WSUtils.TipoImport tipo)
{
_file_prefix = file_prefix;
Tipo = tipo;
_db = DBHelper2.getCyberDBHelper();
}
static private void FinalCheck_PLAS_senza_cicli()
{
DBHelper2 db = DBHelper2.getCyberDBHelper();
string command = @"SELECT
distinct D.[C_ITEM_CODE]
,D.[C_ORDER_CODE]
/*,D.[C_QTY]
,D.[C_WAREHOUSE_CODE]
,I.[C_DESCR ]
,I.[C_M_B ]
,I.[C_ITEM_GROUP ]
,I.[C_USER_STRING01 ]*/
,CICLI.C_CODE
FROM[CyberPlanFrontiera].[dbo].[CYB_DEMAND] D
left join[CyberPlanFrontiera].[dbo].[CYB_ITEM] I on D.C_ITEM_CODE=I.[C_CODE]
left join
(
--query indicata da Savietto per recuperare i cicli; qui serve per recuperare Articoli PLAS che non hanno cicli
SELECT T.C_CODE FROM[CyberPlanFrontiera].[dbo].FiltroCicliPlastica T
LEFT JOIN [CyberPlanFrontiera].[dbo].CYB_STD_OP_MACHINE PO_MAC
ON T.ATTREZZATURA = PO_MAC.C_ROUTING_CODE
left join [CyberPlanFrontiera].[dbo].cyb_machine mac on mac.c_code = PO_MAC.machine_code
where CHARINDEX(TIPO_P, mac.c_user_string04) > 0 -- verifico tipo plastica
and ((T.DIVISORE = 1 and mac.[C_USER_FLAG01] = 1) or T.DIVISORE = 0) -- Devo verificare: se il divisore è richiesto solo macchine con divisore, altrimenti tutte
and((T.PESO_MATER + T.PESO_ITEM) * (CASE WHEN cast(T.IMPRONTE_FORZ as int) = 0 THEN COALESCE(cast(t.IMPRONT_DEFAULT as int), 1) ELSE COALESCE(cast(t.IMPRONTE_FORZ as int), 1) END)) >= MAC.C_USER_REAL01
and((T.PESO_MATER + T.PESO_ITEM)*(CASE WHEN cast(T.IMPRONTE_FORZ as int) = 0 THEN COALESCE(cast(t.IMPRONT_DEFAULT as int), 1) ELSE COALESCE(cast(t.IMPRONTE_FORZ as int), 1) END)) <= MAC.C_USER_REAL02 -- Verifico il peso tra il min e il max della macchina
GROUP BY T.C_CODE, T.C_PLANT_CODE, PO_MAC.C_OPNUM, PO_MAC.C_ALT_OPNUM, COALESCE(MAC.C_WORKCENTER,'TRASH')
) CICLI on D.C_ITEM_CODE= CICLI.C_CODE
where I.[C_USER_STRING01]='PLAS' and CICLI.C_CODE is null
order by D.C_ITEM_CODE ";
_logger.Info("start execution");
string testo_mail = "";
DBHelper2 cyber = DBHelper2.getCyberDBHelper();
DbDataReader dtr = cyber.GetReaderSelectCommand(command);
object[] row = new object[dtr.FieldCount];
string prec_articolo = "";
string articolo = "";
while (dtr.Read())
{
dtr.GetValues(row);
articolo = (string)row[0];
if (articolo != prec_articolo)
{
prec_articolo = articolo;
testo_mail += Utils.NewLineMail() + " codice =" + articolo + " non è producibile ma questi Ordine di produzione lo richiedono " + Utils.NewLineMail();
}
testo_mail += (string)row[1] + " " + Utils.NewLineMail();
}
Utils.SendMail_Anag(Settings.GetSettings(), testo_mail, "WP senza ciclo");
_logger.Info("end execution");
}
protected void LastAction_CheckCoerenzaDB(ref DBHelper2 cm)
{
string testo_mail = "";
//verifico che tutti i componenti in distinta base siano presenti in anagrafica
string chk_query =
@"SELECT [C_BOM_CODE], [C_COMPONENT_CODE]
FROM [CyberPlanFrontiera].[dbo].[CYB_COMPONENT] C
left join [CyberPlanFrontiera].[dbo].[CYB_ITEM] I
on C.C_COMPONENT_CODE=I.[C_CODE ] and [C_ITEM_GROUP ]<> '__TOOL__'
where I.C_CODE is null ";
DbDataReader dtr = cm.GetReaderSelectCommand(chk_query);
object[] row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
testo_mail += "codice =" + getDBV <string>(row[0], "C_BOM_CODE") + " componente =" + getDBV <string>(row[1], "C_COMPONENT_CODE") + "; il componente non è presente in anagrafica o non è rilasciato" + Utils.NewLineMail();
}
//--verifico che tutti i codici con distinta base siano presenti in anagrafica
chk_query = @"SELECT [C_BOM_CODE],[C_COMPONENT_CODE]
FROM [CyberPlanFrontiera].[dbo].[CYB_COMPONENT] C
left join [CyberPlanFrontiera].[dbo].[CYB_ITEM] I
on C.[C_BOM_CODE]=I.[C_CODE ] and [C_ITEM_GROUP ]<> '__TOOL__'
where I.C_CODE is null";
dtr.Close();
dtr = cm.GetReaderSelectCommand(chk_query);
row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
testo_mail += "codice =" + getDBV <string>(row[0], "C_BOM_CODE") + " ha distinta base ma non è presente in anagrafica o non è rilasciato" + Utils.NewLineMail();
}
// --verifico che tutti i codici in anagrafica siano presenti con distinta base
//NB: and [C_M_B ] ='M' --make, prodotti in Sauro
chk_query = @"SELECT I.[C_CODE ],[C_BOM_CODE],[C_COMPONENT_CODE]
FROM [CyberPlanFrontiera].[dbo].[CYB_COMPONENT] C
right join [CyberPlanFrontiera].[dbo].[CYB_ITEM] I
on C.[C_BOM_CODE]=I.[C_CODE ]
where C.[C_BOM_CODE] is null
and [C_ITEM_GROUP ]<> '__TOOL__'
and [C_M_B ] ='M'
order by I.[C_CODE ]";
dtr.Close();
dtr = cm.GetReaderSelectCommand(chk_query);
row = new object[dtr.FieldCount];
while (dtr.Read())
{
dtr.GetValues(row);
string articolo = getDBV <string>(row[0], "C_CODE");
if (!articolo.StartsWith("WR000"))
{
testo_mail += "codice =" + articolo + " ha come in Sage 'tipo proposta'=Produzione ma non ha distinta base" + Utils.NewLineMail();
}
}
Utils.SendMail_Anag(Settings.GetSettings(), testo_mail, "BOM");
}
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 override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
//ThreadPool.QueueUserWorkItem(sendmail);
Utils.SendMail_Plan(Settings.GetSettings(), __bulk_message, "operations");
}
public override void LastAction(ref DBHelper2 cm, DBHelper2 sage)
{
Update_FAMP(ref cm);
}
