private Task <List <PenjualanOfMonth> > GetPenjualanOfMonth(int tahun)
{
var list = new List <ModelsShared.Models.PenjualanOfMonth>();
using (var db = new OcphDbContext())
{
var sp = string.Format("PenjualanOfamount");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
for (int i = 1; i <= 12; i++)
{
var date = new DateTime(tahun, i, 1);
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("Tanggal", date));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PenjualanOfMonth));
var result = new MappingColumn(ent).MappingWithoutInclud <PenjualanOfMonth>(dr);
if (result.Count > 0)
{
var item = result.FirstOrDefault();
list.Add(item);
}
cmd.Parameters.RemoveAt("Tanggal");
dr.Close();
}
}
return(Task.FromResult(list));
}
public Task <List <PackingListPrintModel> > GetPackingList(int ManifestId)
{
using (var db = new OcphDbContext())
{
try
{
var sp = string.Format("PackingList");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ManifestId", ManifestId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PackingListPrintModel));
var list = new MappingColumn(ent).MappingWithoutInclud <PackingListPrintModel>(dr);
dr.Close();
return(Task.FromResult(list));
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public async Task <List <PenjualanOfYear> > GetPenjualanThreeYear()
{
var list = new List <ModelsShared.Models.PenjualanOfYear>();
using (var db = new OcphDbContext())
{
var thisyear = DateTime.Now.Year;
var sp = string.Format("PenjualanOfaYear");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
for (int i = thisyear; i > thisyear - 3; i--)
{
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("tahun", i));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PenjualanOfYear));
list = new MappingColumn(ent).MappingWithoutInclud <PenjualanOfYear>(dr);
if (list.Count > 0)
{
var item = list.FirstOrDefault();
item.Months = await GetPenjualanOfMonth(item.Tahun);
list.Add(item);
}
cmd.Parameters.RemoveAt("tahun");
dr.Close();
}
}
return(list);
}
private static string GetImportValueOf(MappingColumn column, ProcessedImportRow processedImportRow, GeneratorDelegates generatorDelegates)
{
string value = processedImportRow.Row[column.TemplateColumnOrdinal] == DBNull.Value ? "" : processedImportRow.Row[column.TemplateColumnOrdinal].ToString().Trim();
switch (column.LookupType)
{
case LookupType.None:
return(value
.ValidateAgainstDbSetting(column, processedImportRow)
.WithSqlSyntax(column.Datatype));
case LookupType.Table:
case LookupType.Unit:
return(column.LookupValues
.ReplacementFor(column, value.ToUpperInvariant(), processedImportRow)
.ValidateAgainstDbSetting(column, processedImportRow)
.WithSqlSyntax(column.Datatype));
case LookupType.Tag:
return(generatorDelegates.ReplacementForTag(column, processedImportRow)
.ValidateAgainstDbSetting(column, processedImportRow)
.WithSqlSyntax(column.Datatype));
default:
throw new ArgumentException("Unknown lookup type");
}
}
public Task <titipankapal> GetTitipanKapal(int ManifestId)
{
using (var db = new OcphDbContext())
{
try
{
var sp = string.Format("TitipanKapal");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ManifestId", ManifestId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.titipankapal));
var list = new MappingColumn(ent).MappingWithoutInclud <titipankapal>(dr);
dr.Close();
titipankapal titip = null;
if (list.Count > 0)
{
titip = list.FirstOrDefault();
titip.Jumlah = list.Count;
}
return(Task.FromResult(titip));
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public IQueryable <T> Select(Expression <Func <T, bool> > expression)
{
List <T> list = new List <T>();
StringBuilder sb = new StringBuilder();
sb.Append("(Select * From ").Append(Entity.TableName).Append(" Where ");
sb.Append(new WhereTranslator().Translate(expression));
sb.Append(")");
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sb.ToString();
IDataReader dr = null;
try
{
dr = cmd.ExecuteReader() as MySqlDataReader;
var map = new MappingColumn(Entity);
list = map.MappingWithoutInclud <T>(dr);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
dr.Close();
}
return(list.AsQueryable());
}
public IQueryable <T> SelectAll()
{
List <T> list = new List <T>();
StringBuilder sb = new StringBuilder();
sb.Append("Select * From ").Append(Entity.TableName);
IDataReader dr = null;
try
{
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sb.ToString();
dr = cmd.ExecuteReader();
list = new MappingColumn(Entity).MappingWithoutInclud <T>(dr);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
dr.Close();
}
return(list.AsQueryable <T>());
}
public IQueryable <T> ExecuteStoreProcedureQuery(string storeProcedure)
{
List <T> list = new List <T>();
IDataReader dr = null;
try
{
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CallEmployee";
dr = cmd.ExecuteReader();
list = new MappingColumn(Entity).MappingWithoutInclud <T>(dr);
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(list.AsQueryable <T>());
}
public object GetLastItem()
{
List <T> list = new List <T>();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select * From " + Entity.TableName + " Order By " + Entity.GetAttributPrimaryKeyName() + " Desc Limit 1";
IDataReader dr = null;
try
{
dr = cmd.ExecuteReader() as MySqlDataReader;
list = new MappingColumn(Entity).MappingWithoutInclud <T>(dr);
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(list.FirstOrDefault());
}
public IQueryable <T> Select(Expression <Func <T, dynamic> > expression)
{
EntityInfo entity = new EntityInfo(typeof(T));
List <T> list = new List <T>();
StringBuilder sb = new StringBuilder();
var job = new CollectPropertyFromExpression().Translate(expression);
sb.Append("Select ");
if (job.Count < 1)
{
sb.Append(" * ");
}
else
{
int count = job.Count;
for (int i = 0; i < count; i++)
{
var att = entity.GetAttributDbColumn(job[i]);
sb.Append(string.Format("{0}", att));
if (i < count)
{
sb.Append(", ");
}
}
}
string temp = sb.ToString();
sb.Clear();
temp = temp.Substring(0, temp.Length - 2);
sb.Append(temp + " ");
sb.Append(" From ").Append(Entity.TableName);
// sb.Append(new WhereTranslator().Translate(expression));
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sb.ToString();
IDataReader dr = null;
try
{
dr = cmd.ExecuteReader() as MySqlDataReader;
var map = new MappingColumn(Entity);
list = map.MappingWithoutInclud <T>(dr);
}
catch (MySqlException ex)
{
throw new SystemException(ex.Message);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(list.AsQueryable());
}
public IQueryable <T> Includ(IQueryable <T> query, Expression <Func <T, dynamic> > expression)
{
var job = new CollectPropertyFromExpression().Translate(expression);
foreach (T Item in query)
{
foreach (PropertyInfo propertyJOb in job)
{
EntityInfo entityChild = null;
if (propertyJOb.PropertyType.GenericTypeArguments.Count() > 0)
{
entityChild = new EntityInfo(propertyJOb.PropertyType.GenericTypeArguments[0]);
string vsql = new InsertQuery(Entity).GetChildInsertQuery(propertyJOb, Item, entityChild);
if (vsql != string.Empty)
{
IDataReader dr = null;
try
{
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = vsql;
dr = cmd.ExecuteReader();
var propertyproduct = Entity.GetPropertyByPropertyName(propertyJOb.Name);
IList list = (IList)Activator.CreateInstance(typeof(List <>).MakeGenericType(entityChild.GetEntityType()));
var map = new MappingColumn(Entity);
var resultMapping = (IList)map.MappingWithoutInclud(dr, entityChild.GetEntityType());
foreach (var item in resultMapping)
{
list.Add(item);
}
propertyproduct.SetValue(Item, list, null);
}
catch (Exception ex)
{
throw new System.Exception(ex.Message);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
}
}
else
{
entityChild = new EntityInfo(propertyJOb.ReflectedType);
}
}
}
return(query);
}
public static int?ColumnOrdinalOf(this MappingTable mappingTable, string columnName)
{
MappingColumn column = mappingTable.MappingColumns.Where(c => c.StarchefColumnName.ToUpperInvariant() == columnName.ToUpperInvariant()).FirstOrDefault();
if (column != null)
{
return(column.TemplateColumnOrdinal);
}
return(null);
}
public static int?ColumnOrdinalOf(this IList <MappingTable> mappingTables, string tableName, string columnName)
{
MappingColumn column = mappingTables.Where(t => t.TableName.ToUpperInvariant() == (tableName != null ? tableName.ToUpperInvariant() : string.Empty))
.Select(mappingTable => mappingTable.MappingColumns.Where(i => i.StarchefColumnName.ToUpperInvariant() == columnName.ToUpperInvariant()).FirstOrDefault()).FirstOrDefault();
if (column != null)
{
return(column.TemplateColumnOrdinal);
}
return(null);
}
public static string TagDelegateTwoLevel(MappingColumn column, ProcessedImportRow processedImportRow)
{
string mainCategory = processedImportRow.Row[column.TemplateColumnOrdinal - 1] == DBNull.Value ? "" : processedImportRow.Row[column.TemplateColumnOrdinal - 1].ToString().Trim();
string subCategory = processedImportRow.Row[column.TemplateColumnOrdinal] == DBNull.Value ? "" : processedImportRow.Row[column.TemplateColumnOrdinal].ToString().Trim();
//main category and all sub category is empty, we can continue with the import without categories
if (string.IsNullOrWhiteSpace(mainCategory) && string.IsNullOrWhiteSpace(subCategory))
{
column.IgnoreThisTable = true;
return("");
}
//only main Category is available
if (!string.IsNullOrWhiteSpace(mainCategory) && string.IsNullOrWhiteSpace(subCategory))
{
LookupValue categoryLookup = column.LookupValues.Where(tag => tag.Lookup == mainCategory.ToUpperInvariant()).FirstOrDefault();
if (categoryLookup != null)
{
return(categoryLookup.ReplacementId.ToString());
}
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
//We have main category and subcategory level 2
if (!string.IsNullOrWhiteSpace(mainCategory) && !string.IsNullOrWhiteSpace(subCategory))
{
string category = string.Format("{0}||{1}", mainCategory, subCategory).ToUpperInvariant();
LookupValue categoryLookup = column.LookupValues.Where(tag => tag.Lookup == category).FirstOrDefault();
if (categoryLookup != null)
{
return(categoryLookup.ReplacementId.ToString());
}
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
//All other conditions are invalid hierarchy
processedImportRow.AddException(new ImportDataException {
ExceptionType = ExceptionType.CategoryIsNotValid, TemplateMappingColumn = column.TemplateMappingColumn, TemplateColumnName = column.TemplateColumnName, IsValid = false
});
return("");
}
public Task <List <invoice> > GetInvoiceNotYetPaid()
{
using (var db = new OcphDbContext())
{
var sp = string.Format("InvoiceNotYetPaid");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.invoice));
var list = new MappingColumn(ent).MappingWithoutInclud <invoice>(dr);
dr.Close();
return(Task.FromResult(list));
}
}
public Task <List <invoice> > GetInvoiceJatuhTempo()
{
using (var db = new OcphDbContext())
{
var sp = string.Format("InvoiceJatuhTempo");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("Tanggal", DateTime.Now));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.invoice));
var list = new MappingColumn(ent).MappingWithoutInclud <invoice>(dr);
dr.Close();
return(Task.FromResult(list));
}
}
public static string ReplacementFor(this HashSet<LookupValue> lookupValues, MappingColumn column, string importValue,ProcessedImportRow processedImportRow)
{
if (string.IsNullOrWhiteSpace(importValue))
return "";
foreach (LookupValue lookupValue in lookupValues)
{
if (lookupValue.Lookup == importValue)
return lookupValue.ReplacementId.ToString();
}
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.LookupMissing,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
return "";
}
public TracingModel GetPenjualan(int STT)
{
using (var db = new OcphDbContext())
{
try
{
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "GetPenjualan";
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("STT", STT));
var reader = cmd.ExecuteReader();
var mapp = new MappingColumn(new EntityInfo(typeof(TracingModel)));
var result = mapp.MappingWithoutInclud <TracingModel>(reader);
reader.Close();
var tracing = result.FirstOrDefault();
if (result != null)
{
var packlists = db.PackingLists.Where(O => O.PenjualanId == tracing.Id).GroupBy(O => O.ManifestID).ToList();
tracing.Manifests = new List <manifestoutgoing>();
foreach (var item in packlists)
{
var manifest = db.Outgoing.Where(O => O.Id == item.Key).FirstOrDefault();
if (manifest != null)
{
tracing.Manifests.Add(manifest);
}
}
return(tracing);
}
else
{
throw new SystemException(MessageCollection.Message(MessageType.NotFound));
}
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public IQueryable <T> Select(Expression <Func <T, bool> > expression)
{
List <T> list = new List <T>();
StringBuilder sb = new StringBuilder();
sb.Append("Select * From ").Append(Entity.TableName).Append(" Where ");
sb.Append(new WhereTranslator().Translate(expression));
IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sb.ToString();
IDataReader dr = null;
try
{
dr = cmd.ExecuteReader();
if (dr != null)
{
var mapping = new MappingColumn(Entity);
mapping.ReaderSchema = this.ReadColumnInfo(dr.GetSchemaTable());
list = mapping.MappingWithoutInclud <T>(dr);
}
else
{
throw new SystemException("Data Tidak Ditemukan");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
dr.Close();
}
return(list.AsQueryable());
}
public IEnumerable <manifestoutgoing> ManifestsByPenjualanId(int penjualanId)
{
try
{
using (var db = new OcphDbContext())
{
var sp = string.Format("GetManifestsByPenjualan");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("penjualanId", penjualanId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.manifestoutgoing));
var list = new MappingColumn(ent).MappingWithoutInclud <manifestoutgoing>(dr);
dr.Close();
return(list);
}
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
public static string MappingNameOf(this MappingTable mappingTable, string columnName)
{
MappingColumn column = mappingTable.MappingColumns.Where(c => c.StarchefColumnName.ToUpperInvariant() == columnName.ToUpperInvariant()).FirstOrDefault();
return(column != null ? column.TemplateMappingColumn : null);
}
public static string ValidateAgainstDbSetting(this string importValue, MappingColumn column, ProcessedImportRow processedImportRow)
{
if (column.ValidationRules != null)
{
foreach (var rule in column.ValidationRules)
{
if (string.IsNullOrEmpty(importValue) && (rule.Mandatory || column.Mandatory))
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = column.StarchefColumnName == "tag_id" ? ExceptionType.CategoryRequired : ExceptionType.DbSettingIsMandatory,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn
});
}
if (rule.StringLength < int.MaxValue && rule.StringLength > 0 && importValue.Length > rule.StringLength)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMaxLength,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.StringLength.ToString()
});
}
if (rule.MinimumValue != rule.MaximumValue && !string.IsNullOrEmpty(importValue))
{
//TODO:candidate for refactoring, perhaps the whole class
decimal value;
if (decimal.TryParse(importValue, out value))
{
if (value < rule.MinimumValue)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMinimumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
if (value > rule.MaximumValue)
{
processedImportRow.AddException(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMaximumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
}
else
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingMinimumValue,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.MinimumValue.ToString(),
MessageParam2 = rule.MaximumValue.ToString()
});
}
}
if (!string.IsNullOrEmpty(rule.RegEx))
{
Regex regex = new Regex(rule.RegEx);
if (!regex.Match(importValue).Success)
{
processedImportRow.AddException
(new ImportDataException
{
ExceptionType = ExceptionType.DbSettingRegEx,
TemplateColumnName = column.TemplateColumnName,
IsValid = false,
TemplateMappingColumn = column.TemplateMappingColumn,
MessageParam1 = rule.RegEx,
});
}
}
}
}
return(importValue);
}
