Index: trunk/BazaReklam/OferForm.cs =================================================================== --- trunk/BazaReklam/OferForm.cs (revision 591) +++ trunk/BazaReklam/OferForm.cs (revision 602) @@ -7,4 +7,5 @@ using System.Windows.Forms; using System.IO; +using Baza_Reklam.Classes.Helpers; @@ -139,34 +140,34 @@ private static void OpenOfer(string filename) { - WordHandler wh = new WordHandler(); - - wh.openFile(filename); - wh.setWordVisible(); + WordHelper wh = new WordHelper(); + + wh.OpenFile(filename); + wh.SetWordVisible(); } private void CreateNewOfer(string filename, string newFileName) { - WordHandler wh = new WordHandler(); - - wh.openFile(filename); - - wh.findAndReplace("<>", id_ofertyTextBox.Text + "/" + User.Instance().Kod_agenta.ToUpper() + "/" + DateTime.Today.Year); - wh.findAndReplace("<>", id_klienci_os_kontaktComboBox.Text); - wh.findAndReplace("<>", adres_KorTextBox.Text); - wh.findAndReplace("<>", User.Instance().Miasto); - wh.findAndReplace("<>", data_OfertyDateTimePicker.Value.ToShortDateString()); - wh.findAndReplace("<>", User.Instance().Imie + " " + User.Instance().Nazwisko); - wh.findAndReplace("<>", User.Instance().Email); - wh.findAndReplace("<>", User.Instance().TelWewnetrzny); - wh.findAndReplace("<>", User.Instance().KomSluzbowa); - wh.findAndReplace("<>", User.Instance().AdresBiura); - wh.findAndReplace("<>", User.Instance().TelSekretariat); - wh.findAndReplace("<>", User.Instance().FaxSekretariat); - wh.findAndReplace("<>", opis_DolTextBox.Text); - wh.findAndReplace("<>", User.Instance().Stanowisko); - - wh.saveAs(newFileName); - - wh.setWordVisible(); + WordHelper wh = new WordHelper(); + + wh.OpenFile(filename); + + wh.FindAndReplace("<>", id_ofertyTextBox.Text + "/" + User.Instance().Kod_agenta.ToUpper() + "/" + DateTime.Today.Year); + wh.FindAndReplace("<>", id_klienci_os_kontaktComboBox.Text); + wh.FindAndReplace("<>", adres_KorTextBox.Text); + wh.FindAndReplace("<>", User.Instance().Miasto); + wh.FindAndReplace("<>", data_OfertyDateTimePicker.Value.ToShortDateString()); + wh.FindAndReplace("<>", User.Instance().Imie + " " + User.Instance().Nazwisko); + wh.FindAndReplace("<>", User.Instance().Email); + wh.FindAndReplace("<>", User.Instance().TelWewnetrzny); + wh.FindAndReplace("<>", User.Instance().KomSluzbowa); + wh.FindAndReplace("<>", User.Instance().AdresBiura); + wh.FindAndReplace("<>", User.Instance().TelSekretariat); + wh.FindAndReplace("<>", User.Instance().FaxSekretariat); + wh.FindAndReplace("<>", opis_DolTextBox.Text); + wh.FindAndReplace("<>", User.Instance().Stanowisko); + + wh.SaveAs(newFileName); + + wh.SetWordVisible(); } Index: trunk/BazaReklam/OrderDetails.cs =================================================================== --- trunk/BazaReklam/OrderDetails.cs (revision 591) +++ trunk/BazaReklam/OrderDetails.cs (revision 602) @@ -7,5 +7,5 @@ using System.Windows.Forms; using System.Text.RegularExpressions; -using Baza_Reklam.Classes; +using Baza_Reklam.Classes.Helpers; namespace Baza_Reklam Index: trunk/BazaReklam/ZamowieniaForm.cs =================================================================== --- trunk/BazaReklam/ZamowieniaForm.cs (revision 591) +++ trunk/BazaReklam/ZamowieniaForm.cs (revision 602) @@ -906,5 +906,5 @@ naglowekKorekty.NUMER_ROZ = faktura.NUMER_ROZ; - naglowekKorekty.NUMER = DbUtils.numerNowejFakturyKorekty(DateTime.Today.Year); + naglowekKorekty.NUMER = FakturaHelper.GetNewCorrectionInvoiceNumber(DateTime.Today.Year, faktura.SystemKsiegowyId); naglowekKorekty.NUMER_ROK = DateTime.Today.Year; naglowekKorekty.DATA_WYSTAWIENIA = DateTime.Today; Index: trunk/BazaReklam/ZestawienieZamowienForm.cs =================================================================== --- trunk/BazaReklam/ZestawienieZamowienForm.cs (revision 591) +++ trunk/BazaReklam/ZestawienieZamowienForm.cs (revision 602) @@ -5,4 +5,5 @@ using System.Drawing; using System.Windows.Forms; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces; Index: trunk/BazaReklam/Baza Reklam.csproj =================================================================== --- trunk/BazaReklam/Baza Reklam.csproj (revision 591) +++ trunk/BazaReklam/Baza Reklam.csproj (revision 602) @@ -108,6 +108,6 @@ - - + + @@ -124,13 +124,13 @@ - + - - + + - + Form Index: trunk/BazaReklam/AddZamowienieForm.cs =================================================================== --- trunk/BazaReklam/AddZamowienieForm.cs (revision 591) +++ trunk/BazaReklam/AddZamowienieForm.cs (revision 602) @@ -38,6 +38,6 @@ row = (REKLAMADataSet.zamowieniaRow)r.Row; - row.nrZamowienia = DbUtils.numerNowegoZamowienia(idKlienta, DateTime.Today.Year, User.Instance().Kod_agenta); - row.rokZamowienia = DateTime.Today.Year; + row.nrZamowienia = DbUtils.numerNowegoZamowienia(idKlienta, DateTime.Now.Year, User.Instance().Kod_agenta); + row.rokZamowienia = DateTime.Now.Year; row.kodAgenta = User.Instance().Kod_agenta; row.idKlienta = idKlienta; Index: trunk/BazaReklam/Facturer.cs =================================================================== --- trunk/BazaReklam/Facturer.cs (revision 591) +++ trunk/BazaReklam/Facturer.cs (revision 602) @@ -3,4 +3,5 @@ using System.Data.SqlClient; using System.Windows.Forms; +using Baza_Reklam.Classes.Helpers; namespace Baza_Reklam @@ -764,5 +765,5 @@ naglowekKorekty.NUMER_ROZ = faktura.NUMER_ROZ; - naglowekKorekty.NUMER = DbUtils.numerNowejFakturyKorekty(DateTime.Today.Year); + naglowekKorekty.NUMER = FakturaHelper.GetNewCorrectionInvoiceNumber(DateTime.Today.Year, faktura.SystemKsiegowyId); naglowekKorekty.NUMER_ROK = DateTime.Today.Year; naglowekKorekty.DATA_WYSTAWIENIA = DateTime.Today; Index: trunk/BazaReklam/FakturowaniePoznanKatowice.cs =================================================================== --- trunk/BazaReklam/FakturowaniePoznanKatowice.cs (revision 591) +++ trunk/BazaReklam/FakturowaniePoznanKatowice.cs (revision 602) @@ -6,4 +6,5 @@ using System.Globalization; using System.Data.SqlClient; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Model; @@ -48,5 +49,5 @@ _agencjaRedakcja = agencjeTableAdapter.GetDataByIdAgencji((int)FakturaHelper.OfficeId.Redakcja)[0]; - _konto = kontaTableAdapter.GetDataById((int)FakturaHelper.BankAccount.CentralaPLN)[0]; + _konto = kontaTableAdapter.GetDataById((int)FakturaHelper.BankAccount.CentralaPln)[0]; } @@ -542,5 +543,5 @@ naglowekKorekty.NUMER_ROZ = faktura.NUMER_ROZ; - naglowekKorekty.NUMER = DbUtils.numerNowejFakturyKorekty(DateTime.Today.Year); + naglowekKorekty.NUMER = FakturaHelper.GetNewCorrectionInvoiceNumber(DateTime.Today.Year, faktura.SystemKsiegowyId); naglowekKorekty.NUMER_ROK = DateTime.Today.Year; naglowekKorekty.DATA_WYSTAWIENIA = DateTime.Today; Index: trunk/BazaReklam/Classes/Helpers/WordHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/WordHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/WordHelper.cs (revision 602) @@ -0,0 +1,51 @@ +using System; +using System.Reflection; +using Word; + +namespace Baza_Reklam.Classes.Helpers +{ + internal class WordHelper + { + private readonly Application oWord; + private Object oFalse = false; + private Object oMissing = Missing.Value; + private Object oTrue = true; + private Document oWordDoc; + + public WordHelper() + { + oWord = new Application(); + } + + public void OpenFile(string filename) + { + Object oPlik = filename; + oWordDoc = oWord.Documents.Open(ref oPlik, ref oMissing, ref oMissing, ref oMissing, ref oMissing, + ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, + ref oMissing, ref oTrue); + } + + public void SetWordVisible() + { + if (!oWord.Visible) oWord.Visible = true; + } + + public void FindAndReplace(string strFind, string strReplace) + { + Object oFindText = strFind; + Object oReplaceText = strReplace; + Object oReplace = WdReplace.wdReplaceAll; + oWordDoc.Content.Find.Execute(ref oFindText, ref oFalse, ref oFalse, ref oFalse, ref oFalse, + ref oFalse, ref oFalse, ref oMissing, ref oFalse, + ref oReplaceText, ref oReplace, ref oFalse, ref oFalse, + ref oFalse, ref oFalse); + } + + public void SaveAs(string filename) + { + Object oFileNameAs = filename; + oWordDoc.SaveAs(ref oFileNameAs, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, + ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing); + } + } +} Index: trunk/BazaReklam/Classes/Helpers/ReklamaHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/ReklamaHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/ReklamaHelper.cs (revision 602) @@ -0,0 +1,24 @@ +namespace Baza_Reklam.Classes.Helpers +{ + public class ReklamaHelper + { + public static bool IsReklamaNew(REKLAMADataSet.ReklamyZestawienieRow reklama) + { + if (!reklama.IsidZamowieniaNull()) return true; + + return reklama.DATA_ZAMÓWIENIA.Year >= 2009; + } + + public static bool IsReklamaNew(REKLAMADataSet.REKLAMARow reklama) + { + if (!reklama.IsidZamowieniaNull()) return true; + + return reklama.DATA_ZAMÓWIENIA.Year >= 2009; + } + + public static bool IsReklamaOldExpo(REKLAMADataSet.REKLAMARow reklama) + { + return (reklama.IsidZamowieniaNull() && reklama.TYTU£.ToUpper() == "EXPO"); + } + } +} Index: trunk/BazaReklam/Classes/Helpers/FakturaHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/FakturaHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/FakturaHelper.cs (revision 602) @@ -0,0 +1,331 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SqlClient; +using Baza_Reklam.Classes.Model; +using Baza_Reklam.REKLAMADataSetTableAdapters; + +namespace Baza_Reklam.Classes.Helpers +{ + public class FakturaHelper + { + public enum PaymentType + { + Card = 0, + Transfer = 1, + Cash = 2, + Barter = 3 + } + + public enum ClientId + { + Katowice = 678, + Poznan = 18845 + } + + public enum OfficeId + { + Redakcja = 2, + Katowice = 4, + Poznan = 6 + } + + public enum OrderId + { + Poznan = 272, + Katowice = 273 + } + + public enum BankAccount + { + CentralaPln = 43 + } + + public static string GetPaymentType(int paymentType) + { + switch (paymentType) + { + case 0: + return "Karta kredytowa"; + case 1: + return "Przelew"; + case 2: + return "Gotówka"; + case 3: + return "Barter"; + default: + return string.Empty; + } + } + + public static bool AddInvoice(REKLAMADataSet reklamaDataSet, FAKTURYTableAdapter fakturaAdapter, + FAKTURA_DETAILSTableAdapter fakturaDetailsAdapter, + WplatyTableAdapter wplataAdapter, REKLAMADataSet.FAKTURYRow fakturaRow) + { + SqlConnection conn; + SqlTransaction transaction = null; + try + { + conn = new SqlConnection(ConnString.getConnString().Value); + fakturaAdapter.Connection = conn; + fakturaDetailsAdapter.Connection = conn; + wplataAdapter.Connection = conn; + + conn.Open(); + + transaction = conn.BeginTransaction(); + fakturaAdapter.AttachTransaction(transaction); + fakturaDetailsAdapter.AttachTransaction(transaction); + wplataAdapter.AttachTransaction(transaction); + + fakturaAdapter.Update(reklamaDataSet.FAKTURY); + fakturaDetailsAdapter.Update(reklamaDataSet.FAKTURA_DETAILS); + wplataAdapter.Update(reklamaDataSet.Wplaty); + transaction.Commit(); + } + catch (Exception) + { + if (transaction != null) transaction.Rollback(); + reklamaDataSet.FAKTURY.RemoveFAKTURYRow(fakturaRow); + throw; + } + return true; + } + + public static void AddInvoiceDetails(REKLAMADataSet.FAKTURYRow newInvoice, + REKLAMADataSet.FAKTURYRow clientInvoice, + REKLAMADataSet.FAKTURA_DETAILSRow detailsRow, + REKLAMADataSet.FAKTURA_DETAILSDataTable detailsTable) + { + REKLAMADataSet.FAKTURA_DETAILSRow row = detailsTable.NewFAKTURA_DETAILSRow(); + + row.ROK = detailsRow.ROK; + row.MIESIAC = detailsRow.MIESIAC; + row.TYTUL = detailsRow.TYTUL; + row.NAZWA_USLUGI = clientInvoice != null + ? clientInvoice.NUMER + "/" + clientInvoice.NUMER_ROZ + "/" + clientInvoice.NUMER_ROK + + " | " + detailsRow.NAZWA_USLUGI + : ""; + row.reklamaId = detailsRow.reklamaId; + row.NR_WYDANIA = detailsRow.NR_WYDANIA; + + row.CENA_JEDN = detailsRow.CENA_JEDN; + row.ILOSC = 1; + row.JM = "szt."; + + row.UPUST_NETTO = detailsRow.UPUST_NETTO; + row.UPUST_PR = detailsRow.UPUST_PR; + row.NETTO = detailsRow.NETTO; + row.S_VAT = VatHelper.PL22; + row.VAT = (decimal)VatHelper.PL22 * detailsRow.NETTO; + row.BRUTTO = detailsRow.NETTO * (decimal)(1 + VatHelper.PL22); + + row.TYP = 2; + row.PODTYP = 1; + row.ID_FAKTURY = newInvoice.ID_FAKTURY; + + row.wyroznienie = (!detailsRow.IswyroznienieNull() && detailsRow.wyroznienie); + + detailsTable.AddFAKTURA_DETAILSRow(row); + } + + public static string GetNazwaUslugiPozKat(REKLAMADataSet.FAKTURYRow faktura, + REKLAMADataSet.FAKTURA_DETAILSRow pozycjaFaktury) + { + if (faktura == null) return string.Empty; + + return faktura.NUMER + "/" + faktura.NUMER_ROZ + "/" + faktura.NUMER_ROK + " | " + + pozycjaFaktury.NAZWA_USLUGI; + } + + public static REKLAMADataSet.FAKTURYRow GetInvoice(DataTable fakturyTable, + REKLAMADataSet.KLIENCIRow klientAgencja, + SLOWNIKDataSet.AGENCJERow agencja, + SLOWNIKDataSet.AGENCJERow redakcja, + SLOWNIKDataSet.KONTARow bankAccount, + string numerRoz, + PaymentType paymentType, + DateTime dateTime) + { + REKLAMADataSet.FAKTURYRow naglowekFaktury = (REKLAMADataSet.FAKTURYRow)fakturyTable.NewRow(); + + naglowekFaktury.KOREKTA = false; + naglowekFaktury.NUMER_ROZ = numerRoz; + naglowekFaktury.NUMER = DbUtils.numerNowejFaktury(naglowekFaktury.NUMER_ROZ, DateTime.Today.Year); + naglowekFaktury.NUMER_ROK = DateTime.Today.Year; + + naglowekFaktury.DATA_WYSTAWIENIA = dateTime != DateTime.Today ? dateTime : DateTime.Today; + + naglowekFaktury.idZamowienia = agencja.Id_agencji == (int)OfficeId.Poznan + ? + (int)OrderId.Poznan + : (int)OrderId.Katowice; + + naglowekFaktury.ID_SPRZEDAWCY = agencja.Id_agencji; + + naglowekFaktury.EKSPORT = false; + + naglowekFaktury.MIEJSCOWOSC_WYSTAWIENIA = redakcja.miasto; + naglowekFaktury.SPRZEDAWCA_ADRES = redakcja.Adres_Fk; + naglowekFaktury.SPRZEDAWCA_NIP = redakcja.NIP; + + naglowekFaktury.PODPIS_WYSTAWIL = string.Empty; + naglowekFaktury.DATA_SPRZEDAZY = naglowekFaktury.DATA_WYSTAWIENIA; + + naglowekFaktury.ID_NABYWCY = klientAgencja.CustomerID; + naglowekFaktury.NABYWCA_ADRES = klientAgencja.Adres_Fkatura; + naglowekFaktury.NABYWCA_NIP = klientAgencja.Nip; + naglowekFaktury.opis = string.Empty; + + naglowekFaktury.SPOSOB_ZAPLATY = GetPaymentType((int)paymentType); + + naglowekFaktury.TERMIN_ZAPLATY = naglowekFaktury.DATA_SPRZEDAZY.AddDays(14); + naglowekFaktury.ZAPLACONO = false; + + naglowekFaktury.FAKTURA_TYP = 2; + naglowekFaktury.FAKTURA_PODTYP = 1; + + naglowekFaktury.ID_KONTA = Convert.ToInt16(bankAccount.id); + + naglowekFaktury.EndEdit(); + return naglowekFaktury; + } + + public static bool IsSumaEmisjiMniejszaOdFakturyNetto(REKLAMADataSet.VIEW_ZESTAWIENIE_FAKTUR_NOWERow faktura) + { + if (faktura.IssumaNettoZEmisjiNull()) return false; + + if (IsFakturaWWalucie(faktura) && !faktura.Iswaluta_kursNull()) + return Math.Round(faktura.sumaNettoZEmisji / faktura.waluta_kurs, 0) < + (decimal)Math.Round(faktura.waluta_brutto, 0); + + return Math.Round(faktura.sumaNettoZEmisji, 0) < Math.Round(faktura.NETTO, 0); + } + + public static bool IsFakturaWWalucie(REKLAMADataSet.VIEW_ZESTAWIENIE_FAKTUR_NOWERow faktura) + { + if (faktura.Iswaluta_mianoNull()) return false; + if (string.IsNullOrEmpty(faktura.waluta_miano)) return false; + if (faktura.waluta_miano.ToUpper() == "PLN") return false; + + return true; + } + + + public static List GetInvoiceList(int parentInvoiceId) + { + List invoices = new List(); + SqlConnection conn = null; + SqlCommand cmd = null; + SqlDataReader reader = null; + + const string query = "SELECT IDPozKat AS Id, NrFakturyPozKat AS Label, NettoPozKat AS Amount, DataWystawieniaPozKat AS Date FROM dbo.FakturyPoznanKatowice WHERE ID=@id"; + + try + { + conn = new SqlConnection(ConnString.getConnString().Value); + conn.Open(); + cmd = new SqlCommand(query, conn); + cmd.Parameters.AddWithValue("@id", parentInvoiceId); + reader = cmd.ExecuteReader(); + + if (reader != null) + while (reader.Read()) + { + Invoice invoice = new Invoice(); + invoice.Id = Convert.ToInt32(reader["Id"]); + invoice.Label = Convert.ToString(reader["Label"]); + invoice.Amount = Convert.ToDecimal(reader["Amount"]); + invoice.Date = Convert.ToDateTime(reader["Date"]); + invoices.Add(invoice); + } + } + finally + { + if (reader != null) + { + reader.Close(); + reader.Dispose(); + } + if (cmd != null) + { + cmd.Dispose(); + } + if (conn != null) + { + conn.Close(); + conn.Dispose(); + } + } + + return invoices; + } + + public static List GetPartnerInvoiceList(int year, int month, string partner) + { + List invoices = new List(); + SqlConnection conn = null; + SqlCommand cmd = null; + SqlDataReader reader = null; + + try + { + conn = new SqlConnection(ConnString.getConnString().Value); + conn.Open(); + cmd = new SqlCommand("sp_FindPartnerInvoices", conn); + cmd.CommandType = CommandType.StoredProcedure; + cmd.Parameters.AddWithValue("@year", year); + cmd.Parameters.AddWithValue("@month", month); + cmd.Parameters.AddWithValue("@partner", partner); + reader = cmd.ExecuteReader(); + + if (reader != null) + while (reader.Read()) + { + Invoice invoice = new Invoice(); + invoice.Id = Convert.ToInt32(reader["Id"]); + invoice.Label = Convert.ToString(reader["Number"]) + "/" + Convert.ToString(reader["AgentShortCut"]) + "/" + Convert.ToString(reader["Year"]); + invoice.Amount = Convert.ToDecimal(reader["Amount"]); + invoice.Date = Convert.ToDateTime(reader["Date"]); + invoice.PaidAmount = Convert.ToDecimal(reader["TotalPaid"]); + invoice.IsCorrectionInvoice = Convert.ToBoolean(reader["Correction"]); + invoice.Currency = Convert.ToString(reader["Currency"]); + invoice.WeekNumber = Convert.ToInt32(reader["WeekNumber"]); + invoices.Add(invoice); + } + } + finally + { + if (reader != null) + { + reader.Close(); + reader.Dispose(); + } + if (cmd != null) + { + cmd.Dispose(); + } + if (conn != null) + { + conn.Close(); + conn.Dispose(); + } + } + + return invoices; + } + + /// + /// Zwraca numer nastêpnej faktury korekty dla danego agenta. + /// + public static int GetNewCorrectionInvoiceNumber(int year, int systemKsiegowy) + { + string query = "SELECT MAX(Numer) FROM Faktury WHERE Korekta=1 AND [Numer_Rok]=" + year + " AND SystemKsiegowyId=" + systemKsiegowy; + + object nr = DbUtils.ExecuteQuery(query); + if (nr is DBNull) return 1; + return ((int)nr + 1); + } + + } +} Index: trunk/BazaReklam/Classes/Helpers/ExcelHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/ExcelHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/ExcelHelper.cs (revision 602) @@ -0,0 +1,42 @@ +using System.Windows.Forms; + +namespace Baza_Reklam.Classes.Helpers +{ + public class ExcelHelper + { + public void Export(DataGridView source) + { + Excel.ApplicationClass excel = new Excel.ApplicationClass(); + excel.Application.Workbooks.Add(true); + DataGridView table = source; + int cIndex = 0; + foreach (DataGridViewColumn col in table.Columns) + { + if (col.Visible) + { + cIndex++; + excel.Cells[1, cIndex] = col.HeaderText; + } + } + int rIndex = 0; + + foreach (DataGridViewRow row in table.Rows) + { + rIndex++; + cIndex = 0; + foreach (DataGridViewColumn col in table.Columns) + { + if (col.Visible) + { + cIndex++; + excel.Cells[rIndex + 1, cIndex] = row.Cells[col.Name].Value == null + ? "" + : row.Cells[col.Name].Value.ToString(); + } + } + } + // excel.Save("New.xls"); + excel.Visible = true; + } + } +} Index: trunk/BazaReklam/Classes/Helpers/ZamowienieHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/ZamowienieHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/ZamowienieHelper.cs (revision 602) @@ -0,0 +1,36 @@ +using System.Data; +using System.Data.SqlClient; + +namespace Baza_Reklam.Classes.Helpers +{ + public class ZamowienieHelper + { + public static void MakeZamowienieZafakturowane(int zamowienieId) + { + SqlConnection conn = null; + SqlCommand cmd = null; + + try + { + conn = new SqlConnection(ConnString.getConnString().Value); + conn.Open(); + cmd = new SqlCommand("sp_MakeZamowienieZafakturowane", conn); + cmd.CommandType = CommandType.StoredProcedure; + cmd.Parameters.AddWithValue("@zamowienieId", zamowienieId); + cmd.ExecuteNonQuery(); + } + finally + { + if (cmd != null) + cmd.Dispose(); + + if (conn != null && conn.State == ConnectionState.Open) + { + conn.Close(); + conn.Dispose(); + } + } + + } + } +} Index: trunk/BazaReklam/Classes/Helpers/VatHelper.cs =================================================================== --- trunk/BazaReklam/Classes/Helpers/VatHelper.cs (revision 602) +++ trunk/BazaReklam/Classes/Helpers/VatHelper.cs (revision 602) @@ -0,0 +1,9 @@ +namespace Baza_Reklam.Classes.Helpers +{ + public class VatHelper + { + public const double EU00 = 0.00; + public const double PL07 = 0.07; + public const double PL22 = 0.22; + } +} Index: trunk/BazaReklam/Classes/DbUtils.cs =================================================================== --- trunk/BazaReklam/Classes/DbUtils.cs (revision 591) +++ trunk/BazaReklam/Classes/DbUtils.cs (revision 602) @@ -195,15 +195,5 @@ } - /// - /// Zwraca numer nastêpnej faktury danego agenta. - /// - public static int numerNowejFaktury(string tagAgenta, int year) - { - string query = "SELECT MAX(Numer) FROM Faktury WHERE Korekta=0 AND [Numer_Roz]='" + tagAgenta + "' AND [Numer_Rok]=" + year; - - object nr = ExecuteQuery(query); - if (nr is DBNull) return 1; - return ((int)nr + 1); - } + @@ -273,9 +263,9 @@ /// - /// Zwraca numer nowego zamówienia - /// - public static int numerNowegoZamowienia(int idKlienta, int year, string kodAgenta) - { - string query = "SELECT MAX(nrZamowienia) FROM Zamowienia WHERE idKlienta=" + idKlienta + " AND rokZamowienia=" + year + " AND kodAgenta='" + kodAgenta + "'"; + /// Zwraca numer nastêpnej faktury danego agenta. + /// + public static int numerNowejFaktury(string tagAgenta, int year) + { + string query = "SELECT MAX(Numer) FROM Faktury WHERE Korekta=0 AND [Numer_Roz]='" + tagAgenta + "' AND [Numer_Rok]=" + year; object nr = ExecuteQuery(query); @@ -284,10 +274,11 @@ } - /// - /// Zwraca numer nastêpnej faktury korekty dla danego agenta. - /// - public static int numerNowejFakturyKorekty(int year) - { - string query = "SELECT MAX(Numer) FROM Faktury WHERE Korekta=1 AND [Numer_Rok]=" + year; + + /// + /// Zwraca numer nowego zamówienia + /// + public static int numerNowegoZamowienia(int idKlienta, int year, string kodAgenta) + { + string query = "SELECT MAX(nrZamowienia) FROM Zamowienia WHERE idKlienta=" + idKlienta + " AND rokZamowienia=" + year + " AND kodAgenta='" + kodAgenta + "'"; object nr = ExecuteQuery(query); Index: trunk/BazaReklam/Classes/ReklamaHelper.cs =================================================================== --- trunk/BazaReklam/Classes/ReklamaHelper.cs (revision 507) +++ (revision ) @@ -1,24 +1,0 @@ -namespace Baza_Reklam -{ - public class ReklamaHelper - { - public static bool IsReklamaNew(REKLAMADataSet.ReklamyZestawienieRow reklama) - { - if (!reklama.IsidZamowieniaNull()) return true; - - return reklama.DATA_ZAMÓWIENIA.Year >= 2009; - } - - public static bool IsReklamaNew(REKLAMADataSet.REKLAMARow reklama) - { - if (!reklama.IsidZamowieniaNull()) return true; - - return reklama.DATA_ZAMÓWIENIA.Year >= 2009; - } - - public static bool IsReklamaOldExpo(REKLAMADataSet.REKLAMARow reklama) - { - return (reklama.IsidZamowieniaNull() && reklama.TYTU£.ToUpper() == "EXPO"); - } - } -} Index: trunk/BazaReklam/Classes/VatHelper.cs =================================================================== --- trunk/BazaReklam/Classes/VatHelper.cs (revision 539) +++ (revision ) @@ -1,9 +1,0 @@ -namespace Baza_Reklam -{ - public class VatHelper - { - public const double EU00 = 0.00; - public const double PL07 = 0.07; - public const double PL22 = 0.22; - } -} Index: trunk/BazaReklam/Classes/FakturaHelper.cs =================================================================== --- trunk/BazaReklam/Classes/FakturaHelper.cs (revision 591) +++ (revision ) @@ -1,318 +1,0 @@ -using System; -using System.Collections.Generic; -using System.Data; -using System.Data.SqlClient; -using Baza_Reklam.Classes.Model; -using Baza_Reklam.REKLAMADataSetTableAdapters; - -namespace Baza_Reklam -{ - public class FakturaHelper - { - public enum PaymentType - { - Card = 0, - Transfer = 1, - Cash = 2, - Barter = 3 - } - - public enum ClientId - { - Katowice = 678, - Poznan = 18845 - } - - public enum OfficeId - { - Redakcja = 2, - Katowice = 4, - Poznan = 6 - } - - public enum OrderId - { - Poznan = 272, - Katowice = 273 - } - - public enum BankAccount - { - CentralaPLN = 43 - } - - public static string GetPaymentType(int paymentType) - { - switch (paymentType) - { - case 0: - return "Karta kredytowa"; - case 1: - return "Przelew"; - case 2: - return "Gotówka"; - case 3: - return "Barter"; - default: - return string.Empty; - } - } - - public static bool AddInvoice(REKLAMADataSet reklamaDataSet, FAKTURYTableAdapter fakturaAdapter, - FAKTURA_DETAILSTableAdapter fakturaDetailsAdapter, - WplatyTableAdapter wplataAdapter, REKLAMADataSet.FAKTURYRow fakturaRow) - { - SqlConnection conn; - SqlTransaction transaction = null; - try - { - conn = new SqlConnection(ConnString.getConnString().Value); - fakturaAdapter.Connection = conn; - fakturaDetailsAdapter.Connection = conn; - wplataAdapter.Connection = conn; - - conn.Open(); - - transaction = conn.BeginTransaction(); - fakturaAdapter.AttachTransaction(transaction); - fakturaDetailsAdapter.AttachTransaction(transaction); - wplataAdapter.AttachTransaction(transaction); - - fakturaAdapter.Update(reklamaDataSet.FAKTURY); - fakturaDetailsAdapter.Update(reklamaDataSet.FAKTURA_DETAILS); - wplataAdapter.Update(reklamaDataSet.Wplaty); - transaction.Commit(); - } - catch (Exception) - { - if (transaction != null) transaction.Rollback(); - reklamaDataSet.FAKTURY.RemoveFAKTURYRow(fakturaRow); - throw; - } - return true; - } - - public static void AddInvoiceDetails(REKLAMADataSet.FAKTURYRow newInvoice, - REKLAMADataSet.FAKTURYRow clientInvoice, - REKLAMADataSet.FAKTURA_DETAILSRow detailsRow, - REKLAMADataSet.FAKTURA_DETAILSDataTable detailsTable) - { - REKLAMADataSet.FAKTURA_DETAILSRow row = detailsTable.NewFAKTURA_DETAILSRow(); - - row.ROK = detailsRow.ROK; - row.MIESIAC = detailsRow.MIESIAC; - row.TYTUL = detailsRow.TYTUL; - row.NAZWA_USLUGI = clientInvoice != null - ? clientInvoice.NUMER + "/" + clientInvoice.NUMER_ROZ + "/" + clientInvoice.NUMER_ROK + - " | " + detailsRow.NAZWA_USLUGI - : ""; - row.reklamaId = detailsRow.reklamaId; - row.NR_WYDANIA = detailsRow.NR_WYDANIA; - - row.CENA_JEDN = detailsRow.CENA_JEDN; - row.ILOSC = 1; - row.JM = "szt."; - - row.UPUST_NETTO = detailsRow.UPUST_NETTO; - row.UPUST_PR = detailsRow.UPUST_PR; - row.NETTO = detailsRow.NETTO; - row.S_VAT = VatHelper.PL22; - row.VAT = (decimal)VatHelper.PL22 * detailsRow.NETTO; - row.BRUTTO = detailsRow.NETTO * (decimal)(1 + VatHelper.PL22); - - row.TYP = 2; - row.PODTYP = 1; - row.ID_FAKTURY = newInvoice.ID_FAKTURY; - - row.wyroznienie = (!detailsRow.IswyroznienieNull() && detailsRow.wyroznienie); - - detailsTable.AddFAKTURA_DETAILSRow(row); - } - - public static string GetNazwaUslugiPozKat(REKLAMADataSet.FAKTURYRow faktura, - REKLAMADataSet.FAKTURA_DETAILSRow pozycjaFaktury) - { - if (faktura == null) return string.Empty; - - return faktura.NUMER + "/" + faktura.NUMER_ROZ + "/" + faktura.NUMER_ROK + " | " + - pozycjaFaktury.NAZWA_USLUGI; - } - - public static REKLAMADataSet.FAKTURYRow GetInvoice(DataTable fakturyTable, - REKLAMADataSet.KLIENCIRow klientAgencja, - SLOWNIKDataSet.AGENCJERow agencja, - SLOWNIKDataSet.AGENCJERow redakcja, - SLOWNIKDataSet.KONTARow bankAccount, - string numer_roz, - PaymentType paymentType, - DateTime dateTime) - { - REKLAMADataSet.FAKTURYRow naglowekFaktury = (REKLAMADataSet.FAKTURYRow)fakturyTable.NewRow(); - - naglowekFaktury.KOREKTA = false; - naglowekFaktury.NUMER_ROZ = numer_roz; - naglowekFaktury.NUMER = DbUtils.numerNowejFaktury(naglowekFaktury.NUMER_ROZ, DateTime.Today.Year); - naglowekFaktury.NUMER_ROK = DateTime.Today.Year; - - naglowekFaktury.DATA_WYSTAWIENIA = dateTime != DateTime.Today ? dateTime : DateTime.Today; - - naglowekFaktury.idZamowienia = agencja.Id_agencji == (int)OfficeId.Poznan - ? - (int)OrderId.Poznan - : (int)OrderId.Katowice; - - naglowekFaktury.ID_SPRZEDAWCY = agencja.Id_agencji; - - naglowekFaktury.EKSPORT = false; - - naglowekFaktury.MIEJSCOWOSC_WYSTAWIENIA = redakcja.miasto; - naglowekFaktury.SPRZEDAWCA_ADRES = redakcja.Adres_Fk; - naglowekFaktury.SPRZEDAWCA_NIP = redakcja.NIP; - - naglowekFaktury.PODPIS_WYSTAWIL = string.Empty; - naglowekFaktury.DATA_SPRZEDAZY = naglowekFaktury.DATA_WYSTAWIENIA; - - naglowekFaktury.ID_NABYWCY = klientAgencja.CustomerID; - naglowekFaktury.NABYWCA_ADRES = klientAgencja.Adres_Fkatura; - naglowekFaktury.NABYWCA_NIP = klientAgencja.Nip; - naglowekFaktury.opis = string.Empty; - - naglowekFaktury.SPOSOB_ZAPLATY = GetPaymentType((int)paymentType); - - naglowekFaktury.TERMIN_ZAPLATY = naglowekFaktury.DATA_SPRZEDAZY.AddDays(14); - naglowekFaktury.ZAPLACONO = false; - - naglowekFaktury.FAKTURA_TYP = 2; - naglowekFaktury.FAKTURA_PODTYP = 1; - - naglowekFaktury.ID_KONTA = Convert.ToInt16(bankAccount.id); - - naglowekFaktury.EndEdit(); - return naglowekFaktury; - } - - public static bool IsSumaEmisjiMniejszaOdFakturyNetto(REKLAMADataSet.VIEW_ZESTAWIENIE_FAKTUR_NOWERow faktura) - { - if (faktura.IssumaNettoZEmisjiNull()) return false; - - if (IsFakturaWWalucie(faktura) && !faktura.Iswaluta_kursNull()) - return Math.Round(faktura.sumaNettoZEmisji / faktura.waluta_kurs, 0) < - (decimal)Math.Round(faktura.waluta_brutto, 0); - - return Math.Round(faktura.sumaNettoZEmisji, 0) < Math.Round(faktura.NETTO, 0); - } - - public static bool IsFakturaWWalucie(REKLAMADataSet.VIEW_ZESTAWIENIE_FAKTUR_NOWERow faktura) - { - if (faktura.Iswaluta_mianoNull()) return false; - if (string.IsNullOrEmpty(faktura.waluta_miano)) return false; - if (faktura.waluta_miano.ToUpper() == "PLN") return false; - - return true; - } - - - public static List GetInvoiceList(int parentInvoiceId) - { - List invoices = new List(); - SqlConnection conn = null; - SqlCommand cmd = null; - SqlDataReader reader = null; - - string query = - "SELECT IDPozKat AS Id, NrFakturyPozKat AS Label, NettoPozKat AS Amount, DataWystawieniaPozKat AS Date FROM dbo.FakturyPoznanKatowice WHERE ID=@id"; - - try - { - conn = new SqlConnection(ConnString.getConnString().Value); - conn.Open(); - cmd = new SqlCommand(query, conn); - cmd.Parameters.AddWithValue("@id", parentInvoiceId); - reader = cmd.ExecuteReader(); - - while (reader.Read()) - { - Invoice invoice = new Invoice(); - invoice.Id = Convert.ToInt32(reader["Id"]); - invoice.Label = Convert.ToString(reader["Label"]); - invoice.Amount = Convert.ToDecimal(reader["Amount"]); - invoice.Date = Convert.ToDateTime(reader["Date"]); - invoices.Add(invoice); - } - } - finally - { - if (reader != null) - { - reader.Close(); - reader.Dispose(); - } - if (cmd != null) - { - cmd.Dispose(); - } - if (conn != null) - { - conn.Close(); - conn.Dispose(); - } - } - - return invoices; - } - - public static List GetPartnerInvoiceList(int year, int month, string partner) - { - List invoices = new List(); - SqlConnection conn = null; - SqlCommand cmd = null; - SqlDataReader reader = null; - - try - { - conn = new SqlConnection(ConnString.getConnString().Value); - conn.Open(); - cmd = new SqlCommand("sp_FindPartnerInvoices", conn); - cmd.CommandType = CommandType.StoredProcedure; - cmd.Parameters.AddWithValue("@year", year); - cmd.Parameters.AddWithValue("@month", month); - cmd.Parameters.AddWithValue("@partner", partner); - reader = cmd.ExecuteReader(); - - while (reader.Read()) - { - Invoice invoice = new Invoice(); - invoice.Id = Convert.ToInt32(reader["Id"]); - invoice.Label = Convert.ToString(reader["Number"]) + "/" + Convert.ToString(reader["AgentShortCut"]) + "/" + Convert.ToString(reader["Year"]); - invoice.Amount = Convert.ToDecimal(reader["Amount"]); - invoice.Date = Convert.ToDateTime(reader["Date"]); - invoice.PaidAmount = Convert.ToDecimal(reader["TotalPaid"]); - invoice.IsCorrectionInvoice = Convert.ToBoolean(reader["Correction"]); - invoice.Currency = Convert.ToString(reader["Currency"]); - invoice.WeekNumber = Convert.ToInt32(reader["WeekNumber"]); - invoices.Add(invoice); - } - } - finally - { - if (reader != null) - { - reader.Close(); - reader.Dispose(); - } - if (cmd != null) - { - cmd.Dispose(); - } - if (conn != null) - { - conn.Close(); - conn.Dispose(); - } - } - - return invoices; - } - - } -} Index: trunk/BazaReklam/Classes/ExcelHelper.cs =================================================================== --- trunk/BazaReklam/Classes/ExcelHelper.cs (revision 539) +++ (revision ) @@ -1,42 +1,0 @@ -using System.Windows.Forms; - -namespace Baza_Reklam -{ - public class ExcelHelper - { - public void Export(DataGridView source) - { - Excel.ApplicationClass excel = new Excel.ApplicationClass(); - excel.Application.Workbooks.Add(true); - DataGridView table = source; - int cIndex = 0; - foreach (DataGridViewColumn col in table.Columns) - { - if (col.Visible) - { - cIndex++; - excel.Cells[1, cIndex] = col.HeaderText; - } - } - int rIndex = 0; - - foreach (DataGridViewRow row in table.Rows) - { - rIndex++; - cIndex = 0; - foreach (DataGridViewColumn col in table.Columns) - { - if (col.Visible) - { - cIndex++; - excel.Cells[rIndex + 1, cIndex] = row.Cells[col.Name].Value == null - ? "" - : row.Cells[col.Name].Value.ToString(); - } - } - } - // excel.Save("New.xls"); - excel.Visible = true; - } - } -} Index: trunk/BazaReklam/Classes/ZamowienieHelper.cs =================================================================== --- trunk/BazaReklam/Classes/ZamowienieHelper.cs (revision 557) +++ (revision ) @@ -1,36 +1,0 @@ -using System.Data; -using System.Data.SqlClient; - -namespace Baza_Reklam.Classes -{ - public class ZamowienieHelper - { - public static void MakeZamowienieZafakturowane(int zamowienieId) - { - SqlConnection conn = null; - SqlCommand cmd = null; - - try - { - conn = new SqlConnection(ConnString.getConnString().Value); - conn.Open(); - cmd = new SqlCommand("sp_MakeZamowienieZafakturowane", conn); - cmd.CommandType = CommandType.StoredProcedure; - cmd.Parameters.AddWithValue("@zamowienieId", zamowienieId); - cmd.ExecuteNonQuery(); - } - finally - { - if (cmd != null) - cmd.Dispose(); - - if (conn != null && conn.State == ConnectionState.Open) - { - conn.Close(); - conn.Dispose(); - } - } - - } - } -} Index: trunk/BazaReklam/Classes/WordHandler.cs =================================================================== --- trunk/BazaReklam/Classes/WordHandler.cs (revision 539) +++ (revision ) @@ -1,51 +1,0 @@ -using System; -using System.Reflection; -using Word; - -namespace Baza_Reklam -{ - internal class WordHandler - { - private readonly Application oWord; - private Object oFalse = false; - private Object oMissing = Missing.Value; - private Object oTrue = true; - private Document oWordDoc; - - public WordHandler() - { - oWord = new Application(); - } - - public void openFile(string filename) - { - Object oPlik = filename; - oWordDoc = oWord.Documents.Open(ref oPlik, ref oMissing, ref oMissing, ref oMissing, ref oMissing, - ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, - ref oMissing, ref oTrue); - } - - public void setWordVisible() - { - if (!oWord.Visible) oWord.Visible = true; - } - - public void findAndReplace(string _strFind, string _strReplace) - { - Object oFindText = _strFind; - Object oReplaceText = _strReplace; - Object oReplace = WdReplace.wdReplaceAll; - bool _boolToF = oWordDoc.Content.Find.Execute(ref oFindText, ref oFalse, ref oFalse, ref oFalse, ref oFalse, - ref oFalse, ref oFalse, ref oMissing, ref oFalse, - ref oReplaceText, ref oReplace, ref oFalse, ref oFalse, - ref oFalse, ref oFalse); - } - - public void saveAs(string filename) - { - Object oFileNameAs = filename; - oWordDoc.SaveAs(ref oFileNameAs, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, - ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing); - } - } -} Index: trunk/BazaReklam/FacturesForm.cs =================================================================== --- trunk/BazaReklam/FacturesForm.cs (revision 591) +++ trunk/BazaReklam/FacturesForm.cs (revision 602) @@ -4,4 +4,5 @@ using System.Data.SqlClient; using System.Windows.Forms; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces; Index: trunk/BazaReklam/KorektyForm.cs =================================================================== --- trunk/BazaReklam/KorektyForm.cs (revision 591) +++ trunk/BazaReklam/KorektyForm.cs (revision 602) @@ -7,4 +7,5 @@ using System.Text; using System.Windows.Forms; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces; Index: trunk/BazaReklam/app.config =================================================================== --- trunk/BazaReklam/app.config (revision 599) +++ trunk/BazaReklam/app.config (revision 602) @@ -8,4 +8,6 @@ + Index: trunk/BazaReklam/FacturesFormNEW.cs =================================================================== --- trunk/BazaReklam/FacturesFormNEW.cs (revision 591) +++ trunk/BazaReklam/FacturesFormNEW.cs (revision 602) @@ -5,4 +5,5 @@ using System.Drawing; using System.Windows.Forms; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces; Index: trunk/BazaReklam/InfoForm.cs =================================================================== --- trunk/BazaReklam/InfoForm.cs (revision 591) +++ trunk/BazaReklam/InfoForm.cs (revision 602) @@ -5,4 +5,5 @@ using System.Windows.Forms; using System.Globalization; +using Baza_Reklam.Classes.Helpers; using Baza_Reklam.Classes.Interfaces;