/// <summary>
/// Marks orphaned file records (the ones that do not have a referencing binary record anymore) as Deleted.
/// </summary>
public void CleanupFilesSetDeleteFlag()
{
var isLocalTransaction = false;
if (!TransactionScope.IsActive)
{
TransactionScope.Begin();
isLocalTransaction = true;
}
try
{
using (var proc = new SqlProcedure {
CommandText = CleanupFileSetIsdeletedScript, CommandType = CommandType.Text
})
{
proc.CommandType = CommandType.Text;
proc.ExecuteNonQuery();
}
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
catch (Exception ex)
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during setting deleted flag on files.", ex);
}
}
protected override void ExecuteAction(ActionArgs args, ActionResult result)
{
if (args.CommandName.Equals("Custom") && args.CommandArgument.Equals("Execute"))
{
using (SqlProcedure sp = new SqlProcedure("sp_KMT_Order_Execute"))
{
sp.AddParameter("@Username", Context.User.Identity.Name);
if (sp.ExecuteScalar().ToString().Equals("0"))
{
result.ShowAlert("Invalid Material Number");
result.Refresh();
}
else
{
result.ShowAlert("Execute OK");
result.Refresh();
}
}
}
else if (args.CommandName.Equals("Custom") && args.CommandArgument.Equals("ClearData"))
{
using (SqlProcedure sp = new SqlProcedure("sp_KMT_Order_ClearData"))
{
sp.ExecuteNonQuery();
result.Refresh();
}
}
}
public void r104Implementation(Contratado_Solicitud_VacacionesModel instance)
{
// This is the placeholder for method implementation.
//string conStr = ConfigurationManager.ConnectionStrings["arhmexdb_DEV"].ToString();
int diasFinalesAutorizados = Convert.ToInt32(instance.Dias_Autorizados);
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Revision_De_Solicitud"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
sql.AddParameter("@Tipo_Prestacion_Id", 1); // PRESTACION TIPO VACACION
sql.AddParameter("@Estado_Solicitud_Vacaciones", 4); // 4. Significa AUTORIZADO CON CAMBIOS
sql.AddParameter("@Solicitud_Vacaciones_Key", instance.Solicitud_Vacaciones_Key);
sql.AddParameter("@Dias_Autorizados", diasFinalesAutorizados);
//sql.AddParameter("@Contratante_Id", instance.Contratante_Id);
sql.AddParameter("@Contratado_Id", instance.Contratado_Id);
sql.AddParameter("@Fecha_Solicitud", instance.Fecha_Solicitud);
sql.AddParameter("@Dias_Solicitados", instance.Dias_Solicitados);
sql.AddParameter("@Fecha_Inicio_Vacaciones", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@Notas_Autorizacion_Rechazo", "");
/*Parametros de sistema**/
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
//cmd.Parameters.Add("@Mensaje_Sistema_Id", 1);//.Direction = ParameterDirection.Output;
sql.AddParameter("@Mensaje_Sistema_Id", 1);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", ""); //.Direction = ParameterDirection.Output;
sql.AddParameter("@Debug", 0);
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
} // using (SqlProcedure sql = new SqlProcedure("dbo.sp_Revision_De_Solicitud"))
} //public void r104Implementation(Contratado_Solicitud_VacacionesModel instance)
public void r107Implementation(Contratado_Solicitud_VacacionesModel instance)
{
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Solicitud_Vacaciones"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
sql.AddParameter("@Tipo_Operacion_Id", 1); // 1. Significa INSERT/CREACION NUEVO
sql.AddParameter("@Contratado_Solicitud_Vacaciones_Id", 0); /// Este es el ID de la tabla de solicitudes.
sql.AddParameter("@Solicitud_Vacaciones_Key", "");
sql.AddParameter("@Contratado_Id", instance.Contratado_Id);
sql.AddParameter("@Fecha_Solicitud", instance.Fecha_Solicitud);
sql.AddParameter("@Dias_Solicitados", instance.Dias_Solicitados);
sql.AddParameter("@Fecha_Inicio_Vacaciones", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@Estatus_Solicitud_Id", instance.Estatus_Solicitud_Id);
sql.AddParameter("@Fecha_Autorizacion_Rechazo", DateTime.Today);
sql.AddParameter("@Autorizado_Rechazado_Por_Id", UserId);
sql.AddParameter("@Notas_Autorizacion_Rechazo", instance.Notas_Autorizacion_Rechazo);
sql.AddParameter("@Dias_Autorizados", instance.Dias_Autorizados);
sql.AddParameter("@Fecha_Inicio_Vacaciones_Autorizado ", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones_Autorizado ", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@HTML ", "");
/*Parametros de sistema**/
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
sql.AddParameter("@Mensaje_Sistema_Id", 1);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", "");
sql.AddParameter("@Debug", 0);
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
} // using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Solicitud_Vacaciones"))
} // public void r107Implementation(Contratado_Solicitud_VacacionesModel instance)
/// <summary>
/// Finalizes a chunked save operation.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="fileId">File identifier.</param>
/// <param name="fullSize">Full size (stream length) of the binary value.</param>
/// <param name="source">Binary data containing metadata (e.g. content type).</param>
public void CommitChunk(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source = null)
{
// start a new transaction here if needed
var isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
{
TransactionScope.Begin();
}
try
{
// commit the process: set the final full size and checksum
using (var cmd = new SqlProcedure {
CommandText = CommitChunkScript, CommandType = CommandType.Text
})
{
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = fullSize;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = source != null ? source.ContentType : string.Empty;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = source != null
? source.FileName.FileNameWithoutExtension == null
? DBNull.Value
: (object)source.FileName.FileNameWithoutExtension
: DBNull.Value;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = source != null?ValidateExtension(source.FileName.Extension) : string.Empty;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
// rollback the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during committing binary chunk to file stream.", ex);
}
finally
{
// commit the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
}
/// <summary>
/// Deletes a binary property value from the metadata database, making the corresponding blbo storage entry orphaned.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
public void DeleteBinaryProperty(int versionId, int propertyTypeId)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Delete"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.ExecuteNonQuery();
}
finally
{
cmd?.Dispose();
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("TYM_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40103010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_TYM_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
MyCompany.Data.Objects.TYMForcastImport Order = new MyCompany.Data.Objects.TYMForcastImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = line.Substring(0, 3) + "-" + line.Substring(3, 5) + "-" + line.Substring(8, 2) + "-" + line.Substring(10, 2) + "-" + line.Substring(12, 2) + "-" + line.Substring(14, 4) + "-" + line.Substring(18, 4);//line.Substring(0, 22);
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
Order.DeliveryDate = Convert.ToDateTime(line.Substring(22, 4).Trim() + "-" + line.Substring(26, 2).Trim() + "-" + line.Substring(28, 2).Trim());
Order.Quantity = float.Parse(line.Substring(30, 8)).ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
sr.Close();
}
}
}
public void r111Implementation(AusentismoModel instance)
{
// This is the placeholder for method implementation.
int ausentismoId = 0;
string mensajeSistema = "";
int mensajeSistemaId = 0;
int tipoOperacionId = 1;
string html = "";
if (instance.Tipo_Ausentismo_Id == 1)
{
instance.Fecha_Fin_Ausentismo = instance.Fecha_Inicio_Ausentismo;
}
/*
* TIPO DE OPERACIÓN
* 0 Sin Tipo de Operación
* 1 Alta
* 2 Edición
* 3 Borrado
* 4 Autorización
* 5 Autorización con Cambios
* 6 Rechazo
* 7 Cancelación
*/
using (SqlProcedure sp = new SqlProcedure("dbo.sp_Administrar_Ausentismo"))
{
try
{
//SqlServerCommand.Command.CommandType = System.Data.CommandType.StoredProcedure;
//SqlServerCommand.Command.CommandType
sp.Command.CommandTimeout = 60 * 10;
sp.AddParameter("@Tipo_Operacion_Id", tipoOperacionId);
sp.AddParameter("@Ausentismo_Id", instance.Ausentismo_Id);
sp.AddParameter("@Ausentismo_Key", instance.Ausentismo_Key);
sp.AddParameter("@Tipo_Ausentismo_Id", instance.Tipo_Ausentismo_Id);
sp.AddParameter("@Contratado_Id", instance.Contratado_Id);
sp.AddParameter("@Fecha_Solicitud_Ausentismo", instance.Fecha_Solicitud_Ausentismo);
sp.AddParameter("@Fecha_Inicio_Ausentismo", instance.Fecha_Inicio_Ausentismo);
sp.AddParameter("@Fecha_Fin_Ausentismo", instance.Fecha_Fin_Ausentismo);
sp.AddParameter("@Dias_Ausentismo_Solicitados", instance.Dias_Ausentismo_Solicitados);
sp.AddParameter("@Motivo_Ausentismo", instance.Motivo_Ausentismo);
sp.AddParameter("@Estatus_Ausentismo_Id", instance.Estatus_Ausentismo_Id);
sp.AddParameter("@Fecha_Autorizacion_Rechazo", instance.Fecha_Autorizacion_Rechazo);
sp.AddParameter("@Autorizado_Rechazado_Por_Id", DBNull.Value);
sp.AddParameter("@Notas_Autorizacion_Rechazo", instance.Notas_Autorizacion_Rechazo);
sp.AddParameter("@Dias_Autorizados", instance.Dias_Ausentismo_Solicitados);
sp.AddParameter("@Fecha_Inicio_Ausentismo_Autorizado", instance.Fecha_Inicio_Ausentismo);
sp.AddParameter("@Fecha_Fin_Ausentismo_Autorizado", instance.Fecha_Fin_Ausentismo);
sp.AddParameter("@HTML", html);
sp.AddParameter("@Mensaje_Sistema", mensajeSistema);
//Definición de parámetros de Sistema
sp.AddParameter("@Creado_Por_Id", UserId);
sp.AddParameter("@Transaccion_Id", DBNull.Value);
sp.AddParameter("@Fijar_Bitacora", 1);
sp.AddParameter("@Mostrar_Tablas_Sistema", 0);
sp.AddParameter("@Mensaje_Sistema_Id", 0);
sp.AddParameter("@Simular", 0);
sp.AddParameter("@Trace", "");
sp.AddParameter("@Debug", 0);
//Definición de dirección para parametros de salida
sp.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sp.Parameters["@Mensaje_Sistema"].Direction = ParameterDirection.Output;
//sp.Parameters["@Persona_Domicilio_Id"].Direction = ParameterDirection.Output;
sp.Parameters["@Trace"].Direction = ParameterDirection.Output;
sp.Parameters["@Ausentismo_Id"].Direction = ParameterDirection.Output;
sp.Parameters["@HTML"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sp.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sp.Parameters["@Mensaje_Sistema"].Size = 8000;
sp.Parameters["@Trace"].Size = 8000;
sp.Parameters["@Ausentismo_Id"].Size = 4;
sp.Parameters["@HTML"].Size = 8000;
//EJECUCIÓN SP
sp.ExecuteNonQuery();
//ASIGNACIÓN DE PARÁMETRO DE SALIDA
mensajeSistemaId = Convert.ToInt32(sp.Parameters["@Mensaje_Sistema_Id"].Value);
mensajeSistema = Convert.ToString(sp.Parameters["@Mensaje_Sistema"].Value);
//objetoPersonaDomicilioId = Convert.ToInt32(sp.Parameters["@Persona_Domicilio_Id"].Value);
//Result.ShowAlert(mensajeSistema);
if (mensajeSistemaId == 1)
{
ausentismoId = Convert.ToInt32(sp.Parameters["@Ausentismo_Id"].Value);
instance.Ausentismo_Id = ausentismoId;
Result.Refresh();
Result.Refresh();
Result.Continue();
} // if (mensajeSistemaId == 1)
else // if (mensajeSistemaId != 1)
{
Result.ShowAlert(mensajeSistema);
} // if (mensajeSistemaId != 1)
} //try
catch (Exception ex)
{
mensajeSistemaId = 665;
mensajeSistema = ex.Message;
Result.ShowAlert(mensajeSistema);
//return false;
} //catch (Exception e)
} //using (SqlProcedure sp = new SqlProcedure("dbo.sp_Administrar_Ausentismo"))
} //public void r111Implementation(AusentismoModel instance)
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("NMT_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40118010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
int totalLine = 0;
Dictionary <int, string> DueDate = new Dictionary <int, string>();
using (SqlProcedure sp = new SqlProcedure("sp_NMT_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
string[] data = line.Split(',');
if (data.Length > 0 && totalLine == 0)
{
#region DeliveryDate
for (int i = 11; i < data.Length; i++)
{
if (data[i].StartsWith("Daily Qty(N+"))
{
//Daily Qty(N+1 month 26th)
// get N Month
//string[] montData = data[i].Split('+');
//string DeliveryDate = String.Format("{0}-{1}-", data[236].ToString().Trim() , String.Format("{0:00}"
// , Convert.ToInt32( data[237].ToString().Trim()) + Convert.ToInt32(data[1].Substring(0,1))));
if (!DueDate.ContainsKey(i))
{
DueDate.Add(i, data[i]);
}
}
}
#endregion
}
else
{
// RowData
//DeliveryDate + string.Format("{0:00}", montData[1].Substring(7,montData[1].Length-1).Replace("th","").Replace("st","").Replace("nd","").Replace("rd",""))
if (!String.IsNullOrEmpty(data[236].Trim()))
{
foreach (KeyValuePair <int, string> item in DueDate)
{
int Qty = 0;
Int32.TryParse(data[item.Key], out Qty);
string[] _dueDate = item.Value.Trim().Split('+');
//Daily Qty(N+2 month 18th)
int _month = Convert.ToInt32(data[237]) + Convert.ToInt32(_dueDate[1].Substring(0, 1));
int _year = Convert.ToInt32(data[236]);
if (_month > 12)
{
_year += Convert.ToInt32(_month / 12);
_month = _month % 12;
}
//Check Day
int _day = Convert.ToInt32(_dueDate[1].Substring(8, 4).Trim().Replace("th", "").Replace("st", "").Replace("nd", "").Replace("rd", "").Replace(")", ""));
int lastDayOfMonth = DateTime.DaysInMonth(_year, _month);
if (_day <= lastDayOfMonth)
{
string DeliveryDate = string.Format("{0}-{1}-{2}"
, _year
, String.Format("{0:00}", _month)
, String.Format("{0:00}", _day)
);
//DeliveryDate + string.Format("{0:00}", montData[1].Substring(7,montData[1].Length-1).Replace("th","").Replace("st","").Replace("nd","").Replace("rd",""))
MyCompany.Data.Objects.NMTForcastImport Order = new MyCompany.Data.Objects.NMTForcastImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = data[10].ToString().Trim();
Order.CustomerMatCode = data[4].Trim();
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
Order.DeliveryDate = Convert.ToDateTime(DeliveryDate);
Order.Quantity = Qty.ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
}
}
}
totalLine++;
}
sr.Close();
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("TSM_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_TSM_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
string[] strCol = { "DeliveryDestination", "CustomerMatCode", "CustomerPO", "Quantity", "DeliveryDate", "FromTo", "StatusCode", "DeliveryDateOld" };
DataTable dt = new DataTable();
foreach (string strItem in strCol)
{
if (strItem == "Quantity")
{
dt.Columns.Add(strItem, typeof(int));
}
else
{
dt.Columns.Add(strItem);
}
}
while ((line = sr.ReadLine()) != null)
{
string[] item = ParseCsvRow(line);
if (item.Length > 1 && (item[1].Trim() == "NEW" || item[1].Trim() == "RES"))
{
dt.Rows.Add(item[2].Trim(), item[3].Trim(), item[7].Trim(), Convert.ToInt32(item[9].Trim()), item[10].Trim(), "RTP:" + item[2].Trim(), item[1].Trim(), item[11].Trim());
}
}
DataTable dtTemp = dt.AsEnumerable()
.GroupBy(r => new { Col1 = r["DeliveryDestination"], Col2 = r["CustomerMatCode"], Col3 = r["CustomerPO"], Col4 = r["DeliveryDate"], Col5 = r["FromTo"], Col6 = r["StatusCode"], Col7 = r["DeliveryDateOld"] })
.Select(g =>
{
var row = dt.NewRow();
row["DeliveryDestination"] = g.Key.Col1;
row["CustomerMatCode"] = g.Key.Col2;
row["CustomerPO"] = g.Key.Col3;
row["Quantity"] = g.Sum(r => r.Field <int>("Quantity"));
row["DeliveryDate"] = g.Key.Col4;
row["FromTo"] = g.Key.Col5;
row["StatusCode"] = g.Key.Col6;
row["DeliveryDateOld"] = g.Key.Col7;
return(row);
}).CopyToDataTable();
string CustCode = "40102011";
foreach (DataRow item in dtTemp.Rows)
{
string[] dmy = Convert.ToString(item[4]).Split('/');
MyCompany.Models.TSMForcastImport Forcast = new MyCompany.Models.TSMForcastImport();
Forcast.OrderBy = CustCode;
Forcast.DeliveryDestination = Convert.ToString(item[0]);
Forcast.CustomerMatCode = Convert.ToString(item[1]);
Forcast.CustomerPO = Convert.ToString(item[2]);
Forcast.ReliabilityDevision = "P";
Forcast.Quantity = Convert.ToInt32(Convert.ToString(item[3]));
Forcast.Unit = "ST";
Forcast.PlngPeriod = "D";
Forcast.DeliveryDate = Convert.ToDateTime(dmy[0] + "-" + dmy[1] + "-20" + dmy[2]);
string[] materialTemp = SharedBusinessRules.getMaterial(Forcast.CustomerMatCode, CustCode, Forcast.DeliveryDestination, "TSM").Split(':');
if (materialTemp.Length > 1)
{
Forcast.SAPCode = materialTemp[0];
Forcast.PartsDevision = materialTemp[1];
Forcast.DeliveryDestinationCode = materialTemp[2];
Forcast.Key1 = materialTemp[3];
Forcast.Key2 = materialTemp[4];
Forcast.Key3 = materialTemp[5];
}
Forcast.FileName = fn;
if (Convert.ToString(item[6]) == "RES")
{
string[] dmyOld = Convert.ToString(item[7]).Split('/');
Forcast.DeliveryDateOld = Convert.ToDateTime(dmyOld[0] + "-" + dmyOld[1] + "-20" + dmyOld[2]);
}
Forcast.Condition = Convert.ToString(item[6]);
Forcast.Insert();
}
}
}
}
public void r101Implementation(Persona_DomicilioModel instance)
{
// This is the placeholder for method implementation.
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Creacion_Domicilio_Persona"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
//Definición de Parametros especificos del SP
//
//sql.AddParameter("@Producto_Nombre", instance.Producto_);
sql.AddParameter("@Tipo_Operacion_Id", 1);// 1. INSERT
sql.AddParameter("@Persona_Id", instance.Persona_Id);
sql.AddParameter("@Calle", instance.Custom_Domicilio_Calle);
sql.AddParameter("@Numero_Interior", instance.Custom_Domicilio_Numero_Interior);
sql.AddParameter("@Numero_Exterior", instance.Custom_Domicilio_Numero_Exterior);
sql.AddParameter("@Colonia", instance.Custom_Domicilio_Colonia);
sql.AddParameter("@Ciudad_Id", instance.Custom_Domicilio_Ciudad_Id);
sql.AddParameter("@Codigo_Postal_Id", instance.Custom_Domicilio_Codigo_Postal_Id);
sql.AddParameter("@Entre_Calles", instance.Custom_Domicilio_Entre_Calles);
sql.AddParameter("@Latitud", instance.Custom_Domicilio_Latitud);
sql.AddParameter("@Longitud", instance.Custom_Domicilio_Longitud);
sql.AddParameter("@Tipo_Domicilio_Id", instance.Tipo_Domicilio_Id);
sql.AddParameter("@Es_Domicilio_Principal", instance.Es_Domicilio_Principal);
//-------------------------
//Definición de parámetros de Salida
sql.AddParameter("@Persona_Domicilio_Id", instance.Persona_Domicilio_Id);
sql.AddParameter("@Estatus_Registro_Id", 1);
sql.AddParameter("@Domicilio_Id", 0);
//Definición de parametros de Sistema
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
sql.AddParameter("@Mensaje_Sistema_Id", 0);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", "");
sql.AddParameter("@Debug", 0);
//Definición de dirección para parametros de salida
//sql.Parameters["@Persona_Domicilio_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
}// using (SqlProcedure sql = new SqlProcedure("dbo.sp_Creacion_Domicilio_Persona"))
}
public void r105Implementation(PersonaModel instance)
{
// This is the placeholder for method implementation.
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Persona_Contacto"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
//Definición de Parametros especificos del SP
//
//sql.AddParameter("@Producto_Nombre", instance.Producto_);
sql.AddParameter("@Tipo_Operacion_Id", 1);// 1. INSERT
sql.AddParameter("@Nombre", instance.Nombre);
sql.AddParameter("@Apellido_Paterno", instance.Apellido_Paterno);
sql.AddParameter("@Apellido_Materno", instance.Apellido_Materno);
sql.AddParameter("@Segundo_Nombre", instance.Segundo_Nombre);
sql.AddParameter("@Fecha_Nacimiento", instance.Fecha_Nacimiento);
sql.AddParameter("@Maximo_Nivel_Escolaridad_Id", instance.Maximo_Nivel_Escolaridad_Id);
sql.AddParameter("@Genero_Persona_Id", instance.Genero_Persona_Id);
sql.AddParameter("@Correo_Personal", instance.Correo_Personal);
sql.AddParameter("@Numero_Celular_Personal", instance.Numero_Celular_Personal);
//-------------------------
//Definición de parámetros de Salida
sql.AddParameter("@Persona_Jefe_Id", instance.Persona_Id);
//Definición de parametros de Sistema
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
sql.AddParameter("@Mensaje_Sistema_Id", 0);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", "");
sql.AddParameter("@Debug", 0);
//Definición de dirección para parametros de salida
//sql.Parameters["@Persona_Domicilio_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
}// using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Persona_Contacto"))
/*
* bool resultadoAdministrarPersonaContacto;
* string mensajeSistema = "";
* int mensajeSistemaId = 0;
* int tipoOperacionId = 1;
* string nombre = instance.Nombre;
* string apellidoPaterno = instance.Apellido_Paterno;
* string apellidoMaterno = instance.Apellido_Materno;
* string segundoNombre = instance.Segundo_Nombre;
* DateTime fechaNacimiento = (DateTime)instance.Fecha_Nacimiento;
* int maximoNivelEscolaridadId = (int)instance.Maximo_Nivel_Escolaridad_Id;
* int generoPersonaId = (int)instance.Genero_Persona_Id;
* string correoPersonal = instance.Correo_Personal;
* string numeroCelularPersonal = instance.Numero_Celular_Personal;
* int personaId = 0;
* SharedBusinessRules sb = new SharedBusinessRules();
*
* resultadoAdministrarPersonaContacto = sb.AdministrarPersonaContacto(
* tipoOperacionId
* , nombre
* , apellidoPaterno
* , apellidoMaterno
* , segundoNombre
* , fechaNacimiento
* , maximoNivelEscolaridadId
* , generoPersonaId
* , correoPersonal
* , numeroCelularPersonal
* , out personaId
* , out mensajeSistema
* , out mensajeSistemaId
* );
* if (resultadoAdministrarPersonaContacto)
* {
* //Result.ShowAlert(mensajeSistema);
* Result.Continue();
* // Result.Refresh();
* }//if (resultadoAdministrarPersonaContacto)
* else//if (!resultadoAdministrarPersonaContacto)
* {
* Result.ShowAlert(mensajeSistema);
* }//if (!resultadoAdministrarPersonaContacto)
*/
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("THM_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = string.Empty; // "40108011";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
int totalLine = 0, totalOrder = 1;
using (SqlProcedure sp = new SqlProcedure("sp_THM_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
totalLine++;
if (line.Length > 0)
{
#region Format
// "000000000000" is Supply Part
string shipto = line.Substring(145, 5) + "-" + line.Substring(22, 6);
if ((line.Substring(140, 5) == "45320" || line.Substring(140, 5) == "05386") && line.Substring(10, 12) != "000000000000")
{
MyCompany.Models.THMOrderImport Order = new MyCompany.Models.THMOrderImport();
Order.DeliveryDestination = line.Substring(145, 5) + "-" + line.Substring(22, 6);
Order.OrderBy = "40101011";
Order.CustomerMatCode = line.Substring(28, 18).Trim().Replace(" ", "");
string[] materialTemp = SharedBusinessRules.getMaterial(line.Substring(28, 18).Trim().Replace(" ", ""), Order.OrderBy, line.Substring(22, 6), line.Substring(145, 5)).Split(':');
if (materialTemp.Length > 1)
{
Order.SAPCode = materialTemp[0];
Order.PartsDevision = materialTemp[1];
Order.DeliveryDestinationCode = materialTemp[2];
Order.Key1 = materialTemp[3];
Order.Key2 = materialTemp[4];
Order.Key3 = materialTemp[5];
}
Order.CustomerPO = line.Substring(10, 12).Trim();
Order.ReliabilityDevision = "P"; //Realiability = [Forcast=F,Order=P]
Order.DeliveryDate = Convert.ToDateTime(line.Substring(118, 4).Trim() + "-" + line.Substring(122, 2).Trim() + "-" + line.Substring(124, 2).Trim());
Order.Quantity = line.Substring(126, 7).Trim();
Order.Unit = "ST"; // SD Fixd Data Unit = ST
//SD Fixd Data Unit = ST, Periad = D, Realiability = [Forcast=F,Order=P]
Order.PlngPeriod = "D"; // Periad = D
Order.PlantCode = line.Substring(22, 6) + " Add. " + line.Substring(94, 4).Trim();
Order.AddCode = totalOrder.ToString();
Order.Arrivaltime = line.Substring(116, 2) + ":00:00";
Order.FromTo = line.Substring(88, 5).Trim() + ":" + line.Substring(145, 5);
Order.Filename = fn;
Order.Insert();
totalOrder++;
}
#endregion
}
}
sr.Close();
}
}
//// Save file
//string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
//filename = String.Format("THM_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
//AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
//string CustCode = "40101010";
//filename = Server.MapPath("~/Files/") + filename;
//if (File.Exists(filename))
//{
// string line = string.Empty;
// using (StreamReader sr = new StreamReader(filename))
// {
// using (SqlProcedure sp = new SqlProcedure("sp_THM_Order_ClearData"))
// {
// sp.ExecuteNonQuery();
// }
// while ((line = sr.ReadLine()) != null)
// {
// if (line.Length > 0)
// {
// //string shipto = line.Substring(145, 5) + "-" + line.Substring(22, 6);
// if ((line.Substring(140, 5) == "45320" || line.Substring(140, 5) == "05386") && line.Substring(10, 12) != "000000000000")
// //if ((line.Substring(140, 5).Trim() == "CRS1" || line.Substring(140, 5) == "45320") && line.Substring(10, 12) != "000000000000")
// {
// //string[] item = line.Split(Convert.ToChar("|"));
// MyCompany.Models.THMOrderImport Order = new MyCompany.Models.THMOrderImport();
// Order.OrderBy = CustCode;
// Order.DeliveryDestination = ""; //Not sure
// Order.CustomerMatCode = line.Substring(28, 18).Replace(" ","");
// Order.PartsDevision = "1";
// Order.CustomerPO = line.Substring(10, 12);
// Order.ReliabilityDevision = "P";
// Order.DeliveryDate =Convert.ToDateTime( line.Substring(118, 4) + "-" + line.Substring(122, 2) + "-" + line.Substring(124, 2));
// Order.Quantity = float.Parse(line.Substring(126, 7)).ToString().Trim();
// Order.Unit = "ST";
// Order.PlngPeriod = "D";
// string[] materialTemp = SharedBusinessRules.getMaterial(Order.CustomerMatCode, Order.OrderBy, PlantCode, LocationCode).Split(':');
// if (materialTemp.Length > 1)
// {
// Order.SAPCode = materialTemp[0];
// Order.PartsDevision = materialTemp[1];
// Order.DeliveryDestinationCode = materialTemp[2];
// }
// Order.Insert();
// }
// }
// }
// sr.Close();
// }
//}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("HATC_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_IMCT_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
sr.ReadLine();
while ((line = sr.ReadLine()) != null)
{
string DeliveryDestination = line.Substring(33, 6).Trim();
string OrderBy = "40111011";
string CusMatCode = line.Substring(41, 10);
string locCode = line.Substring(39, 2).Trim();
string[] materialTemp = SharedBusinessRules.getMaterial(CusMatCode, OrderBy, DeliveryDestination, "IMCT").Split(':');
int startPointDate = 92;
int startPointQty = 148;
for (int w = 1; w <= 6; w++)
{
DateTime dateOld = Convert.ToDateTime(line.Substring(startPointDate, 4).ToString() + "-" + line.Substring(startPointDate + 4, 2).ToString() + "-" + line.Substring(startPointDate + 6, 2).ToString());
int subPointDate = 8;
int subPointQty = 0;
for (int d = 1; d <= 6; d++)
{
DateTime dateDrliveryDate;
if (line.Substring(startPointDate + subPointDate, 4).ToString().Trim() != string.Empty)
{
dateDrliveryDate = Convert.ToDateTime(line.Substring(startPointDate + subPointDate, 4).ToString() + "-" + line.Substring(startPointDate + subPointDate + 4, 2).ToString() + "-" + line.Substring(startPointDate + subPointDate + 6, 2).ToString());
}
else
{
dateDrliveryDate = dateOld.AddDays(d - 1);
}
int numQty = Convert.ToInt32(line.Substring(startPointQty + subPointQty, 6).ToString());
MyCompany.Models.IMCTForcastImport forcast = new MyCompany.Models.IMCTForcastImport();
forcast.OrderBy = OrderBy;
forcast.DeliveryDestination = DeliveryDestination + "-" + locCode;
forcast.CustomerMatCode = CusMatCode;
if (materialTemp.Length > 1)
{
forcast.SAPCode = materialTemp[0];
forcast.PartsDevision = materialTemp[1];
forcast.DeliveryDestinationCode = materialTemp[2];
forcast.Key1 = materialTemp[3];
forcast.Key2 = materialTemp[4];
forcast.Key3 = materialTemp[5];
}
forcast.CustomerPO = "";
forcast.ReliabilityDevision = "F";
forcast.Unit = "ST";
forcast.PlngPeriod = "D";
forcast.DeliveryDate = dateDrliveryDate;
forcast.Quantity = numQty;
forcast.FileName = fn;
forcast.Insert();
subPointDate += 8;
subPointQty += 6;
}
startPointDate += 100;
startPointQty += 100;
}
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("HATC_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
string LocationCode = string.Empty;
// HATC Order
string CustCode = string.Empty;
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
//List<MyCompany.Models.HATCForcastImport> HATCList = new List<MyCompany.Models.HATCForcastImport>();
string CustMatNo = string.Empty, year = string.Empty, PlantCode = string.Empty;
Dictionary <int, string> dueDate = new Dictionary <int, string>();
Dictionary <int, double> orderQty = new Dictionary <int, double>();
int lineIndex = -1;
using (SqlProcedure sp = new SqlProcedure("sp_HATC_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
string ReliabilityDevision = string.Empty;
while ((line = sr.ReadLine()) != null)
{
if (lineIndex >= 0)
{
lineIndex++;
}
switch (lineIndex)
{
case 3:
year = line.Substring(40, 4);
string[] ln = line.Split(' ');
LocationCode = ln[3];
break;
case 5:
try
{
dueDate.Clear();
orderQty.Clear();
// get Part and Delivery Date
CustMatNo = line.Substring(0, line.IndexOf("DATE")).Trim();
string[] data = line.Substring(line.IndexOf("DATE") + 4, (line.IndexOf("TOTAL") - line.IndexOf("DATE") - 4)).Trim().Split(' ');
int colIndex = 0;
for (int i = 0; i < data.Length; i++)
{
if (!string.IsNullOrEmpty(data[i].Trim()))
{
string[] _dueDate = data[i].Split('/');
if (_dueDate.Length > 0)
{
dueDate.Add(colIndex, String.Format("{0}-{1}-{2}", year, _dueDate[1], _dueDate[0]));
colIndex++;
}
}
}
}
catch (Exception ex)
{
}
break;
case 6: try
{
line = line.Substring(line.IndexOf("QTY.") + 4, line.Length - line.IndexOf("QTY.") - 10);
int colIndex = 0;
while (line.Length >= 7)
{
string qty = line.Substring(0, 7).Trim();
qty = String.IsNullOrEmpty(qty) ? "0" : qty;
orderQty.Add(colIndex, Convert.ToDouble(qty));
line = line.Substring(7, line.Length - 7);
colIndex++;
}
}
catch (Exception ex)
{
}
break;
case 11:
PlantCode = line.Substring(16, 17).Trim();
for (int i = 0; i < dueDate.Count; i++)
{
//[XXXX]03 => 40138011 => HATC-P
//[XXXX]xx => 40108011 => HATC
MyCompany.Models.HATCForcastImport forcast = new MyCompany.Models.HATCForcastImport();
forcast.OrderBy = PlantCode.EndsWith("03") ? "40138011" : "40108011";
forcast.DeliveryDestination = PlantCode + "-" + LocationCode;
forcast.CustomerMatCode = CustMatNo;
string[] materialTemp = SharedBusinessRules.getMaterial(forcast.CustomerMatCode, PlantCode.EndsWith("03") ? "40138011" : "40108011", PlantCode, LocationCode).Split(':');
if (materialTemp.Length > 1)
{
forcast.SAPCode = materialTemp[0];
if (materialTemp[1] == "1A")
{
forcast.PartsDevision = "1";
}
else if (materialTemp[1] == "1B")
{
forcast.PartsDevision = "2";
}
else
{
forcast.PartsDevision = materialTemp[1];
}
forcast.DeliveryDestinationCode = materialTemp[2];
forcast.Key1 = materialTemp[3];
forcast.Key2 = materialTemp[4];
forcast.Key3 = materialTemp[5];
}
forcast.CustomerPO = "";
forcast.ReliabilityDevision = ReliabilityDevision;
forcast.Unit = "ST";
forcast.PlngPeriod = "D";
forcast.DeliveryDate = Convert.ToDateTime(dueDate[i]);
forcast.Quantity = Convert.ToInt32(orderQty[i].ToString());
forcast.FileName = fn;
forcast.Insert();
}
break;
default:
break;
}
if (line.Trim().StartsWith("PART ORDER SCHEDULE (MAIL)") || line.Trim().StartsWith("PURCHASE SHEET (MAIL)"))
{
if (line.Trim().StartsWith("PART ORDER SCHEDULE (MAIL)"))
{
ReliabilityDevision = "F";
}
else if (line.Trim().StartsWith("PURCHASE SHEET (MAIL)"))
{
ReliabilityDevision = "P";
}
lineIndex = 0;
}
}
sr.Close();
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("HATC_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
//string CustCode = "40108011";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
using (StreamReader sr = new StreamReader(filename))
{
List <MyCompany.Data.Objects.HATCForcastMonthImport> HATCList = new List <MyCompany.Data.Objects.HATCForcastMonthImport>();
string line = string.Empty, CustMatNo = string.Empty, year = string.Empty, PlantCode = string.Empty;
Dictionary <int, string> dueDate = new Dictionary <int, string>();
Dictionary <int, double> orderQty = new Dictionary <int, double>();
using (SqlProcedure sp = new SqlProcedure("sp_HATC_ForcastMonth_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.EndsWith("MONTH") && line.StartsWith(""))
{
dueDate.Clear();
// Correct DueDate
string[] _data = line.Substring(line.IndexOf("PLAN GROUP") + 10, line.IndexOf("MONTH") - line.IndexOf("PLAN GROUP") - 10).Trim().Split(' ');
int dataIndex = 0;
for (int i = 0; i < _data.Length; i++)
{
if (!String.IsNullOrEmpty(_data[i]))
{
dueDate.Add(dataIndex, _data[i]);
dataIndex++;
}
}
}
int lineNum = 0;
if (Int32.TryParse(line.Substring(0, 6).Trim(), out lineNum))
{
if (!(line.Trim().EndsWith("ROKI(THAILAND)CO.,LTD.") || line.Trim().StartsWith("TOTAL") || String.IsNullOrEmpty(line.Substring(0, 50).Trim())))
{
CustMatNo = line.Substring(6, 35).Trim();
PlantCode = line.Substring(41, 15).Trim();
string[] _qty = line.Substring(57, line.Length - 57).Trim().Split(' ');
int dataIndex = 0;
orderQty.Clear();
for (int i = 0; i < _qty.Length; i++)
{
if (!String.IsNullOrEmpty(_qty[i]))
{
orderQty.Add(dataIndex, Convert.ToDouble(_qty[i]));
dataIndex++;
}
}
// Insert Data
foreach (KeyValuePair <int, string> item in dueDate)
{
MyCompany.Data.Objects.HATCForcastMonthImport Order = new MyCompany.Data.Objects.HATCForcastMonthImport();
Order.OrderBy = PlantCode.EndsWith("03") ? "40138011" : "40108011";
Order.DeliveryDestination = "";
Order.CustomerMatCode = CustMatNo;
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "F";
Order.Unit = "ST";
Order.PlngPeriod = "M";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
string[] _dueDate = item.Value.Split('/');
Order.DeliveryDate = Convert.ToDateTime(String.Format("{0}-{1}-{2}", _dueDate[1], string.Format("{0:00}", Convert.ToInt32(_dueDate[0])), "01"));
Order.Quantity = Convert.ToDecimal(orderQty[item.Key]);
Order.Insert();
}
}
}
}
sr.Close();
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40106010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
//List<MT05> Data = new List<MT05>();
string CustomerCode = "SAAPC";
DataTable dt = new DataTable();
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
//MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\ROKI\WorkEDI\EDI DATA_20170301\SD\KMT\Forecast_KMT.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1;");
//string filename = @"D:\ROKI\WorkEDI\EDI DATA_20170301\SD\NMT_X\NMT\GeneralForecast20160622.csv";
//string filename = @"D:\ROKI\WorkEDI\EDI DATA_20170301\SA\APC\Oct\2016-11_FORECAST ORDER 05386-45320_EDI.xls";
MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties='Excel 12.0;HDR=YES;'");
//provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\Programming\\Spreadsheet-Current.xlsx';Extended Properties='Excel 12.0;HDR=YES;'
MyConnection.Open();
DataTable dtSheet = MyConnection.GetSchema("Tables");
string SheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString().Trim();
myCommand.Connection = MyConnection;
//sql = "select * from [05386-45320$]";
sql = "select * from [" + SheetName + "]";
myCommand.CommandText = sql;
OleDbDataReader reader = myCommand.ExecuteReader();
//while (reader.Read())
//{
//var val1 = reader[0].ToString();
//}
//myCommand.ExecuteNonQuery();
//System.Data.OleDb.OleDbDataReader dr = new System.Data.OleDb.OleDbDataReader();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
//Dim y As New Odbc.OdbcDataAdapter(sql, MyConnection);
//dr = myCommand.ExecuteReader();
da.Fill(dt);
MyConnection.Close();
//MessageBox.Show("Success");
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
string issueDate = "";
if (dt.Rows.Count > 0)
{
issueDate = dt.Rows[4][5].ToString().Trim();
issueDate = issueDate.Replace("ISSUED DATE : ", " ");
List <MIndex> itemHead = new List <MIndex>();
using (SqlProcedure sp = new SqlProcedure("sp_APC_ForcastMonth_ClearData"))
{
sp.ExecuteNonQuery();
}
for (int i = 0; i < dt.Columns.Count; i++)
{
string mergeDate = "01/" + dt.Rows[6][i].ToString().Trim() + "/2008";
//mergeDate = mergeDate;
if (IsDateTime(mergeDate) == true)
{
if (IsDateTime("01/" + dt.Rows[6][i + 3].ToString().Trim() + "/2008") == true)
{
//MessageBox.Show(mergeDate);
MIndex iMonth = new MIndex();
if (Convert.ToDateTime(issueDate).Month < Convert.ToDateTime(mergeDate).Month)
{
if (Convert.ToDateTime(issueDate).Year < 2000)
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + (Convert.ToDateTime(issueDate).Year + 543).ToString();
}
else
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + Convert.ToDateTime(issueDate).Year.ToString();
}
iMonth.iTotal = i + 2;
}
else
{
if (Convert.ToDateTime(issueDate).Year < 2000)
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + (Convert.ToDateTime(issueDate).AddYears(1).Year + 543).ToString();;
}
else
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + Convert.ToDateTime(issueDate).AddYears(1).Year.ToString();;
}
iMonth.iTotal = i + 2;
}
itemHead.Add(iMonth);
}
else
{
if (dt.Rows[6][i + 3].ToString().Trim() == "DROP")
{
//MessageBox.Show("Finish");
break;
}
}
}
}
if (itemHead.Count > 0)
{
for (int j = 9; j < dt.Rows.Count; j++)
{
if (SetNumber(dt.Rows[j][5].ToString().Trim()) > 0)
{
//MessageBox.Show(dt.Rows[j][5].ToString().Trim());
for (int iHead = 0; iHead < itemHead.Count; iHead++)
{
MyCompany.Data.Objects.APCForcastMonthImport Order = new MyCompany.Data.Objects.APCForcastMonthImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = dt.Rows[j][6].ToString().Trim();
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
//Order.DeliveryDate = itemHead[iHead].iMonth;
Order.DeliveryDate = Convert.ToDateTime(itemHead[iHead].iMonth.ToString().Substring(7, 4) + "-" + itemHead[iHead].iMonth.ToString().Substring(3, 3) + "-" + itemHead[iHead].iMonth.ToString().Substring(0, 2));
Order.Quantity = SetNumber(dt.Rows[j][itemHead[iHead].iTotal].ToString().Trim().Replace(",", "")).ToString().Trim();
Order.Insert();
//MT05 result = new MT05();
//result.OrderBy = CustomerCode;
//result.DeliveryDesination = "";
//result.CustomerMatCode = dt.Rows[j][6].ToString().Trim();
//result.PartsDivision = "1";
//result.CustomerPONo = "Not sure";
//result.ReliabilityDivision = "P";
//result.DeliveryDate = itemHead[iHead].iMonth;
//result.Quantity = SetNumber(dt.Rows[j][itemHead[iHead].iTotal].ToString().Trim().Replace(",", ""));
//result.Unit = "ST";
//result.PIngPeriod = "D";
//Data.Add(result);
}
}
}
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("AAT_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// AAT
string CustCode = "40116011";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
DataTable dt = new DataTable();
var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", Path.GetDirectoryName(filename));
using (OleDbConnection con = new OleDbConnection(connString))
{
try
{
using (SqlProcedure sp = new SqlProcedure("sp_AAT_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
if (con.State != ConnectionState.Open)
{
con.Open();
}
var query = "SELECT * FROM [" + Path.GetFileName(filename) + "]";
using (var adapter = new OleDbDataAdapter(query, con))
{
adapter.Fill(dt);
if (dt.Columns.Count == 39)
{
bool boolStatusInsert = false;
foreach (DataRow _item in dt.Rows)
{
if (boolStatusInsert)
{
if (_item[38].ToString() == "W")
{
MyCompany.Data.Objects.AATOrderImport Order = new MyCompany.Data.Objects.AATOrderImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = _item[8].ToString();
Order.CustomerMatCode = _item[11].ToString();
string[] scpdTemp = SharedBusinessRules.getMaterial(_item[11].ToString(), CustCode, _item[8].ToString(), "AAT").Split(':');
Order.PartsDevision = scpdTemp[1];
Order.CustomerPO = _item[12].ToString();
Order.ReliabilityDevision = "F";
Order.DeliveryDate = Convert.ToDateTime(_item[35].ToString().Substring(0, 4).Trim() + "-" + _item[35].ToString().Substring(4, 2).Trim() + "-" + _item[35].ToString().Substring(6, 2).Trim());
Order.Quantity = _item[32].ToString();
Order.Unit = _item[33].ToString();
Order.PlngPeriod = "D";
Order.SAPCode = scpdTemp[0];
Order.Insert();
}
}
else
{
boolStatusInsert = true;
}
}
}
else if (dt.Columns.Count == 30)
{
bool boolStatusInsert = false;
foreach (DataRow _item in dt.Rows)
{
if (boolStatusInsert)
{
MyCompany.Data.Objects.AATOrderImport Order = new MyCompany.Data.Objects.AATOrderImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = _item[8].ToString();
Order.CustomerMatCode = _item[11].ToString();
string[] scpdTemp = SharedBusinessRules.getMaterial(_item[11].ToString(), CustCode, _item[8].ToString(), "AAT").Split(':');
Order.PartsDevision = scpdTemp[1];
Order.CustomerPO = _item[12].ToString();
Order.ReliabilityDevision = "F";
Order.DeliveryDate = Convert.ToDateTime(_item[28].ToString().Substring(0, 4).Trim() + "-" + _item[28].ToString().Substring(4, 2).Trim() + "-" + _item[28].ToString().Substring(6, 2).Trim());
Order.Quantity = _item[25].ToString();
Order.Unit = _item[26].ToString();
Order.PlngPeriod = "D";
Order.SAPCode = scpdTemp[0];
Order.Insert();
}
else
{
boolStatusInsert = true;
}
}
}
}
con.Close();
}
catch (Exception ex)
{
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("THM_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
string LocationCode = string.Empty;
// HATC Order
string CustCode = string.Empty;
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
//List<MyCompany.Models.HATCForcastImport> HATCList = new List<MyCompany.Models.HATCForcastImport>();
string CustMatNo = string.Empty, year = string.Empty, PlantCode = string.Empty;
Dictionary <int, string> dueDate = new Dictionary <int, string>();
Dictionary <int, double> orderQty = new Dictionary <int, double>();
int lineIndex = -1;
using (SqlProcedure sp = new SqlProcedure("sp_THM_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
string ReliabilityDevision = string.Empty;
while ((line = sr.ReadLine()) != null)
{
if (lineIndex >= 0)
{
lineIndex++;
}
switch (lineIndex)
{
case 3:
year = line.Substring(40, 4);
string[] ln = line.Split(' ');
LocationCode = ln[3];
break;
case 5:
try
{
dueDate.Clear();
orderQty.Clear();
// get Part and Delivery Date
CustMatNo = line.Substring(0, line.IndexOf("DATE")).Trim();
string[] data = line.Substring(line.IndexOf("DATE") + 4, (line.IndexOf("TOTAL") - line.IndexOf("DATE") - 4)).Trim().Split(' ');
int colIndex = 0;
for (int i = 0; i < data.Length; i++)
{
if (!string.IsNullOrEmpty(data[i].Trim()))
{
string[] _dueDate = data[i].Split('/');
if (_dueDate.Length > 0)
{
dueDate.Add(colIndex, String.Format("{0}-{1}-{2}", year, _dueDate[1], _dueDate[0]));
colIndex++;
}
}
}
}
catch (Exception ex)
{
}
break;
case 6: try
{
line = line.Substring(line.IndexOf("QTY.") + 4, line.Length - line.IndexOf("QTY.") - 10);
int colIndex = 0;
while (line.Length >= 7)
{
string qty = line.Substring(0, 7).Trim();
qty = String.IsNullOrEmpty(qty) ? "0" : qty;
orderQty.Add(colIndex, Convert.ToDouble(qty));
line = line.Substring(7, line.Length - 7);
colIndex++;
}
}
catch (Exception ex)
{
}
break;
case 11:
PlantCode = line.Substring(16, 17).Trim();
for (int i = 0; i < dueDate.Count; i++)
{
//[XXXX]03 => 40138011 => HATC-P
//[XXXX]xx => 40108011 => HATC
MyCompany.Models.THMForcastImport forcast = new MyCompany.Models.THMForcastImport();
forcast.OrderBy = "40101011";
forcast.DeliveryDestination = PlantCode + "-" + LocationCode;
forcast.CustomerMatCode = CustMatNo;
string[] materialTemp = SharedBusinessRules.getMaterial(forcast.CustomerMatCode, forcast.OrderBy, PlantCode, LocationCode).Split(':');
if (materialTemp.Length > 1)
{
forcast.SAPCode = materialTemp[0];
forcast.PartsDevision = materialTemp[1];
forcast.DeliveryDestinationCode = materialTemp[2];
}
forcast.CustomerPO = "";
forcast.ReliabilityDevision = ReliabilityDevision;
forcast.Unit = "ST";
forcast.PlngPeriod = "D";
forcast.DeliveryDate = Convert.ToDateTime(dueDate[i]);
forcast.Quantity = orderQty[i].ToString();
forcast.FileName = fn;
forcast.Insert();
}
break;
default:
break;
}
if (line.Trim().StartsWith("PART ORDER SCHEDULE (MAIL)") || line.Trim().StartsWith("PURCHASE SHEET (MAIL)"))
{
if (line.Trim().StartsWith("PART ORDER SCHEDULE (MAIL)"))
{
ReliabilityDevision = "F";
}
else if (line.Trim().StartsWith("PURCHASE SHEET (MAIL)"))
{
ReliabilityDevision = "P";
}
lineIndex = 0;
}
}
sr.Close();
}
}
//// Save file
//string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
//filename = String.Format("THM_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
//AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
//// HATC Order
//string CustCode = "40101010";
//filename = Server.MapPath("~/Files/") + filename;
//if (File.Exists(filename))
//{
// string line = string.Empty;
// using (StreamReader sr = new StreamReader(filename))
// {
// int rowsCount = 0;
// int i = 0;
// List<string> str = new List<string>();
// string CustPartNo = string.Empty, CustomerCode = string.Empty, Desination = string.Empty;
// using (SqlProcedure sp = new SqlProcedure("sp_THM_Forcast_ClearData"))
// {
// sp.ExecuteNonQuery();
// }
// while ((line = sr.ReadLine()) != null)
// {
// rowsCount++;
// if (rowsCount % 16 == 0)
// {
// //Get Customer code
// CustomerCode = str[2].Substring(5, 10).Trim();
// Desination = str[9].Substring(8, 10).Trim();
// //Get Customer Part No && Array Date
// //string[] arrStrLine6;
// //arrStrLine6 = str[5].Split(Convert.ToChar(" "));
// //int getRow5 = 0;
// List<MQtyOrder> QtyOfDate = new List<MQtyOrder>();
// //CustPartNo = str[5].Split(Convert.ToChar(" "));
// CustPartNo = str[5].Substring(0, 31);
// #region DateOfOrder
// if (str[5].Substring(33, 4) == "DATE")
// {
// //MessageBox.Show("Line OK");
// string Month = "";
// int startPostition = 0;
// int endPostition = 0;
// string flagPosition = "End";
// //string flagSavePosition = "No";
// //Merge stirng Date
// for (int iRow5 = 37; iRow5 < str[5].Length; iRow5++)
// {
// if (str[5].Substring(iRow5, 1) != " ")
// {
// if (flagPosition == "End")
// {
// startPostition = iRow5;
// flagPosition = "Start";
// }
// if (str[5].Substring(iRow5, 5) == "TOTAL")
// {
// //MessageBox.Show("End Line !!!");
// //return;
// break;
// }
// else
// {
// Month = Month + str[5].Substring(iRow5, 1);
// }
// }
// else
// {
// if (flagPosition == "Start")
// {
// //flagPosition = "End";
// //}
// flagPosition = "End";
// endPostition = iRow5;
// //flagSavePosition = "NO";
// }
// if (Month != "")
// {
// //Save Date
// MQtyOrder Qty = new MQtyOrder();
// Qty.OrderDate = Month;
// Qty.startPosition = startPostition;
// Qty.endPosition = endPostition;
// QtyOfDate.Add(Qty);
// //MessageBox.Show(Month + " : " + startPostition + "," + endPostition);
// Month = "";
// }
// }
// }
// #endregion
// #region QtyOfOrder
// if (str[6].Substring(33, 4) == "QTY.")
// {
// //for (int iRow6 = 37; iRow6 < str[6].Length; iRow6++)
// //{
// //}
// for (int iQty = 0; iQty < QtyOfDate.Count; iQty++)
// {
// //MQtyOrder Qty = new MQtyOrder();
// //Qty = QtyOfDate[iQty];
// //Qty.OrderQty =
// string Qty = str[6].Substring(QtyOfDate[iQty].startPosition, QtyOfDate[iQty].endPosition - QtyOfDate[iQty].startPosition).ToString().Trim();
// if (Qty == "")
// {
// QtyOfDate[iQty].OrderQty = 0;
// }
// else
// {
// QtyOfDate[iQty].OrderQty = Convert.ToInt32(Qty);
// }
// //QtyOfDate[iQty].OrderQty = str[6].Substring(QtyOfDate[iQty].startPosition, QtyOfDate[iQty].endPosition - QtyOfDate[iQty].startPosition);
// }
// }
// #endregion
// #region ShowData
// //List<MT05> Data = new List<MT05>();
// for (int iShow = 0; iShow < QtyOfDate.Count; iShow++)
// {
// MyCompany.Data.Objects.THMForcastImport Order = new MyCompany.Data.Objects.THMForcastImport();
// Order.OrderBy = CustomerCode;
// Order.DeliveryDestination = Desination;
// Order.CustomerMatCode = CustPartNo;
// Order.PartsDevision = "1";
// Order.CustomerPO = "Not sure";
// Order.ReliabilityDevision = "P";
// //Order.DeliveryDate = QtyOfDate[iShow].OrderDate;
// //Order.Quantity = Convert.ToDecimal( QtyOfDate[iShow].OrderQty);
// Order.Unit = "ST";
// Order.PlngPeriod = "D";
// Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
// Order.Insert();
// }
// #endregion
// str.Clear();
// //rowsCount = 1;
// }
// }
// else
// {
// str.Add(line);
// }
// }
// sr.Close();
// }
//}
}
//if (line.Trim().Length > 0)
// {
// string[] item = ParseCsvRow(line);
// //Head
// if (item.Length == 9)
// {
// if (IsDateTime(item[8]) == true)
// {
// IssueDate = Convert.ToDateTime(item[8].Trim());
// //MessageBox.Show(item[8]);
// for (int iMonth = 2; iMonth < item.Length - 1; iMonth++)
// {
// //List<String> itemHead = new List<string>();
// int month = DateTime.Parse("1/" + item[iMonth] + "/2008").Month;
// if (month < IssueDate.Month)
// {
// itemHead.Add("01/" + month.ToString("0#") + "/" + (Convert.ToInt32(item[8].Trim().Substring(6)) + 1).ToString().Trim());
// }
// else
// {
// itemHead.Add("01/" + month.ToString("0#") + item[8].Substring(6).Trim());
// }
// //MessageBox.Show(month.ToString());
// //itemHead
// }
// }
// //itemHead = item;
// }
// //Detail
// if (item.Length == 8)
// {
// for (int iMonth = 0; iMonth < itemHead.Count; iMonth++)
// {
// MyCompany.Data.Objects.TSMForcastMonthImport Order = new MyCompany.Data.Objects.TSMForcastMonthImport();
// Order.OrderBy = CustCode;
// Order.DeliveryDestination = "";
// Order.CustomerMatCode = item[0];
// Order.PartsDevision = "1";
// Order.CustomerPO = "";
// Order.ReliabilityDevision = "P";
// Order.DeliveryDate = itemHead[iMonth];
// Order.Quantity = item[iMonth + 2];
// Order.Unit = "ST";
// Order.PlngPeriod = "D";
// Order.SAPCode = SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
// Order.Insert();
// //MT05 result = new MT05();
// //result.OrderBy = CustomerCode;
// //result.DeliveryDesination = CustDesination;
// //result.CustomerMatCode = item[0];
// //result.PartsDivision = "1";
// //result.CustomerPONo = "Not sure";
// //result.ReliabilityDivision = "P";
// //result.DeliveryDate = itemHead[iMonth];
// //result.Quantity = Convert.ToInt32(item[iMonth + 2]);
// //result.Unit = "ST";
// //result.PIngPeriod = "D";
// //Data.Add(result);
// }
// }
// }
#endregion
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("TSM_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40102010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
string CustPartNo = "";
string CustDesination = "";
bool flagGetPostion = false;
DateTime IssueDate;
List <MQtyOrder> colDate = new List <MQtyOrder>();
List <String> itemHead = new List <string>();
using (SqlProcedure sp = new SqlProcedure("sp_TSM_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
if (!line.Contains("ROKI") && !line.Contains("PKTXT") && !line.Contains("DEL"))
{
MyCompany.Data.Objects.TSMOrderImport Order = new MyCompany.Data.Objects.TSMOrderImport();
string[] item = ParseCsvRow(line);
Order.OrderBy = CustCode;
Order.DeliveryDestination = item[2].Trim();
Order.CustomerMatCode = item[3].Trim();
Order.PartsDevision = "1";
Order.CustomerPO = item[7].Trim();
Order.ReliabilityDevision = "P";
string[] spritDate = item[10].Trim().Split(Convert.ToChar("/"));
if (spritDate.Length == 3)
{
Order.DeliveryDate = Convert.ToDateTime(DateTime.Today.Year.ToString().Substring(0, 2) + spritDate[2] + "-" + Convert.ToInt32(spritDate[1]).ToString("0#") + "-" + spritDate[0]);
}
else
{
Order.DeliveryDate = null;
}
//Order.DeliveryDate = item[10];
Order.Quantity = float.Parse(item[9].Trim()).ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
}
sr.Close();
}
}
}
internal static void UpdateStream(BlobStorageContext context, Stream stream)
{
var fileId = context.FileId;
var fileStreamData = ((SqlFileStreamBlobProviderData)context.BlobProviderData).FileStreamData;
SqlProcedure cmd = null;
try
{
// if possible, write the stream using the special Filestream technology
if (UseFileStream(context.Provider, stream.Length))
{
WriteSqlFileStream(stream, fileId, fileStreamData);
return;
}
// We have to work with an integer since SQL does not support
// binary values bigger than [Int32.MaxValue].
var streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_WriteStream"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = fileId;
var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
cmd.Parameters.Add("@UseFileStream", SqlDbType.TinyInt).Value = false;
var offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
// The 'while' loop is misleading here, because we write the whole
// stream at once. Bigger files should go to the Filestream
// column anyway.
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
var bufferSize = streamSize - offset;
if (buffer == null || buffer.Length != bufferSize)
{
buffer = new byte[bufferSize];
}
// Read bytes from the source
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
// Write full stream
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
cmd?.Dispose();
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("TYM_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40103010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_TYM_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
MyCompany.Data.Objects.TYMOrderImport Order = new MyCompany.Data.Objects.TYMOrderImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = line.Substring(0, 3) + "-" + line.Substring(3, 5) + "-" + line.Substring(8, 2) + "-" + line.Substring(10, 2) + "-" + line.Substring(12, 2) + "-" + line.Substring(14, 4) + "-" + line.Substring(18, 4);
Order.PartsDevision = "1A";
Order.CustomerPO = line.Substring(30, 5);
Order.ReliabilityDevision = "P";
Order.DeliveryDate = Convert.ToDateTime(line.Substring(22, 4) + "-" + line.Substring(26, 2) + "-" + line.Substring(28, 2));
Order.Quantity = float.Parse(line.Substring(39, 4)).ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
sr.Close();
}
}
//// Forcast
//string CustomerCode = "";
//string Desination = "";
////BindDataT05(CustomerCode, "HATC", "ODB");
//List<MT05> Data = new List<MT05>();
//if (openFileDialog1.ShowDialog() == DialogResult.OK)
//{
// string path = openFileDialog1.FileName.ToString().Trim();
// ////Import File to Datatable
// string line;
// // Read the file and display it line by line.
// System.IO.StreamReader file =
// new System.IO.StreamReader(path);
// try
// {
// int rowsCount = 0;
// int i = 0;
// List<string> str = new List<string>();
// string CustPartNo = "";
// while ((line = file.ReadLine()) != null)
// {
// rowsCount++;
// MT05 T05 = new MT05();
// if (rowsCount % 16 == 0)
// {
// //Get Customer code
// CustomerCode = str[2].Substring(5, 10).Trim();
// Desination = str[9].Substring(8, 10).Trim();
// //Get Customer Part No && Array Date
// //string[] arrStrLine6;
// //arrStrLine6 = str[5].Split(Convert.ToChar(" "));
// //int getRow5 = 0;
// List<MQtyOrder> QtyOfDate = new List<MQtyOrder>();
// //CustPartNo = str[5].Split(Convert.ToChar(" "));
// CustPartNo = str[5].Substring(0, 31);
// #region DateOfOrder
// if (str[5].Substring(33, 4) == "DATE")
// {
// //MessageBox.Show("Line OK");
// string Month = "";
// int startPostition = 0;
// int endPostition = 0;
// string flagPosition = "End";
// //string flagSavePosition = "No";
// //Merge stirng Date
// for (int iRow5 = 37; iRow5 < str[5].Length; iRow5++)
// {
// if (str[5].Substring(iRow5, 1) != " ")
// {
// if (flagPosition == "End")
// {
// startPostition = iRow5;
// flagPosition = "Start";
// }
// if (str[5].Substring(iRow5, 5) == "TOTAL")
// {
// //MessageBox.Show("End Line !!!");
// //return;
// break;
// }
// else
// {
// Month = Month + str[5].Substring(iRow5, 1);
// }
// }
// else
// {
// if (flagPosition == "Start")
// {
// //flagPosition = "End";
// //}
// flagPosition = "End";
// endPostition = iRow5;
// //flagSavePosition = "NO";
// }
// if (Month != "")
// {
// //Save Date
// MQtyOrder Qty = new MQtyOrder();
// Qty.OrderDate = Month;
// Qty.startPosition = startPostition;
// Qty.endPosition = endPostition;
// QtyOfDate.Add(Qty);
// //MessageBox.Show(Month + " : " + startPostition + "," + endPostition);
// Month = "";
// }
// }
// }
// #endregion
// #region QtyOfOrder
// if (str[6].Substring(33, 4) == "QTY.")
// {
// //for (int iRow6 = 37; iRow6 < str[6].Length; iRow6++)
// //{
// //}
// for (int iQty = 0; iQty < QtyOfDate.Count; iQty++)
// {
// //MQtyOrder Qty = new MQtyOrder();
// //Qty = QtyOfDate[iQty];
// //Qty.OrderQty =
// string Qty = str[6].Substring(QtyOfDate[iQty].startPosition, QtyOfDate[iQty].endPosition - QtyOfDate[iQty].startPosition).ToString().Trim();
// if (Qty == "")
// {
// QtyOfDate[iQty].OrderQty = 0;
// }
// else
// {
// QtyOfDate[iQty].OrderQty = Convert.ToInt32(Qty);
// }
// //QtyOfDate[iQty].OrderQty = str[6].Substring(QtyOfDate[iQty].startPosition, QtyOfDate[iQty].endPosition - QtyOfDate[iQty].startPosition);
// }
// }
// #endregion
// #region ShowData
// //List<MT05> Data = new List<MT05>();
// for (int iShow = 0; iShow < QtyOfDate.Count; iShow++)
// {
// MT05 result = new MT05();
// result.OrderBy = CustomerCode;
// result.DeliveryDesination = Desination;
// result.CustomerMatCode = CustPartNo;
// result.PartsDivision = "1";
// result.CustomerPONo = "Not sure";
// result.ReliabilityDivision = "P";
// result.DeliveryDate = QtyOfDate[iShow].OrderDate;
// result.Quantity = QtyOfDate[iShow].OrderQty;
// result.Unit = "ST";
// result.PIngPeriod = "D";
// Data.Add(result);
// }
// #endregion
// str.Clear();
// //rowsCount = 1;
// }
// }
// else
// {
// str.Add(line);
// }
// }
// dgvData.DataSource = Data;
// }
// catch (Exception)
// {
// throw;
// }
//}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("MMTH_{0}_FORVAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_MMTH_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
string[] arr = line.Split('|');
if (!arr[4].Contains("9999999999999999"))
{
string plantCode = arr[2];
string yyyy = arr[3].Substring(0, 4);
string MM = arr[3].Substring(4, 2);
string partNo = arr[4];
string dayOld = arr[11];
for (int d = 12; d <= arr.Length - 1; d += 2)
{
if (arr[d - 1] == "00")
{
break;
}
if (Convert.ToInt32(dayOld) > Convert.ToInt32(arr[d - 1]))
{
DateTime dtTemp = Convert.ToDateTime(yyyy + "-" + MM + "-01");
dtTemp = dtTemp.AddMonths(1);
MM = dtTemp.Month.ToString();
yyyy = dtTemp.Year.ToString();
}
MyCompany.Models.MMTHForcastImport forcast = new MyCompany.Models.MMTHForcastImport();
forcast.OrderBy = "40113011";
forcast.DeliveryDestination = plantCode;
forcast.CustomerMatCode = partNo.Trim();
string[] materialTemp = SharedBusinessRules.getMaterial(forcast.CustomerMatCode, "40113011", plantCode, "MMTh").Split(':');
if (materialTemp.Length > 1)
{
forcast.SAPCode = materialTemp[0];
forcast.PartsDevision = materialTemp[1];
forcast.DeliveryDestinationCode = materialTemp[2];
}
forcast.CustomerPO = "";
forcast.ReliabilityDevision = "F";
forcast.Unit = "ST";
forcast.PlngPeriod = "D";
forcast.DeliveryDate = Convert.ToDateTime(yyyy + "-" + MM + "-" + arr[d - 1]);
forcast.Quantity = Convert.ToInt32(arr[d]);
forcast.Insert();
dayOld = arr[d - 1];
}
}
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("KYB_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40120010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
//MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\ROKI\WorkEDI\EDI DATA_20170301\SD\KMT\Forecast_KMT.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1;");
//MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties='Excel 12.0;HDR=YES;'");
MyConnection = new System.Data.OleDb.OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0;data source="
+ filename
+ ";Extended Properties=Excel 8.0;");
//provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\Programming\\Spreadsheet-Current.xlsx';Extended Properties='Excel 12.0;HDR=YES;'
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "select * from [Sheet1$]";
myCommand.CommandText = sql;
//OleDbDataReader reader = myCommand.ExecuteReader();
//while (reader.Read())
//{
// var val1 = reader[0].ToString();
//}
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
DataTable dt = new DataTable();
da.Fill(dt);
MyConnection.Close();
if (dt.Rows.Count > 0)
{
using (SqlProcedure sp = new SqlProcedure("sp_KYB_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][2].ToString().Trim() != "")
{
MyCompany.Data.Objects.KYBForcastImport Order = new MyCompany.Data.Objects.KYBForcastImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = dt.Rows[i][2].ToString().Trim();
Order.PartsDevision = "1";
Order.CustomerPO = "";//dt.Rows[i][7].ToString().Trim();
Order.ReliabilityDevision = "P";
string[] spritDate = dt.Rows[i][6].ToString().Trim().Split(Convert.ToChar("/"));
if (spritDate.Length == 3)
{
Order.DeliveryDate = Convert.ToDateTime(spritDate[2] + "-" + Convert.ToInt32(spritDate[1]).ToString("0#") + "-" + spritDate[0]);
}
else
{
Order.DeliveryDate = null;
}
//Order.DeliveryDate = dt.Rows[i][6].ToString().Trim();
Order.Quantity = dt.Rows[i][5].ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40138011";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
string CustPartNo = "";
string CustDesination = "";
bool flagGetPostion = false;
DateTime IssueDate;
List <MQtyOrder> colDate = new List <MQtyOrder>();
List <String> itemHead = new List <string>();
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_TSM_ForcastMonth_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Trim().Length > 0)
{
string[] item = ParseCsvRow(line);
//Head
if (item.Length == 9)
{
if (IsDateTime(item[8]) == true)
{
IssueDate = Convert.ToDateTime(item[8].Trim());
//MessageBox.Show(item[8]);
for (int iMonth = 2; iMonth < item.Length - 1; iMonth++)
{
//List<String> itemHead = new List<string>();
int month = DateTime.Parse("1/" + item[iMonth] + "/2008").Month;
if (month < IssueDate.Month)
{
itemHead.Add("01/" + month.ToString("0#") + "/" + (Convert.ToInt32(item[8].Trim().Substring(6)) + 1).ToString().Trim());
}
else
{
itemHead.Add("01/" + month.ToString("0#") + item[8].Substring(6).Trim());
}
//MessageBox.Show(month.ToString());
//itemHead
}
}
//itemHead = item;
}
//Detail
if (item.Length == 8)
{
for (int iMonth = 0; iMonth < itemHead.Count; iMonth++)
{
MyCompany.Data.Objects.TSMForcastMonthImport Order = new MyCompany.Data.Objects.TSMForcastMonthImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = item[0];
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
//Order.DeliveryDate = itemHead[iMonth];
string[] spritDate = itemHead[iMonth].Split(Convert.ToChar("/"));
if (spritDate.Length == 3)
{
Order.DeliveryDate = Convert.ToDateTime(DateTime.Now.Year.ToString().Substring(0, 2) + spritDate[2] + "-" + Convert.ToInt32(spritDate[1]).ToString("0#") + "-" + spritDate[0]);
}
else
{
Order.DeliveryDate = null;
}
Order.Quantity = item[iMonth + 2];
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
//MT05 result = new MT05();
//result.OrderBy = CustomerCode;
//result.DeliveryDesination = CustDesination;
//result.CustomerMatCode = item[0];
//result.PartsDivision = "1";
//result.CustomerPONo = "Not sure";
//result.ReliabilityDivision = "P";
//result.DeliveryDate = itemHead[iMonth];
//result.Quantity = Convert.ToInt32(item[iMonth + 2]);
//result.Unit = "ST";
//result.PIngPeriod = "D";
//Data.Add(result);
}
}
}
}
}
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
string fn = filename;
filename = String.Format("MMTH_{0}_ORDER_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40113011";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_MMTH_Order_ClearData"))
{
sp.ExecuteNonQuery();
}
int i = 1;
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
if (line.Contains("T099") && !line.Contains("9999999999"))
{
string[] item = line.Split(Convert.ToChar("|"));
MyCompany.Models.MMTHOrderImport Order = new MyCompany.Models.MMTHOrderImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = item[2];
Order.CustomerMatCode = item[7].Trim();
string[] materialTemp = SharedBusinessRules.getMaterial(item[7].Trim(), CustCode, item[2], "MMTh").Split(':');
if (materialTemp.Length > 1)
{
Order.SAPCode = materialTemp[0];
Order.PartsDevision = materialTemp[1];
Order.DeliveryDestinationCode = materialTemp[2];
Order.Key1 = materialTemp[3];
Order.Key2 = materialTemp[4];
Order.Key3 = materialTemp[5];
}
Order.CustomerPO = item[4].Trim();
Order.ReliabilityDevision = "P";
Order.DeliveryDate = Convert.ToDateTime(item[5].Substring(0, 4).Trim() + "-" + item[5].Substring(5, 2).Trim() + "-" + item[5].Substring(8, 2).Trim());
Order.Quantity = float.Parse(item[8]).ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.AddCode = item[3] + ":" + item[6] + ":" + item[11] + ": :" + item[26] + ":" + i.ToString();
Order.PlantCode = item[10];
Order.Arrivaltime = "00:00:00";
Order.FromTo = item[1] + ":PLANT-" + item[2];
Order.Filename = fn;
Order.Insert();
i++;
}
}
}
sr.Close();
}
}
}