private void cSchool_SelectedIndexChanged(object sender, EventArgs e)
{
if (panel3.Visible == true && RadStudent.Checked)
{
LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - "));
}
if (panel3.Visible == true && radStaff.Checked)
{
LoadRealData(" RegisterVendor.Category = '" + cGrp.Text + "'");
}
}
private void RadStudent_CheckedChanged(object sender, EventArgs e)
{
panel3.Visible = true;
if (RadStudent.Checked)
{
lblGrp.Text = "School";
}
oLoadSchName();
{
LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - "));
}
}
private void TV_AfterSelect(object sender, TreeViewEventArgs e)
{
string strQry = "";
if (e.Node.Text == "ALL")
{
strQry = "<ALL>";
}
else
{
switch (CmbCriteria.Text.ToString())
{
case "State of Origin":
strQry = " RegisterStudent.State = '" + e.Node.Text + "'";
break;
case "Bank":
strQry = " RegisterStudent.BankName = '" + e.Node.Text + "'";
break;
case "Degree":
strQry = " RegisterStudent.Degree = '" + e.Node.Text + "'";
break;
case "EndYear":
strQry = " RegisterStudent.EndYear = " + e.Node.Text;
break;
case "School":
strQry = " RegisterStudent.SchoolID = " + MyModules.GetIt4Me(e.Node.Text, " - ");
// str = "SELECT DISTINCT [SchoolID]+' - ' +[SchName] AS dText FROM RegisterStudent LEFT OUTER JOIN RegisterSchool ON RegisterStudent.SchoolID = RegisterSchool.Sn";
break;
case "Category":
strQry = " RegisterStudent.Category = '" + e.Node.Text + "'";
break;
case "Region":
strQry = " RegisterStudent.Region = '" + e.Node.Text + "'";
break;
}
}
LoadLvList(strQry);
}
private void LoadClientBalances()
{
try
{
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
string strQry1 = "";
cmSQL.Parameters.Clear();
if (RadSchool.Checked == true)
{
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='SCHOOL' GROUP BY RefNo, Name";
}
else if (RadStudent.Checked == true)
{
if (cGrp.Text != "ALL")
{
strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " SchoolID =" + MyModules.GetIt4Me(cGrp.Text, " - ");
}
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='STUDENT' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name";
}
else if (RadVendor.Checked == true)
{
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='VENDOR' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name";
}
else
{
if (cGrp.Text != "ALL")
{
strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " RegisterVendor.Category ='" + cGrp.Text + "'";
}
cmSQL.CommandText = "SELECT Ledger.RefNo, Ledger.Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger LEFT OUTER JOIN RegisterVendor ON Ledger.RefNo = RegisterVendor.Sn WHERE Ledger.Source='STAFF' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY Ledger.RefNo, Ledger.Name";
}
cmSQL.CommandType = CommandType.Text;
dataAdapterA = new SqlDataAdapter(cmSQL);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapterA);
DataTable tableA = new DataTable();
tableA.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapterA.Fill(tableA);
bindingSourceA.DataSource = tableA;
DataGridViewCellStyle myStyle = new DataGridViewCellStyle();
DataGridViewCellStyle myStyle1 = new DataGridViewCellStyle();
myStyle.ForeColor = Color.Red;
myStyle.Format = "N2";
myStyle1.Format = "N2";
myStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
myStyle1.ForeColor = Color.Black;
myStyle1.Alignment = DataGridViewContentAlignment.MiddleRight;
cnSQL.Close();
LoadDetails("-899", "sfsfsfs@@");
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void cBank_SelectedIndexChanged(object sender, EventArgs e)
{
tBank.Text = MyModules.GetIt4Me(cBank.Text, " - ");
tBankCode.Text = MyModules.Mid(cBank.Text, Microsoft.VisualBasic.Strings.Len(tBank.Text) + 3, -1);
}
private void cmdUpload_Click(object sender, EventArgs e)
{
try
{
if (RadStudent.Checked == true)
{
if (Dgrid.Columns.Count != DGridReal.Columns.Count - 3)
{
MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
else
{
if (Dgrid.Columns.Count != DGridReal.Columns.Count - 2)
{
MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
if (cGrp.Text == "" && RadStudent.Checked == true)
{
MessageBox.Show("Please select school", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (Dgrid.RowCount < 1)
{
MessageBox.Show("No record to upload", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SqlTransaction myTrans = null;
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
if (RadStudent.Checked == true)
{
myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
for (int i = 0; i < Dgrid.RowCount - 1; i++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRegisterStudent";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", 0);
cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@State", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[10, i].Value == null) ? "" : Dgrid[10, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[11, i].Value == null) ? "" : Dgrid[11, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[12, i].Value == null) ? "" : Dgrid[12, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[13, i].Value == null) ? "" : Dgrid[13, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[14, i].Value == null) ? "" : Dgrid[14, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@InActive", false);
cmSQL.Parameters.AddWithValue("@SchoolID", MyModules.GetIt4Me(cGrp.Text, " - "));
cmSQL.Parameters.AddWithValue("@Degree", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@CourseOfStudy", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@StartYear", ((Dgrid[4, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[4, i].Value).ToString()));
cmSQL.Parameters.AddWithValue("@EndYear", ((Dgrid[5, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[5, i].Value).ToString()));
cmSQL.Parameters.AddWithValue("@LGA", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@SchName", MyModules.Mid(cGrp.Text, Microsoft.VisualBasic.Strings.Len(MyModules.GetIt4Me(cGrp.Text, " - ")) + 3, -1));
cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());
cmSQL.ExecuteNonQuery();
}
}
if (RadSchool.Checked == true)
{
myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
for (int i = 0; i < Dgrid.RowCount - 1; i++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRegisterSchool";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", 0);
cmSQL.Parameters.AddWithValue("@SchName", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@SchAddress", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@State", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@InActive", false);
cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());
cmSQL.ExecuteNonQuery();
}
}
if (RadVendor.Checked == true)
{
myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
for (int i = 0; i < Dgrid.RowCount - 1; i++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRegisterVendor";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", 0);
cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Source", "VENDOR");
cmSQL.Parameters.AddWithValue("@InActive", false);
cmSQL.Parameters.AddWithValue("@Category", "");
cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());
cmSQL.ExecuteNonQuery();
}
}
if (radStaff.Checked == true)
{
myTrans = cnSQL.BeginTransaction();
cmSQL.Transaction = myTrans;
for (int i = 0; i < Dgrid.RowCount - 1; i++)
{
cmSQL.Parameters.Clear();
cmSQL.CommandText = "InsertRegisterVendor";
cmSQL.CommandType = CommandType.StoredProcedure;
cmSQL.Parameters.AddWithValue("@Sn", 0);
cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@Source", "STAFF");
cmSQL.Parameters.AddWithValue("@InActive", false);
cmSQL.Parameters.AddWithValue("@Category", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper());
cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper());
cmSQL.ExecuteNonQuery();
// str = "SELECT [Sn] AS [RefNo],[Name]0 ,[TINNo]1,[Address]2,[EmailAddress]3 AS [Email Address]
// ,[Telephone]4 ,[BankName]5,[BankAcctNo]6,[BankCode]7 AS [Bank Code],[BankAddress] 8AS [Bank Address],
//[Remark],[InActive] FROM RegisterVendor ORDER BY [Name]";
}
}
myTrans.Commit();
if (RadStudent.Checked)
{
LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - "));
}
else
{
LoadRealData("");
}
MessageBox.Show("Successfull!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (ex.ToString().Contains("Violation of PRIMARY KEY constraint"))
{
MessageBox.Show("Duplicate record exist", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
// myTrans.Rollback();
}
else
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
