public void UpdateExtraBill(ExtraBillRegBO argBRegBo, DataTable argBTrans, DataTable argQTrans)
{
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string frmdat = string.Format("{0:dd MMM yyyy}", argBRegBo.BillDate);
// Bill Register
string Sql = "Update dbo.ExtraBillRegister set BillDate='" + frmdat + "',BillNo='" + argBRegBo.RefNo + "',CostCentreId=" + argBRegBo.CCId + "," +
"FlatId=" + argBRegBo.FlatId + ", BillAmount=" + argBRegBo.BillAmt + ",NetAmount=" + argBRegBo.NetAmt + ",Narration='" + argBRegBo.Narration + "' " +
"Where BillRegId=" + argBRegBo.BillRegId + " ";
SqlCommand cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
int iBillRegId = argBRegBo.BillRegId;
Sql = "DELETE FROM dbo.ExtraBillTrans WHERE BillRegId=" + iBillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "Delete from dbo.ExtraBillRateQ where BillRegId=" + iBillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "Delete from dbo.ExtraBillRateQAbs where BillRegId=" + iBillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
//BillTrans
for (int u = 0; u < argBTrans.Rows.Count; u++)
{
Sql = "Insert Into dbo.ExtraBillTrans (BillRegId,ExtraItemId,Qty,Rate,Amount,NetAmount) " +
"Values (" + iBillRegId + "," + argBTrans.Rows[u]["EItemId"].ToString() + "," + argBTrans.Rows[u]["Qty"].ToString() +
"," + argBTrans.Rows[u]["Rate"].ToString() + "," + argBTrans.Rows[u]["Amt"].ToString() + "," + argBTrans.Rows[u]["NetAmt"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//Bill Qualifier
decimal d_TotalTaxAmt = 0;
for (int u = 0; u < argQTrans.Rows.Count; u++)
{
d_TotalTaxAmt = d_TotalTaxAmt + Convert.ToInt32(argQTrans.Rows[u]["Amount"]);
Sql = "Insert Into dbo.ExtraBillRateQ(BillRegId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount, " +
" ExpValue,ExpPerValue,SurValue,EdValue,FlatId,HEDPer,NetPer,TaxablePer,TaxableValue) Values(" + iBillRegId +
"," + argQTrans.Rows[u]["QualifierId"].ToString() + ",'" + argQTrans.Rows[u]["Expression"].ToString() +
"'," + argQTrans.Rows[u]["ExpPer"].ToString() + ",'" + argQTrans.Rows[u]["Add_Less_Flag"].ToString() +
"'," + argQTrans.Rows[u]["SurCharge"].ToString() + "," + argQTrans.Rows[u]["EDCess"].ToString() + "," + argQTrans.Rows[u]["Amount"].ToString() +
"," + argQTrans.Rows[u]["ExpValue"].ToString() + "," + argQTrans.Rows[u]["ExpPerValue"].ToString() +
"," + argQTrans.Rows[u]["SurValue"].ToString() + "," + argQTrans.Rows[u]["EdValue"].ToString() +
"," + argBRegBo.FlatId + "," + argQTrans.Rows[u]["HEDPer"].ToString() + "," + argQTrans.Rows[u]["NetPer"].ToString() +
"," + argQTrans.Rows[u]["TaxablePer"].ToString() + "," + argQTrans.Rows[u]["TaxableValue"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
if (argQTrans.Rows.Count > 0)
{
Sql = "Insert Into dbo.ExtraBillRateQAbs(BillRegId,QualifierId,Amount,Add_Less_Flag) " +
"Values (" + iBillRegId + "," + argQTrans.Rows[0]["QualifierId"].ToString() + "," + d_TotalTaxAmt +
",'" + argQTrans.Rows[0]["Add_Less_Flag"].ToString() + "')";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
tran.Rollback();
}
finally
{
conn.Dispose();
conn.Close();
}
}
public void UpdateExtraBill(ExtraBillRegBO argBRegBo, DataTable argBTrans, DataTable argQTrans)
{
m_lDL.UpdateExtraBill(argBRegBo, argBTrans, argQTrans);
}
public int InsertExtraBill(ExtraBillRegBO argBRegBo, DataTable argBTrans, DataTable argQTrans, int argFlatId)
{
int iBillRegId = 0;
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string frmdat = string.Format("{0:dd MMM yyyy}", argBRegBo.BillDate);
string Sql = "DELETE FROM dbo.ExtraBillRegister WHERE FlatId=" + argFlatId;
SqlCommand cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillTrans WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillRateQ WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillRateQAbs WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
//Bill Register
Sql = "Insert Into dbo.ExtraBillRegister(BillDate,BillNo,CostCentreId,FlatId,BillAmount,NetAmount,Narration) " +
"Values ('" + frmdat + "','" + argBRegBo.RefNo + "'," + argBRegBo.CCId + "," + argBRegBo.FlatId + "," + argBRegBo.BillAmt +
"," + argBRegBo.NetAmt + ",'" + argBRegBo.Narration + "') Select SCOPE_IDENTITY();";
cmd = new SqlCommand(Sql, conn, tran);
iBillRegId = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
//BillTrans
for (int u = 0; u < argBTrans.Rows.Count; u++)
{
Sql = "Insert Into dbo.ExtraBillTrans(BillRegId,ExtraItemId,Qty,Rate,Amount,NetAmount) " +
"Values (" + iBillRegId + "," + argBTrans.Rows[u]["EItemId"].ToString() + "," + argBTrans.Rows[u]["Qty"].ToString() +
"," + argBTrans.Rows[u]["Rate"].ToString() + "," + argBTrans.Rows[u]["Amt"].ToString() + "," + argBTrans.Rows[u]["NetAmt"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//Bill Qualifier
decimal d_TotalTaxAmt = 0;
for (int u = 0; u < argQTrans.Rows.Count; u++)
{
d_TotalTaxAmt = d_TotalTaxAmt + Convert.ToInt32(argQTrans.Rows[u]["Amount"]);
Sql = "Insert Into dbo.ExtraBillRateQ(BillRegId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount, " +
" ExpValue,ExpPerValue,SurValue,EdValue,FlatId,HEDPer,NetPer,TaxablePer,TaxableValue) Values(" + iBillRegId +
"," + argQTrans.Rows[u]["QualifierId"].ToString() + ",'" + argQTrans.Rows[u]["Expression"].ToString() +
"'," + argQTrans.Rows[u]["ExpPer"].ToString() + ",'" + argQTrans.Rows[u]["Add_Less_Flag"].ToString() +
"'," + argQTrans.Rows[u]["SurCharge"].ToString() + "," + argQTrans.Rows[u]["EDCess"].ToString() + "," + argQTrans.Rows[u]["Amount"].ToString() +
"," + argQTrans.Rows[u]["ExpValue"].ToString() + "," + argQTrans.Rows[u]["ExpPerValue"].ToString() +
"," + argQTrans.Rows[u]["SurValue"].ToString() + "," + argQTrans.Rows[u]["EdValue"].ToString() +
"," + argBRegBo.FlatId + "," + argQTrans.Rows[u]["HEDPer"].ToString() + "," + argQTrans.Rows[u]["NetPer"].ToString() +
"," + argQTrans.Rows[u]["TaxablePer"].ToString() + "," + argQTrans.Rows[u]["TaxableValue"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
if (argQTrans.Rows.Count > 0)
{
Sql = "Insert Into dbo.ExtraBillRateQAbs(BillRegId,QualifierId,Amount,Add_Less_Flag) " +
"Values (" + iBillRegId + "," + argQTrans.Rows[0]["QualifierId"].ToString() + "," + d_TotalTaxAmt +
",'" + argQTrans.Rows[0]["Add_Less_Flag"].ToString() + "')";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
tran.Rollback();
}
finally
{
conn.Dispose();
conn.Close();
}
return(iBillRegId);
}
public int InsertExtraBill(ExtraBillRegBO argBRegBo, DataTable argBTrans, DataTable argQTrans, int argFlatId)
{
return(m_lDL.InsertExtraBill(argBRegBo, argBTrans, argQTrans, argFlatId));
}
