public Boolean saveTraderData(String TRDR_FNAME, String TRDR_LNAME, String TRDR_MOB, String TRDR_EMAIL, String TRDR_TMARK, String TRDR_STATE, String TRDR_DIST, String TRDR_PIN)
{
this.TRDR_FNAME = TRDR_FNAME.Trim();
this.TRDR_LNAME = TRDR_LNAME.Trim();
this.TRDR_MOB = TRDR_MOB.Trim();
this.TRDR_EMAIL = TRDR_EMAIL.Trim();
this.TRDR_TMARK = TRDR_TMARK.Trim();
this.TRDR_STATE = TRDR_STATE;
this.TRDR_DIST = TRDR_DIST;
this.TRDR_PIN = TRDR_PIN.Trim();
String TRDR_ID = "TID" + GenerateUniqueID.RandomString(8);
insertQuery = "insert into " + tableName + " values('" + TRDR_ID + "', '" + TRDR_FNAME + "','" + TRDR_LNAME + "','" + TRDR_MOB + "','" + TRDR_EMAIL + "','" + TRDR_TMARK + "','" + TRDR_STATE + "','" + TRDR_DIST + "','" + TRDR_PIN + "');";
if (!(dataAccessUtility.iscolumnAlreadyPresent(tableName, "TRDR_ID", TRDR_ID)))
{
result = insertData(insertQuery);
}
else
{
MessageBox.Show("SomeThing Went Wrong!! Please try Again");
}
return(result);
}
private void addItemSaveBtn_Click(object sender, EventArgs e)
{
String ITEM_CATEGORY = itemCategorySelected;
String ITEM_NAME = itemNameTxt.Text.ToString();
if (String.IsNullOrEmpty(ITEM_CATEGORY))
{
errorProvider1.SetError(addItemCategorycomboBox, "Select Item Category First !");
return;
}
if (String.IsNullOrEmpty(ITEM_NAME))
{
errorProvider1.SetError(itemNameTxt, "Enter Item Name");
return;
}
DataLoadUtility dataLoadUtility = new DataLoadUtility();
String ITEM_ID = GenerateUniqueID.RandomString(5);
String sqlQuery = "insert into item_details values ('" + ITEM_ID + "','" + ITEM_NAME + "','" + ITEM_CATEGORY + "');";
if (!(dataLoadUtility.insertData(sqlQuery)))
{
MessageBox.Show("Item Not Saved");
}
else
{
MessageBox.Show("Item Saved");
this.Close();
}
}
public Boolean saveCustomerData(String CUST_FNAME, String CUST_LNAME, String CUST_MOB, String CUST_EMAIL, String CUST_STATE, String CUST_DISTRICT, String CUST_AREA, String CUST_IMG_LOC)
{
this.CUST_FNAME = CUST_FNAME.Trim();
this.CUST_LNAME = CUST_LNAME.Trim();
this.CUST_MOB = CUST_MOB.Trim();
this.CUST_EMAIL = CUST_EMAIL.Trim();
this.CUST_STATE = CUST_STATE;
this.CUST_DISTRICT = CUST_DISTRICT;
this.CUST_AREA = CUST_AREA;
this.CUST_IMG_LOC = CUST_IMG_LOC;
String CUST_ID = GenerateUniqueID.RandomString(8);
if (!(String.IsNullOrEmpty(CUST_IMG_LOC)))
{
String fileExtension = Path.GetExtension(CUST_IMG_LOC);
this.CUST_IMG_FILENAME = CUST_ID + fileExtension;
if (!(copyUtils.copyImag(CUST_IMG_LOC, CUST_IMG_FILENAME)))
{
return(false);
}
}
else
{
this.CUST_IMG_FILENAME = "";
}
//String CUST_ID = "5L9OSNKD";
insertQuery = "insert into " + tableName + " values('" + CUST_FNAME + "','" + CUST_LNAME + "','" + CUST_MOB + "','" + CUST_EMAIL + "','" + CUST_STATE + "','" + CUST_DISTRICT + "','" + CUST_AREA + "','" + AppConstant.IMG_TRAGET_PATH + "','" + CUST_IMG_FILENAME + "', " + " '" + CUST_ID + "');";
if (!(dataAccessUtility.iscolumnAlreadyPresent(tableName, "CUST_ID", CUST_ID)))
{
result = insertData(insertQuery);
}
else
{
MessageBox.Show("SomeThing Went Wrong!! Please try Again");
}
if (!(dataAccessUtility.iscolumnAlreadyPresent(tableName, "CUST_FNAME", CUST_FNAME, "CUST_LNAME", CUST_LNAME, "CUST_AREA", CUST_AREA)))
{
result = insertData(insertQuery);
}
else
{
MessageBox.Show(CUST_FNAME + " " + CUST_LNAME + " " + CUST_AREA + " Is Already Present");
}
return(result);
}
public Boolean saveSellsData(SellsEntryForCustomerVO sellsEntryForCustomerVO)
{
this.sellsEntryForCustomerVO = sellsEntryForCustomerVO;
try
{
con = dataBaseConnection.getMySqlDBConnection();
con.Open();
MySqlCommand myCommand = con.CreateCommand();
// Start a local transaction
myTrans = con.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = con;
myCommand.Transaction = myTrans;
String CST_INVC_ID = "INVCID" + GenerateUniqueID.RandomString(8);
String CUST_ID = sellsEntryForCustomerVO.Cust_id;
String INVOICE_DATE = sellsEntryForCustomerVO.Date;
double amount = 0;
// Inserting Data into Cust sells_data table
foreach (SellEntryDetails sellEntryDetails in sellsEntryForCustomerVO.SellEntryDetails)
{
String TOT_AMNT = sellEntryDetails.TotalAmount;
amount = amount + Convert.ToDouble(TOT_AMNT);
String QTY = sellEntryDetails.Quantity;
String RATE = sellEntryDetails.Rate1;
String WT_BOX_TYPE = sellEntryDetails.WT_BOX_TYPE1;
String WT_TXT = sellEntryDetails.WT_TXT1;
String SELL_TXN_ID = sellEntryDetails.sell_txn_id1;
String SELL_DATE = sellEntryDetails.sell_date1;
String ITEM_NAME = sellEntryDetails.SellItemName;
insertQuery1 = "insert into " + table1 + " values('" + CUST_ID + "','" + CST_INVC_ID + "','" + INVOICE_DATE + "','" + TOT_AMNT + "','" + SELL_TXN_ID +
"','" + ITEM_NAME + "','" + QTY + "','" + RATE + "','" + SELL_DATE + "','N','N','" + WT_BOX_TYPE + "','" + WT_TXT + "');";
Console.WriteLine(insertQuery1);
if (!(dataAccessUtility.iscolumnAlreadyPresent(table1, "SELL_TXN_ID", SELL_TXN_ID)))
{
insertData(insertQuery1, con);
Console.WriteLine("Sells Entry For Each Lot :" + SELL_TXN_ID);
}
else
{
MessageBox.Show("Some Thing Wrong !! Please Try Again");
return(false);
}
// Inserting Data into trader_sells_book table
foreach (SellsEntryForTraderVO sellsEntryForTraderVO in sellEntryDetails.SellsEntryForTraderVO)
{
String TRDR_ID = sellsEntryForTraderVO.TRDR_ID1;
String G_ENTRY_ID = sellsEntryForTraderVO.G_ENTRY_ID1;
String STOCK_ID = sellsEntryForTraderVO.STOCK_ID1;
String LOT_ID = sellsEntryForTraderVO.LOT_ID1;
insertQuery2 = "insert into " + table2 + " values('" + TRDR_ID + "','" + G_ENTRY_ID + "','" + STOCK_ID + "','" + LOT_ID + "','" + TOT_AMNT +
"','" + SELL_DATE + "','" + SELL_TXN_ID + "');";
Console.WriteLine("trader_sells_book Query:" + insertQuery2);
insertData(insertQuery2, con);
//Update lot_status
String TOT_BAL = sellEntryDetails.tot_bal1;
String TOT_SOLD = sellEntryDetails.tot_sold1;
String TOT_SOLD_IND;
if (int.Parse(TOT_BAL) == 0)
{
TOT_SOLD_IND = "Y";
}
else
{
TOT_SOLD_IND = "N";
}
updateQuery = "update " + table4 + " set TOT_SOLD='" + TOT_SOLD + "',TOT_BAL='" + TOT_BAL + "',TOT_SOLD_IND='" + TOT_SOLD_IND + "' where LOT_ID='"
+ LOT_ID + "' and G_ENTRY_ID='" + G_ENTRY_ID + "'";
insertData(updateQuery, con);
}
}
//Setting Total Bill Amount
sellsEntryForCustomerVO.TotalAmountToday = amount.ToString();
Console.WriteLine("Total Bill:" + sellsEntryForCustomerVO.TotalAmountToday);
//Save Data into Customer Ledger
String TOT_BILL_AMNT = sellsEntryForCustomerVO.TotalAmountToday;
String BLNC_CLEAR = "N";
insertQuery3 = "insert into " + table3 + " values('" + CST_INVC_ID + "','" + CUST_ID + "','" + INVOICE_DATE + "','" + TOT_BILL_AMNT + "','" + BLNC_CLEAR + "');";
Console.WriteLine("cust_ledger Query:" + insertQuery3);
insertData(insertQuery3, con);
myTrans.Commit();
Console.WriteLine("All records are written to database.");
finalResult = true;
return(finalResult);
}
catch (Exception e)
{
finalResult = false;
try
{
myTrans.Rollback();
return(finalResult);
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() + " was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
return(finalResult);
}
finally
{
con.Close();
}
}
private void sellsEntryDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
isCellClicked = true;
SellEntryDetails sellEntryDetails = null;
SellsEntryForTraderVO sellsEntryForTraderVO = null;
if (String.IsNullOrEmpty(itemSelected) || String.IsNullOrEmpty(customerSelected))
{
MessageBox.Show("Please Select Custmer and Item");
}
else
{
try
{
if (e.RowIndex >= 0)
{
String TRDR_FNAME_TMARK = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[0].Value.ToString();
String RECV_DATE = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[1].Value.ToString();
String ITEM_NAME = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[2].Value.ToString();
String WT_BOX_TYPE = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[3].Value.ToString();
String WT_TXT = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[4].Value.ToString();
String TOT_BAL = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[8].Value.ToString();
String LOT_SERIAL = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[5].Value.ToString();
String G_ENTRY_ID = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[9].Value.ToString();
String TOT_SOLD = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[10].Value.ToString();
String TRDR_ID = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[12].Value.ToString();
String STOCK_ID = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[14].Value.ToString();
String LOT_ID = this.sellsEntryDataGridView.Rows[e.RowIndex].Cells[15].Value.ToString();
// Console.Write("TRDR_FNAME_TMARK:" + TRDR_FNAME_TMARK + "\nRECV_DATE:" + RECV_DATE + "\nITEM_NAME:" + ITEM_NAME + "\nWT_BOX_TYPE:" + WT_BOX_TYPE + "\nWT_TXT:" + WT_TXT + "\nTOT_BAL:" + TOT_BAL + "\nG_ENTRY_ID:" + G_ENTRY_ID + "\nTOT_SOLD:" + TOT_SOLD + "\nTRDR_ID:" + TRDR_ID + "\nSTOCK_ID:" + STOCK_ID + "\nLOT_ID:" + LOT_ID + "\nCUST_ID" + CUST_ID + "\ncustomerSelected:" + customerSelected);
//dSellsDetailFrm addSellsDetailFrm = new AddSellsDetailFrm(customerSelected, ITEM_NAME);
//addSellsDetailFrm.Show();
using (var form = new AddSellsDetailFrm(customerSelected, ITEM_NAME, TRDR_FNAME_TMARK, TOT_BAL))
{
var result = form.ShowDialog();
if (result == DialogResult.OK)
{
if (int.Parse(form.quantity1) > int.Parse(TOT_BAL))
{
MessageBox.Show("Quantity Is Greater than Total Balance ");
}
else
{
sellsEntryForTraderVO = new SellsEntryForTraderVO();
sellEntryDetails = new SellEntryDetails();
sellEntryDetails.SellItemName = itemSelected;
int totalSold = int.Parse(TOT_SOLD) + int.Parse(form.quantity1);
int totBal = int.Parse(TOT_BAL) - int.Parse(form.quantity1);
sellEntryDetails.Quantity = form.quantity1;
sellEntryDetails.Rate1 = form.rate1;
sellEntryDetails.TotalAmount = form.totalAmount1;
sellEntryDetails.tot_bal1 = totBal.ToString();
sellEntryDetails.tot_sold1 = totalSold.ToString();
sellEntryDetails.sell_txn_id1 = "TxnSell" + GenerateUniqueID.RandomString(8);
sellEntryDetails.sell_date1 = DateHelper.getSqlString(form.sellDate1);
sellEntryDetails.LOT_ID1 = LOT_ID;
sellEntryDetails.SellItemName = ITEM_NAME;
sellEntryDetails.WT_BOX_TYPE1 = WT_BOX_TYPE;
sellEntryDetails.WT_TXT1 = WT_TXT;
}
//Console.Write("\nTotAmount:" + form.totalAmount1);
}
}
if (sellEntryDetails != null && sellsEntryForTraderVO != null)
{
sellsEntryForTraderVO.G_ENTRY_ID1 = G_ENTRY_ID;
sellsEntryForTraderVO.LOT_ID1 = LOT_ID;
sellsEntryForTraderVO.LOT_SERIAL1 = LOT_SERIAL;
sellsEntryForTraderVO.STOCK_ID1 = STOCK_ID;
sellsEntryForTraderVO.TRDR_FNAME_TMARK1 = TRDR_FNAME_TMARK;
sellsEntryForTraderVO.TRDR_ID1 = TRDR_ID;
sellEntryDetails.SellsEntryForTraderVO.Add(sellsEntryForTraderVO);
sellsEntryForCustomerVO.SellEntryDetails.Add(sellEntryDetails);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public Boolean saveGoodsEntryData(String TRDR_ID, String ITEM_NAME, String WT_BOX_TYPE, String WT_TXT, String LOT_1, String LOT_2, String LOT_3, String LOT_4, String LOT_5,
String LOGISTIC_DETAILS, String SHORT_BOX, String QTY_RCVD, String ORIGIN, String VEHICLE_NO, String FARE_RATE, String TOTAL_FARE, String RECV_DATE, String TRDR_FNAME_TMARK, String LOT_NO_SHORT)
{
this.TRDR_ID = TRDR_ID.Trim();
this.ITEM_NAME = ITEM_NAME.Trim();
this.WT_BOX_TYPE = WT_BOX_TYPE.Trim();
this.WT_TXT = WT_TXT.Trim();
this.LOT_1 = LOT_1;
this.LOT_2 = LOT_2;
this.LOT_3 = LOT_3;
this.LOT_4 = LOT_4;
this.LOT_5 = LOT_5;
this.LOGISTIC_DETAILS = LOGISTIC_DETAILS;
this.VEHICLE_NO = VEHICLE_NO;
this.FARE_RATE = FARE_RATE;
this.TOTAL_FARE = TOTAL_FARE;
this.SHORT_BOX = SHORT_BOX;
this.ORIGIN = ORIGIN;
this.RECV_DATE = RECV_DATE;
this.QTY_RCVD = QTY_RCVD;
this.TRDR_FNAME_TMARK = TRDR_FNAME_TMARK;
this.LOT_NO_SHORT = LOT_NO_SHORT;
//Entry of goods_entry details
String G_ENTRY_ID = "GETRYID" + GenerateUniqueID.RandomString(8);
try{
con = dataBaseConnection.getMySqlDBConnection();
con.Open();
MySqlCommand myCommand = con.CreateCommand();
// Start a local transaction
myTrans = con.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = con;
myCommand.Transaction = myTrans;
insertQuery = "insert into " + tableName + " values('" + G_ENTRY_ID + "','" + TRDR_ID + "','" + ITEM_NAME + "','" + WT_TXT + "','" + LOT_1
+ "','" + LOT_2 + "','" + LOT_3 + "','" + LOT_4 + "','" + LOT_5 + "','" + LOGISTIC_DETAILS + "','" + VEHICLE_NO + "','" + FARE_RATE + "','"
+ TOTAL_FARE + "','" + QTY_RCVD + "','" + ORIGIN + "','" + RECV_DATE + "','" + SHORT_BOX + "','" + WT_BOX_TYPE + "','" + TRDR_FNAME_TMARK + "','" + LOT_NO_SHORT + "');";
Console.WriteLine("Query:" + insertQuery);
if (!(dataAccessUtility.iscolumnAlreadyPresent(tableName, "G_ENTRY_ID", G_ENTRY_ID)))
{
insertData(insertQuery, con);
Console.WriteLine("Goods Entry Inserted");
}
else
{
throw (new CustomException("Some Thing Wrong !! Please Try Again"));
}
//Entry of stock details for each Lot
String stockTable = "stock_details";
String LOTID_1 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_2 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_3 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_4 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_5 = "LOT" + GenerateUniqueID.RandomString(8);
String STOCK_ID = "STOCK" + GenerateUniqueID.RandomString(8);
String[] lotIds = new String[] { LOTID_1, LOTID_2, LOTID_3, LOTID_4, LOTID_5 };
String [] lotBoxes = new String[] { this.LOT_1, this.LOT_2, this.LOT_3, this.LOT_4, this.LOT_5 };
String SHORT_LOT_IND = "N";
if (!(String.IsNullOrEmpty(this.LOT_NO_SHORT)))
{
SHORT_LOT_IND = "Y," + lotIds[int.Parse(this.LOT_NO_SHORT) - 1];
}
insertQuery = "insert into " + stockTable + " values('" + G_ENTRY_ID + "','" + LOTID_1 + "','" + LOTID_2 + "','" + LOTID_3 + "','" + LOTID_4
+ "','" + LOTID_5 + "','" + STOCK_ID + "','" + SHORT_LOT_IND + "');";
if (!(dataAccessUtility.iscolumnAlreadyPresent(stockTable, "STOCK_ID", STOCK_ID)))
{
insertData(insertQuery, con);
Console.WriteLine("StockQuery:" + insertQuery);
}
else
{
MessageBox.Show("Some Thing Wrong !! Please Try Again");
return(false);
}
//Entry of lots details into lot details
String lotDetailsTable = "lot_status";
if (SHORT_LOT_IND.Contains("Y"))
{
int index = int.Parse(this.LOT_NO_SHORT) - 1;
int shortbox = int.Parse(this.SHORT_BOX);
lotBoxes[index] = (int.Parse(lotBoxes[index]) - shortbox).ToString();
}
for (int i = 0; i < lotIds.Length; i++)
{
int lotSerial = 0;
if (String.IsNullOrEmpty(lotBoxes[i]))
{
lotSerial = i + 1;
continue;
}
lotSerial = i + 1;
insertQuery = "insert into " + lotDetailsTable + " values('" + lotIds[i] + "','" + lotBoxes[i] + "','" + G_ENTRY_ID + "','0','" + lotBoxes[i] + "','" + lotSerial + "','N');";
insertData(insertQuery, con);
Console.WriteLine("Lot_details_Query:" + insertQuery);
}
myTrans.Commit();
Console.WriteLine("All records are written to database.");
finalResult = true;
return(finalResult);
}
catch (Exception e)
{
finalResult = false;
try
{
myTrans.Rollback();
return(finalResult);
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() + " was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
return(finalResult);
}
finally
{
con.Close();
}
}