public int GeraNovo_NumeroNFiscal()
{
var db = new PetaPoco.Database("stringConexao");
int iNFiscal = 0;
try
{
db.BeginTransaction();
//Buscando o numero da ultima nota fiscal emitida e gerando um novo numero
iNFiscal = (this.getNumNFiscal() + 1);
//Atualizando o numero da nota fiscal no banco de dados
db.Update("Update Controle Set NFiscal_NFCe=" + iNFiscal + " Where ChvControle = 1");
Controle.getInstance.ultima_NFCe = Convert.ToInt32(iNFiscal);
db.CompleteTransaction();
}
catch (Exception)
{
db.AbortTransaction();
return(0);
}
return(iNFiscal);
}
public static bool UpdateSettings(Dictionary <string, string> dic)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
try
{
db.BeginTransaction();
foreach (var d in dic)
{
var r = db.Query <Models.Settings>(" where title=@0", d.Key).SingleOrDefault();
r.Value = d.Value;
db.Update(r);
}
db.CompleteTransaction();
return(true);
}
catch
{
db.AbortTransaction();
return(false);
}
}
}
public void Setup(SchoolCheckIn.CheckIn.Model.School school)
{
PetaPoco.Database db = new PetaPoco.Database("DatabaseConn");
db.BeginTransaction();
try{
_schoolService.School(db);
_schoolService.Setup(school);
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
throw e;
}
}
public void ImportClass(string file)
{
PetaPoco.Database db = new PetaPoco.Database("DatabaseConn");
db.BeginTransaction();
try
{
_classService.Class(db);
_classService.ImportClass(file);
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
throw e;
}
}
public void Delete(SchoolCheckIn.CheckIn.Model.Class classModel)
{
PetaPoco.Database db = new PetaPoco.Database("DatabaseConn");
db.BeginTransaction();
try
{
_classService.Class(db);
_classService.DeleteClass(classModel);
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
throw e;
}
}
public void ClearLocation(string scenenName, int locationId)
{
var db = new PetaPoco.Database("ccs");
try
{
db.BeginTransaction();
db.Execute("update location set if_cargo=0 where id=" + locationId + " and scene_name='" + scenenName + "'");
db.Execute("DELETE FROM cargo_material WHERE location_id=@0 and scene_name=@1", locationId, scenenName);
db.Execute("DELETE FROM cargo_item WHERE location_id=@0 and scene_name=@1", locationId, scenenName);
db.CompleteTransaction();
}
catch (Exception ex)
{
db.AbortTransaction();
}
}
public void ImportStudent(string file)
{
using (var db = new PetaPoco.Database("databaseConn"))
{
db.BeginTransaction();
try
{
_studentService.Student(db);
_studentService.ImportStudent(file);
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
throw e;
}
}
}
public bool UpdateStudent(StudentViewModel objViewModel)
{
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
DBConnectionString.User user = DBConnectionString.User.SingleOrDefault(objViewModel.UserId);
DBConnectionString.Student student = DBConnectionString.Student.SingleOrDefault(objViewModel.StudentId);
DBConnectionString.Profile profile = db.Query <DBConnectionString.Profile>("Select * from profile where UserId = @0", student.UserId).SingleOrDefault();
db.BeginTransaction();
try
{
if (user != null)
{
user.FirstName = objViewModel.FirstName;
user.LastName = objViewModel.LastName;
user.Update();
}
if (student != null)
{
student.ClassId = objViewModel.ClassId;
student.CourseId = objViewModel.CourseId;
student.DepartmentId = objViewModel.DepartmentId;
student.Email = objViewModel.Email;
student.FullName = objViewModel.FirstName + " " + objViewModel.LastName;
student.ModifiedBy = objViewModel.ModifiedBy;
student.ModifiedOn = objViewModel.ModifiedOn;
student.SectionId = objViewModel.SectionId;
student.Update();
}
if (profile != null)
{
profile.Title = objViewModel.Title;
profile.DateOfBirth = objViewModel.DateOfBirth;
profile.MobileNumber = objViewModel.MobileNumber;
profile.HomeTelephoneNumber = objViewModel.HomeTelephoneNumber;
profile.EmailAddress1 = objViewModel.Email;
profile.Update();
}
//var subjectIdArray = objViewModel.SubjectIds.Split(',');
//foreach (var subjectId in subjectIdArray)
//{
// DBConnectionString.UserSubject userSubject = db.Query<DBConnectionString.UserSubject>("select * from UserSubjects where UserId = @0 and SubjectId = @1", objViewModel.UserId, subjectId).SingleOrDefault();
// if (userSubject == null)
// {
// userSubject.UserId = objViewModel.UserId.Value;
// userSubject.SubjectId = Convert.ToInt64(subjectId);
// userSubject.InsertedOn = DateTime.Now;
// userSubject.InsertedBy = objViewModel.InsertedBy;
// userSubject.Update();
// }
//}
db.CompleteTransaction();
return(true);
}
catch
{
db.AbortTransaction();
return(false);
}
}
public static bool UpdateUserInfo(User model)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
var entity = db.Query <User>("Where uid=@0", model.Uid).SingleOrDefault();
/*
* var properties = typeof(User).GetProperties();
* foreach (var p in properties)
* {
* var value = p.GetValue(model, null);
* if (value != null && !string.IsNullOrWhiteSpace(value.ToString()))
* {
* p.SetValue(entity, value, null);
* }
* }
* */
entity.Email = model.Email;
entity.Homepage = model.Homepage;
entity.Location = model.Location;
entity.QQ = model.QQ;
entity.Signature = model.Signature;
entity.Introduction = model.Introduction;
entity.Money = model.Money;
entity.NickName = model.NickName;
if (string.IsNullOrWhiteSpace(model.NickName))
{
entity.NickName = model.UserName;
}
if (!string.IsNullOrWhiteSpace(model.Password))
{
entity.Password = model.Password;
}
db.BeginTransaction();
try
{
if (entity.Gid != model.Gid)
{
var mentity = db.Query <UserGroup>("Where gid=@0", model.Gid).SingleOrDefault();
var eentity = db.Query <UserGroup>("where gid=@0", entity.Gid).SingleOrDefault();
mentity.UserNum = mentity.UserNum + 1;
eentity.UserNum = eentity.UserNum - 1;
entity.Gid = model.Gid;
if (db.Update(entity) > 0 && db.Update(mentity) > 0 && db.Update(eentity) > 0)
{
db.CompleteTransaction();
return(true);
}
else
{
db.AbortTransaction();
return(false);
}
}
else
{
if (db.Update(entity) > 0)
{
db.CompleteTransaction();
return(true);
}
else
{
db.AbortTransaction();
return(false);
}
}
}
catch
{
db.AbortTransaction();
return(false);
}
}
}
public bool UpdateStudent(StudentViewModel objViewModel)
{
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
DBConnectionString.User user = DBConnectionString.User.SingleOrDefault(objViewModel.UserId);
DBConnectionString.Student student = DBConnectionString.Student.SingleOrDefault(objViewModel.StudentId);
DBConnectionString.Profile profile = db.Query<DBConnectionString.Profile>("Select * from profile where UserId = @0", student.UserId).SingleOrDefault();
db.BeginTransaction();
try
{
if (user != null)
{
user.FirstName = objViewModel.FirstName;
user.LastName = objViewModel.LastName;
user.Update();
}
if (student != null)
{
student.ClassId = objViewModel.ClassId;
student.CourseId = objViewModel.CourseId;
student.DepartmentId = objViewModel.DepartmentId;
student.Email = objViewModel.Email;
student.FullName = objViewModel.FirstName + " " + objViewModel.LastName;
student.ModifiedBy = objViewModel.ModifiedBy;
student.ModifiedOn = objViewModel.ModifiedOn;
student.SectionId = objViewModel.SectionId;
student.Update();
}
if (profile != null)
{
profile.Title = objViewModel.Title;
profile.DateOfBirth = objViewModel.DateOfBirth;
profile.MobileNumber = objViewModel.MobileNumber;
profile.HomeTelephoneNumber = objViewModel.HomeTelephoneNumber;
profile.EmailAddress1 = objViewModel.Email;
profile.Update();
}
//var subjectIdArray = objViewModel.SubjectIds.Split(',');
//foreach (var subjectId in subjectIdArray)
//{
// DBConnectionString.UserSubject userSubject = db.Query<DBConnectionString.UserSubject>("select * from UserSubjects where UserId = @0 and SubjectId = @1", objViewModel.UserId, subjectId).SingleOrDefault();
// if (userSubject == null)
// {
// userSubject.UserId = objViewModel.UserId.Value;
// userSubject.SubjectId = Convert.ToInt64(subjectId);
// userSubject.InsertedOn = DateTime.Now;
// userSubject.InsertedBy = objViewModel.InsertedBy;
// userSubject.Update();
// }
//}
db.CompleteTransaction();
return true;
}
catch
{
db.AbortTransaction();
return false;
}
}
protected override void Process()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
var discountExcelLocation = GetConfiguration().AppSettings.Settings["DiscountImport.ExcelLocation"];
var addToProductGroupMappingID = GetConfiguration().AppSettings.Settings["DiscountImport.AddToProductGroupMappingID"];
var addToMasterGroupMappingID = GetConfiguration().AppSettings.Settings["DiscountImport.AddToMasterGroupMappingID"];
var connectorIDs = GetConfiguration().AppSettings.Settings["DiscountImport.ConnectorIDs"].Value.SplitToInts(',');
if (discountExcelLocation == null || string.IsNullOrEmpty(discountExcelLocation.Value))
{
log.AuditError("The discount excel location was not specified");
return;
}
if (addToProductGroupMappingID != null && !string.IsNullOrEmpty(addToProductGroupMappingID.Value))
{
AddProductsToContentProductGroup = true;
}
var repo = new Sapph.Repositories.ExcelSalesPricesRepository(discountExcelLocation.Value);
var discountList = repo.GetDiscountList().Distinct().ToList();
if (discountList == null)
{
return;
}
var totalRows = discountList.Count;
var currentRow = 0;
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 120;
db.BeginTransaction();
try
{
var processed = new List <string>();
foreach (var discountProduct in discountList)
{
if (currentRow % 50 == 0)
{
log.AuditInfo(string.Format("Progress {0}/{1}", currentRow, totalRows));
}
currentRow++;
if (processed.Contains(discountProduct.VendorItemNumber))
{
continue;
}
var updatePriceQuery = string.Format(@"UPDATE VendorPrice SET SpecialPrice = VP.Price - ( VP.Price * {0} )
FROM Product P
INNER JOIN VendorAssortment VA ON P.ProductID = VA.ProductID
INNER JOIN VendorPrice VP ON VA.VendorAssortmentID = VP.VendorAssortmentID
WHERE P.IsConfigurable = 0 AND P.VendorItemNumber LIKE '{1}%'", discountProduct.DiscountPercentage.ToString(), discountProduct.VendorItemNumber);
db.Execute(updatePriceQuery);
if (AddProductsToContentProductGroup)
{
var deleteQuery = @"DELETE CPG
FROM ContentProductGroup CPG
INNER JOIN Product P ON CPG.ProductID = P.ProductID
WHERE P.VendorItemNumber LIKE '{0}%' AND CPG.MasterGroupMappingID = {1} AND CPG.ConnectorID = {2}";
var baseQuery = @"INSERT INTO ContentProductGroup
SELECT {0}, P.ProductID, {1}, 1, GETDATE(), NULL, NULL, 1, 0, 1, {2}
FROM Product P
INNER JOIN Content C ON P.ProductID = C.ProductID
WHERE C.ConnectorID = {0} AND P.VendorItemNumber LIKE '{3}%'";
foreach (var connectorID in connectorIDs)
{
var deleteExistingContentProductGroupsQuery = string.Format(deleteQuery,
discountProduct.VendorItemNumber,
addToMasterGroupMappingID.Value,
connectorID);
db.Execute(deleteExistingContentProductGroupsQuery);
var insertIntoContentProductGroupQuery = string.Format(baseQuery,
connectorID,
addToProductGroupMappingID.Value,
addToMasterGroupMappingID.Value,
discountProduct.VendorItemNumber);
db.Execute(insertIntoContentProductGroupQuery);
}
}
processed.Add(discountProduct.VendorItemNumber);
}
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
log.AuditError("An error occured update the special prices", e);
}
log.AuditInfo("Succesfully updated special prices with discounts.");
}
}
protected override void Process()
{
_monitoring.Notify(Name, 0);
log.InfoFormat("Start processing Shipment Notification");
var vendorIDsToProcess = VendorSettingsHelper.GetVendorIDsToExportToWehkamp(log);
foreach (var vendorID in vendorIDsToProcess)
{
_monitoring.Notify(Name, vendorID);
log.InfoFormat("Start processing Shipment Notification for VendorID {0}", vendorID);
var dummyProductIDsInFile = new List <int>();
//Get all products in shipment notification that we need to export to Wehkamp
var products = GetShipmentNotificationProductInformationData(vendorID);
if (products == null || products.Count == 0)
{
continue;
}
//Add all products to the artikelInformatie file that aren't sent to Wehkamp before
var artInfo = new artikelInformatie();
//Add all products to the aankomst file
var shipmentInfo = new aankomst();
foreach (var product in products)
{
shipmentInfo.aankomsten.Add(CreateNewShipmentItem(product, vendorID));
//Create dummy product if parentproduct isn't sent to Wehkamp (as dummy or regular product)
if ((string.IsNullOrEmpty(product.ParentSentToWehkamp) || product.ParentSentToWehkamp.ToLower(CultureInfo.InvariantCulture) == "false") && (string.IsNullOrEmpty(product.ParentSentToWehkampAsDummy) || product.ParentSentToWehkampAsDummy.ToLower(CultureInfo.InvariantCulture) == "false") && !dummyProductIDsInFile.Contains(product.ParentProductID))
{
var artikel = CreateNewDummyArtikelInformatieArtikel(product);
var sizes = GetDummyProductSizes(vendorID, product.ParentProductID);
foreach (var size in sizes)
{
artikel.maatlijst.Add(CreateNewDummyArtikelInformatieMaatGegevens(size));
}
artInfo.artikel.Add(artikel);
if (!dummyProductIDsInFile.Contains(product.ParentProductID))
{
dummyProductIDsInFile.Add(product.ParentProductID);
}
if (!dummyProductIDsInFile.Contains(product.ProductID))
{
dummyProductIDsInFile.Add(product.ProductID);
}
}
}
var orderIDs = products.Select(p => p.OrderID).Distinct().ToList();
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
db.CommandTimeout = 60;
try
{
var updateOrdersSql = string.Format("UPDATE [Order] SET IsDispatched = 1, DispatchToVendorDate = getdate() WHERE OrderID IN ({0})", string.Join(",", orderIDs.ToArray()));
db.Execute(updateOrdersSql);
foreach (var orderID in orderIDs)
{
var id = orderID;
var item = from p in products
where p.OrderID == id
select p;
var orderLineIDs = item.Select(p => p.OrderLineID).Distinct().ToList();
var updateOrderLinesSql = string.Format("UPDATE [OrderLine] SET IsDispatched = 1, DispatchedToVendorID = {1} WHERE OrderLineID IN ({0})", string.Join(",", orderLineIDs.ToArray()), vendorID);
db.Execute(updateOrderLinesSql);
}
//Save data to disk
if (artInfo.artikel.Count != 0)
{
SaveProductInformation(vendorID, artInfo, db);
//Set all dummy products as SentToAsDummyWehkamp = true
SetDummyProductsAsSentAsDummyToWehkamp(dummyProductIDsInFile, db);
}
SaveShipmentInformation(vendorID, shipmentInfo, db);
db.CompleteTransaction();
}
catch (Exception e)
{
db.AbortTransaction();
log.AuditError("Something went wrong while updating the order dispatched status or saving the files in the wehkamp drives", e);
}
}
log.InfoFormat("Finished processing Shipment Notification for VendorID {0}", vendorID);
} //end foreach (var vendorID in vendorIDsToProcess)
log.InfoFormat("Finished processing Shipment Notification");
_monitoring.Notify(Name, 1);
}