private void SaveVarsForMailMerge()
{
modelMailMargeVariables MMvars;
MMvars = new modelMailMargeVariables();
MMvars.prepareMailMergeData(newData);
var db = Program.GData.ChooseDB();
db.EnableNamedParams = false;
string sqlDelete = "DELETE FROM tblWordMailMerge WHERE ProjectID=@0";
List <object> argsDelete = new List <object>();
argsDelete.Add(newData.ID);
db.Execute(sqlDelete, argsDelete.ToArray());
for (int index = 0; index < MMvars.mailMergeVarsCount; index++)
{
MMvars.mailMergeVarsIndex = index;
Tuple <string, List <object> > data = SaveVarsForMailMergeVariation(index, ref MMvars, Program.GData.CFG_DB_USE == 0 ? true : false);
db.Execute(data.Item1, data.Item2.ToArray());
}
}
private void ibuttonExportExcel_Click(object sender, EventArgs e)
{
var saveDialog = new SaveFileDialog();
saveDialog.InitialDirectory = Convert.ToString(Environment.SpecialFolder.Desktop);
saveDialog.FileName = "export.xlsx";
saveDialog.Filter = "Excel file (*.xlsx)|*.xlsx|All Files (*.*)|*.*";
saveDialog.FilterIndex = 1;
if (saveDialog.ShowDialog() != DialogResult.OK)
{
return;
}
modelMailMargeVariables MMvars;
MMvars = new modelMailMargeVariables();
MMvars.prepareMailMergeData(newData);
var excp = new ExcelPackage();
var ws = excp.Workbook.Worksheets.Add("Export");
int row = 1;
int column = 1;
for (int index = 0; index < MMvars.mailMergeVarsCount; index++)
{
MMvars.mailMergeVarsIndex = index;
column = 1;
var row1 = MMvars.usedVariables.ToArray();
foreach (var mmvar in row1)
{
string fieldName = mmvar.Value;
if (row == 1)
{
ws.Cells[row, column].Value = fieldName;
}
string varvalue = "";
if (MMvars.mailMergeVars.ContainsKey(fieldName))
{
varvalue = MMvars.mailMergeVars[fieldName];
ws.Cells[row + 1, column].Value = varvalue;
}
column++;
}
row++;
//Tuple<string, List<object>> data = SaveVarsForMailMergeVariation(index, ref MMvars, Program.GData.CFG_DB_USE == 0 ? true : false);
}
row++;
Byte[] bin = excp.GetAsByteArray();
File.WriteAllBytes(saveDialog.FileName, bin);
OpenFile(saveDialog.FileName);
}
private Tuple <string, List <object> > SaveVarsForMailMergeVariation(int index, ref modelMailMargeVariables MMvars, bool namedParams)
{
int column = 1;
string sqlInsert = "INSERT INTO tblWordMailMerge (ProjectID,";
string sqlFields;
if (namedParams)
{
sqlFields = "(@ProjectID,";
}
else
{
sqlFields = "(@0,";
}
var row1 = MMvars.usedVariables.ToArray();
foreach (var mmvar in row1)
{
string fieldName = mmvar.Value;
sqlInsert += fieldName + ",";
if (namedParams)
{
sqlFields += "@" + fieldName + ",";
}
else
{
sqlFields += "@" + column.ToString() + ",";
}
column++;
}
sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1);
sqlFields = sqlFields.Substring(0, sqlFields.Length - 1);
string sql = sqlInsert + ") VALUES " + sqlFields + ")";
List <object> args = new List <object>();
args.Add(newData.ID);
foreach (var mmvar in row1)
{
string fieldName = mmvar.Value;
string varvalue = "";
if (MMvars.mailMergeVars.ContainsKey(fieldName))
{
varvalue = MMvars.mailMergeVars[fieldName];
if (varvalue == null)
{
varvalue = "";
}
//Console.WriteLine(varvalue.Length);
args.Add(varvalue);
}
else
{
args.Add("");
}
column++;
}
return(Tuple.Create(sql, args));
}
