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(); }