public static DataTable getAppSetting()
{
string sql = "select SettingKey, SettingValue from tb_Setting";
return(ConnectionDB.GetData(sql));
}
private string _backupDatabase(string tt)
{
System.Diagnostics.Debug.WriteLine("This is backup by " + tt);
string _info = "Success";
string _bkFolderNameSQL = string.Format(@"{0}\DBRESSer_Local\{1:M.dd.yyyy HH..mm..ss tt}", Directory.GetCurrentDirectory(), DateTime.Now);
string _bkFolderNameSQLite = string.Format(@"{0}\DBRES_Local\{1:M.dd.yyyy HH..mm..ss tt}", Directory.GetCurrentDirectory(), DateTime.Now);
if (StaticClass.GeneralClass.flag_database_type_general) //sql server
{
if (!Directory.Exists(_bkFolderNameSQL))
{
Directory.CreateDirectory(_bkFolderNameSQL);
}
string _fileBackup = string.Format(@"{0}\{1}.bak", _bkFolderNameSQL, ConnectionDB.getSqlServerDataBaseName());
SqlConnection sqlConnect = ConnectionDB.getSQLConnection();
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}' WITH NOFORMAT, NOINIT, NAME = N'data-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10", ConnectionDB.getSqlServerDataBaseName(), @_fileBackup);
try
{
using (var command = new SqlCommand(query, sqlConnect))
{
command.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
_info = ex.Message;
}
}
else //sqlite
{
if (!Directory.Exists(_bkFolderNameSQLite))
{
Directory.CreateDirectory(_bkFolderNameSQLite);
}
string _fileBackup = string.Format(@"{0}\{1}.db", _bkFolderNameSQLite, ConnectionDB.getSqlServerDataBaseName());
using (var destination = new SQLiteConnection(@"Data Source=" + _fileBackup + @"; Version=3; Password = '******'"))
{
try
{
destination.Open();
ConnectionDB.getSQLiteConnection().BackupDatabase(destination, "main", "main", -1, null, 0);
destination.Close();
}
catch (SQLiteException ex)
{
_info = ex.Message;
}
}
}
return(_info);
}
//MainWindow
public MainWindow()
{
//Properties.Settings.Default.Reset();
app.mainWindow = this;
//how to check if another instance of the application is running
if (System.Diagnostics.Process.GetProcessesByName(System.IO.Path.GetFileNameWithoutExtension(System.Reflection.Assembly.GetEntryAssembly().Location)).Count() > 1)
{
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
//check database type
if (!System.IO.File.Exists(current_directory + @"\sqltype"))
{
System.IO.StreamWriter streamwriter = new System.IO.StreamWriter(current_directory + @"\_sqltype");
streamwriter.WriteLine("sqltype:sqlite");
streamwriter.WriteLine("server:" + server);
streamwriter.WriteLine("authentication:" + authentication);
streamwriter.WriteLine("id:" + id);
streamwriter.WriteLine("password:"******"\_sqltype", current_directory + @"\sqltype", StaticClass.GeneralClass.key_register_general);
}
if (System.IO.File.Exists(current_directory + @"\sqltype"))
{
System.IO.StreamReader stream_sqltype = StaticClass.GeneralClass.DecryptFileGD(current_directory + @"\sqltype", StaticClass.GeneralClass.key_register_general);
sqltype = stream_sqltype.ReadLine().Split(':')[1].ToString();
server = stream_sqltype.ReadLine().Split(':')[1].ToString();
Int32.TryParse(stream_sqltype.ReadLine().Split(':')[1].ToString(), out authentication);
id = stream_sqltype.ReadLine().Split(':')[1].ToString();
password = stream_sqltype.ReadLine().Split(':')[1].ToString();
if (stream_sqltype != null)
{
stream_sqltype.Close();
}
if (sqltype == "sqlserver")
{
//set sql type
StaticClass.GeneralClass.flag_database_type_general = true;
OpenConnectionForm();
}
}
ConnectionDB.ConnectionDBInitialize();
//check version app
try
{
double _version = Convert.ToDouble(Properties.Settings.Default.currentVersion.Replace("2.1.", "2.1"));
if (_version <= 2.15)
{
FuncUpgradeDatabase();
Properties.Settings.Default.currentVersion = "2.1.6";
Properties.Settings.Default.Save();
}
else
{
FuncUpgradeDatabase();
}
}
catch
{
FuncUpgradeDatabase();
}
if (File.Exists(current_directory + @"\appSettings.json"))
{
System.Collections.Hashtable _hash = null;
using (StreamReader file = new StreamReader(current_directory + @"\appSettings.json", System.Text.Encoding.UTF8, true))
{
_hash = Newtonsoft.Json.JsonConvert.DeserializeObject <System.Collections.Hashtable>(file.ReadLine().ToString());
Model.UpgradeDatabase.updateAppSetting(_hash);
file.Close();
}
if (_hash != null)
{
File.Delete(current_directory + @"\appSettings.json");
}
}
DataTable _appSetting = Model.UpgradeDatabase.getAppSetting();
if (_appSetting.Rows.Count > 0)
{
foreach (DataRow _dr in _appSetting.Rows)
{
StaticClass.GeneralClass.app_settings.Add(_dr["SettingKey"].ToString(), _dr["SettingValue"]);
}
}
//load app language
StaticClass.GeneralClass.dict_language_current.Source = new Uri("..\\Languages\\" + StaticClass.GeneralClass.app_settings["language"].ToString() + ".xaml", UriKind.Relative);
Application.Current.Resources.MergedDictionaries.Add(StaticClass.GeneralClass.dict_language_current);
InitializeComponent();
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commadddddMMMMyyyy.ToString(), "dddd, d MMMM, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commaddddMMMMdyyyy.ToString(), "dddd, MMMM d, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commadMMMMyyyy.ToString(), "d MMMM, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commaMMMMdyyyy.ToString(), "MMMM d, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commadMMMyyyy.ToString(), "d MMM, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.commaMMMdyyyy.ToString(), "MMM d, yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.slashddMMMyyyy.ToString(), "dd-MMM-yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.slashMdyyyy.ToString(), "M/d/yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.slashMMddyyyy.ToString(), "MM/dd/yyyy");
StaticClass.GeneralClass.dateFromatSettings.Add(dateFormat.slashyyyyMMdd.ToString(), "yyyy/MM/dd");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.None.ToString(), "null");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.HHmm.ToString(), "HH:mm");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.HHmmss.ToString(), "HH:mm:ss");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.hhmmsstt.ToString(), "hh:mm:ss tt");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.hhmmtt.ToString(), "hh:mm tt");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.Hmm.ToString(), ":H:mm");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.Hmmss.ToString(), "H:mm:ss");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.hmmsstt.ToString(), "h:mm:ss tt");
StaticClass.GeneralClass.timeFromatSettings.Add(timeFormat.hmmtt.ToString(), "h:mm tt");
//create app menu
app.linkGroupHome = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_home").ToString()
};
app.linkGroupReport = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_report").ToString()
};
app.linkGroupReport.Links.Add(new Link()
{
DisplayName = "", Source = new Uri(@"/Pages/Report/Report.xaml", UriKind.Relative)
});
app.linkGroupSetting = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_setting").ToString()
};
app.linkGroupSetting.Links.Add(new Link()
{
DisplayName = "", Source = new Uri(@"/Pages/Setting/Setting.xaml", UriKind.Relative)
});
app.linkGroupChart = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_chart").ToString()
};
app.linkGroupChart.Links.Add(new Link()
{
DisplayName = "", Source = new Uri(@"/Pages/Chart/RevenueProfitChart.xaml", UriKind.Relative)
});
app.linkGroupStatistic = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_statistic").ToString()
};
app.linkGroupStatistic.Links.Add(new Link()
{
DisplayName = "", Source = new Uri(@"/Pages/Statistic/Home.xaml", UriKind.Relative)
});
app.linkGroupOption = new LinkGroup()
{
DisplayName = Application.Current.FindResource("lg_option").ToString()
};
app.linkGroupLogInOut = new LinkGroup();
app.linkLogin = new Link()
{
DisplayName = Application.Current.FindResource("l_login").ToString(), Source = new Uri(@"/Pages/Home/Login.xaml", UriKind.Relative)
};
app.linkLogout = new Link()
{
DisplayName = Application.Current.FindResource("l_logout").ToString(), Source = new Uri(@"/Pages/Home/Logout.xaml", UriKind.Relative)
};
app.linkAccount = new Link()
{
DisplayName = Application.Current.FindResource("hi").ToString() + " " + StaticClass.GeneralClass.name_user_general, Source = new Uri(@"/Pages/Setting/Account.xaml", UriKind.Relative)
};
this.TitleLinks.Add(app.linkLogin);
//create link for setting page
new Thread(() =>
{
try
{
app.linkCategory = new Link()
{
DisplayName = FindResource("category").ToString(), Source = new Uri(@"/Pages/Setting/Product.xaml", UriKind.Relative)
};
app.linkCurrency = new Link()
{
DisplayName = FindResource("currency").ToString(), Source = new Uri(@"/Pages/Setting/Currency.xaml", UriKind.Relative)
};
app.linkCustomer = new Link()
{
DisplayName = FindResource("customer").ToString(), Source = new Uri(@"/Pages/Setting/Customer.xaml", UriKind.Relative)
};
app.linkSalesperson = new Link()
{
DisplayName = FindResource("salesperson").ToString(), Source = new Uri(@"/Pages/Setting/Salesperson.xaml", UriKind.Relative)
};
app.linkPayment = new Link()
{
DisplayName = FindResource("payment").ToString(), Source = new Uri(@"/Pages/Setting/Payment.xaml", UriKind.Relative)
};
app.linkUser = new Link()
{
DisplayName = FindResource("user").ToString(), Source = new Uri(@"/Pages/Setting/User.xaml", UriKind.Relative)
};
app.linkBackup = new Link()
{
DisplayName = FindResource("backup_restore").ToString(), Source = new Uri(@"/Pages/Setting/BackupDB.xaml", UriKind.Relative)
};
app.linkAppSetting = new Link()
{
DisplayName = FindResource("app_setting").ToString(), Source = new Uri(@"/Pages/Setting/AppSetting.xaml", UriKind.Relative)
};
app.lnkGiftCard = new Link()
{
DisplayName = FindResource("gift_card").ToString(), Source = new Uri(@"Views/Setting/GiftCard.xaml", UriKind.Relative)
};
}
catch (Exception ex)
{
MessageBox.Show(FindResource("error").ToString() + ": " + ex.Message, FindResource("notification").ToString(), MessageBoxButton.OK, MessageBoxImage.Error);
}
}).Start();
//load theme color
accent_color = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString(StaticClass.GeneralClass.app_settings["accentColor"].ToString());
AppearanceManager.Current.AccentColor = accent_color;
CheckRegisterInto();
}
public AtomController(ConnectionDB context)
{
_context = context;
}
public DataTable InsertaActualizaComunicaciones
(string strRPU,
string strchkPM1, string strchkPM2,
string strcmbActualMeter, string strcmbMeterType, string strcmbRequiredMeter,
string strcmbCommunicationClass,
string strcmbCommunicationType,
string strcmbLocalCommunication,
string strcmbCFECommunication
, string strIdUsuario
)
{
ConnectionDB con = new ConnectionDB();
Boolean bMsg = true;
DataTable dtData;
try
{
string query = string.Format("spInsertaActualizaComunicaciones");
SqlParameter[] sqlParameters = new SqlParameter[11];
sqlParameters[0] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[0].Value = strRPU;
sqlParameters[1] = new SqlParameter("@bTCyTP", SqlDbType.NVarChar);
sqlParameters[1].Value = strchkPM1;
sqlParameters[2] = new SqlParameter("@bBase13", SqlDbType.NVarChar);
sqlParameters[2].Value = strchkPM2;
sqlParameters[3] = new SqlParameter("@IdMedidorActual", SqlDbType.NVarChar);
sqlParameters[3].Value = strcmbActualMeter;
sqlParameters[4] = new SqlParameter("@IdTipoMedidor", SqlDbType.NVarChar);
sqlParameters[4].Value = strcmbMeterType;
sqlParameters[5] = new SqlParameter("@IdMedidorRequerido", SqlDbType.NVarChar);
sqlParameters[5].Value = strcmbRequiredMeter;
sqlParameters[6] = new SqlParameter("@IdComunicacionClase", SqlDbType.NVarChar);
sqlParameters[6].Value = strcmbCommunicationClass;
sqlParameters[7] = new SqlParameter("@IdTipoComunicacion", SqlDbType.NVarChar);
sqlParameters[7].Value = strcmbCommunicationType;
sqlParameters[8] = new SqlParameter("@IdEstatusLocal", SqlDbType.NVarChar);
sqlParameters[8].Value = strcmbLocalCommunication;
sqlParameters[9] = new SqlParameter("@IdEstatusCFE", SqlDbType.NVarChar);
sqlParameters[9].Value = strcmbCFECommunication;
sqlParameters[10] = new SqlParameter("@IdUsuario", SqlDbType.NVarChar);
sqlParameters[10].Value = strIdUsuario;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
return(dtData);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "spInsertaActualizaComunicaciones";
clsError.LogWrite();
bMsg = false;
return(null);
}
//return bMsg;
}
private void FrmThemVaiTro_Load(object sender, EventArgs e)
{
txtMaVaiTro.Text = ConnectionDB.GenerateMa("sp_LayMaVaiTro");
txtMaVaiTro.ReadOnly = true;
}
//LISTE DES ASSUJETTIS DU NOUVEAU SYSTEME (à comparer à celui selectionné précedemment)
//=====================================================================================
public List <Person> lstAssujettiNS(string pnom)
{
string[] nom = pnom.Split(' ');
ConnectionDB con = new ConnectionDB(2);
DataTable dtt = con.Data_Source("SELECT * from " +
"( " +
"SELECT person.id, " +
"physical_person.name + ' ' + physical_person.last_name + ' ' + physical_person.nick_name as Nom, " +
"'-' AS Sigle, " +
"'-' AS numid_nat, " +
"'-' as tax_num_dgi, " +
"person.email, " +
"person.p_o_box, " +
"person.prov, " +
"person.town_dist, " +
"person.commune, " +
"person.quarter_sect, " +
"person.avenue_loc, " +
"person.number " +
"FROM person " +
"INNER JOIN physical_person ON person.id = physical_person.id " +
"union " +
"SELECT person.id, " +
"company.company_name as Nom, " +
"company.initials AS Sigle, " +
"company.numid_nat, " +
"company.tax_num_dgi, " +
"person.email, " +
"person.p_o_box, " +
"person.prov, " +
"person.town_dist, " +
"person.commune, " +
"person.quarter_sect, " +
"person.avenue_loc, " +
"person.number " +
"FROM person " +
"INNER JOIN company ON person.id = company.id " +
") as Personne " +
"where Nom like '%" + nom[0] + "%' and Nom like '%" + nom[1] + "%'"
, "person");
List <Person> lst = new List <Person>();
for (int i = 0; i < dtt.Rows.Count; i++)
{
lst.Add(new Person()
{
nbre = i + 1,
Id_person = dtt.Rows[i]["id"].ToString(),
name = dtt.Rows[i]["Nom"].ToString(),
initials = dtt.Rows[i]["Sigle"].ToString(),
numid_nat = dtt.Rows[i]["numid_nat"].ToString(),
tax_num_dgi = dtt.Rows[i]["tax_num_dgi"].ToString(),
email = dtt.Rows[i]["email"].ToString(),
p_o_box = dtt.Rows[i]["p_o_box"].ToString(),
province = dtt.Rows[i]["prov"].ToString(),
town_dist = dtt.Rows[i]["town_dist"].ToString(),
commune = dtt.Rows[i]["commune"].ToString(),
quarter = dtt.Rows[i]["quarter_sect"].ToString(),
avenue = dtt.Rows[i]["avenue_loc"].ToString(),
number = dtt.Rows[i]["number"].ToString()
});
}
return(lst);
}
public ModificarMedicamentoController(FRM_ModificarMedicamento frm_ModificarMedicamento)
{
this.frm_ModificarMedicamento = frm_ModificarMedicamento;;
connectionDB = new ConnectionDB();
AgregarEventosMedicamentos();
}
public ProductCategoryController(ConnectionDB context)
{
_context = context;
}
private List <PayrollModel> loadDataGridDetails()
{
conDB = new ConnectionDB();
List <PayrollModel> lstPayroll = new List <PayrollModel>();
PayrollModel payroll = new PayrollModel();
queryString = "SELECT tblpayroll.ID, empID, tblpayroll.startdate, enddate, sss, philhealth, pagibig, " +
"sssloan, isap, isavings, pey, pel, grl, eml, electricbill, cashadvance, " +
"absent, lates, undertime, others, remarks, firstname, lastname, netpay, workdays, trips, othours, ottotal, " +
"allowance, commission, particularothers, deductionothers, totalpel, totaleml, totalgrl, totalpey, " +
"totalelectbill, totalsssloan, totalis, totalisap, clinicloan, totalclinicloan FROM (tblpayroll INNER JOIN " +
"tblemployees ON tblpayroll.empID = tblemployees.ID) WHERE tblpayroll.isDeleted = 0 " +
"AND tblemployees.isDeleted = 0 ORDER BY startdate DESC";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
payroll.ID = reader["ID"].ToString();
payroll.EmployeeID = reader["empID"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
payroll.StartDate = dte.ToShortDateString();
dte = DateTime.Parse(reader["enddate"].ToString());
payroll.EndDate = dte.ToShortDateString();
payroll.SSS = reader["sss"].ToString();
payroll.PhilHealth = reader["philhealth"].ToString();
payroll.PagIbig = reader["pagibig"].ToString();
payroll.SSSLoan = reader["sssloan"].ToString();
payroll.ISAP = reader["isap"].ToString();
payroll.IS = reader["isavings"].ToString();
payroll.PEY = reader["pey"].ToString();
payroll.PEL = reader["pel"].ToString();
payroll.GRL = reader["grl"].ToString();
payroll.EML = reader["eml"].ToString();
payroll.ElecBill = reader["electricbill"].ToString();
payroll.CashAdvance = reader["cashadvance"].ToString();
payroll.Absent = reader["absent"].ToString();
payroll.Lates = reader["lates"].ToString();
payroll.Undertime = reader["undertime"].ToString();
payroll.Others = reader["others"].ToString();
payroll.Remarks = reader["remarks"].ToString();
payroll.NetPay = reader["netpay"].ToString();
string firstName = reader["firstname"].ToString();
string lastName = reader["lastname"].ToString();
payroll.FullName = lastName + ", " + firstName;
payroll.WorkDays = reader["workdays"].ToString();
payroll.Trips = reader["trips"].ToString();
payroll.OTHours = reader["othours"].ToString();
payroll.OTTotal = reader["ottotal"].ToString();
payroll.Allowance = reader["allowance"].ToString();
payroll.Commission = reader["commission"].ToString();
payroll.ParticularOthers = reader["particularothers"].ToString();
payroll.DeductionOthers = reader["deductionothers"].ToString();
payroll.TotalPEL = reader["totalpel"].ToString();
payroll.TotalEML = reader["totaleml"].ToString();
payroll.TotalGRL = reader["totalgrl"].ToString();
payroll.TotalPEY = reader["totalpey"].ToString();
payroll.TotalElectBill = reader["totalelectbill"].ToString();
payroll.TotalSSSLoan = reader["totalsssloan"].ToString();
payroll.TotalIS = reader["totalis"].ToString();
payroll.TotalISAP = reader["totalisap"].ToString();
payroll.TotalClinicLoan = reader["totalclinicloan"].ToString();
payroll.ClinicLoan = reader["clinicloan"].ToString();
lstPayroll.Add(payroll);
payroll = new PayrollModel();
}
conDB.closeConnection();
return(lstPayroll);
}
private List <PayrollModel> searchEmployees()
{
conDB = new ConnectionDB();
List <PayrollModel> lstSearching = new List <PayrollModel>();
PayrollModel payroll = new PayrollModel();
queryString = "SELECT tblpayroll.ID, empID, tblpayroll.startdate, enddate, sss, philhealth, pagibig, " +
"sssloan, isap, isavings, pey, pel, grl, eml, electricbill, cashadvance, " +
"absent, lates, undertime, others, remarks, firstname, lastname, netpay, workdays, trips, othours, ottotal, " +
"allowance, commission, particularothers, deductionothers, totalpel, totaleml, totalgrl, totalpey, " +
"totalelectbill, totalsssloan, totalis, totalisap, clinicloan, totalclinicloan FROM (tblpayroll INNER JOIN " +
"tblemployees ON tblpayroll.empID = tblemployees.ID) WHERE tblpayroll.isDeleted = 0 " +
"AND tblemployees.isDeleted = 0 ";
parameters = new List <string>();
if (chkByDate.IsChecked.Value && (!string.IsNullOrEmpty(searchDateFrom.Text) &&
!string.IsNullOrEmpty(searchDateTo.Text)))
{
queryString += " AND (tblpayroll.startdate BETWEEN ? AND ? AND enddate BETWEEN ? AND ?)";
DateTime sdate = DateTime.Parse(searchDateFrom.Text);
parameters.Add(sdate.Year + "/" + sdate.Month + "/" + sdate.Day);
sdate = DateTime.Parse(searchDateTo.Text);
parameters.Add(sdate.Year + "/" + sdate.Month + "/" + sdate.Day);
sdate = DateTime.Parse(searchDateFrom.Text);
parameters.Add(sdate.Year + "/" + sdate.Month + "/" + sdate.Day);
sdate = DateTime.Parse(searchDateTo.Text);
parameters.Add(sdate.Year + "/" + sdate.Month + "/" + sdate.Day);
}
if (chkByCompany.IsChecked.Value && cmbSearchCompany.SelectedItem != null)
{
queryString += " AND (tblemployees.companyID = ?)";
parameters.Add(cmbSearchCompany.SelectedValue.ToString());
}
if (chkByEmployee.IsChecked.Value && cmbSearchEmployee.SelectedItem != null)
{
queryString += " AND (empID =?)";
parameters.Add(cmbSearchEmployee.SelectedValue.ToString());
}
queryString += " ORDER BY tblpayroll.startdate DESC";
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
payroll.ID = reader["ID"].ToString();
payroll.EmployeeID = reader["empID"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
payroll.StartDate = dte.ToShortDateString();
dte = DateTime.Parse(reader["enddate"].ToString());
payroll.EndDate = dte.ToShortDateString();
payroll.SSS = reader["sss"].ToString();
payroll.PhilHealth = reader["philhealth"].ToString();
payroll.PagIbig = reader["pagibig"].ToString();
payroll.SSSLoan = reader["sssloan"].ToString();
payroll.ISAP = reader["isap"].ToString();
payroll.IS = reader["isavings"].ToString();
payroll.PEY = reader["pey"].ToString();
payroll.PEL = reader["pel"].ToString();
payroll.GRL = reader["grl"].ToString();
payroll.EML = reader["eml"].ToString();
payroll.ElecBill = reader["electricbill"].ToString();
payroll.CashAdvance = reader["cashadvance"].ToString();
payroll.Absent = reader["absent"].ToString();
payroll.Lates = reader["lates"].ToString();
payroll.Undertime = reader["undertime"].ToString();
payroll.Others = reader["others"].ToString();
payroll.Remarks = reader["remarks"].ToString();
payroll.NetPay = reader["netpay"].ToString();
string firstName = reader["firstname"].ToString();
string lastName = reader["lastname"].ToString();
payroll.FullName = lastName + ", " + firstName;
payroll.WorkDays = reader["workdays"].ToString();
payroll.Trips = reader["trips"].ToString();
payroll.OTHours = reader["othours"].ToString();
payroll.OTTotal = reader["ottotal"].ToString();
payroll.Allowance = reader["allowance"].ToString();
payroll.Commission = reader["commission"].ToString();
payroll.ParticularOthers = reader["particularothers"].ToString();
payroll.DeductionOthers = reader["deductionothers"].ToString();
payroll.TotalPEL = reader["totalpel"].ToString();
payroll.TotalEML = reader["totaleml"].ToString();
payroll.TotalGRL = reader["totalgrl"].ToString();
payroll.TotalPEY = reader["totalpey"].ToString();
payroll.TotalElectBill = reader["totalelectbill"].ToString();
payroll.TotalSSSLoan = reader["totalsssloan"].ToString();
payroll.TotalIS = reader["totalis"].ToString();
payroll.TotalISAP = reader["totalisap"].ToString();
payroll.ClinicLoan = reader["clinicloan"].ToString();
payroll.TotalClinicLoan = reader["totalclinicloan"].ToString();
lstSearching.Add(payroll);
payroll = new PayrollModel();
}
conDB.closeConnection();
return(lstSearching);
}
public Mensaje getPortalesByKey(string clave, string[] accesLog)
{
try
{
ConnectionDB conn = new ConnectionDB();
conn.tipoServer = TypeDB.SQLServer;
conn.nombreBd = Encoding.UTF8.GetString(Convert.FromBase64String(accesLog[0]));
conn.usuario = Encoding.UTF8.GetString(Convert.FromBase64String(accesLog[1]));
conn.password = Encoding.UTF8.GetString(Convert.FromBase64String(accesLog[2]));
conn.puertoServer = Encoding.UTF8.GetString(Convert.FromBase64String(accesLog[3]));
conn.server = Encoding.UTF8.GetString(Convert.FromBase64String(accesLog[4]));
inicializaVariableMensaje();
ISession session = NHibernateCxn.createSessionFactorie(conn, 3).OpenSession();
setSession(session);
getSession().BeginTransaction();
var portal = (from p in getSession().Query <Portales>()
where p.clave == clave
select new
{
id = p.id,
clave = p.clave,
nombre = p.nombre,
textoPrincipal = p.textoPrincipal,
url = p.url,
estado = p.estado,
postLogin = p.postLogin,
imgLogo = p.imgLogo,
imgFondo = p.imgFondo,
imgBanner = p.imgBanner,
isNombreCorpo = p.isNombreCorpo,
isBannerCorpo = p.isBannerCorpo,
textoBienvenida = p.textoBienvenida,
imgBannerLogo = p.imgBannerLogo,
colorFondo = p.colorFondo,
colorFuente = p.colorFuente,
colorExceEncabe = p.colorExceEncabe,
colorExceTextoEncabe = p.colorExceTextoEncabe,
colorExceGruposTot = p.colorExceGruposTot,
configLoader = p.configLoader
}).FirstOrDefault();
mensajeResultado.resultado = portal;
mensajeResultado.noError = 0;
mensajeResultado.error = "";
EndSession(getSession());
/*getSession().Transaction.Commit();*/
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(concatena.Remove(0, concatena.Length).Append(msgError).Append("getPortalesByHost()1_Error: ").Append(ex));
if (getSession().Transaction.IsActive)
{
getSession().Transaction.Rollback();
}
mensajeResultado.noError = ControlErroresEntity.buscaNoErrorPorExcepcion(ex);
mensajeResultado.error = ex.GetBaseException().ToString();
mensajeResultado.resultado = null;
}
return(mensajeResultado);
}
public Mensaje validateAccesUser(List <PortalesServidoresBd> configs, string user, string password)
{
ISession sessionMaster = null;
try
{
inicializaVariableMensaje();
PortalesServidoresBd serverMaster = new PortalesServidoresBd();
foreach (PortalesServidoresBd item in configs)
{
if (item.tipoDeBD == 1)
{
serverMaster = item;
break;
}
}
String encodePass = Exitosw.Payroll.Core.util.Utilerias.createMD5(password);
ConnectionDB conn = JsonConvert.DeserializeObject <ConnectionDB>(serverMaster.servidoresBd.cadenaCxn);
conn.nombreBd = serverMaster.nombreBd;
ISessionFactory factorieAcces = NHibernateCxn.createSessionFactorie(conn, serverMaster.tipoDeBD);
sessionMaster = factorieAcces.OpenSession();
IQuery q;//GET Global variables
sessionMaster.BeginTransaction();
q = sessionMaster.CreateQuery("from Usuario u where u.nombre = :nombre and u.password = :password");
q.SetParameter("nombre", user);
q.SetParameter("password", encodePass);
Usuario us = q.UniqueResult <Usuario>();
//q = sessionMaster.CreateQuery("select razonSocial.claveRazonSocial from RazonSocialConfiguracion ra where ra.usuario.clave = :clave");
//q.SetParameter("clave", us.clave);
//IList<string> rzCnf = q.List<string>();
//string rzCnfSep = "";
//if (rzCnf.Count > 0)
//{
// string[] array = rzCnf.ToArray();
// rzCnfSep = string.Join(".", array);
//} , rzCnfSep
Object[] info;
if (us == null)
{
info = null;
}
else
{
info = new Object[] { us.id, us.nombre };
}
mensajeResultado.resultado = info;
if (sessionMaster.Transaction != null && sessionMaster.Transaction.IsActive)
{
sessionMaster.Transaction.Commit();
}
sessionMaster.Dispose();
mensajeResultado.noError = 0;
mensajeResultado.error = "";
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(concatena.Remove(0, concatena.Length).Append(msgError).Append("validateAccesUser()1_Error: ").Append(ex));
if (sessionMaster.Transaction.IsActive)
{
sessionMaster.Transaction.Rollback();
}
mensajeResultado.noError = ControlErroresEntity.buscaNoErrorPorExcepcion(ex);
mensajeResultado.error = ex.GetBaseException().ToString();
mensajeResultado.resultado = null;
}
return(mensajeResultado);
}
public List <clsCourseScheme> getCourseSchemes()
{
List <clsCourseScheme> SubjectList = new List <clsCourseScheme>();
DataTable ds = new DataTable();
try
{
using (SqlConnection conn = ConnectionDB.OpenConnection())
{
SqlCommand sqlComm = new SqlCommand("[dbo].[Proc_GetCourseScheme]", conn);
if (CourseID > 0)
{
sqlComm.Parameters.AddWithValue("@CourseID", CourseID);
}
sqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlComm;
da.Fill(ds);
}
int l = 0, t = 0, p = 0, c = 0, ca = 0, ese = 0, tmm = 0, tm = 0, dur = 0;
bool b = false;
foreach (DataRow dr in ds.Rows)
{
b = int.TryParse(dr["CourseLHr"].ToString(), out l);
b = int.TryParse(dr["CourseTHr"].ToString(), out t);
b = int.TryParse(dr["CoursePHr"].ToString(), out p);
b = int.TryParse(dr["CourseCredit"].ToString(), out c);
b = int.TryParse(dr["CourseCAMaxMarks"].ToString(), out ca);
b = int.TryParse(dr["CourseESEMaxMarks"].ToString(), out ese);
b = int.TryParse(dr["CourseTotalMaxMarks"].ToString(), out tmm);
b = int.TryParse(dr["CourseESEDuration"].ToString(), out dur);
SubjectList.Add(new clsCourseScheme
{
CourseID = int.Parse(dr["CourseID"].ToString()),
CourseCategoryID = int.Parse(dr["CourseCategoryID"].ToString()),
ProgramID = int.Parse(dr["programid"].ToString()),
SemesterID = int.Parse(dr["SemesterID"].ToString()),
CourseCode = dr["CourseCode"].ToString(),
CourseTitle = dr["CourseTitle"].ToString(),
CourseGroup = dr["coursegroup"].ToString(),
AcademicYear = dr["CourseAcademicYear"].ToString(),
CourseLHr = l,
CourseTHr = t,
CoursePHr = p,
CourseCredit = c,
CourseCAMaxMarks = ca,
CourseESEMaxMarks = ese,
CourseTotalMaxMarks = tmm,
CourseESEDuration = dur,
CourseType = dr["CourseType"].ToString(),
CourseTHPR = dr["courseTHPR"].ToString()
});
}
}
catch (Exception er)
{
SubjectList.Add(new clsCourseScheme {
ErrorMessage = er.Message.ToString()
});
}
return(SubjectList);
}
static void Main(string[] args)
{
ConnectionDB start = new ConnectionDB();
}
public scrLogin()
{
InitializeComponent();
util = new tbValidators();
conn = new ConnectionDB();
}
public CategoryRepository()
{
conexion = new ConnectionDB();
}
public FamilleDAO()
{
this.Connection = ConnectionDB.GetConnection();
}
//LISTE DES ASSUJETTIS
//====================
public List <Person> AssujettiNoteMig()
{
ConnectionDB con = new ConnectionDB(1);
DataTable dtt = con.Data_Source("SELECT * from " +
"( " +
"SELECT ROW_NUMBER() OVER (ORDER BY Nom_raison ASC) AS row_num,* from " +
"( " +
"SELECT DISTINCT tAssujetti.NumImpot " +
",tAssujetti.Nom_raison " +
",tAssujetti.numid_nat " +
",tAssujetti.Sigle " +
",tAssujetti.telephone " +
",tAssujetti.Email " +
",tAssujetti.typepersonne " +
",tAssujetti.AdressePost " +
",tAssujetti.NumImpotDGI " +
",tAssujetti.Numero " +
",tAvenue.Avenue " +
",tQuartier.Quartier " +
",tCommune.Commune " +
",tVille.Ville " +
",tProvince.Province " +
"FROM tAssujetti " +
"INNER JOIN tNotePercepteur ON tAssujetti.NumImpot=tNotePercepteur.NumImpot " +
"INNER JOIN tAntenne ON tNotePercepteur.IdAntenne=tAntenne.IdAntenne " +
"INNER JOIN tCentre ON tAntenne.IdCentre=tCentre.IdCentre " +
"INNER JOIN tAvenue ON tAssujetti.IdAvenue = tAvenue.IdAvenue " +
"INNER JOIN tQuartier ON tAvenue.IdQuartier = tQuartier.IdQuartier " +
"INNER JOIN tCommune ON dbo.tQuartier.IdCommune = tCommune.IdCommune " +
"INNER JOIN tVille ON dbo.tCommune.IdVille = tVille.IdVille " +
"INNER JOIN dbo.tProvince ON dbo.tVille.IdProv = tProvince.IdProv " +
"WHERE tCentre.IdCentre='DG' " +
"AND tNotePercepteur.IdNote NOT IN (SELECT IdNote FROM tNoteMigree) " +
") as Person " +
") as persComp " +
"where row_num>=0 and row_num<50 " +
"ORDER BY Nom_raison ",
"tAssujetti");
List <Person> lst = new List <Person>();
for (int i = 0; i < dtt.Rows.Count; i++)
{
lst.Add(new Person()
{
nbre = i + 1,
Id_person = dtt.Rows[i]["NumImpot"].ToString(),
name_company = dtt.Rows[i]["Nom_raison"].ToString(),
initials = dtt.Rows[i]["Sigle"].ToString(),
telephone = dtt.Rows[i]["telephone"].ToString(),
email = dtt.Rows[i]["Email"].ToString(),
p_o_box = dtt.Rows[i]["AdressePost"].ToString(),
tax_num_dgi = dtt.Rows[i]["NumImpotDGI"].ToString(),
avenue = dtt.Rows[i]["Avenue"].ToString(),
number = dtt.Rows[i]["Numero"].ToString(),
quarter = dtt.Rows[i]["Quartier"].ToString(),
commune = dtt.Rows[i]["Commune"].ToString(),
town_dist = dtt.Rows[i]["Ville"].ToString()
});
}
return(lst);
}
private void btnPrintBalanc_Click(object sender, EventArgs e)
{
//опросим dataGridView и узнаем что выбрали пользователи
List <ТаблицаДоговор> list = new List <ТаблицаДоговор>();
//заполгним первую строку
ТаблицаДоговор шапка = new ТаблицаДоговор();
//шапка.ПорядковыНомер = "№ п/п";
шапка.НомерУслуги = "№ усл в справ";
шапка.Наименование = "Наименование";
//шапка.Цена = "Цена, руб";
шапка.Количество = "Кол";
шапка.Цена = "Стоимость";
list.Add(шапка);
//подсчитаем сумму выполненных работ в тех листе
decimal суммаРабот = 0m;
foreach (DataGridViewRow row in this.dataGridView1.Rows)
{
if (Convert.ToBoolean(row.Cells["FlagSelect"].Value) != true)
{
//string iTest = row.Cells["Наименование"].Value.ToString().Trim();
ТаблицаДоговор item = new ТаблицаДоговор();
item.НомерУслуги = row.Cells["НомерУслуги"].Value.ToString();
item.Наименование = row.Cells["Наименование"].Value.ToString();
item.Количество = row.Cells["Количество"].Value.ToString();
item.Цена = row.Cells["Стоимость"].Value.ToString();
string стоим = row.Cells["Стоимость"].Value.ToString().Replace('р', ' ').Trim();
string стоимостString = стоим.Replace('.', ' ').Trim();
//суммаРабот = Math.Round(суммаРабот + Convert.ToDecimal(row.Cells["Стоимость"].Value), 2);
суммаРабот = Math.Round(суммаРабот + Convert.ToDecimal(стоимостString), 2);
list.Add(item);
//укажем что данная строка была отправлена на печать
row.Cells["FlagSelect"].Value = true;
//Установим флаг в true
row.Cells["FlagPrint"].Value = true;
}
}
//сформируем строку ИТОГО
ТаблицаДоговор sumТехЛист = new ТаблицаДоговор();
sumТехЛист.Наименование = "Итого: ";
sumТехЛист.Цена = суммаРабот.ToString("c");
//добавим в список list нижную строку с суммой ИТОГО
list.Add(sumТехЛист);
List <ТаблицаДоговор> iApple = list;
//string iTest = "Test";
//Распечатаем технический лист
string fName = "Технический лист " + this.ФИО_Льготника;
try
{
//Скопируем шаблон в папку Документы
FileInfo fn = new FileInfo(System.Windows.Forms.Application.StartupPath + @"\Шаблон\ТехЛист.doc");
fn.CopyTo(System.Windows.Forms.Application.StartupPath + @"\Документы\" + fName + ".doc", true);
}
catch
{
MessageBox.Show("Возможно у вас уже открыт договор с этим льготником. Закройте этот договор.");
}
string filName = System.Windows.Forms.Application.StartupPath + @"\Документы\" + fName + ".doc";
//Создаём новый Word.Application
Microsoft.Office.Interop.Word.Application app = new Microsoft.Office.Interop.Word.Application();
//Загружаем документ
Microsoft.Office.Interop.Word.Document doc = null;
object fileName = filName;
object falseValue = false;
object trueValue = true;
object missing = Type.Missing;
doc = app.Documents.Open(ref fileName, ref missing, ref trueValue,
ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);
object wdrepl = WdReplace.wdReplaceAll;
//object searchtxt = "GreetingLine";
object searchtxt = "Льготник";
object newtxt = (object)this.ФИО_Льготника;
//object frwd = true;
object frwd = false;
doc.Content.Find.Execute(ref searchtxt, ref missing, ref missing, ref missing, ref missing, ref missing, ref frwd, ref missing, ref missing, ref newtxt, ref wdrepl, ref missing, ref missing,
ref missing, ref missing);
//узнаем адрес
string queryАдрес = "select * from Льготник where id_льготник = " + this.Id_Льготник + " ";
DataRow rowЛьготник = ТаблицаБД.GetTable(queryАдрес, ConnectionDB.ConnectionString(), "Льготник").Rows[0];
//получим название района
string queryР = "select РайонОбласти from НаименованиеРайона where id_район in (select id_район from Льготник where id_льготник = " + this.Id_Льготник + " )";
DataTable tab = ДанныеПредставление.GetПредставление(queryР, "Льготник");
//переменная хранит название района
string названиеРайона = string.Empty;
if (tab.Rows.Count != 0)
{
//отобразим район который прописан в БД
названиеРайона = tab.Rows[0][0].ToString();
}
//получим название населённого пункта
string queryН = "select Наименование from НаселенныйПункт where id_насПункт in (select id_насПункт from Льготник where id_льготник = " + this.Id_Льготник + " )";
DataTable tabН = ДанныеПредставление.GetПредставление(queryН, "Льготник");
//переменная хранит название населённого пункта
string населённыйПункт = string.Empty;
if (tabН.Rows.Count != 0)
{
//отобразим населённый пункет в котором прописан текущий льготник
населённыйПункт = tabН.Rows[0][0].ToString();
}
//если населённый пункт = Саратов
if (Regex.IsMatch(населённыйПункт, "Саратов") == true)
{
названиеРайона = "";
}
//улица
string улица = rowЛьготник["улица"].ToString();
string улицаPrint = string.Empty;
if (улица != "")
{
улицаPrint = " ул " + улица;
}
//номер дома
string numHous = rowЛьготник["НомерДома"].ToString();
string numHousPrint = string.Empty;
if (numHous != "")
{
numHousPrint = " д " + numHous;
}
//номер корпуса
string numSubHous = rowЛьготник["корпус"].ToString();
string numSubHousPrint = string.Empty;
if (numSubHous != "")
{
numSubHousPrint = " корп. " + numSubHous;
}
//номер кв
string numEpartment = rowЛьготник["НомерКвартиры"].ToString();
string numEpartmentPrint = string.Empty;
if (numEpartment != "")
{
numEpartmentPrint = " кв. " + numEpartment;
}
//полный адрес
//string адрес = улица + " " + numHous + " " + numSubHous + " " + numEpartment;
string адрес = названиеРайона + " " + населённыйПункт + " " + улицаPrint + numHousPrint + numSubHousPrint + numEpartmentPrint;
object wdrepl2 = WdReplace.wdReplaceAll;
//object searchtxt = "GreetingLine";
object searchtxt2 = "местопроживания";
object newtxt2 = (object)адрес;
//object frwd = true;
object frwd2 = false;
doc.Content.Find.Execute(ref searchtxt2, ref missing, ref missing, ref missing, ref missing, ref missing, ref frwd2, ref missing, ref missing, ref newtxt2, ref wdrepl2, ref missing, ref missing,
ref missing, ref missing);
//вычислим количество полных лет
//получим дату рождения
DateTime др = Convert.ToDateTime(rowЛьготник["ДатаРождения"]);
//год рождения
int yearR = др.Year;
//int yearR = 1973;
//месяц рождения
int montchR = др.Month;
//int montchR = 09;
//текущую дату
DateTime data = DateTime.Today;
// текущий год
int yearT = data.Year;
//текущий месяц
int montchT = data.Month;
int rez = (montchT > montchR) ? (yearT - yearR) : (yearT == yearR) ? 0 : (yearT - yearR - 1);
string возрост = rez.ToString();
object wdrepl3 = WdReplace.wdReplaceAll;
//object searchtxt = "GreetingLine";
object searchtxt3 = "скольколет";
object newtxt3 = (object)возрост;
//object frwd = true;
object frwd3 = false;
doc.Content.Find.Execute(ref searchtxt3, ref missing, ref missing, ref missing, ref missing, ref missing, ref frwd3, ref missing, ref missing, ref newtxt3, ref wdrepl3, ref missing, ref missing,
ref missing, ref missing);
// List<ТаблицаДоговор> list = new List<ТаблицаДоговор>();
// //заполгним первую строку
// ТаблицаДоговор шапка = new ТаблицаДоговор();
// //шапка.ПорядковыНомер = "№ п/п";
// шапка.НомерУслуги = "№ усл в справ";
// шапка.Наименование = "Наименование";
// //шапка.Цена = "Цена, руб";
// шапка.Количество = "Кол";
// шапка.Цена = "Стоимость";
// list.Add(шапка);
// //сформируем таблицу из услуг указанных в gvВидУслуг
// //узнаем колько строк в гриде
// int iCountR = this.gvВидУслуг.Rows.Count;
// //откитнем последную пустую строку
// int iCountRow = iCountR - 1;
// int i = 0;
// //подсчитаем сумму выполненных работ в тех листе
// decimal суммаРабот = 0m;
// foreach (DataGridViewRow row in this.gvВидУслуг.Rows)
// {
// if (i != iCountRow)
// {
// ТаблицаДоговор item = new ТаблицаДоговор();
// item.НомерУслуги = row.Cells["НомерПоПеречню"].Value.ToString();
// item.Наименование = row.Cells["НаименованиеУслуги"].Value.ToString();
// item.Количество = row.Cells["'Кол-во'"].Value.ToString();
// item.Цена = row.Cells["Сумма"].Value.ToString();
// суммаРабот = Math.Round(суммаРабот + Convert.ToDecimal(row.Cells["Сумма"].Value), 2);
// list.Add(item);
// }
// i++;
// }
// ТаблицаДоговор sumТехЛист = new ТаблицаДоговор();
// sumТехЛист.Наименование = "Итого: ";
// sumТехЛист.Цена = суммаРабот.ToString("c");
// //добавим в список list нижную строку с суммой ИТОГО
// list.Add(sumТехЛист);
//Вставить таблицу
object bookNaziv = "таблица";
Range wrdRng = doc.Bookmarks.get_Item(ref bookNaziv).Range;
object behavior = Microsoft.Office.Interop.Word.WdDefaultTableBehavior.wdWord8TableBehavior;
object autobehavior = Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitWindow;
Microsoft.Office.Interop.Word.Table table = doc.Tables.Add(wrdRng, 1, 4, ref behavior, ref autobehavior);
table.Range.ParagraphFormat.SpaceAfter = 3;
table.Columns[1].Width = 150; // Было 50
table.Columns[2].Width = 240; // было 340
table.Columns[3].Width = 35;
table.Columns[4].Width = 60;
table.Borders.Enable = 1; // Рамка - сплошная линия
table.Range.Font.Name = "Times New Roman";
table.Range.Font.Size = 8;
//счётчик строк
int ic = 1;
//запишем данные в таблицу
foreach (ТаблицаДоговор item in list)
{
table.Cell(ic, 1).Range.Text = item.НомерУслуги;
table.Cell(ic, 2).Range.Text = item.Наименование;
table.Cell(ic, 3).Range.Text = item.Количество;
table.Cell(ic, 4).Range.Text = item.Цена;
//doc.Words.Count.ToString();
Object beforeRow1 = Type.Missing;
table.Rows.Add(ref beforeRow1);
ic++;
}
//удалим последную строку
table.Rows[ic].Delete();
//откроем получившейся документ
app.Visible = true;
}
public BuscarEmpleadosController(FRM_BuscarEmpleados frm_BuscarEmpleados, ConnectionDB connection)
{
this.frm_BuscarEmpleados = frm_BuscarEmpleados;
this.connection = connection;
AgregarEventos();
}
//muiBtnUndo_Click
private void muiBtnUndo_Click(object sender, RoutedEventArgs e)
{
if (current_folder_undo != "")
{
ModernDialog md = new ModernDialog();
md.Buttons = new Button[] { md.YesButton, md.CloseButton, };
md.Title = FindResource("notification").ToString();
md.Content = FindResource("are_you_sure").ToString();
md.YesButton.Content = FindResource("yes").ToString();
md.CloseButton.Content = FindResource("no").ToString();
md.YesButton.Focus();
bool result = md.ShowDialog().Value;
if (result == true)
{
//if database type is sqlite
if (StaticClass.GeneralClass.flag_database_type_general == false)
{
//close connect
ConnectionDB.CloseConnect();
if (System.IO.File.Exists(current_directory + @"\DBRES_Local\" + current_folder_undo + @"\CheckOut.db") == true)
{
System.IO.File.Copy(current_directory + @"\DBRES_Local\" + current_folder_undo + @"\CheckOut.db", current_directory + @"\Databases\CheckOut.db", true);
//restart app
System.Diagnostics.Process.Start(Application.ResourceAssembly.Location);
Application.Current.Shutdown();
}
//open connect
ConnectionDB.OpenConnect();
}
//if database type is sql server
else
{
//System.IO.Directory.CreateDirectory(current_directory + @"\DBRESSer_Local\" + __current_time);
string _restoreFile = current_directory + @"\DBRESSer_Local\" + current_folder_undo.Replace("/", ".").Replace(":", "..") + @"\CheckOut.bak";
if (System.IO.File.Exists(_restoreFile))
{
System.Diagnostics.Debug.WriteLine(_restoreFile);
SqlConnection sqlConnect = ConnectionDB.getSQLConnection();
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}' WITH NOFORMAT, NOINIT, NAME = N'data-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10", ConnectionDB.getSqlServerDataBaseName(), current_directory + @"\DBRESSer_Local\" + current_folder_undo.Replace("/", ".").Replace(":", "..") + @"\" + ConnectionDB.getSqlServerDataBaseName() + ".bak");
using (var command = new SqlCommand(query, sqlConnect))
{
//command.ExecuteNonQuery();
command.CommandText = "Use Master";
command.ExecuteNonQuery();
command.CommandText = "ALTER DATABASE " + ConnectionDB.getSqlServerDataBaseName() + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
command.ExecuteNonQuery();
command.CommandText = "RESTORE DATABASE " + ConnectionDB.getSqlServerDataBaseName() + " FROM DISK = '" + @_restoreFile + "' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10; ";
command.ExecuteNonQuery();
command.CommandText = "Use Master";
command.ExecuteNonQuery();
command.CommandText = "ALTER DATABASE " + ConnectionDB.getSqlServerDataBaseName() + " SET MULTI_USER;";
command.ExecuteNonQuery();
command.CommandText = "Use " + ConnectionDB.getSqlServerDataBaseName();
command.ExecuteNonQuery();
}
}
else if (System.IO.File.Exists(current_directory + @"\DBRESSer_Local\" + current_folder_undo + @"\CheckOut.sql") == true)
{
//get connection info
System.IO.StreamReader stream_reader = StaticClass.GeneralClass.DecryptFileGD(current_directory + @"\sqltype", StaticClass.GeneralClass.key_register_general);
stream_reader.ReadLine();
server = stream_reader.ReadLine().Split(':')[1].ToString();
id = stream_reader.ReadLine().Split(':')[1].ToString();
password = stream_reader.ReadLine().Split(':')[1].ToString();
stream_reader.Close();
//connection to server
string connection_string = "server = " + server + "; user id = " + id + "; password = "******"; integrated security = true";
SqlConnection sql_connection = new SqlConnection();
sql_connection.ConnectionString = connection_string;
sql_connection.Open();
//insert data
System.IO.StreamReader stream_reader_insert = StaticClass.GeneralClass.DecryptFileGD(current_directory + @"\DBRESSer_Local\" + current_folder_undo + @"\CheckOut.sql", StaticClass.GeneralClass.key_register_general);
string _line;
StringBuilder _strImport = new StringBuilder();
while ((_line = stream_reader_insert.ReadLine()) != null)
{
if (!string.IsNullOrEmpty(_line) && _line.Substring(0, 3) != "-- ")
{
if (_line.Contains("INSERT INTO"))
{
string _strTemp = _line.Substring(0, _line.IndexOf("("));
_strTemp = _strTemp.Substring("insert into ".Length).Trim().Replace("[", "").Replace("]", "");
_line = _line.Replace("')", @"\u0066").Replace("''", @"\u0055").Replace("','", @"\u0022").Replace("', '", @"\u0099").Replace(",'", @"\u0033").Replace(", '", @"\u0077").Replace("',", @"\u0044").Replace("' ,", @"\u0088").Replace("'", "''");
_line = _line.Replace(@"\u0066", "')").Replace(@"\u0055", "''").Replace(@"\u0022", "',N'").Replace(@"\u0099", "', N'").Replace(@"\u0033", ",N'").Replace(@"\u0077", ", N'").Replace(@"\u0044", "',").Replace(@"\u0088", "' ,");
_line = "if exists (select column_id from sys.columns where object_id = OBJECT_ID('" + _strTemp + "', 'U') and is_identity = 1) begin SET IDENTITY_INSERT " + _strTemp + " ON; " + _line + " SET IDENTITY_INSERT " + _strTemp + " OFF; end else " + _line;
}
_strImport.AppendLine(_line);
}
}
if (!string.IsNullOrEmpty(_strImport.ToString()))
{
SqlTransaction tr = null;
SqlCommand sql_command = null;
try
{
using (tr = sql_connection.BeginTransaction())
{
using (sql_command = sql_connection.CreateCommand())
{
sql_command.Transaction = tr;
sql_command.CommandText = _strImport.ToString();
sql_command.ExecuteNonQuery();
}
tr.Commit();
}
tr.Dispose();
sql_command.Dispose();
}
catch (SqlException ex)
{
if (tr != null)
{
try
{
tr.Rollback();
}
catch (ObjectDisposedException ex2)
{
}
finally
{
tr.Dispose();
}
}
}
}
sql_connection.Close();
//restart app
System.Diagnostics.Process.Start(Application.ResourceAssembly.Location);
Application.Current.Shutdown();
}
}
}
}
}
public ModificarEmpleadoController(FRM_ModificarEmpleado frm_ModificarEmpleado)
{
this.frm_ModificarEmpleado = frm_ModificarEmpleado;
db = new ConnectionDB();
AgregarEventos();
}
private void AddUser_Load(object sender, EventArgs e)
{
ConnectionDB.connectsql();
}
public SalidaProductosController(FRM_SalidaProductos frm_SalidaProductos)
{
this.frm_SalidaProductos = frm_SalidaProductos;
AgregarEventos();
db = new ConnectionDB();
}
public MarqueDAO()
{
this.Connection = ConnectionDB.GetConnection();
}
private string FuncUpgradeDatabase()
{
string _rs = "Success";
StringBuilder _strInsert = new StringBuilder();
_strInsert.Append("insert into [tb_Setting] values ('accentColor', '" + Properties.Settings.Default.accentColor.ToString() + "', '" + Properties.Settings.Default.accentColor.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('language', '" + Properties.Settings.Default.language.ToString() + "', '" + Properties.Settings.Default.language.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('outOfStock', '" + Properties.Settings.Default.outOfStock.ToString() + "', '" + Properties.Settings.Default.outOfStock.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('storeName', '" + Properties.Settings.Default.storeName.ToString() + "', '" + Properties.Settings.Default.storeName.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('storeAddress', '" + Properties.Settings.Default.storeAddress.ToString() + "', '" + Properties.Settings.Default.storeAddress.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('storePhone', '" + Properties.Settings.Default.storePhone.ToString() + "', '" + Properties.Settings.Default.storePhone.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('emailAcc', '" + Properties.Settings.Default.emailAcc.ToString() + "', '" + Properties.Settings.Default.emailAcc.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('emailPass', '" + Properties.Settings.Default.emailPass.ToString() + "', '" + Properties.Settings.Default.emailPass.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('showGuide', '" + Properties.Settings.Default.showGuide.ToString() + "', '" + Properties.Settings.Default.showGuide.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('unit', '" + Properties.Settings.Default.unit.ToString() + "', '" + Properties.Settings.Default.unit.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('unitVal', '" + Properties.Settings.Default.unitVal.ToString() + "', '" + Properties.Settings.Default.unitVal.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('decimalSeparator', '" + Properties.Settings.Default.decimalSeparator.ToString() + "', '" + Properties.Settings.Default.decimalSeparator.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('isSaveLogs', '" + Properties.Settings.Default.isSaveLogs.ToString() + "', '" + Properties.Settings.Default.isSaveLogs.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('fontSize', '" + Properties.Settings.Default.fontSize.ToString() + "', '" + Properties.Settings.Default.fontSize.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('orderDetailWidth', '" + Properties.Settings.Default.orderDetailWidth.ToString() + "', '" + Properties.Settings.Default.orderDetailWidth.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('CalStandard', '" + Properties.Settings.Default.CalStandard.ToString() + "', '" + Properties.Settings.Default.CalStandard.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('currentVersion', '2.1.6', '2.1.6');");
_strInsert.Append("insert into [tb_Setting] values ('appIsRestart', '" + Properties.Settings.Default.appIsRestart.ToString() + "', '" + Properties.Settings.Default.appIsRestart.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('typeBackup', '" + Properties.Settings.Default.typeBackup.ToString() + "', '" + Properties.Settings.Default.typeBackup.ToString() + "');");
_strInsert.Append("insert into [tb_Setting] values ('dateFormat', 'slashMdyyyy', 'slashMdyyyy');");
_strInsert.Append("insert into [tb_Setting] values ('timeFormat', 'hmmtt', 'hmmtt');");
_strInsert.Append("insert into [tb_Setting] values ('shopWebsite', '', '');");
_strInsert.Append("insert into [tb_Setting] values ('shopLogo', '', '');");
_strInsert.Append("insert into [tb_Setting] values ('receiptHeader', '', '');");
_strInsert.Append("insert into [tb_Setting] values ('receiptFooter', '', '');");
_strInsert.Append("insert into [tb_Setting] values ('isLoginToSale', '0', '0');");
if (StaticClass.GeneralClass.flag_database_type_general)
{
SqlConnection sqlConnect = ConnectionDB.getSQLConnection();
SqlTransaction tr = null;
SqlCommand cmd = null;
try
{
using (tr = sqlConnect.BeginTransaction())
{
using (cmd = sqlConnect.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "CREATE TABLE [tb_Currency]([SettingID] INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1), [Currency] nvarchar(200) NULL, [TaxRate] float NULL, [Active] int NULL, [Version] int NULL);";
cmd.ExecuteNonQuery();
cmd.CommandText = "if exists (select column_id from sys.columns where object_id = OBJECT_ID('tb_Currency', 'U') and is_identity = 1) begin SET IDENTITY_INSERT tb_Currency ON; insert into tb_Currency([SettingID],[Currency],[TaxRate],[Active],[Version]) select * from tb_Setting SET IDENTITY_INSERT tb_Currency OFF; end else insert into tb_Currency([SettingID], [Currency], [TaxRate], [Active], [Version]) select * from tb_Setting; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table [tb_Setting]";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_Setting]([SettingKey] nvarchar(255) NOT NULL PRIMARY KEY, [DefaultValue] nvarchar(255) NOT NULL, [SettingValue] nvarchar(255) NOT NULL); ";
cmd.ExecuteNonQuery();
cmd.CommandText = _strInsert.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_GiftCard]([GiftCardID] INTEGER PRIMARY KEY NOT NULL IDENTITY(1,1), [Barcode] nvarchar(255) NOT NULL UNIQUE, [Serial] nvarchar(255) NOT NULL, [CreateDate] INTEGER, [ExpirationDate] INTEGER, [CustomerIDUse] INTEGER, [Amount] decimal, [Balance] decimal, [DeliveredDate] INTEGER NOT NULL DEFAULT 0);";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_OrderPayment] ([OrderPaymentID] INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1), [OrderID] INTEGER, [PaymentID] INTEGER, [CardID] INTEGER, [Amount] decimal );";
cmd.ExecuteNonQuery();
}
tr.Commit();
}
tr.Dispose();
cmd.Dispose();
}
catch (SqlException ex)
{
if (tr != null)
{
try
{
tr.Rollback();
}
catch (ObjectDisposedException ex2)
{
_rs = ex2.Message;
}
finally
{
tr.Dispose();
}
}
_rs = ex.Message;
}
}
else //SQLite
{
SQLiteConnection sqliteConn = ConnectionDB.getSQLiteConnection();
SQLiteTransaction tr = null;
SQLiteCommand cmd = null;
try
{
using (tr = sqliteConn.BeginTransaction())
{
using (cmd = sqliteConn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "CREATE TABLE [tb_Currency]([SettingID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Currency] nvarchar(200) NULL, [TaxRate] float NULL, [Active] int NULL, [Version] int NULL);";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into tb_Currency select * from tb_Setting";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists [tb_Setting]";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_Setting]([SettingKey] nvarchar(255) NOT NULL PRIMARY KEY, [DefaultValue] TEXT NOT NULL, [SettingValue] TEXT NOT NULL); ";
cmd.ExecuteNonQuery();
cmd.CommandText = _strInsert.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_GiftCard]([GiftCardID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [Barcode] nvarchar(255) NOT NULL, [Serial] nvarchar(255) NOT NULL, [CreateDate] INTEGER, [ExpirationDate] INTEGER, [CustomerIDUse] INTEGER, [Amount] DOUBLE, [Balance] DOUBLE, [DeliveredDate] INTEGER NOT NULL DEFAULT 0, UNIQUE([Barcode] ASC));";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE [tb_OrderPayment] ([OrderPaymentID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [OrderID] INTEGER, [PaymentID] INTEGER, [CardID] INTEGER, [Amount] DOUBLE );";
cmd.ExecuteNonQuery();
}
tr.Commit();
}
tr.Dispose();
cmd.Dispose();
}
catch (SQLiteException ex)
{
if (tr != null)
{
try
{
tr.Rollback();
}
catch (ObjectDisposedException ex2)
{
_rs = ex2.Message;
}
finally
{
tr.Dispose();
}
}
_rs = ex.Message;
}
}
return(_rs);
}
private int saveTransaction()
{
conDB = new ConnectionDB();
string queryString = "INSERT INTO dbpackage.tblorderhistory (clientID, date, drNo, total, isPaid, isCancelled, isDeleted) VALUES " +
"(?,?,?,?,?,?,0)";
List <string> parameters = new List <string>();
parameters.Add(homeStockistMod != null ? homeStockistMod.ID :
(megaStockistMod != null ? megaStockistMod.ID :
(employeeStockistMod != null ? employeeStockistMod.ID :
(depotStockistMod != null ? depotStockistMod.ID :
(membersMod != null ? membersMod.ID : "0")))));
DateTime date = DateTime.Parse(dateDR.Text);
parameters.Add(date.Year + "-" + date.Month + "-" + date.Day);
parameters.Add(txtDR.Text);
parameters.Add(dblTotal.ToString());
if (chkIfPaid.IsChecked.Value)
{
parameters.Add("1");
orderHistory.ifPaid = "YES";
}
else
{
parameters.Add("0");
orderHistory.ifPaid = "NO";
}
if (chkCancelled.IsChecked.Value)
{
parameters.Add("1");
}
else
{
parameters.Add("0");
}
orderHistory.ClientID = homeStockistMod != null ? homeStockistMod.ID :
(megaStockistMod != null ? megaStockistMod.ID :
(employeeStockistMod != null ? employeeStockistMod.ID :
(depotStockistMod != null ? depotStockistMod.ID :
(membersMod != null ? membersMod.ID : "0"))));
orderHistory.DRDate = dateDR.Text;
orderHistory.DRNumber = txtDR.Text;
orderHistory.Total = dblTotal.ToString();
conDB.AddRecordToDatabase(queryString, parameters);
MySqlDataReader reader = conDB.getSelectConnection("SELECT ID from dbpackage.tblorderhistory order by ID desc limit 1", null);
int x = 0;
while (reader.Read())
{
x = Convert.ToInt32(reader["ID"].ToString());
}
orderHistory.ID = x.ToString();
conDB.closeConnection();
return(x);
}
public AtividadeBusiness()
{
_connectionDB = new ConnectionDB();
}
public DataTable CargarMateriasporCarreraUsuario(string carnet, int idCarrera)
{
SqlDataAdapter da = new SqlDataAdapter("UDB_CargarMateriasPorCarreraUsuario", ConnectionDB.GetConnectionString());
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@Carnet", SqlDbType.VarChar).Value = carnet;
da.SelectCommand.Parameters.Add("@idCarrera", SqlDbType.VarChar).Value = idCarrera;
DataTable Data = new DataTable();
da.Fill(Data);
return(Data);
}