public StockResponseModel GetStockFromQuery(String xQry)
{
StockResponseModel model = new StockResponseModel();
using (connection = new MySqlConnection(conString))
{
connection.Open();
MySqlCommand comm = new MySqlCommand(xQry, connection);
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
model = new StockResponseModel()
{
StockId = reader.GetInt32("stock_id"),
ProductId = reader.GetInt32("product_id"),
Qty = reader.GetInt32("qty"),
Mrp = reader.GetDouble("mrp"),
Batch = reader.GetString("batch_id"),
ExpDate = reader.GetDateTime("expiry_date")
};
}
connection.Close();
}
return(model);
}
public void BizUserCreateAndDeleteStock()
{
var createStockRequest1 = new StockRequestModel
{
Id = 0,
Name = "Розничный склад 1",
IsMain = false,
StockType = 2
};
var createStockRequest2 = new StockRequestModel
{
Id = 0,
Name = "Розничный склад 2",
IsMain = false,
StockType = 2
};
string login = TesterUtils.CreateBizUserOooUsn15AndFillRequisites();
TesterUtils.SwitchOnStock(login);
string token = OauthUtils.GetAutorizationToken(login);
StockResponseModel responseCreate1 = StockUtils.СreateStock(token, createStockRequest1);
StockResponseModel responseCreate2 = StockUtils.СreateStock(token, createStockRequest2);
StockUtils.DeleteStock(token, responseCreate1.Id);
StockListResponseModel stockList = StockUtils.GetStockList(token, 1, 10);
int count = stockList.ResourceList.Count;
Assert.AreEqual(2, count);
}
public void BizUserCreateUpdateAndGetStock()
{
var createStockRequest = new StockRequestModel
{
Id = 0,
Name = "Розничный склад",
IsMain = false,
StockType = 2
};
string login = TesterUtils.CreateBizUserOooUsn15AndFillRequisites();
TesterUtils.SwitchOnStock(login);
string token = OauthUtils.GetAutorizationToken(login);
StockResponseModel responseCreate = StockUtils.СreateStock(token, createStockRequest);
var updateStockRequest = new StockRequestModel
{
Id = responseCreate.Id,
Name = "Отредактированный склад",
IsMain = true,
StockType = 1
};
StockResponseModel responceUpdate = StockUtils.UpdateStock(token, responseCreate.Id, updateStockRequest);
StockResponseModel responceGet = StockUtils.GetStock(token, responseCreate.Id);
Assert.AreEqual(updateStockRequest.Id, responceGet.Id);
Assert.AreEqual(updateStockRequest.Name, responceGet.Name);
Assert.AreEqual(updateStockRequest.IsMain, responceGet.IsMain);
Assert.AreEqual(updateStockRequest.StockType, responceGet.StockType);
Assert.AreEqual(responceUpdate.SubcontoId, responceGet.SubcontoId);
}
private void txtProductCode_TextChanged(object sender, EventArgs e)
{
txtUnitPrice.Text = "";
StockResponseModel stockResponseModel = xDb.GetStockFromQuery("select " +
" s.stock_id, s.product_id, p.product_name, " +
" s.qty, s.mrp, s.batch_id, s.expiry_date, p.gst " +
" from m_product p,stock s " +
" where s.product_id = p.product_id " +
" and product_code = '" + txtProductCode.Text + "'");
ProductModel productModel = xDb.GetProductFromProductId("select * from m_product where product_id=" + stockResponseModel.ProductId);
txtItem.Text = productModel.ProductName;
txtItemNo.Text = productModel.ProductId.ToString();
txtMrp.Text = stockResponseModel.Mrp.ToString();
txtStockId.Text = stockResponseModel.StockId.ToString();
txtStock.Text = stockResponseModel.Qty.ToString();
txtTax.Text = productModel.Gst;
txtBatch.Text = stockResponseModel.Batch;
txtExpDate.Text = stockResponseModel.ExpDate.ToString();
if (txtMrp.Text != "" && txtTax.Text != "")
{
/*Included Gst Logic */
double xUnitMrp = double.Parse(txtMrp.Text.ToString());
double xGstBefore = double.Parse(txtTax.Text.ToString()) / 100 + 1;
double xUnitRate = (((xUnitMrp / xGstBefore)));
txtUnitPrice.Text = xUnitRate.ToString("#.##");
}
}
public List <int> GetStockProductIDs()
{
List <int> values = new List <int>();
StockResponseModel model = new StockResponseModel();
using (connection = new MySqlConnection(conString))
{
connection.Open();
MySqlCommand comm = new MySqlCommand("select product_id from m_product", connection);
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
values.Add(reader.GetInt32("product_id"));
}
;
}
connection.Close();
return(values);
}
private void mbtnSaveBill_Click(object sender, EventArgs e)
{
if (txtCustomerNo.Text == "")
{
MessageBox.Show("Please Choose Ledger");
return;
}
if (!(dataGridView1.Rows.Count > 0))
{
MessageBox.Show("Please add data");
return;
}
int xPurchaseId = xDb.GetMaxId("purchase_id", "purchase");
string xUser = LoginInfo.UserID;
string xCurrentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (MySqlConnection myConnection = new MySqlConnection(xDb.conString))
{
myConnection.Open();
MySqlTransaction myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
MySqlCommand myCommand = myConnection.CreateCommand();
try
{
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
int xTxNo = dr.Index + 1;
int xProductId = Convert.ToInt32(dr.Cells["product_id"].Value);
int xQty = Convert.ToInt32(dr.Cells["Qty"].Value);
int xPackOf = Convert.ToInt32(dr.Cells["PackOf"].Value);
int xTotalQty = xQty * xPackOf;
string xBatch = dr.Cells["Batch"].Value.ToString();
string xExpDate = dr.Cells["ExpDate"].Value.ToString();
double xMrp = Convert.ToDouble(dr.Cells["SellingPrice"].Value);
double xGst = Convert.ToDouble(dr.Cells["GstPercentage"].Value);
string xQryInsertPurchase = "insert into purchase_details" +
"(purchase_id,txno,product_id,qty,batch_id,expiry_date,mrp,purchase_rate,discount,gst,total) " +
"values(" + xPurchaseId + "," +
" '" + xTxNo + "'," +
" '" + xProductId + "'," +
" '" + xTotalQty + "'," +
" '" + xBatch + "'," +
" '" + xExpDate + "'," +
" " + xMrp + "," +
" " + Convert.ToDouble(dr.Cells["Price"].Value) + "," +
" " + Convert.ToDouble(dr.Cells["DiscPercentage"].Value) + "," +
" " + xGst + "," +
" " + Convert.ToDouble(dr.Cells["TotalAmount"].Value) + " )";
myCommand.CommandText = xQryInsertPurchase;
myCommand.ExecuteNonQuery();
string xLogMessage = "";
StockResponseModel stockResponseModel = xDb.GetStockFromQuery("select * from stock where product_id = " + xProductId + " and batch_id = '" + xBatch + "' and mrp = '" + xMrp + "'");
int xOldQty = 0;
int xNewQty = 0;
if (stockResponseModel.Mrp > 0)
{
xOldQty = stockResponseModel.Qty;
xNewQty = xTotalQty + xOldQty;
String xQryUpdateStock = "update stock set " +
" qty=" + xNewQty + ",updated_by = '" + xUser + "', updated_on = '" + xCurrentDateTime + "' " +
" where product_id=" + xProductId + " and batch_id = '" + xBatch + "' " +
" and mrp = '" + xMrp + "'";
myCommand.CommandText = xQryUpdateStock;
myCommand.ExecuteNonQuery();
xLogMessage = "PURCHASE UPDATED FOR OLD BATCH MRP";
}
else
{
string xQryStockEntry = "insert into stock" +
"(product_id,qty,mrp,batch_id,expiry_date,created_by,created_on) " +
" values(" + xProductId + "," + xTotalQty + "," + xMrp + ",'" + xBatch + "'," +
" '" + xExpDate + "','" + xUser + "','" + xCurrentDateTime + "')";
myCommand.CommandText = xQryStockEntry;
myCommand.ExecuteNonQuery();
xLogMessage = "PURCHASE CREATED FOR NEW BATCH MRP";
}
xOldQty = stockResponseModel.Qty;
xNewQty = xTotalQty + xOldQty;
string xQryStockDetails = "insert into stock_history" +
" (product_id,old_qty,change_qty,current_qty,mrp,batch_id,expiry_date,reason,created_by,created_on)" +
"values(" + xProductId + "," + xOldQty + "," + xTotalQty + "," + xNewQty + "," + xMrp + ",'" + xBatch + "'," +
" '" + xExpDate + "','" + xLogMessage + "','" + xUser + "','" + xCurrentDateTime + "')";
myCommand.CommandText = xQryStockDetails;
myCommand.ExecuteNonQuery();
}
string xQryPurchaseMain = "insert into purchase" +
"(purchase_id,date,supplier_id,bill_invoice_no,freight,others,less_amount,total_amount) " +
"values(" + xPurchaseId + "," +
" '" + Convert.ToDateTime(dtpPEDate.Text).ToString("yyyy-MM-dd") + "'," +
" '" + Convert.ToInt32(txtCustomerNo.Text.ToString()) + "'," +
" '" + txtCompanyInvoiceNo.Text.ToString() + "'," +
" '0'," +
" '0'," +
" '" + txtLessAmount.Text.ToString() + "'," +
" '" + Convert.ToDouble(lbl_total_amount_value.Text) + "')";
myCommand.CommandText = xQryPurchaseMain;
myCommand.ExecuteNonQuery();
myTrans.Commit();
dataGridView1.Rows.Clear();
DataClear();
MessageBox.Show("Record Saved Succesfully Id is " + xPurchaseId);
lblInvoiceNo.Text = xDb.GetMaxId("purchase_id", "purchase").ToString();
txtCompanyInvoiceNo.Text = "";
lblGoodsValue.Text = "0";
lblTotalDiscount.Text = "0";
lblTotalgst.Text = "0";
lblRoundOff.Text = "0";
lbl_total_amount_value.Text = "0";
}
catch (Exception ex)
{
MessageBox.Show("Record not saved - Error Occured" + ex.ToString());
myTrans.Rollback();
}
finally
{
myCommand.Dispose();
myTrans.Dispose();
xDb.connection.Close();
}
}
}
public void DataProcess()
{
string xUser = LoginInfo.UserID;
string xCurrentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (MySqlConnection myConnection = new MySqlConnection(xDb.conString))
{
myConnection.Open();
MySqlTransaction myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
MySqlCommand myCommand = myConnection.CreateCommand();
int xProductId = Convert.ToInt32(txtItemNo.Text);
string xBatch = txtBatch.Text;
double xMrp = Convert.ToDouble(txtMrp.Text);
string xReason = "PURCHASE RETURN ";
string xExpiryDate = txtExpDate.Text;
string xQryPurchaseReturn = "insert into purchase_return" +
"(purchase_id," +
" date,product_id,qty,mrp,batch_id,expiry_date,reason,created_by,created_on) " +
" values(" + Convert.ToInt32(txtPurchaseId.Text) + "," +
" '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," +
" '" + xProductId + "'," +
" '" + Convert.ToInt32(txtChangeQty.Text) + "'," +
" '" + xMrp + "'," +
" '" + xBatch + "'," +
" '" + xExpiryDate + "'," +
" '" + xReason + rchRemarks.Text + "'," +
" '" + xUser + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ "')";
StockResponseModel stockResponseModel = xDb.GetStockFromQuery("select * from stock where product_id = " + xProductId + " and batch_id = '" + xBatch + "' and mrp = '" + xMrp + "'");
int xStockOldQty = 0;
int xStockNewQty = 0;
xStockOldQty = stockResponseModel.Qty;
int xChangeQty = Convert.ToInt32(txtChangeQty.Text);
if (xChangeQty > xStockOldQty)
{
MessageBox.Show("Stock Not Exists /Purchase return done");
return;
}
xStockNewQty = xStockOldQty - xChangeQty;
string xQryStockUpdateEntry = "update stock set " +
" qty = " + xStockNewQty + ", updated_by = '" + xUser + "', updated_on = '" + xCurrentDateTime + "' " +
" where product_id=" + xProductId + " " +
" and batch_id = '" + xBatch + "' " +
" and mrp = '" + xMrp + "'";
string xQryStockHistory = "insert into stock_history" +
" (product_id,old_qty,change_qty,current_qty," +
" mrp,batch_id,expiry_date,reason,created_by,created_on)" +
" values(" + xProductId + "," + xStockOldQty + "," + xChangeQty + "," + xStockNewQty + "," + xMrp + "," +
" '" + xBatch + "','" + xExpiryDate + "','" + xReason + "'," +
" '" + xUser + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ "')";
myCommand.CommandText = xQryPurchaseReturn;
myCommand.ExecuteNonQuery();
myCommand.CommandText = xQryStockUpdateEntry;
myCommand.ExecuteNonQuery();
myCommand.CommandText = xQryStockHistory;
myCommand.ExecuteNonQuery();
myTrans.Commit();
MessageBox.Show("Purchase Item Returned.");
DataClear();
}
}
private void SaveData(string xBillStatus)
{
if (cmbPaymentMode.Text == "Please Select")
{
MessageBox.Show("Please Choose Payment");
cmbPaymentMode.Select();
return;
}
if (cmbBillType.Text == "Please Select")
{
MessageBox.Show("Please Choose Bill Type");
cmbBillType.Select();
return;
}
if (txtCustomerNo.Text == "")
{
MessageBox.Show("Please Choose Ledger");
return;
}
if (!(dataGridView1.Rows.Count > 0))
{
MessageBox.Show("Please add data");
return;
}
int xSalesId = xDb.GetMaxId("sales_id", "sales");
string xUser = LoginInfo.UserID;
string xCurrentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
double xLessAmount = 0;
using (MySqlConnection myConnection = new MySqlConnection(xDb.conString))
{
myConnection.Open();
MySqlTransaction myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
MySqlCommand myCommand = myConnection.CreateCommand();
try
{
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
int xTxNo = dr.Index + 1;
int xStockId = Convert.ToInt32(dr.Cells["Id"].Value);
int xProductId = Convert.ToInt32(dr.Cells["ItemNoDg1"].Value);
int xSalesQty = Convert.ToInt32(dr.Cells["Qty"].Value);
double UnitPrice = Convert.ToDouble(dr.Cells["UnitPrice"].Value);
double xAmount = xSalesQty * UnitPrice;
double xGstPercentage = Convert.ToDouble(dr.Cells["GstPercentage"].Value);
//double xUnitMrp = UnitPrice + (UnitPrice * xGstPercentage / 100);
double xMrp = Convert.ToDouble(dr.Cells["Mrp"].Value);
string xBatch = dr.Cells["Batch"].Value.ToString();
string xQrySalesDetails = "insert into sales_details" +
"(sales_id,txno,product_id,batch_id,expiry_date,qty,unit_rate,unit_sp,amount,gst,discount_percentage,unit_mrp) " +
"values(" + xSalesId + "," +
" '" + xTxNo + "'," +
" '" + xProductId + "'," +
" '" + xBatch + "'," +
" '" + dr.Cells["ExpDate"].Value + "'," +
" " + xSalesQty + "," +
" " + Convert.ToDouble(dr.Cells["UnitPrice"].Value) + "," +
" " + Convert.ToDouble(dr.Cells["UnitSP"].Value) + "," +
" " + xAmount + "," +
" " + Convert.ToDouble(dr.Cells["GstPercentage"].Value) + "," +
" " + Convert.ToDouble(dr.Cells["DiscPercentage"].Value) + "," +
" " + xMrp + " )";
xLessAmount += Convert.ToDouble(dr.Cells["DiscValue"].Value);
myCommand.CommandText = xQrySalesDetails;
myCommand.ExecuteNonQuery();
StockResponseModel stockResponseModel = xDb.GetStockFromQuery("select * from stock where product_id = " + xProductId + " and batch_id = '" + xBatch + "' and mrp = '" + xMrp + "'");
int xOldQty = 0;
int xNewQty = 0;
xOldQty = stockResponseModel.Qty;
xNewQty = xOldQty - xSalesQty;
string xUpdateStockQry = "update stock set " +
" qty = " + xNewQty + ", updated_by = '" + xUser + "', updated_on = '" + xCurrentDateTime + "' " +
" where product_id=" + xProductId + " " +
" and batch_id = '" + xBatch + "' " +
" and mrp = '" + xMrp + "'";
myCommand.CommandText = xUpdateStockQry;
myCommand.ExecuteNonQuery();
string xReason = "NEW SALES";
string xQryStockHistory = "insert into stock_history" +
" (product_id,old_qty,change_qty,current_qty," +
" mrp,batch_id,expiry_date,reason,created_by,created_on)" +
" values(" + xProductId + "," + xOldQty + "," + xSalesQty + "," + xNewQty + "," + xMrp + "," +
" '" + xBatch + "','" + txtExpDate.Text + "','" + xReason + "'," +
" '" + xUser + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ "')";
myCommand.CommandText = xQryStockHistory;
myCommand.ExecuteNonQuery();
}
string xQrySalesMain = "insert into sales" +
"(sales_id,date,patient_id,less_amount,service_amount,total_amount," +
"mode_of_payment,terms_of_delivery,created_by,created_on) " +
"values(" + xSalesId + "," +
" '" + dtpDate.Value.ToString("yyyy-MM-dd") + "'," +
" '" + Convert.ToInt32(txtCustomerNo.Text.ToString()) + "'," +
" '" + xLessAmount + "'," +
" '0'," +
" '" + Convert.ToDouble(lbl_total_amount_value.Text) + "'," +
" '" + cmbPaymentMode.Text.ToString() + "'," +
" '" + cmbBillType.Text.ToString() + "'," +
" '" + xUser + "','" + xCurrentDateTime + "' )";
myCommand.CommandText = xQrySalesMain;
myCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception ex)
{
MessageBox.Show("Record not saved - Error Occured" + ex.ToString());
myTrans.Rollback();
}
finally
{
myCommand.Dispose();
myTrans.Dispose();
xDb.connection.Close();
//DialogResult result = MessageBox.Show("Do you want to print?", "Confirmation", MessageBoxButtons.YesNo);
//if (result == DialogResult.Yes)
//{
// PrintBill(xSalesId);
//}
//else if (result == DialogResult.No)
//{
// //...
//}
}
}
DataClear();
DataClearAfterSave();
lblInvoiceNo.Text = xDb.GetMaxId("sales_id", "sales").ToString();
}
