public List <SalesHead> getSalheadList()
{
List <SalesHead> salHeadList = new List <SalesHead>();
connection();
SqlCommand cmd = new SqlCommand("SELECT * FROM tbl_sal_head", con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
SalesHead salHead = default(SalesHead);
while (rdr.Read())
{
salHead = Activator.CreateInstance <SalesHead>();
foreach (PropertyInfo prop in salHead.GetType().GetProperties())
{
if (!object.Equals(rdr[prop.Name], DBNull.Value))
{
prop.SetValue(salHead, rdr[prop.Name], null);
}
}
salHeadList.Add(salHead);
}
rdr.Close();
con.Close();
return(salHeadList);
}
public SalesInvoice getSalInvoiceDetail(int?id, string type)
{
SalesInvoice salInvoice = new SalesInvoice();
if (id == null || type == null)
{
return(salInvoice);
}
List <SalesItem> salItemList = new List <SalesItem>();
connection();
string selectionQuery = "SELECT * FROM tbl_sal_head where docNum = '" + id + "' and docType = '" + type + "'";
string selectionQuery2 = "SELECT * FROM tbl_sal_item where docNum = '" + id + "' and docType = '" + type + "'";
SqlCommand cmd = new SqlCommand(selectionQuery, con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
SalesHead salesHead = default(SalesHead);
if (dr.Read())
{
salesHead = Activator.CreateInstance <SalesHead>();
foreach (PropertyInfo prop in salesHead.GetType().GetProperties())
{
if (!object.Equals(dr[prop.Name], DBNull.Value))
{
prop.SetValue(salesHead, dr[prop.Name], null);
}
}
salInvoice.salesHead = salesHead;
dr.Close();
SqlCommand cmd2 = new SqlCommand(selectionQuery2, con);
cmd2.CommandType = CommandType.Text;
SqlDataReader rdr = cmd2.ExecuteReader();
SalesItem salesItem = default(SalesItem);
while (rdr.Read())
{
salesItem = Activator.CreateInstance <SalesItem>();
foreach (PropertyInfo prop in salesItem.GetType().GetProperties())
{
if (!object.Equals(rdr[prop.Name], DBNull.Value))
{
prop.SetValue(salesItem, rdr[prop.Name], null);
}
}
salItemList.Add(salesItem);
}
salInvoice.salesItemList = salItemList;
rdr.Close();
}
con.Close();
return(salInvoice);
}
public SalesGeneral UpdateSalesInvoice(SalesGeneral salGen, SalesHead oldh, List <SalesItem> oldit)
{
connection();
con.Open();
var date = "";
if (salGen.salesHead.address == null)
{
salGen.salesHead.address = oldh.address;
}
if (salGen.salesHead.city == null)
{
salGen.salesHead.city = oldh.city;
}
if (salGen.salesHead.country == null)
{
salGen.salesHead.country = oldh.country;
}
if (salGen.salesHead.currency == null)
{
salGen.salesHead.currency = oldh.currency;
}
if (salGen.salesHead.custName1 == null)
{
salGen.salesHead.custName1 = oldh.custName1;
}
if (salGen.salesHead.custName2 == null)
{
salGen.salesHead.custName2 = oldh.custName2;
}
if (salGen.salesHead.docDate == null)
{
date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
date = salGen.salesHead.docDate.ToString("yyyy-MM-dd HH:mm:ss");
}
if (salGen.salesHead.docType == null)
{
salGen.salesHead.docType = oldh.docType;
}
if (salGen.salesHead.email == null)
{
salGen.salesHead.email = oldh.email;
}
if (salGen.salesHead.hCurrency == null)
{
salGen.salesHead.hCurrency = oldh.hCurrency;
}
// --todo dochead and item existing check
// --todo new item insert and delete item feature
if (salGen.salesHead.docNum == 0)
{
salGen.resultUpdated = false;
return(salGen);
}
salGen.salesHead.discount = 0;
salGen.salesHead.subTotal = 0;
for (int x = 0; x < salGen.salesItemList.Count; x++)
{
if (salGen.salesItemList[x].currency == null)
{
salGen.salesItemList[x].currency = oldit[x].currency;
}
if (salGen.salesItemList[x].material == null)
{
salGen.salesItemList[x].material = oldit[x].material;
}
if (salGen.salesItemList[x].unit == null)
{
salGen.salesItemList[x].unit = oldit[x].unit;
}
if (salGen.salesItemList[x].variantOptions == null)
{
salGen.salesItemList[x].variantOptions = oldit[x].variantOptions;
}
if (salGen.salesItemList[x].price <= 0)
{
salGen.salesItemList[x].price = oldit[x].price;
}
if (salGen.salesItemList[x].quantity <= 0)
{
salGen.salesItemList[x].quantity = oldit[x].quantity;
}
salGen.salesItemList[x].docType = salGen.salesHead.docType;
salGen.salesItemList[x].docNum = salGen.salesHead.docNum;
salGen.salesItemList[x].subTotal = salGen.salesItemList[x].quantity * salGen.salesItemList[x].price;
salGen.salesItemList[x].grandTotal = salGen.salesItemList[x].subTotal - salGen.salesItemList[x].discount;
salGen.salesHead.discount = salGen.salesHead.discount + salGen.salesItemList[x].discount;
salGen.salesHead.subTotal = salGen.salesHead.subTotal + salGen.salesItemList[x].subTotal;
}
salGen.salesHead.grandTotal = salGen.salesHead.subTotal - salGen.salesHead.discount;
string selectionQuery = "UPDATE tbl_sal_head SET address ='" + salGen.salesHead.address
+ "', city ='" + salGen.salesHead.city
+ "',country ='" + salGen.salesHead.country
+ "',currency='" + salGen.salesHead.currency
+ "',custName1='" + salGen.salesHead.custName1
+ "',custName2='" + salGen.salesHead.custName2
+ "',discount=" + salGen.salesHead.discount
+ ",email='" + salGen.salesHead.email
+ "',grandTotal=" + salGen.salesHead.grandTotal
+ ",phone=" + salGen.salesHead.phone
+ ",subTotal=" + salGen.salesHead.subTotal
+ " Where docNum =" + salGen.salesHead.docNum
+ " and docType='" + salGen.salesHead.docType + "'"
;
SqlCommand cmd = new SqlCommand(selectionQuery, con);
// con.Open();
int i = cmd.ExecuteNonQuery();
bool result = false;
if (i >= 1)
{
foreach (var item in salGen.salesItemList)
{
string selectionQuery2 = "Update tbl_sal_item SET currency='" +
item.currency
+ "',discount=" + item.discount
+ ",grandTotal=" + item.grandTotal
+ ",material='" + item.material
+ "',price=" + item.price
+ ",quantity=" + item.quantity
+ ",subTotal=" + item.subTotal
+ ",unit='" + item.unit
+ "',variantOptions='" + item.variantOptions
+ "' Where docNum =" + item.docNum
+ " and docType='" + item.docType + "'"
+ " and itemNum=" + item.itemNum
;
SqlCommand cmd2 = new SqlCommand(selectionQuery2, con);
int i2 = cmd2.ExecuteNonQuery();
//to do item-head delete fail case rollback,begin and commit tran.
}
result = true;
}
else
{
result = false;
}
con.Close();
salGen.resultUpdated = result;
return(salGen);
}
