public void SaveInvoice()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
oDB.BeginTran();
foreach (Item ItemObj in this.ItemObject)
{
sbQry.AppendLine("Insert Into Invoice(TinNo,PanNo,BillNo,InvoiceDate,InvoiceDateTime,Address,PoNo");
sbQry.AppendLine(",ItemDesc,Qty,Rate,Amount,Vat,GrandTotalWords) Values('" + TinNo + "','" + PanNo + "'");
sbQry.AppendLine(",'" + BillNo + "','" + DateTime.Now.ToString("dd-MMM-yyyy") + "',Getdate(),'" + Address + "'");
sbQry.AppendLine(",'" + PoNo + "','" + ItemObj.ItemDesc + "'," + ItemObj.ItemQty + "," + ItemObj.Rate + "," + ItemObj.Amount + ",'" + Vat + "','" + GrandTotalInWords + "')");
oDB.ExecuteNonQuery(sbQry.ToString());
sbQry.Length = 0;
}
oDB.CommitTran();
}
catch (Exception ex)
{
oDB.RollBackTran();
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public DataTable GetPanTin()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.Append("Select Top(1) TinNo,PanNo From mPanNo");
return(oDB.GetDataTable(sbQry.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public DataTable GetHeader()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.Append("Select Id,Header1,Header2,Header3,Header4 From mHeader Order by Id");
return(oDB.GetDataTable(sbQry.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public DataTable GetAddress()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.Append("Select Id,Address1,Address2,Address3,Address4 From mAddress Order by Id");
return(oDB.GetDataTable(sbQry.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public DataTable GetItem()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.Append("Select ItemDesc From Item Order by ItemDesc");
return(oDB.GetDataTable(sbQry.ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public int DeleteItem()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.Append("Delete From Item Where ItemDesc = '" + ItemDesc + "'");
int iCount = oDB.ExecuteNonQuery(sbQry.ToString());
return(iCount);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
private void btnGet_Click(object sender, RoutedEventArgs e)
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
sbQry.AppendLine("Select * From Invoice Where InvoiceDate >= '" + Convert.ToDateTime(FromDate.Text).ToString("dd-MMM-yyyy") + "' And InvoiceDate <= '" + Convert.ToDateTime(ToDate.Text).ToString("dd-MMM-yyyy") + "'");
if (cmbFilter.SelectedIndex > 0)
{
sbQry.AppendLine(" And " + cmbFilter.SelectedItem.ToString() + " Like '%" + txtFilterValue.Text.Trim() + "%'");
}
dg.ItemsSource = oDB.GetDataTable(sbQry.ToString()).DefaultView;
btnItemCount.Content = dg.Items.Count;
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public int SaveHeader()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
string sMsg = "OK";
sbQry.Append("Select Count(*) From mHeader Where Id = '" + HeaderId + "'");
int iCount = (int)oDB.ExecuteScalar(sbQry.ToString());
sbQry.Length = 0;
//If already exist then update otherwise insert
if (iCount > 0)
{
sbQry.Append("Update mHeader Set Header1='" + Header1 + "',Header2='" + Header2 + "',Header3='" + Header3 + "',Header4='" + Header4 + "' Where Id = '" + HeaderId + "'");
}
else
{
sbQry.Append("Insert Into mHeader(Id,Header1,Header2,Header3,Header4) Values('" + HeaderId + "','" + Header1 + "','" + Header2 + "','" + Header3 + "','" + Header4 + "')");
}
iCount = oDB.ExecuteNonQuery(sbQry.ToString());
return(iCount);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public string SaveItem()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
string sMsg = "OK";
sbQry.Append("Select Count(*) From Item Where ItemDesc = '" + ItemDesc + "'");
int iCount = (int)oDB.ExecuteScalar(sbQry.ToString());
sbQry.Length = 0;
//If already exist then update otherwise insert
if (iCount > 0)
{
sMsg = "Item already exist!!";
}
else
{
sbQry.Append("Insert Into Item(ItemDesc) Values('" + ItemDesc + "')");
}
iCount = oDB.ExecuteNonQuery(sbQry.ToString());
return(sMsg);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}
public int SaveAddress()
{
StringBuilder sbQry = new StringBuilder();
clsDB oDB = new clsDB();
try
{
oDB.Connect();
string sMsg = "OK";
sbQry.Append("Select Count(*) From mAddress Where Id = '" + AddressId + "'");
int iCount = (int)oDB.ExecuteScalar(sbQry.ToString());
sbQry.Length = 0;
//If already exist then update otherwise insert
if (iCount > 0)
{
sbQry.Append("Update mAddress Set Address1='" + Address1 + "',Address2='" + Address2 + "',Address3='" + Address3 + "',Address4='" + Address4 + "' Where Id = '" + AddressId + "'");
}
else
{
sbQry.Append("Insert Into mAddress(Id,Address1,Address2,Address3,Address4) Values('" + AddressId + "','" + Address1 + "','" + Address2 + "','" + Address3 + "','" + Address4 + "')");
}
iCount = oDB.ExecuteNonQuery(sbQry.ToString());
return(iCount);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sbQry = null;
oDB.DisConnect();
oDB = null;
}
}