private void GenerateGrid() // retrieves all record from MASTERBALLOT table and binds to datagrid
{
List <BallotData> list = new List <BallotData>();
// dgBallotMaster.ItemsSource = null;
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
DataTable dt;
using (SQLiteDataAdapter Sqa = new SQLiteDataAdapter("Select BALLOT_ID , " + " CASE WHEN ELECTION_FOR ='P' then 'Parliamentary' " + " when ELECTION_FOR ='A' then 'Assembly' " + " end ELECTION_FOR , " + "STATE_CODE ,LANGUAGE_1 , LANGUAGE_2 , " + " CASE WHEN IS_FINALIZED ='N' then 'No' else 'Yes' end IS_FINALIZED " + " , FINALIZATION_DATE from " + " MASTERBALLOT", sqlite_conn))
{
dt = new DataTable();
Sqa.Fill(dt); // fill the dataset
}
foreach (DataRow row in dt.Rows)
{
list.Add(new BallotData()
{
BallotID = Convert.ToInt32(row["BALLOT_ID"]),
state = row["STATE_CODE"].ToString(),
constituency = row["ELECTION_FOR"].ToString(),
language1 = row["LANGUAGE_1"].ToString(),
language2 = row["LANGUAGE_2"].ToString(),
IsFinalized = row["IS_FINALIZED"].ToString(),
dateOfFinalization = row["FINALIZATION_DATE"].ToString()
});
}
Dispatcher.BeginInvoke(System.Windows.Threading.DispatcherPriority.Normal, (Action)(() => { dgBallotMaster.ItemsSource = list; }));
SqLite.CloseSQLLiteConnection(sqlite_conn);
}
private void deleteRow(int ballotId) //gets called in dgBallotMaster_SelectedCellsChanged
{
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
string cmdDeletestring = "Delete from MASTERBALLOT where Ballot_ID = " + ballotId + " ;";
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = cmdDeletestring;
int n = sqlite_cmd.ExecuteNonQuery();
if (n > 0)
{
MessageBox.Show("Record Deleted");
GenerateGrid();
}
SqLite.CloseSQLLiteConnection(sqlite_conn);
}
private void btnMasterDetails_Click(object sender, RoutedEventArgs e)
{
if (constituencyCombo.SelectedIndex <= 0 || stateCombo.SelectedIndex <= 0 || language1Combo.SelectedIndex <= 0 || language2Combo.SelectedIndex <= 0)
{
MessageBox.Show("Please select all fields.");
}
else
{
object state = stateCombo.SelectedItem?.GetType().GetProperty("state_display")?.GetValue(stateCombo.SelectedItem, null);
// string state = null;
string lang2 = (string)((ComboBoxItem)language2Combo.SelectedValue).Content;
int ballotIdToInsert = GetBallotId();
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
// string commandstring = "Insert into MASTERBALLOT " + "( BALLOT_ID ,ELECTION_FOR,STATE_CODE,LANGUAGE_1,LANGUAGE_2,IS_FINALIZED) VALUES ( " + ballotIdToInsert + ", '" + constituencyCombo.SelectedValue.ToString() + "','" + state.ToString() + "("+ stateCombo.SelectedValue.ToString()+")','" + language1Combo.SelectedValue.ToString() + "','" + lang2 + "','N')";
string commandstring = "Insert into MASTERBALLOT " + "( BALLOT_ID ,ELECTION_FOR,STATE_CODE,LANGUAGE_1,LANGUAGE_2,IS_FINALIZED) VALUES ( " + ballotIdToInsert + ", '" + constituencyCombo.SelectedValue.ToString() + "','" + state.ToString() + "','" + language1Combo.SelectedValue.ToString() + "','" + lang2 + "','N')";
sqlite_cmd.CommandText = commandstring;
int result = sqlite_cmd.ExecuteNonQuery();
if (result > 0)
{
//MessageBox.Show("Master record inserted");
GenerateGrid();
//DisableAddBallot();
Reset();
}
else
{
MessageBox.Show("Master record insertion failed");
}
sqlite_cmd.Dispose();
SqLite.CloseSQLLiteConnection(sqlite_conn);
AddMasterBallot.Visibility = System.Windows.Visibility.Hidden;
}
}
private void Update_Click(object sender, RoutedEventArgs e)
{
int count = CandidateRecordList.CandidateRecords.Select(x => x.CandidateNO).Distinct().Count();
if (count < CandidateRecordList.CandidateRecords.Count)
{
System.Windows.Forms.MessageBox.Show("Duplicate Candidate Serial Number according to form7A Found \n Please correct and update again...!");
}
else
{
int result = 0;
foreach (CandidateRecord candidateRecord in CandidateRecordList.CandidateRecords)
{
SQLiteConnection sqlite_conn = SqLite.OpenSQLLiteConnection(new SQLiteConnection());
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
string command = "UPDATE BALLOTDETAILS SET CANDIDATESLNO = @CANDIDATESLNO where DETAILBALLOTID = @DETAILBALLOTID";
using (sqlite_cmd = new SQLiteCommand(command, sqlite_conn))
{
sqlite_cmd.Parameters.AddWithValue("@CANDIDATESLNO", candidateRecord.CandidateNO);
sqlite_cmd.Parameters.AddWithValue("@DETAILBALLOTID", candidateRecord.DetailBallotID);
result = sqlite_cmd.ExecuteNonQuery();
}
sqlite_cmd.Dispose();
SqLite.CloseSQLLiteConnection(sqlite_conn);
}
if (result > 0)
{
System.Windows.Forms.MessageBox.Show("Record Update Successfully..!");
btnUpdate.IsEnabled = false;
btnSave.IsEnabled = true;
}
else
{
MessageBox.Show("Failed to update serial number", "Error");
}
}
}
private void Save_Click(object sender, RoutedEventArgs e)
{
SelectPdf.GlobalProperties.LicenseKey = "xu335vTz9+b19PL05vf+6Pbm9ffo9/To/////w== ";
TTfileDict.Clear();
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
string commandstring = "SELECT * FROM BALLOTDETAILS WHERE BALLOTID = " + BallotWindow.BallotId + " ORDER BY CANDIDATESLNO";
sqlite_cmd.CommandText = commandstring;
SQLiteDataReader oReader = sqlite_cmd.ExecuteReader();
while (oReader.Read())
{
if (string.IsNullOrEmpty(oReader["CANDIDATEPHOTO"].ToString()))
{
TTfileDict.Add(Convert.ToInt32(oReader["DETAILBALLOTID"]),
new CandidateRecord()
{
BallotID = Convert.ToInt32(oReader["BALLOTID"].ToString()),
CandidateNO = Convert.ToInt32(oReader["CANDIDATESLNO"]),
CandidateNameENG = oReader["CANDIDATENAMEENG"].ToString(),
CandidateNameOL = oReader["CANDIDATENAMEOL"].ToString(),
PartyNameENG = oReader["PARTYAFFILIATIONENG"].ToString(),
PartyNameOL = oReader["PARTYAFFILIATIONOL"].ToString(),
CandidatePhoto = new byte[1],
ISNOTA = Convert.ToInt32(oReader["ISNOTA"]),
});
}
else
{
TTfileDict.Add(Convert.ToInt32(oReader["DETAILBALLOTID"]),
new CandidateRecord()
{
BallotID = Convert.ToInt32(oReader["BALLOTID"].ToString()),
CandidateNO = Convert.ToInt32(oReader["CANDIDATESLNO"]),
CandidateNameENG = oReader["CANDIDATENAMEENG"].ToString(),
CandidateNameOL = oReader["CANDIDATENAMEOL"].ToString(),
PartyNameENG = oReader["PARTYAFFILIATIONENG"].ToString(),
PartyNameOL = oReader["PARTYAFFILIATIONOL"].ToString(),
CandidatePhoto = (byte[])oReader["CANDIDATEPHOTO"],
// CandidatePhoto = Convert.ToBase64String(oReader["CANDIDATEPHOTO"] as byte[]),
ISNOTA = Convert.ToInt32(oReader["ISNOTA"]),
});
}
}
oReader.Close();
sqlite_cmd.Dispose();
SqLite.CloseSQLLiteConnection(sqlite_conn);
if (TTfileDict.Values.Count == 0)
{
MessageBox.Show("No Candidate Record Found...");
return;
}
string str = "";
List <CandidateRecord> candidateLists = new List <CandidateRecord>();
candidateLists.Clear();
foreach (KeyValuePair <int, CandidateRecord> candidate in TTfileDict)
{
candidateLists.Add(candidate.Value);
}
ballotCandidateList.Candidate = candidateLists;
string exeFile = (new System.Uri(Assembly.GetEntryAssembly().CodeBase)).AbsolutePath;
string exeDir = System.IO.Path.GetDirectoryName(exeFile);
string Directorypath = Directory.GetCurrentDirectory() + "\\images";
string base64String1 = ConvertImage(Directorypath + "\\eci-logo.png");
string Imagesrc = "data:image/jpg;base64," + base64String1;
string base64String2 = null;
string Imagesrc2 = null;
//string Bordername = null;
// var TemplateInstance=
if (BallotWindow.strConstType == "Parliamentary")
{
base64String2 = ConvertImage(Directorypath + "\\PC.PNG");
Imagesrc2 = "data:image/jpg;base64," + base64String2;
BallotTemplatePC TemplateInstance = new BallotTemplatePC
{
Session = new Dictionary <string, object>()
};
TemplateInstance.Session.Add("TTfileDict", ballotCandidateList);
TemplateInstance.Session.Add("Imagesrc", Imagesrc);
TemplateInstance.Session.Add("Imagesrc2", Imagesrc2);
TemplateInstance.Session.Add("FontFamily", BallotWindow.selectedfont);
TemplateInstance.Initialize();
str = TemplateInstance.TransformText();
// Bordername = "PC.PNG";
}
else
{
base64String2 = ConvertImage(Directorypath + "\\AC.PNG");
Imagesrc2 = "data:image/jpg;base64," + base64String2;
BallotTemplateAC TemplateInstanceAC = new BallotTemplateAC
{
Session = new Dictionary <string, object>()
};
TemplateInstanceAC.Session.Add("TTfileDict", ballotCandidateList);
TemplateInstanceAC.Session.Add("Imagesrc", Imagesrc);
TemplateInstanceAC.Session.Add("Imagesrc2", Imagesrc2);
TemplateInstanceAC.Session.Add("FontFamily", BallotWindow.selectedfont);
TemplateInstanceAC.Initialize();
str = TemplateInstanceAC.TransformText();
// Bordername = "AC.PNG";
}
// ConvertHtmlToImage(str);
/////////////////////////////////////////////////////////////////////////////////////////
// Intentionally blank page tt file code
string str1 = "";
IntentionallyBlankPage TemplateInstance1 = new IntentionallyBlankPage();
//TemplateInstance1.Session = new Dictionary<string, object>();
//TemplateInstance1.Session.Add(Imagesrc, Imagesrc);
//TemplateInstance1.Initialize();
str1 = TemplateInstance1.TransformText();
//////////////////////////////////////////////////////////////////////////////////////
HtmlToPdf converter = new HtmlToPdf();
string fullPath = System.IO.Path.Combine(exeDir, "..\\..\\HtmlEngine\\Select.Html.dep");
string filepath = System.IO.Path.GetFileName(fullPath);
GlobalProperties.HtmlEngineFullPath = filepath;
SelectPdf.PdfDocument doc = converter.ConvertHtmlString(str);
SelectPdf.PdfDocument doc1 = converter.ConvertHtmlString(str1);
SelectPdf.PdfPage pdfPage = doc1.Pages[0];
for (int i = 0; i < doc.Pages.Count; i++)
{
if (i % 2 == 1)
{
doc.InsertPage(i, pdfPage);
}
}
SaveFileDialog saveFileDialog = new SaveFileDialog
{
DefaultExt = "pdf",
Filter = "Pdf File|*.pdf",
Title = "Save Ballot",
FileName = "Ballot",
InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
};
if (saveFileDialog.ShowDialog() == true)
{
doc.Save(saveFileDialog.FileName);
doc.Close();
MessageBox.Show("File Saved...");
}
else
{
MessageBox.Show("File Not Saved...");
}
}
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
string command = string.Empty;
if (constituencyCombo.SelectedIndex <= 0 || stateCombo.SelectedIndex <= 0 || language1Combo.SelectedIndex <= 0 || language2Combo.SelectedIndex <= 0)
{
MessageBox.Show("Please select record from table to update.");
Reset();
}
else
{
object state = stateCombo.SelectedItem?.GetType().GetProperty("state_display")?.GetValue(stateCombo.SelectedItem, null);
string empty = "";
string lang2 = (string)((ComboBoxItem)language2Combo.SelectedValue).Content;
//check candidate record associated with current record is NOT empty or warn user not to update or delete
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
command = "select COUNT(BALLOTID) from BALLOTDETAILS where BALLOTID=" + ballotIdToUpdate;
sqlite_cmd.CommandText = command;
int queryResult = Convert.ToInt32(sqlite_cmd.ExecuteScalar());
sqlite_cmd.Dispose();
command = string.Empty;
SqLite.CloseSQLLiteConnection(sqlite_conn);
//Candidate record found in DB
if (queryResult > 0)
{
//warn
MessageBoxResult result = MessageBox.Show("Updating record may cause losing Candidates Record \n Do You want to update ?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning);
//if want to update
if (result == MessageBoxResult.Yes)
{
//first delete record from BALLOTDETAILS
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
command = "delete from BALLOTDETAILS where BALLOTID =" + ballotIdToUpdate;
sqlite_cmd.CommandText = command;
sqlite_cmd.ExecuteNonQuery();
sqlite_cmd.Dispose();
command = string.Empty;
SqLite.CloseSQLLiteConnection(sqlite_conn);
// and update master record
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
command = "Update MASTERBALLOT set ELECTION_FOR='" + constituencyCombo.SelectedValue.ToString() + "',STATE_CODE='" +
state.ToString() + "',LANGUAGE_1='" +
language1Combo.SelectedValue.ToString() + "',LANGUAGE_2='" + lang2 + "',IS_FINALIZED ='N',FINALIZATION_DATE='" + empty + "' where BALLOT_ID =" + ballotIdToUpdate;
sqlite_cmd.CommandText = command;
int n = sqlite_cmd.ExecuteNonQuery();
if (n > 0)
{
System.Windows.MessageBox.Show("Master record Updated");
GenerateGrid();
Reset();
}
else
{
MessageBox.Show("Master record Update failed");
}
sqlite_cmd.Dispose();
SqLite.CloseSQLLiteConnection(sqlite_conn);
}
}
else
{
//update master record
sqlite_conn = SqLite.OpenSQLLiteConnection(sqlite_conn);
sqlite_cmd = sqlite_conn.CreateCommand();
command = "Update MASTERBALLOT set ELECTION_FOR='" + constituencyCombo.SelectedValue.ToString() + "',STATE_CODE='" +
state.ToString() + "',LANGUAGE_1='" +
language1Combo.SelectedValue.ToString() + "',LANGUAGE_2='" + lang2 + "',IS_FINALIZED ='N',FINALIZATION_DATE='" + empty + "' where BALLOT_ID =" + ballotIdToUpdate;
sqlite_cmd.CommandText = command;
int n = sqlite_cmd.ExecuteNonQuery();
if (n > 0)
{
System.Windows.MessageBox.Show("Master record Updated");
GenerateGrid();
Reset();
}
else
{
MessageBox.Show("Master record Update failed");
}
sqlite_cmd.Dispose();
SqLite.CloseSQLLiteConnection(sqlite_conn);
}
}
btnMasterDetails.IsEnabled = true;
btnUpdate.IsEnabled = false;
}
