public void update_documento_Transac_Mov(Ent_Ora_Transac param, ref string error)
{
//string sqlquery = "select rtl_loc_id,business_date,wkstn_id,trans_seq,TOTAL,fiscal_number from trn_trans where trans_typcode='RETAIL_SALE' AND trans_statcode='COMPLETE' AND TOTAL>0 AND RECORD_STATE IS NULL and business_date>='" + fecha + "'";
//string sqlquery = "update trn_trans set RECORD_STATE='X' where trans_typcode='RETAIL_SALE' AND trans_statcode='COMPLETE' AND TOTAL>0 AND RECORD_STATE IS NULL AND rtl_loc_id=" + param.RTL_LOC_ID + " AND wkstn_id=" + param.WKSTN_ID + " AND trans_seq=" + param.TRANS_SEQ + " AND fiscal_number='" + param.FISCAL_NUMBER + "'";
string sqlquery = "";
try
{
switch (param.DOCUMENT_TYPCODE)
{
/*venta*/
case "RETAIL_SALE":
/*X ES PARA EL TEMA DE LAS TICKET REGALO Y A ES PARA ENVIO DE POSLOG*/
sqlquery = "update trn_trans set RECORD_STATE='A' where trans_typcode='RETAIL_SALE' AND trans_statcode='COMPLETE' and (RECORD_STATE='X' or (RECORD_STATE is null and total<0) OR (RECORD_STATE is null and SUBSTR(FISCAL_NUMBER,0,1) NOT IN('B','F') )) AND rtl_loc_id=" + param.RTL_LOC_ID + " AND wkstn_id=" + param.WKSTN_ID + " AND trans_seq=" + param.TRANS_SEQ + " AND fiscal_number='" + param.NUMDOC + "'";
break;
/*envio de guia*/
/*recibo de guias*/
case "SHIPPING":
case "RECEIVING":
sqlquery = "update inv_invctl_trans set RECORD_STATE='X' where document_typcode='" + param.DOCUMENT_TYPCODE + "' AND RECORD_STATE IS NULL AND rtl_loc_id=" + param.RTL_LOC_ID + " AND wkstn_id=" + param.WKSTN_ID + " AND trans_seq=" + param.TRANS_SEQ;
break;
//case "RECEIVING":
// sqlquery = "update trn_trans set RECORD_STATE='X' where trans_typcode='RETAIL_SALE' AND trans_statcode='COMPLETE' AND TOTAL>0 AND RECORD_STATE IS NULL AND rtl_loc_id=" + param.RTL_LOC_ID + " AND wkstn_id=" + param.WKSTN_ID + " AND trans_seq=" + param.TRANS_SEQ + " AND fiscal_number='" + param.FISCAL_NUMBER + "'";
// break;
}
//object results = new object[1];
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
db.ExecuteNonQuery(dbCommandWrapper);
}
catch (Exception exc)
{
error = exc.Message;
//throw;
}
}
public Boolean inserta_tabla_temp_poslog(Ent_Ora_Transac param, ref string error)
{
Boolean insert = false;
decimal _n = Convert.ToDecimal(param.TOTAL, new NumberFormatInfo()
{
NumberDecimalSeparator = "."
});
//string sqlquery = "INSERT INTO " + Ent_Acceso_BD.nom_tabla + "(RTL_LOC_ID,BUSINESS_DATE,WKSTN_ID,TRANS_SEQ,TOTAL,FISCAL_NUMBER)" +
// " VALUES(" + param.RTL_LOC_ID + ",'" + param.BUSINESS_DATE + "'," + param.WKSTN_ID + "," + param.TRANS_SEQ + "," + Convert.ToDecimal(param.TOTAL, new NumberFormatInfo() { NumberDecimalSeparator = "." }) + ",'" + param.FISCAL_NUMBER + "')";
//string sqlquery = "INSERT INTO " + Ent_Acceso_BD.nom_tabla_poslog + "(RTL_LOC_ID,WKSTN_ID,TRANS_SEQ,BUSINESS_DATE,NUMDOC,TOTAL,DOCUMENT_TYPCODE) VALUES(:RTL_LOC_ID,:WKSTN_ID,:TRANS_SEQ,:BUSINESS_DATE,:NUMDOC,:TOTAL,:DOCUMENT_TYPCOE)";
string sqlquery = "INSERT INTO " + Ent_Acceso_BD.nom_tabla_poslog + "(RTL_LOC_ID,WKSTN_ID,TRANS_SEQ,BUSINESS_DATE,NUMDOC,TOTAL,DOCUMENT_TYPCODE) VALUES(:RTL_LOC_ID,:WKSTN_ID,:TRANS_SEQ,:BUSINESS_DATE,:NUMDOC,:TOTAL,:DOCUMENT_TYPCODE)";
try
{
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
db.AddInParameter(dbCommandWrapper, "RTL_LOC_ID", DbType.Decimal, param.RTL_LOC_ID);
db.AddInParameter(dbCommandWrapper, "WKSTN_ID", DbType.Decimal, param.WKSTN_ID);
db.AddInParameter(dbCommandWrapper, "TRANS_SEQ", DbType.Decimal, param.TRANS_SEQ);
db.AddInParameter(dbCommandWrapper, "BUSINESS_DATE", DbType.DateTime, param.BUSINESS_DATE);
db.AddInParameter(dbCommandWrapper, "NUMDOC", DbType.String, param.NUMDOC);
db.AddInParameter(dbCommandWrapper, "TOTAL", DbType.Decimal, param.TOTAL);
db.AddInParameter(dbCommandWrapper, "DOCUMENT_TYPCODE", DbType.String, param.DOCUMENT_TYPCODE);
//dbCommandWrapper.Parameters.Add("RTL_LOC_ID",DbType.)
//dbCommandWrapper.Parameters.Add(new OracleParameter(":FName", TextBox1.Text));
db.ExecuteNonQuery(dbCommandWrapper);
insert = true;
}
catch (Exception exc)
{
error = exc.Message + " ==>inserta_tabla_temp ==QUERY==> " + sqlquery;
}
return(insert);
}
public Boolean existe_data_temp_transac(Ent_Ora_Transac param, ref string error)
{
Boolean existe = false;
string sqlquery = "SELECT count(*) as existe FROM " + Ent_Acceso_BD.nom_tabla_poslog + " WHERE RTL_LOC_ID=" + param.RTL_LOC_ID + " and WKSTN_ID=" + param.WKSTN_ID + " AND TRANS_SEQ=" + param.TRANS_SEQ + " AND NUMDOC='" + param.NUMDOC + "'";
DataTable dt_existe = null;
try
{
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
dt_existe = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
if (dt_existe.Rows.Count > 0)
{
existe = Convert.ToInt32(dt_existe.Rows[0]["existe"]) >= 1 ? true : false;
}
}
catch (Exception exc)
{
//throw;
error = exc.Message + " ==>existe_tabla_temp";
}
return(existe);
}
public string ejecuta_envio_poslog()
{
string _fecha_ini = DateTime.Today.AddDays(-90).ToString("dd/MM/yyyy");
string _fecha_fin = DateTime.Today.ToString("dd/MM/yyyy");;
string error = "";
try
{
string nom_pc = Environment.MachineName;//"TIENDA-933-1"
string caja = nom_pc.Substring(nom_pc.Length - 1, 1);
string _server = nom_pc;
if (caja != "1")
{
_server = nom_pc.Substring(0, nom_pc.Length - 1) + "1";
}
_tienda = nom_pc.Substring(nom_pc.IndexOf('-') + 1, nom_pc.Length - (nom_pc.IndexOf('-') + 1));
_tienda = _tienda.Substring(0, _tienda.Length - 2);
if (_tienda.Length == 3)
{
_tienda = "50" + _tienda;
}
Dat_Ora_Data data_ora = new Dat_Ora_Data();
#region <CONEXIONES DE ORACLE>
Ent_Conexion_Ora_Xstore con_ora = data_ora.ws_conexion_xstore(ref error);
if (error.Length > 0)
{
return(error);
}
if (con_ora == null)
{
return("");
}
//_server = "";
// "172.30.41.10" TDA 143
// 172.30.39.10 TDA 141
Ent_Acceso_BD.server = _server; //"172.16.100.200";// _server;// "172.16.100.189";// _server; //con_ora.server;// ConfigurationManager.AppSettings["server"].ToString();
Ent_Acceso_BD.user = con_ora.usuario; // ConfigurationManager.AppSettings["usuario"].ToString();
Ent_Acceso_BD.password = con_ora.password; // ConfigurationManager.AppSettings["password"].ToString();
Ent_Acceso_BD.port = con_ora.port; // Convert.ToInt32(ConfigurationManager.AppSettings["port"].ToString());
Ent_Acceso_BD.sid = con_ora.sid; // ConfigurationManager.AppSettings["sid"].ToString();
Ent_Acceso_BD.nom_tabla = ConfigurationManager.AppSettings["tempo"].ToString();
Ent_Acceso_BD.nom_tabla_poslog = "TMP_BATA_TRANSAC";
#endregion
#region <ORACLE CREACION Y EXISTENCIA>
Boolean existe = data_ora.existe_tabla_poslog(ref error);
if (error.Length > 0)
{
return(error);
}
if (!existe)
{
error = data_ora.crear_table_poslog();
}
if (error.Length > 0)
{
return(error);
}
#endregion
#region <ENVIO DE DATOS DE VENTA,GUIAS AL TEMPORAL>
// _tienda = "50704";
DataTable dtventa_ora = data_ora.get_documento_TRN_INV_TRANS_POSLOG(_tienda, _fecha_ini, _fecha_fin, ref error);
if (error.Length > 0)
{
return(error);
}
if (dtventa_ora != null)
{
if (dtventa_ora.Rows.Count > 0)
{
foreach (DataRow fila in dtventa_ora.Rows)
{
Ent_Ora_Transac transac = new Ent_Ora_Transac();
transac.RTL_LOC_ID = Convert.ToDecimal(fila["RTL_LOC_ID"]);
transac.WKSTN_ID = Convert.ToInt32(fila["WKSTN_ID"]);
transac.TRANS_SEQ = Convert.ToDecimal(fila["TRANS_SEQ"]);
transac.BUSINESS_DATE = Convert.ToDateTime(fila["BUSINESS_DATE"]);
transac.NUMDOC = fila["NUMDOC"].ToString();
transac.TOTAL = Convert.ToDecimal(fila["TOTAL"]);
transac.DOCUMENT_TYPCODE = fila["DOCUMENT_TYPCODE"].ToString();
decimal _numero_region = Convert.ToDecimal(fila["TOTAL"], new NumberFormatInfo()
{
NumberDecimalSeparator = "."
});
transac.TOTAL = _numero_region;
/*VERIFICAR QUE NO EXISTA EL DOCUMENTO*/
Boolean existe_tmp = data_ora.existe_data_temp_transac(transac, ref error);
if (error.Length > 0)
{
return(error);
}
if (!existe_tmp)
{
Boolean insert = data_ora.inserta_tabla_temp_poslog(transac, ref error);
if (error.Length > 0)
{
return(error);
}
/*si el insert es correcto entonces hacemos un update*/
if (insert)
{
data_ora.update_documento_Transac_Mov(transac, ref error);
if (error.Length > 0)
{
return(error);
}
}
}
else
{
/*realizar el update en la tabla principal*/
data_ora.update_documento_Transac_Mov(transac, ref error);
if (error.Length > 0)
{
return(error);
}
}
}
}
}
#endregion
#region <ENVIO LOS DATOS AL SERVER AL POSLOG POR WEBSERVICE>
DataTable dt_envio = data_ora.select_tmp_poslog_ora();/*seleccionamos datos del oracle*/
if (dt_envio != null)
{
if (dt_envio.Rows.Count > 0)
{
foreach (DataRow fila in dt_envio.Rows)
{
Ent_PosLog_Tda param = new Ent_PosLog_Tda();
param.rtl_loc_id = Convert.ToInt32(fila["RTL_LOC_ID"]);
param.wkstn_id = Convert.ToInt32(fila["WKSTN_ID"]);
param.trans_seq = Convert.ToDecimal(fila["TRANS_SEQ"]);
param.business_date = Convert.ToDateTime(fila["BUSINESS_DATE"]);
param.numdoc = fila["NUMDOC"].ToString();
param.total = Convert.ToDecimal(fila["TOTAL"]);
param.document_typcode = fila["DOCUMENT_TYPCODE"].ToString();
param.pos_log_data = fila["POSLOG_DATA"].ToString();
string env = data_ora.ws_envio_poslog_tda(param);
if (error.Length > 0)
{
return(error);
}
/*en este caso quiere decir que no hay errores en el envio*/
if (env.Length == 0)
{
data_ora.update_tmp_bata_transac_ora(param, ref error);
}
// param.
}
}
}
#endregion
}
catch (Exception exc)
{
error = exc.Message + "==>POSLOG";
}
return(error);
}
