public bool Save(Sale newSale, string _WHCode)
{
int TempId = dc.CreateSale(newSale, _WHCode);
if (TempId > 0)
{
return true;
}
return false;
}
public Sale getBySaleCode(string _saleCode)
{
Sale sale = null;
try
{
Conn = db.openConnAccess();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,SaleCode,CCode,PQutation,DateSale,SaleBy,NetDc,NetVat,NetTotal FROM tbSale ");
sb.Append(" WHERE (SaleCode='" + _saleCode + "')");
string sql;
sql = sb.ToString();
com = new OleDbCommand();
com.CommandText = sql;
com.CommandType = CommandType.Text;
com.Connection = Conn;
dr = com.ExecuteReader();
if (dr.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dr);
int index = 1;
foreach (DataRow drw in dt.Rows)
{
sale = new Sale();
sale.ID = Convert.ToInt32(drw["ID"].ToString());
sale.Index = Convert.ToString(index);
sale.SaleCode = drw["SaleCode"].ToString();
Customer cus = serviceCustomer.getByCode(drw["CCode"].ToString());
sale.CustomerForSale = cus;
sale.PQutation = drw["PQutation"].ToString();
sale.DateSale =Convert.ToDateTime(drw["DateSale"].ToString());
sale.SaleBy = drw["SaleBy"].ToString();
sale.NetDc =Convert.ToDouble(drw["NetDc"].ToString());
sale.NetVat = Convert.ToDouble(drw["NetVat"].ToString());
sale.NetTotal = Convert.ToDouble(drw["NetTotal"].ToString());
IList<SaleDetail> saleDetails = this.getsaleDetailByCode(drw["SaleCode"].ToString());
sale.saleDetails = saleDetails;
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
Conn.Close();
return null;
throw ex;
}
finally
{
Conn.Close();
}
return sale;
}
public int CreateSale(Sale newSale, string _whCode)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbSale(SaleCode,CCode,PQutation,DateSale,SaleBy,NetDc,NetVat,NetTotal)");
sb.Append(" VALUES (@SaleCode,@CCode,@PQutation,@DateSale,@SaleBy,@NetDc,@NetVat,@NetTotal)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = newSale.SaleCode;
com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newSale.CustomerForSale.CCode;
com.Parameters.Add("@PQutation", OleDbType.VarChar).Value = newSale.PQutation;
string dateSale = String.Format("{0:dd/MM/yyyy}", newSale.DateSale);
com.Parameters.Add("@DateSale", OleDbType.Date).Value = dateSale;
com.Parameters.Add("@SaleBy", OleDbType.VarChar).Value = newSale.SaleBy;
com.Parameters.Add("@NetDc", OleDbType.VarChar).Value = newSale.NetDc;
com.Parameters.Add("@NetVat", OleDbType.VarChar).Value = newSale.NetVat;
com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = newSale.NetTotal;
com.ExecuteNonQuery();
foreach (SaleDetail r in newSale.saleDetails)
{
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbSaleDetail(SaleCode,PCode,PName,UCode,UName,Amount,SalePrice,NetTotal)");
sb.Append(" VALUES (@SaleCode,@PCode,@PName,@UCode,@UName,@Amount,@SalePrice,@NetTotal)");
string sqlSaveDetail;
sqlSaveDetail = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSaveDetail;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = r.SaleCode;
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode;
com.Parameters.Add("@PName", OleDbType.VarChar).Value = r.PName;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = r.UCode;
com.Parameters.Add("@UName", OleDbType.VarChar).Value = r.UName;
com.Parameters.Add("@Amount", OleDbType.VarChar).Value = r.Amount;
com.Parameters.Add("@SalePrice", OleDbType.VarChar).Value = r.SalePrice;
com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = r.NetTotal;
com.ExecuteNonQuery();
ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode);
if (pOnwarehouse != null)
{
Product p = serviceProduct.getByCode(r.PCode);
pOnwarehouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
pOnwarehouse.QtyWithUnit = u.QtyUnit;
int oldblance = pOnwarehouse.RealQty;
int newbalnce = oldblance - r.Amount;
pOnwarehouse.RealQty = newbalnce;
int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse);
if (tmp > 0)
{
Console.WriteLine("update pOnwarehouse Complate");
}
}
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtSearchCustomer.Text.Trim() == "")
{
MessageBox.Show("กรุณากรอกชื่อลูกค้าด้วย !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtSearchCustomer.Focus();
return;
}
int count = lsvProductList.Items.Count;
if (count == 0)
{
MessageBox.Show("กรุณาเลือกสินค้าด้วย !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtSearchProduct.Focus();
return;
}
if (MessageBox.Show("คุณต้องการขายสินค้า ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
try
{
Sale sale = serviceSale.getBySaleCode(txtSaleCode.Text.Trim());
if (sale == null)
{
Sale newSale = new Sale();
newSale.SaleCode = txtSaleCode.Text.Trim();
Customer cus = serviceCustomer.getByCode(txtSearchCustomer.Text.Trim());
newSale.CustomerForSale = cus;
newSale.PQutation = txtQutation.Text.Trim();
newSale.DateSale = dtpSale.Value;
newSale.SaleBy = "";//รอ ทำระบบ login ก่อน
newSale.NetDc = Convert.ToDouble(lblNetDC.Text.Trim());
newSale.NetVat =Convert.ToDouble(lblNetVAT.Text.Trim());
newSale.NetTotal = Convert.ToDouble(lblNet.Text.Trim());
IList<SaleDetail> saleDetails = new List<SaleDetail>();
SaleDetail saleDetail = null;
int j = 1;
for (int i = 0; i <= lsvProductList.Items.Count - 1; i++)
{
saleDetail = new SaleDetail();
saleDetail.Index = Convert.ToString(j);
saleDetail.SaleCode = txtSaleCode.Text.Trim();
saleDetail.PCode = lsvProductList.Items[i].SubItems[0].Text.Replace(",", "");
saleDetail.PName = lsvProductList.Items[i].SubItems[1].Text.Replace(",", "");
Product product = serviceProduct.getByCode(lsvProductList.Items[i].SubItems[0].Text.Replace(",", ""));
saleDetail.UCode = product.UCode;
saleDetail.UName = product.UName;
saleDetail.Amount= Convert.ToInt32(lsvProductList.Items[i].SubItems[3].Text.Replace(",", ""));
saleDetail.SalePrice = Convert.ToDouble(lsvProductList.Items[i].SubItems[2].Text.Replace(",", ""));
saleDetail.NetTotal = Convert.ToDouble(lsvProductList.Items[i].SubItems[4].Text.Replace(",", ""));
saleDetails.Add(saleDetail);
j++;
}
newSale.saleDetails = saleDetails;
bool save = serviceSale.Save(newSale, cboWarehouse.SelectedValue.ToString());
if (save)
{
MessageBox.Show("เพิ่มการขายสินค้า เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearProduct();
ClearNet();
lsvProductList.Items.Clear();
txtSearchProduct.Focus();
ClearCustomer();
txtSaleCode.Text = serviceSale.getSaleCode();
}
else
{
MessageBox.Show("ไม่สามารถ เพิ่มการขายสินค้าได้!!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else {
MessageBox.Show("ไม่สามารถ บันทึกการขายสินค้าได้ เนื่องจาก !!! มีหมายเลขการขายสินค้านี้แล้ว ", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex) {
MessageBox.Show("ไม่สามารถ บันทึกการขายสินค้าได้ เนื่องจาก !!! : " + ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}