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");
}