public Object ReceivableEmployeeQuery(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1 ");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "employeename")
{
sb.AppendFormat(" and (employeename ilike '%{0}%' or employeecode ilike '%{0}%')", f);
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by employeecode ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string sql = "select employeeid,employeename,employeecode,coalesce(sum(total),0) as total from mvw_receivabledetail "
+ sb.ToString()
+ " group by employeeid,employeename,employeecode "
+ sborder.ToString();
string sqlCount = "select count(0) as cnt from (select employeeid,employeename,employeecode from mvw_receivabledetail "
+ sb.ToString()
+ " group by employeeid,employeename,employeecode) as t";
string sqlTotalSum = "select coalesce(sum(total),0) as total from mvw_receivabledetail " + sb.ToString();
int recordcount = db.Count(sqlCount);
decimal totalsum = Convert.ToDecimal(db.Scalar(sqlTotalSum));
DataTable list = db.QueryTable(sql);
return(new
{
resulttotal = recordcount,
totalsum = totalsum,
data = list,
});
}
public static bool UpdatePropertiesWithValuesFromFamily(Element loadedElementOrType, IEnumerable <CADProperty> properties, bool setUserDefinedValues = false, bool deleteUnknownProperties = false)
{
var hasUserDefinedValues = false;
var monitoredExecutionContext = ApplicationGlobals.ApplicationContext.GetService <IMonitoredExecutionContext>();
var parameterHelper = new ParameterHelper();
using (var monitoredExecutionBlock = monitoredExecutionContext
.MonitorMethod <UOLAddInUtilities>(nameof(UpdatePropertiesWithValuesFromFamily))
.WithParameter(nameof(loadedElementOrType), loadedElementOrType)
.WithParameter(nameof(properties), properties)
.WithParameter(nameof(setUserDefinedValues), setUserDefinedValues)
.WithTiming())
{
var propertiesToDelete = new List <CADProperty>();
foreach (var cadProperty in properties)
{
if (setUserDefinedValues || (!setUserDefinedValues && !cadProperty.Selected) || (!setUserDefinedValues && deleteUnknownProperties))
{
var propertyName = cadProperty.Code.StartsWith("uob_prod_", StringComparison.OrdinalIgnoreCase) ? $"{cadProperty.Code}" : $"{cadProperty.Code}_{cadProperty.PortCode}_";
var revitParameter = parameterHelper.GetParameterBySearchString(loadedElementOrType, propertyName, true);
if (revitParameter == null || !revitParameter.HasValue)
{
if (revitParameter == null && deleteUnknownProperties)
{
propertiesToDelete.Add(cadProperty);
}
continue;
}
switch (revitParameter.StorageType)
{
case StorageType.Double:
var etimDisplayUnitType = UOLAddInUtilities.ConvertEUCode(cadProperty.UnitCode);
double?parameterDoubleValue = Math.Round(parameterHelper.ConvertFromAPI(etimDisplayUnitType, revitParameter.AsDouble()), 6);
if (parameterDoubleValue == 0 && cadProperty.Value.NumericValue == null)
{
parameterDoubleValue = cadProperty.Value.NumericValue;
}
if (setUserDefinedValues && !parameterDoubleValue.Equals(cadProperty.Value.NumericValue))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
NumericValue = parameterDoubleValue
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues)
{
cadProperty.Value.NumericValue = parameterDoubleValue;
}
break;
case StorageType.Integer:
if (cadProperty.Type == "L")
{
var parameterBooleanValue = Convert.ToBoolean(revitParameter.AsInteger());
if (setUserDefinedValues && !parameterBooleanValue.Equals(cadProperty.Value.LogicalValue))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
LogicalValue = parameterBooleanValue
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues)
{
cadProperty.Value.LogicalValue = parameterBooleanValue;
}
}
else
{
double?parameterIntegerValue = revitParameter.AsInteger();
if (parameterIntegerValue == 0 && cadProperty.Value.NumericValue == null)
{
parameterIntegerValue = cadProperty.Value.NumericValue;
}
if (setUserDefinedValues && !parameterIntegerValue.Equals(cadProperty.Value.NumericValue))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
NumericValue = parameterIntegerValue
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues)
{
cadProperty.Value.NumericValue = parameterIntegerValue;
}
}
break;
case StorageType.String:
var parameterStringValue = revitParameter.AsString();
if (parameterStringValue.StartsWith("EV", StringComparison.OrdinalIgnoreCase))
{
if (setUserDefinedValues && !parameterStringValue.Equals(cadProperty.Value.Code))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
Code = parameterStringValue, Description = null
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues && (string.IsNullOrEmpty(cadProperty.Value.Code) || !cadProperty.Value.Code.Equals(parameterStringValue, StringComparison.OrdinalIgnoreCase)))
{
cadProperty.Value.Code = parameterStringValue;
cadProperty.Value.Description = null;
}
}
else
{
if (setUserDefinedValues && !(parameterStringValue == string.Empty && cadProperty.Value.Description == null) && !parameterStringValue.Equals(cadProperty.Value.Description))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
Code = null, Description = string.IsNullOrEmpty(parameterStringValue) ? null : parameterStringValue
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues)
{
cadProperty.Value.Code = null;
cadProperty.Value.Description = string.IsNullOrEmpty(parameterStringValue) ? null : parameterStringValue;
}
}
break;
case StorageType.ElementId:
var parameterElementIdIntegerValue = revitParameter.AsElementId().IntegerValue;
if (setUserDefinedValues && !parameterElementIdIntegerValue.Equals(cadProperty.Value.NumericValue))
{
cadProperty.UserDefinedValue = new CADPropertyValue {
NumericValue = parameterElementIdIntegerValue
};
cadProperty.Selected = true;
hasUserDefinedValues = true;
}
else if (!setUserDefinedValues)
{
cadProperty.Value.NumericValue = parameterElementIdIntegerValue;
}
break;
}
}
}
if (propertiesToDelete.Count > 0)
{
var filteredProperties = new List <CADProperty>(properties);
foreach (var property in propertiesToDelete)
{
filteredProperties.Remove(property);
}
properties = filteredProperties;
}
}
return(hasUserDefinedValues);
}
public Object LoadBill(string parameters)
{
ParameterHelper ph = new ParameterHelper(parameters);
int id = ph.GetParameterValue <int>("id");
DBHelper db = new DBHelper();
var bill = db.First("bill", id);
var maker = db.First("employee", bill.content.Value <int>("makerid"));
var vendor = db.First("vendor", bill.content.Value <int>("vendorid"));
var employee = db.First("employee", bill.content.Value <int>("employeeid"));
var option = db.First("select * from option");
int digit = 2;
bool noeditbillcode = option.content["noeditbillcode"] == null ? false : option.content.Value <bool>("noeditbillcode");
bool noeditbilldate = option.content["noeditbilldate"] == null ? false : option.content.Value <bool>("noeditbilldate");
StringBuilder sb = new StringBuilder();
Dictionary <int, JObject> dic = new Dictionary <int, JObject>();
foreach (var item in bill.content.Value <JArray>("details").Values <JObject>())
{
sb.Append(item.Value <int>("billid"));
sb.Append(",");
dic.Add(item.Value <int>("billid"), item);
}
string where = sb.ToString().Substring(0, sb.Length - 1);
string sql = "select * from bill where id in (" + where + ")";
var bills = db.Where(sql);
foreach (var item in bills)
{
item.content.Add("makername", db.First("employee", item.content.Value <int>("makerid")).content.Value <string>("name"));
item.content.Add("employeename", db.First("employee", item.content.Value <int>("employeeid")).content.Value <string>("name"));
string billname = item.content.Value <string>("billname");
if (billname == "purchasebackbill")
{
item.content["total"] = -item.content.Value <decimal>("total");
}
item.content.Add("thisinvoicetotal", dic[item.id].Value <decimal>("invoicetotal"));
item.content.Add("invoicetype", dic[item.id].Value <string>("invoicetype"));
item.content.Add("invoicecode", dic[item.id].Value <string>("invoicecode"));
}
decimal totalsum = bills.Sum(c => c.content.Value <decimal>("total"));
decimal invoicetotalsum = bills.Sum(c => c.content.Value <decimal>("invoicetotal"));
return(new
{
content = bill.content,
makername = maker.content.Value <string>("name"),
digit = digit,
vendorname = vendor.content.Value <string>("name"),
employeename = employee.content.Value <string>("name"),
noeditbillcode = noeditbillcode,
noeditbilldate = noeditbilldate,
totalsum = totalsum,
invoicetotalsum = invoicetotalsum,
data = bills,
});
}
private string ExpandPlatformParameters(string pValue, string pEntity)
{
string strValue = pValue;
if (pEntity != null)
{
strValue = strValue.Replace("{entity}", pEntity);
if (mPlatformDef.entitygeneration != null &&
mPlatformDef.entitygeneration.generateentity != null &&
mPlatformDef.entitygeneration.generateentity.parameterdefinitions != null)
{
type_generationGenerateentity entity = null;
if (mCurrentPlatform.entitygeneration != null && mCurrentPlatform.entitygeneration.generateentity != null)
{
foreach (type_generationGenerateentity e in mCurrentPlatform.entitygeneration.generateentity)
{
if (e.entity.Equals(pEntity))
{
entity = e;
}
}
}
if (entity != null && entity.parameters != null)
{
foreach (parameterdefinitionsParameterdefinition paramdef in mPlatformDef.entitygeneration.generateentity.parameterdefinitions)
{
strValue = strValue.Replace("{$" + paramdef.name + "}", ParameterHelper.GetParameter(paramdef.name, paramdef, entity.parameters));
}
}
}
if (mConfig.entitygeneration != null &&
mConfig.entitygeneration.generateentity != null &&
mConfig.entitygeneration.generateentity.parameterdefinitions != null)
{
type_generationGenerateentity entity = null;
if (mProjectSettings.entitygeneration != null && mProjectSettings.entitygeneration.generateentity != null)
{
foreach (type_generationGenerateentity e in mProjectSettings.entitygeneration.generateentity)
{
if (e.entity.Equals(pEntity))
{
entity = e;
}
}
}
if (entity != null && entity.parameters != null)
{
foreach (parameterdefinitionsParameterdefinition paramdef in mConfig.entitygeneration.generateentity.parameterdefinitions)
{
strValue = strValue.Replace("{$" + paramdef.name + "}", ParameterHelper.GetParameter(paramdef.name, paramdef, entity.parameters));
}
}
}
}
return(ExpandPlatformParameters(strValue));
}
//--------------------------------------------------------------------------------
// Table
//--------------------------------------------------------------------------------
public static string GetTableName(IGeneratorOption option, MethodInfo mi)
{
var parameter = mi.GetParameters()
.FirstOrDefault(x => ParameterHelper.IsSqlParameter(x) && ParameterHelper.IsNestedParameter(x));
if (parameter == null)
{
return(null);
}
return(GetTableNameOfType(option, parameter.ParameterType));
}
public ParameterHelperWindow(ParameterHelper parameterHelper)
{
InitializeComponent();
this.parameterHelper = parameterHelper;
}
public Object ImportData(string parameters)
{
using (DBHelper db = new DBHelper())
{
var options = db.First("select * from option");
if (options.content["initoverdate"] != null)
{
return(new { message = StringHelper.GetString("系统已经开账，不能再修改期初数据！") });
}
}
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
string path = dic["path"].ToString();
int rowno = 0;
try
{
string ext = Path.GetExtension(path).ToLower();
//导入数据
IWorkbook workbook = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (ext == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
var sheet = workbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum + 1;
StringBuilder sb = new StringBuilder();
using (DBHelper db = new DBHelper())
{
for (int i = 1; i < rowcount; i++)
{
#region 逐行导入
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
var vendor = db.First("select * from vendor where content->>'code'='" + code + "'");
if (row.GetCell(2).GetCellValue().ToString() != "")
{
vendor.content["initpayable"] = Convert.ToDecimal(row.GetCell(2).GetCellValue());
}
else
{
vendor.content.Remove("initpayable");
}
db.Edit(tablename, vendor);
#endregion
}
if (sb.Length > 0)
{
db.Discard();
return(new { message = sb.ToString() });
}
db.SaveChanges();
}
return(new { message = "ok" });
}
catch (Exception ex)
{
//Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
return(new { message = "导入出错(" + rowno + ")" + ex.Message });
}
}
public override bool Edit(ProductInfoVO m)
{
CodeCommand command = new CodeCommand();
command.CommandText = EDIT;
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Name", Value = ParameterHelper.ConvertValue(m.Name)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Desc", Value = ParameterHelper.ConvertValue(m.Desc)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdId", Value = ParameterHelper.ConvertValue(m.AdId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Price", Value = ParameterHelper.ConvertValue(m.Price)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AttrText", Value = ParameterHelper.ConvertValue(m.AttrText)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AttrStyle", Value = ParameterHelper.ConvertValue(m.AttrStyle)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsState", Value = ParameterHelper.ConvertValue(m.IsState)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsDelete", Value = ParameterHelper.ConvertValue(m.IsDelete)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@CreateUserId", Value = ParameterHelper.ConvertValue(m.CreateUserId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@CreateDate", Value = ParameterHelper.ConvertValue(m.CreateDate)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Id", Value = ParameterHelper.ConvertValue(m.Id)
});
int result = DbProxyFactory.Instance.Proxy.ExecuteNonQuery(command);
if (result >= 1)
{
return(true);
}
return(false);
}
public override int InsertIdentityId(LogBrowseHistoryVO m)
{
CodeCommand command = new CodeCommand();
command.CommandText = INSERT + "; select @@Identity";
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Url", Value = ParameterHelper.ConvertValue(m.Url)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ClientIp", Value = ParameterHelper.ConvertValue(m.ClientIp)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseType", Value = ParameterHelper.ConvertValue(m.BrowseType)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@CreateDate", Value = ParameterHelper.ConvertValue(m.CreateDate)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdId", Value = ParameterHelper.ConvertValue(m.AdId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdUserId", Value = ParameterHelper.ConvertValue(m.AdUserId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@FlowUserId", Value = ParameterHelper.ConvertValue(m.FlowUserId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdUrl", Value = ParameterHelper.ConvertValue(m.AdUrl)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Money", Value = ParameterHelper.ConvertValue(m.Money)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsMoney", Value = ParameterHelper.ConvertValue(m.IsMoney)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Time", Value = ParameterHelper.ConvertValue(m.Time)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ClientId", Value = ParameterHelper.ConvertValue(m.ClientId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsMobile", Value = ParameterHelper.ConvertValue(m.IsMobile)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ReferrerUrl", Value = ParameterHelper.ConvertValue(m.ReferrerUrl)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseName", Value = ParameterHelper.ConvertValue(m.BrowseName)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseVersion", Value = ParameterHelper.ConvertValue(m.BrowseVersion)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@OsName", Value = ParameterHelper.ConvertValue(m.OsName)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Country", Value = ParameterHelper.ConvertValue(m.Country)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Area", Value = ParameterHelper.ConvertValue(m.Area)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Region", Value = ParameterHelper.ConvertValue(m.Region)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@City", Value = ParameterHelper.ConvertValue(m.City)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@County", Value = ParameterHelper.ConvertValue(m.County)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Isp", Value = ParameterHelper.ConvertValue(m.Isp)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IpSource", Value = ParameterHelper.ConvertValue(m.IpSource)
});
var result = DbProxyFactory.Instance.Proxy.ExecuteScalar(command);
return(int.Parse(result.ToString()));
}
public ActionResult Index(string category, string item, int page = 1)
{
Validate validate = new Validate();
validate.CheckDictionary <string, string>(WebResource.Field_PurchaseCategory, category, ParameterHelper.GetPurchaseItemCatetory());
if (validate.IsFailed)
{
return(RedirectToAction("Error", "Common", validate.ErrorMessages));
}
PurchaseIndexModel model = new PurchaseIndexModel(category, page);
model.SelectedItem = item;
int totalCount = 0;
List <Purchase> entities = this.purchaseService.Search(category, item, model.PageIndex, model.PageSize, ref totalCount);
Dictionary <string, PurchaseItemCacheModel> items = ParameterHelper.GetPurchaseItem(category, false);
foreach (Purchase entity in entities)
{
PurchasePageModel pageModel = new PurchasePageModel(entity);
PurchaseItemCacheModel purchaseItem = items[entity.Item];
if (purchaseItem != null)
{
pageModel.Item = purchaseItem.Name;
}
model.PageData.Add(pageModel);
}
model.TotalCount = totalCount;
return(View(model));
}
/// <summary>
/// 提交验证
/// </summary>
/// <param name="validate">返回验证对象</param>
public void PostValidate(ref Validate validate)
{
this.AddressId = validate.CheckInputString(WebResource.Field_Address, this.AddressId, true, Constant.STRING_LENGTH_32);
this.Date = validate.CheckDateString(WebResource.Field_Date, this.DateString, true);
PurchaseItemCacheModel item = validate.CheckDictionary <string, PurchaseItemCacheModel>(WebResource.Field_Product, this.Item, ParameterHelper.GetPurchaseItem(PurchaseItemCategory.Product));
if (item != null)
{
this.Unit = item.OutUnit;
this.Price = item.Price;
}
validate.CheckDecimal(WebResource.Field_Quantity, this.Quantity, Constant.DECIMAL_REQUIRED_MIN, Constant.DECIMAL_MAX);
validate.CheckDecimal(WebResource.Field_ActualAmount, this.ActualAmount, Constant.DECIMAL_ZERO, Constant.DECIMAL_MAX);
this.Remark = validate.CheckInputString(WebResource.Field_Remark, this.Remark, false, Constant.STRING_LENGTH_100);
}
public ActionResult Add(string category)
{
Validate validate = new Validate();
validate.CheckDictionary <string, string>(WebResource.Field_PurchaseCategory, category, ParameterHelper.GetPurchaseItemCatetory());
if (validate.IsFailed)
{
return(RedirectToAction("Error", "Common", validate.ErrorMessages));
}
return(View(new PurchaseAddModel(category)));
}
public Object Categorys(string parameters)
{
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
return(CategoryHelper.GetCategoryTreeData(dic["classname"].ToString()));
}
private void Commitjob_transaction(ILog log)
{
//initial parameters
Parameter maxJob = session.Load <Parameter>("PAYROLL_SCH_JOB_READ_MASS_INQ");
Parameter maxTrx = session.Load <Parameter>("PAYROLL_SCH_GENERATE_MASS_INQ_MAX_TRX");
int maxResult = int.Parse(maxTrx.Data);
IList <TrxPayroll> trxList = session.CreateSQLQuery("select * from trxpayrolls where status=? and (seqnumber%?)=? and lastupdate<=? order by id ASC")
.AddEntity(typeof(TrxPayroll))
.SetInt32(0, ParameterHelper.PAYROLL_WAITING_CHECKACC) //11 : waiting mass inq output
.SetInt32(1, int.Parse(maxJob.Data)) //id mod(10)
.SetInt32(2, jobNumber) //id mod = ini mod 0, ambil trxID kelipatan 10
.SetDateTime(3, DateTime.Now)
//.SetMaxResults(maxResult)
.List <TrxPayroll>();
//we've found some data
foreach (TrxPayroll trx in trxList)
{
string outStatus = "";
Boolean isReject = false;
//Pengecekkan retry, jika dr created time sudah 1 jam maka Lastupdate di tambah setMenitBye menit
int setMenitBye = int.Parse(ParameterHelper.GetString("PAYROLL_SET_MINUTES_TROUBLE_MASSACC", session)); //60
int paramRetryPosition = int.Parse(ParameterHelper.GetString("PAYROLL_PARAMETER__MAX_RETRY_MASSACC", session)); //10
if (trx.RetryPosition > paramRetryPosition)
{
trx.ErrorDescription += "|Transaksi terdeteksi sudah retry sebanyak > " + paramRetryPosition.ToString() + " kali, REJECT Transactions";
isReject = MassInquiryHelper.rejectPayroll(trx, session, log, trx.RetryPosition);
if (isReject)
{
log.Error(SchCode + " === (TrxID " + trx.Id + ") Transaksi terdeteksi sudah retry sebanyak > " + paramRetryPosition.ToString() + " kali, REJECT Transactions");
}
else
{
log.Error(SchCode + " === (TrxID " + trx.Id + ") Transaksi terdeteksi sudah retry sebanyak > " + paramRetryPosition.ToString() + " kali, GAGAL REJECT Transactions");
trx.LastUpdate = DateTime.Now;
trx.Status = ParameterHelper.TRXSTATUS_REJECT;//4
trx.Description = "ERROR - Problem in Host BRI, please contact Administrator";
trx.ErrorDescription += "|Reject Trx : Failed to Delete Transactions or Delete detail Payroll, Set Reject Payroll only";
session.Update(trx);
session.Flush();
}
}
else
{
if (MassInquiryHelper.downloadHostResponse(trx, session, out outStatus, log, jobNumber))
{
/*
* SUKSES file valid maka normal
* ERROR file jawaban host mengirim message error generate file lagi akan generate 10 menit
* RETRY file blm complete, blm ada footer add 2 minutes
* UNKNOWN unknown isi file Host belum dikenal, generate lagi add 10 menit
* INVALID cek footer gagal, retry reading 2 minutes
*/
#region download true
Parameter retryAja = session.Load <Parameter>("PAYROLL_ADD_MINUTES_RETRY_MASSACC");
Parameter retryError = session.Load <Parameter>("PAYROLL_ADD_MINUTES_RETRY_MASSACC2");
int retryAjaInt = int.Parse(retryAja.Data);
int retryErrorInt = int.Parse(retryError.Data);
if (outStatus.Equals("SUKSES"))//jika sukses download
{
#region sukses
log.Info("Success download file.");
trx.ErrorDescription += schInfo + "Success download file";
if (MassInquiryHelper.parsingFile(trx, session, log, jobNumber, SchCode, out outStatus))
{
#region cek sudahkah anak2 keupdate semua?
string temp_query = "";
string msg = "";
bool lanjut = true;
int count = 0;
string dbResult = "";
bool result = true;
if (result)
{
temp_query = @"select count(1) from trxpayrolldetails where pid = '" + trx.Id + "' and status = " + ParameterHelper.PAYROLLDETAILS_WAITING_CHECK + " and instructioncode = '" + TotalOBHelper.IFT + "';";
if (!PayrollHelper.ExecuteQueryValue(session, temp_query, out dbResult, out msg))
{
log.Error(SchCode + " === (TrxID " + trx.Id + ") Failed Query: " + temp_query + " " + msg);
result = false;
}
else
{
try
{
if (int.TryParse(dbResult, out count))
{
if (count > 0)
{
lanjut = false;
log.Info(SchCode + " === (TrxID " + trx.Id + ") Processing update Detail Transactions not completed, retry parsing again in 1 minutes");
}
}
}
catch (Exception ex)
{
lanjut = false;
log.Error("=== (TrxID " + trx.Id + ") Msg : " + ex.Message + "Stack :" + ex.StackTrace + "Stack :" + ex.InnerException);
}
}
}
#endregion
if (!lanjut && result)
{
log.Error(SchCode + " === (TrxID " + trx.Id + ") Ada " + count.ToString() + " anak yang beum keupdate, ulangi transaksi " + retryAja.Data.ToString() + " menit kemudian!");
trx.LastUpdate = DateTime.Now.AddMinutes(retryAjaInt);
session.Update(trx);
session.Flush();
return;
}
else if (result && lanjut)
{
trx.Status = ParameterHelper.PAYROLL_SUCCESS_PARSING; //46
trx.LastUpdate = DateTime.Now;
log.Info(SchCode + " === (TrxID " + trx.Id + ") Finish Processing");
}
log.Info("Success Parsing File.");
}
else
{
if (outStatus.Equals("[NULL]"))
{
//trx.Status = ParameterHelper.PAYROLL_WAITING_GENERATE_MASS_INQ;//10
trx.LastUpdate = DateTime.Now.AddMinutes(retryAjaInt);
trx.ErrorDescription += "|file balikan host 0 KB / Not Completed-Pending 2 minutes : " + trx.LastUpdate + "|";
trx.RetryPosition += 1;
}
else
{
log.Error("Failed in Parsing File-try again to parsing at: " + trx.LastUpdate);
trx.LastUpdate = DateTime.Now.AddMinutes(retryAjaInt);
trx.ErrorDescription += schInfo + "Failed in Parsing File-Pending 2 minutes : " + trx.LastUpdate;
trx.RetryPosition += 1;
}
}
#endregion sukses
}
else if (outStatus.Equals("ERROR")) // File mendapat message error dr host dan generate lagi
{
trx.LastUpdate = DateTime.Now.AddMinutes(retryErrorInt);
trx.Status = ParameterHelper.PAYROLL_WAITING_GENERATE_MASS_INQ;//10
trx.ErrorDescription += schInfo + " Message from Host error, generate again (" + retryErrorInt.ToString() + " minutes) at " + trx.LastUpdate;
log.Error("ID : " + trx.SeqNumber + " Message from Host error, generate again (" + retryErrorInt.ToString() + " minutes) at " + trx.LastUpdate);
trx.RetryPosition += 1;
}
else if (outStatus.Equals("RETRY")) // Not Completed
{
trx.LastUpdate = DateTime.Now.AddMinutes(retryAjaInt);
trx.ErrorDescription += schInfo + "Mass Inq file is not Completed, try again (" + retryAjaInt.ToString() + " minutes) to read file at " + trx.LastUpdate;
log.Error("ID : " + trx.SeqNumber + " Mass Inq file is not Completed, try again (" + retryAjaInt.ToString() + " minutes) to read file at " + trx.LastUpdate);
trx.RetryPosition += 1;
}
else if (outStatus.Equals("UNKNOWN")) // UNKNOWN
{
trx.LastUpdate = DateTime.Now.AddMinutes(retryErrorInt);
trx.Status = ParameterHelper.PAYROLL_WAITING_GENERATE_MASS_INQ;//10
trx.ErrorDescription += schInfo + "Message from host is unknown, generate again (" + retryErrorInt.ToString() + " minutes) at " + trx.LastUpdate;
log.Error("ID : " + trx.SeqNumber + " Message from host is unknown, generate again (" + retryErrorInt.ToString() + " minutes) at " + trx.LastUpdate);
trx.RetryPosition += 1;
}
else if (outStatus.Equals("INVALID")) // Cek footer gagal retry aja
{
trx.LastUpdate = DateTime.Now.AddMinutes(retryAjaInt);
trx.ErrorDescription += schInfo + "Failed checking Footer, try again (" + retryAjaInt.ToString() + " minutes) to read file at " + trx.LastUpdate;
log.Error("ID : " + trx.SeqNumber + " Failed checking Footer, try again (" + retryAjaInt.ToString() + " minutes) to read file at " + trx.LastUpdate);
trx.RetryPosition += 1;
}
else
{
/*
* JIka sejam retry sampai BOSOKS maksinal 1 jam maka akan reject transakski, eaea dan
* Jika lebih dr 3 menit akan generate Mass Acc Inq, set 10
*/
//if (!MassInquiryHelper.retryMassInq(trx, session, log))
//{
// log.Error("Something Wrong In Retry Mass Inq");
// trx.LastUpdate = DateTime.Now.AddMinutes(2);
// trx.ErrorDescription += schInfo + "Something Wrong In Retry Mass Inq, try again at " + trx.LastUpdate;
//}
#endregion
}
}
else
{
log.Error("Something Wrong In Download File :(");
trx.LastUpdate = DateTime.Now.AddMinutes(2);
trx.ErrorDescription += schInfo + "Something Wrong In Download File" + trx.LastUpdate;
trx.RetryPosition += 1;
}
}
session.Update(trx);
session.Flush();
}
}
public DefaultNarrator()
{
var parser = new NarratorFileParser();
narratorItems = parser.Parse("Narrator/Narrator.txt");
parameterHelper = new ParameterHelper();
}
public override bool Edit(LogBrowseHistoryVO m)
{
CodeCommand command = new CodeCommand();
command.CommandText = EDIT;
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Url", Value = ParameterHelper.ConvertValue(m.Url)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ClientIp", Value = ParameterHelper.ConvertValue(m.ClientIp)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseType", Value = ParameterHelper.ConvertValue(m.BrowseType)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@CreateDate", Value = ParameterHelper.ConvertValue(m.CreateDate)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdId", Value = ParameterHelper.ConvertValue(m.AdId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdUserId", Value = ParameterHelper.ConvertValue(m.AdUserId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@FlowUserId", Value = ParameterHelper.ConvertValue(m.FlowUserId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@AdUrl", Value = ParameterHelper.ConvertValue(m.AdUrl)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Money", Value = ParameterHelper.ConvertValue(m.Money)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsMoney", Value = ParameterHelper.ConvertValue(m.IsMoney)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Time", Value = ParameterHelper.ConvertValue(m.Time)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ClientId", Value = ParameterHelper.ConvertValue(m.ClientId)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IsMobile", Value = ParameterHelper.ConvertValue(m.IsMobile)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@ReferrerUrl", Value = ParameterHelper.ConvertValue(m.ReferrerUrl)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseName", Value = ParameterHelper.ConvertValue(m.BrowseName)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@BrowseVersion", Value = ParameterHelper.ConvertValue(m.BrowseVersion)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@OsName", Value = ParameterHelper.ConvertValue(m.OsName)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Country", Value = ParameterHelper.ConvertValue(m.Country)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Area", Value = ParameterHelper.ConvertValue(m.Area)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Region", Value = ParameterHelper.ConvertValue(m.Region)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@City", Value = ParameterHelper.ConvertValue(m.City)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@County", Value = ParameterHelper.ConvertValue(m.County)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Isp", Value = ParameterHelper.ConvertValue(m.Isp)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@IpSource", Value = ParameterHelper.ConvertValue(m.IpSource)
});
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@Id", Value = ParameterHelper.ConvertValue(m.Id)
});
int result = DbProxyFactory.Instance.Proxy.ExecuteNonQuery(command);
if (result >= 1)
{
return(true);
}
return(false);
}
private List <DataSourceParameter> GetParameters(Report report, XmlNode node)
{
var parameters = new List <DataSourceParameter>();
var candidates = node.ChildNodes.Cast <XmlNode>().Where(x => x.Name.Equals("Parameters"));
foreach (var item in candidates)
{
foreach (var current in item.ChildNodes.Cast <XmlNode>().Where(x => x.Name.Equals("Parameter")))
{
if (current.Attributes != null)
{
string name = this.GetAttributeValue(current, "Name").ToString();
string type = this.GetAttributeValue(current, "Type").ToString();
var defaultValue = this.GetDefaultValue(report, current, type);
bool hasMetaValue = this.HasMetaValue(current);
string populateFrom = this.GetAttributeValue(current, "PopulateFrom")?.ToString();
string keyField = this.GetAttributeValue(current, "KeyField")?.ToString();
string valueField = this.GetAttributeValue(current, "ValueField")?.ToString();
string fieldLabel = this.GetAttributeValue(current, "FieldLabel")?.ToString();
var optional = this.GetAttributeValue(current, "Optional")?.ToString().ToUpperInvariant().StartsWith("T");
fieldLabel = ExpressionHelper.ParseExpression(report.Tenant, fieldLabel, report.DataSources, ParameterHelper.GetPraParameterInfo(report));
parameters.Add(new DataSourceParameter
{
Name = name,
Type = type,
DefaultValue = defaultValue,
HasMetaValue = hasMetaValue,
PopulateFrom = populateFrom,
KeyField = keyField,
ValueField = valueField,
FieldLabel = fieldLabel,
Optional = optional ?? false
});
}
}
}
return(parameters);
}
public Object ImportData(string parameters)
{
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
string path = dic["path"].ToString();
bool cover = Convert.ToBoolean(dic["cover"]);
int rowno = 0;
try
{
string ext = Path.GetExtension(path).ToLower();
//导入数据
IWorkbook workbook = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (ext == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
var sheet = workbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum + 1;
StringBuilder sb = new StringBuilder();
using (DBHelper db = new DBHelper())
{
if (cover)
{
db.Truncate(tablename);
}
for (int i = 1; i < rowcount; i++)
{
#region 逐行导入
rowno = i + 1;
IRow row = sheet.GetRow(i);
string customercode = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(customercode))
{
continue;
}
string productcode = row.GetCell(2).GetCellValue().ToString();
if (string.IsNullOrEmpty(customercode))
{
continue;
}
var customer = db.FirstOrDefault("select * from customer where content->>'code'='" + customercode + "'");
if (customer == null)
{
sb.AppendFormat("第{0}行出现错误：客户编号不存在！<br/>", rowno);
continue;
}
var product = db.FirstOrDefault("select * from product where content->>'code'='" + productcode + "'");
if (product == null)
{
sb.AppendFormat("第{0}行出现错误：产品编号不存在！<br/>", rowno);
continue;
}
if (row.GetCell(4).GetCellValue().ToString() == "")
{
sb.AppendFormat("第{0}行出现错误：没有填写价格！<br/>", rowno);
continue;
}
decimal price = Convert.ToDecimal(row.GetCell(4).GetCellValue());
JObject vp = new JObject();
vp.Add("customerid", customer.id);
vp.Add("productid", product.id);
vp.Add("price", price);
TableModel model = new TableModel()
{
content = vp
};
if (!cover)
{
var dbmodel = db.FirstOrDefault("select * from customerproduct where (content->>'customerid')::int=" + customer.id + " and (content->>'productid')::int=" + product.id);
if (dbmodel == null)
{
db.Add(tablename, model);
}
else
{
dbmodel.content = model.content;
db.Edit(tablename, dbmodel);
}
}
else
{
db.Add(tablename, model);
}
#endregion
}
if (sb.Length > 0)
{
db.Discard();
return(new { message = sb.ToString() });
}
db.SaveChanges();
}
return(new { message = "ok" });
}
catch (Exception ex)
{
//Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
return(new { message = "导入出错(" + rowno + ")" + ex.Message });
}
}
public void Init()
{
helper = new ParameterHelper();
}
public Object List(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" where coalesce(customer.content->>'stop','')='' and coalesce(product.content->>'stop','')=''");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "customerid")
{
sb.AppendFormat(" and (customerproduct.content->>'customerid')::int = {0}", f);
}
else if (field == "productid")
{
sb.AppendFormat(" and (customerproduct.content->>'productid')::int = {0}", f);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
if (field.ToLower() == "customer.code")
{
sborder.AppendFormat(" customer.content->'customer'->>'code' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "customer.name")
{
sborder.AppendFormat(" customer.content->'customer'->>'name' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "product.code")
{
sborder.AppendFormat(" product.content->'product'->>'code' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "product.name")
{
sborder.AppendFormat(" product.content->'product'->>'name' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else
{
sborder.AppendFormat(" customerproduct.content->>'{0}' {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
}
if (mysorting.Count == 0)
{
sborder.Append(" order by customerproduct.id desc ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
DBHelper db = new DBHelper();
string sql = "select customerproduct.id,jsonb_set(jsonb_set(customerproduct.content,'{customer}',customer.content),'{product}',product.content) as content"
+ " from customerproduct "
+ " inner join customer on (customerproduct.content->>'customerid')::int=customer.id "
+ " inner join product on (customerproduct.content->>'productid')::int=product.id "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt"
+ " from customerproduct "
+ " inner join customer on (customerproduct.content->>'customerid')::int=customer.id "
+ " inner join product on (customerproduct.content->>'productid')::int=product.id "
+ sb.ToString();
int recordcount = db.Count(sqlCount);
List <TableModel> list = db.Where(sql);
return(new { resulttotal = recordcount, data = list });
}
public Object List(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
StringBuilder sb = new StringBuilder();
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field.ToLower() == "categoryid")
{
if (f != "0")
{
var ids = CategoryHelper.GetChildrenIds(Convert.ToInt32(f));
ids.Add(Convert.ToInt32(f));
var idsfilter = ids.Select(c => c.ToString()).Aggregate((a, b) => a + "," + b);
sb.AppendFormat(" and (content->>'categoryid')::int in ({0})", idsfilter);
}
}
else
{
sb.AppendFormat(" and content->>'{0}' ilike '%{1}%'", field.ToLower(), f);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
sborder.AppendFormat(" content->>'{0}' {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
if (mysorting.Count == 0)
{
sborder.Append(" order by content->>'code' ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
int recordcount = 0;
DBHelper db = new DBHelper();
List <TableModel> list = db.Query(this.tablename, sb.ToString(), sborder.ToString(), out recordcount);
decimal totalsum = Convert.ToDecimal(db.Scalar("select coalesce(sum(coalesce(content->>'initpayable','0')::decimal),0) as total from vendor where 1=1 " + sb.ToString()));
var options = db.First("select * from option");
bool initover = options.content["initoverdate"] != null;
return(new { resulttotal = recordcount, data = list, totalsum = totalsum, initover = initover });
}
public Object List(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
StringBuilder sb = new StringBuilder();
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field.ToLower() == "state")
{
sb.AppendFormat(" and coalesce(content->>'stop','')='{0}'", f == "normal" ? "" : "t");
}
else
{
sb.AppendFormat(" and content->>'{0}' ilike '%{1}%'", field.ToLower(), f);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
sborder.AppendFormat(" content->>'{0}' {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
if (mysorting.Count == 0)
{
sborder.Append(" order by content->>'code' ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
int recordcount = 0;
DBHelper db = new DBHelper();
List <TableModel> list = db.Query(this.tablename, sb.ToString(), sborder.ToString(), out recordcount);
return(new { resulttotal = recordcount, data = list });
}
internal static long Add(DateTime valueDate, string book, int officeId, int userId, long loginId, int costCenterId, string referenceNumber, string statementReference, StockMaster stockMaster, Collection <StockDetail> details, Collection <long> transactionIdCollection, Collection <Attachment> attachments)
{
if (stockMaster == null)
{
return(0);
}
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
string tranIds = ParameterHelper.CreateBigintArrayParameter(transactionIdCollection, "bigint", "@TranId");
string detail = StockMasterDetailHelper.CreateStockMasterDetailParameter(details);
string attachment = AttachmentHelper.CreateAttachmentModelParameter(attachments);
string sql = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM transactions.post_purchase(@BookName::national character varying(12), @OfficeId::integer, @UserId::integer, @LoginId::bigint, @ValueDate::date, @CostCenterId::integer, @ReferenceNumber::national character varying(12), @StatementReference::text, @IsCredit::boolean, @PartyCode::national character varying(12), @PriceTypeId::integer, @ShipperId::integer, @StoreId::integer, ARRAY[{0}]::bigint[], ARRAY[{1}], ARRAY[{2}])", tranIds, detail, attachment);
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@BookName", book);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@LoginId", loginId);
command.Parameters.AddWithValue("@ValueDate", valueDate);
command.Parameters.AddWithValue("@CostCenterId", costCenterId);
command.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
command.Parameters.AddWithValue("@StatementReference", statementReference);
command.Parameters.AddWithValue("@IsCredit", stockMaster.IsCredit);
command.Parameters.AddWithValue("@PartyCode", stockMaster.PartyCode);
if (stockMaster.PriceTypeId.Equals(0))
{
command.Parameters.AddWithValue("@PriceTypeId", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@PriceTypeId", stockMaster.PriceTypeId);
}
if (stockMaster.ShipperId.Equals(0))
{
command.Parameters.AddWithValue("@ShipperId", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
}
command.Parameters.AddWithValue("@StoreId", stockMaster.StoreId);
command.Parameters.AddRange(ParameterHelper.AddBigintArrayParameter(transactionIdCollection, "@TranId").ToArray());
command.Parameters.AddRange(StockMasterDetailHelper.AddStockMasterDetailParameter(details).ToArray());
command.Parameters.AddRange(AttachmentHelper.AddAttachmentParameter(attachments).ToArray());
long tranId = Conversion.TryCastLong(DbOperation.GetScalarValue(command));
return(tranId);
}
}
public CombinedStrategyAssembler(CombinedStrategySettings settings, bool allowRemovingInstructionRandomly)
{
if (settings == null)
{
throw new ArgumentNullException();
}
_componentSettings = settings.ComponentSettings.Where(s => s.Enabled).ToArray();
if (_componentSettings.Length == 0)
{
throw new ArgumentException("No trading strategy component is enabled in settings");
}
_allowRemovingInstructionRandomly = allowRemovingInstructionRandomly;
// verify if component settings can be used for creating new combined strategy
try
{
NewStrategy();
}
catch (Exception ex)
{
throw new InvalidOperationException("settings can't be used to create a valid combined strategy", ex);
}
// verify if components' parameter settings are correct
var components = CreateComponents().ToArray();
Debug.Assert(components.Length == _componentSettings.Length);
for (var i = 0; i < _componentSettings.Length; ++i)
{
var attributes = ParameterHelper.GetParameterAttributes(components[i]).ToArray();
var allParameterSettings = _componentSettings[i].ComponentParameterSettings;
if (allParameterSettings == null || allParameterSettings.Length == 0)
{
continue;
}
// detect duplicate names
var duplicateNames = allParameterSettings
.Select(p => p.Name)
.GroupBy(s => s)
.Where(g => g.Count() > 1)
.ToArray();
if (duplicateNames.Any())
{
throw new InvalidOperationException(
string.Format(
"duplicate parameter name {0} is found in component {0} settings",
duplicateNames.First().Key,
components[i].GetType().FullName));
}
// verify if name and valueType matches the component's parameter
foreach (var parameterSettings in allParameterSettings)
{
// varify if there is name not defined in class.
var attribute = attributes.Where(a => a.Name == parameterSettings.Name).ToArray();
if (!attribute.Any())
{
throw new InvalidOperationException(
string.Format(
"there is no parameter named {0} in component {1}",
parameterSettings.Name,
components[i].GetType().FullName));
}
// verify if ValueType is a correct type
var valueType = Type.GetType(parameterSettings.ValueType, false);
if (valueType == null)
{
throw new InvalidOperationException(
string.Format("{0} is not a valid type", parameterSettings.ValueType));
}
// verify if ValueType match the true type in class
if (attribute.First().ParameterType != valueType)
{
throw new InvalidOperationException(
string.Format(
"value type of parameter {0}: {1} does not match the definition of component {2}",
parameterSettings.Name,
parameterSettings.ValueType,
components[i].GetType().FullName));
}
// verify the values specified in parameter setting is correct
object[] values;
try
{
values = parameterSettings.GetParsedValues().ToArray();
}
catch (Exception ex)
{
throw new InvalidOperationException(
string.Format(
"Failed to parse value for component {0}, parameter {1}",
components[i].GetType().FullName,
parameterSettings.Name),
ex);
}
// associate attribute with all possible values
if (values.Length > 0)
{
_parameterValueSelectors.Add(
new ParameterValueSelector
{
Attribute = attribute.First(),
Values = values,
ValueIndex = 0
});
}
}
}
// calculate the number of parameter value combinations
NumberOfParmeterValueCombinations = 1L;
if (_parameterValueSelectors.Count > 0)
{
foreach (var selector in _parameterValueSelectors)
{
checked
{
NumberOfParmeterValueCombinations *= selector.Values.Length;
}
}
}
}
//--------------------------------------------------------------------------------
// Add
//--------------------------------------------------------------------------------
public static void AddCondition(StringBuilder sql, IList <BuildParameterInfo> parameters)
{
if (parameters.Count == 0)
{
return;
}
var addAnd = parameters
.Select(x => x.GetAttribute <ConditionAttribute>())
.Any(x => (x?.ExcludeNull ?? false) || (x?.ExcludeEmpty ?? false));
sql.Append(" WHERE");
if (addAnd)
{
sql.Append(" 1 = 1");
}
else
{
sql.Append(" ");
}
foreach (var parameter in parameters)
{
if (ParameterHelper.IsMultipleParameter(parameter.ParameterType))
{
if (addAnd)
{
sql.Append(" AND ");
}
sql.Append(parameter.ParameterName);
sql.Append(" IN ");
sql.Append($"/*@ {parameter.Name} */dummy");
}
else
{
var condition = parameter.GetAttribute <ConditionAttribute>();
var excludeNull = (condition?.ExcludeNull ?? false) || (condition?.ExcludeEmpty ?? false);
if (excludeNull)
{
if (condition.ExcludeEmpty)
{
sql.Append($"/*% if (IsNotEmpty({parameter.Name})) {{ */");
}
else
{
sql.Append($"/*% if (IsNotNull({parameter.Name})) {{ */");
}
}
if (addAnd)
{
sql.Append(" AND ");
}
sql.Append(parameter.ParameterName);
if (condition != null)
{
sql.Append($" {condition.Operand} ");
}
else
{
sql.Append(" = ");
}
sql.Append($"/*@ {parameter.Name} */dummy");
if (excludeNull)
{
sql.Append("/*% } */");
}
}
addAnd = true;
}
}
public Object ImportData(string parameters)
{
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
string path = dic["path"].ToString();
bool cover = Convert.ToBoolean(dic["cover"]);
int rowno = 0;
try
{
string ext = Path.GetExtension(path).ToLower();
//导入数据
IWorkbook workbook = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (ext == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
var sheet = workbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum + 1;
StringBuilder sb = new StringBuilder();
using (DBHelper db = new DBHelper())
{
List <string> codes = new List <string>();
for (int i = 1; i < rowcount; i++)
{
#region 检查编号重复
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
if (codes.Contains(code))
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
}
else
{
codes.Add(code);
}
#endregion
}
if (sb.Length > 0)
{
return(new { message = sb.ToString() });
}
if (cover)
{
db.Truncate(tablename);
}
for (int i = 1; i < rowcount; i++)
{
#region 逐行导入
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
//检查编号重复
if (!cover)
{
int cnt = db.Count("select count(id) as cnt from \"" + tablename + "\" where content->>'code'='" + code + "'");
if (cnt > 0)
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
continue;
}
}
string name = row.GetCell(1).GetCellValue().ToString();
string categoryname = row.GetCell(2).GetCellValue().ToString();
string standard = row.GetCell(3).GetCellValue().ToString();
string type = row.GetCell(4).GetCellValue().ToString();
string unit = row.GetCell(5).GetCellValue().ToString();
string barcode = row.GetCell(6).GetCellValue().ToString();
string area = row.GetCell(7).GetCellValue().ToString();
string comment = row.GetCell(8).GetCellValue().ToString();
var category = db.FirstOrDefault("select * from category where content->>'name'='" + categoryname
+ "' and content->>'classname'='" + tablename + "'");
Dictionary <string, object> product = new Dictionary <string, object>();
product.Add("code", code);
product.Add("name", name);
if (category != null)
{
product.Add("categoryid", category.id);
}
product.Add("standard", standard);
product.Add("type", type);
product.Add("unit", unit);
product.Add("barcode", barcode);
product.Add("area", area);
product.Add("comment", comment);
product.Add("pycode", PyConverter.IndexCode(name));
TableModel model = new TableModel()
{
content = JsonConvert.DeserializeObject <JObject>(JsonConvert.SerializeObject(product))
};
db.Add(tablename, model);
#endregion
}
if (sb.Length > 0)
{
db.Discard();
return(new { message = sb.ToString() });
}
db.SaveChanges();
}
return(new { message = "ok" });
}
catch (Exception ex)
{
//Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
return(new { message = "导入出错(" + rowno + ")" + ex.Message });
}
}
public Object LoadBillQuery(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" where content->>'billname'='{0}'", billname);
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "startdate")
{
sb.AppendFormat(" and content->>'billdate'>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and content->>'billdate'<='{0}'", f);
}
else if (field == "billcode")
{
sb.AppendFormat(" and content->>'billcode' ilike '%{0}%'", f);
}
else if (field == "vendorname")
{
DataTable dt = db.QueryTable("select id from vendor where content->>'name' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'vendorid')::int in ({0})", ids);
}
else if (field == "employeename")
{
DataTable dt = db.QueryTable("select id from employee where content->>'name' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'employeeid')::int in ({0})", ids);
}
else if (field == "comment")
{
sb.AppendFormat(" and content->>'comment' ilike '%{0}%'", f);
}
else if (field == "status")
{
if (f == "draft")
{
sb.Append(" and (content->>'auditstatus')::int=0");
}
else if (f == "audited")
{
sb.Append(" and (content->>'auditstatus')::int=1");
}
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by content->>'billdate' desc,id desc ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string sql = "select * from bill "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt from bill "
+ sb.ToString();
int recordcount = db.Count(sqlCount);
List <TableModel> list = db.Where(sql);
foreach (var item in list)
{
item.content.Add("makername", db.First("employee", item.content.Value <int>("makerid")).content.Value <string>("name"));
item.content.Add("vendorname", db.First("vendor", item.content.Value <int>("vendorid")).content.Value <string>("name"));
item.content.Add("employeename", db.First("employee", item.content.Value <int>("employeeid")).content.Value <string>("name"));
}
return(new { resulttotal = recordcount, data = list });
}
public Object List(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
StringBuilder sb = new StringBuilder();
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field.ToLower() == "state")
{
sb.AppendFormat(" and coalesce(content->>'stop','')='{0}'", f == "normal" ? "" : "t");
}
else if (field.ToLower() == "categoryid")
{
if (f != "0")
{
var ids = CategoryHelper.GetChildrenIds(Convert.ToInt32(f));
ids.Add(Convert.ToInt32(f));
var idsfilter = ids.Select(c => c.ToString()).Aggregate((a, b) => a + "," + b);
sb.AppendFormat(" and (content->>'categoryid')::int in ({0})", idsfilter);
}
}
else
{
sb.AppendFormat(" and content->>'{0}' ilike '%{1}%'", field.ToLower(), f);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
sborder.AppendFormat(" content->>'{0}' {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
if (mysorting.Count == 0)
{
sborder.Append(" order by content->>'code' ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
int recordcount = 0;
DBHelper db = new DBHelper();
List <TableModel> list = db.Query(this.tablename, sb.ToString(), sborder.ToString(), out recordcount);
foreach (var item in list)
{
if (item.content["categoryid"] == null)
{
continue;
}
var category = db.FirstOrDefault("select * from category where id=" + item.content.Value <int>("categoryid"));
if (category == null)
{
continue;
}
item.content.Add("category", category.content);
}
return(new { resulttotal = recordcount, data = list });
}
public Object RegisterPrintModel(string parameters)
{
ParameterHelper ph = new ParameterHelper(parameters);
int billid = ph.GetParameterValue <int>("billid");
DBHelper db = new DBHelper();
var bill = db.First("bill", billid);
var employee = db.First("employee", bill.content.Value <int>("employeeid"));
var maker = db.First("employee", bill.content.Value <int>("makerid"));
var vendor = db.First("vendor", bill.content.Value <int>("vendorid"));
decimal total = bill.content.Value <decimal>("total");
PrintData pd = new PrintData();
pd.HeaderField = new List <string>()
{
"公司名称",
"单据编号",
"单据日期",
"经手人",
"制单人",
"供应商名称",
"供应商联系人",
"供应商电话",
"供应商地址",
"备注",
"系统日期",
"系统时间",
"开票金额",
"开票金额大写"
};
pd.HeaderValue = new Dictionary <string, string>()
{
{ "公司名称", PluginContext.Current.Account.CompanyName },
{ "单据编号", bill.content.Value <string>("billcode") },
{ "单据日期", bill.content.Value <string>("billdate") },
{ "经手人", employee.content.Value <string>("name") },
{ "制单人", maker.content.Value <string>("name") },
{ "供应商名称", vendor.content.Value <string>("name") },
{ "供应商联系人", vendor.content.Value <string>("linkman") },
{ "供应商电话", vendor.content.Value <string>("linkmobile") },
{ "供应商地址", vendor.content.Value <string>("address") },
{ "备注", bill.content.Value <string>("comment") },
{ "系统日期", DateTime.Now.ToString("yyyy-MM-dd") },
{ "系统时间", DateTime.Now.ToString("yyyy-MM-dd HH:mm") },
{ "开票金额", total.ToString("n2") },
{ "开票金额大写", MoneyHelper.ConvertSum(total) }
};
pd.DetailField = new List <string>()
{
"行号",
"单据编号",
"单据名称",
"开票金额",
"发票号码",
"发票类型",
"备注"
};
pd.DetailValue = new List <Dictionary <string, string> >();
int i = 0;
foreach (var item in bill.content.Value <JArray>("details").Values <JObject>())
{
var bzbill = db.First("bill", item.Value <int>("billid"));
string billname = bzbill.content.Value <string>("billname");
if (billname == "purchasebill")
{
billname = "采购入库单";
}
else if (billname == "purchasebackbill")
{
billname = "采购退货单";
}
Dictionary <string, string> detail = new Dictionary <string, string>();
i++;
detail.Add("行号", i.ToString());
detail.Add("单据编号", bzbill.content.Value <string>("billcode"));
detail.Add("单据名称", billname);
detail.Add("开票金额", item.Value <decimal>("invoicetotal").ToString("n2"));
detail.Add("发票号码", item.Value <string>("invoicecode"));
detail.Add("发票类型", item.Value <string>("invoicetype"));
detail.Add("备注", item.Value <string>("comment"));
pd.DetailValue.Add(detail);
}
PrintManager pm = new PrintManager(PluginContext.Current.Account.ApplicationId);
int modelid = pm.RegisterPrintModel(pd);
return(new { modelid = modelid });
}
private object GetDefaultValue(Report report, XmlNode node, string type)
{
string value = this.ReadAttributeValue(node, "DefaultValue");
if (string.IsNullOrWhiteSpace(value))
{
return(null);
}
value = ExpressionHelper.ParseExpression(report.Tenant, value, report.DataSources, ParameterHelper.GetPraParameterInfo(report));
return(DataSourceParameterHelper.CastValue(value, type));
}
public Object ReceivableEmployeeQueryDetail(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "employeeid")
{
sb.AppendFormat(" and employeeid={0}", f);
}
else if (field == "startdate")
{
sb.AppendFormat(" and billdate>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and billdate<='{0}'", f);
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by billdate,id ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string sql = "select * from mvw_receivabledetail where 1=1 "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt from mvw_receivabledetail where 1=1 "
+ sb.ToString();
string sqlTotalSum = "select coalesce(sum(total),0) as total from mvw_receivabledetail where 1=1 " + sb.ToString();
string sqlTotalBefore = "select coalesce(sum(total),0) as total from mvw_receivabledetail where billdate<'"
+ myfilter["startdate"] + "' and employeeid=" + myfilter["employeeid"];
int recordcount = db.Count(sqlCount);
decimal totalsum = Convert.ToDecimal(db.Scalar(sqlTotalSum));
decimal totalbefore = Convert.ToDecimal(db.Scalar(sqlTotalBefore));
DataTable list = db.QueryTable(sql);
list.Columns.Add("billcode", typeof(string));
list.Columns.Add("billname", typeof(string));
list.Columns.Add("comment", typeof(string));
foreach (DataRow item in list.Rows)
{
var bill = db.First("bill", Convert.ToInt32(item["billid"]));
item["billcode"] = bill.content.Value <string>("billcode");
item["billname"] = bill.content.Value <string>("billname");
item["comment"] = bill.content.Value <string>("comment");
}
var employee = db.First("employee", Convert.ToInt32(myfilter["employeeid"]));
return(new
{
employee = employee,
totalbefore = totalbefore,
resulttotal = recordcount,
totalsum = totalsum,
data = list,
});
}
public ParameterHelperWindow(ParameterHelper parameterHelper)
{
InitializeComponent();
this.parameterHelper = parameterHelper;
}
