//Прибыль
public void PeriodProfid(DateTime begin, DateTime end)
{
if ((end - begin).Days <= 5)
{
string beginDate = begin.ToString("yyyy-MM-dd");
string endDate = end.ToString("yyyy-MM-dd");
//excel = new Application();
//excel.Visible = true;
//excel.Workbooks.Add();
//Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
ExcelClass excel_class = new ExcelClass();
excel_class.Open(System.Windows.Forms.Application.StartupPath + "\\reports\\period_expense1.xls");
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = GetQueryExpense(beginDate, endDate);
System.Data.DataTable tableExpense = new System.Data.DataTable();
System.Data.DataTable tableRealize = new System.Data.DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(tableExpense);
command.CommandText = GetQueryRealize();
adapter.Fill(tableRealize);
excel_class.ColWidth("B", 50);
int i = 7;
foreach (DataRow dr in tableExpense.Rows)
{
DataRow[] dr1 = tableRealize.Select("productId = " + dr["productId"]);
if (dr["productId"].ToString() == "2635")
{
}
excel_class.SetCell("A" + i, dr["productId"].ToString(), false);
excel_class.SetCell("B" + i, dr["name"].ToString(), false);
//if (dr["measureId"].ToString() == "2")
// excel_class.SetCell("C" + i, "ШТ", false);
//else
// excel_class.SetCell("C" + i, "КГ", false);
excel_class.SetCell("E" + i, dr["SumCount"].ToString(), false);
excel_class.SetCell("F" + i, dr["SumOrder"].ToString(), false);
if (dr1.Length != 0)
{
double raznica = double.Parse(dr1[0]["soldPrice"].ToString()) - double.Parse(dr1[0]["price"].ToString());
excel_class.SetCell("G" + i, (double.Parse(dr["SumCount"].ToString()) * raznica).ToString(), false);
}
i++;
}
excel_class.activeSheet.Application.Visible = true;
}
//workSheet.get_Range("A").ColumnWidth = 35;
}
public void Selling(DateTime beginDate, DateTime endDate)
{
string dateBegin = beginDate.ToString("yyyy-MM-dd");
string dateEnd = endDate.ToString("yyyy-MM-dd");
//excel = new Application();
//excel.Visible = true;
//excel.Workbooks.Add();
//Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
ExcelClass excel_class = new ExcelClass();
excel_class.Open(System.Windows.Forms.Application.StartupPath + "\\reports\\period_saldo.xls");
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = GetQueryReport2(dateBegin, dateEnd);
System.Data.DataTable tableReport = new System.Data.DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(tableReport);
//command.CommandText = GetQueryExpense(dateBegin, dateEnd);
//adapter.Fill(tableExpense);
excel_class.ColWidth("B", 50);
int i = 7;
//DataRow[] rBalanceSaldo = tableBalanceList.Select("balanceDate = '"+dateBegin+"'");
//DataRow[] rBalanceOstatok = tableBalanceList.Select("balanceDate = '" + dateEnd + "'");
foreach (DataRow dr in tableReport.Rows)
{
excel_class.SetCell("A" + i, dr["productId"].ToString(), false);
excel_class.SetCell("B" + i, dr["name"].ToString(), false);
excel_class.SetCell("D" + i, dr["saldoCount"].ToString(), false);
excel_class.SetCell("E" + i, dr["price"].ToString(), false);
excel_class.SetCell("F" + i, Math.Round(double.Parse(dr["saldoCount"].ToString()) * double.Parse(dr["price"].ToString()), 3).ToString(), false);
excel_class.SetCell("G" + i, dr["prixCount"].ToString(), false);
excel_class.SetCell("H" + i, dr["prPrice"].ToString(), false);
if (dr["prixCount"] != DBNull.Value)
{
excel_class.SetCell("I" + i, Math.Round(double.Parse(dr["prixCount"].ToString()) * double.Parse(dr["prPrice"].ToString()), 3).ToString(), false);
}
excel_class.SetCell("K" + i, dr["spisCount"].ToString(), false);
excel_class.SetCell("L" + i, dr["spisSumm"].ToString(), false);
if (dr["spisCount"] != DBNull.Value)
{
excel_class.SetCell("M" + i, Math.Round(double.Parse(dr["spisCount"].ToString()) * double.Parse(dr["spisSumm"].ToString()), 3).ToString(), false);
}
excel_class.SetCell("N" + i, dr["exCount"].ToString(), false);
excel_class.SetCell("O" + i, dr["exSumm"].ToString(), false);
if (dr["exCount"] != DBNull.Value)
{
excel_class.SetCell("P" + i, Math.Round(double.Parse(dr["exCount"].ToString()) * double.Parse(dr["exSumm"].ToString()), 3).ToString(), false);
}
excel_class.SetCell("Q" + i, dr["backCount"].ToString(), false);
excel_class.SetCell("R" + i, dr["backSumm"].ToString(), false);
if (dr["backCount"] != DBNull.Value)
{
excel_class.SetCell("S" + i, Math.Round(double.Parse(dr["backCount"].ToString()) * double.Parse(dr["backSumm"].ToString()), 3).ToString(), false);
}
excel_class.SetCell("T" + i, dr["ostatokCount"].ToString(), false);
excel_class.SetCell("U" + i, dr["price"].ToString(), false);
if (dr["ostatokCount"] != DBNull.Value)
{
excel_class.SetCell("V" + i, Math.Round(double.Parse(dr["ostatokCount"].ToString()) * double.Parse(dr["price"].ToString()), 3).ToString(), false);
}
i++;
}
excel_class.activeSheet.Application.Visible = true;
}
public void TodayOrdersReport(string date)
{
ExcelClass excel_class = new ExcelClass();
excel_class.Open(System.Windows.Forms.Application.StartupPath + "\\reports\\today_orders.xls");
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = this.GetDateOrdersReport(date);
System.Data.DataTable tableReport = new System.Data.DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(tableReport);
command.CommandText = "select sum(e.expSum) as summa, sum(e.terminal) as terminal from expense as e where e.expType = 1 and date(e.expenseDate) = date(" + date + ")" + (userID == ""?"":" and e.userID =" + userID);
System.Data.DataTable table2 = new System.Data.DataTable();
adapter.Fill(table2);
int i = 7;
string expVal = "";
int summa = 0;
int dolg = 0;
int terminal = 0;
System.Drawing.Color clr1 = System.Drawing.Color.FromArgb(184, 197, 230);
System.Drawing.Color clr2 = System.Drawing.Color.FromArgb(104, 222, 159);
excel_class.SetCell("D1", "Магазин \"" + tposDesktop.Properties.Settings.Default.orgName + "\"", false);
excel_class.SetCell("D2", "Отчёт по продажам за " + (date == "now()"?"сегодня":date), false);
excel_class.backColor = clr2;
foreach (DataRow dr in tableReport.Rows)
{
if (expVal != dr["expenseId"].ToString())
{
if (excel_class.backColor == clr1)
{
excel_class.backColor = clr2;
}
else
{
excel_class.backColor = clr1;
}
expVal = dr["expenseId"].ToString();
if (dr["status"].ToString() == "0")
{
summa += Convert.ToInt32(dr["expSum"].ToString());
}
else
{
dolg += Convert.ToInt32(dr["expSum"].ToString());
}
int term = Convert.ToInt32(dr["terminal"].ToString());
terminal += term;
excel_class.SetCell("H" + i, dr["expSum"].ToString(), false);
}
excel_class.SetCell("A" + i, dr["expenseId"].ToString(), false);
excel_class.SetCell("B" + i, dr["prodId"].ToString(), false);
excel_class.SetCell("C" + i, dr["expenseDate"].ToString(), false);
excel_class.SetCell("D" + i, dr["name"].ToString(), false);
excel_class.SetCell("E" + i, dr["meas"].ToString(), false);
excel_class.SetCell("F" + i, dr["packCount"].ToString(), false);
excel_class.SetCell("G" + i, dr["orderSumm"].ToString(), false);
//excel_class.SetCell("H" + i, dr["expSum"].ToString(), false);
excel_class.SetCell("I" + i, dr["Debts"].ToString(), false);
i++;
}
excel_class.backColor = System.Drawing.Color.White;
excel_class.SetCell("F" + i, "Итого:", true);
excel_class.SetCell("G" + i, "Сумма продажи:", true);
excel_class.SetCell("H" + i, summa.ToString(), true);
i++;
excel_class.SetCell("G" + i, "Наличные:", true);
excel_class.SetCell("H" + i, (summa - terminal).ToString(), true);
i++;
excel_class.SetCell("G" + i, "Терминал:", true);
excel_class.SetCell("H" + i, terminal.ToString(), true);
i++;
excel_class.SetCell("G" + i, "Долг:", true);
excel_class.SetCell("H" + i, dolg.ToString(), true);
if (table2.Rows.Count > 0 && table2.Rows[0]["summa"] != DBNull.Value)
{
i += 2;
excel_class.SetCell("G" + i, "Возврат:", true);
excel_class.SetCell("H" + i, table2.Rows[0]["summa"].ToString(), true);
excel_class.SetCell("I" + i, table2.Rows[0]["terminal"].ToString(), true);
i++;
excel_class.SetCell("F" + i, "Всего наличных:", true);
excel_class.SetCell("H" + i, ("=H" + (i - 5) + "-(H" + (i - 1) + "-I" + (i - 1) + ")").ToString(), true);
}
excel_class.activeSheet.Application.Visible = true;
excel_class.savepath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + (date == "now()"?DateTime.Now.ToShortDateString():date) + "_report.xls";
excel_class.SaveOnly();
}
