public CDMarzoAutoShopMethods()
{
customerinfobll = new CustomerInfoDAL();
itembll = new ItemDAL();
orderinfobll = new OrderInfoDAL();
salesbll = new SalesDAL();
}
private void SetCustomerDg2()
{
// Get customers that has no sales assign to from ForecastOrder table
DataTable dt = ForecastOrderBLL.getCustomerWithNoSalesFo();
if (dt.Rows.Count != 0)
{
dgCustomerData2.ItemsSource = dt.DefaultView;
// Set sales dropdown in datagrid
dt = SalesBLL.getSalesAll();
var Sales = new List <string>();
Sales.Add("==請選擇業務==");
foreach (DataRow dr in dt.Rows)
{
Sales.Add(dr[0].ToString() + "-" + dr[1].ToString());
}
dgCbSales2.ItemsSource = Sales;
btnSave2.IsEnabled = true;
}
else
{
btnSave2.IsEnabled = false;
}
dt = null;
}
private void btnExportReport_Click(object sender, RoutedEventArgs e)
{
// Create report
ReportsBLL.calculateReport(Convert.ToInt32(this.lbReportYear.Content));
// Insert data into excel sheet
using (ExcelPackage p = new ExcelPackage())
{
// Here setting some document properties
p.Workbook.Properties.Author = "YM";
p.Workbook.Properties.Title = "Sales Monthly Report";
// Create a sheet
p.Workbook.Worksheets.Add("WorkSheet");
ExcelWorksheet ws = p.Workbook.Worksheets[1];
// Write to excel sheet 1
// sheet 1 table 1 = s1t1, sheet 1 table 2 = s1t2, sheet 1 table 3 = s1t3
int currentRow = 1;
currentRow = writeToExcel("s1t1", currentRow, ws, "");
currentRow = writeToExcel("s1t2", currentRow, ws, "");
writeToExcel("s1t3", currentRow, ws, "");
// Write to excel sheet 2
currentRow = 1;
p.Workbook.Worksheets.Add("WorkSheet2");
ExcelWorksheet ws2 = p.Workbook.Worksheets[2];
p.Workbook.Worksheets.Add("WorkSheet3");
ExcelWorksheet ws3 = p.Workbook.Worksheets[3];
DataTable dt = SalesBLL.getSalesAll();
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
currentRow = writeToExcel("s2", currentRow, ws2, dr["Id"].ToString());
}
// Write s2 Total
writeToExcel("s2", currentRow + 2, ws2, "");
currentRow = 1;
foreach (DataRow dr in dt.Rows)
{
currentRow = writeToExcel("s3", currentRow, ws3, dr["Id"].ToString());
}
// Write s3 Total
writeToExcel("s3", currentRow + 2, ws3, "");
}
// Generate A File with Random name
Byte[] bin = p.GetAsByteArray();
//string file = "d:\\" + Guid.NewGuid().ToString() + ".xlsx";
string savePath = saveFile();
string file = savePath;
File.WriteAllBytes(file, bin);
System.Windows.Forms.MessageBox.Show("檔案儲存完畢", "Message");
}
}
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
if (!string.IsNullOrWhiteSpace(tbSalesId.Text.Trim()) && !string.IsNullOrWhiteSpace(tbSalesName.Text.Trim()))
{
SalesBEL salesBEL = new SalesBEL();
salesBEL.Id = tbSalesId.Text.Trim();
salesBEL.Name = tbSalesName.Text.Trim();
salesBEL.IsEnable = "1";
SalesBLL.insertSales(salesBEL);
SetCurrentSales();
}
}
public Sale MakeNewSale(Sale data, List <SaleDetail> saleDetails)
{
var salesBLL = new SalesBLL();
var salesDetailBLL = new SalesDetailBLL();
var newSale = salesBLL.MakeSale(data);
foreach (var item in saleDetails)
{
item.Id_sale = newSale.Id_sales;
salesDetailBLL.MakeSale(item);
}
return(newSale);
}
private void SetCurrentSales()
{
DataTable dt = SalesBLL.getSalesAll();
dgCurrentSales.ItemsSource = dt.DefaultView;
cbSales01.Items.Clear();
cbSales02.Items.Clear();
foreach (DataRow dr in dt.Rows)
{
cbSales01.Items.Add(dr[0].ToString() + "-" + dr[1].ToString());
cbSales02.Items.Add(dr[0].ToString() + "-" + dr[1].ToString());
}
cbSales01.SelectedIndex = 0;
cbSales02.SelectedIndex = 0;
dt = null;
}
/// <summary>
/// 更新销售相关表
/// </summary>
/// <param name="salesList"></param>
/// <returns></returns>
public bool UpdateSalesData(List <Sales> salesList)
{
#region 更新销售相关表
//先清空单次销售明细数据
if (new SalesBLL().DeleteSalesTime() < 0)
{
MessageBox.Show("数据清空出错，请重试");
Log.WriteErrorLog("单次销售明细数据清空失败");
return(false);
}
//保存数据到单次销售明细表中，true 成功，false 失败
bool result = new SalesBLL().AddSalesTime(salesList);
//单次销售明细表插入出现错误，清空数据
if (!result)
{
MessageBox.Show("单次销售数据保存出错，请重试");
Log.WriteErrorLog("单次销售明细数据保存出错");
if (new SalesBLL().DeleteSalesTime() < 0)
{
MessageBox.Show("数据清空出错");
Log.WriteErrorLog("单次销售明细数据清空失败");
}
return(false);
}
//保存数据到单日和总的销售明细表中，使用事务，失败就回滚
if (!new SalesBLL().AddSalesData())
{
MessageBox.Show("销售数据保存出错，请重试");
Log.WriteErrorLog("销售明细数据保存出错");
if (new SalesBLL().DeleteSalesTime() < 0)
{
MessageBox.Show("数据清空出错，请重试");
Log.WriteErrorLog("单次销售明细数据清空失败");
}
return(false);
}
#endregion
return(true);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (detail.SalesID == 0)
{
MessageBox.Show("Please select asales from table");
}
else
{
FrmSales frm = new FrmSales();
frm.isUpdate = true;
frm.detail = detail;
frm.dto = dto;
this.Hide();
frm.ShowDialog();
this.Visible = true;
bll = new SalesBLL();
dto = bll.Select();
dataGridView1.DataSource = dto.Sales;
CleanFilters();
}
}
public HttpResponseMessage UpdateStatus([FromBody] Sale sale)
{
var response = new APIResponse <Sale>();
try
{
var updateLogic = new SalesBLL();
var newStatus = updateLogic.UpdateSale(sale);
response.Data = newStatus;
response.Message = "Status changed correctly";
response.HttpStatusCode = HttpStatusCode.OK;
}
catch (Exception e)
{
response.HttpStatusCode = HttpStatusCode.InternalServerError;
response.Message = "Error updating sale";
response.HasError = true;
}
return(Request.CreateResponse(response.HttpStatusCode, response));
}
private void Initialize()
{
// Set year dropdown
int year = DateTime.Now.Year + 1;
for (int i = 0; i < 3; i++)
{
cbYear.Items.Add((year - i).ToString());
}
cbYear.SelectedIndex = 0;
// Set sales dropdown
DataTable dt = SalesBLL.getSalesAll();
foreach (DataRow dr in dt.Rows)
{
cbSales.Items.Add(dr[0].ToString() + "-" + dr[1].ToString());
}
cbSales.SelectedIndex = 0;
dt = null;
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (detail.SalesID == 0)
{
MessageBox.Show("Please select a sales from table");
}
else
{
DialogResult result = MessageBox.Show("Are you sure?", "Warning!!", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
if (bll.Delete(detail))
{
MessageBox.Show("Sales was deleted");
bll = new SalesBLL();
dto = bll.Select();
dataGridView1.DataSource = dto.Sales;
CleanFilters();
}
}
}
}
private void btnDeleteSsales_Click(object sender, RoutedEventArgs e)
{
try
{
DataRowView rowview = dgCurrentSales.SelectedItem as DataRowView;
string salesId = rowview.Row["Id"].ToString();
DataTable dt = CustomerBLL.getCustomerBySales(salesId);
if (dt.Rows.Count == 0)
{
SalesBLL.updateSalesIsEnable(salesId, "0");
SetCurrentSales();
}
else
{
System.Windows.Forms.MessageBox.Show("該業務尚有客戶，請先轉移客戶至其他業務人員", "Message");
}
}
catch
{
System.Windows.Forms.MessageBox.Show("請先選擇要刪除的業務人員", "Message");
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (detail.ProductID == 0)
{
MessageBox.Show("Please select a product from product table");
}
else if (detail.CustomerID == 0)
{
MessageBox.Show("Please select a customer");
}
else if (detail.StockAmount < Convert.ToInt32(txtProductSalesAmount.Text))
{
MessageBox.Show("You have not enough product for sale");
}
else
{
detail.SalesAmount = Convert.ToInt32(txtProductSalesAmount.Text);
detail.SalesDate = DateTime.Today;
if (bll.Insert(detail))
{
MessageBox.Show("Sales was Added");
bll = new SalesBLL();
dto = bll.Select();
gridProduct.DataSource = dto.Products;
gridCustomer.DataSource = dto.Customers;
comboFull = false;
cbCategory.DataSource = dto.Categories;
if (dto.Products.Count > 0)
{
comboFull = true;
}
txtProductSalesAmount.Clear();
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (detail.ProductID == 0)
{
MessageBox.Show("Please select a product");
}
else
{
if (!isUpdate)
{
if (detail.CustomerID == 0)
{
MessageBox.Show("Please select a customer ID");
}
else if (detail.CustomerID == 0)
{
MessageBox.Show("Select a customer from the table");
}
else if (detail.StockAmount < Convert.ToInt32(txtProductStock.Text))
{
MessageBox.Show("Stock is not enough");
}
else
{
detail.SalesAmount = Convert.ToInt32(txtSalesAmount.Text);
detail.SalesDate = DateTime.Today;
if (bll.Insert(detail))
{
MessageBox.Show("Sles was added");
bll = new SalesBLL();
dto = bll.Select();
GridProduct.DataSource = dto.Products;
combofull = false;
cmbCategory.DataSource = dto.Categories;
if (dto.Products.Count > 0)
{
combofull = true;
}
txtSalesAmount.Clear();
}
}
}
else
{
if (detail.SalesAmount == Convert.ToInt32(txtSalesAmount.Text))
{
MessageBox.Show("There is not changes");
}
else
{
int temp = detail.StockAmount + detail.SalesAmount;
if (temp < Convert.ToInt32(txtSalesAmount.Text))
{
MessageBox.Show("You don have enoigh stock");
}
else
{
detail.SalesAmount = Convert.ToInt32(txtSalesAmount.Text);
detail.StockAmount = temp - detail.SalesAmount;
if (bll.Update(detail))
{
MessageBox.Show("Sales was updated");
this.Close();
}
}
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (txtProductSalesAmount.Text.Trim() == "")
{
MessageBox.Show("Please fill the sales amount area");
}
else
{
if (!isUpdate)//Add
{
if (detail.ProductID == 0)
{
MessageBox.Show("Please select a product from product table");
}
else if (detail.CustomerID == 0)
{
MessageBox.Show("Please select a customer from customer table");
}
else if (detail.StockAmount < Convert.ToInt32(txtProductSalesAmount.Text))
{
MessageBox.Show("You have bot enough product for sale");
}
else
{
detail.SalesAmount = Convert.ToInt32(txtProductSalesAmount.Text);
detail.SalesDate = DateTime.Today;
if (bll.Insert(detail))
{
MessageBox.Show("Sales was added");
bll = new SalesBLL();
dto = bll.Select();
gridProduct.DataSource = dto.Products;
dto.Customers = dto.Customers;
combofull = false;
cmbCategory.DataSource = dto.Categories;
if (dto.Products.Count > 0)
{
combofull = true;
}
txtProductSalesAmount.Clear();
}
}
}
else//Update
{
if (detail.SalesAmount == Convert.ToInt32(txtProductSalesAmount.Text))
{
MessageBox.Show("There is no chnage");
}
else
{
int temp = detail.StockAmount + detail.SalesAmount;
if (temp < Convert.ToInt32(txtProductSalesAmount.Text))
{
MessageBox.Show("You have not enough product for sale");
}
else
{
detail.SalesAmount = Convert.ToInt32(txtProductSalesAmount.Text);
detail.StockAmount = temp - detail.SalesAmount;
if (bll.Update(detail))
{
MessageBox.Show("Sales was Updated");
this.Close();
}
}
}
}
}
}
/// <summary>
/// 正常状态（文件夹没有
/// , control.tab, error.tab），更新本地 xsxb.dbf
/// </summary>
/// <returns></returns>
public static int EmptyOperation(string source)
{
int ret = 0;
int r = 0;
int unsureDataNumber = new SalesBLL().SelectSalesUnsure();
if (unsureDataNumber < 0)
{
ret = -3;
Log.WriteErrorLog("查询 unsure 表数据出错", source, "EmptyOperation()");
}
else
{
//unsure 表有数据，说明 backups 里是延迟上传的数据
if (unsureDataNumber > 0)
{
//文件夹 empty 说明 unsure 表保存的数据已经成功回收，要清空
if (new SalesBLL().DeleteSalesUnsure() > 0)
{
Log.WriteNormalLog("清空 unsure 表", "EmptyOperation()", source);
}
else
{
Log.WriteErrorLog("清空 unsure 表", source, "EmptyOperation()");
}
//将新数据追加到 backups 里
r = new SalesBLL().TimeToBackups();
if (r > 0)
{
ret = 9;
Log.WriteNormalLog("新数据追加到 backups 表", "EmptyOperation()", source);
}
else if (r < 0)
{
Log.WriteErrorLog("新数据追加到 backups 表", source, "EmptyOperation()");
}
else
{
ret = 10;//没有新数据可追加
}
}
//unsure 表没有数据，说明 backups 里是已上传的数据，已被成功回收
else
{
//覆盖新数据到 backups 表
r = new SalesBLL().TimeCoverBackups();
if (r > 0)
{
ret = 11;
Log.WriteNormalLog("新数据覆盖 backups 表", "EmptyOperation()", source);
}
else if (r < 0)
{
Log.WriteErrorLog("新数据覆盖 backups 表", source, "EmptyOperation()");
}
else
{
ret = 12;//没有新数据可覆盖
}
}
//复制原始文件到单次销售明细 dbf 文件本地保存路径（覆盖）
//dbf文件无法真正删除老数据，每次上传前先使用比较小的原始文件
File.Copy(GlobalParams.xsxbIniPath, GlobalParams.xsxbPath, true);
try
{
//导出单次销售明细数据到本地 DBF 文件
r = new DataOperator().SalesBackupstoDBF();
if (r > 0)
{
Log.WriteNormalLog("导出数据到本地 DBF 文件", "EmptyOperation()", source);
}
else if (r == -3)
{
ret = -5;
Log.WriteNormalLog("backups 表没有数据, 停止导出", "EmptyOperation()", source);
}
}
catch (Exception ex)
{
ret = -4;
Log.WriteErrorLog(ex.Message, ex.Source, "EmptyOperation()");
}
}
return(ret);
}
/// <summary>
/// 重新上传状态（文件夹有 xsxb.dbf 或者 error.tab）,更新本地 xsxb.dbf
/// </summary>
/// <returns></returns>
public static int ReUploadOperation(string source)
{
int ret = 0;
bool error = false;
if (new SalesBLL().SelectSalesBackups() == 0 && new SalesBLL().SelectSalesUnsure() == 0)
{
error = true;
Log.WriteNormalLog("本地没有历史数据无法重新上传，出现数据丢失，后台请核对", "ReUploadOperation()", source);
MessageBox.Show("可能出现数据丢失，请前往后台回收数据，然后核对");
}
//将新数据追加到 backups 里
int r = new SalesBLL().TimeToBackups();
if (r > 0)
{
ret = 7;
Log.WriteNormalLog("新数据追加到 backups 表", "ReUploadOperation()", source);
}
else if (r < 0)
{
Log.WriteErrorLog("新数据追加到 backups 表", source, "ReUploadOperation()");
}
else
{
ret = 8;//没有新数据
}
if (error)
{
ret = 6;
}
//将 unsure 表数据追加到 backups 里
if (new SalesBLL().UnsureToBackups() > 0)
{
//如果 unsure 表里有数据，追加后就清空
new SalesBLL().DeleteSalesUnsure();
ret = 5;
Log.WriteNormalLog("unsure 表数据追加到 backups 表，然后清空 unsure 表", "ReUploadOperation()", source);
}
//复制原始文件到单次销售明细 dbf 文件本地保存路径（覆盖）
//dbf文件无法真正删除老数据，每次上传前先使用比较小的原始文件
File.Copy(GlobalParams.xsxbIniPath, GlobalParams.xsxbPath, true);
try
{
//导出单次销售明细数据到本地 DBF 文件
r = new DataOperator().SalesBackupstoDBF();
if (r > 0)
{
Log.WriteNormalLog("导出数据到本地 DBF 文件", "ReUploadOperation()", source);
}
}
catch (Exception ex)
{
ret = -2;
Log.WriteErrorLog(ex.Message, ex.Source, "ReUploadOperation()");
}
return(ret);
}
/// <summary>
/// 不确定状态（文件夹有 control.tab，或者无法访问）
/// </summary>
/// <returns></returns>
public static int UnsureOperation(string source)
{
int ret = 0;
int r;
int unsureDataNumber = new SalesBLL().SelectSalesUnsure();
if (unsureDataNumber < 0)
{
ret = -1;
Log.WriteErrorLog("查询 unsure 表数据出错", source, "UnsureOperation()");
}
else
{
//unsure 表有数据，说明 backups 里是延迟上传的数据
if (unsureDataNumber > 0)
{
//将新数据追加到 backups 里
r = new SalesBLL().TimeToBackups();
if (r > 0)
{
ret = 1;
Log.WriteNormalLog("新数据追加到 backups 表", "UnsureOperation()", source);
}
else if (r < 0)
{
Log.WriteErrorLog("新数据追加到 backups 表", source, "UnsureOperation()");
}
else
{
ret = 2;
}
}
//unsure 表没有数据，backups 如果有数据，成了不确定数据，需要移动到 unsure 里，然后新数据覆盖 backups（延迟上传）
//unsure 表没有数据，backups 如果也没有数据，属于初始状态，
else
{
//将 backups 数据存入 unsure 表
int iniState = new SalesBLL().BackupsToUnsure();
if (iniState > 0)
{
ret = 3;
Log.WriteNormalLog("将 backups 数据存入 unsure 表", "UnsureOperation()", source);
}
//backups 里如果没数据，初始状态，还未上传过 dbf 文件
else if (iniState == 0)
{
Log.WriteNormalLog("初始状态，backups 没有数据", "UnsureOperation()", source);
//新数据存入 unsure 里，作为初始状态的标记
if (new SalesBLL().TimeToUnsure() > 0)
{
ret = 4;
Log.WriteNormalLog("将新数据存入 unsure 表", "UnsureOperation()", source);
}
}
else
{
Log.WriteErrorLog("将 backups 数据存入 unsure 表", source, "UnsureOperation()");
}
//覆盖新数据到 backups 里
r = new SalesBLL().TimeCoverBackups();
if (r > 0)
{
Log.WriteNormalLog("新数据覆盖 backups 表", "UnsureOperation()", source);
}
else if (r < 0)
{
Log.WriteErrorLog("新数据覆盖 backups 表", source, "UnsureOperation()");
}
}
}
return(ret);
}
/// <summary>
/// 汇总单数据
/// </summary>
/// <returns></returns>
public string GetSalesDayStr()
{
decimal salesDayCash = 0;
decimal salesDayCard = 0;
decimal salesDayOther = 0;
decimal salesDayTotal = 0;
decimal moling = 0;
List <Sales> salesDay = new SalesBLL().GetSalesDay();
if (salesDay != null)
{
//tzm 为 1 的数据没有抹零到角
var money =
from s in salesDay
group s by new { s.tzm, s.xph, s.jsfsbh } into g
select new
{
tzm = g.Key.tzm,
xph = g.Key.xph,
jsfsbh = g.Key.jsfsbh,
ssje = Math.Truncate(g.Sum(o => o.ssje) * 10) / 10
};
//原来的方法
salesDayCash = money.Where(o => o.jsfsbh == "0").Sum(s => s.ssje);
salesDayCard = money.Where(o => o.jsfsbh == "5").Sum(s => s.ssje);
salesDayOther = money.Where(o => (o.jsfsbh != "0" && o.jsfsbh != "5")).Sum(s => s.ssje);
salesDayTotal = money.Sum(o => o.ssje);
#region /* 2017-8-15 暂保留此段注释(另一种统计方式：从当日的销售小票中算当日销售金额汇总) */
/* 2017-8-15 暂保留此段注释
* //从当日的销售小票中算当日销售金额汇总
* //MessageBox.Show(DateTime.Now.ToString("yyyyMMdd"));
* List<Receipt> rList = new List<Receipt>();
* try
* {
*
* BinaryFormatter formatter = new BinaryFormatter();
* using (Stream input = File.OpenRead(GlobalParams.DataPath + "\\" + DateTime.Now.Year + "\\" + DateTime.Now.Month + "\\"
+ DateTime.Now.ToString("yyyyMMdd") + ".rec"))
+ {
+ rList = (List<Receipt>)formatter.Deserialize(input);
+ }
+ }
+ catch
+ {
+ MessageBox.Show("未找到当日销售数据");
+ }
+ if (rList != null)
+ {
+ foreach (Receipt rec in rList)
+ {
+ if (!string.IsNullOrEmpty(rec.mName))
+ {
+ salesDayCard += rec.cardPay;
+ }
+ else
+ {
+ salesDayOther += rec.cardPay;
+
+ }
+ salesDayCash += rec.cash;
+ moling += rec.change;
+ }
+ salesDayCash = salesDayCash - moling;
+ salesDayTotal = salesDayCash + salesDayCard + salesDayOther;
+ // MessageBox.Show("现金"+salesDayCash+"刷卡"+salesDayCard+"其他"+salesDayOther+"总计"+salesDayTotal);
+ }*/
#endregion
}
else
{
Log.WriteErrorLog("获取当日销售明细中的支付数据", "", "GetSalesDayStr()");
}
StringBuilder sb = new StringBuilder();
sb.Append("现金： " + salesDayCash + Environment.NewLine);
sb.Append("刷卡： " + salesDayCard + Environment.NewLine);
sb.Append("其它： " + salesDayOther + Environment.NewLine);
sb.Append("总计： " + salesDayTotal + Environment.NewLine);
return(sb.ToString());
}
