//-----------------------------------------------------------------------------------------------------------------------
// 제목 : Connection 과 Recordset 을 닫는 함수
//
// 인자 : rs - Recordset
//-----------------------------------------------------------------------------------------------------------------------
public static void CloseAll(ref ADODB.Recordset Rs, short TransAction)
{
try
{
//RecordSet 해제
if (CN.State != (int)ADODB.ObjectStateEnum.adStateClosed)
{
switch (TransAction)
{
case 0:
CN.Close();
break;
case 1:
CN.CommitTrans(); //CommitTrans
break;
case 2:
CN.RollbackTrans(); //RollbackTrans
break;
}
}
CN = null;
if (Rs != null)
{
if (Rs.State != (int)ADODB.ObjectStateEnum.adStateClosed)
{
Rs.Close();
}
}
//ADO 연결 해제 (1= Commit, 2=Rollback)
CloseCN(TransAction);
}
catch (Exception)
{
//MessageBox.Show(e.Message + "\n Data Base Error!", "확인");
//CloseAll(ref Rs, 0);
Rs.Close();
CN.Close();
}
finally
{
}
}
private void btnsave_Click(object sender, EventArgs e)
{
ADODB.Recordset tmp = new ADODB.Recordset();
try
{
if (ADOconn.State == 0)
{
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
}
ADODB.Recordset rec = new ADODB.Recordset();
Conn.Close();
// Conn.Open();
bool isempty;
isempty = false;
if (isedit)
{
if (txtpriv.Text.Substring(1, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
else
{
if (txtpriv.Text.Substring(0, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
if (isempty)
{
MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry");
return;
}
try
{
// ADOconn.BeginTrans();
//if (cmbmonth.SelectedIndex<0)
//{
// MessageBox.Show("Invalid Salary Month, Please Select a Valid Month", "Invalid Entry");
// return;
//}
ADOconn.BeginTrans();
int i = 0;
string entry_no = dtentry.Value.Date.ToString("yyyyMMdd");
if (isedit)
{
sql = "delete from reconcil_det where Reconcil_No = '" + entry_no + "'";
object a;
ADOconn.Execute(sql, out a);
}
sql = "select * from reconcil_det where Reconcil_No = '" + entry_no + "'";
rec = new ADODB.Recordset();
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (rec.RecordCount == 0)
{
// rec.AddNew();
}
else
{
MessageBox.Show("Entry Already Exist , Cannot enter as a New entry", "Invalid Entry");
return;
}
for (i = 0; i < dgv1.Rows.Count; i++)
{
if (dgv1[0, i].Value != null)
{
rec.AddNew();
if (dgv1[3, i].Value == null)
{
dgv1[3, i].Value = 0;
}
if (dgv1[4, i].Value == null)
{
dgv1[4, i].Value = 0;
}
double amt = Convert.ToDouble(dgv1[3, i].Value) + Convert.ToDouble(dgv1[4, i].Value);
rec.Fields["Reconcil_No"].Value = entry_no;
rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date;
rec.Fields["Reconcil_user"].Value = Gvar.username;
rec.Fields["Amount"].Value = Math.Abs(amt);
rec.Fields["Bank_No"].Value = cmbbank.SelectedValue;
rec.Fields["DR_CR"].Value = dgv1[2, i].Value;
rec.Fields["plus_minus"].Value = "";
rec.Fields["pay_date"].Value = dgv1[0, i].Value;
rec.Fields["SNO"].Value = i + 1;
rec.Fields["trn_type"].Value = "A";
rec.Fields["NARRATION"].Value = dgv1[5, i].Value;
rec.Update();
}
}
for (i = 0; i < dgvbank.Rows.Count; i++)
{
if (dgvbank[2, i].Value != null)
{
rec.AddNew();
if (dgvbank[0, i].Value == null)
{
dgvbank[0, i].Value = "";
}
if (dgvbank[1, i].Value == null)
{
dgvbank[1, i].Value = 0;
}
double amt = Convert.ToDouble(dgvbank[2, i].Value);
rec.Fields["Reconcil_No"].Value = entry_no;
rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date;
rec.Fields["Reconcil_user"].Value = Gvar.username;
rec.Fields["Amount"].Value = Math.Abs(amt);
rec.Fields["Bank_No"].Value = cmbbank.SelectedValue;
if (dgvbank[1, i].Value == "+")
{
rec.Fields["DR_CR"].Value = "D";
}
else
{
rec.Fields["DR_CR"].Value = "C";
}
rec.Fields["plus_minus"].Value = dgvbank[1, i].Value;
rec.Fields["pay_date"].Value = "";
rec.Fields["SNO"].Value = i + 1;
rec.Fields["trn_type"].Value = "B";
rec.Fields["NARRATION"].Value = dgvbank[0, i].Value;
rec.Update();
}
}
for (i = 0; i < dgvbook.Rows.Count; i++)
{
if (dgvbook[2, i].Value != null)
{
rec.AddNew();
if (dgvbook[0, i].Value == null)
{
dgvbook[0, i].Value = "";
}
if (dgvbook[1, i].Value == null)
{
dgvbook[1, i].Value = 0;
}
double amt = Convert.ToDouble(dgvbook[2, i].Value);
rec.Fields["Reconcil_No"].Value = entry_no;
rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date;
rec.Fields["Reconcil_user"].Value = Gvar.username;
rec.Fields["Amount"].Value = Math.Abs(amt);
rec.Fields["Bank_No"].Value = cmbbank.SelectedValue;
if (dgvbook[1, i].Value == "+")
{
rec.Fields["DR_CR"].Value = "D";
}
else
{
rec.Fields["DR_CR"].Value = "C";
}
rec.Fields["plus_minus"].Value = dgvbook[1, i].Value;
rec.Fields["pay_date"].Value = "";
rec.Fields["SNO"].Value = i + 1;
rec.Fields["trn_type"].Value = "C";
rec.Fields["NARRATION"].Value = dgvbook[0, i].Value;
rec.Update();
}
}
ADOconn.CommitTrans();
isedit = true;
MessageBox.Show("Successfully Saved");
}
catch (Exception ex)
{
ADOconn.RollbackTrans();
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnsave_Click(object sender, EventArgs e)
{
ADODB.Recordset tmp = new ADODB.Recordset();
try
{
if (!Program.session_valid(dt1.Value.Date.ToString("yyyy-MM-dd")))
{
MessageBox.Show("There is no valid Finance Session Found, Please check the Entry Date or Contact Admin ", "Invalid Transaction Date ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (ADOconn.State == 0)
{
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
}
ADODB.Recordset rec = new ADODB.Recordset();
Conn.Close();
// Conn.Open();
bool isempty;
isempty = false;
if (isedit)
{
if (txtpriv.Text.Substring(1, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
else
{
if (txtpriv.Text.Substring(0, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
//if (isempty)
//{
// MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry");
// return;
//}
try
{
// ADOconn.BeginTrans();
// if (!isedit)
// {
// if (txtismanual.Text == "0")
// {
// gen_accno();
// tmp = new ADODB.Recordset();
// sql = "update ACC_TYPE SET CUR_NO = CUR_NO+1 WHERE ACC_TYPE_CODE=" + cmbtype.SelectedValue;
// tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1);
// }
// }
if (txtsalacno.Text == "")
{
MessageBox.Show("Invalid Salary Account Number, Please Enter a Valid Number", "Invalid Entry");
return;
}
if (cmbmonth.SelectedIndex < 0)
{
MessageBox.Show("Invalid Salary Month, Please Select a Valid Month", "Invalid Entry");
return;
}
if (txtnet.Text.Trim() == "")
{
txtnet.Text = "0";
}
// //if (txtvalue.Text.Trim() == "") txtvalue.Text = "0";
isini = true;
if (txtbasic.Text == "")
{
txtbasic.Text = "0";
}
if (txtempid.Text == "")
{
txtempid.Text = "0";
}
if (txthousing.Text == "")
{
txthousing.Text = "0";
}
if (txttransport.Text == "")
{
txttransport.Text = "0";
}
if (txtother.Text == "")
{
txtother.Text = "0";
}
if (txtdeduction.Text == "")
{
txtdeduction.Text = "0";
}
if (txtloan.Text == "")
{
txtloan.Text = "0";
}
if (txtcontractno.Text == "")
{
txtcontractno.Text = "0";
}
if (txtworkeddays.Text == "")
{
txtworkeddays.Text = "0";
}
if (txtnet.Text == "")
{
txtnet.Text = "0";
}
if (txtsalacno.Text == "")
{
txtsalacno.Text = "0";
}
if (txtempid.Text == "")
{
txtempid.Text = "0";
}
if (txtpaidamt.Text == "")
{
txtpaidamt.Text = "0";
}
if (txtbrncode.Text == "")
{
txtbrncode.Text = Gvar.brn_code.ToString();
}
if (txtcontractno.Text == "0")
{
MessageBox.Show("Invalid Contract Numebr, Please check and Try Again", "Invalid Entry");
return;
}
if (Convert.ToDecimal(txtpaidamt.Text) > Convert.ToDecimal(txtnet.Text))
{
MessageBox.Show("There is already a higher Paid amount found, Please check and Try Again", "Invalid Entry");
return;
}
isini = false;
find_total();
//rec.Fields["veh_no"].Value = Convert.ToDouble(txtvehno.Text.Trim());
ADOconn.BeginTrans();
int TRN_BY = 13;
sql = "select * from TRN_accounts where trn_NO = '" + txtactrnno.Text.Trim() + "' AND TRN_BY = 13 and Sno = 1";
rec = new ADODB.Recordset();
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
long trnno = 0;
long trnno2 = 0;
if (rec.RecordCount == 0)
{
rec.AddNew();
tmp = new ADODB.Recordset();
sql = "SELECT * FROM TRNNO";
tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1);
//if (tmp.Fields[0].Value)
// txttrn.Text="1";
//else
if (tmp.Fields[0].Value == DBNull.Value)
{
trnno = 1;
}
else
{
trnno = Convert.ToInt64(tmp.Fields[0].Value.ToString());
trnno2 = trnno + 1;
}
rec.Fields["trn_no"].Value = trnno;
rec.Fields["trn_no2"].Value = trnno + 1;
}
trnno = Convert.ToInt64(rec.Fields["trn_no"].Value);
double amt = Convert.ToDouble(txtnet.Text);
long doc_no = -1 * trnno;
string DR_CR = "D";
string DR_CR1 = "C";
rec.Fields["acc_no"].Value = txtempacno.Text;
rec.Fields["EntrY_no"].Value = 0;
double rate = 1;
rec.Fields["PAY_AMOUNT"].Value = Math.Abs(amt * rate);
rec.Fields["F_PAY_AMOUNT"].Value = Math.Abs(amt);
rec.Fields["F_RATE"].Value = 1;
rec.Fields["TRN_BY"].Value = TRN_BY;
rec.Fields["DR_CR"].Value = DR_CR;
rec.Fields["user_ID"].Value = Gvar.Userid;
rec.Fields["PAYBY"].Value = txtsalacno.Text;
//rec.Fields["RQTY"].Value = 0;
rec.Fields["SNO"].Value = 1;
// rec.Fields["FRACTION"].Value = dgv1["fraction", i].Value;
rec.Fields["NARRATION"].Value = "Salary Process of " + cmbmonth.Text;
rec.Fields["Voucher_No"].Value = 0;
rec.Fields["cost_code"].Value = 0;
rec.Fields["dept_code"].Value = 0;
rec.Fields["pay_date"].Value = dt1.Value;
rec.Fields["doc_no"].Value = doc_no;
rec.Fields["NYEAR"].Value = dt1.Value.Year;
rec.Fields["brn_code"].Value = Gvar.brn_code;
rec.Fields["currency"].Value = "SR";
rec.Fields["brn_code"].Value = txtbrncode.Text;
rec.Fields["trn_type"].Value = TRN_BY;
rec.Update();
sql = "select * from TRN_accounts where trn_NO2 = '" + txtactrnno.Text.Trim() + "' AND TRN_BY = 13 and Sno = 2";
rec = new ADODB.Recordset();
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (rec.RecordCount == 0)
{
rec.AddNew();
tmp = new ADODB.Recordset();
sql = "SELECT * FROM TRNNO";
tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1);
//if (tmp.Fields[0].Value)
// txttrn.Text="1";
//else
if (tmp.Fields[0].Value == DBNull.Value)
{
trnno2 = 1;
}
else
{
trnno2 = Convert.ToInt64(tmp.Fields[0].Value.ToString());
}
rec.Fields["trn_no"].Value = trnno2;
rec.Fields["trn_no2"].Value = trnno;
}
trnno2 = Convert.ToInt64(rec.Fields["trn_no"].Value);
rec.Fields["acc_no"].Value = txtsalacno.Text;
rec.Fields["EntrY_no"].Value = 0;
rec.Fields["PAY_AMOUNT"].Value = Math.Abs(amt * rate);
rec.Fields["F_PAY_AMOUNT"].Value = Math.Abs(amt);
rec.Fields["F_RATE"].Value = 1;
rec.Fields["TRN_BY"].Value = TRN_BY;
rec.Fields["DR_CR"].Value = DR_CR1;
rec.Fields["user_ID"].Value = Gvar.Userid;
rec.Fields["PAYBY"].Value = txtempacno.Text;
//rec.Fields["RQTY"].Value = 0;
rec.Fields["SNO"].Value = 2;
// rec.Fields["FRACTION"].Value = dgv1["fraction", i].Value;
rec.Fields["NARRATION"].Value = "Salary Process of " + cmbmonth.Text + " for " + lblname.Text;
rec.Fields["Voucher_No"].Value = 0;
rec.Fields["cost_code"].Value = 0;
rec.Fields["dept_code"].Value = 0;
rec.Fields["pay_date"].Value = dt1.Value;
rec.Fields["doc_no"].Value = doc_no;
rec.Fields["NYEAR"].Value = dt1.Value.Year;
rec.Fields["brn_code"].Value = txtbrncode.Text;
rec.Fields["trn_type"].Value = TRN_BY;
rec.Fields["currency"].Value = "SR";
rec.Update();
rec = new ADODB.Recordset();
sql = "SELECT * FROM salary_det where salary_month = '" + cmbmonth.Text + "' and emp_id =" + txtempid.Text;
// sql = "SELECT * FROM Accounts where Acc_no ='" + txtaccno.Text.Trim() + "'";
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (rec.RecordCount == 0)
{
rec.AddNew();
}
rec.Fields["EMP_ID"].Value = txtempid.Text.Trim();
rec.Fields["SALARY_MONTH"].Value = cmbmonth.Text.Trim();
rec.Fields["EFFECT_DATE"].Value = dt1.Value.Date.ToString("yyyy-MM-dd");
rec.Fields["FULLNAME"].Value = lblname.Text;
rec.Fields["BASIC"].Value = txtbasic.Text;
rec.Fields["HOUSING"].Value = txthousing.Text.Trim();
rec.Fields["TRANSPORTATION"].Value = txttransport.Text.Trim();
rec.Fields["OTHER"].Value = txtother.Text.Trim();
rec.Fields["DEDUCTION"].Value = txtdeduction.Text.Trim();
rec.Fields["LOAN_AMT"].Value = txtloan.Text.Trim();
rec.Fields["REMARKS"].Value = txtremarks.Text.Trim();
rec.Fields["NET_AMOUNT"].Value = txtnet.Text.Trim();
rec.Fields["SAL_ACNO"].Value = txtsalacno.Text.Trim();
rec.Fields["EMP_ACNO"].Value = txtempacno.Text.Trim();
rec.Fields["worked_days"].Value = txtworkeddays.Text.Trim();
rec.Fields["CONTRACT_ID"].Value = txtcontractno.Text.Trim();
rec.Fields["NYEAR"].Value = dt1.Value.Year.ToString();
rec.Fields["TRN_NO1"].Value = trnno;
rec.Fields["TRN_NO2"].Value = trnno2;
rec.Fields["EMP_BraNCH"].Value = txtbrncode.Text.Trim();
rec.Update();
ADOconn.CommitTrans();
populate_empdet();
//DataGridViewRow row = dgv1.CurrentRow;
//if (txtempid.Text.ToString() == dgv1[1, dgv1.CurrentRow.Index].Value.ToString())
//{
// dgv1.Rows.Remove(row);
//}
isedit = true;
MessageBox.Show("Successfully Saved");
}
catch (Exception ex)
{
ADOconn.RollbackTrans();
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//string sql;
private void save_form()
{
ADODB.Connection ADOconn = new ADODB.Connection();
ADODB.Recordset tmp = new ADODB.Recordset();
try
{
//if (tooltip.Text.Trim().Length < 3)
//{
// MessageBox.Show("Invalid Length of Code, Must Be 3 Digit!!");
// return;
//}
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
ADODB.Recordset rec = new ADODB.Recordset();
Conn.Close();
// Conn.Open();
bool isempty;
isempty = false;
if (isedit)
{
if (txtpriv.Text.Substring(1, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
else
{
if (txtpriv.Text.Substring(0, 1) == "0")
{
MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges ");
return;
}
}
foreach (Control gb in this.Controls)
{
if (gb is GroupBox)
{
foreach (Control tb in gb.Controls)
{
if (tb is TextBox)
{
if (tb.Tag == "1")
{
tb.BackColor = System.Drawing.Color.White;
if (string.IsNullOrEmpty(tb.Text.Trim()))
{
tb.BackColor = System.Drawing.Color.Yellow;
isempty = true;
}
}
}
}
}
}
if (isempty)
{
MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry");
return;
}
try
{
ADOconn.BeginTrans();
if (!isedit)
{
gen_accno();
tmp = new ADODB.Recordset();
//sql = "update ACC_TYPE SET CUR_NO = CUR_NO+1 WHERE ACC_TYPE_CODE=" + cmbCostCode.SelectedValue;
// tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1);
}
if (txtProjectAmt.Text.Trim() == "")
{
txtProjectAmt.Text = "0";
}
//if (txtvalue.Text.Trim() == "") txtvalue.Text = "0";
//sql = "SELECT * FROM Customer where Customer_no =" + Convert.ToDecimal(txtcuscode.Text.Trim());
sql = "SELECT * FROM project_master where project_code ='" + txtprojectcode.Text.Trim() + "'";
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (rec.RecordCount == 0)
{
rec.AddNew();
}
if (txtProjectAmt.Text == "")
{
txtProjectAmt.Text = "0";
}
if (txtpercent.Text == "")
{
txtpercent.Text = "0";
}
//rec.Fields["veh_no"].Value = Convert.ToDouble(txtvehno.Text.Trim());
rec.Fields["Project_Code"].Value = txtprojectcode.Text.Trim();
rec.Fields["Project_Name"].Value = txtProjectname.Text.Trim();
rec.Fields["Client_code"].Value = cmbacc.SelectedValue;
rec.Fields["Cost_Code"].Value = Convert.ToInt32(txtprojectcode.Text); //cmbCostCode.SelectedValue;
rec.Fields["Start_date"].Value = dt1.Value.Date.ToString("yyyy-MM-dd");;
rec.Fields["End_Date"].Value = dtend.Value.Date.ToString("yyyy-MM-dd");
rec.Fields["Project_det"].Value = txtDescription.Text.Trim();
rec.Fields["completed"].Value = Convert.ToDouble(txtpercent.Text);
rec.Fields["Status"].Value = cmbstatus.Text.Trim();
rec.Fields["Incharge"].Value = cmbincharge.Text.Trim();
rec.Fields["Location"].Value = cmblocaltion.Text.Trim();
rec.Fields["Project_Amt"].Value = Convert.ToDouble(txtProjectAmt.Text);
rec.Update();
sql = "select Cost_Code,Cost_name from Cost_Master where cost_code= " + Convert.ToInt32(txtprojectcode.Text);
rec = new ADODB.Recordset();
rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (rec.RecordCount == 0)
{
rec.AddNew();
}
rec.Fields["Cost_Code"].Value = Convert.ToInt32(txtprojectcode.Text);
rec.Fields["Cost_name"].Value = txtProjectname.Text.Trim();
rec.Update();
ADOconn.CommitTrans();
isedit = true;
MessageBox.Show("Successfully Saved");
load_leaders();
}
catch (Exception ex)
{
ADOconn.RollbackTrans();
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}