public object Edit(SheetRow _Row)
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
ValueRange v = new ValueRange();
v.Range = "Data!A" + _Row.RowIndex;
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedColumns,totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
private Boolean _loadLocaleFile(LanguageName _lang, String _file_name)
{
//StringBuilder string_builder = new StringBuilder( 128 );
Utility.SheetLite.SheetReader reader = new Utility.SheetLite.SheetReader();
if (!reader.OpenSheet("language", String.Format("{0}/{1}", _lang, _file_name)))
{
Log.Error("[Locale]语言包文件: {0} 加载失败!", _file_name);
return(false);
}
for (Int32 i = 0; i < reader.Count; ++i)
{
SheetRow row = reader[i];
String key = row["Key"];
String category = row["Category"];
if (!String.IsNullOrEmpty(category))
{
key = String.Format("{0}@{1}", category, key);
}
if (m_locale.ContainsKey(key))
{
Utility.Log.Error("[Loclae]:语言包中包含重复键值：{0} {1}", _lang, key);
continue;
}
String value = row["Value"];
// 颜色值转换
//string_builder.Length = 0;
//Int32 pos = 0;
//while( pos < value.Length )
//{
// if( '[' == value[pos] )
// {
// if( UnityEngine.UI.Text.ProcessColor( value, pos, string_builder, out pos ) )
// continue;
// }
// string_builder.Append( value[pos++] );
//}
//value = string_builder.ToString().Trim();
m_locale.Add(key, value);
}
return(true);
}
public static void LoadCsvConfigWithStructKey <Tkey, Tvalue>(string _name, Dictionary <Tkey, Tvalue> _dic) where Tkey : struct where Tvalue : new()
{
ResFramework.ResManager.Instance.LoadAsset(string.Format("Assets/Res/Config/{0}.txt", _name),
(_data, _object) =>
{
Utility.SheetLite.SheetReader reader = new Utility.SheetLite.SheetReader();
TextAsset text = _object as TextAsset;
if (!reader.OpenSheet(_name, text.bytes))
{
return;
}
for (int i = 0; i < reader.Count; ++i)
{
SheetRow row = reader[i];
Type type = typeof(Tvalue);
Tkey new_key = (Tkey)ConverFieldValue(reader.Sheet.GetKeyTypeString(0), row[0]);
Tvalue new_value = new Tvalue();
for (int j = 0; j < row.Count; ++j)
{
if (string.IsNullOrEmpty(row[j]))
{
continue;
}
string key = reader.Sheet.GetKeyString(j);
string key_type = reader.Sheet.GetKeyTypeString(j);
try
{
FieldInfo Property = type.GetField(key);
Property.SetValue(new_value, ConverFieldValue(key_type, row[j]));
}
catch (Exception e)
{
Debug.LogError(string.Format("表头 {0} 不是{1}类型 检查类 {2} 对应的配置 !!!{3}", key, key_type, type.Name, e.Message));
}
}
_dic.Add(new_key, new_value);
}
if (_data != null)
{
_data.Unload();
}
}, false);
}
public object Create(SheetRow _Row)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = WebApiApplication.credential,
ApplicationName = WebApiApplication.ApplicationName
});
// Define request parameters.
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, "Data!A:B");
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
ValueRange v = new ValueRange();
v.Range = "Data!A" + (values.Count + 1) + ":Z" + (values.Count + 1);
IList <IList <object> > ret = new List <IList <object> >();
IList <object> row = new List <object>();
IList <ValueRange> lst = new List <ValueRange>();
foreach (string s in _Row.Row)
{
row.Add(s);
}
ret.Add(row);
v.Values = ret;
lst.Add(v);
Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest _body = new Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest();
_body.Data = lst;
_body.ValueInputOption = "RAW";
v.MajorDimension = "ROWS";
SpreadsheetsResource.ValuesResource.BatchUpdateRequest request2 =
service.Spreadsheets.Values.BatchUpdate(_body, spreadsheetId);
request2.Fields = "totalUpdatedRows";
BatchUpdateValuesResponse response2 = request2.Execute();
return(response2.TotalUpdatedRows);
}
private void FillPropertyValue(SheetRow rowModel, ImportColumnProperty columnProperty, ICell cell)
{
var cellValue = cell == null ? null : CellValueHelper.GetCellValue(cell);
if (string.IsNullOrWhiteSpace(cellValue))
{
if (columnProperty.IsRequired || (!columnProperty.PropertyInfo.IsNullable() && columnProperty.PropertyInfo.PropertyType != typeof(string)))
{
rowModel.SetError(columnProperty.PropertyInfo.Name, columnProperty.EmptyErrorMessage);
}
}
else
{
if (columnProperty.HasRegex && !Regex.IsMatch(cellValue, columnProperty.RegexPattern, columnProperty.RegexOptions))
{
rowModel.SetError(columnProperty.PropertyInfo.Name, columnProperty.RegexErrorMessage);
return;
}
var targetType = columnProperty.PropertyInfo.IsNullable() ? columnProperty.PropertyInfo.PropertyType.GetGenericArguments()[0] : columnProperty.PropertyInfo.PropertyType;
columnProperty.PropertyInfo.SetValue(rowModel, this.ConvertValueType(cellValue, targetType));
}
}
static void Main(string[] args)
{
// check if already running
if (IsAppRunning())
{
return;
}
// load configuration
IConfiguration config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
var optionsBuilder = new DbContextOptionsBuilder <PosybeContext>();
optionsBuilder.UseSqlServer(Datapac.CryptUnit.KX_Decrypt(config.GetConnectionString("PosybeConnection"), "PosybeConnection"));
// setup logger
Log.Logger = new LoggerConfiguration()
.ReadFrom.Configuration(config)
.CreateLogger();
Log.Information($"App started ..");
Environment.ExitCode = 0;
bool generateEnabled = false, exportTemplateEnabled = false;
string inputFileName = string.Empty, outputFileName = string.Empty, mappingFile = "Posybe2ERPMapping.xml";
var o = args.ToList();
var p = o.Select(p => p.ToLower()).ToList();
if (o.Count > 0)
{
generateEnabled = p[0] == "/generatesql";
exportTemplateEnabled = p[0] == "/exporttemplate";
}
//exportTemplateEnabled = j != -1;
if (o.Count > 1)
{
inputFileName = o[1];
}
if (o.Count > 2)
{
outputFileName = o[2];
}
if (o.Count > 3)
{
mappingFile = o[3];
}
if (!generateEnabled && !exportTemplateEnabled || (generateEnabled && (string.IsNullOrEmpty(inputFileName) || string.IsNullOrEmpty(outputFileName))) || (exportTemplateEnabled && string.IsNullOrEmpty(inputFileName)))
{
Log.Information($"Wrong input parameters defined. ");
Console.WriteLine("No arguments specified. !!");
Console.WriteLine();
Console.WriteLine("Available arguments:");
Console.WriteLine("Commands:");
Console.WriteLine(" /generatesql - Generate SQL script from excel, input parameters [excel file path] [output file path] [mapping file- default name Posybe2ERPMapping.xml]");
Console.WriteLine(" /exporttemplate - Generate Excel file from database, input parameters [excel file path to create] (ConnectionString PosybeConnection must be defined in appsettings.json)");
Environment.ExitCode = 1;
}
try
{
if (generateEnabled)
{
Log.Information($"Generating SQL file {outputFileName}");
if (File.Exists(outputFileName))
{
File.Delete(outputFileName);
}
string result = string.Empty;
var ex = new Export(null, mappingFile, null);
var ei = new ExcelImport();
int dc = 0;
int errors = 0;
int[] sposob = new int[2];
ei.ImportMovementsFromExcel(inputFileName, (a) =>
{
try
{
dc++;
var name = a.Operation == 1 ? "Issue" : "Receipt";
Mapping mapping = null;
if (a.IsExternalCode)
{
mapping = ex.GoodsMovementConversionBack(a.Type, a.Operation, a.ExternalCode, 0);
//if (mapping == null) mapping = new Mapping() { PosybeCode = "0" };
if (mapping == null)
{
throw new Exception($"Cannot map code from external code {a.ExternalCode} for {name} item name {a.Name}");
}
}
else if (!string.IsNullOrEmpty(a.SubCode))
{
mapping = new Mapping()
{
PosybeCode = a.SubCode
};
}
else
{
throw new Exception($"No mapping code defined for {name} item name {a.Name}");
}
string sql = $"If Not Exists( Select * From [Posybe].[dbo].[F_SKLAD_POHYBY_SPOSOBY] Where OPERACIA={a.Operation} And SPOSOB={sposob[a.Operation]} And TYP={a.Type}) \r\n" +
$"Insert[Posybe].[dbo].[F_SKLAD_POHYBY_SPOSOBY] (OPERACIA,SPOSOB,POVOLENY,SKRATKA,NAZOV,MAPOVANIE_POHYB,SQL_FILTER,SQL_FILTER_PARTNER,TYP) Values({a.Operation}, {sposob[a.Operation]}, 0, '', N'', 0, Null, Null, {a.Type})";
string enabled = string.Join("'',''", a.Items.Where(p => p.State).Select(p => p.Prefix));
string disabled = string.Join("'',''", a.Items.Where(p => !p.State).Select(p => p.Prefix));
int povoleny = 1;
string filter = $"\r\nSQL_FILTER = 'And s.TOVAR In ( Select us.TOVAR From UPL_SKLAD us Left Outer Join UPL_SUBCATEGORIES usc on us.SUBCATEGORY_ID = usc.ID Where usc.NOTES ##In## (\r\n''";
if (enabled.Length == 0)
{
povoleny = 0;
filter = string.Empty;
}
else if (disabled.Length == 0)
{
povoleny = 1;
filter = string.Empty;
}
if (enabled.Length < disabled.Length)
{
povoleny = 1;
filter = filter.Replace("##In##", "In");
filter += enabled + "'') )";
}
else
{
povoleny = 1;
filter = filter.Replace("##In##", "Not In");
filter += disabled + "'') )";
}
sql += $"\r\n\r\nUpdate[Posybe].[dbo].[F_SKLAD_POHYBY_SPOSOBY] Set POVOLENY = {povoleny}, SKRATKA = '{a.ShortName}', NAZOV = N'{a.Name}', MAPOVANIE_POHYB = {mapping.PosybeCode},";
if (a.Limits[0].HasValue && a.Limits[0].Value && a.Limits[1].HasValue && a.Limits[1].Value)
{
throw new Exception($"Cannot define both item limits for {name} item name {a.Name}");
}
if (a.Limits[2].HasValue && a.Limits[2].Value && a.Limits[3].HasValue && a.Limits[3].Value)
{
throw new Exception($"Cannot define both partner limits for {name} item name {a.Name}");
}
if (a.Limits[4].HasValue && a.Limits[4].Value && a.Limits[5].HasValue && a.Limits[5].Value)
{
throw new Exception($"Cannot define both partner ses limits for {name} item name {a.Name}");
}
string itemLimits = string.Empty;
if (a.Limits[0].HasValue && a.Limits[0].Value)
{
itemLimits += " And IsNull(ctd.DPH_T, -1) <> -1";
}
if (a.Limits[1].HasValue && a.Limits[1].Value)
{
itemLimits += " And IsNull(ctd.DPH_T, -1) = -1";
}
if (string.IsNullOrEmpty(itemLimits))
{
if (string.IsNullOrEmpty(filter))
{
filter = "\r\nSQL_FILTER = Null";
}
else
{
filter += "'";
}
}
else
{
filter += itemLimits + "'";
}
sql += filter + ",";
filter = "\r\nSQL_FILTER_PARTNER = ";
string PartnerLimits = string.Empty;
if (a.Limits[2].HasValue && a.Limits[2].Value)
{
PartnerLimits += " And IsNull(spe.PRISTUP_K_TOVAROM, 0) <> 2";
}
if (a.Limits[3].HasValue && a.Limits[3].Value)
{
PartnerLimits += " And IsNull(spe.PRISTUP_K_TOVAROM, 0) = 2";
}
string PartnerLimitsSes = string.Empty;
if (a.Limits[4].HasValue && a.Limits[4].Value)
{
PartnerLimitsSes += " And sp.PARTNER Between 10000 And 99999";
}
if (a.Limits[5].HasValue && a.Limits[5].Value)
{
PartnerLimitsSes += " And sp.PARTNER In (Select CISLO_CS From C_ID_CS)";
}
if (string.IsNullOrEmpty(PartnerLimits) && string.IsNullOrEmpty(PartnerLimitsSes))
{
filter += "Null";
}
else
{
filter += "'";
if (!string.IsNullOrEmpty(PartnerLimits))
{
filter += PartnerLimits;
}
if (!string.IsNullOrEmpty(PartnerLimitsSes))
{
filter += PartnerLimitsSes;
}
filter += "'";
}
sql += filter;
sql += $"\r\nWhere OPERACIA = {a.Operation} And SPOSOB = {sposob[a.Operation]} And TYP = {a.Type}";
sposob[a.Operation]++;
result += "\r\n\r\n" + sql;
}
catch (Exception ex)
{
errors++;
Console.WriteLine($"Error: {ex.Message}");
Log.Error(ex, $"Unexpected error while reading excel file {inputFileName}");
}
});
if (dc == 0)
{
throw new Exception($"No data found in excel file {inputFileName}");
}
else if (errors == 0)
{
Environment.ExitCode = 1;
File.AppendAllText(outputFileName, result);
Console.WriteLine($"SQL script {outputFileName} generated. Records count: {dc}");
Log.Information($"SQL script {outputFileName} generated. Records count: {dc}");
}
}
else if (exportTemplateEnabled)
{
try
{
using (var db = new PosybeContext(optionsBuilder.Options))
{
var k = db.FSkladPohybySposoby.AsNoTracking().Where(p => p.Operacia == 1 && p.Typ == 0);
var rows = new List <SheetRow>();
var r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "External system movement", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Posybe movement", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.MapovaniePohyb.ToString()
});
}
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Posybe movement operation", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.Skratka
});
}
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Item limitation - COCA", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Item limitation - CODO", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - COCA", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - CODO", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - only SeS", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - same SeS", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()
{
Value = "MC Prefix", Bold = true
}); r.Columns.Add(new SheetCellValue()
{
Value = "Movement/MC name", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.Nazov
});
}
var m = new List <string>();
foreach (var cc in k)
{
if (!string.IsNullOrEmpty(cc.SqlFilter))
{
if (cc.SqlFilter.Contains("UPL_SUBCATEGORIES"))
{
int i = cc.SqlFilter.IndexOf("'");
int j = cc.SqlFilter.LastIndexOf("'");
if (j >= 0 && i >= 0)
{
foreach (var ca in cc.SqlFilter.Substring(i, j - i).Replace("'", "").Split(','))
{
var ko = ca.Trim();
if (!m.Contains(ko))
{
m.Add(ko);
}
}
;
}
}
}
}
foreach (var sku in m)
{
foreach (var boi in db.UplSubcategories.AsNoTracking().Where(p => p.Notes.Contains(sku)))
{
r = new SheetRow(); r.Columns.Add(new SheetCellValue()
{
Value = sku
}); r.Columns.Add(new SheetCellValue()
{
Value = boi.Title.Trim()
});
rows.Add(r);
}
}
//issue
var sheets = new List <SheetDefinition <SheetRow> >();
var c = new SheetDefinition <SheetRow>()
{
Name = "Issue", Fields = new List <SpreadsheetField>(), Objects = rows
};
sheets.Add(c);
k = db.FSkladPohybySposoby.AsNoTracking().Where(p => p.Operacia == 0 && p.Typ == 0);
rows = new List <SheetRow>();
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "External system movement", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Posybe movement", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.MapovaniePohyb.ToString()
});
}
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Posybe movement operation", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.Skratka
});
}
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Item limitation - COCA", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Item limitation - CODO", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - COCA", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - CODO", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - only SeS", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()); r.Columns.Add(new SheetCellValue()
{
Value = "Partner limitation - same SeS", Bold = true
});
rows.Add(r);
r = new SheetRow(); r.Columns.Add(new SheetCellValue()
{
Value = "MC Prefix", Bold = true
}); r.Columns.Add(new SheetCellValue()
{
Value = "Movement/MC name", Bold = true
});
rows.Add(r);
foreach (var cc in k)
{
r.Columns.Add(new SheetCellValue()
{
Value = cc.Nazov
});
}
m = new List <string>();
foreach (var cc in k)
{
if (!string.IsNullOrEmpty(cc.SqlFilter))
{
if (cc.SqlFilter.Contains("UPL_SUBCATEGORIES"))
{
int i = cc.SqlFilter.IndexOf("'");
int j = cc.SqlFilter.LastIndexOf("'");
if (j >= 0 && i >= 0)
{
foreach (var ca in cc.SqlFilter.Substring(i, j - i).Replace("'", "").Split(','))
{
var ko = ca.Trim();
if (!m.Contains(ko))
{
m.Add(ko);
}
}
;
}
}
}
}
foreach (var sku in m)
{
foreach (var boi in db.UplSubcategories.AsNoTracking().Where(p => p.Notes.Contains(sku)))
{
r = new SheetRow(); r.Columns.Add(new SheetCellValue()
{
Value = sku
}); r.Columns.Add(new SheetCellValue()
{
Value = boi.Title.Trim()
});
rows.Add(r);
}
}
c = new SheetDefinition <SheetRow>()
{
Name = "Receipt", Fields = new List <SpreadsheetField>(), Objects = rows
};
sheets.Add(c);
// genrate excel file from table
Spreadsheet.Create <SheetRow>(inputFileName, sheets.ToArray());
Console.WriteLine($"Excel file {inputFileName} generated.");
Log.Information($"Excel file {inputFileName} generated.");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
Log.Error(ex, $"Unexpected error while generating excel file {inputFileName}");
}
}
}
catch (Exception ex)
{
Environment.ExitCode = 1;
Console.WriteLine($"Error: {ex.Message}");
}
}