public void Sincronizar_PagosSAP_en_MySQL() { String oSql; MySqlCommand QryCmd; //MySqlDataReader Reader; String Autorizacion; String Rut; String RutAux; String Tickets; String sErr; Int32 nErr; try { conexionMysql = new ConexionMysql(); conexionMysql.oLog = FLog; // Solo Deuda vencida => Bloquear oSql = "Select DISTINCT spr.cardcode SocPrin, s.CardCode, s.LicTradNum, s.GroupCode, spr.saldo, s.frozenFor, s.U_Tickets " + " from OCRD s, " + " (select i.CardCode, i.DocTotal-i.PaidToDate as Saldo, i.DocDueDate, " + " c.U_cod , c.U_cod1, c.U_co2 , c.U_cod3, c.U_cod4 , c.U_cod5, " + " c.U_cod6, c.U_cod7, c.U_cod8, c.U_cod9, c.U_cod10, c.U_cod11 " + " from OINV i inner join OCRD c on i.CardCode = c.CardCode " + " where i.DocTotal > i.PaidToDate " + // Con Deuda " and i.DocDueDate < GETDATE() " + // Deuda Vencida " and c.GroupCode = 100 " + " and c.CardType = 'C') spr " + " where s.CardCode = spr.CardCode " + " or s.CardCode = spr.U_cod " + " or s.CardCode = spr.U_cod1 " + " or s.CardCode = spr.U_co2 " + " or s.CardCode = spr.U_cod3 " + " or s.CardCode = spr.U_cod4 " + " or s.CardCode = spr.U_cod5 " + " or s.CardCode = spr.U_cod6 " + " or s.CardCode = spr.U_cod7 " + " or s.CardCode = spr.U_cod8 " + " or s.CardCode = spr.U_cod9 " + " or s.CardCode = spr.U_cod10 " + " or s.CardCode = spr.U_cod11 "; RutAux = ""; FRecordSet.DoQuery(oSql); while (!FRecordSet.EoF) { Rut = (System.String)FRecordSet.Fields.Item("LicTradNum").Value; Rut = Rut.Substring(0, Rut.Length - 2); if (Rut == RutAux) { FRecordSet.MoveNext(); continue; } else { RutAux = Rut; } try { if (conexionMysql.AbrirConexion() == true) { // Sobre MySQL oSql = "SELECT ACCESO_AUTORIZACION, ACCESO_CUPOS_VISITAS FROM acceso_llacolen " + " WHERE ACCESO_RUT = '" + Rut + "'"; QryCmd = new MySqlCommand(oSql, conexionMysql.conexionMysql); MySqlDataReader Reader = QryCmd.ExecuteReader(); if (Reader.HasRows) { while (Reader.Read()) { if ((Reader.GetString(0).Trim() != "N") || (Reader.GetInt32(1) != 0)) { oSql = "UPDATE acceso_llacolen " + " SET ACCESO_AUTORIZACION = 'N'" + " ,ACCESO_CUPOS_VISITAS = 0 " + " WHERE ACCESO_RUT = '" + Rut + "'"; MySqlConnection conn = new MySqlConnection(getLink()); conn.Open(); MySqlCommand QryCmd2 = new MySqlCommand(oSql, conn); if (QryCmd2.ExecuteNonQuery() > 0) { oLog.LogMsg("Socio bloqueado en MySQL, Rut: " + Rut, "A", "D"); } else { oLog.LogMsg("Fallo bloqueo de cliente sobre MySQL, Rut: " + Rut + " ************ ERROR ", "A", "D"); } conn.Close(); } } Reader.Close(); } conexionMysql.CerrarConexion(); // Sobre SBO if (((System.String)FRecordSet.Fields.Item("frozenFor").Value) != "Y" || ((System.Int32)FRecordSet.Fields.Item("U_Tickets").Value != 0)) { if (oBP.GetByKey((System.String)FRecordSet.Fields.Item("CardCode").Value)) { oBP.Frozen = SAPbobsCOM.BoYesNoEnum.tYES; oBP.Valid = SAPbobsCOM.BoYesNoEnum.tNO; oBP.UserFields.Fields.Item("U_Tickets").Value = 0; nErr = oBP.Update(); if (nErr != 0) { sErr = oCompany.GetLastErrorDescription(); oLog.LogMsg("Error al bloquear por pago en SBO, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value + " - " + sErr, "A", "D"); } else { oLog.LogMsg("Bloqueo por pago en SBO, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value, "A", "D"); } } } FRecordSet.MoveNext(); } } catch (MySqlException ex) { oLog.LogMsg("MYSQLException " + ex.Message, "A", "D"); conexionMysql.CerrarConexion(); oLog.LogMsg("Cerrando Conexion EXCEPTION", "F", "E"); } } // Con Pago reciente => Desbloquear oSql = "Select DISTINCT spr.cardcode SocPrin, s.CardCode, s.LicTradNum, s.GroupCode, s.frozenFor, s.U_Ausente, spr.saldo, spr.DocDueDate " + " ,isnull((select GroupCode from OCRD where CardCode = s.U_cod1), -1) Grp102 " + " from OCRD s, " + " (select i.CardCode, i.DocTotal-i.PaidToDate as Saldo, i.DocDueDate, " + " c.U_cod , c.U_cod1, c.U_co2 , c.U_cod3, c.U_cod4 , c.U_cod5, " + " c.U_cod6, c.U_cod7, c.U_cod8, c.U_cod9, c.U_cod10, c.U_cod11 " + " from OINV i inner join OCRD c on i.CardCode = c.CardCode " + " where i.DocTotal = i.PaidToDate " + // Sin Deuda " and i.DocDueDate <= GETDATE() " + // Deuda Vencida " and i.DocDueDate > IsNull(U_SincPago, '1900-01-01') " + // Vencimiento > fecha sincroniz. => no actulizado " and c.GroupCode = 100 " + " and c.CardType = 'C') spr " + " where s.CardCode = spr.CardCode " + " or s.CardCode = spr.U_cod " + " or s.CardCode = spr.U_cod1 " + " or s.CardCode = spr.U_co2 " + " or s.CardCode = spr.U_cod3 " + " or s.CardCode = spr.U_cod4 " + " or s.CardCode = spr.U_cod5 " + " or s.CardCode = spr.U_cod6 " + " or s.CardCode = spr.U_cod7 " + " or s.CardCode = spr.U_cod8 " + " or s.CardCode = spr.U_cod9 " + " or s.CardCode = spr.U_cod10 " + " or s.CardCode = spr.U_cod11 "; RutAux = ""; FRecordSet.DoQuery(oSql); while (!FRecordSet.EoF) { Autorizacion = "Y"; if ((System.String)FRecordSet.Fields.Item("U_Ausente").Value == "2") // U_Ausente = 2 => Si, 1 => No { Autorizacion = "N"; } if ((System.String)FRecordSet.Fields.Item("FrozenFor").Value == "Y") { Autorizacion = "N"; } Rut = (System.String)FRecordSet.Fields.Item("LicTradNum").Value; Rut = Rut.Substring(0, Rut.Length - 2); Tickets = "0"; if (((System.Int32)FRecordSet.Fields.Item("GroupCode").Value == 100) && ((System.Int32)FRecordSet.Fields.Item("Grp102").Value == -1)) { Tickets = "20"; } else if (((System.Int32)FRecordSet.Fields.Item("GroupCode").Value == 100) || ((System.Int32)FRecordSet.Fields.Item("GroupCode").Value == 102)) { Tickets = "10"; } //MYSQL if (conexionMysql.AbrirConexion() == true) { oSql = "UPDATE acceso_llacolen " + " SET ACCESO_CUPOS_VISITAS = " + Tickets + " ,ACCESO_AUTORIZACION = '" + Autorizacion + "'" + " WHERE ACCESO_RUT = '" + Rut + "'"; QryCmd = new MySqlCommand(oSql, conexionMysql.conexionMysql); //QryCmd = new MySqlCommand(oSql, FBDBatchMySQL); if (QryCmd.ExecuteNonQuery() > 0) { oLog.LogMsg("Socio actualizado, " + Tickets + " cupos asignados, en MySQL, RUT: " + Rut, "A", "D"); } else { oLog.LogMsg("Fallo Actualizacion de socio en MySQL: " + Rut + " ************ ERROR ", "A", "D"); } conexionMysql.CerrarConexion(); if (oBP.GetByKey((System.String)FRecordSet.Fields.Item("CardCode").Value)) { oBP.Frozen = SAPbobsCOM.BoYesNoEnum.tNO; oBP.Valid = SAPbobsCOM.BoYesNoEnum.tYES; oBP.UserFields.Fields.Item("U_Tickets").Value = Int32.Parse(Tickets); oBP.UserFields.Fields.Item("U_SincPago").Value = (System.DateTime)FRecordSet.Fields.Item("DocDueDate").Value; nErr = oBP.Update(); if (nErr != 0) { sErr = oCompany.GetLastErrorDescription(); oLog.LogMsg("Error al actualizar por pago en SBO, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value + " - " + sErr, "A", "D"); } else { oLog.LogMsg("Actualización por pago en SBO, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value, "A", "D"); } } } FRecordSet.MoveNext(); } } catch (Exception e) { oLog.LogMsg("Error al sincronizar SBO : " + e.Message, "A", "E"); } }
public void ActualizarCuposBP_SBO() { String oSql; //MySqlDataReader Reader; MySqlCommand Qry; String sErr; Int32 nErr; try { conexionMysql = new ConexionMysql(); conexionMysql.oLog = FLog; oSql = "SELECT ACCESO_RUT, ACCESO_AUTORIZACION, ACCESO_CUPOS_VISITAS FROM acceso_llacolen"; try { if (conexionMysql.AbrirConexion() == true) { Qry = new MySqlCommand(oSql, conexionMysql.conexionMysql); MySqlDataReader Reader = Qry.ExecuteReader(); while (Reader.Read()) { int cupos = Reader.GetInt32(2); oSql = "Select CardCode " + " From OCRD " + " Where (GroupCode = 100 or GroupCode = 102) " + " and substring(LicTradNum,1,len(LicTradNum)-2)= '" + Reader.GetString(0) + "' " + " and U_Tickets <> " + Reader.GetInt32(2).ToString(); FRecordSet.DoQuery(oSql); if (!FRecordSet.EoF) { if (oBP.GetByKey((System.String)FRecordSet.Fields.Item("CardCode").Value)) { oBP.UserFields.Fields.Item("U_Tickets").Value = cupos; nErr = oBP.Update(); if (nErr != 0) { sErr = oCompany.GetLastErrorDescription(); oLog.LogMsg("Error al actualizar cupos desde MySQL a SAP, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value + " :" + sErr, "A", "D"); } else { oLog.LogMsg("Actualización de CUPOS en SBO, socio: " + (System.String)FRecordSet.Fields.Item("CardCode").Value, "A", "D"); } } FRecordSet.MoveNext(); } } Reader.Close(); conexionMysql.CerrarConexion(); } } catch (MySqlException ex) { oLog.LogMsg("MYSQLException " + ex.Message, "A", "D"); conexionMysql.CerrarConexion(); oLog.LogMsg("Cerrando Conexion EXCEPTION", "F", "E"); } } catch (Exception e) { oLog.LogMsg("Error al actualizar cupos en SBO, problema con MySQL: " + e.Message, "A", "E"); } }
public void ActualizarSocios_MySQL() { String oSql; MySqlCommand QryCmd; //MySqlDataReader Reader; String Autorizacion; String Rut; Int32 TipoSocio; Int32 Tickets; try { conexionMysql = new ConexionMysql(); conexionMysql.oLog = FLog; oSql = "Select CardCode, FrozenFor, isnull(U_Tickets,0) U_Tickets, LicTradNum, U_Ausente, GroupCode " + " from OCRD " + " where CardType = 'C' and GroupCode <> '108'"; // 108 - grupo accionistas FRecordSet.DoQuery(oSql); while (!FRecordSet.EoF) { Autorizacion = "Y"; if ((System.String)FRecordSet.Fields.Item("U_Ausente").Value == "2") // U_Ausente = 2 => Si, 1 => No { Autorizacion = "N"; } if ((System.String)FRecordSet.Fields.Item("FrozenFor").Value == "Y") { Autorizacion = "N"; } Rut = (System.String)FRecordSet.Fields.Item("LicTradNum").Value; Rut = Rut.Substring(0, Rut.Length - 2); TipoSocio = (System.Int32)FRecordSet.Fields.Item("GroupCode").Value; Tickets = (System.Int32)FRecordSet.Fields.Item("U_Tickets").Value; try { if (conexionMysql.AbrirConexion() == true) { oSql = "SELECT ACCESO_AUTORIZACION, ACCESO_CUPOS_VISITAS FROM acceso_llacolen " + " WHERE ACCESO_RUT = '" + Rut + "'"; QryCmd = new MySqlCommand(oSql, conexionMysql.conexionMysql); MySqlDataReader Reader = QryCmd.ExecuteReader(); if (Reader.HasRows) { while (Reader.Read()) { if (Autorizacion != Reader.GetString(0).Trim()) { oSql = "UPDATE acceso_llacolen " + " SET ACCESO_AUTORIZACION = '" + Autorizacion + "'" + " WHERE ACCESO_RUT = '" + Rut + "'"; MySqlConnection conn = new MySqlConnection(getLink()); conn.Open(); MySqlCommand QryCmd2 = new MySqlCommand(oSql, conn); if (QryCmd2.ExecuteNonQuery() > 0) { oLog.LogMsg("Actualización de AUTORIZACION sobre MySQL: " + Rut, "A", "D"); } conn.Close(); } if ((Tickets > 0) && (Reader.GetInt32(1) == 0)) { oSql = "UPDATE acceso_llacolen " + " SET ACCESO_CUPOS_VISITAS = " + Tickets.ToString() + " WHERE ACCESO_RUT = '" + Rut + "'"; MySqlConnection conn = new MySqlConnection(getLink()); conn.Open(); MySqlCommand QryCmd2 = new MySqlCommand(oSql, conn); if (QryCmd2.ExecuteNonQuery() > 0) { oLog.LogMsg("Actualización de TICKETS sobre MySQL: " + Rut, "A", "D"); } conn.Close(); } } Reader.Close(); } else { //SON CODIGOS CORRESPONDIENTES AL SOCIO TITULAR 100 Y CONYUJE 102, LLEVAN CUPOS if ((TipoSocio == 100) || (TipoSocio == 102)) { oSql = "INSERT INTO acceso_llacolen " + " ( ACCESO_RUT, ACCESO_AUTORIZACION, ACCESO_CUPOS_VISITAS) " + " VALUES (" + "'" + Rut + "'," + "'" + Autorizacion + "'," + Tickets.ToString() + ")"; } else { oSql = "INSERT INTO acceso_llacolen " + " ( ACCESO_RUT, ACCESO_AUTORIZACION, ACCESO_CUPOS_VISITAS) " + " VALUES (" + "'" + Rut + "'," + "'" + Autorizacion + "'," + "0" + ")"; } MySqlConnection conn = new MySqlConnection(getLink()); conn.Open(); MySqlCommand QryCmd2 = new MySqlCommand(oSql, conn); QryCmd2.ExecuteNonQuery(); oLog.LogMsg("Nuevo socio Ingresado a MySQL: " + (System.String)FRecordSet.Fields.Item("CardCode").Value, "A", "D"); conn.Close(); } FRecordSet.MoveNext(); conexionMysql.CerrarConexion(); } } catch (MySqlException ex) { oLog.LogMsg("MYSQLException " + ex.Message, "A", "D"); conexionMysql.CerrarConexion(); oLog.LogMsg("Cerrando Conexion.", "F", "E"); } } } catch (Exception e) { oLog.LogMsg("Error al actualizar socios en MySql : " + e.Message, "A", "E"); } }
//Methods public void Doit(ref int nErr, ref string sErr) { if (oCompany == null) { oCompany = new SAPbobsCOM.Company(); } if (!oCompany.Connected) { ConnectSBO(ref nErr, ref sErr); if (nErr != 0) { oLog.LogMsg(sErr, "F", "E"); return; } else { oLog.LogMsg("Connected to SAP", "F", "D"); } } //if (Llacolen_SBOService.Properties.Settings.Default.BatchMySQLType == "SQLServer") //{ // if (BDBatchSQLServer == null) // BDBatchSQLServer = new System.Data.SqlClient.SqlConnection(); // if ((BDBatchSQLServer.State == System.Data.ConnectionState.Closed) | (BDBatchSQLServer.State == System.Data.ConnectionState.Broken)) // { // ConnectBDBatchSQLServer(ref nErr, ref sErr); // if (nErr != 0) // { // oLog.LogMsg(sErr, "F", "E"); // return; // } // } //} //else //{ //if (BDBatchMySQL == null) // BDBatchMySQL = new MySqlConnection(); //if ((BDBatchMySQL.State == System.Data.ConnectionState.Closed) | (BDBatchMySQL.State == System.Data.ConnectionState.Broken)) //{ // ConnectBDBatchMySQL(ref nErr, ref sErr); // if (nErr != 0) // { // oLog.LogMsg(sErr, "F", "E"); // return; // } // else // { // oLog.LogMsg("Connected to MYSQL", "F", "D"); // } //} //} oRecordSet = (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset); LlacolenDocs SincDocs = new LlacolenDocs(); SincDocs.oCompany = oCompany; // SincDocs.oBDBatchMySQL = conexionMysql; SincDocs.oLog = FLog; SincDocs.oRecordSet = oRecordSet; SincDocs.InitVars(); conexionMysql = new ConexionMysql(); conexionMysql.oLog = FLog; oLog.LogMsg("------------------PROCESO 1-----------------------", "F", "E"); oLog.LogMsg("Bloqueo/desbloqueo de socios en base a pagos, iniciado.", "F", "E"); SincDocs.Sincronizar_PagosSAP_en_MySQL(); oLog.LogMsg("------------------PROCESO 2-----------------------", "F", "E"); oLog.LogMsg("Actualizar tickets e ingresar socios nuevos a punto, iniciado.", "F", "E"); SincDocs.ActualizarSocios_MySQL(); oLog.LogMsg("------------------PROCESO 3-----------------------", "F", "E"); oLog.LogMsg("Actualizar cupos desde punto, iniciado.", "F", "E"); SincDocs.ActualizarCuposBP_SBO(); oLog.LogMsg("Finaliza actualización.", "F", "E"); }