public void GenerateNextDSSID(mdlSaleInvoice obj_SaleInvoice)
{
try
{
GetMaxDSSID(obj_SaleInvoice);
SqlCommand cmd = new SqlCommand("stp_ERP_GenerateNextDSSID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pk_DSSId", SqlDbType.VarChar, 50).Value = obj_SaleInvoice.DSSID;
cmd.Parameters.Add("@DSSDate", SqlDbType.DateTime).Value = obj_SaleInvoice.WorkDate;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 200).Value = obj_SaleInvoice.SummaryMode;
cmd.Parameters.Add("@OCGroupID", SqlDbType.VarChar, 50).Value = obj_SaleInvoice.OCGroupID;
cmd.Parameters.Add("@DelManID", SqlDbType.VarChar, 50).Value = obj_SaleInvoice.SalesManID;
cmd.Parameters.Add("@AddDate", SqlDbType.DateTime).Value = obj_SaleInvoice.WorkDate;
cmd.Parameters.Add("@AddTime", SqlDbType.DateTime).Value = obj_SaleInvoice.WorkDate;
cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 50).Value = obj_SaleInvoice.UserName;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{ conn.Close(); }
}
}
public DataSet GetInvoiceDetail(mdlSaleInvoice obj_SaleInvoice)
{
DataSet functionReturnValue = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("stp_ERP_SaleInvoiceDetail_Select", conn);
cmd.CommandType = CommandType.StoredProcedure;
if (obj_SaleInvoice.pk_InvoiceId == null)
{
obj_SaleInvoice.pk_InvoiceId = "Empty";
}
cmd.Parameters.Add("@InvoiceID", SqlDbType.VarChar, 50).Value = obj_SaleInvoice.pk_InvoiceId;
SqlDataAdapter ad = new SqlDataAdapter(cmd);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
ad.Fill(functionReturnValue);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return functionReturnValue;
}
private bool SaveInvoiceDetails(mdlSaleInvoice obj_mdlSaleInvoice, DataSet ds_InvoiceDetail, SqlTransaction transaction)
{
bool functionReturnValue = false;
double Ln_GrossAmount = 0;
double Ln_Discount = 0;
double Ln_SaleTax = 0;
double Ln_AddlTax = 0;
double Ln_NetAmount = 0;
double Ln_CostAmount = 0;
double Ln_Commission = 0;
DataSet ds_Batch = new DataSet();
double Ln_HoldQty = 0;
double Ln_InvoiceQty = 0;
Int16 J = default(Int16);
bool Ln_UseAutoBonus = false;
functionReturnValue = true;
if (ds_InvoiceDetail.Tables[0].Rows.Count > 0)
{
string SQL;
SQL = "Delete From D_SaleInvoice Where InvoiceId = '" + Lc_MaxInvoiceId + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
for (int I = 0; I <= ds_InvoiceDetail.Tables[0].Rows.Count - 1; I++)
{
if (!(ds_InvoiceDetail.Tables[0].Rows[I].RowState == DataRowState.Deleted))
{
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) > 0 | Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"].ToString()) > 0)
{
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString()) != Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["FlatRate"].ToString()))
{
ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"] = 0.0;
ds_InvoiceDetail.Tables[0].Rows[I]["Commission"] = 0.0;
}
Ln_Availble_Stock = 0;
Ln_Availble_Bonus = 0;
if (ReturnAvailbleStock(ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", ""), transaction) >= (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"].ToString())))
{
//*******************************************
//ISSUE STOCK QUANTITIES
//*******************************************
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) > 0)
{
SQL = "";
Ln_HoldQty = 0;
SQL = " SELECT M_BatchBalance.ProductId, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.ExpiryDate,ManfactureDate, ";
SQL = SQL + " M_BatchBalance.CostRate, ";
SQL = SQL + " M_BatchBalance.STaxValue, ";
SQL = SQL + " (M_BatchBalance.BalanceQty - M_BatchBalance.BlockQty ) As BalanceQty ,";
SQL = SQL + " M_BatchBalance.PStatus, M_Products.Fk_GroupId, M_BatchBalance.Priority,";
SQL = SQL + " M_Products.Fk_CompanyId, M_Products.SaleTaxPer, ";
SQL = SQL + " M_Products.MaxSaleDiscPer, AutoBonus";
SQL = SQL + " FROM M_BatchBalance LEFT OUTER JOIN";
//WITH (NOLOCK)
SQL = SQL + " M_Products ON ";
SQL = SQL + " M_BatchBalance.ProductId = M_Products.pk_ProductId";
SQL = SQL + " LEFT OUTER JOIN M_Company ON";
SQL = SQL + " Left(M_BatchBalance.ProductId, 3) = M_Company.pk_CompanyId";
SQL = SQL + " WHERE (M_BatchBalance.ProductId = '" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", "") + "')";
SQL = SQL + " And (M_BatchBalance.PStatus = 'N') AND ";
SQL = SQL + " (M_BatchBalance.ExpiryDate > '" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "')";
SQL = SQL + " AND ";
SQL = SQL + " M_BatchBalance.BalanceQty - M_BatchBalance.BlockQty > 0 ";
SQL = SQL + " And StockType in('Stock','Bonus')";
SQL = SQL + " and " + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) + ">=QuantityFrom and " + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) + "<=QuantityTo";
SQL = SQL + " ORDER BY M_BatchBalance.ExpiryDate, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.Priority";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
//cmd.ExecuteNonQuery();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(ds_Batch);
if (ds_Batch.Tables[0].Rows.Count > 0)
{
Ln_InvoiceQty = Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString());
for (J = 0; J <= ds_Batch.Tables[0].Rows.Count - 1; J++)
{
if (Ln_InvoiceQty <= Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"].ToString()) & Ln_InvoiceQty > 0)
{
SQL = "";
Ln_HoldQty = Ln_InvoiceQty;
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority ='" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
else if (Ln_InvoiceQty > 0)
{
SQL = "";
Ln_HoldQty = Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"].ToString());
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority = '" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
//********************************************
//UPDATE DATA IN D_SALEINVOICE TABLE
//********************************************
if (Ln_InvoiceQty > 0)
{
SQL = "";
SQL = " Insert Into D_SaleInvoice (InvoiceId,ProductId,StockType,UnitPrice,BatchNo,ExpiryDate,ManfactureDate,STaxValue,QtyOut,DiscountPer,Discount,GrossAmount,SaleTaxPer,SaleTaxAmount,AddlTax,";
SQL = SQL + " AddlTaxPer,NetAmount,CostRate,DiscPolicy,FlatRate,ClaimableDisc,OurShare,CommPer, Commission)";
SQL = SQL + " Values(";
SQL = SQL + "'" + obj_mdlSaleInvoice.pk_InvoiceId + "',";
//SQL = SQL + "'" + Lc_MaxInvoiceId + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", "") + "',";
SQL = SQL + "'Stock',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["GroupId"].ToString() + "',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["OCGroup"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["FlatRate"].ToString()) + "',";
SQL = SQL + "'" + ds_Batch.Tables[0].Rows[J]["BatchNo"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ExpiryDate"].ToString()) + "',";
SQL = SQL + "'" + Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ManfactureDate"].ToString()) + "',";
//SQL = SQL + "'0',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()) + "',";
SQL = SQL + "'" + Ln_HoldQty + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) + "',";
//Calculating Values
double lc_Gross = 0;
lc_Gross = Ln_HoldQty * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString());
double lc_Discount = 0;
lc_Discount = lc_Gross * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) / 100;
double lc_SaleTaxAmount = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) != 100)
{
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()) > 0)
{
lc_SaleTaxAmount = (Ln_HoldQty * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()));
}
else
{
lc_SaleTaxAmount = (((lc_Gross - lc_Discount) * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxPer"].ToString())) / 100);
}
}
else
{
lc_SaleTaxAmount = 0;
}
double Lc_NetAmount = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) > 99)
{
Lc_NetAmount = 0;
}
else
{
Lc_NetAmount = lc_Gross - lc_Discount + lc_SaleTaxAmount;
}
double Lc_Commission = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) > 99)
{
Lc_Commission = (lc_Gross - lc_Discount + lc_SaleTaxAmount) * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString()) / 100;
}
else
{
Lc_Commission = (lc_Gross * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString())) / 100;
}
//SQL = SQL & "'" & lc_Discount & "',"
if (lc_Discount <= Convert.ToDouble("0.0001") && lc_Discount != Convert.ToDouble("0"))
{
SQL = SQL + "'" + 0.0001 + "',";
}
else
{
SQL = SQL + "'" + lc_Discount + "',";
}
SQL = SQL + "'" + lc_Gross + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxPer"].ToString()) + "',";
//sc_Comm.Parameters["@SaleTaxAmount"].Value = Val(.Rows[I]["Sale Tax"].ToString)
//************* Change of Sale Tax For Two Batches
SQL = SQL + "'" + lc_SaleTaxAmount + "',";
//************* Change of Sale Tax For Two Batches
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + "'" + Lc_NetAmount + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["CostRate"].ToString()) + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["DiscPolicy"].ToString() + "',";
//SQL = SQL & "'',"
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString()) + "',";
SQL = SQL + "'" + 0 + "',";
//Val(.Rows[I][""].ToString())
SQL = SQL + "'" + 0 + "',";
//SQL = SQL + "'" + 0 + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString()) + "',";
SQL = SQL + "'" + Lc_Commission + "')";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
Ln_GrossAmount = Ln_GrossAmount + lc_Gross;
Ln_Discount = Ln_Discount + lc_Discount;
Ln_SaleTax = Ln_SaleTax + lc_SaleTaxAmount;
Ln_AddlTax = Ln_AddlTax + 0;
Ln_NetAmount = Ln_NetAmount + Lc_NetAmount;
Ln_CostAmount = Ln_CostAmount + (Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["CostRate"].ToString()) * Ln_HoldQty);
Ln_Commission = Ln_Commission + Lc_Commission;
}
Ln_InvoiceQty = Ln_InvoiceQty - Ln_HoldQty;
}
}
else
{
SQL = "";
SQL = " SELECT M_BatchBalance.ProductId, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.ExpiryDate,ManfactureDate, ";
SQL = SQL + " M_BatchBalance.CostRate, ";
SQL = SQL + " M_BatchBalance.STaxValue, ";
SQL = SQL + " (M_BatchBalance.BalanceQty - M_BatchBalance.BlockQty ) As BalanceQty ,";
SQL = SQL + " M_BatchBalance.PStatus, M_Products.Fk_GroupId,M_BatchBalance.Priority, ";
SQL = SQL + " M_Products.Fk_CompanyId, M_Products.SaleTaxPer, ";
SQL = SQL + " M_Products.MaxSaleDiscPer, AutoBonus";
SQL = SQL + " FROM M_BatchBalance LEFT OUTER JOIN";
//WITH (NOLOCK)
SQL = SQL + " M_Products ON ";
SQL = SQL + " M_BatchBalance.ProductId = M_Products.pk_ProductId";
SQL = SQL + " LEFT OUTER JOIN M_Company ON";
SQL = SQL + " Left(M_BatchBalance.ProductId, 3) = M_Company.pk_CompanyId";
SQL = SQL + " WHERE (M_BatchBalance.ProductId = '" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", "") + "')";
SQL = SQL + " And (M_BatchBalance.PStatus = 'N') AND ";
SQL = SQL + " (M_BatchBalance.ExpiryDate > '" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "')";
SQL = SQL + " AND ";
SQL = SQL + " M_BatchBalance.BalanceQty - M_BatchBalance.BlockQty > 0 ";
SQL = SQL + " And StockType in('Stock','Bonus') and QuantityTo=0 ";
//SQL = SQL + " and " + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) + ">=QuantityFrom and " + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString()) + "<=QuantityTo";
SQL = SQL + " ORDER BY M_BatchBalance.ExpiryDate, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.Priority";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
ad = new SqlDataAdapter(cmd);
ad.Fill(ds_Batch);
if (ds_Batch.Tables[0].Rows.Count > 0)
{
Ln_InvoiceQty = Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Quantity"].ToString());
for (J = 0; J <= ds_Batch.Tables[0].Rows.Count - 1; J++)
{
if (Ln_InvoiceQty <= Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"].ToString()) & Ln_InvoiceQty > 0)
{
SQL = "";
Ln_HoldQty = Ln_InvoiceQty;
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority ='" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
else if (Ln_InvoiceQty > 0)
{
SQL = "";
Ln_HoldQty = Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"]);
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority = '" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
//********************************************
//UPDATE DATA IN D_SALEINVOICE TABLE
//********************************************
if (Ln_InvoiceQty > 0)
{
SQL = "";
SQL = " Insert Into D_SaleInvoice (InvoiceId,ProductId,StockType,UnitPrice,BatchNo,ExpiryDate,ManfactureDate,STaxValue,QtyOut,DiscountPer,Discount,GrossAmount,SaleTaxPer,SaleTaxAmount,AddlTax,";
SQL = SQL + " AddlTaxPer,NetAmount,CostRate,DiscPolicy,FlatRate,ClaimableDisc,OurShare,CommPer, Commission)";
SQL = SQL + " Values(";
SQL = SQL + "'" + obj_mdlSaleInvoice.pk_InvoiceId + "',";
//SQL = SQL + "'" + Lc_MaxInvoiceId + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", "") + "',";
SQL = SQL + "'Stock',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["GroupId"].ToString() + "',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["OCGroup"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["FlatRate"].ToString()) + "',";
SQL = SQL + "'" + ds_Batch.Tables[0].Rows[J]["BatchNo"].ToString() + "',";
SQL = SQL + "'" + (Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ExpiryDate"].ToString())) + "',";
SQL = SQL + "'" + (Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ManfactureDate"].ToString())) + "',";
//SQL = SQL + "'0',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()) + "',";
SQL = SQL + "'" + Ln_HoldQty + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) + "',";
//Calculating Values
double lc_Gross = 0;
lc_Gross = Ln_HoldQty * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString());
double lc_Discount = 0;
lc_Discount = lc_Gross * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) / 100;
double lc_SaleTaxAmount = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) != 100)
{
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()) > 0)
{
lc_SaleTaxAmount = Ln_HoldQty * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString());
}
else
{
lc_SaleTaxAmount = ((lc_Gross - lc_Discount) * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxPer"].ToString())) / 100;
}
}
else
{
lc_SaleTaxAmount = 0;
}
double Lc_NetAmount = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) > 99)
{
Lc_NetAmount = 0;
}
else
{
Lc_NetAmount = lc_Gross - lc_Discount + lc_SaleTaxAmount;
}
double Lc_Commission = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) > 99)
{
Lc_Commission = (lc_Gross - lc_Discount + lc_SaleTaxAmount) * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString()) / 100;
}
else
{
Lc_Commission = (lc_Gross * Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString())) / 100;
}
//SQL = SQL & "'" & lc_Discount & "',"
if (lc_Discount <= Convert.ToDouble("0.0001") && lc_Discount != Convert.ToDouble("0"))
{
SQL = SQL + "'" + 0.0001 + "',";
}
else
{
SQL = SQL + "'" + lc_Discount + "',";
}
SQL = SQL + "'" + lc_Gross + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxPer"].ToString()) + "',";
//sc_Comm.Parameters["@SaleTaxAmount"].Value = Val(.Rows[I]["Sale Tax"].ToString)
//************* Change of Sale Tax For Two Batches
SQL = SQL + "'" + lc_SaleTaxAmount + "',";
//************* Change of Sale Tax For Two Batches
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + "'" + Lc_NetAmount + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["CostRate"].ToString()) + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["DiscPolicy"].ToString() + "',";
//SQL = SQL & "'',"
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString()) + "',";
SQL = SQL + "'" + 0 + "',";
SQL = SQL + "'" + 0 + "',";
//SQL = SQL + "'" + 0 + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["CommPer"].ToString()) + "',";
SQL = SQL + "'" + Lc_Commission + "')";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
Ln_GrossAmount = Ln_GrossAmount + lc_Gross;
Ln_Discount = Ln_Discount + lc_Discount;
Ln_SaleTax = Ln_SaleTax + lc_SaleTaxAmount;
Ln_AddlTax = Ln_AddlTax + 0;
Ln_NetAmount = Ln_NetAmount + Lc_NetAmount;
Ln_CostAmount = Ln_CostAmount + (Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["CostRate"].ToString()) * Ln_HoldQty);
Ln_Commission = Ln_Commission + Lc_Commission;
}
Ln_InvoiceQty = Ln_InvoiceQty - Ln_HoldQty;
}
}
}
}
//End If
Ln_HoldQty = 0;
Ln_UseAutoBonus = false;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"].ToString()) > Ln_Availble_Bonus)
{
Ln_UseAutoBonus = true;
if (ds_InvoiceDetail.Tables[0].Rows[I]["bk_SaleTaxValue"].ToString() == "False")
{
ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"] = 0;
}
}
else
{
Ln_UseAutoBonus = false;
}
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"].ToString()) > 0)
{
SQL = "";
SQL = " SELECT M_BatchBalance.ProductId, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.ExpiryDate,ManfactureDate, ";
SQL = SQL + " M_BatchBalance.UnitPrice, ";
SQL = SQL + " M_BatchBalance.STaxValue, ";
SQL = SQL + " M_BatchBalance.Bonus, ";
SQL = SQL + " (M_BatchBalance.BalanceQty - M_BatchBalance.HoldQty - M_BatchBalance.OrderQty - M_BatchBalance.BlockQty) As BalanceQty ,";
SQL = SQL + " M_BatchBalance.CostRate, ";
SQL = SQL + " M_BatchBalance.Priority, ";
SQL = SQL + " M_BatchBalance.PStatus, M_Products.GroupId, ";
SQL = SQL + " M_Products.OCGroup, M_Products.SaleTaxPer, ";
SQL = SQL + " M_Products.MaxSaleDisc, AutoBonus";
SQL = SQL + " FROM M_BatchBalance LEFT OUTER JOIN";
//WITH (NOLOCK)
SQL = SQL + " M_Products ON ";
SQL = SQL + " M_BatchBalance.ProductId = M_Products.pk_ProductId";
SQL = SQL + " LEFT OUTER JOIN M_Company ON";
SQL = SQL + " Left(M_BatchBalance.ProductId, 2) = M_Company.pk_CompanyId";
SQL = SQL + " WHERE (M_BatchBalance.ProductId = '" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString().Replace("-", "") + "')";
SQL = SQL + " And (M_BatchBalance.PStatus = 'N') AND ";
SQL = SQL + " (M_BatchBalance.ExpiryDate > '" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "')";
SQL = SQL + " AND ";
SQL = SQL + " (M_BatchBalance.BalanceQty - M_BatchBalance.HoldQty";
SQL = SQL + " - M_BatchBalance.OrderQty - M_BatchBalance.BlockQty";
SQL = SQL + " > 0) AND (M_BatchBalance.Bonus = '" + (Ln_UseAutoBonus ? 0 : 1) + "')";
SQL = SQL + " And StockType in('Stock','Bonus')";
SQL = SQL + " ORDER BY M_BatchBalance.ExpiryDate, ";
SQL = SQL + " M_BatchBalance.BatchNo, ";
SQL = SQL + " M_BatchBalance.Priority";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(ds_Batch);
if (ds_Batch.Tables[0].Rows.Count > 0)
{
Ln_InvoiceQty = Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Bonus"].ToString());
for (J = 0; J <= ds_Batch.Tables[0].Rows.Count - 1; J++)
{
if (Ln_InvoiceQty <= Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"]) && Ln_InvoiceQty > 0)
{
Ln_HoldQty = Ln_InvoiceQty;
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority ='" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
else if (Ln_InvoiceQty > 0)
{
Ln_HoldQty = Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["BalanceQty"]);
SQL = " Update M_BatchBalance Set ";
SQL = SQL + " BalanceQty = BalanceQty - '" + Ln_HoldQty + "'";
SQL = SQL + " Where";
SQL = SQL + " Priority = '" + ds_Batch.Tables[0].Rows[J]["Priority"] + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
//********************************************
//UPDATE DATA IN D_SALEINVOICE TABLE
//********************************************
if (Ln_InvoiceQty > 0)
{
SQL = " Insert Into D_SaleInvoice (InvoiceId,ProductId,StockType,UnitPrice,BatchNo,ExpiryDate,ManfactureDate,STaxValue,QtyOut,DiscountPer,Discount,GrossAmount,SaleTaxPer,SaleTaxAmount,AddlTax,";
SQL = SQL + " AddlTaxPer,NetAmount,CostRate,DiscPolicy,FlatRate,ClaimableDisc,OurShare,CommPer, Commission)";
SQL = SQL + " Values(";
SQL = SQL + "'" + obj_mdlSaleInvoice.pk_InvoiceId + "',";
//SQL = SQL + "'" + Lc_MaxInvoiceId + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString() + "',";
SQL = SQL + "'Bonus',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["GroupId"].ToString() + "',";
//SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["OCGroup"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["FlatRate"].ToString()) + "',";
SQL = SQL + "'" + ds_Batch.Tables[0].Rows[J]["BatchNo"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ExpiryDate"].ToString()) + "',";
SQL = SQL + "'" + Convert.ToDateTime(ds_Batch.Tables[0].Rows[J]["ManfactureDate"].ToString()) + "',";
//SQL = SQL + "'1',";
SQL = SQL + "'0',";
SQL = SQL + "'" + Ln_HoldQty + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) + "',";
//Calculating Values
double lc_Gross = 0;
lc_Gross = 0;
double lc_Discount = 0;
lc_Discount = 0;
double lc_SaleTaxAmount = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["SaleTaxVal"].ToString()) > 0)
{
lc_SaleTaxAmount = 0;
}
else
{
lc_SaleTaxAmount = 0;
}
double Lc_NetAmount = 0;
Lc_NetAmount = 0;
double Lc_Commission = 0;
if (Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["Disc%"].ToString()) > 99)
{
Lc_Commission = 0;
}
else
{
Lc_Commission = 0;
}
//*********************
SQL = SQL + "'" + lc_Discount + "',";
SQL = SQL + "'" + lc_Gross + "',";
SQL = SQL + "'0',";
//sc_Comm.Parameters["@SaleTaxAmount"].Value = Val(.Rows[I]["Sale Tax"].ToString)
//************* Change of Sale Tax For Two Batches
SQL = SQL + "'" + lc_SaleTaxAmount + "',";
//************* Change of Sale Tax For Two Batches
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + "'" + Lc_NetAmount + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_Batch.Tables[0].Rows[J]["CostRate"].ToString()) + "',";
SQL = SQL + "'" + ds_InvoiceDetail.Tables[0].Rows[I]["DiscPolicy"].ToString() + "',";
SQL = SQL + "'" + Convert.ToDouble(ds_InvoiceDetail.Tables[0].Rows[I]["T.P."].ToString()) + "',";
SQL = SQL + "'" + 0 + "',";
//Val(.Rows[I][""].ToString())
SQL = SQL + "'" + 0 + "',";
//SQL = SQL + "'" + (Ln_UseAutoBonus ? 1 : 0) + "',";
SQL = SQL + "'0',";
SQL = SQL + "'" + Lc_Commission + "')";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
}
Ln_InvoiceQty = Ln_InvoiceQty - Ln_HoldQty;
}
}
}
//*******************************************
//ISSUE STOCK QUANTITIES
//*******************************************
//*******************************************
//ISSUE BONUS QUANTITIES
//*******************************************
//*******************************************
//ISSUE BONUS QUANTITIES
//*******************************************
obj_mdlSaleInvoice.NetAmount = Ln_NetAmount.ToString();
obj_mdlSaleInvoice.GrossAmount = Ln_GrossAmount.ToString();
obj_mdlSaleInvoice.Discount = Ln_Discount.ToString();
obj_mdlSaleInvoice.SaleTax = Ln_SaleTax.ToString();
obj_mdlSaleInvoice.Commission = Ln_Commission.ToString();
functionReturnValue = true;
}
else
{
MessageBox.Show("Not enough stock is availble for" + ds_InvoiceDetail.Tables[0].Rows[I]["Product"].ToString() + "-" + ds_InvoiceDetail.Tables[0].Rows[I]["Product Name"].ToString(), "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Information);
functionReturnValue = false;
return functionReturnValue;
}
}
}
}
SQL = "Update M_SaleInvoice Set ";
SQL = SQL + " GrossAmount = " + Ln_GrossAmount + ",";
SQL = SQL + " Discount = " + Ln_Discount + ",";
SQL = SQL + " SaleTax = " + Ln_SaleTax + ",";
SQL = SQL + " AddlTax = " + Ln_AddlTax + ",";
SQL = SQL + " NetAmount = " + Ln_NetAmount + ",";
SQL = SQL + " Commission = " + Ln_Commission + ",";
SQL = SQL + " Credit = 0.00";
SQL = SQL + " Where pk_InvoiceId = '" + Lc_MaxInvoiceId + "'";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
//**************** Post DSS Inv Entries
functionReturnValue = SaveDSSInv(obj_mdlSaleInvoice, transaction);
//**************** Post GENERAL LEDGER ENTRIES
functionReturnValue = PostSaleInvoice(Ln_CostAmount);
//**************** CLOSE WITH OUT SUMMARY DSS
//If m_SaleInvoice["SummaryMode"] = "Without Summary" Then
// SaveInvoiceDetails = CloseDss(m_SaleInvoice["DSSId"], m_SaleInvoice["DelManId"])
//End If
}
else
{
functionReturnValue = false;
}
cmd.Cancel();
cmd.Dispose();
return functionReturnValue;
}
public DataSet FindSaleInvoice(mdlSaleInvoice obj_SaleInvoice, string InvoiceID)
{
DataSet functionReturnValue = new DataSet();
SqlCommand cmd = new SqlCommand("Select *,CustomerName,BrickName,TerritoryName,M_BMC_Employee.FirstName +' ' + M_BMC_Employee.LastName As EmpName,PatientName From M_SaleInvoice WITH (NOLOCK) left outer JOIN M_Customers ON M_Customers.pk_CustomerId=M_SaleInvoice.CustomerId left outer JOIN M_Bricks ON M_Bricks.pk_Brickid=M_SaleInvoice.BrickId left outer JOIN M_Territory ON M_Territory.pk_TerritoryId=M_SaleInvoice.TerritoryId left outer JOIN M_BMC_Employee ON M_BMC_Employee.pk_EmployeeId=M_SaleInvoice.EmployeeId left outer JOIN M_Patients ON M_Patients.pk_PatientId=M_SaleInvoice.PatientId where pk_InvoiceId = '" + InvoiceID + "'", conn);
cmd.CommandType = CommandType.Text;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
//dr_Reader = cmd.ExecuteReader();
ad.Fill(functionReturnValue);
if (functionReturnValue.Tables[0].Rows.Count > 0)
{
obj_SaleInvoice.pk_InvoiceId = functionReturnValue.Tables[0].Rows[0]["pk_InvoiceId"].ToString();
obj_SaleInvoice.DSSID = functionReturnValue.Tables[0].Rows[0]["DSSId"].ToString();
obj_SaleInvoice.BrickId = functionReturnValue.Tables[0].Rows[0]["BrickId"].ToString();
obj_SaleInvoice.BrickName = functionReturnValue.Tables[0].Rows[0]["BrickName"].ToString();
obj_SaleInvoice.TerritoryId = functionReturnValue.Tables[0].Rows[0]["TerritoryId"].ToString();
obj_SaleInvoice.TerritoryName = functionReturnValue.Tables[0].Rows[0]["TerritoryName"].ToString();
obj_SaleInvoice.Claimable = Convert.ToBoolean(functionReturnValue.Tables[0].Rows[0]["Claimable"].ToString());
obj_SaleInvoice.WOS = Convert.ToBoolean(functionReturnValue.Tables[0].Rows[0]["WOS"].ToString());
obj_SaleInvoice.Salebase = Convert.ToBoolean(functionReturnValue.Tables[0].Rows[0]["Salebase"].ToString());
obj_SaleInvoice.OCGroupID = functionReturnValue.Tables[0].Rows[0]["OCGroupId"].ToString();
obj_SaleInvoice.InvoiceDate = Convert.ToDateTime(functionReturnValue.Tables[0].Rows[0]["InvoiceDate"].ToString());
obj_SaleInvoice.CustomerId = functionReturnValue.Tables[0].Rows[0]["CustomerId"].ToString();
obj_SaleInvoice.CustomerName = functionReturnValue.Tables[0].Rows[0]["CustomerName"].ToString();
obj_SaleInvoice.EmployeeID = functionReturnValue.Tables[0].Rows[0]["EmployeeId"].ToString();
obj_SaleInvoice.EmployeeName = functionReturnValue.Tables[0].Rows[0]["EmpName"].ToString();
obj_SaleInvoice.SalesManID = functionReturnValue.Tables[0].Rows[0]["SalesManId"].ToString();
obj_SaleInvoice.SalesManName = functionReturnValue.Tables[0].Rows[0]["EmpName"].ToString();
obj_SaleInvoice.PatientId = functionReturnValue.Tables[0].Rows[0]["PatientId"].ToString();
obj_SaleInvoice.PatientName = functionReturnValue.Tables[0].Rows[0]["PatientName"].ToString();
obj_SaleInvoice.DueDate = Convert.ToDateTime(functionReturnValue.Tables[0].Rows[0]["DueDate"].ToString());
obj_SaleInvoice.GrossAmount = functionReturnValue.Tables[0].Rows[0]["GrossAmount"].ToString();
obj_SaleInvoice.Discount = functionReturnValue.Tables[0].Rows[0]["Discount"].ToString();
obj_SaleInvoice.SaleTax = functionReturnValue.Tables[0].Rows[0]["SaleTax"].ToString();
obj_SaleInvoice.AddlTax = functionReturnValue.Tables[0].Rows[0]["AddlTax"].ToString();
obj_SaleInvoice.NetAmount = functionReturnValue.Tables[0].Rows[0]["NetAmount"].ToString();
obj_SaleInvoice.Remarks = functionReturnValue.Tables[0].Rows[0]["Remarks"].ToString();
obj_SaleInvoice.RefNo = functionReturnValue.Tables[0].Rows[0]["RefNo"].ToString();
obj_SaleInvoice.RefDate = Convert.ToDateTime(functionReturnValue.Tables[0].Rows[0]["RefDate"].ToString());
obj_SaleInvoice.InvoiceType = functionReturnValue.Tables[0].Rows[0]["InvoiceType"].ToString();
ds_LoadInvoiceDetail = LoadInvoiceDetail(obj_SaleInvoice.pk_InvoiceId);
}
return ds_LoadInvoiceDetail;
}
public bool SaveSaleOrderInvoices(mdlSaleInvoice obj_mdlSaleInvoice, DataSet p_DataSet, bool p_LocalOrders, bool p_mergorder, string p_WorkDate)
{
bool functionReturnValue = false;
Int16 I = default(Int16);
Int16 J = default(Int16);
Int16 k = default(Int16);
double Ln_DiscPer = 0;
double Ln_GrossAmountCompany = 0;
double Ln_Discount = 0;
double Ln_SaleTax = 0;
double Ln_GrsAmount = 0;
string p_ProductPrice = null;
string p_Stock = null;
string p_Bonus = null;
string Ocgroup = null;
string CheckProduct = null;
//LoadSystemDefaults();
functionReturnValue = false;
SqlTransaction transaction = null;
try
{
for (I = 0; I <= p_DataSet.Tables[0].Rows.Count - 1; I++)
{
if (p_DataSet.Tables[0].Rows[I][" "].ToString() == "True")
{
//**********Customer Checking
ReturnKeyName(p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), "Customers", p_WorkDate);
//***********************Licence Verification
//********Active Customer Verification
if (obj_mdlCustomer.IsDiscontinue == true)
{
continue;
}
//*****Customers Verification
if (obj_mdlCustomer.Category == "DOCTOR" || obj_mdlCustomer.Category == "HOSPITAL")
{
}
else
{
if (string.IsNullOrEmpty(GetSaleMan(p_DataSet.Tables[0].Rows[I]["TerritoryCode"].ToString(), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString())))
{
continue;
}
}
//******************Product Entry
ds_SaleOrderInvoiceDetail.Clear();
ds_SaleOrderInvoiceDetail = GetSaleOrderInvoiceDetail(p_DataSet.Tables[0].Rows[I]["DeviceCode"].ToString(), p_DataSet.Tables[0].Rows[I]["Order ID"].ToString(), p_LocalOrders, p_DataSet.Tables[0].Rows[I]["EmployeeCode"].ToString(), p_DataSet.Tables[0].Rows[I]["TerritoryCode"].ToString(), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_mergorder, p_DataSet.Tables[0].Rows[I]["Order Date"].ToString());
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows.Count <= 0)
{
continue;
}
for (J = 0; J <= ds_SaleOrderInvoiceDetail.Tables[0].Rows.Count - 1; J++)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Process"] = true;
//**********For Product Change
if (ReturnKeyName(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), "CheckProductAuto", p_WorkDate) == "True")
{
CheckProduct = ReturnKeyName(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), "CheckProduct", p_WorkDate);
if (!string.IsNullOrEmpty(CheckProduct))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"] = CheckProduct;
}
}
else
{
if (Convert.ToBoolean(p_DataSet.Tables[0].Rows[I]["S.Stock"]) == false)
{
}
else
{
CheckProduct = ReturnKeyName(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), "CheckProduct", p_WorkDate);
if (!string.IsNullOrEmpty(CheckProduct))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"] = CheckProduct;
}
}
}
//****************************
// '''*******NEW ROW ADDITION
//Dim newCustomersRow As DataRow = ds_SaleOrderInvoiceDetail.Tables("Customers"].NewRow()
//newCustomersRow("CustomerID"] = "ALFKI"
//newCustomersRow("CompanyName"] = "Alfreds Futterkiste"
//ds_SaleOrderInvoiceDetail.Tables("Customers"].Rows.Add(newCustomersRow)
//************For Brick Verification
//If Not VerifyBrickID(Format(Val(Strings.Left(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), 2)), "00"], p_DataSet.Tables[0].Rows[I]["Brick ID"].ToString()) Then
// Continue For
//End If
//***********************
//****************For Gift Entry
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString() == "08850")
{
if (!string.IsNullOrEmpty(GetGift(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), "Gift")))
{
continue;
}
}
//**********************
//**************Norcotic Product Checking
ReturnKeyName(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), "Product", p_WorkDate);
if (obj_mdlProduct.Narcotic == true)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"] = 0;
continue;
}
//****************************
//************Stock Checking
//getStockandBonus(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), "ProductStock");
p_Stock = obj_mdlProduct.BalanceStock;
p_Bonus = obj_mdlProduct.AutoBonus.ToString();
if (Convert.ToDouble(p_Stock) == 0)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"] = 0;
continue;
}
//**************************
//************************Product Information Update
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["UseFlatRate"] = (obj_mdlProduct.UseFlatRate);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"] = (obj_mdlProduct.TradePrice);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["FlatRate"] = (obj_mdlProduct.TradePrice);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MaxSaleDisc"] = (obj_mdlProduct.MaxSaleDiscPer);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MinFlatRate"] = (obj_mdlProduct.MinFlatRate);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MaxFlatRate"] = (obj_mdlProduct.MaxFlatRate);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SpecialRate"] = (obj_mdlProduct.SpecialRate);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Ins"] = "Ins";
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Narcotic"] = (obj_mdlProduct.Narcotic);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MaxQtyDay"] = (obj_mdlProduct.MaxQtyDays);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MaxQty"] = (obj_mdlProduct.MaxQtyDay);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["MaxDays"] = (obj_mdlProduct.MaxQtyDays);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxPer"] = (obj_mdlProduct.SaleTaxPer);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Sale Tax"] = (obj_mdlProduct.SaleTaxValue);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["OCGroup"] = (obj_mdlProduct.Fk_GroupId);
Ocgroup = (obj_mdlProduct.Fk_GroupId);
if (Ocgroup == "000010")
{
Ocgroup = "000002";
}
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["GroupId"] = (obj_mdlProduct.Fk_GroupId);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["bk_BatchNo"] = true;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["bk_ExpiryDate"] = Convert.ToDateTime("01-01-2000");
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["bk_TradePrice"] = obj_mdlProduct.TradePrice;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["bk_Bonus"] = obj_mdlProduct.AutoBonus;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["bk_SaleTaxValue"] = obj_mdlProduct.SaleTaxValue;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscShare"] = "0";
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["AutoBonus"] = obj_mdlProduct.AutoBonus;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["AllowCutRate"] = obj_mdlProduct.AllowHundredPer;
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["CommPer"] = "0";
//******************************************************
//************Price Checking
p_ProductPrice = GetPrice(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_WorkDate);
if (!string.IsNullOrEmpty(p_ProductPrice))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"] = Convert.ToDouble(p_ProductPrice);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"] = "(SP)-" + ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"].ToString().Replace("(SP)-", "");
}
else if (string.IsNullOrEmpty(p_ProductPrice) & Convert.ToDouble(obj_mdlProduct.SpecialRate) != 0)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"] = Convert.ToDouble(obj_mdlProduct.SpecialRate);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"] = "(SP)-" + ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"].ToString().Replace("(SP)-", "");
}
else
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"] = Convert.ToDouble(obj_mdlProduct.TradePrice);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"] = (obj_mdlProduct.ProductName);
}
//**************Stock Issuance
//*** order Quantity checking
if (Convert.ToDouble(p_Stock) < Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"] = 0;
continue;
}
//*** Max Qty and Quantity check
if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]) > Convert.ToDouble(obj_mdlProduct.MaxQtyPerCustomer))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"] = 0;
continue;
}
//*** Customer Bonus Qty check
///''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product Name"].ToString().Contains("(SP)-") == false)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Bonus"] = ReturnCustomerBonusQty(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString()), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_WorkDate);
//*** Bonus Qty check
if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Bonus"]) == 0)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Bonus"] = ReturnBonusQty(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]), "Bonus", p_WorkDate);
}
//' ''********Customers Product discount policy
Ln_GrossAmountCompany = ReturnGrossAmountForCompany(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString().Remove(3));
if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"]) != Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["FlatRate"]))
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"] = "0.000";
}
else
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"] = CalculateDiscPercent(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString()), Ln_GrossAmountCompany, ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPolicy"].ToString(), p_WorkDate);
}
//***********Missing Code
//*************Start of change 2 for Rate and Disc
}
else
{
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString() == "31209")
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Bonus"] = ReturnCustomerBonusQty(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString(), Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_WorkDate);
//.Columns("Bonus"].Tag = .Columns("Bonus"].Text
Ln_GrossAmountCompany = ReturnGrossAmountForCompany(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString().Remove(3));
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"] = "0.000";
}
else
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Bonus"] = 0;
Ln_GrossAmountCompany = ReturnGrossAmountForCompany(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Product ID"].ToString().Remove(3));
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"] = "0.000";
}
}
//*******************End of change 2 for Rate and Disc
//************Discount Calculation
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Discount"] = ReturnGridCalculation("Discount", ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Sale Tax"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"].ToString(), "0");
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxVal"] = ReturnGridCalculation("SaleTax", ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Sale Tax"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"].ToString(), "0");
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Total"] = ReturnGridCalculation("Total", ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Sale Tax"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"].ToString(), "0");
ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Commission"] = ReturnGridCalculation("Commission", ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["SaleTaxPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Sale Tax"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Unit Price"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["DiscPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["CommPer"].ToString());
for (k = 0; k <= ds_SaleOrderInvoiceDetail.Tables[0].Rows.Count - 1; k++)
{
if (Convert.ToBoolean(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Process"]) == true && Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]) > 0)
{
string Lc_CompId = null;
Ln_GrossAmountCompany = ReturnGrossAmountForCompany(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product ID"].ToString().Remove(4));
Lc_CompId = ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product ID"].ToString().Remove(4);
if (Lc_CompId == ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product ID"].ToString().Remove(4))
{
Ln_DiscPer = CalculateDiscPer(Lc_CompId, Ln_GrossAmountCompany, p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_WorkDate);
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"].ToString() == "CPP" && ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"].ToString() == "0")
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"] = "COMP";
}
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product ID"].ToString().Remove(4) == Lc_CompId && ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"].ToString() != "CPP" && ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"].ToString() != "CRV")
{
if (Ln_DiscPer > Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["MaxSaleDisc"]))
{
Ln_DiscPer = Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["MaxSaleDisc"]);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"] = "COMP";
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product Name"].ToString().Contains("(SP)-") == false)
{
//'*********Company Discount policy
if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Unit Price"]) != Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["FlatRate"]))
{
//ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product Name"] = "(SP)-" & Replace(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product Name"], "(SP)-", ""]
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"] = "0.000";
}
else
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"] = string.Format("{0:0.0000}", Ln_DiscPer);
}
//******************
}
}
if (Ln_DiscPer != 0)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPolicy"] = "COMP";
if (ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Product Name"].ToString().Contains("(SP)-") == false)
{
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"] = string.Format("{0:0.0000}", Ln_DiscPer);
}
}
}
//***************
Ln_GrsAmount = (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Quantity"]) * Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Unit Price"]));
Ln_Discount = Convert.ToDouble(string.Format("{0:0.0000}", Convert.ToString((Ln_GrsAmount * Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"].ToString())) / 100)));
if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["SaleTaxPer"]) != 0)
{
Ln_SaleTax = Convert.ToDouble(string.Format("{0:0.0000}", (Ln_GrsAmount - Ln_Discount) * Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["SaleTaxPer"]) / 100));
}
else if (Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Sale Tax"]) != 0)
{
Ln_SaleTax = Convert.ToDouble(string.Format("{0:0.0000}", Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Quantity"]) * Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Sale Tax"])));
}
else
{
Ln_SaleTax = 0;
}
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Discount"] = string.Format("{0:0.0000}", Ln_Discount);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["SaleTaxVal"] = string.Format("0:{0.0000}", Ln_SaleTax);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Total"] = string.Format("0:{0.0000}", Ln_GrsAmount - Ln_Discount + Ln_SaleTax);
ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Commission"] = string.Format("0:{0.0000}", ReturnGridCalculation("Commission", ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["SaleTaxPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Sale Tax"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Quantity"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["Unit Price"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["DiscPer"].ToString(), ds_SaleOrderInvoiceDetail.Tables[0].Rows[k]["CommPer"].ToString()));
}
}
}
if (Convert.ToDouble(p_Stock) > Convert.ToDouble(ds_SaleOrderInvoiceDetail.Tables[0].Rows[J]["Quantity"]))
{
p_Bonus = "0";
}
}
//*************Discount Policy
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
transaction = conn.BeginTransaction();
p_DataSet.Tables[0].Rows[I]["OCGroupID"] = Ocgroup;
if (!string.IsNullOrEmpty(Ocgroup))
{
string SQL = "";
//***** Get Next Invoice ID
GetMaxInvoiceID(p_WorkDate);
//***** Get Next DSS ID
obj_mdlSaleInvoice.OCGroupID = p_DataSet.Tables[0].Rows[I]["OCGroupID"].ToString();
obj_mdlSaleInvoice.TerritoryId = p_DataSet.Tables[0].Rows[I]["TerritoryCode"].ToString();
obj_mdlSaleInvoice.EmployeeID = p_DataSet.Tables[0].Rows[I]["EmployeeCode"].ToString();
GenerateNextDSSID(obj_mdlSaleInvoice);
string Lc_CurrDSSId = obj_mdlSaleInvoice.DSSID;
SQL = " Insert Into M_SaleInvoice (pk_InvoiceId,InvoiceDate,DSSId,CustomerId,BrickId,BookingManId,SalesManId,DelManId,PatientId,DueDate,Claimable,SaleBase,OCGroupId,InvoiceType,Status,GrossAmount,Discount,SaleTax,AddlTax,";
SQL = SQL + " NetAmount,Credit,ReturnAmount,Paid,Remarks,AddUser,AddDate,AddTime,WOS,RefNo,RefDate)";
SQL = SQL + " Values(";
SQL = SQL + "'" + Lc_MaxInvoiceId + "',";
SQL = SQL + "'" + Convert.ToDateTime(p_WorkDate) + "',";
SQL = SQL + "'" + Lc_CurrDSSId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.CustomerId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.BrickId + "',";
SQL = SQL + "'" + p_DataSet.Tables[0].Rows[I]["TerritoryCode"] + "',";
SQL = SQL + "'" + p_DataSet.Tables[0].Rows[I]["EmployeeCode"] + "',";
SQL = SQL + "'" + p_DataSet.Tables[0].Rows[I]["TerritoryCode"] + "',";
SQL = SQL + " '',";
DateTime dtvalue;
if (DateTime.TryParse(p_WorkDate, out dtvalue))
{
SQL = SQL + "'" + Convert.ToDateTime(p_WorkDate) + "',";
}
else
{
SQL = SQL + "'" + Convert.ToDateTime("01/01/1900") + "',";
}
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'" + p_DataSet.Tables[0].Rows[I]["OCGroupID"] + "',";
SQL = SQL + "'D',";
SQL = SQL + "'" + "Un-Posted" + "',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + "'Remarks',";
SQL = SQL + "'" + obj_mdlSaleInvoice.UserName + "',";
SQL = SQL + "'" + Convert.ToDateTime(p_WorkDate) + "',";
SQL = SQL + "'" + Convert.ToDateTime(p_WorkDate) + "',";
SQL = SQL + "'0',";
SQL = SQL + "'Ref No',";
if (DateTime.TryParse(p_DataSet.Tables[0].Rows[I]["Order Date"].ToString(), out dtvalue))
{
SQL = SQL + "'" + Convert.ToDateTime(p_DataSet.Tables[0].Rows[I]["Order Date"]) + "')";
}
else
{
SQL = SQL + "'" + Convert.ToDateTime("01/01/1900") + "')";
}
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
if (cmd.ExecuteNonQuery() != 0)
{
if (SaveInvoiceDetails(obj_mdlSaleInvoice, ds_SaleOrderInvoiceDetail, transaction) == true)
{
if (UpdateSystemDefaults() & UpdateSaleOrdervalue(p_DataSet.Tables[0].Rows[I]["Order ID"].ToString(), p_LocalOrders, p_DataSet.Tables[0].Rows[I]["EmployeeCode"].ToString(), p_DataSet.Tables[0].Rows[I]["TerritoryCode"].ToString(), p_DataSet.Tables[0].Rows[I]["DeviceCode"].ToString(), p_DataSet.Tables[0].Rows[I]["Customer ID"].ToString(), p_mergorder, p_DataSet.Tables[0].Rows[I]["Order Date"].ToString()))
{
transaction.Commit();
//LoadSystemDefaults();
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
}
else
{
transaction.Rollback();
}
cmd.Cancel();
cmd.Dispose();
}
else
{
transaction.Commit();
cmd.Cancel();
cmd.Dispose();
}
if (dr_Reader.IsClosed == false)
{ dr_Reader.Close(); }
cmd.Cancel();
cmd.Dispose();
}
}
functionReturnValue = true;
}
catch (Exception ex)
{
transaction.Rollback();
functionReturnValue = false;
MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return functionReturnValue;
}
private bool SaveDSSInv(mdlSaleInvoice obj_mdlSaleInvoice, SqlTransaction transaction)
{
bool functionReturnValue = false;
functionReturnValue = false;
string SQL;
SQL = "Delete From D_DSSInv Where InvoiceId = '" + Lc_MaxInvoiceId + "' And CreditInv = 0";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
SQL = "";
SQL = " Insert Into D_DSSInv (DSSId,InvoiceId,CustomerId,GrossAmount,Discount,SaleTax,Amount,Credit,ReturnAmount,Paid,CreditInv,TerritoryId,EmployeeId,AddUser,AddDate,AddTime,Commission)";
SQL = SQL + " Values(";
SQL = SQL + "'" + obj_mdlSaleInvoice.DSSID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.pk_InvoiceId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.CustomerId + "',";
//SQL = SQL + "'" + obj_mdlSaleInvoice.BrickId + "',";
//SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.InvoiceDate) + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.GrossAmount + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.Discount + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.SaleTax + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.NetAmount + "',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'0',";
SQL = SQL + "'" + obj_mdlSaleInvoice.TerritoryId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.SalesManID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.UserName + "',";
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "',";
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "',";
//SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.DueDate) + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.Commission + "')";
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn.Open();
cmd.ExecuteNonQuery();
functionReturnValue = true;
return functionReturnValue;
}
public bool ModifySaleInvoice(mdlSaleInvoice obj_mdlSaleInvoice, DataSet ds_InvoiceDetail)
{
bool functionReturnValue = false;
SqlTransaction transaction;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
transaction = conn.BeginTransaction();
try
{
cmd = new SqlCommand("stp_ERP_M_SaleInvoice_UPDATE", conn, transaction);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("@pk_InvoiceId", SqlDbType.VarChar, 9).Value = obj_mdlSaleInvoice.pk_InvoiceId;
cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = Convert.ToDateTime(obj_mdlSaleInvoice.InvoiceDate);
cmd.Parameters.Add("@DSSId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.DSSID;
cmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.CustomerId;
cmd.Parameters.Add("@BrickId", SqlDbType.VarChar, 4).Value = obj_mdlSaleInvoice.BrickId;
cmd.Parameters.Add("@SalesManId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.SalesManID;
cmd.Parameters.Add("@DelManId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.TerritoryId;
cmd.Parameters.Add("@PatientId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.PatientId;
cmd.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = Convert.ToDateTime(obj_mdlSaleInvoice.DueDate);
cmd.Parameters.Add("@Claimable", SqlDbType.Bit).Value = obj_mdlSaleInvoice.Claimable;
cmd.Parameters.Add("@SaleBase", SqlDbType.Bit).Value = obj_mdlSaleInvoice.Salebase;
cmd.Parameters.Add("@WOS", SqlDbType.Bit).Value = obj_mdlSaleInvoice.WOS;
cmd.Parameters.Add("@OCGroupId", SqlDbType.VarChar, 6).Value = obj_mdlSaleInvoice.OCGroupID;
cmd.Parameters.Add("@InvoiceType", SqlDbType.VarChar, 15).Value = obj_mdlSaleInvoice.InvoiceType;
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 10).Value = "Un-Posted";
cmd.Parameters.Add("@GrossAmount", SqlDbType.Decimal).Value = Convert.ToDouble(obj_mdlSaleInvoice.GrossAmount);
cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = Convert.ToDouble(obj_mdlSaleInvoice.Discount);
cmd.Parameters.Add("@SaleTax", SqlDbType.Decimal).Value = Convert.ToDouble(obj_mdlSaleInvoice.SaleTax);
cmd.Parameters.Add("@AddlTax", SqlDbType.Decimal).Value = Convert.ToDouble(obj_mdlSaleInvoice.AddlTax);
cmd.Parameters.Add("@NetAmount", SqlDbType.Decimal).Value = Convert.ToDouble(obj_mdlSaleInvoice.NetAmount);
cmd.Parameters.Add("@Credit", SqlDbType.Decimal).Value = 0;
cmd.Parameters.Add("@ReturnAmount", SqlDbType.Decimal).Value = 0;
cmd.Parameters.Add("@Paid", SqlDbType.Decimal).Value = 0;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 100).Value = obj_mdlSaleInvoice.Remarks;
cmd.Parameters.Add("@RefNo", SqlDbType.VarChar, 50).Value = obj_mdlSaleInvoice.RefNo;
cmd.Parameters.Add("@RefDate", SqlDbType.DateTime).Value = Convert.ToDateTime(obj_mdlSaleInvoice.RefDate);
if (cmd.ExecuteNonQuery() != 0)
{
if (SaveInvoiceDetails(obj_mdlSaleInvoice, ds_InvoiceDetail, transaction) == true)
{
transaction.Commit();
functionReturnValue = true;
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
cmd.Cancel();
cmd.Dispose();
if (dr_Reader.IsClosed == false)
{ dr_Reader.Close(); }
}
catch (Exception ex)
{
transaction.Rollback();
functionReturnValue = false;
MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return functionReturnValue;
}
public DataSet GetInvoiceDetail(mdlSaleInvoice obj_SaleInvoice)
{
return obj_dalSaleInvoice.GetInvoiceDetail(obj_SaleInvoice);
}
public DataSet FindSaleInvoice(mdlSaleInvoice obj_SaleInvoice, string InvoiceID)
{
return obj_dalSaleInvoice.FindSaleInvoice(obj_SaleInvoice, InvoiceID);
}
public string GenerateNextDSSID(mdlSaleInvoice obj_mdlSaleInvoice)
{
obj_dalSaleInvoice.GenerateNextDSSID(obj_mdlSaleInvoice);
return obj_mdlSaleInvoice.DSSID.ToString();
}
public bool SaveSaleOrderInvoices(mdlSaleInvoice obj_mdlSaleInvoice, DataSet p_DataSet, bool p_LocalOrders, bool p_mergorder, string p_WorkDate)
{
return obj_dalSaleInvoice.SaveSaleOrderInvoices(obj_mdlSaleInvoice, p_DataSet, p_LocalOrders, p_mergorder, p_WorkDate);
}
public bool SaveNewSaleInvoice(mdlSaleInvoice obj_mdlSaleInvoice, DataSet ds_InvoiceDetail)
{
return obj_dalSaleInvoice.SaveNewSaleInvoice(obj_mdlSaleInvoice, ds_InvoiceDetail);
}
public bool ModifySaleInvoice(mdlSaleInvoice obj_mdlSaleInvoice, DataSet ds_InvoiceDetail)
{
return obj_dalSaleInvoice.ModifySaleInvoice(obj_mdlSaleInvoice, ds_InvoiceDetail);
}
public void GetMaxSaleInvoiceID(mdlSaleInvoice obj_SaleInvoice)
{
SqlCommand cmd = new SqlCommand("Select Coalesce(Max(Left(pk_InvoiceId,6)),0)+1 As MaxInvoiceID From M_SaleInvoice Where right(Year(GetDate()),2)=(Select Right(Year(WorkDate),2) From M_SystemInformation)", conn);
cmd.CommandType = CommandType.Text;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
obj_SaleInvoice.pk_InvoiceId = string.Format("{0:000000}", Convert.ToInt32(reader.GetValue(0).ToString())) + "-" + DateTime.Today.Year.ToString().Remove(0, 2);
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
public bool SaveNewSaleInvoice(mdlSaleInvoice obj_mdlSaleInvoice, DataSet ds_InvoiceDetail)
{
//*****************Max INvoice Id
GetMaxInvoiceID(obj_mdlSaleInvoice.WorkDate);
obj_mdlSaleInvoice.pk_InvoiceId = Lc_MaxInvoiceId;
bool functionReturnValue = false;
SqlTransaction transaction;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
transaction = conn.BeginTransaction();
try
{
string SQL;
//****************
SQL = " Insert Into M_SaleInvoice (pk_InvoiceId,InvoiceDate,DSSId,CustomerId,BrickId,TerritoryId,SalesManId,EmployeeId,PatientId,DueDate,Claimable,SaleBase,OCGroupId,InvoiceType,Status,GrossAmount,Discount,SaleTax,AddlTax,";
SQL = SQL + " NetAmount,Credit,ReturnAmount,Paid,Remarks,AddUser,AddDate,AddTime,WOS,RefNo,RefDate)";
SQL = SQL + " Values(";
SQL = SQL + "'" + Lc_MaxInvoiceId + "',";
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.InvoiceDate) + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.DSSID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.CustomerId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.BrickId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.TerritoryId + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.SalesManID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.EmployeeID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.PatientId + "',";
if (obj_mdlSaleInvoice.DueDate.ToString() != "")
{
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.DueDate) + "',";
}
else
{
SQL = SQL + "'" + Convert.ToDateTime("01/01/1900") + "',";
}
SQL = SQL + "'" + obj_mdlSaleInvoice.Claimable + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.Salebase + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.OCGroupID + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.InvoiceType + "',";
SQL = SQL + "'" + "Un-Posted" + "',";
SQL = SQL + "'" + Convert.ToDouble(obj_mdlSaleInvoice.GrossAmount) + "',";
SQL = SQL + "'" + Convert.ToDouble(obj_mdlSaleInvoice.Discount) + "',";
SQL = SQL + "'" + Convert.ToDouble(obj_mdlSaleInvoice.SaleTax) + "',";
SQL = SQL + "'" + Convert.ToDouble(obj_mdlSaleInvoice.AddlTax) + "',";
SQL = SQL + "'" + Convert.ToDouble(obj_mdlSaleInvoice.NetAmount) + "',";
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + " '0',";
SQL = SQL + "'" + obj_mdlSaleInvoice.Remarks + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.UserName + "',";
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "',";
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.WorkDate) + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.WOS + "',";
SQL = SQL + "'" + obj_mdlSaleInvoice.RefNo + "',";
if (obj_mdlSaleInvoice.RefDate.ToString() != "")
{
SQL = SQL + "'" + Convert.ToDateTime(obj_mdlSaleInvoice.RefDate) + "')";
}
else
{
SQL = SQL + "'" + Convert.ToDateTime("01/01/1900") + "')";
}
cmd = new SqlCommand(SQL, conn, transaction);
cmd.CommandType = CommandType.Text;
if (cmd.ExecuteNonQuery() != 0)
{
if (SaveInvoiceDetails(obj_mdlSaleInvoice, ds_InvoiceDetail, transaction) == true)
{
transaction.Commit();
functionReturnValue = true;
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
}
else
{
transaction.Rollback();
functionReturnValue = false;
}
cmd.Cancel();
cmd.Dispose();
if (dr_Reader.IsClosed == false)
{ dr_Reader.Close(); }
}
catch (Exception ex)
{
transaction.Rollback();
functionReturnValue = false;
MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return functionReturnValue;
}
public frmSaleInvoice(mdlSaleInvoice mdlSaleInvoice)
{
obj_mdlSaleInvoice = mdlSaleInvoice;
InitializeComponent();
}
public string GenerateNextSaleInvoiceID(mdlSaleInvoice obj_mdlSaleInvoice)
{
obj_dalSaleInvoice.GetMaxSaleInvoiceID(obj_mdlSaleInvoice);
return obj_mdlSaleInvoice.pk_InvoiceId.ToString();
}
