protected void btnYes_Click(object sender, EventArgs e)
{
if (ViewState["action"].ToString() == "Save")
{
#region save
con = obje.NewConnection();
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
if (btnSave.Text == "Save")
{
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
OdbcCommand f11 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f11.CommandType = CommandType.StoredProcedure;
f11.Parameters.AddWithValue("tblname", "m_userprevsetting");
f11.Parameters.AddWithValue("attribute", "prev_level");
f11.Parameters.AddWithValue("conditionv", "prev_level =" + int.Parse(txtUserlevel.Text) + " and rowstatus<>2");
OdbcDataAdapter dacnt3 = new OdbcDataAdapter(f11);
DataTable dtt3 = new DataTable();
f11.Transaction = odbTrans;
dacnt3.Fill(dtt3);
if (dtt3.Rows.Count > 0)
{
lblOk.Text = "Level already exists"; lblHead.Text = "Tsunami ARMS - Warning";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
return;
}
//user prev setting
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
OdbcCommand f12 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f12.CommandType = CommandType.StoredProcedure;
f12.Parameters.AddWithValue("tblname", "m_sub_form");
f12.Parameters.AddWithValue("attribute", "form_id");
f12.Parameters.AddWithValue("conditionv", "displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'");
f12.Transaction = odbTrans;
OdbcDataAdapter dacnt31 = new OdbcDataAdapter(f12);
DataTable dtt31 = new DataTable();
dacnt31.Fill(dtt31);
fid = Convert.ToInt32(dtt31.Rows[0][0].ToString());
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd5 = new OdbcCommand("CALL savedata(?,?)", con);
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.AddWithValue("tblname", "m_userprevsetting");
string aaa = "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "";
cmd5.Parameters.AddWithValue("val", "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd5.Transaction = odbTrans;
cmd5.ExecuteNonQuery();
OdbcCommand Del = new OdbcCommand("DELETE from m_userprev_formset where prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
Del.Transaction = odbTrans;
Del.ExecuteNonQuery();
OdbcCommand cmd3 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd3.Transaction = odbTrans;
if (Convert.IsDBNull(cmd3.ExecuteScalar()) == true)
{
o = 1;
}
else
{
o = Convert.ToInt32(cmd3.ExecuteScalar());
o = o + 1;
}
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd2 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd2.Transaction = odbTrans;
if (Convert.IsDBNull(cmd2.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd2.ExecuteScalar());
n = n + 1;
}
OdbcCommand f13 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f13.CommandType = CommandType.StoredProcedure;
f13.Parameters.AddWithValue("tblname", "m_sub_form");
f13.Parameters.AddWithValue("attribute", "form_id");
f13.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt32 = new OdbcDataAdapter(f13);
f13.Transaction = odbTrans;
DataTable dtt32 = new DataTable();
dacnt32.Fill(dtt32);
fid1 = Convert.ToInt32(dtt32.Rows[0][0].ToString());
OdbcCommand cmd6 = new OdbcCommand("CALL savedata(?,?)", con);
cmd6.CommandType = CommandType.StoredProcedure;
cmd6.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd6.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd6.Transaction = odbTrans;
cmd6.ExecuteNonQuery();
}
odbTrans.Commit();
cmbDefault.SelectedIndex = -1;
cmbExecute.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data saved successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
else if (ViewState["action"].ToString() == "Edit")
{
#region edit
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
listboxselection.Enabled = false;
vlistbox.Enabled = false;
btnSave.CausesValidation = false;
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
odbTrans = con.BeginTransaction();
#region log table
OdbcCommand cmd1 = new OdbcCommand("select max(rowno) from m_userprevsetting_log", con);
cmd1.Transaction = odbTrans;
if (Convert.IsDBNull(cmd1.ExecuteScalar()) == true)
{
rn = 1;
}
else
{
rn = Convert.ToInt32(cmd1.ExecuteScalar());
rn = rn + 1;
}
OdbcCommand cmd46p = new OdbcCommand("CALL selectcond(?,?,?)", con);
cmd46p.CommandType = CommandType.StoredProcedure;
cmd46p.Parameters.AddWithValue("tblname", "m_userprevsetting");
cmd46p.Parameters.AddWithValue("attribute", "*");
cmd46p.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
cmd46p.Transaction = odbTrans;
OdbcDataAdapter dacnt46p = new OdbcDataAdapter(cmd46p);
DataTable dtt46p = new DataTable();
dacnt46p.Fill(dtt46p);
OdbcCommand cmd55 = new OdbcCommand("CALL savedata(?,?)", con);
cmd55.CommandType = CommandType.StoredProcedure;
cmd55.Parameters.AddWithValue("tblname", "m_userprevsetting_log");
DateTime Date1 = DateTime.Parse(dtt46p.Rows[0]["createdon"].ToString());
string Date2 = Date1.ToString("yyyy-MM-dd HH:mm:ss");
string aaa = "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "";
cmd55.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "");
cmd55.Transaction = odbTrans;
cmd55.ExecuteNonQuery();
OdbcCommand crr1 = new OdbcCommand("delete from m_userprev_formset_log where prev_level=" + q + "", con);
crr1.Transaction = odbTrans;
crr1.ExecuteNonQuery();
OdbcCommand cmd6 = new OdbcCommand("select max(rowno)from m_userprev_formset_log", con);
cmd6.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6.ExecuteScalar());
n = n + 1;
}
OdbcCommand f16 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f16.CommandType = CommandType.StoredProcedure;
f16.Parameters.AddWithValue("tblname", "m_userprev_formset");
f16.Parameters.AddWithValue("attribute", "*");
f16.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
f16.Transaction = odbTrans;
OdbcDataAdapter dacnt3o = new OdbcDataAdapter(f16);
DataTable dtt3o = new DataTable();
dacnt3o.Fill(dtt3o);
OdbcCommand cmd24 = new OdbcCommand("CALL savedata(?,?)", con);
cmd24.CommandType = CommandType.StoredProcedure;
cmd24.Parameters.AddWithValue("tblname", "m_userprev_formset_log");
DateTime Date4 = DateTime.Parse(dtt3o.Rows[0]["createdon"].ToString());
string Date5 = Date4.ToString("yyyy-MM-dd HH:mm:ss");
cmd24.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt3o.Rows[0]["prev_forms_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["form_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["createdby"]) + ",'" + Date5.ToString() + "'," + "1" + "," + n + "");
cmd24.Transaction = odbTrans;
cmd24.ExecuteNonQuery();
#endregion
OdbcCommand defa1 = new OdbcCommand("select form_id from m_sub_form where displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'", con);
defa1.Transaction = odbTrans;
OdbcDataReader defr1 = defa1.ExecuteReader();
if (defr1.Read())
{
fid = Convert.ToInt32(defr1["form_id"].ToString());
}
id = Convert.ToInt32(Session["userid"].ToString());
m1 = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
OdbcCommand cmd25 = new OdbcCommand("call updatedata(?,?,?)", con);
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
cmd25.CommandType = CommandType.StoredProcedure;
cmd25.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd25.Parameters.AddWithValue("valu", "prev_level=" + int.Parse(txtUserlevel.Text) + ",defaultform_id=" + fid + ",execoverride=" + b + ",userid=" + id + ",updateddate='" + dat + "',rowstatus=" + "1" + "");
cmd25.Parameters.AddWithValue("convariable", "prev_level=" + m1 + "");
cmd25.Transaction = odbTrans;
cmd25.ExecuteNonQuery();
OdbcCommand crr = new OdbcCommand("delete from m_userprev_formset where prev_level=" + m1 + "", con);
crr.Transaction = odbTrans;
crr.ExecuteNonQuery();
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd6a = new OdbcCommand("select max(prev_forms_id)from m_userprev_formset", con);
cmd6a.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6a.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6a.ExecuteScalar());
n = n + 1;
}
OdbcCommand f15 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f15.CommandType = CommandType.StoredProcedure;
f15.Parameters.AddWithValue("tblname", "m_sub_form");
f15.Parameters.AddWithValue("attribute", "form_id");
f15.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt3a = new OdbcDataAdapter(f15);
f15.Transaction = odbTrans;
DataTable dtt3a = new DataTable();
dacnt3a.Fill(dtt3a);
fid1 = Convert.ToInt32(dtt3a.Rows[0]["form_id"].ToString());
OdbcCommand cmd61 = new OdbcCommand("CALL savedata(?,?)", con);
cmd61.CommandType = CommandType.StoredProcedure;
cmd61.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd61.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "1" + "");
cmd61.Transaction = odbTrans;
cmd61.ExecuteNonQuery();
}
odbTrans.Commit();
btnSave.Text = "Save";
cmbExecute.SelectedIndex = -1;
cmbDefault.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data updated successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#region CHECK PRIVILEGE LEVEL IS ALREADY EXISTS OR NOT
else if (ViewState["action"].ToString() == "Level")
{
this.ScriptManager1.SetFocus(txtUserlevel);
txtUserlevel.Text = Session["prevlevel"].ToString();
lstSelectform.SelectedIndex = -1;
con = obje.NewConnection();
OdbcCommand PLevel = new OdbcCommand("DELETE from m_userprevsetting WHERE prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
PLevel.ExecuteNonQuery();
con.Close();
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#endregion
else if (ViewState["action"].ToString() == "Delete")
{
#region delete
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd28 = new OdbcCommand("call updatedata(?,?,?)", con);
cmd28.CommandType = CommandType.StoredProcedure;
cmd28.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd28.Parameters.AddWithValue("valu", "rowstatus=" + "2" + ",userid=" + id + "");
cmd28.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd28.Transaction = odbTrans;
cmd28.ExecuteNonQuery();
OdbcCommand cmd29 = new OdbcCommand("CALL updatedata(?,?,?)", con);
cmd29.CommandType = CommandType.StoredProcedure;
cmd29.Parameters.AddWithValue("tablename", "m_userprev_formset");
cmd29.Parameters.AddWithValue("valu", "rowstatus=" + "2" + "");
cmd29.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd29.Transaction = odbTrans;
cmd29.ExecuteNonQuery();
odbTrans.Commit();
lblOk.Text = " Data successfully deleted "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
con.Close();
clear();
dguserlevel();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in Deleting ");
}
#endregion
}
}
public DataSet GetFormDataBE2012(string strPage)
{
string strQuery = "";
OdbcCommand cmdOdbc = new OdbcCommand();
OdbcDataAdapter daOdbc;
DataTable dtTemp = new DataTable();
DataSet dsData = new DataSet();
try
{
if (connOdbc.State == ConnectionState.Closed)
{
connOdbc.Open();
}
switch (strPage)
{
case "BE2012Page1":
strQuery = "SELECT TP_PASSPORT_NO, TP_COUNTRY, TP_GENDER, TP_STATUS, FORMAT(TP_DATE_MARRIAGE,'dd/mm/yyyy'), " +
"FORMAT(TP_DATE_DIVORCE,'dd/mm/yyyy'), TP_TYPE_ASSESSMENT, TP_KUP, TP_CURR_ADD_LINE1, TP_CURR_ADD_LINE2, " +
"TP_CURR_ADD_LINE3, TP_CURR_POSTCODE, TP_CURR_CITY, TP_CURR_STATE, TP_TEL1, " +
"TP_TEL2, TP_EMPLOYER_NO2, TP_EMPLOYER_NO3, TP_EMAIL, TP_BANK, " +
"TP_BANK_ACC, TP_ASSESSMENTON " +
"FROM TAXP_PROFILE WHERE TP_5=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select b.bk_desc from taxp_profile t, bank b where tp_5=? and t.tp_bank=b.bk_name";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_SELECT_BANK");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//weihong
strQuery = "select FORMAT(TP_WORKER_APPROVEDATE,'dd/mm/yyyy'), TP_COM_ADD_STATUS from TAXP_PROFILE2 where TP_REF_NO=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_TAX_PROFILE2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page2":
strQuery = "SELECT TP_HW_NAME, TP_HW_REF_NO_PREFIX, TP_HW_REF_NO1, TP_HW_REF_NO2, TP_HW_REF_NO3, " +
"TP_HW_IC_NEW1, TP_HW_IC_NEW2, TP_HW_IC_NEW3, TP_HW_POLICE_NO, TP_HW_ARMY_NO, " +
"TP_HW_PASSPORT_NO, TP_HW_TYPEOFINCOME " +
"FROM TAXP_PROFILE WHERE TP_5=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT TP_HW_NAME, TP_HW_REF_NO_PREFIX, TP_HW_REF_NO1, TP_HW_REF_NO2, TP_HW_REF_NO3, " +
"TP_HW_IC_NEW1, TP_HW_IC_NEW2, TP_HW_IC_NEW3, TP_HW_POLICE_NO, TP_HW_ARMY_NO, " +
"TP_HW_PASSPORT_NO, TP_HW_TYPEOFINCOME " +
"FROM TAXP_PROFILE_HW_OTHERS WHERE TP_REF_NO=? ORDER BY TP_HW_ORDER";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_TAX_PROFILE_HW_OTHERS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page3":
//LEESH 24 FEB 2012
strQuery = "select TC_EMPLOYMENT_INCOME, TC_DIVIDEND, TC_INTEREST, TC_DISCOUNT, TC_RENTAL_ROYALTY, " +
"TC_PREMIUM, TC_PENSION_AND_ETC, TC_OTHER_GAIN_PROFIT, TC_SEC4A, TC_ADDITION_43, " +
"TC_INCOME_TRANSFER_FROM_HW, TC_INSTALLMENT_PAYMENT_SELF, TC_INSTALLMENT_PAYMENT_HW, TC_EXHK3_TRANSFER_FROM_HW " +
"FROM TAX_COMPUTATION WHERE TC_REF_NO=? AND TC_YA=?";
//LEESH END
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_TAX_COMPUTATION");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//DANNYLEE 15 FEB 2013
strQuery = "select (CDBL(OS_DV_STAT_INCOME)+CDBL(OS_RT_RENTAL_BF)), OS_RT_RENTAL_BF " +
"FROM INCOME_OTHERSOURCE WHERE OS_REF_NO=? AND OS_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_DIVIDEND_RENTAL");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//DANNYLEE END
//DANNYLEE 15 FEB 2013
strQuery = "SELECT ((cdbl(TAX_COMPUTATION.TC_INTEREST)+cdbl(TAX_COMPUTATION.TC_DISCOUNT)+" +
"cdbl(TAX_COMPUTATION.TC_RENTAL_ROYALTY)+cdbl(TAX_COMPUTATION.TC_PREMIUM)+" +
"cdbl(TAX_COMPUTATION.TC_PENSION_AND_ETC)+cdbl(TAX_COMPUTATION.TC_OTHER_GAIN_PROFIT)+" +
"cdbl(TAX_COMPUTATION.TC_SEC4A))- CDBL(INCOME_OTHERSOURCE.OS_RT_RENTAL_BF )) " +
"FROM TAX_COMPUTATION INNER JOIN INCOME_OTHERSOURCE " +
"ON TAX_COMPUTATION.TC_REF_NO=INCOME_OTHERSOURCE.OS_REF_NO " +
"AND TAX_COMPUTATION.TC_YA = INCOME_OTHERSOURCE.OS_YA " +
"WHERE TAX_COMPUTATION.TC_REF_NO=? AND TAX_COMPUTATION.TC_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INCOME_EXCLUDE_RENTAL");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//END
//dannylee 21/02/2013
//strQuery = "select TCG_KEY, TCG_AMOUNT " +
//"FROM TAX_GIFTS WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) ORDER BY TCG_KEY";
strQuery = "select SUM(TCG_AMOUNT) " +
"FROM TAX_GIFTS WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?)";
//end
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_TAX_GIFTS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
dsData.Tables["P3_TAX_GIFTS"].PrimaryKey = new DataColumn[] { dsData.Tables["P3_TAX_GIFTS"].Columns["TCG_KEY"] };
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT TP_HW_NAME, TP_HW_REF_NO_PREFIX, TP_HW_REF_NO1, TP_HW_REF_NO2, TP_HW_REF_NO3, " +
"TP_HW_IC_NEW1, TP_HW_IC_NEW2, TP_HW_IC_NEW3, TP_HW_POLICE_NO, TP_HW_ARMY_NO, " +
"TP_HW_PASSPORT_NO, TP_HW_TYPEOFINCOME " +
"FROM TAXP_PROFILE WHERE TP_5=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//NGOHCS B2010.2
strQuery = "SELECT TP_HW_NAME, TP_HW_REF_NO_PREFIX, TP_HW_REF_NO1, TP_HW_REF_NO2, TP_HW_REF_NO3, " +
"TP_HW_IC_NEW1, TP_HW_IC_NEW2, TP_HW_IC_NEW3, TP_HW_POLICE_NO, TP_HW_ARMY_NO, " +
"TP_HW_PASSPORT_NO, TP_HW_TYPEOFINCOME " +
"FROM TAXP_PROFILE_HW_OTHERS WHERE TP_REF_NO=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_TAX_PROFILE_OTHER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//NGOHCS B2010.2 END
//weihong Gross income from employment
strQuery = "select EI_SCHEDULE1, EI_GROSS from INCOME_EMPLOYMENT where EI_REF_NO=? and EI_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INCOME_EMPLOYMENT");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//weihong
//weihong Gross income from employment
strQuery = "select OS_OTHER_GAINS_TOTAL, OS_PENSION_AND_ETC, OS_SEC4A, OS_RY_GROSS_ROYALTY110, OS_RY_ROYALTY_INCOME, OS_INT_GROSS_RECEIVED, OS_INT_LOAN, OS_RT_GROSS_RENTAL, OS_DV_GROSS_DIVIDEND from INCOME_OTHERSOURCE where OS_REF_NO=? and OS_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INCOME_OTHERSOURCE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//weihong
//weihong Total gross income from all source
strQuery = "select PL_SALES from PROFIT_LOSS_ACCOUNT where PL_REF_NO=? and PL_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PROFIT_LOSS_ACCOUNT");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//weihong
break;
case "BE2012Page4":
strQuery = "SELECT TOP 5 PY_INCOME_TYPE, PY_PAYMENT_YEAR, PY_AMOUNT, PY_EPF " +
"FROM PRECEDING_YEAR_DETAIL WHERE PY_KEY IN (SELECT PY_KEY FROM PRECEDING_YEAR WHERE PY_REF_NO=? and PY_YA=?) ORDER BY PY_DKEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PRECEDING_YEAR_DETAIL");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page5":
strQuery = "select TCC_KEY, TCC_AMOUNT " +
"FROM TAX_RELIEF WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) ORDER BY TCC_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
dsData.Tables["P4_TAX_RELIEF"].PrimaryKey = new DataColumn[] { dsData.Tables["P4_TAX_RELIEF"].Columns["TCC_KEY"] };
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) ORDER BY TCC_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO IN (SELECT TP_HW_REF_NO1 FROM TAXP_PROFILE WHERE TP_5=?) AND TC_YA=?) ORDER BY TCC_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_HW");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO IN (SELECT TP_HW_REF_NO1 FROM TAXP_PROFILE_HW_OTHERS WHERE TP_REF_NO=?) AND TC_YA=?) ORDER BY TCC_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_HW_OTHERS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 14 AND TCC_RELIEF = '1,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_UNDER18_1000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 14 AND TCC_RELIEF = '500'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_UNDER18_500");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 15 AND TCC_RELIEF = '1,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_18ABOVE_1000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 15 AND TCC_RELIEF = '500'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_18ABOVE_500");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 15 AND TCC_RELIEF = '4,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_18ABOVE_4000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 15 AND TCC_RELIEF = '2,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_18ABOVE_2000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 16 AND TCC_RELIEF = '5,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_DISABLED_5000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 16 AND TCC_RELIEF = '2,500'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_DISABLED_2500");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 16 AND TCC_RELIEF = '9,000'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_DISABLED_9000");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TCC_KEY, TCC_100, TCC_50 " +
"FROM TAX_RELIEF_CHILD WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) AND TCC_KEY = 16 AND TCC_RELIEF = '4,500'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_TAX_RELIEF_CHILD_DISABLED_4500");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page6":
strQuery = "select TCR_KEY, TCR_AMOUNT " +
"FROM TAX_REBATE WHERE TC_KEY IN (SELECT TC_KEY FROM TAX_COMPUTATION WHERE TC_REF_NO=? and TC_YA=?) ORDER BY TCR_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_TAX_REBATE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
dsData.Tables["P5_TAX_REBATE"].PrimaryKey = new DataColumn[] { dsData.Tables["P5_TAX_REBATE"].Columns["TCR_KEY"] };
daOdbc.Dispose();
cmdOdbc.Dispose();
//dannylee 21/02/2013
//strQuery = "select TC_SEC110_DIVIDEND, TC_SEC110_OTHERS, TC_1, TC_2 " +
strQuery = "select TC_SEC110_DIVIDEND, TC_SEC110_OTHERS, (cdbl(TC_1)+cdbl(TC_2)) " +
"FROM TAX_COMPUTATION WHERE TC_REF_NO=? AND TC_YA=?";
//end
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_TAX_COMPUTATION");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page7":
strQuery = "select TP_ADM_NAME, TP_ADM_IC_NEW1, TP_ADM_IC_NEW2, TP_ADM_IC_NEW3, TP_ADM_POLICE_NO, TP_ADM_ARMY_NO, TP_ADM_PASSPORT_NO " +
"FROM TAXP_PROFILE WHERE TP_5=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXADM_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select TA_TEL_NO " +
"FROM TAXA_PROFILE WHERE TA_CO_NAME=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxagent", strTaxAgent));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXA_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
case "BE2012Page9":
strQuery = "select format(div_date,'dd MMM yyyy') as [Date of Payment], Iif(div_year_end is null,format(div_date,'dd MMM yyyy'),format(div_year_end,'dd MMM yyyy')) as [Year End], div_serialno as [Waran No], div_company as [Company Name], div_gross as [Gross Dividend], DIV_RATE as [Tax Rate], "
+ "div_tax as [Tax Deducted], div_net as [Net Dividend], os_dv_interest as [Dividend Interest]"
+ "from income_othersource inner join income_os_dividend on income_othersource.os_key = income_os_dividend.os_key "
+ "where income_othersource.os_ref_no =? and income_othersource.os_ya =?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P12_HK3_MASTER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select format(div_date,'dd MMM yyyy') as [Date of Payment], Iif(div_year_end is null,format(div_date,'dd MMM yyyy'),format(div_year_end,'dd MMM yyyy')) as [Year End], div_serialno as [Waran No], div_company as [Company Name], div_gross as [Gross Dividend], DIV_RATE as [Tax Rate], "
+ "div_tax as [Tax Deducted], div_net as [Net Dividend], os_dv_interest as [Dividend Interest] "
+ "from income_othersource inner join income_os_dividend on income_othersource.os_key = income_os_dividend.os_key "
+ "where income_othersource.os_ref_no in (select tp_hw_ref_no1 from taxp_profile_hw_others where tp_ref_no = ? and income_othersource.os_ya =?) "
+ "UNION select format(div_date,'dd MMM yyyy') as [Date of Payment], Iif(div_year_end is null,format(div_date,'dd MMM yyyy'),format(div_year_end,'dd MMM yyyy')) as [Year End], div_serialno as [Waran No], div_company as [Company Name], div_gross as [Gross Dividend], DIV_RATE as [Tax Rate], "
+ "div_tax as [Tax Deducted], div_net as [Net Dividend], os_dv_interest as [Dividend Interest] "
+ "from income_othersource inner join income_os_dividend on income_othersource.os_key = income_os_dividend.os_key "
+ "where income_othersource.os_ref_no = (select tp_hw_ref_no1 from taxp_profile where (tp_ref_no1+tp_ref_no2+tp_ref_no3) = ?) and income_othersource.os_ya =?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer2", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya2", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P12_HK3HW_MASTER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
}
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.ToString()); }
return(dsData);
}
/// <summary>
/// Query that returns no data but number of affected rows. This query works transactional, changes done are will be
/// rolled back in case of errors.
/// </summary>
/// <param name="sqlStatement">The SQL statement.</param>
/// <returns>
/// The result of NonQuery. -4 general exception, -3 exception on rollback, -2 rollback done, all other depend on the
/// SQL
/// statement itself.
/// </returns>
public int NonQueryWithTransaction(string sqlStatement)
{
int retryCount = 0;
int result = 0;
if (this.ConnectionType == ConnType.MSSQL)
{
while (true)
{
try
{
using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString))
{
con.Open();
var adapter = new SqlDataAdapter();
SqlTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new SqlCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
else
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
break;
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
this.WriteErrorLog(ex.ToString(), sqlStatement);
throw;
}
}
}
else if (this.ConnectionType == ConnType.ODBC)
{
using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString))
{
con.Open();
var adapter = new OdbcDataAdapter();
OdbcTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new OdbcCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
else if (this.ConnectionType == ConnType.OLEDB)
{
using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString))
{
con.Open();
var adapter = new OleDbDataAdapter();
OleDbTransaction transaction = con.BeginTransaction();
try
{
adapter.SelectCommand = new OleDbCommand(sqlStatement, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
adapter.SelectCommand.Transaction = transaction;
result = adapter.SelectCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
result = -2;
this.WriteErrorLog(ex.ToString(), sqlStatement);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
throw;
}
catch (Exception ex2)
{
result = -3;
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
FileOperation.LogError(ex2, FileOperation.MaxLogFileSize);
throw;
}
}
}
}
return(result);
}
public DbScriptAccess()
{
da = new OdbcDataAdapter();
}
public DataTable LoadDataTable(string connectionString, string sql)
{
if (MyLoadDataTable != null)
{
return(MyLoadDataTable(connectionString, sql));
}
DataTable table = new DataTable();
if (UseDbDataAdapter)
{
DbDataAdapter adapter = null;
var connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
if (connection is OdbcConnection)
{
adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
}
else
{
adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
}
adapter.SelectCommand.CommandTimeout = SelectTimeout;
adapter.Fill(table);
}
else
{
DbCommand cmd = new OdbcCommand();
var connection = Helper.DbConnectionFromConnectionString(connectionString);
connection.Open();
if (connection is OdbcConnection)
{
cmd = new OdbcCommand(sql, (OdbcConnection)connection);
}
else
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
}
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
DbDataReader dr = cmd.ExecuteReader();
DataTable schemaTable = dr.GetSchemaTable();
foreach (DataRow dataRow in schemaTable.Rows)
{
DataColumn dataColumn = new DataColumn();
dataColumn.ColumnName = dataRow["ColumnName"].ToString();
dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.Unique = (bool)dataRow["IsUnique"];
for (int i = 0; i < table.Columns.Count; i++)
{
if (dataColumn.ColumnName == table.Columns[i].ColumnName)
{
dataColumn.ColumnName += "_" + table.Columns.Count.ToString();
}
}
table.Columns.Add(dataColumn);
}
while (dr.Read())
{
DataRow dataRow = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dataRow[i] = dr[i];
}
table.Rows.Add(dataRow);
}
}
return(table);
}
public DataSet GetFormDataP2010(string strPage)
{
string strQuery = "";
OdbcCommand cmdOdbc = new OdbcCommand();
OdbcDataAdapter daOdbc;
DataTable dtTemp = new DataTable();
DataSet dsData = new DataSet();
try
{
if (connOdbc.State == ConnectionState.Closed)
{
connOdbc.Open();
}
switch (strPage)
{
#region "Page1"
case "P2010Page1":
//strQuery= "SELECT PT_NAME, PT_REF_NO, " &
strQuery = "SELECT PT_NAME, PT_REF_NO, " +
"PT_REGISTER_NO, PT_NO_PARTNERS, PT_APPORTIONMENT, PT_COMPLIANCE, " +
"PT_REG_ADDRESS1 , PT_REG_ADDRESS2 ,PT_REG_ADDRESS3,PT_REG_POSTCODE,PT_REG_CITY,PT_REG_STATE," +
"PT_BUS_ADDRESS1 , PT_BUS_ADDRESS2 , PT_BUS_ADDRESS3 ,PT_BUS_POSTCODE,PT_BUS_CITY,PT_BUS_STATE," +
"PT_COR_ADDRESS1 , PT_COR_ADDRESS2 , PT_COR_ADDRESS3 ,PT_COR_POSTCODE, PT_COR_CITY,PT_COR_STATE," +
"PT_ACC_ADDRESS1 ,PT_ACC_ADDRESS2, PT_ACC_ADDRESS3,PT_ACC_POSTCODE, PT_ACC_CITY,PT_ACC_STATE," +
"PT_EMPLOYER_NO2,PT_PRE_PARTNER,PT_TEL1 + PT_TEL2,PT_MOBILE1 + PT_MOBILE2,PT_EMAIL, PT_BWA " +
"FROM TAXP_PROFILE where PT_REF_NO=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_INPUT_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page2"
case "P2010Page2": //Pendapatan Perniagaan
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND [P_YA]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader drP = cmdOdbc.ExecuteReader();
string strTemp = "";
if (drP.HasRows)
{
while (drP.Read())
{
strTemp = drP.GetString(0);
}
}
//drP.Dispose;
//cmdOdbc.Dispose();
strQuery = "SELECT PI_TYPE,PI_PIONEER_INCOME,PI_INCOME_LOSS,PI_P_BEBEFIT,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? ORDER BY [PS_SOURCENO]";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr = cmdOdbc.ExecuteReader();
string strTemp10 = "";
string strTemp11 = "";
if (dr.HasRows)
{
while (dr.Read())
{
strTemp10 = dr.GetString(6);
strTemp11 = dr.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp10 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp10));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
string strTemp12 = "";
string strTemp13 = "";
if (dr2.HasRows)
{
while (dr2.Read())
{
strTemp12 = dr2.GetString(6);
strTemp13 = dr2.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp12 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp13));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp12));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
break;
#endregion
#region "Page3"
case "P2010Page3": //Pendapatan Perniagaan
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
string strTempP3 = "";
if (drP3.HasRows)
{
while (drP3.Read())
{
strTempP3 = drP3.GetString(0);
}
}
////empty A8-A14
strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]=3 and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//empty
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=3 ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_SELECT_TAXP");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
////>S3 A8-A14
strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]>=3 and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//>S3
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO>=3 ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_SELECT_TAXP1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Comment it for next project"
//#region "Page2"
//case "P2009Page2": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP = cmdOdbc.ExecuteReader();
// string strTemp = "";
// if (drP.HasRows)
// {
// while (drP.Read())
// { strTemp = drP.GetString(0);}
// }
// //drP.Dispose;
// //cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes'";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// //cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// // cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT TOP 1 PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] = (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes')";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// OdbcDataReader dr = cmdOdbc.ExecuteReader();
// string strTemp6 = "";
// if (dr.HasRows)
// {
// while (dr.Read())
// { strTemp6 = dr.GetString(0); }
// }
// dr.Close();
// cmdOdbc.Dispose();
// if (strTemp6 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? AND [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp6));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// strQuery = "SELECT TOP 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
// string strTemp1 = "";
// string strTemp7 = "";
// if (dr2.HasRows)
// {
// while (dr2.Read())
// {
// strTemp1 = dr2.GetString(0);
// strTemp7 = dr2.GetString(1);
// }
// }
// dr2.Close();
// cmdOdbc.Dispose();
// if (strTemp1 != "" && strTemp7 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp7));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp1));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
//#region "Page3"
//case "P2009Page3": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
// string strTempP3 = "";
// if (drP3.HasRows)
// {
// while (drP3.Read())
// { strTempP3 = drP3.GetString(0); }
// }
// ////empty A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_REF_NO]=" +
// "(SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes') and [PI_TYPE]='' ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr3 = cmdOdbc.ExecuteReader();
// string strTemp8 = "";
// if (dr3.HasRows)
// {
// while (dr3.Read())
// {
// strTemp8 = dr3.GetString(0);
// }
// }
// dr3.Close();
// cmdOdbc.Dispose();
// //empty
// if (strTemp8 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@sourceno", strTemp8));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// ////>S3 A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
// "FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] not in (SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') " +
// "ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr4 = cmdOdbc.ExecuteReader();
// string strTemp9 = "";
// string strTemp10 = "";
// if (dr4.HasRows)
// {
// while (dr4.Read())
// {
// strTemp9 = dr4.GetString(0);
// strTemp10 = dr4.GetString(1);
// }
// }
// dr4.Close();
// cmdOdbc.Dispose();
// //>S3
// if (strTemp9 != "" && strTemp10 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp10));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@source", strTemp9));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
#endregion
#region "Page4"
case "P2010Page4":
strQuery = "SELECT P_DIVISIBLE_INT_DIS,P_DIVISIBLE_RENT_ROY_PRE,P_DIVISIBLE_NOTLISTED,P_DIVISIBLE_ADD_43,P_TAXDED_110," +
" P_TAXDED_132,P_TAXDED_133,P_DIVISIBLE_ADD_43 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr1 = cmdOdbc.ExecuteReader();
string strTemp2 = "";
if (dr1.HasRows)
{
while (dr1.Read())
{
strTemp2 = dr1.GetString(0);
}
}
//drP.Dispose;
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PY_INCOME_TYPE,PY_PAYMENT_YEAR,PY_AMOUNT,PY_EPF FROM [PRECEDING_YEAR] WHERE [P_KEY] = ? order by [PY_DKEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp2));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PRECEDING_YEAR");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT P_DIV_MALDIV,P_TAX_MALDIV FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page5"
case "P2010Page5":
strQuery = "SELECT P_DIVS_EXP_1,P_DIVS_EXP_3,P_DIVS_EXP_4,P_DIVS_EXP_5,P_DIVS_EXP_8 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr8 = cmdOdbc.ExecuteReader();
string strTemp3 = "";
if (dr8.HasRows)
{
while (dr8.Read())
{
strTemp3 = dr8.GetString(0);
}
}
dr8.Close();
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PC_CL_CODE,PC_AMOUNT FROM [P_OTHER_CLAIMS] WHERE [P_KEY] = ? order by [PC_KEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_P_OTHER_CLAIMS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page6"
case "P2010Page6":
strQuery = "SELECT P_WITHTAX_107A_GROSS,P_WITHTAX_107A_TAX," +
" P_WITHTAX_109_GROSS, P_WITHTAX_109_TAX," +
" P_WITHTAX_109A_GROSS,P_WITHTAX_109A_TAX," +
" P_WITHTAX_109B_GROSS,P_WITHTAX_109B_TAX," +
" P_WITHTAX_109F_GROSS,P_WITHTAX_109F_TAX" +
" FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr6 = cmdOdbc.ExecuteReader();
string strTemp4 = "";
if (dr6.HasRows)
{
while (dr6.Read())
{
strTemp4 = dr6.GetString(0);
}
}
else
{
strTemp4 = "0";
}
// dr4.Dispose;
cmdOdbc.Dispose();
strQuery = "SELECT P_CP30_ASAL,format(P_CP30_ASAL_DATE,'dd/mm/yyyy'), P_CP30_PINDAAN, format(P_CP30_PINDAAN_DATE,'dd/mm/yyyy') FROM [PARTNERSHIP_INCOME] where [P_KEY]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp4));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page7"
case "P2010Page7":
strQuery = "SELECT [PT_KEY] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr5 = cmdOdbc.ExecuteReader();
string strTemp5 = "0";
if (dr5.HasRows)
{
while (dr5.Read())
{
strTemp5 = dr5.GetString(0);
}
}
cmdOdbc.Dispose();
dr5.Close();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND [P_YA]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr9 = cmdOdbc.ExecuteReader();
string strTemp9 = "0";
if (dr9.HasRows)
{
while (dr9.Read())
{
strTemp9 = dr9.GetString(0);
}
}
cmdOdbc.Dispose();
dr9.Close();
strQuery = "select PN_PREFIX As [PREFIX],PN_REF_NO AS [REFERENCE_NO]," +
"PN_NAME AS [NAME],PN_IDENTITY AS [IDENTITY],PN_COUNTRY AS [COUNTRY]," +
"PN_DATE_APPOINTNENT AS [DATE_APPOINTMENT]," +
"PN_DATE_CESSATION AS [DATE_CESSATION],PN_SHARE AS [SHARE], " +
"PN_BENEFIT_1 AS [BENEFIT_1],PN_BENEFIT_2 AS [BENEFIT_2],PN_BENEFIT_3 AS [BENEFIT_3]," +
"CP_B_ADJ_INCOMELOSS AS [ADJUSTED_AMOUNT], CP_B_BAL_CHARGE AS [BALANCING_CHARGE]," +
"CP_B_BAL_ALLOWANCE AS [BALANCING_ALLOWANCE], CP_B_7A_ALLOWANCE AS [SCHEDULE_7A]," +
"CP_B_EXP_ALLOWANCE AS [EXPORT_ALLOWANCE] " +
"from CP30 a INNER JOIN TAXP_PARTNERS b on a.CP_REF_NO = b.PN_REF_NO " +
"WHERE [PT_KEY] = ? AND [P_KEY] = ? order by PN_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PartnerKey", strTemp5));
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp9));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PARTNERS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [PT_PRE_PARTNER] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PREPARTNER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page8"
case "P2010Page8":
strQuery = "SELECT [PS_CODE] FROM [TAXP_PSOURCE] where PS_REF_NO=? and PS_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_TAXP_PSOURCE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PPL_SALES,PPL_OP_STK,PPL_PURCHASES_COST,PPL_CLS_STK, PPL_COGS," +
"PPL_OTH_BSIN,PPL_OTH_IN_DIVIDEND,PPL_OTH_IN_INTEREST,PPL_OTH_IN_RENTAL_ROYALTY,PPL_OTH_IN_OTHER," +
"PPL_EXP_LOANINTEREST,PPL_EXP_SALARY,PPL_EXP_RENTAL,PPL_EXP_CONTRACT,PPL_EXP_COMMISSION," +
"PPL_BAD_DEBTS,PPL_TRAVEL,PPL_EXP_REPAIR_MAINT,PPL_EXP_PRO_ADV,PPL_OTHER_EXP, " +
"PPL_NET_PROFIT,PPL_DISALLOWED_EXP FROM [P_PROFIT_AND_LOSS] where P_REF_NO=? and P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT BS_LAND,BS_MACHINERY,BS_TRANSPORT,BS_OTH_FA," +
"BS_INVESTMENT, BS_STOCK,BS_TRADE_DEBTORS,BS_OTH_DEBTORS,BS_CASH,BS_BANK,BS_OTH_CA," +
"BS_LOAN, BS_TRADE_CR,BS_OTHER_CR,BS_OTH_LIAB,BS_LT_LIAB, " +
"BS_CAPITALACCOUNT,BS_BROUGHT_FORWARD,BS_CY_PROFITLOSS,BS_DRAWING, BS_CARRIED_FORWARD " +
"FROM [P_BALANCE_SHEET] where P_REF_NO=? and P_YA=? order by BS_SOURCENO";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page9"
case "P2010Page9":
if (connOdbcB.State == ConnectionState.Closed)
{
connOdbcB.Open();
}
strQuery = "select ta_add_line1, ta_add_line2, ta_add_line3, ta_add_postcode, ta_add_city, ta_add_state, ta_tel_no, TA_MOBILE, ta_email from taxa_profile where ta_co_name=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbcB);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxagent", strTaxAgent));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P8_FIRM");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
}
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.ToString()); }
return(dsData);
}
public OdbcDataAdapter llenarLibroDiario()
{
OdbcDataAdapter dataTable = Mayor.LlenarTablaDiarioEncabezado();
return(dataTable);
}
internal static DataTable RunQuery(string dateClause, string styleClause)
{
try {
using (OdbcConnection conn = new OdbcConnection(connectionString)) {
conn.Open();
string query = $@"
SELECT det.dhous, det.scdat, det.endat, det.ordnr, det.orvch,
det.ditem, det.dlsiz, siz.letwid, nam.letname,
det.dlwr1, det.dlwr2, det.dlwr3, det.dlwr4,
CASE WHEN det.ditem LIKE 'SIGN%' THEN clr.gclr ELSE TRIM(det.dclr1) END AS dclr1, det.dclr2, det.dclr3, det.dclr4, det.rudat
FROM (
SELECT d.dhous,
CASE WHEN d.dscmo = 0 THEN NULL ELSE DATE(d.dsccy||d.dscyr||'-'||RIGHT('00'||d.dscmo, 2)||'-'||RIGHT('00'||d.dscda, 2)) END AS scdat,
DATE(d.dorcy||d.doryr||'-'||RIGHT('00'||d.dormo, 2)||'-'||RIGHT('00'||d.dorda, 2)) AS endat,
d.ordnr, d.orvch, d.dpvch, TRIM(d.ditem) AS ditem, d.dlsiz,
TRIM(d.dlwr1) as dlwr1, TRIM(d.dlwr2) as dlwr2, TRIM(d.dlwr3) as dlwr3, TRIM(d.dlwr4) as dlwr4,
TRIM(d.dclr1) AS dclr1, TRIM(d.dclr2) AS dclr2, TRIM(d.dclr3) AS dclr3, TRIM(d.dclr4) AS dclr4,
CASE d.drumo WHEN 0 THEN NULL ELSE DATE(d.drucy||d.druyr||'-'||RIGHT('00'||d.drumo, 2)||'-'||RIGHT('00'||d.druda, 2)) END AS rudat
FROM VARSITYF.DETAIL AS d
WHERE ({dateClause}) AND ({styleClause}) AND (d.dscda > 0)
) AS det
LEFT JOIN
DJLIBR.ORD_NAM_C
AS nam
ON det.ordnr = nam.ordnr AND det.orvch = nam.orvch AND nam.letname <> ''
LEFT JOIN (
SELECT DISTINCT s.ordnr, s.orvch, s.letwid
FROM VARSITYF.HLDSIZ AS s
) AS siz
ON det.ordnr = siz.ordnr AND det.dpvch = siz.orvch
LEFT JOIN
VARSITYF.HLDCLR
AS clr
ON det.ordnr = clr.ordnr AND det.orvch = clr.orvch AND clr.itseq = 2
ORDER BY det.ditem";
OdbcCommand command = new OdbcCommand(query);
command.Connection = conn;
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();
return(table);
}
} catch (OdbcException e) {
if (e.Errors[0].SQLState == "IM002")
{
ErrorHandler.HandleError(ErrorType.Alert, "Driver not found.\n\nPlease contact the IT Department to install the ODBC Driver for IBM iSeries Access.");
return(null);
}
else
{
string odbcErrorLog = "";
for (int i = 0; i != e.Errors.Count; ++i)
{
odbcErrorLog += ("Error " + (i + 1) + " of " + e.Errors.Count + "\n");
odbcErrorLog += ("SQLState: " + e.Errors[i].SQLState + "\n");
odbcErrorLog += ("NativErr: " + e.Errors[i].NativeError + "\n");
odbcErrorLog += ("EMessage: " + e.Errors[i].Message + "\n");
odbcErrorLog += ("ESource: " + e.Errors[i].Source + "\n\n");
}
ErrorHandler.HandleError(ErrorType.Alert, odbcErrorLog);
return(null);
}
} catch (Exception e) {
ErrorHandler.HandleError(ErrorType.Alert, "Exception: " + e.Message);
return(null);
}
}
protected void btn_reg_Click(object sender, EventArgs e)
{
string selected = rdbtn_type.SelectedValue;
if (selected == "1")
{
grid_display1.Visible = false;
btn_excel1.Visible = false;
int desc = Convert.ToInt32(Drp_1.SelectedValue);
conn_asset.Open();
OdbcCommand cmda = conn_asset.CreateCommand();
cmda.CommandText = "select s.type, s.currentStock, s.make, s.model, m.ID, m.description from spare_master s inner join master_spare_entry m on m.ID = s.type where type = '" + Drp_1.SelectedValue + "' and currentStock != '0' ";
OdbcDataAdapter da = new OdbcDataAdapter();
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmda.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("currentStock", typeof(Int32)));
dt.Columns.Add(new System.Data.DataColumn("type", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("make", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("model", typeof(String)));
/// Problem with geting spare code and spre type ka description and mc guy ka name
while (dr.Read())
{
newRow = dt.NewRow();
newRow["currentStock"] = Convert.ToInt32(dr["currentStock"]);
newRow["type"] = Convert.ToString(dr["description"]);
newRow["make"] = Convert.ToString(dr["make"]);
newRow["model"] = Convert.ToString(dr["model"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_display.Visible = true;
grid_display.DataSource = dt;
grid_display.DataBind();
btn_excel.Visible = true;
}
conn_asset.Close();
}
else
{
grid_display.Visible = false;
btn_excel.Visible = false;
int desc = 1;
string type = "";
Int64 quantity = 0;
int j = 0;
int count = 0;
int flag = 0;
OdbcCommand cmd = conn_asset.CreateCommand();
conn_asset.Open();
cmd.CommandText = "select * from spare_master s inner join master_spare_entry m on m.ID = s.type order by type asc ";
OdbcDataReader cat = cmd.ExecuteReader();
while (cat.Read())
{
count++;
}
conn_asset.Close();
conn_asset.Open();
OdbcCommand cmda = conn_asset.CreateCommand();
cmda.CommandText = "select s.currentStock, s.type, m.description from spare_master s inner join master_spare_entry m on m.ID = s.type order by type desc";
OdbcDataAdapter da = new OdbcDataAdapter();
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmda.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("currentStock", typeof(Int32)));
dt.Columns.Add(new System.Data.DataColumn("type", typeof(String)));
/// Problem with geting spare code and spre type ka description and mc guy ka name
while (dr.Read())
{
if (j == 0)
{
desc = Convert.ToInt32(dr["type"]);
}
if (Convert.ToInt32(dr["type"]) == desc)
{
quantity = quantity + Convert.ToInt32(dr["currentStock"]);
type = Convert.ToString(dr["description"]);
flag = 1;
}
else
{
desc = Convert.ToInt32(dr["type"]);
newRow = dt.NewRow();
newRow["currentStock"] = quantity;
quantity = 0;
newRow["type"] = type;
flag = 0;
dt.Rows.Add(newRow);
}
j++;
if (j == count)
{
if (flag == 0)
{
quantity = quantity + Convert.ToInt32(dr["currentStock"]);
}
type = Convert.ToString(dr["description"]);
newRow = dt.NewRow();
newRow["currentStock"] = quantity;
newRow["type"] = type;
dt.Rows.Add(newRow);
}
}
if (dt.Rows.Count > 0)
{
grid_display1.Visible = true;
grid_display1.DataSource = dt;
grid_display1.DataBind();
btn_excel1.Visible = true;
}
conn_asset.Close();
}
}
public AdmPuMkDeployAccess()
{
da = new OdbcDataAdapter();
}
public void loadData()
{
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = DBConnSetting_.ConnectionStr;
//DateTime referenceDate = Settings.evaluationDate();
DateTime referenceDate = ReferenceDate_;
try
{
OdbcCommand sqlCommand = new OdbcCommand();
conn.Open();
// ------------------------------------------ Vol & Dividend --------------------------------------------
sqlCommand.CommandText = "select STD_DATE,STOCK_ID,OTHER_ID,CORR " +
"from otc_td_mrelsstockcorr " +
"where STD_DATE='" + referenceDate.ToString("yyyyMMdd") + "'";
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(sqlCommand.CommandText, conn);
DataTable tb = new DataTable();
dataAdapter.Fill(tb);
using (SqlCeConnection connection = new SqlCeConnection("Data Source=D:\\Project File\\OTCDerivativesCalculatorModule\\Project_CSharp\\RiskMonitor\\Data\\MarketDataBase.sdf"))
using (SqlCeCommand command = connection.CreateCommand())
{
//command.CommandText = "INSERT INTO CORRELATION (DATE, CODE_A, NAME_A, CODE_B, NAME_B, CORR, TYPE, VENDOR, DESCRIPTION) " +
// " VALUES (@pdate, @pcode_a, @pname_a ,@pcode_b, @pname_b, @pcorr, @ptype, @pvendor, @pdescription)" +
// " WHERE DATE not in ( select DATE from CORRELATION where DATE='" + ReferenceDate_.ToString("yyyyMMdd") + "')";
connection.Open();
SqlCeCommand delCommand = connection.CreateCommand();
delCommand.CommandText = "DELETE FROM CORRELATION WHERE DATE ='" + ReferenceDate_.ToString("yyyyMMdd") + "'";
delCommand.ExecuteNonQuery();
command.CommandText = "INSERT INTO CORRELATION (DATE, CODE_A, NAME_A, CODE_B, NAME_B, CORR, TYPE, VENDOR, DESCRIPTION) " +
" VALUES (@pdate, @pcode_a, @pname_a ,@pcode_b, @pname_b, @pcorr, @ptype, @pvendor, @pdescription)";
command.Parameters.Add("@pdate", SqlDbType.NVarChar);
command.Parameters.Add("@pcode_a", SqlDbType.NVarChar);
command.Parameters.Add("@pname_a", SqlDbType.NVarChar);
command.Parameters.Add("@pcode_b", SqlDbType.NVarChar);
command.Parameters.Add("@pname_b", SqlDbType.NVarChar);
command.Parameters.Add("@pcorr", SqlDbType.NVarChar);
command.Parameters.Add("@ptype", SqlDbType.NVarChar);
command.Parameters.Add("@pvendor", SqlDbType.NVarChar);
command.Parameters.Add("@pdescription", SqlDbType.NVarChar);
//command.ExecuteNonQuery();
foreach (DataRow item in tb.Rows)
{
string code_a = item["STOCK_ID"].ToString();
string code_b = item["OTHER_ID"].ToString();
if (code_a != code_b)
{
command.Parameters["@pdate"].Value = item["STD_DATE"].ToString();
command.Parameters["@pcode_a"].Value = code_a;
command.Parameters["@pname_a"].Value = "null";
command.Parameters["@pcode_b"].Value = code_b;
command.Parameters["@pname_b"].Value = "null";
command.Parameters["@pcorr"].Value = item["CORR"].ToString();
command.Parameters["@ptype"].Value = "History";
command.Parameters["@pvendor"].Value = "MRO";
command.Parameters["@pdescription"].Value = "This is test";
command.ExecuteNonQuery();
}
}
}
conn.Close();
}
catch (OdbcException e)
{
conn.Close();
OutputLogViewModel.addResult("DataBase Connection Error : MRO DB Load");
throw;
}
}
private void EditApplication(int appID)
{
OdbcCommand cmd = null;
OdbcConnection conn = null;
OdbcDataAdapter dataAdapter = null;
pnlApplication.Visible = true;
try
{
string sqlSelectApp = @"SELECT * from OnlineApplications where ApplicationID = ?";
string connString = ConfigurationManager.ConnectionStrings["HelloWorldConnect"].ConnectionString;
conn = new OdbcConnection(connString);
cmd = new OdbcCommand(sqlSelectApp, conn);
cmd.Parameters.Add(new OdbcParameter("appID", OdbcType.Int)
{
Value = appID
});
conn.Open();
dataAdapter = new OdbcDataAdapter(cmd);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
if (dataSet.Tables.Count == 0)
{
return;
}
DataTable dt = dataSet.Tables[0];
if (dt.Rows.Count == 0)
{
return;
}
txtFirstName.Text = dt.Rows[0]["FirstName"].ToString();
txtLastName.Text = dt.Rows[0]["LastName"].ToString();
txtEmail.Text = dt.Rows[0]["Email"].ToString();
txtPhone.Text = dt.Rows[0]["Phone"].ToString();
ddlIntendedMajor.SelectedValue = dt.Rows[0]["IntendedMajor"].ToString();
rbtnlResidentStatus.SelectedValue = dt.Rows[0]["ResidentStatus"].ToString();
txtEssay.Text = dt.Rows[0]["Essay"].ToString();
Session["ApplicationListEditAppID"] = appID;
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
lblMessage.Visible = true;
}
finally
{
dataAdapter.Dispose();
conn.Close();
}
}
// End GetAdapterForTable method.
/// <summary>
/// Gets the DataSet (in memory representation) for the table
/// for the specified adapter
/// </summary>
/// <param name="adapter">Adapter to be used for obtaining
/// the table</param>
/// <param name="tableName">Name of the table for which a
/// DataSet is required</param>
/// <returns>The DataSet with the filled in schema</returns>
private DataSet GetDataSetForTable(OdbcDataAdapter adapter, string tableName)
{
// Create a dataset object which will provide an in-memory
// representation of the data being worked upon in the
// data source.
var ds = new DataSet();
// Create a table named "Table" which will contain the same
// schema as in the data source.
adapter.Fill(ds, tableName);
ds.Locale = CultureInfo.InvariantCulture;
return ds;
}
// End RemoveTable method.
/// <summary>
/// Obtain a data adapter for the specified Table
/// </summary>
/// <param name="tableName">Name of the table to obtain the
/// adapter for</param>
/// <returns>Adapter object for the specified table</returns>
/// <remarks>An adapter serves as a bridge between a DataSet (in memory
/// representation of table) and the data source</remarks>
private OdbcDataAdapter GetAdapterForTable(string tableName)
{
OdbcDataAdapter da = null;
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null || !TableNameIsValid(tableName) || !TableIsPresent(tableName))
{
return null;
}
OdbcConnection connection = di.Connection;
try
{
// Create an ODBC data adapter. This can be used to update the
// data source with the records that will be created here
// using data sets.
string sql = "Select * from " + tableName;
da = new OdbcDataAdapter(new OdbcCommand(sql, connection));
// Create an ODBC command builder object. This will create sql
// commands automatically for a single table, thus
// eliminating the need to create new sql statements for
// every operation to be done.
var cmd = new OdbcCommandBuilder(da);
// Set the delete command for the table here.
sql = "Delete from " + tableName + " where ID = ?";
da.DeleteCommand = new OdbcCommand(sql, connection);
// Specify a DeleteCommand parameter based on the "ID"
// column.
da.DeleteCommand.Parameters.Add(new OdbcParameter());
da.DeleteCommand.Parameters[0].SourceColumn = "ID";
// Create an InsertCommand based on the sql string
// Insert into "tablename" values (?,?,?)" where
// ? represents a column in the table. Note that
// the number of ? will be equal to the number of
// columns.
var ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
ds.Locale = CultureInfo.InvariantCulture;
sql = "Insert into " + tableName + " values ( ";
for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
{
sql += "?, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")";
da.InsertCommand = new OdbcCommand(sql, connection);
// Create parameters for the InsertCommand based on the
// captions of each column.
for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
{
da.InsertCommand.Parameters.Add(new OdbcParameter());
da.InsertCommand.Parameters[i].SourceColumn =
ds.Tables["Table"].Columns[i].Caption;
}
// Open the connection if it is not already open.
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
catch (Exception e)
{
WriteError(new ErrorRecord(
e,
"CannotAccessSpecifiedTable",
ErrorCategory.InvalidOperation,
tableName));
}
return da;
}
public List <SlideModel> GetData(List <int> T_ID)
{
try
{
//SlideDataModel slideDataModel = new SlideDataModel();
List <DataTable> dtList = new List <DataTable>();
// List<SlideResponse> slideResponse = new List<SlideResponse>();
List <VotingResponseModel> votingResponse = new List <VotingResponseModel>();
//List<AnswerModel> slidesOptionList = new List<AnswerModel>();
var SlideOptions = new Dictionary <string, string>();
OdbcConnection conn = getOdbcConnection();
OdbcDataAdapter adapter;
DataTable table;
OdbcCommand cmd;
cmd = new OdbcCommand();
// cmd.CommandText = "SELECT * FROM ST_Response2 WHERE T_ID in (" + "'" + String.Join("','", T_ID) + "'" + ")";
cmd.CommandText = "SELECT T_ID As Slide , TP_Value AS Questions From ST_TopicPar WHERE TP_Name = 'OptionCount' AND t_id in (" + "'" + String.Join("','", T_ID) + "'" + ")";
cmd.Connection = conn;
adapter = new OdbcDataAdapter(cmd);
table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
//AnswerModel entity = new AnswerModel();
//entity.SlideOptions = new Dictionary<string, string>();
//entity.SlideOptions.Add(Convert.ToString(table.Rows[i][0]), Convert.ToString(table.Rows[i][1]));
SlideOptions.Add(Convert.ToString(table.Rows[i][0]), Convert.ToString(table.Rows[i][1]));
}
}
//slideDataModel.SlideOptionList = slidesOptionList;
cmd = new OdbcCommand();
cmd.Connection = conn;
cmd.CommandText = $@"SELECT A.v_id AS V_ID, Max (A.r_id) AS UnicoID,
B.r_result AS Answer, a.t_ID AS Slide
FROM ST_Response AS A LEFT JOIN ST_Response AS B
ON (A.r_id = B.r_id) AND (A.v_id = B.v_id)
WHERE A.t_id In (" + "'" + string.Join("','", T_ID) + "'" + ")" +
@"GROUP BY A.v_id, b.r_result, a.t_id
ORDER BY a.t_id";
adapter = new OdbcDataAdapter(cmd);
table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
VotingResponseModel entity = new VotingResponseModel();
entity.V_ID = Convert.ToInt32(table.Rows[i][0]);
entity.UnicoID = Convert.ToInt32(table.Rows[i][1]);
entity.Answer = Convert.ToString(table.Rows[i][2]);
entity.Slide = Convert.ToString(table.Rows[i][3]);
votingResponse.Add(entity);
}
}
//slideDataModel.VotingResponseList = votingResponse;
var slideResults = votingResponse.GroupBy(n => new { n.Slide })
.Select(g => new { Slide = g.Key, Votes = g });
List <SlideModel> list = new List <SlideModel>();
foreach (var result in slideResults)
{
var optionResult = result.Votes.GroupBy(n => new { n.Answer })
.Select(g => new Options {
OptionNumber = Convert.ToInt32(g.Key.Answer.Replace(",", "")), OptionPercentage = g.Count() * 100 / result.Votes.Count()
});
list.Add(new SlideModel
{
SlideNumber =
result.Slide.Slide,
Options = optionResult.Select(x =>
new Options
{
OptionNumber = x.OptionNumber,
OptionPercentage = x.OptionPercentage
}).ToList()
});
}
foreach (var slide in list)
{
int length = Convert.ToInt32(SlideOptions[slide.SlideNumber]);
for (int i = 1; i <= length; i++)
{
if (!slide.Options.Any(x => x.OptionNumber == i))
{
slide.Options.Add(new Options {
OptionNumber = i, OptionPercentage = 0
});
}
}
slide.Options = slide.Options.OrderBy(x => x.OptionNumber).ToList();
}
return(list);
}
catch (Exception ex)
{
return(new List <SlideModel>());
}
}
private static string GetBBInfo(ODBCHelper bb, string Ssbz, string Debug, int Flag, string sftqbb)
{
OdbcCommand cmd = bb.GetStoredProcCommond("{call pGETPatientInfoForPathNet(?,?,?,?,?)}");
bb.AddInParameter(cmd, "@In_Flag", DbType.Int16, Flag);
if (Flag == 1)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, "");
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.DateTime, Ssbz.Substring(0, 10));
int mzls;
try
{
mzls = int.Parse(Ssbz.Substring(11));
}
catch
{
MessageBox.Show("请检查输入的门诊号格式是否正确！");
return("0");
}
bb.AddInParameter(cmd, "@In_SeqNo", DbType.Int32, mzls);
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, 0);
}
else if (Flag == 2)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, Ssbz);
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_SeqNo", DbType.String, "");
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, 0);
}
else if (Flag == 4)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, "");
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_SeqNo", DbType.String, "");
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, Ssbz);
}
OdbcDataAdapter sqlda = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
sqlda.Fill(ds);
if (ds.Tables == null || ds.Tables.Count < 1)
{
return("0");
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count < 1)
{
return("0");
}
LogeneXmlHelper lxp = new LogeneXmlHelper();
lxp.F_ZYH = dt.Rows[0]["IPSeqNOText"] == null ? "" : dt.Rows[0]["IPSeqNOText"].ToString().Trim();
lxp.F_BRBH = dt.Rows[0]["patientid"] == null ? "" : dt.Rows[0]["patientid"].ToString().Trim();
//empiid存职业字段
lxp.F_ZY = GetEMPIID(lxp.F_BRBH);
lxp.F_CH = dt.Rows[0]["sickbedno"] == null ? "" : dt.Rows[0]["sickbedno"].ToString().Trim();
lxp.F_XM = dt.Rows[0]["patientname"] == null ? "" : dt.Rows[0]["patientname"].ToString().Trim();
string birthday = dt.Rows[0]["patientbirthday"] == null ? "" : dt.Rows[0]["patientbirthday"].ToString().Trim();
if (birthday != "")
{
try
{
DateTime birth = DateTime.Parse(birthday);
int nl = CalculateAgeCorrect(birth, DateTime.Now);
lxp.F_NL = nl.ToString() + "岁";
}
catch
{
lxp.F_NL = "";
}
}
lxp.F_XB = dt.Rows[0]["patientsex"] == null ? "" : dt.Rows[0]["patientsex"].ToString().Trim();
lxp.F_HY = dt.Rows[0]["marriage"] == null ? "" : dt.Rows[0]["marriage"].ToString().Trim();
lxp.F_SJKS = dt.Rows[0]["departmentname"] == null ? "" : dt.Rows[0]["departmentname"].ToString().Trim();
lxp.F_ADDRESS = dt.Rows[0]["address"] == null ? "" : dt.Rows[0]["address"].ToString().Trim();
lxp.F_TELEPHONE = dt.Rows[0]["phone"] == null ? "" : dt.Rows[0]["phone"].ToString().Trim();
switch (Flag)
{
case 1:
lxp.F_BRLB = "门诊";
break;
case 2:
lxp.F_BRLB = "住院";
break;
default:
lxp.F_BRLB = dt.Rows[0]["PatientTypeListName"] == null ? "" : dt.Rows[0]["PatientTypeListName"].ToString().Trim();
break;
}
if (sftqbb == "1")
{
if (Debug == "1")
{
MessageBox.Show("取标本列表");
}
lxp.BBLBLIST = TQBBLB(bb, Ssbz);
if (lxp.BBLBLIST == null)
{
MessageBox.Show("未查询到标本信息");
}
else
{
if (Debug == "1")
{
MessageBox.Show("返回XML：" + lxp.ReturnBBLBXML());
}
return(lxp.ReturnBBLBXML());
}
}
return(lxp.ReturnLogeneXML());
}
/// <summary>
/// ODBCRecordsetClass Constructor: initialize internal variables,
/// the connection is initialized using the connection string, and
/// the query uses the sqlSelectString.
/// </summary>
/// <param name="connString"></param>
/// <param name="sqlSelectString"></param>
public ODBCRecordsetClass(String connString, String sqlSelectString)
{
this.dbConnection = new OdbcConnection(connString);
this.SqlQuery = sqlSelectString;
this.operationFinished = false;
this.dbAdapter = CreateAdapter(SqlQuery, dbConnection);
this.dbDataSet = new DataSet();
this.dbTable = new DataTable();
this.dbTable = dbDataSet.Tables.Add("Table");
this.dbAdapter.Fill(this.dbTable);
CheckUpdateCommandsFromMetaData();
this.operationFinished = true;
this.newRow = false;
this.foundRecordsCounter = 0;
this.OnAfterQuery();
classFields = new FieldsClass(this);
SetInitialPosition();
}
private static string GetBBSQInfo(string Ssbz, string Debug, int Flag, string sftqbb)
{
string constring1 = f.ReadString("GDSY", "hisstring", "DSN=pathnethisgdsy;UID=pathnetuser;PWD=userpathnet;");
ODBCHelper bb = new ODBCHelper(constring1);
if (Flag == 4)
{
int a = 0;
if (int.TryParse(Ssbz, out a) == false)
{
Ssbz = Ssbz.Substring(4);
}
}
OdbcCommand cmd = bb.GetStoredProcCommond("{call pGetFunctionRequestForPathNet(?,?,?,?,?,?,?,?)}");
bb.AddInParameter(cmd, "@In_Flag", DbType.Int16, Flag);
bb.AddInParameter(cmd, "@In_StartDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_EndDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_RequestDepartmentNos", DbType.String, "");
if (Flag == 1)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, "");
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.DateTime, Ssbz.Substring(0, 10));
int mzls;
try
{
mzls = int.Parse(Ssbz.Substring(11));
}
catch
{
MessageBox.Show("请检查输入的门诊号格式是否正确！");
return("0");
}
bb.AddInParameter(cmd, "@In_SeqNo", DbType.Int32, mzls);
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, 0);
}
else if (Flag == 2)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, Ssbz);
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_SeqNo", DbType.Int32, 0);
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, 0);
}
else if (Flag == 4)
{
bb.AddInParameter(cmd, "@In_IPSeqNoText", DbType.String, "");
bb.AddInParameter(cmd, "@In_RegisterDate", DbType.String, "");
bb.AddInParameter(cmd, "@In_SeqNo", DbType.String, "");
bb.AddInParameter(cmd, "@In_FunctionSampleLabelID", DbType.Int32, Ssbz);
}
else
{
GDSYSQForm sqform = new GDSYSQForm();
if (sqform.DialogResult == DialogResult.OK)
{
return(sqform.rexml);
}
}
OdbcDataAdapter sqlda = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
sqlda.Fill(ds);
if (ds.Tables == null || ds.Tables.Count < 1)
{
return(GetBBInfo(bb, Ssbz, Debug, Flag, sftqbb));
}
DataTable dt = ds.Tables[0];
if (Debug == "1")
{
MessageBox.Show("查询到数据条数：" + dt.Rows.Count.ToString());
}
if (dt == null || dt.Rows.Count < 1)
{
return(GetBBInfo(bb, Ssbz, Debug, Flag, sftqbb));
}
GDSYUNIForm uniform = new GDSYUNIForm();
uniform.indt = dt;
if (uniform.ShowDialog() == DialogResult.OK)
{
DataRow outdr = uniform.outdr;
if (outdr == null)
{
return(GetBBInfo(bb, Ssbz, Debug, Flag, sftqbb));
}
//DataRow outdr = dt.Rows[0];
LogeneXmlHelper lxp = new LogeneXmlHelper();
lxp.F_ZYH = outdr["IPSeqNoText"] == null ? "" : outdr["IPSeqNoText"].ToString().Trim();
lxp.F_BRBH = outdr["PatientID"] == null ? "" : outdr["PatientID"].ToString().Trim();
//empiid存职业字段
lxp.F_ZY = GetEMPIID(lxp.F_BRBH);
lxp.F_CH = outdr["SickBedNo"] == null ? "" : outdr["SickBedNo"].ToString().Trim();
lxp.F_XM = outdr["PatientName"] == null ? "" : outdr["PatientName"].ToString().Trim();
lxp.F_XB = outdr["PatientSex"] == null ? "" : outdr["PatientSex"].ToString().Trim();
//string birth = outdr["PatientBirthDay"] == null ? "" : outdr["PatientBirthDay"].ToString().Trim();
//lxp.F_NL = CalculateAgeCorrect(DateTime.Parse(birth), DateTime.Now).ToString(); ;
lxp.F_NL = outdr["PatientAge"] == null ? "" : outdr["PatientAge"].ToString().Trim();
lxp.F_MZH = outdr["SeqNo"] == null ? "" : (outdr["SeqNo"].ToString().Trim() == "-1" ? " " : outdr["SeqNo"].ToString().Trim());
lxp.F_SJKS = outdr["RequestDepartmentName"] == null ? "" : outdr["RequestDepartmentName"].ToString().Trim();
lxp.F_SJYS = outdr["RequestEmployeeName"] == null ? "" : outdr["RequestEmployeeName"].ToString().Trim();
lxp.F_JZID = outdr["InPatientID"] == null ? "" : outdr["InPatientID"].ToString().Trim();
lxp.F_SQXH = outdr["FunctionRequestID"] == null ? "" : outdr["FunctionRequestID"].ToString().Trim();
lxp.F_YZXM = outdr["ItemName"] == null ? "" : outdr["ItemName"].ToString().Trim();
lxp.F_HY = outdr["Marriage"] == null ? "" : outdr["Marriage"].ToString();
lxp.F_ADDRESS = outdr["Address"] == null ? "" : outdr["Address"].ToString();
lxp.F_TELEPHONE = outdr["Phone"] == null ? "" : outdr["Phone"].ToString();
lxp.F_SFZH = outdr["IdentityCardNo"] == null ? "" : outdr["IdentityCardNo"].ToString();
lxp.F_MZ = outdr["RaceDesc"] == null ? "" : outdr["RaceDesc"].ToString();
// lxp.F_ZY = outdr["ProfessionDesc"] == null ? "" : outdr["ProfessionDesc"].ToString();
lxp.F_LCZD = outdr["diseaseName"] == null ? "" : outdr["diseaseName"].ToString();
lxp.F_BQ = outdr["IPDepartmentName"] == null ? "" : outdr["IPDepartmentName"].ToString();
if (Flag == 1)
{
lxp.F_BRLB = "门诊";
}
else if (Flag == 2)
{
lxp.F_BRLB = "住院";
}
if (sftqbb == "1")
{
lxp.BBLBLIST = TQBBLB(bb, Ssbz);
if (Debug == "1")
{
MessageBox.Show("标本数量：" + lxp.BBLBLIST.Count.ToString());
}
if (lxp.BBLBLIST == null)
{
MessageBox.Show("未查询到标本信息");
}
else
{
if (Debug == "1")
{
MessageBox.Show("返回XML：" + lxp.ReturnBBLBXML());
}
return(lxp.ReturnBBLBXML());
}
}
if (Debug == "1")
{
MessageBox.Show("返回XML：" + lxp.ReturnLogeneXML());
}
return(lxp.ReturnLogeneXML());
}
//GDSYMutiForm mutiform = new GDSYMutiForm();
//mutiform.indt = dt;
//if (mutiform.ShowDialog() == DialogResult.OK)
//{
// return mutiform.rexml;
//}
return(GetBBInfo(bb, Ssbz, Debug, Flag, sftqbb));
}
public OdbcDataAdapter llenarBalance(string idBalance)
{
string query = "SELECT 'ACTIVO CORRIENTE', '','','' " +
"UNION ALL " +
"SELECT cuenta_contable, saldo,'','' FROM balance_general_detalles WHERE id_balance_general =" + idBalance + " AND '1.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'TOTAL ACTIVO CORRIENTE','', ROUND(SUM(saldo),2),'' FROM balance_general_detalles WHERE id_balance_general =" + idBalance + " AND '1.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'ACTIVO NO CORRIENTE', '','','' " +
"UNION ALL " +
"SELECT cuenta_contable, saldo,'','' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '1.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'TOTAL ACTIVO NO CORRIENTE','', ROUND(SUM(saldo),2),'' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '1.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable ) " +
"UNION ALL " +
"SELECT 'TOTAL ACTIVO','','', ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('1.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '1.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)) " +
"UNION ALL " +
"SELECT 'PASIVO CORRIENTE', '','','' " +
"UNION ALL " +
"SELECT cuenta_contable, saldo,'','' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'TOTAL PASIVO CORRIENTE','', ROUND(SUM(saldo),2),'' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable ) " +
"UNION ALL " +
"SELECT 'PASIVO NO CORRIENTE', '','','' " +
"UNION ALL " +
"SELECT cuenta_contable, saldo,'','' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'TOTAL PASIVO NO CORRIENTE','', ROUND(SUM(saldo),2),'' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable ) " +
"UNION ALL " +
"SELECT 'TOTAL PASIVO','','', ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)) " +
"UNION ALL " + // CAPITAL
"SELECT cuenta_contable,'', saldo,'' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'GANANCIA DEL EJECICIO','', " +
"ROUND((" +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('1.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '1.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable))) " +
"- " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable))) " +
"- " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)) " +
"),2) " +
",'' FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) " +
"UNION ALL " +
"SELECT 'PASIVO + NUEVO CAPITAL','','', " +
"ROUND((( " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('1.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '1.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable))) " +
"- " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable))) " +
"- " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)) " +
") " +
"+ " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND('2.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable) OR '2.2' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable))) " +
"+ " +
"(SELECT ROUND(SUM(saldo),2) FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)) " +
"),2 ) " +
"FROM balance_general_detalles WHERE id_balance_general = " + idBalance + " AND '3.1' = (SELECT id_tipo_cuenta from cuentas WHERE nombre = cuenta_contable)";
OdbcDataAdapter dataTable = Mayor.LlenarTablaMayor(query);
return(dataTable);
}
protected void btnCargar_Click(object sender, EventArgs e)
{
try
{
HttpFileCollection uploads = HttpContext.Current.Request.Files;
for (int i = 0; i < uploads.Count; i++)
{
HttpPostedFile upload = uploads[i];
if (upload.ContentLength == 0)
{
continue;
}
string c = System.IO.Path.GetFileName(upload.FileName);
try
{
string ruta = Server.MapPath("context/Archivos/") + c;
upload.SaveAs(ruta);
}
catch (Exception Exp)
{
throw (Exp);
}
}
if (FileUpload1.PostedFile != null)
{
HttpPostedFile attFile = FileUpload1.PostedFile;
int attachFileLength = attFile.ContentLength;
if (attachFileLength > 0)
{
if (FileUpload1.PostedFile.ContentLength > 0)
{
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string inFileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string pathDataSource = Server.MapPath("context/Archivos/") + inFileName;
//string conStr = "";
if (Extension == ".xls" || Extension == ".xlsx" || Extension == ".xlsm")
{
string CadenaConexion = @"driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
driverid=1046;dbq=" + pathDataSource;
DataTable ds = null;
try
{
using (OdbcConnection conn = new OdbcConnection(CadenaConexion))
{
conn.Open();
using (OdbcCommand command = conn.CreateCommand())
{
//consulta para mostrar en hoja de excel
command.CommandText = "SELECT * FROM [CargaDatos$]";
OdbcDataAdapter ad = new OdbcDataAdapter(command);
ds = new DataTable();
ad.Fill(ds);
conn.Close();
int i = 0;
foreach (DataRow fila in ds.Rows)
{
i++;
if (fila[0].ToString().Trim() == "0" || fila[0].ToString().Trim() == "")
{
fila.Delete();
}
}
ViewState["dataSource"] = ds;
ViewState["plantillaCargada"] = "true";
gridExcel.DataSource = ds;
gridExcel.DataBind();
if (ViewState["plantillaCargada"].ToString() == "true")
{
btnGrabar.Enabled = false;
btnGrabar.CssClass = "btn btn-danger";
lblMensaje.Text = "Esta plantilla ya ha sido cargada en la base de datos";
divMensaje.Attributes.Add("class", "alert alert-success col-md-8");
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
else
{
//Show your error in any error controls
}
}
else
{
FileUpload1.Focus();
gridExcel.DataSource = null;
gridExcel.DataBind();
}
}
}
}
catch (Exception ex)
{
//throw ex;
lblMensaje.Text = ex.Message;
}
//la conexion donde se encuentra el archivo para ser mostrado en el gridview
}
public string CrearLibroMayor(string idLibroBalances, string idBalanceGeneral)
{
int noDetalle = 0;
float activo = 0;
float pasivo = 0;
float patrimonio = 0;
string sql = "";
int i = 0;
OdbcDataAdapter ODBC_AC = Mayor.ActivoC(idLibroBalances); //AC
DataTable ac = new DataTable(); // Tabla AC
ODBC_AC.Fill(ac);
OdbcDataAdapter ODBC_ANC = Mayor.ActivoNC(idLibroBalances); //ANC
DataTable anc = new DataTable(); // Tabla ANC
ODBC_ANC.Fill(anc);
OdbcDataAdapter ODBC_PC = Mayor.PasivoC(idLibroBalances); //PC
DataTable pc = new DataTable(); // Tabla PC
ODBC_PC.Fill(pc);
OdbcDataAdapter ODBC_PNC = Mayor.PasivoNC(idLibroBalances); //PNC
DataTable pnc = new DataTable(); // Tabla PNC
ODBC_PNC.Fill(pnc);
OdbcDataAdapter ODBC_PAT = Mayor.Patrimonio(idLibroBalances); //Patrimonio
DataTable pat = new DataTable(); // Tabla Patrimonio
ODBC_PAT.Fill(pat);
//APARICION
// DEBE
foreach (DataRow cuenta in ac.Rows)
{
noDetalle++;
activo += Convert.ToInt32(cuenta[1]);
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, cuenta[0].ToString(), cuenta[1].ToString());
}
progreso = 25;
foreach (DataRow cuenta in anc.Rows)
{
noDetalle++;
activo += Convert.ToInt32(cuenta[1]);
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, cuenta[0].ToString(), cuenta[1].ToString());
}
progreso = 50;
foreach (DataRow cuenta in pc.Rows)
{
noDetalle++;
pasivo += Convert.ToInt32(cuenta[1]);
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, cuenta[0].ToString(), cuenta[1].ToString());
}
progreso = 75;
foreach (DataRow cuenta in pnc.Rows)
{
noDetalle++;
pasivo += Convert.ToInt32(cuenta[1]);
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, cuenta[0].ToString(), cuenta[1].ToString());
}
foreach (DataRow cuenta in pat.Rows)
{
noDetalle++;
patrimonio += Convert.ToInt32(cuenta[1]);
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, cuenta[0].ToString(), cuenta[1].ToString());
}
noDetalle++;
InsertarDetalleBalanceGeneral(noDetalle.ToString(), idBalanceGeneral, "Ganacia del Ejercicio", (activo - pasivo - patrimonio).ToString());
progreso = 100;
return("Balances Generados Correctamente");
}
public static DataTable searchCustomers(string namephrase)
{
_log.Debug("searchCustomers >> " + namephrase);
List <CashRegCust> Results = new List <CashRegCust>();
clsBaseUtility locObjUtil = new clsBaseUtility();
clsSqlFactory hSql = new clsSqlFactory();
DataTable retDataTable = new DataTable();
bool bRet = true;
try
{
string searchString = namephrase;
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
searchString = regex.Replace(searchString, @" ");
string[] words = searchString.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
int i = 0;
string strSql = "";
string strOrder = "";
string strCUST = locObjUtil.getTable("CUST");
string strFTVIEW_CUST = (strCUST == "CUST")?"FTVIEW_CUST":locObjUtil.getTable("FTVIEW_CUST");
strSql = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " isnull(a.CUSTNO,'') as CUSTNO,isnull(a.LNAME,'') as LNAME,isnull(a.FNAME,'') as FNAME,isnull(a.SPINNR,'') as SPINNR, " +
" isnull(replace(a.WTEL,'/',''),'') +'/'+ isnull(replace(a.HTEL,'/',''),'') +'/'+ isnull(replace(a.Tel3,'/',''),'') as PHONES, " +
" isnull(a.EMAIL,'') as EMAIL, " +
" isnull(a.PO,'')+' '+ isnull(a.POSTCD,'')+' '+isnull(a.ADDR2,'')+' '+isnull(a.ADDR2E,'')+' '+isnull(a.ADDR1,'') as ADDRESS " +
" from " + strCUST + " a ";
while (i < words.Length)
{
if (words[i] != "")
{
strSql += " inner join containstable(" + strFTVIEW_CUST + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a._OID = T" + i.ToString() + ".[KEY] ";
if (i > 0)
{
strOrder += "+";
}
strOrder += "T" + i.ToString() + ".RANK";
}
i++;
}
strSql = strSql + " where a.CUSTNO >0 ";
if (strOrder != "")
{
strOrder = " order by " + strOrder + " desc ";
}
if (words.Length > 0)
{
strSql += strOrder;
}
_log.Debug(strSql);
bRet = hSql.NewCommand(strSql);
using (OdbcConnection connection =
new OdbcConnection(new clsGlobalVariable().getConnectionString()))
{
OdbcDataAdapter adapter =
new OdbcDataAdapter(strSql, connection);
// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(retDataTable);
}
catch (Exception ex)
{
_log.Error(ex.ToString());
throw ex;
}
}
}
catch (Exception ex)
{
_log.Error(ex.ToString());
throw ex;
}
finally
{
hSql.Close();
}
_log.Debug("searchCustomers <<");
return(retDataTable);
}
/// <summary>
/// Gets the data.
/// </summary>
private DataTable GetData()
{
Membre member = (Membre)Session["Membre"];
string critere = "";
if (Session["mesLocations_validePropMy"] == "1")
{
critere += " AND valider_proposition = true";
}
else if (Session["mesLocations_validePropMy"] == "0")
{
critere += " AND valider_proposition = false";
}
if (Session["mesLocations_valideLocation"] == "1")
{
critere += " AND valider_signature = true";
}
if (Session["mesLocations_valideLocation"] == "0")
{
critere += " AND valider_signature = false";
}
if (FuAsp_acq.SelectedValue != "-1")
{
critere += " AND Acquereurs.id_acq = " + FuAsp_acq.SelectedValue;
}
if (Session["mesLocations_dateSignBailMin"] != null && Session["mesLocations_dateSignBailMin"].ToString() != "")
{
string[] date = Session["mesLocations_dateSignBailMin"].ToString().Split('/');
critere += " AND date_signature_bail < #" + date[1] + "/" + date[0] + "/" + date[2] + "#";
}
if (Session["mesLocations_dateSignBailMax"] != null && Session["mesLocations_dateSignBailMax"].ToString() != "")
{
string[] date = Session["mesLocations_dateSignBailMax"].ToString().Split('/');
critere += " AND date_signature_bail > #" + date[1] + "/" + date[0] + "/" + date[2] + "#";
}
//if (Session["mesVentes_dateSignatureMin"] != null && Session["mesVentes_dateSignatureMin"].ToString() != "")
//{
//string[] date = Session["mesVentes_dateSignatureMin"].ToString().Split('/');
//critere += " AND date_signature < #" + date[1] + "/" + date[0] + "/" + date[2] + "#";
//}
//if (Session["mesVentes_dateSignatureMax"] != null && Session["mesVentes_dateSignatureMax"].ToString() != "")
//{
//string[] date = Session["mesVentes_dateSignatureMax"].ToString().Split('/');
//critere += " AND date_signature > #" + date[1] + "/" + date[0] + "/" + date[2] + "#";
//}
DataTable table = new DataTable();
string sql = "SELECT Locations.*,nom_client,prenom_client,nom,prenom,[nom vendeur],[prenom vendeur] FROM Biens,Locations,Clients,Acquereurs WHERE Locations.ref_bien = Biens.ref AND Locations.id_nego = Clients.idclient AND id_acq = id_acquereur"
+ " AND (Locations.id_nego =" + member.IDCLIENT + " OR Acquereurs.idclient =" + member.IDCLIENT + ")"
+ critere
+ " ORDER BY ID DESC";
// get the connection
using (OdbcConnection cI = new OdbcConnection(_ConnectionString))
{
// write the sql statement to execute
//string sql = recherche.ToString();
// instantiate the command object to fire
using (OdbcCommand cmd = new OdbcCommand(sql, cI))
{
// get the adapter object and attach the command object to it
using (OdbcDataAdapter ad = new OdbcDataAdapter(cmd))
{
// fire Fill method to fetch the data and fill into DataTable
ad.Fill(table);
}
}
}
if (table.Rows.Count == 0)
{
gtfo.Visible = true;
}
return(table);
}
public static void GetDataFromHive()
{
var conn = new OdbcConnection
{
ConnectionString = @"DRIVER={Microsoft Hive ODBC Driver};
Host=10.0.0.4;
Port=10000;
Schema=default;
DefaultTable=table_name;
HiveServerType=1;
ApplySSPWithQueries=1;
AsyncExecPollInterval=100;
AuthMech=0;
CAIssuedCertNamesMismatch=0;
TrustedCerts=C:\Program Files\Microsoft Hive ODBC Driver\lib\cacerts.pem;"
};
try
{
conn.Open();
var adp = new OdbcDataAdapter("Select * from customers limit 10", conn);
var ds = new DataSet();
adp.Fill(ds);
foreach (var table in ds.Tables)
{
var dataTable = table as DataTable;
if (dataTable == null)
{
continue;
}
var dataRows = dataTable.Rows;
if (dataRows == null)
{
continue;
}
//log.Info("Records found " + dataTable.Rows.Count);
foreach (var row in dataRows)
{
var dataRow = row as DataRow;
if (dataRow == null)
{
continue;
}
//log.Info(dataRow[0].ToString() + " " + dataRow[1].ToString());
}
}
}
catch (Exception ex)
{
// log.Info("Failed to connect to data source");
}
finally
{
conn.Close();
}
}
public DataSet GetFormDataCP204(string strPage)
{
string strQuery = "";
OdbcCommand cmdOdbc = new OdbcCommand();
OdbcDataAdapter daOdbc;
DataTable dtTemp = new DataTable();
DataSet dsData = new DataSet();
try
{
if (connOdbc.State == ConnectionState.Closed)
{
connOdbc.Open();
}
//switch (strPage)
//{
// case "CP2042009Page1":
strQuery = "select bcp_correspond_add1, bcp_correspond_add2, bcp_correspond_add3, bcp_correspond_post, bcp_correspond_city, bcp_correspond_state, bcp_curr_corr_add1, bcp_curr_corr_add2, bcp_curr_corr_add3, bcp_curr_corr_post, bcp_curr_corr_city, bcp_curr_corr_state, bcp_estimated_tax, format(bcp_newco_date,'dd/mm/yyyy'), bcp_indicate, format(bcp_acc_period_fr,'dd/mm/yyyy'), format(bcp_acc_period_to,'dd/mm/yyyy'), format(bcp_basis_period_fr,'dd/mm/yyyy'), format(bcp_basis_period_to,'dd/mm/yyyy'), format(bcp_newco_bas_fr,'dd/mm/yyyy'), format(bcp_newco_bas_to,'dd/mm/yyyy'), format(bcp_newco_bas_sub_fr,'dd/mm/yyyy'), format(bcp_newco_bas_sub_to,'dd/mm/yyyy'), format(bcp_sme_period_fr,'yyyy'), format(bcp_sme_period_to,'yyyy') from borang_cp204 where bcp_ref_no=? and bcp_ya=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_BORANG_CP204");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select ta_add_line1, ta_add_line2, ta_add_line3, ta_add_postcode, ta_add_city, ta_add_state, ta_tel_no, ta_roc_no, ta_email from taxa_profile where ta_co_name=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxagent", strTaxAgent.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_FIRM");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select left(format(bcp_acc_period_fr,'dd/mm/yyyy'),2), mid(format(bcp_acc_period_fr,'dd/mm/yyyy'), 4, 2), right(format(bcp_acc_period_fr,'dd/mm/yyyy'),4), left(format(bcp_acc_period_to,'dd/mm/yyyy'),2), mid(format(bcp_acc_period_to,'dd/mm/yyyy'), 4, 2), right(format(bcp_acc_period_to,'dd/mm/yyyy'),4) from borang_cp204 where bcp_ref_no=? and bcp_ya=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_ACC_PERIOD");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "select left(format(bcp_basis_period_fr,'dd/mm/yyyy'),2), mid(format(bcp_basis_period_fr,'dd/mm/yyyy'), 4, 2), right(format(bcp_basis_period_fr,'dd/mm/yyyy'),4), left(format(bcp_basis_period_to,'dd/mm/yyyy'),2), mid(format(bcp_basis_period_to,'dd/mm/yyyy'), 4, 2), right(format(bcp_basis_period_to,'dd/mm/yyyy'),4) from borang_cp204 where bcp_ref_no=? and bcp_ya=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_BASIS_PERIOD");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
// break;
//}
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.ToString()); }
return(dsData);
}
protected object EjecutarODBC(object Sql, TipoRetorno TipoRetorno, Transaccion Transaccion, string CnnStrDB, string nombreODBC)
{
switch (Sql.GetType().FullName.ToString())
{
case "System.String[]":
sqls = (string[])Sql;
break;
default: //case "System.String":
sqls = new String[] { (string)Sql };
break;
}
cantReg = new int[sqls.Length];
switch (Transaccion)
{
case (Transaccion.Acepta):
usaTransaccion = (sqls.Length > 1);
break;
case (Transaccion.NoAcepta):
usaTransaccion = false;
break;
default: //(Transaccion.Usa):
usaTransaccion = true;
break;
}
if (usaTransaccion)
{
ODBCTransaccion = ODBCConexion.BeginTransaction();
}
try
{
ODBCConexion = new OdbcConnection("dsn=" + nombreODBC + ";uid=" + sesion.Usuario.Id + ";pwd=" + sesion.Usuario.Password + ";");
ODBCConexion.Open();
ODBCComando = new OdbcCommand(Sql.ToString(), ODBCConexion);
ds = new DataSet();
for (i = 0; i < sqls.Length; i++)
{
System.Diagnostics.Debug.WriteLine(sqls[i]);
ODBCAdapter = new OdbcDataAdapter(sqls[i], ODBCConexion);
if (usaTransaccion)
{
ODBCAdapter.SelectCommand.Transaction = ODBCTransaccion;
}
ODBCAdapter.SelectCommand.CommandTimeout = 90;
if (i == 0)
{
ODBCAdapter.Fill(ds);
}
else
{
ds.Tables.Add();
ODBCAdapter.Fill(ds.Tables[ds.Tables.Count - 1]);
}
}
return(ds);
}
catch (Exception ex)
{
throw new CedServicios.EX.db.Conexion(ex);
}
}
/// <summary>
/// SQL command with return type scalar.
/// </summary>
/// <param name="sqlScalar">The SQL scalar.</param>
/// <returns>
/// True if successful, otherwise false.
/// </returns>
public object Scalar(string sqlScalar)
{
int retryCount = 0;
switch (this.ConnectionType)
{
case ConnType.MSSQL:
{
while (true)
{
try
{
using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString))
{
con.Open();
var adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sqlScalar, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
return(adapter.SelectCommand.ExecuteScalar());
}
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
else
{
this.WriteErrorLog(ex.ToString(), sqlScalar);
throw;
}
}
}
}
case ConnType.ODBC:
{
try
{
using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString))
{
con.Open();
var adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand(sqlScalar, con);
adapter.SelectCommand.CommandTimeout = 60000;
return(adapter.SelectCommand.ExecuteScalar());
}
}
catch (Exception ex)
{
this.WriteErrorLog(ex.ToString(), sqlScalar);
throw;
}
}
case ConnType.OLEDB:
{
try
{
using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString))
{
con.Open();
var adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(sqlScalar, con);
adapter.SelectCommand.CommandTimeout = 60000;
return(adapter.SelectCommand.ExecuteScalar());
}
}
catch (Exception ex)
{
this.WriteErrorLog(ex.ToString(), sqlScalar);
throw;
}
}
default:
{
return(false);
}
}
}
public void Start(string requestId, bool viewSQL)
{
try
{
if (Settings.Count == 0)
{
throw new Exception(CommonMessages.Exception_MissingSettings);
}
status.Code = RequestStatus.StatusCode.InProgress;
string server = Settings.GetAsString("Server", "");
string port = Settings.GetAsString("Port", "");
string userId = Settings.GetAsString("UserID", "");
string password = Settings.GetAsString("Password", "");
string database = Settings.GetAsString("Database", "");
string dataSourceName = Settings.GetAsString("DataSourceName", "");
string connectionTimeout = Settings.GetAsString("ConnectionTimeout", "15");
string commandTimeout = Settings.GetAsString("CommandTimeout", "120");
log.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout);
if (!Settings.ContainsKey("DataProvider"))
{
throw new Exception(CommonMessages.Exception_MissingDataProviderType);
}
string connectionString = string.Empty;
switch ((Lpp.Dns.DataMart.Model.Settings.SQLProvider)Enum.Parse(typeof(Lpp.Dns.DataMart.Model.Settings.SQLProvider), Settings.GetAsString("DataProvider", ""), true))
{
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.ODBC:
if (string.IsNullOrWhiteSpace(dataSourceName))
{
throw new Exception(CommonMessages.Exception_MissingODBCDatasourceName);
}
using (OdbcConnection cn = new OdbcConnection(string.Format("DSN={0}", dataSourceName)))
try
{
OdbcDataAdapter da = new OdbcDataAdapter(query, cn);
da.Fill(resultDataset);
}
finally
{
cn.Close();
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.PostgreSQL:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port == null || port == string.Empty)
{
port = "5432";
}
connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
// Making connection with Npgsql provider
using (NpgsqlConnection connnection = new NpgsqlConnection(connectionString))
{
try
{
connnection.Open();
NpgsqlCommand command = new NpgsqlCommand(query, connnection);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connnection.Close();
}
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.SQLServer:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port != null && port != string.Empty)
{
server += ", " + port;
}
connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}", server, userId, password, database, connectionTimeout) :
String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}", server, database, connectionTimeout);
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
try
{
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection);
command.CommandTimeout = int.Parse(commandTimeout);
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
break;
//case Lpp.Dns.DataMart.Model.Settings.SQLProvider.Oracle:
// // TODO: Implement this provider
// //throw new NotImplementedException("Oracle client not implemented yet");
// //connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};;Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
// //// TODO: Upgrade Oracle client
// //using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionString))
// //{
// // try
// // {
// // connection.Open();
// // System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(query, connection);
// // System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(command);
// // resultDataset.Reset();
// // da.Fill(resultDataset);
// // }
// // catch (Exception ex)
// // {
// // throw ex;
// // }
// // finally
// // {
// // connection.Close();
// // }
// //}
// break;
default:
throw new Exception(CommonMessages.Exception_InvalidDataProviderType);
}
status.Code = RequestStatus.StatusCode.Complete;
status.Message = "";
}
catch (Exception e)
{
status.Code = RequestStatus.StatusCode.Error;
status.Message = e.Message;
throw e;
}
}
/// <summary>
/// Used for select query.
/// </summary>
/// <param name="sqlCommand">The SQL string.</param>
/// <param name="querySchema">if set to <c>true</c> [query schema].</param>
/// <param name="schemaType">Type of the schema.</param>
/// <returns>
/// A Data Set with the selected Data tables and Data Rows.
/// </returns>
public DataSet Query(
string sqlCommand,
bool querySchema = false,
SchemaType schemaType = SchemaType.Source)
{
var result = new DataSet();
if (this.ConnectionType == ConnType.MSSQL)
{
int retryCount = 0;
while (true)
{
try
{
using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString))
{
con.Open();
var adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sqlCommand, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
if (querySchema)
{
adapter.FillSchema(result, schemaType);
}
adapter.Fill(result);
break;
}
}
catch (Exception ex)
{
if (ex is SqlException &&
Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) &&
retryCount < MaxRetry)
{
retryCount++;
Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait);
continue;
}
else
{
this.WriteErrorLog(ex.ToString(), sqlCommand);
throw;
}
}
}
}
else if (this.ConnectionType == ConnType.ODBC)
{
try
{
using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString))
{
con.Open();
var adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand(sqlCommand, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
if (querySchema)
{
adapter.FillSchema(result, schemaType);
}
adapter.Fill(result);
}
}
catch (Exception ex)
{
this.WriteErrorLog(ex.ToString(), sqlCommand);
throw;
}
}
else if (this.ConnectionType == ConnType.OLEDB)
{
try
{
using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString))
{
con.Open();
var adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(sqlCommand, con);
adapter.SelectCommand.CommandTimeout = SqlCmdTimeout;
if (querySchema)
{
adapter.FillSchema(result, schemaType);
}
adapter.Fill(result);
}
}
catch (Exception ex)
{
this.WriteErrorLog(ex.ToString(), sqlCommand);
throw;
}
}
return(result);
}
private void button1_Click(object sender, EventArgs e)
{
ConexionCapaDatos cone = new ConexionCapaDatos();
double sueldoM;
double sueldoD;
string saldo;
string dinero = "";
try {
try{
string auxi;
int diastxt = Convert.ToInt32(textBox6.Text);
if (btn1 == 1 && btn == 1 && diastxt <= 31 && diastxt > 0)
{
////
string cadenaB = "SELECT tE.ID_empleado, SUM(tCR.importe) AS total" +
" FROM tbl_empleados tE" +
" INNER JOIN tbl_empleadoconcepto tEC ON" +
" tE.ID_Empleado = tEC.ID_Empleado" +
" INNER JOIN tbl_conceptosretributivos tCR ON" +
" tCR.ID_ConceptosR = tEC.ID_ConceptosR" +
" WHERE tCR.tipo = 'ABONO' AND tE.ID_empleado=" + ID_EMP +
" group by 1;";
OdbcCommand cmd3 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer3 = cmd3.ExecuteReader();
while (leer3.Read())
{
bono = leer3.GetDouble(1);
}
cone.cnxClose();
string cadenaA = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + ID_EMP + ";";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer = cmd1.ExecuteReader();
while (leer.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
cone.cnxClose();
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadena = "UPDATE `Percepciones` SET `Dias` = " + "'" + DIA + "'" + ", `Total` = " + "'" + result + "'" + " WHERE (Percepciones.ID_Percepcion=" +
"\"" + ID_PER + "\"" + " and Percepciones.ID_Empleado=" + ID_EMP + ");";
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("Actualizado");
cone.cnxClose();
}
else
{
MessageBox.Show("Debe usar primero el boton de Actualizar\n o Los dias no deben superar los 31 ni menor a 1");
}
}
catch (Exception ex)
{
MessageBox.Show("HUBO UN ERROR \n" + ex);
}
}
catch (OdbcException ex)
{
MessageBox.Show("ERROE EN LA BASE DE DATOS \n" + ex);
}
}
protected void dtgUsergrid_SelectedIndexChanged(object sender, EventArgs e)
{
string s;
btnSave.Enabled = false;
btnEdit.Enabled = true;
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
con = obje.NewConnection();
cmbDefault.Items.Clear();
lstSelectedform.Items.Clear();
lstSelectform.Items.Clear();
OdbcCommand EditGrid = new OdbcCommand();
EditGrid.CommandType = CommandType.StoredProcedure;
EditGrid.Parameters.AddWithValue("tblname", "m_sub_form");
EditGrid.Parameters.AddWithValue("attribute", "form_id,formname,displayname");
EditGrid.Parameters.AddWithValue("conditionv", "form_id not in(select form_id from m_userprev_formset where prev_level=" + q + ") order by displayname asc");
OdbcDataAdapter EditG = new OdbcDataAdapter(EditGrid);
DataTable dtt5 = new DataTable();
dtt5 = obje.SpDtTbl("CALL selectcond(?,?,?)", EditGrid);
for (int ii = 0; ii < dtt5.Rows.Count; ii++)
{
lstSelectform.Items.Add(dtt5.Rows[ii][2].ToString());
lstSelectform.Items[ii].Text = dtt5.Rows[ii][2].ToString();
lstSelectform.Items[ii].Value = dtt5.Rows[ii][0].ToString();
}
OdbcCommand cmd9 = new OdbcCommand();
cmd9.CommandType = CommandType.StoredProcedure;
cmd9.Parameters.AddWithValue("tblname", "m_userprev_formset f,m_sub_form mf");
cmd9.Parameters.AddWithValue("attribute", "f.form_id,formname,displayname");
cmd9.Parameters.AddWithValue("conditionv", "prev_level=" + q + " and f.form_id=mf.form_id and rowstatus<>'2'");
OdbcDataAdapter dacnt35 = new OdbcDataAdapter(cmd9);
DataTable dtt35 = new DataTable();
dtt35 = obje.SpDtTbl("CALL selectcond(?,?,?)", cmd9);
for (int ii = 0; ii < dtt35.Rows.Count; ii++)
{
cmbDefault.Items.Add(dtt35.Rows[ii]["displayname"].ToString());
lstSelectedform.Items.Add(dtt35.Rows[ii]["displayname"].ToString());
}
OdbcCommand Use = new OdbcCommand();
Use.CommandType = CommandType.StoredProcedure;
Use.Parameters.AddWithValue("tblname", "m_userprevsetting u,m_sub_form mf");
Use.Parameters.AddWithValue("attribute", "prev_level,defaultform_id,displayname,execoverride");
Use.Parameters.AddWithValue("conditionv", "prev_level=" + q + " and u.defaultform_id=mf.form_id and mf.status<>'2'");
OdbcDataAdapter rd2 = new OdbcDataAdapter(Use);
DataTable dt5 = new DataTable();
dt5 = obje.SpDtTbl("CALL selectcond(?,?,?)", Use);
foreach (DataRow dr5 in dt5.Rows)
{
txtUserlevel.Text = dr5["prev_level"].ToString();
try
{
lstSelectedform.SelectedValue = dr5["displayname"].ToString();
cmbDefault.SelectedValue = dr5["displayname"].ToString();
}
catch
{
}
int g2 = Convert.ToInt32(dr5["execoverride"].ToString());
if (g2 > 0)
{
s = "Yes";
}
else
{
s = "No";
}
cmbExecute.SelectedValue = s.ToString();
}
btnSave.Enabled = false;
btnEdit.Enabled = true;
con.Close();
}
private void button7_Click(object sender, EventArgs e)
{
string auxi;
ConexionCapaDatos cone = new ConexionCapaDatos();
int contador = 1;
double sueldoM;
double sueldoD;
string saldo;
try {
try {
int x = 1;
string[] vector = new string[1024];
string nuevo_emp = Microsoft.VisualBasic.Interaction.InputBox("Ingrese el nuevo id del empleado", "Ingreso");
string cadena = "select ID_Empleado from Percepciones";/* where ID_Empleado="+
* // nuevo_emp +" and ID_Percepcion=" +"'"+ID_PER+"'"+"; ";*/
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
OdbcDataReader leer = cmd.ExecuteReader();
while (leer.Read())
{
vector[x] = leer.GetString(0);
x++;
}
cone.cnxClose();
for (int y = 1; y < x; y++)
{
string cadenaA = "select ID_Empleado from Percepciones where ID_Empleado=" +
nuevo_emp + " and ID_Percepcion=" + "'" + ID_PER + "'" + "; ";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer1 = cmd1.ExecuteReader();
while (leer1.Read())
{
if (vector[y] == nuevo_emp)
{
contador = 1;
}
else
{
contador = 0;
}
}
cone.cnxClose();
}
if (contador == 1)
{
MessageBox.Show("EMPLEADO YA INGRESADO");
}
else
{
string nuevo_dias = Microsoft.VisualBasic.Interaction.InputBox("Ingrese los dias", "Ingreso");
///////////////////
int diastxt = Convert.ToInt32(nuevo_dias);
string cadenaB = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + nuevo_emp + ";";
MessageBox.Show(cadenaB);
OdbcCommand cmd2 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer1 = cmd2.ExecuteReader();
while (leer1.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadenaC = "insert into Percepciones value(" +
"\"" + ID_PER + "\"," + ID_EMP + "," + result + "," + diastxt + ");";
OdbcCommand cmd3 = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("EMPLEADO INSERTADO");
cone.cnxClose();
////////////////
}
}
catch (OdbcException ex)
{
MessageBox.Show("Error en la base de datos \n" + ex);
cone.cnxClose();
}
}
catch (Exception ex)
{
MessageBox.Show("Error en la base de datos\n" + ex);
cone.cnxClose();
}
}
//Buscar
private void button2_Click(object sender, EventArgs e)
{
if (txtParam.Text != "")
{
int x;
OdbcConnection con = TBD1.AseConnection.Connect();
OdbcCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "execute SP_READ_EMPLEADO @id =" + txtParam.Text;
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(dt);
x = Convert.ToInt32(dt.Rows.Count.ToString());
if (x == 0)
{
TBD1.AseConnection.messageBox("Empledo No Existe.");
txtParam.Text = "";
}
else
{
int x2;
cmd.CommandText = "execute SP_READ_EMPLEADO_CORREO2 @CODIGO =" + txtParam.Text;
cmd.ExecuteNonQuery();
DataTable dt2 = new DataTable();
OdbcDataAdapter da2 = new OdbcDataAdapter(cmd);
da2.Fill(dt2);
x2 = Convert.ToInt32(dt2.Rows.Count.ToString());
if (x2 != 0)
{
dt2.Columns["correo_electronico"].ColumnName = "Correo Electronico";
dt2.Columns["empleado_codigo_empleado"].ColumnName = "Codigo Empleado";
dataGridView2.DataSource = dt2;
}
int x3;
cmd.CommandText = "execute SP_READ_EMPLEADO_TELEFONO2 @CODIGO =" + txtParam.Text;
cmd.ExecuteNonQuery();
DataTable dt3 = new DataTable();
OdbcDataAdapter da3 = new OdbcDataAdapter(cmd);
da3.Fill(dt3);
x3 = Convert.ToInt32(dt3.Rows.Count.ToString());
if (x3 != 0)
{
dt3.Columns["Telefono"].ColumnName = "Telefono";
dt3.Columns["empleado_codigo_empleado"].ColumnName = "Codigo Empleado";
dataGridView3.DataSource = dt3;
}
dt.Columns["codigo_empleado"].ColumnName = "Codigo Empleado";
dt.Columns["primer_nombre"].ColumnName = "Primer Nombre";
dt.Columns["segundo_nombre"].ColumnName = "Segundo Nombre";
dt.Columns["primer_apellido"].ColumnName = "Primer Apellido";
dt.Columns["segundo_apellido"].ColumnName = "Segundo Apellido";
dt.Columns["ciudad"].ColumnName = "Ciudad";
dt.Columns["calle"].ColumnName = "Calle";
dt.Columns["avenida"].ColumnName = "Avenida";
dt.Columns["numero_casa"].ColumnName = "#Casa";
dt.Columns["departamento"].ColumnName = "Departamento";
dt.Columns["referencia"].ColumnName = "Referencia";
dt.Columns["fecha_inicio"].ColumnName = "Fecha Inicio";
dt.Columns["fecha_nacimiento"].ColumnName = "Fecha Nacimiento";
dt.Columns["fecha_creacion"].ColumnName = "Fecha Creacion";
dt.Columns["ultima_modificacion"].ColumnName = "Ultima Modificacion";
dt.Columns["usuario_creador"].ColumnName = "Usuario Creador";
dt.Columns["usuario_modificador"].ColumnName = "Usuario Modificador";
dt.Columns["usuario_id_usuario"].ColumnName = "Id Usuario";
dataGridView1.DataSource = dt;
txtParam.Text = "";
}
}
else
{
TBD1.AseConnection.messageBox("Debe ingresar un valor de Empleado!");
}
}
/// <summary>
/// ODBCRecordsetClass Constructor: initialize internal variables,
/// the connection is initialized using the connection string.
/// </summary>
/// <param name="connString"></param>
public ODBCRecordsetClass(String connString)
{
dbConnection = new OdbcConnection(connString);
dbAdapter = new OdbcDataAdapter();
dbDataSet = new DataSet();
newRow = false;
foundRecordsCounter = 0;
classFields = new FieldsClass(this);
SetInitialPosition();
}
