예제 #1
0
        //Прибыль
        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;
        }
예제 #2
0
        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;
        }
예제 #3
0
        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();
        }