public string SaveUserMaster()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("Insert into MyMaster.dbo.UserMaster (UserCode, UserName, UserPassword, StartDate, EndDate, CreateBy, CreateDate, MobileNo, EmailId,LedgerId,UserType) \n");
strSql.Append("select N'" + Model.UserCode.Trim().Replace("'", "''") + "',N'" + Model.UserName.Trim().Replace("'", "''") + "','" + Model.UserPassword + "','" + Model.StartDate.ToString("MM/dd/yyyy") + "','" + Model.EndDate.ToString("MM/dd/yyyy") + "','" + Model.CreateBy + "',GETDATE(),'" + Model.MobileNo.Trim() + "','" + Model.EmailId.Trim() + "' ,'" + Model.LedgerId + "','" + Model.UserType + "'\n");
strSql.Append("Insert into MyMaster.[dbo].[CompanyRights] (UserCode, Initial) \n");
strSql.Append("select N'" + Model.UserCode.Trim().Replace("'", "''") + "',N'" + Model.CompanyIniTial.Trim().Replace("'", "''") + "' \n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE MyMaster.dbo.UserMaster SET UserCode=N'" + Model.UserCode.Trim().Replace("'", "''") + "',UserName = N'" + Model.UserName.Trim().Replace("'", "''") + "',UserPassword = '******',StartDate = '" + Model.StartDate.ToString("MM/dd/yyyy") + "',EndDate = '" + Model.EndDate.ToString("MM/dd/yyyy") + "',[CreateBy]='" + Model.CreateBy + "',CreateDate= GETDATE(),[MobileNo]='" + Model.MobileNo + "', [EmailId]='" + Model.EmailId + "',LedgerId='" + Model.LedgerId + "',UserType='" + Model.UserType + "' WHERE UserCode = '" + Model.UserCode + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM MyMaster.dbo.CompanyRights WHERE UserCode = '" + Model.UserCode + "' \n");
strSql.Append("DELETE FROM MyMaster.dbo.UserMaster WHERE UserCode = '" + Model.UserCode + "' \n");
}
else if (Model.Tag == "ChangePassword")
{
strSql.Append("UPDATE MyMaster.dbo.UserMaster SET UserPassword = '******' WHERE UserCode = '" + Model.UserCode + "' \n");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("END CATCH \n");
return(DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString()).ToString());
}
public string SaveUdfMaster()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @UDFCode int =(select ISNULL((Select Top 1 max(cast(UDFCode as int)) from ERP.UDFMasterEntry),0)+1 ) \n");
strSql.Append("INSERT INTO ERP.UDFMasterEntry(UDFCode, EntryModule, FieldName, FieldType, FieldWidth, MandotaryOpt, DateFormat, FieldDecimal, UdfPosition,AllowDuplicate,EnterBy,EnterDate,Gadget) \n");
strSql.Append("Select @UDFCode,'" + Model.EntryModule.Trim() + "','" + Model.FieldName.Trim() + "','" + Model.FieldType.Trim() + "','" + Model.FieldWidth + "','" + Model.MandotaryOpt + "','" + Model.DateFormat + "','" + Model.FieldDecimal + "','" + Model.UdfPosition + "','" + Model.AllowDuplicate + "','" + Model.EnterBy.Trim() + "',GETDATE(),'" + Model.Gadget + "'\n");
strSql.Append("DELETE FROM ERP.UDFDetailsEntry WHERE UDFCode=@UDFCode \n");
foreach (UDFDetailsEntryViewModel dr in this.ModelUDFDetailsEntry)
{
strSql.Append("INSERT INTO ERP.UDFDetailsEntry(UDFCode,EntryModule,FieldName,ListName) \n");
strSql.Append("Select @UDFCode,'" + Model.EntryModule + "','" + Model.FieldName + "','" + dr.ListName + "'\n");
}
strSql.Append("SET @VNo =@UDFCode");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("DELETE FROM ERP.UDFDetailsEntry WHERE UDFCode='" + Model.UDFCode + "' \n");
strSql.Append("Update ERP.UDFMasterEntry set FieldName='" + Model.FieldName.Trim() + "', FieldType='" + Model.FieldType.Trim() + "', FieldWidth='" + Model.FieldWidth + "', MandotaryOpt='" + Model.MandotaryOpt + "', DateFormat='" + Model.DateFormat + "', FieldDecimal='" + Model.FieldDecimal + "', UdfPosition='" + Model.UdfPosition + "', AllowDuplicate='" + Model.AllowDuplicate + "',Gadget='" + Model.Gadget + "' where UDFCode='" + Model.UDFCode + "' \n");
foreach (UDFDetailsEntryViewModel dr in this.ModelUDFDetailsEntry)
{
strSql.Append("INSERT INTO ERP.UDFDetailsEntry(UDFCode,EntryModule,FieldName,ListName) \n");
strSql.Append("Select '" + Model.UDFCode + "','" + Model.EntryModule.Trim() + "','" + Model.FieldName.Trim() + "','" + dr.ListName.Trim() + "'\n");
}
strSql.Append("SET @VNo ='" + Model.UDFCode + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.UDFDetailsEntry WHERE UDFCode = '" + Model.UDFCode + "' \n");
strSql.Append("DELETE FROM ERP.UDFMasterEntry WHERE UDFCode = '" + Model.UDFCode + "' \n");
strSql.Append("SET @VNo ='1'");
}
this.ModelUDFDetailsEntry.Clear();
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveUserRestriction(string usercode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
strSql.Append("DELETE FROM [MyMaster].[dbo].[UserRestriction] WHERE UserCode = '" + usercode + "' \n");
foreach (UserRestrictionViewModel det in Model)
{
strSql.Append("INSERT INTO [MyMaster].[dbo].[UserRestriction]([UserCode],[IniTial],[AccessSalesRateChange],[AccessSalesTermChange],[AccessPurchaseRateChange],[AccessPurchaseTermChange]) \n");
strSql.Append("Select '" + det.UserCode.Trim() + "','" + det.IniTial.Trim() + "','" + det.AccessSalesRateChange + "','" + det.AccessSalesTermChange + "','" + det.AccessPurchaseRateChange + "','" + det.AccessPurchaseTermChange + "' \n");
}
Model.Clear();
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveBillOfMaterial()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
strSql.Append("Set @VoucherNo ='" + Model.BillOfMaterialId + "' \n");
if (Model.Tag == "NEW")
{
strSql.Append("INSERT INTO ERP.BillOfMaterialMaster(BillOfMaterialId, BillOfMaterialDesc, DepartmentId1, DepartmentId2, DepartmentId3, DepartmentId4, BranchId, CompanyUnitId, Remarks, EnterBy, EnterDate, Gadget, EntryFromProject) \n");
strSql.Append("Select @VoucherNo,N'" + Model.BillOfMaterialDesc + "'," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ", " + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + " ," + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + "," + (string.IsNullOrEmpty(Model.Remarks) ? "null" : "'" + Model.Remarks + "'") + ", '" + Model.EnterBy + "', GETDATE(),'" + Model.Gadget + "', '" + Model.EntryFromProject + "' \n");
strSql.Append("Update ERP.DocumentNumbering set DocCurrentNo = DocCurrentNo + 1 where DocId =" + Model.DocId + "\n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("DELETE FROM [ERP].[BillOfMaterialFinished] WHERE BillOfMaterialId ='" + Model.BillOfMaterialId + "' \n");
strSql.Append("DELETE FROM [ERP].[BillOfMaterialDetails] WHERE BillOfMaterialId ='" + Model.BillOfMaterialId + "' \n");
strSql.Append("UPDATE ERP.BillOfMaterialMaster SET BillOfMaterialDesc= N'" + Model.BillOfMaterialDesc + "',DepartmentId1=" + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ",DepartmentId2= " + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + " , DepartmentId3=" + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ",DepartmentId4= " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ",BranchId= " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + ",CompanyUnitId=" + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", Remarks=" + (string.IsNullOrEmpty(Model.Remarks) ? "null" : "'" + Model.Remarks + "'") + ",EnterBy= '" + Model.EnterBy + "',EnterDate= GETDATE(),Gadget='" + Model.Gadget + "',EntryFromProject= '" + Model.EntryFromProject + "' Where BillOfMaterialId='" + Model.BillOfMaterialId + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM [ERP].[BillOfMaterialFinished] WHERE BillOfMaterialId ='" + Model.BillOfMaterialId + "' \n");
strSql.Append("DELETE FROM [ERP].[BillOfMaterialDetails] WHERE BillOfMaterialId ='" + Model.BillOfMaterialId + "' \n");
strSql.Append("DELETE FROM [ERP].[BillOfMaterialMaster] WHERE BillOfMaterialId ='" + Model.BillOfMaterialId + "' \n");
strSql.Append("SET @VoucherNo ='1'");
}
if (Model.Tag != "DELETE")
{
foreach (BillOfMaterialDetailsViewModel det in ModelDetails)
{
strSql.Append("INSERT INTO ERP.BillOfMaterialDetails(BillOfMaterialId, SNO, ProductId, GodownId, CostCenterDetailId, AltQty, AltProductUnitId, Qty, ProductUnitId) \n");
strSql.Append("Select @VoucherNo, '" + det.SNO + "', '" + det.ProductId + "', " + ((det.GodownId == 0) ? "null" : "'" + det.GodownId + "'") + ", " + ((det.CostCenterDetailId == 0) ? "null" : "'" + det.CostCenterDetailId + "'") + ",'" + det.AltQty + "'," + ((det.AltProductUnitId == 0) ? "null" : "'" + det.AltProductUnitId + "'") + ", '" + det.Qty + "', " + ((det.ProductUnitId == 0) ? "null" : "'" + det.ProductUnitId + "'") + " \n");
}
foreach (BillOfMaterialFinishedViewModel det in ModelFinished)
{
strSql.Append("INSERT INTO ERP.BillOfMaterialFinished(BillOfMaterialId, SNO, ProductId, GodownId, CostCenterId, AltQty, AltProductUnitId, Qty, ProductUnitId,FinishedCosting) \n");
strSql.Append("Select @VoucherNo, '" + det.SNO + "', '" + det.ProductId + "', " + ((det.GodownId == 0) ? "null" : "'" + det.GodownId + "'") + ", " + ((det.CostCenterId == 0) ? "null" : "'" + det.CostCenterId + "'") + ",'" + det.AltQty + "'," + ((det.AltProductUnitId == 0) ? "null" : "'" + det.AltProductUnitId + "'") + ", '" + det.Qty + "', " + ((det.ProductUnitId == 0) ? "null" : "'" + det.ProductUnitId + "'") + " ,'" + det.FinishedCosting + "'\n");
}
}
ModelDetails.Clear();
ModelFinished.Clear();
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VoucherNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VoucherNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveSalesman()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @SalesmanId int=(select ISNULL((Select Top 1 max(cast(SalesmanId as int)) from ERP.Salesman),0)+1) \n");
strSql.Append("Insert into ERP.Salesman(SalesmanId, SalesmanDesc, SalesmanShortName, SalesmanPicture, SalesmanPictureUrl, Address, Country, PhoneNo, MobileNo, EmailId, Fax, CommissionRate, CreditLimit, CreditDays, CreditType, ExpiryDate, LedgerId, MainSalesmanId, SalesmanType, Status, EnterBy, EnterDate,MemberTypeId, MembershipId, MemberFromDate, MemberToDate,Gadget) \n");
strSql.Append("Select @SalesmanId,N'" + Model.SalesmanDesc.Trim().Replace("'", "''") + "',N'" + Model.SalesmanShortName.Trim().Replace("'", "''") + "', null,null, " + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + "," + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + "," + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + "," + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + "," + ((Model.Fax == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + "," + Model.CommissionRate + "," + Model.CreditLimit + "," + Model.CreditDays + ",null,null," + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + "," + ((Model.MainSalesmanId == 0) ? "null" : "'" + Model.MainSalesmanId + "'") + "," + ((Model.SalesmanType == "") ? "null" : "'" + Model.SalesmanType + "'") + ", '" + Model.Status.ToString().ToLower() + "', '" + Model.EnterBy + "', GETDATE(),\n");
strSql.Append(" " + ((Model.MemberTypeId == 0) ? "null" : "N'" + Model.MemberTypeId + "'") + " ," + ((Model.MembershipId == "") ? "null" : "N'" + Model.MembershipId.Trim() + "'") + "," + ((Model.MemberFromDate == null) ? "null" : "'" + Convert.ToDateTime(Model.MemberFromDate).ToString("yyyy-MM-dd") + "'") + "," + ((Model.MemberToDate == null) ? "null" : "'" + Convert.ToDateTime(Model.MemberToDate).ToString("yyyy-MM-dd") + "'") + ",'" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@SalesmanId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.Salesman SET SalesmanDesc=N'" + Model.SalesmanDesc.Trim().Replace("'", "''") + "',SalesmanShortName = N'" + Model.SalesmanShortName.Trim().Replace("'", "''") + "', Address=" + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ",Country=" + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ",PhoneNo=" + "" + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ", \n");
strSql.Append("Fax =" + ((Model.Fax == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + ",MobileNo=" + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + ",EmailId=" + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + ",LedgerId=" + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ",MainSalesmanId=" + ((Model.MainSalesmanId == 0) ? "null" : "'" + Model.MainSalesmanId + "'") + ",SalesmanType=" + ((Model.SalesmanType == "") ? "null" : "'" + Model.SalesmanType + "'") + ",CommissionRate=" + Model.CommissionRate + ",\n");
strSql.Append(" CreditLimit =" + Model.CreditLimit + ",CreditDays=" + Model.CreditDays + ", Status='" + Model.Status.ToString().ToLower() + "',EnterBy= '" + Model.EnterBy + "',Gadget='" + Model.Gadget + "' WHERE SalesmanId = '" + Model.SalesmanId + "' \n");
strSql.Append("SET @VNo ='" + Model.SalesmanId + "'");
}
else if (Model.Tag == "DELETE")
{
if (Model.SalesmanType == "Member")
{
strSql.Append("DELETE FROM ERP.GeneralLedger WHERE SalesmanId = '" + Model.SalesmanId + "' \n");
}
strSql.Append("DELETE FROM ERP.Salesman WHERE SalesmanId = '" + Model.SalesmanId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public void SaveMenuPermission(string Group)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("DELETE FROM MyMaster.dbo.MenuPremissionGroup where PremissionGroupName='" + Group + "' \n");
foreach (MenuPermissionGroupViewModel det in this.Model)
{
strSql.Append("INSERT INTO MyMaster.dbo.MenuPremissionGroup ([MainForm],[FormName],[DisplayName],[Odr],[Access],[MenuId],[Module],[PageUrl],[PageId],[PremissionGroupName]) \n");
strSql.Append("Select '" + det.MainForm + "','" + det.FormName + "','" + det.DisplayName + "','" + det.Odr + "','" + det.Access + "','" + det.MenuId + "', '" + det.Module + "',NULL,NULL,'" + det.PremissionGroupName + "' \n");
}
DAL.ExecuteNonQuery(System.Data.CommandType.Text, strSql.ToString());
}
public string SaveProductScheme()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @SchemeId int=(select ISNULL((Select Top 1 max(SchemeId) from ERP.SpecialRateSchemeMaster),0)+1) \n");
strSql.Append("INSERT INTO [ERP].[SpecialRateSchemeMaster]([SchemeId],[SchemeName],[EnterBy],[EnterDate],[SchemeWise]) \n");
strSql.Append("Select @SchemeId,'" + Model.SchemeName.Trim() + "'," + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",GETDATE(),'" + Model.SchemeWise + "' \n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE [ERP].[SpecialRateSchemeMaster] SET [SchemeName] = '" + Model.SchemeName + "', \n");
strSql.Append("[EnterBy] = " + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",[EnterDate] = GETDATE(),[SchemeWise] = '" + Model.SchemeWise + "'\n");
strSql.Append("WHERE [SchemeId]= @SchemeId \n");
strSql.Append("DELETE FROM [ERP].[[SpecialRateSchemeDetails]] WHERE SchemeId ='" + Model.SchemeId + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM [ERP].[SpecialRateSchemeMaster] WHERE SchemeId ='" + Model.SchemeId + "' \n");
strSql.Append("DELETE FROM [ERP].[SpecialRateSchemeDetails] WHERE SchemeId = '" + Model.SchemeId + "' \n");
strSql.Append("SET @VoucherNo ='1'");
DetailsSchemeModel.Clear();
}
foreach (ProductSchemeDetailsViewModel det in this.DetailsSchemeModel)
{
strSql.Append("INSERT INTO [ERP].[SpecialRateSchemeDetails]([SchemeId],[ProductId],[ProductGrpId],[ProductSubGrpId],[StartDate],[EndDate],[DiscountPercent],[SchemeRate],[BranchId],[CompanyUnitId]) \n");
strSql.Append("Select @SchemeId,'" + det.ProductId + "'," + ((det.ProductGrpId == 0) ? "null" : "'" + det.ProductGrpId + "'") + "," + ((det.ProductSubGrpId == 0) ? "null" : "'" + det.ProductSubGrpId + "'") + ",'" + det.StartDate.ToString("yyyy-MM-dd") + "','" + det.EndDate.ToString("yyyy-MM-dd") + "','" + det.DiscountPercent + "','" + det.SchemeRate + "', " + ((det.BranchId == 0) ? "null" : "'" + det.BranchId + "'") + "," + ((det.CompanyUnitId == 0) ? "null" : "'" + det.CompanyUnitId + "'") + " \n");
}
this.DetailsSchemeModel.Clear();
strSql.Append("SET @VNo =@SchemeId");
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveKOTAssign()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @KOTId int=(select ISNULL((Select Top 1 max(cast(KOTId as int)) from ERP.KOTAssign),0)+1) \n");
foreach (KOTAssignViewModel det in ModelKOTAssign)
{
strSql.Append("Insert into ERP.KOTAssign(KOTId, Sno, StartNo, EndNo, Waiter, KOTDate, KOTMiti, UsedNo, BranchId, CompanyUnitId, CounterId, Gadget,Status,EnterBy,EnterDate) \n");
strSql.Append("select @KOTId,'" + det.Sno + "','" + det.StartNo + "','" + det.EndNo + "',N'" + det.Waiter.Trim().Replace("'", "''") + "','" + det.KOTDate.ToString("yyyy-MM-dd") + "','" + det.KOTMiti.Trim() + "'," + ((det.UsedNo == 0) ? "null" : "'" + det.UsedNo + "'") + ", " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + "," + ((Model.CounterId == 0) ? "null" : "'" + Model.CounterId + "'") + ",'" + Model.Gadget + "','" + Model.Status.ToString().ToLower() + "', '" + Model.EnterBy + "',GETDATE() \n");
}
strSql.Append("SET @VNo =@KOTId");
}
else if (Model.Tag == "KOTCLOSE")
{
foreach (KOTAssignViewModel det in ModelKOTAssign)
{
strSql.Append("Update ERP.KOTAssign set UsedNo='" + det.UsedNo + "' Where sno='" + det.Sno + "' and KOTId='" + det.KOTId + "' \n");
strSql.Append("SET @VNo ='" + det.KOTId + "'");
}
}
//else if (Model.Tag == "DELETE")
//{
// strSql.Append("DELETE FROM ERP.Counter WHERE KOTId = '" + Model.KOTId + "' \n");
// strSql.Append("SET @VNo ='1'");
//}
ModelKOTAssign.Clear();
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveAccountSubGroup()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @AccountSubGrpId int=(select ISNULL((Select Top 1 max(cast(AccountSubGrpId as int)) from ERP.AccountSubGroup),0)+1) \n");
strSql.Append("Insert into ERP.AccountSubGroup(AccountSubGrpId,AccountSubGrpDesc,AccountSubGrpShortName,AccountGrpId,[Status],EnterBy,EnterDate,Gadget) \n");
strSql.Append("select @AccountSubGrpId,N'" + Model.AccountSubGrpDesc.Trim().Replace("'", "''") + "',N'" + Model.AccountSubGrpShortName.Trim().Replace("'", "''") + "','" + Model.AccountGrpId + "','" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE(),'" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@AccountSubGrpId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.AccountSubGroup SET AccountSubGrpDesc=N'" + Model.AccountSubGrpDesc.Trim().Replace("'", "''") + "',AccountSubGrpShortName = N'" + Model.AccountSubGrpShortName.Trim().Replace("'", "''") + "',AccountGrpId = '" + Model.AccountGrpId + "',[Status]='" + Model.Status.ToString().ToLower() + "',Gadget='" + Model.Gadget + "' WHERE AccountSubGrpId = '" + Model.AccountSubGrpId + "' \n");
strSql.Append("SET @VNo ='" + Model.AccountSubGrpId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.AccountSubGroup WHERE AccountSubGrpId = '" + Model.AccountSubGrpId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveArea()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @AreaId int=(select ISNULL((Select Top 1 max(cast(AreaId as int)) from ERP.Area),0)+1) \n");
strSql.Append("Insert into ERP.Area([AreaId],[AreaDesc],[AreaShortName],[Country],[Location],[MainAreaId],[Status],[EnterBy],[EnterDate],Gadget) \n");
strSql.Append("select @AreaId,N'" + Model.AreaDesc.Trim().Replace("'", "''") + "',N'" + Model.AreaShortName.Trim().Replace("'", "''") + "',N'" + Model.Country.Trim().Replace("'", "''") + "',N'" + Model.Location.Trim().Replace("'", "''") + "'," + ((Model.MainAreaId == 0) ? "null" : "'" + Model.MainAreaId + "'") + ",'" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE(),'" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@AreaId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.Area SET AreaDesc=N'" + Model.AreaDesc.Trim().Replace("'", "''") + "',AreaShortName = N'" + Model.AreaShortName.Trim().Replace("'", "''") + "',Country = N'" + Model.Country.Trim().Replace("'", "''") + "',Location = N'" + Model.Location.Trim().Replace("'", "''") + "',MainAreaId = " + ((Model.MainAreaId == 0) ? "null" : "'" + Model.MainAreaId + "'") + ",[Status]='" + Model.Status.ToString().ToLower() + "',Gadget='" + Model.Gadget + "' WHERE AreaId = '" + Model.AreaId + "' \n");
strSql.Append("SET @VNo ='" + Model.AreaId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.Area WHERE AreaId = '" + Model.AreaId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveLogin()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
//if (Model.Tag == "NEW")
//{
strSql.Append("declare @LogInId int=(select ISNULL((Select Top 1 max(cast(LogInId as int)) from UserLogin),0)+1) \n");
strSql.Append("Insert into UserLogin(LogInId, LoginUserName, LoginPassword) \n");
strSql.Append("Select @LogInId,'" + Model.LoginUserName.Trim() + "','" + Model.LoginPassword.Trim() + "'\n");
strSql.Append("SET @VNo =@LogInId");
// }
//else if (Model.Tag == "EDIT")
//{
// strSql.Append("UPDATE UserLogin SET [LoginUserName] = '" + Model.LoginUserName.Trim() + "',[BranchShortName] = '" + Model.LoginPassword.Trim() + "' where LoginId='"+Model.LoginId+"'");
// strSql.Append("SET @VNo ='" + Model.LoginId + "'");
//}
//else if (Model.Tag == "DELETE")
//{
// strSql.Append("DELETE FROM UserLogin WHERE BranchId = '" + Model.LoginId + "' \n");
// strSql.Append("SET @VNo ='1'");
//}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveCostCenter()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @CostCenterId int=(select ISNULL((Select Top 1 max(cast(CostCenterId as int)) from ERP.CostCenter),0)+1) \n");
strSql.Append("Insert into ERP.CostCenter(CostCenterId, CostCenterDesc, CostCenterShortName, Address, Country, PhoneNo,MobileNo, ContactPerson, ContactPersonAdd, ContPersonPhoneNo, LedgerId, Status, EnterBy, EnterDate,Gadget) \n");
strSql.Append("select @CostCenterId,N'" + Model.CostCenterDesc.Trim().Replace("'", "''") + "',N'" + Model.CostCenterShortName.Trim().Replace("'", "''") + "','" + Model.Address + "','" + Model.Country.Trim() + "', \n");
strSql.Append("'" + Model.PhoneNo.Trim() + "','" + Model.MobileNo.Trim() + "','" + Model.ContactPerson.Trim() + "','" + Model.ContactPersonAdd.Trim() + "','" + Model.ContPersonPhoneNo.Trim() + "'," + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ",'" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE(),Gadget='" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@CostCenterId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.CostCenter SET CostCenterDesc=N'" + Model.CostCenterDesc.Trim().Replace("'", "''") + "',CostCenterShortName = N'" + Model.CostCenterShortName.Trim().Replace("'", "''") + "',Address = '" + Model.Address + "',Country = '" + Model.Country.Trim() + "',PhoneNo = '" + Model.PhoneNo.Trim() + "',MobileNo = '" + Model.MobileNo.Trim() + "',ContactPerson ='" + Model.ContactPerson.Trim() + "',ContactPersonAdd ='" + Model.ContactPersonAdd.Trim() + "',ContPersonPhoneNo ='" + Model.ContPersonPhoneNo.Trim() + "',LedgerId=" + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ",[Status]='" + Model.Status.ToString().ToLower() + "',Gadget='" + Model.Gadget + "' WHERE CostCenterId = '" + Model.CostCenterId + "' \n");
strSql.Append("SET @VNo ='" + Model.CostCenterId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.CostCenter WHERE CostCenterId = '" + Model.CostCenterId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveSubledger()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @SubledgerId int=(select ISNULL((Select Top 1 max(cast(SubledgerId as int)) from ERP.Subledger),0)+1) \n");
strSql.Append("insert into [ERP].[SubLedger] (SubledgerId, SubledgerDesc, SubledgerShortName, SubledgerType, SubledgerPicture, SubledgerPictureUrl, LedgerId, Address, Country, NationalId, PhoneNo, MobileNo, EmailId, PanNo, Fax, InterestRate, Status, EnterBy, EnterDate, BankAccountNo,Gadget)\n");
strSql.Append("Select @SubledgerId,N'" + Model.SubledgerDesc.Trim().Replace("'", "''") + "',N'" + Model.SubledgerShortName.Trim().Replace("'", "''") + "', 'Account', null, null, " + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + "," + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + "," + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + "," + ((Model.NationalId == "") ? "null" : "'" + Model.NationalId.Trim().Replace("'", "''") + "'") + "," + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + "," + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + "," + ((Model.PanNo == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + "," + ((Model.Fax == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + " , " + Model.InterestRate + ", '" + Model.Status.ToString().ToLower() + "', '" + Model.EnterBy + "', GETDATE()," + ((Model.BankAccountNo == "") ? "null" : "N'" + Model.BankAccountNo.Trim().Replace("'", "''") + "'") + " ,'" + Model.Gadget + "'\n");
strSql.Append("SET @VNo =@SubledgerId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.Subledger SET SubledgerDesc=N'" + Model.SubledgerDesc.Trim().Replace("'", "''") + "',SubledgerShortName = N'" + Model.SubledgerShortName.Trim().Replace("'", "''") + "',[Status]='" + Model.Status.ToString().ToLower() + "', LedgerId=" + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ",Address= " + ((Model.Address == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ", Country= " + ((Model.Country == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ",NationalId= " + ((Model.NationalId == "") ? "null" : "N'" + Model.NationalId.Trim().Replace("'", "''") + "'") + ", PhoneNo= " + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ", MobileNo= " + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + ", EmailId=" + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + ", PanNo= " + ((Model.PanNo == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + ",Fax= " + ((Model.Fax == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + " , InterestRate=" + Model.InterestRate + " ,BankAccountNo= " + ((Model.BankAccountNo == "") ? "null" : "N'" + Model.BankAccountNo.Trim().Replace("'", "''") + "'") + " ,Gadget='" + Model.Gadget + "' WHERE SubledgerId = '" + Model.SubledgerId + "' \n");
strSql.Append("SET @VNo ='" + Model.SubledgerId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.Subledger WHERE SubledgerId = '" + Model.SubledgerId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveMainSalesman()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @MainSalesmanId int=(select ISNULL((Select Top 1 max(cast(MainSalesmanId as int)) from ERP.MainSalesman),0)+1) \n");
strSql.Append("Insert into ERP.MainSalesman(MainSalesmanId, MainSalesmanDesc, MainSalesmanShortName,Address, PhoneNo, MobileNo, CommissionRate, LedgerId, BranchId, CompanyUnitId, Status, EnterBy, EnterDate , Gadget) \n");
strSql.Append("Select @MainSalesmanId,N'" + Model.MainSalesmanDesc.Trim().Replace("'", "''") + "',N'" + Model.MainSalesmanShortName.Trim().Replace("'", "''") + "', " + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + "," + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + "," + Model.CommissionRate + "," + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + "," + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", '" + Model.Status.ToString().ToLower() + "', '" + Model.EnterBy + "', GETDATE(),'" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@MainSalesmanId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.MainSalesman SET MainSalesmanDesc=N'" + Model.MainSalesmanDesc.Trim().Replace("'", "''") + "',MainSalesmanShortName = N'" + Model.MainSalesmanShortName.Trim().Replace("'", "''") + "', Address=" + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ",PhoneNo=" + "" + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ",MobileNo=" + "" + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + ",LedgerId=" + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ",BranchId=" + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + ",CompanyUnitId=" + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ",CommissionRate= " + Model.CommissionRate + ",Status='" + Model.Status.ToString().ToLower() + "',EnterBy= '" + Model.EnterBy + "',Gadget='" + Model.Gadget + "' WHERE MainSalesmanId = '" + Model.MainSalesmanId + "' \n");
strSql.Append("SET @VNo ='" + Model.MainSalesmanId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.MainSalesman WHERE MainSalesmanId = '" + Model.MainSalesmanId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveDocNumbering()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @DocId int=(select ISNULL((Select Top 1 max(cast(DocId as int)) from ERP.DocumentNumbering),0)+1) \n");
strSql.Append("Insert into ERP.DocumentNumbering([DocId],[DocModule],[DocDesc],[DocStartDate],[DocEndDate],[DocType],[DocPrefix],[DocSufix],[DocBodyLength],[DocTotalLength],[DocIsNumericFill],[DocFillCharacter],[DocStartNo],[DocCurrentNo],[DocEndNo],[PrintDesignId],[Status],[EnterBy],[EnterDate],NumericalStyle,Gadget) \n");
strSql.Append("select @DocId,'" + Model.DocModule.Trim() + "','" + Model.DocDesc.Trim() + "','" + Model.DocStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "','" + Model.DocEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "'," + ((Model.DocType.Trim() == "") ? "null" : "'" + Model.DocType.Trim().Replace("'", "''") + "'") + "," + ((Model.DocPrefix.Trim() == "") ? "null" : "'" + Model.DocPrefix.Trim().Replace("'", "''") + "'") + "," + ((Model.DocSufix.Trim() == "") ? "null" : "'" + Model.DocSufix.Trim().Replace("'", "''") + "'") + ",'" + Model.DocBodyLength + "','" + Model.DocTotalLength + "','" + Model.DocIsNumericFill + "'," + ((Model.DocFillCharacter.Trim() == "") ? "null" : "'" + Model.DocFillCharacter.Trim().Replace("'", "''") + "'") + ",'" + Model.DocStartNo + "','" + Model.DocCurrentNo + "','" + Model.DocEndNo + "'," + ((Model.PrintDesignId == 0) ? "null" : "'" + Model.PrintDesignId + "'") + ",'" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE(),'" + Model.NumericalStyle + "','" + Model.Gadget + "'");
strSql.Append("SET @VNo =@DocId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.DocumentNumbering SET [DocDesc] = '" + Model.DocDesc.Trim() + "',[DocStartDate] = '" + Model.DocStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "',[DocEndDate] = '" + Model.DocEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "',[DocType] = " + ((Model.DocType.Trim() == "") ? "null" : "'" + Model.DocType.Trim().Replace("'", "''") + "'") + ",[DocPrefix] = " + ((Model.DocPrefix.Trim() == "") ? "null" : "'" + Model.DocPrefix.Trim().Replace("'", "''") + "'") + ",[DocSufix] = " + ((Model.DocSufix.Trim() == "") ? "null" : "'" + Model.DocSufix.Trim().Replace("'", "''") + "'") + ",[DocBodyLength] = '" + Model.DocBodyLength + "',[DocTotalLength] = '" + Model.DocTotalLength + "',[DocIsNumericFill] = '" + Model.DocIsNumericFill + "',[DocFillCharacter] = '" + Model.DocFillCharacter.Trim() + "',[DocStartNo] = '" + Model.DocStartNo + "',[DocCurrentNo] = '" + Model.DocCurrentNo + "',[DocEndNo] = '" + Model.DocEndNo + "',[PrintDesignId] = " + ((Model.PrintDesignId == 0) ? "null" : "'" + Model.PrintDesignId + "'") + ",[Status]='" + Model.Status.ToString().ToLower() + "', NumericalStyle='" + Model.NumericalStyle + "',Gadget='" + Model.Gadget + "' WHERE DocId = '" + Model.DocId + "'");
strSql.Append("SET @VNo ='" + Model.DocId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.DocumentNumbering WHERE DocId = '" + Model.DocId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveCompanyUnit()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @CompanyUnitId int=(select ISNULL((Select Top 1 max(cast(CompanyUnitId as int)) from ERP.CompanyUnit),0)+1) \n");
strSql.Append("Insert into ERP.CompanyUnit(CompanyUnitId, CmpUnitName, CmpUnitShortName, Address, City, State, Country, PhoneNo, Fax, Email, ContactPerson, ContactPersonAdd, ContactPersonPhoneNo, ContactPersonMobileNo,BranchId, EnterBy, EnterDate, Gadget) \n");
strSql.Append("Select @CompanyUnitId,'" + Model.CmpUnitName.Trim() + "','" + Model.CmpUnitShortName.Trim() + "', " + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ", " + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + ", " + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + ", " + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ", " + ((Model.PhoneNo.Trim() == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ", " + ((Model.Fax.Trim() == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + ", " + ((Model.Email.Trim() == "") ? "null" : "N'" + Model.Email.Trim().Replace("'", "''") + "'") + ", " + ((Model.ContactPerson.Trim() == "") ? "null" : "N'" + Model.ContactPerson.Trim().Replace("'", "''") + "'") + ", " + ((Model.ContactPersonAdd.Trim() == "") ? "null" : "N'" + Model.ContactPersonAdd.Trim().Replace("'", "''") + "'") + ", " + ((Model.ContactPersonPhone.Trim() == "") ? "null" : "N'" + Model.ContactPersonPhone.Trim().Replace("'", "''") + "'") + ", " + ((Model.ContactPersonPhone.Trim() == "") ? "null" : "N'" + Model.ContactPersonPhone.Trim().Replace("'", "''") + "'") + "," + ((Model.BranchId == 0) ? "0" : "'" + Model.BranchId + "'") + ",'" + Model.EnterBy.Trim() + "',GETDATE(), '" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@CompanyUnitId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.CompanyUnit SET [BranchName] = '" + Model.CmpUnitName.Trim() + "',[BranchShortName] = '" + Model.CmpUnitShortName.Trim() + "',[Address] = " + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ",[City] = " + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + ",[State] = " + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + ",[Country] = " + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ",[PhoneNo] = " + ((Model.PhoneNo.Trim() == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ",[Fax] = " + ((Model.Fax.Trim() == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + ",[Email] = " + ((Model.Email.Trim() == "") ? "null" : "N'" + Model.Email.Trim().Replace("'", "''") + "'") + ",[ContactPerson] = " + ((Model.ContactPerson.Trim() == "") ? "null" : "N'" + Model.ContactPerson.Trim().Replace("'", "''") + "'") + ",[ContactPersonAdd] = " + ((Model.ContactPersonAdd.Trim() == "") ? "null" : "N'" + Model.ContactPersonAdd.Trim().Replace("'", "''") + "'") + ",[ContactPersonPhoneNo] = " + ((Model.ContactPersonPhone.Trim() == "") ? "null" : "N'" + Model.ContactPersonPhone.Trim().Replace("'", "''") + "'") + ",[ContactPersonMobileNo] = " + ((Model.ContactPersonMobileNo.Trim() == "") ? "null" : "N'" + Model.ContactPersonMobileNo.Trim().Replace("'", "''") + "'") + ",[BranchId] = " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + " WHERE CompanyUnitId = '" + Model.CompanyUnitId + "'");
strSql.Append("SET @VNo ='" + Model.CompanyUnitId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.CompanyUnit WHERE CompanyUnitId = '" + Model.CompanyUnitId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveProductGroup(string tableName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @ProductGrpId int=(select ISNULL((Select Top 1 max(cast(ProductGrpId as int)) from ERP." + tableName + "),0)+1) \n");
strSql.Append("Insert into ERP." + tableName + "(ProductGrpId, ProductGrpDesc, ProductGrpShortName,Margin,PrinterName, [Status], EnterBy, EnterDate,Gadget) \n");
strSql.Append("select @ProductGrpId,N'" + Model.ProductGrpDesc.Trim().Replace("'", "''") + "',N'" + Model.ProductGrpShortName.Trim().Replace("'", "''") + "', " + ((Model.ProductGrpMargin == null) ? "0" : "'" + Model.ProductGrpMargin + "'") + ", '" + Model.ProductGrpPrinterName.Trim() + "','" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE(),'" + Model.Gadget + "' \n");
strSql.Append("SET @VNo =@ProductGrpId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP." + tableName + " SET ProductGrpDesc=N'" + Model.ProductGrpDesc.Trim().Replace("'", "''") + "',ProductGrpShortName = N'" + Model.ProductGrpShortName.Trim().Replace("'", "''") + "',Margin = " + ((Model.ProductGrpMargin == null) ? "0" : "'" + Model.ProductGrpMargin + "'") + ",PrinterName = '" + Model.ProductGrpPrinterName.Trim() + "',[Status]='" + Model.Status.ToString().ToLower() + "',Gadget='" + Model.Gadget + "' WHERE ProductGrpId = '" + Model.ProductGrpId + "' \n");
strSql.Append("SET @VNo ='" + Model.ProductGrpId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP." + tableName + " WHERE ProductGrpId = '" + Model.ProductGrpId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveTable()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @TableId int=(select ISNULL((Select Top 1 max(cast(TableId as int)) from ERP.TableMaster),0)+1) \n");
strSql.Append("INSERT INTO [ERP].[TableMaster]([TableId],[TableDesc],[TableShortName],[FloorId],[TableType],[TableStatus],[Status],[EnterBy],[EnterDate],Gadget)\n");
strSql.Append("Select @TableId,N'" + Model.TableDesc.Trim().Replace("'", "''") + "',N'" + Model.TableShortName.Trim().Replace("'", "''") + "' ,'" + Model.FloorId + "' ,'" + Model.TableType + "','" + Model.TableStatus + "','" + Model.Status.ToString() + "','" + Model.EnterBy + "',GETDATE(),'" + Model.Gadget + "'\n");
strSql.Append("SET @VNo =@TableId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.TableMaster SET TableDesc=N'" + Model.TableDesc.Trim().Replace("'", "''") + "',TableShortName = N'" + Model.TableShortName.Trim().Replace("'", "''") + "',FloorId='" + Model.FloorId + "',TableType='" + Model.TableType + "',TableStatus='" + Model.TableStatus + "',[Status]='" + Model.Status.ToString().ToLower() + "' WHERE TableId = '" + Model.TableId + "' \n");
strSql.Append("SET @VNo ='" + Model.TableId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.TableMaster WHERE TableId = '" + Model.TableId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SavePurchaseBillingTerm()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @TermId int=(select ISNULL((Select Top 1 max(cast(TermId as int)) from ERP.PurchaseBillingTerm),0)+1) \n");
strSql.Append("Insert into ERP.PurchaseBillingTerm([TermId],[TermPosition],[TermType],[TermDesc],[Category],[LedgerId],[Basis],[PTSign],[Billwise],[TermRate],[StockValuation],[SupressZero],[Formula],[Status],[EnterBy],[EnterDate],Gadget) \n");
strSql.Append("select @TermId," + Model.TermPosition + ",'" + Model.TermType.Trim() + "',N'" + Model.TermDesc.Trim().Replace("'", "''") + "','" + Model.Category.Trim() + "','" + Model.LedgerId + "','" + Model.Basis.Trim() + "','" + Model.PTSign.Trim() + "','" + Model.Billwise.Trim() + "','" + Model.TermRate + "','" + Model.StockValuation + "','" + Model.SupressZero + "','" + Model.Formula.Trim() + "','" + Model.Status.ToString().ToLower() + "','" + Model.EnterBy.Trim() + "',GETDATE() ,'" + Model.Gadget + "'\n");
strSql.Append("SET @VNo =@TermId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.PurchaseBillingTerm SET [TermPosition] = '" + Model.TermPosition + "',[TermType] = '" + Model.TermType.Trim() + "',[TermDesc] = '" + Model.TermDesc.Trim() + "',[Category] = '" + Model.Category.Trim() + "',[LedgerId] = '" + Model.LedgerId + "',[Basis] = '" + Model.Basis.Trim() + "',[PTSign] = '" + Model.PTSign.Trim() + "',[Billwise] = '" + Model.Billwise.Trim() + "',[TermRate] = '" + Model.TermRate + "',[StockValuation] = '" + Model.StockValuation + "',[SupressZero] = '" + Model.SupressZero + "',[Formula] = '" + Model.Formula.Trim() + "',[Status]='" + Model.Status.ToString().ToLower() + "',Gadget='" + Model.Gadget + "' WHERE TermId = '" + Model.TermId + "' \n");
strSql.Append("SET @VNo ='" + Model.TermId + "'");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM ERP.PurchaseBillingTerm WHERE TermId = '" + Model.TermId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
p[0].Direction = ParameterDirection.Output;
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveProduct()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @ProductId int=(select ISNULL((Select Top 1 max(cast(productId as int)) from ERP.Product),0)+1) \n");
strSql.Append("insert into ERP.product(ProductId, ProductDesc, ProductShortName, ProductPrintingName, GenericName, ProductAlias,ProductDescription, ProductCategory, ProductType, \n");
strSql.Append("ValuationTech, ProductGrpId, PGrpId1, PGrpId2, ProductSubGrpId, IsBatchwise, IsSerialWise, IsSizewise, IsExpiryDate, \n");
strSql.Append("IsManufacturingDate, SerialNo, PartsNo, ProductUnitId, QtyConv, ProductAltUnitId, AltConv, BuyRate, SalesRate, Margin1, TradeRate,\n");
strSql.Append("Margin2, MRP, MRRate, MaxStock, MinStock, ReorderLevel, ReorderQty, PercentDisc, MinQty, MinDisc, MaxQty, MaxDisc,\n");
strSql.Append("IsTaxable, TaxRate, PurchaseLedgerId, PurchaseReturnLedgerId, SalesLedgerId, SalesReturnLedgerId, PLOpeningLedgerId, \n");
strSql.Append("PLClosingLedgerId, BSClosingLedgerId, DepreciationType, DepreciationLedgerId, DepreciationRate, PImage, DepartmentId, \n");
strSql.Append("DepartmentId1, DepartmentId2, DepartmentId3, EnterBy, EnterDate, Status, BarCodeNo2, BarCodeNo1,Gadget,ProductModel)\n");
strSql.Append("select @ProductId,N'" + Model.ProductDesc.Trim().Replace("'", "''") + "',N'" + Model.ProductShortName.Trim().Replace("'", "''") + "'," + ((Model.ProductPrintingName == "") ? "null" : "N'" + Model.ProductPrintingName.Trim().Replace("'", "''") + "'") + ",\n");
strSql.Append("" + ((Model.GenericName == "") ? "null" : "N'" + Model.GenericName.Trim().Replace("'", "''") + "'") + "," + ((Model.ProductAlias == "") ? "null" : "N'" + Model.ProductAlias.Trim().Replace("'", "''") + "'") + "," + ((Model.ProductDescription == "") ? "null" : "N'" + Model.ProductDescription.Trim().Replace("'", "''") + "'") + ",N'" + Model.ProductCategory.Trim().Replace("'", "''") + "',N'" + Model.ProductType.Trim().Replace("'", "''") + "',\n");
strSql.Append("N'" + Model.ValuationTech.Trim().Replace("'", "''") + "', " + ((Model.ProductGrpId == 0) ? "null" : "'" + Model.ProductGrpId + "'") + "," + ((Model.PGrpId1 == 0) ? "null" : "'" + Model.PGrpId1 + "'") + "," + ((Model.PGrpId2 == 0) ? "null" : "'" + Model.PGrpId2 + "'") + "," + ((Model.ProductSubGrpId == 0) ? "null" : "'" + Model.ProductSubGrpId + "'") + ",\n");
strSql.Append("'" + Model.IsBatchwise + "','" + Model.IsSerialWise + "','" + Model.IsSizewise + "','" + Model.IsExpiryDate + "','" + Model.IsManufacturingDate + "',\n");
strSql.Append("" + ((Model.SerialNo == "") ? "null" : "N'" + Model.SerialNo.Trim().Replace("'", "''") + "'") + "," + ((Model.PartsNo == "") ? "null" : "N'" + Model.PartsNo.Trim().Replace("'", "''") + "'") + "," + ((Model.ProductUnitId == 0) ? "null" : "'" + Model.ProductUnitId + "'") + ",'" + ClsGlobal.Val(Model.QtyConv.ToString()) + "'," + ((Model.ProductAltUnitId == 0) ? "null" : "'" + Model.ProductAltUnitId + "'") + ",\n");
strSql.Append("'" + Model.AltConv + "','" + ClsGlobal.Val(Model.BuyRate.ToString()) + "','" + ClsGlobal.Val(Model.SalesRate.ToString()) + "','" + ClsGlobal.Val(Model.Margin1.ToString()) + "','" + ClsGlobal.Val(Model.TradeRate.ToString()) + "',\n");
strSql.Append("'" + ClsGlobal.Val(Model.Margin2.ToString()) + "','" + ClsGlobal.Val(Model.MRP.ToString()) + "','" + ClsGlobal.Val(Model.MRRate.ToString()) + "','" + ClsGlobal.Val(Model.MaxStock.ToString()) + "','" + ClsGlobal.Val(Model.MinStock.ToString()) + "','" + ClsGlobal.Val(Model.ReorderLevel.ToString()) + "',\n");
strSql.Append("'" + ClsGlobal.Val(Model.ReorderQty.ToString()) + "','" + ClsGlobal.Val(Model.PercentDisc.ToString()) + "','" + ClsGlobal.Val(Model.MinQty.ToString()) + "','" + ClsGlobal.Val(Model.MinDisc.ToString()) + "','" + ClsGlobal.Val(Model.MaxQty.ToString()) + "','" + ClsGlobal.Val(Model.MaxDisc.ToString()) + "',\n");
strSql.Append("'" + Model.IsTaxable + "','" + ClsGlobal.Val(Model.TaxRate.ToString()) + "'," + ((Model.PurchaseLedgerId == 0) ? "null" : "'" + Model.PurchaseLedgerId + "'") + "," + ((Model.PurchaseReturnLedgerId == 0) ? "null" : "'" + Model.PurchaseReturnLedgerId + "'") + "," + ((Model.SalesLedgerId == 0) ? "null" : "'" + Model.SalesLedgerId + "'") + ",\n");
strSql.Append("" + ((Model.SalesReturnLedgerId == 0) ? "null" : "'" + Model.SalesReturnLedgerId + "'") + "," + ((Model.PLOpeningLedgerId == 0) ? "null" : "'" + Model.PLOpeningLedgerId + "'") + "," + ((Model.PLClosingLedgerId == 0) ? "null" : "'" + Model.PLClosingLedgerId + "'") + "," + ((Model.BSClosingLedgerId == 0) ? "null" : "'" + Model.BSClosingLedgerId + "'") + ",\n");
strSql.Append("'" + Model.DepreciationType + "'," + ((Model.DepreciationLedgerId == 0) ? "null" : "'" + Model.DepreciationLedgerId + "'") + ",'" + ClsGlobal.Val(Model.DepreciationRate.ToString()) + "',null,\n");
strSql.Append("" + ((Model.DepartmentId == 0) ? "null" : "'" + Model.DepartmentId + "'") + "," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + "," + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + "," + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ",N'" + Model.EnterBy.Trim().Replace("'", "''") + "',\n");
strSql.Append("GetDate(),'" + Model.Status + "'," + ((Model.BarCodeNo2 == "") ? "null" : "N'" + Model.BarCodeNo2.Trim().Replace("'", "''") + "'") + "," + ((Model.BarCodeNo1 == "") ? "null" : "N'" + Model.BarCodeNo1.Trim().Replace("'", "''") + "'") + ",'" + Model.Gadget + "'," + (string.IsNullOrEmpty(Model.ProductModel) ? "null" : "'" + Model.ProductModel + "'") + "\n");
foreach (SalesProductTerm det in this.ModelSalesProductTerm)
{
strSql.Append("INSERT INTO [ERP].[SalesProductTerm] (ProductId,TermId,Rate) \n");
strSql.Append("Select @ProductId,'" + det.TermId + "','" + ClsGlobal.Val(det.Rate.ToString()) + "' \n");
}
this.ModelSalesProductTerm.Clear();
foreach (PurchaseProductTerm det in this.ModelPurchaseProductTerm)
{
strSql.Append("INSERT INTO [ERP].[PurchaseProductTerm] (ProductId,TermId,Rate) \n");
strSql.Append("Select @ProductId,'" + det.TermId + "','" + ClsGlobal.Val(det.Rate.ToString()) + "' \n");
}
this.ModelPurchaseProductTerm.Clear();
strSql.Append("SET @VNo =@ProductId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.PRODUCT SET ProductDesc= N'" + Model.ProductDesc.Trim().Replace("'", "''") + "',ProductShortName=N'" + Model.ProductShortName.Trim().Replace("'", "''") + "',ProductPrintingName=" + ((Model.ProductPrintingName == "") ? "null" : "N'" + Model.ProductPrintingName.Trim().Replace("'", "''") + "'") + ",\n");
strSql.Append("GenericName=" + ((Model.GenericName == "") ? "null" : "N'" + Model.GenericName.Trim().Replace("'", "''") + "'") + ",ProductAlias=" + ((Model.ProductAlias == "") ? "null" : "N'" + Model.ProductAlias.Trim().Replace("'", "''") + "'") + ",ProductDescription=" + ((Model.ProductDescription == "") ? "null" : "N'" + Model.ProductDescription.Trim().Replace("'", "''") + "'") + ",ProductCategory=N'" + Model.ProductCategory.Trim().Replace("'", "''") + "',ProductType=N'" + Model.ProductType.Trim().Replace("'", "''") + "',\n");
strSql.Append("ValuationTech=N'" + Model.ValuationTech.Trim().Replace("'", "''") + "',ProductGrpId= " + ((Model.ProductGrpId == 0) ? "null" : "'" + Model.ProductGrpId + "'") + ",PGrpId1=" + ((Model.PGrpId1 == 0) ? "null" : "'" + Model.PGrpId1 + "'") + ",PGrpId2=" + ((Model.PGrpId2 == 0) ? "null" : "'" + Model.PGrpId2 + "'") + ",ProductSubGrpId=" + ((Model.ProductSubGrpId == 0) ? "null" : "'" + Model.ProductSubGrpId + "'") + ",\n");
strSql.Append("IsBatchwise='" + Model.IsBatchwise + "',IsSerialWise='" + Model.IsSerialWise + "',IsSizewise='" + Model.IsSizewise + "',IsExpiryDate='" + Model.IsExpiryDate + "',IsManufacturingDate='" + Model.IsManufacturingDate + "',\n");
strSql.Append("SerialNo=" + ((Model.SerialNo == "") ? "null" : "N'" + Model.SerialNo.Trim().Replace("'", "''") + "'") + ",PartsNo=" + ((Model.PartsNo == "") ? "null" : "N'" + Model.PartsNo.Trim().Replace("'", "''") + "'") + ",ProductUnitId=" + ((Model.ProductUnitId == 0) ? "null" : "'" + Model.ProductUnitId + "'") + ",QtyConv='" + ClsGlobal.Val(Model.QtyConv.ToString()) + "',ProductAltUnitId=" + ((Model.ProductAltUnitId == 0) ? "null" : "'" + Model.ProductAltUnitId + "'") + ",\n");
strSql.Append("AltConv='" + Model.AltConv + "',BuyRate='" + ClsGlobal.Val(Model.BuyRate.ToString()) + "',SalesRate='" + ClsGlobal.Val(Model.SalesRate.ToString()) + "',Margin1='" + ClsGlobal.Val(Model.Margin1.ToString()) + "',TradeRate='" + ClsGlobal.Val(Model.TradeRate.ToString()) + "',\n");
strSql.Append("Margin2='" + ClsGlobal.Val(Model.Margin2.ToString()) + "',MRP='" + ClsGlobal.Val(Model.MRP.ToString()) + "',MRRate='" + ClsGlobal.Val(Model.MRRate.ToString()) + "',MaxStock='" + ClsGlobal.Val(Model.MaxStock.ToString()) + "',MinStock='" + ClsGlobal.Val(Model.MinStock.ToString()) + "',ReorderLevel='" + ClsGlobal.Val(Model.ReorderLevel.ToString()) + "',\n");
strSql.Append("ReorderQty='" + ClsGlobal.Val(Model.ReorderQty.ToString()) + "',PercentDisc='" + ClsGlobal.Val(Model.PercentDisc.ToString()) + "',MinQty='" + ClsGlobal.Val(Model.MinQty.ToString()) + "',MinDisc='" + ClsGlobal.Val(Model.MinDisc.ToString()) + "',MaxQty='" + ClsGlobal.Val(Model.MaxQty.ToString()) + "',MaxDisc='" + ClsGlobal.Val(Model.MaxDisc.ToString()) + "',\n");
strSql.Append("IsTaxable='" + Model.IsTaxable + "',TaxRate='" + ClsGlobal.Val(Model.TaxRate.ToString()) + "',PurchaseLedgerId=" + ((Model.PurchaseLedgerId == 0) ? "null" : "'" + Model.PurchaseLedgerId + "'") + ",PurchaseReturnLedgerId=" + ((Model.PurchaseReturnLedgerId == 0) ? "null" : "'" + Model.PurchaseReturnLedgerId + "'") + ",SalesLedgerId=" + ((Model.SalesLedgerId == 0) ? "null" : "'" + Model.SalesLedgerId + "'") + ",\n");
strSql.Append("SalesReturnLedgerId=" + ((Model.SalesReturnLedgerId == 0) ? "null" : "'" + Model.SalesReturnLedgerId + "'") + ",PLOpeningLedgerId=" + ((Model.PLOpeningLedgerId == 0) ? "null" : "'" + Model.PLOpeningLedgerId + "'") + ",PLClosingLedgerId=" + ((Model.PLClosingLedgerId == 0) ? "null" : "'" + Model.PLClosingLedgerId + "'") + ",BSClosingLedgerId=" + ((Model.BSClosingLedgerId == 0) ? "null" : "'" + Model.BSClosingLedgerId + "'") + ",\n");
strSql.Append("DepreciationType='" + Model.DepreciationType + "',DepreciationLedgerId=" + ((Model.DepreciationLedgerId == 0) ? "null" : "'" + Model.DepreciationLedgerId + "'") + ",DepreciationRate='" + ClsGlobal.Val(Model.DepreciationRate.ToString()) + "',\n");
strSql.Append("DepartmentId=" + ((Model.DepartmentId == 0) ? "null" : "'" + Model.DepartmentId + "'") + ",DepartmentId1=" + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ",DepartmentId2=" + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ",DepartmentId3=" + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ",EnterBy=N'" + Model.EnterBy.Trim().Replace("'", "''") + "',\n");
strSql.Append("Status='" + Model.Status + "',BarCodeNo2=" + ((Model.BarCodeNo2 == "") ? "null" : "N'" + Model.BarCodeNo2.Trim().Replace("'", "''") + "'") + ",BarCodeNo1=" + ((Model.BarCodeNo1 == "") ? "null" : "N'" + Model.BarCodeNo1.Trim().Replace("'", "''") + "'") + ",Gadget='" + Model.Gadget + "',ProductModel=" + (string.IsNullOrEmpty(Model.ProductModel) ? "null" : "'" + Model.ProductModel + "'") + " WHERE ProductId='" + Model.ProductId + "'\n");
strSql.Append("SET @VNo ='" + Model.ProductId + "'");
strSql.Append("Delete from [ERP].[SalesProductTerm] where ProductId ='" + Model.ProductId + "' \n");
strSql.Append("Delete from [ERP].[PurchaseProductTerm] where ProductId ='" + Model.ProductId + "' \n");
foreach (SalesProductTerm det in this.ModelSalesProductTerm)
{
strSql.Append("INSERT INTO [ERP].[SalesProductTerm] (ProductId,TermId,Rate,LedgerId) \n");
strSql.Append("Select '" + Model.ProductId + "','" + det.TermId + "','" + ClsGlobal.Val(det.Rate.ToString()) + "',(select LedgerId from erp.SalesBillingTerm where TermId='" + det.TermId + "') \n");
}
this.ModelSalesProductTerm.Clear();
foreach (PurchaseProductTerm det in this.ModelPurchaseProductTerm)
{
strSql.Append("INSERT INTO [ERP].[PurchaseProductTerm] (ProductId,TermId,Rate,LedgerId) \n");
strSql.Append("Select '" + Model.ProductId + "','" + det.TermId + "','" + ClsGlobal.Val(det.Rate.ToString()) + "',(select LedgerId from erp.PurchaseBillingTerm where TermId='" + det.TermId + "') \n");
}
this.ModelPurchaseProductTerm.Clear();
}
else if (Model.Tag == "DELETE")
{
strSql.Append("Delete from [ERP].[SalesProductTerm] where ProductId ='" + Model.ProductId + "' \n");
strSql.Append("Delete from [ERP].[PurchaseProductTerm] where ProductId ='" + Model.ProductId + "' \n");
strSql.Append("DELETE FROM ERP.Product WHERE ProductId = '" + Model.ProductId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveSalesChallan()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
strSql.Append("Set @VoucherNo ='" + Model.VoucherNo.Trim() + "' \n");
if (Model.Tag == "NEW")
{
strSql.Append("INSERT INTO [ERP].[SalesChallanMaster] ([VoucherNo],[VDate],[VTime],[VMiti],[LedgerId] \n");
strSql.Append(",[SubLedgerId],[SalesmanId],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4],[CurrencyId],[CurrencyRate] \n");
strSql.Append(",[BranchId],[CompanyUnitId],[BasicAmount],[TermAmount],[NetAmount],[LocalNetAmount],[PartyName],[PartyVatNo],[PartyAddress] \n");
strSql.Append(",[PartyMobileNo],[ChequeNo],[ChequeDate],[ChequeMiti],[Remarks],[QuotationNo],[OrderNo],[EnterBy],[EnterDate],[IsReconcile]\n");
strSql.Append(",[ReconcileBy],[ReconcileDate],[IsPosted],[PostedBy],[PostedDate],[IsAuthorized],[AuthorizedBy],[AuthorizedDate],[AuthorizeRemarks],[Gadget]) \n");
strSql.Append("Select @VoucherNo,'" + Model.VDate.ToString("yyyy-MM-dd") + "','" + Model.VDate.ToString("yyyy-MM-dd") + ' ' + DateTime.Now.ToShortTimeString() + "','" + Model.VMiti.ToString() + "'," + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ", \n");
strSql.Append("" + ((Model.SubLedgerId == 0) ? "null" : "'" + Model.SubLedgerId + "'") + ", " + ((Model.SalesmanId == 0) ? "null" : "'" + Model.SalesmanId + "'") + "," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ", " + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + " ," + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",'" + (Model.CurrencyRate == 0 ? 1 : Model.CurrencyRate) + "', " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", \n");
strSql.Append("'" + Model.BasicAmount + "','" + Model.TermAmount + "', '" + Model.NetAmount + "', '" + (Model.NetAmount * (Model.CurrencyRate == 0 ? 1 : Model.CurrencyRate)) + "' , \n");
strSql.Append("" + (string.IsNullOrEmpty(Model.PartyName) ? "null" : "'" + Model.PartyName + "'") + ", " + (string.IsNullOrEmpty(Model.PartyVatNo) ? "null" : "'" + Model.PartyVatNo + "'") + ", " + (string.IsNullOrEmpty(Model.PartyAddress) ? "null" : "'" + Model.PartyAddress + "'") + ", " + (string.IsNullOrEmpty(Model.PartyMobileNo) ? "null" : "'" + Model.PartyMobileNo + "'") + "," + (string.IsNullOrEmpty(Model.ChequeNo) ? "null" : "'" + Model.ChequeNo + "'") + ", " + ((string.IsNullOrEmpty(Model.ChequeDate.ToString())) ? "null" : "'" + Model.ChequeDate.Value.ToString("yyyy-MM-dd") + "'") + ", " + (string.IsNullOrEmpty(Model.ChequeMiti) ? "null" : "'" + Model.ChequeMiti + "'") + ",\n");
strSql.Append("" + (string.IsNullOrEmpty(Model.Remarks) ? "null" : "'" + Model.Remarks + "'") + ", " + (string.IsNullOrEmpty(Model.QuotationNo) ? "null" : "'" + Model.QuotationNo + "'") + ", " + (string.IsNullOrEmpty(Model.OrderNo) ? "null" : "'" + Model.OrderNo + "'") + ", '" + Model.EnterBy + "', GETDATE(), '" + Model.IsReconcile + "'," + (string.IsNullOrEmpty(Model.ReconcileBy) ? "null" : "'" + Model.ReconcileBy + "'") + ", " + ((Model.ReconcileDate.ToString() == "") ? "null" : "'" + Model.ReconcileDate.ToString() + "'") + ", '" + Model.IsPosted + "', " + (string.IsNullOrEmpty(Model.PostedBy) ? "null" : "'" + Model.PostedBy + "'") + ", " + ((Model.PostedDate.ToString() == "") ? "null" : "'" + Model.PostedDate.ToString() + "'") + ",'" + Model.IsAuthorized + "', " + (string.IsNullOrEmpty(Model.AuthorizedBy) ? "null" : "'" + Model.AuthorizedBy + "'") + ", \n");
strSql.Append("" + ((Model.AuthorizedDate.ToString() == "") ? "null" : "'" + Model.AuthorizedDate.ToString() + "'") + ", " + (string.IsNullOrEmpty(Model.AuthorizeRemarks) ? "null" : "'" + Model.AuthorizeRemarks + "'") + ", '" + Model.Gadget + "' \n");
strSql.Append("Update ERP.DocumentNumbering set DocCurrentNo = DocCurrentNo + 1 where DocId =" + Model.DocId + "\n");
strSql.Append("INSERT INTO [ERP].[SalesChallanOtherDetails]([VoucherNo],[Transport],[VehicleNo],[Package],[CnNo],[CnDate],[CnFreight],[CnType]\n");
strSql.Append(",[Advance],[BalFreight],[DriverName],[DriverLicNo],[DriverMobileNo],[ContractNo],[ContractDate],[ExpInvNo],[ExpInvDate],[PoNo],[PoDate],[DocBank],[LcNo],[CustomName],[Cofd])\n");
strSql.Append("Select @VoucherNo," + (string.IsNullOrEmpty(ModelOtherDetails.Transport) ? "null" : "'" + ModelOtherDetails.Transport + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.VehicleNo) ? "null" : "'" + ModelOtherDetails.VehicleNo + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.Package) ? "null" : "'" + ModelOtherDetails.Package + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.CnNo) ? "null" : "'" + ModelOtherDetails.CnNo + "'") + "," + ((Convert.ToDateTime(ModelOtherDetails.CnDate).ToString("yyyy-MM-dd") == "") ? "null" : "'" + Convert.ToDateTime(ModelOtherDetails.CnDate).ToString("yyyy-MM-dd") + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.CnFreight) ? "null" : "'" + ModelOtherDetails.CnFreight + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.CnType) ? "null" : "'" + ModelOtherDetails.CnType + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.Advance) ? "null" : "'" + ModelOtherDetails.Advance + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.BalFreight) ? "null" : "'" + ModelOtherDetails.BalFreight + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.DriverName) ? "null" : "'" + ModelOtherDetails.DriverName + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.DriverLicNo) ? "null" : "'" + ModelOtherDetails.DriverLicNo + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.DriverMobileNo) ? "null" : "'" + ModelOtherDetails.DriverMobileNo + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.ContractNo) ? "null" : "'" + ModelOtherDetails.ContractNo + "'") + "," + ((Convert.ToDateTime(ModelOtherDetails.ContractDate).ToString("yyyy-MM-dd") == null) ? "null" : "'" + Convert.ToDateTime(ModelOtherDetails.ContractDate).ToString("yyyy-MM-dd") + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.ExpInvNo) ? "null" : "'" + ModelOtherDetails.ExpInvNo + "'") + "," + ((Convert.ToDateTime(ModelOtherDetails.ExpInvDate).ToString("yyyy-MM-dd") == null) ? "null" : "'" + Convert.ToDateTime(ModelOtherDetails.ExpInvDate).ToString("yyyy-MM-dd") + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.PoNo) ? "null" : "'" + ModelOtherDetails.PoNo + "'") + "," + ((Convert.ToDateTime(ModelOtherDetails.PoDate).ToString("yyyy-MM-dd") == null) ? "null" : "'" + Convert.ToDateTime(ModelOtherDetails.PoDate).ToString("yyyy-MM-dd") + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.DocBank) ? "null" : "'" + ModelOtherDetails.DocBank + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.LcNo) ? "null" : "'" + ModelOtherDetails.LcNo + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.CustomName) ? "null" : "'" + ModelOtherDetails.CustomName + "'") + "," + (string.IsNullOrEmpty(ModelOtherDetails.Cofd) ? "null" : "'" + ModelOtherDetails.Cofd + "'") + " \n");
strSql.Append("INSERT INTO[ERP].[SalesChallanBillingAddress] ([VoucherNo],[LedgerId],[BillingAddress],[BillingCity],[BillingState],[BillingCountry],[BillingEmail],[ShippingAddress],[ShippingCity],[ShippingState],[ShippingCountry],[ShippingEmail],[DeliveryDate],[Remarks])\n");
strSql.Append("Select @VoucherNo," + ((ModelBillAddress.LedgerId == 0) ? "null" : "'" + ModelBillAddress.LedgerId + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.BillingAddress) ? "null" : "'" + ModelBillAddress.BillingAddress + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.BillingCity) ? "null" : "'" + ModelBillAddress.BillingCity + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.BillingState) ? "null" : "'" + ModelBillAddress.BillingState + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.BillingCountry) ? "null" : "'" + ModelBillAddress.BillingCountry + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.BillingEmail) ? "null" : "'" + ModelBillAddress.BillingEmail + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.ShippingAddress) ? "null" : "'" + ModelBillAddress.ShippingAddress + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.ShippingCity) ? "null" : "'" + ModelBillAddress.ShippingCity + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.ShippingState) ? "null" : "'" + ModelBillAddress.ShippingState + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.ShippingCountry) ? "null" : "'" + ModelBillAddress.ShippingCountry + "'") + "," + (string.IsNullOrEmpty(ModelBillAddress.ShippingEmail) ? "null" : "'" + ModelBillAddress.ShippingEmail + "'") + "," + ((ModelBillAddress.DeliveryDate.ToString() == "") ? "null" : "'" + ModelBillAddress.DeliveryDate.ToString() + "'") + ", " + (string.IsNullOrEmpty(ModelBillAddress.Remarks) ? "null" : "'" + ModelBillAddress.Remarks + "'") + " \n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.SalesChallanMaster SET VDate='" + Model.VDate.ToString("yyyy-MM-dd") + "',VMiti='" + Model.VMiti.ToString() + "',LedgerId= " + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ", \n");
strSql.Append("SubLedgerId=" + ((Model.SubLedgerId == 0) ? "null" : "'" + Model.SubLedgerId + "'") + ",SalesmanId= " + ((Model.SalesmanId == 0) ? "null" : "'" + Model.SalesmanId + "'") + ", DepartmentId1=" + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ", DepartmentId2=" + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ", DepartmentId3=" + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", DepartmentId4=" + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", CurrencyId=" + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",CurrencyRate='" + (Model.CurrencyRate == 0 ? 1 : Model.CurrencyRate) + "', BranchId= " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + ",CompanyUnitId=" + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", \n");
strSql.Append("BasicAmount='" + Model.BasicAmount + "',TermAmount='" + Model.TermAmount + "',NetAmount= '" + Model.NetAmount + "' ,LocalNetAmount ='" + (Model.NetAmount * (Model.CurrencyRate == 0 ? 1 : Model.CurrencyRate)) + "', \n");
strSql.Append("PartyName=" + ((Model.PartyName == "") ? "null" : "'" + Model.PartyName + "'") + ", PartyVatNo= " + (string.IsNullOrEmpty(Model.PartyVatNo) ? "null" : "'" + Model.PartyVatNo + "'") + ", PartyAddress= " + (string.IsNullOrEmpty(Model.PartyAddress) ? "null" : "'" + Model.PartyAddress + "'") + ", PartyMobileNo= " + (string.IsNullOrEmpty(Model.PartyMobileNo) ? "null" : "'" + Model.PartyMobileNo + "'") + ",ChequeNo=" + (string.IsNullOrEmpty(Model.ChequeNo) ? "null" : "'" + Model.ChequeNo + "'") + ",ChequeDate= " + ((string.IsNullOrEmpty(Model.ChequeDate.ToString())) ? "null" : "'" + Model.ChequeDate.Value.ToString("yyyy-MM-dd") + "'") + ", ChequeMiti= " + (string.IsNullOrEmpty(Model.ChequeMiti) ? "null" : "'" + Model.ChequeMiti + "'") + ",\n");
strSql.Append("Remarks=" + (string.IsNullOrEmpty(Model.Remarks) ? "null" : "'" + Model.Remarks + "'") + ", QuotationNo=" + (string.IsNullOrEmpty(Model.QuotationNo) ? "null" : "'" + Model.QuotationNo + "'") + ", OrderNo=" + (string.IsNullOrEmpty(Model.OrderNo) ? "null" : "'" + Model.OrderNo + "'") + ", ReconcileBy= " + (string.IsNullOrEmpty(Model.ReconcileBy) ? "null" : "'" + Model.ReconcileBy + "'") + ", ReconcileDate=" + ((string.IsNullOrEmpty(Model.ReconcileDate.ToString())) ? "null" : "'" + Model.ReconcileDate.Value.ToString("yyyy-MM-dd") + "'") + ", IsPosted='" + Model.IsPosted + "', PostedBy= " + (string.IsNullOrEmpty(Model.PostedBy) ? "null" : "'" + Model.PostedBy + "'") + ", PostedDate=" + ((string.IsNullOrEmpty(Model.PostedDate.ToString())) ? "null" : "'" + Model.PostedDate.Value.ToString("yyyy-MM-dd") + "'") + ",IsAuthorized='" + Model.IsAuthorized + "', AuthorizedBy= " + (string.IsNullOrEmpty(Model.AuthorizedBy) ? "null" : "'" + Model.AuthorizedBy + "'") + ", \n");
strSql.Append("AuthorizedDate= " + ((string.IsNullOrEmpty(Model.AuthorizedDate.ToString())) ? "null" : "'" + Model.AuthorizedDate.Value.ToString("yyyy-MM-dd") + "'") + ", AuthorizeRemarks= " + (string.IsNullOrEmpty(Model.AuthorizeRemarks) ? "null" : "'" + Model.AuthorizeRemarks.Trim() + "'") + ", Gadget='" + Model.Gadget.Trim() + "' WHERE VoucherNo = '" + Model.VoucherNo + "' \n");
strSql.Append("UPDATE [ERP].[SalesChallanOtherDetails] SET Transport = " + (string.IsNullOrEmpty(ModelOtherDetails.Transport) ? "null" : "'" + ModelOtherDetails.Transport + "'") + ",VehicleNo = " + (string.IsNullOrEmpty(ModelOtherDetails.VehicleNo) ? "null" : "'" + ModelOtherDetails.VehicleNo + "'") + ",Package = " + (string.IsNullOrEmpty(ModelOtherDetails.Package) ? "null" : "'" + ModelOtherDetails.Package + "'") + ",CnNo = " + (string.IsNullOrEmpty(ModelOtherDetails.CnNo) ? "null" : "'" + ModelOtherDetails.CnNo + "'") + ",CnDate = " + ((string.IsNullOrEmpty(ModelOtherDetails.CnDate.ToString())) ? "null" : "'" + ModelOtherDetails.CnDate.Value.ToString("yyyy-MM-dd") + "'") + ",CnFreight = " + (string.IsNullOrEmpty(ModelOtherDetails.CnFreight) ? "null" : "'" + ModelOtherDetails.CnFreight + "'") + ",CnType = " + (string.IsNullOrEmpty(ModelOtherDetails.CnType) ? "null" : "'" + ModelOtherDetails.CnType + "'") + ",Advance = " + (string.IsNullOrEmpty(ModelOtherDetails.Advance) ? "null" : "'" + ModelOtherDetails.Advance + "'") + ",BalFreight = " + (string.IsNullOrEmpty(ModelOtherDetails.BalFreight) ? "null" : "'" + ModelOtherDetails.BalFreight + "'") + ",DriverName = " + (string.IsNullOrEmpty(ModelOtherDetails.DriverName) ? "null" : "'" + ModelOtherDetails.DriverName + "'") + ",DriverLicNo = " + (string.IsNullOrEmpty(ModelOtherDetails.DriverLicNo) ? "null" : "'" + ModelOtherDetails.DriverLicNo + "'") + ",DriverMobileNo = " + (string.IsNullOrEmpty(ModelOtherDetails.DriverMobileNo) ? "null" : "'" + ModelOtherDetails.DriverMobileNo + "'") + ",ContractNo = " + (string.IsNullOrEmpty(ModelOtherDetails.ContractNo) ? "null" : "'" + ModelOtherDetails.ContractNo + "'") + ",ContractDate = " + ((string.IsNullOrEmpty(ModelOtherDetails.ContractDate.ToString())) ? "null" : "'" + ModelOtherDetails.ContractDate.Value.ToString("yyyy-MM-dd") + "'") + ",ExpInvNo = " + (string.IsNullOrEmpty(ModelOtherDetails.ExpInvNo) ? "null" : "'" + ModelOtherDetails.ExpInvNo + "'") + ",ExpInvDate = " + ((string.IsNullOrEmpty(ModelOtherDetails.ExpInvDate.ToString())) ? "null" : "'" + ModelOtherDetails.ExpInvDate.Value.ToString("yyyy-MM-dd") + "'") + ",PoNo = " + (string.IsNullOrEmpty(ModelOtherDetails.PoNo) ? "null" : "'" + ModelOtherDetails.PoNo + "'") + ",PoDate = " + ((string.IsNullOrEmpty(ModelOtherDetails.PoDate.ToString())) ? "null" : "'" + ModelOtherDetails.PoDate.Value.ToString("yyyy-MM-dd") + "'") + ",DocBank = " + (string.IsNullOrEmpty(ModelOtherDetails.DocBank) ? "null" : "'" + ModelOtherDetails.DocBank + "'") + ",LcNo = " + (string.IsNullOrEmpty(ModelOtherDetails.LcNo) ? "null" : "'" + ModelOtherDetails.LcNo + "'") + ",CustomName = " + (string.IsNullOrEmpty(ModelOtherDetails.CustomName) ? "null" : "'" + ModelOtherDetails.CustomName + "'") + ",Cofd = " + (string.IsNullOrEmpty(ModelOtherDetails.Cofd) ? "null" : "'" + ModelOtherDetails.Cofd + "'") + " WHERE VoucherNo = '" + Model.VoucherNo + "'\n");
strSql.Append("UPDATE[ERP].[SalesChallanBillingAddress] SET LedgerId =" + ((ModelBillAddress.LedgerId == 0) ? "null" : "'" + ModelBillAddress.LedgerId + "'") + ", [BillingAddress] =" + (string.IsNullOrEmpty(ModelBillAddress.BillingAddress) ? "null" : "'" + ModelBillAddress.BillingAddress + "'") + ",[BillingCity] = " + (string.IsNullOrEmpty(ModelBillAddress.BillingCity) ? "null" : "'" + ModelBillAddress.BillingCity + "'") + ",[BillingState] = " + (string.IsNullOrEmpty(ModelBillAddress.BillingState) ? "null" : "'" + ModelBillAddress.BillingState + "'") + ",[BillingCountry] = " + (string.IsNullOrEmpty(ModelBillAddress.BillingCountry) ? "null" : "'" + ModelBillAddress.BillingCountry + "'") + ",[BillingEmail] = " + (string.IsNullOrEmpty(ModelBillAddress.BillingEmail) ? "null" : "'" + ModelBillAddress.BillingEmail + "'") + ",ShippingAddress=" + (string.IsNullOrEmpty(ModelBillAddress.ShippingAddress) ? "null" : "'" + ModelBillAddress.ShippingAddress + "'") + ",ShippingCity=" + (string.IsNullOrEmpty(ModelBillAddress.ShippingCity) ? "null" : "'" + ModelBillAddress.ShippingCity + "'") + ",ShippingState=" + (string.IsNullOrEmpty(ModelBillAddress.ShippingState) ? "null" : "'" + ModelBillAddress.ShippingState + "'") + ",ShippingCountry=" + (string.IsNullOrEmpty(ModelBillAddress.ShippingCountry) ? "null" : "'" + ModelBillAddress.ShippingCountry + "'") + ",ShippingEmail=" + (string.IsNullOrEmpty(ModelBillAddress.ShippingEmail) ? "null" : "'" + ModelBillAddress.ShippingEmail + "'") + ",DeliveryDate=" + ((ModelBillAddress.DeliveryDate.ToString() == "") ? "null" : "'" + ModelBillAddress.DeliveryDate.ToString() + "'") + ", Remarks=" + (string.IsNullOrEmpty(ModelBillAddress.Remarks) ? "null" : "'" + ModelBillAddress.Remarks + "'") + " WHERE VoucherNo = '" + Model.VoucherNo + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("Delete FROM [ERP].InventoryTransaction Where Source = 'SB' and VoucherNo = @VoucherNo \n");
strSql.Append("DELETE FROM [ERP].[SalesChallanTerm] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[SalesChallanDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[SalesChallanBillingAddress] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[SalesChallanOtherDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[SalesChallanMaster] WHERE VoucherNo = '" + Model.VoucherNo + "' \n");
strSql.Append("SET @VoucherNo ='1'");
ModelTerms.Clear();
ModelDetails.Clear();
}
if (Model.Tag == "EDIT")
{
strSql.Append("DELETE FROM [ERP].[SalesChallanDetails] WHERE VoucherNo =@VoucherNo \n");
}
foreach (SalesChallanDetailsViewModel det in ModelDetails)
{
strSql.Append("INSERT INTO [ERP].[SalesChallanDetails]([VoucherNo],[Sno],[ProductId],[ProductAltUnit],[ProductUnit],[GodownId],[AltQty],[Qty],[SalesRate],[BasicAmount],[TermAmount],[NetAmount],[LocalNetAmount],[AdditionalDesc],[ConversionRatio],[FreeQty],[FreeQtyUnit],[OrderNo],[OrderSNo],[DispatchOrderNo],[DispatchOrderSNo],QuotationNo,QuotationSno) \n");
strSql.Append("Select @VoucherNo, '" + det.Sno + "', '" + det.ProductId + "'," + ((det.ProductAltUnit == 0) ? "null" : "'" + det.ProductAltUnit + "'") + ", " + ((det.ProductUnit == 0) ? "null" : "'" + det.ProductUnit + "'") + ", " + ((det.GodownId == 0) ? "null" : "'" + det.GodownId + "'") + ",'" + det.AltQty + "', '" + det.Qty + "', '" + det.SalesRate + "', '" + det.BasicAmount + "', '" + det.TermAmount + "', '" + det.NetAmount + "', '" + det.LocalNetAmount + "', '" + det.AdditionalDesc + "','" + det.ConversionRatio + "', '" + det.FreeQty + "', " + ((det.FreeQtyUnit == 0) ? "null" : "'" + det.FreeQtyUnit + "'") + ", " + (string.IsNullOrEmpty(det.OrderNo) ? "null" : "'" + det.OrderNo + "'") + ", " + ((det.OrderSNo == 0) ? "null" : "'" + det.OrderSNo + "'") + ", " + (string.IsNullOrEmpty(det.DispatchOrderNo) ? "null" : "'" + det.DispatchOrderNo + "'") + ", " + ((det.DispatchOrderSNo == 0) ? "null" : "'" + det.DispatchOrderSNo + "'") + "," + (string.IsNullOrEmpty(det.QuotationNo) ? "null" : "'" + det.QuotationNo + "'") + "," + ((det.QuotationSNo == 0) ? "null" : "'" + det.QuotationSNo + "'") + " \n");
}
if (ModelTerms.Count > 0)
{
strSql.Append("DELETE FROM [ERP].[SalesChallanTerm] WHERE VoucherNo =@VoucherNo \n");
foreach (TermViewModel det in ModelTerms)
{
strSql.Append("INSERT INTO ERP.SalesChallanTerm(VoucherNo, Sno, ProductId, TermId, TermType, STSign, TermRate, TermAmt, LocalTermAmt) \n");
strSql.Append("Select @VoucherNo, '" + det.Sno + "', " + ((det.ProductId == 0) ? "null" : "'" + det.ProductId + "'") + ", '" + det.TermId + "', '" + det.TermType + "', '" + det.STSign + "', '" + det.TermRate + "', '" + det.TermAmt + "', '" + det.LocalTermAmt + "' \n");
}
ModelTerms.Clear();
}
if (Model.UdfDetails != null)
{
if (Model.UdfDetails.Rows.Count > 0)
{
strSql.Append("DELETE FROM ERP.UDFDataEntry WHERE VoucherNo=@VoucherNo AND EntryModule='SC' AND SNO<>0 \n");
int _s = 0;
foreach (DataRow ro in Model.UdfDetails.Rows)
{
int j = 1;
for (int i = 0; i < (Model.UdfDetails.Columns.Count - 1) / 2; i++)
{
strSql.Append("INSERT INTO ERP.UDFDataEntry(VoucherNo,EntryModule,SNO,UDFCode,UDFData,ProductId) \n");
strSql.Append("Select @VoucherNo,'SC','" + ro[0].ToString() + "', ");
strSql.Append("'" + ro[j].ToString() + "' ");
j++;
strSql.Append("," + (string.IsNullOrEmpty(ro[j].ToString()) ? "null" : "'" + ro[j].ToString() + "'") + "");
j++;
strSql.Append(",'" + ModelDetails[_s].ProductId.ToString() + "' \n");
}
_s++;
}
Model.UdfDetails.Rows.Clear();
}
}
if (Model.UdfMaster != null)
{
if (Model.UdfMaster.Rows.Count > 0)
{
strSql.Append("DELETE FROM ERP.UDFDataEntry WHERE VoucherNo=@VoucherNo AND EntryModule='SC' AND SNO=0 \n");
foreach (DataRow ro in Model.UdfMaster.Rows)
{
int j = 1;
for (int i = 0; i < (Model.UdfMaster.Columns.Count - 1) / 2; i++)
{
strSql.Append("INSERT INTO ERP.UDFDataEntry(VoucherNo,EntryModule,SNO,UDFCode,UDFData,ProductId) \n");
strSql.Append("Select @VoucherNo,'SC','0','" + ro[j].ToString() + "',");
j++;
strSql.Append("" + (string.IsNullOrEmpty(ro[j].ToString()) ? "null" : "'" + ro[j].ToString() + "'") + ",NULL \n");
j++;
}
}
Model.UdfMaster.Rows.Clear();
}
}
ModelDetails.Clear();
//BT Posting
strSql.Append("Insert into erp.SalesChallanTerm(VoucherNo, TermId, Sno, ProductId, TermType, STSign, TermRate, TermAmt) \n");
strSql.Append("(Select @VoucherNo as VoucherNo, TermId as TermId, Sno, SBD1.ProductId, 'BT' as TermType, STSign, TermRate \n");
strSql.Append(", isnull(abs(sum((Amt * SBD1.Bamt1) / Bamt)), 0) as TermAmt1 from erp.SalesChallanMaster as scm, \n");
strSql.Append("(Select Sno, SCD.ProductId, SCD.VoucherNo, sum(Case When SCD.NetAmount <> 0 then SCD.NetAmount else SCD.BasicAmount end) as Bamt1 \n");
strSql.Append(" from erp.SalesChallanDetails as SCD, erp.SalesChallanMaster as scm \n");
strSql.Append(" where SCD.VoucherNo = scm.VoucherNo \n");
strSql.Append("group by SCD.ProductId, SCD.VoucherNo, Sno) as SBD1, (select SCD.VoucherNo, CASE WHEN sum(Case when SCD.NetAmount <> 0 then SCD.NetAmount * CurrencyRate else SCD.BasicAmount * CurrencyRate end) = 0 THEN 1 ELSE sum(Case when SCD.NetAmount<>0 then SCD.NetAmount* CurrencyRate else SCD.BasicAmount* CurrencyRate end ) END as Bamt from erp.SalesChallanDetails as SCD,erp.SalesChallanMaster as scm where SCD.VoucherNo = scm.VoucherNo group by SCD.VoucherNo) as Sbd, \n");
strSql.Append("(Select SCD.VoucherNo,SCD.TermId,SCD.TermRate,sum((case when STM.STSign = '+' then(SCD.TermAmt * CurrencyRate) else -(SCD.TermAmt * CurrencyRate) end)) as Amt,Stm.STSign from erp.SalesInvoiceTerm as SCD, erp.SalesChallanMaster as scm,erp.SalesBillingTerm as Stm \n");
strSql.Append("where SCD.VoucherNo = scm.VoucherNo and SCD.TermId = STM.TermId and ProductId is Null and Basis <> 'Q' and Exists(Select* from erp.SalesChallanDetails as SBD where SCD.VoucherNo = SBD.VoucherNo group by ProductId) and SCD.VoucherNo = @VoucherNo \n");
strSql.Append("group by SCD.VoucherNo,SCD.TermId,SCD.TermRate,STM.STSign) as Trm where scm.VoucherNo = SBD1.VoucherNo and scm.VoucherNo = Trm.VoucherNo And Sbd.VoucherNo = Trm.VoucherNo group by SBD1.ProductId,TermId,TermRate,Sno,Trm.STSign \n");
strSql.Append("Union All \n");
strSql.Append("Select @VoucherNo as VoucherNo,TermId as TermId,Sno,Sbd.ProductId,'BT' as TermType,Trm.STSign ,TermRate,isnull(abs(sum(Case when TotQty <> 0 then(Amt / TotQty) * Bamt end)), 0) as TermAmt1 \n");
strSql.Append("from erp.SalesChallanMaster as scm,(Select VoucherNo, Sum(Qty) as TotQty from erp.SalesChallanDetails group by VoucherNo) as SCD, \n");
strSql.Append("(select Sno, ProductId, VoucherNo, sum(Qty) as Bamt from erp.SalesChallanDetails group by VoucherNo,ProductId,Sno) as Sbd, \n");
strSql.Append("(Select SCD.VoucherNo,SCD.TermId,SCD.TermRate,sum((case when STM.STSign = '+' then(SCD.TermAmt * CurrencyRate) else -(SCD.TermAmt * CurrencyRate) end)) as Amt,Stm.STSign \n");
strSql.Append("from erp.SalesInvoiceTerm as SCD,erp.SalesChallanMaster as scm,erp.SalesBillingTerm as Stm where SCD.VoucherNo = scm.VoucherNo and SCD.TermId = STM.TermId and ProductId is Null and Basis = 'Q' \n");
strSql.Append("and Exists(Select* from erp.SalesChallanDetails as SBD where SCD.VoucherNo = SBD.VoucherNo group by ProductId) and SCD.VoucherNo = @VoucherNo group by SCD.VoucherNo,SCD.TermId,SCD.TermRate,STm.STSign) as Trm \n");
strSql.Append("Where scm.VoucherNo = Trm.VoucherNo And Sbd.VoucherNo = Trm.VoucherNo and scm.VoucherNo = SCD.VoucherNo \n");
strSql.Append("group by Sbd.ProductId,TermId,TermRate,Sno,Trm.STSign) \n");
if (Model.Tag == "EDIT")
{
strSql.Append("Delete from ERP.InventoryTransaction Where Source = 'SC' and VoucherNo = @VoucherNo \n");
}
strSql.Append("Insert Into ERP.InventoryTransaction \n");
strSql.Append("(VoucherNo, VDate, VMiti, VTime, LedgerId, SalesManId, DepartmentId1, DepartmentId2, DepartmentId3, CurrencyId, CurrencyRate, Sno, ProductId, GodownId, BranchId, CostCenterId, \n");
strSql.Append("AltQuantity, AltUnitId, Quantity, ProductUnitId, AltStockQuantity, StockQuantity, FreeQuantity, StockFreeQuantity, FreeUnitId, ConversionRatio, Rate, BasicAmount, TermAmount, NetAmount, TransactionType, \n");
strSql.Append("Source, GodownDetailId, DocumentValue, BillTerm, StockValue, TmpStockValue, ExtraFreeQty, ExtraStockFreeQty, ExtraFreeUnit, RefVoucherNo, ReferenceSource, Issueqty,IsBillCancel) \n");
strSql.Append("Select SCM.VoucherNo,VDate,VMiti,VTime,LedgerId,SalesmanId,DepartmentId1,DepartmentId2,DepartmentId3, CurrencyId,CurrencyRate,Sno,ProductId,GodownId,BranchId, null as CostCenterId, \n");
strSql.Append("AltQty,ProductAltUnit, Qty,ProductUnit,AltQty,Qty,0 as FreeQty,0 as StFreeQty,null as FreeUnitId ,1 as Ratio, [SCD].SalesRate, [SCD].BasicAmount,[SCD].TermAmount as ProductTerm,[SCD].NetAmount ,'O'as TransactionType , \n");
strSql.Append("'SC' as Source,SCD.GodownId,0 as DocumentValue, SCM.TermAmount as BillTerm,0 as StockValue,0 as TmpStockValue, 0 as ExtraFreeQty, 0 as ExtraStockFreeQty,null as ExtraFreeUnit,null as RefVoucherNo, null as ReferenceSource, 0 as Issueqty, null as IsBillCancel \n");
strSql.Append("from ERP.SalesChallanMaster SCM Left Outer Join ERP.SalesChallanDetails [SCD] on[SCD].VoucherNo= SCM.VoucherNo where ProductId in (Select ProductId from erp.Product where [ProductCategory] <> 'S') \n");
strSql.Append("and SCM.VoucherNo=@VoucherNo \n");
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VoucherNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VoucherNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveCompany()
{
//------if not exit create MyMaster Database (Our Master Database)-----------
//--------------Create Company Database--------
if (Model.Tag == "NEW")
{
StringBuilder sqlMymaster = new StringBuilder();
sqlMymaster.Append("USE MASTER; if NOT EXISTS(select * from sys.databases where name = 'MYMASTER')\n");
sqlMymaster.Append("begin \n");
sqlMymaster.Append("CREATE DATABASE MYMASTER ON PRIMARY (NAME =MYMASTER, FILENAME = '" + Model.DatabasePath + "MYMASTER.mdf',\n");
sqlMymaster.Append("SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 100%) LOG ON (NAME = " + "MYMASTER_Log, FILENAME = '" + Model.DatabasePath + "MYMASTERLog.ldf', \n");
sqlMymaster.Append("SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 100%) \n");
sqlMymaster.Append("RESTORE DATABASE MYMASTER FROM DISK = '" + Path.GetDirectoryName(Application.ExecutablePath) + "\\MYMASTER.BAK" + "' WITH REPLACE , \n");
sqlMymaster.Append("MOVE 'MYMASTER' TO '" + Model.DatabasePath + "\\MYMASTER.mdf" + "', \n");
sqlMymaster.Append("MOVE 'MYMASTER_Log' TO '" + Model.DatabasePath + "\\MYMASTERLog.ldf" + "' \n");
sqlMymaster.Append("end \n");
sqlMymaster.Append("CREATE DATABASE " + Model.DatabaseName.Trim() + " ON PRIMARY (NAME = " + Model.DatabaseName + ", FILENAME = '" + Model.DatabasePath + Model.DatabaseName + ".mdf',\n");
sqlMymaster.Append("SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 100%) LOG ON (NAME = " + Model.DatabaseName + "_Log, FILENAME = '" + Model.DatabasePath + Model.DatabaseName + "Log.ldf', \n");
sqlMymaster.Append("SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 100%) \n");
// sqlMymaster.Append("USE[master] \n");
//sqlMymaster.Append("GO \n");
//sqlMymaster.Append("RESTORE DATABASE MANISH FROM DISK = '" + Model.DatabasePath + Model.DatabaseName + ".mdf'\n");
//sqlMymaster.Append("WITH FILE = 1, \n");
//sqlMymaster.Append("MOVE 'DEFAULTDATA' TO '" + Model.DatabasePath + "\\" + Model.DatabaseName.Trim() + ".mdf" + "', \n");
//sqlMymaster.Append("MOVE 'DEFAULTDATA_log' TO '" + Model.DatabasePath + "\\" + Model.DatabaseName.Trim() + "Log.ldf, \n");
//sqlMymaster.Append("RECOVERY, REPLACE, STATS = 15; \n");
int result = DAL.ExecuteNonQuery(CommandType.Text, sqlMymaster.ToString());
}
//------------------------CREATE DATABASE END------------------------------------
//StringBuilder strSql = new StringBuilder();
//strSql.Append("BEGIN TRANSACTION \n");
//strSql.Append("BEGIN TRY \n");
//if (Model.Tag == "NEW")
//{
// strSql.Append("Insert INTO MYMaster.dbo.CompanyMaster (IniTial, CompanyName, StartDate, Enddate, DatabaseName, DatabasePath, BackupPath) VALUES ('" + Model.Initial + "','" + Model.CompanyName + "','" + Model.StartDate + "','" + Model.EndDate + "','" + Model.DatabaseName + "','" + Model.DatabasePath + "','" + Model.DataBackupPath + "')");
// strSql.Append("Insert into ERP.CompanyInfo(Initial, CompanyName, StartDate, EndDate, FiscalYear, RegDate, Address, City,District, State,Country, PhoneNo,AltPhoneNo, Fax, PanNo, Email, Website, VersionNo, EnterBy, CreateDate) \n");
// strSql.Append("Select '" + Model.Initial.Trim() + "',N'" + Model.CompanyName.Trim().Replace("'", "''") + "','" + Model.StartDate.ToString("MM/dd/yyyy") + "','" + Model.EndDate.ToString("MM/dd/yyyy") + "','" + Model.FiscalYear + "',NULL," + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + "," + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + "," + ((Model.District.Trim() == "") ? "null" : "N'" + Model.District.Trim().Replace("'", "''") + "'") + "," + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + "," + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + "," + ((Model.PhoneNo.Trim() == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.AltPhoneNo.Trim() == "") ? "null" : "N'" + Model.AltPhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.Fax.Trim() == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + "," + ((Model.PanNo.Trim() == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + "," + ((Model.Email.Trim() == "") ? "null" : "N'" + Model.Email.Trim().Replace("'", "''") + "'") + "," + ((Model.Website.Trim() == "") ? "null" : "N'" + Model.Website.Trim().Replace("'", "''") + "'") + "," + ((Model.VersionNo == 0) ? "0" : "'" + Model.VersionNo + "'") + ",'" + Model.EnterBy + "',GETDATE() \n");
// strSql.Append("SET @VNo ='" + Model.Initial.Trim() + "'");
//}
//else if (Model.Tag == "EDIT")
//{
// strSql.Append("UPDATE MYMaster.dbo.CompanyMaster SET CompanyName='" + Model.CompanyName + "',StartDate='" + Model.StartDate.ToString("MM/dd/yyyy") + "',EndDate='" + Model.EndDate.ToString("MM/dd/yyyy") + "',DatabaseName='" + Model.DatabaseName + "',BackupPath='" + Model.DataBackupPath + "' WHERE INITIAL= '" + Model.Initial + "'");//DatabasePath='" + Model.DatabasePath + "',
// strSql.Append("UPDATE ERP.CompanyInfo SET [CompanyName] =N'" + Model.CompanyName.Trim().Replace("'", "''") + "',StartDate='" + Model.StartDate.ToString("MM/dd/yyyy") + "',EndDate='" + Model.EndDate.ToString("MM/dd/yyyy") + "',FiscalYear='" + Model.FiscalYear + "',Address=" + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ",City=" + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + ",District=" + ((Model.District.Trim() == "") ? "null" : "N'" + Model.District.Trim().Replace("'", "''") + "'") + ",State=" + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + ",Country=" + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ",PhoneNo=" + ((Model.PhoneNo.Trim() == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ",AltPhoneNo=" + ((Model.AltPhoneNo.Trim() == "") ? "null" : "N'" + Model.AltPhoneNo.Trim().Replace("'", "''") + "'") + ",Fax=" + ((Model.Fax.Trim() == "") ? "null" : "N'" + Model.Fax.Trim().Replace("'", "''") + "'") + ",PanNo=" + ((Model.PanNo.Trim() == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + ",Email=" + ((Model.Email.Trim() == "") ? "null" : "N'" + Model.Email.Trim().Replace("'", "''") + "'") + ",Website=" + ((Model.Website.Trim() == "") ? "null" : "N'" + Model.Website.Trim().Replace("'", "''") + "'") + ",VersionNo=" + ((Model.VersionNo == 0) ? "0" : "'" + Model.VersionNo + "'") + ",EnterBy='" + Model.EnterBy.Trim() + "' WHERE Initial = '" + Model.Initial + "'");
// strSql.Append("SET @VNo ='" + Model.Initial + "'");
//}
//else if (Model.Tag == "DELETE")
//{
// strSql.Append("DELETE FROM MYMASTER.DBO.CompanyMaster WHERE Initial = '" + Model.Initial + "' \n");
// strSql.Append("DELETE FROM ERP.CompanyInfo WHERE Initial = '" + Model.Initial + "' \n");
// strSql.Append("SET @VNo ='1'");
//}
//strSql.Append("\n COMMIT TRANSACTION \n");
//strSql.Append("END TRY \n");
//strSql.Append("BEGIN CATCH \n");
//strSql.Append("ROLLBACK TRANSACTION \n");
//strSql.Append("Set @VNo = '' \n");
//strSql.Append("END CATCH \n");
//SqlParameter[] p = new SqlParameter[1];
//p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25);
//p[0].Direction = ParameterDirection.Output;
//DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
//return p[0].Value.ToString();
return("aa");
}
public string SaveCashBank()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
strSql.Append("Set @VoucherNo ='" + Model.VoucherNo.Trim() + "' \n");
if (Model.Tag == "NEW")
{
strSql.Append("INSERT INTO [ERP].[CashBankMaster]([VoucherNo],[VDate],[VTime],[VMiti],[ChequeNo],[ChequeDate],[CurrencyId], \n");
strSql.Append("[CurrencyRate],[LedgerId],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4],[BranchId],\n");
strSql.Append("[CompanyUnitId],[ReferenceNo],[ReferenceDate],[EnterBy],[EnterDate],[Remarks], \n");
strSql.Append("IsReconcile,ReconcileBy,ReconcileDate,IsPosted,PostedBy,PostedDate,IsAuthorized,AuthorizedBy,AuthorizedDate,AuthorizeRemarks,Gadget)\n");
strSql.Append("Select @VoucherNo,'" + Model.VDate.ToString("yyyy-MM-dd") + "','" + Model.VDate.ToString("yyyy-MM-dd") + ' ' + DateTime.Now.ToShortTimeString() + "','" + Model.VMiti.ToString() + "','" + Model.ChequeNo + "', \n");
strSql.Append("" + ((Model.ChequeDate.ToString() == "") ? "null" : "'" + Model.ChequeDate.ToString() + "'") + ", " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",'" + Model.CurrencyRate + "', " + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + "," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + "," + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ", \n");
strSql.Append("" + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + "," + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", \n");
strSql.Append("" + ((Model.ReferenceNo == "") ? "null" : "'" + Model.ReferenceNo + "'") + ", " + ((Model.ReferenceDate.ToString() == "") ? "null" : "'" + Model.ReferenceDate.ToString() + "'") + "," + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",GETDATE()," + ((Model.Remarks == "") ? "null" : "'" + Model.Remarks + "'") + ",\n");
strSql.Append(" '" + Model.IsReconcile + "'," + ((Model.ReconcileBy == "") ? "null" : "'" + Model.ReconcileBy + "'") + ", " + ((Model.ReconcileDate.ToString() == "") ? "null" : "'" + Model.ReconcileDate.ToString() + "'") + ", '" + Model.IsPosted + "', " + ((Model.PostedBy == "") ? "null" : "'" + Model.PostedBy + "'") + ", " + ((Model.PostedDate.ToString() == "") ? "null" : "'" + Model.PostedDate.ToString() + "'") + ",'" + Model.IsAuthorized + "', " + ((Model.AuthorizedBy == "") ? "null" : "'" + Model.AuthorizedBy + "'") + ", \n");
strSql.Append(" " + ((Model.AuthorizedDate.ToString() == "") ? "null" : "'" + Model.AuthorizedDate.ToString() + "'") + ", " + ((Model.AuthorizeRemarks == "") ? "null" : "'" + Model.AuthorizeRemarks + "'") + ", '" + Model.Gadget + "' \n");
strSql.Append("Update ERP.DocumentNumbering set DocCurrentNo = DocCurrentNo + 1 where DocId =" + Model.DocId + "\n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE [ERP].[CashBankMaster] SET [VDate] = '" + Model.VDate.ToString("yyyy-MM-dd") + "',[VTime] = '" + Model.VDate.ToString("yyyy-MM-dd") + ' ' + DateTime.Now.ToShortTimeString() + "',[VMiti] = '" + Model.VMiti.ToString() + "',[ChequeNo] = '" + Model.ChequeNo + "', \n");
strSql.Append("[ChequeDate] = " + ((Model.ChequeDate.ToString() == "") ? "null" : "'" + Convert.ToDateTime(Model.ChequeDate).ToString("yyyy-MM-dd") + "'") + ",[CurrencyId] = " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",[CurrencyRate] = '" + Model.CurrencyRate + "',[LedgerId] = " + ((Model.LedgerId == 0) ? "null" : "'" + Model.LedgerId + "'") + ", \n");
strSql.Append("[DepartmentId1] = " + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ",[DepartmentId2] =" + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ",[DepartmentId3] = " + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ",[DepartmentId4] = " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", \n");
strSql.Append("[BranchId] = " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + ",[CompanyUnitId] = " + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ",[ReferenceNo] = " + ((Model.ReferenceNo == "") ? "null" : "'" + Model.ReferenceNo + "'") + ",[ReferenceDate] = " + ((Model.ReferenceDate.ToString() == "") ? "null" : "'" + Convert.ToDateTime(Model.ReferenceDate).ToString("yyyy-MM-dd") + "'") + ", \n");
strSql.Append("[EnterBy] = " + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",[EnterDate] = GETDATE(),[Remarks] = " + ((Model.Remarks == "") ? "null" : "'" + Model.Remarks + "'") + " \n");
strSql.Append("WHERE [VoucherNo]= @VoucherNo \n");
strSql.Append("DELETE FROM [ERP].[CashBankDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM [ERP].[CashBankDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[CashBankMaster] WHERE VoucherNo = '" + Model.VoucherNo + "' \n");
strSql.Append("Delete from [ERP].[FinanceTransaction] Where [VoucherNo] = @VoucherNo and [Source] = 'CB' \n");
strSql.Append("SET @VoucherNo ='1'");
ModelDetails.Clear();
}
foreach (CashBankDetailsViewModel det in ModelDetails)
{
strSql.Append("INSERT INTO [ERP].[CashBankDetails] ([VoucherNo],[SNO],[LedgerId],[SubledgerId],[SalesmanId],[DepartmentIdDet1],[DepartmentIdDet2],[DepartmentIdDet3],[DepartmentIdDet4],[Naration],[RecAmt],[PayAmt],[RecLocalAmt],[PayLocalAmt]) \n");
strSql.Append("Select @VoucherNo,'" + det.SNO + "','" + det.LedgerId + "'," + ((det.SubledgerId == 0) ? "null" : "'" + det.SubledgerId + "'") + "," + ((det.SalesmanId == 0) ? "null" : "'" + det.SalesmanId + "'") + ", \n");
strSql.Append(" " + ((det.DepartmentIdDet1 == 0) ? "null" : "'" + det.DepartmentIdDet1 + "'") + "," + ((det.DepartmentIdDet2 == 0) ? "null" : "'" + det.DepartmentIdDet2 + "'") + "," + ((det.DepartmentIdDet3 == 0) ? "null" : "'" + det.DepartmentIdDet3 + "'") + "," + ((det.DepartmentIdDet4 == 0) ? "null" : "'" + det.DepartmentIdDet4 + "'") + ", \n");
strSql.Append(" '" + det.Naration + "','" + det.RecAmt + "','" + det.PayAmt + "','" + det.RecLocalAmt + "','" + det.PayLocalAmt + "' \n");
}
ModelDetails.Clear();
if (Model.Tag == "EDIT")
{
strSql.Append("Delete from [ERP].[FinanceTransaction] Where [VoucherNo] = @VoucherNo and [Source] = 'CB' \n");
}
strSql.Append("INSERT INTO[ERP].[FinanceTransaction] ([VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4]\n");
strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt]\n");
strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy]\n");
strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel)\n");
strSql.Append("(select[VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4]\n");
strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt]\n");
strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy]\n");
strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel\n");
strSql.Append("from (Select CM.VoucherNo, CM.VDate,CM.VMiti ,CM.VTime,CM.CurrencyId,CM.CurrencyRate,\n");
strSql.Append("(case when CD.DepartmentIdDet1 is not null then CD.DepartmentIdDet1 else CM.DepartmentId1 end) as DepartmentId1, \n");
strSql.Append("(case when CD.DepartmentIdDet2 is not null then CD.DepartmentIdDet2 else CM.DepartmentId2 end) as DepartmentId2, \n");
strSql.Append("(case when CD.DepartmentIdDet3 is not null then CD.DepartmentIdDet3 else CM.DepartmentId3 end) as DepartmentId3, \n");
strSql.Append("(case when CD.DepartmentIdDet4 is not null then CD.DepartmentIdDet4 else CM.DepartmentId4 end) as DepartmentId4, \n");
strSql.Append("CM.BranchId,CM.CompanyUnitId,CD.SalesmanId, CM.LedgerId,CD.SubLedgerId,\n");
strSql.Append("RecAmt as DrAmt, PayAmt as CrAmt, RecLocalAmt as LocalDrAmt,PayLocalAmt as LocalCrAmt,\n");
strSql.Append("CM.ReconcileDate,CD.Sno,null as CbCode,null as EffecDate,null as TDueDate,null as RefVNo,\n");
strSql.Append(" null as [ClearingDate],null as[ClearedBy],EnterBy,Naration,Remarks,'CB' as Source,chequeNo,ChequeDate,NULL as IsBillCancel \n");
strSql.Append("from ERP.CashBankMaster CM left Join ERP.CashBankDetails CD On CD.VoucherNo = CM.VoucherNo \n");
strSql.Append("Union All \n");
strSql.Append("select VoucherNo, VDate,VMiti ,VTime, CurrencyId, CurrencyRate, DepartmentId1, DepartmentId2, DepartmentId3, DepartmentId4,\n");
strSql.Append("BranchId, CompanyUnitId, SalesmanId, LedgerId, SubLedgerId,\n");
strSql.Append("Case when amt< 0 then Abs(Amt) else 0 end as DrAmt, \n");
strSql.Append("Case when amt >= 0 then Amt else 0 end as CrAmt,\n");
strSql.Append("Case when LocalAmt< 0 then Abs(LocalAmt) else 0 end as LocalDrAmt,\n");
strSql.Append("Case when LocalAmt >= 0 then LocalAmt else 0 end as LocalCrAmt,\n");
strSql.Append("ReconcileDate,Sno,CbCode,EffecDate, TDueDate,RefVNo,\n");
strSql.Append("[ClearingDate],[ClearedBy],EnterBy,Naration,Remarks, Source,chequeNo,ChequeDate,IsBillCancel \n");
strSql.Append("from(Select CM.VoucherNo as VoucherNo, CM.VDate as VDate,VMiti, CM.VTime as VTime, CM.CurrencyId, CM.CurrencyRate,\n");
strSql.Append("(case when CD.DepartmentIdDet1 is not null then CD.DepartmentIdDet1 else CM.DepartmentId1 end) as DepartmentId1, \n");
strSql.Append("(case when CD.DepartmentIdDet2 is not null then CD.DepartmentIdDet2 else CM.DepartmentId2 end) as DepartmentId2, \n");
strSql.Append("(case when CD.DepartmentIdDet3 is not null then CD.DepartmentIdDet3 else CM.DepartmentId3 end) as DepartmentId3, \n");
strSql.Append("(case when CD.DepartmentIdDet4 is not null then CD.DepartmentIdDet4 else CM.DepartmentId4 end) as DepartmentId4, \n");
strSql.Append("CM.BranchId,CM.CompanyUnitId,CD.SalesmanId, CD.LedgerId,CD.SubLedgerId, \n");
strSql.Append("sum((RecAmt) - (PayAmt)) as Amt, sum((RecLocalAmt) - (PayLocalAmt)) as LocalAmt, \n");
strSql.Append("CM.ReconcileDate,CD.Sno,null as CbCode,null as EffecDate,null as TDueDate,null as RefVNo,\n");
strSql.Append("null as [ClearingDate],null as[ClearedBy],EnterBy,Naration,Remarks,'CB' as Source,chequeNo,ChequeDate,NULL as IsBillCancel\n");
strSql.Append("from ERP.CashBankMaster CM left Join ERP.CashBankDetails CD On CD.VoucherNo = CM.VoucherNo\n");
strSql.Append("Group By CM.VoucherNo , CM.VDate , CM.VTime ,VMiti, CM.CurrencyId, CM.CurrencyRate,\n");
strSql.Append("CD.DepartmentIdDet1, CD.DepartmentIdDet2, CD.DepartmentIdDet3, CD.DepartmentIdDet4 , CM.DepartmentId1, CM.DepartmentId2, CM.DepartmentId3, CM.DepartmentId4, \n");
strSql.Append("CM.BranchId, CM.CompanyUnitId, CD.SalesmanId, CD.LedgerId, CD.SubLedgerId, \n");
strSql.Append("ReconcileDate, Sno, EnterBy, Naration, Remarks, chequeNo, ChequeDate \n");
strSql.Append(") as CbMaster )as FinanceTran \n");
strSql.Append("where VoucherNo =@VoucherNo and source='CB' ) \n");
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VoucherNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VoucherNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public void SaveEntryControl()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE ERP.SystemSetting set \n");
strSql.Append("[DateType] = '" + SystemControlModel.DateType.Trim() + "', \n");
strSql.Append("[UDFSystem] = '" + SystemControlModel.UDFSystem + "', \n");
strSql.Append("[ConfirmSave] = '" + SystemControlModel.ConfirmSave + "', \n");
strSql.Append("[ConfirmFormCancel] = '" + SystemControlModel.ConfirmFormCancel + "', \n");
strSql.Append("[ConfirmFormClear] = '" + SystemControlModel.ConfirmFormClear + "', \n");
strSql.Append("[DefaultCurrencyId] = " + ((SystemControlModel.DefaultCurrencyId == 0) ? "null" : "'" + SystemControlModel.DefaultCurrencyId + "'") + ", \n");
strSql.Append("[BackupSchIntvDays] = '" + SystemControlModel.BackupSchIntvDays + "', \n");
strSql.Append("[BackupPath] = '" + SystemControlModel.BackupPath.Trim() + "',\n");
strSql.Append("[PLLedgerId] = " + ((SystemControlModel.PLLedgerId == 0) ? "null" : "'" + SystemControlModel.PLLedgerId + "'") + ", \n");
strSql.Append("[CashLedgerId] = " + ((SystemControlModel.CashLedgerId == 0) ? "null" : "'" + SystemControlModel.CashLedgerId + "'") + ",\n");
strSql.Append("[CardLedgerId] = " + ((SystemControlModel.CardLedgerId == 0) ? "null" : "'" + SystemControlModel.CardLedgerId + "'") + ",\n");
strSql.Append("[VatLedgerId] = " + ((SystemControlModel.VatLedgerId == 0) ? "null" : "'" + SystemControlModel.VatLedgerId + "'") + ",\n");
strSql.Append("[PDCBankLedgerId] = " + ((SystemControlModel.PDCBankLedgerId == 0) ? "null" : "'" + SystemControlModel.PDCBankLedgerId + "'") + ",\n");
strSql.Append("[AmountFormate] = '" + SystemControlModel.AmountFormate.Trim() + "', \n");
strSql.Append("[RateFormat] = '" + SystemControlModel.RateFormat.Trim() + "',\n");
strSql.Append("[QtyFormat] = '" + SystemControlModel.QtyFormat.Trim() + "',\n");
strSql.Append("[AltQtyFormat] = '" + SystemControlModel.AltQtyFormat.Trim() + "', \n");
strSql.Append("[CurrencyFormat] = '" + SystemControlModel.CurrencyFormat.Trim() + "',\n");
strSql.Append("[FontName] = '" + SystemControlModel.FontName.Trim() + "', \n");
strSql.Append("[FontSize] = '" + SystemControlModel.FontSize + "',\n");
strSql.Append("[PaperSize] = '" + SystemControlModel.PaperSize.Trim() + "', \n");
strSql.Append("[ReportFontStyle] = '" + SystemControlModel.ReportFontStyle.Trim() + "', \n");
strSql.Append("[PrintingDateTime] = '" + SystemControlModel.PrintingDateTime + "',\n");
strSql.Append("[PBLedgerId] = " + ((SystemControlModel.PBLedgerId == 0) ? "null" : "'" + SystemControlModel.PBLedgerId + "'") + ",\n");
strSql.Append("[PRLedgerId] = " + ((SystemControlModel.PRLedgerId == 0) ? "null" : "'" + SystemControlModel.PRLedgerId + "'") + ",\n");
strSql.Append("[PBSubLedgerId] = " + ((SystemControlModel.PBSubLedgerId == 0) ? "null" : "'" + SystemControlModel.PBSubLedgerId + "'") + ",\n");
strSql.Append("[PRSubLedgerId] = " + ((SystemControlModel.PRSubLedgerId == 0) ? "null" : "'" + SystemControlModel.PRSubLedgerId + "'") + ",\n");
strSql.Append("[PBVatTermId] = " + ((SystemControlModel.PBVatTermId == 0) ? "null" : "'" + SystemControlModel.PBVatTermId + "'") + ",\n");
strSql.Append("[PABVatTermId] = " + ((SystemControlModel.PABVatTermId == 0) ? "null" : "'" + SystemControlModel.PABVatTermId + "'") + ", \n");
strSql.Append("[PBProductDiscountTermId] = " + ((SystemControlModel.PBProductDiscountTermId == 0) ? "null" : "'" + SystemControlModel.PBProductDiscountTermId + "'") + ",\n");
strSql.Append("[PBBillDiscountTermId] = " + ((SystemControlModel.PBBillDiscountTermId == 0) ? "null" : "'" + SystemControlModel.PBBillDiscountTermId + "'") + ",\n");
strSql.Append("[PBCreditBalanceWarning] = '" + SystemControlModel.PBCreditBalanceWarning.Trim() + "',\n");
strSql.Append("[PBCreditDaysWarning] = '" + SystemControlModel.PBCreditDaysWarning.Trim() + "',\n");
strSql.Append("[PBCarryRate] = '" + SystemControlModel.PBCarryRate + "',\n");
strSql.Append("[PBLastRate] = '" + SystemControlModel.PBLastRate + "',\n");
strSql.Append("[PBBatchRate] = '" + SystemControlModel.PBBatchRate + "', \n");
strSql.Append("[PBGrpWiseBilling] = '" + SystemControlModel.PBGrpWiseBilling + "', \n");
strSql.Append("[PBAdvancePayment] = '" + SystemControlModel.PBAdvancePayment + "', \n");
strSql.Append("[SBLedgerId] = " + ((SystemControlModel.SBLedgerId == 0) ? "null" : "'" + SystemControlModel.SBLedgerId + "'") + ",\n");
strSql.Append("[SRLedgerId] = " + ((SystemControlModel.SRLedgerId == 0) ? "null" : "'" + SystemControlModel.SRLedgerId + "'") + ",\n");
strSql.Append("[SBSubLedgerId] = " + ((SystemControlModel.SBSubLedgerId == 0) ? "null" : "'" + SystemControlModel.SBSubLedgerId + "'") + ",\n");
strSql.Append("[SRSubLedgerId] = " + ((SystemControlModel.SRSubLedgerId == 0) ? "null" : "'" + SystemControlModel.SRSubLedgerId + "'") + ",\n");
strSql.Append("[SBVatTermId] = " + ((SystemControlModel.SBVatTermId == 0) ? "null" : "'" + SystemControlModel.SBVatTermId + "'") + ", \n");
strSql.Append("[SBProductDiscountTermId] = " + ((SystemControlModel.SBProductDiscountTermId == 0) ? "null" : "'" + SystemControlModel.SBProductDiscountTermId + "'") + ",\n");
strSql.Append("[SBBillDiscountTermId] = " + ((SystemControlModel.SBBillDiscountTermId == 0) ? "null" : "'" + SystemControlModel.SBBillDiscountTermId + "'") + ",\n");
strSql.Append("[SBSpecialDiscountTermId] = " + ((SystemControlModel.SBSpecialDiscountTermId == 0) ? "null" : "'" + SystemControlModel.SBSpecialDiscountTermId + "'") + ",\n");
strSql.Append("[SBServiceChargeTermId] = " + ((SystemControlModel.SBServiceChargeTermId == 0) ? "null" : "'" + SystemControlModel.SBServiceChargeTermId + "'") + ", \n");
strSql.Append("[SBCreditBalanceWarning] = '" + SystemControlModel.SBCreditBalanceWarning.Trim() + "', \n");
strSql.Append("[SBCreditDaysWarning] = '" + SystemControlModel.SBCreditDaysWarning.Trim() + "',\n");
strSql.Append("[SBChangeRate] = '" + SystemControlModel.SBChangeRate + "', \n");
strSql.Append("[SBLastRate] = '" + SystemControlModel.SBLastRate + "',\n");
strSql.Append("[SBCarryRate] = '" + SystemControlModel.SBCarryRate + "', \n");
strSql.Append("[DefaultInvoicePrintDesignId] = " + ((SystemControlModel.DefaultInvoicePrintDesignId == 0) ? "null" : "'" + SystemControlModel.DefaultInvoicePrintDesignId + "'") + ", \n");
strSql.Append("[DefaultPOSDocNumberingId] = " + ((SystemControlModel.DefaultPOSDocNumberingId == 0) ? "null" : "'" + SystemControlModel.DefaultPOSDocNumberingId + "'") + ", \n");
strSql.Append("[DefaultOrderPrintDesignId] = " + ((SystemControlModel.DefaultOrderPrintDesignId == 0) ? "null" : "'" + SystemControlModel.DefaultOrderPrintDesignId + "'") + ", \n");
strSql.Append("[DefaultOrderDocNumberingId] = " + ((SystemControlModel.DefaultOrderDocNumberingId == 0) ? "null" : "'" + SystemControlModel.DefaultOrderDocNumberingId + "'") + ", \n");
strSql.Append("[StockValueInSalesReturn] = '" + SystemControlModel.StockValueInSalesReturn + "',\n");
strSql.Append("[AvailableStock] = '" + SystemControlModel.AvailableStock + "',\n");
strSql.Append("[SBGrpWiseBilling] = '" + SystemControlModel.SBGrpWiseBilling + "',\n");
strSql.Append("[OpeningStockLedgerId] = " + ((SystemControlModel.OpeningStockLedgerId == 0) ? "null" : "'" + SystemControlModel.OpeningStockLedgerId + "'") + ", \n");
strSql.Append("[ClosingStockLedgerId] = " + ((SystemControlModel.ClosingStockLedgerId == 0) ? "null" : "'" + SystemControlModel.ClosingStockLedgerId + "'") + ", \n");
strSql.Append("[ClosingStockLedgerBSId] = " + ((SystemControlModel.ClosingStockLedgerBSId == 0) ? "null" : "'" + SystemControlModel.ClosingStockLedgerBSId + "'") + ", \n");
strSql.Append("[ClosingStockSubLedgerId] = " + ((SystemControlModel.ClosingStockSubLedgerId == 0) ? "null" : "'" + SystemControlModel.ClosingStockSubLedgerId + "'") + ", \n");
strSql.Append("[OpeningStockSubLedgerId] = " + ((SystemControlModel.OpeningStockSubLedgerId == 0) ? "null" : "'" + SystemControlModel.OpeningStockSubLedgerId + "'") + ", \n");
strSql.Append("[ClosingStockSubLedgerBSId] = " + ((SystemControlModel.ClosingStockSubLedgerBSId == 0) ? "null" : "'" + SystemControlModel.ClosingStockSubLedgerBSId + "'") + ", \n");
strSql.Append("[StockInHandLedgerId] = " + ((SystemControlModel.StockInHandLedgerId == 0) ? "null" : "'" + SystemControlModel.StockInHandLedgerId + "'") + ", \n");
strSql.Append("[NegativeStockWarning] = '" + SystemControlModel.NegativeStockWarning + "', \n");
strSql.Append("[AltQtyAlteration] = '" + SystemControlModel.AltQtyAlteration + "', \n");
strSql.Append("[AlterationPart] = '" + SystemControlModel.AlterationPart + "', \n");
strSql.Append("[CarryBatchQty] = '" + SystemControlModel.CarryBatchQty + "', \n");
strSql.Append("[BreakupQty] = '" + SystemControlModel.BreakupQty + "', \n");
strSql.Append("[MfgDate] = '" + SystemControlModel.MfgDate + "', \n");
strSql.Append("[ExpDate] = '" + SystemControlModel.ExpDate + "', \n");
strSql.Append("[MfgDateValidation] = '" + SystemControlModel.MfgDateValidation + "', \n");
strSql.Append("[ExpDateValidation] = '" + SystemControlModel.ExpDateValidation + "', \n");
strSql.Append("[FreeQty] = '" + SystemControlModel.FreeQty + "', \n");
strSql.Append("[ExtraFreeQty] = '" + SystemControlModel.ExtraFreeQty + "',\n");
strSql.Append("[IGodownWiseFilter] = '" + SystemControlModel.IGodownWiseFilter + "', \n");
strSql.Append("[SalaryLedgerId] = " + ((SystemControlModel.SalaryLedgerId == 0) ? "null" : "'" + SystemControlModel.SalaryLedgerId + "'") + ", \n");
strSql.Append("[TDSLedgerId] = " + ((SystemControlModel.TDSLedgerId == 0) ? "null" : "'" + SystemControlModel.TDSLedgerId + "'") + ",\n");
strSql.Append("[SecurityDepositLedgerId] = " + ((SystemControlModel.SecurityDepositLedgerId == 0) ? "null" : "'" + SystemControlModel.SecurityDepositLedgerId + "'") + ", \n");
strSql.Append("[BranchOrCompanyUnitWise] ='" + SystemControlModel.BranchOrCompanyUnitWise.Trim() + "',\n");
strSql.Append("[CompanyPrintName] = '" + SystemControlModel.CompanyPrintName.Trim() + "',\n");
strSql.Append("[InterBranchPurchaseLedgerId] = " + ((SystemControlModel.InterBranchPurchase == 0) ? "null" : "'" + SystemControlModel.InterBranchPurchase + "'") + ",\n");
strSql.Append("[InterBranchSalesLedgerId] = " + ((SystemControlModel.InterBranchSales == 0) ? "null" : "'" + SystemControlModel.InterBranchSales + "'") + ", \n");
strSql.Append("[Gadget] = '" + SystemControlModel.Gadget.Trim() + "',\n");
strSql.Append("[AbbreviatedAmount] = '" + SystemControlModel.AbbreviatedAmount + "' ,\n");
strSql.Append("[FineAndPenaltyLedgerId] = " + ((SystemControlModel.FineAndPenaltyLedgerId == 0) ? "null" : "'" + SystemControlModel.FineAndPenaltyLedgerId + "'") + ",\n");
strSql.Append("[TDSPercent] = '" + SystemControlModel.TDSPercent + "',\n");
strSql.Append("[SecurityDepositPercent] = '" + SystemControlModel.SecurityDepositPercent + "',\n");
strSql.Append("[FineAndPenaltyPercent] = '" + SystemControlModel.FineAndPenaltyPercent + "',\n");
strSql.Append("[ProductionLedgerId] = " + ((SystemControlModel.ProductionLedgerId == 0) ? "null" : "'" + SystemControlModel.ProductionLedgerId + "'") + "\n");
strSql.Append("DELETE FROM ERP.EntryControl \n");
for (int i = 0; i < EntryControl.Count; i++)
{
strSql.Append("INSERT INTO ERP.EntryControl \n");
strSql.Append("SELECT '" + EntryControl[i].EntryModule.ToString() + "','" + EntryControl[i].ControlName.ToString() + "','" + EntryControl[i].ControlValue.ToString() + "','" + EntryControl[i].MandatoryOpt.ToString() + "' \n");
}
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString());
EntryControl.Clear();
}
public string SaveGeneralLedger()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
if (Model.Tag == "NEW")
{
strSql.Append("declare @ledgerId int=(select ISNULL((Select Top 1 max(cast(ledgerId as int)) from ERP.Generalledger),0)+1) \n");
strSql.Append("Insert into [ERP].[Generalledger] (LedgerId, GlDesc, GlShortName,GlPrintingName, GlAlias, ACCode, GlCategory, AccountGrpId, AccountSubGrpId, AreaId, SalesmanId, CurrencyId, DepartmentId1,DepartmentId2,DepartmentId3,DepartmentId4, IsSubledger, IsCard, IsTDSAplicable, IsDocAdjustment, \n");
strSql.Append("CreditDays, CreditDaysWarning, CreditLimit, CreditLimitWarning, CreditType, ChequeReciveDays, InterestRate, SchemeId, Address, Address1, City, District, State, Country, PhoneNo, AltPhoneNo, MobileNo, DOB, Age, Gender, FaxNo, Email, Website, PanNo,NationalId,DrvingLicenseNo,\n");
strSql.Append("GlPictureUrl,ContactPersonName, CPAddress, CPPhoneNo, CPAltPhoneNo, Status, IsSystemLedger, EnterBy, EnterDate,Gadget,CustomerType)\n");//GlPicture,Signature,
strSql.Append("Select @ledgerId,N'" + Model.GlDesc.Trim().Replace("'", "''") + "',N'" + Model.GlShortName.Trim().Replace("'", "''") + "'," + ((Model.GlPrintingName == "") ? "null" : "N'" + Model.GlPrintingName.Trim().Replace("'", "''") + "'") + "," + ((Model.GlAlias == "") ? "null" : "N'" + Model.GlAlias.Trim().Replace("'", "''") + "'") + "," + ((Model.ACCode == "") ? "null" : "N'" + Model.ACCode.Trim().Replace("'", "''") + "'") + ",N'" + Model.GlCategory.Trim().Replace("'", "''") + "', \n");
strSql.Append("'" + Model.AccountGrpId + "' , " + ((Model.AccountSubGrpId == 0) ? "null" : "'" + Model.AccountSubGrpId + "'") + ", " + ((Model.AreaId == 0) ? "null" : "'" + Model.AreaId + "'") + ", " + ((Model.SalesmanId == 0) ? "null" : "'" + Model.SalesmanId + "'") + "," + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + "," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + "," + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + "," + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + "," + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", \n");
strSql.Append("'" + Model.IsSubledger.ToString().ToLower() + "', '" + Model.IsCard.ToString().ToLower() + "','" + Model.IsTDSAplicable.ToString().ToLower() + "', '" + Model.IsDocAdjustment.ToString().ToLower() + "','" + ClsGlobal.Val(Model.CreditDays.ToString()) + "','" + Model.CreditDaysWarning.ToString() + "','" + ClsGlobal.Val(Model.CreditLimit.ToString()) + "', '" + Model.CreditLimitWarning.ToString() + "',null, " + ((Model.ChequeReciveDays == 0) ? "null" : "'" + Model.ChequeReciveDays + "'") + ", " + ((Model.InterestRate == 0) ? "null" : "'" + Model.InterestRate + "'") + ", " + ((Model.SchemeId == 0) ? "null" : "'" + Model.SchemeId + "'") + ",\n");
strSql.Append("" + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + "," + ((Model.Address1.Trim() == "") ? "null" : "N'" + Model.Address1.Trim().Replace("'", "''") + "'") + "," + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + "," + ((Model.District.Trim() == "") ? "null" : "N'" + Model.District.Trim().Replace("'", "''") + "'") + "," + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + "," + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ", \n");
strSql.Append("" + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.AltPhoneNo == "") ? "null" : "N'" + Model.AltPhoneNo.Trim().Replace("'", "''") + "'") + "," + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + ",null,null,null,\n");
strSql.Append("" + ((Model.FaxNo == "") ? "null" : "N'" + Model.FaxNo.Trim().Replace("'", "''") + "'") + " , " + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + ",null," + ((Model.PanNo == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + ",null,null,\n");
strSql.Append(" null ," + ((Model.ContactPersonName == "") ? "null" : "N'" + Model.ContactPersonName.Trim().Replace("'", "''") + "'") + " , " + ((Model.CPAddress == "") ? "null" : "N'" + Model.CPAddress.Trim().Replace("'", "''") + "'") + "," + ((Model.CPPhoneNo == "") ? "null" : "'" + Model.CPPhoneNo.Trim().Replace("'", "''") + "'") + ",null, \n");
strSql.Append("'" + Model.Status.ToString().ToLower() + "',0, '" + Model.EnterBy + "', GETDATE(),Gadget='" + Model.Gadget + "','" + Model.CustomerType + "' \n");
if (ClsGlobal.BranchOrCompanyUnitWise == "Branch")
{
foreach (LedgerBranchCompanyUnitModel det in ModelLedgerBranchCompanyUnit)
{
strSql.Append("INSERT INTO [ERP].[LedgerBranchUnitMapping] (LedgerId, BranchId, CompanyUnitId) \n");
strSql.Append("Select @ledgerId,'" + det.BranchId + "',null \n");
}
ModelLedgerBranchCompanyUnit.Clear();
}
else if (ClsGlobal.BranchOrCompanyUnitWise == "CompanyUnit")
{
foreach (LedgerBranchCompanyUnitModel det in ModelLedgerBranchCompanyUnit)
{
strSql.Append("INSERT INTO [ERP].[LedgerBranchUnitMapping] (LedgerId, BranchId, CompanyUnitId) \n");
strSql.Append("Select @ledgerId,'" + det.BranchId + "','" + det.CompanyUnitId + "' \n");
}
ModelLedgerBranchCompanyUnit.Clear();
}
strSql.Append("SET @VNo =@ledgerId");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE ERP.Generalledger SET GlDesc=N'" + Model.GlDesc.Trim().Replace("'", "''") + "',GlShortName = N'" + Model.GlShortName.Trim().Replace("'", "''") + "',GlPrintingName = " + ((Model.GlPrintingName == "") ? "null" : "N'" + Model.GlPrintingName.Trim().Replace("'", "''") + "'") + ", GlAlias = " + ((Model.GlAlias == "") ? "null" : "N'" + Model.GlAlias.Trim().Replace("'", "''") + "'") + ",ACCode= " + ((Model.ACCode == "") ? "null" : "N'" + Model.ACCode.Trim().Replace("'", "''") + "'") + ",GlCategory = N'" + Model.GlCategory.Trim().Replace("'", "''") + "',\n");
strSql.Append("AccountGrpId = '" + Model.AccountGrpId + "' ,AccountSubGrpId = " + ((Model.AccountSubGrpId == 0) ? "null" : "'" + Model.AccountSubGrpId + "'") + ",AreaId = " + ((Model.AreaId == 0) ? "null" : "'" + Model.AreaId + "'") + ",SalesmanId = " + ((Model.SalesmanId == 0) ? "null" : "'" + Model.SalesmanId + "'") + ",CurrencyId = " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",DepartmentId1 = " + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ",DepartmentId2 = " + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ",DepartmentId3 = " + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", DepartmentId4 = " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", \n");
strSql.Append("IsSubledger = '" + Model.IsSubledger.ToString().ToLower() + "',IsCard = '" + Model.IsCard.ToString().ToLower() + "',IsTDSAplicable ='" + Model.IsTDSAplicable.ToString().ToLower() + "',IsDocAdjustment = '" + Model.IsDocAdjustment.ToString().ToLower() + "',CreditDays=" + ClsGlobal.Val(Model.CreditDays.ToString()) + ",CreditDaysWarning = '" + Model.CreditDaysWarning.ToString() + "',CreditLimit = '" + ClsGlobal.Val(Model.CreditLimit.ToString()) + "',CreditLimitWarning = '" + Model.CreditLimitWarning.ToString() + "',ChequeReciveDays = '" + ClsGlobal.Val(Model.ChequeReciveDays.ToString()) + "',InterestRate = '" + ClsGlobal.Val(Model.InterestRate.ToString()) + "',SchemeId = " + ((Model.SchemeId == 0) ? "null" : "'" + Model.SchemeId + "'") + ",\n");
strSql.Append("Address = " + ((Model.Address.Trim() == "") ? "null" : "N'" + Model.Address.Trim().Replace("'", "''") + "'") + ",Address1 =" + ((Model.Address1.Trim() == "") ? "null" : "N'" + Model.Address1.Trim().Replace("'", "''") + "'") + ",City = " + ((Model.City.Trim() == "") ? "null" : "N'" + Model.City.Trim().Replace("'", "''") + "'") + ",District = " + ((Model.District.Trim() == "") ? "null" : "N'" + Model.District.Trim().Replace("'", "''") + "'") + ",State = " + ((Model.State.Trim() == "") ? "null" : "N'" + Model.State.Trim().Replace("'", "''") + "'") + ",Country = " + ((Model.Country.Trim() == "") ? "null" : "N'" + Model.Country.Trim().Replace("'", "''") + "'") + ", \n");
strSql.Append("PhoneNo = " + ((Model.PhoneNo == "") ? "null" : "N'" + Model.PhoneNo.Trim().Replace("'", "''") + "'") + ",AltPhoneNo =" + ((Model.AltPhoneNo == "") ? "null" : "N'" + Model.AltPhoneNo.Trim().Replace("'", "''") + "'") + ",MobileNo = " + ((Model.MobileNo == "") ? "null" : "N'" + Model.MobileNo.Trim().Replace("'", "''") + "'") + ",\n");
strSql.Append("FaxNo = " + ((Model.FaxNo == "") ? "null" : "N'" + Model.FaxNo.Trim().Replace("'", "''") + "'") + " ,Email = " + ((Model.EmailId == "") ? "null" : "N'" + Model.EmailId.Trim().Replace("'", "''") + "'") + ",PanNo = " + ((Model.PanNo == "") ? "null" : "N'" + Model.PanNo.Trim().Replace("'", "''") + "'") + ",\n");
strSql.Append("ContactPersonName = " + ((Model.ContactPersonName == "") ? "null" : "N'" + Model.ContactPersonName.Trim().Replace("'", "''") + "'") + " ,CPAddress = " + ((Model.CPAddress == "") ? "null" : "N'" + Model.CPAddress.Trim().Replace("'", "''") + "'") + ",CPPhoneNo = " + ((Model.CPPhoneNo == "") ? "null" : "'" + Model.CPPhoneNo.Trim().Replace("'", "''") + "'") + ", \n");
strSql.Append("Status = '" + Model.Status.ToString().ToLower() + "',EnterBy = '" + Model.EnterBy + "',EnterDate = GETDATE(),Gadget='" + Model.Gadget + "',CustomerType='" + Model.CustomerType + "' \n");
strSql.Append("WHERE LedgerId = '" + Model.LedgerId + "' \n");
strSql.Append("SET @VNo ='" + Model.LedgerId + "'");
if (ClsGlobal.BranchOrCompanyUnitWise == "Branch")
{
strSql.Append("Delete from [ERP].[LedgerBranchUnitMapping] where LedgerId ='" + Model.LedgerId + "' \n");
foreach (LedgerBranchCompanyUnitModel det in ModelLedgerBranchCompanyUnit)
{
strSql.Append("INSERT INTO [ERP].[LedgerBranchUnitMapping] (LedgerId, BranchId, CompanyUnitId) \n");
strSql.Append("Select '" + Model.LedgerId + "','" + det.BranchId + "',null \n");
}
ModelLedgerBranchCompanyUnit.Clear();
}
else if (ClsGlobal.BranchOrCompanyUnitWise == "CompanyUnit")
{
strSql.Append("Delete from [ERP].[LedgerBranchUnitMapping] where LedgerId ='" + Model.LedgerId + "' \n");
foreach (LedgerBranchCompanyUnitModel det in ModelLedgerBranchCompanyUnit)
{
strSql.Append("INSERT INTO [ERP].[LedgerBranchUnitMapping] (LedgerId, BranchId, CompanyUnitId) \n");
strSql.Append("Select '" + Model.LedgerId + "','" + det.BranchId + "','" + det.CompanyUnitId + "' \n");
}
ModelLedgerBranchCompanyUnit.Clear();
}
}
else if (Model.Tag == "DELETE")
{
strSql.Append("Delete from [ERP].[LedgerBranchUnitMapping] where LedgerId ='" + Model.LedgerId + "' \n");
strSql.Append("DELETE FROM ERP.Generalledger WHERE ledgerId = '" + Model.LedgerId + "' \n");
strSql.Append("DELETE FROM MyMaster.dbo.UserMaster WHERE LedgerId = '" + Model.LedgerId + "' \n");
strSql.Append("SET @VNo ='1'");
}
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}
public string SaveJournal()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("BEGIN TRANSACTION \n");
strSql.Append("BEGIN TRY \n");
strSql.Append("Set @VoucherNo ='" + Model.VoucherNo.Trim() + "' \n");
if (Model.Tag == "NEW")
{
strSql.Append("INSERT INTO [ERP].[JournalMaster]([VoucherNo],[VDate],[VTime],[VMiti],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4], \n");
strSql.Append("[BranchId],[CompanyUnitId],[ReferenceNo],[ReferenceDate],[EnterBy],[EnterDate],[Remarks],[SourceModule],[JVType],[IsReconcile],\n");
strSql.Append("[ReconcileBy],[ReconcileDate],[IsPosted],[PostedBy],[PostedDate],[IsAuthorized],[AuthorizedBy],[AuthorizedDate],[AuthorizeRemarks],[Gadget])\n");
strSql.Append("Select @VoucherNo,'" + Model.VDate.ToString("yyyy-MM-dd") + "','" + Model.VDate.ToString("yyyy-MM-dd") + ' ' + DateTime.Now.ToShortTimeString() + "','" + Model.VMiti.ToString() + "', \n");
strSql.Append(" " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",'" + Model.CurrencyRate + "'," + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + "," + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ", \n");
strSql.Append("" + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ", " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + "," + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + "," + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ", \n");
strSql.Append("" + ((Model.ReferenceNo == "") ? "null" : "'" + Model.ReferenceNo + "'") + ", " + ((Model.ReferenceDate.ToString() == "") ? "null" : "'" + Model.ReferenceDate.ToString() + "'") + "," + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",GETDATE()," + ((Model.Remarks == "") ? "null" : "'" + Model.Remarks + "'") + ",'" + Model.SourceModule + "','" + Model.JVType + "',\n");
strSql.Append(" '" + Model.IsReconcile + "'," + ((Model.ReconcileBy == "") ? "null" : "'" + Model.ReconcileBy + "'") + ", " + ((Model.ReconcileDate.ToString() == "") ? "null" : "'" + Model.ReconcileDate.ToString() + "'") + ", '" + Model.IsPosted + "', " + ((Model.PostedBy == "") ? "null" : "'" + Model.PostedBy + "'") + ", " + ((Model.PostedDate.ToString() == "") ? "null" : "'" + Model.PostedDate.ToString() + "'") + ",'" + Model.IsAuthorized + "', " + ((Model.AuthorizedBy == "") ? "null" : "'" + Model.AuthorizedBy + "'") + ", \n");
strSql.Append(" " + ((Model.AuthorizedDate.ToString() == "") ? "null" : "'" + Model.AuthorizedDate.ToString() + "'") + ", " + ((Model.AuthorizeRemarks == "") ? "null" : "'" + Model.AuthorizeRemarks + "'") + ", '" + Model.Gadget + "' \n");
strSql.Append("Update ERP.DocumentNumbering set DocCurrentNo = DocCurrentNo + 1 where DocId =" + Model.DocId + "\n");
}
else if (Model.Tag == "EDIT")
{
strSql.Append("UPDATE [ERP].[JournalMaster] SET [VDate] = '" + Model.VDate.ToString("yyyy-MM-dd") + "',[VTime] = '" + Model.VDate.ToString("yyyy-MM-dd") + ' ' + DateTime.Now.ToShortTimeString() + "',[VMiti] = '" + Model.VMiti.ToString() + "', \n");
strSql.Append("[CurrencyId] = " + ((Model.CurrencyId == 0) ? "null" : "'" + Model.CurrencyId + "'") + ",[CurrencyRate] = '" + Model.CurrencyRate + "', \n");
strSql.Append("[DepartmentId1] = " + ((Model.DepartmentId1 == 0) ? "null" : "'" + Model.DepartmentId1 + "'") + ",[DepartmentId2] =" + ((Model.DepartmentId2 == 0) ? "null" : "'" + Model.DepartmentId2 + "'") + ",[DepartmentId3] = " + ((Model.DepartmentId3 == 0) ? "null" : "'" + Model.DepartmentId3 + "'") + ",[DepartmentId4] = " + ((Model.DepartmentId4 == 0) ? "null" : "'" + Model.DepartmentId4 + "'") + ", \n");
strSql.Append("[BranchId] = " + ((Model.BranchId == 0) ? "null" : "'" + Model.BranchId + "'") + ",[CompanyUnitId] = " + ((Model.CompanyUnitId == 0) ? "null" : "'" + Model.CompanyUnitId + "'") + ",[ReferenceNo] = " + ((Model.ReferenceNo == "") ? "null" : "'" + Model.ReferenceNo + "'") + ",[ReferenceDate] = " + ((Model.ReferenceDate.ToString() == "") ? "null" : "'" + Convert.ToDateTime(Model.ReferenceDate).ToString("yyyy-MM-dd") + "'") + ", \n");
strSql.Append("[EnterBy] = " + ((Model.EnterBy == "") ? "null" : "'" + Model.EnterBy + "'") + ",[EnterDate] = GETDATE(),[Remarks] = " + ((Model.Remarks == "") ? "null" : "'" + Model.Remarks + "'") + " ,[SourceModule]='" + Model.SourceModule + "',[JVType]='" + Model.JVType + "' \n");
strSql.Append("WHERE [VoucherNo]= @VoucherNo \n");
strSql.Append("DELETE FROM [ERP].[JournalDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
}
else if (Model.Tag == "DELETE")
{
strSql.Append("DELETE FROM [ERP].[JournalDetails] WHERE VoucherNo ='" + Model.VoucherNo + "' \n");
strSql.Append("DELETE FROM [ERP].[JournalMaster] WHERE VoucherNo = '" + Model.VoucherNo + "' \n");
strSql.Append(" Delete from[ERP].[FinanceTransaction] Where [VoucherNo] = @VoucherNo and[Source] = 'JV' \n");
strSql.Append("SET @VoucherNo ='1'");
ModelDetails.Clear();
}
foreach (JournalDetailsViewModel det in ModelDetails)
{
strSql.Append("INSERT INTO [ERP].[JournalDetails]([VoucherNo],[CurrencyRate],[SNO],[LedgerId],[SubLedgerId],[SalesmanId],[DepartmentIdDet1],[DepartmentIdDet2],[DepartmentIdDet3],[DepartmentIdDet4],[AmtType],[Amount],[Narration])\n");
strSql.Append("Select @VoucherNo,'" + det.CurrencyRate + "','" + det.SNO + "','" + det.LedgerId + "'," + ((det.SubLedgerId == 0) ? "null" : "'" + det.SubLedgerId + "'") + "," + ((det.SalesmanId == 0) ? "null" : "'" + det.SalesmanId + "'") + ", \n");
strSql.Append(" " + ((det.DepartmentIdDet1 == 0) ? "null" : "'" + det.DepartmentIdDet1 + "'") + "," + ((det.DepartmentIdDet2 == 0) ? "null" : "'" + det.DepartmentIdDet2 + "'") + "," + ((det.DepartmentIdDet3 == 0) ? "null" : "'" + det.DepartmentIdDet3 + "'") + "," + ((det.DepartmentIdDet4 == 0) ? "null" : "'" + det.DepartmentIdDet4 + "'") + ", \n");
strSql.Append(" '" + det.AmtType + "','" + det.Amount + "','" + det.Narration + "' \n");
}
ModelDetails.Clear();
if (Model.Tag == "EDIT")
{
strSql.Append(" Delete from[ERP].[FinanceTransaction] Where [VoucherNo] = @VoucherNo and[Source] = 'JV' \n");
}
//strSql.Append("INSERT INTO[ERP].[FinanceTransaction] ([VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4]\n");
//strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt]\n");
//strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy]\n");
//strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel)\n");
//strSql.Append("(select[VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4]\n");
//strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt]\n");
//strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy]\n");
//strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel\n");
//strSql.Append("from (Select '0' as VoucherNo, (select StartDate-1 from ERP.CompanyInfo ) as VDate,(select MMiti from erp.DateMiti where Mdate=(select convert(datetime, StartDate-1,105) from ERP.CompanyInfo )) as VMiti , (select StartDate-1 from ERP.CompanyInfo ) as VTime,JM.CurrencyId,JM.CurrencyRate,\n");
//strSql.Append("(case when JD.DepartmentIdDet1 is not null then JD.DepartmentIdDet1 else JM.DepartmentId1 end) as DepartmentId1, \n");
//strSql.Append("(case when JD.DepartmentIdDet2 is not null then JD.DepartmentIdDet2 else JM.DepartmentId2 end) as DepartmentId2, \n");
//strSql.Append("(case when JD.DepartmentIdDet3 is not null then JD.DepartmentIdDet3 else JM.DepartmentId3 end) as DepartmentId3, \n");
//strSql.Append("(case when JD.DepartmentIdDet4 is not null then JD.DepartmentIdDet4 else JM.DepartmentId4 end) as DepartmentId4, \n");
//strSql.Append("JM.BranchId,JM.CompanyUnitId,JD.SalesmanId, JD.LedgerId,JD.SubLedgerId,\n");
//strSql.Append("Case when AmtType = 'D' then Amount else 0 end as DrAmt,\n");
//strSql.Append("Case when AmtType = 'C' then Amount else 0 end as CrAmt,\n");
//strSql.Append("Case when AmtType = 'D' then Amount else 0 end as LocalDrAmt, \n");
//strSql.Append("Case when AmtType = 'C' then Amount else 0 end as LocalCrAmt, \n");
//strSql.Append("JM.ReconcileDate,JD.Sno,null as CbCode,null as EffecDate,null as TDueDate,null as RefVNo,\n");
//strSql.Append("null as [ClearingDate],null as[ClearedBy],EnterBy,null as Naration,Remarks,'OB' as Source,null as chequeNo,null as ChequeDate,NULL as IsBillCancel \n");
//strSql.Append("from ERP.JournalMaster JM left Join ERP.JournalDetails JD On JD.VoucherNo = JM.VoucherNo \n");
//strSql.Append("Union All \n");
//strSql.Append("Select JM.VoucherNo, JM.VDate,JM.VMiti ,JM.VTime,JM.CurrencyId,JM.CurrencyRate,\n");
//strSql.Append("(case when JD.DepartmentIdDet1 is not null then JD.DepartmentIdDet1 else JM.DepartmentId1 end) as DepartmentId1, \n");
//strSql.Append("(case when JD.DepartmentIdDet2 is not null then JD.DepartmentIdDet2 else JM.DepartmentId2 end) as DepartmentId2, \n");
//strSql.Append("(case when JD.DepartmentIdDet3 is not null then JD.DepartmentIdDet3 else JM.DepartmentId3 end) as DepartmentId3, \n");
//strSql.Append("(case when JD.DepartmentIdDet4 is not null then JD.DepartmentIdDet4 else JM.DepartmentId4 end) as DepartmentId4, \n");
//strSql.Append("JM.BranchId,JM.CompanyUnitId,JD.SalesmanId, JD.LedgerId,JD.SubLedgerId,\n");
//strSql.Append("Case when AmtType = 'D' then Amount else 0 end as DrAmt,\n");
//strSql.Append("Case when AmtType = 'C' then Amount else 0 end as CrAmt,\n");
//strSql.Append("Case when AmtType = 'D' then Amount else 0 end as LocalDrAmt, \n");
//strSql.Append("Case when AmtType = 'C' then Amount else 0 end as LocalCrAmt, \n");
//strSql.Append("JM.ReconcileDate,JD.Sno,null as CbCode,null as EffecDate,null as TDueDate,null as RefVNo,\n");
//strSql.Append("null as [ClearingDate],null as[ClearedBy],EnterBy,null as Naration,Remarks,'JV' as Source,null as chequeNo,null as ChequeDate,NULL as IsBillCancel \n");
//strSql.Append("from ERP.JournalMaster JM left Join ERP.JournalDetails JD On JD.VoucherNo = JM.VoucherNo \n");
//strSql.Append(")as FinanceTran where VoucherNo =@VoucherNo and source='JV' ) \n");
strSql.Append("INSERT INTO[ERP].[FinanceTransaction] ([VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4] \n");
strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt] \n");
strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy] \n");
strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel) \n");
strSql.Append("(select[VoucherNo],[VDate],[VMiti],[VTime],[CurrencyId],[CurrencyRate],[DepartmentId1],[DepartmentId2],[DepartmentId3],[DepartmentId4] \n");
strSql.Append(",[BranchId],[CompanyUnitId],[SalesmanId],[LedgerId],[SubLedgerId],[DrAmt],[CrAmt],[LocalDrAmt],[LocalCrAmt] \n");
strSql.Append(",[ReconcileDate],[SNO],[CbCode],[EffecDate],[TDueDate],[RefVNo],[ClearingDate],[ClearedBy] \n");
strSql.Append(",[EnterBy],[Naration],[Remarks],[Source],[chequeNo],[ChequeDate],IsBillCancel \n");
strSql.Append("from ( \n");
strSql.Append("Select JM.VoucherNo, JM.VDate,JM.VMiti ,JM.VTime,JM.CurrencyId,JM.CurrencyRate, \n");
strSql.Append("(case when JD.DepartmentIdDet1 is not null then JD.DepartmentIdDet1 else JM.DepartmentId1 end) as DepartmentId1, \n");
strSql.Append("(case when JD.DepartmentIdDet2 is not null then JD.DepartmentIdDet2 else JM.DepartmentId2 end) as DepartmentId2, \n");
strSql.Append("(case when JD.DepartmentIdDet3 is not null then JD.DepartmentIdDet3 else JM.DepartmentId3 end) as DepartmentId3, \n");
strSql.Append("(case when JD.DepartmentIdDet4 is not null then JD.DepartmentIdDet4 else JM.DepartmentId4 end) as DepartmentId4, \n");
strSql.Append("JM.BranchId,JM.CompanyUnitId,JD.SalesmanId, JD.LedgerId,JD.SubLedgerId, \n");
strSql.Append("Case when AmtType = 'D' then Amount else 0 end as DrAmt, \n");
strSql.Append("Case when AmtType = 'C' then Amount else 0 end as CrAmt, \n");
strSql.Append("Case when AmtType = 'D' then Amount else 0 end as LocalDrAmt, \n");
strSql.Append("Case when AmtType = 'C' then Amount else 0 end as LocalCrAmt, \n");
strSql.Append("JM.ReconcileDate,JD.Sno,null as CbCode,null as EffecDate,null as TDueDate,null as RefVNo, \n");
strSql.Append("null as [ClearingDate],null as[ClearedBy],EnterBy,jd.Narration as Naration,Remarks,'JV' as Source,null as chequeNo,null as ChequeDate,NULL as IsBillCancel \n");
strSql.Append("from ERP.JournalMaster JM left Join ERP.JournalDetails JD On JD.VoucherNo = JM.VoucherNo \n");
strSql.Append("where JM.VoucherNo =@VoucherNo \n");
strSql.Append(")as FinanceTran) \n");
strSql.Append("\n COMMIT TRANSACTION \n");
strSql.Append("END TRY \n");
strSql.Append("BEGIN CATCH \n");
strSql.Append("ROLLBACK TRANSACTION \n");
strSql.Append("Set @VoucherNo = '' \n");
strSql.Append("END CATCH \n");
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter("@VoucherNo", SqlDbType.VarChar, 25)
{
Direction = ParameterDirection.Output
};
DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString(), p);
return(p[0].Value.ToString());
}