private void select_Item(purchase_packing purchase_packing, item item, app_branch app_branch)
{
if (purchase_packing.purchase_packing_detail.Where(a => a.id_item == item.id_item).FirstOrDefault() == null)
{
purchase_packing_detail _purchase_packing_detail = new purchase_packing_detail();
_purchase_packing_detail.purchase_packing = purchase_packing;
_purchase_packing_detail.item = item;
_purchase_packing_detail.quantity = 1;
_purchase_packing_detail.id_item = item.id_item;
if (app_branch != null)
{
_purchase_packing_detail.id_location = app_branch.app_location.Where(x => x.is_default).FirstOrDefault().id_location;
_purchase_packing_detail.app_location = app_branch.app_location.Where(x => x.is_default).FirstOrDefault();
}
purchase_packing.purchase_packing_detail.Add(_purchase_packing_detail);
}
else
{
purchase_packing_detail purchase_packing_detail = purchase_packing.purchase_packing_detail.Where(a => a.id_item == item.id_item).FirstOrDefault();
purchase_packing_detail.quantity += 1;
}
Dispatcher.BeginInvoke((Action)(() =>
{
purchase_packingpurchase_packinglist_detailViewSource.View.Refresh();
}));
}
public async Task <ActionResult> Create([Bind(Include = "id,bran_code,branch_name,bran_type_code,address1,address2,state,zip,assets")] app_branch app_branch)
{
ViewBag.alert = "";
//var ck = _repository.CheckCustomer(app_cus_main.gender, app_cus_main.firstname, app_cus_main.lastname, app_cus_main.middlename, app_cus_main.dob, app_cus_other_info.verification_id, app_cus_contact.tele_number);
//if (ck)
//{
// ModelState.AddModelError("", "Branch Already exist");
//}
if (ModelState.IsValid)
{
db.app_branch.Add(app_branch);
await db.SaveChangesAsync();
ViewBag.alert = "saved";
return(RedirectToAction("Index"));
}
else
{
ViewBag.alert = "error";
}
var errors = ModelState.Values.SelectMany(v => v.Errors);
ViewBag.bran_type_code = new SelectList(db.app_bran_type, "id", "name", app_branch.bran_type_code);
return(View(app_branch));
}
private void select_Item(sales_packing sales_packing, item item, app_branch app_branch)
{
if (sales_packing.sales_packing_detail.Where(a => a.id_item == item.id_item).FirstOrDefault() == null)
{
sales_packing_detail _sales_packing_detail = new sales_packing_detail();
_sales_packing_detail.sales_packing = sales_packing;
_sales_packing_detail.item = item;
_sales_packing_detail.quantity = 1;
_sales_packing_detail.id_item = item.id_item;
if (app_branch != null)
{
_sales_packing_detail.id_location = app_branch.app_location.Where(x => x.is_default).FirstOrDefault().id_location;
_sales_packing_detail.app_location = app_branch.app_location.Where(x => x.is_default).FirstOrDefault();
}
sales_packing.sales_packing_detail.Add(_sales_packing_detail);
}
else
{
sales_packing_detail sales_packing_detail = sales_packing.sales_packing_detail.Where(a => a.id_item == item.id_item).FirstOrDefault();
sales_packing_detail.quantity += 1;
}
Dispatcher.BeginInvoke((Action)(() =>
{
sales_packingsales_packinglist_detailViewSource.View.Refresh();
}));
}
public async Task <ActionResult> Edit([Bind(Include = "id,bran_code,branch_name,bran_type_code,address1,address2,state,zip,assets")] app_branch app_branch)
{
if (ModelState.IsValid)
{
db.Entry(app_branch).State = EntityState.Modified;
await db.SaveChangesAsync();
ViewBag.alert = "saved";
return(RedirectToAction("Index"));
// return RedirectToAction("Index");
}
else
{
ViewBag.alert = "error";
}
var errors = ModelState.Values.SelectMany(v => v.Errors);
ViewBag.bran_type_code = new SelectList(db.app_bran_type, "id", "name", app_branch.bran_type_code);
return(View(app_branch));
//if (ModelState.IsValid)
//{
// db.Entry(app_branch).State = EntityState.Modified;
// await db.SaveChangesAsync();
// return RedirectToAction("Index");
//}
//ViewBag.bran_type_code = new SelectList(db.app_bran_type, "id", "name", app_branch.bran_type_code);
//return View(app_branch);
}
public List <StockList> List(app_branch app_branch, app_location app_location, item_product item_product)
{
string query = @"select
parent.id_movement as MovementID,
parent.trans_date as TransDate,
parent.credit - if( sum(child.debit) > 0, sum(child.debit), 0 ) as QtyBalance,
(select sum(unit_value) from item_movement_value as parent_val where id_movement = parent.id_movement) as Cost
from item_movement as parent
inner join app_location as loc on parent.id_location = loc.id_location
left join item_movement as child on child._parent_id_movement = parent.id_movement
where {0} and parent.id_item_product = {1} and parent.status = 2 and parent.debit = 0
group by parent.id_movement
order by parent.trans_date";
string WhereQuery = "";
//This determins if we should bring cost of entire block of
if (app_location != null)
{
WhereQuery = String.Format("parent.id_location = {0}", app_location.id_location);
}
else
{
WhereQuery = String.Format("loc.id_branch = {0}", app_branch.id_branch);
}
query = String.Format(query, WhereQuery, item_product.id_item_product);
DataTable dt = exeDT(query);
return(GenerateList(dt));
}
private void cbxBranch_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (cbxBranch.SelectedItem != null)
{
app_branch app_branch = cbxBranch.SelectedItem as app_branch;
cbxLocation.ItemsSource = app_branch.app_location.ToList();
}
}
public async Task <ActionResult> DeleteConfirmed(int id)
{
app_branch app_branch = await db.app_branch.FindAsync(id);
db.app_branch.Remove(app_branch);
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
public app_location GenerateDefaultLocation(app_branch app_branch)
{
app_location app_location = new app_location();
app_location.name = "Default";
app_location.id_branch = app_branch.id_branch;
app_location.is_default = true;
app_branch.app_location.Add(app_location);
return(app_location);
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
MessageBoxResult res = MessageBox.Show("Are you sure want to delete ?", "Cognitivo", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (res == MessageBoxResult.Yes)
{
app_branch objbranch = app_branchViewSource.View.CurrentItem as entity.app_branch;
objbranch.is_active = false;
btnSave_Click(sender, e);
}
}
public static ValidationResult Checkbranch(app_branch app_branch_origin)
{
if (app_branch_origin != null)
{
return(ValidationResult.Success);
}
else
{
return(new ValidationResult("Invalid branch"));
}
}
private void btnNew_Click(object sender, RoutedEventArgs e)
{
crud_modal.Visibility = System.Windows.Visibility.Visible;
cntrl.branch objBranch = new cntrl.branch();
app_branch app_branch = new app_branch();
entity.db.app_branch.Add(app_branch);
branchViewSource.View.MoveCurrentToLast();
objBranch.app_branchViewSource = branchViewSource;
objBranch.entity = entity;
crud_modal.Children.Add(objBranch);
}
// GET: app_branch/Details/5
public async Task <ActionResult> Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
app_branch app_branch = await db.app_branch.FindAsync(id);
if (app_branch == null)
{
return(HttpNotFound());
}
return(View(app_branch));
}
// GET: app_branch/Edit/5
public async Task <ActionResult> Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
app_branch app_branch = await db.app_branch.FindAsync(id);
if (app_branch == null)
{
return(HttpNotFound());
}
ViewBag.bran_type_code = new SelectList(db.app_bran_type, "id", "name", app_branch.bran_type_code);
return(View(app_branch));
}
private void calc_Inventory()
{
app_branch app_branch = (app_branch)dgvBranch.SelectedItem;
if (app_branch != null && app_branch.id_branch > 0)
{
int BranchID = app_branch.id_branch;
Class.StockCalculations StockCalculations = new Class.StockCalculations();
inventoryViewSource = ((CollectionViewSource)(FindResource("inventoryViewSource")));
inventoryViewSource.Source = StockCalculations.ByBranch(BranchID, InventoryDate);
TextBox_TextChanged(null, null);
}
}
public Packing()
{
InitializeComponent();
item_movementViewSource = ((CollectionViewSource)(FindResource("item_movementViewSource")));
inventoryViewSource = ((CollectionViewSource)(FindResource("inventoryViewSource")));
cbxDocument.ItemsSource = entity.Brillo.Logic.Range.List_Range(dbContext, entity.App.Names.PackingList, CurrentSession.Id_Branch, CurrentSession.Id_Terminal);
sales_packing sales_packing = new sales_packing();
dbContext.sales_packing.Add(sales_packing);
sales_packingViewSource = ((CollectionViewSource)(FindResource("sales_packingViewSource")));
sales_packingViewSource.Source = dbContext.sales_packing.Local;
sales_packingViewSource.View.MoveCurrentToLast();
app_branch = dbContext.app_branch.Where(x => x.id_branch == CurrentSession.Id_Branch).FirstOrDefault();
cbxLocation.ItemsSource = app_branch.app_location.ToList();
}
public int get_DefaultLocation(app_branch app_branch)
{
int id_location = 0;
if (app_branch.app_location.Count != 0)
{
if (app_branch.app_location.Any(x => x.is_default))
{
id_location = app_branch.app_location.Where(x => x.is_default == true).FirstOrDefault().id_location;
}
else
{
id_location = app_branch.app_location.FirstOrDefault().id_location;
}
}
return(id_location);
}
public int get_Location(item_product item_product, app_branch app_branch)
{
try
{
return(get_ProductLocation(item_product, app_branch));
}
catch
{
app_location app_location = new app_location();
app_location.id_branch = app_branch.id_branch;
app_location.name = "Default of " + app_branch.name;
app_location.is_default = true;
using (db db = new db())
{
db.app_location.Add(app_location);
db.SaveChangesAsync();
return(app_location.id_location);
}
}
}
private void item_Select(object sender, EventArgs e)
{
app_branch app_branch = null;
if (sbxItem.ItemID > 0)
{
sales_packing sales_packing = sales_packingViewSource.View.CurrentItem as sales_packing;
item item = dbContext.items.Where(x => x.id_item == sbxItem.ItemID).FirstOrDefault();
if (item != null && item.id_item > 0 && sales_packing != null)
{
if (cbxBranch.SelectedItem != null)
{
app_branch = cbxBranch.SelectedItem as app_branch;
}
Task Thread = Task.Factory.StartNew(() => select_Item(sales_packing, item, app_branch));
}
}
}
private void toolBar_btnNew_Click(object sender)
{
try
{
app_branch app_branch = app_branchViewSource.View.CurrentItem as app_branch;
item_inventory item_inventory = new item_inventory();
item_inventory.IsSelected = true;
item_inventory.id_branch = app_branch.id_branch;
item_inventory.trans_date = DateTime.Now;
InventoryDB.Entry(item_inventory).State = EntityState.Added;
item_inventory.State = EntityState.Added;
app_branchViewSource.View.MoveCurrentToFirst();
app_branchapp_locationViewSource.View.MoveCurrentToFirst();
item_inventoryViewSource.View.Refresh();
item_inventoryViewSource.View.MoveCurrentToLast();
}
catch (Exception ex)
{
toolBar.msgError(ex);
}
}
public int get_ProductLocation(item_product item_product, app_branch app_branch)
{
int id_location = 0;
if (item_product != null)
{
//calculate location.
using (db db = new db())
{
if (db.item_movement.Where(x => x.item_product.id_item_product == item_product.id_item_product && x.app_location.id_branch == app_branch.id_branch).Any())
{
id_location = Convert.ToInt16(db.item_movement.Where(x => x.item_product.id_item_product == item_product.id_item_product &&
x.app_location.id_branch == app_branch.id_branch)
.FirstOrDefault().id_location);
}
else
{
id_location = get_DefaultLocation(app_branch);
}
}
}
return(id_location);
}
public void purchase()
{
string sql = " SELECT " +
" COMPRAS.CODCOMPRA, " + //0
" COMPRAS.NUMCOMPRA, " + //1
" COMPRAS.FECHACOMPRA, " + //2
" COMPRAS.TOTALDESCUENTO, " + //3
" COMPRAS.TOTALEXENTA, " + //4
" COMPRAS.TOTALGRAVADA, " + //5
" COMPRAS.TOTALIVA, " + //6
" COMPRAS.MODALIDADPAGO, " + //7
" COMPRAS.FECGRA, " + //8
" COMPRAS.ESTADO, " + //9
" COMPRAS.MOTIVOANULADO, " + //10
" COMPRAS.FECHACOMPRA, " + //11
" COMPRAS.TIMBRADOPROV, " + //12
" COMPRAS.TOTALIVA5, " + //13
" COMPRAS.TOTALIVA10, " + //14
" COMPRAS.METODO, " + //15
" COMPRAS.TOTALGRAVADO5, " + //16
" COMPRAS.TOTALGRAVADO10, " + //17
" COMPRAS.ASENTADO, " + //18
" COMPRAS.TOTALCOMPRA, " + //19
" PROVEEDOR.NOMBRE, " + //20
" PROVEEDOR.RUC_CIN, " + //21
" dbo.SUCURSAL.DESSUCURSAL, " +//28
" COMPRAS.COTIZACION1, " +
" dbo.FACTURAPAGAR.FECHAVCTO" +
" FROM COMPRAS RIGHT OUTER JOIN " +
" PROVEEDOR ON COMPRAS.CODPROVEEDOR = PROVEEDOR.CODPROVEEDOR" +
" LEFT OUTER JOIN FACTURAPAGAR ON COMPRAS.CODCOMPRA = FACTURAPAGAR.CODCOMPRA" +
" RIGHT OUTER JOIN SUCURSAL ON COMPRAS.CODSUCURSAL = SUCURSAL.CODSUCURSAL";
SqlConnection conn = new SqlConnection(_connString);
//Counts Total number of Rows we have to process
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandText = "SELECT COUNT(*) FROM COMPRAS";
cmd.CommandType = CommandType.Text;
int count = (int)cmd.ExecuteScalar();
conn.Close();
int value = 0;
Dispatcher.BeginInvoke((Action)(() => purchaseMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => purchaseValue.Text = value.ToString()));
Dispatcher.BeginInvoke((Action)(() => progPurchase.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progPurchase.Value = value));
cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
DataTable dt_purchase = exeDT(sql);
//SqlDataReader reader = cmd.ExecuteReader();
foreach (DataRow purchaserow in dt_purchase.Rows)
{
using (PurchaseInvoiceDB db = new PurchaseInvoiceDB())
{
db.Configuration.AutoDetectChangesEnabled = false;
purchase_invoice purchase_invoice = db.New();
purchase_invoice.number = purchaserow["NUMCOMPRA"] is DBNull ? null : purchaserow["NUMCOMPRA"].ToString();
if (!(purchaserow["FECHACOMPRA"] is DBNull))
{
purchase_invoice.trans_date = Convert.ToDateTime(purchaserow["FECHACOMPRA"]);
}
else
{
continue;
}
//Supplier
if (!(purchaserow["NOMBRE"] is DBNull))
{
string _customer = purchaserow["NOMBRE"].ToString();
contact contact = db.contacts.Where(x => x.name == _customer && x.id_company == id_company).FirstOrDefault();
purchase_invoice.id_contact = contact.id_contact;
}
//Condition (Cash or Credit)
if (!(purchaserow["MODALIDADPAGO"] is DBNull) && Convert.ToInt32(purchaserow["MODALIDADPAGO"]) == 0)
{
app_condition app_condition = db.app_condition.Where(x => x.name == "Contado").FirstOrDefault();
purchase_invoice.id_condition = app_condition.id_condition;
//Contract...
app_contract_detail app_contract_detail = db.app_contract_detail.Where(x => x.app_contract.id_condition == purchase_invoice.id_condition && x.app_contract.id_company == id_company).FirstOrDefault();
if (app_contract_detail != null)
{
purchase_invoice.id_contract = app_contract_detail.id_contract;
}
else
{
app_contract app_contract = GenerateDefaultContrat(app_condition, 0);
db.app_contract.Add(app_contract);
purchase_invoice.app_contract = app_contract;
purchase_invoice.id_contract = app_contract.id_contract;
}
}
else if (!(purchaserow["MODALIDADPAGO"] is DBNull) && Convert.ToInt32(purchaserow["MODALIDADPAGO"]) == 1)
{
app_condition app_condition = db.app_condition.Where(x => x.name == "Crédito" && x.id_company == id_company).FirstOrDefault();
purchase_invoice.id_condition = app_condition.id_condition;
//Contract...
if (!(purchaserow["FECHAVCTO"] is DBNull))
{
DateTime _due_date = Convert.ToDateTime(purchaserow["FECHAVCTO"]);
int interval = (_due_date - purchase_invoice.trans_date).Days;
app_contract_detail app_contract_detail = db.app_contract_detail.Where(x => x.app_contract.id_condition == purchase_invoice.id_condition && x.app_contract.id_company == id_company && x.interval == interval).FirstOrDefault();
if (app_contract_detail != null)
{
purchase_invoice.id_contract = app_contract_detail.id_contract;
}
else
{
app_contract app_contract = GenerateDefaultContrat(app_condition, interval);
db.app_contract.Add(app_contract);
purchase_invoice.app_contract = app_contract;
purchase_invoice.id_contract = app_contract.id_contract;
}
}
}
int id_location = 0;
//Branch
if (!(purchaserow["DESSUCURSAL"] is DBNull))
{
//Branch
string _branch = purchaserow["DESSUCURSAL"].ToString();
app_branch app_branch = db.app_branch.Where(x => x.name == _branch && x.id_company == id_company).FirstOrDefault();
purchase_invoice.id_branch = app_branch.id_branch;
//Location
id_location = db.app_location.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault().id_location;
//Terminal
purchase_invoice.id_terminal = db.app_terminal.Where(x => x.app_branch.id_branch == app_branch.id_branch).FirstOrDefault().id_terminal;
}
string _desMoneda = string.Empty;
//Sales Invoice Detail
string sqlDetail = "SELECT"
+ " dbo.PRODUCTOS.DESPRODUCTO as ITEM_DESPRODUCTO," //0
+ " dbo.COMPRASDETALLE.DESPRODUCTO," //1
+ " dbo.COMPRASDETALLE.CANTIDADCOMPRA, " //2
+ " dbo.COMPRASDETALLE.COSTOUNITARIO, " //3
+ " dbo.COMPRASDETALLE.IVA, " //4
+ " dbo.COMPRAS.COTIZACION1, " //5
+ " dbo.MONEDA.DESMONEDA " //6
+ " FROM dbo.COMPRAS LEFT OUTER JOIN"
+ " dbo.MONEDA ON dbo.COMPRAS.CODMONEDA = dbo.MONEDA.CODMONEDA LEFT OUTER JOIN"
+ " dbo.COMPRASDETALLE ON dbo.COMPRAS.CODCOMPRA = dbo.COMPRASDETALLE.CODCOMPRA LEFT OUTER JOIN"
+ " dbo.PRODUCTOS ON dbo.COMPRASDETALLE.CODPRODUCTO = dbo.PRODUCTOS.CODPRODUCTO"
+ " WHERE (dbo.COMPRASDETALLE.CODCOMPRA = " + purchaserow["CODCOMPRA"].ToString() + ")";
DataTable dt = exeDT(sqlDetail);
foreach (DataRow row in dt.Rows)
{
//db Related Insertion.
purchase_invoice.id_currencyfx = 1;
purchase_invoice_detail purchase_invoice_detail = new purchase_invoice_detail();
string _prod_Name = row["ITEM_DESPRODUCTO"].ToString();
if (db.items.Where(x => x.name == _prod_Name && x.id_company == id_company).FirstOrDefault() != null)
{
//Only if Item Exists
item item = db.items.Where(x => x.name == _prod_Name && x.id_company == id_company).FirstOrDefault();
purchase_invoice_detail.id_item = item.id_item;
}
if (row["DESPRODUCTO"] is DBNull)
{
//If not Item Description, then just continue out of this loop.
continue;
}
purchase_invoice_detail.item_description = row["DESPRODUCTO"].ToString();
purchase_invoice_detail.quantity = Convert.ToDecimal(row["CANTIDADCOMPRA"]);
purchase_invoice_detail.id_location = id_location;
string _iva = row["IVA"].ToString();
if (_iva == "10.00")
{
purchase_invoice_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "10%").FirstOrDefault().id_vat_group;
}
else if (_iva == "5.00")
{
purchase_invoice_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "5%").FirstOrDefault().id_vat_group;
}
else
{
if (db.app_vat_group.Where(x => x.name == "Excento").FirstOrDefault() != null)
{
purchase_invoice_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "Excento").FirstOrDefault().id_vat_group;
}
}
decimal cotiz1 = Convert.ToDecimal((row["COTIZACION1"] is DBNull) ? 1 : Convert.ToDecimal(row["COTIZACION1"]));
// purchase_invoice_detail.unit_price = (Convert.ToDecimal(row["PRECIOVENTANETO"]) / purchase_invoice_detail.quantity) / cotiz1;
if (row["COSTOUNITARIO"] is DBNull)
{
purchase_invoice_detail.unit_cost = 0;
}
else
{
purchase_invoice_detail.unit_cost = Convert.ToDecimal(row["COSTOUNITARIO"]);
}
//Commit Sales Invoice Detail
purchase_invoice.purchase_invoice_detail.Add(purchase_invoice_detail);
}
if (purchase_invoice.Error == null)
{
try
{
purchase_invoice.State = System.Data.Entity.EntityState.Added;
purchase_invoice.IsSelected = true;
// db.purchase_invoice.Add(purchase_invoice);
IEnumerable<DbEntityValidationResult> validationresult = db.GetValidationErrors();
if (validationresult.Count() == 0)
{
db.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
//Sales Brillo
//if (reader.GetInt32(10) == 1)
//{
// entity.Brillo.Approve.SalesInvoice salesBrillo = new entity.Brillo.Approve.SalesInvoice();
// salesBrillo.Start(ref db, sales_invoice);
// sales_invoice.status = 0; ?????
//}
//else if (reader.GetInt32(10))
//{
// entity.Brillo.Approve.SalesInvoice salesBrillo = new entity.Brillo.Approve.SalesInvoice();
// salesBrillo.Start(ref db, sales_invoice);
// entity.Brillo.Annul.SalesInvoice salesAnullBrillo = new entity.Brillo.Annul.SalesInvoice();
// salesAnullBrillo.Start(ref db, sales_invoice);
// sales_invoice.status = 0; ?????
//}
value += 1;
Dispatcher.BeginInvoke((Action)(() => progPurchase.Value = value));
Dispatcher.BeginInvoke((Action)(() =>purchaseValue.Text = value.ToString()));
}
else
{
//Add code to include error contacts into
purchase_invoice_ErrorList.Add(purchase_invoice);
}
}
}
// reader.Close();
cmd.Dispose();
conn.Close();
_customer_Current = _customer_Max;
}
public async Task <ActionResult> CreateImport(app_file model)
{
//Chcking if modelState is valid or not.
if (ModelState.IsValid)
{
ViewBag.alert = "";
//Create an object of Service class.
//--- UploadService service = new UploadService();
//Saved the uploaded file details to database.
//--- string fileGuid = service.SaveFileDetails(model);
//The file is then saved to physical folder.
string savedFileName = "~/Doc/" + "_" + "000_imp" + model.File.FileName;
model.File.SaveAs(Server.MapPath(savedFileName));
//The below code reads the excel file.
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", Server.MapPath(savedFileName));
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "results");
DataTable data = ds.Tables["results"];
//The excel file is validated for data entered in excel file.
// bool isValid = _repository.ValidateExcelFileData(data);
// if (isValid)
// {
//If the excel file uploaded validates to true then the data mentioned is saved to database.
foreach (DataRow row in data.Rows)
{
app_cus_main app_cus = new app_cus_main();
app_cus_contact app_contact = new app_cus_contact();
app_cus_other_info app_info = new app_cus_other_info();
app_cus_type app_type = new app_cus_type();
app_occupation app_occ = new app_occupation();
app_rel_office app_rel = new app_rel_office();
app_branch app_bra = new app_branch();
app_gender app_gen = new app_gender();
app_cus.cus_code = row["CUST_ID"].ToString();
app_cus.title = row["TITLE"].ToString();
app_cus.firstname = row["FIRST_NAME"].ToString();
app_cus.middlename = row["OTHER_NAME"].ToString();
app_cus.lastname = row["LAST_NAME"].ToString();
//CUSTOMER TYPE
//FK app_cus.cus_type_code = Convert.ToInt32(row["CUSTOMER_TYPE"]);
app_type.type_name = row["CUSTOMER_TYPE"].ToString();
//GENDER
app_gen.sex = row["GENDER"].ToString();
app_cus.dob = Convert.ToDateTime(row["DATE_BIRTH"]);
//contact
app_contact.address2 = row["HOUSE_NUMBER"].ToString();
app_contact.tele_number = row["TEL_1"].ToString();
app_contact.tele_number2 = row["TEL_2"].ToString();
app_contact.email = row["EMAIL"].ToString();
app_contact.address1 = row["ADDRESS"].ToString();
//OTHER INFO
app_info.verification_id = row["VERI_ID"].ToString();
app_info.security_group = row["SECURITY_GROUP"].ToString();
if (row["SECURITY_GROUP"].ToString() == null || row["SECURITY_GROUP"].ToString() == string.Empty)
{
app_info.security_group = "Public";
}
//OCCUPATION
//FK app_cus.occupation_code = Convert.ToInt32(row["OCCUPATION"]);
app_occ.name = row["OCCUPATION"].ToString();
app_cus.marital_status = row["MARITAL_STATUS"].ToString();
//relations office
// app_cus.rel_off_code = Convert.ToInt32(row["GENDER"]);
app_cus.rel_off_code = (int)row["REL_ID"];
//app_rel.firstname = row["RELATION_OFFICER_FIRSTNAME"].ToString();
//app_rel.lastname = row["RELATION_OFFICER_LASTNAME"].ToString();
//app_rel.middlename = row["RELATION_OFFICER_OTHERNAME"].ToString();
//BRANCH
//FK
// app_cus.bran_code = Convert.ToInt32(row["BRANCH"]);
app_bra.branch_name = row["BRANCH"].ToString();
//save to db
app_cus.created_by = _repository.GetLoginUser();
app_cus.created_date = _repository.GetCurrentDateTime();
app_cus.cus_since = _repository.GetCurrentDateTime();
app_cus.img_url = "/image/photo";
app_cus.status = "ini";
app_cus.sign_img_url = "/image/signature";
app_cus.app_cus_contact = app_contact;
app_cus.app_cus_other_info = app_info;
app_cus.app_cus_type = app_type;
app_cus.app_occupation = app_occ;
app_cus.app_rel_office = app_rel;
app_cus.app_branch = app_bra;
app_cus.app_gender = app_gen;
if (TryValidateModel(app_cus))
{
var errors = ModelState.Values.SelectMany(v => v.Errors);
db.app_cus_main.Add(app_cus);
ViewBag.alert = "saved";
await db.SaveChangesAsync();
}
else
{
var errors = ModelState.Values.SelectMany(v => v.Errors);
ViewBag.alert = "error";
return(View(app_cus));
}
// app_cus_main.cus_code = _repository.GetCustomerCode(app_cus_main.id);
//_repository.SaveUserDetails(firstname, lastname, age, email, password);
}
return(View("Index"));
// _repository.UpdateExcelStatus(Guid.Parse(fileGuid), true, string.Empty);
// }
// else
// {
// _repository.UpdateExcelStatus(Guid.Parse(fileGuid), true, "Failure");
// }
}
else
{
return(View(model));
}
}
private void sync_Company()
{
DataTable dt = exeDT("SELECT * FROM EMPRESA");
DataTable dt_Branch = exeDT("SELECT * FROM SUCURSAL");
DataTable dt_Terminal = exeDT("SELECT * FROM PC");
foreach (DataRow row in dt.Rows)
{
app_company _app_company = new app_company();
_app_company.name = row["NOMCONTRIBUYENTE"].ToString();
_app_company.alias = row["NOMFANTASIA"].ToString();
if (_app_company.name != null && _app_company.alias != null)
{
_app_company.name = _app_company.alias;
}
else
{
continue;
}
_app_company.address = (row["DIRECCION"].ToString() == "") ? "Address Placeholder" : row["DIRECCION"].ToString();
_app_company.gov_code = (row["RUCCONTRIBUYENTE"].ToString() == "") ? "GovID Placeholder" : row["RUCCONTRIBUYENTE"].ToString();
dbContext.app_company.Add(_app_company);
dbContext.SaveChanges();
id_company = _app_company.id_company;
CurrentSession.Id_Company = id_company;
Dispatcher.BeginInvoke((Action)(() =>
{
entity.Properties.Settings.Default.company_ID = id_company;
entity.Properties.Settings.Default.Save();
}
));
sync_Users();
id_user = dbContext.security_user.Where(i => i.id_company == id_company).FirstOrDefault().id_user;
CurrentSession.Id_User = id_company;
foreach (DataRow row_Branch in dt_Branch.Rows)
{
app_branch _app_branch = new app_branch();
_app_branch.id_company = id_company;
_app_branch.name = row_Branch["DESSUCURSAL"].ToString();
_app_branch.code = row_Branch["SUCURSALTIMBRADO"].ToString();
_app_branch.can_invoice = (row_Branch["TIPOSUCURSAL"].ToString().Contains("Factura")) ? true : false;
_app_branch.can_stock = (row_Branch["TIPOSUCURSAL"].ToString().Contains("Stock")) ? true : false;
if (_app_branch.can_stock)
{
app_location app_location = new app_location();
app_location.is_active = true;
app_location.is_default = true;
app_location.name = "Deposito";
_app_branch.app_location.Add(app_location);
}
string id_branchString = row_Branch["CODSUCURSAL"].ToString();
foreach (DataRow row_Terminal in dt_Terminal.Select("CODSUCURSAL = " + id_branchString))
{
app_terminal app_terminal = new app_terminal();
app_terminal.is_active = true;
app_terminal.code = row_Terminal["NUMMAQUINA"].ToString();
app_terminal.name = row_Terminal["NOMBRE"].ToString();
_app_branch.app_terminal.Add(app_terminal);
}
if (_app_branch.Error == null)
{
dbContext.app_branch.Add(_app_branch);
dbContext.SaveChanges();
}
}
id_branch = dbContext.app_branch.Where(i => i.id_company == id_company).FirstOrDefault().id_branch;
id_terminal = dbContext.app_terminal.Where(i => i.id_company == id_company).FirstOrDefault().id_terminal;
Dispatcher.BeginInvoke((Action)(() =>
{
entity.Properties.Settings.Default.branch_ID = id_branch;
entity.Properties.Settings.Default.terminal_ID = id_terminal;
entity.Properties.Settings.Default.Save();
}
));
}
dt.Clear();
dt_Branch.Clear();
dt_Terminal.Clear();
}
public void salesReturn()
{
string sql = " SELECT "
+ " DEVOLUCION.CODDEVOLUCION,"
+ " CLIENTES.NOMBRE,"
+ " VENTAS.NUMVENTA,"
+ " DEVOLUCION.CODCOMPROBANTE,"
+ " DEVOLUCION.CODVENTA,"
+ " DEVOLUCION.NUMDEVOLUCION,"
+ " DEVOLUCION.FECHADEVOLUCION, "
+ " DEVOLUCION.TOTALEXENTA,"
+ " DEVOLUCION.TOTALGRAVADA,"
+ " DEVOLUCION.TOTALIVA,"
+ " DEVOLUCION.TOTALDEVOLUCION,"
+ " DEVOLUCION.COTIZACION1,"
+ " DEVOLUCION.FECGRA,"
+ " DEVOLUCION.CODVENDEDOR, "
+ " DEVOLUCION.CODCOMPROBANTERECP,"
+ " DEVOLUCION.COBRADO,"
+ " DEVOLUCION.CODDEPOSITO,"
+ " DEVOLUCION.ESTADO,"
+ " DEVOLUCION.MOTIVOANULADO,"
+ " DEVOLUCION.MOTIVODESCARTE, "
+ " DEVOLUCION.TOTALIVA5,"
+ " DEVOLUCION.TOTALIVA10,"
+ " DEVOLUCION.TIPODEVOLUCION,"
+ " DEVOLUCION.DESCONTARMONTO,"
+ " MONEDA.DESMONEDA,"
+ " SUCURSAL.DESSUCURSAL,"
+ " DEVOLUCION.SALDO, "
+ " VENDEDOR.DESVENDEDOR"
+ " FROM DEVOLUCION INNER JOIN"
+ " CLIENTES ON DEVOLUCION.CODCLIENTE = CLIENTES.CODCLIENTE INNER JOIN"
+ " MONEDA ON DEVOLUCION.CODMONEDA = MONEDA.CODMONEDA INNER JOIN"
+ " SUCURSAL ON DEVOLUCION.CODSUCURSAL = SUCURSAL.CODSUCURSAL LEFT OUTER JOIN"
+ " VENDEDOR ON DEVOLUCION.CODVENDEDOR = VENDEDOR.CODVENDEDOR LEFT OUTER JOIN"
+ " VENTAS ON CLIENTES.CODCLIENTE = VENTAS.CODCLIENTE AND DEVOLUCION.CODVENTA = VENTAS.CODVENTA ";
SqlConnection conn = new SqlConnection(_connString);
//Counts Total number of Rows we have to process
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandText = "SELECT COUNT(*) FROM DEVOLUCION ";
cmd.CommandType = CommandType.Text;
int count = (int)cmd.ExecuteScalar();
conn.Close();
int value = 0;
Dispatcher.BeginInvoke((Action)(() => salesReturnMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => salesReturnValue.Text = value.ToString()));
Dispatcher.BeginInvoke((Action)(() => progSalesReturn.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progSalesReturn.Value = value));
cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
//SqlDataReader reader = cmd.ExecuteReader();
DataTable dt_salesReturn = exeDT(sql);
foreach (DataRow reader in dt_salesReturn.Rows)
{
using (SalesReturnDB db = new SalesReturnDB())
{
db.Configuration.AutoDetectChangesEnabled = false;
sales_return sales_return = db.New();
sales_return.id_company = id_company;
//if ((reader[6] is DBNull))
//{
// sales_invoice.is_accounted = false;
//}
//else
//{
// sales_invoice.is_accounted = (Convert.ToByte(reader[23]) == 0) ? false : true;
//}
//sales_invoice.version = 1;
sales_return.number = (reader["NUMDEVOLUCION"] is DBNull) ? null : reader["NUMDEVOLUCION"].ToString();
sales_return.trans_date = Convert.ToDateTime(reader["FECHADEVOLUCION"]);
//Customer
if (!(reader["NOMBRE"] is DBNull))
{
string _customer = reader["NOMBRE"].ToString();
contact contact = db.contacts.Where(x => x.name == _customer && x.id_company == id_company).FirstOrDefault();
if (contact != null)
{
sales_return.id_contact = contact.id_contact;
sales_return.contact = contact;
}
}
//Condition (Cash or Credit)
app_condition app_condition = db.app_condition.Where(x => x.name == "Contado").FirstOrDefault();
sales_return.id_condition = app_condition.id_condition;
if (db.app_contract.Where(x => x.name == "0 Días").Count() == 0)
{
app_contract app_contract = GenerateDefaultContrat(app_condition, 0);
db.app_contract.Add(app_contract);
sales_return.app_contract = app_contract;
sales_return.id_contract = app_contract.id_contract;
}
else
{
app_contract app_contract = db.app_contract.Where(x => x.name == "0 Días").FirstOrDefault();
sales_return.app_contract = app_contract;
sales_return.id_contract = app_contract.id_contract;
}
int id_location = 0;
app_location app_location = null;
//Branch
if (!(reader["DESSUCURSAL"] is DBNull))
{
//Branch
string _branch = reader["DESSUCURSAL"].ToString();
app_branch app_branch = db.app_branch.Where(x => x.name == _branch && x.id_company == id_company).FirstOrDefault();
sales_return.id_branch = app_branch.id_branch;
//Location
id_location = db.app_location.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault().id_location;
app_location = db.app_location.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault();
//Terminal
sales_return.id_terminal = db.app_terminal.Where(x => x.app_branch.id_branch == app_branch.id_branch).FirstOrDefault().id_terminal;
}
if (!(reader["NUMVENTA"] is DBNull))
{
string _salesNumber = reader["NUMVENTA"].ToString();
sales_invoice sales_invoice = db.sales_invoice.Where(x => x.number == _salesNumber).FirstOrDefault();
sales_return.id_sales_invoice = sales_invoice.id_sales_invoice;
// sales_return.sales_invoice = sales_invoice;
}
string _desMoneda = string.Empty;
//Sales Invoice Detail
string sqlDetail = "SELECT"
+ " dbo.PRODUCTOS.DESPRODUCTO," //0
+ " dbo.DEVOLUCIONDETALLE.CANTIDADDEVUELTA," //1
+ " dbo.DEVOLUCIONDETALLE.PRECIONETO, " //2
+ " dbo.DEVOLUCIONDETALLE.COSTOPROMEDIO, " //4
+ " dbo.DEVOLUCIONDETALLE.COSTOULTIMO, " //5
+ " dbo.DEVOLUCIONDETALLE.IVA, " //6
+ " dbo.DEVOLUCION.COTIZACION1 " //6
+ " FROM dbo.DEVOLUCION LEFT OUTER JOIN"
+ " dbo.DEVOLUCIONDETALLE ON dbo.DEVOLUCION.CODDEVOLUCION = dbo.DEVOLUCIONDETALLE.CODDEVOLUCION LEFT OUTER JOIN"
+ " dbo.PRODUCTOS ON dbo.DEVOLUCIONDETALLE.CODPRODUCTO = dbo.PRODUCTOS.CODPRODUCTO"
+ " WHERE (dbo.DEVOLUCIONDETALLE.CODDEVOLUCION = " + reader["CODDEVOLUCION"].ToString() + ")";
DataTable dt = exeDT(sqlDetail);
foreach (DataRow row in dt.Rows)
{
//db Related Insertion.
sales_return.id_currencyfx = db.app_currencyfx.Where(x => x.is_active).FirstOrDefault().id_currencyfx;
sales_return.app_currencyfx = db.app_currencyfx.Where(x => x.is_active).FirstOrDefault();
sales_return_detail sales_return_detail = new sales_return_detail();
string _prod_Name = row["DESPRODUCTO"].ToString();
item item = db.items.Where(x => x.name == _prod_Name && x.id_company == id_company).FirstOrDefault();
if (item != null)
{
sales_return_detail.id_item = item.id_item;
}
else
{
value += 1;
Dispatcher.BeginInvoke((Action)(() => progSalesReturn.Value = value));
Dispatcher.BeginInvoke((Action)(() => salesReturnValue.Text = value.ToString()));
continue;
}
sales_return_detail.id_item = item.id_item;
sales_return_detail.quantity = Convert.ToDecimal(row["CANTIDADDEVUELTA"]);
sales_return_detail.id_location = id_location;
sales_return_detail.app_location = app_location;
string _iva = row["IVA"].ToString();
if (_iva == "10.00")
{
sales_return_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "10%").FirstOrDefault().id_vat_group;
}
else if (_iva == "5.00")
{
sales_return_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "5%").FirstOrDefault().id_vat_group;
}
else
{
if (db.app_vat_group.Where(x => x.name == "Excento").FirstOrDefault() != null)
{
sales_return_detail.id_vat_group = db.app_vat_group.Where(x => x.name == "Excento").FirstOrDefault().id_vat_group;
}
}
decimal cotiz1 = Convert.ToDecimal((row["COTIZACION1"] is DBNull) ? 1 : Convert.ToDecimal(row["COTIZACION1"]));
sales_return_detail.unit_price = (Convert.ToDecimal(row["PRECIONETO"]) / sales_return_detail.quantity) / cotiz1;
if (!(row["COSTOPROMEDIO"] is DBNull))
{
sales_return_detail.unit_cost = Convert.ToDecimal(row["COSTOPROMEDIO"]);
}
//Commit Sales Invoice Detail
sales_return.sales_return_detail.Add(sales_return_detail);
}
sales_return.return_type = Status.ReturnTypes.Bonus;
if (sales_return.Error == null)
{
sales_return.State = System.Data.Entity.EntityState.Added;
sales_return.IsSelected = true;
db.sales_return.Add(sales_return);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
if (!(reader["ESTADO"] is DBNull))
{
int status = Convert.ToInt32(reader["ESTADO"]);
if (status == 0)
{
sales_return.status = Status.Documents_General.Pending;
if (!(reader[11] is DBNull))
{
sales_return.comment = reader[11].ToString();
}
}
else if (status == 1)
{
sales_return.status = Status.Documents_General.Approved;
if (!(reader[11] is DBNull))
{
sales_return.comment = reader[11].ToString();
}
db.Approve();
}
else if (status == 2)
{
sales_return.status = Status.Documents_General.Annulled;
if (!(reader[11] is DBNull))
{
sales_return.comment = reader[11].ToString();
}
db.Approve();
db.Anull();
}
}
value += 1;
Dispatcher.BeginInvoke((Action)(() => progSalesReturn.Value = value));
Dispatcher.BeginInvoke((Action)(() => salesReturnValue.Text = value.ToString()));
}
else
{
//Add code to include error contacts into
sales_return_ErrorList.Add(sales_return);
}
}
}
// reader.Close();
//cmd.Dispose();
conn.Close();
//_customer_Current = _customer_Max;
}
public string SalesInvoice(sales_invoice sales_invoice)
{
string Header = string.Empty;
string Detail = string.Empty;
string Footer = string.Empty;
string BranchName = string.Empty;
string TerminalName = string.Empty;
app_company app_company = null;
if (sales_invoice.app_company != null)
{
app_company = sales_invoice.app_company;
}
else
{
using (db db = new db())
{
if (db.app_company.Where(x => x.id_company == sales_invoice.id_company).FirstOrDefault() != null)
{
app_company = db.app_company.Where(x => x.id_company == sales_invoice.id_company).FirstOrDefault();
}
}
}
if (sales_invoice.app_branch != null)
{
BranchName = sales_invoice.app_branch.name;
}
else
{
using (db db = new db())
{
app_branch app_branch = db.app_branch.Where(x => x.id_branch == sales_invoice.id_branch).FirstOrDefault();
if (app_branch != null)
{
BranchName = app_branch.name;
}
}
}
string UserGiven = "";
if (sales_invoice.security_user != null)
{
UserGiven = sales_invoice.security_user.name;
}
else
{
using (db db = new db())
{
security_user security_user = db.security_user.Where(x => x.id_user == sales_invoice.id_user).FirstOrDefault();
if (security_user != null)
{
UserGiven = security_user.name;
}
}
}
string ContractName = "";
if (sales_invoice.app_contract != null)
{
ContractName = sales_invoice.app_contract.name;
}
else
{
using (db db = new db())
{
app_contract app_contract = db.app_contract.Where(x => x.id_contract == sales_invoice.id_contract).FirstOrDefault();
if (app_contract != null)
{
ContractName = app_contract.name;
}
}
}
string ConditionName = "";
if (sales_invoice.app_condition != null)
{
ConditionName = sales_invoice.app_condition.name;
}
else
{
using (db db = new db())
{
app_condition app_condition = db.app_condition.Where(x => x.id_condition == sales_invoice.id_condition).FirstOrDefault();
if (app_condition != null)
{
ConditionName = app_condition.name;
}
}
}
string CurrencyName = "";
if (sales_invoice.app_currencyfx != null)
{
if (sales_invoice.app_currencyfx.app_currency != null)
{
CurrencyName = sales_invoice.app_currencyfx.app_currency.name;
}
}
else
{
using (db db = new db())
{
app_currencyfx app_currencyfx = db.app_currencyfx.Where(x => x.id_currencyfx == sales_invoice.id_currencyfx).FirstOrDefault();
if (app_currencyfx != null)
{
CurrencyName = app_currencyfx.app_currency.name;
}
}
}
string TransNumber = sales_invoice.number;
DateTime TransDate = sales_invoice.trans_date;
Header =
app_company.name + "\n"
+ "RUC:" + app_company.gov_code + "\n"
+ app_company.address + "\n"
+ "***" + app_company.alias + "***" + "\n"
+ "Timbrado : " + sales_invoice.app_document_range.code + "\n"
+ "Vencimiento : " + sales_invoice.app_document_range.expire_date + "\n"
+ "--------------------------------"
+ "Descripcion, Cantiad, Precio" + "\n"
+ "--------------------------------" + "\n"
+ "\n";
foreach (sales_invoice_detail d in sales_invoice.sales_invoice_detail)
{
string ItemName = d.item.name;
string ItemCode = d.item.code;
decimal?Qty = d.quantity;
string TaskName = d.item_description;
Detail = Detail
+ ItemName + "\n"
+ Qty.ToString() + "\t" + ItemCode + "\t" + Math.Round((d.UnitPrice_Vat + d.DiscountVat), 2) + "\n";
}
decimal DiscountTotal = sales_invoice.sales_invoice_detail.Sum(x => x.Discount_SubTotal_Vat);
Footer = "--------------------------------" + "\n";
Footer += "Total Bruto : " + Math.Round((sales_invoice.GrandTotal + DiscountTotal), 2) + "\n";
Footer += "Total Descuento : -" + Math.Round(sales_invoice.sales_invoice_detail.Sum(x => x.Discount_SubTotal_Vat), 2) + "\n";
Footer += "Total " + CurrencyName + " : " + Math.Round(sales_invoice.GrandTotal, 2) + "\n";
Footer += "Fecha & Hora : " + sales_invoice.trans_date + "\n";
Footer += "Numero de Factura : " + sales_invoice.number + "\n";
Footer += "-------------------------------" + "\n";
if (sales_invoice != null)
{
List <sales_invoice_detail> sales_invoice_detail = sales_invoice.sales_invoice_detail.ToList();
if (sales_invoice_detail.Count > 0)
{
using (db db = new db())
{
var listvat = sales_invoice_detail
.Join(db.app_vat_group_details, ad => ad.id_vat_group, cfx => cfx.id_vat_group
, (ad, cfx) => new { name = cfx.app_vat.name, value = ad.unit_price * cfx.app_vat.coefficient, id_vat = cfx.app_vat.id_vat, ad })
.GroupBy(a => new { a.name, a.id_vat, a.ad })
.Select(g => new
{
vatname = g.Key.ad.app_vat_group.name,
id_vat = g.Key.id_vat,
name = g.Key.name,
value = g.Sum(a => a.value * a.ad.quantity)
}).ToList();
var VAtList = listvat.GroupBy(x => x.id_vat).Select(g => new
{
vatname = g.Max(y => y.vatname),
id_vat = g.Max(y => y.id_vat),
name = g.Max(y => y.name),
value = g.Sum(a => a.value)
}).ToList();
foreach (dynamic item in VAtList)
{
Footer += item.vatname + " : " + Math.Round(item.value, 2) + "\n";
}
Footer += "Total IVA : " + CurrencyName + " " + Math.Round(VAtList.Sum(x => x.value), 2) + "\n";
}
}
}
Footer += "-------------------------------";
Footer += "Cliente : " + sales_invoice.contact.name + "\n";
Footer += "Documento : " + sales_invoice.contact.gov_code + "\n";
Footer += "Condicion : " + ConditionName + "\n";
Footer += "-------------------------------";
Footer += "Sucursal : " + BranchName + "\n";
Footer += "Terminal : " + TerminalName;
if (sales_invoice.id_sales_rep > 0)
{
string SalesRep_Name = "";
if (sales_invoice.sales_rep == null)
{
using (db db = new db())
{
SalesRep_Name = db.sales_rep.Where(x => x.id_sales_rep == (int)sales_invoice.id_sales_rep).FirstOrDefault().name;
}
}
else
{
SalesRep_Name = sales_invoice.sales_rep.name;
}
Footer += "\n";
Footer += "Vendedor/a : " + SalesRep_Name;
}
Footer += "\n";
Footer += "Cajero/a : " + UserGiven;
string Text = Header + Detail + Footer;
return(Text);
}
public void sales()
{
string sql = " SELECT "
+ " dbo.VENTAS.CODVENTA, dbo.VENTAS.NUMVENTA, dbo.VENTAS.FECHAVENTA, dbo.VENTAS.PORCENTAJEDESCUENTO,"
+ " dbo.VENTAS.TOTALEXENTA, dbo.VENTAS.TOTALGRAVADA, dbo.VENTAS.TOTALIVA, dbo.VENTAS.TOTALDESCUENTO,"
+ " dbo.VENTAS.MODALIDADPAGO, dbo.VENTAS.FECGRA, dbo.VENTAS.ESTADO, dbo.VENTAS.MOTIVOANULADO,"
+ " dbo.VENTAS.FECHAANULADO, dbo.VENTAS.TIPOVENTA, dbo.VENTAS.TIPOPRECIO, dbo.VENTAS.NUMVENTATIMBRADO,"
+ " dbo.VENTAS.TOTAL5, dbo.VENTAS.TOTAL10, dbo.VENTAS.CODPRESUPUESTO, dbo.VENTAS.METODO, dbo.VENTAS.ENVIADO,"
+ " dbo.VENTAS.TOTALGRAVADO5, dbo.VENTAS.TOTALGRAVADO10, dbo.VENTAS.ASENTADO,"
+ " dbo.VENTAS.TOTALVENTA, dbo.VENDEDOR.DESVENDEDOR, dbo.CLIENTES.NOMBRE, dbo.CLIENTES.RUC,"
+ " dbo.SUCURSAL.DESSUCURSAL, dbo.VENTAS.COTIZACION1, FACTURACOBRAR_1.FECHAVCTO,"
+ " dbo.FACTURACOBRAR.FECHAVCTO AS Expr1, dbo.FACTURACOBRAR.SALDOCUOTA, dbo.FACTURACOBRAR.IMPORTECUOTA, "
+ " dbo.FACTURACOBRAR.COTIZACION, dbo.VENTASFORMACOBRO.IMPORTE, dbo.VENTASFORMACOBRO.DESTIPOCOBRO,"
+ " dbo.VENTASFORMACOBRO.NUMDEVOLUCION, dbo.VENTASFORMACOBRO.TIPOCOBRO"
+ " FROM dbo.SUCURSAL RIGHT OUTER JOIN"
+ " dbo.FACTURACOBRAR RIGHT OUTER JOIN"
+ " dbo.VENTAS ON dbo.FACTURACOBRAR.CODVENTA = dbo.VENTAS.CODVENTA LEFT OUTER JOIN"
+ " dbo.VENTASFORMACOBRO ON dbo.VENTAS.CODVENTA = dbo.VENTASFORMACOBRO.CODVENTA ON dbo.SUCURSAL.CODSUCURSAL = dbo.VENTAS.CODSUCURSAL"
+ " LEFT OUTER JOIN dbo.VENDEDOR ON dbo.VENTAS.CODVENDEDOR = dbo.VENDEDOR.CODVENDEDOR LEFT OUTER JOIN"
+ " dbo.CLIENTES ON dbo.VENTAS.CODCLIENTE = dbo.CLIENTES.CODCLIENTE LEFT OUTER JOIN"
+ " dbo.FACTURACOBRAR AS FACTURACOBRAR_1 ON dbo.VENTAS.CODVENTA = FACTURACOBRAR_1.CODVENTA";
SqlConnection conn = new SqlConnection(_connString);
//Counts Total number of Rows we have to process
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
DataTable dt_sales = exeDT(sql);
int count = (int)dt_sales.Rows.Count;
conn.Close();
int value = 0;
Dispatcher.BeginInvoke((Action)(() => salesMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => salesValue.Text = value.ToString()));
Dispatcher.BeginInvoke((Action)(() => progSales.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progSales.Value = value));
//Sales Invoice Detail
string sqlDetail = "SELECT"
+ " dbo.PRODUCTOS.DESPRODUCTO," //0
+ " dbo.VENTASDETALLE.CANTIDADVENTA," //1
+ " dbo.VENTASDETALLE.PRECIOVENTANETO, " //2
+ " dbo.VENTASDETALLE.PRECIOVENTALISTA, " //3
+ " dbo.VENTASDETALLE.COSTOPROMEDIO, " //4
+ " dbo.VENTASDETALLE.COSTOULTIMO, " //5
+ " dbo.VENTASDETALLE.IVA, " //6
+ " dbo.VENTAS.COTIZACION1, " //7
+ " dbo.MONEDA.DESMONEDA, " //8
+ " dbo.VENTASDETALLE.CODVENTA"
+ " FROM dbo.VENTAS LEFT OUTER JOIN"
+ " dbo.MONEDA ON dbo.VENTAS.CODMONEDA = dbo.MONEDA.CODMONEDA LEFT OUTER JOIN"
+ " dbo.VENTASDETALLE ON dbo.VENTAS.CODVENTA = dbo.VENTASDETALLE.CODVENTA LEFT OUTER JOIN"
+ " dbo.PRODUCTOS ON dbo.VENTASDETALLE.CODPRODUCTO = dbo.PRODUCTOS.CODPRODUCTO";
DataTable dt_detail = exeDT(sqlDetail);
int RoofValue = 1000;
int FloorValue = 0;
//Run a Foreach Lap
for (int i = FloorValue; i < RoofValue; i++)
{
using (SalesInvoiceDB db = new SalesInvoiceDB())
{
db.Configuration.AutoDetectChangesEnabled = false;
List <entity.app_vat_group> VATGroupList = db.app_vat_group.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.contact> ContactList = db.contacts.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.sales_rep> sales_repList = db.sales_rep.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.app_branch> BranchList = db.app_branch.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.app_location> LocationList = db.app_location.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.app_terminal> TerminalList = db.app_terminal.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.item> ItemList = db.items.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
List <entity.app_currencyfx> app_currencyfxList = db.app_currencyfx.Where(x => x.id_company == CurrentSession.Id_Company).ToList();
app_condition app_conditionCrédito = db.app_condition.Where(x => x.name == "Crédito" && x.id_company == id_company).FirstOrDefault();
app_condition app_conditionContado = db.app_condition.Where(x => x.name == "Contado" && x.id_company == id_company).FirstOrDefault();
app_currencyfx app_currencyfx = null;
if (app_currencyfxList.Where(x => x.is_active).FirstOrDefault() != null)
{
app_currencyfx = app_currencyfxList.Where(x => x.is_active).FirstOrDefault();
}
app_vat_group app_vat_group10 = VATGroupList.Where(x => x.name.Contains("10")).FirstOrDefault();
app_vat_group app_vat_group5 = VATGroupList.Where(x => x.name.Contains("5")).FirstOrDefault();
app_vat_group app_vat_group0 = VATGroupList.Where(x => x.name.Contains("0")).FirstOrDefault();
foreach (DataRow InnerRow in dt_sales.Select("CODVENTA > " + FloorValue + " AND CODVENTA < " + RoofValue + ""))
{
sales_invoice sales_invoice = new entity.sales_invoice();
sales_invoice.State = EntityState.Added;
sales_invoice.status = Status.Documents_General.Pending;
sales_invoice.IsSelected = true;
sales_invoice.trans_type = Status.TransactionTypes.Normal;
sales_invoice.trans_date = DateTime.Now.AddDays(0);
sales_invoice.timestamp = DateTime.Now;
sales_invoice.id_company = id_company;
sales_invoice.number = (InnerRow["NUMVENTA"] is DBNull) ? null : InnerRow["NUMVENTA"].ToString();
sales_invoice.trans_date = (InnerRow["FECHAVENTA"] is DBNull) ? DateTime.Now :Convert.ToDateTime(InnerRow["FECHAVENTA"]);
//Customer
if (!(InnerRow["NOMBRE"] is DBNull))
{
string _customer = InnerRow["NOMBRE"].ToString();
contact contact = ContactList.Where(x => x.name == _customer && x.id_company == id_company).FirstOrDefault();
if (contact != null)
{
sales_invoice.id_contact = contact.id_contact;
sales_invoice.contact = contact;
}
}
//Condition (Cash or Credit)
if (!(InnerRow["TIPOVENTA"] is DBNull) && Convert.ToByte(InnerRow["TIPOVENTA"]) == 0)
{
sales_invoice.id_condition = app_conditionContado.id_condition;
//Contract...
app_contract_detail app_contract_detail =
db.app_contract_detail.Where(x => x.id_company == id_company &&
x.app_contract.id_condition == app_conditionContado.id_condition)
.FirstOrDefault();
if (app_contract_detail != null)
{
sales_invoice.app_contract = app_contract_detail.app_contract;
sales_invoice.id_contract = app_contract_detail.id_contract;
}
else
{
app_contract app_contract = GenerateDefaultContrat(app_conditionContado, 0);
db.app_contract.Add(app_contract);
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
}
else if (!(InnerRow["TIPOVENTA"] is DBNull) && Convert.ToByte(InnerRow["TIPOVENTA"]) == 1)
{
sales_invoice.id_condition = app_conditionCrédito.id_condition;
//Contract...
if (!(InnerRow["FECHAVCTO"] is DBNull))
{
DateTime _due_date = Convert.ToDateTime(InnerRow["FECHAVCTO"]);
int interval = (_due_date - sales_invoice.trans_date).Days;
app_contract_detail app_contract_detail =
db.app_contract_detail.Where(x =>
x.app_contract.id_condition == sales_invoice.id_condition &&
x.app_contract.id_company == id_company &&
x.interval == interval).FirstOrDefault();
if (app_contract_detail != null)
{
sales_invoice.app_contract = app_contract_detail.app_contract;
sales_invoice.id_contract = app_contract_detail.id_contract;
}
else
{
app_contract app_contract = GenerateDefaultContrat(app_conditionCrédito, interval);
db.app_contract.Add(app_contract);
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
}
else
{
if (db.app_contract.Where(x => x.name == "0 Días").Count() == 0)
{
app_contract app_contract = GenerateDefaultContrat(app_conditionCrédito, 0);
db.app_contract.Add(app_contract);
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
else
{
app_contract app_contract = db.app_contract.Where(x => x.name == "0 Días").FirstOrDefault();
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
}
}
else
{
sales_invoice.id_condition = app_conditionContado.id_condition;
if (db.app_contract.Where(x => x.name == "0 Días").Count() == 0)
{
app_contract app_contract = GenerateDefaultContrat(app_conditionContado, 0);
db.app_contract.Add(app_contract);
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
else
{
app_contract app_contract = db.app_contract.Where(x => x.name == "0 Días").FirstOrDefault();
sales_invoice.app_contract = app_contract;
sales_invoice.id_contract = app_contract.id_contract;
}
}
//Sales Rep
if (!(InnerRow["DESVENDEDOR"] is DBNull))
{
string _sales_rep = InnerRow["DESVENDEDOR"].ToString();
sales_rep sales_rep = sales_repList.Where(x => x.name == _sales_rep && x.id_company == id_company).FirstOrDefault();
sales_invoice.id_sales_rep = sales_rep.id_sales_rep;
}
int id_location = 0;
app_location app_location = null;
//Branch
if (!(InnerRow["DESSUCURSAL"] is DBNull))
{
//Branch
string _branch = InnerRow["DESSUCURSAL"].ToString();
app_branch app_branch = BranchList.Where(x => x.name == _branch && x.id_company == id_company).FirstOrDefault();
sales_invoice.id_branch = app_branch.id_branch;
//Location
if (LocationList.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault() != null)
{
id_location = LocationList.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault().id_location;
app_location = LocationList.Where(x => x.id_branch == app_branch.id_branch && x.is_default).FirstOrDefault();
}
//Terminal
sales_invoice.id_terminal = TerminalList.Where(x => x.app_branch.id_branch == app_branch.id_branch).FirstOrDefault().id_terminal;
}
if (app_currencyfx != null)
{
sales_invoice.id_currencyfx = app_currencyfx.id_currencyfx;
sales_invoice.app_currencyfx = app_currencyfx;
}
DataTable dt_CurrentDetail = new DataTable();
if (dt_detail.Select("CODVENTA =" + InnerRow[0].ToString()).Count() > 0)
{
dt_CurrentDetail = dt_detail.Select("CODVENTA =" + InnerRow[0].ToString()).CopyToDataTable();
}
foreach (DataRow row in dt_CurrentDetail.Rows)
{
//db Related Insertion.
sales_invoice_detail sales_invoice_detail = new sales_invoice_detail();
string _prod_Name = row["DESPRODUCTO"].ToString();
item item = ItemList.Where(x => x.name == _prod_Name && x.id_company == id_company).FirstOrDefault();
sales_invoice_detail.id_item = item.id_item;
sales_invoice_detail.quantity = Convert.ToDecimal(row["CANTIDADVENTA"]);
sales_invoice_detail.id_location = id_location;
sales_invoice_detail.app_location = app_location;
string _iva = row["IVA"].ToString();
if (_iva == "10.00")
{
if (app_vat_group10 != null)
{
sales_invoice_detail.id_vat_group = app_vat_group10.id_vat_group;
}
}
else if (_iva == "5.00")
{
if (app_vat_group5 != null)
{
sales_invoice_detail.id_vat_group = app_vat_group5.id_vat_group;
}
}
else
{
if (app_vat_group0 != null)
{
sales_invoice_detail.id_vat_group = app_vat_group0.id_vat_group;
}
}
decimal cotiz1 = Convert.ToDecimal((row["COTIZACION1"] is DBNull) ? 1 : Convert.ToDecimal(row["COTIZACION1"]));
if (cotiz1 == 0)
{
cotiz1 = 1;
}
sales_invoice_detail.unit_price = (Convert.ToDecimal(row["PRECIOVENTANETO"]) / sales_invoice_detail.quantity) / cotiz1;
sales_invoice_detail.unit_cost = Convert.ToDecimal(row["COSTOPROMEDIO"]);
//Commit Sales Invoice Detail
sales_invoice.sales_invoice_detail.Add(sales_invoice_detail);
}
if (sales_invoice.Error == null)
{
sales_invoice.State = System.Data.Entity.EntityState.Added;
sales_invoice.IsSelected = true;
db.sales_invoice.Add(sales_invoice);
if (!(InnerRow["ESTADO"] is DBNull))
{
int status = Convert.ToInt32(InnerRow["ESTADO"]);
if (status == 0)
{
sales_invoice.status = Status.Documents_General.Pending;
}
else if (status == 1)
{
db.Approve(true);
sales_invoice.State = System.Data.Entity.EntityState.Modified;
sales_invoice.status = Status.Documents_General.Approved;
sales_invoice.IsSelected = true;
add_paymnet_detail(db, sales_invoice, InnerRow["SALDOCUOTA"], InnerRow["IMPORTE"]);
}
else if (status == 2)
{
sales_invoice.status = Status.Documents_General.Annulled;
if (!(InnerRow["MOTIVOANULADO"] is DBNull))
{
sales_invoice.comment = InnerRow["MOTIVOANULADO"].ToString();
}
}
try
{
db.SaveChanges();
sales_invoice.IsSelected = false;
}
catch (Exception ex)
{
throw ex;
}
}
}
else
{
//Add code to include error contacts into
SalesInvoice_ErrorList.Add(sales_invoice);
}
// }
value += 1;
Dispatcher.BeginInvoke((Action)(() => progSales.Value = value));
Dispatcher.BeginInvoke((Action)(() => salesValue.Text = value.ToString()));
}
}
FloorValue = RoofValue;
RoofValue += 1000;
}
}
public void ZReport(app_account_session app_account_session)
{
string Header = string.Empty;
string Detail = string.Empty;
string Footer = string.Empty;
string CompanyName = string.Empty;
string BranchName = string.Empty;
app_company app_company = null;
if (app_account_session.app_company != null)
{
CompanyName = app_account_session.app_company.name;
app_company = app_account_session.app_company;
}
else
{
using (db db = new db())
{
if (db.app_company.Where(x => x.id_company == app_account_session.id_company).FirstOrDefault() != null)
{
app_company = db.app_company.Where(x => x.id_company == app_account_session.id_company).FirstOrDefault();
CompanyName = app_company.name;
}
}
}
string UserName = "";
if (app_account_session.security_user != null)
{
UserName = app_account_session.security_user.name;
}
else
{
using (db db = new db())
{
if (db.security_user.Where(x => x.id_user == app_account_session.id_user).FirstOrDefault() != null)
{
security_user security_user = db.security_user.Where(x => x.id_user == app_account_session.id_user).FirstOrDefault();
UserName = security_user.name;
}
}
}
using (db db = new db())
{
if (db.app_branch.Where(x => x.id_branch == CurrentSession.Id_Branch).FirstOrDefault() != null)
{
app_branch app_branch = db.app_branch.Where(x => x.id_branch == CurrentSession.Id_Branch).FirstOrDefault();
BranchName = app_branch.name;
}
}
string SessionID = app_account_session.id_session.ToString();
DateTime OpenDate = app_account_session.op_date;
DateTime CloseDate = DateTime.Now;
if (app_account_session.cl_date != null)
{
CloseDate = (DateTime)app_account_session.cl_date;
}
Header =
"***Z Report***" + "\n"
+ CompanyName + "\t" + BranchName + "\n"
+ "R.U.C. :" + app_company.gov_code + "\n"
+ app_company.address + "\n"
+ "***" + app_company.alias + "***" + "\n"
+ "Apertura : " + OpenDate + "\n"
+ "Cierre : " + CloseDate
+ "\n"
+ "--------------------------------" + "\n"
+ "Hora Factura / Valor Moneda" + "\n"
+ "--------------------------------" + "\n";
string CustomerName = string.Empty;
foreach (app_account_detail detail in app_account_session.app_account_detail.GroupBy(x => x.id_currencyfx).Select(x => x.FirstOrDefault()).ToList())
{
Detail += "Moneda : " + detail.app_currencyfx.app_currency.name + "\n";
if (detail.tran_type == app_account_detail.tran_types.Open)
{
Detail += "Balance de Apertura : " + Math.Round(detail.credit, 2) + "\n";
}
foreach (app_account_detail d in app_account_session.app_account_detail.Where(x => x.tran_type == app_account_detail.tran_types.Transaction && x.id_currencyfx == detail.id_currencyfx).ToList())
{
string AccountName = string.Empty;
if (d.app_account == null)
{
using (db db = new db())
{
app_account app_account = db.app_account.Where(x => x.id_account == d.id_account).FirstOrDefault();
AccountName = app_account.name;
}
}
string currency = string.Empty;
if (d.app_currencyfx == null)
{
using (db db = new db())
{
currency = db.app_currencyfx.Where(x => x.id_currencyfx == d.id_currencyfx).FirstOrDefault().app_currency.name;
}
}
string InvoiceNumber = string.Empty;
string InvoiceTime = string.Empty;
payment_detail payment_detail = d.payment_detail as payment_detail;
foreach (payment_schedual payment_schedual in payment_detail.payment_schedual)
{
if (payment_schedual.sales_invoice.number != null)
{
if (!(InvoiceNumber.Contains(payment_schedual.sales_invoice.number)))
{
InvoiceNumber += payment_schedual.sales_invoice.number;
InvoiceTime = payment_schedual.sales_invoice.trans_date.ToShortTimeString();
}
}
}
decimal?value = d.credit - d.debit;
}
var listvat = app_account_session.app_account_detail.Where(x => x.tran_type == app_account_detail.tran_types.Transaction && x.id_currencyfx == detail.id_currencyfx)
.GroupBy(a => new { a.id_payment_type, a.id_currencyfx })
.Select(g => new
{
Currencyname = g.Max(x => x.app_currencyfx).app_currency.name,
paymentname = g.Max(x => x.payment_type).name,
id_currencyfx = g.Key.id_currencyfx,
id_payment_type = g.Key.id_payment_type,
value = g.Sum(a => a.credit)
}).ToList().OrderBy(x => x.id_currencyfx);
Detail += "Total de Ventas Neto :" + Math.Round(listvat.Sum(x => x.value), 2) + "\n";
foreach (dynamic item in listvat)
{
Detail += item.paymentname + "\t" + Math.Round(item.value, 2) + "\n";
}
foreach (app_account_detail account_detail in app_account_session.app_account_detail.Where(x => x.tran_type == app_account_detail.tran_types.Close && x.id_currencyfx == detail.id_currencyfx).GroupBy(x => x.id_currencyfx).Select(x => x.FirstOrDefault()).ToList())
{
Detail += "Balance de Cierre : " + Math.Round(account_detail.debit, 2);
Detail += "\n--------------------------------" + "\n";
}
Detail += "\n--------------------------------" + "\n";
}
using (db db = new db())
{
decimal amount = 0M;
int[] id_schedual = new int[10];
int index = 0;
foreach (app_account_detail account_detail in db.app_account_detail.Where(x => x.id_session == app_account_session.id_session && x.tran_type == app_account_detail.tran_types.Transaction).ToList())
{
foreach (payment_schedual payment_schedual in account_detail.payment_detail.payment_schedual.ToList())
{
if (!id_schedual.Contains(payment_schedual.parent.id_payment_schedual))
{
if (payment_schedual.parent != null)
{
id_schedual[index] = payment_schedual.parent.id_payment_schedual;
amount += payment_schedual.parent.debit;
}
else
{
amount += payment_schedual.credit;
}
}
}
}
Detail += "Total de Ventas Neto : " + Math.Round(amount, 2) + " \n";
}
Footer += "Cajero/a : " + UserName + " /n";
Footer += "--------------------------------" + " \n";
string Text = Header + Detail + Footer;
Reciept Reciept = new Reciept();
PrintDialog pd = new PrintDialog();
FlowDocument document = new FlowDocument(new Paragraph(new Run(Text)));
document.Name = "ItemMovement";
document.FontFamily = new FontFamily("Courier New");
document.FontSize = 11.0;
document.FontStretch = FontStretches.Normal;
document.FontWeight = FontWeights.Normal;
document.PagePadding = new Thickness(20);
document.PageHeight = double.NaN;
document.PageWidth = double.NaN;
//document.
//Specify minimum page sizes. Origintally 283, but was too small.
document.MinPageWidth = 283;
//Specify maximum page sizes.
document.MaxPageWidth = 300;
IDocumentPaginatorSource idpSource = document;
try
{
Nullable <bool> print = pd.ShowDialog();
if (print == true)
{
pd.PrintDocument(idpSource.DocumentPaginator, Text);
}
}
catch
{ MessageBox.Show("Output (Reciept Printer) not Found Error", "Error 101"); }
}
