protected void SaveButton_Click(object sender, EventArgs e)
{
SqlTransaction transaction = null;
var sql = _MainRepository.ConnectionString();
using (SqlConnection Sqlcon = new SqlConnection(sql))
{
Sqlcon.Open();
transaction = Sqlcon.BeginTransaction();
try
{
AccountSales _AccountSales = new AccountSales();
_AccountSales.Serial = txtSerial.Text;
_AccountSales.CashierName = txtCashierName.Text;
_AccountSales.CustomerName = txtCustomerName.Text;
_AccountSales.GrandTotal = Convert.ToDecimal(txtTotalCost.Text);
_AccountSales.Discount = Convert.ToDecimal(txtDiscount.Text);
_AccountSales.PaidAmount = Convert.ToDecimal(txtPaidAmount.Text);
_AccountSales.ChangesAmount = Convert.ToDecimal(lblChanges.Text);
SqlCommand command1 = new SqlCommand("Insert Into AccountSales(Serial,CashierName,CustomerName,GrandTotal,Discount,PaidAmount,ChangesAmount,Date) Values ('" + _AccountSales.Serial + "','" + _AccountSales.CashierName + "','" + _AccountSales.CustomerName + "','" + _AccountSales.GrandTotal + "','" + _AccountSales.Discount + "','" + _AccountSales.PaidAmount + "','" + _AccountSales.ChangesAmount + "','" + DateTime.Now.ToShortDateString() + "')", Sqlcon, transaction);
command1.CommandType = CommandType.Text;
command1.ExecuteNonQuery(); //AccountSale Save;
//ItemSales Start With foreach
DataTable dt = (DataTable)ViewState["Details"];
foreach (DataRow dr in dt.Rows)
{
ItemSales _ItemSales = new ItemSales();
_ItemSales.Name = dr["Name"].ToString();
_ItemSales.Qty = Convert.ToInt32(dr["Qty"].ToString());
_ItemSales.Per_Price = Convert.ToDecimal(dr["Perprice"].ToString());
_ItemSales.Sub_Price = Convert.ToDecimal(dr["Subprice"].ToString());
_ItemSales.Serial = txtSerial.Text;
SqlCommand command = new SqlCommand("Insert Into ItemSales(Name,Qty,Per_Price,Sub_Price,Serial,Date) Values ('" + _ItemSales.Name + "','" + _ItemSales.Qty + "','" + _ItemSales.Per_Price + "','" + _ItemSales.Sub_Price + "','" + _ItemSales.Serial + "','" + DateTime.Now.ToShortDateString() + "')", Sqlcon, transaction);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
transaction.Commit();
Response.Redirect(Request.Url.AbsoluteUri);
//CreatePdf();
}
catch
{
transaction.Rollback();
}
finally
{
Sqlcon.Close();
}
}
}
public static List <ItemSales> GetItemSales(DateTime time, int type)
{
List <ItemSales> list = new List <ItemSales>();
var sql = @"SELECT ItemSalesId,ItemMasterId,M.ItemId,S.ItemName,CustomerId,SalesType,ItemType,SalesCount,StockPrice,SoldPirce,S.UpdateTime FROM ItemSales AS S
INNER JOIN ItemMaster AS M
ON S.ItemMasterId = M.Id";
if (type == 0)
{
sql += " WHERE DATEPART(dd, S.UpdateTime) = '" + time.Day + "' AND DATEPART(m, S.UpdateTime) = '" + time.Month + "' AND DATEPART(yyyy, S.UpdateTime) = '" + time.Year + "'";
}
else if (type == 1)
{
sql += " WHERE DATEPART(m, S.UpdateTime) = '" + time.Month + "' AND DATEPART(yyyy, S.UpdateTime) = '" + time.Year + "'";
}
else
{
sql += " WHERE DATEPART(yyyy,S.UpdateTime) = '" + time.Year + "'";
}
sql += " ORDER BY UpdateTime DESC";
DataTable dt = new DataTable();
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
conn.Open();
SqlCeCommand command = new SqlCeCommand(sql, conn);
//var parameters = new[]
//{
// new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50) { Value = customerId }
//};
//command.Parameters.AddRange(parameters);
using (SqlCeDataReader dataReader = command.ExecuteReader())
{
dt.Load(dataReader);
}
conn.Close();
}
foreach (DataRow dr in dt.Rows)
{
var item = new ItemSales();
item.ItemSalesId = dr["ItemSalesId"].ToString();
item.ItemId = dr["ItemId"].ToString();
item.ItemName = dr["ItemName"].ToString();
item.ItemType = int.Parse(dr["ItemType"].ToString());
item.SalesType = int.Parse(dr["SalesType"].ToString());
item.SalesCount = int.Parse(dr["SalesCount"].ToString());
item.StockPrice = decimal.Parse(dr["StockPrice"].ToString());
item.SoldPirce = decimal.Parse(dr["SoldPirce"].ToString());
item.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
list.Add(item);
}
return(list);
}
private void btn_itemsales_Click(object sender, EventArgs e)
{
today = Convert.ToDateTime(DateTime.Today.ToShortDateString());
DataTable dt = customer.GetItems(txt_searchbox.Text, today);
ItemSales item = new ItemSales();
if (dt.Rows.Count > 0)
{
for (int inc = 0; inc < dt.Rows.Count; inc++)
{
int ww = item.dataGridView1.Rows.Add();
item.dataGridView1.Rows[inc].Cells["calitem"].Value = dt.Rows[inc]["item_name"].ToString();
item.dataGridView1.Rows[inc].Cells["calquantity"].Value = dt.Rows[inc]["quantity"].ToString();
item.dataGridView1.Rows[inc].Cells["caltotal"].Value = dt.Rows[inc]["total"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_cost"].Value = dt.Rows[inc]["cost"].ToString();
item.dataGridView1.Rows[inc].Cells["caldate"].Value = dt.Rows[inc]["date_of_sale"].ToString();
item.dataGridView1.Rows[inc].Cells["calcardamount"].Value = dt.Rows[inc]["card_amount"].ToString();
item.dataGridView1.Rows[inc].Cells["calcashamt"].Value = dt.Rows[inc]["cash_amount"].ToString();
item.dataGridView1.Rows[inc].Cells["calpaymode"].Value = dt.Rows[inc]["payment_mode"].ToString();
item.dataGridView1.Rows[inc].Cells["calcashier_name"].Value = dt.Rows[inc]["cashier_name"].ToString();
string bill_no = dt.Rows[inc]["bill_no"].ToString();
item.dataGridView1.Rows[inc].Cells["calbill_no"].Value = bill_no;
DateTime date_of_billing = Convert.ToDateTime(dt.Rows[inc]["date2"].ToString());
item.dataGridView1.Rows[inc].Cells["caldate"].Value = Convert.ToDateTime(dt.Rows[inc]["date_of_sale"].ToString());
DataTable dt_bill = blbt.get_all_tax_by_bill_no(bill_no, date_of_billing);
if (dt_bill.Rows.Count > 0)
{
item.dataGridView1.Rows[inc].Cells["cal_tax"].Value = dt_bill.Rows[0]["tax_amount"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_service_charge"].Value = dt_bill.Rows[0]["service_charge"].ToString();
}
else
{
item.dataGridView1.Rows[inc].Cells["cal_tax"].Value = "0.00";
item.dataGridView1.Rows[inc].Cells["cal_service_charge"].Value = "0.00";
}
item.dataGridView1.Rows[inc].Cells["cal_grand_total"].Value = dt.Rows[inc]["grand_total"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_sales_type"].Value = dt.Rows[inc]["sales_type"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_sales_id"].Value = dt.Rows[inc]["rest_sale_id"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_table_no"].Value = dt.Rows[inc]["table_no"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_category_name"].Value = dt.Rows[inc]["category_name"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_discount"].Value = dt.Rows[inc]["discount"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_sub_total"].Value = dt.Rows[inc]["sub_total"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_date2"].Value = dt.Rows[inc]["date2"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_customer_pan"].Value = dt.Rows[inc]["customer_no"].ToString();
item.dataGridView1.Rows[inc].Cells["cal_customer_name"].Value = dt.Rows[inc]["customer_name"].ToString();
}
item.lbl_card_no.Text = txt_searchbox.Text;
item.Show();
}
else
{
MessageBox.Show("Data is not available", "Data Empty !!");
}
}
/// <summary>
/// GetItemSales
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public static List <ItemSales> GetItemSales(string customerId)
{
List <ItemSales> list = new List <ItemSales>();
var sql = @"SELECT ItemSalesId,ItemMasterId,M.ItemId,S.ItemName,CustomerId,SalesType
, SalesCount, SoldPirce, S.UpdateTime FROM ItemSales AS S
INNER JOIN ItemMaster AS M
ON S.ItemMasterId = M.Id
WHERE CustomerId = @CustomerId
ORDER BY UpdateTime DESC";
DataTable dt = new DataTable();
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
conn.Open();
SqlCeCommand command = new SqlCeCommand(sql, conn);
var parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = customerId
}
};
command.Parameters.AddRange(parameters);
using (SqlCeDataReader dataReader = command.ExecuteReader())
{
dt.Load(dataReader);
}
conn.Close();
}
foreach (DataRow dr in dt.Rows)
{
var item = new ItemSales();
item.ItemSalesId = dr["ItemSalesId"].ToString();
item.ItemId = dr["ItemId"].ToString();
item.ItemName = dr["ItemName"].ToString();
item.SalesType = int.Parse(dr["SalesType"].ToString());
item.SalesCount = int.Parse(dr["SalesCount"].ToString());
item.SoldPirce = decimal.Parse(dr["SoldPirce"].ToString());
list.Add(item);
}
return(list);
}
public List <ItemSales> GetAllReport(string serial)
{
var _ItemSalesList = new List <ItemSales>();
string query = ("Select *from ItemSales where Serial='" + serial + "' And Date='" + DateTime.Now.ToShortDateString() + "' ");
var reader = _MainRepository.Reader(query, _MainRepository.ConnectionString());
if (reader.HasRows)
{
while (reader.Read())
{
var _ItemSales = new ItemSales();
_ItemSales.Name = reader["Name"].ToString();
_ItemSales.Qty = Convert.ToInt32(reader["Qty"].ToString());
_ItemSales.Per_Price = Convert.ToDecimal(reader["Per_Price"].ToString());
_ItemSales.Sub_Price = Convert.ToDecimal(reader["Sub_Price"].ToString());
_ItemSalesList.Add(_ItemSales);
}
}
reader.Close();
return(_ItemSalesList);
}
/// <summary>
///
/// </summary>
/// <param name="window"></param>
public SalesWindowViewModel(Window window)
{
//ItemSalesColletion.CollectionChanged += (s, e) =>
//{
// if (e.Action != System.Collections.Specialized.NotifyCollectionChangedAction.Reset)
// {
// decimal amount = 0;
// foreach (ItemSales item in e.NewItems)
// {
// item.PropertyChanged += (ss, ee) =>
// {
// if (ee.PropertyName == "SoldPirce")
// {
// var sss = (ItemSales)ss;
// }
// };
// amount += item.SoldPirce;
// MessageBox.Show(amount.ToString());
// }
// }
//};
SalesDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
Timer timer = new Timer(1000);
timer.Elapsed += (s, e) => { SalesDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); };
timer.Start();
WINDOW = window;
LogoutCommand = new DelegateCommand(Execute, () => { return(true); });
CloseCommand = new DelegateCommand(() => { Application.Current.Shutdown(); });
SelectItemMasterCommand = new DelegateCommand(() =>
{
SelectItemMasterWindow w = new SelectItemMasterWindow();
SelectItemMasterViewModel model = new SelectItemMasterViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
//SOURCE = new ObservableCollection<ItemSales>(model.CheckedList);
SOURCE.Clear();
model.CheckedList.ForEach(s =>
{
SOURCE.Add(new ItemSales
{
Id = s.Id,
ItemId = s.ItemId,
ItemName = s.ItemName,
ItemSize = s.ItemSize,
ItemType = s.ItemType,
StockCount = s.StockCount,
SalesType = 1,
SalesCount = 1,
StockPrice = s.StockPrice,
Price = s.Price,
SoldPirce = s.Price
});
});
//ItemSalesColletion = new ObservableCollection<ItemSales>(SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE));
ItemSalesColletion.Clear();
SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE).ToList().ForEach(ItemSalesColletion.Add);
});
SelectCustomerCommand = new DelegateCommand(() =>
{
SelectCustomerWindow w = new SelectCustomerWindow();
SelectCustomerViewModel model = new SelectCustomerViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
if (model.CheckedList != null)
{
CustomerId = model.CheckedList.Id;
CustomerName = model.CheckedList.Name;
Phone = model.CheckedList.Phone;
IM = model.CheckedList.IM;
Deposit = model.CheckedList.Deposit;
DiscountRate = model.CheckedList.Discount;
ItemSalesColletion.ToList().ForEach(s =>
{
s.SoldPirce = s.SoldPirce * DiscountRate / 100;
});
if (SelectedItemSales != null)
{
SoldPirce = SelectedItemSales.SoldPirce;
}
}
});
ClearCustomerCommand = new DelegateCommand(() =>
{
ClearCustomer();
ItemSalesColletion.ToList().ForEach(s =>
{
s.SoldPirce = s.Price * s.SalesCount;
});
if (SelectedItemSales != null)
{
SoldPirce = SelectedItemSales.Price * Count;
}
DiscountRate = 100;
});
SelectCommand = new DelegateCommand <ItemSales>(s =>
{
if (s != null)
{
SelectedItemSales = s;
ItemId = s.ItemId;
ItemName = s.ItemName;
ItemSize = s.ItemSize;
ItemType = s.ItemType;
StockPrice = s.StockPrice;
Price = s.Price;
Count = s.SalesCount;
SliderMaximum = s.StockCount;
SoldPirce = s.SoldPirce;
}
});
RemoveCommand = new DelegateCommand <ItemSales>(s =>
{
SOURCE.Remove(s);
ItemSalesColletion = new ObservableCollection <ItemSales>(SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE));
ItemId = "";
ItemName = "";
ItemSize = 0;
ItemType = 0;
StockPrice = 0;
Price = 0;
Count = 1;
});
//ItemMasterColletion = new ObservableCollection<ItemMaster>(SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE));
PreviousCommand = new DelegateCommand(() =>
{
PageIndex--;
ItemSalesColletion = new ObservableCollection <ItemSales>(SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE));
}, () => { return(PageIndex > 0 ? true : false); });
NextCommand = new DelegateCommand(() =>
{
PageIndex++;
ItemSalesColletion = new ObservableCollection <ItemSales>(SOURCE.Skip(PageIndex * PAGESIZE).Take(PAGESIZE));
}, () => { return((PageIndex + 1) * PAGESIZE < SOURCE.Count() ? true : false); });
SalesItemCommand = new DelegateCommand(() =>
{
SalesItemWindow w = new SalesItemWindow();
SalesItemViewModel model = new SalesItemViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
});
CustomerCommand = new DelegateCommand(() =>
{
CustomerWindow w = new CustomerWindow();
CustomerViewModel model = new CustomerViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
}, () => { return(string.IsNullOrEmpty(CustomerName)); });
StockCommand = new DelegateCommand(() =>
{
StockWindow w = new StockWindow();
StockViewModel model = new StockViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
}, () => { return(ItemSalesColletion == null || ItemSalesColletion.Count == 0); });
VoidCommand = new DelegateCommand(() =>
{
VoidWindow w = new VoidWindow();
VoidWindowViewModel model = new VoidWindowViewModel(w);
w.DataContext = model;
w.Owner = App.Current.MainWindow;
w.ShowDialog();
});
SoldCommand = new DelegateCommand(() =>
{
StringBuilder message = new StringBuilder();
if (!string.IsNullOrEmpty(CustomerName))
{
var ned = Deposit - ItemSalesColletion.Sum(s => s.SoldPirce);
if (ned < 0)
{
message.Append("注意！！！");
message.Append(Environment.NewLine);
message.Append("客户余额不足，还差：" + ned + "元");
message.Append(Environment.NewLine);
}
}
message.Append("是否确认售出？");
if (MessageBox.Show(window, message.ToString(), "确认售出点“Yes”，否则点“No”", MessageBoxButton.YesNo, MessageBoxImage.Warning, MessageBoxResult.Yes) == MessageBoxResult.Yes)
{
if (!string.IsNullOrEmpty(CustomerId))
{
ItemSalesColletion.ToList().ForEach(s => { s.CustomerId = CustomerId; s.DepositForUpdate = Deposit; });
}
if (SqlServerCompactService.InsertItemSales(ItemSalesColletion.ToList()))
{
message.Clear();
message.Append("本次共计售出：" + ItemSalesColletion.Sum(s => s.SalesCount) + "件，赚得：" + (ItemSalesColletion.Sum(s => s.SoldPirce) - ItemSalesColletion.Sum(s => s.StockPrice * s.SalesCount)) + "元！");
MessageBox.Show(message.ToString());
ClearItem();
ClearCustomer();
ItemSalesColletion.Clear();
}
else
{
message.Clear();
message.Append("销售失败，请重试！！！");
MessageBox.Show(message.ToString());
}
}
}, () => { return(ItemSalesColletion != null && ItemSalesColletion.Count > 0); });
}
public int Add(ItemSales _ItemSales)
{
string query = "Insert Into ItemSales(Name,Qty,Per_Price,Sub_Price,Serial,Date) Values ('" + _ItemSales.Name + "','" + _ItemSales.Qty + "','" + _ItemSales.Per_Price + "','" + _ItemSales.Sub_Price + "','" + _ItemSales.Serial + "','" + DateTime.Now.ToShortDateString() + "')";
return(_MainRepository.ExecuteNonQuery(query, _MainRepository.ConnectionString()));
}
public int Update(ItemSales _ItemSales)
{
string query = "Update ItemSales SET Qty='" + _ItemSales.Qty + "',Sub_Price='" + _ItemSales.Sub_Price + "' where Id='" + _ItemSales.Id + "' ";
return(_MainRepository.ExecuteNonQuery(query, _MainRepository.ConnectionString()));
}
