/// <summary>
/// Code to delete text areas
/// </summary>
/// <param name="ObjEProposal"></param>
/// <param name="ID"></param>
public DataSet DeleteTextModuleAreas(EProposal ObjEProposal, int ID)
{
DataSet dsTextModuleAreas = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Del_TextModule]";
cmd.Parameters.AddWithValue("@TextID", ID);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Löschen der Daten");
}
else
{
throw new Exception("Error Occured While deleting the data");
}
}
finally
{
SQLCon.Close();
}
return(dsTextModuleAreas);
}
/// <summary>
/// Code to Get Form blatt articles mapping
/// </summary>
/// <param name="ObjEFormBlatt"></param>
/// <returns></returns>
public EFormBlatt GetFormBlattMapping(EFormBlatt ObjEFormBlatt)
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_FormBlattMapping]";
cmd.Parameters.Add("@ProjectID", ObjEFormBlatt.ProjectID);
Object ObjReturn = cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(Convert.ToString(ObjReturn)))
{
throw new Exception("Bitte weisen Sie dem Kostentyp 'Nachunternehmerleistungen/Stoffkosten' Warengruppen-/arten zu");
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
SQLCon.Close();
}
return(ObjEFormBlatt);
}
/// <summary>
/// Code to fetch text areas to show in a grid contol
/// </summary>
/// <param name="ObjEProposal"></param>
public DataSet GetTextModuleAreas()
{
DataSet dsTextModuleAreas = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_TextModuleAreas]";
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsTextModuleAreas);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Daten");
}
else
{
throw new Exception("Error Occured While Retreiving the data");
}
}
finally
{
SQLCon.Close();
}
return(dsTextModuleAreas);
}
/// <summary>
/// Code to fetch position list from database for Copy LVs module
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public DataSet GetOldPositionList(int ProjectID)
{
DataSet dsPositionsList = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_PositionList_Copy]";
cmd.Parameters.AddWithValue("@ProjectID", ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsPositionsList, "Positions");
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Positionsliste");
}
else
{
throw new Exception("Error Occured While Retreiving Position List");
}
}
finally
{
SQLCon.Close();
}
return(dsPositionsList);
}
/// <summary>
/// Code to get old raster if old raster if raster got changed
/// </summary>
/// <param name="_ProjectID"></param>
/// <returns></returns>
public string GetOld_LVRaster(int _ProjectID)
{
string Old_Raster = string.Empty;
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_Old_LVRaster]";
cmd.Parameters.AddWithValue("@ProjectID", _ProjectID);
object _objreturn = cmd.ExecuteScalar();
Old_Raster = Convert.ToString(_objreturn);
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Rasters");
}
else
{
throw new Exception("Error Occured While Retreiving Rasters");
}
}
finally
{
SQLCon.Close();
}
return(Old_Raster);
}
/// <summary>
/// Code to update factors from Multi5 module
/// </summary>
/// <param name="ObjEMulti"></param>
/// <returns></returns>
public EMulti UpdateMulti5(EMulti ObjEMulti)
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Upd_Multi]";
cmd.Parameters.Add("@ProjectID", ObjEMulti.ProjectID);
cmd.Parameters.Add("@LVSection", ObjEMulti.LVSection);
cmd.Parameters.Add("@dt", ObjEMulti.dtArticles);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Aktualisierung von Multi 5");
}
else
{
throw new Exception("Error while updating multi5");
}
}
finally
{
SQLCon.Close();
}
return(ObjEMulti);
}
/// <summary>
/// Code to get Article groups based on LV section for Multi5 module
/// </summary>
/// <param name="ObjEMulti"></param>
/// <returns></returns>
public EMulti GetArticleGroups(EMulti ObjEMulti)
{
try
{
ObjEMulti.dtArticles = new DataTable();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_WGForMulti]";
cmd.Parameters.Add("@ProjectID", ObjEMulti.ProjectID);
cmd.Parameters.Add("@LVSection", ObjEMulti.LVSection);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ObjEMulti.dtArticles);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Artikelgruppen");
}
else
{
throw new Exception("Error while retrieving article groups");
}
}
finally
{
SQLCon.Close();
}
return(ObjEMulti);
}
/// <summary>
/// Code to get text areas categories
/// </summary>
/// <param name="ObjEProposal"></param>
/// <param name="_textID"></param>
public DataSet GetCategories(int _textID)
{
DataSet dsCategory = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_Category]";
cmd.Parameters.AddWithValue("@TextareaID", _textID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsCategory);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Daten");
}
else
{
throw new Exception("Error Occured While Retreiving the data");
}
}
finally
{
SQLCon.Close();
}
return(dsCategory);
}
/// <summary>
/// Code to get LV rasters related to project
/// </summary>
/// <returns></returns>
public DataTable Get_LVRaster()
{
DataTable dtRaster = new DataTable();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_LVRaster]";
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dtRaster);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Rasters");
}
else
{
throw new Exception("Error Occured While Retreiving Rasters");
}
}
finally
{
SQLCon.Close();
}
return(dtRaster);
}
/// <summary>
/// Code to distribute special cost to all positions based on business logiv
/// </summary>
/// <param name="ObjEUmlage"></param>
/// <returns></returns>
public EUmlage UpdateSpecialCost(EUmlage ObjEUmlage)
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Upd_SpecialCost]";
cmd.Parameters.Add("@ProjectID", ObjEUmlage.ProjectID);
cmd.Parameters.Add("@dt", ObjEUmlage.dtSpecialCost);
cmd.Parameters.Add("@UmlageMode", ObjEUmlage.UmlageMode);
Object Objreturn = cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler bei der Verteilung der Generellen Kosten");
}
else
{
throw new Exception("Error while distributing special cost");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUmlage);
}
/// <summary>
/// Code to get LV sections for import
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public DataTable GetLVSection(int ProjectID)
{
DataTable dtLVSecton = new DataTable();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_LVSectionForImport]";
cmd.Parameters.Add("@ProjectID", ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dtLVSecton);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der LV Sektion");
}
else
{
throw new Exception("Error Occured While Retreiving LV Sections");
}
}
finally
{
SQLCon.Close();
}
return(dtLVSecton);
}
/// <summary>
/// Code to fetch PRoject, Organization and customer details at once
/// </summary>
/// <param name="ProjectID"></param>
/// <returns></returns>
public DataTable GetProjectCustomerDetails(int ProjectID)
{
DataTable dt = new DataTable();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Rpt_ProjectAndCustomerAndOTTODetails]";
cmd.Parameters.AddWithValue("@ProjectID", ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(dt);
}
}
catch (Exception ex)
{
throw;
}
finally
{
SQLCon.Close();
}
return(dt);
}
/// <summary>
/// Code to calculate estimated special cost
/// </summary>
/// <param name="ObjEUmlage"></param>
/// <returns></returns>
public EUmlage ShowUmlage(EUmlage ObjEUmlage)
{
try
{
ObjEUmlage.dtUmlage = new DataTable();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_ShowUmlage]";
cmd.Parameters.Add("@ProjectId", ObjEUmlage.ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ObjEUmlage.dtUmlage);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Umlage");
}
else
{
throw new Exception("Error while retrieving umlage");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUmlage);
}
/// <summary>
/// Code to Save report settings
/// </summary>
/// <param name="ObjEObject"></param>
public DataSet SaveReportSetting(EReportDesign ObjEReportDesign)
{
DataSet dsReportSetting = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_ReportSetting]";
cmd.Parameters.AddWithValue("@LVPosition", ObjEReportDesign.LVPosition);
cmd.Parameters.AddWithValue("@Fabrikat", ObjEReportDesign.Fabrikat);
cmd.Parameters.AddWithValue("@ArticleNr", ObjEReportDesign.ArticlNr);
cmd.Parameters.AddWithValue("@LieferantMA", ObjEReportDesign.Lieferant);
cmd.Parameters.AddWithValue("@LangText", ObjEReportDesign.LangText);
cmd.Parameters.AddWithValue("@KurzText", ObjEReportDesign.KurzText);
cmd.Parameters.AddWithValue("@Sender", ObjEReportDesign.Sender);
cmd.Parameters.AddWithValue("@Menge", ObjEReportDesign.Menge);
cmd.Parameters.AddWithValue("@GB", ObjEReportDesign.GB);
cmd.Parameters.AddWithValue("@EP", ObjEReportDesign.EP);
cmd.Parameters.AddWithValue("@Prices", ObjEReportDesign.Prices);
cmd.Parameters.AddWithValue("@MAMO", ObjEReportDesign.MAMO);
cmd.Parameters.AddWithValue("@KurzAndLangText", ObjEReportDesign.KurzAnLangText);
cmd.Parameters.AddWithValue("@HPos", ObjEReportDesign.HPos);
cmd.Parameters.AddWithValue("@ABPos", ObjEReportDesign.ABPos);
cmd.Parameters.AddWithValue("@BAPos", ObjEReportDesign.BAPos);
cmd.Parameters.AddWithValue("@VRPos", ObjEReportDesign.VRPos);
cmd.Parameters.AddWithValue("@UBPos", ObjEReportDesign.UBPos);
cmd.Parameters.AddWithValue("@NonePos", ObjEReportDesign.NonePos);
cmd.Parameters.AddWithValue("@WithDetKZ", ObjEReportDesign.WithDetailKZ);
cmd.Parameters.AddWithValue("@WithTitlePrices", ObjEReportDesign.WithTitlePrices);
cmd.Parameters.AddWithValue("@OnheMengeZeroPositions", ObjEReportDesign.OnheMengeZeroPositions);
cmd.Parameters.AddWithValue("@OnheMontagePrice", ObjEReportDesign.OnheMontagePrice);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsReportSetting);
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Speichern der Daten");
}
else
{
throw new Exception("Error while saving the data");
}
}
finally
{
SQLCon.Close();
}
return(dsReportSetting);
}
/// <summary>
/// Code to save User details while adding or editing from user master
/// </summary>
/// <param name="XmlDoc"></param>
/// <param name="ObjEUserInfo"></param>
/// <returns></returns>
public int SaveUserDetails(XmlDocument XmlDoc, EUserInfo ObjEUserInfo)
{
int UserID = -1;
DataSet ds = new DataSet();
try
{
string innerxml = XmlDoc.InnerXml;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_UserInfo]";
SqlParameter param = new SqlParameter("@XMLUserInfo", SqlDbType.Xml);
param.Value = innerxml;
cmd.Parameters.Add(param);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
string str = ds.Tables[0].Rows[0][0] == DBNull.Value ? "" : ds.Tables[0].Rows[0][0].ToString();
if (!string.IsNullOrEmpty(str))
{
if (int.TryParse(str, out UserID))
{
ObjEUserInfo.UserID = UserID;
ObjEUserInfo.dtUserInfo = ds.Tables[0];
}
else if (str.ToString().Contains("UNIQUE"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Der Nutzername existiert bereits!");
}
else
{
throw new Exception("UserName is already exists.!");
}
}
else
{
throw new Exception(str);
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
SQLCon.Close();
}
return(UserID);
}
/// <summary>
/// Code to get save Organization details
/// </summary>
/// <param name="ObjEOTTO"></param>
/// <returns></returns>
public EOTTO SaveOTTODetails(XmlDocument XmlDoc, EOTTO ObjEOTTO)
{
int OTTOID = -1;
DataSet dsOTTO = new DataSet();
try
{
string innerxml = XmlDoc.InnerXml;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_OTTODetails]";
SqlParameter param = new SqlParameter("@XMLOTTO", SqlDbType.Xml);
param.Value = innerxml;
cmd.Parameters.Add(param);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsOTTO);
}
if (dsOTTO != null && dsOTTO.Tables.Count > 0)
{
string str = Convert.ToString(dsOTTO.Tables[0].Rows[0][0]);
if (int.TryParse(str, out OTTOID))
{
ObjEOTTO.OTTOID = OTTOID;
if (dsOTTO.Tables.Count > 1)
{
ObjEOTTO.dtOTTO = dsOTTO.Tables[1];
}
}
else
{
if (str.Contains("duplicate"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Dieser Kurzname ist bereits vergeben");
}
else
{
throw new Exception("ShortName is already exists.!");
}
}
else
{
throw new Exception("Fehler beim Speichern von Daten zu OTTO");
}
}
}
}
}
catch (Exception ex) { throw; }
finally{ SQLCon.Close(); }
return(ObjEOTTO);
}
/// <summary>
/// Code to Copy a Position from Copy LVs modules
/// </summary>
/// <param name="ObjEPosition"></param>
/// <param name="stOZChar"></param>
/// <returns></returns>
public int CopyPosition(EPosition ObjEPosition, string stOZChar)
{
int ProjectID = -1;
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_CopyPosition]";
cmd.Parameters.AddWithValue("@ProjectID", ObjEPosition.ProjectID);
cmd.Parameters.AddWithValue("@ParentID", ObjEPosition.ParentID);
cmd.Parameters.AddWithValue("@PositionOZ", ObjEPosition.Position_OZ);
cmd.Parameters.AddWithValue("@SNO", ObjEPosition.SNO);
cmd.Parameters.AddWithValue("@dtCopyPosition", ObjEPosition.dtCopyPosition);
cmd.Parameters.AddWithValue("@OZID", ObjEPosition.OZID);
cmd.Parameters.AddWithValue("@OZChar", stOZChar);
object returnObj = cmd.ExecuteScalar();
if (!int.TryParse(Convert.ToString(returnObj), out ProjectID))
{
throw new Exception(Convert.ToString(returnObj));
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Diese Ordnungskennzahl existiert bereits");
}
else
{
throw new Exception("OZ Already Exists");
}
}
else
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Verschieben der Position");
}
else
{
throw new Exception("Error While Moving the position");
}
}
}
finally
{
SQLCon.Close();
}
return(ProjectID);
}
/// <summary>
/// Code to Save Project Specific Articles
/// </summary>
/// <param name="ObjEPosition"></param>
/// <returns></returns>
public EPosition SaveProjectArticle(EPosition ObjEPosition)
{
try
{
int WGID = 0;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_ProjectArticle]";
cmd.Parameters.AddWithValue("@ProjectID", ObjEPosition.ProjectID);
cmd.Parameters.AddWithValue("@WG", ObjEPosition.WG);
cmd.Parameters.AddWithValue("@WA", ObjEPosition.WA);
cmd.Parameters.AddWithValue("@WGDescription", ObjEPosition.WGDescription);
cmd.Parameters.AddWithValue("@WADescription", ObjEPosition.WADescription);
cmd.Parameters.AddWithValue("@UserID", ObjEPosition.UserID);
object returnObj = cmd.ExecuteScalar();
if (!int.TryParse(Convert.ToString(returnObj), out WGID))
{
throw new Exception(Convert.ToString(returnObj));
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Article already exists");
}
else
{
throw new Exception("Article already exists");
}
}
else
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Error While saving article");
}
else
{
throw new Exception("Error While saving article");
}
}
}
finally
{
SQLCon.Close();
}
return(ObjEPosition);
}
/// <summary>
/// Code to get invoice next blatt numbers
/// </summary>
/// <param name="ObjEInvoice"></param>
/// <returns></returns>
public EInvoice GeTBlattNumbers(EInvoice ObjEInvoice)
{
try
{
DataSet dsInvoices = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_BlattNumbers]";
cmd.Parameters.AddWithValue("@ProjectID", ObjEInvoice.ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsInvoices);
}
}
if (dsInvoices != null)
{
if (dsInvoices.Tables.Count > 0)
{
ObjEInvoice.dtBlattNumbers = dsInvoices.Tables[0];
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Datenabruf zu den Aufmassen");
}
else
{
throw new Exception("Error While Retrieving the Delivery Notes");
}
}
finally
{
SQLCon.Close();
}
}
catch (Exception ex)
{
throw;
}
return(ObjEInvoice);
}
/// <summary>
/// Code to save customer address details
/// </summary>
/// <param name="XmlDoc"></param>
/// <returns></returns>
public int SaveCustomerAddressDetails(XmlDocument XmlDoc)
{
int AddressID = -1;
try
{
string innerxml = XmlDoc.InnerXml;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_Customer_Address]";
SqlParameter param = new SqlParameter("@XMLCustomerAddress", SqlDbType.Xml);
param.Value = innerxml;
cmd.Parameters.Add(param);
object returnObj = cmd.ExecuteScalar();
if (returnObj != null)
{
if (returnObj.ToString().Contains("DefaultAddress"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Diese Standardadresse existiert bereits");
}
else
{
throw new Exception("Default Address is already exists");
}
}
if (!int.TryParse(returnObj.ToString(), out AddressID))
{
throw new Exception(returnObj.ToString());
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
SQLCon.Close();
}
return(AddressID);
}
/// <summary>
/// Code to save special cost in database
/// </summary>
/// <param name="ObjEUmlage"></param>
/// <returns></returns>
public EUmlage SaveSpecialCost(EUmlage ObjEUmlage)
{
try
{
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_SpecialCost]";
cmd.Parameters.Add("@ProjectID", ObjEUmlage.ProjectID);
cmd.Parameters.Add("@dt", ObjEUmlage.dtSpecialCost);
cmd.Parameters.Add("@UmlageMode", ObjEUmlage.UmlageMode);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
decimal DValue = 0;
if (decimal.TryParse(Convert.ToString(dt.Rows[0]["UmlageFactor"]), out DValue))
{
ObjEUmlage.UmlageFactor = DValue;
}
if (decimal.TryParse(Convert.ToString(dt.Rows[0]["UmlageValue"]), out DValue))
{
ObjEUmlage.UmlageValue = DValue;
}
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Speichern der Generalkosten");
}
else
{
throw new Exception("Error while saving special cost");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUmlage);
}
/// <summary>
/// Code to Change the user password from Profile
/// </summary>
/// <param name="ObjEUserInfo"></param>
/// <returns></returns>
public EUserInfo ResetPassword(EUserInfo ObjEUserInfo)
{
DataSet dsFeature = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Upd_Password]";
cmd.Parameters.AddWithValue("@UserID", ObjEUserInfo.UserID);
cmd.Parameters.AddWithValue("@OldPassword", ObjEUserInfo.OldPassword);
cmd.Parameters.AddWithValue("@NewPassword", ObjEUserInfo.NewPassword);
cmd.Parameters.AddWithValue("@IsAdmin", ObjEUserInfo.IsAdmin);
object Objreturn = cmd.ExecuteScalar();
string str = Convert.ToString(Objreturn);
if (!string.IsNullOrEmpty(str))
{
throw new Exception(str);
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("Valid"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Bitte geben Sie das gültige alte Passwort ein");
}
else
{
throw new Exception("Please Enter Valid Old Password");
}
}
else
{
throw new Exception("Fehler beim Zurücksetzen des Passworts");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUserInfo);
}
/// <summary>
/// Code to save customer details from customer master
/// </summary>
/// <param name="ObjECustomer"></param>
/// <returns></returns>
public int SaveCustomerDetails(XmlDocument XmlDoc)
{
int CustomerID = -1;
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_Customer]";
SqlParameter param = new SqlParameter("@XMLCustomers", SqlDbType.Xml);
param.Value = XmlDoc.InnerXml;
cmd.Parameters.Add(param);
object returnObj = cmd.ExecuteScalar();
if (returnObj != null)
{
if (returnObj.ToString().Contains("duplicate"))
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Dieser Kurzname ist bereits vergeben");
}
else
{
throw new Exception("ShortName is already exists.!");
}
}
if (!int.TryParse(returnObj.ToString(), out CustomerID))
{
throw new Exception(returnObj.ToString());
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
SQLCon.Close();
}
return(CustomerID);
}
/// <summary>
/// Code to fetch Positions for GAEB export
/// </summary>
/// <param name="ProjectID"></param>
/// <param name="strLVSection"></param>
/// <param name="_Raster"></param>
/// <returns></returns>
public DataSet GetPositionsDataForTML(int ProjectID, object strLVSection, string _Raster)
{
DataSet dsTMLData = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_PositionForTML]";
cmd.Parameters.AddWithValue("@ProjectID", ProjectID);
cmd.Parameters.AddWithValue("@LVSection", strLVSection);
cmd.Parameters.AddWithValue("@LVRaster", _Raster);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsTMLData, "TMLData");
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("RasterError"))
{
throw new Exception("Betrieb nicht möglich mit altem Raster");
}
else
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Positionsliste");
}
else
{
throw new Exception("Error Occured While Retreiving Position List");
}
}
}
finally
{
SQLCon.Close();
}
return(dsTMLData);
}
/// <summary>
/// Code to fetch non active deliveries from database
/// </summary>
/// <param name="ObjEDeliveryNotes"></param>
/// <returns></returns>
public EDeliveryNotes GetNonActiveDelivery(EDeliveryNotes ObjEDeliveryNotes)
{
DataSet dsPositions = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_NonActivedelivery]";
cmd.Parameters.AddWithValue("@ProjectID", ObjEDeliveryNotes.ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsPositions);
}
ObjEDeliveryNotes.dtNonActivedelivery = dsPositions.Tables[0];
if (ObjEDeliveryNotes.dtNonActivedelivery.Rows.Count > 0 && dsPositions.Tables[1].Rows.Count > 0)
{
ObjEDeliveryNotes.BlattID = dsPositions.Tables[1].Rows[0]["BlattID"] == DBNull.Value
? -1 : Convert.ToInt32(dsPositions.Tables[1].Rows[0]["BlattID"]);
ObjEDeliveryNotes.BlattName = dsPositions.Tables[1].Rows[0]["BlattNumber"] == DBNull.Value
? "" : dsPositions.Tables[1].Rows[0]["BlattNumber"].ToString();
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Error While Retrieving the Positions");
}
else
{
throw new Exception("Error While Retrieving the Positions");
}
}
finally
{
SQLCon.Close();
}
return(ObjEDeliveryNotes);
}
/// <summary>
/// Code to fetch special cost of a project
/// </summary>
/// <param name="ObjEUmlage"></param>
/// <returns></returns>
public EUmlage GetSpecialCost(EUmlage ObjEUmlage)
{
try
{
ObjEUmlage.dtSpecialCost = new DataTable();
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_SpecialCost]";
cmd.Parameters.Add("@ProjectID", ObjEUmlage.ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
if (ds != null && ds.Tables.Count > 0)
{
ObjEUmlage.dtSpecialCost = ds.Tables[0];
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
{
int.TryParse(Convert.ToString(ds.Tables[1].Rows[0][0]), out ObjEUmlage.UmlageMode);
}
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Generellen Kosten");
}
else
{
throw new Exception("Error while retrieving special cost");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUmlage);
}
/// <summary>
/// Code to get Organization details
/// </summary>
/// <param name="ObjEOTTO"></param>
/// <returns></returns>
public EOTTO GetOTTODetails(EOTTO ObjEOTTO)
{
DataSet dsOTTO = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_OTTODetails]";
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsOTTO);
}
if (dsOTTO != null && dsOTTO.Tables.Count > 0)
{
ObjEOTTO.dtOTTO = dsOTTO.Tables[0];
if (dsOTTO.Tables.Count > 1)
{
ObjEOTTO.dtContact = dsOTTO.Tables[1];
}
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der daten zu OTTO");
}
else
{
throw new Exception("Error Occured While Retreiving OTTO details");
}
}
finally
{
SQLCon.Close();
}
return(ObjEOTTO);
}
/// <summary>
/// Code to update values to position from Bulk proccess Section A
/// </summary>
/// <param name="ProjectID"></param>
/// <param name="tType"></param>
/// <param name="MA_Selbstkosten"></param>
/// <param name="MO_Selbstkosten"></param>
/// <param name="MA_Verkaufspreis"></param>
/// <param name="MO_Verkaufspreis"></param>
/// <param name="dt"></param>
/// <returns></returns>
public DataSet UpdateBulkProcess_ActionA(int ProjectID, string tType, decimal MA_Selbstkosten, decimal MO_Selbstkosten, decimal MA_Verkaufspreis, decimal MO_Verkaufspreis, DataTable dt)
{
DataSet dsPositionsOZList = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Upd_BulkProcess_ActionA]";
cmd.Parameters.AddWithValue("@ProjectID", ProjectID);
cmd.Parameters.AddWithValue("@Position_Type", tType);
cmd.Parameters.AddWithValue("@MA_selbstkostenMulti", MA_Selbstkosten);
cmd.Parameters.AddWithValue("@MO_selbstkostenMulti", MO_Selbstkosten);
cmd.Parameters.AddWithValue("@MA_verkaufspreis_Multi", MA_Verkaufspreis);
cmd.Parameters.AddWithValue("@MO_verkaufspreisMulti", MO_Verkaufspreis);
cmd.Parameters.AddWithValue("@Bulk_Process_ActionA_Table", dt);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsPositionsOZList, "Positions");
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler beim Laden der Positionsliste");
}
else
{
throw new Exception("Error Occured While Retreiving Position OZ List");
}
}
finally
{
SQLCon.Close();
}
return(dsPositionsOZList);
}
/// <summary>
/// Code to get Articles based on cost type mapping
/// </summary>
/// <param name="ObjEFormBlatt"></param>
/// <returns></returns>
public EFormBlatt Get_FormBlattArticles(EFormBlatt ObjEFormBlatt)
{
DataSet dsFormBlattArticle = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Get_ArticleForFormBlatt]";
cmd.Parameters.Add("@CostTypeID", ObjEFormBlatt.LookUpID);
cmd.Parameters.Add("@ProjectID", ObjEFormBlatt.ProjectID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsFormBlattArticle);
}
if (dsFormBlattArticle != null && dsFormBlattArticle.Tables.Count > 0)
{
ObjEFormBlatt.dtBlattArticles = dsFormBlattArticle.Tables[0];
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Ein Fehler ist aufgetreten beim Laden des Formulars (Artikel)");
}
else
{
throw new Exception("Error Occured While Retreiving FormBlatt Articles");
}
}
finally
{
SQLCon.Close();
}
return(ObjEFormBlatt);
}
/// <summary>
/// Code to save Role and feature mapping
/// </summary>
/// <param name="ObjEUserInfo"></param>
/// <param name="dt"></param>
/// <returns></returns>
public EUserInfo SaveFeatureMap(EUserInfo ObjEUserInfo, DataTable dt)
{
DataSet dsFeature = new DataSet();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = SQLCon.Sqlconn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[P_Ins_RoleFeatureMap]";
cmd.Parameters.Add("@RoleID", ObjEUserInfo.RoleID);
cmd.Parameters.Add("@dtFeatureMap", dt);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsFeature);
}
if (dsFeature != null && dsFeature.Tables.Count > 0)
{
ObjEUserInfo.dtFeature = dsFeature.Tables[0];
}
}
}
catch (Exception ex)
{
if (System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToString() == "de-DE")
{
throw new Exception("Fehler bei der Zuordnung einer Berechtigung zu einer Rolle");
}
else
{
throw new Exception("Error Occured While Mapping Features To Role");
}
}
finally
{
SQLCon.Close();
}
return(ObjEUserInfo);
}
