private void btnGeneraScript_Click(object sender, EventArgs e)
{
if (Meta.IsEmpty)
{
return;
}
Meta.SaveFormData();
DataAccess Conn = Meta.Conn;
DialogResult r = saveFileDialog1.ShowDialog(this);
string fileName = saveFileDialog1.FileName;
if (r != DialogResult.OK)
{
return;
}
DataRow Curr = DS.exportfunction.Rows[0];
string procedurename = Curr["procedurename"].ToString();
DataTable tRep = Conn.CreateTableByName("exportfunction", "*");
Conn.RUN_SELECT_INTO_TABLE(tRep, null, QHS.CmpEq("procedurename", procedurename), null, false);
DataTable tRepPar = Conn.CreateTableByName("exportfunctionparam", "*");
Conn.RUN_SELECT_INTO_TABLE(tRepPar, "number", QHS.CmpEq("procedurename", procedurename), null, false);
DataAccess.AddExtendedProperty(Conn, tRep);
DataAccess.AddExtendedProperty(Conn, tRepPar);
DataSet D1 = new DataSet();
D1.Tables.Add(tRep);
GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, false, UpdateType.insertAndUpdate,
DataGenerationType.onlyData, true);
D1 = new DataSet();
D1.Tables.Add(tRepPar);
GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.insertAndUpdate,
DataGenerationType.onlyData, true);
if (chkScriptSP.Checked)
{
StreamWriter sw = new StreamWriter(fileName, true);
sw.Write(GeneraSQL.scriptOneSP(Conn, procedurename));
sw.Flush();
sw.Close();
}
MessageBox.Show(this, "File salvato in " + fileName, "Avviso");
}
private void btnDati_Click(object sender, EventArgs e)
{
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr != DialogResult.OK)
{
MessageBox.Show(this, "File non scelto. Procedura interrotta");
return;
}
string filename = saveFileDialog1.FileName;
StreamWriter writer = new StreamWriter(filename, false, Encoding.Default);
writer.WriteLine(Header);
writer.WriteLine("delete from inventorytree");
writer.WriteLine("GO");
DataTable tInventoryTree = DataAccess.CreateTableByName(Meta.Conn, "inventorytree", "*");
DataAccess.RUN_SELECT_INTO_TABLE(Meta.Conn, tInventoryTree, "idinv", null, null, true);
if (tInventoryTree == null)
{
MessageBox.Show(this, "Errore nel riempimento dell tabella INVENTORYTREE", "Errore");
return;
}
if (tInventoryTree.Rows.Count == 0)
{
MessageBox.Show(this, "La tabella INVENTORYTREE risulta vuota, non verrà generato alcuno script", "Avviso");
return;
}
DataSet dsIT = new DataSet();
dsIT.Tables.Add(tInventoryTree);
GeneraSQL.GeneraStrutturaEDati(Meta.Conn, dsIT, writer, UpdateType.onlyInsert, DataGenerationType.onlyData, true);
writer.Close();
MessageBox.Show("File generato correttamente");
return;
}
private void btnGeneraScript_Click(object sender, EventArgs e)
{
if (Meta.IsEmpty)
{
return;
}
Meta.SaveFormData();
DialogResult r = saveFileDialog1.ShowDialog(this);
string fileName = saveFileDialog1.FileName;
if (r != DialogResult.OK)
{
return;
}
btnGeneraScript.Enabled = false;
DataRow Curr = DS.report.Rows[0];
string reportName = Curr["reportname"].ToString();
DataTable tRep = Conn.CreateTableByName("report", "*");
Conn.RUN_SELECT_INTO_TABLE(tRep, null, QHS.CmpEq("reportname", reportName), null, false);
DataTable tRepPar = Conn.CreateTableByName("reportparameter", "*");
Conn.RUN_SELECT_INTO_TABLE(tRepPar, "number", QHS.CmpEq("reportname", reportName), null, false);
DataTable tRepAddPar = Conn.CreateTableByName("reportadditionalparam", "*");
Conn.RUN_SELECT_INTO_TABLE(tRepAddPar, null,
QHS.AppAnd(QHS.CmpEq("reportname", reportName), QHS.IsNull("stop")), null, false);
DataAccess.AddExtendedProperty(Conn, tRep);
DataAccess.AddExtendedProperty(Conn, tRepPar);
DataAccess.AddExtendedProperty(Conn, tRepAddPar);
DataSet D1 = new DataSet();
D1.Tables.Add(tRep);
GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, false, UpdateType.insertAndUpdate,
DataGenerationType.onlyData, true);
D1 = new DataSet();
D1.Tables.Add(tRepPar);
GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.insertAndUpdate,
DataGenerationType.onlyData, true);
D1 = new DataSet();
D1.Tables.Add(tRepAddPar);
GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.onlyInsert, DataGenerationType.onlyData, true);
Dictionary <string, bool> spAdded = new Dictionary <string, bool>();
if (chkScriptSP.Checked)
{
ReportDocument ReportDoc = new ReportDocument();
string path = Meta.GetUsr("localreportdir").ToString();
string fName = txtFile.Text;
string repFileName = Path.Combine(path, fName);
try {
// Open a temporary copy of the report.
ReportDoc.Load(repFileName, OpenReportMethod.OpenReportByTempCopy);
StreamWriter sw = new StreamWriter(fileName, true);
string spName = getSPName(ReportDoc);
sw.Write(GeneraSQL.scriptOneSP(Conn, spName));
spAdded.Add(spName, true);
//mi scorro tutti i subreport (se presenti) del report principale
ReportDefinition repDef = ReportDoc.ReportDefinition;
foreach (Section sec in repDef.Sections)
{
foreach (ReportObject repObj in sec.ReportObjects)
{
if (repObj.Kind != ReportObjectKind.SubreportObject)
{
continue;
}
SubreportObject subRep = (SubreportObject)repObj;
ReportDocument SubReport = subRep.OpenSubreport(subRep.SubreportName);
spName = getSPName(SubReport);
if (!spAdded.ContainsKey(spName) && spName != "stampa_logo")
{
spAdded.Add(spName, true);
sw.Write(GeneraSQL.scriptOneSP(Conn, spName));
}
SubReport.Close();
}
}
ReportDoc.Close();
sw.Flush();
sw.Close();
sw.Dispose();
}
catch (Exception ee) {
QueryCreator.ShowException(this, "Impossibile caricare il report " + repFileName, ee);
}
}
MessageBox.Show(this, "File salvato in " + fileName, "Avviso");
btnGeneraScript.Enabled = true;
}
private void btnAddScriptAuditCheck_Click(object sender, EventArgs e)
{
if (DS.auditcheck.Rows.Count == 0)
{
return;
}
QueryHelper QHS = Conn.GetQueryHelper();
DataTable t = Conn.CreateTableByName("auditcheck", "*", true);
DataRow r = DS.auditcheck.Rows[0];
string filter = null;
bool clear = false;
UpdateType updateType = UpdateType.insertAndUpdate;
string filename = "auditcheck.sql";
bool append = false;
if (radRule.Checked)
{
filter = QHS.CmpEq("idaudit", r["idaudit"]);
clear = true;
updateType = UpdateType.bulkinsert;
filename = r["idaudit"].ToString().ToLower() + ".sql";
}
if (radToday.Checked)
{
filter = QHS.AppAnd(QHS.CmpEq("idaudit", r["idaudit"]), "( (convert(date,getdate()) = convert(date, lt)) )");
filename = r["idaudit"].ToString().ToLower() + ".sql";
}
if (radTable.Checked)
{
filter = QHS.MCmp(r, "idaudit", "tablename");
clear = true;
updateType = UpdateType.bulkinsert;
filename = r["idaudit"].ToString().ToLower() + "_" + r["tablename"].ToString().ToLower() + ".sql";
}
if (radThis.Checked)
{
filter = QHS.CmpKey(r);
filename = r["idaudit"].ToString().ToLower() + "_" +
r["tablename"].ToString().ToLower() + "_" +
r["opkind"].ToString().ToLower() +
r["idcheck"].ToString().ToLower() +
".sql";
}
if (radSingleAppend.Checked)
{
filter = QHS.CmpKey(r);
filename = r["idaudit"].ToString().ToLower() + ".sql";
append = true;
}
DataAccess.RUN_SELECT_INTO_TABLE(Conn, t, null, filter, null, true);
if (t.Rows.Count == 0)
{
MessageBox.Show("Nessuna riga trovata. Filtro:" + filter +
" Ultimo errore:" + Conn.LastError);
return;
}
DataAccess.AddExtendedProperty(Conn, t);
DataSet DS2 = new DataSet();
DS2.Tables.Add(t);
if (txtFolder.Text != "")
{
filename = Path.Combine(txtFolder.Text, filename);
}
StreamWriter writer = new StreamWriter(filename, append, System.Text.Encoding.Default);
if (radRule.Checked)
{
//Eventuale riga di audit
writer.WriteLine("-- AUDITCHECK " + r["idaudit"]);
DataSet D3 = new DataSet();
DataTable t2 = Conn.RUN_SELECT("audit", "*", null, QHS.CmpEq("idaudit", r["idaudit"]), null, false);
D3.Tables.Add(t2);
GeneraSQL.DO_GENERATE(Conn, D3, writer, UpdateType.onlyInsert, DataGenerationType.onlyData, true);
}
//eventuale cleanup
if (clear)
{
writer.WriteLine("delete from auditcheck where " + filter);
writer.WriteLine("GO");
writer.Flush();
}
//Inserimento dati
GeneraSQL.DO_GENERATE(Conn, DS2, writer, updateType, DataGenerationType.onlyData, true);
writer.Flush();
writer.Close();
MessageBox.Show("Script " + filename + (append ? " aggiornato " : " generato ") + " con successo", "Avviso");
}
private bool aggiornaDB(string tName)
{
StringBuilder SB = new StringBuilder();
if (tName == "config")
{
// A differenza delle altre tabelle CONFIG viene inserita o aggiornata.
// L'aggiornamento vinee fatto solo sui campi relativi al patrimonio
string assetsetup_fields = "assetload_flag, asset_flagnumbering, asset_flagrestart";
string[] listaCampi = new string[] { "assetload_flag", "asset_flagnumbering", "asset_flagrestart" };
foreach (DataRow rAssetSetup in dsFile.Tables["config"].Rows)
{
string filtro = QHS.CmpEq("ayear", rAssetSetup["ayear"]);
int nRow = Meta.Conn.RUN_SELECT_COUNT("config", filtro, true);
if (nRow > 0)
{
string queryAssetSetup = "UPDATE config SET ";
string u = "";
foreach (string fName in listaCampi)
{
u += fName + " = " + QHS.quote(rAssetSetup[fName]) + ",";
}
u = u.Substring(0, u.Length - 2);
queryAssetSetup += u + " WHERE " + QHS.CmpEq("ayear", rAssetSetup["ayear"]);
SB.Append(queryAssetSetup);
}
else
{
string fieldForInsert = "ayear," + assetsetup_fields;
string sqlInsert = "INSERT INTO config (" + fieldForInsert + ") VALUES (";
string values = GeneraSQL.GetSQLDataValues(rAssetSetup);
sqlInsert += values;
SB.Append(sqlInsert);
}
}
}
else
{
string f_id;
string f_code;
string f_descr;
ottieniCampiTabella(tName, out f_id, out f_code, out f_descr);
if ((f_id == "") || (f_code == "") || (f_descr == ""))
{
return(false);
}
string[] tLinked = ottieniElencoTabelleCollegate(tName);
// Parte UPDATE delle chiavi esterne e DELETE delle righe che non devono esserci nel DB del dipartimento
foreach (DataRow r in dsLookUp.Tables[tName].Rows)
{
if ((r["!linkedcode"] == DBNull.Value) || (r["!linkedcode"].ToString() == ""))
{
continue;
}
foreach (string tabella in tLinked)
{
if ((tabella == null) || (tabella == ""))
{
continue;
}
string valore_codice_db = r[f_code].ToString();
string valore_codice_ds = r["!linkedcode"].ToString();
if (valore_codice_db != valore_codice_ds)
{
string sqlUpdate =
"UPDATE " + tabella + " SET " + f_id + " = " + QueryCreator.quotedstrvalue(r["!linkedid"], true)
+ " WHERE " + f_id + " = " + QueryCreator.quotedstrvalue(r[f_id], true) + "\n\r";
SB.Append(sqlUpdate);
}
}
string sqlDelete = "DELETE FROM " + tName + " WHERE " + f_id + " = " + QueryCreator.quotedstrvalue(r[f_id], true) + "\n\r";
SB.Append(sqlDelete);
if (SB.ToString() != "")
{
SB.Append("GO\n\r");
}
}
// Parte delle INSERT delle righe assenti nel DB del dipartimento
string fields = "";
foreach (DataColumn c in dsPruned.Tables[tName].Columns)
{
fields += c.ColumnName + ",";
}
fields = fields.Remove(fields.Length - 1, 1);
foreach (DataRow rDS in dsPruned.Tables[tName].Rows)
{
string filter = QueryCreator.WHERE_KEY_CLAUSE(rDS, DataRowVersion.Current, false);
if (dsLookUp.Tables[tName].Select(filter).Length > 0)
{
continue;
}
string sqlInsert = "INSERT INTO " + tName + "(" + fields + ") VALUES (";
string values = GeneraSQL.GetSQLDataValues(rDS);
sqlInsert += values;
SB.Append(sqlInsert);
}
}
if (SB.ToString() != "")
{
SB.Append("GO\n\r");
string error;
return(Download.RUN_SCRIPT(Meta.Conn, SB, out error));
}
return(true);
}
